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; }