public static DataRelationshipDefinition[] GetDataRelationshipDefinitions(DatabaseAgent sourceDb)
{
if (dataRelationships == null)
{
dataRelationships = new Dictionary <DatabaseAgent, DataRelationshipDefinition[]>();
}
if (!dataRelationships.ContainsKey(sourceDb))
{
string sql = @"SELECT FK.constraint_name as ForeignKeyName,
FK.table_name as ForeignKeyTable,
FKU.column_name as ForeignKeyColumn,
UK.constraint_name as PrimaryKeyName,
UK.table_name as PrimaryKeyTable,
UKU.column_name as PrimaryKeyColumn
FROM Information_Schema.Table_Constraints AS FK
INNER JOIN
Information_Schema.Key_Column_Usage AS FKU
ON FK.constraint_type = 'FOREIGN KEY' AND
FKU.constraint_name = FK.constraint_name
INNER JOIN
Information_Schema.Referential_Constraints AS RC
ON RC.constraint_name = FK.constraint_name
INNER JOIN
Information_Schema.Table_Constraints AS UK
ON UK.constraint_name = RC.unique_constraint_name
INNER JOIN
Information_Schema.Key_Column_Usage AS UKU
ON UKU.constraint_name = UK.constraint_name AND
UKU.ordinal_position =FKU.ordinal_position";
List <DataRelationshipDefinition> list = new List <DataRelationshipDefinition>();
DataTable foreignKeyData = sourceDb.GetDataTableFromSql(sql);
foreach (DataRow row in foreignKeyData.Rows)
{
list.Add(DataRelationshipDefinition.CreateFromDataRow(row));
}
DataRelationshipDefinition[] retVal = list.ToArray();
dataRelationships.Add(sourceDb, retVal);
return(retVal);
}
else
{
return(dataRelationships[sourceDb]);
}
}
public DataTable GetFireBirdTableDetail(string tableName, DatabaseAgent agent)
{
string tableDetailQuery = @"SELECT r.RDB$FIELD_NAME AS field_name,
r.RDB$DESCRIPTION AS field_description,
r.RDB$DEFAULT_VALUE AS field_default_value,
r.RDB$NULL_FLAG AS field_not_null_constraint,
f.RDB$FIELD_LENGTH AS field_length,
f.RDB$FIELD_PRECISION AS field_precision,
f.RDB$FIELD_SCALE AS field_scale,
CASE f.RDB$FIELD_TYPE
WHEN 261 THEN 'BLOB'
WHEN 14 THEN 'CHAR'
WHEN 40 THEN 'CSTRING'
WHEN 11 THEN 'D_FLOAT'
WHEN 27 THEN 'DOUBLE'
WHEN 10 THEN 'FLOAT'
WHEN 16 THEN 'INT64'
WHEN 8 THEN 'INTEGER'
WHEN 9 THEN 'QUAD'
WHEN 7 THEN 'SMALLINT'
WHEN 12 THEN 'DATE'
WHEN 13 THEN 'TIME'
WHEN 35 THEN 'TIMESTAMP'
WHEN 37 THEN 'VARCHAR'
ELSE 'UNKNOWN'
END AS field_type,
f.RDB$FIELD_SUB_TYPE AS field_subtype,
coll.RDB$COLLATION_NAME AS field_collation,
cset.RDB$CHARACTER_SET_NAME AS field_charset
FROM RDB$RELATION_FIELDS r
LEFT JOIN RDB$FIELDS f ON r.RDB$FIELD_SOURCE = f.RDB$FIELD_NAME
LEFT JOIN RDB$COLLATIONS coll ON f.RDB$COLLATION_ID = coll.RDB$COLLATION_ID
LEFT JOIN RDB$CHARACTER_SETS cset ON f.RDB$CHARACTER_SET_ID = cset.RDB$CHARACTER_SET_ID
WHERE r.RDB$RELATION_NAME='" + tableName + @"'
ORDER BY r.RDB$FIELD_POSITION";
return(agent.GetDataTableFromSql(tableDetailQuery));
}
public static DataRelationshipDefinition[] GetDataRelationshipDefinitions(DatabaseAgent sourceDb)
{
if (dataRelationships == null)
dataRelationships = new Dictionary<DatabaseAgent, DataRelationshipDefinition[]>();
if (!dataRelationships.ContainsKey(sourceDb))
{
string sql = @"SELECT FK.constraint_name as ForeignKeyName,
FK.table_name as ForeignKeyTable,
FKU.column_name as ForeignKeyColumn,
UK.constraint_name as PrimaryKeyName,
UK.table_name as PrimaryKeyTable,
UKU.column_name as PrimaryKeyColumn
FROM Information_Schema.Table_Constraints AS FK
INNER JOIN
Information_Schema.Key_Column_Usage AS FKU
ON FK.constraint_type = 'FOREIGN KEY' AND
FKU.constraint_name = FK.constraint_name
INNER JOIN
Information_Schema.Referential_Constraints AS RC
ON RC.constraint_name = FK.constraint_name
INNER JOIN
Information_Schema.Table_Constraints AS UK
ON UK.constraint_name = RC.unique_constraint_name
INNER JOIN
Information_Schema.Key_Column_Usage AS UKU
ON UKU.constraint_name = UK.constraint_name AND
UKU.ordinal_position =FKU.ordinal_position";
List<DataRelationshipDefinition> list = new List<DataRelationshipDefinition>();
DataTable foreignKeyData = sourceDb.GetDataTableFromSql(sql);
foreach (DataRow row in foreignKeyData.Rows)
{
list.Add(DataRelationshipDefinition.CreateFromDataRow(row));
}
DataRelationshipDefinition[] retVal = list.ToArray();
dataRelationships.Add(sourceDb, retVal);
return retVal;
}
else
{
return dataRelationships[sourceDb];
}
}