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 Shell(PrintWriter pw, PrintWriter err)40 public Shell(PrintWriter pw, PrintWriter err) { 41 this.pw = pw; 42 this.err = err; 43 } 44 Shell(PrintStream ps, PrintStream errs)45 public Shell(PrintStream ps, PrintStream errs) { 46 pw = new PrintWriter(ps); 47 err = new PrintWriter(errs); 48 } 49 clone()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 sql_quote_dbl(String str)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 sql_quote(String str)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 html_quote(String str)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("<"); 129 } else if (c == '>') { 130 sb.append(">"); 131 } else if (c == '&') { 132 sb.append("&"); 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 is_numeric(String str)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 set_table_name(String str)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 columns(String args[])166 public void columns(String args[]) { 167 cols = args; 168 } 169 types(String args[])170 public void types(String args[]) { 171 /* Empty body to satisfy SQLite.Callback interface. */ 172 } 173 newrow(String args[])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 do_meta(String line)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 read_line(BufferedReader is, String prompt)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 do_input(BufferedReader is)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 do_cmd(String sql)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 main(String args[])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 DBDump(Shell s, String tables[])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 columns(String col[])641 public void columns(String col[]) { 642 /* Empty body to satisfy SQLite.Callback interface. */ 643 } 644 types(String args[])645 public void types(String args[]) { 646 /* Empty body to satisfy SQLite.Callback interface. */ 647 } 648 newrow(String args[])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