Mercurial > hg > monetdb-java

comparison src/main/java/org/monetdb/jdbc/MonetDatabaseMetaData.java @ 900:be8476c1acec

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Adapt queries used in JDBC DatabaseMetaData methods getBestRowIdentifier() and getIndexInfo() to include new key_type: 3 = Unique Key With Nulls Not Distinct.
author Martin van Dinther <martin.van.dinther@monetdbsolutions.com>
date Thu, 30 May 2024 18:27:51 +0200 (11 months ago)
parents cd6e5449fb1a
children cc6425e98017
comparison
equal deleted inserted replaced
899:cccaeb65a5d6 900:be8476c1acec
2450 query.append("with syskeys as (" + 2450 query.append("with syskeys as (" +
2451 // all pkeys 2451 // all pkeys
2452 "SELECT \"id\", \"table_id\" FROM \"sys\".\"keys\" WHERE \"type\" = 0 " + 2452 "SELECT \"id\", \"table_id\" FROM \"sys\".\"keys\" WHERE \"type\" = 0 " +
2453 "UNION ALL " + 2453 "UNION ALL " +
2454 // and first unique constraint of a table when table has no pkey 2454 // and first unique constraint of a table when table has no pkey
2455 "SELECT \"id\", \"table_id\" FROM \"sys\".\"keys\" WHERE \"type\" = 1 " + 2455 "SELECT \"id\", \"table_id\" FROM \"sys\".\"keys\" WHERE \"type\" IN (1, 3) " +
2456 "AND \"table_id\" NOT IN (select \"table_id\" from \"sys\".\"keys\" where \"type\" = 0) " + 2456 "AND \"table_id\" NOT IN (select \"table_id\" from \"sys\".\"keys\" where \"type\" = 0) " +
2457 "AND (\"table_id\", \"id\") IN (select \"table_id\", min(\"id\") from \"sys\".\"keys\" where \"type\" = 1 group by \"table_id\"))"); 2457 "AND (\"table_id\", \"id\") IN (select \"table_id\", min(\"id\") from \"sys\".\"keys\" where \"type\" IN (1, 3) group by \"table_id\"))");
2458 if (incltmpkey) { 2458 if (incltmpkey) {
2459 // we must also include the primary key or unique constraint of local temporary tables which are stored in tmp.keys 2459 // we must also include the primary key or unique constraint of local temporary tables which are stored in tmp.keys
2460 // 2nd cte: tmpkeys 2460 // 2nd cte: tmpkeys
2461 query.append(", tmpkeys as (" + 2461 query.append(", tmpkeys as (" +
2462 "SELECT \"id\", \"table_id\" FROM \"tmp\".\"keys\" WHERE \"type\" = 0 " + 2462 "SELECT \"id\", \"table_id\" FROM \"tmp\".\"keys\" WHERE \"type\" = 0 " +
2463 "UNION ALL " + 2463 "UNION ALL " +
2464 "SELECT \"id\", \"table_id\" FROM \"tmp\".\"keys\" WHERE \"type\" = 1 " + 2464 "SELECT \"id\", \"table_id\" FROM \"tmp\".\"keys\" WHERE \"type\" IN (1, 3) " +
2465 "AND \"table_id\" NOT IN (select \"table_id\" from \"tmp\".\"keys\" where \"type\" = 0) " + 2465 "AND \"table_id\" NOT IN (select \"table_id\" from \"tmp\".\"keys\" where \"type\" = 0) " +
2466 "AND (\"table_id\", \"id\") IN (select \"table_id\", min(\"id\") from \"tmp\".\"keys\" where \"type\" = 1 group by \"table_id\"))"); 2466 "AND (\"table_id\", \"id\") IN (select \"table_id\", min(\"id\") from \"tmp\".\"keys\" where \"type\" IN (1, 3) group by \"table_id\"))");
2467 } 2467 }
2468 // 3rd cte: tableids 2468 // 3rd cte: tableids
2469 query.append(", tableids as (" + 2469 query.append(", tableids as (" +
2470 "SELECT t.\"id\" " + 2470 "SELECT t.\"id\" " +
2471 "FROM \"sys\".\"tables\" t " + 2471 "FROM \"sys\".\"tables\" t " +
2518 // (else in SQuirreL no header is shown in the "Row IDs" tab) 2518 // (else in SQuirreL no header is shown in the "Row IDs" tab)
2519 query.append(" UNION ALL " + 2519 query.append(" UNION ALL " +
2520 "SELECT c.\"name\", c.\"type\", c.\"type_digits\", c.\"type_scale\", c.\"number\" " + 2520 "SELECT c.\"name\", c.\"type\", c.\"type_digits\", c.\"type_scale\", c.\"number\" " +
2521 "FROM tableids t " + 2521 "FROM tableids t " +
2522 "JOIN \"sys\".\"_columns\" c ON t.\"id\" = c.\"table_id\" " + 2522 "JOIN \"sys\".\"_columns\" c ON t.\"id\" = c.\"table_id\" " +
2523 "WHERE t.\"id\" NOT IN (SELECT \"table_id\" FROM \"sys\".\"keys\" WHERE \"type\" in (0, 1))"); 2523 "WHERE t.\"id\" NOT IN (SELECT \"table_id\" FROM \"sys\".\"keys\" WHERE \"type\" in (0, 1, 3))");
2524 if (!nullable) { 2524 if (!nullable) {
2525 query.append(" AND c.\"null\" = false"); 2525 query.append(" AND c.\"null\" = false");
2526 } 2526 }
2527 if (incltmpkey) { 2527 if (incltmpkey) {
2528 query.append(" UNION ALL " + 2528 query.append(" UNION ALL " +
2529 "SELECT c.\"name\", c.\"type\", c.\"type_digits\", c.\"type_scale\", c.\"number\" " + 2529 "SELECT c.\"name\", c.\"type\", c.\"type_digits\", c.\"type_scale\", c.\"number\" " +
2530 "FROM tableids t " + 2530 "FROM tableids t " +
2531 "JOIN \"tmp\".\"_columns\" c ON t.\"id\" = c.\"table_id\" " + 2531 "JOIN \"tmp\".\"_columns\" c ON t.\"id\" = c.\"table_id\" " +
2532 "WHERE t.\"id\" NOT IN (SELECT \"table_id\" FROM \"tmp\".\"keys\" WHERE \"type\" in (0, 1))"); 2532 "WHERE t.\"id\" NOT IN (SELECT \"table_id\" FROM \"tmp\".\"keys\" WHERE \"type\" in (0, 1, 3))");
2533 if (!nullable) { 2533 if (!nullable) {
2534 query.append(" AND c.\"null\" = false"); 2534 query.append(" AND c.\"null\" = false");
2535 } 2535 }
2536 } 2536 }
2537 // the final select query 2537 // the final select query
3228 "FROM \"sys\".\"idxs\" i " + 3228 "FROM \"sys\".\"idxs\" i " +
3229 "JOIN \"sys\".\"_tables\" t ON i.\"table_id\" = t.\"id\" " + 3229 "JOIN \"sys\".\"_tables\" t ON i.\"table_id\" = t.\"id\" " +
3230 "JOIN \"sys\".\"schemas\" s ON t.\"schema_id\" = s.\"id\" " + 3230 "JOIN \"sys\".\"schemas\" s ON t.\"schema_id\" = s.\"id\" " +
3231 "JOIN \"sys\".\"objects\" o ON i.\"id\" = o.\"id\" " + 3231 "JOIN \"sys\".\"objects\" o ON i.\"id\" = o.\"id\" " +
3232 "JOIN \"sys\".\"_columns\" c ON (t.\"id\" = c.\"table_id\" AND o.\"name\" = c.\"name\") " + 3232 "JOIN \"sys\".\"_columns\" c ON (t.\"id\" = c.\"table_id\" AND o.\"name\" = c.\"name\") " +
3233 "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 3233 "LEFT OUTER JOIN \"sys\".\"keys\" k ON (i.\"name\" = k.\"name\" AND i.\"table_id\" = k.\"table_id\" AND k.\"type\" IN (0, 1, 3)) "); // primary (0) and unique keys (1 or 3) only
3234 3234
3235 if (catalog != null && !catalog.isEmpty()) { 3235 if (catalog != null && !catalog.isEmpty()) {
3236 // non-empty catalog selection. 3236 // non-empty catalog selection.
3237 // as we do not support catalogs this always results in no rows returned 3237 // as we do not support catalogs this always results in no rows returned
3238 query.append("WHERE 1=0"); 3238 query.append("WHERE 1=0");
3275 "FROM \"tmp\".\"idxs\" i " + 3275 "FROM \"tmp\".\"idxs\" i " +
3276 "JOIN \"tmp\".\"_tables\" t ON i.\"table_id\" = t.\"id\" " + 3276 "JOIN \"tmp\".\"_tables\" t ON i.\"table_id\" = t.\"id\" " +
3277 "JOIN \"sys\".\"schemas\" s ON t.\"schema_id\" = s.\"id\" " + 3277 "JOIN \"sys\".\"schemas\" s ON t.\"schema_id\" = s.\"id\" " +
3278 "JOIN \"tmp\".\"objects\" o ON i.\"id\" = o.\"id\" " + 3278 "JOIN \"tmp\".\"objects\" o ON i.\"id\" = o.\"id\" " +
3279 "JOIN \"tmp\".\"_columns\" c ON (t.\"id\" = c.\"table_id\" AND o.\"name\" = c.\"name\") " + 3279 "JOIN \"tmp\".\"_columns\" c ON (t.\"id\" = c.\"table_id\" AND o.\"name\" = c.\"name\") " +
3280 "LEFT OUTER JOIN \"tmp\".\"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 3280 "LEFT OUTER JOIN \"tmp\".\"keys\" k ON (i.\"name\" = k.\"name\" AND i.\"table_id\" = k.\"table_id\" AND k.\"type\" IN (0, 1, 3)) "); // primary (0) and unique keys (1 or 3) only
3281 3281
3282 if (catalog != null && !catalog.isEmpty()) { 3282 if (catalog != null && !catalog.isEmpty()) {
3283 // non-empty catalog selection. 3283 // non-empty catalog selection.
3284 // as we do not support catalogs this always results in no rows returned 3284 // as we do not support catalogs this always results in no rows returned
3285 query.append("WHERE 1=0"); 3285 query.append("WHERE 1=0");