Mercurial > hg > monetdb-java

changeset 263:8fbed4aeff0e

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Improved MonetDatabaseMetaData methods: - getNumericFunctions(): it no longer lists aggregate functions: avg, prod and sum - getSystemFunctions(): it no longer lists timedate function: extract - getTimeDateFunctions(): it now also lists function: epoch
author Martin van Dinther <martin.van.dinther@monetdbsolutions.com>
date Thu, 07 Feb 2019 18:33:45 +0100 (2019-02-07)
parents 2d62ca1f758b
children d31b99f06b26
files ChangeLog src/main/java/nl/cwi/monetdb/jdbc/MonetDatabaseMetaData.java
diffstat 2 files changed, 26 insertions(+), 16 deletions(-) [+]
line wrap: on
line diff
--- a/ChangeLog
+++ b/ChangeLog
@@ -1,6 +1,12 @@
 # ChangeLog file for monetdb-java
 # This file is updated with Maddlog
 
+* Thu Feb  7 2019 Martin van Dinther <martin.van.dinther@monetdbsolutions.com>
+- Improved MonetDatabaseMetaData methods:
+  - getNumericFunctions(): it no longer lists aggregate functions: avg, prod and sum
+  - getSystemFunctions(): it no longer lists timedate function: extract
+  - getTimeDateFunctions(): it now also lists function: epoch
+
 * Thu Sep 20 2018 Martin van Dinther <martin.van.dinther@monetdbsolutions.com>
 - Improved example program SQLcopyinto.java and moved it to tests directory
   for automatic testing.
--- a/src/main/java/nl/cwi/monetdb/jdbc/MonetDatabaseMetaData.java
+++ b/src/main/java/nl/cwi/monetdb/jdbc/MonetDatabaseMetaData.java
@@ -481,20 +481,20 @@ public class MonetDatabaseMetaData exten
 	}
 
 	// SQL query parts shared by four get<Type>Functions() below
-	private static final String FunctionsSelect = "SELECT DISTINCT \"name\" FROM \"sys\".\"functions\" ";
-	private static final String FunctionsWhere = "WHERE \"id\" IN (SELECT \"func_id\" FROM \"sys\".\"args\" WHERE \"number\" = 1 AND \"name\" = 'arg_1' AND \"type\" IN ";
-	// Scalar functions sql_max(x, y) and sql_min(x, y) are defined in sys.args only for type 'any'.
-	// Easiest way to include them in the Num, Str and TimeDate lists is to add them explicitly via UNION SQL:
-	private static final String AddFunctionsMaxMin = " UNION SELECT 'sql_max' UNION SELECT 'sql_min'";
+	private static final String FunctionsSelect = "SELECT DISTINCT \"name\" FROM \"sys\".\"functions\" WHERE ";
+	private static final String FunctionsWhere = "(\"id\" IN (SELECT \"func_id\" FROM \"sys\".\"args\" WHERE \"number\" = 1 AND \"name\" = 'arg_1' AND \"type\" IN ";
+	// 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.
+	private static final String OrFunctionsMaxMin = " OR \"name\" IN ('sql_max','sql_min','least','greatest')";
 	private static final String FunctionsOrderBy1 = " ORDER BY 1";
 
 	@Override
 	public String getNumericFunctions() {
 		String match =
 			"('tinyint', 'smallint', 'int', 'bigint', 'hugeint', 'decimal', 'double', 'real') )" +
+			" AND \"type\" = 1" +	// only scalar functions
 			// exclude functions which belong to the 'str' module
-			" AND \"mod\" <> 'str'";
-		return getConcatenatedStringFromQuery(FunctionsSelect + FunctionsWhere + match + AddFunctionsMaxMin + FunctionsOrderBy1);
+			" AND \"mod\" <> 'str')";	// to filter out string functions: 'code' and 'space'
+		return getConcatenatedStringFromQuery(FunctionsSelect + FunctionsWhere + match + OrFunctionsMaxMin + FunctionsOrderBy1);
 	}
 
 	@Override
@@ -502,24 +502,23 @@ public class MonetDatabaseMetaData exten
 		String match =
 			"('char', 'varchar', 'clob', 'json') )" +
 			// include functions which belong to the 'str' module
-			" OR \"mod\" = 'str'";
-		return getConcatenatedStringFromQuery(FunctionsSelect + FunctionsWhere + match + AddFunctionsMaxMin + FunctionsOrderBy1);
+			" OR \"mod\" = 'str')";
+		return getConcatenatedStringFromQuery(FunctionsSelect + FunctionsWhere + match + OrFunctionsMaxMin + FunctionsOrderBy1);
 	}
 
 	@Override
 	public String getSystemFunctions() {
 		String wherePart =
-			"WHERE \"id\" NOT IN (SELECT \"func_id\" FROM \"sys\".\"args\" WHERE \"number\" = 1)" +
-			" AND \"id\" IN (SELECT \"function_id\" FROM \"sys\".\"systemfunctions\")" +
+			"\"id\" NOT IN (SELECT \"func_id\" FROM \"sys\".\"args\" WHERE \"number\" = 1)" +	// without any args
+			" AND \"id\" IN (SELECT \"function_id\" FROM \"sys\".\"systemfunctions\")" +	// only functions marked as system
 			" AND \"type\" = 1" +	// only scalar functions
 			// exclude functions which belong to the 'mtime' module
 			" AND \"mod\" <> 'mtime'" +
-			" AND \"name\" NOT IN ('localtime', 'localtimestamp')" +
+			" AND \"name\" NOT IN ('localtime','localtimestamp')" +
 			// add system functions which are not listed in sys.functions but implemented in the SQL parser (see sql/server/sql_parser.y)
 			" UNION SELECT 'cast'" +
+			" UNION SELECT 'coalesce'" +
 			" UNION SELECT 'convert'" +
-			" UNION SELECT 'coalesce'" +
-			" UNION SELECT 'extract'" +
 			" UNION SELECT 'ifthenelse'" +
 			" UNION SELECT 'isnull'" +
 			" UNION SELECT 'nullif'";
@@ -528,8 +527,13 @@ public class MonetDatabaseMetaData exten
 
 	@Override
 	public String getTimeDateFunctions() {
-		String wherePart = "WHERE \"mod\" = 'mtime' OR \"name\" IN ('localtime', 'localtimestamp') UNION SELECT 'extract' UNION SELECT 'now'";
-		return getConcatenatedStringFromQuery(FunctionsSelect + wherePart + AddFunctionsMaxMin + FunctionsOrderBy1);
+		String wherePart =
+			 "\"mod\" = 'mtime' OR \"name\" IN ('epoch','localtime','localtimestamp')";
+		String unionPart =
+			// add time date functions which are not listed in sys.functions but implemented in the SQL parser (see sql/server/sql_parser.y)
+			" UNION SELECT 'extract'" +
+			" UNION SELECT 'now'";
+		return getConcatenatedStringFromQuery(FunctionsSelect + wherePart + OrFunctionsMaxMin + unionPart + FunctionsOrderBy1);
 	}
 
 	/**