Mercurial > hg > monetdb-java

comparison src/main/java/nl/cwi/monetdb/jdbc/MonetDatabaseMetaData.java @ 302:1b6c514106ce

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Added table aliases and use them in all meta data SQL queries to reduce query size. Also use JOIN syntax in FROM clause instead of old way of joining in WHERE clause. This further reduces size. In getIndexInfo() no longer issue a SELECT COUNT(*) query when the schema or table name contains wildcard: %. In getIndexInfo() added missing join condition AND i.\"table_id\" = k.\"table_id\" to make sure the index and the key constraint both reference the same table and extended the output to also lists info for primary keys (which also use an internal hash index).
author Martin van Dinther <martin.van.dinther@monetdbsolutions.com>
date Thu, 15 Aug 2019 19:01:49 +0200 (2019-08-15)
parents 8cc3b51d1984
children a91168efdf27
comparison
equal deleted inserted replaced
301:59bc8bebbfe9 302:1b6c514106ce
19 19
20 /** 20 /**
21 * A DatabaseMetaData object suitable for the MonetDB database. 21 * A DatabaseMetaData object suitable for the MonetDB database.
22 * 22 *
23 * @author Fabian Groffen, Martin van Dinther 23 * @author Fabian Groffen, Martin van Dinther
24 * @version 0.7 24 * @version 0.8
25 */ 25 */
26 public class MonetDatabaseMetaData 26 public class MonetDatabaseMetaData
27 extends MonetWrapper 27 extends MonetWrapper
28 implements DatabaseMetaData 28 implements DatabaseMetaData
29 { 29 {
955 * The SQL Integrity Enhancement facility offers additional tools for referential integrity, 955 * The SQL Integrity Enhancement facility offers additional tools for referential integrity,
956 * CHECK constraint clauses, and DEFAULT clauses. Referential integrity allows specification of 956 * CHECK constraint clauses, and DEFAULT clauses. Referential integrity allows specification of
957 * primary and foreign keys with the requirement that no foreign key row may be inserted or 957 * primary and foreign keys with the requirement that no foreign key row may be inserted or
958 * updated unless a matching primary key row exists. Check clauses allow specification of 958 * updated unless a matching primary key row exists. Check clauses allow specification of
959 * inter-column constraints to be maintained by the database system. 959 * inter-column constraints to be maintained by the database system.
960 * Default clauses provide optional default values for missing data. 960 * Default clauses provide optional default values for missing data.
961 * 961 *
962 * We currently do not supprt CHECK constraints (see bug 3568) nor deferrable FK constraints. 962 * We currently do not supprt CHECK constraints (see bug 3568) nor deferrable FK constraints.
963 * 963 *
964 * @return true if so 964 * @return true if so
965 */ 965 */
1712 ) throws SQLException 1712 ) throws SQLException
1713 { 1713 {
1714 final boolean useCommentsTable = ((MonetConnection)con).commentsTableExists(); 1714 final boolean useCommentsTable = ((MonetConnection)con).commentsTableExists();
1715 final StringBuilder query = new StringBuilder(980); 1715 final StringBuilder query = new StringBuilder(980);
1716 query.append("SELECT cast(null as char(1)) AS \"PROCEDURE_CAT\", " + 1716 query.append("SELECT cast(null as char(1)) AS \"PROCEDURE_CAT\", " +
1717 "\"schemas\".\"name\" AS \"PROCEDURE_SCHEM\", " + 1717 "s.\"name\" AS \"PROCEDURE_SCHEM\", " +
1718 "\"functions\".\"name\" AS \"PROCEDURE_NAME\", " + 1718 "f.\"name\" AS \"PROCEDURE_NAME\", " +
1719 "cast(null as char(1)) AS \"Field4\", " + 1719 "cast(null as char(1)) AS \"Field4\", " +
1720 "cast(null as char(1)) AS \"Field5\", " + 1720 "cast(null as char(1)) AS \"Field5\", " +
1721 "cast(null as char(1)) AS \"Field6\", ") 1721 "cast(null as char(1)) AS \"Field6\", ")
1722 .append(useCommentsTable ? "COALESCE(\"comments\".\"remark\", cast(\"functions\".\"func\" as varchar(9999)))" : "cast(\"functions\".\"func\" as varchar(9999))").append(" AS \"REMARKS\", " + 1722 .append(useCommentsTable ? "COALESCE(cm.\"remark\", cast(f.\"func\" as varchar(9999)))" : "cast(f.\"func\" as varchar(9999))").append(" AS \"REMARKS\", " +
1723 // in MonetDB procedures have no return value by design. 1723 // in MonetDB procedures have no return value by design.
1724 "cast(").append(DatabaseMetaData.procedureNoResult).append(" AS smallint) AS \"PROCEDURE_TYPE\", " + 1724 "cast(").append(DatabaseMetaData.procedureNoResult).append(" AS smallint) AS \"PROCEDURE_TYPE\", " +
1725 // only the id value uniquely identifies a procedure. Include it to be able to differentiate between multiple overloaded procedures with the same name 1725 // only the id value uniquely identifies a procedure. Include it to be able to differentiate between multiple overloaded procedures with the same name
1726 "cast(\"functions\".\"id\" as varchar(10)) AS \"SPECIFIC_NAME\" " + 1726 "cast(f.\"id\" as varchar(10)) AS \"SPECIFIC_NAME\" " +
1727 "FROM \"sys\".\"functions\" JOIN \"sys\".\"schemas\" ON (\"functions\".\"schema_id\" = \"schemas\".\"id\") "); 1727 "FROM \"sys\".\"functions\" f JOIN \"sys\".\"schemas\" s ON (f.\"schema_id\" = s.\"id\") ");
1728 if (useCommentsTable) { 1728 if (useCommentsTable) {
1729 query.append("LEFT OUTER JOIN \"sys\".\"comments\" ON (\"functions\".\"id\" = \"comments\".\"id\") "); 1729 query.append("LEFT OUTER JOIN \"sys\".\"comments\" cm ON (f.\"id\" = cm.\"id\") ");
1730 } 1730 }
1731 // include procedures only (type = 2). Others will be returned via getFunctions() 1731 // include procedures only (type = 2). Others will be returned via getFunctions()
1732 query.append("WHERE \"functions\".\"type\" = 2"); 1732 query.append("WHERE f.\"type\" = 2");
1733 1733
1734 if (catalog != null && !catalog.isEmpty()) { 1734 if (catalog != null && !catalog.isEmpty()) {
1735 // non-empty catalog selection. 1735 // non-empty catalog selection.
1736 // as we do not support catalogs this always results in no rows returned 1736 // as we do not support catalogs this always results in no rows returned
1737 query.append(" AND 1 = 0"); 1737 query.append(" AND 1 = 0");
1738 } else { 1738 } else {
1739 if (schemaPattern != null) { 1739 if (schemaPattern != null) {
1740 query.append(" AND \"schemas\".\"name\" ").append(composeMatchPart(schemaPattern)); 1740 query.append(" AND s.\"name\" ").append(composeMatchPart(schemaPattern));
1741 } 1741 }
1742 if (procedureNamePattern != null) { 1742 if (procedureNamePattern != null) {
1743 query.append(" AND \"functions\".\"name\" ").append(composeMatchPart(procedureNamePattern)); 1743 query.append(" AND f.\"name\" ").append(composeMatchPart(procedureNamePattern));
1744 } 1744 }
1745 } 1745 }
1746 1746
1747 query.append(" ORDER BY \"PROCEDURE_SCHEM\", \"PROCEDURE_NAME\", \"SPECIFIC_NAME\""); 1747 query.append(" ORDER BY \"PROCEDURE_SCHEM\", \"PROCEDURE_NAME\", \"SPECIFIC_NAME\"");
1748 1748
1824 final String procedureNamePattern, 1824 final String procedureNamePattern,
1825 final String columnNamePattern 1825 final String columnNamePattern
1826 ) throws SQLException { 1826 ) throws SQLException {
1827 final StringBuilder query = new StringBuilder(2900); 1827 final StringBuilder query = new StringBuilder(2900);
1828 query.append("SELECT cast(null as char(1)) AS \"PROCEDURE_CAT\", " + 1828 query.append("SELECT cast(null as char(1)) AS \"PROCEDURE_CAT\", " +
1829 "\"schemas\".\"name\" AS \"PROCEDURE_SCHEM\", " + 1829 "s.\"name\" AS \"PROCEDURE_SCHEM\", " +
1830 "\"functions\".\"name\" AS \"PROCEDURE_NAME\", " + 1830 "f.\"name\" AS \"PROCEDURE_NAME\", " +
1831 "\"args\".\"name\" AS \"COLUMN_NAME\", " + 1831 "a.\"name\" AS \"COLUMN_NAME\", " +
1832 "cast(CASE \"args\".\"inout\"" + 1832 "cast(CASE a.\"inout\"" +
1833 " WHEN 0 THEN (CASE \"args\".\"number\" WHEN 0 THEN ").append(DatabaseMetaData.procedureColumnReturn).append(" ELSE ").append(DatabaseMetaData.procedureColumnOut).append(" END)" + 1833 " WHEN 0 THEN (CASE a.\"number\" WHEN 0 THEN ").append(DatabaseMetaData.procedureColumnReturn).append(" ELSE ").append(DatabaseMetaData.procedureColumnOut).append(" END)" +
1834 " WHEN 1 THEN ").append(DatabaseMetaData.procedureColumnIn) 1834 " WHEN 1 THEN ").append(DatabaseMetaData.procedureColumnIn)
1835 .append(" ELSE ").append(DatabaseMetaData.procedureColumnUnknown).append(" END AS smallint) AS \"COLUMN_TYPE\", " + 1835 .append(" ELSE ").append(DatabaseMetaData.procedureColumnUnknown).append(" END AS smallint) AS \"COLUMN_TYPE\", " +
1836 "cast(").append(MonetDriver.getSQLTypeMap("\"args\".\"type\"")).append(" AS int) AS \"DATA_TYPE\", " + 1836 "cast(").append(MonetDriver.getSQLTypeMap("a.\"type\"")).append(" AS int) AS \"DATA_TYPE\", " +
1837 "\"args\".\"type\" AS \"TYPE_NAME\", " + 1837 "a.\"type\" AS \"TYPE_NAME\", " +
1838 "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\", " + 1838 "CASE a.\"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 a.\"type_digits\" END AS \"PRECISION\", " +
1839 "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\", " + 1839 "CASE a.\"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 a.\"type_digits\" END AS \"LENGTH\", " +
1840 "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\", " + 1840 "cast(CASE WHEN a.\"type\" IN ('tinyint','smallint','int','bigint','hugeint','oid','wrd','decimal','numeric','time','timetz','timestamp','timestamptz','sec_interval') THEN a.\"type_scale\" ELSE NULL END AS smallint) AS \"SCALE\", " +
1841 "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\", " + 1841 "cast(CASE WHEN a.\"type\" IN ('tinyint','smallint','int','bigint','hugeint','oid','wrd','decimal','numeric') THEN 10 WHEN a.\"type\" IN ('real','float','double') THEN 2 ELSE NULL END AS smallint) AS \"RADIX\", " +
1842 "cast(").append(DatabaseMetaData.procedureNullableUnknown).append(" AS smallint) AS \"NULLABLE\", " + 1842 "cast(").append(DatabaseMetaData.procedureNullableUnknown).append(" AS smallint) AS \"NULLABLE\", " +
1843 "cast(null as char(1)) AS \"REMARKS\", " + 1843 "cast(null as char(1)) AS \"REMARKS\", " +
1844 "cast(null as char(1)) AS \"COLUMN_DEF\", " + 1844 "cast(null as char(1)) AS \"COLUMN_DEF\", " +
1845 "cast(0 as int) AS \"SQL_DATA_TYPE\", " + 1845 "cast(0 as int) AS \"SQL_DATA_TYPE\", " +
1846 "cast(0 as int) AS \"SQL_DATETIME_SUB\", " + 1846 "cast(0 as int) AS \"SQL_DATETIME_SUB\", " +
1847 "cast(CASE WHEN \"args\".\"type\" IN ('char','varchar','clob') THEN \"args\".\"type_digits\" ELSE NULL END as int) AS \"CHAR_OCTET_LENGTH\", " + 1847 "cast(CASE WHEN a.\"type\" IN ('char','varchar','clob') THEN a.\"type_digits\" ELSE NULL END as int) AS \"CHAR_OCTET_LENGTH\", " +
1848 // 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. 1848 // 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.
1849 "cast(\"args\".\"number\" + 1 as int) AS \"ORDINAL_POSITION\", " + 1849 "cast(a.\"number\" + 1 as int) AS \"ORDINAL_POSITION\", " +
1850 "cast('' as varchar(3)) AS \"IS_NULLABLE\", " + 1850 "cast('' as varchar(3)) AS \"IS_NULLABLE\", " +
1851 // the specific name contains the function id, in order to be able to match the args to the correct overloaded procedure name 1851 // the specific name contains the function id, in order to be able to match the args to the correct overloaded procedure name
1852 "cast(\"functions\".\"id\" as varchar(10)) AS \"SPECIFIC_NAME\" " + 1852 "cast(f.\"id\" as varchar(10)) AS \"SPECIFIC_NAME\" " +
1853 "FROM \"sys\".\"args\" " + 1853 "FROM \"sys\".\"args\" a " +
1854 "JOIN \"sys\".\"functions\" ON (\"args\".\"func_id\" = \"functions\".\"id\") " + 1854 "JOIN \"sys\".\"functions\" f ON (a.\"func_id\" = f.\"id\") " +
1855 "JOIN \"sys\".\"schemas\" ON (\"functions\".\"schema_id\" = \"schemas\".\"id\") " + 1855 "JOIN \"sys\".\"schemas\" s ON (f.\"schema_id\" = s.\"id\") " +
1856 // include procedures only (type = 2). Others will be returned via getFunctionColumns() 1856 // include procedures only (type = 2). Others will be returned via getFunctionColumns()
1857 "WHERE \"functions\".\"type\" = 2"); 1857 "WHERE f.\"type\" = 2");
1858 1858
1859 if (catalog != null && !catalog.isEmpty()) { 1859 if (catalog != null && !catalog.isEmpty()) {
1860 // non-empty catalog selection. 1860 // non-empty catalog selection.
1861 // as we do not support catalogs this always results in no rows returned 1861 // as we do not support catalogs this always results in no rows returned
1862 query.append(" AND 1 = 0"); 1862 query.append(" AND 1 = 0");
1863 } else { 1863 } else {
1864 if (schemaPattern != null) { 1864 if (schemaPattern != null) {
1865 query.append(" AND \"schemas\".\"name\" ").append(composeMatchPart(schemaPattern)); 1865 query.append(" AND s.\"name\" ").append(composeMatchPart(schemaPattern));
1866 } 1866 }
1867 if (procedureNamePattern != null) { 1867 if (procedureNamePattern != null) {
1868 query.append(" AND \"functions\".\"name\" ").append(composeMatchPart(procedureNamePattern)); 1868 query.append(" AND f.\"name\" ").append(composeMatchPart(procedureNamePattern));
1869 } 1869 }
1870 if (columnNamePattern != null) { 1870 if (columnNamePattern != null) {
1871 query.append(" AND \"args\".\"name\" ").append(composeMatchPart(columnNamePattern)); 1871 query.append(" AND a.\"name\" ").append(composeMatchPart(columnNamePattern));
1872 } 1872 }
1873 } 1873 }
1874 1874
1875 query.append(" ORDER BY \"PROCEDURE_SCHEM\", \"PROCEDURE_NAME\", \"SPECIFIC_NAME\", \"ORDINAL_POSITION\""); 1875 query.append(" ORDER BY \"PROCEDURE_SCHEM\", \"PROCEDURE_NAME\", \"SPECIFIC_NAME\", \"ORDINAL_POSITION\"");
1876 1876
1971 if (preJul2015 && types != null && types.length > 0) { 1971 if (preJul2015 && types != null && types.length > 0) {
1972 // we need to filter on the constructed "TABLE_TYPE" expression, this is only possible when we use a subquery in the FROM 1972 // we need to filter on the constructed "TABLE_TYPE" expression, this is only possible when we use a subquery in the FROM
1973 query.append("SELECT * FROM ("); 1973 query.append("SELECT * FROM (");
1974 } 1974 }
1975 query.append("SELECT cast(null as char(1)) AS \"TABLE_CAT\", " + 1975 query.append("SELECT cast(null as char(1)) AS \"TABLE_CAT\", " +
1976 "\"schemas\".\"name\" AS \"TABLE_SCHEM\", " + 1976 "s.\"name\" AS \"TABLE_SCHEM\", " +
1977 "\"tables\".\"name\" AS \"TABLE_NAME\", "); 1977 "t.\"name\" AS \"TABLE_NAME\", ");
1978 if (preJul2015) { 1978 if (preJul2015) {
1979 query.append( 1979 query.append(
1980 "CASE WHEN \"tables\".\"system\" = true AND \"tables\".\"type\" IN (0, 10) AND \"tables\".\"temporary\" = 0 THEN 'SYSTEM TABLE' " + 1980 "CASE WHEN t.\"system\" = true AND t.\"type\" IN (0, 10) AND t.\"temporary\" = 0 THEN 'SYSTEM TABLE' " +
1981 "WHEN \"tables\".\"system\" = true AND \"tables\".\"type\" IN (1, 11) AND \"tables\".\"temporary\" = 0 THEN 'SYSTEM VIEW' " + 1981 "WHEN t.\"system\" = true AND t.\"type\" IN (1, 11) AND t.\"temporary\" = 0 THEN 'SYSTEM VIEW' " +
1982 "WHEN \"tables\".\"system\" = false AND \"tables\".\"type\" = 0 AND \"tables\".\"temporary\" = 0 THEN 'TABLE' " + 1982 "WHEN t.\"system\" = false AND t.\"type\" = 0 AND t.\"temporary\" = 0 THEN 'TABLE' " +
1983 "WHEN \"tables\".\"system\" = false AND \"tables\".\"type\" = 1 AND \"tables\".\"temporary\" = 0 THEN 'VIEW' " + 1983 "WHEN t.\"system\" = false AND t.\"type\" = 1 AND t.\"temporary\" = 0 THEN 'VIEW' " +
1984 "WHEN \"tables\".\"system\" = true AND \"tables\".\"type\" IN (0, 20) AND \"tables\".\"temporary\" = 1 THEN 'SYSTEM SESSION TABLE' " + 1984 "WHEN t.\"system\" = true AND t.\"type\" IN (0, 20) AND t.\"temporary\" = 1 THEN 'SYSTEM SESSION TABLE' " +
1985 "WHEN \"tables\".\"system\" = true AND \"tables\".\"type\" IN (1, 21) AND \"tables\".\"temporary\" = 1 THEN 'SYSTEM SESSION VIEW' " + 1985 "WHEN t.\"system\" = true AND t.\"type\" IN (1, 21) AND t.\"temporary\" = 1 THEN 'SYSTEM SESSION VIEW' " +
1986 "WHEN \"tables\".\"system\" = false AND \"tables\".\"type\" IN (0, 30) AND \"tables\".\"temporary\" = 1 THEN 'SESSION TABLE' " + 1986 "WHEN t.\"system\" = false AND t.\"type\" IN (0, 30) AND t.\"temporary\" = 1 THEN 'SESSION TABLE' " +
1987 "WHEN \"tables\".\"system\" = false AND \"tables\".\"type\" IN (1, 31) AND \"tables\".\"temporary\" = 1 THEN 'SESSION VIEW' " + 1987 "WHEN t.\"system\" = false AND t.\"type\" IN (1, 31) AND t.\"temporary\" = 1 THEN 'SESSION VIEW' " +
1988 "END AS \"TABLE_TYPE\", "); 1988 "END AS \"TABLE_TYPE\", ");
1989 } else { 1989 } else {
1990 query.append("\"table_types\".\"table_type_name\" AS \"TABLE_TYPE\", "); 1990 query.append("tt.\"table_type_name\" AS \"TABLE_TYPE\", ");
1991 } 1991 }
1992 query.append(useCommentsTable ? "COALESCE(\"comments\".\"remark\", \"tables\".\"query\")" : "\"tables\".\"query\"").append(" AS \"REMARKS\", " + 1992 query.append(useCommentsTable ? "COALESCE(cm.\"remark\", t.\"query\")" : "t.\"query\"").append(" AS \"REMARKS\", " +
1993 "cast(null as char(1)) AS \"TYPE_CAT\", " + 1993 "cast(null as char(1)) AS \"TYPE_CAT\", " +
1994 "cast(null as char(1)) AS \"TYPE_SCHEM\", " + 1994 "cast(null as char(1)) AS \"TYPE_SCHEM\", " +
1995 "cast(null as char(1)) AS \"TYPE_NAME\", " + 1995 "cast(null as char(1)) AS \"TYPE_NAME\", " +
1996 "cast(null as char(1)) AS \"SELF_REFERENCING_COL_NAME\", " + 1996 "cast(null as char(1)) AS \"SELF_REFERENCING_COL_NAME\", " +
1997 "cast(null as char(1)) AS \"REF_GENERATION\" " + 1997 "cast(null as char(1)) AS \"REF_GENERATION\" " +
1998 "FROM \"sys\".\"tables\""); 1998 "FROM \"sys\".\"tables\" t ");
1999 if (!preJul2015) {
2000 query.append("JOIN \"sys\".\"table_types\" tt ON t.\"type\" = tt.\"table_type_id\" ");
2001 }
2002 query.append("JOIN \"sys\".\"schemas\" s ON t.\"schema_id\" = s.\"id\" ");
1999 if (useCommentsTable) { 2003 if (useCommentsTable) {
2000 query.append(" LEFT OUTER JOIN \"sys\".\"comments\" ON (\"tables\".\"id\" = \"comments\".\"id\")"); 2004 query.append("LEFT OUTER JOIN \"sys\".\"comments\" cm ON t.\"id\" = cm.\"id\" ");
2001 } 2005 }
2002 query.append(", \"sys\".\"schemas\""); 2006 query.append("WHERE 1 = 1");
2003 if (!preJul2015) {
2004 query.append(", \"sys\".\"table_types\"");
2005 }
2006 query.append(" WHERE \"tables\".\"schema_id\" = \"schemas\".\"id\"");
2007 if (!preJul2015) {
2008 query.append(" AND \"tables\".\"type\" = \"table_types\".\"table_type_id\"");
2009 }
2010 2007
2011 if (catalog != null && !catalog.isEmpty()) { 2008 if (catalog != null && !catalog.isEmpty()) {
2012 // non-empty catalog selection. 2009 // non-empty catalog selection.
2013 // as we do not support catalogs this always results in no rows returned 2010 // as we do not support catalogs this always results in no rows returned
2014 query.append(" AND 1 = 0"); 2011 query.append(" AND 1 = 0");
2015 } else { 2012 } else {
2016 if (schemaPattern != null) { 2013 if (schemaPattern != null) {
2017 query.append(" AND \"schemas\".\"name\" ").append(composeMatchPart(schemaPattern)); 2014 query.append(" AND s.\"name\" ").append(composeMatchPart(schemaPattern));
2018 } 2015 }
2019 if (tableNamePattern != null) { 2016 if (tableNamePattern != null) {
2020 query.append(" AND \"tables\".\"name\" ").append(composeMatchPart(tableNamePattern)); 2017 query.append(" AND t.\"name\" ").append(composeMatchPart(tableNamePattern));
2021 } 2018 }
2022 } 2019 }
2023 2020
2024 if (types != null && types.length > 0) { 2021 if (types != null && types.length > 0) {
2025 if (preJul2015) { 2022 if (preJul2015) {
2026 query.append(") AS \"getTables\" WHERE \"TABLE_TYPE\" IN ("); 2023 query.append(") AS \"getTables\" WHERE \"TABLE_TYPE\" IN (");
2027 } else { 2024 } else {
2028 query.append(" AND \"table_types\".\"table_type_name\" IN ("); 2025 query.append(" AND tt.\"table_type_name\" IN (");
2029 } 2026 }
2030 for (int i = 0; i < types.length; i++) { 2027 for (int i = 0; i < types.length; i++) {
2031 if (i > 0) { 2028 if (i > 0) {
2032 query.append(", "); 2029 query.append(", ");
2033 } 2030 }
2220 ) throws SQLException 2217 ) throws SQLException
2221 { 2218 {
2222 final boolean useCommentsTable = ((MonetConnection)con).commentsTableExists(); 2219 final boolean useCommentsTable = ((MonetConnection)con).commentsTableExists();
2223 final StringBuilder query = new StringBuilder(2450); 2220 final StringBuilder query = new StringBuilder(2450);
2224 query.append("SELECT cast(null as char(1)) AS \"TABLE_CAT\", " + 2221 query.append("SELECT cast(null as char(1)) AS \"TABLE_CAT\", " +
2225 "\"schemas\".\"name\" AS \"TABLE_SCHEM\", " + 2222 "s.\"name\" AS \"TABLE_SCHEM\", " +
2226 "\"tables\".\"name\" AS \"TABLE_NAME\", " + 2223 "t.\"name\" AS \"TABLE_NAME\", " +
2227 "\"columns\".\"name\" AS \"COLUMN_NAME\", " + 2224 "c.\"name\" AS \"COLUMN_NAME\", " +
2228 "cast(").append(MonetDriver.getSQLTypeMap("\"columns\".\"type\"")).append(" AS int) AS \"DATA_TYPE\", " + 2225 "cast(").append(MonetDriver.getSQLTypeMap("c.\"type\"")).append(" AS int) AS \"DATA_TYPE\", " +
2229 "\"columns\".\"type\" AS \"TYPE_NAME\", " + 2226 "c.\"type\" AS \"TYPE_NAME\", " +
2230 "\"columns\".\"type_digits\" AS \"COLUMN_SIZE\", " + 2227 "c.\"type_digits\" AS \"COLUMN_SIZE\", " +
2231 "0 AS \"BUFFER_LENGTH\", " + 2228 "0 AS \"BUFFER_LENGTH\", " +
2232 "\"columns\".\"type_scale\" AS \"DECIMAL_DIGITS\", " + 2229 "c.\"type_scale\" AS \"DECIMAL_DIGITS\", " +
2233 "cast(CASE WHEN \"columns\".\"type\" IN ('decimal', 'numeric', 'sec_interval') THEN 10 " + 2230 "cast(CASE WHEN c.\"type\" IN ('decimal', 'numeric', 'sec_interval') THEN 10 " +
2234 "WHEN \"columns\".\"type\" IN ('int', 'smallint', 'tinyint', 'bigint', 'hugeint', 'float', 'real', 'double', 'oid', 'wrd') THEN 2 " + 2231 "WHEN c.\"type\" IN ('int', 'smallint', 'tinyint', 'bigint', 'hugeint', 'float', 'real', 'double', 'oid', 'wrd') THEN 2 " +
2235 "ELSE 0 END AS int) AS \"NUM_PREC_RADIX\", " + 2232 "ELSE 0 END AS int) AS \"NUM_PREC_RADIX\", " +
2236 "cast(CASE \"null\" WHEN true THEN ").append(ResultSetMetaData.columnNullable) 2233 "cast(CASE c.\"null\" WHEN true THEN ").append(ResultSetMetaData.columnNullable)
2237 .append(" WHEN false THEN ").append(ResultSetMetaData.columnNoNulls).append(" END AS int) AS \"NULLABLE\", ") 2234 .append(" WHEN false THEN ").append(ResultSetMetaData.columnNoNulls).append(" END AS int) AS \"NULLABLE\", ")
2238 .append(useCommentsTable ? "\"comments\".\"remark\"" : "cast(null AS varchar(9999))").append(" AS \"REMARKS\", " + 2235 .append(useCommentsTable ? "cm.\"remark\"" : "cast(null AS varchar(9999))").append(" AS \"REMARKS\", " +
2239 "\"columns\".\"default\" AS \"COLUMN_DEF\", " + 2236 "c.\"default\" AS \"COLUMN_DEF\", " +
2240 "cast(0 as int) AS \"SQL_DATA_TYPE\", " + 2237 "cast(0 as int) AS \"SQL_DATA_TYPE\", " +
2241 "cast(0 as int) AS \"SQL_DATETIME_SUB\", " + 2238 "cast(0 as int) AS \"SQL_DATETIME_SUB\", " +
2242 "cast(CASE WHEN \"columns\".\"type\" IN ('char','varchar','clob') THEN \"columns\".\"type_digits\" ELSE NULL END as int) AS \"CHAR_OCTET_LENGTH\", " + 2239 "cast(CASE WHEN c.\"type\" IN ('char','varchar','clob') THEN c.\"type_digits\" ELSE NULL END as int) AS \"CHAR_OCTET_LENGTH\", " +
2243 "cast(\"columns\".\"number\" + 1 as int) AS \"ORDINAL_POSITION\", " + 2240 "cast(c.\"number\" + 1 as int) AS \"ORDINAL_POSITION\", " +
2244 "cast(CASE \"null\" WHEN true THEN 'YES' WHEN false THEN 'NO' ELSE '' END AS varchar(3)) AS \"IS_NULLABLE\", " + 2241 "cast(CASE c.\"null\" WHEN true THEN 'YES' WHEN false THEN 'NO' ELSE '' END AS varchar(3)) AS \"IS_NULLABLE\", " +
2245 "cast(null AS char(1)) AS \"SCOPE_CATALOG\", " + 2242 "cast(null AS char(1)) AS \"SCOPE_CATALOG\", " +
2246 "cast(null AS char(1)) AS \"SCOPE_SCHEMA\", " + 2243 "cast(null AS char(1)) AS \"SCOPE_SCHEMA\", " +
2247 "cast(null AS char(1)) AS \"SCOPE_TABLE\", " + 2244 "cast(null AS char(1)) AS \"SCOPE_TABLE\", " +
2248 "cast(null AS smallint) AS \"SOURCE_DATA_TYPE\", " + 2245 "cast(null AS smallint) AS \"SOURCE_DATA_TYPE\", " +
2249 "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\", " + 2246 "cast(CASE WHEN c.\"default\" IS NOT NULL AND c.\"default\" LIKE 'next value for %' THEN 'YES' ELSE 'NO' END AS varchar(3)) AS \"IS_AUTOINCREMENT\", " +
2250 "cast('NO' AS varchar(3)) AS \"IS_GENERATEDCOLUMN\" " + 2247 "cast('NO' AS varchar(3)) AS \"IS_GENERATEDCOLUMN\" " +
2251 "FROM \"sys\".\"columns\""); 2248 "FROM \"sys\".\"columns\" c " +
2249 "JOIN \"sys\".\"tables\" t ON c.\"table_id\" = t.\"id\" " +
2250 "JOIN \"sys\".\"schemas\" s ON t.\"schema_id\" = s.\"id\" ");
2252 if (useCommentsTable) { 2251 if (useCommentsTable) {
2253 query.append(" LEFT OUTER JOIN \"sys\".\"comments\" ON (\"columns\".\"id\" = \"comments\".\"id\")"); 2252 query.append("LEFT OUTER JOIN \"sys\".\"comments\" cm ON c.\"id\" = cm.\"id\" ");
2254 } 2253 }
2255 query.append(", \"sys\".\"tables\"" + 2254 query.append("WHERE 1 = 1");
2256 ", \"sys\".\"schemas\" " +
2257 "WHERE \"columns\".\"table_id\" = \"tables\".\"id\"" +
2258 " AND \"tables\".\"schema_id\" = \"schemas\".\"id\"");
2259 2255
2260 if (catalog != null && !catalog.isEmpty()) { 2256 if (catalog != null && !catalog.isEmpty()) {
2261 // non-empty catalog selection. 2257 // non-empty catalog selection.
2262 // as we do not support catalogs this always results in no rows returned 2258 // as we do not support catalogs this always results in no rows returned
2263 query.append(" AND 1 = 0"); 2259 query.append(" AND 1 = 0");
2264 } else { 2260 } else {
2265 if (schemaPattern != null) { 2261 if (schemaPattern != null) {
2266 query.append(" AND \"schemas\".\"name\" ").append(composeMatchPart(schemaPattern)); 2262 query.append(" AND s.\"name\" ").append(composeMatchPart(schemaPattern));
2267 } 2263 }
2268 if (tableNamePattern != null) { 2264 if (tableNamePattern != null) {
2269 query.append(" AND \"tables\".\"name\" ").append(composeMatchPart(tableNamePattern)); 2265 query.append(" AND t.\"name\" ").append(composeMatchPart(tableNamePattern));
2270 } 2266 }
2271 if (columnNamePattern != null) { 2267 if (columnNamePattern != null) {
2272 query.append(" AND \"columns\".\"name\" ").append(composeMatchPart(columnNamePattern)); 2268 query.append(" AND c.\"name\" ").append(composeMatchPart(columnNamePattern));
2273 } 2269 }
2274 } 2270 }
2275 2271
2276 query.append(" ORDER BY \"TABLE_SCHEM\", \"TABLE_NAME\", \"ORDINAL_POSITION\""); 2272 query.append(" ORDER BY \"TABLE_SCHEM\", \"TABLE_NAME\", \"ORDINAL_POSITION\"");
2277 2273
2318 ) throws SQLException 2314 ) throws SQLException
2319 { 2315 {
2320 final boolean usePrivilege_codesTable = ((MonetConnection)con).privilege_codesTableExists(); 2316 final boolean usePrivilege_codesTable = ((MonetConnection)con).privilege_codesTableExists();
2321 final StringBuilder query = new StringBuilder(1100); 2317 final StringBuilder query = new StringBuilder(1100);
2322 query.append("SELECT cast(null as char(1)) AS \"TABLE_CAT\", " + 2318 query.append("SELECT cast(null as char(1)) AS \"TABLE_CAT\", " +
2323 "\"schemas\".\"name\" AS \"TABLE_SCHEM\", " + 2319 "s.\"name\" AS \"TABLE_SCHEM\", " +
2324 "\"tables\".\"name\" AS \"TABLE_NAME\", " + 2320 "t.\"name\" AS \"TABLE_NAME\", " +
2325 "\"columns\".\"name\" AS \"COLUMN_NAME\", " + 2321 "c.\"name\" AS \"COLUMN_NAME\", " +
2326 "\"grantors\".\"name\" AS \"GRANTOR\", " + 2322 "grantors.\"name\" AS \"GRANTOR\", " +
2327 "\"grantees\".\"name\" AS \"GRANTEE\", ") 2323 "grantees.\"name\" AS \"GRANTEE\", ")
2328 .append(usePrivilege_codesTable ? "\"privilege_codes\".\"privilege_code_name\"" : 2324 .append(usePrivilege_codesTable ? "pc.\"privilege_code_name\"" :
2329 "cast(CASE \"privileges\".\"privileges\" " + 2325 "cast(CASE p.\"privileges\" " +
2330 "WHEN 1 THEN 'SELECT' " + 2326 "WHEN 1 THEN 'SELECT' " +
2331 "WHEN 2 THEN 'UPDATE' " + 2327 "WHEN 2 THEN 'UPDATE' " +
2332 "WHEN 4 THEN 'INSERT' " + 2328 "WHEN 4 THEN 'INSERT' " +
2333 "WHEN 8 THEN 'DELETE' " + 2329 "WHEN 8 THEN 'DELETE' " +
2334 "WHEN 16 THEN 'EXECUTE' " + 2330 "WHEN 16 THEN 'EXECUTE' " +
2335 "WHEN 32 THEN 'GRANT' " + 2331 "WHEN 32 THEN 'GRANT' " +
2336 "ELSE NULL " + 2332 "ELSE NULL " +
2337 "END AS varchar(7))").append(" AS \"PRIVILEGE\", " + 2333 "END AS varchar(7))").append(" AS \"PRIVILEGE\", " +
2338 "cast(CASE \"privileges\".\"grantable\" " + 2334 "cast(CASE p.\"grantable\" " +
2339 "WHEN 0 THEN 'NO' " + 2335 "WHEN 0 THEN 'NO' " +
2340 "WHEN 1 THEN 'YES' " + 2336 "WHEN 1 THEN 'YES' " +
2341 "ELSE NULL " + 2337 "ELSE NULL " +
2342 "END AS varchar(3)) AS \"IS_GRANTABLE\" " + 2338 "END AS varchar(3)) AS \"IS_GRANTABLE\" " +
2343 "FROM \"sys\".\"privileges\", " + 2339 "FROM \"sys\".\"privileges\" p " +
2344 "\"sys\".\"tables\", " + 2340 "JOIN \"sys\".\"columns\" c ON p.\"obj_id\" = c.\"id\" " +
2345 "\"sys\".\"schemas\", " + 2341 "JOIN \"sys\".\"tables\" t ON c.\"table_id\" = t.\"id\" " +
2346 "\"sys\".\"columns\", " + 2342 "JOIN \"sys\".\"schemas\" s ON t.\"schema_id\" = s.\"id\" " +
2347 "\"sys\".\"auths\" AS \"grantors\", " + 2343 "JOIN \"sys\".\"auths\" grantors ON p.\"grantor\" = grantors.\"id\" " +
2348 "\"sys\".\"auths\" AS \"grantees\" "); 2344 "JOIN \"sys\".\"auths\" grantees ON p.\"auth_id\" = grantees.\"id\" ");
2349 if (usePrivilege_codesTable) { 2345 if (usePrivilege_codesTable) {
2350 query.append(", \"sys\".\"privilege_codes\" "); 2346 query.append("JOIN \"sys\".\"privilege_codes\" pc ON p.\"privileges\" = pc.\"privilege_code_id\" ");
2351 } 2347 }
2352 query.append("WHERE \"privileges\".\"obj_id\" = \"columns\".\"id\" " + 2348 query.append("WHERE 1 = 1");
2353 "AND \"columns\".\"table_id\" = \"tables\".\"id\" " +
2354 "AND \"tables\".\"schema_id\" = \"schemas\".\"id\" " +
2355 "AND \"privileges\".\"auth_id\" = \"grantees\".\"id\" " +
2356 "AND \"privileges\".\"grantor\" = \"grantors\".\"id\"");
2357 if (usePrivilege_codesTable) {
2358 query.append(" AND \"privileges\".\"privileges\" = \"privilege_codes\".\"privilege_code_id\"");
2359 }
2360 2349
2361 if (catalog != null && !catalog.isEmpty()) { 2350 if (catalog != null && !catalog.isEmpty()) {
2362 // non-empty catalog selection. 2351 // non-empty catalog selection.
2363 // as we do not support catalogs this always results in no rows returned 2352 // as we do not support catalogs this always results in no rows returned
2364 query.append(" AND 1 = 0"); 2353 query.append(" AND 1 = 0");
2365 } else { 2354 } else {
2366 if (schemaPattern != null) { 2355 if (schemaPattern != null) {
2367 query.append(" AND \"schemas\".\"name\" ").append(composeMatchPart(schemaPattern)); 2356 query.append(" AND s.\"name\" ").append(composeMatchPart(schemaPattern));
2368 } 2357 }
2369 if (tableNamePattern != null) { 2358 if (tableNamePattern != null) {
2370 query.append(" AND \"tables\".\"name\" ").append(composeMatchPart(tableNamePattern)); 2359 query.append(" AND t.\"name\" ").append(composeMatchPart(tableNamePattern));
2371 } 2360 }
2372 if (columnNamePattern != null) { 2361 if (columnNamePattern != null) {
2373 query.append(" AND \"columns\".\"name\" ").append(composeMatchPart(columnNamePattern)); 2362 query.append(" AND c.\"name\" ").append(composeMatchPart(columnNamePattern));
2374 } 2363 }
2375 } 2364 }
2376 2365
2377 query.append(" ORDER BY \"TABLE_SCHEM\", \"TABLE_NAME\", \"COLUMN_NAME\", \"PRIVILEGE\""); 2366 query.append(" ORDER BY \"TABLE_SCHEM\", \"TABLE_NAME\", \"COLUMN_NAME\", \"PRIVILEGE\"");
2378 2367
2416 ) throws SQLException 2405 ) throws SQLException
2417 { 2406 {
2418 final boolean usePrivilege_codesTable = ((MonetConnection)con).privilege_codesTableExists(); 2407 final boolean usePrivilege_codesTable = ((MonetConnection)con).privilege_codesTableExists();
2419 final StringBuilder query = new StringBuilder(1000); 2408 final StringBuilder query = new StringBuilder(1000);
2420 query.append("SELECT cast(null as char(1)) AS \"TABLE_CAT\", " + 2409 query.append("SELECT cast(null as char(1)) AS \"TABLE_CAT\", " +
2421 "\"schemas\".\"name\" AS \"TABLE_SCHEM\", " + 2410 "s.\"name\" AS \"TABLE_SCHEM\", " +
2422 "\"tables\".\"name\" AS \"TABLE_NAME\", " + 2411 "t.\"name\" AS \"TABLE_NAME\", " +
2423 "\"grantors\".\"name\" AS \"GRANTOR\", " + 2412 "grantors.\"name\" AS \"GRANTOR\", " +
2424 "\"grantees\".\"name\" AS \"GRANTEE\", ") 2413 "grantees.\"name\" AS \"GRANTEE\", ")
2425 .append(usePrivilege_codesTable ? "\"privilege_codes\".\"privilege_code_name\"" : 2414 .append(usePrivilege_codesTable ? "pc.\"privilege_code_name\"" :
2426 "cast(CASE \"privileges\".\"privileges\" " + 2415 "cast(CASE p.\"privileges\" " +
2427 "WHEN 1 THEN 'SELECT' " + 2416 "WHEN 1 THEN 'SELECT' " +
2428 "WHEN 2 THEN 'UPDATE' " + 2417 "WHEN 2 THEN 'UPDATE' " +
2429 "WHEN 4 THEN 'INSERT' " + 2418 "WHEN 4 THEN 'INSERT' " +
2430 "WHEN 8 THEN 'DELETE' " + 2419 "WHEN 8 THEN 'DELETE' " +
2431 "WHEN 16 THEN 'EXECUTE' " + 2420 "WHEN 16 THEN 'EXECUTE' " +
2432 "WHEN 32 THEN 'GRANT' " + 2421 "WHEN 32 THEN 'GRANT' " +
2433 "ELSE NULL " + 2422 "ELSE NULL " +
2434 "END AS varchar(7))").append(" AS \"PRIVILEGE\", " + 2423 "END AS varchar(7))").append(" AS \"PRIVILEGE\", " +
2435 "cast(CASE \"privileges\".\"grantable\" " + 2424 "cast(CASE p.\"grantable\" " +
2436 "WHEN 0 THEN 'NO' " + 2425 "WHEN 0 THEN 'NO' " +
2437 "WHEN 1 THEN 'YES' " + 2426 "WHEN 1 THEN 'YES' " +
2438 "ELSE NULL " + 2427 "ELSE NULL " +
2439 "END AS varchar(3)) AS \"IS_GRANTABLE\" " + 2428 "END AS varchar(3)) AS \"IS_GRANTABLE\" " +
2440 "FROM \"sys\".\"privileges\", " + 2429 "FROM \"sys\".\"privileges\" p " +
2441 "\"sys\".\"tables\", " + 2430 "JOIN \"sys\".\"tables\" t ON p.\"obj_id\" = t.\"id\" " +
2442 "\"sys\".\"schemas\", " + 2431 "JOIN \"sys\".\"schemas\" s ON t.\"schema_id\" = s.\"id\" " +
2443 "\"sys\".\"auths\" AS \"grantors\", " + 2432 "JOIN \"sys\".\"auths\" grantors ON p.\"grantor\" = grantors.\"id\" " +
2444 "\"sys\".\"auths\" AS \"grantees\" "); 2433 "JOIN \"sys\".\"auths\" grantees ON p.\"auth_id\" = grantees.\"id\" ");
2445 if (usePrivilege_codesTable) { 2434 if (usePrivilege_codesTable) {
2446 query.append(", \"sys\".\"privilege_codes\" "); 2435 query.append("JOIN \"sys\".\"privilege_codes\" pc ON p.\"privileges\" = pc.\"privilege_code_id\" ");
2447 } 2436 }
2448 query.append("WHERE \"privileges\".\"obj_id\" = \"tables\".\"id\" " + 2437 query.append("WHERE 1 = 1");
2449 "AND \"tables\".\"schema_id\" = \"schemas\".\"id\" " +
2450 "AND \"privileges\".\"auth_id\" = \"grantees\".\"id\" " +
2451 "AND \"privileges\".\"grantor\" = \"grantors\".\"id\"");
2452 if (usePrivilege_codesTable) {
2453 query.append(" AND \"privileges\".\"privileges\" = \"privilege_codes\".\"privilege_code_id\"");
2454 }
2455 2438
2456 if (catalog != null && !catalog.isEmpty()) { 2439 if (catalog != null && !catalog.isEmpty()) {
2457 // non-empty catalog selection. 2440 // non-empty catalog selection.
2458 // as we do not support catalogs this always results in no rows returned 2441 // as we do not support catalogs this always results in no rows returned
2459 query.append(" AND 1 = 0"); 2442 query.append(" AND 1 = 0");
2460 } else { 2443 } else {
2461 if (schemaPattern != null) { 2444 if (schemaPattern != null) {
2462 query.append(" AND \"schemas\".\"name\" ").append(composeMatchPart(schemaPattern)); 2445 query.append(" AND s.\"name\" ").append(composeMatchPart(schemaPattern));
2463 } 2446 }
2464 if (tableNamePattern != null) { 2447 if (tableNamePattern != null) {
2465 query.append(" AND \"tables\".\"name\" ").append(composeMatchPart(tableNamePattern)); 2448 query.append(" AND t.\"name\" ").append(composeMatchPart(tableNamePattern));
2466 } 2449 }
2467 } 2450 }
2468 2451
2469 query.append(" ORDER BY \"TABLE_SCHEM\", \"TABLE_NAME\", \"PRIVILEGE\""); 2452 query.append(" ORDER BY \"TABLE_SCHEM\", \"TABLE_NAME\", \"PRIVILEGE\"");
2470 2453
2516 final boolean nullable 2499 final boolean nullable
2517 ) throws SQLException 2500 ) throws SQLException
2518 { 2501 {
2519 final StringBuilder query = new StringBuilder(1500); 2502 final StringBuilder query = new StringBuilder(1500);
2520 query.append("SELECT cast(").append(DatabaseMetaData.bestRowSession).append(" AS smallint) AS \"SCOPE\", " + 2503 query.append("SELECT cast(").append(DatabaseMetaData.bestRowSession).append(" AS smallint) AS \"SCOPE\", " +
2521 "\"columns\".\"name\" AS \"COLUMN_NAME\", " + 2504 "c.\"name\" AS \"COLUMN_NAME\", " +
2522 "cast(").append(MonetDriver.getSQLTypeMap("\"columns\".\"type\"")).append(" AS int) AS \"DATA_TYPE\", " + 2505 "cast(").append(MonetDriver.getSQLTypeMap("c.\"type\"")).append(" AS int) AS \"DATA_TYPE\", " +
2523 "\"columns\".\"type\" AS \"TYPE_NAME\", " + 2506 "c.\"type\" AS \"TYPE_NAME\", " +
2524 "\"columns\".\"type_digits\" AS \"COLUMN_SIZE\", " + 2507 "c.\"type_digits\" AS \"COLUMN_SIZE\", " +
2525 "cast(0 as int) AS \"BUFFER_LENGTH\", " + 2508 "cast(0 as int) AS \"BUFFER_LENGTH\", " +
2526 "cast(\"columns\".\"type_scale\" AS smallint) AS \"DECIMAL_DIGITS\", " + 2509 "cast(c.\"type_scale\" AS smallint) AS \"DECIMAL_DIGITS\", " +
2527 "cast(").append(DatabaseMetaData.bestRowNotPseudo).append(" AS smallint) AS \"PSEUDO_COLUMN\" " + 2510 "cast(").append(DatabaseMetaData.bestRowNotPseudo).append(" AS smallint) AS \"PSEUDO_COLUMN\" " +
2528 "FROM \"sys\".\"keys\", " + 2511 "FROM \"sys\".\"keys\" k " +
2529 "\"sys\".\"objects\", " + 2512 "JOIN \"sys\".\"objects\" o ON k.\"id\" = o.\"id\" " +
2530 "\"sys\".\"columns\", " + 2513 "JOIN \"sys\".\"columns\" c ON (k.\"table_id\" = c.\"table_id\" AND o.\"name\" = c.\"name\") " +
2531 "\"sys\".\"tables\", " + 2514 "JOIN \"sys\".\"tables\" t ON k.\"table_id\" = t.\"id\" " +
2532 "\"sys\".\"schemas\" " + 2515 "JOIN \"sys\".\"schemas\" s ON t.\"schema_id\" = s.\"id\" " +
2533 "WHERE \"keys\".\"id\" = \"objects\".\"id\" " + 2516 "WHERE k.\"type\" IN (0, 1)"); // only primary keys (type = 0) and unique keys (type = 1), not fkeys (type = 2)
2534 "AND \"keys\".\"table_id\" = \"tables\".\"id\" " +
2535 "AND \"keys\".\"table_id\" = \"columns\".\"table_id\" " +
2536 "AND \"objects\".\"name\" = \"columns\".\"name\" " +
2537 "AND \"tables\".\"schema_id\" = \"schemas\".\"id\" " +
2538 "AND \"keys\".\"type\" IN (0, 1)"); // only primary keys (type = 0) and unique keys (type = 1), not fkeys (type = 2)
2539 2517
2540 if (catalog != null && !catalog.isEmpty()) { 2518 if (catalog != null && !catalog.isEmpty()) {
2541 // non-empty catalog selection. 2519 // non-empty catalog selection.
2542 // as we do not support catalogs this always results in no rows returned 2520 // as we do not support catalogs this always results in no rows returned
2543 query.append(" AND 1 = 0"); 2521 query.append(" AND 1 = 0");
2546 && scope != DatabaseMetaData.bestRowTransaction 2524 && scope != DatabaseMetaData.bestRowTransaction
2547 && scope != DatabaseMetaData.bestRowTemporary) { 2525 && scope != DatabaseMetaData.bestRowTemporary) {
2548 query.append(" AND 1 = 0"); 2526 query.append(" AND 1 = 0");
2549 } else { 2527 } else {
2550 if (schema != null) { 2528 if (schema != null) {
2551 query.append(" AND \"schemas\".\"name\" ").append(composeMatchPart(schema)); 2529 query.append(" AND s.\"name\" ").append(composeMatchPart(schema));
2552 } 2530 }
2553 if (table != null) { 2531 if (table != null) {
2554 query.append(" AND \"tables\".\"name\" ").append(composeMatchPart(table)); 2532 query.append(" AND t.\"name\" ").append(composeMatchPart(table));
2555 } 2533 }
2556 if (!nullable) { 2534 if (!nullable) {
2557 query.append(" AND \"columns\".\"null\" = false"); 2535 query.append(" AND c.\"null\" = false");
2558 } 2536 }
2559 } 2537 }
2560 } 2538 }
2561 2539
2562 query.append(" ORDER BY \"keys\".\"type\", \"columns\".\"name\""); 2540 query.append(" ORDER BY k.\"type\", c.\"name\"");
2563 2541
2564 return executeMetaDataQuery(query.toString()); 2542 return executeMetaDataQuery(query.toString());
2565 } 2543 }
2566 2544
2567 /** 2545 /**
2642 final String table 2620 final String table
2643 ) throws SQLException 2621 ) throws SQLException
2644 { 2622 {
2645 final StringBuilder query = new StringBuilder(600); 2623 final StringBuilder query = new StringBuilder(600);
2646 query.append("SELECT cast(null AS char(1)) AS \"TABLE_CAT\", " + 2624 query.append("SELECT cast(null AS char(1)) AS \"TABLE_CAT\", " +
2647 "\"schemas\".\"name\" AS \"TABLE_SCHEM\", " + 2625 "s.\"name\" AS \"TABLE_SCHEM\", " +
2648 "\"tables\".\"name\" AS \"TABLE_NAME\", " + 2626 "t.\"name\" AS \"TABLE_NAME\", " +
2649 "\"objects\".\"name\" AS \"COLUMN_NAME\", " + 2627 "o.\"name\" AS \"COLUMN_NAME\", " +
2650 "cast(1 + \"objects\".\"nr\" AS smallint) AS \"KEY_SEQ\", " + 2628 "cast(1 + o.\"nr\" AS smallint) AS \"KEY_SEQ\", " +
2651 " \"keys\".\"name\" AS \"PK_NAME\" " + 2629 " k.\"name\" AS \"PK_NAME\" " +
2652 "FROM \"sys\".\"keys\", " + 2630 "FROM \"sys\".\"keys\" k " +
2653 "\"sys\".\"objects\", " + 2631 "JOIN \"sys\".\"objects\" o ON k.\"id\" = o.\"id\" " +
2654 "\"sys\".\"tables\", " + 2632 "JOIN \"sys\".\"tables\" t ON k.\"table_id\" = t.\"id\" " +
2655 "\"sys\".\"schemas\" " + 2633 "JOIN \"sys\".\"schemas\" s ON t.\"schema_id\" = s.\"id\" " +
2656 "WHERE \"keys\".\"id\" = \"objects\".\"id\" " + 2634 "WHERE k.\"type\" = 0"); // only primary keys (type = 0)
2657 "AND \"keys\".\"table_id\" = \"tables\".\"id\" " +
2658 "AND \"tables\".\"schema_id\" = \"schemas\".\"id\" " +
2659 "AND \"keys\".\"type\" = 0");
2660 2635
2661 if (catalog != null && !catalog.isEmpty()) { 2636 if (catalog != null && !catalog.isEmpty()) {
2662 // non-empty catalog selection. 2637 // non-empty catalog selection.
2663 // as we do not support catalogs this always results in no rows returned 2638 // as we do not support catalogs this always results in no rows returned
2664 query.append(" AND 1 = 0"); 2639 query.append(" AND 1 = 0");
2665 } else { 2640 } else {
2666 if (schema != null) { 2641 if (schema != null) {
2667 query.append(" AND \"schemas\".\"name\" ").append(composeMatchPart(schema)); 2642 query.append(" AND s.\"name\" ").append(composeMatchPart(schema));
2668 } 2643 }
2669 if (table != null) { 2644 if (table != null) {
2670 query.append(" AND \"tables\".\"name\" ").append(composeMatchPart(table)); 2645 query.append(" AND t.\"name\" ").append(composeMatchPart(table));
2671 } 2646 }
2672 } 2647 }
2673 2648
2674 query.append(" ORDER BY \"TABLE_SCHEM\", \"TABLE_NAME\", \"COLUMN_NAME\""); 2649 query.append(" ORDER BY \"TABLE_SCHEM\", \"TABLE_NAME\", \"COLUMN_NAME\"");
2675 2650
2677 } 2652 }
2678 2653
2679 2654
2680 private static final String keyQuery = 2655 private static final String keyQuery =
2681 "SELECT cast(null AS char(1)) AS \"PKTABLE_CAT\", " + 2656 "SELECT cast(null AS char(1)) AS \"PKTABLE_CAT\", " +
2682 "\"pkschema\".\"name\" AS \"PKTABLE_SCHEM\", " + 2657 "pkschema.\"name\" AS \"PKTABLE_SCHEM\", " +
2683 "\"pktable\".\"name\" AS \"PKTABLE_NAME\", " + 2658 "pktable.\"name\" AS \"PKTABLE_NAME\", " +
2684 "\"pkkeycol\".\"name\" AS \"PKCOLUMN_NAME\", " + 2659 "pkkeycol.\"name\" AS \"PKCOLUMN_NAME\", " +
2685 "cast(null AS char(1)) AS \"FKTABLE_CAT\", " + 2660 "cast(null AS char(1)) AS \"FKTABLE_CAT\", " +
2686 "\"fkschema\".\"name\" AS \"FKTABLE_SCHEM\", " + 2661 "fkschema.\"name\" AS \"FKTABLE_SCHEM\", " +
2687 "\"fktable\".\"name\" AS \"FKTABLE_NAME\", " + 2662 "fktable.\"name\" AS \"FKTABLE_NAME\", " +
2688 "\"fkkeycol\".\"name\" AS \"FKCOLUMN_NAME\", " + 2663 "fkkeycol.\"name\" AS \"FKCOLUMN_NAME\", " +
2689 "cast(1 + \"pkkeycol\".\"nr\" AS smallint) AS \"KEY_SEQ\", " + 2664 "cast(1 + \"pkkeycol\".\"nr\" AS smallint) AS \"KEY_SEQ\", " +
2690 DatabaseMetaData.importedKeyNoAction + " AS \"UPDATE_RULE\", " + 2665 DatabaseMetaData.importedKeyNoAction + " AS \"UPDATE_RULE\", " +
2691 DatabaseMetaData.importedKeyNoAction + " AS \"DELETE_RULE\", " + 2666 DatabaseMetaData.importedKeyNoAction + " AS \"DELETE_RULE\", " +
2692 "\"fkkey\".\"name\" AS \"FK_NAME\", " + 2667 "fkkey.\"name\" AS \"FK_NAME\", " +
2693 "\"pkkey\".\"name\" AS \"PK_NAME\", " + 2668 "pkkey.\"name\" AS \"PK_NAME\", " +
2694 DatabaseMetaData.importedKeyNotDeferrable + " AS \"DEFERRABILITY\" " + 2669 DatabaseMetaData.importedKeyNotDeferrable + " AS \"DEFERRABILITY\" " +
2695 "FROM \"sys\".\"keys\" AS \"fkkey\", " + 2670 "FROM \"sys\".\"keys\" pkkey " +
2696 "\"sys\".\"keys\" AS \"pkkey\", " + 2671 "JOIN \"sys\".\"objects\" pkkeycol ON pkkey.\"id\" = pkkeycol.\"id\" " +
2697 "\"sys\".\"objects\" AS \"fkkeycol\", " + 2672 "JOIN \"sys\".\"tables\" pktable ON pktable.\"id\" = pkkey.\"table_id\" " +
2698 "\"sys\".\"objects\" AS \"pkkeycol\", " + 2673 "JOIN \"sys\".\"schemas\" pkschema ON pkschema.\"id\" = pktable.\"schema_id\" " +
2699 "\"sys\".\"tables\" AS \"fktable\", " + 2674 "JOIN \"sys\".\"keys\" fkkey ON fkkey.\"rkey\" = pkkey.\"id\" " +
2700 "\"sys\".\"tables\" AS \"pktable\", " + 2675 "JOIN \"sys\".\"objects\" fkkeycol ON (fkkey.\"id\" = fkkeycol.\"id\" AND fkkeycol.\"nr\" = pkkeycol.\"nr\") " +
2701 "\"sys\".\"schemas\" AS \"fkschema\", " + 2676 "JOIN \"sys\".\"tables\" fktable ON fktable.\"id\" = fkkey.\"table_id\" " +
2702 "\"sys\".\"schemas\" AS \"pkschema\" " + 2677 "JOIN \"sys\".\"schemas\" fkschema ON fkschema.\"id\" = fktable.\"schema_id\" " +
2703 "WHERE \"fktable\".\"id\" = \"fkkey\".\"table_id\"" + 2678 "WHERE fkkey.\"rkey\" > 0"; // exclude invalid key references, such as -1
2704 " AND \"pktable\".\"id\" = \"pkkey\".\"table_id\"" +
2705 " AND \"fkkey\".\"id\" = \"fkkeycol\".\"id\"" +
2706 " AND \"pkkey\".\"id\" = \"pkkeycol\".\"id\"" +
2707 " AND \"fkschema\".\"id\" = \"fktable\".\"schema_id\"" +
2708 " AND \"pkschema\".\"id\" = \"pktable\".\"schema_id\"" +
2709 " AND \"fkkey\".\"rkey\" > -1" +
2710 " AND \"fkkey\".\"rkey\" = \"pkkey\".\"id\"" +
2711 " AND \"fkkeycol\".\"nr\" = \"pkkeycol\".\"nr\"";
2712 2679
2713 /** 2680 /**
2714 * Get a description of the primary key columns that are 2681 * Get a description of the primary key columns that are
2715 * referenced by a table's foreign key columns (the primary keys 2682 * referenced by a table's foreign key columns (the primary keys
2716 * imported by a table). They are ordered by PKTABLE_CAT, 2683 * imported by a table). They are ordered by PKTABLE_CAT,
2785 // non-empty catalog selection. 2752 // non-empty catalog selection.
2786 // as we do not support catalogs this always results in no rows returned 2753 // as we do not support catalogs this always results in no rows returned
2787 query.append(" AND 1 = 0"); 2754 query.append(" AND 1 = 0");
2788 } else { 2755 } else {
2789 if (schema != null) { 2756 if (schema != null) {
2790 query.append(" AND \"fkschema\".\"name\" ").append(composeMatchPart(schema)); 2757 query.append(" AND fkschema.\"name\" ").append(composeMatchPart(schema));
2791 } 2758 }
2792 if (table != null) { 2759 if (table != null) {
2793 query.append(" AND \"fktable\".\"name\" ").append(composeMatchPart(table)); 2760 query.append(" AND fktable.\"name\" ").append(composeMatchPart(table));
2794 } 2761 }
2795 } 2762 }
2796 2763
2797 query.append(" ORDER BY \"PKTABLE_SCHEM\", \"PKTABLE_NAME\", \"PK_NAME\", \"KEY_SEQ\""); 2764 query.append(" ORDER BY \"PKTABLE_SCHEM\", \"PKTABLE_NAME\", \"PK_NAME\", \"KEY_SEQ\"");
2798 2765
2873 // non-empty catalog selection. 2840 // non-empty catalog selection.
2874 // as we do not support catalogs this always results in no rows returned 2841 // as we do not support catalogs this always results in no rows returned
2875 query.append(" AND 1 = 0"); 2842 query.append(" AND 1 = 0");
2876 } else { 2843 } else {
2877 if (schema != null) { 2844 if (schema != null) {
2878 query.append(" AND \"pkschema\".\"name\" ").append(composeMatchPart(schema)); 2845 query.append(" AND pkschema.\"name\" ").append(composeMatchPart(schema));
2879 } 2846 }
2880 if (table != null) { 2847 if (table != null) {
2881 query.append(" AND \"pktable\".\"name\" ").append(composeMatchPart(table)); 2848 query.append(" AND pktable.\"name\" ").append(composeMatchPart(table));
2882 } 2849 }
2883 } 2850 }
2884 2851
2885 query.append(" ORDER BY \"FKTABLE_SCHEM\", \"FKTABLE_NAME\", \"FK_NAME\", \"KEY_SEQ\""); 2852 query.append(" ORDER BY \"FKTABLE_SCHEM\", \"FKTABLE_NAME\", \"FK_NAME\", \"KEY_SEQ\"");
2886 2853
2972 // non-empty catalog selection. 2939 // non-empty catalog selection.
2973 // as we do not support catalogs this always results in no rows returned 2940 // as we do not support catalogs this always results in no rows returned
2974 query.append(" AND 1 = 0"); 2941 query.append(" AND 1 = 0");
2975 } else { 2942 } else {
2976 if (pschema != null) { 2943 if (pschema != null) {
2977 query.append(" AND \"pkschema\".\"name\" ").append(composeMatchPart(pschema)); 2944 query.append(" AND pkschema.\"name\" ").append(composeMatchPart(pschema));
2978 } 2945 }
2979 if (ptable != null) { 2946 if (ptable != null) {
2980 query.append(" AND \"pktable\".\"name\" ").append(composeMatchPart(ptable)); 2947 query.append(" AND pktable.\"name\" ").append(composeMatchPart(ptable));
2981 } 2948 }
2982 2949
2983 if (fschema != null) { 2950 if (fschema != null) {
2984 query.append(" AND \"fkschema\".\"name\" ").append(composeMatchPart(fschema)); 2951 query.append(" AND fkschema.\"name\" ").append(composeMatchPart(fschema));
2985 } 2952 }
2986 if (ftable != null) { 2953 if (ftable != null) {
2987 query.append(" AND \"fktable\".\"name\" ").append(composeMatchPart(ftable)); 2954 query.append(" AND fktable.\"name\" ").append(composeMatchPart(ftable));
2988 } 2955 }
2989 } 2956 }
2990 2957
2991 query.append(" ORDER BY \"FKTABLE_SCHEM\", \"FKTABLE_NAME\", \"FK_NAME\", \"KEY_SEQ\""); 2958 query.append(" ORDER BY \"FKTABLE_SCHEM\", \"FKTABLE_NAME\", \"FK_NAME\", \"KEY_SEQ\"");
2992 2959
3140 final boolean approximate 3107 final boolean approximate
3141 ) throws SQLException 3108 ) throws SQLException
3142 { 3109 {
3143 String table_row_count = "0"; 3110 String table_row_count = "0";
3144 3111
3145 if (!approximate && schema != null && table != null && !schema.isEmpty() && !table.isEmpty()) { 3112 if (!approximate
3113 && schema != null && !schema.isEmpty() && !schema.contains("%")
3114 && table != null && !table.isEmpty() && !table.contains("%")) {
3146 // we need the exact cardinality for one specific fully qualified table 3115 // we need the exact cardinality for one specific fully qualified table
3147 ResultSet count = null; 3116 ResultSet count = null;
3148 try { 3117 try {
3149 count = executeMetaDataQuery("SELECT COUNT(*) FROM \"" + schema + "\".\"" + table + "\""); 3118 count = executeMetaDataQuery("SELECT COUNT(*) FROM \"" + schema + "\".\"" + table + "\"");
3150 if (count != null && count.next()) { 3119 if (count != null && count.next()) {
3160 } 3129 }
3161 3130
3162 final StringBuilder query = new StringBuilder(1250); 3131 final StringBuilder query = new StringBuilder(1250);
3163 query.append( 3132 query.append(
3164 "SELECT cast(null AS char(1)) AS \"TABLE_CAT\", " + 3133 "SELECT cast(null AS char(1)) AS \"TABLE_CAT\", " +
3165 "\"schemas\".\"name\" AS \"TABLE_SCHEM\", " + 3134 "s.\"name\" AS \"TABLE_SCHEM\", " +
3166 "\"tables\".\"name\" AS \"TABLE_NAME\", " + 3135 "t.\"name\" AS \"TABLE_NAME\", " +
3167 "CASE WHEN \"keys\".\"name\" IS NULL THEN true ELSE false END AS \"NON_UNIQUE\", " + 3136 "CASE WHEN k.\"name\" IS NULL THEN true ELSE false END AS \"NON_UNIQUE\", " +
3168 "cast(null AS char(1)) AS \"INDEX_QUALIFIER\", " + 3137 "cast(null AS char(1)) AS \"INDEX_QUALIFIER\", " +
3169 "\"idxs\".\"name\" AS \"INDEX_NAME\", " + 3138 "i.\"name\" AS \"INDEX_NAME\", " +
3170 "CASE \"idxs\".\"type\" WHEN 0 THEN ").append(DatabaseMetaData.tableIndexHashed).append(" ELSE ").append(DatabaseMetaData.tableIndexOther).append(" END AS \"TYPE\", " + 3139 "CASE i.\"type\" WHEN 0 THEN ").append(DatabaseMetaData.tableIndexHashed).append(" ELSE ").append(DatabaseMetaData.tableIndexOther).append(" END AS \"TYPE\", " +
3171 "cast(\"objects\".\"nr\" +1 AS smallint) AS \"ORDINAL_POSITION\", "+ 3140 "cast(o.\"nr\" +1 AS smallint) AS \"ORDINAL_POSITION\", "+
3172 "\"columns\".\"name\" AS \"COLUMN_NAME\", " + 3141 "c.\"name\" AS \"COLUMN_NAME\", " +
3173 "cast(null AS char(1)) AS \"ASC_OR_DESC\", " + // sort sequence currently not supported in keys or indexes in MonetDB 3142 "cast(null AS char(1)) AS \"ASC_OR_DESC\", " + // sort sequence currently not supported in keys or indexes in MonetDB
3174 "cast(").append(table_row_count).append(" AS int) AS \"CARDINALITY\", " + 3143 "cast(").append(table_row_count).append(" AS int) AS \"CARDINALITY\", " +
3175 "cast(0 AS int) AS \"PAGES\", " + 3144 "cast(0 AS int) AS \"PAGES\", " +
3176 "cast(null AS char(1)) AS \"FILTER_CONDITION\" " + 3145 "cast(null AS char(1)) AS \"FILTER_CONDITION\" " +
3177 "FROM \"sys\".\"idxs\" LEFT JOIN \"sys\".\"keys\" ON \"idxs\".\"name\" = \"keys\".\"name\", " + 3146 "FROM \"sys\".\"idxs\" i " +
3178 "\"sys\".\"schemas\", " + 3147 "JOIN \"sys\".\"tables\" t ON i.\"table_id\" = t.\"id\" " +
3179 "\"sys\".\"objects\", " + 3148 "JOIN \"sys\".\"schemas\" s ON t.\"schema_id\" = s.\"id\" " +
3180 "\"sys\".\"columns\", " + 3149 "JOIN \"sys\".\"objects\" o ON i.\"id\" = o.\"id\" " +
3181 "\"sys\".\"tables\" " + 3150 "JOIN \"sys\".\"columns\" c ON (t.\"id\" = c.\"table_id\" AND o.\"name\" = c.\"name\") " +
3182 "WHERE \"idxs\".\"table_id\" = \"tables\".\"id\" " + 3151 "LEFT OUTER JOIN \"sys\".\"keys\" k ON (i.\"name\" = k.\"name\" AND i.\"table_id\" = k.\"table_id\" AND k.\"type\" IN (0,1)) " + // primary (0) and unique keys (1) only
3183 "AND \"tables\".\"schema_id\" = \"schemas\".\"id\" " + 3152 "WHERE 1 = 1");
3184 "AND \"idxs\".\"id\" = \"objects\".\"id\" " +
3185 "AND \"tables\".\"id\" = \"columns\".\"table_id\" " +
3186 "AND \"objects\".\"name\" = \"columns\".\"name\" " +
3187 "AND (\"keys\".\"type\" IS NULL OR \"keys\".\"type\" = 1)");
3188 3153
3189 if (catalog != null && !catalog.isEmpty()) { 3154 if (catalog != null && !catalog.isEmpty()) {
3190 // non-empty catalog selection. 3155 // non-empty catalog selection.
3191 // as we do not support catalogs this always results in no rows returned 3156 // as we do not support catalogs this always results in no rows returned
3192 query.append(" AND 1 = 0"); 3157 query.append(" AND 1 = 0");
3193 } else { 3158 } else {
3194 if (schema != null) { 3159 if (schema != null) {
3195 query.append(" AND \"schemas\".\"name\" ").append(composeMatchPart(schema)); 3160 query.append(" AND s.\"name\" ").append(composeMatchPart(schema));
3196 } 3161 }
3197 if (table != null) { 3162 if (table != null) {
3198 query.append(" AND \"tables\".\"name\" ").append(composeMatchPart(table)); 3163 query.append(" AND t.\"name\" ").append(composeMatchPart(table));
3199 } 3164 }
3200 if (unique) { 3165 if (unique) {
3201 query.append(" AND \"keys\".\"name\" IS NOT NULL"); 3166 query.append(" AND k.\"name\" IS NOT NULL");
3202 } 3167 }
3203 } 3168 }
3204 3169
3205 query.append(" ORDER BY \"NON_UNIQUE\", \"TYPE\", \"INDEX_NAME\", \"ORDINAL_POSITION\""); 3170 query.append(" ORDER BY \"NON_UNIQUE\", \"TYPE\", \"INDEX_NAME\", \"ORDINAL_POSITION\"");
3206 3171
3335 final StringBuilder query = new StringBuilder(990); 3300 final StringBuilder query = new StringBuilder(990);
3336 if (types != null && types.length > 0) { 3301 if (types != null && types.length > 0) {
3337 query.append("SELECT * FROM ("); 3302 query.append("SELECT * FROM (");
3338 } 3303 }
3339 query.append("SELECT cast(null as char(1)) AS \"TYPE_CAT\", " + 3304 query.append("SELECT cast(null as char(1)) AS \"TYPE_CAT\", " +
3340 "\"schemas\".\"name\" AS \"TYPE_SCHEM\", " + 3305 "s.\"name\" AS \"TYPE_SCHEM\", " +
3341 "\"types\".\"sqlname\" AS \"TYPE_NAME\", " + 3306 "t.\"sqlname\" AS \"TYPE_NAME\", " +
3342 "CASE \"types\".\"sqlname\"" + 3307 "CASE t.\"sqlname\"" +
3343 // next 4 UDTs are standard 3308 // next 4 UDTs are standard
3344 " WHEN 'inet' THEN 'nl.cwi.monetdb.jdbc.types.INET'" + 3309 " WHEN 'inet' THEN 'nl.cwi.monetdb.jdbc.types.INET'" +
3345 " WHEN 'json' THEN 'java.lang.String'" + 3310 " WHEN 'json' THEN 'java.lang.String'" +
3346 " WHEN 'url' THEN 'nl.cwi.monetdb.jdbc.types.URL'" + 3311 " WHEN 'url' THEN 'nl.cwi.monetdb.jdbc.types.URL'" +
3347 " WHEN 'uuid' THEN 'java.lang.String'" + 3312 " WHEN 'uuid' THEN 'java.lang.String'" +
3348 " ELSE 'java.lang.Object' END AS \"CLASS_NAME\", " + 3313 " ELSE 'java.lang.Object' END AS \"CLASS_NAME\", " +
3349 "cast(CASE WHEN \"types\".\"sqlname\" IN ('inet', 'json', 'url', 'uuid') THEN ").append(Types.JAVA_OBJECT) 3314 "cast(CASE WHEN t.\"sqlname\" IN ('inet', 'json', 'url', 'uuid') THEN ").append(Types.JAVA_OBJECT)
3350 .append(" ELSE ").append(Types.STRUCT).append(" END AS int) AS \"DATA_TYPE\", " + 3315 .append(" ELSE ").append(Types.STRUCT).append(" END AS int) AS \"DATA_TYPE\", " +
3351 "\"types\".\"systemname\" AS \"REMARKS\", " + 3316 "t.\"systemname\" AS \"REMARKS\", " +
3352 "cast(null as smallint) AS \"BASE_TYPE\" " + 3317 "cast(null as smallint) AS \"BASE_TYPE\" " +
3353 "FROM \"sys\".\"types\" JOIN \"sys\".\"schemas\" ON \"types\".\"schema_id\" = \"schemas\".\"id\" " + 3318 "FROM \"sys\".\"types\" t " +
3319 "JOIN \"sys\".\"schemas\" s ON t.\"schema_id\" = s.\"id\" " +
3354 // exclude the built-in types (I assume they always have id <= 99 and eclass < 15) 3320 // exclude the built-in types (I assume they always have id <= 99 and eclass < 15)
3355 "WHERE \"types\".\"id\" > 99 AND \"types\".\"eclass\" >= 15"); 3321 "WHERE t.\"id\" > 99 AND t.\"eclass\" >= 15");
3356 3322
3357 if (catalog != null && !catalog.isEmpty()) { 3323 if (catalog != null && !catalog.isEmpty()) {
3358 // non-empty catalog selection. 3324 // non-empty catalog selection.
3359 // as we do not support catalogs this always results in no rows returned 3325 // as we do not support catalogs this always results in no rows returned
3360 query.append(" AND 1 = 0"); 3326 query.append(" AND 1 = 0");
3361 } else { 3327 } else {
3362 if (schemaPattern != null) { 3328 if (schemaPattern != null) {
3363 query.append(" AND \"schemas\".\"name\" ").append(composeMatchPart(schemaPattern)); 3329 query.append(" AND s.\"name\" ").append(composeMatchPart(schemaPattern));
3364 } 3330 }
3365 if (typeNamePattern != null) { 3331 if (typeNamePattern != null) {
3366 query.append(" AND \"types\".\"sqlname\" ").append(composeMatchPart(typeNamePattern)); 3332 query.append(" AND t.\"sqlname\" ").append(composeMatchPart(typeNamePattern));
3367 } 3333 }
3368 } 3334 }
3369 3335
3370 if (types != null && types.length > 0) { 3336 if (types != null && types.length > 0) {
3371 query.append(") AS getUDTs WHERE \"DATA_TYPE\" IN ("); 3337 query.append(") AS getUDTs WHERE \"DATA_TYPE\" IN (");
3853 * property. This will typically contain information as 3819 * property. This will typically contain information as
3854 * to where this property is stored in the database. 3820 * to where this property is stored in the database.
3855 * 3821 *
3856 * The ResultSet is sorted by the NAME column 3822 * The ResultSet is sorted by the NAME column
3857 * 3823 *
3858 * @return A ResultSet object; each row is a supported client info 3824 * @return A ResultSet object; each row is a supported client info property
3859 * property, none in case of MonetDB's current JDBC driver
3860 * @throws SQLException if a database access error occurs 3825 * @throws SQLException if a database access error occurs
3861 */ 3826 */
3862 @Override 3827 @Override
3863 public ResultSet getClientInfoProperties() throws SQLException { 3828 public ResultSet getClientInfoProperties() throws SQLException {
3864 // for a list of connection properties see also MonetConnection.java constructor MonetConnection(Properties props) 3829 // for a list of connection properties see also MonetConnection.java constructor MonetConnection(Properties props)
3930 ) throws SQLException 3895 ) throws SQLException
3931 { 3896 {
3932 final boolean useCommentsTable = ((MonetConnection)con).commentsTableExists(); 3897 final boolean useCommentsTable = ((MonetConnection)con).commentsTableExists();
3933 final StringBuilder query = new StringBuilder(800); 3898 final StringBuilder query = new StringBuilder(800);
3934 query.append("SELECT cast(null as char(1)) AS \"FUNCTION_CAT\", " + 3899 query.append("SELECT cast(null as char(1)) AS \"FUNCTION_CAT\", " +
3935 "\"schemas\".\"name\" AS \"FUNCTION_SCHEM\", " + 3900 "s.\"name\" AS \"FUNCTION_SCHEM\", " +
3936 "\"functions\".\"name\" AS \"FUNCTION_NAME\", ") 3901 "f.\"name\" AS \"FUNCTION_NAME\", ")
3937 .append(useCommentsTable ? "COALESCE(\"comments\".\"remark\", cast(\"functions\".\"func\" as varchar(9999)))" : "cast(\"functions\".\"func\" as varchar(9999))").append(" AS \"REMARKS\", " + 3902 .append(useCommentsTable ? "COALESCE(cm.\"remark\", cast(f.\"func\" as varchar(9999)))" : "cast(f.\"func\" as varchar(9999))").append(" AS \"REMARKS\", " +
3938 "CASE \"functions\".\"type\"" + 3903 "CASE f.\"type\"" +
3939 " WHEN 1 THEN ").append(DatabaseMetaData.functionNoTable) 3904 " WHEN 1 THEN ").append(DatabaseMetaData.functionNoTable)
3940 .append(" WHEN 2 THEN ").append(DatabaseMetaData.functionNoTable) 3905 .append(" WHEN 2 THEN ").append(DatabaseMetaData.functionNoTable)
3941 .append(" WHEN 3 THEN ").append(DatabaseMetaData.functionNoTable) 3906 .append(" WHEN 3 THEN ").append(DatabaseMetaData.functionNoTable)
3942 .append(" WHEN 4 THEN ").append(DatabaseMetaData.functionNoTable) 3907 .append(" WHEN 4 THEN ").append(DatabaseMetaData.functionNoTable)
3943 .append(" WHEN 5 THEN ").append(DatabaseMetaData.functionReturnsTable) 3908 .append(" WHEN 5 THEN ").append(DatabaseMetaData.functionReturnsTable)
3944 .append(" WHEN 6 THEN ").append(DatabaseMetaData.functionNoTable) 3909 .append(" WHEN 6 THEN ").append(DatabaseMetaData.functionNoTable)
3945 .append(" WHEN 7 THEN ").append(DatabaseMetaData.functionReturnsTable) 3910 .append(" WHEN 7 THEN ").append(DatabaseMetaData.functionReturnsTable)
3946 .append(" ELSE ").append(DatabaseMetaData.functionResultUnknown).append(" END AS \"FUNCTION_TYPE\", " + 3911 .append(" ELSE ").append(DatabaseMetaData.functionResultUnknown).append(" END AS \"FUNCTION_TYPE\", " +
3947 // only the id value uniquely identifies a function. Include it to be able to differentiate between multiple overloaded functions with the same name 3912 // only the id value uniquely identifies a function. Include it to be able to differentiate between multiple overloaded functions with the same name
3948 "cast(\"functions\".\"id\" as varchar(10)) AS \"SPECIFIC_NAME\" " + 3913 "cast(f.\"id\" as varchar(10)) AS \"SPECIFIC_NAME\" " +
3949 "FROM \"sys\".\"functions\" JOIN \"sys\".\"schemas\" ON (\"functions\".\"schema_id\" = \"schemas\".\"id\") "); 3914 "FROM \"sys\".\"functions\" f " +
3915 "JOIN \"sys\".\"schemas\" s ON f.\"schema_id\" = s.\"id\" ");
3950 if (useCommentsTable) { 3916 if (useCommentsTable) {
3951 query.append("LEFT OUTER JOIN \"sys\".\"comments\" ON (\"functions\".\"id\" = \"comments\".\"id\") "); 3917 query.append("LEFT OUTER JOIN \"sys\".\"comments\" cm ON (f.\"id\" = cm.\"id\") ");
3952 } 3918 }
3953 // only functions, so exclude procedures (type = 2). Those will to be returned via getProcedures() 3919 // only functions, so exclude procedures (type = 2). Those will to be returned via getProcedures()
3954 query.append("WHERE \"functions\".\"type\" <> 2"); 3920 query.append("WHERE f.\"type\" <> 2");
3955 3921
3956 if (catalog != null && !catalog.isEmpty()) { 3922 if (catalog != null && !catalog.isEmpty()) {
3957 // non-empty catalog selection. 3923 // non-empty catalog selection.
3958 // as we do not support catalogs this always results in no rows returned 3924 // as we do not support catalogs this always results in no rows returned
3959 query.append(" AND 1 = 0"); 3925 query.append(" AND 1 = 0");
3960 } else { 3926 } else {
3961 if (schemaPattern != null) { 3927 if (schemaPattern != null) {
3962 query.append(" AND \"schemas\".\"name\" ").append(composeMatchPart(schemaPattern)); 3928 query.append(" AND s.\"name\" ").append(composeMatchPart(schemaPattern));
3963 } 3929 }
3964 if (functionNamePattern != null) { 3930 if (functionNamePattern != null) {
3965 query.append(" AND \"functions\".\"name\" ").append(composeMatchPart(functionNamePattern)); 3931 query.append(" AND f.\"name\" ").append(composeMatchPart(functionNamePattern));
3966 } 3932 }
3967 } 3933 }
3968 3934
3969 query.append(" ORDER BY \"FUNCTION_SCHEM\", \"FUNCTION_NAME\", \"SPECIFIC_NAME\""); 3935 query.append(" ORDER BY \"FUNCTION_SCHEM\", \"FUNCTION_NAME\", \"SPECIFIC_NAME\"");
3970 3936
4038 final String columnNamePattern 4004 final String columnNamePattern
4039 ) throws SQLException 4005 ) throws SQLException
4040 { 4006 {
4041 final StringBuilder query = new StringBuilder(2600); 4007 final StringBuilder query = new StringBuilder(2600);
4042 query.append("SELECT cast(null as char(1)) AS \"FUNCTION_CAT\", " + 4008 query.append("SELECT cast(null as char(1)) AS \"FUNCTION_CAT\", " +
4043 "\"schemas\".\"name\" AS \"FUNCTION_SCHEM\", " + 4009 "s.\"name\" AS \"FUNCTION_SCHEM\", " +
4044 "\"functions\".\"name\" AS \"FUNCTION_NAME\", " + 4010 "f.\"name\" AS \"FUNCTION_NAME\", " +
4045 "\"args\".\"name\" AS \"COLUMN_NAME\", " + 4011 "a.\"name\" AS \"COLUMN_NAME\", " +
4046 "cast(CASE \"args\".\"inout\"" + 4012 "cast(CASE a.\"inout\"" +
4047 " WHEN 0 THEN (CASE \"args\".\"number\" WHEN 0 THEN ") 4013 " WHEN 0 THEN (CASE a.\"number\" WHEN 0 THEN ")
4048 .append(DatabaseMetaData.functionReturn).append(" ELSE ").append(DatabaseMetaData.functionColumnOut).append(" END)" + 4014 .append(DatabaseMetaData.functionReturn).append(" ELSE ").append(DatabaseMetaData.functionColumnOut).append(" END)" +
4049 " WHEN 1 THEN ").append(DatabaseMetaData.functionColumnIn) 4015 " WHEN 1 THEN ").append(DatabaseMetaData.functionColumnIn)
4050 .append(" ELSE ").append(DatabaseMetaData.functionColumnUnknown).append(" END AS smallint) AS \"COLUMN_TYPE\", " + 4016 .append(" ELSE ").append(DatabaseMetaData.functionColumnUnknown).append(" END AS smallint) AS \"COLUMN_TYPE\", " +
4051 "cast(").append(MonetDriver.getSQLTypeMap("\"args\".\"type\"")).append(" AS int) AS \"DATA_TYPE\", " + 4017 "cast(").append(MonetDriver.getSQLTypeMap("a.\"type\"")).append(" AS int) AS \"DATA_TYPE\", " +
4052 "\"args\".\"type\" AS \"TYPE_NAME\", " + 4018 "a.\"type\" AS \"TYPE_NAME\", " +
4053 "CASE \"args\".\"type\" WHEN 'tinyint' THEN 3 WHEN 'smallint' THEN 5 WHEN 'int' THEN 10 WHEN 'bigint' THEN 19" + 4019 "CASE a.\"type\" WHEN 'tinyint' THEN 3 WHEN 'smallint' THEN 5 WHEN 'int' THEN 10 WHEN 'bigint' THEN 19" +
4054 " WHEN 'hugeint' THEN 38 WHEN 'oid' THEN 19 WHEN 'wrd' THEN 19 ELSE \"args\".\"type_digits\" END AS \"PRECISION\", " + 4020 " WHEN 'hugeint' THEN 38 WHEN 'oid' THEN 19 WHEN 'wrd' THEN 19 ELSE a.\"type_digits\" END AS \"PRECISION\", " +
4055 "CASE \"args\".\"type\" WHEN 'tinyint' THEN 1 WHEN 'smallint' THEN 2 WHEN 'int' THEN 4 WHEN 'bigint' THEN 8" + 4021 "CASE a.\"type\" WHEN 'tinyint' THEN 1 WHEN 'smallint' THEN 2 WHEN 'int' THEN 4 WHEN 'bigint' THEN 8" +
4056 " WHEN 'hugeint' THEN 16 WHEN 'oid' THEN 8 WHEN 'wrd' THEN 8 ELSE \"args\".\"type_digits\" END AS \"LENGTH\", " + 4022 " WHEN 'hugeint' THEN 16 WHEN 'oid' THEN 8 WHEN 'wrd' THEN 8 ELSE a.\"type_digits\" END AS \"LENGTH\", " +
4057 "cast(CASE WHEN \"args\".\"type\" IN ('tinyint','smallint','int','bigint','hugeint','oid','wrd','decimal','numeric'," + 4023 "cast(CASE WHEN a.\"type\" IN ('tinyint','smallint','int','bigint','hugeint','oid','wrd','decimal','numeric'," +
4058 "'time','timetz','timestamp','timestamptz','sec_interval') THEN \"args\".\"type_scale\" ELSE NULL END AS smallint) AS \"SCALE\", " + 4024 "'time','timetz','timestamp','timestamptz','sec_interval') THEN a.\"type_scale\" ELSE NULL END AS smallint) AS \"SCALE\", " +
4059 "cast(CASE WHEN \"args\".\"type\" IN ('tinyint','smallint','int','bigint','hugeint','oid','wrd','decimal','numeric') THEN 10" + 4025 "cast(CASE WHEN a.\"type\" IN ('tinyint','smallint','int','bigint','hugeint','oid','wrd','decimal','numeric') THEN 10" +
4060 " WHEN \"args\".\"type\" IN ('real','float','double') THEN 2 ELSE NULL END AS smallint) AS \"RADIX\", " + 4026 " WHEN a.\"type\" IN ('real','float','double') THEN 2 ELSE NULL END AS smallint) AS \"RADIX\", " +
4061 "cast(").append(DatabaseMetaData.functionNullableUnknown).append(" AS smallint) AS \"NULLABLE\", " + 4027 "cast(").append(DatabaseMetaData.functionNullableUnknown).append(" AS smallint) AS \"NULLABLE\", " +
4062 "cast(null as char(1)) AS \"REMARKS\", " + 4028 "cast(null as char(1)) AS \"REMARKS\", " +
4063 "cast(CASE WHEN \"args\".\"type\" IN ('char','varchar','clob') THEN \"args\".\"type_digits\" ELSE NULL END as int) AS \"CHAR_OCTET_LENGTH\", " + 4029 "cast(CASE WHEN a.\"type\" IN ('char','varchar','clob') THEN a.\"type_digits\" ELSE NULL END as int) AS \"CHAR_OCTET_LENGTH\", " +
4064 "cast(\"args\".\"number\" as int) AS \"ORDINAL_POSITION\", " + 4030 "cast(a.\"number\" as int) AS \"ORDINAL_POSITION\", " +
4065 "cast('' as varchar(3)) AS \"IS_NULLABLE\", " + 4031 "cast('' as varchar(3)) AS \"IS_NULLABLE\", " +
4066 // the specific name contains the function id, in order to be able to match the args to the correct overloaded function name 4032 // the specific name contains the function id, in order to be able to match the args to the correct overloaded function name
4067 "cast(\"functions\".\"id\" as varchar(10)) AS \"SPECIFIC_NAME\" " + 4033 "cast(f.\"id\" as varchar(10)) AS \"SPECIFIC_NAME\" " +
4068 "FROM \"sys\".\"args\" " + 4034 "FROM \"sys\".\"args\" a " +
4069 "JOIN \"sys\".\"functions\" ON (\"args\".\"func_id\" = \"functions\".\"id\") " + 4035 "JOIN \"sys\".\"functions\" f ON a.\"func_id\" = f.\"id\" " +
4070 "JOIN \"sys\".\"schemas\" ON (\"functions\".\"schema_id\" = \"schemas\".\"id\") " + 4036 "JOIN \"sys\".\"schemas\" s ON f.\"schema_id\" = s.\"id\" " +
4071 // only functions, so exclude procedures (type = 2). Those will to be returned via getProcedureColumns() 4037 // only functions, so exclude procedures (type = 2). Those will to be returned via getProcedureColumns()
4072 "WHERE \"functions\".\"type\" <> 2"); 4038 "WHERE f.\"type\" <> 2");
4073 4039
4074 if (catalog != null && !catalog.isEmpty()) { 4040 if (catalog != null && !catalog.isEmpty()) {
4075 // non-empty catalog selection. 4041 // non-empty catalog selection.
4076 // as we do not support catalogs this always results in no rows returned 4042 // as we do not support catalogs this always results in no rows returned
4077 query.append(" AND 1 = 0"); 4043 query.append(" AND 1 = 0");
4078 } else { 4044 } else {
4079 if (schemaPattern != null) { 4045 if (schemaPattern != null) {
4080 query.append(" AND \"schemas\".\"name\" ").append(composeMatchPart(schemaPattern)); 4046 query.append(" AND s.\"name\" ").append(composeMatchPart(schemaPattern));
4081 } 4047 }
4082 if (functionNamePattern != null) { 4048 if (functionNamePattern != null) {
4083 query.append(" AND \"functions\".\"name\" ").append(composeMatchPart(functionNamePattern)); 4049 query.append(" AND f.\"name\" ").append(composeMatchPart(functionNamePattern));
4084 } 4050 }
4085 if (columnNamePattern != null) { 4051 if (columnNamePattern != null) {
4086 query.append(" AND \"args\".\"name\" ").append(composeMatchPart(columnNamePattern)); 4052 query.append(" AND a.\"name\" ").append(composeMatchPart(columnNamePattern));
4087 } 4053 }
4088 } 4054 }
4089 4055
4090 query.append(" ORDER BY \"FUNCTION_SCHEM\", \"FUNCTION_NAME\", \"SPECIFIC_NAME\", \"ORDINAL_POSITION\""); 4056 query.append(" ORDER BY \"FUNCTION_SCHEM\", \"FUNCTION_NAME\", \"SPECIFIC_NAME\", \"ORDINAL_POSITION\"");
4091 4057