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

annotate src/main/java/org/monetdb/util/MDBvalidator.java @ 932:f16966084980

In JdbcClient when running the \vsci or \vdbi or \vsi commands, we now suppress "42000 SELECT: insufficient privileges for ..." and "42000 SELECT: access denied for ..." error messages when the connected user does not have 'monetdb' or 'sysadmin' privileges, needed for some validations.

author	Martin van Dinther <martin.van.dinther@monetdbsolutions.com>
date	Thu, 07 Nov 2024 18:02:34 +0100 (5 months ago)
parents	d311affc65f0
children	80ade6a717c2

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

Mercurial > hg > monetdb-java

annotate src/main/java/org/monetdb/util/MDBvalidator.java @ 932:f16966084980