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
from information_schema.key_column_usage as ky
inner join information_schema.table_constraints as co
on ky.constraint_name = co.constraint_name
and ky.table_schema = co.table_schema
and ky.table_name = co.table_name
where co.constraint_schema = '{0}'
and co.constraint_type = 'UNIQUE'
order by ky.constraint_name, ky.ordinal_position",
schemaName);
var ds = this.ExecuteSelect(stmt);
var cList = new Utils.ChainedList <Unique>();
foreach (DataRow dr in ds.Tables[0].Rows)
{
var uq = new Unique(dr["schema_name"].ToString(), dr["table_name"].ToString(), dr["key_name"].ToString());
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 <Unique> GetUniqueConstraints(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 = 'U'
order by ucc.table_name, ucc.position",
schemaName);
using (var ds = this.ExecuteSelect(stmt))
{
var cList = new Utils.ChainedList <Unique>();
foreach (DataRow dr in ds.Tables[0].Rows)
{
var uq = new Unique(dr["schema_name"].ToString(), dr["table_name"].ToString(), dr["constraint_name"].ToString());
uq.AddColumn(Convert.ToInt32(dr["position"]), dr["column_name"].ToString());
Unique tmp;
if (!cList.Add(uq, out tmp))
{
tmp.AddCoumns(uq.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 <DBObjects.Unique> GetUniqueConstraints(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 = 'U'
order by inxcol.indname, inxcol.colseq" , schemaName);
var ds = this.ExecuteSelect(stmt);
var cList = new Utils.ChainedList <Unique>();
foreach (DataRow dr in ds.Tables[0].Rows)
{
var uk = new Unique(dr["schema_name"].ToString(), dr["table_name"].ToString(), dr["index_name"].ToString());
uk.AddColumn(Convert.ToInt32(dr["column_position"]), dr["column_name"].ToString());
Unique tmp;
if (!cList.Add(uk, out tmp))
{
tmp.AddCoumns(uk.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());
}