Commits

Grace Batumbya  committed d1715e3

Fixes #47 - MetaData.getExportedKeys() returns empty string for a named foreign key

  • Participants
  • Parent commits 22671ae

Comments (0)

Files changed (4)

 Work in progress:
-
+* Issue 47 - MetaData.getExportedKeys() returns empty string for a named foreign key
+* Issue 46 - org.sqlite.MetaData.getExportedKeys constructs incorrect SQL
+* Issue 44 - getExportedKeys throws Null Exception for foreign key without explicit primary column name
+    1. Now returns keys for foreign key definitions like "create table referring (id, foreign key references referred)"
 * Implemented getPercision(int) and getScale(int) on ResultSetMetaData.
 * Issue 43 - ResultSetMetaData does not return the SQL Type if there are no results from the query.
     1. ResultSetMetaData.getColumnTypeName() now gets type from either 'create table' statement or CAST(expr AS TYPE) otherwise sqlite3_value_type.

File src/main/java/org/sqlite/MetaData.java

     }
 
     /**
+     * Pattern used to extract a named primary key.
+     */
+     protected final static Pattern FK_NAMED_PATTERN =
+         Pattern.compile(".* constraint +(.*?) +foreign +key *\\((.*?)\\).*", Pattern.CASE_INSENSITIVE);
+
+     /**
      * @see java.sql.DatabaseMetaData#getExportedKeys(java.lang.String, java.lang.String,
      *      java.lang.String)
      */
             for (String tbl : tableList) {
                 try {
                     fk = stat.executeQuery("pragma foreign_key_list('" + escape(tbl) + "')");
+                } catch (SQLException e) {
+                    if (e.getErrorCode() == Codes.SQLITE_DONE) 
+                        continue; // expected if table has no foreign keys
+
+                    throw e;
+                }
+
+                Stmt stat2 = null;
+                try {
+                    stat2 = (Stmt)conn.createStatement();
                     while(fk.next()) {
                         int keySeq = fk.getInt(2) + 1;
                         String PKTabName = fk.getString(3).toLowerCase();
-    
+
                         if (PKTabName == null || !PKTabName.equals(target)) {
                             continue;
                         }
-    
+
                         String PKColName = fk.getString(5);
                         PKColName = (PKColName == null) ? pkColumns[0] : PKColName.toLowerCase();
 
                         exportedKeysQuery
                             .append(count > 0 ? " union all select " : "select ")
                             .append(Integer.toString(keySeq)).append(" as ks, lower('")
-                            .append(escape(tbl)).append("') as fkn, lower('")
+                            .append(escape(tbl)).append("') as fkt, lower('")
                             .append(escape(fk.getString(4))).append("') as fcn, '")
                             .append(escape(PKColName)).append("' as pcn, ")
                             .append(RULE_MAP.get(fk.getString(6))).append(" as ur, ")
-                            .append(RULE_MAP.get(fk.getString(7))).append(" as dr");
-    
+                            .append(RULE_MAP.get(fk.getString(7))).append(" as dr, ");
+
+                        String fkName = "''";
+                        rs = stat2.executeQuery("select sql from sqlite_master where" +
+                            " lower(name) = lower('" + escape(tbl) + "')");
+                        if (rs.next())
+                        {
+                            Matcher matcher = FK_NAMED_PATTERN.matcher(rs.getString(1));
+                            if (matcher.find()){
+                                fkName = '\'' + escape(matcher.group(1).toLowerCase()) + '\'';
+                            }
+                        }
+                        rs.close();
+
+                        exportedKeysQuery.append(fkName).append(" as fkn");
                         count++;
                     }
                 }
-                catch (SQLException e) {
-                    // continue
-                    //TODO:
-                }
                 finally {
-                    if (fk != null)
-                        try{
-                            fk.close();
-                        }catch(SQLException e) {}
+                    try{
+                        if (rs != null) rs.close();
+                    }catch(SQLException e) {}
+                    try{
+                        if (stat2 != null) stat2.close();
+                    }catch(SQLException e) {}
+                    try{
+                        if (fk != null) fk.close();
+                    }catch(SQLException e) {}
                 }
             }
         }
             .append(exist ? "pcn" : "''").append(" as PKCOLUMN_NAME, ")
             .append(catalog).append(" as FKTABLE_CAT, ")
             .append(schema).append(" as FKTABLE_SCHEM, ")
-            .append(exist ? "fkn" : "''").append(" as FKTABLE_NAME, ")
+            .append(exist ? "fkt" : "''").append(" as FKTABLE_NAME, ")
             .append(exist ? "fcn" : "''").append(" as FKCOLUMN_NAME, ")
             .append(exist ? "ks" : "-1").append(" as KEY_SEQ, ")
             .append(exist ? "ur" : "3").append(" as UPDATE_RULE, ")
             .append(exist ? "dr" : "3").append(" as DELETE_RULE, ")
-            .append("'' as FK_NAME, ")
-            .append("'' as PK_NAME, ")
+            .append(exist ? "fkn" : "''").append(" as FK_NAME, ")
+            .append(pkFinder.getName() != null ? pkFinder.getName() : "''").append(" as PK_NAME, ")
             .append(Integer.toString(importedKeyInitiallyDeferred)) // FIXME: Check for pragma foreign_keys = true ?
             .append(" as DEFERRABILITY ")
             .append(exist ? "from (" : "limit 0");
        if (exist)
-           sql.append(exportedKeysQuery).append(')');
+           sql.append(exportedKeysQuery).append(") order by fkt");
 
         return stat.executeQuery(sql.toString()); //FIXME: close stat
     }
     /**
      * Pattern used to extract column order for an unnamed primary key.
      */
-    protected final static Pattern PK_UNNAMED =
+    protected final static Pattern PK_UNNAMED_PATTERN =
             Pattern.compile(".* primary +key *\\((.*?,+.*?)\\).*", Pattern.CASE_INSENSITIVE);
 
     /**
-    * Pattern used to extract a named primary key.
-    */
-    protected final static Pattern PK_NAMED =
-        Pattern.compile(".* constraint +(.*?) +primary +key *\\((.*?)\\).*", Pattern.CASE_INSENSITIVE);
+     * Pattern used to extract a named primary key.
+     */
+     protected final static Pattern PK_NAMED_PATTERN =
+         Pattern.compile(".* constraint +(.*?) +primary +key *\\((.*?)\\).*", Pattern.CASE_INSENSITIVE);
 
     /**
      * Parses the sqlite_master table for a table's primary key
                 if (!rs.next())
                     throw new SQLException("Table not found: '" + table + "'");
 
-                Matcher matcher = PK_NAMED.matcher(rs.getString(1));
+                Matcher matcher = PK_NAMED_PATTERN.matcher(rs.getString(1));
                 if (matcher.find()){
                     pkName = '\'' + escape(matcher.group(1).toLowerCase()) + '\'';
                     pkColumns = matcher.group(2).split(",");
                 }
                 else {
-                    matcher = PK_UNNAMED.matcher(rs.getString(1));
+                    matcher = PK_UNNAMED_PATTERN.matcher(rs.getString(1));
                     if (matcher.find()){
                         pkColumns = matcher.group(1).split(",");
                     }
 
                 if (pkColumns != null)
                     for (int i = 0; i < pkColumns.length; i++) {
-                        pkColumns[i] = pkColumns[i].toLowerCase();
+                        pkColumns[i] = pkColumns[i].toLowerCase().trim();
                     }
                 //FIXME: close stat
             }

File src/main/java/org/sqlite/Stmt.java

         db.prepare(this);
         if (!exec()) {
             internalClose();
-            throw new SQLException("query does not return ResultSet");
+            throw new SQLException("query does not return ResultSet", "SQLITE_DONE", SQLITE_DONE);
         }
         return getResultSet();
     }

File src/test/java/org/sqlite/DBMetaDataTest.java

         stat.executeUpdate("create table child1 (id1 integer, id2 integer, foreign key(id1) references parent(id1), foreign key(id2) references parent(id2))");
         stat.executeUpdate("create table child2 (id1 integer, id2 integer, foreign key(id2, id1) references parent(id2, id1))");
 
-        ResultSet importedKeys = meta.getExportedKeys(null, null, "parent");
+        ResultSet exportedKeys = meta.getExportedKeys(null, null, "parent");
 
         //1st fk (simple) - child1
-        assertTrue(importedKeys.next());
-        assertEquals("parent", importedKeys.getString("PKTABLE_NAME"));
-        assertEquals("id2", importedKeys.getString("PKCOLUMN_NAME"));
-        assertNotNull(importedKeys.getString("PK_NAME"));
-        assertNotNull(importedKeys.getString("FK_NAME"));
-        assertEquals("child1", importedKeys.getString("FKTABLE_NAME"));
-        assertEquals("id2", importedKeys.getString("FKCOLUMN_NAME"));
+        assertTrue(exportedKeys.next());
+        assertEquals("parent", exportedKeys.getString("PKTABLE_NAME"));
+        assertEquals("id2", exportedKeys.getString("PKCOLUMN_NAME"));
+        assertNotNull(exportedKeys.getString("PK_NAME"));
+        assertNotNull(exportedKeys.getString("FK_NAME"));
+        assertEquals("child1", exportedKeys.getString("FKTABLE_NAME"));
+        assertEquals("id2", exportedKeys.getString("FKCOLUMN_NAME"));
 
         //2nd fk (simple) - child1
-        assertTrue(importedKeys.next());
-        assertEquals("parent", importedKeys.getString("PKTABLE_NAME"));
-        assertEquals("id1", importedKeys.getString("PKCOLUMN_NAME"));
-        assertNotNull(importedKeys.getString("PK_NAME"));
-        assertNotNull(importedKeys.getString("FK_NAME"));
-        assertEquals("child1", importedKeys.getString("FKTABLE_NAME"));
-        assertEquals("id1", importedKeys.getString("FKCOLUMN_NAME"));
+        assertTrue(exportedKeys.next());
+        assertEquals("parent", exportedKeys.getString("PKTABLE_NAME"));
+        assertEquals("id1", exportedKeys.getString("PKCOLUMN_NAME"));
+        assertNotNull(exportedKeys.getString("PK_NAME"));
+        assertNotNull(exportedKeys.getString("FK_NAME"));
+        assertEquals("child1", exportedKeys.getString("FKTABLE_NAME"));
+        assertEquals("id1", exportedKeys.getString("FKCOLUMN_NAME"));
 
         //3rd fk (composite) - child2
-        assertTrue(importedKeys.next());
-        assertEquals("parent", importedKeys.getString("PKTABLE_NAME"));
-        assertEquals("id2", importedKeys.getString("PKCOLUMN_NAME"));
-        assertNotNull(importedKeys.getString("PK_NAME"));
-        assertNotNull(importedKeys.getString("FK_NAME"));
-        assertEquals("child2", importedKeys.getString("FKTABLE_NAME"));
-        assertEquals("id2", importedKeys.getString("FKCOLUMN_NAME"));
+        assertTrue(exportedKeys.next());
+        assertEquals("parent", exportedKeys.getString("PKTABLE_NAME"));
+        assertEquals("id2", exportedKeys.getString("PKCOLUMN_NAME"));
+        assertNotNull(exportedKeys.getString("PK_NAME"));
+        assertNotNull(exportedKeys.getString("FK_NAME"));
+        assertEquals("child2", exportedKeys.getString("FKTABLE_NAME"));
+        assertEquals("id2", exportedKeys.getString("FKCOLUMN_NAME"));
 
-        assertTrue(importedKeys.next());
-        assertEquals("parent", importedKeys.getString("PKTABLE_NAME"));
-        assertEquals("id1", importedKeys.getString("PKCOLUMN_NAME"));
-        assertNotNull(importedKeys.getString("PK_NAME"));
-        assertNotNull(importedKeys.getString("FK_NAME"));
-        assertEquals("child2", importedKeys.getString("FKTABLE_NAME"));
-        assertEquals("id1", importedKeys.getString("FKCOLUMN_NAME"));
+        assertTrue(exportedKeys.next());
+        assertEquals("parent", exportedKeys.getString("PKTABLE_NAME"));
+        assertEquals("id1", exportedKeys.getString("PKCOLUMN_NAME"));
+        assertNotNull(exportedKeys.getString("PK_NAME"));
+        assertNotNull(exportedKeys.getString("FK_NAME"));
+        assertEquals("child2", exportedKeys.getString("FKTABLE_NAME"));
+        assertEquals("id1", exportedKeys.getString("FKCOLUMN_NAME"));
 
-        assertFalse(importedKeys.next());
+        assertFalse(exportedKeys.next());
 
-        importedKeys.close();
+        exportedKeys.close();
     }
 
     @Test
 
         // With explicit primary column defined.
         stat.executeUpdate("create table REFERRED (ID integer primary key not null)");
-        stat.executeUpdate("create table REFERRING (ID integer, RID integer, foreign key (RID) references REFERRED(id))");
+        stat.executeUpdate("create table REFERRING (ID integer, RID integer, constraint fk foreign key (RID) references REFERRED(id))");
 
         exportedKeys = meta.getExportedKeys(null, null, "referred");
         assertEquals("referred", exportedKeys.getString("PKTABLE_NAME"));
         assertEquals("referring", exportedKeys.getString("FKTABLE_NAME"));
+        assertEquals("fk", exportedKeys.getString("FK_NAME"));
         exportedKeys.close();
     }