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