Changeset: 50e43af49d47 for monetdb-java URL: https://dev.monetdb.org/hg/monetdb-java?cmd=changeset;node=50e43af49d47 Modified Files: ChangeLog src/main/java/org/monetdb/jdbc/MonetDatabaseMetaData.java src/main/java/org/monetdb/jdbc/MonetDriver.java.in src/main/java/org/monetdb/jdbc/MonetResultSet.java Branch: default Log Message:
Improved DatabaseMetaData.getTypeInfo() output for temporal data types: sec_interval, day_interval, month_interval, date, time, timetz, timestamp and timestamptz. diffs (196 lines): diff --git a/ChangeLog b/ChangeLog --- a/ChangeLog +++ b/ChangeLog @@ -1,6 +1,11 @@ # ChangeLog file for monetdb-java # This file is updated with Maddlog +* Thu Jan 14 2021 Martin van Dinther <[email protected]> +- Improved DatabaseMetaData.getTypeInfo() output for temporal data + types: sec_interval, day_interval, month_interval, date, time, timetz, + timestamp and timestamptz. + * Wed Jan 6 2021 Martin van Dinther <[email protected]> - Corrected output of resultset columns UPDATE_RULE and DELETE_RULE when calling DatabaseMetaData API methods getImportedKeys() or @@ -24,9 +29,9 @@ nl.cwi.monetdb.mcl.net.MapiSocket nl.cwi.monetdb.client.JdbcClient They are implemented as simple wrappers of their org.monetdb.* equivalents. - Note: These nl.cwi.monetdb.* classes are now marked as deprecated and may be - removed in a future release. If you still use them in your Java code, - update them to use the new package names. + Note: These nl.cwi.monetdb.* classes are now marked as deprecated and may + be removed in a future release. If you still use them in your Java code or + configuration files, update them to use the new package names. * Thu Oct 29 2020 Martin van Dinther <[email protected]> - Extended JdbcClient program with 3 new commands to quickly validate @@ -42,11 +47,11 @@ - Column NOT NULL constraint - Varchar(n) max length constraint - Idem for char(n), clob(n), blob(n), json(n) and url(n). - It can be usefull to run \vsci before and after an upgrade. + It can be usefull to run \vsci before and after an upgrade of MonetDB server. Use \vsi my_schema to validate data in all tables of a specific schema. Use \vdbi to validate integrity of data in all user schemas in - the database. Note this can take a while, depending on your number - of user schemas and tables sizes. Despite being tested on several + the database. Note: this can take a while, depending on your number + of user schemas, tables and tables sizes. Despite being tested on several internal dbs the functionality is still beta, so you can get false errors reported. If you encounter these let us know asap. @@ -54,6 +59,8 @@ - Improved performance of ResultSetMetaData methods isAutoIncrement(), getPrecision() and getScale() significantly for columns of specific data types as in some cases no costly meta data query is executed anymore. + +* Thu Oct 8 2020 Martin van Dinther <[email protected]> - The connection properties treat_clob_as_varchar and treat_blob_as_binary are now set to true by default within the JDBC driver. This is done as it results by default in less memory usage, (much) faster response @@ -74,6 +81,8 @@ - adding 3 methods to MonetPreparedStatement - adding 4 methods to MonetResultSet - adding 8 methods to MonetStatement + +* Wed Sep 23 2020 Martin van Dinther <[email protected]> - Corrected MonetDatabaseMetaData.getTypeInfo() - The LITERAL_PREFIX column now includes the required casting name for types: clob, inet, json, url, uuid and blob. diff --git a/src/main/java/org/monetdb/jdbc/MonetDatabaseMetaData.java b/src/main/java/org/monetdb/jdbc/MonetDatabaseMetaData.java --- a/src/main/java/org/monetdb/jdbc/MonetDatabaseMetaData.java +++ b/src/main/java/org/monetdb/jdbc/MonetDatabaseMetaData.java @@ -1773,7 +1773,7 @@ public class MonetDatabaseMetaData " WHEN 'hugeint' THEN 16 WHEN 'oid' THEN 8 WHEN 'wrd' THEN 8 ELSE a.\"type_digits\" END AS \"LENGTH\", " + "cast(CASE WHEN a.\"type\" IN ('tinyint','smallint','int','bigint','hugeint','oid','wrd','decimal','numeric'," + "'time','timetz','timestamp','timestamptz','day_interval','month_interval','sec_interval') THEN a.\"type_scale\" ELSE NULL END AS smallint) AS \"SCALE\", " + - "cast(CASE WHEN a.\"type\" IN ('tinyint','smallint','int','bigint','hugeint','oid','wrd','decimal','numeric') THEN 10" + + "cast(CASE WHEN a.\"type\" IN ('tinyint','smallint','int','bigint','hugeint','oid','wrd','decimal','numeric','day_interval','month_interval','sec_interval') THEN 10" + " WHEN a.\"type\" IN ('real','float','double') THEN 2 ELSE NULL END AS smallint) AS \"RADIX\", " + "cast(").append(DatabaseMetaData.procedureNullableUnknown).append(" AS smallint) AS \"NULLABLE\", " + "cast(null as char(1)) AS \"REMARKS\", " + @@ -2940,12 +2940,14 @@ public class MonetDatabaseMetaData public ResultSet getTypeInfo() throws SQLException { final StringBuilder query = new StringBuilder(2300); query.append("SELECT \"sqlname\" AS \"TYPE_NAME\", " + + // TODO map 'day_interval' to 'interval day' (or 'interval day to second'), 'month_interval' to 'interval month' (or 'interval year to month'), 'sec_interval' to 'interval second' "cast(").append(MonetDriver.getSQLTypeMap("\"sqlname\"")).append(" AS int) AS \"DATA_TYPE\", " + "\"digits\" AS \"PRECISION\", " + // note that when radix is 2 the precision shows the number of bits - "cast(CASE WHEN \"sqlname\" IN ('char','varchar') THEN ''''" + - " WHEN \"sqlname\" IN ('clob','inet','json','url','uuid','blob','sqlblob') THEN \"sqlname\"||' '''" + - " ELSE NULL END AS varchar(9)) AS \"LITERAL_PREFIX\", " + - "cast(CASE WHEN \"systemname\" IN ('str','inet','json','url','uuid','blob','sqlblob') THEN ''''" + + "cast(CASE WHEN \"sqlname\" IN ('char','varchar','sec_interval','day_interval','month_interval') THEN ''''" + + " WHEN \"sqlname\" IN ('clob','inet','json','url','uuid','date','time','timetz','timestamp','timestamptz','blob','sqlblob') THEN \"sqlname\"||' '''" + + " ELSE NULL END AS varchar(16)) AS \"LITERAL_PREFIX\", " + + "cast(CASE WHEN \"sqlname\" IN ('char','varchar','sec_interval','day_interval','month_interval'" + + ",'clob','inet','json','url','uuid','date','time','timetz','timestamp','timestamptz','blob','sqlblob') THEN ''''" + " ELSE NULL END AS varchar(2)) AS \"LITERAL_SUFFIX\", " + "CASE WHEN \"sqlname\" IN ('char','varchar') THEN 'max length'" + " WHEN \"sqlname\" = 'decimal' THEN 'precision, scale'" + @@ -2956,7 +2958,8 @@ public class MonetDatabaseMetaData "CASE WHEN \"systemname\" IN ('str','json','url') THEN true ELSE false END AS \"CASE_SENSITIVE\", " + "cast(CASE WHEN \"systemname\" IN ('str','inet','json','url','uuid') THEN ").append(DatabaseMetaData.typeSearchable) .append(" ELSE ").append(DatabaseMetaData.typePredBasic).append(" END AS smallint) AS \"SEARCHABLE\", " + - "CASE WHEN \"sqlname\" IN ('tinyint','smallint','int','bigint','hugeint','decimal','real','double','day_interval','month_interval','sec_interval') THEN false ELSE true END AS \"UNSIGNED_ATTRIBUTE\", " + + "CASE WHEN \"sqlname\" IN ('tinyint','smallint','int','bigint','hugeint','decimal','real','double'" + + ",'day_interval','month_interval','sec_interval') THEN false ELSE true END AS \"UNSIGNED_ATTRIBUTE\", " + "CASE \"sqlname\" WHEN 'decimal' THEN true ELSE false END AS \"FIXED_PREC_SCALE\", " + "CASE WHEN \"sqlname\" IN ('tinyint','smallint','int','bigint') THEN true ELSE false END AS \"AUTO_INCREMENT\", " + "\"systemname\" AS \"LOCAL_TYPE_NAME\", " + @@ -2965,7 +2968,7 @@ public class MonetDatabaseMetaData " WHEN \"sqlname\" IN ('time','timetz','timestamp','timestamptz','sec_interval') THEN 6 ELSE 0 END AS smallint) AS \"MAXIMUM_SCALE\", " + "cast(0 AS int) AS \"SQL_DATA_TYPE\", " + "cast(0 AS int) AS \"SQL_DATETIME_SUB\", " + - "cast(\"radix\" as int) AS \"NUM_PREC_RADIX\" " + + "cast(CASE WHEN \"sqlname\" IN ('time','timetz','timestamp','timestamptz','sec_interval') THEN 10 ELSE \"radix\" END AS int) AS \"NUM_PREC_RADIX\" " + "FROM \"sys\".\"types\" " + "ORDER BY \"DATA_TYPE\", \"sqlname\", \"id\""); @@ -3709,17 +3712,13 @@ public class MonetDatabaseMetaData /** * Retrieves a list of the client info properties that the driver - * supports. The result set contains the following columns - * + * supports. The result set contains the following columns: * 1. NAME String => The name of the client info property - * 2. MAX_LEN int => The maximum length of the value for the - * property - * 3. DEFAULT_VALUE String => The default value of the - * property - * 4. DESCRIPTION String => A description of the - * property. This will typically contain information as - * to where this property is stored in the database. - * + * 2. MAX_LEN int => The maximum length of the value for the property + * 3. DEFAULT_VALUE String => The default value of the property + * 4. DESCRIPTION String => A description of the property. + * This will typically contain information as to + * where this property is stored in the database. * The ResultSet is sorted by the NAME column * * @return A ResultSet object; each row is a supported client info property @@ -3738,8 +3737,8 @@ public class MonetDatabaseMetaData "SELECT 'debug', 5, 'false', 'boolean flag true or false' UNION ALL " + "SELECT 'logfile', 1024, 'monet_######.log', 'name of logfile used when debug is enabled' UNION ALL " + "SELECT 'hash', 128, '', 'hash methods list to use in server connection. Supported are SHA512, SHA384, SHA256 and SHA1' UNION ALL " + - "SELECT 'treat_blob_as_binary', 5, 'false', 'should blob columns be mapped to Types.VARBINARY instead of default Types.BLOB in ResultSets and PreparedStatements' UNION ALL " + - "SELECT 'treat_clob_as_varchar', 5, 'false', 'should clob columns be mapped to Types.VARCHAR instead of default Types.CLOB in ResultSets and PreparedStatements' UNION ALL " + + "SELECT 'treat_blob_as_binary', 5, 'true', 'should blob columns be mapped to Types.VARBINARY instead of default Types.BLOB in ResultSets and PreparedStatements' UNION ALL " + + "SELECT 'treat_clob_as_varchar', 5, 'true', 'should clob columns be mapped to Types.VARCHAR instead of default Types.CLOB in ResultSets and PreparedStatements' UNION ALL " + "SELECT 'so_timeout', 10, '0', 'timeout (in milliseconds) of communication socket. 0 means no timeout is set' " + "ORDER BY \"NAME\""; @@ -3917,7 +3916,7 @@ public class MonetDatabaseMetaData " WHEN 'hugeint' THEN 16 WHEN 'oid' THEN 8 WHEN 'wrd' THEN 8 ELSE a.\"type_digits\" END AS \"LENGTH\", " + "cast(CASE WHEN a.\"type\" IN ('tinyint','smallint','int','bigint','hugeint','oid','wrd','decimal','numeric'," + "'time','timetz','timestamp','timestamptz','day_interval','month_interval','sec_interval') THEN a.\"type_scale\" ELSE NULL END AS smallint) AS \"SCALE\", " + - "cast(CASE WHEN a.\"type\" IN ('tinyint','smallint','int','bigint','hugeint','oid','wrd','decimal','numeric') THEN 10" + + "cast(CASE WHEN a.\"type\" IN ('tinyint','smallint','int','bigint','hugeint','oid','wrd','decimal','numeric','day_interval','month_interval','sec_interval') THEN 10" + " WHEN a.\"type\" IN ('real','float','double') THEN 2 ELSE NULL END AS smallint) AS \"RADIX\", " + "cast(").append(DatabaseMetaData.functionNullableUnknown).append(" AS smallint) AS \"NULLABLE\", " + "cast(null as char(1)) AS \"REMARKS\", " + diff --git a/src/main/java/org/monetdb/jdbc/MonetDriver.java.in b/src/main/java/org/monetdb/jdbc/MonetDriver.java.in --- a/src/main/java/org/monetdb/jdbc/MonetDriver.java.in +++ b/src/main/java/org/monetdb/jdbc/MonetDriver.java.in @@ -334,7 +334,7 @@ public class MonetDriver implements Driv typeMap.put("char", Integer.valueOf(Types.CHAR)); typeMap.put("clob", Integer.valueOf(Types.CLOB)); typeMap.put("date", Integer.valueOf(Types.DATE)); - typeMap.put("day_interval", Integer.valueOf(Types.BIGINT)); // New as of Oct2020 release + typeMap.put("day_interval", Integer.valueOf(Types.NUMERIC)); // New as of Oct2020 release typeMap.put("decimal", Integer.valueOf(Types.DECIMAL)); typeMap.put("double", Integer.valueOf(Types.DOUBLE)); // typeMap.put("geometry", Integer.valueOf(Types.???)); diff --git a/src/main/java/org/monetdb/jdbc/MonetResultSet.java b/src/main/java/org/monetdb/jdbc/MonetResultSet.java --- a/src/main/java/org/monetdb/jdbc/MonetResultSet.java +++ b/src/main/java/org/monetdb/jdbc/MonetResultSet.java @@ -1636,7 +1636,16 @@ public class MonetResultSet switch (getColumnType(column)) { case Types.DECIMAL: case Types.NUMERIC: - // these data types may have a scale, max scale is 38 + { + // special handling for: day_interval and sec_interval as these are mapped to these result types (see MonetDriver typemap) + // they appear to have a fixed scale (tested against Oct2020) + final String monettype = getColumnTypeName(column); + if ("day_interval".equals(monettype)) + return 0; + if ("sec_interval".equals(monettype)) + return 3; + + // these data types may have a variable scale, max scale is 38 try { if (_is_fetched[column] != true) { fetchColumnInfo(column); @@ -1645,6 +1654,7 @@ public class MonetResultSet } catch (IndexOutOfBoundsException e) { throw MonetResultSet.newSQLInvalidColumnIndexException(column); } + } case Types.TIME: case Types.TIME_WITH_TIMEZONE: case Types.TIMESTAMP: _______________________________________________ checkin-list mailing list [email protected] https://www.monetdb.org/mailman/listinfo/checkin-list
