Mercurial > hg > monetdb-java

comparison src/main/java/nl/cwi/monetdb/jdbc/MonetDatabaseMetaData.java @ 216:116b5a149fb4

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Optimise SQL query generation for cases where the WHERE clause already has a AND 1 = 0 condition. Exlude adding more AND ... conditions. Optimise java code by replacing "catalog.length() > 0)" into "!catalog.isEmpty()".
author Martin van Dinther <martin.van.dinther@monetdbsolutions.com>
date Thu, 19 Apr 2018 17:15:09 +0200 (2018-04-19)
parents 71b039bc2d99
children 7cbd20ff628c
comparison
equal deleted inserted replaced
215:71b039bc2d99 216:116b5a149fb4
409 @Override 409 @Override
410 public String getSQLKeywords() { 410 public String getSQLKeywords() {
411 String keywords = getConcatenatedStringFromQuery("SELECT \"keyword\" FROM \"sys\".\"keywords\" ORDER BY 1"); 411 String keywords = getConcatenatedStringFromQuery("SELECT \"keyword\" FROM \"sys\".\"keywords\" ORDER BY 1");
412 412
413 /* An old MonetDB server (pre Jul2015 release) will not have a table sys.keywords and return an empty String */ 413 /* An old MonetDB server (pre Jul2015 release) will not have a table sys.keywords and return an empty String */
414 return (keywords.length() > 0) ? keywords : 414 return (keywords.isEmpty()) ?
415 /* for old servers return static list (as returned in clients/odbc/driver/SQLGetInfo.c case SQL_KEYWORDS:) */ 415 /* for old servers return static list (as returned in clients/odbc/driver/SQLGetInfo.c case SQL_KEYWORDS:) */
416 "ADMIN,AFTER,AGGREGATE,ALWAYS,ASYMMETRIC,ATOMIC," + 416 "ADMIN,AFTER,AGGREGATE,ALWAYS,ASYMMETRIC,ATOMIC," +
417 "AUTO_INCREMENT,BEFORE,BIGINT,BIGSERIAL,BINARY,BLOB," + 417 "AUTO_INCREMENT,BEFORE,BIGINT,BIGSERIAL,BINARY,BLOB," +
418 "CALL,CHAIN,CLOB,COMMITTED,COPY,CORR,CUME_DIST," + 418 "CALL,CHAIN,CLOB,COMMITTED,COPY,CORR,CUME_DIST," +
419 "CURRENT_ROLE,CYCLE,DATABASE,DELIMITERS,DENSE_RANK," + 419 "CURRENT_ROLE,CYCLE,DATABASE,DELIMITERS,DENSE_RANK," +
429 "SERIAL,SERIALIZABLE,SIMPLE,START,STATEMENT,STDIN," + 429 "SERIAL,SERIALIZABLE,SIMPLE,START,STATEMENT,STDIN," +
430 "STDOUT,STREAM,STRING,SYMMETRIC,TIES,TINYINT,TRIGGER," + 430 "STDOUT,STREAM,STRING,SYMMETRIC,TIES,TINYINT,TRIGGER," +
431 "UNBOUNDED,UNCOMMITTED,UNENCRYPTED,WHILE,XMLAGG," + 431 "UNBOUNDED,UNCOMMITTED,UNENCRYPTED,WHILE,XMLAGG," +
432 "XMLATTRIBUTES,XMLCOMMENT,XMLCONCAT,XMLDOCUMENT," + 432 "XMLATTRIBUTES,XMLCOMMENT,XMLCONCAT,XMLDOCUMENT," +
433 "XMLELEMENT,XMLFOREST,XMLNAMESPACES,XMLPARSE,XMLPI," + 433 "XMLELEMENT,XMLFOREST,XMLNAMESPACES,XMLPARSE,XMLPI," +
434 "XMLQUERY,XMLSCHEMA,XMLTEXT,XMLVALIDATE"; 434 "XMLQUERY,XMLSCHEMA,XMLTEXT,XMLVALIDATE"
435 : keywords;
435 } 436 }
436 437
437 /** 438 /**
438 * Internal utility method getConcatenatedStringFromQuery(String query) 439 * Internal utility method getConcatenatedStringFromQuery(String query)
439 * args: query: SQL SELECT query. Only the output of the first column is concatenated. 440 * args: query: SQL SELECT query. Only the output of the first column is concatenated.
1723 query.append("LEFT OUTER JOIN \"sys\".\"comments\" ON (\"functions\".\"id\" = \"comments\".\"id\") "); 1724 query.append("LEFT OUTER JOIN \"sys\".\"comments\" ON (\"functions\".\"id\" = \"comments\".\"id\") ");
1724 } 1725 }
1725 // include procedures only (type = 2). Others will be returned via getFunctions() 1726 // include procedures only (type = 2). Others will be returned via getFunctions()
1726 query.append("WHERE \"functions\".\"type\" = 2"); 1727 query.append("WHERE \"functions\".\"type\" = 2");
1727 1728
1728 if (catalog != null && catalog.length() > 0) { 1729 if (catalog != null && !catalog.isEmpty()) {
1729 // none empty catalog selection. 1730 // none empty catalog selection.
1730 // as we do not support catalogs this always results in no rows returned 1731 // as we do not support catalogs this always results in no rows returned
1731 query.append(" AND 1 = 0"); 1732 query.append(" AND 1 = 0");
1733 } else {
1734 if (schemaPattern != null) {
1735 query.append(" AND \"schemas\".\"name\" ").append(composeMatchPart(schemaPattern));
1736 }
1737 if (procedureNamePattern != null) {
1738 query.append(" AND \"functions\".\"name\" ").append(composeMatchPart(procedureNamePattern));
1739 }
1732 } 1740 }
1733 if (schemaPattern != null) { 1741
1734 query.append(" AND \"schemas\".\"name\" ").append(composeMatchPart(schemaPattern));
1735 }
1736 if (procedureNamePattern != null) {
1737 query.append(" AND \"functions\".\"name\" ").append(composeMatchPart(procedureNamePattern));
1738 }
1739 query.append(" ORDER BY \"PROCEDURE_SCHEM\", \"PROCEDURE_NAME\", \"SPECIFIC_NAME\""); 1742 query.append(" ORDER BY \"PROCEDURE_SCHEM\", \"PROCEDURE_NAME\", \"SPECIFIC_NAME\"");
1740 1743
1741 return executeMetaDataQuery(query.toString()); 1744 return executeMetaDataQuery(query.toString());
1742 } 1745 }
1743 1746
1844 "JOIN \"sys\".\"functions\" ON (\"args\".\"func_id\" = \"functions\".\"id\") " + 1847 "JOIN \"sys\".\"functions\" ON (\"args\".\"func_id\" = \"functions\".\"id\") " +
1845 "JOIN \"sys\".\"schemas\" ON (\"functions\".\"schema_id\" = \"schemas\".\"id\") " + 1848 "JOIN \"sys\".\"schemas\" ON (\"functions\".\"schema_id\" = \"schemas\".\"id\") " +
1846 // include procedures only (type = 2). Others will be returned via getFunctionColumns() 1849 // include procedures only (type = 2). Others will be returned via getFunctionColumns()
1847 "WHERE \"functions\".\"type\" = 2"); 1850 "WHERE \"functions\".\"type\" = 2");
1848 1851
1849 if (catalog != null && catalog.length() > 0) { 1852 if (catalog != null && !catalog.isEmpty()) {
1850 // none empty catalog selection. 1853 // none empty catalog selection.
1851 // as we do not support catalogs this always results in no rows returned 1854 // as we do not support catalogs this always results in no rows returned
1852 query.append(" AND 1 = 0"); 1855 query.append(" AND 1 = 0");
1856 } else {
1857 if (schemaPattern != null) {
1858 query.append(" AND \"schemas\".\"name\" ").append(composeMatchPart(schemaPattern));
1859 }
1860 if (procedureNamePattern != null) {
1861 query.append(" AND \"functions\".\"name\" ").append(composeMatchPart(procedureNamePattern));
1862 }
1863 if (columnNamePattern != null) {
1864 query.append(" AND \"args\".\"name\" ").append(composeMatchPart(columnNamePattern));
1865 }
1853 } 1866 }
1854 if (schemaPattern != null) { 1867
1855 query.append(" AND \"schemas\".\"name\" ").append(composeMatchPart(schemaPattern));
1856 }
1857 if (procedureNamePattern != null) {
1858 query.append(" AND \"functions\".\"name\" ").append(composeMatchPart(procedureNamePattern));
1859 }
1860 if (columnNamePattern != null) {
1861 query.append(" AND \"args\".\"name\" ").append(composeMatchPart(columnNamePattern));
1862 }
1863 query.append(" ORDER BY \"PROCEDURE_SCHEM\", \"PROCEDURE_NAME\", \"SPECIFIC_NAME\", \"ORDINAL_POSITION\""); 1868 query.append(" ORDER BY \"PROCEDURE_SCHEM\", \"PROCEDURE_NAME\", \"SPECIFIC_NAME\", \"ORDINAL_POSITION\"");
1864 1869
1865 return executeMetaDataQuery(query.toString()); 1870 return executeMetaDataQuery(query.toString());
1866 } 1871 }
1867 1872
1993 query.append(" WHERE \"tables\".\"schema_id\" = \"schemas\".\"id\""); 1998 query.append(" WHERE \"tables\".\"schema_id\" = \"schemas\".\"id\"");
1994 if (!preJul2015) { 1999 if (!preJul2015) {
1995 query.append(" AND \"tables\".\"type\" = \"table_types\".\"table_type_id\""); 2000 query.append(" AND \"tables\".\"type\" = \"table_types\".\"table_type_id\"");
1996 } 2001 }
1997 2002
1998 if (catalog != null && catalog.length() > 0) { 2003 if (catalog != null && !catalog.isEmpty()) {
1999 // none empty catalog selection. 2004 // none empty catalog selection.
2000 // as we do not support catalogs this always results in no rows returned 2005 // as we do not support catalogs this always results in no rows returned
2001 query.append(" AND 1 = 0"); 2006 query.append(" AND 1 = 0");
2007 } else {
2008 if (schemaPattern != null) {
2009 query.append(" AND \"schemas\".\"name\" ").append(composeMatchPart(schemaPattern));
2010 }
2011 if (tableNamePattern != null) {
2012 query.append(" AND \"tables\".\"name\" ").append(composeMatchPart(tableNamePattern));
2013 }
2002 } 2014 }
2003 if (schemaPattern != null) { 2015
2004 query.append(" AND \"schemas\".\"name\" ").append(composeMatchPart(schemaPattern));
2005 }
2006 if (tableNamePattern != null) {
2007 query.append(" AND \"tables\".\"name\" ").append(composeMatchPart(tableNamePattern));
2008 }
2009 if (types != null && types.length > 0) { 2016 if (types != null && types.length > 0) {
2010 if (preJul2015) { 2017 if (preJul2015) {
2011 query.append(") AS \"getTables\" WHERE \"TABLE_TYPE\" IN ("); 2018 query.append(") AS \"getTables\" WHERE \"TABLE_TYPE\" IN (");
2012 } else { 2019 } else {
2013 query.append(" AND \"table_types\".\"table_type_name\" IN ("); 2020 query.append(" AND \"table_types\".\"table_type_name\" IN (");
2054 StringBuilder query = new StringBuilder(170); 2061 StringBuilder query = new StringBuilder(170);
2055 query.append("SELECT \"name\" AS \"TABLE_SCHEM\", " + 2062 query.append("SELECT \"name\" AS \"TABLE_SCHEM\", " +
2056 "cast(null as char(1)) AS \"TABLE_CATALOG\" " + 2063 "cast(null as char(1)) AS \"TABLE_CATALOG\" " +
2057 "FROM \"sys\".\"schemas\""); 2064 "FROM \"sys\".\"schemas\"");
2058 2065
2059 if (catalog != null && catalog.length() > 0) { 2066 if (catalog != null && !catalog.isEmpty()) {
2060 // none empty catalog selection. 2067 // none empty catalog selection.
2061 // as we do not support catalogs this always results in no rows returned 2068 // as we do not support catalogs this always results in no rows returned
2062 query.append(" WHERE 1 = 0"); 2069 query.append(" WHERE 1 = 0");
2063 } else { 2070 } else {
2064 if (schemaPattern != null) { 2071 if (schemaPattern != null) {
2239 query.append(", \"sys\".\"tables\"" + 2246 query.append(", \"sys\".\"tables\"" +
2240 ", \"sys\".\"schemas\" " + 2247 ", \"sys\".\"schemas\" " +
2241 "WHERE \"columns\".\"table_id\" = \"tables\".\"id\"" + 2248 "WHERE \"columns\".\"table_id\" = \"tables\".\"id\"" +
2242 " AND \"tables\".\"schema_id\" = \"schemas\".\"id\""); 2249 " AND \"tables\".\"schema_id\" = \"schemas\".\"id\"");
2243 2250
2244 if (catalog != null && catalog.length() > 0) { 2251 if (catalog != null && !catalog.isEmpty()) {
2245 // none empty catalog selection. 2252 // none empty catalog selection.
2246 // as we do not support catalogs this always results in no rows returned 2253 // as we do not support catalogs this always results in no rows returned
2247 query.append(" AND 1 = 0"); 2254 query.append(" AND 1 = 0");
2248 } 2255 } else {
2249 if (schemaPattern != null) { 2256 if (schemaPattern != null) {
2250 query.append(" AND \"schemas\".\"name\" ").append(composeMatchPart(schemaPattern)); 2257 query.append(" AND \"schemas\".\"name\" ").append(composeMatchPart(schemaPattern));
2251 } 2258 }
2252 if (tableNamePattern != null) { 2259 if (tableNamePattern != null) {
2253 query.append(" AND \"tables\".\"name\" ").append(composeMatchPart(tableNamePattern)); 2260 query.append(" AND \"tables\".\"name\" ").append(composeMatchPart(tableNamePattern));
2254 } 2261 }
2255 if (columnNamePattern != null) { 2262 if (columnNamePattern != null) {
2256 query.append(" AND \"columns\".\"name\" ").append(composeMatchPart(columnNamePattern)); 2263 query.append(" AND \"columns\".\"name\" ").append(composeMatchPart(columnNamePattern));
2264 }
2257 } 2265 }
2258 2266
2259 query.append(" ORDER BY \"TABLE_SCHEM\", \"TABLE_NAME\", \"ORDINAL_POSITION\""); 2267 query.append(" ORDER BY \"TABLE_SCHEM\", \"TABLE_NAME\", \"ORDINAL_POSITION\"");
2260 2268
2261 return executeMetaDataQuery(query.toString()); 2269 return executeMetaDataQuery(query.toString());
2329 "AND \"columns\".\"table_id\" = \"tables\".\"id\" " + 2337 "AND \"columns\".\"table_id\" = \"tables\".\"id\" " +
2330 "AND \"tables\".\"schema_id\" = \"schemas\".\"id\" " + 2338 "AND \"tables\".\"schema_id\" = \"schemas\".\"id\" " +
2331 "AND \"privileges\".\"auth_id\" = \"grantees\".\"id\" " + 2339 "AND \"privileges\".\"auth_id\" = \"grantees\".\"id\" " +
2332 "AND \"privileges\".\"grantor\" = \"grantors\".\"id\""); 2340 "AND \"privileges\".\"grantor\" = \"grantors\".\"id\"");
2333 2341
2334 if (catalog != null && catalog.length() > 0) { 2342 if (catalog != null && !catalog.isEmpty()) {
2335 // none empty catalog selection. 2343 // none empty catalog selection.
2336 // as we do not support catalogs this always results in no rows returned 2344 // as we do not support catalogs this always results in no rows returned
2337 query.append(" AND 1 = 0"); 2345 query.append(" AND 1 = 0");
2338 } 2346 } else {
2339 if (schemaPattern != null) { 2347 if (schemaPattern != null) {
2340 query.append(" AND \"schemas\".\"name\" ").append(composeMatchPart(schemaPattern)); 2348 query.append(" AND \"schemas\".\"name\" ").append(composeMatchPart(schemaPattern));
2341 } 2349 }
2342 if (tableNamePattern != null) { 2350 if (tableNamePattern != null) {
2343 query.append(" AND \"tables\".\"name\" ").append(composeMatchPart(tableNamePattern)); 2351 query.append(" AND \"tables\".\"name\" ").append(composeMatchPart(tableNamePattern));
2344 } 2352 }
2345 if (columnNamePattern != null) { 2353 if (columnNamePattern != null) {
2346 query.append(" AND \"columns\".\"name\" ").append(composeMatchPart(columnNamePattern)); 2354 query.append(" AND \"columns\".\"name\" ").append(composeMatchPart(columnNamePattern));
2355 }
2347 } 2356 }
2348 2357
2349 query.append(" ORDER BY \"TABLE_SCHEM\", \"TABLE_NAME\", \"COLUMN_NAME\", \"PRIVILEGE\""); 2358 query.append(" ORDER BY \"TABLE_SCHEM\", \"TABLE_NAME\", \"COLUMN_NAME\", \"PRIVILEGE\"");
2350 2359
2351 return executeMetaDataQuery(query.toString()); 2360 return executeMetaDataQuery(query.toString());
2414 "WHERE \"privileges\".\"obj_id\" = \"tables\".\"id\" " + 2423 "WHERE \"privileges\".\"obj_id\" = \"tables\".\"id\" " +
2415 "AND \"tables\".\"schema_id\" = \"schemas\".\"id\" " + 2424 "AND \"tables\".\"schema_id\" = \"schemas\".\"id\" " +
2416 "AND \"privileges\".\"auth_id\" = \"grantees\".\"id\" " + 2425 "AND \"privileges\".\"auth_id\" = \"grantees\".\"id\" " +
2417 "AND \"privileges\".\"grantor\" = \"grantors\".\"id\""); 2426 "AND \"privileges\".\"grantor\" = \"grantors\".\"id\"");
2418 2427
2419 if (catalog != null && catalog.length() > 0) { 2428 if (catalog != null && !catalog.isEmpty()) {
2420 // none empty catalog selection. 2429 // none empty catalog selection.
2421 // as we do not support catalogs this always results in no rows returned 2430 // as we do not support catalogs this always results in no rows returned
2422 query.append(" AND 1 = 0"); 2431 query.append(" AND 1 = 0");
2423 } 2432 } else {
2424 if (schemaPattern != null) { 2433 if (schemaPattern != null) {
2425 query.append(" AND \"schemas\".\"name\" ").append(composeMatchPart(schemaPattern)); 2434 query.append(" AND \"schemas\".\"name\" ").append(composeMatchPart(schemaPattern));
2426 } 2435 }
2427 if (tableNamePattern != null) { 2436 if (tableNamePattern != null) {
2428 query.append(" AND \"tables\".\"name\" ").append(composeMatchPart(tableNamePattern)); 2437 query.append(" AND \"tables\".\"name\" ").append(composeMatchPart(tableNamePattern));
2438 }
2429 } 2439 }
2430 2440
2431 query.append(" ORDER BY \"TABLE_SCHEM\", \"TABLE_NAME\", \"PRIVILEGE\""); 2441 query.append(" ORDER BY \"TABLE_SCHEM\", \"TABLE_NAME\", \"PRIVILEGE\"");
2432 2442
2433 return executeMetaDataQuery(query.toString()); 2443 return executeMetaDataQuery(query.toString());
2496 "AND \"keys\".\"table_id\" = \"columns\".\"table_id\" " + 2506 "AND \"keys\".\"table_id\" = \"columns\".\"table_id\" " +
2497 "AND \"objects\".\"name\" = \"columns\".\"name\" " + 2507 "AND \"objects\".\"name\" = \"columns\".\"name\" " +
2498 "AND \"tables\".\"schema_id\" = \"schemas\".\"id\" " + 2508 "AND \"tables\".\"schema_id\" = \"schemas\".\"id\" " +
2499 "AND \"keys\".\"type\" IN (0, 1)"); // only primary keys (type = 0) and unique keys (type = 1), not fkeys (type = 2) 2509 "AND \"keys\".\"type\" IN (0, 1)"); // only primary keys (type = 0) and unique keys (type = 1), not fkeys (type = 2)
2500 2510
2501 if (catalog != null && catalog.length() > 0) { 2511 if (catalog != null && !catalog.isEmpty()) {
2502 // none empty catalog selection. 2512 // none empty catalog selection.
2503 // as we do not support catalogs this always results in no rows returned 2513 // as we do not support catalogs this always results in no rows returned
2504 query.append(" AND 1 = 0"); 2514 query.append(" AND 1 = 0");
2505 } 2515 } else {
2506 if (schema != null) { 2516 if (scope != DatabaseMetaData.bestRowSession
2507 query.append(" AND \"schemas\".\"name\" ").append(composeMatchPart(schema)); 2517 && scope != DatabaseMetaData.bestRowTransaction
2508 } 2518 && scope != DatabaseMetaData.bestRowTemporary) {
2509 if (table != null) { 2519 query.append(" AND 1 = 0");
2510 query.append(" AND \"tables\".\"name\" ").append(composeMatchPart(table)); 2520 } else {
2511 } 2521 if (schema != null) {
2512 if (scope != DatabaseMetaData.bestRowSession && scope != DatabaseMetaData.bestRowTransaction && scope != DatabaseMetaData.bestRowTemporary) { 2522 query.append(" AND \"schemas\".\"name\" ").append(composeMatchPart(schema));
2513 query.append(" AND 1 = 0"); 2523 }
2514 } 2524 if (table != null) {
2515 if (!nullable) { 2525 query.append(" AND \"tables\".\"name\" ").append(composeMatchPart(table));
2516 query.append(" AND \"columns\".\"null\" = false"); 2526 }
2527 if (!nullable) {
2528 query.append(" AND \"columns\".\"null\" = false");
2529 }
2530 }
2517 } 2531 }
2518 2532
2519 query.append(" ORDER BY \"keys\".\"type\", \"columns\".\"name\""); 2533 query.append(" ORDER BY \"keys\".\"type\", \"columns\".\"name\"");
2520 2534
2521 return executeMetaDataQuery(query.toString()); 2535 return executeMetaDataQuery(query.toString());
2612 "WHERE \"keys\".\"id\" = \"objects\".\"id\" " + 2626 "WHERE \"keys\".\"id\" = \"objects\".\"id\" " +
2613 "AND \"keys\".\"table_id\" = \"tables\".\"id\" " + 2627 "AND \"keys\".\"table_id\" = \"tables\".\"id\" " +
2614 "AND \"tables\".\"schema_id\" = \"schemas\".\"id\" " + 2628 "AND \"tables\".\"schema_id\" = \"schemas\".\"id\" " +
2615 "AND \"keys\".\"type\" = 0"); 2629 "AND \"keys\".\"type\" = 0");
2616 2630
2617 if (catalog != null && catalog.length() > 0) { 2631 if (catalog != null && !catalog.isEmpty()) {
2618 // none empty catalog selection. 2632 // none empty catalog selection.
2619 // as we do not support catalogs this always results in no rows returned 2633 // as we do not support catalogs this always results in no rows returned
2620 query.append(" AND 1 = 0"); 2634 query.append(" AND 1 = 0");
2621 } 2635 } else {
2622 if (schema != null) { 2636 if (schema != null) {
2623 query.append(" AND \"schemas\".\"name\" ").append(composeMatchPart(schema)); 2637 query.append(" AND \"schemas\".\"name\" ").append(composeMatchPart(schema));
2624 } 2638 }
2625 if (table != null) { 2639 if (table != null) {
2626 query.append(" AND \"tables\".\"name\" ").append(composeMatchPart(table)); 2640 query.append(" AND \"tables\".\"name\" ").append(composeMatchPart(table));
2641 }
2627 } 2642 }
2628 2643
2629 query.append(" ORDER BY \"TABLE_SCHEM\", \"TABLE_NAME\", \"COLUMN_NAME\""); 2644 query.append(" ORDER BY \"TABLE_SCHEM\", \"TABLE_NAME\", \"COLUMN_NAME\"");
2630 2645
2631 return executeMetaDataQuery(query.toString()); 2646 return executeMetaDataQuery(query.toString());
2730 throws SQLException 2745 throws SQLException
2731 { 2746 {
2732 StringBuilder query = new StringBuilder(keyQuery.length() + 250); 2747 StringBuilder query = new StringBuilder(keyQuery.length() + 250);
2733 query.append(keyQuery); 2748 query.append(keyQuery);
2734 2749
2735 if (catalog != null && catalog.length() > 0) { 2750 if (catalog != null && !catalog.isEmpty()) {
2736 // none empty catalog selection. 2751 // none empty catalog selection.
2737 // as we do not support catalogs this always results in no rows returned 2752 // as we do not support catalogs this always results in no rows returned
2738 query.append(" AND 1 = 0"); 2753 query.append(" AND 1 = 0");
2739 } 2754 } else {
2740 if (schema != null) { 2755 if (schema != null) {
2741 query.append(" AND \"fkschema\".\"name\" ").append(composeMatchPart(schema)); 2756 query.append(" AND \"fkschema\".\"name\" ").append(composeMatchPart(schema));
2742 } 2757 }
2743 if (table != null) { 2758 if (table != null) {
2744 query.append(" AND \"fktable\".\"name\" ").append(composeMatchPart(table)); 2759 query.append(" AND \"fktable\".\"name\" ").append(composeMatchPart(table));
2760 }
2745 } 2761 }
2746 2762
2747 query.append(" ORDER BY \"PKTABLE_SCHEM\", \"PKTABLE_NAME\", \"PK_NAME\", \"KEY_SEQ\""); 2763 query.append(" ORDER BY \"PKTABLE_SCHEM\", \"PKTABLE_NAME\", \"PK_NAME\", \"KEY_SEQ\"");
2748 2764
2749 return executeMetaDataQuery(query.toString()); 2765 return executeMetaDataQuery(query.toString());
2813 throws SQLException 2829 throws SQLException
2814 { 2830 {
2815 StringBuilder query = new StringBuilder(keyQuery.length() + 250); 2831 StringBuilder query = new StringBuilder(keyQuery.length() + 250);
2816 query.append(keyQuery); 2832 query.append(keyQuery);
2817 2833
2818 if (catalog != null && catalog.length() > 0) { 2834 if (catalog != null && !catalog.isEmpty()) {
2819 // none empty catalog selection. 2835 // none empty catalog selection.
2820 // as we do not support catalogs this always results in no rows returned 2836 // as we do not support catalogs this always results in no rows returned
2821 query.append(" AND 1 = 0"); 2837 query.append(" AND 1 = 0");
2822 } 2838 } else {
2823 if (schema != null) { 2839 if (schema != null) {
2824 query.append(" AND \"pkschema\".\"name\" ").append(composeMatchPart(schema)); 2840 query.append(" AND \"pkschema\".\"name\" ").append(composeMatchPart(schema));
2825 } 2841 }
2826 if (table != null) { 2842 if (table != null) {
2827 query.append(" AND \"pktable\".\"name\" ").append(composeMatchPart(table)); 2843 query.append(" AND \"pktable\".\"name\" ").append(composeMatchPart(table));
2844 }
2828 } 2845 }
2829 2846
2830 query.append(" ORDER BY \"FKTABLE_SCHEM\", \"FKTABLE_NAME\", \"FK_NAME\", \"KEY_SEQ\""); 2847 query.append(" ORDER BY \"FKTABLE_SCHEM\", \"FKTABLE_NAME\", \"FK_NAME\", \"KEY_SEQ\"");
2831 2848
2832 return executeMetaDataQuery(query.toString()); 2849 return executeMetaDataQuery(query.toString());
2908 ) throws SQLException 2925 ) throws SQLException
2909 { 2926 {
2910 StringBuilder query = new StringBuilder(keyQuery.length() + 350); 2927 StringBuilder query = new StringBuilder(keyQuery.length() + 350);
2911 query.append(keyQuery); 2928 query.append(keyQuery);
2912 2929
2913 if (pcatalog != null && pcatalog.length() > 0) { 2930 if ((pcatalog != null && !pcatalog.isEmpty())
2931 || (fcatalog != null && !fcatalog.isEmpty())) {
2914 // none empty catalog selection. 2932 // none empty catalog selection.
2915 // as we do not support catalogs this always results in no rows returned 2933 // as we do not support catalogs this always results in no rows returned
2916 query.append(" AND 1 = 0"); 2934 query.append(" AND 1 = 0");
2917 } 2935 } else {
2918 if (pschema != null) { 2936 if (pschema != null) {
2919 query.append(" AND \"pkschema\".\"name\" ").append(composeMatchPart(pschema)); 2937 query.append(" AND \"pkschema\".\"name\" ").append(composeMatchPart(pschema));
2920 } 2938 }
2921 if (ptable != null) { 2939 if (ptable != null) {
2922 query.append(" AND \"pktable\".\"name\" ").append(composeMatchPart(ptable)); 2940 query.append(" AND \"pktable\".\"name\" ").append(composeMatchPart(ptable));
2923 } 2941 }
2924 2942
2925 if (fcatalog != null && fcatalog.length() > 0) { 2943 if (fschema != null) {
2926 // none empty catalog selection. 2944 query.append(" AND \"fkschema\".\"name\" ").append(composeMatchPart(fschema));
2927 // as we do not support catalogs this always results in no rows returned 2945 }
2928 query.append(" AND 1 = 0"); 2946 if (ftable != null) {
2929 } 2947 query.append(" AND \"fktable\".\"name\" ").append(composeMatchPart(ftable));
2930 if (fschema != null) { 2948 }
2931 query.append(" AND \"fkschema\".\"name\" ").append(composeMatchPart(fschema));
2932 }
2933 if (ftable != null) {
2934 query.append(" AND \"fktable\".\"name\" ").append(composeMatchPart(ftable));
2935 } 2949 }
2936 2950
2937 query.append(" ORDER BY \"FKTABLE_SCHEM\", \"FKTABLE_NAME\", \"FK_NAME\", \"KEY_SEQ\""); 2951 query.append(" ORDER BY \"FKTABLE_SCHEM\", \"FKTABLE_NAME\", \"FK_NAME\", \"KEY_SEQ\"");
2938 2952
2939 return executeMetaDataQuery(query.toString()); 2953 return executeMetaDataQuery(query.toString());
3087 boolean approximate 3101 boolean approximate
3088 ) throws SQLException 3102 ) throws SQLException
3089 { 3103 {
3090 String table_row_count = "0"; 3104 String table_row_count = "0";
3091 3105
3092 if (!approximate && schema != null && table != null && schema.length() > 0 && table.length() > 0) { 3106 if (!approximate && schema != null && table != null && !schema.isEmpty() && !table.isEmpty()) {
3093 // we need the exact cardinality for one specific fully qualified table 3107 // we need the exact cardinality for one specific fully qualified table
3094 ResultSet count = null; 3108 ResultSet count = null;
3095 try { 3109 try {
3096 count = executeMetaDataQuery("SELECT COUNT(*) FROM \"" + schema + "\".\"" + table + "\""); 3110 count = executeMetaDataQuery("SELECT COUNT(*) FROM \"" + schema + "\".\"" + table + "\"");
3097 if (count != null && count.next()) { 3111 if (count != null && count.next()) {
3098 String count_value = count.getString(1); 3112 String count_value = count.getString(1);
3099 if (count_value != null && count_value.length() > 0) 3113 if (count_value != null && !count_value.isEmpty())
3100 table_row_count = count_value; 3114 table_row_count = count_value;
3101 } 3115 }
3102 } catch (SQLException e) { 3116 } catch (SQLException e) {
3103 // ignore 3117 // ignore
3104 } finally { 3118 } finally {
3135 "AND \"idxs\".\"id\" = \"objects\".\"id\" " + 3149 "AND \"idxs\".\"id\" = \"objects\".\"id\" " +
3136 "AND \"tables\".\"id\" = \"columns\".\"table_id\" " + 3150 "AND \"tables\".\"id\" = \"columns\".\"table_id\" " +
3137 "AND \"objects\".\"name\" = \"columns\".\"name\" " + 3151 "AND \"objects\".\"name\" = \"columns\".\"name\" " +
3138 "AND (\"keys\".\"type\" IS NULL OR \"keys\".\"type\" = 1)"); 3152 "AND (\"keys\".\"type\" IS NULL OR \"keys\".\"type\" = 1)");
3139 3153
3140 if (catalog != null && catalog.length() > 0) { 3154 if (catalog != null && !catalog.isEmpty()) {
3141 // none empty catalog selection. 3155 // none empty catalog selection.
3142 // as we do not support catalogs this always results in no rows returned 3156 // as we do not support catalogs this always results in no rows returned
3143 query.append(" AND 1 = 0"); 3157 query.append(" AND 1 = 0");
3158 } else {
3159 if (schema != null) {
3160 query.append(" AND \"schemas\".\"name\" ").append(composeMatchPart(schema));
3161 }
3162 if (table != null) {
3163 query.append(" AND \"tables\".\"name\" ").append(composeMatchPart(table));
3164 }
3165 if (unique) {
3166 query.append(" AND \"keys\".\"name\" IS NOT NULL");
3167 }
3144 } 3168 }
3145 if (schema != null) { 3169
3146 query.append(" AND \"schemas\".\"name\" ").append(composeMatchPart(schema));
3147 }
3148 if (table != null) {
3149 query.append(" AND \"tables\".\"name\" ").append(composeMatchPart(table));
3150 }
3151 if (unique) {
3152 query.append(" AND \"keys\".\"name\" IS NOT NULL");
3153 }
3154 query.append(" ORDER BY \"NON_UNIQUE\", \"TYPE\", \"INDEX_NAME\", \"ORDINAL_POSITION\""); 3170 query.append(" ORDER BY \"NON_UNIQUE\", \"TYPE\", \"INDEX_NAME\", \"ORDINAL_POSITION\"");
3155 3171
3156 return executeMetaDataQuery(query.toString()); 3172 return executeMetaDataQuery(query.toString());
3157 } 3173 }
3158 3174
3301 "cast(null as smallint) AS \"BASE_TYPE\" " + 3317 "cast(null as smallint) AS \"BASE_TYPE\" " +
3302 "FROM \"sys\".\"types\" JOIN \"sys\".\"schemas\" ON \"types\".\"schema_id\" = \"schemas\".\"id\" " + 3318 "FROM \"sys\".\"types\" JOIN \"sys\".\"schemas\" ON \"types\".\"schema_id\" = \"schemas\".\"id\" " +
3303 // exclude the built-in types (I assume they always have id <= 99 and eclass < 15) 3319 // exclude the built-in types (I assume they always have id <= 99 and eclass < 15)
3304 "WHERE \"types\".\"id\" > 99 AND \"types\".\"eclass\" >= 15"); 3320 "WHERE \"types\".\"id\" > 99 AND \"types\".\"eclass\" >= 15");
3305 3321
3306 if (catalog != null && catalog.length() > 0) { 3322 if (catalog != null && !catalog.isEmpty()) {
3307 // none empty catalog selection. 3323 // none empty catalog selection.
3308 // as we do not support catalogs this always results in no rows returned 3324 // as we do not support catalogs this always results in no rows returned
3309 query.append(" AND 1 = 0"); 3325 query.append(" AND 1 = 0");
3310 } 3326 } else {
3311 if (schemaPattern != null) { 3327 if (schemaPattern != null) {
3312 query.append(" AND \"schemas\".\"name\" ").append(composeMatchPart(schemaPattern)); 3328 query.append(" AND \"schemas\".\"name\" ").append(composeMatchPart(schemaPattern));
3313 } 3329 }
3314 if (typeNamePattern != null) { 3330 if (typeNamePattern != null) {
3315 query.append(" AND \"types\".\"sqlname\" ").append(composeMatchPart(typeNamePattern)); 3331 query.append(" AND \"types\".\"sqlname\" ").append(composeMatchPart(typeNamePattern));
3332 }
3316 } 3333 }
3317 3334
3318 if (types != null && types.length > 0) { 3335 if (types != null && types.length > 0) {
3319 query.append(") AS getUDTs WHERE \"DATA_TYPE\" IN ("); 3336 query.append(") AS getUDTs WHERE \"DATA_TYPE\" IN (");
3320 for (int i = 0; i < types.length; i++) { 3337 for (int i = 0; i < types.length; i++) {
3323 } 3340 }
3324 query.append(types[i]); 3341 query.append(types[i]);
3325 } 3342 }
3326 query.append(")"); 3343 query.append(")");
3327 } 3344 }
3345
3328 query.append(" ORDER BY \"DATA_TYPE\", \"TYPE_SCHEM\", \"TYPE_NAME\""); 3346 query.append(" ORDER BY \"DATA_TYPE\", \"TYPE_SCHEM\", \"TYPE_NAME\"");
3329 3347
3330 return executeMetaDataQuery(query.toString()); 3348 return executeMetaDataQuery(query.toString());
3331 } 3349 }
3332 3350
3899 query.append("LEFT OUTER JOIN \"sys\".\"comments\" ON (\"functions\".\"id\" = \"comments\".\"id\") "); 3917 query.append("LEFT OUTER JOIN \"sys\".\"comments\" ON (\"functions\".\"id\" = \"comments\".\"id\") ");
3900 } 3918 }
3901 // only functions, so exclude procedures (type = 2). Those will to be returned via getProcedures() 3919 // only functions, so exclude procedures (type = 2). Those will to be returned via getProcedures()
3902 query.append("WHERE \"functions\".\"type\" <> 2"); 3920 query.append("WHERE \"functions\".\"type\" <> 2");
3903 3921
3904 if (catalog != null && catalog.length() > 0) { 3922 if (catalog != null && !catalog.isEmpty()) {
3905 // none empty catalog selection. 3923 // none empty catalog selection.
3906 // as we do not support catalogs this always results in no rows returned 3924 // as we do not support catalogs this always results in no rows returned
3907 query.append(" AND 1 = 0"); 3925 query.append(" AND 1 = 0");
3908 } 3926 } else {
3909 if (schemaPattern != null) { 3927 if (schemaPattern != null) {
3910 query.append(" AND \"schemas\".\"name\" ").append(composeMatchPart(schemaPattern)); 3928 query.append(" AND \"schemas\".\"name\" ").append(composeMatchPart(schemaPattern));
3911 } 3929 }
3912 if (functionNamePattern != null) { 3930 if (functionNamePattern != null) {
3913 query.append(" AND \"functions\".\"name\" ").append(composeMatchPart(functionNamePattern)); 3931 query.append(" AND \"functions\".\"name\" ").append(composeMatchPart(functionNamePattern));
3932 }
3914 } 3933 }
3915 3934
3916 query.append(" ORDER BY \"FUNCTION_SCHEM\", \"FUNCTION_NAME\", \"SPECIFIC_NAME\""); 3935 query.append(" ORDER BY \"FUNCTION_SCHEM\", \"FUNCTION_NAME\", \"SPECIFIC_NAME\"");
3917 3936
3918 return executeMetaDataQuery(query.toString()); 3937 return executeMetaDataQuery(query.toString());
4014 "JOIN \"sys\".\"functions\" ON (\"args\".\"func_id\" = \"functions\".\"id\") " + 4033 "JOIN \"sys\".\"functions\" ON (\"args\".\"func_id\" = \"functions\".\"id\") " +
4015 "JOIN \"sys\".\"schemas\" ON (\"functions\".\"schema_id\" = \"schemas\".\"id\") " + 4034 "JOIN \"sys\".\"schemas\" ON (\"functions\".\"schema_id\" = \"schemas\".\"id\") " +
4016 // only functions, so exclude procedures (type = 2). Those will to be returned via getProcedureColumns() 4035 // only functions, so exclude procedures (type = 2). Those will to be returned via getProcedureColumns()
4017 "WHERE \"functions\".\"type\" <> 2"); 4036 "WHERE \"functions\".\"type\" <> 2");
4018 4037
4019 if (catalog != null && catalog.length() > 0) { 4038 if (catalog != null && !catalog.isEmpty()) {
4020 // none empty catalog selection. 4039 // none empty catalog selection.
4021 // as we do not support catalogs this always results in no rows returned 4040 // as we do not support catalogs this always results in no rows returned
4022 query.append(" AND 1 = 0"); 4041 query.append(" AND 1 = 0");
4042 } else {
4043 if (schemaPattern != null) {
4044 query.append(" AND \"schemas\".\"name\" ").append(composeMatchPart(schemaPattern));
4045 }
4046 if (functionNamePattern != null) {
4047 query.append(" AND \"functions\".\"name\" ").append(composeMatchPart(functionNamePattern));
4048 }
4049 if (columnNamePattern != null) {
4050 query.append(" AND \"args\".\"name\" ").append(composeMatchPart(columnNamePattern));
4051 }
4023 } 4052 }
4024 if (schemaPattern != null) { 4053
4025 query.append(" AND \"schemas\".\"name\" ").append(composeMatchPart(schemaPattern));
4026 }
4027 if (functionNamePattern != null) {
4028 query.append(" AND \"functions\".\"name\" ").append(composeMatchPart(functionNamePattern));
4029 }
4030 if (columnNamePattern != null) {
4031 query.append(" AND \"args\".\"name\" ").append(composeMatchPart(columnNamePattern));
4032 }
4033 query.append(" ORDER BY \"FUNCTION_SCHEM\", \"FUNCTION_NAME\", \"SPECIFIC_NAME\", \"ORDINAL_POSITION\""); 4054 query.append(" ORDER BY \"FUNCTION_SCHEM\", \"FUNCTION_NAME\", \"SPECIFIC_NAME\", \"ORDINAL_POSITION\"");
4034 4055
4035 return executeMetaDataQuery(query.toString()); 4056 return executeMetaDataQuery(query.toString());
4036 } 4057 }
4037 4058