Обсуждение: DatabaseMetaData getImportedKeys() order
Hi, In org.postgresql.jdbc2.AbstractJdbc2DatabaseMetaData both getImportedKeys() and getExportedKeys() call getImportedExportedKeys() which order its result strictly according to java.sql.DatabaseMetaData : PKTABLE_CAT, PKTABLE_SCHEM, PKTABLE_NAME, KEY_SEQ. But this is not enough when 2 multi-field keys point to the same table. E.g. : CREATE TABLE test."PERSON" ( "FIRST_NAME" character varying(100) NOT NULL, "LAST_NAME" character varying(100) NOT NULL, "FIRST_NAME_PARENT_1" character varying(100), "LAST_NAME_PARENT_1" character varying(100), "FIRST_NAME_PARENT_2" character varying(100), "LAST_NAME_PARENT_2" character varying(100), CONSTRAINT "PERSON_pkey" PRIMARY KEY ("FIRST_NAME" , "LAST_NAME" ), CONSTRAINT "PARENT_1_fkey" FOREIGN KEY ("FIRST_NAME_PARENT_1", "LAST_NAME_PARENT_1") REFERENCES test."PERSON" ("FIRST_NAME", "LAST_NAME") MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT "PARENT_2_fkey" FOREIGN KEY ("FIRST_NAME_PARENT_2", "LAST_NAME_PARENT_2") REFERENCES test."PERSON" ("FIRST_NAME", "LAST_NAME") MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE ); For this method to be useful the constraint must be used otherwise we can't know which first name goes with which last name. The H2 database (and I believe MySQL as well) order by PKTABLE_CAT, PKTABLE_SCHEM, PKTABLE_NAME, FK_NAME, KEY_SEQ (see https://code.google.com/p/h2database/source/browse/tags/version-1.3.172/h2/src/main/org/h2/jdbc/JdbcDatabaseMetaData.java ). For 9.2-1003, con.conname just needs to be added before pos.n at line 3440 : --- AbstractJdbc2DatabaseMetaData-9.2-1003.java 2013-07-22 17:07:25.001273200 +0200 +++ AbstractJdbc2DatabaseMetaData-multiField.java 2013-07-22 17:09:16.570654600 +0200 @@ -3437,13 +3437,16 @@ sql += " AND fkc.relname = " + escapeQuotes(foreignTable); } + // con.conname required otherwise we cannot assemble + // multi-field foreign keys when a table has multiple links + // to another if (primaryTable != null) { - sql += " ORDER BY fkn.nspname,fkc.relname,pos.n"; + sql += " ORDER BY fkn.nspname,fkc.relname,con.conname,pos.n"; } else { - sql += " ORDER BY pkn.nspname,pkc.relname,pos.n"; + sql += " ORDER BY pkn.nspname,pkc.relname,con.conname,pos.n"; } return createMetaDataStatement().executeQuery(sql); Cheers, Sylvain
Sylvain,
I made the changes and added this test case
https://github.com/davecramer/pgjdbc/compare/REL9_2_STABLE, can you check it to see if it makes sense ?
On Mon, Jul 22, 2013 at 11:20 AM, Sylvain Cuaz <sylvain@ilm-informatique.fr> wrote:
Hi,
In org.postgresql.jdbc2.AbstractJdbc2DatabaseMetaData both getImportedKeys() and getExportedKeys() call getImportedExportedKeys() which order its result strictly according to java.sql.DatabaseMetaData : PKTABLE_CAT, PKTABLE_SCHEM, PKTABLE_NAME, KEY_SEQ. But this is not enough when 2 multi-field keys point to the same table. E.g. :
CREATE TABLE test."PERSON"
(
"FIRST_NAME" character varying(100) NOT NULL,
"LAST_NAME" character varying(100) NOT NULL,
"FIRST_NAME_PARENT_1" character varying(100),
"LAST_NAME_PARENT_1" character varying(100),
"FIRST_NAME_PARENT_2" character varying(100),
"LAST_NAME_PARENT_2" character varying(100),
CONSTRAINT "PERSON_pkey" PRIMARY KEY ("FIRST_NAME" , "LAST_NAME" ),
CONSTRAINT "PARENT_1_fkey" FOREIGN KEY ("FIRST_NAME_PARENT_1", "LAST_NAME_PARENT_1")
REFERENCES test."PERSON" ("FIRST_NAME", "LAST_NAME") MATCH SIMPLE
ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT "PARENT_2_fkey" FOREIGN KEY ("FIRST_NAME_PARENT_2", "LAST_NAME_PARENT_2")
REFERENCES test."PERSON" ("FIRST_NAME", "LAST_NAME") MATCH SIMPLE
ON UPDATE CASCADE ON DELETE CASCADE
);
For this method to be useful the constraint must be used otherwise we can't know which first name goes with which last name. The H2 database (and I believe MySQL as well) order by PKTABLE_CAT, PKTABLE_SCHEM, PKTABLE_NAME, FK_NAME, KEY_SEQ (see https://code.google.com/p/h2database/source/browse/tags/version-1.3.172/h2/src/main/org/h2/jdbc/JdbcDatabaseMetaData.java ).
For 9.2-1003, con.conname just needs to be added before pos.n at line 3440 :
--- AbstractJdbc2DatabaseMetaData-9.2-1003.java 2013-07-22 17:07:25.001273200 +0200
+++ AbstractJdbc2DatabaseMetaData-multiField.java 2013-07-22 17:09:16.570654600 +0200
@@ -3437,13 +3437,16 @@
sql += " AND fkc.relname = " + escapeQuotes(foreignTable);
}
+ // con.conname required otherwise we cannot assemble
+ // multi-field foreign keys when a table has multiple links
+ // to another
if (primaryTable != null)
{
- sql += " ORDER BY fkn.nspname,fkc.relname,pos.n";
+ sql += " ORDER BY fkn.nspname,fkc.relname,con.conname,pos.n";
}
else
{
- sql += " ORDER BY pkn.nspname,pkc.relname,pos.n";
+ sql += " ORDER BY pkn.nspname,pkc.relname,con.conname,pos.n";
}
return createMetaDataStatement().executeQuery(sql);
Cheers,
Sylvain
--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc
Le 29/07/2013 15:46, Dave Cramer a écrit : > Sylvain, > > I made the changes and added this test case > > https://github.com/davecramer/pgjdbc/compare/REL9_2_STABLE, can you check > it to see if it makes sense ? Your patch isn't right : the first pos.n must be removed so that the order is "nspname,relname,con.conname,pos.n". Your test doesn't use the order so it works equally before and after the patch. Replace the for loop with this snippet : final List<String> fkNames = new ArrayList<String>(); int lastFieldCount = -1; while (rs.next()) { // destination table (all foreign keys point to the same) String pkTableName = rs.getString("PKTABLE_NAME"); assertEquals(tableName, pkTableName); // destination fields String pkColumnName = rs.getString("PKCOLUMN_NAME"); assertTrue("FIRST_NAME".equals(pkColumnName) || "LAST_NAME".equals(pkColumnName)); // source table (all foreign keys are in the same) String fkTableName = rs.getString("FKTABLE_NAME"); assertEquals(tableName, fkTableName); // foreign key name String fkName = rs.getString("FK_NAME"); // sequence number within the foreign key int seq = rs.getInt("KEY_SEQ"); if (seq == 1) { // begin new foreign key assertFalse(fkNames.contains(fkName)); fkNames.add(fkName); // all foreign keys have 2 fields assertTrue(lastFieldCount < 0 || lastFieldCount == 2); } else { // continue foreign key, i.e. fkName matches the last foreign key assertEquals(fkNames.get(fkNames.size() - 1), fkName); // seq always increases by 1 assertTrue(seq == lastFieldCount + 1); } lastFieldCount = seq; } // there's more than one foreign key from a table to another assertEquals(2, fkNames.size()); Cheers, Sylvain
David, I saw you committed my changes into master, thanks. Cheers, Sylvain