4 ** The author disclaims copyright to this source code. In place of
5 ** a legal notice, here is a blessing:
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.
11 *************************************************************************
12 ** This file contains code to implement the "sqlite" command line
13 ** utility for accessing SQLite databases.
15 #if (defined(_WIN32) || defined(WIN32)) && !defined(_CRT_SECURE_NO_WARNINGS)
16 /* This needs to come before any includes for MSVC compiler */
17 #define _CRT_SECURE_NO_WARNINGS
21 ** Warning pragmas copied from msvc.h in the core.
24 #pragma warning(disable : 4054)
25 #pragma warning(disable : 4055)
26 #pragma warning(disable : 4100)
27 #pragma warning(disable : 4127)
28 #pragma warning(disable : 4130)
29 #pragma warning(disable : 4152)
30 #pragma warning(disable : 4189)
31 #pragma warning(disable : 4206)
32 #pragma warning(disable : 4210)
33 #pragma warning(disable : 4232)
34 #pragma warning(disable : 4244)
35 #pragma warning(disable : 4305)
36 #pragma warning(disable : 4306)
37 #pragma warning(disable : 4702)
38 #pragma warning(disable : 4706)
39 #endif /* defined(_MSC_VER) */
42 ** No support for loadable extensions in VxWorks.
44 #if (defined(__RTP__) || defined(_WRS_KERNEL)) && !SQLITE_OMIT_LOAD_EXTENSION
45 # define SQLITE_OMIT_LOAD_EXTENSION 1
49 ** Enable large-file support for fopen() and friends on unix.
51 #ifndef SQLITE_DISABLE_LFS
52 # define _LARGE_FILE 1
53 # ifndef _FILE_OFFSET_BITS
54 # define _FILE_OFFSET_BITS 64
56 # define _LARGEFILE_SOURCE 1
64 typedef sqlite3_int64 i64
;
65 typedef sqlite3_uint64 u64
;
66 typedef unsigned char u8
;
67 #if SQLITE_USER_AUTHENTICATION
68 # include "sqlite3userauth.h"
73 #if !defined(_WIN32) && !defined(WIN32)
75 # if !defined(__RTP__) && !defined(_WRS_KERNEL)
79 #if (!defined(_WIN32) && !defined(WIN32)) || defined(__MINGW32__)
82 # if defined(__MINGW32__)
83 # define DIRENT dirent
85 # define S_ISLNK(mode) (0)
89 #include <sys/types.h>
93 # include <readline/readline.h>
94 # include <readline/history.h>
98 # include <editline/readline.h>
101 #if HAVE_EDITLINE || HAVE_READLINE
103 # define shell_add_history(X) add_history(X)
104 # define shell_read_history(X) read_history(X)
105 # define shell_write_history(X) write_history(X)
106 # define shell_stifle_history(X) stifle_history(X)
107 # define shell_readline(X) readline(X)
111 # include "linenoise.h"
112 # define shell_add_history(X) linenoiseHistoryAdd(X)
113 # define shell_read_history(X) linenoiseHistoryLoad(X)
114 # define shell_write_history(X) linenoiseHistorySave(X)
115 # define shell_stifle_history(X) linenoiseHistorySetMaxLen(X)
116 # define shell_readline(X) linenoise(X)
120 # define shell_read_history(X)
121 # define shell_write_history(X)
122 # define shell_stifle_history(X)
124 # define SHELL_USE_LOCAL_GETLINE 1
128 #if defined(_WIN32) || defined(WIN32)
131 # define isatty(h) _isatty(h)
133 # define access(f,m) _access((f),(m))
136 # define popen _popen
138 # define pclose _pclose
140 /* Make sure isatty() has a prototype. */
141 extern int isatty(int);
143 # if !defined(__RTP__) && !defined(_WRS_KERNEL)
144 /* popen and pclose are not C89 functions and so are
145 ** sometimes omitted from the <stdio.h> header */
146 extern FILE *popen(const char*,const char*);
147 extern int pclose(FILE*);
149 # define SQLITE_OMIT_POPEN 1
153 #if defined(_WIN32_WCE)
154 /* Windows CE (arm-wince-mingw32ce-gcc) does not provide isatty()
155 * thus we always assume that we have a console. That can be
156 * overridden with the -batch command line option.
161 /* ctype macros that work with signed characters */
162 #define IsSpace(X) isspace((unsigned char)X)
163 #define IsDigit(X) isdigit((unsigned char)X)
164 #define ToLower(X) (char)tolower((unsigned char)X)
166 #if defined(_WIN32) || defined(WIN32)
169 /* string conversion routines only needed on Win32 */
170 extern char *sqlite3_win32_unicode_to_utf8(LPCWSTR
);
171 extern char *sqlite3_win32_mbcs_to_utf8_v2(const char *, int);
172 extern char *sqlite3_win32_utf8_to_mbcs_v2(const char *, int);
173 extern LPWSTR
sqlite3_win32_utf8_to_unicode(const char *zText
);
176 /* On Windows, we normally run with output mode of TEXT so that \n characters
177 ** are automatically translated into \r\n. However, this behavior needs
178 ** to be disabled in some cases (ex: when generating CSV output and when
179 ** rendering quoted strings that contain \n characters). The following
180 ** routines take care of that.
182 #if defined(_WIN32) || defined(WIN32)
183 static void setBinaryMode(FILE *file
, int isOutput
){
184 if( isOutput
) fflush(file
);
185 _setmode(_fileno(file
), _O_BINARY
);
187 static void setTextMode(FILE *file
, int isOutput
){
188 if( isOutput
) fflush(file
);
189 _setmode(_fileno(file
), _O_TEXT
);
192 # define setBinaryMode(X,Y)
193 # define setTextMode(X,Y)
197 /* True if the timer is enabled */
198 static int enableTimer
= 0;
200 /* Return the current wall-clock time */
201 static sqlite3_int64
timeOfDay(void){
202 static sqlite3_vfs
*clockVfs
= 0;
204 if( clockVfs
==0 ) clockVfs
= sqlite3_vfs_find(0);
205 if( clockVfs
->iVersion
>=2 && clockVfs
->xCurrentTimeInt64
!=0 ){
206 clockVfs
->xCurrentTimeInt64(clockVfs
, &t
);
209 clockVfs
->xCurrentTime(clockVfs
, &r
);
210 t
= (sqlite3_int64
)(r
*86400000.0);
215 #if !defined(_WIN32) && !defined(WIN32) && !defined(__minux)
216 #include <sys/time.h>
217 #include <sys/resource.h>
219 /* VxWorks does not support getrusage() as far as we can determine */
220 #if defined(_WRS_KERNEL) || defined(__RTP__)
222 struct timeval ru_utime
; /* user CPU time used */
223 struct timeval ru_stime
; /* system CPU time used */
225 #define getrusage(A,B) memset(B,0,sizeof(*B))
228 /* Saved resource information for the beginning of an operation */
229 static struct rusage sBegin
; /* CPU time at start */
230 static sqlite3_int64 iBegin
; /* Wall-clock time at start */
233 ** Begin timing an operation
235 static void beginTimer(void){
237 getrusage(RUSAGE_SELF
, &sBegin
);
238 iBegin
= timeOfDay();
242 /* Return the difference of two time_structs in seconds */
243 static double timeDiff(struct timeval
*pStart
, struct timeval
*pEnd
){
244 return (pEnd
->tv_usec
- pStart
->tv_usec
)*0.000001 +
245 (double)(pEnd
->tv_sec
- pStart
->tv_sec
);
249 ** Print the timing results.
251 static void endTimer(void){
253 sqlite3_int64 iEnd
= timeOfDay();
255 getrusage(RUSAGE_SELF
, &sEnd
);
256 printf("Run Time: real %.3f user %f sys %f\n",
257 (iEnd
- iBegin
)*0.001,
258 timeDiff(&sBegin
.ru_utime
, &sEnd
.ru_utime
),
259 timeDiff(&sBegin
.ru_stime
, &sEnd
.ru_stime
));
263 #define BEGIN_TIMER beginTimer()
264 #define END_TIMER endTimer()
267 #elif (defined(_WIN32) || defined(WIN32))
269 /* Saved resource information for the beginning of an operation */
270 static HANDLE hProcess
;
271 static FILETIME ftKernelBegin
;
272 static FILETIME ftUserBegin
;
273 static sqlite3_int64 ftWallBegin
;
274 typedef BOOL (WINAPI
*GETPROCTIMES
)(HANDLE
, LPFILETIME
, LPFILETIME
,
275 LPFILETIME
, LPFILETIME
);
276 static GETPROCTIMES getProcessTimesAddr
= NULL
;
279 ** Check to see if we have timer support. Return 1 if necessary
280 ** support found (or found previously).
282 static int hasTimer(void){
283 if( getProcessTimesAddr
){
286 /* GetProcessTimes() isn't supported in WIN95 and some other Windows
287 ** versions. See if the version we are running on has it, and if it
288 ** does, save off a pointer to it and the current process handle.
290 hProcess
= GetCurrentProcess();
292 HINSTANCE hinstLib
= LoadLibrary(TEXT("Kernel32.dll"));
293 if( NULL
!= hinstLib
){
294 getProcessTimesAddr
=
295 (GETPROCTIMES
) GetProcAddress(hinstLib
, "GetProcessTimes");
296 if( NULL
!= getProcessTimesAddr
){
299 FreeLibrary(hinstLib
);
307 ** Begin timing an operation
309 static void beginTimer(void){
310 if( enableTimer
&& getProcessTimesAddr
){
311 FILETIME ftCreation
, ftExit
;
312 getProcessTimesAddr(hProcess
,&ftCreation
,&ftExit
,
313 &ftKernelBegin
,&ftUserBegin
);
314 ftWallBegin
= timeOfDay();
318 /* Return the difference of two FILETIME structs in seconds */
319 static double timeDiff(FILETIME
*pStart
, FILETIME
*pEnd
){
320 sqlite_int64 i64Start
= *((sqlite_int64
*) pStart
);
321 sqlite_int64 i64End
= *((sqlite_int64
*) pEnd
);
322 return (double) ((i64End
- i64Start
) / 10000000.0);
326 ** Print the timing results.
328 static void endTimer(void){
329 if( enableTimer
&& getProcessTimesAddr
){
330 FILETIME ftCreation
, ftExit
, ftKernelEnd
, ftUserEnd
;
331 sqlite3_int64 ftWallEnd
= timeOfDay();
332 getProcessTimesAddr(hProcess
,&ftCreation
,&ftExit
,&ftKernelEnd
,&ftUserEnd
);
333 printf("Run Time: real %.3f user %f sys %f\n",
334 (ftWallEnd
- ftWallBegin
)*0.001,
335 timeDiff(&ftUserBegin
, &ftUserEnd
),
336 timeDiff(&ftKernelBegin
, &ftKernelEnd
));
340 #define BEGIN_TIMER beginTimer()
341 #define END_TIMER endTimer()
342 #define HAS_TIMER hasTimer()
351 ** Used to prevent warnings about unused parameters
353 #define UNUSED_PARAMETER(x) (void)(x)
356 ** Number of elements in an array
358 #define ArraySize(X) (int)(sizeof(X)/sizeof(X[0]))
361 ** If the following flag is set, then command execution stops
362 ** at an error if we are not interactive.
364 static int bail_on_error
= 0;
367 ** Threat stdin as an interactive input if the following variable
368 ** is true. Otherwise, assume stdin is connected to a file or pipe.
370 static int stdin_is_interactive
= 1;
373 ** On Windows systems we have to know if standard output is a console
374 ** in order to translate UTF-8 into MBCS. The following variable is
375 ** true if translation is required.
377 static int stdout_is_console
= 1;
380 ** The following is the open SQLite database. We make a pointer
381 ** to this database a static variable so that it can be accessed
382 ** by the SIGINT handler to interrupt database processing.
384 static sqlite3
*globalDb
= 0;
387 ** True if an interrupt (Control-C) has been received.
389 static volatile int seenInterrupt
= 0;
392 ** This is the name of our program. It is set in main(), used
393 ** in a number of other places, mostly for error messages.
398 ** Prompt strings. Initialized in main. Settable with
399 ** .prompt main continue
401 static char mainPrompt
[20]; /* First line prompt. default: "sqlite> "*/
402 static char continuePrompt
[20]; /* Continuation prompt. default: " ...> " */
405 ** Render output like fprintf(). Except, if the output is going to the
406 ** console and if this is running on a Windows machine, translate the
407 ** output from UTF-8 into MBCS.
409 #if defined(_WIN32) || defined(WIN32)
410 void utf8_printf(FILE *out
, const char *zFormat
, ...){
412 va_start(ap
, zFormat
);
413 if( stdout_is_console
&& (out
==stdout
|| out
==stderr
) ){
414 char *z1
= sqlite3_vmprintf(zFormat
, ap
);
415 char *z2
= sqlite3_win32_utf8_to_mbcs_v2(z1
, 0);
420 vfprintf(out
, zFormat
, ap
);
424 #elif !defined(utf8_printf)
425 # define utf8_printf fprintf
429 ** Render output like fprintf(). This should not be used on anything that
430 ** includes string formatting (e.g. "%s").
432 #if !defined(raw_printf)
433 # define raw_printf fprintf
437 ** Write I/O traces to the following stream.
439 #ifdef SQLITE_ENABLE_IOTRACE
440 static FILE *iotrace
= 0;
444 ** This routine works like printf in that its first argument is a
445 ** format string and subsequent arguments are values to be substituted
446 ** in place of % fields. The result of formatting this string
447 ** is written to iotrace.
449 #ifdef SQLITE_ENABLE_IOTRACE
450 static void SQLITE_CDECL
iotracePrintf(const char *zFormat
, ...){
453 if( iotrace
==0 ) return;
454 va_start(ap
, zFormat
);
455 z
= sqlite3_vmprintf(zFormat
, ap
);
457 utf8_printf(iotrace
, "%s", z
);
463 ** Output string zUtf to stream pOut as w characters. If w is negative,
464 ** then right-justify the text. W is the width in UTF-8 characters, not
465 ** in bytes. This is different from the %*.*s specification in printf
466 ** since with %*.*s the width is measured in bytes, not characters.
468 static void utf8_width_print(FILE *pOut
, int w
, const char *zUtf
){
471 int aw
= w
<0 ? -w
: w
;
473 if( aw
>(int)sizeof(zBuf
)/3 ) aw
= (int)sizeof(zBuf
)/3;
474 for(i
=n
=0; zUtf
[i
]; i
++){
475 if( (zUtf
[i
]&0xc0)!=0x80 ){
478 do{ i
++; }while( (zUtf
[i
]&0xc0)==0x80 );
484 utf8_printf(pOut
, "%.*s", i
, zUtf
);
486 utf8_printf(pOut
, "%*s%s", aw
-n
, "", zUtf
);
488 utf8_printf(pOut
, "%s%*s", zUtf
, aw
-n
, "");
494 ** Determines if a string is a number of not.
496 static int isNumber(const char *z
, int *realnum
){
497 if( *z
=='-' || *z
=='+' ) z
++;
502 if( realnum
) *realnum
= 0;
503 while( IsDigit(*z
) ){ z
++; }
506 if( !IsDigit(*z
) ) return 0;
507 while( IsDigit(*z
) ){ z
++; }
508 if( realnum
) *realnum
= 1;
510 if( *z
=='e' || *z
=='E' ){
512 if( *z
=='+' || *z
=='-' ) z
++;
513 if( !IsDigit(*z
) ) return 0;
514 while( IsDigit(*z
) ){ z
++; }
515 if( realnum
) *realnum
= 1;
521 ** Compute a string length that is limited to what can be stored in
522 ** lower 30 bits of a 32-bit signed integer.
524 static int strlen30(const char *z
){
526 while( *z2
){ z2
++; }
527 return 0x3fffffff & (int)(z2
- z
);
531 ** Return the length of a string in characters. Multibyte UTF8 characters
532 ** count as a single character.
534 static int strlenChar(const char *z
){
537 if( (0xc0&*(z
++))!=0x80 ) n
++;
543 ** This routine reads a line of text from FILE in, stores
544 ** the text in memory obtained from malloc() and returns a pointer
545 ** to the text. NULL is returned at end of file, or if malloc()
548 ** If zLine is not NULL then it is a malloced buffer returned from
549 ** a previous call to this routine that may be reused.
551 static char *local_getline(char *zLine
, FILE *in
){
552 int nLine
= zLine
==0 ? 0 : 100;
557 nLine
= nLine
*2 + 100;
558 zLine
= realloc(zLine
, nLine
);
559 if( zLine
==0 ) return 0;
561 if( fgets(&zLine
[n
], nLine
- n
, in
)==0 ){
569 while( zLine
[n
] ) n
++;
570 if( n
>0 && zLine
[n
-1]=='\n' ){
572 if( n
>0 && zLine
[n
-1]=='\r' ) n
--;
577 #if defined(_WIN32) || defined(WIN32)
578 /* For interactive input on Windows systems, translate the
579 ** multi-byte characterset characters into UTF-8. */
580 if( stdin_is_interactive
&& in
==stdin
){
581 char *zTrans
= sqlite3_win32_mbcs_to_utf8_v2(zLine
, 0);
583 int nTrans
= strlen30(zTrans
)+1;
585 zLine
= realloc(zLine
, nTrans
);
587 sqlite3_free(zTrans
);
591 memcpy(zLine
, zTrans
, nTrans
);
592 sqlite3_free(zTrans
);
595 #endif /* defined(_WIN32) || defined(WIN32) */
600 ** Retrieve a single line of input text.
602 ** If in==0 then read from standard input and prompt before each line.
603 ** If isContinuation is true, then a continuation prompt is appropriate.
604 ** If isContinuation is zero, then the main prompt should be used.
606 ** If zPrior is not NULL then it is a buffer from a prior call to this
607 ** routine that can be reused.
609 ** The result is stored in space obtained from malloc() and must either
610 ** be freed by the caller or else passed back into this routine via the
611 ** zPrior argument for reuse.
613 static char *one_input_line(FILE *in
, char *zPrior
, int isContinuation
){
617 zResult
= local_getline(zPrior
, in
);
619 zPrompt
= isContinuation
? continuePrompt
: mainPrompt
;
620 #if SHELL_USE_LOCAL_GETLINE
621 printf("%s", zPrompt
);
623 zResult
= local_getline(zPrior
, stdin
);
626 zResult
= shell_readline(zPrompt
);
627 if( zResult
&& *zResult
) shell_add_history(zResult
);
635 ** Return the value of a hexadecimal digit. Return -1 if the input
636 ** is not a hex digit.
638 static int hexDigitValue(char c
){
639 if( c
>='0' && c
<='9' ) return c
- '0';
640 if( c
>='a' && c
<='f' ) return c
- 'a' + 10;
641 if( c
>='A' && c
<='F' ) return c
- 'A' + 10;
646 ** Interpret zArg as an integer value, possibly with suffixes.
648 static sqlite3_int64
integerValue(const char *zArg
){
650 static const struct { char *zSuffix
; int iMult
; } aMult
[] = {
652 { "MiB", 1024*1024 },
653 { "GiB", 1024*1024*1024 },
656 { "GB", 1000000000 },
666 }else if( zArg
[0]=='+' ){
669 if( zArg
[0]=='0' && zArg
[1]=='x' ){
672 while( (x
= hexDigitValue(zArg
[0]))>=0 ){
677 while( IsDigit(zArg
[0]) ){
678 v
= v
*10 + zArg
[0] - '0';
682 for(i
=0; i
<ArraySize(aMult
); i
++){
683 if( sqlite3_stricmp(aMult
[i
].zSuffix
, zArg
)==0 ){
688 return isNeg
? -v
: v
;
692 ** A variable length string to which one can append text.
694 typedef struct ShellText ShellText
;
702 ** Initialize and destroy a ShellText object
704 static void initText(ShellText
*p
){
705 memset(p
, 0, sizeof(*p
));
707 static void freeText(ShellText
*p
){
712 /* zIn is either a pointer to a NULL-terminated string in memory obtained
713 ** from malloc(), or a NULL pointer. The string pointed to by zAppend is
714 ** added to zIn, and the result returned in memory obtained from malloc().
715 ** zIn, if it was not NULL, is freed.
717 ** If the third argument, quote, is not '\0', then it is used as a
718 ** quote character for zAppend.
720 static void appendText(ShellText
*p
, char const *zAppend
, char quote
){
723 int nAppend
= strlen30(zAppend
);
725 len
= nAppend
+p
->n
+1;
728 for(i
=0; i
<nAppend
; i
++){
729 if( zAppend
[i
]==quote
) len
++;
733 if( p
->n
+len
>=p
->nAlloc
){
734 p
->nAlloc
= p
->nAlloc
*2 + len
+ 20;
735 p
->z
= realloc(p
->z
, p
->nAlloc
);
737 memset(p
, 0, sizeof(*p
));
743 char *zCsr
= p
->z
+p
->n
;
745 for(i
=0; i
<nAppend
; i
++){
746 *zCsr
++ = zAppend
[i
];
747 if( zAppend
[i
]==quote
) *zCsr
++ = quote
;
750 p
->n
= (int)(zCsr
- p
->z
);
753 memcpy(p
->z
+p
->n
, zAppend
, nAppend
);
760 ** Attempt to determine if identifier zName needs to be quoted, either
761 ** because it contains non-alphanumeric characters, or because it is an
762 ** SQLite keyword. Be conservative in this estimate: When in doubt assume
763 ** that quoting is required.
765 ** Return '"' if quoting is required. Return 0 if no quoting is required.
767 static char quoteChar(const char *zName
){
768 /* All SQLite keywords, in alphabetical order */
769 static const char *azKeywords
[] = {
770 "ABORT", "ACTION", "ADD", "AFTER", "ALL", "ALTER", "ANALYZE", "AND", "AS",
771 "ASC", "ATTACH", "AUTOINCREMENT", "BEFORE", "BEGIN", "BETWEEN", "BY",
772 "CASCADE", "CASE", "CAST", "CHECK", "COLLATE", "COLUMN", "COMMIT",
773 "CONFLICT", "CONSTRAINT", "CREATE", "CROSS", "CURRENT_DATE",
774 "CURRENT_TIME", "CURRENT_TIMESTAMP", "DATABASE", "DEFAULT", "DEFERRABLE",
775 "DEFERRED", "DELETE", "DESC", "DETACH", "DISTINCT", "DROP", "EACH",
776 "ELSE", "END", "ESCAPE", "EXCEPT", "EXCLUSIVE", "EXISTS", "EXPLAIN",
777 "FAIL", "FOR", "FOREIGN", "FROM", "FULL", "GLOB", "GROUP", "HAVING", "IF",
778 "IGNORE", "IMMEDIATE", "IN", "INDEX", "INDEXED", "INITIALLY", "INNER",
779 "INSERT", "INSTEAD", "INTERSECT", "INTO", "IS", "ISNULL", "JOIN", "KEY",
780 "LEFT", "LIKE", "LIMIT", "MATCH", "NATURAL", "NO", "NOT", "NOTNULL",
781 "NULL", "OF", "OFFSET", "ON", "OR", "ORDER", "OUTER", "PLAN", "PRAGMA",
782 "PRIMARY", "QUERY", "RAISE", "RECURSIVE", "REFERENCES", "REGEXP",
783 "REINDEX", "RELEASE", "RENAME", "REPLACE", "RESTRICT", "RIGHT",
784 "ROLLBACK", "ROW", "SAVEPOINT", "SELECT", "SET", "TABLE", "TEMP",
785 "TEMPORARY", "THEN", "TO", "TRANSACTION", "TRIGGER", "UNION", "UNIQUE",
786 "UPDATE", "USING", "VACUUM", "VALUES", "VIEW", "VIRTUAL", "WHEN", "WHERE",
789 int i
, lwr
, upr
, mid
, c
;
790 if( !isalpha((unsigned char)zName
[0]) && zName
[0]!='_' ) return '"';
791 for(i
=0; zName
[i
]; i
++){
792 if( !isalnum((unsigned char)zName
[i
]) && zName
[i
]!='_' ) return '"';
795 upr
= sizeof(azKeywords
)/sizeof(azKeywords
[0]) - 1;
798 c
= sqlite3_stricmp(azKeywords
[mid
], zName
);
799 if( c
==0 ) return '"';
810 ** Construct a fake object name and column list to describe the structure
811 ** of the view, virtual table, or table valued function zSchema.zName.
813 static char *shellFakeSchema(
814 sqlite3
*db
, /* The database connection containing the vtab */
815 const char *zSchema
, /* Schema of the database holding the vtab */
816 const char *zName
/* The name of the virtual table */
818 sqlite3_stmt
*pStmt
= 0;
825 zSql
= sqlite3_mprintf("PRAGMA \"%w\".table_info=%Q;",
826 zSchema
? zSchema
: "main", zName
);
827 sqlite3_prepare_v2(db
, zSql
, -1, &pStmt
, 0);
831 cQuote
= quoteChar(zSchema
);
832 if( cQuote
&& sqlite3_stricmp(zSchema
,"temp")==0 ) cQuote
= 0;
833 appendText(&s
, zSchema
, cQuote
);
834 appendText(&s
, ".", 0);
836 cQuote
= quoteChar(zName
);
837 appendText(&s
, zName
, cQuote
);
838 while( sqlite3_step(pStmt
)==SQLITE_ROW
){
839 const char *zCol
= (const char*)sqlite3_column_text(pStmt
, 1);
841 appendText(&s
, zDiv
, 0);
843 cQuote
= quoteChar(zCol
);
844 appendText(&s
, zCol
, cQuote
);
846 appendText(&s
, ")", 0);
847 sqlite3_finalize(pStmt
);
856 ** SQL function: shell_module_schema(X)
858 ** Return a fake schema for the table-valued function or eponymous virtual
861 static void shellModuleSchema(
862 sqlite3_context
*pCtx
,
864 sqlite3_value
**apVal
866 const char *zName
= (const char*)sqlite3_value_text(apVal
[0]);
867 char *zFake
= shellFakeSchema(sqlite3_context_db_handle(pCtx
), 0, zName
);
869 sqlite3_result_text(pCtx
, sqlite3_mprintf("/* %s */", zFake
),
876 ** SQL function: shell_add_schema(S,X)
878 ** Add the schema name X to the CREATE statement in S and return the result.
881 ** CREATE TABLE t1(x) -> CREATE TABLE xyz.t1(x);
886 ** CREATE UNIQUE INDEX
889 ** CREATE VIRTUAL TABLE
891 ** This UDF is used by the .schema command to insert the schema name of
892 ** attached databases into the middle of the sqlite_master.sql field.
894 static void shellAddSchemaName(
895 sqlite3_context
*pCtx
,
897 sqlite3_value
**apVal
899 static const char *aPrefix
[] = {
908 const char *zIn
= (const char*)sqlite3_value_text(apVal
[0]);
909 const char *zSchema
= (const char*)sqlite3_value_text(apVal
[1]);
910 const char *zName
= (const char*)sqlite3_value_text(apVal
[2]);
911 sqlite3
*db
= sqlite3_context_db_handle(pCtx
);
912 if( zIn
!=0 && strncmp(zIn
, "CREATE ", 7)==0 ){
913 for(i
=0; i
<(int)(sizeof(aPrefix
)/sizeof(aPrefix
[0])); i
++){
914 int n
= strlen30(aPrefix
[i
]);
915 if( strncmp(zIn
+7, aPrefix
[i
], n
)==0 && zIn
[n
+7]==' ' ){
919 char cQuote
= quoteChar(zSchema
);
920 if( cQuote
&& sqlite3_stricmp(zSchema
,"temp")!=0 ){
921 z
= sqlite3_mprintf("%.*s \"%w\".%s", n
+7, zIn
, zSchema
, zIn
+n
+8);
923 z
= sqlite3_mprintf("%.*s %s.%s", n
+7, zIn
, zSchema
, zIn
+n
+8);
927 && aPrefix
[i
][0]=='V'
928 && (zFake
= shellFakeSchema(db
, zSchema
, zName
))!=0
931 z
= sqlite3_mprintf("%s\n/* %s */", zIn
, zFake
);
933 z
= sqlite3_mprintf("%z\n/* %s */", z
, zFake
);
938 sqlite3_result_text(pCtx
, z
, -1, sqlite3_free
);
944 sqlite3_result_value(pCtx
, apVal
[0]);
948 ** The source code for several run-time loadable extensions is inserted
949 ** below by the ../tool/mkshellc.tcl script. Before processing that included
950 ** code, we need to override some macros to make the included program code
951 ** work here in the middle of this regular program.
953 #define SQLITE_EXTENSION_INIT1
954 #define SQLITE_EXTENSION_INIT2(X) (void)(X)
956 #if defined(_WIN32) && defined(_MSC_VER)
957 INCLUDE test_windirent
.h
958 INCLUDE test_windirent
.c
959 #define dirent DIRENT
961 INCLUDE
../ext
/misc
/shathree
.c
962 INCLUDE
../ext
/misc
/fileio
.c
963 INCLUDE
../ext
/misc
/completion
.c
964 INCLUDE
../ext
/misc
/appendvfs
.c
965 #ifdef SQLITE_HAVE_ZLIB
966 INCLUDE
../ext
/misc
/zipfile
.c
967 INCLUDE
../ext
/misc
/sqlar
.c
969 INCLUDE
../ext
/expert
/sqlite3expert
.h
970 INCLUDE
../ext
/expert
/sqlite3expert
.c
972 #if defined(SQLITE_ENABLE_SESSION)
974 ** State information for a single open session
976 typedef struct OpenSession OpenSession
;
978 char *zName
; /* Symbolic name for this session */
979 int nFilter
; /* Number of xFilter rejection GLOB patterns */
980 char **azFilter
; /* Array of xFilter rejection GLOB patterns */
981 sqlite3_session
*p
; /* The open session */
986 ** Shell output mode information from before ".explain on",
987 ** saved so that it can be restored by ".explain off"
989 typedef struct SavedModeInfo SavedModeInfo
;
990 struct SavedModeInfo
{
991 int valid
; /* Is there legit data in here? */
992 int mode
; /* Mode prior to ".explain on" */
993 int showHeader
; /* The ".header" setting prior to ".explain on" */
994 int colWidth
[100]; /* Column widths prior to ".explain on" */
997 typedef struct ExpertInfo ExpertInfo
;
999 sqlite3expert
*pExpert
;
1004 ** State information about the database connection is contained in an
1005 ** instance of the following structure.
1007 typedef struct ShellState ShellState
;
1009 sqlite3
*db
; /* The database */
1010 u8 autoExplain
; /* Automatically turn on .explain mode */
1011 u8 autoEQP
; /* Run EXPLAIN QUERY PLAN prior to seach SQL stmt */
1012 u8 statsOn
; /* True to display memory stats before each finalize */
1013 u8 scanstatsOn
; /* True to display scan stats before each finalize */
1014 u8 openMode
; /* SHELL_OPEN_NORMAL, _APPENDVFS, or _ZIPFILE */
1015 int outCount
; /* Revert to stdout when reaching zero */
1016 int cnt
; /* Number of records displayed so far */
1017 FILE *out
; /* Write results here */
1018 FILE *traceOut
; /* Output for sqlite3_trace() */
1019 int nErr
; /* Number of errors seen */
1020 int mode
; /* An output mode setting */
1021 int cMode
; /* temporary output mode for the current query */
1022 int normalMode
; /* Output mode before ".explain on" */
1023 int writableSchema
; /* True if PRAGMA writable_schema=ON */
1024 int showHeader
; /* True to show column names in List or Column mode */
1025 int nCheck
; /* Number of ".check" commands run */
1026 unsigned shellFlgs
; /* Various flags */
1027 char *zDestTable
; /* Name of destination table when MODE_Insert */
1028 char zTestcase
[30]; /* Name of current test case */
1029 char colSeparator
[20]; /* Column separator character for several modes */
1030 char rowSeparator
[20]; /* Row separator character for MODE_Ascii */
1031 int colWidth
[100]; /* Requested width of each column when in column mode*/
1032 int actualWidth
[100]; /* Actual width of each column */
1033 char nullValue
[20]; /* The text to print when a NULL comes back from
1035 char outfile
[FILENAME_MAX
]; /* Filename for *out */
1036 const char *zDbFilename
; /* name of the database file */
1037 char *zFreeOnClose
; /* Filename to free when closing */
1038 const char *zVfs
; /* Name of VFS to use */
1039 sqlite3_stmt
*pStmt
; /* Current statement if any. */
1040 FILE *pLog
; /* Write log output here */
1041 int *aiIndent
; /* Array of indents used in MODE_Explain */
1042 int nIndent
; /* Size of array aiIndent[] */
1043 int iIndent
; /* Index of current op in aiIndent[] */
1044 #if defined(SQLITE_ENABLE_SESSION)
1045 int nSession
; /* Number of active sessions */
1046 OpenSession aSession
[4]; /* Array of sessions. [0] is in focus. */
1048 ExpertInfo expert
; /* Valid if previous command was ".expert OPT..." */
1052 /* Allowed values for ShellState.autoEQP
1054 #define AUTOEQP_off 0
1055 #define AUTOEQP_on 1
1056 #define AUTOEQP_trigger 2
1057 #define AUTOEQP_full 3
1059 /* Allowed values for ShellState.openMode
1061 #define SHELL_OPEN_UNSPEC 0 /* No open-mode specified */
1062 #define SHELL_OPEN_NORMAL 1 /* Normal database file */
1063 #define SHELL_OPEN_APPENDVFS 2 /* Use appendvfs */
1064 #define SHELL_OPEN_ZIPFILE 3 /* Use the zipfile virtual table */
1067 ** These are the allowed shellFlgs values
1069 #define SHFLG_Pagecache 0x00000001 /* The --pagecache option is used */
1070 #define SHFLG_Lookaside 0x00000002 /* Lookaside memory is used */
1071 #define SHFLG_Backslash 0x00000004 /* The --backslash option is used */
1072 #define SHFLG_PreserveRowid 0x00000008 /* .dump preserves rowid values */
1073 #define SHFLG_Newlines 0x00000010 /* .dump --newline flag */
1074 #define SHFLG_CountChanges 0x00000020 /* .changes setting */
1075 #define SHFLG_Echo 0x00000040 /* .echo or --echo setting */
1078 ** Macros for testing and setting shellFlgs
1080 #define ShellHasFlag(P,X) (((P)->shellFlgs & (X))!=0)
1081 #define ShellSetFlag(P,X) ((P)->shellFlgs|=(X))
1082 #define ShellClearFlag(P,X) ((P)->shellFlgs&=(~(X)))
1085 ** These are the allowed modes.
1087 #define MODE_Line 0 /* One column per line. Blank line between records */
1088 #define MODE_Column 1 /* One record per line in neat columns */
1089 #define MODE_List 2 /* One record per line with a separator */
1090 #define MODE_Semi 3 /* Same as MODE_List but append ";" to each line */
1091 #define MODE_Html 4 /* Generate an XHTML table */
1092 #define MODE_Insert 5 /* Generate SQL "insert" statements */
1093 #define MODE_Quote 6 /* Quote values as for SQL */
1094 #define MODE_Tcl 7 /* Generate ANSI-C or TCL quoted elements */
1095 #define MODE_Csv 8 /* Quote strings, numbers are plain */
1096 #define MODE_Explain 9 /* Like MODE_Column, but do not truncate data */
1097 #define MODE_Ascii 10 /* Use ASCII unit and record separators (0x1F/0x1E) */
1098 #define MODE_Pretty 11 /* Pretty-print schemas */
1100 static const char *modeDescr
[] = {
1116 ** These are the column/row/line separators used by the various
1117 ** import/export modes.
1119 #define SEP_Column "|"
1120 #define SEP_Row "\n"
1121 #define SEP_Tab "\t"
1122 #define SEP_Space " "
1123 #define SEP_Comma ","
1124 #define SEP_CrLf "\r\n"
1125 #define SEP_Unit "\x1F"
1126 #define SEP_Record "\x1E"
1129 ** A callback for the sqlite3_log() interface.
1131 static void shellLog(void *pArg
, int iErrCode
, const char *zMsg
){
1132 ShellState
*p
= (ShellState
*)pArg
;
1133 if( p
->pLog
==0 ) return;
1134 utf8_printf(p
->pLog
, "(%d) %s\n", iErrCode
, zMsg
);
1139 ** Output the given string as a hex-encoded blob (eg. X'1234' )
1141 static void output_hex_blob(FILE *out
, const void *pBlob
, int nBlob
){
1143 char *zBlob
= (char *)pBlob
;
1144 raw_printf(out
,"X'");
1145 for(i
=0; i
<nBlob
; i
++){ raw_printf(out
,"%02x",zBlob
[i
]&0xff); }
1146 raw_printf(out
,"'");
1150 ** Find a string that is not found anywhere in z[]. Return a pointer
1153 ** Try to use zA and zB first. If both of those are already found in z[]
1154 ** then make up some string and store it in the buffer zBuf.
1156 static const char *unused_string(
1157 const char *z
, /* Result must not appear anywhere in z */
1158 const char *zA
, const char *zB
, /* Try these first */
1159 char *zBuf
/* Space to store a generated string */
1162 if( strstr(z
, zA
)==0 ) return zA
;
1163 if( strstr(z
, zB
)==0 ) return zB
;
1165 sqlite3_snprintf(20,zBuf
,"(%s%u)", zA
, i
++);
1166 }while( strstr(z
,zBuf
)!=0 );
1171 ** Output the given string as a quoted string using SQL quoting conventions.
1173 ** See also: output_quoted_escaped_string()
1175 static void output_quoted_string(FILE *out
, const char *z
){
1178 setBinaryMode(out
, 1);
1179 for(i
=0; (c
= z
[i
])!=0 && c
!='\''; i
++){}
1181 utf8_printf(out
,"'%s'",z
);
1183 raw_printf(out
, "'");
1185 for(i
=0; (c
= z
[i
])!=0 && c
!='\''; i
++){}
1188 utf8_printf(out
, "%.*s", i
, z
);
1192 raw_printf(out
, "'");
1200 raw_printf(out
, "'");
1202 setTextMode(out
, 1);
1206 ** Output the given string as a quoted string using SQL quoting conventions.
1207 ** Additionallly , escape the "\n" and "\r" characters so that they do not
1208 ** get corrupted by end-of-line translation facilities in some operating
1211 ** This is like output_quoted_string() but with the addition of the \r\n
1212 ** escape mechanism.
1214 static void output_quoted_escaped_string(FILE *out
, const char *z
){
1217 setBinaryMode(out
, 1);
1218 for(i
=0; (c
= z
[i
])!=0 && c
!='\'' && c
!='\n' && c
!='\r'; i
++){}
1220 utf8_printf(out
,"'%s'",z
);
1222 const char *zNL
= 0;
1223 const char *zCR
= 0;
1226 char zBuf1
[20], zBuf2
[20];
1227 for(i
=0; z
[i
]; i
++){
1228 if( z
[i
]=='\n' ) nNL
++;
1229 if( z
[i
]=='\r' ) nCR
++;
1232 raw_printf(out
, "replace(");
1233 zNL
= unused_string(z
, "\\n", "\\012", zBuf1
);
1236 raw_printf(out
, "replace(");
1237 zCR
= unused_string(z
, "\\r", "\\015", zBuf2
);
1239 raw_printf(out
, "'");
1241 for(i
=0; (c
= z
[i
])!=0 && c
!='\n' && c
!='\r' && c
!='\''; i
++){}
1244 utf8_printf(out
, "%.*s", i
, z
);
1248 raw_printf(out
, "'");
1256 raw_printf(out
, "%s", zNL
);
1259 raw_printf(out
, "%s", zCR
);
1261 raw_printf(out
, "'");
1263 raw_printf(out
, ",'%s',char(13))", zCR
);
1266 raw_printf(out
, ",'%s',char(10))", zNL
);
1269 setTextMode(out
, 1);
1273 ** Output the given string as a quoted according to C or TCL quoting rules.
1275 static void output_c_string(FILE *out
, const char *z
){
1278 while( (c
= *(z
++))!=0 ){
1285 }else if( c
=='\t' ){
1288 }else if( c
=='\n' ){
1291 }else if( c
=='\r' ){
1294 }else if( !isprint(c
&0xff) ){
1295 raw_printf(out
, "\\%03o", c
&0xff);
1304 ** Output the given string with characters that are special to
1307 static void output_html_string(FILE *out
, const char *z
){
1319 utf8_printf(out
,"%.*s",i
,z
);
1322 raw_printf(out
,"<");
1323 }else if( z
[i
]=='&' ){
1324 raw_printf(out
,"&");
1325 }else if( z
[i
]=='>' ){
1326 raw_printf(out
,">");
1327 }else if( z
[i
]=='\"' ){
1328 raw_printf(out
,""");
1329 }else if( z
[i
]=='\'' ){
1330 raw_printf(out
,"'");
1339 ** If a field contains any character identified by a 1 in the following
1340 ** array, then the string must be quoted for CSV.
1342 static const char needCsvQuote
[] = {
1343 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1344 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1345 1, 0, 1, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0,
1346 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
1347 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
1348 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
1349 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
1350 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1,
1351 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1352 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1353 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1354 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1355 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1356 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1357 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1358 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1362 ** Output a single term of CSV. Actually, p->colSeparator is used for
1363 ** the separator, which may or may not be a comma. p->nullValue is
1364 ** the null value. Strings are quoted if necessary. The separator
1365 ** is only issued if bSep is true.
1367 static void output_csv(ShellState
*p
, const char *z
, int bSep
){
1370 utf8_printf(out
,"%s",p
->nullValue
);
1373 int nSep
= strlen30(p
->colSeparator
);
1374 for(i
=0; z
[i
]; i
++){
1375 if( needCsvQuote
[((unsigned char*)z
)[i
]]
1376 || (z
[i
]==p
->colSeparator
[0] &&
1377 (nSep
==1 || memcmp(z
, p
->colSeparator
, nSep
)==0)) ){
1383 char *zQuoted
= sqlite3_mprintf("\"%w\"", z
);
1384 utf8_printf(out
, "%s", zQuoted
);
1385 sqlite3_free(zQuoted
);
1387 utf8_printf(out
, "%s", z
);
1391 utf8_printf(p
->out
, "%s", p
->colSeparator
);
1396 ** This routine runs when the user presses Ctrl-C
1398 static void interrupt_handler(int NotUsed
){
1399 UNUSED_PARAMETER(NotUsed
);
1401 if( seenInterrupt
>2 ) exit(1);
1402 if( globalDb
) sqlite3_interrupt(globalDb
);
1405 #if (defined(_WIN32) || defined(WIN32)) && !defined(_WIN32_WCE)
1407 ** This routine runs for console events (e.g. Ctrl-C) on Win32
1409 static BOOL WINAPI
ConsoleCtrlHandler(
1410 DWORD dwCtrlType
/* One of the CTRL_*_EVENT constants */
1412 if( dwCtrlType
==CTRL_C_EVENT
){
1413 interrupt_handler(0);
1420 #ifndef SQLITE_OMIT_AUTHORIZATION
1422 ** When the ".auth ON" is set, the following authorizer callback is
1423 ** invoked. It always returns SQLITE_OK.
1425 static int shellAuth(
1433 ShellState
*p
= (ShellState
*)pClientData
;
1434 static const char *azAction
[] = { 0,
1435 "CREATE_INDEX", "CREATE_TABLE", "CREATE_TEMP_INDEX",
1436 "CREATE_TEMP_TABLE", "CREATE_TEMP_TRIGGER", "CREATE_TEMP_VIEW",
1437 "CREATE_TRIGGER", "CREATE_VIEW", "DELETE",
1438 "DROP_INDEX", "DROP_TABLE", "DROP_TEMP_INDEX",
1439 "DROP_TEMP_TABLE", "DROP_TEMP_TRIGGER", "DROP_TEMP_VIEW",
1440 "DROP_TRIGGER", "DROP_VIEW", "INSERT",
1441 "PRAGMA", "READ", "SELECT",
1442 "TRANSACTION", "UPDATE", "ATTACH",
1443 "DETACH", "ALTER_TABLE", "REINDEX",
1444 "ANALYZE", "CREATE_VTABLE", "DROP_VTABLE",
1445 "FUNCTION", "SAVEPOINT", "RECURSIVE"
1453 utf8_printf(p
->out
, "authorizer: %s", azAction
[op
]);
1455 raw_printf(p
->out
, " ");
1457 output_c_string(p
->out
, az
[i
]);
1459 raw_printf(p
->out
, "NULL");
1462 raw_printf(p
->out
, "\n");
1468 ** Print a schema statement. Part of MODE_Semi and MODE_Pretty output.
1470 ** This routine converts some CREATE TABLE statements for shadow tables
1471 ** in FTS3/4/5 into CREATE TABLE IF NOT EXISTS statements.
1473 static void printSchemaLine(FILE *out
, const char *z
, const char *zTail
){
1474 if( sqlite3_strglob("CREATE TABLE ['\"]*", z
)==0 ){
1475 utf8_printf(out
, "CREATE TABLE IF NOT EXISTS %s%s", z
+13, zTail
);
1477 utf8_printf(out
, "%s%s", z
, zTail
);
1480 static void printSchemaLineN(FILE *out
, char *z
, int n
, const char *zTail
){
1483 printSchemaLine(out
, z
, zTail
);
1488 ** Return true if string z[] has nothing but whitespace and comments to the
1489 ** end of the first line.
1491 static int wsToEol(const char *z
){
1493 for(i
=0; z
[i
]; i
++){
1494 if( z
[i
]=='\n' ) return 1;
1495 if( IsSpace(z
[i
]) ) continue;
1496 if( z
[i
]=='-' && z
[i
+1]=='-' ) return 1;
1504 ** This is the callback routine that the shell
1505 ** invokes for each row of a query result.
1507 static int shell_callback(
1509 int nArg
, /* Number of result columns */
1510 char **azArg
, /* Text of each result column */
1511 char **azCol
, /* Column names */
1512 int *aiType
/* Column types */
1515 ShellState
*p
= (ShellState
*)pArg
;
1517 if( azArg
==0 ) return 0;
1521 if( azArg
==0 ) break;
1522 for(i
=0; i
<nArg
; i
++){
1523 int len
= strlen30(azCol
[i
] ? azCol
[i
] : "");
1524 if( len
>w
) w
= len
;
1526 if( p
->cnt
++>0 ) utf8_printf(p
->out
, "%s", p
->rowSeparator
);
1527 for(i
=0; i
<nArg
; i
++){
1528 utf8_printf(p
->out
,"%*s = %s%s", w
, azCol
[i
],
1529 azArg
[i
] ? azArg
[i
] : p
->nullValue
, p
->rowSeparator
);
1535 static const int aExplainWidths
[] = {4, 13, 4, 4, 4, 13, 2, 13};
1536 const int *colWidth
;
1539 if( p
->cMode
==MODE_Column
){
1540 colWidth
= p
->colWidth
;
1541 showHdr
= p
->showHeader
;
1542 rowSep
= p
->rowSeparator
;
1544 colWidth
= aExplainWidths
;
1549 for(i
=0; i
<nArg
; i
++){
1551 if( i
<ArraySize(p
->colWidth
) ){
1557 w
= strlenChar(azCol
[i
] ? azCol
[i
] : "");
1559 n
= strlenChar(azArg
&& azArg
[i
] ? azArg
[i
] : p
->nullValue
);
1562 if( i
<ArraySize(p
->actualWidth
) ){
1563 p
->actualWidth
[i
] = w
;
1566 utf8_width_print(p
->out
, w
, azCol
[i
]);
1567 utf8_printf(p
->out
, "%s", i
==nArg
-1 ? rowSep
: " ");
1571 for(i
=0; i
<nArg
; i
++){
1573 if( i
<ArraySize(p
->actualWidth
) ){
1574 w
= p
->actualWidth
[i
];
1579 utf8_printf(p
->out
,"%-*.*s%s",w
,w
,
1580 "----------------------------------------------------------"
1581 "----------------------------------------------------------",
1582 i
==nArg
-1 ? rowSep
: " ");
1586 if( azArg
==0 ) break;
1587 for(i
=0; i
<nArg
; i
++){
1589 if( i
<ArraySize(p
->actualWidth
) ){
1590 w
= p
->actualWidth
[i
];
1594 if( p
->cMode
==MODE_Explain
&& azArg
[i
] && strlenChar(azArg
[i
])>w
){
1595 w
= strlenChar(azArg
[i
]);
1597 if( i
==1 && p
->aiIndent
&& p
->pStmt
){
1598 if( p
->iIndent
<p
->nIndent
){
1599 utf8_printf(p
->out
, "%*.s", p
->aiIndent
[p
->iIndent
], "");
1603 utf8_width_print(p
->out
, w
, azArg
[i
] ? azArg
[i
] : p
->nullValue
);
1604 utf8_printf(p
->out
, "%s", i
==nArg
-1 ? rowSep
: " ");
1608 case MODE_Semi
: { /* .schema and .fullschema output */
1609 printSchemaLine(p
->out
, azArg
[0], ";\n");
1612 case MODE_Pretty
: { /* .schema and .fullschema with --indent */
1620 if( azArg
[0]==0 ) break;
1621 if( sqlite3_strlike("CREATE VIEW%", azArg
[0], 0)==0
1622 || sqlite3_strlike("CREATE TRIG%", azArg
[0], 0)==0
1624 utf8_printf(p
->out
, "%s;\n", azArg
[0]);
1627 z
= sqlite3_mprintf("%s", azArg
[0]);
1629 for(i
=0; IsSpace(z
[i
]); i
++){}
1630 for(; (c
= z
[i
])!=0; i
++){
1632 if( z
[j
-1]=='\r' ) z
[j
-1] = '\n';
1633 if( IsSpace(z
[j
-1]) || z
[j
-1]=='(' ) continue;
1634 }else if( (c
=='(' || c
==')') && j
>0 && IsSpace(z
[j
-1]) ){
1639 while( j
>0 && IsSpace(z
[j
-1]) ){ j
--; }
1641 if( strlen30(z
)>=79 ){
1642 for(i
=j
=0; (c
= z
[i
])!=0; i
++){ /* Copy changes from z[i] back to z[j] */
1645 }else if( c
=='"' || c
=='\'' || c
=='`' ){
1649 }else if( c
=='-' && z
[i
+1]=='-' ){
1655 if( nLine
>0 && nParen
==0 && j
>0 ){
1656 printSchemaLineN(p
->out
, z
, j
, "\n");
1661 if( nParen
==1 && cEnd
==0
1662 && (c
=='(' || c
=='\n' || (c
==',' && !wsToEol(z
+i
+1)))
1665 printSchemaLineN(p
->out
, z
, j
, "\n ");
1668 while( IsSpace(z
[i
+1]) ){ i
++; }
1673 printSchemaLine(p
->out
, z
, ";\n");
1678 if( p
->cnt
++==0 && p
->showHeader
){
1679 for(i
=0; i
<nArg
; i
++){
1680 utf8_printf(p
->out
,"%s%s",azCol
[i
],
1681 i
==nArg
-1 ? p
->rowSeparator
: p
->colSeparator
);
1684 if( azArg
==0 ) break;
1685 for(i
=0; i
<nArg
; i
++){
1687 if( z
==0 ) z
= p
->nullValue
;
1688 utf8_printf(p
->out
, "%s", z
);
1690 utf8_printf(p
->out
, "%s", p
->colSeparator
);
1692 utf8_printf(p
->out
, "%s", p
->rowSeparator
);
1698 if( p
->cnt
++==0 && p
->showHeader
){
1699 raw_printf(p
->out
,"<TR>");
1700 for(i
=0; i
<nArg
; i
++){
1701 raw_printf(p
->out
,"<TH>");
1702 output_html_string(p
->out
, azCol
[i
]);
1703 raw_printf(p
->out
,"</TH>\n");
1705 raw_printf(p
->out
,"</TR>\n");
1707 if( azArg
==0 ) break;
1708 raw_printf(p
->out
,"<TR>");
1709 for(i
=0; i
<nArg
; i
++){
1710 raw_printf(p
->out
,"<TD>");
1711 output_html_string(p
->out
, azArg
[i
] ? azArg
[i
] : p
->nullValue
);
1712 raw_printf(p
->out
,"</TD>\n");
1714 raw_printf(p
->out
,"</TR>\n");
1718 if( p
->cnt
++==0 && p
->showHeader
){
1719 for(i
=0; i
<nArg
; i
++){
1720 output_c_string(p
->out
,azCol
[i
] ? azCol
[i
] : "");
1721 if(i
<nArg
-1) utf8_printf(p
->out
, "%s", p
->colSeparator
);
1723 utf8_printf(p
->out
, "%s", p
->rowSeparator
);
1725 if( azArg
==0 ) break;
1726 for(i
=0; i
<nArg
; i
++){
1727 output_c_string(p
->out
, azArg
[i
] ? azArg
[i
] : p
->nullValue
);
1728 if(i
<nArg
-1) utf8_printf(p
->out
, "%s", p
->colSeparator
);
1730 utf8_printf(p
->out
, "%s", p
->rowSeparator
);
1734 setBinaryMode(p
->out
, 1);
1735 if( p
->cnt
++==0 && p
->showHeader
){
1736 for(i
=0; i
<nArg
; i
++){
1737 output_csv(p
, azCol
[i
] ? azCol
[i
] : "", i
<nArg
-1);
1739 utf8_printf(p
->out
, "%s", p
->rowSeparator
);
1742 for(i
=0; i
<nArg
; i
++){
1743 output_csv(p
, azArg
[i
], i
<nArg
-1);
1745 utf8_printf(p
->out
, "%s", p
->rowSeparator
);
1747 setTextMode(p
->out
, 1);
1751 if( azArg
==0 ) break;
1752 utf8_printf(p
->out
,"INSERT INTO %s",p
->zDestTable
);
1753 if( p
->showHeader
){
1754 raw_printf(p
->out
,"(");
1755 for(i
=0; i
<nArg
; i
++){
1756 if( i
>0 ) raw_printf(p
->out
, ",");
1757 if( quoteChar(azCol
[i
]) ){
1758 char *z
= sqlite3_mprintf("\"%w\"", azCol
[i
]);
1759 utf8_printf(p
->out
, "%s", z
);
1762 raw_printf(p
->out
, "%s", azCol
[i
]);
1765 raw_printf(p
->out
,")");
1768 for(i
=0; i
<nArg
; i
++){
1769 raw_printf(p
->out
, i
>0 ? "," : " VALUES(");
1770 if( (azArg
[i
]==0) || (aiType
&& aiType
[i
]==SQLITE_NULL
) ){
1771 utf8_printf(p
->out
,"NULL");
1772 }else if( aiType
&& aiType
[i
]==SQLITE_TEXT
){
1773 if( ShellHasFlag(p
, SHFLG_Newlines
) ){
1774 output_quoted_string(p
->out
, azArg
[i
]);
1776 output_quoted_escaped_string(p
->out
, azArg
[i
]);
1778 }else if( aiType
&& aiType
[i
]==SQLITE_INTEGER
){
1779 utf8_printf(p
->out
,"%s", azArg
[i
]);
1780 }else if( aiType
&& aiType
[i
]==SQLITE_FLOAT
){
1782 double r
= sqlite3_column_double(p
->pStmt
, i
);
1783 sqlite3_snprintf(50,z
,"%!.20g", r
);
1784 raw_printf(p
->out
, "%s", z
);
1785 }else if( aiType
&& aiType
[i
]==SQLITE_BLOB
&& p
->pStmt
){
1786 const void *pBlob
= sqlite3_column_blob(p
->pStmt
, i
);
1787 int nBlob
= sqlite3_column_bytes(p
->pStmt
, i
);
1788 output_hex_blob(p
->out
, pBlob
, nBlob
);
1789 }else if( isNumber(azArg
[i
], 0) ){
1790 utf8_printf(p
->out
,"%s", azArg
[i
]);
1791 }else if( ShellHasFlag(p
, SHFLG_Newlines
) ){
1792 output_quoted_string(p
->out
, azArg
[i
]);
1794 output_quoted_escaped_string(p
->out
, azArg
[i
]);
1797 raw_printf(p
->out
,");\n");
1801 if( azArg
==0 ) break;
1802 if( p
->cnt
==0 && p
->showHeader
){
1803 for(i
=0; i
<nArg
; i
++){
1804 if( i
>0 ) raw_printf(p
->out
, ",");
1805 output_quoted_string(p
->out
, azCol
[i
]);
1807 raw_printf(p
->out
,"\n");
1810 for(i
=0; i
<nArg
; i
++){
1811 if( i
>0 ) raw_printf(p
->out
, ",");
1812 if( (azArg
[i
]==0) || (aiType
&& aiType
[i
]==SQLITE_NULL
) ){
1813 utf8_printf(p
->out
,"NULL");
1814 }else if( aiType
&& aiType
[i
]==SQLITE_TEXT
){
1815 output_quoted_string(p
->out
, azArg
[i
]);
1816 }else if( aiType
&& aiType
[i
]==SQLITE_INTEGER
){
1817 utf8_printf(p
->out
,"%s", azArg
[i
]);
1818 }else if( aiType
&& aiType
[i
]==SQLITE_FLOAT
){
1820 double r
= sqlite3_column_double(p
->pStmt
, i
);
1821 sqlite3_snprintf(50,z
,"%!.20g", r
);
1822 raw_printf(p
->out
, "%s", z
);
1823 }else if( aiType
&& aiType
[i
]==SQLITE_BLOB
&& p
->pStmt
){
1824 const void *pBlob
= sqlite3_column_blob(p
->pStmt
, i
);
1825 int nBlob
= sqlite3_column_bytes(p
->pStmt
, i
);
1826 output_hex_blob(p
->out
, pBlob
, nBlob
);
1827 }else if( isNumber(azArg
[i
], 0) ){
1828 utf8_printf(p
->out
,"%s", azArg
[i
]);
1830 output_quoted_string(p
->out
, azArg
[i
]);
1833 raw_printf(p
->out
,"\n");
1837 if( p
->cnt
++==0 && p
->showHeader
){
1838 for(i
=0; i
<nArg
; i
++){
1839 if( i
>0 ) utf8_printf(p
->out
, "%s", p
->colSeparator
);
1840 utf8_printf(p
->out
,"%s",azCol
[i
] ? azCol
[i
] : "");
1842 utf8_printf(p
->out
, "%s", p
->rowSeparator
);
1844 if( azArg
==0 ) break;
1845 for(i
=0; i
<nArg
; i
++){
1846 if( i
>0 ) utf8_printf(p
->out
, "%s", p
->colSeparator
);
1847 utf8_printf(p
->out
,"%s",azArg
[i
] ? azArg
[i
] : p
->nullValue
);
1849 utf8_printf(p
->out
, "%s", p
->rowSeparator
);
1857 ** This is the callback routine that the SQLite library
1858 ** invokes for each row of a query result.
1860 static int callback(void *pArg
, int nArg
, char **azArg
, char **azCol
){
1861 /* since we don't have type info, call the shell_callback with a NULL value */
1862 return shell_callback(pArg
, nArg
, azArg
, azCol
, NULL
);
1866 ** This is the callback routine from sqlite3_exec() that appends all
1867 ** output onto the end of a ShellText object.
1869 static int captureOutputCallback(void *pArg
, int nArg
, char **azArg
, char **az
){
1870 ShellText
*p
= (ShellText
*)pArg
;
1872 UNUSED_PARAMETER(az
);
1873 if( azArg
==0 ) return 0;
1874 if( p
->n
) appendText(p
, "|", 0);
1875 for(i
=0; i
<nArg
; i
++){
1876 if( i
) appendText(p
, ",", 0);
1877 if( azArg
[i
] ) appendText(p
, azArg
[i
], 0);
1883 ** Generate an appropriate SELFTEST table in the main database.
1885 static void createSelftestTable(ShellState
*p
){
1888 "SAVEPOINT selftest_init;\n"
1889 "CREATE TABLE IF NOT EXISTS selftest(\n"
1890 " tno INTEGER PRIMARY KEY,\n" /* Test number */
1891 " op TEXT,\n" /* Operator: memo run */
1892 " cmd TEXT,\n" /* Command text */
1893 " ans TEXT\n" /* Desired answer */
1895 "CREATE TEMP TABLE [_shell$self](op,cmd,ans);\n"
1896 "INSERT INTO [_shell$self](rowid,op,cmd)\n"
1897 " VALUES(coalesce((SELECT (max(tno)+100)/10 FROM selftest),10),\n"
1898 " 'memo','Tests generated by --init');\n"
1899 "INSERT INTO [_shell$self]\n"
1901 " 'SELECT hex(sha3_query(''SELECT type,name,tbl_name,sql "
1902 "FROM sqlite_master ORDER BY 2'',224))',\n"
1903 " hex(sha3_query('SELECT type,name,tbl_name,sql "
1904 "FROM sqlite_master ORDER BY 2',224));\n"
1905 "INSERT INTO [_shell$self]\n"
1907 " 'SELECT hex(sha3_query(''SELECT * FROM \"' ||"
1908 " printf('%w',name) || '\" NOT INDEXED'',224))',\n"
1909 " hex(sha3_query(printf('SELECT * FROM \"%w\" NOT INDEXED',name),224))\n"
1911 " SELECT name FROM sqlite_master\n"
1912 " WHERE type='table'\n"
1913 " AND name<>'selftest'\n"
1914 " AND coalesce(rootpage,0)>0\n"
1917 "INSERT INTO [_shell$self]\n"
1918 " VALUES('run','PRAGMA integrity_check','ok');\n"
1919 "INSERT INTO selftest(tno,op,cmd,ans)"
1920 " SELECT rowid*10,op,cmd,ans FROM [_shell$self];\n"
1921 "DROP TABLE [_shell$self];"
1924 utf8_printf(stderr
, "SELFTEST initialization failure: %s\n", zErrMsg
);
1925 sqlite3_free(zErrMsg
);
1927 sqlite3_exec(p
->db
, "RELEASE selftest_init",0,0,0);
1932 ** Set the destination table field of the ShellState structure to
1933 ** the name of the table given. Escape any quote characters in the
1936 static void set_table_name(ShellState
*p
, const char *zName
){
1941 if( p
->zDestTable
){
1942 free(p
->zDestTable
);
1945 if( zName
==0 ) return;
1946 cQuote
= quoteChar(zName
);
1947 n
= strlen30(zName
);
1948 if( cQuote
) n
+= n
+2;
1949 z
= p
->zDestTable
= malloc( n
+1 );
1951 raw_printf(stderr
,"Error: out of memory\n");
1955 if( cQuote
) z
[n
++] = cQuote
;
1956 for(i
=0; zName
[i
]; i
++){
1958 if( zName
[i
]==cQuote
) z
[n
++] = cQuote
;
1960 if( cQuote
) z
[n
++] = cQuote
;
1966 ** Execute a query statement that will generate SQL output. Print
1967 ** the result columns, comma-separated, on a line and then add a
1968 ** semicolon terminator to the end of that line.
1970 ** If the number of columns is 1 and that column contains text "--"
1971 ** then write the semicolon on a separate line. That way, if a
1972 ** "--" comment occurs at the end of the statement, the comment
1973 ** won't consume the semicolon terminator.
1975 static int run_table_dump_query(
1976 ShellState
*p
, /* Query context */
1977 const char *zSelect
, /* SELECT statement to extract content */
1978 const char *zFirstRow
/* Print before first row, if not NULL */
1980 sqlite3_stmt
*pSelect
;
1985 rc
= sqlite3_prepare_v2(p
->db
, zSelect
, -1, &pSelect
, 0);
1986 if( rc
!=SQLITE_OK
|| !pSelect
){
1987 utf8_printf(p
->out
, "/**** ERROR: (%d) %s *****/\n", rc
,
1988 sqlite3_errmsg(p
->db
));
1989 if( (rc
&0xff)!=SQLITE_CORRUPT
) p
->nErr
++;
1992 rc
= sqlite3_step(pSelect
);
1993 nResult
= sqlite3_column_count(pSelect
);
1994 while( rc
==SQLITE_ROW
){
1996 utf8_printf(p
->out
, "%s", zFirstRow
);
1999 z
= (const char*)sqlite3_column_text(pSelect
, 0);
2000 utf8_printf(p
->out
, "%s", z
);
2001 for(i
=1; i
<nResult
; i
++){
2002 utf8_printf(p
->out
, ",%s", sqlite3_column_text(pSelect
, i
));
2005 while( z
[0] && (z
[0]!='-' || z
[1]!='-') ) z
++;
2007 raw_printf(p
->out
, "\n;\n");
2009 raw_printf(p
->out
, ";\n");
2011 rc
= sqlite3_step(pSelect
);
2013 rc
= sqlite3_finalize(pSelect
);
2014 if( rc
!=SQLITE_OK
){
2015 utf8_printf(p
->out
, "/**** ERROR: (%d) %s *****/\n", rc
,
2016 sqlite3_errmsg(p
->db
));
2017 if( (rc
&0xff)!=SQLITE_CORRUPT
) p
->nErr
++;
2023 ** Allocate space and save off current error string.
2025 static char *save_err_msg(
2026 sqlite3
*db
/* Database to query */
2028 int nErrMsg
= 1+strlen30(sqlite3_errmsg(db
));
2029 char *zErrMsg
= sqlite3_malloc64(nErrMsg
);
2031 memcpy(zErrMsg
, sqlite3_errmsg(db
), nErrMsg
);
2038 ** Attempt to display I/O stats on Linux using /proc/PID/io
2040 static void displayLinuxIoStats(FILE *out
){
2043 sqlite3_snprintf(sizeof(z
), z
, "/proc/%d/io", getpid());
2044 in
= fopen(z
, "rb");
2046 while( fgets(z
, sizeof(z
), in
)!=0 ){
2047 static const struct {
2048 const char *zPattern
;
2051 { "rchar: ", "Bytes received by read():" },
2052 { "wchar: ", "Bytes sent to write():" },
2053 { "syscr: ", "Read() system calls:" },
2054 { "syscw: ", "Write() system calls:" },
2055 { "read_bytes: ", "Bytes read from storage:" },
2056 { "write_bytes: ", "Bytes written to storage:" },
2057 { "cancelled_write_bytes: ", "Cancelled write bytes:" },
2060 for(i
=0; i
<ArraySize(aTrans
); i
++){
2061 int n
= strlen30(aTrans
[i
].zPattern
);
2062 if( strncmp(aTrans
[i
].zPattern
, z
, n
)==0 ){
2063 utf8_printf(out
, "%-36s %s", aTrans
[i
].zDesc
, &z
[n
]);
2073 ** Display a single line of status using 64-bit values.
2075 static void displayStatLine(
2076 ShellState
*p
, /* The shell context */
2077 char *zLabel
, /* Label for this one line */
2078 char *zFormat
, /* Format for the result */
2079 int iStatusCtrl
, /* Which status to display */
2080 int bReset
/* True to reset the stats */
2082 sqlite3_int64 iCur
= -1;
2083 sqlite3_int64 iHiwtr
= -1;
2086 sqlite3_status64(iStatusCtrl
, &iCur
, &iHiwtr
, bReset
);
2087 for(i
=0, nPercent
=0; zFormat
[i
]; i
++){
2088 if( zFormat
[i
]=='%' ) nPercent
++;
2091 sqlite3_snprintf(sizeof(zLine
), zLine
, zFormat
, iCur
, iHiwtr
);
2093 sqlite3_snprintf(sizeof(zLine
), zLine
, zFormat
, iHiwtr
);
2095 raw_printf(p
->out
, "%-36s %s\n", zLabel
, zLine
);
2099 ** Display memory stats.
2101 static int display_stats(
2102 sqlite3
*db
, /* Database to query */
2103 ShellState
*pArg
, /* Pointer to ShellState */
2104 int bReset
/* True to reset the stats */
2109 if( pArg
&& pArg
->out
){
2110 displayStatLine(pArg
, "Memory Used:",
2111 "%lld (max %lld) bytes", SQLITE_STATUS_MEMORY_USED
, bReset
);
2112 displayStatLine(pArg
, "Number of Outstanding Allocations:",
2113 "%lld (max %lld)", SQLITE_STATUS_MALLOC_COUNT
, bReset
);
2114 if( pArg
->shellFlgs
& SHFLG_Pagecache
){
2115 displayStatLine(pArg
, "Number of Pcache Pages Used:",
2116 "%lld (max %lld) pages", SQLITE_STATUS_PAGECACHE_USED
, bReset
);
2118 displayStatLine(pArg
, "Number of Pcache Overflow Bytes:",
2119 "%lld (max %lld) bytes", SQLITE_STATUS_PAGECACHE_OVERFLOW
, bReset
);
2120 displayStatLine(pArg
, "Largest Allocation:",
2121 "%lld bytes", SQLITE_STATUS_MALLOC_SIZE
, bReset
);
2122 displayStatLine(pArg
, "Largest Pcache Allocation:",
2123 "%lld bytes", SQLITE_STATUS_PAGECACHE_SIZE
, bReset
);
2124 #ifdef YYTRACKMAXSTACKDEPTH
2125 displayStatLine(pArg
, "Deepest Parser Stack:",
2126 "%lld (max %lld)", SQLITE_STATUS_PARSER_STACK
, bReset
);
2130 if( pArg
&& pArg
->out
&& db
){
2131 if( pArg
->shellFlgs
& SHFLG_Lookaside
){
2133 sqlite3_db_status(db
, SQLITE_DBSTATUS_LOOKASIDE_USED
,
2134 &iCur
, &iHiwtr
, bReset
);
2135 raw_printf(pArg
->out
,
2136 "Lookaside Slots Used: %d (max %d)\n",
2138 sqlite3_db_status(db
, SQLITE_DBSTATUS_LOOKASIDE_HIT
,
2139 &iCur
, &iHiwtr
, bReset
);
2140 raw_printf(pArg
->out
, "Successful lookaside attempts: %d\n",
2142 sqlite3_db_status(db
, SQLITE_DBSTATUS_LOOKASIDE_MISS_SIZE
,
2143 &iCur
, &iHiwtr
, bReset
);
2144 raw_printf(pArg
->out
, "Lookaside failures due to size: %d\n",
2146 sqlite3_db_status(db
, SQLITE_DBSTATUS_LOOKASIDE_MISS_FULL
,
2147 &iCur
, &iHiwtr
, bReset
);
2148 raw_printf(pArg
->out
, "Lookaside failures due to OOM: %d\n",
2152 sqlite3_db_status(db
, SQLITE_DBSTATUS_CACHE_USED
, &iCur
, &iHiwtr
, bReset
);
2153 raw_printf(pArg
->out
, "Pager Heap Usage: %d bytes\n",
2156 sqlite3_db_status(db
, SQLITE_DBSTATUS_CACHE_HIT
, &iCur
, &iHiwtr
, 1);
2157 raw_printf(pArg
->out
, "Page cache hits: %d\n", iCur
);
2159 sqlite3_db_status(db
, SQLITE_DBSTATUS_CACHE_MISS
, &iCur
, &iHiwtr
, 1);
2160 raw_printf(pArg
->out
, "Page cache misses: %d\n", iCur
);
2162 sqlite3_db_status(db
, SQLITE_DBSTATUS_CACHE_WRITE
, &iCur
, &iHiwtr
, 1);
2163 raw_printf(pArg
->out
, "Page cache writes: %d\n", iCur
);
2165 sqlite3_db_status(db
, SQLITE_DBSTATUS_SCHEMA_USED
, &iCur
, &iHiwtr
, bReset
);
2166 raw_printf(pArg
->out
, "Schema Heap Usage: %d bytes\n",
2169 sqlite3_db_status(db
, SQLITE_DBSTATUS_STMT_USED
, &iCur
, &iHiwtr
, bReset
);
2170 raw_printf(pArg
->out
, "Statement Heap/Lookaside Usage: %d bytes\n",
2174 if( pArg
&& pArg
->out
&& db
&& pArg
->pStmt
){
2175 iCur
= sqlite3_stmt_status(pArg
->pStmt
, SQLITE_STMTSTATUS_FULLSCAN_STEP
,
2177 raw_printf(pArg
->out
, "Fullscan Steps: %d\n", iCur
);
2178 iCur
= sqlite3_stmt_status(pArg
->pStmt
, SQLITE_STMTSTATUS_SORT
, bReset
);
2179 raw_printf(pArg
->out
, "Sort Operations: %d\n", iCur
);
2180 iCur
= sqlite3_stmt_status(pArg
->pStmt
, SQLITE_STMTSTATUS_AUTOINDEX
,bReset
);
2181 raw_printf(pArg
->out
, "Autoindex Inserts: %d\n", iCur
);
2182 iCur
= sqlite3_stmt_status(pArg
->pStmt
, SQLITE_STMTSTATUS_VM_STEP
, bReset
);
2183 raw_printf(pArg
->out
, "Virtual Machine Steps: %d\n", iCur
);
2187 displayLinuxIoStats(pArg
->out
);
2190 /* Do not remove this machine readable comment: extra-stats-output-here */
2196 ** Display scan stats.
2198 static void display_scanstats(
2199 sqlite3
*db
, /* Database to query */
2200 ShellState
*pArg
/* Pointer to ShellState */
2202 #ifndef SQLITE_ENABLE_STMT_SCANSTATUS
2203 UNUSED_PARAMETER(db
);
2204 UNUSED_PARAMETER(pArg
);
2207 raw_printf(pArg
->out
, "-------- scanstats --------\n");
2209 for(k
=0; k
<=mx
; k
++){
2210 double rEstLoop
= 1.0;
2212 sqlite3_stmt
*p
= pArg
->pStmt
;
2213 sqlite3_int64 nLoop
, nVisit
;
2216 const char *zExplain
;
2217 if( sqlite3_stmt_scanstatus(p
, i
, SQLITE_SCANSTAT_NLOOP
, (void*)&nLoop
) ){
2220 sqlite3_stmt_scanstatus(p
, i
, SQLITE_SCANSTAT_SELECTID
, (void*)&iSid
);
2221 if( iSid
>mx
) mx
= iSid
;
2222 if( iSid
!=k
) continue;
2224 rEstLoop
= (double)nLoop
;
2225 if( k
>0 ) raw_printf(pArg
->out
, "-------- subquery %d -------\n", k
);
2228 sqlite3_stmt_scanstatus(p
, i
, SQLITE_SCANSTAT_NVISIT
, (void*)&nVisit
);
2229 sqlite3_stmt_scanstatus(p
, i
, SQLITE_SCANSTAT_EST
, (void*)&rEst
);
2230 sqlite3_stmt_scanstatus(p
, i
, SQLITE_SCANSTAT_EXPLAIN
, (void*)&zExplain
);
2231 utf8_printf(pArg
->out
, "Loop %2d: %s\n", n
, zExplain
);
2233 raw_printf(pArg
->out
,
2234 " nLoop=%-8lld nRow=%-8lld estRow=%-8lld estRow/Loop=%-8g\n",
2235 nLoop
, nVisit
, (sqlite3_int64
)(rEstLoop
+0.5), rEst
2239 raw_printf(pArg
->out
, "---------------------------\n");
2244 ** Parameter azArray points to a zero-terminated array of strings. zStr
2245 ** points to a single nul-terminated string. Return non-zero if zStr
2246 ** is equal, according to strcmp(), to any of the strings in the array.
2247 ** Otherwise, return zero.
2249 static int str_in_array(const char *zStr
, const char **azArray
){
2251 for(i
=0; azArray
[i
]; i
++){
2252 if( 0==strcmp(zStr
, azArray
[i
]) ) return 1;
2258 ** If compiled statement pSql appears to be an EXPLAIN statement, allocate
2259 ** and populate the ShellState.aiIndent[] array with the number of
2260 ** spaces each opcode should be indented before it is output.
2262 ** The indenting rules are:
2264 ** * For each "Next", "Prev", "VNext" or "VPrev" instruction, indent
2265 ** all opcodes that occur between the p2 jump destination and the opcode
2266 ** itself by 2 spaces.
2268 ** * For each "Goto", if the jump destination is earlier in the program
2269 ** and ends on one of:
2270 ** Yield SeekGt SeekLt RowSetRead Rewind
2271 ** or if the P1 parameter is one instead of zero,
2272 ** then indent all opcodes between the earlier instruction
2273 ** and "Goto" by 2 spaces.
2275 static void explain_data_prepare(ShellState
*p
, sqlite3_stmt
*pSql
){
2276 const char *zSql
; /* The text of the SQL statement */
2277 const char *z
; /* Used to check if this is an EXPLAIN */
2278 int *abYield
= 0; /* True if op is an OP_Yield */
2279 int nAlloc
= 0; /* Allocated size of p->aiIndent[], abYield */
2280 int iOp
; /* Index of operation in p->aiIndent[] */
2282 const char *azNext
[] = { "Next", "Prev", "VPrev", "VNext", "SorterNext",
2283 "NextIfOpen", "PrevIfOpen", 0 };
2284 const char *azYield
[] = { "Yield", "SeekLT", "SeekGT", "RowSetRead",
2286 const char *azGoto
[] = { "Goto", 0 };
2288 /* Try to figure out if this is really an EXPLAIN statement. If this
2289 ** cannot be verified, return early. */
2290 if( sqlite3_column_count(pSql
)!=8 ){
2294 zSql
= sqlite3_sql(pSql
);
2295 if( zSql
==0 ) return;
2296 for(z
=zSql
; *z
==' ' || *z
=='\t' || *z
=='\n' || *z
=='\f' || *z
=='\r'; z
++);
2297 if( sqlite3_strnicmp(z
, "explain", 7) ){
2302 for(iOp
=0; SQLITE_ROW
==sqlite3_step(pSql
); iOp
++){
2304 int iAddr
= sqlite3_column_int(pSql
, 0);
2305 const char *zOp
= (const char*)sqlite3_column_text(pSql
, 1);
2307 /* Set p2 to the P2 field of the current opcode. Then, assuming that
2308 ** p2 is an instruction address, set variable p2op to the index of that
2309 ** instruction in the aiIndent[] array. p2 and p2op may be different if
2310 ** the current instruction is part of a sub-program generated by an
2311 ** SQL trigger or foreign key. */
2312 int p2
= sqlite3_column_int(pSql
, 3);
2313 int p2op
= (p2
+ (iOp
-iAddr
));
2315 /* Grow the p->aiIndent array as required */
2318 /* Do further verfication that this is explain output. Abort if
2320 static const char *explainCols
[] = {
2321 "addr", "opcode", "p1", "p2", "p3", "p4", "p5", "comment" };
2323 for(jj
=0; jj
<ArraySize(explainCols
); jj
++){
2324 if( strcmp(sqlite3_column_name(pSql
,jj
),explainCols
[jj
])!=0 ){
2326 sqlite3_reset(pSql
);
2332 p
->aiIndent
= (int*)sqlite3_realloc64(p
->aiIndent
, nAlloc
*sizeof(int));
2333 abYield
= (int*)sqlite3_realloc64(abYield
, nAlloc
*sizeof(int));
2335 abYield
[iOp
] = str_in_array(zOp
, azYield
);
2336 p
->aiIndent
[iOp
] = 0;
2339 if( str_in_array(zOp
, azNext
) ){
2340 for(i
=p2op
; i
<iOp
; i
++) p
->aiIndent
[i
] += 2;
2342 if( str_in_array(zOp
, azGoto
) && p2op
<p
->nIndent
2343 && (abYield
[p2op
] || sqlite3_column_int(pSql
, 2))
2345 for(i
=p2op
; i
<iOp
; i
++) p
->aiIndent
[i
] += 2;
2350 sqlite3_free(abYield
);
2351 sqlite3_reset(pSql
);
2355 ** Free the array allocated by explain_data_prepare().
2357 static void explain_data_delete(ShellState
*p
){
2358 sqlite3_free(p
->aiIndent
);
2365 ** Disable and restore .wheretrace and .selecttrace settings.
2367 #if defined(SQLITE_DEBUG) && defined(SQLITE_ENABLE_SELECTTRACE)
2368 extern int sqlite3SelectTrace
;
2369 static int savedSelectTrace
;
2371 #if defined(SQLITE_DEBUG) && defined(SQLITE_ENABLE_WHERETRACE)
2372 extern int sqlite3WhereTrace
;
2373 static int savedWhereTrace
;
2375 static void disable_debug_trace_modes(void){
2376 #if defined(SQLITE_DEBUG) && defined(SQLITE_ENABLE_SELECTTRACE)
2377 savedSelectTrace
= sqlite3SelectTrace
;
2378 sqlite3SelectTrace
= 0;
2380 #if defined(SQLITE_DEBUG) && defined(SQLITE_ENABLE_WHERETRACE)
2381 savedWhereTrace
= sqlite3WhereTrace
;
2382 sqlite3WhereTrace
= 0;
2385 static void restore_debug_trace_modes(void){
2386 #if defined(SQLITE_DEBUG) && defined(SQLITE_ENABLE_SELECTTRACE)
2387 sqlite3SelectTrace
= savedSelectTrace
;
2389 #if defined(SQLITE_DEBUG) && defined(SQLITE_ENABLE_WHERETRACE)
2390 sqlite3WhereTrace
= savedWhereTrace
;
2395 ** Run a prepared statement
2397 static void exec_prepared_stmt(
2398 ShellState
*pArg
, /* Pointer to ShellState */
2399 sqlite3_stmt
*pStmt
, /* Statment to run */
2400 int (*xCallback
)(void*,int,char**,char**,int*) /* Callback function */
2404 /* perform the first step. this will tell us if we
2405 ** have a result set or not and how wide it is.
2407 rc
= sqlite3_step(pStmt
);
2408 /* if we have a result set... */
2409 if( SQLITE_ROW
== rc
){
2410 /* if we have a callback... */
2412 /* allocate space for col name ptr, value ptr, and type */
2413 int nCol
= sqlite3_column_count(pStmt
);
2414 void *pData
= sqlite3_malloc64(3*nCol
*sizeof(const char*) + 1);
2418 char **azCols
= (char **)pData
; /* Names of result columns */
2419 char **azVals
= &azCols
[nCol
]; /* Results */
2420 int *aiTypes
= (int *)&azVals
[nCol
]; /* Result types */
2422 assert(sizeof(int) <= sizeof(char *));
2423 /* save off ptrs to column names */
2424 for(i
=0; i
<nCol
; i
++){
2425 azCols
[i
] = (char *)sqlite3_column_name(pStmt
, i
);
2428 /* extract the data and data types */
2429 for(i
=0; i
<nCol
; i
++){
2430 aiTypes
[i
] = x
= sqlite3_column_type(pStmt
, i
);
2431 if( x
==SQLITE_BLOB
&& pArg
&& pArg
->cMode
==MODE_Insert
){
2434 azVals
[i
] = (char*)sqlite3_column_text(pStmt
, i
);
2436 if( !azVals
[i
] && (aiTypes
[i
]!=SQLITE_NULL
) ){
2438 break; /* from for */
2442 /* if data and types extracted successfully... */
2443 if( SQLITE_ROW
== rc
){
2444 /* call the supplied callback with the result row data */
2445 if( xCallback(pArg
, nCol
, azVals
, azCols
, aiTypes
) ){
2448 rc
= sqlite3_step(pStmt
);
2451 } while( SQLITE_ROW
== rc
);
2452 sqlite3_free(pData
);
2456 rc
= sqlite3_step(pStmt
);
2457 } while( rc
== SQLITE_ROW
);
2462 #ifndef SQLITE_OMIT_VIRTUALTABLE
2464 ** This function is called to process SQL if the previous shell command
2465 ** was ".expert". It passes the SQL in the second argument directly to
2466 ** the sqlite3expert object.
2468 ** If successful, SQLITE_OK is returned. Otherwise, an SQLite error
2469 ** code. In this case, (*pzErr) may be set to point to a buffer containing
2470 ** an English language error message. It is the responsibility of the
2471 ** caller to eventually free this buffer using sqlite3_free().
2473 static int expertHandleSQL(
2478 assert( pState
->expert
.pExpert
);
2479 assert( pzErr
==0 || *pzErr
==0 );
2480 return sqlite3_expert_sql(pState
->expert
.pExpert
, zSql
, pzErr
);
2484 ** This function is called either to silently clean up the object
2485 ** created by the ".expert" command (if bCancel==1), or to generate a
2486 ** report from it and then clean it up (if bCancel==0).
2488 ** If successful, SQLITE_OK is returned. Otherwise, an SQLite error
2489 ** code. In this case, (*pzErr) may be set to point to a buffer containing
2490 ** an English language error message. It is the responsibility of the
2491 ** caller to eventually free this buffer using sqlite3_free().
2493 static int expertFinish(
2499 sqlite3expert
*p
= pState
->expert
.pExpert
;
2501 assert( bCancel
|| pzErr
==0 || *pzErr
==0 );
2503 FILE *out
= pState
->out
;
2504 int bVerbose
= pState
->expert
.bVerbose
;
2506 rc
= sqlite3_expert_analyze(p
, pzErr
);
2507 if( rc
==SQLITE_OK
){
2508 int nQuery
= sqlite3_expert_count(p
);
2512 const char *zCand
= sqlite3_expert_report(p
,0,EXPERT_REPORT_CANDIDATES
);
2513 raw_printf(out
, "-- Candidates -----------------------------\n");
2514 raw_printf(out
, "%s\n", zCand
);
2516 for(i
=0; i
<nQuery
; i
++){
2517 const char *zSql
= sqlite3_expert_report(p
, i
, EXPERT_REPORT_SQL
);
2518 const char *zIdx
= sqlite3_expert_report(p
, i
, EXPERT_REPORT_INDEXES
);
2519 const char *zEQP
= sqlite3_expert_report(p
, i
, EXPERT_REPORT_PLAN
);
2520 if( zIdx
==0 ) zIdx
= "(no new indexes)\n";
2522 raw_printf(out
, "-- Query %d --------------------------------\n",i
+1);
2523 raw_printf(out
, "%s\n\n", zSql
);
2525 raw_printf(out
, "%s\n", zIdx
);
2526 raw_printf(out
, "%s\n", zEQP
);
2530 sqlite3_expert_destroy(p
);
2531 pState
->expert
.pExpert
= 0;
2536 ** Implementation of ".expert" dot command.
2538 static int expertDotCommand(
2539 ShellState
*pState
, /* Current shell tool state */
2540 char **azArg
, /* Array of arguments passed to dot command */
2541 int nArg
/* Number of entries in azArg[] */
2548 assert( pState
->expert
.pExpert
==0 );
2549 memset(&pState
->expert
, 0, sizeof(ExpertInfo
));
2551 for(i
=1; rc
==SQLITE_OK
&& i
<nArg
; i
++){
2554 if( z
[0]=='-' && z
[1]=='-' ) z
++;
2556 if( n
>=2 && 0==strncmp(z
, "-verbose", n
) ){
2557 pState
->expert
.bVerbose
= 1;
2559 else if( n
>=2 && 0==strncmp(z
, "-sample", n
) ){
2561 raw_printf(stderr
, "option requires an argument: %s\n", z
);
2564 iSample
= (int)integerValue(azArg
[++i
]);
2565 if( iSample
<0 || iSample
>100 ){
2566 raw_printf(stderr
, "value out of range: %s\n", azArg
[i
]);
2572 raw_printf(stderr
, "unknown option: %s\n", z
);
2577 if( rc
==SQLITE_OK
){
2578 pState
->expert
.pExpert
= sqlite3_expert_new(pState
->db
, &zErr
);
2579 if( pState
->expert
.pExpert
==0 ){
2580 raw_printf(stderr
, "sqlite3_expert_new: %s\n", zErr
);
2583 sqlite3_expert_config(
2584 pState
->expert
.pExpert
, EXPERT_CONFIG_SAMPLE
, iSample
2591 #endif /* ifndef SQLITE_OMIT_VIRTUALTABLE */
2594 ** Execute a statement or set of statements. Print
2595 ** any result rows/columns depending on the current mode
2596 ** set via the supplied callback.
2598 ** This is very similar to SQLite's built-in sqlite3_exec()
2599 ** function except it takes a slightly different callback
2600 ** and callback data argument.
2602 static int shell_exec(
2603 sqlite3
*db
, /* An open database */
2604 const char *zSql
, /* SQL to be evaluated */
2605 int (*xCallback
)(void*,int,char**,char**,int*), /* Callback function */
2606 /* (not the same as sqlite3_exec) */
2607 ShellState
*pArg
, /* Pointer to ShellState */
2608 char **pzErrMsg
/* Error msg written here */
2610 sqlite3_stmt
*pStmt
= NULL
; /* Statement to execute. */
2611 int rc
= SQLITE_OK
; /* Return Code */
2613 const char *zLeftover
; /* Tail of unprocessed SQL */
2619 #ifndef SQLITE_OMIT_VIRTUALTABLE
2620 if( pArg
->expert
.pExpert
){
2621 rc
= expertHandleSQL(pArg
, zSql
, pzErrMsg
);
2622 return expertFinish(pArg
, (rc
!=SQLITE_OK
), pzErrMsg
);
2626 while( zSql
[0] && (SQLITE_OK
== rc
) ){
2627 static const char *zStmtSql
;
2628 rc
= sqlite3_prepare_v2(db
, zSql
, -1, &pStmt
, &zLeftover
);
2629 if( SQLITE_OK
!= rc
){
2631 *pzErrMsg
= save_err_msg(db
);
2635 /* this happens for a comment or white-space */
2637 while( IsSpace(zSql
[0]) ) zSql
++;
2640 zStmtSql
= sqlite3_sql(pStmt
);
2641 if( zStmtSql
==0 ) zStmtSql
= "";
2642 while( IsSpace(zStmtSql
[0]) ) zStmtSql
++;
2644 /* save off the prepared statment handle and reset row count */
2646 pArg
->pStmt
= pStmt
;
2650 /* echo the sql statement if echo on */
2651 if( pArg
&& ShellHasFlag(pArg
, SHFLG_Echo
) ){
2652 utf8_printf(pArg
->out
, "%s\n", zStmtSql
? zStmtSql
: zSql
);
2655 /* Show the EXPLAIN QUERY PLAN if .eqp is on */
2656 if( pArg
&& pArg
->autoEQP
&& sqlite3_strlike("EXPLAIN%",zStmtSql
,0)!=0 ){
2657 sqlite3_stmt
*pExplain
;
2660 disable_debug_trace_modes();
2661 sqlite3_db_config(db
, SQLITE_DBCONFIG_TRIGGER_EQP
, -1, &triggerEQP
);
2662 if( pArg
->autoEQP
>=AUTOEQP_trigger
){
2663 sqlite3_db_config(db
, SQLITE_DBCONFIG_TRIGGER_EQP
, 1, 0);
2665 zEQP
= sqlite3_mprintf("EXPLAIN QUERY PLAN %s", zStmtSql
);
2666 rc
= sqlite3_prepare_v2(db
, zEQP
, -1, &pExplain
, 0);
2667 if( rc
==SQLITE_OK
){
2668 while( sqlite3_step(pExplain
)==SQLITE_ROW
){
2669 raw_printf(pArg
->out
,"--EQP-- %d,",sqlite3_column_int(pExplain
, 0));
2670 raw_printf(pArg
->out
,"%d,", sqlite3_column_int(pExplain
, 1));
2671 raw_printf(pArg
->out
,"%d,", sqlite3_column_int(pExplain
, 2));
2672 utf8_printf(pArg
->out
,"%s\n", sqlite3_column_text(pExplain
, 3));
2675 sqlite3_finalize(pExplain
);
2677 if( pArg
->autoEQP
>=AUTOEQP_full
){
2678 /* Also do an EXPLAIN for ".eqp full" mode */
2679 zEQP
= sqlite3_mprintf("EXPLAIN %s", zStmtSql
);
2680 rc
= sqlite3_prepare_v2(db
, zEQP
, -1, &pExplain
, 0);
2681 if( rc
==SQLITE_OK
){
2682 pArg
->cMode
= MODE_Explain
;
2683 explain_data_prepare(pArg
, pExplain
);
2684 exec_prepared_stmt(pArg
, pExplain
, xCallback
);
2685 explain_data_delete(pArg
);
2687 sqlite3_finalize(pExplain
);
2690 sqlite3_db_config(db
, SQLITE_DBCONFIG_TRIGGER_EQP
, triggerEQP
, 0);
2691 restore_debug_trace_modes();
2695 pArg
->cMode
= pArg
->mode
;
2696 if( pArg
->autoExplain
2697 && sqlite3_column_count(pStmt
)==8
2698 && sqlite3_strlike("EXPLAIN%", zStmtSql
,0)==0
2700 pArg
->cMode
= MODE_Explain
;
2703 /* If the shell is currently in ".explain" mode, gather the extra
2704 ** data required to add indents to the output.*/
2705 if( pArg
->cMode
==MODE_Explain
){
2706 explain_data_prepare(pArg
, pStmt
);
2710 exec_prepared_stmt(pArg
, pStmt
, xCallback
);
2711 explain_data_delete(pArg
);
2713 /* print usage stats if stats on */
2714 if( pArg
&& pArg
->statsOn
){
2715 display_stats(db
, pArg
, 0);
2718 /* print loop-counters if required */
2719 if( pArg
&& pArg
->scanstatsOn
){
2720 display_scanstats(db
, pArg
);
2723 /* Finalize the statement just executed. If this fails, save a
2724 ** copy of the error message. Otherwise, set zSql to point to the
2725 ** next statement to execute. */
2726 rc2
= sqlite3_finalize(pStmt
);
2727 if( rc
!=SQLITE_NOMEM
) rc
= rc2
;
2728 if( rc
==SQLITE_OK
){
2730 while( IsSpace(zSql
[0]) ) zSql
++;
2731 }else if( pzErrMsg
){
2732 *pzErrMsg
= save_err_msg(db
);
2735 /* clear saved stmt handle */
2746 ** Release memory previously allocated by tableColumnList().
2748 static void freeColumnList(char **azCol
){
2750 for(i
=1; azCol
[i
]; i
++){
2751 sqlite3_free(azCol
[i
]);
2753 /* azCol[0] is a static string */
2754 sqlite3_free(azCol
);
2758 ** Return a list of pointers to strings which are the names of all
2759 ** columns in table zTab. The memory to hold the names is dynamically
2760 ** allocated and must be released by the caller using a subsequent call
2761 ** to freeColumnList().
2763 ** The azCol[0] entry is usually NULL. However, if zTab contains a rowid
2764 ** value that needs to be preserved, then azCol[0] is filled in with the
2765 ** name of the rowid column.
2767 ** The first regular column in the table is azCol[1]. The list is terminated
2768 ** by an entry with azCol[i]==0.
2770 static char **tableColumnList(ShellState
*p
, const char *zTab
){
2772 sqlite3_stmt
*pStmt
;
2776 int nPK
= 0; /* Number of PRIMARY KEY columns seen */
2777 int isIPK
= 0; /* True if one PRIMARY KEY column of type INTEGER */
2778 int preserveRowid
= ShellHasFlag(p
, SHFLG_PreserveRowid
);
2781 zSql
= sqlite3_mprintf("PRAGMA table_info=%Q", zTab
);
2782 rc
= sqlite3_prepare_v2(p
->db
, zSql
, -1, &pStmt
, 0);
2785 while( sqlite3_step(pStmt
)==SQLITE_ROW
){
2786 if( nCol
>=nAlloc
-2 ){
2787 nAlloc
= nAlloc
*2 + nCol
+ 10;
2788 azCol
= sqlite3_realloc(azCol
, nAlloc
*sizeof(azCol
[0]));
2790 raw_printf(stderr
, "Error: out of memory\n");
2794 azCol
[++nCol
] = sqlite3_mprintf("%s", sqlite3_column_text(pStmt
, 1));
2795 if( sqlite3_column_int(pStmt
, 5) ){
2798 && sqlite3_stricmp((const char*)sqlite3_column_text(pStmt
,2),
2807 sqlite3_finalize(pStmt
);
2808 if( azCol
==0 ) return 0;
2812 /* The decision of whether or not a rowid really needs to be preserved
2813 ** is tricky. We never need to preserve a rowid for a WITHOUT ROWID table
2814 ** or a table with an INTEGER PRIMARY KEY. We are unable to preserve
2815 ** rowids on tables where the rowid is inaccessible because there are other
2816 ** columns in the table named "rowid", "_rowid_", and "oid".
2818 if( preserveRowid
&& isIPK
){
2819 /* If a single PRIMARY KEY column with type INTEGER was seen, then it
2820 ** might be an alise for the ROWID. But it might also be a WITHOUT ROWID
2821 ** table or a INTEGER PRIMARY KEY DESC column, neither of which are
2822 ** ROWID aliases. To distinguish these cases, check to see if
2823 ** there is a "pk" entry in "PRAGMA index_list". There will be
2824 ** no "pk" index if the PRIMARY KEY really is an alias for the ROWID.
2826 zSql
= sqlite3_mprintf("SELECT 1 FROM pragma_index_list(%Q)"
2827 " WHERE origin='pk'", zTab
);
2828 rc
= sqlite3_prepare_v2(p
->db
, zSql
, -1, &pStmt
, 0);
2831 freeColumnList(azCol
);
2834 rc
= sqlite3_step(pStmt
);
2835 sqlite3_finalize(pStmt
);
2836 preserveRowid
= rc
==SQLITE_ROW
;
2838 if( preserveRowid
){
2839 /* Only preserve the rowid if we can find a name to use for the
2841 static char *azRowid
[] = { "rowid", "_rowid_", "oid" };
2844 for(i
=1; i
<=nCol
; i
++){
2845 if( sqlite3_stricmp(azRowid
[j
],azCol
[i
])==0 ) break;
2848 /* At this point, we know that azRowid[j] is not the name of any
2849 ** ordinary column in the table. Verify that azRowid[j] is a valid
2850 ** name for the rowid before adding it to azCol[0]. WITHOUT ROWID
2851 ** tables will fail this last check */
2852 rc
= sqlite3_table_column_metadata(p
->db
,0,zTab
,azRowid
[j
],0,0,0,0,0);
2853 if( rc
==SQLITE_OK
) azCol
[0] = azRowid
[j
];
2862 ** Toggle the reverse_unordered_selects setting.
2864 static void toggleSelectOrder(sqlite3
*db
){
2865 sqlite3_stmt
*pStmt
= 0;
2868 sqlite3_prepare_v2(db
, "PRAGMA reverse_unordered_selects", -1, &pStmt
, 0);
2869 if( sqlite3_step(pStmt
)==SQLITE_ROW
){
2870 iSetting
= sqlite3_column_int(pStmt
, 0);
2872 sqlite3_finalize(pStmt
);
2873 sqlite3_snprintf(sizeof(zStmt
), zStmt
,
2874 "PRAGMA reverse_unordered_selects(%d)", !iSetting
);
2875 sqlite3_exec(db
, zStmt
, 0, 0, 0);
2879 ** This is a different callback routine used for dumping the database.
2880 ** Each row received by this callback consists of a table name,
2881 ** the table type ("index" or "table") and SQL to create the table.
2882 ** This routine should print text sufficient to recreate the table.
2884 static int dump_callback(void *pArg
, int nArg
, char **azArg
, char **azNotUsed
){
2889 ShellState
*p
= (ShellState
*)pArg
;
2891 UNUSED_PARAMETER(azNotUsed
);
2892 if( nArg
!=3 || azArg
==0 ) return 0;
2897 if( strcmp(zTable
, "sqlite_sequence")==0 ){
2898 raw_printf(p
->out
, "DELETE FROM sqlite_sequence;\n");
2899 }else if( sqlite3_strglob("sqlite_stat?", zTable
)==0 ){
2900 raw_printf(p
->out
, "ANALYZE sqlite_master;\n");
2901 }else if( strncmp(zTable
, "sqlite_", 7)==0 ){
2903 }else if( strncmp(zSql
, "CREATE VIRTUAL TABLE", 20)==0 ){
2905 if( !p
->writableSchema
){
2906 raw_printf(p
->out
, "PRAGMA writable_schema=ON;\n");
2907 p
->writableSchema
= 1;
2909 zIns
= sqlite3_mprintf(
2910 "INSERT INTO sqlite_master(type,name,tbl_name,rootpage,sql)"
2911 "VALUES('table','%q','%q',0,'%q');",
2912 zTable
, zTable
, zSql
);
2913 utf8_printf(p
->out
, "%s\n", zIns
);
2917 printSchemaLine(p
->out
, zSql
, ";\n");
2920 if( strcmp(zType
, "table")==0 ){
2925 char *savedDestTable
;
2928 azCol
= tableColumnList(p
, zTable
);
2934 /* Always quote the table name, even if it appears to be pure ascii,
2935 ** in case it is a keyword. Ex: INSERT INTO "table" ... */
2937 appendText(&sTable
, zTable
, quoteChar(zTable
));
2938 /* If preserving the rowid, add a column list after the table name.
2939 ** In other words: "INSERT INTO tab(rowid,a,b,c,...) VALUES(...)"
2940 ** instead of the usual "INSERT INTO tab VALUES(...)".
2943 appendText(&sTable
, "(", 0);
2944 appendText(&sTable
, azCol
[0], 0);
2945 for(i
=1; azCol
[i
]; i
++){
2946 appendText(&sTable
, ",", 0);
2947 appendText(&sTable
, azCol
[i
], quoteChar(azCol
[i
]));
2949 appendText(&sTable
, ")", 0);
2952 /* Build an appropriate SELECT statement */
2954 appendText(&sSelect
, "SELECT ", 0);
2956 appendText(&sSelect
, azCol
[0], 0);
2957 appendText(&sSelect
, ",", 0);
2959 for(i
=1; azCol
[i
]; i
++){
2960 appendText(&sSelect
, azCol
[i
], quoteChar(azCol
[i
]));
2962 appendText(&sSelect
, ",", 0);
2965 freeColumnList(azCol
);
2966 appendText(&sSelect
, " FROM ", 0);
2967 appendText(&sSelect
, zTable
, quoteChar(zTable
));
2969 savedDestTable
= p
->zDestTable
;
2970 savedMode
= p
->mode
;
2971 p
->zDestTable
= sTable
.z
;
2972 p
->mode
= p
->cMode
= MODE_Insert
;
2973 rc
= shell_exec(p
->db
, sSelect
.z
, shell_callback
, p
, 0);
2974 if( (rc
&0xff)==SQLITE_CORRUPT
){
2975 raw_printf(p
->out
, "/****** CORRUPTION ERROR *******/\n");
2976 toggleSelectOrder(p
->db
);
2977 shell_exec(p
->db
, sSelect
.z
, shell_callback
, p
, 0);
2978 toggleSelectOrder(p
->db
);
2980 p
->zDestTable
= savedDestTable
;
2981 p
->mode
= savedMode
;
2990 ** Run zQuery. Use dump_callback() as the callback routine so that
2991 ** the contents of the query are output as SQL statements.
2993 ** If we get a SQLITE_CORRUPT error, rerun the query after appending
2994 ** "ORDER BY rowid DESC" to the end.
2996 static int run_schema_dump_query(
3002 rc
= sqlite3_exec(p
->db
, zQuery
, dump_callback
, p
, &zErr
);
3003 if( rc
==SQLITE_CORRUPT
){
3005 int len
= strlen30(zQuery
);
3006 raw_printf(p
->out
, "/****** CORRUPTION ERROR *******/\n");
3008 utf8_printf(p
->out
, "/****** %s ******/\n", zErr
);
3012 zQ2
= malloc( len
+100 );
3013 if( zQ2
==0 ) return rc
;
3014 sqlite3_snprintf(len
+100, zQ2
, "%s ORDER BY rowid DESC", zQuery
);
3015 rc
= sqlite3_exec(p
->db
, zQ2
, dump_callback
, p
, &zErr
);
3017 utf8_printf(p
->out
, "/****** ERROR: %s ******/\n", zErr
);
3019 rc
= SQLITE_CORRUPT
;
3028 ** Text of a help message
3030 static char zHelp
[] =
3031 #if defined(SQLITE_HAVE_ZLIB) && !defined(SQLITE_OMIT_VIRTUALTABLE)
3032 ".archive ... Manage SQL archives: \".archive --help\" for details\n"
3034 #ifndef SQLITE_OMIT_AUTHORIZATION
3035 ".auth ON|OFF Show authorizer callbacks\n"
3037 ".backup ?DB? FILE Backup DB (default \"main\") to FILE\n"
3038 ".bail on|off Stop after hitting an error. Default OFF\n"
3039 ".binary on|off Turn binary output on or off. Default OFF\n"
3040 ".cd DIRECTORY Change the working directory to DIRECTORY\n"
3041 ".changes on|off Show number of rows changed by SQL\n"
3042 ".check GLOB Fail if output since .testcase does not match\n"
3043 ".clone NEWDB Clone data into NEWDB from the existing database\n"
3044 ".databases List names and files of attached databases\n"
3045 ".dbinfo ?DB? Show status information about the database\n"
3046 ".dump ?TABLE? ... Dump the database in an SQL text format\n"
3047 " If TABLE specified, only dump tables matching\n"
3048 " LIKE pattern TABLE.\n"
3049 ".echo on|off Turn command echo on or off\n"
3050 ".eqp on|off|full Enable or disable automatic EXPLAIN QUERY PLAN\n"
3051 ".exit Exit this program\n"
3052 ".expert EXPERIMENTAL. Suggest indexes for specified queries\n"
3053 /* Because explain mode comes on automatically now, the ".explain" mode
3054 ** is removed from the help screen. It is still supported for legacy, however */
3055 /*".explain ?on|off|auto? Turn EXPLAIN output mode on or off or to automatic\n"*/
3056 ".fullschema ?--indent? Show schema and the content of sqlite_stat tables\n"
3057 ".headers on|off Turn display of headers on or off\n"
3058 ".help Show this message\n"
3059 ".import FILE TABLE Import data from FILE into TABLE\n"
3060 #ifndef SQLITE_OMIT_TEST_CONTROL
3061 ".imposter INDEX TABLE Create imposter table TABLE on index INDEX\n"
3063 ".indexes ?TABLE? Show names of all indexes\n"
3064 " If TABLE specified, only show indexes for tables\n"
3065 " matching LIKE pattern TABLE.\n"
3066 #ifdef SQLITE_ENABLE_IOTRACE
3067 ".iotrace FILE Enable I/O diagnostic logging to FILE\n"
3069 ".limit ?LIMIT? ?VAL? Display or change the value of an SQLITE_LIMIT\n"
3070 ".lint OPTIONS Report potential schema issues. Options:\n"
3071 " fkey-indexes Find missing foreign key indexes\n"
3072 #ifndef SQLITE_OMIT_LOAD_EXTENSION
3073 ".load FILE ?ENTRY? Load an extension library\n"
3075 ".log FILE|off Turn logging on or off. FILE can be stderr/stdout\n"
3076 ".mode MODE ?TABLE? Set output mode where MODE is one of:\n"
3077 " ascii Columns/rows delimited by 0x1F and 0x1E\n"
3078 " csv Comma-separated values\n"
3079 " column Left-aligned columns. (See .width)\n"
3080 " html HTML <table> code\n"
3081 " insert SQL insert statements for TABLE\n"
3082 " line One value per line\n"
3083 " list Values delimited by \"|\"\n"
3084 " quote Escape answers as for SQL\n"
3085 " tabs Tab-separated values\n"
3086 " tcl TCL list elements\n"
3087 ".nullvalue STRING Use STRING in place of NULL values\n"
3088 ".once FILENAME Output for the next SQL command only to FILENAME\n"
3089 ".open ?OPTIONS? ?FILE? Close existing database and reopen FILE\n"
3090 " The --new option starts with an empty file\n"
3091 ".output ?FILENAME? Send output to FILENAME or stdout\n"
3092 ".print STRING... Print literal STRING\n"
3093 ".prompt MAIN CONTINUE Replace the standard prompts\n"
3094 ".quit Exit this program\n"
3095 ".read FILENAME Execute SQL in FILENAME\n"
3096 ".restore ?DB? FILE Restore content of DB (default \"main\") from FILE\n"
3097 ".save FILE Write in-memory database into FILE\n"
3098 ".scanstats on|off Turn sqlite3_stmt_scanstatus() metrics on or off\n"
3099 ".schema ?PATTERN? Show the CREATE statements matching PATTERN\n"
3100 " Add --indent for pretty-printing\n"
3101 ".selftest ?--init? Run tests defined in the SELFTEST table\n"
3102 ".separator COL ?ROW? Change the column separator and optionally the row\n"
3103 " separator for both the output mode and .import\n"
3104 #if defined(SQLITE_ENABLE_SESSION)
3105 ".session CMD ... Create or control sessions\n"
3107 ".sha3sum ?OPTIONS...? Compute a SHA3 hash of database content\n"
3108 ".shell CMD ARGS... Run CMD ARGS... in a system shell\n"
3109 ".show Show the current values for various settings\n"
3110 ".stats ?on|off? Show stats or turn stats on or off\n"
3111 ".system CMD ARGS... Run CMD ARGS... in a system shell\n"
3112 ".tables ?TABLE? List names of tables\n"
3113 " If TABLE specified, only list tables matching\n"
3114 " LIKE pattern TABLE.\n"
3115 ".testcase NAME Begin redirecting output to 'testcase-out.txt'\n"
3116 ".timeout MS Try opening locked tables for MS milliseconds\n"
3117 ".timer on|off Turn SQL timer on or off\n"
3118 ".trace FILE|off Output each SQL statement as it is run\n"
3119 ".vfsinfo ?AUX? Information about the top-level VFS\n"
3120 ".vfslist List all available VFSes\n"
3121 ".vfsname ?AUX? Print the name of the VFS stack\n"
3122 ".width NUM1 NUM2 ... Set column widths for \"column\" mode\n"
3123 " Negative values right-justify\n"
3126 #if defined(SQLITE_ENABLE_SESSION)
3128 ** Print help information for the ".sessions" command
3130 void session_help(ShellState
*p
){
3132 ".session ?NAME? SUBCOMMAND ?ARGS...?\n"
3133 "If ?NAME? is omitted, the first defined session is used.\n"
3135 " attach TABLE Attach TABLE\n"
3136 " changeset FILE Write a changeset into FILE\n"
3137 " close Close one session\n"
3138 " enable ?BOOLEAN? Set or query the enable bit\n"
3139 " filter GLOB... Reject tables matching GLOBs\n"
3140 " indirect ?BOOLEAN? Mark or query the indirect status\n"
3141 " isempty Query whether the session is empty\n"
3142 " list List currently open session names\n"
3143 " open DB NAME Open a new session on DB\n"
3144 " patchset FILE Write a patchset into FILE\n"
3150 /* Forward reference */
3151 static int process_input(ShellState
*p
, FILE *in
);
3154 ** Read the content of file zName into memory obtained from sqlite3_malloc64()
3155 ** and return a pointer to the buffer. The caller is responsible for freeing
3158 ** If parameter pnByte is not NULL, (*pnByte) is set to the number of bytes
3161 ** For convenience, a nul-terminator byte is always appended to the data read
3162 ** from the file before the buffer is returned. This byte is not included in
3163 ** the final value of (*pnByte), if applicable.
3165 ** NULL is returned if any error is encountered. The final value of *pnByte
3166 ** is undefined in this case.
3168 static char *readFile(const char *zName
, int *pnByte
){
3169 FILE *in
= fopen(zName
, "rb");
3173 if( in
==0 ) return 0;
3174 fseek(in
, 0, SEEK_END
);
3177 pBuf
= sqlite3_malloc64( nIn
+1 );
3178 if( pBuf
==0 ) return 0;
3179 nRead
= fread(pBuf
, nIn
, 1, in
);
3186 if( pnByte
) *pnByte
= nIn
;
3190 #if defined(SQLITE_ENABLE_SESSION)
3192 ** Close a single OpenSession object and release all of its associated
3195 static void session_close(OpenSession
*pSession
){
3197 sqlite3session_delete(pSession
->p
);
3198 sqlite3_free(pSession
->zName
);
3199 for(i
=0; i
<pSession
->nFilter
; i
++){
3200 sqlite3_free(pSession
->azFilter
[i
]);
3202 sqlite3_free(pSession
->azFilter
);
3203 memset(pSession
, 0, sizeof(OpenSession
));
3208 ** Close all OpenSession objects and release all associated resources.
3210 #if defined(SQLITE_ENABLE_SESSION)
3211 static void session_close_all(ShellState
*p
){
3213 for(i
=0; i
<p
->nSession
; i
++){
3214 session_close(&p
->aSession
[i
]);
3219 # define session_close_all(X)
3223 ** Implementation of the xFilter function for an open session. Omit
3224 ** any tables named by ".session filter" but let all other table through.
3226 #if defined(SQLITE_ENABLE_SESSION)
3227 static int session_filter(void *pCtx
, const char *zTab
){
3228 OpenSession
*pSession
= (OpenSession
*)pCtx
;
3230 for(i
=0; i
<pSession
->nFilter
; i
++){
3231 if( sqlite3_strglob(pSession
->azFilter
[i
], zTab
)==0 ) return 0;
3238 ** Try to deduce the type of file for zName based on its content. Return
3239 ** one of the SHELL_OPEN_* constants.
3241 static int deduceDatabaseType(const char *zName
){
3242 FILE *f
= fopen(zName
, "rb");
3244 int rc
= SHELL_OPEN_UNSPEC
;
3246 if( f
==0 ) return SHELL_OPEN_NORMAL
;
3247 fseek(f
, -25, SEEK_END
);
3248 n
= fread(zBuf
, 25, 1, f
);
3249 if( n
==1 && memcmp(zBuf
, "Start-Of-SQLite3-", 17)==0 ){
3250 rc
= SHELL_OPEN_APPENDVFS
;
3252 fseek(f
, -22, SEEK_END
);
3253 n
= fread(zBuf
, 22, 1, f
);
3254 if( n
==1 && zBuf
[0]==0x50 && zBuf
[1]==0x4b && zBuf
[2]==0x05
3256 rc
= SHELL_OPEN_ZIPFILE
;
3264 ** Make sure the database is open. If it is not, then open it. If
3265 ** the database fails to open, print an error message and exit.
3267 static void open_db(ShellState
*p
, int keepAlive
){
3269 sqlite3_initialize();
3270 if( p
->openMode
==SHELL_OPEN_UNSPEC
&& access(p
->zDbFilename
,0)==0 ){
3271 p
->openMode
= deduceDatabaseType(p
->zDbFilename
);
3273 switch( p
->openMode
){
3274 case SHELL_OPEN_APPENDVFS
: {
3275 sqlite3_open_v2(p
->zDbFilename
, &p
->db
,
3276 SQLITE_OPEN_READWRITE
|SQLITE_OPEN_CREATE
, "apndvfs");
3279 case SHELL_OPEN_ZIPFILE
: {
3280 sqlite3_open(":memory:", &p
->db
);
3283 case SHELL_OPEN_UNSPEC
:
3284 case SHELL_OPEN_NORMAL
: {
3285 sqlite3_open(p
->zDbFilename
, &p
->db
);
3290 if( p
->db
==0 || SQLITE_OK
!=sqlite3_errcode(p
->db
) ){
3291 utf8_printf(stderr
,"Error: unable to open database \"%s\": %s\n",
3292 p
->zDbFilename
, sqlite3_errmsg(p
->db
));
3293 if( keepAlive
) return;
3296 #ifndef SQLITE_OMIT_LOAD_EXTENSION
3297 sqlite3_enable_load_extension(p
->db
, 1);
3299 sqlite3_fileio_init(p
->db
, 0, 0);
3300 sqlite3_shathree_init(p
->db
, 0, 0);
3301 sqlite3_completion_init(p
->db
, 0, 0);
3302 #ifdef SQLITE_HAVE_ZLIB
3303 sqlite3_zipfile_init(p
->db
, 0, 0);
3304 sqlite3_sqlar_init(p
->db
, 0, 0);
3306 sqlite3_create_function(p
->db
, "shell_add_schema", 3, SQLITE_UTF8
, 0,
3307 shellAddSchemaName
, 0, 0);
3308 sqlite3_create_function(p
->db
, "shell_module_schema", 1, SQLITE_UTF8
, 0,
3309 shellModuleSchema
, 0, 0);
3310 if( p
->openMode
==SHELL_OPEN_ZIPFILE
){
3311 char *zSql
= sqlite3_mprintf(
3312 "CREATE VIRTUAL TABLE zip USING zipfile(%Q);", p
->zDbFilename
);
3313 sqlite3_exec(p
->db
, zSql
, 0, 0, 0);
3319 #if HAVE_READLINE || HAVE_EDITLINE
3321 ** Readline completion callbacks
3323 static char *readline_completion_generator(const char *text
, int state
){
3324 static sqlite3_stmt
*pStmt
= 0;
3328 sqlite3_finalize(pStmt
);
3329 zSql
= sqlite3_mprintf("SELECT DISTINCT candidate COLLATE nocase"
3330 " FROM completion(%Q) ORDER BY 1", text
);
3331 sqlite3_prepare_v2(globalDb
, zSql
, -1, &pStmt
, 0);
3334 if( sqlite3_step(pStmt
)==SQLITE_ROW
){
3335 zRet
= strdup((const char*)sqlite3_column_text(pStmt
, 0));
3337 sqlite3_finalize(pStmt
);
3343 static char **readline_completion(const char *zText
, int iStart
, int iEnd
){
3344 rl_attempted_completion_over
= 1;
3345 return rl_completion_matches(zText
, readline_completion_generator
);
3348 #elif HAVE_LINENOISE
3350 ** Linenoise completion callback
3352 static void linenoise_completion(const char *zLine
, linenoiseCompletions
*lc
){
3353 int nLine
= strlen30(zLine
);
3355 sqlite3_stmt
*pStmt
= 0;
3359 if( nLine
>sizeof(zBuf
)-30 ) return;
3360 if( zLine
[0]=='.' ) return;
3361 for(i
=nLine
-1; i
>=0 && (isalnum(zLine
[i
]) || zLine
[i
]=='_'); i
--){}
3362 if( i
==nLine
-1 ) return;
3364 memcpy(zBuf
, zLine
, iStart
);
3365 zSql
= sqlite3_mprintf("SELECT DISTINCT candidate COLLATE nocase"
3366 " FROM completion(%Q,%Q) ORDER BY 1",
3367 &zLine
[iStart
], zLine
);
3368 sqlite3_prepare_v2(globalDb
, zSql
, -1, &pStmt
, 0);
3370 sqlite3_exec(globalDb
, "PRAGMA page_count", 0, 0, 0); /* Load the schema */
3371 while( sqlite3_step(pStmt
)==SQLITE_ROW
){
3372 const char *zCompletion
= (const char*)sqlite3_column_text(pStmt
, 0);
3373 int nCompletion
= sqlite3_column_bytes(pStmt
, 0);
3374 if( iStart
+nCompletion
< sizeof(zBuf
)-1 ){
3375 memcpy(zBuf
+iStart
, zCompletion
, nCompletion
+1);
3376 linenoiseAddCompletion(lc
, zBuf
);
3379 sqlite3_finalize(pStmt
);
3384 ** Do C-language style dequoting.
3390 ** \v -> vertical tab
3392 ** \r -> carriage return
3397 ** \NNN -> ascii character NNN in octal
3399 static void resolve_backslashes(char *z
){
3402 while( *z
&& *z
!='\\' ) z
++;
3403 for(i
=j
=0; (c
= z
[i
])!=0; i
++, j
++){
3404 if( c
=='\\' && z
[i
+1]!=0 ){
3422 }else if( c
=='\'' ){
3424 }else if( c
=='\\' ){
3426 }else if( c
>='0' && c
<='7' ){
3428 if( z
[i
+1]>='0' && z
[i
+1]<='7' ){
3430 c
= (c
<<3) + z
[i
] - '0';
3431 if( z
[i
+1]>='0' && z
[i
+1]<='7' ){
3433 c
= (c
<<3) + z
[i
] - '0';
3444 ** Interpret zArg as either an integer or a boolean value. Return 1 or 0
3445 ** for TRUE and FALSE. Return the integer value if appropriate.
3447 static int booleanValue(const char *zArg
){
3449 if( zArg
[0]=='0' && zArg
[1]=='x' ){
3450 for(i
=2; hexDigitValue(zArg
[i
])>=0; i
++){}
3452 for(i
=0; zArg
[i
]>='0' && zArg
[i
]<='9'; i
++){}
3454 if( i
>0 && zArg
[i
]==0 ) return (int)(integerValue(zArg
) & 0xffffffff);
3455 if( sqlite3_stricmp(zArg
, "on")==0 || sqlite3_stricmp(zArg
,"yes")==0 ){
3458 if( sqlite3_stricmp(zArg
, "off")==0 || sqlite3_stricmp(zArg
,"no")==0 ){
3461 utf8_printf(stderr
, "ERROR: Not a boolean value: \"%s\". Assuming \"no\".\n",
3467 ** Set or clear a shell flag according to a boolean value.
3469 static void setOrClearFlag(ShellState
*p
, unsigned mFlag
, const char *zArg
){
3470 if( booleanValue(zArg
) ){
3471 ShellSetFlag(p
, mFlag
);
3473 ShellClearFlag(p
, mFlag
);
3478 ** Close an output file, assuming it is not stderr or stdout
3480 static void output_file_close(FILE *f
){
3481 if( f
&& f
!=stdout
&& f
!=stderr
) fclose(f
);
3485 ** Try to open an output file. The names "stdout" and "stderr" are
3486 ** recognized and do the right thing. NULL is returned if the output
3487 ** filename is "off".
3489 static FILE *output_file_open(const char *zFile
){
3491 if( strcmp(zFile
,"stdout")==0 ){
3493 }else if( strcmp(zFile
, "stderr")==0 ){
3495 }else if( strcmp(zFile
, "off")==0 ){
3498 f
= fopen(zFile
, "wb");
3500 utf8_printf(stderr
, "Error: cannot open \"%s\"\n", zFile
);
3506 #if !defined(SQLITE_UNTESTABLE)
3507 #if !defined(SQLITE_OMIT_TRACE) && !defined(SQLITE_OMIT_FLOATING_POINT)
3509 ** A routine for handling output from sqlite3_trace().
3511 static int sql_trace_callback(
3517 FILE *f
= (FILE*)pArg
;
3518 UNUSED_PARAMETER(mType
);
3519 UNUSED_PARAMETER(pP
);
3521 const char *z
= (const char*)pX
;
3522 int i
= strlen30(z
);
3523 while( i
>0 && z
[i
-1]==';' ){ i
--; }
3524 utf8_printf(f
, "%.*s;\n", i
, z
);
3532 ** A no-op routine that runs with the ".breakpoint" doc-command. This is
3533 ** a useful spot to set a debugger breakpoint.
3535 static void test_breakpoint(void){
3536 static int nCall
= 0;
3541 ** An object used to read a CSV and other files for import.
3543 typedef struct ImportCtx ImportCtx
;
3545 const char *zFile
; /* Name of the input file */
3546 FILE *in
; /* Read the CSV text from this input stream */
3547 char *z
; /* Accumulated text for a field */
3548 int n
; /* Number of bytes in z */
3549 int nAlloc
; /* Space allocated for z[] */
3550 int nLine
; /* Current line number */
3551 int bNotFirst
; /* True if one or more bytes already read */
3552 int cTerm
; /* Character that terminated the most recent field */
3553 int cColSep
; /* The column separator character. (Usually ",") */
3554 int cRowSep
; /* The row separator character. (Usually "\n") */
3557 /* Append a single byte to z[] */
3558 static void import_append_char(ImportCtx
*p
, int c
){
3559 if( p
->n
+1>=p
->nAlloc
){
3560 p
->nAlloc
+= p
->nAlloc
+ 100;
3561 p
->z
= sqlite3_realloc64(p
->z
, p
->nAlloc
);
3563 raw_printf(stderr
, "out of memory\n");
3567 p
->z
[p
->n
++] = (char)c
;
3570 /* Read a single field of CSV text. Compatible with rfc4180 and extended
3571 ** with the option of having a separator other than ",".
3573 ** + Input comes from p->in.
3574 ** + Store results in p->z of length p->n. Space to hold p->z comes
3575 ** from sqlite3_malloc64().
3576 ** + Use p->cSep as the column separator. The default is ",".
3577 ** + Use p->rSep as the row separator. The default is "\n".
3578 ** + Keep track of the line number in p->nLine.
3579 ** + Store the character that terminates the field in p->cTerm. Store
3580 ** EOF on end-of-file.
3581 ** + Report syntax errors on stderr
3583 static char *SQLITE_CDECL
csv_read_one_field(ImportCtx
*p
){
3585 int cSep
= p
->cColSep
;
3586 int rSep
= p
->cRowSep
;
3589 if( c
==EOF
|| seenInterrupt
){
3595 int startLine
= p
->nLine
;
3600 if( c
==rSep
) p
->nLine
++;
3607 if( (c
==cSep
&& pc
==cQuote
)
3608 || (c
==rSep
&& pc
==cQuote
)
3609 || (c
==rSep
&& pc
=='\r' && ppc
==cQuote
)
3610 || (c
==EOF
&& pc
==cQuote
)
3612 do{ p
->n
--; }while( p
->z
[p
->n
]!=cQuote
);
3616 if( pc
==cQuote
&& c
!='\r' ){
3617 utf8_printf(stderr
, "%s:%d: unescaped %c character\n",
3618 p
->zFile
, p
->nLine
, cQuote
);
3621 utf8_printf(stderr
, "%s:%d: unterminated %c-quoted field\n",
3622 p
->zFile
, startLine
, cQuote
);
3626 import_append_char(p
, c
);
3631 /* If this is the first field being parsed and it begins with the
3632 ** UTF-8 BOM (0xEF BB BF) then skip the BOM */
3633 if( (c
&0xff)==0xef && p
->bNotFirst
==0 ){
3634 import_append_char(p
, c
);
3636 if( (c
&0xff)==0xbb ){
3637 import_append_char(p
, c
);
3639 if( (c
&0xff)==0xbf ){
3642 return csv_read_one_field(p
);
3646 while( c
!=EOF
&& c
!=cSep
&& c
!=rSep
){
3647 import_append_char(p
, c
);
3652 if( p
->n
>0 && p
->z
[p
->n
-1]=='\r' ) p
->n
--;
3656 if( p
->z
) p
->z
[p
->n
] = 0;
3661 /* Read a single field of ASCII delimited text.
3663 ** + Input comes from p->in.
3664 ** + Store results in p->z of length p->n. Space to hold p->z comes
3665 ** from sqlite3_malloc64().
3666 ** + Use p->cSep as the column separator. The default is "\x1F".
3667 ** + Use p->rSep as the row separator. The default is "\x1E".
3668 ** + Keep track of the row number in p->nLine.
3669 ** + Store the character that terminates the field in p->cTerm. Store
3670 ** EOF on end-of-file.
3671 ** + Report syntax errors on stderr
3673 static char *SQLITE_CDECL
ascii_read_one_field(ImportCtx
*p
){
3675 int cSep
= p
->cColSep
;
3676 int rSep
= p
->cRowSep
;
3679 if( c
==EOF
|| seenInterrupt
){
3683 while( c
!=EOF
&& c
!=cSep
&& c
!=rSep
){
3684 import_append_char(p
, c
);
3691 if( p
->z
) p
->z
[p
->n
] = 0;
3696 ** Try to transfer data for table zTable. If an error is seen while
3697 ** moving forward, try to go backwards. The backwards movement won't
3698 ** work for WITHOUT ROWID tables.
3700 static void tryToCloneData(
3705 sqlite3_stmt
*pQuery
= 0;
3706 sqlite3_stmt
*pInsert
= 0;
3711 int nTable
= strlen30(zTable
);
3714 const int spinRate
= 10000;
3716 zQuery
= sqlite3_mprintf("SELECT * FROM \"%w\"", zTable
);
3717 rc
= sqlite3_prepare_v2(p
->db
, zQuery
, -1, &pQuery
, 0);
3719 utf8_printf(stderr
, "Error %d: %s on [%s]\n",
3720 sqlite3_extended_errcode(p
->db
), sqlite3_errmsg(p
->db
),
3724 n
= sqlite3_column_count(pQuery
);
3725 zInsert
= sqlite3_malloc64(200 + nTable
+ n
*3);
3727 raw_printf(stderr
, "out of memory\n");
3730 sqlite3_snprintf(200+nTable
,zInsert
,
3731 "INSERT OR IGNORE INTO \"%s\" VALUES(?", zTable
);
3732 i
= strlen30(zInsert
);
3734 memcpy(zInsert
+i
, ",?", 2);
3737 memcpy(zInsert
+i
, ");", 3);
3738 rc
= sqlite3_prepare_v2(newDb
, zInsert
, -1, &pInsert
, 0);
3740 utf8_printf(stderr
, "Error %d: %s on [%s]\n",
3741 sqlite3_extended_errcode(newDb
), sqlite3_errmsg(newDb
),
3746 while( (rc
= sqlite3_step(pQuery
))==SQLITE_ROW
){
3748 switch( sqlite3_column_type(pQuery
, i
) ){
3750 sqlite3_bind_null(pInsert
, i
+1);
3753 case SQLITE_INTEGER
: {
3754 sqlite3_bind_int64(pInsert
, i
+1, sqlite3_column_int64(pQuery
,i
));
3757 case SQLITE_FLOAT
: {
3758 sqlite3_bind_double(pInsert
, i
+1, sqlite3_column_double(pQuery
,i
));
3762 sqlite3_bind_text(pInsert
, i
+1,
3763 (const char*)sqlite3_column_text(pQuery
,i
),
3768 sqlite3_bind_blob(pInsert
, i
+1, sqlite3_column_blob(pQuery
,i
),
3769 sqlite3_column_bytes(pQuery
,i
),
3775 rc
= sqlite3_step(pInsert
);
3776 if( rc
!=SQLITE_OK
&& rc
!=SQLITE_ROW
&& rc
!=SQLITE_DONE
){
3777 utf8_printf(stderr
, "Error %d: %s\n", sqlite3_extended_errcode(newDb
),
3778 sqlite3_errmsg(newDb
));
3780 sqlite3_reset(pInsert
);
3782 if( (cnt
%spinRate
)==0 ){
3783 printf("%c\b", "|/-\\"[(cnt
/spinRate
)%4]);
3787 if( rc
==SQLITE_DONE
) break;
3788 sqlite3_finalize(pQuery
);
3789 sqlite3_free(zQuery
);
3790 zQuery
= sqlite3_mprintf("SELECT * FROM \"%w\" ORDER BY rowid DESC;",
3792 rc
= sqlite3_prepare_v2(p
->db
, zQuery
, -1, &pQuery
, 0);
3794 utf8_printf(stderr
, "Warning: cannot step \"%s\" backwards", zTable
);
3797 } /* End for(k=0...) */
3800 sqlite3_finalize(pQuery
);
3801 sqlite3_finalize(pInsert
);
3802 sqlite3_free(zQuery
);
3803 sqlite3_free(zInsert
);
3808 ** Try to transfer all rows of the schema that match zWhere. For
3809 ** each row, invoke xForEach() on the object defined by that row.
3810 ** If an error is encountered while moving forward through the
3811 ** sqlite_master table, try again moving backwards.
3813 static void tryToCloneSchema(
3817 void (*xForEach
)(ShellState
*,sqlite3
*,const char*)
3819 sqlite3_stmt
*pQuery
= 0;
3822 const unsigned char *zName
;
3823 const unsigned char *zSql
;
3826 zQuery
= sqlite3_mprintf("SELECT name, sql FROM sqlite_master"
3827 " WHERE %s", zWhere
);
3828 rc
= sqlite3_prepare_v2(p
->db
, zQuery
, -1, &pQuery
, 0);
3830 utf8_printf(stderr
, "Error: (%d) %s on [%s]\n",
3831 sqlite3_extended_errcode(p
->db
), sqlite3_errmsg(p
->db
),
3833 goto end_schema_xfer
;
3835 while( (rc
= sqlite3_step(pQuery
))==SQLITE_ROW
){
3836 zName
= sqlite3_column_text(pQuery
, 0);
3837 zSql
= sqlite3_column_text(pQuery
, 1);
3838 printf("%s... ", zName
); fflush(stdout
);
3839 sqlite3_exec(newDb
, (const char*)zSql
, 0, 0, &zErrMsg
);
3841 utf8_printf(stderr
, "Error: %s\nSQL: [%s]\n", zErrMsg
, zSql
);
3842 sqlite3_free(zErrMsg
);
3846 xForEach(p
, newDb
, (const char*)zName
);
3850 if( rc
!=SQLITE_DONE
){
3851 sqlite3_finalize(pQuery
);
3852 sqlite3_free(zQuery
);
3853 zQuery
= sqlite3_mprintf("SELECT name, sql FROM sqlite_master"
3854 " WHERE %s ORDER BY rowid DESC", zWhere
);
3855 rc
= sqlite3_prepare_v2(p
->db
, zQuery
, -1, &pQuery
, 0);
3857 utf8_printf(stderr
, "Error: (%d) %s on [%s]\n",
3858 sqlite3_extended_errcode(p
->db
), sqlite3_errmsg(p
->db
),
3860 goto end_schema_xfer
;
3862 while( (rc
= sqlite3_step(pQuery
))==SQLITE_ROW
){
3863 zName
= sqlite3_column_text(pQuery
, 0);
3864 zSql
= sqlite3_column_text(pQuery
, 1);
3865 printf("%s... ", zName
); fflush(stdout
);
3866 sqlite3_exec(newDb
, (const char*)zSql
, 0, 0, &zErrMsg
);
3868 utf8_printf(stderr
, "Error: %s\nSQL: [%s]\n", zErrMsg
, zSql
);
3869 sqlite3_free(zErrMsg
);
3873 xForEach(p
, newDb
, (const char*)zName
);
3879 sqlite3_finalize(pQuery
);
3880 sqlite3_free(zQuery
);
3884 ** Open a new database file named "zNewDb". Try to recover as much information
3885 ** as possible out of the main database (which might be corrupt) and write it
3888 static void tryToClone(ShellState
*p
, const char *zNewDb
){
3891 if( access(zNewDb
,0)==0 ){
3892 utf8_printf(stderr
, "File \"%s\" already exists.\n", zNewDb
);
3895 rc
= sqlite3_open(zNewDb
, &newDb
);
3897 utf8_printf(stderr
, "Cannot create output database: %s\n",
3898 sqlite3_errmsg(newDb
));
3900 sqlite3_exec(p
->db
, "PRAGMA writable_schema=ON;", 0, 0, 0);
3901 sqlite3_exec(newDb
, "BEGIN EXCLUSIVE;", 0, 0, 0);
3902 tryToCloneSchema(p
, newDb
, "type='table'", tryToCloneData
);
3903 tryToCloneSchema(p
, newDb
, "type!='table'", 0);
3904 sqlite3_exec(newDb
, "COMMIT;", 0, 0, 0);
3905 sqlite3_exec(p
->db
, "PRAGMA writable_schema=OFF;", 0, 0, 0);
3907 sqlite3_close(newDb
);
3911 ** Change the output file back to stdout
3913 static void output_reset(ShellState
*p
){
3914 if( p
->outfile
[0]=='|' ){
3915 #ifndef SQLITE_OMIT_POPEN
3919 output_file_close(p
->out
);
3926 ** Run an SQL command and return the single integer result.
3928 static int db_int(ShellState
*p
, const char *zSql
){
3929 sqlite3_stmt
*pStmt
;
3931 sqlite3_prepare_v2(p
->db
, zSql
, -1, &pStmt
, 0);
3932 if( pStmt
&& sqlite3_step(pStmt
)==SQLITE_ROW
){
3933 res
= sqlite3_column_int(pStmt
,0);
3935 sqlite3_finalize(pStmt
);
3940 ** Convert a 2-byte or 4-byte big-endian integer into a native integer
3942 static unsigned int get2byteInt(unsigned char *a
){
3943 return (a
[0]<<8) + a
[1];
3945 static unsigned int get4byteInt(unsigned char *a
){
3946 return (a
[0]<<24) + (a
[1]<<16) + (a
[2]<<8) + a
[3];
3950 ** Implementation of the ".info" command.
3952 ** Return 1 on error, 2 to exit, and 0 otherwise.
3954 static int shell_dbinfo_command(ShellState
*p
, int nArg
, char **azArg
){
3955 static const struct { const char *zName
; int ofst
; } aField
[] = {
3956 { "file change counter:", 24 },
3957 { "database page count:", 28 },
3958 { "freelist page count:", 36 },
3959 { "schema cookie:", 40 },
3960 { "schema format:", 44 },
3961 { "default cache size:", 48 },
3962 { "autovacuum top root:", 52 },
3963 { "incremental vacuum:", 64 },
3964 { "text encoding:", 56 },
3965 { "user version:", 60 },
3966 { "application id:", 68 },
3967 { "software version:", 96 },
3969 static const struct { const char *zName
; const char *zSql
; } aQuery
[] = {
3970 { "number of tables:",
3971 "SELECT count(*) FROM %s WHERE type='table'" },
3972 { "number of indexes:",
3973 "SELECT count(*) FROM %s WHERE type='index'" },
3974 { "number of triggers:",
3975 "SELECT count(*) FROM %s WHERE type='trigger'" },
3976 { "number of views:",
3977 "SELECT count(*) FROM %s WHERE type='view'" },
3979 "SELECT total(length(sql)) FROM %s" },
3983 char *zDb
= nArg
>=2 ? azArg
[1] : "main";
3984 sqlite3_stmt
*pStmt
= 0;
3985 unsigned char aHdr
[100];
3987 if( p
->db
==0 ) return 1;
3988 sqlite3_prepare_v2(p
->db
,"SELECT data FROM sqlite_dbpage(?1) WHERE pgno=1",
3990 sqlite3_bind_text(pStmt
, 1, zDb
, -1, SQLITE_STATIC
);
3991 if( sqlite3_step(pStmt
)==SQLITE_ROW
3992 && sqlite3_column_bytes(pStmt
,0)>100
3994 memcpy(aHdr
, sqlite3_column_blob(pStmt
,0), 100);
3995 sqlite3_finalize(pStmt
);
3997 raw_printf(stderr
, "unable to read database header\n");
3998 sqlite3_finalize(pStmt
);
4001 i
= get2byteInt(aHdr
+16);
4002 if( i
==1 ) i
= 65536;
4003 utf8_printf(p
->out
, "%-20s %d\n", "database page size:", i
);
4004 utf8_printf(p
->out
, "%-20s %d\n", "write format:", aHdr
[18]);
4005 utf8_printf(p
->out
, "%-20s %d\n", "read format:", aHdr
[19]);
4006 utf8_printf(p
->out
, "%-20s %d\n", "reserved bytes:", aHdr
[20]);
4007 for(i
=0; i
<ArraySize(aField
); i
++){
4008 int ofst
= aField
[i
].ofst
;
4009 unsigned int val
= get4byteInt(aHdr
+ ofst
);
4010 utf8_printf(p
->out
, "%-20s %u", aField
[i
].zName
, val
);
4013 if( val
==1 ) raw_printf(p
->out
, " (utf8)");
4014 if( val
==2 ) raw_printf(p
->out
, " (utf16le)");
4015 if( val
==3 ) raw_printf(p
->out
, " (utf16be)");
4018 raw_printf(p
->out
, "\n");
4021 zSchemaTab
= sqlite3_mprintf("main.sqlite_master");
4022 }else if( strcmp(zDb
,"temp")==0 ){
4023 zSchemaTab
= sqlite3_mprintf("%s", "sqlite_temp_master");
4025 zSchemaTab
= sqlite3_mprintf("\"%w\".sqlite_master", zDb
);
4027 for(i
=0; i
<ArraySize(aQuery
); i
++){
4028 char *zSql
= sqlite3_mprintf(aQuery
[i
].zSql
, zSchemaTab
);
4029 int val
= db_int(p
, zSql
);
4031 utf8_printf(p
->out
, "%-20s %d\n", aQuery
[i
].zName
, val
);
4033 sqlite3_free(zSchemaTab
);
4038 ** Print the current sqlite3_errmsg() value to stderr and return 1.
4040 static int shellDatabaseError(sqlite3
*db
){
4041 const char *zErr
= sqlite3_errmsg(db
);
4042 utf8_printf(stderr
, "Error: %s\n", zErr
);
4047 ** Print an out-of-memory message to stderr and return 1.
4049 static int shellNomemError(void){
4050 raw_printf(stderr
, "Error: out of memory\n");
4055 ** Compare the pattern in zGlob[] against the text in z[]. Return TRUE
4056 ** if they match and FALSE (0) if they do not match.
4060 ** '*' Matches any sequence of zero or more characters.
4062 ** '?' Matches exactly one character.
4064 ** [...] Matches one character from the enclosed list of
4067 ** [^...] Matches one character not in the enclosed list.
4069 ** '#' Matches any sequence of one or more digits with an
4070 ** optional + or - sign in front
4072 ** ' ' Any span of whitespace matches any other span of
4075 ** Extra whitespace at the end of z[] is ignored.
4077 static int testcase_glob(const char *zGlob
, const char *z
){
4082 while( (c
= (*(zGlob
++)))!=0 ){
4084 if( !IsSpace(*z
) ) return 0;
4085 while( IsSpace(*zGlob
) ) zGlob
++;
4086 while( IsSpace(*z
) ) z
++;
4088 while( (c
=(*(zGlob
++))) == '*' || c
=='?' ){
4089 if( c
=='?' && (*(z
++))==0 ) return 0;
4094 while( *z
&& testcase_glob(zGlob
-1,z
)==0 ){
4099 while( (c2
= (*(z
++)))!=0 ){
4102 if( c2
==0 ) return 0;
4104 if( testcase_glob(zGlob
,z
) ) return 1;
4108 if( (*(z
++))==0 ) return 0;
4114 if( c
==0 ) return 0;
4121 if( c
==']' ) seen
= 1;
4124 while( c2
&& c2
!=']' ){
4125 if( c2
=='-' && zGlob
[0]!=']' && zGlob
[0]!=0 && prior_c
>0 ){
4127 if( c
>=prior_c
&& c
<=c2
) seen
= 1;
4137 if( c2
==0 || (seen
^ invert
)==0 ) return 0;
4139 if( (z
[0]=='-' || z
[0]=='+') && IsDigit(z
[1]) ) z
++;
4140 if( !IsDigit(z
[0]) ) return 0;
4142 while( IsDigit(z
[0]) ){ z
++; }
4144 if( c
!=(*(z
++)) ) return 0;
4147 while( IsSpace(*z
) ){ z
++; }
4153 ** Compare the string as a command-line option with either one or two
4154 ** initial "-" characters.
4156 static int optionMatch(const char *zStr
, const char *zOpt
){
4157 if( zStr
[0]!='-' ) return 0;
4159 if( zStr
[0]=='-' ) zStr
++;
4160 return strcmp(zStr
, zOpt
)==0;
4166 int shellDeleteFile(const char *zFilename
){
4169 wchar_t *z
= sqlite3_win32_utf8_to_unicode(zFilename
);
4173 rc
= unlink(zFilename
);
4180 ** The implementation of SQL scalar function fkey_collate_clause(), used
4181 ** by the ".lint fkey-indexes" command. This scalar function is always
4182 ** called with four arguments - the parent table name, the parent column name,
4183 ** the child table name and the child column name.
4185 ** fkey_collate_clause('parent-tab', 'parent-col', 'child-tab', 'child-col')
4187 ** If either of the named tables or columns do not exist, this function
4188 ** returns an empty string. An empty string is also returned if both tables
4189 ** and columns exist but have the same default collation sequence. Or,
4190 ** if both exist but the default collation sequences are different, this
4191 ** function returns the string " COLLATE <parent-collation>", where
4192 ** <parent-collation> is the default collation sequence of the parent column.
4194 static void shellFkeyCollateClause(
4195 sqlite3_context
*pCtx
,
4197 sqlite3_value
**apVal
4199 sqlite3
*db
= sqlite3_context_db_handle(pCtx
);
4200 const char *zParent
;
4201 const char *zParentCol
;
4202 const char *zParentSeq
;
4204 const char *zChildCol
;
4205 const char *zChildSeq
= 0; /* Initialize to avoid false-positive warning */
4209 zParent
= (const char*)sqlite3_value_text(apVal
[0]);
4210 zParentCol
= (const char*)sqlite3_value_text(apVal
[1]);
4211 zChild
= (const char*)sqlite3_value_text(apVal
[2]);
4212 zChildCol
= (const char*)sqlite3_value_text(apVal
[3]);
4214 sqlite3_result_text(pCtx
, "", -1, SQLITE_STATIC
);
4215 rc
= sqlite3_table_column_metadata(
4216 db
, "main", zParent
, zParentCol
, 0, &zParentSeq
, 0, 0, 0
4218 if( rc
==SQLITE_OK
){
4219 rc
= sqlite3_table_column_metadata(
4220 db
, "main", zChild
, zChildCol
, 0, &zChildSeq
, 0, 0, 0
4224 if( rc
==SQLITE_OK
&& sqlite3_stricmp(zParentSeq
, zChildSeq
) ){
4225 char *z
= sqlite3_mprintf(" COLLATE %s", zParentSeq
);
4226 sqlite3_result_text(pCtx
, z
, -1, SQLITE_TRANSIENT
);
4233 ** The implementation of dot-command ".lint fkey-indexes".
4235 static int lintFkeyIndexes(
4236 ShellState
*pState
, /* Current shell tool state */
4237 char **azArg
, /* Array of arguments passed to dot command */
4238 int nArg
/* Number of entries in azArg[] */
4240 sqlite3
*db
= pState
->db
; /* Database handle to query "main" db of */
4241 FILE *out
= pState
->out
; /* Stream to write non-error output to */
4242 int bVerbose
= 0; /* If -verbose is present */
4243 int bGroupByParent
= 0; /* If -groupbyparent is present */
4244 int i
; /* To iterate through azArg[] */
4245 const char *zIndent
= ""; /* How much to indent CREATE INDEX by */
4246 int rc
; /* Return code */
4247 sqlite3_stmt
*pSql
= 0; /* Compiled version of SQL statement below */
4250 ** This SELECT statement returns one row for each foreign key constraint
4251 ** in the schema of the main database. The column values are:
4253 ** 0. The text of an SQL statement similar to:
4255 ** "EXPLAIN QUERY PLAN SELECT 1 FROM child_table WHERE child_key=?"
4257 ** This SELECT is similar to the one that the foreign keys implementation
4258 ** needs to run internally on child tables. If there is an index that can
4259 ** be used to optimize this query, then it can also be used by the FK
4260 ** implementation to optimize DELETE or UPDATE statements on the parent
4263 ** 1. A GLOB pattern suitable for sqlite3_strglob(). If the plan output by
4264 ** the EXPLAIN QUERY PLAN command matches this pattern, then the schema
4265 ** contains an index that can be used to optimize the query.
4267 ** 2. Human readable text that describes the child table and columns. e.g.
4269 ** "child_table(child_key1, child_key2)"
4271 ** 3. Human readable text that describes the parent table and columns. e.g.
4273 ** "parent_table(parent_key1, parent_key2)"
4275 ** 4. A full CREATE INDEX statement for an index that could be used to
4276 ** optimize DELETE or UPDATE statements on the parent table. e.g.
4278 ** "CREATE INDEX child_table_child_key ON child_table(child_key)"
4280 ** 5. The name of the parent table.
4282 ** These six values are used by the C logic below to generate the report.
4286 " 'EXPLAIN QUERY PLAN SELECT 1 FROM ' || quote(s.name) || ' WHERE '"
4287 " || group_concat(quote(s.name) || '.' || quote(f.[from]) || '=?' "
4288 " || fkey_collate_clause("
4289 " f.[table], COALESCE(f.[to], p.[name]), s.name, f.[from]),' AND ')"
4291 " 'SEARCH TABLE ' || s.name || ' USING COVERING INDEX*('"
4292 " || group_concat('*=?', ' AND ') || ')'"
4294 " s.name || '(' || group_concat(f.[from], ', ') || ')'"
4296 " f.[table] || '(' || group_concat(COALESCE(f.[to], p.[name])) || ')'"
4298 " 'CREATE INDEX ' || quote(s.name ||'_'|| group_concat(f.[from], '_'))"
4299 " || ' ON ' || quote(s.name) || '('"
4300 " || group_concat(quote(f.[from]) ||"
4301 " fkey_collate_clause("
4302 " f.[table], COALESCE(f.[to], p.[name]), s.name, f.[from]), ', ')"
4306 "FROM sqlite_master AS s, pragma_foreign_key_list(s.name) AS f "
4307 "LEFT JOIN pragma_table_info AS p ON (pk-1=seq AND p.arg=f.[table]) "
4308 "GROUP BY s.name, f.id "
4309 "ORDER BY (CASE WHEN ? THEN f.[table] ELSE s.name END)"
4311 const char *zGlobIPK
= "SEARCH TABLE * USING INTEGER PRIMARY KEY (rowid=?)";
4313 for(i
=2; i
<nArg
; i
++){
4314 int n
= strlen30(azArg
[i
]);
4315 if( n
>1 && sqlite3_strnicmp("-verbose", azArg
[i
], n
)==0 ){
4318 else if( n
>1 && sqlite3_strnicmp("-groupbyparent", azArg
[i
], n
)==0 ){
4323 raw_printf(stderr
, "Usage: %s %s ?-verbose? ?-groupbyparent?\n",
4326 return SQLITE_ERROR
;
4330 /* Register the fkey_collate_clause() SQL function */
4331 rc
= sqlite3_create_function(db
, "fkey_collate_clause", 4, SQLITE_UTF8
,
4332 0, shellFkeyCollateClause
, 0, 0
4336 if( rc
==SQLITE_OK
){
4337 rc
= sqlite3_prepare_v2(db
, zSql
, -1, &pSql
, 0);
4339 if( rc
==SQLITE_OK
){
4340 sqlite3_bind_int(pSql
, 1, bGroupByParent
);
4343 if( rc
==SQLITE_OK
){
4346 while( SQLITE_ROW
==sqlite3_step(pSql
) ){
4348 sqlite3_stmt
*pExplain
= 0;
4349 const char *zEQP
= (const char*)sqlite3_column_text(pSql
, 0);
4350 const char *zGlob
= (const char*)sqlite3_column_text(pSql
, 1);
4351 const char *zFrom
= (const char*)sqlite3_column_text(pSql
, 2);
4352 const char *zTarget
= (const char*)sqlite3_column_text(pSql
, 3);
4353 const char *zCI
= (const char*)sqlite3_column_text(pSql
, 4);
4354 const char *zParent
= (const char*)sqlite3_column_text(pSql
, 5);
4356 rc
= sqlite3_prepare_v2(db
, zEQP
, -1, &pExplain
, 0);
4357 if( rc
!=SQLITE_OK
) break;
4358 if( SQLITE_ROW
==sqlite3_step(pExplain
) ){
4359 const char *zPlan
= (const char*)sqlite3_column_text(pExplain
, 3);
4361 0==sqlite3_strglob(zGlob
, zPlan
)
4362 || 0==sqlite3_strglob(zGlobIPK
, zPlan
)
4365 rc
= sqlite3_finalize(pExplain
);
4366 if( rc
!=SQLITE_OK
) break;
4369 raw_printf(stderr
, "Error: internal error");
4373 && (bVerbose
|| res
==0)
4374 && (zPrev
==0 || sqlite3_stricmp(zParent
, zPrev
))
4376 raw_printf(out
, "-- Parent table %s\n", zParent
);
4377 sqlite3_free(zPrev
);
4378 zPrev
= sqlite3_mprintf("%s", zParent
);
4382 raw_printf(out
, "%s%s --> %s\n", zIndent
, zCI
, zTarget
);
4383 }else if( bVerbose
){
4384 raw_printf(out
, "%s/* no extra indexes required for %s -> %s */\n",
4385 zIndent
, zFrom
, zTarget
4390 sqlite3_free(zPrev
);
4392 if( rc
!=SQLITE_OK
){
4393 raw_printf(stderr
, "%s\n", sqlite3_errmsg(db
));
4396 rc2
= sqlite3_finalize(pSql
);
4397 if( rc
==SQLITE_OK
&& rc2
!=SQLITE_OK
){
4399 raw_printf(stderr
, "%s\n", sqlite3_errmsg(db
));
4402 raw_printf(stderr
, "%s\n", sqlite3_errmsg(db
));
4409 ** Implementation of ".lint" dot command.
4411 static int lintDotCommand(
4412 ShellState
*pState
, /* Current shell tool state */
4413 char **azArg
, /* Array of arguments passed to dot command */
4414 int nArg
/* Number of entries in azArg[] */
4417 n
= (nArg
>=2 ? strlen30(azArg
[1]) : 0);
4418 if( n
<1 || sqlite3_strnicmp(azArg
[1], "fkey-indexes", n
) ) goto usage
;
4419 return lintFkeyIndexes(pState
, azArg
, nArg
);
4422 raw_printf(stderr
, "Usage %s sub-command ?switches...?\n", azArg
[0]);
4423 raw_printf(stderr
, "Where sub-commands are:\n");
4424 raw_printf(stderr
, " fkey-indexes\n");
4425 return SQLITE_ERROR
;
4428 #if !defined(SQLITE_OMIT_VIRTUALTABLE) && defined(SQLITE_HAVE_ZLIB)
4429 /*********************************************************************************
4430 ** The ".archive" or ".ar" command.
4432 static void shellPrepare(
4436 sqlite3_stmt
**ppStmt
4439 if( *pRc
==SQLITE_OK
){
4440 int rc
= sqlite3_prepare_v2(db
, zSql
, -1, ppStmt
, 0);
4441 if( rc
!=SQLITE_OK
){
4442 raw_printf(stderr
, "sql error: %s (%d)\n",
4443 sqlite3_errmsg(db
), sqlite3_errcode(db
)
4450 static void shellPreparePrintf(
4453 sqlite3_stmt
**ppStmt
,
4458 if( *pRc
==SQLITE_OK
){
4462 z
= sqlite3_vmprintf(zFmt
, ap
);
4464 *pRc
= SQLITE_NOMEM
;
4466 shellPrepare(db
, pRc
, z
, ppStmt
);
4472 static void shellFinalize(
4477 sqlite3
*db
= sqlite3_db_handle(pStmt
);
4478 int rc
= sqlite3_finalize(pStmt
);
4479 if( *pRc
==SQLITE_OK
){
4480 if( rc
!=SQLITE_OK
){
4481 raw_printf(stderr
, "SQL error: %s\n", sqlite3_errmsg(db
));
4488 static void shellReset(
4492 int rc
= sqlite3_reset(pStmt
);
4493 if( *pRc
==SQLITE_OK
){
4494 if( rc
!=SQLITE_OK
){
4495 sqlite3
*db
= sqlite3_db_handle(pStmt
);
4496 raw_printf(stderr
, "SQL error: %s\n", sqlite3_errmsg(db
));
4502 ** Structure representing a single ".ar" command.
4504 typedef struct ArCommand ArCommand
;
4506 u8 eCmd
; /* An AR_CMD_* value */
4507 u8 bVerbose
; /* True if --verbose */
4508 u8 bZip
; /* True if the archive is a ZIP */
4509 u8 bDryRun
; /* True if --dry-run */
4510 u8 bAppend
; /* True if --append */
4511 int nArg
; /* Number of command arguments */
4512 char *zSrcTable
; /* "sqlar", "zipfile($file)" or "zip" */
4513 const char *zFile
; /* --file argument, or NULL */
4514 const char *zDir
; /* --directory argument, or NULL */
4515 char **azArg
; /* Array of command arguments */
4516 ShellState
*p
; /* Shell state */
4517 sqlite3
*db
; /* Database containing the archive */
4521 ** Print a usage message for the .ar command to stderr and return SQLITE_ERROR.
4523 static int arUsage(FILE *f
){
4526 "Usage: .ar [OPTION...] [FILE...]\n"
4527 "The .ar command manages sqlar archives.\n"
4530 " .ar -cf archive.sar foo bar # Create archive.sar from files foo and bar\n"
4531 " .ar -tf archive.sar # List members of archive.sar\n"
4532 " .ar -xvf archive.sar # Verbosely extract files from archive.sar\n"
4534 "Each command line must feature exactly one command option:\n"
4535 " -c, --create Create a new archive\n"
4536 " -u, --update Update or add files to an existing archive\n"
4537 " -t, --list List contents of archive\n"
4538 " -x, --extract Extract files from archive\n"
4540 "And zero or more optional options:\n"
4541 " -v, --verbose Print each filename as it is processed\n"
4542 " -f FILE, --file FILE Operate on archive FILE (default is current db)\n"
4543 " -C DIR, --directory DIR Change to directory DIR to read/extract files\n"
4544 " -n, --dryrun Show the SQL that would have occurred\n"
4545 " -a, --append Append the SQLAR to an existing file\n"
4547 "See also: http://sqlite.org/cli.html#sqlar_archive_support\n"
4550 return SQLITE_ERROR
;
4554 ** Print an error message for the .ar command to stderr and return
4557 static int arErrorMsg(const char *zFmt
, ...){
4561 z
= sqlite3_vmprintf(zFmt
, ap
);
4563 raw_printf(stderr
, "Error: %s (try \".ar --help\")\n", z
);
4565 return SQLITE_ERROR
;
4569 ** Values for ArCommand.eCmd.
4571 #define AR_CMD_CREATE 1
4572 #define AR_CMD_EXTRACT 2
4573 #define AR_CMD_LIST 3
4574 #define AR_CMD_UPDATE 4
4575 #define AR_CMD_HELP 5
4578 ** Other (non-command) switches.
4580 #define AR_SWITCH_VERBOSE 6
4581 #define AR_SWITCH_FILE 7
4582 #define AR_SWITCH_DIRECTORY 8
4583 #define AR_SWITCH_APPEND 9
4584 #define AR_SWITCH_DRYRUN 10
4586 static int arProcessSwitch(ArCommand
*pAr
, int eSwitch
, const char *zArg
){
4589 case AR_CMD_EXTRACT
:
4594 return arErrorMsg("multiple command options");
4596 pAr
->eCmd
= eSwitch
;
4599 case AR_SWITCH_DRYRUN
:
4602 case AR_SWITCH_VERBOSE
:
4605 case AR_SWITCH_APPEND
:
4609 case AR_SWITCH_FILE
:
4612 case AR_SWITCH_DIRECTORY
:
4621 ** Parse the command line for an ".ar" command. The results are written into
4622 ** structure (*pAr). SQLITE_OK is returned if the command line is parsed
4623 ** successfully, otherwise an error message is written to stderr and
4624 ** SQLITE_ERROR returned.
4626 static int arParseCommand(
4627 char **azArg
, /* Array of arguments passed to dot command */
4628 int nArg
, /* Number of entries in azArg[] */
4629 ArCommand
*pAr
/* Populate this object */
4637 { "create", 'c', AR_CMD_CREATE
, 0 },
4638 { "extract", 'x', AR_CMD_EXTRACT
, 0 },
4639 { "list", 't', AR_CMD_LIST
, 0 },
4640 { "update", 'u', AR_CMD_UPDATE
, 0 },
4641 { "help", 'h', AR_CMD_HELP
, 0 },
4642 { "verbose", 'v', AR_SWITCH_VERBOSE
, 0 },
4643 { "file", 'f', AR_SWITCH_FILE
, 1 },
4644 { "directory", 'C', AR_SWITCH_DIRECTORY
, 1 },
4645 { "append", 'a', AR_SWITCH_APPEND
, 0 },
4646 { "dryrun", 'n', AR_SWITCH_DRYRUN
, 0 },
4648 int nSwitch
= sizeof(aSwitch
) / sizeof(struct ArSwitch
);
4649 struct ArSwitch
*pEnd
= &aSwitch
[nSwitch
];
4652 return arUsage(stderr
);
4655 memset(pAr
, 0, sizeof(ArCommand
));
4658 /* Traditional style [tar] invocation */
4661 for(i
=0; z
[i
]; i
++){
4662 const char *zArg
= 0;
4663 struct ArSwitch
*pOpt
;
4664 for(pOpt
=&aSwitch
[0]; pOpt
<pEnd
; pOpt
++){
4665 if( z
[i
]==pOpt
->cShort
) break;
4668 return arErrorMsg("unrecognized option: %c", z
[i
]);
4672 return arErrorMsg("option requires an argument: %c",z
[i
]);
4674 zArg
= azArg
[iArg
++];
4676 if( arProcessSwitch(pAr
, pOpt
->eSwitch
, zArg
) ) return SQLITE_ERROR
;
4678 pAr
->nArg
= nArg
-iArg
;
4680 pAr
->azArg
= &azArg
[iArg
];
4683 /* Non-traditional invocation */
4685 for(iArg
=1; iArg
<nArg
; iArg
++){
4689 /* All remaining command line words are command arguments. */
4690 pAr
->azArg
= &azArg
[iArg
];
4691 pAr
->nArg
= nArg
-iArg
;
4698 /* One or more short options */
4700 const char *zArg
= 0;
4701 struct ArSwitch
*pOpt
;
4702 for(pOpt
=&aSwitch
[0]; pOpt
<pEnd
; pOpt
++){
4703 if( z
[i
]==pOpt
->cShort
) break;
4706 return arErrorMsg("unrecognized option: %c\n", z
[i
]);
4713 if( iArg
>=(nArg
-1) ){
4714 return arErrorMsg("option requires an argument: %c\n",z
[i
]);
4716 zArg
= azArg
[++iArg
];
4719 if( arProcessSwitch(pAr
, pOpt
->eSwitch
, zArg
) ) return SQLITE_ERROR
;
4721 }else if( z
[2]=='\0' ){
4722 /* A -- option, indicating that all remaining command line words
4723 ** are command arguments. */
4724 pAr
->azArg
= &azArg
[iArg
+1];
4725 pAr
->nArg
= nArg
-iArg
-1;
4729 const char *zArg
= 0; /* Argument for option, if any */
4730 struct ArSwitch
*pMatch
= 0; /* Matching option */
4731 struct ArSwitch
*pOpt
; /* Iterator */
4732 for(pOpt
=&aSwitch
[0]; pOpt
<pEnd
; pOpt
++){
4733 const char *zLong
= pOpt
->zLong
;
4734 if( (n
-2)<=strlen30(zLong
) && 0==memcmp(&z
[2], zLong
, n
-2) ){
4736 return arErrorMsg("ambiguous option: %s",z
);
4744 return arErrorMsg("unrecognized option: %s", z
);
4747 if( iArg
>=(nArg
-1) ){
4748 return arErrorMsg("option requires an argument: %s", z
);
4750 zArg
= azArg
[++iArg
];
4752 if( arProcessSwitch(pAr
, pMatch
->eSwitch
, zArg
) ) return SQLITE_ERROR
;
4762 ** This function assumes that all arguments within the ArCommand.azArg[]
4763 ** array refer to archive members, as for the --extract or --list commands.
4764 ** It checks that each of them are present. If any specified file is not
4765 ** present in the archive, an error is printed to stderr and an error
4766 ** code returned. Otherwise, if all specified arguments are present in
4767 ** the archive, SQLITE_OK is returned.
4769 ** This function strips any trailing '/' characters from each argument.
4770 ** This is consistent with the way the [tar] command seems to work on
4773 static int arCheckEntries(ArCommand
*pAr
){
4777 sqlite3_stmt
*pTest
= 0;
4779 shellPreparePrintf(pAr
->db
, &rc
, &pTest
,
4780 "SELECT name FROM %s WHERE name=$name",
4783 j
= sqlite3_bind_parameter_index(pTest
, "$name");
4784 for(i
=0; i
<pAr
->nArg
&& rc
==SQLITE_OK
; i
++){
4785 char *z
= pAr
->azArg
[i
];
4786 int n
= strlen30(z
);
4788 while( n
>0 && z
[n
-1]=='/' ) n
--;
4790 sqlite3_bind_text(pTest
, j
, z
, -1, SQLITE_STATIC
);
4791 if( SQLITE_ROW
==sqlite3_step(pTest
) ){
4794 shellReset(&rc
, pTest
);
4795 if( rc
==SQLITE_OK
&& bOk
==0 ){
4796 utf8_printf(stderr
, "not found in archive: %s\n", z
);
4800 shellFinalize(&rc
, pTest
);
4806 ** Format a WHERE clause that can be used against the "sqlar" table to
4807 ** identify all archive members that match the command arguments held
4808 ** in (*pAr). Leave this WHERE clause in (*pzWhere) before returning.
4809 ** The caller is responsible for eventually calling sqlite3_free() on
4810 ** any non-NULL (*pzWhere) value.
4812 static void arWhereClause(
4815 char **pzWhere
/* OUT: New WHERE clause */
4818 if( *pRc
==SQLITE_OK
){
4820 zWhere
= sqlite3_mprintf("1");
4823 const char *zSep
= "";
4824 for(i
=0; i
<pAr
->nArg
; i
++){
4825 const char *z
= pAr
->azArg
[i
];
4826 zWhere
= sqlite3_mprintf(
4827 "%z%s name = '%q' OR substr(name,1,%d) = '%q/'",
4828 zWhere
, zSep
, z
, strlen30(z
)+1, z
4831 *pRc
= SQLITE_NOMEM
;
4842 ** Argument zMode must point to a buffer at least 11 bytes in size. This
4843 ** function populates this buffer with the string interpretation of
4844 ** the unix file mode passed as the second argument (e.g. "drwxr-xr-x").
4846 static void shellModeToString(char *zMode
, int mode
){
4849 /* Magic numbers copied from [man 2 stat] */
4850 if( mode
& 0040000 ){
4852 }else if( (mode
& 0120000)==0120000 ){
4859 int m
= (mode
>> ((2-i
)*3));
4860 char *a
= &zMode
[1 + i
*3];
4861 a
[0] = (m
& 0x4) ? 'r' : '-';
4862 a
[1] = (m
& 0x2) ? 'w' : '-';
4863 a
[2] = (m
& 0x1) ? 'x' : '-';
4869 ** Implementation of .ar "lisT" command.
4871 static int arListCommand(ArCommand
*pAr
){
4872 const char *zSql
= "SELECT %s FROM %s WHERE %s";
4873 const char *azCols
[] = {
4875 "mode, sz, datetime(mtime, 'unixepoch'), name"
4879 sqlite3_stmt
*pSql
= 0;
4882 rc
= arCheckEntries(pAr
);
4883 arWhereClause(&rc
, pAr
, &zWhere
);
4885 shellPreparePrintf(pAr
->db
, &rc
, &pSql
, zSql
, azCols
[pAr
->bVerbose
],
4886 pAr
->zSrcTable
, zWhere
);
4888 utf8_printf(pAr
->p
->out
, "%s\n", sqlite3_sql(pSql
));
4890 while( rc
==SQLITE_OK
&& SQLITE_ROW
==sqlite3_step(pSql
) ){
4891 if( pAr
->bVerbose
){
4893 shellModeToString(zMode
, sqlite3_column_int(pSql
, 0));
4895 utf8_printf(pAr
->p
->out
, "%s % 10d %s %s\n", zMode
,
4896 sqlite3_column_int(pSql
, 1),
4897 sqlite3_column_text(pSql
, 2),
4898 sqlite3_column_text(pSql
, 3)
4901 utf8_printf(pAr
->p
->out
, "%s\n", sqlite3_column_text(pSql
, 0));
4905 shellFinalize(&rc
, pSql
);
4911 ** Implementation of .ar "eXtract" command.
4913 static int arExtractCommand(ArCommand
*pAr
){
4917 " writefile(($dir || name), %s, mode, mtime) "
4918 "FROM %s WHERE (%s) AND (data IS NULL OR $dirOnly = 0)";
4920 const char *azExtraArg
[] = {
4921 "sqlar_uncompress(data, sz)",
4925 sqlite3_stmt
*pSql
= 0;
4931 /* If arguments are specified, check that they actually exist within
4932 ** the archive before proceeding. And formulate a WHERE clause to
4934 rc
= arCheckEntries(pAr
);
4935 arWhereClause(&rc
, pAr
, &zWhere
);
4937 if( rc
==SQLITE_OK
){
4939 zDir
= sqlite3_mprintf("%s/", pAr
->zDir
);
4941 zDir
= sqlite3_mprintf("");
4943 if( zDir
==0 ) rc
= SQLITE_NOMEM
;
4946 shellPreparePrintf(pAr
->db
, &rc
, &pSql
, zSql1
,
4947 azExtraArg
[pAr
->bZip
], pAr
->zSrcTable
, zWhere
4950 if( rc
==SQLITE_OK
){
4951 j
= sqlite3_bind_parameter_index(pSql
, "$dir");
4952 sqlite3_bind_text(pSql
, j
, zDir
, -1, SQLITE_STATIC
);
4954 /* Run the SELECT statement twice. The first time, writefile() is called
4955 ** for all archive members that should be extracted. The second time,
4956 ** only for the directories. This is because the timestamps for
4957 ** extracted directories must be reset after they are populated (as
4958 ** populating them changes the timestamp). */
4960 j
= sqlite3_bind_parameter_index(pSql
, "$dirOnly");
4961 sqlite3_bind_int(pSql
, j
, i
);
4963 utf8_printf(pAr
->p
->out
, "%s\n", sqlite3_sql(pSql
));
4965 while( rc
==SQLITE_OK
&& SQLITE_ROW
==sqlite3_step(pSql
) ){
4966 if( i
==0 && pAr
->bVerbose
){
4967 utf8_printf(pAr
->p
->out
, "%s\n", sqlite3_column_text(pSql
, 0));
4971 shellReset(&rc
, pSql
);
4973 shellFinalize(&rc
, pSql
);
4977 sqlite3_free(zWhere
);
4982 ** Run the SQL statement in zSql. Or if doing a --dryrun, merely print it out.
4984 static int arExecSql(ArCommand
*pAr
, const char *zSql
){
4987 utf8_printf(pAr
->p
->out
, "%s\n", zSql
);
4990 rc
= sqlite3_exec(pAr
->db
, zSql
, 0, 0, 0);
4997 ** Implementation of .ar "create" and "update" commands.
4999 ** Create the "sqlar" table in the database if it does not already exist.
5000 ** Then add each file in the azFile[] array to the archive. Directories
5001 ** are added recursively. If argument bVerbose is non-zero, a message is
5002 ** printed on stdout for each file archived.
5004 ** The create command is the same as update, except that it drops
5005 ** any existing "sqlar" table before beginning.
5007 static int arCreateOrUpdateCommand(
5008 ArCommand
*pAr
, /* Command arguments and options */
5009 int bUpdate
/* true for a --create. false for --update */
5011 const char *zSql
= "SELECT name, mode, mtime, data FROM fsdir($name, $dir)";
5012 const char *zCreate
=
5013 "CREATE TABLE IF NOT EXISTS sqlar(\n"
5014 " name TEXT PRIMARY KEY, -- name of the file\n"
5015 " mode INT, -- access permissions\n"
5016 " mtime INT, -- last modification time\n"
5017 " sz INT, -- original file size\n"
5018 " data BLOB -- compressed content\n"
5020 const char *zDrop
= "DROP TABLE IF EXISTS sqlar";
5021 const char *zInsert
= "REPLACE INTO sqlar VALUES(?,?,?,?,sqlar_compress(?))";
5023 sqlite3_stmt
*pStmt
= 0; /* Directory traverser */
5024 sqlite3_stmt
*pInsert
= 0; /* Compilation of zInsert */
5025 int i
; /* For iterating through azFile[] */
5026 int j
; /* Parameter index */
5027 int rc
; /* Return code */
5029 assert( pAr
->bZip
==0 );
5031 rc
= arExecSql(pAr
, "SAVEPOINT ar;");
5032 if( rc
!=SQLITE_OK
) return rc
;
5035 rc
= arExecSql(pAr
, zDrop
);
5036 if( rc
!=SQLITE_OK
) return rc
;
5039 rc
= arExecSql(pAr
, zCreate
);
5040 if( !pAr
->bDryRun
){
5041 shellPrepare(pAr
->db
, &rc
, zInsert
, &pInsert
);
5043 shellPrepare(pAr
->db
, &rc
, zSql
, &pStmt
);
5044 j
= sqlite3_bind_parameter_index(pStmt
, "$dir");
5045 sqlite3_bind_text(pStmt
, j
, pAr
->zDir
, -1, SQLITE_STATIC
);
5047 utf8_printf(pAr
->p
->out
, "%s;\n", sqlite3_sql(pStmt
));
5050 for(i
=0; i
<pAr
->nArg
&& rc
==SQLITE_OK
; i
++){
5051 j
= sqlite3_bind_parameter_index(pStmt
, "$name");
5052 sqlite3_bind_text(pStmt
, j
, pAr
->azArg
[i
], -1, SQLITE_STATIC
);
5053 while( rc
==SQLITE_OK
&& SQLITE_ROW
==sqlite3_step(pStmt
) ){
5055 const char *zName
= (const char*)sqlite3_column_text(pStmt
, 0);
5056 int mode
= sqlite3_column_int(pStmt
, 1);
5057 unsigned int mtime
= sqlite3_column_int(pStmt
, 2);
5059 if( pAr
->bVerbose
){
5060 utf8_printf(pAr
->p
->out
, "%s\n", zName
);
5063 utf8_printf(pAr
->p
->out
, "%s;\n", zInsert
);
5067 sqlite3_bind_text(pInsert
, 1, zName
, -1, SQLITE_STATIC
);
5068 sqlite3_bind_int(pInsert
, 2, mode
);
5069 sqlite3_bind_int64(pInsert
, 3, (sqlite3_int64
)mtime
);
5071 if( S_ISDIR(mode
) ){
5073 sqlite3_bind_null(pInsert
, 5);
5075 sqlite3_bind_value(pInsert
, 5, sqlite3_column_value(pStmt
, 3));
5076 if( S_ISLNK(mode
) ){
5079 sz
= sqlite3_column_bytes(pStmt
, 3);
5083 sqlite3_bind_int(pInsert
, 4, sz
);
5085 utf8_printf(pAr
->p
->out
, "%s\n", sqlite3_sql(pInsert
));
5087 sqlite3_step(pInsert
);
5089 rc
= sqlite3_reset(pInsert
);
5091 shellReset(&rc
, pStmt
);
5094 if( rc
!=SQLITE_OK
){
5095 arExecSql(pAr
, "ROLLBACK TO ar; RELEASE ar;");
5097 rc
= arExecSql(pAr
, "RELEASE ar;");
5099 shellFinalize(&rc
, pStmt
);
5100 shellFinalize(&rc
, pInsert
);
5105 ** Implementation of ".ar" dot command.
5107 static int arDotCommand(
5108 ShellState
*pState
, /* Current shell tool state */
5109 char **azArg
, /* Array of arguments passed to dot command */
5110 int nArg
/* Number of entries in azArg[] */
5114 rc
= arParseCommand(azArg
, nArg
, &cmd
);
5115 if( rc
==SQLITE_OK
){
5116 int eDbType
= SHELL_OPEN_UNSPEC
;
5118 cmd
.db
= pState
->db
;
5121 eDbType
= deduceDatabaseType(cmd
.zFile
);
5123 eDbType
= pState
->openMode
;
5125 if( eDbType
==SHELL_OPEN_ZIPFILE
){
5127 cmd
.zSrcTable
= sqlite3_mprintf("zip");
5129 cmd
.zSrcTable
= sqlite3_mprintf("zipfile(%Q)", cmd
.zFile
);
5131 if( cmd
.eCmd
==AR_CMD_CREATE
|| cmd
.eCmd
==AR_CMD_UPDATE
){
5132 utf8_printf(stderr
, "zip archives are read-only\n");
5134 goto end_ar_command
;
5137 }else if( cmd
.zFile
){
5139 if( cmd
.bAppend
) eDbType
= SHELL_OPEN_APPENDVFS
;
5140 if( cmd
.eCmd
==AR_CMD_CREATE
|| cmd
.eCmd
==AR_CMD_UPDATE
){
5141 flags
= SQLITE_OPEN_READWRITE
|SQLITE_OPEN_CREATE
;
5143 flags
= SQLITE_OPEN_READONLY
;
5147 utf8_printf(pState
->out
, "-- open database '%s'%s\n", cmd
.zFile
,
5148 eDbType
==SHELL_OPEN_APPENDVFS
? " using 'apndvfs'" : "");
5150 rc
= sqlite3_open_v2(cmd
.zFile
, &cmd
.db
, flags
,
5151 eDbType
==SHELL_OPEN_APPENDVFS
? "apndvfs" : 0);
5152 if( rc
!=SQLITE_OK
){
5153 utf8_printf(stderr
, "cannot open file: %s (%s)\n",
5154 cmd
.zFile
, sqlite3_errmsg(cmd
.db
)
5156 goto end_ar_command
;
5158 sqlite3_fileio_init(cmd
.db
, 0, 0);
5159 #ifdef SQLITE_HAVE_ZLIB
5160 sqlite3_sqlar_init(cmd
.db
, 0, 0);
5163 if( cmd
.zSrcTable
==0 ){
5164 if( sqlite3_table_column_metadata(cmd
.db
,0,"sqlar","name",0,0,0,0,0) ){
5165 utf8_printf(stderr
, "database does not contain an 'sqlar' table\n");
5167 goto end_ar_command
;
5169 cmd
.zSrcTable
= sqlite3_mprintf("sqlar");
5174 rc
= arCreateOrUpdateCommand(&cmd
, 0);
5177 case AR_CMD_EXTRACT
:
5178 rc
= arExtractCommand(&cmd
);
5182 rc
= arListCommand(&cmd
);
5186 arUsage(pState
->out
);
5190 assert( cmd
.eCmd
==AR_CMD_UPDATE
);
5191 rc
= arCreateOrUpdateCommand(&cmd
, 1);
5196 if( cmd
.db
!=pState
->db
){
5197 sqlite3_close(cmd
.db
);
5199 sqlite3_free(cmd
.zSrcTable
);
5203 /* End of the ".archive" or ".ar" command logic
5204 **********************************************************************************/
5205 #endif /* !defined(SQLITE_OMIT_VIRTUALTABLE) && defined(SQLITE_HAVE_ZLIB) */
5209 ** If an input line begins with "." then invoke this routine to
5210 ** process that line.
5212 ** Return 1 on error, 2 to exit, and 0 otherwise.
5214 static int do_meta_command(char *zLine
, ShellState
*p
){
5221 #ifndef SQLITE_OMIT_VIRTUALTABLE
5222 if( p
->expert
.pExpert
){
5223 expertFinish(p
, 1, 0);
5227 /* Parse the input line into tokens.
5229 while( zLine
[h
] && nArg
<ArraySize(azArg
) ){
5230 while( IsSpace(zLine
[h
]) ){ h
++; }
5231 if( zLine
[h
]==0 ) break;
5232 if( zLine
[h
]=='\'' || zLine
[h
]=='"' ){
5233 int delim
= zLine
[h
++];
5234 azArg
[nArg
++] = &zLine
[h
];
5235 while( zLine
[h
] && zLine
[h
]!=delim
){
5236 if( zLine
[h
]=='\\' && delim
=='"' && zLine
[h
+1]!=0 ) h
++;
5239 if( zLine
[h
]==delim
){
5242 if( delim
=='"' ) resolve_backslashes(azArg
[nArg
-1]);
5244 azArg
[nArg
++] = &zLine
[h
];
5245 while( zLine
[h
] && !IsSpace(zLine
[h
]) ){ h
++; }
5246 if( zLine
[h
] ) zLine
[h
++] = 0;
5247 resolve_backslashes(azArg
[nArg
-1]);
5251 /* Process the input line.
5253 if( nArg
==0 ) return 0; /* no tokens, no error */
5254 n
= strlen30(azArg
[0]);
5257 #ifndef SQLITE_OMIT_AUTHORIZATION
5258 if( c
=='a' && strncmp(azArg
[0], "auth", n
)==0 ){
5260 raw_printf(stderr
, "Usage: .auth ON|OFF\n");
5262 goto meta_command_exit
;
5265 if( booleanValue(azArg
[1]) ){
5266 sqlite3_set_authorizer(p
->db
, shellAuth
, p
);
5268 sqlite3_set_authorizer(p
->db
, 0, 0);
5273 #if !defined(SQLITE_OMIT_VIRTUALTABLE) && defined(SQLITE_HAVE_ZLIB)
5274 if( c
=='a' && strncmp(azArg
[0], "archive", n
)==0 ){
5276 rc
= arDotCommand(p
, azArg
, nArg
);
5280 if( (c
=='b' && n
>=3 && strncmp(azArg
[0], "backup", n
)==0)
5281 || (c
=='s' && n
>=3 && strncmp(azArg
[0], "save", n
)==0)
5283 const char *zDestFile
= 0;
5284 const char *zDb
= 0;
5286 sqlite3_backup
*pBackup
;
5288 for(j
=1; j
<nArg
; j
++){
5289 const char *z
= azArg
[j
];
5291 while( z
[0]=='-' ) z
++;
5292 /* No options to process at this time */
5294 utf8_printf(stderr
, "unknown option: %s\n", azArg
[j
]);
5297 }else if( zDestFile
==0 ){
5298 zDestFile
= azArg
[j
];
5301 zDestFile
= azArg
[j
];
5303 raw_printf(stderr
, "too many arguments to .backup\n");
5308 raw_printf(stderr
, "missing FILENAME argument on .backup\n");
5311 if( zDb
==0 ) zDb
= "main";
5312 rc
= sqlite3_open(zDestFile
, &pDest
);
5313 if( rc
!=SQLITE_OK
){
5314 utf8_printf(stderr
, "Error: cannot open \"%s\"\n", zDestFile
);
5315 sqlite3_close(pDest
);
5319 pBackup
= sqlite3_backup_init(pDest
, "main", p
->db
, zDb
);
5321 utf8_printf(stderr
, "Error: %s\n", sqlite3_errmsg(pDest
));
5322 sqlite3_close(pDest
);
5325 while( (rc
= sqlite3_backup_step(pBackup
,100))==SQLITE_OK
){}
5326 sqlite3_backup_finish(pBackup
);
5327 if( rc
==SQLITE_DONE
){
5330 utf8_printf(stderr
, "Error: %s\n", sqlite3_errmsg(pDest
));
5333 sqlite3_close(pDest
);
5336 if( c
=='b' && n
>=3 && strncmp(azArg
[0], "bail", n
)==0 ){
5338 bail_on_error
= booleanValue(azArg
[1]);
5340 raw_printf(stderr
, "Usage: .bail on|off\n");
5345 if( c
=='b' && n
>=3 && strncmp(azArg
[0], "binary", n
)==0 ){
5347 if( booleanValue(azArg
[1]) ){
5348 setBinaryMode(p
->out
, 1);
5350 setTextMode(p
->out
, 1);
5353 raw_printf(stderr
, "Usage: .binary on|off\n");
5358 if( c
=='c' && strcmp(azArg
[0],"cd")==0 ){
5360 #if defined(_WIN32) || defined(WIN32)
5361 wchar_t *z
= sqlite3_win32_utf8_to_unicode(azArg
[1]);
5362 rc
= !SetCurrentDirectoryW(z
);
5365 rc
= chdir(azArg
[1]);
5368 utf8_printf(stderr
, "Cannot change to directory \"%s\"\n", azArg
[1]);
5372 raw_printf(stderr
, "Usage: .cd DIRECTORY\n");
5377 /* The undocumented ".breakpoint" command causes a call to the no-op
5378 ** routine named test_breakpoint().
5380 if( c
=='b' && n
>=3 && strncmp(azArg
[0], "breakpoint", n
)==0 ){
5384 if( c
=='c' && n
>=3 && strncmp(azArg
[0], "changes", n
)==0 ){
5386 setOrClearFlag(p
, SHFLG_CountChanges
, azArg
[1]);
5388 raw_printf(stderr
, "Usage: .changes on|off\n");
5393 /* Cancel output redirection, if it is currently set (by .testcase)
5394 ** Then read the content of the testcase-out.txt file and compare against
5395 ** azArg[1]. If there are differences, report an error and exit.
5397 if( c
=='c' && n
>=3 && strncmp(azArg
[0], "check", n
)==0 ){
5401 raw_printf(stderr
, "Usage: .check GLOB-PATTERN\n");
5403 }else if( (zRes
= readFile("testcase-out.txt", 0))==0 ){
5404 raw_printf(stderr
, "Error: cannot read 'testcase-out.txt'\n");
5406 }else if( testcase_glob(azArg
[1],zRes
)==0 ){
5408 "testcase-%s FAILED\n Expected: [%s]\n Got: [%s]\n",
5409 p
->zTestcase
, azArg
[1], zRes
);
5412 utf8_printf(stdout
, "testcase-%s ok\n", p
->zTestcase
);
5418 if( c
=='c' && strncmp(azArg
[0], "clone", n
)==0 ){
5420 tryToClone(p
, azArg
[1]);
5422 raw_printf(stderr
, "Usage: .clone FILENAME\n");
5427 if( c
=='d' && n
>1 && strncmp(azArg
[0], "databases", n
)==0 ){
5431 memcpy(&data
, p
, sizeof(data
));
5432 data
.showHeader
= 0;
5433 data
.cMode
= data
.mode
= MODE_List
;
5434 sqlite3_snprintf(sizeof(data
.colSeparator
),data
.colSeparator
,": ");
5436 sqlite3_exec(p
->db
, "SELECT name, file FROM pragma_database_list",
5437 callback
, &data
, &zErrMsg
);
5439 utf8_printf(stderr
,"Error: %s\n", zErrMsg
);
5440 sqlite3_free(zErrMsg
);
5445 if( c
=='d' && strncmp(azArg
[0], "dbinfo", n
)==0 ){
5446 rc
= shell_dbinfo_command(p
, nArg
, azArg
);
5449 if( c
=='d' && strncmp(azArg
[0], "dump", n
)==0 ){
5450 const char *zLike
= 0;
5452 int savedShowHeader
= p
->showHeader
;
5453 ShellClearFlag(p
, SHFLG_PreserveRowid
|SHFLG_Newlines
);
5454 for(i
=1; i
<nArg
; i
++){
5455 if( azArg
[i
][0]=='-' ){
5456 const char *z
= azArg
[i
]+1;
5457 if( z
[0]=='-' ) z
++;
5458 if( strcmp(z
,"preserve-rowids")==0 ){
5459 #ifdef SQLITE_OMIT_VIRTUALTABLE
5460 raw_printf(stderr
, "The --preserve-rowids option is not compatible"
5461 " with SQLITE_OMIT_VIRTUALTABLE\n");
5463 goto meta_command_exit
;
5465 ShellSetFlag(p
, SHFLG_PreserveRowid
);
5468 if( strcmp(z
,"newlines")==0 ){
5469 ShellSetFlag(p
, SHFLG_Newlines
);
5472 raw_printf(stderr
, "Unknown option \"%s\" on \".dump\"\n", azArg
[i
]);
5474 goto meta_command_exit
;
5477 raw_printf(stderr
, "Usage: .dump ?--preserve-rowids? "
5478 "?--newlines? ?LIKE-PATTERN?\n");
5480 goto meta_command_exit
;
5486 /* When playing back a "dump", the content might appear in an order
5487 ** which causes immediate foreign key constraints to be violated.
5488 ** So disable foreign-key constraint enforcement to prevent problems. */
5489 raw_printf(p
->out
, "PRAGMA foreign_keys=OFF;\n");
5490 raw_printf(p
->out
, "BEGIN TRANSACTION;\n");
5491 p
->writableSchema
= 0;
5493 /* Set writable_schema=ON since doing so forces SQLite to initialize
5494 ** as much of the schema as it can even if the sqlite_master table is
5496 sqlite3_exec(p
->db
, "SAVEPOINT dump; PRAGMA writable_schema=ON", 0, 0, 0);
5499 run_schema_dump_query(p
,
5500 "SELECT name, type, sql FROM sqlite_master "
5501 "WHERE sql NOT NULL AND type=='table' AND name!='sqlite_sequence'"
5503 run_schema_dump_query(p
,
5504 "SELECT name, type, sql FROM sqlite_master "
5505 "WHERE name=='sqlite_sequence'"
5507 run_table_dump_query(p
,
5508 "SELECT sql FROM sqlite_master "
5509 "WHERE sql NOT NULL AND type IN ('index','trigger','view')", 0
5513 zSql
= sqlite3_mprintf(
5514 "SELECT name, type, sql FROM sqlite_master "
5515 "WHERE tbl_name LIKE %Q AND type=='table'"
5516 " AND sql NOT NULL", zLike
);
5517 run_schema_dump_query(p
,zSql
);
5519 zSql
= sqlite3_mprintf(
5520 "SELECT sql FROM sqlite_master "
5521 "WHERE sql NOT NULL"
5522 " AND type IN ('index','trigger','view')"
5523 " AND tbl_name LIKE %Q", zLike
);
5524 run_table_dump_query(p
, zSql
, 0);
5527 if( p
->writableSchema
){
5528 raw_printf(p
->out
, "PRAGMA writable_schema=OFF;\n");
5529 p
->writableSchema
= 0;
5531 sqlite3_exec(p
->db
, "PRAGMA writable_schema=OFF;", 0, 0, 0);
5532 sqlite3_exec(p
->db
, "RELEASE dump;", 0, 0, 0);
5533 raw_printf(p
->out
, p
->nErr
? "ROLLBACK; -- due to errors\n" : "COMMIT;\n");
5534 p
->showHeader
= savedShowHeader
;
5537 if( c
=='e' && strncmp(azArg
[0], "echo", n
)==0 ){
5539 setOrClearFlag(p
, SHFLG_Echo
, azArg
[1]);
5541 raw_printf(stderr
, "Usage: .echo on|off\n");
5546 if( c
=='e' && strncmp(azArg
[0], "eqp", n
)==0 ){
5548 if( strcmp(azArg
[1],"full")==0 ){
5549 p
->autoEQP
= AUTOEQP_full
;
5550 }else if( strcmp(azArg
[1],"trigger")==0 ){
5551 p
->autoEQP
= AUTOEQP_trigger
;
5553 p
->autoEQP
= booleanValue(azArg
[1]);
5556 raw_printf(stderr
, "Usage: .eqp off|on|trigger|full\n");
5561 if( c
=='e' && strncmp(azArg
[0], "exit", n
)==0 ){
5562 if( nArg
>1 && (rc
= (int)integerValue(azArg
[1]))!=0 ) exit(rc
);
5566 /* The ".explain" command is automatic now. It is largely pointless. It
5567 ** retained purely for backwards compatibility */
5568 if( c
=='e' && strncmp(azArg
[0], "explain", n
)==0 ){
5571 if( strcmp(azArg
[1],"auto")==0 ){
5574 val
= booleanValue(azArg
[1]);
5577 if( val
==1 && p
->mode
!=MODE_Explain
){
5578 p
->normalMode
= p
->mode
;
5579 p
->mode
= MODE_Explain
;
5582 if( p
->mode
==MODE_Explain
) p
->mode
= p
->normalMode
;
5584 }else if( val
==99 ){
5585 if( p
->mode
==MODE_Explain
) p
->mode
= p
->normalMode
;
5590 #ifndef SQLITE_OMIT_VIRTUALTABLE
5591 if( c
=='e' && strncmp(azArg
[0], "expert", n
)==0 ){
5593 expertDotCommand(p
, azArg
, nArg
);
5597 if( c
=='f' && strncmp(azArg
[0], "fullschema", n
)==0 ){
5601 memcpy(&data
, p
, sizeof(data
));
5602 data
.showHeader
= 0;
5603 data
.cMode
= data
.mode
= MODE_Semi
;
5604 if( nArg
==2 && optionMatch(azArg
[1], "indent") ){
5605 data
.cMode
= data
.mode
= MODE_Pretty
;
5609 raw_printf(stderr
, "Usage: .fullschema ?--indent?\n");
5611 goto meta_command_exit
;
5614 rc
= sqlite3_exec(p
->db
,
5616 " (SELECT sql sql, type type, tbl_name tbl_name, name name, rowid x"
5617 " FROM sqlite_master UNION ALL"
5618 " SELECT sql, type, tbl_name, name, rowid FROM sqlite_temp_master) "
5619 "WHERE type!='meta' AND sql NOTNULL AND name NOT LIKE 'sqlite_%' "
5621 callback
, &data
, &zErrMsg
5623 if( rc
==SQLITE_OK
){
5624 sqlite3_stmt
*pStmt
;
5625 rc
= sqlite3_prepare_v2(p
->db
,
5626 "SELECT rowid FROM sqlite_master"
5627 " WHERE name GLOB 'sqlite_stat[134]'",
5629 doStats
= sqlite3_step(pStmt
)==SQLITE_ROW
;
5630 sqlite3_finalize(pStmt
);
5633 raw_printf(p
->out
, "/* No STAT tables available */\n");
5635 raw_printf(p
->out
, "ANALYZE sqlite_master;\n");
5636 sqlite3_exec(p
->db
, "SELECT 'ANALYZE sqlite_master'",
5637 callback
, &data
, &zErrMsg
);
5638 data
.cMode
= data
.mode
= MODE_Insert
;
5639 data
.zDestTable
= "sqlite_stat1";
5640 shell_exec(p
->db
, "SELECT * FROM sqlite_stat1",
5641 shell_callback
, &data
,&zErrMsg
);
5642 data
.zDestTable
= "sqlite_stat3";
5643 shell_exec(p
->db
, "SELECT * FROM sqlite_stat3",
5644 shell_callback
, &data
,&zErrMsg
);
5645 data
.zDestTable
= "sqlite_stat4";
5646 shell_exec(p
->db
, "SELECT * FROM sqlite_stat4",
5647 shell_callback
, &data
, &zErrMsg
);
5648 raw_printf(p
->out
, "ANALYZE sqlite_master;\n");
5652 if( c
=='h' && strncmp(azArg
[0], "headers", n
)==0 ){
5654 p
->showHeader
= booleanValue(azArg
[1]);
5656 raw_printf(stderr
, "Usage: .headers on|off\n");
5661 if( c
=='h' && strncmp(azArg
[0], "help", n
)==0 ){
5662 utf8_printf(p
->out
, "%s", zHelp
);
5665 if( c
=='i' && strncmp(azArg
[0], "import", n
)==0 ){
5666 char *zTable
; /* Insert data into this table */
5667 char *zFile
; /* Name of file to extra content from */
5668 sqlite3_stmt
*pStmt
= NULL
; /* A statement */
5669 int nCol
; /* Number of columns in the table */
5670 int nByte
; /* Number of bytes in an SQL string */
5671 int i
, j
; /* Loop counters */
5672 int needCommit
; /* True to COMMIT or ROLLBACK at end */
5673 int nSep
; /* Number of bytes in p->colSeparator[] */
5674 char *zSql
; /* An SQL statement */
5675 ImportCtx sCtx
; /* Reader context */
5676 char *(SQLITE_CDECL
*xRead
)(ImportCtx
*); /* Func to read one value */
5677 int (SQLITE_CDECL
*xCloser
)(FILE*); /* Func to close file */
5680 raw_printf(stderr
, "Usage: .import FILE TABLE\n");
5681 goto meta_command_exit
;
5686 memset(&sCtx
, 0, sizeof(sCtx
));
5688 nSep
= strlen30(p
->colSeparator
);
5691 "Error: non-null column separator required for import\n");
5695 raw_printf(stderr
, "Error: multi-character column separators not allowed"
5699 nSep
= strlen30(p
->rowSeparator
);
5701 raw_printf(stderr
, "Error: non-null row separator required for import\n");
5704 if( nSep
==2 && p
->mode
==MODE_Csv
&& strcmp(p
->rowSeparator
, SEP_CrLf
)==0 ){
5705 /* When importing CSV (only), if the row separator is set to the
5706 ** default output row separator, change it to the default input
5707 ** row separator. This avoids having to maintain different input
5708 ** and output row separators. */
5709 sqlite3_snprintf(sizeof(p
->rowSeparator
), p
->rowSeparator
, SEP_Row
);
5710 nSep
= strlen30(p
->rowSeparator
);
5713 raw_printf(stderr
, "Error: multi-character row separators not allowed"
5719 if( sCtx
.zFile
[0]=='|' ){
5720 #ifdef SQLITE_OMIT_POPEN
5721 raw_printf(stderr
, "Error: pipes are not supported in this OS\n");
5724 sCtx
.in
= popen(sCtx
.zFile
+1, "r");
5725 sCtx
.zFile
= "<pipe>";
5729 sCtx
.in
= fopen(sCtx
.zFile
, "rb");
5732 if( p
->mode
==MODE_Ascii
){
5733 xRead
= ascii_read_one_field
;
5735 xRead
= csv_read_one_field
;
5738 utf8_printf(stderr
, "Error: cannot open \"%s\"\n", zFile
);
5741 sCtx
.cColSep
= p
->colSeparator
[0];
5742 sCtx
.cRowSep
= p
->rowSeparator
[0];
5743 zSql
= sqlite3_mprintf("SELECT * FROM %s", zTable
);
5745 raw_printf(stderr
, "Error: out of memory\n");
5749 nByte
= strlen30(zSql
);
5750 rc
= sqlite3_prepare_v2(p
->db
, zSql
, -1, &pStmt
, 0);
5751 import_append_char(&sCtx
, 0); /* To ensure sCtx.z is allocated */
5752 if( rc
&& sqlite3_strglob("no such table: *", sqlite3_errmsg(p
->db
))==0 ){
5753 char *zCreate
= sqlite3_mprintf("CREATE TABLE %s", zTable
);
5755 while( xRead(&sCtx
) ){
5756 zCreate
= sqlite3_mprintf("%z%c\n \"%w\" TEXT", zCreate
, cSep
, sCtx
.z
);
5758 if( sCtx
.cTerm
!=sCtx
.cColSep
) break;
5761 sqlite3_free(zCreate
);
5762 sqlite3_free(sCtx
.z
);
5764 utf8_printf(stderr
,"%s: empty file\n", sCtx
.zFile
);
5767 zCreate
= sqlite3_mprintf("%z\n)", zCreate
);
5768 rc
= sqlite3_exec(p
->db
, zCreate
, 0, 0, 0);
5769 sqlite3_free(zCreate
);
5771 utf8_printf(stderr
, "CREATE TABLE %s(...) failed: %s\n", zTable
,
5772 sqlite3_errmsg(p
->db
));
5773 sqlite3_free(sCtx
.z
);
5777 rc
= sqlite3_prepare_v2(p
->db
, zSql
, -1, &pStmt
, 0);
5781 if (pStmt
) sqlite3_finalize(pStmt
);
5782 utf8_printf(stderr
,"Error: %s\n", sqlite3_errmsg(p
->db
));
5786 nCol
= sqlite3_column_count(pStmt
);
5787 sqlite3_finalize(pStmt
);
5789 if( nCol
==0 ) return 0; /* no columns, no error */
5790 zSql
= sqlite3_malloc64( nByte
*2 + 20 + nCol
*2 );
5792 raw_printf(stderr
, "Error: out of memory\n");
5796 sqlite3_snprintf(nByte
+20, zSql
, "INSERT INTO \"%w\" VALUES(?", zTable
);
5798 for(i
=1; i
<nCol
; i
++){
5804 rc
= sqlite3_prepare_v2(p
->db
, zSql
, -1, &pStmt
, 0);
5807 utf8_printf(stderr
, "Error: %s\n", sqlite3_errmsg(p
->db
));
5808 if (pStmt
) sqlite3_finalize(pStmt
);
5812 needCommit
= sqlite3_get_autocommit(p
->db
);
5813 if( needCommit
) sqlite3_exec(p
->db
, "BEGIN", 0, 0, 0);
5815 int startLine
= sCtx
.nLine
;
5816 for(i
=0; i
<nCol
; i
++){
5817 char *z
= xRead(&sCtx
);
5819 ** Did we reach end-of-file before finding any columns?
5820 ** If so, stop instead of NULL filling the remaining columns.
5822 if( z
==0 && i
==0 ) break;
5824 ** Did we reach end-of-file OR end-of-line before finding any
5825 ** columns in ASCII mode? If so, stop instead of NULL filling
5826 ** the remaining columns.
5828 if( p
->mode
==MODE_Ascii
&& (z
==0 || z
[0]==0) && i
==0 ) break;
5829 sqlite3_bind_text(pStmt
, i
+1, z
, -1, SQLITE_TRANSIENT
);
5830 if( i
<nCol
-1 && sCtx
.cTerm
!=sCtx
.cColSep
){
5831 utf8_printf(stderr
, "%s:%d: expected %d columns but found %d - "
5832 "filling the rest with NULL\n",
5833 sCtx
.zFile
, startLine
, nCol
, i
+1);
5835 while( i
<=nCol
){ sqlite3_bind_null(pStmt
, i
); i
++; }
5838 if( sCtx
.cTerm
==sCtx
.cColSep
){
5842 }while( sCtx
.cTerm
==sCtx
.cColSep
);
5843 utf8_printf(stderr
, "%s:%d: expected %d columns but found %d - "
5845 sCtx
.zFile
, startLine
, nCol
, i
);
5848 sqlite3_step(pStmt
);
5849 rc
= sqlite3_reset(pStmt
);
5850 if( rc
!=SQLITE_OK
){
5851 utf8_printf(stderr
, "%s:%d: INSERT failed: %s\n", sCtx
.zFile
,
5852 startLine
, sqlite3_errmsg(p
->db
));
5855 }while( sCtx
.cTerm
!=EOF
);
5858 sqlite3_free(sCtx
.z
);
5859 sqlite3_finalize(pStmt
);
5860 if( needCommit
) sqlite3_exec(p
->db
, "COMMIT", 0, 0, 0);
5863 #ifndef SQLITE_UNTESTABLE
5864 if( c
=='i' && strncmp(azArg
[0], "imposter", n
)==0 ){
5867 sqlite3_stmt
*pStmt
;
5871 utf8_printf(stderr
, "Usage: .imposter INDEX IMPOSTER\n");
5873 goto meta_command_exit
;
5876 zSql
= sqlite3_mprintf("SELECT rootpage FROM sqlite_master"
5877 " WHERE name='%q' AND type='index'", azArg
[1]);
5878 sqlite3_prepare_v2(p
->db
, zSql
, -1, &pStmt
, 0);
5880 if( sqlite3_step(pStmt
)==SQLITE_ROW
){
5881 tnum
= sqlite3_column_int(pStmt
, 0);
5883 sqlite3_finalize(pStmt
);
5885 utf8_printf(stderr
, "no such index: \"%s\"\n", azArg
[1]);
5887 goto meta_command_exit
;
5889 zSql
= sqlite3_mprintf("PRAGMA index_xinfo='%q'", azArg
[1]);
5890 rc
= sqlite3_prepare_v2(p
->db
, zSql
, -1, &pStmt
, 0);
5893 while( sqlite3_step(pStmt
)==SQLITE_ROW
){
5895 const char *zCol
= (const char*)sqlite3_column_text(pStmt
,2);
5898 if( sqlite3_column_int(pStmt
,1)==-1 ){
5901 sqlite3_snprintf(sizeof(zLabel
),zLabel
,"expr%d",i
);
5906 zCollist
= sqlite3_mprintf("\"%w\"", zCol
);
5908 zCollist
= sqlite3_mprintf("%z,\"%w\"", zCollist
, zCol
);
5911 sqlite3_finalize(pStmt
);
5912 zSql
= sqlite3_mprintf(
5913 "CREATE TABLE \"%w\"(%s,PRIMARY KEY(%s))WITHOUT ROWID",
5914 azArg
[2], zCollist
, zCollist
);
5915 sqlite3_free(zCollist
);
5916 rc
= sqlite3_test_control(SQLITE_TESTCTRL_IMPOSTER
, p
->db
, "main", 1, tnum
);
5917 if( rc
==SQLITE_OK
){
5918 rc
= sqlite3_exec(p
->db
, zSql
, 0, 0, 0);
5919 sqlite3_test_control(SQLITE_TESTCTRL_IMPOSTER
, p
->db
, "main", 0, 0);
5921 utf8_printf(stderr
, "Error in [%s]: %s\n", zSql
, sqlite3_errmsg(p
->db
));
5923 utf8_printf(stdout
, "%s;\n", zSql
);
5925 "WARNING: writing to an imposter table will corrupt the index!\n"
5929 raw_printf(stderr
, "SQLITE_TESTCTRL_IMPOSTER returns %d\n", rc
);
5934 #endif /* !defined(SQLITE_OMIT_TEST_CONTROL) */
5936 #ifdef SQLITE_ENABLE_IOTRACE
5937 if( c
=='i' && strncmp(azArg
[0], "iotrace", n
)==0 ){
5938 SQLITE_API
extern void (SQLITE_CDECL
*sqlite3IoTrace
)(const char*, ...);
5939 if( iotrace
&& iotrace
!=stdout
) fclose(iotrace
);
5943 }else if( strcmp(azArg
[1], "-")==0 ){
5944 sqlite3IoTrace
= iotracePrintf
;
5947 iotrace
= fopen(azArg
[1], "w");
5949 utf8_printf(stderr
, "Error: cannot open \"%s\"\n", azArg
[1]);
5953 sqlite3IoTrace
= iotracePrintf
;
5959 if( c
=='l' && n
>=5 && strncmp(azArg
[0], "limits", n
)==0 ){
5960 static const struct {
5961 const char *zLimitName
; /* Name of a limit */
5962 int limitCode
; /* Integer code for that limit */
5964 { "length", SQLITE_LIMIT_LENGTH
},
5965 { "sql_length", SQLITE_LIMIT_SQL_LENGTH
},
5966 { "column", SQLITE_LIMIT_COLUMN
},
5967 { "expr_depth", SQLITE_LIMIT_EXPR_DEPTH
},
5968 { "compound_select", SQLITE_LIMIT_COMPOUND_SELECT
},
5969 { "vdbe_op", SQLITE_LIMIT_VDBE_OP
},
5970 { "function_arg", SQLITE_LIMIT_FUNCTION_ARG
},
5971 { "attached", SQLITE_LIMIT_ATTACHED
},
5972 { "like_pattern_length", SQLITE_LIMIT_LIKE_PATTERN_LENGTH
},
5973 { "variable_number", SQLITE_LIMIT_VARIABLE_NUMBER
},
5974 { "trigger_depth", SQLITE_LIMIT_TRIGGER_DEPTH
},
5975 { "worker_threads", SQLITE_LIMIT_WORKER_THREADS
},
5980 for(i
=0; i
<ArraySize(aLimit
); i
++){
5981 printf("%20s %d\n", aLimit
[i
].zLimitName
,
5982 sqlite3_limit(p
->db
, aLimit
[i
].limitCode
, -1));
5985 raw_printf(stderr
, "Usage: .limit NAME ?NEW-VALUE?\n");
5987 goto meta_command_exit
;
5990 n2
= strlen30(azArg
[1]);
5991 for(i
=0; i
<ArraySize(aLimit
); i
++){
5992 if( sqlite3_strnicmp(aLimit
[i
].zLimitName
, azArg
[1], n2
)==0 ){
5996 utf8_printf(stderr
, "ambiguous limit: \"%s\"\n", azArg
[1]);
5998 goto meta_command_exit
;
6003 utf8_printf(stderr
, "unknown limit: \"%s\"\n"
6004 "enter \".limits\" with no arguments for a list.\n",
6007 goto meta_command_exit
;
6010 sqlite3_limit(p
->db
, aLimit
[iLimit
].limitCode
,
6011 (int)integerValue(azArg
[2]));
6013 printf("%20s %d\n", aLimit
[iLimit
].zLimitName
,
6014 sqlite3_limit(p
->db
, aLimit
[iLimit
].limitCode
, -1));
6018 if( c
=='l' && n
>2 && strncmp(azArg
[0], "lint", n
)==0 ){
6020 lintDotCommand(p
, azArg
, nArg
);
6023 #ifndef SQLITE_OMIT_LOAD_EXTENSION
6024 if( c
=='l' && strncmp(azArg
[0], "load", n
)==0 ){
6025 const char *zFile
, *zProc
;
6028 raw_printf(stderr
, "Usage: .load FILE ?ENTRYPOINT?\n");
6030 goto meta_command_exit
;
6033 zProc
= nArg
>=3 ? azArg
[2] : 0;
6035 rc
= sqlite3_load_extension(p
->db
, zFile
, zProc
, &zErrMsg
);
6036 if( rc
!=SQLITE_OK
){
6037 utf8_printf(stderr
, "Error: %s\n", zErrMsg
);
6038 sqlite3_free(zErrMsg
);
6044 if( c
=='l' && strncmp(azArg
[0], "log", n
)==0 ){
6046 raw_printf(stderr
, "Usage: .log FILENAME\n");
6049 const char *zFile
= azArg
[1];
6050 output_file_close(p
->pLog
);
6051 p
->pLog
= output_file_open(zFile
);
6055 if( c
=='m' && strncmp(azArg
[0], "mode", n
)==0 ){
6056 const char *zMode
= nArg
>=2 ? azArg
[1] : "";
6057 int n2
= strlen30(zMode
);
6059 if( c2
=='l' && n2
>2 && strncmp(azArg
[1],"lines",n2
)==0 ){
6060 p
->mode
= MODE_Line
;
6061 sqlite3_snprintf(sizeof(p
->rowSeparator
), p
->rowSeparator
, SEP_Row
);
6062 }else if( c2
=='c' && strncmp(azArg
[1],"columns",n2
)==0 ){
6063 p
->mode
= MODE_Column
;
6064 sqlite3_snprintf(sizeof(p
->rowSeparator
), p
->rowSeparator
, SEP_Row
);
6065 }else if( c2
=='l' && n2
>2 && strncmp(azArg
[1],"list",n2
)==0 ){
6066 p
->mode
= MODE_List
;
6067 sqlite3_snprintf(sizeof(p
->colSeparator
), p
->colSeparator
, SEP_Column
);
6068 sqlite3_snprintf(sizeof(p
->rowSeparator
), p
->rowSeparator
, SEP_Row
);
6069 }else if( c2
=='h' && strncmp(azArg
[1],"html",n2
)==0 ){
6070 p
->mode
= MODE_Html
;
6071 }else if( c2
=='t' && strncmp(azArg
[1],"tcl",n2
)==0 ){
6073 sqlite3_snprintf(sizeof(p
->colSeparator
), p
->colSeparator
, SEP_Space
);
6074 sqlite3_snprintf(sizeof(p
->rowSeparator
), p
->rowSeparator
, SEP_Row
);
6075 }else if( c2
=='c' && strncmp(azArg
[1],"csv",n2
)==0 ){
6077 sqlite3_snprintf(sizeof(p
->colSeparator
), p
->colSeparator
, SEP_Comma
);
6078 sqlite3_snprintf(sizeof(p
->rowSeparator
), p
->rowSeparator
, SEP_CrLf
);
6079 }else if( c2
=='t' && strncmp(azArg
[1],"tabs",n2
)==0 ){
6080 p
->mode
= MODE_List
;
6081 sqlite3_snprintf(sizeof(p
->colSeparator
), p
->colSeparator
, SEP_Tab
);
6082 }else if( c2
=='i' && strncmp(azArg
[1],"insert",n2
)==0 ){
6083 p
->mode
= MODE_Insert
;
6084 set_table_name(p
, nArg
>=3 ? azArg
[2] : "table");
6085 }else if( c2
=='q' && strncmp(azArg
[1],"quote",n2
)==0 ){
6086 p
->mode
= MODE_Quote
;
6087 }else if( c2
=='a' && strncmp(azArg
[1],"ascii",n2
)==0 ){
6088 p
->mode
= MODE_Ascii
;
6089 sqlite3_snprintf(sizeof(p
->colSeparator
), p
->colSeparator
, SEP_Unit
);
6090 sqlite3_snprintf(sizeof(p
->rowSeparator
), p
->rowSeparator
, SEP_Record
);
6091 }else if( nArg
==1 ){
6092 raw_printf(p
->out
, "current output mode: %s\n", modeDescr
[p
->mode
]);
6094 raw_printf(stderr
, "Error: mode should be one of: "
6095 "ascii column csv html insert line list quote tabs tcl\n");
6101 if( c
=='n' && strncmp(azArg
[0], "nullvalue", n
)==0 ){
6103 sqlite3_snprintf(sizeof(p
->nullValue
), p
->nullValue
,
6104 "%.*s", (int)ArraySize(p
->nullValue
)-1, azArg
[1]);
6106 raw_printf(stderr
, "Usage: .nullvalue STRING\n");
6111 if( c
=='o' && strncmp(azArg
[0], "open", n
)==0 && n
>=2 ){
6112 char *zNewFilename
; /* Name of the database file to open */
6113 int iName
= 1; /* Index in azArg[] of the filename */
6114 int newFlag
= 0; /* True to delete file before opening */
6115 /* Close the existing database */
6116 session_close_all(p
);
6117 sqlite3_close(p
->db
);
6120 sqlite3_free(p
->zFreeOnClose
);
6121 p
->zFreeOnClose
= 0;
6122 p
->openMode
= SHELL_OPEN_UNSPEC
;
6123 /* Check for command-line arguments */
6124 for(iName
=1; iName
<nArg
&& azArg
[iName
][0]=='-'; iName
++){
6125 const char *z
= azArg
[iName
];
6126 if( optionMatch(z
,"new") ){
6128 #ifdef SQLITE_HAVE_ZIP
6129 }else if( optionMatch(z
, "zip") ){
6130 p
->openMode
= SHELL_OPEN_ZIPFILE
;
6132 }else if( optionMatch(z
, "append") ){
6133 p
->openMode
= SHELL_OPEN_APPENDVFS
;
6134 }else if( z
[0]=='-' ){
6135 utf8_printf(stderr
, "unknown option: %s\n", z
);
6137 goto meta_command_exit
;
6140 /* If a filename is specified, try to open it first */
6141 zNewFilename
= nArg
>iName
? sqlite3_mprintf("%s", azArg
[iName
]) : 0;
6143 if( newFlag
) shellDeleteFile(zNewFilename
);
6144 p
->zDbFilename
= zNewFilename
;
6147 utf8_printf(stderr
, "Error: cannot open '%s'\n", zNewFilename
);
6148 sqlite3_free(zNewFilename
);
6150 p
->zFreeOnClose
= zNewFilename
;
6154 /* As a fall-back open a TEMP database */
6161 && (strncmp(azArg
[0], "output", n
)==0 || strncmp(azArg
[0], "once", n
)==0)
6163 const char *zFile
= nArg
>=2 ? azArg
[1] : "stdout";
6165 utf8_printf(stderr
, "Usage: .%s FILE\n", azArg
[0]);
6167 goto meta_command_exit
;
6169 if( n
>1 && strncmp(azArg
[0], "once", n
)==0 ){
6171 raw_printf(stderr
, "Usage: .once FILE\n");
6173 goto meta_command_exit
;
6180 if( zFile
[0]=='|' ){
6181 #ifdef SQLITE_OMIT_POPEN
6182 raw_printf(stderr
, "Error: pipes are not supported in this OS\n");
6186 p
->out
= popen(zFile
+ 1, "w");
6188 utf8_printf(stderr
,"Error: cannot open pipe \"%s\"\n", zFile
+ 1);
6192 sqlite3_snprintf(sizeof(p
->outfile
), p
->outfile
, "%s", zFile
);
6196 p
->out
= output_file_open(zFile
);
6198 if( strcmp(zFile
,"off")!=0 ){
6199 utf8_printf(stderr
,"Error: cannot write to \"%s\"\n", zFile
);
6204 sqlite3_snprintf(sizeof(p
->outfile
), p
->outfile
, "%s", zFile
);
6209 if( c
=='p' && n
>=3 && strncmp(azArg
[0], "print", n
)==0 ){
6211 for(i
=1; i
<nArg
; i
++){
6212 if( i
>1 ) raw_printf(p
->out
, " ");
6213 utf8_printf(p
->out
, "%s", azArg
[i
]);
6215 raw_printf(p
->out
, "\n");
6218 if( c
=='p' && strncmp(azArg
[0], "prompt", n
)==0 ){
6220 strncpy(mainPrompt
,azArg
[1],(int)ArraySize(mainPrompt
)-1);
6223 strncpy(continuePrompt
,azArg
[2],(int)ArraySize(continuePrompt
)-1);
6227 if( c
=='q' && strncmp(azArg
[0], "quit", n
)==0 ){
6231 if( c
=='r' && n
>=3 && strncmp(azArg
[0], "read", n
)==0 ){
6234 raw_printf(stderr
, "Usage: .read FILE\n");
6236 goto meta_command_exit
;
6238 alt
= fopen(azArg
[1], "rb");
6240 utf8_printf(stderr
,"Error: cannot open \"%s\"\n", azArg
[1]);
6243 rc
= process_input(p
, alt
);
6248 if( c
=='r' && n
>=3 && strncmp(azArg
[0], "restore", n
)==0 ){
6249 const char *zSrcFile
;
6252 sqlite3_backup
*pBackup
;
6256 zSrcFile
= azArg
[1];
6258 }else if( nArg
==3 ){
6259 zSrcFile
= azArg
[2];
6262 raw_printf(stderr
, "Usage: .restore ?DB? FILE\n");
6264 goto meta_command_exit
;
6266 rc
= sqlite3_open(zSrcFile
, &pSrc
);
6267 if( rc
!=SQLITE_OK
){
6268 utf8_printf(stderr
, "Error: cannot open \"%s\"\n", zSrcFile
);
6269 sqlite3_close(pSrc
);
6273 pBackup
= sqlite3_backup_init(p
->db
, zDb
, pSrc
, "main");
6275 utf8_printf(stderr
, "Error: %s\n", sqlite3_errmsg(p
->db
));
6276 sqlite3_close(pSrc
);
6279 while( (rc
= sqlite3_backup_step(pBackup
,100))==SQLITE_OK
6280 || rc
==SQLITE_BUSY
){
6281 if( rc
==SQLITE_BUSY
){
6282 if( nTimeout
++ >= 3 ) break;
6286 sqlite3_backup_finish(pBackup
);
6287 if( rc
==SQLITE_DONE
){
6289 }else if( rc
==SQLITE_BUSY
|| rc
==SQLITE_LOCKED
){
6290 raw_printf(stderr
, "Error: source database is busy\n");
6293 utf8_printf(stderr
, "Error: %s\n", sqlite3_errmsg(p
->db
));
6296 sqlite3_close(pSrc
);
6300 if( c
=='s' && strncmp(azArg
[0], "scanstats", n
)==0 ){
6302 p
->scanstatsOn
= booleanValue(azArg
[1]);
6303 #ifndef SQLITE_ENABLE_STMT_SCANSTATUS
6304 raw_printf(stderr
, "Warning: .scanstats not available in this build.\n");
6307 raw_printf(stderr
, "Usage: .scanstats on|off\n");
6312 if( c
=='s' && strncmp(azArg
[0], "schema", n
)==0 ){
6316 const char *zDiv
= "(";
6317 const char *zName
= 0;
6323 memcpy(&data
, p
, sizeof(data
));
6324 data
.showHeader
= 0;
6325 data
.cMode
= data
.mode
= MODE_Semi
;
6327 for(ii
=1; ii
<nArg
; ii
++){
6328 if( optionMatch(azArg
[ii
],"indent") ){
6329 data
.cMode
= data
.mode
= MODE_Pretty
;
6330 }else if( optionMatch(azArg
[ii
],"debug") ){
6332 }else if( zName
==0 ){
6335 raw_printf(stderr
, "Usage: .schema ?--indent? ?LIKE-PATTERN?\n");
6337 goto meta_command_exit
;
6341 int isMaster
= sqlite3_strlike(zName
, "sqlite_master", 0)==0;
6342 if( isMaster
|| sqlite3_strlike(zName
,"sqlite_temp_master",0)==0 ){
6343 char *new_argv
[2], *new_colv
[2];
6344 new_argv
[0] = sqlite3_mprintf(
6345 "CREATE TABLE %s (\n"
6349 " rootpage integer,\n"
6351 ")", isMaster
? "sqlite_master" : "sqlite_temp_master");
6353 new_colv
[0] = "sql";
6355 callback(&data
, 1, new_argv
, new_colv
);
6356 sqlite3_free(new_argv
[0]);
6360 sqlite3_stmt
*pStmt
= 0;
6361 rc
= sqlite3_prepare_v2(p
->db
, "SELECT name FROM pragma_database_list",
6364 utf8_printf(stderr
, "Error: %s\n", sqlite3_errmsg(p
->db
));
6365 sqlite3_finalize(pStmt
);
6367 goto meta_command_exit
;
6369 appendText(&sSelect
, "SELECT sql FROM", 0);
6371 while( sqlite3_step(pStmt
)==SQLITE_ROW
){
6372 const char *zDb
= (const char*)sqlite3_column_text(pStmt
, 0);
6374 sqlite3_snprintf(sizeof(zScNum
), zScNum
, "%d", ++iSchema
);
6375 appendText(&sSelect
, zDiv
, 0);
6376 zDiv
= " UNION ALL ";
6377 appendText(&sSelect
, "SELECT shell_add_schema(sql,", 0);
6378 if( sqlite3_stricmp(zDb
, "main")!=0 ){
6379 appendText(&sSelect
, zDb
, '"');
6381 appendText(&sSelect
, "NULL", 0);
6383 appendText(&sSelect
, ",name) AS sql, type, tbl_name, name, rowid,", 0);
6384 appendText(&sSelect
, zScNum
, 0);
6385 appendText(&sSelect
, " AS snum, ", 0);
6386 appendText(&sSelect
, zDb
, '\'');
6387 appendText(&sSelect
, " AS sname FROM ", 0);
6388 appendText(&sSelect
, zDb
, '"');
6389 appendText(&sSelect
, ".sqlite_master", 0);
6391 sqlite3_finalize(pStmt
);
6392 #ifdef SQLITE_INTROSPECTION_PRAGMAS
6394 appendText(&sSelect
,
6395 " UNION ALL SELECT shell_module_schema(name),"
6396 " 'table', name, name, name, 9e+99, 'main' FROM pragma_module_list", 0);
6399 appendText(&sSelect
, ") WHERE ", 0);
6401 char *zQarg
= sqlite3_mprintf("%Q", zName
);
6402 if( strchr(zName
, '.') ){
6403 appendText(&sSelect
, "lower(printf('%s.%s',sname,tbl_name))", 0);
6405 appendText(&sSelect
, "lower(tbl_name)", 0);
6407 appendText(&sSelect
, strchr(zName
, '*') ? " GLOB " : " LIKE ", 0);
6408 appendText(&sSelect
, zQarg
, 0);
6409 appendText(&sSelect
, " AND ", 0);
6410 sqlite3_free(zQarg
);
6412 appendText(&sSelect
, "type!='meta' AND sql IS NOT NULL"
6413 " ORDER BY snum, rowid", 0);
6415 utf8_printf(p
->out
, "SQL: %s;\n", sSelect
.z
);
6417 rc
= sqlite3_exec(p
->db
, sSelect
.z
, callback
, &data
, &zErrMsg
);
6422 utf8_printf(stderr
,"Error: %s\n", zErrMsg
);
6423 sqlite3_free(zErrMsg
);
6425 }else if( rc
!= SQLITE_OK
){
6426 raw_printf(stderr
,"Error: querying schema information\n");
6433 #if defined(SQLITE_DEBUG) && defined(SQLITE_ENABLE_SELECTTRACE)
6434 if( c
=='s' && n
==11 && strncmp(azArg
[0], "selecttrace", n
)==0 ){
6435 sqlite3SelectTrace
= (int)integerValue(azArg
[1]);
6439 #if defined(SQLITE_ENABLE_SESSION)
6440 if( c
=='s' && strncmp(azArg
[0],"session",n
)==0 && n
>=3 ){
6441 OpenSession
*pSession
= &p
->aSession
[0];
6442 char **azCmd
= &azArg
[1];
6444 int nCmd
= nArg
- 1;
6446 if( nArg
<=1 ) goto session_syntax_error
;
6449 for(iSes
=0; iSes
<p
->nSession
; iSes
++){
6450 if( strcmp(p
->aSession
[iSes
].zName
, azArg
[1])==0 ) break;
6452 if( iSes
<p
->nSession
){
6453 pSession
= &p
->aSession
[iSes
];
6457 pSession
= &p
->aSession
[0];
6462 /* .session attach TABLE
6463 ** Invoke the sqlite3session_attach() interface to attach a particular
6464 ** table so that it is never filtered.
6466 if( strcmp(azCmd
[0],"attach")==0 ){
6467 if( nCmd
!=2 ) goto session_syntax_error
;
6468 if( pSession
->p
==0 ){
6470 raw_printf(stderr
, "ERROR: No sessions are open\n");
6472 rc
= sqlite3session_attach(pSession
->p
, azCmd
[1]);
6474 raw_printf(stderr
, "ERROR: sqlite3session_attach() returns %d\n", rc
);
6480 /* .session changeset FILE
6481 ** .session patchset FILE
6482 ** Write a changeset or patchset into a file. The file is overwritten.
6484 if( strcmp(azCmd
[0],"changeset")==0 || strcmp(azCmd
[0],"patchset")==0 ){
6486 if( nCmd
!=2 ) goto session_syntax_error
;
6487 if( pSession
->p
==0 ) goto session_not_open
;
6488 out
= fopen(azCmd
[1], "wb");
6490 utf8_printf(stderr
, "ERROR: cannot open \"%s\" for writing\n", azCmd
[1]);
6494 if( azCmd
[0][0]=='c' ){
6495 rc
= sqlite3session_changeset(pSession
->p
, &szChng
, &pChng
);
6497 rc
= sqlite3session_patchset(pSession
->p
, &szChng
, &pChng
);
6500 printf("Error: error code %d\n", rc
);
6504 && fwrite(pChng
, szChng
, 1, out
)!=1 ){
6505 raw_printf(stderr
, "ERROR: Failed to write entire %d-byte output\n",
6508 sqlite3_free(pChng
);
6514 ** Close the identified session
6516 if( strcmp(azCmd
[0], "close")==0 ){
6517 if( nCmd
!=1 ) goto session_syntax_error
;
6519 session_close(pSession
);
6520 p
->aSession
[iSes
] = p
->aSession
[--p
->nSession
];
6524 /* .session enable ?BOOLEAN?
6525 ** Query or set the enable flag
6527 if( strcmp(azCmd
[0], "enable")==0 ){
6529 if( nCmd
>2 ) goto session_syntax_error
;
6530 ii
= nCmd
==1 ? -1 : booleanValue(azCmd
[1]);
6532 ii
= sqlite3session_enable(pSession
->p
, ii
);
6533 utf8_printf(p
->out
, "session %s enable flag = %d\n",
6534 pSession
->zName
, ii
);
6538 /* .session filter GLOB ....
6539 ** Set a list of GLOB patterns of table names to be excluded.
6541 if( strcmp(azCmd
[0], "filter")==0 ){
6543 if( nCmd
<2 ) goto session_syntax_error
;
6545 for(ii
=0; ii
<pSession
->nFilter
; ii
++){
6546 sqlite3_free(pSession
->azFilter
[ii
]);
6548 sqlite3_free(pSession
->azFilter
);
6549 nByte
= sizeof(pSession
->azFilter
[0])*(nCmd
-1);
6550 pSession
->azFilter
= sqlite3_malloc( nByte
);
6551 if( pSession
->azFilter
==0 ){
6552 raw_printf(stderr
, "Error: out or memory\n");
6555 for(ii
=1; ii
<nCmd
; ii
++){
6556 pSession
->azFilter
[ii
-1] = sqlite3_mprintf("%s", azCmd
[ii
]);
6558 pSession
->nFilter
= ii
-1;
6562 /* .session indirect ?BOOLEAN?
6563 ** Query or set the indirect flag
6565 if( strcmp(azCmd
[0], "indirect")==0 ){
6567 if( nCmd
>2 ) goto session_syntax_error
;
6568 ii
= nCmd
==1 ? -1 : booleanValue(azCmd
[1]);
6570 ii
= sqlite3session_indirect(pSession
->p
, ii
);
6571 utf8_printf(p
->out
, "session %s indirect flag = %d\n",
6572 pSession
->zName
, ii
);
6577 ** Determine if the session is empty
6579 if( strcmp(azCmd
[0], "isempty")==0 ){
6581 if( nCmd
!=1 ) goto session_syntax_error
;
6583 ii
= sqlite3session_isempty(pSession
->p
);
6584 utf8_printf(p
->out
, "session %s isempty flag = %d\n",
6585 pSession
->zName
, ii
);
6590 ** List all currently open sessions
6592 if( strcmp(azCmd
[0],"list")==0 ){
6593 for(i
=0; i
<p
->nSession
; i
++){
6594 utf8_printf(p
->out
, "%d %s\n", i
, p
->aSession
[i
].zName
);
6598 /* .session open DB NAME
6599 ** Open a new session called NAME on the attached database DB.
6600 ** DB is normally "main".
6602 if( strcmp(azCmd
[0],"open")==0 ){
6604 if( nCmd
!=3 ) goto session_syntax_error
;
6606 if( zName
[0]==0 ) goto session_syntax_error
;
6607 for(i
=0; i
<p
->nSession
; i
++){
6608 if( strcmp(p
->aSession
[i
].zName
,zName
)==0 ){
6609 utf8_printf(stderr
, "Session \"%s\" already exists\n", zName
);
6610 goto meta_command_exit
;
6613 if( p
->nSession
>=ArraySize(p
->aSession
) ){
6614 raw_printf(stderr
, "Maximum of %d sessions\n", ArraySize(p
->aSession
));
6615 goto meta_command_exit
;
6617 pSession
= &p
->aSession
[p
->nSession
];
6618 rc
= sqlite3session_create(p
->db
, azCmd
[1], &pSession
->p
);
6620 raw_printf(stderr
, "Cannot open session: error code=%d\n", rc
);
6622 goto meta_command_exit
;
6624 pSession
->nFilter
= 0;
6625 sqlite3session_table_filter(pSession
->p
, session_filter
, pSession
);
6627 pSession
->zName
= sqlite3_mprintf("%s", zName
);
6629 /* If no command name matches, show a syntax error */
6630 session_syntax_error
:
6636 /* Undocumented commands for internal testing. Subject to change
6637 ** without notice. */
6638 if( c
=='s' && n
>=10 && strncmp(azArg
[0], "selftest-", 9)==0 ){
6639 if( strncmp(azArg
[0]+9, "boolean", n
-9)==0 ){
6641 for(i
=1; i
<nArg
; i
++){
6642 v
= booleanValue(azArg
[i
]);
6643 utf8_printf(p
->out
, "%s: %d 0x%x\n", azArg
[i
], v
, v
);
6646 if( strncmp(azArg
[0]+9, "integer", n
-9)==0 ){
6647 int i
; sqlite3_int64 v
;
6648 for(i
=1; i
<nArg
; i
++){
6650 v
= integerValue(azArg
[i
]);
6651 sqlite3_snprintf(sizeof(zBuf
),zBuf
,"%s: %lld 0x%llx\n", azArg
[i
],v
,v
);
6652 utf8_printf(p
->out
, "%s", zBuf
);
6658 if( c
=='s' && n
>=4 && strncmp(azArg
[0],"selftest",n
)==0 ){
6659 int bIsInit
= 0; /* True to initialize the SELFTEST table */
6660 int bVerbose
= 0; /* Verbose output */
6661 int bSelftestExists
; /* True if SELFTEST already exists */
6662 int i
, k
; /* Loop counters */
6663 int nTest
= 0; /* Number of tests runs */
6664 int nErr
= 0; /* Number of errors seen */
6665 ShellText str
; /* Answer for a query */
6666 sqlite3_stmt
*pStmt
= 0; /* Query against the SELFTEST table */
6669 for(i
=1; i
<nArg
; i
++){
6670 const char *z
= azArg
[i
];
6671 if( z
[0]=='-' && z
[1]=='-' ) z
++;
6672 if( strcmp(z
,"-init")==0 ){
6675 if( strcmp(z
,"-v")==0 ){
6679 utf8_printf(stderr
, "Unknown option \"%s\" on \"%s\"\n",
6680 azArg
[i
], azArg
[0]);
6681 raw_printf(stderr
, "Should be one of: --init -v\n");
6683 goto meta_command_exit
;
6686 if( sqlite3_table_column_metadata(p
->db
,"main","selftest",0,0,0,0,0,0)
6688 bSelftestExists
= 0;
6690 bSelftestExists
= 1;
6693 createSelftestTable(p
);
6694 bSelftestExists
= 1;
6697 appendText(&str
, "x", 0);
6698 for(k
=bSelftestExists
; k
>=0; k
--){
6700 rc
= sqlite3_prepare_v2(p
->db
,
6701 "SELECT tno,op,cmd,ans FROM selftest ORDER BY tno",
6704 rc
= sqlite3_prepare_v2(p
->db
,
6705 "VALUES(0,'memo','Missing SELFTEST table - default checks only',''),"
6706 " (1,'run','PRAGMA integrity_check','ok')",
6710 raw_printf(stderr
, "Error querying the selftest table\n");
6712 sqlite3_finalize(pStmt
);
6713 goto meta_command_exit
;
6715 for(i
=1; sqlite3_step(pStmt
)==SQLITE_ROW
; i
++){
6716 int tno
= sqlite3_column_int(pStmt
, 0);
6717 const char *zOp
= (const char*)sqlite3_column_text(pStmt
, 1);
6718 const char *zSql
= (const char*)sqlite3_column_text(pStmt
, 2);
6719 const char *zAns
= (const char*)sqlite3_column_text(pStmt
, 3);
6723 char *zQuote
= sqlite3_mprintf("%q", zSql
);
6724 printf("%d: %s %s\n", tno
, zOp
, zSql
);
6725 sqlite3_free(zQuote
);
6727 if( strcmp(zOp
,"memo")==0 ){
6728 utf8_printf(p
->out
, "%s\n", zSql
);
6730 if( strcmp(zOp
,"run")==0 ){
6734 rc
= sqlite3_exec(p
->db
, zSql
, captureOutputCallback
, &str
, &zErrMsg
);
6737 utf8_printf(p
->out
, "Result: %s\n", str
.z
);
6739 if( rc
|| zErrMsg
){
6742 utf8_printf(p
->out
, "%d: error-code-%d: %s\n", tno
, rc
, zErrMsg
);
6743 sqlite3_free(zErrMsg
);
6744 }else if( strcmp(zAns
,str
.z
)!=0 ){
6747 utf8_printf(p
->out
, "%d: Expected: [%s]\n", tno
, zAns
);
6748 utf8_printf(p
->out
, "%d: Got: [%s]\n", tno
, str
.z
);
6753 "Unknown operation \"%s\" on selftest line %d\n", zOp
, tno
);
6757 } /* End loop over rows of content from SELFTEST */
6758 sqlite3_finalize(pStmt
);
6759 } /* End loop over k */
6761 utf8_printf(p
->out
, "%d errors out of %d tests\n", nErr
, nTest
);
6764 if( c
=='s' && strncmp(azArg
[0], "separator", n
)==0 ){
6765 if( nArg
<2 || nArg
>3 ){
6766 raw_printf(stderr
, "Usage: .separator COL ?ROW?\n");
6770 sqlite3_snprintf(sizeof(p
->colSeparator
), p
->colSeparator
,
6771 "%.*s", (int)ArraySize(p
->colSeparator
)-1, azArg
[1]);
6774 sqlite3_snprintf(sizeof(p
->rowSeparator
), p
->rowSeparator
,
6775 "%.*s", (int)ArraySize(p
->rowSeparator
)-1, azArg
[2]);
6779 if( c
=='s' && n
>=4 && strncmp(azArg
[0],"sha3sum",n
)==0 ){
6780 const char *zLike
= 0; /* Which table to checksum. 0 means everything */
6781 int i
; /* Loop counter */
6782 int bSchema
= 0; /* Also hash the schema */
6783 int bSeparate
= 0; /* Hash each table separately */
6784 int iSize
= 224; /* Hash algorithm to use */
6785 int bDebug
= 0; /* Only show the query that would have run */
6786 sqlite3_stmt
*pStmt
; /* For querying tables names */
6787 char *zSql
; /* SQL to be run */
6788 char *zSep
; /* Separator */
6789 ShellText sSql
; /* Complete SQL for the query to run the hash */
6790 ShellText sQuery
; /* Set of queries used to read all content */
6792 for(i
=1; i
<nArg
; i
++){
6793 const char *z
= azArg
[i
];
6796 if( z
[0]=='-' ) z
++;
6797 if( strcmp(z
,"schema")==0 ){
6800 if( strcmp(z
,"sha3-224")==0 || strcmp(z
,"sha3-256")==0
6801 || strcmp(z
,"sha3-384")==0 || strcmp(z
,"sha3-512")==0
6803 iSize
= atoi(&z
[5]);
6805 if( strcmp(z
,"debug")==0 ){
6809 utf8_printf(stderr
, "Unknown option \"%s\" on \"%s\"\n",
6810 azArg
[i
], azArg
[0]);
6811 raw_printf(stderr
, "Should be one of: --schema"
6812 " --sha3-224 --sha3-255 --sha3-384 --sha3-512\n");
6814 goto meta_command_exit
;
6817 raw_printf(stderr
, "Usage: .sha3sum ?OPTIONS? ?LIKE-PATTERN?\n");
6819 goto meta_command_exit
;
6823 if( sqlite3_strlike("sqlite_%", zLike
, 0)==0 ) bSchema
= 1;
6827 zSql
= "SELECT lower(name) FROM sqlite_master"
6828 " WHERE type='table' AND coalesce(rootpage,0)>1"
6829 " UNION ALL SELECT 'sqlite_master'"
6830 " ORDER BY 1 collate nocase";
6832 zSql
= "SELECT lower(name) FROM sqlite_master"
6833 " WHERE type='table' AND coalesce(rootpage,0)>1"
6834 " AND name NOT LIKE 'sqlite_%'"
6835 " ORDER BY 1 collate nocase";
6837 sqlite3_prepare_v2(p
->db
, zSql
, -1, &pStmt
, 0);
6840 appendText(&sSql
, "WITH [sha3sum$query](a,b) AS(",0);
6842 while( SQLITE_ROW
==sqlite3_step(pStmt
) ){
6843 const char *zTab
= (const char*)sqlite3_column_text(pStmt
,0);
6844 if( zLike
&& sqlite3_strlike(zLike
, zTab
, 0)!=0 ) continue;
6845 if( strncmp(zTab
, "sqlite_",7)!=0 ){
6846 appendText(&sQuery
,"SELECT * FROM ", 0);
6847 appendText(&sQuery
,zTab
,'"');
6848 appendText(&sQuery
," NOT INDEXED;", 0);
6849 }else if( strcmp(zTab
, "sqlite_master")==0 ){
6850 appendText(&sQuery
,"SELECT type,name,tbl_name,sql FROM sqlite_master"
6851 " ORDER BY name;", 0);
6852 }else if( strcmp(zTab
, "sqlite_sequence")==0 ){
6853 appendText(&sQuery
,"SELECT name,seq FROM sqlite_sequence"
6854 " ORDER BY name;", 0);
6855 }else if( strcmp(zTab
, "sqlite_stat1")==0 ){
6856 appendText(&sQuery
,"SELECT tbl,idx,stat FROM sqlite_stat1"
6857 " ORDER BY tbl,idx;", 0);
6858 }else if( strcmp(zTab
, "sqlite_stat3")==0
6859 || strcmp(zTab
, "sqlite_stat4")==0 ){
6860 appendText(&sQuery
, "SELECT * FROM ", 0);
6861 appendText(&sQuery
, zTab
, 0);
6862 appendText(&sQuery
, " ORDER BY tbl, idx, rowid;\n", 0);
6864 appendText(&sSql
, zSep
, 0);
6865 appendText(&sSql
, sQuery
.z
, '\'');
6867 appendText(&sSql
, ",", 0);
6868 appendText(&sSql
, zTab
, '\'');
6871 sqlite3_finalize(pStmt
);
6873 zSql
= sqlite3_mprintf(
6875 " SELECT lower(hex(sha3_query(a,%d))) AS hash, b AS label"
6876 " FROM [sha3sum$query]",
6879 zSql
= sqlite3_mprintf(
6881 " SELECT lower(hex(sha3_query(group_concat(a,''),%d))) AS hash"
6882 " FROM [sha3sum$query]",
6888 utf8_printf(p
->out
, "%s\n", zSql
);
6890 shell_exec(p
->db
, zSql
, shell_callback
, p
, 0);
6896 && (strncmp(azArg
[0], "shell", n
)==0 || strncmp(azArg
[0],"system",n
)==0)
6901 raw_printf(stderr
, "Usage: .system COMMAND\n");
6903 goto meta_command_exit
;
6905 zCmd
= sqlite3_mprintf(strchr(azArg
[1],' ')==0?"%s":"\"%s\"", azArg
[1]);
6906 for(i
=2; i
<nArg
; i
++){
6907 zCmd
= sqlite3_mprintf(strchr(azArg
[i
],' ')==0?"%z %s":"%z \"%s\"",
6912 if( x
) raw_printf(stderr
, "System command returns %d\n", x
);
6915 if( c
=='s' && strncmp(azArg
[0], "show", n
)==0 ){
6916 static const char *azBool
[] = { "off", "on", "trigger", "full"};
6919 raw_printf(stderr
, "Usage: .show\n");
6921 goto meta_command_exit
;
6923 utf8_printf(p
->out
, "%12.12s: %s\n","echo",
6924 azBool
[ShellHasFlag(p
, SHFLG_Echo
)]);
6925 utf8_printf(p
->out
, "%12.12s: %s\n","eqp", azBool
[p
->autoEQP
&3]);
6926 utf8_printf(p
->out
, "%12.12s: %s\n","explain",
6927 p
->mode
==MODE_Explain
? "on" : p
->autoExplain
? "auto" : "off");
6928 utf8_printf(p
->out
,"%12.12s: %s\n","headers", azBool
[p
->showHeader
!=0]);
6929 utf8_printf(p
->out
, "%12.12s: %s\n","mode", modeDescr
[p
->mode
]);
6930 utf8_printf(p
->out
, "%12.12s: ", "nullvalue");
6931 output_c_string(p
->out
, p
->nullValue
);
6932 raw_printf(p
->out
, "\n");
6933 utf8_printf(p
->out
,"%12.12s: %s\n","output",
6934 strlen30(p
->outfile
) ? p
->outfile
: "stdout");
6935 utf8_printf(p
->out
,"%12.12s: ", "colseparator");
6936 output_c_string(p
->out
, p
->colSeparator
);
6937 raw_printf(p
->out
, "\n");
6938 utf8_printf(p
->out
,"%12.12s: ", "rowseparator");
6939 output_c_string(p
->out
, p
->rowSeparator
);
6940 raw_printf(p
->out
, "\n");
6941 utf8_printf(p
->out
, "%12.12s: %s\n","stats", azBool
[p
->statsOn
!=0]);
6942 utf8_printf(p
->out
, "%12.12s: ", "width");
6943 for (i
=0;i
<(int)ArraySize(p
->colWidth
) && p
->colWidth
[i
] != 0;i
++) {
6944 raw_printf(p
->out
, "%d ", p
->colWidth
[i
]);
6946 raw_printf(p
->out
, "\n");
6947 utf8_printf(p
->out
, "%12.12s: %s\n", "filename",
6948 p
->zDbFilename
? p
->zDbFilename
: "");
6951 if( c
=='s' && strncmp(azArg
[0], "stats", n
)==0 ){
6953 p
->statsOn
= booleanValue(azArg
[1]);
6954 }else if( nArg
==1 ){
6955 display_stats(p
->db
, p
, 0);
6957 raw_printf(stderr
, "Usage: .stats ?on|off?\n");
6962 if( (c
=='t' && n
>1 && strncmp(azArg
[0], "tables", n
)==0)
6963 || (c
=='i' && (strncmp(azArg
[0], "indices", n
)==0
6964 || strncmp(azArg
[0], "indexes", n
)==0) )
6966 sqlite3_stmt
*pStmt
;
6973 rc
= sqlite3_prepare_v2(p
->db
, "PRAGMA database_list", -1, &pStmt
, 0);
6974 if( rc
) return shellDatabaseError(p
->db
);
6976 if( nArg
>2 && c
=='i' ){
6977 /* It is an historical accident that the .indexes command shows an error
6978 ** when called with the wrong number of arguments whereas the .tables
6979 ** command does not. */
6980 raw_printf(stderr
, "Usage: .indexes ?LIKE-PATTERN?\n");
6982 goto meta_command_exit
;
6984 for(ii
=0; sqlite3_step(pStmt
)==SQLITE_ROW
; ii
++){
6985 const char *zDbName
= (const char*)sqlite3_column_text(pStmt
, 1);
6986 if( zDbName
==0 ) continue;
6987 if( s
.z
&& s
.z
[0] ) appendText(&s
, " UNION ALL ", 0);
6988 if( sqlite3_stricmp(zDbName
, "main")==0 ){
6989 appendText(&s
, "SELECT name FROM ", 0);
6991 appendText(&s
, "SELECT ", 0);
6992 appendText(&s
, zDbName
, '\'');
6993 appendText(&s
, "||'.'||name FROM ", 0);
6995 appendText(&s
, zDbName
, '"');
6996 appendText(&s
, ".sqlite_master ", 0);
6998 appendText(&s
," WHERE type IN ('table','view')"
6999 " AND name NOT LIKE 'sqlite_%'"
7000 " AND name LIKE ?1", 0);
7002 appendText(&s
," WHERE type='index'"
7003 " AND tbl_name LIKE ?1", 0);
7006 rc
= sqlite3_finalize(pStmt
);
7007 appendText(&s
, " ORDER BY 1", 0);
7008 rc
= sqlite3_prepare_v2(p
->db
, s
.z
, -1, &pStmt
, 0);
7010 if( rc
) return shellDatabaseError(p
->db
);
7012 /* Run the SQL statement prepared by the above block. Store the results
7013 ** as an array of nul-terminated strings in azResult[]. */
7017 sqlite3_bind_text(pStmt
, 1, azArg
[1], -1, SQLITE_TRANSIENT
);
7019 sqlite3_bind_text(pStmt
, 1, "%", -1, SQLITE_STATIC
);
7021 while( sqlite3_step(pStmt
)==SQLITE_ROW
){
7024 int n2
= nAlloc
*2 + 10;
7025 azNew
= sqlite3_realloc64(azResult
, sizeof(azResult
[0])*n2
);
7027 rc
= shellNomemError();
7033 azResult
[nRow
] = sqlite3_mprintf("%s", sqlite3_column_text(pStmt
, 0));
7034 if( 0==azResult
[nRow
] ){
7035 rc
= shellNomemError();
7040 if( sqlite3_finalize(pStmt
)!=SQLITE_OK
){
7041 rc
= shellDatabaseError(p
->db
);
7044 /* Pretty-print the contents of array azResult[] to the output */
7045 if( rc
==0 && nRow
>0 ){
7046 int len
, maxlen
= 0;
7048 int nPrintCol
, nPrintRow
;
7049 for(i
=0; i
<nRow
; i
++){
7050 len
= strlen30(azResult
[i
]);
7051 if( len
>maxlen
) maxlen
= len
;
7053 nPrintCol
= 80/(maxlen
+2);
7054 if( nPrintCol
<1 ) nPrintCol
= 1;
7055 nPrintRow
= (nRow
+ nPrintCol
- 1)/nPrintCol
;
7056 for(i
=0; i
<nPrintRow
; i
++){
7057 for(j
=i
; j
<nRow
; j
+=nPrintRow
){
7058 char *zSp
= j
<nPrintRow
? "" : " ";
7059 utf8_printf(p
->out
, "%s%-*s", zSp
, maxlen
,
7060 azResult
[j
] ? azResult
[j
]:"");
7062 raw_printf(p
->out
, "\n");
7066 for(ii
=0; ii
<nRow
; ii
++) sqlite3_free(azResult
[ii
]);
7067 sqlite3_free(azResult
);
7070 /* Begin redirecting output to the file "testcase-out.txt" */
7071 if( c
=='t' && strcmp(azArg
[0],"testcase")==0 ){
7073 p
->out
= output_file_open("testcase-out.txt");
7075 raw_printf(stderr
, "Error: cannot open 'testcase-out.txt'\n");
7078 sqlite3_snprintf(sizeof(p
->zTestcase
), p
->zTestcase
, "%s", azArg
[1]);
7080 sqlite3_snprintf(sizeof(p
->zTestcase
), p
->zTestcase
, "?");
7084 #ifndef SQLITE_UNTESTABLE
7085 if( c
=='t' && n
>=8 && strncmp(azArg
[0], "testctrl", n
)==0 ){
7086 static const struct {
7087 const char *zCtrlName
; /* Name of a test-control option */
7088 int ctrlCode
; /* Integer code for that option */
7089 const char *zUsage
; /* Usage notes */
7091 { "always", SQLITE_TESTCTRL_ALWAYS
, "BOOLEAN" },
7092 { "assert", SQLITE_TESTCTRL_ASSERT
, "BOOLEAN" },
7093 /*{ "benign_malloc_hooks",SQLITE_TESTCTRL_BENIGN_MALLOC_HOOKS, "" },*/
7094 /*{ "bitvec_test", SQLITE_TESTCTRL_BITVEC_TEST, "" },*/
7095 { "byteorder", SQLITE_TESTCTRL_BYTEORDER
, "" },
7096 /*{ "fault_install", SQLITE_TESTCTRL_FAULT_INSTALL, "" }, */
7097 { "imposter", SQLITE_TESTCTRL_IMPOSTER
, "SCHEMA ON/OFF ROOTPAGE"},
7098 #ifdef SQLITE_N_KEYWORD
7099 { "iskeyword", SQLITE_TESTCTRL_ISKEYWORD
, "IDENTIFIER" },
7101 { "localtime_fault", SQLITE_TESTCTRL_LOCALTIME_FAULT
,"BOOLEAN" },
7102 { "never_corrupt", SQLITE_TESTCTRL_NEVER_CORRUPT
, "BOOLEAN" },
7103 { "optimizations", SQLITE_TESTCTRL_OPTIMIZATIONS
, "DISABLE-MASK" },
7105 { "parser_coverage", SQLITE_TESTCTRL_PARSER_COVERAGE
, "" },
7107 { "pending_byte", SQLITE_TESTCTRL_PENDING_BYTE
, "OFFSET " },
7108 { "prng_reset", SQLITE_TESTCTRL_PRNG_RESET
, "" },
7109 { "prng_restore", SQLITE_TESTCTRL_PRNG_RESTORE
, "" },
7110 { "prng_save", SQLITE_TESTCTRL_PRNG_SAVE
, "" },
7111 { "reserve", SQLITE_TESTCTRL_RESERVE
, "BYTES-OF-RESERVE" },
7115 int rc2
= 0; /* 0: usage. 1: %d 2: %x 3: no-output */
7118 const char *zCmd
= 0;
7121 zCmd
= nArg
>=2 ? azArg
[1] : "help";
7123 /* The argument can optionally begin with "-" or "--" */
7124 if( zCmd
[0]=='-' && zCmd
[1] ){
7126 if( zCmd
[0]=='-' && zCmd
[1] ) zCmd
++;
7129 /* --help lists all test-controls */
7130 if( strcmp(zCmd
,"help")==0 ){
7131 utf8_printf(p
->out
, "Available test-controls:\n");
7132 for(i
=0; i
<ArraySize(aCtrl
); i
++){
7133 utf8_printf(p
->out
, " .testctrl %s %s\n",
7134 aCtrl
[i
].zCtrlName
, aCtrl
[i
].zUsage
);
7137 goto meta_command_exit
;
7140 /* convert testctrl text option to value. allow any unique prefix
7141 ** of the option name, or a numerical value. */
7142 n2
= strlen30(zCmd
);
7143 for(i
=0; i
<ArraySize(aCtrl
); i
++){
7144 if( strncmp(zCmd
, aCtrl
[i
].zCtrlName
, n2
)==0 ){
7146 testctrl
= aCtrl
[i
].ctrlCode
;
7149 utf8_printf(stderr
, "Error: ambiguous test-control: \"%s\"\n"
7150 "Use \".testctrl --help\" for help\n", zCmd
);
7152 goto meta_command_exit
;
7157 utf8_printf(stderr
,"Error: unknown test-control: %s\n"
7158 "Use \".testctrl --help\" for help\n", zCmd
);
7162 /* sqlite3_test_control(int, db, int) */
7163 case SQLITE_TESTCTRL_OPTIMIZATIONS
:
7164 case SQLITE_TESTCTRL_RESERVE
:
7166 int opt
= (int)strtol(azArg
[2], 0, 0);
7167 rc2
= sqlite3_test_control(testctrl
, p
->db
, opt
);
7172 /* sqlite3_test_control(int) */
7173 case SQLITE_TESTCTRL_PRNG_SAVE
:
7174 case SQLITE_TESTCTRL_PRNG_RESTORE
:
7175 case SQLITE_TESTCTRL_PRNG_RESET
:
7176 case SQLITE_TESTCTRL_BYTEORDER
:
7178 rc2
= sqlite3_test_control(testctrl
);
7179 isOk
= testctrl
==SQLITE_TESTCTRL_BYTEORDER
? 1 : 3;
7183 /* sqlite3_test_control(int, uint) */
7184 case SQLITE_TESTCTRL_PENDING_BYTE
:
7186 unsigned int opt
= (unsigned int)integerValue(azArg
[2]);
7187 rc2
= sqlite3_test_control(testctrl
, opt
);
7192 /* sqlite3_test_control(int, int) */
7193 case SQLITE_TESTCTRL_ASSERT
:
7194 case SQLITE_TESTCTRL_ALWAYS
:
7196 int opt
= booleanValue(azArg
[2]);
7197 rc2
= sqlite3_test_control(testctrl
, opt
);
7202 /* sqlite3_test_control(int, int) */
7203 case SQLITE_TESTCTRL_LOCALTIME_FAULT
:
7204 case SQLITE_TESTCTRL_NEVER_CORRUPT
:
7206 int opt
= booleanValue(azArg
[2]);
7207 rc2
= sqlite3_test_control(testctrl
, opt
);
7212 /* sqlite3_test_control(int, char *) */
7213 #ifdef SQLITE_N_KEYWORD
7214 case SQLITE_TESTCTRL_ISKEYWORD
:
7216 const char *opt
= azArg
[2];
7217 rc2
= sqlite3_test_control(testctrl
, opt
);
7223 case SQLITE_TESTCTRL_IMPOSTER
:
7225 rc2
= sqlite3_test_control(testctrl
, p
->db
,
7227 integerValue(azArg
[3]),
7228 integerValue(azArg
[4]));
7234 case SQLITE_TESTCTRL_PARSER_COVERAGE
:
7236 sqlite3_test_control(testctrl
, p
->out
);
7242 if( isOk
==0 && iCtrl
>=0 ){
7243 utf8_printf(p
->out
, "Usage: .testctrl %s %s\n", zCmd
, aCtrl
[iCtrl
].zUsage
);
7245 }else if( isOk
==1 ){
7246 raw_printf(p
->out
, "%d\n", rc2
);
7247 }else if( isOk
==2 ){
7248 raw_printf(p
->out
, "0x%08x\n", rc2
);
7251 #endif /* !defined(SQLITE_UNTESTABLE) */
7253 if( c
=='t' && n
>4 && strncmp(azArg
[0], "timeout", n
)==0 ){
7255 sqlite3_busy_timeout(p
->db
, nArg
>=2 ? (int)integerValue(azArg
[1]) : 0);
7258 if( c
=='t' && n
>=5 && strncmp(azArg
[0], "timer", n
)==0 ){
7260 enableTimer
= booleanValue(azArg
[1]);
7261 if( enableTimer
&& !HAS_TIMER
){
7262 raw_printf(stderr
, "Error: timer not available on this system.\n");
7266 raw_printf(stderr
, "Usage: .timer on|off\n");
7271 if( c
=='t' && strncmp(azArg
[0], "trace", n
)==0 ){
7274 raw_printf(stderr
, "Usage: .trace FILE|off\n");
7276 goto meta_command_exit
;
7278 output_file_close(p
->traceOut
);
7279 p
->traceOut
= output_file_open(azArg
[1]);
7280 #if !defined(SQLITE_OMIT_TRACE) && !defined(SQLITE_OMIT_FLOATING_POINT)
7281 if( p
->traceOut
==0 ){
7282 sqlite3_trace_v2(p
->db
, 0, 0, 0);
7284 sqlite3_trace_v2(p
->db
, SQLITE_TRACE_STMT
, sql_trace_callback
,p
->traceOut
);
7289 #if SQLITE_USER_AUTHENTICATION
7290 if( c
=='u' && strncmp(azArg
[0], "user", n
)==0 ){
7292 raw_printf(stderr
, "Usage: .user SUBCOMMAND ...\n");
7294 goto meta_command_exit
;
7297 if( strcmp(azArg
[1],"login")==0 ){
7299 raw_printf(stderr
, "Usage: .user login USER PASSWORD\n");
7301 goto meta_command_exit
;
7303 rc
= sqlite3_user_authenticate(p
->db
, azArg
[2], azArg
[3], strlen30(azArg
[3]));
7305 utf8_printf(stderr
, "Authentication failed for user %s\n", azArg
[2]);
7308 }else if( strcmp(azArg
[1],"add")==0 ){
7310 raw_printf(stderr
, "Usage: .user add USER PASSWORD ISADMIN\n");
7312 goto meta_command_exit
;
7314 rc
= sqlite3_user_add(p
->db
, azArg
[2], azArg
[3], strlen30(azArg
[3]),
7315 booleanValue(azArg
[4]));
7317 raw_printf(stderr
, "User-Add failed: %d\n", rc
);
7320 }else if( strcmp(azArg
[1],"edit")==0 ){
7322 raw_printf(stderr
, "Usage: .user edit USER PASSWORD ISADMIN\n");
7324 goto meta_command_exit
;
7326 rc
= sqlite3_user_change(p
->db
, azArg
[2], azArg
[3], strlen30(azArg
[3]),
7327 booleanValue(azArg
[4]));
7329 raw_printf(stderr
, "User-Edit failed: %d\n", rc
);
7332 }else if( strcmp(azArg
[1],"delete")==0 ){
7334 raw_printf(stderr
, "Usage: .user delete USER\n");
7336 goto meta_command_exit
;
7338 rc
= sqlite3_user_delete(p
->db
, azArg
[2]);
7340 raw_printf(stderr
, "User-Delete failed: %d\n", rc
);
7344 raw_printf(stderr
, "Usage: .user login|add|edit|delete ...\n");
7346 goto meta_command_exit
;
7349 #endif /* SQLITE_USER_AUTHENTICATION */
7351 if( c
=='v' && strncmp(azArg
[0], "version", n
)==0 ){
7352 utf8_printf(p
->out
, "SQLite %s %s\n" /*extra-version-info*/,
7353 sqlite3_libversion(), sqlite3_sourceid());
7356 if( c
=='v' && strncmp(azArg
[0], "vfsinfo", n
)==0 ){
7357 const char *zDbName
= nArg
==2 ? azArg
[1] : "main";
7358 sqlite3_vfs
*pVfs
= 0;
7360 sqlite3_file_control(p
->db
, zDbName
, SQLITE_FCNTL_VFS_POINTER
, &pVfs
);
7362 utf8_printf(p
->out
, "vfs.zName = \"%s\"\n", pVfs
->zName
);
7363 raw_printf(p
->out
, "vfs.iVersion = %d\n", pVfs
->iVersion
);
7364 raw_printf(p
->out
, "vfs.szOsFile = %d\n", pVfs
->szOsFile
);
7365 raw_printf(p
->out
, "vfs.mxPathname = %d\n", pVfs
->mxPathname
);
7370 if( c
=='v' && strncmp(azArg
[0], "vfslist", n
)==0 ){
7372 sqlite3_vfs
*pCurrent
= 0;
7374 sqlite3_file_control(p
->db
, "main", SQLITE_FCNTL_VFS_POINTER
, &pCurrent
);
7376 for(pVfs
=sqlite3_vfs_find(0); pVfs
; pVfs
=pVfs
->pNext
){
7377 utf8_printf(p
->out
, "vfs.zName = \"%s\"%s\n", pVfs
->zName
,
7378 pVfs
==pCurrent
? " <--- CURRENT" : "");
7379 raw_printf(p
->out
, "vfs.iVersion = %d\n", pVfs
->iVersion
);
7380 raw_printf(p
->out
, "vfs.szOsFile = %d\n", pVfs
->szOsFile
);
7381 raw_printf(p
->out
, "vfs.mxPathname = %d\n", pVfs
->mxPathname
);
7383 raw_printf(p
->out
, "-----------------------------------\n");
7388 if( c
=='v' && strncmp(azArg
[0], "vfsname", n
)==0 ){
7389 const char *zDbName
= nArg
==2 ? azArg
[1] : "main";
7392 sqlite3_file_control(p
->db
, zDbName
, SQLITE_FCNTL_VFSNAME
, &zVfsName
);
7394 utf8_printf(p
->out
, "%s\n", zVfsName
);
7395 sqlite3_free(zVfsName
);
7400 #if defined(SQLITE_DEBUG) && defined(SQLITE_ENABLE_WHERETRACE)
7401 if( c
=='w' && strncmp(azArg
[0], "wheretrace", n
)==0 ){
7402 sqlite3WhereTrace
= nArg
>=2 ? booleanValue(azArg
[1]) : 0xff;
7406 if( c
=='w' && strncmp(azArg
[0], "width", n
)==0 ){
7408 assert( nArg
<=ArraySize(azArg
) );
7409 for(j
=1; j
<nArg
&& j
<ArraySize(p
->colWidth
); j
++){
7410 p
->colWidth
[j
-1] = (int)integerValue(azArg
[j
]);
7415 utf8_printf(stderr
, "Error: unknown command or invalid arguments: "
7416 " \"%s\". Enter \".help\" for help\n", azArg
[0]);
7423 if( p
->outCount
==0 ) output_reset(p
);
7429 ** Return TRUE if a semicolon occurs anywhere in the first N characters
7432 static int line_contains_semicolon(const char *z
, int N
){
7434 for(i
=0; i
<N
; i
++){ if( z
[i
]==';' ) return 1; }
7439 ** Test to see if a line consists entirely of whitespace.
7441 static int _all_whitespace(const char *z
){
7443 if( IsSpace(z
[0]) ) continue;
7444 if( *z
=='/' && z
[1]=='*' ){
7446 while( *z
&& (*z
!='*' || z
[1]!='/') ){ z
++; }
7447 if( *z
==0 ) return 0;
7451 if( *z
=='-' && z
[1]=='-' ){
7453 while( *z
&& *z
!='\n' ){ z
++; }
7454 if( *z
==0 ) return 1;
7463 ** Return TRUE if the line typed in is an SQL command terminator other
7464 ** than a semi-colon. The SQL Server style "go" command is understood
7465 ** as is the Oracle "/".
7467 static int line_is_command_terminator(const char *zLine
){
7468 while( IsSpace(zLine
[0]) ){ zLine
++; };
7469 if( zLine
[0]=='/' && _all_whitespace(&zLine
[1]) ){
7470 return 1; /* Oracle */
7472 if( ToLower(zLine
[0])=='g' && ToLower(zLine
[1])=='o'
7473 && _all_whitespace(&zLine
[2]) ){
7474 return 1; /* SQL Server */
7480 ** Return true if zSql is a complete SQL statement. Return false if it
7481 ** ends in the middle of a string literal or C-style comment.
7483 static int line_is_complete(char *zSql
, int nSql
){
7485 if( zSql
==0 ) return 1;
7488 rc
= sqlite3_complete(zSql
);
7494 ** Run a single line of SQL
7496 static int runOneSqlLine(ShellState
*p
, char *zSql
, FILE *in
, int startline
){
7501 if( ShellHasFlag(p
,SHFLG_Backslash
) ) resolve_backslashes(zSql
);
7503 rc
= shell_exec(p
->db
, zSql
, shell_callback
, p
, &zErrMsg
);
7505 if( rc
|| zErrMsg
){
7507 if( in
!=0 || !stdin_is_interactive
){
7508 sqlite3_snprintf(sizeof(zPrefix
), zPrefix
,
7509 "Error: near line %d:", startline
);
7511 sqlite3_snprintf(sizeof(zPrefix
), zPrefix
, "Error:");
7514 utf8_printf(stderr
, "%s %s\n", zPrefix
, zErrMsg
);
7515 sqlite3_free(zErrMsg
);
7518 utf8_printf(stderr
, "%s %s\n", zPrefix
, sqlite3_errmsg(p
->db
));
7521 }else if( ShellHasFlag(p
, SHFLG_CountChanges
) ){
7522 raw_printf(p
->out
, "changes: %3d total_changes: %d\n",
7523 sqlite3_changes(p
->db
), sqlite3_total_changes(p
->db
));
7530 ** Read input from *in and process it. If *in==0 then input
7531 ** is interactive - the user is typing it it. Otherwise, input
7532 ** is coming from a file or device. A prompt is issued and history
7533 ** is saved only if input is interactive. An interrupt signal will
7534 ** cause this routine to exit immediately, unless input is interactive.
7536 ** Return the number of errors.
7538 static int process_input(ShellState
*p
, FILE *in
){
7539 char *zLine
= 0; /* A single input line */
7540 char *zSql
= 0; /* Accumulated SQL text */
7541 int nLine
; /* Length of current line */
7542 int nSql
= 0; /* Bytes of zSql[] used */
7543 int nAlloc
= 0; /* Allocated zSql[] space */
7544 int nSqlPrior
= 0; /* Bytes of zSql[] used by prior line */
7545 int rc
; /* Error code */
7546 int errCnt
= 0; /* Number of errors seen */
7547 int lineno
= 0; /* Current line number */
7548 int startline
= 0; /* Line number for start of current input */
7550 while( errCnt
==0 || !bail_on_error
|| (in
==0 && stdin_is_interactive
) ){
7552 zLine
= one_input_line(in
, zLine
, nSql
>0);
7555 if( in
==0 && stdin_is_interactive
) printf("\n");
7558 if( seenInterrupt
){
7563 if( nSql
==0 && _all_whitespace(zLine
) ){
7564 if( ShellHasFlag(p
, SHFLG_Echo
) ) printf("%s\n", zLine
);
7567 if( zLine
&& zLine
[0]=='.' && nSql
==0 ){
7568 if( ShellHasFlag(p
, SHFLG_Echo
) ) printf("%s\n", zLine
);
7569 rc
= do_meta_command(zLine
, p
);
7570 if( rc
==2 ){ /* exit requested */
7577 if( line_is_command_terminator(zLine
) && line_is_complete(zSql
, nSql
) ){
7578 memcpy(zLine
,";",2);
7580 nLine
= strlen30(zLine
);
7581 if( nSql
+nLine
+2>=nAlloc
){
7582 nAlloc
= nSql
+nLine
+100;
7583 zSql
= realloc(zSql
, nAlloc
);
7585 raw_printf(stderr
, "Error: out of memory\n");
7592 for(i
=0; zLine
[i
] && IsSpace(zLine
[i
]); i
++){}
7593 assert( nAlloc
>0 && zSql
!=0 );
7594 memcpy(zSql
, zLine
+i
, nLine
+1-i
);
7598 zSql
[nSql
++] = '\n';
7599 memcpy(zSql
+nSql
, zLine
, nLine
+1);
7602 if( nSql
&& line_contains_semicolon(&zSql
[nSqlPrior
], nSql
-nSqlPrior
)
7603 && sqlite3_complete(zSql
) ){
7604 errCnt
+= runOneSqlLine(p
, zSql
, in
, startline
);
7610 }else if( nSql
&& _all_whitespace(zSql
) ){
7611 if( ShellHasFlag(p
, SHFLG_Echo
) ) printf("%s\n", zSql
);
7615 if( nSql
&& !_all_whitespace(zSql
) ){
7616 runOneSqlLine(p
, zSql
, in
, startline
);
7624 ** Return a pathname which is the user's home directory. A
7625 ** 0 return indicates an error of some kind.
7627 static char *find_home_dir(int clearFlag
){
7628 static char *home_dir
= NULL
;
7634 if( home_dir
) return home_dir
;
7636 #if !defined(_WIN32) && !defined(WIN32) && !defined(_WIN32_WCE) \
7637 && !defined(__RTP__) && !defined(_WRS_KERNEL)
7639 struct passwd
*pwent
;
7640 uid_t uid
= getuid();
7641 if( (pwent
=getpwuid(uid
)) != NULL
) {
7642 home_dir
= pwent
->pw_dir
;
7647 #if defined(_WIN32_WCE)
7648 /* Windows CE (arm-wince-mingw32ce-gcc) does not provide getenv()
7653 #if defined(_WIN32) || defined(WIN32)
7655 home_dir
= getenv("USERPROFILE");
7660 home_dir
= getenv("HOME");
7663 #if defined(_WIN32) || defined(WIN32)
7665 char *zDrive
, *zPath
;
7667 zDrive
= getenv("HOMEDRIVE");
7668 zPath
= getenv("HOMEPATH");
7669 if( zDrive
&& zPath
){
7670 n
= strlen30(zDrive
) + strlen30(zPath
) + 1;
7671 home_dir
= malloc( n
);
7672 if( home_dir
==0 ) return 0;
7673 sqlite3_snprintf(n
, home_dir
, "%s%s", zDrive
, zPath
);
7680 #endif /* !_WIN32_WCE */
7683 int n
= strlen30(home_dir
) + 1;
7684 char *z
= malloc( n
);
7685 if( z
) memcpy(z
, home_dir
, n
);
7693 ** Read input from the file given by sqliterc_override. Or if that
7694 ** parameter is NULL, take input from ~/.sqliterc
7696 ** Returns the number of errors.
7698 static void process_sqliterc(
7699 ShellState
*p
, /* Configuration data */
7700 const char *sqliterc_override
/* Name of config file. NULL to use default */
7702 char *home_dir
= NULL
;
7703 const char *sqliterc
= sqliterc_override
;
7707 if (sqliterc
== NULL
) {
7708 home_dir
= find_home_dir(0);
7710 raw_printf(stderr
, "-- warning: cannot find home directory;"
7711 " cannot read ~/.sqliterc\n");
7714 sqlite3_initialize();
7715 zBuf
= sqlite3_mprintf("%s/.sqliterc",home_dir
);
7718 in
= fopen(sqliterc
,"rb");
7720 if( stdin_is_interactive
){
7721 utf8_printf(stderr
,"-- Loading resources from %s\n",sqliterc
);
7723 process_input(p
,in
);
7730 ** Show available command line options
7732 static const char zOptions
[] =
7733 " -ascii set output mode to 'ascii'\n"
7734 " -bail stop after hitting an error\n"
7735 " -batch force batch I/O\n"
7736 " -column set output mode to 'column'\n"
7737 " -cmd COMMAND run \"COMMAND\" before reading stdin\n"
7738 " -csv set output mode to 'csv'\n"
7739 " -echo print commands before execution\n"
7740 " -init FILENAME read/process named file\n"
7741 " -[no]header turn headers on or off\n"
7742 #if defined(SQLITE_ENABLE_MEMSYS3) || defined(SQLITE_ENABLE_MEMSYS5)
7743 " -heap SIZE Size of heap for memsys3 or memsys5\n"
7745 " -help show this message\n"
7746 " -html set output mode to HTML\n"
7747 " -interactive force interactive I/O\n"
7748 " -line set output mode to 'line'\n"
7749 " -list set output mode to 'list'\n"
7750 " -lookaside SIZE N use N entries of SZ bytes for lookaside memory\n"
7751 " -mmap N default mmap size set to N\n"
7752 #ifdef SQLITE_ENABLE_MULTIPLEX
7753 " -multiplex enable the multiplexor VFS\n"
7755 " -newline SEP set output row separator. Default: '\\n'\n"
7756 " -nullvalue TEXT set text string for NULL values. Default ''\n"
7757 " -pagecache SIZE N use N slots of SZ bytes each for page cache memory\n"
7758 " -quote set output mode to 'quote'\n"
7759 " -separator SEP set output column separator. Default: '|'\n"
7760 " -stats print memory stats before each finalize\n"
7761 " -version show SQLite version\n"
7762 " -vfs NAME use NAME as the default VFS\n"
7763 #ifdef SQLITE_ENABLE_VFSTRACE
7764 " -vfstrace enable tracing of all VFS calls\n"
7767 static void usage(int showDetail
){
7769 "Usage: %s [OPTIONS] FILENAME [SQL]\n"
7770 "FILENAME is the name of an SQLite database. A new database is created\n"
7771 "if the file does not previously exist.\n", Argv0
);
7773 utf8_printf(stderr
, "OPTIONS include:\n%s", zOptions
);
7775 raw_printf(stderr
, "Use the -help option for additional information\n");
7781 ** Initialize the state information in data
7783 static void main_init(ShellState
*data
) {
7784 memset(data
, 0, sizeof(*data
));
7785 data
->normalMode
= data
->cMode
= data
->mode
= MODE_List
;
7786 data
->autoExplain
= 1;
7787 memcpy(data
->colSeparator
,SEP_Column
, 2);
7788 memcpy(data
->rowSeparator
,SEP_Row
, 2);
7789 data
->showHeader
= 0;
7790 data
->shellFlgs
= SHFLG_Lookaside
;
7791 sqlite3_config(SQLITE_CONFIG_URI
, 1);
7792 sqlite3_config(SQLITE_CONFIG_LOG
, shellLog
, data
);
7793 sqlite3_config(SQLITE_CONFIG_MULTITHREAD
);
7794 sqlite3_snprintf(sizeof(mainPrompt
), mainPrompt
,"sqlite> ");
7795 sqlite3_snprintf(sizeof(continuePrompt
), continuePrompt
," ...> ");
7799 ** Output text to the console in a font that attracts extra attention.
7802 static void printBold(const char *zText
){
7803 HANDLE out
= GetStdHandle(STD_OUTPUT_HANDLE
);
7804 CONSOLE_SCREEN_BUFFER_INFO defaultScreenInfo
;
7805 GetConsoleScreenBufferInfo(out
, &defaultScreenInfo
);
7806 SetConsoleTextAttribute(out
,
7807 FOREGROUND_RED
|FOREGROUND_INTENSITY
7809 printf("%s", zText
);
7810 SetConsoleTextAttribute(out
, defaultScreenInfo
.wAttributes
);
7813 static void printBold(const char *zText
){
7814 printf("\033[1m%s\033[0m", zText
);
7819 ** Get the argument to an --option. Throw an error and die if no argument
7822 static char *cmdline_option_value(int argc
, char **argv
, int i
){
7824 utf8_printf(stderr
, "%s: Error: missing argument to %s\n",
7825 argv
[0], argv
[argc
-1]);
7831 #ifndef SQLITE_SHELL_IS_UTF8
7832 # if (defined(_WIN32) || defined(WIN32)) && defined(_MSC_VER)
7833 # define SQLITE_SHELL_IS_UTF8 (0)
7835 # define SQLITE_SHELL_IS_UTF8 (1)
7839 #if SQLITE_SHELL_IS_UTF8
7840 int SQLITE_CDECL
main(int argc
, char **argv
){
7842 int SQLITE_CDECL
wmain(int argc
, wchar_t **wargv
){
7847 const char *zInitFile
= 0;
7850 int warnInmemoryDb
= 0;
7855 setBinaryMode(stdin
, 0);
7856 setvbuf(stderr
, 0, _IONBF
, 0); /* Make sure stderr is unbuffered */
7857 stdin_is_interactive
= isatty(0);
7858 stdout_is_console
= isatty(1);
7860 #if USE_SYSTEM_SQLITE+0!=1
7861 if( strncmp(sqlite3_sourceid(),SQLITE_SOURCE_ID
,60)!=0 ){
7862 utf8_printf(stderr
, "SQLite header and source version mismatch\n%s\n%s\n",
7863 sqlite3_sourceid(), SQLITE_SOURCE_ID
);
7868 #if !SQLITE_SHELL_IS_UTF8
7869 sqlite3_initialize();
7870 argv
= sqlite3_malloc64(sizeof(argv
[0])*argc
);
7872 raw_printf(stderr
, "out of memory\n");
7875 for(i
=0; i
<argc
; i
++){
7876 argv
[i
] = sqlite3_win32_unicode_to_utf8(wargv
[i
]);
7878 raw_printf(stderr
, "out of memory\n");
7883 assert( argc
>=1 && argv
&& argv
[0] );
7886 /* Make sure we have a valid signal handler early, before anything
7890 signal(SIGINT
, interrupt_handler
);
7891 #elif (defined(_WIN32) || defined(WIN32)) && !defined(_WIN32_WCE)
7892 SetConsoleCtrlHandler(ConsoleCtrlHandler
, TRUE
);
7895 #ifdef SQLITE_SHELL_DBNAME_PROC
7897 /* If the SQLITE_SHELL_DBNAME_PROC macro is defined, then it is the name
7898 ** of a C-function that will provide the name of the database file. Use
7899 ** this compile-time option to embed this shell program in larger
7901 extern void SQLITE_SHELL_DBNAME_PROC(const char**);
7902 SQLITE_SHELL_DBNAME_PROC(&data
.zDbFilename
);
7907 /* Do an initial pass through the command-line argument to locate
7908 ** the name of the database file, the name of the initialization file,
7909 ** the size of the alternative malloc heap,
7910 ** and the first command to execute.
7912 for(i
=1; i
<argc
; i
++){
7916 if( data
.zDbFilename
==0 ){
7917 data
.zDbFilename
= z
;
7919 /* Excesss arguments are interpreted as SQL (or dot-commands) and
7920 ** mean that nothing is read from stdin */
7923 azCmd
= realloc(azCmd
, sizeof(azCmd
[0])*nCmd
);
7925 raw_printf(stderr
, "out of memory\n");
7931 if( z
[1]=='-' ) z
++;
7932 if( strcmp(z
,"-separator")==0
7933 || strcmp(z
,"-nullvalue")==0
7934 || strcmp(z
,"-newline")==0
7935 || strcmp(z
,"-cmd")==0
7937 (void)cmdline_option_value(argc
, argv
, ++i
);
7938 }else if( strcmp(z
,"-init")==0 ){
7939 zInitFile
= cmdline_option_value(argc
, argv
, ++i
);
7940 }else if( strcmp(z
,"-batch")==0 ){
7941 /* Need to check for batch mode here to so we can avoid printing
7942 ** informational messages (like from process_sqliterc) before
7943 ** we do the actual processing of arguments later in a second pass.
7945 stdin_is_interactive
= 0;
7946 }else if( strcmp(z
,"-heap")==0 ){
7947 #if defined(SQLITE_ENABLE_MEMSYS3) || defined(SQLITE_ENABLE_MEMSYS5)
7949 sqlite3_int64 szHeap
;
7951 zSize
= cmdline_option_value(argc
, argv
, ++i
);
7952 szHeap
= integerValue(zSize
);
7953 if( szHeap
>0x7fff0000 ) szHeap
= 0x7fff0000;
7954 sqlite3_config(SQLITE_CONFIG_HEAP
, malloc((int)szHeap
), (int)szHeap
, 64);
7956 (void)cmdline_option_value(argc
, argv
, ++i
);
7958 }else if( strcmp(z
,"-pagecache")==0 ){
7960 sz
= (int)integerValue(cmdline_option_value(argc
,argv
,++i
));
7961 if( sz
>70000 ) sz
= 70000;
7963 n
= (int)integerValue(cmdline_option_value(argc
,argv
,++i
));
7964 sqlite3_config(SQLITE_CONFIG_PAGECACHE
,
7965 (n
>0 && sz
>0) ? malloc(n
*sz
) : 0, sz
, n
);
7966 data
.shellFlgs
|= SHFLG_Pagecache
;
7967 }else if( strcmp(z
,"-lookaside")==0 ){
7969 sz
= (int)integerValue(cmdline_option_value(argc
,argv
,++i
));
7971 n
= (int)integerValue(cmdline_option_value(argc
,argv
,++i
));
7973 sqlite3_config(SQLITE_CONFIG_LOOKASIDE
, sz
, n
);
7974 if( sz
*n
==0 ) data
.shellFlgs
&= ~SHFLG_Lookaside
;
7975 #ifdef SQLITE_ENABLE_VFSTRACE
7976 }else if( strcmp(z
,"-vfstrace")==0 ){
7977 extern int vfstrace_register(
7978 const char *zTraceName
,
7979 const char *zOldVfsName
,
7980 int (*xOut
)(const char*,void*),
7984 vfstrace_register("trace",0,(int(*)(const char*,void*))fputs
,stderr
,1);
7986 #ifdef SQLITE_ENABLE_MULTIPLEX
7987 }else if( strcmp(z
,"-multiplex")==0 ){
7988 extern int sqlite3_multiple_initialize(const char*,int);
7989 sqlite3_multiplex_initialize(0, 1);
7991 }else if( strcmp(z
,"-mmap")==0 ){
7992 sqlite3_int64 sz
= integerValue(cmdline_option_value(argc
,argv
,++i
));
7993 sqlite3_config(SQLITE_CONFIG_MMAP_SIZE
, sz
, sz
);
7994 }else if( strcmp(z
,"-vfs")==0 ){
7995 sqlite3_vfs
*pVfs
= sqlite3_vfs_find(cmdline_option_value(argc
,argv
,++i
));
7997 sqlite3_vfs_register(pVfs
, 1);
7999 utf8_printf(stderr
, "no such VFS: \"%s\"\n", argv
[i
]);
8002 #ifdef SQLITE_HAVE_ZIP
8003 }else if( strcmp(z
,"-zip")==0 ){
8004 data
.openMode
= SHELL_OPEN_ZIPFILE
;
8006 }else if( strcmp(z
,"-append")==0 ){
8007 data
.openMode
= SHELL_OPEN_APPENDVFS
;
8010 if( data
.zDbFilename
==0 ){
8011 #ifndef SQLITE_OMIT_MEMORYDB
8012 data
.zDbFilename
= ":memory:";
8013 warnInmemoryDb
= argc
==1;
8015 utf8_printf(stderr
,"%s: Error: no database filename specified\n", Argv0
);
8020 sqlite3_appendvfs_init(0,0,0);
8022 /* Go ahead and open the database file if it already exists. If the
8023 ** file does not exist, delay opening it. This prevents empty database
8024 ** files from being created if a user mistypes the database name argument
8025 ** to the sqlite command-line tool.
8027 if( access(data
.zDbFilename
, 0)==0 ){
8031 /* Process the initialization file if there is one. If no -init option
8032 ** is given on the command line, look for a file named ~/.sqliterc and
8033 ** try to process it.
8035 process_sqliterc(&data
,zInitFile
);
8037 /* Make a second pass through the command-line argument and set
8038 ** options. This second pass is delayed until after the initialization
8039 ** file is processed so that the command-line arguments will override
8040 ** settings in the initialization file.
8042 for(i
=1; i
<argc
; i
++){
8044 if( z
[0]!='-' ) continue;
8045 if( z
[1]=='-' ){ z
++; }
8046 if( strcmp(z
,"-init")==0 ){
8048 }else if( strcmp(z
,"-html")==0 ){
8049 data
.mode
= MODE_Html
;
8050 }else if( strcmp(z
,"-list")==0 ){
8051 data
.mode
= MODE_List
;
8052 }else if( strcmp(z
,"-quote")==0 ){
8053 data
.mode
= MODE_Quote
;
8054 }else if( strcmp(z
,"-line")==0 ){
8055 data
.mode
= MODE_Line
;
8056 }else if( strcmp(z
,"-column")==0 ){
8057 data
.mode
= MODE_Column
;
8058 }else if( strcmp(z
,"-csv")==0 ){
8059 data
.mode
= MODE_Csv
;
8060 memcpy(data
.colSeparator
,",",2);
8061 #ifdef SQLITE_HAVE_ZIP
8062 }else if( strcmp(z
,"-zip")==0 ){
8063 data
.openMode
= SHELL_OPEN_ZIPFILE
;
8065 }else if( strcmp(z
,"-append")==0 ){
8066 data
.openMode
= SHELL_OPEN_APPENDVFS
;
8067 }else if( strcmp(z
,"-ascii")==0 ){
8068 data
.mode
= MODE_Ascii
;
8069 sqlite3_snprintf(sizeof(data
.colSeparator
), data
.colSeparator
,
8071 sqlite3_snprintf(sizeof(data
.rowSeparator
), data
.rowSeparator
,
8073 }else if( strcmp(z
,"-separator")==0 ){
8074 sqlite3_snprintf(sizeof(data
.colSeparator
), data
.colSeparator
,
8075 "%s",cmdline_option_value(argc
,argv
,++i
));
8076 }else if( strcmp(z
,"-newline")==0 ){
8077 sqlite3_snprintf(sizeof(data
.rowSeparator
), data
.rowSeparator
,
8078 "%s",cmdline_option_value(argc
,argv
,++i
));
8079 }else if( strcmp(z
,"-nullvalue")==0 ){
8080 sqlite3_snprintf(sizeof(data
.nullValue
), data
.nullValue
,
8081 "%s",cmdline_option_value(argc
,argv
,++i
));
8082 }else if( strcmp(z
,"-header")==0 ){
8083 data
.showHeader
= 1;
8084 }else if( strcmp(z
,"-noheader")==0 ){
8085 data
.showHeader
= 0;
8086 }else if( strcmp(z
,"-echo")==0 ){
8087 ShellSetFlag(&data
, SHFLG_Echo
);
8088 }else if( strcmp(z
,"-eqp")==0 ){
8089 data
.autoEQP
= AUTOEQP_on
;
8090 }else if( strcmp(z
,"-eqpfull")==0 ){
8091 data
.autoEQP
= AUTOEQP_full
;
8092 }else if( strcmp(z
,"-stats")==0 ){
8094 }else if( strcmp(z
,"-scanstats")==0 ){
8095 data
.scanstatsOn
= 1;
8096 }else if( strcmp(z
,"-backslash")==0 ){
8097 /* Undocumented command-line option: -backslash
8098 ** Causes C-style backslash escapes to be evaluated in SQL statements
8099 ** prior to sending the SQL into SQLite. Useful for injecting
8100 ** crazy bytes in the middle of SQL statements for testing and debugging.
8102 ShellSetFlag(&data
, SHFLG_Backslash
);
8103 }else if( strcmp(z
,"-bail")==0 ){
8105 }else if( strcmp(z
,"-version")==0 ){
8106 printf("%s %s\n", sqlite3_libversion(), sqlite3_sourceid());
8108 }else if( strcmp(z
,"-interactive")==0 ){
8109 stdin_is_interactive
= 1;
8110 }else if( strcmp(z
,"-batch")==0 ){
8111 stdin_is_interactive
= 0;
8112 }else if( strcmp(z
,"-heap")==0 ){
8114 }else if( strcmp(z
,"-pagecache")==0 ){
8116 }else if( strcmp(z
,"-lookaside")==0 ){
8118 }else if( strcmp(z
,"-mmap")==0 ){
8120 }else if( strcmp(z
,"-vfs")==0 ){
8122 #ifdef SQLITE_ENABLE_VFSTRACE
8123 }else if( strcmp(z
,"-vfstrace")==0 ){
8126 #ifdef SQLITE_ENABLE_MULTIPLEX
8127 }else if( strcmp(z
,"-multiplex")==0 ){
8130 }else if( strcmp(z
,"-help")==0 ){
8132 }else if( strcmp(z
,"-cmd")==0 ){
8133 /* Run commands that follow -cmd first and separately from commands
8134 ** that simply appear on the command-line. This seems goofy. It would
8135 ** be better if all commands ran in the order that they appear. But
8136 ** we retain the goofy behavior for historical compatibility. */
8137 if( i
==argc
-1 ) break;
8138 z
= cmdline_option_value(argc
,argv
,++i
);
8140 rc
= do_meta_command(z
, &data
);
8141 if( rc
&& bail_on_error
) return rc
==2 ? 0 : rc
;
8144 rc
= shell_exec(data
.db
, z
, shell_callback
, &data
, &zErrMsg
);
8146 utf8_printf(stderr
,"Error: %s\n", zErrMsg
);
8147 if( bail_on_error
) return rc
!=0 ? rc
: 1;
8149 utf8_printf(stderr
,"Error: unable to process SQL \"%s\"\n", z
);
8150 if( bail_on_error
) return rc
;
8154 utf8_printf(stderr
,"%s: Error: unknown option: %s\n", Argv0
, z
);
8155 raw_printf(stderr
,"Use -help for a list of options.\n");
8158 data
.cMode
= data
.mode
;
8162 /* Run all arguments that do not begin with '-' as if they were separate
8163 ** command-line inputs, except for the argToSkip argument which contains
8164 ** the database filename.
8166 for(i
=0; i
<nCmd
; i
++){
8167 if( azCmd
[i
][0]=='.' ){
8168 rc
= do_meta_command(azCmd
[i
], &data
);
8169 if( rc
) return rc
==2 ? 0 : rc
;
8172 rc
= shell_exec(data
.db
, azCmd
[i
], shell_callback
, &data
, &zErrMsg
);
8174 utf8_printf(stderr
,"Error: %s\n", zErrMsg
);
8175 return rc
!=0 ? rc
: 1;
8177 utf8_printf(stderr
,"Error: unable to process SQL: %s\n", azCmd
[i
]);
8184 /* Run commands received from standard input
8186 if( stdin_is_interactive
){
8191 "SQLite version %s %.19s\n" /*extra-version-info*/
8192 "Enter \".help\" for usage hints.\n",
8193 sqlite3_libversion(), sqlite3_sourceid()
8195 if( warnInmemoryDb
){
8196 printf("Connected to a ");
8197 printBold("transient in-memory database");
8198 printf(".\nUse \".open FILENAME\" to reopen on a "
8199 "persistent database.\n");
8201 zHome
= find_home_dir(0);
8203 nHistory
= strlen30(zHome
) + 20;
8204 if( (zHistory
= malloc(nHistory
))!=0 ){
8205 sqlite3_snprintf(nHistory
, zHistory
,"%s/.sqlite_history", zHome
);
8208 if( zHistory
){ shell_read_history(zHistory
); }
8209 #if HAVE_READLINE || HAVE_EDITLINE
8210 rl_attempted_completion_function
= readline_completion
;
8211 #elif HAVE_LINENOISE
8212 linenoiseSetCompletionCallback(linenoise_completion
);
8214 rc
= process_input(&data
, 0);
8216 shell_stifle_history(2000);
8217 shell_write_history(zHistory
);
8221 rc
= process_input(&data
, stdin
);
8224 set_table_name(&data
, 0);
8226 session_close_all(&data
);
8227 sqlite3_close(data
.db
);
8229 sqlite3_free(data
.zFreeOnClose
);
8231 #if !SQLITE_SHELL_IS_UTF8
8232 for(i
=0; i
<argc
; i
++) sqlite3_free(argv
[i
]);