/// <summary>
/// Reads tables index keys
/// </summary>
private void ApplyTablesConstraintKeys(IEnumerable <DbTable> tables, string ownerName)
{
string constraintKeySql = @"SELECT INFORMATION_SCHEMA.COLUMNS.TABLE_NAME AS TableName, INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME AS ColumnName,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE.CONSTRAINT_NAME AS IndexName, INFORMATION_SCHEMA.COLUMNS.COLUMN_KEY='PRI' AS IsPrimaryKey,
INFORMATION_SCHEMA.COLUMNS.COLUMN_KEY='UNI' AS IsUnique
FROM INFORMATION_SCHEMA.COLUMNS
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE ON INFORMATION_SCHEMA.KEY_COLUMN_USAGE.TABLE_NAME = INFORMATION_SCHEMA.COLUMNS.TABLE_NAME AND INFORMATION_SCHEMA.KEY_COLUMN_USAGE.COLUMN_NAME = INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME
WHERE INFORMATION_SCHEMA.COLUMNS.TABLE_SCHEMA = DATABASE()";
using (var adapter = new MySqlDataAdapter(constraintKeySql, _dbConnection)) {
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
// description data table
using (var keysData = new DataTable()) {
// Just to avoid stupid "Failed to enable constraints" error!
using (DataSet tempDs = new DataSet()) {
// Avoiding stupid "Failed to enable constraints" error!
tempDs.EnforceConstraints = false;
tempDs.Tables.Add(keysData);
// Get from db
adapter.Fill(keysData);
}
if (keysData.Rows.Count > 0)
{
// find description if there is any
foreach (var table in tables)
{
// filter row
keysData.DefaultView.RowFilter = " TableName='" + table.TableName + "' AND IsPrimaryKey=0 ";
// fetch findings, if there is any
foreach (DataRowView keysDataRow in keysData.DefaultView)
{
// found table !
DataRow keyRow = keysDataRow.Row;
// constraint Key
var constraintKey = new DbConstraintKey()
{
//IsUnique = keyRow["IsUnique"].ToString() == "1",
KeyColumnName = keyRow["ColumnName"].ToString(),
//KeyName = keyRow["IndexName"].ToString()
};
// constraint keys
//table.Indexes.Add(constraintKey);
// find key column
DbColumn keyColumn = table.FindColumnDb(constraintKey.KeyColumnName);
constraintKey.KeyColumn = keyColumn;
}
}
}
}
}
}
/// <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 index keys
/// </summary>
private void ApplyTablesConstraintKeys(List <DbTable> tables, SQLServerVersions sqlVersion)
{
if (sqlVersion == SQLServerVersions.SQL2000 ||
sqlVersion == SQLServerVersions.SQL2000Below)
{
// not supported
return;
}
//// Table constraints for SQL Server 2005 and above
//SELECT sys.objects.name AS TableName, sys.columns.name AS ColumnName, sys.indexes.name AS IndexName, sys.indexes.is_unique AS IsUnique,
// sys.indexes.is_primary_key AS IsPrimaryKey, sys.indexes.ignore_dup_key AS IgnoreDuplicateKey, sys.indexes.is_unique_constraint AS IsUniqueConstraintKey,
// sys.indexes.is_disabled AS Disabled
//FROM sys.objects INNER JOIN
// sys.indexes INNER JOIN
// sys.index_columns INNER JOIN
// sys.columns ON sys.index_columns.object_id = sys.columns.object_id AND sys.index_columns.column_id = sys.columns.column_id ON
// sys.indexes.object_id = sys.index_columns.object_id AND sys.indexes.index_id = sys.index_columns.index_id ON
// sys.objects.object_id = sys.index_columns.object_id
//WHERE (sys.objects.is_ms_shipped = 0) AND (sys.objects.type = 'U')
string constraintKeySql = "SELECT sys.objects.name AS TableName, sys.columns.name AS ColumnName, sys.indexes.name AS IndexName, sys.indexes.is_unique AS IsUnique, " +
" sys.indexes.is_primary_key AS IsPrimaryKey, sys.indexes.ignore_dup_key AS IgnoreDuplicateKey, sys.indexes.is_unique_constraint AS IsUniqueConstraintKey, " +
" sys.indexes.is_disabled AS Disabled " +
" FROM sys.objects INNER JOIN " +
" sys.indexes INNER JOIN " +
" sys.index_columns INNER JOIN " +
" sys.columns ON sys.index_columns.object_id = sys.columns.object_id AND sys.index_columns.column_id = sys.columns.column_id ON " +
" sys.indexes.object_id = sys.index_columns.object_id AND sys.indexes.index_id = sys.index_columns.index_id ON " +
" sys.objects.object_id = sys.index_columns.object_id " +
" WHERE (sys.objects.is_ms_shipped = 0) AND (sys.objects.type = 'U') ";
using (SqlDataAdapter adapter = new SqlDataAdapter(constraintKeySql, _dbConnection))
{
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
// description data table
using (DataTable keysData = new DataTable())
{
// Just to avoid stupid "Failed to enable constraints" error!
using (DataSet tempDs = new DataSet())
{
// Avoiding stupid "Failed to enable constraints" error!
tempDs.EnforceConstraints = false;
tempDs.Tables.Add(keysData);
// Get from db
adapter.Fill(keysData);
}
if (keysData.Rows.Count > 0)
{
// find description if there is any
foreach (var table in tables)
{
// filter row
keysData.DefaultView.RowFilter = " TableName='" + table.TableName + "' AND IsPrimaryKey=0 ";
// fetch findings, if there is any
foreach (DataRowView keysDataRow in keysData.DefaultView)
{
// table found !
DataRow keyRow = keysDataRow.Row;
// constraint Key
var constraintKey = new DbConstraintKey()
{
IsUnique = Convert.ToBoolean(keyRow["IsUnique"].ToString()),
KeyColumnName = keyRow["ColumnName"].ToString(),
KeyName = keyRow["IndexName"].ToString()
};
// constraint keys
table.ConstraintKeys.Add(constraintKey);
// find key column
DbColumn keyColumn = table.FindColumnDb(constraintKey.KeyColumnName);
constraintKey.KeyColumn = keyColumn;
}
}
}
}
}
}
/// <summary>
/// Reads tables index keys
/// </summary>
private void ApplyTablesConstraintKeys(IEnumerable <DbTable> tables)
{
const string constraintKeySql = @"SELECT
t.relname as TableName
,a.attname as ColumnName
,c.relname as IndexName
,i.indisunique as IsUnique
,i.indisprimary as IsPrimaryKey
,FALSE as IgnoreDuplicateKey
,FALSE as IsUniqueConstraintKey
,FALSE as Disabled
FROM
pg_catalog.pg_class c
join pg_catalog.pg_namespace n on n.oid = c.relnamespace
join pg_catalog.pg_index i on i.indexrelid = c.oid
join pg_catalog.pg_class t on i.indrelid = t.oid
join pg_catalog.pg_attribute a on a.attrelid = c.oid
WHERE
c.relkind = 'i' and n.nspname not in ('pg_catalog', 'pg_toast') and pg_catalog.pg_table_is_visible(c.oid)" ;
using (var adapter = new NpgsqlDataAdapter(constraintKeySql, _dbConnection))
{
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
// description data table
using (var keysData = new DataTable())
{
// Just to avoid stupid "Failed to enable constraints" error!
using (DataSet tempDs = new DataSet())
{
// Avoiding stupid "Failed to enable constraints" error!
tempDs.EnforceConstraints = false;
tempDs.Tables.Add(keysData);
// Get from db
adapter.Fill(keysData);
}
if (keysData.Rows.Count > 0)
{
// find description if there is any
foreach (var table in tables)
{
// filter row
keysData.DefaultView.RowFilter = " TableName='" + table.TableName + "' AND IsPrimaryKey=0 ";
// fetch findings, if there is any
foreach (DataRowView keysDataRow in keysData.DefaultView)
{
// found table !
DataRow keyRow = keysDataRow.Row;
// constraint Key
var constraintKey = new DbConstraintKey()
{
//IsUnique = Convert.ToBoolean(keyRow["IsUnique"].ToString()),
KeyColumnName = keyRow["ColumnName"].ToString(),
//KeyName = keyRow["IndexName"].ToString()
};
// constraint keys
//table.Indexes.Add(constraintKey);
// find key column
DbColumn keyColumn = table.FindColumnDb(constraintKey.KeyColumnName);
constraintKey.KeyColumn = keyColumn;
}
}
}
}
}
}