public override IList <ForeignReference> GetForeignReferences(bool isCacheable = false)
{
if (isCacheable)
{
return(_dataCache.GetItem(
string.Format("{0}.{1}.references", _databaseDefinition.DatabaseHost, _databaseDefinition.DatabaseName),
() => GetForeignReferences(false)));
}
var references = new List <ForeignReference>();
using (var connection = CreateConnection())
{
var command = connection.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = (
"SELECT r.conrelid, n.nspname, c.relname, conname, pg_catalog.pg_get_constraintdef(r.oid, true) as condef " +
" FROM pg_catalog.pg_constraint r " +
" LEFT JOIN pg_catalog.pg_class c on c.oid = r.conrelid " +
" LEFT JOIN pg_catalog.pg_namespace n on n.oid = c.relnamespace " +
" WHERE r.contype = 'f' " +
" ORDER BY 1"
);
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
var conmin = reader.GetString(4)
.RegexReplace("^FOREIGN KEY.*REFERENCES ", "")
.Replace("\"", "");
var contable = conmin
.RegexReplace("\\(.*", "");
var concolumns = conmin
.RegexReplace("^.*\\(", "")
.RegexReplace("\\).*", "")
.Split(',');
var reference = new ForeignReference()
{
SourceOID = Convert.ToString(reader.GetValue(0)),
SourceSchema = reader.GetString(1),
SourceTable = reader.GetString(2),
ReferenceName = reader.GetString(3),
ReferenceTable = contable,
ReferenceColumns = concolumns
};
references.Add(reference);
}
}
}
return(references);
}
public override IList <ForeignReference> GetForeignReferences(bool isCacheable)
{
var references = new List <ForeignReference>();
using (var connection = CreateConnection())
{
var query = (
"SELECT RC.CONSTRAINT_NAME FK_Name " +
" , KF.TABLE_SCHEMA FK_Schema" +
" , KF.TABLE_NAME FK_Table" +
" , KF.COLUMN_NAME FK_Column" +
" , RC.UNIQUE_CONSTRAINT_NAME PK_Name" +
" , KP.TABLE_SCHEMA PK_Schema" +
" , KP.TABLE_NAME PK_Table" +
" , KP.COLUMN_NAME PK_Column" +
" , RC.MATCH_OPTION MatchOption" +
" , RC.UPDATE_RULE UpdateRule" +
" , RC.DELETE_RULE DeleteRule" +
" FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC" +
" JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KF ON RC.CONSTRAINT_NAME = KF.CONSTRAINT_NAME" +
" JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KP ON RC.UNIQUE_CONSTRAINT_NAME = KP.CONSTRAINT_NAME"
);
var command = connection.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = query;
using (var reader = command.ExecuteReader())
{
// foreign key
var fkName = reader.GetString(0);
var fkSchema = reader.GetString(1);
var fkTable = reader.GetString(2);
// source key
var skSchema = reader.GetString(5);
var skTable = reader.GetString(6);
var skColumn = reader.GetString(7);
// find the reference
var reference = new ForeignReference()
{
SourceSchema = fkSchema,
SourceTable = fkTable,
ReferenceName = fkName,
ReferenceTable = skTable
};
references.Add(reference);
}
}
return(references);
}
