Changeset: fbed03097738 for monetdb-java URL: https://dev.monetdb.org/hg/monetdb-java/rev/fbed03097738 Modified Files: ChangeLog src/main/java/org/monetdb/jdbc/MonetDatabaseMetaData.java tests/JDBC_API_Tester.java Branch: default Log Message:
Corrected DatabaseMetaData methods getPrimaryKeys(), getBestRowIdentifier() and getIndexInfo() for temporary tables in schema tmp. They did not return any rows when the tmp table had a primary or unique key or index. Now they do return rows as expected. diffs (254 lines): diff --git a/ChangeLog b/ChangeLog --- a/ChangeLog +++ b/ChangeLog @@ -1,6 +1,12 @@ # ChangeLog file for monetdb-java # This file is updated with Maddlog +* Wed Mar 30 2022 Martin van Dinther <[email protected]> +- Corrected DatabaseMetaData methods getPrimaryKeys(), getBestRowIdentifier() + and getIndexInfo() for temporary tables in schema tmp. They did not + return any rows when the tmp table had a primary or unique key or index. + Now they do return rows as expected. + * Thu Feb 10 2022 Martin van Dinther <[email protected]> - Added recognition of 'xml' type. Use default mapping to Types.VARCHAR for easy and fast (as java.lang.String) retrieval, display and setting data of diff --git a/src/main/java/org/monetdb/jdbc/MonetDatabaseMetaData.java b/src/main/java/org/monetdb/jdbc/MonetDatabaseMetaData.java --- a/src/main/java/org/monetdb/jdbc/MonetDatabaseMetaData.java +++ b/src/main/java/org/monetdb/jdbc/MonetDatabaseMetaData.java @@ -2395,7 +2395,6 @@ public class MonetDatabaseMetaData * @param catalog a catalog name; "" retrieves those without a catalog * @param schema a schema name; "" retrieves those without a schema * @param table a table name - * Note that our implementation allows this param to be null also (for efficiency as no extra LIKE '%' condition is added to be evaluated). * @param scope the scope of interest; use same values as SCOPE * @param nullable include columns that are nullable? * @return ResultSet each row is a column description @@ -2410,7 +2409,9 @@ public class MonetDatabaseMetaData final boolean nullable ) throws SQLException { - // first find out if the table has a Primary Key, If it does, we should return only those columns + final String sysORtmp = (schema != null && "tmp".equals(schema)) ? "\"tmp\"" : "\"sys\""; + + // first find out if the table has a Primary Key. If it does, we should return only those columns boolean hasPK = false; ResultSet pkey = null; try { @@ -2442,10 +2443,10 @@ public class MonetDatabaseMetaData "cast(0 as int) AS \"BUFFER_LENGTH\", " + "cast(c.\"type_scale\" AS smallint) AS \"DECIMAL_DIGITS\", " + "cast(").append(DatabaseMetaData.bestRowNotPseudo).append(" AS smallint) AS \"PSEUDO_COLUMN\" " + - "FROM \"sys\".\"keys\" k " + - "JOIN \"sys\".\"objects\" o ON k.\"id\" = o.\"id\" " + - "JOIN \"sys\".\"columns\" c ON (k.\"table_id\" = c.\"table_id\" AND o.\"name\" = c.\"name\") " + - "JOIN \"sys\".\"tables\" t ON k.\"table_id\" = t.\"id\" " + + "FROM ").append(sysORtmp).append(".\"keys\" k " + + "JOIN ").append(sysORtmp).append(".\"objects\" o ON k.\"id\" = o.\"id\" " + + "JOIN ").append(sysORtmp).append(".\"_columns\" c ON (k.\"table_id\" = c.\"table_id\" AND o.\"name\" = c.\"name\") " + + "JOIN ").append(sysORtmp).append(".\"_tables\" t ON k.\"table_id\" = t.\"id\" " + "JOIN \"sys\".\"schemas\" s ON t.\"schema_id\" = s.\"id\" " + "WHERE k.\"type\" = ").append(hasPK ? "0" : "1"); // the primary key (type = 0) or else any unique key (type = 1) @@ -2457,11 +2458,13 @@ public class MonetDatabaseMetaData if (scope == DatabaseMetaData.bestRowSession || scope == DatabaseMetaData.bestRowTransaction || scope == DatabaseMetaData.bestRowTemporary) { - if (schema != null && !schema.equals("%")) { - query.append(" AND s.\"name\" ").append(composeMatchPart(schema)); + if (schema != null) { + // do not allow wildcard matching with LIKE, as the resultset does not include the schema info + query.append(" AND s.\"name\" = ").append(MonetWrapper.sq(schema)); } - if (table != null && !table.equals("%")) { - query.append(" AND t.\"name\" ").append(composeMatchPart(table)); + if (table != null) { + // do not allow wildcard matching with LIKE, as the resultset does not include the table info + query.append(" AND t.\"name\" = ").append(MonetWrapper.sq(table)); } if (!nullable) { query.append(" AND c.\"null\" = false"); @@ -2560,6 +2563,7 @@ public class MonetDatabaseMetaData final String table ) throws SQLException { + final String sysORtmp = (schema != null && "tmp".equals(schema)) ? "\"tmp\"" : "\"sys\""; final StringBuilder query = new StringBuilder(600); query.append("SELECT cast(null AS char(1)) AS \"TABLE_CAT\", " + "s.\"name\" AS \"TABLE_SCHEM\", " + @@ -2567,9 +2571,9 @@ public class MonetDatabaseMetaData "o.\"name\" AS \"COLUMN_NAME\", " + "cast(1 + o.\"nr\" AS smallint) AS \"KEY_SEQ\", " + " k.\"name\" AS \"PK_NAME\" " + - "FROM \"sys\".\"keys\" k " + - "JOIN \"sys\".\"objects\" o ON k.\"id\" = o.\"id\" " + - "JOIN \"sys\".\"tables\" t ON k.\"table_id\" = t.\"id\" " + + "FROM ").append(sysORtmp).append(".\"keys\" k " + + "JOIN ").append(sysORtmp).append(".\"objects\" o ON k.\"id\" = o.\"id\" " + + "JOIN ").append(sysORtmp).append(".\"_tables\" t ON k.\"table_id\" = t.\"id\" " + "JOIN \"sys\".\"schemas\" s ON t.\"schema_id\" = s.\"id\" " + "WHERE k.\"type\" = 0"); // only primary keys (type = 0) @@ -3077,6 +3081,7 @@ public class MonetDatabaseMetaData } } + final String sysORtmp = (schema != null && "tmp".equals(schema)) ? "\"tmp\"" : "\"sys\""; final StringBuilder query = new StringBuilder(1250); query.append( "SELECT cast(null AS char(1)) AS \"TABLE_CAT\", " + @@ -3092,12 +3097,12 @@ public class MonetDatabaseMetaData "cast(").append(table_row_count).append(" AS int) AS \"CARDINALITY\", " + "cast(0 AS int) AS \"PAGES\", " + "cast(null AS char(1)) AS \"FILTER_CONDITION\" " + - "FROM \"sys\".\"idxs\" i " + - "JOIN \"sys\".\"tables\" t ON i.\"table_id\" = t.\"id\" " + + "FROM ").append(sysORtmp).append(".\"idxs\" i " + + "JOIN ").append(sysORtmp).append(".\"_tables\" t ON i.\"table_id\" = t.\"id\" " + "JOIN \"sys\".\"schemas\" s ON t.\"schema_id\" = s.\"id\" " + - "JOIN \"sys\".\"objects\" o ON i.\"id\" = o.\"id\" " + - "JOIN \"sys\".\"columns\" c ON (t.\"id\" = c.\"table_id\" AND o.\"name\" = c.\"name\") " + - "LEFT OUTER JOIN \"sys\".\"keys\" k ON (i.\"name\" = k.\"name\" AND i.\"table_id\" = k.\"table_id\" AND k.\"type\" IN (0,1)) "); // primary (0) and unique keys (1) only + "JOIN ").append(sysORtmp).append(".\"objects\" o ON i.\"id\" = o.\"id\" " + + "JOIN ").append(sysORtmp).append(".\"_columns\" c ON (t.\"id\" = c.\"table_id\" AND o.\"name\" = c.\"name\") " + + "LEFT OUTER JOIN ").append(sysORtmp).append(".\"keys\" k ON (i.\"name\" = k.\"name\" AND i.\"table_id\" = k.\"table_id\" AND k.\"type\" IN (0,1)) "); // primary (0) and unique keys (1) only if (catalog != null && !catalog.isEmpty()) { // non-empty catalog selection. diff --git a/tests/JDBC_API_Tester.java b/tests/JDBC_API_Tester.java --- a/tests/JDBC_API_Tester.java +++ b/tests/JDBC_API_Tester.java @@ -679,20 +679,33 @@ final public class JDBC_API_Tester { private void Test_Dobjects() { sb.setLength(0); // clear the output log buffer + String tablename = ""; + int response; Statement stmt = null; try { stmt = con.createStatement(); - int response = stmt.executeUpdate("CREATE TABLE nopk_twoucs (id INT NOT NULL UNIQUE, name VARCHAR(99) UNIQUE)"); + tablename = "nopk_twoucs"; + response = stmt.executeUpdate("CREATE TABLE nopk_twoucs (id INT NOT NULL UNIQUE, name VARCHAR(99) UNIQUE)"); + if (response != Statement.SUCCESS_NO_INFO) + sb.append("Creating table ").append(tablename).append(" failed to return -2!! It returned: ").append(response).append("\n"); + + tablename = "tmp_nopk_twoucs"; + response = stmt.executeUpdate("CREATE LOCAL TEMP TABLE tmp_nopk_twoucs (id2 INT NOT NULL UNIQUE, name2 VARCHAR(99) UNIQUE)"); if (response != Statement.SUCCESS_NO_INFO) - sb.append("Creating table nopk_twoucs failed to return -2!! It returned: " + response + "\n"); + sb.append("Creating table ").append(tablename).append(" failed to return -2!! It returned: ").append(response).append("\n"); + + tablename = "tmp_pk_uc"; + response = stmt.executeUpdate("CREATE LOCAL TEMP TABLE tmp_pk_uc (id1 INT NOT NULL PRIMARY KEY, name1 VARCHAR(99) UNIQUE)"); + if (response != Statement.SUCCESS_NO_INFO) + sb.append("Creating table ").append(tablename).append(" failed to return -2!! It returned: ").append(response).append("\n"); } catch (SQLException e) { - sb.append("failed to create test table nopk_twoucs: ").append(e.getMessage()); + sb.append("failed to create test table ").append(tablename).append(": ").append(e.getMessage()); } try { - int response = stmt.executeUpdate("CREATE TYPE xml EXTERNAL NAME xml"); + response = stmt.executeUpdate("CREATE TYPE xml EXTERNAL NAME xml"); if (response != Statement.SUCCESS_NO_INFO) - sb.append("Creating type xml failed to return -2!! It returned: " + response + "\n"); + sb.append("Creating type xml failed to return -2!! It returned: ").append(response).append("\n"); } catch (SQLException e) { sb.append("failed to create type xml: ").append(e.getMessage()); } @@ -713,6 +726,8 @@ final public class JDBC_API_Tester { compareResultSet(dbmd.getTables(null, "tmp", null, null), "getTables(null, tmp, null, null)", // schema tmp has 6 tables "Resultset with 10 columns\n" + "TABLE_CAT TABLE_SCHEM TABLE_NAME TABLE_TYPE REMARKS TYPE_CAT TYPE_SCHEM TYPE_NAME SELF_REFERENCING_COL_NAME REF_GENERATION\n" + + "null tmp tmp_nopk_twoucs LOCAL TEMPORARY TABLE null null null null null null\n" + + "null tmp tmp_pk_uc LOCAL TEMPORARY TABLE null null null null null null\n" + "null tmp _columns SYSTEM TABLE null null null null null null\n" + "null tmp _tables SYSTEM TABLE null null null null null null\n" + "null tmp idxs SYSTEM TABLE null null null null null null\n" + @@ -736,6 +751,11 @@ final public class JDBC_API_Tester { "TABLE_CAT TABLE_SCHEM TABLE_NAME COLUMN_NAME KEY_SEQ PK_NAME\n" + "null sys table_types table_type_id 1 table_types_table_type_id_pkey\n"); + compareResultSet(dbmd.getPrimaryKeys(null, "tmp", "tmp_pk_uc"), "getPrimaryKeys(null, tmp, tmp_pk_uc)", + "Resultset with 6 columns\n" + + "TABLE_CAT TABLE_SCHEM TABLE_NAME COLUMN_NAME KEY_SEQ PK_NAME\n" + + "null tmp tmp_pk_uc id1 1 tmp_pk_uc_id1_pkey\n"); + compareResultSet(dbmd.getExportedKeys(null, "sys", "table\\_types"), "getExportedKeys(null, sys, table\\_types)", "Resultset with 14 columns\n" + "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\n"); @@ -754,6 +774,12 @@ final public class JDBC_API_Tester { "null sys key_types false null key_types_key_type_id_pkey 2 1 key_type_id null 3 0 null\n" + "null sys key_types false null key_types_key_type_name_unique 2 1 key_type_name null 3 0 null\n"); + compareResultSet(dbmd.getIndexInfo(null, "tmp", "tmp_pk_uc", false, false), "getIndexInfo(null, tmp, tmp_pk_uc, false, false)", + "Resultset with 13 columns\n" + + "TABLE_CAT TABLE_SCHEM TABLE_NAME NON_UNIQUE INDEX_QUALIFIER INDEX_NAME TYPE ORDINAL_POSITION COLUMN_NAME ASC_OR_DESC CARDINALITY PAGES FILTER_CONDITION\n" + + "null tmp tmp_pk_uc false null tmp_pk_uc_id1_pkey 2 1 id1 null 0 0 null\n" + + "null tmp tmp_pk_uc false null tmp_pk_uc_name1_unique 2 1 name1 null 0 0 null\n"); + compareResultSet(dbmd.getBestRowIdentifier(null, "sys", "function_languages", DatabaseMetaData.bestRowTransaction, true), "getBestRowIdentifier(null, sys, function_languages, DatabaseMetaData.bestRowTransaction, true)", "Resultset with 8 columns\n" + @@ -773,6 +799,25 @@ final public class JDBC_API_Tester { "SCOPE COLUMN_NAME DATA_TYPE TYPE_NAME COLUMN_SIZE BUFFER_LENGTH DECIMAL_DIGITS PSEUDO_COLUMN\n" + "2 id 4 int 32 0 0 1\n"); + compareResultSet(dbmd.getBestRowIdentifier(null, "tmp", "tmp_pk_uc", DatabaseMetaData.bestRowTransaction, true), + "getBestRowIdentifier(null, tmp, tmp_pk_uc, DatabaseMetaData.bestRowTransaction, true)", + "Resultset with 8 columns\n" + + "SCOPE COLUMN_NAME DATA_TYPE TYPE_NAME COLUMN_SIZE BUFFER_LENGTH DECIMAL_DIGITS PSEUDO_COLUMN\n" + + "2 id1 4 int 32 0 0 1\n"); + + compareResultSet(dbmd.getBestRowIdentifier(null, "tmp", "tmp_nopk_twoucs", DatabaseMetaData.bestRowTransaction, true), + "getBestRowIdentifier(null, tmp, tmp_nopk_twoucs, DatabaseMetaData.bestRowTransaction, true)", + "Resultset with 8 columns\n" + + "SCOPE COLUMN_NAME DATA_TYPE TYPE_NAME COLUMN_SIZE BUFFER_LENGTH DECIMAL_DIGITS PSEUDO_COLUMN\n" + + "2 id2 4 int 32 0 0 1\n" + + "2 name2 12 varchar 99 0 0 1\n"); + + compareResultSet(dbmd.getBestRowIdentifier(null, "tmp", "tmp_nopk_twoucs", DatabaseMetaData.bestRowTransaction, false), + "getBestRowIdentifier(null, tmp, tmp_nopk_twoucs, DatabaseMetaData.bestRowTransaction, false)", + "Resultset with 8 columns\n" + + "SCOPE COLUMN_NAME DATA_TYPE TYPE_NAME COLUMN_SIZE BUFFER_LENGTH DECIMAL_DIGITS PSEUDO_COLUMN\n" + + "2 id2 4 int 32 0 0 1\n"); + compareResultSet(dbmd.getTablePrivileges(null, "sys", "table\\_types"), "getTablePrivileges(null, sys, table\\_types)", "Resultset with 7 columns\n" + "TABLE_CAT TABLE_SCHEM TABLE_NAME GRANTOR GRANTEE PRIVILEGE IS_GRANTABLE\n" + @@ -804,17 +849,28 @@ final public class JDBC_API_Tester { // cleanup created db objects try { - int response = stmt.executeUpdate("DROP TABLE nopk_twoucs"); + tablename = "nopk_twoucs"; + response = stmt.executeUpdate("DROP TABLE " + tablename); + if (response != Statement.SUCCESS_NO_INFO) + sb.append("Dropping table ").append(tablename).append(" failed to return -2!! It returned: ").append(response).append("\n"); + + tablename = "tmp_nopk_twoucs"; + response = stmt.executeUpdate("DROP TABLE " + tablename); if (response != Statement.SUCCESS_NO_INFO) - sb.append("Dropping table nopk_twoucs failed to return -2!! It returned: " + response + "\n"); + sb.append("Dropping table ").append(tablename).append(" failed to return -2!! It returned: ").append(response).append("\n"); + + tablename = "tmp_pk_uc"; + response = stmt.executeUpdate("DROP TABLE " + tablename); + if (response != Statement.SUCCESS_NO_INFO) + sb.append("Dropping table ").append(tablename).append(" failed to return -2!! It returned: ").append(response).append("\n"); } catch (SQLException e) { - sb.append("failed to drop table: ").append(e.getMessage()); + sb.append("failed to drop test table ").append(tablename).append(": ").append(e.getMessage()); } try { - int response = stmt.executeUpdate("DROP TYPE xml"); + response = stmt.executeUpdate("DROP TYPE xml"); if (response != Statement.SUCCESS_NO_INFO) - sb.append("Dropping type xml failed to return -2!! It returned: " + response + "\n"); + sb.append("Dropping type xml failed to return -2!! It returned: ").append(response).append("\n"); } catch (SQLException e) { sb.append("failed to drop type: ").append(e.getMessage()); } _______________________________________________ checkin-list mailing list -- [email protected] To unsubscribe send an email to [email protected]
