Mercurial > hg > monetdb-java

comparison src/main/java/org/monetdb/jdbc/MonetDatabaseMetaData.java @ 767:1547843b3a9b

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Removed code to support old MonetDB servers Oct2014 or older. Those old servers did not yet have the system tables: sys.keywords and sys.table_types which are introduced in Jul2015 release. Those system tables are used by MonetDatabaseMetaData methods: getSQLKeywords(), getTableTypes() and getTables(). These 3 methods will now fail when used with those very old MonetDB servers.
author Martin van Dinther <martin.van.dinther@monetdbsolutions.com>
date Thu, 06 Jul 2023 12:57:02 +0200 (22 months ago)
parents e1389c0ffb7c
children 71d32f1a4d60
comparison
equal deleted inserted replaced
766:32dd608f9169 767:1547843b3a9b
334 * 334 *
335 * @return a comma separated list of MonetDB keywords that are not also SQL:2003 keywords 335 * @return a comma separated list of MonetDB keywords that are not also SQL:2003 keywords
336 */ 336 */
337 @Override 337 @Override
338 public String getSQLKeywords() { 338 public String getSQLKeywords() {
339 final String keywords = getConcatenatedStringFromQuery( 339 return getConcatenatedStringFromQuery(
340 "SELECT \"keyword\" FROM \"sys\".\"keywords\" " + 340 "SELECT \"keyword\" FROM \"sys\".\"keywords\" " +
341 // exclude all SQL:2003 keywords 341 // exclude all SQL:2003 keywords
342 "WHERE \"keyword\" NOT IN (" + 342 "WHERE \"keyword\" NOT IN (" +
343 "'ABS','ALL','ALLOCATE','ALTER','AND','ANY','ARE','ARRAY','AS'," + 343 "'ABS','ALL','ALLOCATE','ALTER','AND','ANY','ARE','ARRAY','AS'," +
344 "'ASENSITIVE','ASYMMETRIC','AT','ATOMIC','AUTHORIZATION','AVG'," + 344 "'ASENSITIVE','ASYMMETRIC','AT','ATOMIC','AUTHORIZATION','AVG'," +
374 "'TO','TRAILING','TRANSLATE','TRANSLATION','TREAT','TRIGGER','TRIM','TRUE'," + 374 "'TO','TRAILING','TRANSLATE','TRANSLATION','TREAT','TRIGGER','TRIM','TRUE'," +
375 "'UESCAPE','UNION','UNIQUE','UNKNOWN','UNNEST','UPDATE','UPPER','USER','USING'," + 375 "'UESCAPE','UNION','UNIQUE','UNKNOWN','UNNEST','UPDATE','UPPER','USER','USING'," +
376 "'VALUE','VALUES','VARCHAR','VARYING','VAR_POP','VAR_SAMP'," + 376 "'VALUE','VALUES','VARCHAR','VARYING','VAR_POP','VAR_SAMP'," +
377 "'WHEN','WHENEVER','WHERE','WIDTH_BUCKET','WINDOW','WITH','WITHIN','WITHOUT','YEAR'" + 377 "'WHEN','WHENEVER','WHERE','WIDTH_BUCKET','WINDOW','WITH','WITHIN','WITHOUT','YEAR'" +
378 ") ORDER BY 1"); 378 ") ORDER BY 1");
379
380 /* An old MonetDB server (pre Jul2015 release) will not have a table sys.keywords and return an empty String */
381 return (keywords.isEmpty()) ?
382 /* for old servers return static list (as returned in clients/odbc/driver/SQLGetInfo.c case SQL_KEYWORDS:) */
383 "ADMIN,AFTER,AGGREGATE,ALWAYS,ASYMMETRIC,ATOMIC," +
384 "AUTO_INCREMENT,BEFORE,BIGINT,BIGSERIAL,BINARY,BLOB," +
385 "CALL,CHAIN,CLOB,COMMITTED,COPY,CORR,CUME_DIST," +
386 "CURRENT_ROLE,CYCLE,DATABASE,DELIMITERS,DENSE_RANK," +
387 "DO,EACH,ELSEIF,ENCRYPTED,EVERY,EXCLUDE,FOLLOWING," +
388 "FUNCTION,GENERATED,IF,ILIKE,INCREMENT,LAG,LEAD," +
389 "LIMIT,LOCALTIME,LOCALTIMESTAMP,LOCKED,MAXVALUE," +
390 "MEDIAN,MEDIUMINT,MERGE,MINVALUE,NEW,NOCYCLE," +
391 "NOMAXVALUE,NOMINVALUE,NOW,OFFSET,OLD,OTHERS,OVER," +
392 "PARTITION,PERCENT_RANK,PLAN,PRECEDING,PROD,QUANTILE," +
393 "RANGE,RANK,RECORDS,REFERENCING,REMOTE,RENAME," +
394 "REPEATABLE,REPLICA,RESTART,RETURN,RETURNS," +
395 "ROW_NUMBER,ROWS,SAMPLE,SAVEPOINT,SCHEMA,SEQUENCE," +
396 "SERIAL,SERIALIZABLE,SIMPLE,START,STATEMENT,STDIN," +
397 "STDOUT,STREAM,STRING,SYMMETRIC,TIES,TINYINT,TRIGGER," +
398 "UNBOUNDED,UNCOMMITTED,UNENCRYPTED,WHILE,XMLAGG," +
399 "XMLATTRIBUTES,XMLCOMMENT,XMLCONCAT,XMLDOCUMENT," +
400 "XMLELEMENT,XMLFOREST,XMLNAMESPACES,XMLPARSE,XMLPI," +
401 "XMLQUERY,XMLSCHEMA,XMLTEXT,XMLVALIDATE"
402 : keywords;
403 } 379 }
404 380
405 /** 381 /**
406 * Internal utility method getConcatenatedStringFromQuery(String query) 382 * Internal utility method getConcatenatedStringFromQuery(String query)
407 * @param query the SQL SELECT query. Only the output of the first column is fetched and concatenated. 383 * @param query the SQL SELECT query. Only the output of the first column is fetched and concatenated.
1902 final String schemaPattern, 1878 final String schemaPattern,
1903 final String tableNamePattern, 1879 final String tableNamePattern,
1904 final String types[] 1880 final String types[]
1905 ) throws SQLException 1881 ) throws SQLException
1906 { 1882 {
1907 // 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).
1908 // as of Jul2015 release we also have a new table: sys.table_types with names for the new table types
1909 // for correct behavior we need to know if the server is using the old (pre Jul2015) or new sys.tables.type values
1910 final boolean preJul2015 = ("11.19.15".compareTo(getDatabaseProductVersion()) >= 0);
1911 // for debug: System.out.println("getDatabaseProductVersion() is " + getDatabaseProductVersion() + " preJul2015 is " + preJul2015);
1912
1913 final boolean useCommentsTable = con.commentsTableExists(); 1883 final boolean useCommentsTable = con.commentsTableExists();
1914 final StringBuilder query = new StringBuilder(1600); 1884 final StringBuilder query = new StringBuilder(1600);
1915 if (preJul2015 && types != null && types.length > 0) {
1916 // we need to filter on the constructed "TABLE_TYPE" expression, this is only possible when we use a subquery in the FROM
1917 query.append("SELECT * FROM (");
1918 }
1919 query.append("SELECT cast(null as char(1)) AS \"TABLE_CAT\", " + 1885 query.append("SELECT cast(null as char(1)) AS \"TABLE_CAT\", " +
1920 "s.\"name\" AS \"TABLE_SCHEM\", " + 1886 "s.\"name\" AS \"TABLE_SCHEM\", " +
1921 "t.\"name\" AS \"TABLE_NAME\", "); 1887 "t.\"name\" AS \"TABLE_NAME\", " +
1922 if (preJul2015) { 1888 "tt.\"table_type_name\" AS \"TABLE_TYPE\", ")
1923 query.append( 1889 .append(useCommentsTable ? "COALESCE(cm.\"remark\", t.\"query\")" : "t.\"query\"")
1924 "CASE WHEN t.\"system\" = true AND t.\"type\" IN (0, 10) AND t.\"temporary\" = 0 THEN 'SYSTEM TABLE' " + 1890 .append(" AS \"REMARKS\", " +
1925 "WHEN t.\"system\" = true AND t.\"type\" IN (1, 11) AND t.\"temporary\" = 0 THEN 'SYSTEM VIEW' " +
1926 "WHEN t.\"system\" = false AND t.\"type\" = 0 AND t.\"temporary\" = 0 THEN 'TABLE' " +
1927 "WHEN t.\"system\" = false AND t.\"type\" = 1 AND t.\"temporary\" = 0 THEN 'VIEW' " +
1928 "WHEN t.\"system\" = true AND t.\"type\" IN (0, 20) AND t.\"temporary\" = 1 THEN 'SYSTEM SESSION TABLE' " +
1929 "WHEN t.\"system\" = true AND t.\"type\" IN (1, 21) AND t.\"temporary\" = 1 THEN 'SYSTEM SESSION VIEW' " +
1930 "WHEN t.\"system\" = false AND t.\"type\" IN (0, 30) AND t.\"temporary\" = 1 THEN 'SESSION TABLE' " +
1931 "WHEN t.\"system\" = false AND t.\"type\" IN (1, 31) AND t.\"temporary\" = 1 THEN 'SESSION VIEW' " +
1932 "END AS \"TABLE_TYPE\", ");
1933 } else {
1934 query.append("tt.\"table_type_name\" AS \"TABLE_TYPE\", ");
1935 }
1936 query.append(useCommentsTable ? "COALESCE(cm.\"remark\", t.\"query\")" : "t.\"query\"").append(" AS \"REMARKS\", " +
1937 "cast(null as char(1)) AS \"TYPE_CAT\", " + 1891 "cast(null as char(1)) AS \"TYPE_CAT\", " +
1938 "cast(null as char(1)) AS \"TYPE_SCHEM\", " + 1892 "cast(null as char(1)) AS \"TYPE_SCHEM\", " +
1939 "cast(null as char(1)) AS \"TYPE_NAME\", " + 1893 "cast(null as char(1)) AS \"TYPE_NAME\", " +
1940 "cast(null as char(1)) AS \"SELF_REFERENCING_COL_NAME\", " + 1894 "cast(null as char(1)) AS \"SELF_REFERENCING_COL_NAME\", " +
1941 "cast(null as char(1)) AS \"REF_GENERATION\" " + 1895 "cast(null as char(1)) AS \"REF_GENERATION\" " +
1942 "FROM \"sys\".\"tables\" t "); 1896 "FROM \"sys\".\"tables\" t " +
1943 if (!preJul2015) { 1897 "JOIN \"sys\".\"table_types\" tt ON t.\"type\" = tt.\"table_type_id\" " +
1944 query.append("JOIN \"sys\".\"table_types\" tt ON t.\"type\" = tt.\"table_type_id\" "); 1898 "JOIN \"sys\".\"schemas\" s ON t.\"schema_id\" = s.\"id\" ");
1945 }
1946 query.append("JOIN \"sys\".\"schemas\" s ON t.\"schema_id\" = s.\"id\" ");
1947 if (useCommentsTable) { 1899 if (useCommentsTable) {
1948 query.append("LEFT OUTER JOIN \"sys\".\"comments\" cm ON t.\"id\" = cm.\"id\" "); 1900 query.append("LEFT OUTER JOIN \"sys\".\"comments\" cm ON t.\"id\" = cm.\"id\" ");
1949 } 1901 }
1950 1902
1951 boolean needWhere = true; 1903 boolean needWhere = true;
1965 needWhere = false; 1917 needWhere = false;
1966 } 1918 }
1967 } 1919 }
1968 1920
1969 if (types != null && types.length > 0) { 1921 if (types != null && types.length > 0) {
1970 if (preJul2015) { 1922 query.append(needWhere ? "WHERE" : " AND").append(" tt.\"table_type_name\" IN (");
1971 query.append(") AS \"getTables\" WHERE \"TABLE_TYPE\" IN (");
1972 } else {
1973 query.append(needWhere ? "WHERE" : " AND")
1974 .append(" tt.\"table_type_name\" IN (");
1975 }
1976 for (int i = 0; i < types.length; i++) { 1923 for (int i = 0; i < types.length; i++) {
1977 if (i > 0) { 1924 if (i > 0) {
1978 query.append(", "); 1925 query.append(',');
1979 } 1926 }
1980 query.append("'").append(types[i]).append("'"); 1927 query.append('\'').append(types[i]).append('\'');
1981 } 1928 }
1982 query.append(")"); 1929 query.append(')');
1983 } 1930 }
1984 1931
1985 query.append(" ORDER BY \"TABLE_TYPE\", \"TABLE_SCHEM\", \"TABLE_NAME\""); 1932 query.append(" ORDER BY \"TABLE_TYPE\", \"TABLE_SCHEM\", \"TABLE_NAME\"");
1986 1933
1987 return executeMetaDataQuery(query.toString()); 1934 return executeMetaDataQuery(query.toString());
2066 * @return ResultSet each row has a single String column that is a table type 2013 * @return ResultSet each row has a single String column that is a table type
2067 * @throws SQLException if a database error occurs 2014 * @throws SQLException if a database error occurs
2068 */ 2015 */
2069 @Override 2016 @Override
2070 public ResultSet getTableTypes() throws SQLException { 2017 public ResultSet getTableTypes() throws SQLException {
2071 // as of Jul2015 release we have a new table: sys.table_types with more table types 2018 return executeMetaDataQuery("SELECT \"table_type_name\" AS \"TABLE_TYPE\" FROM \"sys\".\"table_types\" ORDER BY 1");
2072 String query = "SELECT \"table_type_name\" AS \"TABLE_TYPE\" FROM \"sys\".\"table_types\" ORDER BY 1";
2073
2074 // For old (pre jul2015) servers fall back to old behavior.
2075 if ("11.19.15".compareTo(getDatabaseProductVersion()) >= 0)
2076 query = "SELECT 'SESSION TABLE' AS \"TABLE_TYPE\" UNION ALL " +
2077 "SELECT 'SESSION VIEW' UNION ALL " +
2078 "SELECT 'SYSTEM SESSION TABLE' UNION ALL " +
2079 "SELECT 'SYSTEM SESSION VIEW' UNION ALL " +
2080 "SELECT 'SYSTEM TABLE' UNION ALL " +
2081 "SELECT 'SYSTEM VIEW' UNION ALL " +
2082 "SELECT 'TABLE' UNION ALL " +
2083 "SELECT 'VIEW' ORDER BY 1";
2084
2085 return executeMetaDataQuery(query);
2086 } 2019 }
2087 2020
2088 /** 2021 /**
2089 * Get a description of table columns available in a catalog. 2022 * Get a description of table columns available in a catalog.
2090 * 2023 *
3462 3395
3463 if (types != null && types.length > 0) { 3396 if (types != null && types.length > 0) {
3464 query.append(") AS getUDTs WHERE \"DATA_TYPE\" IN ("); 3397 query.append(") AS getUDTs WHERE \"DATA_TYPE\" IN (");
3465 for (int i = 0; i < types.length; i++) { 3398 for (int i = 0; i < types.length; i++) {
3466 if (i > 0) { 3399 if (i > 0) {
3467 query.append(", "); 3400 query.append(',');
3468 } 3401 }
3469 query.append(types[i]); 3402 query.append(types[i]);
3470 } 3403 }
3471 query.append(")"); 3404 query.append(')');
3472 } 3405 }
3473 3406
3474 query.append(" ORDER BY \"DATA_TYPE\", \"TYPE_SCHEM\", \"TYPE_NAME\""); 3407 query.append(" ORDER BY \"DATA_TYPE\", \"TYPE_SCHEM\", \"TYPE_NAME\"");
3475 3408
3476 return executeMetaDataQuery(query.toString()); 3409 return executeMetaDataQuery(query.toString());