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);
}
}
}
}
protected virtual void ExtractFulltextIndexes()
{
string query = @"
SELECT
t.schema_id,
fic.object_id,
fi.unique_index_id,
fc.name,
fc.is_default,
fic.column_id,
fic.type_column_id,
fl.name,
i.name,
fi.change_tracking_state,
fi.crawl_start_date
FROM {CATALOG}.sys.tables t
INNER JOIN {CATALOG}.sys.fulltext_index_columns AS fic
ON t.object_id = fic.object_id
INNER JOIN {CATALOG}.sys.fulltext_languages AS fl
ON fic.language_id = fl.lcid
INNER JOIN {CATALOG}.sys.fulltext_indexes fi
ON fic.object_id = fi.object_id
INNER JOIN {CATALOG}.sys.fulltext_catalogs fc
ON fc.fulltext_catalog_id = fi.fulltext_catalog_id
INNER JOIN {CATALOG}.sys.indexes AS i
ON fic.object_id = i.object_id
AND fi.unique_index_id = i.index_id
WHERE {SYSTABLE_FILTER}
AND t.schema_id {SCHEMA_FILTER}
ORDER BY
t.schema_id,
fic.object_id,
fic.column_id";
query = PerformReplacements(query);
int currentTableId = 0;
ColumnResolver table = null;
FullTextIndex index = null;
using (var cmd = Connection.CreateCommand(query))
using (var reader = cmd.ExecuteReader())
while (reader.Read())
{
int nextTableId = reader.GetInt32(1);
if (currentTableId != nextTableId)
{
GetDataTable(nextTableId, ref currentTableId, ref table);
index = table.Table.CreateFullTextIndex(string.Empty);
index.FullTextCatalog = reader.GetBoolean(4)
? null
: reader.GetString(3);
index.UnderlyingUniqueIndex = reader.GetString(8);
index.ChangeTrackingMode = GetChangeTrackingMode(reader.GetString(9), reader.IsDBNull(10));
}
var column = index.CreateIndexColumn(table.GetColumn(reader.GetInt32(5)));
column.TypeColumn = (reader.IsDBNull(6)) ? null : table.GetColumn(reader.GetInt32(6));
column.Languages.Add(new Language(reader.GetString(7)));
}
}
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 void GetDataTable(int id, ref int currentId, ref ColumnResolver currentObj)
{
if (id == currentId)
{
return;
}
currentObj = columnResolverIndex[id];
currentId = id;
}
private void ExtractForeignKeys()
{
string 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 = PerformReplacements(query);
int tableId = 0, constraintId = 0;
ColumnResolver referencingTable = null;
ColumnResolver referencedTable = null;
ForeignKey foreignKey = null;
using (var cmd = Connection.CreateCommand(query))
using (var reader = cmd.ExecuteReader()) {
while (reader.Read())
{
// First column in constraint => new constraint
if (reader.GetInt32(5) == 1)
{
GetDataTable(reader.GetInt32(6), ref tableId, ref referencingTable);
foreignKey = ((Table)referencingTable.Table).CreateForeignKey(reader.GetString(2));
referencedTable = columnResolverIndex[reader.GetInt32(8)];
foreignKey.ReferencedTable = (Table)referencedTable.Table;
foreignKey.OnDelete = GetReferentialAction(reader.GetByte(3));
foreignKey.OnUpdate = GetReferentialAction(reader.GetByte(4));
}
foreignKey.Columns.Add((TableColumn)referencingTable.GetColumn(reader.GetInt32(7)));
foreignKey.ReferencedColumns.Add((TableColumn)referencedTable.GetColumn(reader.GetInt32(9)));
}
}
}
protected virtual void ExtractFulltextIndexes(ExtractionContext context)
{
var query = BuildExtractFullTextIndexesQuery(context);
var currentTableId = 0;
ColumnResolver table = null;
FullTextIndex index = null;
using var cmd = Connection.CreateCommand(query);
using var reader = cmd.ExecuteReader();
while (reader.Read())
{
ReadFullTextIndexColumnData(reader, context, ref currentTableId, ref table, ref index);
}
}
private void ExtractForeignKeys(ExtractionContext context)
{
var query = BuildExtractForeignKeysQuery(context);
int tableId = 0, constraintId = 0;
ColumnResolver referencingTable = null;
ColumnResolver referencedTable = null;
ForeignKey foreignKey = null;
using var cmd = Connection.CreateCommand(query);
using var reader = cmd.ExecuteReader();
while (reader.Read())
{
ReadForeignKeyColumnData(reader, context, ref tableId, ref foreignKey, ref referencingTable, ref referencedTable);
}
}
private void ReadForeignKeyColumnData(DbDataReader reader, ExtractionContext context, ref int tableId, ref ForeignKey foreignKey,
ref ColumnResolver referencingTable, ref ColumnResolver referencedTable)
{
// First column in constraint => new constraint
if (reader.GetInt32(5) == 1)
{
GetDataTable(reader.GetInt32(6), context, ref tableId, ref referencingTable);
foreignKey = ((Table)referencingTable.Table).CreateForeignKey(reader.GetString(2));
referencedTable = context.ColumnResolverIndex[reader.GetInt32(8)];
foreignKey.ReferencedTable = (Table)referencedTable.Table;
foreignKey.OnDelete = GetReferentialAction(reader.GetByte(3));
foreignKey.OnUpdate = GetReferentialAction(reader.GetByte(4));
}
foreignKey.Columns.Add((TableColumn)referencingTable.GetColumn(reader.GetInt32(7)));
foreignKey.ReferencedColumns.Add((TableColumn)referencedTable.GetColumn(reader.GetInt32(9)));
}
private void ExtractIndexes(ExtractionContext context)
{
var query = BuildExtractIndexesQuery(context);
const int spatialIndexType = 4;
var tableId = 0;
ColumnResolver table = null;
Index index = null;
PrimaryKey primaryKey = null;
UniqueConstraint uniqueConstraint = null;
using var cmd = Connection.CreateCommand(query);
using var reader = cmd.ExecuteReader();
while (reader.Read())
{
ReadIndexColumnData(reader, context,
ref tableId, spatialIndexType, ref primaryKey, ref uniqueConstraint, ref index, ref table);
}
}
protected virtual async Task ExtractFulltextIndexesAsync(ExtractionContext context, CancellationToken token)
{
var query = BuildExtractFullTextIndexesQuery(context);
var currentTableId = 0;
ColumnResolver table = null;
FullTextIndex index = null;
var 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))
{
ReadFullTextIndexColumnData(reader, context, ref currentTableId, ref table, ref index);
}
}
}
}
private void ReadFullTextIndexColumnData(DbDataReader reader, ExtractionContext context,
ref int currentTableId, ref ColumnResolver table, ref FullTextIndex index)
{
var nextTableId = reader.GetInt32(1);
if (currentTableId != nextTableId)
{
GetDataTable(nextTableId, context, ref currentTableId, ref table);
index = table.Table.CreateFullTextIndex(string.Empty);
index.FullTextCatalog = reader.GetBoolean(4)
? null
: reader.GetString(3);
index.UnderlyingUniqueIndex = reader.GetString(8);
index.ChangeTrackingMode = GetChangeTrackingMode(reader.GetString(9), reader.IsDBNull(10));
}
var column = index.CreateIndexColumn(table.GetColumn(reader.GetInt32(5)));
column.TypeColumn = (reader.IsDBNull(6)) ? null : table.GetColumn(reader.GetInt32(6));
column.Languages.Add(new Language(reader.GetString(7)));
}
private async Task ExtractForeignKeysAsync(ExtractionContext context, CancellationToken token)
{
var query = BuildExtractForeignKeysQuery(context);
int tableId = 0, constraintId = 0;
ColumnResolver referencingTable = null;
ColumnResolver referencedTable = null;
ForeignKey foreignKey = null;
var 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))
{
ReadForeignKeyColumnData(reader, context, ref tableId, ref foreignKey, ref referencingTable, ref referencedTable);
}
}
}
}
private async Task ExtractIndexesAsync(ExtractionContext context, CancellationToken token)
{
var query = BuildExtractIndexesQuery(context);
const int spatialIndexType = 4;
var tableId = 0;
ColumnResolver table = null;
Index index = null;
PrimaryKey primaryKey = null;
UniqueConstraint uniqueConstraint = null;
var 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))
{
ReadIndexColumnData(reader, context,
ref tableId, spatialIndexType, ref primaryKey, ref uniqueConstraint, ref index, ref table);
}
}
}
}
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 ExtractIndexes()
{
string query = GetIndexQuery();
const int spatialIndexType = 4;
int tableId = 0;
ColumnResolver table = null;
Index index = null;
PrimaryKey primaryKey = null;
UniqueConstraint uniqueConstraint = null;
using (var cmd = Connection.CreateCommand(query))
using (var reader = cmd.ExecuteReader())
while (reader.Read())
{
int columnId = reader.GetInt32(10);
int indexType = reader.GetByte(5);
// First column in index => new index or index is spatial (always has exactly one column)
if (reader.GetByte(12) == 1 || indexType == spatialIndexType)
{
primaryKey = null;
uniqueConstraint = null;
index = null;
// Table could be changed only on new index creation
GetDataTable(reader.GetInt32(1), ref tableId, ref table);
var indexId = reader.GetInt32(3);
var indexName = reader.GetString(4);
// Index is a part of primary key constraint
if (reader.GetBoolean(6))
{
primaryKey = ((Table)table.Table).CreatePrimaryKey(indexName);
if (Driver.ServerInfo.PrimaryKey.Features.Supports(PrimaryKeyConstraintFeatures.Clustered))
{
primaryKey.IsClustered = reader.GetByte(5) == 1;
}
}
else
{
// Spatial index
if (indexType == spatialIndexType)
{
index = table.Table.CreateSpatialIndex(indexName);
index.FillFactor = reader.GetByte(9);
}
else
{
index = table.Table.CreateIndex(indexName);
index.IsUnique = reader.GetBoolean(7);
if (Driver.ServerInfo.Index.Features.Supports(IndexFeatures.Clustered))
{
index.IsClustered = reader.GetByte(5) == 1;
}
index.FillFactor = reader.GetByte(9);
if (!reader.IsDBNull(15) && reader.GetBoolean(15))
{
index.Where = SqlDml.Native(reader.GetString(16));
}
// Index is a part of unique constraint
if (reader.GetBoolean(8))
{
uniqueConstraint = ((Table)table.Table).CreateUniqueConstraint(indexName);
if (index.IsClustered && Driver.ServerInfo.UniqueConstraint.Features.Supports(UniqueConstraintFeatures.Clustered))
{
uniqueConstraint.IsClustered = true;
}
}
}
}
}
// Column is a part of a primary index
if (reader.GetBoolean(6))
{
primaryKey.Columns.Add((TableColumn)table.GetColumn(columnId));
}
else
{
// Column is a part of unique constraint
if (reader.GetBoolean(8))
{
uniqueConstraint.Columns.Add((TableColumn)table.GetColumn(columnId));
}
if (index != null)
{
// Column is non key column
if (reader.GetBoolean(14))
{
index.NonkeyColumns.Add(table.GetColumn(columnId));
}
else
{
index.CreateIndexColumn(table.GetColumn(columnId), !reader.GetBoolean(13));
}
}
}
}
}
private void ExtractColumns()
{
string query = @"
SELECT
t.schema_id,
c.object_id,
c.column_id,
c.name,
c.user_type_id,
c.precision,
c.scale,
c.max_length,
c.collation_name,
c.is_nullable,
c.is_identity,
dc.name,
dc.definition,
cc.is_persisted,
cc.definition
FROM {CATALOG}.sys.columns AS c
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 c.object_id = t.object_id
LEFT OUTER JOIN {CATALOG}.sys.default_constraints AS dc
ON c.object_id = dc.parent_object_id
AND c.column_id = dc.parent_column_id
LEFT OUTER JOIN {CATALOG}.sys.computed_columns AS cc
ON c.object_id = cc.object_id
AND c.column_id = cc.column_id
WHERE t.schema_id {SCHEMA_FILTER}
ORDER BY
t.schema_id,
c.object_id,
c.column_id";
query = PerformReplacements(query);
int currentTableId = 0;
ColumnResolver columnResolver = null;
//int currentSchemaId = schemaId;
//Schema currentSchema = schema;
using (var cmd = Connection.CreateCommand(query))
using (var reader = cmd.ExecuteReader()) {
while (reader.Read())
{
int tableId = reader.GetInt32(1);
int columnId = reader.GetInt32(2);
GetDataTable(tableId, ref currentTableId, ref columnResolver);
var table = columnResolver.Table as Table;
// Table column
if (table != null)
{
var typeId = reader.GetInt32(4);
var sqlDataType = GetValueType(typeNameIndex[typeId], reader.GetByte(5), reader.GetByte(6), reader.GetInt16(7));
var column = table.CreateColumn(reader.GetString(3), sqlDataType);
int 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
Domain domain;
if (domainIndex.TryGetValue(typeId, out domain))
{
column.Domain = domain;
}
// Collation
if (!reader.IsDBNull(8))
{
var currentSchema = GetSchema(reader.GetInt32(0));
string 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));
}
}
}
query = @"
SELECT
t.schema_id,
ic.object_id,
ic.column_id,
ic.seed_value,
ic.increment_value,
ic.last_value
FROM {CATALOG}.sys.identity_columns AS ic
INNER JOIN {CATALOG}.sys.tables AS t
ON ic.object_id = t.object_id
WHERE seed_value IS NOT NULL
AND increment_value IS NOT NULL
AND {SYSTABLE_FILTER}
AND t.schema_id {SCHEMA_FILTER}
ORDER BY
t.schema_id,
ic.object_id";
query = PerformReplacements(query);
using (var cmd = Connection.CreateCommand(query))
using (var reader = cmd.ExecuteReader())
while (reader.Read())
{
var dataColumn = columnResolverIndex[reader.GetInt32(1)].GetColumn(reader.GetInt32(2));
var tableColumn = (TableColumn)dataColumn;
tableColumn.SequenceDescriptor = new SequenceDescriptor(tableColumn);
if (!reader.IsDBNull(3))
{
tableColumn.SequenceDescriptor.StartValue = Convert.ToInt64(reader.GetValue(3));
}
if (!reader.IsDBNull(4))
{
tableColumn.SequenceDescriptor.Increment = Convert.ToInt64(reader.GetValue(4));
}
if (!reader.IsDBNull(5))
{
tableColumn.SequenceDescriptor.LastValue = Convert.ToInt64(reader.GetValue(5));
}
}
}
private void ReadIndexColumnData(DbDataReader reader, ExtractionContext context,
ref int tableId, int spatialIndexType, ref PrimaryKey primaryKey,
ref UniqueConstraint uniqueConstraint, ref Index index, ref ColumnResolver table)
{
var columnId = reader.GetInt32(10);
int indexType = reader.GetByte(5);
// First column in index => new index or index is spatial (always has exactly one column)
if (reader.GetByte(12) == 1 || indexType == spatialIndexType)
{
primaryKey = null;
uniqueConstraint = null;
index = null;
// Table could be changed only on new index creation
GetDataTable(reader.GetInt32(1), context, ref tableId, ref table);
var indexId = reader.GetInt32(3);
var indexName = reader.GetString(4);
// Index is a part of primary key constraint
if (reader.GetBoolean(6))
{
primaryKey = ((Table)table.Table).CreatePrimaryKey(indexName);
if (Driver.ServerInfo.PrimaryKey.Features.Supports(PrimaryKeyConstraintFeatures.Clustered))
{
primaryKey.IsClustered = reader.GetByte(5) == 1;
}
}
else
{
// Spatial index
if (indexType == spatialIndexType)
{
index = table.Table.CreateSpatialIndex(indexName);
index.FillFactor = reader.GetByte(9);
}
else
{
index = table.Table.CreateIndex(indexName);
index.IsUnique = reader.GetBoolean(7);
if (Driver.ServerInfo.Index.Features.Supports(IndexFeatures.Clustered))
{
index.IsClustered = reader.GetByte(5) == 1;
}
index.FillFactor = reader.GetByte(9);
if (!reader.IsDBNull(15) && reader.GetBoolean(15))
{
index.Where = SqlDml.Native(reader.GetString(16));
}
// Index is a part of unique constraint
if (reader.GetBoolean(8))
{
uniqueConstraint = ((Table)table.Table).CreateUniqueConstraint(indexName);
if (index.IsClustered &&
Driver.ServerInfo.UniqueConstraint.Features.Supports(UniqueConstraintFeatures.Clustered))
{
uniqueConstraint.IsClustered = true;
}
}
}
}
}
// Column is a part of a primary index
if (reader.GetBoolean(6))
{
primaryKey.Columns.Add((TableColumn)table.GetColumn(columnId));
}
else
{
// Column is a part of unique constraint
if (reader.GetBoolean(8))
{
uniqueConstraint.Columns.Add((TableColumn)table.GetColumn(columnId));
}
if (index != null)
{
// Column is non key column
if (reader.GetBoolean(14))
{
index.NonkeyColumns.Add(table.GetColumn(columnId));
}
else
{
_ = index.CreateIndexColumn(table.GetColumn(columnId), !reader.GetBoolean(13));
}
}
}
}
