/// <summary> /// Removes duplicate table constraints, PK > UK > IX /// </summary> private void NormalizeTablesConstraintKeys(List <DbTable> result, OracleServerVersions sqlVersion) { // look in tables list foreach (DbTable table in result) { if (table.ConstraintKeys.Count == 0) { continue; } StringCollection duplicateConstraints = new StringCollection(); // fetching the contraints keys for (int j = table.ConstraintKeys.Count - 1; j >= 0; j--) { var constraintKey = table.ConstraintKeys[j]; // no primary keys are allowed if (constraintKey.KeyColumn != null && constraintKey.KeyColumn.PrimaryKey) { // There is no need in keeping the primary key table.ConstraintKeys.RemoveAt(j); continue; } // first look in the foreign keys! int index = table.ForeignKeys.FindIndex(x => x.LocalColumnName == constraintKey.KeyColumnName); if (index != -1) { // this is a foreign key and should not be here table.ConstraintKeys.RemoveAt(j); continue; } // if this is not a unique key // seach for a unique one if it is there if (constraintKey.IsUnique == false) { index = table.ConstraintKeys.FindIndex(x => x.KeyColumnName == constraintKey.KeyColumnName && x.IsUnique == true); if (index != -1) { // the same and the Unique key is already there! table.ConstraintKeys.RemoveAt(j); continue; } } else { var notUniqueKeys = table.ConstraintKeys.FindAll(x => x.KeyColumnName == constraintKey.KeyColumnName && x.IsUnique == false); if (notUniqueKeys != null && notUniqueKeys.Count > 0) { // remove them notUniqueKeys.ForEach(x => table.ConstraintKeys.Remove(x)); continue; } } // look for duplication constraint key if (duplicateConstraints.Contains(constraintKey.KeyColumnName)) { // the column with index is already there table.ConstraintKeys.RemoveAt(j); continue; } // all to the constraint key list duplicateConstraints.Add(constraintKey.KeyColumnName); } } }
/// <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; } } }
/// <summary> /// Removes duplicate table constraints, PK > UK > IX /// </summary> private void NormalizeTablesConstraintKeys(List<DbTable> result, OracleServerVersions sqlVersion) { // look in tables list foreach (DbTable table in result) { if (table.ConstraintKeys.Count == 0) { continue; } StringCollection duplicateConstraints = new StringCollection(); // fetching the contraints keys for (int j = table.ConstraintKeys.Count - 1; j >= 0; j--) { var constraintKey = table.ConstraintKeys[j]; // no primary keys are allowed if (constraintKey.KeyColumn != null && constraintKey.KeyColumn.PrimaryKey) { // There is no need in keeping the primary key table.ConstraintKeys.RemoveAt(j); continue; } // first look in the foreign keys! int index = table.ForeignKeys.FindIndex(x => x.LocalColumnName == constraintKey.KeyColumnName); if (index != -1) { // this is a foreign key and should not be here table.ConstraintKeys.RemoveAt(j); continue; } // if this is not a unique key // seach for a unique one if it is there if (constraintKey.IsUnique == false) { index = table.ConstraintKeys.FindIndex(x => x.KeyColumnName == constraintKey.KeyColumnName && x.IsUnique == true); if (index != -1) { // the same and the Unique key is already there! table.ConstraintKeys.RemoveAt(j); continue; } } else { var notUniqueKeys = table.ConstraintKeys.FindAll(x => x.KeyColumnName == constraintKey.KeyColumnName && x.IsUnique == false); if (notUniqueKeys != null && notUniqueKeys.Count > 0) { // remove them notUniqueKeys.ForEach(x => table.ConstraintKeys.Remove(x)); continue; } } // look for duplication constraint key if (duplicateConstraints.Contains(constraintKey.KeyColumnName)) { // the column with index is already there table.ConstraintKeys.RemoveAt(j); continue; } // all to the constraint key list duplicateConstraints.Add(constraintKey.KeyColumnName); } } }
/// <summary> /// Reads tables schema from database /// </summary> private List <DbTable> ReadTables(List <DbView> viewList) { List <DbTable> result = new List <DbTable>(); string[] restrictions = null; if (!string.IsNullOrWhiteSpace(this.SpecificOwner)) { restrictions = new string[] { SpecificOwner.ToUpper() }; } using (DataTable tables = _dbConnection.GetSchema("TABLES", restrictions)) { foreach (DataRow row in tables.Rows) { string tableName = row["TABLE_NAME"].ToString(); if (!IsTableSelected(tableName)) { continue; } var jumpToNext = false; // search in views about this foreach (var view in viewList) { if (view.TableName == tableName) { jumpToNext = true; // we ignore view here break; } } if (jumpToNext) { continue; } // View columns List <DbColumn> columns = ReadColumns(tableName); // read columns description if (ReadColumnsDescription) { ApplyColumnsDescription(tableName, columns); } // new table var dbTable = new DbTable(tableName, columns); // table schema dbTable.OwnerName = row["OWNER"].ToString(); // add to results result.Add(dbTable); } // correct tables name by case sesitivity usage! AssignCaseSensitiveTablesName(result); ApplyTablesSequenceNames(result); // detect the sql server version OracleServerVersions dbVersion = DetectVersion(_dbConnection); if (ReadConstraintKeys) { // The constraint keys will read here ApplyTablesConstraintKeys(result, dbVersion); } // it is time to read foreign keys! // foreign keys are requested? if (ReadTablesForeignKeys) { ApplyTablesForeignKeys(result); } // Normalize the constraints keys NormalizeTablesConstraintKeys(result, dbVersion); if (ReadTablesForeignKeys) { ApplyDetectedOneToOneRelation(result); } } return(result); }
/// <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; } } }