shell.c revision 7da544e045fff9ba2343adce7072846647809355
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) 16/* This needs to come before any includes for MSVC compiler */ 17#define _CRT_SECURE_NO_WARNINGS 18#endif 19 20#include <stdlib.h> 21#include <string.h> 22#include <stdio.h> 23#include <assert.h> 24#include "sqlite3.h" 25#include <ctype.h> 26#include <stdarg.h> 27 28#if !defined(_WIN32) && !defined(WIN32) && !defined(__OS2__) 29# include <signal.h> 30# if !defined(__RTP__) && !defined(_WRS_KERNEL) 31# include <pwd.h> 32# endif 33# include <unistd.h> 34# include <sys/types.h> 35#endif 36 37#ifdef __OS2__ 38# include <unistd.h> 39#endif 40 41#if defined(HAVE_READLINE) && HAVE_READLINE==1 42# include <readline/readline.h> 43# include <readline/history.h> 44#else 45# define readline(p) local_getline(p,stdin) 46# define add_history(X) 47# define read_history(X) 48# define write_history(X) 49# define stifle_history(X) 50#endif 51 52#if defined(_WIN32) || defined(WIN32) 53# include <io.h> 54#define isatty(h) _isatty(h) 55#define access(f,m) _access((f),(m)) 56#else 57/* Make sure isatty() has a prototype. 58*/ 59extern int isatty(); 60#endif 61 62#if defined(_WIN32_WCE) 63/* Windows CE (arm-wince-mingw32ce-gcc) does not provide isatty() 64 * thus we always assume that we have a console. That can be 65 * overridden with the -batch command line option. 66 */ 67#define isatty(x) 1 68#endif 69 70#if !defined(_WIN32) && !defined(WIN32) && !defined(__OS2__) && !defined(__RTP__) && !defined(_WRS_KERNEL) 71#include <sys/time.h> 72#include <sys/resource.h> 73 74/* Saved resource information for the beginning of an operation */ 75static struct rusage sBegin; 76 77/* True if the timer is enabled */ 78static int enableTimer = 0; 79 80/* 81** Begin timing an operation 82*/ 83static void beginTimer(void){ 84 if( enableTimer ){ 85 getrusage(RUSAGE_SELF, &sBegin); 86 } 87} 88 89/* Return the difference of two time_structs in seconds */ 90static double timeDiff(struct timeval *pStart, struct timeval *pEnd){ 91 return (pEnd->tv_usec - pStart->tv_usec)*0.000001 + 92 (double)(pEnd->tv_sec - pStart->tv_sec); 93} 94 95/* 96** Print the timing results. 97*/ 98static void endTimer(void){ 99 if( enableTimer ){ 100 struct rusage sEnd; 101 getrusage(RUSAGE_SELF, &sEnd); 102 printf("CPU Time: user %f sys %f\n", 103 timeDiff(&sBegin.ru_utime, &sEnd.ru_utime), 104 timeDiff(&sBegin.ru_stime, &sEnd.ru_stime)); 105 } 106} 107 108#define BEGIN_TIMER beginTimer() 109#define END_TIMER endTimer() 110#define HAS_TIMER 1 111 112#elif (defined(_WIN32) || defined(WIN32)) 113 114#include <windows.h> 115 116/* Saved resource information for the beginning of an operation */ 117static HANDLE hProcess; 118static FILETIME ftKernelBegin; 119static FILETIME ftUserBegin; 120typedef BOOL (WINAPI *GETPROCTIMES)(HANDLE, LPFILETIME, LPFILETIME, LPFILETIME, LPFILETIME); 121static GETPROCTIMES getProcessTimesAddr = NULL; 122 123/* True if the timer is enabled */ 124static int enableTimer = 0; 125 126/* 127** Check to see if we have timer support. Return 1 if necessary 128** support found (or found previously). 129*/ 130static int hasTimer(void){ 131 if( getProcessTimesAddr ){ 132 return 1; 133 } else { 134 /* GetProcessTimes() isn't supported in WIN95 and some other Windows versions. 135 ** See if the version we are running on has it, and if it does, save off 136 ** a pointer to it and the current process handle. 137 */ 138 hProcess = GetCurrentProcess(); 139 if( hProcess ){ 140 HINSTANCE hinstLib = LoadLibrary(TEXT("Kernel32.dll")); 141 if( NULL != hinstLib ){ 142 getProcessTimesAddr = (GETPROCTIMES) GetProcAddress(hinstLib, "GetProcessTimes"); 143 if( NULL != getProcessTimesAddr ){ 144 return 1; 145 } 146 FreeLibrary(hinstLib); 147 } 148 } 149 } 150 return 0; 151} 152 153/* 154** Begin timing an operation 155*/ 156static void beginTimer(void){ 157 if( enableTimer && getProcessTimesAddr ){ 158 FILETIME ftCreation, ftExit; 159 getProcessTimesAddr(hProcess, &ftCreation, &ftExit, &ftKernelBegin, &ftUserBegin); 160 } 161} 162 163/* Return the difference of two FILETIME structs in seconds */ 164static double timeDiff(FILETIME *pStart, FILETIME *pEnd){ 165 sqlite_int64 i64Start = *((sqlite_int64 *) pStart); 166 sqlite_int64 i64End = *((sqlite_int64 *) pEnd); 167 return (double) ((i64End - i64Start) / 10000000.0); 168} 169 170/* 171** Print the timing results. 172*/ 173static void endTimer(void){ 174 if( enableTimer && getProcessTimesAddr){ 175 FILETIME ftCreation, ftExit, ftKernelEnd, ftUserEnd; 176 getProcessTimesAddr(hProcess, &ftCreation, &ftExit, &ftKernelEnd, &ftUserEnd); 177 printf("CPU Time: user %f sys %f\n", 178 timeDiff(&ftUserBegin, &ftUserEnd), 179 timeDiff(&ftKernelBegin, &ftKernelEnd)); 180 } 181} 182 183#define BEGIN_TIMER beginTimer() 184#define END_TIMER endTimer() 185#define HAS_TIMER hasTimer() 186 187#else 188#define BEGIN_TIMER 189#define END_TIMER 190#define HAS_TIMER 0 191#endif 192 193/* 194** Used to prevent warnings about unused parameters 195*/ 196#define UNUSED_PARAMETER(x) (void)(x) 197 198 199/************************************************************************** 200*************************************************************************** 201** Begin genfkey logic. 202*/ 203#if !defined(SQLITE_OMIT_VIRTUALTABLE) && !defined SQLITE_OMIT_SUBQUERY 204 205#define GENFKEY_ERROR 1 206#define GENFKEY_DROPTRIGGER 2 207#define GENFKEY_CREATETRIGGER 3 208static int genfkey_create_triggers(sqlite3 *, const char *, void *, 209 int (*)(void *, int, const char *) 210); 211 212struct GenfkeyCb { 213 void *pCtx; 214 int eType; 215 int (*xData)(void *, int, const char *); 216}; 217typedef struct GenfkeyCb GenfkeyCb; 218 219/* The code in this file defines a sqlite3 virtual-table module that 220** provides a read-only view of the current database schema. There is one 221** row in the schema table for each column in the database schema. 222*/ 223#define SCHEMA \ 224"CREATE TABLE x(" \ 225 "database," /* Name of database (i.e. main, temp etc.) */ \ 226 "tablename," /* Name of table */ \ 227 "cid," /* Column number (from left-to-right, 0 upward) */ \ 228 "name," /* Column name */ \ 229 "type," /* Specified type (i.e. VARCHAR(32)) */ \ 230 "not_null," /* Boolean. True if NOT NULL was specified */ \ 231 "dflt_value," /* Default value for this column */ \ 232 "pk" /* True if this column is part of the primary key */ \ 233")" 234 235#define SCHEMA2 \ 236"CREATE TABLE x(" \ 237 "database," /* Name of database (i.e. main, temp etc.) */ \ 238 "from_tbl," /* Name of table */ \ 239 "fkid," \ 240 "seq," \ 241 "to_tbl," \ 242 "from_col," \ 243 "to_col," \ 244 "on_update," \ 245 "on_delete," \ 246 "match" \ 247")" 248 249#define SCHEMA3 \ 250"CREATE TABLE x(" \ 251 "database," /* Name of database (i.e. main, temp etc.) */ \ 252 "tablename," /* Name of table */ \ 253 "seq," \ 254 "name," \ 255 "isunique" \ 256")" 257 258#define SCHEMA4 \ 259"CREATE TABLE x(" \ 260 "database," /* Name of database (i.e. main, temp etc.) */ \ 261 "indexname," /* Name of table */ \ 262 "seqno," \ 263 "cid," \ 264 "name" \ 265")" 266 267#define SCHEMA5 \ 268"CREATE TABLE x(" \ 269 "database," /* Name of database (i.e. main, temp etc.) */ \ 270 "triggername," /* Name of trigger */ \ 271 "dummy" /* Unused */ \ 272")" 273 274typedef struct SchemaTable SchemaTable; 275static struct SchemaTable { 276 const char *zName; 277 const char *zObject; 278 const char *zPragma; 279 const char *zSchema; 280} aSchemaTable[] = { 281 { "table_info", "table", "PRAGMA %Q.table_info(%Q)", SCHEMA }, 282 { "foreign_key_list", "table", "PRAGMA %Q.foreign_key_list(%Q)", SCHEMA2 }, 283 { "index_list", "table", "PRAGMA %Q.index_list(%Q)", SCHEMA3 }, 284 { "index_info", "index", "PRAGMA %Q.index_info(%Q)", SCHEMA4 }, 285 { "trigger_list", "trigger", "SELECT 1", SCHEMA5 }, 286 { 0, 0, 0, 0 } 287}; 288 289typedef struct schema_vtab schema_vtab; 290typedef struct schema_cursor schema_cursor; 291 292/* A schema table object */ 293struct schema_vtab { 294 sqlite3_vtab base; 295 sqlite3 *db; 296 SchemaTable *pType; 297}; 298 299/* A schema table cursor object */ 300struct schema_cursor { 301 sqlite3_vtab_cursor base; 302 sqlite3_stmt *pDbList; 303 sqlite3_stmt *pTableList; 304 sqlite3_stmt *pColumnList; 305 int rowid; 306}; 307 308/* 309** Table destructor for the schema module. 310*/ 311static int schemaDestroy(sqlite3_vtab *pVtab){ 312 sqlite3_free(pVtab); 313 return 0; 314} 315 316/* 317** Table constructor for the schema module. 318*/ 319static int schemaCreate( 320 sqlite3 *db, 321 void *pAux, 322 int argc, const char *const*argv, 323 sqlite3_vtab **ppVtab, 324 char **pzErr 325){ 326 int rc = SQLITE_NOMEM; 327 schema_vtab *pVtab; 328 SchemaTable *pType = &aSchemaTable[0]; 329 330 UNUSED_PARAMETER(pzErr); 331 if( argc>3 ){ 332 int i; 333 pType = 0; 334 for(i=0; aSchemaTable[i].zName; i++){ 335 if( 0==strcmp(argv[3], aSchemaTable[i].zName) ){ 336 pType = &aSchemaTable[i]; 337 } 338 } 339 if( !pType ){ 340 return SQLITE_ERROR; 341 } 342 } 343 344 pVtab = sqlite3_malloc(sizeof(schema_vtab)); 345 if( pVtab ){ 346 memset(pVtab, 0, sizeof(schema_vtab)); 347 pVtab->db = (sqlite3 *)pAux; 348 pVtab->pType = pType; 349 rc = sqlite3_declare_vtab(db, pType->zSchema); 350 } 351 *ppVtab = (sqlite3_vtab *)pVtab; 352 return rc; 353} 354 355/* 356** Open a new cursor on the schema table. 357*/ 358static int schemaOpen(sqlite3_vtab *pVTab, sqlite3_vtab_cursor **ppCursor){ 359 int rc = SQLITE_NOMEM; 360 schema_cursor *pCur; 361 UNUSED_PARAMETER(pVTab); 362 pCur = sqlite3_malloc(sizeof(schema_cursor)); 363 if( pCur ){ 364 memset(pCur, 0, sizeof(schema_cursor)); 365 *ppCursor = (sqlite3_vtab_cursor *)pCur; 366 rc = SQLITE_OK; 367 } 368 return rc; 369} 370 371/* 372** Close a schema table cursor. 373*/ 374static int schemaClose(sqlite3_vtab_cursor *cur){ 375 schema_cursor *pCur = (schema_cursor *)cur; 376 sqlite3_finalize(pCur->pDbList); 377 sqlite3_finalize(pCur->pTableList); 378 sqlite3_finalize(pCur->pColumnList); 379 sqlite3_free(pCur); 380 return SQLITE_OK; 381} 382 383static void columnToResult(sqlite3_context *ctx, sqlite3_stmt *pStmt, int iCol){ 384 switch( sqlite3_column_type(pStmt, iCol) ){ 385 case SQLITE_NULL: 386 sqlite3_result_null(ctx); 387 break; 388 case SQLITE_INTEGER: 389 sqlite3_result_int64(ctx, sqlite3_column_int64(pStmt, iCol)); 390 break; 391 case SQLITE_FLOAT: 392 sqlite3_result_double(ctx, sqlite3_column_double(pStmt, iCol)); 393 break; 394 case SQLITE_TEXT: { 395 const char *z = (const char *)sqlite3_column_text(pStmt, iCol); 396 sqlite3_result_text(ctx, z, -1, SQLITE_TRANSIENT); 397 break; 398 } 399 } 400} 401 402/* 403** Retrieve a column of data. 404*/ 405static int schemaColumn(sqlite3_vtab_cursor *cur, sqlite3_context *ctx, int i){ 406 schema_cursor *pCur = (schema_cursor *)cur; 407 switch( i ){ 408 case 0: 409 columnToResult(ctx, pCur->pDbList, 1); 410 break; 411 case 1: 412 columnToResult(ctx, pCur->pTableList, 0); 413 break; 414 default: 415 columnToResult(ctx, pCur->pColumnList, i-2); 416 break; 417 } 418 return SQLITE_OK; 419} 420 421/* 422** Retrieve the current rowid. 423*/ 424static int schemaRowid(sqlite3_vtab_cursor *cur, sqlite_int64 *pRowid){ 425 schema_cursor *pCur = (schema_cursor *)cur; 426 *pRowid = pCur->rowid; 427 return SQLITE_OK; 428} 429 430static int finalize(sqlite3_stmt **ppStmt){ 431 int rc = sqlite3_finalize(*ppStmt); 432 *ppStmt = 0; 433 return rc; 434} 435 436static int schemaEof(sqlite3_vtab_cursor *cur){ 437 schema_cursor *pCur = (schema_cursor *)cur; 438 return (pCur->pDbList ? 0 : 1); 439} 440 441/* 442** Advance the cursor to the next row. 443*/ 444static int schemaNext(sqlite3_vtab_cursor *cur){ 445 int rc = SQLITE_OK; 446 schema_cursor *pCur = (schema_cursor *)cur; 447 schema_vtab *pVtab = (schema_vtab *)(cur->pVtab); 448 char *zSql = 0; 449 450 while( !pCur->pColumnList || SQLITE_ROW!=sqlite3_step(pCur->pColumnList) ){ 451 if( SQLITE_OK!=(rc = finalize(&pCur->pColumnList)) ) goto next_exit; 452 453 while( !pCur->pTableList || SQLITE_ROW!=sqlite3_step(pCur->pTableList) ){ 454 if( SQLITE_OK!=(rc = finalize(&pCur->pTableList)) ) goto next_exit; 455 456 assert(pCur->pDbList); 457 while( SQLITE_ROW!=sqlite3_step(pCur->pDbList) ){ 458 rc = finalize(&pCur->pDbList); 459 goto next_exit; 460 } 461 462 /* Set zSql to the SQL to pull the list of tables from the 463 ** sqlite_master (or sqlite_temp_master) table of the database 464 ** identfied by the row pointed to by the SQL statement pCur->pDbList 465 ** (iterating through a "PRAGMA database_list;" statement). 466 */ 467 if( sqlite3_column_int(pCur->pDbList, 0)==1 ){ 468 zSql = sqlite3_mprintf( 469 "SELECT name FROM sqlite_temp_master WHERE type=%Q", 470 pVtab->pType->zObject 471 ); 472 }else{ 473 sqlite3_stmt *pDbList = pCur->pDbList; 474 zSql = sqlite3_mprintf( 475 "SELECT name FROM %Q.sqlite_master WHERE type=%Q", 476 sqlite3_column_text(pDbList, 1), pVtab->pType->zObject 477 ); 478 } 479 if( !zSql ){ 480 rc = SQLITE_NOMEM; 481 goto next_exit; 482 } 483 484 rc = sqlite3_prepare(pVtab->db, zSql, -1, &pCur->pTableList, 0); 485 sqlite3_free(zSql); 486 if( rc!=SQLITE_OK ) goto next_exit; 487 } 488 489 /* Set zSql to the SQL to the table_info pragma for the table currently 490 ** identified by the rows pointed to by statements pCur->pDbList and 491 ** pCur->pTableList. 492 */ 493 zSql = sqlite3_mprintf(pVtab->pType->zPragma, 494 sqlite3_column_text(pCur->pDbList, 1), 495 sqlite3_column_text(pCur->pTableList, 0) 496 ); 497 498 if( !zSql ){ 499 rc = SQLITE_NOMEM; 500 goto next_exit; 501 } 502 rc = sqlite3_prepare(pVtab->db, zSql, -1, &pCur->pColumnList, 0); 503 sqlite3_free(zSql); 504 if( rc!=SQLITE_OK ) goto next_exit; 505 } 506 pCur->rowid++; 507 508next_exit: 509 /* TODO: Handle rc */ 510 return rc; 511} 512 513/* 514** Reset a schema table cursor. 515*/ 516static int schemaFilter( 517 sqlite3_vtab_cursor *pVtabCursor, 518 int idxNum, const char *idxStr, 519 int argc, sqlite3_value **argv 520){ 521 int rc; 522 schema_vtab *pVtab = (schema_vtab *)(pVtabCursor->pVtab); 523 schema_cursor *pCur = (schema_cursor *)pVtabCursor; 524 UNUSED_PARAMETER(idxNum); 525 UNUSED_PARAMETER(idxStr); 526 UNUSED_PARAMETER(argc); 527 UNUSED_PARAMETER(argv); 528 pCur->rowid = 0; 529 finalize(&pCur->pTableList); 530 finalize(&pCur->pColumnList); 531 finalize(&pCur->pDbList); 532 rc = sqlite3_prepare(pVtab->db,"SELECT 0, 'main'", -1, &pCur->pDbList, 0); 533 return (rc==SQLITE_OK ? schemaNext(pVtabCursor) : rc); 534} 535 536/* 537** Analyse the WHERE condition. 538*/ 539static int schemaBestIndex(sqlite3_vtab *tab, sqlite3_index_info *pIdxInfo){ 540 UNUSED_PARAMETER(tab); 541 UNUSED_PARAMETER(pIdxInfo); 542 return SQLITE_OK; 543} 544 545/* 546** A virtual table module that merely echos method calls into TCL 547** variables. 548*/ 549static sqlite3_module schemaModule = { 550 0, /* iVersion */ 551 schemaCreate, 552 schemaCreate, 553 schemaBestIndex, 554 schemaDestroy, 555 schemaDestroy, 556 schemaOpen, /* xOpen - open a cursor */ 557 schemaClose, /* xClose - close a cursor */ 558 schemaFilter, /* xFilter - configure scan constraints */ 559 schemaNext, /* xNext - advance a cursor */ 560 schemaEof, /* xEof */ 561 schemaColumn, /* xColumn - read data */ 562 schemaRowid, /* xRowid - read data */ 563 0, /* xUpdate */ 564 0, /* xBegin */ 565 0, /* xSync */ 566 0, /* xCommit */ 567 0, /* xRollback */ 568 0, /* xFindMethod */ 569 0, /* xRename */ 570}; 571 572/* 573** Extension load function. 574*/ 575static int installSchemaModule(sqlite3 *db, sqlite3 *sdb){ 576 sqlite3_create_module(db, "schema", &schemaModule, (void *)sdb); 577 return 0; 578} 579 580/* 581** sj(zValue, zJoin) 582** 583** The following block contains the implementation of an aggregate 584** function that returns a string. Each time the function is stepped, 585** it appends data to an internal buffer. When the aggregate is finalized, 586** the contents of the buffer are returned. 587** 588** The first time the aggregate is stepped the buffer is set to a copy 589** of the first argument. The second time and subsequent times it is 590** stepped a copy of the second argument is appended to the buffer, then 591** a copy of the first. 592** 593** Example: 594** 595** INSERT INTO t1(a) VALUES('1'); 596** INSERT INTO t1(a) VALUES('2'); 597** INSERT INTO t1(a) VALUES('3'); 598** SELECT sj(a, ', ') FROM t1; 599** 600** => "1, 2, 3" 601** 602*/ 603struct StrBuffer { 604 char *zBuf; 605}; 606typedef struct StrBuffer StrBuffer; 607static void joinFinalize(sqlite3_context *context){ 608 StrBuffer *p; 609 p = (StrBuffer *)sqlite3_aggregate_context(context, sizeof(StrBuffer)); 610 sqlite3_result_text(context, p->zBuf, -1, SQLITE_TRANSIENT); 611 sqlite3_free(p->zBuf); 612} 613static void joinStep( 614 sqlite3_context *context, 615 int argc, 616 sqlite3_value **argv 617){ 618 StrBuffer *p; 619 UNUSED_PARAMETER(argc); 620 p = (StrBuffer *)sqlite3_aggregate_context(context, sizeof(StrBuffer)); 621 if( p->zBuf==0 ){ 622 p->zBuf = sqlite3_mprintf("%s", sqlite3_value_text(argv[0])); 623 }else{ 624 char *zTmp = p->zBuf; 625 p->zBuf = sqlite3_mprintf("%s%s%s", 626 zTmp, sqlite3_value_text(argv[1]), sqlite3_value_text(argv[0]) 627 ); 628 sqlite3_free(zTmp); 629 } 630} 631 632/* 633** dq(zString) 634** 635** This scalar function accepts a single argument and interprets it as 636** a text value. The return value is the argument enclosed in double 637** quotes. If any double quote characters are present in the argument, 638** these are escaped. 639** 640** dq('the raven "Nevermore."') == '"the raven ""Nevermore."""' 641*/ 642static void doublequote( 643 sqlite3_context *context, 644 int argc, 645 sqlite3_value **argv 646){ 647 int ii; 648 char *zOut; 649 char *zCsr; 650 const char *zIn = (const char *)sqlite3_value_text(argv[0]); 651 int nIn = sqlite3_value_bytes(argv[0]); 652 653 UNUSED_PARAMETER(argc); 654 zOut = sqlite3_malloc(nIn*2+3); 655 zCsr = zOut; 656 *zCsr++ = '"'; 657 for(ii=0; ii<nIn; ii++){ 658 *zCsr++ = zIn[ii]; 659 if( zIn[ii]=='"' ){ 660 *zCsr++ = '"'; 661 } 662 } 663 *zCsr++ = '"'; 664 *zCsr++ = '\0'; 665 666 sqlite3_result_text(context, zOut, -1, SQLITE_TRANSIENT); 667 sqlite3_free(zOut); 668} 669 670/* 671** multireplace(zString, zSearch1, zReplace1, ...) 672*/ 673static void multireplace( 674 sqlite3_context *context, 675 int argc, 676 sqlite3_value **argv 677){ 678 int i = 0; 679 char *zOut = 0; 680 int nOut = 0; 681 int nMalloc = 0; 682 const char *zIn = (const char *)sqlite3_value_text(argv[0]); 683 int nIn = sqlite3_value_bytes(argv[0]); 684 685 while( i<nIn ){ 686 const char *zCopy = &zIn[i]; 687 int nCopy = 1; 688 int nReplace = 1; 689 int j; 690 for(j=1; j<(argc-1); j+=2){ 691 const char *z = (const char *)sqlite3_value_text(argv[j]); 692 int n = sqlite3_value_bytes(argv[j]); 693 if( n<=(nIn-i) && 0==strncmp(z, zCopy, n) ){ 694 zCopy = (const char *)sqlite3_value_text(argv[j+1]); 695 nCopy = sqlite3_value_bytes(argv[j+1]); 696 nReplace = n; 697 break; 698 } 699 } 700 if( (nOut+nCopy)>nMalloc ){ 701 char *zNew; 702 nMalloc = 16 + (nOut+nCopy)*2; 703 zNew = (char*)sqlite3_realloc(zOut, nMalloc); 704 if( zNew==0 ){ 705 sqlite3_result_error_nomem(context); 706 return; 707 }else{ 708 zOut = zNew; 709 } 710 } 711 assert( nMalloc>=(nOut+nCopy) ); 712 memcpy(&zOut[nOut], zCopy, nCopy); 713 i += nReplace; 714 nOut += nCopy; 715 } 716 717 sqlite3_result_text(context, zOut, nOut, SQLITE_TRANSIENT); 718 sqlite3_free(zOut); 719} 720 721/* 722** A callback for sqlite3_exec() invokes the callback specified by the 723** GenfkeyCb structure pointed to by the void* passed as the first argument. 724*/ 725static int invokeCallback(void *p, int nArg, char **azArg, char **azCol){ 726 GenfkeyCb *pCb = (GenfkeyCb *)p; 727 UNUSED_PARAMETER(nArg); 728 UNUSED_PARAMETER(azCol); 729 return pCb->xData(pCb->pCtx, pCb->eType, azArg[0]); 730} 731 732static int detectSchemaProblem( 733 sqlite3 *db, /* Database connection */ 734 const char *zMessage, /* English language error message */ 735 const char *zSql, /* SQL statement to run */ 736 GenfkeyCb *pCb 737){ 738 sqlite3_stmt *pStmt; 739 int rc; 740 rc = sqlite3_prepare(db, zSql, -1, &pStmt, 0); 741 if( rc!=SQLITE_OK ){ 742 return rc; 743 } 744 while( SQLITE_ROW==sqlite3_step(pStmt) ){ 745 char *zDel; 746 int iFk = sqlite3_column_int(pStmt, 0); 747 const char *zTab = (const char *)sqlite3_column_text(pStmt, 1); 748 zDel = sqlite3_mprintf("Error in table %s: %s", zTab, zMessage); 749 rc = pCb->xData(pCb->pCtx, pCb->eType, zDel); 750 sqlite3_free(zDel); 751 if( rc!=SQLITE_OK ) return rc; 752 zDel = sqlite3_mprintf( 753 "DELETE FROM temp.fkey WHERE from_tbl = %Q AND fkid = %d" 754 , zTab, iFk 755 ); 756 sqlite3_exec(db, zDel, 0, 0, 0); 757 sqlite3_free(zDel); 758 } 759 sqlite3_finalize(pStmt); 760 return SQLITE_OK; 761} 762 763/* 764** Create and populate temporary table "fkey". 765*/ 766static int populateTempTable(sqlite3 *db, GenfkeyCb *pCallback){ 767 int rc; 768 769 rc = sqlite3_exec(db, 770 "CREATE VIRTUAL TABLE temp.v_fkey USING schema(foreign_key_list);" 771 "CREATE VIRTUAL TABLE temp.v_col USING schema(table_info);" 772 "CREATE VIRTUAL TABLE temp.v_idxlist USING schema(index_list);" 773 "CREATE VIRTUAL TABLE temp.v_idxinfo USING schema(index_info);" 774 "CREATE VIRTUAL TABLE temp.v_triggers USING schema(trigger_list);" 775 "CREATE TABLE temp.fkey AS " 776 "SELECT from_tbl, to_tbl, fkid, from_col, to_col, on_update, on_delete " 777 "FROM temp.v_fkey WHERE database = 'main';" 778 , 0, 0, 0 779 ); 780 if( rc!=SQLITE_OK ) return rc; 781 782 rc = detectSchemaProblem(db, "foreign key columns do not exist", 783 "SELECT fkid, from_tbl " 784 "FROM temp.fkey " 785 "WHERE to_col IS NOT NULL AND NOT EXISTS (SELECT 1 " 786 "FROM temp.v_col WHERE tablename=to_tbl AND name==to_col" 787 ")", pCallback 788 ); 789 if( rc!=SQLITE_OK ) return rc; 790 791 /* At this point the temp.fkey table is mostly populated. If any foreign 792 ** keys were specified so that they implicitly refer to they primary 793 ** key of the parent table, the "to_col" values of the temp.fkey rows 794 ** are still set to NULL. 795 ** 796 ** This is easily fixed for single column primary keys, but not for 797 ** composites. With a composite primary key, there is no way to reliably 798 ** query sqlite for the order in which the columns that make up the 799 ** composite key were declared i.e. there is no way to tell if the 800 ** schema actually contains "PRIMARY KEY(a, b)" or "PRIMARY KEY(b, a)". 801 ** Therefore, this case is not handled. The following function call 802 ** detects instances of this case. 803 */ 804 rc = detectSchemaProblem(db, "implicit mapping to composite primary key", 805 "SELECT fkid, from_tbl " 806 "FROM temp.fkey " 807 "WHERE to_col IS NULL " 808 "GROUP BY fkid, from_tbl HAVING count(*) > 1", pCallback 809 ); 810 if( rc!=SQLITE_OK ) return rc; 811 812 /* Detect attempts to implicitly map to the primary key of a table 813 ** that has no primary key column. 814 */ 815 rc = detectSchemaProblem(db, "implicit mapping to non-existant primary key", 816 "SELECT fkid, from_tbl " 817 "FROM temp.fkey " 818 "WHERE to_col IS NULL AND NOT EXISTS " 819 "(SELECT 1 FROM temp.v_col WHERE pk AND tablename = temp.fkey.to_tbl)" 820 , pCallback 821 ); 822 if( rc!=SQLITE_OK ) return rc; 823 824 /* Fix all the implicit primary key mappings in the temp.fkey table. */ 825 rc = sqlite3_exec(db, 826 "UPDATE temp.fkey SET to_col = " 827 "(SELECT name FROM temp.v_col WHERE pk AND tablename=temp.fkey.to_tbl)" 828 " WHERE to_col IS NULL;" 829 , 0, 0, 0 830 ); 831 if( rc!=SQLITE_OK ) return rc; 832 833 /* Now check that all all parent keys are either primary keys or 834 ** subject to a unique constraint. 835 */ 836 rc = sqlite3_exec(db, 837 "CREATE TABLE temp.idx2 AS SELECT " 838 "il.tablename AS tablename," 839 "ii.indexname AS indexname," 840 "ii.name AS col " 841 "FROM temp.v_idxlist AS il, temp.v_idxinfo AS ii " 842 "WHERE il.isunique AND il.database='main' AND ii.indexname = il.name;" 843 "INSERT INTO temp.idx2 " 844 "SELECT tablename, 'pk', name FROM temp.v_col WHERE pk;" 845 846 "CREATE TABLE temp.idx AS SELECT " 847 "tablename, indexname, sj(dq(col),',') AS cols " 848 "FROM (SELECT * FROM temp.idx2 ORDER BY col) " 849 "GROUP BY tablename, indexname;" 850 851 "CREATE TABLE temp.fkey2 AS SELECT " 852 "fkid, from_tbl, to_tbl, sj(dq(to_col),',') AS cols " 853 "FROM (SELECT * FROM temp.fkey ORDER BY to_col) " 854 "GROUP BY fkid, from_tbl;" 855 856 "CREATE TABLE temp.triggers AS SELECT " 857 "triggername FROM temp.v_triggers WHERE database='main' AND " 858 "triggername LIKE 'genfkey%';" 859 , 0, 0, 0 860 ); 861 if( rc!=SQLITE_OK ) return rc; 862 rc = detectSchemaProblem(db, "foreign key is not unique", 863 "SELECT fkid, from_tbl " 864 "FROM temp.fkey2 " 865 "WHERE NOT EXISTS (SELECT 1 " 866 "FROM temp.idx WHERE tablename=to_tbl AND fkey2.cols==idx.cols" 867 ")", pCallback 868 ); 869 if( rc!=SQLITE_OK ) return rc; 870 871 return rc; 872} 873 874#define GENFKEY_ERROR 1 875#define GENFKEY_DROPTRIGGER 2 876#define GENFKEY_CREATETRIGGER 3 877static int genfkey_create_triggers( 878 sqlite3 *sdb, /* Connection to read schema from */ 879 const char *zDb, /* Name of db to read ("main", "temp") */ 880 void *pCtx, /* Context pointer to pass to xData */ 881 int (*xData)(void *, int, const char *) 882){ 883 const char *zSql = 884 "SELECT multireplace('" 885 886 "-- Triggers for foreign key mapping:\n" 887 "--\n" 888 "-- /from_readable/ REFERENCES /to_readable/\n" 889 "-- on delete /on_delete/\n" 890 "-- on update /on_update/\n" 891 "--\n" 892 893 /* The "BEFORE INSERT ON <referencing>" trigger. This trigger's job is to 894 ** throw an exception if the user tries to insert a row into the 895 ** referencing table for which there is no corresponding row in 896 ** the referenced table. 897 */ 898 "CREATE TRIGGER /name/_insert_referencing BEFORE INSERT ON /tbl/ WHEN \n" 899 " /key_notnull/ AND NOT EXISTS (SELECT 1 FROM /ref/ WHERE /cond1/)\n" 900 "BEGIN\n" 901 " SELECT RAISE(ABORT, ''constraint failed'');\n" 902 "END;\n" 903 904 /* The "BEFORE UPDATE ON <referencing>" trigger. This trigger's job 905 ** is to throw an exception if the user tries to update a row in the 906 ** referencing table causing it to correspond to no row in the 907 ** referenced table. 908 */ 909 "CREATE TRIGGER /name/_update_referencing BEFORE\n" 910 " UPDATE OF /rkey_list/ ON /tbl/ WHEN \n" 911 " /key_notnull/ AND \n" 912 " NOT EXISTS (SELECT 1 FROM /ref/ WHERE /cond1/)\n" 913 "BEGIN\n" 914 " SELECT RAISE(ABORT, ''constraint failed'');\n" 915 "END;\n" 916 917 918 /* The "BEFORE DELETE ON <referenced>" trigger. This trigger's job 919 ** is to detect when a row is deleted from the referenced table to 920 ** which rows in the referencing table correspond. The action taken 921 ** depends on the value of the 'ON DELETE' clause. 922 */ 923 "CREATE TRIGGER /name/_delete_referenced BEFORE DELETE ON /ref/ WHEN\n" 924 " EXISTS (SELECT 1 FROM /tbl/ WHERE /cond2/)\n" 925 "BEGIN\n" 926 " /delete_action/\n" 927 "END;\n" 928 929 /* The "AFTER UPDATE ON <referenced>" trigger. This trigger's job 930 ** is to detect when the key columns of a row in the referenced table 931 ** to which one or more rows in the referencing table correspond are 932 ** updated. The action taken depends on the value of the 'ON UPDATE' 933 ** clause. 934 */ 935 "CREATE TRIGGER /name/_update_referenced AFTER\n" 936 " UPDATE OF /fkey_list/ ON /ref/ WHEN \n" 937 " EXISTS (SELECT 1 FROM /tbl/ WHERE /cond2/)\n" 938 "BEGIN\n" 939 " /update_action/\n" 940 "END;\n" 941 "'" 942 943 /* These are used in the SQL comment written above each set of triggers */ 944 ", '/from_readable/', from_tbl || '(' || sj(from_col, ', ') || ')'" 945 ", '/to_readable/', to_tbl || '(' || sj(to_col, ', ') || ')'" 946 ", '/on_delete/', on_delete" 947 ", '/on_update/', on_update" 948 949 ", '/name/', 'genfkey' || min(rowid)" 950 ", '/tbl/', dq(from_tbl)" 951 ", '/ref/', dq(to_tbl)" 952 ", '/key_notnull/', sj('new.' || dq(from_col) || ' IS NOT NULL', ' AND ')" 953 954 ", '/fkey_list/', sj(dq(to_col), ', ')" 955 ", '/rkey_list/', sj(dq(from_col), ', ')" 956 957 ", '/cond1/', sj(multireplace('new./from/ == /to/'" 958 ", '/from/', dq(from_col)" 959 ", '/to/', dq(to_col)" 960 "), ' AND ')" 961 ", '/cond2/', sj(multireplace('old./to/ == /from/'" 962 ", '/from/', dq(from_col)" 963 ", '/to/', dq(to_col)" 964 "), ' AND ')" 965 966 ", '/update_action/', CASE on_update " 967 "WHEN 'SET NULL' THEN " 968 "multireplace('UPDATE /tbl/ SET /setlist/ WHERE /where/;' " 969 ", '/setlist/', sj(dq(from_col)||' = NULL',', ')" 970 ", '/tbl/', dq(from_tbl)" 971 ", '/where/', sj(dq(from_col)||' = old.'||dq(to_col),' AND ')" 972 ")" 973 "WHEN 'CASCADE' THEN " 974 "multireplace('UPDATE /tbl/ SET /setlist/ WHERE /where/;' " 975 ", '/setlist/', sj(dq(from_col)||' = new.'||dq(to_col),', ')" 976 ", '/tbl/', dq(from_tbl)" 977 ", '/where/', sj(dq(from_col)||' = old.'||dq(to_col),' AND ')" 978 ")" 979 "ELSE " 980 " 'SELECT RAISE(ABORT, ''constraint failed'');'" 981 "END " 982 983 ", '/delete_action/', CASE on_delete " 984 "WHEN 'SET NULL' THEN " 985 "multireplace('UPDATE /tbl/ SET /setlist/ WHERE /where/;' " 986 ", '/setlist/', sj(dq(from_col)||' = NULL',', ')" 987 ", '/tbl/', dq(from_tbl)" 988 ", '/where/', sj(dq(from_col)||' = old.'||dq(to_col),' AND ')" 989 ")" 990 "WHEN 'CASCADE' THEN " 991 "multireplace('DELETE FROM /tbl/ WHERE /where/;' " 992 ", '/tbl/', dq(from_tbl)" 993 ", '/where/', sj(dq(from_col)||' = old.'||dq(to_col),' AND ')" 994 ")" 995 "ELSE " 996 " 'SELECT RAISE(ABORT, ''constraint failed'');'" 997 "END " 998 999 ") FROM temp.fkey " 1000 "GROUP BY from_tbl, fkid" 1001 ; 1002 1003 int rc; 1004 const int enc = SQLITE_UTF8; 1005 sqlite3 *db = 0; 1006 1007 GenfkeyCb cb; 1008 cb.xData = xData; 1009 cb.pCtx = pCtx; 1010 1011 UNUSED_PARAMETER(zDb); 1012 1013 /* Open the working database handle. */ 1014 rc = sqlite3_open(":memory:", &db); 1015 if( rc!=SQLITE_OK ) goto genfkey_exit; 1016 1017 /* Create the special scalar and aggregate functions used by this program. */ 1018 sqlite3_create_function(db, "dq", 1, enc, 0, doublequote, 0, 0); 1019 sqlite3_create_function(db, "multireplace", -1, enc, db, multireplace, 0, 0); 1020 sqlite3_create_function(db, "sj", 2, enc, 0, 0, joinStep, joinFinalize); 1021 1022 /* Install the "schema" virtual table module */ 1023 installSchemaModule(db, sdb); 1024 1025 /* Create and populate a temp table with the information required to 1026 ** build the foreign key triggers. See function populateTempTable() 1027 ** for details. 1028 */ 1029 cb.eType = GENFKEY_ERROR; 1030 rc = populateTempTable(db, &cb); 1031 if( rc!=SQLITE_OK ) goto genfkey_exit; 1032 1033 /* Unless the --no-drop option was specified, generate DROP TRIGGER 1034 ** statements to drop any triggers in the database generated by a 1035 ** previous run of this program. 1036 */ 1037 cb.eType = GENFKEY_DROPTRIGGER; 1038 rc = sqlite3_exec(db, 1039 "SELECT 'DROP TRIGGER main.' || dq(triggername) || ';' FROM triggers" 1040 ,invokeCallback, (void *)&cb, 0 1041 ); 1042 if( rc!=SQLITE_OK ) goto genfkey_exit; 1043 1044 /* Run the main query to create the trigger definitions. */ 1045 cb.eType = GENFKEY_CREATETRIGGER; 1046 rc = sqlite3_exec(db, zSql, invokeCallback, (void *)&cb, 0); 1047 if( rc!=SQLITE_OK ) goto genfkey_exit; 1048 1049genfkey_exit: 1050 sqlite3_close(db); 1051 return rc; 1052} 1053 1054 1055#endif 1056/* End genfkey logic. */ 1057/*************************************************************************/ 1058/*************************************************************************/ 1059 1060/* 1061** If the following flag is set, then command execution stops 1062** at an error if we are not interactive. 1063*/ 1064static int bail_on_error = 0; 1065 1066/* 1067** Threat stdin as an interactive input if the following variable 1068** is true. Otherwise, assume stdin is connected to a file or pipe. 1069*/ 1070static int stdin_is_interactive = 1; 1071 1072/* 1073** The following is the open SQLite database. We make a pointer 1074** to this database a static variable so that it can be accessed 1075** by the SIGINT handler to interrupt database processing. 1076*/ 1077static sqlite3 *db = 0; 1078 1079/* 1080** True if an interrupt (Control-C) has been received. 1081*/ 1082static volatile int seenInterrupt = 0; 1083 1084/* 1085** This is the name of our program. It is set in main(), used 1086** in a number of other places, mostly for error messages. 1087*/ 1088static char *Argv0; 1089 1090/* 1091** Prompt strings. Initialized in main. Settable with 1092** .prompt main continue 1093*/ 1094static char mainPrompt[20]; /* First line prompt. default: "sqlite> "*/ 1095static char continuePrompt[20]; /* Continuation prompt. default: " ...> " */ 1096 1097/* 1098** Write I/O traces to the following stream. 1099*/ 1100#ifdef SQLITE_ENABLE_IOTRACE 1101static FILE *iotrace = 0; 1102#endif 1103 1104/* 1105** This routine works like printf in that its first argument is a 1106** format string and subsequent arguments are values to be substituted 1107** in place of % fields. The result of formatting this string 1108** is written to iotrace. 1109*/ 1110#ifdef SQLITE_ENABLE_IOTRACE 1111static void iotracePrintf(const char *zFormat, ...){ 1112 va_list ap; 1113 char *z; 1114 if( iotrace==0 ) return; 1115 va_start(ap, zFormat); 1116 z = sqlite3_vmprintf(zFormat, ap); 1117 va_end(ap); 1118 fprintf(iotrace, "%s", z); 1119 sqlite3_free(z); 1120} 1121#endif 1122 1123 1124/* 1125** Determines if a string is a number of not. 1126*/ 1127static int isNumber(const char *z, int *realnum){ 1128 if( *z=='-' || *z=='+' ) z++; 1129 if( !isdigit(*z) ){ 1130 return 0; 1131 } 1132 z++; 1133 if( realnum ) *realnum = 0; 1134 while( isdigit(*z) ){ z++; } 1135 if( *z=='.' ){ 1136 z++; 1137 if( !isdigit(*z) ) return 0; 1138 while( isdigit(*z) ){ z++; } 1139 if( realnum ) *realnum = 1; 1140 } 1141 if( *z=='e' || *z=='E' ){ 1142 z++; 1143 if( *z=='+' || *z=='-' ) z++; 1144 if( !isdigit(*z) ) return 0; 1145 while( isdigit(*z) ){ z++; } 1146 if( realnum ) *realnum = 1; 1147 } 1148 return *z==0; 1149} 1150 1151/* 1152** A global char* and an SQL function to access its current value 1153** from within an SQL statement. This program used to use the 1154** sqlite_exec_printf() API to substitue a string into an SQL statement. 1155** The correct way to do this with sqlite3 is to use the bind API, but 1156** since the shell is built around the callback paradigm it would be a lot 1157** of work. Instead just use this hack, which is quite harmless. 1158*/ 1159static const char *zShellStatic = 0; 1160static void shellstaticFunc( 1161 sqlite3_context *context, 1162 int argc, 1163 sqlite3_value **argv 1164){ 1165 assert( 0==argc ); 1166 assert( zShellStatic ); 1167 UNUSED_PARAMETER(argc); 1168 UNUSED_PARAMETER(argv); 1169 sqlite3_result_text(context, zShellStatic, -1, SQLITE_STATIC); 1170} 1171 1172 1173/* 1174** This routine reads a line of text from FILE in, stores 1175** the text in memory obtained from malloc() and returns a pointer 1176** to the text. NULL is returned at end of file, or if malloc() 1177** fails. 1178** 1179** The interface is like "readline" but no command-line editing 1180** is done. 1181*/ 1182static char *local_getline(char *zPrompt, FILE *in){ 1183 char *zLine; 1184 int nLine; 1185 int n; 1186 int eol; 1187 1188 if( zPrompt && *zPrompt ){ 1189 printf("%s",zPrompt); 1190 fflush(stdout); 1191 } 1192 nLine = 100; 1193 zLine = malloc( nLine ); 1194 if( zLine==0 ) return 0; 1195 n = 0; 1196 eol = 0; 1197 while( !eol ){ 1198 if( n+100>nLine ){ 1199 nLine = nLine*2 + 100; 1200 zLine = realloc(zLine, nLine); 1201 if( zLine==0 ) return 0; 1202 } 1203 if( fgets(&zLine[n], nLine - n, in)==0 ){ 1204 if( n==0 ){ 1205 free(zLine); 1206 return 0; 1207 } 1208 zLine[n] = 0; 1209 eol = 1; 1210 break; 1211 } 1212 while( zLine[n] ){ n++; } 1213 if( n>0 && zLine[n-1]=='\n' ){ 1214 n--; 1215 if( n>0 && zLine[n-1]=='\r' ) n--; 1216 zLine[n] = 0; 1217 eol = 1; 1218 } 1219 } 1220 zLine = realloc( zLine, n+1 ); 1221 return zLine; 1222} 1223 1224/* 1225** Retrieve a single line of input text. 1226** 1227** zPrior is a string of prior text retrieved. If not the empty 1228** string, then issue a continuation prompt. 1229*/ 1230static char *one_input_line(const char *zPrior, FILE *in){ 1231 char *zPrompt; 1232 char *zResult; 1233 if( in!=0 ){ 1234 return local_getline(0, in); 1235 } 1236 if( zPrior && zPrior[0] ){ 1237 zPrompt = continuePrompt; 1238 }else{ 1239 zPrompt = mainPrompt; 1240 } 1241 zResult = readline(zPrompt); 1242#if defined(HAVE_READLINE) && HAVE_READLINE==1 1243 if( zResult && *zResult ) add_history(zResult); 1244#endif 1245 return zResult; 1246} 1247 1248struct previous_mode_data { 1249 int valid; /* Is there legit data in here? */ 1250 int mode; 1251 int showHeader; 1252 int colWidth[100]; 1253}; 1254 1255/* 1256** An pointer to an instance of this structure is passed from 1257** the main program to the callback. This is used to communicate 1258** state and mode information. 1259*/ 1260struct callback_data { 1261 sqlite3 *db; /* The database */ 1262 int echoOn; /* True to echo input commands */ 1263 int cnt; /* Number of records displayed so far */ 1264 FILE *out; /* Write results here */ 1265 int mode; /* An output mode setting */ 1266 int writableSchema; /* True if PRAGMA writable_schema=ON */ 1267 int showHeader; /* True to show column names in List or Column mode */ 1268 char *zDestTable; /* Name of destination table when MODE_Insert */ 1269 char separator[20]; /* Separator character for MODE_List */ 1270 int colWidth[100]; /* Requested width of each column when in column mode*/ 1271 int actualWidth[100]; /* Actual width of each column */ 1272 char nullvalue[20]; /* The text to print when a NULL comes back from 1273 ** the database */ 1274 struct previous_mode_data explainPrev; 1275 /* Holds the mode information just before 1276 ** .explain ON */ 1277 char outfile[FILENAME_MAX]; /* Filename for *out */ 1278 const char *zDbFilename; /* name of the database file */ 1279 sqlite3_stmt *pStmt; /* Current statement if any. */ 1280}; 1281 1282/* 1283** These are the allowed modes. 1284*/ 1285#define MODE_Line 0 /* One column per line. Blank line between records */ 1286#define MODE_Column 1 /* One record per line in neat columns */ 1287#define MODE_List 2 /* One record per line with a separator */ 1288#define MODE_Semi 3 /* Same as MODE_List but append ";" to each line */ 1289#define MODE_Html 4 /* Generate an XHTML table */ 1290#define MODE_Insert 5 /* Generate SQL "insert" statements */ 1291#define MODE_Tcl 6 /* Generate ANSI-C or TCL quoted elements */ 1292#define MODE_Csv 7 /* Quote strings, numbers are plain */ 1293#define MODE_Explain 8 /* Like MODE_Column, but do not truncate data */ 1294 1295static const char *modeDescr[] = { 1296 "line", 1297 "column", 1298 "list", 1299 "semi", 1300 "html", 1301 "insert", 1302 "tcl", 1303 "csv", 1304 "explain", 1305}; 1306 1307/* 1308** Number of elements in an array 1309*/ 1310#define ArraySize(X) (int)(sizeof(X)/sizeof(X[0])) 1311 1312/* 1313** Compute a string length that is limited to what can be stored in 1314** lower 30 bits of a 32-bit signed integer. 1315*/ 1316static int strlen30(const char *z){ 1317 const char *z2 = z; 1318 while( *z2 ){ z2++; } 1319 return 0x3fffffff & (int)(z2 - z); 1320} 1321 1322/* 1323** Output the given string as a hex-encoded blob (eg. X'1234' ) 1324*/ 1325static void output_hex_blob(FILE *out, const void *pBlob, int nBlob){ 1326 int i; 1327 char *zBlob = (char *)pBlob; 1328 fprintf(out,"X'"); 1329 for(i=0; i<nBlob; i++){ fprintf(out,"%02x",zBlob[i]); } 1330 fprintf(out,"'"); 1331} 1332 1333/* 1334** Output the given string as a quoted string using SQL quoting conventions. 1335*/ 1336static void output_quoted_string(FILE *out, const char *z){ 1337 int i; 1338 int nSingle = 0; 1339 for(i=0; z[i]; i++){ 1340 if( z[i]=='\'' ) nSingle++; 1341 } 1342 if( nSingle==0 ){ 1343 fprintf(out,"'%s'",z); 1344 }else{ 1345 fprintf(out,"'"); 1346 while( *z ){ 1347 for(i=0; z[i] && z[i]!='\''; i++){} 1348 if( i==0 ){ 1349 fprintf(out,"''"); 1350 z++; 1351 }else if( z[i]=='\'' ){ 1352 fprintf(out,"%.*s''",i,z); 1353 z += i+1; 1354 }else{ 1355 fprintf(out,"%s",z); 1356 break; 1357 } 1358 } 1359 fprintf(out,"'"); 1360 } 1361} 1362 1363/* 1364** Output the given string as a quoted according to C or TCL quoting rules. 1365*/ 1366static void output_c_string(FILE *out, const char *z){ 1367 unsigned int c; 1368 fputc('"', out); 1369 while( (c = *(z++))!=0 ){ 1370 if( c=='\\' ){ 1371 fputc(c, out); 1372 fputc(c, out); 1373 }else if( c=='\t' ){ 1374 fputc('\\', out); 1375 fputc('t', out); 1376 }else if( c=='\n' ){ 1377 fputc('\\', out); 1378 fputc('n', out); 1379 }else if( c=='\r' ){ 1380 fputc('\\', out); 1381 fputc('r', out); 1382 }else if( !isprint(c) ){ 1383 fprintf(out, "\\%03o", c&0xff); 1384 }else{ 1385 fputc(c, out); 1386 } 1387 } 1388 fputc('"', out); 1389} 1390 1391/* 1392** Output the given string with characters that are special to 1393** HTML escaped. 1394*/ 1395static void output_html_string(FILE *out, const char *z){ 1396 int i; 1397 while( *z ){ 1398 for(i=0; z[i] 1399 && z[i]!='<' 1400 && z[i]!='&' 1401 && z[i]!='>' 1402 && z[i]!='\"' 1403 && z[i]!='\''; 1404 i++){} 1405 if( i>0 ){ 1406 fprintf(out,"%.*s",i,z); 1407 } 1408 if( z[i]=='<' ){ 1409 fprintf(out,"<"); 1410 }else if( z[i]=='&' ){ 1411 fprintf(out,"&"); 1412 }else if( z[i]=='>' ){ 1413 fprintf(out,">"); 1414 }else if( z[i]=='\"' ){ 1415 fprintf(out,"""); 1416 }else if( z[i]=='\'' ){ 1417 fprintf(out,"'"); 1418 }else{ 1419 break; 1420 } 1421 z += i + 1; 1422 } 1423} 1424 1425/* 1426** If a field contains any character identified by a 1 in the following 1427** array, then the string must be quoted for CSV. 1428*/ 1429static const char needCsvQuote[] = { 1430 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1431 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1432 1, 0, 1, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 1433 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1434 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1435 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1436 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1437 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 1438 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1439 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1440 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1441 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1442 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1443 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1444 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1445 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1446}; 1447 1448/* 1449** Output a single term of CSV. Actually, p->separator is used for 1450** the separator, which may or may not be a comma. p->nullvalue is 1451** the null value. Strings are quoted using ANSI-C rules. Numbers 1452** appear outside of quotes. 1453*/ 1454static void output_csv(struct callback_data *p, const char *z, int bSep){ 1455 FILE *out = p->out; 1456 if( z==0 ){ 1457 fprintf(out,"%s",p->nullvalue); 1458 }else{ 1459 int i; 1460 int nSep = strlen30(p->separator); 1461 for(i=0; z[i]; i++){ 1462 if( needCsvQuote[((unsigned char*)z)[i]] 1463 || (z[i]==p->separator[0] && 1464 (nSep==1 || memcmp(z, p->separator, nSep)==0)) ){ 1465 i = 0; 1466 break; 1467 } 1468 } 1469 if( i==0 ){ 1470 putc('"', out); 1471 for(i=0; z[i]; i++){ 1472 if( z[i]=='"' ) putc('"', out); 1473 putc(z[i], out); 1474 } 1475 putc('"', out); 1476 }else{ 1477 fprintf(out, "%s", z); 1478 } 1479 } 1480 if( bSep ){ 1481 fprintf(p->out, "%s", p->separator); 1482 } 1483} 1484 1485#ifdef SIGINT 1486/* 1487** This routine runs when the user presses Ctrl-C 1488*/ 1489static void interrupt_handler(int NotUsed){ 1490 UNUSED_PARAMETER(NotUsed); 1491 seenInterrupt = 1; 1492 if( db ) sqlite3_interrupt(db); 1493} 1494#endif 1495 1496/* 1497** This is the callback routine that the shell 1498** invokes for each row of a query result. 1499*/ 1500static int shell_callback(void *pArg, int nArg, char **azArg, char **azCol, int *aiType){ 1501 int i; 1502 struct callback_data *p = (struct callback_data*)pArg; 1503 1504 if( p->echoOn && p->cnt==0 && p->pStmt){ 1505 printf("%s\n", sqlite3_sql(p->pStmt)); 1506 } 1507 1508 switch( p->mode ){ 1509 case MODE_Line: { 1510 int w = 5; 1511 if( azArg==0 ) break; 1512 for(i=0; i<nArg; i++){ 1513 int len = strlen30(azCol[i] ? azCol[i] : ""); 1514 if( len>w ) w = len; 1515 } 1516 if( p->cnt++>0 ) fprintf(p->out,"\n"); 1517 for(i=0; i<nArg; i++){ 1518 fprintf(p->out,"%*s = %s\n", w, azCol[i], 1519 azArg[i] ? azArg[i] : p->nullvalue); 1520 } 1521 break; 1522 } 1523 case MODE_Explain: 1524 case MODE_Column: { 1525 if( p->cnt++==0 ){ 1526 for(i=0; i<nArg; i++){ 1527 int w, n; 1528 if( i<ArraySize(p->colWidth) ){ 1529 w = p->colWidth[i]; 1530 }else{ 1531 w = 0; 1532 } 1533 if( w<=0 ){ 1534 w = strlen30(azCol[i] ? azCol[i] : ""); 1535 if( w<10 ) w = 10; 1536 n = strlen30(azArg && azArg[i] ? azArg[i] : p->nullvalue); 1537 if( w<n ) w = n; 1538 } 1539 if( i<ArraySize(p->actualWidth) ){ 1540 p->actualWidth[i] = w; 1541 } 1542 if( p->showHeader ){ 1543 fprintf(p->out,"%-*.*s%s",w,w,azCol[i], i==nArg-1 ? "\n": " "); 1544 } 1545 } 1546 if( p->showHeader ){ 1547 for(i=0; i<nArg; i++){ 1548 int w; 1549 if( i<ArraySize(p->actualWidth) ){ 1550 w = p->actualWidth[i]; 1551 }else{ 1552 w = 10; 1553 } 1554 fprintf(p->out,"%-*.*s%s",w,w,"-----------------------------------" 1555 "----------------------------------------------------------", 1556 i==nArg-1 ? "\n": " "); 1557 } 1558 } 1559 } 1560 if( azArg==0 ) break; 1561 for(i=0; i<nArg; i++){ 1562 int w; 1563 if( i<ArraySize(p->actualWidth) ){ 1564 w = p->actualWidth[i]; 1565 }else{ 1566 w = 10; 1567 } 1568 if( p->mode==MODE_Explain && azArg[i] && 1569 strlen30(azArg[i])>w ){ 1570 w = strlen30(azArg[i]); 1571 } 1572 fprintf(p->out,"%-*.*s%s",w,w, 1573 azArg[i] ? azArg[i] : p->nullvalue, i==nArg-1 ? "\n": " "); 1574 } 1575 break; 1576 } 1577 case MODE_Semi: 1578 case MODE_List: { 1579 if( p->cnt++==0 && p->showHeader ){ 1580 for(i=0; i<nArg; i++){ 1581 fprintf(p->out,"%s%s",azCol[i], i==nArg-1 ? "\n" : p->separator); 1582 } 1583 } 1584 if( azArg==0 ) break; 1585 for(i=0; i<nArg; i++){ 1586 char *z = azArg[i]; 1587 if( z==0 ) z = p->nullvalue; 1588 fprintf(p->out, "%s", z); 1589 if( i<nArg-1 ){ 1590 fprintf(p->out, "%s", p->separator); 1591 }else if( p->mode==MODE_Semi ){ 1592 fprintf(p->out, ";\n"); 1593 }else{ 1594 fprintf(p->out, "\n"); 1595 } 1596 } 1597 break; 1598 } 1599 case MODE_Html: { 1600 if( p->cnt++==0 && p->showHeader ){ 1601 fprintf(p->out,"<TR>"); 1602 for(i=0; i<nArg; i++){ 1603 fprintf(p->out,"<TH>"); 1604 output_html_string(p->out, azCol[i]); 1605 fprintf(p->out,"</TH>\n"); 1606 } 1607 fprintf(p->out,"</TR>\n"); 1608 } 1609 if( azArg==0 ) break; 1610 fprintf(p->out,"<TR>"); 1611 for(i=0; i<nArg; i++){ 1612 fprintf(p->out,"<TD>"); 1613 output_html_string(p->out, azArg[i] ? azArg[i] : p->nullvalue); 1614 fprintf(p->out,"</TD>\n"); 1615 } 1616 fprintf(p->out,"</TR>\n"); 1617 break; 1618 } 1619 case MODE_Tcl: { 1620 if( p->cnt++==0 && p->showHeader ){ 1621 for(i=0; i<nArg; i++){ 1622 output_c_string(p->out,azCol[i] ? azCol[i] : ""); 1623 fprintf(p->out, "%s", p->separator); 1624 } 1625 fprintf(p->out,"\n"); 1626 } 1627 if( azArg==0 ) break; 1628 for(i=0; i<nArg; i++){ 1629 output_c_string(p->out, azArg[i] ? azArg[i] : p->nullvalue); 1630 fprintf(p->out, "%s", p->separator); 1631 } 1632 fprintf(p->out,"\n"); 1633 break; 1634 } 1635 case MODE_Csv: { 1636 if( p->cnt++==0 && p->showHeader ){ 1637 for(i=0; i<nArg; i++){ 1638 output_csv(p, azCol[i] ? azCol[i] : "", i<nArg-1); 1639 } 1640 fprintf(p->out,"\n"); 1641 } 1642 if( azArg==0 ) break; 1643 for(i=0; i<nArg; i++){ 1644 output_csv(p, azArg[i], i<nArg-1); 1645 } 1646 fprintf(p->out,"\n"); 1647 break; 1648 } 1649 case MODE_Insert: { 1650 p->cnt++; 1651 if( azArg==0 ) break; 1652 fprintf(p->out,"INSERT INTO %s VALUES(",p->zDestTable); 1653 for(i=0; i<nArg; i++){ 1654 char *zSep = i>0 ? ",": ""; 1655 if( (azArg[i]==0) || (aiType && aiType[i]==SQLITE_NULL) ){ 1656 fprintf(p->out,"%sNULL",zSep); 1657 }else if( aiType && aiType[i]==SQLITE_TEXT ){ 1658 if( zSep[0] ) fprintf(p->out,"%s",zSep); 1659 output_quoted_string(p->out, azArg[i]); 1660 }else if( aiType && (aiType[i]==SQLITE_INTEGER || aiType[i]==SQLITE_FLOAT) ){ 1661 fprintf(p->out,"%s%s",zSep, azArg[i]); 1662 }else if( aiType && aiType[i]==SQLITE_BLOB && p->pStmt ){ 1663 const void *pBlob = sqlite3_column_blob(p->pStmt, i); 1664 int nBlob = sqlite3_column_bytes(p->pStmt, i); 1665 if( zSep[0] ) fprintf(p->out,"%s",zSep); 1666 output_hex_blob(p->out, pBlob, nBlob); 1667 }else if( isNumber(azArg[i], 0) ){ 1668 fprintf(p->out,"%s%s",zSep, azArg[i]); 1669 }else{ 1670 if( zSep[0] ) fprintf(p->out,"%s",zSep); 1671 output_quoted_string(p->out, azArg[i]); 1672 } 1673 } 1674 fprintf(p->out,");\n"); 1675 break; 1676 } 1677 } 1678 return 0; 1679} 1680 1681/* 1682** This is the callback routine that the SQLite library 1683** invokes for each row of a query result. 1684*/ 1685static int callback(void *pArg, int nArg, char **azArg, char **azCol){ 1686 /* since we don't have type info, call the shell_callback with a NULL value */ 1687 return shell_callback(pArg, nArg, azArg, azCol, NULL); 1688} 1689 1690/* 1691** Set the destination table field of the callback_data structure to 1692** the name of the table given. Escape any quote characters in the 1693** table name. 1694*/ 1695static void set_table_name(struct callback_data *p, const char *zName){ 1696 int i, n; 1697 int needQuote; 1698 char *z; 1699 1700 if( p->zDestTable ){ 1701 free(p->zDestTable); 1702 p->zDestTable = 0; 1703 } 1704 if( zName==0 ) return; 1705 needQuote = !isalpha((unsigned char)*zName) && *zName!='_'; 1706 for(i=n=0; zName[i]; i++, n++){ 1707 if( !isalnum((unsigned char)zName[i]) && zName[i]!='_' ){ 1708 needQuote = 1; 1709 if( zName[i]=='\'' ) n++; 1710 } 1711 } 1712 if( needQuote ) n += 2; 1713 z = p->zDestTable = malloc( n+1 ); 1714 if( z==0 ){ 1715 fprintf(stderr,"Error: out of memory\n"); 1716 exit(1); 1717 } 1718 n = 0; 1719 if( needQuote ) z[n++] = '\''; 1720 for(i=0; zName[i]; i++){ 1721 z[n++] = zName[i]; 1722 if( zName[i]=='\'' ) z[n++] = '\''; 1723 } 1724 if( needQuote ) z[n++] = '\''; 1725 z[n] = 0; 1726} 1727 1728/* zIn is either a pointer to a NULL-terminated string in memory obtained 1729** from malloc(), or a NULL pointer. The string pointed to by zAppend is 1730** added to zIn, and the result returned in memory obtained from malloc(). 1731** zIn, if it was not NULL, is freed. 1732** 1733** If the third argument, quote, is not '\0', then it is used as a 1734** quote character for zAppend. 1735*/ 1736static char *appendText(char *zIn, char const *zAppend, char quote){ 1737 int len; 1738 int i; 1739 int nAppend = strlen30(zAppend); 1740 int nIn = (zIn?strlen30(zIn):0); 1741 1742 len = nAppend+nIn+1; 1743 if( quote ){ 1744 len += 2; 1745 for(i=0; i<nAppend; i++){ 1746 if( zAppend[i]==quote ) len++; 1747 } 1748 } 1749 1750 zIn = (char *)realloc(zIn, len); 1751 if( !zIn ){ 1752 return 0; 1753 } 1754 1755 if( quote ){ 1756 char *zCsr = &zIn[nIn]; 1757 *zCsr++ = quote; 1758 for(i=0; i<nAppend; i++){ 1759 *zCsr++ = zAppend[i]; 1760 if( zAppend[i]==quote ) *zCsr++ = quote; 1761 } 1762 *zCsr++ = quote; 1763 *zCsr++ = '\0'; 1764 assert( (zCsr-zIn)==len ); 1765 }else{ 1766 memcpy(&zIn[nIn], zAppend, nAppend); 1767 zIn[len-1] = '\0'; 1768 } 1769 1770 return zIn; 1771} 1772 1773 1774/* 1775** Execute a query statement that has a single result column. Print 1776** that result column on a line by itself with a semicolon terminator. 1777** 1778** This is used, for example, to show the schema of the database by 1779** querying the SQLITE_MASTER table. 1780*/ 1781static int run_table_dump_query( 1782 FILE *out, /* Send output here */ 1783 sqlite3 *db, /* Database to query */ 1784 const char *zSelect, /* SELECT statement to extract content */ 1785 const char *zFirstRow /* Print before first row, if not NULL */ 1786){ 1787 sqlite3_stmt *pSelect; 1788 int rc; 1789 rc = sqlite3_prepare(db, zSelect, -1, &pSelect, 0); 1790 if( rc!=SQLITE_OK || !pSelect ){ 1791 return rc; 1792 } 1793 rc = sqlite3_step(pSelect); 1794 while( rc==SQLITE_ROW ){ 1795 if( zFirstRow ){ 1796 fprintf(out, "%s", zFirstRow); 1797 zFirstRow = 0; 1798 } 1799 fprintf(out, "%s;\n", sqlite3_column_text(pSelect, 0)); 1800 rc = sqlite3_step(pSelect); 1801 } 1802 return sqlite3_finalize(pSelect); 1803} 1804 1805/* 1806** Allocate space and save off current error string. 1807*/ 1808static char *save_err_msg( 1809 sqlite3 *db /* Database to query */ 1810){ 1811 int nErrMsg = 1+strlen30(sqlite3_errmsg(db)); 1812 char *zErrMsg = sqlite3_malloc(nErrMsg); 1813 if( zErrMsg ){ 1814 memcpy(zErrMsg, sqlite3_errmsg(db), nErrMsg); 1815 } 1816 return zErrMsg; 1817} 1818 1819/* 1820** Execute a statement or set of statements. Print 1821** any result rows/columns depending on the current mode 1822** set via the supplied callback. 1823** 1824** This is very similar to SQLite's built-in sqlite3_exec() 1825** function except it takes a slightly different callback 1826** and callback data argument. 1827*/ 1828static int shell_exec( 1829 sqlite3 *db, /* An open database */ 1830 const char *zSql, /* SQL to be evaluated */ 1831 int (*xCallback)(void*,int,char**,char**,int*), /* Callback function */ 1832 /* (not the same as sqlite3_exec) */ 1833 struct callback_data *pArg, /* Pointer to struct callback_data */ 1834 char **pzErrMsg /* Error msg written here */ 1835){ 1836 sqlite3_stmt *pStmt = NULL; /* Statement to execute. */ 1837 int rc = SQLITE_OK; /* Return Code */ 1838 const char *zLeftover; /* Tail of unprocessed SQL */ 1839 1840 if( pzErrMsg ){ 1841 *pzErrMsg = NULL; 1842 } 1843 1844 while( zSql[0] && (SQLITE_OK == rc) ){ 1845 rc = sqlite3_prepare_v2(db, zSql, -1, &pStmt, &zLeftover); 1846 if( SQLITE_OK != rc ){ 1847 if( pzErrMsg ){ 1848 *pzErrMsg = save_err_msg(db); 1849 } 1850 }else{ 1851 if( !pStmt ){ 1852 /* this happens for a comment or white-space */ 1853 zSql = zLeftover; 1854 while( isspace(zSql[0]) ) zSql++; 1855 continue; 1856 } 1857 1858 /* perform the first step. this will tell us if we 1859 ** have a result set or not and how wide it is. 1860 */ 1861 rc = sqlite3_step(pStmt); 1862 /* if we have a result set... */ 1863 if( SQLITE_ROW == rc ){ 1864 /* if we have a callback... */ 1865 if( xCallback ){ 1866 /* allocate space for col name ptr, value ptr, and type */ 1867 int nCol = sqlite3_column_count(pStmt); 1868 void *pData = sqlite3_malloc(3*nCol*sizeof(const char*) + 1); 1869 if( !pData ){ 1870 rc = SQLITE_NOMEM; 1871 }else{ 1872 char **azCols = (char **)pData; /* Names of result columns */ 1873 char **azVals = &azCols[nCol]; /* Results */ 1874 int *aiTypes = (int *)&azVals[nCol]; /* Result types */ 1875 int i; 1876 assert(sizeof(int) <= sizeof(char *)); 1877 /* save off ptrs to column names */ 1878 for(i=0; i<nCol; i++){ 1879 azCols[i] = (char *)sqlite3_column_name(pStmt, i); 1880 } 1881 /* save off the prepared statment handle and reset row count */ 1882 if( pArg ){ 1883 pArg->pStmt = pStmt; 1884 pArg->cnt = 0; 1885 } 1886 do{ 1887 /* extract the data and data types */ 1888 for(i=0; i<nCol; i++){ 1889 azVals[i] = (char *)sqlite3_column_text(pStmt, i); 1890 aiTypes[i] = sqlite3_column_type(pStmt, i); 1891 if( !azVals[i] && (aiTypes[i]!=SQLITE_NULL) ){ 1892 rc = SQLITE_NOMEM; 1893 break; /* from for */ 1894 } 1895 } /* end for */ 1896 1897 /* if data and types extracted successfully... */ 1898 if( SQLITE_ROW == rc ){ 1899 /* call the supplied callback with the result row data */ 1900 if( xCallback(pArg, nCol, azVals, azCols, aiTypes) ){ 1901 rc = SQLITE_ABORT; 1902 }else{ 1903 rc = sqlite3_step(pStmt); 1904 } 1905 } 1906 } while( SQLITE_ROW == rc ); 1907 sqlite3_free(pData); 1908 if( pArg ){ 1909 pArg->pStmt = NULL; 1910 } 1911 } 1912 }else{ 1913 do{ 1914 rc = sqlite3_step(pStmt); 1915 } while( rc == SQLITE_ROW ); 1916 } 1917 } 1918 1919 /* Finalize the statement just executed. If this fails, save a 1920 ** copy of the error message. Otherwise, set zSql to point to the 1921 ** next statement to execute. */ 1922 rc = sqlite3_finalize(pStmt); 1923 if( rc==SQLITE_OK ){ 1924 zSql = zLeftover; 1925 while( isspace(zSql[0]) ) zSql++; 1926 }else if( pzErrMsg ){ 1927 *pzErrMsg = save_err_msg(db); 1928 } 1929 } 1930 } /* end while */ 1931 1932 return rc; 1933} 1934 1935 1936/* 1937** This is a different callback routine used for dumping the database. 1938** Each row received by this callback consists of a table name, 1939** the table type ("index" or "table") and SQL to create the table. 1940** This routine should print text sufficient to recreate the table. 1941*/ 1942static int dump_callback(void *pArg, int nArg, char **azArg, char **azCol){ 1943 int rc; 1944 const char *zTable; 1945 const char *zType; 1946 const char *zSql; 1947 const char *zPrepStmt = 0; 1948 struct callback_data *p = (struct callback_data *)pArg; 1949 1950 UNUSED_PARAMETER(azCol); 1951 if( nArg!=3 ) return 1; 1952 zTable = azArg[0]; 1953 zType = azArg[1]; 1954 zSql = azArg[2]; 1955 1956 if( strcmp(zTable, "sqlite_sequence")==0 ){ 1957 zPrepStmt = "DELETE FROM sqlite_sequence;\n"; 1958 }else if( strcmp(zTable, "sqlite_stat1")==0 ){ 1959 fprintf(p->out, "ANALYZE sqlite_master;\n"); 1960 }else if( strncmp(zTable, "sqlite_", 7)==0 ){ 1961 return 0; 1962 }else if( strncmp(zSql, "CREATE VIRTUAL TABLE", 20)==0 ){ 1963 char *zIns; 1964 if( !p->writableSchema ){ 1965 fprintf(p->out, "PRAGMA writable_schema=ON;\n"); 1966 p->writableSchema = 1; 1967 } 1968 zIns = sqlite3_mprintf( 1969 "INSERT INTO sqlite_master(type,name,tbl_name,rootpage,sql)" 1970 "VALUES('table','%q','%q',0,'%q');", 1971 zTable, zTable, zSql); 1972 fprintf(p->out, "%s\n", zIns); 1973 sqlite3_free(zIns); 1974 return 0; 1975 }else{ 1976 fprintf(p->out, "%s;\n", zSql); 1977 } 1978 1979 if( strcmp(zType, "table")==0 ){ 1980 sqlite3_stmt *pTableInfo = 0; 1981 char *zSelect = 0; 1982 char *zTableInfo = 0; 1983 char *zTmp = 0; 1984 int nRow = 0; 1985 1986 zTableInfo = appendText(zTableInfo, "PRAGMA table_info(", 0); 1987 zTableInfo = appendText(zTableInfo, zTable, '"'); 1988 zTableInfo = appendText(zTableInfo, ");", 0); 1989 1990 rc = sqlite3_prepare(p->db, zTableInfo, -1, &pTableInfo, 0); 1991 free(zTableInfo); 1992 if( rc!=SQLITE_OK || !pTableInfo ){ 1993 return 1; 1994 } 1995 1996 zSelect = appendText(zSelect, "SELECT 'INSERT INTO ' || ", 0); 1997 zTmp = appendText(zTmp, zTable, '"'); 1998 if( zTmp ){ 1999 zSelect = appendText(zSelect, zTmp, '\''); 2000 } 2001 zSelect = appendText(zSelect, " || ' VALUES(' || ", 0); 2002 rc = sqlite3_step(pTableInfo); 2003 while( rc==SQLITE_ROW ){ 2004 const char *zText = (const char *)sqlite3_column_text(pTableInfo, 1); 2005 zSelect = appendText(zSelect, "quote(", 0); 2006 zSelect = appendText(zSelect, zText, '"'); 2007 rc = sqlite3_step(pTableInfo); 2008 if( rc==SQLITE_ROW ){ 2009 zSelect = appendText(zSelect, ") || ',' || ", 0); 2010 }else{ 2011 zSelect = appendText(zSelect, ") ", 0); 2012 } 2013 nRow++; 2014 } 2015 rc = sqlite3_finalize(pTableInfo); 2016 if( rc!=SQLITE_OK || nRow==0 ){ 2017 free(zSelect); 2018 return 1; 2019 } 2020 zSelect = appendText(zSelect, "|| ')' FROM ", 0); 2021 zSelect = appendText(zSelect, zTable, '"'); 2022 2023 rc = run_table_dump_query(p->out, p->db, zSelect, zPrepStmt); 2024 if( rc==SQLITE_CORRUPT ){ 2025 zSelect = appendText(zSelect, " ORDER BY rowid DESC", 0); 2026 rc = run_table_dump_query(p->out, p->db, zSelect, 0); 2027 } 2028 if( zSelect ) free(zSelect); 2029 } 2030 return 0; 2031} 2032 2033/* 2034** Run zQuery. Use dump_callback() as the callback routine so that 2035** the contents of the query are output as SQL statements. 2036** 2037** If we get a SQLITE_CORRUPT error, rerun the query after appending 2038** "ORDER BY rowid DESC" to the end. 2039*/ 2040static int run_schema_dump_query( 2041 struct callback_data *p, 2042 const char *zQuery, 2043 char **pzErrMsg 2044){ 2045 int rc; 2046 rc = sqlite3_exec(p->db, zQuery, dump_callback, p, pzErrMsg); 2047 if( rc==SQLITE_CORRUPT ){ 2048 char *zQ2; 2049 int len = strlen30(zQuery); 2050 if( pzErrMsg ) sqlite3_free(*pzErrMsg); 2051 zQ2 = malloc( len+100 ); 2052 if( zQ2==0 ) return rc; 2053 sqlite3_snprintf(sizeof(zQ2), zQ2, "%s ORDER BY rowid DESC", zQuery); 2054 rc = sqlite3_exec(p->db, zQ2, dump_callback, p, pzErrMsg); 2055 free(zQ2); 2056 } 2057 return rc; 2058} 2059 2060#if !defined(SQLITE_OMIT_VIRTUALTABLE) && !defined(SQLITE_OMIT_SUBQUERY) 2061struct GenfkeyCmd { 2062 sqlite3 *db; /* Database handle */ 2063 struct callback_data *pCb; /* Callback data */ 2064 int isIgnoreErrors; /* True for --ignore-errors */ 2065 int isExec; /* True for --exec */ 2066 int isNoDrop; /* True for --no-drop */ 2067 int nErr; /* Number of errors seen so far */ 2068}; 2069typedef struct GenfkeyCmd GenfkeyCmd; 2070 2071static int genfkeyParseArgs(GenfkeyCmd *p, char **azArg, int nArg){ 2072 int ii; 2073 memset(p, 0, sizeof(GenfkeyCmd)); 2074 2075 for(ii=0; ii<nArg; ii++){ 2076 int n = strlen30(azArg[ii]); 2077 2078 if( n>2 && n<10 && 0==strncmp(azArg[ii], "--no-drop", n) ){ 2079 p->isNoDrop = 1; 2080 }else if( n>2 && n<16 && 0==strncmp(azArg[ii], "--ignore-errors", n) ){ 2081 p->isIgnoreErrors = 1; 2082 }else if( n>2 && n<7 && 0==strncmp(azArg[ii], "--exec", n) ){ 2083 p->isExec = 1; 2084 }else{ 2085 fprintf(stderr, "unknown option: %s\n", azArg[ii]); 2086 return -1; 2087 } 2088 } 2089 2090 return SQLITE_OK; 2091} 2092 2093static int genfkeyCmdCb(void *pCtx, int eType, const char *z){ 2094 GenfkeyCmd *p = (GenfkeyCmd *)pCtx; 2095 if( eType==GENFKEY_ERROR && !p->isIgnoreErrors ){ 2096 p->nErr++; 2097 fprintf(stderr, "%s\n", z); 2098 } 2099 2100 if( p->nErr==0 && ( 2101 (eType==GENFKEY_CREATETRIGGER) 2102 || (eType==GENFKEY_DROPTRIGGER && !p->isNoDrop) 2103 )){ 2104 if( p->isExec ){ 2105 sqlite3_exec(p->db, z, 0, 0, 0); 2106 }else{ 2107 char *zCol = "sql"; 2108 callback((void *)p->pCb, 1, (char **)&z, (char **)&zCol); 2109 } 2110 } 2111 2112 return SQLITE_OK; 2113} 2114#endif 2115 2116/* 2117** Text of a help message 2118*/ 2119static char zHelp[] = 2120 ".backup ?DB? FILE Backup DB (default \"main\") to FILE\n" 2121 ".bail ON|OFF Stop after hitting an error. Default OFF\n" 2122 ".databases List names and files of attached databases\n" 2123 ".dump ?TABLE? ... Dump the database in an SQL text format\n" 2124 " If TABLE specified, only dump tables matching\n" 2125 " LIKE pattern TABLE.\n" 2126 ".echo ON|OFF Turn command echo on or off\n" 2127 ".exit Exit this program\n" 2128 ".explain ?ON|OFF? Turn output mode suitable for EXPLAIN on or off.\n" 2129 " With no args, it turns EXPLAIN on.\n" 2130#if !defined(SQLITE_OMIT_VIRTUALTABLE) && !defined(SQLITE_OMIT_SUBQUERY) 2131 ".genfkey ?OPTIONS? Options are:\n" 2132 " --no-drop: Do not drop old fkey triggers.\n" 2133 " --ignore-errors: Ignore tables with fkey errors\n" 2134 " --exec: Execute generated SQL immediately\n" 2135 " See file tool/genfkey.README in the source \n" 2136 " distribution for further information.\n" 2137#endif 2138 ".header(s) ON|OFF Turn display of headers on or off\n" 2139 ".help Show this message\n" 2140 ".import FILE TABLE Import data from FILE into TABLE\n" 2141 ".indices ?TABLE? Show names of all indices\n" 2142 " If TABLE specified, only show indices for tables\n" 2143 " matching LIKE pattern TABLE.\n" 2144#ifdef SQLITE_ENABLE_IOTRACE 2145 ".iotrace FILE Enable I/O diagnostic logging to FILE\n" 2146#endif 2147#ifndef SQLITE_OMIT_LOAD_EXTENSION 2148 ".load FILE ?ENTRY? Load an extension library\n" 2149#endif 2150 ".mode MODE ?TABLE? Set output mode where MODE is one of:\n" 2151 " csv Comma-separated values\n" 2152 " column Left-aligned columns. (See .width)\n" 2153 " html HTML <table> code\n" 2154 " insert SQL insert statements for TABLE\n" 2155 " line One value per line\n" 2156 " list Values delimited by .separator string\n" 2157 " tabs Tab-separated values\n" 2158 " tcl TCL list elements\n" 2159 ".nullvalue STRING Print STRING in place of NULL values\n" 2160 ".output FILENAME Send output to FILENAME\n" 2161 ".output stdout Send output to the screen\n" 2162 ".prompt MAIN CONTINUE Replace the standard prompts\n" 2163 ".quit Exit this program\n" 2164 ".read FILENAME Execute SQL in FILENAME\n" 2165 ".restore ?DB? FILE Restore content of DB (default \"main\") from FILE\n" 2166 ".schema ?TABLE? Show the CREATE statements\n" 2167 " If TABLE specified, only show tables matching\n" 2168 " LIKE pattern TABLE.\n" 2169 ".separator STRING Change separator used by output mode and .import\n" 2170 ".show Show the current values for various settings\n" 2171 ".tables ?TABLE? List names of tables\n" 2172 " If TABLE specified, only list tables matching\n" 2173 " LIKE pattern TABLE.\n" 2174 ".timeout MS Try opening locked tables for MS milliseconds\n" 2175 ".width NUM1 NUM2 ... Set column widths for \"column\" mode\n" 2176; 2177 2178static char zTimerHelp[] = 2179 ".timer ON|OFF Turn the CPU timer measurement on or off\n" 2180; 2181 2182/* Forward reference */ 2183static int process_input(struct callback_data *p, FILE *in); 2184 2185/* 2186** Make sure the database is open. If it is not, then open it. If 2187** the database fails to open, print an error message and exit. 2188*/ 2189static void open_db(struct callback_data *p){ 2190 if( p->db==0 ){ 2191 sqlite3_open(p->zDbFilename, &p->db); 2192 db = p->db; 2193 if( db && sqlite3_errcode(db)==SQLITE_OK ){ 2194 sqlite3_create_function(db, "shellstatic", 0, SQLITE_UTF8, 0, 2195 shellstaticFunc, 0, 0); 2196 } 2197 if( db==0 || SQLITE_OK!=sqlite3_errcode(db) ){ 2198 fprintf(stderr,"Error: unable to open database \"%s\": %s\n", 2199 p->zDbFilename, sqlite3_errmsg(db)); 2200 exit(1); 2201 } 2202#ifndef SQLITE_OMIT_LOAD_EXTENSION 2203 sqlite3_enable_load_extension(p->db, 1); 2204#endif 2205 } 2206} 2207 2208/* 2209** Do C-language style dequoting. 2210** 2211** \t -> tab 2212** \n -> newline 2213** \r -> carriage return 2214** \NNN -> ascii character NNN in octal 2215** \\ -> backslash 2216*/ 2217static void resolve_backslashes(char *z){ 2218 int i, j; 2219 char c; 2220 for(i=j=0; (c = z[i])!=0; i++, j++){ 2221 if( c=='\\' ){ 2222 c = z[++i]; 2223 if( c=='n' ){ 2224 c = '\n'; 2225 }else if( c=='t' ){ 2226 c = '\t'; 2227 }else if( c=='r' ){ 2228 c = '\r'; 2229 }else if( c>='0' && c<='7' ){ 2230 c -= '0'; 2231 if( z[i+1]>='0' && z[i+1]<='7' ){ 2232 i++; 2233 c = (c<<3) + z[i] - '0'; 2234 if( z[i+1]>='0' && z[i+1]<='7' ){ 2235 i++; 2236 c = (c<<3) + z[i] - '0'; 2237 } 2238 } 2239 } 2240 } 2241 z[j] = c; 2242 } 2243 z[j] = 0; 2244} 2245 2246/* 2247** Interpret zArg as a boolean value. Return either 0 or 1. 2248*/ 2249static int booleanValue(char *zArg){ 2250 int val = atoi(zArg); 2251 int j; 2252 for(j=0; zArg[j]; j++){ 2253 zArg[j] = (char)tolower(zArg[j]); 2254 } 2255 if( strcmp(zArg,"on")==0 ){ 2256 val = 1; 2257 }else if( strcmp(zArg,"yes")==0 ){ 2258 val = 1; 2259 } 2260 return val; 2261} 2262 2263/* 2264** If an input line begins with "." then invoke this routine to 2265** process that line. 2266** 2267** Return 1 on error, 2 to exit, and 0 otherwise. 2268*/ 2269static int do_meta_command(char *zLine, struct callback_data *p){ 2270 int i = 1; 2271 int nArg = 0; 2272 int n, c; 2273 int rc = 0; 2274 char *azArg[50]; 2275 2276 /* Parse the input line into tokens. 2277 */ 2278 while( zLine[i] && nArg<ArraySize(azArg) ){ 2279 while( isspace((unsigned char)zLine[i]) ){ i++; } 2280 if( zLine[i]==0 ) break; 2281 if( zLine[i]=='\'' || zLine[i]=='"' ){ 2282 int delim = zLine[i++]; 2283 azArg[nArg++] = &zLine[i]; 2284 while( zLine[i] && zLine[i]!=delim ){ i++; } 2285 if( zLine[i]==delim ){ 2286 zLine[i++] = 0; 2287 } 2288 if( delim=='"' ) resolve_backslashes(azArg[nArg-1]); 2289 }else{ 2290 azArg[nArg++] = &zLine[i]; 2291 while( zLine[i] && !isspace((unsigned char)zLine[i]) ){ i++; } 2292 if( zLine[i] ) zLine[i++] = 0; 2293 resolve_backslashes(azArg[nArg-1]); 2294 } 2295 } 2296 2297 /* Process the input line. 2298 */ 2299 if( nArg==0 ) return 0; /* no tokens, no error */ 2300 n = strlen30(azArg[0]); 2301 c = azArg[0][0]; 2302 if( c=='b' && n>=3 && strncmp(azArg[0], "backup", n)==0 && nArg>1 && nArg<4){ 2303 const char *zDestFile; 2304 const char *zDb; 2305 sqlite3 *pDest; 2306 sqlite3_backup *pBackup; 2307 if( nArg==2 ){ 2308 zDestFile = azArg[1]; 2309 zDb = "main"; 2310 }else{ 2311 zDestFile = azArg[2]; 2312 zDb = azArg[1]; 2313 } 2314 rc = sqlite3_open(zDestFile, &pDest); 2315 if( rc!=SQLITE_OK ){ 2316 fprintf(stderr, "Error: cannot open \"%s\"\n", zDestFile); 2317 sqlite3_close(pDest); 2318 return 1; 2319 } 2320 open_db(p); 2321 pBackup = sqlite3_backup_init(pDest, "main", p->db, zDb); 2322 if( pBackup==0 ){ 2323 fprintf(stderr, "Error: %s\n", sqlite3_errmsg(pDest)); 2324 sqlite3_close(pDest); 2325 return 1; 2326 } 2327 while( (rc = sqlite3_backup_step(pBackup,100))==SQLITE_OK ){} 2328 sqlite3_backup_finish(pBackup); 2329 if( rc==SQLITE_DONE ){ 2330 rc = 0; 2331 }else{ 2332 fprintf(stderr, "Error: %s\n", sqlite3_errmsg(pDest)); 2333 rc = 1; 2334 } 2335 sqlite3_close(pDest); 2336 }else 2337 2338 if( c=='b' && n>=3 && strncmp(azArg[0], "bail", n)==0 && nArg>1 && nArg<3 ){ 2339 bail_on_error = booleanValue(azArg[1]); 2340 }else 2341 2342 if( c=='d' && n>1 && strncmp(azArg[0], "databases", n)==0 && nArg==1 ){ 2343 struct callback_data data; 2344 char *zErrMsg = 0; 2345 open_db(p); 2346 memcpy(&data, p, sizeof(data)); 2347 data.showHeader = 1; 2348 data.mode = MODE_Column; 2349 data.colWidth[0] = 3; 2350 data.colWidth[1] = 15; 2351 data.colWidth[2] = 58; 2352 data.cnt = 0; 2353 sqlite3_exec(p->db, "PRAGMA database_list; ", callback, &data, &zErrMsg); 2354 if( zErrMsg ){ 2355 fprintf(stderr,"Error: %s\n", zErrMsg); 2356 sqlite3_free(zErrMsg); 2357 rc = 1; 2358 } 2359 }else 2360 2361 if( c=='d' && strncmp(azArg[0], "dump", n)==0 && nArg<3 ){ 2362 char *zErrMsg = 0; 2363 open_db(p); 2364 /* When playing back a "dump", the content might appear in an order 2365 ** which causes immediate foreign key constraints to be violated. 2366 ** So disable foreign-key constraint enforcement to prevent problems. */ 2367 fprintf(p->out, "PRAGMA foreign_keys=OFF;\n"); 2368 fprintf(p->out, "BEGIN TRANSACTION;\n"); 2369 p->writableSchema = 0; 2370 sqlite3_exec(p->db, "PRAGMA writable_schema=ON", 0, 0, 0); 2371 if( nArg==1 ){ 2372 run_schema_dump_query(p, 2373 "SELECT name, type, sql FROM sqlite_master " 2374 "WHERE sql NOT NULL AND type=='table' AND name!='sqlite_sequence'", 0 2375 ); 2376 run_schema_dump_query(p, 2377 "SELECT name, type, sql FROM sqlite_master " 2378 "WHERE name=='sqlite_sequence'", 0 2379 ); 2380 run_table_dump_query(p->out, p->db, 2381 "SELECT sql FROM sqlite_master " 2382 "WHERE sql NOT NULL AND type IN ('index','trigger','view')", 0 2383 ); 2384 }else{ 2385 int i; 2386 for(i=1; i<nArg; i++){ 2387 zShellStatic = azArg[i]; 2388 run_schema_dump_query(p, 2389 "SELECT name, type, sql FROM sqlite_master " 2390 "WHERE tbl_name LIKE shellstatic() AND type=='table'" 2391 " AND sql NOT NULL", 0); 2392 run_table_dump_query(p->out, p->db, 2393 "SELECT sql FROM sqlite_master " 2394 "WHERE sql NOT NULL" 2395 " AND type IN ('index','trigger','view')" 2396 " AND tbl_name LIKE shellstatic()", 0 2397 ); 2398 zShellStatic = 0; 2399 } 2400 } 2401 if( p->writableSchema ){ 2402 fprintf(p->out, "PRAGMA writable_schema=OFF;\n"); 2403 p->writableSchema = 0; 2404 } 2405 sqlite3_exec(p->db, "PRAGMA writable_schema=OFF", 0, 0, 0); 2406 if( zErrMsg ){ 2407 fprintf(stderr,"Error: %s\n", zErrMsg); 2408 sqlite3_free(zErrMsg); 2409 }else{ 2410 fprintf(p->out, "COMMIT;\n"); 2411 } 2412 }else 2413 2414 if( c=='e' && strncmp(azArg[0], "echo", n)==0 && nArg>1 && nArg<3 ){ 2415 p->echoOn = booleanValue(azArg[1]); 2416 }else 2417 2418 if( c=='e' && strncmp(azArg[0], "exit", n)==0 && nArg==1 ){ 2419 rc = 2; 2420 }else 2421 2422 if( c=='e' && strncmp(azArg[0], "explain", n)==0 && nArg<3 ){ 2423 int val = nArg>=2 ? booleanValue(azArg[1]) : 1; 2424 if(val == 1) { 2425 if(!p->explainPrev.valid) { 2426 p->explainPrev.valid = 1; 2427 p->explainPrev.mode = p->mode; 2428 p->explainPrev.showHeader = p->showHeader; 2429 memcpy(p->explainPrev.colWidth,p->colWidth,sizeof(p->colWidth)); 2430 } 2431 /* We could put this code under the !p->explainValid 2432 ** condition so that it does not execute if we are already in 2433 ** explain mode. However, always executing it allows us an easy 2434 ** was to reset to explain mode in case the user previously 2435 ** did an .explain followed by a .width, .mode or .header 2436 ** command. 2437 */ 2438 p->mode = MODE_Explain; 2439 p->showHeader = 1; 2440 memset(p->colWidth,0,ArraySize(p->colWidth)); 2441 p->colWidth[0] = 4; /* addr */ 2442 p->colWidth[1] = 13; /* opcode */ 2443 p->colWidth[2] = 4; /* P1 */ 2444 p->colWidth[3] = 4; /* P2 */ 2445 p->colWidth[4] = 4; /* P3 */ 2446 p->colWidth[5] = 13; /* P4 */ 2447 p->colWidth[6] = 2; /* P5 */ 2448 p->colWidth[7] = 13; /* Comment */ 2449 }else if (p->explainPrev.valid) { 2450 p->explainPrev.valid = 0; 2451 p->mode = p->explainPrev.mode; 2452 p->showHeader = p->explainPrev.showHeader; 2453 memcpy(p->colWidth,p->explainPrev.colWidth,sizeof(p->colWidth)); 2454 } 2455 }else 2456 2457#if !defined(SQLITE_OMIT_VIRTUALTABLE) && !defined(SQLITE_OMIT_SUBQUERY) 2458 if( c=='g' && strncmp(azArg[0], "genfkey", n)==0 ){ 2459 GenfkeyCmd cmd; 2460 if( 0==genfkeyParseArgs(&cmd, &azArg[1], nArg-1) ){ 2461 cmd.db = p->db; 2462 cmd.pCb = p; 2463 genfkey_create_triggers(p->db, "main", (void *)&cmd, genfkeyCmdCb); 2464 } 2465 }else 2466#endif 2467 2468 if( c=='h' && (strncmp(azArg[0], "header", n)==0 || 2469 strncmp(azArg[0], "headers", n)==0) && nArg>1 && nArg<3 ){ 2470 p->showHeader = booleanValue(azArg[1]); 2471 }else 2472 2473 if( c=='h' && strncmp(azArg[0], "help", n)==0 ){ 2474 fprintf(stderr,"%s",zHelp); 2475 if( HAS_TIMER ){ 2476 fprintf(stderr,"%s",zTimerHelp); 2477 } 2478 }else 2479 2480 if( c=='i' && strncmp(azArg[0], "import", n)==0 && nArg==3 ){ 2481 char *zTable = azArg[2]; /* Insert data into this table */ 2482 char *zFile = azArg[1]; /* The file from which to extract data */ 2483 sqlite3_stmt *pStmt = NULL; /* A statement */ 2484 int nCol; /* Number of columns in the table */ 2485 int nByte; /* Number of bytes in an SQL string */ 2486 int i, j; /* Loop counters */ 2487 int nSep; /* Number of bytes in p->separator[] */ 2488 char *zSql; /* An SQL statement */ 2489 char *zLine; /* A single line of input from the file */ 2490 char **azCol; /* zLine[] broken up into columns */ 2491 char *zCommit; /* How to commit changes */ 2492 FILE *in; /* The input file */ 2493 int lineno = 0; /* Line number of input file */ 2494 2495 open_db(p); 2496 nSep = strlen30(p->separator); 2497 if( nSep==0 ){ 2498 fprintf(stderr, "Error: non-null separator required for import\n"); 2499 return 1; 2500 } 2501 zSql = sqlite3_mprintf("SELECT * FROM '%q'", zTable); 2502 if( zSql==0 ){ 2503 fprintf(stderr, "Error: out of memory\n"); 2504 return 1; 2505 } 2506 nByte = strlen30(zSql); 2507 rc = sqlite3_prepare(p->db, zSql, -1, &pStmt, 0); 2508 sqlite3_free(zSql); 2509 if( rc ){ 2510 if (pStmt) sqlite3_finalize(pStmt); 2511 fprintf(stderr,"Error: %s\n", sqlite3_errmsg(db)); 2512 return 1; 2513 } 2514 nCol = sqlite3_column_count(pStmt); 2515 sqlite3_finalize(pStmt); 2516 pStmt = 0; 2517 if( nCol==0 ) return 0; /* no columns, no error */ 2518 zSql = malloc( nByte + 20 + nCol*2 ); 2519 if( zSql==0 ){ 2520 fprintf(stderr, "Error: out of memory\n"); 2521 return 1; 2522 } 2523 sqlite3_snprintf(nByte+20, zSql, "INSERT INTO '%q' VALUES(?", zTable); 2524 j = strlen30(zSql); 2525 for(i=1; i<nCol; i++){ 2526 zSql[j++] = ','; 2527 zSql[j++] = '?'; 2528 } 2529 zSql[j++] = ')'; 2530 zSql[j] = 0; 2531 rc = sqlite3_prepare(p->db, zSql, -1, &pStmt, 0); 2532 free(zSql); 2533 if( rc ){ 2534 fprintf(stderr, "Error: %s\n", sqlite3_errmsg(db)); 2535 if (pStmt) sqlite3_finalize(pStmt); 2536 return 1; 2537 } 2538 in = fopen(zFile, "rb"); 2539 if( in==0 ){ 2540 fprintf(stderr, "Error: cannot open \"%s\"\n", zFile); 2541 sqlite3_finalize(pStmt); 2542 return 1; 2543 } 2544 azCol = malloc( sizeof(azCol[0])*(nCol+1) ); 2545 if( azCol==0 ){ 2546 fprintf(stderr, "Error: out of memory\n"); 2547 fclose(in); 2548 sqlite3_finalize(pStmt); 2549 return 1; 2550 } 2551 sqlite3_exec(p->db, "BEGIN", 0, 0, 0); 2552 zCommit = "COMMIT"; 2553 while( (zLine = local_getline(0, in))!=0 ){ 2554 char *z; 2555 i = 0; 2556 lineno++; 2557 azCol[0] = zLine; 2558 for(i=0, z=zLine; *z && *z!='\n' && *z!='\r'; z++){ 2559 if( *z==p->separator[0] && strncmp(z, p->separator, nSep)==0 ){ 2560 *z = 0; 2561 i++; 2562 if( i<nCol ){ 2563 azCol[i] = &z[nSep]; 2564 z += nSep-1; 2565 } 2566 } 2567 } /* end for */ 2568 *z = 0; 2569 if( i+1!=nCol ){ 2570 fprintf(stderr, 2571 "Error: %s line %d: expected %d columns of data but found %d\n", 2572 zFile, lineno, nCol, i+1); 2573 zCommit = "ROLLBACK"; 2574 free(zLine); 2575 rc = 1; 2576 break; /* from while */ 2577 } 2578 for(i=0; i<nCol; i++){ 2579 sqlite3_bind_text(pStmt, i+1, azCol[i], -1, SQLITE_STATIC); 2580 } 2581 sqlite3_step(pStmt); 2582 rc = sqlite3_reset(pStmt); 2583 free(zLine); 2584 if( rc!=SQLITE_OK ){ 2585 fprintf(stderr,"Error: %s\n", sqlite3_errmsg(db)); 2586 zCommit = "ROLLBACK"; 2587 rc = 1; 2588 break; /* from while */ 2589 } 2590 } /* end while */ 2591 free(azCol); 2592 fclose(in); 2593 sqlite3_finalize(pStmt); 2594 sqlite3_exec(p->db, zCommit, 0, 0, 0); 2595 }else 2596 2597 if( c=='i' && strncmp(azArg[0], "indices", n)==0 && nArg<3 ){ 2598 struct callback_data data; 2599 char *zErrMsg = 0; 2600 open_db(p); 2601 memcpy(&data, p, sizeof(data)); 2602 data.showHeader = 0; 2603 data.mode = MODE_List; 2604 if( nArg==1 ){ 2605 rc = sqlite3_exec(p->db, 2606 "SELECT name FROM sqlite_master " 2607 "WHERE type='index' AND name NOT LIKE 'sqlite_%' " 2608 "UNION ALL " 2609 "SELECT name FROM sqlite_temp_master " 2610 "WHERE type='index' " 2611 "ORDER BY 1", 2612 callback, &data, &zErrMsg 2613 ); 2614 }else{ 2615 zShellStatic = azArg[1]; 2616 rc = sqlite3_exec(p->db, 2617 "SELECT name FROM sqlite_master " 2618 "WHERE type='index' AND tbl_name LIKE shellstatic() " 2619 "UNION ALL " 2620 "SELECT name FROM sqlite_temp_master " 2621 "WHERE type='index' AND tbl_name LIKE shellstatic() " 2622 "ORDER BY 1", 2623 callback, &data, &zErrMsg 2624 ); 2625 zShellStatic = 0; 2626 } 2627 if( zErrMsg ){ 2628 fprintf(stderr,"Error: %s\n", zErrMsg); 2629 sqlite3_free(zErrMsg); 2630 rc = 1; 2631 }else if( rc != SQLITE_OK ){ 2632 fprintf(stderr,"Error: querying sqlite_master and sqlite_temp_master\n"); 2633 rc = 1; 2634 } 2635 }else 2636 2637#ifdef SQLITE_ENABLE_IOTRACE 2638 if( c=='i' && strncmp(azArg[0], "iotrace", n)==0 ){ 2639 extern void (*sqlite3IoTrace)(const char*, ...); 2640 if( iotrace && iotrace!=stdout ) fclose(iotrace); 2641 iotrace = 0; 2642 if( nArg<2 ){ 2643 sqlite3IoTrace = 0; 2644 }else if( strcmp(azArg[1], "-")==0 ){ 2645 sqlite3IoTrace = iotracePrintf; 2646 iotrace = stdout; 2647 }else{ 2648 iotrace = fopen(azArg[1], "w"); 2649 if( iotrace==0 ){ 2650 fprintf(stderr, "Error: cannot open \"%s\"\n", azArg[1]); 2651 sqlite3IoTrace = 0; 2652 rc = 1; 2653 }else{ 2654 sqlite3IoTrace = iotracePrintf; 2655 } 2656 } 2657 }else 2658#endif 2659 2660#ifndef SQLITE_OMIT_LOAD_EXTENSION 2661 if( c=='l' && strncmp(azArg[0], "load", n)==0 && nArg>=2 ){ 2662 const char *zFile, *zProc; 2663 char *zErrMsg = 0; 2664 zFile = azArg[1]; 2665 zProc = nArg>=3 ? azArg[2] : 0; 2666 open_db(p); 2667 rc = sqlite3_load_extension(p->db, zFile, zProc, &zErrMsg); 2668 if( rc!=SQLITE_OK ){ 2669 fprintf(stderr, "Error: %s\n", zErrMsg); 2670 sqlite3_free(zErrMsg); 2671 rc = 1; 2672 } 2673 }else 2674#endif 2675 2676 if( c=='m' && strncmp(azArg[0], "mode", n)==0 && nArg==2 ){ 2677 int n2 = strlen30(azArg[1]); 2678 if( (n2==4 && strncmp(azArg[1],"line",n2)==0) 2679 || 2680 (n2==5 && strncmp(azArg[1],"lines",n2)==0) ){ 2681 p->mode = MODE_Line; 2682 }else if( (n2==6 && strncmp(azArg[1],"column",n2)==0) 2683 || 2684 (n2==7 && strncmp(azArg[1],"columns",n2)==0) ){ 2685 p->mode = MODE_Column; 2686 }else if( n2==4 && strncmp(azArg[1],"list",n2)==0 ){ 2687 p->mode = MODE_List; 2688 }else if( n2==4 && strncmp(azArg[1],"html",n2)==0 ){ 2689 p->mode = MODE_Html; 2690 }else if( n2==3 && strncmp(azArg[1],"tcl",n2)==0 ){ 2691 p->mode = MODE_Tcl; 2692 }else if( n2==3 && strncmp(azArg[1],"csv",n2)==0 ){ 2693 p->mode = MODE_Csv; 2694 sqlite3_snprintf(sizeof(p->separator), p->separator, ","); 2695 }else if( n2==4 && strncmp(azArg[1],"tabs",n2)==0 ){ 2696 p->mode = MODE_List; 2697 sqlite3_snprintf(sizeof(p->separator), p->separator, "\t"); 2698 }else if( n2==6 && strncmp(azArg[1],"insert",n2)==0 ){ 2699 p->mode = MODE_Insert; 2700 set_table_name(p, "table"); 2701 }else { 2702 fprintf(stderr,"Error: mode should be one of: " 2703 "column csv html insert line list tabs tcl\n"); 2704 rc = 1; 2705 } 2706 }else 2707 2708 if( c=='m' && strncmp(azArg[0], "mode", n)==0 && nArg==3 ){ 2709 int n2 = strlen30(azArg[1]); 2710 if( n2==6 && strncmp(azArg[1],"insert",n2)==0 ){ 2711 p->mode = MODE_Insert; 2712 set_table_name(p, azArg[2]); 2713 }else { 2714 fprintf(stderr, "Error: invalid arguments: " 2715 " \"%s\". Enter \".help\" for help\n", azArg[2]); 2716 rc = 1; 2717 } 2718 }else 2719 2720 if( c=='n' && strncmp(azArg[0], "nullvalue", n)==0 && nArg==2 ) { 2721 sqlite3_snprintf(sizeof(p->nullvalue), p->nullvalue, 2722 "%.*s", (int)ArraySize(p->nullvalue)-1, azArg[1]); 2723 }else 2724 2725 if( c=='o' && strncmp(azArg[0], "output", n)==0 && nArg==2 ){ 2726 if( p->out!=stdout ){ 2727 fclose(p->out); 2728 } 2729 if( strcmp(azArg[1],"stdout")==0 ){ 2730 p->out = stdout; 2731 sqlite3_snprintf(sizeof(p->outfile), p->outfile, "stdout"); 2732 }else{ 2733 p->out = fopen(azArg[1], "wb"); 2734 if( p->out==0 ){ 2735 fprintf(stderr,"Error: cannot write to \"%s\"\n", azArg[1]); 2736 p->out = stdout; 2737 rc = 1; 2738 } else { 2739 sqlite3_snprintf(sizeof(p->outfile), p->outfile, "%s", azArg[1]); 2740 } 2741 } 2742 }else 2743 2744 if( c=='p' && strncmp(azArg[0], "prompt", n)==0 && (nArg==2 || nArg==3)){ 2745 if( nArg >= 2) { 2746 strncpy(mainPrompt,azArg[1],(int)ArraySize(mainPrompt)-1); 2747 } 2748 if( nArg >= 3) { 2749 strncpy(continuePrompt,azArg[2],(int)ArraySize(continuePrompt)-1); 2750 } 2751 }else 2752 2753 if( c=='q' && strncmp(azArg[0], "quit", n)==0 && nArg==1 ){ 2754 rc = 2; 2755 }else 2756 2757 if( c=='r' && n>=3 && strncmp(azArg[0], "read", n)==0 && nArg==2 ){ 2758 FILE *alt = fopen(azArg[1], "rb"); 2759 if( alt==0 ){ 2760 fprintf(stderr,"Error: cannot open \"%s\"\n", azArg[1]); 2761 rc = 1; 2762 }else{ 2763 rc = process_input(p, alt); 2764 fclose(alt); 2765 } 2766 }else 2767 2768 if( c=='r' && n>=3 && strncmp(azArg[0], "restore", n)==0 && nArg>1 && nArg<4){ 2769 const char *zSrcFile; 2770 const char *zDb; 2771 sqlite3 *pSrc; 2772 sqlite3_backup *pBackup; 2773 int nTimeout = 0; 2774 2775 if( nArg==2 ){ 2776 zSrcFile = azArg[1]; 2777 zDb = "main"; 2778 }else{ 2779 zSrcFile = azArg[2]; 2780 zDb = azArg[1]; 2781 } 2782 rc = sqlite3_open(zSrcFile, &pSrc); 2783 if( rc!=SQLITE_OK ){ 2784 fprintf(stderr, "Error: cannot open \"%s\"\n", zSrcFile); 2785 sqlite3_close(pSrc); 2786 return 1; 2787 } 2788 open_db(p); 2789 pBackup = sqlite3_backup_init(p->db, zDb, pSrc, "main"); 2790 if( pBackup==0 ){ 2791 fprintf(stderr, "Error: %s\n", sqlite3_errmsg(p->db)); 2792 sqlite3_close(pSrc); 2793 return 1; 2794 } 2795 while( (rc = sqlite3_backup_step(pBackup,100))==SQLITE_OK 2796 || rc==SQLITE_BUSY ){ 2797 if( rc==SQLITE_BUSY ){ 2798 if( nTimeout++ >= 3 ) break; 2799 sqlite3_sleep(100); 2800 } 2801 } 2802 sqlite3_backup_finish(pBackup); 2803 if( rc==SQLITE_DONE ){ 2804 rc = 0; 2805 }else if( rc==SQLITE_BUSY || rc==SQLITE_LOCKED ){ 2806 fprintf(stderr, "Error: source database is busy\n"); 2807 rc = 1; 2808 }else{ 2809 fprintf(stderr, "Error: %s\n", sqlite3_errmsg(p->db)); 2810 rc = 1; 2811 } 2812 sqlite3_close(pSrc); 2813 }else 2814 2815 if( c=='s' && strncmp(azArg[0], "schema", n)==0 && nArg<3 ){ 2816 struct callback_data data; 2817 char *zErrMsg = 0; 2818 open_db(p); 2819 memcpy(&data, p, sizeof(data)); 2820 data.showHeader = 0; 2821 data.mode = MODE_Semi; 2822 if( nArg>1 ){ 2823 int i; 2824 for(i=0; azArg[1][i]; i++) azArg[1][i] = (char)tolower(azArg[1][i]); 2825 if( strcmp(azArg[1],"sqlite_master")==0 ){ 2826 char *new_argv[2], *new_colv[2]; 2827 new_argv[0] = "CREATE TABLE sqlite_master (\n" 2828 " type text,\n" 2829 " name text,\n" 2830 " tbl_name text,\n" 2831 " rootpage integer,\n" 2832 " sql text\n" 2833 ")"; 2834 new_argv[1] = 0; 2835 new_colv[0] = "sql"; 2836 new_colv[1] = 0; 2837 callback(&data, 1, new_argv, new_colv); 2838 rc = SQLITE_OK; 2839 }else if( strcmp(azArg[1],"sqlite_temp_master")==0 ){ 2840 char *new_argv[2], *new_colv[2]; 2841 new_argv[0] = "CREATE TEMP TABLE sqlite_temp_master (\n" 2842 " type text,\n" 2843 " name text,\n" 2844 " tbl_name text,\n" 2845 " rootpage integer,\n" 2846 " sql text\n" 2847 ")"; 2848 new_argv[1] = 0; 2849 new_colv[0] = "sql"; 2850 new_colv[1] = 0; 2851 callback(&data, 1, new_argv, new_colv); 2852 rc = SQLITE_OK; 2853 }else{ 2854 zShellStatic = azArg[1]; 2855 rc = sqlite3_exec(p->db, 2856 "SELECT sql FROM " 2857 " (SELECT sql sql, type type, tbl_name tbl_name, name name" 2858 " FROM sqlite_master UNION ALL" 2859 " SELECT sql, type, tbl_name, name FROM sqlite_temp_master) " 2860 "WHERE tbl_name LIKE shellstatic() AND type!='meta' AND sql NOTNULL " 2861 "ORDER BY substr(type,2,1), name", 2862 callback, &data, &zErrMsg); 2863 zShellStatic = 0; 2864 } 2865 }else{ 2866 rc = sqlite3_exec(p->db, 2867 "SELECT sql FROM " 2868 " (SELECT sql sql, type type, tbl_name tbl_name, name name" 2869 " FROM sqlite_master UNION ALL" 2870 " SELECT sql, type, tbl_name, name FROM sqlite_temp_master) " 2871 "WHERE type!='meta' AND sql NOTNULL AND name NOT LIKE 'sqlite_%'" 2872 "ORDER BY substr(type,2,1), name", 2873 callback, &data, &zErrMsg 2874 ); 2875 } 2876 if( zErrMsg ){ 2877 fprintf(stderr,"Error: %s\n", zErrMsg); 2878 sqlite3_free(zErrMsg); 2879 rc = 1; 2880 }else if( rc != SQLITE_OK ){ 2881 fprintf(stderr,"Error: querying schema information\n"); 2882 rc = 1; 2883 }else{ 2884 rc = 0; 2885 } 2886 }else 2887 2888 if( c=='s' && strncmp(azArg[0], "separator", n)==0 && nArg==2 ){ 2889 sqlite3_snprintf(sizeof(p->separator), p->separator, 2890 "%.*s", (int)sizeof(p->separator)-1, azArg[1]); 2891 }else 2892 2893 if( c=='s' && strncmp(azArg[0], "show", n)==0 && nArg==1 ){ 2894 int i; 2895 fprintf(p->out,"%9.9s: %s\n","echo", p->echoOn ? "on" : "off"); 2896 fprintf(p->out,"%9.9s: %s\n","explain", p->explainPrev.valid ? "on" :"off"); 2897 fprintf(p->out,"%9.9s: %s\n","headers", p->showHeader ? "on" : "off"); 2898 fprintf(p->out,"%9.9s: %s\n","mode", modeDescr[p->mode]); 2899 fprintf(p->out,"%9.9s: ", "nullvalue"); 2900 output_c_string(p->out, p->nullvalue); 2901 fprintf(p->out, "\n"); 2902 fprintf(p->out,"%9.9s: %s\n","output", 2903 strlen30(p->outfile) ? p->outfile : "stdout"); 2904 fprintf(p->out,"%9.9s: ", "separator"); 2905 output_c_string(p->out, p->separator); 2906 fprintf(p->out, "\n"); 2907 fprintf(p->out,"%9.9s: ","width"); 2908 for (i=0;i<(int)ArraySize(p->colWidth) && p->colWidth[i] != 0;i++) { 2909 fprintf(p->out,"%d ",p->colWidth[i]); 2910 } 2911 fprintf(p->out,"\n"); 2912 }else 2913 2914 if( c=='t' && n>1 && strncmp(azArg[0], "tables", n)==0 && nArg<3 ){ 2915 char **azResult; 2916 int nRow; 2917 char *zErrMsg; 2918 open_db(p); 2919 if( nArg==1 ){ 2920 rc = sqlite3_get_table(p->db, 2921 "SELECT name FROM sqlite_master " 2922 "WHERE type IN ('table','view') AND name NOT LIKE 'sqlite_%' " 2923 "UNION ALL " 2924 "SELECT name FROM sqlite_temp_master " 2925 "WHERE type IN ('table','view') " 2926 "ORDER BY 1", 2927 &azResult, &nRow, 0, &zErrMsg 2928 ); 2929 }else{ 2930 zShellStatic = azArg[1]; 2931 rc = sqlite3_get_table(p->db, 2932 "SELECT name FROM sqlite_master " 2933 "WHERE type IN ('table','view') AND name LIKE shellstatic() " 2934 "UNION ALL " 2935 "SELECT name FROM sqlite_temp_master " 2936 "WHERE type IN ('table','view') AND name LIKE shellstatic() " 2937 "ORDER BY 1", 2938 &azResult, &nRow, 0, &zErrMsg 2939 ); 2940 zShellStatic = 0; 2941 } 2942 if( zErrMsg ){ 2943 fprintf(stderr,"Error: %s\n", zErrMsg); 2944 sqlite3_free(zErrMsg); 2945 rc = 1; 2946 }else if( rc != SQLITE_OK ){ 2947 fprintf(stderr,"Error: querying sqlite_master and sqlite_temp_master\n"); 2948 rc = 1; 2949 }else{ 2950 int len, maxlen = 0; 2951 int i, j; 2952 int nPrintCol, nPrintRow; 2953 for(i=1; i<=nRow; i++){ 2954 if( azResult[i]==0 ) continue; 2955 len = strlen30(azResult[i]); 2956 if( len>maxlen ) maxlen = len; 2957 } 2958 nPrintCol = 80/(maxlen+2); 2959 if( nPrintCol<1 ) nPrintCol = 1; 2960 nPrintRow = (nRow + nPrintCol - 1)/nPrintCol; 2961 for(i=0; i<nPrintRow; i++){ 2962 for(j=i+1; j<=nRow; j+=nPrintRow){ 2963 char *zSp = j<=nPrintRow ? "" : " "; 2964 printf("%s%-*s", zSp, maxlen, azResult[j] ? azResult[j] : ""); 2965 } 2966 printf("\n"); 2967 } 2968 } 2969 sqlite3_free_table(azResult); 2970 }else 2971 2972 if( c=='t' && n>4 && strncmp(azArg[0], "timeout", n)==0 && nArg==2 ){ 2973 open_db(p); 2974 sqlite3_busy_timeout(p->db, atoi(azArg[1])); 2975 }else 2976 2977 if( HAS_TIMER && c=='t' && n>=5 && strncmp(azArg[0], "timer", n)==0 && nArg==2 ){ 2978 enableTimer = booleanValue(azArg[1]); 2979 }else 2980 2981 if( c=='w' && strncmp(azArg[0], "width", n)==0 && nArg>1 ){ 2982 int j; 2983 assert( nArg<=ArraySize(azArg) ); 2984 for(j=1; j<nArg && j<ArraySize(p->colWidth); j++){ 2985 p->colWidth[j-1] = atoi(azArg[j]); 2986 } 2987 }else 2988 2989 { 2990 fprintf(stderr, "Error: unknown command or invalid arguments: " 2991 " \"%s\". Enter \".help\" for help\n", azArg[0]); 2992 rc = 1; 2993 } 2994 2995 return rc; 2996} 2997 2998/* 2999** Return TRUE if a semicolon occurs anywhere in the first N characters 3000** of string z[]. 3001*/ 3002static int _contains_semicolon(const char *z, int N){ 3003 int i; 3004 for(i=0; i<N; i++){ if( z[i]==';' ) return 1; } 3005 return 0; 3006} 3007 3008/* 3009** Test to see if a line consists entirely of whitespace. 3010*/ 3011static int _all_whitespace(const char *z){ 3012 for(; *z; z++){ 3013 if( isspace(*(unsigned char*)z) ) continue; 3014 if( *z=='/' && z[1]=='*' ){ 3015 z += 2; 3016 while( *z && (*z!='*' || z[1]!='/') ){ z++; } 3017 if( *z==0 ) return 0; 3018 z++; 3019 continue; 3020 } 3021 if( *z=='-' && z[1]=='-' ){ 3022 z += 2; 3023 while( *z && *z!='\n' ){ z++; } 3024 if( *z==0 ) return 1; 3025 continue; 3026 } 3027 return 0; 3028 } 3029 return 1; 3030} 3031 3032/* 3033** Return TRUE if the line typed in is an SQL command terminator other 3034** than a semi-colon. The SQL Server style "go" command is understood 3035** as is the Oracle "/". 3036*/ 3037static int _is_command_terminator(const char *zLine){ 3038 while( isspace(*(unsigned char*)zLine) ){ zLine++; }; 3039 if( zLine[0]=='/' && _all_whitespace(&zLine[1]) ){ 3040 return 1; /* Oracle */ 3041 } 3042 if( tolower(zLine[0])=='g' && tolower(zLine[1])=='o' 3043 && _all_whitespace(&zLine[2]) ){ 3044 return 1; /* SQL Server */ 3045 } 3046 return 0; 3047} 3048 3049/* 3050** Return true if zSql is a complete SQL statement. Return false if it 3051** ends in the middle of a string literal or C-style comment. 3052*/ 3053static int _is_complete(char *zSql, int nSql){ 3054 int rc; 3055 if( zSql==0 ) return 1; 3056 zSql[nSql] = ';'; 3057 zSql[nSql+1] = 0; 3058 rc = sqlite3_complete(zSql); 3059 zSql[nSql] = 0; 3060 return rc; 3061} 3062 3063/* 3064** Read input from *in and process it. If *in==0 then input 3065** is interactive - the user is typing it it. Otherwise, input 3066** is coming from a file or device. A prompt is issued and history 3067** is saved only if input is interactive. An interrupt signal will 3068** cause this routine to exit immediately, unless input is interactive. 3069** 3070** Return the number of errors. 3071*/ 3072static int process_input(struct callback_data *p, FILE *in){ 3073 char *zLine = 0; 3074 char *zSql = 0; 3075 int nSql = 0; 3076 int nSqlPrior = 0; 3077 char *zErrMsg; 3078 int rc; 3079 int errCnt = 0; 3080 int lineno = 0; 3081 int startline = 0; 3082 3083 while( errCnt==0 || !bail_on_error || (in==0 && stdin_is_interactive) ){ 3084 fflush(p->out); 3085 free(zLine); 3086 zLine = one_input_line(zSql, in); 3087 if( zLine==0 ){ 3088 break; /* We have reached EOF */ 3089 } 3090 if( seenInterrupt ){ 3091 if( in!=0 ) break; 3092 seenInterrupt = 0; 3093 } 3094 lineno++; 3095 if( (zSql==0 || zSql[0]==0) && _all_whitespace(zLine) ) continue; 3096 if( zLine && zLine[0]=='.' && nSql==0 ){ 3097 if( p->echoOn ) printf("%s\n", zLine); 3098 rc = do_meta_command(zLine, p); 3099 if( rc==2 ){ /* exit requested */ 3100 break; 3101 }else if( rc ){ 3102 errCnt++; 3103 } 3104 continue; 3105 } 3106 if( _is_command_terminator(zLine) && _is_complete(zSql, nSql) ){ 3107 memcpy(zLine,";",2); 3108 } 3109 nSqlPrior = nSql; 3110 if( zSql==0 ){ 3111 int i; 3112 for(i=0; zLine[i] && isspace((unsigned char)zLine[i]); i++){} 3113 if( zLine[i]!=0 ){ 3114 nSql = strlen30(zLine); 3115 zSql = malloc( nSql+3 ); 3116 if( zSql==0 ){ 3117 fprintf(stderr, "Error: out of memory\n"); 3118 exit(1); 3119 } 3120 memcpy(zSql, zLine, nSql+1); 3121 startline = lineno; 3122 } 3123 }else{ 3124 int len = strlen30(zLine); 3125 zSql = realloc( zSql, nSql + len + 4 ); 3126 if( zSql==0 ){ 3127 fprintf(stderr,"Error: out of memory\n"); 3128 exit(1); 3129 } 3130 zSql[nSql++] = '\n'; 3131 memcpy(&zSql[nSql], zLine, len+1); 3132 nSql += len; 3133 } 3134 if( zSql && _contains_semicolon(&zSql[nSqlPrior], nSql-nSqlPrior) 3135 && sqlite3_complete(zSql) ){ 3136 p->cnt = 0; 3137 open_db(p); 3138 BEGIN_TIMER; 3139 rc = shell_exec(p->db, zSql, shell_callback, p, &zErrMsg); 3140 END_TIMER; 3141 if( rc || zErrMsg ){ 3142 char zPrefix[100]; 3143 if( in!=0 || !stdin_is_interactive ){ 3144 sqlite3_snprintf(sizeof(zPrefix), zPrefix, 3145 "Error: near line %d:", startline); 3146 }else{ 3147 sqlite3_snprintf(sizeof(zPrefix), zPrefix, "Error:"); 3148 } 3149 if( zErrMsg!=0 ){ 3150 fprintf(stderr, "%s %s\n", zPrefix, zErrMsg); 3151 sqlite3_free(zErrMsg); 3152 zErrMsg = 0; 3153 }else{ 3154 fprintf(stderr, "%s %s\n", zPrefix, sqlite3_errmsg(p->db)); 3155 } 3156 errCnt++; 3157 } 3158 free(zSql); 3159 zSql = 0; 3160 nSql = 0; 3161 } 3162 } 3163 if( zSql ){ 3164 if( !_all_whitespace(zSql) ) fprintf(stderr, "Error: incomplete SQL: %s\n", zSql); 3165 free(zSql); 3166 } 3167 free(zLine); 3168 return errCnt; 3169} 3170 3171/* 3172** Return a pathname which is the user's home directory. A 3173** 0 return indicates an error of some kind. Space to hold the 3174** resulting string is obtained from malloc(). The calling 3175** function should free the result. 3176*/ 3177static char *find_home_dir(void){ 3178 char *home_dir = NULL; 3179 3180#if !defined(_WIN32) && !defined(WIN32) && !defined(__OS2__) && !defined(_WIN32_WCE) && !defined(__RTP__) && !defined(_WRS_KERNEL) 3181 struct passwd *pwent; 3182 uid_t uid = getuid(); 3183 if( (pwent=getpwuid(uid)) != NULL) { 3184 home_dir = pwent->pw_dir; 3185 } 3186#endif 3187 3188#if defined(_WIN32_WCE) 3189 /* Windows CE (arm-wince-mingw32ce-gcc) does not provide getenv() 3190 */ 3191 home_dir = strdup("/"); 3192#else 3193 3194#if defined(_WIN32) || defined(WIN32) || defined(__OS2__) 3195 if (!home_dir) { 3196 home_dir = getenv("USERPROFILE"); 3197 } 3198#endif 3199 3200 if (!home_dir) { 3201 home_dir = getenv("HOME"); 3202 } 3203 3204#if defined(_WIN32) || defined(WIN32) || defined(__OS2__) 3205 if (!home_dir) { 3206 char *zDrive, *zPath; 3207 int n; 3208 zDrive = getenv("HOMEDRIVE"); 3209 zPath = getenv("HOMEPATH"); 3210 if( zDrive && zPath ){ 3211 n = strlen30(zDrive) + strlen30(zPath) + 1; 3212 home_dir = malloc( n ); 3213 if( home_dir==0 ) return 0; 3214 sqlite3_snprintf(n, home_dir, "%s%s", zDrive, zPath); 3215 return home_dir; 3216 } 3217 home_dir = "c:\\"; 3218 } 3219#endif 3220 3221#endif /* !_WIN32_WCE */ 3222 3223 if( home_dir ){ 3224 int n = strlen30(home_dir) + 1; 3225 char *z = malloc( n ); 3226 if( z ) memcpy(z, home_dir, n); 3227 home_dir = z; 3228 } 3229 3230 return home_dir; 3231} 3232 3233/* 3234** Read input from the file given by sqliterc_override. Or if that 3235** parameter is NULL, take input from ~/.sqliterc 3236** 3237** Returns the number of errors. 3238*/ 3239static int process_sqliterc( 3240 struct callback_data *p, /* Configuration data */ 3241 const char *sqliterc_override /* Name of config file. NULL to use default */ 3242){ 3243 char *home_dir = NULL; 3244 const char *sqliterc = sqliterc_override; 3245 char *zBuf = 0; 3246 FILE *in = NULL; 3247 int nBuf; 3248 int rc = 0; 3249 3250 if (sqliterc == NULL) { 3251 home_dir = find_home_dir(); 3252 if( home_dir==0 ){ 3253#if !defined(__RTP__) && !defined(_WRS_KERNEL) 3254 fprintf(stderr,"%s: Error: cannot locate your home directory\n", Argv0); 3255#endif 3256 return 1; 3257 } 3258 nBuf = strlen30(home_dir) + 16; 3259 zBuf = malloc( nBuf ); 3260 if( zBuf==0 ){ 3261 fprintf(stderr,"%s: Error: out of memory\n",Argv0); 3262 return 1; 3263 } 3264 sqlite3_snprintf(nBuf, zBuf,"%s/.sqliterc",home_dir); 3265 free(home_dir); 3266 sqliterc = (const char*)zBuf; 3267 } 3268 in = fopen(sqliterc,"rb"); 3269 if( in ){ 3270 if( stdin_is_interactive ){ 3271 fprintf(stderr,"-- Loading resources from %s\n",sqliterc); 3272 } 3273 rc = process_input(p,in); 3274 fclose(in); 3275 } 3276 free(zBuf); 3277 return rc; 3278} 3279 3280/* 3281** Show available command line options 3282*/ 3283static const char zOptions[] = 3284 " -help show this message\n" 3285 " -init filename read/process named file\n" 3286 " -echo print commands before execution\n" 3287 " -[no]header turn headers on or off\n" 3288 " -bail stop after hitting an error\n" 3289 " -interactive force interactive I/O\n" 3290 " -batch force batch I/O\n" 3291 " -column set output mode to 'column'\n" 3292 " -csv set output mode to 'csv'\n" 3293 " -html set output mode to HTML\n" 3294 " -line set output mode to 'line'\n" 3295 " -list set output mode to 'list'\n" 3296 " -separator 'x' set output field separator (|)\n" 3297 " -nullvalue 'text' set text string for NULL values\n" 3298 " -version show SQLite version\n" 3299; 3300static void usage(int showDetail){ 3301 fprintf(stderr, 3302 "Usage: %s [OPTIONS] FILENAME [SQL]\n" 3303 "FILENAME is the name of an SQLite database. A new database is created\n" 3304 "if the file does not previously exist.\n", Argv0); 3305 if( showDetail ){ 3306 fprintf(stderr, "OPTIONS include:\n%s", zOptions); 3307 }else{ 3308 fprintf(stderr, "Use the -help option for additional information\n"); 3309 } 3310 exit(1); 3311} 3312 3313/* 3314** Initialize the state information in data 3315*/ 3316static void main_init(struct callback_data *data) { 3317 memset(data, 0, sizeof(*data)); 3318 data->mode = MODE_List; 3319 memcpy(data->separator,"|", 2); 3320 data->showHeader = 0; 3321 sqlite3_snprintf(sizeof(mainPrompt), mainPrompt,"sqlite> "); 3322 sqlite3_snprintf(sizeof(continuePrompt), continuePrompt," ...> "); 3323} 3324 3325int main(int argc, char **argv){ 3326 char *zErrMsg = 0; 3327 struct callback_data data; 3328 const char *zInitFile = 0; 3329 char *zFirstCmd = 0; 3330 int i; 3331 int rc = 0; 3332 3333 Argv0 = argv[0]; 3334 main_init(&data); 3335 stdin_is_interactive = isatty(0); 3336 3337 /* Make sure we have a valid signal handler early, before anything 3338 ** else is done. 3339 */ 3340#ifdef SIGINT 3341 signal(SIGINT, interrupt_handler); 3342#endif 3343 3344 /* Do an initial pass through the command-line argument to locate 3345 ** the name of the database file, the name of the initialization file, 3346 ** and the first command to execute. 3347 */ 3348 for(i=1; i<argc-1; i++){ 3349 char *z; 3350 if( argv[i][0]!='-' ) break; 3351 z = argv[i]; 3352 if( z[0]=='-' && z[1]=='-' ) z++; 3353 if( strcmp(argv[i],"-separator")==0 || strcmp(argv[i],"-nullvalue")==0 ){ 3354 i++; 3355 }else if( strcmp(argv[i],"-init")==0 ){ 3356 i++; 3357 zInitFile = argv[i]; 3358 /* Need to check for batch mode here to so we can avoid printing 3359 ** informational messages (like from process_sqliterc) before 3360 ** we do the actual processing of arguments later in a second pass. 3361 */ 3362 }else if( strcmp(argv[i],"-batch")==0 ){ 3363 stdin_is_interactive = 0; 3364 } 3365 } 3366 if( i<argc ){ 3367#if defined(SQLITE_OS_OS2) && SQLITE_OS_OS2 3368 data.zDbFilename = (const char *)convertCpPathToUtf8( argv[i++] ); 3369#else 3370 data.zDbFilename = argv[i++]; 3371#endif 3372 }else{ 3373#ifndef SQLITE_OMIT_MEMORYDB 3374 data.zDbFilename = ":memory:"; 3375#else 3376 data.zDbFilename = 0; 3377#endif 3378 } 3379 if( i<argc ){ 3380 zFirstCmd = argv[i++]; 3381 } 3382 if( i<argc ){ 3383 fprintf(stderr,"%s: Error: too many options: \"%s\"\n", Argv0, argv[i]); 3384 fprintf(stderr,"Use -help for a list of options.\n"); 3385 return 1; 3386 } 3387 data.out = stdout; 3388 3389#ifdef SQLITE_OMIT_MEMORYDB 3390 if( data.zDbFilename==0 ){ 3391 fprintf(stderr,"%s: Error: no database filename specified\n", Argv0); 3392 return 1; 3393 } 3394#endif 3395 3396 /* Go ahead and open the database file if it already exists. If the 3397 ** file does not exist, delay opening it. This prevents empty database 3398 ** files from being created if a user mistypes the database name argument 3399 ** to the sqlite command-line tool. 3400 */ 3401 if( access(data.zDbFilename, 0)==0 ){ 3402 open_db(&data); 3403 } 3404 3405 /* Process the initialization file if there is one. If no -init option 3406 ** is given on the command line, look for a file named ~/.sqliterc and 3407 ** try to process it. 3408 */ 3409 rc = process_sqliterc(&data,zInitFile); 3410 if( rc>0 ){ 3411 return rc; 3412 } 3413 3414 /* Make a second pass through the command-line argument and set 3415 ** options. This second pass is delayed until after the initialization 3416 ** file is processed so that the command-line arguments will override 3417 ** settings in the initialization file. 3418 */ 3419 for(i=1; i<argc && argv[i][0]=='-'; i++){ 3420 char *z = argv[i]; 3421 if( z[1]=='-' ){ z++; } 3422 if( strcmp(z,"-init")==0 ){ 3423 i++; 3424 }else if( strcmp(z,"-html")==0 ){ 3425 data.mode = MODE_Html; 3426 }else if( strcmp(z,"-list")==0 ){ 3427 data.mode = MODE_List; 3428 }else if( strcmp(z,"-line")==0 ){ 3429 data.mode = MODE_Line; 3430 }else if( strcmp(z,"-column")==0 ){ 3431 data.mode = MODE_Column; 3432 }else if( strcmp(z,"-csv")==0 ){ 3433 data.mode = MODE_Csv; 3434 memcpy(data.separator,",",2); 3435 }else if( strcmp(z,"-separator")==0 ){ 3436 i++; 3437 if(i>=argc){ 3438 fprintf(stderr,"%s: Error: missing argument for option: %s\n", Argv0, z); 3439 fprintf(stderr,"Use -help for a list of options.\n"); 3440 return 1; 3441 } 3442 sqlite3_snprintf(sizeof(data.separator), data.separator, 3443 "%.*s",(int)sizeof(data.separator)-1,argv[i]); 3444 }else if( strcmp(z,"-nullvalue")==0 ){ 3445 i++; 3446 if(i>=argc){ 3447 fprintf(stderr,"%s: Error: missing argument for option: %s\n", Argv0, z); 3448 fprintf(stderr,"Use -help for a list of options.\n"); 3449 return 1; 3450 } 3451 sqlite3_snprintf(sizeof(data.nullvalue), data.nullvalue, 3452 "%.*s",(int)sizeof(data.nullvalue)-1,argv[i]); 3453 }else if( strcmp(z,"-header")==0 ){ 3454 data.showHeader = 1; 3455 }else if( strcmp(z,"-noheader")==0 ){ 3456 data.showHeader = 0; 3457 }else if( strcmp(z,"-echo")==0 ){ 3458 data.echoOn = 1; 3459 }else if( strcmp(z,"-bail")==0 ){ 3460 bail_on_error = 1; 3461 }else if( strcmp(z,"-version")==0 ){ 3462 printf("%s\n", sqlite3_libversion()); 3463 return 0; 3464 }else if( strcmp(z,"-interactive")==0 ){ 3465 stdin_is_interactive = 1; 3466 }else if( strcmp(z,"-batch")==0 ){ 3467 stdin_is_interactive = 0; 3468 }else if( strcmp(z,"-help")==0 || strcmp(z, "--help")==0 ){ 3469 usage(1); 3470 }else{ 3471 fprintf(stderr,"%s: Error: unknown option: %s\n", Argv0, z); 3472 fprintf(stderr,"Use -help for a list of options.\n"); 3473 return 1; 3474 } 3475 } 3476 3477 if( zFirstCmd ){ 3478 /* Run just the command that follows the database name 3479 */ 3480 if( zFirstCmd[0]=='.' ){ 3481 rc = do_meta_command(zFirstCmd, &data); 3482 return rc; 3483 }else{ 3484 open_db(&data); 3485 rc = shell_exec(data.db, zFirstCmd, shell_callback, &data, &zErrMsg); 3486 if( zErrMsg!=0 ){ 3487 fprintf(stderr,"Error: %s\n", zErrMsg); 3488 return rc!=0 ? rc : 1; 3489 }else if( rc!=0 ){ 3490 fprintf(stderr,"Error: unable to process SQL \"%s\"\n", zFirstCmd); 3491 return rc; 3492 } 3493 } 3494 }else{ 3495 /* Run commands received from standard input 3496 */ 3497 if( stdin_is_interactive ){ 3498 char *zHome; 3499 char *zHistory = 0; 3500 int nHistory; 3501 printf( 3502 "SQLite version %s\n" 3503 "Enter \".help\" for instructions\n" 3504 "Enter SQL statements terminated with a \";\"\n", 3505 sqlite3_libversion() 3506 ); 3507 zHome = find_home_dir(); 3508 if( zHome ){ 3509 nHistory = strlen30(zHome) + 20; 3510 if( (zHistory = malloc(nHistory))!=0 ){ 3511 sqlite3_snprintf(nHistory, zHistory,"%s/.sqlite_history", zHome); 3512 } 3513 } 3514#if defined(HAVE_READLINE) && HAVE_READLINE==1 3515 if( zHistory ) read_history(zHistory); 3516#endif 3517 rc = process_input(&data, 0); 3518 if( zHistory ){ 3519 stifle_history(100); 3520 write_history(zHistory); 3521 free(zHistory); 3522 } 3523 free(zHome); 3524 }else{ 3525 rc = process_input(&data, stdin); 3526 } 3527 } 3528 set_table_name(&data, 0); 3529 if( db ){ 3530 if( sqlite3_close(db)!=SQLITE_OK ){ 3531 fprintf(stderr,"Error: cannot close database \"%s\"\n", sqlite3_errmsg(db)); 3532 rc++; 3533 } 3534 } 3535 return rc; 3536} 3537