1 /* 2 ** 2001 September 15 3 ** 4 ** The author disclaims copyright to this source code. In place of 5 ** a legal notice, here is a blessing: 6 ** 7 ** May you do good and not evil. 8 ** May you find forgiveness for yourself and forgive others. 9 ** May you share freely, never taking more than you give. 10 ** 11 ************************************************************************* 12 ** This file contains code to implement the "sqlite" command line 13 ** utility for accessing SQLite databases. 14 */ 15 #if (defined(_WIN32) || defined(WIN32)) && !defined(_CRT_SECURE_NO_WARNINGS) 16 /* This needs to come before any includes for MSVC compiler */ 17 #define _CRT_SECURE_NO_WARNINGS 18 #endif 19 20 /* 21 ** Enable large-file support for fopen() and friends on unix. 22 */ 23 #ifndef SQLITE_DISABLE_LFS 24 # define _LARGE_FILE 1 25 # ifndef _FILE_OFFSET_BITS 26 # define _FILE_OFFSET_BITS 64 27 # endif 28 # define _LARGEFILE_SOURCE 1 29 #endif 30 31 #include <stdlib.h> 32 #include <string.h> 33 #include <stdio.h> 34 #include <assert.h> 35 #include "sqlite3.h" 36 #include <ctype.h> 37 #include <stdarg.h> 38 39 #if !defined(_WIN32) && !defined(WIN32) && !defined(__OS2__) 40 # include <signal.h> 41 # if !defined(__RTP__) && !defined(_WRS_KERNEL) 42 # include <pwd.h> 43 # endif 44 # include <unistd.h> 45 # include <sys/types.h> 46 #endif 47 48 #ifdef __OS2__ 49 # include <unistd.h> 50 #endif 51 52 #ifdef HAVE_EDITLINE 53 # include <editline/editline.h> 54 #endif 55 #if defined(HAVE_READLINE) && HAVE_READLINE==1 56 # include <readline/readline.h> 57 # include <readline/history.h> 58 #endif 59 #if !defined(HAVE_EDITLINE) && (!defined(HAVE_READLINE) || HAVE_READLINE!=1) 60 # define readline(p) local_getline(p,stdin) 61 # define add_history(X) 62 # define read_history(X) 63 # define write_history(X) 64 # define stifle_history(X) 65 #endif 66 67 #if defined(_WIN32) || defined(WIN32) 68 # include <io.h> 69 #define isatty(h) _isatty(h) 70 #define access(f,m) _access((f),(m)) 71 #else 72 /* Make sure isatty() has a prototype. 73 */ 74 extern int isatty(int); 75 #endif 76 77 #if defined(_WIN32_WCE) 78 /* Windows CE (arm-wince-mingw32ce-gcc) does not provide isatty() 79 * thus we always assume that we have a console. That can be 80 * overridden with the -batch command line option. 81 */ 82 #define isatty(x) 1 83 #endif 84 85 /* True if the timer is enabled */ 86 static int enableTimer = 0; 87 88 /* ctype macros that work with signed characters */ 89 #define IsSpace(X) isspace((unsigned char)X) 90 #define IsDigit(X) isdigit((unsigned char)X) 91 #define ToLower(X) (char)tolower((unsigned char)X) 92 93 #if !defined(_WIN32) && !defined(WIN32) && !defined(__OS2__) && !defined(__RTP__) && !defined(_WRS_KERNEL) 94 #include <sys/time.h> 95 #include <sys/resource.h> 96 97 /* Saved resource information for the beginning of an operation */ 98 static struct rusage sBegin; 99 100 /* 101 ** Begin timing an operation 102 */ 103 static void beginTimer(void){ 104 if( enableTimer ){ 105 getrusage(RUSAGE_SELF, &sBegin); 106 } 107 } 108 109 /* Return the difference of two time_structs in seconds */ 110 static double timeDiff(struct timeval *pStart, struct timeval *pEnd){ 111 return (pEnd->tv_usec - pStart->tv_usec)*0.000001 + 112 (double)(pEnd->tv_sec - pStart->tv_sec); 113 } 114 115 /* 116 ** Print the timing results. 117 */ 118 static void endTimer(void){ 119 if( enableTimer ){ 120 struct rusage sEnd; 121 getrusage(RUSAGE_SELF, &sEnd); 122 printf("CPU Time: user %f sys %f\n", 123 timeDiff(&sBegin.ru_utime, &sEnd.ru_utime), 124 timeDiff(&sBegin.ru_stime, &sEnd.ru_stime)); 125 } 126 } 127 128 #define BEGIN_TIMER beginTimer() 129 #define END_TIMER endTimer() 130 #define HAS_TIMER 1 131 132 #elif (defined(_WIN32) || defined(WIN32)) 133 134 #include <windows.h> 135 136 /* Saved resource information for the beginning of an operation */ 137 static HANDLE hProcess; 138 static FILETIME ftKernelBegin; 139 static FILETIME ftUserBegin; 140 typedef BOOL (WINAPI *GETPROCTIMES)(HANDLE, LPFILETIME, LPFILETIME, LPFILETIME, LPFILETIME); 141 static GETPROCTIMES getProcessTimesAddr = NULL; 142 143 /* 144 ** Check to see if we have timer support. Return 1 if necessary 145 ** support found (or found previously). 146 */ 147 static int hasTimer(void){ 148 if( getProcessTimesAddr ){ 149 return 1; 150 } else { 151 /* GetProcessTimes() isn't supported in WIN95 and some other Windows versions. 152 ** See if the version we are running on has it, and if it does, save off 153 ** a pointer to it and the current process handle. 154 */ 155 hProcess = GetCurrentProcess(); 156 if( hProcess ){ 157 HINSTANCE hinstLib = LoadLibrary(TEXT("Kernel32.dll")); 158 if( NULL != hinstLib ){ 159 getProcessTimesAddr = (GETPROCTIMES) GetProcAddress(hinstLib, "GetProcessTimes"); 160 if( NULL != getProcessTimesAddr ){ 161 return 1; 162 } 163 FreeLibrary(hinstLib); 164 } 165 } 166 } 167 return 0; 168 } 169 170 /* 171 ** Begin timing an operation 172 */ 173 static void beginTimer(void){ 174 if( enableTimer && getProcessTimesAddr ){ 175 FILETIME ftCreation, ftExit; 176 getProcessTimesAddr(hProcess, &ftCreation, &ftExit, &ftKernelBegin, &ftUserBegin); 177 } 178 } 179 180 /* Return the difference of two FILETIME structs in seconds */ 181 static double timeDiff(FILETIME *pStart, FILETIME *pEnd){ 182 sqlite_int64 i64Start = *((sqlite_int64 *) pStart); 183 sqlite_int64 i64End = *((sqlite_int64 *) pEnd); 184 return (double) ((i64End - i64Start) / 10000000.0); 185 } 186 187 /* 188 ** Print the timing results. 189 */ 190 static void endTimer(void){ 191 if( enableTimer && getProcessTimesAddr){ 192 FILETIME ftCreation, ftExit, ftKernelEnd, ftUserEnd; 193 getProcessTimesAddr(hProcess, &ftCreation, &ftExit, &ftKernelEnd, &ftUserEnd); 194 printf("CPU Time: user %f sys %f\n", 195 timeDiff(&ftUserBegin, &ftUserEnd), 196 timeDiff(&ftKernelBegin, &ftKernelEnd)); 197 } 198 } 199 200 #define BEGIN_TIMER beginTimer() 201 #define END_TIMER endTimer() 202 #define HAS_TIMER hasTimer() 203 204 #else 205 #define BEGIN_TIMER 206 #define END_TIMER 207 #define HAS_TIMER 0 208 #endif 209 210 /* 211 ** Used to prevent warnings about unused parameters 212 */ 213 #define UNUSED_PARAMETER(x) (void)(x) 214 215 /* 216 ** If the following flag is set, then command execution stops 217 ** at an error if we are not interactive. 218 */ 219 static int bail_on_error = 0; 220 221 /* 222 ** Threat stdin as an interactive input if the following variable 223 ** is true. Otherwise, assume stdin is connected to a file or pipe. 224 */ 225 static int stdin_is_interactive = 1; 226 227 /* 228 ** The following is the open SQLite database. We make a pointer 229 ** to this database a static variable so that it can be accessed 230 ** by the SIGINT handler to interrupt database processing. 231 */ 232 static sqlite3 *db = 0; 233 234 /* 235 ** True if an interrupt (Control-C) has been received. 236 */ 237 static volatile int seenInterrupt = 0; 238 239 /* 240 ** This is the name of our program. It is set in main(), used 241 ** in a number of other places, mostly for error messages. 242 */ 243 static char *Argv0; 244 245 /* 246 ** Prompt strings. Initialized in main. Settable with 247 ** .prompt main continue 248 */ 249 static char mainPrompt[20]; /* First line prompt. default: "sqlite> "*/ 250 static char continuePrompt[20]; /* Continuation prompt. default: " ...> " */ 251 252 /* 253 ** Write I/O traces to the following stream. 254 */ 255 #ifdef SQLITE_ENABLE_IOTRACE 256 static FILE *iotrace = 0; 257 #endif 258 259 /* 260 ** This routine works like printf in that its first argument is a 261 ** format string and subsequent arguments are values to be substituted 262 ** in place of % fields. The result of formatting this string 263 ** is written to iotrace. 264 */ 265 #ifdef SQLITE_ENABLE_IOTRACE 266 static void iotracePrintf(const char *zFormat, ...){ 267 va_list ap; 268 char *z; 269 if( iotrace==0 ) return; 270 va_start(ap, zFormat); 271 z = sqlite3_vmprintf(zFormat, ap); 272 va_end(ap); 273 fprintf(iotrace, "%s", z); 274 sqlite3_free(z); 275 } 276 #endif 277 278 279 /* 280 ** Determines if a string is a number of not. 281 */ 282 static int isNumber(const char *z, int *realnum){ 283 if( *z=='-' || *z=='+' ) z++; 284 if( !IsDigit(*z) ){ 285 return 0; 286 } 287 z++; 288 if( realnum ) *realnum = 0; 289 while( IsDigit(*z) ){ z++; } 290 if( *z=='.' ){ 291 z++; 292 if( !IsDigit(*z) ) return 0; 293 while( IsDigit(*z) ){ z++; } 294 if( realnum ) *realnum = 1; 295 } 296 if( *z=='e' || *z=='E' ){ 297 z++; 298 if( *z=='+' || *z=='-' ) z++; 299 if( !IsDigit(*z) ) return 0; 300 while( IsDigit(*z) ){ z++; } 301 if( realnum ) *realnum = 1; 302 } 303 return *z==0; 304 } 305 306 /* 307 ** A global char* and an SQL function to access its current value 308 ** from within an SQL statement. This program used to use the 309 ** sqlite_exec_printf() API to substitue a string into an SQL statement. 310 ** The correct way to do this with sqlite3 is to use the bind API, but 311 ** since the shell is built around the callback paradigm it would be a lot 312 ** of work. Instead just use this hack, which is quite harmless. 313 */ 314 static const char *zShellStatic = 0; 315 static void shellstaticFunc( 316 sqlite3_context *context, 317 int argc, 318 sqlite3_value **argv 319 ){ 320 assert( 0==argc ); 321 assert( zShellStatic ); 322 UNUSED_PARAMETER(argc); 323 UNUSED_PARAMETER(argv); 324 sqlite3_result_text(context, zShellStatic, -1, SQLITE_STATIC); 325 } 326 327 328 /* 329 ** This routine reads a line of text from FILE in, stores 330 ** the text in memory obtained from malloc() and returns a pointer 331 ** to the text. NULL is returned at end of file, or if malloc() 332 ** fails. 333 ** 334 ** The interface is like "readline" but no command-line editing 335 ** is done. 336 */ 337 static char *local_getline(char *zPrompt, FILE *in){ 338 char *zLine; 339 int nLine; 340 int n; 341 int eol; 342 343 if( zPrompt && *zPrompt ){ 344 printf("%s",zPrompt); 345 fflush(stdout); 346 } 347 nLine = 100; 348 zLine = malloc( nLine ); 349 if( zLine==0 ) return 0; 350 n = 0; 351 eol = 0; 352 while( !eol ){ 353 if( n+100>nLine ){ 354 nLine = nLine*2 + 100; 355 zLine = realloc(zLine, nLine); 356 if( zLine==0 ) return 0; 357 } 358 if( fgets(&zLine[n], nLine - n, in)==0 ){ 359 if( n==0 ){ 360 free(zLine); 361 return 0; 362 } 363 zLine[n] = 0; 364 eol = 1; 365 break; 366 } 367 while( zLine[n] ){ n++; } 368 if( n>0 && zLine[n-1]=='\n' ){ 369 n--; 370 if( n>0 && zLine[n-1]=='\r' ) n--; 371 zLine[n] = 0; 372 eol = 1; 373 } 374 } 375 zLine = realloc( zLine, n+1 ); 376 return zLine; 377 } 378 379 /* 380 ** Retrieve a single line of input text. 381 ** 382 ** zPrior is a string of prior text retrieved. If not the empty 383 ** string, then issue a continuation prompt. 384 */ 385 static char *one_input_line(const char *zPrior, FILE *in){ 386 char *zPrompt; 387 char *zResult; 388 if( in!=0 ){ 389 return local_getline(0, in); 390 } 391 if( zPrior && zPrior[0] ){ 392 zPrompt = continuePrompt; 393 }else{ 394 zPrompt = mainPrompt; 395 } 396 zResult = readline(zPrompt); 397 #if defined(HAVE_READLINE) && HAVE_READLINE==1 398 if( zResult && *zResult ) add_history(zResult); 399 #endif 400 return zResult; 401 } 402 403 struct previous_mode_data { 404 int valid; /* Is there legit data in here? */ 405 int mode; 406 int showHeader; 407 int colWidth[100]; 408 }; 409 410 /* 411 ** An pointer to an instance of this structure is passed from 412 ** the main program to the callback. This is used to communicate 413 ** state and mode information. 414 */ 415 struct callback_data { 416 sqlite3 *db; /* The database */ 417 int echoOn; /* True to echo input commands */ 418 int statsOn; /* True to display memory stats before each finalize */ 419 int cnt; /* Number of records displayed so far */ 420 FILE *out; /* Write results here */ 421 int nErr; /* Number of errors seen */ 422 int mode; /* An output mode setting */ 423 int writableSchema; /* True if PRAGMA writable_schema=ON */ 424 int showHeader; /* True to show column names in List or Column mode */ 425 char *zDestTable; /* Name of destination table when MODE_Insert */ 426 char separator[20]; /* Separator character for MODE_List */ 427 int colWidth[100]; /* Requested width of each column when in column mode*/ 428 int actualWidth[100]; /* Actual width of each column */ 429 char nullvalue[20]; /* The text to print when a NULL comes back from 430 ** the database */ 431 struct previous_mode_data explainPrev; 432 /* Holds the mode information just before 433 ** .explain ON */ 434 char outfile[FILENAME_MAX]; /* Filename for *out */ 435 const char *zDbFilename; /* name of the database file */ 436 const char *zVfs; /* Name of VFS to use */ 437 sqlite3_stmt *pStmt; /* Current statement if any. */ 438 FILE *pLog; /* Write log output here */ 439 }; 440 441 /* 442 ** These are the allowed modes. 443 */ 444 #define MODE_Line 0 /* One column per line. Blank line between records */ 445 #define MODE_Column 1 /* One record per line in neat columns */ 446 #define MODE_List 2 /* One record per line with a separator */ 447 #define MODE_Semi 3 /* Same as MODE_List but append ";" to each line */ 448 #define MODE_Html 4 /* Generate an XHTML table */ 449 #define MODE_Insert 5 /* Generate SQL "insert" statements */ 450 #define MODE_Tcl 6 /* Generate ANSI-C or TCL quoted elements */ 451 #define MODE_Csv 7 /* Quote strings, numbers are plain */ 452 #define MODE_Explain 8 /* Like MODE_Column, but do not truncate data */ 453 454 static const char *modeDescr[] = { 455 "line", 456 "column", 457 "list", 458 "semi", 459 "html", 460 "insert", 461 "tcl", 462 "csv", 463 "explain", 464 }; 465 466 /* 467 ** Number of elements in an array 468 */ 469 #define ArraySize(X) (int)(sizeof(X)/sizeof(X[0])) 470 471 /* 472 ** Compute a string length that is limited to what can be stored in 473 ** lower 30 bits of a 32-bit signed integer. 474 */ 475 static int strlen30(const char *z){ 476 const char *z2 = z; 477 while( *z2 ){ z2++; } 478 return 0x3fffffff & (int)(z2 - z); 479 } 480 481 /* 482 ** A callback for the sqlite3_log() interface. 483 */ 484 static void shellLog(void *pArg, int iErrCode, const char *zMsg){ 485 struct callback_data *p = (struct callback_data*)pArg; 486 if( p->pLog==0 ) return; 487 fprintf(p->pLog, "(%d) %s\n", iErrCode, zMsg); 488 fflush(p->pLog); 489 } 490 491 /* 492 ** Output the given string as a hex-encoded blob (eg. X'1234' ) 493 */ 494 static void output_hex_blob(FILE *out, const void *pBlob, int nBlob){ 495 int i; 496 char *zBlob = (char *)pBlob; 497 fprintf(out,"X'"); 498 for(i=0; i<nBlob; i++){ fprintf(out,"%02x",zBlob[i]); } 499 fprintf(out,"'"); 500 } 501 502 /* 503 ** Output the given string as a quoted string using SQL quoting conventions. 504 */ 505 static void output_quoted_string(FILE *out, const char *z){ 506 int i; 507 int nSingle = 0; 508 for(i=0; z[i]; i++){ 509 if( z[i]=='\'' ) nSingle++; 510 } 511 if( nSingle==0 ){ 512 fprintf(out,"'%s'",z); 513 }else{ 514 fprintf(out,"'"); 515 while( *z ){ 516 for(i=0; z[i] && z[i]!='\''; i++){} 517 if( i==0 ){ 518 fprintf(out,"''"); 519 z++; 520 }else if( z[i]=='\'' ){ 521 fprintf(out,"%.*s''",i,z); 522 z += i+1; 523 }else{ 524 fprintf(out,"%s",z); 525 break; 526 } 527 } 528 fprintf(out,"'"); 529 } 530 } 531 532 /* 533 ** Output the given string as a quoted according to C or TCL quoting rules. 534 */ 535 static void output_c_string(FILE *out, const char *z){ 536 unsigned int c; 537 fputc('"', out); 538 while( (c = *(z++))!=0 ){ 539 if( c=='\\' ){ 540 fputc(c, out); 541 fputc(c, out); 542 }else if( c=='\t' ){ 543 fputc('\\', out); 544 fputc('t', out); 545 }else if( c=='\n' ){ 546 fputc('\\', out); 547 fputc('n', out); 548 }else if( c=='\r' ){ 549 fputc('\\', out); 550 fputc('r', out); 551 }else if( !isprint(c) ){ 552 fprintf(out, "\\%03o", c&0xff); 553 }else{ 554 fputc(c, out); 555 } 556 } 557 fputc('"', out); 558 } 559 560 /* 561 ** Output the given string with characters that are special to 562 ** HTML escaped. 563 */ 564 static void output_html_string(FILE *out, const char *z){ 565 int i; 566 while( *z ){ 567 for(i=0; z[i] 568 && z[i]!='<' 569 && z[i]!='&' 570 && z[i]!='>' 571 && z[i]!='\"' 572 && z[i]!='\''; 573 i++){} 574 if( i>0 ){ 575 fprintf(out,"%.*s",i,z); 576 } 577 if( z[i]=='<' ){ 578 fprintf(out,"<"); 579 }else if( z[i]=='&' ){ 580 fprintf(out,"&"); 581 }else if( z[i]=='>' ){ 582 fprintf(out,">"); 583 }else if( z[i]=='\"' ){ 584 fprintf(out,"""); 585 }else if( z[i]=='\'' ){ 586 fprintf(out,"'"); 587 }else{ 588 break; 589 } 590 z += i + 1; 591 } 592 } 593 594 /* 595 ** If a field contains any character identified by a 1 in the following 596 ** array, then the string must be quoted for CSV. 597 */ 598 static const char needCsvQuote[] = { 599 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 600 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 601 1, 0, 1, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 602 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 603 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 604 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 605 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 606 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 607 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 608 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 609 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 610 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 611 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 612 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 613 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 614 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 615 }; 616 617 /* 618 ** Output a single term of CSV. Actually, p->separator is used for 619 ** the separator, which may or may not be a comma. p->nullvalue is 620 ** the null value. Strings are quoted using ANSI-C rules. Numbers 621 ** appear outside of quotes. 622 */ 623 static void output_csv(struct callback_data *p, const char *z, int bSep){ 624 FILE *out = p->out; 625 if( z==0 ){ 626 fprintf(out,"%s",p->nullvalue); 627 }else{ 628 int i; 629 int nSep = strlen30(p->separator); 630 for(i=0; z[i]; i++){ 631 if( needCsvQuote[((unsigned char*)z)[i]] 632 || (z[i]==p->separator[0] && 633 (nSep==1 || memcmp(z, p->separator, nSep)==0)) ){ 634 i = 0; 635 break; 636 } 637 } 638 if( i==0 ){ 639 putc('"', out); 640 for(i=0; z[i]; i++){ 641 if( z[i]=='"' ) putc('"', out); 642 putc(z[i], out); 643 } 644 putc('"', out); 645 }else{ 646 fprintf(out, "%s", z); 647 } 648 } 649 if( bSep ){ 650 fprintf(p->out, "%s", p->separator); 651 } 652 } 653 654 #ifdef SIGINT 655 /* 656 ** This routine runs when the user presses Ctrl-C 657 */ 658 static void interrupt_handler(int NotUsed){ 659 UNUSED_PARAMETER(NotUsed); 660 seenInterrupt = 1; 661 if( db ) sqlite3_interrupt(db); 662 } 663 #endif 664 665 /* 666 ** This is the callback routine that the shell 667 ** invokes for each row of a query result. 668 */ 669 static int shell_callback(void *pArg, int nArg, char **azArg, char **azCol, int *aiType){ 670 int i; 671 struct callback_data *p = (struct callback_data*)pArg; 672 673 switch( p->mode ){ 674 case MODE_Line: { 675 int w = 5; 676 if( azArg==0 ) break; 677 for(i=0; i<nArg; i++){ 678 int len = strlen30(azCol[i] ? azCol[i] : ""); 679 if( len>w ) w = len; 680 } 681 if( p->cnt++>0 ) fprintf(p->out,"\n"); 682 for(i=0; i<nArg; i++){ 683 fprintf(p->out,"%*s = %s\n", w, azCol[i], 684 azArg[i] ? azArg[i] : p->nullvalue); 685 } 686 break; 687 } 688 case MODE_Explain: 689 case MODE_Column: { 690 if( p->cnt++==0 ){ 691 for(i=0; i<nArg; i++){ 692 int w, n; 693 if( i<ArraySize(p->colWidth) ){ 694 w = p->colWidth[i]; 695 }else{ 696 w = 0; 697 } 698 if( w<=0 ){ 699 w = strlen30(azCol[i] ? azCol[i] : ""); 700 if( w<10 ) w = 10; 701 n = strlen30(azArg && azArg[i] ? azArg[i] : p->nullvalue); 702 if( w<n ) w = n; 703 } 704 if( i<ArraySize(p->actualWidth) ){ 705 p->actualWidth[i] = w; 706 } 707 if( p->showHeader ){ 708 fprintf(p->out,"%-*.*s%s",w,w,azCol[i], i==nArg-1 ? "\n": " "); 709 } 710 } 711 if( p->showHeader ){ 712 for(i=0; i<nArg; i++){ 713 int w; 714 if( i<ArraySize(p->actualWidth) ){ 715 w = p->actualWidth[i]; 716 }else{ 717 w = 10; 718 } 719 fprintf(p->out,"%-*.*s%s",w,w,"-----------------------------------" 720 "----------------------------------------------------------", 721 i==nArg-1 ? "\n": " "); 722 } 723 } 724 } 725 if( azArg==0 ) break; 726 for(i=0; i<nArg; i++){ 727 int w; 728 if( i<ArraySize(p->actualWidth) ){ 729 w = p->actualWidth[i]; 730 }else{ 731 w = 10; 732 } 733 if( p->mode==MODE_Explain && azArg[i] && 734 strlen30(azArg[i])>w ){ 735 w = strlen30(azArg[i]); 736 } 737 fprintf(p->out,"%-*.*s%s",w,w, 738 azArg[i] ? azArg[i] : p->nullvalue, i==nArg-1 ? "\n": " "); 739 } 740 break; 741 } 742 case MODE_Semi: 743 case MODE_List: { 744 if( p->cnt++==0 && p->showHeader ){ 745 for(i=0; i<nArg; i++){ 746 fprintf(p->out,"%s%s",azCol[i], i==nArg-1 ? "\n" : p->separator); 747 } 748 } 749 if( azArg==0 ) break; 750 for(i=0; i<nArg; i++){ 751 char *z = azArg[i]; 752 if( z==0 ) z = p->nullvalue; 753 fprintf(p->out, "%s", z); 754 if( i<nArg-1 ){ 755 fprintf(p->out, "%s", p->separator); 756 }else if( p->mode==MODE_Semi ){ 757 fprintf(p->out, ";\n"); 758 }else{ 759 fprintf(p->out, "\n"); 760 } 761 } 762 break; 763 } 764 case MODE_Html: { 765 if( p->cnt++==0 && p->showHeader ){ 766 fprintf(p->out,"<TR>"); 767 for(i=0; i<nArg; i++){ 768 fprintf(p->out,"<TH>"); 769 output_html_string(p->out, azCol[i]); 770 fprintf(p->out,"</TH>\n"); 771 } 772 fprintf(p->out,"</TR>\n"); 773 } 774 if( azArg==0 ) break; 775 fprintf(p->out,"<TR>"); 776 for(i=0; i<nArg; i++){ 777 fprintf(p->out,"<TD>"); 778 output_html_string(p->out, azArg[i] ? azArg[i] : p->nullvalue); 779 fprintf(p->out,"</TD>\n"); 780 } 781 fprintf(p->out,"</TR>\n"); 782 break; 783 } 784 case MODE_Tcl: { 785 if( p->cnt++==0 && p->showHeader ){ 786 for(i=0; i<nArg; i++){ 787 output_c_string(p->out,azCol[i] ? azCol[i] : ""); 788 fprintf(p->out, "%s", p->separator); 789 } 790 fprintf(p->out,"\n"); 791 } 792 if( azArg==0 ) break; 793 for(i=0; i<nArg; i++){ 794 output_c_string(p->out, azArg[i] ? azArg[i] : p->nullvalue); 795 fprintf(p->out, "%s", p->separator); 796 } 797 fprintf(p->out,"\n"); 798 break; 799 } 800 case MODE_Csv: { 801 if( p->cnt++==0 && p->showHeader ){ 802 for(i=0; i<nArg; i++){ 803 output_csv(p, azCol[i] ? azCol[i] : "", i<nArg-1); 804 } 805 fprintf(p->out,"\n"); 806 } 807 if( azArg==0 ) break; 808 for(i=0; i<nArg; i++){ 809 output_csv(p, azArg[i], i<nArg-1); 810 } 811 fprintf(p->out,"\n"); 812 break; 813 } 814 case MODE_Insert: { 815 p->cnt++; 816 if( azArg==0 ) break; 817 fprintf(p->out,"INSERT INTO %s VALUES(",p->zDestTable); 818 for(i=0; i<nArg; i++){ 819 char *zSep = i>0 ? ",": ""; 820 if( (azArg[i]==0) || (aiType && aiType[i]==SQLITE_NULL) ){ 821 fprintf(p->out,"%sNULL",zSep); 822 }else if( aiType && aiType[i]==SQLITE_TEXT ){ 823 if( zSep[0] ) fprintf(p->out,"%s",zSep); 824 output_quoted_string(p->out, azArg[i]); 825 }else if( aiType && (aiType[i]==SQLITE_INTEGER || aiType[i]==SQLITE_FLOAT) ){ 826 fprintf(p->out,"%s%s",zSep, azArg[i]); 827 }else if( aiType && aiType[i]==SQLITE_BLOB && p->pStmt ){ 828 const void *pBlob = sqlite3_column_blob(p->pStmt, i); 829 int nBlob = sqlite3_column_bytes(p->pStmt, i); 830 if( zSep[0] ) fprintf(p->out,"%s",zSep); 831 output_hex_blob(p->out, pBlob, nBlob); 832 }else if( isNumber(azArg[i], 0) ){ 833 fprintf(p->out,"%s%s",zSep, azArg[i]); 834 }else{ 835 if( zSep[0] ) fprintf(p->out,"%s",zSep); 836 output_quoted_string(p->out, azArg[i]); 837 } 838 } 839 fprintf(p->out,");\n"); 840 break; 841 } 842 } 843 return 0; 844 } 845 846 /* 847 ** This is the callback routine that the SQLite library 848 ** invokes for each row of a query result. 849 */ 850 static int callback(void *pArg, int nArg, char **azArg, char **azCol){ 851 /* since we don't have type info, call the shell_callback with a NULL value */ 852 return shell_callback(pArg, nArg, azArg, azCol, NULL); 853 } 854 855 /* 856 ** Set the destination table field of the callback_data structure to 857 ** the name of the table given. Escape any quote characters in the 858 ** table name. 859 */ 860 static void set_table_name(struct callback_data *p, const char *zName){ 861 int i, n; 862 int needQuote; 863 char *z; 864 865 if( p->zDestTable ){ 866 free(p->zDestTable); 867 p->zDestTable = 0; 868 } 869 if( zName==0 ) return; 870 needQuote = !isalpha((unsigned char)*zName) && *zName!='_'; 871 for(i=n=0; zName[i]; i++, n++){ 872 if( !isalnum((unsigned char)zName[i]) && zName[i]!='_' ){ 873 needQuote = 1; 874 if( zName[i]=='\'' ) n++; 875 } 876 } 877 if( needQuote ) n += 2; 878 z = p->zDestTable = malloc( n+1 ); 879 if( z==0 ){ 880 fprintf(stderr,"Error: out of memory\n"); 881 exit(1); 882 } 883 n = 0; 884 if( needQuote ) z[n++] = '\''; 885 for(i=0; zName[i]; i++){ 886 z[n++] = zName[i]; 887 if( zName[i]=='\'' ) z[n++] = '\''; 888 } 889 if( needQuote ) z[n++] = '\''; 890 z[n] = 0; 891 } 892 893 /* zIn is either a pointer to a NULL-terminated string in memory obtained 894 ** from malloc(), or a NULL pointer. The string pointed to by zAppend is 895 ** added to zIn, and the result returned in memory obtained from malloc(). 896 ** zIn, if it was not NULL, is freed. 897 ** 898 ** If the third argument, quote, is not '\0', then it is used as a 899 ** quote character for zAppend. 900 */ 901 static char *appendText(char *zIn, char const *zAppend, char quote){ 902 int len; 903 int i; 904 int nAppend = strlen30(zAppend); 905 int nIn = (zIn?strlen30(zIn):0); 906 907 len = nAppend+nIn+1; 908 if( quote ){ 909 len += 2; 910 for(i=0; i<nAppend; i++){ 911 if( zAppend[i]==quote ) len++; 912 } 913 } 914 915 zIn = (char *)realloc(zIn, len); 916 if( !zIn ){ 917 return 0; 918 } 919 920 if( quote ){ 921 char *zCsr = &zIn[nIn]; 922 *zCsr++ = quote; 923 for(i=0; i<nAppend; i++){ 924 *zCsr++ = zAppend[i]; 925 if( zAppend[i]==quote ) *zCsr++ = quote; 926 } 927 *zCsr++ = quote; 928 *zCsr++ = '\0'; 929 assert( (zCsr-zIn)==len ); 930 }else{ 931 memcpy(&zIn[nIn], zAppend, nAppend); 932 zIn[len-1] = '\0'; 933 } 934 935 return zIn; 936 } 937 938 939 /* 940 ** Execute a query statement that has a single result column. Print 941 ** that result column on a line by itself with a semicolon terminator. 942 ** 943 ** This is used, for example, to show the schema of the database by 944 ** querying the SQLITE_MASTER table. 945 */ 946 static int run_table_dump_query( 947 struct callback_data *p, /* Query context */ 948 const char *zSelect, /* SELECT statement to extract content */ 949 const char *zFirstRow /* Print before first row, if not NULL */ 950 ){ 951 sqlite3_stmt *pSelect; 952 int rc; 953 rc = sqlite3_prepare(p->db, zSelect, -1, &pSelect, 0); 954 if( rc!=SQLITE_OK || !pSelect ){ 955 fprintf(p->out, "/**** ERROR: (%d) %s *****/\n", rc, sqlite3_errmsg(p->db)); 956 p->nErr++; 957 return rc; 958 } 959 rc = sqlite3_step(pSelect); 960 while( rc==SQLITE_ROW ){ 961 if( zFirstRow ){ 962 fprintf(p->out, "%s", zFirstRow); 963 zFirstRow = 0; 964 } 965 fprintf(p->out, "%s;\n", sqlite3_column_text(pSelect, 0)); 966 rc = sqlite3_step(pSelect); 967 } 968 rc = sqlite3_finalize(pSelect); 969 if( rc!=SQLITE_OK ){ 970 fprintf(p->out, "/**** ERROR: (%d) %s *****/\n", rc, sqlite3_errmsg(p->db)); 971 p->nErr++; 972 } 973 return rc; 974 } 975 976 /* 977 ** Allocate space and save off current error string. 978 */ 979 static char *save_err_msg( 980 sqlite3 *db /* Database to query */ 981 ){ 982 int nErrMsg = 1+strlen30(sqlite3_errmsg(db)); 983 char *zErrMsg = sqlite3_malloc(nErrMsg); 984 if( zErrMsg ){ 985 memcpy(zErrMsg, sqlite3_errmsg(db), nErrMsg); 986 } 987 return zErrMsg; 988 } 989 990 /* 991 ** Display memory stats. 992 */ 993 static int display_stats( 994 sqlite3 *db, /* Database to query */ 995 struct callback_data *pArg, /* Pointer to struct callback_data */ 996 int bReset /* True to reset the stats */ 997 ){ 998 int iCur; 999 int iHiwtr; 1000 1001 if( pArg && pArg->out ){ 1002 1003 iHiwtr = iCur = -1; 1004 sqlite3_status(SQLITE_STATUS_MEMORY_USED, &iCur, &iHiwtr, bReset); 1005 fprintf(pArg->out, "Memory Used: %d (max %d) bytes\n", iCur, iHiwtr); 1006 iHiwtr = iCur = -1; 1007 sqlite3_status(SQLITE_STATUS_MALLOC_COUNT, &iCur, &iHiwtr, bReset); 1008 fprintf(pArg->out, "Number of Outstanding Allocations: %d (max %d)\n", iCur, iHiwtr); 1009 /* 1010 ** Not currently used by the CLI. 1011 ** iHiwtr = iCur = -1; 1012 ** sqlite3_status(SQLITE_STATUS_PAGECACHE_USED, &iCur, &iHiwtr, bReset); 1013 ** fprintf(pArg->out, "Number of Pcache Pages Used: %d (max %d) pages\n", iCur, iHiwtr); 1014 */ 1015 iHiwtr = iCur = -1; 1016 sqlite3_status(SQLITE_STATUS_PAGECACHE_OVERFLOW, &iCur, &iHiwtr, bReset); 1017 fprintf(pArg->out, "Number of Pcache Overflow Bytes: %d (max %d) bytes\n", iCur, iHiwtr); 1018 /* 1019 ** Not currently used by the CLI. 1020 ** iHiwtr = iCur = -1; 1021 ** sqlite3_status(SQLITE_STATUS_SCRATCH_USED, &iCur, &iHiwtr, bReset); 1022 ** fprintf(pArg->out, "Number of Scratch Allocations Used: %d (max %d)\n", iCur, iHiwtr); 1023 */ 1024 iHiwtr = iCur = -1; 1025 sqlite3_status(SQLITE_STATUS_SCRATCH_OVERFLOW, &iCur, &iHiwtr, bReset); 1026 fprintf(pArg->out, "Number of Scratch Overflow Bytes: %d (max %d) bytes\n", iCur, iHiwtr); 1027 iHiwtr = iCur = -1; 1028 sqlite3_status(SQLITE_STATUS_MALLOC_SIZE, &iCur, &iHiwtr, bReset); 1029 fprintf(pArg->out, "Largest Allocation: %d bytes\n", iHiwtr); 1030 iHiwtr = iCur = -1; 1031 sqlite3_status(SQLITE_STATUS_PAGECACHE_SIZE, &iCur, &iHiwtr, bReset); 1032 fprintf(pArg->out, "Largest Pcache Allocation: %d bytes\n", iHiwtr); 1033 iHiwtr = iCur = -1; 1034 sqlite3_status(SQLITE_STATUS_SCRATCH_SIZE, &iCur, &iHiwtr, bReset); 1035 fprintf(pArg->out, "Largest Scratch Allocation: %d bytes\n", iHiwtr); 1036 #ifdef YYTRACKMAXSTACKDEPTH 1037 iHiwtr = iCur = -1; 1038 sqlite3_status(SQLITE_STATUS_PARSER_STACK, &iCur, &iHiwtr, bReset); 1039 fprintf(pArg->out, "Deepest Parser Stack: %d (max %d)\n", iCur, iHiwtr); 1040 #endif 1041 } 1042 1043 if( pArg && pArg->out && db ){ 1044 iHiwtr = iCur = -1; 1045 sqlite3_db_status(db, SQLITE_DBSTATUS_LOOKASIDE_USED, &iCur, &iHiwtr, bReset); 1046 fprintf(pArg->out, "Lookaside Slots Used: %d (max %d)\n", iCur, iHiwtr); 1047 sqlite3_db_status(db, SQLITE_DBSTATUS_LOOKASIDE_HIT, &iCur, &iHiwtr, bReset); 1048 fprintf(pArg->out, "Successful lookaside attempts: %d\n", iHiwtr); 1049 sqlite3_db_status(db, SQLITE_DBSTATUS_LOOKASIDE_MISS_SIZE, &iCur, &iHiwtr, bReset); 1050 fprintf(pArg->out, "Lookaside failures due to size: %d\n", iHiwtr); 1051 sqlite3_db_status(db, SQLITE_DBSTATUS_LOOKASIDE_MISS_FULL, &iCur, &iHiwtr, bReset); 1052 fprintf(pArg->out, "Lookaside failures due to OOM: %d\n", iHiwtr); 1053 iHiwtr = iCur = -1; 1054 sqlite3_db_status(db, SQLITE_DBSTATUS_CACHE_USED, &iCur, &iHiwtr, bReset); 1055 fprintf(pArg->out, "Pager Heap Usage: %d bytes\n", iCur); iHiwtr = iCur = -1; 1056 sqlite3_db_status(db, SQLITE_DBSTATUS_CACHE_HIT, &iCur, &iHiwtr, 1); 1057 fprintf(pArg->out, "Page cache hits: %d\n", iCur); 1058 iHiwtr = iCur = -1; 1059 sqlite3_db_status(db, SQLITE_DBSTATUS_CACHE_MISS, &iCur, &iHiwtr, 1); 1060 fprintf(pArg->out, "Page cache misses: %d\n", iCur); 1061 iHiwtr = iCur = -1; 1062 sqlite3_db_status(db, SQLITE_DBSTATUS_SCHEMA_USED, &iCur, &iHiwtr, bReset); 1063 fprintf(pArg->out, "Schema Heap Usage: %d bytes\n", iCur); 1064 iHiwtr = iCur = -1; 1065 sqlite3_db_status(db, SQLITE_DBSTATUS_STMT_USED, &iCur, &iHiwtr, bReset); 1066 fprintf(pArg->out, "Statement Heap/Lookaside Usage: %d bytes\n", iCur); 1067 } 1068 1069 if( pArg && pArg->out && db && pArg->pStmt ){ 1070 iCur = sqlite3_stmt_status(pArg->pStmt, SQLITE_STMTSTATUS_FULLSCAN_STEP, bReset); 1071 fprintf(pArg->out, "Fullscan Steps: %d\n", iCur); 1072 iCur = sqlite3_stmt_status(pArg->pStmt, SQLITE_STMTSTATUS_SORT, bReset); 1073 fprintf(pArg->out, "Sort Operations: %d\n", iCur); 1074 iCur = sqlite3_stmt_status(pArg->pStmt, SQLITE_STMTSTATUS_AUTOINDEX, bReset); 1075 fprintf(pArg->out, "Autoindex Inserts: %d\n", iCur); 1076 } 1077 1078 return 0; 1079 } 1080 1081 /* 1082 ** Execute a statement or set of statements. Print 1083 ** any result rows/columns depending on the current mode 1084 ** set via the supplied callback. 1085 ** 1086 ** This is very similar to SQLite's built-in sqlite3_exec() 1087 ** function except it takes a slightly different callback 1088 ** and callback data argument. 1089 */ 1090 static int shell_exec( 1091 sqlite3 *db, /* An open database */ 1092 const char *zSql, /* SQL to be evaluated */ 1093 int (*xCallback)(void*,int,char**,char**,int*), /* Callback function */ 1094 /* (not the same as sqlite3_exec) */ 1095 struct callback_data *pArg, /* Pointer to struct callback_data */ 1096 char **pzErrMsg /* Error msg written here */ 1097 ){ 1098 sqlite3_stmt *pStmt = NULL; /* Statement to execute. */ 1099 int rc = SQLITE_OK; /* Return Code */ 1100 const char *zLeftover; /* Tail of unprocessed SQL */ 1101 1102 if( pzErrMsg ){ 1103 *pzErrMsg = NULL; 1104 } 1105 1106 while( zSql[0] && (SQLITE_OK == rc) ){ 1107 rc = sqlite3_prepare_v2(db, zSql, -1, &pStmt, &zLeftover); 1108 if( SQLITE_OK != rc ){ 1109 if( pzErrMsg ){ 1110 *pzErrMsg = save_err_msg(db); 1111 } 1112 }else{ 1113 if( !pStmt ){ 1114 /* this happens for a comment or white-space */ 1115 zSql = zLeftover; 1116 while( IsSpace(zSql[0]) ) zSql++; 1117 continue; 1118 } 1119 1120 /* save off the prepared statment handle and reset row count */ 1121 if( pArg ){ 1122 pArg->pStmt = pStmt; 1123 pArg->cnt = 0; 1124 } 1125 1126 /* echo the sql statement if echo on */ 1127 if( pArg && pArg->echoOn ){ 1128 const char *zStmtSql = sqlite3_sql(pStmt); 1129 fprintf(pArg->out, "%s\n", zStmtSql ? zStmtSql : zSql); 1130 } 1131 1132 /* perform the first step. this will tell us if we 1133 ** have a result set or not and how wide it is. 1134 */ 1135 rc = sqlite3_step(pStmt); 1136 /* if we have a result set... */ 1137 if( SQLITE_ROW == rc ){ 1138 /* if we have a callback... */ 1139 if( xCallback ){ 1140 /* allocate space for col name ptr, value ptr, and type */ 1141 int nCol = sqlite3_column_count(pStmt); 1142 void *pData = sqlite3_malloc(3*nCol*sizeof(const char*) + 1); 1143 if( !pData ){ 1144 rc = SQLITE_NOMEM; 1145 }else{ 1146 char **azCols = (char **)pData; /* Names of result columns */ 1147 char **azVals = &azCols[nCol]; /* Results */ 1148 int *aiTypes = (int *)&azVals[nCol]; /* Result types */ 1149 int i; 1150 assert(sizeof(int) <= sizeof(char *)); 1151 /* save off ptrs to column names */ 1152 for(i=0; i<nCol; i++){ 1153 azCols[i] = (char *)sqlite3_column_name(pStmt, i); 1154 } 1155 do{ 1156 /* extract the data and data types */ 1157 for(i=0; i<nCol; i++){ 1158 azVals[i] = (char *)sqlite3_column_text(pStmt, i); 1159 aiTypes[i] = sqlite3_column_type(pStmt, i); 1160 if( !azVals[i] && (aiTypes[i]!=SQLITE_NULL) ){ 1161 rc = SQLITE_NOMEM; 1162 break; /* from for */ 1163 } 1164 } /* end for */ 1165 1166 /* if data and types extracted successfully... */ 1167 if( SQLITE_ROW == rc ){ 1168 /* call the supplied callback with the result row data */ 1169 if( xCallback(pArg, nCol, azVals, azCols, aiTypes) ){ 1170 rc = SQLITE_ABORT; 1171 }else{ 1172 rc = sqlite3_step(pStmt); 1173 } 1174 } 1175 } while( SQLITE_ROW == rc ); 1176 sqlite3_free(pData); 1177 } 1178 }else{ 1179 do{ 1180 rc = sqlite3_step(pStmt); 1181 } while( rc == SQLITE_ROW ); 1182 } 1183 } 1184 1185 /* print usage stats if stats on */ 1186 if( pArg && pArg->statsOn ){ 1187 display_stats(db, pArg, 0); 1188 } 1189 1190 /* Finalize the statement just executed. If this fails, save a 1191 ** copy of the error message. Otherwise, set zSql to point to the 1192 ** next statement to execute. */ 1193 rc = sqlite3_finalize(pStmt); 1194 if( rc==SQLITE_OK ){ 1195 zSql = zLeftover; 1196 while( IsSpace(zSql[0]) ) zSql++; 1197 }else if( pzErrMsg ){ 1198 *pzErrMsg = save_err_msg(db); 1199 } 1200 1201 /* clear saved stmt handle */ 1202 if( pArg ){ 1203 pArg->pStmt = NULL; 1204 } 1205 } 1206 } /* end while */ 1207 1208 return rc; 1209 } 1210 1211 1212 /* 1213 ** This is a different callback routine used for dumping the database. 1214 ** Each row received by this callback consists of a table name, 1215 ** the table type ("index" or "table") and SQL to create the table. 1216 ** This routine should print text sufficient to recreate the table. 1217 */ 1218 static int dump_callback(void *pArg, int nArg, char **azArg, char **azCol){ 1219 int rc; 1220 const char *zTable; 1221 const char *zType; 1222 const char *zSql; 1223 const char *zPrepStmt = 0; 1224 struct callback_data *p = (struct callback_data *)pArg; 1225 1226 UNUSED_PARAMETER(azCol); 1227 if( nArg!=3 ) return 1; 1228 zTable = azArg[0]; 1229 zType = azArg[1]; 1230 zSql = azArg[2]; 1231 1232 if( strcmp(zTable, "sqlite_sequence")==0 ){ 1233 zPrepStmt = "DELETE FROM sqlite_sequence;\n"; 1234 }else if( strcmp(zTable, "sqlite_stat1")==0 ){ 1235 fprintf(p->out, "ANALYZE sqlite_master;\n"); 1236 }else if( strncmp(zTable, "sqlite_", 7)==0 ){ 1237 return 0; 1238 }else if( strncmp(zSql, "CREATE VIRTUAL TABLE", 20)==0 ){ 1239 char *zIns; 1240 if( !p->writableSchema ){ 1241 fprintf(p->out, "PRAGMA writable_schema=ON;\n"); 1242 p->writableSchema = 1; 1243 } 1244 zIns = sqlite3_mprintf( 1245 "INSERT INTO sqlite_master(type,name,tbl_name,rootpage,sql)" 1246 "VALUES('table','%q','%q',0,'%q');", 1247 zTable, zTable, zSql); 1248 fprintf(p->out, "%s\n", zIns); 1249 sqlite3_free(zIns); 1250 return 0; 1251 }else{ 1252 fprintf(p->out, "%s;\n", zSql); 1253 } 1254 1255 if( strcmp(zType, "table")==0 ){ 1256 sqlite3_stmt *pTableInfo = 0; 1257 char *zSelect = 0; 1258 char *zTableInfo = 0; 1259 char *zTmp = 0; 1260 int nRow = 0; 1261 1262 zTableInfo = appendText(zTableInfo, "PRAGMA table_info(", 0); 1263 zTableInfo = appendText(zTableInfo, zTable, '"'); 1264 zTableInfo = appendText(zTableInfo, ");", 0); 1265 1266 rc = sqlite3_prepare(p->db, zTableInfo, -1, &pTableInfo, 0); 1267 free(zTableInfo); 1268 if( rc!=SQLITE_OK || !pTableInfo ){ 1269 return 1; 1270 } 1271 1272 zSelect = appendText(zSelect, "SELECT 'INSERT INTO ' || ", 0); 1273 zTmp = appendText(zTmp, zTable, '"'); 1274 if( zTmp ){ 1275 zSelect = appendText(zSelect, zTmp, '\''); 1276 } 1277 zSelect = appendText(zSelect, " || ' VALUES(' || ", 0); 1278 rc = sqlite3_step(pTableInfo); 1279 while( rc==SQLITE_ROW ){ 1280 const char *zText = (const char *)sqlite3_column_text(pTableInfo, 1); 1281 zSelect = appendText(zSelect, "quote(", 0); 1282 zSelect = appendText(zSelect, zText, '"'); 1283 rc = sqlite3_step(pTableInfo); 1284 if( rc==SQLITE_ROW ){ 1285 zSelect = appendText(zSelect, ") || ',' || ", 0); 1286 }else{ 1287 zSelect = appendText(zSelect, ") ", 0); 1288 } 1289 nRow++; 1290 } 1291 rc = sqlite3_finalize(pTableInfo); 1292 if( rc!=SQLITE_OK || nRow==0 ){ 1293 free(zSelect); 1294 return 1; 1295 } 1296 zSelect = appendText(zSelect, "|| ')' FROM ", 0); 1297 zSelect = appendText(zSelect, zTable, '"'); 1298 1299 rc = run_table_dump_query(p, zSelect, zPrepStmt); 1300 if( rc==SQLITE_CORRUPT ){ 1301 zSelect = appendText(zSelect, " ORDER BY rowid DESC", 0); 1302 run_table_dump_query(p, zSelect, 0); 1303 } 1304 if( zSelect ) free(zSelect); 1305 } 1306 return 0; 1307 } 1308 1309 /* 1310 ** Run zQuery. Use dump_callback() as the callback routine so that 1311 ** the contents of the query are output as SQL statements. 1312 ** 1313 ** If we get a SQLITE_CORRUPT error, rerun the query after appending 1314 ** "ORDER BY rowid DESC" to the end. 1315 */ 1316 static int run_schema_dump_query( 1317 struct callback_data *p, 1318 const char *zQuery 1319 ){ 1320 int rc; 1321 char *zErr = 0; 1322 rc = sqlite3_exec(p->db, zQuery, dump_callback, p, &zErr); 1323 if( rc==SQLITE_CORRUPT ){ 1324 char *zQ2; 1325 int len = strlen30(zQuery); 1326 fprintf(p->out, "/****** CORRUPTION ERROR *******/\n"); 1327 if( zErr ){ 1328 fprintf(p->out, "/****** %s ******/\n", zErr); 1329 sqlite3_free(zErr); 1330 zErr = 0; 1331 } 1332 zQ2 = malloc( len+100 ); 1333 if( zQ2==0 ) return rc; 1334 sqlite3_snprintf(sizeof(zQ2), zQ2, "%s ORDER BY rowid DESC", zQuery); 1335 rc = sqlite3_exec(p->db, zQ2, dump_callback, p, &zErr); 1336 if( rc ){ 1337 fprintf(p->out, "/****** ERROR: %s ******/\n", zErr); 1338 }else{ 1339 rc = SQLITE_CORRUPT; 1340 } 1341 sqlite3_free(zErr); 1342 free(zQ2); 1343 } 1344 return rc; 1345 } 1346 1347 /* 1348 ** Text of a help message 1349 */ 1350 static char zHelp[] = 1351 ".backup ?DB? FILE Backup DB (default \"main\") to FILE\n" 1352 ".bail ON|OFF Stop after hitting an error. Default OFF\n" 1353 ".databases List names and files of attached databases\n" 1354 ".dump ?TABLE? ... Dump the database in an SQL text format\n" 1355 " If TABLE specified, only dump tables matching\n" 1356 " LIKE pattern TABLE.\n" 1357 ".echo ON|OFF Turn command echo on or off\n" 1358 ".exit Exit this program\n" 1359 ".explain ?ON|OFF? Turn output mode suitable for EXPLAIN on or off.\n" 1360 " With no args, it turns EXPLAIN on.\n" 1361 ".header(s) ON|OFF Turn display of headers on or off\n" 1362 ".help Show this message\n" 1363 ".import FILE TABLE Import data from FILE into TABLE\n" 1364 ".indices ?TABLE? Show names of all indices\n" 1365 " If TABLE specified, only show indices for tables\n" 1366 " matching LIKE pattern TABLE.\n" 1367 #ifdef SQLITE_ENABLE_IOTRACE 1368 ".iotrace FILE Enable I/O diagnostic logging to FILE\n" 1369 #endif 1370 #ifndef SQLITE_OMIT_LOAD_EXTENSION 1371 ".load FILE ?ENTRY? Load an extension library\n" 1372 #endif 1373 ".log FILE|off Turn logging on or off. FILE can be stderr/stdout\n" 1374 ".mode MODE ?TABLE? Set output mode where MODE is one of:\n" 1375 " csv Comma-separated values\n" 1376 " column Left-aligned columns. (See .width)\n" 1377 " html HTML <table> code\n" 1378 " insert SQL insert statements for TABLE\n" 1379 " line One value per line\n" 1380 " list Values delimited by .separator string\n" 1381 " tabs Tab-separated values\n" 1382 " tcl TCL list elements\n" 1383 ".nullvalue STRING Print STRING in place of NULL values\n" 1384 ".output FILENAME Send output to FILENAME\n" 1385 ".output stdout Send output to the screen\n" 1386 ".prompt MAIN CONTINUE Replace the standard prompts\n" 1387 ".quit Exit this program\n" 1388 ".read FILENAME Execute SQL in FILENAME\n" 1389 ".restore ?DB? FILE Restore content of DB (default \"main\") from FILE\n" 1390 ".schema ?TABLE? Show the CREATE statements\n" 1391 " If TABLE specified, only show tables matching\n" 1392 " LIKE pattern TABLE.\n" 1393 ".separator STRING Change separator used by output mode and .import\n" 1394 ".show Show the current values for various settings\n" 1395 ".stats ON|OFF Turn stats on or off\n" 1396 ".tables ?TABLE? List names of tables\n" 1397 " If TABLE specified, only list tables matching\n" 1398 " LIKE pattern TABLE.\n" 1399 ".timeout MS Try opening locked tables for MS milliseconds\n" 1400 ".width NUM1 NUM2 ... Set column widths for \"column\" mode\n" 1401 ; 1402 1403 static char zTimerHelp[] = 1404 ".timer ON|OFF Turn the CPU timer measurement on or off\n" 1405 ; 1406 1407 /* Forward reference */ 1408 static int process_input(struct callback_data *p, FILE *in); 1409 1410 /* 1411 ** Make sure the database is open. If it is not, then open it. If 1412 ** the database fails to open, print an error message and exit. 1413 */ 1414 static void open_db(struct callback_data *p){ 1415 if( p->db==0 ){ 1416 sqlite3_open(p->zDbFilename, &p->db); 1417 db = p->db; 1418 if( db && sqlite3_errcode(db)==SQLITE_OK ){ 1419 sqlite3_create_function(db, "shellstatic", 0, SQLITE_UTF8, 0, 1420 shellstaticFunc, 0, 0); 1421 } 1422 if( db==0 || SQLITE_OK!=sqlite3_errcode(db) ){ 1423 fprintf(stderr,"Error: unable to open database \"%s\": %s\n", 1424 p->zDbFilename, sqlite3_errmsg(db)); 1425 exit(1); 1426 } 1427 #ifndef SQLITE_OMIT_LOAD_EXTENSION 1428 sqlite3_enable_load_extension(p->db, 1); 1429 #endif 1430 } 1431 } 1432 1433 /* 1434 ** Do C-language style dequoting. 1435 ** 1436 ** \t -> tab 1437 ** \n -> newline 1438 ** \r -> carriage return 1439 ** \NNN -> ascii character NNN in octal 1440 ** \\ -> backslash 1441 */ 1442 static void resolve_backslashes(char *z){ 1443 int i, j; 1444 char c; 1445 for(i=j=0; (c = z[i])!=0; i++, j++){ 1446 if( c=='\\' ){ 1447 c = z[++i]; 1448 if( c=='n' ){ 1449 c = '\n'; 1450 }else if( c=='t' ){ 1451 c = '\t'; 1452 }else if( c=='r' ){ 1453 c = '\r'; 1454 }else if( c>='0' && c<='7' ){ 1455 c -= '0'; 1456 if( z[i+1]>='0' && z[i+1]<='7' ){ 1457 i++; 1458 c = (c<<3) + z[i] - '0'; 1459 if( z[i+1]>='0' && z[i+1]<='7' ){ 1460 i++; 1461 c = (c<<3) + z[i] - '0'; 1462 } 1463 } 1464 } 1465 } 1466 z[j] = c; 1467 } 1468 z[j] = 0; 1469 } 1470 1471 /* 1472 ** Interpret zArg as a boolean value. Return either 0 or 1. 1473 */ 1474 static int booleanValue(char *zArg){ 1475 int val = atoi(zArg); 1476 int j; 1477 for(j=0; zArg[j]; j++){ 1478 zArg[j] = ToLower(zArg[j]); 1479 } 1480 if( strcmp(zArg,"on")==0 ){ 1481 val = 1; 1482 }else if( strcmp(zArg,"yes")==0 ){ 1483 val = 1; 1484 } 1485 return val; 1486 } 1487 1488 /* 1489 ** If an input line begins with "." then invoke this routine to 1490 ** process that line. 1491 ** 1492 ** Return 1 on error, 2 to exit, and 0 otherwise. 1493 */ 1494 static int do_meta_command(char *zLine, struct callback_data *p){ 1495 int i = 1; 1496 int nArg = 0; 1497 int n, c; 1498 int rc = 0; 1499 char *azArg[50]; 1500 1501 /* Parse the input line into tokens. 1502 */ 1503 while( zLine[i] && nArg<ArraySize(azArg) ){ 1504 while( IsSpace(zLine[i]) ){ i++; } 1505 if( zLine[i]==0 ) break; 1506 if( zLine[i]=='\'' || zLine[i]=='"' ){ 1507 int delim = zLine[i++]; 1508 azArg[nArg++] = &zLine[i]; 1509 while( zLine[i] && zLine[i]!=delim ){ i++; } 1510 if( zLine[i]==delim ){ 1511 zLine[i++] = 0; 1512 } 1513 if( delim=='"' ) resolve_backslashes(azArg[nArg-1]); 1514 }else{ 1515 azArg[nArg++] = &zLine[i]; 1516 while( zLine[i] && !IsSpace(zLine[i]) ){ i++; } 1517 if( zLine[i] ) zLine[i++] = 0; 1518 resolve_backslashes(azArg[nArg-1]); 1519 } 1520 } 1521 1522 /* Process the input line. 1523 */ 1524 if( nArg==0 ) return 0; /* no tokens, no error */ 1525 n = strlen30(azArg[0]); 1526 c = azArg[0][0]; 1527 if( c=='b' && n>=3 && strncmp(azArg[0], "backup", n)==0 && nArg>1 && nArg<4){ 1528 const char *zDestFile; 1529 const char *zDb; 1530 sqlite3 *pDest; 1531 sqlite3_backup *pBackup; 1532 if( nArg==2 ){ 1533 zDestFile = azArg[1]; 1534 zDb = "main"; 1535 }else{ 1536 zDestFile = azArg[2]; 1537 zDb = azArg[1]; 1538 } 1539 rc = sqlite3_open(zDestFile, &pDest); 1540 if( rc!=SQLITE_OK ){ 1541 fprintf(stderr, "Error: cannot open \"%s\"\n", zDestFile); 1542 sqlite3_close(pDest); 1543 return 1; 1544 } 1545 open_db(p); 1546 pBackup = sqlite3_backup_init(pDest, "main", p->db, zDb); 1547 if( pBackup==0 ){ 1548 fprintf(stderr, "Error: %s\n", sqlite3_errmsg(pDest)); 1549 sqlite3_close(pDest); 1550 return 1; 1551 } 1552 while( (rc = sqlite3_backup_step(pBackup,100))==SQLITE_OK ){} 1553 sqlite3_backup_finish(pBackup); 1554 if( rc==SQLITE_DONE ){ 1555 rc = 0; 1556 }else{ 1557 fprintf(stderr, "Error: %s\n", sqlite3_errmsg(pDest)); 1558 rc = 1; 1559 } 1560 sqlite3_close(pDest); 1561 }else 1562 1563 if( c=='b' && n>=3 && strncmp(azArg[0], "bail", n)==0 && nArg>1 && nArg<3 ){ 1564 bail_on_error = booleanValue(azArg[1]); 1565 }else 1566 1567 if( c=='d' && n>1 && strncmp(azArg[0], "databases", n)==0 && nArg==1 ){ 1568 struct callback_data data; 1569 char *zErrMsg = 0; 1570 open_db(p); 1571 memcpy(&data, p, sizeof(data)); 1572 data.showHeader = 1; 1573 data.mode = MODE_Column; 1574 data.colWidth[0] = 3; 1575 data.colWidth[1] = 15; 1576 data.colWidth[2] = 58; 1577 data.cnt = 0; 1578 sqlite3_exec(p->db, "PRAGMA database_list; ", callback, &data, &zErrMsg); 1579 if( zErrMsg ){ 1580 fprintf(stderr,"Error: %s\n", zErrMsg); 1581 sqlite3_free(zErrMsg); 1582 rc = 1; 1583 } 1584 }else 1585 1586 if( c=='d' && strncmp(azArg[0], "dump", n)==0 && nArg<3 ){ 1587 open_db(p); 1588 /* When playing back a "dump", the content might appear in an order 1589 ** which causes immediate foreign key constraints to be violated. 1590 ** So disable foreign-key constraint enforcement to prevent problems. */ 1591 fprintf(p->out, "PRAGMA foreign_keys=OFF;\n"); 1592 fprintf(p->out, "BEGIN TRANSACTION;\n"); 1593 p->writableSchema = 0; 1594 sqlite3_exec(p->db, "SAVEPOINT dump; PRAGMA writable_schema=ON", 0, 0, 0); 1595 p->nErr = 0; 1596 if( nArg==1 ){ 1597 run_schema_dump_query(p, 1598 "SELECT name, type, sql FROM sqlite_master " 1599 "WHERE sql NOT NULL AND type=='table' AND name!='sqlite_sequence'" 1600 ); 1601 run_schema_dump_query(p, 1602 "SELECT name, type, sql FROM sqlite_master " 1603 "WHERE name=='sqlite_sequence'" 1604 ); 1605 run_table_dump_query(p, 1606 "SELECT sql FROM sqlite_master " 1607 "WHERE sql NOT NULL AND type IN ('index','trigger','view')", 0 1608 ); 1609 }else{ 1610 int i; 1611 for(i=1; i<nArg; i++){ 1612 zShellStatic = azArg[i]; 1613 run_schema_dump_query(p, 1614 "SELECT name, type, sql FROM sqlite_master " 1615 "WHERE tbl_name LIKE shellstatic() AND type=='table'" 1616 " AND sql NOT NULL"); 1617 run_table_dump_query(p, 1618 "SELECT sql FROM sqlite_master " 1619 "WHERE sql NOT NULL" 1620 " AND type IN ('index','trigger','view')" 1621 " AND tbl_name LIKE shellstatic()", 0 1622 ); 1623 zShellStatic = 0; 1624 } 1625 } 1626 if( p->writableSchema ){ 1627 fprintf(p->out, "PRAGMA writable_schema=OFF;\n"); 1628 p->writableSchema = 0; 1629 } 1630 sqlite3_exec(p->db, "PRAGMA writable_schema=OFF;", 0, 0, 0); 1631 sqlite3_exec(p->db, "RELEASE dump;", 0, 0, 0); 1632 fprintf(p->out, p->nErr ? "ROLLBACK; -- due to errors\n" : "COMMIT;\n"); 1633 }else 1634 1635 if( c=='e' && strncmp(azArg[0], "echo", n)==0 && nArg>1 && nArg<3 ){ 1636 p->echoOn = booleanValue(azArg[1]); 1637 }else 1638 1639 if( c=='e' && strncmp(azArg[0], "exit", n)==0 && nArg==1 ){ 1640 rc = 2; 1641 }else 1642 1643 if( c=='e' && strncmp(azArg[0], "explain", n)==0 && nArg<3 ){ 1644 int val = nArg>=2 ? booleanValue(azArg[1]) : 1; 1645 if(val == 1) { 1646 if(!p->explainPrev.valid) { 1647 p->explainPrev.valid = 1; 1648 p->explainPrev.mode = p->mode; 1649 p->explainPrev.showHeader = p->showHeader; 1650 memcpy(p->explainPrev.colWidth,p->colWidth,sizeof(p->colWidth)); 1651 } 1652 /* We could put this code under the !p->explainValid 1653 ** condition so that it does not execute if we are already in 1654 ** explain mode. However, always executing it allows us an easy 1655 ** was to reset to explain mode in case the user previously 1656 ** did an .explain followed by a .width, .mode or .header 1657 ** command. 1658 */ 1659 p->mode = MODE_Explain; 1660 p->showHeader = 1; 1661 memset(p->colWidth,0,ArraySize(p->colWidth)); 1662 p->colWidth[0] = 4; /* addr */ 1663 p->colWidth[1] = 13; /* opcode */ 1664 p->colWidth[2] = 4; /* P1 */ 1665 p->colWidth[3] = 4; /* P2 */ 1666 p->colWidth[4] = 4; /* P3 */ 1667 p->colWidth[5] = 13; /* P4 */ 1668 p->colWidth[6] = 2; /* P5 */ 1669 p->colWidth[7] = 13; /* Comment */ 1670 }else if (p->explainPrev.valid) { 1671 p->explainPrev.valid = 0; 1672 p->mode = p->explainPrev.mode; 1673 p->showHeader = p->explainPrev.showHeader; 1674 memcpy(p->colWidth,p->explainPrev.colWidth,sizeof(p->colWidth)); 1675 } 1676 }else 1677 1678 if( c=='h' && (strncmp(azArg[0], "header", n)==0 || 1679 strncmp(azArg[0], "headers", n)==0) && nArg>1 && nArg<3 ){ 1680 p->showHeader = booleanValue(azArg[1]); 1681 }else 1682 1683 if( c=='h' && strncmp(azArg[0], "help", n)==0 ){ 1684 fprintf(stderr,"%s",zHelp); 1685 if( HAS_TIMER ){ 1686 fprintf(stderr,"%s",zTimerHelp); 1687 } 1688 }else 1689 1690 if( c=='i' && strncmp(azArg[0], "import", n)==0 && nArg==3 ){ 1691 char *zTable = azArg[2]; /* Insert data into this table */ 1692 char *zFile = azArg[1]; /* The file from which to extract data */ 1693 sqlite3_stmt *pStmt = NULL; /* A statement */ 1694 int nCol; /* Number of columns in the table */ 1695 int nByte; /* Number of bytes in an SQL string */ 1696 int i, j; /* Loop counters */ 1697 int nSep; /* Number of bytes in p->separator[] */ 1698 char *zSql; /* An SQL statement */ 1699 char *zLine; /* A single line of input from the file */ 1700 char **azCol; /* zLine[] broken up into columns */ 1701 char *zCommit; /* How to commit changes */ 1702 FILE *in; /* The input file */ 1703 int lineno = 0; /* Line number of input file */ 1704 1705 open_db(p); 1706 nSep = strlen30(p->separator); 1707 if( nSep==0 ){ 1708 fprintf(stderr, "Error: non-null separator required for import\n"); 1709 return 1; 1710 } 1711 zSql = sqlite3_mprintf("SELECT * FROM %s", zTable); 1712 if( zSql==0 ){ 1713 fprintf(stderr, "Error: out of memory\n"); 1714 return 1; 1715 } 1716 nByte = strlen30(zSql); 1717 rc = sqlite3_prepare(p->db, zSql, -1, &pStmt, 0); 1718 sqlite3_free(zSql); 1719 if( rc ){ 1720 if (pStmt) sqlite3_finalize(pStmt); 1721 fprintf(stderr,"Error: %s\n", sqlite3_errmsg(db)); 1722 return 1; 1723 } 1724 nCol = sqlite3_column_count(pStmt); 1725 sqlite3_finalize(pStmt); 1726 pStmt = 0; 1727 if( nCol==0 ) return 0; /* no columns, no error */ 1728 zSql = malloc( nByte + 20 + nCol*2 ); 1729 if( zSql==0 ){ 1730 fprintf(stderr, "Error: out of memory\n"); 1731 return 1; 1732 } 1733 sqlite3_snprintf(nByte+20, zSql, "INSERT INTO %s VALUES(?", zTable); 1734 j = strlen30(zSql); 1735 for(i=1; i<nCol; i++){ 1736 zSql[j++] = ','; 1737 zSql[j++] = '?'; 1738 } 1739 zSql[j++] = ')'; 1740 zSql[j] = 0; 1741 rc = sqlite3_prepare(p->db, zSql, -1, &pStmt, 0); 1742 free(zSql); 1743 if( rc ){ 1744 fprintf(stderr, "Error: %s\n", sqlite3_errmsg(db)); 1745 if (pStmt) sqlite3_finalize(pStmt); 1746 return 1; 1747 } 1748 in = fopen(zFile, "rb"); 1749 if( in==0 ){ 1750 fprintf(stderr, "Error: cannot open \"%s\"\n", zFile); 1751 sqlite3_finalize(pStmt); 1752 return 1; 1753 } 1754 azCol = malloc( sizeof(azCol[0])*(nCol+1) ); 1755 if( azCol==0 ){ 1756 fprintf(stderr, "Error: out of memory\n"); 1757 fclose(in); 1758 sqlite3_finalize(pStmt); 1759 return 1; 1760 } 1761 sqlite3_exec(p->db, "BEGIN", 0, 0, 0); 1762 zCommit = "COMMIT"; 1763 while( (zLine = local_getline(0, in))!=0 ){ 1764 char *z; 1765 i = 0; 1766 lineno++; 1767 azCol[0] = zLine; 1768 for(i=0, z=zLine; *z && *z!='\n' && *z!='\r'; z++){ 1769 if( *z==p->separator[0] && strncmp(z, p->separator, nSep)==0 ){ 1770 *z = 0; 1771 i++; 1772 if( i<nCol ){ 1773 azCol[i] = &z[nSep]; 1774 z += nSep-1; 1775 } 1776 } 1777 } /* end for */ 1778 *z = 0; 1779 if( i+1!=nCol ){ 1780 fprintf(stderr, 1781 "Error: %s line %d: expected %d columns of data but found %d\n", 1782 zFile, lineno, nCol, i+1); 1783 zCommit = "ROLLBACK"; 1784 free(zLine); 1785 rc = 1; 1786 break; /* from while */ 1787 } 1788 for(i=0; i<nCol; i++){ 1789 sqlite3_bind_text(pStmt, i+1, azCol[i], -1, SQLITE_STATIC); 1790 } 1791 sqlite3_step(pStmt); 1792 rc = sqlite3_reset(pStmt); 1793 free(zLine); 1794 if( rc!=SQLITE_OK ){ 1795 fprintf(stderr,"Error: %s\n", sqlite3_errmsg(db)); 1796 zCommit = "ROLLBACK"; 1797 rc = 1; 1798 break; /* from while */ 1799 } 1800 } /* end while */ 1801 free(azCol); 1802 fclose(in); 1803 sqlite3_finalize(pStmt); 1804 sqlite3_exec(p->db, zCommit, 0, 0, 0); 1805 }else 1806 1807 if( c=='i' && strncmp(azArg[0], "indices", n)==0 && nArg<3 ){ 1808 struct callback_data data; 1809 char *zErrMsg = 0; 1810 open_db(p); 1811 memcpy(&data, p, sizeof(data)); 1812 data.showHeader = 0; 1813 data.mode = MODE_List; 1814 if( nArg==1 ){ 1815 rc = sqlite3_exec(p->db, 1816 "SELECT name FROM sqlite_master " 1817 "WHERE type='index' AND name NOT LIKE 'sqlite_%' " 1818 "UNION ALL " 1819 "SELECT name FROM sqlite_temp_master " 1820 "WHERE type='index' " 1821 "ORDER BY 1", 1822 callback, &data, &zErrMsg 1823 ); 1824 }else{ 1825 zShellStatic = azArg[1]; 1826 rc = sqlite3_exec(p->db, 1827 "SELECT name FROM sqlite_master " 1828 "WHERE type='index' AND tbl_name LIKE shellstatic() " 1829 "UNION ALL " 1830 "SELECT name FROM sqlite_temp_master " 1831 "WHERE type='index' AND tbl_name LIKE shellstatic() " 1832 "ORDER BY 1", 1833 callback, &data, &zErrMsg 1834 ); 1835 zShellStatic = 0; 1836 } 1837 if( zErrMsg ){ 1838 fprintf(stderr,"Error: %s\n", zErrMsg); 1839 sqlite3_free(zErrMsg); 1840 rc = 1; 1841 }else if( rc != SQLITE_OK ){ 1842 fprintf(stderr,"Error: querying sqlite_master and sqlite_temp_master\n"); 1843 rc = 1; 1844 } 1845 }else 1846 1847 #ifdef SQLITE_ENABLE_IOTRACE 1848 if( c=='i' && strncmp(azArg[0], "iotrace", n)==0 ){ 1849 extern void (*sqlite3IoTrace)(const char*, ...); 1850 if( iotrace && iotrace!=stdout ) fclose(iotrace); 1851 iotrace = 0; 1852 if( nArg<2 ){ 1853 sqlite3IoTrace = 0; 1854 }else if( strcmp(azArg[1], "-")==0 ){ 1855 sqlite3IoTrace = iotracePrintf; 1856 iotrace = stdout; 1857 }else{ 1858 iotrace = fopen(azArg[1], "w"); 1859 if( iotrace==0 ){ 1860 fprintf(stderr, "Error: cannot open \"%s\"\n", azArg[1]); 1861 sqlite3IoTrace = 0; 1862 rc = 1; 1863 }else{ 1864 sqlite3IoTrace = iotracePrintf; 1865 } 1866 } 1867 }else 1868 #endif 1869 1870 #ifndef SQLITE_OMIT_LOAD_EXTENSION 1871 if( c=='l' && strncmp(azArg[0], "load", n)==0 && nArg>=2 ){ 1872 const char *zFile, *zProc; 1873 char *zErrMsg = 0; 1874 zFile = azArg[1]; 1875 zProc = nArg>=3 ? azArg[2] : 0; 1876 open_db(p); 1877 rc = sqlite3_load_extension(p->db, zFile, zProc, &zErrMsg); 1878 if( rc!=SQLITE_OK ){ 1879 fprintf(stderr, "Error: %s\n", zErrMsg); 1880 sqlite3_free(zErrMsg); 1881 rc = 1; 1882 } 1883 }else 1884 #endif 1885 1886 if( c=='l' && strncmp(azArg[0], "log", n)==0 && nArg>=2 ){ 1887 const char *zFile = azArg[1]; 1888 if( p->pLog && p->pLog!=stdout && p->pLog!=stderr ){ 1889 fclose(p->pLog); 1890 p->pLog = 0; 1891 } 1892 if( strcmp(zFile,"stdout")==0 ){ 1893 p->pLog = stdout; 1894 }else if( strcmp(zFile, "stderr")==0 ){ 1895 p->pLog = stderr; 1896 }else if( strcmp(zFile, "off")==0 ){ 1897 p->pLog = 0; 1898 }else{ 1899 p->pLog = fopen(zFile, "w"); 1900 if( p->pLog==0 ){ 1901 fprintf(stderr, "Error: cannot open \"%s\"\n", zFile); 1902 } 1903 } 1904 }else 1905 1906 if( c=='m' && strncmp(azArg[0], "mode", n)==0 && nArg==2 ){ 1907 int n2 = strlen30(azArg[1]); 1908 if( (n2==4 && strncmp(azArg[1],"line",n2)==0) 1909 || 1910 (n2==5 && strncmp(azArg[1],"lines",n2)==0) ){ 1911 p->mode = MODE_Line; 1912 }else if( (n2==6 && strncmp(azArg[1],"column",n2)==0) 1913 || 1914 (n2==7 && strncmp(azArg[1],"columns",n2)==0) ){ 1915 p->mode = MODE_Column; 1916 }else if( n2==4 && strncmp(azArg[1],"list",n2)==0 ){ 1917 p->mode = MODE_List; 1918 }else if( n2==4 && strncmp(azArg[1],"html",n2)==0 ){ 1919 p->mode = MODE_Html; 1920 }else if( n2==3 && strncmp(azArg[1],"tcl",n2)==0 ){ 1921 p->mode = MODE_Tcl; 1922 }else if( n2==3 && strncmp(azArg[1],"csv",n2)==0 ){ 1923 p->mode = MODE_Csv; 1924 sqlite3_snprintf(sizeof(p->separator), p->separator, ","); 1925 }else if( n2==4 && strncmp(azArg[1],"tabs",n2)==0 ){ 1926 p->mode = MODE_List; 1927 sqlite3_snprintf(sizeof(p->separator), p->separator, "\t"); 1928 }else if( n2==6 && strncmp(azArg[1],"insert",n2)==0 ){ 1929 p->mode = MODE_Insert; 1930 set_table_name(p, "table"); 1931 }else { 1932 fprintf(stderr,"Error: mode should be one of: " 1933 "column csv html insert line list tabs tcl\n"); 1934 rc = 1; 1935 } 1936 }else 1937 1938 if( c=='m' && strncmp(azArg[0], "mode", n)==0 && nArg==3 ){ 1939 int n2 = strlen30(azArg[1]); 1940 if( n2==6 && strncmp(azArg[1],"insert",n2)==0 ){ 1941 p->mode = MODE_Insert; 1942 set_table_name(p, azArg[2]); 1943 }else { 1944 fprintf(stderr, "Error: invalid arguments: " 1945 " \"%s\". Enter \".help\" for help\n", azArg[2]); 1946 rc = 1; 1947 } 1948 }else 1949 1950 if( c=='n' && strncmp(azArg[0], "nullvalue", n)==0 && nArg==2 ) { 1951 sqlite3_snprintf(sizeof(p->nullvalue), p->nullvalue, 1952 "%.*s", (int)ArraySize(p->nullvalue)-1, azArg[1]); 1953 }else 1954 1955 if( c=='o' && strncmp(azArg[0], "output", n)==0 && nArg==2 ){ 1956 if( p->out!=stdout ){ 1957 fclose(p->out); 1958 } 1959 if( strcmp(azArg[1],"stdout")==0 ){ 1960 p->out = stdout; 1961 sqlite3_snprintf(sizeof(p->outfile), p->outfile, "stdout"); 1962 }else{ 1963 p->out = fopen(azArg[1], "wb"); 1964 if( p->out==0 ){ 1965 fprintf(stderr,"Error: cannot write to \"%s\"\n", azArg[1]); 1966 p->out = stdout; 1967 rc = 1; 1968 } else { 1969 sqlite3_snprintf(sizeof(p->outfile), p->outfile, "%s", azArg[1]); 1970 } 1971 } 1972 }else 1973 1974 if( c=='p' && strncmp(azArg[0], "prompt", n)==0 && (nArg==2 || nArg==3)){ 1975 if( nArg >= 2) { 1976 strncpy(mainPrompt,azArg[1],(int)ArraySize(mainPrompt)-1); 1977 } 1978 if( nArg >= 3) { 1979 strncpy(continuePrompt,azArg[2],(int)ArraySize(continuePrompt)-1); 1980 } 1981 }else 1982 1983 if( c=='q' && strncmp(azArg[0], "quit", n)==0 && nArg==1 ){ 1984 rc = 2; 1985 }else 1986 1987 if( c=='r' && n>=3 && strncmp(azArg[0], "read", n)==0 && nArg==2 ){ 1988 FILE *alt = fopen(azArg[1], "rb"); 1989 if( alt==0 ){ 1990 fprintf(stderr,"Error: cannot open \"%s\"\n", azArg[1]); 1991 rc = 1; 1992 }else{ 1993 rc = process_input(p, alt); 1994 fclose(alt); 1995 } 1996 }else 1997 1998 if( c=='r' && n>=3 && strncmp(azArg[0], "restore", n)==0 && nArg>1 && nArg<4){ 1999 const char *zSrcFile; 2000 const char *zDb; 2001 sqlite3 *pSrc; 2002 sqlite3_backup *pBackup; 2003 int nTimeout = 0; 2004 2005 if( nArg==2 ){ 2006 zSrcFile = azArg[1]; 2007 zDb = "main"; 2008 }else{ 2009 zSrcFile = azArg[2]; 2010 zDb = azArg[1]; 2011 } 2012 rc = sqlite3_open(zSrcFile, &pSrc); 2013 if( rc!=SQLITE_OK ){ 2014 fprintf(stderr, "Error: cannot open \"%s\"\n", zSrcFile); 2015 sqlite3_close(pSrc); 2016 return 1; 2017 } 2018 open_db(p); 2019 pBackup = sqlite3_backup_init(p->db, zDb, pSrc, "main"); 2020 if( pBackup==0 ){ 2021 fprintf(stderr, "Error: %s\n", sqlite3_errmsg(p->db)); 2022 sqlite3_close(pSrc); 2023 return 1; 2024 } 2025 while( (rc = sqlite3_backup_step(pBackup,100))==SQLITE_OK 2026 || rc==SQLITE_BUSY ){ 2027 if( rc==SQLITE_BUSY ){ 2028 if( nTimeout++ >= 3 ) break; 2029 sqlite3_sleep(100); 2030 } 2031 } 2032 sqlite3_backup_finish(pBackup); 2033 if( rc==SQLITE_DONE ){ 2034 rc = 0; 2035 }else if( rc==SQLITE_BUSY || rc==SQLITE_LOCKED ){ 2036 fprintf(stderr, "Error: source database is busy\n"); 2037 rc = 1; 2038 }else{ 2039 fprintf(stderr, "Error: %s\n", sqlite3_errmsg(p->db)); 2040 rc = 1; 2041 } 2042 sqlite3_close(pSrc); 2043 }else 2044 2045 if( c=='s' && strncmp(azArg[0], "schema", n)==0 && nArg<3 ){ 2046 struct callback_data data; 2047 char *zErrMsg = 0; 2048 open_db(p); 2049 memcpy(&data, p, sizeof(data)); 2050 data.showHeader = 0; 2051 data.mode = MODE_Semi; 2052 if( nArg>1 ){ 2053 int i; 2054 for(i=0; azArg[1][i]; i++) azArg[1][i] = ToLower(azArg[1][i]); 2055 if( strcmp(azArg[1],"sqlite_master")==0 ){ 2056 char *new_argv[2], *new_colv[2]; 2057 new_argv[0] = "CREATE TABLE sqlite_master (\n" 2058 " type text,\n" 2059 " name text,\n" 2060 " tbl_name text,\n" 2061 " rootpage integer,\n" 2062 " sql text\n" 2063 ")"; 2064 new_argv[1] = 0; 2065 new_colv[0] = "sql"; 2066 new_colv[1] = 0; 2067 callback(&data, 1, new_argv, new_colv); 2068 rc = SQLITE_OK; 2069 }else if( strcmp(azArg[1],"sqlite_temp_master")==0 ){ 2070 char *new_argv[2], *new_colv[2]; 2071 new_argv[0] = "CREATE TEMP TABLE sqlite_temp_master (\n" 2072 " type text,\n" 2073 " name text,\n" 2074 " tbl_name text,\n" 2075 " rootpage integer,\n" 2076 " sql text\n" 2077 ")"; 2078 new_argv[1] = 0; 2079 new_colv[0] = "sql"; 2080 new_colv[1] = 0; 2081 callback(&data, 1, new_argv, new_colv); 2082 rc = SQLITE_OK; 2083 }else{ 2084 zShellStatic = azArg[1]; 2085 rc = sqlite3_exec(p->db, 2086 "SELECT sql FROM " 2087 " (SELECT sql sql, type type, tbl_name tbl_name, name name" 2088 " FROM sqlite_master UNION ALL" 2089 " SELECT sql, type, tbl_name, name FROM sqlite_temp_master) " 2090 "WHERE tbl_name LIKE shellstatic() AND type!='meta' AND sql NOTNULL " 2091 "ORDER BY substr(type,2,1), name", 2092 callback, &data, &zErrMsg); 2093 zShellStatic = 0; 2094 } 2095 }else{ 2096 rc = sqlite3_exec(p->db, 2097 "SELECT sql FROM " 2098 " (SELECT sql sql, type type, tbl_name tbl_name, name name" 2099 " FROM sqlite_master UNION ALL" 2100 " SELECT sql, type, tbl_name, name FROM sqlite_temp_master) " 2101 "WHERE type!='meta' AND sql NOTNULL AND name NOT LIKE 'sqlite_%'" 2102 "ORDER BY substr(type,2,1), name", 2103 callback, &data, &zErrMsg 2104 ); 2105 } 2106 if( zErrMsg ){ 2107 fprintf(stderr,"Error: %s\n", zErrMsg); 2108 sqlite3_free(zErrMsg); 2109 rc = 1; 2110 }else if( rc != SQLITE_OK ){ 2111 fprintf(stderr,"Error: querying schema information\n"); 2112 rc = 1; 2113 }else{ 2114 rc = 0; 2115 } 2116 }else 2117 2118 if( c=='s' && strncmp(azArg[0], "separator", n)==0 && nArg==2 ){ 2119 sqlite3_snprintf(sizeof(p->separator), p->separator, 2120 "%.*s", (int)sizeof(p->separator)-1, azArg[1]); 2121 }else 2122 2123 if( c=='s' && strncmp(azArg[0], "show", n)==0 && nArg==1 ){ 2124 int i; 2125 fprintf(p->out,"%9.9s: %s\n","echo", p->echoOn ? "on" : "off"); 2126 fprintf(p->out,"%9.9s: %s\n","explain", p->explainPrev.valid ? "on" :"off"); 2127 fprintf(p->out,"%9.9s: %s\n","headers", p->showHeader ? "on" : "off"); 2128 fprintf(p->out,"%9.9s: %s\n","mode", modeDescr[p->mode]); 2129 fprintf(p->out,"%9.9s: ", "nullvalue"); 2130 output_c_string(p->out, p->nullvalue); 2131 fprintf(p->out, "\n"); 2132 fprintf(p->out,"%9.9s: %s\n","output", 2133 strlen30(p->outfile) ? p->outfile : "stdout"); 2134 fprintf(p->out,"%9.9s: ", "separator"); 2135 output_c_string(p->out, p->separator); 2136 fprintf(p->out, "\n"); 2137 fprintf(p->out,"%9.9s: %s\n","stats", p->statsOn ? "on" : "off"); 2138 fprintf(p->out,"%9.9s: ","width"); 2139 for (i=0;i<(int)ArraySize(p->colWidth) && p->colWidth[i] != 0;i++) { 2140 fprintf(p->out,"%d ",p->colWidth[i]); 2141 } 2142 fprintf(p->out,"\n"); 2143 }else 2144 2145 if( c=='s' && strncmp(azArg[0], "stats", n)==0 && nArg>1 && nArg<3 ){ 2146 p->statsOn = booleanValue(azArg[1]); 2147 }else 2148 2149 if( c=='t' && n>1 && strncmp(azArg[0], "tables", n)==0 && nArg<3 ){ 2150 char **azResult; 2151 int nRow; 2152 char *zErrMsg; 2153 open_db(p); 2154 if( nArg==1 ){ 2155 rc = sqlite3_get_table(p->db, 2156 "SELECT name FROM sqlite_master " 2157 "WHERE type IN ('table','view') AND name NOT LIKE 'sqlite_%' " 2158 "UNION ALL " 2159 "SELECT name FROM sqlite_temp_master " 2160 "WHERE type IN ('table','view') " 2161 "ORDER BY 1", 2162 &azResult, &nRow, 0, &zErrMsg 2163 ); 2164 }else{ 2165 zShellStatic = azArg[1]; 2166 rc = sqlite3_get_table(p->db, 2167 "SELECT name FROM sqlite_master " 2168 "WHERE type IN ('table','view') AND name LIKE shellstatic() " 2169 "UNION ALL " 2170 "SELECT name FROM sqlite_temp_master " 2171 "WHERE type IN ('table','view') AND name LIKE shellstatic() " 2172 "ORDER BY 1", 2173 &azResult, &nRow, 0, &zErrMsg 2174 ); 2175 zShellStatic = 0; 2176 } 2177 if( zErrMsg ){ 2178 fprintf(stderr,"Error: %s\n", zErrMsg); 2179 sqlite3_free(zErrMsg); 2180 rc = 1; 2181 }else if( rc != SQLITE_OK ){ 2182 fprintf(stderr,"Error: querying sqlite_master and sqlite_temp_master\n"); 2183 rc = 1; 2184 }else{ 2185 int len, maxlen = 0; 2186 int i, j; 2187 int nPrintCol, nPrintRow; 2188 for(i=1; i<=nRow; i++){ 2189 if( azResult[i]==0 ) continue; 2190 len = strlen30(azResult[i]); 2191 if( len>maxlen ) maxlen = len; 2192 } 2193 nPrintCol = 80/(maxlen+2); 2194 if( nPrintCol<1 ) nPrintCol = 1; 2195 nPrintRow = (nRow + nPrintCol - 1)/nPrintCol; 2196 for(i=0; i<nPrintRow; i++){ 2197 for(j=i+1; j<=nRow; j+=nPrintRow){ 2198 char *zSp = j<=nPrintRow ? "" : " "; 2199 printf("%s%-*s", zSp, maxlen, azResult[j] ? azResult[j] : ""); 2200 } 2201 printf("\n"); 2202 } 2203 } 2204 sqlite3_free_table(azResult); 2205 }else 2206 2207 if( c=='t' && n>=8 && strncmp(azArg[0], "testctrl", n)==0 && nArg>=2 ){ 2208 static const struct { 2209 const char *zCtrlName; /* Name of a test-control option */ 2210 int ctrlCode; /* Integer code for that option */ 2211 } aCtrl[] = { 2212 { "prng_save", SQLITE_TESTCTRL_PRNG_SAVE }, 2213 { "prng_restore", SQLITE_TESTCTRL_PRNG_RESTORE }, 2214 { "prng_reset", SQLITE_TESTCTRL_PRNG_RESET }, 2215 { "bitvec_test", SQLITE_TESTCTRL_BITVEC_TEST }, 2216 { "fault_install", SQLITE_TESTCTRL_FAULT_INSTALL }, 2217 { "benign_malloc_hooks", SQLITE_TESTCTRL_BENIGN_MALLOC_HOOKS }, 2218 { "pending_byte", SQLITE_TESTCTRL_PENDING_BYTE }, 2219 { "assert", SQLITE_TESTCTRL_ASSERT }, 2220 { "always", SQLITE_TESTCTRL_ALWAYS }, 2221 { "reserve", SQLITE_TESTCTRL_RESERVE }, 2222 { "optimizations", SQLITE_TESTCTRL_OPTIMIZATIONS }, 2223 { "iskeyword", SQLITE_TESTCTRL_ISKEYWORD }, 2224 { "pghdrsz", SQLITE_TESTCTRL_PGHDRSZ }, 2225 { "scratchmalloc", SQLITE_TESTCTRL_SCRATCHMALLOC }, 2226 }; 2227 int testctrl = -1; 2228 int rc = 0; 2229 int i, n; 2230 open_db(p); 2231 2232 /* convert testctrl text option to value. allow any unique prefix 2233 ** of the option name, or a numerical value. */ 2234 n = strlen30(azArg[1]); 2235 for(i=0; i<(int)(sizeof(aCtrl)/sizeof(aCtrl[0])); i++){ 2236 if( strncmp(azArg[1], aCtrl[i].zCtrlName, n)==0 ){ 2237 if( testctrl<0 ){ 2238 testctrl = aCtrl[i].ctrlCode; 2239 }else{ 2240 fprintf(stderr, "ambiguous option name: \"%s\"\n", azArg[i]); 2241 testctrl = -1; 2242 break; 2243 } 2244 } 2245 } 2246 if( testctrl<0 ) testctrl = atoi(azArg[1]); 2247 if( (testctrl<SQLITE_TESTCTRL_FIRST) || (testctrl>SQLITE_TESTCTRL_LAST) ){ 2248 fprintf(stderr,"Error: invalid testctrl option: %s\n", azArg[1]); 2249 }else{ 2250 switch(testctrl){ 2251 2252 /* sqlite3_test_control(int, db, int) */ 2253 case SQLITE_TESTCTRL_OPTIMIZATIONS: 2254 case SQLITE_TESTCTRL_RESERVE: 2255 if( nArg==3 ){ 2256 int opt = (int)strtol(azArg[2], 0, 0); 2257 rc = sqlite3_test_control(testctrl, p->db, opt); 2258 printf("%d (0x%08x)\n", rc, rc); 2259 } else { 2260 fprintf(stderr,"Error: testctrl %s takes a single int option\n", 2261 azArg[1]); 2262 } 2263 break; 2264 2265 /* sqlite3_test_control(int) */ 2266 case SQLITE_TESTCTRL_PRNG_SAVE: 2267 case SQLITE_TESTCTRL_PRNG_RESTORE: 2268 case SQLITE_TESTCTRL_PRNG_RESET: 2269 case SQLITE_TESTCTRL_PGHDRSZ: 2270 if( nArg==2 ){ 2271 rc = sqlite3_test_control(testctrl); 2272 printf("%d (0x%08x)\n", rc, rc); 2273 } else { 2274 fprintf(stderr,"Error: testctrl %s takes no options\n", azArg[1]); 2275 } 2276 break; 2277 2278 /* sqlite3_test_control(int, uint) */ 2279 case SQLITE_TESTCTRL_PENDING_BYTE: 2280 if( nArg==3 ){ 2281 unsigned int opt = (unsigned int)atoi(azArg[2]); 2282 rc = sqlite3_test_control(testctrl, opt); 2283 printf("%d (0x%08x)\n", rc, rc); 2284 } else { 2285 fprintf(stderr,"Error: testctrl %s takes a single unsigned" 2286 " int option\n", azArg[1]); 2287 } 2288 break; 2289 2290 /* sqlite3_test_control(int, int) */ 2291 case SQLITE_TESTCTRL_ASSERT: 2292 case SQLITE_TESTCTRL_ALWAYS: 2293 if( nArg==3 ){ 2294 int opt = atoi(azArg[2]); 2295 rc = sqlite3_test_control(testctrl, opt); 2296 printf("%d (0x%08x)\n", rc, rc); 2297 } else { 2298 fprintf(stderr,"Error: testctrl %s takes a single int option\n", 2299 azArg[1]); 2300 } 2301 break; 2302 2303 /* sqlite3_test_control(int, char *) */ 2304 #ifdef SQLITE_N_KEYWORD 2305 case SQLITE_TESTCTRL_ISKEYWORD: 2306 if( nArg==3 ){ 2307 const char *opt = azArg[2]; 2308 rc = sqlite3_test_control(testctrl, opt); 2309 printf("%d (0x%08x)\n", rc, rc); 2310 } else { 2311 fprintf(stderr,"Error: testctrl %s takes a single char * option\n", 2312 azArg[1]); 2313 } 2314 break; 2315 #endif 2316 2317 case SQLITE_TESTCTRL_BITVEC_TEST: 2318 case SQLITE_TESTCTRL_FAULT_INSTALL: 2319 case SQLITE_TESTCTRL_BENIGN_MALLOC_HOOKS: 2320 case SQLITE_TESTCTRL_SCRATCHMALLOC: 2321 default: 2322 fprintf(stderr,"Error: CLI support for testctrl %s not implemented\n", 2323 azArg[1]); 2324 break; 2325 } 2326 } 2327 }else 2328 2329 if( c=='t' && n>4 && strncmp(azArg[0], "timeout", n)==0 && nArg==2 ){ 2330 open_db(p); 2331 sqlite3_busy_timeout(p->db, atoi(azArg[1])); 2332 }else 2333 2334 if( HAS_TIMER && c=='t' && n>=5 && strncmp(azArg[0], "timer", n)==0 2335 && nArg==2 2336 ){ 2337 enableTimer = booleanValue(azArg[1]); 2338 }else 2339 2340 if( c=='v' && strncmp(azArg[0], "version", n)==0 ){ 2341 printf("SQLite %s %s\n", 2342 sqlite3_libversion(), sqlite3_sourceid()); 2343 }else 2344 2345 if( c=='w' && strncmp(azArg[0], "width", n)==0 && nArg>1 ){ 2346 int j; 2347 assert( nArg<=ArraySize(azArg) ); 2348 for(j=1; j<nArg && j<ArraySize(p->colWidth); j++){ 2349 p->colWidth[j-1] = atoi(azArg[j]); 2350 } 2351 }else 2352 2353 { 2354 fprintf(stderr, "Error: unknown command or invalid arguments: " 2355 " \"%s\". Enter \".help\" for help\n", azArg[0]); 2356 rc = 1; 2357 } 2358 2359 return rc; 2360 } 2361 2362 /* 2363 ** Return TRUE if a semicolon occurs anywhere in the first N characters 2364 ** of string z[]. 2365 */ 2366 static int _contains_semicolon(const char *z, int N){ 2367 int i; 2368 for(i=0; i<N; i++){ if( z[i]==';' ) return 1; } 2369 return 0; 2370 } 2371 2372 /* 2373 ** Test to see if a line consists entirely of whitespace. 2374 */ 2375 static int _all_whitespace(const char *z){ 2376 for(; *z; z++){ 2377 if( IsSpace(z[0]) ) continue; 2378 if( *z=='/' && z[1]=='*' ){ 2379 z += 2; 2380 while( *z && (*z!='*' || z[1]!='/') ){ z++; } 2381 if( *z==0 ) return 0; 2382 z++; 2383 continue; 2384 } 2385 if( *z=='-' && z[1]=='-' ){ 2386 z += 2; 2387 while( *z && *z!='\n' ){ z++; } 2388 if( *z==0 ) return 1; 2389 continue; 2390 } 2391 return 0; 2392 } 2393 return 1; 2394 } 2395 2396 /* 2397 ** Return TRUE if the line typed in is an SQL command terminator other 2398 ** than a semi-colon. The SQL Server style "go" command is understood 2399 ** as is the Oracle "/". 2400 */ 2401 static int _is_command_terminator(const char *zLine){ 2402 while( IsSpace(zLine[0]) ){ zLine++; }; 2403 if( zLine[0]=='/' && _all_whitespace(&zLine[1]) ){ 2404 return 1; /* Oracle */ 2405 } 2406 if( ToLower(zLine[0])=='g' && ToLower(zLine[1])=='o' 2407 && _all_whitespace(&zLine[2]) ){ 2408 return 1; /* SQL Server */ 2409 } 2410 return 0; 2411 } 2412 2413 /* 2414 ** Return true if zSql is a complete SQL statement. Return false if it 2415 ** ends in the middle of a string literal or C-style comment. 2416 */ 2417 static int _is_complete(char *zSql, int nSql){ 2418 int rc; 2419 if( zSql==0 ) return 1; 2420 zSql[nSql] = ';'; 2421 zSql[nSql+1] = 0; 2422 rc = sqlite3_complete(zSql); 2423 zSql[nSql] = 0; 2424 return rc; 2425 } 2426 2427 /* 2428 ** Read input from *in and process it. If *in==0 then input 2429 ** is interactive - the user is typing it it. Otherwise, input 2430 ** is coming from a file or device. A prompt is issued and history 2431 ** is saved only if input is interactive. An interrupt signal will 2432 ** cause this routine to exit immediately, unless input is interactive. 2433 ** 2434 ** Return the number of errors. 2435 */ 2436 static int process_input(struct callback_data *p, FILE *in){ 2437 char *zLine = 0; 2438 char *zSql = 0; 2439 int nSql = 0; 2440 int nSqlPrior = 0; 2441 char *zErrMsg; 2442 int rc; 2443 int errCnt = 0; 2444 int lineno = 0; 2445 int startline = 0; 2446 2447 while( errCnt==0 || !bail_on_error || (in==0 && stdin_is_interactive) ){ 2448 fflush(p->out); 2449 free(zLine); 2450 zLine = one_input_line(zSql, in); 2451 if( zLine==0 ){ 2452 break; /* We have reached EOF */ 2453 } 2454 if( seenInterrupt ){ 2455 if( in!=0 ) break; 2456 seenInterrupt = 0; 2457 } 2458 lineno++; 2459 if( (zSql==0 || zSql[0]==0) && _all_whitespace(zLine) ) continue; 2460 if( zLine && zLine[0]=='.' && nSql==0 ){ 2461 if( p->echoOn ) printf("%s\n", zLine); 2462 rc = do_meta_command(zLine, p); 2463 if( rc==2 ){ /* exit requested */ 2464 break; 2465 }else if( rc ){ 2466 errCnt++; 2467 } 2468 continue; 2469 } 2470 if( _is_command_terminator(zLine) && _is_complete(zSql, nSql) ){ 2471 memcpy(zLine,";",2); 2472 } 2473 nSqlPrior = nSql; 2474 if( zSql==0 ){ 2475 int i; 2476 for(i=0; zLine[i] && IsSpace(zLine[i]); i++){} 2477 if( zLine[i]!=0 ){ 2478 nSql = strlen30(zLine); 2479 zSql = malloc( nSql+3 ); 2480 if( zSql==0 ){ 2481 fprintf(stderr, "Error: out of memory\n"); 2482 exit(1); 2483 } 2484 memcpy(zSql, zLine, nSql+1); 2485 startline = lineno; 2486 } 2487 }else{ 2488 int len = strlen30(zLine); 2489 zSql = realloc( zSql, nSql + len + 4 ); 2490 if( zSql==0 ){ 2491 fprintf(stderr,"Error: out of memory\n"); 2492 exit(1); 2493 } 2494 zSql[nSql++] = '\n'; 2495 memcpy(&zSql[nSql], zLine, len+1); 2496 nSql += len; 2497 } 2498 if( zSql && _contains_semicolon(&zSql[nSqlPrior], nSql-nSqlPrior) 2499 && sqlite3_complete(zSql) ){ 2500 p->cnt = 0; 2501 open_db(p); 2502 BEGIN_TIMER; 2503 rc = shell_exec(p->db, zSql, shell_callback, p, &zErrMsg); 2504 END_TIMER; 2505 if( rc || zErrMsg ){ 2506 char zPrefix[100]; 2507 if( in!=0 || !stdin_is_interactive ){ 2508 sqlite3_snprintf(sizeof(zPrefix), zPrefix, 2509 "Error: near line %d:", startline); 2510 }else{ 2511 sqlite3_snprintf(sizeof(zPrefix), zPrefix, "Error:"); 2512 } 2513 if( zErrMsg!=0 ){ 2514 fprintf(stderr, "%s %s\n", zPrefix, zErrMsg); 2515 sqlite3_free(zErrMsg); 2516 zErrMsg = 0; 2517 }else{ 2518 fprintf(stderr, "%s %s\n", zPrefix, sqlite3_errmsg(p->db)); 2519 } 2520 errCnt++; 2521 } 2522 free(zSql); 2523 zSql = 0; 2524 nSql = 0; 2525 } 2526 } 2527 if( zSql ){ 2528 if( !_all_whitespace(zSql) ){ 2529 fprintf(stderr, "Error: incomplete SQL: %s\n", zSql); 2530 } 2531 free(zSql); 2532 } 2533 free(zLine); 2534 return errCnt; 2535 } 2536 2537 /* 2538 ** Return a pathname which is the user's home directory. A 2539 ** 0 return indicates an error of some kind. Space to hold the 2540 ** resulting string is obtained from malloc(). The calling 2541 ** function should free the result. 2542 */ 2543 static char *find_home_dir(void){ 2544 char *home_dir = NULL; 2545 2546 #if !defined(_WIN32) && !defined(WIN32) && !defined(__OS2__) && !defined(_WIN32_WCE) && !defined(__RTP__) && !defined(_WRS_KERNEL) 2547 struct passwd *pwent; 2548 uid_t uid = getuid(); 2549 if( (pwent=getpwuid(uid)) != NULL) { 2550 home_dir = pwent->pw_dir; 2551 } 2552 #endif 2553 2554 #if defined(_WIN32_WCE) 2555 /* Windows CE (arm-wince-mingw32ce-gcc) does not provide getenv() 2556 */ 2557 home_dir = strdup("/"); 2558 #else 2559 2560 #if defined(_WIN32) || defined(WIN32) || defined(__OS2__) 2561 if (!home_dir) { 2562 home_dir = getenv("USERPROFILE"); 2563 } 2564 #endif 2565 2566 if (!home_dir) { 2567 home_dir = getenv("HOME"); 2568 } 2569 2570 #if defined(_WIN32) || defined(WIN32) || defined(__OS2__) 2571 if (!home_dir) { 2572 char *zDrive, *zPath; 2573 int n; 2574 zDrive = getenv("HOMEDRIVE"); 2575 zPath = getenv("HOMEPATH"); 2576 if( zDrive && zPath ){ 2577 n = strlen30(zDrive) + strlen30(zPath) + 1; 2578 home_dir = malloc( n ); 2579 if( home_dir==0 ) return 0; 2580 sqlite3_snprintf(n, home_dir, "%s%s", zDrive, zPath); 2581 return home_dir; 2582 } 2583 home_dir = "c:\\"; 2584 } 2585 #endif 2586 2587 #endif /* !_WIN32_WCE */ 2588 2589 if( home_dir ){ 2590 int n = strlen30(home_dir) + 1; 2591 char *z = malloc( n ); 2592 if( z ) memcpy(z, home_dir, n); 2593 home_dir = z; 2594 } 2595 2596 return home_dir; 2597 } 2598 2599 /* 2600 ** Read input from the file given by sqliterc_override. Or if that 2601 ** parameter is NULL, take input from ~/.sqliterc 2602 ** 2603 ** Returns the number of errors. 2604 */ 2605 static int process_sqliterc( 2606 struct callback_data *p, /* Configuration data */ 2607 const char *sqliterc_override /* Name of config file. NULL to use default */ 2608 ){ 2609 char *home_dir = NULL; 2610 const char *sqliterc = sqliterc_override; 2611 char *zBuf = 0; 2612 FILE *in = NULL; 2613 int nBuf; 2614 int rc = 0; 2615 2616 if (sqliterc == NULL) { 2617 home_dir = find_home_dir(); 2618 if( home_dir==0 ){ 2619 #if !defined(__RTP__) && !defined(_WRS_KERNEL) 2620 fprintf(stderr,"%s: Error: cannot locate your home directory\n", Argv0); 2621 #endif 2622 return 1; 2623 } 2624 nBuf = strlen30(home_dir) + 16; 2625 zBuf = malloc( nBuf ); 2626 if( zBuf==0 ){ 2627 fprintf(stderr,"%s: Error: out of memory\n",Argv0); 2628 return 1; 2629 } 2630 sqlite3_snprintf(nBuf, zBuf,"%s/.sqliterc",home_dir); 2631 free(home_dir); 2632 sqliterc = (const char*)zBuf; 2633 } 2634 in = fopen(sqliterc,"rb"); 2635 if( in ){ 2636 if( stdin_is_interactive ){ 2637 fprintf(stderr,"-- Loading resources from %s\n",sqliterc); 2638 } 2639 rc = process_input(p,in); 2640 fclose(in); 2641 } 2642 free(zBuf); 2643 return rc; 2644 } 2645 2646 /* 2647 ** Show available command line options 2648 */ 2649 static const char zOptions[] = 2650 " -help show this message\n" 2651 " -init filename read/process named file\n" 2652 " -echo print commands before execution\n" 2653 " -[no]header turn headers on or off\n" 2654 " -bail stop after hitting an error\n" 2655 " -interactive force interactive I/O\n" 2656 " -batch force batch I/O\n" 2657 " -column set output mode to 'column'\n" 2658 " -csv set output mode to 'csv'\n" 2659 " -html set output mode to HTML\n" 2660 " -line set output mode to 'line'\n" 2661 " -list set output mode to 'list'\n" 2662 " -separator 'x' set output field separator (|)\n" 2663 " -stats print memory stats before each finalize\n" 2664 " -nullvalue 'text' set text string for NULL values\n" 2665 " -version show SQLite version\n" 2666 " -vfs NAME use NAME as the default VFS\n" 2667 #ifdef SQLITE_ENABLE_VFSTRACE 2668 " -vfstrace enable tracing of all VFS calls\n" 2669 #endif 2670 #ifdef SQLITE_ENABLE_MULTIPLEX 2671 " -multiplex enable the multiplexor VFS\n" 2672 #endif 2673 ; 2674 static void usage(int showDetail){ 2675 fprintf(stderr, 2676 "Usage: %s [OPTIONS] FILENAME [SQL]\n" 2677 "FILENAME is the name of an SQLite database. A new database is created\n" 2678 "if the file does not previously exist.\n", Argv0); 2679 if( showDetail ){ 2680 fprintf(stderr, "OPTIONS include:\n%s", zOptions); 2681 }else{ 2682 fprintf(stderr, "Use the -help option for additional information\n"); 2683 } 2684 exit(1); 2685 } 2686 2687 /* 2688 ** Initialize the state information in data 2689 */ 2690 static void main_init(struct callback_data *data) { 2691 memset(data, 0, sizeof(*data)); 2692 data->mode = MODE_List; 2693 memcpy(data->separator,"|", 2); 2694 data->showHeader = 0; 2695 sqlite3_config(SQLITE_CONFIG_URI, 1); 2696 sqlite3_config(SQLITE_CONFIG_LOG, shellLog, data); 2697 sqlite3_snprintf(sizeof(mainPrompt), mainPrompt,"sqlite> "); 2698 sqlite3_snprintf(sizeof(continuePrompt), continuePrompt," ...> "); 2699 sqlite3_config(SQLITE_CONFIG_SINGLETHREAD); 2700 } 2701 2702 int main(int argc, char **argv){ 2703 char *zErrMsg = 0; 2704 struct callback_data data; 2705 const char *zInitFile = 0; 2706 char *zFirstCmd = 0; 2707 int i; 2708 int rc = 0; 2709 2710 if( strcmp(sqlite3_sourceid(),SQLITE_SOURCE_ID)!=0 ){ 2711 fprintf(stderr, "SQLite header and source version mismatch\n%s\n%s\n", 2712 sqlite3_sourceid(), SQLITE_SOURCE_ID); 2713 exit(1); 2714 } 2715 Argv0 = argv[0]; 2716 main_init(&data); 2717 stdin_is_interactive = isatty(0); 2718 2719 /* Make sure we have a valid signal handler early, before anything 2720 ** else is done. 2721 */ 2722 #ifdef SIGINT 2723 signal(SIGINT, interrupt_handler); 2724 #endif 2725 2726 /* Do an initial pass through the command-line argument to locate 2727 ** the name of the database file, the name of the initialization file, 2728 ** the size of the alternative malloc heap, 2729 ** and the first command to execute. 2730 */ 2731 for(i=1; i<argc-1; i++){ 2732 char *z; 2733 if( argv[i][0]!='-' ) break; 2734 z = argv[i]; 2735 if( z[0]=='-' && z[1]=='-' ) z++; 2736 if( strcmp(argv[i],"-separator")==0 || strcmp(argv[i],"-nullvalue")==0 ){ 2737 i++; 2738 }else if( strcmp(argv[i],"-init")==0 ){ 2739 i++; 2740 zInitFile = argv[i]; 2741 /* Need to check for batch mode here to so we can avoid printing 2742 ** informational messages (like from process_sqliterc) before 2743 ** we do the actual processing of arguments later in a second pass. 2744 */ 2745 }else if( strcmp(argv[i],"-batch")==0 ){ 2746 stdin_is_interactive = 0; 2747 }else if( strcmp(argv[i],"-heap")==0 ){ 2748 int j, c; 2749 const char *zSize; 2750 sqlite3_int64 szHeap; 2751 2752 zSize = argv[++i]; 2753 szHeap = atoi(zSize); 2754 for(j=0; (c = zSize[j])!=0; j++){ 2755 if( c=='M' ){ szHeap *= 1000000; break; } 2756 if( c=='K' ){ szHeap *= 1000; break; } 2757 if( c=='G' ){ szHeap *= 1000000000; break; } 2758 } 2759 if( szHeap>0x7fff0000 ) szHeap = 0x7fff0000; 2760 #if defined(SQLITE_ENABLE_MEMSYS3) || defined(SQLITE_ENABLE_MEMSYS5) 2761 sqlite3_config(SQLITE_CONFIG_HEAP, malloc((int)szHeap), (int)szHeap, 64); 2762 #endif 2763 #ifdef SQLITE_ENABLE_VFSTRACE 2764 }else if( strcmp(argv[i],"-vfstrace")==0 ){ 2765 extern int vfstrace_register( 2766 const char *zTraceName, 2767 const char *zOldVfsName, 2768 int (*xOut)(const char*,void*), 2769 void *pOutArg, 2770 int makeDefault 2771 ); 2772 vfstrace_register("trace",0,(int(*)(const char*,void*))fputs,stderr,1); 2773 #endif 2774 #ifdef SQLITE_ENABLE_MULTIPLEX 2775 }else if( strcmp(argv[i],"-multiplex")==0 ){ 2776 extern int sqlite3_multiple_initialize(const char*,int); 2777 sqlite3_multiplex_initialize(0, 1); 2778 #endif 2779 }else if( strcmp(argv[i],"-vfs")==0 ){ 2780 sqlite3_vfs *pVfs = sqlite3_vfs_find(argv[++i]); 2781 if( pVfs ){ 2782 sqlite3_vfs_register(pVfs, 1); 2783 }else{ 2784 fprintf(stderr, "no such VFS: \"%s\"\n", argv[i]); 2785 exit(1); 2786 } 2787 } 2788 } 2789 if( i<argc ){ 2790 #if defined(SQLITE_OS_OS2) && SQLITE_OS_OS2 2791 data.zDbFilename = (const char *)convertCpPathToUtf8( argv[i++] ); 2792 #else 2793 data.zDbFilename = argv[i++]; 2794 #endif 2795 }else{ 2796 #ifndef SQLITE_OMIT_MEMORYDB 2797 data.zDbFilename = ":memory:"; 2798 #else 2799 data.zDbFilename = 0; 2800 #endif 2801 } 2802 if( i<argc ){ 2803 zFirstCmd = argv[i++]; 2804 } 2805 if( i<argc ){ 2806 fprintf(stderr,"%s: Error: too many options: \"%s\"\n", Argv0, argv[i]); 2807 fprintf(stderr,"Use -help for a list of options.\n"); 2808 return 1; 2809 } 2810 data.out = stdout; 2811 2812 #ifdef SQLITE_OMIT_MEMORYDB 2813 if( data.zDbFilename==0 ){ 2814 fprintf(stderr,"%s: Error: no database filename specified\n", Argv0); 2815 return 1; 2816 } 2817 #endif 2818 2819 /* Go ahead and open the database file if it already exists. If the 2820 ** file does not exist, delay opening it. This prevents empty database 2821 ** files from being created if a user mistypes the database name argument 2822 ** to the sqlite command-line tool. 2823 */ 2824 if( access(data.zDbFilename, 0)==0 ){ 2825 open_db(&data); 2826 } 2827 2828 /* Process the initialization file if there is one. If no -init option 2829 ** is given on the command line, look for a file named ~/.sqliterc and 2830 ** try to process it. 2831 */ 2832 rc = process_sqliterc(&data,zInitFile); 2833 if( rc>0 ){ 2834 return rc; 2835 } 2836 2837 /* Make a second pass through the command-line argument and set 2838 ** options. This second pass is delayed until after the initialization 2839 ** file is processed so that the command-line arguments will override 2840 ** settings in the initialization file. 2841 */ 2842 for(i=1; i<argc && argv[i][0]=='-'; i++){ 2843 char *z = argv[i]; 2844 if( z[1]=='-' ){ z++; } 2845 if( strcmp(z,"-init")==0 ){ 2846 i++; 2847 }else if( strcmp(z,"-html")==0 ){ 2848 data.mode = MODE_Html; 2849 }else if( strcmp(z,"-list")==0 ){ 2850 data.mode = MODE_List; 2851 }else if( strcmp(z,"-line")==0 ){ 2852 data.mode = MODE_Line; 2853 }else if( strcmp(z,"-column")==0 ){ 2854 data.mode = MODE_Column; 2855 }else if( strcmp(z,"-csv")==0 ){ 2856 data.mode = MODE_Csv; 2857 memcpy(data.separator,",",2); 2858 }else if( strcmp(z,"-separator")==0 ){ 2859 i++; 2860 if(i>=argc){ 2861 fprintf(stderr,"%s: Error: missing argument for option: %s\n", Argv0, z); 2862 fprintf(stderr,"Use -help for a list of options.\n"); 2863 return 1; 2864 } 2865 sqlite3_snprintf(sizeof(data.separator), data.separator, 2866 "%.*s",(int)sizeof(data.separator)-1,argv[i]); 2867 }else if( strcmp(z,"-nullvalue")==0 ){ 2868 i++; 2869 if(i>=argc){ 2870 fprintf(stderr,"%s: Error: missing argument for option: %s\n", Argv0, z); 2871 fprintf(stderr,"Use -help for a list of options.\n"); 2872 return 1; 2873 } 2874 sqlite3_snprintf(sizeof(data.nullvalue), data.nullvalue, 2875 "%.*s",(int)sizeof(data.nullvalue)-1,argv[i]); 2876 }else if( strcmp(z,"-header")==0 ){ 2877 data.showHeader = 1; 2878 }else if( strcmp(z,"-noheader")==0 ){ 2879 data.showHeader = 0; 2880 }else if( strcmp(z,"-echo")==0 ){ 2881 data.echoOn = 1; 2882 }else if( strcmp(z,"-stats")==0 ){ 2883 data.statsOn = 1; 2884 }else if( strcmp(z,"-bail")==0 ){ 2885 bail_on_error = 1; 2886 }else if( strcmp(z,"-version")==0 ){ 2887 printf("%s %s\n", sqlite3_libversion(), sqlite3_sourceid()); 2888 return 0; 2889 }else if( strcmp(z,"-interactive")==0 ){ 2890 stdin_is_interactive = 1; 2891 }else if( strcmp(z,"-batch")==0 ){ 2892 stdin_is_interactive = 0; 2893 }else if( strcmp(z,"-heap")==0 ){ 2894 i++; 2895 }else if( strcmp(z,"-vfs")==0 ){ 2896 i++; 2897 #ifdef SQLITE_ENABLE_VFSTRACE 2898 }else if( strcmp(z,"-vfstrace")==0 ){ 2899 i++; 2900 #endif 2901 #ifdef SQLITE_ENABLE_MULTIPLEX 2902 }else if( strcmp(z,"-multiplex")==0 ){ 2903 i++; 2904 #endif 2905 }else if( strcmp(z,"-help")==0 || strcmp(z, "--help")==0 ){ 2906 usage(1); 2907 }else{ 2908 fprintf(stderr,"%s: Error: unknown option: %s\n", Argv0, z); 2909 fprintf(stderr,"Use -help for a list of options.\n"); 2910 return 1; 2911 } 2912 } 2913 2914 if( zFirstCmd ){ 2915 /* Run just the command that follows the database name 2916 */ 2917 if( zFirstCmd[0]=='.' ){ 2918 rc = do_meta_command(zFirstCmd, &data); 2919 }else{ 2920 open_db(&data); 2921 rc = shell_exec(data.db, zFirstCmd, shell_callback, &data, &zErrMsg); 2922 if( zErrMsg!=0 ){ 2923 fprintf(stderr,"Error: %s\n", zErrMsg); 2924 return rc!=0 ? rc : 1; 2925 }else if( rc!=0 ){ 2926 fprintf(stderr,"Error: unable to process SQL \"%s\"\n", zFirstCmd); 2927 return rc; 2928 } 2929 } 2930 }else{ 2931 /* Run commands received from standard input 2932 */ 2933 if( stdin_is_interactive ){ 2934 char *zHome; 2935 char *zHistory = 0; 2936 int nHistory; 2937 printf( 2938 "SQLite version %s %.19s\n" 2939 "Enter \".help\" for instructions\n" 2940 "Enter SQL statements terminated with a \";\"\n", 2941 sqlite3_libversion(), sqlite3_sourceid() 2942 ); 2943 zHome = find_home_dir(); 2944 if( zHome ){ 2945 nHistory = strlen30(zHome) + 20; 2946 if( (zHistory = malloc(nHistory))!=0 ){ 2947 sqlite3_snprintf(nHistory, zHistory,"%s/.sqlite_history", zHome); 2948 } 2949 } 2950 #if defined(HAVE_READLINE) && HAVE_READLINE==1 2951 if( zHistory ) read_history(zHistory); 2952 #endif 2953 rc = process_input(&data, 0); 2954 if( zHistory ){ 2955 stifle_history(100); 2956 write_history(zHistory); 2957 free(zHistory); 2958 } 2959 free(zHome); 2960 }else{ 2961 rc = process_input(&data, stdin); 2962 } 2963 } 2964 set_table_name(&data, 0); 2965 if( data.db ){ 2966 sqlite3_close(data.db); 2967 } 2968 return rc; 2969 } 2970