/// <summary>
/// Reads tables list. This method requires views list to prevent from conflict!
/// </summary>
private StringCollection ReadTablesList(StringCollection viewsList)
{
StringCollection result = new StringCollection();
string[] restrictions = null;
if (!string.IsNullOrWhiteSpace(this.SpecificOwner))
{
restrictions = new string[] { SpecificOwner.ToUpper() }
}
;
using (DataTable views = _dbConnection.GetSchema("TABLES", restrictions))
{
foreach (DataRow row in views.Rows)
{
string tableName = row["TABLE_NAME"].ToString();
// search in views about this
if (viewsList.Contains(tableName))
{
continue;
}
// add to results
result.Add(tableName);
}
}
return(result);
}
/// <summary>
/// Reads views schema from database
/// </summary>
private List <DbView> ReadViews()
{
List <DbView> result = new List <DbView>();
string[] restrictions = null;
if (!string.IsNullOrWhiteSpace(this.SpecificOwner))
{
restrictions = new string[] { SpecificOwner.ToUpper() };
}
using (DataTable views = _dbConnection.GetSchema("Views", restrictions)) {
foreach (DataRow row in views.Rows)
{
string viewName = row["VIEW_NAME"].ToString();
if (!IsViewSelected(viewName))
{
continue;
}
// View columns
List <DbColumn> columns = ReadColumns(viewName);
// new view
var view = new DbView(viewName, columns);
// view schema
view.OwnerName = row["OWNER"].ToString();
// add to results
result.Add(view);
}
}
return(result);
}
/// <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>
/// Read columns schema from database
/// </summary>
private List <DbColumn> ReadColumns(String tableName)
{
List <DbColumn> result = new List <DbColumn>();
string[] restrictions = null;
if (!string.IsNullOrWhiteSpace(this.SpecificOwner))
{
restrictions = new string[]
{
SpecificOwner.ToUpper(),
tableName
};
}
// Used to get columns Sql DataType
using (DataTable columnsDbTypeTable = _dbConnection.GetSchema("COLUMNS", restrictions))
{
// Fetch the rows
foreach (DataRow dr in columnsDbTypeTable.Rows)
{
string columnName = dr["COLUMN_NAME"].ToString();
DbColumn column = new DbColumn(columnName)
{
Owner = dr["OWNER"].ToString(),
DataTypeDotNet = FindMatchingDotNetDataType(dr["DATATYPE"].ToString()),
DataTypeDb = dr["DATATYPE"].ToString(),
Length = Common.TryConvertInt32(dr["LENGTH"].ToString(), 0),
ColumnOrdinal = Common.TryConvertInt32(dr["ID"].ToString(), -1),
AllowNull = dr["Nullable"].ToString().ToUpper() == "Y",
NumericPrecision = Common.TryConvertInt32(dr["PRECISION"].ToString(), -1),
NumericScale = Common.TryConvertInt32(dr["SCALE"].ToString(), -1),
// needed to be read
AutoIncrement = false,
PrimaryKey = false,
UserDescription = ""
};
column.FieldNameSchema = DbSchemaNames.FieldName_RemoveInvalidChars(column.FieldNameSchema);
// Columns which needs additional fetch
FillColumnAdditionalInfo(column, tableName, columnName);
// Add to result
result.Add(column);
}
}
return(result);
}
/// <summary>
/// Reads views list
/// </summary>
private StringCollection ReadViewsList()
{
StringCollection result = new StringCollection();
string[] restrictions = null;
if (!string.IsNullOrWhiteSpace(this.SpecificOwner))
{
restrictions = new string[] { SpecificOwner.ToUpper() };
}
using (DataTable views = _dbConnection.GetSchema("Views", restrictions)) {
foreach (DataRow row in views.Rows)
{
string viewName = row["VIEW_NAME"].ToString();
// add to results
result.Add(viewName);
}
}
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;
}
}
}
/// <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);
}
