Index: ChangeLog =================================================================== --- ChangeLog (revision 960) +++ ChangeLog (working copy) @@ -1,8 +1,12 @@ 5.1.2 Functionality added or changed: + * SQLForeignKeys uses INFORMATION_SCHEMA when it is available on the server, + which allows more complete information to be returned. Bugs fixed: + * SQLForeignKeys returned an empty string for the schema columns instead of + a NULL. (Bug #19923) * Adding or updating a row using SQLSetPos() on a result set with aliased columns would fail. (Bug #6157) Index: driver/catalog.c =================================================================== --- driver/catalog.c (revision 959) +++ driver/catalog.c (working copy) @@ -1715,9 +1715,131 @@ CLEAR_STMT_ERROR(hstmt); my_SQLFreeStmt(hstmt,MYSQL_RESET); - if ( is_minimum_version(stmt->dbc->mysql.server_version,"3.23",4) ) + /* For 5.0 and later, use INFORMATION_SCHEMA. */ + if (is_minimum_version(stmt->dbc->mysql.server_version, "5.0", 3)) { - STMT FAR *stmt=(STMT FAR*) hstmt; + MYSQL *mysql= &stmt->dbc->mysql; + char query[2048], *buff; /* This should be big enough. */ + char *update_rule, *delete_rule, *ref_constraints_join; + SQLRETURN rc; + + /* + With 5.1, we can use REFERENTIAL_CONSTRAINTS to get even more info. + */ + if (is_minimum_version(stmt->dbc->mysql.server_version, "5.1", 3)) + { + update_rule= "CASE" + " WHEN R.UPDATE_RULE = 'CASCADE' THEN 0" + " WHEN R.UPDATE_RULE = 'SET NULL' THEN 2" + " WHEN R.UPDATE_RULE = 'SET DEFAULT' THEN 4" + " WHEN R.UPDATE_RULE = 'SET RESTRICT' THEN 1" + " WHEN R.UPDATE_RULE = 'SET NO ACTION' THEN 3" + " ELSE " + " END"; + delete_rule= "CASE" + " WHEN R.DELETE_RULE = 'CASCADE' THEN 0" + " WHEN R.DELETE_RULE = 'SET NULL' THEN 2" + " WHEN R.DELETE_RULE = 'SET DEFAULT' THEN 4" + " WHEN R.DELETE_RULE = 'SET RESTRICT' THEN 1" + " WHEN R.DELETE_RULE = 'SET NO ACTION' THEN 3" + " ELSE " + " END"; + + ref_constraints_join= + " JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS R" + " ON (R.CONSTRAINT_NAME = B.CONSTRAINT_NAME" + " AND R.TABLE_NAME = B.TABLE_NAME" + " AND R.CONSTRAINT_SCHEMA = B.TABLE_SCHEMA)"; + } + else + { + /* Just return '1' to be compatible with pre-I_S version. */ + update_rule= delete_rule= "1"; + ref_constraints_join= ""; + } + + /* This is a big, ugly query. But it works! */ + buff= strxmov(query, + "SELECT A.REFERENCED_TABLE_SCHEMA AS PKTABLE_CAT," + "NULL AS PKTABLE_SCHEM," + "A.REFERENCED_TABLE_NAME AS PKTABLE_NAME," + "A.REFERENCED_COLUMN_NAME AS PKCOLUMN_NAME," + "A.TABLE_SCHEMA AS FKTABLE_CAT, NULL AS FKTABLE_SCHEM," + "A.TABLE_NAME AS FKTABLE_NAME," + "A.COLUMN_NAME AS FKCOLUMN_NAME," + "A.ORDINAL_POSITION AS KEY_SEQ,", + update_rule, " AS UPDATE_RULE,", + delete_rule, " AS DELETE_RULE," + "A.CONSTRAINT_NAME AS FK_NAME," + "(SELECT CONSTRAINT_NAME FROM" + " INFORMATION_SCHEMA.TABLE_CONSTRAINTS" + " WHERE TABLE_SCHEMA = REFERENCED_TABLE_SCHEMA AND" + " TABLE_NAME = REFERENCED_TABLE_NAME AND" + " CONSTRAINT_TYPE IN ('UNIQUE','PRIMARY KEY') LIMIT 1)" + " AS PK_NAME," + "7 AS DEFERRABILITY" + " FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE A" + " JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS B" + " USING (CONSTRAINT_NAME,TABLE_NAME)", + ref_constraints_join, + " WHERE B.CONSTRAINT_TYPE = 'FOREIGN KEY' ", + NullS); + + if (szPkTableName && szPkTableName[0]) + { + buff= strmov(buff, "AND A.REFERENCED_TABLE_SCHEMA = "); + if (szPkCatalogName && szPkCatalogName[0]) + { + if (cbPkCatalogName == SQL_NTS) + cbPkCatalogName= strlen((char *)szPkCatalogName); + buff= strmov(buff, "'"); + buff+= mysql_real_escape_string(mysql, buff, (char *)szPkCatalogName, + cbPkCatalogName); + buff= strmov(buff, "' "); + } + else + buff= strmov(buff, "DATABASE() "); + buff= strmov(buff, "AND A.REFERENCED_TABLE_NAME = '"); + if (cbPkTableName == SQL_NTS) + cbPkTableName= strlen((char *)szPkTableName); + buff+= mysql_real_escape_string(mysql, buff, (char *)szPkTableName, + cbPkTableName); + buff= strmov(buff, "' "); + } + + if (szFkTableName && szFkTableName[0]) + { + buff= strmov(buff, "AND A.TABLE_SCHEMA = "); + if (szFkCatalogName && szFkCatalogName[0]) + { + if (cbFkCatalogName == SQL_NTS) + cbFkCatalogName= strlen((char *)szFkCatalogName); + buff= strmov(buff, "'"); + buff+= mysql_real_escape_string(mysql, buff, (char *)szFkCatalogName, + cbFkCatalogName); + buff= strmov(buff, "' "); + } + else + buff= strmov(buff, "DATABASE() "); + buff= strmov(buff, "AND A.TABLE_NAME = '"); + if (cbFkTableName == SQL_NTS) + cbFkTableName= strlen((char *)szFkTableName); + buff+= mysql_real_escape_string(mysql, buff, (char *)szFkTableName, + cbFkTableName); + buff= strmov(buff, "' "); + } + + strmov(buff, "ORDER BY FKTABLE_CAT, FKTABLE_NAME, KEY_SEQ"); + + rc= MySQLPrepare(hstmt, (SQLCHAR *)query, SQL_NTS, FALSE); + if (!SQL_SUCCEEDED(rc)) + return rc; + + return my_SQLExecute(hstmt); + } + /* For 3.23 and later, use comment in SHOW TABLE STATUS (yuck). */ + else if (is_minimum_version(stmt->dbc->mysql.server_version,"3.23",4)) + { MEM_ROOT *alloc; MYSQL_ROW row; char **data; @@ -1760,8 +1882,8 @@ /* Convert mysql fields to data that odbc wants */ alloc= &stmt->result->field_alloc; - data= tempdata; - comment_id= stmt->result->field_count-1; + data= tempdata; + comment_id= stmt->result->field_count - 1; while ( (row= mysql_fetch_row(stmt->result)) ) { @@ -1775,11 +1897,12 @@ if ( !(comment_token= strchr(row[comment_id],';')) ) continue; /* MySQL 4.1 and above, the comment field is '15' */ - do + do { - /* - Found reference information in comment field from InnoDB type, - and parse the same to get the FK information .. + /* + Found reference information in comment field from + InnoDB type, and parse the same to get the FK + information .. */ key_seq= 1; @@ -1793,7 +1916,7 @@ if ( !(token= my_next_token(token+8,&comment_token,ref_token,'/')) ) continue; - + data[0]= strdup_root(alloc,ref_token); /* PKTABLE_CAT */ if (!(token= my_next_token(token, &comment_token, @@ -1811,7 +1934,7 @@ continue; pk_length= (uint)((token-2)-pk_cols_start); - data[1]= ""; /* PKTABLE_SCHEM */ + data[1]= NULL; /* PKTABLE_SCHEM */ /** @todo clean this up when current database tracking is @@ -1824,26 +1947,21 @@ data[4]= (szFkCatalogName ? strdup_root(alloc, (char *)szFkCatalogName) : strdup_root(alloc, stmt->dbc->database)); - data[5]= ""; /* FKTABLE_SCHEM */ + data[5]= NULL; /* FKTABLE_SCHEM */ data[6]= row[0]; /* FKTABLE_TABLE */ - /* - TODO : FIX both UPDATE_RULE and DELETE_RULE after - 3.23.52 is released, which supports this feature in - server by updating the 'comment' field as well as - from SHOW CREATE TABLE defination.. - - right now return only SQL_CASCADE as the DELETE/UPDATE - rule - */ - - data[9]= "1"; /*SQL_CASCADE*/ /* UPDATE_RULE */ - data[10]= "1"; /*SQL_CASCADE*/ /* DELETE_RULE */ + /* + We could figure out UPDATE_RULE and DELETE_RULE by + parsing the comment field. For now, we just return + SQL_CASCADE> + */ + data[9]= "1"; /*SQL_CASCADE*/ /* UPDATE_RULE */ + data[10]= "1"; /*SQL_CASCADE*/ /* DELETE_RULE */ data[11]= "NULL"; /* FK_NAME */ data[12]= "NULL"; /* PK_NAME */ data[13]= "7"; /*SQL_NOT_DEFERRABLE*/ /* DEFERRABILITY */ - token = fkcomment = (char *)fk_cols_start; + token = fkcomment = (char *)fk_cols_start; pktoken = pkcomment = (char *)pk_cols_start; fkcomment[fk_length]= '\0'; pkcomment[pk_length]= '\0'; @@ -1861,8 +1979,8 @@ row_count++; for ( fk_length= SQLFORE_KEYS_FIELDS; fk_length--; ) data[fk_length]= prev_data[fk_length]; - } - data[7]= strdup_root(alloc,fkcomment); /* FKTABLE_COLUMN */ + } + data[7]= strdup_root(alloc,fkcomment); /* FKTABLE_COLUMN */ data[3]= strdup_root(alloc,pkcomment); /* PKTABLE_COLUMN */ sprintf(ref_token,"%d",key_seq); data[8]= strdup_root(alloc,ref_token); /* KEY_SEQ */ @@ -1887,10 +2005,11 @@ return handle_connection_error(stmt); } } - else /* NO FOREIGN KEY support from SERVER */ + /* Versions older than 3.23 don't support foreign keys at all. */ + else { goto empty_set; - } + } stmt->result->row_count= row_count; mysql_link_fields(stmt,SQLFORE_KEYS_fields,SQLFORE_KEYS_FIELDS); return SQL_SUCCESS; Index: test/my_catalog.c =================================================================== --- test/my_catalog.c (revision 959) +++ test/my_catalog.c (working copy) @@ -764,12 +764,13 @@ Bug #4518: SQLForeignKeys returns too many foreign key Bug #27723: SQLForeignKeys does not escape _ and % in the table name arguments - The original test case was extended to have a table that would inadvertantly + The original test case was extended to have a table that would inadvertently get included because of the poor escaping. */ DECLARE_TEST(t_bug4518) { SQLCHAR buff[255]; + SQLLEN len; /** @todo re-enable this test when I_S based SQLForeignKeys is done. */ if (mysql_min_version(hdbc, "5.1", 3)) @@ -806,6 +807,12 @@ is_str(my_fetch_str(hstmt, buff, 7), "t_bug4518_c", 11); is_str(my_fetch_str(hstmt, buff, 8), "parent_id", 9); + /* For Bug #19923: Test that schema columns are NULL. */ + ok_stmt(hstmt, SQLGetData(hstmt, 2, SQL_C_CHAR, buff, sizeof(buff), &len)); + is_num(len, SQL_NULL_DATA); + ok_stmt(hstmt, SQLGetData(hstmt, 6, SQL_C_CHAR, buff, sizeof(buff), &len)); + is_num(len, SQL_NULL_DATA); + expect_stmt(hstmt, SQLFetch(hstmt), SQL_NO_DATA_FOUND); ok_stmt(hstmt, SQLFreeStmt(hstmt,SQL_CLOSE)); @@ -1118,6 +1125,50 @@ } +/** + Bug #19923: MyODBC Foreign key retrieval broken in multiple ways + Two issues to test: schema columns should be NULL, not just an empty string, + and more than one constraint should be returned. +*/ +DECLARE_TEST(t_bug19923) +{ + SQLCHAR buff[255]; + SQLLEN len; + + ok_sql(hstmt, "DROP TABLE IF EXISTS t_bug19923c, t_bug19923b, t_bug19923a"); + ok_sql(hstmt, "CREATE TABLE t_bug19923a (a INT PRIMARY KEY) ENGINE=InnoDB"); + ok_sql(hstmt, "CREATE TABLE t_bug19923b (b INT PRIMARY KEY) ENGINE=InnoDB"); + ok_sql(hstmt, "CREATE TABLE t_bug19923c (a INT, b INT, UNIQUE(a), UNIQUE(b), CONSTRAINT `first_constraint` FOREIGN KEY (`b`) REFERENCES `t_bug19923b` (`b`), CONSTRAINT `second_constraint` FOREIGN KEY (`a`) REFERENCES `t_bug19923a` (`a`)) ENGINE=InnoDB"); + + + ok_stmt(hstmt, SQLForeignKeys(hstmt, NULL, 0, NULL, 0, NULL, 0, NULL, 0, + NULL, 0, (SQLCHAR *)"t_bug19923c", SQL_NTS)); + + ok_stmt(hstmt, SQLFetch(hstmt)); + is_str(my_fetch_str(hstmt, buff, 3), "t_bug19923b", 10); + is_str(my_fetch_str(hstmt, buff, 4), "b", 1); + is_str(my_fetch_str(hstmt, buff, 7), "t_bug19923c", 10); + is_str(my_fetch_str(hstmt, buff, 8), "b", 1); + is_str(my_fetch_str(hstmt, buff, 12), "first_constraint", 16); + ok_stmt(hstmt, SQLGetData(hstmt, 2, SQL_C_CHAR, buff, sizeof(buff), &len)); + is_num(len, SQL_NULL_DATA); + ok_stmt(hstmt, SQLGetData(hstmt, 6, SQL_C_CHAR, buff, sizeof(buff), &len)); + is_num(len, SQL_NULL_DATA); + + ok_stmt(hstmt, SQLFetch(hstmt)); + is_str(my_fetch_str(hstmt, buff, 3), "t_bug19923a", 10); + is_str(my_fetch_str(hstmt, buff, 4), "a", 1); + is_str(my_fetch_str(hstmt, buff, 7), "t_bug19923c", 10); + is_str(my_fetch_str(hstmt, buff, 8), "a", 1); + is_str(my_fetch_str(hstmt, buff, 12), "second_constraint", 17); + + expect_stmt(hstmt, SQLFetch(hstmt), SQL_NO_DATA_FOUND); + + ok_sql(hstmt, "DROP TABLE IF EXISTS t_bug19923c, t_bug19923b, t_bug19923a"); + return OK; +} + + BEGIN_TESTS ADD_TEST(my_columns_null) ADD_TEST(my_drop_table) @@ -1140,6 +1191,7 @@ ADD_TEST(t_bug26934) ADD_TEST(t_bug29888) ADD_TEST(t_bug14407) + ADD_TEST(t_bug19923) END_TESTS Index: test/my_keys.c =================================================================== --- test/my_keys.c (revision 959) +++ test/my_keys.c (working copy) @@ -222,7 +222,7 @@ NULL, SQL_NTS, /*FK SCHEMA*/ NULL, SQL_NTS); /*FK TABLE*/ mystmt(hstmt,rc); -/* myassert(9 == myresult(hstmt)); */ + myassert(9 == myresult(hstmt)); printMessage("\n WITH ONLY FK OPTION"); rc = SQLForeignKeys(hstmt, @@ -244,7 +244,7 @@ NULL, SQL_NTS, "test_fkey_c1", SQL_NTS); mystmt(hstmt,rc); - /* myassert(15 == myresult(hstmt)); */ + myassert(15 == myresult(hstmt)); printMessage("\n WITH ONLY FK OPTION"); rc = SQLForeignKeys(hstmt, @@ -255,7 +255,7 @@ NULL, SQL_NTS, "test_fkey2", SQL_NTS); mystmt(hstmt,rc); - /* myassert(3 == myresult(hstmt)); */ + myassert(3 == myresult(hstmt)); printMessage("\n WITH ONLY PK OPTION"); rc = SQLForeignKeys(hstmt, @@ -266,7 +266,7 @@ NULL, SQL_NTS, NULL, SQL_NTS); mystmt(hstmt,rc); - /* myassert(11 == myresult(hstmt)); */ + myassert(11 == myresult(hstmt)); printMessage("\n WITH ONLY PK OPTION"); rc = SQLForeignKeys(hstmt, @@ -288,7 +288,7 @@ NULL, SQL_NTS, NULL, SQL_NTS); mystmt(hstmt,rc); - /* myassert(2 == myresult(hstmt)); */ + myassert(2 == myresult(hstmt)); printMessage("\n WITH ONLY PK OPTION"); rc = SQLForeignKeys(hstmt, @@ -299,7 +299,7 @@ NULL, SQL_NTS, NULL, SQL_NTS); mystmt(hstmt,rc); - /* myassert(9 == myresult(hstmt)); */ + myassert(9 == myresult(hstmt)); printMessage("\n WITH ONLY FK OPTION"); rc = SQLForeignKeys(hstmt, @@ -310,7 +310,7 @@ NULL, SQL_NTS, "test_fkey3", SQL_NTS); mystmt(hstmt,rc); - /* myassert(4 == myresult(hstmt)); */ + myassert(4 == myresult(hstmt)); printMessage("\n WITH BOTH PK and FK OPTION"); rc = SQLForeignKeys(hstmt, @@ -321,7 +321,7 @@ NULL, SQL_NTS, "test_fkey3", SQL_NTS); mystmt(hstmt,rc); - /* myassert(3 == myresult(hstmt)); */ + myassert(3 == myresult(hstmt)); printMessage("\n WITH BOTH PK and FK OPTION"); rc = SQLForeignKeys(hstmt, @@ -332,7 +332,7 @@ NULL, SQL_NTS, "test_fkey_c1", SQL_NTS); mystmt(hstmt,rc); - /* myassert(11 == myresult(hstmt)); */ + myassert(11 == myresult(hstmt)); printMessage("\n WITH BOTH PK and FK OPTION"); rc = SQLForeignKeys(hstmt, @@ -343,7 +343,7 @@ NULL, SQL_NTS, "test_fkey2", SQL_NTS); mystmt(hstmt,rc); - /* myassert(3 == myresult(hstmt)); */ + myassert(3 == myresult(hstmt)); printMessage("\n WITH BOTH PK and FK OPTION"); rc = SQLForeignKeys(hstmt, @@ -388,7 +388,7 @@ NULL, SQL_NTS, "test_fkey2",10); mystmt(hstmt,rc); - /* assert(3 == myresult(hstmt)); */ + myassert(3 == myresult(hstmt)); SQLFreeStmt(hstmt,SQL_CLOSE); printMessage("\n WITH NON-EXISTANT TABLES"); @@ -400,7 +400,7 @@ NULL, SQL_NTS, "test_fkey_junk", SQL_NTS); mystmt(hstmt,rc); - myassert(0 == myresult(hstmt)); + myassert(0 == myresult(hstmt)); SQLFreeStmt(hstmt,SQL_CLOSE); printMessage("\n WITH COMMENT FIELD"); @@ -412,7 +412,7 @@ NULL, SQL_NTS, "test_fkey_comment_f", SQL_NTS); mystmt(hstmt,rc); - /* myassert(1 == myresult(hstmt)); */ + myassert(1 == myresult(hstmt)); { char buff[255];