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 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 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)));
}
}
}
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()
{
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 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));
}
}
}
}
