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

annotate src/main/java/org/monetdb/util/MDBvalidator.java @ 610:6aa38e8c0f2d

Updated Copyright year.

author	Martin van Dinther <martin.van.dinther@monetdbsolutions.com>
date	Wed, 05 Jan 2022 14:29:56 +0100 (2022-01-05)
parents	6f68317d4d0f
children	844139b33cdd

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

Mercurial > hg > monetdb-java

annotate src/main/java/org/monetdb/util/MDBvalidator.java @ 610:6aa38e8c0f2d