/// <summary>
/// This is an internal API that supports the Entity Framework Core infrastructure and not subject to
/// the same compatibility standards as public APIs. It may be changed or removed without notice in
/// any release. You should only use it directly in your code with extreme caution and knowing that
/// doing so can result in application failures when updating to a new Entity Framework Core release.
/// </summary>
protected virtual IndexBuilder VisitUniqueConstraint(
[NotNull] EntityTypeBuilder builder, [NotNull] DatabaseUniqueConstraint uniqueConstraint)
{
Check.NotNull(builder, nameof(builder));
Check.NotNull(uniqueConstraint, nameof(uniqueConstraint));
var unmappedColumns = uniqueConstraint.Columns
.Where(c => _unmappedColumns.Contains(c))
.Select(c => c.Name)
.ToList();
if (unmappedColumns.Count > 0)
{
_reporter.WriteWarning(
DesignStrings.UnableToScaffoldIndexMissingProperty(
uniqueConstraint.Name,
string.Join(CultureInfo.CurrentCulture.TextInfo.ListSeparator, unmappedColumns)));
return(null);
}
var propertyNames = uniqueConstraint.Columns.Select(GetPropertyName).ToArray();
var indexBuilder = builder.HasIndex(propertyNames, uniqueConstraint.Name).IsUnique();
indexBuilder.Metadata.AddAnnotations(uniqueConstraint.GetAnnotations());
return(indexBuilder);
}
private void GetUniqueConstraints()
{
var command = _connection.CreateCommand();
command.CommandText =
"SHOW " +
" CONSTRAINTCOLUMNS " +
"WHERE " +
" ConstraintType = 'UNIQUE' AND TableName <> '" + HistoryRepository.DefaultTableName + "' " +
"ORDER BY " +
" TableName, ConstraintName, ColumnOrdinal";
using (var reader = command.ExecuteReader())
{
DatabaseUniqueConstraint uniqueConstraint = null;
while (reader.Read())
{
var schemaName = "Jet";
var tableName = reader.GetValueOrDefault <string>("TableName");
var indexName = reader.GetValueOrDefault <string>("ConstraintName");
var typeDesc = reader.GetValueOrDefault <string>("ConstraintType");
var columnName = reader.GetValueOrDefault <string>("ColumnName");
// ReSharper disable once UnusedVariable
var indexOrdinal = reader.GetValueOrDefault <int>("ColumnOrdinal");
Debug.Assert(uniqueConstraint == null || uniqueConstraint.Table != null);
if (uniqueConstraint == null ||
uniqueConstraint.Name != indexName
// ReSharper disable once PossibleNullReferenceException
|| uniqueConstraint.Table.Name != tableName ||
uniqueConstraint.Table.Schema != schemaName)
{
if (!_tables.TryGetValue(SchemaQualifiedKey(tableName, schemaName), out var table))
{
continue;
}
Logger.UniqueConstraintFound(indexName, DisplayName(schemaName, tableName));
uniqueConstraint = new DatabaseUniqueConstraint
{
Table = table,
Name = indexName
};
if (typeDesc == "CLUSTERED")
{
uniqueConstraint[JetAnnotationNames.Clustered] = true;
}
table.UniqueConstraints.Add(uniqueConstraint);
}
if (_tableColumns.TryGetValue(ColumnKey(uniqueConstraint.Table, columnName), out var column))
{
uniqueConstraint.Columns.Add(column);
}
}
}
}
private IEnumerable <DatabaseUniqueConstraint> GetUniqueConstraints(
DbConnection connection,
string table,
IList <DatabaseColumn> columns)
{
using var command1 = connection.CreateCommand();
command1.CommandText = new StringBuilder()
.AppendLine("SELECT \"name\"")
.AppendLine("FROM pragma_index_list(@table)")
.AppendLine("WHERE \"origin\" = 'u'")
.AppendLine("ORDER BY \"seq\";")
.ToString();
var parameter1 = command1.CreateParameter();
parameter1.ParameterName = "@table";
parameter1.Value = table;
command1.Parameters.Add(parameter1);
using var reader1 = command1.ExecuteReader();
while (reader1.Read())
{
var uniqueConstraint = new DatabaseUniqueConstraint();
var name = reader1.GetString(0);
if (!name.StartsWith("sqlite_", StringComparison.Ordinal))
{
uniqueConstraint.Name = name;
}
_logger.UniqueConstraintFound(name, table);
using (var command2 = connection.CreateCommand())
{
command2.CommandText = new StringBuilder()
.AppendLine("SELECT \"name\"")
.AppendLine("FROM pragma_index_info(@index)")
.AppendLine("ORDER BY \"seqno\";")
.ToString();
var parameter2 = command2.CreateParameter();
parameter2.ParameterName = "@index";
parameter2.Value = name;
command2.Parameters.Add(parameter2);
using var reader2 = command2.ExecuteReader();
while (reader2.Read())
{
var columnName = reader2.GetString(0);
var column = columns.FirstOrDefault(c => c.Name == columnName)
?? columns.FirstOrDefault(
c => c.Name.Equals(columnName, StringComparison.OrdinalIgnoreCase));
Debug.Assert(column != null, "column is null.");
uniqueConstraint.Columns.Add(column);
}
}
yield return(uniqueConstraint);
}
}
private void GetUniqueConstraints(TSqlTable table, DatabaseModel dbModel)
{
var dbTable = dbModel.Tables
.Single(t => t.Name == table.Name.Parts[1] &&
t.Schema == table.Name.Parts[0]);
var uqs = table.UniqueConstraints.ToList();
foreach (var uq in uqs)
{
var uniqueConstraint = new DatabaseUniqueConstraint
{
Name = uq.Name.HasName ? uq.Name.Parts[1] : null,
Table = dbTable
};
if (uq.Clustered)
{
uniqueConstraint["SqlServer:Clustered"] = true;
}
foreach (var uqCol in uq.Columns)
{
var dbCol = dbTable.Columns
.Single(c => c.Name == uqCol.Name.Parts[2]);
uniqueConstraint.Columns.Add(dbCol);
}
dbTable.UniqueConstraints.Add(uniqueConstraint);
}
}
public DatabaseIndexData(DatabaseUniqueConstraint index)
{
Table = index.Table;
Name = index.Name;
Columns = index.Columns;
IsUnique = true;
}
private void GetUniqueConstraints(DbConnection connection, DatabaseTable table)
{
using var command1 = connection.CreateCommand();
command1.CommandText = new StringBuilder()
.AppendLine("SELECT \"name\"")
.AppendLine("FROM pragma_index_list(@table)")
.AppendLine("WHERE \"origin\" = 'u'")
.AppendLine("ORDER BY \"seq\";")
.ToString();
var parameter1 = command1.CreateParameter();
parameter1.ParameterName = "@table";
parameter1.Value = table.Name;
command1.Parameters.Add(parameter1);
using var reader1 = command1.ExecuteReader();
while (reader1.Read())
{
var constraintName = reader1.GetString(0);
var uniqueConstraint = new DatabaseUniqueConstraint
{
Table = table,
Name = constraintName.StartsWith("sqlite_", StringComparison.Ordinal) ? string.Empty : constraintName
};
_logger.UniqueConstraintFound(constraintName, table.Name);
using (var command2 = connection.CreateCommand())
{
command2.CommandText = new StringBuilder()
.AppendLine("SELECT \"name\"")
.AppendLine("FROM pragma_index_info(@index)")
.AppendLine("ORDER BY \"seqno\";")
.ToString();
var parameter2 = command2.CreateParameter();
parameter2.ParameterName = "@index";
parameter2.Value = constraintName;
command2.Parameters.Add(parameter2);
using var reader2 = command2.ExecuteReader();
while (reader2.Read())
{
var columnName = reader2.GetString(0);
var column = table.Columns.FirstOrDefault(c => c.Name == columnName)
?? table.Columns.FirstOrDefault(
c => c.Name !.Equals(columnName, StringComparison.OrdinalIgnoreCase));
Check.DebugAssert(column != null, "column is null.");
uniqueConstraint.Columns.Add(column);
}
}
table.UniqueConstraints.Add(uniqueConstraint);
}
}
private void GetUniqueConstraints()
{
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],
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 i.is_hypothetical = 0
AND i.is_unique_constraint = 1
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())
{
DatabaseUniqueConstraint uniqueConstraint = 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 typeDesc = reader.GetValueOrDefault <string>("type_desc");
var columnName = reader.GetValueOrDefault <string>("column_name");
var indexOrdinal = reader.GetValueOrDefault <byte>("key_ordinal");
Logger.IndexColumnFound(
DisplayName(schemaName, tableName), indexName, true, columnName, indexOrdinal);
if (!_tableSelectionSet.Allows(schemaName, tableName))
{
Logger.IndexColumnSkipped(columnName, indexName, DisplayName(schemaName, tableName));
continue;
}
if (string.IsNullOrEmpty(indexName))
{
Logger.IndexNotNamedWarning(DisplayName(schemaName, tableName));
continue;
}
Debug.Assert(uniqueConstraint == null || uniqueConstraint.Table != null);
if (uniqueConstraint == null ||
uniqueConstraint.Name != indexName
// ReSharper disable once PossibleNullReferenceException
|| uniqueConstraint.Table.Name != tableName ||
uniqueConstraint.Table.Schema != schemaName)
{
DatabaseTable table;
if (!_tables.TryGetValue(SchemaQualifiedKey(tableName, schemaName), out table))
{
Logger.IndexTableMissingWarning(indexName, DisplayName(schemaName, tableName));
continue;
}
uniqueConstraint = new DatabaseUniqueConstraint
{
Table = table,
Name = indexName
};
if (typeDesc == "CLUSTERED")
{
uniqueConstraint[OracleAnnotationNames.Clustered] = true;
table.PrimaryKey?.RemoveAnnotation(OracleAnnotationNames.Clustered);
}
table.UniqueConstraints.Add(uniqueConstraint);
}
DatabaseColumn column;
if (string.IsNullOrEmpty(columnName))
{
Logger.IndexColumnNotNamedWarning(indexName, DisplayName(schemaName, tableName));
}
else if (!_tableColumns.TryGetValue(ColumnKey(uniqueConstraint.Table, columnName), out column))
{
Logger.IndexColumnsNotMappedWarning(indexName, new[] { columnName });
}
else
{
uniqueConstraint.Columns.Add(column);
}
}
}
}
/// <summary>
/// This API supports the Entity Framework Core infrastructure and is not intended to be used
/// directly from your code. This API may change or be removed in future releases.
/// </summary>
protected virtual IndexBuilder VisitUniqueConstraint(EntityTypeBuilder builder, DatabaseUniqueConstraint uniqueConstraint)
{
if (builder == null)
{
throw new ArgumentNullException(nameof(builder));
}
if (uniqueConstraint == null)
{
throw new ArgumentNullException(nameof(uniqueConstraint));
}
var unmappedColumns = uniqueConstraint.Columns
.Where(c => _unmappedColumns.Contains(c))
.Select(c => c.Name)
.ToList();
if (unmappedColumns.Any())
{
_reporter.WriteWarning(
DesignStrings.UnableToScaffoldIndexMissingProperty(
uniqueConstraint.Name,
string.Join(CultureInfo.CurrentCulture.TextInfo.ListSeparator, unmappedColumns)));
return(null);
}
var propertyNames = uniqueConstraint.Columns.Select(GetPropertyName).ToArray();
var indexBuilder = builder.HasIndex(propertyNames).IsUnique();
if (!string.IsNullOrEmpty(uniqueConstraint.Name))
{
indexBuilder.HasName(uniqueConstraint.Name);
}
indexBuilder.Metadata.AddAnnotations(uniqueConstraint.GetAnnotations());
return(indexBuilder);
}
void GetConstraints(
NpgsqlConnection connection,
IReadOnlyList <DatabaseTable> tables,
string tableFilter,
out List <uint> constraintIndexes)
{
constraintIndexes = new List <uint>();
var getConstraints = @"
SELECT
ns.nspname, cls.relname, conname, contype, conkey, conindid,
frnns.nspname AS fr_nspname, frncls.relname AS fr_relname, confkey, confdeltype
FROM pg_class AS cls
JOIN pg_namespace AS ns ON ns.oid = cls.relnamespace
JOIN pg_constraint as con ON con.conrelid = cls.oid
LEFT OUTER JOIN pg_class AS frncls ON frncls.oid = con.confrelid
LEFT OUTER JOIN pg_namespace as frnns ON frnns.oid = frncls.relnamespace
WHERE
cls.relkind = 'r' AND
ns.nspname NOT IN ('pg_catalog', 'information_schema')
AND
con.contype IN ('p', 'f', 'u')
" + tableFilter;
var command = connection.CreateCommand();
command.CommandText = getConstraints;
using (var reader = command.ExecuteReader())
{
var tableGroups = reader.Cast <DbDataRecord>()
.GroupBy(
ddr => (tableSchema: ddr.GetValueOrDefault <string>("nspname"),
tableName: ddr.GetValueOrDefault <string>("relname")));
foreach (var tableGroup in tableGroups)
{
var tableSchema = tableGroup.Key.tableSchema;
var tableName = tableGroup.Key.tableName;
var table = tables.Single(t => t.Schema == tableSchema && t.Name == tableName);
// Primary keys
foreach (var primaryKeyRecord in tableGroup
.Where(ddr => ddr.GetValueOrDefault <char>("contype") == 'p'))
{
var primaryKey = new DatabasePrimaryKey
{
Table = table,
Name = primaryKeyRecord.GetValueOrDefault <string>("conname")
};
var pkColumnIndices = primaryKeyRecord.GetValueOrDefault <short[]>("conkey");
foreach (var pkColumnIndex in pkColumnIndices)
{
primaryKey.Columns.Add(table.Columns[pkColumnIndex - 1]);
}
table.PrimaryKey = primaryKey;
}
// Foreign keys
foreach (var foreignKeyRecord in tableGroup
.Where(ddr => ddr.GetValueOrDefault <char>("contype") == 'f'))
{
var fkName = foreignKeyRecord.GetValueOrDefault <string>("conname");
var principalTableSchema = foreignKeyRecord.GetValueOrDefault <string>("fr_nspname");
var principalTableName = foreignKeyRecord.GetValueOrDefault <string>("fr_relname");
var onDeleteAction = foreignKeyRecord.GetValueOrDefault <char>("confdeltype");
var principalTable = tables.FirstOrDefault(
t => t.Schema == principalTableSchema &&
t.Name == principalTableName)
?? tables.FirstOrDefault(
t => t.Schema.Equals(principalTableSchema, StringComparison.OrdinalIgnoreCase) &&
t.Name.Equals(principalTableName, StringComparison.OrdinalIgnoreCase));
if (principalTable == null)
{
_logger.ForeignKeyReferencesMissingPrincipalTableWarning(
fkName,
DisplayName(table.Schema, table.Name),
DisplayName(principalTableSchema, principalTableName));
continue;
}
var foreignKey = new DatabaseForeignKey
{
Name = fkName,
Table = table,
PrincipalTable = principalTable,
OnDelete = ConvertToReferentialAction(onDeleteAction)
};
var columnIndices = foreignKeyRecord.GetValueOrDefault <short[]>("conkey");
var principalColumnIndices = foreignKeyRecord.GetValueOrDefault <short[]>("confkey");
if (columnIndices.Length != principalColumnIndices.Length)
{
throw new Exception("Got varying lengths for column and principal column indices");
}
var principalColumns = (List <DatabaseColumn>)principalTable.Columns;
for (var i = 0; i < columnIndices.Length; i++)
{
foreignKey.Columns.Add(table.Columns[columnIndices[i] - 1]);
foreignKey.PrincipalColumns.Add(principalColumns[principalColumnIndices[i] - 1]);
}
table.ForeignKeys.Add(foreignKey);
}
// Unique constraints
foreach (var record in tableGroup
.Where(ddr => ddr.GetValueOrDefault <char>("contype") == 'u')
.ToArray())
{
var name = record.GetValueOrDefault <string>("conname");
_logger.UniqueConstraintFound(name, DisplayName(tableSchema, tableName));
var uniqueConstraint = new DatabaseUniqueConstraint
{
Table = table,
Name = name
};
var columnIndices = record.GetValueOrDefault <short[]>("conkey");
foreach (var t in columnIndices)
{
uniqueConstraint.Columns.Add(table.Columns[t - 1]);
}
table.UniqueConstraints.Add(uniqueConstraint);
constraintIndexes.Add(record.GetValueOrDefault <uint>("conindid"));
}
}
}
}
private void GetUniqueConstraints()
{
var command = _connection.CreateCommand();
command.CommandText = @"SELECT
ix.[INDEX_NAME] AS [index_name],
NULL AS [schema_name],
ix.[TABLE_NAME] AS [table_name],
ix.[UNIQUE] AS is_unique,
ix.[COLUMN_NAME] AS [column_name],
ix.[ORDINAL_POSITION] AS [key_ordinal]
FROM INFORMATION_SCHEMA.INDEXES ix
WHERE ix.PRIMARY_KEY = 0
AND ix.[UNIQUE] = 1
AND (SUBSTRING(TABLE_NAME, 1,2) <> '__')
AND (SUBSTRING(ix.COLUMN_NAME, 1,5) <> '__sys')
ORDER BY ix.[TABLE_NAME], ix.[INDEX_NAME], ix.[ORDINAL_POSITION];";
using (var reader = command.ExecuteReader())
{
DatabaseUniqueConstraint uniqueConstraint = 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 columnName = reader.GetValueOrDefault <string>("column_name");
//var indexOrdinal = reader.GetValueOrDefault<byte>("key_ordinal");
//Logger.IndexColumnFound(
// tableName, indexName, true, columnName, indexOrdinal);
if (!_tableSelectionSet.Allows(tableName))
{
//Logger.IndexColumnSkipped(columnName, indexName, DisplayName(schemaName, tableName));
continue;
}
Debug.Assert(uniqueConstraint == null || uniqueConstraint.Table != null);
if (uniqueConstraint == null ||
uniqueConstraint.Name != indexName
// ReSharper disable once PossibleNullReferenceException
|| uniqueConstraint.Table.Name != tableName ||
uniqueConstraint.Table.Schema != schemaName)
{
var table = _tables[TableKey(tableName)];
uniqueConstraint = new DatabaseUniqueConstraint
{
Table = table,
Name = indexName
};
table.UniqueConstraints.Add(uniqueConstraint);
}
DatabaseColumn column;
if (!_tableColumns.TryGetValue(ColumnKey(uniqueConstraint.Table, columnName), out column))
{
//Logger.IndexColumnsNotMappedWarning(indexName, new[] { columnName });
}
else
{
uniqueConstraint.Columns.Add(column);
}
}
}
}
private void GetKeys(
DbConnection connection,
string tableFilter,
DatabaseModel databaseModel)
{
using (var command = connection.CreateCommand())
{
command.CommandText = new StringBuilder()
.AppendLine("SELECT")
.AppendLine(" t.tablespace_name,")
.AppendLine(" a.table_name,")
.AppendLine(" a.column_name,")
.AppendLine(" c.delete_rule,")
.AppendLine(" a.constraint_name,")
.AppendLine(" c.constraint_type")
.AppendLine("FROM all_cons_columns a")
.AppendLine("JOIN all_constraints c")
.AppendLine(" ON a.CONSTRAINT_NAME = c.CONSTRAINT_NAME")
.AppendLine("INNER JOIN user_tables t")
.AppendLine(" ON t.table_name = a.table_name ")
.AppendLine(tableFilter)
.AppendLine(" AND c.constraint_type IN ('P','U') ")
.ToString();
using (var reader = command.ExecuteReader())
{
var tableIndexGroups = reader.Cast <DbDataRecord>()
.GroupBy(
ddr => (tableSchema: ddr.GetValueOrDefault <string>("tablespace_name"),
tableName: ddr.GetValueOrDefault <string>("table_name")));
foreach (var tableIndexGroup in tableIndexGroups)
{
var tableSchema = tableIndexGroup.Key.tableSchema;
var tableName = tableIndexGroup.Key.tableName;
var table = databaseModel.Tables.Single(t => t.Schema == tableSchema && t.Name == tableName);
var primaryKeyGroups = tableIndexGroup
.Where(ddr => ddr.GetValueOrDefault <string>("constraint_type").Equals("P"))
.GroupBy(ddr => ddr.GetValueOrDefault <string>("constraint_name"))
.ToArray();
if (primaryKeyGroups.Length == 1)
{
var primaryKeyGroup = primaryKeyGroups[0];
_logger.PrimaryKeyFound(primaryKeyGroup.Key, DisplayName(tableSchema, tableName));
var primaryKey = new DatabasePrimaryKey
{
Table = table,
Name = primaryKeyGroup.Key
};
foreach (var dataRecord in primaryKeyGroup)
{
var columnName = dataRecord.GetValueOrDefault <string>("column_name");
var column = table.Columns.FirstOrDefault(c => c.Name == columnName)
?? table.Columns.FirstOrDefault(c => c.Name.Equals(columnName, StringComparison.OrdinalIgnoreCase));
primaryKey.Columns.Add(column);
}
table.PrimaryKey = primaryKey;
}
var uniqueConstraintGroups = tableIndexGroup
.Where(ddr => ddr.GetValueOrDefault <string>("constraint_type").Equals("U"))
.GroupBy(ddr => ddr.GetValueOrDefault <string>("constraint_name"))
.ToArray();
foreach (var uniqueConstraintGroup in uniqueConstraintGroups)
{
_logger.UniqueConstraintFound(uniqueConstraintGroup.Key, DisplayName(tableSchema, tableName));
var uniqueConstraint = new DatabaseUniqueConstraint
{
Table = table,
Name = uniqueConstraintGroup.Key
};
foreach (var dataRecord in uniqueConstraintGroup)
{
var columnName = dataRecord.GetValueOrDefault <string>("column_name");
var column = table.Columns.FirstOrDefault(c => c.Name == columnName)
?? table.Columns.FirstOrDefault(c => c.Name.Equals(columnName, StringComparison.OrdinalIgnoreCase));
uniqueConstraint.Columns.Add(column);
}
table.UniqueConstraints.Add(uniqueConstraint);
}
}
}
}
}
private void GetIndexes(DbConnection connection, IReadOnlyList <DatabaseTable> tables)
{
var indexTable = new DataTable();
using (var command = connection.CreateCommand())
{
command.CommandText = $@"SELECT * FROM `INFORMATION_SCHEMA.INDEXES` ORDER BY TABLE_NAME, INDEX_NAME";
using var reader = command.ExecuteReader();
indexTable.Load(reader);
}
var indexColumnsTable = new DataTable();
using (var command = connection.CreateCommand())
{
command.CommandText = "SELECT * FROM `INFORMATION_SCHEMA.INDEX_COLUMNS` ORDER BY TABLE_NAME, INDEX_NAME, ORDINAL_POSITION";
using var reader = command.ExecuteReader();
indexColumnsTable.Load(reader);
}
var groupedIndexColumns = indexColumnsTable.Rows.Cast <DataRow>()
.GroupBy(r => (TableName: r.GetValueOrDefault <string>("TABLE_NAME"), IndexName: r.GetValueOrDefault <string>("INDEX_NAME")))
.ToList();
foreach (DataRow indexRow in indexTable.Rows)
{
var tableName = indexRow.GetValueOrDefault <string>("TABLE_NAME");
var indexName = indexRow.GetValueOrDefault <string>("INDEX_NAME");
var indexType = indexRow.GetValueOrDefault <string>("INDEX_TYPE");
var nullable = indexRow.GetValueOrDefault <bool>("IS_NULLABLE");
var ignoresNulls = indexRow.GetValueOrDefault <bool>("IGNORES_NULLS");
var table = tables.FirstOrDefault(t => string.Equals(t.Name, tableName)) ??
tables.FirstOrDefault(t => string.Equals(t.Name, tableName, StringComparison.OrdinalIgnoreCase));
if (table != null)
{
var indexColumns = groupedIndexColumns.FirstOrDefault(g => g.Key == (tableName, indexName));
if (indexColumns?.Any() ?? false)
{
object indexOrKey = null;
if (indexType == "PRIMARY")
{
var primaryKey = new DatabasePrimaryKey
{
Table = table,
Name = indexName,
};
_logger.PrimaryKeyFound(indexName, tableName);
table.PrimaryKey = primaryKey;
indexOrKey = primaryKey;
}
else if (indexType == "UNIQUE" &&
!nullable)
{
var uniqueConstraint = new DatabaseUniqueConstraint
{
Table = table,
Name = indexName,
};
_logger.UniqueConstraintFound(indexName, tableName);
table.UniqueConstraints.Add(uniqueConstraint);
indexOrKey = uniqueConstraint;
}
else
{
var index = new DatabaseIndex
{
Table = table,
Name = indexName,
IsUnique = indexType == "UNIQUE",
};
_logger.IndexFound(indexName, tableName, index.IsUnique);
table.Indexes.Add(index);
indexOrKey = index;
}
foreach (var indexColumn in indexColumns)
{
var columnName = indexColumn.GetValueOrDefault <string>("COLUMN_NAME");
var descending = indexColumn.GetValueOrDefault <bool>("IS_DESCENDING");
var column = table.Columns.FirstOrDefault(c => c.Name == columnName) ??
table.Columns.FirstOrDefault(c => string.Equals(c.Name, columnName, StringComparison.OrdinalIgnoreCase));
if (column != null)
{
switch (indexOrKey)
{
case DatabasePrimaryKey primaryKey:
primaryKey.Columns.Add(column);
break;
case DatabaseUniqueConstraint uniqueConstraint:
uniqueConstraint.Columns.Add(column);
break;
case DatabaseIndex index:
index.Columns.Add(column);
break;
}
}
}
}
}
}
}
public UniqueConstraint(DatabaseUniqueConstraint source)
{
Name = source.Name;
Columns = source.Columns.Select(x => x.Name).ToList();
}
private void GetIndexes(DbConnection connection, IReadOnlyList <DatabaseTable> tables)
{
var indexTable = new DataTable();
using (var command = connection.CreateCommand())
{
command.CommandText = $@"SELECT * FROM `INFORMATION_SCHEMA.INDEXES` ORDER BY TABLE_NAME, INDEX_NAME";
using var reader = command.ExecuteReader();
indexTable.Load(reader);
}
var indexColumnsTable = new DataTable();
using (var command = connection.CreateCommand())
{
command.CommandText = "SELECT * FROM `INFORMATION_SCHEMA.INDEX_COLUMNS` ORDER BY TABLE_NAME, INDEX_NAME, ORDINAL_POSITION";
using var reader = command.ExecuteReader();
indexColumnsTable.Load(reader);
}
var groupedIndexColumns = indexColumnsTable.Rows.Cast <DataRow>()
.GroupBy(r => (TableName: r.GetValueOrDefault <string>("TABLE_NAME"), IndexName: r.GetValueOrDefault <string>("INDEX_NAME")))
.ToList();
foreach (DataRow indexRow in indexTable.Rows)
{
var tableName = indexRow.GetValueOrDefault <string>("TABLE_NAME");
var indexName = indexRow.GetValueOrDefault <string>("INDEX_NAME");
var indexType = indexRow.GetValueOrDefault <string>("INDEX_TYPE");
var nullable = indexRow.GetValueOrDefault <bool>("IS_NULLABLE");
var ignoresNulls = indexRow.GetValueOrDefault <bool>("IGNORES_NULLS");
var table = tables.FirstOrDefault(t => string.Equals(t.Name, tableName)) ??
tables.FirstOrDefault(t => string.Equals(t.Name, tableName, StringComparison.OrdinalIgnoreCase));
if (table != null)
{
var indexColumns = groupedIndexColumns.FirstOrDefault(g => g.Key == (tableName, indexName));
if (indexColumns?.Any() ?? false)
{
object indexOrKey = null;
if (indexType == "PRIMARY")
{
var primaryKey = new DatabasePrimaryKey
{
Table = table,
Name = indexName,
};
_logger.PrimaryKeyFound(indexName, tableName);
table.PrimaryKey = primaryKey;
indexOrKey = primaryKey;
}
else
{
var isUnique = indexType == "UNIQUE";
if (isUnique &&
!nullable)
{
var uniqueConstraint = new DatabaseUniqueConstraint
{
Table = table,
Name = indexName,
};
_logger.UniqueConstraintFound(indexName, tableName);
table.UniqueConstraints.Add(uniqueConstraint);
indexOrKey = uniqueConstraint;
}
else
{
// In contrast to SQL Standard, MS Access will implicitly create an index for every FK
// constraint.
// According to https://docs.microsoft.com/en-us/office/client-developer/access/desktop-database-reference/constraint-clause-microsoft-access-sql,
// this behavior can be disabled, but manuall creating an index with the same name as an
// FK would still results in a runtime error.
// We therefore skip indexes with the same name as existing FKs.
if (table.ForeignKeys.Any(fk => fk.Name == indexName))
{
_logger.IndexSkipped(indexName, tableName, isUnique);
continue;
}
var index = new DatabaseIndex
{
Table = table,
Name = indexName,
IsUnique = isUnique,
};
_logger.IndexFound(indexName, tableName, index.IsUnique);
table.Indexes.Add(index);
indexOrKey = index;
}
}
foreach (var indexColumn in indexColumns)
{
var columnName = indexColumn.GetValueOrDefault <string>("COLUMN_NAME");
var descending = indexColumn.GetValueOrDefault <bool>("IS_DESCENDING");
var column = table.Columns.FirstOrDefault(c => c.Name == columnName) ??
table.Columns.FirstOrDefault(c => string.Equals(c.Name, columnName, StringComparison.OrdinalIgnoreCase));
if (column != null)
{
switch (indexOrKey)
{
case DatabasePrimaryKey primaryKey:
primaryKey.Columns.Add(column);
break;
case DatabaseUniqueConstraint uniqueConstraint:
uniqueConstraint.Columns.Add(column);
break;
case DatabaseIndex index:
index.Columns.Add(column);
break;
}
}
}
}
}
}
}
