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); }