Mercurial > hg > monetdb-java

comparison src/main/java/nl/cwi/monetdb/jdbc/MonetDatabaseMetaData.java @ 331:6ed8f5b1f9ed

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Corrected method DatabaseMetaData.getBestRowIdentifier(). It used to return columns of both primary key and unique constraints. Now it only returns the columns of the primary key if it has one, else columns of a unique constraint.
author Martin van Dinther <martin.van.dinther@monetdbsolutions.com>
date Wed, 18 Sep 2019 20:02:44 +0200 (2019-09-18)
parents 8701024a9bb0
children e8962bdaa206
comparison
equal deleted inserted replaced
330:98ae44c5fd56 331:6ed8f5b1f9ed
458 } catch (SQLException e) { 458 } catch (SQLException e) {
459 /* ignore */ 459 /* ignore */
460 } finally { 460 } finally {
461 MonetConnection.closeResultsetStatement(rs, st); 461 MonetConnection.closeResultsetStatement(rs, st);
462 } 462 }
463 // for debug: System.out.println("SQL query: " + query + "\nResult string: " + sb.toString()); 463 // for debug: System.out.println("SQL (len " + query.length() + "): " + query + "\nResult string: " + sb.toString());
464 return sb.toString(); 464 return sb.toString();
465 } 465 }
466 466
467 // SQL query parts shared by four get<Type>Functions() below 467 // SQL query parts shared by four get<Type>Functions() below
468 private static final String FunctionsSelect = "SELECT DISTINCT CASE WHEN \"language\" > 0 THEN s.\"name\"||'.'||f.\"name\" ELSE f.\"name\" END FROM \"sys\".\"functions\" f JOIN \"sys\".\"schemas\" s ON f.\"schema_id\" = s.\"id\" WHERE "; 468 private static final String FunctionsSelect = "SELECT DISTINCT CASE WHEN \"language\" > 0 THEN s.\"name\"||'.'||f.\"name\" ELSE f.\"name\" END FROM \"sys\".\"functions\" f JOIN \"sys\".\"schemas\" s ON f.\"schema_id\" = s.\"id\" WHERE ";
1924 { 1924 {
1925 // as of Jul2015 release the sys.tables.type values (0 through 6) is extended with new values 10, 11, 20, and 30 (for system and temp tables/views). 1925 // as of Jul2015 release the sys.tables.type values (0 through 6) is extended with new values 10, 11, 20, and 30 (for system and temp tables/views).
1926 // as of Jul2015 release we also have a new table: sys.table_types with names for the new table types 1926 // as of Jul2015 release we also have a new table: sys.table_types with names for the new table types
1927 // for correct behavior we need to know if the server is using the old (pre Jul2015) or new sys.tables.type values 1927 // for correct behavior we need to know if the server is using the old (pre Jul2015) or new sys.tables.type values
1928 final boolean preJul2015 = ("11.19.15".compareTo(getDatabaseProductVersion()) >= 0); 1928 final boolean preJul2015 = ("11.19.15".compareTo(getDatabaseProductVersion()) >= 0);
1929 /* for debug: System.out.println("getDatabaseProductVersion() is " + getDatabaseProductVersion() + " preJul2015 is " + preJul2015); */ 1929 // for debug: System.out.println("getDatabaseProductVersion() is " + getDatabaseProductVersion() + " preJul2015 is " + preJul2015);
1930 1930
1931 final boolean useCommentsTable = ((MonetConnection)con).commentsTableExists(); 1931 final boolean useCommentsTable = ((MonetConnection)con).commentsTableExists();
1932 final StringBuilder query = new StringBuilder(1600); 1932 final StringBuilder query = new StringBuilder(1600);
1933 if (preJul2015 && types != null && types.length > 0) { 1933 if (preJul2015 && types != null && types.length > 0) {
1934 // we need to filter on the constructed "TABLE_TYPE" expression, this is only possible when we use a subquery in the FROM 1934 // we need to filter on the constructed "TABLE_TYPE" expression, this is only possible when we use a subquery in the FROM
2184 "t.\"name\" AS \"TABLE_NAME\", " + 2184 "t.\"name\" AS \"TABLE_NAME\", " +
2185 "c.\"name\" AS \"COLUMN_NAME\", " + 2185 "c.\"name\" AS \"COLUMN_NAME\", " +
2186 "cast(").append(MonetDriver.getSQLTypeMap("c.\"type\"")).append(" AS int) AS \"DATA_TYPE\", " + 2186 "cast(").append(MonetDriver.getSQLTypeMap("c.\"type\"")).append(" AS int) AS \"DATA_TYPE\", " +
2187 "c.\"type\" AS \"TYPE_NAME\", " + 2187 "c.\"type\" AS \"TYPE_NAME\", " +
2188 "c.\"type_digits\" AS \"COLUMN_SIZE\", " + 2188 "c.\"type_digits\" AS \"COLUMN_SIZE\", " +
2189 "0 AS \"BUFFER_LENGTH\", " + 2189 "cast(0 as int) AS \"BUFFER_LENGTH\", " +
2190 "c.\"type_scale\" AS \"DECIMAL_DIGITS\", " + 2190 "c.\"type_scale\" AS \"DECIMAL_DIGITS\", " +
2191 "cast(CASE WHEN c.\"type\" IN ('decimal', 'numeric', 'sec_interval') THEN 10 " + 2191 "cast(CASE WHEN c.\"type\" IN ('decimal', 'numeric', 'sec_interval') THEN 10 " +
2192 "WHEN c.\"type\" IN ('int', 'smallint', 'tinyint', 'bigint', 'hugeint', 'float', 'real', 'double', 'oid', 'wrd') THEN 2 " + 2192 "WHEN c.\"type\" IN ('int', 'smallint', 'tinyint', 'bigint', 'hugeint', 'float', 'real', 'double', 'oid', 'wrd') THEN 2 " +
2193 "ELSE 0 END AS int) AS \"NUM_PREC_RADIX\", " + 2193 "ELSE 0 END AS int) AS \"NUM_PREC_RADIX\", " +
2194 "cast(CASE c.\"null\" WHEN true THEN ").append(ResultSetMetaData.columnNullable) 2194 "cast(CASE c.\"null\" WHEN true THEN ").append(ResultSetMetaData.columnNullable)
2458 final String table, 2458 final String table,
2459 final int scope, 2459 final int scope,
2460 final boolean nullable 2460 final boolean nullable
2461 ) throws SQLException 2461 ) throws SQLException
2462 { 2462 {
2463 // first find out if the table has a Primary Key, If it does, we should return only those columns
2464 boolean hasPK = false;
2465 ResultSet pkey = null;
2466 try {
2467 pkey = getPrimaryKeys(catalog, schema, table);
2468 if (pkey != null && pkey.next()) {
2469 hasPK = true;
2470 }
2471 } catch (SQLException e) {
2472 // ignore
2473 } finally {
2474 MonetConnection.closeResultsetStatement(pkey, null);
2475 }
2476
2463 final StringBuilder query = new StringBuilder(1500); 2477 final StringBuilder query = new StringBuilder(1500);
2464 query.append("SELECT cast(").append(DatabaseMetaData.bestRowSession).append(" AS smallint) AS \"SCOPE\", " + 2478 // Note: DISTINCT is needed to filter out possible duplicate column names from multiple unique constraints
2479 query.append("SELECT DISTINCT cast(").append(DatabaseMetaData.bestRowSession).append(" AS smallint) AS \"SCOPE\", " +
2465 "c.\"name\" AS \"COLUMN_NAME\", " + 2480 "c.\"name\" AS \"COLUMN_NAME\", " +
2466 "cast(").append(MonetDriver.getSQLTypeMap("c.\"type\"")).append(" AS int) AS \"DATA_TYPE\", " + 2481 "cast(").append(MonetDriver.getSQLTypeMap("c.\"type\"")).append(" AS int) AS \"DATA_TYPE\", " +
2467 "c.\"type\" AS \"TYPE_NAME\", " + 2482 "c.\"type\" AS \"TYPE_NAME\", " +
2468 "c.\"type_digits\" AS \"COLUMN_SIZE\", " + 2483 "c.\"type_digits\" AS \"COLUMN_SIZE\", " +
2469 "cast(0 as int) AS \"BUFFER_LENGTH\", " + 2484 "cast(0 as int) AS \"BUFFER_LENGTH\", " +
2472 "FROM \"sys\".\"keys\" k " + 2487 "FROM \"sys\".\"keys\" k " +
2473 "JOIN \"sys\".\"objects\" o ON k.\"id\" = o.\"id\" " + 2488 "JOIN \"sys\".\"objects\" o ON k.\"id\" = o.\"id\" " +
2474 "JOIN \"sys\".\"columns\" c ON (k.\"table_id\" = c.\"table_id\" AND o.\"name\" = c.\"name\") " + 2489 "JOIN \"sys\".\"columns\" c ON (k.\"table_id\" = c.\"table_id\" AND o.\"name\" = c.\"name\") " +
2475 "JOIN \"sys\".\"tables\" t ON k.\"table_id\" = t.\"id\" " + 2490 "JOIN \"sys\".\"tables\" t ON k.\"table_id\" = t.\"id\" " +
2476 "JOIN \"sys\".\"schemas\" s ON t.\"schema_id\" = s.\"id\" " + 2491 "JOIN \"sys\".\"schemas\" s ON t.\"schema_id\" = s.\"id\" " +
2477 "WHERE k.\"type\" IN (0, 1)"); // only primary keys (type = 0) and unique keys (type = 1), not fkeys (type = 2) 2492 "WHERE k.\"type\" = ").append(hasPK ? "0" : "1"); // the primary key (type = 0) or else any unique key (type = 1)
2493 // 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)
2478 2494
2479 if (catalog != null && !catalog.isEmpty()) { 2495 if (catalog != null && !catalog.isEmpty()) {
2480 // non-empty catalog selection. 2496 // non-empty catalog selection.
2481 // as we do not support catalogs this always results in no rows returned 2497 // as we do not support catalogs this always results in no rows returned
2482 query.append(" AND 1 = 0"); 2498 query.append(" AND 1 = 0");
2483 } else { 2499 } else {
2484 if (scope != DatabaseMetaData.bestRowSession 2500 if (scope == DatabaseMetaData.bestRowSession
2485 && scope != DatabaseMetaData.bestRowTransaction 2501 || scope == DatabaseMetaData.bestRowTransaction
2486 && scope != DatabaseMetaData.bestRowTemporary) { 2502 || scope == DatabaseMetaData.bestRowTemporary) {
2487 query.append(" AND 1 = 0");
2488 } else {
2489 if (schema != null && !schema.equals("%")) { 2503 if (schema != null && !schema.equals("%")) {
2490 query.append(" AND s.\"name\" ").append(composeMatchPart(schema)); 2504 query.append(" AND s.\"name\" ").append(composeMatchPart(schema));
2491 } 2505 }
2492 if (table != null && !table.equals("%")) { 2506 if (table != null && !table.equals("%")) {
2493 query.append(" AND t.\"name\" ").append(composeMatchPart(table)); 2507 query.append(" AND t.\"name\" ").append(composeMatchPart(table));
2494 } 2508 }
2495 if (!nullable) { 2509 if (!nullable) {
2496 query.append(" AND c.\"null\" = false"); 2510 query.append(" AND c.\"null\" = false");
2497 } 2511 }
2512 } else {
2513 query.append(" AND 1 = 0");
2498 } 2514 }
2499 } 2515 }
2500 2516
2501 query.append(" ORDER BY k.\"type\", c.\"name\""); 2517 query.append(" ORDER BY \"SCOPE\", o.\"nr\", \"COLUMN_NAME\"");
2502 2518
2503 return executeMetaDataQuery(query.toString()); 2519 return executeMetaDataQuery(query.toString());
2504 } 2520 }
2505 2521
2506 /** 2522 /**
3265 "t.\"sqlname\" AS \"TYPE_NAME\", " + 3281 "t.\"sqlname\" AS \"TYPE_NAME\", " +
3266 "CASE t.\"sqlname\"" + 3282 "CASE t.\"sqlname\"" +
3267 // next 4 UDTs are standard 3283 // next 4 UDTs are standard
3268 " WHEN 'inet' THEN 'nl.cwi.monetdb.jdbc.types.INET'" + 3284 " WHEN 'inet' THEN 'nl.cwi.monetdb.jdbc.types.INET'" +
3269 " WHEN 'json' THEN 'java.lang.String'" + 3285 " WHEN 'json' THEN 'java.lang.String'" +
3270 " WHEN 'url' THEN 'nl.cwi.monetdb.jdbc.types.URL'" + 3286 " WHEN 'url' THEN 'nl.cwi.monetdb.jdbc.types.URL'" +
3271 " WHEN 'uuid' THEN 'java.lang.String'" + 3287 " WHEN 'uuid' THEN 'java.lang.String'" +
3272 " ELSE 'java.lang.Object' END AS \"CLASS_NAME\", " + 3288 " ELSE 'java.lang.Object' END AS \"CLASS_NAME\", " +
3273 "cast(CASE WHEN t.\"sqlname\" IN ('inet', 'json', 'url', 'uuid') THEN ").append(Types.JAVA_OBJECT) 3289 "cast(CASE WHEN t.\"sqlname\" IN ('inet', 'json', 'url', 'uuid') THEN ").append(Types.JAVA_OBJECT)
3274 .append(" ELSE ").append(Types.STRUCT).append(" END AS int) AS \"DATA_TYPE\", " + 3290 .append(" ELSE ").append(Types.STRUCT).append(" END AS int) AS \"DATA_TYPE\", " +
3275 "t.\"systemname\" AS \"REMARKS\", " + 3291 "t.\"systemname\" AS \"REMARKS\", " +