Mercurial > hg > monetdb-java

comparison src/main/java/nl/cwi/monetdb/jdbc/MonetDatabaseMetaData.java @ 298:6db8f6702ce8

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Improved MonetDatabaseMetaData methods: - getNumericFunctions(): it now includes functions: code, space and sys.alpha - getNumericFunctions(): it no longer lists functions: not_uniques and rotate_xor_hash - getStringFunctions(): it now includes functions: json.isarray, json.isobject, json.isvalid, sys.reverse which accept a string value as argument - getStringFunctions(): it no longer lists functions: copyfrom, get_value_for, next_value_for, restart. - getSystemFunctions(): it now includes system functions: get_value_for, hash, next_value_for, sys.columnsize, sys.debug, sys.hashsize, sys.heapsize, sys.imprintsize, sys.isauuid, sys.md5, sys.newurl, sys.password_hash, isaurl - getSystemFunctions(): it no longer lists function: getlimit. Also some functions require the prefix "sys." or "json." when used from another working schema. This is now included in the names returned by methods getNumericFunctions(), getStringFunctions(), getSystemFunctions() and getTimeDateFunctions().
author Martin van Dinther <martin.van.dinther@monetdbsolutions.com>
date Wed, 14 Aug 2019 20:25:52 +0200 (2019-08-14)
parents 003ae6d881db
children 1f0324072b0c
comparison
equal deleted inserted replaced
297:bb273e9c7e09 298:6db8f6702ce8
479 // for debug: System.out.println("SQL query: " + query + "\nResult string: " + sb.toString()); 479 // for debug: System.out.println("SQL query: " + query + "\nResult string: " + sb.toString());
480 return sb.toString(); 480 return sb.toString();
481 } 481 }
482 482
483 // SQL query parts shared by four get<Type>Functions() below 483 // SQL query parts shared by four get<Type>Functions() below
484 private static final String FunctionsSelect = "SELECT DISTINCT \"name\" FROM \"sys\".\"functions\" WHERE "; 484 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 ";
485 private static final String FunctionsWhere = "(\"id\" IN (SELECT \"func_id\" FROM \"sys\".\"args\" WHERE \"number\" = 1 AND \"name\" = 'arg_1' AND \"type\" IN "; 485 private static final String FunctionsWhere = "(f.\"id\" IN (SELECT \"func_id\" FROM \"sys\".\"args\" WHERE \"number\" = 1 AND \"name\" = 'arg_1' AND \"type\" IN ";
486 // Scalar functions sql_max(x,y), sql_min(x,y), greatest(x,y) and least(x,y) are defined in sys.args for type 'any' and usable as num, str and timedate functions. 486 // Scalar functions sql_max(x,y), sql_min(x,y), greatest(x,y) and least(x,y) are defined in sys.args for type 'any' and usable as num, str and timedate functions.
487 private static final String OrFunctionsMaxMin = " OR \"name\" IN ('sql_max','sql_min','least','greatest')"; 487 private static final String OrFunctionsMaxMin = " OR f.\"name\" IN ('sql_max','sql_min','least','greatest')";
488 private static final String FunctionsOrderBy1 = " ORDER BY 1"; 488 private static final String FunctionsOrderBy1 = " ORDER BY 1";
489 489
490 @Override 490 @Override
491 public String getNumericFunctions() { 491 public String getNumericFunctions() {
492 final String match = 492 final String match =
493 "('tinyint', 'smallint', 'int', 'bigint', 'hugeint', 'decimal', 'double', 'real') )" + 493 "('tinyint','smallint','int','bigint','hugeint','decimal','double','real'))" +
494 " AND \"type\" = 1" + // only scalar functions 494 " AND \"type\" = 1" + // only scalar functions, note that code(int) and space(int) will also be listed as string functions
495 // exclude functions which belong to the 'str' module 495 // exclude functions which do not work
496 " AND \"mod\" <> 'str')" + // to filter out string functions: 'code' and 'space' 496 " AND f.\"name\" NOT IN ('not_uniques','rotate_xor_hash'))" +
497 " OR \"name\" IN ('degrees','fuse','pi','ms_round','ms_str','ms_trunc','radians')"; 497 // include specific functions which have no 1st arg (pi()) or it is not numeric
498 " OR f.\"name\" IN ('alpha','degrees','fuse','ms_round','ms_str','ms_trunc','pi','radians')";
498 return getConcatenatedStringFromQuery(FunctionsSelect + FunctionsWhere + match + OrFunctionsMaxMin + FunctionsOrderBy1); 499 return getConcatenatedStringFromQuery(FunctionsSelect + FunctionsWhere + match + OrFunctionsMaxMin + FunctionsOrderBy1);
499 } 500 }
500 501
501 @Override 502 @Override
502 public String getStringFunctions() { 503 public String getStringFunctions() {
503 final String match = 504 final String match =
504 "('char', 'varchar', 'clob', 'json') )" + 505 "('char','varchar','clob','json','url'))" +
505 // include functions which belong to the 'str' module 506 " AND \"type\" = 1" + // only scalar functions
506 " OR \"mod\" = 'str')"; 507 // exclude sql functions: get_value_for, next_value_for, restart
508 " AND \"mod\" <> 'sql')" +
509 // include specific functions code(int) and space(int) which belong to the 'str' module
510 " OR \"mod\" = 'str'" +
511 // include 3 specific json functions and udf reverse which accept a string arg
512 " OR f.\"name\" IN ('isarray','isobject','isvalid','reverse')";
507 final String unionPart = 513 final String unionPart =
508 // add system functions which are not listed in sys.functions but implemented in the SQL parser (see sql/server/sql_parser.y) 514 // add functions which are not listed in sys.functions but implemented in the SQL parser (see sql/server/sql_parser.y)
509 " UNION SELECT 'position'"; 515 " UNION SELECT 'position'";
510 return getConcatenatedStringFromQuery(FunctionsSelect + FunctionsWhere + match + OrFunctionsMaxMin + unionPart + FunctionsOrderBy1); 516 return getConcatenatedStringFromQuery(FunctionsSelect + FunctionsWhere + match + OrFunctionsMaxMin + unionPart + FunctionsOrderBy1);
511 } 517 }
512 518
513 @Override 519 @Override
514 public String getSystemFunctions() { 520 public String getSystemFunctions() {
515 // Note: As of Apr2019 (11.33.3) release the system table systemfunctions is replaced by a view which queries functions.system
516 // TODO: Replace join to sys.systemfunctions with " AND \"system\" " but only if the server-version is >= 11.33.3
517 final String wherePart = 521 final String wherePart =
518 "\"id\" NOT IN (SELECT \"func_id\" FROM \"sys\".\"args\" WHERE \"number\" = 1)" + // without any args 522 "f.\"name\" IN ('columnsize','debug','get_value_for','hash','hashsize','heapsize'" +
519 " AND \"id\" IN (SELECT \"function_id\" FROM \"sys\".\"systemfunctions\")" + // only functions marked as system 523 ",'ifthenelse','imprintsize','isaurl','isauuid','isnull','masterclock','mastertick'" +
520 " AND \"type\" = 1" + // only scalar functions 524 ",'md5','newurl','next_value_for','password_hash','replicaclock','replicatick','uuid')" +
521 // exclude functions which belong to the 'mtime' module 525 // add functions which are not listed in sys.functions but implemented in the SQL parser (see sql/server/sql_parser.y)
522 " AND \"mod\" <> 'mtime'" + 526 " UNION ALL SELECT * FROM (VALUES('cast'),('coalesce'),('convert'),('nullif')) as sf";
523 " AND \"name\" NOT IN ('localtime','localtimestamp','pi','rand')" +
524 // add system functions which are not listed in sys.functions but implemented in the SQL parser (see sql/server/sql_parser.y)
525 " UNION SELECT 'cast'" +
526 " UNION SELECT 'coalesce'" +
527 " UNION SELECT 'convert'" +
528 " UNION SELECT 'ifthenelse'" +
529 " UNION SELECT 'isnull'" +
530 " UNION SELECT 'nullif'";
531 return getConcatenatedStringFromQuery(FunctionsSelect + wherePart + FunctionsOrderBy1); 527 return getConcatenatedStringFromQuery(FunctionsSelect + wherePart + FunctionsOrderBy1);
532 } 528 }
533 529
534 @Override 530 @Override
535 public String getTimeDateFunctions() { 531 public String getTimeDateFunctions() {
536 final String wherePart = 532 final String wherePart =
537 "\"mod\" IN ('mtime','timestamp') OR \"name\" IN ('localtime','localtimestamp','date_trunc')"; 533 "\"mod\" IN ('mtime','timestamp')" +
534 // include Date/Time functions which are located in 'str' module
535 " OR f.\"name\" IN ('localtime','localtimestamp','date_trunc')";
538 final String unionPart = 536 final String unionPart =
539 // add time date functions which are not listed in sys.functions but implemented in the SQL parser (see sql/server/sql_parser.y) 537 // add time date functions which are not listed in sys.functions but implemented in the SQL parser (see sql/server/sql_parser.y)
540 " UNION SELECT 'extract'" + 538 " UNION SELECT 'extract'" +
541 " UNION SELECT 'now'"; 539 " UNION SELECT 'now'";
542 return getConcatenatedStringFromQuery(FunctionsSelect + wherePart + OrFunctionsMaxMin + unionPart + FunctionsOrderBy1); 540 return getConcatenatedStringFromQuery(FunctionsSelect + wherePart + OrFunctionsMaxMin + unionPart + FunctionsOrderBy1);