Changeset: 15b87fd68052 for monetdb-java URL: https://dev.monetdb.org/hg/monetdb-java?cmd=changeset;node=15b87fd68052 Modified Files: ChangeLog src/main/java/nl/cwi/monetdb/client/JdbcClient.java Branch: default Log Message:
Extended JdbcClient program with 3 new commands to quickly validate data integrity: \vsci validate sql system catalog integrity \vsi <schema> validate integrity of data in the given schema \vdbi validate integrity of data in all user schemas in the database The current validations include: - Primary Key uniqueness - Primary Key column(s) being NOT NULL (currently only for \vsci) - Unique constraint uniqueness - Foreign Key referential integrity (currently only for \vsci) - Column NOT NULL constraint - Varchar(n) max length constraint - Idem for char(n), clob(n), blob(n), json(n) and url(n). It can be usefull to run \vsci before and after an upgrade. Use \vsi my_schema to validate data in all tables of a specific schema. Use \vdbi to validate integrity of data in all user schemas in the database. Note this can take a while, depending on your number of user schemas and tables sizes. Despite being tested on several internal dbs the functionality is still beta, so you can get false errors reported. If you encounter these let us know asap. diffs (truncated from 3968 to 300 lines): diff --git a/ChangeLog b/ChangeLog --- a/ChangeLog +++ b/ChangeLog @@ -1,6 +1,28 @@ # ChangeLog file for monetdb-java # This file is updated with Maddlog +* Thu Oct 29 2020 Martin van Dinther <martin.van.dint...@monetdbsolutions.com> +- Extended JdbcClient program with 3 new commands to quickly validate + data integrity: + \vsci validate sql system catalog integrity + \vsi <schema> validate integrity of data in the given schema + \vdbi validate integrity of data in all user schemas in the database + The current validations include: + - Primary Key uniqueness + - Primary Key column(s) being NOT NULL (currently only for \vsci) + - Unique constraint uniqueness + - Foreign Key referential integrity (currently only for \vsci) + - Column NOT NULL constraint + - Varchar(n) max length constraint + - Idem for char(n), clob(n), blob(n), json(n) and url(n). + It can be usefull to run \vsci before and after an upgrade. + Use \vsi my_schema to validate data in all tables of a specific schema. + Use \vdbi to validate integrity of data in all user schemas in + the database. Note this can take a while, depending on your number + of user schemas and tables sizes. Despite being tested on several + internal dbs the functionality is still beta, so you can get false + errors reported. If you encounter these let us know asap. + * Thu Oct 8 2020 Martin van Dinther <martin.van.dint...@monetdbsolutions.com> - Improved performance of ResultSetMetaData methods isAutoIncrement(), getPrecision() and getScale() significant for columns of specific data diff --git a/src/main/java/nl/cwi/monetdb/client/JdbcClient.java b/src/main/java/nl/cwi/monetdb/client/JdbcClient.java --- a/src/main/java/nl/cwi/monetdb/client/JdbcClient.java +++ b/src/main/java/nl/cwi/monetdb/client/JdbcClient.java @@ -1,1306 +1,2626 @@ -/* - * This Source Code Form is subject to the terms of the Mozilla Public - * License, v. 2.0. If a copy of the MPL was not distributed with this - * file, You can obtain one at http://mozilla.org/MPL/2.0/. - * - * Copyright 1997 - July 2008 CWI, August 2008 - 2020 MonetDB B.V. - */ - -package nl.cwi.monetdb.client; - -import nl.cwi.monetdb.util.CmdLineOpts; -import nl.cwi.monetdb.util.Exporter; -import nl.cwi.monetdb.util.OptionsException; -import nl.cwi.monetdb.util.SQLExporter; -import nl.cwi.monetdb.util.XMLExporter; - -import java.io.BufferedReader; -import java.io.BufferedWriter; -import java.io.IOException; -import java.io.InputStreamReader; -import java.io.File; -import java.io.PrintWriter; -import java.net.HttpURLConnection; -import java.net.URL; -import java.sql.Connection; -import java.sql.DriverManager; // required as it will load the nl.cwi.monetdb.jdbc.MonetDriver class -import java.sql.DatabaseMetaData; -import java.sql.ResultSet; -import java.sql.ResultSetMetaData; -import java.sql.Statement; -import java.sql.SQLException; -import java.sql.SQLWarning; -import java.sql.Types; -import java.util.ArrayList; -import java.util.LinkedList; -import java.util.List; - -/** - * This program acts like an extended client program for MonetDB. Its - * look and feel is very much like PostgreSQL's interactive terminal - * program. Although it looks like this client is designed for MonetDB, - * it demonstrates the power of the JDBC interface since it built on top - * of JDBC only. - * - * @author Fabian Groffen, Martin van Dinther - * @version 1.4 - */ - -public final class JdbcClient { - - private static Connection con; - private static DatabaseMetaData dbmd; - private static Statement stmt; - private static BufferedReader in; - private static PrintWriter out; - private static Exporter exporter; - - /** - * JdbcClient is a command line query tool for MonetDB, similar to mclient. - * It uses the JDBC API and the MonetDB JDBC driver to communicate with a - * MonetDB server. The MonetDB JDBC driver is included in the jdbcclient.jre8.jar - * for ease of use, so only 1 jar file is needed to use it. - * - * <pre>Usage java -jar jdbcclient.jre8.jar - * [-h host[:port]] [-p port] [-f file] [-u user] - * [-l language] [-d database] [-e] [-D [table]] [-X<opt>] - * | [--help] | [--version] - * or using long option equivalents --host --port --file --user --language - * --dump --echo --database. - * Arguments may be written directly after the option like -p50000. - * - * If no host and port are given, localhost and 50000 are assumed. - * An .monetdb file may exist in the user's home directory. This file can contain - * preferences to use each time JdbcClient is started. Options given on the - * command line override the preferences file. The .monetdb file syntax is - * <option>=<value> where option is one of the options host, port, file, mode - * debug, or password. Note that the last one is perilous and therefore not - * available as command line option. - * If no input file is given using the -f flag, an interactive session is - * started on the terminal. - * - * OPTIONS - * -h --host The hostname of the host that runs the MonetDB database. A port - * number can be supplied by use of a colon, i.e. -h somehost:12345. - * -p --port The port number to connect to. - * -f --file A file name to use either for reading or writing. The file will - * be used for writing when dump mode is used (-D --dump). In read - * mode, the file can also be an URL pointing to a plain text file - * that is optionally gzip compressed. - * -u --user The username to use when connecting to the database. - * -d --database Try to connect to the given database (only makes sense if - * connecting to monetdbd). - * -l --language Use the given language, defaults to 'sql'. - * --help This help screen. - * --version Display driver version and exit. - * -e --echo Also outputs the contents of the input file, if any. - * -q --quiet Suppress printing the welcome header. - * -D --dump Dumps the given table(s), or the complete database if none given. - * -Xoutput The output mode when dumping. Default is sql, xml may be used for - * an experimental XML output. - * -Xhash Use the given hash algorithm during challenge response. Supported - * algorithm names: SHA512, SHA384, SHA256 and SHA1. - * -Xdebug Writes a transmission log to disk for debugging purposes. If a - * file name is given, it is used, otherwise a file called - * monet<timestamp>.log is created. A given file never be - * overwritten; instead a unique variation of the file is used. - * -Xbatching Indicates that a batch should be used instead of direct - * communication with the server for each statement. If a number is - * given, it is used as batch size. i.e. 8000 would execute the - * contents on the batch after each 8000 statements read. Batching - * can greatly speedup the process of restoring a database dump.</pre> - * - * @param args optional list of startup arguments - * @throws Exception if uncaught exception is thrown - */ - public final static void main(String[] args) throws Exception { - final CmdLineOpts copts = new CmdLineOpts(); - - // arguments which take exactly one argument - copts.addOption("h", "host", CmdLineOpts.CAR_ONE, "localhost", - "The hostname of the host that runs the MonetDB database. " + - "A port number can be supplied by use of a colon, i.e. " + - "-h somehost:12345."); - copts.addOption("p", "port", CmdLineOpts.CAR_ONE, "50000", - "The port number to connect to."); - // todo make it CAR_ONE_MANY - copts.addOption("f", "file", CmdLineOpts.CAR_ONE, null, - "A file name to use either for reading or writing. The " + - "file will be used for writing when dump mode is used " + - "(-D --dump). In read mode, the file can also be an URL " + - "pointing to a plain text file that is optionally gzip " + - "compressed."); - copts.addOption("u", "user", CmdLineOpts.CAR_ONE, System.getProperty("user.name"), - "The username to use when connecting to the database."); - // this one is only here for the .monetdb file parsing, it is - // removed before the command line arguments are parsed - copts.addOption(null, "password", CmdLineOpts.CAR_ONE, null, null); - copts.addOption("d", "database", CmdLineOpts.CAR_ONE, "", - "Try to connect to the given database (only makes sense " + - "if connecting to monetdbd)."); - copts.addOption("l", "language", CmdLineOpts.CAR_ONE, "sql", - "Use the given language, defaults to 'sql'."); - - // arguments which have no argument(s) - copts.addOption(null, "help", CmdLineOpts.CAR_ZERO, null, - "This help screen."); - copts.addOption(null, "version", CmdLineOpts.CAR_ZERO, null, - "Display driver version and exit."); - copts.addOption("e", "echo", CmdLineOpts.CAR_ZERO, null, - "Also outputs the contents of the input file, if any."); - copts.addOption("q", "quiet", CmdLineOpts.CAR_ZERO, null, - "Suppress printing the welcome header."); - - // arguments which have zero to many arguments - copts.addOption("D", "dump", CmdLineOpts.CAR_ZERO_MANY, null, - "Dumps the given table(s), or the complete database if " + - "none given."); - - // extended options - copts.addOption(null, "Xoutput", CmdLineOpts.CAR_ONE, null, - "The output mode when dumping. Default is sql, xml may " + - "be used for an experimental XML output."); - copts.addOption(null, "Xhash", CmdLineOpts.CAR_ONE, null, - "Use the given hash algorithm during challenge response. " + - "Supported algorithm names: SHA512, SHA384, SHA256 and SHA1."); - // arguments which can have zero or one argument(s) - copts.addOption(null, "Xdebug", CmdLineOpts.CAR_ZERO_ONE, null, - "Writes a transmission log to disk for debugging purposes. " + - "If a file name is given, it is used, otherwise a file " + - "called monet<timestamp>.log is created. A given file " + - "never be overwritten; instead a unique variation of the " + - "file is used."); - copts.addOption(null, "Xbatching", CmdLineOpts.CAR_ZERO_ONE, null, - "Indicates that a batch should be used instead of direct " + - "communication with the server for each statement. If a " + - "number is given, it is used as batch size. i.e. 8000 " + - "would execute the contents on the batch after each 8000 " + - "statements read. Batching can greatly speedup the " + - "process of restoring a database dump."); - - // we store user and password in separate variables in order to - // be able to properly act on them like forgetting the password - // from the user's file if the user supplies a username on the - // command line arguments - String pass = null; - String user = null; - - // look for a file called .monetdb in the current dir or in the - // user's homedir and read its preferences - File pref = new File(".monetdb"); - if (!pref.exists()) - pref = new File(System.getProperty("user.home"), ".monetdb"); - if (pref.exists()) { - try { - copts.processFile(pref); - } catch (OptionsException e) { - System.err.println("Error in " + pref.getAbsolutePath() + ": " + e.getMessage()); - System.exit(1); - } - user = copts.getOption("user").getArgument(); - pass = copts.getOption("password").getArgument(); - } - - // process the command line arguments, remove password option - // first, and save the user we had at this point - copts.removeOption("password"); - try { - copts.processArgs(args); - } catch (OptionsException e) { - System.err.println("Error: " + e.getMessage()); - System.exit(1); - } - // we can actually compare pointers (objects) here - if (user != copts.getOption("user").getArgument()) - pass = null; - - if (copts.getOption("help").isPresent()) { - System.out.print( - "Usage java -jar jdbcclient.jre8.jar\n" + - "\t\t[-h host[:port]] [-p port] [-f file] [-u user]\n" + - "\t\t[-l language] [-d database] [-e] [-D [table]] [-X<opt>]\n" + - "\t\t| [--help] | [--version]\n" + - "or using long option equivalents --host --port --file --user --language\n" + - "--dump --echo --database.\n" + - "Arguments may be written directly after the option like -p50000.\n" + - "\n" + - "If no host and port are given, localhost and 50000 are assumed.\n" + - "An .monetdb file may exist in the user's home directory. This file can contain\n" + - "preferences to use each time JdbcClient is started. Options given on the\n" + - "command line override the preferences file. The .monetdb file syntax is\n" + - "<option>=<value> where option is one of the options host, port, file, mode\n" + - "debug, or password. Note that the last one is perilous and therefore not\n" + - "available as command line option.\n" + - "If no input file is given using the -f flag, an interactive session is\n" + - "started on the terminal.\n" + - "\n" + - "OPTIONS\n" + - copts.produceHelpMessage() - ); - System.exit(0); - } - - if (copts.getOption("version").isPresent()) { - // We cannot use the DatabaseMetaData here, because we - // cannot get a Connection. So instead, we just get the - // values we want out of the Driver directly. - System.out.println("JDBC Driver: v" + nl.cwi.monetdb.jdbc.MonetDriver.getDriverVersion()); - System.exit(0); - } - - // whether the semi-colon at the end of a String terminates the - // query or not (default = yes => SQL) - final boolean scolonterm = true; - final boolean xmlMode = "xml".equals(copts.getOption("Xoutput").getArgument()); - - // we need the password from the user, fetch it with a pseudo - // password protector - if (pass == null) { - final char[] tmp = System.console().readPassword("password: "); - if (tmp == null) { - System.err.println("Invalid password!"); - System.exit(1); - } - pass = String.valueOf(tmp); _______________________________________________ checkin-list mailing list checkin-list@monetdb.org https://www.monetdb.org/mailman/listinfo/checkin-list
