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	if (db.is3()) {
160	    tableName = Shell.sql_quote_dbl(str);
161	} else {
162	    tableName = Shell.sql_quote(str);
163	}
164    }
165
166    public void columns(String args[]) {
167	cols = args;
168    }
169
170    public void types(String args[]) {
171	/* Empty body to satisfy SQLite.Callback interface. */
172    }
173
174    public boolean newrow(String args[]) {
175	int i;
176	String tname;
177	switch (mode) {
178	case Shell.MODE_Line:
179	    if (args.length == 0) {
180		break;
181	    }
182	    if (count++ > 0) {
183		pw.println("");
184	    }
185	    for (i = 0; i < args.length; i++) {
186		pw.println(cols[i] + " = " +
187			   args[i] == null ? "NULL" : args[i]);
188	    }
189	    break;
190	case Shell.MODE_Column:
191	    String csep = "";
192	    if (count++ == 0) {
193		colwidth = new int[args.length];
194		for (i = 0; i < args.length; i++) {
195		    int w, n;
196		    w = cols[i].length();
197		    if (w < 10) {
198			w = 10;
199		    }
200		    colwidth[i] = w;
201		    if (showHeader) {
202			pw.print(csep + cols[i]);
203			csep = " ";
204		    }
205		}
206		if (showHeader) {
207		    pw.println("");
208		}
209	    }
210	    if (args.length == 0) {
211		break;
212	    }
213	    csep = "";
214	    for (i = 0; i < args.length; i++) {
215		pw.print(csep + (args[i] == null ? "NULL" : args[i]));
216		csep = " ";
217	    }
218	    pw.println("");
219	    break;
220	case Shell.MODE_Semi:
221	case Shell.MODE_List:
222	    if (count++ == 0 && showHeader) {
223		for (i = 0; i < args.length; i++) {
224		    pw.print(cols[i] +
225			     (i == args.length - 1 ? "\n" : sep));
226		}
227	    }
228	    if (args.length == 0) {
229		break;
230	    }
231	    for (i = 0; i < args.length; i++) {
232		pw.print(args[i] == null ? "NULL" : args[i]);
233		if (mode == Shell.MODE_Semi) {
234		    pw.print(";");
235		} else if (i < args.length - 1) {
236		    pw.print(sep);
237		}
238	    }
239	    pw.println("");
240	    break;
241	case MODE_Html:
242	    if (count++ == 0 && showHeader) {
243		pw.print("<TR>");
244		for (i = 0; i < args.length; i++) {
245		    pw.print("<TH>" + html_quote(cols[i]) + "</TH>");
246		}
247		pw.println("</TR>");
248	    }
249	    if (args.length == 0) {
250		break;
251	    }
252	    pw.print("<TR>");
253	    for (i = 0; i < args.length; i++) {
254		pw.print("<TD>" + html_quote(args[i]) + "</TD>");
255	    }
256	    pw.println("</TR>");
257	    break;
258	case MODE_Insert:
259	    if (args.length == 0) {
260		break;
261	    }
262	    tname = tableName;
263	    if (destTable != null) {
264	        tname = destTable;
265	    }
266	    pw.print("INSERT INTO " + tname + " VALUES(");
267	    for (i = 0; i < args.length; i++) {
268	        String tsep = i > 0 ? "," : "";
269		if (args[i] == null) {
270		    pw.print(tsep + "NULL");
271		} else if (is_numeric(args[i])) {
272		    pw.print(tsep + args[i]);
273		} else {
274		    pw.print(tsep + sql_quote(args[i]));
275		}
276	    }
277	    pw.println(");");
278	    break;
279	case MODE_Insert2:
280	    if (args.length == 0) {
281		break;
282	    }
283	    tname = tableName;
284	    if (destTable != null) {
285	        tname = destTable;
286	    }
287	    pw.print("INSERT INTO " + tname + " VALUES(");
288	    for (i = 0; i < args.length; i++) {
289	        String tsep = i > 0 ? "," : "";
290		pw.print(tsep + args[i]);
291	    }
292	    pw.println(");");
293	    break;
294	}
295	return false;
296    }
297
298    void do_meta(String line) {
299        StringTokenizer st = new StringTokenizer(line.toLowerCase());
300	int n = st.countTokens();
301	if (n <= 0) {
302	    return;
303	}
304	String cmd = st.nextToken();
305	String args[] = new String[n - 1];
306	int i = 0;
307	while (st.hasMoreTokens()) {
308	    args[i] = st.nextToken();
309	    ++i;
310	}
311	if (cmd.compareTo(".dump") == 0) {
312	    new DBDump(this, args);
313	    return;
314	}
315	if (cmd.compareTo(".echo") == 0) {
316	    if (args.length > 0 &&
317		(args[0].startsWith("y") || args[0].startsWith("on"))) {
318		echo = true;
319	    }
320	    return;
321	}
322	if (cmd.compareTo(".exit") == 0) {
323	    try {
324		db.close();
325	    } catch (Exception e) {
326	    }
327	    System.exit(0);
328	}
329	if (cmd.compareTo(".header") == 0) {
330	    if (args.length > 0 &&
331		(args[0].startsWith("y") || args[0].startsWith("on"))) {
332		showHeader = true;
333	    }
334	    return;
335	}
336	if (cmd.compareTo(".help") == 0) {
337	    pw.println(".dump ?TABLE? ...  Dump database in text fmt");
338	    pw.println(".echo ON|OFF       Command echo on or off");
339	    pw.println(".enc ?NAME?        Change encoding");
340	    pw.println(".exit              Exit program");
341	    pw.println(".header ON|OFF     Display headers on or off");
342	    pw.println(".help              This message");
343	    pw.println(".mode MODE         Set output mode to\n" +
344		       "                   line, column, insert\n" +
345		       "                   list, or html");
346	    pw.println(".mode insert TABLE Generate SQL insert stmts");
347	    pw.println(".schema ?PATTERN?  List table schema");
348	    pw.println(".separator STRING  Set separator string");
349	    pw.println(".tables ?PATTERN?  List table names");
350	    return;
351	}
352	if (cmd.compareTo(".mode") == 0) {
353	    if (args.length > 0) {
354		if (args[0].compareTo("line") == 0) {
355		    mode = Shell.MODE_Line;
356		} else if (args[0].compareTo("column") == 0) {
357		    mode = Shell.MODE_Column;
358		} else if (args[0].compareTo("list") == 0) {
359		    mode = Shell.MODE_List;
360		} else if (args[0].compareTo("html") == 0) {
361		    mode = Shell.MODE_Html;
362		} else if (args[0].compareTo("insert") == 0) {
363		    mode = Shell.MODE_Insert;
364		    if (args.length > 1) {
365			destTable = args[1];
366		    }
367		}
368	    }
369	    return;
370	}
371	if (cmd.compareTo(".separator") == 0) {
372	    if (args.length > 0) {
373		sep = args[0];
374	    }
375	    return;
376	}
377	if (cmd.compareTo(".tables") == 0) {
378	    TableResult t = null;
379	    if (args.length > 0) {
380		try {
381		    String qarg[] = new String[1];
382		    qarg[0] = args[0];
383		    t = db.get_table("SELECT name FROM sqlite_master " +
384				     "WHERE type='table' AND " +
385				     "name LIKE '%%%q%%' " +
386				     "ORDER BY name", qarg);
387		} catch (Exception e) {
388		    err.println("SQL Error: " + e);
389		    err.flush();
390		}
391	    } else {
392		try {
393		    t = db.get_table("SELECT name FROM sqlite_master " +
394				     "WHERE type='table' ORDER BY name");
395		} catch (Exception e) {
396		    err.println("SQL Error: " + e);
397		    err.flush();
398		}
399	    }
400	    if (t != null) {
401		for (i = 0; i < t.nrows; i++) {
402		    String tab = ((String[]) t.rows.elementAt(i))[0];
403		    if (tab != null) {
404			pw.println(tab);
405		    }
406		}
407	    }
408	    return;
409	}
410	if (cmd.compareTo(".schema") == 0) {
411	    if (args.length > 0) {
412		try {
413		    String qarg[] = new String[1];
414		    qarg[0] = args[0];
415		    db.exec("SELECT sql FROM sqlite_master " +
416			    "WHERE type!='meta' AND " +
417			    "name LIKE '%%%q%%' AND " +
418			    "sql NOTNULL " +
419			    "ORDER BY type DESC, name",
420			    this, qarg);
421		} catch (Exception e) {
422		    err.println("SQL Error: " + e);
423		    err.flush();
424		}
425	    } else {
426		try {
427		    db.exec("SELECT sql FROM sqlite_master " +
428			    "WHERE type!='meta' AND " +
429			    "sql NOTNULL " +
430			    "ORDER BY tbl_name, type DESC, name",
431			    this);
432		} catch (Exception e) {
433		    err.println("SQL Error: " + e);
434		    err.flush();
435		}
436	    }
437	    return;
438	}
439	if (cmd.compareTo(".enc") == 0) {
440	    try {
441		db.set_encoding(args.length > 0 ? args[0] : null);
442	    } catch (Exception e) {
443		err.println("" + e);
444		err.flush();
445	    }
446	    return;
447	}
448	if (cmd.compareTo(".rekey") == 0) {
449	    try {
450		db.rekey(args.length > 0 ? args[0] : null);
451	    } catch (Exception e) {
452		err.println("" + e);
453		err.flush();
454	    }
455	    return;
456	}
457	err.println("Unknown command '" + cmd + "'");
458	err.flush();
459    }
460
461    String read_line(BufferedReader is, String prompt) {
462	try {
463	    if (prompt != null) {
464		pw.print(prompt);
465		pw.flush();
466	    }
467	    String line = is.readLine();
468	    return line;
469	} catch (IOException e) {
470	    return null;
471	}
472    }
473
474    void do_input(BufferedReader is) {
475	String line, sql = null;
476	String prompt = "SQLITE> ";
477	while ((line = read_line(is, prompt)) != null) {
478	    if (echo) {
479		pw.println(line);
480	    }
481	    if (line.length() > 0 && line.charAt(0) == '.') {
482	        do_meta(line);
483	    } else {
484		if (sql == null) {
485		    sql = line;
486		} else {
487		    sql = sql + " " + line;
488		}
489		if (Database.complete(sql)) {
490		    try {
491			db.exec(sql, this);
492		    } catch (Exception e) {
493			if (!echo) {
494			    err.println(sql);
495			}
496			err.println("SQL Error: " + e);
497			err.flush();
498		    }
499		    sql = null;
500		    prompt = "SQLITE> ";
501		} else {
502		    prompt = "SQLITE? ";
503		}
504	    }
505	    pw.flush();
506	}
507	if (sql != null) {
508	    err.println("Incomplete SQL: " + sql);
509	    err.flush();
510	}
511    }
512
513    void do_cmd(String sql) {
514        if (db == null) {
515	    return;
516	}
517        if (sql.length() > 0 && sql.charAt(0) == '.') {
518	    do_meta(sql);
519	} else {
520	    try {
521	        db.exec(sql, this);
522	    } catch (Exception e) {
523		err.println("SQL Error: " + e);
524		err.flush();
525	    }
526	}
527    }
528
529    public static void main(String args[]) {
530	String key = null;
531	Shell s = new Shell(System.out, System.err);
532	s.mode = Shell.MODE_List;
533	s.sep = "|";
534	s.showHeader = false;
535	s.db = new Database();
536	String dbname = null, sql = null;
537	for (int i = 0; i < args.length; i++) {
538	    if(args[i].compareTo("-html") ==0) {
539		s.mode = Shell.MODE_Html;
540	    } else if (args[i].compareTo("-list") == 0) {
541		s.mode = Shell.MODE_List;
542	    } else if (args[i].compareTo("-line") == 0) {
543		s.mode = Shell.MODE_Line;
544	    } else if (i < args.length - 1 &&
545		       args[i].compareTo("-separator") == 0) {
546		++i;
547		s.sep = args[i];
548	    } else if (args[i].compareTo("-header") == 0) {
549		s.showHeader = true;
550	    } else if (args[i].compareTo("-noheader") == 0) {
551		s.showHeader = false;
552	    } else if (args[i].compareTo("-echo") == 0) {
553		s.echo = true;
554	    } else if (args[i].compareTo("-key") == 0) {
555		++i;
556		key = args[i];
557	    } else if (dbname == null) {
558		dbname = args[i];
559	    } else if (sql == null) {
560		sql = args[i];
561	    } else {
562		System.err.println("Arguments: ?OPTIONS? FILENAME ?SQL?");
563		System.exit(1);
564	    }
565	}
566	if (dbname == null) {
567	    System.err.println("No database file given");
568	    System.exit(1);
569	}
570	try {
571	    s.db.open(dbname, 0);
572	} catch (Exception e) {
573	    System.err.println("Unable to open database: " + e);
574	    System.exit(1);
575	}
576	if (key != null) {
577	    try {
578		s.db.key(key);
579	    } catch (Exception e) {
580		System.err.println("Unable to set key: " + e);
581		System.exit(1);
582	    }
583	}
584	if (sql != null) {
585	    s.do_cmd(sql);
586	    s.pw.flush();
587	} else {
588	    BufferedReader is =
589		new BufferedReader(new InputStreamReader(System.in));
590	    s.do_input(is);
591	    s.pw.flush();
592	}
593	try {
594	    s.db.close();
595	} catch (Exception ee) {
596	}
597    }
598}
599
600/**
601 * Internal class for dumping an entire database.
602 * It contains a special callback interface to traverse the
603 * tables of the current database and output create SQL statements
604 * and for the data insert SQL statements.
605 */
606
607class DBDump implements Callback {
608    Shell s;
609
610    DBDump(Shell s, String tables[]) {
611        this.s = s;
612	s.pw.println("BEGIN TRANSACTION;");
613        if (tables == null || tables.length == 0) {
614	    try {
615	        s.db.exec("SELECT name, type, sql FROM sqlite_master " +
616			  "WHERE type!='meta' AND sql NOT NULL " +
617			  "ORDER BY substr(type,2,1), name", this);
618	    } catch (Exception e) {
619	        s.err.println("SQL Error: " + e);
620		s.err.flush();
621	    }
622	} else {
623	    String arg[] = new String[1];
624	    for (int i = 0; i < tables.length; i++) {
625	        arg[0] = tables[i];
626		try {
627		    s.db.exec("SELECT name, type, sql FROM sqlite_master " +
628			      "WHERE tbl_name LIKE '%q' AND type!='meta' " +
629			      " AND sql NOT NULL " +
630			      " ORDER BY substr(type,2,1), name",
631			      this, arg);
632		} catch (Exception e) {
633		    s.err.println("SQL Error: " + e);
634		    s.err.flush();
635		}
636	    }
637	}
638	s.pw.println("COMMIT;");
639    }
640
641    public void columns(String col[]) {
642	/* Empty body to satisfy SQLite.Callback interface. */
643    }
644
645    public void types(String args[]) {
646	/* Empty body to satisfy SQLite.Callback interface. */
647    }
648
649    public boolean newrow(String args[]) {
650        if (args.length != 3) {
651	    return true;
652	}
653	s.pw.println(args[2] + ";");
654	if (args[1].compareTo("table") == 0) {
655	    Shell s2 = (Shell) s.clone();
656	    s2.mode = Shell.MODE_Insert;
657	    s2.set_table_name(args[0]);
658	    String qargs[] = new String[1];
659	    qargs[0] = args[0];
660	    try {
661	        if (s2.db.is3()) {
662		    TableResult t = null;
663		    t = s2.db.get_table("PRAGMA table_info('%q')", qargs);
664		    String query;
665		    if (t != null) {
666		        StringBuffer sb = new StringBuffer();
667			String sep = "";
668
669			sb.append("SELECT ");
670			for (int i = 0; i < t.nrows; i++) {
671			    String col = ((String[]) t.rows.elementAt(i))[1];
672			    sb.append(sep + "quote(" +
673				      Shell.sql_quote_dbl(col) + ")");
674			    sep = ",";
675			}
676			sb.append(" from '%q'");
677			query = sb.toString();
678			s2.mode = Shell.MODE_Insert2;
679		    } else {
680		        query = "SELECT * from '%q'";
681		    }
682		    s2.db.exec(query, s2, qargs);
683		} else {
684		    s2.db.exec("SELECT * from '%q'", s2, qargs);
685		}
686	    } catch (Exception e) {
687	        s.err.println("SQL Error: " + e);
688		s.err.flush();
689		return true;
690	    }
691	}
692	return false;
693    }
694}
695