protected IEnumerable <ITableSourceForeignKeyInfo> GetForeignKeys_Old(IEnumerable <CacheTableSourceInfo> tableSources)
{
string paramPrefix = DatabaseServices.ExecutionService.ParameterPrefix;
string tableNames = "";
try {
tableNames = "'" + tableSources.Select(t => t.Name).StrCat("','") + "'";
IList <ITableSourceForeignKeyInfo> foreignKeys = new List <ITableSourceForeignKeyInfo>();
//on mysql table_schema always equal to constraint_schema so we can shortcut to optimize
string query = string.Format(@"SELECT t.TABLE_NAME,
c.CONSTRAINT_NAME, c.COLUMN_NAME, c.REFERENCED_TABLE_NAME, c.REFERENCED_COLUMN_NAME,
r.DELETE_RULE
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS t ON c.CONSTRAINT_NAME = t.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS r ON r.CONSTRAINT_NAME = t.CONSTRAINT_NAME
WHERE t.CONSTRAINT_TYPE = 'FOREIGN KEY'
AND t.TABLE_SCHEMA = {0}
AND c.CONSTRAINT_SCHEMA = {0}
AND r.CONSTRAINT_SCHEMA = {0}
AND t.TABLE_NAME IN (" + tableNames + @")", paramPrefix + "schema");
using (IDbConnection conn = DatabaseServices.TransactionService.CreateConnection()) {
IDbCommand cmd = DatabaseServices.ExecutionService.CreateCommand(conn, query);
//----------------- Caché --------------------
// Caché does not look at the parameter name, it assigns parameters in the order they appear, one per question mark "?"
// So the above query expects 3 parameters, although it is the same Caché expects 3, so add 3 parameters.
DatabaseServices.ExecutionService.CreateParameter(cmd, paramPrefix + "schema", DbType.String, tableSources.First().Database.Name);
cmd.CommandTimeout = QueryTimeout;
using (IDataReader reader = cmd.ExecuteReader()) {
while (reader.Read())
{
string tableName = (string)reader["TABLE_NAME"];
string foreignKeyName = (string)reader["CONSTRAINT_NAME"];
string columnName = (string)reader["COLUMN_NAME"];
string referencedColumnName = (string)reader["REFERENCED_COLUMN_NAME"];
string referencedTableName = (string)reader["REFERENCED_TABLE_NAME"];
bool isCascadeDelete = "CASCADE".EqualsIgnoreCase((string)reader["DELETE_RULE"]);
CacheTableSourceInfo tableSource = tableSources.First(t => t.Name.EqualsIgnoreCase(tableName));
string qualifiedReferencedTableName = GetQualifiedIdentifier(tableSource.Database.Name, referencedTableName);
ITableSourceInfo referencedTableSource = new CacheTableSourceInfo(DatabaseServices, tableSource.Database, referencedTableName, qualifiedReferencedTableName);
ITableSourceForeignKeyInfo foreignKeyInfo = new CacheTableSourceForeignKeyInfo(tableSource, foreignKeyName, columnName, referencedTableSource, referencedColumnName, isCascadeDelete);
foreignKeys.Add(foreignKeyInfo);
}
}
return(foreignKeys);
}
}
catch (Exception e) {
OSTrace.Error(string.Format("Failed to retrieve foreign key information from database. Tables: {0}", tableNames), e);
return(new List <ITableSourceForeignKeyInfo>());
}
}
protected IEnumerable <ITableSourceForeignKeyInfo> GetForeignKeys(IEnumerable <CacheTableSourceInfo> tableSources)
{
string paramPrefix = DatabaseServices.ExecutionService.ParameterPrefix;
string tableNames = "";
try {
tableNames = "'" + tableSources.Select(t => t.Name).StrCat("','") + "'";
IList <ITableSourceForeignKeyInfo> foreignKeys = new List <ITableSourceForeignKeyInfo>();
using (IDbConnection conn = DatabaseServices.TransactionService.CreateConnection()) {
foreach (CacheTableSourceInfo tableSource in tableSources)
{
string query = string.Format(
@"SELECT t.TABLE_NAME, c.CONSTRAINT_NAME, c.COLUMN_NAME, c.REFERENCED_TABLE_NAME,
c.REFERENCED_COLUMN_NAME, r.DELETE_RULE
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS t ON c.CONSTRAINT_NAME = t.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS r ON r.CONSTRAINT_NAME = t.CONSTRAINT_NAME
WHERE t.CONSTRAINT_TYPE = 'FOREIGN KEY'
AND t.TABLE_SCHEMA = {0}
AND c.CONSTRAINT_SCHEMA = {0}
AND r.CONSTRAINT_SCHEMA = {0}
AND t.TABLE_NAME = {1}", paramPrefix + "schema", paramPrefix + "table");
IDbCommand cmd = DatabaseServices.ExecutionService.CreateCommand(conn, query);
DatabaseServices.ExecutionService.CreateParameter(cmd, paramPrefix + "schema", DbType.String, tableSource.Database.Name);
DatabaseServices.ExecutionService.CreateParameter(cmd, paramPrefix + "table", DbType.String, tableSource.Name);
cmd.CommandTimeout = QueryTimeout;
using (IDataReader reader = cmd.ExecuteReader()) {
while (reader.Read())
{
string tableName = (string)reader["TABLE_NAME"];
string foreignKeyName = (string)reader["CONSTRAINT_NAME"];
string columnName = (string)reader["COLUMN_NAME"];
string referencedColumnName = (string)reader["REFERENCED_COLUMN_NAME"];
string referencedTableName = (string)reader["REFERENCED_TABLE_NAME"];
bool isCascadeDelete = "CASCADE".EqualsIgnoreCase((string)reader["DELETE_RULE"]);
string qualifiedReferencedTableName = GetQualifiedIdentifier(tableSource.Database.Name, referencedTableName);
ITableSourceInfo referencedTableSource = new CacheTableSourceInfo(DatabaseServices, tableSource.Database, referencedTableName, qualifiedReferencedTableName);
ITableSourceForeignKeyInfo foreignKeyInfo = new CacheTableSourceForeignKeyInfo(tableSource, foreignKeyName, columnName, referencedTableSource, referencedColumnName, isCascadeDelete);
foreignKeys.Add(foreignKeyInfo);
}
}
// Also check if there are relationships defined on columns, these also get added as a foreign key
// If a relationship is found check if this column does not already have a foreign key, if it has then
// do not add another foreign key.
// If a column references another object the type will not start with %Library, so we check for all runtimetypes
// taht do not start with %, this indicates reference to another object. Further we only want to know about references to other
// persistent objects.
string queryRelationships = string.Format(
@"SELECT P.SqlFieldName AS COLUMN_NAME, P.RuntimeType AS RUNTIME_TYPE, P.Collection,
P.Relationship AS RELATIONSHIP, P.Cardinality,
P.Parent, P.SqlListDelimiter, P.SqlListType, P.Name AS ColumnName
FROM %Dictionary.CompiledClass C, %Dictionary.CompiledProperty P
WHERE P.parent = C.ID
AND NOT P.RuntimeType %STARTSWITH '%'
AND C.SqlSchemaName = {0}
AND C.SqlTableName = {1}", paramPrefix + "schema", paramPrefix + "table");
IDbCommand cmdRelationships = DatabaseServices.ExecutionService.CreateCommand(conn, queryRelationships);
DatabaseServices.ExecutionService.CreateParameter(cmdRelationships, paramPrefix + "schema", DbType.String, tableSource.Database.Name);
DatabaseServices.ExecutionService.CreateParameter(cmdRelationships, paramPrefix + "table", DbType.String, tableSource.Name);
cmdRelationships.CommandTimeout = QueryTimeout;
using (IDataReader readerRel = cmdRelationships.ExecuteReader()) {
while (readerRel.Read())
{
string runtimeType = (string)readerRel["RUNTIME_TYPE"];
// Check if the Runtime Type is that of a persistent object
bool isPersistentType = false;
string queryPersistentType = string.Format(
@"SELECT ID, Name, ClassType FROM %Dictionary.CompiledClass WHERE Name = '" + runtimeType + @"'");
IDbCommand cmdPersistentType = DatabaseServices.ExecutionService.CreateCommand(conn, queryPersistentType);
using (IDataReader readerPersistent = cmdPersistentType.ExecuteReader()) {
if (readerPersistent.Read())
{
string classType = (string)readerPersistent["ClassType"];
isPersistentType = classType.CompareTo("persistent") == 0 ? true : false;
}
}
if (isPersistentType)
{
// Check if there is such a foreign key
string referencedTableName = ExtractTableNameFromQualifiedName(runtimeType);
string columnName = (string)readerRel["COLUMN_NAME"];
int idx = foreignKeys.IndexOf(t => t.ColumnName.EqualsIgnoreCase(columnName));
if (idx < 0)
{
// Add a foreign key constraint which is named this column's name pre-pended with "FK_"[referenced table name]
// and the referenced column is the "ID" column of the referenced table.
string foreignKeyName = "FK_" + referencedTableName + "_" + columnName;
string qualifiedReferencedTableName = GetQualifiedIdentifier(tableSource.Database.Name, referencedTableName);
ITableSourceInfo referencedTableSource = new CacheTableSourceInfo(DatabaseServices, tableSource.Database, referencedTableName, qualifiedReferencedTableName);
ITableSourceForeignKeyInfo foreignKeyInfo = new CacheTableSourceForeignKeyInfo(tableSource, foreignKeyName, columnName, referencedTableSource, "ID", false);
foreignKeys.Add(foreignKeyInfo);
}
}
}
}
} // foreach
}
return(foreignKeys);
}
catch (Exception e) {
OSTrace.Error(string.Format("Failed to retrieve foreign key information from database. Tables: {0}", tableNames), e);
return(new List <ITableSourceForeignKeyInfo>());
}
}
