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;
}
/// <summary>
/// Populates the anindex from the meta data dataRow
/// </summary>
/// <param name="anindex"></param>
/// <param name="dataRow"></param>
protected override void FillIndexData(DBSchemaIndex anindex, DataRow dataRow)
{
DataTable dt = dataRow.Table;
DataColumn name = GetColumn(dt, "INDEX_NAME", true);
DataColumn pk = GetColumn(dt, "PRIMARY_KEY", false);
DataColumn unique = GetColumn(dt, "UNIQUE", false);
DataColumn col = GetColumn(dt, "COLUMN_NAME", true);
DataColumn tbl = GetColumn(dt, "TABLE_NAME", false);
anindex.Name = GetColumnStringValue(dataRow, name);
anindex.IsPrimaryKey = GetColumnBoolValue(dataRow, pk);
anindex.IsUnique = GetColumnBoolValue(dataRow, unique);
string table = GetColumnStringValue(dataRow, tbl);
if (string.IsNullOrEmpty(table) == false)
{
anindex.TableReference = new DBSchemaItemRef(DBSchemaTypes.Table, table);
}
string column = GetColumnStringValue(dataRow, col);
if (string.IsNullOrEmpty(column) == false)
{
anindex.Columns.Add(new DBSchemaIndexColumn(column));
}
}
protected override void FillIndexData(DBSchemaIndex anindex, DataRow dataRow)
{
DataColumn catalog = GetColumn(dataRow.Table, "index_catalog", false);
DataColumn schema = GetColumn(dataRow.Table, "index_schema", false);
DataColumn name = GetColumn(dataRow.Table, "index_name", true);
//The SQLite Connection GetSchema() method may incorrectly report this as true
DataColumn ixPK = GetColumn(dataRow.Table, "primary_key", false);
DataColumn unique = GetColumn(dataRow.Table, "unique", false);
DataColumn tblcatalog = GetColumn(dataRow.Table, "table_catalog", false);
DataColumn tblschema = GetColumn(dataRow.Table, "table_schema", false);
DataColumn tblname = GetColumn(dataRow.Table, "table_name", true);
anindex.Catalog = GetColumnStringValue(dataRow, catalog);
anindex.Schema = GetColumnStringValue(dataRow, schema);
anindex.Name = GetColumnStringValue(dataRow, name);
DBSchemaItemRef tblref = new DBSchemaItemRef(DBSchemaTypes.Table,
GetColumnStringValue(dataRow, tblcatalog), GetColumnStringValue(dataRow, tblschema), GetColumnStringValue(dataRow, tblname));
anindex.TableReference = tblref;
anindex.IsPrimaryKey = GetColumnBoolValue(dataRow, ixPK, false);
anindex.IsUnique = GetColumnBoolValue(dataRow, unique, false);
}
/// <summary>
/// Loads specific information about an index
/// </summary>
/// <param name="con"></param>
/// <param name="idxref"></param>
/// <returns></returns>
protected override DBSchemaIndex LoadAnIndex(DbConnection con, DBSchemaItemRef idxref)
{
DataTable dtIdxs = this.GetIndexData(con, idxref);
DBSchemaIndex anindex = null;
if (null != dtIdxs && dtIdxs.Rows.Count > 0)
{
anindex = new DBSchemaIndex();
this.FillIndexData(anindex, dtIdxs.Rows[0]);
if (dtIdxs.Rows.Count > 1)
{
DataColumn col = GetColumn(dtIdxs, "COLUMN_NAME", true);
//DataColumn tbl = GetColumn(dtIdxs, "TABLE_NAME", false);
for (int i = 1; i < dtIdxs.Rows.Count; i++)
{
string column = GetColumnStringValue(dtIdxs.Rows[i], col);
if (string.IsNullOrEmpty(column) == false)
{
anindex.Columns.Add(new DBSchemaIndexColumn(column));
}
}
}
//DataTable dtIdxColumns = this.GetIndexColumns(con, idxref);
//if (null != dtIdxColumns)
// this.FillIndexColuns(anindex, dtIdxColumns);
}
return(anindex);
}
protected override void FillIndexData(DBSchemaIndex anindex, DataRow dataRow)
{
DataColumn catalog = GetColumn(dataRow.Table, "CATALOG", false);
DataColumn schema = GetColumn(dataRow.Table, "OWNER", false);
DataColumn name = GetColumn(dataRow.Table, "INDEX_NAME", true);
DataColumn tblcatalog = GetColumn(dataRow.Table, "TABLE_CATALOG", false);
DataColumn tblschema = GetColumn(dataRow.Table, "TABLE_OWNER", false);
DataColumn tblname = GetColumn(dataRow.Table, "TABLE_NAME", true);
anindex.Catalog = GetColumnStringValue(dataRow, catalog);
anindex.Schema = GetColumnStringValue(dataRow, schema);
anindex.Name = GetColumnStringValue(dataRow, name);
DBSchemaItemRef tblref = new DBSchemaItemRef(DBSchemaTypes.Table,
GetColumnStringValue(dataRow, tblcatalog), GetColumnStringValue(dataRow, tblschema), GetColumnStringValue(dataRow, tblname));
anindex.TableReference = tblref;
}
protected override void FillIndexData(DBSchemaIndex anindex, DataRow dataRow)
{
DataColumn catalog = GetColumn(dataRow.Table, "index_catalog", false);
DataColumn schema = GetColumn(dataRow.Table, "index_schema", false);
DataColumn name = GetColumn(dataRow.Table, "index_name", true);
DataColumn tblcatalog = GetColumn(dataRow.Table, "table_catalog", false);
DataColumn tblschema = GetColumn(dataRow.Table, "table_schema", false);
DataColumn tblname = GetColumn(dataRow.Table, "table_name", true);
DataColumn unique = GetColumn(dataRow.Table, "unique", false);
DataColumn pk = GetColumn(dataRow.Table, "primary", false);
anindex.Catalog = GetColumnStringValue(dataRow, catalog);
anindex.Schema = GetColumnStringValue(dataRow, schema);
anindex.Name = GetColumnStringValue(dataRow, name);
anindex.IsPrimaryKey = GetColumnBoolValue(dataRow, pk);
anindex.IsUnique = GetColumnBoolValue(dataRow, unique);
DBSchemaItemRef tblref = new DBSchemaItemRef(DBSchemaTypes.Table,
GetColumnStringValue(dataRow, tblcatalog), GetColumnStringValue(dataRow, tblschema), GetColumnStringValue(dataRow, tblname));
anindex.TableReference = tblref;
}
//
// 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);
}
/// <summary>
/// Ignored
/// </summary>
/// <param name="anindex"></param>
/// <param name="dtIdxColumns"></param>
protected override void FillIndexColuns(DBSchemaIndex anindex, DataTable dtIdxColumns)
{
//Do nothing - this is done on the FillIndexData method
}
public override void GenerateCreateIndexScript(DBSchemaIndex schemaIndex)
{
if (null == schemaIndex)
{
throw new ArgumentNullException("schemaIndex");
}
if (string.IsNullOrEmpty(schemaIndex.Name))
{
throw new ArgumentNullException("schemaIndex.Name");
}
if (schemaIndex.Type != DBSchemaTypes.Index)
{
throw new ArgumentOutOfRangeException("schemaIndex.Type");
}
if (null == schemaIndex.TableReference)
{
throw new ArgumentNullException("schemaIndex.TableReference");
}
if (schemaIndex.Columns == null)
{
throw new ArgumentNullException("schemaIndex.Columns");
}
else if (schemaIndex.Columns.Count == 0)
{
throw new ArgumentOutOfRangeException("schemaIndex.Columns");
}
if (schemaIndex.IsPrimaryKey)
{
throw new NotSupportedException("Cannot create a primary key on a table using the CREATE INDEX statement with MySQL. Use the alter table methods instead");
}
this.BeginCreate(DBSchemaTypes.Index, schemaIndex.IsUnique ? "UNIQUE" : "");
this.BeginIdentifier();
this.WriteObjectName(schemaIndex.Name);
this.EndIdentifier();
this.WriteRawSQLString(" ON ");
DBSchemaItemRef tbl = schemaIndex.TableReference;
this.WriteSourceTable(tbl.Catalog, tbl.Schema, tbl.Name, null);
this.BeginBlock();
DBSchemaIndexColumnCollection sorted = schemaIndex.Columns;
int index = 0;
foreach (DBSchemaIndexColumn col in sorted)
{
if (string.IsNullOrEmpty(col.ColumnName))
{
throw new ArgumentNullException("DBSchemaIndexColumn.Name");
}
this.BeginOrderClause(col.SortOrder);
this.BeginIdentifier();
this.WriteObjectName(col.ColumnName);
this.EndIdentifier();
this.EndOrderClause(col.SortOrder);
index++;
if (index < sorted.Count)
{
this.WriteReferenceSeparator();
}
}
this.EndBlock();
}
