/// <remarks>
/// Primary keys are handled as in <see cref="GetConstraints"/>, not here
/// </remarks>
private void GetPrimaryKeys()
{
foreach (var x in _tables)
{
using (var command =
new MySqlCommand(string.Format(GetPrimaryQuery, _connection.Database, x.Key),
_connection))
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
try
{
var index = new DatabasePrimaryKey
{
Table = x.Value,
Name = reader.GetString(0)
};
foreach (var column in reader.GetString(2).Split(','))
{
index.Columns.Add(x.Value.Columns.Single(y => y.Name == column));
}
x.Value.PrimaryKey = index;
}
catch (Exception ex)
{
Logger.LogError(ex, "Error assigning primary key for {table}.", x.Key);
}
}
}
}
}
/// <remarks>
/// Primary keys are handled as in <see cref="GetConstraints"/>, not here
/// </remarks>
private void GetPrimaryKeys(
DbConnection connection,
IReadOnlyList <DatabaseTable> tables)
{
foreach (var table in tables)
{
using (var command = connection.CreateCommand())
{
command.CommandText = string.Format(GetPrimaryQuery, connection.Database, table.Name);
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
try
{
var index = new DatabasePrimaryKey {
Table = table, Name = reader.GetString(0)
};
foreach (var column in reader.GetString(2).Split(','))
{
index.Columns.Add(table.Columns.Single(y => y.Name == column));
}
table.PrimaryKey = index;
}
catch (Exception ex)
{
_logger.Logger.LogError(ex, "Error assigning primary key for {table}.", table.Name);
}
}
}
}
}
}
private void GetPrimaryKeys(DbConnection connection, IReadOnlyList <DatabaseTable> tables)
{
foreach (var x in tables)
{
using (var command = connection.CreateCommand())
{
command.CommandText = string.Format(GetPrimaryQuery, x.Name);
using (var reader = command.ExecuteReader())
{
DatabasePrimaryKey index = null;
while (reader.Read())
{
if (index == null)
{
index = new DatabasePrimaryKey
{
Table = x,
Name = reader.GetString(0).Trim()
};
}
index.Columns.Add(x.Columns.Single(y => y.Name == reader.GetString(1).Trim()));
x.PrimaryKey = index;
}
}
}
}
}
private void GetPrimaryKeyV3(LinqToEdmx.Model.StorageV3.EntityTypeStore table, DatabaseTable dbTable)
{
if (table.Key.PropertyRefs.Count() == 0)
{
return;
}
var pk = table.Key;
var primaryKey = new DatabasePrimaryKey
{
// We do not have information about the primary key name in the model.
// So we're making it up
Name = string.Concat("PK_", dbTable.Name),
Table = dbTable
};
foreach (var pkCol in table.Key.PropertyRefs)
{
var dbCol = dbTable.Columns
.Single(c => c.Name == pkCol.Name);
primaryKey.Columns.Add(dbCol);
}
dbTable.PrimaryKey = primaryKey;
}
private void GetPrimaryKey(TSqlTable table, DatabaseTable dbTable)
{
if (table.PrimaryKeyConstraints.Count() == 0)
{
return;
}
var pk = table.PrimaryKeyConstraints.First();
var primaryKey = new DatabasePrimaryKey
{
Name = pk.Name.HasName ? pk.Name.Parts[1] : null,
Table = dbTable
};
if (!pk.Clustered)
{
primaryKey["SqlServer:Clustered"] = false;
}
foreach (var pkCol in pk.Columns)
{
var dbCol = dbTable.Columns
.Single(c => c.Name == pkCol.Name.Parts[2]);
primaryKey.Columns.Add(dbCol);
}
dbTable.PrimaryKey = primaryKey;
}
private DatabasePrimaryKey GetPrimaryKey(DbConnection connection, string table, IList <DatabaseColumn> columns)
{
var primaryKey = new DatabasePrimaryKey();
using (var command = connection.CreateCommand())
{
command.CommandText = new StringBuilder()
.AppendLine("SELECT \"name\"")
.AppendLine("FROM pragma_index_list(@table)")
.AppendLine("WHERE \"origin\" = 'pk'")
.AppendLine("ORDER BY \"seq\";")
.ToString();
var parameter = command.CreateParameter();
parameter.ParameterName = "@table";
parameter.Value = table;
command.Parameters.Add(parameter);
var name = (string)command.ExecuteScalar();
if (name == null)
{
return(GetRowidPrimaryKey(connection, table, columns));
}
if (!name.StartsWith("sqlite_", StringComparison.Ordinal))
{
primaryKey.Name = name;
}
_logger.PrimaryKeyFound(name, table);
command.CommandText = new StringBuilder()
.AppendLine("SELECT \"name\"")
.AppendLine("FROM pragma_index_info(@index)")
.AppendLine("ORDER BY \"seqno\";")
.ToString();
parameter.ParameterName = "@index";
parameter.Value = name;
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
var columnName = reader.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.");
primaryKey.Columns.Add(column);
}
}
}
return(primaryKey);
}
static RelationalDatabaseModelFactoryTest()
{
Database = new DatabaseModel();
Table = new DatabaseTable(Database, "Foo");
IdColumn = new DatabaseColumn(Table, "Id", "int");
IdPrimaryKey = new DatabasePrimaryKey(Table, "IdPrimaryKey")
{
Columns = { IdColumn }
};
}
private void GetPrimaryKey(DbConnection connection, DatabaseTable table)
{
using var command = connection.CreateCommand();
command.CommandText = new StringBuilder()
.AppendLine("SELECT \"name\"")
.AppendLine("FROM pragma_index_list(@table)")
.AppendLine("WHERE \"origin\" = 'pk'")
.AppendLine("ORDER BY \"seq\";")
.ToString();
var parameter = command.CreateParameter();
parameter.ParameterName = "@table";
parameter.Value = table.Name;
command.Parameters.Add(parameter);
var name = (string)command.ExecuteScalar();
if (name == null)
{
GetRowidPrimaryKey(connection, table);
return;
}
var primaryKey = new DatabasePrimaryKey
{
Table = table,
Name = name.StartsWith("sqlite_", StringComparison.Ordinal) ? string.Empty : name
};
_logger.PrimaryKeyFound(name, table.Name);
command.CommandText = new StringBuilder()
.AppendLine("SELECT \"name\"")
.AppendLine("FROM pragma_index_info(@index)")
.AppendLine("ORDER BY \"seqno\";")
.ToString();
parameter.ParameterName = "@index";
parameter.Value = name;
using var reader = command.ExecuteReader();
while (reader.Read())
{
var columnName = reader.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.");
primaryKey.Columns.Add(column);
}
table.PrimaryKey = primaryKey;
}
/// <remarks>
/// Primary keys are handled as in <see cref="GetConstraints"/>, not here
/// </remarks>
protected virtual void GetPrimaryKeys(
DbConnection connection,
IReadOnlyList <DatabaseTable> tables)
{
foreach (var table in tables)
{
using (var command = connection.CreateCommand())
{
command.CommandText = string.Format(GetPrimaryQuery, connection.Database, table.Name);
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
try
{
var key = new DatabasePrimaryKey
{
Table = table,
Name = reader.GetValueOrDefault <string>("INDEX_NAME"),
};
foreach (var column in reader.GetValueOrDefault <string>("COLUMNS").Split(','))
{
key.Columns.Add(table.Columns.Single(y => y.Name == column));
}
var prefixLengths = reader.GetValueOrDefault <string>("SUB_PARTS")
.Split(',')
.Select(int.Parse)
.ToArray();
if (prefixLengths.Length > 1 ||
prefixLengths.Length == 1 && prefixLengths[0] > 0)
{
key[MySqlAnnotationNames.IndexPrefixLength] = prefixLengths;
}
table.PrimaryKey = key;
}
catch (Exception ex)
{
_logger.Logger.LogError(ex, "Error assigning primary key for {table}.", table.Name);
}
}
}
}
}
}
static RelationalScaffoldingModelFactoryTest()
{
Database = new DatabaseModel();
Table = new DatabaseTable {
Database = Database, Name = "Foo"
};
IdColumn = new DatabaseColumn
{
Table = Table,
Name = "Id",
StoreType = "int"
};
IdPrimaryKey = new DatabasePrimaryKey
{
Table = Table,
Name = "IdPrimaryKey",
Columns = { IdColumn }
};
}
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"));
}
}
}
}
public PrimaryKey(DatabasePrimaryKey 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 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;
}
}
}
}
}
}
}
private void GetPrimaryKeys()
{
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_primary_key = 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())
{
DatabasePrimaryKey primaryKey = 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(primaryKey == null || primaryKey.Table != null);
if (primaryKey == null ||
primaryKey.Name != indexName
// ReSharper disable once PossibleNullReferenceException
|| primaryKey.Table.Name != tableName ||
primaryKey.Table.Schema != schemaName)
{
DatabaseTable table;
if (!_tables.TryGetValue(SchemaQualifiedKey(tableName, schemaName), out table))
{
Logger.IndexTableMissingWarning(indexName, DisplayName(schemaName, tableName));
continue;
}
primaryKey = new DatabasePrimaryKey
{
Table = table,
Name = indexName
};
if (typeDesc == "NONCLUSTERED")
{
primaryKey[OracleAnnotationNames.Clustered] = false;
}
Debug.Assert(table.PrimaryKey == null);
table.PrimaryKey = primaryKey;
}
DatabaseColumn column;
if (string.IsNullOrEmpty(columnName))
{
Logger.IndexColumnNotNamedWarning(indexName, DisplayName(schemaName, tableName));
}
else if (!_tableColumns.TryGetValue(ColumnKey(primaryKey.Table, columnName), out column))
{
Logger.IndexColumnsNotMappedWarning(indexName, new[] { columnName });
}
else
{
primaryKey.Columns.Add(column);
}
}
}
}
private void GetPrimaryKeys()
{
var command = _connection.CreateCommand();
command.CommandText =
"SHOW " +
" CONSTRAINTCOLUMNS " +
"WHERE " +
" ConstraintType = 'PRIMARY KEY' AND TableName <> '" + HistoryRepository.DefaultTableName + "' " +
"ORDER BY " +
" TableName, ConstraintName, ColumnOrdinal";
using (var reader = command.ExecuteReader())
{
DatabasePrimaryKey primaryKey = 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");
var indexOrdinal = reader.GetValueOrDefault <int>("ColumnOrdinal");
Logger.IndexColumnFound(
DisplayName(schemaName, tableName), indexName, true, columnName, indexOrdinal);
Debug.Assert(primaryKey == null || primaryKey.Table != null);
if (primaryKey == null ||
primaryKey.Name != indexName
// ReSharper disable once PossibleNullReferenceException
|| primaryKey.Table.Name != tableName ||
primaryKey.Table.Schema != schemaName)
{
if (!_tables.TryGetValue(SchemaQualifiedKey(tableName, schemaName), out var table))
{
Logger.IndexTableMissingWarning(indexName, DisplayName(schemaName, tableName));
continue;
}
primaryKey = new DatabasePrimaryKey
{
Table = table,
Name = indexName
};
if (typeDesc == "NONCLUSTERED")
{
primaryKey[JetAnnotationNames.Clustered] = false;
}
Debug.Assert(table.PrimaryKey == null);
table.PrimaryKey = primaryKey;
}
if (_tableColumns.TryGetValue(ColumnKey(primaryKey.Table, columnName), out var column))
{
primaryKey.Columns.Add(column);
}
}
}
}
protected virtual void GetPrimaryKeys(
DbConnection connection,
IReadOnlyList <DatabaseTable> tables)
{
foreach (var table in tables)
{
using (var command = connection.CreateCommand())
{
command.CommandText = string.Format(GetPrimaryQuery, connection.Database, table.Name);
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
try
{
var key = new DatabasePrimaryKey
{
Table = table,
Name = reader.GetValueOrDefault <string>("INDEX_NAME"),
};
foreach (var column in reader.GetValueOrDefault <string>("COLUMNS").Split(','))
{
key.Columns.Add(table.Columns.Single(y => y.Name == column));
}
var prefixLengths = reader.GetValueOrDefault <string>("SUB_PARTS")
.Split(',')
.Select(int.Parse)
.ToArray();
if (prefixLengths.Length > 1 ||
prefixLengths.Length == 1 && prefixLengths[0] > 0)
{
key[MySqlAnnotationNames.IndexPrefixLength] = prefixLengths;
}
var firstKeyColumn = key.Columns[0];
if (key.Columns.Count == 1 &&
firstKeyColumn.ValueGenerated == null &&
(firstKeyColumn.DefaultValueSql == null ||
string.Equals(firstKeyColumn.DefaultValueSql, "uuid()", StringComparison.OrdinalIgnoreCase) ||
string.Equals(firstKeyColumn.DefaultValueSql, "uuid_to_bin(uuid())", StringComparison.OrdinalIgnoreCase)) &&
_typeMappingSource.FindMapping(firstKeyColumn.StoreType) is MySqlGuidTypeMapping)
{
firstKeyColumn.ValueGenerated = ValueGenerated.OnAdd;
firstKeyColumn.DefaultValueSql = null;
}
table.PrimaryKey = key;
}
catch (Exception ex)
{
_logger.Logger.LogError(ex, "Error assigning primary key for {table}.", table.Name);
}
}
}
}
}
}
/// <remarks>
/// Primary keys are handled as in <see cref="GetConstraints"/>, not here
/// </remarks>
void GetPrimaryKeys()
{
foreach (var x in _tables)
{
DatabasePrimaryKey index = null;
using (var command = new FbCommand(string.Format(GetPrimaryQuery, x.Key.Replace("\"", "")), _connection))
using (var reader = command.ExecuteReader())
while (reader.Read())
{
if (index == null)
{
index = new DatabasePrimaryKey
{
Table = x.Value,
Name = reader.GetString(0).Trim()
}
}
;
index.Columns.Add(x.Value.Columns.Single(y => y.Name == reader.GetString(1).Trim()));
}
x.Value.PrimaryKey = index;
if (x.Value.PrimaryKey != null)
{
Logger.LogDebug($"GetPrimaryKeys => pk for table {x.Key} found => {x.Value.PrimaryKey.Name} with columns {string.Join(",", x.Value.PrimaryKey.Columns.Select(c=>c.Name))}");
}
else
{
Logger.LogDebug($"GetPrimaryKeys => pk for table {x.Key} not found");
}
}
}
const string GetIndexesQuery = @"
SELECT
I.rdb$index_name as Index_Name,
COALESCE(I.rdb$unique_flag, 0) as Non_Unique,
I.rdb$relation_name as Columns
FROM
RDB$INDICES i
LEFT JOIN rdb$index_segments sg on i.rdb$index_name = sg.rdb$index_name
LEFT JOIN rdb$relation_constraints rc on rc.rdb$index_name = I.rdb$index_name and rc.rdb$constraint_type = null
WHERE
i.rdb$relation_name = '{0}'
GROUP BY
Index_Name, Non_Unique, Columns";
/// <remarks>
/// Primary keys are handled as in <see cref="GetConstraints"/>, not here
/// </remarks>
void GetIndexes()
{
foreach (var x in _tables)
{
DatabaseIndex index = null;
using (var command = new FbCommand(string.Format(GetIndexesQuery, x.Key), _connection))
using (var reader = command.ExecuteReader())
while (reader.Read())
{
try
{
if (index == null)
{
index = new DatabaseIndex
{
Table = x.Value,
Name = reader.GetString(0).Trim(),
IsUnique = !reader.GetBoolean(1),
}
}
;
foreach (var column in reader.GetString(2).Trim().Split(','))
{
index.Columns.Add(x.Value.Columns.Single(y => y.Name == column));
}
x.Value.Indexes.Add(index);
}
catch { }
}
Logger.LogDebug($"GetIndexes => Table {x.Key} => " + (x.Value.Indexes != null ? $"{x.Value.Indexes.Count}" : "0") + " index found");
}
}
const string GetConstraintsQuery = @"
SELECT
drs.rdb$constraint_name as PK_NAME,
LIST(distinct trim(dis.rdb$field_name)||'#'||dis.rdb$field_position,',') AS SRC_COLUMN_NAME,
mrc.rdb$relation_name AS PRINC_TABLE_NAME,
LIST(distinct trim(mis.rdb$field_name)||'#'||mis.rdb$field_position,',') AS PRINC_COLUMN_NAME,
rc.RDB$DELETE_RULE as DELETE_RULE
FROM
rdb$relation_constraints drs
left JOIN rdb$index_segments dis ON drs.rdb$index_name = dis.rdb$index_name
left JOIN rdb$ref_constraints rc ON drs.rdb$constraint_name = rc.rdb$constraint_name
left JOIN rdb$relation_constraints mrc ON rc.rdb$const_name_uq = mrc.rdb$constraint_name
left JOIN rdb$index_segments mis ON mrc.rdb$index_name = mis.rdb$index_name
WHERE
drs.rdb$constraint_type = 'FOREIGN KEY'
AND drs.RDB$RELATION_NAME = '{0}'
GROUP BY
drs.rdb$constraint_name,
mrc.rdb$relation_name,
rc.RDB$DELETE_RULE";
void GetConstraints()
{
foreach (var x in _tables)
{
using (var command = new FbCommand(string.Format(GetConstraintsQuery, x.Key), _connection))
using (var reader = command.ExecuteReader())
while (reader.Read())
{
if (_tables.ContainsKey($"{x.Key}"))
{
DatabaseForeignKey fkInfo = new DatabaseForeignKey
{
Table = x.Value,
Name = reader.GetString(0).Trim(),
OnDelete = ConvertToReferentialAction(reader.GetString(4)),
PrincipalTable = _tables[reader.GetString(2).Trim()]
};
// TODO: the following code is ugly, must refactor (o_0)
Logger.LogDebug(
$" PK ==> Table {x.Value.Name} => {fkInfo.Name}, PrincipalTable {fkInfo.PrincipalTable.Name}, {reader.GetString(1).Trim()}, {reader.GetString(3).Trim()}");
var fkcols = reader.GetString(1).Split(',');
var columns = new string[fkcols.Length];
foreach (var colandpos in fkcols)
{
var split = colandpos.Split('#');
columns[int.Parse(split[1])] = split[0];
}
foreach (var column in columns)
{
fkInfo.Columns.Add(x.Value.Columns.Single(y => y.Name == column));
}
var fkcols2 = reader.GetString(3).Split(',');
var columns2 = new string[fkcols2.Length];
foreach (var colandpos in fkcols2)
{
var split = colandpos.Split('#');
columns2[int.Parse(split[1])] = split[0];
}
/*columns.Clear();
* foreach (var colandpos in reader.GetString(3).Split(','))
* {
* var split = colandpos.Split('#');
* columns.Insert(int.Parse(split[1]), split[0]);
* }*/
foreach (var column in columns2)
{
fkInfo.PrincipalColumns.Add(x.Value.Columns.Single(y => y.Name == column));
}
//foreach (var column in reader.GetString(3).Split(','))
// fkInfo.PrincipalColumns.Add(x.Value.Columns.Single(y => y.Name == column));
x.Value.ForeignKeys.Add(fkInfo);
}
else
{
Logger.LogWarning($"GetConstraints => Referenced table { reader.GetString(3).Trim() } is not in dictionary.");
}
}
Logger.LogDebug($"GetConstraints => Table {x.Key} => {x.Value.ForeignKeys.Count}");
}
}
private void GetPrimaryKeys()
{
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 = 1
AND (SUBSTRING(TABLE_NAME, 1,2) <> '__')
ORDER BY ix.[TABLE_NAME], ix.[INDEX_NAME], ix.[ORDINAL_POSITION];";
using (var reader = command.ExecuteReader())
{
DatabasePrimaryKey primaryKey = 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");
//Logger.IndexColumnFound(
// tableName, indexName, true, columnName, indexOrdinal);
if (!_tableSelectionSet.Allows(tableName))
{
//Logger.IndexColumnSkipped(columnName, indexName, DisplayName(schemaName, tableName));
continue;
}
Debug.Assert(primaryKey == null || primaryKey.Table != null);
if (primaryKey == null ||
primaryKey.Name != indexName
// ReSharper disable once PossibleNullReferenceException
|| primaryKey.Table.Name != tableName ||
primaryKey.Table.Schema != schemaName)
{
DatabaseTable table;
if (!_tables.TryGetValue(TableKey(tableName), out table))
{
//Logger.IndexTableMissingWarning(indexName, tableName);
continue;
}
primaryKey = new DatabasePrimaryKey
{
Table = table,
Name = indexName
};
Debug.Assert(table.PrimaryKey == null);
table.PrimaryKey = primaryKey;
}
DatabaseColumn column;
if (!_tableColumns.TryGetValue(ColumnKey(primaryKey.Table, columnName), out column))
{
//Logger.IndexColumnsNotMappedWarning(indexName, new[] { columnName });
}
else
{
primaryKey.Columns.Add(column);
}
}
}
}
private void GetIndexes(DbConnection connection, IReadOnlyList <DatabaseTable> tables)
{
using var command = connection.CreateCommand();
var commandText = @"SELECT INDEXES.TABLE_NAME, INDEXES.INDEX_NAME, INDEXES.INDEX_TYPE, COLUMN_NAME, COLUMN_ORDERING, IS_UNIQUE, IS_NULL_FILTERED
FROM INFORMATION_SCHEMA.INDEX_COLUMNS
INNER JOIN INFORMATION_SCHEMA.INDEXES
ON INDEX_COLUMNS.TABLE_CATALOG = INDEXES.TABLE_CATALOG
AND INDEX_COLUMNS.TABLE_SCHEMA = INDEXES.TABLE_SCHEMA
AND INDEX_COLUMNS.TABLE_NAME = INDEXES.TABLE_NAME
AND INDEX_COLUMNS.INDEX_NAME = INDEXES.INDEX_NAME
WHERE INDEXES.TABLE_CATALOG = '' AND INDEXES.TABLE_SCHEMA = ''
AND SPANNER_IS_MANAGED = FALSE -- Skip Spanner managed indexes as these will be generated by Spanner automatically
ORDER BY INDEXES.TABLE_NAME, INDEXES.INDEX_NAME, ORDINAL_POSITION
";
command.CommandText = commandText;
using var reader = command.ExecuteReader();
var tableIndexGroups = reader.Cast <DbDataRecord>()
.GroupBy(ddr => ddr.GetValueOrDefault <string>("TABLE_NAME"));
foreach (var tableIndexGroup in tableIndexGroups)
{
var tableName = tableIndexGroup.Key;
var table = tables.Single(t => t.Name == tableName);
var primaryKeyGroups = tableIndexGroup
.Where(ddr => ddr.GetValueOrDefault <string>("INDEX_TYPE") == "PRIMARY_KEY")
.GroupBy(
ddr =>
(Name: ddr.GetValueOrDefault <string>("INDEX_NAME"),
TypeDesc: ddr.GetValueOrDefault <string>("INDEX_TYPE")))
.ToArray();
if (primaryKeyGroups.Length == 1)
{
var primaryKeyGroup = primaryKeyGroups[0];
var primaryKey = new DatabasePrimaryKey {
Table = table, Name = primaryKeyGroup.Key.Name
};
if (primaryKeyGroup.Key.TypeDesc == "INDEX")
{
primaryKey["Spanner:Clustered"] = false;
}
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 indexGroups = tableIndexGroup.Where(
ddr => ddr.GetValueOrDefault <string>("INDEX_TYPE") != "PRIMARY_KEY")
.GroupBy(ddr => (
Name: ddr.GetValueOrDefault <string>("INDEX_NAME"),
TypeDesc: ddr.GetValueOrDefault <string>("INDEX_TYPE"),
IsUnique: ddr.GetValueOrDefault <bool>("IS_UNIQUE"),
IsNullFiltered: ddr.GetValueOrDefault <bool>("IS_NULL_FILTERED")
)
).ToArray();
foreach (var indexGroup in indexGroups)
{
var index = new DatabaseIndex
{
Table = table,
Name = indexGroup.Key.Name,
IsUnique = indexGroup.Key.IsUnique,
};
if (indexGroup.Key.TypeDesc == "PRIMARY_KEY")
{
index["Spanner:Clustered"] = true;
}
if (indexGroup.Key.IsNullFiltered)
{
index["Spanner:IsNullFiltered"] = true;
}
foreach (var dataRecord in indexGroup)
{
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));
index.Columns.Add(column);
}
table.Indexes.Add(index);
}
}
}
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);
}
}
}
}
}
void GetConstraints()
{
using (var command = new NpgsqlCommand(GetConstraintsQuery, _connection))
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
var schemaName = reader.GetValueOrDefault <string>("nspname");
var tableName = reader.GetValueOrDefault <string>("relname");
if (!_tableSelectionSet.Allows(schemaName, tableName))
{
continue;
}
var table = _tables[TableKey(tableName, schemaName)];
var columns = (List <DatabaseColumn>)table.Columns;
var constraintName = reader.GetValueOrDefault <string>("conname");
var constraintType = reader.GetValueOrDefault <char>("contype");
switch (constraintType)
{
case 'p':
var primaryKey = new DatabasePrimaryKey
{
Table = table,
Name = constraintName
};
var pkColumnIndices = reader.GetValueOrDefault <short[]>("conkey");
foreach (var pkColumnIndex in pkColumnIndices)
{
primaryKey.Columns.Add(columns[pkColumnIndex - 1]);
}
Debug.Assert(table.PrimaryKey == null);
table.PrimaryKey = primaryKey;
continue;
case 'f':
var foreignSchemaName = reader.GetValueOrDefault <string>("fr_nspname");
var foreignTableName = reader.GetValueOrDefault <string>("fr_relname");
if (!_tables.TryGetValue(TableKey(foreignTableName, foreignSchemaName), out var principalTable))
{
continue;
}
var foreignKey = new DatabaseForeignKey
{
Name = constraintName,
Table = table,
PrincipalTable = principalTable,
OnDelete = ConvertToReferentialAction(reader.GetValueOrDefault <char>("confdeltype"))
};
var columnIndices = reader.GetValueOrDefault <short[]>("conkey");
var principalColumnIndices = reader.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(columns[columnIndices[i] - 1]);
foreignKey.PrincipalColumns.Add(principalColumns[principalColumnIndices[i] - 1]);
}
table.ForeignKeys.Add(foreignKey);
break;
default:
throw new NotSupportedException($"Unknown constraint type code {constraintType} for constraint {constraintName}");
}
}
}
}
private void GetColumns(
DbConnection connection,
string tableFilter,
DatabaseModel databaseModel)
{
using (var command = connection.CreateCommand())
{
command.CommandText = new StringBuilder()
.AppendLine("SELECT")
.AppendLine(" f.\"_Field-Name\",")
.AppendLine(" f.\"_Data-Type\",")
.AppendLine(" t.\"_File-Name\" as name,")
.AppendLine(" t.\"_Prime-Index\" as primeindex,")
.AppendLine(" f.\"_Mandatory\",")
.AppendLine(" if.\"_index-recid\" as identity,")
.AppendLine(" f.\"_initial\"")
.AppendLine("FROM pub.\"_field\" f")
.AppendLine("INNER JOIN pub.\"_File\" t ")
.AppendLine("ON t.rowid = f.\"_File-recid\"")
.AppendLine("LEFT JOIN pub.\"_index-field\" if ")
.AppendLine("ON if.\"_index-recid\" = t.\"_Prime-Index\" AND if.\"_field-recid\" = f.rowid")
.AppendLine(tableFilter)
.AppendLine("ORDER BY f.\"_Order\"")
.ToString();
using (var reader = command.ExecuteReader())
{
var tableColumnGroups = reader.Cast <DbDataRecord>()
.GroupBy(
ddr => ddr.GetValueOrDefault <string>("name"));
foreach (var tableColumnGroup in tableColumnGroups)
{
var tableName = tableColumnGroup.Key;
var table = databaseModel.Tables.Single(t => t.Schema == DatabaseModelDefaultSchema && t.Name == tableName);
var primaryKey = new DatabasePrimaryKey
{
Table = table,
Name = table + "_PK"
};
table.PrimaryKey = primaryKey;
foreach (var dataRecord in tableColumnGroup)
{
var columnName = dataRecord.GetValueOrDefault <string>("_Field-Name");
var dataTypeName = dataRecord.GetValueOrDefault <string>("_Data-Type");
var isNullable = !dataRecord.GetValueOrDefault <bool>("_Mandatory");
var isIdentity = dataRecord.GetValueOrDefault <long?>("identity") != null;
var defaultValue = !isIdentity?dataRecord.GetValueOrDefault <object>("_initial") : null;
var storeType = dataTypeName;
if (string.IsNullOrWhiteSpace(defaultValue?.ToString()))
{
defaultValue = null;
}
table.Columns.Add(new DatabaseColumn
{
Table = table,
Name = columnName,
StoreType = storeType,
IsNullable = isNullable,
DefaultValueSql = defaultValue?.ToString(),
ValueGenerated = default
});
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;
}
}
}
}
}
}
}
