Re: DatabaseMetaData getImportedKeys() order

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 ?

Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca

On Mon, Jul 22, 2013 at 11:20 AM, Sylvain Cuaz
<sylvain(at)ilm-informatique(dot)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<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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/**mailpref/pgsql-jdbc<http://www.postgresql.org/mailpref/pgsql-jdbc>
>