SQLite.Shell Maven / Gradle / Ivy
package SQLite;
import SQLite.*;
import java.io.*;
import java.util.*;
/**
* SQLite command line shell. This is a partial reimplementaion
* of sqlite/src/shell.c and can be invoked by:
*
*
* java SQLite.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, single = 0, dbl = 0;
for (i = 0; i < str.length(); i++) {
if (str.charAt(i) == '\'') {
single++;
} 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 d = 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 SQLite.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, n;
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, 0);
} 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 SQLite.Callback interface. */
}
public void types(String args[]) {
/* Empty body to satisfy SQLite.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;
}
}