protected void FillForeignKeyData(DBSchemaForeignKey fk, DataRow dtFKRow, out DBSchemaIndex related_pk)
{
DataColumn catalog = GetColumn(dtFKRow.Table, "foreign_key_catalog", false);
DataColumn schema = GetColumn(dtFKRow.Table, "foreign_key_owner", false);
DataColumn name = GetColumn(dtFKRow.Table, "foreign_key_constraint_name", true);
DataColumn table = GetColumn(dtFKRow.Table, "foreign_key_table_name", false);
DataColumn pk_table = GetColumn(dtFKRow.Table, "primary_key_table_name", false);
DataColumn pk_owner = GetColumn(dtFKRow.Table, "primary_key_owner", false);
DataColumn pk_constraint = GetColumn(dtFKRow.Table, "primary_key_constraint_name", false);
fk.Catalog = GetColumnStringValue(dtFKRow, catalog);
fk.Schema = GetColumnStringValue(dtFKRow, schema);
fk.Name = GetColumnStringValue(dtFKRow, name);
string fk_table_value = GetColumnStringValue(dtFKRow, table);
fk.ForeignKeyTable = new DBSchemaItemRef(DBSchemaTypes.Table, fk.Schema, fk_table_value);
string pk_table_value = GetColumnStringValue(dtFKRow, pk_table);
string pk_owner_value = GetColumnStringValue(dtFKRow, pk_owner);
string pk_constraint_value = GetColumnStringValue(dtFKRow, pk_constraint);
related_pk = new DBSchemaIndex(pk_owner_value, pk_constraint_value);
related_pk.TableReference = new DBSchemaItemRef(DBSchemaTypes.Table, pk_owner_value, pk_table_value);
fk.PrimaryKeyTable = related_pk.TableReference;
}
protected override void FillForeignKeyColumns(DBSchemaForeignKey anFk, DataTable dtColumns)
{
DataColumn fktblcol = GetColumn(dtColumns, "FKTableName", true);
DataColumn fkschcol = GetColumn(dtColumns, "FKTableSchema", true);
DataColumn fkcatcol = GetColumn(dtColumns, "FKTableCatalog", true);
DataColumn fkcolumn = GetColumn(dtColumns, "FKColumnName", true);
DataColumn pktblcol = GetColumn(dtColumns, "PKTableName", true);
DataColumn pkschcol = GetColumn(dtColumns, "PKTableSchema", true);
DataColumn pkcatcol = GetColumn(dtColumns, "PKTableCatalog", true);
DataColumn pkcolumn = GetColumn(dtColumns, "PKColumnName", true);
bool first = true;
foreach (DataRow row in dtColumns.Rows)
{
if (first)
{
first = false;
//populate the references to the foreign key tables and primary key tables
DBSchemaItemRef fktbl = new DBSchemaItemRef(DBSchemaTypes.Table,
GetColumnStringValue(row, fkcatcol), GetColumnStringValue(row, fkschcol), GetColumnStringValue(row, fktblcol));
anFk.ForeignKeyTable = fktbl;
DBSchemaItemRef pktbl = new DBSchemaItemRef(DBSchemaTypes.Table,
GetColumnStringValue(row, pkcatcol), GetColumnStringValue(row, pkschcol), GetColumnStringValue(row, pktblcol));
anFk.PrimaryKeyTable = pktbl;
}
DBSchemaForeignKeyMapping map = new DBSchemaForeignKeyMapping();
map.ForeignColumn = GetColumnStringValue(row, fkcolumn);
map.PrimaryColumn = GetColumnStringValue(row, pkcolumn);
anFk.Mappings.Add(map);
}
}
//
// foreign key schema
//
protected override DBSchemaForeignKey LoadAForeignKey(DbConnection con, DBSchemaItemRef fkref)
{
DataTable dt = this.GetForeignKeyData(con, fkref);
if (dt.Rows.Count > 0)
{
DBSchemaIndex pk;
DBSchemaForeignKey fk = new DBSchemaForeignKey();
this.FillForeignKeyData(fk, dt.Rows[0], out pk);
//TODO Fill Columns
DBSchemaItemRef pkIxdRef = pk.GetReference();
pkIxdRef.Container = pk.TableReference;
DataTable pkCols = GetIndexColumns(con, pkIxdRef);
this.FillIndexColuns(pk, pkCols);
DBSchemaItemRef fkIdxRef = fk.GetReference();
fkIdxRef.Container = fk.ForeignKeyTable;
DataTable fkCols = GetForeignKeyColumns(con, fkIdxRef);
this.FillForeignKeyColumns(fk, fkCols, pkCols);
return(fk);
}
else
{
return(null);
}
}
protected override DBSchemaForeignKey LoadAForeignKey(DbConnection con, DBSchemaItemRef fkref)
{
DataTable dtFK = GetForeignKeyData(con, fkref);
DBSchemaForeignKey anFk = null;
if (null != dtFK && dtFK.Rows.Count > 0)
{
anFk = new DBSchemaForeignKey();
this.FillForeignKeyData(anFk, dtFK.Rows[0]);
}
return(anFk);
}
/// <summary>
/// populates the meta data for a specific foreign key
/// </summary>
/// <param name="fk"></param>
/// <param name="dtFKRow"></param>
protected override void FillForeignKeyData(DBSchemaForeignKey fk, DataRow dtFKRow)
{
DataTable dt = dtFKRow.Table;
DataColumn schemacol = GetColumn(dt, "FK_SCHEMA", false);
DataColumn catalogcol = GetColumn(dt, "FK_CATALOG", false);
DataColumn namecol = GetColumn(dt, "FK_NAME", true);
DataColumn pkcatalogcol = GetColumn(dt, "PK_CATALOG_NAME", false);
DataColumn pkschemacol = GetColumn(dt, "PK_SCHEMA_NAME", false);
DataColumn pktablecol = GetColumn(dt, "PK_TABLE_NAME", true);
DataColumn pkcolumncol = GetColumn(dt, "PK_COLUMN_NAME", true);
DataColumn fkcatalogcol = GetColumn(dt, "FK_CATALOG_NAME", false);
DataColumn fkschemacol = GetColumn(dt, "FK_SCHEMA_NAME", false);
DataColumn fktablecol = GetColumn(dt, "FK_TABLE_NAME", false);
DataColumn fkcolumncol = GetColumn(dt, "FK_COLUMN_NAME", false);
fk.Catalog = GetColumnStringValue(dtFKRow, catalogcol);
fk.Schema = GetColumnStringValue(dtFKRow, schemacol);
fk.Name = GetColumnStringValue(dtFKRow, namecol);
DBSchemaItemRef pktable = new DBSchemaItemRef(DBSchemaTypes.Table,
GetColumnStringValue(dtFKRow, pkcatalogcol),
GetColumnStringValue(dtFKRow, pkschemacol),
GetColumnStringValue(dtFKRow, pktablecol));
DBSchemaItemRef fktable = new DBSchemaItemRef(DBSchemaTypes.Table,
GetColumnStringValue(dtFKRow, fkcatalogcol),
GetColumnStringValue(dtFKRow, fkschemacol),
GetColumnStringValue(dtFKRow, fktablecol));
fk.ForeignKeyTable = fktable;
fk.PrimaryKeyTable = pktable;
DBSchemaForeignKeyMapping map = new DBSchemaForeignKeyMapping();
map.ForeignColumn = GetColumnStringValue(dtFKRow, fkcolumncol);
map.PrimaryColumn = GetColumnStringValue(dtFKRow, pkcolumncol);
fk.Mappings.Add(map);
}
protected void FillForeignKeyColumns(DBSchemaForeignKey anFk, DataTable fkColumns, DataTable pkColumns)
{
if (fkColumns.Rows.Count == pkColumns.Rows.Count)
{
DataColumn fkNameCol = fkColumns.Columns["COLUMN_NAME"];
DataColumn pkNameCol = pkColumns.Columns["COLUMN_NAME"];
for (int i = 0; i < fkColumns.Rows.Count; i++)
{
DBSchemaForeignKeyMapping mapping = new DBSchemaForeignKeyMapping();
mapping.PrimaryColumn = GetColumnStringValue(pkColumns.Rows[i], pkNameCol);
mapping.ForeignColumn = GetColumnStringValue(fkColumns.Rows[i], fkNameCol);
anFk.Mappings.Add(mapping);
}
}
else
{
//Cannot do anything as we cannot match
}
}
protected override void FillForeignKeyData(DBSchemaForeignKey fk, DataRow dtFKRow)
{
DataTable tbl = dtFKRow.Table;
DataColumn catalog = GetColumn(tbl, "CONSTRAINT_CATALOG", false);
DataColumn schema = GetColumn(tbl, "CONSTRAINT_SCHEMA", false);
DataColumn name = GetColumn(tbl, "CONSTRAINT_NAME", true);
fk.Catalog = GetColumnStringValue(dtFKRow, catalog);
fk.Schema = GetColumnStringValue(dtFKRow, schema);
fk.Name = GetColumnStringValue(dtFKRow, name);
DataColumn fkFromCatalog = GetColumn(tbl, "TABLE_CATALOG", false);
DataColumn fkFromSchema = GetColumn(tbl, "TABLE_SCHEMA", false);
DataColumn fkFromTable = GetColumn(tbl, "TABLE_NAME", false);
DataColumn fkFromColumn = GetColumn(tbl, "FKEY_FROM_COLUMN", true);
DataColumn fkToCatalog = GetColumn(tbl, "FKEY_TO_CATALOG", false);
DataColumn fkToSchema = GetColumn(tbl, "FKEY_TO_SCHEMA", false);
DataColumn fkToTable = GetColumn(tbl, "FKEY_TO_TABLE", true);
DataColumn fkToColumn = GetColumn(tbl, "FKEY_TO_COLUMN", true);
fk.ForeignKeyTable = new DBSchemaItemRef(DBSchemaTypes.Table,
GetColumnStringValue(dtFKRow, fkFromCatalog),
GetColumnStringValue(dtFKRow, fkFromSchema),
GetColumnStringValue(dtFKRow, fkFromTable));
fk.PrimaryKeyTable = new DBSchemaItemRef(DBSchemaTypes.Table,
GetColumnStringValue(dtFKRow, fkToCatalog),
GetColumnStringValue(dtFKRow, fkToSchema),
GetColumnStringValue(dtFKRow, fkToTable));
DBSchemaForeignKeyMapping map = new DBSchemaForeignKeyMapping();
map.ForeignColumn = GetColumnStringValue(dtFKRow, fkFromColumn);
map.PrimaryColumn = GetColumnStringValue(dtFKRow, fkToColumn);
fk.Mappings.Add(map);
}
protected override void FillForeignKeyData(DBSchemaForeignKey fk, DataRow dtFKRow)
{
DBSchemaIndex related_pk;
this.FillForeignKeyData(fk, dtFKRow, out related_pk);
}
/// <summary>
/// Ignored
/// </summary>
/// <param name="anFk"></param>
/// <param name="dtColumns"></param>
protected override void FillForeignKeyColumns(DBSchemaForeignKey anFk, DataTable dtColumns)
{
// Should be done in the FillForeignKeyData
}
//
// Table overrides
//
#region protected override DBSchemaTable LoadATable(DbConnection con, DBSchemaItemRef tableref)
/// <summary>
/// Loads the info on a specific table
/// </summary>
/// <param name="con"></param>
/// <param name="tableref"></param>
/// <returns></returns>
protected override DBSchemaTable LoadATable(DbConnection con, DBSchemaItemRef tableref)
{
DataTable dtTable = GetTableData(con, tableref);
DBSchemaTable atable = null;
if (null != dtTable && dtTable.Rows.Count > 0)
{
atable = new DBSchemaTable();
this.FillTableData(atable, dtTable.Rows[0]);
DataTable dtColumns = this.GetTableColumns(con, tableref);
if (null != dtColumns)
{
this.FillTableColumns(atable.Columns, dtColumns);
}
DBSchemaItemRefCollection idxs = new DBSchemaItemRefCollection();
DBSchemaItemRefCollection fks = new DBSchemaItemRefCollection();
this.LoadForeignKeyRefs(con, fks, tableref);
this.LoadIndexRefs(con, idxs, tableref);
DBSchemaIndexCollection indexes = new DBSchemaIndexCollection();
foreach (DBSchemaItemRef idx in idxs)
{
DBSchemaIndex same;
DBSchemaIndex anindex = this.LoadAnIndex(con, idx);
if (indexes.TryGetIndex(idx, out same))
{
same.Columns.AddRange(anindex.Columns);
}
else
{
indexes.Add(anindex);
}
}
foreach (DBSchemaIndex idx in indexes)
{
if (idx.IsPrimaryKey)
{
foreach (DBSchemaIndexColumn idxcol in idx.Columns)
{
DBSchemaTableColumn tblcol;
if (atable.Columns.TryGetColumn(idxcol.ColumnName, out tblcol))
{
tblcol.PrimaryKey = true;
}
}
}
}
DBSchemaForeignKeyCollection foreignkeys = new DBSchemaForeignKeyCollection();
foreach (DBSchemaItemRef fk in fks)
{
DBSchemaForeignKey aForeignKey = this.LoadAForeignKey(con, fk);
foreignkeys.Add(aForeignKey);
}
atable.ForeignKeys = foreignkeys;
atable.Indexes = indexes;
}
return(atable);
}
