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("<"); 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 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 582 class 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
