sqlfile.java

纯Java的数据库

/* 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.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
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.UnsupportedEncodingException;
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.List;
import java.util.Set;
import java.util.HashSet;
import java.util.Map;
import java.util.SortedMap;
import java.util.TreeMap;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.regex.PatternSyntaxException;

/* $Id: SqlFile.java,v 1.170 2008/03/15 13:53:46 fredt 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
 * "immediate" command (not the "buffer", which is a different thing).
 * All you can do to the immediate command is append new lines to it,
 * execute it, or save it to buffer.
 * When you are entering a buffer edit command like ":s/this/that/",
 * your immediate command is the buffer-edit-command.  The buffer
 * is the command string that you are editing.
 * The buffer usually contains either an exact copy of the last command
 * executed or sent to buffer by entering a blank line,
 * but BUFFER commands can change the contents of the 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.
 *
 * Buffer commands are unique to SQLFile.  The ":" commands allow
 * you to edit the buffer and to execute the buffer.
 *
 * \d commands are very poorly supported for Mysql because
 * (a) Mysql lacks most of the most basic JDBC support elements, and
 * the most basic role and schema features, and
 * (b) to access the Mysql data dictionay, one must change the database
 * instance (to do that would require work to restore the original state
 * and could have disastrous effects upon transactions).
 *
 * To make changes to this class less destructive to external callers,
 * the input parameters should be moved to setters (probably JavaBean
 * setters would be best) instead of constructor args and System
 * Properties.
 *
 * The process*() methods, other than processBuffHist() ALWAYS execute
 * on "buffer", and expect it to contain the method specific prefix
 * (if any).
 *
 * @version $Revision: 1.170 $
 * @author Blaine Simpson unsaved@users
 */

public class SqlFile {
    private static final int DEFAULT_HISTORY_SIZE = 40;
    private File             file;
    private boolean          interactive;
    private String           primaryPrompt    = "sql> ";
    private String           rawPrompt      = null;
    private String           contPrompt       = "  +> ";
    private Connection       curConn          = null;
    private boolean          htmlMode         = false;
    private Map              userVars; // Always a non-null map set in cons.
    private List             history          = null;
    private int              rawMode          = RAW_FALSE;
    private String           nullRepToken     = null;
    private String           dsvTargetFile    = null;
    private String           dsvTargetTable   = null;
    private String           dsvConstCols     = null;
    private String           dsvRejectFile    = null;
    private String           dsvRejectReport  = null;
    public static String     LS = System.getProperty("line.separator");
    private int              maxHistoryLength = 1;
    private SqltoolRB        rb               = null;
    private String           magicPrefix      = null;
    // For append editing, this is automatically prefixed to what the
    // user enters.

    private static final int RAW_FALSE = 0; // Raw mode off
    private static final int RAW_EMPTY = 1; // Raw mode on, but no raw input yet
    private static final int RAW_DATA  = 2; // Raw mode on and we have input

    /**
     * N.b. javax.util.regex Optional capture groups (...)? are completely
     * unpredictable wrt whether you get a null capture group vs. no capture.
     * Must always check count!
     */
    private static Pattern   specialPattern =
            Pattern.compile("\\s*\\\\(\\S+)(?:\\s+(.*\\S))?\\s*");
    private static Pattern   plPattern  =
            Pattern.compile("\\s*\\*\\s*(.*\\S)?\\s*");
    private static Pattern   foreachPattern =
            Pattern.compile("\\s*\\*\\s*foreach\\s+(\\S+)\\s*\\(([^)]*)\\)\\s*");
    private static Pattern   ifwhilePattern =
            Pattern.compile("\\s*\\*\\s*\\S+\\s*\\(([^)]*)\\)\\s*");
    private static Pattern   varsetPattern =
            Pattern.compile("\\s*\\*\\s*(\\S+)\\s*([=_~])\\s*(?:(.*\\S)\\s*)?");
    private static Pattern   substitutionPattern =
            Pattern.compile("(\\S)(.+?)\\1(.*?)\\1(.+)?\\s*");
            // Note that this pattern does not include the leading ":s".
    private static Pattern   slashHistoryPattern =
            Pattern.compile("\\s*/([^/]+)/\\s*(\\S.*)?");
    private static Pattern   historyPattern =
            Pattern.compile("\\s*(-?\\d+)?\\s*(\\S.*)?");
            // Note that this pattern does not include the leading ":".
    private static Pattern wincmdPattern = null;

    static {
        if (System.getProperty("os.name").startsWith("Windows")) {
            wincmdPattern = Pattern.compile("([^\"]+)?(\"[^\"]*\")?");
        }
    }
    // This can throw a runtime exception, but since the pattern
    // Strings are constant, one test run of the program will tell
    // if the patterns are good.

    /**
     * Encapsulate updating local variables which depend upon PL variables.
     *
     * Right now this is called whenever the user variable map is changed.
     * It would be more efficient to do it JIT by keeping track of when
     * the vars may be "dirty" by a variable map change, and having all
     * methods that use the settings call a conditional updater, but that
     * is less reliable since there is no way to guarantee that the vars
     * are not used without checking.
     */
    private void updateUserSettings() {
        dsvSkipPrefix = SqlFile.convertEscapes(
                (String) userVars.get("*DSV_SKIP_PREFIX"));
        if (dsvSkipPrefix == null) {
            dsvSkipPrefix = DEFAULT_SKIP_PREFIX;
        }
        dsvSkipCols = (String) userVars.get("*DSV_SKIP_COLS");
        dsvColDelim =
            SqlFile.convertEscapes((String) userVars.get("*DSV_COL_DELIM"));
        if (dsvColDelim == null) {
            dsvColDelim =
                SqlFile.convertEscapes((String) userVars.get("*CSV_COL_DELIM"));
        }
        if (dsvColDelim == null) {
            dsvColDelim = DEFAULT_COL_DELIM;
        }

        dsvRowDelim =
            SqlFile.convertEscapes((String) userVars.get("*DSV_ROW_DELIM"));
        if (dsvRowDelim == null) {
            dsvRowDelim =
                SqlFile.convertEscapes((String) userVars.get("*CSV_ROW_DELIM"));
        }
        if (dsvRowDelim == null) {
            dsvRowDelim = DEFAULT_ROW_DELIM;
        }

        dsvTargetFile = (String) userVars.get("*DSV_TARGET_FILE");
        if (dsvTargetFile == null) {
            dsvTargetFile = (String) userVars.get("*CSV_FILEPATH");
        }
        dsvTargetTable = (String) userVars.get("*DSV_TARGET_TABLE");
        if (dsvTargetTable == null) {
            dsvTargetTable = (String) userVars.get("*CSV_TABLENAME");
            // This just for legacy variable name.
        }

        dsvConstCols = (String) userVars.get("*DSV_CONST_COLS");
        dsvRejectFile = (String) userVars.get("*DSV_REJECT_FILE");
        dsvRejectReport = (String) userVars.get("*DSV_REJECT_REPORT");

        nullRepToken = (String) userVars.get("*NULL_REP_TOKEN");
        if (nullRepToken == null) {
            nullRepToken = (String) userVars.get("*CSV_NULL_REP");
        }
        if (nullRepToken == null) {
            nullRepToken = DEFAULT_NULL_REP;
        }
    }

    /**
     * Private class to "share" a variable among a family of SqlFile
     * instances.
     */
    private static class BooleanBucket {
        BooleanBucket() {}
        private boolean bPriv = false;

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

        public boolean get() {
            return bPriv;
        }
    }

    BooleanBucket possiblyUncommitteds = new BooleanBucket();
    // 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.

    private static final String DIVIDER =
        "-----------------------------------------------------------------"
        + "-----------------------------------------------------------------";
    // Needs to be at least as wide as the widest field or header displayed.
    private static final String SPACES =
        "                                                                 "
        + "                                                                 ";
    // Needs to be at least as wide as the widest field or header displayed.
    private static String revnum = null;

    static {
        revnum = "354";
    }

    private String DSV_OPTIONS_TEXT = null;
    private String D_OPTIONS_TEXT = null;
    private String RAW_LEADIN_MSG = null;

    /**
     * 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.
     * @throws IOException  If can't open specified SQL file.
     */
    public SqlFile(File inFile, boolean inInteractive, Map inVars)
            throws IOException {
        // Set up ResourceBundle first, so that any other errors may be
        // reported with localized messages.
        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;
        }
        rawPrompt = rb.getString(SqltoolRB.RAWMODE_PROMPT) + "> ";
        DSV_OPTIONS_TEXT = rb.getString(SqltoolRB.DSV_OPTIONS);
        D_OPTIONS_TEXT = rb.getString(SqltoolRB.D_OPTIONS);
        RAW_LEADIN_MSG = rb.getString(SqltoolRB.RAW_LEADIN);
        DSV_X_SYNTAX_MSG = rb.getString(SqltoolRB.DSV_X_SYNTAX);
        DSV_M_SYNTAX_MSG = rb.getString(SqltoolRB.DSV_M_SYNTAX);
        nobufferYetString = rb.getString(SqltoolRB.NOBUFFER_YET);

        file        = inFile;
        interactive = inInteractive;
        userVars    = inVars;
        if (userVars == null) {