org.hsqldb.cmdline
Class SqlFile

java.lang.Object
  org.hsqldb.cmdline.SqlFile

public class SqlFile
extends Object

Encapsulation of SQL text and the environment under which it will executed with a JDBC Connection. 'SqlInputStream' would be a more precise name, but the content we are talking about here is what is colloqially known as the contents of "SQL file"s.

The file src/org/hsqldb/sample/SqlFileEmbedder.java in the HSQLDB distribution provides an example for using SqlFile to execute SQL files directly from your own Java classes.

The complexities of passing userVars and macros maps are to facilitate strong scoping (among blocks and nested scripts).

Some implementation comments and variable names use keywords based on the following definitions.

• 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 very differently than Postgresql does, in part 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 dictionary, one must change the database instance (to do that would require work to restore the original state and could have disastrous effects upon transactions).

The process*() methods, other than processBuffHist() ALWAYS execute on "buffer", and expect it to contain the method specific prefix (if any).

The input/output Reader/Stream are generally managed by the caller. An exception is that the input reader may be closed automatically or on demand by the user, since in some cases this class builds the Reader. There is no corresponding functionality for output since the user always has control over that object (which may be null or System.out).

Author:

Blaine Simpson (blaine dot simpson at admc dot com)

See Also:

The SqlTool chapter of the HyperSQL Utilities Guide, SqlFileEmbedder

Field Summary

static String

LS Platform-specific line separator

Constructor Summary

SqlFile(File inputFile)
Convenience wrapper for the SqlFile(File, String) constructor

SqlFile(File inputFile, String encoding)
Convenience wrapper for the SqlFile(File, String, boolean) constructor

SqlFile(File inputFile, String encoding, boolean interactive)
Constructor for non-interactive usage with a SQL file, using the specified encoding and sending normal output to stdout.

SqlFile(Reader reader, String inputStreamLabel, PrintStream psStd, String encoding, boolean interactive, File baseDir)
Instantiate a SqlFile instance for SQL input from 'reader'.

SqlFile(String encoding, boolean interactive)
Constructor for interactive usage with stdin/stdout

Method Summary

void	addMacros(Map<String,org.hsqldb.cmdline.sqltool.Token> newMacros)
void	addUserVars(Map<String,String> newUserVars)
static byte[]	bitCharsToBytes(String hexChars) Just a stub for now.
static boolean	canDisplayType(int i) This method is used to tell SqlFile whether this Sql Type must ALWAYS be loaded to the binary buffer without displaying.
void	closeReader() Close the reader.
static String	convertEscapes(String inString) Translates user-supplied escapes into the traditionaly corresponding corresponding binary characters.
void	dsvSafe(String s) Validate that String is safe to write TO DSV file.
static String	escapeHtml(String s) Escaping rules taken from 'Reserved Characters in HTML table at http://www.w3schools.com/tags/ref_entities.asp
void	execute() Process all the commands from the file or Reader associated with "this" object.
static String	getBanner(Connection c) Returns a String report for the specified JDBC Connection.
Connection	getConnection()
String	getCurrentSchema()
Map<String,org.hsqldb.cmdline.sqltool.Token>	getMacros()
Map<String,String>	getUserVars() Get a reference to the user variable map.
static byte[]	hexCharOctetsToBytes(String hexChars) Convert a String to a byte array by interpreting every 2 characters as an octal byte value.
void	importDsv(String filePath, String skipPrefix) Name is self-explanatory.
static byte[]	loadBinary(File binFile) Binary file load
void	setAutoClose(boolean autoClose) Specify whether the supplied or generated input Reader should automatically be closed by the execute() method.
void	setConnection(Connection jdbcConn)
void	setContinueOnError(boolean continueOnError)
void	setMaxHistoryLength(int maxHistoryLength)
static String	sqlTypeToString(int i) Return a String representation of the specified java.sql.Types type.
static byte[]	streamToBytes(InputStream is) As the name says...
String	streamToString(InputStream isIn, String cs) As the name says...

Methods inherited from class java.lang.Object

equals, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait

Field Detail

LS

public static String LS

Platform-specific line separator

Constructor Detail

SqlFile

public SqlFile(File inputFile)
        throws IOException

Convenience wrapper for the SqlFile(File, String) constructor

Throws:

IOException

See Also:

SqlFile(File, String)

SqlFile

public SqlFile(File inputFile,
               String encoding)
        throws IOException

Convenience wrapper for the SqlFile(File, String, boolean) constructor

Parameters:

encoding - is applied to both the given File and other files read in or written out. Null will use your env+JVM settings.

Throws:

IOException

See Also:

SqlFile(File, String, boolean)

SqlFile

public SqlFile(File inputFile,
               String encoding,
               boolean interactive)
        throws IOException

Constructor for non-interactive usage with a SQL file, using the specified encoding and sending normal output to stdout.

Parameters:

encoding - is applied to the given File and other files read in or written out. Null will use your env+JVM settings.

interactive - If true, prompts are printed, the interactive Special commands are enabled, and continueOnError defaults to true.

Throws:

IOException

See Also:

SqlFile(Reader, String, PrintStream, String, boolean, File)

SqlFile

public SqlFile(String encoding,
               boolean interactive)
        throws IOException

Constructor for interactive usage with stdin/stdout

Parameters:

encoding - is applied to other files read in or written out (but not to stdin or stdout). Null will use your env+JVM settings.

interactive - If true, prompts are printed, the interactive Special commands are enabled, and continueOnError defaults to true.

Throws:

IOException

See Also:

SqlFile(Reader, String, PrintStream, String, boolean, File)

SqlFile

public SqlFile(Reader reader,
               String inputStreamLabel,
               PrintStream psStd,
               String encoding,
               boolean interactive,
               File baseDir)
        throws IOException

Instantiate a SqlFile instance for SQL input from 'reader'. After any needed customization, the SQL can be executed by the execute method.

Most Special Commands and many Buffer commands are only for interactive use.

This program never writes to an error stream (stderr or alternative). All meta messages and error messages are written using the logging facility.

Parameters:

reader - Source for the SQL to be executed. Caller is responsible for setting up encoding. (the 'encoding' parameter will NOT be applied to this reader).

psStd - PrintStream for normal output. If null, normal output will be discarded. Caller is responsible for settingup encoding (the 'encoding' parameter will NOT be applied to this stream).

interactive - If true, prompts are printed, the interactive Special commands are enabled, and continueOnError defaults to true.

Throws:

IOException

See Also:

execute()

Method Detail

setConnection

public void setConnection(Connection jdbcConn)

getConnection

public Connection getConnection()

setContinueOnError

public void setContinueOnError(boolean continueOnError)

setMaxHistoryLength

public void setMaxHistoryLength(int maxHistoryLength)

addMacros

public void addMacros(Map<String,org.hsqldb.cmdline.sqltool.Token> newMacros)

addUserVars

public void addUserVars(Map<String,String> newUserVars)

getUserVars

public Map<String,String> getUserVars()

Get a reference to the user variable map. Since you are getting a reference to the private map used inside this class, update this map with great caution and attention to lifecycle handling of the variable map.

getMacros

public Map<String,org.hsqldb.cmdline.sqltool.Token> getMacros()

setAutoClose

public void setAutoClose(boolean autoClose)

Specify whether the supplied or generated input Reader should automatically be closed by the execute() method.

execute() will close the Reader by default (i.e. 'autoClose' defaults to true). You may want to set this to false if you want to stop execution with \q or similar, then continue using the Reader or underlying Stream.

The caller is always responsible for closing the output object (if any) used by SqlFile.

execute

public void execute()
             throws org.hsqldb.cmdline.SqlToolError,
                    SQLException

Process all the commands from the file or Reader associated with "this" object. SQL commands in the content get executed against the current JDBC data source connection.

Throws:

SQLExceptions - thrown by JDBC driver. Only possible if in "\c false" mode.

org.hsqldb.cmdline.SqlToolError - all other errors. This includes including QuitNow, BreakException, ContinueException for recursive calls only.

SQLException

closeReader

public void closeReader()

Close the reader. The execute method will run this automatically, by default.

getCurrentSchema

public String getCurrentSchema()
                        throws org.hsqldb.cmdline.SqlFile.BadSpecial,
                               org.hsqldb.cmdline.SqlToolError

Throws:

org.hsqldb.cmdline.SqlFile.BadSpecial

org.hsqldb.cmdline.SqlToolError

streamToString

public String streamToString(InputStream isIn,
                             String cs)
                      throws IOException

As the name says... This method always closes the input stream.

Throws:

IOException

streamToBytes

public static byte[] streamToBytes(InputStream is)
                            throws IOException

As the name says...

Throws:

IOException

loadBinary

public static byte[] loadBinary(File binFile)
                         throws IOException

Binary file load

Returns:

The bytes which are the content of the fil

Throws:

IOException

canDisplayType

public static boolean canDisplayType(int i)

This method is used to tell SqlFile whether this Sql Type must ALWAYS be loaded to the binary buffer without displaying.

N.b.: If this returns "true" for a type, then the user can never "see" values for these columns. Therefore, if a type may-or-may-not-be displayable, better to return false here and let the user choose. In general, if there is a toString() operator for this Sql Type then return false, since the JDBC driver should know how to make the value displayable.

See Also:

http://java.sun.com/docs/books/tutorial/jdbc/basics/retrieving.html The table on this page lists the most common SqlTypes, all of which must implement toString(), Types

sqlTypeToString

public static String sqlTypeToString(int i)

Return a String representation of the specified java.sql.Types type.

dsvSafe

public void dsvSafe(String s)
             throws org.hsqldb.cmdline.SqlToolError

Validate that String is safe to write TO DSV file.

Throws:

org.hsqldb.cmdline.SqlToolError - if validation fails.

convertEscapes

public static String convertEscapes(String inString)

Translates user-supplied escapes into the traditionaly corresponding corresponding binary characters. Allowed sequences:

• \0\d+ (an octal digit)
• \[0-9]\d* (a decimal digit)
• \[Xx][0-9]{2} (a hex digit)
• \n Newline (Ctrl-J)
• \r Carriage return (Ctrl-M)
• \t Horizontal tab (Ctrl-I)
• \f Form feed (Ctrl-L)

Java 1.4 String methods will make this into a 1 or 2 line task.

importDsv

public void importDsv(String filePath,
                      String skipPrefix)
               throws org.hsqldb.cmdline.SqlToolError

Name is self-explanatory.

Throws:

org.hsqldb.cmdline.SqlToolError - Would prefer to throw an internal exception, but we want this method to have external visibility.

hexCharOctetsToBytes

public static byte[] hexCharOctetsToBytes(String hexChars)

Convert a String to a byte array by interpreting every 2 characters as an octal byte value.

bitCharsToBytes

public static byte[] bitCharsToBytes(String hexChars)

Just a stub for now.

getBanner

public static String getBanner(Connection c)

Returns a String report for the specified JDBC Connection. For databases with poor JDBC support, you won't get much detail.

escapeHtml

public static String escapeHtml(String s)

Escaping rules taken from 'Reserved Characters in HTML table at http://www.w3schools.com/tags/ref_entities.asp