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

annotate src/main/java/org/monetdb/util/MDBvalidator.java @ 419:d9f9e077cd03

Finish implementation of foreign key referential integrity checks for user tables.

author	Martin van Dinther <martin.van.dinther@monetdbsolutions.com>
date	Thu, 28 Jan 2021 21:38:10 +0100 (2021-01-28)
parents	bf9f6b6ecf40
children	055dd9264d46

rev	line source
387 37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	1 /*
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	2 * This Source Code Form is subject to the terms of the Mozilla Public
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	3 * License, v. 2.0. If a copy of the MPL was not distributed with this
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	4 * file, You can obtain one at http://mozilla.org/MPL/2.0/.
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	5 *
406 bf9f6b6ecf40 Update Copyright year. Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: 394 diff changeset	6 * Copyright 1997 - July 2008 CWI, August 2008 - 2021 MonetDB B.V.
387 37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	7 */
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	8
391 f523727db392 Moved Java classes from packages starting with nl.cwi.monetdb.* to package org.monetdb.* Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: 390 diff changeset	9 package org.monetdb.util;
387 37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	10
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	11 import java.sql.Connection;
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	12 import java.sql.DatabaseMetaData;
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	13 import java.sql.ResultSet;
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	14 import java.sql.ResultSetMetaData;
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	15 import java.sql.Statement;
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	16 import java.sql.SQLException;
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	17 import java.sql.Types;
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	18
419 d9f9e077cd03 Finish implementation of foreign key referential integrity checks for user tables. Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: 406 diff changeset	19 import java.util.Iterator;
d9f9e077cd03 Finish implementation of foreign key referential integrity checks for user tables. Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: 406 diff changeset	20 import java.util.LinkedHashSet;
d9f9e077cd03 Finish implementation of foreign key referential integrity checks for user tables. Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: 406 diff changeset	21 import java.util.Set;
d9f9e077cd03 Finish implementation of foreign key referential integrity checks for user tables. Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: 406 diff changeset	22
387 37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	23 /**
394 2397e0f58949 Correct some warnings shown when running make doc Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: 391 diff changeset	24 * <pre>MonetDB Data Integrity Validator class (MDBvalidator) can
387 37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	25 * a) validate system tables data integrity in system schemas: sys and tmp
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	26 * this includes violations of:
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	27 * primary key uniqueness
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	28 * primary key column(s) not null
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	29 * unique constraint uniqueness
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	30 * foreign key referential integrity
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	31 * column not null
394 2397e0f58949 Correct some warnings shown when running make doc Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: 391 diff changeset	32 * column maximum length for char/varchar/clob/blob/json/url columns which have max length > 0
2397e0f58949 Correct some warnings shown when running make doc Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: 391 diff changeset	33 * b) validate user schema tables & columns data integrity based on available meta data from system tables & system views
387 37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	34 * primary key uniqueness
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	35 * TODO primary key column(s) not null
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	36 * unique constraint uniqueness
419 d9f9e077cd03 Finish implementation of foreign key referential integrity checks for user tables. Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: 406 diff changeset	37 * foreign key referential integrity
387 37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	38 * column not null
394 2397e0f58949 Correct some warnings shown when running make doc Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: 391 diff changeset	39 * column maximum length for char/varchar/clob/blob/json/url columns which have max length > 0
387 37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	40 *
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	41 * More possible validations for future
394 2397e0f58949 Correct some warnings shown when running make doc Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: 391 diff changeset	42 * col char/varchar/clob/blob/json/url minimum length (some columns may not be empty, so length >= 1)
387 37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	43 * col with sequence (serial/bigserial/autoincrement) in range (0/1/min_value .. max_value)
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	44 * col value is valid in domain (date/time/timestamp/json/inet/url/uuid/...)
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	45 * col in list checks (some columns may have only certain values which are not stored in a table or view (eg as fk))
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	46 SELECT * FROM sys.table_partitions WHERE "type" NOT IN (5,6,9,10); -- 5=By Column Range (1+4), 6=By Expression Range (2+4), 9=By Column Value (1+8), 10=By Expression Value (2+8), see sql_catalog.h #define PARTITION_*. Note table_partitions is introduced in Apr2019 "33"
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	47 * col conditional checks (column is not null when other column is (not) null)
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	48 -- i.e.: either column_id or expression in sys.table_partitions must be populated
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	49 SELECT "column_id", "expression", 'Missing either column_id or expression' AS violation, * FROM "sys"."table_partitions" WHERE "column_id" IS NULL AND "expression" IS NULL;
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	50 SELECT "column_id", "expression", 'column_id and expression may not both be populated. One of them must be NULL' AS violation, * FROM "sys"."table_partitions" WHERE "column_id" IS NOT NULL AND "expression" IS NOT NULL;
394 2397e0f58949 Correct some warnings shown when running make doc Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: 391 diff changeset	51 *</pre>
387 37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	52 * @author Martin van Dinther
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	53 * @version 0.1
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	54 */
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	55
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	56 public final class MDBvalidator {
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	57 private static final String prg = "MDBvalidator";
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	58 private Connection con;
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	59 private int majorversion;
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	60 private int minorversion;
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	61
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	62 private boolean verbose = false; // set it to true for tracing all generated SQL queries, see validateQuery(qry, ...)
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	63
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	64 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	65 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	66 }
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 /* 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	69 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	70 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	71 // 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	72
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	73 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	74 : "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	75 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	76 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	77 return;
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	78 }
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 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	81 try {
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	82 // 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	83 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	84 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	85 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	86
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	87 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	88
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	89 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	90 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	91 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	92
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	93 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	94 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	95
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	96 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	97 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	98 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	99 } 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	100 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	101 }
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 // 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	104 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	105 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	106 }
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 public static void validateSqlCatalogIntegrity(final Connection conn) {
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	111 final MDBvalidator mdbv = new MDBvalidator(conn);
387 37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	112 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	113 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	114 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	115 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	116 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	117
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	118 public static void validateSqlNetcdfTablesIntegrity(final Connection conn) {
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	119 final MDBvalidator mdbv = new MDBvalidator(conn);
387 37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	120 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	121 // 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	122 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	123 && 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	124 && 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	125 && 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	126 && 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	127 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	128 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	129 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	130
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	131 public static void validateSqlGeomTablesIntegrity(final Connection conn) {
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	132 final MDBvalidator mdbv = new MDBvalidator(conn);
387 37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	133 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	134 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	135 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	136 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	137 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	138
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	139 public static void validateSchemaIntegrity(final Connection conn, 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	140 final MDBvalidator mdbv = new MDBvalidator(conn);
387 37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	141 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	142 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	143 else
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	144 System.out.println("Schema: " + schema + " does not exist 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	145 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	146
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	147 public static void validateDBIntegrity(final Connection conn) {
419 d9f9e077cd03 Finish implementation of foreign key referential integrity checks for user tables. Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: 406 diff changeset	148 final MDBvalidator mdbv = new MDBvalidator(conn);
d9f9e077cd03 Finish implementation of foreign key referential integrity checks for user tables. Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: 406 diff changeset	149 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	150 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	151 return;
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	152
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	153 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	154 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	155 try {
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	156 // 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	157 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	158 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	159 // 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	160 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	161 String schema = rs.getString(1);
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	162 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	163 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	164 mdbv.validateSchema(schema, null, null, null, null, null, true);
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	165 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	166 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	167 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	168 } 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	169 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	170 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	171 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	172 if (!hasUserSchemas)
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	173 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	174 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	175
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	176
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	177 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	178 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	179 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	180 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	181 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	182 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	183 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	184 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	185 {
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	186 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	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 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	189 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	190 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	191 } else {
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	192 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	193 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	194
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	195 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	196 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	197 } else {
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	198 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	199 }
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 (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	202 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	203 } else {
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	204 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	205 }
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 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	208 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	209 } else {
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	210 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	211 }
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 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	214 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	215 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	216
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	217 /* 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	218 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	219 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	220 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	221 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	222 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	223 {
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	224 final int len = data.length;
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	225 System.out.println("Checking " + minimumWidth(len,6) + (group != null ? " " + group : "") + " tables/keys in schema " + schema + " for " + checkType + " violations.");
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	226
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	227 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	228 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	229 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	230 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	231 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	232 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	233 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	234 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	235 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	236 // 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	237 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	238 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	239 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	240 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	241 }
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(tbl)
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	243 .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	244 .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	245 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	246 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	247 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	248 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	249
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	250 /* 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	251 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	252 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	253 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	254 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	255 {
419 d9f9e077cd03 Finish implementation of foreign key referential integrity checks for user tables. Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: 406 diff changeset	256 final 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	257 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	258 return;
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	259
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	260 // 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	261 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	262 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	263 + " 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	264 .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	265 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	266 final int count = runCountQuery(qry);
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	267 System.out.println("Checking " + minimumWidth(count,6) + " keys in schema " + schema + " for " + checkType + " violations.");
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	268
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	269 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	270 try {
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	271 sb.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	272 // 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	273 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	274 .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	275 + " 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	276 .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	277 .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	278 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	279 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	280 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	281 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	282 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	283 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	284 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	285 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	286 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	287 // 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	288 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	289 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	290 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	291 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	292 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	293 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	294 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	295 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	296 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	297 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	298 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	299 else
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	300 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	301 } else {
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	302 // 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	303 // 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	304 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	305 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	306 .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	307 .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	308 .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	309 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	310 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	311 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	312 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	313 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	314 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	315 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	316 // 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	317 // 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	318 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	319 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	320 .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	321 .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	322 .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	323 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	324 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	325 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	326 } 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	327 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	328 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	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 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	331 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	332
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	333 /* 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	334 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	335 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	336 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	337 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	338 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	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 final int len = data.length;
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	341 System.out.println("Checking " + minimumWidth(len,6) + (group != null ? " " + group : "") + " foreign keys in schema " + schema + " for " + checkType + " violations.");
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	342
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	343 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	344 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	345 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	346 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	347 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	348 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	349 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	350 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	351 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	352 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	353 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	354 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	355 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	356 // 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	357 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	358 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	359 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	360 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	361 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	362 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	363 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	364 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	365 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	366 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	367 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	368 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	369
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	370 /* 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	371 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	372 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	373 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	374 {
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	375 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	376 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	377 // 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	378 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	379 } 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	380 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	381 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	382 }
387 37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	383 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	384 return;
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 // 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	387 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	388 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	389 + " 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	390 .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	391 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	392 final int count = runCountQuery(qry);
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	393 System.out.println("Checking " + minimumWidth(count,6) + " foreign keys in schema " + schema + " for " + checkType + " violations.");
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	394
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	395 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	396 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	397 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	398 // 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	399 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	400 "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	401 "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	402 // ", 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	403 " 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	404 " 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	405 " 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	406 " 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	407 " 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	408 " 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	409 " 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	410 " 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	411 " 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	412 " 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	413 .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	414 .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	415 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	416 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	417 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	418 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	419 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	420 // 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	421 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	422 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	423 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	424 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	425 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	426 // 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	427 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	428 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	429 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	430 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	431 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	432 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	433 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	434 // 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	435 // 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	436
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 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	438 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	439 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	440 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	441
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	442 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	443 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	444 // 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	445 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	446 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	447 }
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 // 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	449 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	450 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	451
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 // 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	453 // 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	454 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	455 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	456 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	457 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	458 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	459 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	460 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	461 }
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 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	463 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	464 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	465 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	466 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	467 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	468 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	469 }
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 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	471 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	472 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	473 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	474 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	475 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	476 }
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(") 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	478 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	479 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	480 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	481 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	482 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	483 }
d9f9e077cd03 Finish implementation of foreign key referential integrity checks for user tables. Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: 406 diff changeset	484 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	485 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	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 }
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 } 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	489 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	490 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	491 }
387 37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	492 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	493 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	494
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	495 /* 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	496 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	497 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	498 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	499 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	500 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	501 {
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	502 final int len = data.length;
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	503 System.out.println("Checking " + minimumWidth(len,6) + (group != null ? " " + group : "") + " columns in schema " + schema + " for " + checkType + " violations.");
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	504
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	505 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	506 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	507 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	508 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	509 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	510 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	511 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	512 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	513 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	514 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	515 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	516 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	517 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	518 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	519 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	520 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	521 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	522 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	523 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	524 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	525 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	526 } else {
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	527 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	528 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	529 // 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	530 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	531 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	532 .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	533 .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	534 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	535 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	536 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	537 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	538
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	539 /* 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	540 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	541 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	542 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	543 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	544 {
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	545 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	546 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	547 return;
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	548
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	549 // 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	550 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	551 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	552 + " 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	553 + " 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	554 .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	555 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	556 final int count = runCountQuery(qry);
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	557 System.out.println("Checking " + minimumWidth(count,6) + " columns in schema " + schema + " for " + checkType + " violations.");
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	558
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	559 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	560 try {
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	561 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	562 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	563 .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	564 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	565 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	566 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	567 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	568 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	569 // 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	570 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	571 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	572 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	573 // 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	574 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	575 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	576 .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	577 .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	578 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	579 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	580 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	581 } 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	582 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	583 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	584 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	585 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	586 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	587
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	588 /* 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	589 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	590 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	591 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	592 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	593 {
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	594 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	595 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	596 return;
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	597
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	598 // 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	599 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	600 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	601 + " 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	602 + " 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	603 + " 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	604 .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	605 .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	606 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	607 final int count = runCountQuery(qry);
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	608 System.out.println("Checking " + minimumWidth(count,6) + " columns in schema " + schema + " for " + checkType + " violations.");
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	609
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	610 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	611 try {
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	612 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	613 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	614 .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	615 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	616 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	617 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	618 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	619 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	620 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	621 // 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	622 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	623 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	624 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	625 max_len = rs.getLong(4);
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	626 // 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	627 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	628 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	629 .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	630 .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	631 .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	632 .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	633 .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	634 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	635 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	636 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	637 } 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	638 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	639 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	640 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	641 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	642 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	643
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	644
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	645 /* 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	646 * 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	647 * When rows are returned those are the ones that contain violations.
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	648 * Retrieve them and convert the results (currently first 16 only) into a (large) violation string
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	649 * 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	650 */
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	651 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	652 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	653 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	654 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	655 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	656 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	657 {
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	658 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	659 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	660 return;
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	661
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	662 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	663 try {
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	664 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	665 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	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 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	668 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	669 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	670 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	671 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	672 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	673 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	674 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	675 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	676 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	677 // 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	678 row++;
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	679 if (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	680 // 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	681 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	682 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	683 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	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 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	686 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	687 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	688 // 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	689 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	690 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	691 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	692 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	693 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	694 } else {
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	695 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	696 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	697 \|\| 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	698 \|\| 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	699 \|\| 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	700 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	701 } else {
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	702 sb.append(val);
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	703 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	704 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	705 }
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('\n');
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 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	709 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	710 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	711 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	712 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	713 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	714 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	715 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	716 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	717 } 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	718 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	719 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	720 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	721 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	722 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	723
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	724 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	725 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	726 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	727 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	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 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	730 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	731 try {
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	732 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	733 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	734 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	735 // 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	736 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	737 }
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 } 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	740 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	741 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	742 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	743 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	744 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	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 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	748 try {
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	749 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	750 } 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	751 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	752 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	753 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	754 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	755 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	756
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	757 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	758 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	759 // 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	760 try {
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	761 // 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	762 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	763 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	764 // 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	765 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	766 minorversion = dbmd.getDatabaseMinorVersion();
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	767 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	768 } 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	769 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	770 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	771 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	772 // 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	773 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	774 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	775 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	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 true;
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 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	781 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	782 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	783
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	784 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	785 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	786 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	787 } 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	788 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	789 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	790 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	791 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	792
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	793 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	794 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	795 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	796 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	797
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	798 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	799 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	800 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	801 try {
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	802 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	803 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	804 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	805 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	806 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	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 } 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	810 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	811 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	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 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	814 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	815 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	816
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	817 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	818 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	819 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	820 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	821
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	822 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	823 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	824 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	825 try {
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	826 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	827 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	828 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	829 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	830 && 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	831 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	832 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	833 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	834 } 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	835 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	836 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	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 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	839 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	840 }
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 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	843 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	844 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	845 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	846 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	847 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	848 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	849 {
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	850 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	851 sb.append(checkType).append(" violation(s) found in \"")
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	852 .append(schema).append("\".\"").append(table).append("\".\"").append(columns).append("\":\n")
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	853 .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	854 .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	855 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	856 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	857
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	858 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	859 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	860 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	861 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	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 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	864
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	865 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	866 // 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	867 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	868 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	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 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	871 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	872 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	873 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	874
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	875 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	876 final String valstr = Integer.toString(val);
387 37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	877 int spacesneeded = minWidth - valstr.length();
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	878 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	879 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	880 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	881 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	882 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	883 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	884 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	885 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	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 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	888
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	889
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	890 // ******* 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	891 // 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	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 // 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	894 // 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	895 // 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	896 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	897 {"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	898 {"_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	899 {"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	900 {"_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	901 {"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	902 {"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	903 {"systemfunctions", "function_id", null}, // has become a view in Apr2019 (11.33.3) and maybe removed in the future as is deprecated
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	904 {"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	905 {"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	906 {"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	907 {"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	908 {"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	909 {"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	910 {"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	911 {"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	912 {"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	913 {"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	914 {"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	915 {"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	916 {"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	917 {"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	918 {"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	919 {"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	920 {"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	921 {"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	922 {"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	923 {"statistics", "column_id", null},
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	924 // old {"tracelog", "event", null}, -- Error: Profiler not started. This table now (Jun2020) contains only: ticks, stmt
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	925 // {"storage", "schema, table, column", null}, // is a view on table producing function: storage().
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	926 {"\"storage\"()", "schema, table, column", null}, // the function "storage"() also lists the storage for system tables
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	927 {"storagemodelinput", "schema, table, column", null},
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	928
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	929 {"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	930
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	931 {"keywords", "keyword", "21"}, // introduced in Jul2015 release (11.21.5)
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	932 {"table_types", "table_type_id", "21"}, // introduced in Jul2015 release (11.21.5)
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	933
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	934 {"function_languages", "language_id", "27"}, // introduced in Jul2017 release (11.27.1)
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	935 {"function_types", "function_type_id", "27"}, // introduced in Jul2017 release (11.27.1)
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	936 {"index_types", "index_type_id", "27"}, // introduced in Jul2017 release (11.27.1)
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	937 {"key_types", "key_type_id", "27"}, // introduced in Jul2017 release (11.27.1)
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	938 {"privilege_codes", "privilege_code_id", "27"}, // introduced in Jul2017 release (11.27.1)
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	939
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	940 {"comments", "id", "29"}, // introduced in Mar2018 release (11.29.3)
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	941 {"ids", "id", "29"}, // introduced in Mar2018 release (11.29.3), it is a view
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	942 {"var_values", "var_name", "29"}, // var_values is introduced in Mar2018 release (11.29.3), it is a view
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	943
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	944 // new views introduced in Apr 2019 feature release (11.33.3)
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	945 // {"tablestorage", "schema, table", "33"}, // is a view on view storage
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	946 // {"schemastorage", "schema", "33"}, // is a view on view storage
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	947 // {"storagemodel", "schema, table, column", null}, // is a view on storagemodelinput
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	948 // {"tablestoragemodel", "schema, table", null}, // is a view on storagemodelinput
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	949
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	950 // new tables introduced in Apr 2019 feature release (11.33.3)
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	951 {"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	952 {"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	953 {"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	954
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	955 // 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	956 {"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	957 // old {"sessions", "\"user\", login, active", null}, // sessions has changed in Jun2020 (11.37.7), pkey was previously "user", login, active
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	958 {"sessions", "sessionid", "37"} // sessions has changed in Jun2020 (11.37.7), pkey is now called sessionid
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	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
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	961 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	962 {"_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	963 {"_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	964 {"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	965 {"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	966 {"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	967 {"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	968 };
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	969
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	970 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	971 {"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	972 {"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	973 {"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	974 {"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	975 {"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	976 };
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	977
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	978 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	979 {"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	980 };
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	981
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	982
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	983 // 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	984 // 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	985 // 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	986 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	987 {"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	988 {"_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	989 {"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	990 {"_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	991 {"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	992 {"_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	993 {"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	994 // 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	995 {"(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	996 {"(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	997 // 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	998 {"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	999 {"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	1000 {"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	1001 {"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	1002 {"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	1003 {"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	1004 {"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	1005 {"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	1006 {"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	1007 {"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	1008 {"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	1009
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	1010 // 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	1011 {"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	1012 {"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	1013 {"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	1014 {"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	1015 {"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	1016 {"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	1017 {"comments", "id", "29"},
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	1018 // new tables introduced in Apr 2019 feature release (11.33.3)
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	1019 {"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	1020 {"table_partitions WHERE \"expression\" IS NOT NULL", "table_id, \"expression\"", "33"}, // requires WHERE "expression" IS NOT NULL
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	1021 {"range_partitions", "table_id, partition_id, \"maximum\"", "33"}
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	1022 };
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	1023
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	1024 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	1025 {"_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	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, number", null},
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	1028 {"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	1029 {"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	1030 {"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	1031 {"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	1032 };
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	1033
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	1034 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	1035 {"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	1036 };
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	1037
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	1038 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	1039 {"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	1040 };
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	1041
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	1042
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	1043 // 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	1044 // 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	1045 // 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	1046 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	1047 {"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	1048 {"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	1049 {"_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	1050 {"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	1051 {"_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	1052 {"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	1053 {"_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	1054 {"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	1055 {"_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	1056 {"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	1057 {"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	1058 {"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	1059 {"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	1060 // system functions should refer only to functions in MonetDB system schemas
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	1061 {"functions WHERE system AND ", "schema_id", "id", "schemas WHERE system", null},
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	1062 {"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	1063 {"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	1064 {"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	1065 // {"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	1066 {"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	1067 {"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	1068 {"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	1069 {"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	1070 {"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	1071 {"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	1072 {"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	1073 // SELECT * FROM sys.keys WHERE action <> -1 AND action NOT IN (SELECT id FROM sys.?); -- TODO: find out which action values are valid and what they mean.
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	1074 {"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	1075 {"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	1076 {"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	1077 {"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	1078 {"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	1079 {"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	1080 {"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	1081 {"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	1082 {"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	1083 {"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	1084 {"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	1085 {"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	1086 {"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	1087 {"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	1088 {"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	1089 {"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	1090 {"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	1091 {"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	1092 {"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	1093 {"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	1094 {"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	1095 {"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	1096 {"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	1097 {"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	1098 {"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	1099 {"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	1100 {"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	1101 {"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	1102 {"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	1103 {"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	1104 {"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	1105 {"querylog_history", "pipe", "name", "optimizers", null},
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	1106 {"queue WHERE tag > cast(0 as oid) AND ", "tag", "tag", "queue", null},
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	1107 {"queue WHERE tag > cast(0 as oid) AND ", "tag", "cast(tag as oid)", "queue", null},
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	1108 {"queue", "tag", "cast(tag as oid)", "queue", null},
391 f523727db392 Moved Java classes from packages starting with nl.cwi.monetdb.* to package org.monetdb.* Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: 390 diff changeset	1109 // not a fk: {"queue", "\"username\"", "name", "users", null}, // as queue contains a historical list, the user may have been removed in the meantime, so not a real persistent fk
387 37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	1110 {"sessions", "\"username\"", "name", "users", null},
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	1111 {"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	1112 {"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	1113 {"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	1114 {"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	1115 {"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	1116 {"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	1117 {"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	1118 {"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	1119 {"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	1120 {"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	1121 {"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	1122 {"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	1123 {"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	1124 {"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	1125 {"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	1126 {"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	1127 {"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	1128 {"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	1129 {"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	1130 {"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	1131 {"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	1132 {"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	1133 {"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	1134 {"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	1135 {"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	1136 // 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	1137 {"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	1138 {"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	1139 {"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	1140 {"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	1141 {"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	1142 {"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	1143 {"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	1144 {"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	1145 {"value_partitions", "table_id", "id", "_tables", "33"},
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	1146 {"value_partitions", "partition_id", "id", "table_partitions", "33"}
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	1147 };
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	1148
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	1149 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	1150 {"_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	1151 {"_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	1152 {"_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	1153 {"_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	1154 {"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	1155 {"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	1156 {"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	1157 {"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	1158 // SELECT * FROM tmp.keys WHERE action <> -1 AND action NOT IN (SELECT id FROM tmp.?); -- TODO: find out which action values are valid and what they mean.
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	1159 {"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	1160 {"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	1161 {"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	1162 {"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	1163 {"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	1164 };
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	1165
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	1166 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	1167 {"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	1168 {"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	1169 {"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	1170 {"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	1171 {"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	1172 {"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	1173 {"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	1174 {"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	1175 };
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	1176
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	1177 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	1178 {"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	1179 };
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	1180
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	1181
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	1182 // 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	1183 // 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	1184 // 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	1185 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	1186 {"_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	1187 {"_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	1188 {"_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	1189 {"_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	1190 {"_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	1191 {"_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	1192 {"_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	1193 {"_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	1194 {"_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	1195 {"_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	1196 {"_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	1197 {"_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	1198 {"_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	1199 {"_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	1200 {"_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	1201 {"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	1202 {"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	1203 {"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	1204 {"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	1205 {"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	1206 {"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	1207 {"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	1208 {"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	1209 {"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	1210 {"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	1211 {"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	1212 {"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	1213 {"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	1214 {"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	1215 {"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	1216 {"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	1217 {"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	1218 {"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	1219 {"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	1220 {"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	1221 {"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	1222 {"function_types", "function_type_keyword", "27"},
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	1223 {"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	1224 {"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	1225 {"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	1226 {"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	1227 {"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	1228 {"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	1229 {"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	1230 {"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	1231 {"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	1232 {"functions", "schema_id", null},
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	1233 {"functions", "system", null},
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	1234 {"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	1235 {"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	1236 {"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	1237 {"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	1238 {"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	1239 {"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	1240 {"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	1241 {"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	1242 {"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	1243 {"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	1244 {"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	1245 {"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	1246 {"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	1247 {"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	1248 {"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	1249 {"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	1250 {"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	1251 {"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	1252 {"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	1253 {"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	1254 {"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	1255 {"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	1256 {"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	1257 {"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	1258 {"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	1259 {"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	1260 {"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	1261 {"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	1262 {"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	1263 {"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	1264 {"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	1265 {"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	1266 {"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	1267 {"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	1268 {"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	1269 {"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	1270 {"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	1271 {"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	1272 {"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	1273 {"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	1274 {"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	1275 {"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	1276 {"statistics", "column_id", null},
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	1277 {"statistics", "type", null},
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	1278 {"statistics", "width", null},
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	1279 {"statistics", "stamp", null},
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	1280 {"statistics", "\"sample\"", null},
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	1281 {"statistics", "count", null},
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	1282 {"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	1283 {"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	1284 {"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	1285 {"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	1286 // 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	1287 {"\"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	1288 {"\"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	1289 {"\"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	1290 {"\"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	1291 {"\"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	1292 {"\"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	1293 {"\"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	1294 {"\"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	1295 {"\"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	1296 {"\"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	1297 {"\"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	1298 {"\"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	1299 {"\"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	1300 {"\"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	1301 {"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	1302 {"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	1303 {"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	1304 {"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	1305 {"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	1306 {"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	1307 {"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	1308 {"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	1309 {"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	1310 {"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	1311 {"storagemodelinput", "\"unique\"", null},
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	1312 {"storagemodelinput", "isacolumn", null},
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	1313 {"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	1314 {"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	1315 {"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	1316 {"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	1317 {"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	1318 {"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	1319 {"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	1320 {"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	1321 {"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	1322 {"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	1323 {"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	1324 {"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	1325 {"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	1326 {"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	1327 {"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	1328 {"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	1329 {"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	1330 {"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	1331 {"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	1332 {"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	1333 {"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	1334 {"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	1335 {"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	1336 {"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	1337 {"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	1338 {"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	1339 {"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	1340 {"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	1341 {"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	1342 {"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	1343 {"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	1344 {"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	1345 {"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	1346 {"var_values", "value", "29"},
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	1347 // new tables introduced in Apr 2019 feature release (11.33.3)
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	1348 {"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	1349 {"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	1350 {"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	1351 {"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	1352 {"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	1353 {"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	1354 {"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	1355 {"value_partitions", "partition_id", "33"},
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	1356 {"value_partitions", "value", "33"} // Can this be null when WITH NULL VALUES is specified?
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	1357 };
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	1358
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	1359 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	1360 {"_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	1361 {"_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	1362 {"_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	1363 {"_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	1364 {"_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	1365 {"_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	1366 {"_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	1367 {"_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	1368 {"_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	1369 {"_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	1370 {"_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	1371 {"_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	1372 {"_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	1373 {"_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	1374 {"_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	1375 {"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	1376 {"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	1377 {"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	1378 {"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	1379 {"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	1380 {"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	1381 {"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	1382 {"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	1383 {"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	1384 {"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	1385 {"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	1386 {"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	1387 {"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	1388 {"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	1389 {"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	1390 {"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	1391 {"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	1392 {"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	1393 {"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	1394 {"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	1395 };
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	1396
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	1397 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	1398 {"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	1399 {"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	1400 {"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	1401 {"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	1402 {"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	1403 {"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	1404 {"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	1405 {"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	1406 {"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	1407 {"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	1408 {"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	1409 {"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	1410 {"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	1411 {"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	1412 {"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	1413 {"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	1414 {"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	1415 {"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	1416 {"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	1417 {"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	1418 };
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	1419
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	1420 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	1421 {"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	1422 {"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	1423 {"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	1424 {"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	1425 {"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	1426 };
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	1427 }

Mercurial > hg > monetdb-java

annotate src/main/java/org/monetdb/util/MDBvalidator.java @ 419:d9f9e077cd03