private List <DbForeignKey> GetForeignKeys(DatabaseConnectionInfo connectionInfo)
{
var fKeys = new Dictionary <string, DbForeignKey>();
var sql = @"select SCHEMA_NAME(sof.schema_id) + '.' + sof.name as ConstraintName ,
SCHEMA_NAME(sof.schema_id) as SchemaName ,
sof.name,
SCHEMA_NAME(sop.schema_id) + '.' + sop.name as ForeignKeyTable,
SCHEMA_NAME(sor.schema_id) + '.' +sor.name as PrimaryKeyTable,
scp.name as ForeignKeyColumn,
scf.name as PrimaryKeyColumn
from sys.foreign_key_columns fk
inner join Sys.objects sop on fk.parent_object_id = sop.object_id
inner join Sys.objects sof on fk.constraint_object_id = sof.object_id
inner join Sys.objects sor on fk.referenced_object_id = sor.object_id
inner join Sys.Columns scp on fk.parent_column_id = scp.column_id and fk.parent_object_id = scp.object_id
inner join Sys.Columns scf on fk.referenced_column_id = scf.column_id and fk.referenced_object_id = scf.object_id";
var primaryKeys = new Dictionary <string, List <string> >();
using (var conn = new SqlConnection(connectionInfo.ConnectionString))
{
conn.Open();
using (var cmd = conn.CreateCommand())
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = sql;
var reader = cmd.ExecuteReader();
var constraintNamePos = reader.GetOrdinal("ConstraintName");
var schemaNamePos = reader.GetOrdinal("SchemaName");
var namePos = reader.GetOrdinal("name");
var foreignKeyTablePos = reader.GetOrdinal("ForeignKeyTable");
var primaryKeyTablePos = reader.GetOrdinal("PrimaryKeyTable");
var foreignKeyColumnPos = reader.GetOrdinal("ForeignKeyColumn");
var primaryKeyColumnPos = reader.GetOrdinal("PrimaryKeyColumn");
while (reader.Read())
{
var constraintName = reader.GetString(constraintNamePos);
DbForeignKey fKey;
if (!fKeys.ContainsKey(constraintName))
{
fKey = new DbForeignKey
{
ConstraintFullName = constraintName,
Name = reader.GetString(namePos),
SchemaName = reader.GetString(schemaNamePos),
ForeignKeyTable = reader.GetString(foreignKeyTablePos),
PrimaryKeyTable = reader.GetString(primaryKeyTablePos)
};
fKeys.Add(constraintName, fKey);
}
fKeys[constraintName].Columns.Add(new DbForeignKeyColumn
{
PrimaryKeyColumn = reader.GetString(primaryKeyColumnPos),
ForeignKeyColumn = reader.GetString(foreignKeyColumnPos),
});
}
}
}
Dictionary <string, DbForeignKey> .ValueCollection fkList = fKeys.Values;
foreach (var key in fkList)
{
CodeBuilder cb = new CodeBuilder();
cb.AppendLine($"alter table {key.ForeignKeyTable}");
cb.Indent();
cb.AppendLine($"Add constraint {key.Name}");
cb.StartLine("foreign key(");
cb.AppendDelimited(", ", key.Columns, c => c.ForeignKeyColumn);
cb.Append($") references {key.PrimaryKeyTable}(");
cb.AppendDelimited(", ", key.Columns, c => c.PrimaryKeyColumn);
cb.EndLine(")");
key.Definition = cb.ToString();
}
return(fkList.ToList());
}
/// <summary>
/// Reads specified table foreign keys.
/// </summary>
private void ApplyTablesForeignKeys(List <DbTable> tables, SQLServerVersions sqlServer)
{
/*
* sql 2005 format
* SELECT CONVERT(SYSNAME, DB_NAME()) AS PKTABLE_QUALIFIER, CONVERT(SYSNAME, SCHEMA_NAME(O1.schema_id)) AS PKTABLE_OWNER, CONVERT(SYSNAME,
* O1.name) AS PKTABLE_NAME, CONVERT(SYSNAME, C1.name) AS PKCOLUMN_NAME, CONVERT(SYSNAME, DB_NAME()) AS FKTABLE_QUALIFIER,
* CONVERT(SYSNAME, SCHEMA_NAME(O2.schema_id)) AS FKTABLE_OWNER, CONVERT(SYSNAME, O2.name) AS FKTABLE_NAME, CONVERT(SYSNAME, C2.name)
* AS FKCOLUMN_NAME, CONVERT(SMALLINT, CASE OBJECTPROPERTY(F.OBJECT_ID, 'CnstIsUpdateCascade') WHEN 1 THEN 0 ELSE 1 END) AS UPDATE_RULE,
* CONVERT(SMALLINT, CASE OBJECTPROPERTY(F.OBJECT_ID, 'CnstIsDeleteCascade') WHEN 1 THEN 0 ELSE 1 END) AS DELETE_RULE, CONVERT(SYSNAME,
* OBJECT_NAME(F.object_id)) AS FK_NAME, CONVERT(SYSNAME, I.name) AS PK_NAME, CONVERT(SMALLINT, 7) AS DEFERRABILITY, F.delete_referential_action,
* F.update_referential_action
* FROM sys.all_objects AS O1 INNER JOIN
* sys.foreign_keys AS F INNER JOIN
* sys.foreign_key_columns AS K ON K.constraint_object_id = F.object_id INNER JOIN
* sys.indexes AS I ON F.referenced_object_id = I.object_id AND F.key_index_id = I.index_id ON O1.object_id = F.referenced_object_id INNER JOIN
* sys.all_objects AS O2 ON F.parent_object_id = O2.object_id INNER JOIN
* sys.all_columns AS C1 ON F.referenced_object_id = C1.object_id AND K.referenced_column_id = C1.column_id INNER JOIN
* sys.all_columns AS C2 ON F.parent_object_id = C2.object_id AND K.parent_column_id = C2.column_id
*/
// GENERAL command format
string foreignKeySql = @"SELECT OBJECT_NAME(f.constid) AS 'ForeignKey', OBJECT_NAME(f.fkeyid) AS 'FKTable',
c1.name AS 'FKColumnName', OBJECT_NAME(f.rkeyid) AS 'PKTable', c2.name AS 'PKColumnName' ,
-1 as update_referential_action, -1 as delete_referential_action
FROM sysforeignkeys AS f
INNER JOIN syscolumns AS c1 ON f.fkeyid = c1.id AND f.fkey = c1.colid
INNER JOIN syscolumns AS c2 ON f.rkeyid = c2.id AND f.rkey = c2.colid
ORDER BY 'FKTable', c1.colid ";
// NEW command format
if (sqlServer > SQLServerVersions.SQL2000)
{
foreignKeySql =
@"SELECT CONVERT(SYSNAME, DB_NAME()) AS PKTABLE_QUALIFIER, CONVERT(SYSNAME, SCHEMA_NAME(O1.schema_id)) AS PKTABLE_OWNER, CONVERT(SYSNAME,
O1.name) AS 'PKTable', CONVERT(SYSNAME, C1.name) AS 'PKColumnName', CONVERT(SYSNAME, DB_NAME()) AS FKTABLE_QUALIFIER,
CONVERT(SYSNAME, SCHEMA_NAME(O2.schema_id)) AS FKTABLE_OWNER, CONVERT(SYSNAME, O2.name) AS 'FKTable', CONVERT(SYSNAME, C2.name)
AS 'FKColumnName', CONVERT(SMALLINT, CASE OBJECTPROPERTY(F.OBJECT_ID, 'CnstIsUpdateCascade') WHEN 1 THEN 0 ELSE 1 END) AS UPDATE_RULE,
CONVERT(SMALLINT, CASE OBJECTPROPERTY(F.OBJECT_ID, 'CnstIsDeleteCascade') WHEN 1 THEN 0 ELSE 1 END) AS DELETE_RULE, CONVERT(SYSNAME,
OBJECT_NAME(F.object_id)) AS 'ForeignKey', CONVERT(SYSNAME, I.name) AS PK_NAME, CONVERT(SMALLINT, 7) AS DEFERRABILITY, F.delete_referential_action,
F.update_referential_action
FROM sys.all_objects AS O1 INNER JOIN
sys.foreign_keys AS F INNER JOIN
sys.foreign_key_columns AS K ON K.constraint_object_id = F.object_id INNER JOIN
sys.indexes AS I ON F.referenced_object_id = I.object_id AND F.key_index_id = I.index_id ON O1.object_id = F.referenced_object_id INNER JOIN
sys.all_objects AS O2 ON F.parent_object_id = O2.object_id INNER JOIN
sys.all_columns AS C1 ON F.referenced_object_id = C1.object_id AND K.referenced_column_id = C1.column_id INNER JOIN
sys.all_columns AS C2 ON F.parent_object_id = C2.object_id AND K.parent_column_id = C2.column_id
ORDER BY 'FKTable', C2.column_id";
}
try
{
using (var adapter = new SqlDataAdapter(foreignKeySql, (SqlConnection)_dbConnection))
{
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
// description data table
using (var keysData = new DataTable())
{
// Just to avoid stupid "Failed to enable constraints" error!
using (var 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)
{
foreach (DataRow keysDataRow in keysData.Rows)
{
var foreignKeyTableName = keysDataRow["FKTable"].ToString();
var primaryKeyTableName = keysDataRow["PKTable"].ToString();
var foreignKeyTable = FindTable(tables, foreignKeyTableName);
var primaryKeyTable = FindTable(tables, primaryKeyTableName);
// one-to-many foreign relation will be added
if (primaryKeyTable != null)
{
// foreign key many end
var manyMultiplicityKey_Local = new DbForeignKey()
{
ForeignKeyName = keysDataRow["ForeignKey"].ToString(),
LocalColumnName = keysDataRow["PKColumnName"].ToString(),
ForeignColumnName = keysDataRow["FKColumnName"].ToString(),
ForeignTableName = keysDataRow["FKTable"].ToString(),
Multiplicity = DbForeignKey.ForeignKeyMultiplicity.ManyToOne
};
// check if it is already there
if (primaryKeyTable.ForeignKeys.Exists(
x =>
x.Multiplicity == DbForeignKey.ForeignKeyMultiplicity.ManyToOne &&
x.ForeignKeyName == manyMultiplicityKey_Local.ForeignKeyName))
{
continue;
}
manyMultiplicityKey_Local.UpdateAction =
ConvertSqlServerForeignKeyAction(Convert.ToInt32(keysDataRow["update_referential_action"].ToString()));
manyMultiplicityKey_Local.DeleteAction =
ConvertSqlServerForeignKeyAction(Convert.ToInt32(keysDataRow["delete_referential_action"].ToString()));
// to the list
primaryKeyTable.ForeignKeys.Add(manyMultiplicityKey_Local);
// apply local column
DbColumn localColumn = primaryKeyTable.FindColumnDb(manyMultiplicityKey_Local.LocalColumnName);
manyMultiplicityKey_Local.LocalColumn = localColumn;
if (!localColumn.PrimaryKey)
{
localColumn.IsReferenceKey = true;
localColumn.IsReferenceKeyTable = primaryKeyTable;
}
if (foreignKeyTable != null)
{
// foreign table of that!
manyMultiplicityKey_Local.ForeignTable = foreignKeyTable;
// apply foreign column
DbColumn foreignColumn = foreignKeyTable.FindColumnDb(manyMultiplicityKey_Local.ForeignColumnName);
manyMultiplicityKey_Local.ForeignColumn = foreignColumn;
}
else
{
manyMultiplicityKey_Local.ForeignTable = null;
manyMultiplicityKey_Local.ForeignColumn = null;
}
}
// one-to-? foreign relation will be added
if (foreignKeyTable != null)
{
// foreign key many end
var oneMultiplicityKey_Foreign = new DbForeignKey()
{
ForeignKeyName = keysDataRow["ForeignKey"].ToString(),
LocalColumnName = keysDataRow["FKColumnName"].ToString(),
ForeignColumnName = keysDataRow["PKColumnName"].ToString(),
ForeignTableName = keysDataRow["PKTable"].ToString(),
Multiplicity = DbForeignKey.ForeignKeyMultiplicity.OneToMany
};
// check if it is already there
if (foreignKeyTable.ForeignKeys.Exists(
x =>
x.Multiplicity == DbForeignKey.ForeignKeyMultiplicity.OneToMany &&
x.ForeignKeyName == oneMultiplicityKey_Foreign.ForeignKeyName))
{
continue;
}
oneMultiplicityKey_Foreign.UpdateAction =
ConvertSqlServerForeignKeyAction(Convert.ToInt32(keysDataRow["update_referential_action"].ToString()));
oneMultiplicityKey_Foreign.DeleteAction =
ConvertSqlServerForeignKeyAction(Convert.ToInt32(keysDataRow["delete_referential_action"].ToString()));
// to the list
foreignKeyTable.ForeignKeys.Add(oneMultiplicityKey_Foreign);
// apply local column
DbColumn localColumn = foreignKeyTable.FindColumnDb(oneMultiplicityKey_Foreign.LocalColumnName);
oneMultiplicityKey_Foreign.LocalColumn = localColumn;
if (!localColumn.PrimaryKey)
{
localColumn.IsReferenceKey = true;
localColumn.IsReferenceKeyTable = primaryKeyTable;
}
if (primaryKeyTable != null)
{
// foreign table of that!
oneMultiplicityKey_Foreign.ForeignTable = primaryKeyTable;
// apply foreign column
DbColumn foreignColumn = primaryKeyTable.FindColumnDb(oneMultiplicityKey_Foreign.ForeignColumnName);
oneMultiplicityKey_Foreign.ForeignColumn = foreignColumn;
}
else
{
oneMultiplicityKey_Foreign.ForeignTable = null;
oneMultiplicityKey_Foreign.ForeignColumn = null;
}
}
}// all foreign keys
// look for one-to-one situation!
}
}
}
}
catch
{
// Seems this version of SQL Server doesn't support this query!
// don't stop here!
}
}
/// <summary>
/// Reads specified table foreign keys.
/// </summary>
private void ApplyTablesForeignKeys(List <DbTable> tables)
{
if (_dbConnection.State != ConnectionState.Open)
{
_dbConnection.Open();
}
// Used to get columns Sql DataType
using (DataTable foreignKeysTable = _dbConnection.GetSchema("ForeignKeys"))
{
// nothing found!
if (foreignKeysTable.Rows.Count == 0)
{
return;
}
// find description if there is any
foreach (var table in tables)
{
// only FOREIGN KEY
foreignKeysTable.DefaultView.RowFilter =
string.Format(" CONSTRAINT_TYPE='FOREIGN KEY' AND TABLE_NAME='{0}' ", table.TableName);
// Fetch the rows
foreach (DataRowView keysData in foreignKeysTable.DefaultView)
{
// foreign key found!
DataRow keyRow = keysData.Row;
var foreignKeyTableName = keyRow["FKEY_TO_TABLE"].ToString();
var primaryKeyTableName = table.TableName;
var foreignKeyTable = FindTable(tables, foreignKeyTableName);
var primaryKeyTable = table;
if (primaryKeyTable != null)
{
// foreign key
var foreignKey = new DbForeignKey()
{
ForeignKeyName = keyRow["CONSTRAINT_NAME"].ToString(),
LocalColumnName = keyRow["FKEY_FROM_COLUMN"].ToString(),
ForeignColumnName = keyRow["FKEY_TO_COLUMN"].ToString(),
ForeignTableName = keyRow["FKEY_TO_TABLE"].ToString(),
Multiplicity = DbForeignKey.ForeignKeyMultiplicity.OneToMany
};
// add foreign key
table.ForeignKeys.Add(foreignKey);
// apply local column
DbColumn localColumn = table.FindColumnDb(foreignKey.LocalColumnName);
foreignKey.LocalColumn = localColumn;
//apply foreign table
DbTable foreignTable = foreignKeyTable;
// referenced key
if (!localColumn.PrimaryKey)
{
localColumn.IsReferenceKey = true;
localColumn.IsReferenceKeyTable = foreignTable;
}
if (foreignTable != null)
{
foreignKey.ForeignTable = foreignTable;
// apply foreign column
DbColumn foreignColumn = foreignTable.FindColumnDb(foreignKey.ForeignColumnName);
foreignKey.ForeignColumn = foreignColumn;
}
else
{
foreignKey.ForeignTable = null;
foreignKey.ForeignColumn = null;
}
}
// adding the relation to the foreign table!
if (foreignKeyTable != null)
{
// foreign key
var oneMultiplicityKey_Foreign = new DbForeignKey()
{
ForeignKeyName = keyRow["CONSTRAINT_NAME"].ToString(),
LocalColumnName = keyRow["FKEY_TO_COLUMN"].ToString(),
ForeignColumnName = keyRow["FKEY_FROM_COLUMN"].ToString(),
ForeignTableName = primaryKeyTableName,
Multiplicity = DbForeignKey.ForeignKeyMultiplicity.ManyToOne
};
// check if it is already there
if (foreignKeyTable.ForeignKeys.Exists(x => x.ForeignKeyName == oneMultiplicityKey_Foreign.ForeignKeyName))
{
continue;
}
// to the list
foreignKeyTable.ForeignKeys.Add(oneMultiplicityKey_Foreign);
// apply local column
DbColumn localColumn = foreignKeyTable.FindColumnDb(oneMultiplicityKey_Foreign.LocalColumnName);
oneMultiplicityKey_Foreign.LocalColumn = localColumn;
if (!localColumn.PrimaryKey)
{
localColumn.IsReferenceKey = true;
localColumn.IsReferenceKeyTable = primaryKeyTable;
}
if (primaryKeyTable != null)
{
// foreign table of that!
oneMultiplicityKey_Foreign.ForeignTable = primaryKeyTable;
// apply foreign column
DbColumn foreignColumn = primaryKeyTable.FindColumnDb(oneMultiplicityKey_Foreign.ForeignColumnName);
oneMultiplicityKey_Foreign.ForeignColumn = foreignColumn;
}
else
{
oneMultiplicityKey_Foreign.ForeignTable = null;
oneMultiplicityKey_Foreign.ForeignColumn = null;
}
}
}
}
}
}
/// <summary>
/// Reads specified table foreign keys.
/// </summary>
private void ApplyTablesForeignKeys(List <DbTable> tables)
{
if (_dbConnection.State != ConnectionState.Open)
{
_dbConnection.Open();
}
try {
// Used to get columns Sql DataType
const string cmd = "SELECT A.COLUMN_NAME as UNIQUE_COLUMN_NAME,C.COLUMN_NAME as CONSTRAINT_COLUMN_NAME , B.* FROM INFORMATION_SCHEMA.indexes A INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS B ON UNIQUE_CONSTRAINT_NAME = INDEX_NAME inner join INFORMATION_SCHEMA.KEY_COLUMN_USAGE C ON C.CONSTRAINT_NAME=B.CONSTRAINT_NAME";
using (var adapter = new SqlCeDataAdapter(cmd, _dbConnection.ConnectionString))
using (var foreignKeysTable = new DataTable()) {
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
adapter.Fill(foreignKeysTable);
// nothing found!)
if (foreignKeysTable.Rows.Count == 0)
{
return;
}
foreach (DataRow keyRow in foreignKeysTable.Rows)
{
var foreignKeyTableName = keyRow["CONSTRAINT_TABLE_NAME"].ToString();
var primaryKeyTableName = keyRow["UNIQUE_CONSTRAINT_TABLE_NAME"].ToString();
var foreignKeyTable = FindTable(tables, foreignKeyTableName);
var primaryKeyTable = FindTable(tables, primaryKeyTableName);
// one-to-many foreign relation will be added
if (primaryKeyTable != null)
{
// foreign key many end
var manyMultiplicityKey_Local = new DbForeignKey()
{
ForeignKeyName = keyRow["CONSTRAINT_NAME"].ToString(),
LocalColumnName = keyRow["UNIQUE_COLUMN_NAME"].ToString(),
ForeignColumnName = keyRow["CONSTRAINT_COLUMN_NAME"].ToString(),
ForeignTableName = keyRow["CONSTRAINT_TABLE_NAME"].ToString(),
Multiplicity = DbForeignKey.ForeignKeyMultiplicity.ManyToOne
};
// check if it is already there
if (primaryKeyTable.ForeignKeys.Exists(x => x.ForeignKeyName == manyMultiplicityKey_Local.ForeignKeyName))
{
continue;
}
manyMultiplicityKey_Local.UpdateAction =
ConvertSqlCeForeignKeyAction(keyRow["UPDATE_RULE"].ToString());
manyMultiplicityKey_Local.DeleteAction =
ConvertSqlCeForeignKeyAction(keyRow["DELETE_RULE"].ToString());
// to the list
primaryKeyTable.ForeignKeys.Add(manyMultiplicityKey_Local);
// apply local column
DbColumn localColumn = primaryKeyTable.FindColumnDb(manyMultiplicityKey_Local.LocalColumnName);
manyMultiplicityKey_Local.LocalColumn = localColumn;
if (!localColumn.PrimaryKey)
{
localColumn.IsReferenceKey = true;
localColumn.IsReferenceKeyTable = primaryKeyTable;
}
if (foreignKeyTable != null)
{
// foreign table of that!
manyMultiplicityKey_Local.ForeignTable = foreignKeyTable;
// apply foreign column
DbColumn foreignColumn = foreignKeyTable.FindColumnDb(manyMultiplicityKey_Local.ForeignColumnName);
manyMultiplicityKey_Local.ForeignColumn = foreignColumn;
}
else
{
manyMultiplicityKey_Local.ForeignTable = null;
manyMultiplicityKey_Local.ForeignColumn = null;
}
}
// one-to-one foreign relation will be added
if (foreignKeyTable != null)
{
// foreign key many end
var oneMultiplicityKey_Foreign = new DbForeignKey()
{
ForeignKeyName = keyRow["CONSTRAINT_NAME"].ToString(),
LocalColumnName = keyRow["CONSTRAINT_COLUMN_NAME"].ToString(),
ForeignColumnName = keyRow["UNIQUE_COLUMN_NAME"].ToString(),
ForeignTableName = keyRow["UNIQUE_CONSTRAINT_TABLE_NAME"].ToString(),
Multiplicity = DbForeignKey.ForeignKeyMultiplicity.OneToMany
};
// check if it is already there
if (foreignKeyTable.ForeignKeys.Exists(x => x.ForeignKeyName == oneMultiplicityKey_Foreign.ForeignKeyName))
{
continue;
}
oneMultiplicityKey_Foreign.UpdateAction =
ConvertSqlCeForeignKeyAction(keyRow["UPDATE_RULE"].ToString());
oneMultiplicityKey_Foreign.DeleteAction =
ConvertSqlCeForeignKeyAction(keyRow["DELETE_RULE"].ToString());
// to the list
foreignKeyTable.ForeignKeys.Add(oneMultiplicityKey_Foreign);
// apply local column
DbColumn localColumn = foreignKeyTable.FindColumnDb(oneMultiplicityKey_Foreign.LocalColumnName);
oneMultiplicityKey_Foreign.LocalColumn = localColumn;
if (!localColumn.PrimaryKey)
{
localColumn.IsReferenceKey = true;
localColumn.IsReferenceKeyTable = primaryKeyTable;
}
if (primaryKeyTable != null)
{
// foreign table of that!
oneMultiplicityKey_Foreign.ForeignTable = primaryKeyTable;
// apply foreign column
DbColumn foreignColumn = primaryKeyTable.FindColumnDb(oneMultiplicityKey_Foreign.ForeignColumnName);
oneMultiplicityKey_Foreign.ForeignColumn = foreignColumn;
}
else
{
oneMultiplicityKey_Foreign.ForeignTable = null;
oneMultiplicityKey_Foreign.ForeignColumn = null;
}
}
} // all keys
}
}
finally {
_dbConnection.Close();
}
}
/// <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>
/// Applies project settings to fields name
/// </summary>
private string NaturalizeNames_ForeignTableFieldName(DbTable table, DbForeignKey foreignKey)
{
if (string.IsNullOrEmpty(foreignKey.ForeignTableNameInLocalTable))
{
return(foreignKey.ForeignTableNameInLocalTable);
}
var newName = NaturalizeNames_TableName_Rename(foreignKey.ForeignTableNameInLocalTable);
var stringCompare = StringComparison.InvariantCulture;
if (_patternProject.LanguageSettings.KeywordsCaseSensitive == false)
{
stringCompare = StringComparison.InvariantCultureIgnoreCase;
}
// suppress pattern
string replacement = _patternProject.LanguageSettings.LanguageKeywordsSuppress;
int initReplacePartCount = 0;
string initReplacePartStr = "";
// column name should not be the same
if (newName.Equals(table.TableNameSchema, stringCompare) ||
newName.Equals(table.TableNameSchemaCS, stringCompare))
{
var renamedName = string.Format(replacement, newName, initReplacePartStr);
initReplacePartCount++;
initReplacePartStr = initReplacePartCount.ToString();
// no duplicate
while (table.FindColumnSchema(renamedName) != null ||
table.ForeignKeys.Any(x => x.ForeignTableNameInLocalTable.Equals(renamedName, stringCompare)))
{
renamedName = string.Format(replacement, newName, initReplacePartStr);
initReplacePartCount++;
initReplacePartStr = initReplacePartCount.ToString();
}
newName = renamedName;
}
// foreign name is not changed and is a member
if (newName.Equals(foreignKey.ForeignTableNameInLocalTable, stringCompare))
{
var sameNameForeignKeys =
table.ForeignKeys.Where(x => x.ForeignTableNameInLocalTable.Equals(newName, stringCompare)).ToList();
// no more than one occurrence, including itself
if (table.FindColumnSchema(newName) != null ||
(sameNameForeignKeys.Count > 1 && sameNameForeignKeys.IndexOf(foreignKey) > 0))
{
var renamedName = string.Format(replacement, newName, initReplacePartStr);
initReplacePartCount++;
initReplacePartStr = initReplacePartCount.ToString();
// no duplicate
while (table.FindColumnSchema(renamedName) != null ||
table.ForeignKeys.Any(x => x.ForeignTableNameInLocalTable.Equals(renamedName, stringCompare)))
{
renamedName = string.Format(replacement, newName, initReplacePartStr);
initReplacePartCount++;
initReplacePartStr = initReplacePartCount.ToString();
}
newName = renamedName;
}
}
else
{
if (table.FindColumnSchema(newName) != null ||
table.ForeignKeys.Any(x => x.ForeignTableNameInLocalTable.Equals(newName, stringCompare)))
{
var renamedName = string.Format(replacement, newName, initReplacePartStr);
initReplacePartCount++;
initReplacePartStr = initReplacePartCount.ToString();
// no duplicate
while (table.FindColumnSchema(renamedName) != null ||
table.ForeignKeys.Any(x => x.ForeignTableNameInLocalTable.Equals(renamedName, stringCompare)))
{
renamedName = string.Format(replacement, newName, initReplacePartStr);
initReplacePartCount++;
initReplacePartStr = initReplacePartCount.ToString();
}
newName = renamedName;
}
}
// checking keyword match if only foreign name is not changed
if (newName.Equals(foreignKey.ForeignTableNameInLocalTable, stringCompare))
{
// ignoring keywords
foreach (var keyword in _patternProject.LanguageSettings.LanguageKeywords)
{
// keyword match
if (newName.Equals(keyword, stringCompare))
{
var renamedName = string.Format(replacement, newName, initReplacePartStr);
initReplacePartCount++;
initReplacePartStr = initReplacePartCount.ToString();
// no duplicate
while (table.FindColumnSchema(renamedName) != null ||
table.ForeignKeys.Any(x => x.ForeignTableNameInLocalTable.Equals(renamedName, stringCompare)))
{
renamedName = string.Format(replacement, newName, initReplacePartStr);
initReplacePartCount++;
initReplacePartStr = initReplacePartCount.ToString();
}
newName = renamedName;
// name is chaned and check is no longer required
break;
}
}
}
// foreign name is ok to be used
return(newName);
}
/// <summary>
/// Reads specified table foreign keys.
/// </summary>
private void ApplyTablesForeignKeys(List <DbTable> tables)
{
// GENERAL command format
const string foreignKeySql = @"select
INFORMATION_SCHEMA.KEY_COLUMN_USAGE.CONSTRAINT_NAME AS ForeignKey,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE.TABLE_NAME AS FKTable,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE.COLUMN_Name as FKColumnName,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE.REFERENCED_TABLE_NAME AS PKTable,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE.REFERENCED_COLUMN_NAME AS PKColumnName,
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS.UPDATE_RULE AS UPDATE_RULE,
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS.DELETE_RULE AS DELETE_RULE
from INFORMATION_SCHEMA.KEY_COLUMN_USAGE
inner join INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
ON INFORMATION_SCHEMA.KEY_COLUMN_USAGE.CONSTRAINT_SCHEMA = INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS.CONSTRAINT_SCHEMA
and INFORMATION_SCHEMA.KEY_COLUMN_USAGE.CONSTRAINT_NAME = INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS.CONSTRAINT_NAME
where INFORMATION_SCHEMA.KEY_COLUMN_USAGE.constraint_SCHEMA = DATABASE()";
try
{
using (var adapter = new MySqlDataAdapter(foreignKeySql, _dbConnection))
{
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
// description data table
using (var keysData = new DataTable())
{
// Just to avoid stupid "Failed to enable constraints" error!
using (var 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)
{
foreach (DataRow keysDataRow in keysData.Rows)
{
var foreignKeyTableName = keysDataRow["FKTable"].ToString();
var primaryKeyTableName = keysDataRow["PKTable"].ToString();
var foreignKeyTable = FindTable(tables, foreignKeyTableName);
var primaryKeyTable = FindTable(tables, primaryKeyTableName);
// one-to-many foreign relation will be added
if (primaryKeyTable != null)
{
// foreign key many end
var manyMultiplicityKeyLocal = new DbForeignKey
{
ForeignKeyName = keysDataRow["ForeignKey"].ToString(),
LocalColumnName = keysDataRow["PKColumnName"].ToString(),
ForeignColumnName = keysDataRow["FKColumnName"].ToString(),
ForeignTableName = keysDataRow["FKTable"].ToString(),
Multiplicity = DbForeignKey.ForeignKeyMultiplicity.ManyToOne
};
// check if it is already there
if (primaryKeyTable.ForeignKeys.Exists(
x =>
x.Multiplicity == DbForeignKey.ForeignKeyMultiplicity.ManyToOne &&
x.ForeignKeyName == manyMultiplicityKeyLocal.ForeignKeyName))
{
continue;
}
manyMultiplicityKeyLocal.UpdateAction =
ConvertMysqlForeignKeyAction(keysDataRow["UPDATE_RULE"].ToString());
manyMultiplicityKeyLocal.DeleteAction =
ConvertMysqlForeignKeyAction(keysDataRow["DELETE_RULE"].ToString());
// to the list
primaryKeyTable.ForeignKeys.Add(manyMultiplicityKeyLocal);
// apply local column
var localColumn = primaryKeyTable.FindColumnDb(manyMultiplicityKeyLocal.LocalColumnName);
manyMultiplicityKeyLocal.LocalColumn = localColumn;
if (!localColumn.PrimaryKey)
{
localColumn.IsReferenceKey = true;
localColumn.IsReferenceKeyTable = primaryKeyTable;
}
if (foreignKeyTable != null)
{
// foreign table of that!
manyMultiplicityKeyLocal.ForeignTable = foreignKeyTable;
// apply foreign column
DbColumn foreignColumn = foreignKeyTable.FindColumnDb(manyMultiplicityKeyLocal.ForeignColumnName);
manyMultiplicityKeyLocal.ForeignColumn = foreignColumn;
}
else
{
manyMultiplicityKeyLocal.ForeignTable = null;
manyMultiplicityKeyLocal.ForeignColumn = null;
}
}
// one-to-? foreign relation will be added
if (foreignKeyTable != null)
{
// foreign key many end
var oneMultiplicityKeyForeign = new DbForeignKey
{
ForeignKeyName = keysDataRow["ForeignKey"].ToString(),
LocalColumnName = keysDataRow["FKColumnName"].ToString(),
ForeignColumnName = keysDataRow["PKColumnName"].ToString(),
ForeignTableName = keysDataRow["PKTable"].ToString(),
Multiplicity = DbForeignKey.ForeignKeyMultiplicity.OneToMany
};
// check if it is already there
if (foreignKeyTable.ForeignKeys.Exists(
x =>
x.Multiplicity == DbForeignKey.ForeignKeyMultiplicity.OneToMany &&
x.ForeignKeyName == oneMultiplicityKeyForeign.ForeignKeyName))
{
continue;
}
oneMultiplicityKeyForeign.UpdateAction =
ConvertMysqlForeignKeyAction(keysDataRow["UPDATE_RULE"].ToString());
oneMultiplicityKeyForeign.DeleteAction =
ConvertMysqlForeignKeyAction(keysDataRow["DELETE_RULE"].ToString());
// to the list
foreignKeyTable.ForeignKeys.Add(oneMultiplicityKeyForeign);
// apply local column
DbColumn localColumn = foreignKeyTable.FindColumnDb(oneMultiplicityKeyForeign.LocalColumnName);
oneMultiplicityKeyForeign.LocalColumn = localColumn;
if (!localColumn.PrimaryKey)
{
localColumn.IsReferenceKey = true;
localColumn.IsReferenceKeyTable = primaryKeyTable;
}
if (primaryKeyTable != null)
{
// foreign table of that!
oneMultiplicityKeyForeign.ForeignTable = primaryKeyTable;
// apply foreign column
DbColumn foreignColumn = primaryKeyTable.FindColumnDb(oneMultiplicityKeyForeign.ForeignColumnName);
oneMultiplicityKeyForeign.ForeignColumn = foreignColumn;
}
else
{
oneMultiplicityKeyForeign.ForeignTable = null;
oneMultiplicityKeyForeign.ForeignColumn = null;
}
}
} // all foreign keys
// look for one-to-one situation!
}
}
}
}
catch
{
// Seems this version of postgresql doesn't support this query!
// don't stop here!
}
}
/// <summary>
/// Reads specified table foreign keys.
/// </summary>
private void ApplyTablesForeignKeys(List <DbTable> tables)
{
// GENERAL command format
const string foreignKeySql = @"SELECT
tc.constraint_name as ForeignKey,
tc.table_name AS FKTable,
kcu.column_name AS FKColumnName,
ccu.table_name AS PKTable,
ccu.column_name AS PKColumnName,
-1 as update_referential_action,
-1 as delete_referential_action
FROM
information_schema.table_constraints AS tc
JOIN information_schema.key_column_usage AS kcu ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage AS ccu ON ccu.constraint_name = tc.constraint_name
WHERE constraint_type = 'FOREIGN KEY'" ;
try
{
using (var adapter = new NpgsqlDataAdapter(foreignKeySql, _dbConnection))
{
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
// description data table
using (var keysData = new DataTable())
{
// Just to avoid stupid "Failed to enable constraints" error!
using (var 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)
{
foreach (DataRow keysDataRow in keysData.Rows)
{
var foreignKeyTableName = keysDataRow["FKTable"].ToString();
var primaryKeyTableName = keysDataRow["PKTable"].ToString();
var foreignKeyTable = FindTable(tables, foreignKeyTableName);
var primaryKeyTable = FindTable(tables, primaryKeyTableName);
// one-to-many foreign relation will be added
if (primaryKeyTable != null)
{
// foreign key many end
var manyMultiplicityKeyLocal = new DbForeignKey
{
ForeignKeyName = keysDataRow["ForeignKey"].ToString(),
LocalColumnName = keysDataRow["PKColumnName"].ToString(),
ForeignColumnName = keysDataRow["FKColumnName"].ToString(),
ForeignTableName = keysDataRow["FKTable"].ToString(),
Multiplicity = DbForeignKey.ForeignKeyMultiplicity.ManyToOne
};
// check if it is already there
if (primaryKeyTable.ForeignKeys.Exists(
x =>
x.Multiplicity == DbForeignKey.ForeignKeyMultiplicity.ManyToOne &&
x.ForeignKeyName == manyMultiplicityKeyLocal.ForeignKeyName))
{
continue;
}
manyMultiplicityKeyLocal.UpdateAction =
ConvertPosgresqlForeignKeyAction(Convert.ToInt32(keysDataRow["update_referential_action"].ToString()));
manyMultiplicityKeyLocal.DeleteAction =
ConvertPosgresqlForeignKeyAction(Convert.ToInt32(keysDataRow["delete_referential_action"].ToString()));
// to the list
primaryKeyTable.ForeignKeys.Add(manyMultiplicityKeyLocal);
// apply local column
var localColumn = primaryKeyTable.FindColumnDb(manyMultiplicityKeyLocal.LocalColumnName);
manyMultiplicityKeyLocal.LocalColumn = localColumn;
if (!localColumn.PrimaryKey)
{
localColumn.IsReferenceKey = true;
localColumn.IsReferenceKeyTable = primaryKeyTable;
}
if (foreignKeyTable != null)
{
// foreign table of that!
manyMultiplicityKeyLocal.ForeignTable = foreignKeyTable;
// apply foreign column
DbColumn foreignColumn = foreignKeyTable.FindColumnDb(manyMultiplicityKeyLocal.ForeignColumnName);
manyMultiplicityKeyLocal.ForeignColumn = foreignColumn;
}
else
{
manyMultiplicityKeyLocal.ForeignTable = null;
manyMultiplicityKeyLocal.ForeignColumn = null;
}
}
// one-to-? foreign relation will be added
if (foreignKeyTable != null)
{
// foreign key many end
var oneMultiplicityKeyForeign = new DbForeignKey
{
ForeignKeyName = keysDataRow["ForeignKey"].ToString(),
LocalColumnName = keysDataRow["FKColumnName"].ToString(),
ForeignColumnName = keysDataRow["PKColumnName"].ToString(),
ForeignTableName = keysDataRow["PKTable"].ToString(),
Multiplicity = DbForeignKey.ForeignKeyMultiplicity.OneToMany
};
// check if it is already there
if (foreignKeyTable.ForeignKeys.Exists(
x =>
x.Multiplicity == DbForeignKey.ForeignKeyMultiplicity.OneToMany &&
x.ForeignKeyName == oneMultiplicityKeyForeign.ForeignKeyName))
{
continue;
}
oneMultiplicityKeyForeign.UpdateAction =
ConvertPosgresqlForeignKeyAction(Convert.ToInt32(keysDataRow["update_referential_action"].ToString()));
oneMultiplicityKeyForeign.DeleteAction =
ConvertPosgresqlForeignKeyAction(Convert.ToInt32(keysDataRow["delete_referential_action"].ToString()));
// to the list
foreignKeyTable.ForeignKeys.Add(oneMultiplicityKeyForeign);
// apply local column
DbColumn localColumn = foreignKeyTable.FindColumnDb(oneMultiplicityKeyForeign.LocalColumnName);
oneMultiplicityKeyForeign.LocalColumn = localColumn;
if (!localColumn.PrimaryKey)
{
localColumn.IsReferenceKey = true;
localColumn.IsReferenceKeyTable = primaryKeyTable;
}
if (primaryKeyTable != null)
{
// foreign table of that!
oneMultiplicityKeyForeign.ForeignTable = primaryKeyTable;
// apply foreign column
DbColumn foreignColumn = primaryKeyTable.FindColumnDb(oneMultiplicityKeyForeign.ForeignColumnName);
oneMultiplicityKeyForeign.ForeignColumn = foreignColumn;
}
else
{
oneMultiplicityKeyForeign.ForeignTable = null;
oneMultiplicityKeyForeign.ForeignColumn = null;
}
}
} // all foreign keys
// look for one-to-one situation!
}
}
}
}
catch
{
// Seems this version of postgresql doesn't support this query!
// don't stop here!
}
}
private DbSchemaObject ParseSchemaObject(FileInfo file)
{
DbSchemaObject schemaObject = null;
var name = System.IO.Path.GetFileNameWithoutExtension(file.FullName);
var nameparts = name.Split('_');
var objectType = (ESchemaObjectType)Enum.Parse(typeof(ESchemaObjectType), nameparts[0]);
switch (objectType)
{
case ESchemaObjectType.Table:
schemaObject = new DbTable();
break;
case ESchemaObjectType.View:
schemaObject = new DbView();
break;
case ESchemaObjectType.StoredProcedure:
schemaObject = new DbStoredProc();
break;
case ESchemaObjectType.ScalarFunction:
schemaObject = new DbScalarFunction();
break;
case ESchemaObjectType.TableFunction:
schemaObject = new DbTableFunction();
break;
case ESchemaObjectType.Trigger:
schemaObject = new DbTrigger();
break;
case ESchemaObjectType.PrimaryKey:
schemaObject = new DbPrimaryKey();
break;
case ESchemaObjectType.ForeignKey:
schemaObject = new DbForeignKey();
break;
default:
throw new ArgumentOutOfRangeException();
}
if (schemaObject == null)
{
throw new Exception("Unable to identify object");
}
var objectParts = nameparts[1].Split('.');
if (objectParts.Length != 2)
{
throw new Exception($"Problem with name {nameparts[1]}");
}
schemaObject.SchemaName = objectParts[0];
schemaObject.Name = objectParts[1];
schemaObject.Definition = file.ReadAllText();
return(schemaObject);
}
