Re: DatabaseMetaData getImportedKeys() order - Mailing list pgsql-jdbc
From | Dave Cramer |
---|---|
Subject | Re: DatabaseMetaData getImportedKeys() order |
Date | |
Msg-id | CADK3HHLG6QkfA7=sQtd7Jarb8TfWfYUUN4Ft3Q=FvF7tZ95C2A@mail.gmail.com Whole thread Raw |
In response to | DatabaseMetaData getImportedKeys() order (Sylvain Cuaz <sylvain@ilm-informatique.fr>) |
Responses |
Re: DatabaseMetaData getImportedKeys() order
(Sylvain Cuaz <sylvain@ilm-informatique.fr>)
|
List | pgsql-jdbc |
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
pgsql-jdbc by date: