sqlfile.java

hsql是很有名的嵌入式数据库

/* Copyright (c) 2001-2005, 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.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.OutputStreamWriter;
import java.io.PrintStream;
import java.io.PrintWriter;
import java.io.StringWriter;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;
import java.util.StringTokenizer;
import java.util.TreeMap;

/* $Id: SqlFile.java,v 1.121 2005/06/30 15:18:26 unsaved Exp $ */

/**
 * Encapsulation of a sql text file like 'myscript.sql'.
 * The ultimate goal is to run the execute() method to feed the SQL
 * commands within the file to a jdbc connection.
 *
 * Some implementation comments and variable names use keywords based
 * on the following definitions.  <UL>
 * <LI> COMMAND = Statement || SpecialCommand || BufferCommand
 * Statement = SQL statement like "SQL Statement;"
 * SpecialCommand =  Special Command like "\x arg..."
 * BufferCommand =  Editing/buffer command like ":s/this/that/"
 *
 * When entering SQL statements, you are always "appending" to the
 * "current" command (not the "buffer", which is a different thing).
 * All you can do to the current command is append new lines to it,
 * execute it, or save it to buffer.
 *
 * In general, the special commands mirror those of Postgresql's psql,
 * but SqlFile handles command editing much different from Postgresql
 * because of Java's lack of support for raw tty I/O.
 * The \p special command, in particular, is very different from psql's.
 * Also, to keep the code simpler, we're sticking to only single-char
 * special commands until we really need more.
 *
 * Buffer commands are uniqueue to SQLFile.  The ":" commands allow
 * you to edit the buffer and to execute the buffer.
 *
 * The command history consists only of SQL Statements (i.e., special
 * commands and editing commands are not stored for later viewing or
 * editing).
 *
 * Most of the Special Commands and Editing Commands are for
 * interactive use only.
 *
 * @version $Revision: 1.121 $
 * @author Blaine Simpson unsaved@users
 */
public class SqlFile {

    private static final int DEFAULT_HISTORY_SIZE = 20;
    private File             file;
    private boolean          interactive;
    private String           primaryPrompt    = "sql> ";
    private String           chunkPrompt      = "raw> ";
    private String           contPrompt       = "  +> ";
    private Connection       curConn          = null;
    private boolean          htmlMode         = false;
    private HashMap          userVars         = null;
    private String[]         statementHistory = null;
    private boolean          chunking         = false;

    /**
     * Private class to "share" a variable among a family of SqlFile
     * instances.
     */
    private static class BooleanBucket {

        private boolean bPriv = false;

        public void set(boolean bIn) {
            bPriv = bIn;
        }

        public boolean get() {
            return bPriv;
        }
    }

    // This is an imperfect solution since when user runs SQL they could
    // be running DDL or a commit or rollback statement.  All we know is,
    // they MAY run some DML that needs to be committed.
    BooleanBucket possiblyUncommitteds = new BooleanBucket();

    // Ascii field separator blanks
    private static final int SEP_LEN = 2;
    private static final String DIVIDER =
        "-----------------------------------------------------------------"
        + "-----------------------------------------------------------------";
    private static final String SPACES =
        "                                                                 "
        + "                                                                 ";
    private static String revnum = null;

    static {
        revnum = "$Revision: 1.121 $".substring("$Revision: ".length(),
                "$Revision: 1.121 $".length() - 2);
    }

    private static String BANNER =
        "(SqlFile processor v. " + revnum + ")\n"
        + "Distribution is permitted under the terms of the HSQLDB license.\n"
        + "(c) 2004-2005 Blaine Simpson and the HSQLDB Development Group.\n\n"
        + "    \\q    to Quit.\n" + "    \\?    lists Special Commands.\n"
        + "    :?    lists Buffer/Editing commands.\n"
        + "    *?    lists PL commands (including alias commands).\n\n"
        + "SPECIAL Commands begin with '\\' and execute when you hit ENTER.\n"
        + "BUFFER Commands begin with ':' and execute when you hit ENTER.\n"
        + "COMMENTS begin with '/*' and end with the very next '*/'.\n"
        + "PROCEDURAL LANGUAGE commands begin with '* ' and end when you hit ENTER.\n"
        + "All other lines comprise SQL Statements.\n"
        + "  SQL Statements are terminated by either a blank line (which moves the\n"
        + "  statement into the buffer without executing) or a line ending with ';'\n"
        + "  (which executes the statement).\n"
        + "  SQL Statements may begin with '/PLVARNAME' and/or contain *{PLVARNAME}s.\n";
    private static final String BUFFER_HELP_TEXT =
        "BUFFER Commands (only \":;\" is available for non-interactive use).\n"
        + "    :?                Help\n"
        + "    :;                Execute current buffer as an SQL Statement\n"
        + "    :a[text]          Enter append mode with a copy of the buffer\n"
        + "    :l                List current contents of buffer\n"
        + "    :s/from/to        Substitute \"to\" for first occurrence of \"from\"\n"
        + "    :s/from/to/[i;g2] Substitute \"to\" for occurrence(s) of \"from\"\n"
        + "                from:  '$'s represent line breaks\n"
        + "                to:    If empty, from's will be deleted (e.g. \":s/x//\").\n"
        + "                       '$'s represent line breaks\n"
        + "                       You can't use ';' in order to execute the SQL (use\n"
        + "                       the ';' switch for this purpose, as explained below).\n"
        + "                /:     Can actually be any character which occurs in\n"
        + "                       neither \"to\" string nor \"from\" string.\n"
        + "                SUBSTITUTION MODE SWITCHES:\n"
        + "                       i:  case Insensitive\n"
        + "                       ;:  execute immediately after substitution\n"
        + "                       g:  Global (substitute ALL occurrences of \"from\" string)\n"
        + "                       2:  Narrows substitution to specified buffer line number\n"
        + "                           (Use any line number in place of '2').\n"
    ;
    private static final String HELP_TEXT = "SPECIAL Commands.\n"
        + "* commands only available for interactive use.\n"
        + "In place of \"3\" below, you can use nothing for the previous command, or\n"
        + "an integer \"X\" to indicate the Xth previous command.\n"
        + "Filter substrings are cases-sensitive!  Use \"SCHEMANAME.\" to narrow schema.\n"
        + "    \\?                   Help\n"
        + "    \\p [line to print]   Print string to stdout\n"
        + "    \\w file/path.sql     Append current buffer to file\n"
        + "    \\i file/path.sql     Include/execute commands from external file\n"
        + "    \\d{tvsiSanu*} [substr]  List objects of specified type:\n"
        + "             Tbls/Views/Seqs/Indexes/SysTbls/Aliases/schemaNames/Users/all\n"
        + "    \\d OBJECTNAME [subs] Describe table or view columns\n"
        + "    \\o [file/path.html]  Tee (or stop teeing) query output to specified file\n"
        + "    \\H                   Toggle HTML output mode\n"
        + "    \\! COMMAND ARGS      Execute external program (no support for stdin)\n"
        + "    \\c [true|false]      Continue upon errors (a.o.t. abort upon error)\n"
        + "    \\a [true|false]      Auto-commit JDBC DML commands\n"
        + "    \\b                   save next result to Binary buffer (no display)\n"
        + "    \\bd file/path.bin    Dump Binary buffer to file\n"
        + "    \\bl file/path.bin    Load file into Binary buffer\n"
        + "    \\bp                  Use ? in next SQL statement to upload Bin. buffer\n"
        + "    \\.                   Enter raw SQL.  End with line containing only \".\"\n"
        + "    \\s                   * Show previous commands (i.e. SQL command history)\n"
        + "    \\-[3]                * reload a command to buffer (for : commands)\n"
        + "    \\-[3];               * reload command and execute (via \":;\")\n"
        + "    \\q [abort message]   Quit (or end input like Ctrl-Z or Ctrl-D)\n"
    ;
    private static final String PL_HELP_TEXT = "PROCEDURAL LANGUAGE Commands.\n"
        + "    *?                            Help\n"
        + "    *                             Expand PL variables from now on.\n"
        + "                                  (this is also implied by all the following).\n"
        + "    * VARNAME = Variable value    Set variable value\n"
        + "    * VARNAME =                   Unset variable\n"
        + "    * VARNAME ~                   Set variable value to the value of the very\n"
        + "                                  next SQL statement executed (see details\n"
        + "                                  at the bottom of this listing).\n"
        + "    * VARNAME _                   Same as * VARNAME _, except the query is\n"
        + "                                  done silently (i.e, no rows to screen)\n"
        + "    * list[value] [VARNAME1...]   List variable(s) (defaults to all)\n"
        + "    * load VARNAME path.txt       Load variable value from text file\n"
        + "    * dump VARNAME path.txt       Dump variable value to text file\n"
        + "    * prepare VARNAME             Use ? in next SQL statement to upload val.\n"
        + "    * foreach VARNAME ([val1...]) Repeat the following PL block with the\n"
        + "                                  variable set to each value in turn.\n"
        + "    * if (logical expr)           Execute following PL block only if expr true\n"
        + "    * while (logical expr)        Repeat following PL block while expr true\n"
        + "    * end foreach|if|while        Ends a PL block\n"
        + "    * break [foreach|if|while|file] Exits a PL block or file early\n"
        + "    * continue [foreach|while]    Exits a PL block iteration early\n\n"
        + "Use PL variables (which you have set) like: *{VARNAME}.\n"
        + "You may use /VARNAME instead iff /VARNAME is the first word of a SQL command.\n"
        + "Use PL variables in logical expressions like: *VARNAME.\n\n"
        + "'* VARNAME ~' or '* VARNAME _' sets the variable value according to the very\n"
        + "next SQL statement (~ will echo the value, _ will do it silently):\n"
        + "    Query:  The value of the first field of the first row returned.\n"
        + "    other:  Return status of the command (for updates this will be\n"
        + "            the number of rows updated).\n"
    ;

    /**
     * Interpret lines of input file as SQL Statements, Comments,
     * Special Commands, and Buffer Commands.
     * Most Special Commands and many Buffer commands are only for
     * interactive use.
     *
     * @param inFile  inFile of null means to read stdin.
     * @param inInteractive  If true, prompts are printed, the interactive
     *                       Special commands are enabled, and
     *                       continueOnError defaults to true.
     */
    public SqlFile(File inFile, boolean inInteractive,
                   HashMap inVars) throws IOException {

        file        = inFile;
        interactive = inInteractive;
        userVars    = inVars;

        try {
            statementHistory =
                new String[interactive ? Integer.parseInt(System.getProperty("sqltool.historyLength"))
                                       : 1];
        } catch (Throwable t) {
            statementHistory = null;
        }

        if (statementHistory == null) {
            statementHistory = new String[DEFAULT_HISTORY_SIZE];
        }

        if (file != null &&!file.canRead()) {
            throw new IOException("Can't read SQL file '" + file + "'");
        }
    }

    /**
     * Constructor for reading stdin instead of a file for commands.
     *
     * @see #SqlFile(File,boolean)
     */
    public SqlFile(boolean inInteractive, HashMap inVars) throws IOException {
        this(null, inInteractive, inVars);
    }

    /**
     * Process all the commands on stdin.
     *
     * @param conn The JDBC connection to use for SQL Commands.
     * @see #execute(Connection,PrintStream,PrintStream,boolean)
     */
    public void execute(Connection conn,
                        Boolean coeOverride)
                        throws IOException, SqlToolError, SQLException {
        execute(conn, System.out, System.err, coeOverride);
    }

    /**
     * Process all the commands on stdin.
     *
     * @param conn The JDBC connection to use for SQL Commands.
     * @see #execute(Connection,PrintStream,PrintStream,boolean)
     */
    public void execute(Connection conn,
                        boolean coeOverride)
                        throws IOException, SqlToolError, SQLException {
        execute(conn, System.out, System.err, new Boolean(coeOverride));
    }

    // So we can tell how to handle quit and break commands.
    public boolean      recursed     = false;
    private String      curCommand   = null;
    private int         curLinenum   = -1;
    private int         curHist      = -1;
    private PrintStream psStd        = null;
    private PrintStream psErr        = null;
    private PrintWriter pwQuery      = null;
    StringBuffer        stringBuffer = new StringBuffer();
    /*
     * This is reset upon each execute() invocation (to true if interactive,
     * false otherwise).
     */
    private boolean             continueOnError = false;
    private static final String DEFAULT_CHARSET = "US-ASCII";
    private BufferedReader      br              = null;
    private String              charset         = null;

    /**
     * Process all the commands in the file (or stdin) associated with
     * "this" object.
     * Run SQL in the file through the given database connection.
     *
     * This is synchronized so that I can use object variables to keep
     * track of current line number, command, connection, i/o streams, etc.
     *
     * Sets encoding character set to that specified with System Property
     * 'sqlfile.charset'.  Defaults to "US-ASCII".
     *
     * @param conn The JDBC connection to use for SQL Commands.
     */
    public synchronized void execute(Connection conn, PrintStream stdIn,
                                     PrintStream errIn,
                                     Boolean coeOverride)
                                     throws IOException, SqlToolError,
                                         SQLException {

        psStd      = stdIn;
        psErr      = errIn;