/// <summary> /// Column additional information /// </summary> private void FillColumnAdditionalInfo(DbColumn toSetColumn, string tableName, string columnName) { DataRow[] addInfo = Cache_All_Constraints.Select(string.Format("OWNER='{0}' AND TABLE_NAME='{1}' AND CONSTRAINT_TYPE='{2}' AND COLUMN_NAME='{3}'", SpecificOwner.ToUpper(), tableName, STR_ConstraintType_Primarykey, columnName)); if (addInfo != null && addInfo.Length > 0) { toSetColumn.PrimaryKey = true; } }
/// <summary> /// Reads specified table foreign keys. /// </summary> private void ApplyTablesForeignKeys(List <DbTable> tables) { if (Cache_ForeignKeys.Rows.Count == 0) { return; } // adding the foreign keys foreach (DataRow keysDataRow in Cache_ForeignKeys.Rows) { var foreignKeyTableName = keysDataRow["FOREIGN_KEY_TABLE_NAME"].ToString(); var primaryKeyTableName = keysDataRow["PRIMARY_KEY_TABLE_NAME"].ToString(); var foreignKeyConstraintName = keysDataRow["FOREIGN_KEY_CONSTRAINT_NAME"].ToString(); var primaryKeyConstraintName = keysDataRow["PRIMARY_KEY_CONSTRAINT_NAME"].ToString(); string foreignKeyColumnName = null; string primaryKeyColumnName = null; // read the columns info DataRow[] columnInfo; // reading foreign key column info columnInfo = Cache_All_Constraints.Select(string.Format("CONSTRAINT_NAME='{0}'", foreignKeyConstraintName)); if (columnInfo != null && columnInfo.Length > 0) { foreignKeyColumnName = columnInfo[0]["COLUMN_NAME"].ToString(); } // reading primary key column info columnInfo = Cache_All_Constraints.Select(string.Format("CONSTRAINT_NAME='{0}'", primaryKeyConstraintName)); if (columnInfo != null && columnInfo.Length > 0) { primaryKeyColumnName = columnInfo[0]["COLUMN_NAME"].ToString(); } // there should be column names! if (foreignKeyColumnName == null || primaryKeyColumnName == null) { continue; } // find schema tables model var foreignKeyTable = FindTable(tables, foreignKeyTableName); var primaryKeyTable = FindTable(tables, primaryKeyTableName); // there should be tables! if (foreignKeyTable == null || primaryKeyTable == null) { continue; } if (foreignKeyTable != null) { // foreign key many end var oneMultiplicityKey = new DbForeignKey() { ForeignKeyName = foreignKeyConstraintName, LocalColumnName = foreignKeyColumnName, ForeignColumnName = primaryKeyColumnName, ForeignTableName = primaryKeyTableName, Multiplicity = DbForeignKey.ForeignKeyMultiplicity.OneToMany }; // check if it is already there if (foreignKeyTable.ForeignKeys.Exists(x => x.ForeignKeyName == oneMultiplicityKey.ForeignKeyName)) { continue; } //oneMultiplicityKey.UpdateAction = // ConvertOracleForeignKeyAction(keysDataRow["UPDATE_RULE"].ToString()); oneMultiplicityKey.DeleteAction = ConvertOracleForeignKeyAction(keysDataRow["DELETE_RULE"].ToString()); // to the list foreignKeyTable.ForeignKeys.Add(oneMultiplicityKey); // apply local column DbColumn localColumn = foreignKeyTable.FindColumnDb(oneMultiplicityKey.LocalColumnName); oneMultiplicityKey.LocalColumn = localColumn; if (!localColumn.PrimaryKey) { localColumn.IsReferenceKey = true; localColumn.IsReferenceKeyTable = primaryKeyTable; } if (primaryKeyTable != null) { // foreign table of that! oneMultiplicityKey.ForeignTable = primaryKeyTable; // apply foreign column DbColumn foreignColumn = primaryKeyTable.FindColumnDb(oneMultiplicityKey.ForeignColumnName); oneMultiplicityKey.ForeignColumn = foreignColumn; } else { oneMultiplicityKey.ForeignTable = null; oneMultiplicityKey.ForeignColumn = null; } } if (primaryKeyTable != null) { // foreign key many end var manyMultiplicityKey = new DbForeignKey() { ForeignKeyName = primaryKeyConstraintName, LocalColumnName = primaryKeyColumnName, ForeignColumnName = foreignKeyColumnName, ForeignTableName = foreignKeyTableName, Multiplicity = DbForeignKey.ForeignKeyMultiplicity.ManyToOne }; // check if it is already there if (primaryKeyTable.ForeignKeys.Exists(x => x.ForeignKeyName == manyMultiplicityKey.ForeignKeyName)) { continue; } //manyMultiplicityKey.UpdateAction = // ConvertOracleForeignKeyAction(keysDataRow["UPDATE_RULE"].ToString()); manyMultiplicityKey.DeleteAction = ConvertOracleForeignKeyAction(keysDataRow["DELETE_RULE"].ToString()); // to the list primaryKeyTable.ForeignKeys.Add(manyMultiplicityKey); // apply local column DbColumn localColumn = primaryKeyTable.FindColumnDb(manyMultiplicityKey.LocalColumnName); manyMultiplicityKey.LocalColumn = localColumn; if (!localColumn.PrimaryKey) { localColumn.IsReferenceKey = true; localColumn.IsReferenceKeyTable = primaryKeyTable; } if (foreignKeyTable != null) { // foreign table of that! manyMultiplicityKey.ForeignTable = foreignKeyTable; // apply foreign column DbColumn foreignColumn = foreignKeyTable.FindColumnDb(manyMultiplicityKey.ForeignColumnName); manyMultiplicityKey.ForeignColumn = foreignColumn; } else { manyMultiplicityKey.ForeignTable = null; manyMultiplicityKey.ForeignColumn = null; } } } }
/// <summary> /// Reads tables index keys /// </summary> private void ApplyTablesConstraintKeys(List <DbTable> tables, OracleServerVersions sqlVersion) { if (Cache_Indexes.Rows.Count == 0) { return; } // find description if there is any foreach (var table in tables) { // filter row Cache_Indexes.DefaultView.RowFilter = string.Format("TABLE_NAME='{0}'", table.TableName); // fetch findings, if there is any foreach (DataRowView keysDataRow in Cache_Indexes.DefaultView) { // found table ! DataRow keyRow = keysDataRow.Row; var indexName = keyRow["INDEX_NAME"].ToString(); // it should not be a primary key! DataRow[] indexColumnInfo = Cache_IndexColumns.Select(string.Format("INDEX_NAME='{0}'", indexName)); // column information if (indexColumnInfo == null || indexColumnInfo.Length == 0) { continue; } var columnName = indexColumnInfo[0]["COLUMN_NAME"].ToString(); // check if this is aprimary key! DataRow[] primaryKeyInfo = Cache_All_Constraints.Select(string.Format("OWNER='{0}' AND TABLE_NAME='{1}' AND CONSTRAINT_TYPE='{2}' AND COLUMN_NAME='{3}'", SpecificOwner.ToUpper(), table.TableName, STR_ConstraintType_Primarykey, columnName)); if (primaryKeyInfo != null && primaryKeyInfo.Length > 0) { // sorry! this is a primary key and it is already added // next! continue; } const string STR_IndexUniqueName = "UNIQUE"; const string STR_IndexNonUniqueName = "NONUNIQUE"; // constraint Key and its uniqueness var constraintKey = new DbConstraintKey() { IsUnique = (keyRow["UNIQUENESS"].ToString() == STR_IndexUniqueName), KeyColumnName = columnName, KeyName = indexName }; // constraint keys table.ConstraintKeys.Add(constraintKey); // find key column DbColumn keyColumn = table.FindColumnDb(constraintKey.KeyColumnName); constraintKey.KeyColumn = keyColumn; } } }