Mercurial > hg > monetdb-java
changeset 228:51e886d7f3b4 embedded
Merge with default
| author | Pedro Ferreira <pedro.ferreira@monetdbsolutions.com> |
|---|---|
| date | Tue, 08 May 2018 19:04:17 +0200 (2018-05-08) |
| parents | e938bb2c587f (current diff) 5ea126405bac (diff) |
| children | 672523c56341 |
| files | example/MJDBCTest.java example/PreparedExample.java example/SQLImport.java example/SQLcopyinto.java src/main/java/nl/cwi/monetdb/jdbc/MonetBlob.java src/main/java/nl/cwi/monetdb/jdbc/MonetClob.java src/main/java/nl/cwi/monetdb/jdbc/MonetConnection.java src/main/java/nl/cwi/monetdb/jdbc/MonetDatabaseMetaData.java src/main/java/nl/cwi/monetdb/jdbc/MonetDriver.java.in src/main/java/nl/cwi/monetdb/jdbc/MonetPreparedStatement.java src/main/java/nl/cwi/monetdb/jdbc/MonetResultSet.java src/main/java/nl/cwi/monetdb/mcl/responses/ResultSetResponse.java |
| diffstat | 14 files changed, 776 insertions(+), 495 deletions(-) [+] |
line wrap: on
line diff
--- a/ChangeLog +++ b/ChangeLog @@ -1,61 +1,28 @@ # ChangeLog file for monetdb-java # This file is updated with Maddlog -* Mon Oct 23 2017 Sjoerd Mullender <sjoerd@acm.org> -- Compiled and released new jars: monetdb-jdbc-2.27.jar, monetdb-mcl-1.16.jar - and updated jdbcclient.jar - -* Thu Oct 5 2017 Martin van Dinther <martin.van.dinther@monetdbsolutions.com> -- Corrected method Connection.prepareCall(). It used to return null. - Now it throws an SQLFeatureNotSupportedException to comply with the - JDBC specification. +* Thu Apr 26 2018 Martin van Dinther <martin.van.dinther@monetdbsolutions.com> +- Corrected and extended output of DatabaseMetaData methods + getTimeDateFunctions() and getSystemFunctions(). The Date/Time functions + (curdate, current_date, current_time, current_timestamp, curtime, + local_timezone, localtime, localtimestamp) were returned by + getSystemFunctions() but are now returned by getTimeDateFunctions(). + getTimeDateFunctions() now also lists functions: date_to_str, extract, now, + str_to_date, str_to_time, str_to_timestamp, time_to_str and timestamp_to_str. +- Improved DatabaseMetaData methods getTablePrivileges() and + getColumnPrivileges() by returning also any combination of privileges + for the table or column in the PRIVILEGE result column. Previously only + single privileges (SELECT or UPDATE or INSERT or DELETE or EXECUTE or + GRANT) would be returned. -* Thu Sep 28 2017 Martin van Dinther <martin.van.dinther@monetdbsolutions.com> -- Added possibility to let the JDBC driver return columnType value - Types.VARCHAR instead of Types.CLOB in case the result column of a - ResultSet or parameter in a PreparedStatement is of data type 'clob'. - To enable this behavior provide it as JDBC connection URL property: - treat_clob_as_varchar=true - For example: jdbc:monetdb://localhost:50000/demo?treat_clob_as_varchar=true - See also: - https://www.monetdb.org/Documentation/Manuals/SQLreference/Programming/JDBC - This custom clob mapping informs generic JDBC programs to fetch clob - column values via method ResultSet.getString() instead of getClob() - and Clob.getCharacterStream() and next fetching from the stream. - As MonetDB server MAPI communication protocol does not support - streaming of parts of a CLOB value, the current implementation is to - send over the whole CLOB value as a string. Therefore there is no - performance gain when fetching those Clob values via getClob() and - getCharacterStream(). In fact it creates a lot of overhead (additional - objects and method calls) and triples the amount of needed Java Heap memory - for each Clob value! - With this connection property set, you can reduce this overhead when - working with clob column data from generic JDBC programs. +* Thu Apr 19 2018 Martin van Dinther <martin.van.dinther@monetdbsolutions.com> +- Corrected method DatabaseMetaData.getFunctions() for result column + FUNCTION_TYPE. It used to return DatabaseMetaData.functionResultUnknown + value for Analytic (functions.type 6) and Loader function (functions type 7). + It now returns DatabaseMetaData.functionNoTable for Analytic functions and + DatabaseMetaData.functionReturnsTable for Loader functions. +- DatabaseMetaData methods getTables(), getColumns(), getProcedures() and + getFunctions() now return the comment in the REMARKS result column when a + comment has been set for the table / view / column / procedure / function + via the SQL command COMMENT ON <db-object type> <qname> IS 'comment-text'. -* Thu Sep 7 2017 Martin van Dinther <martin.van.dinther@monetdbsolutions.com> -- Implemented PreparedStatement methods setNCharacterStream(int, Reader) - and setNCharacterStream(int, Reader, long). - -* Thu Aug 31 2017 Martin van Dinther <martin.van.dinther@monetdbsolutions.com> -- Corrected PreparedStatement methods setString(int, String) - and setObject(int, Object, ...) in case the target parameter - data type was json or inet or url or uuid. See also - https://www.monetdb.org/bugzilla/show_bug.cgi?id=6382 - -* Thu Aug 24 2017 Martin van Dinther <martin.van.dinther@monetdbsolutions.com> -- Implemented PreparedStatement method setURL(int, URL). -- Implemented PreparedStatement method setNString(int, String). -- The MonetDB JDBC driver code and jdbcclient program are now compiled - without debug info and with optimise flag enabled. - The new jar files are now some 20% smaller in size. - -* Thu Aug 17 2017 Martin van Dinther <martin.van.dinther@monetdbsolutions.com> -- Implemented ResultSet method getNCharacterStream(). -- In class MonetClob implemented methods getCharacterStream() - and getCharacterStream(long pos, long length). Method - getCharacterStream() is called by DBeaver to fetch the Clob value. - It used to throw a SQLFeatureNotSupportedException with message: - "Operation getCharacterStream() currently not supported". This caused - DBeaver to log the exception and show NULL as the value on screen, - which is incorrect. This has been fixed. -
--- a/example/MJDBCTest.java +++ b/example/MJDBCTest.java @@ -17,14 +17,24 @@ import java.sql.*; */ public class MJDBCTest { public static void main(String[] args) throws Exception { - //Class.forName("nl.cwi.monetdb.jdbc.MonetDriver"); - // turn on debugging (disabled) - //nl.cwi.monetdb.jdbc.MonetConnection.setDebug(true); - Connection con = DriverManager.getConnection("jdbc:monetdb://localhost/notused", "monetdb", "monetdb"); + String MonetDB_JDBC_URL = "jdbc:monetdb://localhost:50000/demo"; // change host, port and databasename + Connection con; + try { + con = DriverManager.getConnection(MonetDB_JDBC_URL, "monetdb", "monetdb"); + } catch (SQLException e) { + System.err.println("Failed to connect to MonetDB server! Message: " + e.getMessage()); + } + + if (con == null) { + System.err.println("Failed to create a connection object!"); + return; + } + Statement st = con.createStatement(); ResultSet rs; - rs = st.executeQuery("SELECT a.var1, COUNT(b.id) as total FROM a, b WHERE a.var1 = b.id AND a.var1 = 'andb' GROUP BY a.var1 ORDER BY a.var1, total;"); + String sql = "SELECT a.var1, COUNT(b.id) as total FROM a, b WHERE a.var1 = b.id AND a.var1 = 'andb' GROUP BY a.var1 ORDER BY a.var1, total;"; + rs = st.executeQuery(sql); // get meta data and print columns with their type ResultSetMetaData md = rs.getMetaData(); for (int i = 1; i <= md.getColumnCount(); i++) { @@ -48,39 +58,51 @@ public class MJDBCTest { // we ask the database for 22 rows, while we set the JDBC driver to // 5 rows, this shouldn't be a problem at all... rs = st.executeQuery("select * from a limit 22"); + int var1_cnr = rs.findColumn("var1"); + int var2_cnr = rs.findColumn("var2"); + int var3_cnr = rs.findColumn("var3"); + int var4_cnr = rs.findColumn("var4"); // read till the driver says there are no rows left for (int i = 0; rs.next(); i++) { - System.out.print("[" + rs.getString("var1") + "]"); - System.out.print("[" + rs.getString("var2") + "]"); - System.out.print("[" + rs.getInt("var3") + "]"); - System.out.println("[" + rs.getString("var4") + "]"); + System.out.println( + "[" + rs.getString(var1_cnr) + "]" + + "[" + rs.getString(var2_cnr) + "]" + + "[" + rs.getInt(var3_cnr) + "]" + + "[" + rs.getString(var4_cnr) + "]" ); } - - // this close is not needed, should be done by next execute(Query) call + + // this rs.close is not needed, should be done by next execute(Query) call // however if there can be some time between this point and the next // execute call, it is from a resource perspective better to close it. - //rs.close(); + rs.close(); // unset the row limit; 0 means as much as the database sends us st.setMaxRows(0); // we only ask 10 rows rs = st.executeQuery("select * from b limit 10;"); + int rowid_cnr = rs.findColumn("rowid"); + int id_cnr = rs.findColumn("id"); + var1_cnr = rs.findColumn("var1"); + var2_cnr = rs.findColumn("var2"); + var3_cnr = rs.findColumn("var3"); + var4_cnr = rs.findColumn("var4"); // and simply print them while (rs.next()) { - System.out.print(rs.getInt("rowid") + ", "); - System.out.print(rs.getString("id") + ", "); - System.out.print(rs.getInt("var1") + ", "); - System.out.print(rs.getInt("var2") + ", "); - System.out.print(rs.getString("var3") + ", "); - System.out.println(rs.getString("var4")); + System.out.println( + rs.getInt(rowid_cnr) + ", " + + rs.getString(id_cnr) + ", " + + rs.getInt(var1_cnr) + ", " + + rs.getInt(var2_cnr) + ", " + + rs.getString(var3_cnr) + ", " + + rs.getString(var4_cnr) ); } - // this close is not needed, as the Statement will close the last + // this close is not required, as the Statement will close the last // ResultSet around when it's closed // again, if that can take some time, it's nicer to close immediately // the reason why these closes are commented out here, is to test if // the driver really cleans up it's mess like it should - //rs.close(); + rs.close(); // perform a ResultSet-less query (with no trailing ; since that should // be possible as well and is JDBC standard) @@ -89,12 +111,12 @@ public class MJDBCTest { // support returning the affected rows. st.executeUpdate("delete from a where var1 = 'zzzz'"); - // closing the connection should take care of closing all generated - // statements from it... // Don't forget to do it yourself if the connection is reused or much // longer alive, since the Statement object contains a lot of things // you probably want to reclaim if you don't need them anymore. - //st.close(); + st.close(); + // closing the connection should take care of closing all generated + // statements from it... con.close(); } }
--- a/example/PreparedExample.java +++ b/example/PreparedExample.java @@ -15,7 +15,6 @@ import java.sql.*; */ public class PreparedExample { public static void main(String[] args) throws Exception { - //Class.forName("nl.cwi.monetdb.jdbc.MonetDriver"); Connection con = DriverManager.getConnection("jdbc:monetdb://localhost/notused", "monetdb", "monetdb"); PreparedStatement st = con.prepareStatement("SELECT ? AS a1, ? AS a2"); ResultSet rs;
--- a/example/SQLImport.java +++ b/example/SQLImport.java @@ -37,7 +37,6 @@ public class SQLImport { // open the file BufferedReader fr = new BufferedReader(new FileReader(args[0])); - // Class.forName("nl.cwi.monetdb.jdbc.MonetDriver"); // request a connection suitable for Monet from the driver manager // note that the database specifier is currently not implemented, for // Monet itself can't access multiple databases.
--- a/example/SQLcopyinto.java +++ b/example/SQLcopyinto.java @@ -23,9 +23,10 @@ import nl.cwi.monetdb.mcl.protocol.Abstr public class SQLcopyinto { public static void main(String[] args) throws Exception { - // Class.forName("nl.cwi.monetdb.jdbc.MonetDriver"); - // request a connection suitable for Monet from the driver manager note that the database specifier is currently - // not implemented, for Monet itself can't access multiple databases turn on debugging + // request a connection suitable for Monet from the driver manager + // note that the database specifier is currently not implemented, for + // Monet itself can't access multiple databases. + // turn on debugging Connection con = DriverManager.getConnection("jdbc:monetdb://localhost/database", "monetdb", "monetdb"); // get a statement to execute on
--- a/release.txt +++ b/release.txt @@ -44,7 +44,7 @@ implementation is rich enough to be suit application settings. Below a list of (un)supported features can be found. -Please read this list if you intend to use this driver. +Please read this list if you intend to use this JDBC driver. If you feel some features are missing or have encountered an issue/bug, please let us know at our bugtracker: @@ -59,19 +59,20 @@ Currently implemented JDBC 4.1 interface - createArrayOf, createNClob, createStruct, createSQLXML - createStatement with result set holdability - prepareCall (CallableStatement is not supported) + see also: https://www.monetdb.org/bugzilla/show_bug.cgi?id=6402 - prepareStatement with array of column indices or column names - - setHoldability (close/hold cursors over commit is not - configurable) - NOTE: be sure to check for warnings after setting concurrencies or - isolation levels; MonetDB currently does not support anything - else but "fully serializable" transactions. + - setHoldability (close/hold cursors over commit is not configurable) + + NOTE: be sure to check for warnings after setting concurrencies or + isolation levels; MonetDB currently does not support anything + else but "fully serializable" transactions. * java.sql.DatabaseMetaData * java.sql.Statement The next features/methods are NOT implemented: - cancel (query execution cannot be terminated, once started) - see also: https://www.monetdb.org/bugzilla/show_bug.cgi?id=6222 + see also: https://www.monetdb.org/bugzilla/show_bug.cgi?id=6222 - execute with column indices or names - executeUpdate with column indices or names - setMaxFieldSize
--- a/src/main/java/nl/cwi/monetdb/jdbc/MonetBlob.java +++ b/src/main/java/nl/cwi/monetdb/jdbc/MonetBlob.java @@ -18,16 +18,18 @@ import java.sql.SQLFeatureNotSupportedEx import java.util.Arrays; /** - * The MonetBlob class implements the {@link java.sql.Blob} interface. Because - * MonetDB/SQL currently has no support for streams, this class is a + * The MonetBlob class implements the {@link java.sql.Blob} interface. + * + * Because MonetDB/SQL currently has no support for streams, this class is a * shallow wrapper of a byte[]. It is more or less supplied to * enable an application that depends on it to run. It may be obvious * that it is a real resource expensive workaround that contradicts the * benefits for a Blob: avoidance of huge resource consumption. + * <b>Use of this class is highly discouraged.</b> * * @author Fabian Groffen */ -public class MonetBlob implements Blob, Serializable, Comparable<MonetBlob> { +public final class MonetBlob implements Blob, Serializable, Comparable<MonetBlob> { private byte[] buffer; @@ -63,8 +65,13 @@ public class MonetBlob implements Blob, * This method frees the Blob object and releases the resources that it holds. The object is invalid once the * free method is called. * - * After free has been called, any attempt to invoke a method other than free will result in a SQLException being - * thrown. If free is called multiple times, the subsequent calls to free are treated as a no-op. + * After free has been called, any attempt to invoke a method other + * than free will result in a SQLException being thrown. If free is + * called multiple times, the subsequent calls to free are treated + * as a no-op. + * + * @throws SQLException if an error occurs releasing the Blob's + * resources */ @Override public void free() throws SQLException { @@ -87,23 +94,27 @@ public class MonetBlob implements Blob, * Returns an InputStream object that contains a partial Blob value, starting with the byte specified by pos, * which is length bytes in length. * - * @param pos the offset to the first byte of the partial value to be retrieved. The first byte in the Blob is at - * position 1 - * @param length the length in bytes of the partial value to be retrieved - * @return InputStream through which the partial Blob value can be read. - * @throws SQLException if pos is less than 1 or if pos is greater than the number of bytes in the Blob or if pos + - * length is greater than the number of bytes in the Blob + * @param pos the offset to the first byte of the partial value to + * be retrieved. The first byte in the Blob is at position 1 + * @param length the length in bytes of the partial value to be + * retrieved + * @return InputStream through which the partial Blob value can be + * read. + * @throws SQLException if pos is less than 1 or if pos is + * greater than the number of bytes in the Blob or if pos + + * length is greater than the number of bytes in the Blob */ @Override public InputStream getBinaryStream(long pos, long length) throws SQLException { checkBufIsNotNull(); - if (pos < 1) - throw new SQLException("pos is less than 1", "M1M05"); - if (pos - 1 > buffer.length) - throw new SQLException("pos is greater than the number of bytes in the Blob", "M1M05"); - if (pos - 1 + length > buffer.length) - throw new SQLException("pos + length is greater than the number of bytes in the Blob", "M1M05"); - return new ByteArrayInputStream(buffer, (int)(pos - 1), (int)length); + if (pos < 1 || pos > buffer.length) { + throw new SQLException("Invalid pos value: " + pos, "M1M05"); + } + if (length < 0 || pos - 1 + length > buffer.length) { + throw new SQLException("Invalid length value: " + length, "M1M05"); + } + + return new ByteArrayInputStream(buffer, (int) pos - 1, (int) length); } /** @@ -120,6 +131,13 @@ public class MonetBlob implements Blob, @Override public byte[] getBytes(long pos, int length) throws SQLException { checkBufIsNotNull(); + if (pos < 1 || pos > buffer.length) { + throw new SQLException("Invalid pos value: " + pos, "M1M05"); + } + if (length < 0 || pos - 1 + length > buffer.length) { + throw new SQLException("Invalid length value: " + length, "M1M05"); + } + try { return java.util.Arrays.copyOfRange(buffer, (int) pos - 1, (int) pos - 1 + length); } catch (IndexOutOfBoundsException e) { @@ -150,6 +168,9 @@ public class MonetBlob implements Blob, */ @Override public long position(Blob pattern, long start) throws SQLException { + if (pattern == null) { + throw new SQLException("Missing pattern object", "M1M05"); + } return position(pattern.getBytes(1L, (int)pattern.length()), start); } @@ -165,14 +186,22 @@ public class MonetBlob implements Blob, @Override public long position(byte[] pattern, long start) throws SQLException { checkBufIsNotNull(); + if (pattern == null) { + throw new SQLException("Missing pattern object", "M1M05"); + } + if (start < 1 || start > buffer.length) { + throw new SQLException("Invalid start value: " + start, "M1M05"); + } try { - for (int i = (int)(start - 1); i < buffer.length - pattern.length; i++) { + final int patternLength = pattern.length; + final int bufLength = buffer.length; + for (int i = (int)(start - 1); i < bufLength - patternLength; i++) { int j; - for (j = 0; j < pattern.length; j++) { + for (j = 0; j < patternLength; j++) { if (buffer[i + j] != pattern[j]) break; } - if (j == pattern.length) + if (j == patternLength) return i; } } catch (IndexOutOfBoundsException e) { @@ -208,10 +237,14 @@ public class MonetBlob implements Blob, * @param pos the position in the BLOB object at which to start writing * @param bytes the array of bytes to be written to the BLOB value that this Blob object represents * @return the number of bytes written - * @throws SQLException if there is an error accessing the BLOB value + * @throws SQLException if there is an error accessing the + * BLOB value or if pos is less than 1 */ @Override public int setBytes(long pos, byte[] bytes) throws SQLException { + if (bytes == null) { + throw new SQLException("Missing bytes[] object", "M1M05"); + } return setBytes(pos, bytes, 1, bytes.length); } @@ -226,11 +259,25 @@ public class MonetBlob implements Blob, * @param offset the offset into the array bytes at which to start reading the bytes to be set * @param len the number of bytes to be written to the BLOB value from the array of bytes bytes * @return the number of bytes written - * @throws SQLException if there is an error accessing the BLOB value + * @throws SQLException if there is an error accessing the + * BLOB value or if pos is less than 1 */ @Override public int setBytes(long pos, byte[] bytes, int offset, int len) throws SQLException { checkBufIsNotNull(); + if (bytes == null) { + throw new SQLException("Missing bytes[] object", "M1M05"); + } + if (pos < 1 || pos > Integer.MAX_VALUE) { + throw new SQLException("Invalid pos value: " + pos, "M1M05"); + } + if (len < 0 || pos + len > buffer.length) { + throw new SQLException("Invalid len value: " + len, "M1M05"); + } + if (offset < 0 || offset > bytes.length) { + throw new SQLException("Invalid offset value: " + offset, "M1M05"); + } + try { /* transactions? what are you talking about? */ System.arraycopy(bytes, offset - 1 + (int) pos, buffer, (int) pos, len - (int) pos); @@ -249,6 +296,9 @@ public class MonetBlob implements Blob, @Override public void truncate(long len) throws SQLException { checkBufIsNotNull(); + if (len < 0 || len > buffer.length) { + throw new SQLException("Invalid len value: " + len, "M1M05"); + } if (buffer.length > len) { byte[] newbuf = new byte[(int)len]; System.arraycopy(buffer, 0, newbuf, 0, (int) len);
--- a/src/main/java/nl/cwi/monetdb/jdbc/MonetClob.java +++ b/src/main/java/nl/cwi/monetdb/jdbc/MonetClob.java @@ -14,8 +14,9 @@ import java.sql.SQLException; import java.sql.SQLFeatureNotSupportedException; /** - * The MonetClob class implements the {@link java.sql.Clob} interface. Because - * MonetDB/SQL currently has no support for streams, this class is a + * The MonetClob class implements the {@link java.sql.Clob} interface. + * + * Because MonetDB/SQL currently has no support for streams, this class is a * shallow wrapper of a {@link StringBuilder}. It is more or less supplied to * enable an application that depends on it to run. It may be obvious * that it is a real resource expensive workaround that contradicts the @@ -24,7 +25,7 @@ import java.sql.SQLFeatureNotSupportedEx * * @author Fabian Groffen */ -public class MonetClob implements Clob, Serializable, Comparable<MonetClob> { +public final class MonetClob implements Clob, Serializable, Comparable<MonetClob> { private final StringBuilder buffer; @@ -64,7 +65,9 @@ public class MonetClob implements Clob, * Retrieves the CLOB value designated by this Clob object as an ascii stream. * * @return a java.io.InputStream object containing the CLOB data - * @throws SQLException if there is an error accessing the CLOB value + * @throws SQLException - if there is an error accessing the CLOB value + * @throws SQLFeatureNotSupportedException this JDBC driver does + * not support this method */ @Override public InputStream getAsciiStream() throws SQLException { @@ -79,7 +82,7 @@ public class MonetClob implements Clob, * (or as a stream of characters). * * @return a java.io.Reader object containing the CLOB data - * @throws SQLException if there is an error accessing the CLOB value + * @throws SQLException - if there is an error accessing the CLOB value */ @Override public Reader getCharacterStream() throws SQLException { @@ -95,11 +98,19 @@ public class MonetClob implements Clob, * Clob is at position 1. * @param length the length in characters of the partial value to be retrieved. * @return Reader through which the partial Clob value can be read. - * @throws SQLException if there is an error accessing the CLOB value + * @throws SQLException - if pos is less than 1 + * or if pos is greater than the number of characters in the Clob + * or if pos + length is greater than the number of characters in the Clob */ @Override public Reader getCharacterStream(long pos, long length) throws SQLException { checkBufIsNotNull(); + if (pos < 1 || pos > buffer.length()) { + throw new SQLException("Invalid pos value: " + pos, "M1M05"); + } + if (length < 0 || pos -1 + length > buffer.length()) { + throw new SQLException("Invalid length value: " + length, "M1M05"); + } return new StringReader(getSubString(pos, (int)length)); } @@ -109,16 +120,26 @@ public class MonetClob implements Clob, * * @param pos the first character of the substring to be extracted. The first character is at position 1. * @param length the number of consecutive characters to be copied - * @return a String that is the specified substring in the CLOB value designated by this Clob object - * @throws SQLException if there is an error accessing the CLOB value + * @return a String that is the specified substring in the + * CLOB value designated by this Clob object + * @throws SQLException - if pos is less than 1 + * or if pos is greater than the number of characters in the Clob + * or if pos + length is greater than the number of characters in the Clob + * @throws SQLException - if there is an error accessing the CLOB value */ @Override public String getSubString(long pos, int length) throws SQLException { checkBufIsNotNull(); + if (pos < 1 || pos > buffer.length()) { + throw new SQLException("Invalid pos value: " + pos, "M1M05"); + } + if (length < 0 || pos -1 + length > buffer.length()) { + throw new SQLException("Invalid length value: " + length, "M1M05"); + } try { return buffer.substring((int)(pos - 1), (int)(pos - 1 + length)); } catch (IndexOutOfBoundsException e) { - throw new SQLException(e.getMessage()); + throw new SQLException(e.getMessage(), "M1M05"); } } @@ -139,14 +160,18 @@ public class MonetClob implements Clob, * The search begins at position start. * * @param searchstr the Clob object for which to search - * @param start the position at which to begin searching; the first position is 1 - * @return the position at which the Clob object appears or -1 if it is not present; the first position is 1 - * @throws SQLException if there is an error accessing the CLOB value + * @param start the position at which to begin searching; + * the first position is 1 + * @return the position at which the Clob object appears or + * -1 if it is not present; the first position is 1 + * @throws SQLException - if there is an error accessing the CLOB value */ @Override public long position(Clob searchstr, long start) throws SQLException { - checkBufIsNotNull(); - return position(searchstr.getSubString(1L, (int)(searchstr.length())), start); + if (searchstr == null) { + throw new SQLException("Missing searchstr object", "M1M05"); + } + return position(searchstr.toString(), start); } /** @@ -154,81 +179,147 @@ public class MonetClob implements Clob, * represented by this Clob object. The search begins at position start. * * @param searchstr the substring for which to search - * @param start the position at which to begin searching; the first position is 1 - * @return the position at which the substring appears or -1 if it is not present; the first position is 1 - * @throws SQLException if there is an error accessing the CLOB value + * @param start the position at which to begin searching; + * the first position is 1 + * @return the position at which the substring appears or + * -1 if it is not present; the first position is 1 + * @throws SQLException - if there is an error accessing the CLOB value */ @Override public long position(String searchstr, long start) throws SQLException { checkBufIsNotNull(); + if (searchstr == null) { + throw new SQLException("Missing searchstr object", "M1M05"); + } + if (start < 1 || start > buffer.length()) { + throw new SQLException("Invalid start value: " + start, "M1M05"); + } return (long)(buffer.indexOf(searchstr, (int)(start - 1))); } + /** + * Retrieves a stream to be used to write Ascii characters to the CLOB value that this + * Clob object represents, starting at position pos. Characters written to the stream + * will overwrite the existing characters in the Clob object starting at the position pos. + * If the end of the Clob value is reached while writing characters to the stream, + * then the length of the Clob value will be increased to accomodate the extra characters. + * + * Note: If the value specified for pos is greater then the length+1 of the CLOB value + * then the behavior is undefined. Some JDBC drivers may throw a SQLException while + * other drivers may support this operation. + * + * @param pos - the position at which to start writing to this CLOB object; The first position is 1 + * @return the stream to which ASCII encoded characters can be written + * @throws SQLException - if there is an error accessing the CLOB value or if pos is less than 1 + * @throws SQLFeatureNotSupportedException - if the JDBC driver does not support this method + */ @Override public OutputStream setAsciiStream(long pos) throws SQLException { throw new SQLFeatureNotSupportedException("Method setAsciiStream(long pos) not supported", "0A000"); } + /** + * Retrieves a stream to be used to write a stream of Unicode characters to the CLOB value that + * this Clob object represents, starting at position pos. Characters written to the stream + * will overwrite the existing characters in the Clob object starting at the position pos. + * If the end of the Clob value is reached while writing characters to the stream, + * then the length of the Clob value will be increased to accomodate the extra characters. + * + * Note: If the value specified for pos is greater then the length+1 of the CLOB value + * then the behavior is undefined. Some JDBC drivers may throw a SQLException while + * other drivers may support this operation. + * + * @param pos - the position at which to start writing to this CLOB object; The first position is 1 + * @return the stream to which Unicode encoded characters can be written + * @throws SQLException - if there is an error accessing the CLOB value or if pos is less than 1 + * @throws SQLFeatureNotSupportedException - if the JDBC driver does not support this method + */ @Override public Writer setCharacterStream(long pos) throws SQLException { throw new SQLFeatureNotSupportedException("Method setCharacterStream(long pos) not supported", "0A000"); } /** - * Writes the given Java String to the CLOB value that this Clob object designates at the position pos. + * Writes the given Java String to the CLOB value that this Clob object designates at the position pos. + * The string will overwrite the existing characters in the Clob object starting at the position pos. + * If the end of the Clob value is reached while writing the given string, then the length of + * the Clob value will be increased to accomodate the extra characters. * * @param pos the position at which to start writing to the CLOB value that this Clob object represents * @param str the string to be written to the CLOB value that this Clob designates * @return the number of characters written - * @throws SQLException if there is an error accessing the CLOB value + * @throws SQLException if there is an error accessing the + * CLOB value or if pos is less than 1 */ @Override public int setString(long pos, String str) throws SQLException { - return setString(pos, str, 1, str.length()); + if (str == null) { + throw new SQLException("Missing str object", "M1M05"); + } + return setString(pos, str, 0, str.length()); } /** * Writes len characters of str, starting at character offset, to the CLOB value that this Clob represents. + * The string will overwrite the existing characters in the Clob object starting at the position pos. + * If the end of the Clob value is reached while writing the given string, then the length of + * the Clob value will be increased to accomodate the extra characters. * * @param pos the position at which to start writing to this CLOB object * @param str the string to be written to the CLOB value that this Clob object represents * @param offset the offset into str to start reading the characters to be written * @param len the number of characters to be written * @return the number of characters written - * @throws SQLException if there is an error accessing the CLOB value + * @throws SQLException if there is an error accessing the + * CLOB value or if pos is less than 1 */ @Override public int setString(long pos, String str, int offset, int len) throws SQLException { checkBufIsNotNull(); - int buflen = buffer.length(); - int retlen = Math.min(buflen, (int)(pos - 1 + len)); - if (retlen > 0) { - buffer.replace((int)(pos - 1), (int)(pos + retlen), str.substring(offset - 1, (offset + len))); - return retlen; - } else { - return 0; + if (str == null) { + throw new SQLException("Missing str object", "M1M05"); + } + if (pos < 1 || pos > Integer.MAX_VALUE) { + throw new SQLException("Invalid pos value: " + pos, "M1M05"); + } + if (offset < 0 || offset > str.length()) { + throw new SQLException("Invalid offset value: " + offset, "M1M05"); } + if (len < 1 || (offset + len) > str.length()) { + throw new SQLException("Invalid len value: " + len, "M1M05"); + } + + int ipos = (int) pos; + if ((ipos + len) > buffer.capacity()) { + buffer.ensureCapacity(ipos + len); + } + buffer.replace(ipos - 1, ipos + len, str.substring(offset, (offset + len))); + return len; } /** * Truncates the CLOB value that this Clob designates to have a length of len characters. * - * @param len the length, in bytes, to which the CLOB value should be truncated - * @throws SQLException if there is an error accessing the CLOB value + * @param len the length, in bytes, to which the CLOB value + * should be truncated + * @throws SQLException if there is an error accessing the + * CLOB value or if len is less than 0 */ @Override public void truncate(long len) throws SQLException { checkBufIsNotNull(); - buffer.setLength((int) len); + if (len < 0 || len > buffer.length()) { + throw new SQLException("Invalid len value: " + len, "M1M05"); + } + buffer.delete((int)len, buffer.length()); } /** * Returns the String behind this Clob. This is a MonetClob extension that does not violate nor is described in * the Clob interface. * - * @return the String this Clob wraps. + * @return the String this Clob wraps or "null" when this Clob is freed. */ - @Override public String toString() { if (buffer == null || buffer.length() == 0) return "null";
--- a/src/main/java/nl/cwi/monetdb/jdbc/MonetConnection.java +++ b/src/main/java/nl/cwi/monetdb/jdbc/MonetConnection.java @@ -170,6 +170,15 @@ public abstract class MonetConnection ex */ public abstract int getDefFetchsize(); + // Internal cache for determining if system table sys.privilege_codes (new as of Jul2017 release) exists on server + private boolean queriedPrivilege_codesTable = false; + private boolean hasPrivilege_codesTable = false; + + // Internal cache for determining if system table sys.comments (new as of Mar2018 release) exists on server + private boolean queriedCommentsTable = false; + private boolean hasCommentsTable = false; + + /** * Gets the initial value for the StringBuilder size. * @@ -1511,6 +1520,69 @@ public abstract class MonetConnection ex } /** + * Internal utility method to query the server to find out if it has + * the system table sys.comments (which is new as of Mar2018 release). + * The result is cached and reused, so that we only test the query once per connection. + * This method is used by methods from MonetDatabaseMetaData. + */ + boolean commentsTableExists() { + if (!queriedCommentsTable) { + hasCommentsTable = existsSysTable("comments"); + queriedCommentsTable = true; // set flag, so the querying is done only at first invocation. + } + return hasCommentsTable; + } + + /** + * Internal utility method to query the server to find out if it has + * the system table sys.privilege_codes (which is new as of Jul2017 release). + * The result is cached and reused, so that we only test the query once per connection. + * This method is used by methods from MonetDatabaseMetaData. + */ + boolean privilege_codesTableExists() { + if (!queriedPrivilege_codesTable) { + hasPrivilege_codesTable = existsSysTable("privilege_codes"); + queriedPrivilege_codesTable = true; // set flag, so the querying is done only at first invocation. + } + return hasPrivilege_codesTable; + } + + /** + * Internal utility method to query the server to find out if it has the system table sys.<tablename>. + */ + private boolean existsSysTable(String tablename) { + boolean exists = false; + Statement stmt = null; + ResultSet rs = null; + try { + stmt = createStatement(); + if (stmt != null) { + rs = stmt.executeQuery("SELECT id FROM sys._tables WHERE name = '" + + tablename + + "' AND schema_id IN (SELECT id FROM sys.schemas WHERE name = 'sys')"); + if (rs != null) { + exists = rs.next(); // if a row is available it exists, else not + } + } + } catch (SQLException se) { + /* ignore */ + } finally { + if (rs != null) { + try { + rs.close(); + } catch (SQLException e) { /* ignore */ } + } + if (stmt != null) { + try { + stmt.close(); + } catch (SQLException e) { /* ignore */ } + } + } +// for debug: System.out.println("testTableExists(" + tablename + ") returns: " + exists); + return exists; + } + + /** * Sends the given string to MonetDB as special transaction command. * All possible returned information is discarded. * Encountered errors are reported.
--- a/src/main/java/nl/cwi/monetdb/jdbc/MonetDatabaseMetaData.java +++ b/src/main/java/nl/cwi/monetdb/jdbc/MonetDatabaseMetaData.java @@ -23,7 +23,7 @@ import java.util.ArrayList; * A DatabaseMetaData object suitable for the MonetDB database. * * @author Fabian Groffen, Martin van Dinther - * @version 0.6 + * @version 0.7 */ public class MonetDatabaseMetaData extends MonetWrapper implements DatabaseMetaData { private Connection con; @@ -300,7 +300,7 @@ public class MonetDatabaseMetaData exten /** * Does the database treat mixed case unquoted SQL identifiers * as case sensitive and as a result store them in mixed case? - * A JDBC-Compliant driver will always return false. + * A JDBC Compliant <sup><font size=-2>TM</font></sup> driver always returns false. * * @return false */ @@ -344,8 +344,8 @@ public class MonetDatabaseMetaData exten /** * Does the database treat mixed case quoted SQL identifiers as - * case sensitive and as a result store them in mixed case? A - * JDBC compliant driver will always return true. + * case sensitive and as a result store them in mixed case? + * A JDBC Compliant <sup><font size=-2>TM</font></sup> driver always returns true. * * @return true if so */ @@ -388,9 +388,10 @@ public class MonetDatabaseMetaData exten } /** - * What is the string used to quote SQL identifiers? This returns - * a space if identifier quoting isn't supported. A JDBC Compliant - * driver will always use a double quote character. + * What is the string used to quote SQL identifiers? + * This returns a space if identifier quoting isn't supported. + * A JDBC Compliant <sup><font size=-2>TM</font></sup> driver + * will always use a double quote character. * * @return the quoting string */ @@ -410,7 +411,7 @@ public class MonetDatabaseMetaData exten String keywords = getConcatenatedStringFromQuery("SELECT \"keyword\" FROM \"sys\".\"keywords\" ORDER BY 1"); /* An old MonetDB server (pre Jul2015 release) will not have a table sys.keywords and return an empty String */ - return (keywords.length() > 0) ? keywords : + return (keywords.isEmpty()) ? /* for old servers return static list (as returned in clients/odbc/driver/SQLGetInfo.c case SQL_KEYWORDS:) */ "ADMIN,AFTER,AGGREGATE,ALWAYS,ASYMMETRIC,ATOMIC," + "AUTO_INCREMENT,BEFORE,BIGINT,BIGSERIAL,BINARY,BLOB," + @@ -430,7 +431,8 @@ public class MonetDatabaseMetaData exten "UNBOUNDED,UNCOMMITTED,UNENCRYPTED,WHILE,XMLAGG," + "XMLATTRIBUTES,XMLCOMMENT,XMLCONCAT,XMLDOCUMENT," + "XMLELEMENT,XMLFOREST,XMLNAMESPACES,XMLPARSE,XMLPI," + - "XMLQUERY,XMLSCHEMA,XMLTEXT,XMLVALIDATE"; + "XMLQUERY,XMLSCHEMA,XMLTEXT,XMLVALIDATE" + : keywords; } /** @@ -508,6 +510,9 @@ public class MonetDatabaseMetaData exten "WHERE \"id\" NOT IN (SELECT \"func_id\" FROM \"sys\".\"args\" WHERE \"number\" = 1)" + " AND \"id\" IN (SELECT \"function_id\" FROM \"sys\".\"systemfunctions\")" + " AND \"type\" = 1" + // only scalar functions + // exclude functions which belong to the 'mtime' module + " AND \"mod\" <> 'mtime'" + + " 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 'convert'" + @@ -521,9 +526,8 @@ public class MonetDatabaseMetaData exten @Override public String getTimeDateFunctions() { - String match = - "('date', 'time', 'timestamp', 'timetz', 'timestamptz', 'sec_interval', 'month_interval') )"; - return getConcatenatedStringFromQuery(FunctionsSelect + FunctionsWhere + match + AddFunctionsMaxMin + FunctionsOrderBy1); + String wherePart = "WHERE \"mod\" = 'mtime' OR \"name\" IN ('localtime', 'localtimestamp') UNION SELECT 'extract' UNION SELECT 'now'"; + return getConcatenatedStringFromQuery(FunctionsSelect + wherePart + AddFunctionsMaxMin + FunctionsOrderBy1); } /** @@ -573,8 +577,8 @@ public class MonetDatabaseMetaData exten * Is column aliasing supported? * * <p>If so, the SQL AS clause can be used to provide names for - * computed columns or to provide alias names for columns as - * required. A JDBC Compliant driver always returns true. + * computed columns or to provide alias names for columns as required. + * A JDBC Compliant <sup><font size=-2>TM</font></sup> driver always returns true. * * <p>e.g. * @@ -592,8 +596,8 @@ public class MonetDatabaseMetaData exten } /** - * Are concatenations between NULL and non-NULL values NULL? A - * JDBC Compliant driver always returns true + * Are concatenations between NULL and non-NULL values NULL? + * A JDBC Compliant <sup><font size=-2>TM</font></sup> driver always returns true. * * @return true if so */ @@ -758,8 +762,8 @@ public class MonetDatabaseMetaData exten } /** - * Are table correlation names supported? A JDBC Compliant - * driver always returns true. + * Are table correlation names supported? + * A JDBC Compliant <sup><font size=-2>TM</font></sup> driver always returns true. * * @return true if so */ @@ -839,8 +843,8 @@ public class MonetDatabaseMetaData exten } /** - * Is the escape character in "LIKE" clauses supported? A - * JDBC compliant driver always returns true. + * Is the escape character in "LIKE" clauses supported? + * A JDBC Compliant <sup><font size=-2>TM</font></sup> driver always returns true. * * @return true if so */ @@ -872,8 +876,8 @@ public class MonetDatabaseMetaData exten } /** - * Can columns be defined as non-nullable. A JDBC Compliant driver - * always returns true. + * Can columns be defined as non-nullable. + * A JDBC Compliant <sup><font size=-2>TM</font></sup> driver always returns true. * * @return true if so */ @@ -921,8 +925,7 @@ public class MonetDatabaseMetaData exten /** * Does this driver support the ANSI-92 entry level SQL grammar? - * All JDBC Compliant drivers must return true. We should be this - * compliant, so let's 'act' like we are. + * All JDBC Compliant <sup><font size=-2>TM</font></sup> drivers must return true. * * @return true if so */ @@ -1197,8 +1200,9 @@ public class MonetDatabaseMetaData exten } /** - * Are subqueries in comparison expressions supported? A JDBC - * Compliant driver always returns true. MonetDB also supports this + * Are subqueries in comparison expressions supported? + * A JDBC Compliant <sup><font size=-2>TM</font></sup> driver always returns true. + * MonetDB also supports this * * @return true if so; false otherwise */ @@ -1208,8 +1212,8 @@ public class MonetDatabaseMetaData exten } /** - * Are subqueries in 'exists' expressions supported? A JDBC - * Compliant driver always returns true. + * Are subqueries in 'exists' expressions supported? + * A JDBC Compliant <sup><font size=-2>TM</font></sup> driver always returns true. * * @return true if so; false otherwise */ @@ -1219,8 +1223,8 @@ public class MonetDatabaseMetaData exten } /** - * Are subqueries in 'in' statements supported? A JDBC - * Compliant driver always returns true. + * Are subqueries in 'in' statements supported? + * A JDBC Compliant <sup><font size=-2>TM</font></sup> driver always returns true. * * @return true if so; false otherwise */ @@ -1230,8 +1234,8 @@ public class MonetDatabaseMetaData exten } /** - * Are subqueries in quantified expressions supported? A JDBC - * Compliant driver always returns true. + * Are subqueries in quantified expressions supported? + * A JDBC Compliant <sup><font size=-2>TM</font></sup> driver always returns true. * * (No idea what this is, but we support a good deal of * subquerying.) @@ -1244,8 +1248,8 @@ public class MonetDatabaseMetaData exten } /** - * Are correlated subqueries supported? A JDBC Compliant driver - * always returns true. + * Are correlated subqueries supported? + * A JDBC Compliant <sup><font size=-2>TM</font></sup> driver always returns true. * * (a.k.a. subselect in from?) * @@ -1594,7 +1598,7 @@ public class MonetDatabaseMetaData exten /** * Does the database support the given transaction isolation level? - * We only support TRANSACTION_READ_COMMITTED as far as I know + * We only support TRANSACTION_SERIALIZABLE as far as I know * * @param level the values are defined in java.sql.Connection * @return true if so @@ -1704,32 +1708,37 @@ public class MonetDatabaseMetaData exten String procedureNamePattern ) throws SQLException { + boolean useCommentsTable = ((MonetConnection)con).commentsTableExists(); StringBuilder query = new StringBuilder(980); - query.append("SELECT cast(null as varchar(1)) AS \"PROCEDURE_CAT\", " + + query.append("SELECT cast(null as char(1)) AS \"PROCEDURE_CAT\", " + "\"schemas\".\"name\" AS \"PROCEDURE_SCHEM\", " + "\"functions\".\"name\" AS \"PROCEDURE_NAME\", " + "cast(null as char(1)) AS \"Field4\", " + "cast(null as char(1)) AS \"Field5\", " + - "cast(null as char(1)) AS \"Field6\", " + - "cast(\"functions\".\"func\" as varchar(9999)) AS \"REMARKS\", " + + "cast(null as char(1)) AS \"Field6\", ") + .append(useCommentsTable ? "COALESCE(\"comments\".\"remark\", cast(\"functions\".\"func\" as varchar(9999)))" : "cast(\"functions\".\"func\" as varchar(9999))").append(" AS \"REMARKS\", " + // in MonetDB procedures have no return value by design. - "CAST(").append(DatabaseMetaData.procedureNoResult).append(" AS smallint) AS \"PROCEDURE_TYPE\", " + + "cast(").append(DatabaseMetaData.procedureNoResult).append(" AS smallint) AS \"PROCEDURE_TYPE\", " + // only the id value uniquely identifies a procedure. Include it to be able to differentiate between multiple overloaded procedures with the same name "cast(\"functions\".\"id\" as varchar(10)) AS \"SPECIFIC_NAME\" " + - "FROM \"sys\".\"functions\" JOIN \"sys\".\"schemas\" ON (\"functions\".\"schema_id\" = \"schemas\".\"id\") " + + "FROM \"sys\".\"functions\" JOIN \"sys\".\"schemas\" ON (\"functions\".\"schema_id\" = \"schemas\".\"id\") "); + if (useCommentsTable) { + query.append("LEFT OUTER JOIN \"sys\".\"comments\" ON (\"functions\".\"id\" = \"comments\".\"id\") "); + } // include procedures only (type = 2). Others will be returned via getFunctions() - "WHERE \"functions\".\"type\" = 2"); - - if (catalog != null && catalog.length() > 0) { + query.append("WHERE \"functions\".\"type\" = 2"); + + if (catalog != null && !catalog.isEmpty()) { // none empty catalog selection. // as we do not support catalogs this always results in no rows returned query.append(" AND 1 = 0"); - } - if (schemaPattern != null) { - query.append(" AND \"schemas\".\"name\" ").append(composeMatchPart(schemaPattern)); - } - if (procedureNamePattern != null) { - query.append(" AND \"functions\".\"name\" ").append(composeMatchPart(procedureNamePattern)); + } else { + if (schemaPattern != null) { + query.append(" AND \"schemas\".\"name\" ").append(composeMatchPart(schemaPattern)); + } + if (procedureNamePattern != null) { + query.append(" AND \"functions\".\"name\" ").append(composeMatchPart(procedureNamePattern)); + } } query.append(" ORDER BY \"PROCEDURE_SCHEM\", \"PROCEDURE_NAME\", \"SPECIFIC_NAME\""); @@ -1811,51 +1820,53 @@ public class MonetDatabaseMetaData exten String columnNamePattern ) throws SQLException { StringBuilder query = new StringBuilder(2900); - query.append("SELECT cast(null as varchar(1)) AS \"PROCEDURE_CAT\", " + + query.append("SELECT cast(null as char(1)) AS \"PROCEDURE_CAT\", " + "\"schemas\".\"name\" AS \"PROCEDURE_SCHEM\", " + "\"functions\".\"name\" AS \"PROCEDURE_NAME\", " + "\"args\".\"name\" AS \"COLUMN_NAME\", " + - "CAST(CASE \"args\".\"inout\"" + + "cast(CASE \"args\".\"inout\"" + " WHEN 0 THEN (CASE \"args\".\"number\" WHEN 0 THEN ").append(DatabaseMetaData.procedureColumnReturn).append(" ELSE ").append(DatabaseMetaData.procedureColumnOut).append(" END)" + " WHEN 1 THEN ").append(DatabaseMetaData.procedureColumnIn) .append(" ELSE ").append(DatabaseMetaData.procedureColumnUnknown).append(" END AS smallint) AS \"COLUMN_TYPE\", " + - "CAST(").append(MonetDriver.getSQLTypeMap("\"args\".\"type\"")).append(" AS int) AS \"DATA_TYPE\", " + + "cast(").append(MonetDriver.getSQLTypeMap("\"args\".\"type\"")).append(" AS int) AS \"DATA_TYPE\", " + "\"args\".\"type\" AS \"TYPE_NAME\", " + "CASE \"args\".\"type\" WHEN 'tinyint' THEN 3 WHEN 'smallint' THEN 5 WHEN 'int' THEN 10 WHEN 'bigint' THEN 19 WHEN 'hugeint' THEN 38 WHEN 'oid' THEN 19 WHEN 'wrd' THEN 19 ELSE \"args\".\"type_digits\" END AS \"PRECISION\", " + "CASE \"args\".\"type\" WHEN 'tinyint' THEN 1 WHEN 'smallint' THEN 2 WHEN 'int' THEN 4 WHEN 'bigint' THEN 8 WHEN 'hugeint' THEN 16 WHEN 'oid' THEN 8 WHEN 'wrd' THEN 8 ELSE \"args\".\"type_digits\" END AS \"LENGTH\", " + - "CAST(CASE WHEN \"args\".\"type\" IN ('tinyint','smallint','int','bigint','hugeint','oid','wrd','decimal','numeric','time','timetz','timestamp','timestamptz','sec_interval') THEN \"args\".\"type_scale\" ELSE NULL END AS smallint) AS \"SCALE\", " + - "CAST(CASE WHEN \"args\".\"type\" IN ('tinyint','smallint','int','bigint','hugeint','oid','wrd','decimal','numeric') THEN 10 WHEN \"args\".\"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\", " + - "CAST(null as char(1)) AS \"COLUMN_DEF\", " + - "CAST(0 as int) AS \"SQL_DATA_TYPE\", " + - "CAST(0 as int) AS \"SQL_DATETIME_SUB\", " + - "CAST(CASE WHEN \"args\".\"type\" IN ('char','varchar','clob') THEN \"args\".\"type_digits\" ELSE NULL END as int) AS \"CHAR_OCTET_LENGTH\", " + + "cast(CASE WHEN \"args\".\"type\" IN ('tinyint','smallint','int','bigint','hugeint','oid','wrd','decimal','numeric','time','timetz','timestamp','timestamptz','sec_interval') THEN \"args\".\"type_scale\" ELSE NULL END AS smallint) AS \"SCALE\", " + + "cast(CASE WHEN \"args\".\"type\" IN ('tinyint','smallint','int','bigint','hugeint','oid','wrd','decimal','numeric') THEN 10 WHEN \"args\".\"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\", " + + "cast(null as char(1)) AS \"COLUMN_DEF\", " + + "cast(0 as int) AS \"SQL_DATA_TYPE\", " + + "cast(0 as int) AS \"SQL_DATETIME_SUB\", " + + "cast(CASE WHEN \"args\".\"type\" IN ('char','varchar','clob') THEN \"args\".\"type_digits\" ELSE NULL END as int) AS \"CHAR_OCTET_LENGTH\", " + // in MonetDB procedures have no return value by design. The arguments in sys.args are numbered from 0 so we must add 1 to comply with the API specification. - "CAST(\"args\".\"number\" + 1 as int) AS \"ORDINAL_POSITION\", " + - "CAST('' as varchar(3)) AS \"IS_NULLABLE\", " + + "cast(\"args\".\"number\" + 1 as int) AS \"ORDINAL_POSITION\", " + + "cast('' as varchar(3)) AS \"IS_NULLABLE\", " + // the specific name contains the function id, in order to be able to match the args to the correct overloaded procedure name "cast(\"functions\".\"id\" as varchar(10)) AS \"SPECIFIC_NAME\" " + - "FROM \"sys\".\"args\", \"sys\".\"functions\", \"sys\".\"schemas\" " + - "WHERE \"args\".\"func_id\" = \"functions\".\"id\" " + - "AND \"functions\".\"schema_id\" = \"schemas\".\"id\" " + + "FROM \"sys\".\"args\" " + + "JOIN \"sys\".\"functions\" ON (\"args\".\"func_id\" = \"functions\".\"id\") " + + "JOIN \"sys\".\"schemas\" ON (\"functions\".\"schema_id\" = \"schemas\".\"id\") " + // include procedures only (type = 2). Others will be returned via getFunctionColumns() - "AND \"functions\".\"type\" = 2"); - - if (catalog != null && catalog.length() > 0) { + "WHERE \"functions\".\"type\" = 2"); + + if (catalog != null && !catalog.isEmpty()) { // none empty catalog selection. // as we do not support catalogs this always results in no rows returned query.append(" AND 1 = 0"); - } - if (schemaPattern != null) { - query.append(" AND \"schemas\".\"name\" ").append(composeMatchPart(schemaPattern)); + } else { + if (schemaPattern != null) { + query.append(" AND \"schemas\".\"name\" ").append(composeMatchPart(schemaPattern)); + } + if (procedureNamePattern != null) { + query.append(" AND \"functions\".\"name\" ").append(composeMatchPart(procedureNamePattern)); + } + if (columnNamePattern != null) { + query.append(" AND \"args\".\"name\" ").append(composeMatchPart(columnNamePattern)); + } } - if (procedureNamePattern != null) { - query.append(" AND \"functions\".\"name\" ").append(composeMatchPart(procedureNamePattern)); - } - if (columnNamePattern != null) { - query.append(" AND \"args\".\"name\" ").append(composeMatchPart(columnNamePattern)); - } + query.append(" ORDER BY \"PROCEDURE_SCHEM\", \"PROCEDURE_NAME\", \"SPECIFIC_NAME\", \"ORDINAL_POSITION\""); return executeMetaDataQuery(query.toString()); @@ -1949,12 +1960,13 @@ public class MonetDatabaseMetaData exten boolean preJul2015 = ("11.19.15".compareTo(getDatabaseProductVersion()) >= 0); /* for debug: System.out.println("getDatabaseProductVersion() is " + getDatabaseProductVersion() + " preJul2015 is " + preJul2015); */ + boolean useCommentsTable = ((MonetConnection)con).commentsTableExists(); StringBuilder query = new StringBuilder(1600); if (preJul2015 && types != null && types.length > 0) { // we need to filter on the constructed "TABLE_TYPE" expression, this is only possible when we use a subquery in the FROM query.append("SELECT * FROM ("); } - query.append("SELECT DISTINCT cast(null as char(1)) AS \"TABLE_CAT\", " + + query.append("SELECT cast(null as char(1)) AS \"TABLE_CAT\", " + "\"schemas\".\"name\" AS \"TABLE_SCHEM\", " + "\"tables\".\"name\" AS \"TABLE_NAME\", "); if (preJul2015) { @@ -1971,13 +1983,17 @@ public class MonetDatabaseMetaData exten } else { query.append("\"table_types\".\"table_type_name\" AS \"TABLE_TYPE\", "); } - query.append("\"tables\".\"query\" AS \"REMARKS\", " + + query.append(useCommentsTable ? "COALESCE(\"comments\".\"remark\", \"tables\".\"query\")" : "\"tables\".\"query\"").append(" AS \"REMARKS\", " + "cast(null as char(1)) AS \"TYPE_CAT\", " + "cast(null as char(1)) AS \"TYPE_SCHEM\", " + "cast(null as char(1)) AS \"TYPE_NAME\", " + "cast(null as char(1)) AS \"SELF_REFERENCING_COL_NAME\", " + "cast(null as char(1)) AS \"REF_GENERATION\" " + - "FROM \"sys\".\"tables\", \"sys\".\"schemas\""); + "FROM \"sys\".\"tables\""); + if (useCommentsTable) { + query.append(" LEFT OUTER JOIN \"sys\".\"comments\" ON (\"tables\".\"id\" = \"comments\".\"id\")"); + } + query.append(", \"sys\".\"schemas\""); if (!preJul2015) { query.append(", \"sys\".\"table_types\""); } @@ -1986,17 +2002,19 @@ public class MonetDatabaseMetaData exten query.append(" AND \"tables\".\"type\" = \"table_types\".\"table_type_id\""); } - if (catalog != null && catalog.length() > 0) { + if (catalog != null && !catalog.isEmpty()) { // none empty catalog selection. // as we do not support catalogs this always results in no rows returned query.append(" AND 1 = 0"); - } - if (schemaPattern != null) { - query.append(" AND \"schemas\".\"name\" ").append(composeMatchPart(schemaPattern)); + } else { + if (schemaPattern != null) { + query.append(" AND \"schemas\".\"name\" ").append(composeMatchPart(schemaPattern)); + } + if (tableNamePattern != null) { + query.append(" AND \"tables\".\"name\" ").append(composeMatchPart(tableNamePattern)); + } } - if (tableNamePattern != null) { - query.append(" AND \"tables\".\"name\" ").append(composeMatchPart(tableNamePattern)); - } + if (types != null && types.length > 0) { if (preJul2015) { query.append(") AS \"getTables\" WHERE \"TABLE_TYPE\" IN ("); @@ -2047,7 +2065,7 @@ public class MonetDatabaseMetaData exten "cast(null as char(1)) AS \"TABLE_CATALOG\" " + "FROM \"sys\".\"schemas\""); - if (catalog != null && catalog.length() > 0) { + if (catalog != null && !catalog.isEmpty()) { // none empty catalog selection. // as we do not support catalogs this always results in no rows returned query.append(" WHERE 1 = 0"); @@ -2194,6 +2212,7 @@ public class MonetDatabaseMetaData exten String columnNamePattern ) throws SQLException { + boolean useCommentsTable = ((MonetConnection)con).commentsTableExists(); StringBuilder query = new StringBuilder(2450); query.append("SELECT cast(null as char(1)) AS \"TABLE_CAT\", " + "\"schemas\".\"name\" AS \"TABLE_SCHEM\", " + @@ -2208,39 +2227,43 @@ public class MonetDatabaseMetaData exten "WHEN \"columns\".\"type\" IN ('int', 'smallint', 'tinyint', 'bigint', 'hugeint', 'float', 'real', 'double', 'oid', 'wrd') THEN 2 " + "ELSE 0 END AS int) AS \"NUM_PREC_RADIX\", " + "cast(CASE \"null\" WHEN true THEN ").append(ResultSetMetaData.columnNullable) - .append(" WHEN false THEN ").append(ResultSetMetaData.columnNoNulls).append(" END AS int) AS \"NULLABLE\", " + - "cast(null AS varchar(1)) AS \"REMARKS\", " + + .append(" WHEN false THEN ").append(ResultSetMetaData.columnNoNulls).append(" END AS int) AS \"NULLABLE\", ") + .append(useCommentsTable ? "\"comments\".\"remark\"" : "cast(null AS varchar(9999))").append(" AS \"REMARKS\", " + "\"columns\".\"default\" AS \"COLUMN_DEF\", " + "cast(0 as int) AS \"SQL_DATA_TYPE\", " + "cast(0 as int) AS \"SQL_DATETIME_SUB\", " + "cast(CASE WHEN \"columns\".\"type\" IN ('char','varchar','clob') THEN \"columns\".\"type_digits\" ELSE NULL END as int) AS \"CHAR_OCTET_LENGTH\", " + "cast(\"columns\".\"number\" + 1 as int) AS \"ORDINAL_POSITION\", " + "cast(CASE \"null\" WHEN true THEN 'YES' WHEN false THEN 'NO' ELSE '' END AS varchar(3)) AS \"IS_NULLABLE\", " + - "cast(null AS varchar(1)) AS \"SCOPE_CATALOG\", " + - "cast(null AS varchar(1)) AS \"SCOPE_SCHEMA\", " + - "cast(null AS varchar(1)) AS \"SCOPE_TABLE\", " + + "cast(null AS char(1)) AS \"SCOPE_CATALOG\", " + + "cast(null AS char(1)) AS \"SCOPE_SCHEMA\", " + + "cast(null AS char(1)) AS \"SCOPE_TABLE\", " + "cast(null AS smallint) AS \"SOURCE_DATA_TYPE\", " + "cast(CASE WHEN \"columns\".\"default\" IS NOT NULL AND \"columns\".\"default\" LIKE 'next value for %' THEN 'YES' ELSE 'NO' END AS varchar(3)) AS \"IS_AUTOINCREMENT\", " + "cast('NO' AS varchar(3)) AS \"IS_GENERATEDCOLUMN\" " + - "FROM \"sys\".\"columns\", " + - "\"sys\".\"tables\", " + - "\"sys\".\"schemas\" " + - "WHERE \"columns\".\"table_id\" = \"tables\".\"id\" " + - "AND \"tables\".\"schema_id\" = \"schemas\".\"id\""); - - if (catalog != null && catalog.length() > 0) { + "FROM \"sys\".\"columns\""); + if (useCommentsTable) { + query.append(" LEFT OUTER JOIN \"sys\".\"comments\" ON (\"columns\".\"id\" = \"comments\".\"id\")"); + } + query.append(", \"sys\".\"tables\"" + + ", \"sys\".\"schemas\" " + + "WHERE \"columns\".\"table_id\" = \"tables\".\"id\"" + + " AND \"tables\".\"schema_id\" = \"schemas\".\"id\""); + + if (catalog != null && !catalog.isEmpty()) { // none empty catalog selection. // as we do not support catalogs this always results in no rows returned query.append(" AND 1 = 0"); - } - if (schemaPattern != null) { - query.append(" AND \"schemas\".\"name\" ").append(composeMatchPart(schemaPattern)); - } - if (tableNamePattern != null) { - query.append(" AND \"tables\".\"name\" ").append(composeMatchPart(tableNamePattern)); - } - if (columnNamePattern != null) { - query.append(" AND \"columns\".\"name\" ").append(composeMatchPart(columnNamePattern)); + } else { + if (schemaPattern != null) { + query.append(" AND \"schemas\".\"name\" ").append(composeMatchPart(schemaPattern)); + } + if (tableNamePattern != null) { + query.append(" AND \"tables\".\"name\" ").append(composeMatchPart(tableNamePattern)); + } + if (columnNamePattern != null) { + query.append(" AND \"columns\".\"name\" ").append(composeMatchPart(columnNamePattern)); + } } query.append(" ORDER BY \"TABLE_SCHEM\", \"TABLE_NAME\", \"ORDINAL_POSITION\""); @@ -2285,14 +2308,16 @@ public class MonetDatabaseMetaData exten String columnNamePattern ) throws SQLException { + boolean usePrivilege_codesTable = ((MonetConnection)con).privilege_codesTableExists(); StringBuilder query = new StringBuilder(1100); query.append("SELECT cast(null as char(1)) AS \"TABLE_CAT\", " + "\"schemas\".\"name\" AS \"TABLE_SCHEM\", " + "\"tables\".\"name\" AS \"TABLE_NAME\", " + "\"columns\".\"name\" AS \"COLUMN_NAME\", " + "\"grantors\".\"name\" AS \"GRANTOR\", " + - "\"grantees\".\"name\" AS \"GRANTEE\", " + - "CAST(CASE \"privileges\".\"privileges\" " + + "\"grantees\".\"name\" AS \"GRANTEE\", ") + .append(usePrivilege_codesTable ? "\"privilege_codes\".\"privilege_code_name\"" : + "cast(CASE \"privileges\".\"privileges\" " + "WHEN 1 THEN 'SELECT' " + "WHEN 2 THEN 'UPDATE' " + "WHEN 4 THEN 'INSERT' " + @@ -2300,8 +2325,8 @@ public class MonetDatabaseMetaData exten "WHEN 16 THEN 'EXECUTE' " + "WHEN 32 THEN 'GRANT' " + "ELSE NULL " + - "END AS varchar(7)) AS \"PRIVILEGE\", " + - "CAST(CASE \"privileges\".\"grantable\" " + + "END AS varchar(7))").append(" AS \"PRIVILEGE\", " + + "cast(CASE \"privileges\".\"grantable\" " + "WHEN 0 THEN 'NO' " + "WHEN 1 THEN 'YES' " + "ELSE NULL " + @@ -2311,26 +2336,33 @@ public class MonetDatabaseMetaData exten "\"sys\".\"schemas\", " + "\"sys\".\"columns\", " + "\"sys\".\"auths\" AS \"grantors\", " + - "\"sys\".\"auths\" AS \"grantees\" " + - "WHERE \"privileges\".\"obj_id\" = \"columns\".\"id\" " + + "\"sys\".\"auths\" AS \"grantees\" "); + if (usePrivilege_codesTable) { + query.append(", \"sys\".\"privilege_codes\" "); + } + query.append("WHERE \"privileges\".\"obj_id\" = \"columns\".\"id\" " + "AND \"columns\".\"table_id\" = \"tables\".\"id\" " + "AND \"tables\".\"schema_id\" = \"schemas\".\"id\" " + "AND \"privileges\".\"auth_id\" = \"grantees\".\"id\" " + "AND \"privileges\".\"grantor\" = \"grantors\".\"id\""); - - if (catalog != null && catalog.length() > 0) { + if (usePrivilege_codesTable) { + query.append(" AND \"privileges\".\"privileges\" = \"privilege_codes\".\"privilege_code_id\""); + } + + if (catalog != null && !catalog.isEmpty()) { // none empty catalog selection. // as we do not support catalogs this always results in no rows returned query.append(" AND 1 = 0"); - } - if (schemaPattern != null) { - query.append(" AND \"schemas\".\"name\" ").append(composeMatchPart(schemaPattern)); - } - if (tableNamePattern != null) { - query.append(" AND \"tables\".\"name\" ").append(composeMatchPart(tableNamePattern)); - } - if (columnNamePattern != null) { - query.append(" AND \"columns\".\"name\" ").append(composeMatchPart(columnNamePattern)); + } else { + if (schemaPattern != null) { + query.append(" AND \"schemas\".\"name\" ").append(composeMatchPart(schemaPattern)); + } + if (tableNamePattern != null) { + query.append(" AND \"tables\".\"name\" ").append(composeMatchPart(tableNamePattern)); + } + if (columnNamePattern != null) { + query.append(" AND \"columns\".\"name\" ").append(composeMatchPart(columnNamePattern)); + } } query.append(" ORDER BY \"TABLE_SCHEM\", \"TABLE_NAME\", \"COLUMN_NAME\", \"PRIVILEGE\""); @@ -2373,13 +2405,15 @@ public class MonetDatabaseMetaData exten String tableNamePattern ) throws SQLException { + boolean usePrivilege_codesTable = ((MonetConnection)con).privilege_codesTableExists(); StringBuilder query = new StringBuilder(1000); query.append("SELECT cast(null as char(1)) AS \"TABLE_CAT\", " + "\"schemas\".\"name\" AS \"TABLE_SCHEM\", " + "\"tables\".\"name\" AS \"TABLE_NAME\", " + "\"grantors\".\"name\" AS \"GRANTOR\", " + - "\"grantees\".\"name\" AS \"GRANTEE\", " + - "CAST(CASE \"privileges\".\"privileges\" " + + "\"grantees\".\"name\" AS \"GRANTEE\", ") + .append(usePrivilege_codesTable ? "\"privilege_codes\".\"privilege_code_name\"" : + "cast(CASE \"privileges\".\"privileges\" " + "WHEN 1 THEN 'SELECT' " + "WHEN 2 THEN 'UPDATE' " + "WHEN 4 THEN 'INSERT' " + @@ -2387,8 +2421,8 @@ public class MonetDatabaseMetaData exten "WHEN 16 THEN 'EXECUTE' " + "WHEN 32 THEN 'GRANT' " + "ELSE NULL " + - "END AS varchar(7)) AS \"PRIVILEGE\", " + - "CAST(CASE \"privileges\".\"grantable\" " + + "END AS varchar(7))").append(" AS \"PRIVILEGE\", " + + "cast(CASE \"privileges\".\"grantable\" " + "WHEN 0 THEN 'NO' " + "WHEN 1 THEN 'YES' " + "ELSE NULL " + @@ -2397,22 +2431,29 @@ public class MonetDatabaseMetaData exten "\"sys\".\"tables\", " + "\"sys\".\"schemas\", " + "\"sys\".\"auths\" AS \"grantors\", " + - "\"sys\".\"auths\" AS \"grantees\" " + - "WHERE \"privileges\".\"obj_id\" = \"tables\".\"id\" " + + "\"sys\".\"auths\" AS \"grantees\" "); + if (usePrivilege_codesTable) { + query.append(", \"sys\".\"privilege_codes\" "); + } + query.append("WHERE \"privileges\".\"obj_id\" = \"tables\".\"id\" " + "AND \"tables\".\"schema_id\" = \"schemas\".\"id\" " + "AND \"privileges\".\"auth_id\" = \"grantees\".\"id\" " + "AND \"privileges\".\"grantor\" = \"grantors\".\"id\""); - - if (catalog != null && catalog.length() > 0) { + if (usePrivilege_codesTable) { + query.append(" AND \"privileges\".\"privileges\" = \"privilege_codes\".\"privilege_code_id\""); + } + + if (catalog != null && !catalog.isEmpty()) { // none empty catalog selection. // as we do not support catalogs this always results in no rows returned query.append(" AND 1 = 0"); - } - if (schemaPattern != null) { - query.append(" AND \"schemas\".\"name\" ").append(composeMatchPart(schemaPattern)); - } - if (tableNamePattern != null) { - query.append(" AND \"tables\".\"name\" ").append(composeMatchPart(tableNamePattern)); + } else { + if (schemaPattern != null) { + query.append(" AND \"schemas\".\"name\" ").append(composeMatchPart(schemaPattern)); + } + if (tableNamePattern != null) { + query.append(" AND \"tables\".\"name\" ").append(composeMatchPart(tableNamePattern)); + } } query.append(" ORDER BY \"TABLE_SCHEM\", \"TABLE_NAME\", \"PRIVILEGE\""); @@ -2465,14 +2506,14 @@ public class MonetDatabaseMetaData exten ) throws SQLException { StringBuilder query = new StringBuilder(1500); - query.append("SELECT CAST(").append(DatabaseMetaData.bestRowSession).append(" AS smallint) AS \"SCOPE\", " + + query.append("SELECT cast(").append(DatabaseMetaData.bestRowSession).append(" AS smallint) AS \"SCOPE\", " + "\"columns\".\"name\" AS \"COLUMN_NAME\", " + "cast(").append(MonetDriver.getSQLTypeMap("\"columns\".\"type\"")).append(" AS int) AS \"DATA_TYPE\", " + "\"columns\".\"type\" AS \"TYPE_NAME\", " + "\"columns\".\"type_digits\" AS \"COLUMN_SIZE\", " + - "CAST(0 as int) AS \"BUFFER_LENGTH\", " + - "CAST(\"columns\".\"type_scale\" AS smallint) AS \"DECIMAL_DIGITS\", " + - "CAST(").append(DatabaseMetaData.bestRowNotPseudo).append(" AS smallint) AS \"PSEUDO_COLUMN\" " + + "cast(0 as int) AS \"BUFFER_LENGTH\", " + + "cast(\"columns\".\"type_scale\" AS smallint) AS \"DECIMAL_DIGITS\", " + + "cast(").append(DatabaseMetaData.bestRowNotPseudo).append(" AS smallint) AS \"PSEUDO_COLUMN\" " + "FROM \"sys\".\"keys\", " + "\"sys\".\"objects\", " + "\"sys\".\"columns\", " + @@ -2485,22 +2526,26 @@ public class MonetDatabaseMetaData exten "AND \"tables\".\"schema_id\" = \"schemas\".\"id\" " + "AND \"keys\".\"type\" IN (0, 1)"); // only primary keys (type = 0) and unique keys (type = 1), not fkeys (type = 2) - if (catalog != null && catalog.length() > 0) { + if (catalog != null && !catalog.isEmpty()) { // none empty catalog selection. // as we do not support catalogs this always results in no rows returned query.append(" AND 1 = 0"); - } - if (schema != null) { - query.append(" AND \"schemas\".\"name\" ").append(composeMatchPart(schema)); - } - if (table != null) { - query.append(" AND \"tables\".\"name\" ").append(composeMatchPart(table)); - } - if (scope != DatabaseMetaData.bestRowSession && scope != DatabaseMetaData.bestRowTransaction && scope != DatabaseMetaData.bestRowTemporary) { - query.append(" AND 1 = 0"); - } - if (!nullable) { - query.append(" AND \"columns\".\"null\" = false"); + } else { + if (scope != DatabaseMetaData.bestRowSession + && scope != DatabaseMetaData.bestRowTransaction + && scope != DatabaseMetaData.bestRowTemporary) { + query.append(" AND 1 = 0"); + } else { + if (schema != null) { + query.append(" AND \"schemas\".\"name\" ").append(composeMatchPart(schema)); + } + if (table != null) { + query.append(" AND \"tables\".\"name\" ").append(composeMatchPart(table)); + } + if (!nullable) { + query.append(" AND \"columns\".\"null\" = false"); + } + } } query.append(" ORDER BY \"keys\".\"type\", \"columns\".\"name\""); @@ -2544,14 +2589,14 @@ public class MonetDatabaseMetaData exten { // MonetDB currently does not have columns which update themselves, so return an empty ResultSet String query = - "SELECT CAST(0 as smallint) AS \"SCOPE\", " + - "CAST(null as varchar(1)) AS \"COLUMN_NAME\", " + - "CAST(0 as int) AS \"DATA_TYPE\", " + - "CAST(null as varchar(1)) AS \"TYPE_NAME\", " + - "CAST(0 as int) AS \"COLUMN_SIZE\", " + - "CAST(0 as int) AS \"BUFFER_LENGTH\", " + - "CAST(0 as smallint) AS \"DECIMAL_DIGITS\", " + - "CAST(0 as smallint) AS \"PSEUDO_COLUMN\" " + + "SELECT cast(0 as smallint) AS \"SCOPE\", " + + "cast(null as char(1)) AS \"COLUMN_NAME\", " + + "cast(0 as int) AS \"DATA_TYPE\", " + + "cast(null as char(1)) AS \"TYPE_NAME\", " + + "cast(0 as int) AS \"COLUMN_SIZE\", " + + "cast(0 as int) AS \"BUFFER_LENGTH\", " + + "cast(0 as smallint) AS \"DECIMAL_DIGITS\", " + + "cast(0 as smallint) AS \"PSEUDO_COLUMN\" " + "WHERE 1 = 0"; return executeMetaDataQuery(query); @@ -2586,11 +2631,11 @@ public class MonetDatabaseMetaData exten ) throws SQLException { StringBuilder query = new StringBuilder(600); - query.append("SELECT cast(null AS varchar(1)) AS \"TABLE_CAT\", " + + query.append("SELECT cast(null AS char(1)) AS \"TABLE_CAT\", " + "\"schemas\".\"name\" AS \"TABLE_SCHEM\", " + "\"tables\".\"name\" AS \"TABLE_NAME\", " + "\"objects\".\"name\" AS \"COLUMN_NAME\", " + - "CAST(1 + \"objects\".\"nr\" AS smallint) AS \"KEY_SEQ\", " + + "cast(1 + \"objects\".\"nr\" AS smallint) AS \"KEY_SEQ\", " + " \"keys\".\"name\" AS \"PK_NAME\" " + "FROM \"sys\".\"keys\", " + "\"sys\".\"objects\", " + @@ -2601,16 +2646,17 @@ public class MonetDatabaseMetaData exten "AND \"tables\".\"schema_id\" = \"schemas\".\"id\" " + "AND \"keys\".\"type\" = 0"); - if (catalog != null && catalog.length() > 0) { + if (catalog != null && !catalog.isEmpty()) { // none empty catalog selection. // as we do not support catalogs this always results in no rows returned query.append(" AND 1 = 0"); - } - if (schema != null) { - query.append(" AND \"schemas\".\"name\" ").append(composeMatchPart(schema)); - } - if (table != null) { - query.append(" AND \"tables\".\"name\" ").append(composeMatchPart(table)); + } else { + if (schema != null) { + query.append(" AND \"schemas\".\"name\" ").append(composeMatchPart(schema)); + } + if (table != null) { + query.append(" AND \"tables\".\"name\" ").append(composeMatchPart(table)); + } } query.append(" ORDER BY \"TABLE_SCHEM\", \"TABLE_NAME\", \"COLUMN_NAME\""); @@ -2620,15 +2666,15 @@ public class MonetDatabaseMetaData exten private static final String keyQuery = - "SELECT cast(null AS varchar(1)) AS \"PKTABLE_CAT\", " + + "SELECT cast(null AS char(1)) AS \"PKTABLE_CAT\", " + "\"pkschema\".\"name\" AS \"PKTABLE_SCHEM\", " + "\"pktable\".\"name\" AS \"PKTABLE_NAME\", " + "\"pkkeycol\".\"name\" AS \"PKCOLUMN_NAME\", " + - "cast(null AS varchar(1)) AS \"FKTABLE_CAT\", " + + "cast(null AS char(1)) AS \"FKTABLE_CAT\", " + "\"fkschema\".\"name\" AS \"FKTABLE_SCHEM\", " + "\"fktable\".\"name\" AS \"FKTABLE_NAME\", " + "\"fkkeycol\".\"name\" AS \"FKCOLUMN_NAME\", " + - "CAST(1 + \"pkkeycol\".\"nr\" AS smallint) AS \"KEY_SEQ\", " + + "cast(1 + \"pkkeycol\".\"nr\" AS smallint) AS \"KEY_SEQ\", " + DatabaseMetaData.importedKeyNoAction + " AS \"UPDATE_RULE\", " + DatabaseMetaData.importedKeyNoAction + " AS \"DELETE_RULE\", " + "\"fkkey\".\"name\" AS \"FK_NAME\", " + @@ -2719,16 +2765,17 @@ public class MonetDatabaseMetaData exten StringBuilder query = new StringBuilder(keyQuery.length() + 250); query.append(keyQuery); - if (catalog != null && catalog.length() > 0) { + if (catalog != null && !catalog.isEmpty()) { // none empty catalog selection. // as we do not support catalogs this always results in no rows returned query.append(" AND 1 = 0"); - } - if (schema != null) { - query.append(" AND \"fkschema\".\"name\" ").append(composeMatchPart(schema)); - } - if (table != null) { - query.append(" AND \"fktable\".\"name\" ").append(composeMatchPart(table)); + } else { + if (schema != null) { + query.append(" AND \"fkschema\".\"name\" ").append(composeMatchPart(schema)); + } + if (table != null) { + query.append(" AND \"fktable\".\"name\" ").append(composeMatchPart(table)); + } } query.append(" ORDER BY \"PKTABLE_SCHEM\", \"PKTABLE_NAME\", \"PK_NAME\", \"KEY_SEQ\""); @@ -2802,16 +2849,17 @@ public class MonetDatabaseMetaData exten StringBuilder query = new StringBuilder(keyQuery.length() + 250); query.append(keyQuery); - if (catalog != null && catalog.length() > 0) { + if (catalog != null && !catalog.isEmpty()) { // none empty catalog selection. // as we do not support catalogs this always results in no rows returned query.append(" AND 1 = 0"); - } - if (schema != null) { - query.append(" AND \"pkschema\".\"name\" ").append(composeMatchPart(schema)); - } - if (table != null) { - query.append(" AND \"pktable\".\"name\" ").append(composeMatchPart(table)); + } else { + if (schema != null) { + query.append(" AND \"pkschema\".\"name\" ").append(composeMatchPart(schema)); + } + if (table != null) { + query.append(" AND \"pktable\".\"name\" ").append(composeMatchPart(table)); + } } query.append(" ORDER BY \"FKTABLE_SCHEM\", \"FKTABLE_NAME\", \"FK_NAME\", \"KEY_SEQ\""); @@ -2897,28 +2945,25 @@ public class MonetDatabaseMetaData exten StringBuilder query = new StringBuilder(keyQuery.length() + 350); query.append(keyQuery); - if (pcatalog != null && pcatalog.length() > 0) { + if ((pcatalog != null && !pcatalog.isEmpty()) + || (fcatalog != null && !fcatalog.isEmpty())) { // none empty catalog selection. // as we do not support catalogs this always results in no rows returned query.append(" AND 1 = 0"); - } - if (pschema != null) { - query.append(" AND \"pkschema\".\"name\" ").append(composeMatchPart(pschema)); - } - if (ptable != null) { - query.append(" AND \"pktable\".\"name\" ").append(composeMatchPart(ptable)); - } - - if (fcatalog != null && fcatalog.length() > 0) { - // none empty catalog selection. - // as we do not support catalogs this always results in no rows returned - query.append(" AND 1 = 0"); - } - if (fschema != null) { - query.append(" AND \"fkschema\".\"name\" ").append(composeMatchPart(fschema)); - } - if (ftable != null) { - query.append(" AND \"fktable\".\"name\" ").append(composeMatchPart(ftable)); + } else { + if (pschema != null) { + query.append(" AND \"pkschema\".\"name\" ").append(composeMatchPart(pschema)); + } + if (ptable != null) { + query.append(" AND \"pktable\".\"name\" ").append(composeMatchPart(ptable)); + } + + if (fschema != null) { + query.append(" AND \"fkschema\".\"name\" ").append(composeMatchPart(fschema)); + } + if (ftable != null) { + query.append(" AND \"fktable\".\"name\" ").append(composeMatchPart(ftable)); + } } query.append(" ORDER BY \"FKTABLE_SCHEM\", \"FKTABLE_NAME\", \"FK_NAME\", \"KEY_SEQ\""); @@ -3076,14 +3121,14 @@ public class MonetDatabaseMetaData exten { String table_row_count = "0"; - if (!approximate && schema != null && table != null && schema.length() > 0 && table.length() > 0) { + if (!approximate && schema != null && table != null && !schema.isEmpty() && !table.isEmpty()) { // we need the exact cardinality for one specific fully qualified table ResultSet count = null; try { count = executeMetaDataQuery("SELECT COUNT(*) FROM \"" + schema + "\".\"" + table + "\""); if (count != null && count.next()) { String count_value = count.getString(1); - if (count_value != null && count_value.length() > 0) + if (count_value != null && !count_value.isEmpty()) table_row_count = count_value; } } catch (SQLException e) { @@ -3099,19 +3144,19 @@ public class MonetDatabaseMetaData exten StringBuilder query = new StringBuilder(1250); query.append( - "SELECT CAST(null AS char(1)) AS \"TABLE_CAT\", " + + "SELECT cast(null AS char(1)) AS \"TABLE_CAT\", " + "\"schemas\".\"name\" AS \"TABLE_SCHEM\", " + "\"tables\".\"name\" AS \"TABLE_NAME\", " + "CASE WHEN \"keys\".\"name\" IS NULL THEN true ELSE false END AS \"NON_UNIQUE\", " + - "CAST(null AS varchar(1)) AS \"INDEX_QUALIFIER\", " + + "cast(null AS char(1)) AS \"INDEX_QUALIFIER\", " + "\"idxs\".\"name\" AS \"INDEX_NAME\", " + "CASE \"idxs\".\"type\" WHEN 0 THEN ").append(DatabaseMetaData.tableIndexHashed).append(" ELSE ").append(DatabaseMetaData.tableIndexOther).append(" END AS \"TYPE\", " + - "CAST(\"objects\".\"nr\" +1 AS smallint) AS \"ORDINAL_POSITION\", "+ + "cast(\"objects\".\"nr\" +1 AS smallint) AS \"ORDINAL_POSITION\", "+ "\"columns\".\"name\" AS \"COLUMN_NAME\", " + - "CAST(null AS varchar(1)) AS \"ASC_OR_DESC\", " + // sort sequence currently not supported in keys or indexes in MonetDB - "CAST(").append(table_row_count).append(" AS int) AS \"CARDINALITY\", " + - "CAST(0 AS int) AS \"PAGES\", " + - "CAST(null AS varchar(1)) AS \"FILTER_CONDITION\" " + + "cast(null AS char(1)) AS \"ASC_OR_DESC\", " + // sort sequence currently not supported in keys or indexes in MonetDB + "cast(").append(table_row_count).append(" AS int) AS \"CARDINALITY\", " + + "cast(0 AS int) AS \"PAGES\", " + + "cast(null AS char(1)) AS \"FILTER_CONDITION\" " + "FROM \"sys\".\"idxs\" LEFT JOIN \"sys\".\"keys\" ON \"idxs\".\"name\" = \"keys\".\"name\", " + "\"sys\".\"schemas\", " + "\"sys\".\"objects\", " + @@ -3124,20 +3169,22 @@ public class MonetDatabaseMetaData exten "AND \"objects\".\"name\" = \"columns\".\"name\" " + "AND (\"keys\".\"type\" IS NULL OR \"keys\".\"type\" = 1)"); - if (catalog != null && catalog.length() > 0) { + if (catalog != null && !catalog.isEmpty()) { // none empty catalog selection. // as we do not support catalogs this always results in no rows returned query.append(" AND 1 = 0"); - } - if (schema != null) { - query.append(" AND \"schemas\".\"name\" ").append(composeMatchPart(schema)); + } else { + if (schema != null) { + query.append(" AND \"schemas\".\"name\" ").append(composeMatchPart(schema)); + } + if (table != null) { + query.append(" AND \"tables\".\"name\" ").append(composeMatchPart(table)); + } + if (unique) { + query.append(" AND \"keys\".\"name\" IS NOT NULL"); + } } - if (table != null) { - query.append(" AND \"tables\".\"name\" ").append(composeMatchPart(table)); - } - if (unique) { - query.append(" AND \"keys\".\"name\" IS NOT NULL"); - } + query.append(" ORDER BY \"NON_UNIQUE\", \"TYPE\", \"INDEX_NAME\", \"ORDINAL_POSITION\""); return executeMetaDataQuery(query.toString()); @@ -3276,31 +3323,33 @@ public class MonetDatabaseMetaData exten "\"schemas\".\"name\" AS \"TYPE_SCHEM\", " + "\"types\".\"sqlname\" AS \"TYPE_NAME\", " + "CASE \"types\".\"sqlname\"" + - // next 4 UDTs are known + // next 4 UDTs are standard " WHEN 'inet' THEN 'nl.cwi.monetdb.jdbc.types.INET'" + " WHEN 'json' THEN 'java.lang.String'" + " WHEN 'url' THEN 'nl.cwi.monetdb.jdbc.types.URL'" + " WHEN 'uuid' THEN 'java.lang.String'" + " ELSE 'java.lang.Object' END AS \"CLASS_NAME\", " + - "CAST(CASE WHEN \"types\".\"sqlname\" IN ('inet', 'json', 'url', 'uuid') THEN ").append(Types.JAVA_OBJECT) + "cast(CASE WHEN \"types\".\"sqlname\" IN ('inet', 'json', 'url', 'uuid') THEN ").append(Types.JAVA_OBJECT) .append(" ELSE ").append(Types.STRUCT).append(" END AS int) AS \"DATA_TYPE\", " + "\"types\".\"systemname\" AS \"REMARKS\", " + "cast(null as smallint) AS \"BASE_TYPE\" " + - "FROM \"sys\".\"types\" JOIN \"sys\".\"schemas\" ON \"types\".\"schema_id\" = \"schemas\".\"id\" " + - // exclude the built-in types (I assume they always have id <= 99 and eclass < 15) - "WHERE \"types\".\"id\" > 99 AND \"types\".\"eclass\" >= 15"); - - if (catalog != null && catalog.length() > 0) { + "FROM \"sys\".\"types\" JOIN \"sys\".\"schemas\" ON \"types\".\"schema_id\" = \"schemas\".\"id\" " + + // exclude the built-in types (I assume they always have id <= 99 and eclass < 15) + "WHERE \"types\".\"id\" > 99 AND \"types\".\"eclass\" >= 15"); + + if (catalog != null && !catalog.isEmpty()) { // none empty catalog selection. // as we do not support catalogs this always results in no rows returned query.append(" AND 1 = 0"); - } - if (schemaPattern != null) { - query.append(" AND \"schemas\".\"name\" ").append(composeMatchPart(schemaPattern)); + } else { + if (schemaPattern != null) { + query.append(" AND \"schemas\".\"name\" ").append(composeMatchPart(schemaPattern)); + } + if (typeNamePattern != null) { + query.append(" AND \"types\".\"sqlname\" ").append(composeMatchPart(typeNamePattern)); + } } - if (typeNamePattern != null) { - query.append(" AND \"types\".\"sqlname\" ").append(composeMatchPart(typeNamePattern)); - } + if (types != null && types.length > 0) { query.append(") AS getUDTs WHERE \"DATA_TYPE\" IN ("); for (int i = 0; i < types.length; i++) { @@ -3311,6 +3360,7 @@ public class MonetDatabaseMetaData exten } query.append(")"); } + query.append(" ORDER BY \"DATA_TYPE\", \"TYPE_SCHEM\", \"TYPE_NAME\""); return executeMetaDataQuery(query.toString()); @@ -3567,13 +3617,13 @@ public class MonetDatabaseMetaData exten { String query = "SELECT cast(null as char(1)) AS \"TYPE_CAT\", '' AS \"TYPE_SCHEM\", '' AS \"TYPE_NAME\", " + - "'' AS \"ATTR_NAME\", CAST(0 as int) AS \"DATA_TYPE\", '' AS \"ATTR_TYPE_NAME\", CAST(0 as int) AS \"ATTR_SIZE\", " + - "CAST(0 as int) AS \"DECIMAL_DIGITS\", CAST(0 as int) AS \"NUM_PREC_RADIX\", CAST(0 as int) AS \"NULLABLE\", " + - "'' AS \"REMARKS\", '' AS \"ATTR_DEF\", CAST(0 as int) AS \"SQL_DATA_TYPE\", " + - "CAST(0 as int) AS \"SQL_DATETIME_SUB\", CAST(0 as int) AS \"CHAR_OCTET_LENGTH\", " + - "CAST(0 as int) AS \"ORDINAL_POSITION\", 'YES' AS \"IS_NULLABLE\", " + + "'' AS \"ATTR_NAME\", cast(0 as int) AS \"DATA_TYPE\", '' AS \"ATTR_TYPE_NAME\", cast(0 as int) AS \"ATTR_SIZE\", " + + "cast(0 as int) AS \"DECIMAL_DIGITS\", cast(0 as int) AS \"NUM_PREC_RADIX\", cast(0 as int) AS \"NULLABLE\", " + + "'' AS \"REMARKS\", '' AS \"ATTR_DEF\", cast(0 as int) AS \"SQL_DATA_TYPE\", " + + "cast(0 as int) AS \"SQL_DATETIME_SUB\", cast(0 as int) AS \"CHAR_OCTET_LENGTH\", " + + "cast(0 as int) AS \"ORDINAL_POSITION\", 'YES' AS \"IS_NULLABLE\", " + "'' AS \"SCOPE_CATALOG\", '' AS \"SCOPE_SCHEMA\", '' AS \"SCOPE_TABLE\", " + - "CAST(0 as smallint) AS \"SOURCE_DATA_TYPE\" " + + "cast(0 as smallint) AS \"SOURCE_DATA_TYPE\" " + "WHERE 1 = 0"; return executeMetaDataQuery(query); @@ -3800,7 +3850,7 @@ public class MonetDatabaseMetaData exten public ResultSet getClientInfoProperties() throws SQLException { // for a list of connection properties see also MonetConnection.java constructor MonetConnection(Properties props) String query = - "SELECT 'host' AS \"NAME\", CAST(1024 as int) AS \"MAX_LEN\", 'localhost' AS \"DEFAULT_VALUE\", 'DSN or IP-address of machine running MonetDB' AS \"DESCRIPTION\" UNION ALL " + + "SELECT 'host' AS \"NAME\", cast(1024 as int) AS \"MAX_LEN\", 'localhost' AS \"DEFAULT_VALUE\", 'DSN or IP-address of machine running MonetDB' AS \"DESCRIPTION\" UNION ALL " + "SELECT 'port', 5, '50000', 'communication port number of MonetDB server process' UNION ALL " + "SELECT 'user', 1024, '', 'user name to login to MonetDB server' UNION ALL " + "SELECT 'password', 128, '', 'password for user name to login to MonetDB server' UNION ALL " + @@ -3865,35 +3915,41 @@ public class MonetDatabaseMetaData exten String functionNamePattern) throws SQLException { + boolean useCommentsTable = ((MonetConnection)con).commentsTableExists(); StringBuilder query = new StringBuilder(800); - query.append("SELECT cast(null as varchar(1)) AS \"FUNCTION_CAT\", " + + query.append("SELECT cast(null as char(1)) AS \"FUNCTION_CAT\", " + "\"schemas\".\"name\" AS \"FUNCTION_SCHEM\", " + - "\"functions\".\"name\" AS \"FUNCTION_NAME\", " + - "cast(\"functions\".\"func\" as varchar(9999)) AS \"REMARKS\", " + + "\"functions\".\"name\" AS \"FUNCTION_NAME\", ") + .append(useCommentsTable ? "COALESCE(\"comments\".\"remark\", cast(\"functions\".\"func\" as varchar(9999)))" : "cast(\"functions\".\"func\" as varchar(9999))").append(" AS \"REMARKS\", " + "CASE \"functions\".\"type\"" + " WHEN 1 THEN ").append(DatabaseMetaData.functionNoTable) .append(" WHEN 2 THEN ").append(DatabaseMetaData.functionNoTable) .append(" WHEN 3 THEN ").append(DatabaseMetaData.functionNoTable) .append(" WHEN 4 THEN ").append(DatabaseMetaData.functionNoTable) .append(" WHEN 5 THEN ").append(DatabaseMetaData.functionReturnsTable) + .append(" WHEN 6 THEN ").append(DatabaseMetaData.functionNoTable) + .append(" WHEN 7 THEN ").append(DatabaseMetaData.functionReturnsTable) .append(" ELSE ").append(DatabaseMetaData.functionResultUnknown).append(" END AS \"FUNCTION_TYPE\", " + // only the id value uniquely identifies a function. Include it to be able to differentiate between multiple overloaded functions with the same name "cast(\"functions\".\"id\" as varchar(10)) AS \"SPECIFIC_NAME\" " + - "FROM \"sys\".\"functions\", \"sys\".\"schemas\" " + - "WHERE \"functions\".\"schema_id\" = \"schemas\".\"id\" " + - // exclude procedures (type = 2). Those need to be returned via getProcedures() - "AND \"functions\".\"type\" <> 2"); - - if (catalog != null && catalog.length() > 0) { + "FROM \"sys\".\"functions\" JOIN \"sys\".\"schemas\" ON (\"functions\".\"schema_id\" = \"schemas\".\"id\") "); + if (useCommentsTable) { + query.append("LEFT OUTER JOIN \"sys\".\"comments\" ON (\"functions\".\"id\" = \"comments\".\"id\") "); + } + // only functions, so exclude procedures (type = 2). Those will to be returned via getProcedures() + query.append("WHERE \"functions\".\"type\" <> 2"); + + if (catalog != null && !catalog.isEmpty()) { // none empty catalog selection. // as we do not support catalogs this always results in no rows returned query.append(" AND 1 = 0"); - } - if (schemaPattern != null) { - query.append(" AND \"schemas\".\"name\" ").append(composeMatchPart(schemaPattern)); - } - if (functionNamePattern != null) { - query.append(" AND \"functions\".\"name\" ").append(composeMatchPart(functionNamePattern)); + } else { + if (schemaPattern != null) { + query.append(" AND \"schemas\".\"name\" ").append(composeMatchPart(schemaPattern)); + } + if (functionNamePattern != null) { + query.append(" AND \"functions\".\"name\" ").append(composeMatchPart(functionNamePattern)); + } } query.append(" ORDER BY \"FUNCTION_SCHEM\", \"FUNCTION_NAME\", \"SPECIFIC_NAME\""); @@ -3967,52 +4023,54 @@ public class MonetDatabaseMetaData exten throws SQLException { StringBuilder query = new StringBuilder(2600); - query.append("SELECT DISTINCT CAST(null as char(1)) AS \"FUNCTION_CAT\", " + + query.append("SELECT cast(null as char(1)) AS \"FUNCTION_CAT\", " + "\"schemas\".\"name\" AS \"FUNCTION_SCHEM\", " + "\"functions\".\"name\" AS \"FUNCTION_NAME\", " + "\"args\".\"name\" AS \"COLUMN_NAME\", " + - "CAST(CASE \"args\".\"inout\"" + + "cast(CASE \"args\".\"inout\"" + " WHEN 0 THEN (CASE \"args\".\"number\" WHEN 0 THEN ") .append(DatabaseMetaData.functionReturn).append(" ELSE ").append(DatabaseMetaData.functionColumnOut).append(" END)" + " WHEN 1 THEN ").append(DatabaseMetaData.functionColumnIn) .append(" ELSE ").append(DatabaseMetaData.functionColumnUnknown).append(" END AS smallint) AS \"COLUMN_TYPE\", " + - "CAST(").append(MonetDriver.getSQLTypeMap("\"args\".\"type\"")).append(" AS int) AS \"DATA_TYPE\", " + + "cast(").append(MonetDriver.getSQLTypeMap("\"args\".\"type\"")).append(" AS int) AS \"DATA_TYPE\", " + "\"args\".\"type\" AS \"TYPE_NAME\", " + "CASE \"args\".\"type\" WHEN 'tinyint' THEN 3 WHEN 'smallint' THEN 5 WHEN 'int' THEN 10 WHEN 'bigint' THEN 19" + " WHEN 'hugeint' THEN 38 WHEN 'oid' THEN 19 WHEN 'wrd' THEN 19 ELSE \"args\".\"type_digits\" END AS \"PRECISION\", " + "CASE \"args\".\"type\" WHEN 'tinyint' THEN 1 WHEN 'smallint' THEN 2 WHEN 'int' THEN 4 WHEN 'bigint' THEN 8" + " WHEN 'hugeint' THEN 16 WHEN 'oid' THEN 8 WHEN 'wrd' THEN 8 ELSE \"args\".\"type_digits\" END AS \"LENGTH\", " + - "CAST(CASE WHEN \"args\".\"type\" IN ('tinyint','smallint','int','bigint','hugeint','oid','wrd','decimal','numeric'," + + "cast(CASE WHEN \"args\".\"type\" IN ('tinyint','smallint','int','bigint','hugeint','oid','wrd','decimal','numeric'," + "'time','timetz','timestamp','timestamptz','sec_interval') THEN \"args\".\"type_scale\" ELSE NULL END AS smallint) AS \"SCALE\", " + - "CAST(CASE WHEN \"args\".\"type\" IN ('tinyint','smallint','int','bigint','hugeint','oid','wrd','decimal','numeric') THEN 10" + + "cast(CASE WHEN \"args\".\"type\" IN ('tinyint','smallint','int','bigint','hugeint','oid','wrd','decimal','numeric') THEN 10" + " WHEN \"args\".\"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\", " + - "CAST(CASE WHEN \"args\".\"type\" IN ('char','varchar','clob') THEN \"args\".\"type_digits\" ELSE NULL END as int) AS \"CHAR_OCTET_LENGTH\", " + - "CAST(\"args\".\"number\" as int) AS \"ORDINAL_POSITION\", " + - "CAST('' as varchar(3)) AS \"IS_NULLABLE\", " + + "cast(").append(DatabaseMetaData.functionNullableUnknown).append(" AS smallint) AS \"NULLABLE\", " + + "cast(null as char(1)) AS \"REMARKS\", " + + "cast(CASE WHEN \"args\".\"type\" IN ('char','varchar','clob') THEN \"args\".\"type_digits\" ELSE NULL END as int) AS \"CHAR_OCTET_LENGTH\", " + + "cast(\"args\".\"number\" as int) AS \"ORDINAL_POSITION\", " + + "cast('' as varchar(3)) AS \"IS_NULLABLE\", " + // the specific name contains the function id, in order to be able to match the args to the correct overloaded function name "cast(\"functions\".\"id\" as varchar(10)) AS \"SPECIFIC_NAME\" " + - "FROM \"sys\".\"args\", \"sys\".\"functions\", \"sys\".\"schemas\" " + - "WHERE \"args\".\"func_id\" = \"functions\".\"id\" " + - "AND \"functions\".\"schema_id\" = \"schemas\".\"id\" " + - // exclude procedures (type = 2). Those need to be returned via getProcedureColumns() - "AND \"functions\".\"type\" <> 2"); - - if (catalog != null && catalog.length() > 0) { + "FROM \"sys\".\"args\" " + + "JOIN \"sys\".\"functions\" ON (\"args\".\"func_id\" = \"functions\".\"id\") " + + "JOIN \"sys\".\"schemas\" ON (\"functions\".\"schema_id\" = \"schemas\".\"id\") " + + // only functions, so exclude procedures (type = 2). Those will to be returned via getProcedureColumns() + "WHERE \"functions\".\"type\" <> 2"); + + if (catalog != null && !catalog.isEmpty()) { // none empty catalog selection. // as we do not support catalogs this always results in no rows returned query.append(" AND 1 = 0"); - } - if (schemaPattern != null) { - query.append(" AND \"schemas\".\"name\" ").append(composeMatchPart(schemaPattern)); + } else { + if (schemaPattern != null) { + query.append(" AND \"schemas\".\"name\" ").append(composeMatchPart(schemaPattern)); + } + if (functionNamePattern != null) { + query.append(" AND \"functions\".\"name\" ").append(composeMatchPart(functionNamePattern)); + } + if (columnNamePattern != null) { + query.append(" AND \"args\".\"name\" ").append(composeMatchPart(columnNamePattern)); + } } - if (functionNamePattern != null) { - query.append(" AND \"functions\".\"name\" ").append(composeMatchPart(functionNamePattern)); - } - if (columnNamePattern != null) { - query.append(" AND \"args\".\"name\" ").append(composeMatchPart(columnNamePattern)); - } + query.append(" ORDER BY \"FUNCTION_SCHEM\", \"FUNCTION_NAME\", \"SPECIFIC_NAME\", \"ORDINAL_POSITION\""); return executeMetaDataQuery(query.toString()); @@ -4067,18 +4125,18 @@ public class MonetDatabaseMetaData exten { // MonetDB currently does not support pseudo or hidden columns, so return an empty ResultSet String query = - "SELECT CAST(null as char(1)) AS \"TABLE_CAT\", " + - "CAST('' as varchar(1)) AS \"TABLE_SCHEM\", " + - "CAST('' as varchar(1)) AS \"TABLE_NAME\", " + - "CAST('' as varchar(1)) AS \"COLUMN_NAME\", " + - "CAST(0 as int) AS \"DATA_TYPE\", " + - "CAST(0 as int) AS \"COLUMN_SIZE\", " + - "CAST(0 as int) AS \"DECIMAL_DIGITS\", " + - "CAST(0 as int) AS \"NUM_PREC_RADIX\", " + - "CAST('' as varchar(1)) AS \"COLUMN_USAGE\", " + - "CAST(null as varchar(1)) AS \"REMARKS\", " + - "CAST(0 as int) AS \"CHAR_OCTET_LENGTH\", " + - "CAST('' as varchar(3)) AS \"IS_NULLABLE\" " + + "SELECT cast(null as char(1)) AS \"TABLE_CAT\", " + + "'' AS \"TABLE_SCHEM\", " + + "'' AS \"TABLE_NAME\", " + + "'' AS \"COLUMN_NAME\", " + + "cast(0 as int) AS \"DATA_TYPE\", " + + "cast(0 as int) AS \"COLUMN_SIZE\", " + + "cast(0 as int) AS \"DECIMAL_DIGITS\", " + + "cast(0 as int) AS \"NUM_PREC_RADIX\", " + + "'' AS \"COLUMN_USAGE\", " + + "'' AS \"REMARKS\", " + + "cast(0 as int) AS \"CHAR_OCTET_LENGTH\", " + + "'' AS \"IS_NULLABLE\" " + "WHERE 1 = 0"; return executeMetaDataQuery(query);
--- a/src/main/java/nl/cwi/monetdb/jdbc/MonetDriver.java.in +++ b/src/main/java/nl/cwi/monetdb/jdbc/MonetDriver.java.in @@ -138,60 +138,79 @@ public final class MonetDriver implement if (!acceptsURL(url)) return null; - List<DriverPropertyInfo> props = new ArrayList<>(); + String[] boolean_choices = new String[] { "true", "false" }; + String[] language_choices = new String[] { "sql", "mal" }; + String[] hash_choices = new String[] { "SHA512", "SHA384", "SHA256", "SHA1", "MD5" }; + DriverPropertyInfo[] dpi = new DriverPropertyInfo[11]; // we currently support 11 connection properties - DriverPropertyInfo prop = new DriverPropertyInfo("user", info.getProperty("user")); + DriverPropertyInfo prop = new DriverPropertyInfo("user", info != null ? info.getProperty("user") : null); prop.required = true; - prop.description = "The user name to use when authenticating on the database server"; - props.add(prop); + prop.description = "The user loginname to use when authenticating on the database server"; + dpi[0] = prop; - prop = new DriverPropertyInfo("password", info.getProperty("password")); + prop = new DriverPropertyInfo("password", info != null ? info.getProperty("password") : null); prop.required = true; prop.description = "The password to use when authenticating on the database server"; - props.add(prop); + dpi[1] = prop; + + prop = new DriverPropertyInfo("debug", "false"); + prop.description = "Whether or not to create a log file for debugging purposes (MAPI connection only)"; + prop.choices = boolean_choices; + dpi[2] = prop; - prop = new DriverPropertyInfo("hash", ""); + prop = new DriverPropertyInfo("logfile", null); + prop.required = false; + prop.description = "The filename to write the debug log to. Only takes effect if debug is set to true. If the file exists, an incrementing number is added, till the filename is unique. (MAPI connection only)"; + dpi[3] = prop; + + prop = new DriverPropertyInfo("language", "sql"); + prop.required = false; + prop.description = "What language to use for MonetDB conversations (MAPI connection only) (experts only)"; + prop.choices = language_choices; + dpi[4] = prop; + + prop = new DriverPropertyInfo("hash", null); prop.required = false; prop.description = "Force the use of the given hash algorithm during challenge response (one of SHA1, MD5, plain) (MAPI connection only)"; - props.add(prop); + prop.choices = hash_choices; + dpi[5] = prop; + + prop = new DriverPropertyInfo("treat_blob_as_binary", "false"); + prop.required = false; + prop.description = "Should blob columns be mapped to Types.VARBINARY instead of default Types.BLOB in ResultSets and PreparedStatements (MAPI connection only)"; + prop.choices = boolean_choices; + dpi[6] = prop; + + prop = new DriverPropertyInfo("treat_clob_as_varchar", "false"); + prop.required = false; + prop.description = "Should clob columns be mapped to Types.VARCHAR instead of default Types.CLOB in ResultSets and PreparedStatements (MAPI connection only)"; // recommend for increased performance due to less overhead + prop.choices = boolean_choices; + dpi[7] = prop; prop = new DriverPropertyInfo("so_timeout", "0"); prop.required = false; prop.description = "Defines the maximum time to wait in milliseconds on a blocking read socket call (MAPI connection only)"; // this corresponds to the Connection.setNetworkTimeout() method introduced in JDBC 4.1 - props.add(prop); - - prop = new DriverPropertyInfo("treat_blob_as_binary", "false"); - prop.required = false; - prop.description = "Whether BLOBs on the server should be treated as BINARY types, thus mapped to byte[] (MAPI connection only)"; - props.add(prop); - - prop = new DriverPropertyInfo("treat_clob_as_varchar", "false"); - prop.required = false; - prop.description = "Whether CLOBs on the server should be treated and handled as VARCHAR types in the JDBC driver (MAPI connection only)"; // recommend for increased performance due to less overhead - props.add(prop); - - prop = new DriverPropertyInfo("treat_clob_as_longvarchar", "false"); - prop.required = false; - prop.description = "Whether CLOBs on the server should be treated as LONGVARCHAR types, thus mapped to String (MAPI connection only)"; - props.add(prop); - - prop = new DriverPropertyInfo("language", "sql"); - prop.required = false; - prop.description = "What language to use for MonetDB conversations (experts only)"; - props.add(prop); + dpi[8] = prop; prop = new DriverPropertyInfo("silentFlag", "true"); prop.required = false; prop.description = "Don't produce output from the server (i.e. debug) (Embedded connection only)"; - props.add(prop); + dpi[9] = prop; prop = new DriverPropertyInfo("sequentialFlag", "false"); prop.required = false; prop.description = "Use sequential pipeline instead of default pipeline (Embedded connection only)"; - props.add(prop); + dpi[10] = prop; - DriverPropertyInfo[] dpi = new DriverPropertyInfo[props.size()]; - return props.toArray(dpi); +/* next property is no longer supported in: new MonetConnection(props) + prop = new DriverPropertyInfo("follow_redirects", "true"); + prop.required = false; + prop.description = "Whether redirects issued by the server should be followed"; + prop.choices = boolean_choices; + dpi[11] = prop; +*/ + + return dpi; } /** @@ -260,20 +279,22 @@ public final class MonetDriver implement } /** - * Returns the java.sql.Types equivalent of the given MonetDB type. + * Returns the java.sql.Types equivalent of the given MonetDB type name. * - * @param type the type as used by MonetDB - * @return the matching java.sql.Types constant or java.sql.Types.OTHER if nothing matched on the given string + * @param type the SQL data type name as used by MonetDB + * @return the matching java.sql.Types constant or + * java.sql.Types.OTHER if nothing matched the given type name */ - public static int getJavaType(String type) { - // match the currentColumns type on a java.sql.Types constant + public static int getJdbcSQLType(String type) { + // find the column type name in the typeMap Integer tp = typeMap.get(type); if (tp != null) { - return tp; - } else { - // this should not be able to happen do not assert, since maybe future versions introduce new types - return Types.OTHER; + return tp.intValue(); } + // When type name is not found in the map, for instance + // when it is a new type (not yet added in the above typeMap) or + // when type name is: any or geometry or geometrya or mbr or ptr or table. + return Types.OTHER; } private static String TypeMapppingSQL = null; // cache to optimise getSQLTypeMap()
--- a/src/main/java/nl/cwi/monetdb/jdbc/MonetPreparedStatement.java +++ b/src/main/java/nl/cwi/monetdb/jdbc/MonetPreparedStatement.java @@ -116,7 +116,7 @@ public class MonetPreparedStatement exte int column_colnr = rs.findColumn("column"); for (int i = 0; rs.next(); i++) { monetdbType[i] = rs.getString(type_colnr); - javaType[i] = MonetDriver.getJavaType(monetdbType[i]); + javaType[i] = MonetDriver.getJdbcSQLType(monetdbType[i]); if (javaType[i] == Types.CLOB) { if (connection.mapClobAsVarChar()) javaType[i] = Types.VARCHAR; @@ -178,7 +178,7 @@ public class MonetPreparedStatement exte */ @Override public void clearParameters() { - for (int i = 0; i < values.length; i++) { + for (int i = 0; i < size; i++) { values[i] = null; } }
--- a/src/main/java/nl/cwi/monetdb/jdbc/MonetResultSet.java +++ b/src/main/java/nl/cwi/monetdb/jdbc/MonetResultSet.java @@ -145,7 +145,7 @@ public class MonetResultSet extends Mone } catch (SQLException se) { /* ignore it */ } for (int i = 0; i < types.length; i++) { - int javaSQLtype = MonetDriver.getJavaType(types[i]); + int javaSQLtype = MonetDriver.getJdbcSQLType(types[i]); if (javaSQLtype == Types.CLOB) { if (connection != null && connection.mapClobAsVarChar()) javaSQLtype = Types.VARCHAR;
--- a/src/main/java/nl/cwi/monetdb/mcl/responses/ResultSetResponse.java +++ b/src/main/java/nl/cwi/monetdb/mcl/responses/ResultSetResponse.java @@ -133,7 +133,7 @@ public class ResultSetResponse implement */ private void populateJdbcSQLTypesArray() { for (int i = 0; i < this.type.length; i++) { - int javaSQLtype = MonetDriver.getJavaType(this.type[i]); + int javaSQLtype = MonetDriver.getJdbcSQLType(this.type[i]); if (javaSQLtype == Types.BLOB && con.mapBlobAsVarBinary()) { javaSQLtype = Types.LONGVARBINARY; }