public PrimalKey(string relName, ColumnKey attr)
{
TableName = relName;
Attribute = attr;
Attributes.Add(attr);
}
public Column GetTableAttributeCached(ColumnKey key)
{
if (Columns.ContainsKey(key))
return Columns[key];
throw new Exception("not found: " + key);
}
private void GetTableConstraintsCached(string tablename, Table currentTable)
{
#region referential constraints
using (
SqlDataRecordExtended rs2 = _conn.ExecuteDataReader(
@"
SELECT
con.CONSTRAINT_NAME,
con.UNIQUE_CONSTRAINT_NAME , --con.*,
tcon.table_name tcon_table_name,
pkcon.table_name pkcon_table_name,
con_col.column_name con_col_column_name,
pkcon_col.column_name pkcon_col_column_name
FROM
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS con
JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tcon ON con.CONSTRAINT_NAME = tcon.CONSTRAINT_NAME
JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS pkcon ON
con.UNIQUE_CONSTRAINT_NAME = pkcon.CONSTRAINT_NAME
AND pkcon.CONSTRAINT_TYPE = 'PRIMARY KEY'
JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE con_col ON con_col.CONSTRAINT_NAME = con.CONSTRAINT_NAME
JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE pkcon_col ON pkcon_col.CONSTRAINT_NAME = pkcon.CONSTRAINT_NAME
WHERE tcon.table_name = @id
",
new {id = tablename}))
{
ForeignKeyConstraint currentConstraint = null;
while (rs2.Read())
{
string constraintName1 = rs2.GetString("CONSTRAINT_NAME");
#region if nec. create constraint (class)
if (currentConstraint == null || currentConstraint.ConstraintName != constraintName1)
{
currentConstraint = new ForeignKeyConstraint(constraintName1, currentTable.TableName);
ForeignKeys.Add(currentConstraint);
}
#endregion
#region constraint keys
var key = new ColumnKey(
rs2.GetString("tcon_table_name"),
rs2.GetString("con_col_column_name"));
var fkey = new ColumnKey(
rs2.GetString("pkcon_table_name"),
rs2.GetString("pkcon_col_column_name"));
currentConstraint.ConstraintKeys.Add(new ForeignKeyColumn(key, fkey));
#endregion
}
}
#endregion
#region primary keys
using (SqlDataRecordExtended rs2 = _conn.ExecuteDataReader(
@"
SELECT
pkcon.constraint_name,
pkcon.table_name,
pkcon_col.column_name
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS pkcon
join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE pkcon_col on pkcon_col.CONSTRAINT_NAME = pkcon.CONSTRAINT_NAME
/*join INFORMATION_SCHEMA.COLUMNS pkcon_colc on
pkcon_colc.table_name = pkcon_col.table_name
and pkcon_colc.COLUMN_NAME = pkcon_col.COLUMN_NAME*/
WHERE
-- con.UNIQUE_CONSTRAINT_NAME = pkcon.CONSTRAINT_NAME
pkcon.CONSTRAINT_TYPE = 'PRIMARY KEY'
AND pkcon.table_name = @id
",
new {id = tablename}))
{
//_conn.ExecuteDataReader("sp_jgljsdklfjsdkljf", new List<object>(){
// "param1", 1,
// "param2", 2,
// "param3", 3});
PrimaryKeyConstraint currentConstraint = null;
while (rs2.Read())
{
string conoid = rs2.GetString("CONSTRAINT_NAME");
#region if nec. create constraint (class)
if (currentConstraint == null || currentConstraint.ConstraintName != conoid)
{
string constraintName = rs2.GetString("CONSTRAINT_NAME");
currentConstraint = new PrimaryKeyConstraint(constraintName, tablename);
if (currentTable.PrimaryKey != null) throw new Exception("multiple primary keys??");
currentTable.PrimaryKey = currentConstraint;
}
#endregion
#region constraint keys
currentConstraint.ConstraintKeys.Add(new ColumnKey(
rs2.GetString("table_name"),
rs2.GetString("column_name")));
#endregion
}
}
#endregion
#region unique
#if (true)
{
using (SqlDataRecordExtended rs2 = _conn.ExecuteDataReader(
@"
select con.CONSTRAINT_NAME,con.UNIQUE_CONSTRAINT_NAME , --con.*,
tcon.table_name tcon_table_name,
pkcon.table_name pkcon_table_name,
con_col.column_name con_col_column_name,
pkcon_col.column_name pkcon_col_column_name
--con_colc.ORDINAL_POSITION con_colc_ORDINAL_POSITION,
--pkcon_colc.ORDINAL_POSITION pkcon_colc_ORDINAL_POSITION
from
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS con
join INFORMATION_SCHEMA.TABLE_CONSTRAINTS tcon on con.CONSTRAINT_NAME = tcon.CONSTRAINT_NAME
join INFORMATION_SCHEMA.TABLE_CONSTRAINTS pkcon on
con.UNIQUE_CONSTRAINT_NAME = pkcon.CONSTRAINT_NAME
and pkcon.CONSTRAINT_TYPE = 'PRIMARY KEY'
join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE con_col on con_col.CONSTRAINT_NAME = con.CONSTRAINT_NAME
join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE pkcon_col on pkcon_col.CONSTRAINT_NAME = pkcon.CONSTRAINT_NAME
/*join INFORMATION_SCHEMA.COLUMNS con_colc on
con_colc.table_name = con_col.table_name
and con_colc.COLUMN_NAME = con_col.COLUMN_NAME
join INFORMATION_SCHEMA.COLUMNS pkcon_colc on
pkcon_colc.table_name = pkcon_col.table_name
and pkcon_colc.COLUMN_NAME = pkcon_col.COLUMN_NAME*/
where tcon.table_name = @id
",
new {id = tablename}))
{
Model.UniqueConstraint currentConstraint = null;
while (rs2.Read())
{
string conoid = rs2.GetString("CONSTRAINT_NAME");
if (currentConstraint == null || currentConstraint.ConstraintName != conoid)
{
string ConstraintName = rs2.GetString("CONSTRAINT_NAME");
currentConstraint = new UniqueConstraint(ConstraintName, tablename);
UniqueConstraints.Add(currentConstraint);
}
currentConstraint.ConstraintKeys.Add(new ColumnKey(rs2.GetString("tcon_table_name"),
rs2.GetString("con_col_column_name")));
}
}
}
#endif
#endregion
}
private bool TryGetPrimaryKeyConstraint(IDictionary<string,Table> Tables,ColumnKey attr, out PrimaryKeyConstraint primaryKey)
{
primaryKey = Tables[attr.TableName].PrimaryKey.ConstraintKeys.Contains(attr)
? Tables[attr.TableName].PrimaryKey
: null;
return primaryKey != null;
}
/// <summary>
/// not safe if the db contains foreign key loops
/// </summary>
/// <param name="attr"></param>
/// <param name="className"></param>
/// <returns></returns>
public bool TryGetPrimalKey(IEnumerable<Table> tables, IEnumerable<ForeignKeyConstraint> foreignKeys, Dictionary<ColumnKey, PrimalKey> PrimalKeys, ColumnKey attr, out string className)
{
#region first try to get primal key
if (PrimalKeys.ContainsKey(attr))
{
className = PrimalKeys[attr].ClassTypeName;
return true;
}
#endregion
#region find the table owning the attribute and then try to find a foreign key pair
foreach (ForeignKeyConstraint f in foreignKeys.Where(fk => fk.TableName == attr.TableName))
{
foreach (var ta in f.ConstraintKeys)
{
if (ta.From.TableName == attr.TableName && ta.From.ColumnName == attr.ColumnName)
{
#region foreign key found
var foreignKeyV = new ColumnKey
(ta.To.TableName, ta.To.ColumnName);
if (PrimalKeys.ContainsKey(foreignKeyV))
{
className = PrimalKeys[foreignKeyV].ClassTypeName;
return true;
}
// recurse!
return TryGetPrimalKey(tables, foreignKeys, PrimalKeys, foreignKeyV, out className);
#endregion
}
}
}
#endregion
// primal key not found
className = null;
return false;
}
public static extern void double_greater_equal(QueryHandle queryPtr, ColumnKey columnKey, Double value, out NativeException ex);
public static extern void float_greater_equal(QueryHandle queryPtr, ColumnKey columnKey, Single value, out NativeException ex);
public static extern void long_greater_equal(QueryHandle queryPtr, ColumnKey columnKey, Int64 value, out NativeException ex);
