Shell.java revision 417deb1db112103aff04231b6ca79772ff7d3a21 
1package SQLite;
2
3import SQLite.*;
4import java.io.*;
5import java.util.*;
6
7/**
8 * SQLite command line shell. This is a partial reimplementaion
9 * of sqlite/src/shell.c and can be invoked by:<P>
10 *
11 * <verb>
12 *     java SQLite.Shell [OPTIONS] database [SHELLCMD]
13 * or
14 *     java -jar sqlite.jar [OPTIONS] database [SHELLCMD]
15 * </verb>
16 */
17
18public class Shell implements Callback {
19    Database db;
20    boolean echo;
21    int count;
22    int mode;
23    boolean showHeader;
24    String tableName;
25    String sep;
26    String cols[];
27    int colwidth[];
28    String destTable;
29    PrintWriter pw;
30    PrintWriter err;
31
32    static final int MODE_Line = 0;
33    static final int MODE_Column = 1;
34    static final int MODE_List = 2;
35    static final int MODE_Semi = 3;
36    static final int MODE_Html = 4;
37    static final int MODE_Insert = 5;
38    static final int MODE_Insert2 = 6;
39
40    public Shell(PrintWriter pw, PrintWriter err) {
41    this.pw = pw;
42    this.err = err;
43    }
44
45    public Shell(PrintStream ps, PrintStream errs) {
46    pw = new PrintWriter(ps);
47    err = new PrintWriter(errs);
48    }
49
50    protected Object clone() {
51        Shell s = new Shell(this.pw, this.err);
52    s.db = db;
53    s.echo = echo;
54    s.mode = mode;
55    s.count = 0;
56    s.showHeader = showHeader;
57    s.tableName = tableName;
58    s.sep = sep;
59    s.colwidth = colwidth;
60    return s;
61    }
62
63    static public String sql_quote_dbl(String str) {
64    if (str == null) {
65        return "NULL";
66    }
67    int i, single = 0, dbl = 0;
68    for (i = 0; i < str.length(); i++) {
69        if (str.charAt(i) == '\'') {
70        single++;
71        } else if (str.charAt(i) == '"') {
72        dbl++;
73        }
74    }
75    if (dbl == 0) {
76        return "\"" + str + "\"";
77    }
78    StringBuffer sb = new StringBuffer("\"");
79    for (i = 0; i < str.length(); i++) {
80        char c = str.charAt(i);
81        if (c == '"') {
82        sb.append("\"\"");
83        } else {
84        sb.append(c);
85        }
86    }
87    return sb.toString();
88    }
89
90    static public String sql_quote(String str) {
91    if (str == null) {
92        return "NULL";
93    }
94    int i, single = 0, dbl = 0;
95    for (i = 0; i < str.length(); i++) {
96        if (str.charAt(i) == '\'') {
97        single++;
98        } else if (str.charAt(i) == '"') {
99        dbl++;
100        }
101    }
102    if (single == 0) {
103        return "'" + str + "'";
104    }
105    if (dbl == 0) {
106        return "\"" + str + "\"";
107    }
108    StringBuffer sb = new StringBuffer("'");
109    for (i = 0; i < str.length(); i++) {
110        char c = str.charAt(i);
111        if (c == '\'') {
112        sb.append("''");
113        } else {
114        sb.append(c);
115        }
116    }
117    return sb.toString();
118    }
119
120    static String html_quote(String str) {
121    if (str == null) {
122        return "NULL";
123    }
124    StringBuffer sb = new StringBuffer();
125    for (int i = 0; i < str.length(); i++) {
126        char c = str.charAt(i);
127        if (c == '<') {
128        sb.append("&lt;");
129        } else if (c == '>') {
130        sb.append("&gt;");
131        } else if (c == '&') {
132        sb.append("&amp;");
133        } else {
134        int x = c;
135        if (x < 32 || x > 127) {
136            sb.append("&#" + x + ";");
137        } else {
138            sb.append(c);
139        }
140        }
141    }
142    return sb.toString();
143    }
144
145    static boolean is_numeric(String str) {
146    try {
147        Double d = Double.valueOf(str);
148    } catch (java.lang.Exception e) {
149        return false;
150    }
151    return true;
152    }
153
154    void set_table_name(String str) {
155    if (str == null) {
156        tableName = "";
157        return;
158    }
159    tableName = Shell.sql_quote(str);
160    }
161
162    public void columns(String args[]) {
163    cols = args;
164    }
165
166    public void types(String args[]) {
167    /* Empty body to satisfy SQLite.Callback interface. */
168    }
169
170    public boolean newrow(String args[]) {
171    int i;
172    String tname;
173    switch (mode) {
174    case Shell.MODE_Line:
175        if (args.length == 0) {
176        break;
177        }
178        if (count++ > 0) {
179        pw.println("");
180        }
181        for (i = 0; i < args.length; i++) {
182        pw.println(cols[i] + " = " +
183               args[i] == null ? "NULL" : args[i]);
184        }
185        break;
186    case Shell.MODE_Column:
187        String csep = "";
188        if (count++ == 0) {
189        colwidth = new int[args.length];
190        for (i = 0; i < args.length; i++) {
191            int w, n;
192            w = cols[i].length();
193            if (w < 10) {
194            w = 10;
195            }
196            colwidth[i] = w;
197            if (showHeader) {
198            pw.print(csep + cols[i]);
199            csep = " ";
200            }
201        }
202        if (showHeader) {
203            pw.println("");
204        }
205        }
206        if (args.length == 0) {
207        break;
208        }
209        csep = "";
210        for (i = 0; i < args.length; i++) {
211        pw.print(csep + (args[i] == null ? "NULL" : args[i]));
212        csep = " ";
213        }
214        pw.println("");
215        break;
216    case Shell.MODE_Semi:
217    case Shell.MODE_List:
218        if (count++ == 0 && showHeader) {
219        for (i = 0; i < args.length; i++) {
220            pw.print(cols[i] +
221                 (i == args.length - 1 ? "\n" : sep));
222        }
223        }
224        if (args.length == 0) {
225        break;
226        }
227        for (i = 0; i < args.length; i++) {
228        pw.print(args[i] == null ? "NULL" : args[i]);
229        if (mode == Shell.MODE_Semi) {
230            pw.print(";");
231        } else if (i < args.length - 1) {
232            pw.print(sep);
233        }
234        }
235        pw.println("");
236        break;
237    case MODE_Html:
238        if (count++ == 0 && showHeader) {
239        pw.print("<TR>");
240        for (i = 0; i < args.length; i++) {
241            pw.print("<TH>" + html_quote(cols[i]) + "</TH>");
242        }
243        pw.println("</TR>");
244        }
245        if (args.length == 0) {
246        break;
247        }
248        pw.print("<TR>");
249        for (i = 0; i < args.length; i++) {
250        pw.print("<TD>" + html_quote(args[i]) + "</TD>");
251        }
252        pw.println("</TR>");
253        break;
254    case MODE_Insert:
255        if (args.length == 0) {
256        break;
257        }
258        tname = tableName;
259        if (destTable != null) {
260            tname = destTable;
261        }
262        pw.print("INSERT INTO " + tname + " VALUES(");
263        for (i = 0; i < args.length; i++) {
264            String tsep = i > 0 ? "," : "";
265        if (args[i] == null) {
266            pw.print(tsep + "NULL");
267        } else if (is_numeric(args[i])) {
268            pw.print(tsep + args[i]);
269        } else {
270            pw.print(tsep + sql_quote(args[i]));
271        }
272        }
273        pw.println(");");
274        break;
275    case MODE_Insert2:
276        if (args.length == 0) {
277        break;
278        }
279        tname = tableName;
280        if (destTable != null) {
281            tname = destTable;
282        }
283        pw.print("INSERT INTO " + tname + " VALUES(");
284        for (i = 0; i < args.length; i++) {
285            String tsep = i > 0 ? "," : "";
286        pw.print(tsep + args[i]);
287        }
288        pw.println(");");
289        break;
290    }
291    return false;
292    }
293
294    void do_meta(String line) {
295        StringTokenizer st = new StringTokenizer(line.toLowerCase());
296    int n = st.countTokens();
297    if (n <= 0) {
298        return;
299    }
300    String cmd = st.nextToken();
301    String args[] = new String[n - 1];
302    int i = 0;
303    while (st.hasMoreTokens()) {
304        args[i] = st.nextToken();
305        ++i;
306    }
307    if (cmd.compareTo(".dump") == 0) {
308        new DBDump(this, args);
309        return;
310    }
311    if (cmd.compareTo(".echo") == 0) {
312        if (args.length > 0 &&
313        (args[0].startsWith("y") || args[0].startsWith("on"))) {
314        echo = true;
315        }
316        return;
317    }
318    if (cmd.compareTo(".exit") == 0) {
319        try {
320        db.close();
321        } catch (Exception e) {
322        }
323        System.exit(0);
324    }
325    if (cmd.compareTo(".header") == 0) {
326        if (args.length > 0 &&
327        (args[0].startsWith("y") || args[0].startsWith("on"))) {
328        showHeader = true;
329        }
330        return;
331    }
332    if (cmd.compareTo(".help") == 0) {
333        pw.println(".dump ?TABLE? ...  Dump database in text fmt");
334        pw.println(".echo ON|OFF       Command echo on or off");
335        pw.println(".enc ?NAME?        Change encoding");
336        pw.println(".exit              Exit program");
337        pw.println(".header ON|OFF     Display headers on or off");
338        pw.println(".help              This message");
339        pw.println(".mode MODE         Set output mode to\n" +
340               "                   line, column, insert\n" +
341               "                   list, or html");
342        pw.println(".mode insert TABLE Generate SQL insert stmts");
343        pw.println(".schema ?PATTERN?  List table schema");
344        pw.println(".separator STRING  Set separator string");
345        pw.println(".tables ?PATTERN?  List table names");
346        return;
347    }
348    if (cmd.compareTo(".mode") == 0) {
349        if (args.length > 0) {
350        if (args[0].compareTo("line") == 0) {
351            mode = Shell.MODE_Line;
352        } else if (args[0].compareTo("column") == 0) {
353            mode = Shell.MODE_Column;
354        } else if (args[0].compareTo("list") == 0) {
355            mode = Shell.MODE_List;
356        } else if (args[0].compareTo("html") == 0) {
357            mode = Shell.MODE_Html;
358        } else if (args[0].compareTo("insert") == 0) {
359            mode = Shell.MODE_Insert;
360            if (args.length > 1) {
361            destTable = args[1];
362            }
363        }
364        }
365        return;
366    }
367    if (cmd.compareTo(".separator") == 0) {
368        if (args.length > 0) {
369        sep = args[0];
370        }
371        return;
372    }
373    if (cmd.compareTo(".tables") == 0) {
374        TableResult t = null;
375        if (args.length > 0) {
376        try {
377            String qarg[] = new String[1];
378            qarg[0] = args[0];
379            t = db.get_table("SELECT name FROM sqlite_master " +
380                     "WHERE type='table' AND " +
381                     "name LIKE '%%%q%%' " +
382                     "ORDER BY name", qarg);
383        } catch (Exception e) {
384            err.println("SQL Error: " + e);
385            err.flush();
386        }
387        } else {
388        try {
389            t = db.get_table("SELECT name FROM sqlite_master " +
390                     "WHERE type='table' ORDER BY name");
391        } catch (Exception e) {
392            err.println("SQL Error: " + e);
393            err.flush();
394        }
395        }
396        if (t != null) {
397        for (i = 0; i < t.nrows; i++) {
398            String tab = ((String[]) t.rows.elementAt(i))[0];
399            if (tab != null) {
400            pw.println(tab);
401            }
402        }
403        }
404        return;
405    }
406    if (cmd.compareTo(".schema") == 0) {
407        if (args.length > 0) {
408        try {
409            String qarg[] = new String[1];
410            qarg[0] = args[0];
411            db.exec("SELECT sql FROM sqlite_master " +
412                "WHERE type!='meta' AND " +
413                "name LIKE '%%%q%%' AND " +
414                "sql NOTNULL " +
415                "ORDER BY type DESC, name",
416                this, qarg);
417        } catch (Exception e) {
418            err.println("SQL Error: " + e);
419            err.flush();
420        }
421        } else {
422        try {
423            db.exec("SELECT sql FROM sqlite_master " +
424                "WHERE type!='meta' AND " +
425                "sql NOTNULL " +
426                "ORDER BY tbl_name, type DESC, name",
427                this);
428        } catch (Exception e) {
429            err.println("SQL Error: " + e);
430            err.flush();
431        }
432        }
433        return;
434    }
435    if (cmd.compareTo(".enc") == 0) {
436        try {
437        db.set_encoding(args.length > 0 ? args[0] : null);
438        } catch (Exception e) {
439        err.println("" + e);
440        err.flush();
441        }
442        return;
443    }
444    err.println("Unknown command '" + cmd + "'");
445    err.flush();
446    }
447
448    String read_line(BufferedReader is, String prompt) {
449    try {
450        if (prompt != null) {
451        pw.print(prompt);
452        pw.flush();
453        }
454        String line = is.readLine();
455        return line;
456    } catch (IOException e) {
457        return null;
458    }
459    }
460
461    void do_input(BufferedReader is) {
462    String line, sql = null;
463    String prompt = "SQLITE> ";
464    while ((line = read_line(is, prompt)) != null) {
465        if (echo) {
466        pw.println(line);
467        }
468        if (line.length() > 0 && line.charAt(0) == '.') {
469            do_meta(line);
470        } else {
471        if (sql == null) {
472            sql = line;
473        } else {
474            sql = sql + " " + line;
475        }
476        if (Database.complete(sql)) {
477            try {
478            db.exec(sql, this);
479            } catch (Exception e) {
480            if (!echo) {
481                err.println(sql);
482            }
483            err.println("SQL Error: " + e);
484            err.flush();
485            }
486            sql = null;
487            prompt = "SQLITE> ";
488        } else {
489            prompt = "SQLITE? ";
490        }
491        }
492        pw.flush();
493    }
494    if (sql != null) {
495        err.println("Incomplete SQL: " + sql);
496        err.flush();
497    }
498    }
499
500    void do_cmd(String sql) {
501        if (db == null) {
502        return;
503    }
504        if (sql.length() > 0 && sql.charAt(0) == '.') {
505        do_meta(sql);
506    } else {
507        try {
508            db.exec(sql, this);
509        } catch (Exception e) {
510        err.println("SQL Error: " + e);
511        err.flush();
512        }
513    }
514    }
515
516    public static void main(String args[]) {
517    Shell s = new Shell(System.out, System.err);
518    s.mode = Shell.MODE_List;
519    s.sep = "|";
520    s.showHeader = false;
521    s.db = new Database();
522    String dbname = null, sql = null;
523    for (int i = 0; i < args.length; i++) {
524        if(args[i].compareTo("-html") ==0) {
525        s.mode = Shell.MODE_Html;
526        } else if (args[i].compareTo("-list") == 0) {
527        s.mode = Shell.MODE_List;
528        } else if (args[i].compareTo("-line") == 0) {
529        s.mode = Shell.MODE_Line;
530        } else if (i < args.length - 1 &&
531               args[i].compareTo("-separator") == 0) {
532        ++i;
533        s.sep = args[i];
534        } else if (args[i].compareTo("-header") == 0) {
535        s.showHeader = true;
536        } else if (args[i].compareTo("-noheader") == 0) {
537        s.showHeader = false;
538        } else if (args[i].compareTo("-echo") == 0) {
539        s.echo = true;
540        } else if (dbname == null) {
541        dbname = args[i];
542        } else if (sql == null) {
543        sql = args[i];
544        } else {
545        System.err.println("Arguments: ?OPTIONS? FILENAME ?SQL?");
546        System.exit(1);
547        }
548    }
549    if (dbname == null) {
550        System.err.println("No database file given");
551        System.exit(1);
552    }
553    try {
554        s.db.open(dbname, 0);
555    } catch (Exception e) {
556        System.err.println("Unable to open database: " + e);
557        System.exit(1);
558    }
559    if (sql != null) {
560        s.do_cmd(sql);
561    } else {
562        // BEGIN android-modified
563        BufferedReader is =
564            new BufferedReader(new InputStreamReader(System.in), 8192);
565        // END android-modified
566        s.do_input(is);
567    }
568    try {
569        s.db.close();
570    } catch (Exception ee) {
571    }
572    }
573}
574
575/**
576 * Internal class for dumping an entire database.
577 * It contains a special callback interface to traverse the
578 * tables of the current database and output create SQL statements
579 * and for the data insert SQL statements.
580 */
581
582class DBDump implements Callback {
583    Shell s;
584
585    DBDump(Shell s, String tables[]) {
586        this.s = s;
587    s.pw.println("BEGIN TRANSACTION;");
588        if (tables == null || tables.length == 0) {
589        try {
590            s.db.exec("SELECT name, type, sql FROM sqlite_master " +
591              "WHERE type!='meta' AND sql NOT NULL " +
592              "ORDER BY substr(type,2,1), name", this);
593        } catch (Exception e) {
594            s.err.println("SQL Error: " + e);
595        s.err.flush();
596        }
597    } else {
598        String arg[] = new String[1];
599        for (int i = 0; i < tables.length; i++) {
600            arg[0] = tables[i];
601        try {
602            s.db.exec("SELECT name, type, sql FROM sqlite_master " +
603                  "WHERE tbl_name LIKE '%q' AND type!='meta' " +
604                  " AND sql NOT NULL " +
605                  " ORDER BY substr(type,2,1), name",
606                  this, arg);
607        } catch (Exception e) {
608            s.err.println("SQL Error: " + e);
609            s.err.flush();
610        }
611        }
612    }
613    s.pw.println("COMMIT;");
614    }
615
616    public void columns(String col[]) {
617    /* Empty body to satisfy SQLite.Callback interface. */
618    }
619
620    public void types(String args[]) {
621    /* Empty body to satisfy SQLite.Callback interface. */
622    }
623
624    public boolean newrow(String args[]) {
625        if (args.length != 3) {
626        return true;
627    }
628    s.pw.println(args[2] + ";");
629    if (args[1].compareTo("table") == 0) {
630        Shell s2 = (Shell) s.clone();
631        s2.mode = Shell.MODE_Insert;
632        s2.set_table_name(args[0]);
633        String qargs[] = new String[1];
634        qargs[0] = args[0];
635        try {
636            if (s2.db.is3()) {
637            TableResult t = null;
638            t = s2.db.get_table("PRAGMA table_info('%q')", qargs);
639            String query;
640            if (t != null) {
641                StringBuffer sb = new StringBuffer();
642            String sep = "";
643
644            sb.append("SELECT ");
645            for (int i = 0; i < t.nrows; i++) {
646                String col = ((String[]) t.rows.elementAt(i))[1];
647                sb.append(sep + "quote(" +
648                      Shell.sql_quote_dbl(col) + ")");
649                sep = ",";
650            }
651            sb.append(" from '%q'");
652            query = sb.toString();
653            s2.mode = Shell.MODE_Insert2;
654            } else {
655                query = "SELECT * from '%q'";
656            }
657            s2.db.exec(query, s2, qargs);
658        } else {
659            s2.db.exec("SELECT * from '%q'", s2, qargs);
660        }
661        } catch (Exception e) {
662            s.err.println("SQL Error: " + e);
663        s.err.flush();
664        return true;
665        }
666    }
667    return false;
668    }
669}
670