jsqlite.Shell Maven / Gradle / Ivy
package jsqlite;
import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.io.PrintStream;
import java.io.PrintWriter;
import java.util.StringTokenizer;
/**
* SQLite command line shell. This is a partial reimplementation
* of sqlite/src/shell.c and can be invoked by:
*
*
* java jsqlite.Shell [OPTIONS] database [SHELLCMD]
* or
* java -jar sqlite.jar [OPTIONS] database [SHELLCMD]
*
*/
public class Shell implements Callback {
Database db;
boolean echo;
int count;
int mode;
boolean showHeader;
String tableName;
String sep;
String cols[];
int colwidth[];
String destTable;
PrintWriter pw;
PrintWriter err;
static final int MODE_Line = 0;
static final int MODE_Column = 1;
static final int MODE_List = 2;
static final int MODE_Semi = 3;
static final int MODE_Html = 4;
static final int MODE_Insert = 5;
static final int MODE_Insert2 = 6;
public Shell( PrintWriter pw, PrintWriter err ) {
this.pw = pw;
this.err = err;
}
public Shell( PrintStream ps, PrintStream errs ) {
pw = new PrintWriter(ps);
err = new PrintWriter(errs);
}
protected Object clone() {
Shell s = new Shell(this.pw, this.err);
s.db = db;
s.echo = echo;
s.mode = mode;
s.count = 0;
s.showHeader = showHeader;
s.tableName = tableName;
s.sep = sep;
s.colwidth = colwidth;
return s;
}
static public String sql_quote_dbl( String str ) {
if (str == null) {
return "NULL";
}
int i, dbl = 0;
for( i = 0; i < str.length(); i++ ) {
if (str.charAt(i) == '\'') {
} else if (str.charAt(i) == '"') {
dbl++;
}
}
if (dbl == 0) {
return "\"" + str + "\"";
}
StringBuffer sb = new StringBuffer("\"");
for( i = 0; i < str.length(); i++ ) {
char c = str.charAt(i);
if (c == '"') {
sb.append("\"\"");
} else {
sb.append(c);
}
}
return sb.toString();
}
static public String sql_quote( String str ) {
if (str == null) {
return "NULL";
}
int i, single = 0, dbl = 0;
for( i = 0; i < str.length(); i++ ) {
if (str.charAt(i) == '\'') {
single++;
} else if (str.charAt(i) == '"') {
dbl++;
}
}
if (single == 0) {
return "'" + str + "'";
}
if (dbl == 0) {
return "\"" + str + "\"";
}
StringBuffer sb = new StringBuffer("'");
for( i = 0; i < str.length(); i++ ) {
char c = str.charAt(i);
if (c == '\'') {
sb.append("''");
} else {
sb.append(c);
}
}
return sb.toString();
}
static String html_quote( String str ) {
if (str == null) {
return "NULL";
}
StringBuffer sb = new StringBuffer();
for( int i = 0; i < str.length(); i++ ) {
char c = str.charAt(i);
if (c == '<') {
sb.append("<");
} else if (c == '>') {
sb.append(">");
} else if (c == '&') {
sb.append("&");
} else {
int x = c;
if (x < 32 || x > 127) {
sb.append("&#" + x + ";");
} else {
sb.append(c);
}
}
}
return sb.toString();
}
static boolean is_numeric( String str ) {
try {
Double.valueOf(str);
} catch (java.lang.Exception e) {
return false;
}
return true;
}
void set_table_name( String str ) {
if (str == null) {
tableName = "";
return;
}
if (db.is3()) {
tableName = Shell.sql_quote_dbl(str);
} else {
tableName = Shell.sql_quote(str);
}
}
public void columns( String args[] ) {
cols = args;
}
public void types( String args[] ) {
/* Empty body to satisfy jsqlite.Callback interface. */
}
public boolean newrow( String args[] ) {
int i;
String tname;
switch( mode ) {
case Shell.MODE_Line:
if (args.length == 0) {
break;
}
if (count++ > 0) {
pw.println("");
}
for( i = 0; i < args.length; i++ ) {
pw.println(cols[i] + " = " + args[i] == null ? "NULL" : args[i]);
}
break;
case Shell.MODE_Column:
String csep = "";
if (count++ == 0) {
colwidth = new int[args.length];
for( i = 0; i < args.length; i++ ) {
int w;
w = cols[i].length();
if (w < 10) {
w = 10;
}
colwidth[i] = w;
if (showHeader) {
pw.print(csep + cols[i]);
csep = " ";
}
}
if (showHeader) {
pw.println("");
}
}
if (args.length == 0) {
break;
}
csep = "";
for( i = 0; i < args.length; i++ ) {
pw.print(csep + (args[i] == null ? "NULL" : args[i]));
csep = " ";
}
pw.println("");
break;
case Shell.MODE_Semi:
case Shell.MODE_List:
if (count++ == 0 && showHeader) {
for( i = 0; i < args.length; i++ ) {
pw.print(cols[i] + (i == args.length - 1 ? "\n" : sep));
}
}
if (args.length == 0) {
break;
}
for( i = 0; i < args.length; i++ ) {
pw.print(args[i] == null ? "NULL" : args[i]);
if (mode == Shell.MODE_Semi) {
pw.print(";");
} else if (i < args.length - 1) {
pw.print(sep);
}
}
pw.println("");
break;
case MODE_Html:
if (count++ == 0 && showHeader) {
pw.print("
");
for( i = 0; i < args.length; i++ ) {
pw.print("" + html_quote(cols[i]) + " ");
}
pw.println(" ");
}
if (args.length == 0) {
break;
}
pw.print("");
for( i = 0; i < args.length; i++ ) {
pw.print("" + html_quote(args[i]) + " ");
}
pw.println(" ");
break;
case MODE_Insert:
if (args.length == 0) {
break;
}
tname = tableName;
if (destTable != null) {
tname = destTable;
}
pw.print("INSERT INTO " + tname + " VALUES(");
for( i = 0; i < args.length; i++ ) {
String tsep = i > 0 ? "," : "";
if (args[i] == null) {
pw.print(tsep + "NULL");
} else if (is_numeric(args[i])) {
pw.print(tsep + args[i]);
} else {
pw.print(tsep + sql_quote(args[i]));
}
}
pw.println(");");
break;
case MODE_Insert2:
if (args.length == 0) {
break;
}
tname = tableName;
if (destTable != null) {
tname = destTable;
}
pw.print("INSERT INTO " + tname + " VALUES(");
for( i = 0; i < args.length; i++ ) {
String tsep = i > 0 ? "," : "";
pw.print(tsep + args[i]);
}
pw.println(");");
break;
}
return false;
}
void do_meta( String line ) {
StringTokenizer st = new StringTokenizer(line.toLowerCase());
int n = st.countTokens();
if (n <= 0) {
return;
}
String cmd = st.nextToken();
String args[] = new String[n - 1];
int i = 0;
while( st.hasMoreTokens() ) {
args[i] = st.nextToken();
++i;
}
if (cmd.compareTo(".dump") == 0) {
new DBDump(this, args);
return;
}
if (cmd.compareTo(".echo") == 0) {
if (args.length > 0 && (args[0].startsWith("y") || args[0].startsWith("on"))) {
echo = true;
}
return;
}
if (cmd.compareTo(".exit") == 0) {
try {
db.close();
} catch (Exception e) {
}
System.exit(0);
}
if (cmd.compareTo(".header") == 0) {
if (args.length > 0 && (args[0].startsWith("y") || args[0].startsWith("on"))) {
showHeader = true;
}
return;
}
if (cmd.compareTo(".help") == 0) {
pw.println(".dump ?TABLE? ... Dump database in text fmt");
pw.println(".echo ON|OFF Command echo on or off");
pw.println(".enc ?NAME? Change encoding");
pw.println(".exit Exit program");
pw.println(".header ON|OFF Display headers on or off");
pw.println(".help This message");
pw.println(".mode MODE Set output mode to\n" + " line, column, insert\n"
+ " list, or html");
pw.println(".mode insert TABLE Generate SQL insert stmts");
pw.println(".schema ?PATTERN? List table schema");
pw.println(".separator STRING Set separator string");
pw.println(".tables ?PATTERN? List table names");
return;
}
if (cmd.compareTo(".mode") == 0) {
if (args.length > 0) {
if (args[0].compareTo("line") == 0) {
mode = Shell.MODE_Line;
} else if (args[0].compareTo("column") == 0) {
mode = Shell.MODE_Column;
} else if (args[0].compareTo("list") == 0) {
mode = Shell.MODE_List;
} else if (args[0].compareTo("html") == 0) {
mode = Shell.MODE_Html;
} else if (args[0].compareTo("insert") == 0) {
mode = Shell.MODE_Insert;
if (args.length > 1) {
destTable = args[1];
}
}
}
return;
}
if (cmd.compareTo(".separator") == 0) {
if (args.length > 0) {
sep = args[0];
}
return;
}
if (cmd.compareTo(".tables") == 0) {
TableResult t = null;
if (args.length > 0) {
try {
String qarg[] = new String[1];
qarg[0] = args[0];
t = db.get_table("SELECT name FROM sqlite_master " + "WHERE type='table' AND " + "name LIKE '%%%q%%' "
+ "ORDER BY name", qarg);
} catch (Exception e) {
err.println("SQL Error: " + e);
err.flush();
}
} else {
try {
t = db.get_table("SELECT name FROM sqlite_master " + "WHERE type='table' ORDER BY name");
} catch (Exception e) {
err.println("SQL Error: " + e);
err.flush();
}
}
if (t != null) {
for( i = 0; i < t.nrows; i++ ) {
String tab = ((String[]) t.rows.elementAt(i))[0];
if (tab != null) {
pw.println(tab);
}
}
}
return;
}
if (cmd.compareTo(".schema") == 0) {
if (args.length > 0) {
try {
String qarg[] = new String[1];
qarg[0] = args[0];
db.exec("SELECT sql FROM sqlite_master " + "WHERE type!='meta' AND " + "name LIKE '%%%q%%' AND "
+ "sql NOTNULL " + "ORDER BY type DESC, name", this, qarg);
} catch (Exception e) {
err.println("SQL Error: " + e);
err.flush();
}
} else {
try {
db.exec("SELECT sql FROM sqlite_master " + "WHERE type!='meta' AND " + "sql NOTNULL "
+ "ORDER BY tbl_name, type DESC, name", this);
} catch (Exception e) {
err.println("SQL Error: " + e);
err.flush();
}
}
return;
}
if (cmd.compareTo(".enc") == 0) {
try {
db.set_encoding(args.length > 0 ? args[0] : null);
} catch (Exception e) {
err.println("" + e);
err.flush();
}
return;
}
if (cmd.compareTo(".rekey") == 0) {
try {
db.rekey(args.length > 0 ? args[0] : null);
} catch (Exception e) {
err.println("" + e);
err.flush();
}
return;
}
err.println("Unknown command '" + cmd + "'");
err.flush();
}
String read_line( BufferedReader is, String prompt ) {
try {
if (prompt != null) {
pw.print(prompt);
pw.flush();
}
String line = is.readLine();
return line;
} catch (IOException e) {
return null;
}
}
void do_input( BufferedReader is ) {
String line, sql = null;
String prompt = "SQLITE> ";
while( (line = read_line(is, prompt)) != null ) {
if (echo) {
pw.println(line);
}
if (line.length() > 0 && line.charAt(0) == '.') {
do_meta(line);
} else {
if (sql == null) {
sql = line;
} else {
sql = sql + " " + line;
}
if (Database.complete(sql)) {
try {
db.exec(sql, this);
} catch (Exception e) {
if (!echo) {
err.println(sql);
}
err.println("SQL Error: " + e);
err.flush();
}
sql = null;
prompt = "SQLITE> ";
} else {
prompt = "SQLITE? ";
}
}
pw.flush();
}
if (sql != null) {
err.println("Incomplete SQL: " + sql);
err.flush();
}
}
void do_cmd( String sql ) {
if (db == null) {
return;
}
if (sql.length() > 0 && sql.charAt(0) == '.') {
do_meta(sql);
} else {
try {
db.exec(sql, this);
} catch (Exception e) {
err.println("SQL Error: " + e);
err.flush();
}
}
}
public static void main( String args[] ) {
String key = null;
Shell s = new Shell(System.out, System.err);
s.mode = Shell.MODE_List;
s.sep = "|";
s.showHeader = false;
s.db = new Database();
String dbname = null, sql = null;
for( int i = 0; i < args.length; i++ ) {
if (args[i].compareTo("-html") == 0) {
s.mode = Shell.MODE_Html;
} else if (args[i].compareTo("-list") == 0) {
s.mode = Shell.MODE_List;
} else if (args[i].compareTo("-line") == 0) {
s.mode = Shell.MODE_Line;
} else if (i < args.length - 1 && args[i].compareTo("-separator") == 0) {
++i;
s.sep = args[i];
} else if (args[i].compareTo("-header") == 0) {
s.showHeader = true;
} else if (args[i].compareTo("-noheader") == 0) {
s.showHeader = false;
} else if (args[i].compareTo("-echo") == 0) {
s.echo = true;
} else if (args[i].compareTo("-key") == 0) {
++i;
key = args[i];
} else if (dbname == null) {
dbname = args[i];
} else if (sql == null) {
sql = args[i];
} else {
System.err.println("Arguments: ?OPTIONS? FILENAME ?SQL?");
System.exit(1);
}
}
if (dbname == null) {
System.err.println("No database file given");
System.exit(1);
}
try {
s.db.open(dbname, jsqlite.Constants.SQLITE_OPEN_READWRITE | jsqlite.Constants.SQLITE_OPEN_CREATE);
} catch (Exception e) {
System.err.println("Unable to open database: " + e);
System.exit(1);
}
if (key != null) {
try {
s.db.key(key);
} catch (Exception e) {
System.err.println("Unable to set key: " + e);
System.exit(1);
}
}
if (sql != null) {
s.do_cmd(sql);
s.pw.flush();
} else {
BufferedReader is = new BufferedReader(new InputStreamReader(System.in));
s.do_input(is);
s.pw.flush();
}
try {
s.db.close();
} catch (Exception ee) {
}
}
}
/**
* Internal class for dumping an entire database.
* It contains a special callback interface to traverse the
* tables of the current database and output create SQL statements
* and for the data insert SQL statements.
*/
class DBDump implements Callback {
Shell s;
DBDump( Shell s, String tables[] ) {
this.s = s;
s.pw.println("BEGIN TRANSACTION;");
if (tables == null || tables.length == 0) {
try {
s.db.exec("SELECT name, type, sql FROM sqlite_master " + "WHERE type!='meta' AND sql NOT NULL "
+ "ORDER BY substr(type,2,1), name", this);
} catch (Exception e) {
s.err.println("SQL Error: " + e);
s.err.flush();
}
} else {
String arg[] = new String[1];
for( int i = 0; i < tables.length; i++ ) {
arg[0] = tables[i];
try {
s.db.exec("SELECT name, type, sql FROM sqlite_master " + "WHERE tbl_name LIKE '%q' AND type!='meta' "
+ " AND sql NOT NULL " + " ORDER BY substr(type,2,1), name", this, arg);
} catch (Exception e) {
s.err.println("SQL Error: " + e);
s.err.flush();
}
}
}
s.pw.println("COMMIT;");
}
public void columns( String col[] ) {
/* Empty body to satisfy jsqlite.Callback interface. */
}
public void types( String args[] ) {
/* Empty body to satisfy jsqlite.Callback interface. */
}
public boolean newrow( String args[] ) {
if (args.length != 3) {
return true;
}
s.pw.println(args[2] + ";");
if (args[1].compareTo("table") == 0) {
Shell s2 = (Shell) s.clone();
s2.mode = Shell.MODE_Insert;
s2.set_table_name(args[0]);
String qargs[] = new String[1];
qargs[0] = args[0];
try {
if (s2.db.is3()) {
TableResult t = null;
t = s2.db.get_table("PRAGMA table_info('%q')", qargs);
String query;
if (t != null) {
StringBuffer sb = new StringBuffer();
String sep = "";
sb.append("SELECT ");
for( int i = 0; i < t.nrows; i++ ) {
String col = ((String[]) t.rows.elementAt(i))[1];
sb.append(sep + "quote(" + Shell.sql_quote_dbl(col) + ")");
sep = ",";
}
sb.append(" from '%q'");
query = sb.toString();
s2.mode = Shell.MODE_Insert2;
} else {
query = "SELECT * from '%q'";
}
s2.db.exec(query, s2, qargs);
} else {
s2.db.exec("SELECT * from '%q'", s2, qargs);
}
} catch (Exception e) {
s.err.println("SQL Error: " + e);
s.err.flush();
return true;
}
}
return false;
}
}