private string GetFKModifier(bool multiplefksOnSameTable, MetaDataForeignKey fk)
{
string cols = "";
if (!multiplefksOnSameTable)
return cols;
cols = fk.BaseColumnNames.Aggregate("", (current, bcN) => current + bcN);
cols = cols.TrimEnd(' ', ',');
if (String.IsNullOrEmpty(cols))
cols = Sequenciator++.ToString();
return cols;
}
public List<MetaDataForeignKey> GetTableForeignKeys(string tableName, string serviceName, bool getParentFKs, bool getChildrenFks)
{
string schema, name, sql;
DataTable dttTable;
GetSchemaAndName(tableName, serviceName, out schema, out name);
List<MetaDataForeignKey> fks = new List<MetaDataForeignKey>();
if (getParentFKs)
{
sql = String.Format("SELECT c.constraint_name, c.r_constraint_name, c.r_owner, c_fk.owner, c_fk.table_name r_table_name " +
"FROM all_constraints c " +
"JOIN all_constraints c_fk ON c.r_owner = c_fk.owner " +
"AND c.r_constraint_name = c_fk.constraint_name " +
"WHERE c.constraint_type = 'R' " +
"AND c.table_name = '{1}' " +
"AND c.owner = '{0}' ", schema, name);
dttTable = DbCustom.GetTable(sql, serviceName);
foreach (DataRow row in dttTable.Rows)
{
string thatOwner = row["R_OWNER"].ToString();
string thatTableName = row["R_TABLE_NAME"].ToString();
var thisConstraint = row["CONSTRAINT_NAME"].ToString();
var thatConstraint = row["R_CONSTRAINT_NAME"].ToString();
string sqlThisConstraint = String.Format("SELECT COLUMN_NAME FROM ALL_CONS_COLUMNS WHERE owner = '{0}' AND constraint_name = '{1}' ", schema, thisConstraint);
string sqlThatConstraint = String.Format("SELECT COLUMN_NAME FROM ALL_CONS_COLUMNS WHERE owner = '{0}' AND constraint_name = '{1}' ", thatOwner, thatConstraint);
var fkToAdd = new MetaDataForeignKey {IsParent = true, Name = thisConstraint};
DataTable dttTmp = DbCustom.GetTable(sqlThisConstraint, serviceName);
foreach (DataRow thisRow in dttTmp.Rows)
fkToAdd.BaseColumnNames.Add(thisRow["COLUMN_NAME"].ToString());
dttTmp = DbCustom.GetTable(sqlThatConstraint, serviceName);
fkToAdd.Reference.TableName = thatTableName;
fkToAdd.Reference.Owner = thatOwner;
foreach (DataRow thatRow in dttTmp.Rows)
fkToAdd.Reference.ColumnNames.Add(thatRow["COLUMN_NAME"].ToString());
fks.Add(fkToAdd);
}
}
if (getChildrenFks)
{
sql = string.Format("Select c1.constraint_name, c1.owner, c1.table_name " +
"FROM all_constraints c1, all_constraints c2 " +
"WHERE c1.constraint_type = 'R' " +
"AND c2.constraint_type IN ('P', 'U') " +
"AND c2.owner = '{0}' " +
"AND c2.table_name = '{1}' " +
"AND c1.r_owner = c2.owner " +
"AND c1.r_constraint_Name = c2.constraint_name", schema, name);
dttTable = DbCustom.GetTable(sql, serviceName);
fks.AddRange(from DataRow r in dttTable.Rows
select new MetaDataForeignKey
{
Name = r["CONSTRAINT_NAME"].ToString(),
IsParent = false,
Reference = new MetaDataForeignKeyReference
{
TableName = r["TABLE_NAME"].ToString(),
Owner = r["OWNER"].ToString()
},
});
}
return fks;
}
private bool IsMultipleFksToSameTable(List<MetaDataForeignKey> fksUsed, MetaDataForeignKey fkBase)
{
List<string> referencedTables = new List<string>();
foreach (var key in fksUsed)
{
if (!referencedTables.Contains(key.Reference.TableName))
referencedTables.Add(key.Reference.TableName);
else
return key.Reference.TableName == fkBase.Reference.TableName;
}
return false;
}
