monetdb-java: src/main/java/org/monetdb/util/MDBvalidator.java annotate

annotate src/main/java/org/monetdb/util/MDBvalidator.java @ 468:a888cabe2b15

Add missing FKey check from sessions.optimzer to optimizers.name. Remove old no longer usefull fk checks for queue.tag It used to be from qtag to tag but column qtag has been removed since Jun2020.

author	Martin van Dinther <martin.van.dinther@monetdbsolutions.com>
date	Wed, 14 Apr 2021 19:29:34 +0200 (2021-04-14)
parents	ebda95332d57
children	9fcf77d703b1

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

Mercurial > hg > monetdb-java

annotate src/main/java/org/monetdb/util/MDBvalidator.java @ 468:a888cabe2b15