📄 sqlfile.java
字号:
/* 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.135 2006/07/27 20:04:31 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
* "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 unique 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.
*
* \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.
*
* @version $Revision: 1.135 $
* @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 String csvNullRep = null;
/**
* 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.135 $".substring("$Revision: ".length(),
"$Revision: 1.135 $".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{tvsiSanur*} [substr] List objects of specified type:\n"
+ " (Tbls/Views/Seqs/Indexes/SysTbls/Aliases/schemaNames/Users/Roles/table-like)\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 (opt. exec. w/ \":;\"))\n"
+ " \\x {TABLE|SELECT...} eXport table or query to CSV text file\n"
+ " \\m file/path.csv iMport CSV text file records into a table\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;
private PrintWriter pwCsv = null;
StringBuffer stringBuffer = new StringBuffer();
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -