Mercurial > hg > monetdb-java

comparison src/main/java/nl/cwi/monetdb/jdbc/MonetDatabaseMetaData.java @ 0:a5a898f6886c

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Copy of MonetDB java directory changeset e6e32756ad31.
author Sjoerd Mullender <sjoerd@acm.org>
date Wed, 21 Sep 2016 09:34:48 +0200 (2016-09-21)
parents
children b3ca1157be73
comparison
equal deleted inserted replaced
-1:000000000000 0:a5a898f6886c
1 /*
2 * This Source Code Form is subject to the terms of the Mozilla Public
3 * License, v. 2.0. If a copy of the MPL was not distributed with this
4 * file, You can obtain one at http://mozilla.org/MPL/2.0/.
5 *
6 * Copyright 1997 - July 2008 CWI, August 2008 - 2016 MonetDB B.V.
7 */
8
9 package nl.cwi.monetdb.jdbc;
10
11 import java.sql.Connection;
12 import java.sql.DatabaseMetaData;
13 import java.sql.Statement;
14 import java.sql.SQLException;
15 import java.sql.ResultSet;
16 import java.sql.ResultSetMetaData;
17 import java.sql.RowIdLifetime;
18 import java.sql.Types;
19
20 import java.util.ArrayList;
21
22 /**
23 * A DatabaseMetaData object suitable for the MonetDB database.
24 *
25 * @author Fabian Groffen, Martin van Dinther
26 * @version 0.6
27 */
28 public class MonetDatabaseMetaData extends MonetWrapper implements DatabaseMetaData {
29 private Connection con;
30 private String env_current_user;
31 private String env_monet_version;
32 private String env_max_clients;
33
34 public MonetDatabaseMetaData(Connection parent) {
35 con = parent;
36 }
37
38 /**
39 * Internal cache for 3 environment values retrieved from the
40 * server, to avoid querying the server over and over again.
41 * Once a value is read, it is kept in the private env_* variables for reuse.
42 * We currently only need the env values of: current_user, monet_version and max_clients.
43 */
44 private synchronized void getEnvValues() {
45 Statement st = null;
46 ResultSet rs = null;
47 try {
48 st = con.createStatement();
49 rs = st.executeQuery(
50 "SELECT \"name\", \"value\" FROM \"sys\".\"environment\"" +
51 " WHERE \"name\" IN ('monet_version', 'max_clients')" +
52 " UNION SELECT 'current_user' as \"name\", current_user as \"value\"");
53 if (rs != null) {
54 while (rs.next()) {
55 String prop = rs.getString("name");
56 String value = rs.getString("value");
57 if ("current_user".equals(prop)) {
58 env_current_user = value;
59 } else
60 if ("monet_version".equals(prop)) {
61 env_monet_version = value;
62 } else
63 if ("max_clients".equals(prop)) {
64 env_max_clients = value;
65 }
66 }
67 }
68 } catch (SQLException e) {
69 // ignore
70 } finally {
71 if (rs != null) {
72 try {
73 rs.close();
74 } catch (SQLException e) { /* ignore */ }
75 }
76 if (st != null) {
77 try {
78 st.close();
79 } catch (SQLException e) { /* ignore */ }
80 }
81 }
82 // for debug: System.out.println("Read: env_current_user: " + env_current_user + " env_monet_version: " + env_monet_version + " env_max_clients: " + env_max_clients);
83 }
84
85
86 /**
87 * Internal utility method to create a Statement object, execute a query and return the ResulSet object.
88 * As the Statement object is created internally (the caller does not see it and thus can not close it),
89 * we set it to close (and free server resources) when the ResultSet object is closed by the caller.
90 */
91 private ResultSet executeMetaDataQuery(String query) throws SQLException {
92 Statement stmt = null;
93 ResultSet rs = null;
94 stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
95 if (stmt != null) {
96 // for debug: System.out.println("SQL (len " + query.length() + "): " + query);
97 rs = stmt.executeQuery(query);
98 if (rs != null) {
99 /* we want the statement object to be closed also when the resultset is closed by the caller */
100 stmt.closeOnCompletion();
101 } else {
102 /* failed to produce a resultset, so release resources for created statement object now */
103 stmt.close();
104 }
105 }
106 return rs;
107 }
108
109 /**
110 * Can all the procedures returned by getProcedures be called
111 * by the current user?
112 *
113 * @return true if so
114 */
115 @Override
116 public boolean allProceduresAreCallable() {
117 return true;
118 }
119
120 /**
121 * Can all the tables returned by getTable be SELECTed by
122 * the current user?
123 *
124 * @return true because we only have one user a.t.m.
125 */
126 @Override
127 public boolean allTablesAreSelectable() {
128 return true;
129 }
130
131 /**
132 * What is the URL for this database?
133 *
134 * @return a reconstructed connection string
135 * @throws SQLException if a database access error occurs
136 */
137 @Override
138 public String getURL() throws SQLException {
139 return ((MonetConnection)con).getJDBCURL();
140 }
141
142 /**
143 * What is our user name as known to the database?
144 *
145 * @return sql user
146 * @throws SQLException if a database access error occurs
147 */
148 @Override
149 public String getUserName() throws SQLException {
150 if (env_current_user == null)
151 getEnvValues();
152 return env_current_user;
153 }
154
155 /**
156 * Is the database in read-only mode?
157 *
158 * @return always false for now
159 */
160 @Override
161 public boolean isReadOnly() {
162 return false;
163 }
164
165 /**
166 * Are NULL values sorted high?
167 *
168 * @return true because MonetDB puts NULL values on top upon ORDER BY
169 */
170 @Override
171 public boolean nullsAreSortedHigh() {
172 return true;
173 }
174
175 /**
176 * Are NULL values sorted low?
177 *
178 * @return negative of nullsAreSortedHigh()
179 * @see #nullsAreSortedHigh()
180 */
181 @Override
182 public boolean nullsAreSortedLow() {
183 return !nullsAreSortedHigh();
184 }
185
186 /**
187 * Are NULL values sorted at the start regardless of sort order?
188 *
189 * @return false, since MonetDB doesn't do this
190 */
191 @Override
192 public boolean nullsAreSortedAtStart() {
193 return false;
194 }
195
196 /**
197 * Are NULL values sorted at the end regardless of sort order?
198 *
199 * @return false, since MonetDB doesn't do this
200 */
201 @Override
202 public boolean nullsAreSortedAtEnd() {
203 return false;
204 }
205
206 /**
207 * What is the name of this database product - this should be MonetDB
208 * of course, so we return that explicitly.
209 *
210 * @return the database product name
211 */
212 @Override
213 public String getDatabaseProductName() {
214 return "MonetDB";
215 }
216
217 /**
218 * What is the version of this database product.
219 *
220 * @return the mserver5 version number string
221 * @throws SQLException if a database access error occurs
222 */
223 @Override
224 public String getDatabaseProductVersion() throws SQLException {
225 if (env_monet_version == null)
226 getEnvValues();
227 return env_monet_version;
228 }
229
230 /**
231 * What is the name of this JDBC driver?
232 *
233 * @return the JDBC driver name
234 */
235 @Override
236 public String getDriverName() {
237 return "MonetDB Native Driver";
238 }
239
240 /**
241 * Retrieves the version number of this JDBC driver as a String.
242 *
243 * @return the JDBC driver version string
244 */
245 @Override
246 public String getDriverVersion() {
247 return MonetDriver.getDriverVersion();
248 }
249
250 /**
251 * What is this JDBC driver's major version number?
252 *
253 * @return the JDBC driver major version number
254 */
255 @Override
256 public int getDriverMajorVersion() {
257 return MonetDriver.getDriverMajorVersion();
258 }
259
260 /**
261 * What is this JDBC driver's minor version number?
262 *
263 * @return the JDBC driver minor version number
264 */
265 @Override
266 public int getDriverMinorVersion() {
267 return MonetDriver.getDriverMinorVersion();
268 }
269
270 /**
271 * Does the database store tables in a local file? No - it
272 * stores them in a file on the server.
273 *
274 * @return false because that's what MonetDB is for
275 */
276 @Override
277 public boolean usesLocalFiles() {
278 return false;
279 }
280
281 /**
282 * Does the database use a local file for each table? Well, not really,
283 * since it doesn't use local files.
284 *
285 * @return false for it doesn't
286 */
287 @Override
288 public boolean usesLocalFilePerTable() {
289 return false;
290 }
291
292 /**
293 * Does the database treat mixed case unquoted SQL identifiers
294 * as case sensitive and as a result store them in mixed case?
295 * A JDBC-Compliant driver will always return false.
296 *
297 * @return false
298 */
299 @Override
300 public boolean supportsMixedCaseIdentifiers() {
301 return false;
302 }
303
304 /**
305 * Does the database treat mixed case unquoted SQL identifiers as
306 * case insensitive and store them in upper case?
307 *
308 * @return true if so
309 */
310 @Override
311 public boolean storesUpperCaseIdentifiers() {
312 return false;
313 }
314
315 /**
316 * Does the database treat mixed case unquoted SQL identifiers as
317 * case insensitive and store them in lower case?
318 *
319 * @return true if so
320 */
321 @Override
322 public boolean storesLowerCaseIdentifiers() {
323 return true;
324 }
325
326 /**
327 * Does the database treat mixed case unquoted SQL identifiers as
328 * case insensitive and store them in mixed case?
329 *
330 * @return true if so
331 */
332 @Override
333 public boolean storesMixedCaseIdentifiers() {
334 return false;
335 }
336
337 /**
338 * Does the database treat mixed case quoted SQL identifiers as
339 * case sensitive and as a result store them in mixed case? A
340 * JDBC compliant driver will always return true.
341 *
342 * @return true if so
343 */
344 @Override
345 public boolean supportsMixedCaseQuotedIdentifiers() {
346 return true;
347 }
348
349 /**
350 * Does the database treat mixed case quoted SQL identifiers as
351 * case insensitive and store them in upper case?
352 *
353 * @return true if so
354 */
355 @Override
356 public boolean storesUpperCaseQuotedIdentifiers() {
357 return false;
358 }
359
360 /**
361 * Does the database treat mixed case quoted SQL identifiers as case
362 * insensitive and store them in lower case?
363 *
364 * @return true if so
365 */
366 @Override
367 public boolean storesLowerCaseQuotedIdentifiers() {
368 return false;
369 }
370
371 /**
372 * Does the database treat mixed case quoted SQL identifiers as case
373 * insensitive and store them in mixed case?
374 *
375 * @return true if so
376 */
377 @Override
378 public boolean storesMixedCaseQuotedIdentifiers() {
379 return false;
380 }
381
382 /**
383 * What is the string used to quote SQL identifiers? This returns
384 * a space if identifier quoting isn't supported. A JDBC Compliant
385 * driver will always use a double quote character.
386 *
387 * @return the quoting string
388 */
389 @Override
390 public String getIdentifierQuoteString() {
391 return "\"";
392 }
393
394 /**
395 * Get a comma separated list of all a database's SQL keywords that
396 * are NOT also SQL:2003 keywords.
397 *
398 * @return a comma separated list of MonetDB keywords
399 */
400 @Override
401 public String getSQLKeywords() {
402 String keywords = getConcatenatedStringFromQuery("SELECT \"keyword\" FROM \"sys\".\"keywords\" ORDER BY 1");
403
404 /* An old MonetDB server (pre Jul2015 release) will not have a table sys.keywords and return an empty String */
405 return (keywords.length() > 0) ? keywords :
406 /* for old servers return static list (as returned in clients/odbc/driver/SQLGetInfo.c case SQL_KEYWORDS:) */
407 "ADMIN,AFTER,AGGREGATE,ALWAYS,ASYMMETRIC,ATOMIC," +
408 "AUTO_INCREMENT,BEFORE,BIGINT,BIGSERIAL,BINARY,BLOB," +
409 "CALL,CHAIN,CLOB,COMMITTED,COPY,CORR,CUME_DIST," +
410 "CURRENT_ROLE,CYCLE,DATABASE,DELIMITERS,DENSE_RANK," +
411 "DO,EACH,ELSEIF,ENCRYPTED,EVERY,EXCLUDE,FOLLOWING," +
412 "FUNCTION,GENERATED,IF,ILIKE,INCREMENT,LAG,LEAD," +
413 "LIMIT,LOCALTIME,LOCALTIMESTAMP,LOCKED,MAXVALUE," +
414 "MEDIAN,MEDIUMINT,MERGE,MINVALUE,NEW,NOCYCLE," +
415 "NOMAXVALUE,NOMINVALUE,NOW,OFFSET,OLD,OTHERS,OVER," +
416 "PARTITION,PERCENT_RANK,PLAN,PRECEDING,PROD,QUANTILE," +
417 "RANGE,RANK,RECORDS,REFERENCING,REMOTE,RENAME," +
418 "REPEATABLE,REPLICA,RESTART,RETURN,RETURNS," +
419 "ROW_NUMBER,ROWS,SAMPLE,SAVEPOINT,SCHEMA,SEQUENCE," +
420 "SERIAL,SERIALIZABLE,SIMPLE,START,STATEMENT,STDIN," +
421 "STDOUT,STREAM,STRING,SYMMETRIC,TIES,TINYINT,TRIGGER," +
422 "UNBOUNDED,UNCOMMITTED,UNENCRYPTED,WHILE,XMLAGG," +
423 "XMLATTRIBUTES,XMLCOMMENT,XMLCONCAT,XMLDOCUMENT," +
424 "XMLELEMENT,XMLFOREST,XMLNAMESPACES,XMLPARSE,XMLPI," +
425 "XMLQUERY,XMLSCHEMA,XMLTEXT,XMLVALIDATE";
426 }
427
428 /**
429 * Internal utility method getConcatenatedStringFromQuery(String query)
430 * args: query: SQL SELECT query. Only the output of the first column is concatenated.
431 * @return a String of query result values concatenated into one string, and values separated by comma's
432 */
433 private String getConcatenatedStringFromQuery(String query) {
434 StringBuilder sb = new StringBuilder(1024);
435 Statement st = null;
436 ResultSet rs = null;
437 try {
438 st = con.createStatement();
439 rs = st.executeQuery(query);
440 // Fetch the first column output and concatenate the values into a StringBuilder separated by comma's
441 boolean isfirst = true;
442 while (rs.next()) {
443 String value = rs.getString(1);
444 if (value != null) {
445 if (isfirst) {
446 isfirst = false;
447 } else {
448 sb.append(',');
449 }
450 sb.append(value);
451 }
452 }
453 } catch (SQLException e) {
454 /* ignore */
455 } finally {
456 if (rs != null) {
457 try {
458 rs.close();
459 } catch (SQLException e) { /* ignore */ }
460 }
461 if (st != null) {
462 try {
463 st.close();
464 } catch (SQLException e) { /* ignore */ }
465 }
466 }
467 // for debug: System.out.println("SQL query: " + query + "\nResult string: " + sb.toString());
468 return sb.toString();
469 }
470
471 // SQL query parts shared in below four getXxxxFunctions() methods
472 private final static String FunctionsSelect = "SELECT DISTINCT \"name\" FROM \"sys\".\"functions\" ";
473 private final static String FunctionsWhere = "WHERE \"id\" IN (SELECT \"func_id\" FROM \"sys\".\"args\" WHERE \"number\" = 1 AND \"name\" = 'arg_1' AND \"type\" IN ";
474
475 @Override
476 public String getNumericFunctions() {
477 String match =
478 "('tinyint', 'smallint', 'int', 'bigint', 'hugeint', 'decimal', 'double', 'real') )" +
479 // exclude functions which belong to the 'str' module
480 " AND \"mod\" <> 'str'";
481 return getConcatenatedStringFromQuery(FunctionsSelect + FunctionsWhere + match + " ORDER BY 1");
482 }
483
484 @Override
485 public String getStringFunctions() {
486 String match =
487 "('char', 'varchar', 'clob', 'json') )" +
488 // include functions which belong to the 'str' module
489 " OR \"mod\" = 'str'";
490 return getConcatenatedStringFromQuery(FunctionsSelect + FunctionsWhere + match + " ORDER BY 1");
491 }
492
493 @Override
494 public String getSystemFunctions() {
495 String wherePart =
496 "WHERE \"id\" NOT IN (SELECT \"func_id\" FROM \"sys\".\"args\" WHERE \"number\" = 1)" +
497 " AND \"id\" IN (SELECT \"function_id\" FROM \"sys\".\"systemfunctions\")" +
498 " AND \"type\" = 1" + // only scalar functions
499 // add system functions which are not listed in sys.functions but implemented in the SQL parser (see sql/server/sql_parser.y)
500 " UNION SELECT 'cast'" +
501 " UNION SELECT 'convert'" +
502 " UNION SELECT 'coalesce'" +
503 " UNION SELECT 'extract'" +
504 " UNION SELECT 'ifthenelse'" +
505 " UNION SELECT 'isnull'" +
506 " UNION SELECT 'nullif'";
507 return getConcatenatedStringFromQuery(FunctionsSelect + wherePart + " ORDER BY 1");
508 }
509
510 @Override
511 public String getTimeDateFunctions() {
512 String match =
513 "('date', 'time', 'timestamp', 'timetz', 'timestamptz', 'sec_interval', 'month_interval') )";
514 return getConcatenatedStringFromQuery(FunctionsSelect + FunctionsWhere + match + " ORDER BY 1");
515 }
516
517 /**
518 * This is the string that can be used to escape '_' and '%' in
519 * a search string pattern style catalog search parameters
520 *
521 * @return the string used to escape wildcard characters
522 */
523 @Override
524 public String getSearchStringEscape() {
525 return "\\";
526 }
527
528 /**
529 * Get all the "extra" characters that can be used in unquoted
530 * identifier names (those beyond a-zA-Z0-9 and _)
531 * MonetDB has no extra characters (verified it for chars: !@#$%^&*()~{}[]?
532 *
533 * @return a string containing the extra characters
534 */
535 @Override
536 public String getExtraNameCharacters() {
537 return "";
538 }
539
540 /**
541 * Is "ALTER TABLE" with an add column supported?
542 *
543 * @return true if so
544 */
545 @Override
546 public boolean supportsAlterTableWithAddColumn() {
547 return true;
548 }
549
550 /**
551 * Is "ALTER TABLE" with a drop column supported?
552 *
553 * @return true if so
554 */
555 @Override
556 public boolean supportsAlterTableWithDropColumn() {
557 return true;
558 }
559
560 /**
561 * Is column aliasing supported?
562 *
563 * <p>If so, the SQL AS clause can be used to provide names for
564 * computed columns or to provide alias names for columns as
565 * required. A JDBC Compliant driver always returns true.
566 *
567 * <p>e.g.
568 *
569 * <br><pre>
570 * select count(C) as C_COUNT from T group by C;
571 *
572 * </pre><br>
573 * should return a column named as C_COUNT instead of count(C)
574 *
575 * @return true if so
576 */
577 @Override
578 public boolean supportsColumnAliasing() {
579 return true;
580 }
581
582 /**
583 * Are concatenations between NULL and non-NULL values NULL? A
584 * JDBC Compliant driver always returns true
585 *
586 * @return true if so
587 */
588 @Override
589 public boolean nullPlusNonNullIsNull() {
590 return true;
591 }
592
593 /**
594 * Retrieves whether this database supports the JDBC scalar function
595 * CONVERT for the conversion of one JDBC type to another.
596 * The JDBC types are the generic SQL data types defined in java.sql.Types.
597 *
598 * @return true if so; false otherwise
599 */
600 @Override
601 public boolean supportsConvert() {
602 return true;
603 }
604
605 /**
606 * Retrieves whether this database supports the JDBC scalar function
607 * CONVERT for conversions between the JDBC types fromType and toType.
608 * The JDBC types are the generic SQL data types defined in java.sql.Types.
609 *
610 * @return true if so; false otherwise
611 */
612 @Override
613 public boolean supportsConvert(int fromType, int toType) {
614 switch (fromType) {
615 case Types.BOOLEAN:
616 switch (toType) {
617 case Types.BOOLEAN:
618 /* case Types.BIT: is not supported by MonetDB and will fail */
619 case Types.TINYINT:
620 case Types.SMALLINT:
621 case Types.INTEGER:
622 case Types.BIGINT:
623 /* conversion to FLOAT, REAL, DOUBLE, NUMERIC and DECIMAL is not supported by MonetDB */
624 case Types.CHAR:
625 case Types.VARCHAR:
626 /* case Types.LONGVARCHAR: is not supported by MonetDB and will fail */
627 case Types.CLOB:
628 return true;
629 }
630 // conversion to all other types is not supported
631 return false;
632 /* case Types.BIT: is not supported by MonetDB and will fail */
633 /* case Types.BINARY: is not supported by MonetDB and will fail */
634 /* case Types.VARBINARY: is not supported by MonetDB and will fail */
635 /* case Types.LONGVARBINARY: is not supported by MonetDB and will fail */
636 case Types.BLOB:
637 switch (toType) {
638 /* case Types.BINARY: is not supported by MonetDB and will fail */
639 /* case Types.VARBINARY: is not supported by MonetDB and will fail */
640 /* case Types.LONGVARBINARY: is not supported by MonetDB and will fail */
641 case Types.BLOB:
642 case Types.VARCHAR:
643 /* case Types.LONGVARCHAR: is not supported by MonetDB and will fail */
644 case Types.CLOB:
645 return true;
646 }
647 // conversion to all other types is not supported
648 return false;
649 case Types.TINYINT:
650 case Types.SMALLINT:
651 case Types.INTEGER:
652 case Types.BIGINT:
653 case Types.FLOAT:
654 case Types.REAL:
655 case Types.DOUBLE:
656 case Types.NUMERIC:
657 case Types.DECIMAL:
658 switch (toType) {
659 case Types.BOOLEAN:
660 /* case Types.BIT: is not supported by MonetDB and will fail */
661 case Types.TINYINT:
662 case Types.SMALLINT:
663 case Types.INTEGER:
664 case Types.BIGINT:
665 case Types.FLOAT:
666 case Types.REAL:
667 case Types.DOUBLE:
668 case Types.NUMERIC:
669 case Types.DECIMAL:
670 case Types.CHAR:
671 case Types.VARCHAR:
672 /* case Types.LONGVARCHAR: is not supported by MonetDB and will fail */
673 case Types.CLOB:
674 return true;
675 }
676 // conversion to all other types is not supported
677 return false;
678 case Types.CHAR:
679 case Types.VARCHAR:
680 /* case Types.LONGVARCHAR: is not supported by MonetDB and will fail */
681 case Types.CLOB:
682 switch (toType) {
683 case Types.BOOLEAN:
684 /* case Types.BIT: is not supported by MonetDB and will fail */
685 case Types.TINYINT:
686 case Types.SMALLINT:
687 case Types.INTEGER:
688 case Types.BIGINT:
689 case Types.FLOAT:
690 case Types.REAL:
691 case Types.DOUBLE:
692 case Types.NUMERIC:
693 case Types.DECIMAL:
694 case Types.CHAR:
695 case Types.VARCHAR:
696 /* case Types.LONGVARCHAR: is not supported by MonetDB and will fail */
697 case Types.CLOB:
698 case Types.BLOB:
699 case Types.DATE:
700 case Types.TIME:
701 case Types.TIMESTAMP:
702 return true;
703 }
704 // conversion to all other types is not supported
705 return false;
706 case Types.DATE:
707 switch (toType) {
708 case Types.CHAR:
709 case Types.VARCHAR:
710 /* case Types.LONGVARCHAR: is not supported by MonetDB and will fail */
711 case Types.CLOB:
712 case Types.DATE:
713 case Types.TIMESTAMP:
714 return true;
715 }
716 // conversion to all other types is not supported
717 return false;
718 case Types.TIME:
719 switch (toType) {
720 case Types.CHAR:
721 case Types.VARCHAR:
722 /* case Types.LONGVARCHAR: is not supported by MonetDB and will fail */
723 case Types.CLOB:
724 case Types.TIME:
725 return true;
726 }
727 // conversion to all other types is not supported
728 return false;
729 case Types.TIMESTAMP:
730 switch (toType) {
731 case Types.CHAR:
732 case Types.VARCHAR:
733 /* case Types.LONGVARCHAR: is not supported by MonetDB and will fail */
734 case Types.CLOB:
735 case Types.DATE:
736 case Types.TIME:
737 case Types.TIMESTAMP:
738 return true;
739 }
740 // conversion to all other types is not supported
741 return false;
742 }
743
744 // conversion from all other JDBC SQL types are not supported
745 return false;
746 }
747
748 /**
749 * Are table correlation names supported? A JDBC Compliant
750 * driver always returns true.
751 *
752 * @return true if so
753 */
754 @Override
755 public boolean supportsTableCorrelationNames() {
756 return true;
757 }
758
759 /**
760 * If table correlation names are supported, are they restricted to
761 * be different from the names of the tables?
762 *
763 * @return true if so; false otherwise
764 */
765 @Override
766 public boolean supportsDifferentTableCorrelationNames() {
767 return false;
768 }
769
770 /**
771 * Are expressions in "ORDER BY" lists supported?
772 * e.g. select * from t order by a + b;
773 *
774 * MonetDB supports this, try:
775 * select (radix * 1000) + digits as comp, * from types order by (radix * 1000) + digits, -id;
776 *
777 * @return true if so
778 */
779 @Override
780 public boolean supportsExpressionsInOrderBy() {
781 return true;
782 }
783
784 /**
785 * Can an "ORDER BY" clause use columns not in the SELECT?
786 * MonetDB differs from SQL03 =&gt; true
787 *
788 * @return true if so
789 */
790 @Override
791 public boolean supportsOrderByUnrelated() {
792 return true;
793 }
794
795 /**
796 * Is some form of "GROUP BY" clause supported?
797 *
798 * @return true since MonetDB supports it
799 */
800 @Override
801 public boolean supportsGroupBy() {
802 return true;
803 }
804
805 /**
806 * Can a "GROUP BY" clause use columns not in the SELECT?
807 *
808 * @return true since that also is supported
809 */
810 @Override
811 public boolean supportsGroupByUnrelated() {
812 return true;
813 }
814
815 /**
816 * Can a "GROUP BY" clause add columns not in the SELECT provided
817 * it specifies all the columns in the SELECT?
818 *
819 * (MonetDB already supports the more difficult supportsGroupByUnrelated(),
820 * so this is a piece of cake)
821 *
822 * @return true if so
823 */
824 @Override
825 public boolean supportsGroupByBeyondSelect() {
826 return true;
827 }
828
829 /**
830 * Is the escape character in "LIKE" clauses supported? A
831 * JDBC compliant driver always returns true.
832 *
833 * @return true if so
834 */
835 @Override
836 public boolean supportsLikeEscapeClause() {
837 return true;
838 }
839
840 /**
841 * Are multiple ResultSets from a single execute supported?
842 *
843 * @return true if so
844 */
845 @Override
846 public boolean supportsMultipleResultSets() {
847 return true;
848 }
849
850 /**
851 * Can we have multiple transactions open at once (on different
852 * connections?)
853 * This is the main idea behind the Connection, is it?
854 *
855 * @return true if so
856 */
857 @Override
858 public boolean supportsMultipleTransactions() {
859 return true;
860 }
861
862 /**
863 * Can columns be defined as non-nullable. A JDBC Compliant driver
864 * always returns true.
865 *
866 * @return true if so
867 */
868 @Override
869 public boolean supportsNonNullableColumns() {
870 return true;
871 }
872
873 /**
874 * Does this driver support the minimum ODBC SQL grammar. This
875 * grammar is defined at:
876 *
877 * http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odappcpr.asp
878 * From this description, we seem to support the ODBC minimal (Level 0) grammar.
879 *
880 * @return true if so
881 */
882 @Override
883 public boolean supportsMinimumSQLGrammar() {
884 return true;
885 }
886
887 /**
888 * Does this driver support the Core ODBC SQL grammar. We need
889 * SQL-92 conformance for this.
890 *
891 * @return true if so
892 */
893 @Override
894 public boolean supportsCoreSQLGrammar() {
895 return true;
896 }
897
898 /**
899 * Does this driver support the Extended (Level 2) ODBC SQL
900 * grammar. We don't conform to the Core (Level 1), so we can't
901 * conform to the Extended SQL Grammar.
902 *
903 * @return true if so
904 */
905 @Override
906 public boolean supportsExtendedSQLGrammar() {
907 return false;
908 }
909
910 /**
911 * Does this driver support the ANSI-92 entry level SQL grammar?
912 * All JDBC Compliant drivers must return true. We should be this
913 * compliant, so let's 'act' like we are.
914 *
915 * @return true if so
916 */
917 @Override
918 public boolean supportsANSI92EntryLevelSQL() {
919 return true;
920 }
921
922 /**
923 * Does this driver support the ANSI-92 intermediate level SQL
924 * grammar?
925 * probably not
926 *
927 * @return true if so
928 */
929 @Override
930 public boolean supportsANSI92IntermediateSQL() {
931 return false;
932 }
933
934 /**
935 * Does this driver support the ANSI-92 full SQL grammar?
936 * Would be good if it was like that
937 *
938 * @return true if so
939 */
940 @Override
941 public boolean supportsANSI92FullSQL() {
942 return false;
943 }
944
945 /**
946 * Is the SQL Integrity Enhancement Facility supported?
947 * Our best guess is that this means support for constraints
948 *
949 * @return true if so
950 */
951 @Override
952 public boolean supportsIntegrityEnhancementFacility() {
953 return true;
954 }
955
956 /**
957 * Is some form of outer join supported?
958 *
959 * @return true if so
960 */
961 @Override
962 public boolean supportsOuterJoins(){
963 return true;
964 }
965
966 /**
967 * Are full nexted outer joins supported?
968 *
969 * @return true if so
970 */
971 @Override
972 public boolean supportsFullOuterJoins() {
973 return true;
974 }
975
976 /**
977 * Is there limited support for outer joins?
978 *
979 * @return true if so
980 */
981 @Override
982 public boolean supportsLimitedOuterJoins() {
983 return false;
984 }
985
986 /**
987 * What is the database vendor's preferred term for "schema"?
988 * MonetDB uses the term "schema".
989 *
990 * @return the vendor term
991 */
992 @Override
993 public String getSchemaTerm() {
994 return "schema";
995 }
996
997 /**
998 * What is the database vendor's preferred term for "procedure"?
999 *
1000 * @return the vendor term
1001 */
1002 @Override
1003 public String getProcedureTerm() {
1004 return "procedure";
1005 }
1006
1007 /**
1008 * What is the database vendor's preferred term for "catalog"?
1009 * MonetDB doesn't really have them (from driver accessible) but
1010 * from the monetdb.conf file the term "database" sounds best
1011 *
1012 * @return the vendor term
1013 */
1014 @Override
1015 public String getCatalogTerm() {
1016 return "database";
1017 }
1018
1019 /**
1020 * Does a catalog appear at the start of a qualified table name?
1021 * (Otherwise it appears at the end).
1022 * Currently there is no catalog support at all in MonetDB
1023 *
1024 * @return true if so
1025 */
1026 @Override
1027 public boolean isCatalogAtStart() {
1028 // return true here; we return false for every other catalog function
1029 // so it won't matter what we return here
1030 return true;
1031 }
1032
1033 /**
1034 * What is the Catalog separator.
1035 *
1036 * @return the catalog separator string
1037 */
1038 @Override
1039 public String getCatalogSeparator() {
1040 // MonetDB does NOT support catalogs, so also no catalog separator
1041 return null;
1042 }
1043
1044 /**
1045 * Can a schema name be used in a data manipulation statement?
1046 *
1047 * @return true if so
1048 */
1049 @Override
1050 public boolean supportsSchemasInDataManipulation() {
1051 return true;
1052 }
1053
1054 /**
1055 * Can a schema name be used in a procedure call statement?
1056 * Ohw probably, but I don't know of procedures in MonetDB
1057 *
1058 * @return true if so
1059 */
1060 @Override
1061 public boolean supportsSchemasInProcedureCalls() {
1062 return true;
1063 }
1064
1065 /**
1066 * Can a schema be used in a table definition statement?
1067 *
1068 * @return true if so
1069 */
1070 @Override
1071 public boolean supportsSchemasInTableDefinitions() {
1072 return true;
1073 }
1074
1075 /**
1076 * Can a schema name be used in an index definition statement?
1077 *
1078 * @return true if so
1079 */
1080 @Override
1081 public boolean supportsSchemasInIndexDefinitions() {
1082 return true;
1083 }
1084
1085 /**
1086 * Can a schema name be used in a privilege definition statement?
1087 *
1088 * @return true if so
1089 */
1090 @Override
1091 public boolean supportsSchemasInPrivilegeDefinitions() {
1092 return true;
1093 }
1094
1095 /**
1096 * Can a catalog name be used in a data manipulation statement?
1097 *
1098 * @return true if so
1099 */
1100 @Override
1101 public boolean supportsCatalogsInDataManipulation() {
1102 return false;
1103 }
1104
1105 /**
1106 * Can a catalog name be used in a procedure call statement?
1107 *
1108 * @return true if so
1109 */
1110 @Override
1111 public boolean supportsCatalogsInProcedureCalls() {
1112 return false;
1113 }
1114
1115 /**
1116 * Can a catalog name be used in a table definition statement?
1117 *
1118 * @return true if so
1119 */
1120 @Override
1121 public boolean supportsCatalogsInTableDefinitions() {
1122 return false;
1123 }
1124
1125 /**
1126 * Can a catalog name be used in an index definition?
1127 *
1128 * @return true if so
1129 */
1130 @Override
1131 public boolean supportsCatalogsInIndexDefinitions() {
1132 return false;
1133 }
1134
1135 /**
1136 * Can a catalog name be used in a privilege definition statement?
1137 *
1138 * @return true if so
1139 */
1140 @Override
1141 public boolean supportsCatalogsInPrivilegeDefinitions() {
1142 return false;
1143 }
1144
1145 /**
1146 * MonetDB doesn't support positioned DELETEs I guess
1147 *
1148 * @return true if so
1149 */
1150 @Override
1151 public boolean supportsPositionedDelete() {
1152 return false;
1153 }
1154
1155 /**
1156 * Is positioned UPDATE supported? (same as above)
1157 *
1158 * @return true if so
1159 */
1160 @Override
1161 public boolean supportsPositionedUpdate() {
1162 return false;
1163 }
1164
1165 /**
1166 * Is SELECT FOR UPDATE supported?
1167 * My test resulted in a negative answer
1168 *
1169 * @return true if so; false otherwise
1170 */
1171 @Override
1172 public boolean supportsSelectForUpdate(){
1173 return false;
1174 }
1175
1176 /**
1177 * Are stored procedure calls using the stored procedure escape
1178 * syntax supported?
1179 *
1180 * @return true if so; false otherwise
1181 */
1182 @Override
1183 public boolean supportsStoredProcedures() {
1184 return true;
1185 }
1186
1187 /**
1188 * Are subqueries in comparison expressions supported? A JDBC
1189 * Compliant driver always returns true. MonetDB also supports this
1190 *
1191 * @return true if so; false otherwise
1192 */
1193 @Override
1194 public boolean supportsSubqueriesInComparisons() {
1195 return true;
1196 }
1197
1198 /**
1199 * Are subqueries in 'exists' expressions supported? A JDBC
1200 * Compliant driver always returns true.
1201 *
1202 * @return true if so; false otherwise
1203 */
1204 @Override
1205 public boolean supportsSubqueriesInExists() {
1206 return true;
1207 }
1208
1209 /**
1210 * Are subqueries in 'in' statements supported? A JDBC
1211 * Compliant driver always returns true.
1212 *
1213 * @return true if so; false otherwise
1214 */
1215 @Override
1216 public boolean supportsSubqueriesInIns() {
1217 return true;
1218 }
1219
1220 /**
1221 * Are subqueries in quantified expressions supported? A JDBC
1222 * Compliant driver always returns true.
1223 *
1224 * (No idea what this is, but we support a good deal of
1225 * subquerying.)
1226 *
1227 * @return true if so; false otherwise
1228 */
1229 @Override
1230 public boolean supportsSubqueriesInQuantifieds() {
1231 return true;
1232 }
1233
1234 /**
1235 * Are correlated subqueries supported? A JDBC Compliant driver
1236 * always returns true.
1237 *
1238 * (a.k.a. subselect in from?)
1239 *
1240 * @return true if so; false otherwise
1241 */
1242 @Override
1243 public boolean supportsCorrelatedSubqueries() {
1244 return true;
1245 }
1246
1247 /**
1248 * Is SQL UNION supported?
1249 * since 2004-03-20
1250 *
1251 * @return true if so
1252 */
1253 @Override
1254 public boolean supportsUnion() {
1255 return true;
1256 }
1257
1258 /**
1259 * Is SQL UNION ALL supported?
1260 * since 2004-03-20
1261 *
1262 * @return true if so
1263 */
1264 @Override
1265 public boolean supportsUnionAll() {
1266 return true;
1267 }
1268
1269 /**
1270 * ResultSet objects (cursors) are not closed upon explicit or
1271 * implicit commit.
1272 *
1273 * @return true if so
1274 */
1275 @Override
1276 public boolean supportsOpenCursorsAcrossCommit() {
1277 return true;
1278 }
1279
1280 /**
1281 * Same as above
1282 *
1283 * @return true if so
1284 */
1285 @Override
1286 public boolean supportsOpenCursorsAcrossRollback() {
1287 return true;
1288 }
1289
1290 /**
1291 * Can statements remain open across commits? They may, but
1292 * this driver cannot guarentee that. In further reflection.
1293 * we are taking a Statement object here, so the answer is
1294 * yes, since the Statement is only a vehicle to execute some SQL
1295 *
1296 * @return true if they always remain open; false otherwise
1297 */
1298 @Override
1299 public boolean supportsOpenStatementsAcrossCommit() {
1300 return true;
1301 }
1302
1303 /**
1304 * Can statements remain open across rollbacks? They may, but
1305 * this driver cannot guarentee that. In further contemplation,
1306 * we are taking a Statement object here, so the answer is yes again.
1307 *
1308 * @return true if they always remain open; false otherwise
1309 */
1310 @Override
1311 public boolean supportsOpenStatementsAcrossRollback() {
1312 return true;
1313 }
1314
1315 /**
1316 * How many hex characters can you have in an inline binary literal
1317 * I honestly wouldn't know...
1318 *
1319 * @return the max literal length
1320 */
1321 @Override
1322 public int getMaxBinaryLiteralLength() {
1323 return 0; // no limit
1324 }
1325
1326 /**
1327 * What is the maximum length for a character literal
1328 * Is there a max?
1329 *
1330 * @return the max literal length
1331 */
1332 @Override
1333 public int getMaxCharLiteralLength() {
1334 return 0; // no limit
1335 }
1336
1337 /**
1338 * Whats the limit on column name length.
1339 * I take some safety here, but it's just a varchar in MonetDB
1340 *
1341 * @return the maximum column name length
1342 */
1343 @Override
1344 public int getMaxColumnNameLength() {
1345 return 1024;
1346 }
1347
1348 /**
1349 * What is the maximum number of columns in a "GROUP BY" clause?
1350 *
1351 * @return the max number of columns
1352 */
1353 @Override
1354 public int getMaxColumnsInGroupBy() {
1355 return 0; // no limit
1356 }
1357
1358 /**
1359 * What's the maximum number of columns allowed in an index?
1360 *
1361 * @return max number of columns
1362 */
1363 @Override
1364 public int getMaxColumnsInIndex() {
1365 return 0; // unlimited I guess
1366 }
1367
1368 /**
1369 * What's the maximum number of columns in an "ORDER BY clause?
1370 *
1371 * @return the max columns
1372 */
1373 @Override
1374 public int getMaxColumnsInOrderBy() {
1375 return 0; // unlimited I guess
1376 }
1377
1378 /**
1379 * What is the maximum number of columns in a "SELECT" list?
1380 *
1381 * @return the max columns
1382 */
1383 @Override
1384 public int getMaxColumnsInSelect() {
1385 return 0; // unlimited I guess
1386 }
1387
1388 /**
1389 * What is the maximum number of columns in a table?
1390 * wasn't MonetDB designed for datamining? (= much columns)
1391 *
1392 * @return the max columns
1393 */
1394 @Override
1395 public int getMaxColumnsInTable() {
1396 return 0;
1397 }
1398
1399 /**
1400 * How many active connections can we have at a time to this
1401 * database? Well, since it depends on Mserver, which just listens
1402 * for new connections and creates a new thread for each connection,
1403 * this number can be very high, and theoretically till the system
1404 * runs out of resources. However, knowing MonetDB is knowing that you
1405 * should handle it a little bit with care, so I give a very minimalistic
1406 * number here.
1407 *
1408 * @return the maximum number of connections
1409 */
1410 @Override
1411 public int getMaxConnections() {
1412 if (env_max_clients == null)
1413 getEnvValues();
1414
1415 int max_clients = 16;
1416 if (env_max_clients != null) {
1417 try {
1418 max_clients = Integer.parseInt(env_max_clients);
1419 } catch (NumberFormatException nfe) { /* ignore */ }
1420 }
1421 return max_clients;
1422 }
1423
1424 /**
1425 * What is the maximum cursor name length
1426 * Actually we do not do named cursors, so I keep the value small as
1427 * a precaution for maybe the future.
1428 *
1429 * @return max cursor name length in bytes
1430 */
1431 @Override
1432 public int getMaxCursorNameLength() {
1433 return 1024;
1434 }
1435
1436 /**
1437 * Retrieves the maximum number of bytes for an index, including all
1438 * of the parts of the index.
1439 *
1440 * @return max index length in bytes, which includes the composite
1441 * of all the constituent parts of the index; a result of zero
1442 * means that there is no limit or the limit is not known
1443 */
1444 @Override
1445 public int getMaxIndexLength() {
1446 return 0; // I assume it is large, but I don't know
1447 }
1448
1449 /**
1450 * Retrieves the maximum number of characters that this database
1451 * allows in a schema name.
1452 *
1453 * @return the number of characters or 0 if there is no limit, or the
1454 * limit is unknown.
1455 */
1456 @Override
1457 public int getMaxSchemaNameLength() {
1458 return 1024;
1459 }
1460
1461 /**
1462 * What is the maximum length of a procedure name
1463 *
1464 * @return the max name length in bytes
1465 */
1466 @Override
1467 public int getMaxProcedureNameLength() {
1468 return 1024;
1469 }
1470
1471 /**
1472 * What is the maximum length of a catalog
1473 *
1474 * @return the maximum number of characters allowed in a catalog name;
1475 * a result of zero means that there is no limit or the limit is not known
1476 */
1477 @Override
1478 public int getMaxCatalogNameLength() {
1479 return 0; // MonetDB does not support catalog names
1480 }
1481
1482 /**
1483 * What is the maximum length of a single row?
1484 *
1485 * @return max row size in bytes
1486 */
1487 @Override
1488 public int getMaxRowSize() {
1489 return 0; // very long I hope...
1490 }
1491
1492 /**
1493 * Did getMaxRowSize() include LONGVARCHAR and LONGVARBINARY
1494 * blobs?
1495 * Yes I thought so...
1496 *
1497 * @return true if so
1498 */
1499 @Override
1500 public boolean doesMaxRowSizeIncludeBlobs() {
1501 return true;
1502 }
1503
1504 /**
1505 * What is the maximum length of a SQL statement?
1506 * Till a programmer makes a mistake and causes a segmentation fault
1507 * on a string overflow...
1508 *
1509 * @return max length in bytes
1510 */
1511 @Override
1512 public int getMaxStatementLength() {
1513 return 0; // actually whatever fits in size_t
1514 }
1515
1516 /**
1517 * How many active statements can we have open at one time to
1518 * this database? Basically, since each Statement downloads
1519 * the results as the query is executed, we can have many.
1520 *
1521 * @return the maximum
1522 */
1523 @Override
1524 public int getMaxStatements() {
1525 return 0;
1526 }
1527
1528 /**
1529 * What is the maximum length of a table name
1530 *
1531 * @return max name length in bytes
1532 */
1533 @Override
1534 public int getMaxTableNameLength() {
1535 return 1024;
1536 }
1537
1538 /**
1539 * What is the maximum number of tables that can be specified
1540 * in a SELECT?
1541 *
1542 * @return the maximum
1543 */
1544 @Override
1545 public int getMaxTablesInSelect() {
1546 return 0; // no limit
1547 }
1548
1549 /**
1550 * What is the maximum length of a user name
1551 *
1552 * @return the max name length in bytes
1553 */
1554 @Override
1555 public int getMaxUserNameLength() {
1556 return 512;
1557 }
1558
1559 /**
1560 * What is the database's default transaction isolation level?
1561 * We only see commited data, nonrepeatable reads and phantom
1562 * reads can occur.
1563 *
1564 * @return the default isolation level
1565 * @see Connection
1566 */
1567 @Override
1568 public int getDefaultTransactionIsolation() {
1569 return Connection.TRANSACTION_SERIALIZABLE;
1570 }
1571
1572 /**
1573 * Are transactions supported? If not, commit and rollback are noops
1574 * and the isolation level is TRANSACTION_NONE. We do support
1575 * transactions.
1576 *
1577 * @return true if transactions are supported
1578 */
1579 @Override
1580 public boolean supportsTransactions() {
1581 return true;
1582 }
1583
1584 /**
1585 * Does the database support the given transaction isolation level?
1586 * We only support TRANSACTION_READ_COMMITTED as far as I know
1587 *
1588 * @param level the values are defined in java.sql.Connection
1589 * @return true if so
1590 * @see Connection
1591 */
1592 @Override
1593 public boolean supportsTransactionIsolationLevel(int level) {
1594 return level == Connection.TRANSACTION_SERIALIZABLE;
1595 }
1596
1597 /**
1598 * Are both data definition and data manipulation transactions
1599 * supported?
1600 * Supposedly that data definition is like CREATE or ALTER TABLE
1601 * yes it is.
1602 *
1603 * @return true if so
1604 */
1605 @Override
1606 public boolean supportsDataDefinitionAndDataManipulationTransactions() {
1607 return true;
1608 }
1609
1610 /**
1611 * Are only data manipulation statements within a transaction
1612 * supported?
1613 *
1614 * @return true if so
1615 */
1616 @Override
1617 public boolean supportsDataManipulationTransactionsOnly() {
1618 return false;
1619 }
1620
1621 /**
1622 * Does a data definition statement within a transaction force
1623 * the transaction to commit? I think this means something like:
1624 *
1625 * <p><pre>
1626 * CREATE TABLE T (A INT);
1627 * INSERT INTO T (A) VALUES (2);
1628 * BEGIN;
1629 * UPDATE T SET A = A + 1;
1630 * CREATE TABLE X (A INT);
1631 * SELECT A FROM T INTO X;
1632 * COMMIT;
1633 * </pre></p>
1634 *
1635 * does the CREATE TABLE call cause a commit? The answer is no.
1636 *
1637 * @return true if so
1638 */
1639 @Override
1640 public boolean dataDefinitionCausesTransactionCommit() {
1641 return false;
1642 }
1643
1644 /**
1645 * Is a data definition statement within a transaction ignored?
1646 *
1647 * @return true if so
1648 */
1649 @Override
1650 public boolean dataDefinitionIgnoredInTransactions() {
1651 return false;
1652 }
1653
1654 /**
1655 * Get a description of stored procedures available in a catalog
1656 *
1657 * <p>Only procedure descriptions matching the schema and procedure
1658 * name criteria are returned. They are ordered by PROCEDURE_SCHEM,
1659 * PROCEDURE_NAME and SPECIFIC_NAME.
1660 *
1661 * <p>Each procedure description has the following columns:
1662 * <ol>
1663 * <li><b>PROCEDURE_CAT</b> String => procedure catalog (may be null)
1664 * <li><b>PROCEDURE_SCHEM</b> String => procedure schema (may be null)
1665 * <li><b>PROCEDURE_NAME</b> String => procedure name
1666 * <li><b>Field4</b> reserved (make it null)
1667 * <li><b>Field5</b> reserved (make it null)
1668 * <li><b>Field6</b> reserved (make it null)
1669 * <li><b>REMARKS</b> String => explanatory comment on the procedure
1670 * <li><b>PROCEDURE_TYPE</b> short => kind of procedure
1671 * <ul>
1672 * <li> procedureResultUnknown - May return a result
1673 * <li> procedureNoResult - Does not return a result
1674 * <li> procedureReturnsResult - Returns a result
1675 * </ul>
1676 * </ol>
1677 * <li><b>SPECIFIC_NAME</b> String => The name which uniquely identifies this procedure within its schema.
1678 *
1679 * @param catalog - a catalog name; must match the catalog name as it is stored in the database;
1680 * "" retrieves those without a catalog;
1681 * null means that the catalog name should not be used to narrow the search
1682 * @param schemaPattern - a schema name pattern; must match the schema name as it is stored in the database;
1683 * "" retrieves those without a schema;
1684 * null means that the schema name should not be used to narrow the search
1685 * @param procedureNamePattern - a procedure name pattern; must match the procedure name as it is stored in the database
1686 * @return ResultSet - each row is a procedure description
1687 * @throws SQLException if a database access error occurs
1688 */
1689 @Override
1690 public ResultSet getProcedures(
1691 String catalog,
1692 String schemaPattern,
1693 String procedureNamePattern
1694 ) throws SQLException
1695 {
1696 StringBuilder query = new StringBuilder(980);
1697 query.append("SELECT cast(null as varchar(1)) AS \"PROCEDURE_CAT\", " +
1698 "\"schemas\".\"name\" AS \"PROCEDURE_SCHEM\", " +
1699 "\"functions\".\"name\" AS \"PROCEDURE_NAME\", " +
1700 "cast(null as char(1)) AS \"Field4\", " +
1701 "cast(null as char(1)) AS \"Field5\", " +
1702 "cast(null as char(1)) AS \"Field6\", " +
1703 "cast(\"functions\".\"func\" as varchar(9999)) AS \"REMARKS\", " +
1704 // in MonetDB procedures have no return value by design.
1705 "CAST(").append(DatabaseMetaData.procedureNoResult).append(" AS smallint) AS \"PROCEDURE_TYPE\", " +
1706 // only the id value uniquely identifies a procedure. Include it to be able to differentiate between multiple overloaded procedures with the same name
1707 "cast(\"functions\".\"id\" as varchar(10)) AS \"SPECIFIC_NAME\" " +
1708 "FROM \"sys\".\"functions\" JOIN \"sys\".\"schemas\" ON (\"functions\".\"schema_id\" = \"schemas\".\"id\") " +
1709 // include procedures only (type = 2). Others will be returned via getFunctions()
1710 "WHERE \"functions\".\"type\" = 2");
1711
1712 if (catalog != null && catalog.length() > 0) {
1713 // none empty catalog selection.
1714 // as we do not support catalogs this always results in no rows returned
1715 query.append(" AND 1 = 0");
1716 }
1717 if (schemaPattern != null) {
1718 query.append(" AND \"schemas\".\"name\" ").append(composeMatchPart(schemaPattern));
1719 }
1720 if (procedureNamePattern != null) {
1721 query.append(" AND \"functions\".\"name\" ").append(composeMatchPart(procedureNamePattern));
1722 }
1723
1724 query.append(" ORDER BY \"PROCEDURE_SCHEM\", \"PROCEDURE_NAME\", \"SPECIFIC_NAME\"");
1725
1726 return executeMetaDataQuery(query.toString());
1727 }
1728
1729 /**
1730 * Get a description of a catalog's stored procedure parameters
1731 * and result columns.
1732 *
1733 * <p>Only descriptions matching the schema, procedure and parameter name
1734 * criteria are returned. They are ordered by PROCEDURE_SCHEM, PROCEDURE_NAME
1735 * and SPECIFIC_NAME. Within this, the return value, if any, is first.
1736 * Next are the parameter descriptions in call order. The
1737 * column descriptions follow in column number order.
1738 *
1739 * <p>Each row in the ResultSet is a parameter description or column
1740 * description with the following fields:
1741 * <ol>
1742 * <li><b>PROCEDURE_CAT</b> String => procedure catalog (may be null)
1743 * <li><b>PROCEDURE_SCHEM</b> String => procedure schema (may be null)
1744 * <li><b>PROCEDURE_NAME</b> String => procedure name
1745 * <li><b>COLUMN_NAME</b> String => column/parameter name
1746 * <li><b>COLUMN_TYPE</b> Short => kind of column/parameter:
1747 * <ul><li>procedureColumnUnknown - nobody knows
1748 * <li>procedureColumnIn - IN parameter
1749 * <li>procedureColumnInOut - INOUT parameter
1750 * <li>procedureColumnOut - OUT parameter
1751 * <li>procedureColumnReturn - procedure return value
1752 * <li>procedureColumnResult - result column in ResultSet
1753 * </ul>
1754 * <li><b>DATA_TYPE</b> int => SQL type from java.sql.Types
1755 * <li><b>TYPE_NAME</b> String => SQL type name, for a UDT type the type name is fully qualified
1756 * <li><b>PRECISION</b> int => precision
1757 * <li><b>LENGTH</b> int => length in bytes of data
1758 * <li><b>SCALE</b> short => scale - null is returned for data types where SCALE is not applicable.
1759 * <li><b>RADIX</b> short => radix
1760 * <li><b>NULLABLE</b> short => can it contain NULL?
1761 * <ul><li>procedureNoNulls - does not allow NULL values
1762 * <li>procedureNullable - allows NULL values
1763 * <li>procedureNullableUnknown - nullability unknown
1764 * </ul>
1765 * <li><b>REMARKS</b> String => comment describing parameter/column
1766 * <li><b>COLUMN_DEF</b> String => default value for the column, which should be interpreted as a string when the value is enclosed in single quotes (may be null)
1767 * The string NULL (not enclosed in quotes) - if NULL was specified as the default value
1768 * TRUNCATE (not enclosed in quotes) - if the specified default value cannot be represented without truncation
1769 * NULL - if a default value was not specified
1770 * <li><b>SQL_DATA_TYPE</b> int => reserved for future use
1771 * <li><b>SQL_DATETIME_SUB</b> int => reserved for future use
1772 * <li><b>CHAR_OCTET_LENGTH</b> int => the maximum length of binary and character based columns. For any other datatype the returned value is a NULL
1773 * <li><b>ORDINAL_POSITION</b> int => the ordinal position, starting from 1, for the input and output parameters for a procedure.
1774 * A value of 0 is returned if this row describes the procedure's return value. For result set columns, it is the ordinal position of the
1775 * column in the result set starting from 1. If there are multiple result sets, the column ordinal positions are implementation defined.
1776 * <li><b>IS_NULLABLE</b> String => ISO rules are used to determine the nullability for a column.
1777 * <ul><li>YES --- if the parameter can include NULLs
1778 * <li>NO --- if the parameter cannot include NULLs
1779 * <li>empty string --- if the nullability for the parameter is unknown
1780 * </ul>
1781 * <li><b>SPECIFIC_NAME</b> String => the name which uniquely identifies this procedure within its schema.
1782 * </ol>
1783 * @param catalog - a catalog name; must match the catalog name as it is stored in the database;
1784 * "" retrieves those without a catalog;
1785 * null means that the catalog name should not be used to narrow the search
1786 * @param schemaPattern - a schema name pattern; must match the schema name as it is stored in the database;
1787 * "" retrieves those without a schema;
1788 * null means that the schema name should not be used to narrow the search
1789 * @param procedureNamePattern - a procedure name pattern; must match the procedure name as it is stored in the database
1790 * @param columnNamePattern - a column name pattern; must match the column name as it is stored in the database
1791 * @return ResultSet - each row describes a stored procedure parameter or column
1792 * @throws SQLException if a database-access error occurs
1793 * @see #getSearchStringEscape
1794 */
1795 @Override
1796 public ResultSet getProcedureColumns(
1797 String catalog,
1798 String schemaPattern,
1799 String procedureNamePattern,
1800 String columnNamePattern
1801 ) throws SQLException {
1802 StringBuilder query = new StringBuilder(2900);
1803 query.append("SELECT cast(null as varchar(1)) AS \"PROCEDURE_CAT\", " +
1804 "\"schemas\".\"name\" AS \"PROCEDURE_SCHEM\", " +
1805 "\"functions\".\"name\" AS \"PROCEDURE_NAME\", " +
1806 "\"args\".\"name\" AS \"COLUMN_NAME\", " +
1807 "CAST(CASE \"args\".\"inout\"" +
1808 " WHEN 0 THEN (CASE \"args\".\"number\" WHEN 0 THEN ").append(DatabaseMetaData.procedureColumnReturn).append(" ELSE ").append(DatabaseMetaData.procedureColumnOut).append(" END)" +
1809 " WHEN 1 THEN ").append(DatabaseMetaData.procedureColumnIn)
1810 .append(" ELSE ").append(DatabaseMetaData.procedureColumnUnknown).append(" END AS smallint) AS \"COLUMN_TYPE\", " +
1811 "CAST(").append(MonetDriver.getSQLTypeMap("\"args\".\"type\"")).append(" AS int) AS \"DATA_TYPE\", " +
1812 "\"args\".\"type\" AS \"TYPE_NAME\", " +
1813 "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\", " +
1814 "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\", " +
1815 "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\", " +
1816 "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\", " +
1817 "CAST(").append(DatabaseMetaData.procedureNullableUnknown).append(" AS smallint) AS \"NULLABLE\", " +
1818 "CAST(null as char(1)) AS \"REMARKS\", " +
1819 "CAST(null as char(1)) AS \"COLUMN_DEF\", " +
1820 "CAST(0 as int) AS \"SQL_DATA_TYPE\", " +
1821 "CAST(0 as int) AS \"SQL_DATETIME_SUB\", " +
1822 "CAST(CASE WHEN \"args\".\"type\" IN ('char','varchar','clob') THEN \"args\".\"type_digits\" ELSE NULL END as int) AS \"CHAR_OCTET_LENGTH\", " +
1823 // 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.
1824 "CAST(\"args\".\"number\" + 1 as int) AS \"ORDINAL_POSITION\", " +
1825 "CAST('' as varchar(3)) AS \"IS_NULLABLE\", " +
1826 // the specific name contains the function id, in order to be able to match the args to the correct overloaded procedure name
1827 "cast(\"functions\".\"id\" as varchar(10)) AS \"SPECIFIC_NAME\" " +
1828 "FROM \"sys\".\"args\", \"sys\".\"functions\", \"sys\".\"schemas\" " +
1829 "WHERE \"args\".\"func_id\" = \"functions\".\"id\" " +
1830 "AND \"functions\".\"schema_id\" = \"schemas\".\"id\" " +
1831 // include procedures only (type = 2). Others will be returned via getFunctionColumns()
1832 "AND \"functions\".\"type\" = 2");
1833
1834 if (catalog != null && catalog.length() > 0) {
1835 // none empty catalog selection.
1836 // as we do not support catalogs this always results in no rows returned
1837 query.append(" AND 1 = 0");
1838 }
1839 if (schemaPattern != null) {
1840 query.append(" AND \"schemas\".\"name\" ").append(composeMatchPart(schemaPattern));
1841 }
1842 if (procedureNamePattern != null) {
1843 query.append(" AND \"functions\".\"name\" ").append(composeMatchPart(procedureNamePattern));
1844 }
1845 if (columnNamePattern != null) {
1846 query.append(" AND \"args\".\"name\" ").append(composeMatchPart(columnNamePattern));
1847 }
1848 query.append(" ORDER BY \"PROCEDURE_SCHEM\", \"PROCEDURE_NAME\", \"SPECIFIC_NAME\", \"ORDINAL_POSITION\"");
1849
1850 return executeMetaDataQuery(query.toString());
1851 }
1852
1853
1854 //== this is a helper method which does not belong to the interface
1855
1856 /**
1857 * Returns a SQL match part string where depending on the input value we
1858 * compose an exact match (use =) or match with wildcards (use LIKE)
1859 *
1860 * @param in the string to match
1861 * @return the SQL match part string
1862 */
1863 private static final String composeMatchPart(String in) {
1864 if (in == null)
1865 return "IS NULL";
1866
1867 String sql = "= '";
1868 // check if SQL wildcards are used in the input, if so use LIKE
1869 if (in.contains("%") || in.contains("_"))
1870 sql = "LIKE '";
1871
1872 // all slashes and single quotes in input are escaped with a slash.
1873 String escaped = in.replaceAll("\\\\", "\\\\\\\\").replaceAll("'", "\\\\'");
1874
1875 return sql + escaped + "'";
1876 }
1877
1878 /**
1879 * Returns the given string between two double quotes for usage as
1880 * exact column or table name in SQL queries.
1881 *
1882 * @param in the string to quote
1883 * @return the quoted string
1884 */
1885 // @SuppressWarnings("unused")
1886 // private static final String dq(String in) {
1887 // return "\"" + in.replaceAll("\\\\", "\\\\\\\\").replaceAll("\"", "\\\\\"") + "\"";
1888 // }
1889
1890 //== end helper methods
1891
1892
1893 /**
1894 * Retrieves a description of the tables available in the given catalog.
1895 * Only table descriptions matching the catalog, schema, table name and type criteria are returned.
1896 * They are ordered by TABLE_TYPE, TABLE_CAT, TABLE_SCHEM and TABLE_NAME.
1897 *
1898 * <p>Each table description has the following columns:
1899 *
1900 * <ol>
1901 * <li><b>TABLE_CAT</b> String => table catalog (may be null)
1902 * <li><b>TABLE_SCHEM</b> String => table schema (may be null)
1903 * <li><b>TABLE_NAME</b> String => table name
1904 * <li><b>TABLE_TYPE</b> String => table type. Typical types are "TABLE",
1905 * "VIEW", "SYSTEM TABLE", "GLOBAL TEMPORARY", "LOCAL TEMPORARY", "ALIAS", "SYNONYM".
1906 * <li><b>REMARKS</b> String => explanatory comment on the table
1907 * <li><b>TYPE_CAT</b> String => the types catalog (may be null)
1908 * <li><b>TYPE_SCHEM</b> String => the types schema (may be null)
1909 * <li><b>TYPE_NAME</b> String => type name (may be null)
1910 * <li><b>SELF_REFERENCING_COL_NAME</b> String => name of the designated "identifier" column of a typed table (may be null)
1911 * <li><b>REF_GENERATION</b> String => specifies how values in SELF_REFERENCING_COL_NAME are created. Values are "SYSTEM", "USER", "DERIVED". (may be null)
1912 * </ol>
1913 *
1914 * @param catalog - a catalog name; must match the catalog name as it is stored in the database;
1915 * "" retrieves those without a catalog; null means that the
1916 * catalog name should not be used to narrow the search
1917 * @param schemaPattern - a schema name pattern; must match the schema name as it is stored
1918 * in the database; "" retrieves those without a schema;
1919 * null means that the schema name should not be used to narrow the search
1920 * @param tableNamePattern - a table name pattern; must match the table name as it is stored in the database
1921 * For all tables this should be "%"
1922 * @param types - a list of table types, which must be from the list of table types returned
1923 * from getTableTypes(),to include; null returns all types
1924 * @return ResultSet - each row is a table description
1925 * @throws SQLException if a database-access error occurs.
1926 */
1927 @Override
1928 public ResultSet getTables(
1929 String catalog,
1930 String schemaPattern,
1931 String tableNamePattern,
1932 String types[]
1933 ) throws SQLException
1934 {
1935 // as of Jul2015 release the sys.tables.type values (0 through 6) is extended with new values 10, 11, 20, and 30 (for system and temp tables/views).
1936 // as of Jul2015 release we also have a new table: sys.table_types with names for the new table types
1937 // for correct behavior we need to know if the server is using the old (pre Jul2015) or new sys.tables.type values
1938 boolean preJul2015 = ("11.19.15".compareTo(getDatabaseProductVersion()) >= 0);
1939 /* for debug: System.out.println("getDatabaseProductVersion() is " + getDatabaseProductVersion() + " preJul2015 is " + preJul2015); */
1940
1941 StringBuilder query = new StringBuilder(1600);
1942 if (preJul2015 && types != null && types.length > 0) {
1943 // we need to filter on the constructed "TABLE_TYPE" expression, this is only possible when we use a subquery in the FROM
1944 query.append("SELECT * FROM (");
1945 }
1946 query.append("SELECT DISTINCT cast(null as char(1)) AS \"TABLE_CAT\", " +
1947 "\"schemas\".\"name\" AS \"TABLE_SCHEM\", " +
1948 "\"tables\".\"name\" AS \"TABLE_NAME\", ");
1949 if (preJul2015) {
1950 query.append(
1951 "CASE WHEN \"tables\".\"system\" = true AND \"tables\".\"type\" IN (0, 10) AND \"tables\".\"temporary\" = 0 THEN 'SYSTEM TABLE' " +
1952 "WHEN \"tables\".\"system\" = true AND \"tables\".\"type\" IN (1, 11) AND \"tables\".\"temporary\" = 0 THEN 'SYSTEM VIEW' " +
1953 "WHEN \"tables\".\"system\" = false AND \"tables\".\"type\" = 0 AND \"tables\".\"temporary\" = 0 THEN 'TABLE' " +
1954 "WHEN \"tables\".\"system\" = false AND \"tables\".\"type\" = 1 AND \"tables\".\"temporary\" = 0 THEN 'VIEW' " +
1955 "WHEN \"tables\".\"system\" = true AND \"tables\".\"type\" IN (0, 20) AND \"tables\".\"temporary\" = 1 THEN 'SYSTEM SESSION TABLE' " +
1956 "WHEN \"tables\".\"system\" = true AND \"tables\".\"type\" IN (1, 21) AND \"tables\".\"temporary\" = 1 THEN 'SYSTEM SESSION VIEW' " +
1957 "WHEN \"tables\".\"system\" = false AND \"tables\".\"type\" IN (0, 30) AND \"tables\".\"temporary\" = 1 THEN 'SESSION TABLE' " +
1958 "WHEN \"tables\".\"system\" = false AND \"tables\".\"type\" IN (1, 31) AND \"tables\".\"temporary\" = 1 THEN 'SESSION VIEW' " +
1959 "END AS \"TABLE_TYPE\", ");
1960 } else {
1961 query.append("\"table_types\".\"table_type_name\" AS \"TABLE_TYPE\", ");
1962 }
1963 query.append("\"tables\".\"query\" AS \"REMARKS\", " +
1964 "cast(null as char(1)) AS \"TYPE_CAT\", " +
1965 "cast(null as char(1)) AS \"TYPE_SCHEM\", " +
1966 "cast(null as char(1)) AS \"TYPE_NAME\", " +
1967 "cast(null as char(1)) AS \"SELF_REFERENCING_COL_NAME\", " +
1968 "cast(null as char(1)) AS \"REF_GENERATION\" " +
1969 "FROM \"sys\".\"tables\", \"sys\".\"schemas\"");
1970 if (!preJul2015) {
1971 query.append(", \"sys\".\"table_types\"");
1972 }
1973 query.append(" WHERE \"tables\".\"schema_id\" = \"schemas\".\"id\"");
1974 if (!preJul2015) {
1975 query.append(" AND \"tables\".\"type\" = \"table_types\".\"table_type_id\"");
1976 }
1977
1978 if (catalog != null && catalog.length() > 0) {
1979 // none empty catalog selection.
1980 // as we do not support catalogs this always results in no rows returned
1981 query.append(" AND 1 = 0");
1982 }
1983 if (schemaPattern != null) {
1984 query.append(" AND \"schemas\".\"name\" ").append(composeMatchPart(schemaPattern));
1985 }
1986 if (tableNamePattern != null) {
1987 query.append(" AND \"tables\".\"name\" ").append(composeMatchPart(tableNamePattern));
1988 }
1989 if (types != null && types.length > 0) {
1990 if (preJul2015) {
1991 query.append(") AS \"getTables\" WHERE \"TABLE_TYPE\" IN (");
1992 } else {
1993 query.append(" AND \"table_types\".\"table_type_name\" IN (");
1994 }
1995 for (int i = 0; i < types.length; i++) {
1996 if (i > 0) {
1997 query.append(", ");
1998 }
1999 query.append("'").append(types[i]).append("'");
2000 }
2001 query.append(")");
2002 }
2003
2004 query.append(" ORDER BY \"TABLE_TYPE\", \"TABLE_SCHEM\", \"TABLE_NAME\"");
2005
2006 return executeMetaDataQuery(query.toString());
2007 }
2008
2009 /**
2010 * Get the schema names available in this database. The results
2011 * are ordered by schema name.
2012 *
2013 * <P>The schema column is:
2014 * <OL>
2015 * <LI><B>TABLE_SCHEM</B> String => schema name
2016 * <LI><B>TABLE_CATALOG</B> String => catalog name (may be null)
2017 * </OL>
2018 *
2019 * @param catalog a catalog name; must match the catalog name as it
2020 * is stored in the database;"" retrieves those without a
2021 * catalog; null means catalog name should not be used to
2022 * narrow down the search.
2023 * @param schemaPattern a schema name; must match the schema name as
2024 * it is stored in the database; null means schema name
2025 * should not be used to narrow down the search.
2026 * @return ResultSet each row has a single String column that is a
2027 * schema name
2028 * @throws SQLException if a database error occurs
2029 */
2030 @Override
2031 public ResultSet getSchemas(String catalog, String schemaPattern)
2032 throws SQLException
2033 {
2034 StringBuilder query = new StringBuilder(170);
2035 query.append("SELECT \"name\" AS \"TABLE_SCHEM\", " +
2036 "cast(null as char(1)) AS \"TABLE_CATALOG\" " +
2037 "FROM \"sys\".\"schemas\"");
2038
2039 if (catalog != null && catalog.length() > 0) {
2040 // none empty catalog selection.
2041 // as we do not support catalogs this always results in no rows returned
2042 query.append(" WHERE 1 = 0");
2043 } else {
2044 if (schemaPattern != null) {
2045 query.append(" WHERE \"name\" ").append(composeMatchPart(schemaPattern));
2046 }
2047 }
2048 query.append(" ORDER BY \"TABLE_SCHEM\"");
2049
2050 return executeMetaDataQuery(query.toString());
2051 }
2052
2053 /**
2054 * Get the catalog names available in this database. The results
2055 * are ordered by catalog name.
2056 *
2057 * <P>The catalog column is:
2058 * <OL>
2059 * <LI><B>TABLE_CAT</B> String => catalog name
2060 * </OL>
2061 *
2062 *
2063 * @return ResultSet each row has a single String column that is a
2064 * catalog name
2065 * @throws SQLException if a database error occurs
2066 */
2067 @Override
2068 public ResultSet getCatalogs() throws SQLException {
2069 // MonetDB does NOT support catalogs.
2070 // Return a resultset with no rows
2071 return executeMetaDataQuery("SELECT cast(null as char(1)) AS \"TABLE_CAT\" WHERE 1 = 0");
2072 }
2073
2074 /**
2075 * Get the table types available in this database. The results
2076 * are ordered by table type.
2077 *
2078 * <P>The table type is:
2079 * <OL>
2080 * <LI><B>TABLE_TYPE</B> String => table type. Typical types are "TABLE",
2081 * "VIEW", "SYSTEM TABLE", "GLOBAL TEMPORARY",
2082 * "LOCAL TEMPORARY", "ALIAS", "SYNONYM".
2083 * </OL>
2084 *
2085 * @return ResultSet each row has a single String column that is a
2086 * table type
2087 * @throws SQLException if a database error occurs
2088 */
2089 @Override
2090 public ResultSet getTableTypes() throws SQLException {
2091 // as of Jul2015 release we have a new table: sys.table_types with more table types
2092 String query = "SELECT \"table_type_name\" AS \"TABLE_TYPE\" FROM \"sys\".\"table_types\" ORDER BY 1";
2093 // For old (pre jul2015) servers fall back to old behavior.
2094 boolean preJul2015 = ("11.19.15".compareTo(getDatabaseProductVersion()) >= 0);
2095 if (preJul2015) {
2096 query = "SELECT 'SESSION TABLE' AS \"TABLE_TYPE\" UNION ALL " +
2097 "SELECT 'SESSION VIEW' UNION ALL " +
2098 "SELECT 'SYSTEM SESSION TABLE' UNION ALL " +
2099 "SELECT 'SYSTEM SESSION VIEW' UNION ALL " +
2100 "SELECT 'SYSTEM TABLE' UNION ALL " +
2101 "SELECT 'SYSTEM VIEW' UNION ALL " +
2102 "SELECT 'TABLE' UNION ALL " +
2103 "SELECT 'VIEW' ORDER BY 1";
2104 }
2105
2106 return executeMetaDataQuery(query);
2107 }
2108
2109 /**
2110 * Get a description of table columns available in a catalog.
2111 *
2112 * <P>Only column descriptions matching the catalog, schema, table
2113 * and column name criteria are returned. They are ordered by
2114 * TABLE_SCHEM, TABLE_NAME and ORDINAL_POSITION.
2115 *
2116 * <P>Each column description has the following columns:
2117 * <OL>
2118 * <LI><B>TABLE_CAT</B> String => table catalog (may be null)
2119 * <LI><B>TABLE_SCHEM</B> String => table schema (may be null)
2120 * <LI><B>TABLE_NAME</B> String => table name
2121 * <LI><B>COLUMN_NAME</B> String => column name
2122 * <LI><B>DATA_TYPE</B> int => SQL type from java.sql.Types
2123 * <LI><B>TYPE_NAME</B> String => Data source dependent type name
2124 * <LI><B>COLUMN_SIZE</B> int => column size. For char or date
2125 * types this is the maximum number of characters, for numeric or
2126 * decimal types this is precision.
2127 * <LI><B>BUFFER_LENGTH</B> is not used.
2128 * <LI><B>DECIMAL_DIGITS</B> int => the number of fractional digits
2129 * <LI><B>NUM_PREC_RADIX</B> int => Radix (typically either 10 or 2)
2130 * <LI><B>NULLABLE</B> int => is NULL allowed?
2131 * <UL>
2132 * <LI> columnNoNulls - might not allow NULL values
2133 * <LI> columnNullable - definitely allows NULL values
2134 * <LI> columnNullableUnknown - nullability unknown
2135 * </UL>
2136 * <LI><B>REMARKS</B> String => comment describing column (may be null)
2137 * <LI><B>COLUMN_DEF</B> String => default value (may be null)
2138 * <LI><B>SQL_DATA_TYPE</B> int => unused
2139 * <LI><B>SQL_DATETIME_SUB</B> int => unused
2140 * <LI><B>CHAR_OCTET_LENGTH</B> int => for char types the
2141 * maximum number of bytes in the column
2142 * <LI><B>ORDINAL_POSITION</B> int => index of column in table
2143 * (starting at 1)
2144 * <LI><B>IS_NULLABLE</B> String => "NO" means column definitely
2145 * does not allow NULL values; "YES" means the column might
2146 * allow NULL values. An empty string means nobody knows.
2147 * <LI><B>SCOPE_CATALOG</B> String => catalog of table that is the scope of a reference attribute (null if DATA_TYPE isn't REF)
2148 * <LI><B>SCOPE_SCHEMA</B> String => schema of table that is the scope of a reference attribute (null if the DATA_TYPE isn't REF)
2149 * <LI><B>SCOPE_TABLE</B> String => table name that this the scope of a reference attribute (null if the DATA_TYPE isn't REF)
2150 * <LI><B>SOURCE_DATA_TYPE</B> short => source type of a distinct type or user-generated Ref type, SQL type from java.sql.Types (null if DATA_TYPE isn't DISTINCT or user-generated REF)
2151 * <LI><B>IS_AUTOINCREMENT</B> String => Indicates whether this column is auto incremented
2152 * <UL>
2153 * <LI> YES --- if the column is auto incremented
2154 * <LI> NO --- if the column is not auto incremented
2155 * <LI> empty string --- if it cannot be determined whether the column is auto incremented
2156 * </UL>
2157 * <LI><B>IS_GENERATEDCOLUMN</B> String => Indicates whether this is a generated column
2158 * <UL>
2159 * <LI> YES --- if this a generated column
2160 * <LI> NO --- if this not a generated column
2161 * <LI> empty string --- if it cannot be determined whether this is a generated column
2162 * </UL>
2163 * </OL>
2164 *
2165 * @param catalog - a catalog name; must match the catalog name as it is stored in the database;
2166 * "" retrieves those without a catalog; null means that the
2167 * catalog name should not be used to narrow the search
2168 * @param schemaPattern - a schema name pattern; must match the schema name as it is stored
2169 * in the database; "" retrieves those without a schema;
2170 * null means that the schema name should not be used to narrow the search
2171 * @param tableNamePattern - a table name pattern; must match the table name as it is stored in the database
2172 * For all tables this should be "%"
2173 * @param columnNamePattern - a column name pattern; must match the column name as it is stored in the database
2174 * @return ResultSet - each row is a column description
2175 * @throws SQLException if a database error occurs
2176 * @see #getSearchStringEscape
2177 */
2178 @Override
2179 public ResultSet getColumns(
2180 String catalog,
2181 String schemaPattern,
2182 String tableNamePattern,
2183 String columnNamePattern
2184 ) throws SQLException
2185 {
2186 StringBuilder query = new StringBuilder(2450);
2187 query.append("SELECT cast(null as char(1)) AS \"TABLE_CAT\", " +
2188 "\"schemas\".\"name\" AS \"TABLE_SCHEM\", " +
2189 "\"tables\".\"name\" AS \"TABLE_NAME\", " +
2190 "\"columns\".\"name\" AS \"COLUMN_NAME\", " +
2191 "cast(").append(MonetDriver.getSQLTypeMap("\"columns\".\"type\"")).append(" AS int) AS \"DATA_TYPE\", " +
2192 "\"columns\".\"type\" AS \"TYPE_NAME\", " +
2193 "\"columns\".\"type_digits\" AS \"COLUMN_SIZE\", " +
2194 "0 AS \"BUFFER_LENGTH\", " +
2195 "\"columns\".\"type_scale\" AS \"DECIMAL_DIGITS\", " +
2196 "cast(CASE WHEN \"columns\".\"type\" IN ('decimal', 'numeric', 'sec_interval') THEN 10 " +
2197 "WHEN \"columns\".\"type\" IN ('int', 'smallint', 'tinyint', 'bigint', 'hugeint', 'float', 'real', 'double', 'oid', 'wrd') THEN 2 " +
2198 "ELSE 0 END AS int) AS \"NUM_PREC_RADIX\", " +
2199 "cast(CASE \"null\" WHEN true THEN ").append(ResultSetMetaData.columnNullable)
2200 .append(" WHEN false THEN ").append(ResultSetMetaData.columnNoNulls).append(" END AS int) AS \"NULLABLE\", " +
2201 "cast(null AS varchar(1)) AS \"REMARKS\", " +
2202 "\"columns\".\"default\" AS \"COLUMN_DEF\", " +
2203 "cast(0 as int) AS \"SQL_DATA_TYPE\", " +
2204 "cast(0 as int) AS \"SQL_DATETIME_SUB\", " +
2205 "cast(CASE WHEN \"columns\".\"type\" IN ('char','varchar','clob') THEN \"columns\".\"type_digits\" ELSE NULL END as int) AS \"CHAR_OCTET_LENGTH\", " +
2206 "cast(\"columns\".\"number\" + 1 as int) AS \"ORDINAL_POSITION\", " +
2207 "cast(CASE \"null\" WHEN true THEN 'YES' WHEN false THEN 'NO' ELSE '' END AS varchar(3)) AS \"IS_NULLABLE\", " +
2208 "cast(null AS varchar(1)) AS \"SCOPE_CATALOG\", " +
2209 "cast(null AS varchar(1)) AS \"SCOPE_SCHEMA\", " +
2210 "cast(null AS varchar(1)) AS \"SCOPE_TABLE\", " +
2211 "cast(").append(MonetDriver.getJavaType("other")).append(" AS smallint) AS \"SOURCE_DATA_TYPE\", " +
2212 "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\", " +
2213 "cast('NO' AS varchar(3)) AS \"IS_GENERATEDCOLUMN\" " +
2214 "FROM \"sys\".\"columns\", " +
2215 "\"sys\".\"tables\", " +
2216 "\"sys\".\"schemas\" " +
2217 "WHERE \"columns\".\"table_id\" = \"tables\".\"id\" " +
2218 "AND \"tables\".\"schema_id\" = \"schemas\".\"id\"");
2219
2220 if (catalog != null && catalog.length() > 0) {
2221 // none empty catalog selection.
2222 // as we do not support catalogs this always results in no rows returned
2223 query.append(" AND 1 = 0");
2224 }
2225 if (schemaPattern != null) {
2226 query.append(" AND \"schemas\".\"name\" ").append(composeMatchPart(schemaPattern));
2227 }
2228 if (tableNamePattern != null) {
2229 query.append(" AND \"tables\".\"name\" ").append(composeMatchPart(tableNamePattern));
2230 }
2231 if (columnNamePattern != null) {
2232 query.append(" AND \"columns\".\"name\" ").append(composeMatchPart(columnNamePattern));
2233 }
2234
2235 query.append(" ORDER BY \"TABLE_SCHEM\", \"TABLE_NAME\", \"ORDINAL_POSITION\"");
2236
2237 return executeMetaDataQuery(query.toString());
2238 }
2239
2240 /**
2241 * Get a description of the access rights for a table's columns.
2242 * MonetDB doesn't have this level of access rights.
2243 *
2244 * <P>Only privileges matching the column name criteria are
2245 * returned. They are ordered by COLUMN_NAME and PRIVILEGE.
2246 *
2247 * <P>Each privilige description has the following columns:
2248 * <OL>
2249 * <LI><B>TABLE_CAT</B> String => table catalog (may be null)
2250 * <LI><B>TABLE_SCHEM</B> String => table schema (may be null)
2251 * <LI><B>TABLE_NAME</B> String => table name
2252 * <LI><B>COLUMN_NAME</B> String => column name
2253 * <LI><B>GRANTOR</B> => grantor of access (may be null)
2254 * <LI><B>GRANTEE</B> String => grantee of access
2255 * <LI><B>PRIVILEGE</B> String => name of access (SELECT,
2256 * INSERT, UPDATE, REFRENCES, ...)
2257 * <LI><B>IS_GRANTABLE</B> String => "YES" if grantee is permitted
2258 * to grant to others; "NO" if not; null if unknown
2259 * </OL>
2260 *
2261 * @param catalog a catalog name; "" retrieves those without a catalog
2262 * @param schemaPattern a schema name; "" retrieves those without a schema
2263 * @param tableNamePattern a table name
2264 * @param columnNamePattern a column name pattern
2265 * @return ResultSet each row is a column privilege description
2266 * @see #getSearchStringEscape
2267 * @throws SQLException if a database error occurs
2268 */
2269 @Override
2270 public ResultSet getColumnPrivileges(
2271 String catalog,
2272 String schemaPattern,
2273 String tableNamePattern,
2274 String columnNamePattern
2275 ) throws SQLException
2276 {
2277 StringBuilder query = new StringBuilder(1100);
2278 query.append("SELECT cast(null as char(1)) AS \"TABLE_CAT\", " +
2279 "\"schemas\".\"name\" AS \"TABLE_SCHEM\", " +
2280 "\"tables\".\"name\" AS \"TABLE_NAME\", " +
2281 "\"columns\".\"name\" AS \"COLUMN_NAME\", " +
2282 "\"grantors\".\"name\" AS \"GRANTOR\", " +
2283 "\"grantees\".\"name\" AS \"GRANTEE\", " +
2284 "CAST(CASE \"privileges\".\"privileges\" " +
2285 "WHEN 1 THEN 'SELECT' " +
2286 "WHEN 2 THEN 'UPDATE' " +
2287 "WHEN 4 THEN 'INSERT' " +
2288 "WHEN 8 THEN 'DELETE' " +
2289 "WHEN 16 THEN 'EXECUTE' " +
2290 "WHEN 32 THEN 'GRANT' " +
2291 "ELSE NULL " +
2292 "END AS varchar(7)) AS \"PRIVILEGE\", " +
2293 "CAST(CASE \"privileges\".\"grantable\" " +
2294 "WHEN 0 THEN 'NO' " +
2295 "WHEN 1 THEN 'YES' " +
2296 "ELSE NULL " +
2297 "END AS varchar(3)) AS \"IS_GRANTABLE\" " +
2298 "FROM \"sys\".\"privileges\", " +
2299 "\"sys\".\"tables\", " +
2300 "\"sys\".\"schemas\", " +
2301 "\"sys\".\"columns\", " +
2302 "\"sys\".\"auths\" AS \"grantors\", " +
2303 "\"sys\".\"auths\" AS \"grantees\" " +
2304 "WHERE \"privileges\".\"obj_id\" = \"columns\".\"id\" " +
2305 "AND \"columns\".\"table_id\" = \"tables\".\"id\" " +
2306 "AND \"tables\".\"schema_id\" = \"schemas\".\"id\" " +
2307 "AND \"privileges\".\"auth_id\" = \"grantees\".\"id\" " +
2308 "AND \"privileges\".\"grantor\" = \"grantors\".\"id\"");
2309
2310 if (catalog != null && catalog.length() > 0) {
2311 // none empty catalog selection.
2312 // as we do not support catalogs this always results in no rows returned
2313 query.append(" AND 1 = 0");
2314 }
2315 if (schemaPattern != null) {
2316 query.append(" AND \"schemas\".\"name\" ").append(composeMatchPart(schemaPattern));
2317 }
2318 if (tableNamePattern != null) {
2319 query.append(" AND \"tables\".\"name\" ").append(composeMatchPart(tableNamePattern));
2320 }
2321 if (columnNamePattern != null) {
2322 query.append(" AND \"columns\".\"name\" ").append(composeMatchPart(columnNamePattern));
2323 }
2324
2325 query.append(" ORDER BY \"TABLE_SCHEM\", \"TABLE_NAME\", \"COLUMN_NAME\", \"PRIVILEGE\"");
2326
2327 return executeMetaDataQuery(query.toString());
2328 }
2329
2330 /**
2331 * Get a description of the access rights for each table available
2332 * in a catalog.
2333 *
2334 * <P>Only privileges matching the schema and table name
2335 * criteria are returned. They are ordered by TABLE_SCHEM,
2336 * TABLE_NAME, and PRIVILEGE.
2337 *
2338 * <P>Each privilege description has the following columns:
2339 * <OL>
2340 * <LI><B>TABLE_CAT</B> String => table catalog (may be null)
2341 * <LI><B>TABLE_SCHEM</B> String => table schema (may be null)
2342 * <LI><B>TABLE_NAME</B> String => table name
2343 * <LI><B>GRANTOR</B> => grantor of access (may be null)
2344 * <LI><B>GRANTEE</B> String => grantee of access
2345 * <LI><B>PRIVILEGE</B> String => name of access (SELECT,
2346 * INSERT, UPDATE, REFRENCES, ...)
2347 * <LI><B>IS_GRANTABLE</B> String => "YES" if grantee is permitted
2348 * to grant to others; "NO" if not; null if unknown
2349 * </OL>
2350 *
2351 * @param catalog a catalog name; "" retrieves those without a catalog
2352 * @param schemaPattern a schema name pattern; "" retrieves those without a schema
2353 * @param tableNamePattern a table name pattern
2354 * @return ResultSet each row is a table privilege description
2355 * @see #getSearchStringEscape
2356 * @throws SQLException if a database error occurs
2357 */
2358 @Override
2359 public ResultSet getTablePrivileges(
2360 String catalog,
2361 String schemaPattern,
2362 String tableNamePattern
2363 ) throws SQLException
2364 {
2365 StringBuilder query = new StringBuilder(1000);
2366 query.append("SELECT cast(null as char(1)) AS \"TABLE_CAT\", " +
2367 "\"schemas\".\"name\" AS \"TABLE_SCHEM\", " +
2368 "\"tables\".\"name\" AS \"TABLE_NAME\", " +
2369 "\"grantors\".\"name\" AS \"GRANTOR\", " +
2370 "\"grantees\".\"name\" AS \"GRANTEE\", " +
2371 "CAST(CASE \"privileges\".\"privileges\" " +
2372 "WHEN 1 THEN 'SELECT' " +
2373 "WHEN 2 THEN 'UPDATE' " +
2374 "WHEN 4 THEN 'INSERT' " +
2375 "WHEN 8 THEN 'DELETE' " +
2376 "WHEN 16 THEN 'EXECUTE' " +
2377 "WHEN 32 THEN 'GRANT' " +
2378 "ELSE NULL " +
2379 "END AS varchar(7)) AS \"PRIVILEGE\", " +
2380 "CAST(CASE \"privileges\".\"grantable\" " +
2381 "WHEN 0 THEN 'NO' " +
2382 "WHEN 1 THEN 'YES' " +
2383 "ELSE NULL " +
2384 "END AS varchar(3)) AS \"IS_GRANTABLE\" " +
2385 "FROM \"sys\".\"privileges\", " +
2386 "\"sys\".\"tables\", " +
2387 "\"sys\".\"schemas\", " +
2388 "\"sys\".\"auths\" AS \"grantors\", " +
2389 "\"sys\".\"auths\" AS \"grantees\" " +
2390 "WHERE \"privileges\".\"obj_id\" = \"tables\".\"id\" " +
2391 "AND \"tables\".\"schema_id\" = \"schemas\".\"id\" " +
2392 "AND \"privileges\".\"auth_id\" = \"grantees\".\"id\" " +
2393 "AND \"privileges\".\"grantor\" = \"grantors\".\"id\"");
2394
2395 if (catalog != null && catalog.length() > 0) {
2396 // none empty catalog selection.
2397 // as we do not support catalogs this always results in no rows returned
2398 query.append(" AND 1 = 0");
2399 }
2400 if (schemaPattern != null) {
2401 query.append(" AND \"schemas\".\"name\" ").append(composeMatchPart(schemaPattern));
2402 }
2403 if (tableNamePattern != null) {
2404 query.append(" AND \"tables\".\"name\" ").append(composeMatchPart(tableNamePattern));
2405 }
2406
2407 query.append(" ORDER BY \"TABLE_SCHEM\", \"TABLE_NAME\", \"PRIVILEGE\"");
2408
2409 return executeMetaDataQuery(query.toString());
2410 }
2411
2412 /**
2413 * Get a description of a table's optimal set of columns that
2414 * uniquely identifies a row. They are ordered by SCOPE.
2415 *
2416 * <P>Each column description has the following columns:
2417 * <OL>
2418 * <LI><B>SCOPE</B> short => actual scope of result
2419 * <UL>
2420 * <LI> bestRowTemporary - very temporary, while using row
2421 * <LI> bestRowTransaction - valid for remainder of current transaction
2422 * <LI> bestRowSession - valid for remainder of current session
2423 * </UL>
2424 * <LI><B>COLUMN_NAME</B> String => column name
2425 * <LI><B>DATA_TYPE</B> int => SQL data type from java.sql.Types
2426 * <LI><B>TYPE_NAME</B> String => Data source dependent type name
2427 * <LI><B>COLUMN_SIZE</B> int => precision
2428 * <LI><B>BUFFER_LENGTH</B> int => not used
2429 * <LI><B>DECIMAL_DIGITS</B> short => scale
2430 * <LI><B>PSEUDO_COLUMN</B> short => is this a pseudo column
2431 * like an Oracle ROWID
2432 * <UL>
2433 * <LI> bestRowUnknown - may or may not be pseudo column
2434 * <LI> bestRowNotPseudo - is NOT a pseudo column
2435 * <LI> bestRowPseudo - is a pseudo column
2436 * </UL>
2437 * </OL>
2438 *
2439 * @param catalog a catalog name; "" retrieves those without a catalog
2440 * @param schema a schema name; "" retrieves those without a schema
2441 * @param table a table name
2442 * @param scope the scope of interest; use same values as SCOPE
2443 * @param nullable include columns that are nullable?
2444 * @return ResultSet each row is a column description
2445 * @throws SQLException if a database error occurs
2446 */
2447 @Override
2448 public ResultSet getBestRowIdentifier(
2449 String catalog,
2450 String schema,
2451 String table,
2452 int scope,
2453 boolean nullable
2454 ) throws SQLException
2455 {
2456 StringBuilder query = new StringBuilder(1500);
2457 query.append("SELECT CAST(").append(DatabaseMetaData.bestRowSession).append(" AS smallint) AS \"SCOPE\", " +
2458 "\"columns\".\"name\" AS \"COLUMN_NAME\", " +
2459 "cast(").append(MonetDriver.getSQLTypeMap("\"columns\".\"type\"")).append(" AS int) AS \"DATA_TYPE\", " +
2460 "\"columns\".\"type\" AS \"TYPE_NAME\", " +
2461 "\"columns\".\"type_digits\" AS \"COLUMN_SIZE\", " +
2462 "CAST(0 as int) AS \"BUFFER_LENGTH\", " +
2463 "CAST(\"columns\".\"type_scale\" AS smallint) AS \"DECIMAL_DIGITS\", " +
2464 "CAST(").append(DatabaseMetaData.bestRowNotPseudo).append(" AS smallint) AS \"PSEUDO_COLUMN\" " +
2465 "FROM \"sys\".\"keys\", " +
2466 "\"sys\".\"objects\", " +
2467 "\"sys\".\"columns\", " +
2468 "\"sys\".\"tables\", " +
2469 "\"sys\".\"schemas\" " +
2470 "WHERE \"keys\".\"id\" = \"objects\".\"id\" " +
2471 "AND \"keys\".\"table_id\" = \"tables\".\"id\" " +
2472 "AND \"keys\".\"table_id\" = \"columns\".\"table_id\" " +
2473 "AND \"objects\".\"name\" = \"columns\".\"name\" " +
2474 "AND \"tables\".\"schema_id\" = \"schemas\".\"id\" " +
2475 "AND \"keys\".\"type\" IN (0, 1)"); // only primary keys (type = 0) and unique keys (type = 1), not fkeys (type = 2)
2476
2477 if (catalog != null && catalog.length() > 0) {
2478 // none empty catalog selection.
2479 // as we do not support catalogs this always results in no rows returned
2480 query.append(" AND 1 = 0");
2481 }
2482 if (schema != null) {
2483 query.append(" AND \"schemas\".\"name\" ").append(composeMatchPart(schema));
2484 }
2485 if (table != null) {
2486 query.append(" AND \"tables\".\"name\" ").append(composeMatchPart(table));
2487 }
2488 if (scope != DatabaseMetaData.bestRowSession && scope != DatabaseMetaData.bestRowTransaction && scope != DatabaseMetaData.bestRowTemporary) {
2489 query.append(" AND 1 = 0");
2490 }
2491 if (!nullable) {
2492 query.append(" AND \"columns\".\"null\" = false");
2493 }
2494
2495 query.append(" ORDER BY \"keys\".\"type\", \"columns\".\"name\"");
2496
2497 return executeMetaDataQuery(query.toString());
2498 }
2499
2500 /**
2501 * Get a description of a table's columns that are automatically
2502 * updated when any value in a row is updated. They are unordered.
2503 *
2504 * <P>Each column description has the following columns:
2505 * <OL>
2506 * <LI><B>SCOPE</B> short => is not used
2507 * <LI><B>COLUMN_NAME</B> String => column name
2508 * <LI><B>DATA_TYPE</B> int => SQL data type from java.sql.Types
2509 * <LI><B>TYPE_NAME</B> String => Data source dependent type name
2510 * <LI><B>COLUMN_SIZE</B> int => precision
2511 * <LI><B>BUFFER_LENGTH</B> int => length of column value in bytes
2512 * <LI><B>DECIMAL_DIGITS</B> short => scale
2513 * <LI><B>PSEUDO_COLUMN</B> short => is this a pseudo column like an Oracle ROWID
2514 * <UL>
2515 * <LI> versionColumnUnknown - may or may not be pseudo column
2516 * <LI> versionColumnNotPseudo - is NOT a pseudo column
2517 * <LI> versionColumnPseudo - is a pseudo column
2518 * </UL>
2519 * </OL>
2520 *
2521 * @param catalog a catalog name; "" retrieves those without a catalog
2522 * @param schema a schema name; "" retrieves those without a schema
2523 * @param table a table name
2524 * @return ResultSet each row is a column description
2525 * @throws SQLException if a database error occurs
2526 */
2527 @Override
2528 public ResultSet getVersionColumns(
2529 String catalog,
2530 String schema,
2531 String table
2532 ) throws SQLException
2533 {
2534 // MonetDB currently does not have columns which update themselves, so return an empty ResultSet
2535 String query =
2536 "SELECT CAST(0 as smallint) AS \"SCOPE\", " +
2537 "CAST(null as varchar(1)) AS \"COLUMN_NAME\", " +
2538 "CAST(0 as int) AS \"DATA_TYPE\", " +
2539 "CAST(null as varchar(1)) AS \"TYPE_NAME\", " +
2540 "CAST(0 as int) AS \"COLUMN_SIZE\", " +
2541 "CAST(0 as int) AS \"BUFFER_LENGTH\", " +
2542 "CAST(0 as smallint) AS \"DECIMAL_DIGITS\", " +
2543 "CAST(0 as smallint) AS \"PSEUDO_COLUMN\" " +
2544 "WHERE 1 = 0";
2545
2546 return executeMetaDataQuery(query);
2547 }
2548
2549 /**
2550 * Get a description of a table's primary key columns. They
2551 * are ordered by COLUMN_NAME.
2552 *
2553 * <P>Each column description has the following columns:
2554 * <OL>
2555 * <LI><B>TABLE_CAT</B> String => table catalog (may be null)
2556 * <LI><B>TABLE_SCHEM</B> String => table schema (may be null)
2557 * <LI><B>TABLE_NAME</B> String => table name
2558 * <LI><B>COLUMN_NAME</B> String => column name
2559 * <LI><B>KEY_SEQ</B> short => sequence number within primary key
2560 * <LI><B>PK_NAME</B> String => primary key name (may be null)
2561 * </OL>
2562 *
2563 * @param catalog a catalog name; "" retrieves those without a catalog
2564 * @param schema a schema name pattern; "" retrieves those
2565 * without a schema
2566 * @param table a table name
2567 * @return ResultSet each row is a primary key column description
2568 * @throws SQLException if a database error occurs
2569 */
2570 @Override
2571 public ResultSet getPrimaryKeys(
2572 String catalog,
2573 String schema,
2574 String table
2575 ) throws SQLException
2576 {
2577 StringBuilder query = new StringBuilder(600);
2578 query.append("SELECT cast(null AS varchar(1)) AS \"TABLE_CAT\", " +
2579 "\"schemas\".\"name\" AS \"TABLE_SCHEM\", " +
2580 "\"tables\".\"name\" AS \"TABLE_NAME\", " +
2581 "\"objects\".\"name\" AS \"COLUMN_NAME\", " +
2582 "CAST(1 + \"objects\".\"nr\" AS smallint) AS \"KEY_SEQ\", " +
2583 " \"keys\".\"name\" AS \"PK_NAME\" " +
2584 "FROM \"sys\".\"keys\", " +
2585 "\"sys\".\"objects\", " +
2586 "\"sys\".\"tables\", " +
2587 "\"sys\".\"schemas\" " +
2588 "WHERE \"keys\".\"id\" = \"objects\".\"id\" " +
2589 "AND \"keys\".\"table_id\" = \"tables\".\"id\" " +
2590 "AND \"tables\".\"schema_id\" = \"schemas\".\"id\" " +
2591 "AND \"keys\".\"type\" = 0");
2592
2593 if (catalog != null && catalog.length() > 0) {
2594 // none empty catalog selection.
2595 // as we do not support catalogs this always results in no rows returned
2596 query.append(" AND 1 = 0");
2597 }
2598 if (schema != null) {
2599 query.append(" AND \"schemas\".\"name\" ").append(composeMatchPart(schema));
2600 }
2601 if (table != null) {
2602 query.append(" AND \"tables\".\"name\" ").append(composeMatchPart(table));
2603 }
2604
2605 query.append(" ORDER BY \"TABLE_SCHEM\", \"TABLE_NAME\", \"COLUMN_NAME\"");
2606
2607 return executeMetaDataQuery(query.toString());
2608 }
2609
2610
2611 private final static String keyQuery =
2612 "SELECT cast(null AS varchar(1)) AS \"PKTABLE_CAT\", " +
2613 "\"pkschema\".\"name\" AS \"PKTABLE_SCHEM\", " +
2614 "\"pktable\".\"name\" AS \"PKTABLE_NAME\", " +
2615 "\"pkkeycol\".\"name\" AS \"PKCOLUMN_NAME\", " +
2616 "cast(null AS varchar(1)) AS \"FKTABLE_CAT\", " +
2617 "\"fkschema\".\"name\" AS \"FKTABLE_SCHEM\", " +
2618 "\"fktable\".\"name\" AS \"FKTABLE_NAME\", " +
2619 "\"fkkeycol\".\"name\" AS \"FKCOLUMN_NAME\", " +
2620 "CAST(1 + \"pkkeycol\".\"nr\" AS smallint) AS \"KEY_SEQ\", " +
2621 DatabaseMetaData.importedKeyNoAction + " AS \"UPDATE_RULE\", " +
2622 DatabaseMetaData.importedKeyNoAction + " AS \"DELETE_RULE\", " +
2623 "\"fkkey\".\"name\" AS \"FK_NAME\", " +
2624 "\"pkkey\".\"name\" AS \"PK_NAME\", " +
2625 DatabaseMetaData.importedKeyNotDeferrable + " AS \"DEFERRABILITY\" " +
2626 "FROM \"sys\".\"keys\" AS \"fkkey\", " +
2627 "\"sys\".\"keys\" AS \"pkkey\", " +
2628 "\"sys\".\"objects\" AS \"fkkeycol\", " +
2629 "\"sys\".\"objects\" AS \"pkkeycol\", " +
2630 "\"sys\".\"tables\" AS \"fktable\", " +
2631 "\"sys\".\"tables\" AS \"pktable\", " +
2632 "\"sys\".\"schemas\" AS \"fkschema\", " +
2633 "\"sys\".\"schemas\" AS \"pkschema\" " +
2634 "WHERE \"fktable\".\"id\" = \"fkkey\".\"table_id\"" +
2635 " AND \"pktable\".\"id\" = \"pkkey\".\"table_id\"" +
2636 " AND \"fkkey\".\"id\" = \"fkkeycol\".\"id\"" +
2637 " AND \"pkkey\".\"id\" = \"pkkeycol\".\"id\"" +
2638 " AND \"fkschema\".\"id\" = \"fktable\".\"schema_id\"" +
2639 " AND \"pkschema\".\"id\" = \"pktable\".\"schema_id\"" +
2640 " AND \"fkkey\".\"rkey\" > -1" +
2641 " AND \"fkkey\".\"rkey\" = \"pkkey\".\"id\"" +
2642 " AND \"fkkeycol\".\"nr\" = \"pkkeycol\".\"nr\"";
2643
2644 /**
2645 * Get a description of the primary key columns that are
2646 * referenced by a table's foreign key columns (the primary keys
2647 * imported by a table). They are ordered by PKTABLE_CAT,
2648 * PKTABLE_SCHEM, PKTABLE_NAME, and KEY_SEQ.
2649 *
2650 * <P>Each primary key column description has the following columns:
2651 * <OL>
2652 * <LI><B>PKTABLE_CAT</B> String => primary key table catalog
2653 * being imported (may be null)
2654 * <LI><B>PKTABLE_SCHEM</B> String => primary key table schema
2655 * being imported (may be null)
2656 * <LI><B>PKTABLE_NAME</B> String => primary key table name
2657 * being imported
2658 * <LI><B>PKCOLUMN_NAME</B> String => primary key column name
2659 * being imported
2660 * <LI><B>FKTABLE_CAT</B> String => foreign key table catalog (may be null)
2661 * <LI><B>FKTABLE_SCHEM</B> String => foreign key table schema (may be null)
2662 * <LI><B>FKTABLE_NAME</B> String => foreign key table name
2663 * <LI><B>FKCOLUMN_NAME</B> String => foreign key column name
2664 * <LI><B>KEY_SEQ</B> short => sequence number within foreign key
2665 * (a value of 1 represents the first column of the foreign key, a value of 2 would represent the second column within the foreign key).
2666 * <LI><B>UPDATE_RULE</B> short => What happens to
2667 * foreign key when primary is updated:
2668 * <UL>
2669 * <LI> importedKeyNoAction - do not allow update of primary key if it has been imported
2670 * <LI> importedKeyCascade - change imported key to agree
2671 * with primary key update
2672 * <LI> importedKeyRestrict - do not allow update of primary
2673 * key if it has been imported
2674 * <LI> importedKeySetNull - change imported key to NULL if
2675 * its primary key has been updated
2676 * </UL>
2677 * <LI><B>DELETE_RULE</B> short => What happens to
2678 * the foreign key when primary is deleted.
2679 * <UL>
2680 * <LI> importedKeyNoAction - do not allow delete of primary key if it has been imported
2681 * <LI> importedKeyCascade - delete rows that import a deleted key
2682 * <LI> importedKeyRestrict - do not allow delete of primary
2683 * key if it has been imported
2684 * <LI> importedKeySetNull - change imported key to NULL if
2685 * its primary key has been deleted
2686 * </UL>
2687 * <LI><B>FK_NAME</B> String => foreign key name (may be null)
2688 * <LI><B>PK_NAME</B> String => primary key name (may be null)
2689 * <LI><B>DEFERRABILITY</B> short => can the evaluation of foreign key constraints be deferred until commit
2690 * <UL>
2691 * <LI> importedKeyInitiallyDeferred - see SQL92 for definition
2692 * <LI> importedKeyInitiallyImmediate - see SQL92 for definition
2693 * <LI> importedKeyNotDeferrable - see SQL92 for definition
2694 * </UL>
2695 * </OL>
2696 *
2697 * @param catalog a catalog name; "" retrieves those without a catalog
2698 * @param schema a schema name pattern; "" retrieves those without a schema
2699 * @param table a table name
2700 * @return ResultSet each row is a primary key column description
2701 * @see #getExportedKeys
2702 * @throws SQLException if a database error occurs
2703 */
2704 @Override
2705 public ResultSet getImportedKeys(String catalog, String schema, String table)
2706 throws SQLException
2707 {
2708 StringBuilder query = new StringBuilder(keyQuery.length() + 250);
2709 query.append(keyQuery);
2710
2711 if (catalog != null && catalog.length() > 0) {
2712 // none empty catalog selection.
2713 // as we do not support catalogs this always results in no rows returned
2714 query.append(" AND 1 = 0");
2715 }
2716 if (schema != null) {
2717 query.append(" AND \"fkschema\".\"name\" ").append(composeMatchPart(schema));
2718 }
2719 if (table != null) {
2720 query.append(" AND \"fktable\".\"name\" ").append(composeMatchPart(table));
2721 }
2722
2723 query.append(" ORDER BY \"PKTABLE_SCHEM\", \"PKTABLE_NAME\", \"PK_NAME\", \"KEY_SEQ\"");
2724
2725 return executeMetaDataQuery(query.toString());
2726 }
2727
2728 /**
2729 * Get a description of a foreign key columns that reference a
2730 * table's primary key columns (the foreign keys exported by a table).
2731 * They are ordered by FKTABLE_CAT, FKTABLE_SCHEM, FKTABLE_NAME, and KEY_SEQ.
2732 *
2733 * <P>Each foreign key column description has the following columns:
2734 * <OL>
2735 * <LI><B>PKTABLE_CAT</B> String => primary key table catalog (may be null)
2736 * <LI><B>PKTABLE_SCHEM</B> String => primary key table schema (may be null)
2737 * <LI><B>PKTABLE_NAME</B> String => primary key table name
2738 * <LI><B>PKCOLUMN_NAME</B> String => primary key column name
2739 * <LI><B>FKTABLE_CAT</B> String => foreign key table catalog (may be null)
2740 * being exported (may be null)
2741 * <LI><B>FKTABLE_SCHEM</B> String => foreign key table schema (may be null)
2742 * being exported (may be null)
2743 * <LI><B>FKTABLE_NAME</B> String => foreign key table name
2744 * being exported
2745 * <LI><B>FKCOLUMN_NAME</B> String => foreign key column name
2746 * being exported
2747 * <LI><B>KEY_SEQ</B> short => sequence number within foreign key
2748 * (a value of 1 represents the first column of the foreign key, a value of 2 would represent the second column within the foreign key).
2749 * <LI><B>UPDATE_RULE</B> short => What happens to
2750 * foreign key when primary is updated:
2751 * <UL>
2752 * <LI> importedKeyNoAction - do not allow update of primary key if it has been imported
2753 * <LI> importedKeyCascade - change imported key to agree
2754 * with primary key update
2755 * <LI> importedKeyRestrict - do not allow update of primary
2756 * key if it has been imported
2757 * <LI> importedKeySetNull - change imported key to NULL if
2758 * its primary key has been updated
2759 * </UL>
2760 * <LI><B>DELETE_RULE</B> short => What happens to
2761 * the foreign key when primary is deleted.
2762 * <UL>
2763 * <LI> importedKeyNoAction - do not allow delete of primary key if it has been imported
2764 * <LI> importedKeyCascade - delete rows that import a deleted key
2765 * <LI> importedKeyRestrict - do not allow delete of primary
2766 * key if it has been imported
2767 * <LI> importedKeySetNull - change imported key to NULL if
2768 * its primary key has been deleted
2769 * </UL>
2770 * <LI><B>FK_NAME</B> String => foreign key identifier (may be null)
2771 * <LI><B>PK_NAME</B> String => primary key identifier (may be null)
2772 * <LI><B>DEFERRABILITY</B> short => can the evaluation of foreign key constraints be deferred until commit
2773 * <UL>
2774 * <LI> importedKeyInitiallyDeferred - see SQL92 for definition
2775 * <LI> importedKeyInitiallyImmediate - see SQL92 for definition
2776 * <LI> importedKeyNotDeferrable - see SQL92 for definition
2777 * </UL>
2778 * </OL>
2779 *
2780 * @param catalog a catalog name; "" retrieves those without a catalog
2781 * @param schema a schema name pattern; "" retrieves those without a schema
2782 * @param table a table name
2783 * @return ResultSet each row is a foreign key column description
2784 * @see #getImportedKeys
2785 * @throws SQLException if a database error occurs
2786 */
2787 @Override
2788 public ResultSet getExportedKeys(String catalog, String schema, String table)
2789 throws SQLException
2790 {
2791 StringBuilder query = new StringBuilder(keyQuery.length() + 250);
2792 query.append(keyQuery);
2793
2794 if (catalog != null && catalog.length() > 0) {
2795 // none empty catalog selection.
2796 // as we do not support catalogs this always results in no rows returned
2797 query.append(" AND 1 = 0");
2798 }
2799 if (schema != null) {
2800 query.append(" AND \"pkschema\".\"name\" ").append(composeMatchPart(schema));
2801 }
2802 if (table != null) {
2803 query.append(" AND \"pktable\".\"name\" ").append(composeMatchPart(table));
2804 }
2805
2806 query.append(" ORDER BY \"FKTABLE_SCHEM\", \"FKTABLE_NAME\", \"FK_NAME\", \"KEY_SEQ\"");
2807
2808 return executeMetaDataQuery(query.toString());
2809 }
2810
2811 /**
2812 * Get a description of the foreign key columns in the foreign key
2813 * table that reference the primary key columns of the primary key
2814 * table. (describe how one table imports another's key) This
2815 * should normally return a single foreign key/primary key pair
2816 * (most tables only import a foreign key from a table once.)
2817 * They are ordered by FKTABLE_CAT, FKTABLE_SCHEM, FKTABLE_NAME, and KEY_SEQ.
2818 *
2819 * <P>Each foreign key column description has the following columns:
2820 * <OL>
2821 * <LI><B>PKTABLE_CAT</B> String => primary key table catalog (may be null)
2822 * <LI><B>PKTABLE_SCHEM</B> String => primary key table schema (may be null)
2823 * <LI><B>PKTABLE_NAME</B> String => primary key table name
2824 * <LI><B>PKCOLUMN_NAME</B> String => primary key column name
2825 * <LI><B>FKTABLE_CAT</B> String => foreign key table catalog (may be null)
2826 * being exported (may be null)
2827 * <LI><B>FKTABLE_SCHEM</B> String => foreign key table schema (may be null)
2828 * being exported (may be null)
2829 * <LI><B>FKTABLE_NAME</B> String => foreign key table name
2830 * being exported
2831 * <LI><B>FKCOLUMN_NAME</B> String => foreign key column name
2832 * being exported
2833 * <LI><B>KEY_SEQ</B> short => sequence number within foreign key
2834 * (a value of 1 represents the first column of the foreign key, a value of 2 would represent the second column within the foreign key).
2835 * <LI><B>UPDATE_RULE</B> short => What happens to
2836 * foreign key when primary is updated:
2837 * <UL>
2838 * <LI> importedKeyNoAction - do not allow update of primary key if it has been imported
2839 * <LI> importedKeyCascade - change imported key to agree
2840 * with primary key update
2841 * <LI> importedKeyRestrict - do not allow update of primary
2842 * key if it has been imported
2843 * <LI> importedKeySetNull - change imported key to NULL if
2844 * its primary key has been updated
2845 * </UL>
2846 * <LI><B>DELETE_RULE</B> short => What happens to
2847 * the foreign key when primary is deleted.
2848 * <UL>
2849 * <LI> importedKeyNoAction - do not allow delete of primary key if it has been imported
2850 * <LI> importedKeyCascade - delete rows that import a deleted key
2851 * <LI> importedKeyRestrict - do not allow delete of primary
2852 * key if it has been imported
2853 * <LI> importedKeySetNull - change imported key to NULL if
2854 * its primary key has been deleted
2855 * </UL>
2856 * <LI><B>FK_NAME</B> String => foreign key identifier (may be null)
2857 * <LI><B>PK_NAME</B> String => primary key identifier (may be null)
2858 * <LI><B>DEFERRABILITY</B> short => can the evaluation of foreign key constraints be deferred until commit
2859 * <UL>
2860 * <LI> importedKeyInitiallyDeferred - see SQL92 for definition
2861 * <LI> importedKeyInitiallyImmediate - see SQL92 for definition
2862 * <LI> importedKeyNotDeferrable - see SQL92 for definition
2863 * </UL>
2864 * </OL>
2865 *
2866 * @param pcatalog primary key catalog name; "" retrieves those without a catalog
2867 * @param pschema primary key schema name pattern; "" retrieves those without a schema
2868 * @param ptable primary key table name
2869 * @param fcatalog foreign key catalog name; "" retrieves those without a catalog
2870 * @param fschema foreign key schema name pattern; "" retrieves those without a schema
2871 * @param ftable koreign key table name
2872 * @return ResultSet each row is a foreign key column description
2873 * @throws SQLException if a database error occurs
2874 * @see #getImportedKeys
2875 */
2876 @Override
2877 public ResultSet getCrossReference(
2878 String pcatalog,
2879 String pschema,
2880 String ptable,
2881 String fcatalog,
2882 String fschema,
2883 String ftable
2884 ) throws SQLException
2885 {
2886 StringBuilder query = new StringBuilder(keyQuery.length() + 350);
2887 query.append(keyQuery);
2888
2889 if (pcatalog != null && pcatalog.length() > 0) {
2890 // none empty catalog selection.
2891 // as we do not support catalogs this always results in no rows returned
2892 query.append(" AND 1 = 0");
2893 }
2894 if (pschema != null) {
2895 query.append(" AND \"pkschema\".\"name\" ").append(composeMatchPart(pschema));
2896 }
2897 if (ptable != null) {
2898 query.append(" AND \"pktable\".\"name\" ").append(composeMatchPart(ptable));
2899 }
2900
2901 if (fcatalog != null && fcatalog.length() > 0) {
2902 // none empty catalog selection.
2903 // as we do not support catalogs this always results in no rows returned
2904 query.append(" AND 1 = 0");
2905 }
2906 if (fschema != null) {
2907 query.append(" AND \"fkschema\".\"name\" ").append(composeMatchPart(fschema));
2908 }
2909 if (ftable != null) {
2910 query.append(" AND \"fktable\".\"name\" ").append(composeMatchPart(ftable));
2911 }
2912
2913 query.append(" ORDER BY \"FKTABLE_SCHEM\", \"FKTABLE_NAME\", \"FK_NAME\", \"KEY_SEQ\"");
2914
2915 return executeMetaDataQuery(query.toString());
2916 }
2917
2918 /**
2919 * Get a description of all the SQL data types supported by
2920 * this database. They are ordered by DATA_TYPE and then by how
2921 * closely the data type maps to the corresponding JDBC SQL type.
2922 *
2923 * If the database supports SQL distinct types, then getTypeInfo() will
2924 * return a single row with a TYPE_NAME of DISTINCT and a DATA_TYPE of Types.DISTINCT.
2925 * If the database supports SQL structured types, then getTypeInfo() will
2926 * return a single row with a TYPE_NAME of STRUCT and a DATA_TYPE of Types.STRUCT.
2927 * If SQL distinct or structured types are supported, then information on
2928 * the individual types may be obtained from the getUDTs() method.
2929 *
2930 * <P>Each type description has the following columns:
2931 * <OL>
2932 * <LI><B>TYPE_NAME</B> String => Type name
2933 * <LI><B>DATA_TYPE</B> int => SQL data type from java.sql.Types
2934 * <LI><B>PRECISION</B> int => maximum precision
2935 * <LI><B>LITERAL_PREFIX</B> String => prefix used to quote a literal (may be null)
2936 * <LI><B>LITERAL_SUFFIX</B> String => suffix used to quote a literal (may be null)
2937 * <LI><B>CREATE_PARAMS</B> String => parameters used in creating
2938 * the type (may be null)
2939 * <LI><B>NULLABLE</B> short => can you use NULL for this type?
2940 * <UL>
2941 * <LI> typeNoNulls - does not allow NULL values
2942 * <LI> typeNullable - allows NULL values
2943 * <LI> typeNullableUnknown - nullability unknown
2944 * </UL>
2945 * <LI><B>CASE_SENSITIVE</B> boolean=> is it case sensitive?
2946 * <LI><B>SEARCHABLE</B> short => can you use "WHERE" based on this type:
2947 * <UL>
2948 * <LI> typePredNone - No support
2949 * <LI> typePredChar - Only supported with WHERE .. LIKE
2950 * <LI> typePredBasic - Supported except for WHERE .. LIKE
2951 * <LI> typeSearchable - Supported for all WHERE ..
2952 * </UL>
2953 * <LI><B>UNSIGNED_ATTRIBUTE</B> boolean => is it unsigned?
2954 * <LI><B>FIXED_PREC_SCALE</B> boolean => can it be a money value?
2955 * <LI><B>AUTO_INCREMENT</B> boolean => can it be used for an
2956 * auto-increment value?
2957 * <LI><B>LOCAL_TYPE_NAME</B> String => localized version of type name
2958 * (may be null)
2959 * <LI><B>MINIMUM_SCALE</B> short => minimum scale supported
2960 * <LI><B>MAXIMUM_SCALE</B> short => maximum scale supported
2961 * <LI><B>SQL_DATA_TYPE</B> int => unused
2962 * <LI><B>SQL_DATETIME_SUB</B> int => unused
2963 * <LI><B>NUM_PREC_RADIX</B> int => usually 2 or 10
2964 * </OL>
2965 *
2966 * @return ResultSet each row is a SQL type description
2967 * @throws Exception if the developer made a Boo-Boo
2968 */
2969 @Override
2970 public ResultSet getTypeInfo() throws SQLException {
2971 StringBuilder query = new StringBuilder(2300);
2972 query.append("SELECT \"sqlname\" AS \"TYPE_NAME\", " +
2973 "cast(").append(MonetDriver.getSQLTypeMap("\"sqlname\"")).append(" AS int) AS \"DATA_TYPE\", " +
2974 "\"digits\" AS \"PRECISION\", " + // note that when radix is 2 the precision shows the number of bits
2975 "cast(CASE WHEN \"systemname\" IN ('str', 'inet', 'json', 'url', 'uuid') THEN ''''" +
2976 " ELSE NULL END AS varchar(2)) AS \"LITERAL_PREFIX\", " +
2977 "cast(CASE WHEN \"systemname\" IN ('str', 'inet', 'json', 'url', 'uuid') THEN ''''" +
2978 " ELSE NULL END AS varchar(2)) AS \"LITERAL_SUFFIX\", " +
2979 "CASE WHEN \"sqlname\" IN ('char', 'varchar') THEN 'max length'" +
2980 " WHEN \"sqlname\" = 'decimal' THEN 'precision, scale'" +
2981 " WHEN \"sqlname\" IN ('time', 'timetz', 'timestamp', 'timestamptz', 'sec_interval') THEN 'precision'" +
2982 " ELSE NULL END AS \"CREATE_PARAMS\", " +
2983 "cast(CASE WHEN \"systemname\" = 'oid' THEN ").append(DatabaseMetaData.typeNoNulls)
2984 .append(" ELSE ").append(DatabaseMetaData.typeNullable).append(" END AS smallint) AS \"NULLABLE\", " +
2985 "CASE WHEN \"systemname\" IN ('str', 'json', 'url') THEN true ELSE false END AS \"CASE_SENSITIVE\", " +
2986 "cast(CASE \"systemname\" WHEN 'table' THEN ").append(DatabaseMetaData.typePredNone)
2987 .append(" WHEN 'str' THEN ").append(DatabaseMetaData.typePredChar)
2988 .append(" WHEN 'sqlblob' THEN ").append(DatabaseMetaData.typePredChar)
2989 .append(" ELSE ").append(DatabaseMetaData.typePredBasic).append(" END AS smallint) AS \"SEARCHABLE\", " +
2990 "CASE WHEN \"sqlname\" IN ('tinyint','smallint','int','bigint','hugeint','decimal','real','double','sec_interval','month_interval') THEN false ELSE true END AS \"UNSIGNED_ATTRIBUTE\", " +
2991 "CASE \"sqlname\" WHEN 'decimal' THEN true ELSE false END AS \"FIXED_PREC_SCALE\", " +
2992 "CASE WHEN \"sqlname\" IN ('tinyint','smallint','int','bigint','hugeint','decimal','oid','wrd') THEN true ELSE false END AS \"AUTO_INCREMENT\", " +
2993 "\"systemname\" AS \"LOCAL_TYPE_NAME\", " +
2994 "cast(0 AS smallint) AS \"MINIMUM_SCALE\", " +
2995 "cast(CASE WHEN \"sqlname\" = 'decimal' THEN (CASE \"systemname\" WHEN 'lng' THEN 18 WHEN 'hge' THEN 38 WHEN 'int' THEN 9 WHEN 'sht' THEN 4 WHEN 'bte' THEN 2 ELSE 0 END)" +
2996 " WHEN \"sqlname\" IN ('sec_interval', 'timestamp', 'timestamptz') THEN 9 ELSE 0 END AS smallint) AS \"MAXIMUM_SCALE\", " +
2997 "cast(0 AS int) AS \"SQL_DATA_TYPE\", " +
2998 "cast(0 AS int) AS \"SQL_DATETIME_SUB\", " +
2999 "cast(\"radix\" as int) AS \"NUM_PREC_RADIX\" " +
3000 "FROM \"sys\".\"types\" " +
3001 "ORDER BY \"DATA_TYPE\", \"sqlname\", \"id\"");
3002
3003 return executeMetaDataQuery(query.toString());
3004 }
3005
3006 /**
3007 * Retrieves a description of the given table's indices and statistics.
3008 * They are ordered by NON_UNIQUE, TYPE, INDEX_NAME, and ORDINAL_POSITION.
3009 *
3010 * <P>Each index column description has the following columns:
3011 * <OL>
3012 * <LI><B>TABLE_CAT</B> String => table catalog (may be null)
3013 * <LI><B>TABLE_SCHEM</B> String => table schema (may be null)
3014 * <LI><B>TABLE_NAME</B> String => table name
3015 * <LI><B>NON_UNIQUE</B> boolean => Can index values be non-unique?
3016 * false when TYPE is tableIndexStatistic
3017 * <LI><B>INDEX_QUALIFIER</B> String => index catalog (may be null);
3018 * null when TYPE is tableIndexStatistic
3019 * <LI><B>INDEX_NAME</B> String => index name; null when TYPE is
3020 * tableIndexStatistic
3021 * <LI><B>TYPE</B> short => index type:
3022 * <UL>
3023 * <LI> tableIndexStatistic - this identifies table statistics that are
3024 * returned in conjuction with a table's index descriptions
3025 * <LI> tableIndexClustered - this is a clustered index
3026 * <LI> tableIndexHashed - this is a hashed index
3027 * <LI> tableIndexOther - this is some other style of index
3028 * </UL>
3029 * <LI><B>ORDINAL_POSITION</B> short => column sequence number
3030 * within index; zero when TYPE is tableIndexStatistic
3031 * <LI><B>COLUMN_NAME</B> String => column name; null when TYPE is
3032 * tableIndexStatistic
3033 * <LI><B>ASC_OR_DESC</B> String => column sort sequence, "A" => ascending
3034 * "D" => descending, may be null if sort sequence is not supported;
3035 * null when TYPE is tableIndexStatistic
3036 * <LI><B>CARDINALITY</B> int => When TYPE is tableIndexStatisic then
3037 * this is the number of rows in the table; otherwise it is the
3038 * number of unique values in the index.
3039 * <LI><B>PAGES</B> int => When TYPE is tableIndexStatisic then
3040 * this is the number of pages used for the table, otherwise it
3041 * is the number of pages used for the current index.
3042 * <LI><B>FILTER_CONDITION</B> String => Filter condition, if any.
3043 * (may be null)
3044 * </OL>
3045 *
3046 * @param catalog a catalog name; "" retrieves those without a catalog
3047 * @param schema a schema name pattern; "" retrieves those without a schema
3048 * @param table a table name
3049 * @param unique when true, return only indices for unique values;
3050 * when false, return indices regardless of whether unique or not
3051 * @param approximate when true, result is allowed to reflect approximate
3052 * or out of data values; when false, results are requested to be
3053 * accurate
3054 * @return ResultSet each row is an index column description
3055 * @throws SQLException if a database occurs
3056 */
3057 @Override
3058 public ResultSet getIndexInfo(
3059 String catalog,
3060 String schema,
3061 String table,
3062 boolean unique,
3063 boolean approximate
3064 ) throws SQLException
3065 {
3066 String table_row_count = "0";
3067
3068 if (!approximate && schema != null && table != null && schema.length() > 0 && table.length() > 0) {
3069 // we need the exact cardinality for one specific fully qualified table
3070 ResultSet count = null;
3071 try {
3072 count = executeMetaDataQuery("SELECT COUNT(*) FROM \"" + schema + "\".\"" + table + "\"");
3073 if (count != null && count.next()) {
3074 String count_value = count.getString(1);
3075 if (count_value != null && count_value.length() > 0)
3076 table_row_count = count_value;
3077 }
3078 } catch (SQLException e) {
3079 // ignore
3080 } finally {
3081 if (count != null) {
3082 try {
3083 count.close();
3084 } catch (SQLException e) { /* ignore */ }
3085 }
3086 }
3087 }
3088
3089 StringBuilder query = new StringBuilder(1250);
3090 query.append(
3091 "SELECT CAST(null AS char(1)) AS \"TABLE_CAT\", " +
3092 "\"schemas\".\"name\" AS \"TABLE_SCHEM\", " +
3093 "\"tables\".\"name\" AS \"TABLE_NAME\", " +
3094 "CASE WHEN \"keys\".\"name\" IS NULL THEN true ELSE false END AS \"NON_UNIQUE\", " +
3095 "CAST(null AS varchar(1)) AS \"INDEX_QUALIFIER\", " +
3096 "\"idxs\".\"name\" AS \"INDEX_NAME\", " +
3097 "CASE \"idxs\".\"type\" WHEN 0 THEN ").append(DatabaseMetaData.tableIndexHashed).append(" ELSE ").append(DatabaseMetaData.tableIndexOther).append(" END AS \"TYPE\", " +
3098 "CAST(\"objects\".\"nr\" +1 AS smallint) AS \"ORDINAL_POSITION\", "+
3099 "\"columns\".\"name\" AS \"COLUMN_NAME\", " +
3100 "CAST(null AS varchar(1)) AS \"ASC_OR_DESC\", " + // sort sequence currently not supported in keys or indexes in MonetDB
3101 "CAST(").append(table_row_count).append(" AS int) AS \"CARDINALITY\", " +
3102 "CAST(0 AS int) AS \"PAGES\", " +
3103 "CAST(null AS varchar(1)) AS \"FILTER_CONDITION\" " +
3104 "FROM \"sys\".\"idxs\" LEFT JOIN \"sys\".\"keys\" ON \"idxs\".\"name\" = \"keys\".\"name\", " +
3105 "\"sys\".\"schemas\", " +
3106 "\"sys\".\"objects\", " +
3107 "\"sys\".\"columns\", " +
3108 "\"sys\".\"tables\" " +
3109 "WHERE \"idxs\".\"table_id\" = \"tables\".\"id\" " +
3110 "AND \"tables\".\"schema_id\" = \"schemas\".\"id\" " +
3111 "AND \"idxs\".\"id\" = \"objects\".\"id\" " +
3112 "AND \"tables\".\"id\" = \"columns\".\"table_id\" " +
3113 "AND \"objects\".\"name\" = \"columns\".\"name\" " +
3114 "AND (\"keys\".\"type\" IS NULL OR \"keys\".\"type\" = 1)");
3115
3116 if (catalog != null && catalog.length() > 0) {
3117 // none empty catalog selection.
3118 // as we do not support catalogs this always results in no rows returned
3119 query.append(" AND 1 = 0");
3120 }
3121 if (schema != null) {
3122 query.append(" AND \"schemas\".\"name\" ").append(composeMatchPart(schema));
3123 }
3124 if (table != null) {
3125 query.append(" AND \"tables\".\"name\" ").append(composeMatchPart(table));
3126 }
3127 if (unique) {
3128 query.append(" AND \"keys\".\"name\" IS NOT NULL");
3129 }
3130 query.append(" ORDER BY \"NON_UNIQUE\", \"TYPE\", \"INDEX_NAME\", \"ORDINAL_POSITION\"");
3131
3132 return executeMetaDataQuery(query.toString());
3133 }
3134
3135 //== 1.2 methods (JDBC 2)
3136
3137 /**
3138 * Does the database support the given result set type?
3139 *
3140 * @param type - defined in java.sql.ResultSet
3141 * @return true if so; false otherwise
3142 * @throws SQLException - if a database access error occurs
3143 */
3144 @Override
3145 public boolean supportsResultSetType(int type) throws SQLException {
3146 // The only type we don't support
3147 return type != ResultSet.TYPE_SCROLL_SENSITIVE;
3148 }
3149
3150
3151 /**
3152 * Does the database support the concurrency type in combination
3153 * with the given result set type?
3154 *
3155 * @param type - defined in java.sql.ResultSet
3156 * @param concurrency - type defined in java.sql.ResultSet
3157 * @return true if so; false otherwise
3158 * @throws SQLException - if a database access error occurs
3159 */
3160 @Override
3161 public boolean supportsResultSetConcurrency(int type, int concurrency)
3162 throws SQLException
3163 {
3164 // These combinations are not supported!
3165 if (type == ResultSet.TYPE_SCROLL_SENSITIVE)
3166 return false;
3167
3168 // We do only support Read Only ResultSets
3169 if (concurrency != ResultSet.CONCUR_READ_ONLY)
3170 return false;
3171
3172 // Everything else we do (well, what's left of it :) )
3173 return true;
3174 }
3175
3176
3177 /* lots of unsupported stuff... (no updatable ResultSet!) */
3178 @Override
3179 public boolean ownUpdatesAreVisible(int type) {
3180 return false;
3181 }
3182
3183 @Override
3184 public boolean ownDeletesAreVisible(int type) {
3185 return false;
3186 }
3187
3188 @Override
3189 public boolean ownInsertsAreVisible(int type) {
3190 return false;
3191 }
3192
3193 @Override
3194 public boolean othersUpdatesAreVisible(int type) {
3195 return false;
3196 }
3197
3198 @Override
3199 public boolean othersDeletesAreVisible(int i) {
3200 return false;
3201 }
3202
3203 @Override
3204 public boolean othersInsertsAreVisible(int type) {
3205 return false;
3206 }
3207
3208 @Override
3209 public boolean updatesAreDetected(int type) {
3210 return false;
3211 }
3212
3213 @Override
3214 public boolean deletesAreDetected(int i) {
3215 return false;
3216 }
3217
3218 @Override
3219 public boolean insertsAreDetected(int type) {
3220 return false;
3221 }
3222
3223 /**
3224 * Indicates whether the driver supports batch updates.
3225 */
3226 @Override
3227 public boolean supportsBatchUpdates() {
3228 return true;
3229 }
3230
3231 /**
3232 * Retrieves a description of the user-defined types (UDTs) defined in a particular schema.
3233 * Schema-specific UDTs may have type JAVA_OBJECT, STRUCT, or DISTINCT.
3234 * Only types matching the catalog, schema, type name and type criteria are returned.
3235 * They are ordered by DATA_TYPE, TYPE_CAT, TYPE_SCHEM and TYPE_NAME.
3236 * The type name parameter may be a fully-qualified name. In this case, the catalog and schemaPattern parameters are ignored.
3237 *
3238 * Each type description has the following columns:
3239 *
3240 * 1 TYPE_CAT String => the type's catalog (may be null)
3241 * 2 TYPE_SCHEM String => type's schema (may be null)
3242 * 3 TYPE_NAME String => type name
3243 * 4 CLASS_NAME String => Java class name
3244 * 5 DATA_TYPE int => type value defined in java.sql.Types. One of JAVA_OBJECT, STRUCT, or DISTINCT
3245 * 6 REMARKS String => explanatory comment on the type
3246 * 7 BASE_TYPE short => type code of the source type of a DISTINCT type or the type that implements the
3247 * user-generated reference type of the SELF_REFERENCING_COLUMN of a structured type as defined
3248 * in java.sql.Types (null if DATA_TYPE is not DISTINCT or not STRUCT with REFERENCE_GENERATION = USER_DEFINED)
3249 *
3250 * @throws SQLException
3251 */
3252 @Override
3253 public ResultSet getUDTs(
3254 String catalog,
3255 String schemaPattern,
3256 String typeNamePattern,
3257 int[] types
3258 ) throws SQLException
3259 {
3260 StringBuilder query = new StringBuilder(990);
3261 if (types != null && types.length > 0) {
3262 query.append("SELECT * FROM (");
3263 }
3264 query.append("SELECT cast(null as char(1)) AS \"TYPE_CAT\", " +
3265 "\"schemas\".\"name\" AS \"TYPE_SCHEM\", " +
3266 "\"types\".\"sqlname\" AS \"TYPE_NAME\", " +
3267 "CASE \"types\".\"sqlname\"" +
3268 // next 4 UDTs are known
3269 " WHEN 'inet' THEN 'nl.cwi.monetdb.jdbc.types.INET'" +
3270 " WHEN 'json' THEN 'java.lang.String'" +
3271 " WHEN 'url' THEN 'nl.cwi.monetdb.jdbc.types.URL'" +
3272 " WHEN 'uuid' THEN 'java.lang.String'" +
3273 " ELSE 'java.lang.Object' END AS \"CLASS_NAME\", " +
3274 "CAST(CASE WHEN \"types\".\"sqlname\" IN ('inet', 'json', 'url', 'uuid') THEN ").append(Types.JAVA_OBJECT)
3275 .append(" ELSE ").append(Types.STRUCT).append(" END AS int) AS \"DATA_TYPE\", " +
3276 "\"types\".\"systemname\" AS \"REMARKS\", " +
3277 "cast(null as smallint) AS \"BASE_TYPE\" " +
3278 "FROM \"sys\".\"types\" JOIN \"sys\".\"schemas\" ON \"types\".\"schema_id\" = \"schemas\".\"id\" " +
3279 // exclude the built-in types (I assume they always have id <= 99 and eclass < 15)
3280 "WHERE \"types\".\"id\" > 99 AND \"types\".\"eclass\" >= 15");
3281
3282 if (catalog != null && catalog.length() > 0) {
3283 // none empty catalog selection.
3284 // as we do not support catalogs this always results in no rows returned
3285 query.append(" AND 1 = 0");
3286 }
3287 if (schemaPattern != null) {
3288 query.append(" AND \"schemas\".\"name\" ").append(composeMatchPart(schemaPattern));
3289 }
3290 if (typeNamePattern != null) {
3291 query.append(" AND \"types\".\"sqlname\" ").append(composeMatchPart(typeNamePattern));
3292 }
3293 if (types != null && types.length > 0) {
3294 query.append(") AS getUDTs WHERE \"DATA_TYPE\" IN (");
3295 for (int i = 0; i < types.length; i++) {
3296 if (i > 0) {
3297 query.append(", ");
3298 }
3299 query.append(types[i]);
3300 }
3301 query.append(")");
3302 }
3303 query.append(" ORDER BY \"DATA_TYPE\", \"TYPE_SCHEM\", \"TYPE_NAME\"");
3304
3305 return executeMetaDataQuery(query.toString());
3306 }
3307
3308
3309 /**
3310 * Retrieves the connection that produced this metadata object.
3311 *
3312 * @return the connection that produced this metadata object
3313 */
3314 @Override
3315 public Connection getConnection() {
3316 return con;
3317 }
3318
3319 /* I don't find these in the spec!?! */
3320 public boolean rowChangesAreDetected(int type) {
3321 return false;
3322 }
3323
3324 public boolean rowChangesAreVisible(int type) {
3325 return false;
3326 }
3327
3328 //== 1.4 methods (JDBC 3)
3329
3330 /**
3331 * Retrieves whether this database supports savepoints.
3332 *
3333 * @return <code>true</code> if savepoints are supported;
3334 * <code>false</code> otherwise
3335 */
3336 @Override
3337 public boolean supportsSavepoints() {
3338 return true;
3339 }
3340
3341 /**
3342 * Retrieves whether this database supports named parameters to callable
3343 * statements.
3344 *
3345 * @return <code>true</code> if named parameters are supported;
3346 * <code>false</code> otherwise
3347 */
3348 @Override
3349 public boolean supportsNamedParameters() {
3350 return false;
3351 }
3352
3353 /**
3354 * Retrieves whether it is possible to have multiple <code>ResultSet</code> objects
3355 * returned from a <code>CallableStatement</code> object
3356 * simultaneously.
3357 *
3358 * @return <code>true</code> if a <code>CallableStatement</code> object
3359 * can return multiple <code>ResultSet</code> objects
3360 * simultaneously; <code>false</code> otherwise
3361 */
3362 @Override
3363 public boolean supportsMultipleOpenResults() {
3364 return true;
3365 }
3366
3367 /**
3368 * Retrieves whether auto-generated keys can be retrieved after
3369 * a statement has been executed.
3370 *
3371 * @return <code>true</code> if auto-generated keys can be retrieved
3372 * after a statement has executed; <code>false</code> otherwise
3373 */
3374 @Override
3375 public boolean supportsGetGeneratedKeys() {
3376 return true;
3377 }
3378
3379 /**
3380 * Retrieves a description of the user-defined type (UDT)
3381 * hierarchies defined in a particular schema in this database. Only
3382 * the immediate super type/ sub type relationship is modeled.
3383 * <P>
3384 * Only supertype information for UDTs matching the catalog,
3385 * schema, and type name is returned. The type name parameter
3386 * may be a fully-qualified name. When the UDT name supplied is a
3387 * fully-qualified name, the catalog and schemaPattern parameters are
3388 * ignored.
3389 * <P>
3390 * If a UDT does not have a direct super type, it is not listed here.
3391 * A row of the <code>ResultSet</code> object returned by this method
3392 * describes the designated UDT and a direct supertype. A row has the following
3393 * columns:
3394 * <OL>
3395 * <LI><B>TYPE_CAT</B> String => the UDT's catalog (may be <code>null</code>)
3396 * <LI><B>TYPE_SCHEM</B> String => UDT's schema (may be <code>null</code>)
3397 * <LI><B>TYPE_NAME</B> String => type name of the UDT
3398 * <LI><B>SUPERTYPE_CAT</B> String => the direct super type's catalog
3399 * (may be <code>null</code>)
3400 * <LI><B>SUPERTYPE_SCHEM</B> String => the direct super type's schema
3401 * (may be <code>null</code>)
3402 * <LI><B>SUPERTYPE_NAME</B> String => the direct super type's name
3403 * </OL>
3404 *
3405 * <P><B>Note:</B> If the driver does not support type hierarchies, an
3406 * empty result set is returned.
3407 *
3408 * @param catalog a catalog name; "" retrieves those without a catalog;
3409 * <code>null</code> means drop catalog name from the selection criteria
3410 * @param schemaPattern a schema name pattern; "" retrieves those
3411 * without a schema
3412 * @param typeNamePattern a UDT name pattern; may be a fully-qualified
3413 * name
3414 * @return a <code>ResultSet</code> object in which a row gives information
3415 * about the designated UDT
3416 * @throws SQLException if a database access error occurs
3417 */
3418 @Override
3419 public ResultSet getSuperTypes(
3420 String catalog,
3421 String schemaPattern,
3422 String typeNamePattern
3423 ) throws SQLException
3424 {
3425 String query =
3426 "SELECT cast(null as char(1)) AS \"TYPE_CAT\", '' AS \"TYPE_SCHEM\", '' AS \"TYPE_NAME\", " +
3427 "cast(null as char(1)) AS \"SUPERTYPE_CAT\", '' AS \"SUPERTYPE_SCHEM\", '' AS \"SUPERTYPE_NAME\" " +
3428 "WHERE 1 = 0";
3429
3430 return executeMetaDataQuery(query);
3431 }
3432
3433 /**
3434 * Retrieves a description of the table hierarchies defined in a particular
3435 * schema in this database.
3436 *
3437 * <P>Only supertable information for tables matching the catalog, schema
3438 * and table name are returned. The table name parameter may be a fully-
3439 * qualified name, in which case, the catalog and schemaPattern parameters
3440 * are ignored. If a table does not have a super table, it is not listed here.
3441 * Supertables have to be defined in the same catalog and schema as the
3442 * sub tables. Therefore, the type description does not need to include
3443 * this information for the supertable.
3444 *
3445 * <P>Each type description has the following columns:
3446 * <OL>
3447 * <LI><B>TABLE_CAT</B> String => the type's catalog (may be <code>null</code>)
3448 * <LI><B>TABLE_SCHEM</B> String => type's schema (may be <code>null</code>)
3449 * <LI><B>TABLE_NAME</B> String => type name
3450 * <LI><B>SUPERTABLE_NAME</B> String => the direct super type's name
3451 * </OL>
3452 *
3453 * <P><B>Note:</B> If the driver does not support type hierarchies, an
3454 * empty result set is returned.
3455 *
3456 * @param catalog a catalog name; "" retrieves those without a catalog;
3457 * <code>null</code> means drop catalog name from the selection criteria
3458 * @param schemaPattern a schema name pattern; "" retrieves those
3459 * without a schema
3460 * @param tableNamePattern a table name pattern; may be a fully-qualified
3461 * name
3462 * @return a <code>ResultSet</code> object in which each row is a type description
3463 * @throws SQLException if a database access error occurs
3464 */
3465 @Override
3466 public ResultSet getSuperTables(
3467 String catalog,
3468 String schemaPattern,
3469 String tableNamePattern
3470 ) throws SQLException
3471 {
3472 String query =
3473 "SELECT cast(null as char(1)) AS \"TABLE_CAT\", " +
3474 "'' AS \"TABLE_SCHEM\", '' AS \"TABLE_NAME\", '' AS \"SUPERTABLE_NAME\" " +
3475 "WHERE 1 = 0";
3476
3477 return executeMetaDataQuery(query);
3478 }
3479
3480 /**
3481 * Retrieves a description of the given attribute of the given type
3482 * for a user-defined type (UDT) that is available in the given schema
3483 * and catalog.
3484 * <P>
3485 * Descriptions are returned only for attributes of UDTs matching the
3486 * catalog, schema, type, and attribute name criteria. They are ordered by
3487 * TYPE_SCHEM, TYPE_NAME and ORDINAL_POSITION. This description
3488 * does not contain inherited attributes.
3489 * <P>
3490 * The <code>ResultSet</code> object that is returned has the following
3491 * columns:
3492 * <OL>
3493 * <LI><B>TYPE_CAT</B> String => type catalog (may be <code>null</code>)
3494 * <LI><B>TYPE_SCHEM</B> String => type schema (may be <code>null</code>)
3495 * <LI><B>TYPE_NAME</B> String => type name
3496 * <LI><B>ATTR_NAME</B> String => attribute name
3497 * <LI><B>DATA_TYPE</B> int => attribute type SQL type from java.sql.Types
3498 * <LI><B>ATTR_TYPE_NAME</B> String => Data source dependent type name.
3499 * For a UDT, the type name is fully qualified. For a REF, the type name is
3500 * fully qualified and represents the target type of the reference type.
3501 * <LI><B>ATTR_SIZE</B> int => column size. For char or date
3502 * types this is the maximum number of characters; for numeric or
3503 * decimal types this is precision.
3504 * <LI><B>DECIMAL_DIGITS</B> int => the number of fractional digits
3505 * <LI><B>NUM_PREC_RADIX</B> int => Radix (typically either 10 or 2)
3506 * <LI><B>NULLABLE</B> int => whether NULL is allowed
3507 * <UL>
3508 * <LI> attributeNoNulls - might not allow NULL values
3509 * <LI> attributeNullable - definitely allows NULL values
3510 * <LI> attributeNullableUnknown - nullability unknown
3511 * </UL>
3512 * <LI><B>REMARKS</B> String => comment describing column (may be <code>null</code>)
3513 * <LI><B>ATTR_DEF</B> String => default value (may be <code>null</code>)
3514 * <LI><B>SQL_DATA_TYPE</B> int => unused
3515 * <LI><B>SQL_DATETIME_SUB</B> int => unused
3516 * <LI><B>CHAR_OCTET_LENGTH</B> int => for char types the
3517 * maximum number of bytes in the column
3518 * <LI><B>ORDINAL_POSITION</B> int => index of column in table
3519 * (starting at 1)
3520 * <LI><B>IS_NULLABLE</B> String => "NO" means column definitely
3521 * does not allow NULL values; "YES" means the column might
3522 * allow NULL values. An empty string means unknown.
3523 * <LI><B>SCOPE_CATALOG</B> String => catalog of table that is the
3524 * scope of a reference attribute (<code>null</code> if DATA_TYPE isn't REF)
3525 * <LI><B>SCOPE_SCHEMA</B> String => schema of table that is the
3526 * scope of a reference attribute (<code>null</code> if DATA_TYPE isn't REF)
3527 * <LI><B>SCOPE_TABLE</B> String => table name that is the scope of a
3528 * reference attribute (<code>null</code> if the DATA_TYPE isn't REF)
3529 * <LI><B>SOURCE_DATA_TYPE</B> short => source type of a distinct type or user-generated
3530 * Ref type,SQL type from java.sql.Types (<code>null</code> if DATA_TYPE
3531 * isn't DISTINCT or user-generated REF)
3532 * </OL>
3533 * @param catalog a catalog name; must match the catalog name as it
3534 * is stored in the database; "" retrieves those without a catalog;
3535 * <code>null</code> means that the catalog name should not be used to narrow
3536 * the search
3537 * @param schemaPattern a schema name pattern; must match the schema name
3538 * as it is stored in the database; "" retrieves those without a schema;
3539 * <code>null</code> means that the schema name should not be used to narrow
3540 * the search
3541 * @param typeNamePattern a type name pattern; must match the
3542 * type name as it is stored in the database
3543 * @param attributeNamePattern an attribute name pattern; must match the attribute
3544 * name as it is declared in the database
3545 * @return a <code>ResultSet</code> object in which each row is an
3546 * attribute description
3547 * @throws SQLException if a database access error occurs
3548 */
3549 @Override
3550 public ResultSet getAttributes(
3551 String catalog,
3552 String schemaPattern,
3553 String typeNamePattern,
3554 String attributeNamePattern
3555 ) throws SQLException
3556 {
3557 String query =
3558 "SELECT cast(null as char(1)) AS \"TYPE_CAT\", '' AS \"TYPE_SCHEM\", '' AS \"TYPE_NAME\", " +
3559 "'' AS \"ATTR_NAME\", CAST(0 as int) AS \"DATA_TYPE\", '' AS \"ATTR_TYPE_NAME\", CAST(0 as int) AS \"ATTR_SIZE\", " +
3560 "CAST(0 as int) AS \"DECIMAL_DIGITS\", CAST(0 as int) AS \"NUM_PREC_RADIX\", CAST(0 as int) AS \"NULLABLE\", " +
3561 "'' AS \"REMARKS\", '' AS \"ATTR_DEF\", CAST(0 as int) AS \"SQL_DATA_TYPE\", " +
3562 "CAST(0 as int) AS \"SQL_DATETIME_SUB\", CAST(0 as int) AS \"CHAR_OCTET_LENGTH\", " +
3563 "CAST(0 as int) AS \"ORDINAL_POSITION\", 'YES' AS \"IS_NULLABLE\", " +
3564 "'' AS \"SCOPE_CATALOG\", '' AS \"SCOPE_SCHEMA\", '' AS \"SCOPE_TABLE\", " +
3565 "CAST(0 as smallint) AS \"SOURCE_DATA_TYPE\" " +
3566 "WHERE 1 = 0";
3567
3568 return executeMetaDataQuery(query);
3569 }
3570
3571 /**
3572 * Retrieves whether this database supports the given result set holdability.
3573 *
3574 * @param holdability one of the following constants:
3575 * <code>ResultSet.HOLD_CURSORS_OVER_COMMIT</code> or
3576 * <code>ResultSet.CLOSE_CURSORS_AT_COMMIT</code>
3577 * @return <code>true</code> if so; <code>false</code> otherwise
3578 * @see Connection
3579 */
3580 @Override
3581 public boolean supportsResultSetHoldability(int holdability) {
3582 // we don't close ResultSets at commit; and we don't do updateable
3583 // result sets, so comes closest to hold cursors over commit
3584 return holdability == ResultSet.HOLD_CURSORS_OVER_COMMIT;
3585 }
3586
3587 /**
3588 * Retrieves the default holdability of this <code>ResultSet</code>
3589 * object.
3590 *
3591 * @return the default holdability; either
3592 * <code>ResultSet.HOLD_CURSORS_OVER_COMMIT</code> or
3593 * <code>ResultSet.CLOSE_CURSORS_AT_COMMIT</code>
3594 */
3595 @Override
3596 public int getResultSetHoldability() {
3597 return ResultSet.HOLD_CURSORS_OVER_COMMIT;
3598 }
3599
3600 /**
3601 * Retrieves the major version number of the underlying database.
3602 *
3603 * @return the underlying database's major version
3604 * @throws SQLException if a database access error occurs
3605 */
3606 @Override
3607 public int getDatabaseMajorVersion() throws SQLException {
3608 if (env_monet_version == null)
3609 getEnvValues();
3610 int major = 0;
3611 if (env_monet_version != null) {
3612 try {
3613 int start = env_monet_version.indexOf(".");
3614 major = Integer.parseInt(env_monet_version.substring(0, start));
3615 } catch (NumberFormatException e) {
3616 // ignore
3617 }
3618 }
3619 return major;
3620 }
3621
3622 /**
3623 * Retrieves the minor version number of the underlying database.
3624 *
3625 * @return underlying database's minor version
3626 * @throws SQLException if a database access error occurs
3627 */
3628 @Override
3629 public int getDatabaseMinorVersion() throws SQLException {
3630 if (env_monet_version == null)
3631 getEnvValues();
3632 int minor = 0;
3633 if (env_monet_version != null) {
3634 try {
3635 int start = env_monet_version.indexOf(".");
3636 int end = env_monet_version.indexOf(".", start + 1);
3637 minor = Integer.parseInt(env_monet_version.substring(start + 1, end));
3638 } catch (NumberFormatException e) {
3639 // ignore
3640 }
3641 }
3642 return minor;
3643 }
3644
3645 /**
3646 * Retrieves the major JDBC version number for this driver.
3647 *
3648 * @return JDBC version major number
3649 */
3650 @Override
3651 public int getJDBCMajorVersion() {
3652 return 4; // This class implements JDBC 4.1 (at least we try to)
3653 }
3654
3655 /**
3656 * Retrieves the minor JDBC version number for this driver.
3657 *
3658 * @return JDBC version minor number
3659 */
3660 @Override
3661 public int getJDBCMinorVersion() {
3662 return 1; // This class implements JDBC 4.1 (at least we try to)
3663 }
3664
3665 /**
3666 * Indicates whether the SQLSTATEs returned by <code>SQLException.getSQLState</code>
3667 * is X/Open (now known as Open Group) SQL CLI or SQL:2003.
3668 * @return the type of SQLSTATEs, one of:
3669 * sqlStateXOpen or
3670 * sqlStateSQL
3671 */
3672 @Override
3673 public int getSQLStateType() {
3674 // At least this driver conforms with SQLSTATE to the SQL:2003 standard
3675 return DatabaseMetaData.sqlStateSQL;
3676 }
3677
3678 /**
3679 * Indicates whether updates made to a LOB are made on a copy or directly
3680 * to the LOB.
3681 * @return <code>true</code> if updates are made to a copy of the LOB;
3682 * <code>false</code> if updates are made directly to the LOB
3683 */
3684 @Override
3685 public boolean locatorsUpdateCopy() {
3686 // not that we have it, but in a transaction it will be copy-on-write
3687 return true;
3688 }
3689
3690 /**
3691 * Retrieves whether this database supports statement pooling.
3692 *
3693 * @return <code>true</code> is so;
3694 <code>false</code> otherwise
3695 */
3696 @Override
3697 public boolean supportsStatementPooling() {
3698 // For the moment, I don't think so
3699 return false;
3700 }
3701
3702 //== 1.6 methods (JDBC 4)
3703
3704 /**
3705 * Indicates whether or not this data source supports the SQL ROWID
3706 * type, and if so the lifetime for which a RowId object remains
3707 * valid.
3708 *
3709 * @return ROWID_UNSUPPORTED for now
3710 */
3711 @Override
3712 public RowIdLifetime getRowIdLifetime() {
3713 // I believe we don't do rowids
3714 return RowIdLifetime.ROWID_UNSUPPORTED;
3715 }
3716
3717 /**
3718 * Get the schema names available in this database. The results
3719 * are ordered by schema name.
3720 *
3721 * <P>The schema column is:
3722 * <OL>
3723 * <LI><B>TABLE_SCHEM</B> String => schema name
3724 * <LI><B>TABLE_CATALOG</B> String => catalog name (may be null)
3725 * </OL>
3726 *
3727 * @return ResultSet each row has a single String column that is a
3728 * schema name
3729 * @throws SQLException if a database error occurs
3730 */
3731 @Override
3732 public ResultSet getSchemas() throws SQLException {
3733 return getSchemas(null, null);
3734 }
3735
3736 /**
3737 * Retrieves whether this database supports invoking user-defined or
3738 * vendor functions using the stored procedure escape syntax.
3739 *
3740 * @return true if so; false otherwise
3741 */
3742 @Override
3743 public boolean supportsStoredFunctionsUsingCallSyntax() {
3744 return false;
3745 }
3746
3747 /**
3748 * Retrieves whether a SQLException while autoCommit is true
3749 * inidcates that all open ResultSets are closed, even ones that are
3750 * holdable. When a SQLException occurs while autocommit is true, it
3751 * is vendor specific whether the JDBC driver responds with a commit
3752 * operation, a rollback operation, or by doing neither a commit nor
3753 * a rollback. A potential result of this difference is in whether
3754 * or not holdable ResultSets are closed.
3755 *
3756 * @return true if so; false otherwise
3757 */
3758 @Override
3759 public boolean autoCommitFailureClosesAllResultSets() {
3760 // The driver caches most of it, and as far as I knoww the
3761 // server doesn't close outstanding result handles on commit
3762 // failure either.
3763 return false;
3764 }
3765
3766 /**
3767 * Retrieves a list of the client info properties that the driver
3768 * supports. The result set contains the following columns
3769 *
3770 * 1. NAME String=> The name of the client info property
3771 * 2. MAX_LEN int=> The maximum length of the value for the
3772 * property
3773 * 3. DEFAULT_VALUE String=> The default value of the
3774 * property
3775 * 4. DESCRIPTION String=> A description of the
3776 * property. This will typically contain information as
3777 * to where this property is stored in the database.
3778 *
3779 * The ResultSet is sorted by the NAME column
3780 *
3781 * @return A ResultSet object; each row is a supported client info
3782 * property, none in case of MonetDB's current JDBC driver
3783 * @throws SQLException if a database access error occurs
3784 */
3785 @Override
3786 public ResultSet getClientInfoProperties() throws SQLException {
3787 // for a list of connection properties see also MonetConnection.java constructor MonetConnection(Properties props)
3788 String query =
3789 "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 " +
3790 "SELECT 'port', 5, '50000', 'communication port number of MonetDB server process' UNION ALL " +
3791 "SELECT 'user', 128, '', 'user name to login to MonetDB server' UNION ALL " +
3792 "SELECT 'password', 128, '', 'password for user name to login to MonetDB server' UNION ALL " +
3793 "SELECT 'language', 16, 'sql', 'language (sql or mal) used to parse commands in MonetDB server' UNION ALL " +
3794 "SELECT 'debug', 5, 'false', 'boolean flag true or false' UNION ALL " +
3795 "SELECT 'hash', 128, '', 'hash string' UNION ALL " +
3796 "SELECT 'treat_blob_as_binary', 5, 'false', 'boolean flag true or false' UNION ALL " +
3797 "SELECT 'so_timeout', 10, '0', 'timeout of communication socket. 0 means no timeout is set' " +
3798 "ORDER BY \"NAME\"";
3799
3800 return executeMetaDataQuery(query);
3801 }
3802
3803 /**
3804 * Retrieves a description of the system and user functions
3805 * available in the given catalog.
3806 *
3807 * Only system and user function descriptions matching the schema
3808 * and function name criteria are returned. They are ordered by
3809 * FUNCTION_CAT, FUNCTION_SCHEM, FUNCTION_NAME and SPECIFIC_ NAME.
3810 *
3811 * Each function description has the the following columns:
3812 *
3813 * 1. FUNCTION_CAT String => function catalog (may be null)
3814 * 2. FUNCTION_SCHEM String => function schema (may be null)
3815 * 3. FUNCTION_NAME String => function name. This is the
3816 * name used to invoke the function
3817 * 4. REMARKS String => explanatory comment on the function
3818 * 5. FUNCTION_TYPE short => kind of function:
3819 * * functionResultUnknown - Cannot determine if a return
3820 * value or table will be returned
3821 * * functionNoTable- Does not return a table
3822 * * functionReturnsTable - Returns a table
3823 * 6. SPECIFIC_NAME String => the name which uniquely identifies
3824 * this function within its schema. This is a user specified,
3825 * or DBMS generated, name that may be different then the
3826 * FUNCTION_NAME for example with overload functions
3827 *
3828 * A user may not have permission to execute any of the functions
3829 * that are returned by getFunctions.
3830 *
3831 * @param catalog a catalog name; must match the catalog name as it
3832 * is stored in the database; "" retrieves those without a
3833 * catalog; null means that the catalog name should not be
3834 * used to narrow the search
3835 * @param schemaPattern a schema name pattern; must match the schema
3836 * name as it is stored in the database; "" retrieves those
3837 * without a schema; null means that the schema name should
3838 * not be used to narrow the search
3839 * @param functionNamePattern a function name pattern; must match
3840 * the function name as it is stored in the database
3841 * @return ResultSet - each row is a function description
3842 * @throws SQLException if a database access error occurs
3843 */
3844 @Override
3845 public ResultSet getFunctions(
3846 String catalog,
3847 String schemaPattern,
3848 String functionNamePattern)
3849 throws SQLException
3850 {
3851 StringBuilder query = new StringBuilder(800);
3852 query.append("SELECT cast(null as varchar(1)) AS \"FUNCTION_CAT\", " +
3853 "\"schemas\".\"name\" AS \"FUNCTION_SCHEM\", " +
3854 "\"functions\".\"name\" AS \"FUNCTION_NAME\", " +
3855 "cast(\"functions\".\"func\" as varchar(9999)) AS \"REMARKS\", " +
3856 "CASE \"functions\".\"type\"" +
3857 " WHEN 1 THEN ").append(DatabaseMetaData.functionNoTable)
3858 .append(" WHEN 2 THEN ").append(DatabaseMetaData.functionNoTable)
3859 .append(" WHEN 3 THEN ").append(DatabaseMetaData.functionNoTable)
3860 .append(" WHEN 4 THEN ").append(DatabaseMetaData.functionNoTable)
3861 .append(" WHEN 5 THEN ").append(DatabaseMetaData.functionReturnsTable)
3862 .append(" ELSE ").append(DatabaseMetaData.functionResultUnknown).append(" END AS \"FUNCTION_TYPE\", " +
3863 // only the id value uniquely identifies a function. Include it to be able to differentiate between multiple overloaded functions with the same name
3864 "cast(\"functions\".\"id\" as varchar(10)) AS \"SPECIFIC_NAME\" " +
3865 "FROM \"sys\".\"functions\", \"sys\".\"schemas\" " +
3866 "WHERE \"functions\".\"schema_id\" = \"schemas\".\"id\" " +
3867 // exclude procedures (type = 2). Those need to be returned via getProcedures()
3868 "AND \"functions\".\"type\" <> 2");
3869
3870 if (catalog != null && catalog.length() > 0) {
3871 // none empty catalog selection.
3872 // as we do not support catalogs this always results in no rows returned
3873 query.append(" AND 1 = 0");
3874 }
3875 if (schemaPattern != null) {
3876 query.append(" AND \"schemas\".\"name\" ").append(composeMatchPart(schemaPattern));
3877 }
3878 if (functionNamePattern != null) {
3879 query.append(" AND \"functions\".\"name\" ").append(composeMatchPart(functionNamePattern));
3880 }
3881
3882 query.append(" ORDER BY \"FUNCTION_SCHEM\", \"FUNCTION_NAME\", \"SPECIFIC_NAME\"");
3883
3884 return executeMetaDataQuery(query.toString());
3885 }
3886
3887 /**
3888 * Retrieves a description of the given catalog's system or user
3889 * function parameters and return type.
3890 *
3891 * Only descriptions matching the schema, function and parameter name criteria are returned.
3892 * They are ordered by FUNCTION_CAT, FUNCTION_SCHEM, FUNCTION_NAME and SPECIFIC_ NAME.
3893 * Within this, the return value, if any, is first. Next are the parameter descriptions in call order.
3894 * The column descriptions follow in column number order.
3895 *
3896 * 1. FUNCTION_CAT String => function catalog (may be null)
3897 * 2. FUNCTION_SCHEM String => function schema (may be null)
3898 * 3. FUNCTION_NAME String => function name. This is the name used to invoke the function
3899 * 4. COLUMN_NAME String => column/parameter name
3900 * 5. COLUMN_TYPE Short => kind of column/parameter:
3901 * functionColumnUnknown - nobody knows
3902 * functionColumnIn - IN parameter
3903 * functionColumnInOut - INOUT parameter
3904 * functionColumnOut - OUT parameter
3905 * functionColumnReturn - function return value
3906 * functionColumnResult - Indicates that the parameter or column is a column in the ResultSet
3907 * 6. DATA_TYPE int => SQL type from java.sql.Types
3908 * 7. TYPE_NAME String => SQL type name, for a UDT type the type name is fully qualified
3909 * 8. PRECISION int => precision
3910 * 9. LENGTH int => length in bytes of data
3911 * 10. SCALE short => scale - null is returned for data types where SCALE is not applicable.
3912 * 11. RADIX short => radix
3913 * 12. NULLABLE short => can it contain NULL.
3914 * functionNoNulls - does not allow NULL values
3915 * functionNullable - allows NULL values
3916 * functionNullableUnknown - nullability unknown
3917 * 13. REMARKS String => comment describing column/parameter
3918 * 14. CHAR_OCTET_LENGTH int => the maximum length of binary and character based parameters or columns. For any other datatype the returned value is a NULL
3919 * 15. ORDINAL_POSITION int => the ordinal position, starting from 1, for the input and output parameters.
3920 * A value of 0 is returned if this row describes the function's return value. For result set columns, it is the ordinal position of the column in the result set starting from 1.
3921 * 16. IS_NULLABLE String => ISO rules are used to determine the nullability for a parameter or column.
3922 * YES --- if the parameter or column can include NULLs
3923 * NO --- if the parameter or column cannot include NULLs
3924 * empty string --- if the nullability for the parameter or column is unknown
3925 * 17. SPECIFIC_NAME String => the name which uniquely identifies this function within its schema.
3926 * This is a user specified, or DBMS generated, name that may be different then the FUNCTION_NAME for example with overload functions
3927 *
3928 * @param catalog a catalog name; must match the catalog name as
3929 * it is stored in the database; "" retrieves those without a
3930 * catalog; null means that the catalog name should not be
3931 * used to narrow the search
3932 * @param schemaPattern a schema name pattern; must match the schema
3933 * name as it is stored in the database; "" retrieves those
3934 * without a schema; null means that the schema name should
3935 * not be used to narrow the search
3936 * @param functionNamePattern a procedure name pattern; must match the
3937 * function name as it is stored in the database
3938 * @param columnNamePattern a parameter name pattern; must match the
3939 * parameter or column name as it is stored in the database
3940 * @return ResultSet - each row describes a user function parameter,
3941 * column or return type
3942 * @throws SQLException - if a database access error occurs
3943 */
3944 @Override
3945 public ResultSet getFunctionColumns(
3946 String catalog,
3947 String schemaPattern,
3948 String functionNamePattern,
3949 String columnNamePattern)
3950 throws SQLException
3951 {
3952 StringBuilder query = new StringBuilder(2600);
3953 query.append("SELECT DISTINCT CAST(null as char(1)) AS \"FUNCTION_CAT\", " +
3954 "\"schemas\".\"name\" AS \"FUNCTION_SCHEM\", " +
3955 "\"functions\".\"name\" AS \"FUNCTION_NAME\", " +
3956 "\"args\".\"name\" AS \"COLUMN_NAME\", " +
3957 "CAST(CASE \"args\".\"inout\"" +
3958 " WHEN 0 THEN (CASE \"args\".\"number\" WHEN 0 THEN ")
3959 .append(DatabaseMetaData.functionReturn).append(" ELSE ").append(DatabaseMetaData.functionColumnOut).append(" END)" +
3960 " WHEN 1 THEN ").append(DatabaseMetaData.functionColumnIn)
3961 .append(" ELSE ").append(DatabaseMetaData.functionColumnUnknown).append(" END AS smallint) AS \"COLUMN_TYPE\", " +
3962 "CAST(").append(MonetDriver.getSQLTypeMap("\"args\".\"type\"")).append(" AS int) AS \"DATA_TYPE\", " +
3963 "\"args\".\"type\" AS \"TYPE_NAME\", " +
3964 "CASE \"args\".\"type\" WHEN 'tinyint' THEN 3 WHEN 'smallint' THEN 5 WHEN 'int' THEN 10 WHEN 'bigint' THEN 19" +
3965 " WHEN 'hugeint' THEN 38 WHEN 'oid' THEN 19 WHEN 'wrd' THEN 19 ELSE \"args\".\"type_digits\" END AS \"PRECISION\", " +
3966 "CASE \"args\".\"type\" WHEN 'tinyint' THEN 1 WHEN 'smallint' THEN 2 WHEN 'int' THEN 4 WHEN 'bigint' THEN 8" +
3967 " WHEN 'hugeint' THEN 16 WHEN 'oid' THEN 8 WHEN 'wrd' THEN 8 ELSE \"args\".\"type_digits\" END AS \"LENGTH\", " +
3968 "CAST(CASE WHEN \"args\".\"type\" IN ('tinyint','smallint','int','bigint','hugeint','oid','wrd','decimal','numeric'," +
3969 "'time','timetz','timestamp','timestamptz','sec_interval') THEN \"args\".\"type_scale\" ELSE NULL END AS smallint) AS \"SCALE\", " +
3970 "CAST(CASE WHEN \"args\".\"type\" IN ('tinyint','smallint','int','bigint','hugeint','oid','wrd','decimal','numeric') THEN 10" +
3971 " WHEN \"args\".\"type\" IN ('real','float','double') THEN 2 ELSE NULL END AS smallint) AS \"RADIX\", " +
3972 "CAST(").append(DatabaseMetaData.functionNullableUnknown).append(" AS smallint) AS \"NULLABLE\", " +
3973 "CAST(null as char(1)) AS \"REMARKS\", " +
3974 "CAST(CASE WHEN \"args\".\"type\" IN ('char','varchar','clob') THEN \"args\".\"type_digits\" ELSE NULL END as int) AS \"CHAR_OCTET_LENGTH\", " +
3975 "CAST(\"args\".\"number\" as int) AS \"ORDINAL_POSITION\", " +
3976 "CAST('' as varchar(3)) AS \"IS_NULLABLE\", " +
3977 // the specific name contains the function id, in order to be able to match the args to the correct overloaded function name
3978 "cast(\"functions\".\"id\" as varchar(10)) AS \"SPECIFIC_NAME\" " +
3979 "FROM \"sys\".\"args\", \"sys\".\"functions\", \"sys\".\"schemas\" " +
3980 "WHERE \"args\".\"func_id\" = \"functions\".\"id\" " +
3981 "AND \"functions\".\"schema_id\" = \"schemas\".\"id\" " +
3982 // exclude procedures (type = 2). Those need to be returned via getProcedureColumns()
3983 "AND \"functions\".\"type\" <> 2");
3984
3985 if (catalog != null && catalog.length() > 0) {
3986 // none empty catalog selection.
3987 // as we do not support catalogs this always results in no rows returned
3988 query.append(" AND 1 = 0");
3989 }
3990 if (schemaPattern != null) {
3991 query.append(" AND \"schemas\".\"name\" ").append(composeMatchPart(schemaPattern));
3992 }
3993 if (functionNamePattern != null) {
3994 query.append(" AND \"functions\".\"name\" ").append(composeMatchPart(functionNamePattern));
3995 }
3996 if (columnNamePattern != null) {
3997 query.append(" AND \"args\".\"name\" ").append(composeMatchPart(columnNamePattern));
3998 }
3999 query.append(" ORDER BY \"FUNCTION_SCHEM\", \"FUNCTION_NAME\", \"SPECIFIC_NAME\", \"ORDINAL_POSITION\"");
4000
4001 return executeMetaDataQuery(query.toString());
4002 }
4003
4004 //== 1.7 methods (JDBC 4.1)
4005
4006 /**
4007 * Retrieves a description of the pseudo or hidden columns available
4008 * in a given table within the specified catalog and schema. Pseudo
4009 * or hidden columns may not always be stored within a table and are
4010 * not visible in a ResultSet unless they are specified in the
4011 * query's outermost SELECT list. Pseudo or hidden columns may not
4012 * necessarily be able to be modified.
4013 * If there are no pseudo or hidden columns, an empty ResultSet is returned.
4014 *
4015 * Only column descriptions matching the catalog, schema, table and column name criteria are returned.
4016 * They are ordered by TABLE_CAT,TABLE_SCHEM, TABLE_NAME and COLUMN_NAME.
4017 *
4018 * Each column description has the following columns:
4019 *
4020 * 1. TABLE_CAT String => table catalog (may be null)
4021 * 2. TABLE_SCHEM String => table schema (may be null)
4022 * 3. TABLE_NAME String => table name
4023 * 4. COLUMN_NAME String => column name
4024 * 5. DATA_TYPE int => SQL type from java.sql.Types
4025 * 6. COLUMN_SIZE int => column size.
4026 * 7. DECIMAL_DIGITS int => the number of fractional digits. Null is returned for data types where DECIMAL_DIGITS is not applicable.
4027 * 8. NUM_PREC_RADIX int => Radix (typically either 10 or 2)
4028 * 9. COLUMN_USAGE String => The allowed usage for the column. The value returned will correspond to the enum name returned by PseudoColumnUsage.name()
4029 * 10. REMARKS String => comment describing column (may be null)
4030 * 11. CHAR_OCTET_LENGTH int => for char types the maximum number of bytes in the column
4031 * 12. IS_NULLABLE String => ISO rules are used to determine the nullability for a column.
4032 * YES --- if the column can include NULLs
4033 * NO --- if the column cannot include NULLs
4034 * empty string --- if the nullability for the column is unknown
4035 *
4036 * @param catalog a catalog name
4037 * @param schemaPattern a schema name pattern
4038 * @param tableNamePattern a table name pattern
4039 * @param columnNamePattern a column name pattern
4040 * @return ResultSet where each row is a column description
4041 * @throws SQLException if a database access error occurs
4042 */
4043 @Override
4044 public ResultSet getPseudoColumns(
4045 String catalog,
4046 String schemaPattern,
4047 String tableNamePattern,
4048 String columnNamePattern)
4049 throws SQLException
4050 {
4051 // MonetDB currently does not support pseudo or hidden columns, so return an empty ResultSet
4052 String query =
4053 "SELECT CAST(null as char(1)) AS \"TABLE_CAT\", " +
4054 "CAST('' as varchar(1)) AS \"TABLE_SCHEM\", " +
4055 "CAST('' as varchar(1)) AS \"TABLE_NAME\", " +
4056 "CAST('' as varchar(1)) AS \"COLUMN_NAME\", " +
4057 "CAST(0 as int) AS \"DATA_TYPE\", " +
4058 "CAST(0 as int) AS \"COLUMN_SIZE\", " +
4059 "CAST(0 as int) AS \"DECIMAL_DIGITS\", " +
4060 "CAST(0 as int) AS \"NUM_PREC_RADIX\", " +
4061 "CAST('' as varchar(1)) AS \"COLUMN_USAGE\", " +
4062 "CAST(null as varchar(1)) AS \"REMARKS\", " +
4063 "CAST(0 as int) AS \"CHAR_OCTET_LENGTH\", " +
4064 "CAST('' as varchar(3)) AS \"IS_NULLABLE\" " +
4065 "WHERE 1 = 0";
4066
4067 return executeMetaDataQuery(query);
4068 }
4069
4070 /**
4071 * Retrieves whether a generated key will always be returned if the
4072 * column name(s) or index(es) specified for the auto generated key
4073 * column(s) are valid and the statement succeeds. The key that is
4074 * returned may or may not be based on the column(s) for the auto
4075 * generated key.
4076 *
4077 * @return true if so, false otherwise
4078 * @throws SQLException - if a database access error occurs
4079 */
4080 @Override
4081 public boolean generatedKeyAlwaysReturned() throws SQLException {
4082 return true;
4083 }
4084
4085 //== end methods interface DatabaseMetaData
4086 }