| Bug #517 | DatabaseMetaData.getImportedForeignKeys does not work | ||
|---|---|---|---|
| Submitted: | 27 May 2003 8:45 | Modified: | 18 Jun 2003 8:11 |
| Reporter: | Parwinder Sekhon | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | Connector / J | Severity: | S2 (Serious) |
| Version: | Connector/J 3.0.8, MySQL 4.0.13 | OS: | Windows (Windows) |
| Assigned to: | Mark Matthews | CPU Architecture: | Any |
[28 May 2003 3:09]
Parwinder Sekhon
Here is the diff for the fix, that shows FKs and gives the FK a name.
***************
*** 1185,1191 ****
fields[8] = new Field("", "KEY_SEQ", Types.SMALLINT, 2);
fields[9] = new Field("", "UPDATE_RULE", Types.SMALLINT, 2);
fields[10] = new Field("", "DELETE_RULE", Types.SMALLINT, 2);
! fields[11] = new Field("", "FK_NAME", Types.CHAR, 0);
fields[12] = new Field("", "PK_NAME", Types.CHAR, 0);
fields[13] = new Field("", "DEFERRABILITY", Types.INTEGER, 2);
--- 1185,1191 ----
fields[8] = new Field("", "KEY_SEQ", Types.SMALLINT, 2);
fields[9] = new Field("", "UPDATE_RULE", Types.SMALLINT, 2);
fields[10] = new Field("", "DELETE_RULE", Types.SMALLINT, 2);
! fields[11] = new Field("", "FK_NAME", Types.CHAR, 255);
fields[12] = new Field("", "PK_NAME", Types.CHAR, 0);
fields[13] = new Field("", "DEFERRABILITY", Types.INTEGER, 2);
***************
*** 1259,1264 ****
--- 1259,1267 ----
// multi-columned keys : (m n) REFER airline/vv(a b)
int firstLeftParenIndex = keys.indexOf('(');
int firstRightParenIndex = keys.indexOf(')');
+
+ String constraintName = keys.substring(0, firstLeftParenIndex);
+
String referencingColumns = keys.substring(firstLeftParenIndex
+ 1, firstRightParenIndex);
StringTokenizer referencingColumnsTokenizer = new StringTokenizer(referencingColumns,
***************
*** 1319,1325 ****
.getBytes();
tuple[10] = Integer.toString(actions[0])
.getBytes();
! tuple[11] = null; // FK_NAME
tuple[12] = null; // PK_NAME
tuple[13] = Integer.toString(java.sql.DatabaseMetaData.importedKeyNotDeferrable)
.getBytes();
--- 1322,1328 ----
.getBytes();
tuple[10] = Integer.toString(actions[0])
.getBytes();
! tuple[11] = s2b(constraintName); // FK_NAME
tuple[12] = null; // PK_NAME
tuple[13] = Integer.toString(java.sql.DatabaseMetaData.importedKeyNotDeferrable)
.getBytes();
***************
*** 1592,1598 ****
fields[8] = new Field("", "KEY_SEQ", Types.SMALLINT, 2);
fields[9] = new Field("", "UPDATE_RULE", Types.SMALLINT, 2);
fields[10] = new Field("", "DELETE_RULE", Types.SMALLINT, 2);
! fields[11] = new Field("", "FK_NAME", Types.CHAR, 0);
fields[12] = new Field("", "PK_NAME", Types.CHAR, 0);
fields[13] = new Field("", "DEFERRABILITY", Types.INTEGER, 2);
--- 1595,1601 ----
fields[8] = new Field("", "KEY_SEQ", Types.SMALLINT, 2);
fields[9] = new Field("", "UPDATE_RULE", Types.SMALLINT, 2);
fields[10] = new Field("", "DELETE_RULE", Types.SMALLINT, 2);
! fields[11] = new Field("", "FK_NAME", Types.CHAR, 255);
fields[12] = new Field("", "PK_NAME", Types.CHAR, 0);
fields[13] = new Field("", "DEFERRABILITY", Types.INTEGER, 2);
***************
*** 1657,1665 ****
while (commentTokens.hasMoreTokens()) {
String keys = commentTokens.nextToken();
! getExportKeyResults(catalog,
! tableNameWithCase, keys, tuples,
! fkresults.getString("Name"));
}
}
}
--- 1660,1666 ----
while (commentTokens.hasMoreTokens()) {
String keys = commentTokens.nextToken();
! getExportKeyResults(catalog, tableNameWithCase, keys, tuples, fkresults.getString("Name"));
}
}
}
***************
*** 1869,1875 ****
fields[8] = new Field("", "KEY_SEQ", Types.SMALLINT, 2);
fields[9] = new Field("", "UPDATE_RULE", Types.SMALLINT, 2);
fields[10] = new Field("", "DELETE_RULE", Types.SMALLINT, 2);
! fields[11] = new Field("", "FK_NAME", Types.CHAR, 0);
fields[12] = new Field("", "PK_NAME", Types.CHAR, 0);
fields[13] = new Field("", "DEFERRABILITY", Types.INTEGER, 2);
--- 1870,1876 ----
fields[8] = new Field("", "KEY_SEQ", Types.SMALLINT, 2);
fields[9] = new Field("", "UPDATE_RULE", Types.SMALLINT, 2);
fields[10] = new Field("", "DELETE_RULE", Types.SMALLINT, 2);
! fields[11] = new Field("", "FK_NAME", Types.CHAR, 255);
fields[12] = new Field("", "PK_NAME", Types.CHAR, 0);
fields[13] = new Field("", "DEFERRABILITY", Types.INTEGER, 2);
***************
*** 1931,1938 ****
while (commentTokens.hasMoreTokens()) {
String keys = commentTokens.nextToken();
! getImportKeyResults(catalog, table, keys,
! tuples);
}
}
}
--- 1932,1938 ----
while (commentTokens.hasMoreTokens()) {
String keys = commentTokens.nextToken();
! getImportKeyResults(catalog, table, keys, tuples);
}
}
}
***************
*** 4921,4927 ****
* @return the list of rows with new rows added
*/
public List extractForeignKeyForTable(ArrayList rows,
! java.sql.ResultSet rs, String catalog) throws SQLException {
byte[][] row = new byte[3][];
row[0] = rs.getBytes(1);
row[1] = s2b(SUPPORTS_FK);
--- 4921,4928 ----
* @return the list of rows with new rows added
*/
public List extractForeignKeyForTable(ArrayList rows,
! java.sql.ResultSet rs,
! String catalog) throws SQLException {
byte[][] row = new byte[3][];
row[0] = rs.getBytes(1);
row[1] = s2b(SUPPORTS_FK);
***************
*** 4935,4940 ****
--- 4936,4947 ----
while (lineTokenizer.hasMoreTokens()) {
String line = lineTokenizer.nextToken().trim();
+ String constraintName = null;
+ String fkprefix = "CONSTRAINT ` ` ";
+ if (line.length() > fkprefix.length()) {
+ constraintName = line.substring(0, fkprefix.length());
+ line = line.substring(fkprefix.length(), line.length());
+ }
if (line.startsWith("FOREIGN KEY")) {
if (line.endsWith(",")) {
line = line.substring(0, line.length() - 1);
***************
*** 4960,4965 ****
--- 4967,4973 ----
String localColumnNamesString = keyTokens.nextToken();
String referCatalogTableString = keyTokens.nextToken();
+
StringTokenizer referSchemaTable = new StringTokenizer(referCatalogTableString
.trim(), " .");
String referColumnNamesString = keyTokens.nextToken();
***************
*** 4985,4990 ****
--- 4993,4999 ----
firstTime = false;
}
+ commentBuf.append(constraintName);
commentBuf.append("(");
commentBuf.append(localColumnNamesString);
commentBuf.append(") REFER ");
***************
*** 4998,5005 ****
int lastParenIndex = line.lastIndexOf(")");
if (lastParenIndex != (line.length() - 1)) {
! String cascadeOptions = cascadeOptions = line.substring(lastParenIndex
! + 1);
commentBuf.append(" ");
commentBuf.append(cascadeOptions);
}
--- 5007,5013 ----
int lastParenIndex = line.lastIndexOf(")");
if (lastParenIndex != (line.length() - 1)) {
! String cascadeOptions = cascadeOptions = line.substring(lastParenIndex + 1);
commentBuf.append(" ");
commentBuf.append(cascadeOptions);
}
***************
*** 6282,6292 ****
*
* @throws SQLException if a database access error occurs
*/
! private void getExportKeyResults(String catalog, String exportingTable,
! String keysComment, List tuples, String fkTableName)
throws SQLException {
! getResultsImpl(catalog, exportingTable, keysComment, tuples,
! fkTableName, true);
}
/**
--- 6290,6302 ----
*
* @throws SQLException if a database access error occurs
*/
! private void getExportKeyResults(String catalog,
! String exportingTable,
! String keysComment,
! List tuples,
! String fkTableName)
throws SQLException {
! getResultsImpl(catalog, exportingTable, keysComment, tuples, fkTableName, true);
}
/**
***************
*** 6329,6341 ****
*
* @throws SQLException if a database access error occurs
*/
! private void getImportKeyResults(String catalog, String importingTable,
! String keysComment, List tuples) throws SQLException {
getResultsImpl(catalog, importingTable, keysComment, tuples, null, false);
}
! private void getResultsImpl(String catalog, String table,
! String keysComment, List tuples, String fkTableName, boolean isExport)
throws SQLException {
// keys will equal something like this:
// (parent_service_id child_service_id) REFER ds/subservices(parent_service_id child_service_id)
--- 6339,6357 ----
*
* @throws SQLException if a database access error occurs
*/
! private void getImportKeyResults(String catalog,
! String importingTable,
! String keysComment,
! List tuples) throws SQLException {
getResultsImpl(catalog, importingTable, keysComment, tuples, null, false);
}
! private void getResultsImpl(String catalog,
! String table,
! String keysComment,
! List tuples,
! String fkTableName,
! boolean isExport)
throws SQLException {
// keys will equal something like this:
// (parent_service_id child_service_id) REFER ds/subservices(parent_service_id child_service_id)
***************
*** 6343,6348 ****
--- 6359,6368 ----
// 1: parse the opening parentheses to determine how many results there will be
// 2: read in the schema name/table name
// 3: parse the closing parentheses
+ int firstLeftParenIndex = keysComment.indexOf('(');
+ String constraintName = keysComment.substring(0, firstLeftParenIndex);
+ keysComment = keysComment.substring(firstLeftParenIndex, keysComment.length());
+
StringTokenizer keyTokens = new StringTokenizer(keysComment.trim(),
"()", false);
String localColumnNamesString = keyTokens.nextToken();
***************
*** 6388,6394 ****
tuple[UPDATE_RULE] = s2b(Integer.toString(actions[1]));
tuple[DELETE_RULE] = s2b(Integer.toString(actions[0]));
! tuple[FK_NAME] = null; //not available from show table status
tuple[PK_NAME] = null; //not available from show table status
tuple[DEFERRABILITY] = s2b(Integer.toString(
java.sql.DatabaseMetaData.importedKeyNotDeferrable));
--- 6408,6414 ----
tuple[UPDATE_RULE] = s2b(Integer.toString(actions[1]));
tuple[DELETE_RULE] = s2b(Integer.toString(actions[0]));
! tuple[FK_NAME] = s2b(constraintName);
tuple[PK_NAME] = null; //not available from show table status
tuple[DEFERRABILITY] = s2b(Integer.toString(
java.sql.DatabaseMetaData.importedKeyNotDeferrable));
[4 Jun 2003 2:15]
Lenz Grimmer
Mark, please take a look at this. Thanks!
[4 Jun 2003 15:44]
Mark Matthews
Parwinder, can you send your patch directly to me, mark@mysql.com? The bug system has formatted it, so 'patch' doesn't like it :( Thanks!
[18 Jun 2003 8:11]
Mark Matthews
This is now fixed in the 3.0 and 3.1 tree. A combination of your patch, along with some extra code to allow older MySQL servers without constraint names in 'SHOW CREATE TABLE' fixed it. You can check out the fix in either 3.1 or 3.0 in this evening's snapshot builds after 00:00 GMT 19-Jun-03 at http://mmmysql.sourceforge.net/snapshots/ Thanks for your bug report and patch!
Description: DatabaseMetaData.extractForeignKeyForTable checks if the comment line begins with "FOREIGN KEY", however the line actually begins with something like "CONSTRAINT `0_181` FOREIGN KEY", so DatabaseMetaData.getImportedForeignKeys returns no foreign keys for tables. This probably also applies to DatabaseMetaData.getExportedForeignKeys. This also bring me to another point. If the current code for DatabaseMetaData.getImportedForeignKeys worked, it would return a null values for the foreign key name, i.e the following assert would fail: ResultSet rs = connection.getMetaData().getImportedKeys(catalogName, schemaName, tableName); ... String fkName = rs.getString(12); assert (fkName != null) : "Foreign Key name is null"; However it seems to me if the comments for the table schema returns lines such as "CONSTRAINT `0_181` FOREIGN KEY", then it is atleast possible to assign some meaningful value to foreign key name, in this case "0_181". Why is this important? Because it allows users to easily group together foreign keys that are made up of more than column. Regards Parwy How to repeat: execute: ResultSet rs = connection.getMetaData().getImportedKeys(catalogName, schemaName, tableName); ... while (rs.next()) { System.out.println("Got a fk"); } You should never see any fks output. Suggested fix: update DatabaseMetaData.extractForeignKeyForTable so that it can parse lines begining with text such as "CONSTRAINT `0_181` FOREIGN KEY" and use the constraint name/id as the foreign key name.