Mercurial > hg > monetdb-java

comparison src/main/java/org/monetdb/util/MDBvalidator.java @ 391:f523727db392

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Moved Java classes from packages starting with nl.cwi.monetdb.* to package org.monetdb.* This naming complies to the Java Package Naming convention as MonetDB's main website is www.monetdb.org.
author Martin van Dinther <martin.van.dinther@monetdbsolutions.com>
date Thu, 12 Nov 2020 22:02:01 +0100 (2020-11-12)
parents src/main/java/nl/cwi/monetdb/util/MDBvalidator.java@6199e0be3c6e
children 2397e0f58949
comparison
equal deleted inserted replaced
390:6199e0be3c6e 391:f523727db392
1 /*
2 * This Source Code Form is subject to the terms of the Mozilla Public
3 * License, v. 2.0. If a copy of the MPL was not distributed with this
4 * file, You can obtain one at http://mozilla.org/MPL/2.0/.
5 *
6 * Copyright 1997 - July 2008 CWI, August 2008 - 2020 MonetDB B.V.
7 */
8
9 package org.monetdb.util;
10
11 import java.sql.Connection;
12 import java.sql.DatabaseMetaData;
13 import java.sql.ResultSet;
14 import java.sql.ResultSetMetaData;
15 import java.sql.Statement;
16 import java.sql.SQLException;
17 import java.sql.Types;
18
19 /**
20 * MonetDB Data Integrity Validator class (MDBvalidator) can
21 * a) validate system tables data integrity in system schemas: sys and tmp
22 * this includes violations of:
23 * primary key uniqueness
24 * primary key column(s) not null
25 * unique constraint uniqueness
26 * foreign key referential integrity
27 * column not null
28 * column maximum length for char/varchar/clob/blob/json/url columns which have max length > 0
29 * b) validate user schema tables & columns data integrity based on available meta data from system tables & system views
30 * primary key uniqueness
31 * TODO primary key column(s) not null
32 * unique constraint uniqueness
33 * TODO foreign key referential integrity
34 * column not null
35 * column maximum length for char/varchar/clob/blob/json/url columns which have max length > 0
36 *
37 * More possible validations for future
38 * col char/varchar/clob/blob/json/url minimum length (some columns may not be empty, so length >= 1)
39 * col with sequence (serial/bigserial/autoincrement) in range (0/1/min_value .. max_value)
40 * col value is valid in domain (date/time/timestamp/json/inet/url/uuid/...)
41 * col in list checks (some columns may have only certain values which are not stored in a table or view (eg as fk))
42 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_*. Note table_partitions is introduced in Apr2019 "33"
43 * col conditional checks (column is not null when other column is (not) null)
44 -- i.e.: either column_id or expression in sys.table_partitions must be populated
45 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;
46 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;
47 *
48 * @author Martin van Dinther
49 * @version 0.1
50 */
51
52 public final class MDBvalidator {
53 private static final String prg = "MDBvalidator";
54 private Connection con;
55 private int majorversion;
56 private int minorversion;
57
58 private boolean verbose = false; // set it to true for tracing all generated SQL queries, see validateQuery(qry, ...)
59
60 MDBvalidator(Connection conn) {
61 con = conn;
62 }
63
64 /* disabled as it should be called from JdbcClient program
65 public static void main(String[] args) throws Exception {
66 System.out.println(prg + " started with " + args.length + " arguments." + (args.length == 0 ? " Using default JDBC URL !" : ""));
67 // parse input args: connection (JDBC_URL), check systbls (default) or user schema or user db
68
69 String JDBC_URL = (args.length > 0) ? args[0]
70 : "jdbc:monetdb://localhost:50000/demo?user=monetdb&password=monetdb&so_timeout=14000";
71 if (!JDBC_URL.startsWith("jdbc:monetdb://")) {
72 System.out.println("ERROR: Invalid JDBC URL. It does not start with jdbc:monetdb:");
73 return;
74 }
75
76 Connection con = null;
77 try {
78 // make connection to target server
79 con = java.sql.DriverManager.getConnection(JDBC_URL);
80 System.out.println(prg + " connected to MonetDB server");
81 printExceptions(con.getWarnings());
82
83 long start_time = System.currentTimeMillis();
84
85 validateSqlCatalogIntegrity(con);
86 validateSqlNetcdfTablesIntegrity(con);
87 validateSqlGeomTablesIntegrity(con);
88
89 validateSchemaIntegrity(con, "sys");
90 validateDBIntegrity(con);
91
92 long elapsed = System.currentTimeMillis() - start_time;
93 long secs = elapsed /1000;
94 System.out.println("Validation completed in " + secs + "s and " + (elapsed - (secs *1000)) + "ms");
95 } catch (SQLException e) {
96 printExceptions(e);
97 }
98
99 // free resources
100 if (con != null) {
101 try { con.close(); } catch (SQLException e) { /* ignore * / }
102 }
103 }
104 */
105
106 public static void validateSqlCatalogIntegrity(final Connection conn) {
107 MDBvalidator mdbv = new MDBvalidator(conn);
108 if (mdbv.checkMonetDBVersion()) {
109 mdbv.validateSchema("sys", null, sys_pkeys, sys_akeys, sys_fkeys, sys_notnull, true);
110 mdbv.validateSchema("tmp", null, tmp_pkeys, tmp_akeys, tmp_fkeys, tmp_notnull, true);
111 }
112 }
113
114 public static void validateSqlNetcdfTablesIntegrity(final Connection conn) {
115 MDBvalidator mdbv = new MDBvalidator(conn);
116 if (mdbv.checkMonetDBVersion()) {
117 // determine if the 5 netcdf tables exist in the sys schema
118 if (mdbv.checkTableExists("sys", "netcdf_files")
119 && mdbv.checkTableExists("sys", "netcdf_dims")
120 && mdbv.checkTableExists("sys", "netcdf_vars")
121 && mdbv.checkTableExists("sys", "netcdf_vardim")
122 && mdbv.checkTableExists("sys", "netcdf_attrs"))
123 mdbv.validateSchema("sys", "netcdf", netcdf_pkeys, netcdf_akeys, netcdf_fkeys, netcdf_notnull, false);
124 }
125 }
126
127 public static void validateSqlGeomTablesIntegrity(final Connection conn) {
128 MDBvalidator mdbv = new MDBvalidator(conn);
129 if (mdbv.checkMonetDBVersion()) {
130 if (mdbv.checkTableExists("sys", "spatial_ref_sys")) // No need to also test if view sys.geometry_columns exists
131 mdbv.validateSchema("sys", "geom", geom_pkeys, geom_akeys, geom_fkeys, geom_notnull, false);
132 }
133 }
134
135 public static void validateSchemaIntegrity(final Connection conn, final String schema) {
136 MDBvalidator mdbv = new MDBvalidator(conn);
137 if (mdbv.checkSchemaExists(schema))
138 mdbv.validateSchema(schema, null, null, null, null, null, true);
139 else
140 System.out.println("Schema: " + schema + " does not exist in this database.");
141 }
142
143 public static void validateDBIntegrity(final Connection conn) {
144 MDBvalidator mdbv = new MDBvalidator(conn);
145 Statement stmt = mdbv.createStatement("validateDBIntegrity()");
146 if (stmt == null)
147 return;
148
149 boolean hasUserSchemas = false;
150 ResultSet rs = null;
151 try {
152 // retrieve all non-system schemas
153 rs = stmt.executeQuery("SELECT name FROM sys.schemas WHERE NOT system ORDER BY name;");
154 if (rs != null) {
155 // for each user schema do:
156 while (rs.next()) {
157 String schema = rs.getString(1);
158 if (schema != null && !schema.isEmpty()) {
159 hasUserSchemas = true;
160 mdbv.validateSchema(schema, null, null, null, null, null, true);
161 }
162 }
163 }
164 } catch (SQLException e) {
165 printExceptions(e);
166 }
167 freeStmtRs(stmt, rs);
168 if (!hasUserSchemas)
169 System.out.println("No user schemas found in this database.");
170 }
171
172
173 private void validateSchema(
174 final String schema,
175 final String group,
176 final String[][] pkeys,
177 final String[][] ukeys,
178 final String[][] fkeys,
179 final String[][] colnotnull,
180 final boolean checkMaxStr)
181 {
182 boolean is_system_schema = ("sys".equals(schema) || "tmp".equals(schema));
183
184 if (pkeys != null) {
185 validateUniqueness(schema, group, pkeys, "Primary Key uniqueness");
186 validateNotNull(schema, group, pkeys, "Primary Key Not Null");
187 } else {
188 validateUniqueness(schema, true, "Primary Key uniqueness");
189 }
190
191 if (ukeys != null) {
192 validateUniqueness(schema, group, ukeys, "Unique Constraint");
193 } else {
194 validateUniqueness(schema, false, "Unique Constraint");
195 }
196
197 if (fkeys != null) {
198 validateFKs(schema, group, fkeys, "Foreign Key referential integrity");
199 } else {
200 validateFKs(schema, "Foreign Key referential integrity");
201 }
202
203 if (colnotnull != null) {
204 validateNotNull(schema, group, colnotnull, "Not Null");
205 } else {
206 validateNotNull(schema, is_system_schema, "Not Null");
207 }
208
209 if (checkMaxStr)
210 validateMaxCharStrLength(schema, is_system_schema, "Max Character Length");
211 }
212
213 /* validate uniqueness of primary key or uniqueness constraints based on static data array */
214 private void validateUniqueness(
215 final String schema,
216 final String group,
217 final String[][] data,
218 final String checkType)
219 {
220 final int len = data.length;
221 System.out.println("Checking " + minimumWidth(len,6) + (group != null ? " " + group : "") + " tables/keys in schema " + schema + " for " + checkType + " violations.");
222
223 StringBuilder sb = new StringBuilder(256); // reusable buffer to compose SQL validation queries
224 sb.append("SELECT COUNT(*) AS duplicates, ");
225 final int qry_len = sb.length();
226 String tbl;
227 String keycols;
228 for (int i = 0; i < len; i++) {
229 if (isValidVersion(data[i][2])) {
230 tbl = data[i][0];
231 keycols = data[i][1];
232 // reuse the StringBuilder by cleaning it partial
233 sb.setLength(qry_len);
234 sb.append(keycols).append(" FROM ");
235 if (!tbl.startsWith("(")) { // when tbl starts with a ( it is a unioned table set which we cannot prefix with a schema name qualifier
236 sb.append(schema).append('.');
237 }
238 sb.append(tbl)
239 .append(" GROUP BY ").append(keycols)
240 .append(" HAVING COUNT(*) > 1;");
241 validateQuery(sb.toString(), schema, tbl, keycols, checkType);
242 }
243 }
244 }
245
246 /* validate uniqueness of primary key or uniqueness constraints based on dynamic retrieved system data from sys.keys */
247 private void validateUniqueness(
248 final String schema,
249 final boolean pkey,
250 final String checkType)
251 {
252 Statement stmt = createStatement("validateUniqueness()");
253 if (stmt == null)
254 return;
255
256 // fetch the primary or unique key info from the MonetDB system tables
257 StringBuilder sb = new StringBuilder(400);
258 sb.append(" FROM sys.keys k JOIN sys.tables t ON k.table_id = t.id JOIN sys.schemas s ON t.schema_id = s.id"
259 + " WHERE k.type = ").append(pkey ? 0 : 1) // 0 = primary keys, 1 = unique keys
260 .append(" and s.name = '").append(schema).append("'");
261 String qry = sb.toString();
262 final int count = runCountQuery(qry);
263 System.out.println("Checking " + minimumWidth(count,6) + " keys in schema " + schema + " for " + checkType + " violations.");
264
265 ResultSet rs = null;
266 try {
267 sb.setLength(0); // empty previous usage of sb
268 // fetch the primary or unique key info including columns from the MonetDB system tables
269 sb.append("SELECT s.name as sch_nm, t.name as tbl_nm, k.name as key_nm, o.name as col_nm, o.nr")
270 .append(" FROM sys.keys k JOIN sys.objects o ON k.id = o.id JOIN sys.tables t ON k.table_id = t.id JOIN sys.schemas s ON t.schema_id = s.id"
271 + " WHERE k.type = ").append(pkey ? 0 : 1) // 0 = primary keys, 1 = unique keys
272 .append(" and s.name = '").append(schema).append("'")
273 .append(" ORDER BY t.name, k.name, o.nr;");
274 qry = sb.toString();
275 rs = stmt.executeQuery(qry);
276 if (rs != null) {
277 String sch = null, tbl, key, col;
278 String prv_tbl = null, prv_key = null, keycols = null;
279 sb.setLength(0); // empty previous usage of sb
280 sb.append("SELECT COUNT(*) AS duplicates, ");
281 final int qry_len = sb.length();
282 while (rs.next()) {
283 // retrieve meta data
284 sch = rs.getString(1);
285 tbl = rs.getString(2);
286 key = rs.getString(3);
287 col = rs.getString(4);
288 if (prv_tbl == null)
289 prv_tbl = tbl;
290 if (prv_key == null)
291 prv_key = key;
292 if (tbl.equals(prv_tbl) && key.equals(prv_key)) {
293 if (keycols == null)
294 keycols = "\"" + col + "\"";
295 else
296 keycols = keycols + ", \"" + col + "\"";
297 } else {
298 // compose validation query for the previous retrieved key columns
299 // reuse the StringBuilder by cleaning it partial
300 sb.setLength(qry_len);
301 sb.append(keycols)
302 .append(" FROM \"").append(sch).append("\".\"").append(prv_tbl).append("\"")
303 .append(" GROUP BY ").append(keycols)
304 .append(" HAVING COUNT(*) > 1;");
305 validateQuery(sb.toString(), sch, prv_tbl, keycols, checkType);
306 prv_tbl = tbl;
307 prv_key = key;
308 keycols = "\"" + col + "\"";
309 }
310 }
311 if (sch != null && prv_tbl != null && keycols != null) {
312 // compose validation query for the last retrieved key
313 // reuse the StringBuilder by cleaning it partial
314 sb.setLength(qry_len);
315 sb.append(keycols)
316 .append(" FROM \"").append(sch).append("\".\"").append(prv_tbl).append("\"")
317 .append(" GROUP BY ").append(keycols)
318 .append(" HAVING COUNT(*) > 1;");
319 validateQuery(sb.toString(), sch, prv_tbl, keycols, checkType);
320 }
321 }
322 } catch (SQLException e) {
323 System.err.println("Failed to execute query: " + qry);
324 printExceptions(e);
325 }
326 freeStmtRs(stmt, rs);
327 }
328
329 /* validate foreign key constraints based on static data array */
330 private void validateFKs(
331 final String schema,
332 final String group,
333 final String[][] data,
334 final String checkType)
335 {
336 final int len = data.length;
337 System.out.println("Checking " + minimumWidth(len,6) + (group != null ? " " + group : "") + " foreign keys in schema " + schema + " for " + checkType + " violations.");
338
339 StringBuilder sb = new StringBuilder(400); // reusable buffer to compose SQL validation queries
340 sb.append("SELECT ");
341 final int qry_len = sb.length();
342 String tbl;
343 String cols;
344 String ref_tbl;
345 String ref_cols;
346 for (int i = 0; i < len; i++) {
347 if (isValidVersion(data[i][4])) {
348 tbl = data[i][0];
349 cols = data[i][1];
350 ref_cols = data[i][2];
351 ref_tbl = data[i][3];
352 // reuse the StringBuilder by cleaning it partial
353 sb.setLength(qry_len);
354 sb.append(cols).append(", * FROM ").append(schema).append('.').append(tbl);
355 if (!tbl.contains(" WHERE "))
356 sb.append(" WHERE ");
357 sb.append('(').append(cols).append(") NOT IN (SELECT ").append(ref_cols).append(" FROM ");
358 if (!ref_tbl.contains("."))
359 sb.append(schema).append('.');
360 sb.append(ref_tbl).append(");");
361 validateQuery(sb.toString(), schema, tbl, cols, checkType);
362 }
363 }
364 }
365
366 /* validate foreign key constraints based on dynamic retrieved system data from sys.keys */
367 private void validateFKs(
368 final String schema,
369 final String checkType)
370 {
371 Statement stmt = createStatement("validateFKs()");
372 if (stmt == null)
373 return;
374
375 // fetch the foreign key info from the MonetDB system tables
376 StringBuilder sb = new StringBuilder(400);
377 sb.append(" FROM sys.keys k JOIN sys.tables t ON k.table_id = t.id JOIN sys.schemas s ON t.schema_id = s.id"
378 + " WHERE k.type = 2") // 2 = foreign keys
379 .append(" and s.name = '").append(schema).append("'");
380 String qry = sb.toString();
381 final int count = runCountQuery(qry);
382 System.out.println("Checking " + minimumWidth(count,6) + " foreign keys in schema " + schema + " for " + checkType + " violations.");
383
384 ResultSet rs = null;
385 // TODO: finish FK validation implementation
386
387 freeStmtRs(stmt, rs);
388 }
389
390 /* validate NOT NULL constraints based on static data array */
391 private void validateNotNull(
392 final String schema,
393 final String group,
394 final String[][] data,
395 final String checkType)
396 {
397 final int len = data.length;
398 System.out.println("Checking " + minimumWidth(len,6) + (group != null ? " " + group : "") + " columns in schema " + schema + " for " + checkType + " violations.");
399
400 StringBuilder sb = new StringBuilder(256); // reusable buffer to compose SQL validation queries
401 sb.append("SELECT ");
402 final int qry_len = sb.length();
403 String tbl;
404 String col;
405 boolean multicolumn = false;
406 StringBuilder isNullCond = new StringBuilder(80);
407 for (int i = 0; i < len; i++) {
408 if (isValidVersion(data[i][2])) {
409 tbl = data[i][0];
410 col = data[i][1];
411 multicolumn = col.contains(", "); // some pkeys consist of multiple columns
412 isNullCond.setLength(0); // empty previous content
413 if (multicolumn) {
414 String[] cols = col.split(", ");
415 for (int c = 0; c < cols.length; c++) {
416 if (c > 0) {
417 isNullCond.append(" OR ");
418 }
419 isNullCond.append(cols[c]).append(" IS NULL");
420 }
421 } else {
422 isNullCond.append(col).append(" IS NULL");
423 }
424 // reuse the StringBuilder by cleaning it partial
425 sb.setLength(qry_len);
426 sb.append(col)
427 .append(", * FROM ").append(schema).append('.').append(tbl)
428 .append(" WHERE ").append(isNullCond).append(';');
429 validateQuery(sb.toString(), schema, tbl, col, checkType);
430 }
431 }
432 }
433
434 /* validate NOT NULL constraints based on dynamic retrieved system data from sys.columns */
435 private void validateNotNull(
436 final String schema,
437 final boolean system,
438 final String checkType)
439 {
440 Statement stmt = createStatement("validateNotNull()");
441 if (stmt == null)
442 return;
443
444 // fetch the NOT NULL info from the MonetDB system tables as those are leading for user tables (but not system tables)
445 StringBuilder sb = new StringBuilder(400);
446 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"
447 + " where t.type in (0, 10, 1, 11) and c.\"null\" = false" // t.type 0 = TABLE, 10 = SYSTEM TABLE, 1 = VIEW, 11 = SYSTEM VIEW
448 + " and t.system = ").append(system)
449 .append(" and s.name = '").append(schema).append("'");
450 String qry = sb.toString();
451 final int count = runCountQuery(qry);
452 System.out.println("Checking " + minimumWidth(count,6) + " columns in schema " + schema + " for " + checkType + " violations.");
453
454 ResultSet rs = null;
455 try {
456 sb.setLength(0); // empty previous usage of sb
457 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
458 .append(qry).append(" ORDER BY s.name, t.name, c.name;");
459 qry = sb.toString();
460 rs = stmt.executeQuery(qry);
461 if (rs != null) {
462 String sch, tbl, col;
463 while (rs.next()) {
464 // retrieve meta data
465 sch = rs.getString(1);
466 tbl = rs.getString(2);
467 col = rs.getString(3);
468 // compose validation query for this specific column
469 sb.setLength(0); // empty previous usage of sb
470 sb.append("SELECT '").append(sch).append('.').append(tbl).append('.').append(col).append("' as full_col_nm, *")
471 .append(" FROM \"").append(sch).append("\".\"").append(tbl).append("\"")
472 .append(" WHERE \"").append(col).append("\" IS NULL;");
473 validateQuery(sb.toString(), sch, tbl, col, checkType);
474 }
475 }
476 } catch (SQLException e) {
477 System.err.println("Failed to execute query: " + qry);
478 printExceptions(e);
479 }
480 freeStmtRs(stmt, rs);
481 }
482
483 /* validate Maximum (Var)Char(LOB) Length constraints based on dynamic retrieved system data from sys.columns */
484 private void validateMaxCharStrLength(
485 final String schema,
486 final boolean system,
487 final String checkType)
488 {
489 Statement stmt = createStatement("validateMaxCharStrLength()");
490 if (stmt == null)
491 return;
492
493 // fetch the max char str len info from the MonetDB system tables as those are leading
494 StringBuilder sb = new StringBuilder(400);
495 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"
496 + " where t.type in (0, 10, 1, 11)" // t.type 0 = TABLE, 10 = SYSTEM TABLE, 1 = VIEW, 11 = SYSTEM VIEW
497 + " and c.type_digits >= 1" // only when a positive max length is specified
498 + " and t.system = ").append(system)
499 .append(" and c.type in ('varchar','char','clob','json','url','blob')") // only for variable character/bytes data type columns
500 .append(" and s.name = '").append(schema).append("'");
501 String qry = sb.toString();
502 final int count = runCountQuery(qry);
503 System.out.println("Checking " + minimumWidth(count,6) + " columns in schema " + schema + " for " + checkType + " violations.");
504
505 ResultSet rs = null;
506 try {
507 sb.setLength(0); // empty previous usage of sb
508 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
509 .append(qry).append(" ORDER BY s.name, t.name, c.name, c.type_digits;");
510 qry = sb.toString();
511 rs = stmt.executeQuery(qry);
512 if (rs != null) {
513 long max_len = 0;
514 String sch, tbl, col;
515 while (rs.next()) {
516 // retrieve meta data
517 sch = rs.getString(1);
518 tbl = rs.getString(2);
519 col = rs.getString(3);
520 max_len = rs.getLong(4);
521 // compose validation query for this specific column
522 sb.setLength(0); // empty previous usage of sb
523 sb.append("SELECT '").append(sch).append('.').append(tbl).append('.').append(col).append("' as full_col_nm, ")
524 .append(max_len).append(" as max_allowed_length, ")
525 .append("length(\"").append(col).append("\") as data_length, ")
526 .append("\"").append(col).append("\" as data_value")
527 .append(" FROM \"").append(sch).append("\".\"").append(tbl).append("\"")
528 .append(" WHERE \"").append(col).append("\" IS NOT NULL AND length(\"").append(col).append("\") > ").append(max_len);
529 validateQuery(sb.toString(), sch, tbl, col, checkType);
530 }
531 }
532 } catch (SQLException e) {
533 System.err.println("Failed to execute query: " + qry);
534 printExceptions(e);
535 }
536 freeStmtRs(stmt, rs);
537 }
538
539
540 /* Run a validation query.
541 * It should result in no rows returned.
542 * When rows are returned those are the ones that contain violations.
543 * Retrieve them and convert the results (currently first 16 only) into a (large) violation string
544 * Log/Print the violation.
545 */
546 private void validateQuery(
547 final String qry,
548 final String sch,
549 final String tbl,
550 final String cols,
551 final String checkType)
552 {
553 Statement stmt = createStatement("validateQuery()");
554 if (stmt == null)
555 return;
556
557 ResultSet rs = null;
558 try {
559 if (verbose) {
560 System.out.println(qry);
561 }
562 rs = stmt.executeQuery(qry);
563 if (rs != null) {
564 final ResultSetMetaData rsmd = rs.getMetaData();
565 final int nr_cols = rsmd.getColumnCount();
566 final StringBuilder sb = new StringBuilder(1024);
567 final int maxprintrows = 16;
568 int row = 0;
569 String val;
570 int tp;
571 while (rs.next()) {
572 // query returns found violations
573 row++;
574 if (row == 1) {
575 // print result header once
576 for (int i = 1; i <= nr_cols; i++) {
577 sb.append((i > 1) ? ", " : "\t");
578 sb.append(rsmd.getColumnLabel(i));
579 }
580 sb.append('\n');
581 }
582 if (row <= maxprintrows) { // print only the first n rows
583 // retrieve row data
584 for (int i = 1; i <= nr_cols; i++) {
585 sb.append((i > 1) ? ", " : "\t");
586 val = rs.getString(i);
587 if (val == null || rs.wasNull()) {
588 sb.append("null");
589 } else {
590 tp = rsmd.getColumnType(i); // this method is very fast, so no need to cache it outside the loop
591 if (tp == Types.VARCHAR || tp == Types.CHAR || tp == Types.CLOB || tp == Types.BLOB) {
592 sb.append('"').append(val).append('"');
593 } else {
594 sb.append(val);
595 }
596 }
597 }
598 sb.append('\n');
599 }
600 }
601 if (row > 0) {
602 if (row > maxprintrows) {
603 sb.append("...\n");
604 sb.append("Listed only first ").append(maxprintrows).append(" violations of ").append(row).append(" found!\n");
605 }
606 logViolations(checkType, sch, tbl, cols, qry, sb.toString());
607 }
608 }
609 } catch (SQLException e) {
610 System.err.println("Failed to execute query: " + qry);
611 printExceptions(e);
612 }
613 freeStmtRs(stmt, rs);
614 }
615
616 private int runCountQuery(final String from_qry) {
617 Statement stmt = createStatement("runCountQuery()");
618 if (stmt == null)
619 return 0;
620
621 ResultSet rs = null;
622 int count = 0;
623 try {
624 rs = stmt.executeQuery("SELECT COUNT(*) " + from_qry);
625 if (rs != null) {
626 if (rs.next()) {
627 // retrieve count data
628 count = rs.getInt(1);
629 }
630 }
631 } catch (SQLException e) {
632 System.err.println("Failed to execute SELECT COUNT(*) " + from_qry);
633 printExceptions(e);
634 }
635 freeStmtRs(stmt, rs);
636 return count;
637 }
638
639 private Statement createStatement(final String method) {
640 try {
641 return con.createStatement();
642 } catch (SQLException e) {
643 System.err.print("Failed to create Statement in " + method);
644 printExceptions(e);
645 }
646 return null;
647 }
648
649 private boolean checkMonetDBVersion() {
650 if (majorversion == 0 && minorversion == 0) {
651 // we haven't fetched them before.
652 try {
653 // retrieve server version numbers (major and minor). These are needed to filter out version specific validations
654 DatabaseMetaData dbmd = con.getMetaData();
655 if (dbmd != null) {
656 // System.out.println("MonetDB server version " + dbmd.getDatabaseProductVersion());
657 majorversion = dbmd.getDatabaseMajorVersion();
658 minorversion = dbmd.getDatabaseMinorVersion();
659 }
660 } catch (SQLException e) {
661 printExceptions(e);
662 }
663 }
664 // validate majorversion (should be 11) and minorversion (should be >= 19) (from Jul2015 (11.19.15))
665 if (majorversion < 11 || (majorversion == 11 && minorversion < 19)) {
666 System.out.println("Warning: this MonetDB server is too old for " + prg + ". Please upgrade MonetDB server.");
667 return false;
668 }
669 return true;
670 }
671
672 private boolean isValidVersion(final String version) {
673 if (version == null)
674 return true; // when no version string is supplied it is valid by default
675
676 try {
677 int v = Integer.parseInt(version);
678 return minorversion >= v;
679 } catch (NumberFormatException e) {
680 System.out.println("Failed to parse version string '" + version + "' as an integer number.");
681 }
682 return false;
683 }
684
685 private boolean checkSchemaExists(final String schema) {
686 Statement stmt = createStatement("checkSchemaExists()");
687 if (stmt == null)
688 return false;
689
690 String sql = "SELECT name FROM sys.schemas WHERE name = '" + schema + "';";
691 ResultSet rs = null;
692 boolean ret = false;
693 try {
694 rs = stmt.executeQuery(sql);
695 if (rs != null) {
696 if (rs.next()) {
697 if (schema != null && schema.equals(rs.getString(1)))
698 ret = true;
699 }
700 }
701 } catch (SQLException e) {
702 System.err.println("Failed to execute " + sql);
703 printExceptions(e);
704 }
705 freeStmtRs(stmt, rs);
706 return ret;
707 }
708
709 private boolean checkTableExists(final String schema, final String table) {
710 Statement stmt = createStatement("checkTableExists()");
711 if (stmt == null)
712 return false;
713
714 String sql = "SELECT s.name, t.name FROM sys.tables t JOIN sys.schemas s ON t.schema_id = s.id WHERE t.name = '" + table + "' AND s.name = '" + schema + "';";
715 ResultSet rs = null;
716 boolean ret = false;
717 try {
718 rs = stmt.executeQuery(sql);
719 if (rs != null) {
720 if (rs.next()) {
721 if (schema != null && schema.equals(rs.getString(1))
722 && table != null && table.equals(rs.getString(2)) )
723 ret = true;
724 }
725 }
726 } catch (SQLException e) {
727 System.err.println("Failed to execute " + sql);
728 printExceptions(e);
729 }
730 freeStmtRs(stmt, rs);
731 return ret;
732 }
733
734 private void logViolations(
735 final String checkType,
736 final String schema,
737 final String table,
738 final String columns,
739 final String query,
740 final String violations)
741 {
742 StringBuilder sb = new StringBuilder(2048);
743 sb.append(checkType).append(" violation(s) found in \"")
744 .append(schema).append("\".\"").append(table).append("\".\"").append(columns).append("\":\n")
745 .append(violations)
746 .append("Found using query: ").append(query).append("\n");
747 System.out.println(sb.toString());
748 }
749
750 private static void printExceptions(SQLException se) {
751 while (se != null) {
752 System.err.println(se.getSQLState() + " " + se.getMessage());
753 se = se.getNextException();
754 }
755 }
756
757 private static void freeStmtRs(final Statement stmt, final ResultSet rs) {
758 // free resources
759 if (rs != null) {
760 try { rs.close(); } catch (SQLException e) { /* ignore */ }
761 }
762 if (stmt != null) {
763 try { stmt.close(); } catch (SQLException e) { /* ignore */ }
764 }
765 }
766
767 private static String minimumWidth(int val, int minWidth) {
768 String valstr = Integer.toString(val);
769 int spacesneeded = minWidth - valstr.length();
770 switch (spacesneeded) {
771 case 1: return " " + valstr;
772 case 2: return " " + valstr;
773 case 3: return " " + valstr;
774 case 4: return " " + valstr;
775 case 5: return " " + valstr;
776 case 6: return " " + valstr;
777 default: return valstr;
778 }
779 }
780
781
782 // ********* below are many 2-dimensional String arrays (all private) containing the data for constructing the validation queries *********
783 // based on data from: https://dev.monetdb.org/hg/MonetDB/file/Jun2020/sql/test/sys-schema/Tests
784
785 // static list of all sys tables with its pkey columns
786 // each entry contains: table_nm, pk_col_nms, from_minor_version
787 // data originally pulled from https://dev.monetdb.org/hg/MonetDB/file/Jun2020/sql/test/sys-schema/Tests/check_PrimaryKey_uniqueness.sql
788 private static final String[][] sys_pkeys = {
789 {"schemas", "id", null},
790 {"_tables", "id", null},
791 {"tables", "id", null}, // is a view
792 {"_columns", "id", null},
793 {"columns", "id", null}, // is a view
794 {"functions", "id", null},
795 {"systemfunctions", "function_id", null}, // has become a view in Apr2019 (11.33.3) and maybe removed in the future as is deprecated
796 {"args", "id", null},
797 {"types", "id", null},
798 {"objects", "id, nr", null},
799 {"keys", "id", null},
800 {"idxs", "id", null},
801 {"triggers", "id", null},
802 {"sequences", "id", null},
803 {"dependency_types", "dependency_type_id", null},
804 {"dependencies", "id, depend_id", null},
805 {"auths", "id", null},
806 {"users", "name", null},
807 {"user_role", "login_id, role_id", null},
808 {"privileges", "obj_id, auth_id, privileges", null},
809 {"querylog_catalog", "id", null},
810 {"querylog_calls", "id", null},
811 {"querylog_history", "id", null},
812 {"optimizers", "name", null},
813 {"environment", "name", null}, // is a view on sys.env()
814 {"db_user_info", "name", null},
815 {"statistics", "column_id", null},
816 // old {"tracelog", "event", null}, -- Error: Profiler not started. This table now (Jun2020) contains only: ticks, stmt
817 // {"storage", "schema, table, column", null}, // is a view on table producing function: storage().
818 {"\"storage\"()", "schema, table, column", null}, // the function "storage"() also lists the storage for system tables
819 {"storagemodelinput", "schema, table, column", null},
820
821 {"rejects", "rowid", "19"}, // querying this view caused problems in versions pre Jul2015, see https://www.monetdb.org/bugzilla/show_bug.cgi?id=3794
822
823 {"keywords", "keyword", "21"}, // introduced in Jul2015 release (11.21.5)
824 {"table_types", "table_type_id", "21"}, // introduced in Jul2015 release (11.21.5)
825
826 {"function_languages", "language_id", "27"}, // introduced in Jul2017 release (11.27.1)
827 {"function_types", "function_type_id", "27"}, // introduced in Jul2017 release (11.27.1)
828 {"index_types", "index_type_id", "27"}, // introduced in Jul2017 release (11.27.1)
829 {"key_types", "key_type_id", "27"}, // introduced in Jul2017 release (11.27.1)
830 {"privilege_codes", "privilege_code_id", "27"}, // introduced in Jul2017 release (11.27.1)
831
832 {"comments", "id", "29"}, // introduced in Mar2018 release (11.29.3)
833 {"ids", "id", "29"}, // introduced in Mar2018 release (11.29.3), it is a view
834 {"var_values", "var_name", "29"}, // var_values is introduced in Mar2018 release (11.29.3), it is a view
835
836 // new views introduced in Apr 2019 feature release (11.33.3)
837 // {"tablestorage", "schema, table", "33"}, // is a view on view storage
838 // {"schemastorage", "schema", "33"}, // is a view on view storage
839 // {"storagemodel", "schema, table, column", null}, // is a view on storagemodelinput
840 // {"tablestoragemodel", "schema, table", null}, // is a view on storagemodelinput
841
842 // new tables introduced in Apr 2019 feature release (11.33.3)
843 {"table_partitions", "id", "33"},
844 {"range_partitions", "table_id, partition_id, minimum", "33"},
845 {"value_partitions", "table_id, partition_id, \"value\"", "33"},
846
847 // old {"queue", "qtag", null}, // queue has changed in Jun2020 (11.37.7), pkey was previously qtag
848 {"queue", "tag", "37"}, // queue has changed in Jun2020 (11.37.7), pkey is now called tag
849 // old {"sessions", "\"user\", login, active", null}, // sessions has changed in Jun2020 (11.37.7), pkey was previously "user", login, active
850 {"sessions", "sessionid", "37"} // sessions has changed in Jun2020 (11.37.7), pkey is now called sessionid
851 };
852
853 private static final String[][] tmp_pkeys = {
854 {"_tables", "id", null},
855 {"_columns", "id", null},
856 {"objects", "id, nr", null},
857 {"keys", "id", null},
858 {"idxs", "id", null},
859 {"triggers", "id", null}
860 };
861
862 private static final String[][] netcdf_pkeys = {
863 {"netcdf_files", "file_id", null},
864 {"netcdf_attrs", "file_id, att_name", null}, // to be verified if this is correct, maybe also include obj_name
865 {"netcdf_dims", "dim_id, file_id", null},
866 {"netcdf_vars", "var_id, file_id", null},
867 {"netcdf_vardim", "var_id, dim_id, file_id", null}
868 };
869
870 private static final String[][] geom_pkeys = {
871 {"spatial_ref_sys", "srid", null}
872 };
873
874
875 // static list of all sys tables with its alternate key (unique constraint) columns
876 // each entry contains: table_nm, ak_col_nms, from_minor_version
877 // data originally pulled from https://dev.monetdb.org/hg/MonetDB/file/Jun2020/sql/test/sys-schema/Tests/check_AlternateKey_uniqueness.sql
878 private static final String[][] sys_akeys = {
879 {"schemas", "name", null},
880 {"_tables", "schema_id, name", null},
881 {"tables", "schema_id, name", null}, // is a view
882 {"_columns", "table_id, name", null},
883 {"columns", "table_id, name", null}, // is a view
884 {"_columns", "table_id, number", null},
885 {"columns", "table_id, number", null}, // is a view
886 // The id values from sys.schemas, sys._tables, sys._columns and sys.functions combined must be exclusive (see FK from sys.privileges.obj_id)
887 {"(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) as T", "T.id", null},
888 {"(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) as T", "T.id", null},
889 // the next query used to return duplicates for overloaded functions (same function but with different arg names/types), hence it has been extended
890 {"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},
891 {"args", "func_id, name, inout", null},
892 {"types", "schema_id, systemname, sqlname", null},
893 {"objects", "id, name", null},
894 {"keys", "table_id, name", null},
895 {"idxs", "table_id, name", null},
896 {"triggers", "table_id, name", null},
897 {"sequences", "schema_id, name", null},
898 {"dependency_types", "dependency_type_name", null},
899 {"auths", "name", null}, // is this always unique?? is it possible to define a user and a role with the same name?
900 {"optimizers", "def", null},
901
902 // new tables introduced in older release
903 {"table_types", "table_type_name", "21"},
904 {"function_types", "function_type_name", "27"},
905 {"function_languages", "language_name", "27"},
906 {"index_types", "index_type_name", "27"},
907 {"key_types", "key_type_name", "27"},
908 {"privilege_codes", "privilege_code_name", "27"},
909 {"comments", "id", "29"},
910 // new tables introduced in Apr 2019 feature release (11.33.3)
911 {"table_partitions WHERE column_id IS NOT NULL", "table_id, column_id", "33"}, // requires WHERE "column_id" IS NOT NULL
912 {"table_partitions WHERE \"expression\" IS NOT NULL", "table_id, \"expression\"", "33"}, // requires WHERE "expression" IS NOT NULL
913 {"range_partitions", "table_id, partition_id, \"maximum\"", "33"}
914 };
915
916 private static final String[][] tmp_akeys = {
917 {"_tables", "schema_id, name", null},
918 {"_columns", "table_id, name", null},
919 {"_columns", "table_id, number", null},
920 {"objects", "id, name", null},
921 {"keys", "table_id, name", null},
922 {"idxs", "table_id, name", null},
923 {"triggers", "table_id, name", null}
924 };
925
926 private static final String[][] netcdf_akeys = {
927 {"netcdf_files", "location", null}
928 };
929
930 private static final String[][] geom_akeys = {
931 {"spatial_ref_sys", "auth_name, auth_srid, srtext, proj4text", null}
932 };
933
934
935 // static list of all sys tables with its foreign key columns
936 // each entry contains: table_nm, fk_col_nms, ref_col_nms, ref_tbl_nm, from_minor_version
937 // data originally pulled from https://dev.monetdb.org/hg/MonetDB/file/Jun2020/sql/test/sys-schema/Tests/check_ForeignKey_referential_integrity.sql
938 private static final String[][] sys_fkeys = {
939 {"schemas", "authorization", "id", "auths", null},
940 {"schemas", "owner", "id", "auths", null},
941 {"_tables", "schema_id", "id", "schemas", null},
942 {"tables", "schema_id", "id", "schemas", null},
943 {"_tables", "type", "table_type_id", "table_types", "21"},
944 {"tables", "type", "table_type_id", "table_types", "21"},
945 {"_columns", "table_id", "id", "_tables", null},
946 {"columns", "table_id", "id", "tables", null},
947 {"_columns", "type", "sqlname", "types", null},
948 {"columns", "type", "sqlname", "types", null},
949 {"functions", "schema_id", "id", "schemas", null},
950 {"functions", "type", "function_type_id", "function_types", "27"},
951 {"functions", "language", "language_id", "function_languages", "27"},
952 // system functions should refer only to functions in MonetDB system schemas
953 {"functions WHERE system AND ", "schema_id", "id", "schemas WHERE system", null},
954 {"args", "func_id", "id", "functions", null},
955 {"args", "type", "sqlname", "types", null},
956 {"types", "schema_id", "id", "schemas", null},
957 // {"types WHERE schema_id <> 0 AND ", "schema_id", "id", "schemas", null}, // types with schema_id = 0 should no longer exist
958 {"objects", "id", "id", "ids", "29"},
959 {"ids WHERE obj_type IN ('key', 'index') AND ", "id", "id", "objects", "29"},
960 {"keys", "id", "id", "objects", null},
961 {"keys", "table_id", "id", "_tables", null},
962 {"keys", "table_id", "id", "tables", null},
963 {"keys", "type", "key_type_id", "key_types", "27"},
964 {"keys WHERE rkey <> -1 AND ", "rkey", "id", "keys", null},
965 // 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.
966 {"idxs", "id", "id", "objects", null},
967 {"idxs", "table_id", "id", "_tables", null},
968 {"idxs", "table_id", "id", "tables", null},
969 {"idxs", "type", "index_type_id", "index_types", "27"},
970 {"sequences", "schema_id", "id", "schemas", null},
971 {"triggers", "table_id", "id", "_tables", null},
972 {"triggers", "table_id", "id", "tables", null},
973 {"comments", "id", "id", "ids", "29"},
974 {"dependencies", "id", "id", "ids", "29"},
975 {"dependencies", "depend_id", "id", "ids", "29"},
976 {"dependencies", "depend_type", "dependency_type_id", "dependency_types", null},
977 {"dependencies", "id, depend_id, depend_type", "v.id, v.used_by_id, v.depend_type", "dependencies_vw v", "29"}, // dependencies_vw is introduced in Mar2018 release (11.29.3), it is a view
978 {"auths WHERE grantor > 0 AND ", "grantor", "id", "auths", null},
979 {"users", "name", "name", "auths", null},
980 {"users", "default_schema", "id", "schemas", null},
981 {"db_user_info", "name", "name", "auths", null},
982 {"db_user_info", "default_schema", "id", "schemas", null},
983 {"user_role", "login_id", "id", "auths", null},
984 {"user_role", "login_id", "a.id", "auths a WHERE a.name IN (SELECT u.name FROM sys.users u)", null},
985 {"user_role", "role_id", "id", "auths", null},
986 {"user_role", "role_id", "a.id", "auths a WHERE a.name IN (SELECT u.name FROM sys.users u)", null},
987 {"user_role", "role_id", "id", "roles", "29"}, // roles is introduced in Mar2018 release (11.29.3), it is a view
988 {"privileges", "obj_id", "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) as t", null},
989 {"privileges", "auth_id", "id", "auths", null},
990 {"privileges WHERE grantor > 0 AND ", "grantor", "id", "auths", null},
991 {"privileges", "privileges", "privilege_code_id", "privilege_codes", "27"},
992 {"querylog_catalog", "owner", "name", "users", null},
993 {"querylog_catalog", "pipe", "name", "optimizers", null},
994 {"querylog_calls", "id", "id", "querylog_catalog", null},
995 {"querylog_history", "id", "id", "querylog_catalog", null},
996 {"querylog_history", "owner", "name", "users", null},
997 {"querylog_history", "pipe", "name", "optimizers", null},
998 {"queue WHERE tag > cast(0 as oid) AND ", "tag", "tag", "queue", null},
999 {"queue WHERE tag > cast(0 as oid) AND ", "tag", "cast(tag as oid)", "queue", null},
1000 {"queue", "tag", "cast(tag as oid)", "queue", null},
1001 // not a fk: {"queue", "\"username\"", "name", "users", null}, // as queue contains a historical list, the user may have been removed in the meantime, so not a real persistent fk
1002 {"sessions", "\"username\"", "name", "users", null},
1003 {"statistics", "column_id", "id", "(SELECT id FROM sys._columns UNION ALL SELECT id FROM tmp._columns) as c", null},
1004 {"statistics", "type", "sqlname", "types", null},
1005 {"storage()", "schema", "name", "schemas", null},
1006 {"storage()", "table", "name", "(SELECT name FROM sys._tables UNION ALL SELECT name FROM tmp._tables) as t", null},
1007 {"storage()", "schema, table", "sname, tname", "(SELECT sch.name as sname, tbl.name as tname FROM sys.schemas AS sch JOIN sys.tables AS tbl ON sch.id = tbl.schema_id) as t", null},
1008 {"storage()", "column", "name", "(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) as c", null},
1009 {"storage()", "type", "sqlname", "types", null},
1010 {"storage", "schema", "name", "schemas", null},
1011 {"storage", "table", "name", "(SELECT name FROM sys._tables UNION ALL SELECT name FROM tmp._tables) as t", null},
1012 {"storage", "schema, table", "sname, tname", "(SELECT sch.name as sname, tbl.name as tname FROM sys.schemas AS sch JOIN sys.tables AS tbl ON sch.id = tbl.schema_id) as t", null},
1013 {"storage", "column", "name", "(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) as c", null},
1014 {"storage", "type", "sqlname", "types", null},
1015 {"storagemodel", "schema", "name", "schemas", null},
1016 {"storagemodel", "table", "name", "(SELECT name FROM sys._tables UNION ALL SELECT name FROM tmp._tables) as t", null},
1017 {"storagemodel", "schema, table", "sname, tname", "(SELECT sch.name as sname, tbl.name as tname FROM sys.schemas AS sch JOIN sys.tables AS tbl ON sch.id = tbl.schema_id) as t", null},
1018 {"storagemodel", "column", "name", "(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) as c", null},
1019 {"storagemodel", "type", "sqlname", "types", null},
1020 {"storagemodelinput", "schema", "name", "schemas", null},
1021 {"storagemodelinput", "table", "name", "(SELECT name FROM sys._tables UNION ALL SELECT name FROM tmp._tables) as t", null},
1022 {"storagemodelinput", "schema, table", "sname, tname", "(SELECT sch.name as sname, tbl.name as tname FROM sys.schemas AS sch JOIN sys.tables AS tbl ON sch.id = tbl.schema_id) as t", null},
1023 {"storagemodelinput", "column", "name", "(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) as c", null},
1024 {"storagemodelinput", "type", "sqlname", "types", null},
1025 {"tablestoragemodel", "schema", "name", "schemas", null},
1026 {"tablestoragemodel", "table", "name", "(SELECT name FROM sys._tables UNION ALL SELECT name FROM tmp._tables) as t", null},
1027 {"tablestoragemodel", "schema, table", "sname, tname", "(SELECT sch.name as sname, tbl.name as tname FROM sys.schemas AS sch JOIN sys.tables AS tbl ON sch.id = tbl.schema_id) as t", null},
1028 // new tables / views introduced in Apr2019 "33"
1029 {"schemastorage", "schema", "name", "schemas", "33"},
1030 {"tablestorage", "schema", "name", "schemas", "33"},
1031 {"tablestorage", "table", "name", "(SELECT name FROM sys._tables UNION ALL SELECT name FROM tmp._tables) as t", "33"},
1032 {"tablestorage", "schema, table", "sname, tname", "(SELECT sch.name as sname, tbl.name as tname FROM sys.schemas AS sch JOIN sys.tables AS tbl ON sch.id = tbl.schema_id) as t", "33"},
1033 {"table_partitions", "table_id", "id", "_tables", "33"},
1034 {"table_partitions WHERE column_id IS NOT NULL AND ", "column_id", "id", "_columns", "33"},
1035 {"range_partitions", "table_id", "id", "_tables", "33"},
1036 {"range_partitions", "partition_id", "id", "table_partitions", "33"},
1037 {"value_partitions", "table_id", "id", "_tables", "33"},
1038 {"value_partitions", "partition_id", "id", "table_partitions", "33"}
1039 };
1040
1041 private static final String[][] tmp_fkeys = {
1042 {"_tables", "schema_id", "id", "sys.schemas", null},
1043 {"_tables", "type", "table_type_id", "sys.table_types", "21"},
1044 {"_columns", "table_id", "id", "_tables", null},
1045 {"_columns", "type", "sqlname", "sys.types", null},
1046 {"keys", "id", "id", "objects", null},
1047 {"keys", "table_id", "id", "_tables", null},
1048 {"keys", "type", "key_type_id", "sys.key_types", "27"},
1049 {"keys WHERE rkey <> -1 AND ", "rkey", "id", "keys", null},
1050 // 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.
1051 {"idxs", "id", "id", "objects", null},
1052 {"idxs", "table_id", "id", "_tables", null},
1053 {"idxs", "type", "index_type_id", "sys.index_types", "27"},
1054 {"objects", "id", "id", "sys.ids", "29"},
1055 {"triggers", "table_id", "id", "_tables", null}
1056 };
1057
1058 private static final String[][] netcdf_fkeys = {
1059 {"netcdf_attrs", "file_id", "file_id", "netcdf_files", null},
1060 {"netcdf_dims", "file_id", "file_id", "netcdf_files", null},
1061 {"netcdf_vars", "file_id", "file_id", "netcdf_files", null},
1062 {"netcdf_vardim", "file_id", "file_id", "netcdf_files", null},
1063 {"netcdf_vardim", "dim_id", "dim_id", "netcdf_dims", null},
1064 {"netcdf_vardim", "dim_id, file_id", "dim_id, file_id", "netcdf_dims", null},
1065 {"netcdf_vardim", "var_id", "var_id", "netcdf_vars", null},
1066 {"netcdf_vardim", "var_id, file_id", "var_id, file_id", "netcdf_vars", null}
1067 };
1068
1069 private static final String[][] geom_fkeys = {
1070 {"spatial_ref_sys", "auth_srid", "srid", "spatial_ref_sys", null}
1071 };
1072
1073
1074 // static list of all sys tables with its not null constraint columns
1075 // each entry contains: table_nm, col_nm, from_minor_version
1076 // data originally pulled from https://dev.monetdb.org/hg/MonetDB/file/Jun2020/sql/test/sys-schema/Tests/check_Not_Nullable_columns.sql
1077 private static final String[][] sys_notnull = {
1078 {"_columns", "id", null},
1079 {"_columns", "name", null},
1080 {"_columns", "type", null},
1081 {"_columns", "type_digits", null},
1082 {"_columns", "type_scale", null},
1083 {"_columns", "table_id", null},
1084 {"_columns", "\"null\"", null},
1085 {"_columns", "number", null},
1086 {"_tables", "id", null},
1087 {"_tables", "name", null},
1088 {"_tables", "schema_id", null},
1089 {"_tables", "type", null},
1090 {"_tables", "system", null},
1091 {"_tables", "commit_action", null},
1092 {"_tables", "access", null},
1093 {"args", "id", null},
1094 {"args", "func_id", null},
1095 {"args", "name", null},
1096 {"args", "type", null},
1097 {"args", "type_digits", null},
1098 {"args", "type_scale", null},
1099 {"args", "inout", null},
1100 {"args", "number", null},
1101 {"auths", "id", null},
1102 {"auths", "name", null},
1103 {"auths", "grantor", null},
1104 {"db_user_info", "name", null},
1105 {"db_user_info", "fullname", null},
1106 {"db_user_info", "default_schema", null},
1107 {"dependencies", "id", null},
1108 {"dependencies", "depend_id", null},
1109 {"dependencies", "depend_type", null},
1110 {"function_languages", "language_id", "27"},
1111 {"function_languages", "language_name", "27"},
1112 {"function_types", "function_type_id", "27"},
1113 {"function_types", "function_type_name", "27"},
1114 {"function_types", "function_type_keyword", "27"},
1115 {"functions", "id", null},
1116 {"functions", "name", null},
1117 {"functions", "func", null},
1118 {"functions", "mod", null},
1119 {"functions", "language", null},
1120 {"functions", "type", null},
1121 {"functions", "side_effect", null},
1122 {"functions", "varres", null},
1123 {"functions", "vararg", null},
1124 {"functions", "schema_id", null},
1125 {"functions", "system", null},
1126 {"idxs", "id", null},
1127 {"idxs", "table_id", null},
1128 {"idxs", "type", null},
1129 {"idxs", "name", null},
1130 {"index_types", "index_type_id", "27"},
1131 {"index_types", "index_type_name", "27"},
1132 {"key_types", "key_type_id", "27"},
1133 {"key_types", "key_type_name", "27"},
1134 {"keys", "id", null},
1135 {"keys", "table_id", null},
1136 {"keys", "type", null},
1137 {"keys", "name", null},
1138 {"keys", "rkey", null},
1139 {"keys", "action", null},
1140 {"keywords", "keyword", "21"},
1141 {"objects", "id", null},
1142 {"objects", "name", null},
1143 {"objects", "nr", null},
1144 {"optimizers", "name", null},
1145 {"optimizers", "def", null},
1146 {"optimizers", "status", null},
1147 {"privilege_codes", "privilege_code_id", "27"},
1148 {"privilege_codes", "privilege_code_name", "27"},
1149 {"privileges", "obj_id", null},
1150 {"privileges", "auth_id", null},
1151 {"privileges", "privileges", null},
1152 {"privileges", "grantor", null},
1153 {"privileges", "grantable", null},
1154 {"schemas", "id", null},
1155 {"schemas", "name", null},
1156 {"schemas", "authorization", null},
1157 {"schemas", "owner", null},
1158 {"schemas", "system", null},
1159 {"sequences", "id", null},
1160 {"sequences", "schema_id", null},
1161 {"sequences", "name", null},
1162 {"sequences", "start", null},
1163 {"sequences", "minvalue", null},
1164 {"sequences", "maxvalue", null},
1165 {"sequences", "increment", null},
1166 {"sequences", "cacheinc", null},
1167 {"sequences", "cycle", null},
1168 {"statistics", "column_id", null},
1169 {"statistics", "type", null},
1170 {"statistics", "width", null},
1171 {"statistics", "stamp", null},
1172 {"statistics", "\"sample\"", null},
1173 {"statistics", "count", null},
1174 {"statistics", "\"unique\"", null},
1175 {"statistics", "nils", null},
1176 {"statistics", "sorted", null},
1177 {"statistics", "revsorted", null},
1178 // the table producing function "storage"() also lists the storage for system tables, whereas the view "storage" does not, so use "storage"()
1179 {"\"storage\"()", "schema", null},
1180 {"\"storage\"()", "table", null},
1181 {"\"storage\"()", "column", null},
1182 {"\"storage\"()", "type", null},
1183 {"\"storage\"()", "mode", null},
1184 {"\"storage\"()", "location", null},
1185 {"\"storage\"()", "count", null},
1186 {"\"storage\"()", "typewidth", null},
1187 {"\"storage\"()", "columnsize", null},
1188 {"\"storage\"()", "heapsize", null},
1189 {"\"storage\"()", "hashes", null},
1190 {"\"storage\"()", "phash", null},
1191 {"\"storage\"()", "imprints", null},
1192 {"\"storage\"()", "orderidx", null},
1193 {"storagemodelinput", "schema", null},
1194 {"storagemodelinput", "table", null},
1195 {"storagemodelinput", "column", null},
1196 {"storagemodelinput", "type", null},
1197 {"storagemodelinput", "typewidth", null},
1198 {"storagemodelinput", "count", null},
1199 {"storagemodelinput", "\"distinct\"", null},
1200 {"storagemodelinput", "atomwidth", null},
1201 {"storagemodelinput", "reference", null},
1202 {"storagemodelinput", "sorted", null},
1203 {"storagemodelinput", "\"unique\"", null},
1204 {"storagemodelinput", "isacolumn", null},
1205 {"table_types", "table_type_id", "21"},
1206 {"table_types", "table_type_name", "21"},
1207 {"tables", "id", null},
1208 {"tables", "name", null},
1209 {"tables", "schema_id", null},
1210 {"tables", "type", null},
1211 {"tables", "system", null},
1212 {"tables", "commit_action", null},
1213 {"tables", "access", null},
1214 {"tables", "temporary", null},
1215 {"tracelog", "ticks", null},
1216 {"tracelog", "stmt", null},
1217 {"triggers", "id", null},
1218 {"triggers", "name", null},
1219 {"triggers", "table_id", null},
1220 {"triggers", "time", null},
1221 {"triggers", "orientation", null},
1222 {"triggers", "event", null},
1223 {"triggers", "statement", null},
1224 {"types", "id", null},
1225 {"types", "systemname", null},
1226 {"types", "sqlname", null},
1227 {"types", "digits", null},
1228 {"types", "scale", null},
1229 {"types", "radix", null},
1230 {"types", "eclass", null},
1231 {"types", "schema_id", null},
1232 {"user_role", "login_id", null},
1233 {"user_role", "role_id", null},
1234 {"users", "name", null},
1235 {"users", "fullname", null},
1236 {"users", "default_schema", null},
1237 {"var_values", "var_name", "29"},
1238 {"var_values", "value", "29"},
1239 // new tables introduced in Apr 2019 feature release (11.33.3)
1240 {"range_partitions", "table_id", "33"},
1241 {"range_partitions", "partition_id", "33"},
1242 {"range_partitions", "with_nulls", "33"},
1243 {"table_partitions", "id", "33"},
1244 {"table_partitions", "table_id", "33"},
1245 {"table_partitions", "type", "33"},
1246 {"value_partitions", "table_id", "33"},
1247 {"value_partitions", "partition_id", "33"},
1248 {"value_partitions", "value", "33"} // Can this be null when WITH NULL VALUES is specified?
1249 };
1250
1251 private static final String[][] tmp_notnull = {
1252 {"_columns", "id", null},
1253 {"_columns", "name", null},
1254 {"_columns", "type", null},
1255 {"_columns", "type_digits", null},
1256 {"_columns", "type_scale", null},
1257 {"_columns", "table_id", null},
1258 {"_columns", "\"null\"", null},
1259 {"_columns", "number", null},
1260 {"_tables", "id", null},
1261 {"_tables", "name", null},
1262 {"_tables", "schema_id", null},
1263 {"_tables", "type", null},
1264 {"_tables", "system", null},
1265 {"_tables", "commit_action", null},
1266 {"_tables", "access", null},
1267 {"idxs", "id", null},
1268 {"idxs", "table_id", null},
1269 {"idxs", "type", null},
1270 {"idxs", "name", null},
1271 {"keys", "id", null},
1272 {"keys", "table_id", null},
1273 {"keys", "type", null},
1274 {"keys", "name", null},
1275 {"keys", "rkey", null},
1276 {"keys", "action", null},
1277 {"objects", "id", null},
1278 {"objects", "name", null},
1279 {"objects", "nr", null},
1280 {"triggers", "id", null},
1281 {"triggers", "name", null},
1282 {"triggers", "table_id", null},
1283 {"triggers", "time", null},
1284 {"triggers", "orientation", null},
1285 {"triggers", "event", null},
1286 {"triggers", "statement", null}
1287 };
1288
1289 private static final String[][] netcdf_notnull = {
1290 {"netcdf_files", "file_id", null},
1291 {"netcdf_files", "location", null},
1292 {"netcdf_dims", "dim_id", null},
1293 {"netcdf_dims", "file_id", null},
1294 {"netcdf_dims", "name", null},
1295 {"netcdf_dims", "length", null},
1296 {"netcdf_vars", "var_id", null},
1297 {"netcdf_vars", "file_id", null},
1298 {"netcdf_vars", "name", null},
1299 {"netcdf_vars", "vartype", null},
1300 {"netcdf_vardim", "var_id", null},
1301 {"netcdf_vardim", "dim_id", null},
1302 {"netcdf_vardim", "file_id", null},
1303 {"netcdf_vardim", "dimpos", null},
1304 {"netcdf_attrs", "obj_name", null},
1305 {"netcdf_attrs", "att_name", null},
1306 {"netcdf_attrs", "att_type", null},
1307 {"netcdf_attrs", "value", null},
1308 {"netcdf_attrs", "file_id", null},
1309 {"netcdf_attrs", "gr_name", null}
1310 };
1311
1312 private static final String[][] geom_notnull = {
1313 {"spatial_ref_sys", "srid", null},
1314 {"spatial_ref_sys", "auth_name", null},
1315 {"spatial_ref_sys", "auth_srid", null},
1316 {"spatial_ref_sys", "srtext", null},
1317 {"spatial_ref_sys", "proj4text", null}
1318 };
1319 }