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

annotate src/main/java/org/monetdb/util/MDBvalidator.java @ 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. These are intended only for use in the internal automated testing web.

author	Martin van Dinther <martin.van.dinther@monetdbsolutions.com>
date	Thu, 11 Mar 2021 19:52:08 +0100 (2021-03-11)
parents	d9f9e077cd03
children	ebda95332d57

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)
387 37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	112 public static void validateSqlCatalogIntegrity(final Connection 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	113 validateSqlCatalogIntegrity(conn, true);
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 }
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	115
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	116 public static void validateSqlCatalogIntegrity(final Connection conn, 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	117 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	118 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	119 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	120 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	121 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	122 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	123 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	124
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	125 public static void validateSqlNetcdfTablesIntegrity(final Connection 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	126 validateSqlNetcdfTablesIntegrity(conn, true);
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	127 }
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	128
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	129 public static void validateSqlNetcdfTablesIntegrity(final Connection conn, 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	130 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	131 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	132 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	133 // 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	134 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	135 && 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	136 && 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	137 && 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	138 && 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	139 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	140 }
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 public static void validateSqlGeomTablesIntegrity(final Connection 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	144 validateSqlGeomTablesIntegrity(conn, true);
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	145 }
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
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	147 public static void validateSqlGeomTablesIntegrity(final Connection conn, 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	148 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	149 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	150 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	151 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	152 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	153 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	154 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	155
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	156 public static void validateSchemaIntegrity(final Connection conn, final String schema) {
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	157 validateSchemaIntegrity(conn, schema, true);
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	158 }
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	159
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 public static void validateSchemaIntegrity(final Connection conn, final String schema, 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	161 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	162 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	163 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	164 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	165 else
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	166 if (mdbv.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	167 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	168 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	169
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	170 public static void validateDBIntegrity(final Connection 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	171 validateDBIntegrity(conn, true);
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	172 }
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	173
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	174 public static void validateDBIntegrity(final Connection conn, 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	175 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	176 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	177 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	178 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	179 return;
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	180
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	181 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	182 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	183 try {
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	184 // 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	185 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	186 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	187 // 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	188 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	189 String schema = 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	190 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	191 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	192 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	193 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	194 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	195 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	196 } 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	197 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	198 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	199 freeStmtRs(stmt, rs);
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	200 if (!hasUserSchemas && mdbv.showValidationInfo)
387 37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	201 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	202 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	203
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	204 // 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	205 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	206 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	207 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	208 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	209 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	210 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	211 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	212 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	213 {
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	214 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	215
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	216 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	217 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	218 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	219 } else {
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	220 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	221 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	222
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	223 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	224 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	225 } else {
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	226 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	227 }
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 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	230 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	231 } else {
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	232 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	233 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	234
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	235 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	236 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	237 } else {
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	238 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	239 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	240
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	241 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	242 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	243 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	244
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	245 /* 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	246 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	247 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	248 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	249 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	250 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	251 {
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	252 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	253 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	254 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	255
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	256 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	257 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	258 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	259 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	260 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	261 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	262 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	263 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	264 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	265 // 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	266 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	267 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	268 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	269 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	270 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	271 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	272 .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	273 .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	274 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	275 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	276 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	277 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	278
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	279 /* 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	280 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	281 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	282 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	283 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	284 {
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	285 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	286 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	287 return;
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	288
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	289 // 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	290 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	291 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	292 + " 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	293 .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	294 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	295 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	296 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	297 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	298
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	299 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	300 try {
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	301 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	302 // 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	303 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	304 .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	305 + " 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	306 .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	307 .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	308 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	309 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	310 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	311 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	312 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	313 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	314 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	315 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	316 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	317 // 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	318 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	319 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	320 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	321 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	322 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	323 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	324 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	325 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	326 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	327 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	328 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	329 else
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	330 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	331 } else {
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	332 // 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	333 // 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	334 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	335 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	336 .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	337 .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	338 .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	339 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	340 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	341 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	342 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	343 }
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 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	346 // 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	347 // 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	348 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	349 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	350 .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	351 .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	352 .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	353 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	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 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	356 } 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	357 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	358 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	359 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	360 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	361 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	362
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	363 /* 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	364 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	365 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	366 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	367 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	368 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	369 {
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	370 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	371 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	372 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	373
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	374 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	375 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	376 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	377 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	378 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	379 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	380 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	381 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	382 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	383 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	384 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	385 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	386 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	387 // 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	388 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	389 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	390 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	391 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	392 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	393 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	394 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	395 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	396 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	397 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	398 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	399 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	400
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	401 /* 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	402 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	403 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	404 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	405 {
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	406 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	407 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	408 // 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	409 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	410 } 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	411 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	412 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	413 }
387 37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	414 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	415 return;
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	416
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	417 // 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	418 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	419 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	420 + " 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	421 .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	422 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	423 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	424 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	425 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	426
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	427 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	428 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	429 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	430 // 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	431 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	432 "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	433 "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	434 // ", 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	435 " 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	436 " 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	437 " 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	438 " 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	439 " 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	440 " 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	441 " 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	442 " 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	443 " 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	444 " 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	445 .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	446 .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	447 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	448 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	449 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	450 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	451 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	452 // 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	453 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	454 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	455 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	456 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	457 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	458 // 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	459 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	460 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	461 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	462 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	463 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	464 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	465 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	466 // 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	467 // 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	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 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	470 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	471 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	472 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	473
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	474 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	475 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	476 // 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	477 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	478 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	479 }
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 // 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	481 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	482 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	483
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 // 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	485 // 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	486 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	487 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	488 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	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(", * 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	495 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	496 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	497 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	498 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	499 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	500 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	501 }
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 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	503 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	504 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	505 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	506 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	507 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	508 }
d9f9e077cd03 Finish implementation of foreign key referential integrity checks for user tables. Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: 406 diff changeset	509 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	510 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	511 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	512 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	513 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	514 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	515 }
d9f9e077cd03 Finish implementation of foreign key referential integrity checks for user tables. Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: 406 diff changeset	516 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	517 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	518 }
d9f9e077cd03 Finish implementation of foreign key referential integrity checks for user tables. Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: 406 diff changeset	519 }
d9f9e077cd03 Finish implementation of foreign key referential integrity checks for user tables. Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: 406 diff changeset	520 } 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	521 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	522 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	523 }
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 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	525 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	526
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	527 /* 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	528 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	529 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	530 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	531 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	532 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	533 {
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	534 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	535 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	536 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	537
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	538 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	539 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	540 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	541 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	542 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	543 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	544 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	545 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	546 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	547 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	548 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	549 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	550 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	551 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	552 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	553 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	554 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	555 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	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 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	558 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	559 } else {
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	560 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	561 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	562 // 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	563 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	564 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	565 .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	566 .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	567 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	568 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	569 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	570 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	571
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	572 /* 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	573 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	574 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	575 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	576 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	577 {
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	578 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	579 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	580 return;
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	581
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	582 // 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	583 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	584 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	585 + " 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	586 + " 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	587 .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	588 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	589 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	590 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	591 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	592
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	593 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	594 try {
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	595 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	596 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	597 .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	598 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	599 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	600 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	601 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	602 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	603 // 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	604 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	605 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	606 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	607 // 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	608 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	609 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	610 .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	611 .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	612 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	613 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	614 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	615 } 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	616 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	617 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	618 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	619 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	620 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	621
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	622 /* 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	623 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	624 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	625 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	626 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	627 {
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	628 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	629 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	630 return;
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	631
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	632 // 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	633 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	634 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	635 + " 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	636 + " 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	637 + " 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	638 .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	639 .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	640 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	641 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	642 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	643 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	644
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	645 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	646 try {
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 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	649 .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	650 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	651 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	652 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	653 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	654 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	655 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	656 // 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	657 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	658 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	659 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	660 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	661 // 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	662 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	663 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	664 .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	665 .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	666 .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	667 .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	668 .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	669 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	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 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	672 } 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	673 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	674 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	675 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	676 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	677 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	678
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	679
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	680 /* 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	681 * 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	682 * 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	683 * 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	684 * 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	685 */
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	686 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	687 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	688 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	689 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	690 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	691 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	692 {
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	693 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	694 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	695 return;
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	696
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	697 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	698 try {
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 (verbose) {
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	700 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	701 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	702 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	703 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	704 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	705 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	706 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	707 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	708 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	709 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	710 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	711 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	712 // 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	713 row++;
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	714 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	715 // 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	716 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	717 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	718 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	719 }
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('\n');
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	721 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	722 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	723 // 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	724 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	725 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	726 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	727 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	728 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	729 } else {
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	730 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	731 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	732 \|\| 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	733 \|\| 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	734 \|\| 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	735 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	736 } else {
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	737 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	738 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	739 }
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 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	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 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	745 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	746 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	747 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	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 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	750 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	751 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	752 } 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	753 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	754 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	755 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	756 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	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 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	760 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	761 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	762 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	763
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	764 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	765 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	766 try {
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	767 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	768 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	769 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	770 // 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	771 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	772 }
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 } 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	775 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	776 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	777 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	778 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	779 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	780 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	781
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	782 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	783 try {
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	784 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	785 } 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	786 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	787 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	788 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	789 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	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 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	793 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	794 // 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	795 try {
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	796 // 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	797 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	798 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	799 // 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	800 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	801 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	802 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	803 } 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	804 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	805 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	806 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	807 // 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	808 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	809 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	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 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	813 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	814
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	815 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	816 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	817 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	818
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	819 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	820 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	821 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	822 } 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	823 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	824 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	825 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	826 }
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 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	829 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	830 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	831 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	832
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	833 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	834 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	835 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	836 try {
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	837 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	838 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	839 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	840 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	841 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	842 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	843 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	844 } 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	845 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	846 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	847 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	848 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	849 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	850 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	851
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	852 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	853 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	854 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	855 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	856
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	857 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	858 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	859 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	860 try {
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	861 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	862 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	863 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	864 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	865 && 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	866 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	867 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	868 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	869 } 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	870 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	871 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	872 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	873 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	874 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	875 }
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 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	878 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	879 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	880 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	881 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	882 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	883 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	884 {
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	885 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	886 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	887 .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	888 .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	889 .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	890 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	891 }
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 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	894 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	895 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	896 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	897 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	898 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	899
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	900 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	901 // 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	902 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	903 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	904 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	905 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	906 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	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 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	911 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	912 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	913 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	914 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	915 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	916 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	917 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	918 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	919 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	920 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	921 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	922 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	923
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	924
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	925 // ******* 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	926 // 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	927
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	928 // 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	929 // 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	930 // 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	931 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	932 {"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	933 {"_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	934 {"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	935 {"_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	936 {"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	937 {"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	938 {"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	939 {"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	940 {"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	941 {"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	942 {"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	943 {"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	944 {"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	945 {"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	946 {"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	947 {"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	948 {"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	949 {"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	950 {"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	951 {"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	952 {"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	953 {"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	954 {"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	955 {"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	956 {"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	957 {"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	958 {"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	959 // 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	960 // {"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	961 {"\"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	962 {"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	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 {"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	965
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	966 {"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	967 {"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	968
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	969 {"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	970 {"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	971 {"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	972 {"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	973 {"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	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 {"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	976 {"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	977 {"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	978
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	979 // 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	980 // {"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	981 // {"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	982 // {"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	983 // {"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	984
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	985 // 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	986 {"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	987 {"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	988 {"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	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 // 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	991 {"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	992 // 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	993 {"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	994 };
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	995
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	996 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	997 {"_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	998 {"_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	999 {"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	1000 {"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	1001 {"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	1002 {"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	1003 };
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	1004
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	1005 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	1006 {"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	1007 {"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	1008 {"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	1009 {"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	1010 {"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	1011 };
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	1012
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	1013 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	1014 {"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	1015 };
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	1016
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	1017
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	1018 // 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	1019 // 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	1020 // 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	1021 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	1022 {"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	1023 {"_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	1024 {"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	1025 {"_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	1026 {"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	1027 {"_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	1028 {"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	1029 // 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	1030 {"(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	1031 {"(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	1032 // 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	1033 {"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	1034 {"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	1035 {"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	1036 {"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	1037 {"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	1038 {"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	1039 {"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	1040 {"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	1041 {"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	1042 {"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	1043 {"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	1044
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	1045 // 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	1046 {"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	1047 {"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	1048 {"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	1049 {"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	1050 {"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	1051 {"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	1052 {"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	1053 // 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	1054 {"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	1055 {"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	1056 {"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	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
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	1059 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	1060 {"_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	1061 {"_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	1062 {"_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	1063 {"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	1064 {"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	1065 {"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	1066 {"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	1067 };
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	1068
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	1069 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	1070 {"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	1071 };
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	1072
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	1073 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	1074 {"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	1075 };
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	1076
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	1077
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	1078 // 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	1079 // 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	1080 // 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	1081 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	1082 {"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	1083 {"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	1084 {"_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	1085 {"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	1086 {"_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	1087 {"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	1088 {"_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	1089 {"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	1090 {"_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	1091 {"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	1092 {"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	1093 {"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	1094 {"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	1095 // 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	1096 {"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	1097 {"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	1098 {"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	1099 {"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	1100 // {"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	1101 {"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	1102 {"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	1103 {"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	1104 {"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	1105 {"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	1106 {"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	1107 {"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	1108 // 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	1109 {"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	1110 {"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	1111 {"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	1112 {"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	1113 {"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	1114 {"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	1115 {"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	1116 {"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	1117 {"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	1118 {"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	1119 {"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	1120 {"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	1121 {"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	1122 {"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	1123 {"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	1124 {"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	1125 {"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	1126 {"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	1127 {"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	1128 {"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	1129 {"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	1130 {"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	1131 {"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	1132 {"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	1133 {"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	1134 {"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	1135 {"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	1136 {"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	1137 {"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	1138 {"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	1139 {"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	1140 {"querylog_history", "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	1141 {"queue WHERE tag > cast(0 as oid) AND ", "tag", "tag", "queue", 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 {"queue WHERE tag > cast(0 as oid) AND ", "tag", "cast(tag as oid)", "queue", 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 {"queue", "tag", "cast(tag as oid)", "queue", null},
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	1144 // 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	1145 {"sessions", "\"username\"", "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	1146 {"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	1147 {"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	1148 {"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	1149 {"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	1150 {"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	1151 {"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	1152 {"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	1153 {"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	1154 {"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	1155 {"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	1156 {"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	1157 {"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	1158 {"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	1159 {"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	1160 {"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	1161 {"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	1162 {"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	1163 {"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	1164 {"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	1165 {"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	1166 {"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	1167 {"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	1168 {"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	1169 {"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	1170 {"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	1171 // 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	1172 {"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	1173 {"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	1174 {"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	1175 {"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	1176 {"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	1177 {"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	1178 {"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	1179 {"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	1180 {"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	1181 {"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	1182 };
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 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	1185 {"_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	1186 {"_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	1187 {"_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	1188 {"_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	1189 {"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	1190 {"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	1191 {"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	1192 {"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	1193 // 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	1194 {"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	1195 {"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	1196 {"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	1197 {"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	1198 {"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	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 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	1202 {"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	1203 {"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	1204 {"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	1205 {"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	1206 {"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	1207 {"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	1208 {"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	1209 {"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	1210 };
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	1211
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	1212 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	1213 {"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	1214 };
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	1215
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	1216
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	1217 // 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	1218 // 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	1219 // 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	1220 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	1221 {"_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	1222 {"_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	1223 {"_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	1224 {"_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	1225 {"_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	1226 {"_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	1227 {"_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	1228 {"_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	1229 {"_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	1230 {"_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	1231 {"_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	1232 {"_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	1233 {"_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	1234 {"_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	1235 {"_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	1236 {"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	1237 {"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	1238 {"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	1239 {"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	1240 {"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	1241 {"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	1242 {"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	1243 {"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	1244 {"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	1245 {"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	1246 {"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	1247 {"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	1248 {"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	1249 {"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	1250 {"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	1251 {"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	1252 {"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	1253 {"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	1254 {"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	1255 {"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	1256 {"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	1257 {"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	1258 {"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	1259 {"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	1260 {"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	1261 {"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	1262 {"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	1263 {"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	1264 {"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	1265 {"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	1266 {"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	1267 {"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	1268 {"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	1269 {"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	1270 {"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	1271 {"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	1272 {"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	1273 {"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	1274 {"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	1275 {"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	1276 {"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	1277 {"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	1278 {"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	1279 {"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	1280 {"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	1281 {"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	1282 {"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	1283 {"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	1284 {"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	1285 {"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	1286 {"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	1287 {"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	1288 {"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	1289 {"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	1290 {"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	1291 {"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	1292 {"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	1293 {"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	1294 {"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	1295 {"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	1296 {"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	1297 {"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	1298 {"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	1299 {"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	1300 {"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	1301 {"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	1302 {"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	1303 {"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	1304 {"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	1305 {"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	1306 {"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	1307 {"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	1308 {"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	1309 {"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	1310 {"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	1311 {"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	1312 {"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	1313 {"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	1314 {"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	1315 {"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	1316 {"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	1317 {"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	1318 {"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	1319 {"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	1320 {"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	1321 // 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	1322 {"\"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	1323 {"\"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	1324 {"\"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	1325 {"\"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	1326 {"\"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	1327 {"\"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	1328 {"\"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	1329 {"\"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	1330 {"\"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	1331 {"\"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	1332 {"\"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	1333 {"\"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	1334 {"\"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	1335 {"\"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	1336 {"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	1337 {"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	1338 {"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	1339 {"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	1340 {"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	1341 {"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	1342 {"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	1343 {"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	1344 {"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	1345 {"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	1346 {"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	1347 {"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	1348 {"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	1349 {"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	1350 {"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	1351 {"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	1352 {"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	1353 {"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	1354 {"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	1355 {"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	1356 {"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	1357 {"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	1358 {"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	1359 {"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	1360 {"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	1361 {"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	1362 {"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	1363 {"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	1364 {"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	1365 {"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	1366 {"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	1367 {"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	1368 {"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	1369 {"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	1370 {"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	1371 {"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	1372 {"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	1373 {"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	1374 {"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	1375 {"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	1376 {"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	1377 {"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	1378 {"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	1379 {"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	1380 {"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	1381 {"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	1382 // 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	1383 {"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	1384 {"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	1385 {"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	1386 {"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	1387 {"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	1388 {"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	1389 {"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	1390 {"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	1391 {"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	1392 };
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	1393
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	1394 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	1395 {"_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	1396 {"_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	1397 {"_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	1398 {"_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	1399 {"_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	1400 {"_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	1401 {"_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	1402 {"_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	1403 {"_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	1404 {"_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	1405 {"_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	1406 {"_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	1407 {"_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	1408 {"_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	1409 {"_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	1410 {"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	1411 {"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	1412 {"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	1413 {"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	1414 {"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	1415 {"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	1416 {"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	1417 {"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	1418 {"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	1419 {"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	1420 {"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	1421 {"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	1422 {"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	1423 {"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	1424 {"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	1425 {"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	1426 {"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	1427 {"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	1428 {"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	1429 {"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	1430 };
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	1431
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	1432 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	1433 {"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	1434 {"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	1435 {"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	1436 {"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	1437 {"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	1438 {"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	1439 {"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	1440 {"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	1441 {"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	1442 {"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	1443 {"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	1444 {"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	1445 {"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	1446 {"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	1447 {"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	1448 {"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	1449 {"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	1450 {"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	1451 {"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	1452 {"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	1453 };
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	1454
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	1455 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	1456 {"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	1457 {"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	1458 {"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	1459 {"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	1460 {"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	1461 };
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	1462 }

Mercurial > hg > monetdb-java

annotate src/main/java/org/monetdb/util/MDBvalidator.java @ 456:055dd9264d46