Mercurial > hg > monetdb-java

comparison src/main/java/org/monetdb/jdbc/MonetDatabaseMetaData.java @ 645:fbed03097738

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
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.
author Martin van Dinther <martin.van.dinther@monetdbsolutions.com>
date Wed, 30 Mar 2022 17:55:33 +0200 (2022-03-30)
parents dd9b4fb14256
children 060347aa81ea
comparison
equal deleted inserted replaced
644:901a9873a351 645:fbed03097738
2393 * </OL> 2393 * </OL>
2394 * 2394 *
2395 * @param catalog a catalog name; "" retrieves those without a catalog 2395 * @param catalog a catalog name; "" retrieves those without a catalog
2396 * @param schema a schema name; "" retrieves those without a schema 2396 * @param schema a schema name; "" retrieves those without a schema
2397 * @param table a table name 2397 * @param table a table name
2398 * Note that our implementation allows this param to be null also (for efficiency as no extra LIKE '%' condition is added to be evaluated).
2399 * @param scope the scope of interest; use same values as SCOPE 2398 * @param scope the scope of interest; use same values as SCOPE
2400 * @param nullable include columns that are nullable? 2399 * @param nullable include columns that are nullable?
2401 * @return ResultSet each row is a column description 2400 * @return ResultSet each row is a column description
2402 * @throws SQLException if a database error occurs 2401 * @throws SQLException if a database error occurs
2403 */ 2402 */
2408 final String table, 2407 final String table,
2409 final int scope, 2408 final int scope,
2410 final boolean nullable 2409 final boolean nullable
2411 ) throws SQLException 2410 ) throws SQLException
2412 { 2411 {
2413 // first find out if the table has a Primary Key, If it does, we should return only those columns 2412 final String sysORtmp = (schema != null && "tmp".equals(schema)) ? "\"tmp\"" : "\"sys\"";
2413
2414 // first find out if the table has a Primary Key. If it does, we should return only those columns
2414 boolean hasPK = false; 2415 boolean hasPK = false;
2415 ResultSet pkey = null; 2416 ResultSet pkey = null;
2416 try { 2417 try {
2417 pkey = getPrimaryKeys(catalog, schema, table); 2418 pkey = getPrimaryKeys(catalog, schema, table);
2418 if (pkey != null && pkey.next()) { 2419 if (pkey != null && pkey.next()) {
2440 "c.\"type\" AS \"TYPE_NAME\", " + 2441 "c.\"type\" AS \"TYPE_NAME\", " +
2441 "c.\"type_digits\" AS \"COLUMN_SIZE\", " + 2442 "c.\"type_digits\" AS \"COLUMN_SIZE\", " +
2442 "cast(0 as int) AS \"BUFFER_LENGTH\", " + 2443 "cast(0 as int) AS \"BUFFER_LENGTH\", " +
2443 "cast(c.\"type_scale\" AS smallint) AS \"DECIMAL_DIGITS\", " + 2444 "cast(c.\"type_scale\" AS smallint) AS \"DECIMAL_DIGITS\", " +
2444 "cast(").append(DatabaseMetaData.bestRowNotPseudo).append(" AS smallint) AS \"PSEUDO_COLUMN\" " + 2445 "cast(").append(DatabaseMetaData.bestRowNotPseudo).append(" AS smallint) AS \"PSEUDO_COLUMN\" " +
2445 "FROM \"sys\".\"keys\" k " + 2446 "FROM ").append(sysORtmp).append(".\"keys\" k " +
2446 "JOIN \"sys\".\"objects\" o ON k.\"id\" = o.\"id\" " + 2447 "JOIN ").append(sysORtmp).append(".\"objects\" o ON k.\"id\" = o.\"id\" " +
2447 "JOIN \"sys\".\"columns\" c ON (k.\"table_id\" = c.\"table_id\" AND o.\"name\" = c.\"name\") " + 2448 "JOIN ").append(sysORtmp).append(".\"_columns\" c ON (k.\"table_id\" = c.\"table_id\" AND o.\"name\" = c.\"name\") " +
2448 "JOIN \"sys\".\"tables\" t ON k.\"table_id\" = t.\"id\" " + 2449 "JOIN ").append(sysORtmp).append(".\"_tables\" t ON k.\"table_id\" = t.\"id\" " +
2449 "JOIN \"sys\".\"schemas\" s ON t.\"schema_id\" = s.\"id\" " + 2450 "JOIN \"sys\".\"schemas\" s ON t.\"schema_id\" = s.\"id\" " +
2450 "WHERE k.\"type\" = ").append(hasPK ? "0" : "1"); // the primary key (type = 0) or else any unique key (type = 1) 2451 "WHERE k.\"type\" = ").append(hasPK ? "0" : "1"); // the primary key (type = 0) or else any unique key (type = 1)
2451 2452
2452 if (catalog != null && !catalog.isEmpty()) { 2453 if (catalog != null && !catalog.isEmpty()) {
2453 // non-empty catalog selection. 2454 // non-empty catalog selection.
2455 query.append(" AND 1=0"); 2456 query.append(" AND 1=0");
2456 } else { 2457 } else {
2457 if (scope == DatabaseMetaData.bestRowSession 2458 if (scope == DatabaseMetaData.bestRowSession
2458 || scope == DatabaseMetaData.bestRowTransaction 2459 || scope == DatabaseMetaData.bestRowTransaction
2459 || scope == DatabaseMetaData.bestRowTemporary) { 2460 || scope == DatabaseMetaData.bestRowTemporary) {
2460 if (schema != null && !schema.equals("%")) { 2461 if (schema != null) {
2461 query.append(" AND s.\"name\" ").append(composeMatchPart(schema)); 2462 // do not allow wildcard matching with LIKE, as the resultset does not include the schema info
2463 query.append(" AND s.\"name\" = ").append(MonetWrapper.sq(schema));
2462 } 2464 }
2463 if (table != null && !table.equals("%")) { 2465 if (table != null) {
2464 query.append(" AND t.\"name\" ").append(composeMatchPart(table)); 2466 // do not allow wildcard matching with LIKE, as the resultset does not include the table info
2467 query.append(" AND t.\"name\" = ").append(MonetWrapper.sq(table));
2465 } 2468 }
2466 if (!nullable) { 2469 if (!nullable) {
2467 query.append(" AND c.\"null\" = false"); 2470 query.append(" AND c.\"null\" = false");
2468 } 2471 }
2469 } else { 2472 } else {
2558 final String catalog, 2561 final String catalog,
2559 final String schema, 2562 final String schema,
2560 final String table 2563 final String table
2561 ) throws SQLException 2564 ) throws SQLException
2562 { 2565 {
2566 final String sysORtmp = (schema != null && "tmp".equals(schema)) ? "\"tmp\"" : "\"sys\"";
2563 final StringBuilder query = new StringBuilder(600); 2567 final StringBuilder query = new StringBuilder(600);
2564 query.append("SELECT cast(null AS char(1)) AS \"TABLE_CAT\", " + 2568 query.append("SELECT cast(null AS char(1)) AS \"TABLE_CAT\", " +
2565 "s.\"name\" AS \"TABLE_SCHEM\", " + 2569 "s.\"name\" AS \"TABLE_SCHEM\", " +
2566 "t.\"name\" AS \"TABLE_NAME\", " + 2570 "t.\"name\" AS \"TABLE_NAME\", " +
2567 "o.\"name\" AS \"COLUMN_NAME\", " + 2571 "o.\"name\" AS \"COLUMN_NAME\", " +
2568 "cast(1 + o.\"nr\" AS smallint) AS \"KEY_SEQ\", " + 2572 "cast(1 + o.\"nr\" AS smallint) AS \"KEY_SEQ\", " +
2569 " k.\"name\" AS \"PK_NAME\" " + 2573 " k.\"name\" AS \"PK_NAME\" " +
2570 "FROM \"sys\".\"keys\" k " + 2574 "FROM ").append(sysORtmp).append(".\"keys\" k " +
2571 "JOIN \"sys\".\"objects\" o ON k.\"id\" = o.\"id\" " + 2575 "JOIN ").append(sysORtmp).append(".\"objects\" o ON k.\"id\" = o.\"id\" " +
2572 "JOIN \"sys\".\"tables\" t ON k.\"table_id\" = t.\"id\" " + 2576 "JOIN ").append(sysORtmp).append(".\"_tables\" t ON k.\"table_id\" = t.\"id\" " +
2573 "JOIN \"sys\".\"schemas\" s ON t.\"schema_id\" = s.\"id\" " + 2577 "JOIN \"sys\".\"schemas\" s ON t.\"schema_id\" = s.\"id\" " +
2574 "WHERE k.\"type\" = 0"); // only primary keys (type = 0) 2578 "WHERE k.\"type\" = 0"); // only primary keys (type = 0)
2575 2579
2576 if (catalog != null && !catalog.isEmpty()) { 2580 if (catalog != null && !catalog.isEmpty()) {
2577 // non-empty catalog selection. 2581 // non-empty catalog selection.
3075 } finally { 3079 } finally {
3076 MonetConnection.closeResultsetStatement(count, null); 3080 MonetConnection.closeResultsetStatement(count, null);
3077 } 3081 }
3078 } 3082 }
3079 3083
3084 final String sysORtmp = (schema != null && "tmp".equals(schema)) ? "\"tmp\"" : "\"sys\"";
3080 final StringBuilder query = new StringBuilder(1250); 3085 final StringBuilder query = new StringBuilder(1250);
3081 query.append( 3086 query.append(
3082 "SELECT cast(null AS char(1)) AS \"TABLE_CAT\", " + 3087 "SELECT cast(null AS char(1)) AS \"TABLE_CAT\", " +
3083 "s.\"name\" AS \"TABLE_SCHEM\", " + 3088 "s.\"name\" AS \"TABLE_SCHEM\", " +
3084 "t.\"name\" AS \"TABLE_NAME\", " + 3089 "t.\"name\" AS \"TABLE_NAME\", " +
3090 "c.\"name\" AS \"COLUMN_NAME\", " + 3095 "c.\"name\" AS \"COLUMN_NAME\", " +
3091 "cast(null AS char(1)) AS \"ASC_OR_DESC\", " + // sort sequence currently not supported in keys or indexes in MonetDB 3096 "cast(null AS char(1)) AS \"ASC_OR_DESC\", " + // sort sequence currently not supported in keys or indexes in MonetDB
3092 "cast(").append(table_row_count).append(" AS int) AS \"CARDINALITY\", " + 3097 "cast(").append(table_row_count).append(" AS int) AS \"CARDINALITY\", " +
3093 "cast(0 AS int) AS \"PAGES\", " + 3098 "cast(0 AS int) AS \"PAGES\", " +
3094 "cast(null AS char(1)) AS \"FILTER_CONDITION\" " + 3099 "cast(null AS char(1)) AS \"FILTER_CONDITION\" " +
3095 "FROM \"sys\".\"idxs\" i " + 3100 "FROM ").append(sysORtmp).append(".\"idxs\" i " +
3096 "JOIN \"sys\".\"tables\" t ON i.\"table_id\" = t.\"id\" " + 3101 "JOIN ").append(sysORtmp).append(".\"_tables\" t ON i.\"table_id\" = t.\"id\" " +
3097 "JOIN \"sys\".\"schemas\" s ON t.\"schema_id\" = s.\"id\" " + 3102 "JOIN \"sys\".\"schemas\" s ON t.\"schema_id\" = s.\"id\" " +
3098 "JOIN \"sys\".\"objects\" o ON i.\"id\" = o.\"id\" " + 3103 "JOIN ").append(sysORtmp).append(".\"objects\" o ON i.\"id\" = o.\"id\" " +
3099 "JOIN \"sys\".\"columns\" c ON (t.\"id\" = c.\"table_id\" AND o.\"name\" = c.\"name\") " + 3104 "JOIN ").append(sysORtmp).append(".\"_columns\" c ON (t.\"id\" = c.\"table_id\" AND o.\"name\" = c.\"name\") " +
3100 "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 3105 "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
3101 3106
3102 if (catalog != null && !catalog.isEmpty()) { 3107 if (catalog != null && !catalog.isEmpty()) {
3103 // non-empty catalog selection. 3108 // non-empty catalog selection.
3104 // as we do not support catalogs this always results in no rows returned 3109 // as we do not support catalogs this always results in no rows returned
3105 query.append("WHERE 1=0"); 3110 query.append("WHERE 1=0");