Mercurial > hg > monetdb-java
changeset 365:63cccacad468
Add sys.ms_stuff() to returned list of StringFunctions.
| author | Martin van Dinther <martin.van.dinther@monetdbsolutions.com> |
|---|---|
| date | Wed, 16 Sep 2020 18:56:18 +0200 (2020-09-16) |
| parents | 37f54743b1fd |
| children | 83bfade6333e |
| files | src/main/java/nl/cwi/monetdb/client/JdbcClient.java src/main/java/nl/cwi/monetdb/jdbc/MonetDatabaseMetaData.java |
| diffstat | 2 files changed, 1046 insertions(+), 4 deletions(-) [+] |
line wrap: on
line diff
--- a/src/main/java/nl/cwi/monetdb/client/JdbcClient.java +++ b/src/main/java/nl/cwi/monetdb/client/JdbcClient.java @@ -26,9 +26,11 @@ import java.sql.Connection; import java.sql.DriverManager; // required as it will load the nl.cwi.monetdb.jdbc.MonetDriver class import java.sql.DatabaseMetaData; import java.sql.ResultSet; +import java.sql.ResultSetMetaData; import java.sql.Statement; import java.sql.SQLException; import java.sql.SQLWarning; +import java.sql.Types; import java.util.ArrayList; import java.util.LinkedList; import java.util.List; @@ -41,7 +43,7 @@ import java.util.List; * of JDBC only. * * @author Fabian Groffen, Martin van Dinther - * @version 1.4 + * @version 1.5 */ public final class JdbcClient { @@ -779,6 +781,8 @@ public final class JdbcClient { if (tbl != null) tbl.close(); } + } else if (command.equals("\\check_system_catalog_integrity")) { + MDBvalidator.check_system_catalog_integrity(con); } else if (command.startsWith("\\l") || command.startsWith("\\i")) { String object = command.substring(2).trim(); if (scolonterm && object.endsWith(";")) @@ -1301,3 +1305,1041 @@ final class Table { } } } + +/** + * MonetDB Data Integrity Validator program (MDBvalidator) can + * a) check system tables data integrity (in system schemas: sys, tmp, json, profiler, and possibly more depending on MonetDB version) + * this includes violations of: + * primary key uniqueness + * primary key column(s) not null + * unique constraint uniqueness (alternate keys) + * foreign key referential integrity (match / partial) + * column not null + * column maximum length for char/varchar/clob/blob/json/url + * b) check user schema tables & columns data integrity based on available meta data from system tables/views + * column not null + * column maximum length for char/varchar/clob/blob/json/url + * TODO primary key uniqueness + * TODO primary key column(s) not null + * TODO unique constraint uniqueness (alternate keys) + * TODO foreign key referential integrity (match / partial) + * + * designed and created by Martin van Dinther + */ + +final class MDBvalidator { + private static final String prg = "MDBvalidator"; + private static Connection con; + private static int majorversion; + private static int minorversion; + + private static boolean verbose = false; // set it to true for tracing all generated SQL queries +// private static boolean use_log_table = false; // Not Yet Supported (and maybe not needed) + +// MDBvalidator() {} + + public static void main(String[] args) throws Exception { + System.out.println(prg + " started with " + args.length + " arguments." + (args.length == 0 ? " Using default JDBC URL !" : "")); + // parse input args: connection (JDBC_URL), check systbls (default) or user schema or user db + + String JDBC_URL = (args.length > 0) ? args[0] + : "jdbc:monetdb://localhost:50000/demo?user=monetdb&password=monetdb&so_timeout=14000"; // &treat_clob_as_varchar=true"; + if (!JDBC_URL.startsWith("jdbc:monetdb://")) { + System.out.println("ERROR: Invalid JDBC URL. It does not start with jdbc:monetdb:"); + return; + } + try { + // make connection to target server + con = java.sql.DriverManager.getConnection(JDBC_URL); + System.out.println(prg + " connected to MonetDB server"); + printExceptions(con.getWarnings()); + + check_system_catalog_integrity(con); + } catch (SQLException e) { + printExceptions(e); + } + + // free resources + if (con != null) { + try { con.close(); } catch (SQLException e) { /* ignore */ } + } + } + + static void check_system_catalog_integrity(Connection conn) { + long start_time = System.currentTimeMillis(); + try { + con = conn; + // retrieve server version numbers (major and minor). These are needed to filter out version specific validations + DatabaseMetaData dbmd = con.getMetaData(); + if (dbmd != null) { + majorversion = dbmd.getDatabaseMajorVersion(); + minorversion = dbmd.getDatabaseMinorVersion(); + System.out.println("MonetDB server version " + dbmd.getDatabaseProductVersion()); + // validate majorversion (should be 11) and minorversion (should be >= 19) (from Jul2015 (11.19.15)) + if (majorversion < 11 || (majorversion == 11 && minorversion < 19)) { + System.out.println("ERROR: this MonetDB server is too old for " + prg + ". Please upgrade server."); + con.close(); + return; + } + } + String cur_schema = con.getSchema(); + if (!"sys".equals(cur_schema)) + con.setSchema("sys"); + + verify("sys", null, sys_pkeys, sys_akeys, sys_fkeys, sys_notnull, true); + verify("tmp", null, tmp_pkeys, tmp_akeys, tmp_fkeys, tmp_notnull, true); + + // first determine if netcdf tables (sys.netcdf_files, sys.netcdf_dims, sys.netcdf_vars, sys.netcdf_vardim) exist in the db + if (false) { // ToDo built check for existance of the 5 netcdf tables in sys + verify("sys", "netcdf", netcdf_pkeys, netcdf_akeys, netcdf_fkeys, netcdf_notnull, false); + } + + // first determine if geom tables (sys.spatial_ref_sys) exist in the db + if (true) { // ToDo built check for existance of the 2 geom tables in sys + verify("sys", "geom", geom_pkeys, geom_akeys, geom_fkeys, geom_notnull, false); + } + } catch (SQLException e) { + printExceptions(e); + } + + long elapsed = System.currentTimeMillis() - start_time; + long secs = elapsed /1000; + System.out.println("Validation completed in " + secs + "s and " + (elapsed - (secs *1000)) + "ms"); + } + + private static void verify(String schema, String group, String[][] pkeys, String[][] akeys, String[][] fkeys, String[][] colnotnull, boolean checkmaxstr) { + boolean is_system_schema = ("sys".equals(schema) || "tmp".equals(schema)); + if (pkeys != null) + verifyUniqueness(schema, group, pkeys, "Primary Key uniqueness"); + if (pkeys != null) + verifyNotNull(schema, group, pkeys, "Primary Key Not Null"); + if (akeys != null) + verifyUniqueness(schema, group, akeys, "Alternate Key uniqueness"); + if (fkeys != null) + verifyFKs(schema, group, fkeys, "Foreign Key referential integrity"); + if (colnotnull != null) + verifyNotNull(schema, group, colnotnull, "Not Null"); + else + verifyNotNull(schema, is_system_schema, "Not Null"); + if (checkmaxstr) + verifyMaxCharStrLength(schema, is_system_schema, "Max Character Length"); +/* TODO + * col char/varchar/clob/blob/json/url minimum length (some columns may not be empty, so length >= 1) + * col with sequence (serial/bigserial/autoincrement) in range (0/1/min_value .. max_value) + * col domain is valid (date/time/timestamp/json/inet/url/uuid/...) + * col in list checks (some columns may have only certain values which are not recorded somewhere (eg as fk)) + * col conditional checks (column is not null when other column is (not) null) + -- either column_id or expression in sys.table_partitions must be populated + SELECT "column_id", "expression", 'Missing either column_id or expression' AS violation, * FROM "sys"."table_partitions" WHERE "column_id" IS NULL AND "expression" IS NULL; + SELECT "column_id", "expression", 'column_id and expression may not both be populated. One of them must be NULL' AS violation, * FROM "sys"."table_partitions" WHERE "column_id" IS NOT NULL AND "expression" IS NOT NULL; + */ + } + + private static void verifyUniqueness(String schema, String group, String[][] data, String check_type) { + final int len = data.length; + System.out.println("Checking " + len + (group != null ? " " + group : "") + " tables in schema " + schema + " for " + check_type + " violations."); + + StringBuilder sb = new StringBuilder(256); // reusable buffer to compose SQL validation queries + sb.append("SELECT COUNT(*) AS duplicates, "); + final int qry_len = sb.length(); + String tbl; + String keycols; + for (int i = 0; i < len; i++) { + if (isValidVersion(data[i][2])) { + tbl = data[i][0]; + keycols = data[i][1]; + // reuse the StringBuilder by cleaning it partial + sb.setLength(qry_len); + sb.append(keycols).append(" FROM "); + if (!tbl.startsWith("(")) { // when tbl starts with ( it is a unioned table set which we cannot prefix with a schema name qualifier + sb.append(schema).append('.'); + } + sb.append(tbl) + .append(" GROUP BY ").append(keycols) + .append(" HAVING COUNT(*) > 1;"); + validate(sb.toString(), schema, tbl, keycols, check_type); + } + } + } + + private static void verifyNotNull(String schema, String group, String[][] data, String check_type) { + final int len = data.length; + System.out.println("Checking " + len + (group != null ? " " + group : "") + " columns in schema " + schema + " for " + check_type + " violations."); + + StringBuilder sb = new StringBuilder(256); // reusable buffer to compose SQL validation queries + sb.append("SELECT "); + final int qry_len = sb.length(); + String tbl; + String col; + boolean multicolumn = false; + StringBuilder isNullCond = new StringBuilder(80); + for (int i = 0; i < len; i++) { + if (isValidVersion(data[i][2])) { + tbl = data[i][0]; + col = data[i][1]; + multicolumn = col.contains(", "); // some pkeys consist of multiple columns + isNullCond.setLength(0); // empty previous content + if (multicolumn) { + String[] cols = col.split(", "); + for (int c = 0; c < cols.length; c++) { + if (c > 0) { + isNullCond.append(" OR "); + } + isNullCond.append(cols[c]).append(" IS NULL"); + } + } else { + isNullCond.append(col).append(" IS NULL"); + } + // reuse the StringBuilder by cleaning it partial + sb.setLength(qry_len); + sb.append(col) + .append(", * FROM ").append(schema).append('.').append(tbl) + .append(" WHERE ").append(isNullCond).append(';'); + validate(sb.toString(), schema, tbl, col, check_type); + } + } + } + + private static void verifyFKs(String schema, String group, String[][] data, String check_type) { + final int len = data.length; + System.out.println("Checking " + len + (group != null ? " " + group : "") + " foreign keys in schema " + schema + " for " + check_type + " violations."); + + StringBuilder sb = new StringBuilder(400); // reusable buffer to compose SQL validation queries + sb.append("SELECT "); + final int qry_len = sb.length(); + String tbl; + String cols; + String ref_tbl; + String ref_cols; + for (int i = 0; i < len; i++) { + if (isValidVersion(data[i][4])) { + tbl = data[i][0]; + cols = data[i][1]; + ref_cols = data[i][2]; + ref_tbl = data[i][3]; + // reuse the StringBuilder by cleaning it partial + sb.setLength(qry_len); + sb.append(cols).append(", * FROM ").append(schema).append('.').append(tbl); + if (!tbl.contains(" WHERE ")) + sb.append(" WHERE "); + sb.append('(').append(cols).append(") NOT IN (SELECT ").append(ref_cols).append(" FROM "); + if (!ref_tbl.contains(".")) + sb.append(schema).append('.'); + sb.append(ref_tbl).append(");"); + validate(sb.toString(), schema, tbl, cols, check_type); + } + } + } + + private static void verifyNotNull(String schema, boolean system, String check_type) { + // fetch the NOT NULL info from the MonetDB system tables as those are leading for user tables (but not system tables) + StringBuilder sb = new StringBuilder(400); + sb.append(" from sys.columns c join sys.tables t on c.table_id = t.id join sys.schemas s on t.schema_id = s.id" + + " where t.type in (0, 10, 1, 11) and c.\"null\" = false" // t.type 0 = TABLE, 10 = SYSTEM TABLE, 1 = VIEW, 11 = SYSTEM VIEW + + " and t.system = ").append(system) + .append(" and s.name = '").append(schema).append("'"); + String qry = sb.toString(); + long count = runCountQuery(qry); + System.out.println("Checking " + count + " columns in schema " + schema + " for " + check_type + " violations."); + + Statement stmt = createStatement("verifyMaxCharStrLength()"); + ResultSet rs = null; + try { + sb.setLength(0); // empty previous usage of sb + sb.append("SELECT s.name as sch_nm, t.name as tbl_nm, c.name as col_nm") // , t.type, t.system, c.type, c.type_digits + .append(qry).append(" ORDER BY s.name, t.name, c.name;"); + qry = sb.toString(); + if (stmt != null) + rs = stmt.executeQuery(qry); + if (rs != null) { + String sch, tbl, col; + while (rs.next()) { + // retrieve meta data + sch = rs.getString(1); + tbl = rs.getString(2); + col = rs.getString(3); + // compose validation query for this specific column + sb.setLength(0); // empty previous usage of sb + sb.append("SELECT '").append(sch).append('.').append(tbl).append('.').append(col).append("' as full_col_nm, *") + .append(" FROM \"").append(sch).append("\".\"").append(tbl).append("\"") + .append(" WHERE \"").append(col).append("\" IS NULL;"); + validate(sb.toString(), sch, tbl, col, check_type); + } + } + } catch (SQLException e) { + System.out.println("Failed to execute query: " + qry); + printExceptions(e); + } + freeStmtRs(stmt, rs); + } + + private static void verifyMaxCharStrLength(String schema, boolean system, String check_type) { + // fetch the max char str len info from the MonetDB system tables as those are leading + StringBuilder sb = new StringBuilder(400); + sb.append(" from sys.columns c join sys.tables t on c.table_id = t.id join sys.schemas s on t.schema_id = s.id" + + " where t.type in (0, 10, 1, 11) and c.type_digits >= 1" // t.type 0 = TABLE, 10 = SYSTEM TABLE, 1 = VIEW, 11 = SYSTEM VIEW + + " and t.system = ").append(system) + .append(" and s.name = '").append(schema).append("'") + .append(" and c.type in ('varchar', 'char', 'clob', 'json', 'url', 'blob')"); // only for variable character/bytes data type columns + String qry = sb.toString(); + long count = runCountQuery(qry); + System.out.println("Checking " + count + " columns in schema " + schema + " for " + check_type + " violations."); + + Statement stmt = createStatement("verifyMaxCharStrLength()"); + ResultSet rs = null; + try { + sb.setLength(0); // empty previous usage of sb + sb.append("SELECT s.name as sch_nm, t.name as tbl_nm, c.name as col_nm, c.type_digits") // , t.type, t.system, c.type + .append(qry).append(" ORDER BY s.name, t.name, c.name, c.type_digits;"); + qry = sb.toString(); + if (stmt != null) + rs = stmt.executeQuery(qry); + if (rs != null) { + long max_len = 0; + String sch, tbl, col; + while (rs.next()) { + // retrieve meta data + sch = rs.getString(1); + tbl = rs.getString(2); + col = rs.getString(3); + max_len = rs.getLong(4); + // compose validation query for this specific column + sb.setLength(0); // empty previous usage of sb + sb.append("SELECT '").append(sch).append('.').append(tbl).append('.').append(col).append("' as full_col_nm, ") + .append(max_len).append(" as max_allowed_length, ") + .append("length(\"").append(col).append("\") as data_length, ") + .append("\"").append(col).append("\" as data_value") + .append(" FROM \"").append(sch).append("\".\"").append(tbl).append("\"") + .append(" WHERE \"").append(col).append("\" IS NOT NULL AND length(\"").append(col).append("\") > ").append(max_len); + validate(sb.toString(), sch, tbl, col, check_type); + } + } + } catch (SQLException e) { + System.out.println("Failed to execute query: " + qry); + printExceptions(e); + } + freeStmtRs(stmt, rs); + } + + + /* Run a validation query. + * It should result in no rows returned. + * When rows are returned those are the ones that contain violations. + * Retrieve them and convert the results into a (large) violation string + * Log/Print the violation. + */ + private static void validate(String qry, String sch, String tbl, String cols, String checktype) { + Statement stmt = createStatement("validate()"); + ResultSet rs = null; + ResultSetMetaData rsmd; + StringBuilder sb = new StringBuilder(1024); + int nr_cols; + int row; + String val; + int tp; + try { + if (verbose) { + System.out.println(qry); + } + if (stmt != null) + rs = stmt.executeQuery(qry); + if (rs != null) { + rsmd = rs.getMetaData(); + nr_cols = rsmd.getColumnCount(); + sb.setLength(0); // empty previous usage of sb + row = 0; + while (rs.next()) { + // query returns found violations + row++; + if (row == 1) { + // print result header once + for (int i = 1; i <= nr_cols; i++) { + sb.append((i > 1) ? ", " : "\t"); + sb.append(rsmd.getColumnLabel(i)); + } + sb.append('\n'); + } + // retrieve row data + for (int i = 1; i <= nr_cols; i++) { + sb.append((i > 1) ? ", " : "\t"); + val = rs.getString(i); + if (val == null || rs.wasNull()) { + sb.append("null"); + } else { + tp = rsmd.getColumnType(i); // this method is very fast, so no need to cache it outside the loop + if (tp == Types.VARCHAR || tp == Types.CHAR || tp == Types.CLOB || tp == Types.BLOB) { + sb.append('"').append(val).append('"'); + } else { + sb.append(val); + } + } + } + sb.append('\n'); + } + if (row > 0) + logViolations(checktype, sch, tbl, cols, qry, sb.toString()); + } + } catch (SQLException e) { + System.out.println("Failed to execute query: " + qry); + printExceptions(e); + } + freeStmtRs(stmt, rs); + } + + private static long runCountQuery(String from_qry) { + Statement stmt = createStatement("runCountQuery()"); + ResultSet rs = null; + long count = 0; + try { + if (stmt != null) + rs = stmt.executeQuery("SELECT COUNT(*) " + from_qry); + if (rs != null) { + if (rs.next()) { + // retrieve count data + count = rs.getLong(1); + } + } + } catch (SQLException e) { + System.out.println("Failed to execute select count(*) " + from_qry); + printExceptions(e); + } + freeStmtRs(stmt, rs); + return count; + } + + private static Statement createStatement(String method) { + try { + return con.createStatement(); + } catch (SQLException e) { + System.out.print("Failed to create Statement in " + method); + printExceptions(e); + } + return null; + } + + // private static java.text.SimpleDateFormat df = new java.text.SimpleDateFormat("yyyy-MM-dd' 'HH:mm:ss.SSS"); + private static void logViolations(String checktype, String schema, String table, String columns, String query, String violations) { +// String dts = df.format(new java.util.Date()); + StringBuilder sb = new StringBuilder(8192); +// sb.append(dts).append('\t') + sb.append(checktype).append(" violation(s) found in \"") + .append(schema).append("\".\"").append(table).append("\".\"").append(columns).append("\":\n") + .append(violations) + .append("Found using query: ").append(query).append("\n"); + System.out.println(sb.toString()); + } + + private static boolean isValidVersion(String version) { + if (version != null) { + try { + int v = Integer.parseInt(version); + return minorversion >= v; + } catch (NumberFormatException e) { + /* ignore */ + } + return false; + } + return true; // when no version string is supplied it is valid by default + } + + /* test if the standard log table already exists in target DB. + * if not try to create it. + */ +/* private static boolean create_log_table(Connection con) { + private static final String log_tbl_name = "sys.\"" + prg + "_log\""; + boolean has_log_table = false; + Statement stmt = createStatement("create_log_table()"); + ResultSet rs = null; + try { + rs = stmt.executeQuery("SELECT * FROM " + log_tbl_name + " LIMIT 1;"); + if (rs != null) { + if (rs.next()) { + has_log_table = true; + // ToDo check that the columns are the correct signature + } + } + } catch (SQLException e0) { + // we will get here when the log table does not exist yet + try { + // create it ourselves + int upd = stmt.executeUpdate("CREATE TABLE " + log_tbl_name + " IF NOT EXISTS " + + "(datetime TIMESTAMP(6) NOT NULL," + + " schema_nm VARCHAR(1024) NOT NULL," + + " table_nm VARCHAR(1024) NOT NULL," + + " column_nm VARCHAR(10240) NOT NULL," + + " validation VARCHAR(99999) NOT NULL," + + " violations VARCHAR(99999) NOT NULL);"); + if (upd >= 0) + has_log_table = true; + } catch (SQLException e) { + System.out.print("Failed to create table " + log_tbl_name); + printExceptions(e); + } + } + freeStmtRs(stmt, rs); + return has_log_table; + } +*/ + + private static void printExceptions(SQLException se) { + while (se != null) { + System.out.println(se.getSQLState() + " " + se.getMessage()); + se = se.getNextException(); + } + } + + private static void freeStmtRs(Statement stmt, ResultSet rs) { + // free resources + if (rs != null) { + try { rs.close(); } catch (SQLException e) { /* ignore */ } + } + if (stmt != null) { + try { stmt.close(); } catch (SQLException e) { /* ignore */ } + } + } + + +// ********* below are multiple 2-dimensional String arrays containing the data for constructing the validation queries ********* + // based on data from: https://dev.monetdb.org/hg/MonetDB/file/Jun2020/sql/test/sys-schema/Tests + + // static list of all sys tables with its pkey columns + // each entry contains: table_nm, pk_col_nms, from_minor_version + // data pulled from https://dev.monetdb.org/hg/MonetDB/file/Jun2020/sql/test/sys-schema/Tests/check_PrimaryKey_uniqueness.sql + private static final String[][] sys_pkeys = { + {"schemas", "id", null}, + {"table_types", "table_type_id", null}, + {"_tables", "id", null}, + {"tables", "id", null}, // is a view + {"_columns", "id", null}, + {"columns", "id", null}, // is a view + {"function_types", "function_type_id", null}, + {"function_languages", "language_id", null}, + {"functions", "id", null}, + {"systemfunctions", "function_id", null}, // has become a view in Mar2018 and maybe removed in the future as is deprecated + {"args", "id", null}, + {"types", "id", null}, + {"objects", "id, nr", null}, + {"key_types", "key_type_id", null}, + {"keys", "id", null}, + {"index_types", "index_type_id", null}, + {"idxs", "id", null}, + {"triggers", "id", null}, + {"sequences", "id", null}, + {"comments", "id", null}, + {"dependency_types", "dependency_type_id", null}, + {"dependencies", "id, depend_id", null}, + {"ids", "id", null}, // is a view + {"auths", "id", null}, + {"users", "name", null}, + {"user_role", "login_id, role_id", null}, + {"privilege_codes", "privilege_code_id", null}, + {"privileges", "obj_id, auth_id, privileges", null}, + {"querylog_catalog", "id", null}, + {"querylog_calls", "id", null}, + {"querylog_history", "id", null}, +// old {"queue", "qtag", null}, // queue has changed in Jun2020 (11.37.7), pkey was previously qtag + {"queue", "tag", "37"}, // queue has changed in Jun2020 (11.37.7), pkey is now called tag + {"optimizers", "name", null}, + {"environment", "name", null}, + {"keywords", "keyword", null}, + {"db_user_info", "name", null}, +// old {"sessions", "\"user\", login, active", null}, // sessions has changed in Jun2020 (11.37.7), pkey was previously "user", login, active + {"sessions", "sessionid", "37"}, // sessions has changed in Jun2020 (11.37.7), pkey is now called sessionid + {"statistics", "column_id", null}, + {"rejects", "rowid", "19"}, // querying this view caused problems in versions pre Jul2015, see https://www.monetdb.org/bugzilla/show_bug.cgi?id=3794 +// old {"tracelog", "event", null}, -- Error: Profiler not started. This table now (Jun2020) contains only: ticks, stmt +// {"storage", "schema, table, column", null}, // is a view on storage() + {"\"storage\"()", "schema, table, column", null}, // the function "storage"() also lists the storage for system tables + // new views introduced in Apr 2019 feature release (11.33.3) +// {"tablestorage", "schema, table", "33"}, // is a view on view storage +// {"schemastorage", "schema", "33"}, // is a view on view storage + {"storagemodelinput", "schema, table, column", null}, +// {"storagemodel", "schema, table, column", null}, // is a view on storagemodelinput +// {"tablestoragemodel", "schema, table", null}, // is a view on storagemodelinput + // new tables introduced in Apr 2019 feature release (11.33.3) + {"table_partitions", "id", "33"}, + {"range_partitions", "table_id, partition_id, minimum", "33"}, + {"value_partitions", "table_id, partition_id, \"value\"", "33"} + }; + + private static final String[][] tmp_pkeys = { + {"_tables", "id", null}, + {"_columns", "id", null}, + {"objects", "id, nr", null}, + {"keys", "id", null}, + {"idxs", "id", null}, + {"triggers", "id", null} + }; + + private static final String[][] netcdf_pkeys = { + {"netcdf_files", "file_id", null}, + {"netcdf_dims", "dim_id, file_id", null}, + {"netcdf_vars", "var_id, file_id", null}, + {"netcdf_vardim", "var_id, dim_id, file_id", null} + }; + + private static final String[][] geom_pkeys = { + {"spatial_ref_sys", "srid", null} + }; + + + // static list of all sys tables with its alternate key (unique constraint) columns + // each entry contains: table_nm, ak_col_nms, from_minor_version + // data pulled from https://dev.monetdb.org/hg/MonetDB/file/Jun2020/sql/test/sys-schema/Tests/check_AlternateKey_uniqueness.sql + private static final String[][] sys_akeys = { + {"schemas", "name", null}, + {"table_types", "table_type_name", null}, + {"_tables", "schema_id, name", null}, + {"tables", "schema_id, name", null}, // is a view + {"_columns", "table_id, name", null}, + {"columns", "table_id, name", null}, // is a view + {"_columns", "table_id, number", null}, + {"columns", "table_id, number", null}, // is a view + // The id values from sys.schemas, sys._tables, sys._columns and sys.functions combined must be exclusive (see FK from sys.privileges.obj_id) + {"(SELECT id FROM sys.schemas UNION ALL SELECT id FROM sys._tables UNION ALL SELECT id FROM sys._columns UNION ALL SELECT id FROM sys.functions) T", "T.id", null}, + {"(SELECT id FROM sys.schemas UNION ALL SELECT id FROM sys.tables UNION ALL SELECT id FROM sys.columns UNION ALL SELECT id FROM sys.functions) T", "T.id", null}, + {"function_types", "function_type_name", null}, + {"function_languages", "language_name", null}, + // the next query used to return duplicates for overloaded functions (same function but with different arg names/types), hence it has been extended + {"functions f join sys.args a on f.id=a.func_id", "schema_id, f.name, func, mod, language, f.type, side_effect, varres, vararg, a.id", null}, + {"args", "func_id, name, inout", null}, + {"types", "schema_id, systemname, sqlname", null}, + {"objects", "id, name", null}, + {"key_types", "key_type_name", null}, + {"keys", "table_id, name", null}, + {"index_types", "index_type_name", null}, + {"idxs", "table_id, name", null}, + {"triggers", "table_id, name", null}, + {"sequences", "schema_id, name", null}, + {"comments", "id", null}, + {"dependency_types", "dependency_type_name", null}, + {"auths", "name", null}, // is this always unique?? isn't it possible to define a user and a role with the same name? + {"privilege_codes", "privilege_code_name", null}, + {"optimizers", "def", null}, + // new tables introduced in Apr 2019 feature release (11.33.3) + {"table_partitions WHERE column_id IS NOT NULL", "table_id, column_id", "33"}, // requires WHERE "column_id" IS NOT NULL + {"table_partitions WHERE \"expression\" IS NOT NULL", "table_id, \"expression\"", "33"}, // requires WHERE "expression" IS NOT NULL + {"range_partitions", "table_id, partition_id, \"maximum\"", "33"} + }; + + private static final String[][] tmp_akeys = { + {"_tables", "schema_id, name", null}, + {"_columns", "table_id, name", null}, + {"_columns", "table_id, number", null}, + {"objects", "id, name", null}, + {"keys", "table_id, name", null}, + {"idxs", "table_id, name", null}, + {"triggers", "table_id, name", null} + }; + + private static final String[][] netcdf_akeys = { + {"netcdf_files", "location", null} + }; + + private static final String[][] geom_akeys = { + {"spatial_ref_sys", "auth_name, auth_srid, srtext, proj4text", null} + }; + + + // static list of all sys tables with its foreign key columns + // each entry contains: table_nm, fk_col_nms, ref_col_nms, ref_tbl_nm, from_minor_version + // data pulled from https://dev.monetdb.org/hg/MonetDB/file/Jun2020/sql/test/sys-schema/Tests/check_ForeignKey_referential_integrity.sql + private static final String[][] sys_fkeys = { + {"schemas", "authorization", "id", "auths", null}, + {"schemas", "owner", "id", "auths", null}, + {"_tables", "schema_id", "id", "schemas", null}, + {"tables", "schema_id", "id", "schemas", null}, + {"_tables", "type", "table_type_id", "table_types", null}, + {"tables", "type", "table_type_id", "table_types", null}, + {"_columns", "table_id", "id", "_tables", null}, + {"columns", "table_id", "id", "tables", null}, + {"_columns", "type", "sqlname", "types", null}, + {"columns", "type", "sqlname", "types", null}, + {"functions", "schema_id", "id", "schemas", null}, + {"functions", "type", "function_type_id", "function_types", null}, + {"functions", "language", "language_id", "function_languages", null}, + // system functions should refer only to functions in MonetDB system schemas (on Dec2016 these are: sys, json, profiler and bam) + {"functions WHERE system AND ", "schema_id", "id", "schemas WHERE system", null}, + {"args", "func_id", "id", "functions", null}, + {"args", "type", "sqlname", "types", null}, + {"types", "schema_id", "id", "schemas", null}, + // {"types WHERE schema_id <> 0 AND ", "schema_id", "id", "schemas", null}, + {"objects", "id", "id", "ids", null}, + {"ids WHERE obj_type IN ('key', 'index') AND ", "id", "id", "objects", null}, + {"keys", "id", "id", "objects", null}, + {"keys", "table_id", "id", "_tables", null}, + {"keys", "table_id", "id", "tables", null}, + {"keys", "type", "key_type_id", "key_types", null}, + {"keys WHERE rkey <> -1 AND ", "rkey", "id", "keys", null}, +// SELECT * FROM sys.keys WHERE action <> -1 AND action NOT IN (SELECT id FROM sys.?); -- TODO: find out which action values are valid and what they mean. + {"idxs", "id", "id", "objects", null}, + {"idxs", "table_id", "id", "_tables", null}, + {"idxs", "table_id", "id", "tables", null}, + {"idxs", "type", "index_type_id", "index_types", null}, + {"sequences", "schema_id", "id", "schemas", null}, + {"triggers", "table_id", "id", "_tables", null}, + {"triggers", "table_id", "id", "tables", null}, + {"comments", "id", "id", "ids", null}, + {"dependencies", "id", "id", "ids", null}, + {"dependencies", "depend_id", "id", "ids", null}, + {"dependencies", "depend_type", "dependency_type_id", "dependency_types", null}, + {"dependencies", "id, depend_id, depend_type", "v.id, v.used_by_id, v.depend_type", "dependencies_vw v", null}, + {"auths WHERE grantor > 0 AND ", "grantor", "id", "auths", null}, + {"users", "name", "name", "auths", null}, + {"users", "default_schema", "id", "schemas", null}, + {"db_user_info", "name", "name", "auths", null}, + {"db_user_info", "default_schema", "id", "schemas", null}, + {"user_role", "login_id", "id", "auths", null}, + {"user_role", "login_id", "a.id", "auths a WHERE a.name IN (SELECT u.name FROM sys.users u)", null}, + {"user_role", "role_id", "id", "auths", null}, + {"user_role", "role_id", "a.id", "auths a WHERE a.name IN (SELECT u.name FROM sys.users u)", null}, + {"user_role", "role_id", "id", "roles", null} +/* hier verder afmaken +SELECT * FROM sys.privileges WHERE obj_id NOT IN (SELECT id FROM sys.schemas UNION ALL SELECT id FROM sys._tables UNION ALL SELECT id FROM sys._columns UNION ALL SELECT id FROM sys.functions); +SELECT * FROM sys.privileges WHERE auth_id NOT IN (SELECT id FROM sys.auths); +SELECT * FROM sys.privileges WHERE grantor NOT IN (SELECT id FROM sys.auths) AND grantor > 0; +SELECT * FROM sys.privileges WHERE privileges NOT IN (SELECT privilege_code_id FROM sys.privilege_codes); +--SELECT * FROM sys.privileges WHERE privileges NOT IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,32); -- old check before table sys.privilege_codes existed + +SELECT * FROM sys.querylog_catalog WHERE owner NOT IN (SELECT name FROM sys.users); +SELECT * FROM sys.querylog_catalog WHERE pipe NOT IN (SELECT name FROM sys.optimizers); +SELECT * FROM sys.querylog_calls WHERE id NOT IN (SELECT id FROM sys.querylog_catalog); +SELECT * FROM sys.querylog_history WHERE id NOT IN (SELECT id FROM sys.querylog_catalog); +SELECT * FROM sys.querylog_history WHERE owner NOT IN (SELECT name FROM sys.users); +SELECT * FROM sys.querylog_history WHERE pipe NOT IN (SELECT name FROM sys.optimizers); + +SELECT * FROM sys.queue WHERE tag > cast(0 as oid) AND tag NOT IN (SELECT tag FROM sys.queue); +SELECT * FROM sys.queue WHERE tag > cast(0 as oid) AND tag NOT IN (SELECT cast(tag as oid) FROM sys.queue); +SELECT * FROM sys.queue WHERE tag NOT IN (SELECT cast(tag as oid) FROM sys.queue); +SELECT * FROM sys.queue WHERE "username" NOT IN (SELECT name FROM sys.users); + +SELECT * FROM sys.sessions WHERE "username" NOT IN (SELECT name FROM sys.users); + +SELECT * FROM sys.statistics WHERE column_id NOT IN (SELECT id FROM sys._columns UNION ALL SELECT id FROM tmp._columns); +SELECT * FROM sys.statistics WHERE type NOT IN (SELECT sqlname FROM sys.types); + +SELECT * FROM sys.storage() WHERE schema NOT IN (SELECT name FROM sys.schemas); +SELECT * FROM sys.storage() WHERE table NOT IN (SELECT name FROM sys._tables UNION ALL SELECT name FROM tmp._tables); +SELECT * FROM sys.storage() WHERE (schema, table) NOT IN (SELECT sch.name, tbl.name FROM sys.schemas AS sch JOIN sys.tables AS tbl ON sch.id = tbl.schema_id); +SELECT * FROM sys.storage() WHERE column NOT IN (SELECT name FROM sys._columns UNION ALL SELECT name FROM tmp._columns UNION ALL SELECT name FROM sys.keys UNION ALL SELECT name FROM tmp.keys UNION ALL SELECT name FROM sys.idxs UNION ALL SELECT name FROM tmp.idxs); +SELECT * FROM sys.storage() WHERE type NOT IN (SELECT sqlname FROM sys.types); + +SELECT * FROM sys.storage WHERE schema NOT IN (SELECT name FROM sys.schemas); +SELECT * FROM sys.storage WHERE table NOT IN (SELECT name FROM sys._tables UNION ALL SELECT name FROM tmp._tables); +SELECT * FROM sys.storage WHERE (schema, table) NOT IN (SELECT sch.name, tbl.name FROM sys.schemas AS sch JOIN sys.tables AS tbl ON sch.id = tbl.schema_id); +SELECT * FROM sys.storage WHERE column NOT IN (SELECT name FROM sys._columns UNION ALL SELECT name FROM tmp._columns UNION ALL SELECT name FROM sys.keys UNION ALL SELECT name FROM tmp.keys UNION ALL SELECT name FROM sys.idxs UNION ALL SELECT name FROM tmp.idxs); +SELECT * FROM sys.storage WHERE type NOT IN (SELECT sqlname FROM sys.types); + +SELECT * FROM sys.tablestorage WHERE schema NOT IN (SELECT name FROM sys.schemas); +SELECT * FROM sys.tablestorage WHERE table NOT IN (SELECT name FROM sys._tables UNION ALL SELECT name FROM tmp._tables); +SELECT * FROM sys.tablestorage WHERE (schema, table) NOT IN (SELECT sch.name, tbl.name FROM sys.schemas AS sch JOIN sys.tables AS tbl ON sch.id = tbl.schema_id); + +SELECT * FROM sys.schemastorage WHERE schema NOT IN (SELECT name FROM sys.schemas); + +SELECT * FROM sys.storagemodel WHERE schema NOT IN (SELECT name FROM sys.schemas); +SELECT * FROM sys.storagemodel WHERE table NOT IN (SELECT name FROM sys._tables UNION ALL SELECT name FROM tmp._tables); +SELECT * FROM sys.storagemodel WHERE (schema, table) NOT IN (SELECT sch.name, tbl.name FROM sys.schemas AS sch JOIN sys.tables AS tbl ON sch.id = tbl.schema_id); +SELECT * FROM sys.storagemodel WHERE column NOT IN (SELECT name FROM sys._columns UNION ALL SELECT name FROM tmp._columns UNION ALL SELECT name FROM sys.keys UNION ALL SELECT name FROM tmp.keys UNION ALL SELECT name FROM sys.idxs UNION ALL SELECT name FROM tmp.idxs); +SELECT * FROM sys.storagemodel WHERE type NOT IN (SELECT sqlname FROM sys.types); + +SELECT * FROM sys.storagemodelinput WHERE schema NOT IN (SELECT name FROM sys.schemas); +SELECT * FROM sys.storagemodelinput WHERE table NOT IN (SELECT name FROM sys._tables UNION ALL SELECT name FROM tmp._tables); +SELECT * FROM sys.storagemodelinput WHERE (schema, table) NOT IN (SELECT sch.name, tbl.name FROM sys.schemas AS sch JOIN sys.tables AS tbl ON sch.id = tbl.schema_id); +SELECT * FROM sys.storagemodelinput WHERE column NOT IN (SELECT name FROM sys._columns UNION ALL SELECT name FROM tmp._columns UNION ALL SELECT name FROM sys.keys UNION ALL SELECT name FROM tmp.keys UNION ALL SELECT name FROM sys.idxs UNION ALL SELECT name FROM tmp.idxs); +SELECT * FROM sys.storagemodelinput WHERE type NOT IN (SELECT sqlname FROM sys.types); + +SELECT schema, table, rowcount, columnsize, heapsize, hashsize, imprintsize, orderidxsize FROM sys.tablestoragemodel WHERE schema NOT IN (SELECT name FROM sys.schemas); +SELECT schema, table, rowcount, columnsize, heapsize, hashsize, imprintsize, orderidxsize FROM sys.tablestoragemodel WHERE table NOT IN (SELECT name FROM sys._tables UNION ALL SELECT name FROM tmp._tables); +SELECT schema, table, rowcount, columnsize, heapsize, hashsize, imprintsize, orderidxsize FROM sys.tablestoragemodel WHERE (schema, table) NOT IN (SELECT sch.name, tbl.name FROM sys.schemas AS sch JOIN sys.tables AS tbl ON sch.id = tbl.schema_id); + +-- new tables introduced in 2019 +SELECT * FROM sys.table_partitions WHERE "table_id" NOT IN (SELECT id FROM sys._tables); +SELECT * FROM sys.table_partitions WHERE "column_id" IS NOT NULL AND "column_id" NOT IN (SELECT id FROM sys._columns); +SELECT * FROM sys.table_partitions WHERE "type" NOT IN (5,6,9,10); -- 5=By Column Range (1+4), 6=By Expression Range (2+4), 9=By Column Value (1+8), 10=By Expression Value (2+8), see sql_catalog.h #define PARTITION_* + +SELECT * FROM sys.range_partitions WHERE "table_id" NOT IN (SELECT id FROM sys._tables); +SELECT * FROM sys.range_partitions WHERE "partition_id" NOT IN (SELECT id FROM sys.table_partitions); + +SELECT * FROM sys.value_partitions WHERE "table_id" NOT IN (SELECT id FROM sys._tables); +SELECT * FROM sys.value_partitions WHERE "partition_id" NOT IN (SELECT id FROM sys.table_partitions); +*/ + }; + + private static final String[][] tmp_fkeys = { + {"_tables", "schema_id", "id", "sys.schemas", null}, + {"_tables", "type", "table_type_id", "sys.table_types", null}, + {"_columns", "table_id", "id", "_tables", null}, + {"_columns", "type", "sqlname", "sys.types", null}, + {"keys", "table_id", "id", "_tables", null}, + {"keys", "type", "key_type_id", "sys.key_types", null}, + {"keys WHERE rkey <> -1 AND ", "rkey", "id", "keys", null}, +// SELECT * FROM tmp.keys WHERE action <> -1 AND action NOT IN (SELECT id FROM tmp.?); -- TODO: find out which action values are valid and what they mean. + {"idxs", "id", "id", "objects", null}, + {"idxs", "table_id", "id", "_tables", null}, + {"idxs", "type", "index_type_id", "sys.index_types", null}, + {"triggers", "table_id", "id", "_tables", null} + // nog verder afmaken, zie fkey data hierboven + }; + + private static final String[][] netcdf_fkeys = { + {"netcdf_attrs", "file_id", "file_id", "netcdf_files", null}, + {"netcdf_dims", "file_id", "file_id", "netcdf_files", null}, + {"netcdf_vars", "file_id", "file_id", "netcdf_files", null}, + {"netcdf_vardim", "file_id", "file_id", "netcdf_files", null}, + {"netcdf_vardim", "dim_id", "dim_id", "netcdf_dims", null}, + {"netcdf_vardim", "dim_id, file_id", "dim_id, file_id", "netcdf_dims", null}, + {"netcdf_vardim", "var_id", "var_id", "netcdf_vars", null}, + {"netcdf_vardim", "var_id, file_id", "var_id, file_id", "netcdf_vars", null} + }; + + private static final String[][] geom_fkeys = { + {"spatial_ref_sys", "auth_srid", "srid", "spatial_ref_sys", null} + }; + + + // static list of all sys tables with its not null constraint columns + // each entry contains: table_nm, col_nm, from_minor_version + // data pulled from https://dev.monetdb.org/hg/MonetDB/file/Jun2020/sql/test/sys-schema/Tests/check_Not_Nullable_columns.sql + private static final String[][] sys_notnull = { + {"_columns", "id", null}, + {"_columns", "name", null}, + {"_columns", "type", null}, + {"_columns", "type_digits", null}, + {"_columns", "type_scale", null}, + {"_columns", "table_id", null}, + {"_columns", "\"null\"", null}, + {"_columns", "number", null}, + {"_tables", "id", null}, + {"_tables", "name", null}, + {"_tables", "schema_id", null}, + {"_tables", "type", null}, + {"_tables", "system", null}, + {"_tables", "commit_action", null}, + {"_tables", "access", null}, + {"args", "id", null}, + {"args", "func_id", null}, + {"args", "name", null}, + {"args", "type", null}, + {"args", "type_digits", null}, + {"args", "type_scale", null}, + {"args", "inout", null}, + {"args", "number", null}, + {"auths", "id", null}, + {"auths", "name", null}, + {"auths", "grantor", null}, + {"db_user_info", "name", null}, + {"db_user_info", "fullname", null}, + {"db_user_info", "default_schema", null}, + {"dependencies", "id", null}, + {"dependencies", "depend_id", null}, + {"dependencies", "depend_type", null}, + {"function_languages", "language_id", null}, + {"function_languages", "language_name", null}, + {"function_types", "function_type_id", null}, + {"function_types", "function_type_name", null}, + {"function_types", "function_type_keyword", null}, + {"functions", "id", null}, + {"functions", "name", null}, + {"functions", "func", null}, + {"functions", "mod", null}, + {"functions", "language", null}, + {"functions", "type", null}, + {"functions", "side_effect", null}, + {"functions", "varres", null}, + {"functions", "vararg", null}, + {"functions", "schema_id", null}, + {"functions", "system", null}, + {"idxs", "id", null}, + {"idxs", "table_id", null}, + {"idxs", "type", null}, + {"idxs", "name", null}, + {"index_types", "index_type_id", null}, + {"index_types", "index_type_name", null}, + {"key_types", "key_type_id", null}, + {"key_types", "key_type_name", null}, + {"keys", "id", null}, + {"keys", "table_id", null}, + {"keys", "type", null}, + {"keys", "name", null}, + {"keys", "rkey", null}, + {"keys", "action", null}, + {"keywords", "keyword", null}, + {"objects", "id", null}, + {"objects", "name", null}, + {"objects", "nr", null}, + {"optimizers", "name", null}, + {"optimizers", "def", null}, + {"optimizers", "status", null}, + {"privilege_codes", "privilege_code_id", null}, + {"privilege_codes", "privilege_code_name", null}, + {"privileges", "obj_id", null}, + {"privileges", "auth_id", null}, + {"privileges", "privileges", null}, + {"privileges", "grantor", null}, + {"privileges", "grantable", null}, + {"schemas", "id", null}, + {"schemas", "name", null}, + {"schemas", "authorization", null}, + {"schemas", "owner", null}, + {"schemas", "system", null}, + {"sequences", "id", null}, + {"sequences", "schema_id", null}, + {"sequences", "name", null}, + {"sequences", "start", null}, + {"sequences", "minvalue", null}, + {"sequences", "maxvalue", null}, + {"sequences", "increment", null}, + {"sequences", "cacheinc", null}, + {"sequences", "cycle", null}, + {"statistics", "column_id", null}, + {"statistics", "type", null}, + {"statistics", "width", null}, + {"statistics", "stamp", null}, + {"statistics", "\"sample\"", null}, + {"statistics", "count", null}, + {"statistics", "\"unique\"", null}, + {"statistics", "nils", null}, + {"statistics", "sorted", null}, + {"statistics", "revsorted", null}, +/* temporary disabled, TODO re-enable + {"storage", "schema", null}, + {"storage", "table", null}, + {"storage", "column", null}, + {"storage", "type", null}, + {"storage", "mode", null}, + {"storage", "location", null}, + {"storage", "count", null}, + {"storage", "typewidth", null}, + {"storage", "columnsize", null}, + {"storage", "heapsize", null}, + {"storage", "hashes", null}, + {"storage", "phash", null}, + {"storage", "imprints", null}, + {"storage", "orderidx", null}, +*/ {"storagemodelinput", "schema", null}, + {"storagemodelinput", "table", null}, + {"storagemodelinput", "column", null}, + {"storagemodelinput", "type", null}, + {"storagemodelinput", "typewidth", null}, + {"storagemodelinput", "count", null}, + {"storagemodelinput", "\"distinct\"", null}, + {"storagemodelinput", "atomwidth", null}, + {"storagemodelinput", "reference", null}, + {"storagemodelinput", "sorted", null}, + {"storagemodelinput", "\"unique\"", null}, + {"storagemodelinput", "isacolumn", null}, + {"table_types", "table_type_id", null}, + {"table_types", "table_type_name", null}, + {"tables", "id", null}, + {"tables", "name", null}, + {"tables", "schema_id", null}, + {"tables", "type", null}, + {"tables", "system", null}, + {"tables", "commit_action", null}, + {"tables", "access", null}, + {"tables", "temporary", null}, + {"tracelog", "ticks", null}, + {"tracelog", "stmt", null}, + {"triggers", "id", null}, + {"triggers", "name", null}, + {"triggers", "table_id", null}, + {"triggers", "time", null}, + {"triggers", "orientation", null}, + {"triggers", "event", null}, + {"triggers", "statement", null}, + {"types", "id", null}, + {"types", "systemname", null}, + {"types", "sqlname", null}, + {"types", "digits", null}, + {"types", "scale", null}, + {"types", "radix", null}, + {"types", "eclass", null}, + {"types", "schema_id", null}, + {"user_role", "login_id", null}, + {"user_role", "role_id", null}, + {"users", "name", null}, + {"users", "fullname", null}, + {"users", "default_schema", null}, + {"var_values", "var_name", null}, + {"var_values", "value", null}, + // new tables introduced in Apr 2019 feature release (11.33.3) + {"range_partitions", "table_id", "33"}, + {"range_partitions", "partition_id", "33"}, + {"range_partitions", "with_nulls", "33"}, + {"table_partitions", "id", "33"}, + {"table_partitions", "table_id", "33"}, + {"table_partitions", "type", "33"}, + {"value_partitions", "table_id", "33"}, + {"value_partitions", "partition_id", "33"}, + {"value_partitions", "value", "33"} // ?? Can this be null when WITH NULL VALUES is specified. + }; + + private static final String[][] tmp_notnull = { + {"_columns", "id", null}, + {"_columns", "name", null}, + {"_columns", "type", null}, + {"_columns", "type_digits", null}, + {"_columns", "type_scale", null}, + {"_columns", "table_id", null}, + {"_columns", "\"null\"", null}, + {"_columns", "number", null}, + {"_tables", "id", null}, + {"_tables", "name", null}, + {"_tables", "schema_id", null}, + {"_tables", "type", null}, + {"_tables", "system", null}, + {"_tables", "commit_action", null}, + {"_tables", "access", null}, + {"idxs", "id", null}, + {"idxs", "table_id", null}, + {"idxs", "type", null}, + {"idxs", "name", null}, + {"keys", "id", null}, + {"keys", "table_id", null}, + {"keys", "type", null}, + {"keys", "name", null}, + {"keys", "rkey", null}, + {"keys", "action", null}, + {"objects", "id", null}, + {"objects", "name", null}, + {"objects", "nr", null}, + {"triggers", "id", null}, + {"triggers", "name", null}, + {"triggers", "table_id", null}, + {"triggers", "time", null}, + {"triggers", "orientation", null}, + {"triggers", "event", null}, + {"triggers", "statement", null} + }; + + private static final String[][] netcdf_notnull = { + {"netcdf_files", "file_id", null}, + {"netcdf_files", "location", null}, + {"netcdf_dims", "dim_id", null}, + {"netcdf_dims", "file_id", null}, + {"netcdf_dims", "name", null}, + {"netcdf_dims", "length", null}, + {"netcdf_vars", "var_id", null}, + {"netcdf_vars", "file_id", null}, + {"netcdf_vars", "name", null}, + {"netcdf_vars", "vartype", null}, + {"netcdf_vardim", "var_id", null}, + {"netcdf_vardim", "dim_id", null}, + {"netcdf_vardim", "file_id", null}, + {"netcdf_vardim", "dimpos", null}, + {"netcdf_attrs", "obj_name", null}, + {"netcdf_attrs", "att_name", null}, + {"netcdf_attrs", "att_type", null}, + {"netcdf_attrs", "value", null}, + {"netcdf_attrs", "file_id", null}, + {"netcdf_attrs", "gr_name", null} + }; + + private static final String[][] geom_notnull = { + {"spatial_ref_sys", "srid", null}, + {"spatial_ref_sys", "auth_name", null}, + {"spatial_ref_sys", "auth_srid", null}, + {"spatial_ref_sys", "srtext", null}, + {"spatial_ref_sys", "proj4text", null} + }; +}
--- a/src/main/java/nl/cwi/monetdb/jdbc/MonetDatabaseMetaData.java +++ b/src/main/java/nl/cwi/monetdb/jdbc/MonetDatabaseMetaData.java @@ -408,7 +408,7 @@ public class MonetDatabaseMetaData " AND \"type\" = 1" + // only scalar functions // exclude functions which do not work or belong to string functions (code(int) and space(int)) " AND f.\"name\" NOT IN ('code','not_uniques','rotate_xor_hash','space'))" + - // include specific functions which have no 1st arg (pi()) or it is not numeric + // include specific functions which have no arguments (pi()) or are numeric. Actually ms_str is a conversion function from float to str. " OR f.\"name\" IN ('alpha','degrees','fuse','ms_round','ms_str','ms_trunc','pi','radians')"; return getConcatenatedStringFromQuery(FunctionsSelect + FunctionsWhere + match + OrFunctionsMaxMin + FunctionsOrderBy1); } @@ -422,8 +422,8 @@ public class MonetDatabaseMetaData " AND \"mod\" <> 'sql')" + // include specific functions code(int) and space(int) which belong to the 'str' module " OR \"mod\" = 'str'" + - // include 3 specific json functions, md5() and udf reverse() which all accept a string arg - " OR f.\"name\" IN ('isarray','isobject','isvalid','md5','reverse')"; + // include 3 specific json functions, md5(), ms_stuff() and udf reverse() which all accept a string arg + " OR f.\"name\" IN ('isarray','isobject','isvalid','md5','ms_stuff','reverse')"; final String unionPart = // add functions which are not listed in sys.functions but implemented in the SQL parser (see sql/server/sql_parser.y) " UNION SELECT 'position'";