private void GetIndexes()
{
foreach (var table in _databaseModel.Tables)
{
using (var indexInfo = _connection.CreateCommand())
{
indexInfo.CommandText = $"PRAGMA index_list(\"{table.Name.Replace("\"", "\"\"")}\");";
using (var reader = indexInfo.ExecuteReader())
{
while (reader.Read())
{
var index = new IndexModel
{
Name = reader.GetValueOrDefault<string>("name"),
Table = table,
IsUnique = reader.GetValueOrDefault<bool>("unique")
};
Logger.LogTrace(SqliteDesignStrings
.FoundIndex(index.Name, table.Name, index.IsUnique));
table.Indexes.Add(index);
}
}
foreach (var index in table.Indexes)
{
var indexColumns = _connection.CreateCommand();
indexColumns.CommandText = $"PRAGMA index_info(\"{index.Name.Replace("\"", "\"\"")}\");";
index.IndexColumns = new List<IndexColumnModel>();
using (var reader = indexColumns.ExecuteReader())
{
while (reader.Read())
{
var columnName = reader.GetValueOrDefault<string>("name");
var indexOrdinal = reader.GetValueOrDefault<int>("seqno");
Logger.LogTrace(SqliteDesignStrings.FoundIndexColumn(
index.Name, table.Name, columnName, indexOrdinal));
if (string.IsNullOrEmpty(columnName))
{
Logger.LogWarning(SqliteDesignStrings
.ColumnNameEmptyOnIndex(index.Name, table.Name));
continue;
}
var column = _tableColumns[ColumnKey(index.Table, columnName)];
var indexColumn = new IndexColumnModel
{
Ordinal = indexOrdinal,
Column = column
};
index.IndexColumns.Add(indexColumn);
}
}
}
}
}
}
private void GetIndexes()
{
var command = _connection.CreateCommand();
command.CommandText = @"SELECT
object_schema_name(i.object_id) AS [schema_name],
object_name(i.object_id) AS [table_name],
i.name AS [index_name],
i.is_unique,
c.name AS [column_name],
i.type_desc,
ic.key_ordinal
FROM sys.indexes i
INNER JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
INNER JOIN sys.columns c ON ic.object_id = c.object_id AND c.column_id = ic.column_id
INNER JOIN sys.tables t ON t.object_id = i.object_id
WHERE object_schema_name(i.object_id) <> 'sys'
AND object_name(i.object_id) <> '" + HistoryRepository.DefaultTableName + @"'" +
TemporalTableWhereClause + @"
ORDER BY object_schema_name(i.object_id), object_name(i.object_id), i.name, ic.key_ordinal";
using (var reader = command.ExecuteReader())
{
IndexModel index = null;
while (reader.Read())
{
var schemaName = reader.GetValueOrDefault<string>("schema_name");
var tableName = reader.GetValueOrDefault<string>("table_name");
var indexName = reader.GetValueOrDefault<string>("index_name");
var isUnique = reader.GetValueOrDefault<bool>("is_unique");
var typeDesc = reader.GetValueOrDefault<string>("type_desc");
var columnName = reader.GetValueOrDefault<string>("column_name");
var indexOrdinal = reader.GetValueOrDefault<byte>("key_ordinal");
Logger.LogTrace(SqlServerDesignStrings.FoundIndexColumn(
schemaName, tableName, indexName, isUnique, typeDesc, columnName, indexOrdinal));
if (!_tableSelectionSet.Allows(schemaName, tableName))
{
Logger.LogTrace(SqlServerDesignStrings.IndexColumnNotInSelectionSet(
columnName, indexName, schemaName, tableName));
continue;
}
if (string.IsNullOrEmpty(indexName))
{
Logger.LogWarning(SqlServerDesignStrings.IndexNameEmpty(schemaName, tableName));
continue;
}
Debug.Assert(index == null || index.Table != null);
if (index == null
|| index.Name != indexName
|| index.Table.Name != tableName
|| index.Table.SchemaName != schemaName)
{
TableModel table;
if (!_tables.TryGetValue(TableKey(tableName, schemaName), out table))
{
Logger.LogWarning(
SqlServerDesignStrings.UnableToFindTableForIndex(indexName, schemaName, tableName));
continue;
}
index = new IndexModel
{
Table = table,
Name = indexName,
IsUnique = isUnique
};
index.SqlServer().IsClustered = typeDesc == "CLUSTERED";
table.Indexes.Add(index);
}
ColumnModel column;
if (string.IsNullOrEmpty(columnName))
{
Logger.LogWarning(
SqlServerDesignStrings.ColumnNameEmptyOnIndex(
schemaName, tableName, indexName));
}
else if (!_tableColumns.TryGetValue(ColumnKey(index.Table, columnName), out column))
{
Logger.LogWarning(
SqlServerDesignStrings.UnableToFindColumnForIndex(
indexName, columnName, schemaName, tableName));
}
else
{
var indexColumn = new IndexColumnModel
{
Index = index,
Column = column,
Ordinal = indexOrdinal
};
index.IndexColumns.Add(indexColumn);
}
}
}
}