/// <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; } } } } } } }