• Home
  • Line#
  • Scopes#
  • Navigate#
  • Raw
  • Download
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 */
59 extern 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 */
75 static struct rusage sBegin;
76 
77 /* True if the timer is enabled */
78 static int enableTimer = 0;
79 
80 /*
81 ** Begin timing an operation
82 */
beginTimer(void)83 static void beginTimer(void){
84   if( enableTimer ){
85     getrusage(RUSAGE_SELF, &sBegin);
86   }
87 }
88 
89 /* Return the difference of two time_structs in seconds */
timeDiff(struct timeval * pStart,struct timeval * pEnd)90 static 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 */
endTimer(void)98 static 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 */
117 static HANDLE hProcess;
118 static FILETIME ftKernelBegin;
119 static FILETIME ftUserBegin;
120 typedef BOOL (WINAPI *GETPROCTIMES)(HANDLE, LPFILETIME, LPFILETIME, LPFILETIME, LPFILETIME);
121 static GETPROCTIMES getProcessTimesAddr = NULL;
122 
123 /* True if the timer is enabled */
124 static 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 */
hasTimer(void)130 static 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 */
beginTimer(void)156 static 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 */
timeDiff(FILETIME * pStart,FILETIME * pEnd)164 static 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 */
endTimer(void)173 static 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
208 static int genfkey_create_triggers(sqlite3 *, const char *, void *,
209   int (*)(void *, int, const char *)
210 );
211 
212 struct GenfkeyCb {
213   void *pCtx;
214   int eType;
215   int (*xData)(void *, int, const char *);
216 };
217 typedef 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 
274 typedef struct SchemaTable SchemaTable;
275 static 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 
289 typedef struct schema_vtab schema_vtab;
290 typedef struct schema_cursor schema_cursor;
291 
292 /* A schema table object */
293 struct schema_vtab {
294   sqlite3_vtab base;
295   sqlite3 *db;
296   SchemaTable *pType;
297 };
298 
299 /* A schema table cursor object */
300 struct 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 */
schemaDestroy(sqlite3_vtab * pVtab)311 static int schemaDestroy(sqlite3_vtab *pVtab){
312   sqlite3_free(pVtab);
313   return 0;
314 }
315 
316 /*
317 ** Table constructor for the schema module.
318 */
schemaCreate(sqlite3 * db,void * pAux,int argc,const char * const * argv,sqlite3_vtab ** ppVtab,char ** pzErr)319 static 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 */
schemaOpen(sqlite3_vtab * pVTab,sqlite3_vtab_cursor ** ppCursor)358 static 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 */
schemaClose(sqlite3_vtab_cursor * cur)374 static 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 
columnToResult(sqlite3_context * ctx,sqlite3_stmt * pStmt,int iCol)383 static 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 */
schemaColumn(sqlite3_vtab_cursor * cur,sqlite3_context * ctx,int i)405 static 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 */
schemaRowid(sqlite3_vtab_cursor * cur,sqlite_int64 * pRowid)424 static 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 
finalize(sqlite3_stmt ** ppStmt)430 static int finalize(sqlite3_stmt **ppStmt){
431   int rc = sqlite3_finalize(*ppStmt);
432   *ppStmt = 0;
433   return rc;
434 }
435 
schemaEof(sqlite3_vtab_cursor * cur)436 static 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 */
schemaNext(sqlite3_vtab_cursor * cur)444 static 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 
508 next_exit:
509   /* TODO: Handle rc */
510   return rc;
511 }
512 
513 /*
514 ** Reset a schema table cursor.
515 */
schemaFilter(sqlite3_vtab_cursor * pVtabCursor,int idxNum,const char * idxStr,int argc,sqlite3_value ** argv)516 static 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 */
schemaBestIndex(sqlite3_vtab * tab,sqlite3_index_info * pIdxInfo)539 static 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 */
549 static 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 */
installSchemaModule(sqlite3 * db,sqlite3 * sdb)575 static 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 */
603 struct StrBuffer {
604   char *zBuf;
605 };
606 typedef struct StrBuffer StrBuffer;
joinFinalize(sqlite3_context * context)607 static 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 }
joinStep(sqlite3_context * context,int argc,sqlite3_value ** argv)613 static 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 */
doublequote(sqlite3_context * context,int argc,sqlite3_value ** argv)642 static 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 */
multireplace(sqlite3_context * context,int argc,sqlite3_value ** argv)673 static 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 */
invokeCallback(void * p,int nArg,char ** azArg,char ** azCol)725 static 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 
detectSchemaProblem(sqlite3 * db,const char * zMessage,const char * zSql,GenfkeyCb * pCb)732 static 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 */
populateTempTable(sqlite3 * db,GenfkeyCb * pCallback)766 static 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
genfkey_create_triggers(sqlite3 * sdb,const char * zDb,void * pCtx,int (* xData)(void *,int,const char *))877 static 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 
1049 genfkey_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 */
1064 static 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 */
1070 static 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 */
1077 static sqlite3 *db = 0;
1078 
1079 /*
1080 ** True if an interrupt (Control-C) has been received.
1081 */
1082 static 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 */
1088 static char *Argv0;
1089 
1090 /*
1091 ** Prompt strings. Initialized in main. Settable with
1092 **   .prompt main continue
1093 */
1094 static char mainPrompt[20];     /* First line prompt. default: "sqlite> "*/
1095 static char continuePrompt[20]; /* Continuation prompt. default: "   ...> " */
1096 
1097 /*
1098 ** Write I/O traces to the following stream.
1099 */
1100 #ifdef SQLITE_ENABLE_IOTRACE
1101 static 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
iotracePrintf(const char * zFormat,...)1111 static 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 */
isNumber(const char * z,int * realnum)1127 static 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 */
1159 static const char *zShellStatic = 0;
shellstaticFunc(sqlite3_context * context,int argc,sqlite3_value ** argv)1160 static 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 */
local_getline(char * zPrompt,FILE * in)1182 static 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 */
one_input_line(const char * zPrior,FILE * in)1230 static 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 
1248 struct 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 */
1260 struct 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   FILE *pLog;            /* Write log output here */
1281 };
1282 
1283 /*
1284 ** These are the allowed modes.
1285 */
1286 #define MODE_Line     0  /* One column per line.  Blank line between records */
1287 #define MODE_Column   1  /* One record per line in neat columns */
1288 #define MODE_List     2  /* One record per line with a separator */
1289 #define MODE_Semi     3  /* Same as MODE_List but append ";" to each line */
1290 #define MODE_Html     4  /* Generate an XHTML table */
1291 #define MODE_Insert   5  /* Generate SQL "insert" statements */
1292 #define MODE_Tcl      6  /* Generate ANSI-C or TCL quoted elements */
1293 #define MODE_Csv      7  /* Quote strings, numbers are plain */
1294 #define MODE_Explain  8  /* Like MODE_Column, but do not truncate data */
1295 
1296 static const char *modeDescr[] = {
1297   "line",
1298   "column",
1299   "list",
1300   "semi",
1301   "html",
1302   "insert",
1303   "tcl",
1304   "csv",
1305   "explain",
1306 };
1307 
1308 /*
1309 ** Number of elements in an array
1310 */
1311 #define ArraySize(X)  (int)(sizeof(X)/sizeof(X[0]))
1312 
1313 /*
1314 ** Compute a string length that is limited to what can be stored in
1315 ** lower 30 bits of a 32-bit signed integer.
1316 */
strlen30(const char * z)1317 static int strlen30(const char *z){
1318   const char *z2 = z;
1319   while( *z2 ){ z2++; }
1320   return 0x3fffffff & (int)(z2 - z);
1321 }
1322 
1323 /*
1324 ** A callback for the sqlite3_log() interface.
1325 */
shellLog(void * pArg,int iErrCode,const char * zMsg)1326 static void shellLog(void *pArg, int iErrCode, const char *zMsg){
1327   struct callback_data *p = (struct callback_data*)pArg;
1328   if( p->pLog==0 ) return;
1329   fprintf(p->pLog, "(%d) %s\n", iErrCode, zMsg);
1330   fflush(p->pLog);
1331 }
1332 
1333 /*
1334 ** Output the given string as a hex-encoded blob (eg. X'1234' )
1335 */
output_hex_blob(FILE * out,const void * pBlob,int nBlob)1336 static void output_hex_blob(FILE *out, const void *pBlob, int nBlob){
1337   int i;
1338   char *zBlob = (char *)pBlob;
1339   fprintf(out,"X'");
1340   for(i=0; i<nBlob; i++){ fprintf(out,"%02x",zBlob[i]); }
1341   fprintf(out,"'");
1342 }
1343 
1344 /*
1345 ** Output the given string as a quoted string using SQL quoting conventions.
1346 */
output_quoted_string(FILE * out,const char * z)1347 static void output_quoted_string(FILE *out, const char *z){
1348   int i;
1349   int nSingle = 0;
1350   for(i=0; z[i]; i++){
1351     if( z[i]=='\'' ) nSingle++;
1352   }
1353   if( nSingle==0 ){
1354     fprintf(out,"'%s'",z);
1355   }else{
1356     fprintf(out,"'");
1357     while( *z ){
1358       for(i=0; z[i] && z[i]!='\''; i++){}
1359       if( i==0 ){
1360         fprintf(out,"''");
1361         z++;
1362       }else if( z[i]=='\'' ){
1363         fprintf(out,"%.*s''",i,z);
1364         z += i+1;
1365       }else{
1366         fprintf(out,"%s",z);
1367         break;
1368       }
1369     }
1370     fprintf(out,"'");
1371   }
1372 }
1373 
1374 /*
1375 ** Output the given string as a quoted according to C or TCL quoting rules.
1376 */
output_c_string(FILE * out,const char * z)1377 static void output_c_string(FILE *out, const char *z){
1378   unsigned int c;
1379   fputc('"', out);
1380   while( (c = *(z++))!=0 ){
1381     if( c=='\\' ){
1382       fputc(c, out);
1383       fputc(c, out);
1384     }else if( c=='\t' ){
1385       fputc('\\', out);
1386       fputc('t', out);
1387     }else if( c=='\n' ){
1388       fputc('\\', out);
1389       fputc('n', out);
1390     }else if( c=='\r' ){
1391       fputc('\\', out);
1392       fputc('r', out);
1393     }else if( !isprint(c) ){
1394       fprintf(out, "\\%03o", c&0xff);
1395     }else{
1396       fputc(c, out);
1397     }
1398   }
1399   fputc('"', out);
1400 }
1401 
1402 /*
1403 ** Output the given string with characters that are special to
1404 ** HTML escaped.
1405 */
output_html_string(FILE * out,const char * z)1406 static void output_html_string(FILE *out, const char *z){
1407   int i;
1408   while( *z ){
1409     for(i=0;   z[i]
1410             && z[i]!='<'
1411             && z[i]!='&'
1412             && z[i]!='>'
1413             && z[i]!='\"'
1414             && z[i]!='\'';
1415         i++){}
1416     if( i>0 ){
1417       fprintf(out,"%.*s",i,z);
1418     }
1419     if( z[i]=='<' ){
1420       fprintf(out,"&lt;");
1421     }else if( z[i]=='&' ){
1422       fprintf(out,"&amp;");
1423     }else if( z[i]=='>' ){
1424       fprintf(out,"&gt;");
1425     }else if( z[i]=='\"' ){
1426       fprintf(out,"&quot;");
1427     }else if( z[i]=='\'' ){
1428       fprintf(out,"&#39;");
1429     }else{
1430       break;
1431     }
1432     z += i + 1;
1433   }
1434 }
1435 
1436 /*
1437 ** If a field contains any character identified by a 1 in the following
1438 ** array, then the string must be quoted for CSV.
1439 */
1440 static const char needCsvQuote[] = {
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, 0, 1, 0, 0, 0, 0, 1,   0, 0, 0, 0, 0, 0, 0, 0,
1444   0, 0, 0, 0, 0, 0, 0, 0,   0, 0, 0, 0, 0, 0, 0, 0,
1445   0, 0, 0, 0, 0, 0, 0, 0,   0, 0, 0, 0, 0, 0, 0, 0,
1446   0, 0, 0, 0, 0, 0, 0, 0,   0, 0, 0, 0, 0, 0, 0, 0,
1447   0, 0, 0, 0, 0, 0, 0, 0,   0, 0, 0, 0, 0, 0, 0, 0,
1448   0, 0, 0, 0, 0, 0, 0, 0,   0, 0, 0, 0, 0, 0, 0, 1,
1449   1, 1, 1, 1, 1, 1, 1, 1,   1, 1, 1, 1, 1, 1, 1, 1,
1450   1, 1, 1, 1, 1, 1, 1, 1,   1, 1, 1, 1, 1, 1, 1, 1,
1451   1, 1, 1, 1, 1, 1, 1, 1,   1, 1, 1, 1, 1, 1, 1, 1,
1452   1, 1, 1, 1, 1, 1, 1, 1,   1, 1, 1, 1, 1, 1, 1, 1,
1453   1, 1, 1, 1, 1, 1, 1, 1,   1, 1, 1, 1, 1, 1, 1, 1,
1454   1, 1, 1, 1, 1, 1, 1, 1,   1, 1, 1, 1, 1, 1, 1, 1,
1455   1, 1, 1, 1, 1, 1, 1, 1,   1, 1, 1, 1, 1, 1, 1, 1,
1456   1, 1, 1, 1, 1, 1, 1, 1,   1, 1, 1, 1, 1, 1, 1, 1,
1457 };
1458 
1459 /*
1460 ** Output a single term of CSV.  Actually, p->separator is used for
1461 ** the separator, which may or may not be a comma.  p->nullvalue is
1462 ** the null value.  Strings are quoted using ANSI-C rules.  Numbers
1463 ** appear outside of quotes.
1464 */
output_csv(struct callback_data * p,const char * z,int bSep)1465 static void output_csv(struct callback_data *p, const char *z, int bSep){
1466   FILE *out = p->out;
1467   if( z==0 ){
1468     fprintf(out,"%s",p->nullvalue);
1469   }else{
1470     int i;
1471     int nSep = strlen30(p->separator);
1472     for(i=0; z[i]; i++){
1473       if( needCsvQuote[((unsigned char*)z)[i]]
1474          || (z[i]==p->separator[0] &&
1475              (nSep==1 || memcmp(z, p->separator, nSep)==0)) ){
1476         i = 0;
1477         break;
1478       }
1479     }
1480     if( i==0 ){
1481       putc('"', out);
1482       for(i=0; z[i]; i++){
1483         if( z[i]=='"' ) putc('"', out);
1484         putc(z[i], out);
1485       }
1486       putc('"', out);
1487     }else{
1488       fprintf(out, "%s", z);
1489     }
1490   }
1491   if( bSep ){
1492     fprintf(p->out, "%s", p->separator);
1493   }
1494 }
1495 
1496 #ifdef SIGINT
1497 /*
1498 ** This routine runs when the user presses Ctrl-C
1499 */
interrupt_handler(int NotUsed)1500 static void interrupt_handler(int NotUsed){
1501   UNUSED_PARAMETER(NotUsed);
1502   seenInterrupt = 1;
1503   if( db ) sqlite3_interrupt(db);
1504 }
1505 #endif
1506 
1507 /*
1508 ** This is the callback routine that the shell
1509 ** invokes for each row of a query result.
1510 */
shell_callback(void * pArg,int nArg,char ** azArg,char ** azCol,int * aiType)1511 static int shell_callback(void *pArg, int nArg, char **azArg, char **azCol, int *aiType){
1512   int i;
1513   struct callback_data *p = (struct callback_data*)pArg;
1514 
1515   if( p->echoOn && p->cnt==0  && p->pStmt){
1516     printf("%s\n", sqlite3_sql(p->pStmt));
1517   }
1518 
1519   switch( p->mode ){
1520     case MODE_Line: {
1521       int w = 5;
1522       if( azArg==0 ) break;
1523       for(i=0; i<nArg; i++){
1524         int len = strlen30(azCol[i] ? azCol[i] : "");
1525         if( len>w ) w = len;
1526       }
1527       if( p->cnt++>0 ) fprintf(p->out,"\n");
1528       for(i=0; i<nArg; i++){
1529         fprintf(p->out,"%*s = %s\n", w, azCol[i],
1530                 azArg[i] ? azArg[i] : p->nullvalue);
1531       }
1532       break;
1533     }
1534     case MODE_Explain:
1535     case MODE_Column: {
1536       if( p->cnt++==0 ){
1537         for(i=0; i<nArg; i++){
1538           int w, n;
1539           if( i<ArraySize(p->colWidth) ){
1540             w = p->colWidth[i];
1541           }else{
1542             w = 0;
1543           }
1544           if( w<=0 ){
1545             w = strlen30(azCol[i] ? azCol[i] : "");
1546             if( w<10 ) w = 10;
1547             n = strlen30(azArg && azArg[i] ? azArg[i] : p->nullvalue);
1548             if( w<n ) w = n;
1549           }
1550           if( i<ArraySize(p->actualWidth) ){
1551             p->actualWidth[i] = w;
1552           }
1553           if( p->showHeader ){
1554             fprintf(p->out,"%-*.*s%s",w,w,azCol[i], i==nArg-1 ? "\n": "  ");
1555           }
1556         }
1557         if( p->showHeader ){
1558           for(i=0; i<nArg; i++){
1559             int w;
1560             if( i<ArraySize(p->actualWidth) ){
1561                w = p->actualWidth[i];
1562             }else{
1563                w = 10;
1564             }
1565             fprintf(p->out,"%-*.*s%s",w,w,"-----------------------------------"
1566                    "----------------------------------------------------------",
1567                     i==nArg-1 ? "\n": "  ");
1568           }
1569         }
1570       }
1571       if( azArg==0 ) break;
1572       for(i=0; i<nArg; i++){
1573         int w;
1574         if( i<ArraySize(p->actualWidth) ){
1575            w = p->actualWidth[i];
1576         }else{
1577            w = 10;
1578         }
1579         if( p->mode==MODE_Explain && azArg[i] &&
1580            strlen30(azArg[i])>w ){
1581           w = strlen30(azArg[i]);
1582         }
1583         fprintf(p->out,"%-*.*s%s",w,w,
1584             azArg[i] ? azArg[i] : p->nullvalue, i==nArg-1 ? "\n": "  ");
1585       }
1586       break;
1587     }
1588     case MODE_Semi:
1589     case MODE_List: {
1590       if( p->cnt++==0 && p->showHeader ){
1591         for(i=0; i<nArg; i++){
1592           fprintf(p->out,"%s%s",azCol[i], i==nArg-1 ? "\n" : p->separator);
1593         }
1594       }
1595       if( azArg==0 ) break;
1596       for(i=0; i<nArg; i++){
1597         char *z = azArg[i];
1598         if( z==0 ) z = p->nullvalue;
1599         fprintf(p->out, "%s", z);
1600         if( i<nArg-1 ){
1601           fprintf(p->out, "%s", p->separator);
1602         }else if( p->mode==MODE_Semi ){
1603           fprintf(p->out, ";\n");
1604         }else{
1605           fprintf(p->out, "\n");
1606         }
1607       }
1608       break;
1609     }
1610     case MODE_Html: {
1611       if( p->cnt++==0 && p->showHeader ){
1612         fprintf(p->out,"<TR>");
1613         for(i=0; i<nArg; i++){
1614           fprintf(p->out,"<TH>");
1615           output_html_string(p->out, azCol[i]);
1616           fprintf(p->out,"</TH>\n");
1617         }
1618         fprintf(p->out,"</TR>\n");
1619       }
1620       if( azArg==0 ) break;
1621       fprintf(p->out,"<TR>");
1622       for(i=0; i<nArg; i++){
1623         fprintf(p->out,"<TD>");
1624         output_html_string(p->out, azArg[i] ? azArg[i] : p->nullvalue);
1625         fprintf(p->out,"</TD>\n");
1626       }
1627       fprintf(p->out,"</TR>\n");
1628       break;
1629     }
1630     case MODE_Tcl: {
1631       if( p->cnt++==0 && p->showHeader ){
1632         for(i=0; i<nArg; i++){
1633           output_c_string(p->out,azCol[i] ? azCol[i] : "");
1634           fprintf(p->out, "%s", p->separator);
1635         }
1636         fprintf(p->out,"\n");
1637       }
1638       if( azArg==0 ) break;
1639       for(i=0; i<nArg; i++){
1640         output_c_string(p->out, azArg[i] ? azArg[i] : p->nullvalue);
1641         fprintf(p->out, "%s", p->separator);
1642       }
1643       fprintf(p->out,"\n");
1644       break;
1645     }
1646     case MODE_Csv: {
1647       if( p->cnt++==0 && p->showHeader ){
1648         for(i=0; i<nArg; i++){
1649           output_csv(p, azCol[i] ? azCol[i] : "", i<nArg-1);
1650         }
1651         fprintf(p->out,"\n");
1652       }
1653       if( azArg==0 ) break;
1654       for(i=0; i<nArg; i++){
1655         output_csv(p, azArg[i], i<nArg-1);
1656       }
1657       fprintf(p->out,"\n");
1658       break;
1659     }
1660     case MODE_Insert: {
1661       p->cnt++;
1662       if( azArg==0 ) break;
1663       fprintf(p->out,"INSERT INTO %s VALUES(",p->zDestTable);
1664       for(i=0; i<nArg; i++){
1665         char *zSep = i>0 ? ",": "";
1666         if( (azArg[i]==0) || (aiType && aiType[i]==SQLITE_NULL) ){
1667           fprintf(p->out,"%sNULL",zSep);
1668         }else if( aiType && aiType[i]==SQLITE_TEXT ){
1669           if( zSep[0] ) fprintf(p->out,"%s",zSep);
1670           output_quoted_string(p->out, azArg[i]);
1671         }else if( aiType && (aiType[i]==SQLITE_INTEGER || aiType[i]==SQLITE_FLOAT) ){
1672           fprintf(p->out,"%s%s",zSep, azArg[i]);
1673         }else if( aiType && aiType[i]==SQLITE_BLOB && p->pStmt ){
1674           const void *pBlob = sqlite3_column_blob(p->pStmt, i);
1675           int nBlob = sqlite3_column_bytes(p->pStmt, i);
1676           if( zSep[0] ) fprintf(p->out,"%s",zSep);
1677           output_hex_blob(p->out, pBlob, nBlob);
1678         }else if( isNumber(azArg[i], 0) ){
1679           fprintf(p->out,"%s%s",zSep, azArg[i]);
1680         }else{
1681           if( zSep[0] ) fprintf(p->out,"%s",zSep);
1682           output_quoted_string(p->out, azArg[i]);
1683         }
1684       }
1685       fprintf(p->out,");\n");
1686       break;
1687     }
1688   }
1689   return 0;
1690 }
1691 
1692 /*
1693 ** This is the callback routine that the SQLite library
1694 ** invokes for each row of a query result.
1695 */
callback(void * pArg,int nArg,char ** azArg,char ** azCol)1696 static int callback(void *pArg, int nArg, char **azArg, char **azCol){
1697   /* since we don't have type info, call the shell_callback with a NULL value */
1698   return shell_callback(pArg, nArg, azArg, azCol, NULL);
1699 }
1700 
1701 /*
1702 ** Set the destination table field of the callback_data structure to
1703 ** the name of the table given.  Escape any quote characters in the
1704 ** table name.
1705 */
set_table_name(struct callback_data * p,const char * zName)1706 static void set_table_name(struct callback_data *p, const char *zName){
1707   int i, n;
1708   int needQuote;
1709   char *z;
1710 
1711   if( p->zDestTable ){
1712     free(p->zDestTable);
1713     p->zDestTable = 0;
1714   }
1715   if( zName==0 ) return;
1716   needQuote = !isalpha((unsigned char)*zName) && *zName!='_';
1717   for(i=n=0; zName[i]; i++, n++){
1718     if( !isalnum((unsigned char)zName[i]) && zName[i]!='_' ){
1719       needQuote = 1;
1720       if( zName[i]=='\'' ) n++;
1721     }
1722   }
1723   if( needQuote ) n += 2;
1724   z = p->zDestTable = malloc( n+1 );
1725   if( z==0 ){
1726     fprintf(stderr,"Error: out of memory\n");
1727     exit(1);
1728   }
1729   n = 0;
1730   if( needQuote ) z[n++] = '\'';
1731   for(i=0; zName[i]; i++){
1732     z[n++] = zName[i];
1733     if( zName[i]=='\'' ) z[n++] = '\'';
1734   }
1735   if( needQuote ) z[n++] = '\'';
1736   z[n] = 0;
1737 }
1738 
1739 /* zIn is either a pointer to a NULL-terminated string in memory obtained
1740 ** from malloc(), or a NULL pointer. The string pointed to by zAppend is
1741 ** added to zIn, and the result returned in memory obtained from malloc().
1742 ** zIn, if it was not NULL, is freed.
1743 **
1744 ** If the third argument, quote, is not '\0', then it is used as a
1745 ** quote character for zAppend.
1746 */
appendText(char * zIn,char const * zAppend,char quote)1747 static char *appendText(char *zIn, char const *zAppend, char quote){
1748   int len;
1749   int i;
1750   int nAppend = strlen30(zAppend);
1751   int nIn = (zIn?strlen30(zIn):0);
1752 
1753   len = nAppend+nIn+1;
1754   if( quote ){
1755     len += 2;
1756     for(i=0; i<nAppend; i++){
1757       if( zAppend[i]==quote ) len++;
1758     }
1759   }
1760 
1761   zIn = (char *)realloc(zIn, len);
1762   if( !zIn ){
1763     return 0;
1764   }
1765 
1766   if( quote ){
1767     char *zCsr = &zIn[nIn];
1768     *zCsr++ = quote;
1769     for(i=0; i<nAppend; i++){
1770       *zCsr++ = zAppend[i];
1771       if( zAppend[i]==quote ) *zCsr++ = quote;
1772     }
1773     *zCsr++ = quote;
1774     *zCsr++ = '\0';
1775     assert( (zCsr-zIn)==len );
1776   }else{
1777     memcpy(&zIn[nIn], zAppend, nAppend);
1778     zIn[len-1] = '\0';
1779   }
1780 
1781   return zIn;
1782 }
1783 
1784 
1785 /*
1786 ** Execute a query statement that has a single result column.  Print
1787 ** that result column on a line by itself with a semicolon terminator.
1788 **
1789 ** This is used, for example, to show the schema of the database by
1790 ** querying the SQLITE_MASTER table.
1791 */
run_table_dump_query(FILE * out,sqlite3 * db,const char * zSelect,const char * zFirstRow)1792 static int run_table_dump_query(
1793   FILE *out,              /* Send output here */
1794   sqlite3 *db,            /* Database to query */
1795   const char *zSelect,    /* SELECT statement to extract content */
1796   const char *zFirstRow   /* Print before first row, if not NULL */
1797 ){
1798   sqlite3_stmt *pSelect;
1799   int rc;
1800   rc = sqlite3_prepare(db, zSelect, -1, &pSelect, 0);
1801   if( rc!=SQLITE_OK || !pSelect ){
1802     return rc;
1803   }
1804   rc = sqlite3_step(pSelect);
1805   while( rc==SQLITE_ROW ){
1806     if( zFirstRow ){
1807       fprintf(out, "%s", zFirstRow);
1808       zFirstRow = 0;
1809     }
1810     fprintf(out, "%s;\n", sqlite3_column_text(pSelect, 0));
1811     rc = sqlite3_step(pSelect);
1812   }
1813   return sqlite3_finalize(pSelect);
1814 }
1815 
1816 /*
1817 ** Allocate space and save off current error string.
1818 */
save_err_msg(sqlite3 * db)1819 static char *save_err_msg(
1820   sqlite3 *db            /* Database to query */
1821 ){
1822   int nErrMsg = 1+strlen30(sqlite3_errmsg(db));
1823   char *zErrMsg = sqlite3_malloc(nErrMsg);
1824   if( zErrMsg ){
1825     memcpy(zErrMsg, sqlite3_errmsg(db), nErrMsg);
1826   }
1827   return zErrMsg;
1828 }
1829 
1830 /*
1831 ** Execute a statement or set of statements.  Print
1832 ** any result rows/columns depending on the current mode
1833 ** set via the supplied callback.
1834 **
1835 ** This is very similar to SQLite's built-in sqlite3_exec()
1836 ** function except it takes a slightly different callback
1837 ** and callback data argument.
1838 */
shell_exec(sqlite3 * db,const char * zSql,int (* xCallback)(void *,int,char **,char **,int *),struct callback_data * pArg,char ** pzErrMsg)1839 static int shell_exec(
1840   sqlite3 *db,                                /* An open database */
1841   const char *zSql,                           /* SQL to be evaluated */
1842   int (*xCallback)(void*,int,char**,char**,int*),   /* Callback function */
1843                                               /* (not the same as sqlite3_exec) */
1844   struct callback_data *pArg,                 /* Pointer to struct callback_data */
1845   char **pzErrMsg                             /* Error msg written here */
1846 ){
1847   sqlite3_stmt *pStmt = NULL;     /* Statement to execute. */
1848   int rc = SQLITE_OK;             /* Return Code */
1849   const char *zLeftover;          /* Tail of unprocessed SQL */
1850 
1851   if( pzErrMsg ){
1852     *pzErrMsg = NULL;
1853   }
1854 
1855   while( zSql[0] && (SQLITE_OK == rc) ){
1856     rc = sqlite3_prepare_v2(db, zSql, -1, &pStmt, &zLeftover);
1857     if( SQLITE_OK != rc ){
1858       if( pzErrMsg ){
1859         *pzErrMsg = save_err_msg(db);
1860       }
1861     }else{
1862       if( !pStmt ){
1863         /* this happens for a comment or white-space */
1864         zSql = zLeftover;
1865         while( isspace(zSql[0]) ) zSql++;
1866         continue;
1867       }
1868 
1869       /* perform the first step.  this will tell us if we
1870       ** have a result set or not and how wide it is.
1871       */
1872       rc = sqlite3_step(pStmt);
1873       /* if we have a result set... */
1874       if( SQLITE_ROW == rc ){
1875         /* if we have a callback... */
1876         if( xCallback ){
1877           /* allocate space for col name ptr, value ptr, and type */
1878           int nCol = sqlite3_column_count(pStmt);
1879           void *pData = sqlite3_malloc(3*nCol*sizeof(const char*) + 1);
1880           if( !pData ){
1881             rc = SQLITE_NOMEM;
1882           }else{
1883             char **azCols = (char **)pData;      /* Names of result columns */
1884             char **azVals = &azCols[nCol];       /* Results */
1885             int *aiTypes = (int *)&azVals[nCol]; /* Result types */
1886             int i;
1887             assert(sizeof(int) <= sizeof(char *));
1888             /* save off ptrs to column names */
1889             for(i=0; i<nCol; i++){
1890               azCols[i] = (char *)sqlite3_column_name(pStmt, i);
1891             }
1892             /* save off the prepared statment handle and reset row count */
1893             if( pArg ){
1894               pArg->pStmt = pStmt;
1895               pArg->cnt = 0;
1896             }
1897             do{
1898               /* extract the data and data types */
1899               for(i=0; i<nCol; i++){
1900                 azVals[i] = (char *)sqlite3_column_text(pStmt, i);
1901                 aiTypes[i] = sqlite3_column_type(pStmt, i);
1902                 if( !azVals[i] && (aiTypes[i]!=SQLITE_NULL) ){
1903                   rc = SQLITE_NOMEM;
1904                   break; /* from for */
1905                 }
1906               } /* end for */
1907 
1908               /* if data and types extracted successfully... */
1909               if( SQLITE_ROW == rc ){
1910                 /* call the supplied callback with the result row data */
1911                 if( xCallback(pArg, nCol, azVals, azCols, aiTypes) ){
1912                   rc = SQLITE_ABORT;
1913                 }else{
1914                   rc = sqlite3_step(pStmt);
1915                 }
1916               }
1917             } while( SQLITE_ROW == rc );
1918             sqlite3_free(pData);
1919             if( pArg ){
1920               pArg->pStmt = NULL;
1921             }
1922           }
1923         }else{
1924           do{
1925             rc = sqlite3_step(pStmt);
1926           } while( rc == SQLITE_ROW );
1927         }
1928       }
1929 
1930       /* Finalize the statement just executed. If this fails, save a
1931       ** copy of the error message. Otherwise, set zSql to point to the
1932       ** next statement to execute. */
1933       rc = sqlite3_finalize(pStmt);
1934       if( rc==SQLITE_OK ){
1935         zSql = zLeftover;
1936         while( isspace(zSql[0]) ) zSql++;
1937       }else if( pzErrMsg ){
1938         *pzErrMsg = save_err_msg(db);
1939       }
1940     }
1941   } /* end while */
1942 
1943   return rc;
1944 }
1945 
1946 
1947 /*
1948 ** This is a different callback routine used for dumping the database.
1949 ** Each row received by this callback consists of a table name,
1950 ** the table type ("index" or "table") and SQL to create the table.
1951 ** This routine should print text sufficient to recreate the table.
1952 */
dump_callback(void * pArg,int nArg,char ** azArg,char ** azCol)1953 static int dump_callback(void *pArg, int nArg, char **azArg, char **azCol){
1954   int rc;
1955   const char *zTable;
1956   const char *zType;
1957   const char *zSql;
1958   const char *zPrepStmt = 0;
1959   struct callback_data *p = (struct callback_data *)pArg;
1960 
1961   UNUSED_PARAMETER(azCol);
1962   if( nArg!=3 ) return 1;
1963   zTable = azArg[0];
1964   zType = azArg[1];
1965   zSql = azArg[2];
1966 
1967   if( strcmp(zTable, "sqlite_sequence")==0 ){
1968     zPrepStmt = "DELETE FROM sqlite_sequence;\n";
1969   }else if( strcmp(zTable, "sqlite_stat1")==0 ){
1970     fprintf(p->out, "ANALYZE sqlite_master;\n");
1971   }else if( strncmp(zTable, "sqlite_", 7)==0 ){
1972     return 0;
1973   }else if( strncmp(zSql, "CREATE VIRTUAL TABLE", 20)==0 ){
1974     char *zIns;
1975     if( !p->writableSchema ){
1976       fprintf(p->out, "PRAGMA writable_schema=ON;\n");
1977       p->writableSchema = 1;
1978     }
1979     zIns = sqlite3_mprintf(
1980        "INSERT INTO sqlite_master(type,name,tbl_name,rootpage,sql)"
1981        "VALUES('table','%q','%q',0,'%q');",
1982        zTable, zTable, zSql);
1983     fprintf(p->out, "%s\n", zIns);
1984     sqlite3_free(zIns);
1985     return 0;
1986   }else{
1987     fprintf(p->out, "%s;\n", zSql);
1988   }
1989 
1990   if( strcmp(zType, "table")==0 ){
1991     sqlite3_stmt *pTableInfo = 0;
1992     char *zSelect = 0;
1993     char *zTableInfo = 0;
1994     char *zTmp = 0;
1995     int nRow = 0;
1996 
1997     zTableInfo = appendText(zTableInfo, "PRAGMA table_info(", 0);
1998     zTableInfo = appendText(zTableInfo, zTable, '"');
1999     zTableInfo = appendText(zTableInfo, ");", 0);
2000 
2001     rc = sqlite3_prepare(p->db, zTableInfo, -1, &pTableInfo, 0);
2002     free(zTableInfo);
2003     if( rc!=SQLITE_OK || !pTableInfo ){
2004       return 1;
2005     }
2006 
2007     zSelect = appendText(zSelect, "SELECT 'INSERT INTO ' || ", 0);
2008     zTmp = appendText(zTmp, zTable, '"');
2009     if( zTmp ){
2010       zSelect = appendText(zSelect, zTmp, '\'');
2011     }
2012     zSelect = appendText(zSelect, " || ' VALUES(' || ", 0);
2013     rc = sqlite3_step(pTableInfo);
2014     while( rc==SQLITE_ROW ){
2015       const char *zText = (const char *)sqlite3_column_text(pTableInfo, 1);
2016       zSelect = appendText(zSelect, "quote(", 0);
2017       zSelect = appendText(zSelect, zText, '"');
2018       rc = sqlite3_step(pTableInfo);
2019       if( rc==SQLITE_ROW ){
2020         zSelect = appendText(zSelect, ") || ',' || ", 0);
2021       }else{
2022         zSelect = appendText(zSelect, ") ", 0);
2023       }
2024       nRow++;
2025     }
2026     rc = sqlite3_finalize(pTableInfo);
2027     if( rc!=SQLITE_OK || nRow==0 ){
2028       free(zSelect);
2029       return 1;
2030     }
2031     zSelect = appendText(zSelect, "|| ')' FROM  ", 0);
2032     zSelect = appendText(zSelect, zTable, '"');
2033 
2034     rc = run_table_dump_query(p->out, p->db, zSelect, zPrepStmt);
2035     if( rc==SQLITE_CORRUPT ){
2036       zSelect = appendText(zSelect, " ORDER BY rowid DESC", 0);
2037       rc = run_table_dump_query(p->out, p->db, zSelect, 0);
2038     }
2039     if( zSelect ) free(zSelect);
2040   }
2041   return 0;
2042 }
2043 
2044 /*
2045 ** Run zQuery.  Use dump_callback() as the callback routine so that
2046 ** the contents of the query are output as SQL statements.
2047 **
2048 ** If we get a SQLITE_CORRUPT error, rerun the query after appending
2049 ** "ORDER BY rowid DESC" to the end.
2050 */
run_schema_dump_query(struct callback_data * p,const char * zQuery,char ** pzErrMsg)2051 static int run_schema_dump_query(
2052   struct callback_data *p,
2053   const char *zQuery,
2054   char **pzErrMsg
2055 ){
2056   int rc;
2057   rc = sqlite3_exec(p->db, zQuery, dump_callback, p, pzErrMsg);
2058   if( rc==SQLITE_CORRUPT ){
2059     char *zQ2;
2060     int len = strlen30(zQuery);
2061     if( pzErrMsg ) sqlite3_free(*pzErrMsg);
2062     zQ2 = malloc( len+100 );
2063     if( zQ2==0 ) return rc;
2064     sqlite3_snprintf(sizeof(zQ2), zQ2, "%s ORDER BY rowid DESC", zQuery);
2065     rc = sqlite3_exec(p->db, zQ2, dump_callback, p, pzErrMsg);
2066     free(zQ2);
2067   }
2068   return rc;
2069 }
2070 
2071 #if !defined(SQLITE_OMIT_VIRTUALTABLE) && !defined(SQLITE_OMIT_SUBQUERY)
2072 struct GenfkeyCmd {
2073   sqlite3 *db;                   /* Database handle */
2074   struct callback_data *pCb;     /* Callback data */
2075   int isIgnoreErrors;            /* True for --ignore-errors */
2076   int isExec;                    /* True for --exec */
2077   int isNoDrop;                  /* True for --no-drop */
2078   int nErr;                      /* Number of errors seen so far */
2079 };
2080 typedef struct GenfkeyCmd GenfkeyCmd;
2081 
genfkeyParseArgs(GenfkeyCmd * p,char ** azArg,int nArg)2082 static int genfkeyParseArgs(GenfkeyCmd *p, char **azArg, int nArg){
2083   int ii;
2084   memset(p, 0, sizeof(GenfkeyCmd));
2085 
2086   for(ii=0; ii<nArg; ii++){
2087     int n = strlen30(azArg[ii]);
2088 
2089     if( n>2 && n<10 && 0==strncmp(azArg[ii], "--no-drop", n) ){
2090       p->isNoDrop = 1;
2091     }else if( n>2 && n<16 && 0==strncmp(azArg[ii], "--ignore-errors", n) ){
2092       p->isIgnoreErrors = 1;
2093     }else if( n>2 && n<7 && 0==strncmp(azArg[ii], "--exec", n) ){
2094       p->isExec = 1;
2095     }else{
2096       fprintf(stderr, "unknown option: %s\n", azArg[ii]);
2097       return -1;
2098     }
2099   }
2100 
2101   return SQLITE_OK;
2102 }
2103 
genfkeyCmdCb(void * pCtx,int eType,const char * z)2104 static int genfkeyCmdCb(void *pCtx, int eType, const char *z){
2105   GenfkeyCmd *p = (GenfkeyCmd *)pCtx;
2106   if( eType==GENFKEY_ERROR && !p->isIgnoreErrors ){
2107     p->nErr++;
2108     fprintf(stderr, "%s\n", z);
2109   }
2110 
2111   if( p->nErr==0 && (
2112         (eType==GENFKEY_CREATETRIGGER)
2113      || (eType==GENFKEY_DROPTRIGGER && !p->isNoDrop)
2114   )){
2115     if( p->isExec ){
2116       sqlite3_exec(p->db, z, 0, 0, 0);
2117     }else{
2118       char *zCol = "sql";
2119       callback((void *)p->pCb, 1, (char **)&z, (char **)&zCol);
2120     }
2121   }
2122 
2123   return SQLITE_OK;
2124 }
2125 #endif
2126 
2127 /*
2128 ** Text of a help message
2129 */
2130 static char zHelp[] =
2131   ".backup ?DB? FILE      Backup DB (default \"main\") to FILE\n"
2132   ".bail ON|OFF           Stop after hitting an error.  Default OFF\n"
2133   ".databases             List names and files of attached databases\n"
2134   ".dump ?TABLE? ...      Dump the database in an SQL text format\n"
2135   "                         If TABLE specified, only dump tables matching\n"
2136   "                         LIKE pattern TABLE.\n"
2137   ".echo ON|OFF           Turn command echo on or off\n"
2138   ".exit                  Exit this program\n"
2139   ".explain ?ON|OFF?      Turn output mode suitable for EXPLAIN on or off.\n"
2140   "                         With no args, it turns EXPLAIN on.\n"
2141 #if !defined(SQLITE_OMIT_VIRTUALTABLE) && !defined(SQLITE_OMIT_SUBQUERY)
2142   ".genfkey ?OPTIONS?     Options are:\n"
2143   "                         --no-drop: Do not drop old fkey triggers.\n"
2144   "                         --ignore-errors: Ignore tables with fkey errors\n"
2145   "                         --exec: Execute generated SQL immediately\n"
2146   "                       See file tool/genfkey.README in the source \n"
2147   "                       distribution for further information.\n"
2148 #endif
2149   ".header(s) ON|OFF      Turn display of headers on or off\n"
2150   ".help                  Show this message\n"
2151   ".import FILE TABLE     Import data from FILE into TABLE\n"
2152   ".indices ?TABLE?       Show names of all indices\n"
2153   "                         If TABLE specified, only show indices for tables\n"
2154   "                         matching LIKE pattern TABLE.\n"
2155 #ifdef SQLITE_ENABLE_IOTRACE
2156   ".iotrace FILE          Enable I/O diagnostic logging to FILE\n"
2157 #endif
2158 #ifndef SQLITE_OMIT_LOAD_EXTENSION
2159   ".load FILE ?ENTRY?     Load an extension library\n"
2160 #endif
2161   ".log FILE|off          Turn logging on or off.  FILE can be stderr/stdout\n"
2162   ".mode MODE ?TABLE?     Set output mode where MODE is one of:\n"
2163   "                         csv      Comma-separated values\n"
2164   "                         column   Left-aligned columns.  (See .width)\n"
2165   "                         html     HTML <table> code\n"
2166   "                         insert   SQL insert statements for TABLE\n"
2167   "                         line     One value per line\n"
2168   "                         list     Values delimited by .separator string\n"
2169   "                         tabs     Tab-separated values\n"
2170   "                         tcl      TCL list elements\n"
2171   ".nullvalue STRING      Print STRING in place of NULL values\n"
2172   ".output FILENAME       Send output to FILENAME\n"
2173   ".output stdout         Send output to the screen\n"
2174   ".prompt MAIN CONTINUE  Replace the standard prompts\n"
2175   ".quit                  Exit this program\n"
2176   ".read FILENAME         Execute SQL in FILENAME\n"
2177   ".restore ?DB? FILE     Restore content of DB (default \"main\") from FILE\n"
2178   ".schema ?TABLE?        Show the CREATE statements\n"
2179   "                         If TABLE specified, only show tables matching\n"
2180   "                         LIKE pattern TABLE.\n"
2181   ".separator STRING      Change separator used by output mode and .import\n"
2182   ".show                  Show the current values for various settings\n"
2183   ".tables ?TABLE?        List names of tables\n"
2184   "                         If TABLE specified, only list tables matching\n"
2185   "                         LIKE pattern TABLE.\n"
2186   ".timeout MS            Try opening locked tables for MS milliseconds\n"
2187   ".width NUM1 NUM2 ...   Set column widths for \"column\" mode\n"
2188 ;
2189 
2190 static char zTimerHelp[] =
2191   ".timer ON|OFF          Turn the CPU timer measurement on or off\n"
2192 ;
2193 
2194 /* Forward reference */
2195 static int process_input(struct callback_data *p, FILE *in);
2196 
2197 /*
2198 ** Make sure the database is open.  If it is not, then open it.  If
2199 ** the database fails to open, print an error message and exit.
2200 */
open_db(struct callback_data * p)2201 static void open_db(struct callback_data *p){
2202   if( p->db==0 ){
2203     sqlite3_open(p->zDbFilename, &p->db);
2204     db = p->db;
2205     if( db && sqlite3_errcode(db)==SQLITE_OK ){
2206       sqlite3_create_function(db, "shellstatic", 0, SQLITE_UTF8, 0,
2207           shellstaticFunc, 0, 0);
2208     }
2209     if( db==0 || SQLITE_OK!=sqlite3_errcode(db) ){
2210       fprintf(stderr,"Error: unable to open database \"%s\": %s\n",
2211           p->zDbFilename, sqlite3_errmsg(db));
2212       exit(1);
2213     }
2214 #ifndef SQLITE_OMIT_LOAD_EXTENSION
2215     sqlite3_enable_load_extension(p->db, 1);
2216 #endif
2217   }
2218 }
2219 
2220 /*
2221 ** Do C-language style dequoting.
2222 **
2223 **    \t    -> tab
2224 **    \n    -> newline
2225 **    \r    -> carriage return
2226 **    \NNN  -> ascii character NNN in octal
2227 **    \\    -> backslash
2228 */
resolve_backslashes(char * z)2229 static void resolve_backslashes(char *z){
2230   int i, j;
2231   char c;
2232   for(i=j=0; (c = z[i])!=0; i++, j++){
2233     if( c=='\\' ){
2234       c = z[++i];
2235       if( c=='n' ){
2236         c = '\n';
2237       }else if( c=='t' ){
2238         c = '\t';
2239       }else if( c=='r' ){
2240         c = '\r';
2241       }else if( c>='0' && c<='7' ){
2242         c -= '0';
2243         if( z[i+1]>='0' && z[i+1]<='7' ){
2244           i++;
2245           c = (c<<3) + z[i] - '0';
2246           if( z[i+1]>='0' && z[i+1]<='7' ){
2247             i++;
2248             c = (c<<3) + z[i] - '0';
2249           }
2250         }
2251       }
2252     }
2253     z[j] = c;
2254   }
2255   z[j] = 0;
2256 }
2257 
2258 /*
2259 ** Interpret zArg as a boolean value.  Return either 0 or 1.
2260 */
booleanValue(char * zArg)2261 static int booleanValue(char *zArg){
2262   int val = atoi(zArg);
2263   int j;
2264   for(j=0; zArg[j]; j++){
2265     zArg[j] = (char)tolower(zArg[j]);
2266   }
2267   if( strcmp(zArg,"on")==0 ){
2268     val = 1;
2269   }else if( strcmp(zArg,"yes")==0 ){
2270     val = 1;
2271   }
2272   return val;
2273 }
2274 
2275 /*
2276 ** If an input line begins with "." then invoke this routine to
2277 ** process that line.
2278 **
2279 ** Return 1 on error, 2 to exit, and 0 otherwise.
2280 */
do_meta_command(char * zLine,struct callback_data * p)2281 static int do_meta_command(char *zLine, struct callback_data *p){
2282   int i = 1;
2283   int nArg = 0;
2284   int n, c;
2285   int rc = 0;
2286   char *azArg[50];
2287 
2288   /* Parse the input line into tokens.
2289   */
2290   while( zLine[i] && nArg<ArraySize(azArg) ){
2291     while( isspace((unsigned char)zLine[i]) ){ i++; }
2292     if( zLine[i]==0 ) break;
2293     if( zLine[i]=='\'' || zLine[i]=='"' ){
2294       int delim = zLine[i++];
2295       azArg[nArg++] = &zLine[i];
2296       while( zLine[i] && zLine[i]!=delim ){ i++; }
2297       if( zLine[i]==delim ){
2298         zLine[i++] = 0;
2299       }
2300       if( delim=='"' ) resolve_backslashes(azArg[nArg-1]);
2301     }else{
2302       azArg[nArg++] = &zLine[i];
2303       while( zLine[i] && !isspace((unsigned char)zLine[i]) ){ i++; }
2304       if( zLine[i] ) zLine[i++] = 0;
2305       resolve_backslashes(azArg[nArg-1]);
2306     }
2307   }
2308 
2309   /* Process the input line.
2310   */
2311   if( nArg==0 ) return 0; /* no tokens, no error */
2312   n = strlen30(azArg[0]);
2313   c = azArg[0][0];
2314   if( c=='b' && n>=3 && strncmp(azArg[0], "backup", n)==0 && nArg>1 && nArg<4){
2315     const char *zDestFile;
2316     const char *zDb;
2317     sqlite3 *pDest;
2318     sqlite3_backup *pBackup;
2319     if( nArg==2 ){
2320       zDestFile = azArg[1];
2321       zDb = "main";
2322     }else{
2323       zDestFile = azArg[2];
2324       zDb = azArg[1];
2325     }
2326     rc = sqlite3_open(zDestFile, &pDest);
2327     if( rc!=SQLITE_OK ){
2328       fprintf(stderr, "Error: cannot open \"%s\"\n", zDestFile);
2329       sqlite3_close(pDest);
2330       return 1;
2331     }
2332     open_db(p);
2333     pBackup = sqlite3_backup_init(pDest, "main", p->db, zDb);
2334     if( pBackup==0 ){
2335       fprintf(stderr, "Error: %s\n", sqlite3_errmsg(pDest));
2336       sqlite3_close(pDest);
2337       return 1;
2338     }
2339     while(  (rc = sqlite3_backup_step(pBackup,100))==SQLITE_OK ){}
2340     sqlite3_backup_finish(pBackup);
2341     if( rc==SQLITE_DONE ){
2342       rc = 0;
2343     }else{
2344       fprintf(stderr, "Error: %s\n", sqlite3_errmsg(pDest));
2345       rc = 1;
2346     }
2347     sqlite3_close(pDest);
2348   }else
2349 
2350   if( c=='b' && n>=3 && strncmp(azArg[0], "bail", n)==0 && nArg>1 && nArg<3 ){
2351     bail_on_error = booleanValue(azArg[1]);
2352   }else
2353 
2354   if( c=='d' && n>1 && strncmp(azArg[0], "databases", n)==0 && nArg==1 ){
2355     struct callback_data data;
2356     char *zErrMsg = 0;
2357     open_db(p);
2358     memcpy(&data, p, sizeof(data));
2359     data.showHeader = 1;
2360     data.mode = MODE_Column;
2361     data.colWidth[0] = 3;
2362     data.colWidth[1] = 15;
2363     data.colWidth[2] = 58;
2364     data.cnt = 0;
2365     sqlite3_exec(p->db, "PRAGMA database_list; ", callback, &data, &zErrMsg);
2366     if( zErrMsg ){
2367       fprintf(stderr,"Error: %s\n", zErrMsg);
2368       sqlite3_free(zErrMsg);
2369       rc = 1;
2370     }
2371   }else
2372 
2373   if( c=='d' && strncmp(azArg[0], "dump", n)==0 && nArg<3 ){
2374     char *zErrMsg = 0;
2375     open_db(p);
2376     /* When playing back a "dump", the content might appear in an order
2377     ** which causes immediate foreign key constraints to be violated.
2378     ** So disable foreign-key constraint enforcement to prevent problems. */
2379     fprintf(p->out, "PRAGMA foreign_keys=OFF;\n");
2380     fprintf(p->out, "BEGIN TRANSACTION;\n");
2381     p->writableSchema = 0;
2382     sqlite3_exec(p->db, "PRAGMA writable_schema=ON", 0, 0, 0);
2383     if( nArg==1 ){
2384       run_schema_dump_query(p,
2385         "SELECT name, type, sql FROM sqlite_master "
2386         "WHERE sql NOT NULL AND type=='table' AND name!='sqlite_sequence'", 0
2387       );
2388       run_schema_dump_query(p,
2389         "SELECT name, type, sql FROM sqlite_master "
2390         "WHERE name=='sqlite_sequence'", 0
2391       );
2392       run_table_dump_query(p->out, p->db,
2393         "SELECT sql FROM sqlite_master "
2394         "WHERE sql NOT NULL AND type IN ('index','trigger','view')", 0
2395       );
2396     }else{
2397       int i;
2398       for(i=1; i<nArg; i++){
2399         zShellStatic = azArg[i];
2400         run_schema_dump_query(p,
2401           "SELECT name, type, sql FROM sqlite_master "
2402           "WHERE tbl_name LIKE shellstatic() AND type=='table'"
2403           "  AND sql NOT NULL", 0);
2404         run_table_dump_query(p->out, p->db,
2405           "SELECT sql FROM sqlite_master "
2406           "WHERE sql NOT NULL"
2407           "  AND type IN ('index','trigger','view')"
2408           "  AND tbl_name LIKE shellstatic()", 0
2409         );
2410         zShellStatic = 0;
2411       }
2412     }
2413     if( p->writableSchema ){
2414       fprintf(p->out, "PRAGMA writable_schema=OFF;\n");
2415       p->writableSchema = 0;
2416     }
2417     sqlite3_exec(p->db, "PRAGMA writable_schema=OFF", 0, 0, 0);
2418     if( zErrMsg ){
2419       fprintf(stderr,"Error: %s\n", zErrMsg);
2420       sqlite3_free(zErrMsg);
2421     }else{
2422       fprintf(p->out, "COMMIT;\n");
2423     }
2424   }else
2425 
2426   if( c=='e' && strncmp(azArg[0], "echo", n)==0 && nArg>1 && nArg<3 ){
2427     p->echoOn = booleanValue(azArg[1]);
2428   }else
2429 
2430   if( c=='e' && strncmp(azArg[0], "exit", n)==0  && nArg==1 ){
2431     rc = 2;
2432   }else
2433 
2434   if( c=='e' && strncmp(azArg[0], "explain", n)==0 && nArg<3 ){
2435     int val = nArg>=2 ? booleanValue(azArg[1]) : 1;
2436     if(val == 1) {
2437       if(!p->explainPrev.valid) {
2438         p->explainPrev.valid = 1;
2439         p->explainPrev.mode = p->mode;
2440         p->explainPrev.showHeader = p->showHeader;
2441         memcpy(p->explainPrev.colWidth,p->colWidth,sizeof(p->colWidth));
2442       }
2443       /* We could put this code under the !p->explainValid
2444       ** condition so that it does not execute if we are already in
2445       ** explain mode. However, always executing it allows us an easy
2446       ** was to reset to explain mode in case the user previously
2447       ** did an .explain followed by a .width, .mode or .header
2448       ** command.
2449       */
2450       p->mode = MODE_Explain;
2451       p->showHeader = 1;
2452       memset(p->colWidth,0,ArraySize(p->colWidth));
2453       p->colWidth[0] = 4;                  /* addr */
2454       p->colWidth[1] = 13;                 /* opcode */
2455       p->colWidth[2] = 4;                  /* P1 */
2456       p->colWidth[3] = 4;                  /* P2 */
2457       p->colWidth[4] = 4;                  /* P3 */
2458       p->colWidth[5] = 13;                 /* P4 */
2459       p->colWidth[6] = 2;                  /* P5 */
2460       p->colWidth[7] = 13;                  /* Comment */
2461     }else if (p->explainPrev.valid) {
2462       p->explainPrev.valid = 0;
2463       p->mode = p->explainPrev.mode;
2464       p->showHeader = p->explainPrev.showHeader;
2465       memcpy(p->colWidth,p->explainPrev.colWidth,sizeof(p->colWidth));
2466     }
2467   }else
2468 
2469 #if !defined(SQLITE_OMIT_VIRTUALTABLE) && !defined(SQLITE_OMIT_SUBQUERY)
2470   if( c=='g' && strncmp(azArg[0], "genfkey", n)==0 ){
2471     GenfkeyCmd cmd;
2472     if( 0==genfkeyParseArgs(&cmd, &azArg[1], nArg-1) ){
2473       cmd.db = p->db;
2474       cmd.pCb = p;
2475       genfkey_create_triggers(p->db, "main", (void *)&cmd, genfkeyCmdCb);
2476     }
2477   }else
2478 #endif
2479 
2480   if( c=='h' && (strncmp(azArg[0], "header", n)==0 ||
2481                  strncmp(azArg[0], "headers", n)==0) && nArg>1 && nArg<3 ){
2482     p->showHeader = booleanValue(azArg[1]);
2483   }else
2484 
2485   if( c=='h' && strncmp(azArg[0], "help", n)==0 ){
2486     fprintf(stderr,"%s",zHelp);
2487     if( HAS_TIMER ){
2488       fprintf(stderr,"%s",zTimerHelp);
2489     }
2490   }else
2491 
2492   if( c=='i' && strncmp(azArg[0], "import", n)==0 && nArg==3 ){
2493     char *zTable = azArg[2];    /* Insert data into this table */
2494     char *zFile = azArg[1];     /* The file from which to extract data */
2495     sqlite3_stmt *pStmt = NULL; /* A statement */
2496     int nCol;                   /* Number of columns in the table */
2497     int nByte;                  /* Number of bytes in an SQL string */
2498     int i, j;                   /* Loop counters */
2499     int nSep;                   /* Number of bytes in p->separator[] */
2500     char *zSql;                 /* An SQL statement */
2501     char *zLine;                /* A single line of input from the file */
2502     char **azCol;               /* zLine[] broken up into columns */
2503     char *zCommit;              /* How to commit changes */
2504     FILE *in;                   /* The input file */
2505     int lineno = 0;             /* Line number of input file */
2506 
2507     open_db(p);
2508     nSep = strlen30(p->separator);
2509     if( nSep==0 ){
2510       fprintf(stderr, "Error: non-null separator required for import\n");
2511       return 1;
2512     }
2513     zSql = sqlite3_mprintf("SELECT * FROM '%q'", zTable);
2514     if( zSql==0 ){
2515       fprintf(stderr, "Error: out of memory\n");
2516       return 1;
2517     }
2518     nByte = strlen30(zSql);
2519     rc = sqlite3_prepare(p->db, zSql, -1, &pStmt, 0);
2520     sqlite3_free(zSql);
2521     if( rc ){
2522       if (pStmt) sqlite3_finalize(pStmt);
2523       fprintf(stderr,"Error: %s\n", sqlite3_errmsg(db));
2524       return 1;
2525     }
2526     nCol = sqlite3_column_count(pStmt);
2527     sqlite3_finalize(pStmt);
2528     pStmt = 0;
2529     if( nCol==0 ) return 0; /* no columns, no error */
2530     zSql = malloc( nByte + 20 + nCol*2 );
2531     if( zSql==0 ){
2532       fprintf(stderr, "Error: out of memory\n");
2533       return 1;
2534     }
2535     sqlite3_snprintf(nByte+20, zSql, "INSERT INTO '%q' VALUES(?", zTable);
2536     j = strlen30(zSql);
2537     for(i=1; i<nCol; i++){
2538       zSql[j++] = ',';
2539       zSql[j++] = '?';
2540     }
2541     zSql[j++] = ')';
2542     zSql[j] = 0;
2543     rc = sqlite3_prepare(p->db, zSql, -1, &pStmt, 0);
2544     free(zSql);
2545     if( rc ){
2546       fprintf(stderr, "Error: %s\n", sqlite3_errmsg(db));
2547       if (pStmt) sqlite3_finalize(pStmt);
2548       return 1;
2549     }
2550     in = fopen(zFile, "rb");
2551     if( in==0 ){
2552       fprintf(stderr, "Error: cannot open \"%s\"\n", zFile);
2553       sqlite3_finalize(pStmt);
2554       return 1;
2555     }
2556     azCol = malloc( sizeof(azCol[0])*(nCol+1) );
2557     if( azCol==0 ){
2558       fprintf(stderr, "Error: out of memory\n");
2559       fclose(in);
2560       sqlite3_finalize(pStmt);
2561       return 1;
2562     }
2563     sqlite3_exec(p->db, "BEGIN", 0, 0, 0);
2564     zCommit = "COMMIT";
2565     while( (zLine = local_getline(0, in))!=0 ){
2566       char *z;
2567       i = 0;
2568       lineno++;
2569       azCol[0] = zLine;
2570       for(i=0, z=zLine; *z && *z!='\n' && *z!='\r'; z++){
2571         if( *z==p->separator[0] && strncmp(z, p->separator, nSep)==0 ){
2572           *z = 0;
2573           i++;
2574           if( i<nCol ){
2575             azCol[i] = &z[nSep];
2576             z += nSep-1;
2577           }
2578         }
2579       } /* end for */
2580       *z = 0;
2581       if( i+1!=nCol ){
2582         fprintf(stderr,
2583                 "Error: %s line %d: expected %d columns of data but found %d\n",
2584                 zFile, lineno, nCol, i+1);
2585         zCommit = "ROLLBACK";
2586         free(zLine);
2587         rc = 1;
2588         break; /* from while */
2589       }
2590       for(i=0; i<nCol; i++){
2591         sqlite3_bind_text(pStmt, i+1, azCol[i], -1, SQLITE_STATIC);
2592       }
2593       sqlite3_step(pStmt);
2594       rc = sqlite3_reset(pStmt);
2595       free(zLine);
2596       if( rc!=SQLITE_OK ){
2597         fprintf(stderr,"Error: %s\n", sqlite3_errmsg(db));
2598         zCommit = "ROLLBACK";
2599         rc = 1;
2600         break; /* from while */
2601       }
2602     } /* end while */
2603     free(azCol);
2604     fclose(in);
2605     sqlite3_finalize(pStmt);
2606     sqlite3_exec(p->db, zCommit, 0, 0, 0);
2607   }else
2608 
2609   if( c=='i' && strncmp(azArg[0], "indices", n)==0 && nArg<3 ){
2610     struct callback_data data;
2611     char *zErrMsg = 0;
2612     open_db(p);
2613     memcpy(&data, p, sizeof(data));
2614     data.showHeader = 0;
2615     data.mode = MODE_List;
2616     if( nArg==1 ){
2617       rc = sqlite3_exec(p->db,
2618         "SELECT name FROM sqlite_master "
2619         "WHERE type='index' AND name NOT LIKE 'sqlite_%' "
2620         "UNION ALL "
2621         "SELECT name FROM sqlite_temp_master "
2622         "WHERE type='index' "
2623         "ORDER BY 1",
2624         callback, &data, &zErrMsg
2625       );
2626     }else{
2627       zShellStatic = azArg[1];
2628       rc = sqlite3_exec(p->db,
2629         "SELECT name FROM sqlite_master "
2630         "WHERE type='index' AND tbl_name LIKE shellstatic() "
2631         "UNION ALL "
2632         "SELECT name FROM sqlite_temp_master "
2633         "WHERE type='index' AND tbl_name LIKE shellstatic() "
2634         "ORDER BY 1",
2635         callback, &data, &zErrMsg
2636       );
2637       zShellStatic = 0;
2638     }
2639     if( zErrMsg ){
2640       fprintf(stderr,"Error: %s\n", zErrMsg);
2641       sqlite3_free(zErrMsg);
2642       rc = 1;
2643     }else if( rc != SQLITE_OK ){
2644       fprintf(stderr,"Error: querying sqlite_master and sqlite_temp_master\n");
2645       rc = 1;
2646     }
2647   }else
2648 
2649 #ifdef SQLITE_ENABLE_IOTRACE
2650   if( c=='i' && strncmp(azArg[0], "iotrace", n)==0 ){
2651     extern void (*sqlite3IoTrace)(const char*, ...);
2652     if( iotrace && iotrace!=stdout ) fclose(iotrace);
2653     iotrace = 0;
2654     if( nArg<2 ){
2655       sqlite3IoTrace = 0;
2656     }else if( strcmp(azArg[1], "-")==0 ){
2657       sqlite3IoTrace = iotracePrintf;
2658       iotrace = stdout;
2659     }else{
2660       iotrace = fopen(azArg[1], "w");
2661       if( iotrace==0 ){
2662         fprintf(stderr, "Error: cannot open \"%s\"\n", azArg[1]);
2663         sqlite3IoTrace = 0;
2664         rc = 1;
2665       }else{
2666         sqlite3IoTrace = iotracePrintf;
2667       }
2668     }
2669   }else
2670 #endif
2671 
2672 #ifndef SQLITE_OMIT_LOAD_EXTENSION
2673   if( c=='l' && strncmp(azArg[0], "load", n)==0 && nArg>=2 ){
2674     const char *zFile, *zProc;
2675     char *zErrMsg = 0;
2676     zFile = azArg[1];
2677     zProc = nArg>=3 ? azArg[2] : 0;
2678     open_db(p);
2679     rc = sqlite3_load_extension(p->db, zFile, zProc, &zErrMsg);
2680     if( rc!=SQLITE_OK ){
2681       fprintf(stderr, "Error: %s\n", zErrMsg);
2682       sqlite3_free(zErrMsg);
2683       rc = 1;
2684     }
2685   }else
2686 #endif
2687 
2688   if( c=='l' && strncmp(azArg[0], "log", n)==0 && nArg>=1 ){
2689     const char *zFile = azArg[1];
2690     if( p->pLog && p->pLog!=stdout && p->pLog!=stderr ){
2691       fclose(p->pLog);
2692       p->pLog = 0;
2693     }
2694     if( strcmp(zFile,"stdout")==0 ){
2695       p->pLog = stdout;
2696     }else if( strcmp(zFile, "stderr")==0 ){
2697       p->pLog = stderr;
2698     }else if( strcmp(zFile, "off")==0 ){
2699       p->pLog = 0;
2700     }else{
2701       p->pLog = fopen(zFile, "w");
2702       if( p->pLog==0 ){
2703         fprintf(stderr, "Error: cannot open \"%s\"\n", zFile);
2704       }
2705     }
2706   }else
2707 
2708   if( c=='m' && strncmp(azArg[0], "mode", n)==0 && nArg==2 ){
2709     int n2 = strlen30(azArg[1]);
2710     if( (n2==4 && strncmp(azArg[1],"line",n2)==0)
2711         ||
2712         (n2==5 && strncmp(azArg[1],"lines",n2)==0) ){
2713       p->mode = MODE_Line;
2714     }else if( (n2==6 && strncmp(azArg[1],"column",n2)==0)
2715               ||
2716               (n2==7 && strncmp(azArg[1],"columns",n2)==0) ){
2717       p->mode = MODE_Column;
2718     }else if( n2==4 && strncmp(azArg[1],"list",n2)==0 ){
2719       p->mode = MODE_List;
2720     }else if( n2==4 && strncmp(azArg[1],"html",n2)==0 ){
2721       p->mode = MODE_Html;
2722     }else if( n2==3 && strncmp(azArg[1],"tcl",n2)==0 ){
2723       p->mode = MODE_Tcl;
2724     }else if( n2==3 && strncmp(azArg[1],"csv",n2)==0 ){
2725       p->mode = MODE_Csv;
2726       sqlite3_snprintf(sizeof(p->separator), p->separator, ",");
2727     }else if( n2==4 && strncmp(azArg[1],"tabs",n2)==0 ){
2728       p->mode = MODE_List;
2729       sqlite3_snprintf(sizeof(p->separator), p->separator, "\t");
2730     }else if( n2==6 && strncmp(azArg[1],"insert",n2)==0 ){
2731       p->mode = MODE_Insert;
2732       set_table_name(p, "table");
2733     }else {
2734       fprintf(stderr,"Error: mode should be one of: "
2735          "column csv html insert line list tabs tcl\n");
2736       rc = 1;
2737     }
2738   }else
2739 
2740   if( c=='m' && strncmp(azArg[0], "mode", n)==0 && nArg==3 ){
2741     int n2 = strlen30(azArg[1]);
2742     if( n2==6 && strncmp(azArg[1],"insert",n2)==0 ){
2743       p->mode = MODE_Insert;
2744       set_table_name(p, azArg[2]);
2745     }else {
2746       fprintf(stderr, "Error: invalid arguments: "
2747         " \"%s\". Enter \".help\" for help\n", azArg[2]);
2748       rc = 1;
2749     }
2750   }else
2751 
2752   if( c=='n' && strncmp(azArg[0], "nullvalue", n)==0 && nArg==2 ) {
2753     sqlite3_snprintf(sizeof(p->nullvalue), p->nullvalue,
2754                      "%.*s", (int)ArraySize(p->nullvalue)-1, azArg[1]);
2755   }else
2756 
2757   if( c=='o' && strncmp(azArg[0], "output", n)==0 && nArg==2 ){
2758     if( p->out!=stdout ){
2759       fclose(p->out);
2760     }
2761     if( strcmp(azArg[1],"stdout")==0 ){
2762       p->out = stdout;
2763       sqlite3_snprintf(sizeof(p->outfile), p->outfile, "stdout");
2764     }else{
2765       p->out = fopen(azArg[1], "wb");
2766       if( p->out==0 ){
2767         fprintf(stderr,"Error: cannot write to \"%s\"\n", azArg[1]);
2768         p->out = stdout;
2769         rc = 1;
2770       } else {
2771          sqlite3_snprintf(sizeof(p->outfile), p->outfile, "%s", azArg[1]);
2772       }
2773     }
2774   }else
2775 
2776   if( c=='p' && strncmp(azArg[0], "prompt", n)==0 && (nArg==2 || nArg==3)){
2777     if( nArg >= 2) {
2778       strncpy(mainPrompt,azArg[1],(int)ArraySize(mainPrompt)-1);
2779     }
2780     if( nArg >= 3) {
2781       strncpy(continuePrompt,azArg[2],(int)ArraySize(continuePrompt)-1);
2782     }
2783   }else
2784 
2785   if( c=='q' && strncmp(azArg[0], "quit", n)==0 && nArg==1 ){
2786     rc = 2;
2787   }else
2788 
2789   if( c=='r' && n>=3 && strncmp(azArg[0], "read", n)==0 && nArg==2 ){
2790     FILE *alt = fopen(azArg[1], "rb");
2791     if( alt==0 ){
2792       fprintf(stderr,"Error: cannot open \"%s\"\n", azArg[1]);
2793       rc = 1;
2794     }else{
2795       rc = process_input(p, alt);
2796       fclose(alt);
2797     }
2798   }else
2799 
2800   if( c=='r' && n>=3 && strncmp(azArg[0], "restore", n)==0 && nArg>1 && nArg<4){
2801     const char *zSrcFile;
2802     const char *zDb;
2803     sqlite3 *pSrc;
2804     sqlite3_backup *pBackup;
2805     int nTimeout = 0;
2806 
2807     if( nArg==2 ){
2808       zSrcFile = azArg[1];
2809       zDb = "main";
2810     }else{
2811       zSrcFile = azArg[2];
2812       zDb = azArg[1];
2813     }
2814     rc = sqlite3_open(zSrcFile, &pSrc);
2815     if( rc!=SQLITE_OK ){
2816       fprintf(stderr, "Error: cannot open \"%s\"\n", zSrcFile);
2817       sqlite3_close(pSrc);
2818       return 1;
2819     }
2820     open_db(p);
2821     pBackup = sqlite3_backup_init(p->db, zDb, pSrc, "main");
2822     if( pBackup==0 ){
2823       fprintf(stderr, "Error: %s\n", sqlite3_errmsg(p->db));
2824       sqlite3_close(pSrc);
2825       return 1;
2826     }
2827     while( (rc = sqlite3_backup_step(pBackup,100))==SQLITE_OK
2828           || rc==SQLITE_BUSY  ){
2829       if( rc==SQLITE_BUSY ){
2830         if( nTimeout++ >= 3 ) break;
2831         sqlite3_sleep(100);
2832       }
2833     }
2834     sqlite3_backup_finish(pBackup);
2835     if( rc==SQLITE_DONE ){
2836       rc = 0;
2837     }else if( rc==SQLITE_BUSY || rc==SQLITE_LOCKED ){
2838       fprintf(stderr, "Error: source database is busy\n");
2839       rc = 1;
2840     }else{
2841       fprintf(stderr, "Error: %s\n", sqlite3_errmsg(p->db));
2842       rc = 1;
2843     }
2844     sqlite3_close(pSrc);
2845   }else
2846 
2847   if( c=='s' && strncmp(azArg[0], "schema", n)==0 && nArg<3 ){
2848     struct callback_data data;
2849     char *zErrMsg = 0;
2850     open_db(p);
2851     memcpy(&data, p, sizeof(data));
2852     data.showHeader = 0;
2853     data.mode = MODE_Semi;
2854     if( nArg>1 ){
2855       int i;
2856       for(i=0; azArg[1][i]; i++) azArg[1][i] = (char)tolower(azArg[1][i]);
2857       if( strcmp(azArg[1],"sqlite_master")==0 ){
2858         char *new_argv[2], *new_colv[2];
2859         new_argv[0] = "CREATE TABLE sqlite_master (\n"
2860                       "  type text,\n"
2861                       "  name text,\n"
2862                       "  tbl_name text,\n"
2863                       "  rootpage integer,\n"
2864                       "  sql text\n"
2865                       ")";
2866         new_argv[1] = 0;
2867         new_colv[0] = "sql";
2868         new_colv[1] = 0;
2869         callback(&data, 1, new_argv, new_colv);
2870         rc = SQLITE_OK;
2871       }else if( strcmp(azArg[1],"sqlite_temp_master")==0 ){
2872         char *new_argv[2], *new_colv[2];
2873         new_argv[0] = "CREATE TEMP TABLE sqlite_temp_master (\n"
2874                       "  type text,\n"
2875                       "  name text,\n"
2876                       "  tbl_name text,\n"
2877                       "  rootpage integer,\n"
2878                       "  sql text\n"
2879                       ")";
2880         new_argv[1] = 0;
2881         new_colv[0] = "sql";
2882         new_colv[1] = 0;
2883         callback(&data, 1, new_argv, new_colv);
2884         rc = SQLITE_OK;
2885       }else{
2886         zShellStatic = azArg[1];
2887         rc = sqlite3_exec(p->db,
2888           "SELECT sql FROM "
2889           "  (SELECT sql sql, type type, tbl_name tbl_name, name name"
2890           "     FROM sqlite_master UNION ALL"
2891           "   SELECT sql, type, tbl_name, name FROM sqlite_temp_master) "
2892           "WHERE tbl_name LIKE shellstatic() AND type!='meta' AND sql NOTNULL "
2893           "ORDER BY substr(type,2,1), name",
2894           callback, &data, &zErrMsg);
2895         zShellStatic = 0;
2896       }
2897     }else{
2898       rc = sqlite3_exec(p->db,
2899          "SELECT sql FROM "
2900          "  (SELECT sql sql, type type, tbl_name tbl_name, name name"
2901          "     FROM sqlite_master UNION ALL"
2902          "   SELECT sql, type, tbl_name, name FROM sqlite_temp_master) "
2903          "WHERE type!='meta' AND sql NOTNULL AND name NOT LIKE 'sqlite_%'"
2904          "ORDER BY substr(type,2,1), name",
2905          callback, &data, &zErrMsg
2906       );
2907     }
2908     if( zErrMsg ){
2909       fprintf(stderr,"Error: %s\n", zErrMsg);
2910       sqlite3_free(zErrMsg);
2911       rc = 1;
2912     }else if( rc != SQLITE_OK ){
2913       fprintf(stderr,"Error: querying schema information\n");
2914       rc = 1;
2915     }else{
2916       rc = 0;
2917     }
2918   }else
2919 
2920   if( c=='s' && strncmp(azArg[0], "separator", n)==0 && nArg==2 ){
2921     sqlite3_snprintf(sizeof(p->separator), p->separator,
2922                      "%.*s", (int)sizeof(p->separator)-1, azArg[1]);
2923   }else
2924 
2925   if( c=='s' && strncmp(azArg[0], "show", n)==0 && nArg==1 ){
2926     int i;
2927     fprintf(p->out,"%9.9s: %s\n","echo", p->echoOn ? "on" : "off");
2928     fprintf(p->out,"%9.9s: %s\n","explain", p->explainPrev.valid ? "on" :"off");
2929     fprintf(p->out,"%9.9s: %s\n","headers", p->showHeader ? "on" : "off");
2930     fprintf(p->out,"%9.9s: %s\n","mode", modeDescr[p->mode]);
2931     fprintf(p->out,"%9.9s: ", "nullvalue");
2932       output_c_string(p->out, p->nullvalue);
2933       fprintf(p->out, "\n");
2934     fprintf(p->out,"%9.9s: %s\n","output",
2935             strlen30(p->outfile) ? p->outfile : "stdout");
2936     fprintf(p->out,"%9.9s: ", "separator");
2937       output_c_string(p->out, p->separator);
2938       fprintf(p->out, "\n");
2939     fprintf(p->out,"%9.9s: ","width");
2940     for (i=0;i<(int)ArraySize(p->colWidth) && p->colWidth[i] != 0;i++) {
2941       fprintf(p->out,"%d ",p->colWidth[i]);
2942     }
2943     fprintf(p->out,"\n");
2944   }else
2945 
2946   if( c=='t' && n>1 && strncmp(azArg[0], "tables", n)==0 && nArg<3 ){
2947     char **azResult;
2948     int nRow;
2949     char *zErrMsg;
2950     open_db(p);
2951     if( nArg==1 ){
2952       rc = sqlite3_get_table(p->db,
2953         "SELECT name FROM sqlite_master "
2954         "WHERE type IN ('table','view') AND name NOT LIKE 'sqlite_%' "
2955         "UNION ALL "
2956         "SELECT name FROM sqlite_temp_master "
2957         "WHERE type IN ('table','view') "
2958         "ORDER BY 1",
2959         &azResult, &nRow, 0, &zErrMsg
2960       );
2961     }else{
2962       zShellStatic = azArg[1];
2963       rc = sqlite3_get_table(p->db,
2964         "SELECT name FROM sqlite_master "
2965         "WHERE type IN ('table','view') AND name LIKE shellstatic() "
2966         "UNION ALL "
2967         "SELECT name FROM sqlite_temp_master "
2968         "WHERE type IN ('table','view') AND name LIKE shellstatic() "
2969         "ORDER BY 1",
2970         &azResult, &nRow, 0, &zErrMsg
2971       );
2972       zShellStatic = 0;
2973     }
2974     if( zErrMsg ){
2975       fprintf(stderr,"Error: %s\n", zErrMsg);
2976       sqlite3_free(zErrMsg);
2977       rc = 1;
2978     }else if( rc != SQLITE_OK ){
2979       fprintf(stderr,"Error: querying sqlite_master and sqlite_temp_master\n");
2980       rc = 1;
2981     }else{
2982       int len, maxlen = 0;
2983       int i, j;
2984       int nPrintCol, nPrintRow;
2985       for(i=1; i<=nRow; i++){
2986         if( azResult[i]==0 ) continue;
2987         len = strlen30(azResult[i]);
2988         if( len>maxlen ) maxlen = len;
2989       }
2990       nPrintCol = 80/(maxlen+2);
2991       if( nPrintCol<1 ) nPrintCol = 1;
2992       nPrintRow = (nRow + nPrintCol - 1)/nPrintCol;
2993       for(i=0; i<nPrintRow; i++){
2994         for(j=i+1; j<=nRow; j+=nPrintRow){
2995           char *zSp = j<=nPrintRow ? "" : "  ";
2996           printf("%s%-*s", zSp, maxlen, azResult[j] ? azResult[j] : "");
2997         }
2998         printf("\n");
2999       }
3000     }
3001     sqlite3_free_table(azResult);
3002   }else
3003 
3004   if( c=='t' && n>4 && strncmp(azArg[0], "timeout", n)==0 && nArg==2 ){
3005     open_db(p);
3006     sqlite3_busy_timeout(p->db, atoi(azArg[1]));
3007   }else
3008 
3009   if( HAS_TIMER && c=='t' && n>=5 && strncmp(azArg[0], "timer", n)==0 && nArg==2 ){
3010     enableTimer = booleanValue(azArg[1]);
3011   }else
3012 
3013   if( c=='w' && strncmp(azArg[0], "width", n)==0 && nArg>1 ){
3014     int j;
3015     assert( nArg<=ArraySize(azArg) );
3016     for(j=1; j<nArg && j<ArraySize(p->colWidth); j++){
3017       p->colWidth[j-1] = atoi(azArg[j]);
3018     }
3019   }else
3020 
3021   {
3022     fprintf(stderr, "Error: unknown command or invalid arguments: "
3023       " \"%s\". Enter \".help\" for help\n", azArg[0]);
3024     rc = 1;
3025   }
3026 
3027   return rc;
3028 }
3029 
3030 /*
3031 ** Return TRUE if a semicolon occurs anywhere in the first N characters
3032 ** of string z[].
3033 */
_contains_semicolon(const char * z,int N)3034 static int _contains_semicolon(const char *z, int N){
3035   int i;
3036   for(i=0; i<N; i++){  if( z[i]==';' ) return 1; }
3037   return 0;
3038 }
3039 
3040 /*
3041 ** Test to see if a line consists entirely of whitespace.
3042 */
_all_whitespace(const char * z)3043 static int _all_whitespace(const char *z){
3044   for(; *z; z++){
3045     if( isspace(*(unsigned char*)z) ) continue;
3046     if( *z=='/' && z[1]=='*' ){
3047       z += 2;
3048       while( *z && (*z!='*' || z[1]!='/') ){ z++; }
3049       if( *z==0 ) return 0;
3050       z++;
3051       continue;
3052     }
3053     if( *z=='-' && z[1]=='-' ){
3054       z += 2;
3055       while( *z && *z!='\n' ){ z++; }
3056       if( *z==0 ) return 1;
3057       continue;
3058     }
3059     return 0;
3060   }
3061   return 1;
3062 }
3063 
3064 /*
3065 ** Return TRUE if the line typed in is an SQL command terminator other
3066 ** than a semi-colon.  The SQL Server style "go" command is understood
3067 ** as is the Oracle "/".
3068 */
_is_command_terminator(const char * zLine)3069 static int _is_command_terminator(const char *zLine){
3070   while( isspace(*(unsigned char*)zLine) ){ zLine++; };
3071   if( zLine[0]=='/' && _all_whitespace(&zLine[1]) ){
3072     return 1;  /* Oracle */
3073   }
3074   if( tolower(zLine[0])=='g' && tolower(zLine[1])=='o'
3075          && _all_whitespace(&zLine[2]) ){
3076     return 1;  /* SQL Server */
3077   }
3078   return 0;
3079 }
3080 
3081 /*
3082 ** Return true if zSql is a complete SQL statement.  Return false if it
3083 ** ends in the middle of a string literal or C-style comment.
3084 */
_is_complete(char * zSql,int nSql)3085 static int _is_complete(char *zSql, int nSql){
3086   int rc;
3087   if( zSql==0 ) return 1;
3088   zSql[nSql] = ';';
3089   zSql[nSql+1] = 0;
3090   rc = sqlite3_complete(zSql);
3091   zSql[nSql] = 0;
3092   return rc;
3093 }
3094 
3095 /*
3096 ** Read input from *in and process it.  If *in==0 then input
3097 ** is interactive - the user is typing it it.  Otherwise, input
3098 ** is coming from a file or device.  A prompt is issued and history
3099 ** is saved only if input is interactive.  An interrupt signal will
3100 ** cause this routine to exit immediately, unless input is interactive.
3101 **
3102 ** Return the number of errors.
3103 */
process_input(struct callback_data * p,FILE * in)3104 static int process_input(struct callback_data *p, FILE *in){
3105   char *zLine = 0;
3106   char *zSql = 0;
3107   int nSql = 0;
3108   int nSqlPrior = 0;
3109   char *zErrMsg;
3110   int rc;
3111   int errCnt = 0;
3112   int lineno = 0;
3113   int startline = 0;
3114 
3115   while( errCnt==0 || !bail_on_error || (in==0 && stdin_is_interactive) ){
3116     fflush(p->out);
3117     free(zLine);
3118     zLine = one_input_line(zSql, in);
3119     if( zLine==0 ){
3120       break;  /* We have reached EOF */
3121     }
3122     if( seenInterrupt ){
3123       if( in!=0 ) break;
3124       seenInterrupt = 0;
3125     }
3126     lineno++;
3127     if( (zSql==0 || zSql[0]==0) && _all_whitespace(zLine) ) continue;
3128     if( zLine && zLine[0]=='.' && nSql==0 ){
3129       if( p->echoOn ) printf("%s\n", zLine);
3130       rc = do_meta_command(zLine, p);
3131       if( rc==2 ){ /* exit requested */
3132         break;
3133       }else if( rc ){
3134         errCnt++;
3135       }
3136       continue;
3137     }
3138     if( _is_command_terminator(zLine) && _is_complete(zSql, nSql) ){
3139       memcpy(zLine,";",2);
3140     }
3141     nSqlPrior = nSql;
3142     if( zSql==0 ){
3143       int i;
3144       for(i=0; zLine[i] && isspace((unsigned char)zLine[i]); i++){}
3145       if( zLine[i]!=0 ){
3146         nSql = strlen30(zLine);
3147         zSql = malloc( nSql+3 );
3148         if( zSql==0 ){
3149           fprintf(stderr, "Error: out of memory\n");
3150           exit(1);
3151         }
3152         memcpy(zSql, zLine, nSql+1);
3153         startline = lineno;
3154       }
3155     }else{
3156       int len = strlen30(zLine);
3157       zSql = realloc( zSql, nSql + len + 4 );
3158       if( zSql==0 ){
3159         fprintf(stderr,"Error: out of memory\n");
3160         exit(1);
3161       }
3162       zSql[nSql++] = '\n';
3163       memcpy(&zSql[nSql], zLine, len+1);
3164       nSql += len;
3165     }
3166     if( zSql && _contains_semicolon(&zSql[nSqlPrior], nSql-nSqlPrior)
3167                 && sqlite3_complete(zSql) ){
3168       p->cnt = 0;
3169       open_db(p);
3170       BEGIN_TIMER;
3171       rc = shell_exec(p->db, zSql, shell_callback, p, &zErrMsg);
3172       END_TIMER;
3173       if( rc || zErrMsg ){
3174         char zPrefix[100];
3175         if( in!=0 || !stdin_is_interactive ){
3176           sqlite3_snprintf(sizeof(zPrefix), zPrefix,
3177                            "Error: near line %d:", startline);
3178         }else{
3179           sqlite3_snprintf(sizeof(zPrefix), zPrefix, "Error:");
3180         }
3181         if( zErrMsg!=0 ){
3182           fprintf(stderr, "%s %s\n", zPrefix, zErrMsg);
3183           sqlite3_free(zErrMsg);
3184           zErrMsg = 0;
3185         }else{
3186           fprintf(stderr, "%s %s\n", zPrefix, sqlite3_errmsg(p->db));
3187         }
3188         errCnt++;
3189       }
3190       free(zSql);
3191       zSql = 0;
3192       nSql = 0;
3193     }
3194   }
3195   if( zSql ){
3196     if( !_all_whitespace(zSql) ) fprintf(stderr, "Error: incomplete SQL: %s\n", zSql);
3197     free(zSql);
3198   }
3199   free(zLine);
3200   return errCnt;
3201 }
3202 
3203 /*
3204 ** Return a pathname which is the user's home directory.  A
3205 ** 0 return indicates an error of some kind.  Space to hold the
3206 ** resulting string is obtained from malloc().  The calling
3207 ** function should free the result.
3208 */
find_home_dir(void)3209 static char *find_home_dir(void){
3210   char *home_dir = NULL;
3211 
3212 #if !defined(_WIN32) && !defined(WIN32) && !defined(__OS2__) && !defined(_WIN32_WCE) && !defined(__RTP__) && !defined(_WRS_KERNEL)
3213   struct passwd *pwent;
3214   uid_t uid = getuid();
3215   if( (pwent=getpwuid(uid)) != NULL) {
3216     home_dir = pwent->pw_dir;
3217   }
3218 #endif
3219 
3220 #if defined(_WIN32_WCE)
3221   /* Windows CE (arm-wince-mingw32ce-gcc) does not provide getenv()
3222    */
3223   home_dir = strdup("/");
3224 #else
3225 
3226 #if defined(_WIN32) || defined(WIN32) || defined(__OS2__)
3227   if (!home_dir) {
3228     home_dir = getenv("USERPROFILE");
3229   }
3230 #endif
3231 
3232   if (!home_dir) {
3233     home_dir = getenv("HOME");
3234   }
3235 
3236 #if defined(_WIN32) || defined(WIN32) || defined(__OS2__)
3237   if (!home_dir) {
3238     char *zDrive, *zPath;
3239     int n;
3240     zDrive = getenv("HOMEDRIVE");
3241     zPath = getenv("HOMEPATH");
3242     if( zDrive && zPath ){
3243       n = strlen30(zDrive) + strlen30(zPath) + 1;
3244       home_dir = malloc( n );
3245       if( home_dir==0 ) return 0;
3246       sqlite3_snprintf(n, home_dir, "%s%s", zDrive, zPath);
3247       return home_dir;
3248     }
3249     home_dir = "c:\\";
3250   }
3251 #endif
3252 
3253 #endif /* !_WIN32_WCE */
3254 
3255   if( home_dir ){
3256     int n = strlen30(home_dir) + 1;
3257     char *z = malloc( n );
3258     if( z ) memcpy(z, home_dir, n);
3259     home_dir = z;
3260   }
3261 
3262   return home_dir;
3263 }
3264 
3265 /*
3266 ** Read input from the file given by sqliterc_override.  Or if that
3267 ** parameter is NULL, take input from ~/.sqliterc
3268 **
3269 ** Returns the number of errors.
3270 */
process_sqliterc(struct callback_data * p,const char * sqliterc_override)3271 static int process_sqliterc(
3272   struct callback_data *p,        /* Configuration data */
3273   const char *sqliterc_override   /* Name of config file. NULL to use default */
3274 ){
3275   char *home_dir = NULL;
3276   const char *sqliterc = sqliterc_override;
3277   char *zBuf = 0;
3278   FILE *in = NULL;
3279   int nBuf;
3280   int rc = 0;
3281 
3282   if (sqliterc == NULL) {
3283     home_dir = find_home_dir();
3284     if( home_dir==0 ){
3285 #if !defined(__RTP__) && !defined(_WRS_KERNEL)
3286       fprintf(stderr,"%s: Error: cannot locate your home directory\n", Argv0);
3287 #endif
3288       return 1;
3289     }
3290     nBuf = strlen30(home_dir) + 16;
3291     zBuf = malloc( nBuf );
3292     if( zBuf==0 ){
3293       fprintf(stderr,"%s: Error: out of memory\n",Argv0);
3294       return 1;
3295     }
3296     sqlite3_snprintf(nBuf, zBuf,"%s/.sqliterc",home_dir);
3297     free(home_dir);
3298     sqliterc = (const char*)zBuf;
3299   }
3300   in = fopen(sqliterc,"rb");
3301   if( in ){
3302     if( stdin_is_interactive ){
3303       fprintf(stderr,"-- Loading resources from %s\n",sqliterc);
3304     }
3305     rc = process_input(p,in);
3306     fclose(in);
3307   }
3308   free(zBuf);
3309   return rc;
3310 }
3311 
3312 /*
3313 ** Show available command line options
3314 */
3315 static const char zOptions[] =
3316   "   -help                show this message\n"
3317   "   -init filename       read/process named file\n"
3318   "   -echo                print commands before execution\n"
3319   "   -[no]header          turn headers on or off\n"
3320   "   -bail                stop after hitting an error\n"
3321   "   -interactive         force interactive I/O\n"
3322   "   -batch               force batch I/O\n"
3323   "   -column              set output mode to 'column'\n"
3324   "   -csv                 set output mode to 'csv'\n"
3325   "   -html                set output mode to HTML\n"
3326   "   -line                set output mode to 'line'\n"
3327   "   -list                set output mode to 'list'\n"
3328   "   -separator 'x'       set output field separator (|)\n"
3329   "   -nullvalue 'text'    set text string for NULL values\n"
3330   "   -version             show SQLite version\n"
3331 ;
usage(int showDetail)3332 static void usage(int showDetail){
3333   fprintf(stderr,
3334       "Usage: %s [OPTIONS] FILENAME [SQL]\n"
3335       "FILENAME is the name of an SQLite database. A new database is created\n"
3336       "if the file does not previously exist.\n", Argv0);
3337   if( showDetail ){
3338     fprintf(stderr, "OPTIONS include:\n%s", zOptions);
3339   }else{
3340     fprintf(stderr, "Use the -help option for additional information\n");
3341   }
3342   exit(1);
3343 }
3344 
3345 /*
3346 ** Initialize the state information in data
3347 */
main_init(struct callback_data * data)3348 static void main_init(struct callback_data *data) {
3349   memset(data, 0, sizeof(*data));
3350   data->mode = MODE_List;
3351   memcpy(data->separator,"|", 2);
3352   data->showHeader = 0;
3353   sqlite3_config(SQLITE_CONFIG_LOG, shellLog, data);
3354   sqlite3_snprintf(sizeof(mainPrompt), mainPrompt,"sqlite> ");
3355   sqlite3_snprintf(sizeof(continuePrompt), continuePrompt,"   ...> ");
3356 }
3357 
main(int argc,char ** argv)3358 int main(int argc, char **argv){
3359   char *zErrMsg = 0;
3360   struct callback_data data;
3361   const char *zInitFile = 0;
3362   char *zFirstCmd = 0;
3363   int i;
3364   int rc = 0;
3365 
3366   Argv0 = argv[0];
3367   main_init(&data);
3368   stdin_is_interactive = isatty(0);
3369 
3370   /* Make sure we have a valid signal handler early, before anything
3371   ** else is done.
3372   */
3373 #ifdef SIGINT
3374   signal(SIGINT, interrupt_handler);
3375 #endif
3376 
3377   /* Do an initial pass through the command-line argument to locate
3378   ** the name of the database file, the name of the initialization file,
3379   ** and the first command to execute.
3380   */
3381   for(i=1; i<argc-1; i++){
3382     char *z;
3383     if( argv[i][0]!='-' ) break;
3384     z = argv[i];
3385     if( z[0]=='-' && z[1]=='-' ) z++;
3386     if( strcmp(argv[i],"-separator")==0 || strcmp(argv[i],"-nullvalue")==0 ){
3387       i++;
3388     }else if( strcmp(argv[i],"-init")==0 ){
3389       i++;
3390       zInitFile = argv[i];
3391     /* Need to check for batch mode here to so we can avoid printing
3392     ** informational messages (like from process_sqliterc) before
3393     ** we do the actual processing of arguments later in a second pass.
3394     */
3395     }else if( strcmp(argv[i],"-batch")==0 ){
3396       stdin_is_interactive = 0;
3397     }
3398   }
3399   if( i<argc ){
3400 #if defined(SQLITE_OS_OS2) && SQLITE_OS_OS2
3401     data.zDbFilename = (const char *)convertCpPathToUtf8( argv[i++] );
3402 #else
3403     data.zDbFilename = argv[i++];
3404 #endif
3405   }else{
3406 #ifndef SQLITE_OMIT_MEMORYDB
3407     data.zDbFilename = ":memory:";
3408 #else
3409     data.zDbFilename = 0;
3410 #endif
3411   }
3412   if( i<argc ){
3413     zFirstCmd = argv[i++];
3414   }
3415   if( i<argc ){
3416     fprintf(stderr,"%s: Error: too many options: \"%s\"\n", Argv0, argv[i]);
3417     fprintf(stderr,"Use -help for a list of options.\n");
3418     return 1;
3419   }
3420   data.out = stdout;
3421 
3422 #ifdef SQLITE_OMIT_MEMORYDB
3423   if( data.zDbFilename==0 ){
3424     fprintf(stderr,"%s: Error: no database filename specified\n", Argv0);
3425     return 1;
3426   }
3427 #endif
3428 
3429   /* Go ahead and open the database file if it already exists.  If the
3430   ** file does not exist, delay opening it.  This prevents empty database
3431   ** files from being created if a user mistypes the database name argument
3432   ** to the sqlite command-line tool.
3433   */
3434   if( access(data.zDbFilename, 0)==0 ){
3435     open_db(&data);
3436   }
3437 
3438   /* Process the initialization file if there is one.  If no -init option
3439   ** is given on the command line, look for a file named ~/.sqliterc and
3440   ** try to process it.
3441   */
3442   rc = process_sqliterc(&data,zInitFile);
3443   if( rc>0 ){
3444     return rc;
3445   }
3446 
3447   /* Make a second pass through the command-line argument and set
3448   ** options.  This second pass is delayed until after the initialization
3449   ** file is processed so that the command-line arguments will override
3450   ** settings in the initialization file.
3451   */
3452   for(i=1; i<argc && argv[i][0]=='-'; i++){
3453     char *z = argv[i];
3454     if( z[1]=='-' ){ z++; }
3455     if( strcmp(z,"-init")==0 ){
3456       i++;
3457     }else if( strcmp(z,"-html")==0 ){
3458       data.mode = MODE_Html;
3459     }else if( strcmp(z,"-list")==0 ){
3460       data.mode = MODE_List;
3461     }else if( strcmp(z,"-line")==0 ){
3462       data.mode = MODE_Line;
3463     }else if( strcmp(z,"-column")==0 ){
3464       data.mode = MODE_Column;
3465     }else if( strcmp(z,"-csv")==0 ){
3466       data.mode = MODE_Csv;
3467       memcpy(data.separator,",",2);
3468     }else if( strcmp(z,"-separator")==0 ){
3469       i++;
3470       if(i>=argc){
3471         fprintf(stderr,"%s: Error: missing argument for option: %s\n", Argv0, z);
3472         fprintf(stderr,"Use -help for a list of options.\n");
3473         return 1;
3474       }
3475       sqlite3_snprintf(sizeof(data.separator), data.separator,
3476                        "%.*s",(int)sizeof(data.separator)-1,argv[i]);
3477     }else if( strcmp(z,"-nullvalue")==0 ){
3478       i++;
3479       if(i>=argc){
3480         fprintf(stderr,"%s: Error: missing argument for option: %s\n", Argv0, z);
3481         fprintf(stderr,"Use -help for a list of options.\n");
3482         return 1;
3483       }
3484       sqlite3_snprintf(sizeof(data.nullvalue), data.nullvalue,
3485                        "%.*s",(int)sizeof(data.nullvalue)-1,argv[i]);
3486     }else if( strcmp(z,"-header")==0 ){
3487       data.showHeader = 1;
3488     }else if( strcmp(z,"-noheader")==0 ){
3489       data.showHeader = 0;
3490     }else if( strcmp(z,"-echo")==0 ){
3491       data.echoOn = 1;
3492     }else if( strcmp(z,"-bail")==0 ){
3493       bail_on_error = 1;
3494     }else if( strcmp(z,"-version")==0 ){
3495       printf("%s\n", sqlite3_libversion());
3496       return 0;
3497     }else if( strcmp(z,"-interactive")==0 ){
3498       stdin_is_interactive = 1;
3499     }else if( strcmp(z,"-batch")==0 ){
3500       stdin_is_interactive = 0;
3501     }else if( strcmp(z,"-help")==0 || strcmp(z, "--help")==0 ){
3502       usage(1);
3503     }else{
3504       fprintf(stderr,"%s: Error: unknown option: %s\n", Argv0, z);
3505       fprintf(stderr,"Use -help for a list of options.\n");
3506       return 1;
3507     }
3508   }
3509 
3510   if( zFirstCmd ){
3511     /* Run just the command that follows the database name
3512     */
3513     if( zFirstCmd[0]=='.' ){
3514       rc = do_meta_command(zFirstCmd, &data);
3515       return rc;
3516     }else{
3517       open_db(&data);
3518       rc = shell_exec(data.db, zFirstCmd, shell_callback, &data, &zErrMsg);
3519       if( zErrMsg!=0 ){
3520         fprintf(stderr,"Error: %s\n", zErrMsg);
3521         return rc!=0 ? rc : 1;
3522       }else if( rc!=0 ){
3523         fprintf(stderr,"Error: unable to process SQL \"%s\"\n", zFirstCmd);
3524         return rc;
3525       }
3526     }
3527   }else{
3528     /* Run commands received from standard input
3529     */
3530     if( stdin_is_interactive ){
3531       char *zHome;
3532       char *zHistory = 0;
3533       int nHistory;
3534       printf(
3535         "SQLite version %s\n"
3536         "Enter \".help\" for instructions\n"
3537         "Enter SQL statements terminated with a \";\"\n",
3538         sqlite3_libversion()
3539       );
3540       zHome = find_home_dir();
3541       if( zHome ){
3542         nHistory = strlen30(zHome) + 20;
3543         if( (zHistory = malloc(nHistory))!=0 ){
3544           sqlite3_snprintf(nHistory, zHistory,"%s/.sqlite_history", zHome);
3545         }
3546       }
3547 #if defined(HAVE_READLINE) && HAVE_READLINE==1
3548       if( zHistory ) read_history(zHistory);
3549 #endif
3550       rc = process_input(&data, 0);
3551       if( zHistory ){
3552         stifle_history(100);
3553         write_history(zHistory);
3554         free(zHistory);
3555       }
3556       free(zHome);
3557     }else{
3558       rc = process_input(&data, stdin);
3559     }
3560   }
3561   set_table_name(&data, 0);
3562   if( db ){
3563     if( sqlite3_close(db)!=SQLITE_OK ){
3564       fprintf(stderr,"Error: cannot close database \"%s\"\n", sqlite3_errmsg(db));
3565       rc++;
3566     }
3567   }
3568   return rc;
3569 }
3570