public static SMReference Generate(DBSchema schema, DBTable table)
{
var info = new SMReference
{
SourceTable = "",
Name = "constraint_name",
Schema = "schema_name",
Table = "table_name",
Column = "column_name",
ReferenceSchema = "reference_schema_name",
ReferenceTable = "reference_table_name",
ReferenceColumn = "reference_column_name",
Filter = @"SELECT
KCU1.CONSTRAINT_NAME AS constraint_name
,KCU1.CONSTRAINT_SCHEMA AS schema_name
,KCU1.TABLE_NAME AS table_name
,KCU1.COLUMN_NAME AS column_name
,KCU2.CONSTRAINT_NAME AS reference_constraint_name
,KCU1.CONSTRAINT_SCHEMA AS reference_schema
,KCU2.TABLE_NAME AS reference_table_name
,KCU2.COLUMN_NAME AS reference_column_name
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS RC
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU1
ON KCU1.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG
AND KCU1.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA
AND KCU1.CONSTRAINT_NAME = RC.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU2
ON KCU2.CONSTRAINT_CATALOG = RC.UNIQUE_CONSTRAINT_CATALOG
AND KCU2.CONSTRAINT_SCHEMA = RC.UNIQUE_CONSTRAINT_SCHEMA
AND KCU2.CONSTRAINT_NAME = RC.UNIQUE_CONSTRAINT_NAME
AND KCU2.ORDINAL_POSITION = KCU1.ORDINAL_POSITION "
};
if (schema.Connection.Schema != null || table != null)
{
var schemaFilter = schema.Connection.Schema == null ? null : string.Format("RC.CONSTRAINT_SCHEMA='{0}'", schema.Connection.Schema);
var tableFilter = table == null ? null : string.Format("KCU1.TABLE_NAME='{0}'", table.Name);
info.Filter += string.Format("\nWHERE {0}{1}{2}", schemaFilter, schemaFilter != null && tableFilter != null ? " AND " : " ", tableFilter);
}
if (schema.Connection.System == DBSystem.SQLite)
{
info.Filter = null;//TODO
}
if (schema.Connection.System == DBSystem.Oracle)
{
info.Filter = @"SELECT
a.constraint_name,
c.owner as schema_name,
a.table_name,
a.column_name,
c_pk.constraint_name as reference_constraint_name
c.r_owner as reference_schema,
c_pk.table_name as rererence_table_name,
c_pk.column_name as rererence_column_name
FROM all_cons_columns a
JOIN all_constraints c ON a.owner = c.owner
AND a.constraint_name = c.constraint_name
JOIN all_constraints c_pk ON c.r_owner = c_pk.owner
AND c.r_constraint_name = c_pk.constraint_name
WHERE c.constraint_type = 'R'";
}
return(info);
}
public static void LoadRefrences(DBSchema schema, DBTable table = null)
{
var info = SMReference.Generate(schema, table);
info.Parse(schema, table);
}
