public override List <PrimaryKey> GetPrimaryKeys(string schemaName)
{
var stmt = String.Format(
@"select ucc.owner as schema_name,
ucc.table_name,
ucc.constraint_name,
ucc.position,
ucc.column_name
from all_cons_columns ucc, all_constraints uc
where uc.owner = '{0}'
and uc.owner = ucc.owner
and uc.constraint_name = ucc.constraint_name
and uc.constraint_type = 'P'
order by ucc.table_name, ucc.position",
schemaName);
using (var ds = this.ExecuteSelect(stmt))
{
var cList = new Utils.ChainedList <PrimaryKey>();
foreach (DataRow dr in ds.Tables[0].Rows)
{
var pk = new PrimaryKey(dr["schema_name"].ToString(), dr["table_name"].ToString(), dr["constraint_name"].ToString());
pk.AddColumn(Convert.ToInt32(dr["position"]), dr["column_name"].ToString());
PrimaryKey tmp;
if (!cList.Add(pk, out tmp))
{
tmp.AddCoumns(pk.IndexOrderdColumnNames);
}
}
return(cList.ToList());
}
}
public override List <DBObjects.PrimaryKey> GetPrimaryKeys(string schemaName)
{
var stmt = String.Format(
@"SELECT
rc.rdb$constraint_name as ConstraintName,
s.rdb$field_name as FieldName,
rc.rdb$relation_name as TableName,
s.rdb$field_position+1 as FieldPosition
FROM
rdb$index_segments s
LEFT JOIN rdb$relation_constraints rc ON rc.rdb$index_name = s.rdb$index_name
WHERE
rc.rdb$constraint_type = 'PRIMARY KEY';");
using (var ds = this.ExecuteSelect(stmt))
{
var cList = new Utils.ChainedList <PrimaryKey>();
foreach (DataRow dr in ds.Tables[0].Rows)
{
var pk = new PrimaryKey(this._schemaName, dr["TableName"].ToString().Trim(), dr["ConstraintName"].ToString().Trim());
pk.AddColumn(Convert.ToInt32(dr["FieldPosition"]), dr["FieldName"].ToString().Trim());
PrimaryKey tmp;
if (!cList.Add(pk, out tmp))
{
tmp.AddCoumns(pk.IndexOrderdColumnNames);
}
}
return(cList.ToList());
}
}
public override List <Index> GetIndices(string schemaName)
{
var stmt = String.Format(
@"select
owner,
index_name,
ind.table_name,
ind.uniqueness,
col.column_name,
col.column_position
from all_indexes ind
inner join (select * from all_ind_columns where index_owner = '{0}') col using (index_name)
where ind.owner = '{0}'
and ind.table_type = 'TABLE'
order by index_name, column_position", schemaName);
using (var ds = this.ExecuteSelect(stmt))
{
var cList = new Utils.ChainedList <Index>();
foreach (DataRow dr in ds.Tables[0].Rows)
{
var index = new Index(dr["owner"].ToString(), dr["table_name"].ToString(), dr["index_name"].ToString());
index.IsUnique = (dr["uniqueness"].ToString() == "UNIQUE");
index.AddColumn(Convert.ToInt32(dr["column_position"]), dr["column_name"].ToString());
Index tmp;
if (!cList.Add(index, out tmp))
{
tmp.AddCoumns(index.IndexOrderdColumnNames);
}
}
return(cList.ToList());
}
}
public override List <DBObjects.PrimaryKey> GetPrimaryKeys(string schemaName)
{
var stmt = String.Format(
@"select
inx.tabschema as schema_name,
inx.tabname as table_name,
inx.indname as index_name,
inxcol.colname as column_name,
inxcol.colseq as column_position
from
syscat.indexes as inx,
syscat.indexcoluse inxcol
where
inxcol.indschema = inx.tabschema
and inxcol.indname = inx.indname
and inx.tabschema = '{0}'
and inx.uniquerule = 'P'
order by inxcol.indname, inxcol.colseq" , schemaName);
var ds = this.ExecuteSelect(stmt);
var cList = new Utils.ChainedList <PrimaryKey>();
foreach (DataRow dr in ds.Tables[0].Rows)
{
var pk = new PrimaryKey(dr["schema_name"].ToString(), dr["table_name"].ToString(), dr["index_name"].ToString());
pk.AddColumn(Convert.ToInt32(dr["column_position"]), dr["column_name"].ToString());
PrimaryKey tmp;
if (!cList.Add(pk, out tmp))
{
tmp.AddCoumns(pk.IndexOrderdColumnNames);
}
}
return(cList.ToList());
}
public override List <PrimaryKey> GetPrimaryKeys(string schemaName)
{
var stmt = String.Format(
@"select
ky.table_schema as schema_name,
ky.table_name,
ky.constraint_name as key_name,
ky.ordinal_position,
ky.column_name
from information_schema.key_column_usage as ky
inner join information_schema.table_constraints as co
on ky.constraint_name = co.constraint_name
where co.constraint_schema = '{0}'
and co.constraint_type = 'PRIMARY KEY'
order by ky.constraint_name, ky.ordinal_position",
schemaName);
var ds = this.ExecuteSelect(stmt);
var cList = new Utils.ChainedList <PrimaryKey>();
foreach (DataRow dr in ds.Tables[0].Rows)
{
var pk = new PrimaryKey(dr["schema_name"].ToString(), dr["table_name"].ToString(), dr["key_name"].ToString());
pk.AddColumn(Convert.ToInt32(dr["ordinal_position"]), dr["column_name"].ToString());
PrimaryKey tmp;
if (!cList.Add(pk, out tmp))
{
tmp.AddCoumns(pk.IndexOrderdColumnNames);
}
}
return(cList.ToList());
}
public override List <DBObjects.Unique> GetUniqueConstraints(string schemaName)
{
var stmt = String.Format(
@"SELECT
rc.rdb$constraint_name as ConstraintName,
rc.rdb$relation_name as TableName,
s.rdb$field_name as FieldName,
s.rdb$field_position+1 as FieldPosition,
rc.rdb$deferrable as Deferrable,
rc.rdb$initially_deferred as InitiallyDeferred
FROM rdb$relation_constraints rc
LEFT JOIN rdb$index_segments s ON (rc.rdb$index_name = s.rdb$index_name)
WHERE
rc.rdb$constraint_type = 'UNIQUE';");
using (var ds = this.ExecuteSelect(stmt))
{
var cList = new Utils.ChainedList <Unique>();
foreach (DataRow dr in ds.Tables[0].Rows)
{
var uk = new Unique(this._schemaName, dr["TableName"].ToString().Trim(), dr["ConstraintName"].ToString().Trim());
uk.AddColumn(Convert.ToInt32(dr["FieldPosition"]), dr["FieldName"].ToString().Trim());
Unique tmp;
if (!cList.Add(uk, out tmp))
{
tmp.AddCoumns(uk.IndexOrderdColumnNames);
}
}
return(cList.ToList());
}
}
public override List <Index> GetIndices(string schemaName)
{
var stmt = String.Format(
@"select
sta.table_schema as schema_name,
sta.table_name,
sta.index_name,
sta.column_name,
sta.seq_in_index as column_index,
sta.non_unique
from information_schema.statistics sta
where sta.table_schema = '{0}'",
schemaName);
var ds = this.ExecuteSelect(stmt);
var cList = new Utils.ChainedList <Index>();
foreach (DataRow dr in ds.Tables[0].Rows)
{
var index = new Index(dr["schema_name"].ToString(), dr["table_name"].ToString(), dr["index_name"].ToString());
index.IsUnique = !Convert.ToBoolean(dr["non_unique"]);
index.AddColumn(Convert.ToInt32(dr["column_index"]), dr["column_name"].ToString());
Index tmp;
if (!cList.Add(index, out tmp))
{
tmp.AddCoumns(index.IndexOrderdColumnNames);
}
}
return(cList.ToList());
}
public override List <Index> GetIndices(string schemaName)
{
var stmt = String.Format(
@"select
ixs.schemaname as schema_name,
ixs.tablename as table_name,
ixs.indexname as index_name,
jo.attname as column_name,
jo.attnum as column_index,
jo.indkey as index_order,
jo.indisunique as is_unique
from pg_catalog.pg_indexes as ixs
inner join (
select a.attname, a.attnum, ix.indkey, ix.indisunique, i.relname
from pg_catalog.pg_class as t,
pg_catalog.pg_class as i,
pg_catalog.pg_index as ix,
pg_catalog.pg_attribute as a
where a.attnum > 0
and t.oid = ix.indrelid
and i.oid = ix.indexrelid
and a.attrelid = t.oid
and a.attnum = ANY(ix.indkey)) as jo
on ixs.indexname = jo.relname
where ixs.schemaname = '{0}'",
schemaName);
var ds = this.ExecuteSelect(stmt);
var cList = new Utils.ChainedList <Index>();
foreach (DataRow dr in ds.Tables[0].Rows)
{
var index = new Index(dr["schema_name"].ToString(), dr["table_name"].ToString(), dr["index_name"].ToString());
index.IsUnique = Convert.ToBoolean(dr["is_unique"]);
var indexPos = dr["index_order"].ToString().Split(' ').ToArray();
for (int i = 0; i < indexPos.Length; i++)
{
if (Convert.ToInt32(dr["column_index"].ToString()) == Convert.ToInt32(indexPos[i]))
{
index.AddColumn(i + 1, dr["column_name"].ToString());
break;
}
}
Index tmp;
if (!cList.Add(index, out tmp))
{
tmp.AddCoumns(index.IndexOrderdColumnNames);
}
}
return(cList.ToList());
}
public override List <ForeignKey> GetForeignKeys(string schemaName)
{
var stmt = String.Format(
@"select
fky.table_schema as schema_name,
fky.table_name,
fky.constraint_name as key_name,
fky.column_name,
pky.table_schema as pri_schema_name,
pky.table_name as pri_table_name,
pky.column_name as pri_column_name,
fky.ordinal_position as ordinal_position,
rco.update_rule,
rco.delete_rule,
co.is_deferrable,
co.initially_deferred
from information_schema.key_column_usage as fky
inner join information_schema.table_constraints as co
on fky.constraint_name = co.constraint_name
inner join information_schema.referential_constraints as rco
on rco.constraint_name = co.constraint_name
inner join information_schema.key_column_usage as pky
on pky.constraint_name = rco.unique_constraint_name
and fky.ordinal_position = pky.ordinal_position
where co.constraint_schema = '{0}'
and co.constraint_type = 'FOREIGN KEY'
order by table_name, key_name, ordinal_position",
schemaName);
var ds = this.ExecuteSelect(stmt);
var cList = new Utils.ChainedList <ForeignKey>();
foreach (DataRow dr in ds.Tables[0].Rows)
{
var fk = new ForeignKey(dr["schema_name"].ToString(), dr["table_name"].ToString(), dr["key_name"].ToString(),
dr["pri_schema_name"].ToString(), dr["pri_table_name"].ToString());
fk.UpdateRule = this.ParseUpdateRule(dr["update_rule"].ToString());
fk.DeleteRule = this.ParseDeleteRule(dr["delete_rule"].ToString());
fk.Deferrability = this.ParseBoolean(dr["is_deferrable"].ToString()) ? Deferrability.Deferrable : Deferrability.NotDeferrable;
fk.InitialMode = this.ParseBoolean(dr["initially_deferred"].ToString()) ? InitialMode.InitiallyDeferred : InitialMode.InitiallyImmediate;
fk.AddColumn(Convert.ToInt32(dr["ordinal_position"]), dr["column_name"].ToString(), dr["pri_column_name"].ToString());
ForeignKey tmp;
if (!cList.Add(fk, out tmp))
{
tmp.AddColumns(fk.IndexOrderdColumnNames);
}
}
return(cList.ToList());
}
public override List <ForeignKey> GetForeignKeys(string schemaName)
{
var stmt = String.Format(
@"select
c.owner as schema_name,
c.constraint_name as key_name,
c.delete_rule,
c.deferrable,
c.deferred,
source.table_name as table_name,
source.column_name as column_name,
source.position,
target.owner as pri_schema_name,
target.table_name as pri_table_name,
target.column_name as pri_column_name
from
all_constraints c,
all_cons_columns source,
all_cons_columns target
where c.owner = '{0}'
and c.constraint_type = 'R'
and c.constraint_name = source.constraint_name
and c.r_constraint_name = target.constraint_name
and source.position = target.position
and source.owner = '{0}'
and target.owner = '{0}'", schemaName);
using (var ds = this.ExecuteSelect(stmt))
{
var cList = new Utils.ChainedList <ForeignKey>();
foreach (DataRow dr in ds.Tables[0].Rows)
{
var fk = new ForeignKey(dr["schema_name"].ToString(), dr["table_name"].ToString(), dr["key_name"].ToString(),
dr["pri_schema_name"].ToString(), dr["pri_table_name"].ToString());
// TODO missing update rule
fk.DeleteRule = this.ParseDeleteRule(dr["delete_rule"].ToString());
fk.Deferrability = this.ParseDeferrability(dr["deferrable"].ToString());
fk.InitialMode = this.ParseInitialMode(dr["deferred"].ToString());
fk.AddColumn(Convert.ToInt32(dr["position"]), dr["column_name"].ToString(), dr["pri_column_name"].ToString());
ForeignKey tmp;
if (!cList.Add(fk, out tmp))
{
tmp.AddColumns(fk.IndexOrderdColumnNames);
}
}
return(cList.ToList());
}
}
public override List <DBObjects.Index> GetIndices(string schemaName)
{
var stmt = String.Format(
@"SELECT
i.rdb$relation_name AS TableName,
s.rdb$field_name AS FieldName,
i.rdb$index_name AS IndexName,
rc.rdb$constraint_name AS AltIndexName,
i.rdb$foreign_key AS IsForeignKey,
i.rdb$unique_flag AS IsUnique,
s.rdb$field_position+1 AS FieldPosition,
rc.rdb$deferrable AS Deferrable,
rc.rdb$initially_deferred AS InitiallyDeferred
FROM rdb$indices i
LEFT JOIN rdb$index_segments s ON (i.rdb$index_name = s.rdb$index_name)
LEFT JOIN rdb$relation_constraints rc ON (i.rdb$index_name = rc.rdb$index_name)
WHERE
i.rdb$system_flag = 0 AND
s.rdb$field_name IS NOT NULL");
using (var ds = this.ExecuteSelect(stmt))
{
var cList = new Utils.ChainedList <Index>();
foreach (DataRow dr in ds.Tables[0].Rows)
{
string index_name;
if (dr["AltIndexName"] == DBNull.Value)
{
index_name = dr["IndexName"].ToString().Trim();
}
else
{
index_name = dr["AltIndexName"].ToString().Trim();
}
var ix = new Index(this._schemaName, dr["TableName"].ToString().Trim(), index_name);
ix.IsUnique = (dr["IsUnique"] != DBNull.Value) ? true : false;
ix.AddColumn(Convert.ToInt32(dr["FieldPosition"]), dr["FieldName"].ToString().Trim());
Index tmp;
if (!cList.Add(ix, out tmp))
{
tmp.AddCoumns(ix.IndexOrderdColumnNames);
}
}
return(cList.ToList());
}
}
public override List <DBObjects.ForeignKey> GetForeignKeys(string schemaName)
{
var stmt = String.Format(
@"select
ccol.constname as key_name,
ccol.tabschema as child_schema_name,
ccol.tabname as child_table_name,
ccol.colname as child_column_name,
pcol.tabschema as parent_schema_name,
pcol.tabname as parent_table_name,
pcol.colname as parent_column_name,
ccol.colseq as ordinal_position,
fk.deleterule as delete_rule, -- A = NO ACTION, C = CASCADE, N = SET NULL, R = RESTRICT
fk.updaterule as update_rule -- A = NO ACTION, C = CASCADE, N = SET NULL, R = RESTRICT
from
syscat.references as fk,
syscat.keycoluse as pcol, -- The parent columns
syscat.keycoluse as ccol -- The child columns
where
fk.tabschema = '{0}'
and ccol.tabschema = fk.tabschema
and ccol.tabname = fk.tabname
and ccol.constname = fk.constname
and pcol.tabschema = fk.tabschema
and pcol.tabname = fk.reftabname
and pcol.constname = fk.refkeyname
and ccol.colseq = pcol.colseq
order by ccol.tabname, ccol.constname, ccol.colseq" , schemaName);
var ds = this.ExecuteSelect(stmt);
var cList = new Utils.ChainedList <ForeignKey>();
foreach (DataRow dr in ds.Tables[0].Rows)
{
var fk = new ForeignKey(dr["child_schema_name"].ToString(), dr["child_table_name"].ToString(), dr["key_name"].ToString(),
dr["parent_schema_name"].ToString(), dr["parent_table_name"].ToString());
fk.UpdateRule = this.ParseUpdateRule(dr["update_rule"].ToString());
fk.DeleteRule = this.ParseDeleteRule(dr["delete_rule"].ToString());
fk.AddColumn(Convert.ToInt32(dr["ordinal_position"]), dr["child_column_name"].ToString(), dr["parent_column_name"].ToString());
ForeignKey tmp;
if (!cList.Add(fk, out tmp))
{
tmp.AddColumns(fk.IndexOrderdColumnNames);
}
}
return(cList.ToList());
}
public override List <Index> GetIndices(string schemaName)
{
var stmt = String.Format(
@"select
'{0}' as schema_name,
tab.name as table_name,
inx.name as index_name,
col.name as column_name,
ixcol.key_ordinal as column_index,
inx.is_unique
from sys.objects as obj
inner join sys.indexes as inx
on obj.object_id = inx.object_id
inner join sys.index_columns as ixcol
on inx.object_id = ixcol.object_id
and inx.index_id = ixcol.index_id
inner join sys.columns as col
on obj.object_id = col.object_id
and ixcol.column_id = col.column_id
inner join sys.tables as tab
on obj.object_id = tab.object_id
where obj.schema_id = (select schema_id from sys.schemas where name = '{0}')
and inx.is_disabled = 0
and inx.name is not Null
and ixcol.is_included_column = 0",
schemaName);
var ds = this.ExecuteSelect(stmt);
var cList = new Utils.ChainedList <Index>();
foreach (DataRow dr in ds.Tables[0].Rows)
{
var index = new Index(dr["schema_name"].ToString(), dr["table_name"].ToString(), dr["index_name"].ToString());
index.IsUnique = Convert.ToBoolean(dr["is_unique"]);
index.AddColumn(Convert.ToInt32(dr["column_index"]), dr["column_name"].ToString());
Index tmp;
if (!cList.Add(index, out tmp))
{
tmp.AddCoumns(index.IndexOrderdColumnNames);
}
}
return(cList.ToList());
}
public override List <Unique> GetUniqueConstraints(string schemaName)
{
var stmt = String.Format(
@"select
ky.table_schema as schema_name,
ky.table_name,
ky.constraint_name as key_name,
ky.ordinal_position,
ky.column_name,
ky.constraint_name
from information_schema.key_column_usage as ky
where ky.table_schema = '{0}'
order by ky.constraint_name, ky.ordinal_position",
schemaName);
var ds = this.ExecuteSelect(stmt);
var cList = new Utils.ChainedList <Unique>();
var constraints = from c in this.GetConstraints(schemaName)
where c.ConstraintType == "UNIQUE"
select c;
var rows = from dr in ds.Tables[0].Rows.Cast <DataRow>()
join c in constraints on dr["table_name"].ToString() equals c.TableName
where dr["constraint_name"].ToString() == c.ConstraintName
select new { row = dr, constraint = c };
foreach (var item in rows)
{
var dr = item.row;
var uqSchemaName = dr["schema_name"].ToString();
var uqTableName = dr["table_name"].ToString();
var uqkKeyName = dr["key_name"].ToString();
var uq = new Unique(uqSchemaName, uqTableName, uqkKeyName);
uq.AddColumn(Convert.ToInt32(dr["ordinal_position"]), dr["column_name"].ToString());
Unique tmp;
if (!cList.Add(uq, out tmp))
{
tmp.AddCoumns(uq.IndexOrderdColumnNames);
}
}
return(cList.ToList());
}
public override List <DBObjects.ForeignKey> GetForeignKeys(string schemaName)
{
var stmt = String.Format(
@"SELECT
rc.rdb$constraint_name AS FkName,
rcc.rdb$relation_name AS ChildTable,
isc.rdb$field_name AS ChildColumn,
isc.rdb$field_position+1 AS ChildPosition,
rcp.rdb$relation_name AS ParentTable,
isp.rdb$field_name AS ParentColumn,
rc.rdb$update_rule AS UpdateRule,
rc.rdb$delete_rule AS DeleteRule,
rcc.rdb$deferrable AS Deferrable,
rcc.rdb$initially_deferred AS InitiallyDeferred
FROM
rdb$ref_constraints rc
INNER JOIN rdb$relation_constraints rcc on rc.rdb$constraint_name = rcc.rdb$constraint_name
INNER JOIN rdb$index_segments isc on rcc.rdb$index_name = isc.rdb$index_name
INNER JOIN rdb$relation_constraints rcp on rc.rdb$const_name_uq = rcp.rdb$constraint_name
INNER JOIN rdb$index_segments isp on rcp.rdb$index_name = isp.rdb$index_name;");
using (var ds = this.ExecuteSelect(stmt))
{
var cList = new Utils.ChainedList <ForeignKey>();
foreach (DataRow dr in ds.Tables[0].Rows)
{
var fk = new ForeignKey(this._schemaName, dr["ChildTable"].ToString().Trim(), dr["FkName"].ToString().Trim(), this._schemaName, dr["ParentTable"].ToString().Trim());
fk.DeleteRule = this.ParseDeleteRule(dr["DeleteRule"].ToString().Trim());
fk.UpdateRule = this.ParseUpdateRule(dr["UpdateRule"].ToString().Trim());
fk.InitialMode = (this.ParseBoolean(dr["InitiallyDeferred"].ToString().Trim())) ? InitialMode.InitiallyDeferred : InitialMode.InitiallyImmediate;
fk.Deferrability = (this.ParseBoolean(dr["Deferrable"].ToString().Trim())) ? Deferrability.Deferrable : Deferrability.NotDeferrable;
fk.AddColumn(Convert.ToInt32(dr["ChildPosition"]), dr["ChildColumn"].ToString().Trim(), dr["ParentColumn"].ToString().Trim());
ForeignKey tmp;
if (!cList.Add(fk, out tmp))
{
tmp.AddColumns(fk.IndexOrderdColumnNames);
}
}
return(cList.ToList());
}
}
public override List <DBObjects.Index> GetIndices(string schemaName)
{
var stmt = String.Format(
@"select
inx.tabschema as schema_name,
inx.tabname as table_name,
inx.indname as index_name,
inxcol.colname as column_name,
inxcol.colseq as column_position,
case (inx.uniquerule)
when 'P' then 'Y'
when 'U' then 'Y'
when 'D' then 'N'
end as is_unique
from
syscat.indexes as inx,
syscat.indexcoluse inxcol
where
inxcol.indschema = inx.tabschema
and inxcol.indname = inx.indname
and inx.tabschema = '{0}'
and inx.indextype in ('BLOK', 'CLUS', 'DIM', 'REG')
order by inxcol.indname, inxcol.colseq" , schemaName);
var ds = this.ExecuteSelect(stmt);
var cList = new Utils.ChainedList <Index>();
foreach (DataRow dr in ds.Tables[0].Rows)
{
var index = new Index(dr["schema_name"].ToString(), dr["table_name"].ToString(), dr["index_name"].ToString());
index.IsUnique = dr["is_unique"].ToString().ToLower() == "y";
index.AddColumn(Convert.ToInt32(dr["column_position"]), dr["column_name"].ToString());
Index tmp;
if (!cList.Add(index, out tmp))
{
tmp.AddCoumns(index.IndexOrderdColumnNames);
}
}
return(cList.ToList());
}
public override List <PrimaryKey> GetPrimaryKeys(string schemaName)
{
var stmt = String.Format(
@"select
ky.table_schema as schema_name,
ky.table_name,
ky.constraint_name as key_name,
ky.ordinal_position,
ky.column_name
from information_schema.key_column_usage as ky
where table_schema = '{0}'
order by ky.table_name, ky.ordinal_position",
schemaName);
var constraints = from c in GetConstraints(schemaName)
where c.ConstraintType == "PRIMARY KEY"
select c;
var ds = this.ExecuteSelect(stmt);
var rows = from dr in ds.Tables[0].Rows.Cast <DataRow>()
join c in constraints on dr["key_name"].ToString() equals c.ConstraintName
where c.TableName == dr["table_name"].ToString()
select dr;
var cList = new Utils.ChainedList <PrimaryKey>();
foreach (DataRow dr in rows)
{
var pk = new PrimaryKey(dr["schema_name"].ToString(), dr["table_name"].ToString(), dr["key_name"].ToString());
pk.AddColumn(Convert.ToInt32(dr["ordinal_position"]), dr["column_name"].ToString());
PrimaryKey tmp;
if (!cList.Add(pk, out tmp))
{
tmp.AddCoumns(pk.IndexOrderdColumnNames);
}
}
return(cList.ToList());
}
public override List <ForeignKey> GetForeignKeys(string schemaName)
{
var stmt = String.Format(
@"select
fky.constraint_schema as schema_name,
fky.table_name,
fky.constraint_name as key_name,
fky.column_name,
fky.referenced_table_schema as pri_schema_name,
fky.referenced_table_name as pri_table_name,
fky.referenced_column_name as pri_column_name,
fky.ordinal_position,
rco.update_rule,
rco.delete_rule
from (select * from information_schema.key_column_usage where table_schema = '{0}') as fky
inner join (select constraint_schema as table_schema,
constraint_name,
table_name,
referenced_table_name,
unique_constraint_name,
update_rule,
delete_rule
from information_schema.referential_constraints
WHERE CONSTRAINT_SCHEMA = '{0}'
) as rco
using (table_schema, constraint_name, table_name, referenced_table_name)",
schemaName);
// Hack, to avoide the missing referential_constraints table on old MySQL Versions, default to "Set Default" rules
if (this._runningVersion != null && this._runningVersion.Major == 5 && this._runningVersion.Minor == 0)
{
stmt = String.Format(@"select
constraint_schema as schema_name,
table_name,
constraint_name as key_name,
column_name,
referenced_table_schema as pri_schema_name,
referenced_table_name as pri_table_name,
referenced_column_name as pri_column_name,
ordinal_position,
'RESTRICT' as update_rule,
'CASCADE' as delete_rule
from information_schema.key_column_usage where table_schema = '{0}'
and constraint_name <> 'PRIMARY'",
schemaName);
}
var constraints = from c in GetConstraints(schemaName)
where c.ConstraintType == "FOREIGN KEY"
select c;
var ds = this.ExecuteSelect(stmt);
var rows = from dr in ds.Tables[0].Rows.Cast <DataRow>()
join c in constraints on dr["table_name"].ToString() equals c.TableName
where c.ConstraintName == dr["key_name"].ToString()
select dr;
var cList = new Utils.ChainedList <ForeignKey>();
foreach (DataRow dr in rows)
{
var fk = new ForeignKey(dr["schema_name"].ToString(), dr["table_name"].ToString(), dr["key_name"].ToString(),
dr["pri_schema_name"].ToString(), dr["pri_table_name"].ToString());
fk.UpdateRule = this.ParseUpdateRule(dr["update_rule"].ToString());
fk.DeleteRule = this.ParseDeleteRule(dr["delete_rule"].ToString());
// fk.IsDeferrable = this.ParseBoolean(dr["is_deferrable"].ToString()); // TODO NOT EXTRACTED FROM MYSQL
fk.AddColumn(Convert.ToInt32(dr["ordinal_position"]), dr["column_name"].ToString(), dr["pri_column_name"].ToString());
ForeignKey tmp;
if (!cList.Add(fk, out tmp))
{
tmp.AddColumns(fk.IndexOrderdColumnNames);
}
}
return(cList.ToList());
}
