Обсуждение: 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

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