private void ExtractForeignKeys(Schema schema)
{
// PRAGMA foreign_key_list - retuns column list for all foreign key
// row structure:
// 0 - id (numeric value, identifier, unique across foreign keys but non-unique across results)
// 1 - seq (numeric value, describes order within foreign key)
// 2 - table (string value, foreign table name)
// 3 - from (string value, local column name)
// 4 - to (string value, referenced column)
// 5 - on_update (string value, action on update)
// 6 - on_delete (string value, action on delete)
// 7 - match (string value, always NONE)
foreach (var table in schema.Tables)
{
var query = BuildExtractForeignKeysQuery(table.Name);
var state = new ForeignKeyReaderState(table);
using var cmd = Connection.CreateCommand(query);
using var reader = cmd.ExecuteReader();
while (reader.Read())
{
ReadForeignKeyColumnData(reader, table, ref state);
}
}
}
private void ExtractForeignKeys(ExtractionContext context)
{
using var reader = ExecuteReader(context.PerformReplacements(GetExtractForeignKeysQuery()));
var state = new ForeignKeyReaderState(context.Catalog);
while (reader.Read())
{
ReadForeignKeyColumnData(reader, ref state);
}
}
private void ExtractForeignKeys(Catalog catalog)
{
using var command = Connection.CreateCommand(GetExtractForeignKeysQuery());
using var reader = command.ExecuteReader(CommandBehavior.SingleResult);
var readerState = new ForeignKeyReaderState(catalog.DefaultSchema, catalog.DefaultSchema);
while (reader.Read())
{
ReadForeignKeyColumnData(reader, ref readerState);
}
}
private void ExtractForeignKeys(ExtractionContext context)
{
var query = context.PerformReplacements(GetExtractForeignKeysQuery());
using var command = Connection.CreateCommand(query);
using var reader = command.ExecuteReader();
var state = new ForeignKeyReaderState(context.Catalog);
while (reader.Read())
{
ReadForeignKeyColumnData(reader, ref state);
}
}
private async Task ExtractForeignKeysAsync(ExtractionContext context, CancellationToken token)
{
var query = context.PerformReplacements(GetExtractForeignKeysQuery());
var reader = await ExecuteReaderAsync(query, token).ConfigureAwait(false);
await using (reader.ConfigureAwait(false)) {
var state = new ForeignKeyReaderState(context.Catalog);
while (await reader.ReadAsync(token).ConfigureAwait(false))
{
ReadForeignKeyColumnData(reader, ref state);
}
}
}
private void ExtractForeignKeys(Schema schema)
{
foreach (var table in schema.Tables)
{
var query = BuildExtractForeignKeysQuery(table.Name);
var state = new ForeignKeyReaderState(table);
using var cmd = Connection.CreateCommand(query);
using var reader = cmd.ExecuteReader();
while (reader.Read())
{
ReadForeignKeyColumnData(reader, table, ref state);
}
}
}
private async Task ExtractForeignKeysAsync(Catalog catalog, CancellationToken token)
{
var command = Connection.CreateCommand(GetExtractForeignKeysQuery());
await using (command.ConfigureAwait(false)) {
var reader = await command.ExecuteReaderAsync(CommandBehavior.SingleResult, token).ConfigureAwait(false);
await using (reader.ConfigureAwait(false)) {
var readerState = new ForeignKeyReaderState(catalog.DefaultSchema, catalog.DefaultSchema);
while (await reader.ReadAsync(token).ConfigureAwait(false))
{
ReadForeignKeyColumnData(reader, ref readerState);
}
}
}
}
private async Task ExtractForeignKeysAsync(Schema schema, CancellationToken token)
{
foreach (var table in schema.Tables)
{
var query = BuildExtractForeignKeysQuery(table.Name);
var state = new ForeignKeyReaderState(table);
var cmd = Connection.CreateCommand(query);
await using (cmd.ConfigureAwait(false)) {
var reader = await cmd.ExecuteReaderAsync(token).ConfigureAwait(false);
await using (reader.ConfigureAwait(false)) {
while (await reader.ReadAsync(token).ConfigureAwait(false))
{
ReadForeignKeyColumnData(reader, table, ref state);
}
}
}
}
}
private static void ReadForeignKeyColumnData(DbDataReader reader, ref ForeignKeyReaderState state)
{
int columnPosition = reader.GetInt16(7);
if (columnPosition <= state.LastColumnIndex)
{
state.ReferencingTable = state.ReferencingSchema.Tables[reader.GetString(1).Trim()];
state.ForeignKey = state.ReferencingTable.CreateForeignKey(reader.GetString(2).Trim());
ReadConstraintProperties(state.ForeignKey, reader, 3, 4);
ReadCascadeAction(state.ForeignKey, reader, 5);
state.ReferencedTable = state.ReferencedSchema.Tables[reader.GetString(9).Trim()];
state.ForeignKey.ReferencedTable = state.ReferencedTable;
}
var referencingColumn = state.ReferencingTable.TableColumns[reader.GetString(6).Trim()];
var referencedColumn = state.ReferencedTable.TableColumns[reader.GetString(10).Trim()];
state.ForeignKey.Columns.Add(referencingColumn);
state.ForeignKey.ReferencedColumns.Add(referencedColumn);
state.LastColumnIndex = columnPosition;
}
//---- ReadForeignKeyColumnData
// 0 constraint_schema,
// 1 table_name,
// 2 constraint_name,
// 3 delete_rule,
// 4 column_name,
// 5 ordinal_position,
// 6 referenced_table_schema,
// 7 referenced_table_name,
// 8 referenced_column_name
private static void ReadForeignKeyColumnData(DbDataReader reader, ref ForeignKeyReaderState state)
{
var columnIndex = ReadInt(reader, 5);
if (columnIndex <= state.LastColumnIndex)
{
var referencingSchema = state.Catalog.Schemas[reader.GetString(0)];
state.ReferencingTable = referencingSchema.Tables[reader.GetString(1)];
state.ForeignKey = state.ReferencingTable.CreateForeignKey(reader.GetString(2));
ReadCascadeAction(state.ForeignKey, reader, 3);
var referencedSchema = state.Catalog.Schemas[reader.GetString(0)]; //Schema same as current
state.ReferencedTable = referencedSchema.Tables[reader.GetString(7)];
state.ForeignKey.ReferencedTable = state.ReferencedTable;
}
var referencingColumn = state.ReferencingTable.TableColumns[reader.GetString(4)];
var referencedColumn = state.ReferencedTable.TableColumns[reader.GetString(8)];
state.ForeignKey.Columns.Add(referencingColumn);
state.ForeignKey.ReferencedColumns.Add(referencedColumn);
state.LastColumnIndex = columnIndex;
}
private static void ReadForeignKeyColumnData(DbDataReader reader, ref ForeignKeyReaderState state)
{
var lastColumnIndex = ReadInt(reader, 7);
if (lastColumnIndex <= state.LastColumnIndex)
{
var referencingSchema = state.Catalog.Schemas[reader.GetString(0)];
state.ReferencingTable = referencingSchema.Tables[reader.GetString(1)];
state.ForeignKey = state.ReferencingTable.CreateForeignKey(reader.GetString(2));
ReadConstraintProperties(state.ForeignKey, reader, 3, 4);
ReadCascadeAction(state.ForeignKey, reader, 5);
var referencedSchema = state.Catalog.Schemas[reader.GetString(8)];
state.ReferencedTable = referencedSchema.Tables[reader.GetString(9)];
state.ForeignKey.ReferencedTable = state.ReferencedTable;
}
var referencingColumn = state.ReferencingTable.TableColumns[reader.GetString(6)];
var referencedColumn = state.ReferencedTable.TableColumns[reader.GetString(10)];
state.ForeignKey.Columns.Add(referencingColumn);
state.ForeignKey.ReferencedColumns.Add(referencedColumn);
state.LastColumnIndex = lastColumnIndex;
}
private static void ReadForeignKeyColumnData(DbDataReader reader, Table table, ref ForeignKeyReaderState state)
{
var columnIndex = ReadInt(reader, 1);
if (columnIndex <= state.LastColumnIndex)
{
var foreignKeyName = string.Format(
CultureInfo.InvariantCulture, "FK_{0}_{1}", state.ReferencingTable.Name, ReadStringOrNull(reader, 2));
state.ForeignKey = state.ReferencingTable.CreateForeignKey(foreignKeyName);
ReadCascadeAction(state.ForeignKey, reader, 6);
var referencedSchema = table.Schema; //Schema same as current
var referencedTable = referencedSchema.Tables[ReadStringOrNull(reader, 2)];
state.ReferencedTable = referencedTable;
state.ForeignKey.ReferencedTable = referencedTable;
}
var referencingColumn = state.ReferencingTable.TableColumns[reader.GetString(3)];
var referencedColumn = state.ReferencedTable.TableColumns[reader.GetString(4)];
state.ForeignKey.Columns.Add(referencingColumn);
state.ForeignKey.ReferencedColumns.Add(referencedColumn);
state.LastColumnIndex = columnIndex;
}
