public void SetExtractionContext(StringWithDependencies contextItemName, WebsiteCrawlingSettings.DocumentTypes contextDocumentType)
{
if (contextItemName != null)
{
Context = new ExtractionContext(contextItemName, contextDocumentType);
}
}
private async Task ExtractColumnsAsync(ExtractionContext context, CancellationToken token)
{
var query = BuildExtractColumnsQuery(context);
var currentTableId = 0;
var cmd = Connection.CreateCommand(query);
ColumnResolver columnResolver = null;
await using (cmd.ConfigureAwait(false)) {
var reader = await cmd.ExecuteReaderAsync(token).ConfigureAwait(false);
await using (reader.ConfigureAwait(false)) {
while (await reader.ReadAsync(token).ConfigureAwait(false))
{
ReadColumnData(context, reader, ref currentTableId, ref columnResolver);
}
}
}
query = BuildExtractIdentityColumnsQuery(context);
cmd = Connection.CreateCommand(query);
await using (cmd.ConfigureAwait(false)) {
var reader = await cmd.ExecuteReaderAsync(token).ConfigureAwait(false);
await using (reader.ConfigureAwait(false)) {
while (await reader.ReadAsync(token).ConfigureAwait(false))
{
ReadIdentityColumnData(reader, context);
}
}
}
}
private void ExtractColumns(ExtractionContext context)
{
var query = BuildExtractColumnsQuery(context);
var currentTableId = 0;
ColumnResolver columnResolver = null;
using (var cmd = Connection.CreateCommand(query))
using (var reader = cmd.ExecuteReader()) {
while (reader.Read())
{
ReadColumnData(context, reader, ref currentTableId, ref columnResolver);
}
}
query = BuildExtractIdentityColumnsQuery(context);
using (var cmd = Connection.CreateCommand(query))
using (var reader = cmd.ExecuteReader()) {
while (reader.Read())
{
ReadIdentityColumnData(reader, context);
}
}
}
private string MakeSchemaFilter(ExtractionContext context)
{
if (context.TargetSchemas.Count == 0)
{
return(" > 0");
}
var builder = new StringBuilder();
foreach (var targetSchemaName in context.TargetSchemas)
{
var schema = context.Catalog.Schemas[targetSchemaName];
if (schema == null)
{
throw new InvalidOperationException("Cannot build schema filter because schemas haven't been extracted yet. Extract schemas first.");
}
if (!context.ReversedSchemaIndex.TryGetValue(schema, out var schemaId))
{
continue;
}
_ = builder.Append(builder.Length == 0
? schemaId.ToString(CultureInfo.InvariantCulture)
: $", {schemaId.ToString(CultureInfo.InvariantCulture)}");
}
return($" IN ({builder})");
}
/// <inheritdoc/>
protected override ISqlCompileUnit BuildExtractTableAndViewColumnsQuery(ExtractionContext context)
{
var tableMap = context.TableMap;
var viewMap = context.ViewMap;
var columnsTable = PgAttribute;
var defaultValuesTable = PgAttrDef;
var typesTable = PgType;
var select = SqlDml.Select(columnsTable
.LeftOuterJoin(defaultValuesTable,
columnsTable["attrelid"] == defaultValuesTable["adrelid"] &&
columnsTable["attnum"] == defaultValuesTable["adnum"])
.InnerJoin(typesTable, typesTable["oid"] == columnsTable["atttypid"]));
select.Where = columnsTable["attisdropped"] == false &&
columnsTable["attnum"] > 0 &&
(SqlDml.In(columnsTable["attrelid"], CreateOidRow(tableMap.Keys)) ||
SqlDml.In(columnsTable["attrelid"], CreateOidRow(viewMap.Keys)));
select.Columns.Add(columnsTable["attrelid"]);
select.Columns.Add(columnsTable["attnum"]);
select.Columns.Add(columnsTable["attname"]);
select.Columns.Add(typesTable["typname"]);
select.Columns.Add(columnsTable["atttypmod"]);
select.Columns.Add(columnsTable["attnotnull"]);
select.Columns.Add(columnsTable["atthasdef"]);
select.Columns.Add(SqlDml.ColumnRef(SqlDml.Column(
SqlDml.FunctionCall("pg_get_expr", defaultValuesTable["adbin"], defaultValuesTable["adrelid"])), "adsrc"));
select.OrderBy.Add(columnsTable["attrelid"]);
select.OrderBy.Add(columnsTable["attnum"]);
return(select);
}
private string BuildExtractForeignKeysQuery(ExtractionContext context)
{
var query = @"
SELECT
fk.schema_id,
fk.object_id,
fk.name,
fk.delete_referential_action,
fk.update_referential_action,
fkc.constraint_column_id,
fkc.parent_object_id,
fkc.parent_column_id,
fkc.referenced_object_id,
fkc.referenced_column_id
FROM {CATALOG}.sys.foreign_keys fk
INNER JOIN {CATALOG}.sys.foreign_key_columns fkc
ON fk.object_id = fkc.constraint_object_id
WHERE fk.schema_id {SCHEMA_FILTER}
ORDER BY
fk.schema_id,
fkc.parent_object_id,
fk.object_id,
fkc.constraint_column_id";
query = context.PerformReplacements(query);
return(query);
}
private string BuildExtractTablesAndViewsQuery(ExtractionContext context)
{
var query = @"
SELECT
t.schema_id,
t.object_id,
t.name,
t.type
FROM (
SELECT
schema_id,
object_id,
name,
0 type
FROM {CATALOG}.sys.tables
WHERE {SYSTABLE_FILTER}
UNION
SELECT
schema_id,
object_id,
name,
1 type
FROM {CATALOG}.sys.views
) AS t
WHERE t.schema_id {SCHEMA_FILTER}
ORDER BY t.schema_id, t.object_id";
query = context.PerformReplacements(query);
return(query);
}
private void ReadTypeData(DbDataReader reader, ExtractionContext context)
{
var userTypeId = reader.GetInt32(1);
int systemTypeId = reader.GetByte(2);
var name = reader.GetString(3);
context.TypeNameIndex[userTypeId] = name;
// Type is not user-defined
if (!reader.GetBoolean(7))
{
return;
}
// Unknown system type
string systemTypeName;
if (!context.TypeNameIndex.TryGetValue(systemTypeId, out systemTypeName))
{
return;
}
var currentSchema = context.SchemaIndex[reader.GetInt32(0)];
var dataType = GetValueType(systemTypeName, reader.GetByte(4), reader.GetByte(5), reader.GetInt16(6));
var domain = currentSchema.CreateDomain(name, dataType);
context.DomainIndex[userTypeId] = domain;
}
private void ExtractCheckConstraints(ExtractionContext context)
{
using var reader = ExecuteReader(context.PerformReplacements(GetExtractCheckConstraintsQuery()));
while (reader.Read())
{
ReadCheckConstraintData(reader, context.Catalog);
}
}
private void ExtractSequences(ExtractionContext context)
{
using var reader = ExecuteReader(context.PerformReplacements(GetExtractSequencesQuery()));
while (reader.Read())
{
ReadSequenceData(reader, context.Catalog);
}
}
private void ReadColumnData(ExtractionContext context, DbDataReader reader, ref int currentTableId, ref ColumnResolver columnResolver)
{
var tableId = reader.GetInt32(1);
var columnId = reader.GetInt32(2);
GetDataTable(tableId, context, ref currentTableId, ref columnResolver);
// Table column
if (columnResolver.Table is Table table)
{
var typeId = reader.GetInt32(4);
var sqlDataType = GetValueType(context.TypeNameIndex[typeId], reader.GetByte(5), reader.GetByte(6), reader.GetInt16(7));
var column = table.CreateColumn(reader.GetString(3), sqlDataType);
var count = table.TableColumns.Count;
// <-db column index is not equal to column position in table.Columns. This is common after column removals or insertions.
columnResolver.RegisterColumnMapping(columnId, count - 1);
// Domain
if (context.DomainIndex.TryGetValue(typeId, out var domain))
{
column.Domain = domain;
}
// Collation
if (!reader.IsDBNull(8))
{
var currentSchema = context.SchemaIndex[reader.GetInt32(0)];
var collationName = reader.GetString(8);
column.Collation = currentSchema.Collations[collationName] ?? currentSchema.CreateCollation(collationName);
}
// Nullability
column.IsNullable = reader.GetBoolean(9);
// Default constraint
if (!reader.IsDBNull(11))
{
_ = table.CreateDefaultConstraint(reader.GetString(11), column);
column.DefaultValue = reader.GetString(12).StripRoundBrackets();
}
// Computed column
if (!reader.IsDBNull(13))
{
column.IsPersisted = reader.GetBoolean(13);
column.Expression = SqlDml.Native(reader.GetString(14));
}
}
else
{
var view = (View)columnResolver.Table;
_ = view.CreateColumn(reader.GetString(3));
}
}
private void ExtractViewColumns(ExtractionContext context)
{
using var reader = ExecuteReader(context.PerformReplacements(GetExtractViewColumnsQuery()));
var state = new ColumnReaderState <View>(context.Catalog);
while (reader.Read())
{
ReadViewColumnData(reader, ref state);
}
}
/// <inheritdoc/>
protected override ISqlCompileUnit BuildExtractSequencesQuery(ExtractionContext context)
{
var sequenceMap = context.SequenceMap;
// select all the sequences registered in map
var tableRef = PgSequence;
var select = SqlDml.Select(tableRef);
select.Where = SqlDml.In(tableRef["seqrelid"], SqlDml.Array(sequenceMap.Keys.ToArray()));
return(select);
}
private void GetDataTable(int id, ExtractionContext context, ref int currentId, ref ColumnResolver currentObj)
{
if (id == currentId)
{
return;
}
currentObj = context.ColumnResolverIndex[id];
currentId = id;
}
private void ReadSchemaData(DbDataReader reader, ExtractionContext context)
{
var identifier = reader.GetInt32(0);
var name = reader.GetString(1);
var schema = context.Catalog.Schemas[name] ?? context.Catalog.CreateSchema(name);
context.SchemaIndex[identifier] = schema;
context.ReversedSchemaIndex[schema] = identifier;
schema.Owner = reader.GetString(2);
}
private void ExtractForeignKeys(ExtractionContext context)
{
using var reader = ExecuteReader(context.PerformReplacements(GetExtractForeignKeysQuery()));
var state = new ForeignKeyReaderState(context.Catalog);
while (reader.Read())
{
ReadForeignKeyColumnData(reader, ref state);
}
}
protected virtual void ExtractCatalogContents(ExtractionContext context)
{
ExtractSchemas(context);
RegisterReplacements(context);
ExtractTypes(context);
ExtractTablesAndViews(context);
ExtractColumns(context);
ExtractIndexes(context);
ExtractForeignKeys(context);
ExtractFulltextIndexes(context);
}
private ExtractionContext CreateContext(string catalogName, string[] schemaNames)
{
var catalog = new Catalog(catalogName);
var context = new ExtractionContext(catalog, schemaNames);
foreach (var schemaName in schemaNames)
{
_ = catalog.CreateSchema(schemaName);
}
return(context);
}
private void ExtractTablesAndViews(ExtractionContext context)
{
var query = BuildExtractTablesAndViewsQuery(context);
using var cmd = Connection.CreateCommand(query);
using var reader = cmd.ExecuteReader();
while (reader.Read())
{
ReadTableOrViewData(reader, context);
}
}
// Types & domains must be extracted for all schemas
private void ExtractTypes(ExtractionContext context)
{
var query = BuildExtractTypesQuery(context);
using var command = Connection.CreateCommand(query);
using var reader = command.ExecuteReader();
while (reader.Read())
{
ReadTypeData(reader, context);
}
}
/// <inheritdoc/>
protected override void ReadSequenceDescriptor(DbDataReader dataReader, ExtractionContext context)
{
var seqId = Convert.ToInt64(dataReader["seqrelid"]);
var descriptor = context.SequenceMap[seqId].SequenceDescriptor;
descriptor.Increment = Convert.ToInt64(dataReader["seqincrement"]);
descriptor.IsCyclic = Convert.ToBoolean(dataReader["seqcycle"]);
descriptor.MinValue = Convert.ToInt64(dataReader["seqmin"]);
descriptor.MaxValue = Convert.ToInt64(dataReader["seqmax"]);
descriptor.StartValue = Convert.ToInt64(dataReader["seqstart"]);
}
private async Task ExtractCatalogContentsAsync(ExtractionContext context, CancellationToken token)
{
await ExtractTablesAsync(context, token).ConfigureAwait(false);
await ExtractTableColumnsAsync(context, token).ConfigureAwait(false);
await ExtractViewsAsync(context, token).ConfigureAwait(false);
await ExtractViewColumnsAsync(context, token).ConfigureAwait(false);
await ExtractIndexesAsync(context, token).ConfigureAwait(false);
await ExtractForeignKeysAsync(context, token).ConfigureAwait(false);
await ExtractCheckConstraintsAsync(context, token).ConfigureAwait(false);
await ExtractUniqueAndPrimaryKeyConstraintsAsync(context, token).ConfigureAwait(false);
}
/// <inheritdoc/>
protected override void ReadSequenceDescriptor(DbDataReader dataReader, ExtractionContext context)
{
var seqId = Convert.ToInt64(dataReader["id"]);
var descriptor = context.SequenceMap[seqId].SequenceDescriptor;
descriptor.Increment = Convert.ToInt64(dataReader["increment_by"]);
descriptor.IsCyclic = Convert.ToBoolean(dataReader["is_cycled"]);
descriptor.MinValue = Convert.ToInt64(dataReader["min_value"]);
descriptor.MaxValue = Convert.ToInt64(dataReader["max_value"]);
descriptor.StartValue = Convert.ToInt64(dataReader["start_value"]);
}
private void ExtractViews(ExtractionContext context)
{
var query = context.PerformReplacements(GetExtractViewsQuery());
using var command = Connection.CreateCommand(query);
using var reader = command.ExecuteReader();
while (reader.Read())
{
ReadViewData(reader, context.Catalog);
}
}
private void ExtractCatalogContents(ExtractionContext context)
{
ExtractTables(context);
ExtractTableColumns(context);
ExtractViews(context);
ExtractViewColumns(context);
ExtractIndexes(context);
ExtractForeignKeys(context);
ExtractCheckConstraints(context);
ExtractUniqueAndPrimaryKeyConstraints(context);
}
protected virtual async Task ExtractCatalogContentsAsync(ExtractionContext context, CancellationToken token)
{
await ExtractSchemasAsync(context, token).ConfigureAwait(false);
RegisterReplacements(context);
await ExtractTypesAsync(context, token).ConfigureAwait(false);
await ExtractTablesAndViewsAsync(context, token).ConfigureAwait(false);
await ExtractColumnsAsync(context, token).ConfigureAwait(false);
await ExtractIndexesAsync(context, token).ConfigureAwait(false);
await ExtractForeignKeysAsync(context, token).ConfigureAwait(false);
await ExtractFulltextIndexesAsync(context, token).ConfigureAwait(false);
}
// All schemas
private void ExtractSchemas(ExtractionContext context)
{
var query = BuildExtractSchemasQuery(context);
using (var cmd = Connection.CreateCommand(query))
using (var reader = cmd.ExecuteReader()) {
while (reader.Read())
{
ReadSchemaData(reader, context);
}
}
}
private async Task ExtractSequencesAsync(ExtractionContext context, CancellationToken token)
{
var query = context.PerformReplacements(GetExtractSequencesQuery());
var reader = await ExecuteReaderAsync(query, token).ConfigureAwait(false);
await using (reader.ConfigureAwait(false)) {
while (await reader.ReadAsync(token).ConfigureAwait(false))
{
ReadSequenceData(reader, context.Catalog);
}
}
}
protected virtual string BuildExtractIndexesQuery(ExtractionContext context)
{
var query = @"
SELECT
t.schema_id,
t.object_id,
t.type,
i.index_id,
i.name,
i.type,
i.is_primary_key,
i.is_unique,
i.is_unique_constraint,
i.fill_factor,
ic.column_id,
0,
ic.key_ordinal,
ic.is_descending_key,
ic.is_included_column,
NULL,
NULL
FROM {CATALOG}.sys.indexes i
INNER JOIN (
SELECT
schema_id,
object_id,
0 AS type
FROM {CATALOG}.sys.tables
WHERE {SYSTABLE_FILTER}
UNION
SELECT
schema_id,
object_id,
1 AS type
FROM {CATALOG}.sys.views
) AS t
ON i.object_id = t.object_id
INNER JOIN {CATALOG}.sys.index_columns ic
ON i.object_id = ic.object_id
AND i.index_id = ic.index_id
WHERE i.type IN(1, 2, 4)
AND schema_id {SCHEMA_FILTER}
ORDER BY
t.schema_id,
t.object_id,
i.index_id,
ic.is_included_column,
ic.key_ordinal,
ic.index_column_id";
query = context.PerformReplacements(query);
return(query);
}
private void ExtractIndexes(ExtractionContext context)
{
var query = context.PerformReplacements(GetExtractIndexesQuery());
using var reader = (OracleDataReader)ExecuteReader(query);
var state = new IndexColumnReaderState(context.Catalog);
while (reader.Read())
{
ReadIndexColumnData(reader, ref state);
}
}
