Home | History | Annotate | Download | only in SQLite 
      1 package SQLite;
      2 
      3 import SQLite.*;
      4 import java.io.*;
      5 import 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 
     18 public 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 
    607 class 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