Mercurial > hg > monetdb-java

comparison src/main/java/nl/cwi/monetdb/jdbc/MonetDatabaseMetaData.java @ 215:71b039bc2d99

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Added support for querying the sys.comments table for some meta data methods when connected to a MonetDB server which has table sys.comments.
author Martin van Dinther <martin.van.dinther@monetdbsolutions.com>
date Thu, 19 Apr 2018 16:13:01 +0200 (2018-04-19)
parents 115f6351bf4b
children 116b5a149fb4
comparison
equal deleted inserted replaced
214:b8c007e86694 215:71b039bc2d99
21 21
22 /** 22 /**
23 * A DatabaseMetaData object suitable for the MonetDB database. 23 * A DatabaseMetaData object suitable for the MonetDB database.
24 * 24 *
25 * @author Fabian Groffen, Martin van Dinther 25 * @author Fabian Groffen, Martin van Dinther
26 * @version 0.6 26 * @version 0.7
27 */ 27 */
28 public class MonetDatabaseMetaData extends MonetWrapper implements DatabaseMetaData { 28 public class MonetDatabaseMetaData extends MonetWrapper implements DatabaseMetaData {
29 private Connection con; 29 private Connection con;
30 30
31 // Internal cache for 3 server environment values 31 // Internal cache for 3 server environment values
1703 String catalog, 1703 String catalog,
1704 String schemaPattern, 1704 String schemaPattern,
1705 String procedureNamePattern 1705 String procedureNamePattern
1706 ) throws SQLException 1706 ) throws SQLException
1707 { 1707 {
1708 boolean useCommentsTable = ((MonetConnection)con).commentsTableExists();
1708 StringBuilder query = new StringBuilder(980); 1709 StringBuilder query = new StringBuilder(980);
1709 query.append("SELECT cast(null as varchar(1)) AS \"PROCEDURE_CAT\", " + 1710 query.append("SELECT cast(null as varchar(1)) AS \"PROCEDURE_CAT\", " +
1710 "\"schemas\".\"name\" AS \"PROCEDURE_SCHEM\", " + 1711 "\"schemas\".\"name\" AS \"PROCEDURE_SCHEM\", " +
1711 "\"functions\".\"name\" AS \"PROCEDURE_NAME\", " + 1712 "\"functions\".\"name\" AS \"PROCEDURE_NAME\", " +
1712 "cast(null as char(1)) AS \"Field4\", " + 1713 "cast(null as char(1)) AS \"Field4\", " +
1713 "cast(null as char(1)) AS \"Field5\", " + 1714 "cast(null as char(1)) AS \"Field5\", " +
1714 "cast(null as char(1)) AS \"Field6\", " + 1715 "cast(null as char(1)) AS \"Field6\", ")
1715 "cast(\"functions\".\"func\" as varchar(9999)) AS \"REMARKS\", " + 1716 .append(useCommentsTable ? "COALESCE(\"comments\".\"remark\", cast(\"functions\".\"func\" as varchar(9999)))" : "cast(\"functions\".\"func\" as varchar(9999))").append(" AS \"REMARKS\", " +
1716 // in MonetDB procedures have no return value by design. 1717 // in MonetDB procedures have no return value by design.
1717 "CAST(").append(DatabaseMetaData.procedureNoResult).append(" AS smallint) AS \"PROCEDURE_TYPE\", " + 1718 "CAST(").append(DatabaseMetaData.procedureNoResult).append(" AS smallint) AS \"PROCEDURE_TYPE\", " +
1718 // only the id value uniquely identifies a procedure. Include it to be able to differentiate between multiple overloaded procedures with the same name 1719 // only the id value uniquely identifies a procedure. Include it to be able to differentiate between multiple overloaded procedures with the same name
1719 "cast(\"functions\".\"id\" as varchar(10)) AS \"SPECIFIC_NAME\" " + 1720 "cast(\"functions\".\"id\" as varchar(10)) AS \"SPECIFIC_NAME\" " +
1720 "FROM \"sys\".\"functions\" JOIN \"sys\".\"schemas\" ON (\"functions\".\"schema_id\" = \"schemas\".\"id\") " + 1721 "FROM \"sys\".\"functions\" JOIN \"sys\".\"schemas\" ON (\"functions\".\"schema_id\" = \"schemas\".\"id\") ");
1722 if (useCommentsTable) {
1723 query.append("LEFT OUTER JOIN \"sys\".\"comments\" ON (\"functions\".\"id\" = \"comments\".\"id\") ");
1724 }
1721 // include procedures only (type = 2). Others will be returned via getFunctions() 1725 // include procedures only (type = 2). Others will be returned via getFunctions()
1722 "WHERE \"functions\".\"type\" = 2"); 1726 query.append("WHERE \"functions\".\"type\" = 2");
1723 1727
1724 if (catalog != null && catalog.length() > 0) { 1728 if (catalog != null && catalog.length() > 0) {
1725 // none empty catalog selection. 1729 // none empty catalog selection.
1726 // as we do not support catalogs this always results in no rows returned 1730 // as we do not support catalogs this always results in no rows returned
1727 query.append(" AND 1 = 0"); 1731 query.append(" AND 1 = 0");
1730 query.append(" AND \"schemas\".\"name\" ").append(composeMatchPart(schemaPattern)); 1734 query.append(" AND \"schemas\".\"name\" ").append(composeMatchPart(schemaPattern));
1731 } 1735 }
1732 if (procedureNamePattern != null) { 1736 if (procedureNamePattern != null) {
1733 query.append(" AND \"functions\".\"name\" ").append(composeMatchPart(procedureNamePattern)); 1737 query.append(" AND \"functions\".\"name\" ").append(composeMatchPart(procedureNamePattern));
1734 } 1738 }
1735
1736 query.append(" ORDER BY \"PROCEDURE_SCHEM\", \"PROCEDURE_NAME\", \"SPECIFIC_NAME\""); 1739 query.append(" ORDER BY \"PROCEDURE_SCHEM\", \"PROCEDURE_NAME\", \"SPECIFIC_NAME\"");
1737 1740
1738 return executeMetaDataQuery(query.toString()); 1741 return executeMetaDataQuery(query.toString());
1739 } 1742 }
1740 1743
1835 // 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. 1838 // 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.
1836 "CAST(\"args\".\"number\" + 1 as int) AS \"ORDINAL_POSITION\", " + 1839 "CAST(\"args\".\"number\" + 1 as int) AS \"ORDINAL_POSITION\", " +
1837 "CAST('' as varchar(3)) AS \"IS_NULLABLE\", " + 1840 "CAST('' as varchar(3)) AS \"IS_NULLABLE\", " +
1838 // the specific name contains the function id, in order to be able to match the args to the correct overloaded procedure name 1841 // the specific name contains the function id, in order to be able to match the args to the correct overloaded procedure name
1839 "cast(\"functions\".\"id\" as varchar(10)) AS \"SPECIFIC_NAME\" " + 1842 "cast(\"functions\".\"id\" as varchar(10)) AS \"SPECIFIC_NAME\" " +
1840 "FROM \"sys\".\"args\", \"sys\".\"functions\", \"sys\".\"schemas\" " + 1843 "FROM \"sys\".\"args\" " +
1841 "WHERE \"args\".\"func_id\" = \"functions\".\"id\" " + 1844 "JOIN \"sys\".\"functions\" ON (\"args\".\"func_id\" = \"functions\".\"id\") " +
1842 "AND \"functions\".\"schema_id\" = \"schemas\".\"id\" " + 1845 "JOIN \"sys\".\"schemas\" ON (\"functions\".\"schema_id\" = \"schemas\".\"id\") " +
1843 // include procedures only (type = 2). Others will be returned via getFunctionColumns() 1846 // include procedures only (type = 2). Others will be returned via getFunctionColumns()
1844 "AND \"functions\".\"type\" = 2"); 1847 "WHERE \"functions\".\"type\" = 2");
1845 1848
1846 if (catalog != null && catalog.length() > 0) { 1849 if (catalog != null && catalog.length() > 0) {
1847 // none empty catalog selection. 1850 // none empty catalog selection.
1848 // as we do not support catalogs this always results in no rows returned 1851 // as we do not support catalogs this always results in no rows returned
1849 query.append(" AND 1 = 0"); 1852 query.append(" AND 1 = 0");
1948 // as of Jul2015 release we also have a new table: sys.table_types with names for the new table types 1951 // as of Jul2015 release we also have a new table: sys.table_types with names for the new table types
1949 // for correct behavior we need to know if the server is using the old (pre Jul2015) or new sys.tables.type values 1952 // for correct behavior we need to know if the server is using the old (pre Jul2015) or new sys.tables.type values
1950 boolean preJul2015 = ("11.19.15".compareTo(getDatabaseProductVersion()) >= 0); 1953 boolean preJul2015 = ("11.19.15".compareTo(getDatabaseProductVersion()) >= 0);
1951 /* for debug: System.out.println("getDatabaseProductVersion() is " + getDatabaseProductVersion() + " preJul2015 is " + preJul2015); */ 1954 /* for debug: System.out.println("getDatabaseProductVersion() is " + getDatabaseProductVersion() + " preJul2015 is " + preJul2015); */
1952 1955
1956 boolean useCommentsTable = ((MonetConnection)con).commentsTableExists();
1953 StringBuilder query = new StringBuilder(1600); 1957 StringBuilder query = new StringBuilder(1600);
1954 if (preJul2015 && types != null && types.length > 0) { 1958 if (preJul2015 && types != null && types.length > 0) {
1955 // we need to filter on the constructed "TABLE_TYPE" expression, this is only possible when we use a subquery in the FROM 1959 // we need to filter on the constructed "TABLE_TYPE" expression, this is only possible when we use a subquery in the FROM
1956 query.append("SELECT * FROM ("); 1960 query.append("SELECT * FROM (");
1957 } 1961 }
1970 "WHEN \"tables\".\"system\" = false AND \"tables\".\"type\" IN (1, 31) AND \"tables\".\"temporary\" = 1 THEN 'SESSION VIEW' " + 1974 "WHEN \"tables\".\"system\" = false AND \"tables\".\"type\" IN (1, 31) AND \"tables\".\"temporary\" = 1 THEN 'SESSION VIEW' " +
1971 "END AS \"TABLE_TYPE\", "); 1975 "END AS \"TABLE_TYPE\", ");
1972 } else { 1976 } else {
1973 query.append("\"table_types\".\"table_type_name\" AS \"TABLE_TYPE\", "); 1977 query.append("\"table_types\".\"table_type_name\" AS \"TABLE_TYPE\", ");
1974 } 1978 }
1975 query.append("\"tables\".\"query\" AS \"REMARKS\", " + 1979 query.append(useCommentsTable ? "COALESCE(\"comments\".\"remark\", \"tables\".\"query\")" : "\"tables\".\"query\"").append(" AS \"REMARKS\", " +
1976 "cast(null as char(1)) AS \"TYPE_CAT\", " + 1980 "cast(null as char(1)) AS \"TYPE_CAT\", " +
1977 "cast(null as char(1)) AS \"TYPE_SCHEM\", " + 1981 "cast(null as char(1)) AS \"TYPE_SCHEM\", " +
1978 "cast(null as char(1)) AS \"TYPE_NAME\", " + 1982 "cast(null as char(1)) AS \"TYPE_NAME\", " +
1979 "cast(null as char(1)) AS \"SELF_REFERENCING_COL_NAME\", " + 1983 "cast(null as char(1)) AS \"SELF_REFERENCING_COL_NAME\", " +
1980 "cast(null as char(1)) AS \"REF_GENERATION\" " + 1984 "cast(null as char(1)) AS \"REF_GENERATION\" " +
1981 "FROM \"sys\".\"tables\", \"sys\".\"schemas\""); 1985 "FROM \"sys\".\"tables\"");
1986 if (useCommentsTable) {
1987 query.append(" LEFT OUTER JOIN \"sys\".\"comments\" ON (\"tables\".\"id\" = \"comments\".\"id\")");
1988 }
1989 query.append(", \"sys\".\"schemas\"");
1982 if (!preJul2015) { 1990 if (!preJul2015) {
1983 query.append(", \"sys\".\"table_types\""); 1991 query.append(", \"sys\".\"table_types\"");
1984 } 1992 }
1985 query.append(" WHERE \"tables\".\"schema_id\" = \"schemas\".\"id\""); 1993 query.append(" WHERE \"tables\".\"schema_id\" = \"schemas\".\"id\"");
1986 if (!preJul2015) { 1994 if (!preJul2015) {
2193 String schemaPattern, 2201 String schemaPattern,
2194 String tableNamePattern, 2202 String tableNamePattern,
2195 String columnNamePattern 2203 String columnNamePattern
2196 ) throws SQLException 2204 ) throws SQLException
2197 { 2205 {
2206 boolean useCommentsTable = ((MonetConnection)con).commentsTableExists();
2198 StringBuilder query = new StringBuilder(2450); 2207 StringBuilder query = new StringBuilder(2450);
2199 query.append("SELECT cast(null as char(1)) AS \"TABLE_CAT\", " + 2208 query.append("SELECT cast(null as char(1)) AS \"TABLE_CAT\", " +
2200 "\"schemas\".\"name\" AS \"TABLE_SCHEM\", " + 2209 "\"schemas\".\"name\" AS \"TABLE_SCHEM\", " +
2201 "\"tables\".\"name\" AS \"TABLE_NAME\", " + 2210 "\"tables\".\"name\" AS \"TABLE_NAME\", " +
2202 "\"columns\".\"name\" AS \"COLUMN_NAME\", " + 2211 "\"columns\".\"name\" AS \"COLUMN_NAME\", " +
2207 "\"columns\".\"type_scale\" AS \"DECIMAL_DIGITS\", " + 2216 "\"columns\".\"type_scale\" AS \"DECIMAL_DIGITS\", " +
2208 "cast(CASE WHEN \"columns\".\"type\" IN ('decimal', 'numeric', 'sec_interval') THEN 10 " + 2217 "cast(CASE WHEN \"columns\".\"type\" IN ('decimal', 'numeric', 'sec_interval') THEN 10 " +
2209 "WHEN \"columns\".\"type\" IN ('int', 'smallint', 'tinyint', 'bigint', 'hugeint', 'float', 'real', 'double', 'oid', 'wrd') THEN 2 " + 2218 "WHEN \"columns\".\"type\" IN ('int', 'smallint', 'tinyint', 'bigint', 'hugeint', 'float', 'real', 'double', 'oid', 'wrd') THEN 2 " +
2210 "ELSE 0 END AS int) AS \"NUM_PREC_RADIX\", " + 2219 "ELSE 0 END AS int) AS \"NUM_PREC_RADIX\", " +
2211 "cast(CASE \"null\" WHEN true THEN ").append(ResultSetMetaData.columnNullable) 2220 "cast(CASE \"null\" WHEN true THEN ").append(ResultSetMetaData.columnNullable)
2212 .append(" WHEN false THEN ").append(ResultSetMetaData.columnNoNulls).append(" END AS int) AS \"NULLABLE\", " + 2221 .append(" WHEN false THEN ").append(ResultSetMetaData.columnNoNulls).append(" END AS int) AS \"NULLABLE\", ")
2213 "cast(null AS varchar(1)) AS \"REMARKS\", " + 2222 .append(useCommentsTable ? "\"comments\".\"remark\"" : "cast(null AS varchar(1))").append(" AS \"REMARKS\", " +
2214 "\"columns\".\"default\" AS \"COLUMN_DEF\", " + 2223 "\"columns\".\"default\" AS \"COLUMN_DEF\", " +
2215 "cast(0 as int) AS \"SQL_DATA_TYPE\", " + 2224 "cast(0 as int) AS \"SQL_DATA_TYPE\", " +
2216 "cast(0 as int) AS \"SQL_DATETIME_SUB\", " + 2225 "cast(0 as int) AS \"SQL_DATETIME_SUB\", " +
2217 "cast(CASE WHEN \"columns\".\"type\" IN ('char','varchar','clob') THEN \"columns\".\"type_digits\" ELSE NULL END as int) AS \"CHAR_OCTET_LENGTH\", " + 2226 "cast(CASE WHEN \"columns\".\"type\" IN ('char','varchar','clob') THEN \"columns\".\"type_digits\" ELSE NULL END as int) AS \"CHAR_OCTET_LENGTH\", " +
2218 "cast(\"columns\".\"number\" + 1 as int) AS \"ORDINAL_POSITION\", " + 2227 "cast(\"columns\".\"number\" + 1 as int) AS \"ORDINAL_POSITION\", " +
2221 "cast(null AS varchar(1)) AS \"SCOPE_SCHEMA\", " + 2230 "cast(null AS varchar(1)) AS \"SCOPE_SCHEMA\", " +
2222 "cast(null AS varchar(1)) AS \"SCOPE_TABLE\", " + 2231 "cast(null AS varchar(1)) AS \"SCOPE_TABLE\", " +
2223 "cast(null AS smallint) AS \"SOURCE_DATA_TYPE\", " + 2232 "cast(null AS smallint) AS \"SOURCE_DATA_TYPE\", " +
2224 "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\", " + 2233 "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\", " +
2225 "cast('NO' AS varchar(3)) AS \"IS_GENERATEDCOLUMN\" " + 2234 "cast('NO' AS varchar(3)) AS \"IS_GENERATEDCOLUMN\" " +
2226 "FROM \"sys\".\"columns\", " + 2235 "FROM \"sys\".\"columns\"");
2227 "\"sys\".\"tables\", " + 2236 if (useCommentsTable) {
2228 "\"sys\".\"schemas\" " + 2237 query.append(" LEFT OUTER JOIN \"sys\".\"comments\" ON (\"columns\".\"id\" = \"comments\".\"id\")");
2229 "WHERE \"columns\".\"table_id\" = \"tables\".\"id\" " + 2238 }
2230 "AND \"tables\".\"schema_id\" = \"schemas\".\"id\""); 2239 query.append(", \"sys\".\"tables\"" +
2240 ", \"sys\".\"schemas\" " +
2241 "WHERE \"columns\".\"table_id\" = \"tables\".\"id\"" +
2242 " AND \"tables\".\"schema_id\" = \"schemas\".\"id\"");
2231 2243
2232 if (catalog != null && catalog.length() > 0) { 2244 if (catalog != null && catalog.length() > 0) {
2233 // none empty catalog selection. 2245 // none empty catalog selection.
2234 // as we do not support catalogs this always results in no rows returned 2246 // as we do not support catalogs this always results in no rows returned
2235 query.append(" AND 1 = 0"); 2247 query.append(" AND 1 = 0");
3275 } 3287 }
3276 query.append("SELECT cast(null as char(1)) AS \"TYPE_CAT\", " + 3288 query.append("SELECT cast(null as char(1)) AS \"TYPE_CAT\", " +
3277 "\"schemas\".\"name\" AS \"TYPE_SCHEM\", " + 3289 "\"schemas\".\"name\" AS \"TYPE_SCHEM\", " +
3278 "\"types\".\"sqlname\" AS \"TYPE_NAME\", " + 3290 "\"types\".\"sqlname\" AS \"TYPE_NAME\", " +
3279 "CASE \"types\".\"sqlname\"" + 3291 "CASE \"types\".\"sqlname\"" +
3280 // next 4 UDTs are known 3292 // next 4 UDTs are standard
3281 " WHEN 'inet' THEN 'nl.cwi.monetdb.jdbc.types.INET'" + 3293 " WHEN 'inet' THEN 'nl.cwi.monetdb.jdbc.types.INET'" +
3282 " WHEN 'json' THEN 'java.lang.String'" + 3294 " WHEN 'json' THEN 'java.lang.String'" +
3283 " WHEN 'url' THEN 'nl.cwi.monetdb.jdbc.types.URL'" + 3295 " WHEN 'url' THEN 'nl.cwi.monetdb.jdbc.types.URL'" +
3284 " WHEN 'uuid' THEN 'java.lang.String'" + 3296 " WHEN 'uuid' THEN 'java.lang.String'" +
3285 " ELSE 'java.lang.Object' END AS \"CLASS_NAME\", " + 3297 " ELSE 'java.lang.Object' END AS \"CLASS_NAME\", " +
3286 "CAST(CASE WHEN \"types\".\"sqlname\" IN ('inet', 'json', 'url', 'uuid') THEN ").append(Types.JAVA_OBJECT) 3298 "CAST(CASE WHEN \"types\".\"sqlname\" IN ('inet', 'json', 'url', 'uuid') THEN ").append(Types.JAVA_OBJECT)
3287 .append(" ELSE ").append(Types.STRUCT).append(" END AS int) AS \"DATA_TYPE\", " + 3299 .append(" ELSE ").append(Types.STRUCT).append(" END AS int) AS \"DATA_TYPE\", " +
3288 "\"types\".\"systemname\" AS \"REMARKS\", " + 3300 "\"types\".\"systemname\" AS \"REMARKS\", " +
3289 "cast(null as smallint) AS \"BASE_TYPE\" " + 3301 "cast(null as smallint) AS \"BASE_TYPE\" " +
3290 "FROM \"sys\".\"types\" JOIN \"sys\".\"schemas\" ON \"types\".\"schema_id\" = \"schemas\".\"id\" " + 3302 "FROM \"sys\".\"types\" JOIN \"sys\".\"schemas\" ON \"types\".\"schema_id\" = \"schemas\".\"id\" " +
3291 // exclude the built-in types (I assume they always have id <= 99 and eclass < 15) 3303 // exclude the built-in types (I assume they always have id <= 99 and eclass < 15)
3292 "WHERE \"types\".\"id\" > 99 AND \"types\".\"eclass\" >= 15"); 3304 "WHERE \"types\".\"id\" > 99 AND \"types\".\"eclass\" >= 15");
3293 3305
3294 if (catalog != null && catalog.length() > 0) { 3306 if (catalog != null && catalog.length() > 0) {
3295 // none empty catalog selection. 3307 // none empty catalog selection.
3296 // as we do not support catalogs this always results in no rows returned 3308 // as we do not support catalogs this always results in no rows returned
3297 query.append(" AND 1 = 0"); 3309 query.append(" AND 1 = 0");
3300 query.append(" AND \"schemas\".\"name\" ").append(composeMatchPart(schemaPattern)); 3312 query.append(" AND \"schemas\".\"name\" ").append(composeMatchPart(schemaPattern));
3301 } 3313 }
3302 if (typeNamePattern != null) { 3314 if (typeNamePattern != null) {
3303 query.append(" AND \"types\".\"sqlname\" ").append(composeMatchPart(typeNamePattern)); 3315 query.append(" AND \"types\".\"sqlname\" ").append(composeMatchPart(typeNamePattern));
3304 } 3316 }
3317
3305 if (types != null && types.length > 0) { 3318 if (types != null && types.length > 0) {
3306 query.append(") AS getUDTs WHERE \"DATA_TYPE\" IN ("); 3319 query.append(") AS getUDTs WHERE \"DATA_TYPE\" IN (");
3307 for (int i = 0; i < types.length; i++) { 3320 for (int i = 0; i < types.length; i++) {
3308 if (i > 0) { 3321 if (i > 0) {
3309 query.append(", "); 3322 query.append(", ");
3864 String catalog, 3877 String catalog,
3865 String schemaPattern, 3878 String schemaPattern,
3866 String functionNamePattern) 3879 String functionNamePattern)
3867 throws SQLException 3880 throws SQLException
3868 { 3881 {
3882 boolean useCommentsTable = ((MonetConnection)con).commentsTableExists();
3869 StringBuilder query = new StringBuilder(800); 3883 StringBuilder query = new StringBuilder(800);
3870 query.append("SELECT cast(null as varchar(1)) AS \"FUNCTION_CAT\", " + 3884 query.append("SELECT cast(null as varchar(1)) AS \"FUNCTION_CAT\", " +
3871 "\"schemas\".\"name\" AS \"FUNCTION_SCHEM\", " + 3885 "\"schemas\".\"name\" AS \"FUNCTION_SCHEM\", " +
3872 "\"functions\".\"name\" AS \"FUNCTION_NAME\", " + 3886 "\"functions\".\"name\" AS \"FUNCTION_NAME\", ")
3873 "cast(\"functions\".\"func\" as varchar(9999)) AS \"REMARKS\", " + 3887 .append(useCommentsTable ? "COALESCE(\"comments\".\"remark\", cast(\"functions\".\"func\" as varchar(9999)))" : "cast(\"functions\".\"func\" as varchar(9999))").append(" AS \"REMARKS\", " +
3874 "CASE \"functions\".\"type\"" + 3888 "CASE \"functions\".\"type\"" +
3875 " WHEN 1 THEN ").append(DatabaseMetaData.functionNoTable) 3889 " WHEN 1 THEN ").append(DatabaseMetaData.functionNoTable)
3876 .append(" WHEN 2 THEN ").append(DatabaseMetaData.functionNoTable) 3890 .append(" WHEN 2 THEN ").append(DatabaseMetaData.functionNoTable)
3877 .append(" WHEN 3 THEN ").append(DatabaseMetaData.functionNoTable) 3891 .append(" WHEN 3 THEN ").append(DatabaseMetaData.functionNoTable)
3878 .append(" WHEN 4 THEN ").append(DatabaseMetaData.functionNoTable) 3892 .append(" WHEN 4 THEN ").append(DatabaseMetaData.functionNoTable)
3879 .append(" WHEN 5 THEN ").append(DatabaseMetaData.functionReturnsTable) 3893 .append(" WHEN 5 THEN ").append(DatabaseMetaData.functionReturnsTable)
3880 .append(" ELSE ").append(DatabaseMetaData.functionResultUnknown).append(" END AS \"FUNCTION_TYPE\", " + 3894 .append(" ELSE ").append(DatabaseMetaData.functionResultUnknown).append(" END AS \"FUNCTION_TYPE\", " +
3881 // only the id value uniquely identifies a function. Include it to be able to differentiate between multiple overloaded functions with the same name 3895 // only the id value uniquely identifies a function. Include it to be able to differentiate between multiple overloaded functions with the same name
3882 "cast(\"functions\".\"id\" as varchar(10)) AS \"SPECIFIC_NAME\" " + 3896 "cast(\"functions\".\"id\" as varchar(10)) AS \"SPECIFIC_NAME\" " +
3883 "FROM \"sys\".\"functions\", \"sys\".\"schemas\" " + 3897 "FROM \"sys\".\"functions\" JOIN \"sys\".\"schemas\" ON (\"functions\".\"schema_id\" = \"schemas\".\"id\") ");
3884 "WHERE \"functions\".\"schema_id\" = \"schemas\".\"id\" " + 3898 if (useCommentsTable) {
3885 // exclude procedures (type = 2). Those need to be returned via getProcedures() 3899 query.append("LEFT OUTER JOIN \"sys\".\"comments\" ON (\"functions\".\"id\" = \"comments\".\"id\") ");
3886 "AND \"functions\".\"type\" <> 2"); 3900 }
3901 // only functions, so exclude procedures (type = 2). Those will to be returned via getProcedures()
3902 query.append("WHERE \"functions\".\"type\" <> 2");
3887 3903
3888 if (catalog != null && catalog.length() > 0) { 3904 if (catalog != null && catalog.length() > 0) {
3889 // none empty catalog selection. 3905 // none empty catalog selection.
3890 // as we do not support catalogs this always results in no rows returned 3906 // as we do not support catalogs this always results in no rows returned
3891 query.append(" AND 1 = 0"); 3907 query.append(" AND 1 = 0");
3992 "CAST(CASE WHEN \"args\".\"type\" IN ('char','varchar','clob') THEN \"args\".\"type_digits\" ELSE NULL END as int) AS \"CHAR_OCTET_LENGTH\", " + 4008 "CAST(CASE WHEN \"args\".\"type\" IN ('char','varchar','clob') THEN \"args\".\"type_digits\" ELSE NULL END as int) AS \"CHAR_OCTET_LENGTH\", " +
3993 "CAST(\"args\".\"number\" as int) AS \"ORDINAL_POSITION\", " + 4009 "CAST(\"args\".\"number\" as int) AS \"ORDINAL_POSITION\", " +
3994 "CAST('' as varchar(3)) AS \"IS_NULLABLE\", " + 4010 "CAST('' as varchar(3)) AS \"IS_NULLABLE\", " +
3995 // the specific name contains the function id, in order to be able to match the args to the correct overloaded function name 4011 // the specific name contains the function id, in order to be able to match the args to the correct overloaded function name
3996 "cast(\"functions\".\"id\" as varchar(10)) AS \"SPECIFIC_NAME\" " + 4012 "cast(\"functions\".\"id\" as varchar(10)) AS \"SPECIFIC_NAME\" " +
3997 "FROM \"sys\".\"args\", \"sys\".\"functions\", \"sys\".\"schemas\" " + 4013 "FROM \"sys\".\"args\" " +
3998 "WHERE \"args\".\"func_id\" = \"functions\".\"id\" " + 4014 "JOIN \"sys\".\"functions\" ON (\"args\".\"func_id\" = \"functions\".\"id\") " +
3999 "AND \"functions\".\"schema_id\" = \"schemas\".\"id\" " + 4015 "JOIN \"sys\".\"schemas\" ON (\"functions\".\"schema_id\" = \"schemas\".\"id\") " +
4000 // exclude procedures (type = 2). Those need to be returned via getProcedureColumns() 4016 // only functions, so exclude procedures (type = 2). Those will to be returned via getProcedureColumns()
4001 "AND \"functions\".\"type\" <> 2"); 4017 "WHERE \"functions\".\"type\" <> 2");
4002 4018
4003 if (catalog != null && catalog.length() > 0) { 4019 if (catalog != null && catalog.length() > 0) {
4004 // none empty catalog selection. 4020 // none empty catalog selection.
4005 // as we do not support catalogs this always results in no rows returned 4021 // as we do not support catalogs this always results in no rows returned
4006 query.append(" AND 1 = 0"); 4022 query.append(" AND 1 = 0");