HSQLDB example source code file (SqlTool.java)

This example HSQLDB source code file (SqlTool.java) is included in the DevDaily.com "Java Source Code Warehouse" project. The intent of this project is to help you "Learn Java by Example" ^TM.

Java - HSQLDB tags/keywords

badcmdline, exception, exception, file, io, ioexception, jdbc, ls, map, privateexception, privateexception, sql, sqlfile, sqltoolexception, sqltoolexception, string, string, util

The HSQLDB SqlTool.java source code

/* Copyright (c) 2001-2008, The HSQL Development Group
 * All rights reserved.
 *
 * Redistribution and use in source and binary forms, with or without
 * modification, are permitted provided that the following conditions are met:
 *
 * Redistributions of source code must retain the above copyright notice, this
 * list of conditions and the following disclaimer.
 *
 * Redistributions in binary form must reproduce the above copyright notice,
 * this list of conditions and the following disclaimer in the documentation
 * and/or other materials provided with the distribution.
 *
 * Neither the name of the HSQL Development Group nor the names of its
 * contributors may be used to endorse or promote products derived from this
 * software without specific prior written permission.
 *
 * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
 * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
 * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
 * ARE DISCLAIMED. IN NO EVENT SHALL HSQL DEVELOPMENT GROUP, HSQLDB.ORG,
 * OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL,
 * EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO,
 * PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
 * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
 * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
 * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
 */


package org.hsqldb.util;

import java.io.BufferedReader;
import java.io.File;
import java.io.IOException;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;

/* $Id: SqlTool.java,v 1.72 2007/06/29 12:23:47 unsaved Exp $ */

/**
 * Sql Tool.  A command-line and/or interactive SQL tool.
 * (Note:  For every Javadoc block comment, I'm using a single blank line
 *  immediately after the description, just like's Sun's examples in
 *  their Coding Conventions document).
 *
 * See JavaDocs for the main method for syntax of how to run.
 * This class is mostly used in a static (a.o.t. object) way, because most
 * of the work is done in the static main class.
 * This class should be refactored so that the main work is done in an
 * object method, and the static main invokes the object method.
 * Then programmatic users could use instances of this class in the normal
 * Java way.
 *
 * @see #main()
 * @version $Revision: 1.72 $
 * @author Blaine Simpson unsaved@users
 */
public class SqlTool {
    private static final String DEFAULT_RCFILE =
        System.getProperty("user.home") + "/sqltool.rc";
    // N.b. the following is static!
    private static String  revnum = null;

    public static final int SQLTOOLERR_EXITVAL = 1;
    public static final int SYNTAXERR_EXITVAL = 11;
    public static final int RCERR_EXITVAL = 2;
    public static final int SQLERR_EXITVAL = 3;
    public static final int IOERR_EXITVAL = 4;
    public static final int FILEERR_EXITVAL = 5;
    public static final int INPUTERR_EXITVAL = 6;
    public static final int CONNECTERR_EXITVAL = 7;

    /**
     * The configuration identifier to use when connection parameters are
     * specified on the command line
     */
    private static String CMDLINE_ID = "cmdline";
    static private SqltoolRB rb = null;
    // Must use a shared static RB object, since we need to get messages
    // inside of static methods.
    // This means that the locale will be set the first time this class
    // is accessed.  Subsequent calls will not update the RB if the locale
    // changes (could have it check and reload the RB if this becomes an
    // issue).

    static {
        revnum = "333";
        try {
            rb = new SqltoolRB();
            rb.validate();
            rb.setMissingPosValueBehavior(
                    ValidatingResourceBundle.NOOP_BEHAVIOR);
            rb.setMissingPropertyBehavior(
                    ValidatingResourceBundle.NOOP_BEHAVIOR);
        } catch (RuntimeException re) {
            System.err.println("Failed to initialize resource bundle");
            throw re;
        }
    }
    public static String LS = System.getProperty("line.separator");

    /** Utility nested class for internal use. */
    private static class BadCmdline extends Exception {
        static final long serialVersionUID = -2134764796788108325L;
        BadCmdline() {}
    }

    /** Utility object for internal use. */
    private static BadCmdline bcl = new BadCmdline();

    /** For trapping of exceptions inside this class.
     * These are always handled inside this class.
     */
    private static class PrivateException extends Exception {
        static final long serialVersionUID = -7765061479594523462L;

        PrivateException() {
            super();
        }

        PrivateException(String s) {
            super(s);
        }
    }

    public static class SqlToolException extends Exception {
        static final long serialVersionUID = 1424909871915188519L;

        int exitValue = 1;
        SqlToolException(String message, int exitValue) {
            super(message);
            this.exitValue = exitValue;
        }
        SqlToolException(int exitValue, String message) {
            this(message, exitValue);
        }
        SqlToolException(int exitValue) {
            super();
            this.exitValue = exitValue;
        }
    }

    /**
     * Prompt the user for a password.
     *
     * @param username The user the password is for
     * @return The password the user entered
     */
    private static String promptForPassword(String username)
    throws PrivateException {

        BufferedReader console;
        String         password;

        password = null;

        try {
            console = new BufferedReader(new InputStreamReader(System.in));

            // Prompt for password
            System.out.print(rb.getString(SqltoolRB.PASSWORDFOR_PROMPT,
                    RCData.expandSysPropVars(username)));

            // Read the password from the command line
            password = console.readLine();

            if (password == null) {
                password = "";
            } else {
                password = password.trim();
            }
        } catch (IOException e) {
            throw new PrivateException(e.getMessage());
        }

        return password;
    }

    /**
     * Parses a comma delimited string of name value pairs into a
     * <code>Map object.
     *
     * @param varString The string to parse
     * @param varMap The map to save the paired values into
     * @param lowerCaseKeys Set to <code>true if the map keys should be
     *        converted to lower case
     */
    private static void varParser(String varString, Map varMap,
                                  boolean lowerCaseKeys)
                                  throws PrivateException {

        int       equals;
        String    var;
        String    val;
        String[]  allvars;

        if ((varMap == null) || (varString == null)) {
            throw new IllegalArgumentException(
                    "varMap or varString are null in SqlTool.varParser call");
        }

        allvars = varString.split("\\s*,\\s*");

        for (int i = 0; i < allvars.length; i++) {
            equals     = allvars[i].indexOf('=');

            if (equals < 1) {
                throw new PrivateException(
                    rb.getString(SqltoolRB.SQLTOOL_VARSET_BADFORMAT));
            }

            var = allvars[i].substring(0, equals).trim();
            val = allvars[i].substring(equals + 1).trim();

            if (var.length() < 1) {
                throw new PrivateException(
                    rb.getString(SqltoolRB.SQLTOOL_VARSET_BADFORMAT));
            }

            if (lowerCaseKeys) {
                var = var.toLowerCase();
            }

            varMap.put(var, val);
        }
    }

    /**
     * A static wrapper for objectMain, so that that method may be executed
     * as a Java "program".
     *
     * Throws only RuntimExceptions or Errors, because this method is intended
     * to System.exit() for all but disasterous system problems, for which
     * the inconvenience of a a stack trace would be the least of your worries.
     *
     * If you don't want SqlTool to System.exit(), then use the method
     * objectMain() instead of this method.
     *
     * @see objectMain(String[])
     */
    public static void main(String[] args) {
        try {
            SqlTool.objectMain(args);
        } catch (SqlToolException fr) {
            if (fr.getMessage() != null) {
                System.err.println(fr.getMessage());
            }
            System.exit(fr.exitValue);
        }
        System.exit(0);
    }

    /**
     * Connect to a JDBC Database and execute the commands given on
     * stdin or in SQL file(s).
     *
     * This method is changed for HSQLDB 1.8.0.8 and 1.9.0.x to never
     * System.exit().
     *
     * @param arg  Run "java... org.hsqldb.util.SqlTool --help" for syntax.
     * @throws SqlToolException  Upon any fatal error, with useful
     *                          reason as the exception's message.
     */
    static public void objectMain(String[] arg) throws SqlToolException {

        /*
         * The big picture is, we parse input args; load a RCData;
         * get a JDBC Connection with the RCData; instantiate and
         * execute as many SqlFiles as we need to.
         */
        String  rcFile           = null;
        File    tmpFile          = null;
        String  sqlText          = null;
        String  driver           = null;
        String  targetDb         = null;
        String  varSettings      = null;
        boolean debug            = false;
        File[]  scriptFiles      = null;
        int     i                = -1;
        boolean listMode         = false;
        boolean interactive      = false;
        boolean noinput          = false;
        boolean noautoFile       = false;
        boolean autoCommit       = false;
        Boolean coeOverride      = null;
        Boolean stdinputOverride = null;
        String  rcParams         = null;
        String  rcUrl            = null;
        String  rcUsername       = null;
        String  rcPassword       = null;
        String  rcCharset        = null;
        String  rcTruststore     = null;
        Map     rcFields         = null;
        String  parameter;

        try {
            while ((i + 1 < arg.length) && arg[i + 1].startsWith("--")) {
                i++;

                if (arg[i].length() == 2) {
                    break;             // "--"
                }

                parameter = arg[i].substring(2).toLowerCase();

                if (parameter.equals("help")) {
                    System.out.println(rb.getString(SqltoolRB.SQLTOOL_SYNTAX,
                            revnum, RCData.DEFAULT_JDBC_DRIVER));
                    return;
                }
                if (parameter.equals("abortonerr")) {
                    if (coeOverride != null) {
                        throw new SqlToolException(SYNTAXERR_EXITVAL,
                                rb.getString(
                        SqltoolRB.SQLTOOL_ABORTCONTINUE_MUTUALLYEXCLUSIVE));
                    }

                    coeOverride = Boolean.FALSE;
                } else if (parameter.equals("continueonerr")) {
                    if (coeOverride != null) {
                        throw new SqlToolException(SYNTAXERR_EXITVAL,
                                rb.getString(
                        SqltoolRB.SQLTOOL_ABORTCONTINUE_MUTUALLYEXCLUSIVE));
                    }

                    coeOverride = Boolean.TRUE;
                } else if (parameter.equals("list")) {
                    listMode = true;
                } else if (parameter.equals("rcfile")) {
                    if (++i == arg.length) {
                        throw bcl;
                    }

                    rcFile = arg[i];
                } else if (parameter.equals("setvar")) {
                    if (++i == arg.length) {
                        throw bcl;
                    }

                    varSettings = arg[i];
                } else if (parameter.equals("sql")) {
                    noinput = true;    // but turn back on if file "-" specd.

                    if (++i == arg.length) {
                        throw bcl;
                    }

                    sqlText = arg[i];
                } else if (parameter.equals("debug")) {
                    debug = true;
                } else if (parameter.equals("noautofile")) {
                    noautoFile = true;
                } else if (parameter.equals("autocommit")) {
                    autoCommit = true;
                } else if (parameter.equals("stdinput")) {
                    noinput          = false;
                    stdinputOverride = Boolean.TRUE;
                } else if (parameter.equals("noinput")) {
                    noinput          = true;
                    stdinputOverride = Boolean.FALSE;
                } else if (parameter.equals("driver")) {
                    if (++i == arg.length) {
                        throw bcl;
                    }

                    driver = arg[i];
                } else if (parameter.equals("inlinerc")) {
                    if (++i == arg.length) {
                        throw bcl;
                    }

                    rcParams = arg[i];
                } else {
                    throw bcl;
                }
            }

            if (!listMode) {

                // If an inline RC file was specified, don't worry about the targetDb
                if (rcParams == null) {
                    if (++i == arg.length) {
                        throw bcl;
                    }

                    targetDb = arg[i];
                }
            }

            int scriptIndex = 0;

            if (sqlText != null) {
                try {
                    tmpFile = File.createTempFile("sqltool-", ".sql");

                    //(new java.io.FileWriter(tmpFile)).write(sqlText);
                    java.io.FileWriter fw = new java.io.FileWriter(tmpFile);
                    try {

                        fw.write("/* " + (new java.util.Date()) + ".  "
                                 + SqlTool.class.getName()
                                 + " command-line SQL. */" + LS + LS);
                        fw.write(sqlText + LS);
                        fw.flush();
                    } finally {
                        fw.close();
                    }
                } catch (IOException ioe) {
                    throw new SqlToolException(IOERR_EXITVAL,
                            rb.getString(SqltoolRB.SQLTEMPFILE_FAIL,
                                    ioe.toString()));
                }
            }

            if (stdinputOverride != null) {
                noinput = !stdinputOverride.booleanValue();
            }

            interactive = (!noinput) && (arg.length <= i + 1);

            if (arg.length == i + 2 && arg[i + 1].equals("-")) {
                if (stdinputOverride == null) {
                    noinput = false;
                }
            } else if (arg.length > i + 1) {

                // I.e., if there are any SQL files specified.
                scriptFiles = new File[arg.length - i - 1
                        + ((stdinputOverride == null
                                ||!stdinputOverride.booleanValue()) ? 0 : 1)];

                if (debug) {
                    System.err.println("scriptFiles has "
                                       + scriptFiles.length + " elements");
                }

                while (i + 1 < arg.length) {
                    scriptFiles[scriptIndex++] = new File(arg[++i]);
                }

                if (stdinputOverride != null
                        && stdinputOverride.booleanValue()) {
                    scriptFiles[scriptIndex++] = null;
                    noinput                    = true;
                }
            }
        } catch (BadCmdline bcl) {
            throw new SqlToolException(SYNTAXERR_EXITVAL,
                    rb.getString(SqltoolRB.SQLTOOL_SYNTAX,
                                revnum, RCData.DEFAULT_JDBC_DRIVER));
        }

        RCData conData = null;

        // Use the inline RC file if it was specified
        if (rcParams != null) {
            rcFields = new HashMap();

            try {
                varParser(rcParams, rcFields, true);
            } catch (PrivateException e) {
                throw new SqlToolException(SYNTAXERR_EXITVAL, e.getMessage());
            }

            rcUrl        = (String) rcFields.remove("url");
            rcUsername   = (String) rcFields.remove("user");
            rcCharset    = (String) rcFields.remove("charset");
            rcTruststore = (String) rcFields.remove("truststore");
            rcPassword   = (String) rcFields.remove("password");

            // Don't ask for password if what we have already is invalid!
            if (rcUrl == null || rcUrl.length() < 1)
                throw new SqlToolException(RCERR_EXITVAL, rb.getString(
                        SqltoolRB.RCDATA_INLINEURL_MISSING));
            if (rcUsername == null || rcUsername.length() < 1)
                throw new SqlToolException(RCERR_EXITVAL, rb.getString(
                        SqltoolRB.RCDATA_INLINEUSERNAME_MISSING));
            if (rcPassword != null && rcPassword.length() > 0)
                throw new SqlToolException(RCERR_EXITVAL, rb.getString(
                        SqltoolRB.RCDATA_PASSWORD_VISIBLE));
            if (rcFields.size() > 0) {
                throw new SqlToolException(INPUTERR_EXITVAL,
                        rb.getString(SqltoolRB.RCDATA_INLINE_EXTRAVARS,
                                rcFields.keySet().toString()));
            }

            if (rcPassword == null) try {
                rcPassword   = promptForPassword(rcUsername);
            } catch (PrivateException e) {
                throw new SqlToolException(INPUTERR_EXITVAL,
                        rb.getString(SqltoolRB.PASSWORD_READFAIL,
                                e.getMessage()));
            }
            try {
                conData = new RCData(CMDLINE_ID, rcUrl, rcUsername,
                                     rcPassword, driver, rcCharset,
                                     rcTruststore);
            } catch (Exception e) {
                throw new SqlToolException(RCERR_EXITVAL, rb.getString(
                        SqltoolRB.RCDATA_GENFROMVALUES_FAIL, e.getMessage()));
            }
        } else {
            try {
                conData = new RCData(new File((rcFile == null)
                                              ? DEFAULT_RCFILE
                                              : rcFile), targetDb);
            } catch (Exception e) {
                throw new SqlToolException(RCERR_EXITVAL, rb.getString(
                        SqltoolRB.CONNDATA_RETRIEVAL_FAIL,
                                targetDb, e.getMessage()));
            }
        }

        if (listMode) {
            return;
        }

        if (debug) {
            conData.report();
        }

        Connection conn = null;
        try {
            conn = conData.getConnection(
                driver, System.getProperty("sqlfile.charset"),
                System.getProperty("javax.net.ssl.trustStore"));

            conn.setAutoCommit(autoCommit);

            DatabaseMetaData md = null;

            if (interactive && (md = conn.getMetaData()) != null) {
                System.out.println(
                        rb.getString(SqltoolRB.JDBC_ESTABLISHED,
                                md.getDatabaseProductName(),
                                md.getDatabaseProductVersion(),
                                md.getUserName()));
            }
        } catch (Exception e) {
            //e.printStackTrace();

            // Let's not continue as if nothing is wrong.
            throw new SqlToolException(CONNECTERR_EXITVAL,
                    rb.getString(SqltoolRB.CONNECTION_FAIL,
                            conData.url, conData.username, e.getMessage()));
        }

        File[] emptyFileArray      = {};
        File[] singleNullFileArray = { null };
        File   autoFile            = null;

        if (interactive &&!noautoFile) {
            autoFile = new File(System.getProperty("user.home")
                                + "/auto.sql");

            if ((!autoFile.isFile()) ||!autoFile.canRead()) {
                autoFile = null;
            }
        }

        if (scriptFiles == null) {

            // I.e., if no SQL files given on command-line.
            // Input file list is either nothing or {null} to read stdin.
            scriptFiles = (noinput ? emptyFileArray
                                   : singleNullFileArray);
        }

        int numFiles = scriptFiles.length;

        if (tmpFile != null) {
            numFiles += 1;
        }

        if (autoFile != null) {
            numFiles += 1;
        }

        SqlFile[] sqlFiles = new SqlFile[numFiles];
        Map   userVars = new HashMap();

        if (varSettings != null) try {
            varParser(varSettings, userVars, false);
        } catch (PrivateException pe) {
            throw new SqlToolException(RCERR_EXITVAL, pe.getMessage());
        }

        // We print version before execing this one.
        int interactiveFileIndex = -1;

        try {
            int fileIndex = 0;

            if (autoFile != null) {
                sqlFiles[fileIndex++] = new SqlFile(autoFile, false,
                                                    userVars);
            }

            if (tmpFile != null) {
                sqlFiles[fileIndex++] = new SqlFile(tmpFile, false, userVars);
            }

            for (int j = 0; j < scriptFiles.length; j++) {
                if (interactiveFileIndex < 0 && interactive) {
                    interactiveFileIndex = fileIndex;
                }

                sqlFiles[fileIndex++] = new SqlFile(scriptFiles[j],
                                                    interactive, userVars);
            }
        } catch (IOException ioe) {
            try {
                conn.close();
            } catch (Exception e) {}

            throw new SqlToolException(FILEERR_EXITVAL, ioe.getMessage());
        }

        try {
            for (int j = 0; j < sqlFiles.length; j++) {
                if (j == interactiveFileIndex) {
                    System.out.print("SqlTool v. " + revnum
                                     + ".                        ");
                }

                sqlFiles[j].execute(conn, coeOverride);
            }
            // Following two Exception types are handled properly inside of
            // SqlFile.  We just need to return an appropriate error status.
        } catch (SqlToolError ste) {
            throw new SqlToolException(SQLTOOLERR_EXITVAL);
        } catch (SQLException se) {
            // SqlTool will only throw an SQLException if it is in
            // "\c false" mode.
            throw new SqlToolException(SQLERR_EXITVAL);
        } finally {
            try {
                conn.close();
            } catch (Exception e) {}
        }

        // Taking file removal out of final block because this is good debug
        // info to keep around if the program aborts.
        if (tmpFile != null && !tmpFile.delete()) {
            System.err.println(conData.url + rb.getString(
                    SqltoolRB.TEMPFILE_REMOVAL_FAIL, tmpFile.toString()));
        }
    }
}