protected IEnumerable <ITableSourceForeignKeyInfo> GetForeignKeys(IEnumerable <MySQLTableSourceInfo> tableSources)
{
string tableNames = "";
try {
tableNames = "'" + tableSources.Select(t => t.Name).StrCat("','") + "'";
string paramPrefix = DatabaseServices.ExecutionService.ParameterPrefix;
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);
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"]);
MySQLTableSourceInfo tableSource = tableSources.First(t => t.Name.EqualsIgnoreCase(tableName));
string qualifiedReferencedTableName = GetQualifiedIdentifier(tableSource.Database.Name, referencedTableName);
ITableSourceInfo referencedTableSource = new MySQLTableSourceInfo(DatabaseServices, tableSource.Database, referencedTableName, qualifiedReferencedTableName);
ITableSourceForeignKeyInfo foreignKeyInfo = new MySQLTableSourceForeignKeyInfo(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>());
}
}
public MySQLPlatformTableSourceInfo(MySQLTableSourceInfo tableSource)
: base(tableSource.DatabaseServices, tableSource.Database, tableSource.Name, tableSource.QualifiedName)
{
}
