| 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:<P> |
| * |
| * <verb> |
| * java SQLite.Shell [OPTIONS] database [SHELLCMD] |
| * or |
| * java -jar sqlite.jar [OPTIONS] database [SHELLCMD] |
| * </verb> |
| */ |
| |
| 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("<TR>"); |
| for (i = 0; i < args.length; i++) { |
| pw.print("<TH>" + html_quote(cols[i]) + "</TH>"); |
| } |
| pw.println("</TR>"); |
| } |
| if (args.length == 0) { |
| break; |
| } |
| pw.print("<TR>"); |
| for (i = 0; i < args.length; i++) { |
| pw.print("<TD>" + html_quote(args[i]) + "</TD>"); |
| } |
| pw.println("</TR>"); |
| 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; |
| } |
| } |
