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

annotate src/main/java/org/monetdb/util/MDBvalidator.java @ 406:bf9f6b6ecf40

Update Copyright year.

author	Martin van Dinther <martin.van.dinther@monetdbsolutions.com>
date	Wed, 06 Jan 2021 22:09:32 +0100 (2021-01-06)
parents	2397e0f58949
children	d9f9e077cd03

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
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	19 /**
394 2397e0f58949 Correct some warnings shown when running make doc Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: 391 diff changeset	20 * <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	21 * 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	22 * 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	23 * 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	24 * 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	25 * 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	26 * 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	27 * 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	28 * 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	29 * 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	30 * 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	31 * 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	32 * 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	33 * TODO 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	34 * 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	35 * 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	36 *
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	37 * 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	38 * 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	39 * 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	40 * 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	41 * 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	42 SELECT * FROM sys.table_partitions WHERE "type" NOT IN (5,6,9,10); -- 5=By Column Range (1+4), 6=By Expression Range (2+4), 9=By Column Value (1+8), 10=By Expression Value (2+8), see sql_catalog.h #define PARTITION_*. Note table_partitions is introduced in Apr2019 "33"
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 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	44 -- 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	45 SELECT "column_id", "expression", 'Missing either column_id or expression' AS violation, * FROM "sys"."table_partitions" WHERE "column_id" IS NULL AND "expression" IS NULL;
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 "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	47 *</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	48 * @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	49 * @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	50 */
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	51
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	52 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	53 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	54 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	55 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	56 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	57
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 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	59
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	60 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	61 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	62 }
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 /* 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	65 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	66 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	67 // 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	68
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	69 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	70 : "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	71 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	72 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	73 return;
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	74 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	75
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	76 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	77 try {
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	78 // 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	79 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	80 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	81 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	82
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	83 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	84
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	85 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	86 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	87 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	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 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	90 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	91
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	92 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	93 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	94 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	95 } 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	96 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	97 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	98
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	99 // 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	100 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	101 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	102 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	103 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	104 */
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	105
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	106 public static void validateSqlCatalogIntegrity(final 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	107 MDBvalidator mdbv = new MDBvalidator(conn);
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	108 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	109 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	110 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	111 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	112 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	113
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	114 public static void validateSqlNetcdfTablesIntegrity(final 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	115 MDBvalidator mdbv = new MDBvalidator(conn);
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	116 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	117 // 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	118 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	119 && 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	120 && 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	121 && 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	122 && 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	123 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	124 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	125 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	126
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	127 public static void validateSqlGeomTablesIntegrity(final 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	128 MDBvalidator mdbv = new MDBvalidator(conn);
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	129 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	130 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	131 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	132 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	133 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	134
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	135 public static void validateSchemaIntegrity(final Connection conn, 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	136 MDBvalidator mdbv = new MDBvalidator(conn);
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	137 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	138 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	139 else
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	140 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	141 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	142
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	143 public static void validateDBIntegrity(final 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	144 MDBvalidator mdbv = new MDBvalidator(conn);
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	145 Statement stmt = mdbv.createStatement("validateDBIntegrity()");
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	146 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	147 return;
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	148
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	149 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	150 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	151 try {
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	152 // 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	153 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	154 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	155 // 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	156 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	157 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	158 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	159 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	160 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	161 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	162 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	163 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	164 } 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	165 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	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 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	168 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	169 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	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
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	172
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	173 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	174 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	175 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	176 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	177 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	178 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	179 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	180 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	181 {
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	182 boolean is_system_schema = ("sys".equals(schema) \|\| "tmp".equals(schema));
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	183
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	184 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	185 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	186 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	187 } else {
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	188 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	189 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	190
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	191 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	192 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	193 } else {
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	194 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	195 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	196
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	197 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	198 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	199 } else {
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	200 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	201 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	202
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	203 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	204 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	205 } else {
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	206 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	207 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	208
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	209 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	210 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	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 /* 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	214 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	215 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	216 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	217 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	218 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	219 {
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	220 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	221 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	222
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	223 StringBuilder sb = new StringBuilder(256); // reusable buffer to compose SQL 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	224 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	225 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	226 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	227 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	228 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	229 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	230 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	231 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	232 // 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	233 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	234 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	235 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	236 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	237 }
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(tbl)
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	239 .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	240 .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	241 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	242 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	243 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	244 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	245
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	246 /* 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	247 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	248 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	249 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	250 final String checkType)
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	251 {
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	252 Statement stmt = createStatement("validateUniqueness()");
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	253 if (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	254 return;
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	255
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	256 // fetch the primary or unique key info 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	257 StringBuilder sb = new StringBuilder(400);
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	258 sb.append(" FROM sys.keys k JOIN sys.tables t ON k.table_id = t.id JOIN sys.schemas s ON t.schema_id = s.id"
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	259 + " 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	260 .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	261 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	262 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	263 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	264
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	265 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	266 try {
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	267 sb.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	268 // 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	269 sb.append("SELECT s.name as sch_nm, t.name as tbl_nm, k.name as key_nm, o.name as col_nm, o.nr")
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	270 .append(" FROM sys.keys k JOIN sys.objects o ON k.id = o.id JOIN sys.tables t ON k.table_id = t.id JOIN sys.schemas s ON t.schema_id = s.id"
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	271 + " 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	272 .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	273 .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	274 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	275 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	276 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	277 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	278 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	279 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	280 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	281 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	282 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	283 // 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	284 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	285 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	286 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	287 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	288 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	289 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	290 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	291 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	292 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	293 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	294 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	295 else
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	296 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	297 } else {
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	298 // 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	299 // 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	300 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	301 sb.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	302 .append(" FROM \"").append(sch).append("\".\"").append(prv_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	303 .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	304 .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	305 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	306 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	307 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	308 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	309 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	310 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	311 if (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	312 // 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	313 // 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	314 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	315 sb.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	316 .append(" FROM \"").append(sch).append("\".\"").append(prv_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	317 .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	318 .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	319 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	320 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	321 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	322 } 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	323 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	324 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	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 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	327 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	328
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	329 /* 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	330 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	331 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	332 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	333 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	334 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	335 {
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 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	337 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	338
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	339 StringBuilder sb = new StringBuilder(400); // reusable buffer to compose SQL 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	340 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	341 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	342 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	343 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	344 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	345 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	346 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	347 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	348 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	349 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	350 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	351 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	352 // 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	353 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	354 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	355 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	356 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	357 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	358 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	359 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	360 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	361 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	362 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	363 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	364 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	365
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	366 /* 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	367 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	368 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	369 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	370 {
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	371 Statement stmt = createStatement("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 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	373 return;
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	374
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	375 // fetch the foreign key info 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	376 StringBuilder sb = new StringBuilder(400);
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	377 sb.append(" 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	378 + " 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	379 .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	380 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	381 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	382 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	383
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	384 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	385 // TODO: finish FK validation implementation
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	386
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	387 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	388 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	389
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	390 /* 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	391 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	392 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	393 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	394 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	395 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	396 {
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	397 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	398 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	399
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	400 StringBuilder sb = new StringBuilder(256); // reusable buffer to compose SQL 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	401 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	402 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	403 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	404 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	405 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	406 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	407 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	408 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	409 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	410 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	411 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	412 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	413 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	414 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	415 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	416 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	417 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	418 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	419 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	420 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	421 } else {
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	422 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	423 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	424 // 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	425 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	426 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	427 .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	428 .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	429 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	430 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	431 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	432 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	433
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	434 /* 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	435 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	436 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	437 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	438 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	439 {
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	440 Statement stmt = createStatement("validateNotNull()");
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	441 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	442 return;
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	443
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	444 // fetch the NOT NULL info from the MonetDB system tables as those are leading for user tables (but not 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	445 StringBuilder sb = new StringBuilder(400);
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	446 sb.append(" from sys.columns c join sys.tables t on c.table_id = t.id join sys.schemas s on t.schema_id = s.id"
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	447 + " where t.type in (0, 10, 1, 11) and c.\"null\" = false" // t.type 0 = TABLE, 10 = SYSTEM TABLE, 1 = VIEW, 11 = SYSTEM VIEW
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	448 + " 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	449 .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	450 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	451 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	452 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	453
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	454 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	455 try {
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	456 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	457 sb.append("SELECT s.name as sch_nm, t.name as tbl_nm, c.name as col_nm") // , t.type, t.system, c.type, c.type_digits
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	458 .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	459 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	460 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	461 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	462 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	463 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	464 // 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	465 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	466 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	467 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	468 // 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	469 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	470 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	471 .append(" FROM \"").append(sch).append("\".\"").append(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	472 .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	473 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	474 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	475 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	476 } 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	477 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	478 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	479 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	480 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	481 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	482
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	483 /* 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	484 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	485 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	486 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	487 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	488 {
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	489 Statement stmt = createStatement("validateMaxCharStrLength()");
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	490 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	491 return;
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	492
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	493 // fetch the max char str len info from the MonetDB system tables as those are leading
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	494 StringBuilder sb = new StringBuilder(400);
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	495 sb.append(" from sys.columns c join sys.tables t on c.table_id = t.id join sys.schemas s on t.schema_id = s.id"
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	496 + " 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	497 + " 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	498 + " 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	499 .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	500 .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	501 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	502 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	503 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	504
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	505 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	506 try {
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	507 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	508 sb.append("SELECT s.name as sch_nm, t.name as tbl_nm, c.name as col_nm, c.type_digits") // , t.type, t.system, c.type
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	509 .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	510 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	511 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	512 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	513 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	514 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	515 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	516 // 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	517 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	518 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	519 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	520 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	521 // 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	522 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	523 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	524 .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	525 .append("length(\"").append(col).append("\") as 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	526 .append("\"").append(col).append("\" as data_value")
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	527 .append(" FROM \"").append(sch).append("\".\"").append(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	528 .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	529 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	530 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	531 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	532 } 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	533 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	534 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	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 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	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
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	540 /* 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	541 * 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	542 * 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	543 * 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	544 * 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	545 */
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	546 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	547 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	548 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	549 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	550 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	551 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	552 {
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	553 Statement stmt = createStatement("validateQuery()");
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	554 if (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	555 return;
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	556
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	557 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	558 try {
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	559 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	560 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	561 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	562 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	563 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	564 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	565 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	566 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	567 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	568 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	569 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	570 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	571 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	572 // 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	573 row++;
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	574 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	575 // 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	576 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	577 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	578 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	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 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	581 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	582 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	583 // 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	584 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	585 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	586 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	587 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	588 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	589 } else {
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	590 tp = rsmd.getColumnType(i); // this method is very fast, so no need to cache it outside the loop
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	591 if (tp == Types.VARCHAR \|\| tp == Types.CHAR \|\| tp == Types.CLOB \|\| tp == Types.BLOB) {
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	592 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	593 } else {
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	594 sb.append(val);
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	595 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	596 }
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 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	599 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	600 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	601 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	602 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	603 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	604 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	605 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	606 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	607 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	608 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	609 } 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	610 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	611 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	612 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	613 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	614 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	615
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	616 private int runCountQuery(final String 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	617 Statement stmt = createStatement("runCountQuery()");
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	618 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	619 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	620
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	621 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	622 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	623 try {
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	624 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	625 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	626 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	627 // 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	628 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	629 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	630 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	631 } 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	632 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	633 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	634 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	635 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	636 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	637 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	638
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	639 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	640 try {
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	641 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	642 } 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	643 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	644 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	645 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	646 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	647 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	648
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	649 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	650 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	651 // 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	652 try {
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	653 // retrieve server version numbers (major and minor). These are needed to filter out version specific validations
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	654 DatabaseMetaData dbmd = con.getMetaData();
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	655 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	656 // 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	657 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	658 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	659 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	660 } catch (SQLException e) {
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	661 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	662 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	663 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	664 // 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	665 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	666 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	667 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	668 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	669 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	670 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	671
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	672 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	673 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	674 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	675
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	676 try {
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	677 int v = Integer.parseInt(version);
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	678 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	679 } 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	680 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	681 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	682 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	683 }
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 private boolean checkSchemaExists(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	686 Statement stmt = createStatement("checkSchemaExists()");
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 (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	688 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	689
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	690 String sql = "SELECT name FROM sys.schemas WHERE name = '" + schema + "';";
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	691 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	692 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	693 try {
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	694 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	695 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	696 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	697 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	698 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	699 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	700 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	701 } 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	702 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	703 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	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 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	706 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	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 private boolean checkTableExists(final String schema, 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	710 Statement stmt = createStatement("checkTableExists()");
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	711 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	712 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	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 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 + "';";
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	715 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	716 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	717 try {
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	718 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	719 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	720 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	721 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	722 && 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	723 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	724 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	725 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	726 } 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	727 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	728 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	729 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	730 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	731 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	732 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	733
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	734 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	735 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	736 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	737 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	738 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	739 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	740 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	741 {
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	742 StringBuilder sb = new StringBuilder(2048);
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	743 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	744 .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	745 .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	746 .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	747 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	748 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	749
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	750 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	751 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	752 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	753 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	754 }
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 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	758 // 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	759 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	760 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	761 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	762 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	763 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	764 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	765 }
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	766
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	767 private static String minimumWidth(int val, int minWidth) {
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	768 String valstr = Integer.toString(val);
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	769 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	770 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	771 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	772 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	773 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	774 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	775 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	776 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	777 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	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
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	781
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	782 // ******* 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	783 // 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	784
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	785 // 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	786 // 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	787 // 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	788 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	789 {"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	790 {"_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	791 {"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	792 {"_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	793 {"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	794 {"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	795 {"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	796 {"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	797 {"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	798 {"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	799 {"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	800 {"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	801 {"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	802 {"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	803 {"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	804 {"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	805 {"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	806 {"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	807 {"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	808 {"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	809 {"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	810 {"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	811 {"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	812 {"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	813 {"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	814 {"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	815 {"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	816 // 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	817 // {"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	818 {"\"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	819 {"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	820
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	821 {"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	822
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	823 {"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	824 {"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	825
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	826 {"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	827 {"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	828 {"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	829 {"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	830 {"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	831
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	832 {"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	833 {"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	834 {"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	835
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	836 // 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	837 // {"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	838 // {"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	839 // {"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	840 // {"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	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 // 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	843 {"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	844 {"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	845 {"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	846
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	847 // 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	848 {"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	849 // 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	850 {"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	851 };
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	852
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	853 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	854 {"_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	855 {"_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	856 {"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	857 {"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	858 {"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	859 {"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	860 };
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	861
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	862 private 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	863 {"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	864 {"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	865 {"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	866 {"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	867 {"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	868 };
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	869
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	870 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	871 {"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	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 // 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	876 // 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	877 // 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	878 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	879 {"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	880 {"_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	881 {"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	882 {"_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	883 {"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	884 {"_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	885 {"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	886 // The id values from sys.schemas, sys._tables, sys._columns and sys.functions combined must be exclusive (see FK from sys.privileges.obj_id)
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	887 {"(SELECT id FROM sys.schemas UNION ALL SELECT id FROM sys._tables UNION ALL SELECT id FROM sys._columns UNION ALL SELECT id FROM sys.functions) as T", "T.id", null},
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	888 {"(SELECT id FROM sys.schemas UNION ALL SELECT id FROM sys.tables UNION ALL SELECT id FROM sys.columns UNION ALL SELECT id FROM sys.functions) as T", "T.id", null},
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	889 // 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	890 {"functions f join sys.args a on f.id=a.func_id", "schema_id, f.name, func, mod, language, f.type, side_effect, varres, vararg, a.id", null},
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	891 {"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	892 {"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	893 {"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	894 {"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	895 {"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	896 {"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	897 {"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	898 {"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	899 {"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	900 {"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	901
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	902 // 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	903 {"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	904 {"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	905 {"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	906 {"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	907 {"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	908 {"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	909 {"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	910 // 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	911 {"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	912 {"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	913 {"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	914 };
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	915
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	916 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	917 {"_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	918 {"_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	919 {"_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	920 {"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	921 {"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	922 {"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	923 {"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	924 };
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	925
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	926 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	927 {"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	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
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	930 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	931 {"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	932 };
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
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	935 // 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	936 // 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	937 // 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	938 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	939 {"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	940 {"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	941 {"_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	942 {"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	943 {"_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	944 {"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	945 {"_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	946 {"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	947 {"_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	948 {"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	949 {"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	950 {"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	951 {"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	952 // 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	953 {"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	954 {"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	955 {"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	956 {"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	957 // {"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	958 {"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	959 {"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	960 {"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	961 {"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	962 {"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	963 {"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	964 {"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	965 // SELECT * FROM sys.keys WHERE action <> -1 AND action NOT IN (SELECT id FROM sys.?); -- TODO: find out which action values are valid and what they mean.
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", "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	967 {"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	968 {"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	969 {"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	970 {"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	971 {"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	972 {"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	973 {"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	974 {"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	975 {"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	976 {"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	977 {"dependencies", "id, depend_id, depend_type", "v.id, v.used_by_id, v.depend_type", "dependencies_vw v", "29"}, // dependencies_vw is introduced in Mar2018 release (11.29.3), it is a view
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	978 {"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	979 {"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	980 {"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	981 {"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	982 {"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	983 {"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	984 {"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	985 {"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	986 {"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	987 {"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	988 {"privileges", "obj_id", "id", "(SELECT id FROM sys.schemas UNION ALL SELECT id FROM sys._tables UNION ALL SELECT id FROM sys._columns UNION ALL SELECT id FROM sys.functions) as t", null},
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	989 {"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	990 {"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	991 {"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	992 {"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	993 {"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	994 {"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	995 {"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	996 {"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	997 {"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	998 {"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	999 {"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	1000 {"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	1001 // not a fk: {"queue", "\"username\"", "name", "users", null}, // as queue contains a historical list, the user may have been removed in the meantime, so not a real persistent fk
387 37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	1002 {"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	1003 {"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	1004 {"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	1005 {"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	1006 {"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	1007 {"storage()", "schema, table", "sname, tname", "(SELECT sch.name as sname, tbl.name as tname FROM sys.schemas AS sch JOIN sys.tables AS tbl ON sch.id = tbl.schema_id) as t", null},
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	1008 {"storage()", "column", "name", "(SELECT name FROM sys._columns UNION ALL SELECT name FROM tmp._columns UNION ALL SELECT name FROM sys.keys UNION ALL SELECT name FROM tmp.keys UNION ALL SELECT name FROM sys.idxs UNION ALL SELECT name FROM tmp.idxs) as c", null},
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	1009 {"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	1010 {"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	1011 {"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	1012 {"storage", "schema, table", "sname, tname", "(SELECT sch.name as sname, tbl.name as tname FROM sys.schemas AS sch JOIN sys.tables AS tbl ON sch.id = tbl.schema_id) as t", null},
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	1013 {"storage", "column", "name", "(SELECT name FROM sys._columns UNION ALL SELECT name FROM tmp._columns UNION ALL SELECT name FROM sys.keys UNION ALL SELECT name FROM tmp.keys UNION ALL SELECT name FROM sys.idxs UNION ALL SELECT name FROM tmp.idxs) as c", null},
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	1014 {"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	1015 {"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	1016 {"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	1017 {"storagemodel", "schema, table", "sname, tname", "(SELECT sch.name as sname, tbl.name as tname FROM sys.schemas AS sch JOIN sys.tables AS tbl ON sch.id = tbl.schema_id) as t", null},
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	1018 {"storagemodel", "column", "name", "(SELECT name FROM sys._columns UNION ALL SELECT name FROM tmp._columns UNION ALL SELECT name FROM sys.keys UNION ALL SELECT name FROM tmp.keys UNION ALL SELECT name FROM sys.idxs UNION ALL SELECT name FROM tmp.idxs) as c", null},
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	1019 {"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	1020 {"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	1021 {"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	1022 {"storagemodelinput", "schema, table", "sname, tname", "(SELECT sch.name as sname, tbl.name as tname FROM sys.schemas AS sch JOIN sys.tables AS tbl ON sch.id = tbl.schema_id) as t", null},
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	1023 {"storagemodelinput", "column", "name", "(SELECT name FROM sys._columns UNION ALL SELECT name FROM tmp._columns UNION ALL SELECT name FROM sys.keys UNION ALL SELECT name FROM tmp.keys UNION ALL SELECT name FROM sys.idxs UNION ALL SELECT name FROM tmp.idxs) as c", null},
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	1024 {"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	1025 {"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	1026 {"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	1027 {"tablestoragemodel", "schema, table", "sname, tname", "(SELECT sch.name as sname, tbl.name as tname FROM sys.schemas AS sch JOIN sys.tables AS tbl ON sch.id = tbl.schema_id) as t", null},
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	1028 // 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	1029 {"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	1030 {"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	1031 {"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	1032 {"tablestorage", "schema, table", "sname, tname", "(SELECT sch.name as sname, tbl.name as tname FROM sys.schemas AS sch JOIN sys.tables AS tbl ON sch.id = tbl.schema_id) as t", "33"},
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	1033 {"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	1034 {"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	1035 {"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	1036 {"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	1037 {"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	1038 {"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	1039 };
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 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	1042 {"_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	1043 {"_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	1044 {"_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	1045 {"_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	1046 {"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	1047 {"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	1048 {"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	1049 {"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	1050 // SELECT * FROM tmp.keys WHERE action <> -1 AND action NOT IN (SELECT id FROM tmp.?); -- TODO: find out which action values are valid and what they mean.
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	1051 {"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	1052 {"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	1053 {"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	1054 {"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	1055 {"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	1056 };
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	1057
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	1058 private static final String[][] 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	1059 {"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	1060 {"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	1061 {"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	1062 {"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	1063 {"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	1064 {"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	1065 {"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	1066 {"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	1067 };
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	1068
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	1069 private static final String[][] 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	1070 {"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	1071 };
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	1072
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	1073
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	1074 // 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	1075 // 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	1076 // 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	1077 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	1078 {"_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	1079 {"_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	1080 {"_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	1081 {"_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	1082 {"_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	1083 {"_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	1084 {"_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	1085 {"_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	1086 {"_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	1087 {"_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	1088 {"_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	1089 {"_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	1090 {"_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	1091 {"_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	1092 {"_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	1093 {"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	1094 {"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	1095 {"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	1096 {"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	1097 {"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	1098 {"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	1099 {"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	1100 {"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	1101 {"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	1102 {"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	1103 {"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	1104 {"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	1105 {"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	1106 {"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	1107 {"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	1108 {"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	1109 {"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	1110 {"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	1111 {"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	1112 {"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	1113 {"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	1114 {"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	1115 {"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	1116 {"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	1117 {"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	1118 {"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	1119 {"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	1120 {"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	1121 {"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	1122 {"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	1123 {"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	1124 {"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	1125 {"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	1126 {"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	1127 {"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	1128 {"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	1129 {"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	1130 {"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	1131 {"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	1132 {"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	1133 {"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	1134 {"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	1135 {"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	1136 {"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	1137 {"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	1138 {"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	1139 {"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	1140 {"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	1141 {"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	1142 {"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	1143 {"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	1144 {"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	1145 {"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	1146 {"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	1147 {"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	1148 {"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	1149 {"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	1150 {"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	1151 {"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	1152 {"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	1153 {"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	1154 {"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	1155 {"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	1156 {"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	1157 {"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	1158 {"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	1159 {"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	1160 {"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	1161 {"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	1162 {"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	1163 {"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	1164 {"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	1165 {"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	1166 {"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	1167 {"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	1168 {"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	1169 {"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	1170 {"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	1171 {"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	1172 {"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	1173 {"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	1174 {"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	1175 {"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	1176 {"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	1177 {"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	1178 // 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	1179 {"\"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	1180 {"\"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	1181 {"\"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	1182 {"\"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	1183 {"\"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	1184 {"\"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	1185 {"\"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	1186 {"\"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	1187 {"\"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	1188 {"\"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	1189 {"\"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	1190 {"\"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	1191 {"\"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	1192 {"\"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	1193 {"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	1194 {"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	1195 {"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	1196 {"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	1197 {"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	1198 {"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	1199 {"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	1200 {"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	1201 {"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	1202 {"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	1203 {"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	1204 {"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	1205 {"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	1206 {"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	1207 {"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	1208 {"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	1209 {"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	1210 {"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	1211 {"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	1212 {"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	1213 {"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	1214 {"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	1215 {"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	1216 {"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	1217 {"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	1218 {"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	1219 {"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	1220 {"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	1221 {"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	1222 {"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	1223 {"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	1224 {"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	1225 {"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	1226 {"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	1227 {"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	1228 {"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	1229 {"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	1230 {"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	1231 {"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	1232 {"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	1233 {"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	1234 {"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	1235 {"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	1236 {"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	1237 {"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	1238 {"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	1239 // 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	1240 {"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	1241 {"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	1242 {"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	1243 {"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	1244 {"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	1245 {"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	1246 {"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	1247 {"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	1248 {"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	1249 };
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	1250
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	1251 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	1252 {"_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	1253 {"_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	1254 {"_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	1255 {"_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	1256 {"_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	1257 {"_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	1258 {"_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	1259 {"_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	1260 {"_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	1261 {"_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	1262 {"_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	1263 {"_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	1264 {"_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	1265 {"_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	1266 {"_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	1267 {"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	1268 {"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	1269 {"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	1270 {"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	1271 {"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	1272 {"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	1273 {"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	1274 {"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	1275 {"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	1276 {"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	1277 {"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	1278 {"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	1279 {"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	1280 {"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	1281 {"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	1282 {"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	1283 {"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	1284 {"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	1285 {"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	1286 {"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	1287 };
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	1288
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	1289 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	1290 {"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	1291 {"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	1292 {"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	1293 {"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	1294 {"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	1295 {"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	1296 {"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	1297 {"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	1298 {"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	1299 {"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	1300 {"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	1301 {"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	1302 {"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	1303 {"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	1304 {"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	1305 {"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	1306 {"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	1307 {"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	1308 {"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	1309 {"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	1310 };
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	1311
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	1312 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	1313 {"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	1314 {"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	1315 {"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	1316 {"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	1317 {"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	1318 };
37bf37e26324 Put MDBvalidator class into its own file in the util package Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: diff changeset	1319 }

Mercurial > hg > monetdb-java

annotate src/main/java/org/monetdb/util/MDBvalidator.java @ 406:bf9f6b6ecf40