Mercurial > hg > monetdb-java

comparison src/main/java/org/monetdb/jdbc/MonetDatabaseMetaData.java @ 652:6a34d2c36dec

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Improved and optimized DatabaseMetaData.getBestRowIdentifier().
author Martin van Dinther <martin.van.dinther@monetdbsolutions.com>
date Wed, 24 Aug 2022 22:02:32 +0200 (2022-08-24)
parents 060347aa81ea
children 5eb9d54057e6
comparison
equal deleted inserted replaced
651:3b6139d35057 652:6a34d2c36dec
2075 * does not allow NULL values; "YES" means the column might 2075 * does not allow NULL values; "YES" means the column might
2076 * allow NULL values. An empty string means nobody knows. 2076 * allow NULL values. An empty string means nobody knows.
2077 * <LI><B>SCOPE_CATALOG</B> String =&gt; catalog of table that is the scope of a reference attribute (null if DATA_TYPE isn't REF) 2077 * <LI><B>SCOPE_CATALOG</B> String =&gt; catalog of table that is the scope of a reference attribute (null if DATA_TYPE isn't REF)
2078 * <LI><B>SCOPE_SCHEMA</B> String =&gt; schema of table that is the scope of a reference attribute (null if the DATA_TYPE isn't REF) 2078 * <LI><B>SCOPE_SCHEMA</B> String =&gt; schema of table that is the scope of a reference attribute (null if the DATA_TYPE isn't REF)
2079 * <LI><B>SCOPE_TABLE</B> String =&gt; table name that this the scope of a reference attribute (null if the DATA_TYPE isn't REF) 2079 * <LI><B>SCOPE_TABLE</B> String =&gt; table name that this the scope of a reference attribute (null if the DATA_TYPE isn't REF)
2080 * <LI><B>SOURCE_DATA_TYPE</B> short =&gt; source type of a distinct type or user-generated Ref type, SQL type from java.sql.Types (null if DATA_TYPE isn't DISTINCT or user-generated REF) 2080 * <LI><B>SOURCE_DATA_TYPE</B> short =&gt; source type of a distinct type or user-generated Ref type,
2081 * SQL type from java.sql.Types (null if DATA_TYPE isn't DISTINCT or user-generated REF)
2081 * <LI><B>IS_AUTOINCREMENT</B> String =&gt; Indicates whether this column is auto incremented 2082 * <LI><B>IS_AUTOINCREMENT</B> String =&gt; Indicates whether this column is auto incremented
2082 * <UL> 2083 * <UL>
2083 * <LI> YES --- if the column is auto incremented 2084 * <LI> YES --- if the column is auto incremented
2084 * <LI> NO --- if the column is not auto incremented 2085 * <LI> NO --- if the column is not auto incremented
2085 * <LI> empty string --- if it cannot be determined whether the column is auto incremented 2086 * <LI> empty string --- if it cannot be determined whether the column is auto incremented
2407 final String table, 2408 final String table,
2408 final int scope, 2409 final int scope,
2409 final boolean nullable 2410 final boolean nullable
2410 ) throws SQLException 2411 ) throws SQLException
2411 { 2412 {
2412 // first find out if the table has a Primary Key. If it does, we should return only those columns 2413 /* determine if we need to include a query against the tmp.* tables also */
2413 boolean hasPK = false; 2414 final boolean incltmpkey = (schema == null)
2414 ResultSet pkey = null; 2415 || (schema != null && ("tmp".equals(schema) || schema.contains("%") || schema.contains("_")));
2415 try { 2416
2416 pkey = getPrimaryKeys(catalog, schema, table); 2417 /* When there is a PK for the table we return the pkey columns
2417 if (pkey != null && pkey.next()) { 2418 * When there is No PK but there are multiple unique constraints, we need to pick one.
2418 hasPK = true; 2419 * In the current implementation we return the first uc (lowest sys.keys.id).
2419 } 2420 * Instead of the first (in case of multiple) we should potentially use the uc which has
2420 } catch (SQLException e) { 2421 * a) the least number of columns and
2421 // ignore 2422 * b) the smallest total(size in bytes).
2422 } finally { 2423 * That's complex to built in SQL.
2423 MonetConnection.closeResultsetStatement(pkey, null); 2424 */
2424 pkey = null; 2425 // TODO: when there is No PK and No unique constraints, we potentially should return all columns of the table (else in SQuirreL no header is shown in the "Row IDs" tab)
2426
2427 final StringBuilder query = new StringBuilder(2600);
2428 query.append("with syskeys as (" +
2429 // all pkeys
2430 "SELECT \"id\", \"table_id\" FROM \"sys\".\"keys\" WHERE \"type\" = 0 " +
2431 "UNION ALL " +
2432 // and first unique constraint of a table when table has no pkey
2433 "SELECT \"id\", \"table_id\" FROM \"sys\".\"keys\" WHERE \"type\" = 1 " +
2434 "AND \"table_id\" NOT IN (select \"table_id\" from \"sys\".\"keys\" where \"type\" = 0) " +
2435 "AND (\"table_id\", \"id\") IN (select \"table_id\", min(\"id\") from \"sys\".\"keys\" where \"type\" = 1 group by \"table_id\"))");
2436 if (incltmpkey) {
2437 // we must also include the primary key or unique constraint of local temporary tables which are stored in tmp.keys
2438 query.append(", tmpkeys as (" +
2439 "SELECT \"id\", \"table_id\" FROM \"tmp\".\"keys\" WHERE \"type\" = 0 " +
2440 "UNION ALL " +
2441 "SELECT \"id\", \"table_id\" FROM \"tmp\".\"keys\" WHERE \"type\" = 1 " +
2442 "AND \"table_id\" NOT IN (select \"table_id\" from \"tmp\".\"keys\" where \"type\" = 0) " +
2443 "AND (\"table_id\", \"id\") IN (select \"table_id\", min(\"id\") from \"tmp\".\"keys\" where \"type\" = 1 group by \"table_id\"))");
2425 } 2444 }
2426 2445 query.append(", cols as (" +
2427 // TODO: when there is No PK and there are multiple unique constraints, pick only the unique constraint which has a) the least number of columns and b) the smallest total(size in bytes) 2446 "SELECT c.\"name\", c.\"type\", c.\"type_digits\", c.\"type_scale\", o.\"nr\" " +
2428 // TODO: when there is No PK and No unique constraints, we potentially should return all columns of the table (else in SQuirreL no header is shown in the "Row IDs" tab) 2447 "FROM syskeys k " +
2429 2448 "JOIN \"sys\".\"objects\" o ON k.\"id\" = o.\"id\" " +
2430 final StringBuilder query = new StringBuilder(3000); 2449 "JOIN \"sys\".\"_columns\" c ON (k.\"table_id\" = c.\"table_id\" AND o.\"name\" = c.\"name\") " +
2431 query.append("SELECT "); 2450 "JOIN \"sys\".\"_tables\" t ON k.\"table_id\" = t.\"id\" " +
2432 if (!hasPK) { 2451 "JOIN \"sys\".\"schemas\" s ON t.\"schema_id\" = s.\"id\" " +
2433 // Note: currently DISTINCT is needed to filter out possible duplicate column names when there are multiple unique constraints !! 2452 "WHERE 1=1");
2434 // TODO: when no PK and there are multiple unique constraints determine which one to select such that DISTINCT keyword is not needed anymore
2435 query.append("DISTINCT ");
2436 }
2437 query.append("cast(").append(DatabaseMetaData.bestRowSession).append(" AS smallint) AS \"SCOPE\", " +
2438 "c.\"name\" AS \"COLUMN_NAME\", " +
2439 "cast(").append(MonetDriver.getSQLTypeMap("c.\"type\"")).append(" AS int) AS \"DATA_TYPE\", " +
2440 "c.\"type\" AS \"TYPE_NAME\", " +
2441 "c.\"type_digits\" AS \"COLUMN_SIZE\", " +
2442 "cast(0 as int) AS \"BUFFER_LENGTH\", " +
2443 "cast(c.\"type_scale\" AS smallint) AS \"DECIMAL_DIGITS\", " +
2444 "cast(").append(DatabaseMetaData.bestRowNotPseudo).append(" AS smallint) AS \"PSEUDO_COLUMN\" " +
2445 "FROM \"sys\".\"keys\" k " +
2446 "JOIN \"sys\".\"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 \"sys\".\"_tables\" t ON k.\"table_id\" = t.\"id\" " +
2449 "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
2452 if (catalog != null && !catalog.isEmpty()) { 2453 if (catalog != null && !catalog.isEmpty()) {
2453 // non-empty catalog selection. 2454 // non-empty catalog selection.
2454 // as we do not support catalogs this always results in no rows returned 2455 // as we do not support catalogs this always results in no rows returned
2455 query.append(" AND 1=0"); 2456 query.append(" AND 1=0");
2456 } else { 2457 } else {
2470 } 2471 }
2471 } else { 2472 } else {
2472 query.append(" AND 1=0"); 2473 query.append(" AND 1=0");
2473 } 2474 }
2474 } 2475 }
2475 2476 if (incltmpkey) {
2476 final boolean includetmp = (schema == null) 2477 // we must also include the primary key or unique constraint of local temporary tables
2477 || (schema != null && ("tmp".equals(schema) || schema.contains("%") || schema.contains("_"))); 2478 // which are stored in tmp.keys, tmp.objects, tmp._tables and tmp._columns
2478 if (includetmp) { 2479 query.append(" UNION ALL " +
2479 // we must also include the primary key or unique constraint of local temporary tables which are stored in tmp.keys, tmp.objects, tmp._tables and tmp._columns 2480 "SELECT c.\"name\", c.\"type\", c.\"type_digits\", c.\"type_scale\", o.\"nr\" " +
2480 query.append(" UNION ALL "); 2481 "FROM tmpkeys k " +
2481 query.append("SELECT ");
2482 if (!hasPK) {
2483 // Note: currently DISTINCT is needed to filter out possible duplicate column names when there are multiple unique constraints !!
2484 // TODO: when no PK and there are multiple unique constraints determine which one to select such that DISTINCT keyword is not needed anymore
2485 query.append("DISTINCT ");
2486 }
2487 query.append("cast(").append(DatabaseMetaData.bestRowSession).append(" AS smallint) AS \"SCOPE\", " +
2488 "c.\"name\" AS \"COLUMN_NAME\", " +
2489 "cast(").append(MonetDriver.getSQLTypeMap("c.\"type\"")).append(" AS int) AS \"DATA_TYPE\", " +
2490 "c.\"type\" AS \"TYPE_NAME\", " +
2491 "c.\"type_digits\" AS \"COLUMN_SIZE\", " +
2492 "cast(0 as int) AS \"BUFFER_LENGTH\", " +
2493 "cast(c.\"type_scale\" AS smallint) AS \"DECIMAL_DIGITS\", " +
2494 "cast(").append(DatabaseMetaData.bestRowNotPseudo).append(" AS smallint) AS \"PSEUDO_COLUMN\" " +
2495 "FROM \"tmp\".\"keys\" k " +
2496 "JOIN \"tmp\".\"objects\" o ON k.\"id\" = o.\"id\" " + 2482 "JOIN \"tmp\".\"objects\" o ON k.\"id\" = o.\"id\" " +
2497 "JOIN \"tmp\".\"_columns\" c ON (k.\"table_id\" = c.\"table_id\" AND o.\"name\" = c.\"name\") " + 2483 "JOIN \"tmp\".\"_columns\" c ON (k.\"table_id\" = c.\"table_id\" AND o.\"name\" = c.\"name\") " +
2498 "JOIN \"tmp\".\"_tables\" t ON k.\"table_id\" = t.\"id\" " + 2484 "JOIN \"tmp\".\"_tables\" t ON k.\"table_id\" = t.\"id\" " +
2499 "JOIN \"sys\".\"schemas\" s ON t.\"schema_id\" = s.\"id\" " + 2485 "JOIN \"sys\".\"schemas\" s ON t.\"schema_id\" = s.\"id\" " +
2500 "WHERE k.\"type\" = ").append(hasPK ? "0" : "1"); // the primary key (type = 0) or else any unique key (type = 1) 2486 "WHERE 1=1");
2501
2502 if (catalog != null && !catalog.isEmpty()) { 2487 if (catalog != null && !catalog.isEmpty()) {
2503 // non-empty catalog selection. 2488 // non-empty catalog selection.
2504 // as we do not support catalogs this always results in no rows returned 2489 // as we do not support catalogs this always results in no rows returned
2505 query.append(" AND 1=0"); 2490 query.append(" AND 1=0");
2506 } else { 2491 } else {
2521 } else { 2506 } else {
2522 query.append(" AND 1=0"); 2507 query.append(" AND 1=0");
2523 } 2508 }
2524 } 2509 }
2525 } 2510 }
2526 2511 query.append(") SELECT " +
2527 // was: query.append(" ORDER BY \"SCOPE\", o.\"nr\", \"COLUMN_NAME\""); 2512 "cast(").append(DatabaseMetaData.bestRowSession).append(" AS smallint) AS \"SCOPE\", " +
2528 // But as of Jan2022 this ordering returns error: SELECT: with DISTINCT ORDER BY expressions must appear in select list 2513 "c.\"name\" AS \"COLUMN_NAME\", " +
2529 // so had to remove the o.\"nr\", part when there is No PKey. This means the columns are than ordered on names instead of creation order in their unique constraint definition 2514 "cast(").append(MonetDriver.getSQLTypeMap("c.\"type\"")).append(" AS int) AS \"DATA_TYPE\", " +
2530 query.append(" ORDER BY \"SCOPE\", "); 2515 "c.\"type\" AS \"TYPE_NAME\", " +
2531 if (hasPK && !includetmp) 2516 "c.\"type_digits\" AS \"COLUMN_SIZE\", " +
2532 query.append("o.\"nr\", "); 2517 "cast(0 as int) AS \"BUFFER_LENGTH\", " +
2533 query.append("\"COLUMN_NAME\""); 2518 "cast(c.\"type_scale\" AS smallint) AS \"DECIMAL_DIGITS\", " +
2519 "cast(").append(DatabaseMetaData.bestRowNotPseudo).append(" AS smallint) AS \"PSEUDO_COLUMN\" " +
2520 "FROM cols c " +
2521 "ORDER BY \"SCOPE\", c.\"nr\", \"COLUMN_NAME\"");
2534 2522
2535 return executeMetaDataQuery(query.toString()); 2523 return executeMetaDataQuery(query.toString());
2536 } 2524 }
2537 2525
2538 /** 2526 /**