Mercurial > hg > monetdb-java

comparison src/main/java/org/monetdb/jdbc/MonetDatabaseMetaData.java @ 653:5eb9d54057e6

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Improved DatabaseMetaData.getBestRowIdentifier() further by introducing an extra cte: tableids. It makes it easier to understand/maintain and possibly faster. Also implemented a TODO: when there is No PK and No unique constraints. It now returns all columns of the table, but none for views. Also updated and extended JDBC_API_Tester program.
author Martin van Dinther <martin.van.dinther@monetdbsolutions.com>
date Thu, 25 Aug 2022 15:46:06 +0200 (2022-08-25)
parents 6a34d2c36dec
children 2448ce017593
comparison
equal deleted inserted replaced
652:6a34d2c36dec 653:5eb9d54057e6
2418 * When there is No PK but there are multiple unique constraints, we need to pick one. 2418 * When there is No PK but there are multiple unique constraints, we need to pick one.
2419 * In the current implementation we return the first uc (lowest sys.keys.id). 2419 * In the current implementation we return the first uc (lowest sys.keys.id).
2420 * Instead of the first (in case of multiple) we should potentially use the uc which has 2420 * Instead of the first (in case of multiple) we should potentially use the uc which has
2421 * a) the least number of columns and 2421 * a) the least number of columns and
2422 * b) the smallest total(size in bytes). 2422 * b) the smallest total(size in bytes).
2423 * That's complex to built in SQL. 2423 * That's much more complex to do in SQL than the current implementation, which is fast and gives a correct result.
2424 */ 2424 */
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) 2425
2426 2426 final StringBuilder query = new StringBuilder(3000);
2427 final StringBuilder query = new StringBuilder(2600); 2427 // 1st cte: syskeys
2428 query.append("with syskeys as (" + 2428 query.append("with syskeys as (" +
2429 // all pkeys 2429 // all pkeys
2430 "SELECT \"id\", \"table_id\" FROM \"sys\".\"keys\" WHERE \"type\" = 0 " + 2430 "SELECT \"id\", \"table_id\" FROM \"sys\".\"keys\" WHERE \"type\" = 0 " +
2431 "UNION ALL " + 2431 "UNION ALL " +
2432 // and first unique constraint of a table when table has no pkey 2432 // and first unique constraint of a table when table has no pkey
2433 "SELECT \"id\", \"table_id\" FROM \"sys\".\"keys\" WHERE \"type\" = 1 " + 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) " + 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\"))"); 2435 "AND (\"table_id\", \"id\") IN (select \"table_id\", min(\"id\") from \"sys\".\"keys\" where \"type\" = 1 group by \"table_id\"))");
2436 if (incltmpkey) { 2436 if (incltmpkey) {
2437 // we must also include the primary key or unique constraint of local temporary tables which are stored in tmp.keys 2437 // we must also include the primary key or unique constraint of local temporary tables which are stored in tmp.keys
2438 // 2nd cte: tmpkeys
2438 query.append(", tmpkeys as (" + 2439 query.append(", tmpkeys as (" +
2439 "SELECT \"id\", \"table_id\" FROM \"tmp\".\"keys\" WHERE \"type\" = 0 " + 2440 "SELECT \"id\", \"table_id\" FROM \"tmp\".\"keys\" WHERE \"type\" = 0 " +
2440 "UNION ALL " + 2441 "UNION ALL " +
2441 "SELECT \"id\", \"table_id\" FROM \"tmp\".\"keys\" WHERE \"type\" = 1 " + 2442 "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\" 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\"))"); 2444 "AND (\"table_id\", \"id\") IN (select \"table_id\", min(\"id\") from \"tmp\".\"keys\" where \"type\" = 1 group by \"table_id\"))");
2444 } 2445 }
2445 query.append(", cols as (" + 2446 // 3rd cte: tableids
2446 "SELECT c.\"name\", c.\"type\", c.\"type_digits\", c.\"type_scale\", o.\"nr\" " + 2447 query.append(", tableids as (" +
2447 "FROM syskeys k " + 2448 "SELECT t.\"id\" " +
2448 "JOIN \"sys\".\"objects\" o ON k.\"id\" = o.\"id\" " + 2449 "FROM \"sys\".\"tables\" t " +
2449 "JOIN \"sys\".\"_columns\" c ON (k.\"table_id\" = c.\"table_id\" AND o.\"name\" = c.\"name\") " +
2450 "JOIN \"sys\".\"_tables\" t ON k.\"table_id\" = t.\"id\" " +
2451 "JOIN \"sys\".\"schemas\" s ON t.\"schema_id\" = s.\"id\" " + 2450 "JOIN \"sys\".\"schemas\" s ON t.\"schema_id\" = s.\"id\" " +
2452 "WHERE 1=1"); 2451 "WHERE t.\"type\" NOT IN (1, 11) "); // exclude all VIEWs and SYSTEM VIEWs
2453 if (catalog != null && !catalog.isEmpty()) { 2452 if (catalog != null && !catalog.isEmpty()) {
2454 // non-empty catalog selection. 2453 // non-empty catalog selection.
2455 // as we do not support catalogs this always results in no rows returned 2454 // as we do not support catalogs this always results in no rows returned
2456 query.append(" AND 1=0"); 2455 query.append("AND 1=0");
2457 } else { 2456 } else {
2458 if (scope == DatabaseMetaData.bestRowSession 2457 if (scope == DatabaseMetaData.bestRowSession
2459 || scope == DatabaseMetaData.bestRowTransaction 2458 || scope == DatabaseMetaData.bestRowTransaction
2460 || scope == DatabaseMetaData.bestRowTemporary) { 2459 || scope == DatabaseMetaData.bestRowTemporary) {
2461 if (schema != null) { 2460 if (schema != null) {
2462 // do not allow wildcard matching with LIKE, as the resultset does not include the schema info 2461 // 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 query.append("AND s.\"name\" = ").append(MonetWrapper.sq(schema));
2464 } 2463 }
2465 if (table != null) { 2464 if (table != null) {
2466 // do not allow wildcard matching with LIKE, as the resultset does not include the table info 2465 // 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)); 2466 query.append(" AND t.\"name\" = ").append(MonetWrapper.sq(table));
2468 } 2467 }
2469 if (!nullable) {
2470 query.append(" AND c.\"null\" = false");
2471 }
2472 } else { 2468 } else {
2473 query.append(" AND 1=0"); 2469 query.append("AND 1=0");
2474 } 2470 }
2471 }
2472 // 4th cte: cols, this unions 2 (or 4 when incltmpkey == true) select queries
2473 query.append("), cols as (" +
2474 "SELECT c.\"name\", c.\"type\", c.\"type_digits\", c.\"type_scale\", o.\"nr\" " +
2475 "FROM syskeys k " +
2476 "JOIN tableids t ON k.\"table_id\" = t.\"id\" " +
2477 "JOIN \"sys\".\"objects\" o ON k.\"id\" = o.\"id\" " +
2478 "JOIN \"sys\".\"_columns\" c ON (k.\"table_id\" = c.\"table_id\" AND o.\"name\" = c.\"name\")");
2479 if (!nullable) {
2480 query.append(" WHERE c.\"null\" = false");
2475 } 2481 }
2476 if (incltmpkey) { 2482 if (incltmpkey) {
2477 // we must also include the primary key or unique constraint of local temporary tables 2483 // we must also include the primary key or unique constraint of local temporary tables
2478 // which are stored in tmp.keys, tmp.objects, tmp._tables and tmp._columns 2484 // which are stored in tmp.keys, tmp.objects, tmp._tables and tmp._columns
2479 query.append(" UNION ALL " + 2485 query.append(" UNION ALL " +
2480 "SELECT c.\"name\", c.\"type\", c.\"type_digits\", c.\"type_scale\", o.\"nr\" " + 2486 "SELECT c.\"name\", c.\"type\", c.\"type_digits\", c.\"type_scale\", o.\"nr\" " +
2481 "FROM tmpkeys k " + 2487 "FROM tmpkeys k " +
2488 "JOIN tableids t ON k.\"table_id\" = t.\"id\" " +
2482 "JOIN \"tmp\".\"objects\" o ON k.\"id\" = o.\"id\" " + 2489 "JOIN \"tmp\".\"objects\" o ON k.\"id\" = o.\"id\" " +
2483 "JOIN \"tmp\".\"_columns\" c ON (k.\"table_id\" = c.\"table_id\" AND o.\"name\" = c.\"name\") " + 2490 "JOIN \"tmp\".\"_columns\" c ON (k.\"table_id\" = c.\"table_id\" AND o.\"name\" = c.\"name\")");
2484 "JOIN \"tmp\".\"_tables\" t ON k.\"table_id\" = t.\"id\" " + 2491 if (!nullable) {
2485 "JOIN \"sys\".\"schemas\" s ON t.\"schema_id\" = s.\"id\" " + 2492 query.append(" WHERE c.\"null\" = false");
2486 "WHERE 1=1");
2487 if (catalog != null && !catalog.isEmpty()) {
2488 // non-empty catalog selection.
2489 // as we do not support catalogs this always results in no rows returned
2490 query.append(" AND 1=0");
2491 } else {
2492 if (scope == DatabaseMetaData.bestRowSession
2493 || scope == DatabaseMetaData.bestRowTransaction
2494 || scope == DatabaseMetaData.bestRowTemporary) {
2495 if (schema != null) {
2496 // do not allow wildcard matching with LIKE, as the resultset does not include the schema info
2497 query.append(" AND s.\"name\" = ").append(MonetWrapper.sq(schema));
2498 }
2499 if (table != null) {
2500 // do not allow wildcard matching with LIKE, as the resultset does not include the table info
2501 query.append(" AND t.\"name\" = ").append(MonetWrapper.sq(table));
2502 }
2503 if (!nullable) {
2504 query.append(" AND c.\"null\" = false");
2505 }
2506 } else {
2507 query.append(" AND 1=0");
2508 }
2509 } 2493 }
2510 } 2494 }
2495 // when there is No PK and No unique constraints, we should return all columns of the table
2496 // (else in SQuirreL no header is shown in the "Row IDs" tab)
2497 query.append(" UNION ALL " +
2498 "SELECT c.\"name\", c.\"type\", c.\"type_digits\", c.\"type_scale\", c.\"number\" " +
2499 "FROM tableids t " +
2500 "JOIN \"sys\".\"_columns\" c ON t.\"id\" = c.\"table_id\" " +
2501 "WHERE t.\"id\" NOT IN (SELECT \"table_id\" FROM \"sys\".\"keys\" WHERE \"type\" in (0, 1))");
2502 if (!nullable) {
2503 query.append(" AND c.\"null\" = false");
2504 }
2505 if (incltmpkey) {
2506 query.append(" UNION ALL " +
2507 "SELECT c.\"name\", c.\"type\", c.\"type_digits\", c.\"type_scale\", c.\"number\" " +
2508 "FROM tableids t " +
2509 "JOIN \"tmp\".\"_columns\" c ON t.\"id\" = c.\"table_id\" " +
2510 "WHERE t.\"id\" NOT IN (SELECT \"table_id\" FROM \"tmp\".\"keys\" WHERE \"type\" in (0, 1))");
2511 if (!nullable) {
2512 query.append(" AND c.\"null\" = false");
2513 }
2514 }
2515 // the final select query
2511 query.append(") SELECT " + 2516 query.append(") SELECT " +
2512 "cast(").append(DatabaseMetaData.bestRowSession).append(" AS smallint) AS \"SCOPE\", " + 2517 "cast(").append(DatabaseMetaData.bestRowSession).append(" AS smallint) AS \"SCOPE\", " +
2513 "c.\"name\" AS \"COLUMN_NAME\", " + 2518 "c.\"name\" AS \"COLUMN_NAME\", " +
2514 "cast(").append(MonetDriver.getSQLTypeMap("c.\"type\"")).append(" AS int) AS \"DATA_TYPE\", " + 2519 "cast(").append(MonetDriver.getSQLTypeMap("c.\"type\"")).append(" AS int) AS \"DATA_TYPE\", " +
2515 "c.\"type\" AS \"TYPE_NAME\", " + 2520 "c.\"type\" AS \"TYPE_NAME\", " +