/*
* Copyright 2004-2010 H2 Group. Multiple-Licensed under the H2 License,
* Version 1.0, and under the Eclipse Public License, Version 1.0
* (http://h2database.com/html/license.html).
* Initial Developer: H2 Group
*/
package org.h2.tools;
import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.PrintStream;
import java.io.StringReader;
import java.sql.Connection;
import java.sql.DriverManager;
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.Properties;
import org.h2.engine.Constants;
import org.h2.server.web.ConnectionInfo;
import org.h2.util.IOUtils;
import org.h2.util.JdbcUtils;
import org.h2.util.New;
import org.h2.util.ScriptReader;
import org.h2.util.SortedProperties;
import org.h2.util.Tool;
import org.h2.util.Utils;
/**
* Interactive command line tool to access a database using JDBC.
* @h2.resource
*/
public class Shell extends Tool implements Runnable {
private static final int HISTORY_COUNT = 20;
private PrintStream err = System.err;
private InputStream in = System.in;
private BufferedReader reader;
private Connection conn;
private Statement stat;
private boolean listMode;
private int maxColumnSize = 100;
// Windows: '\u00b3';
private char boxVertical = '|';
private ArrayList<String> history = New.arrayList();
private boolean stopHide;
/**
* Options are case sensitive. Supported options are:
* <table>
* <tr><td>[-help] or [-?]</td>
* <td>Print the list of options</td></tr>
* <tr><td>[-url "<url>"]</td>
* <td>The database URL (jdbc:h2:...)</td></tr>
* <tr><td>[-user <user>]</td>
* <td>The user name</td></tr>
* <tr><td>[-password <pwd>]</td>
* <td>The password</td></tr>
* <tr><td>[-driver <class>]</td>
* <td>The JDBC driver class to use (not required in most cases)</td></tr>
* <tr><td>[-sql "<statements>"]</td>
* <td>Execute the SQL statements and exit</td></tr>
* </table>
* If special characters don't work as expected, you may need to use
* -Dfile.encoding=UTF-8 (Mac OS X) or CP850 (Windows).
* @h2.resource
*
* @param args the command line arguments
*/
public static void main(String... args) throws SQLException {
new Shell().runTool(args);
}
/**
* Sets the standard error stream.
*
* @param err the new standard error stream
*/
public void setErr(PrintStream err) {
this.err = err;
}
/**
* Redirects the standard input. By default, System.in is used.
*
* @param in the input stream to use
*/
public void setIn(InputStream in) {
this.in = in;
}
/**
* Redirects the standard input. By default, System.in is used.
*
* @param reader the input stream reader to use
*/
public void setInReader(BufferedReader reader) {
this.reader = reader;
}
/**
* Run the shell tool with the given command line settings.
*
* @param args the command line settings
*/
public void runTool(String... args) throws SQLException {
String url = null;
String user = "";
String password = "";
String sql = null;
for (int i = 0; args != null && i < args.length; i++) {
String arg = args[i];
if (arg.equals("-url")) {
url = args[++i];
} else if (arg.equals("-user")) {
user = args[++i];
} else if (arg.equals("-password")) {
password = args[++i];
} else if (arg.equals("-driver")) {
String driver = args[++i];
Utils.loadUserClass(driver);
} else if (arg.equals("-sql")) {
sql = args[++i];
} else if (arg.equals("-help") || arg.equals("-?")) {
showUsage();
return;
} else {
throwUnsupportedOption(arg);
}
}
if (url != null) {
org.h2.Driver.load();
conn = DriverManager.getConnection(url, user, password);
stat = conn.createStatement();
}
if (sql == null) {
promptLoop();
} else {
ScriptReader r = new ScriptReader(new StringReader(sql));
while (true) {
String s = r.readStatement();
if (s == null) {
break;
}
execute(s);
}
}
}
private void showHelp() {
println("Commands are case insensitive; SQL statements end with ';'");
println("help or ? Display this help");
println("list Toggle result list / stack trace mode");
println("maxwidth Set maximum column width (default is 100)");
println("show List all tables");
println("describe Describe a table");
println("history Show the last 20 statements");
println("quit or exit Close the connection and exit");
println("");
}
private void promptLoop() {
println("");
println("Welcome to H2 Shell " + Constants.getFullVersion());
println("Exit with Ctrl+C");
if (conn != null) {
showHelp();
}
String statement = null;
if (reader == null) {
reader = new BufferedReader(new InputStreamReader(in));
}
while (true) {
try {
if (conn == null) {
connect();
showHelp();
}
if (statement == null) {
print("sql> ");
} else {
print("...> ");
}
String line = readLine();
if (line == null) {
break;
}
String trimmed = line.trim();
if (trimmed.length() == 0) {
continue;
}
boolean end = trimmed.endsWith(";");
if (end) {
line = line.substring(0, line.lastIndexOf(';'));
trimmed = trimmed.substring(0, trimmed.length() - 1);
}
String upper = trimmed.toUpperCase();
if ("EXIT".equals(upper) || "QUIT".equals(upper)) {
break;
} else if ("HELP".equals(upper) || "?".equals(upper)) {
showHelp();
} else if ("LIST".equals(upper)) {
listMode = !listMode;
println("Result list mode is now " + (listMode ? "on" : "off"));
} else if ("HISTORY".equals(upper)) {
for (int i = 0; i < history.size(); i++) {
String s = history.get(i);
s = s.replace('\n', ' ').replace('\r', ' ');
println("#" + (1 + i) + ": " + s);
}
if (history.size() > 0) {
println("To re-run a statement, type the number and press and enter");
} else {
println("No history");
}
} else if (upper.startsWith("DESCRIBE")) {
String tableName = upper.substring("DESCRIBE".length()).trim();
if (tableName.length() == 0) {
println("Usage: describe [<schema name>.]<table name>");
} else {
String schemaName = null;
int dot = tableName.indexOf('.');
if (dot >= 0) {
schemaName = tableName.substring(0, dot);
tableName = tableName.substring(dot + 1);
}
PreparedStatement prep = null;
ResultSet rs = null;
try {
String sql = "SELECT CAST(COLUMN_NAME AS VARCHAR(32)) AS \"Column Name\", " +
"CAST(TYPE_NAME AS VARCHAR(14)) AS \"Type\", " +
"NUMERIC_PRECISION AS \"Precision\", " +
"CAST(IS_NULLABLE AS VARCHAR(8)) AS \"Nullable\", " +
"CAST(COLUMN_DEFAULT AS VARCHAR(20)) AS \"Default\" " +
"FROM INFORMATION_SCHEMA.COLUMNS " +
"WHERE UPPER(TABLE_NAME)=?";
if (schemaName != null) {
sql += " AND UPPER(TABLE_SCHEMA)=?";
}
sql += " ORDER BY ORDINAL_POSITION";
prep = conn.prepareStatement(sql);
prep.setString(1, tableName.toUpperCase());
if (schemaName != null) {
prep.setString(2, schemaName.toUpperCase());
}
rs = prep.executeQuery();
printResult(rs, false);
} catch (SQLException e) {
println("Exception: " + e.toString());
e.printStackTrace(err);
} finally {
JdbcUtils.closeSilently(rs);
JdbcUtils.closeSilently(prep);
}
}
} else if (upper.startsWith("SHOW")) {
ResultSet rs = null;
try {
rs = stat.executeQuery(
"SELECT CAST(TABLE_SCHEMA AS VARCHAR(32)) AS \"Schema\", TABLE_NAME AS \"Table Name\" " +
"FROM INFORMATION_SCHEMA.TABLES ORDER BY TABLE_SCHEMA, TABLE_NAME");
printResult(rs, false);
} catch (SQLException e) {
println("Exception: " + e.toString());
e.printStackTrace(err);
} finally {
JdbcUtils.closeSilently(rs);
}
} else if (upper.startsWith("MAXWIDTH")) {
upper = upper.substring("MAXWIDTH".length()).trim();
try {
maxColumnSize = Integer.parseInt(upper);
} catch (NumberFormatException e) {
println("Usage: maxwidth <integer value>");
}
println("Maximum column width is now " + maxColumnSize);
} else {
boolean addToHistory = true;
if (statement == null) {
if (isNumber(line)) {
int pos = Integer.parseInt(line);
if (pos == 0 || pos > history.size()) {
println("Not found");
} else {
statement = history.get(pos - 1);
addToHistory = false;
println(statement);
end = true;
}
} else {
statement = line;
}
} else {
statement += "\n" + line;
}
if (end) {
if (addToHistory) {
history.add(0, statement);
if (history.size() > HISTORY_COUNT) {
history.remove(HISTORY_COUNT);
}
}
execute(statement);
statement = null;
}
}
} catch (SQLException e) {
println("SQL Exception: " + e.getMessage());
statement = null;
} catch (IOException e) {
println(e.getMessage());
break;
} catch (Exception e) {
println("Exception: " + e.toString());
e.printStackTrace(err);
break;
}
}
if (conn != null) {
try {
conn.close();
println("Connection closed");
} catch (SQLException e) {
println("SQL Exception: " + e.getMessage());
e.printStackTrace(err);
}
}
}
private boolean isNumber(String s) {
if (s.length() == 0) {
return false;
}
for (char c : s.toCharArray()) {
if (!Character.isDigit(c)) {
return false;
}
}
return true;
}
private void connect() throws IOException, SQLException {
String propertiesFileName = IOUtils.getFileInUserHome(Constants.SERVER_PROPERTIES_FILE);
String url = "jdbc:h2:~/test";
String user = "sa";
String driver = null;
try {
Properties prop = SortedProperties.loadProperties(propertiesFileName);
String data = null;
boolean found = false;
for (int i = 0;; i++) {
String d = prop.getProperty(String.valueOf(i));
if (d == null) {
break;
}
found = true;
data = d;
}
if (found) {
ConnectionInfo info = new ConnectionInfo(data);
url = info.url;
user = info.user;
driver = info.driver;
}
} catch (IOException e) {
// ignore
}
println("[Enter] " + url);
print("URL ");
url = readLine(url);
if (driver == null) {
driver = JdbcUtils.getDriver(url);
}
if (driver != null) {
println("[Enter] " + driver);
}
print("Driver ");
driver = readLine(driver);
println("[Enter] " + user);
print("User ");
user = readLine(user);
println("[Enter] Hide");
print("Password ");
String password = readLine();
if (password.length() == 0) {
password = readPassword();
}
conn = JdbcUtils.getConnection(driver, url, user, password);
stat = conn.createStatement();
println("Connected");
}
/**
* Print the string without newline, and flush.
*
* @param s the string to print
*/
protected void print(String s) {
out.print(s);
out.flush();
}
private void println(String s) {
out.println(s);
out.flush();
}
private String readPassword() throws IOException {
try {
Object console = Utils.callStaticMethod("java.lang.System.console");
print("Password ");
char[] password = (char[]) Utils.callMethod(console, "readPassword");
return password == null ? null : new String(password);
} catch (Exception e) {
// ignore, use the default solution
}
Thread passwordHider = new Thread(this);
stopHide = false;
passwordHider.start();
print("Password > ");
String p = readLine();
stopHide = true;
try {
passwordHider.join();
} catch (InterruptedException e) {
// ignore
}
print("\b\b");
return p;
}
/**
* INTERNAL.
* Hides the password by repeatedly printing
* backspace, backspace, >, <.
*/
public void run() {
while (!stopHide) {
print("\b\b><");
try {
Thread.sleep(10);
} catch (InterruptedException e) {
// ignore
}
}
}
private String readLine(String defaultValue) throws IOException {
String s = readLine();
return s.length() == 0 ? defaultValue : s;
}
private String readLine() throws IOException {
String line = reader.readLine();
if (line == null) {
throw new IOException("Aborted");
}
return line;
}
private void execute(String sql) {
long time = System.currentTimeMillis();
boolean result;
try {
result = stat.execute(sql);
} catch (SQLException e) {
println("Error: " + e.toString());
if (listMode) {
e.printStackTrace(err);
}
return;
}
ResultSet rs = null;
try {
if (result) {
rs = stat.getResultSet();
int rowCount = printResult(rs, listMode);
time = System.currentTimeMillis() - time;
println("(" + rowCount + (rowCount == 1 ? " row, " : " rows, ") + time + " ms)");
} else {
int updateCount = stat.getUpdateCount();
time = System.currentTimeMillis() - time;
println("(Update count: " + updateCount + ", " + time + " ms)");
}
} catch (SQLException e) {
println("Error: " + e.toString());
e.printStackTrace(err);
} finally {
JdbcUtils.closeSilently(rs);
}
}
private int printResult(ResultSet rs, boolean asList) throws SQLException {
ResultSetMetaData meta = rs.getMetaData();
int longest = 0;
int len = meta.getColumnCount();
String[] columns = new String[len];
int[] columnSizes = new int[len];
int total = 0;
for (int i = 0; i < len; i++) {
String s = meta.getColumnLabel(i + 1);
int l = s.length();
if (!asList) {
l = Math.max(l, meta.getColumnDisplaySize(i + 1));
l = Math.min(maxColumnSize, l);
}
if (s.length() > l) {
s = s.substring(0, l);
}
columns[i] = s;
columnSizes[i] = l;
longest = Math.max(longest, l);
total += l;
}
StringBuilder buff = new StringBuilder();
if (!asList) {
for (int i = 0; i < len; i++) {
if (i > 0) {
buff.append(boxVertical);
}
String s = columns[i];
buff.append(s);
if (i < len - 1) {
for (int j = s.length(); j < columnSizes[i]; j++) {
buff.append(' ');
}
}
}
println(buff.toString());
}
boolean truncated = false;
int rowCount = 0;
while (rs.next()) {
rowCount++;
buff.setLength(0);
if (asList) {
if (rowCount > 1) {
println("");
}
for (int i = 0; i < len; i++) {
if (i > 0) {
buff.append('\n');
}
String label = columns[i];
buff.append(label);
for (int j = label.length(); j < longest; j++) {
buff.append(' ');
}
buff.append(": ").append(rs.getString(i + 1));
}
} else {
for (int i = 0; i < len; i++) {
if (i > 0) {
buff.append(boxVertical);
}
String s = rs.getString(i + 1);
if (s == null) {
s = "null";
}
int m = columnSizes[i];
// only truncate if more than once column
if (len > 1 && !asList && s.length() > m) {
s = s.substring(0, m);
truncated = true;
}
buff.append(s);
if (i < len - 1) {
for (int j = s.length(); j < m; j++) {
buff.append(' ');
}
}
}
}
println(buff.toString());
}
if (rowCount == 0 && asList) {
for (String label : columns) {
buff.append(label).append('\n');
}
println(buff.toString());
}
if (truncated) {
println("(data is partially truncated)");
}
return rowCount;
}
}