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

annotate src/main/java/org/monetdb/util/MDBvalidator.java @ 974:a0d66ec5eaf8 default tip

Update monetdb docker image matrix

author	Martin van Dinther <martin.van.dinther@monetdbsolutions.com>
date	Thu, 01 May 2025 14:08:13 +0200 (2 hours ago)
parents	d416e9b6b3d0
children

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 *
937 d416e9b6b3d0 Update Copyright year. Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: 936 diff changeset	8 * Copyright 2024, 2025 MonetDB Foundation;
833 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"
936 e9d65d746c80 When querying unique constraints metadata include new type 3 (= Unique Key With Nulls Not Distinct). Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: 935 diff changeset	281 + " WHERE k.\"type\" ").append(pkey ? "= 0" : "IN (1,3)") // 0 = Primary Key, 1 = Unique Key, 3 = Unique Key With Nulls Not Distinct
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"
936 e9d65d746c80 When querying unique constraints metadata include new type 3 (= Unique Key With Nulls Not Distinct). Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: 935 diff changeset	294 + " WHERE k.\"type\" ").append(pkey ? "= 0" : "IN (1,3)") // 0 = Primary Key, 1 = Unique Key, 3 = Unique Key With Nulls Not Distinct
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"
935 540d8b5944b1 Add double quotes also round column names: type and language. They are keywords and may become reserved keywords in the future. Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: 934 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" +
935 540d8b5944b1 Add double quotes also round column names: type and language. They are keywords and may become reserved keywords in the future. Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: 934 diff changeset	432 " WHERE fk.\"type\" = 2" + // 2 = foreign keys
675 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")
934 80ade6a717c2 Add double quotes around column names which will become reserved keywords: schema, table, column. Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: 932 diff changeset	551 \|\| "\"schema\"".equals(colx) \|\| "\"table\"".equals(colx) \|\| "\"column\"".equals(colx)
80ade6a717c2 Add double quotes around column names which will become reserved keywords: schema, table, column. Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: 932 diff changeset	552 \|\| "func".equals(colx) \|\| "mod".equals(colx) \|\| "statement".equals(colx)
935 540d8b5944b1 Add double quotes also round column names: type and language. They are keywords and may become reserved keywords in the future. Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: 934 diff changeset	553 \|\| ("\"type\"".equals(colx) && ("_columns".equals(tbl) \|\| "args".equals(tbl) \|\| "storage()".equals(tbl) \|\| "storagemodelinput".equals(tbl)))) {
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	554 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	555 }
675 844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	556 }
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	557 } else {
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	558 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	559
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 /* 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	561 if (col.endsWith("name") \|\| col.endsWith("keyword")
934 80ade6a717c2 Add double quotes around column names which will become reserved keywords: schema, table, column. Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: 932 diff changeset	562 \|\| "\"schema\"".equals(col) \|\| "\"table\"".equals(col) \|\| "\"column\"".equals(col)
80ade6a717c2 Add double quotes around column names which will become reserved keywords: schema, table, column. Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: 932 diff changeset	563 \|\| "func".equals(col) \|\| "mod".equals(col) \|\| "statement".equals(col)
935 540d8b5944b1 Add double quotes also round column names: type and language. They are keywords and may become reserved keywords in the future. Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: 934 diff changeset	564 \|\| ("\"type\"".equals(col) && ("_columns".equals(tbl) \|\| "args".equals(tbl) \|\| "storage()".equals(tbl) \|\| "storagemodelinput".equals(tbl)))) {
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	565 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	566 }
675 844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	567 }
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	568 // reuse the StringBuilder by cleaning it partial
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	569 sb.setLength(qry_len);
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	570 sb.append(col)
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	571 .append(", * FROM ").append(schema).append('.').append(tbl)
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	572 .append(" WHERE ").append(isNullCond).append(';');
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	573 validateQuery(sb.toString(), schema, tbl, col, checkType);
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 }
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	577
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	578 /* 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	579 private void validateNotNull(
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	580 final String schema,
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	581 final boolean system,
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	582 final String checkType)
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	583 {
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	584 final Statement stmt = createStatement("validateNotNull()");
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	585 if (stmt == null)
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	586 return;
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	587
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	588 // 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	589 final StringBuilder sb = new StringBuilder(400);
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	590 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"
935 540d8b5944b1 Add double quotes also round column names: type and language. They are keywords and may become reserved keywords in the future. Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: 934 diff changeset	591 + " where t.\"type\" in (0, 10, 1, 11) and c.\"null\" = false" // t."type" 0 = TABLE, 10 = SYSTEM TABLE, 1 = VIEW, 11 = SYSTEM VIEW
675 844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	592 + " 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	593 .append(" and s.name = '").append(schema).append('\'');
675 844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	594 String qry = sb.toString();
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	595 final int count = runCountQuery(qry);
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	596 if (showValidationInfo)
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	597 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	598
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	599 ResultSet rs = null;
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	600 try {
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	601 sb.setLength(0); // empty previous usage of sb
935 540d8b5944b1 Add double quotes also round column names: type and language. They are keywords and may become reserved keywords in the future. Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: 934 diff changeset	602 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
675 844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	603 .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	604 qry = sb.toString();
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	605 rs = stmt.executeQuery(qry);
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	606 if (rs != null) {
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	607 String sch, tbl, col;
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	608 while (rs.next()) {
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	609 // retrieve meta data
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	610 sch = rs.getString(1);
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	611 tbl = rs.getString(2);
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	612 col = rs.getString(3);
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	613 // compose validation query for this specific column
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	614 sb.setLength(0); // empty previous usage of sb
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	615 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	616 .append(" FROM \"").append(sch).append("\".\"").append(tbl).append('"')
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	617 .append(" WHERE \"").append(col).append("\" IS NULL;");
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	618 validateQuery(sb.toString(), sch, tbl, col, checkType);
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	619 }
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	620 }
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	621 } catch (SQLException e) {
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	622 System.err.println("Failed to execute query: " + qry);
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	623 printExceptions(e);
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 freeStmtRs(stmt, rs);
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	626 }
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	627
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	628 /* 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	629 private void validateMaxCharStrLength(
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	630 final String schema,
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	631 final boolean system,
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	632 final String checkType)
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	633 {
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	634 final Statement stmt = createStatement("validateMaxCharStrLength()");
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	635 if (stmt == null)
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	636 return;
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	637
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	638 // 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	639 final StringBuilder sb = new StringBuilder(400);
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	640 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"
935 540d8b5944b1 Add double quotes also round column names: type and language. They are keywords and may become reserved keywords in the future. Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: 934 diff changeset	641 + " where t.\"type\" in (0, 10, 1, 11)" // t."type" 0 = TABLE, 10 = SYSTEM TABLE, 1 = VIEW, 11 = SYSTEM VIEW
675 844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	642 + " 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	643 + " and t.system = ").append(system)
935 540d8b5944b1 Add double quotes also round column names: type and language. They are keywords and may become reserved keywords in the future. Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: 934 diff changeset	644 .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	645 .append(" and s.name = '").append(schema).append('\'');
675 844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	646 String qry = sb.toString();
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	647 final int count = runCountQuery(qry);
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	648 if (showValidationInfo)
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	649 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	650
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	651 ResultSet rs = null;
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	652 try {
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	653 sb.setLength(0); // empty previous usage of sb
935 540d8b5944b1 Add double quotes also round column names: type and language. They are keywords and may become reserved keywords in the future. Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: 934 diff changeset	654 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"
675 844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	655 .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	656 qry = sb.toString();
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	657 rs = stmt.executeQuery(qry);
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	658 if (rs != null) {
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	659 long max_len = 0;
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	660 String sch, tbl, col;
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	661 while (rs.next()) {
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	662 // retrieve meta data
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	663 sch = rs.getString(1);
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	664 tbl = rs.getString(2);
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	665 col = rs.getString(3);
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	666 max_len = rs.getLong(4);
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	667 // 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	668 if (system && max_len == 2048 && col.equals("query"))
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	669 max_len = 1048576;
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	670 // compose validation query for this specific column
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	671 sb.setLength(0); // empty previous usage of sb
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	672 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	673 .append(max_len).append(" as max_allowed_length, ")
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	674 .append("length(\"").append(col).append("\") as data_length, ")
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	675 .append('"').append(col).append("\" as data_value")
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	676 .append(" FROM \"").append(sch).append("\".\"").append(tbl).append('"')
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	677 .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	678 validateQuery(sb.toString(), sch, tbl, col, checkType);
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	679 }
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	680 }
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	681 } catch (SQLException e) {
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	682 System.err.println("Failed to execute query: " + qry);
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	683 printExceptions(e);
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 freeStmtRs(stmt, rs);
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
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	688
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	689 /* Run a validation query.
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	690 * It should result in no rows returned.
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	691 * 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	692 * 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	693 * Log/Print the violation.
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	694 */
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	695 private void validateQuery(
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	696 final String qry,
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	697 final String sch,
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	698 final String tbl,
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	699 final String cols,
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	700 final String checkType)
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	701 {
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	702 final Statement stmt = createStatement("validateQuery()");
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	703 if (stmt == null)
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	704 return;
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	705
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	706 ResultSet rs = null;
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	707 try {
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	708 if (verbose) {
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	709 System.out.println(qry);
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	710 }
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	711 rs = stmt.executeQuery(qry);
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	712 if (rs != null) {
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	713 final ResultSetMetaData rsmd = rs.getMetaData();
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	714 final int nr_cols = rsmd.getColumnCount();
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	715 final StringBuilder sb = new StringBuilder(1024);
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	716 final int maxprintrows = 16;
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	717 int row = 0;
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	718 String val;
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	719 int tp;
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	720 while (rs.next()) {
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	721 // query returns found violations
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	722 row++;
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	723 if (row == 1) {
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	724 // print result header once
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	725 for (int i = 1; i <= nr_cols; i++) {
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	726 sb.append((i > 1) ? ", " : "\t");
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	727 sb.append(rsmd.getColumnLabel(i));
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 sb.append('\n');
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	730 }
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	731 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	732 // retrieve row data
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	733 for (int i = 1; i <= nr_cols; i++) {
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	734 sb.append((i > 1) ? ", " : "\t");
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	735 val = rs.getString(i);
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	736 if (val == null \|\| rs.wasNull()) {
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	737 sb.append("null");
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	738 } else {
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	739 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	740 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	741 \|\| tp == Types.VARBINARY \|\| tp == Types.BLOB
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	742 \|\| 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	743 \|\| 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	744 sb.append('"').append(val).append('"');
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	745 } else {
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	746 sb.append(val);
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 }
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 sb.append('\n');
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	751 }
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	752 }
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	753 if (row > 0) {
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	754 if (row > maxprintrows) {
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	755 sb.append("...\n");
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	756 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	757 }
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	758 logViolations(checkType, sch, tbl, cols, qry, sb.toString());
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	759 }
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	760 }
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	761 } 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	762 // 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	763 // 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	764 // "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	765 // "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	766 // 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	767 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	768 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	769 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	770 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	771 // 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	772 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	773 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	774 \|\| 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	775 }
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 }
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 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	778 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	779 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	780 }
675 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 freeStmtRs(stmt, rs);
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	783 }
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	784
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	785 private int runCountQuery(final String from_qry) {
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	786 final Statement stmt = createStatement("runCountQuery()");
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	787 if (stmt == null)
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	788 return 0;
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	789
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	790 ResultSet rs = null;
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	791 int count = 0;
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	792 try {
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	793 rs = stmt.executeQuery("SELECT COUNT(*) " + from_qry);
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	794 if (rs != null) {
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	795 if (rs.next()) {
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	796 // retrieve count data
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	797 count = rs.getInt(1);
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	798 }
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	799 }
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	800 } catch (SQLException e) {
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	801 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	802 printExceptions(e);
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	803 }
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	804 freeStmtRs(stmt, rs);
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	805 return count;
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	806 }
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	807
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	808 private Statement createStatement(final String method) {
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	809 try {
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	810 return con.createStatement();
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	811 } catch (SQLException e) {
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	812 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	813 printExceptions(e);
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 return null;
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	816 }
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	817
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	818 private boolean checkMonetDBVersion() {
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	819 if (majorversion == 0 && minorversion == 0) {
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	820 // we haven't fetched them before.
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	821 try {
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	822 // 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	823 final DatabaseMetaData dbmd = con.getMetaData();
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	824 if (dbmd != null) {
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	825 // System.out.println("MonetDB server version " + dbmd.getDatabaseProductVersion());
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	826 majorversion = dbmd.getDatabaseMajorVersion();
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	827 minorversion = dbmd.getDatabaseMinorVersion();
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	828 // 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	829 if (((minorversion / 2 ) * 2) == minorversion) {
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	830 // 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	831 //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	832 minorversion++;
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	833 }
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	834 }
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	835 } catch (SQLException e) {
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	836 printExceptions(e);
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	837 }
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	838 }
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	839 // 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	840 if (majorversion < 11 \|\| (majorversion == 11 && minorversion < 19)) {
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	841 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	842 return false;
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 return true;
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	845 }
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	846
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	847 private boolean isValidVersion(final String version) {
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	848 if (version == null)
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	849 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	850
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	851 try {
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	852 final int v = Integer.parseInt(version);
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	853 return minorversion >= v;
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	854 } catch (NumberFormatException e) {
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	855 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	856 }
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	857 return false;
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	858 }
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	859
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	860 private boolean checkSchemaExists(final String schema) {
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	861 final Statement stmt = createStatement("checkSchemaExists()");
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	862 if (stmt == null)
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	863 return false;
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	864
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	865 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	866 ResultSet rs = null;
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	867 boolean ret = false;
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	868 try {
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	869 rs = stmt.executeQuery(sql);
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	870 if (rs != null) {
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	871 if (rs.next()) {
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	872 if (schema != null && schema.equals(rs.getString(1)))
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	873 ret = true;
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	874 }
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	875 }
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	876 } catch (SQLException e) {
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	877 System.err.println("Failed to execute " + sql);
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	878 printExceptions(e);
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	879 }
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	880 freeStmtRs(stmt, rs);
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	881 return ret;
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	882 }
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	883
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	884 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	885 final Statement stmt = createStatement("checkTableExists()");
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	886 if (stmt == null)
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	887 return false;
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	888
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	889 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	890 ResultSet rs = null;
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	891 boolean ret = false;
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	892 try {
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	893 rs = stmt.executeQuery(sql);
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	894 if (rs != null) {
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	895 if (rs.next()) {
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	896 if (schema != null && schema.equals(rs.getString(1))
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	897 && table != null && table.equals(rs.getString(2)) )
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	898 ret = true;
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	899 }
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	900 }
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	901 } catch (SQLException e) {
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	902 System.err.println("Failed to execute " + sql);
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	903 printExceptions(e);
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	904 }
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	905 freeStmtRs(stmt, rs);
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	906 return ret;
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	907 }
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	908
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	909 private void logViolations(
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	910 final String checkType,
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	911 final String schema,
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	912 final String table,
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	913 final String columns,
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	914 final String query,
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	915 final String violations)
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	916 {
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	917 final StringBuilder sb = new StringBuilder(2048);
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	918 sb.append(checkType).append(" violation(s) found in \"")
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	919 .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	920 .append(violations)
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	921 .append("Found using query: ").append(query).append("\n");
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	922 System.out.println(sb.toString());
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	923 }
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	924
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	925 private static void printExceptions(SQLException se) {
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	926 while (se != null) {
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	927 System.err.println(se.getSQLState() + " " + se.getMessage());
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	928 se = se.getNextException();
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 }
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	931
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	932 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	933 // free resources
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	934 if (rs != null) {
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	935 try { rs.close(); } catch (SQLException e) { /* ignore */ }
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	936 }
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	937 if (stmt != null) {
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	938 try { stmt.close(); } catch (SQLException e) { /* ignore */ }
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 }
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	941
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	942 private static String minimumWidth(int val, int minWidth) {
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	943 final String valstr = Integer.toString(val);
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	944 final int spacesneeded = minWidth - valstr.length();
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	945 switch (spacesneeded) {
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	946 case 1: return " " + valstr;
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	947 case 2: return " " + valstr;
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	948 case 3: return " " + valstr;
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	949 case 4: return " " + valstr;
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	950 case 5: return " " + valstr;
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	951 case 6: return " " + valstr;
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	952 default: return valstr;
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
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	956
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	957 // ******* 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	958 // 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	959
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	960 // 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	961 // 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	962 // 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	963 private static final String[][] sys_pkeys = {
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	964 {"schemas", "id", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	965 {"_tables", "id", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	966 {"tables", "id", null}, // is a view
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	967 {"_columns", "id", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	968 {"columns", "id", null}, // is a view
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	969 {"functions", "id", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	970 // 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	971 {"args", "id", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	972 {"types", "id", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	973 {"objects", "id, nr", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	974 {"keys", "id", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	975 {"idxs", "id", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	976 {"triggers", "id", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	977 {"sequences", "id", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	978 {"dependency_types", "dependency_type_id", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	979 {"dependencies", "id, depend_id", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	980 {"auths", "id", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	981 {"users", "name", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	982 {"user_role", "login_id, role_id", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	983 {"privileges", "obj_id, auth_id, privileges", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	984 {"querylog_catalog", "id", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	985 {"querylog_calls", "id", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	986 {"querylog_history", "id", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	987 {"optimizers", "name", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	988 {"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	989 {"db_user_info", "name", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	990 {"statistics", "column_id", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	991 // old {"tracelog", "event", null}, -- Error: Profiler not started. This table now (from Jun2020) contains only: ticks, stmt
934 80ade6a717c2 Add double quotes around column names which will become reserved keywords: schema, table, column. Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: 932 diff changeset	992 {"\"storage\"()", "\"schema\", \"table\", \"column\"", null}, // the function "storage"() also lists the storage for system tables
80ade6a717c2 Add double quotes around column names which will become reserved keywords: schema, table, column. Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: 932 diff changeset	993 // {"\"storage\"", "\"schema\", \"table\", \"column\"", null}, // is a view on table producing function: sys.storage() which filters out all system tables.
80ade6a717c2 Add double quotes around column names which will become reserved keywords: schema, table, column. Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: 932 diff changeset	994 {"storagemodelinput", "\"schema\", \"table\", \"column\"", null},
80ade6a717c2 Add double quotes around column names which will become reserved keywords: schema, table, column. Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: 932 diff changeset	995 // {"storagemodel", "\"schema\", \"table\", \"column\"", null}, // is a view on storagemodelinput
80ade6a717c2 Add double quotes around column names which will become reserved keywords: schema, table, column. Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: 932 diff changeset	996 // {"tablestoragemodel", "\"schema\", \"table\"", null}, // is a view on storagemodelinput
675 844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	997
927 d311affc65f0 Stop referring to monetdb.org/bugzilla, point straight to github. Sjoerd Mullender <sjoerd@acm.org> parents: 890 diff changeset	998 {"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	999
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1000 // 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	1001 {"keywords", "keyword", "21"},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1002 {"table_types", "table_type_id", "21"},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1003
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1004 // 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	1005 {"function_languages", "language_id", "27"},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1006 {"function_types", "function_type_id", "27"},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1007 {"index_types", "index_type_id", "27"},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1008 {"key_types", "key_type_id", "27"},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1009 {"privilege_codes", "privilege_code_id", "27"},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1010
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1011 // 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	1012 {"comments", "id", "29"},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1013 {"ids", "id", "29"}, // is a view
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1014 {"var_values", "var_name", "29"}, // is a view
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1015
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1016 // new views introduced in Apr2019 feature release (11.33.3)
934 80ade6a717c2 Add double quotes around column names which will become reserved keywords: schema, table, column. Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: 932 diff changeset	1017 // {"tablestorage", "\"schema\", \"table\"", "33"}, // is a view on view storage, see check on "storage"() above
80ade6a717c2 Add double quotes around column names which will become reserved keywords: schema, table, column. Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: 932 diff changeset	1018 // {"schemastorage", "\"schema\"", "33"}, // is a view on view storage, see check on "storage"() above
675 844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1019 // 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	1020 {"table_partitions", "id", "33"},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1021 {"range_partitions", "table_id, partition_id, minimum", "33"},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1022 {"value_partitions", "table_id, partition_id, \"value\"", "33"},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1023
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1024 // 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	1025 // 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	1026 {"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	1027 // 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	1028 {"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	1029
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1030 // 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	1031 {"fkey_actions", "action_id", "43"},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1032 {"fkeys", "id", "43"}
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1033 };
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1034
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1035 private static final String[][] tmp_pkeys = {
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1036 {"_tables", "id", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1037 {"_columns", "id", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1038 {"objects", "id, nr", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1039 {"keys", "id", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1040 {"idxs", "id", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1041 {"triggers", "id", null}
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1042 };
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1043
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1044 private static final String[][] netcdf_pkeys = {
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1045 {"netcdf_files", "file_id", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1046 {"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	1047 {"netcdf_dims", "dim_id, file_id", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1048 {"netcdf_vars", "var_id, file_id", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1049 {"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	1050 };
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1051
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1052 private static final String[][] geom_pkeys = {
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1053 {"spatial_ref_sys", "srid", null}
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
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1056
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1057 // 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	1058 // 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	1059 // 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	1060 private static final String[][] sys_akeys = {
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1061 {"schemas", "name", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1062 {"_tables", "schema_id, name", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1063 {"tables", "schema_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, name", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1065 {"columns", "table_id, name", null}, // is a view
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1066 {"_columns", "table_id, number", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1067 {"columns", "table_id, number", null}, // is a view
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1068 // 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	1069 {"(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	1070 {"(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	1071 // the next query used to return duplicates for overloaded functions (same function but with different arg names/types), hence it has been extended
935 540d8b5944b1 Add double quotes also round column names: type and language. They are keywords and may become reserved keywords in the future. Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: 934 diff changeset	1072 {"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},
675 844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1073 {"args", "func_id, name, inout", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1074 {"types", "schema_id, systemname, sqlname", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1075 {"objects", "id, name", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1076 {"keys", "table_id, name", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1077 {"idxs", "table_id, name", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1078 {"triggers", "table_id, name", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1079 {"sequences", "schema_id, name", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1080 {"dependency_types", "dependency_type_name", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1081 {"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	1082 {"optimizers", "def", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1083
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1084 // new tables introduced in older release
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1085 {"table_types", "table_type_name", "21"},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1086 {"function_types", "function_type_name", "27"},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1087 {"function_languages", "language_name", "27"},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1088 {"index_types", "index_type_name", "27"},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1089 {"key_types", "key_type_name", "27"},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1090 {"privilege_codes", "privilege_code_name", "27"},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1091 {"comments", "id", "29"},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1092 // 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	1093 {"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	1094 {"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	1095 {"range_partitions", "table_id, partition_id, \"maximum\"", "33"},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1096 // 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	1097 {"fkey_actions", "action_name", "43"},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1098 {"fkeys", "table_id, name", "43"}
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1099 };
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1100
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1101 private static final String[][] tmp_akeys = {
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1102 {"_tables", "schema_id, name", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1103 {"_columns", "table_id, name", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1104 {"_columns", "table_id, number", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1105 {"objects", "id, name", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1106 {"keys", "table_id, name", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1107 {"idxs", "table_id, name", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1108 {"triggers", "table_id, name", null}
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1109 };
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1110
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1111 private static final String[][] netcdf_akeys = {
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1112 {"netcdf_files", "location", null}
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1113 };
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1114
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1115 private static final String[][] geom_akeys = {
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1116 {"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	1117 };
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1118
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1119
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1120 // 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	1121 // 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	1122 // 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	1123 private static final String[][] sys_fkeys = {
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1124 {"schemas", "authorization", "id", "auths", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1125 {"schemas", "owner", "id", "auths", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1126 {"_tables", "schema_id", "id", "schemas", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1127 {"tables", "schema_id", "id", "schemas", null},
935 540d8b5944b1 Add double quotes also round column names: type and language. They are keywords and may become reserved keywords in the future. Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: 934 diff changeset	1128 {"_tables", "\"type\"", "table_type_id", "table_types", "21"},
540d8b5944b1 Add double quotes also round column names: type and language. They are keywords and may become reserved keywords in the future. Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: 934 diff changeset	1129 {"tables", "\"type\"", "table_type_id", "table_types", "21"},
675 844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1130 {"_columns", "table_id", "id", "_tables", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1131 {"columns", "table_id", "id", "tables", null},
935 540d8b5944b1 Add double quotes also round column names: type and language. They are keywords and may become reserved keywords in the future. Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: 934 diff changeset	1132 {"_columns", "\"type\"", "sqlname", "types", null},
540d8b5944b1 Add double quotes also round column names: type and language. They are keywords and may become reserved keywords in the future. Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: 934 diff changeset	1133 {"columns", "\"type\"", "sqlname", "types", null},
675 844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1134 {"functions", "schema_id", "id", "schemas", null},
935 540d8b5944b1 Add double quotes also round column names: type and language. They are keywords and may become reserved keywords in the future. Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: 934 diff changeset	1135 {"functions", "\"type\"", "function_type_id", "function_types", "27"},
540d8b5944b1 Add double quotes also round column names: type and language. They are keywords and may become reserved keywords in the future. Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: 934 diff changeset	1136 {"functions", "\"language\"", "language_id", "function_languages", "27"},
675 844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1137 // 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	1138 {"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	1139 {"args", "func_id", "id", "functions", null},
935 540d8b5944b1 Add double quotes also round column names: type and language. They are keywords and may become reserved keywords in the future. Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: 934 diff changeset	1140 {"args", "\"type\"", "sqlname", "types", null},
675 844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1141 {"types", "schema_id", "id", "schemas", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1142 // {"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	1143 {"objects", "id", "id", "ids", "29"},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1144 {"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	1145 {"keys", "id", "id", "objects", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1146 {"keys", "table_id", "id", "_tables", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1147 {"keys", "table_id", "id", "tables", null},
935 540d8b5944b1 Add double quotes also round column names: type and language. They are keywords and may become reserved keywords in the future. Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: 934 diff changeset	1148 {"keys", "\"type\"", "key_type_id", "key_types", "27"},
675 844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1149 {"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	1150 {"idxs", "id", "id", "objects", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1151 {"idxs", "table_id", "id", "_tables", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1152 {"idxs", "table_id", "id", "tables", null},
935 540d8b5944b1 Add double quotes also round column names: type and language. They are keywords and may become reserved keywords in the future. Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: 934 diff changeset	1153 {"idxs", "\"type\"", "index_type_id", "index_types", "27"},
675 844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1154 {"sequences", "schema_id", "id", "schemas", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1155 {"triggers", "table_id", "id", "_tables", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1156 {"triggers", "table_id", "id", "tables", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1157 {"comments", "id", "id", "ids", "29"},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1158 {"dependencies", "id", "id", "ids", "29"},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1159 {"dependencies", "depend_id", "id", "ids", "29"},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1160 {"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	1161 {"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	1162 {"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	1163 {"users", "name", "name", "auths", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1164 {"users", "default_schema", "id", "schemas", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1165 {"db_user_info", "name", "name", "auths", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1166 {"db_user_info", "default_schema", "id", "schemas", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1167 {"user_role", "login_id", "id", "auths", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1168 {"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	1169 {"user_role", "role_id", "id", "auths", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1170 {"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	1171 {"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	1172 {"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	1173 {"privileges", "auth_id", "id", "auths", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1174 {"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	1175 {"privileges", "privileges", "privilege_code_id", "privilege_codes", "27"},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1176 {"querylog_catalog", "owner", "name", "users", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1177 {"querylog_catalog", "pipe", "name", "optimizers", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1178 {"querylog_calls", "id", "id", "querylog_catalog", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1179 {"querylog_history", "id", "id", "querylog_catalog", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1180 {"querylog_history", "owner", "name", "users", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1181 {"querylog_history", "pipe", "name", "optimizers", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1182 // 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	1183 // 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	1184 {"sessions", "\"username\"", "name", "users", "37"},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1185 {"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	1186 {"statistics", "column_id", "id", "(SELECT id FROM sys._columns UNION ALL SELECT id FROM tmp._columns) as c", null},
935 540d8b5944b1 Add double quotes also round column names: type and language. They are keywords and may become reserved keywords in the future. Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: 934 diff changeset	1187 {"statistics", "\"type\"", "sqlname", "types", null},
934 80ade6a717c2 Add double quotes around column names which will become reserved keywords: schema, table, column. Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: 932 diff changeset	1188 {"storage()", "\"schema\"", "name", "schemas", null},
80ade6a717c2 Add double quotes around column names which will become reserved keywords: schema, table, column. Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: 932 diff changeset	1189 {"storage()", "\"table\"", "name", "(SELECT name FROM sys._tables UNION ALL SELECT name FROM tmp._tables) as t", null},
80ade6a717c2 Add double quotes around column names which will become reserved keywords: schema, table, column. Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: 932 diff changeset	1190 {"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},
80ade6a717c2 Add double quotes around column names which will become reserved keywords: schema, table, column. Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: 932 diff changeset	1191 {"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},
935 540d8b5944b1 Add double quotes also round column names: type and language. They are keywords and may become reserved keywords in the future. Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: 934 diff changeset	1192 {"storage()", "\"type\"", "sqlname", "types", null},
934 80ade6a717c2 Add double quotes around column names which will become reserved keywords: schema, table, column. Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: 932 diff changeset	1193 {"storage", "\"schema\"", "name", "schemas", null},
80ade6a717c2 Add double quotes around column names which will become reserved keywords: schema, table, column. Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: 932 diff changeset	1194 {"storage", "\"table\"", "name", "(SELECT name FROM sys._tables UNION ALL SELECT name FROM tmp._tables) as t", null},
80ade6a717c2 Add double quotes around column names which will become reserved keywords: schema, table, column. Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: 932 diff changeset	1195 {"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},
80ade6a717c2 Add double quotes around column names which will become reserved keywords: schema, table, column. Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: 932 diff changeset	1196 {"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},
935 540d8b5944b1 Add double quotes also round column names: type and language. They are keywords and may become reserved keywords in the future. Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: 934 diff changeset	1197 {"storage", "\"type\"", "sqlname", "types", null},
934 80ade6a717c2 Add double quotes around column names which will become reserved keywords: schema, table, column. Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: 932 diff changeset	1198 {"storagemodel", "\"schema\"", "name", "schemas", null},
80ade6a717c2 Add double quotes around column names which will become reserved keywords: schema, table, column. Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: 932 diff changeset	1199 {"storagemodel", "\"table\"", "name", "(SELECT name FROM sys._tables UNION ALL SELECT name FROM tmp._tables) as t", null},
80ade6a717c2 Add double quotes around column names which will become reserved keywords: schema, table, column. Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: 932 diff changeset	1200 {"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},
80ade6a717c2 Add double quotes around column names which will become reserved keywords: schema, table, column. Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: 932 diff changeset	1201 {"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},
935 540d8b5944b1 Add double quotes also round column names: type and language. They are keywords and may become reserved keywords in the future. Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: 934 diff changeset	1202 {"storagemodel", "\"type\"", "sqlname", "types", null},
934 80ade6a717c2 Add double quotes around column names which will become reserved keywords: schema, table, column. Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: 932 diff changeset	1203 {"storagemodelinput", "\"schema\"", "name", "schemas", null},
80ade6a717c2 Add double quotes around column names which will become reserved keywords: schema, table, column. Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: 932 diff changeset	1204 {"storagemodelinput", "\"table\"", "name", "(SELECT name FROM sys._tables UNION ALL SELECT name FROM tmp._tables) as t", null},
80ade6a717c2 Add double quotes around column names which will become reserved keywords: schema, table, column. Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: 932 diff changeset	1205 {"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},
80ade6a717c2 Add double quotes around column names which will become reserved keywords: schema, table, column. Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: 932 diff changeset	1206 {"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},
935 540d8b5944b1 Add double quotes also round column names: type and language. They are keywords and may become reserved keywords in the future. Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: 934 diff changeset	1207 {"storagemodelinput", "\"type\"", "sqlname", "types", null},
934 80ade6a717c2 Add double quotes around column names which will become reserved keywords: schema, table, column. Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: 932 diff changeset	1208 {"tablestoragemodel", "\"schema\"", "name", "schemas", null},
80ade6a717c2 Add double quotes around column names which will become reserved keywords: schema, table, column. Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: 932 diff changeset	1209 {"tablestoragemodel", "\"table\"", "name", "(SELECT name FROM sys._tables UNION ALL SELECT name FROM tmp._tables) as t", null},
80ade6a717c2 Add double quotes around column names which will become reserved keywords: schema, table, column. Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: 932 diff changeset	1210 {"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},
675 844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1211 // new tables / views introduced in Apr2019 "33"
934 80ade6a717c2 Add double quotes around column names which will become reserved keywords: schema, table, column. Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: 932 diff changeset	1212 {"schemastorage", "\"schema\"", "name", "schemas", "33"},
80ade6a717c2 Add double quotes around column names which will become reserved keywords: schema, table, column. Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: 932 diff changeset	1213 {"tablestorage", "\"schema\"", "name", "schemas", "33"},
80ade6a717c2 Add double quotes around column names which will become reserved keywords: schema, table, column. Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: 932 diff changeset	1214 {"tablestorage", "\"table\"", "name", "(SELECT name FROM sys._tables UNION ALL SELECT name FROM tmp._tables) as t", "33"},
80ade6a717c2 Add double quotes around column names which will become reserved keywords: schema, table, column. Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: 932 diff changeset	1215 {"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"},
675 844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1216 {"table_partitions", "table_id", "id", "_tables", "33"},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1217 {"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	1218 {"range_partitions", "table_id", "id", "_tables", "33"},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1219 {"range_partitions", "partition_id", "id", "table_partitions", "33"},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1220 {"value_partitions", "table_id", "id", "_tables", "33"},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1221 {"value_partitions", "partition_id", "id", "table_partitions", "33"},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1222 // 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	1223 {"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	1224 {"keys WHERE action >= 0 AND ", "cast((action & 255) as smallint)", "action_id", "fkey_actions", "43"}, // delete action id
935 540d8b5944b1 Add double quotes also round column names: type and language. They are keywords and may become reserved keywords in the future. Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: 934 diff changeset	1225 {"fkeys", "id, table_id, \"type\", name, rkey", "id, table_id, \"type\", name, rkey", "keys", "43"},
675 844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1226 {"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	1227 {"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	1228 };
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1229
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1230 private static final String[][] tmp_fkeys = {
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1231 {"_tables", "schema_id", "id", "sys.schemas", null},
935 540d8b5944b1 Add double quotes also round column names: type and language. They are keywords and may become reserved keywords in the future. Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: 934 diff changeset	1232 {"_tables", "\"type\"", "table_type_id", "sys.table_types", "21"},
675 844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1233 {"_columns", "table_id", "id", "_tables", null},
935 540d8b5944b1 Add double quotes also round column names: type and language. They are keywords and may become reserved keywords in the future. Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: 934 diff changeset	1234 {"_columns", "\"type\"", "sqlname", "sys.types", null},
675 844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1235 {"keys", "id", "id", "objects", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1236 {"keys", "table_id", "id", "_tables", null},
935 540d8b5944b1 Add double quotes also round column names: type and language. They are keywords and may become reserved keywords in the future. Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: 934 diff changeset	1237 {"keys", "\"type\"", "key_type_id", "sys.key_types", "27"},
675 844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1238 {"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	1239 {"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	1240 {"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	1241 {"idxs", "id", "id", "objects", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1242 {"idxs", "table_id", "id", "_tables", null},
935 540d8b5944b1 Add double quotes also round column names: type and language. They are keywords and may become reserved keywords in the future. Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: 934 diff changeset	1243 {"idxs", "\"type\"", "index_type_id", "sys.index_types", "27"},
675 844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1244 {"objects", "id", "id", "sys.ids", "29"},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1245 {"triggers", "table_id", "id", "_tables", null}
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1246 };
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1247
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1248 private static final String[][] netcdf_fkeys = {
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1249 {"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	1250 {"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	1251 {"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	1252 {"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	1253 {"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	1254 {"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	1255 {"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	1256 {"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	1257 };
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1258
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1259 private static final String[][] geom_fkeys = {
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1260 {"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	1261 };
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1262
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1263
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1264 // 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	1265 // 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	1266 // 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	1267 private static final String[][] sys_notnull = {
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1268 {"_columns", "id", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1269 {"_columns", "name", null},
935 540d8b5944b1 Add double quotes also round column names: type and language. They are keywords and may become reserved keywords in the future. Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: 934 diff changeset	1270 {"_columns", "\"type\"", null},
675 844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1271 {"_columns", "type_digits", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1272 {"_columns", "type_scale", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1273 {"_columns", "table_id", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1274 {"_columns", "\"null\"", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1275 {"_columns", "number", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1276 {"_tables", "id", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1277 {"_tables", "name", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1278 {"_tables", "schema_id", null},
935 540d8b5944b1 Add double quotes also round column names: type and language. They are keywords and may become reserved keywords in the future. Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: 934 diff changeset	1279 {"_tables", "\"type\"", null},
675 844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1280 {"_tables", "system", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1281 {"_tables", "commit_action", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1282 {"_tables", "access", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1283 {"args", "id", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1284 {"args", "func_id", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1285 {"args", "name", null},
935 540d8b5944b1 Add double quotes also round column names: type and language. They are keywords and may become reserved keywords in the future. Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: 934 diff changeset	1286 {"args", "\"type\"", null},
675 844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1287 {"args", "type_digits", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1288 {"args", "type_scale", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1289 {"args", "inout", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1290 {"args", "number", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1291 {"auths", "id", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1292 {"auths", "name", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1293 {"auths", "grantor", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1294 {"db_user_info", "name", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1295 {"db_user_info", "fullname", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1296 {"db_user_info", "default_schema", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1297 {"dependencies", "id", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1298 {"dependencies", "depend_id", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1299 {"dependencies", "depend_type", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1300 {"function_languages", "language_id", "27"},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1301 {"function_languages", "language_name", "27"},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1302 {"function_types", "function_type_id", "27"},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1303 {"function_types", "function_type_name", "27"},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1304 {"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	1305 {"functions", "id", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1306 {"functions", "name", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1307 {"functions", "func", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1308 {"functions", "mod", null},
935 540d8b5944b1 Add double quotes also round column names: type and language. They are keywords and may become reserved keywords in the future. Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: 934 diff changeset	1309 {"functions", "\"language\"", null},
540d8b5944b1 Add double quotes also round column names: type and language. They are keywords and may become reserved keywords in the future. Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: 934 diff changeset	1310 {"functions", "\"type\"", null},
675 844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1311 {"functions", "side_effect", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1312 {"functions", "varres", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1313 {"functions", "vararg", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1314 {"functions", "schema_id", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1315 {"functions", "system", "33"},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1316 {"idxs", "id", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1317 {"idxs", "table_id", null},
935 540d8b5944b1 Add double quotes also round column names: type and language. They are keywords and may become reserved keywords in the future. Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: 934 diff changeset	1318 {"idxs", "\"type\"", null},
675 844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1319 {"idxs", "name", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1320 {"index_types", "index_type_id", "27"},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1321 {"index_types", "index_type_name", "27"},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1322 {"key_types", "key_type_id", "27"},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1323 {"key_types", "key_type_name", "27"},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1324 {"keys", "id", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1325 {"keys", "table_id", null},
935 540d8b5944b1 Add double quotes also round column names: type and language. They are keywords and may become reserved keywords in the future. Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: 934 diff changeset	1326 {"keys", "\"type\"", null},
675 844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1327 {"keys", "name", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1328 {"keys", "rkey", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1329 {"keys", "action", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1330 {"keywords", "keyword", "21"},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1331 {"objects", "id", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1332 {"objects", "name", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1333 {"objects", "nr", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1334 {"optimizers", "name", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1335 {"optimizers", "def", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1336 {"optimizers", "status", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1337 {"privilege_codes", "privilege_code_id", "27"},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1338 {"privilege_codes", "privilege_code_name", "27"},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1339 {"privileges", "obj_id", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1340 {"privileges", "auth_id", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1341 {"privileges", "privileges", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1342 {"privileges", "grantor", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1343 {"privileges", "grantable", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1344 {"schemas", "id", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1345 {"schemas", "name", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1346 {"schemas", "authorization", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1347 {"schemas", "owner", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1348 {"schemas", "system", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1349 {"sequences", "id", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1350 {"sequences", "schema_id", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1351 {"sequences", "name", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1352 {"sequences", "start", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1353 {"sequences", "minvalue", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1354 {"sequences", "maxvalue", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1355 {"sequences", "increment", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1356 {"sequences", "cacheinc", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1357 {"sequences", "cycle", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1358 {"statistics", "column_id", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1359 {"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	1360 {"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	1361 {"statistics", "\"column\"", "43"}, // new column as of Jan2022 release (11.43.1)
935 540d8b5944b1 Add double quotes also round column names: type and language. They are keywords and may become reserved keywords in the future. Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: 934 diff changeset	1362 {"statistics", "\"type\"", null},
675 844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1363 {"statistics", "\"width\"", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1364 {"statistics", "\"count\"", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1365 {"statistics", "\"unique\"", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1366 {"statistics", "nils", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1367 {"statistics", "sorted", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1368 {"statistics", "revsorted", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1369 // the table producing function "storage"() also lists the storage for system tables, whereas the view "storage" does not, so use "storage"()
934 80ade6a717c2 Add double quotes around column names which will become reserved keywords: schema, table, column. Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: 932 diff changeset	1370 {"\"storage\"()", "\"schema\"", null},
80ade6a717c2 Add double quotes around column names which will become reserved keywords: schema, table, column. Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: 932 diff changeset	1371 {"\"storage\"()", "\"table\"", null},
80ade6a717c2 Add double quotes around column names which will become reserved keywords: schema, table, column. Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: 932 diff changeset	1372 {"\"storage\"()", "\"column\"", null},
935 540d8b5944b1 Add double quotes also round column names: type and language. They are keywords and may become reserved keywords in the future. Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: 934 diff changeset	1373 {"\"storage\"()", "\"type\"", null},
675 844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1374 {"\"storage\"()", "mode", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1375 {"\"storage\"()", "location", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1376 {"\"storage\"()", "count", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1377 {"\"storage\"()", "typewidth", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1378 {"\"storage\"()", "columnsize", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1379 {"\"storage\"()", "heapsize", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1380 {"\"storage\"()", "hashes", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1381 {"\"storage\"()", "phash", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1382 {"\"storage\"()", "imprints", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1383 {"\"storage\"()", "orderidx", null},
934 80ade6a717c2 Add double quotes around column names which will become reserved keywords: schema, table, column. Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: 932 diff changeset	1384 {"storagemodelinput", "\"schema\"", null},
80ade6a717c2 Add double quotes around column names which will become reserved keywords: schema, table, column. Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: 932 diff changeset	1385 {"storagemodelinput", "\"table\"", null},
80ade6a717c2 Add double quotes around column names which will become reserved keywords: schema, table, column. Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: 932 diff changeset	1386 {"storagemodelinput", "\"column\"", null},
935 540d8b5944b1 Add double quotes also round column names: type and language. They are keywords and may become reserved keywords in the future. Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: 934 diff changeset	1387 {"storagemodelinput", "\"type\"", null},
675 844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1388 {"storagemodelinput", "typewidth", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1389 {"storagemodelinput", "count", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1390 {"storagemodelinput", "\"distinct\"", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1391 {"storagemodelinput", "atomwidth", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1392 {"storagemodelinput", "reference", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1393 {"storagemodelinput", "sorted", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1394 {"storagemodelinput", "\"unique\"", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1395 {"storagemodelinput", "isacolumn", "33"},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1396 {"table_types", "table_type_id", "21"},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1397 {"table_types", "table_type_name", "21"},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1398 {"tables", "id", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1399 {"tables", "name", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1400 {"tables", "schema_id", null},
935 540d8b5944b1 Add double quotes also round column names: type and language. They are keywords and may become reserved keywords in the future. Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: 934 diff changeset	1401 {"tables", "\"type\"", null},
675 844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1402 {"tables", "system", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1403 {"tables", "commit_action", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1404 {"tables", "access", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1405 {"tables", "temporary", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1406 {"tracelog", "ticks", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1407 {"tracelog", "stmt", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1408 {"triggers", "id", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1409 {"triggers", "name", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1410 {"triggers", "table_id", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1411 {"triggers", "time", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1412 {"triggers", "orientation", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1413 {"triggers", "event", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1414 {"triggers", "statement", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1415 {"types", "id", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1416 {"types", "systemname", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1417 {"types", "sqlname", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1418 {"types", "digits", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1419 {"types", "scale", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1420 {"types", "radix", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1421 {"types", "eclass", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1422 {"types", "schema_id", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1423 {"user_role", "login_id", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1424 {"user_role", "role_id", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1425 {"users", "name", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1426 {"users", "fullname", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1427 {"users", "default_schema", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1428 {"var_values", "var_name", "29"},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1429 {"var_values", "value", "29"},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1430 // 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	1431 {"range_partitions", "table_id", "33"},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1432 {"range_partitions", "partition_id", "33"},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1433 {"range_partitions", "with_nulls", "33"},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1434 {"table_partitions", "id", "33"},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1435 {"table_partitions", "table_id", "33"},
935 540d8b5944b1 Add double quotes also round column names: type and language. They are keywords and may become reserved keywords in the future. Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: 934 diff changeset	1436 {"table_partitions", "\"type\"", "33"},
675 844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1437 {"value_partitions", "table_id", "33"},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1438 {"value_partitions", "partition_id", "33"},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1439 // 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	1440 {"fkey_actions", "action_id", "43"},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1441 {"fkey_actions", "action_name", "43"},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1442 {"fkeys", "id", "43"},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1443 {"fkeys", "table_id", "43"},
935 540d8b5944b1 Add double quotes also round column names: type and language. They are keywords and may become reserved keywords in the future. Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: 934 diff changeset	1444 {"fkeys", "\"type\"", "43"},
675 844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1445 {"fkeys", "name", "43"},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1446 {"fkeys", "rkey", "43"},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1447 {"fkeys", "update_action_id", "43"},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1448 {"fkeys", "update_action", "43"},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1449 {"fkeys", "delete_action_id", "43"},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1450 {"fkeys", "delete_action", "43"}
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1451 };
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1452
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1453 private static final String[][] tmp_notnull = {
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1454 {"_columns", "id", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1455 {"_columns", "name", null},
935 540d8b5944b1 Add double quotes also round column names: type and language. They are keywords and may become reserved keywords in the future. Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: 934 diff changeset	1456 {"_columns", "\"type\"", null},
675 844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1457 {"_columns", "type_digits", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1458 {"_columns", "type_scale", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1459 {"_columns", "table_id", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1460 {"_columns", "\"null\"", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1461 {"_columns", "number", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1462 {"_tables", "id", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1463 {"_tables", "name", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1464 {"_tables", "schema_id", null},
935 540d8b5944b1 Add double quotes also round column names: type and language. They are keywords and may become reserved keywords in the future. Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: 934 diff changeset	1465 {"_tables", "\"type\"", null},
675 844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1466 {"_tables", "system", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1467 {"_tables", "commit_action", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1468 {"_tables", "access", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1469 {"idxs", "id", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1470 {"idxs", "table_id", null},
935 540d8b5944b1 Add double quotes also round column names: type and language. They are keywords and may become reserved keywords in the future. Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: 934 diff changeset	1471 {"idxs", "\"type\"", null},
675 844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1472 {"idxs", "name", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1473 {"keys", "id", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1474 {"keys", "table_id", null},
935 540d8b5944b1 Add double quotes also round column names: type and language. They are keywords and may become reserved keywords in the future. Martin van Dinther <martin.van.dinther@monetdbsolutions.com> parents: 934 diff changeset	1475 {"keys", "\"type\"", null},
675 844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1476 {"keys", "name", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1477 {"keys", "rkey", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1478 {"keys", "action", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1479 {"objects", "id", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1480 {"objects", "name", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1481 {"objects", "nr", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1482 {"triggers", "id", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1483 {"triggers", "name", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1484 {"triggers", "table_id", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1485 {"triggers", "time", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1486 {"triggers", "orientation", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1487 {"triggers", "event", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1488 {"triggers", "statement", null}
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1489 };
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1490
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1491 private static final String[][] netcdf_notnull = {
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1492 {"netcdf_files", "file_id", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1493 {"netcdf_files", "location", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1494 {"netcdf_dims", "dim_id", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1495 {"netcdf_dims", "file_id", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1496 {"netcdf_dims", "name", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1497 {"netcdf_dims", "length", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1498 {"netcdf_vars", "var_id", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1499 {"netcdf_vars", "file_id", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1500 {"netcdf_vars", "name", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1501 {"netcdf_vars", "vartype", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1502 {"netcdf_vardim", "var_id", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1503 {"netcdf_vardim", "dim_id", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1504 {"netcdf_vardim", "file_id", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1505 {"netcdf_vardim", "dimpos", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1506 {"netcdf_attrs", "obj_name", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1507 {"netcdf_attrs", "att_name", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1508 {"netcdf_attrs", "att_type", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1509 {"netcdf_attrs", "value", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1510 {"netcdf_attrs", "file_id", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1511 {"netcdf_attrs", "gr_name", null}
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1512 };
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1513
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1514 private static final String[][] geom_notnull = {
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1515 {"spatial_ref_sys", "srid", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1516 {"spatial_ref_sys", "auth_name", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1517 {"spatial_ref_sys", "auth_srid", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1518 {"spatial_ref_sys", "srtext", null},
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1519 {"spatial_ref_sys", "proj4text", null}
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1520 };
844139b33cdd Remove carriage returns (\r) from files. Sjoerd Mullender <sjoerd@acm.org> parents: 610 diff changeset	1521 }

Mercurial > hg > monetdb-java

annotate src/main/java/org/monetdb/util/MDBvalidator.java @ 974:a0d66ec5eaf8 default tip