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(__MINGW_H)
82 # if defined(__MINGW_H)
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 ** If the following flag is set, then command execution stops
357 ** at an error if we are not interactive.
359 static int bail_on_error
= 0;
362 ** Threat stdin as an interactive input if the following variable
363 ** is true. Otherwise, assume stdin is connected to a file or pipe.
365 static int stdin_is_interactive
= 1;
368 ** On Windows systems we have to know if standard output is a console
369 ** in order to translate UTF-8 into MBCS. The following variable is
370 ** true if translation is required.
372 static int stdout_is_console
= 1;
375 ** The following is the open SQLite database. We make a pointer
376 ** to this database a static variable so that it can be accessed
377 ** by the SIGINT handler to interrupt database processing.
379 static sqlite3
*globalDb
= 0;
382 ** True if an interrupt (Control-C) has been received.
384 static volatile int seenInterrupt
= 0;
387 ** This is the name of our program. It is set in main(), used
388 ** in a number of other places, mostly for error messages.
393 ** Prompt strings. Initialized in main. Settable with
394 ** .prompt main continue
396 static char mainPrompt
[20]; /* First line prompt. default: "sqlite> "*/
397 static char continuePrompt
[20]; /* Continuation prompt. default: " ...> " */
400 ** Render output like fprintf(). Except, if the output is going to the
401 ** console and if this is running on a Windows machine, translate the
402 ** output from UTF-8 into MBCS.
404 #if defined(_WIN32) || defined(WIN32)
405 void utf8_printf(FILE *out
, const char *zFormat
, ...){
407 va_start(ap
, zFormat
);
408 if( stdout_is_console
&& (out
==stdout
|| out
==stderr
) ){
409 char *z1
= sqlite3_vmprintf(zFormat
, ap
);
410 char *z2
= sqlite3_win32_utf8_to_mbcs_v2(z1
, 0);
415 vfprintf(out
, zFormat
, ap
);
419 #elif !defined(utf8_printf)
420 # define utf8_printf fprintf
424 ** Render output like fprintf(). This should not be used on anything that
425 ** includes string formatting (e.g. "%s").
427 #if !defined(raw_printf)
428 # define raw_printf fprintf
432 ** Write I/O traces to the following stream.
434 #ifdef SQLITE_ENABLE_IOTRACE
435 static FILE *iotrace
= 0;
439 ** This routine works like printf in that its first argument is a
440 ** format string and subsequent arguments are values to be substituted
441 ** in place of % fields. The result of formatting this string
442 ** is written to iotrace.
444 #ifdef SQLITE_ENABLE_IOTRACE
445 static void SQLITE_CDECL
iotracePrintf(const char *zFormat
, ...){
448 if( iotrace
==0 ) return;
449 va_start(ap
, zFormat
);
450 z
= sqlite3_vmprintf(zFormat
, ap
);
452 utf8_printf(iotrace
, "%s", z
);
458 ** Output string zUtf to stream pOut as w characters. If w is negative,
459 ** then right-justify the text. W is the width in UTF-8 characters, not
460 ** in bytes. This is different from the %*.*s specification in printf
461 ** since with %*.*s the width is measured in bytes, not characters.
463 static void utf8_width_print(FILE *pOut
, int w
, const char *zUtf
){
466 int aw
= w
<0 ? -w
: w
;
468 if( aw
>(int)sizeof(zBuf
)/3 ) aw
= (int)sizeof(zBuf
)/3;
469 for(i
=n
=0; zUtf
[i
]; i
++){
470 if( (zUtf
[i
]&0xc0)!=0x80 ){
473 do{ i
++; }while( (zUtf
[i
]&0xc0)==0x80 );
479 utf8_printf(pOut
, "%.*s", i
, zUtf
);
481 utf8_printf(pOut
, "%*s%s", aw
-n
, "", zUtf
);
483 utf8_printf(pOut
, "%s%*s", zUtf
, aw
-n
, "");
489 ** Determines if a string is a number of not.
491 static int isNumber(const char *z
, int *realnum
){
492 if( *z
=='-' || *z
=='+' ) z
++;
497 if( realnum
) *realnum
= 0;
498 while( IsDigit(*z
) ){ z
++; }
501 if( !IsDigit(*z
) ) return 0;
502 while( IsDigit(*z
) ){ z
++; }
503 if( realnum
) *realnum
= 1;
505 if( *z
=='e' || *z
=='E' ){
507 if( *z
=='+' || *z
=='-' ) z
++;
508 if( !IsDigit(*z
) ) return 0;
509 while( IsDigit(*z
) ){ z
++; }
510 if( realnum
) *realnum
= 1;
516 ** Compute a string length that is limited to what can be stored in
517 ** lower 30 bits of a 32-bit signed integer.
519 static int strlen30(const char *z
){
521 while( *z2
){ z2
++; }
522 return 0x3fffffff & (int)(z2
- z
);
526 ** Return the length of a string in characters. Multibyte UTF8 characters
527 ** count as a single character.
529 static int strlenChar(const char *z
){
532 if( (0xc0&*(z
++))!=0x80 ) n
++;
538 ** This routine reads a line of text from FILE in, stores
539 ** the text in memory obtained from malloc() and returns a pointer
540 ** to the text. NULL is returned at end of file, or if malloc()
543 ** If zLine is not NULL then it is a malloced buffer returned from
544 ** a previous call to this routine that may be reused.
546 static char *local_getline(char *zLine
, FILE *in
){
547 int nLine
= zLine
==0 ? 0 : 100;
552 nLine
= nLine
*2 + 100;
553 zLine
= realloc(zLine
, nLine
);
554 if( zLine
==0 ) return 0;
556 if( fgets(&zLine
[n
], nLine
- n
, in
)==0 ){
564 while( zLine
[n
] ) n
++;
565 if( n
>0 && zLine
[n
-1]=='\n' ){
567 if( n
>0 && zLine
[n
-1]=='\r' ) n
--;
572 #if defined(_WIN32) || defined(WIN32)
573 /* For interactive input on Windows systems, translate the
574 ** multi-byte characterset characters into UTF-8. */
575 if( stdin_is_interactive
&& in
==stdin
){
576 char *zTrans
= sqlite3_win32_mbcs_to_utf8_v2(zLine
, 0);
578 int nTrans
= strlen30(zTrans
)+1;
580 zLine
= realloc(zLine
, nTrans
);
582 sqlite3_free(zTrans
);
586 memcpy(zLine
, zTrans
, nTrans
);
587 sqlite3_free(zTrans
);
590 #endif /* defined(_WIN32) || defined(WIN32) */
595 ** Retrieve a single line of input text.
597 ** If in==0 then read from standard input and prompt before each line.
598 ** If isContinuation is true, then a continuation prompt is appropriate.
599 ** If isContinuation is zero, then the main prompt should be used.
601 ** If zPrior is not NULL then it is a buffer from a prior call to this
602 ** routine that can be reused.
604 ** The result is stored in space obtained from malloc() and must either
605 ** be freed by the caller or else passed back into this routine via the
606 ** zPrior argument for reuse.
608 static char *one_input_line(FILE *in
, char *zPrior
, int isContinuation
){
612 zResult
= local_getline(zPrior
, in
);
614 zPrompt
= isContinuation
? continuePrompt
: mainPrompt
;
615 #if SHELL_USE_LOCAL_GETLINE
616 printf("%s", zPrompt
);
618 zResult
= local_getline(zPrior
, stdin
);
621 zResult
= shell_readline(zPrompt
);
622 if( zResult
&& *zResult
) shell_add_history(zResult
);
628 ** A variable length string to which one can append text.
630 typedef struct ShellText ShellText
;
638 ** Initialize and destroy a ShellText object
640 static void initText(ShellText
*p
){
641 memset(p
, 0, sizeof(*p
));
643 static void freeText(ShellText
*p
){
648 /* zIn is either a pointer to a NULL-terminated string in memory obtained
649 ** from malloc(), or a NULL pointer. The string pointed to by zAppend is
650 ** added to zIn, and the result returned in memory obtained from malloc().
651 ** zIn, if it was not NULL, is freed.
653 ** If the third argument, quote, is not '\0', then it is used as a
654 ** quote character for zAppend.
656 static void appendText(ShellText
*p
, char const *zAppend
, char quote
){
659 int nAppend
= strlen30(zAppend
);
661 len
= nAppend
+p
->n
+1;
664 for(i
=0; i
<nAppend
; i
++){
665 if( zAppend
[i
]==quote
) len
++;
669 if( p
->n
+len
>=p
->nAlloc
){
670 p
->nAlloc
= p
->nAlloc
*2 + len
+ 20;
671 p
->z
= realloc(p
->z
, p
->nAlloc
);
673 memset(p
, 0, sizeof(*p
));
679 char *zCsr
= p
->z
+p
->n
;
681 for(i
=0; i
<nAppend
; i
++){
682 *zCsr
++ = zAppend
[i
];
683 if( zAppend
[i
]==quote
) *zCsr
++ = quote
;
686 p
->n
= (int)(zCsr
- p
->z
);
689 memcpy(p
->z
+p
->n
, zAppend
, nAppend
);
696 ** Attempt to determine if identifier zName needs to be quoted, either
697 ** because it contains non-alphanumeric characters, or because it is an
698 ** SQLite keyword. Be conservative in this estimate: When in doubt assume
699 ** that quoting is required.
701 ** Return '"' if quoting is required. Return 0 if no quoting is required.
703 static char quoteChar(const char *zName
){
704 /* All SQLite keywords, in alphabetical order */
705 static const char *azKeywords
[] = {
706 "ABORT", "ACTION", "ADD", "AFTER", "ALL", "ALTER", "ANALYZE", "AND", "AS",
707 "ASC", "ATTACH", "AUTOINCREMENT", "BEFORE", "BEGIN", "BETWEEN", "BY",
708 "CASCADE", "CASE", "CAST", "CHECK", "COLLATE", "COLUMN", "COMMIT",
709 "CONFLICT", "CONSTRAINT", "CREATE", "CROSS", "CURRENT_DATE",
710 "CURRENT_TIME", "CURRENT_TIMESTAMP", "DATABASE", "DEFAULT", "DEFERRABLE",
711 "DEFERRED", "DELETE", "DESC", "DETACH", "DISTINCT", "DROP", "EACH",
712 "ELSE", "END", "ESCAPE", "EXCEPT", "EXCLUSIVE", "EXISTS", "EXPLAIN",
713 "FAIL", "FOR", "FOREIGN", "FROM", "FULL", "GLOB", "GROUP", "HAVING", "IF",
714 "IGNORE", "IMMEDIATE", "IN", "INDEX", "INDEXED", "INITIALLY", "INNER",
715 "INSERT", "INSTEAD", "INTERSECT", "INTO", "IS", "ISNULL", "JOIN", "KEY",
716 "LEFT", "LIKE", "LIMIT", "MATCH", "NATURAL", "NO", "NOT", "NOTNULL",
717 "NULL", "OF", "OFFSET", "ON", "OR", "ORDER", "OUTER", "PLAN", "PRAGMA",
718 "PRIMARY", "QUERY", "RAISE", "RECURSIVE", "REFERENCES", "REGEXP",
719 "REINDEX", "RELEASE", "RENAME", "REPLACE", "RESTRICT", "RIGHT",
720 "ROLLBACK", "ROW", "SAVEPOINT", "SELECT", "SET", "TABLE", "TEMP",
721 "TEMPORARY", "THEN", "TO", "TRANSACTION", "TRIGGER", "UNION", "UNIQUE",
722 "UPDATE", "USING", "VACUUM", "VALUES", "VIEW", "VIRTUAL", "WHEN", "WHERE",
725 int i
, lwr
, upr
, mid
, c
;
726 if( !isalpha((unsigned char)zName
[0]) && zName
[0]!='_' ) return '"';
727 for(i
=0; zName
[i
]; i
++){
728 if( !isalnum((unsigned char)zName
[i
]) && zName
[i
]!='_' ) return '"';
731 upr
= sizeof(azKeywords
)/sizeof(azKeywords
[0]) - 1;
734 c
= sqlite3_stricmp(azKeywords
[mid
], zName
);
735 if( c
==0 ) return '"';
746 ** Construct a fake object name and column list to describe the structure
747 ** of the view, virtual table, or table valued function zSchema.zName.
749 static char *shellFakeSchema(
750 sqlite3
*db
, /* The database connection containing the vtab */
751 const char *zSchema
, /* Schema of the database holding the vtab */
752 const char *zName
/* The name of the virtual table */
754 sqlite3_stmt
*pStmt
= 0;
761 zSql
= sqlite3_mprintf("PRAGMA \"%w\".table_info=%Q;",
762 zSchema
? zSchema
: "main", zName
);
763 sqlite3_prepare_v2(db
, zSql
, -1, &pStmt
, 0);
767 cQuote
= quoteChar(zSchema
);
768 if( cQuote
&& sqlite3_stricmp(zSchema
,"temp")==0 ) cQuote
= 0;
769 appendText(&s
, zSchema
, cQuote
);
770 appendText(&s
, ".", 0);
772 cQuote
= quoteChar(zName
);
773 appendText(&s
, zName
, cQuote
);
774 while( sqlite3_step(pStmt
)==SQLITE_ROW
){
775 const char *zCol
= (const char*)sqlite3_column_text(pStmt
, 1);
777 appendText(&s
, zDiv
, 0);
779 cQuote
= quoteChar(zCol
);
780 appendText(&s
, zCol
, cQuote
);
782 appendText(&s
, ")", 0);
783 sqlite3_finalize(pStmt
);
792 ** SQL function: shell_module_schema(X)
794 ** Return a fake schema for the table-valued function or eponymous virtual
797 static void shellModuleSchema(
798 sqlite3_context
*pCtx
,
800 sqlite3_value
**apVal
802 const char *zName
= (const char*)sqlite3_value_text(apVal
[0]);
803 char *zFake
= shellFakeSchema(sqlite3_context_db_handle(pCtx
), 0, zName
);
805 sqlite3_result_text(pCtx
, sqlite3_mprintf("/* %z */", zFake
),
811 ** SQL function: shell_add_schema(S,X)
813 ** Add the schema name X to the CREATE statement in S and return the result.
816 ** CREATE TABLE t1(x) -> CREATE TABLE xyz.t1(x);
821 ** CREATE UNIQUE INDEX
824 ** CREATE VIRTUAL TABLE
826 ** This UDF is used by the .schema command to insert the schema name of
827 ** attached databases into the middle of the sqlite_master.sql field.
829 static void shellAddSchemaName(
830 sqlite3_context
*pCtx
,
832 sqlite3_value
**apVal
834 static const char *aPrefix
[] = {
843 const char *zIn
= (const char*)sqlite3_value_text(apVal
[0]);
844 const char *zSchema
= (const char*)sqlite3_value_text(apVal
[1]);
845 const char *zName
= (const char*)sqlite3_value_text(apVal
[2]);
846 sqlite3
*db
= sqlite3_context_db_handle(pCtx
);
847 if( zIn
!=0 && strncmp(zIn
, "CREATE ", 7)==0 ){
848 for(i
=0; i
<(int)(sizeof(aPrefix
)/sizeof(aPrefix
[0])); i
++){
849 int n
= strlen30(aPrefix
[i
]);
850 if( strncmp(zIn
+7, aPrefix
[i
], n
)==0 && zIn
[n
+7]==' ' ){
854 char cQuote
= quoteChar(zSchema
);
855 if( cQuote
&& sqlite3_stricmp(zSchema
,"temp")!=0 ){
856 z
= sqlite3_mprintf("%.*s \"%w\".%s", n
+7, zIn
, zSchema
, zIn
+n
+8);
858 z
= sqlite3_mprintf("%.*s %s.%s", n
+7, zIn
, zSchema
, zIn
+n
+8);
862 && aPrefix
[i
][0]=='V'
863 && (zFake
= shellFakeSchema(db
, zSchema
, zName
))!=0
866 z
= sqlite3_mprintf("%s\n/* %z */", zIn
, zFake
);
868 z
= sqlite3_mprintf("%z\n/* %z */", z
, zFake
);
872 sqlite3_result_text(pCtx
, z
, -1, sqlite3_free
);
878 sqlite3_result_value(pCtx
, apVal
[0]);
882 ** The source code for several run-time loadable extensions is inserted
883 ** below by the ../tool/mkshellc.tcl script. Before processing that included
884 ** code, we need to override some macros to make the included program code
885 ** work here in the middle of this regular program.
887 #define SQLITE_EXTENSION_INIT1
888 #define SQLITE_EXTENSION_INIT2(X) (void)(X)
890 #if defined(_WIN32) && defined(_MSC_VER)
891 INCLUDE test_windirent
.h
892 INCLUDE test_windirent
.c
893 #define dirent DIRENT
895 INCLUDE
../ext
/misc
/shathree
.c
896 INCLUDE
../ext
/misc
/fileio
.c
897 INCLUDE
../ext
/misc
/completion
.c
898 INCLUDE
../ext
/misc
/appendvfs
.c
899 #ifdef SQLITE_HAVE_ZLIB
900 INCLUDE
../ext
/misc
/zipfile
.c
901 INCLUDE
../ext
/misc
/sqlar
.c
903 INCLUDE
../ext
/expert
/sqlite3expert
.h
904 INCLUDE
../ext
/expert
/sqlite3expert
.c
906 #if defined(SQLITE_ENABLE_SESSION)
908 ** State information for a single open session
910 typedef struct OpenSession OpenSession
;
912 char *zName
; /* Symbolic name for this session */
913 int nFilter
; /* Number of xFilter rejection GLOB patterns */
914 char **azFilter
; /* Array of xFilter rejection GLOB patterns */
915 sqlite3_session
*p
; /* The open session */
920 ** Shell output mode information from before ".explain on",
921 ** saved so that it can be restored by ".explain off"
923 typedef struct SavedModeInfo SavedModeInfo
;
924 struct SavedModeInfo
{
925 int valid
; /* Is there legit data in here? */
926 int mode
; /* Mode prior to ".explain on" */
927 int showHeader
; /* The ".header" setting prior to ".explain on" */
928 int colWidth
[100]; /* Column widths prior to ".explain on" */
931 typedef struct ExpertInfo ExpertInfo
;
933 sqlite3expert
*pExpert
;
938 ** State information about the database connection is contained in an
939 ** instance of the following structure.
941 typedef struct ShellState ShellState
;
943 sqlite3
*db
; /* The database */
944 u8 autoExplain
; /* Automatically turn on .explain mode */
945 u8 autoEQP
; /* Run EXPLAIN QUERY PLAN prior to seach SQL stmt */
946 u8 statsOn
; /* True to display memory stats before each finalize */
947 u8 scanstatsOn
; /* True to display scan stats before each finalize */
948 u8 openMode
; /* SHELL_OPEN_NORMAL, _APPENDVFS, or _ZIPFILE */
949 int outCount
; /* Revert to stdout when reaching zero */
950 int cnt
; /* Number of records displayed so far */
951 FILE *out
; /* Write results here */
952 FILE *traceOut
; /* Output for sqlite3_trace() */
953 int nErr
; /* Number of errors seen */
954 int mode
; /* An output mode setting */
955 int cMode
; /* temporary output mode for the current query */
956 int normalMode
; /* Output mode before ".explain on" */
957 int writableSchema
; /* True if PRAGMA writable_schema=ON */
958 int showHeader
; /* True to show column names in List or Column mode */
959 int nCheck
; /* Number of ".check" commands run */
960 unsigned shellFlgs
; /* Various flags */
961 char *zDestTable
; /* Name of destination table when MODE_Insert */
962 char zTestcase
[30]; /* Name of current test case */
963 char colSeparator
[20]; /* Column separator character for several modes */
964 char rowSeparator
[20]; /* Row separator character for MODE_Ascii */
965 int colWidth
[100]; /* Requested width of each column when in column mode*/
966 int actualWidth
[100]; /* Actual width of each column */
967 char nullValue
[20]; /* The text to print when a NULL comes back from
969 char outfile
[FILENAME_MAX
]; /* Filename for *out */
970 const char *zDbFilename
; /* name of the database file */
971 char *zFreeOnClose
; /* Filename to free when closing */
972 const char *zVfs
; /* Name of VFS to use */
973 sqlite3_stmt
*pStmt
; /* Current statement if any. */
974 FILE *pLog
; /* Write log output here */
975 int *aiIndent
; /* Array of indents used in MODE_Explain */
976 int nIndent
; /* Size of array aiIndent[] */
977 int iIndent
; /* Index of current op in aiIndent[] */
978 #if defined(SQLITE_ENABLE_SESSION)
979 int nSession
; /* Number of active sessions */
980 OpenSession aSession
[4]; /* Array of sessions. [0] is in focus. */
982 ExpertInfo expert
; /* Valid if previous command was ".expert OPT..." */
986 /* Allowed values for ShellState.autoEQP
988 #define AUTOEQP_off 0
990 #define AUTOEQP_trigger 2
991 #define AUTOEQP_full 3
993 /* Allowed values for ShellState.openMode
995 #define SHELL_OPEN_UNSPEC 0 /* No open-mode specified */
996 #define SHELL_OPEN_NORMAL 1 /* Normal database file */
997 #define SHELL_OPEN_APPENDVFS 2 /* Use appendvfs */
998 #define SHELL_OPEN_ZIPFILE 3 /* Use the zipfile virtual table */
1001 ** These are the allowed shellFlgs values
1003 #define SHFLG_Pagecache 0x00000001 /* The --pagecache option is used */
1004 #define SHFLG_Lookaside 0x00000002 /* Lookaside memory is used */
1005 #define SHFLG_Backslash 0x00000004 /* The --backslash option is used */
1006 #define SHFLG_PreserveRowid 0x00000008 /* .dump preserves rowid values */
1007 #define SHFLG_Newlines 0x00000010 /* .dump --newline flag */
1008 #define SHFLG_CountChanges 0x00000020 /* .changes setting */
1009 #define SHFLG_Echo 0x00000040 /* .echo or --echo setting */
1012 ** Macros for testing and setting shellFlgs
1014 #define ShellHasFlag(P,X) (((P)->shellFlgs & (X))!=0)
1015 #define ShellSetFlag(P,X) ((P)->shellFlgs|=(X))
1016 #define ShellClearFlag(P,X) ((P)->shellFlgs&=(~(X)))
1019 ** These are the allowed modes.
1021 #define MODE_Line 0 /* One column per line. Blank line between records */
1022 #define MODE_Column 1 /* One record per line in neat columns */
1023 #define MODE_List 2 /* One record per line with a separator */
1024 #define MODE_Semi 3 /* Same as MODE_List but append ";" to each line */
1025 #define MODE_Html 4 /* Generate an XHTML table */
1026 #define MODE_Insert 5 /* Generate SQL "insert" statements */
1027 #define MODE_Quote 6 /* Quote values as for SQL */
1028 #define MODE_Tcl 7 /* Generate ANSI-C or TCL quoted elements */
1029 #define MODE_Csv 8 /* Quote strings, numbers are plain */
1030 #define MODE_Explain 9 /* Like MODE_Column, but do not truncate data */
1031 #define MODE_Ascii 10 /* Use ASCII unit and record separators (0x1F/0x1E) */
1032 #define MODE_Pretty 11 /* Pretty-print schemas */
1034 static const char *modeDescr
[] = {
1050 ** These are the column/row/line separators used by the various
1051 ** import/export modes.
1053 #define SEP_Column "|"
1054 #define SEP_Row "\n"
1055 #define SEP_Tab "\t"
1056 #define SEP_Space " "
1057 #define SEP_Comma ","
1058 #define SEP_CrLf "\r\n"
1059 #define SEP_Unit "\x1F"
1060 #define SEP_Record "\x1E"
1063 ** Number of elements in an array
1065 #define ArraySize(X) (int)(sizeof(X)/sizeof(X[0]))
1068 ** A callback for the sqlite3_log() interface.
1070 static void shellLog(void *pArg
, int iErrCode
, const char *zMsg
){
1071 ShellState
*p
= (ShellState
*)pArg
;
1072 if( p
->pLog
==0 ) return;
1073 utf8_printf(p
->pLog
, "(%d) %s\n", iErrCode
, zMsg
);
1078 ** Output the given string as a hex-encoded blob (eg. X'1234' )
1080 static void output_hex_blob(FILE *out
, const void *pBlob
, int nBlob
){
1082 char *zBlob
= (char *)pBlob
;
1083 raw_printf(out
,"X'");
1084 for(i
=0; i
<nBlob
; i
++){ raw_printf(out
,"%02x",zBlob
[i
]&0xff); }
1085 raw_printf(out
,"'");
1089 ** Find a string that is not found anywhere in z[]. Return a pointer
1092 ** Try to use zA and zB first. If both of those are already found in z[]
1093 ** then make up some string and store it in the buffer zBuf.
1095 static const char *unused_string(
1096 const char *z
, /* Result must not appear anywhere in z */
1097 const char *zA
, const char *zB
, /* Try these first */
1098 char *zBuf
/* Space to store a generated string */
1101 if( strstr(z
, zA
)==0 ) return zA
;
1102 if( strstr(z
, zB
)==0 ) return zB
;
1104 sqlite3_snprintf(20,zBuf
,"(%s%u)", zA
, i
++);
1105 }while( strstr(z
,zBuf
)!=0 );
1110 ** Output the given string as a quoted string using SQL quoting conventions.
1112 ** See also: output_quoted_escaped_string()
1114 static void output_quoted_string(FILE *out
, const char *z
){
1117 setBinaryMode(out
, 1);
1118 for(i
=0; (c
= z
[i
])!=0 && c
!='\''; i
++){}
1120 utf8_printf(out
,"'%s'",z
);
1122 raw_printf(out
, "'");
1124 for(i
=0; (c
= z
[i
])!=0 && c
!='\''; i
++){}
1127 utf8_printf(out
, "%.*s", i
, z
);
1131 raw_printf(out
, "'");
1139 raw_printf(out
, "'");
1141 setTextMode(out
, 1);
1145 ** Output the given string as a quoted string using SQL quoting conventions.
1146 ** Additionallly , escape the "\n" and "\r" characters so that they do not
1147 ** get corrupted by end-of-line translation facilities in some operating
1150 ** This is like output_quoted_string() but with the addition of the \r\n
1151 ** escape mechanism.
1153 static void output_quoted_escaped_string(FILE *out
, const char *z
){
1156 setBinaryMode(out
, 1);
1157 for(i
=0; (c
= z
[i
])!=0 && c
!='\'' && c
!='\n' && c
!='\r'; i
++){}
1159 utf8_printf(out
,"'%s'",z
);
1161 const char *zNL
= 0;
1162 const char *zCR
= 0;
1165 char zBuf1
[20], zBuf2
[20];
1166 for(i
=0; z
[i
]; i
++){
1167 if( z
[i
]=='\n' ) nNL
++;
1168 if( z
[i
]=='\r' ) nCR
++;
1171 raw_printf(out
, "replace(");
1172 zNL
= unused_string(z
, "\\n", "\\012", zBuf1
);
1175 raw_printf(out
, "replace(");
1176 zCR
= unused_string(z
, "\\r", "\\015", zBuf2
);
1178 raw_printf(out
, "'");
1180 for(i
=0; (c
= z
[i
])!=0 && c
!='\n' && c
!='\r' && c
!='\''; i
++){}
1183 utf8_printf(out
, "%.*s", i
, z
);
1187 raw_printf(out
, "'");
1195 raw_printf(out
, "%s", zNL
);
1198 raw_printf(out
, "%s", zCR
);
1200 raw_printf(out
, "'");
1202 raw_printf(out
, ",'%s',char(13))", zCR
);
1205 raw_printf(out
, ",'%s',char(10))", zNL
);
1208 setTextMode(out
, 1);
1212 ** Output the given string as a quoted according to C or TCL quoting rules.
1214 static void output_c_string(FILE *out
, const char *z
){
1217 while( (c
= *(z
++))!=0 ){
1224 }else if( c
=='\t' ){
1227 }else if( c
=='\n' ){
1230 }else if( c
=='\r' ){
1233 }else if( !isprint(c
&0xff) ){
1234 raw_printf(out
, "\\%03o", c
&0xff);
1243 ** Output the given string with characters that are special to
1246 static void output_html_string(FILE *out
, const char *z
){
1258 utf8_printf(out
,"%.*s",i
,z
);
1261 raw_printf(out
,"<");
1262 }else if( z
[i
]=='&' ){
1263 raw_printf(out
,"&");
1264 }else if( z
[i
]=='>' ){
1265 raw_printf(out
,">");
1266 }else if( z
[i
]=='\"' ){
1267 raw_printf(out
,""");
1268 }else if( z
[i
]=='\'' ){
1269 raw_printf(out
,"'");
1278 ** If a field contains any character identified by a 1 in the following
1279 ** array, then the string must be quoted for CSV.
1281 static const char needCsvQuote
[] = {
1282 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1283 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1284 1, 0, 1, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0,
1285 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
1286 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
1287 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
1288 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
1289 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1,
1290 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1291 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1292 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1293 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1294 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1295 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1296 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1297 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1301 ** Output a single term of CSV. Actually, p->colSeparator is used for
1302 ** the separator, which may or may not be a comma. p->nullValue is
1303 ** the null value. Strings are quoted if necessary. The separator
1304 ** is only issued if bSep is true.
1306 static void output_csv(ShellState
*p
, const char *z
, int bSep
){
1309 utf8_printf(out
,"%s",p
->nullValue
);
1312 int nSep
= strlen30(p
->colSeparator
);
1313 for(i
=0; z
[i
]; i
++){
1314 if( needCsvQuote
[((unsigned char*)z
)[i
]]
1315 || (z
[i
]==p
->colSeparator
[0] &&
1316 (nSep
==1 || memcmp(z
, p
->colSeparator
, nSep
)==0)) ){
1322 char *zQuoted
= sqlite3_mprintf("\"%w\"", z
);
1323 utf8_printf(out
, "%s", zQuoted
);
1324 sqlite3_free(zQuoted
);
1326 utf8_printf(out
, "%s", z
);
1330 utf8_printf(p
->out
, "%s", p
->colSeparator
);
1335 ** This routine runs when the user presses Ctrl-C
1337 static void interrupt_handler(int NotUsed
){
1338 UNUSED_PARAMETER(NotUsed
);
1340 if( seenInterrupt
>2 ) exit(1);
1341 if( globalDb
) sqlite3_interrupt(globalDb
);
1344 #if (defined(_WIN32) || defined(WIN32)) && !defined(_WIN32_WCE)
1346 ** This routine runs for console events (e.g. Ctrl-C) on Win32
1348 static BOOL WINAPI
ConsoleCtrlHandler(
1349 DWORD dwCtrlType
/* One of the CTRL_*_EVENT constants */
1351 if( dwCtrlType
==CTRL_C_EVENT
){
1352 interrupt_handler(0);
1359 #ifndef SQLITE_OMIT_AUTHORIZATION
1361 ** When the ".auth ON" is set, the following authorizer callback is
1362 ** invoked. It always returns SQLITE_OK.
1364 static int shellAuth(
1372 ShellState
*p
= (ShellState
*)pClientData
;
1373 static const char *azAction
[] = { 0,
1374 "CREATE_INDEX", "CREATE_TABLE", "CREATE_TEMP_INDEX",
1375 "CREATE_TEMP_TABLE", "CREATE_TEMP_TRIGGER", "CREATE_TEMP_VIEW",
1376 "CREATE_TRIGGER", "CREATE_VIEW", "DELETE",
1377 "DROP_INDEX", "DROP_TABLE", "DROP_TEMP_INDEX",
1378 "DROP_TEMP_TABLE", "DROP_TEMP_TRIGGER", "DROP_TEMP_VIEW",
1379 "DROP_TRIGGER", "DROP_VIEW", "INSERT",
1380 "PRAGMA", "READ", "SELECT",
1381 "TRANSACTION", "UPDATE", "ATTACH",
1382 "DETACH", "ALTER_TABLE", "REINDEX",
1383 "ANALYZE", "CREATE_VTABLE", "DROP_VTABLE",
1384 "FUNCTION", "SAVEPOINT", "RECURSIVE"
1392 utf8_printf(p
->out
, "authorizer: %s", azAction
[op
]);
1394 raw_printf(p
->out
, " ");
1396 output_c_string(p
->out
, az
[i
]);
1398 raw_printf(p
->out
, "NULL");
1401 raw_printf(p
->out
, "\n");
1407 ** Print a schema statement. Part of MODE_Semi and MODE_Pretty output.
1409 ** This routine converts some CREATE TABLE statements for shadow tables
1410 ** in FTS3/4/5 into CREATE TABLE IF NOT EXISTS statements.
1412 static void printSchemaLine(FILE *out
, const char *z
, const char *zTail
){
1413 if( sqlite3_strglob("CREATE TABLE ['\"]*", z
)==0 ){
1414 utf8_printf(out
, "CREATE TABLE IF NOT EXISTS %s%s", z
+13, zTail
);
1416 utf8_printf(out
, "%s%s", z
, zTail
);
1419 static void printSchemaLineN(FILE *out
, char *z
, int n
, const char *zTail
){
1422 printSchemaLine(out
, z
, zTail
);
1427 ** Return true if string z[] has nothing but whitespace and comments to the
1428 ** end of the first line.
1430 static int wsToEol(const char *z
){
1432 for(i
=0; z
[i
]; i
++){
1433 if( z
[i
]=='\n' ) return 1;
1434 if( IsSpace(z
[i
]) ) continue;
1435 if( z
[i
]=='-' && z
[i
+1]=='-' ) return 1;
1443 ** This is the callback routine that the shell
1444 ** invokes for each row of a query result.
1446 static int shell_callback(
1448 int nArg
, /* Number of result columns */
1449 char **azArg
, /* Text of each result column */
1450 char **azCol
, /* Column names */
1451 int *aiType
/* Column types */
1454 ShellState
*p
= (ShellState
*)pArg
;
1456 if( azArg
==0 ) return 0;
1460 if( azArg
==0 ) break;
1461 for(i
=0; i
<nArg
; i
++){
1462 int len
= strlen30(azCol
[i
] ? azCol
[i
] : "");
1463 if( len
>w
) w
= len
;
1465 if( p
->cnt
++>0 ) utf8_printf(p
->out
, "%s", p
->rowSeparator
);
1466 for(i
=0; i
<nArg
; i
++){
1467 utf8_printf(p
->out
,"%*s = %s%s", w
, azCol
[i
],
1468 azArg
[i
] ? azArg
[i
] : p
->nullValue
, p
->rowSeparator
);
1474 static const int aExplainWidths
[] = {4, 13, 4, 4, 4, 13, 2, 13};
1475 const int *colWidth
;
1478 if( p
->cMode
==MODE_Column
){
1479 colWidth
= p
->colWidth
;
1480 showHdr
= p
->showHeader
;
1481 rowSep
= p
->rowSeparator
;
1483 colWidth
= aExplainWidths
;
1488 for(i
=0; i
<nArg
; i
++){
1490 if( i
<ArraySize(p
->colWidth
) ){
1496 w
= strlenChar(azCol
[i
] ? azCol
[i
] : "");
1498 n
= strlenChar(azArg
&& azArg
[i
] ? azArg
[i
] : p
->nullValue
);
1501 if( i
<ArraySize(p
->actualWidth
) ){
1502 p
->actualWidth
[i
] = w
;
1505 utf8_width_print(p
->out
, w
, azCol
[i
]);
1506 utf8_printf(p
->out
, "%s", i
==nArg
-1 ? rowSep
: " ");
1510 for(i
=0; i
<nArg
; i
++){
1512 if( i
<ArraySize(p
->actualWidth
) ){
1513 w
= p
->actualWidth
[i
];
1518 utf8_printf(p
->out
,"%-*.*s%s",w
,w
,
1519 "----------------------------------------------------------"
1520 "----------------------------------------------------------",
1521 i
==nArg
-1 ? rowSep
: " ");
1525 if( azArg
==0 ) break;
1526 for(i
=0; i
<nArg
; i
++){
1528 if( i
<ArraySize(p
->actualWidth
) ){
1529 w
= p
->actualWidth
[i
];
1533 if( p
->cMode
==MODE_Explain
&& azArg
[i
] && strlenChar(azArg
[i
])>w
){
1534 w
= strlenChar(azArg
[i
]);
1536 if( i
==1 && p
->aiIndent
&& p
->pStmt
){
1537 if( p
->iIndent
<p
->nIndent
){
1538 utf8_printf(p
->out
, "%*.s", p
->aiIndent
[p
->iIndent
], "");
1542 utf8_width_print(p
->out
, w
, azArg
[i
] ? azArg
[i
] : p
->nullValue
);
1543 utf8_printf(p
->out
, "%s", i
==nArg
-1 ? rowSep
: " ");
1547 case MODE_Semi
: { /* .schema and .fullschema output */
1548 printSchemaLine(p
->out
, azArg
[0], ";\n");
1551 case MODE_Pretty
: { /* .schema and .fullschema with --indent */
1559 if( azArg
[0]==0 ) break;
1560 if( sqlite3_strlike("CREATE VIEW%", azArg
[0], 0)==0
1561 || sqlite3_strlike("CREATE TRIG%", azArg
[0], 0)==0
1563 utf8_printf(p
->out
, "%s;\n", azArg
[0]);
1566 z
= sqlite3_mprintf("%s", azArg
[0]);
1568 for(i
=0; IsSpace(z
[i
]); i
++){}
1569 for(; (c
= z
[i
])!=0; i
++){
1571 if( z
[j
-1]=='\r' ) z
[j
-1] = '\n';
1572 if( IsSpace(z
[j
-1]) || z
[j
-1]=='(' ) continue;
1573 }else if( (c
=='(' || c
==')') && j
>0 && IsSpace(z
[j
-1]) ){
1578 while( j
>0 && IsSpace(z
[j
-1]) ){ j
--; }
1580 if( strlen30(z
)>=79 ){
1581 for(i
=j
=0; (c
= z
[i
])!=0; i
++){ /* Copy changes from z[i] back to z[j] */
1584 }else if( c
=='"' || c
=='\'' || c
=='`' ){
1588 }else if( c
=='-' && z
[i
+1]=='-' ){
1594 if( nLine
>0 && nParen
==0 && j
>0 ){
1595 printSchemaLineN(p
->out
, z
, j
, "\n");
1600 if( nParen
==1 && cEnd
==0
1601 && (c
=='(' || c
=='\n' || (c
==',' && !wsToEol(z
+i
+1)))
1604 printSchemaLineN(p
->out
, z
, j
, "\n ");
1607 while( IsSpace(z
[i
+1]) ){ i
++; }
1612 printSchemaLine(p
->out
, z
, ";\n");
1617 if( p
->cnt
++==0 && p
->showHeader
){
1618 for(i
=0; i
<nArg
; i
++){
1619 utf8_printf(p
->out
,"%s%s",azCol
[i
],
1620 i
==nArg
-1 ? p
->rowSeparator
: p
->colSeparator
);
1623 if( azArg
==0 ) break;
1624 for(i
=0; i
<nArg
; i
++){
1626 if( z
==0 ) z
= p
->nullValue
;
1627 utf8_printf(p
->out
, "%s", z
);
1629 utf8_printf(p
->out
, "%s", p
->colSeparator
);
1631 utf8_printf(p
->out
, "%s", p
->rowSeparator
);
1637 if( p
->cnt
++==0 && p
->showHeader
){
1638 raw_printf(p
->out
,"<TR>");
1639 for(i
=0; i
<nArg
; i
++){
1640 raw_printf(p
->out
,"<TH>");
1641 output_html_string(p
->out
, azCol
[i
]);
1642 raw_printf(p
->out
,"</TH>\n");
1644 raw_printf(p
->out
,"</TR>\n");
1646 if( azArg
==0 ) break;
1647 raw_printf(p
->out
,"<TR>");
1648 for(i
=0; i
<nArg
; i
++){
1649 raw_printf(p
->out
,"<TD>");
1650 output_html_string(p
->out
, azArg
[i
] ? azArg
[i
] : p
->nullValue
);
1651 raw_printf(p
->out
,"</TD>\n");
1653 raw_printf(p
->out
,"</TR>\n");
1657 if( p
->cnt
++==0 && p
->showHeader
){
1658 for(i
=0; i
<nArg
; i
++){
1659 output_c_string(p
->out
,azCol
[i
] ? azCol
[i
] : "");
1660 if(i
<nArg
-1) utf8_printf(p
->out
, "%s", p
->colSeparator
);
1662 utf8_printf(p
->out
, "%s", p
->rowSeparator
);
1664 if( azArg
==0 ) break;
1665 for(i
=0; i
<nArg
; i
++){
1666 output_c_string(p
->out
, azArg
[i
] ? azArg
[i
] : p
->nullValue
);
1667 if(i
<nArg
-1) utf8_printf(p
->out
, "%s", p
->colSeparator
);
1669 utf8_printf(p
->out
, "%s", p
->rowSeparator
);
1673 setBinaryMode(p
->out
, 1);
1674 if( p
->cnt
++==0 && p
->showHeader
){
1675 for(i
=0; i
<nArg
; i
++){
1676 output_csv(p
, azCol
[i
] ? azCol
[i
] : "", i
<nArg
-1);
1678 utf8_printf(p
->out
, "%s", p
->rowSeparator
);
1681 for(i
=0; i
<nArg
; i
++){
1682 output_csv(p
, azArg
[i
], i
<nArg
-1);
1684 utf8_printf(p
->out
, "%s", p
->rowSeparator
);
1686 setTextMode(p
->out
, 1);
1690 if( azArg
==0 ) break;
1691 utf8_printf(p
->out
,"INSERT INTO %s",p
->zDestTable
);
1692 if( p
->showHeader
){
1693 raw_printf(p
->out
,"(");
1694 for(i
=0; i
<nArg
; i
++){
1695 if( i
>0 ) raw_printf(p
->out
, ",");
1696 if( quoteChar(azCol
[i
]) ){
1697 char *z
= sqlite3_mprintf("\"%w\"", azCol
[i
]);
1698 utf8_printf(p
->out
, "%s", z
);
1701 raw_printf(p
->out
, "%s", azCol
[i
]);
1704 raw_printf(p
->out
,")");
1707 for(i
=0; i
<nArg
; i
++){
1708 raw_printf(p
->out
, i
>0 ? "," : " VALUES(");
1709 if( (azArg
[i
]==0) || (aiType
&& aiType
[i
]==SQLITE_NULL
) ){
1710 utf8_printf(p
->out
,"NULL");
1711 }else if( aiType
&& aiType
[i
]==SQLITE_TEXT
){
1712 if( ShellHasFlag(p
, SHFLG_Newlines
) ){
1713 output_quoted_string(p
->out
, azArg
[i
]);
1715 output_quoted_escaped_string(p
->out
, azArg
[i
]);
1717 }else if( aiType
&& aiType
[i
]==SQLITE_INTEGER
){
1718 utf8_printf(p
->out
,"%s", azArg
[i
]);
1719 }else if( aiType
&& aiType
[i
]==SQLITE_FLOAT
){
1721 double r
= sqlite3_column_double(p
->pStmt
, i
);
1722 sqlite3_snprintf(50,z
,"%!.20g", r
);
1723 raw_printf(p
->out
, "%s", z
);
1724 }else if( aiType
&& aiType
[i
]==SQLITE_BLOB
&& p
->pStmt
){
1725 const void *pBlob
= sqlite3_column_blob(p
->pStmt
, i
);
1726 int nBlob
= sqlite3_column_bytes(p
->pStmt
, i
);
1727 output_hex_blob(p
->out
, pBlob
, nBlob
);
1728 }else if( isNumber(azArg
[i
], 0) ){
1729 utf8_printf(p
->out
,"%s", azArg
[i
]);
1730 }else if( ShellHasFlag(p
, SHFLG_Newlines
) ){
1731 output_quoted_string(p
->out
, azArg
[i
]);
1733 output_quoted_escaped_string(p
->out
, azArg
[i
]);
1736 raw_printf(p
->out
,");\n");
1740 if( azArg
==0 ) break;
1741 if( p
->cnt
==0 && p
->showHeader
){
1742 for(i
=0; i
<nArg
; i
++){
1743 if( i
>0 ) raw_printf(p
->out
, ",");
1744 output_quoted_string(p
->out
, azCol
[i
]);
1746 raw_printf(p
->out
,"\n");
1749 for(i
=0; i
<nArg
; i
++){
1750 if( i
>0 ) raw_printf(p
->out
, ",");
1751 if( (azArg
[i
]==0) || (aiType
&& aiType
[i
]==SQLITE_NULL
) ){
1752 utf8_printf(p
->out
,"NULL");
1753 }else if( aiType
&& aiType
[i
]==SQLITE_TEXT
){
1754 output_quoted_string(p
->out
, azArg
[i
]);
1755 }else if( aiType
&& aiType
[i
]==SQLITE_INTEGER
){
1756 utf8_printf(p
->out
,"%s", azArg
[i
]);
1757 }else if( aiType
&& aiType
[i
]==SQLITE_FLOAT
){
1759 double r
= sqlite3_column_double(p
->pStmt
, i
);
1760 sqlite3_snprintf(50,z
,"%!.20g", r
);
1761 raw_printf(p
->out
, "%s", z
);
1762 }else if( aiType
&& aiType
[i
]==SQLITE_BLOB
&& p
->pStmt
){
1763 const void *pBlob
= sqlite3_column_blob(p
->pStmt
, i
);
1764 int nBlob
= sqlite3_column_bytes(p
->pStmt
, i
);
1765 output_hex_blob(p
->out
, pBlob
, nBlob
);
1766 }else if( isNumber(azArg
[i
], 0) ){
1767 utf8_printf(p
->out
,"%s", azArg
[i
]);
1769 output_quoted_string(p
->out
, azArg
[i
]);
1772 raw_printf(p
->out
,"\n");
1776 if( p
->cnt
++==0 && p
->showHeader
){
1777 for(i
=0; i
<nArg
; i
++){
1778 if( i
>0 ) utf8_printf(p
->out
, "%s", p
->colSeparator
);
1779 utf8_printf(p
->out
,"%s",azCol
[i
] ? azCol
[i
] : "");
1781 utf8_printf(p
->out
, "%s", p
->rowSeparator
);
1783 if( azArg
==0 ) break;
1784 for(i
=0; i
<nArg
; i
++){
1785 if( i
>0 ) utf8_printf(p
->out
, "%s", p
->colSeparator
);
1786 utf8_printf(p
->out
,"%s",azArg
[i
] ? azArg
[i
] : p
->nullValue
);
1788 utf8_printf(p
->out
, "%s", p
->rowSeparator
);
1796 ** This is the callback routine that the SQLite library
1797 ** invokes for each row of a query result.
1799 static int callback(void *pArg
, int nArg
, char **azArg
, char **azCol
){
1800 /* since we don't have type info, call the shell_callback with a NULL value */
1801 return shell_callback(pArg
, nArg
, azArg
, azCol
, NULL
);
1805 ** This is the callback routine from sqlite3_exec() that appends all
1806 ** output onto the end of a ShellText object.
1808 static int captureOutputCallback(void *pArg
, int nArg
, char **azArg
, char **az
){
1809 ShellText
*p
= (ShellText
*)pArg
;
1811 UNUSED_PARAMETER(az
);
1812 if( azArg
==0 ) return 0;
1813 if( p
->n
) appendText(p
, "|", 0);
1814 for(i
=0; i
<nArg
; i
++){
1815 if( i
) appendText(p
, ",", 0);
1816 if( azArg
[i
] ) appendText(p
, azArg
[i
], 0);
1822 ** Generate an appropriate SELFTEST table in the main database.
1824 static void createSelftestTable(ShellState
*p
){
1827 "SAVEPOINT selftest_init;\n"
1828 "CREATE TABLE IF NOT EXISTS selftest(\n"
1829 " tno INTEGER PRIMARY KEY,\n" /* Test number */
1830 " op TEXT,\n" /* Operator: memo run */
1831 " cmd TEXT,\n" /* Command text */
1832 " ans TEXT\n" /* Desired answer */
1834 "CREATE TEMP TABLE [_shell$self](op,cmd,ans);\n"
1835 "INSERT INTO [_shell$self](rowid,op,cmd)\n"
1836 " VALUES(coalesce((SELECT (max(tno)+100)/10 FROM selftest),10),\n"
1837 " 'memo','Tests generated by --init');\n"
1838 "INSERT INTO [_shell$self]\n"
1840 " 'SELECT hex(sha3_query(''SELECT type,name,tbl_name,sql "
1841 "FROM sqlite_master ORDER BY 2'',224))',\n"
1842 " hex(sha3_query('SELECT type,name,tbl_name,sql "
1843 "FROM sqlite_master ORDER BY 2',224));\n"
1844 "INSERT INTO [_shell$self]\n"
1846 " 'SELECT hex(sha3_query(''SELECT * FROM \"' ||"
1847 " printf('%w',name) || '\" NOT INDEXED'',224))',\n"
1848 " hex(sha3_query(printf('SELECT * FROM \"%w\" NOT INDEXED',name),224))\n"
1850 " SELECT name FROM sqlite_master\n"
1851 " WHERE type='table'\n"
1852 " AND name<>'selftest'\n"
1853 " AND coalesce(rootpage,0)>0\n"
1856 "INSERT INTO [_shell$self]\n"
1857 " VALUES('run','PRAGMA integrity_check','ok');\n"
1858 "INSERT INTO selftest(tno,op,cmd,ans)"
1859 " SELECT rowid*10,op,cmd,ans FROM [_shell$self];\n"
1860 "DROP TABLE [_shell$self];"
1863 utf8_printf(stderr
, "SELFTEST initialization failure: %s\n", zErrMsg
);
1864 sqlite3_free(zErrMsg
);
1866 sqlite3_exec(p
->db
, "RELEASE selftest_init",0,0,0);
1871 ** Set the destination table field of the ShellState structure to
1872 ** the name of the table given. Escape any quote characters in the
1875 static void set_table_name(ShellState
*p
, const char *zName
){
1880 if( p
->zDestTable
){
1881 free(p
->zDestTable
);
1884 if( zName
==0 ) return;
1885 cQuote
= quoteChar(zName
);
1886 n
= strlen30(zName
);
1887 if( cQuote
) n
+= n
+2;
1888 z
= p
->zDestTable
= malloc( n
+1 );
1890 raw_printf(stderr
,"Error: out of memory\n");
1894 if( cQuote
) z
[n
++] = cQuote
;
1895 for(i
=0; zName
[i
]; i
++){
1897 if( zName
[i
]==cQuote
) z
[n
++] = cQuote
;
1899 if( cQuote
) z
[n
++] = cQuote
;
1905 ** Execute a query statement that will generate SQL output. Print
1906 ** the result columns, comma-separated, on a line and then add a
1907 ** semicolon terminator to the end of that line.
1909 ** If the number of columns is 1 and that column contains text "--"
1910 ** then write the semicolon on a separate line. That way, if a
1911 ** "--" comment occurs at the end of the statement, the comment
1912 ** won't consume the semicolon terminator.
1914 static int run_table_dump_query(
1915 ShellState
*p
, /* Query context */
1916 const char *zSelect
, /* SELECT statement to extract content */
1917 const char *zFirstRow
/* Print before first row, if not NULL */
1919 sqlite3_stmt
*pSelect
;
1924 rc
= sqlite3_prepare_v2(p
->db
, zSelect
, -1, &pSelect
, 0);
1925 if( rc
!=SQLITE_OK
|| !pSelect
){
1926 utf8_printf(p
->out
, "/**** ERROR: (%d) %s *****/\n", rc
,
1927 sqlite3_errmsg(p
->db
));
1928 if( (rc
&0xff)!=SQLITE_CORRUPT
) p
->nErr
++;
1931 rc
= sqlite3_step(pSelect
);
1932 nResult
= sqlite3_column_count(pSelect
);
1933 while( rc
==SQLITE_ROW
){
1935 utf8_printf(p
->out
, "%s", zFirstRow
);
1938 z
= (const char*)sqlite3_column_text(pSelect
, 0);
1939 utf8_printf(p
->out
, "%s", z
);
1940 for(i
=1; i
<nResult
; i
++){
1941 utf8_printf(p
->out
, ",%s", sqlite3_column_text(pSelect
, i
));
1944 while( z
[0] && (z
[0]!='-' || z
[1]!='-') ) z
++;
1946 raw_printf(p
->out
, "\n;\n");
1948 raw_printf(p
->out
, ";\n");
1950 rc
= sqlite3_step(pSelect
);
1952 rc
= sqlite3_finalize(pSelect
);
1953 if( rc
!=SQLITE_OK
){
1954 utf8_printf(p
->out
, "/**** ERROR: (%d) %s *****/\n", rc
,
1955 sqlite3_errmsg(p
->db
));
1956 if( (rc
&0xff)!=SQLITE_CORRUPT
) p
->nErr
++;
1962 ** Allocate space and save off current error string.
1964 static char *save_err_msg(
1965 sqlite3
*db
/* Database to query */
1967 int nErrMsg
= 1+strlen30(sqlite3_errmsg(db
));
1968 char *zErrMsg
= sqlite3_malloc64(nErrMsg
);
1970 memcpy(zErrMsg
, sqlite3_errmsg(db
), nErrMsg
);
1977 ** Attempt to display I/O stats on Linux using /proc/PID/io
1979 static void displayLinuxIoStats(FILE *out
){
1982 sqlite3_snprintf(sizeof(z
), z
, "/proc/%d/io", getpid());
1983 in
= fopen(z
, "rb");
1985 while( fgets(z
, sizeof(z
), in
)!=0 ){
1986 static const struct {
1987 const char *zPattern
;
1990 { "rchar: ", "Bytes received by read():" },
1991 { "wchar: ", "Bytes sent to write():" },
1992 { "syscr: ", "Read() system calls:" },
1993 { "syscw: ", "Write() system calls:" },
1994 { "read_bytes: ", "Bytes read from storage:" },
1995 { "write_bytes: ", "Bytes written to storage:" },
1996 { "cancelled_write_bytes: ", "Cancelled write bytes:" },
1999 for(i
=0; i
<ArraySize(aTrans
); i
++){
2000 int n
= strlen30(aTrans
[i
].zPattern
);
2001 if( strncmp(aTrans
[i
].zPattern
, z
, n
)==0 ){
2002 utf8_printf(out
, "%-36s %s", aTrans
[i
].zDesc
, &z
[n
]);
2012 ** Display a single line of status using 64-bit values.
2014 static void displayStatLine(
2015 ShellState
*p
, /* The shell context */
2016 char *zLabel
, /* Label for this one line */
2017 char *zFormat
, /* Format for the result */
2018 int iStatusCtrl
, /* Which status to display */
2019 int bReset
/* True to reset the stats */
2021 sqlite3_int64 iCur
= -1;
2022 sqlite3_int64 iHiwtr
= -1;
2025 sqlite3_status64(iStatusCtrl
, &iCur
, &iHiwtr
, bReset
);
2026 for(i
=0, nPercent
=0; zFormat
[i
]; i
++){
2027 if( zFormat
[i
]=='%' ) nPercent
++;
2030 sqlite3_snprintf(sizeof(zLine
), zLine
, zFormat
, iCur
, iHiwtr
);
2032 sqlite3_snprintf(sizeof(zLine
), zLine
, zFormat
, iHiwtr
);
2034 raw_printf(p
->out
, "%-36s %s\n", zLabel
, zLine
);
2038 ** Display memory stats.
2040 static int display_stats(
2041 sqlite3
*db
, /* Database to query */
2042 ShellState
*pArg
, /* Pointer to ShellState */
2043 int bReset
/* True to reset the stats */
2048 if( pArg
&& pArg
->out
){
2049 displayStatLine(pArg
, "Memory Used:",
2050 "%lld (max %lld) bytes", SQLITE_STATUS_MEMORY_USED
, bReset
);
2051 displayStatLine(pArg
, "Number of Outstanding Allocations:",
2052 "%lld (max %lld)", SQLITE_STATUS_MALLOC_COUNT
, bReset
);
2053 if( pArg
->shellFlgs
& SHFLG_Pagecache
){
2054 displayStatLine(pArg
, "Number of Pcache Pages Used:",
2055 "%lld (max %lld) pages", SQLITE_STATUS_PAGECACHE_USED
, bReset
);
2057 displayStatLine(pArg
, "Number of Pcache Overflow Bytes:",
2058 "%lld (max %lld) bytes", SQLITE_STATUS_PAGECACHE_OVERFLOW
, bReset
);
2059 displayStatLine(pArg
, "Largest Allocation:",
2060 "%lld bytes", SQLITE_STATUS_MALLOC_SIZE
, bReset
);
2061 displayStatLine(pArg
, "Largest Pcache Allocation:",
2062 "%lld bytes", SQLITE_STATUS_PAGECACHE_SIZE
, bReset
);
2063 #ifdef YYTRACKMAXSTACKDEPTH
2064 displayStatLine(pArg
, "Deepest Parser Stack:",
2065 "%lld (max %lld)", SQLITE_STATUS_PARSER_STACK
, bReset
);
2069 if( pArg
&& pArg
->out
&& db
){
2070 if( pArg
->shellFlgs
& SHFLG_Lookaside
){
2072 sqlite3_db_status(db
, SQLITE_DBSTATUS_LOOKASIDE_USED
,
2073 &iCur
, &iHiwtr
, bReset
);
2074 raw_printf(pArg
->out
,
2075 "Lookaside Slots Used: %d (max %d)\n",
2077 sqlite3_db_status(db
, SQLITE_DBSTATUS_LOOKASIDE_HIT
,
2078 &iCur
, &iHiwtr
, bReset
);
2079 raw_printf(pArg
->out
, "Successful lookaside attempts: %d\n",
2081 sqlite3_db_status(db
, SQLITE_DBSTATUS_LOOKASIDE_MISS_SIZE
,
2082 &iCur
, &iHiwtr
, bReset
);
2083 raw_printf(pArg
->out
, "Lookaside failures due to size: %d\n",
2085 sqlite3_db_status(db
, SQLITE_DBSTATUS_LOOKASIDE_MISS_FULL
,
2086 &iCur
, &iHiwtr
, bReset
);
2087 raw_printf(pArg
->out
, "Lookaside failures due to OOM: %d\n",
2091 sqlite3_db_status(db
, SQLITE_DBSTATUS_CACHE_USED
, &iCur
, &iHiwtr
, bReset
);
2092 raw_printf(pArg
->out
, "Pager Heap Usage: %d bytes\n",
2095 sqlite3_db_status(db
, SQLITE_DBSTATUS_CACHE_HIT
, &iCur
, &iHiwtr
, 1);
2096 raw_printf(pArg
->out
, "Page cache hits: %d\n", iCur
);
2098 sqlite3_db_status(db
, SQLITE_DBSTATUS_CACHE_MISS
, &iCur
, &iHiwtr
, 1);
2099 raw_printf(pArg
->out
, "Page cache misses: %d\n", iCur
);
2101 sqlite3_db_status(db
, SQLITE_DBSTATUS_CACHE_WRITE
, &iCur
, &iHiwtr
, 1);
2102 raw_printf(pArg
->out
, "Page cache writes: %d\n", iCur
);
2104 sqlite3_db_status(db
, SQLITE_DBSTATUS_SCHEMA_USED
, &iCur
, &iHiwtr
, bReset
);
2105 raw_printf(pArg
->out
, "Schema Heap Usage: %d bytes\n",
2108 sqlite3_db_status(db
, SQLITE_DBSTATUS_STMT_USED
, &iCur
, &iHiwtr
, bReset
);
2109 raw_printf(pArg
->out
, "Statement Heap/Lookaside Usage: %d bytes\n",
2113 if( pArg
&& pArg
->out
&& db
&& pArg
->pStmt
){
2114 iCur
= sqlite3_stmt_status(pArg
->pStmt
, SQLITE_STMTSTATUS_FULLSCAN_STEP
,
2116 raw_printf(pArg
->out
, "Fullscan Steps: %d\n", iCur
);
2117 iCur
= sqlite3_stmt_status(pArg
->pStmt
, SQLITE_STMTSTATUS_SORT
, bReset
);
2118 raw_printf(pArg
->out
, "Sort Operations: %d\n", iCur
);
2119 iCur
= sqlite3_stmt_status(pArg
->pStmt
, SQLITE_STMTSTATUS_AUTOINDEX
,bReset
);
2120 raw_printf(pArg
->out
, "Autoindex Inserts: %d\n", iCur
);
2121 iCur
= sqlite3_stmt_status(pArg
->pStmt
, SQLITE_STMTSTATUS_VM_STEP
, bReset
);
2122 raw_printf(pArg
->out
, "Virtual Machine Steps: %d\n", iCur
);
2126 displayLinuxIoStats(pArg
->out
);
2129 /* Do not remove this machine readable comment: extra-stats-output-here */
2135 ** Display scan stats.
2137 static void display_scanstats(
2138 sqlite3
*db
, /* Database to query */
2139 ShellState
*pArg
/* Pointer to ShellState */
2141 #ifndef SQLITE_ENABLE_STMT_SCANSTATUS
2142 UNUSED_PARAMETER(db
);
2143 UNUSED_PARAMETER(pArg
);
2146 raw_printf(pArg
->out
, "-------- scanstats --------\n");
2148 for(k
=0; k
<=mx
; k
++){
2149 double rEstLoop
= 1.0;
2151 sqlite3_stmt
*p
= pArg
->pStmt
;
2152 sqlite3_int64 nLoop
, nVisit
;
2155 const char *zExplain
;
2156 if( sqlite3_stmt_scanstatus(p
, i
, SQLITE_SCANSTAT_NLOOP
, (void*)&nLoop
) ){
2159 sqlite3_stmt_scanstatus(p
, i
, SQLITE_SCANSTAT_SELECTID
, (void*)&iSid
);
2160 if( iSid
>mx
) mx
= iSid
;
2161 if( iSid
!=k
) continue;
2163 rEstLoop
= (double)nLoop
;
2164 if( k
>0 ) raw_printf(pArg
->out
, "-------- subquery %d -------\n", k
);
2167 sqlite3_stmt_scanstatus(p
, i
, SQLITE_SCANSTAT_NVISIT
, (void*)&nVisit
);
2168 sqlite3_stmt_scanstatus(p
, i
, SQLITE_SCANSTAT_EST
, (void*)&rEst
);
2169 sqlite3_stmt_scanstatus(p
, i
, SQLITE_SCANSTAT_EXPLAIN
, (void*)&zExplain
);
2170 utf8_printf(pArg
->out
, "Loop %2d: %s\n", n
, zExplain
);
2172 raw_printf(pArg
->out
,
2173 " nLoop=%-8lld nRow=%-8lld estRow=%-8lld estRow/Loop=%-8g\n",
2174 nLoop
, nVisit
, (sqlite3_int64
)(rEstLoop
+0.5), rEst
2178 raw_printf(pArg
->out
, "---------------------------\n");
2183 ** Parameter azArray points to a zero-terminated array of strings. zStr
2184 ** points to a single nul-terminated string. Return non-zero if zStr
2185 ** is equal, according to strcmp(), to any of the strings in the array.
2186 ** Otherwise, return zero.
2188 static int str_in_array(const char *zStr
, const char **azArray
){
2190 for(i
=0; azArray
[i
]; i
++){
2191 if( 0==strcmp(zStr
, azArray
[i
]) ) return 1;
2197 ** If compiled statement pSql appears to be an EXPLAIN statement, allocate
2198 ** and populate the ShellState.aiIndent[] array with the number of
2199 ** spaces each opcode should be indented before it is output.
2201 ** The indenting rules are:
2203 ** * For each "Next", "Prev", "VNext" or "VPrev" instruction, indent
2204 ** all opcodes that occur between the p2 jump destination and the opcode
2205 ** itself by 2 spaces.
2207 ** * For each "Goto", if the jump destination is earlier in the program
2208 ** and ends on one of:
2209 ** Yield SeekGt SeekLt RowSetRead Rewind
2210 ** or if the P1 parameter is one instead of zero,
2211 ** then indent all opcodes between the earlier instruction
2212 ** and "Goto" by 2 spaces.
2214 static void explain_data_prepare(ShellState
*p
, sqlite3_stmt
*pSql
){
2215 const char *zSql
; /* The text of the SQL statement */
2216 const char *z
; /* Used to check if this is an EXPLAIN */
2217 int *abYield
= 0; /* True if op is an OP_Yield */
2218 int nAlloc
= 0; /* Allocated size of p->aiIndent[], abYield */
2219 int iOp
; /* Index of operation in p->aiIndent[] */
2221 const char *azNext
[] = { "Next", "Prev", "VPrev", "VNext", "SorterNext",
2222 "NextIfOpen", "PrevIfOpen", 0 };
2223 const char *azYield
[] = { "Yield", "SeekLT", "SeekGT", "RowSetRead",
2225 const char *azGoto
[] = { "Goto", 0 };
2227 /* Try to figure out if this is really an EXPLAIN statement. If this
2228 ** cannot be verified, return early. */
2229 if( sqlite3_column_count(pSql
)!=8 ){
2233 zSql
= sqlite3_sql(pSql
);
2234 if( zSql
==0 ) return;
2235 for(z
=zSql
; *z
==' ' || *z
=='\t' || *z
=='\n' || *z
=='\f' || *z
=='\r'; z
++);
2236 if( sqlite3_strnicmp(z
, "explain", 7) ){
2241 for(iOp
=0; SQLITE_ROW
==sqlite3_step(pSql
); iOp
++){
2243 int iAddr
= sqlite3_column_int(pSql
, 0);
2244 const char *zOp
= (const char*)sqlite3_column_text(pSql
, 1);
2246 /* Set p2 to the P2 field of the current opcode. Then, assuming that
2247 ** p2 is an instruction address, set variable p2op to the index of that
2248 ** instruction in the aiIndent[] array. p2 and p2op may be different if
2249 ** the current instruction is part of a sub-program generated by an
2250 ** SQL trigger or foreign key. */
2251 int p2
= sqlite3_column_int(pSql
, 3);
2252 int p2op
= (p2
+ (iOp
-iAddr
));
2254 /* Grow the p->aiIndent array as required */
2257 /* Do further verfication that this is explain output. Abort if
2259 static const char *explainCols
[] = {
2260 "addr", "opcode", "p1", "p2", "p3", "p4", "p5", "comment" };
2262 for(jj
=0; jj
<ArraySize(explainCols
); jj
++){
2263 if( strcmp(sqlite3_column_name(pSql
,jj
),explainCols
[jj
])!=0 ){
2265 sqlite3_reset(pSql
);
2271 p
->aiIndent
= (int*)sqlite3_realloc64(p
->aiIndent
, nAlloc
*sizeof(int));
2272 abYield
= (int*)sqlite3_realloc64(abYield
, nAlloc
*sizeof(int));
2274 abYield
[iOp
] = str_in_array(zOp
, azYield
);
2275 p
->aiIndent
[iOp
] = 0;
2278 if( str_in_array(zOp
, azNext
) ){
2279 for(i
=p2op
; i
<iOp
; i
++) p
->aiIndent
[i
] += 2;
2281 if( str_in_array(zOp
, azGoto
) && p2op
<p
->nIndent
2282 && (abYield
[p2op
] || sqlite3_column_int(pSql
, 2))
2284 for(i
=p2op
; i
<iOp
; i
++) p
->aiIndent
[i
] += 2;
2289 sqlite3_free(abYield
);
2290 sqlite3_reset(pSql
);
2294 ** Free the array allocated by explain_data_prepare().
2296 static void explain_data_delete(ShellState
*p
){
2297 sqlite3_free(p
->aiIndent
);
2304 ** Disable and restore .wheretrace and .selecttrace settings.
2306 #if defined(SQLITE_DEBUG) && defined(SQLITE_ENABLE_SELECTTRACE)
2307 extern int sqlite3SelectTrace
;
2308 static int savedSelectTrace
;
2310 #if defined(SQLITE_DEBUG) && defined(SQLITE_ENABLE_WHERETRACE)
2311 extern int sqlite3WhereTrace
;
2312 static int savedWhereTrace
;
2314 static void disable_debug_trace_modes(void){
2315 #if defined(SQLITE_DEBUG) && defined(SQLITE_ENABLE_SELECTTRACE)
2316 savedSelectTrace
= sqlite3SelectTrace
;
2317 sqlite3SelectTrace
= 0;
2319 #if defined(SQLITE_DEBUG) && defined(SQLITE_ENABLE_WHERETRACE)
2320 savedWhereTrace
= sqlite3WhereTrace
;
2321 sqlite3WhereTrace
= 0;
2324 static void restore_debug_trace_modes(void){
2325 #if defined(SQLITE_DEBUG) && defined(SQLITE_ENABLE_SELECTTRACE)
2326 sqlite3SelectTrace
= savedSelectTrace
;
2328 #if defined(SQLITE_DEBUG) && defined(SQLITE_ENABLE_WHERETRACE)
2329 sqlite3WhereTrace
= savedWhereTrace
;
2334 ** Run a prepared statement
2336 static void exec_prepared_stmt(
2337 ShellState
*pArg
, /* Pointer to ShellState */
2338 sqlite3_stmt
*pStmt
, /* Statment to run */
2339 int (*xCallback
)(void*,int,char**,char**,int*) /* Callback function */
2343 /* perform the first step. this will tell us if we
2344 ** have a result set or not and how wide it is.
2346 rc
= sqlite3_step(pStmt
);
2347 /* if we have a result set... */
2348 if( SQLITE_ROW
== rc
){
2349 /* if we have a callback... */
2351 /* allocate space for col name ptr, value ptr, and type */
2352 int nCol
= sqlite3_column_count(pStmt
);
2353 void *pData
= sqlite3_malloc64(3*nCol
*sizeof(const char*) + 1);
2357 char **azCols
= (char **)pData
; /* Names of result columns */
2358 char **azVals
= &azCols
[nCol
]; /* Results */
2359 int *aiTypes
= (int *)&azVals
[nCol
]; /* Result types */
2361 assert(sizeof(int) <= sizeof(char *));
2362 /* save off ptrs to column names */
2363 for(i
=0; i
<nCol
; i
++){
2364 azCols
[i
] = (char *)sqlite3_column_name(pStmt
, i
);
2367 /* extract the data and data types */
2368 for(i
=0; i
<nCol
; i
++){
2369 aiTypes
[i
] = x
= sqlite3_column_type(pStmt
, i
);
2370 if( x
==SQLITE_BLOB
&& pArg
&& pArg
->cMode
==MODE_Insert
){
2373 azVals
[i
] = (char*)sqlite3_column_text(pStmt
, i
);
2375 if( !azVals
[i
] && (aiTypes
[i
]!=SQLITE_NULL
) ){
2377 break; /* from for */
2381 /* if data and types extracted successfully... */
2382 if( SQLITE_ROW
== rc
){
2383 /* call the supplied callback with the result row data */
2384 if( xCallback(pArg
, nCol
, azVals
, azCols
, aiTypes
) ){
2387 rc
= sqlite3_step(pStmt
);
2390 } while( SQLITE_ROW
== rc
);
2391 sqlite3_free(pData
);
2395 rc
= sqlite3_step(pStmt
);
2396 } while( rc
== SQLITE_ROW
);
2402 ** This function is called to process SQL if the previous shell command
2403 ** was ".expert". It passes the SQL in the second argument directly to
2404 ** the sqlite3expert object.
2406 ** If successful, SQLITE_OK is returned. Otherwise, an SQLite error
2407 ** code. In this case, (*pzErr) may be set to point to a buffer containing
2408 ** an English language error message. It is the responsibility of the
2409 ** caller to eventually free this buffer using sqlite3_free().
2411 static int expertHandleSQL(
2416 assert( pState
->expert
.pExpert
);
2417 assert( pzErr
==0 || *pzErr
==0 );
2418 return sqlite3_expert_sql(pState
->expert
.pExpert
, zSql
, pzErr
);
2422 ** This function is called either to silently clean up the object
2423 ** created by the ".expert" command (if bCancel==1), or to generate a
2424 ** report from it and then clean it up (if bCancel==0).
2426 ** If successful, SQLITE_OK is returned. Otherwise, an SQLite error
2427 ** code. In this case, (*pzErr) may be set to point to a buffer containing
2428 ** an English language error message. It is the responsibility of the
2429 ** caller to eventually free this buffer using sqlite3_free().
2431 static int expertFinish(
2437 sqlite3expert
*p
= pState
->expert
.pExpert
;
2439 assert( bCancel
|| pzErr
==0 || *pzErr
==0 );
2441 FILE *out
= pState
->out
;
2442 int bVerbose
= pState
->expert
.bVerbose
;
2444 rc
= sqlite3_expert_analyze(p
, pzErr
);
2445 if( rc
==SQLITE_OK
){
2446 int nQuery
= sqlite3_expert_count(p
);
2450 const char *zCand
= sqlite3_expert_report(p
,0,EXPERT_REPORT_CANDIDATES
);
2451 raw_printf(out
, "-- Candidates -----------------------------\n");
2452 raw_printf(out
, "%s\n", zCand
);
2454 for(i
=0; i
<nQuery
; i
++){
2455 const char *zSql
= sqlite3_expert_report(p
, i
, EXPERT_REPORT_SQL
);
2456 const char *zIdx
= sqlite3_expert_report(p
, i
, EXPERT_REPORT_INDEXES
);
2457 const char *zEQP
= sqlite3_expert_report(p
, i
, EXPERT_REPORT_PLAN
);
2458 if( zIdx
==0 ) zIdx
= "(no new indexes)\n";
2460 raw_printf(out
, "-- Query %d --------------------------------\n",i
+1);
2461 raw_printf(out
, "%s\n\n", zSql
);
2463 raw_printf(out
, "%s\n", zIdx
);
2464 raw_printf(out
, "%s\n", zEQP
);
2468 sqlite3_expert_destroy(p
);
2469 pState
->expert
.pExpert
= 0;
2475 ** Execute a statement or set of statements. Print
2476 ** any result rows/columns depending on the current mode
2477 ** set via the supplied callback.
2479 ** This is very similar to SQLite's built-in sqlite3_exec()
2480 ** function except it takes a slightly different callback
2481 ** and callback data argument.
2483 static int shell_exec(
2484 sqlite3
*db
, /* An open database */
2485 const char *zSql
, /* SQL to be evaluated */
2486 int (*xCallback
)(void*,int,char**,char**,int*), /* Callback function */
2487 /* (not the same as sqlite3_exec) */
2488 ShellState
*pArg
, /* Pointer to ShellState */
2489 char **pzErrMsg
/* Error msg written here */
2491 sqlite3_stmt
*pStmt
= NULL
; /* Statement to execute. */
2492 int rc
= SQLITE_OK
; /* Return Code */
2494 const char *zLeftover
; /* Tail of unprocessed SQL */
2500 if( pArg
->expert
.pExpert
){
2501 rc
= expertHandleSQL(pArg
, zSql
, pzErrMsg
);
2502 return expertFinish(pArg
, (rc
!=SQLITE_OK
), pzErrMsg
);
2505 while( zSql
[0] && (SQLITE_OK
== rc
) ){
2506 static const char *zStmtSql
;
2507 rc
= sqlite3_prepare_v2(db
, zSql
, -1, &pStmt
, &zLeftover
);
2508 if( SQLITE_OK
!= rc
){
2510 *pzErrMsg
= save_err_msg(db
);
2514 /* this happens for a comment or white-space */
2516 while( IsSpace(zSql
[0]) ) zSql
++;
2519 zStmtSql
= sqlite3_sql(pStmt
);
2520 if( zStmtSql
==0 ) zStmtSql
= "";
2521 while( IsSpace(zStmtSql
[0]) ) zStmtSql
++;
2523 /* save off the prepared statment handle and reset row count */
2525 pArg
->pStmt
= pStmt
;
2529 /* echo the sql statement if echo on */
2530 if( pArg
&& ShellHasFlag(pArg
, SHFLG_Echo
) ){
2531 utf8_printf(pArg
->out
, "%s\n", zStmtSql
? zStmtSql
: zSql
);
2534 /* Show the EXPLAIN QUERY PLAN if .eqp is on */
2535 if( pArg
&& pArg
->autoEQP
&& sqlite3_strlike("EXPLAIN%",zStmtSql
,0)!=0 ){
2536 sqlite3_stmt
*pExplain
;
2539 disable_debug_trace_modes();
2540 sqlite3_db_config(db
, SQLITE_DBCONFIG_TRIGGER_EQP
, -1, &triggerEQP
);
2541 if( pArg
->autoEQP
>=AUTOEQP_trigger
){
2542 sqlite3_db_config(db
, SQLITE_DBCONFIG_TRIGGER_EQP
, 1, 0);
2544 zEQP
= sqlite3_mprintf("EXPLAIN QUERY PLAN %s", zStmtSql
);
2545 rc
= sqlite3_prepare_v2(db
, zEQP
, -1, &pExplain
, 0);
2546 if( rc
==SQLITE_OK
){
2547 while( sqlite3_step(pExplain
)==SQLITE_ROW
){
2548 raw_printf(pArg
->out
,"--EQP-- %d,",sqlite3_column_int(pExplain
, 0));
2549 raw_printf(pArg
->out
,"%d,", sqlite3_column_int(pExplain
, 1));
2550 raw_printf(pArg
->out
,"%d,", sqlite3_column_int(pExplain
, 2));
2551 utf8_printf(pArg
->out
,"%s\n", sqlite3_column_text(pExplain
, 3));
2554 sqlite3_finalize(pExplain
);
2556 if( pArg
->autoEQP
>=AUTOEQP_full
){
2557 /* Also do an EXPLAIN for ".eqp full" mode */
2558 zEQP
= sqlite3_mprintf("EXPLAIN %s", zStmtSql
);
2559 rc
= sqlite3_prepare_v2(db
, zEQP
, -1, &pExplain
, 0);
2560 if( rc
==SQLITE_OK
){
2561 pArg
->cMode
= MODE_Explain
;
2562 explain_data_prepare(pArg
, pExplain
);
2563 exec_prepared_stmt(pArg
, pExplain
, xCallback
);
2564 explain_data_delete(pArg
);
2566 sqlite3_finalize(pExplain
);
2569 sqlite3_db_config(db
, SQLITE_DBCONFIG_TRIGGER_EQP
, triggerEQP
, 0);
2570 restore_debug_trace_modes();
2574 pArg
->cMode
= pArg
->mode
;
2575 if( pArg
->autoExplain
2576 && sqlite3_column_count(pStmt
)==8
2577 && sqlite3_strlike("EXPLAIN%", zStmtSql
,0)==0
2579 pArg
->cMode
= MODE_Explain
;
2582 /* If the shell is currently in ".explain" mode, gather the extra
2583 ** data required to add indents to the output.*/
2584 if( pArg
->cMode
==MODE_Explain
){
2585 explain_data_prepare(pArg
, pStmt
);
2589 exec_prepared_stmt(pArg
, pStmt
, xCallback
);
2590 explain_data_delete(pArg
);
2592 /* print usage stats if stats on */
2593 if( pArg
&& pArg
->statsOn
){
2594 display_stats(db
, pArg
, 0);
2597 /* print loop-counters if required */
2598 if( pArg
&& pArg
->scanstatsOn
){
2599 display_scanstats(db
, pArg
);
2602 /* Finalize the statement just executed. If this fails, save a
2603 ** copy of the error message. Otherwise, set zSql to point to the
2604 ** next statement to execute. */
2605 rc2
= sqlite3_finalize(pStmt
);
2606 if( rc
!=SQLITE_NOMEM
) rc
= rc2
;
2607 if( rc
==SQLITE_OK
){
2609 while( IsSpace(zSql
[0]) ) zSql
++;
2610 }else if( pzErrMsg
){
2611 *pzErrMsg
= save_err_msg(db
);
2614 /* clear saved stmt handle */
2625 ** Release memory previously allocated by tableColumnList().
2627 static void freeColumnList(char **azCol
){
2629 for(i
=1; azCol
[i
]; i
++){
2630 sqlite3_free(azCol
[i
]);
2632 /* azCol[0] is a static string */
2633 sqlite3_free(azCol
);
2637 ** Return a list of pointers to strings which are the names of all
2638 ** columns in table zTab. The memory to hold the names is dynamically
2639 ** allocated and must be released by the caller using a subsequent call
2640 ** to freeColumnList().
2642 ** The azCol[0] entry is usually NULL. However, if zTab contains a rowid
2643 ** value that needs to be preserved, then azCol[0] is filled in with the
2644 ** name of the rowid column.
2646 ** The first regular column in the table is azCol[1]. The list is terminated
2647 ** by an entry with azCol[i]==0.
2649 static char **tableColumnList(ShellState
*p
, const char *zTab
){
2651 sqlite3_stmt
*pStmt
;
2655 int nPK
= 0; /* Number of PRIMARY KEY columns seen */
2656 int isIPK
= 0; /* True if one PRIMARY KEY column of type INTEGER */
2657 int preserveRowid
= ShellHasFlag(p
, SHFLG_PreserveRowid
);
2660 zSql
= sqlite3_mprintf("PRAGMA table_info=%Q", zTab
);
2661 rc
= sqlite3_prepare_v2(p
->db
, zSql
, -1, &pStmt
, 0);
2664 while( sqlite3_step(pStmt
)==SQLITE_ROW
){
2665 if( nCol
>=nAlloc
-2 ){
2666 nAlloc
= nAlloc
*2 + nCol
+ 10;
2667 azCol
= sqlite3_realloc(azCol
, nAlloc
*sizeof(azCol
[0]));
2669 raw_printf(stderr
, "Error: out of memory\n");
2673 azCol
[++nCol
] = sqlite3_mprintf("%s", sqlite3_column_text(pStmt
, 1));
2674 if( sqlite3_column_int(pStmt
, 5) ){
2677 && sqlite3_stricmp((const char*)sqlite3_column_text(pStmt
,2),
2686 sqlite3_finalize(pStmt
);
2687 if( azCol
==0 ) return 0;
2691 /* The decision of whether or not a rowid really needs to be preserved
2692 ** is tricky. We never need to preserve a rowid for a WITHOUT ROWID table
2693 ** or a table with an INTEGER PRIMARY KEY. We are unable to preserve
2694 ** rowids on tables where the rowid is inaccessible because there are other
2695 ** columns in the table named "rowid", "_rowid_", and "oid".
2697 if( preserveRowid
&& isIPK
){
2698 /* If a single PRIMARY KEY column with type INTEGER was seen, then it
2699 ** might be an alise for the ROWID. But it might also be a WITHOUT ROWID
2700 ** table or a INTEGER PRIMARY KEY DESC column, neither of which are
2701 ** ROWID aliases. To distinguish these cases, check to see if
2702 ** there is a "pk" entry in "PRAGMA index_list". There will be
2703 ** no "pk" index if the PRIMARY KEY really is an alias for the ROWID.
2705 zSql
= sqlite3_mprintf("SELECT 1 FROM pragma_index_list(%Q)"
2706 " WHERE origin='pk'", zTab
);
2707 rc
= sqlite3_prepare_v2(p
->db
, zSql
, -1, &pStmt
, 0);
2710 freeColumnList(azCol
);
2713 rc
= sqlite3_step(pStmt
);
2714 sqlite3_finalize(pStmt
);
2715 preserveRowid
= rc
==SQLITE_ROW
;
2717 if( preserveRowid
){
2718 /* Only preserve the rowid if we can find a name to use for the
2720 static char *azRowid
[] = { "rowid", "_rowid_", "oid" };
2723 for(i
=1; i
<=nCol
; i
++){
2724 if( sqlite3_stricmp(azRowid
[j
],azCol
[i
])==0 ) break;
2727 /* At this point, we know that azRowid[j] is not the name of any
2728 ** ordinary column in the table. Verify that azRowid[j] is a valid
2729 ** name for the rowid before adding it to azCol[0]. WITHOUT ROWID
2730 ** tables will fail this last check */
2731 rc
= sqlite3_table_column_metadata(p
->db
,0,zTab
,azRowid
[j
],0,0,0,0,0);
2732 if( rc
==SQLITE_OK
) azCol
[0] = azRowid
[j
];
2741 ** Toggle the reverse_unordered_selects setting.
2743 static void toggleSelectOrder(sqlite3
*db
){
2744 sqlite3_stmt
*pStmt
= 0;
2747 sqlite3_prepare_v2(db
, "PRAGMA reverse_unordered_selects", -1, &pStmt
, 0);
2748 if( sqlite3_step(pStmt
)==SQLITE_ROW
){
2749 iSetting
= sqlite3_column_int(pStmt
, 0);
2751 sqlite3_finalize(pStmt
);
2752 sqlite3_snprintf(sizeof(zStmt
), zStmt
,
2753 "PRAGMA reverse_unordered_selects(%d)", !iSetting
);
2754 sqlite3_exec(db
, zStmt
, 0, 0, 0);
2758 ** This is a different callback routine used for dumping the database.
2759 ** Each row received by this callback consists of a table name,
2760 ** the table type ("index" or "table") and SQL to create the table.
2761 ** This routine should print text sufficient to recreate the table.
2763 static int dump_callback(void *pArg
, int nArg
, char **azArg
, char **azNotUsed
){
2768 ShellState
*p
= (ShellState
*)pArg
;
2770 UNUSED_PARAMETER(azNotUsed
);
2771 if( nArg
!=3 || azArg
==0 ) return 0;
2776 if( strcmp(zTable
, "sqlite_sequence")==0 ){
2777 raw_printf(p
->out
, "DELETE FROM sqlite_sequence;\n");
2778 }else if( sqlite3_strglob("sqlite_stat?", zTable
)==0 ){
2779 raw_printf(p
->out
, "ANALYZE sqlite_master;\n");
2780 }else if( strncmp(zTable
, "sqlite_", 7)==0 ){
2782 }else if( strncmp(zSql
, "CREATE VIRTUAL TABLE", 20)==0 ){
2784 if( !p
->writableSchema
){
2785 raw_printf(p
->out
, "PRAGMA writable_schema=ON;\n");
2786 p
->writableSchema
= 1;
2788 zIns
= sqlite3_mprintf(
2789 "INSERT INTO sqlite_master(type,name,tbl_name,rootpage,sql)"
2790 "VALUES('table','%q','%q',0,'%q');",
2791 zTable
, zTable
, zSql
);
2792 utf8_printf(p
->out
, "%s\n", zIns
);
2796 printSchemaLine(p
->out
, zSql
, ";\n");
2799 if( strcmp(zType
, "table")==0 ){
2804 char *savedDestTable
;
2807 azCol
= tableColumnList(p
, zTable
);
2813 /* Always quote the table name, even if it appears to be pure ascii,
2814 ** in case it is a keyword. Ex: INSERT INTO "table" ... */
2816 appendText(&sTable
, zTable
, quoteChar(zTable
));
2817 /* If preserving the rowid, add a column list after the table name.
2818 ** In other words: "INSERT INTO tab(rowid,a,b,c,...) VALUES(...)"
2819 ** instead of the usual "INSERT INTO tab VALUES(...)".
2822 appendText(&sTable
, "(", 0);
2823 appendText(&sTable
, azCol
[0], 0);
2824 for(i
=1; azCol
[i
]; i
++){
2825 appendText(&sTable
, ",", 0);
2826 appendText(&sTable
, azCol
[i
], quoteChar(azCol
[i
]));
2828 appendText(&sTable
, ")", 0);
2831 /* Build an appropriate SELECT statement */
2833 appendText(&sSelect
, "SELECT ", 0);
2835 appendText(&sSelect
, azCol
[0], 0);
2836 appendText(&sSelect
, ",", 0);
2838 for(i
=1; azCol
[i
]; i
++){
2839 appendText(&sSelect
, azCol
[i
], quoteChar(azCol
[i
]));
2841 appendText(&sSelect
, ",", 0);
2844 freeColumnList(azCol
);
2845 appendText(&sSelect
, " FROM ", 0);
2846 appendText(&sSelect
, zTable
, quoteChar(zTable
));
2848 savedDestTable
= p
->zDestTable
;
2849 savedMode
= p
->mode
;
2850 p
->zDestTable
= sTable
.z
;
2851 p
->mode
= p
->cMode
= MODE_Insert
;
2852 rc
= shell_exec(p
->db
, sSelect
.z
, shell_callback
, p
, 0);
2853 if( (rc
&0xff)==SQLITE_CORRUPT
){
2854 raw_printf(p
->out
, "/****** CORRUPTION ERROR *******/\n");
2855 toggleSelectOrder(p
->db
);
2856 shell_exec(p
->db
, sSelect
.z
, shell_callback
, p
, 0);
2857 toggleSelectOrder(p
->db
);
2859 p
->zDestTable
= savedDestTable
;
2860 p
->mode
= savedMode
;
2869 ** Run zQuery. Use dump_callback() as the callback routine so that
2870 ** the contents of the query are output as SQL statements.
2872 ** If we get a SQLITE_CORRUPT error, rerun the query after appending
2873 ** "ORDER BY rowid DESC" to the end.
2875 static int run_schema_dump_query(
2881 rc
= sqlite3_exec(p
->db
, zQuery
, dump_callback
, p
, &zErr
);
2882 if( rc
==SQLITE_CORRUPT
){
2884 int len
= strlen30(zQuery
);
2885 raw_printf(p
->out
, "/****** CORRUPTION ERROR *******/\n");
2887 utf8_printf(p
->out
, "/****** %s ******/\n", zErr
);
2891 zQ2
= malloc( len
+100 );
2892 if( zQ2
==0 ) return rc
;
2893 sqlite3_snprintf(len
+100, zQ2
, "%s ORDER BY rowid DESC", zQuery
);
2894 rc
= sqlite3_exec(p
->db
, zQ2
, dump_callback
, p
, &zErr
);
2896 utf8_printf(p
->out
, "/****** ERROR: %s ******/\n", zErr
);
2898 rc
= SQLITE_CORRUPT
;
2907 ** Text of a help message
2909 static char zHelp
[] =
2910 #if defined(SQLITE_HAVE_ZLIB) && !defined(SQLITE_OMIT_VIRTUALTABLE)
2911 ".archive ... Manage SQL archives: \".archive --help\" for details\n"
2913 #ifndef SQLITE_OMIT_AUTHORIZATION
2914 ".auth ON|OFF Show authorizer callbacks\n"
2916 ".backup ?DB? FILE Backup DB (default \"main\") to FILE\n"
2917 ".bail on|off Stop after hitting an error. Default OFF\n"
2918 ".binary on|off Turn binary output on or off. Default OFF\n"
2919 ".cd DIRECTORY Change the working directory to DIRECTORY\n"
2920 ".changes on|off Show number of rows changed by SQL\n"
2921 ".check GLOB Fail if output since .testcase does not match\n"
2922 ".clone NEWDB Clone data into NEWDB from the existing database\n"
2923 ".databases List names and files of attached databases\n"
2924 ".dbinfo ?DB? Show status information about the database\n"
2925 ".dump ?TABLE? ... Dump the database in an SQL text format\n"
2926 " If TABLE specified, only dump tables matching\n"
2927 " LIKE pattern TABLE.\n"
2928 ".echo on|off Turn command echo on or off\n"
2929 ".eqp on|off|full Enable or disable automatic EXPLAIN QUERY PLAN\n"
2930 ".exit Exit this program\n"
2931 ".expert EXPERIMENTAL. Suggest indexes for specified queries\n"
2932 /* Because explain mode comes on automatically now, the ".explain" mode
2933 ** is removed from the help screen. It is still supported for legacy, however */
2934 /*".explain ?on|off|auto? Turn EXPLAIN output mode on or off or to automatic\n"*/
2935 ".fullschema ?--indent? Show schema and the content of sqlite_stat tables\n"
2936 ".headers on|off Turn display of headers on or off\n"
2937 ".help Show this message\n"
2938 ".import FILE TABLE Import data from FILE into TABLE\n"
2939 #ifndef SQLITE_OMIT_TEST_CONTROL
2940 ".imposter INDEX TABLE Create imposter table TABLE on index INDEX\n"
2942 ".indexes ?TABLE? Show names of all indexes\n"
2943 " If TABLE specified, only show indexes for tables\n"
2944 " matching LIKE pattern TABLE.\n"
2945 #ifdef SQLITE_ENABLE_IOTRACE
2946 ".iotrace FILE Enable I/O diagnostic logging to FILE\n"
2948 ".limit ?LIMIT? ?VAL? Display or change the value of an SQLITE_LIMIT\n"
2949 ".lint OPTIONS Report potential schema issues. Options:\n"
2950 " fkey-indexes Find missing foreign key indexes\n"
2951 #ifndef SQLITE_OMIT_LOAD_EXTENSION
2952 ".load FILE ?ENTRY? Load an extension library\n"
2954 ".log FILE|off Turn logging on or off. FILE can be stderr/stdout\n"
2955 ".mode MODE ?TABLE? Set output mode where MODE is one of:\n"
2956 " ascii Columns/rows delimited by 0x1F and 0x1E\n"
2957 " csv Comma-separated values\n"
2958 " column Left-aligned columns. (See .width)\n"
2959 " html HTML <table> code\n"
2960 " insert SQL insert statements for TABLE\n"
2961 " line One value per line\n"
2962 " list Values delimited by \"|\"\n"
2963 " quote Escape answers as for SQL\n"
2964 " tabs Tab-separated values\n"
2965 " tcl TCL list elements\n"
2966 ".nullvalue STRING Use STRING in place of NULL values\n"
2967 ".once FILENAME Output for the next SQL command only to FILENAME\n"
2968 ".open ?OPTIONS? ?FILE? Close existing database and reopen FILE\n"
2969 " The --new option starts with an empty file\n"
2970 ".output ?FILENAME? Send output to FILENAME or stdout\n"
2971 ".print STRING... Print literal STRING\n"
2972 ".prompt MAIN CONTINUE Replace the standard prompts\n"
2973 ".quit Exit this program\n"
2974 ".read FILENAME Execute SQL in FILENAME\n"
2975 ".restore ?DB? FILE Restore content of DB (default \"main\") from FILE\n"
2976 ".save FILE Write in-memory database into FILE\n"
2977 ".scanstats on|off Turn sqlite3_stmt_scanstatus() metrics on or off\n"
2978 ".schema ?PATTERN? Show the CREATE statements matching PATTERN\n"
2979 " Add --indent for pretty-printing\n"
2980 ".selftest ?--init? Run tests defined in the SELFTEST table\n"
2981 ".separator COL ?ROW? Change the column separator and optionally the row\n"
2982 " separator for both the output mode and .import\n"
2983 #if defined(SQLITE_ENABLE_SESSION)
2984 ".session CMD ... Create or control sessions\n"
2986 ".sha3sum ?OPTIONS...? Compute a SHA3 hash of database content\n"
2987 ".shell CMD ARGS... Run CMD ARGS... in a system shell\n"
2988 ".show Show the current values for various settings\n"
2989 ".stats ?on|off? Show stats or turn stats on or off\n"
2990 ".system CMD ARGS... Run CMD ARGS... in a system shell\n"
2991 ".tables ?TABLE? List names of tables\n"
2992 " If TABLE specified, only list tables matching\n"
2993 " LIKE pattern TABLE.\n"
2994 ".testcase NAME Begin redirecting output to 'testcase-out.txt'\n"
2995 ".timeout MS Try opening locked tables for MS milliseconds\n"
2996 ".timer on|off Turn SQL timer on or off\n"
2997 ".trace FILE|off Output each SQL statement as it is run\n"
2998 ".vfsinfo ?AUX? Information about the top-level VFS\n"
2999 ".vfslist List all available VFSes\n"
3000 ".vfsname ?AUX? Print the name of the VFS stack\n"
3001 ".width NUM1 NUM2 ... Set column widths for \"column\" mode\n"
3002 " Negative values right-justify\n"
3005 #if defined(SQLITE_ENABLE_SESSION)
3007 ** Print help information for the ".sessions" command
3009 void session_help(ShellState
*p
){
3011 ".session ?NAME? SUBCOMMAND ?ARGS...?\n"
3012 "If ?NAME? is omitted, the first defined session is used.\n"
3014 " attach TABLE Attach TABLE\n"
3015 " changeset FILE Write a changeset into FILE\n"
3016 " close Close one session\n"
3017 " enable ?BOOLEAN? Set or query the enable bit\n"
3018 " filter GLOB... Reject tables matching GLOBs\n"
3019 " indirect ?BOOLEAN? Mark or query the indirect status\n"
3020 " isempty Query whether the session is empty\n"
3021 " list List currently open session names\n"
3022 " open DB NAME Open a new session on DB\n"
3023 " patchset FILE Write a patchset into FILE\n"
3029 /* Forward reference */
3030 static int process_input(ShellState
*p
, FILE *in
);
3033 ** Read the content of file zName into memory obtained from sqlite3_malloc64()
3034 ** and return a pointer to the buffer. The caller is responsible for freeing
3037 ** If parameter pnByte is not NULL, (*pnByte) is set to the number of bytes
3040 ** For convenience, a nul-terminator byte is always appended to the data read
3041 ** from the file before the buffer is returned. This byte is not included in
3042 ** the final value of (*pnByte), if applicable.
3044 ** NULL is returned if any error is encountered. The final value of *pnByte
3045 ** is undefined in this case.
3047 static char *readFile(const char *zName
, int *pnByte
){
3048 FILE *in
= fopen(zName
, "rb");
3052 if( in
==0 ) return 0;
3053 fseek(in
, 0, SEEK_END
);
3056 pBuf
= sqlite3_malloc64( nIn
+1 );
3057 if( pBuf
==0 ) return 0;
3058 nRead
= fread(pBuf
, nIn
, 1, in
);
3065 if( pnByte
) *pnByte
= nIn
;
3069 #if defined(SQLITE_ENABLE_SESSION)
3071 ** Close a single OpenSession object and release all of its associated
3074 static void session_close(OpenSession
*pSession
){
3076 sqlite3session_delete(pSession
->p
);
3077 sqlite3_free(pSession
->zName
);
3078 for(i
=0; i
<pSession
->nFilter
; i
++){
3079 sqlite3_free(pSession
->azFilter
[i
]);
3081 sqlite3_free(pSession
->azFilter
);
3082 memset(pSession
, 0, sizeof(OpenSession
));
3087 ** Close all OpenSession objects and release all associated resources.
3089 #if defined(SQLITE_ENABLE_SESSION)
3090 static void session_close_all(ShellState
*p
){
3092 for(i
=0; i
<p
->nSession
; i
++){
3093 session_close(&p
->aSession
[i
]);
3098 # define session_close_all(X)
3102 ** Implementation of the xFilter function for an open session. Omit
3103 ** any tables named by ".session filter" but let all other table through.
3105 #if defined(SQLITE_ENABLE_SESSION)
3106 static int session_filter(void *pCtx
, const char *zTab
){
3107 OpenSession
*pSession
= (OpenSession
*)pCtx
;
3109 for(i
=0; i
<pSession
->nFilter
; i
++){
3110 if( sqlite3_strglob(pSession
->azFilter
[i
], zTab
)==0 ) return 0;
3117 ** Try to deduce the type of file for zName based on its content. Return
3118 ** one of the SHELL_OPEN_* constants.
3120 static int deduceDatabaseType(const char *zName
){
3121 FILE *f
= fopen(zName
, "rb");
3123 int rc
= SHELL_OPEN_UNSPEC
;
3125 if( f
==0 ) return SHELL_OPEN_NORMAL
;
3126 fseek(f
, -25, SEEK_END
);
3127 n
= fread(zBuf
, 25, 1, f
);
3128 if( n
==1 && memcmp(zBuf
, "Start-Of-SQLite3-", 17)==0 ){
3129 rc
= SHELL_OPEN_APPENDVFS
;
3131 fseek(f
, -22, SEEK_END
);
3132 n
= fread(zBuf
, 22, 1, f
);
3133 if( n
==1 && zBuf
[0]==0x50 && zBuf
[1]==0x4b && zBuf
[2]==0x05
3135 rc
= SHELL_OPEN_ZIPFILE
;
3143 ** Make sure the database is open. If it is not, then open it. If
3144 ** the database fails to open, print an error message and exit.
3146 static void open_db(ShellState
*p
, int keepAlive
){
3148 sqlite3_initialize();
3149 if( p
->openMode
==SHELL_OPEN_UNSPEC
&& access(p
->zDbFilename
,0)==0 ){
3150 p
->openMode
= deduceDatabaseType(p
->zDbFilename
);
3152 switch( p
->openMode
){
3153 case SHELL_OPEN_APPENDVFS
: {
3154 sqlite3_open_v2(p
->zDbFilename
, &p
->db
,
3155 SQLITE_OPEN_READWRITE
|SQLITE_OPEN_CREATE
, "apndvfs");
3158 case SHELL_OPEN_ZIPFILE
: {
3159 sqlite3_open(":memory:", &p
->db
);
3162 case SHELL_OPEN_UNSPEC
:
3163 case SHELL_OPEN_NORMAL
: {
3164 sqlite3_open(p
->zDbFilename
, &p
->db
);
3169 if( p
->db
==0 || SQLITE_OK
!=sqlite3_errcode(p
->db
) ){
3170 utf8_printf(stderr
,"Error: unable to open database \"%s\": %s\n",
3171 p
->zDbFilename
, sqlite3_errmsg(p
->db
));
3172 if( keepAlive
) return;
3175 #ifndef SQLITE_OMIT_LOAD_EXTENSION
3176 sqlite3_enable_load_extension(p
->db
, 1);
3178 sqlite3_fileio_init(p
->db
, 0, 0);
3179 sqlite3_shathree_init(p
->db
, 0, 0);
3180 sqlite3_completion_init(p
->db
, 0, 0);
3181 #ifdef SQLITE_HAVE_ZLIB
3182 sqlite3_zipfile_init(p
->db
, 0, 0);
3183 sqlite3_sqlar_init(p
->db
, 0, 0);
3185 sqlite3_create_function(p
->db
, "shell_add_schema", 3, SQLITE_UTF8
, 0,
3186 shellAddSchemaName
, 0, 0);
3187 sqlite3_create_function(p
->db
, "shell_module_schema", 1, SQLITE_UTF8
, 0,
3188 shellModuleSchema
, 0, 0);
3189 if( p
->openMode
==SHELL_OPEN_ZIPFILE
){
3190 char *zSql
= sqlite3_mprintf(
3191 "CREATE VIRTUAL TABLE zip USING zipfile(%Q);", p
->zDbFilename
);
3192 sqlite3_exec(p
->db
, zSql
, 0, 0, 0);
3198 #if HAVE_READLINE || HAVE_EDITLINE
3200 ** Readline completion callbacks
3202 static char *readline_completion_generator(const char *text
, int state
){
3203 static sqlite3_stmt
*pStmt
= 0;
3207 sqlite3_finalize(pStmt
);
3208 zSql
= sqlite3_mprintf("SELECT DISTINCT candidate COLLATE nocase"
3209 " FROM completion(%Q) ORDER BY 1", text
);
3210 sqlite3_prepare_v2(globalDb
, zSql
, -1, &pStmt
, 0);
3213 if( sqlite3_step(pStmt
)==SQLITE_ROW
){
3214 zRet
= strdup((const char*)sqlite3_column_text(pStmt
, 0));
3216 sqlite3_finalize(pStmt
);
3222 static char **readline_completion(const char *zText
, int iStart
, int iEnd
){
3223 rl_attempted_completion_over
= 1;
3224 return rl_completion_matches(zText
, readline_completion_generator
);
3227 #elif HAVE_LINENOISE
3229 ** Linenoise completion callback
3231 static void linenoise_completion(const char *zLine
, linenoiseCompletions
*lc
){
3232 int nLine
= strlen30(zLine
);
3234 sqlite3_stmt
*pStmt
= 0;
3238 if( nLine
>sizeof(zBuf
)-30 ) return;
3239 if( zLine
[0]=='.' ) return;
3240 for(i
=nLine
-1; i
>=0 && (isalnum(zLine
[i
]) || zLine
[i
]=='_'); i
--){}
3241 if( i
==nLine
-1 ) return;
3243 memcpy(zBuf
, zLine
, iStart
);
3244 zSql
= sqlite3_mprintf("SELECT DISTINCT candidate COLLATE nocase"
3245 " FROM completion(%Q,%Q) ORDER BY 1",
3246 &zLine
[iStart
], zLine
);
3247 sqlite3_prepare_v2(globalDb
, zSql
, -1, &pStmt
, 0);
3249 sqlite3_exec(globalDb
, "PRAGMA page_count", 0, 0, 0); /* Load the schema */
3250 while( sqlite3_step(pStmt
)==SQLITE_ROW
){
3251 const char *zCompletion
= (const char*)sqlite3_column_text(pStmt
, 0);
3252 int nCompletion
= sqlite3_column_bytes(pStmt
, 0);
3253 if( iStart
+nCompletion
< sizeof(zBuf
)-1 ){
3254 memcpy(zBuf
+iStart
, zCompletion
, nCompletion
+1);
3255 linenoiseAddCompletion(lc
, zBuf
);
3258 sqlite3_finalize(pStmt
);
3263 ** Do C-language style dequoting.
3269 ** \v -> vertical tab
3271 ** \r -> carriage return
3276 ** \NNN -> ascii character NNN in octal
3278 static void resolve_backslashes(char *z
){
3281 while( *z
&& *z
!='\\' ) z
++;
3282 for(i
=j
=0; (c
= z
[i
])!=0; i
++, j
++){
3283 if( c
=='\\' && z
[i
+1]!=0 ){
3301 }else if( c
=='\'' ){
3303 }else if( c
=='\\' ){
3305 }else if( c
>='0' && c
<='7' ){
3307 if( z
[i
+1]>='0' && z
[i
+1]<='7' ){
3309 c
= (c
<<3) + z
[i
] - '0';
3310 if( z
[i
+1]>='0' && z
[i
+1]<='7' ){
3312 c
= (c
<<3) + z
[i
] - '0';
3323 ** Return the value of a hexadecimal digit. Return -1 if the input
3324 ** is not a hex digit.
3326 static int hexDigitValue(char c
){
3327 if( c
>='0' && c
<='9' ) return c
- '0';
3328 if( c
>='a' && c
<='f' ) return c
- 'a' + 10;
3329 if( c
>='A' && c
<='F' ) return c
- 'A' + 10;
3334 ** Interpret zArg as an integer value, possibly with suffixes.
3336 static sqlite3_int64
integerValue(const char *zArg
){
3337 sqlite3_int64 v
= 0;
3338 static const struct { char *zSuffix
; int iMult
; } aMult
[] = {
3340 { "MiB", 1024*1024 },
3341 { "GiB", 1024*1024*1024 },
3344 { "GB", 1000000000 },
3347 { "G", 1000000000 },
3354 }else if( zArg
[0]=='+' ){
3357 if( zArg
[0]=='0' && zArg
[1]=='x' ){
3360 while( (x
= hexDigitValue(zArg
[0]))>=0 ){
3365 while( IsDigit(zArg
[0]) ){
3366 v
= v
*10 + zArg
[0] - '0';
3370 for(i
=0; i
<ArraySize(aMult
); i
++){
3371 if( sqlite3_stricmp(aMult
[i
].zSuffix
, zArg
)==0 ){
3372 v
*= aMult
[i
].iMult
;
3376 return isNeg
? -v
: v
;
3380 ** Interpret zArg as either an integer or a boolean value. Return 1 or 0
3381 ** for TRUE and FALSE. Return the integer value if appropriate.
3383 static int booleanValue(const char *zArg
){
3385 if( zArg
[0]=='0' && zArg
[1]=='x' ){
3386 for(i
=2; hexDigitValue(zArg
[i
])>=0; i
++){}
3388 for(i
=0; zArg
[i
]>='0' && zArg
[i
]<='9'; i
++){}
3390 if( i
>0 && zArg
[i
]==0 ) return (int)(integerValue(zArg
) & 0xffffffff);
3391 if( sqlite3_stricmp(zArg
, "on")==0 || sqlite3_stricmp(zArg
,"yes")==0 ){
3394 if( sqlite3_stricmp(zArg
, "off")==0 || sqlite3_stricmp(zArg
,"no")==0 ){
3397 utf8_printf(stderr
, "ERROR: Not a boolean value: \"%s\". Assuming \"no\".\n",
3403 ** Set or clear a shell flag according to a boolean value.
3405 static void setOrClearFlag(ShellState
*p
, unsigned mFlag
, const char *zArg
){
3406 if( booleanValue(zArg
) ){
3407 ShellSetFlag(p
, mFlag
);
3409 ShellClearFlag(p
, mFlag
);
3414 ** Close an output file, assuming it is not stderr or stdout
3416 static void output_file_close(FILE *f
){
3417 if( f
&& f
!=stdout
&& f
!=stderr
) fclose(f
);
3421 ** Try to open an output file. The names "stdout" and "stderr" are
3422 ** recognized and do the right thing. NULL is returned if the output
3423 ** filename is "off".
3425 static FILE *output_file_open(const char *zFile
){
3427 if( strcmp(zFile
,"stdout")==0 ){
3429 }else if( strcmp(zFile
, "stderr")==0 ){
3431 }else if( strcmp(zFile
, "off")==0 ){
3434 f
= fopen(zFile
, "wb");
3436 utf8_printf(stderr
, "Error: cannot open \"%s\"\n", zFile
);
3442 #if !defined(SQLITE_UNTESTABLE)
3443 #if !defined(SQLITE_OMIT_TRACE) && !defined(SQLITE_OMIT_FLOATING_POINT)
3445 ** A routine for handling output from sqlite3_trace().
3447 static int sql_trace_callback(
3453 FILE *f
= (FILE*)pArg
;
3454 UNUSED_PARAMETER(mType
);
3455 UNUSED_PARAMETER(pP
);
3457 const char *z
= (const char*)pX
;
3458 int i
= strlen30(z
);
3459 while( i
>0 && z
[i
-1]==';' ){ i
--; }
3460 utf8_printf(f
, "%.*s;\n", i
, z
);
3468 ** A no-op routine that runs with the ".breakpoint" doc-command. This is
3469 ** a useful spot to set a debugger breakpoint.
3471 static void test_breakpoint(void){
3472 static int nCall
= 0;
3477 ** An object used to read a CSV and other files for import.
3479 typedef struct ImportCtx ImportCtx
;
3481 const char *zFile
; /* Name of the input file */
3482 FILE *in
; /* Read the CSV text from this input stream */
3483 char *z
; /* Accumulated text for a field */
3484 int n
; /* Number of bytes in z */
3485 int nAlloc
; /* Space allocated for z[] */
3486 int nLine
; /* Current line number */
3487 int bNotFirst
; /* True if one or more bytes already read */
3488 int cTerm
; /* Character that terminated the most recent field */
3489 int cColSep
; /* The column separator character. (Usually ",") */
3490 int cRowSep
; /* The row separator character. (Usually "\n") */
3493 /* Append a single byte to z[] */
3494 static void import_append_char(ImportCtx
*p
, int c
){
3495 if( p
->n
+1>=p
->nAlloc
){
3496 p
->nAlloc
+= p
->nAlloc
+ 100;
3497 p
->z
= sqlite3_realloc64(p
->z
, p
->nAlloc
);
3499 raw_printf(stderr
, "out of memory\n");
3503 p
->z
[p
->n
++] = (char)c
;
3506 /* Read a single field of CSV text. Compatible with rfc4180 and extended
3507 ** with the option of having a separator other than ",".
3509 ** + Input comes from p->in.
3510 ** + Store results in p->z of length p->n. Space to hold p->z comes
3511 ** from sqlite3_malloc64().
3512 ** + Use p->cSep as the column separator. The default is ",".
3513 ** + Use p->rSep as the row separator. The default is "\n".
3514 ** + Keep track of the line number in p->nLine.
3515 ** + Store the character that terminates the field in p->cTerm. Store
3516 ** EOF on end-of-file.
3517 ** + Report syntax errors on stderr
3519 static char *SQLITE_CDECL
csv_read_one_field(ImportCtx
*p
){
3521 int cSep
= p
->cColSep
;
3522 int rSep
= p
->cRowSep
;
3525 if( c
==EOF
|| seenInterrupt
){
3531 int startLine
= p
->nLine
;
3536 if( c
==rSep
) p
->nLine
++;
3543 if( (c
==cSep
&& pc
==cQuote
)
3544 || (c
==rSep
&& pc
==cQuote
)
3545 || (c
==rSep
&& pc
=='\r' && ppc
==cQuote
)
3546 || (c
==EOF
&& pc
==cQuote
)
3548 do{ p
->n
--; }while( p
->z
[p
->n
]!=cQuote
);
3552 if( pc
==cQuote
&& c
!='\r' ){
3553 utf8_printf(stderr
, "%s:%d: unescaped %c character\n",
3554 p
->zFile
, p
->nLine
, cQuote
);
3557 utf8_printf(stderr
, "%s:%d: unterminated %c-quoted field\n",
3558 p
->zFile
, startLine
, cQuote
);
3562 import_append_char(p
, c
);
3567 /* If this is the first field being parsed and it begins with the
3568 ** UTF-8 BOM (0xEF BB BF) then skip the BOM */
3569 if( (c
&0xff)==0xef && p
->bNotFirst
==0 ){
3570 import_append_char(p
, c
);
3572 if( (c
&0xff)==0xbb ){
3573 import_append_char(p
, c
);
3575 if( (c
&0xff)==0xbf ){
3578 return csv_read_one_field(p
);
3582 while( c
!=EOF
&& c
!=cSep
&& c
!=rSep
){
3583 import_append_char(p
, c
);
3588 if( p
->n
>0 && p
->z
[p
->n
-1]=='\r' ) p
->n
--;
3592 if( p
->z
) p
->z
[p
->n
] = 0;
3597 /* Read a single field of ASCII delimited text.
3599 ** + Input comes from p->in.
3600 ** + Store results in p->z of length p->n. Space to hold p->z comes
3601 ** from sqlite3_malloc64().
3602 ** + Use p->cSep as the column separator. The default is "\x1F".
3603 ** + Use p->rSep as the row separator. The default is "\x1E".
3604 ** + Keep track of the row number in p->nLine.
3605 ** + Store the character that terminates the field in p->cTerm. Store
3606 ** EOF on end-of-file.
3607 ** + Report syntax errors on stderr
3609 static char *SQLITE_CDECL
ascii_read_one_field(ImportCtx
*p
){
3611 int cSep
= p
->cColSep
;
3612 int rSep
= p
->cRowSep
;
3615 if( c
==EOF
|| seenInterrupt
){
3619 while( c
!=EOF
&& c
!=cSep
&& c
!=rSep
){
3620 import_append_char(p
, c
);
3627 if( p
->z
) p
->z
[p
->n
] = 0;
3632 ** Try to transfer data for table zTable. If an error is seen while
3633 ** moving forward, try to go backwards. The backwards movement won't
3634 ** work for WITHOUT ROWID tables.
3636 static void tryToCloneData(
3641 sqlite3_stmt
*pQuery
= 0;
3642 sqlite3_stmt
*pInsert
= 0;
3647 int nTable
= strlen30(zTable
);
3650 const int spinRate
= 10000;
3652 zQuery
= sqlite3_mprintf("SELECT * FROM \"%w\"", zTable
);
3653 rc
= sqlite3_prepare_v2(p
->db
, zQuery
, -1, &pQuery
, 0);
3655 utf8_printf(stderr
, "Error %d: %s on [%s]\n",
3656 sqlite3_extended_errcode(p
->db
), sqlite3_errmsg(p
->db
),
3660 n
= sqlite3_column_count(pQuery
);
3661 zInsert
= sqlite3_malloc64(200 + nTable
+ n
*3);
3663 raw_printf(stderr
, "out of memory\n");
3666 sqlite3_snprintf(200+nTable
,zInsert
,
3667 "INSERT OR IGNORE INTO \"%s\" VALUES(?", zTable
);
3668 i
= strlen30(zInsert
);
3670 memcpy(zInsert
+i
, ",?", 2);
3673 memcpy(zInsert
+i
, ");", 3);
3674 rc
= sqlite3_prepare_v2(newDb
, zInsert
, -1, &pInsert
, 0);
3676 utf8_printf(stderr
, "Error %d: %s on [%s]\n",
3677 sqlite3_extended_errcode(newDb
), sqlite3_errmsg(newDb
),
3682 while( (rc
= sqlite3_step(pQuery
))==SQLITE_ROW
){
3684 switch( sqlite3_column_type(pQuery
, i
) ){
3686 sqlite3_bind_null(pInsert
, i
+1);
3689 case SQLITE_INTEGER
: {
3690 sqlite3_bind_int64(pInsert
, i
+1, sqlite3_column_int64(pQuery
,i
));
3693 case SQLITE_FLOAT
: {
3694 sqlite3_bind_double(pInsert
, i
+1, sqlite3_column_double(pQuery
,i
));
3698 sqlite3_bind_text(pInsert
, i
+1,
3699 (const char*)sqlite3_column_text(pQuery
,i
),
3704 sqlite3_bind_blob(pInsert
, i
+1, sqlite3_column_blob(pQuery
,i
),
3705 sqlite3_column_bytes(pQuery
,i
),
3711 rc
= sqlite3_step(pInsert
);
3712 if( rc
!=SQLITE_OK
&& rc
!=SQLITE_ROW
&& rc
!=SQLITE_DONE
){
3713 utf8_printf(stderr
, "Error %d: %s\n", sqlite3_extended_errcode(newDb
),
3714 sqlite3_errmsg(newDb
));
3716 sqlite3_reset(pInsert
);
3718 if( (cnt
%spinRate
)==0 ){
3719 printf("%c\b", "|/-\\"[(cnt
/spinRate
)%4]);
3723 if( rc
==SQLITE_DONE
) break;
3724 sqlite3_finalize(pQuery
);
3725 sqlite3_free(zQuery
);
3726 zQuery
= sqlite3_mprintf("SELECT * FROM \"%w\" ORDER BY rowid DESC;",
3728 rc
= sqlite3_prepare_v2(p
->db
, zQuery
, -1, &pQuery
, 0);
3730 utf8_printf(stderr
, "Warning: cannot step \"%s\" backwards", zTable
);
3733 } /* End for(k=0...) */
3736 sqlite3_finalize(pQuery
);
3737 sqlite3_finalize(pInsert
);
3738 sqlite3_free(zQuery
);
3739 sqlite3_free(zInsert
);
3744 ** Try to transfer all rows of the schema that match zWhere. For
3745 ** each row, invoke xForEach() on the object defined by that row.
3746 ** If an error is encountered while moving forward through the
3747 ** sqlite_master table, try again moving backwards.
3749 static void tryToCloneSchema(
3753 void (*xForEach
)(ShellState
*,sqlite3
*,const char*)
3755 sqlite3_stmt
*pQuery
= 0;
3758 const unsigned char *zName
;
3759 const unsigned char *zSql
;
3762 zQuery
= sqlite3_mprintf("SELECT name, sql FROM sqlite_master"
3763 " WHERE %s", zWhere
);
3764 rc
= sqlite3_prepare_v2(p
->db
, zQuery
, -1, &pQuery
, 0);
3766 utf8_printf(stderr
, "Error: (%d) %s on [%s]\n",
3767 sqlite3_extended_errcode(p
->db
), sqlite3_errmsg(p
->db
),
3769 goto end_schema_xfer
;
3771 while( (rc
= sqlite3_step(pQuery
))==SQLITE_ROW
){
3772 zName
= sqlite3_column_text(pQuery
, 0);
3773 zSql
= sqlite3_column_text(pQuery
, 1);
3774 printf("%s... ", zName
); fflush(stdout
);
3775 sqlite3_exec(newDb
, (const char*)zSql
, 0, 0, &zErrMsg
);
3777 utf8_printf(stderr
, "Error: %s\nSQL: [%s]\n", zErrMsg
, zSql
);
3778 sqlite3_free(zErrMsg
);
3782 xForEach(p
, newDb
, (const char*)zName
);
3786 if( rc
!=SQLITE_DONE
){
3787 sqlite3_finalize(pQuery
);
3788 sqlite3_free(zQuery
);
3789 zQuery
= sqlite3_mprintf("SELECT name, sql FROM sqlite_master"
3790 " WHERE %s ORDER BY rowid DESC", zWhere
);
3791 rc
= sqlite3_prepare_v2(p
->db
, zQuery
, -1, &pQuery
, 0);
3793 utf8_printf(stderr
, "Error: (%d) %s on [%s]\n",
3794 sqlite3_extended_errcode(p
->db
), sqlite3_errmsg(p
->db
),
3796 goto end_schema_xfer
;
3798 while( (rc
= sqlite3_step(pQuery
))==SQLITE_ROW
){
3799 zName
= sqlite3_column_text(pQuery
, 0);
3800 zSql
= sqlite3_column_text(pQuery
, 1);
3801 printf("%s... ", zName
); fflush(stdout
);
3802 sqlite3_exec(newDb
, (const char*)zSql
, 0, 0, &zErrMsg
);
3804 utf8_printf(stderr
, "Error: %s\nSQL: [%s]\n", zErrMsg
, zSql
);
3805 sqlite3_free(zErrMsg
);
3809 xForEach(p
, newDb
, (const char*)zName
);
3815 sqlite3_finalize(pQuery
);
3816 sqlite3_free(zQuery
);
3820 ** Open a new database file named "zNewDb". Try to recover as much information
3821 ** as possible out of the main database (which might be corrupt) and write it
3824 static void tryToClone(ShellState
*p
, const char *zNewDb
){
3827 if( access(zNewDb
,0)==0 ){
3828 utf8_printf(stderr
, "File \"%s\" already exists.\n", zNewDb
);
3831 rc
= sqlite3_open(zNewDb
, &newDb
);
3833 utf8_printf(stderr
, "Cannot create output database: %s\n",
3834 sqlite3_errmsg(newDb
));
3836 sqlite3_exec(p
->db
, "PRAGMA writable_schema=ON;", 0, 0, 0);
3837 sqlite3_exec(newDb
, "BEGIN EXCLUSIVE;", 0, 0, 0);
3838 tryToCloneSchema(p
, newDb
, "type='table'", tryToCloneData
);
3839 tryToCloneSchema(p
, newDb
, "type!='table'", 0);
3840 sqlite3_exec(newDb
, "COMMIT;", 0, 0, 0);
3841 sqlite3_exec(p
->db
, "PRAGMA writable_schema=OFF;", 0, 0, 0);
3843 sqlite3_close(newDb
);
3847 ** Change the output file back to stdout
3849 static void output_reset(ShellState
*p
){
3850 if( p
->outfile
[0]=='|' ){
3851 #ifndef SQLITE_OMIT_POPEN
3855 output_file_close(p
->out
);
3862 ** Run an SQL command and return the single integer result.
3864 static int db_int(ShellState
*p
, const char *zSql
){
3865 sqlite3_stmt
*pStmt
;
3867 sqlite3_prepare_v2(p
->db
, zSql
, -1, &pStmt
, 0);
3868 if( pStmt
&& sqlite3_step(pStmt
)==SQLITE_ROW
){
3869 res
= sqlite3_column_int(pStmt
,0);
3871 sqlite3_finalize(pStmt
);
3876 ** Convert a 2-byte or 4-byte big-endian integer into a native integer
3878 static unsigned int get2byteInt(unsigned char *a
){
3879 return (a
[0]<<8) + a
[1];
3881 static unsigned int get4byteInt(unsigned char *a
){
3882 return (a
[0]<<24) + (a
[1]<<16) + (a
[2]<<8) + a
[3];
3886 ** Implementation of the ".info" command.
3888 ** Return 1 on error, 2 to exit, and 0 otherwise.
3890 static int shell_dbinfo_command(ShellState
*p
, int nArg
, char **azArg
){
3891 static const struct { const char *zName
; int ofst
; } aField
[] = {
3892 { "file change counter:", 24 },
3893 { "database page count:", 28 },
3894 { "freelist page count:", 36 },
3895 { "schema cookie:", 40 },
3896 { "schema format:", 44 },
3897 { "default cache size:", 48 },
3898 { "autovacuum top root:", 52 },
3899 { "incremental vacuum:", 64 },
3900 { "text encoding:", 56 },
3901 { "user version:", 60 },
3902 { "application id:", 68 },
3903 { "software version:", 96 },
3905 static const struct { const char *zName
; const char *zSql
; } aQuery
[] = {
3906 { "number of tables:",
3907 "SELECT count(*) FROM %s WHERE type='table'" },
3908 { "number of indexes:",
3909 "SELECT count(*) FROM %s WHERE type='index'" },
3910 { "number of triggers:",
3911 "SELECT count(*) FROM %s WHERE type='trigger'" },
3912 { "number of views:",
3913 "SELECT count(*) FROM %s WHERE type='view'" },
3915 "SELECT total(length(sql)) FROM %s" },
3919 char *zDb
= nArg
>=2 ? azArg
[1] : "main";
3920 sqlite3_stmt
*pStmt
= 0;
3921 unsigned char aHdr
[100];
3923 if( p
->db
==0 ) return 1;
3924 sqlite3_prepare_v2(p
->db
,"SELECT data FROM sqlite_dbpage(?1) WHERE pgno=1",
3926 sqlite3_bind_text(pStmt
, 1, zDb
, -1, SQLITE_STATIC
);
3927 if( sqlite3_step(pStmt
)==SQLITE_ROW
3928 && sqlite3_column_bytes(pStmt
,0)>100
3930 memcpy(aHdr
, sqlite3_column_blob(pStmt
,0), 100);
3931 sqlite3_finalize(pStmt
);
3933 raw_printf(stderr
, "unable to read database header\n");
3934 sqlite3_finalize(pStmt
);
3937 i
= get2byteInt(aHdr
+16);
3938 if( i
==1 ) i
= 65536;
3939 utf8_printf(p
->out
, "%-20s %d\n", "database page size:", i
);
3940 utf8_printf(p
->out
, "%-20s %d\n", "write format:", aHdr
[18]);
3941 utf8_printf(p
->out
, "%-20s %d\n", "read format:", aHdr
[19]);
3942 utf8_printf(p
->out
, "%-20s %d\n", "reserved bytes:", aHdr
[20]);
3943 for(i
=0; i
<ArraySize(aField
); i
++){
3944 int ofst
= aField
[i
].ofst
;
3945 unsigned int val
= get4byteInt(aHdr
+ ofst
);
3946 utf8_printf(p
->out
, "%-20s %u", aField
[i
].zName
, val
);
3949 if( val
==1 ) raw_printf(p
->out
, " (utf8)");
3950 if( val
==2 ) raw_printf(p
->out
, " (utf16le)");
3951 if( val
==3 ) raw_printf(p
->out
, " (utf16be)");
3954 raw_printf(p
->out
, "\n");
3957 zSchemaTab
= sqlite3_mprintf("main.sqlite_master");
3958 }else if( strcmp(zDb
,"temp")==0 ){
3959 zSchemaTab
= sqlite3_mprintf("%s", "sqlite_temp_master");
3961 zSchemaTab
= sqlite3_mprintf("\"%w\".sqlite_master", zDb
);
3963 for(i
=0; i
<ArraySize(aQuery
); i
++){
3964 char *zSql
= sqlite3_mprintf(aQuery
[i
].zSql
, zSchemaTab
);
3965 int val
= db_int(p
, zSql
);
3967 utf8_printf(p
->out
, "%-20s %d\n", aQuery
[i
].zName
, val
);
3969 sqlite3_free(zSchemaTab
);
3974 ** Print the current sqlite3_errmsg() value to stderr and return 1.
3976 static int shellDatabaseError(sqlite3
*db
){
3977 const char *zErr
= sqlite3_errmsg(db
);
3978 utf8_printf(stderr
, "Error: %s\n", zErr
);
3983 ** Print an out-of-memory message to stderr and return 1.
3985 static int shellNomemError(void){
3986 raw_printf(stderr
, "Error: out of memory\n");
3991 ** Compare the pattern in zGlob[] against the text in z[]. Return TRUE
3992 ** if they match and FALSE (0) if they do not match.
3996 ** '*' Matches any sequence of zero or more characters.
3998 ** '?' Matches exactly one character.
4000 ** [...] Matches one character from the enclosed list of
4003 ** [^...] Matches one character not in the enclosed list.
4005 ** '#' Matches any sequence of one or more digits with an
4006 ** optional + or - sign in front
4008 ** ' ' Any span of whitespace matches any other span of
4011 ** Extra whitespace at the end of z[] is ignored.
4013 static int testcase_glob(const char *zGlob
, const char *z
){
4018 while( (c
= (*(zGlob
++)))!=0 ){
4020 if( !IsSpace(*z
) ) return 0;
4021 while( IsSpace(*zGlob
) ) zGlob
++;
4022 while( IsSpace(*z
) ) z
++;
4024 while( (c
=(*(zGlob
++))) == '*' || c
=='?' ){
4025 if( c
=='?' && (*(z
++))==0 ) return 0;
4030 while( *z
&& testcase_glob(zGlob
-1,z
)==0 ){
4035 while( (c2
= (*(z
++)))!=0 ){
4038 if( c2
==0 ) return 0;
4040 if( testcase_glob(zGlob
,z
) ) return 1;
4044 if( (*(z
++))==0 ) return 0;
4050 if( c
==0 ) return 0;
4057 if( c
==']' ) seen
= 1;
4060 while( c2
&& c2
!=']' ){
4061 if( c2
=='-' && zGlob
[0]!=']' && zGlob
[0]!=0 && prior_c
>0 ){
4063 if( c
>=prior_c
&& c
<=c2
) seen
= 1;
4073 if( c2
==0 || (seen
^ invert
)==0 ) return 0;
4075 if( (z
[0]=='-' || z
[0]=='+') && IsDigit(z
[1]) ) z
++;
4076 if( !IsDigit(z
[0]) ) return 0;
4078 while( IsDigit(z
[0]) ){ z
++; }
4080 if( c
!=(*(z
++)) ) return 0;
4083 while( IsSpace(*z
) ){ z
++; }
4089 ** Compare the string as a command-line option with either one or two
4090 ** initial "-" characters.
4092 static int optionMatch(const char *zStr
, const char *zOpt
){
4093 if( zStr
[0]!='-' ) return 0;
4095 if( zStr
[0]=='-' ) zStr
++;
4096 return strcmp(zStr
, zOpt
)==0;
4102 int shellDeleteFile(const char *zFilename
){
4105 wchar_t *z
= sqlite3_win32_utf8_to_unicode(zFilename
);
4109 rc
= unlink(zFilename
);
4116 ** The implementation of SQL scalar function fkey_collate_clause(), used
4117 ** by the ".lint fkey-indexes" command. This scalar function is always
4118 ** called with four arguments - the parent table name, the parent column name,
4119 ** the child table name and the child column name.
4121 ** fkey_collate_clause('parent-tab', 'parent-col', 'child-tab', 'child-col')
4123 ** If either of the named tables or columns do not exist, this function
4124 ** returns an empty string. An empty string is also returned if both tables
4125 ** and columns exist but have the same default collation sequence. Or,
4126 ** if both exist but the default collation sequences are different, this
4127 ** function returns the string " COLLATE <parent-collation>", where
4128 ** <parent-collation> is the default collation sequence of the parent column.
4130 static void shellFkeyCollateClause(
4131 sqlite3_context
*pCtx
,
4133 sqlite3_value
**apVal
4135 sqlite3
*db
= sqlite3_context_db_handle(pCtx
);
4136 const char *zParent
;
4137 const char *zParentCol
;
4138 const char *zParentSeq
;
4140 const char *zChildCol
;
4141 const char *zChildSeq
= 0; /* Initialize to avoid false-positive warning */
4145 zParent
= (const char*)sqlite3_value_text(apVal
[0]);
4146 zParentCol
= (const char*)sqlite3_value_text(apVal
[1]);
4147 zChild
= (const char*)sqlite3_value_text(apVal
[2]);
4148 zChildCol
= (const char*)sqlite3_value_text(apVal
[3]);
4150 sqlite3_result_text(pCtx
, "", -1, SQLITE_STATIC
);
4151 rc
= sqlite3_table_column_metadata(
4152 db
, "main", zParent
, zParentCol
, 0, &zParentSeq
, 0, 0, 0
4154 if( rc
==SQLITE_OK
){
4155 rc
= sqlite3_table_column_metadata(
4156 db
, "main", zChild
, zChildCol
, 0, &zChildSeq
, 0, 0, 0
4160 if( rc
==SQLITE_OK
&& sqlite3_stricmp(zParentSeq
, zChildSeq
) ){
4161 char *z
= sqlite3_mprintf(" COLLATE %s", zParentSeq
);
4162 sqlite3_result_text(pCtx
, z
, -1, SQLITE_TRANSIENT
);
4169 ** The implementation of dot-command ".lint fkey-indexes".
4171 static int lintFkeyIndexes(
4172 ShellState
*pState
, /* Current shell tool state */
4173 char **azArg
, /* Array of arguments passed to dot command */
4174 int nArg
/* Number of entries in azArg[] */
4176 sqlite3
*db
= pState
->db
; /* Database handle to query "main" db of */
4177 FILE *out
= pState
->out
; /* Stream to write non-error output to */
4178 int bVerbose
= 0; /* If -verbose is present */
4179 int bGroupByParent
= 0; /* If -groupbyparent is present */
4180 int i
; /* To iterate through azArg[] */
4181 const char *zIndent
= ""; /* How much to indent CREATE INDEX by */
4182 int rc
; /* Return code */
4183 sqlite3_stmt
*pSql
= 0; /* Compiled version of SQL statement below */
4186 ** This SELECT statement returns one row for each foreign key constraint
4187 ** in the schema of the main database. The column values are:
4189 ** 0. The text of an SQL statement similar to:
4191 ** "EXPLAIN QUERY PLAN SELECT 1 FROM child_table WHERE child_key=?"
4193 ** This SELECT is similar to the one that the foreign keys implementation
4194 ** needs to run internally on child tables. If there is an index that can
4195 ** be used to optimize this query, then it can also be used by the FK
4196 ** implementation to optimize DELETE or UPDATE statements on the parent
4199 ** 1. A GLOB pattern suitable for sqlite3_strglob(). If the plan output by
4200 ** the EXPLAIN QUERY PLAN command matches this pattern, then the schema
4201 ** contains an index that can be used to optimize the query.
4203 ** 2. Human readable text that describes the child table and columns. e.g.
4205 ** "child_table(child_key1, child_key2)"
4207 ** 3. Human readable text that describes the parent table and columns. e.g.
4209 ** "parent_table(parent_key1, parent_key2)"
4211 ** 4. A full CREATE INDEX statement for an index that could be used to
4212 ** optimize DELETE or UPDATE statements on the parent table. e.g.
4214 ** "CREATE INDEX child_table_child_key ON child_table(child_key)"
4216 ** 5. The name of the parent table.
4218 ** These six values are used by the C logic below to generate the report.
4222 " 'EXPLAIN QUERY PLAN SELECT 1 FROM ' || quote(s.name) || ' WHERE '"
4223 " || group_concat(quote(s.name) || '.' || quote(f.[from]) || '=?' "
4224 " || fkey_collate_clause("
4225 " f.[table], COALESCE(f.[to], p.[name]), s.name, f.[from]),' AND ')"
4227 " 'SEARCH TABLE ' || s.name || ' USING COVERING INDEX*('"
4228 " || group_concat('*=?', ' AND ') || ')'"
4230 " s.name || '(' || group_concat(f.[from], ', ') || ')'"
4232 " f.[table] || '(' || group_concat(COALESCE(f.[to], p.[name])) || ')'"
4234 " 'CREATE INDEX ' || quote(s.name ||'_'|| group_concat(f.[from], '_'))"
4235 " || ' ON ' || quote(s.name) || '('"
4236 " || group_concat(quote(f.[from]) ||"
4237 " fkey_collate_clause("
4238 " f.[table], COALESCE(f.[to], p.[name]), s.name, f.[from]), ', ')"
4242 "FROM sqlite_master AS s, pragma_foreign_key_list(s.name) AS f "
4243 "LEFT JOIN pragma_table_info AS p ON (pk-1=seq AND p.arg=f.[table]) "
4244 "GROUP BY s.name, f.id "
4245 "ORDER BY (CASE WHEN ? THEN f.[table] ELSE s.name END)"
4247 const char *zGlobIPK
= "SEARCH TABLE * USING INTEGER PRIMARY KEY (rowid=?)";
4249 for(i
=2; i
<nArg
; i
++){
4250 int n
= strlen30(azArg
[i
]);
4251 if( n
>1 && sqlite3_strnicmp("-verbose", azArg
[i
], n
)==0 ){
4254 else if( n
>1 && sqlite3_strnicmp("-groupbyparent", azArg
[i
], n
)==0 ){
4259 raw_printf(stderr
, "Usage: %s %s ?-verbose? ?-groupbyparent?\n",
4262 return SQLITE_ERROR
;
4266 /* Register the fkey_collate_clause() SQL function */
4267 rc
= sqlite3_create_function(db
, "fkey_collate_clause", 4, SQLITE_UTF8
,
4268 0, shellFkeyCollateClause
, 0, 0
4272 if( rc
==SQLITE_OK
){
4273 rc
= sqlite3_prepare_v2(db
, zSql
, -1, &pSql
, 0);
4275 if( rc
==SQLITE_OK
){
4276 sqlite3_bind_int(pSql
, 1, bGroupByParent
);
4279 if( rc
==SQLITE_OK
){
4282 while( SQLITE_ROW
==sqlite3_step(pSql
) ){
4284 sqlite3_stmt
*pExplain
= 0;
4285 const char *zEQP
= (const char*)sqlite3_column_text(pSql
, 0);
4286 const char *zGlob
= (const char*)sqlite3_column_text(pSql
, 1);
4287 const char *zFrom
= (const char*)sqlite3_column_text(pSql
, 2);
4288 const char *zTarget
= (const char*)sqlite3_column_text(pSql
, 3);
4289 const char *zCI
= (const char*)sqlite3_column_text(pSql
, 4);
4290 const char *zParent
= (const char*)sqlite3_column_text(pSql
, 5);
4292 rc
= sqlite3_prepare_v2(db
, zEQP
, -1, &pExplain
, 0);
4293 if( rc
!=SQLITE_OK
) break;
4294 if( SQLITE_ROW
==sqlite3_step(pExplain
) ){
4295 const char *zPlan
= (const char*)sqlite3_column_text(pExplain
, 3);
4297 0==sqlite3_strglob(zGlob
, zPlan
)
4298 || 0==sqlite3_strglob(zGlobIPK
, zPlan
)
4301 rc
= sqlite3_finalize(pExplain
);
4302 if( rc
!=SQLITE_OK
) break;
4305 raw_printf(stderr
, "Error: internal error");
4309 && (bVerbose
|| res
==0)
4310 && (zPrev
==0 || sqlite3_stricmp(zParent
, zPrev
))
4312 raw_printf(out
, "-- Parent table %s\n", zParent
);
4313 sqlite3_free(zPrev
);
4314 zPrev
= sqlite3_mprintf("%s", zParent
);
4318 raw_printf(out
, "%s%s --> %s\n", zIndent
, zCI
, zTarget
);
4319 }else if( bVerbose
){
4320 raw_printf(out
, "%s/* no extra indexes required for %s -> %s */\n",
4321 zIndent
, zFrom
, zTarget
4326 sqlite3_free(zPrev
);
4328 if( rc
!=SQLITE_OK
){
4329 raw_printf(stderr
, "%s\n", sqlite3_errmsg(db
));
4332 rc2
= sqlite3_finalize(pSql
);
4333 if( rc
==SQLITE_OK
&& rc2
!=SQLITE_OK
){
4335 raw_printf(stderr
, "%s\n", sqlite3_errmsg(db
));
4338 raw_printf(stderr
, "%s\n", sqlite3_errmsg(db
));
4345 ** Implementation of ".lint" dot command.
4347 static int lintDotCommand(
4348 ShellState
*pState
, /* Current shell tool state */
4349 char **azArg
, /* Array of arguments passed to dot command */
4350 int nArg
/* Number of entries in azArg[] */
4353 n
= (nArg
>=2 ? strlen30(azArg
[1]) : 0);
4354 if( n
<1 || sqlite3_strnicmp(azArg
[1], "fkey-indexes", n
) ) goto usage
;
4355 return lintFkeyIndexes(pState
, azArg
, nArg
);
4358 raw_printf(stderr
, "Usage %s sub-command ?switches...?\n", azArg
[0]);
4359 raw_printf(stderr
, "Where sub-commands are:\n");
4360 raw_printf(stderr
, " fkey-indexes\n");
4361 return SQLITE_ERROR
;
4364 #if !defined(SQLITE_OMIT_VIRTUALTABLE) && defined(SQLITE_HAVE_ZLIB)
4365 /*********************************************************************************
4366 ** The ".archive" or ".ar" command.
4368 static void shellPrepare(
4372 sqlite3_stmt
**ppStmt
4375 if( *pRc
==SQLITE_OK
){
4376 int rc
= sqlite3_prepare_v2(db
, zSql
, -1, ppStmt
, 0);
4377 if( rc
!=SQLITE_OK
){
4378 raw_printf(stderr
, "sql error: %s (%d)\n",
4379 sqlite3_errmsg(db
), sqlite3_errcode(db
)
4386 static void shellPreparePrintf(
4389 sqlite3_stmt
**ppStmt
,
4394 if( *pRc
==SQLITE_OK
){
4398 z
= sqlite3_vmprintf(zFmt
, ap
);
4400 *pRc
= SQLITE_NOMEM
;
4402 shellPrepare(db
, pRc
, z
, ppStmt
);
4408 static void shellFinalize(
4413 sqlite3
*db
= sqlite3_db_handle(pStmt
);
4414 int rc
= sqlite3_finalize(pStmt
);
4415 if( *pRc
==SQLITE_OK
){
4416 if( rc
!=SQLITE_OK
){
4417 raw_printf(stderr
, "SQL error: %s\n", sqlite3_errmsg(db
));
4424 static void shellReset(
4428 int rc
= sqlite3_reset(pStmt
);
4429 if( *pRc
==SQLITE_OK
){
4430 if( rc
!=SQLITE_OK
){
4431 sqlite3
*db
= sqlite3_db_handle(pStmt
);
4432 raw_printf(stderr
, "SQL error: %s\n", sqlite3_errmsg(db
));
4438 ** Structure representing a single ".ar" command.
4440 typedef struct ArCommand ArCommand
;
4442 int eCmd
; /* An AR_CMD_* value */
4443 const char *zFile
; /* --file argument, or NULL */
4444 const char *zDir
; /* --directory argument, or NULL */
4445 int bVerbose
; /* True if --verbose */
4446 int bZip
; /* True if --zip */
4447 int nArg
; /* Number of command arguments */
4448 char **azArg
; /* Array of command arguments */
4452 ** Print a usage message for the .ar command to stderr and return SQLITE_ERROR.
4454 static int arUsage(FILE *f
){
4457 "Usage: .ar [OPTION...] [FILE...]\n"
4458 "The .ar command manages sqlar archives.\n"
4461 " .ar -cf archive.sar foo bar # Create archive.sar from files foo and bar\n"
4462 " .ar -tf archive.sar # List members of archive.sar\n"
4463 " .ar -xvf archive.sar # Verbosely extract files from archive.sar\n"
4465 "Each command line must feature exactly one command option:\n"
4466 " -c, --create Create a new archive\n"
4467 " -u, --update Update or add files to an existing archive\n"
4468 " -t, --list List contents of archive\n"
4469 " -x, --extract Extract files from archive\n"
4471 "And zero or more optional options:\n"
4472 " -v, --verbose Print each filename as it is processed\n"
4473 " -f FILE, --file FILE Operate on archive FILE (default is current db)\n"
4474 " -C DIR, --directory DIR Change to directory DIR to read/extract files\n"
4476 "See also: http://sqlite.org/cli.html#sqlar_archive_support\n"
4479 return SQLITE_ERROR
;
4483 ** Print an error message for the .ar command to stderr and return
4486 static int arErrorMsg(const char *zFmt
, ...){
4490 z
= sqlite3_vmprintf(zFmt
, ap
);
4492 raw_printf(stderr
, "Error: %s (try \".ar --help\")\n", z
);
4494 return SQLITE_ERROR
;
4498 ** Values for ArCommand.eCmd.
4500 #define AR_CMD_CREATE 1
4501 #define AR_CMD_EXTRACT 2
4502 #define AR_CMD_LIST 3
4503 #define AR_CMD_UPDATE 4
4504 #define AR_CMD_HELP 5
4507 ** Other (non-command) switches.
4509 #define AR_SWITCH_VERBOSE 6
4510 #define AR_SWITCH_FILE 7
4511 #define AR_SWITCH_DIRECTORY 8
4512 #define AR_SWITCH_ZIP 9
4514 static int arProcessSwitch(ArCommand
*pAr
, int eSwitch
, const char *zArg
){
4517 case AR_CMD_EXTRACT
:
4522 return arErrorMsg("multiple command options");
4524 pAr
->eCmd
= eSwitch
;
4527 case AR_SWITCH_VERBOSE
:
4534 case AR_SWITCH_FILE
:
4537 case AR_SWITCH_DIRECTORY
:
4546 ** Parse the command line for an ".ar" command. The results are written into
4547 ** structure (*pAr). SQLITE_OK is returned if the command line is parsed
4548 ** successfully, otherwise an error message is written to stderr and
4549 ** SQLITE_ERROR returned.
4551 static int arParseCommand(
4552 char **azArg
, /* Array of arguments passed to dot command */
4553 int nArg
, /* Number of entries in azArg[] */
4554 ArCommand
*pAr
/* Populate this object */
4562 { 'c', "create", AR_CMD_CREATE
, 0 },
4563 { 'x', "extract", AR_CMD_EXTRACT
, 0 },
4564 { 't', "list", AR_CMD_LIST
, 0 },
4565 { 'u', "update", AR_CMD_UPDATE
, 0 },
4566 { 'h', "help", AR_CMD_HELP
, 0 },
4567 { 'v', "verbose", AR_SWITCH_VERBOSE
, 0 },
4568 { 'f', "file", AR_SWITCH_FILE
, 1 },
4569 { 'C', "directory", AR_SWITCH_DIRECTORY
, 1 },
4570 { 'z', "zip", AR_SWITCH_ZIP
, 0 }
4572 int nSwitch
= sizeof(aSwitch
) / sizeof(struct ArSwitch
);
4573 struct ArSwitch
*pEnd
= &aSwitch
[nSwitch
];
4576 return arUsage(stderr
);
4579 memset(pAr
, 0, sizeof(ArCommand
));
4582 /* Traditional style [tar] invocation */
4585 for(i
=0; z
[i
]; i
++){
4586 const char *zArg
= 0;
4587 struct ArSwitch
*pOpt
;
4588 for(pOpt
=&aSwitch
[0]; pOpt
<pEnd
; pOpt
++){
4589 if( z
[i
]==pOpt
->cShort
) break;
4592 return arErrorMsg("unrecognized option: %c", z
[i
]);
4596 return arErrorMsg("option requires an argument: %c",z
[i
]);
4598 zArg
= azArg
[iArg
++];
4600 if( arProcessSwitch(pAr
, pOpt
->eSwitch
, zArg
) ) return SQLITE_ERROR
;
4602 pAr
->nArg
= nArg
-iArg
;
4604 pAr
->azArg
= &azArg
[iArg
];
4607 /* Non-traditional invocation */
4609 for(iArg
=1; iArg
<nArg
; iArg
++){
4613 /* All remaining command line words are command arguments. */
4614 pAr
->azArg
= &azArg
[iArg
];
4615 pAr
->nArg
= nArg
-iArg
;
4622 /* One or more short options */
4624 const char *zArg
= 0;
4625 struct ArSwitch
*pOpt
;
4626 for(pOpt
=&aSwitch
[0]; pOpt
<pEnd
; pOpt
++){
4627 if( z
[i
]==pOpt
->cShort
) break;
4630 return arErrorMsg("unrecognized option: %c\n", z
[i
]);
4637 if( iArg
>=(nArg
-1) ){
4638 return arErrorMsg("option requires an argument: %c\n",z
[i
]);
4640 zArg
= azArg
[++iArg
];
4643 if( arProcessSwitch(pAr
, pOpt
->eSwitch
, zArg
) ) return SQLITE_ERROR
;
4645 }else if( z
[2]=='\0' ){
4646 /* A -- option, indicating that all remaining command line words
4647 ** are command arguments. */
4648 pAr
->azArg
= &azArg
[iArg
+1];
4649 pAr
->nArg
= nArg
-iArg
-1;
4653 const char *zArg
= 0; /* Argument for option, if any */
4654 struct ArSwitch
*pMatch
= 0; /* Matching option */
4655 struct ArSwitch
*pOpt
; /* Iterator */
4656 for(pOpt
=&aSwitch
[0]; pOpt
<pEnd
; pOpt
++){
4657 const char *zLong
= pOpt
->zLong
;
4658 if( (n
-2)<=strlen30(zLong
) && 0==memcmp(&z
[2], zLong
, n
-2) ){
4660 return arErrorMsg("ambiguous option: %s",z
);
4668 return arErrorMsg("unrecognized option: %s", z
);
4671 if( iArg
>=(nArg
-1) ){
4672 return arErrorMsg("option requires an argument: %s", z
);
4674 zArg
= azArg
[++iArg
];
4676 if( arProcessSwitch(pAr
, pMatch
->eSwitch
, zArg
) ) return SQLITE_ERROR
;
4686 ** This function assumes that all arguments within the ArCommand.azArg[]
4687 ** array refer to archive members, as for the --extract or --list commands.
4688 ** It checks that each of them are present. If any specified file is not
4689 ** present in the archive, an error is printed to stderr and an error
4690 ** code returned. Otherwise, if all specified arguments are present in
4691 ** the archive, SQLITE_OK is returned.
4693 ** This function strips any trailing '/' characters from each argument.
4694 ** This is consistent with the way the [tar] command seems to work on
4697 static int arCheckEntries(sqlite3
*db
, ArCommand
*pAr
){
4701 sqlite3_stmt
*pTest
= 0;
4703 shellPreparePrintf(db
, &rc
, &pTest
, "SELECT name FROM %s WHERE name=?1",
4704 pAr
->bZip
? "zipfile(?2)" : "sqlar"
4706 if( rc
==SQLITE_OK
&& pAr
->bZip
){
4707 sqlite3_bind_text(pTest
, 2, pAr
->zFile
, -1, SQLITE_TRANSIENT
);
4709 for(i
=0; i
<pAr
->nArg
&& rc
==SQLITE_OK
; i
++){
4710 char *z
= pAr
->azArg
[i
];
4711 int n
= strlen30(z
);
4713 while( n
>0 && z
[n
-1]=='/' ) n
--;
4715 sqlite3_bind_text(pTest
, 1, z
, -1, SQLITE_STATIC
);
4716 if( SQLITE_ROW
==sqlite3_step(pTest
) ){
4719 shellReset(&rc
, pTest
);
4720 if( rc
==SQLITE_OK
&& bOk
==0 ){
4721 raw_printf(stderr
, "not found in archive: %s\n", z
);
4725 shellFinalize(&rc
, pTest
);
4732 ** Format a WHERE clause that can be used against the "sqlar" table to
4733 ** identify all archive members that match the command arguments held
4734 ** in (*pAr). Leave this WHERE clause in (*pzWhere) before returning.
4735 ** The caller is responsible for eventually calling sqlite3_free() on
4736 ** any non-NULL (*pzWhere) value.
4738 static void arWhereClause(
4741 char **pzWhere
/* OUT: New WHERE clause */
4744 if( *pRc
==SQLITE_OK
){
4746 zWhere
= sqlite3_mprintf("1");
4749 const char *zSep
= "";
4750 for(i
=0; i
<pAr
->nArg
; i
++){
4751 const char *z
= pAr
->azArg
[i
];
4752 zWhere
= sqlite3_mprintf(
4753 "%z%s name = '%q' OR name BETWEEN '%q/' AND '%q0'",
4754 zWhere
, zSep
, z
, z
, z
4757 *pRc
= SQLITE_NOMEM
;
4768 ** Argument zMode must point to a buffer at least 11 bytes in size. This
4769 ** function populates this buffer with the string interpretation of
4770 ** the unix file mode passed as the second argument (e.g. "drwxr-xr-x").
4772 static void shellModeToString(char *zMode
, int mode
){
4775 /* Magic numbers copied from [man 2 stat] */
4776 if( mode
& 0040000 ){
4778 }else if( (mode
& 0120000)==0120000 ){
4785 int m
= (mode
>> ((2-i
)*3));
4786 char *a
= &zMode
[1 + i
*3];
4787 a
[0] = (m
& 0x4) ? 'r' : '-';
4788 a
[1] = (m
& 0x2) ? 'w' : '-';
4789 a
[2] = (m
& 0x1) ? 'x' : '-';
4795 ** Implementation of .ar "lisT" command.
4797 static int arListCommand(ShellState
*p
, sqlite3
*db
, ArCommand
*pAr
){
4798 const char *zSql
= "SELECT %s FROM %s WHERE %s";
4799 const char *zTbl
= (pAr
->bZip
? "zipfile(?)" : "sqlar");
4800 const char *azCols
[] = {
4802 "mode, sz, datetime(mtime, 'unixepoch'), name"
4806 sqlite3_stmt
*pSql
= 0;
4809 rc
= arCheckEntries(db
, pAr
);
4810 arWhereClause(&rc
, pAr
, &zWhere
);
4812 shellPreparePrintf(db
, &rc
, &pSql
, zSql
, azCols
[pAr
->bVerbose
], zTbl
, zWhere
);
4813 if( rc
==SQLITE_OK
&& pAr
->bZip
){
4814 sqlite3_bind_text(pSql
, 1, pAr
->zFile
, -1, SQLITE_TRANSIENT
);
4816 while( rc
==SQLITE_OK
&& SQLITE_ROW
==sqlite3_step(pSql
) ){
4817 if( pAr
->bVerbose
){
4819 shellModeToString(zMode
, sqlite3_column_int(pSql
, 0));
4821 raw_printf(p
->out
, "%s % 10d %s %s\n", zMode
,
4822 sqlite3_column_int(pSql
, 1),
4823 sqlite3_column_text(pSql
, 2),
4824 sqlite3_column_text(pSql
, 3)
4827 raw_printf(p
->out
, "%s\n", sqlite3_column_text(pSql
, 0));
4831 shellFinalize(&rc
, pSql
);
4837 ** Implementation of .ar "eXtract" command.
4839 static int arExtractCommand(ShellState
*p
, sqlite3
*db
, ArCommand
*pAr
){
4843 " writefile(?1 || name, %s, mode, mtime) "
4844 "FROM %s WHERE (%s) AND (data IS NULL OR ?2 = 0)";
4846 const char *azExtraArg
[] = {
4847 "sqlar_uncompress(data, sz)",
4848 "zipfile_uncompress(data, sz, method)"
4850 const char *azSource
[] = {
4851 "sqlar", "zipfile(?3)"
4856 sqlite3_stmt
*pSql
= 0;
4862 /* If arguments are specified, check that they actually exist within
4863 ** the archive before proceeding. And formulate a WHERE clause to
4865 rc
= arCheckEntries(db
, pAr
);
4866 arWhereClause(&rc
, pAr
, &zWhere
);
4868 if( rc
==SQLITE_OK
){
4870 zDir
= sqlite3_mprintf("%s/", pAr
->zDir
);
4872 zDir
= sqlite3_mprintf("");
4874 if( zDir
==0 ) rc
= SQLITE_NOMEM
;
4877 shellPreparePrintf(db
, &rc
, &pSql
, zSql1
,
4878 azExtraArg
[pAr
->bZip
], azSource
[pAr
->bZip
], zWhere
4881 if( rc
==SQLITE_OK
){
4882 sqlite3_bind_text(pSql
, 1, zDir
, -1, SQLITE_STATIC
);
4884 sqlite3_bind_text(pSql
, 3, pAr
->zFile
, -1, SQLITE_STATIC
);
4887 /* Run the SELECT statement twice. The first time, writefile() is called
4888 ** for all archive members that should be extracted. The second time,
4889 ** only for the directories. This is because the timestamps for
4890 ** extracted directories must be reset after they are populated (as
4891 ** populating them changes the timestamp). */
4893 sqlite3_bind_int(pSql
, 2, i
);
4894 while( rc
==SQLITE_OK
&& SQLITE_ROW
==sqlite3_step(pSql
) ){
4895 if( i
==0 && pAr
->bVerbose
){
4896 raw_printf(p
->out
, "%s\n", sqlite3_column_text(pSql
, 0));
4899 shellReset(&rc
, pSql
);
4901 shellFinalize(&rc
, pSql
);
4905 sqlite3_free(zWhere
);
4911 ** Implementation of .ar "create" and "update" commands.
4913 ** Create the "sqlar" table in the database if it does not already exist.
4914 ** Then add each file in the azFile[] array to the archive. Directories
4915 ** are added recursively. If argument bVerbose is non-zero, a message is
4916 ** printed on stdout for each file archived.
4918 ** The create command is the same as update, except that it drops
4919 ** any existing "sqlar" table before beginning.
4921 static int arCreateUpdate(
4922 ShellState
*p
, /* Shell state pointer */
4924 ArCommand
*pAr
, /* Command arguments and options */
4927 const char *zSql
= "SELECT name, mode, mtime, data FROM fsdir(?, ?)";
4928 const char *zCreate
=
4929 "CREATE TABLE IF NOT EXISTS sqlar(\n"
4930 " name TEXT PRIMARY KEY, -- name of the file\n"
4931 " mode INT, -- access permissions\n"
4932 " mtime INT, -- last modification time\n"
4933 " sz INT, -- original file size\n"
4934 " data BLOB -- compressed content\n"
4936 const char *zDrop
= "DROP TABLE IF EXISTS sqlar";
4937 const char *zInsert
= "REPLACE INTO sqlar VALUES(?,?,?,?,sqlar_compress(?))";
4939 sqlite3_stmt
*pStmt
= 0; /* Directory traverser */
4940 sqlite3_stmt
*pInsert
= 0; /* Compilation of zInsert */
4941 int i
; /* For iterating through azFile[] */
4942 int rc
; /* Return code */
4944 assert( pAr
->bZip
==0 );
4946 rc
= sqlite3_exec(db
, "SAVEPOINT ar;", 0, 0, 0);
4947 if( rc
!=SQLITE_OK
) return rc
;
4950 rc
= sqlite3_exec(db
, zDrop
, 0, 0, 0);
4951 if( rc
!=SQLITE_OK
) return rc
;
4954 rc
= sqlite3_exec(db
, zCreate
, 0, 0, 0);
4955 shellPrepare(db
, &rc
, zInsert
, &pInsert
);
4956 shellPrepare(db
, &rc
, zSql
, &pStmt
);
4957 sqlite3_bind_text(pStmt
, 2, pAr
->zDir
, -1, SQLITE_STATIC
);
4959 for(i
=0; i
<pAr
->nArg
&& rc
==SQLITE_OK
; i
++){
4960 sqlite3_bind_text(pStmt
, 1, pAr
->azArg
[i
], -1, SQLITE_STATIC
);
4961 while( rc
==SQLITE_OK
&& SQLITE_ROW
==sqlite3_step(pStmt
) ){
4963 const char *zName
= (const char*)sqlite3_column_text(pStmt
, 0);
4964 int mode
= sqlite3_column_int(pStmt
, 1);
4965 unsigned int mtime
= sqlite3_column_int(pStmt
, 2);
4967 if( pAr
->bVerbose
){
4968 raw_printf(p
->out
, "%s\n", zName
);
4971 sqlite3_bind_text(pInsert
, 1, zName
, -1, SQLITE_STATIC
);
4972 sqlite3_bind_int(pInsert
, 2, mode
);
4973 sqlite3_bind_int64(pInsert
, 3, (sqlite3_int64
)mtime
);
4975 if( S_ISDIR(mode
) ){
4977 sqlite3_bind_null(pInsert
, 5);
4979 sqlite3_bind_value(pInsert
, 5, sqlite3_column_value(pStmt
, 3));
4980 if( S_ISLNK(mode
) ){
4983 sz
= sqlite3_column_bytes(pStmt
, 3);
4987 sqlite3_bind_int(pInsert
, 4, sz
);
4988 sqlite3_step(pInsert
);
4989 rc
= sqlite3_reset(pInsert
);
4991 shellReset(&rc
, pStmt
);
4994 if( rc
!=SQLITE_OK
){
4995 sqlite3_exec(db
, "ROLLBACK TO ar; RELEASE ar;", 0, 0, 0);
4997 rc
= sqlite3_exec(db
, "RELEASE ar;", 0, 0, 0);
4999 shellFinalize(&rc
, pStmt
);
5000 shellFinalize(&rc
, pInsert
);
5005 ** Implementation of .ar "Create" command.
5007 ** Create the "sqlar" table in the database if it does not already exist.
5008 ** Then add each file in the azFile[] array to the archive. Directories
5009 ** are added recursively. If argument bVerbose is non-zero, a message is
5010 ** printed on stdout for each file archived.
5012 static int arCreateCommand(
5013 ShellState
*p
, /* Shell state pointer */
5015 ArCommand
*pAr
/* Command arguments and options */
5017 return arCreateUpdate(p
, db
, pAr
, 0);
5021 ** Implementation of .ar "Update" command.
5023 static int arUpdateCmd(ShellState
*p
, sqlite3
*db
, ArCommand
*pAr
){
5024 return arCreateUpdate(p
, db
, pAr
, 1);
5029 ** Implementation of ".ar" dot command.
5031 static int arDotCommand(
5032 ShellState
*pState
, /* Current shell tool state */
5033 char **azArg
, /* Array of arguments passed to dot command */
5034 int nArg
/* Number of entries in azArg[] */
5038 rc
= arParseCommand(azArg
, nArg
, &cmd
);
5039 if( rc
==SQLITE_OK
){
5040 sqlite3
*db
= 0; /* Database handle to use as archive */
5044 raw_printf(stderr
, "zip format requires a --file switch\n");
5045 return SQLITE_ERROR
;
5047 if( cmd
.eCmd
==AR_CMD_CREATE
|| cmd
.eCmd
==AR_CMD_UPDATE
){
5048 raw_printf(stderr
, "zip archives are read-only\n");
5049 return SQLITE_ERROR
;
5052 }else if( cmd
.zFile
){
5054 if( cmd
.eCmd
==AR_CMD_CREATE
|| cmd
.eCmd
==AR_CMD_UPDATE
){
5055 flags
= SQLITE_OPEN_READWRITE
|SQLITE_OPEN_CREATE
;
5057 flags
= SQLITE_OPEN_READONLY
;
5059 rc
= sqlite3_open_v2(cmd
.zFile
, &db
, flags
, 0);
5060 if( rc
!=SQLITE_OK
){
5061 raw_printf(stderr
, "cannot open file: %s (%s)\n",
5062 cmd
.zFile
, sqlite3_errmsg(db
)
5067 sqlite3_fileio_init(db
, 0, 0);
5068 #ifdef SQLITE_HAVE_ZLIB
5069 sqlite3_sqlar_init(db
, 0, 0);
5077 rc
= arCreateCommand(pState
, db
, &cmd
);
5080 case AR_CMD_EXTRACT
:
5081 rc
= arExtractCommand(pState
, db
, &cmd
);
5085 rc
= arListCommand(pState
, db
, &cmd
);
5089 arUsage(pState
->out
);
5093 assert( cmd
.eCmd
==AR_CMD_UPDATE
);
5094 rc
= arUpdateCmd(pState
, db
, &cmd
);
5105 /* End of the ".archive" or ".ar" command logic
5106 **********************************************************************************/
5107 #endif /* !defined(SQLITE_OMIT_VIRTUALTABLE) && defined(SQLITE_HAVE_ZLIB) */
5110 ** Implementation of ".expert" dot command.
5112 static int expertDotCommand(
5113 ShellState
*pState
, /* Current shell tool state */
5114 char **azArg
, /* Array of arguments passed to dot command */
5115 int nArg
/* Number of entries in azArg[] */
5122 assert( pState
->expert
.pExpert
==0 );
5123 memset(&pState
->expert
, 0, sizeof(ExpertInfo
));
5125 for(i
=1; rc
==SQLITE_OK
&& i
<nArg
; i
++){
5128 if( z
[0]=='-' && z
[1]=='-' ) z
++;
5130 if( n
>=2 && 0==strncmp(z
, "-verbose", n
) ){
5131 pState
->expert
.bVerbose
= 1;
5133 else if( n
>=2 && 0==strncmp(z
, "-sample", n
) ){
5135 raw_printf(stderr
, "option requires an argument: %s\n", z
);
5138 iSample
= (int)integerValue(azArg
[++i
]);
5139 if( iSample
<0 || iSample
>100 ){
5140 raw_printf(stderr
, "value out of range: %s\n", azArg
[i
]);
5146 raw_printf(stderr
, "unknown option: %s\n", z
);
5151 if( rc
==SQLITE_OK
){
5152 pState
->expert
.pExpert
= sqlite3_expert_new(pState
->db
, &zErr
);
5153 if( pState
->expert
.pExpert
==0 ){
5154 raw_printf(stderr
, "sqlite3_expert_new: %s\n", zErr
);
5157 sqlite3_expert_config(
5158 pState
->expert
.pExpert
, EXPERT_CONFIG_SAMPLE
, iSample
5168 ** If an input line begins with "." then invoke this routine to
5169 ** process that line.
5171 ** Return 1 on error, 2 to exit, and 0 otherwise.
5173 static int do_meta_command(char *zLine
, ShellState
*p
){
5180 if( p
->expert
.pExpert
){
5181 expertFinish(p
, 1, 0);
5184 /* Parse the input line into tokens.
5186 while( zLine
[h
] && nArg
<ArraySize(azArg
) ){
5187 while( IsSpace(zLine
[h
]) ){ h
++; }
5188 if( zLine
[h
]==0 ) break;
5189 if( zLine
[h
]=='\'' || zLine
[h
]=='"' ){
5190 int delim
= zLine
[h
++];
5191 azArg
[nArg
++] = &zLine
[h
];
5192 while( zLine
[h
] && zLine
[h
]!=delim
){
5193 if( zLine
[h
]=='\\' && delim
=='"' && zLine
[h
+1]!=0 ) h
++;
5196 if( zLine
[h
]==delim
){
5199 if( delim
=='"' ) resolve_backslashes(azArg
[nArg
-1]);
5201 azArg
[nArg
++] = &zLine
[h
];
5202 while( zLine
[h
] && !IsSpace(zLine
[h
]) ){ h
++; }
5203 if( zLine
[h
] ) zLine
[h
++] = 0;
5204 resolve_backslashes(azArg
[nArg
-1]);
5208 /* Process the input line.
5210 if( nArg
==0 ) return 0; /* no tokens, no error */
5211 n
= strlen30(azArg
[0]);
5214 #ifndef SQLITE_OMIT_AUTHORIZATION
5215 if( c
=='a' && strncmp(azArg
[0], "auth", n
)==0 ){
5217 raw_printf(stderr
, "Usage: .auth ON|OFF\n");
5219 goto meta_command_exit
;
5222 if( booleanValue(azArg
[1]) ){
5223 sqlite3_set_authorizer(p
->db
, shellAuth
, p
);
5225 sqlite3_set_authorizer(p
->db
, 0, 0);
5230 #if !defined(SQLITE_OMIT_VIRTUALTABLE) && defined(SQLITE_HAVE_ZLIB)
5231 if( c
=='a' && strncmp(azArg
[0], "archive", n
)==0 ){
5233 rc
= arDotCommand(p
, azArg
, nArg
);
5237 if( (c
=='b' && n
>=3 && strncmp(azArg
[0], "backup", n
)==0)
5238 || (c
=='s' && n
>=3 && strncmp(azArg
[0], "save", n
)==0)
5240 const char *zDestFile
= 0;
5241 const char *zDb
= 0;
5243 sqlite3_backup
*pBackup
;
5245 for(j
=1; j
<nArg
; j
++){
5246 const char *z
= azArg
[j
];
5248 while( z
[0]=='-' ) z
++;
5249 /* No options to process at this time */
5251 utf8_printf(stderr
, "unknown option: %s\n", azArg
[j
]);
5254 }else if( zDestFile
==0 ){
5255 zDestFile
= azArg
[j
];
5258 zDestFile
= azArg
[j
];
5260 raw_printf(stderr
, "too many arguments to .backup\n");
5265 raw_printf(stderr
, "missing FILENAME argument on .backup\n");
5268 if( zDb
==0 ) zDb
= "main";
5269 rc
= sqlite3_open(zDestFile
, &pDest
);
5270 if( rc
!=SQLITE_OK
){
5271 utf8_printf(stderr
, "Error: cannot open \"%s\"\n", zDestFile
);
5272 sqlite3_close(pDest
);
5276 pBackup
= sqlite3_backup_init(pDest
, "main", p
->db
, zDb
);
5278 utf8_printf(stderr
, "Error: %s\n", sqlite3_errmsg(pDest
));
5279 sqlite3_close(pDest
);
5282 while( (rc
= sqlite3_backup_step(pBackup
,100))==SQLITE_OK
){}
5283 sqlite3_backup_finish(pBackup
);
5284 if( rc
==SQLITE_DONE
){
5287 utf8_printf(stderr
, "Error: %s\n", sqlite3_errmsg(pDest
));
5290 sqlite3_close(pDest
);
5293 if( c
=='b' && n
>=3 && strncmp(azArg
[0], "bail", n
)==0 ){
5295 bail_on_error
= booleanValue(azArg
[1]);
5297 raw_printf(stderr
, "Usage: .bail on|off\n");
5302 if( c
=='b' && n
>=3 && strncmp(azArg
[0], "binary", n
)==0 ){
5304 if( booleanValue(azArg
[1]) ){
5305 setBinaryMode(p
->out
, 1);
5307 setTextMode(p
->out
, 1);
5310 raw_printf(stderr
, "Usage: .binary on|off\n");
5315 if( c
=='c' && strcmp(azArg
[0],"cd")==0 ){
5317 #if defined(_WIN32) || defined(WIN32)
5318 wchar_t *z
= sqlite3_win32_utf8_to_unicode(azArg
[1]);
5319 rc
= !SetCurrentDirectoryW(z
);
5322 rc
= chdir(azArg
[1]);
5325 utf8_printf(stderr
, "Cannot change to directory \"%s\"\n", azArg
[1]);
5329 raw_printf(stderr
, "Usage: .cd DIRECTORY\n");
5334 /* The undocumented ".breakpoint" command causes a call to the no-op
5335 ** routine named test_breakpoint().
5337 if( c
=='b' && n
>=3 && strncmp(azArg
[0], "breakpoint", n
)==0 ){
5341 if( c
=='c' && n
>=3 && strncmp(azArg
[0], "changes", n
)==0 ){
5343 setOrClearFlag(p
, SHFLG_CountChanges
, azArg
[1]);
5345 raw_printf(stderr
, "Usage: .changes on|off\n");
5350 /* Cancel output redirection, if it is currently set (by .testcase)
5351 ** Then read the content of the testcase-out.txt file and compare against
5352 ** azArg[1]. If there are differences, report an error and exit.
5354 if( c
=='c' && n
>=3 && strncmp(azArg
[0], "check", n
)==0 ){
5358 raw_printf(stderr
, "Usage: .check GLOB-PATTERN\n");
5360 }else if( (zRes
= readFile("testcase-out.txt", 0))==0 ){
5361 raw_printf(stderr
, "Error: cannot read 'testcase-out.txt'\n");
5363 }else if( testcase_glob(azArg
[1],zRes
)==0 ){
5365 "testcase-%s FAILED\n Expected: [%s]\n Got: [%s]\n",
5366 p
->zTestcase
, azArg
[1], zRes
);
5369 utf8_printf(stdout
, "testcase-%s ok\n", p
->zTestcase
);
5375 if( c
=='c' && strncmp(azArg
[0], "clone", n
)==0 ){
5377 tryToClone(p
, azArg
[1]);
5379 raw_printf(stderr
, "Usage: .clone FILENAME\n");
5384 if( c
=='d' && n
>1 && strncmp(azArg
[0], "databases", n
)==0 ){
5388 memcpy(&data
, p
, sizeof(data
));
5389 data
.showHeader
= 0;
5390 data
.cMode
= data
.mode
= MODE_List
;
5391 sqlite3_snprintf(sizeof(data
.colSeparator
),data
.colSeparator
,": ");
5393 sqlite3_exec(p
->db
, "SELECT name, file FROM pragma_database_list",
5394 callback
, &data
, &zErrMsg
);
5396 utf8_printf(stderr
,"Error: %s\n", zErrMsg
);
5397 sqlite3_free(zErrMsg
);
5402 if( c
=='d' && strncmp(azArg
[0], "dbinfo", n
)==0 ){
5403 rc
= shell_dbinfo_command(p
, nArg
, azArg
);
5406 if( c
=='d' && strncmp(azArg
[0], "dump", n
)==0 ){
5407 const char *zLike
= 0;
5409 int savedShowHeader
= p
->showHeader
;
5410 ShellClearFlag(p
, SHFLG_PreserveRowid
|SHFLG_Newlines
);
5411 for(i
=1; i
<nArg
; i
++){
5412 if( azArg
[i
][0]=='-' ){
5413 const char *z
= azArg
[i
]+1;
5414 if( z
[0]=='-' ) z
++;
5415 if( strcmp(z
,"preserve-rowids")==0 ){
5416 #ifdef SQLITE_OMIT_VIRTUALTABLE
5417 raw_printf(stderr
, "The --preserve-rowids option is not compatible"
5418 " with SQLITE_OMIT_VIRTUALTABLE\n");
5420 goto meta_command_exit
;
5422 ShellSetFlag(p
, SHFLG_PreserveRowid
);
5425 if( strcmp(z
,"newlines")==0 ){
5426 ShellSetFlag(p
, SHFLG_Newlines
);
5429 raw_printf(stderr
, "Unknown option \"%s\" on \".dump\"\n", azArg
[i
]);
5431 goto meta_command_exit
;
5434 raw_printf(stderr
, "Usage: .dump ?--preserve-rowids? "
5435 "?--newlines? ?LIKE-PATTERN?\n");
5437 goto meta_command_exit
;
5443 /* When playing back a "dump", the content might appear in an order
5444 ** which causes immediate foreign key constraints to be violated.
5445 ** So disable foreign-key constraint enforcement to prevent problems. */
5446 raw_printf(p
->out
, "PRAGMA foreign_keys=OFF;\n");
5447 raw_printf(p
->out
, "BEGIN TRANSACTION;\n");
5448 p
->writableSchema
= 0;
5450 /* Set writable_schema=ON since doing so forces SQLite to initialize
5451 ** as much of the schema as it can even if the sqlite_master table is
5453 sqlite3_exec(p
->db
, "SAVEPOINT dump; PRAGMA writable_schema=ON", 0, 0, 0);
5456 run_schema_dump_query(p
,
5457 "SELECT name, type, sql FROM sqlite_master "
5458 "WHERE sql NOT NULL AND type=='table' AND name!='sqlite_sequence'"
5460 run_schema_dump_query(p
,
5461 "SELECT name, type, sql FROM sqlite_master "
5462 "WHERE name=='sqlite_sequence'"
5464 run_table_dump_query(p
,
5465 "SELECT sql FROM sqlite_master "
5466 "WHERE sql NOT NULL AND type IN ('index','trigger','view')", 0
5470 zSql
= sqlite3_mprintf(
5471 "SELECT name, type, sql FROM sqlite_master "
5472 "WHERE tbl_name LIKE %Q AND type=='table'"
5473 " AND sql NOT NULL", zLike
);
5474 run_schema_dump_query(p
,zSql
);
5476 zSql
= sqlite3_mprintf(
5477 "SELECT sql FROM sqlite_master "
5478 "WHERE sql NOT NULL"
5479 " AND type IN ('index','trigger','view')"
5480 " AND tbl_name LIKE %Q", zLike
);
5481 run_table_dump_query(p
, zSql
, 0);
5484 if( p
->writableSchema
){
5485 raw_printf(p
->out
, "PRAGMA writable_schema=OFF;\n");
5486 p
->writableSchema
= 0;
5488 sqlite3_exec(p
->db
, "PRAGMA writable_schema=OFF;", 0, 0, 0);
5489 sqlite3_exec(p
->db
, "RELEASE dump;", 0, 0, 0);
5490 raw_printf(p
->out
, p
->nErr
? "ROLLBACK; -- due to errors\n" : "COMMIT;\n");
5491 p
->showHeader
= savedShowHeader
;
5494 if( c
=='e' && strncmp(azArg
[0], "echo", n
)==0 ){
5496 setOrClearFlag(p
, SHFLG_Echo
, azArg
[1]);
5498 raw_printf(stderr
, "Usage: .echo on|off\n");
5503 if( c
=='e' && strncmp(azArg
[0], "eqp", n
)==0 ){
5505 if( strcmp(azArg
[1],"full")==0 ){
5506 p
->autoEQP
= AUTOEQP_full
;
5507 }else if( strcmp(azArg
[1],"trigger")==0 ){
5508 p
->autoEQP
= AUTOEQP_trigger
;
5510 p
->autoEQP
= booleanValue(azArg
[1]);
5513 raw_printf(stderr
, "Usage: .eqp off|on|trigger|full\n");
5518 if( c
=='e' && strncmp(azArg
[0], "exit", n
)==0 ){
5519 if( nArg
>1 && (rc
= (int)integerValue(azArg
[1]))!=0 ) exit(rc
);
5523 /* The ".explain" command is automatic now. It is largely pointless. It
5524 ** retained purely for backwards compatibility */
5525 if( c
=='e' && strncmp(azArg
[0], "explain", n
)==0 ){
5528 if( strcmp(azArg
[1],"auto")==0 ){
5531 val
= booleanValue(azArg
[1]);
5534 if( val
==1 && p
->mode
!=MODE_Explain
){
5535 p
->normalMode
= p
->mode
;
5536 p
->mode
= MODE_Explain
;
5539 if( p
->mode
==MODE_Explain
) p
->mode
= p
->normalMode
;
5541 }else if( val
==99 ){
5542 if( p
->mode
==MODE_Explain
) p
->mode
= p
->normalMode
;
5547 if( c
=='e' && strncmp(azArg
[0], "expert", n
)==0 ){
5549 expertDotCommand(p
, azArg
, nArg
);
5552 if( c
=='f' && strncmp(azArg
[0], "fullschema", n
)==0 ){
5556 memcpy(&data
, p
, sizeof(data
));
5557 data
.showHeader
= 0;
5558 data
.cMode
= data
.mode
= MODE_Semi
;
5559 if( nArg
==2 && optionMatch(azArg
[1], "indent") ){
5560 data
.cMode
= data
.mode
= MODE_Pretty
;
5564 raw_printf(stderr
, "Usage: .fullschema ?--indent?\n");
5566 goto meta_command_exit
;
5569 rc
= sqlite3_exec(p
->db
,
5571 " (SELECT sql sql, type type, tbl_name tbl_name, name name, rowid x"
5572 " FROM sqlite_master UNION ALL"
5573 " SELECT sql, type, tbl_name, name, rowid FROM sqlite_temp_master) "
5574 "WHERE type!='meta' AND sql NOTNULL AND name NOT LIKE 'sqlite_%' "
5576 callback
, &data
, &zErrMsg
5578 if( rc
==SQLITE_OK
){
5579 sqlite3_stmt
*pStmt
;
5580 rc
= sqlite3_prepare_v2(p
->db
,
5581 "SELECT rowid FROM sqlite_master"
5582 " WHERE name GLOB 'sqlite_stat[134]'",
5584 doStats
= sqlite3_step(pStmt
)==SQLITE_ROW
;
5585 sqlite3_finalize(pStmt
);
5588 raw_printf(p
->out
, "/* No STAT tables available */\n");
5590 raw_printf(p
->out
, "ANALYZE sqlite_master;\n");
5591 sqlite3_exec(p
->db
, "SELECT 'ANALYZE sqlite_master'",
5592 callback
, &data
, &zErrMsg
);
5593 data
.cMode
= data
.mode
= MODE_Insert
;
5594 data
.zDestTable
= "sqlite_stat1";
5595 shell_exec(p
->db
, "SELECT * FROM sqlite_stat1",
5596 shell_callback
, &data
,&zErrMsg
);
5597 data
.zDestTable
= "sqlite_stat3";
5598 shell_exec(p
->db
, "SELECT * FROM sqlite_stat3",
5599 shell_callback
, &data
,&zErrMsg
);
5600 data
.zDestTable
= "sqlite_stat4";
5601 shell_exec(p
->db
, "SELECT * FROM sqlite_stat4",
5602 shell_callback
, &data
, &zErrMsg
);
5603 raw_printf(p
->out
, "ANALYZE sqlite_master;\n");
5607 if( c
=='h' && strncmp(azArg
[0], "headers", n
)==0 ){
5609 p
->showHeader
= booleanValue(azArg
[1]);
5611 raw_printf(stderr
, "Usage: .headers on|off\n");
5616 if( c
=='h' && strncmp(azArg
[0], "help", n
)==0 ){
5617 utf8_printf(p
->out
, "%s", zHelp
);
5620 if( c
=='i' && strncmp(azArg
[0], "import", n
)==0 ){
5621 char *zTable
; /* Insert data into this table */
5622 char *zFile
; /* Name of file to extra content from */
5623 sqlite3_stmt
*pStmt
= NULL
; /* A statement */
5624 int nCol
; /* Number of columns in the table */
5625 int nByte
; /* Number of bytes in an SQL string */
5626 int i
, j
; /* Loop counters */
5627 int needCommit
; /* True to COMMIT or ROLLBACK at end */
5628 int nSep
; /* Number of bytes in p->colSeparator[] */
5629 char *zSql
; /* An SQL statement */
5630 ImportCtx sCtx
; /* Reader context */
5631 char *(SQLITE_CDECL
*xRead
)(ImportCtx
*); /* Func to read one value */
5632 int (SQLITE_CDECL
*xCloser
)(FILE*); /* Func to close file */
5635 raw_printf(stderr
, "Usage: .import FILE TABLE\n");
5636 goto meta_command_exit
;
5641 memset(&sCtx
, 0, sizeof(sCtx
));
5643 nSep
= strlen30(p
->colSeparator
);
5646 "Error: non-null column separator required for import\n");
5650 raw_printf(stderr
, "Error: multi-character column separators not allowed"
5654 nSep
= strlen30(p
->rowSeparator
);
5656 raw_printf(stderr
, "Error: non-null row separator required for import\n");
5659 if( nSep
==2 && p
->mode
==MODE_Csv
&& strcmp(p
->rowSeparator
, SEP_CrLf
)==0 ){
5660 /* When importing CSV (only), if the row separator is set to the
5661 ** default output row separator, change it to the default input
5662 ** row separator. This avoids having to maintain different input
5663 ** and output row separators. */
5664 sqlite3_snprintf(sizeof(p
->rowSeparator
), p
->rowSeparator
, SEP_Row
);
5665 nSep
= strlen30(p
->rowSeparator
);
5668 raw_printf(stderr
, "Error: multi-character row separators not allowed"
5674 if( sCtx
.zFile
[0]=='|' ){
5675 #ifdef SQLITE_OMIT_POPEN
5676 raw_printf(stderr
, "Error: pipes are not supported in this OS\n");
5679 sCtx
.in
= popen(sCtx
.zFile
+1, "r");
5680 sCtx
.zFile
= "<pipe>";
5684 sCtx
.in
= fopen(sCtx
.zFile
, "rb");
5687 if( p
->mode
==MODE_Ascii
){
5688 xRead
= ascii_read_one_field
;
5690 xRead
= csv_read_one_field
;
5693 utf8_printf(stderr
, "Error: cannot open \"%s\"\n", zFile
);
5696 sCtx
.cColSep
= p
->colSeparator
[0];
5697 sCtx
.cRowSep
= p
->rowSeparator
[0];
5698 zSql
= sqlite3_mprintf("SELECT * FROM %s", zTable
);
5700 raw_printf(stderr
, "Error: out of memory\n");
5704 nByte
= strlen30(zSql
);
5705 rc
= sqlite3_prepare_v2(p
->db
, zSql
, -1, &pStmt
, 0);
5706 import_append_char(&sCtx
, 0); /* To ensure sCtx.z is allocated */
5707 if( rc
&& sqlite3_strglob("no such table: *", sqlite3_errmsg(p
->db
))==0 ){
5708 char *zCreate
= sqlite3_mprintf("CREATE TABLE %s", zTable
);
5710 while( xRead(&sCtx
) ){
5711 zCreate
= sqlite3_mprintf("%z%c\n \"%w\" TEXT", zCreate
, cSep
, sCtx
.z
);
5713 if( sCtx
.cTerm
!=sCtx
.cColSep
) break;
5716 sqlite3_free(zCreate
);
5717 sqlite3_free(sCtx
.z
);
5719 utf8_printf(stderr
,"%s: empty file\n", sCtx
.zFile
);
5722 zCreate
= sqlite3_mprintf("%z\n)", zCreate
);
5723 rc
= sqlite3_exec(p
->db
, zCreate
, 0, 0, 0);
5724 sqlite3_free(zCreate
);
5726 utf8_printf(stderr
, "CREATE TABLE %s(...) failed: %s\n", zTable
,
5727 sqlite3_errmsg(p
->db
));
5728 sqlite3_free(sCtx
.z
);
5732 rc
= sqlite3_prepare_v2(p
->db
, zSql
, -1, &pStmt
, 0);
5736 if (pStmt
) sqlite3_finalize(pStmt
);
5737 utf8_printf(stderr
,"Error: %s\n", sqlite3_errmsg(p
->db
));
5741 nCol
= sqlite3_column_count(pStmt
);
5742 sqlite3_finalize(pStmt
);
5744 if( nCol
==0 ) return 0; /* no columns, no error */
5745 zSql
= sqlite3_malloc64( nByte
*2 + 20 + nCol
*2 );
5747 raw_printf(stderr
, "Error: out of memory\n");
5751 sqlite3_snprintf(nByte
+20, zSql
, "INSERT INTO \"%w\" VALUES(?", zTable
);
5753 for(i
=1; i
<nCol
; i
++){
5759 rc
= sqlite3_prepare_v2(p
->db
, zSql
, -1, &pStmt
, 0);
5762 utf8_printf(stderr
, "Error: %s\n", sqlite3_errmsg(p
->db
));
5763 if (pStmt
) sqlite3_finalize(pStmt
);
5767 needCommit
= sqlite3_get_autocommit(p
->db
);
5768 if( needCommit
) sqlite3_exec(p
->db
, "BEGIN", 0, 0, 0);
5770 int startLine
= sCtx
.nLine
;
5771 for(i
=0; i
<nCol
; i
++){
5772 char *z
= xRead(&sCtx
);
5774 ** Did we reach end-of-file before finding any columns?
5775 ** If so, stop instead of NULL filling the remaining columns.
5777 if( z
==0 && i
==0 ) break;
5779 ** Did we reach end-of-file OR end-of-line before finding any
5780 ** columns in ASCII mode? If so, stop instead of NULL filling
5781 ** the remaining columns.
5783 if( p
->mode
==MODE_Ascii
&& (z
==0 || z
[0]==0) && i
==0 ) break;
5784 sqlite3_bind_text(pStmt
, i
+1, z
, -1, SQLITE_TRANSIENT
);
5785 if( i
<nCol
-1 && sCtx
.cTerm
!=sCtx
.cColSep
){
5786 utf8_printf(stderr
, "%s:%d: expected %d columns but found %d - "
5787 "filling the rest with NULL\n",
5788 sCtx
.zFile
, startLine
, nCol
, i
+1);
5790 while( i
<=nCol
){ sqlite3_bind_null(pStmt
, i
); i
++; }
5793 if( sCtx
.cTerm
==sCtx
.cColSep
){
5797 }while( sCtx
.cTerm
==sCtx
.cColSep
);
5798 utf8_printf(stderr
, "%s:%d: expected %d columns but found %d - "
5800 sCtx
.zFile
, startLine
, nCol
, i
);
5803 sqlite3_step(pStmt
);
5804 rc
= sqlite3_reset(pStmt
);
5805 if( rc
!=SQLITE_OK
){
5806 utf8_printf(stderr
, "%s:%d: INSERT failed: %s\n", sCtx
.zFile
,
5807 startLine
, sqlite3_errmsg(p
->db
));
5810 }while( sCtx
.cTerm
!=EOF
);
5813 sqlite3_free(sCtx
.z
);
5814 sqlite3_finalize(pStmt
);
5815 if( needCommit
) sqlite3_exec(p
->db
, "COMMIT", 0, 0, 0);
5818 #ifndef SQLITE_UNTESTABLE
5819 if( c
=='i' && strncmp(azArg
[0], "imposter", n
)==0 ){
5822 sqlite3_stmt
*pStmt
;
5826 utf8_printf(stderr
, "Usage: .imposter INDEX IMPOSTER\n");
5828 goto meta_command_exit
;
5831 zSql
= sqlite3_mprintf("SELECT rootpage FROM sqlite_master"
5832 " WHERE name='%q' AND type='index'", azArg
[1]);
5833 sqlite3_prepare_v2(p
->db
, zSql
, -1, &pStmt
, 0);
5835 if( sqlite3_step(pStmt
)==SQLITE_ROW
){
5836 tnum
= sqlite3_column_int(pStmt
, 0);
5838 sqlite3_finalize(pStmt
);
5840 utf8_printf(stderr
, "no such index: \"%s\"\n", azArg
[1]);
5842 goto meta_command_exit
;
5844 zSql
= sqlite3_mprintf("PRAGMA index_xinfo='%q'", azArg
[1]);
5845 rc
= sqlite3_prepare_v2(p
->db
, zSql
, -1, &pStmt
, 0);
5848 while( sqlite3_step(pStmt
)==SQLITE_ROW
){
5850 const char *zCol
= (const char*)sqlite3_column_text(pStmt
,2);
5853 if( sqlite3_column_int(pStmt
,1)==-1 ){
5856 sqlite3_snprintf(sizeof(zLabel
),zLabel
,"expr%d",i
);
5861 zCollist
= sqlite3_mprintf("\"%w\"", zCol
);
5863 zCollist
= sqlite3_mprintf("%z,\"%w\"", zCollist
, zCol
);
5866 sqlite3_finalize(pStmt
);
5867 zSql
= sqlite3_mprintf(
5868 "CREATE TABLE \"%w\"(%s,PRIMARY KEY(%s))WITHOUT ROWID",
5869 azArg
[2], zCollist
, zCollist
);
5870 sqlite3_free(zCollist
);
5871 rc
= sqlite3_test_control(SQLITE_TESTCTRL_IMPOSTER
, p
->db
, "main", 1, tnum
);
5872 if( rc
==SQLITE_OK
){
5873 rc
= sqlite3_exec(p
->db
, zSql
, 0, 0, 0);
5874 sqlite3_test_control(SQLITE_TESTCTRL_IMPOSTER
, p
->db
, "main", 0, 0);
5876 utf8_printf(stderr
, "Error in [%s]: %s\n", zSql
, sqlite3_errmsg(p
->db
));
5878 utf8_printf(stdout
, "%s;\n", zSql
);
5880 "WARNING: writing to an imposter table will corrupt the index!\n"
5884 raw_printf(stderr
, "SQLITE_TESTCTRL_IMPOSTER returns %d\n", rc
);
5889 #endif /* !defined(SQLITE_OMIT_TEST_CONTROL) */
5891 #ifdef SQLITE_ENABLE_IOTRACE
5892 if( c
=='i' && strncmp(azArg
[0], "iotrace", n
)==0 ){
5893 SQLITE_API
extern void (SQLITE_CDECL
*sqlite3IoTrace
)(const char*, ...);
5894 if( iotrace
&& iotrace
!=stdout
) fclose(iotrace
);
5898 }else if( strcmp(azArg
[1], "-")==0 ){
5899 sqlite3IoTrace
= iotracePrintf
;
5902 iotrace
= fopen(azArg
[1], "w");
5904 utf8_printf(stderr
, "Error: cannot open \"%s\"\n", azArg
[1]);
5908 sqlite3IoTrace
= iotracePrintf
;
5914 if( c
=='l' && n
>=5 && strncmp(azArg
[0], "limits", n
)==0 ){
5915 static const struct {
5916 const char *zLimitName
; /* Name of a limit */
5917 int limitCode
; /* Integer code for that limit */
5919 { "length", SQLITE_LIMIT_LENGTH
},
5920 { "sql_length", SQLITE_LIMIT_SQL_LENGTH
},
5921 { "column", SQLITE_LIMIT_COLUMN
},
5922 { "expr_depth", SQLITE_LIMIT_EXPR_DEPTH
},
5923 { "compound_select", SQLITE_LIMIT_COMPOUND_SELECT
},
5924 { "vdbe_op", SQLITE_LIMIT_VDBE_OP
},
5925 { "function_arg", SQLITE_LIMIT_FUNCTION_ARG
},
5926 { "attached", SQLITE_LIMIT_ATTACHED
},
5927 { "like_pattern_length", SQLITE_LIMIT_LIKE_PATTERN_LENGTH
},
5928 { "variable_number", SQLITE_LIMIT_VARIABLE_NUMBER
},
5929 { "trigger_depth", SQLITE_LIMIT_TRIGGER_DEPTH
},
5930 { "worker_threads", SQLITE_LIMIT_WORKER_THREADS
},
5935 for(i
=0; i
<ArraySize(aLimit
); i
++){
5936 printf("%20s %d\n", aLimit
[i
].zLimitName
,
5937 sqlite3_limit(p
->db
, aLimit
[i
].limitCode
, -1));
5940 raw_printf(stderr
, "Usage: .limit NAME ?NEW-VALUE?\n");
5942 goto meta_command_exit
;
5945 n2
= strlen30(azArg
[1]);
5946 for(i
=0; i
<ArraySize(aLimit
); i
++){
5947 if( sqlite3_strnicmp(aLimit
[i
].zLimitName
, azArg
[1], n2
)==0 ){
5951 utf8_printf(stderr
, "ambiguous limit: \"%s\"\n", azArg
[1]);
5953 goto meta_command_exit
;
5958 utf8_printf(stderr
, "unknown limit: \"%s\"\n"
5959 "enter \".limits\" with no arguments for a list.\n",
5962 goto meta_command_exit
;
5965 sqlite3_limit(p
->db
, aLimit
[iLimit
].limitCode
,
5966 (int)integerValue(azArg
[2]));
5968 printf("%20s %d\n", aLimit
[iLimit
].zLimitName
,
5969 sqlite3_limit(p
->db
, aLimit
[iLimit
].limitCode
, -1));
5973 if( c
=='l' && n
>2 && strncmp(azArg
[0], "lint", n
)==0 ){
5975 lintDotCommand(p
, azArg
, nArg
);
5978 #ifndef SQLITE_OMIT_LOAD_EXTENSION
5979 if( c
=='l' && strncmp(azArg
[0], "load", n
)==0 ){
5980 const char *zFile
, *zProc
;
5983 raw_printf(stderr
, "Usage: .load FILE ?ENTRYPOINT?\n");
5985 goto meta_command_exit
;
5988 zProc
= nArg
>=3 ? azArg
[2] : 0;
5990 rc
= sqlite3_load_extension(p
->db
, zFile
, zProc
, &zErrMsg
);
5991 if( rc
!=SQLITE_OK
){
5992 utf8_printf(stderr
, "Error: %s\n", zErrMsg
);
5993 sqlite3_free(zErrMsg
);
5999 if( c
=='l' && strncmp(azArg
[0], "log", n
)==0 ){
6001 raw_printf(stderr
, "Usage: .log FILENAME\n");
6004 const char *zFile
= azArg
[1];
6005 output_file_close(p
->pLog
);
6006 p
->pLog
= output_file_open(zFile
);
6010 if( c
=='m' && strncmp(azArg
[0], "mode", n
)==0 ){
6011 const char *zMode
= nArg
>=2 ? azArg
[1] : "";
6012 int n2
= strlen30(zMode
);
6014 if( c2
=='l' && n2
>2 && strncmp(azArg
[1],"lines",n2
)==0 ){
6015 p
->mode
= MODE_Line
;
6016 sqlite3_snprintf(sizeof(p
->rowSeparator
), p
->rowSeparator
, SEP_Row
);
6017 }else if( c2
=='c' && strncmp(azArg
[1],"columns",n2
)==0 ){
6018 p
->mode
= MODE_Column
;
6019 sqlite3_snprintf(sizeof(p
->rowSeparator
), p
->rowSeparator
, SEP_Row
);
6020 }else if( c2
=='l' && n2
>2 && strncmp(azArg
[1],"list",n2
)==0 ){
6021 p
->mode
= MODE_List
;
6022 sqlite3_snprintf(sizeof(p
->colSeparator
), p
->colSeparator
, SEP_Column
);
6023 sqlite3_snprintf(sizeof(p
->rowSeparator
), p
->rowSeparator
, SEP_Row
);
6024 }else if( c2
=='h' && strncmp(azArg
[1],"html",n2
)==0 ){
6025 p
->mode
= MODE_Html
;
6026 }else if( c2
=='t' && strncmp(azArg
[1],"tcl",n2
)==0 ){
6028 sqlite3_snprintf(sizeof(p
->colSeparator
), p
->colSeparator
, SEP_Space
);
6029 sqlite3_snprintf(sizeof(p
->rowSeparator
), p
->rowSeparator
, SEP_Row
);
6030 }else if( c2
=='c' && strncmp(azArg
[1],"csv",n2
)==0 ){
6032 sqlite3_snprintf(sizeof(p
->colSeparator
), p
->colSeparator
, SEP_Comma
);
6033 sqlite3_snprintf(sizeof(p
->rowSeparator
), p
->rowSeparator
, SEP_CrLf
);
6034 }else if( c2
=='t' && strncmp(azArg
[1],"tabs",n2
)==0 ){
6035 p
->mode
= MODE_List
;
6036 sqlite3_snprintf(sizeof(p
->colSeparator
), p
->colSeparator
, SEP_Tab
);
6037 }else if( c2
=='i' && strncmp(azArg
[1],"insert",n2
)==0 ){
6038 p
->mode
= MODE_Insert
;
6039 set_table_name(p
, nArg
>=3 ? azArg
[2] : "table");
6040 }else if( c2
=='q' && strncmp(azArg
[1],"quote",n2
)==0 ){
6041 p
->mode
= MODE_Quote
;
6042 }else if( c2
=='a' && strncmp(azArg
[1],"ascii",n2
)==0 ){
6043 p
->mode
= MODE_Ascii
;
6044 sqlite3_snprintf(sizeof(p
->colSeparator
), p
->colSeparator
, SEP_Unit
);
6045 sqlite3_snprintf(sizeof(p
->rowSeparator
), p
->rowSeparator
, SEP_Record
);
6046 }else if( nArg
==1 ){
6047 raw_printf(p
->out
, "current output mode: %s\n", modeDescr
[p
->mode
]);
6049 raw_printf(stderr
, "Error: mode should be one of: "
6050 "ascii column csv html insert line list quote tabs tcl\n");
6056 if( c
=='n' && strncmp(azArg
[0], "nullvalue", n
)==0 ){
6058 sqlite3_snprintf(sizeof(p
->nullValue
), p
->nullValue
,
6059 "%.*s", (int)ArraySize(p
->nullValue
)-1, azArg
[1]);
6061 raw_printf(stderr
, "Usage: .nullvalue STRING\n");
6066 if( c
=='o' && strncmp(azArg
[0], "open", n
)==0 && n
>=2 ){
6067 char *zNewFilename
; /* Name of the database file to open */
6068 int iName
= 1; /* Index in azArg[] of the filename */
6069 int newFlag
= 0; /* True to delete file before opening */
6070 /* Close the existing database */
6071 session_close_all(p
);
6072 sqlite3_close(p
->db
);
6075 sqlite3_free(p
->zFreeOnClose
);
6076 p
->zFreeOnClose
= 0;
6077 p
->openMode
= SHELL_OPEN_UNSPEC
;
6078 /* Check for command-line arguments */
6079 for(iName
=1; iName
<nArg
&& azArg
[iName
][0]=='-'; iName
++){
6080 const char *z
= azArg
[iName
];
6081 if( optionMatch(z
,"new") ){
6083 #ifdef SQLITE_HAVE_ZIP
6084 }else if( optionMatch(z
, "zip") ){
6085 p
->openMode
= SHELL_OPEN_ZIPFILE
;
6087 }else if( optionMatch(z
, "append") ){
6088 p
->openMode
= SHELL_OPEN_APPENDVFS
;
6089 }else if( z
[0]=='-' ){
6090 utf8_printf(stderr
, "unknown option: %s\n", z
);
6092 goto meta_command_exit
;
6095 /* If a filename is specified, try to open it first */
6096 zNewFilename
= nArg
>iName
? sqlite3_mprintf("%s", azArg
[iName
]) : 0;
6098 if( newFlag
) shellDeleteFile(zNewFilename
);
6099 p
->zDbFilename
= zNewFilename
;
6102 utf8_printf(stderr
, "Error: cannot open '%s'\n", zNewFilename
);
6103 sqlite3_free(zNewFilename
);
6105 p
->zFreeOnClose
= zNewFilename
;
6109 /* As a fall-back open a TEMP database */
6116 && (strncmp(azArg
[0], "output", n
)==0 || strncmp(azArg
[0], "once", n
)==0)
6118 const char *zFile
= nArg
>=2 ? azArg
[1] : "stdout";
6120 utf8_printf(stderr
, "Usage: .%s FILE\n", azArg
[0]);
6122 goto meta_command_exit
;
6124 if( n
>1 && strncmp(azArg
[0], "once", n
)==0 ){
6126 raw_printf(stderr
, "Usage: .once FILE\n");
6128 goto meta_command_exit
;
6135 if( zFile
[0]=='|' ){
6136 #ifdef SQLITE_OMIT_POPEN
6137 raw_printf(stderr
, "Error: pipes are not supported in this OS\n");
6141 p
->out
= popen(zFile
+ 1, "w");
6143 utf8_printf(stderr
,"Error: cannot open pipe \"%s\"\n", zFile
+ 1);
6147 sqlite3_snprintf(sizeof(p
->outfile
), p
->outfile
, "%s", zFile
);
6151 p
->out
= output_file_open(zFile
);
6153 if( strcmp(zFile
,"off")!=0 ){
6154 utf8_printf(stderr
,"Error: cannot write to \"%s\"\n", zFile
);
6159 sqlite3_snprintf(sizeof(p
->outfile
), p
->outfile
, "%s", zFile
);
6164 if( c
=='p' && n
>=3 && strncmp(azArg
[0], "print", n
)==0 ){
6166 for(i
=1; i
<nArg
; i
++){
6167 if( i
>1 ) raw_printf(p
->out
, " ");
6168 utf8_printf(p
->out
, "%s", azArg
[i
]);
6170 raw_printf(p
->out
, "\n");
6173 if( c
=='p' && strncmp(azArg
[0], "prompt", n
)==0 ){
6175 strncpy(mainPrompt
,azArg
[1],(int)ArraySize(mainPrompt
)-1);
6178 strncpy(continuePrompt
,azArg
[2],(int)ArraySize(continuePrompt
)-1);
6182 if( c
=='q' && strncmp(azArg
[0], "quit", n
)==0 ){
6186 if( c
=='r' && n
>=3 && strncmp(azArg
[0], "read", n
)==0 ){
6189 raw_printf(stderr
, "Usage: .read FILE\n");
6191 goto meta_command_exit
;
6193 alt
= fopen(azArg
[1], "rb");
6195 utf8_printf(stderr
,"Error: cannot open \"%s\"\n", azArg
[1]);
6198 rc
= process_input(p
, alt
);
6203 if( c
=='r' && n
>=3 && strncmp(azArg
[0], "restore", n
)==0 ){
6204 const char *zSrcFile
;
6207 sqlite3_backup
*pBackup
;
6211 zSrcFile
= azArg
[1];
6213 }else if( nArg
==3 ){
6214 zSrcFile
= azArg
[2];
6217 raw_printf(stderr
, "Usage: .restore ?DB? FILE\n");
6219 goto meta_command_exit
;
6221 rc
= sqlite3_open(zSrcFile
, &pSrc
);
6222 if( rc
!=SQLITE_OK
){
6223 utf8_printf(stderr
, "Error: cannot open \"%s\"\n", zSrcFile
);
6224 sqlite3_close(pSrc
);
6228 pBackup
= sqlite3_backup_init(p
->db
, zDb
, pSrc
, "main");
6230 utf8_printf(stderr
, "Error: %s\n", sqlite3_errmsg(p
->db
));
6231 sqlite3_close(pSrc
);
6234 while( (rc
= sqlite3_backup_step(pBackup
,100))==SQLITE_OK
6235 || rc
==SQLITE_BUSY
){
6236 if( rc
==SQLITE_BUSY
){
6237 if( nTimeout
++ >= 3 ) break;
6241 sqlite3_backup_finish(pBackup
);
6242 if( rc
==SQLITE_DONE
){
6244 }else if( rc
==SQLITE_BUSY
|| rc
==SQLITE_LOCKED
){
6245 raw_printf(stderr
, "Error: source database is busy\n");
6248 utf8_printf(stderr
, "Error: %s\n", sqlite3_errmsg(p
->db
));
6251 sqlite3_close(pSrc
);
6255 if( c
=='s' && strncmp(azArg
[0], "scanstats", n
)==0 ){
6257 p
->scanstatsOn
= booleanValue(azArg
[1]);
6258 #ifndef SQLITE_ENABLE_STMT_SCANSTATUS
6259 raw_printf(stderr
, "Warning: .scanstats not available in this build.\n");
6262 raw_printf(stderr
, "Usage: .scanstats on|off\n");
6267 if( c
=='s' && strncmp(azArg
[0], "schema", n
)==0 ){
6271 const char *zDiv
= "(";
6272 const char *zName
= 0;
6278 memcpy(&data
, p
, sizeof(data
));
6279 data
.showHeader
= 0;
6280 data
.cMode
= data
.mode
= MODE_Semi
;
6282 for(ii
=1; ii
<nArg
; ii
++){
6283 if( optionMatch(azArg
[ii
],"indent") ){
6284 data
.cMode
= data
.mode
= MODE_Pretty
;
6285 }else if( optionMatch(azArg
[ii
],"debug") ){
6287 }else if( zName
==0 ){
6290 raw_printf(stderr
, "Usage: .schema ?--indent? ?LIKE-PATTERN?\n");
6292 goto meta_command_exit
;
6296 int isMaster
= sqlite3_strlike(zName
, "sqlite_master", 0)==0;
6297 if( isMaster
|| sqlite3_strlike(zName
,"sqlite_temp_master",0)==0 ){
6298 char *new_argv
[2], *new_colv
[2];
6299 new_argv
[0] = sqlite3_mprintf(
6300 "CREATE TABLE %s (\n"
6304 " rootpage integer,\n"
6306 ")", isMaster
? "sqlite_master" : "sqlite_temp_master");
6308 new_colv
[0] = "sql";
6310 callback(&data
, 1, new_argv
, new_colv
);
6311 sqlite3_free(new_argv
[0]);
6315 sqlite3_stmt
*pStmt
= 0;
6316 rc
= sqlite3_prepare_v2(p
->db
, "SELECT name FROM pragma_database_list",
6319 utf8_printf(stderr
, "Error: %s\n", sqlite3_errmsg(p
->db
));
6320 sqlite3_finalize(pStmt
);
6322 goto meta_command_exit
;
6324 appendText(&sSelect
, "SELECT sql FROM", 0);
6326 while( sqlite3_step(pStmt
)==SQLITE_ROW
){
6327 const char *zDb
= (const char*)sqlite3_column_text(pStmt
, 0);
6329 sqlite3_snprintf(sizeof(zScNum
), zScNum
, "%d", ++iSchema
);
6330 appendText(&sSelect
, zDiv
, 0);
6331 zDiv
= " UNION ALL ";
6332 appendText(&sSelect
, "SELECT shell_add_schema(sql,", 0);
6333 if( sqlite3_stricmp(zDb
, "main")!=0 ){
6334 appendText(&sSelect
, zDb
, '"');
6336 appendText(&sSelect
, "NULL", 0);
6338 appendText(&sSelect
, ",name) AS sql, type, tbl_name, name, rowid,", 0);
6339 appendText(&sSelect
, zScNum
, 0);
6340 appendText(&sSelect
, " AS snum, ", 0);
6341 appendText(&sSelect
, zDb
, '\'');
6342 appendText(&sSelect
, " AS sname FROM ", 0);
6343 appendText(&sSelect
, zDb
, '"');
6344 appendText(&sSelect
, ".sqlite_master", 0);
6346 sqlite3_finalize(pStmt
);
6347 #ifdef SQLITE_INTROSPECTION_PRAGMAS
6349 appendText(&sSelect
,
6350 " UNION ALL SELECT shell_module_schema(name),"
6351 " 'table', name, name, name, 9e+99, 'main' FROM pragma_module_list", 0);
6354 appendText(&sSelect
, ") WHERE ", 0);
6356 char *zQarg
= sqlite3_mprintf("%Q", zName
);
6357 if( strchr(zName
, '.') ){
6358 appendText(&sSelect
, "lower(printf('%s.%s',sname,tbl_name))", 0);
6360 appendText(&sSelect
, "lower(tbl_name)", 0);
6362 appendText(&sSelect
, strchr(zName
, '*') ? " GLOB " : " LIKE ", 0);
6363 appendText(&sSelect
, zQarg
, 0);
6364 appendText(&sSelect
, " AND ", 0);
6365 sqlite3_free(zQarg
);
6367 appendText(&sSelect
, "type!='meta' AND sql IS NOT NULL"
6368 " ORDER BY snum, rowid", 0);
6370 utf8_printf(p
->out
, "SQL: %s;\n", sSelect
.z
);
6372 rc
= sqlite3_exec(p
->db
, sSelect
.z
, callback
, &data
, &zErrMsg
);
6377 utf8_printf(stderr
,"Error: %s\n", zErrMsg
);
6378 sqlite3_free(zErrMsg
);
6380 }else if( rc
!= SQLITE_OK
){
6381 raw_printf(stderr
,"Error: querying schema information\n");
6388 #if defined(SQLITE_DEBUG) && defined(SQLITE_ENABLE_SELECTTRACE)
6389 if( c
=='s' && n
==11 && strncmp(azArg
[0], "selecttrace", n
)==0 ){
6390 sqlite3SelectTrace
= (int)integerValue(azArg
[1]);
6394 #if defined(SQLITE_ENABLE_SESSION)
6395 if( c
=='s' && strncmp(azArg
[0],"session",n
)==0 && n
>=3 ){
6396 OpenSession
*pSession
= &p
->aSession
[0];
6397 char **azCmd
= &azArg
[1];
6399 int nCmd
= nArg
- 1;
6401 if( nArg
<=1 ) goto session_syntax_error
;
6404 for(iSes
=0; iSes
<p
->nSession
; iSes
++){
6405 if( strcmp(p
->aSession
[iSes
].zName
, azArg
[1])==0 ) break;
6407 if( iSes
<p
->nSession
){
6408 pSession
= &p
->aSession
[iSes
];
6412 pSession
= &p
->aSession
[0];
6417 /* .session attach TABLE
6418 ** Invoke the sqlite3session_attach() interface to attach a particular
6419 ** table so that it is never filtered.
6421 if( strcmp(azCmd
[0],"attach")==0 ){
6422 if( nCmd
!=2 ) goto session_syntax_error
;
6423 if( pSession
->p
==0 ){
6425 raw_printf(stderr
, "ERROR: No sessions are open\n");
6427 rc
= sqlite3session_attach(pSession
->p
, azCmd
[1]);
6429 raw_printf(stderr
, "ERROR: sqlite3session_attach() returns %d\n", rc
);
6435 /* .session changeset FILE
6436 ** .session patchset FILE
6437 ** Write a changeset or patchset into a file. The file is overwritten.
6439 if( strcmp(azCmd
[0],"changeset")==0 || strcmp(azCmd
[0],"patchset")==0 ){
6441 if( nCmd
!=2 ) goto session_syntax_error
;
6442 if( pSession
->p
==0 ) goto session_not_open
;
6443 out
= fopen(azCmd
[1], "wb");
6445 utf8_printf(stderr
, "ERROR: cannot open \"%s\" for writing\n", azCmd
[1]);
6449 if( azCmd
[0][0]=='c' ){
6450 rc
= sqlite3session_changeset(pSession
->p
, &szChng
, &pChng
);
6452 rc
= sqlite3session_patchset(pSession
->p
, &szChng
, &pChng
);
6455 printf("Error: error code %d\n", rc
);
6459 && fwrite(pChng
, szChng
, 1, out
)!=1 ){
6460 raw_printf(stderr
, "ERROR: Failed to write entire %d-byte output\n",
6463 sqlite3_free(pChng
);
6469 ** Close the identified session
6471 if( strcmp(azCmd
[0], "close")==0 ){
6472 if( nCmd
!=1 ) goto session_syntax_error
;
6474 session_close(pSession
);
6475 p
->aSession
[iSes
] = p
->aSession
[--p
->nSession
];
6479 /* .session enable ?BOOLEAN?
6480 ** Query or set the enable flag
6482 if( strcmp(azCmd
[0], "enable")==0 ){
6484 if( nCmd
>2 ) goto session_syntax_error
;
6485 ii
= nCmd
==1 ? -1 : booleanValue(azCmd
[1]);
6487 ii
= sqlite3session_enable(pSession
->p
, ii
);
6488 utf8_printf(p
->out
, "session %s enable flag = %d\n",
6489 pSession
->zName
, ii
);
6493 /* .session filter GLOB ....
6494 ** Set a list of GLOB patterns of table names to be excluded.
6496 if( strcmp(azCmd
[0], "filter")==0 ){
6498 if( nCmd
<2 ) goto session_syntax_error
;
6500 for(ii
=0; ii
<pSession
->nFilter
; ii
++){
6501 sqlite3_free(pSession
->azFilter
[ii
]);
6503 sqlite3_free(pSession
->azFilter
);
6504 nByte
= sizeof(pSession
->azFilter
[0])*(nCmd
-1);
6505 pSession
->azFilter
= sqlite3_malloc( nByte
);
6506 if( pSession
->azFilter
==0 ){
6507 raw_printf(stderr
, "Error: out or memory\n");
6510 for(ii
=1; ii
<nCmd
; ii
++){
6511 pSession
->azFilter
[ii
-1] = sqlite3_mprintf("%s", azCmd
[ii
]);
6513 pSession
->nFilter
= ii
-1;
6517 /* .session indirect ?BOOLEAN?
6518 ** Query or set the indirect flag
6520 if( strcmp(azCmd
[0], "indirect")==0 ){
6522 if( nCmd
>2 ) goto session_syntax_error
;
6523 ii
= nCmd
==1 ? -1 : booleanValue(azCmd
[1]);
6525 ii
= sqlite3session_indirect(pSession
->p
, ii
);
6526 utf8_printf(p
->out
, "session %s indirect flag = %d\n",
6527 pSession
->zName
, ii
);
6532 ** Determine if the session is empty
6534 if( strcmp(azCmd
[0], "isempty")==0 ){
6536 if( nCmd
!=1 ) goto session_syntax_error
;
6538 ii
= sqlite3session_isempty(pSession
->p
);
6539 utf8_printf(p
->out
, "session %s isempty flag = %d\n",
6540 pSession
->zName
, ii
);
6545 ** List all currently open sessions
6547 if( strcmp(azCmd
[0],"list")==0 ){
6548 for(i
=0; i
<p
->nSession
; i
++){
6549 utf8_printf(p
->out
, "%d %s\n", i
, p
->aSession
[i
].zName
);
6553 /* .session open DB NAME
6554 ** Open a new session called NAME on the attached database DB.
6555 ** DB is normally "main".
6557 if( strcmp(azCmd
[0],"open")==0 ){
6559 if( nCmd
!=3 ) goto session_syntax_error
;
6561 if( zName
[0]==0 ) goto session_syntax_error
;
6562 for(i
=0; i
<p
->nSession
; i
++){
6563 if( strcmp(p
->aSession
[i
].zName
,zName
)==0 ){
6564 utf8_printf(stderr
, "Session \"%s\" already exists\n", zName
);
6565 goto meta_command_exit
;
6568 if( p
->nSession
>=ArraySize(p
->aSession
) ){
6569 raw_printf(stderr
, "Maximum of %d sessions\n", ArraySize(p
->aSession
));
6570 goto meta_command_exit
;
6572 pSession
= &p
->aSession
[p
->nSession
];
6573 rc
= sqlite3session_create(p
->db
, azCmd
[1], &pSession
->p
);
6575 raw_printf(stderr
, "Cannot open session: error code=%d\n", rc
);
6577 goto meta_command_exit
;
6579 pSession
->nFilter
= 0;
6580 sqlite3session_table_filter(pSession
->p
, session_filter
, pSession
);
6582 pSession
->zName
= sqlite3_mprintf("%s", zName
);
6584 /* If no command name matches, show a syntax error */
6585 session_syntax_error
:
6591 /* Undocumented commands for internal testing. Subject to change
6592 ** without notice. */
6593 if( c
=='s' && n
>=10 && strncmp(azArg
[0], "selftest-", 9)==0 ){
6594 if( strncmp(azArg
[0]+9, "boolean", n
-9)==0 ){
6596 for(i
=1; i
<nArg
; i
++){
6597 v
= booleanValue(azArg
[i
]);
6598 utf8_printf(p
->out
, "%s: %d 0x%x\n", azArg
[i
], v
, v
);
6601 if( strncmp(azArg
[0]+9, "integer", n
-9)==0 ){
6602 int i
; sqlite3_int64 v
;
6603 for(i
=1; i
<nArg
; i
++){
6605 v
= integerValue(azArg
[i
]);
6606 sqlite3_snprintf(sizeof(zBuf
),zBuf
,"%s: %lld 0x%llx\n", azArg
[i
],v
,v
);
6607 utf8_printf(p
->out
, "%s", zBuf
);
6613 if( c
=='s' && n
>=4 && strncmp(azArg
[0],"selftest",n
)==0 ){
6614 int bIsInit
= 0; /* True to initialize the SELFTEST table */
6615 int bVerbose
= 0; /* Verbose output */
6616 int bSelftestExists
; /* True if SELFTEST already exists */
6617 int i
, k
; /* Loop counters */
6618 int nTest
= 0; /* Number of tests runs */
6619 int nErr
= 0; /* Number of errors seen */
6620 ShellText str
; /* Answer for a query */
6621 sqlite3_stmt
*pStmt
= 0; /* Query against the SELFTEST table */
6624 for(i
=1; i
<nArg
; i
++){
6625 const char *z
= azArg
[i
];
6626 if( z
[0]=='-' && z
[1]=='-' ) z
++;
6627 if( strcmp(z
,"-init")==0 ){
6630 if( strcmp(z
,"-v")==0 ){
6634 utf8_printf(stderr
, "Unknown option \"%s\" on \"%s\"\n",
6635 azArg
[i
], azArg
[0]);
6636 raw_printf(stderr
, "Should be one of: --init -v\n");
6638 goto meta_command_exit
;
6641 if( sqlite3_table_column_metadata(p
->db
,"main","selftest",0,0,0,0,0,0)
6643 bSelftestExists
= 0;
6645 bSelftestExists
= 1;
6648 createSelftestTable(p
);
6649 bSelftestExists
= 1;
6652 appendText(&str
, "x", 0);
6653 for(k
=bSelftestExists
; k
>=0; k
--){
6655 rc
= sqlite3_prepare_v2(p
->db
,
6656 "SELECT tno,op,cmd,ans FROM selftest ORDER BY tno",
6659 rc
= sqlite3_prepare_v2(p
->db
,
6660 "VALUES(0,'memo','Missing SELFTEST table - default checks only',''),"
6661 " (1,'run','PRAGMA integrity_check','ok')",
6665 raw_printf(stderr
, "Error querying the selftest table\n");
6667 sqlite3_finalize(pStmt
);
6668 goto meta_command_exit
;
6670 for(i
=1; sqlite3_step(pStmt
)==SQLITE_ROW
; i
++){
6671 int tno
= sqlite3_column_int(pStmt
, 0);
6672 const char *zOp
= (const char*)sqlite3_column_text(pStmt
, 1);
6673 const char *zSql
= (const char*)sqlite3_column_text(pStmt
, 2);
6674 const char *zAns
= (const char*)sqlite3_column_text(pStmt
, 3);
6678 char *zQuote
= sqlite3_mprintf("%q", zSql
);
6679 printf("%d: %s %s\n", tno
, zOp
, zSql
);
6680 sqlite3_free(zQuote
);
6682 if( strcmp(zOp
,"memo")==0 ){
6683 utf8_printf(p
->out
, "%s\n", zSql
);
6685 if( strcmp(zOp
,"run")==0 ){
6689 rc
= sqlite3_exec(p
->db
, zSql
, captureOutputCallback
, &str
, &zErrMsg
);
6692 utf8_printf(p
->out
, "Result: %s\n", str
.z
);
6694 if( rc
|| zErrMsg
){
6697 utf8_printf(p
->out
, "%d: error-code-%d: %s\n", tno
, rc
, zErrMsg
);
6698 sqlite3_free(zErrMsg
);
6699 }else if( strcmp(zAns
,str
.z
)!=0 ){
6702 utf8_printf(p
->out
, "%d: Expected: [%s]\n", tno
, zAns
);
6703 utf8_printf(p
->out
, "%d: Got: [%s]\n", tno
, str
.z
);
6708 "Unknown operation \"%s\" on selftest line %d\n", zOp
, tno
);
6712 } /* End loop over rows of content from SELFTEST */
6713 sqlite3_finalize(pStmt
);
6714 } /* End loop over k */
6716 utf8_printf(p
->out
, "%d errors out of %d tests\n", nErr
, nTest
);
6719 if( c
=='s' && strncmp(azArg
[0], "separator", n
)==0 ){
6720 if( nArg
<2 || nArg
>3 ){
6721 raw_printf(stderr
, "Usage: .separator COL ?ROW?\n");
6725 sqlite3_snprintf(sizeof(p
->colSeparator
), p
->colSeparator
,
6726 "%.*s", (int)ArraySize(p
->colSeparator
)-1, azArg
[1]);
6729 sqlite3_snprintf(sizeof(p
->rowSeparator
), p
->rowSeparator
,
6730 "%.*s", (int)ArraySize(p
->rowSeparator
)-1, azArg
[2]);
6734 if( c
=='s' && n
>=4 && strncmp(azArg
[0],"sha3sum",n
)==0 ){
6735 const char *zLike
= 0; /* Which table to checksum. 0 means everything */
6736 int i
; /* Loop counter */
6737 int bSchema
= 0; /* Also hash the schema */
6738 int bSeparate
= 0; /* Hash each table separately */
6739 int iSize
= 224; /* Hash algorithm to use */
6740 int bDebug
= 0; /* Only show the query that would have run */
6741 sqlite3_stmt
*pStmt
; /* For querying tables names */
6742 char *zSql
; /* SQL to be run */
6743 char *zSep
; /* Separator */
6744 ShellText sSql
; /* Complete SQL for the query to run the hash */
6745 ShellText sQuery
; /* Set of queries used to read all content */
6747 for(i
=1; i
<nArg
; i
++){
6748 const char *z
= azArg
[i
];
6751 if( z
[0]=='-' ) z
++;
6752 if( strcmp(z
,"schema")==0 ){
6755 if( strcmp(z
,"sha3-224")==0 || strcmp(z
,"sha3-256")==0
6756 || strcmp(z
,"sha3-384")==0 || strcmp(z
,"sha3-512")==0
6758 iSize
= atoi(&z
[5]);
6760 if( strcmp(z
,"debug")==0 ){
6764 utf8_printf(stderr
, "Unknown option \"%s\" on \"%s\"\n",
6765 azArg
[i
], azArg
[0]);
6766 raw_printf(stderr
, "Should be one of: --schema"
6767 " --sha3-224 --sha3-255 --sha3-384 --sha3-512\n");
6769 goto meta_command_exit
;
6772 raw_printf(stderr
, "Usage: .sha3sum ?OPTIONS? ?LIKE-PATTERN?\n");
6774 goto meta_command_exit
;
6778 if( sqlite3_strlike("sqlite_%", zLike
, 0)==0 ) bSchema
= 1;
6782 zSql
= "SELECT lower(name) FROM sqlite_master"
6783 " WHERE type='table' AND coalesce(rootpage,0)>1"
6784 " UNION ALL SELECT 'sqlite_master'"
6785 " ORDER BY 1 collate nocase";
6787 zSql
= "SELECT lower(name) FROM sqlite_master"
6788 " WHERE type='table' AND coalesce(rootpage,0)>1"
6789 " AND name NOT LIKE 'sqlite_%'"
6790 " ORDER BY 1 collate nocase";
6792 sqlite3_prepare_v2(p
->db
, zSql
, -1, &pStmt
, 0);
6795 appendText(&sSql
, "WITH [sha3sum$query](a,b) AS(",0);
6797 while( SQLITE_ROW
==sqlite3_step(pStmt
) ){
6798 const char *zTab
= (const char*)sqlite3_column_text(pStmt
,0);
6799 if( zLike
&& sqlite3_strlike(zLike
, zTab
, 0)!=0 ) continue;
6800 if( strncmp(zTab
, "sqlite_",7)!=0 ){
6801 appendText(&sQuery
,"SELECT * FROM ", 0);
6802 appendText(&sQuery
,zTab
,'"');
6803 appendText(&sQuery
," NOT INDEXED;", 0);
6804 }else if( strcmp(zTab
, "sqlite_master")==0 ){
6805 appendText(&sQuery
,"SELECT type,name,tbl_name,sql FROM sqlite_master"
6806 " ORDER BY name;", 0);
6807 }else if( strcmp(zTab
, "sqlite_sequence")==0 ){
6808 appendText(&sQuery
,"SELECT name,seq FROM sqlite_sequence"
6809 " ORDER BY name;", 0);
6810 }else if( strcmp(zTab
, "sqlite_stat1")==0 ){
6811 appendText(&sQuery
,"SELECT tbl,idx,stat FROM sqlite_stat1"
6812 " ORDER BY tbl,idx;", 0);
6813 }else if( strcmp(zTab
, "sqlite_stat3")==0
6814 || strcmp(zTab
, "sqlite_stat4")==0 ){
6815 appendText(&sQuery
, "SELECT * FROM ", 0);
6816 appendText(&sQuery
, zTab
, 0);
6817 appendText(&sQuery
, " ORDER BY tbl, idx, rowid;\n", 0);
6819 appendText(&sSql
, zSep
, 0);
6820 appendText(&sSql
, sQuery
.z
, '\'');
6822 appendText(&sSql
, ",", 0);
6823 appendText(&sSql
, zTab
, '\'');
6826 sqlite3_finalize(pStmt
);
6828 zSql
= sqlite3_mprintf(
6830 " SELECT lower(hex(sha3_query(a,%d))) AS hash, b AS label"
6831 " FROM [sha3sum$query]",
6834 zSql
= sqlite3_mprintf(
6836 " SELECT lower(hex(sha3_query(group_concat(a,''),%d))) AS hash"
6837 " FROM [sha3sum$query]",
6843 utf8_printf(p
->out
, "%s\n", zSql
);
6845 shell_exec(p
->db
, zSql
, shell_callback
, p
, 0);
6851 && (strncmp(azArg
[0], "shell", n
)==0 || strncmp(azArg
[0],"system",n
)==0)
6856 raw_printf(stderr
, "Usage: .system COMMAND\n");
6858 goto meta_command_exit
;
6860 zCmd
= sqlite3_mprintf(strchr(azArg
[1],' ')==0?"%s":"\"%s\"", azArg
[1]);
6861 for(i
=2; i
<nArg
; i
++){
6862 zCmd
= sqlite3_mprintf(strchr(azArg
[i
],' ')==0?"%z %s":"%z \"%s\"",
6867 if( x
) raw_printf(stderr
, "System command returns %d\n", x
);
6870 if( c
=='s' && strncmp(azArg
[0], "show", n
)==0 ){
6871 static const char *azBool
[] = { "off", "on", "trigger", "full"};
6874 raw_printf(stderr
, "Usage: .show\n");
6876 goto meta_command_exit
;
6878 utf8_printf(p
->out
, "%12.12s: %s\n","echo",
6879 azBool
[ShellHasFlag(p
, SHFLG_Echo
)]);
6880 utf8_printf(p
->out
, "%12.12s: %s\n","eqp", azBool
[p
->autoEQP
&3]);
6881 utf8_printf(p
->out
, "%12.12s: %s\n","explain",
6882 p
->mode
==MODE_Explain
? "on" : p
->autoExplain
? "auto" : "off");
6883 utf8_printf(p
->out
,"%12.12s: %s\n","headers", azBool
[p
->showHeader
!=0]);
6884 utf8_printf(p
->out
, "%12.12s: %s\n","mode", modeDescr
[p
->mode
]);
6885 utf8_printf(p
->out
, "%12.12s: ", "nullvalue");
6886 output_c_string(p
->out
, p
->nullValue
);
6887 raw_printf(p
->out
, "\n");
6888 utf8_printf(p
->out
,"%12.12s: %s\n","output",
6889 strlen30(p
->outfile
) ? p
->outfile
: "stdout");
6890 utf8_printf(p
->out
,"%12.12s: ", "colseparator");
6891 output_c_string(p
->out
, p
->colSeparator
);
6892 raw_printf(p
->out
, "\n");
6893 utf8_printf(p
->out
,"%12.12s: ", "rowseparator");
6894 output_c_string(p
->out
, p
->rowSeparator
);
6895 raw_printf(p
->out
, "\n");
6896 utf8_printf(p
->out
, "%12.12s: %s\n","stats", azBool
[p
->statsOn
!=0]);
6897 utf8_printf(p
->out
, "%12.12s: ", "width");
6898 for (i
=0;i
<(int)ArraySize(p
->colWidth
) && p
->colWidth
[i
] != 0;i
++) {
6899 raw_printf(p
->out
, "%d ", p
->colWidth
[i
]);
6901 raw_printf(p
->out
, "\n");
6902 utf8_printf(p
->out
, "%12.12s: %s\n", "filename",
6903 p
->zDbFilename
? p
->zDbFilename
: "");
6906 if( c
=='s' && strncmp(azArg
[0], "stats", n
)==0 ){
6908 p
->statsOn
= booleanValue(azArg
[1]);
6909 }else if( nArg
==1 ){
6910 display_stats(p
->db
, p
, 0);
6912 raw_printf(stderr
, "Usage: .stats ?on|off?\n");
6917 if( (c
=='t' && n
>1 && strncmp(azArg
[0], "tables", n
)==0)
6918 || (c
=='i' && (strncmp(azArg
[0], "indices", n
)==0
6919 || strncmp(azArg
[0], "indexes", n
)==0) )
6921 sqlite3_stmt
*pStmt
;
6928 rc
= sqlite3_prepare_v2(p
->db
, "PRAGMA database_list", -1, &pStmt
, 0);
6929 if( rc
) return shellDatabaseError(p
->db
);
6931 if( nArg
>2 && c
=='i' ){
6932 /* It is an historical accident that the .indexes command shows an error
6933 ** when called with the wrong number of arguments whereas the .tables
6934 ** command does not. */
6935 raw_printf(stderr
, "Usage: .indexes ?LIKE-PATTERN?\n");
6937 goto meta_command_exit
;
6939 for(ii
=0; sqlite3_step(pStmt
)==SQLITE_ROW
; ii
++){
6940 const char *zDbName
= (const char*)sqlite3_column_text(pStmt
, 1);
6941 if( zDbName
==0 ) continue;
6942 if( s
.z
&& s
.z
[0] ) appendText(&s
, " UNION ALL ", 0);
6943 if( sqlite3_stricmp(zDbName
, "main")==0 ){
6944 appendText(&s
, "SELECT name FROM ", 0);
6946 appendText(&s
, "SELECT ", 0);
6947 appendText(&s
, zDbName
, '\'');
6948 appendText(&s
, "||'.'||name FROM ", 0);
6950 appendText(&s
, zDbName
, '"');
6951 appendText(&s
, ".sqlite_master ", 0);
6953 appendText(&s
," WHERE type IN ('table','view')"
6954 " AND name NOT LIKE 'sqlite_%'"
6955 " AND name LIKE ?1", 0);
6957 appendText(&s
," WHERE type='index'"
6958 " AND tbl_name LIKE ?1", 0);
6961 rc
= sqlite3_finalize(pStmt
);
6962 appendText(&s
, " ORDER BY 1", 0);
6963 rc
= sqlite3_prepare_v2(p
->db
, s
.z
, -1, &pStmt
, 0);
6965 if( rc
) return shellDatabaseError(p
->db
);
6967 /* Run the SQL statement prepared by the above block. Store the results
6968 ** as an array of nul-terminated strings in azResult[]. */
6972 sqlite3_bind_text(pStmt
, 1, azArg
[1], -1, SQLITE_TRANSIENT
);
6974 sqlite3_bind_text(pStmt
, 1, "%", -1, SQLITE_STATIC
);
6976 while( sqlite3_step(pStmt
)==SQLITE_ROW
){
6979 int n2
= nAlloc
*2 + 10;
6980 azNew
= sqlite3_realloc64(azResult
, sizeof(azResult
[0])*n2
);
6982 rc
= shellNomemError();
6988 azResult
[nRow
] = sqlite3_mprintf("%s", sqlite3_column_text(pStmt
, 0));
6989 if( 0==azResult
[nRow
] ){
6990 rc
= shellNomemError();
6995 if( sqlite3_finalize(pStmt
)!=SQLITE_OK
){
6996 rc
= shellDatabaseError(p
->db
);
6999 /* Pretty-print the contents of array azResult[] to the output */
7000 if( rc
==0 && nRow
>0 ){
7001 int len
, maxlen
= 0;
7003 int nPrintCol
, nPrintRow
;
7004 for(i
=0; i
<nRow
; i
++){
7005 len
= strlen30(azResult
[i
]);
7006 if( len
>maxlen
) maxlen
= len
;
7008 nPrintCol
= 80/(maxlen
+2);
7009 if( nPrintCol
<1 ) nPrintCol
= 1;
7010 nPrintRow
= (nRow
+ nPrintCol
- 1)/nPrintCol
;
7011 for(i
=0; i
<nPrintRow
; i
++){
7012 for(j
=i
; j
<nRow
; j
+=nPrintRow
){
7013 char *zSp
= j
<nPrintRow
? "" : " ";
7014 utf8_printf(p
->out
, "%s%-*s", zSp
, maxlen
,
7015 azResult
[j
] ? azResult
[j
]:"");
7017 raw_printf(p
->out
, "\n");
7021 for(ii
=0; ii
<nRow
; ii
++) sqlite3_free(azResult
[ii
]);
7022 sqlite3_free(azResult
);
7025 /* Begin redirecting output to the file "testcase-out.txt" */
7026 if( c
=='t' && strcmp(azArg
[0],"testcase")==0 ){
7028 p
->out
= output_file_open("testcase-out.txt");
7030 raw_printf(stderr
, "Error: cannot open 'testcase-out.txt'\n");
7033 sqlite3_snprintf(sizeof(p
->zTestcase
), p
->zTestcase
, "%s", azArg
[1]);
7035 sqlite3_snprintf(sizeof(p
->zTestcase
), p
->zTestcase
, "?");
7039 #ifndef SQLITE_UNTESTABLE
7040 if( c
=='t' && n
>=8 && strncmp(azArg
[0], "testctrl", n
)==0 ){
7041 static const struct {
7042 const char *zCtrlName
; /* Name of a test-control option */
7043 int ctrlCode
; /* Integer code for that option */
7044 const char *zUsage
; /* Usage notes */
7046 { "always", SQLITE_TESTCTRL_ALWAYS
, "BOOLEAN" },
7047 { "assert", SQLITE_TESTCTRL_ASSERT
, "BOOLEAN" },
7048 /*{ "benign_malloc_hooks",SQLITE_TESTCTRL_BENIGN_MALLOC_HOOKS, "" },*/
7049 /*{ "bitvec_test", SQLITE_TESTCTRL_BITVEC_TEST, "" },*/
7050 { "byteorder", SQLITE_TESTCTRL_BYTEORDER
, "" },
7051 /*{ "fault_install", SQLITE_TESTCTRL_FAULT_INSTALL, "" }, */
7052 { "imposter", SQLITE_TESTCTRL_IMPOSTER
, "SCHEMA ON/OFF ROOTPAGE"},
7053 #ifdef SQLITE_N_KEYWORD
7054 { "iskeyword", SQLITE_TESTCTRL_ISKEYWORD
, "IDENTIFIER" },
7056 { "localtime_fault", SQLITE_TESTCTRL_LOCALTIME_FAULT
,"BOOLEAN" },
7057 { "never_corrupt", SQLITE_TESTCTRL_NEVER_CORRUPT
, "BOOLEAN" },
7058 { "optimizations", SQLITE_TESTCTRL_OPTIMIZATIONS
, "DISABLE-MASK" },
7060 { "parser_coverage", SQLITE_TESTCTRL_PARSER_COVERAGE
, "" },
7062 { "pending_byte", SQLITE_TESTCTRL_PENDING_BYTE
, "OFFSET " },
7063 { "prng_reset", SQLITE_TESTCTRL_PRNG_RESET
, "" },
7064 { "prng_restore", SQLITE_TESTCTRL_PRNG_RESTORE
, "" },
7065 { "prng_save", SQLITE_TESTCTRL_PRNG_SAVE
, "" },
7066 { "reserve", SQLITE_TESTCTRL_RESERVE
, "BYTES-OF-RESERVE" },
7070 int rc2
= 0; /* 0: usage. 1: %d 2: %x 3: no-output */
7073 const char *zCmd
= 0;
7076 zCmd
= nArg
>=2 ? azArg
[1] : "help";
7078 /* The argument can optionally begin with "-" or "--" */
7079 if( zCmd
[0]=='-' && zCmd
[1] ){
7081 if( zCmd
[0]=='-' && zCmd
[1] ) zCmd
++;
7084 /* --help lists all test-controls */
7085 if( strcmp(zCmd
,"help")==0 ){
7086 utf8_printf(p
->out
, "Available test-controls:\n");
7087 for(i
=0; i
<ArraySize(aCtrl
); i
++){
7088 utf8_printf(p
->out
, " .testctrl %s %s\n",
7089 aCtrl
[i
].zCtrlName
, aCtrl
[i
].zUsage
);
7092 goto meta_command_exit
;
7095 /* convert testctrl text option to value. allow any unique prefix
7096 ** of the option name, or a numerical value. */
7097 n2
= strlen30(zCmd
);
7098 for(i
=0; i
<ArraySize(aCtrl
); i
++){
7099 if( strncmp(zCmd
, aCtrl
[i
].zCtrlName
, n2
)==0 ){
7101 testctrl
= aCtrl
[i
].ctrlCode
;
7104 utf8_printf(stderr
, "Error: ambiguous test-control: \"%s\"\n"
7105 "Use \".testctrl --help\" for help\n", zCmd
);
7107 goto meta_command_exit
;
7112 utf8_printf(stderr
,"Error: unknown test-control: %s\n"
7113 "Use \".testctrl --help\" for help\n", zCmd
);
7117 /* sqlite3_test_control(int, db, int) */
7118 case SQLITE_TESTCTRL_OPTIMIZATIONS
:
7119 case SQLITE_TESTCTRL_RESERVE
:
7121 int opt
= (int)strtol(azArg
[2], 0, 0);
7122 rc2
= sqlite3_test_control(testctrl
, p
->db
, opt
);
7127 /* sqlite3_test_control(int) */
7128 case SQLITE_TESTCTRL_PRNG_SAVE
:
7129 case SQLITE_TESTCTRL_PRNG_RESTORE
:
7130 case SQLITE_TESTCTRL_PRNG_RESET
:
7131 case SQLITE_TESTCTRL_BYTEORDER
:
7133 rc2
= sqlite3_test_control(testctrl
);
7134 isOk
= testctrl
==SQLITE_TESTCTRL_BYTEORDER
? 1 : 3;
7138 /* sqlite3_test_control(int, uint) */
7139 case SQLITE_TESTCTRL_PENDING_BYTE
:
7141 unsigned int opt
= (unsigned int)integerValue(azArg
[2]);
7142 rc2
= sqlite3_test_control(testctrl
, opt
);
7147 /* sqlite3_test_control(int, int) */
7148 case SQLITE_TESTCTRL_ASSERT
:
7149 case SQLITE_TESTCTRL_ALWAYS
:
7151 int opt
= booleanValue(azArg
[2]);
7152 rc2
= sqlite3_test_control(testctrl
, opt
);
7157 /* sqlite3_test_control(int, int) */
7158 case SQLITE_TESTCTRL_LOCALTIME_FAULT
:
7159 case SQLITE_TESTCTRL_NEVER_CORRUPT
:
7161 int opt
= booleanValue(azArg
[2]);
7162 rc2
= sqlite3_test_control(testctrl
, opt
);
7167 /* sqlite3_test_control(int, char *) */
7168 #ifdef SQLITE_N_KEYWORD
7169 case SQLITE_TESTCTRL_ISKEYWORD
:
7171 const char *opt
= azArg
[2];
7172 rc2
= sqlite3_test_control(testctrl
, opt
);
7178 case SQLITE_TESTCTRL_IMPOSTER
:
7180 rc2
= sqlite3_test_control(testctrl
, p
->db
,
7182 integerValue(azArg
[3]),
7183 integerValue(azArg
[4]));
7189 case SQLITE_TESTCTRL_PARSER_COVERAGE
:
7191 sqlite3_test_control(testctrl
, p
->out
);
7197 if( isOk
==0 && iCtrl
>=0 ){
7198 utf8_printf(p
->out
, "Usage: .testctrl %s %s\n", zCmd
, aCtrl
[iCtrl
].zUsage
);
7200 }else if( isOk
==1 ){
7201 raw_printf(p
->out
, "%d\n", rc2
);
7202 }else if( isOk
==2 ){
7203 raw_printf(p
->out
, "0x%08x\n", rc2
);
7206 #endif /* !defined(SQLITE_UNTESTABLE) */
7208 if( c
=='t' && n
>4 && strncmp(azArg
[0], "timeout", n
)==0 ){
7210 sqlite3_busy_timeout(p
->db
, nArg
>=2 ? (int)integerValue(azArg
[1]) : 0);
7213 if( c
=='t' && n
>=5 && strncmp(azArg
[0], "timer", n
)==0 ){
7215 enableTimer
= booleanValue(azArg
[1]);
7216 if( enableTimer
&& !HAS_TIMER
){
7217 raw_printf(stderr
, "Error: timer not available on this system.\n");
7221 raw_printf(stderr
, "Usage: .timer on|off\n");
7226 if( c
=='t' && strncmp(azArg
[0], "trace", n
)==0 ){
7229 raw_printf(stderr
, "Usage: .trace FILE|off\n");
7231 goto meta_command_exit
;
7233 output_file_close(p
->traceOut
);
7234 p
->traceOut
= output_file_open(azArg
[1]);
7235 #if !defined(SQLITE_OMIT_TRACE) && !defined(SQLITE_OMIT_FLOATING_POINT)
7236 if( p
->traceOut
==0 ){
7237 sqlite3_trace_v2(p
->db
, 0, 0, 0);
7239 sqlite3_trace_v2(p
->db
, SQLITE_TRACE_STMT
, sql_trace_callback
,p
->traceOut
);
7244 #if SQLITE_USER_AUTHENTICATION
7245 if( c
=='u' && strncmp(azArg
[0], "user", n
)==0 ){
7247 raw_printf(stderr
, "Usage: .user SUBCOMMAND ...\n");
7249 goto meta_command_exit
;
7252 if( strcmp(azArg
[1],"login")==0 ){
7254 raw_printf(stderr
, "Usage: .user login USER PASSWORD\n");
7256 goto meta_command_exit
;
7258 rc
= sqlite3_user_authenticate(p
->db
, azArg
[2], azArg
[3], strlen30(azArg
[3]));
7260 utf8_printf(stderr
, "Authentication failed for user %s\n", azArg
[2]);
7263 }else if( strcmp(azArg
[1],"add")==0 ){
7265 raw_printf(stderr
, "Usage: .user add USER PASSWORD ISADMIN\n");
7267 goto meta_command_exit
;
7269 rc
= sqlite3_user_add(p
->db
, azArg
[2], azArg
[3], strlen30(azArg
[3]),
7270 booleanValue(azArg
[4]));
7272 raw_printf(stderr
, "User-Add failed: %d\n", rc
);
7275 }else if( strcmp(azArg
[1],"edit")==0 ){
7277 raw_printf(stderr
, "Usage: .user edit USER PASSWORD ISADMIN\n");
7279 goto meta_command_exit
;
7281 rc
= sqlite3_user_change(p
->db
, azArg
[2], azArg
[3], strlen30(azArg
[3]),
7282 booleanValue(azArg
[4]));
7284 raw_printf(stderr
, "User-Edit failed: %d\n", rc
);
7287 }else if( strcmp(azArg
[1],"delete")==0 ){
7289 raw_printf(stderr
, "Usage: .user delete USER\n");
7291 goto meta_command_exit
;
7293 rc
= sqlite3_user_delete(p
->db
, azArg
[2]);
7295 raw_printf(stderr
, "User-Delete failed: %d\n", rc
);
7299 raw_printf(stderr
, "Usage: .user login|add|edit|delete ...\n");
7301 goto meta_command_exit
;
7304 #endif /* SQLITE_USER_AUTHENTICATION */
7306 if( c
=='v' && strncmp(azArg
[0], "version", n
)==0 ){
7307 utf8_printf(p
->out
, "SQLite %s %s\n" /*extra-version-info*/,
7308 sqlite3_libversion(), sqlite3_sourceid());
7311 if( c
=='v' && strncmp(azArg
[0], "vfsinfo", n
)==0 ){
7312 const char *zDbName
= nArg
==2 ? azArg
[1] : "main";
7313 sqlite3_vfs
*pVfs
= 0;
7315 sqlite3_file_control(p
->db
, zDbName
, SQLITE_FCNTL_VFS_POINTER
, &pVfs
);
7317 utf8_printf(p
->out
, "vfs.zName = \"%s\"\n", pVfs
->zName
);
7318 raw_printf(p
->out
, "vfs.iVersion = %d\n", pVfs
->iVersion
);
7319 raw_printf(p
->out
, "vfs.szOsFile = %d\n", pVfs
->szOsFile
);
7320 raw_printf(p
->out
, "vfs.mxPathname = %d\n", pVfs
->mxPathname
);
7325 if( c
=='v' && strncmp(azArg
[0], "vfslist", n
)==0 ){
7327 sqlite3_vfs
*pCurrent
= 0;
7329 sqlite3_file_control(p
->db
, "main", SQLITE_FCNTL_VFS_POINTER
, &pCurrent
);
7331 for(pVfs
=sqlite3_vfs_find(0); pVfs
; pVfs
=pVfs
->pNext
){
7332 utf8_printf(p
->out
, "vfs.zName = \"%s\"%s\n", pVfs
->zName
,
7333 pVfs
==pCurrent
? " <--- CURRENT" : "");
7334 raw_printf(p
->out
, "vfs.iVersion = %d\n", pVfs
->iVersion
);
7335 raw_printf(p
->out
, "vfs.szOsFile = %d\n", pVfs
->szOsFile
);
7336 raw_printf(p
->out
, "vfs.mxPathname = %d\n", pVfs
->mxPathname
);
7338 raw_printf(p
->out
, "-----------------------------------\n");
7343 if( c
=='v' && strncmp(azArg
[0], "vfsname", n
)==0 ){
7344 const char *zDbName
= nArg
==2 ? azArg
[1] : "main";
7347 sqlite3_file_control(p
->db
, zDbName
, SQLITE_FCNTL_VFSNAME
, &zVfsName
);
7349 utf8_printf(p
->out
, "%s\n", zVfsName
);
7350 sqlite3_free(zVfsName
);
7355 #if defined(SQLITE_DEBUG) && defined(SQLITE_ENABLE_WHERETRACE)
7356 if( c
=='w' && strncmp(azArg
[0], "wheretrace", n
)==0 ){
7357 sqlite3WhereTrace
= nArg
>=2 ? booleanValue(azArg
[1]) : 0xff;
7361 if( c
=='w' && strncmp(azArg
[0], "width", n
)==0 ){
7363 assert( nArg
<=ArraySize(azArg
) );
7364 for(j
=1; j
<nArg
&& j
<ArraySize(p
->colWidth
); j
++){
7365 p
->colWidth
[j
-1] = (int)integerValue(azArg
[j
]);
7370 utf8_printf(stderr
, "Error: unknown command or invalid arguments: "
7371 " \"%s\". Enter \".help\" for help\n", azArg
[0]);
7378 if( p
->outCount
==0 ) output_reset(p
);
7384 ** Return TRUE if a semicolon occurs anywhere in the first N characters
7387 static int line_contains_semicolon(const char *z
, int N
){
7389 for(i
=0; i
<N
; i
++){ if( z
[i
]==';' ) return 1; }
7394 ** Test to see if a line consists entirely of whitespace.
7396 static int _all_whitespace(const char *z
){
7398 if( IsSpace(z
[0]) ) continue;
7399 if( *z
=='/' && z
[1]=='*' ){
7401 while( *z
&& (*z
!='*' || z
[1]!='/') ){ z
++; }
7402 if( *z
==0 ) return 0;
7406 if( *z
=='-' && z
[1]=='-' ){
7408 while( *z
&& *z
!='\n' ){ z
++; }
7409 if( *z
==0 ) return 1;
7418 ** Return TRUE if the line typed in is an SQL command terminator other
7419 ** than a semi-colon. The SQL Server style "go" command is understood
7420 ** as is the Oracle "/".
7422 static int line_is_command_terminator(const char *zLine
){
7423 while( IsSpace(zLine
[0]) ){ zLine
++; };
7424 if( zLine
[0]=='/' && _all_whitespace(&zLine
[1]) ){
7425 return 1; /* Oracle */
7427 if( ToLower(zLine
[0])=='g' && ToLower(zLine
[1])=='o'
7428 && _all_whitespace(&zLine
[2]) ){
7429 return 1; /* SQL Server */
7435 ** Return true if zSql is a complete SQL statement. Return false if it
7436 ** ends in the middle of a string literal or C-style comment.
7438 static int line_is_complete(char *zSql
, int nSql
){
7440 if( zSql
==0 ) return 1;
7443 rc
= sqlite3_complete(zSql
);
7449 ** Run a single line of SQL
7451 static int runOneSqlLine(ShellState
*p
, char *zSql
, FILE *in
, int startline
){
7456 if( ShellHasFlag(p
,SHFLG_Backslash
) ) resolve_backslashes(zSql
);
7458 rc
= shell_exec(p
->db
, zSql
, shell_callback
, p
, &zErrMsg
);
7460 if( rc
|| zErrMsg
){
7462 if( in
!=0 || !stdin_is_interactive
){
7463 sqlite3_snprintf(sizeof(zPrefix
), zPrefix
,
7464 "Error: near line %d:", startline
);
7466 sqlite3_snprintf(sizeof(zPrefix
), zPrefix
, "Error:");
7469 utf8_printf(stderr
, "%s %s\n", zPrefix
, zErrMsg
);
7470 sqlite3_free(zErrMsg
);
7473 utf8_printf(stderr
, "%s %s\n", zPrefix
, sqlite3_errmsg(p
->db
));
7476 }else if( ShellHasFlag(p
, SHFLG_CountChanges
) ){
7477 raw_printf(p
->out
, "changes: %3d total_changes: %d\n",
7478 sqlite3_changes(p
->db
), sqlite3_total_changes(p
->db
));
7485 ** Read input from *in and process it. If *in==0 then input
7486 ** is interactive - the user is typing it it. Otherwise, input
7487 ** is coming from a file or device. A prompt is issued and history
7488 ** is saved only if input is interactive. An interrupt signal will
7489 ** cause this routine to exit immediately, unless input is interactive.
7491 ** Return the number of errors.
7493 static int process_input(ShellState
*p
, FILE *in
){
7494 char *zLine
= 0; /* A single input line */
7495 char *zSql
= 0; /* Accumulated SQL text */
7496 int nLine
; /* Length of current line */
7497 int nSql
= 0; /* Bytes of zSql[] used */
7498 int nAlloc
= 0; /* Allocated zSql[] space */
7499 int nSqlPrior
= 0; /* Bytes of zSql[] used by prior line */
7500 int rc
; /* Error code */
7501 int errCnt
= 0; /* Number of errors seen */
7502 int lineno
= 0; /* Current line number */
7503 int startline
= 0; /* Line number for start of current input */
7505 while( errCnt
==0 || !bail_on_error
|| (in
==0 && stdin_is_interactive
) ){
7507 zLine
= one_input_line(in
, zLine
, nSql
>0);
7510 if( in
==0 && stdin_is_interactive
) printf("\n");
7513 if( seenInterrupt
){
7518 if( nSql
==0 && _all_whitespace(zLine
) ){
7519 if( ShellHasFlag(p
, SHFLG_Echo
) ) printf("%s\n", zLine
);
7522 if( zLine
&& zLine
[0]=='.' && nSql
==0 ){
7523 if( ShellHasFlag(p
, SHFLG_Echo
) ) printf("%s\n", zLine
);
7524 rc
= do_meta_command(zLine
, p
);
7525 if( rc
==2 ){ /* exit requested */
7532 if( line_is_command_terminator(zLine
) && line_is_complete(zSql
, nSql
) ){
7533 memcpy(zLine
,";",2);
7535 nLine
= strlen30(zLine
);
7536 if( nSql
+nLine
+2>=nAlloc
){
7537 nAlloc
= nSql
+nLine
+100;
7538 zSql
= realloc(zSql
, nAlloc
);
7540 raw_printf(stderr
, "Error: out of memory\n");
7547 for(i
=0; zLine
[i
] && IsSpace(zLine
[i
]); i
++){}
7548 assert( nAlloc
>0 && zSql
!=0 );
7549 memcpy(zSql
, zLine
+i
, nLine
+1-i
);
7553 zSql
[nSql
++] = '\n';
7554 memcpy(zSql
+nSql
, zLine
, nLine
+1);
7557 if( nSql
&& line_contains_semicolon(&zSql
[nSqlPrior
], nSql
-nSqlPrior
)
7558 && sqlite3_complete(zSql
) ){
7559 errCnt
+= runOneSqlLine(p
, zSql
, in
, startline
);
7565 }else if( nSql
&& _all_whitespace(zSql
) ){
7566 if( ShellHasFlag(p
, SHFLG_Echo
) ) printf("%s\n", zSql
);
7570 if( nSql
&& !_all_whitespace(zSql
) ){
7571 runOneSqlLine(p
, zSql
, in
, startline
);
7579 ** Return a pathname which is the user's home directory. A
7580 ** 0 return indicates an error of some kind.
7582 static char *find_home_dir(int clearFlag
){
7583 static char *home_dir
= NULL
;
7589 if( home_dir
) return home_dir
;
7591 #if !defined(_WIN32) && !defined(WIN32) && !defined(_WIN32_WCE) \
7592 && !defined(__RTP__) && !defined(_WRS_KERNEL)
7594 struct passwd
*pwent
;
7595 uid_t uid
= getuid();
7596 if( (pwent
=getpwuid(uid
)) != NULL
) {
7597 home_dir
= pwent
->pw_dir
;
7602 #if defined(_WIN32_WCE)
7603 /* Windows CE (arm-wince-mingw32ce-gcc) does not provide getenv()
7608 #if defined(_WIN32) || defined(WIN32)
7610 home_dir
= getenv("USERPROFILE");
7615 home_dir
= getenv("HOME");
7618 #if defined(_WIN32) || defined(WIN32)
7620 char *zDrive
, *zPath
;
7622 zDrive
= getenv("HOMEDRIVE");
7623 zPath
= getenv("HOMEPATH");
7624 if( zDrive
&& zPath
){
7625 n
= strlen30(zDrive
) + strlen30(zPath
) + 1;
7626 home_dir
= malloc( n
);
7627 if( home_dir
==0 ) return 0;
7628 sqlite3_snprintf(n
, home_dir
, "%s%s", zDrive
, zPath
);
7635 #endif /* !_WIN32_WCE */
7638 int n
= strlen30(home_dir
) + 1;
7639 char *z
= malloc( n
);
7640 if( z
) memcpy(z
, home_dir
, n
);
7648 ** Read input from the file given by sqliterc_override. Or if that
7649 ** parameter is NULL, take input from ~/.sqliterc
7651 ** Returns the number of errors.
7653 static void process_sqliterc(
7654 ShellState
*p
, /* Configuration data */
7655 const char *sqliterc_override
/* Name of config file. NULL to use default */
7657 char *home_dir
= NULL
;
7658 const char *sqliterc
= sqliterc_override
;
7662 if (sqliterc
== NULL
) {
7663 home_dir
= find_home_dir(0);
7665 raw_printf(stderr
, "-- warning: cannot find home directory;"
7666 " cannot read ~/.sqliterc\n");
7669 sqlite3_initialize();
7670 zBuf
= sqlite3_mprintf("%s/.sqliterc",home_dir
);
7673 in
= fopen(sqliterc
,"rb");
7675 if( stdin_is_interactive
){
7676 utf8_printf(stderr
,"-- Loading resources from %s\n",sqliterc
);
7678 process_input(p
,in
);
7685 ** Show available command line options
7687 static const char zOptions
[] =
7688 " -ascii set output mode to 'ascii'\n"
7689 " -bail stop after hitting an error\n"
7690 " -batch force batch I/O\n"
7691 " -column set output mode to 'column'\n"
7692 " -cmd COMMAND run \"COMMAND\" before reading stdin\n"
7693 " -csv set output mode to 'csv'\n"
7694 " -echo print commands before execution\n"
7695 " -init FILENAME read/process named file\n"
7696 " -[no]header turn headers on or off\n"
7697 #if defined(SQLITE_ENABLE_MEMSYS3) || defined(SQLITE_ENABLE_MEMSYS5)
7698 " -heap SIZE Size of heap for memsys3 or memsys5\n"
7700 " -help show this message\n"
7701 " -html set output mode to HTML\n"
7702 " -interactive force interactive I/O\n"
7703 " -line set output mode to 'line'\n"
7704 " -list set output mode to 'list'\n"
7705 " -lookaside SIZE N use N entries of SZ bytes for lookaside memory\n"
7706 " -mmap N default mmap size set to N\n"
7707 #ifdef SQLITE_ENABLE_MULTIPLEX
7708 " -multiplex enable the multiplexor VFS\n"
7710 " -newline SEP set output row separator. Default: '\\n'\n"
7711 " -nullvalue TEXT set text string for NULL values. Default ''\n"
7712 " -pagecache SIZE N use N slots of SZ bytes each for page cache memory\n"
7713 " -quote set output mode to 'quote'\n"
7714 " -separator SEP set output column separator. Default: '|'\n"
7715 " -stats print memory stats before each finalize\n"
7716 " -version show SQLite version\n"
7717 " -vfs NAME use NAME as the default VFS\n"
7718 #ifdef SQLITE_ENABLE_VFSTRACE
7719 " -vfstrace enable tracing of all VFS calls\n"
7722 static void usage(int showDetail
){
7724 "Usage: %s [OPTIONS] FILENAME [SQL]\n"
7725 "FILENAME is the name of an SQLite database. A new database is created\n"
7726 "if the file does not previously exist.\n", Argv0
);
7728 utf8_printf(stderr
, "OPTIONS include:\n%s", zOptions
);
7730 raw_printf(stderr
, "Use the -help option for additional information\n");
7736 ** Initialize the state information in data
7738 static void main_init(ShellState
*data
) {
7739 memset(data
, 0, sizeof(*data
));
7740 data
->normalMode
= data
->cMode
= data
->mode
= MODE_List
;
7741 data
->autoExplain
= 1;
7742 memcpy(data
->colSeparator
,SEP_Column
, 2);
7743 memcpy(data
->rowSeparator
,SEP_Row
, 2);
7744 data
->showHeader
= 0;
7745 data
->shellFlgs
= SHFLG_Lookaside
;
7746 sqlite3_config(SQLITE_CONFIG_URI
, 1);
7747 sqlite3_config(SQLITE_CONFIG_LOG
, shellLog
, data
);
7748 sqlite3_config(SQLITE_CONFIG_MULTITHREAD
);
7749 sqlite3_snprintf(sizeof(mainPrompt
), mainPrompt
,"sqlite> ");
7750 sqlite3_snprintf(sizeof(continuePrompt
), continuePrompt
," ...> ");
7754 ** Output text to the console in a font that attracts extra attention.
7757 static void printBold(const char *zText
){
7758 HANDLE out
= GetStdHandle(STD_OUTPUT_HANDLE
);
7759 CONSOLE_SCREEN_BUFFER_INFO defaultScreenInfo
;
7760 GetConsoleScreenBufferInfo(out
, &defaultScreenInfo
);
7761 SetConsoleTextAttribute(out
,
7762 FOREGROUND_RED
|FOREGROUND_INTENSITY
7764 printf("%s", zText
);
7765 SetConsoleTextAttribute(out
, defaultScreenInfo
.wAttributes
);
7768 static void printBold(const char *zText
){
7769 printf("\033[1m%s\033[0m", zText
);
7774 ** Get the argument to an --option. Throw an error and die if no argument
7777 static char *cmdline_option_value(int argc
, char **argv
, int i
){
7779 utf8_printf(stderr
, "%s: Error: missing argument to %s\n",
7780 argv
[0], argv
[argc
-1]);
7786 #ifndef SQLITE_SHELL_IS_UTF8
7787 # if (defined(_WIN32) || defined(WIN32)) && defined(_MSC_VER)
7788 # define SQLITE_SHELL_IS_UTF8 (0)
7790 # define SQLITE_SHELL_IS_UTF8 (1)
7794 #if SQLITE_SHELL_IS_UTF8
7795 int SQLITE_CDECL
main(int argc
, char **argv
){
7797 int SQLITE_CDECL
wmain(int argc
, wchar_t **wargv
){
7802 const char *zInitFile
= 0;
7805 int warnInmemoryDb
= 0;
7810 setBinaryMode(stdin
, 0);
7811 setvbuf(stderr
, 0, _IONBF
, 0); /* Make sure stderr is unbuffered */
7812 stdin_is_interactive
= isatty(0);
7813 stdout_is_console
= isatty(1);
7815 #if USE_SYSTEM_SQLITE+0!=1
7816 if( strncmp(sqlite3_sourceid(),SQLITE_SOURCE_ID
,60)!=0 ){
7817 utf8_printf(stderr
, "SQLite header and source version mismatch\n%s\n%s\n",
7818 sqlite3_sourceid(), SQLITE_SOURCE_ID
);
7823 #if !SQLITE_SHELL_IS_UTF8
7824 sqlite3_initialize();
7825 argv
= sqlite3_malloc64(sizeof(argv
[0])*argc
);
7827 raw_printf(stderr
, "out of memory\n");
7830 for(i
=0; i
<argc
; i
++){
7831 argv
[i
] = sqlite3_win32_unicode_to_utf8(wargv
[i
]);
7833 raw_printf(stderr
, "out of memory\n");
7838 assert( argc
>=1 && argv
&& argv
[0] );
7841 /* Make sure we have a valid signal handler early, before anything
7845 signal(SIGINT
, interrupt_handler
);
7846 #elif (defined(_WIN32) || defined(WIN32)) && !defined(_WIN32_WCE)
7847 SetConsoleCtrlHandler(ConsoleCtrlHandler
, TRUE
);
7850 #ifdef SQLITE_SHELL_DBNAME_PROC
7852 /* If the SQLITE_SHELL_DBNAME_PROC macro is defined, then it is the name
7853 ** of a C-function that will provide the name of the database file. Use
7854 ** this compile-time option to embed this shell program in larger
7856 extern void SQLITE_SHELL_DBNAME_PROC(const char**);
7857 SQLITE_SHELL_DBNAME_PROC(&data
.zDbFilename
);
7862 /* Do an initial pass through the command-line argument to locate
7863 ** the name of the database file, the name of the initialization file,
7864 ** the size of the alternative malloc heap,
7865 ** and the first command to execute.
7867 for(i
=1; i
<argc
; i
++){
7871 if( data
.zDbFilename
==0 ){
7872 data
.zDbFilename
= z
;
7874 /* Excesss arguments are interpreted as SQL (or dot-commands) and
7875 ** mean that nothing is read from stdin */
7878 azCmd
= realloc(azCmd
, sizeof(azCmd
[0])*nCmd
);
7880 raw_printf(stderr
, "out of memory\n");
7886 if( z
[1]=='-' ) z
++;
7887 if( strcmp(z
,"-separator")==0
7888 || strcmp(z
,"-nullvalue")==0
7889 || strcmp(z
,"-newline")==0
7890 || strcmp(z
,"-cmd")==0
7892 (void)cmdline_option_value(argc
, argv
, ++i
);
7893 }else if( strcmp(z
,"-init")==0 ){
7894 zInitFile
= cmdline_option_value(argc
, argv
, ++i
);
7895 }else if( strcmp(z
,"-batch")==0 ){
7896 /* Need to check for batch mode here to so we can avoid printing
7897 ** informational messages (like from process_sqliterc) before
7898 ** we do the actual processing of arguments later in a second pass.
7900 stdin_is_interactive
= 0;
7901 }else if( strcmp(z
,"-heap")==0 ){
7902 #if defined(SQLITE_ENABLE_MEMSYS3) || defined(SQLITE_ENABLE_MEMSYS5)
7904 sqlite3_int64 szHeap
;
7906 zSize
= cmdline_option_value(argc
, argv
, ++i
);
7907 szHeap
= integerValue(zSize
);
7908 if( szHeap
>0x7fff0000 ) szHeap
= 0x7fff0000;
7909 sqlite3_config(SQLITE_CONFIG_HEAP
, malloc((int)szHeap
), (int)szHeap
, 64);
7911 (void)cmdline_option_value(argc
, argv
, ++i
);
7913 }else if( strcmp(z
,"-pagecache")==0 ){
7915 sz
= (int)integerValue(cmdline_option_value(argc
,argv
,++i
));
7916 if( sz
>70000 ) sz
= 70000;
7918 n
= (int)integerValue(cmdline_option_value(argc
,argv
,++i
));
7919 sqlite3_config(SQLITE_CONFIG_PAGECACHE
,
7920 (n
>0 && sz
>0) ? malloc(n
*sz
) : 0, sz
, n
);
7921 data
.shellFlgs
|= SHFLG_Pagecache
;
7922 }else if( strcmp(z
,"-lookaside")==0 ){
7924 sz
= (int)integerValue(cmdline_option_value(argc
,argv
,++i
));
7926 n
= (int)integerValue(cmdline_option_value(argc
,argv
,++i
));
7928 sqlite3_config(SQLITE_CONFIG_LOOKASIDE
, sz
, n
);
7929 if( sz
*n
==0 ) data
.shellFlgs
&= ~SHFLG_Lookaside
;
7930 #ifdef SQLITE_ENABLE_VFSTRACE
7931 }else if( strcmp(z
,"-vfstrace")==0 ){
7932 extern int vfstrace_register(
7933 const char *zTraceName
,
7934 const char *zOldVfsName
,
7935 int (*xOut
)(const char*,void*),
7939 vfstrace_register("trace",0,(int(*)(const char*,void*))fputs
,stderr
,1);
7941 #ifdef SQLITE_ENABLE_MULTIPLEX
7942 }else if( strcmp(z
,"-multiplex")==0 ){
7943 extern int sqlite3_multiple_initialize(const char*,int);
7944 sqlite3_multiplex_initialize(0, 1);
7946 }else if( strcmp(z
,"-mmap")==0 ){
7947 sqlite3_int64 sz
= integerValue(cmdline_option_value(argc
,argv
,++i
));
7948 sqlite3_config(SQLITE_CONFIG_MMAP_SIZE
, sz
, sz
);
7949 }else if( strcmp(z
,"-vfs")==0 ){
7950 sqlite3_vfs
*pVfs
= sqlite3_vfs_find(cmdline_option_value(argc
,argv
,++i
));
7952 sqlite3_vfs_register(pVfs
, 1);
7954 utf8_printf(stderr
, "no such VFS: \"%s\"\n", argv
[i
]);
7957 #ifdef SQLITE_HAVE_ZIP
7958 }else if( strcmp(z
,"-zip")==0 ){
7959 data
.openMode
= SHELL_OPEN_ZIPFILE
;
7961 }else if( strcmp(z
,"-append")==0 ){
7962 data
.openMode
= SHELL_OPEN_APPENDVFS
;
7965 if( data
.zDbFilename
==0 ){
7966 #ifndef SQLITE_OMIT_MEMORYDB
7967 data
.zDbFilename
= ":memory:";
7968 warnInmemoryDb
= argc
==1;
7970 utf8_printf(stderr
,"%s: Error: no database filename specified\n", Argv0
);
7975 sqlite3_appendvfs_init(0,0,0);
7977 /* Go ahead and open the database file if it already exists. If the
7978 ** file does not exist, delay opening it. This prevents empty database
7979 ** files from being created if a user mistypes the database name argument
7980 ** to the sqlite command-line tool.
7982 if( access(data
.zDbFilename
, 0)==0 ){
7986 /* Process the initialization file if there is one. If no -init option
7987 ** is given on the command line, look for a file named ~/.sqliterc and
7988 ** try to process it.
7990 process_sqliterc(&data
,zInitFile
);
7992 /* Make a second pass through the command-line argument and set
7993 ** options. This second pass is delayed until after the initialization
7994 ** file is processed so that the command-line arguments will override
7995 ** settings in the initialization file.
7997 for(i
=1; i
<argc
; i
++){
7999 if( z
[0]!='-' ) continue;
8000 if( z
[1]=='-' ){ z
++; }
8001 if( strcmp(z
,"-init")==0 ){
8003 }else if( strcmp(z
,"-html")==0 ){
8004 data
.mode
= MODE_Html
;
8005 }else if( strcmp(z
,"-list")==0 ){
8006 data
.mode
= MODE_List
;
8007 }else if( strcmp(z
,"-quote")==0 ){
8008 data
.mode
= MODE_Quote
;
8009 }else if( strcmp(z
,"-line")==0 ){
8010 data
.mode
= MODE_Line
;
8011 }else if( strcmp(z
,"-column")==0 ){
8012 data
.mode
= MODE_Column
;
8013 }else if( strcmp(z
,"-csv")==0 ){
8014 data
.mode
= MODE_Csv
;
8015 memcpy(data
.colSeparator
,",",2);
8016 #ifdef SQLITE_HAVE_ZIP
8017 }else if( strcmp(z
,"-zip")==0 ){
8018 data
.openMode
= SHELL_OPEN_ZIPFILE
;
8020 }else if( strcmp(z
,"-append")==0 ){
8021 data
.openMode
= SHELL_OPEN_APPENDVFS
;
8022 }else if( strcmp(z
,"-ascii")==0 ){
8023 data
.mode
= MODE_Ascii
;
8024 sqlite3_snprintf(sizeof(data
.colSeparator
), data
.colSeparator
,
8026 sqlite3_snprintf(sizeof(data
.rowSeparator
), data
.rowSeparator
,
8028 }else if( strcmp(z
,"-separator")==0 ){
8029 sqlite3_snprintf(sizeof(data
.colSeparator
), data
.colSeparator
,
8030 "%s",cmdline_option_value(argc
,argv
,++i
));
8031 }else if( strcmp(z
,"-newline")==0 ){
8032 sqlite3_snprintf(sizeof(data
.rowSeparator
), data
.rowSeparator
,
8033 "%s",cmdline_option_value(argc
,argv
,++i
));
8034 }else if( strcmp(z
,"-nullvalue")==0 ){
8035 sqlite3_snprintf(sizeof(data
.nullValue
), data
.nullValue
,
8036 "%s",cmdline_option_value(argc
,argv
,++i
));
8037 }else if( strcmp(z
,"-header")==0 ){
8038 data
.showHeader
= 1;
8039 }else if( strcmp(z
,"-noheader")==0 ){
8040 data
.showHeader
= 0;
8041 }else if( strcmp(z
,"-echo")==0 ){
8042 ShellSetFlag(&data
, SHFLG_Echo
);
8043 }else if( strcmp(z
,"-eqp")==0 ){
8044 data
.autoEQP
= AUTOEQP_on
;
8045 }else if( strcmp(z
,"-eqpfull")==0 ){
8046 data
.autoEQP
= AUTOEQP_full
;
8047 }else if( strcmp(z
,"-stats")==0 ){
8049 }else if( strcmp(z
,"-scanstats")==0 ){
8050 data
.scanstatsOn
= 1;
8051 }else if( strcmp(z
,"-backslash")==0 ){
8052 /* Undocumented command-line option: -backslash
8053 ** Causes C-style backslash escapes to be evaluated in SQL statements
8054 ** prior to sending the SQL into SQLite. Useful for injecting
8055 ** crazy bytes in the middle of SQL statements for testing and debugging.
8057 ShellSetFlag(&data
, SHFLG_Backslash
);
8058 }else if( strcmp(z
,"-bail")==0 ){
8060 }else if( strcmp(z
,"-version")==0 ){
8061 printf("%s %s\n", sqlite3_libversion(), sqlite3_sourceid());
8063 }else if( strcmp(z
,"-interactive")==0 ){
8064 stdin_is_interactive
= 1;
8065 }else if( strcmp(z
,"-batch")==0 ){
8066 stdin_is_interactive
= 0;
8067 }else if( strcmp(z
,"-heap")==0 ){
8069 }else if( strcmp(z
,"-pagecache")==0 ){
8071 }else if( strcmp(z
,"-lookaside")==0 ){
8073 }else if( strcmp(z
,"-mmap")==0 ){
8075 }else if( strcmp(z
,"-vfs")==0 ){
8077 #ifdef SQLITE_ENABLE_VFSTRACE
8078 }else if( strcmp(z
,"-vfstrace")==0 ){
8081 #ifdef SQLITE_ENABLE_MULTIPLEX
8082 }else if( strcmp(z
,"-multiplex")==0 ){
8085 }else if( strcmp(z
,"-help")==0 ){
8087 }else if( strcmp(z
,"-cmd")==0 ){
8088 /* Run commands that follow -cmd first and separately from commands
8089 ** that simply appear on the command-line. This seems goofy. It would
8090 ** be better if all commands ran in the order that they appear. But
8091 ** we retain the goofy behavior for historical compatibility. */
8092 if( i
==argc
-1 ) break;
8093 z
= cmdline_option_value(argc
,argv
,++i
);
8095 rc
= do_meta_command(z
, &data
);
8096 if( rc
&& bail_on_error
) return rc
==2 ? 0 : rc
;
8099 rc
= shell_exec(data
.db
, z
, shell_callback
, &data
, &zErrMsg
);
8101 utf8_printf(stderr
,"Error: %s\n", zErrMsg
);
8102 if( bail_on_error
) return rc
!=0 ? rc
: 1;
8104 utf8_printf(stderr
,"Error: unable to process SQL \"%s\"\n", z
);
8105 if( bail_on_error
) return rc
;
8109 utf8_printf(stderr
,"%s: Error: unknown option: %s\n", Argv0
, z
);
8110 raw_printf(stderr
,"Use -help for a list of options.\n");
8113 data
.cMode
= data
.mode
;
8117 /* Run all arguments that do not begin with '-' as if they were separate
8118 ** command-line inputs, except for the argToSkip argument which contains
8119 ** the database filename.
8121 for(i
=0; i
<nCmd
; i
++){
8122 if( azCmd
[i
][0]=='.' ){
8123 rc
= do_meta_command(azCmd
[i
], &data
);
8124 if( rc
) return rc
==2 ? 0 : rc
;
8127 rc
= shell_exec(data
.db
, azCmd
[i
], shell_callback
, &data
, &zErrMsg
);
8129 utf8_printf(stderr
,"Error: %s\n", zErrMsg
);
8130 return rc
!=0 ? rc
: 1;
8132 utf8_printf(stderr
,"Error: unable to process SQL: %s\n", azCmd
[i
]);
8139 /* Run commands received from standard input
8141 if( stdin_is_interactive
){
8146 "SQLite version %s %.19s\n" /*extra-version-info*/
8147 "Enter \".help\" for usage hints.\n",
8148 sqlite3_libversion(), sqlite3_sourceid()
8150 if( warnInmemoryDb
){
8151 printf("Connected to a ");
8152 printBold("transient in-memory database");
8153 printf(".\nUse \".open FILENAME\" to reopen on a "
8154 "persistent database.\n");
8156 zHome
= find_home_dir(0);
8158 nHistory
= strlen30(zHome
) + 20;
8159 if( (zHistory
= malloc(nHistory
))!=0 ){
8160 sqlite3_snprintf(nHistory
, zHistory
,"%s/.sqlite_history", zHome
);
8163 if( zHistory
){ shell_read_history(zHistory
); }
8164 #if HAVE_READLINE || HAVE_EDITLINE
8165 rl_attempted_completion_function
= readline_completion
;
8166 #elif HAVE_LINENOISE
8167 linenoiseSetCompletionCallback(linenoise_completion
);
8169 rc
= process_input(&data
, 0);
8171 shell_stifle_history(2000);
8172 shell_write_history(zHistory
);
8176 rc
= process_input(&data
, stdin
);
8179 set_table_name(&data
, 0);
8181 session_close_all(&data
);
8182 sqlite3_close(data
.db
);
8184 sqlite3_free(data
.zFreeOnClose
);
8186 #if !SQLITE_SHELL_IS_UTF8
8187 for(i
=0; i
<argc
; i
++) sqlite3_free(argv
[i
]);