db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From abr...@apache.org
Subject svn commit: r546312 - /db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/DatabaseMetaDataTest.java
Date Mon, 11 Jun 2007 22:57:24 GMT
Author: abrown
Date: Mon Jun 11 15:57:24 2007
New Revision: 546312

URL: http://svn.apache.org/viewvc?view=rev&rev=546312
Log:
DERBY-2758: Add a JUnit test case to the existing JUnit DatabaseMetaDataTest.

Modified:
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/DatabaseMetaDataTest.java

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/DatabaseMetaDataTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/DatabaseMetaDataTest.java?view=diff&rev=546312&r1=546311&r2=546312
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/DatabaseMetaDataTest.java
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/DatabaseMetaDataTest.java
Mon Jun 11 15:57:24 2007
@@ -2474,4 +2474,243 @@
         }
     }
  
+    /**
+     * Execute and check the ODBC variant of getImported/Exported keys, which
+     * uses the SQLFOREIGNKEYS system procedure to provide the same information
+     * to ODBC clients.  Note that for "correctness" we just compare the results
+     * to those of the equivalent JDBC calls; this fixture assumes that the
+     * the JDBC calls return correct results (testing of the JDBC results occurs
+     * elsewhere, esp. jdbcapi/metadata_test.java).
+     */
+    public void testGetXXportedKeysODBC() throws SQLException, IOException
+    {
+        Statement st = createStatement();
+
+        // Create some simple tables with primary/foreign keys.
+
+        st.execute("create table pkt1 (i int not null, c char(1) not null)");
+        st.execute("create table pkt2 (i int not null, c char(1) not null)");
+        st.execute("create table pkt3 (i int not null, c char(1) not null)");
+
+        st.execute("alter table pkt1 add constraint pk1 primary key (i)");
+        st.execute("alter table pkt2 add constraint pk2 primary key (c)");
+        st.execute("alter table pkt3 add constraint pk3 primary key (i, c)");
+
+        st.execute("create table fkt1 (fi int, fc char(1), vc varchar(80))");
+        st.execute("create table fkt2 (fi int, fc char(1), vc varchar(80))");
+
+        st.execute("alter table fkt1 add constraint fk1 foreign key (fi) " +
+            "references pkt1(i)");
+
+        st.execute("alter table fkt1 add constraint fk2 foreign key (fc) " +
+            "references pkt2(c)");
+
+        st.execute("alter table fkt2 add constraint fk3 foreign key (fi, fc) " +
+            "references pkt3(i, c)");
+
+        /* Check for all arguments NULL; SQLFOREIGNKEYS allows this, though
+         * there is no equivalent in JDBC.
+         */
+        checkODBCKeys(null, null, null, null, null, null);
+
+        /* Run equivalent of getImportedKeys(), getExportedKeys(),
+         * and getCrossReference for each of the primary/foreign
+         * key pairs.
+         */
+
+        checkODBCKeys(null, null, null, null, null, "FKT1");
+        checkODBCKeys(null, null, "PKT1", null, null, null);
+        checkODBCKeys(null, null, "PKT1", null, null, "FKT1");
+
+        checkODBCKeys(null, null, null, null, null, "FKT2");
+        checkODBCKeys(null, null, "PKT2", null, null, null);
+        checkODBCKeys(null, null, "PKT2", null, null, "FKT2");
+
+        checkODBCKeys(null, null, null, null, null, "FKT3");
+        checkODBCKeys(null, null, "PKT3", null, null, null);
+        checkODBCKeys(null, null, "PKT3", null, null, "FKT3");
+
+        // Reverse primary and foreign tables.
+
+        checkODBCKeys(null, null, "FKT1", null, null, null);
+        checkODBCKeys(null, null, null, null, null, "PKT3");
+        checkODBCKeys(null, null, "FKT1", null, null, "PKT1");
+        checkODBCKeys(null, null, "FKT2", null, null, "PKT2");
+        checkODBCKeys(null, null, "FKT3", null, null, "PKT3");
+
+        // Mix-and-match primary key tables and foreign key tables.
+
+        checkODBCKeys(null, null, "PKT1", null, null, "FKT2");
+        checkODBCKeys(null, null, "PKT1", null, null, "FKT3");
+        checkODBCKeys(null, null, "PKT2", null, null, "FKT3");
+
+        checkODBCKeys(null, null, "FKT1", null, null, "PKT2");
+        checkODBCKeys(null, null, "FKT1", null, null, "PKT3");
+        checkODBCKeys(null, null, "FKT2", null, null, "PKT3");
+
+        // Cleanup.
+
+        st.execute("drop table fkt1");
+        st.execute("drop table fkt2");
+        st.execute("drop table pkt1");
+        st.execute("drop table pkt2");
+        st.execute("drop table pkt3");
+        st.close();
+    }
+
+    /**
+     * Execute a call to the ODBC system procedure "SQLFOREIGNKEYS"
+     * and verify the results by comparing them with the results of
+     * an equivalent JDBC call (if one exists).
+     */
+    private void checkODBCKeys(String pCatalog, String pSchema,
+        String pTable, String fCatalog, String fSchema, String fTable)
+        throws SQLException, IOException
+    {
+        /* To mimic the behavior of the issue which prompted this test
+         * (DERBY-2758) we only send the "ODBC" option; we do *not*
+         * explicitly send the "IMPORTEDKEY=1" nor "EXPORTEDKEY=1"
+         * options, as DB2 Runtime Client does not send those, either.
+         * This effectively means that the SQLFOREIGNKEYS function
+         * will always be mapped to getCrossReference() internally.
+         * Since that worked fine prior to 10.3, we need to preserve
+         * that behavior if we want to maintina backward compatibility.
+         */
+        CallableStatement cs = prepareCall(
+            "CALL SYSIBM.SQLFOREIGNKEYS(?, ?, ?, ?, ?, ?, " +
+            "'DATATYPE=''ODBC''')");
+
+        cs.setString(1, pCatalog);
+        cs.setString(2, pSchema);
+        cs.setString(3, pTable);
+        cs.setString(4, fCatalog);
+        cs.setString(5, fSchema);
+        cs.setString(6, fTable);
+        
+        cs.execute();
+        ResultSet odbcrs = cs.getResultSet();
+        assertNotNull(odbcrs);
+        
+        /* Returned ResultSet will have the same shape as
+         * DatabaseMetaData.getImportedKeys()
+         */
+        checkODBCKeysShape(odbcrs);
+        
+        /* Expect the contents of JDBC and ODBC metadata to be the same,
+         * except if both pTable and cTable are null.  In that case
+         * ODBC treats everything as a wildcard (and so effectively
+         * returns all foreign key columns), while JDBC throws
+         * an error.
+         */
+
+        ResultSet dmdrs = null;
+        if ((pTable != null) && (fTable == null))
+            dmdrs = getDMD().getExportedKeys(pCatalog, pSchema, pTable);
+        else if ((pTable == null) && (fTable != null))
+            dmdrs = getDMD().getImportedKeys(fCatalog, fSchema, fTable);
+        else if (pTable != null)
+        {
+            dmdrs = getDMD().getCrossReference(
+                pCatalog, pSchema, pTable, fCatalog, fSchema, fTable);
+        }
+        else
+        {
+            /* Must be the case of pTable and fTable both null.  Check
+             * results for ODBC (one row for each foreign key column)
+             * and assert error for JDBC.
+             */
+
+            JDBC.assertFullResultSet(odbcrs,
+                new String [][] {
+                    {"","APP","PKT1","I","","APP","FKT1","FI",
+                        "1","3","3","FK1","PK1","7"},
+                    {"","APP","PKT2","C","","APP","FKT1","FC",
+                        "1","3","3","FK2","PK2","7"},
+                    {"","APP","PKT3","I","","APP","FKT2","FI",
+                        "1","3","3","FK3","PK3","7"},
+                    {"","APP","PKT3","C","","APP","FKT2","FC",
+                        "2","3","3","FK3","PK3","7"}
+                });
+
+            try {
+
+                getDMD().getCrossReference(
+                    pCatalog, pSchema, pTable, fCatalog, fSchema, fTable);
+
+                fail("Expected error from call to DMD.getCrossReference() " +
+                    "with NULL primary and foreign key tables.");
+
+            } catch (SQLException se) {
+
+                /* Looks like embedded and client have different (but similar)
+                 * errors for this...
+                 */
+                assertSQLState(usingEmbedded() ? "XJ103" : "XJ110", se);
+
+            }
+            
+        }
+                
+        /* If both pTable and fTable are null then dmdrs will be null, as
+         * well.  So nothing to compare in that case.
+         */
+        if (dmdrs != null)
+        {
+            // Next call closes both results sets as a side effect.
+            JDBC.assertSameContents(odbcrs, dmdrs);
+        }
+        
+        cs.close();
+    }
+
+    /**
+     * Check the shape of the ResultSet from a call to the ODBC function
+     * SQLForeignKeys.
+     */
+    private void checkODBCKeysShape(ResultSet rs) throws SQLException
+    {
+        assertMetaDataResultSet(rs,
+
+            // ODBC and JDBC agree on column names and types.
+
+            new String[] {
+                "PKTABLE_CAT", "PKTABLE_SCHEM", "PKTABLE_NAME", "PKCOLUMN_NAME",
+                "FKTABLE_CAT", "FKTABLE_SCHEM", "FKTABLE_NAME", "FKCOLUMN_NAME",
+                "KEY_SEQ", "UPDATE_RULE", "DELETE_RULE", "FK_NAME",
+                "PK_NAME", "DEFERRABILITY"
+            },
+
+            new int[] {
+                Types.VARCHAR, Types.VARCHAR, Types.VARCHAR, Types.VARCHAR,
+                Types.VARCHAR, Types.VARCHAR, Types.VARCHAR, Types.VARCHAR,
+                Types.SMALLINT, Types.SMALLINT, Types.SMALLINT, Types.VARCHAR,
+                Types.VARCHAR, Types.SMALLINT
+            },
+
+            // Nullability comes from ODBC spec, not JDBC.
+
+            /* DERBY-2797: Nullability of columns in ODBC's SQLForeignKey
+             * result set is incorrect.  Un-comment the correct boolean array
+             * when DERBY-2797 has been fixed.
+             */
+
+            // incorrect
+            new boolean[] {
+                true, false, false, false,
+                true, false, false, false,
+                true, true, true, false,
+                false, true
+            }
+
+            // correct
+            /* new boolean[] {
+                true, true, false, false,
+                true, true, false, false,
+                false, true, true, true,
+                true, true
+            } */
+
+        );        
+    }
+    
 }



Mime
View raw message