Index: ext/sqlite3/shell.c =================================================================== diff -u -N -r4e126c4c1e72c1952781ff23bfcaa2993f86bf22 -r4d6f618a44fd2e5096b5b7ea27b613a49f3aee7f --- ext/sqlite3/shell.c (.../shell.c) (revision 4e126c4c1e72c1952781ff23bfcaa2993f86bf22) +++ ext/sqlite3/shell.c (.../shell.c) (revision 4d6f618a44fd2e5096b5b7ea27b613a49f3aee7f) @@ -18,6 +18,13 @@ #endif /* +** If requested, include the SQLite compiler options file for MSVC. +*/ +#if defined(INCLUDE_MSVC_H) +#include "msvc.h" +#endif + +/* ** Enable large-file support for fopen() and friends on unix. */ #ifndef SQLITE_DISABLE_LFS @@ -33,6 +40,9 @@ #include #include #include "sqlite3.h" +#if SQLITE_USER_AUTHENTICATION +# include "sqlite3userauth.h" +#endif #include #include @@ -45,17 +55,16 @@ # include #endif -#if defined(HAVE_READLINE) && HAVE_READLINE!=0 +#if HAVE_READLINE # include # include -#else -# undef HAVE_READLINE #endif -#if defined(HAVE_EDITLINE) && !defined(HAVE_READLINE) +#if HAVE_EDITLINE +# undef HAVE_READLINE # define HAVE_READLINE 1 # include #endif -#if !defined(HAVE_READLINE) +#if !HAVE_READLINE # define add_history(X) # define read_history(X) # define write_history(X) @@ -64,6 +73,7 @@ #if defined(_WIN32) || defined(WIN32) # include +# include #define isatty(h) _isatty(h) #ifndef access # define access(f,m) _access((f),(m)) @@ -168,7 +178,8 @@ static FILETIME ftKernelBegin; static FILETIME ftUserBegin; static sqlite3_int64 ftWallBegin; -typedef BOOL (WINAPI *GETPROCTIMES)(HANDLE, LPFILETIME, LPFILETIME, LPFILETIME, LPFILETIME); +typedef BOOL (WINAPI *GETPROCTIMES)(HANDLE, LPFILETIME, LPFILETIME, + LPFILETIME, LPFILETIME); static GETPROCTIMES getProcessTimesAddr = NULL; /* @@ -179,15 +190,16 @@ if( getProcessTimesAddr ){ return 1; } else { - /* GetProcessTimes() isn't supported in WIN95 and some other Windows versions. - ** See if the version we are running on has it, and if it does, save off - ** a pointer to it and the current process handle. + /* GetProcessTimes() isn't supported in WIN95 and some other Windows + ** versions. See if the version we are running on has it, and if it + ** does, save off a pointer to it and the current process handle. */ hProcess = GetCurrentProcess(); if( hProcess ){ HINSTANCE hinstLib = LoadLibrary(TEXT("Kernel32.dll")); if( NULL != hinstLib ){ - getProcessTimesAddr = (GETPROCTIMES) GetProcAddress(hinstLib, "GetProcessTimes"); + getProcessTimesAddr = + (GETPROCTIMES) GetProcAddress(hinstLib, "GetProcessTimes"); if( NULL != getProcessTimesAddr ){ return 1; } @@ -204,7 +216,8 @@ static void beginTimer(void){ if( enableTimer && getProcessTimesAddr ){ FILETIME ftCreation, ftExit; - getProcessTimesAddr(hProcess, &ftCreation, &ftExit, &ftKernelBegin, &ftUserBegin); + getProcessTimesAddr(hProcess,&ftCreation,&ftExit, + &ftKernelBegin,&ftUserBegin); ftWallBegin = timeOfDay(); } } @@ -223,7 +236,7 @@ if( enableTimer && getProcessTimesAddr){ FILETIME ftCreation, ftExit, ftKernelEnd, ftUserEnd; sqlite3_int64 ftWallEnd = timeOfDay(); - getProcessTimesAddr(hProcess, &ftCreation, &ftExit, &ftKernelEnd, &ftUserEnd); + getProcessTimesAddr(hProcess,&ftCreation,&ftExit,&ftKernelEnd,&ftUserEnd); printf("Run Time: real %.3f user %f sys %f\n", (ftWallEnd - ftWallBegin)*0.001, timeDiff(&ftUserBegin, &ftUserEnd), @@ -418,7 +431,7 @@ zResult = local_getline(zPrior, in); }else{ zPrompt = isContinuation ? continuePrompt : mainPrompt; -#if defined(HAVE_READLINE) +#if HAVE_READLINE free(zPrior); zResult = readline(zPrompt); if( zResult && *zResult ) add_history(zResult); @@ -431,39 +444,46 @@ return zResult; } -struct previous_mode_data { - int valid; /* Is there legit data in here? */ - int mode; - int showHeader; - int colWidth[100]; +/* +** Shell output mode information from before ".explain on", +** saved so that it can be restored by ".explain off" +*/ +typedef struct SavedModeInfo SavedModeInfo; +struct SavedModeInfo { + int valid; /* Is there legit data in here? */ + int mode; /* Mode prior to ".explain on" */ + int showHeader; /* The ".header" setting prior to ".explain on" */ + int colWidth[100]; /* Column widths prior to ".explain on" */ }; /* -** An pointer to an instance of this structure is passed from -** the main program to the callback. This is used to communicate -** state and mode information. +** State information about the database connection is contained in an +** instance of the following structure. */ -struct callback_data { +typedef struct ShellState ShellState; +struct ShellState { sqlite3 *db; /* The database */ int echoOn; /* True to echo input commands */ - int autoEQP; /* Run EXPLAIN QUERY PLAN prior to seach SQL statement */ + int autoEQP; /* Run EXPLAIN QUERY PLAN prior to seach SQL stmt */ int statsOn; /* True to display memory stats before each finalize */ + int scanstatsOn; /* True to display scan stats before each finalize */ + int outCount; /* Revert to stdout when reaching zero */ int cnt; /* Number of records displayed so far */ FILE *out; /* Write results here */ FILE *traceOut; /* Output for sqlite3_trace() */ int nErr; /* Number of errors seen */ int mode; /* An output mode setting */ int writableSchema; /* True if PRAGMA writable_schema=ON */ int showHeader; /* True to show column names in List or Column mode */ + unsigned shellFlgs; /* Various flags */ char *zDestTable; /* Name of destination table when MODE_Insert */ - char separator[20]; /* Separator character for MODE_List */ + char colSeparator[20]; /* Column separator character for several modes */ + char rowSeparator[20]; /* Row separator character for MODE_Ascii */ int colWidth[100]; /* Requested width of each column when in column mode*/ int actualWidth[100]; /* Actual width of each column */ - char nullvalue[20]; /* The text to print when a NULL comes back from + char nullValue[20]; /* The text to print when a NULL comes back from ** the database */ - struct previous_mode_data explainPrev; - /* Holds the mode information just before - ** .explain ON */ + SavedModeInfo normalMode;/* Holds the mode just before .explain ON */ char outfile[FILENAME_MAX]; /* Filename for *out */ const char *zDbFilename; /* name of the database file */ char *zFreeOnClose; /* Filename to free when closing */ @@ -476,6 +496,13 @@ }; /* +** These are the allowed shellFlgs values +*/ +#define SHFLG_Scratch 0x00001 /* The --scratch option is used */ +#define SHFLG_Pagecache 0x00002 /* The --pagecache option is used */ +#define SHFLG_Lookaside 0x00004 /* Lookaside memory is used */ + +/* ** These are the allowed modes. */ #define MODE_Line 0 /* One column per line. Blank line between records */ @@ -487,6 +514,7 @@ #define MODE_Tcl 6 /* Generate ANSI-C or TCL quoted elements */ #define MODE_Csv 7 /* Quote strings, numbers are plain */ #define MODE_Explain 8 /* Like MODE_Column, but do not truncate data */ +#define MODE_Ascii 9 /* Use ASCII unit and record separators (0x1F/0x1E) */ static const char *modeDescr[] = { "line", @@ -498,9 +526,23 @@ "tcl", "csv", "explain", + "ascii", }; /* +** These are the column/row/line separators used by the various +** import/export modes. +*/ +#define SEP_Column "|" +#define SEP_Row "\n" +#define SEP_Tab "\t" +#define SEP_Space " " +#define SEP_Comma "," +#define SEP_CrLf "\r\n" +#define SEP_Unit "\x1F" +#define SEP_Record "\x1E" + +/* ** Number of elements in an array */ #define ArraySize(X) (int)(sizeof(X)/sizeof(X[0])) @@ -519,7 +561,7 @@ ** A callback for the sqlite3_log() interface. */ static void shellLog(void *pArg, int iErrCode, const char *zMsg){ - struct callback_data *p = (struct callback_data*)pArg; + ShellState *p = (ShellState*)pArg; if( p->pLog==0 ) return; fprintf(p->pLog, "(%d) %s\n", iErrCode, zMsg); fflush(p->pLog); @@ -656,21 +698,22 @@ }; /* -** Output a single term of CSV. Actually, p->separator is used for -** the separator, which may or may not be a comma. p->nullvalue is -** the null value. Strings are quoted if necessary. +** Output a single term of CSV. Actually, p->colSeparator is used for +** the separator, which may or may not be a comma. p->nullValue is +** the null value. Strings are quoted if necessary. The separator +** is only issued if bSep is true. */ -static void output_csv(struct callback_data *p, const char *z, int bSep){ +static void output_csv(ShellState *p, const char *z, int bSep){ FILE *out = p->out; if( z==0 ){ - fprintf(out,"%s",p->nullvalue); + fprintf(out,"%s",p->nullValue); }else{ int i; - int nSep = strlen30(p->separator); + int nSep = strlen30(p->colSeparator); for(i=0; z[i]; i++){ if( needCsvQuote[((unsigned char*)z)[i]] - || (z[i]==p->separator[0] && - (nSep==1 || memcmp(z, p->separator, nSep)==0)) ){ + || (z[i]==p->colSeparator[0] && + (nSep==1 || memcmp(z, p->colSeparator, nSep)==0)) ){ i = 0; break; } @@ -687,7 +730,7 @@ } } if( bSep ){ - fprintf(p->out, "%s", p->separator); + fprintf(p->out, "%s", p->colSeparator); } } @@ -697,7 +740,8 @@ */ static void interrupt_handler(int NotUsed){ UNUSED_PARAMETER(NotUsed); - seenInterrupt = 1; + seenInterrupt++; + if( seenInterrupt>2 ) exit(1); if( db ) sqlite3_interrupt(db); } #endif @@ -706,9 +750,15 @@ ** This is the callback routine that the shell ** invokes for each row of a query result. */ -static int shell_callback(void *pArg, int nArg, char **azArg, char **azCol, int *aiType){ +static int shell_callback( + void *pArg, + int nArg, /* Number of result columns */ + char **azArg, /* Text of each result column */ + char **azCol, /* Column names */ + int *aiType /* Column types */ +){ int i; - struct callback_data *p = (struct callback_data*)pArg; + ShellState *p = (ShellState*)pArg; switch( p->mode ){ case MODE_Line: { @@ -718,10 +768,10 @@ int len = strlen30(azCol[i] ? azCol[i] : ""); if( len>w ) w = len; } - if( p->cnt++>0 ) fprintf(p->out,"\n"); + if( p->cnt++>0 ) fprintf(p->out, "%s", p->rowSeparator); for(i=0; iout,"%*s = %s\n", w, azCol[i], - azArg[i] ? azArg[i] : p->nullvalue); + fprintf(p->out,"%*s = %s%s", w, azCol[i], + azArg[i] ? azArg[i] : p->nullValue, p->rowSeparator); } break; } @@ -738,17 +788,19 @@ if( w==0 ){ w = strlen30(azCol[i] ? azCol[i] : ""); if( w<10 ) w = 10; - n = strlen30(azArg && azArg[i] ? azArg[i] : p->nullvalue); + n = strlen30(azArg && azArg[i] ? azArg[i] : p->nullValue); if( wactualWidth) ){ p->actualWidth[i] = w; } if( p->showHeader ){ if( w<0 ){ - fprintf(p->out,"%*.*s%s",-w,-w,azCol[i], i==nArg-1 ? "\n": " "); + fprintf(p->out,"%*.*s%s",-w,-w,azCol[i], + i==nArg-1 ? p->rowSeparator : " "); }else{ - fprintf(p->out,"%-*.*s%s",w,w,azCol[i], i==nArg-1 ? "\n": " "); + fprintf(p->out,"%-*.*s%s",w,w,azCol[i], + i==nArg-1 ? p->rowSeparator : " "); } } } @@ -763,7 +815,7 @@ } fprintf(p->out,"%-*.*s%s",w,w,"-----------------------------------" "----------------------------------------------------------", - i==nArg-1 ? "\n": " "); + i==nArg-1 ? p->rowSeparator : " "); } } } @@ -786,10 +838,12 @@ } if( w<0 ){ fprintf(p->out,"%*.*s%s",-w,-w, - azArg[i] ? azArg[i] : p->nullvalue, i==nArg-1 ? "\n": " "); + azArg[i] ? azArg[i] : p->nullValue, + i==nArg-1 ? p->rowSeparator : " "); }else{ fprintf(p->out,"%-*.*s%s",w,w, - azArg[i] ? azArg[i] : p->nullvalue, i==nArg-1 ? "\n": " "); + azArg[i] ? azArg[i] : p->nullValue, + i==nArg-1 ? p->rowSeparator : " "); } } break; @@ -798,20 +852,21 @@ case MODE_List: { if( p->cnt++==0 && p->showHeader ){ for(i=0; iout,"%s%s",azCol[i], i==nArg-1 ? "\n" : p->separator); + fprintf(p->out,"%s%s",azCol[i], + i==nArg-1 ? p->rowSeparator : p->colSeparator); } } if( azArg==0 ) break; for(i=0; inullvalue; + if( z==0 ) z = p->nullValue; fprintf(p->out, "%s", z); if( iout, "%s", p->separator); + fprintf(p->out, "%s", p->colSeparator); }else if( p->mode==MODE_Semi ){ - fprintf(p->out, ";\n"); + fprintf(p->out, ";%s", p->rowSeparator); }else{ - fprintf(p->out, "\n"); + fprintf(p->out, "%s", p->rowSeparator); } } break; @@ -830,7 +885,7 @@ fprintf(p->out,""); for(i=0; iout,""); - output_html_string(p->out, azArg[i] ? azArg[i] : p->nullvalue); + output_html_string(p->out, azArg[i] ? azArg[i] : p->nullValue); fprintf(p->out,"\n"); } fprintf(p->out,"\n"); @@ -840,30 +895,39 @@ if( p->cnt++==0 && p->showHeader ){ for(i=0; iout,azCol[i] ? azCol[i] : ""); - if(iout, "%s", p->separator); + if(iout, "%s", p->colSeparator); } - fprintf(p->out,"\n"); + fprintf(p->out, "%s", p->rowSeparator); } if( azArg==0 ) break; for(i=0; iout, azArg[i] ? azArg[i] : p->nullvalue); - if(iout, "%s", p->separator); + output_c_string(p->out, azArg[i] ? azArg[i] : p->nullValue); + if(iout, "%s", p->colSeparator); } - fprintf(p->out,"\n"); + fprintf(p->out, "%s", p->rowSeparator); break; } case MODE_Csv: { +#if defined(WIN32) || defined(_WIN32) + fflush(p->out); + _setmode(_fileno(p->out), _O_BINARY); +#endif if( p->cnt++==0 && p->showHeader ){ for(i=0; iout,"\n"); + fprintf(p->out, "%s", p->rowSeparator); } - if( azArg==0 ) break; - for(i=0; i0 ){ + for(i=0; iout, "%s", p->rowSeparator); } - fprintf(p->out,"\n"); +#if defined(WIN32) || defined(_WIN32) + fflush(p->out); + _setmode(_fileno(p->out), _O_TEXT); +#endif break; } case MODE_Insert: { @@ -877,7 +941,8 @@ }else if( aiType && aiType[i]==SQLITE_TEXT ){ if( zSep[0] ) fprintf(p->out,"%s",zSep); output_quoted_string(p->out, azArg[i]); - }else if( aiType && (aiType[i]==SQLITE_INTEGER || aiType[i]==SQLITE_FLOAT) ){ + }else if( aiType && (aiType[i]==SQLITE_INTEGER + || aiType[i]==SQLITE_FLOAT) ){ fprintf(p->out,"%s%s",zSep, azArg[i]); }else if( aiType && aiType[i]==SQLITE_BLOB && p->pStmt ){ const void *pBlob = sqlite3_column_blob(p->pStmt, i); @@ -894,6 +959,22 @@ fprintf(p->out,");\n"); break; } + case MODE_Ascii: { + if( p->cnt++==0 && p->showHeader ){ + for(i=0; i0 ) fprintf(p->out, "%s", p->colSeparator); + fprintf(p->out,"%s",azCol[i] ? azCol[i] : ""); + } + fprintf(p->out, "%s", p->rowSeparator); + } + if( azArg==0 ) break; + for(i=0; i0 ) fprintf(p->out, "%s", p->colSeparator); + fprintf(p->out,"%s",azArg[i] ? azArg[i] : p->nullValue); + } + fprintf(p->out, "%s", p->rowSeparator); + break; + } } return 0; } @@ -908,11 +989,11 @@ } /* -** Set the destination table field of the callback_data structure to +** Set the destination table field of the ShellState structure to ** the name of the table given. Escape any quote characters in the ** table name. */ -static void set_table_name(struct callback_data *p, const char *zName){ +static void set_table_name(ShellState *p, const char *zName){ int i, n; int needQuote; char *z; @@ -1002,7 +1083,7 @@ ** won't consume the semicolon terminator. */ static int run_table_dump_query( - struct callback_data *p, /* Query context */ + ShellState *p, /* Query context */ const char *zSelect, /* SELECT statement to extract content */ const char *zFirstRow /* Print before first row, if not NULL */ ){ @@ -1065,7 +1146,7 @@ */ static int display_stats( sqlite3 *db, /* Database to query */ - struct callback_data *pArg, /* Pointer to struct callback_data */ + ShellState *pArg, /* Pointer to ShellState */ int bReset /* True to reset the stats */ ){ int iCur; @@ -1075,57 +1156,77 @@ iHiwtr = iCur = -1; sqlite3_status(SQLITE_STATUS_MEMORY_USED, &iCur, &iHiwtr, bReset); - fprintf(pArg->out, "Memory Used: %d (max %d) bytes\n", iCur, iHiwtr); + fprintf(pArg->out, + "Memory Used: %d (max %d) bytes\n", + iCur, iHiwtr); iHiwtr = iCur = -1; sqlite3_status(SQLITE_STATUS_MALLOC_COUNT, &iCur, &iHiwtr, bReset); - fprintf(pArg->out, "Number of Outstanding Allocations: %d (max %d)\n", iCur, iHiwtr); -/* -** Not currently used by the CLI. -** iHiwtr = iCur = -1; -** sqlite3_status(SQLITE_STATUS_PAGECACHE_USED, &iCur, &iHiwtr, bReset); -** fprintf(pArg->out, "Number of Pcache Pages Used: %d (max %d) pages\n", iCur, iHiwtr); -*/ + fprintf(pArg->out, "Number of Outstanding Allocations: %d (max %d)\n", + iCur, iHiwtr); + if( pArg->shellFlgs & SHFLG_Pagecache ){ + iHiwtr = iCur = -1; + sqlite3_status(SQLITE_STATUS_PAGECACHE_USED, &iCur, &iHiwtr, bReset); + fprintf(pArg->out, + "Number of Pcache Pages Used: %d (max %d) pages\n", + iCur, iHiwtr); + } iHiwtr = iCur = -1; sqlite3_status(SQLITE_STATUS_PAGECACHE_OVERFLOW, &iCur, &iHiwtr, bReset); - fprintf(pArg->out, "Number of Pcache Overflow Bytes: %d (max %d) bytes\n", iCur, iHiwtr); -/* -** Not currently used by the CLI. -** iHiwtr = iCur = -1; -** sqlite3_status(SQLITE_STATUS_SCRATCH_USED, &iCur, &iHiwtr, bReset); -** fprintf(pArg->out, "Number of Scratch Allocations Used: %d (max %d)\n", iCur, iHiwtr); -*/ + fprintf(pArg->out, + "Number of Pcache Overflow Bytes: %d (max %d) bytes\n", + iCur, iHiwtr); + if( pArg->shellFlgs & SHFLG_Scratch ){ + iHiwtr = iCur = -1; + sqlite3_status(SQLITE_STATUS_SCRATCH_USED, &iCur, &iHiwtr, bReset); + fprintf(pArg->out, "Number of Scratch Allocations Used: %d (max %d)\n", + iCur, iHiwtr); + } iHiwtr = iCur = -1; sqlite3_status(SQLITE_STATUS_SCRATCH_OVERFLOW, &iCur, &iHiwtr, bReset); - fprintf(pArg->out, "Number of Scratch Overflow Bytes: %d (max %d) bytes\n", iCur, iHiwtr); + fprintf(pArg->out, + "Number of Scratch Overflow Bytes: %d (max %d) bytes\n", + iCur, iHiwtr); iHiwtr = iCur = -1; sqlite3_status(SQLITE_STATUS_MALLOC_SIZE, &iCur, &iHiwtr, bReset); - fprintf(pArg->out, "Largest Allocation: %d bytes\n", iHiwtr); + fprintf(pArg->out, "Largest Allocation: %d bytes\n", + iHiwtr); iHiwtr = iCur = -1; sqlite3_status(SQLITE_STATUS_PAGECACHE_SIZE, &iCur, &iHiwtr, bReset); - fprintf(pArg->out, "Largest Pcache Allocation: %d bytes\n", iHiwtr); + fprintf(pArg->out, "Largest Pcache Allocation: %d bytes\n", + iHiwtr); iHiwtr = iCur = -1; sqlite3_status(SQLITE_STATUS_SCRATCH_SIZE, &iCur, &iHiwtr, bReset); - fprintf(pArg->out, "Largest Scratch Allocation: %d bytes\n", iHiwtr); + fprintf(pArg->out, "Largest Scratch Allocation: %d bytes\n", + iHiwtr); #ifdef YYTRACKMAXSTACKDEPTH iHiwtr = iCur = -1; sqlite3_status(SQLITE_STATUS_PARSER_STACK, &iCur, &iHiwtr, bReset); - fprintf(pArg->out, "Deepest Parser Stack: %d (max %d)\n", iCur, iHiwtr); + fprintf(pArg->out, "Deepest Parser Stack: %d (max %d)\n", + iCur, iHiwtr); #endif } if( pArg && pArg->out && db ){ + if( pArg->shellFlgs & SHFLG_Lookaside ){ + iHiwtr = iCur = -1; + sqlite3_db_status(db, SQLITE_DBSTATUS_LOOKASIDE_USED, + &iCur, &iHiwtr, bReset); + fprintf(pArg->out, "Lookaside Slots Used: %d (max %d)\n", + iCur, iHiwtr); + sqlite3_db_status(db, SQLITE_DBSTATUS_LOOKASIDE_HIT, + &iCur, &iHiwtr, bReset); + fprintf(pArg->out, "Successful lookaside attempts: %d\n", iHiwtr); + sqlite3_db_status(db, SQLITE_DBSTATUS_LOOKASIDE_MISS_SIZE, + &iCur, &iHiwtr, bReset); + fprintf(pArg->out, "Lookaside failures due to size: %d\n", iHiwtr); + sqlite3_db_status(db, SQLITE_DBSTATUS_LOOKASIDE_MISS_FULL, + &iCur, &iHiwtr, bReset); + fprintf(pArg->out, "Lookaside failures due to OOM: %d\n", iHiwtr); + } iHiwtr = iCur = -1; - sqlite3_db_status(db, SQLITE_DBSTATUS_LOOKASIDE_USED, &iCur, &iHiwtr, bReset); - fprintf(pArg->out, "Lookaside Slots Used: %d (max %d)\n", iCur, iHiwtr); - sqlite3_db_status(db, SQLITE_DBSTATUS_LOOKASIDE_HIT, &iCur, &iHiwtr, bReset); - fprintf(pArg->out, "Successful lookaside attempts: %d\n", iHiwtr); - sqlite3_db_status(db, SQLITE_DBSTATUS_LOOKASIDE_MISS_SIZE, &iCur, &iHiwtr, bReset); - fprintf(pArg->out, "Lookaside failures due to size: %d\n", iHiwtr); - sqlite3_db_status(db, SQLITE_DBSTATUS_LOOKASIDE_MISS_FULL, &iCur, &iHiwtr, bReset); - fprintf(pArg->out, "Lookaside failures due to OOM: %d\n", iHiwtr); - iHiwtr = iCur = -1; sqlite3_db_status(db, SQLITE_DBSTATUS_CACHE_USED, &iCur, &iHiwtr, bReset); - fprintf(pArg->out, "Pager Heap Usage: %d bytes\n", iCur); iHiwtr = iCur = -1; + fprintf(pArg->out, "Pager Heap Usage: %d bytes\n",iCur); + iHiwtr = iCur = -1; sqlite3_db_status(db, SQLITE_DBSTATUS_CACHE_HIT, &iCur, &iHiwtr, 1); fprintf(pArg->out, "Page cache hits: %d\n", iCur); iHiwtr = iCur = -1; @@ -1136,18 +1237,19 @@ fprintf(pArg->out, "Page cache writes: %d\n", iCur); iHiwtr = iCur = -1; sqlite3_db_status(db, SQLITE_DBSTATUS_SCHEMA_USED, &iCur, &iHiwtr, bReset); - fprintf(pArg->out, "Schema Heap Usage: %d bytes\n", iCur); + fprintf(pArg->out, "Schema Heap Usage: %d bytes\n",iCur); iHiwtr = iCur = -1; sqlite3_db_status(db, SQLITE_DBSTATUS_STMT_USED, &iCur, &iHiwtr, bReset); - fprintf(pArg->out, "Statement Heap/Lookaside Usage: %d bytes\n", iCur); + fprintf(pArg->out, "Statement Heap/Lookaside Usage: %d bytes\n",iCur); } if( pArg && pArg->out && db && pArg->pStmt ){ - iCur = sqlite3_stmt_status(pArg->pStmt, SQLITE_STMTSTATUS_FULLSCAN_STEP, bReset); + iCur = sqlite3_stmt_status(pArg->pStmt, SQLITE_STMTSTATUS_FULLSCAN_STEP, + bReset); fprintf(pArg->out, "Fullscan Steps: %d\n", iCur); iCur = sqlite3_stmt_status(pArg->pStmt, SQLITE_STMTSTATUS_SORT, bReset); fprintf(pArg->out, "Sort Operations: %d\n", iCur); - iCur = sqlite3_stmt_status(pArg->pStmt, SQLITE_STMTSTATUS_AUTOINDEX, bReset); + iCur = sqlite3_stmt_status(pArg->pStmt, SQLITE_STMTSTATUS_AUTOINDEX,bReset); fprintf(pArg->out, "Autoindex Inserts: %d\n", iCur); iCur = sqlite3_stmt_status(pArg->pStmt, SQLITE_STMTSTATUS_VM_STEP, bReset); fprintf(pArg->out, "Virtual Machine Steps: %d\n", iCur); @@ -1157,6 +1259,51 @@ } /* +** Display scan stats. +*/ +static void display_scanstats( + sqlite3 *db, /* Database to query */ + ShellState *pArg /* Pointer to ShellState */ +){ +#ifdef SQLITE_ENABLE_STMT_SCANSTATUS + int i, k, n, mx; + fprintf(pArg->out, "-------- scanstats --------\n"); + mx = 0; + for(k=0; k<=mx; k++){ + double rEstLoop = 1.0; + for(i=n=0; 1; i++){ + sqlite3_stmt *p = pArg->pStmt; + sqlite3_int64 nLoop, nVisit; + double rEst; + int iSid; + const char *zExplain; + if( sqlite3_stmt_scanstatus(p, i, SQLITE_SCANSTAT_NLOOP, (void*)&nLoop) ){ + break; + } + sqlite3_stmt_scanstatus(p, i, SQLITE_SCANSTAT_SELECTID, (void*)&iSid); + if( iSid>mx ) mx = iSid; + if( iSid!=k ) continue; + if( n==0 ){ + rEstLoop = (double)nLoop; + if( k>0 ) fprintf(pArg->out, "-------- subquery %d -------\n", k); + } + n++; + sqlite3_stmt_scanstatus(p, i, SQLITE_SCANSTAT_NVISIT, (void*)&nVisit); + sqlite3_stmt_scanstatus(p, i, SQLITE_SCANSTAT_EST, (void*)&rEst); + sqlite3_stmt_scanstatus(p, i, SQLITE_SCANSTAT_EXPLAIN, (void*)&zExplain); + fprintf(pArg->out, "Loop %2d: %s\n", n, zExplain); + rEstLoop *= rEst; + fprintf(pArg->out, + " nLoop=%-8lld nRow=%-8lld estRow=%-8lld estRow/Loop=%-8g\n", + nLoop, nVisit, (sqlite3_int64)(rEstLoop+0.5), rEst + ); + } + } + fprintf(pArg->out, "---------------------------\n"); +#endif +} + +/* ** Parameter azArray points to a zero-terminated array of strings. zStr ** points to a single nul-terminated string. Return non-zero if zStr ** is equal, according to strcmp(), to any of the strings in the array. @@ -1172,7 +1319,7 @@ /* ** If compiled statement pSql appears to be an EXPLAIN statement, allocate -** and populate the callback_data.aiIndent[] array with the number of +** and populate the ShellState.aiIndent[] array with the number of ** spaces each opcode should be indented before it is output. ** ** The indenting rules are: @@ -1188,15 +1335,17 @@ ** then indent all opcodes between the earlier instruction ** and "Goto" by 2 spaces. */ -static void explain_data_prepare(struct callback_data *p, sqlite3_stmt *pSql){ +static void explain_data_prepare(ShellState *p, sqlite3_stmt *pSql){ const char *zSql; /* The text of the SQL statement */ const char *z; /* Used to check if this is an EXPLAIN */ int *abYield = 0; /* True if op is an OP_Yield */ int nAlloc = 0; /* Allocated size of p->aiIndent[], abYield */ int iOp; /* Index of operation in p->aiIndent[] */ - const char *azNext[] = { "Next", "Prev", "VPrev", "VNext", "SorterNext", 0 }; - const char *azYield[] = { "Yield", "SeekLt", "SeekGt", "RowSetRead", "Rewind", 0 }; + const char *azNext[] = { "Next", "Prev", "VPrev", "VNext", "SorterNext", + "NextIfOpen", "PrevIfOpen", 0 }; + const char *azYield[] = { "Yield", "SeekLT", "SeekGT", "RowSetRead", + "Rewind", 0 }; const char *azGoto[] = { "Goto", 0 }; /* Try to figure out if this is really an EXPLAIN statement. If this @@ -1247,7 +1396,7 @@ /* ** Free the array allocated by explain_data_prepare(). */ -static void explain_data_delete(struct callback_data *p){ +static void explain_data_delete(ShellState *p){ sqlite3_free(p->aiIndent); p->aiIndent = 0; p->nIndent = 0; @@ -1264,12 +1413,12 @@ ** and callback data argument. */ static int shell_exec( - sqlite3 *db, /* An open database */ - const char *zSql, /* SQL to be evaluated */ + sqlite3 *db, /* An open database */ + const char *zSql, /* SQL to be evaluated */ int (*xCallback)(void*,int,char**,char**,int*), /* Callback function */ - /* (not the same as sqlite3_exec) */ - struct callback_data *pArg, /* Pointer to struct callback_data */ - char **pzErrMsg /* Error msg written here */ + /* (not the same as sqlite3_exec) */ + ShellState *pArg, /* Pointer to ShellState */ + char **pzErrMsg /* Error msg written here */ ){ sqlite3_stmt *pStmt = NULL; /* Statement to execute. */ int rc = SQLITE_OK; /* Return Code */ @@ -1309,7 +1458,8 @@ /* Show the EXPLAIN QUERY PLAN if .eqp is on */ if( pArg && pArg->autoEQP ){ sqlite3_stmt *pExplain; - char *zEQP = sqlite3_mprintf("EXPLAIN QUERY PLAN %s", sqlite3_sql(pStmt)); + char *zEQP = sqlite3_mprintf("EXPLAIN QUERY PLAN %s", + sqlite3_sql(pStmt)); rc = sqlite3_prepare_v2(db, zEQP, -1, &pExplain, 0); if( rc==SQLITE_OK ){ while( sqlite3_step(pExplain)==SQLITE_ROW ){ @@ -1323,15 +1473,6 @@ sqlite3_free(zEQP); } - /* Output TESTCTRL_EXPLAIN text of requested */ - if( pArg && pArg->mode==MODE_Explain ){ - const char *zExplain = 0; - sqlite3_test_control(SQLITE_TESTCTRL_EXPLAIN_STMT, pStmt, &zExplain); - if( zExplain && zExplain[0] ){ - fprintf(pArg->out, "%s", zExplain); - } - } - /* If the shell is currently in ".explain" mode, gather the extra ** data required to add indents to the output.*/ if( pArg && pArg->mode==MODE_Explain ){ @@ -1402,6 +1543,11 @@ display_stats(db, pArg, 0); } + /* print loop-counters if required */ + if( pArg && pArg->scanstatsOn ){ + display_scanstats(db, pArg); + } + /* Finalize the statement just executed. If this fails, save a ** copy of the error message. Otherwise, set zSql to point to the ** next statement to execute. */ @@ -1437,7 +1583,7 @@ const char *zType; const char *zSql; const char *zPrepStmt = 0; - struct callback_data *p = (struct callback_data *)pArg; + ShellState *p = (ShellState *)pArg; UNUSED_PARAMETER(azCol); if( nArg!=3 ) return 1; @@ -1533,7 +1679,7 @@ ** "ORDER BY rowid DESC" to the end. */ static int run_schema_dump_query( - struct callback_data *p, + ShellState *p, const char *zQuery ){ int rc; @@ -1568,17 +1714,19 @@ */ static char zHelp[] = ".backup ?DB? FILE Backup DB (default \"main\") to FILE\n" - ".bail ON|OFF Stop after hitting an error. Default OFF\n" + ".bail on|off Stop after hitting an error. Default OFF\n" ".clone NEWDB Clone data into NEWDB from the existing database\n" ".databases List names and files of attached databases\n" ".dump ?TABLE? ... Dump the database in an SQL text format\n" " If TABLE specified, only dump tables matching\n" " LIKE pattern TABLE.\n" - ".echo ON|OFF Turn command echo on or off\n" + ".echo on|off Turn command echo on or off\n" + ".eqp on|off Enable or disable automatic EXPLAIN QUERY PLAN\n" ".exit Exit this program\n" - ".explain ?ON|OFF? Turn output mode suitable for EXPLAIN on or off.\n" + ".explain ?on|off? Turn output mode suitable for EXPLAIN on or off.\n" " With no args, it turns EXPLAIN on.\n" - ".header(s) ON|OFF Turn display of headers on or off\n" + ".fullschema Show schema and the content of sqlite_stat tables\n" + ".headers on|off Turn display of headers on or off\n" ".help Show this message\n" ".import FILE TABLE Import data from FILE into TABLE\n" ".indices ?TABLE? Show names of all indices\n" @@ -1592,51 +1740,114 @@ #endif ".log FILE|off Turn logging on or off. FILE can be stderr/stdout\n" ".mode MODE ?TABLE? Set output mode where MODE is one of:\n" + " ascii Columns/rows delimited by 0x1F and 0x1E\n" " csv Comma-separated values\n" " column Left-aligned columns. (See .width)\n" " html HTML code\n" " insert SQL insert statements for TABLE\n" " line One value per line\n" - " list Values delimited by .separator string\n" + " list Values delimited by .separator strings\n" " tabs Tab-separated values\n" " tcl TCL list elements\n" ".nullvalue STRING Use STRING in place of NULL values\n" + ".once FILENAME Output for the next SQL command only to FILENAME\n" ".open ?FILENAME? Close existing database and reopen FILENAME\n" - ".output FILENAME Send output to FILENAME\n" - ".output stdout Send output to the screen\n" + ".output ?FILENAME? Send output to FILENAME or stdout\n" ".print STRING... Print literal STRING\n" ".prompt MAIN CONTINUE Replace the standard prompts\n" ".quit Exit this program\n" ".read FILENAME Execute SQL in FILENAME\n" ".restore ?DB? FILE Restore content of DB (default \"main\") from FILE\n" ".save FILE Write in-memory database into FILE\n" + ".scanstats on|off Turn sqlite3_stmt_scanstatus() metrics on or off\n" ".schema ?TABLE? Show the CREATE statements\n" " If TABLE specified, only show tables matching\n" " LIKE pattern TABLE.\n" - ".separator STRING Change separator used by output mode and .import\n" + ".separator COL ?ROW? Change the column separator and optionally the row\n" + " separator for both the output mode and .import\n" + ".shell CMD ARGS... Run CMD ARGS... in a system shell\n" ".show Show the current values for various settings\n" - ".stats ON|OFF Turn stats on or off\n" + ".stats on|off Turn stats on or off\n" + ".system CMD ARGS... Run CMD ARGS... in a system shell\n" ".tables ?TABLE? List names of tables\n" " If TABLE specified, only list tables matching\n" " LIKE pattern TABLE.\n" ".timeout MS Try opening locked tables for MS milliseconds\n" + ".timer on|off Turn SQL timer on or off\n" ".trace FILE|off Output each SQL statement as it is run\n" ".vfsname ?AUX? Print the name of the VFS stack\n" ".width NUM1 NUM2 ... Set column widths for \"column\" mode\n" + " Negative values right-justify\n" ; -static char zTimerHelp[] = - ".timer ON|OFF Turn the CPU timer measurement on or off\n" -; - /* Forward reference */ -static int process_input(struct callback_data *p, FILE *in); +static int process_input(ShellState *p, FILE *in); +/* +** Implementation of the "readfile(X)" SQL function. The entire content +** of the file named X is read and returned as a BLOB. NULL is returned +** if the file does not exist or is unreadable. +*/ +static void readfileFunc( + sqlite3_context *context, + int argc, + sqlite3_value **argv +){ + const char *zName; + FILE *in; + long nIn; + void *pBuf; + zName = (const char*)sqlite3_value_text(argv[0]); + if( zName==0 ) return; + in = fopen(zName, "rb"); + if( in==0 ) return; + fseek(in, 0, SEEK_END); + nIn = ftell(in); + rewind(in); + pBuf = sqlite3_malloc( nIn ); + if( pBuf && 1==fread(pBuf, nIn, 1, in) ){ + sqlite3_result_blob(context, pBuf, nIn, sqlite3_free); + }else{ + sqlite3_free(pBuf); + } + fclose(in); +} + /* +** Implementation of the "writefile(X,Y)" SQL function. The argument Y +** is written into file X. The number of bytes written is returned. Or +** NULL is returned if something goes wrong, such as being unable to open +** file X for writing. +*/ +static void writefileFunc( + sqlite3_context *context, + int argc, + sqlite3_value **argv +){ + FILE *out; + const char *z; + sqlite3_int64 rc; + const char *zFile; + + zFile = (const char*)sqlite3_value_text(argv[0]); + if( zFile==0 ) return; + out = fopen(zFile, "wb"); + if( out==0 ) return; + z = (const char*)sqlite3_value_blob(argv[1]); + if( z==0 ){ + rc = 0; + }else{ + rc = fwrite(z, 1, sqlite3_value_bytes(argv[1]), out); + } + fclose(out); + sqlite3_result_int64(context, rc); +} + +/* ** Make sure the database is open. If it is not, then open it. If ** the database fails to open, print an error message and exit. */ -static void open_db(struct callback_data *p, int keepAlive){ +static void open_db(ShellState *p, int keepAlive){ if( p->db==0 ){ sqlite3_initialize(); sqlite3_open(p->zDbFilename, &p->db); @@ -1654,6 +1865,10 @@ #ifndef SQLITE_OMIT_LOAD_EXTENSION sqlite3_enable_load_extension(p->db, 1); #endif + sqlite3_create_function(db, "readfile", 1, SQLITE_UTF8, 0, + readfileFunc, 0, 0); + sqlite3_create_function(db, "writefile", 2, SQLITE_UTF8, 0, + writefileFunc, 0, 0); } } @@ -1670,6 +1885,7 @@ static void resolve_backslashes(char *z){ int i, j; char c; + while( *z && *z!='\\' ) z++; for(i=j=0; (c = z[i])!=0; i++, j++){ if( c=='\\' ){ c = z[++i]; @@ -1695,7 +1911,7 @@ } z[j] = c; } - z[j] = 0; + if( j0 && z[i-1]==';' ){ i--; } + fprintf(f, "%.*s;\n", i, z); + } } /* @@ -1825,22 +2045,23 @@ } /* -** An object used to read a CSV file +** An object used to read a CSV and other files for import. */ -typedef struct CSVReader CSVReader; -struct CSVReader { +typedef struct ImportCtx ImportCtx; +struct ImportCtx { const char *zFile; /* Name of the input file */ FILE *in; /* Read the CSV text from this input stream */ char *z; /* Accumulated text for a field */ int n; /* Number of bytes in z */ int nAlloc; /* Space allocated for z[] */ int nLine; /* Current line number */ int cTerm; /* Character that terminated the most recent field */ - int cSeparator; /* The separator character. (Usually ",") */ + int cColSep; /* The column separator character. (Usually ",") */ + int cRowSep; /* The row separator character. (Usually "\n") */ }; /* Append a single byte to z[] */ -static void csv_append_char(CSVReader *p, int c){ +static void import_append_char(ImportCtx *p, int c){ if( p->n+1>=p->nAlloc ){ p->nAlloc += p->nAlloc + 100; p->z = sqlite3_realloc(p->z, p->nAlloc); @@ -1858,37 +2079,40 @@ ** + Input comes from p->in. ** + Store results in p->z of length p->n. Space to hold p->z comes ** from sqlite3_malloc(). -** + Use p->cSep as the separator. The default is ",". +** + Use p->cSep as the column separator. The default is ",". +** + Use p->rSep as the row separator. The default is "\n". ** + Keep track of the line number in p->nLine. ** + Store the character that terminates the field in p->cTerm. Store ** EOF on end-of-file. ** + Report syntax errors on stderr */ -static char *csv_read_one_field(CSVReader *p){ - int c, pc, ppc; - int cSep = p->cSeparator; +static char *csv_read_one_field(ImportCtx *p){ + int c; + int cSep = p->cColSep; + int rSep = p->cRowSep; p->n = 0; c = fgetc(p->in); if( c==EOF || seenInterrupt ){ p->cTerm = EOF; return 0; } if( c=='"' ){ + int pc, ppc; int startLine = p->nLine; int cQuote = c; pc = ppc = 0; while( 1 ){ c = fgetc(p->in); - if( c=='\n' ) p->nLine++; + if( c==rSep ) p->nLine++; if( c==cQuote ){ if( pc==cQuote ){ pc = 0; continue; } } if( (c==cSep && pc==cQuote) - || (c=='\n' && pc==cQuote) - || (c=='\n' && pc=='\r' && ppc==cQuote) + || (c==rSep && pc==cQuote) + || (c==rSep && pc=='\r' && ppc==cQuote) || (c==EOF && pc==cQuote) ){ do{ p->n--; }while( p->z[p->n]!=cQuote ); @@ -1902,19 +2126,19 @@ if( c==EOF ){ fprintf(stderr, "%s:%d: unterminated %c-quoted field\n", p->zFile, startLine, cQuote); - p->cTerm = EOF; + p->cTerm = c; break; } - csv_append_char(p, c); + import_append_char(p, c); ppc = pc; pc = c; } }else{ - while( c!=EOF && c!=cSep && c!='\n' ){ - csv_append_char(p, c); + while( c!=EOF && c!=cSep && c!=rSep ){ + import_append_char(p, c); c = fgetc(p->in); } - if( c=='\n' ){ + if( c==rSep ){ p->nLine++; if( p->n>0 && p->z[p->n-1]=='\r' ) p->n--; } @@ -1924,13 +2148,47 @@ return p->z; } +/* Read a single field of ASCII delimited text. +** +** + Input comes from p->in. +** + Store results in p->z of length p->n. Space to hold p->z comes +** from sqlite3_malloc(). +** + Use p->cSep as the column separator. The default is "\x1F". +** + Use p->rSep as the row separator. The default is "\x1E". +** + Keep track of the row number in p->nLine. +** + Store the character that terminates the field in p->cTerm. Store +** EOF on end-of-file. +** + Report syntax errors on stderr +*/ +static char *ascii_read_one_field(ImportCtx *p){ + int c; + int cSep = p->cColSep; + int rSep = p->cRowSep; + p->n = 0; + c = fgetc(p->in); + if( c==EOF || seenInterrupt ){ + p->cTerm = EOF; + return 0; + } + while( c!=EOF && c!=cSep && c!=rSep ){ + import_append_char(p, c); + c = fgetc(p->in); + } + if( c==rSep ){ + p->nLine++; + } + p->cTerm = c; + if( p->z ) p->z[p->n] = 0; + return p->z; +} + /* ** Try to transfer data for table zTable. If an error is seen while ** moving forward, try to go backwards. The backwards movement won't ** work for WITHOUT ROWID tables. */ static void tryToCloneData( - struct callback_data *p, + ShellState *p, sqlite3 *newDb, const char *zTable ){ @@ -2043,10 +2301,10 @@ ** sqlite_master table, try again moving backwards. */ static void tryToCloneSchema( - struct callback_data *p, + ShellState *p, sqlite3 *newDb, const char *zWhere, - void (*xForEach)(struct callback_data*,sqlite3*,const char*) + void (*xForEach)(ShellState*,sqlite3*,const char*) ){ sqlite3_stmt *pQuery = 0; char *zQuery = 0; @@ -2117,7 +2375,7 @@ ** as possible out of the main database (which might be corrupt) and write it ** into zNewDb. */ -static void tryToClone(struct callback_data *p, const char *zNewDb){ +static void tryToClone(ShellState *p, const char *zNewDb){ int rc; sqlite3 *newDb = 0; if( access(zNewDb,0)==0 ){ @@ -2129,21 +2387,36 @@ fprintf(stderr, "Cannot create output database: %s\n", sqlite3_errmsg(newDb)); }else{ + sqlite3_exec(p->db, "PRAGMA writable_schema=ON;", 0, 0, 0); sqlite3_exec(newDb, "BEGIN EXCLUSIVE;", 0, 0, 0); tryToCloneSchema(p, newDb, "type='table'", tryToCloneData); tryToCloneSchema(p, newDb, "type!='table'", 0); sqlite3_exec(newDb, "COMMIT;", 0, 0, 0); + sqlite3_exec(p->db, "PRAGMA writable_schema=OFF;", 0, 0, 0); } sqlite3_close(newDb); } /* +** Change the output file back to stdout +*/ +static void output_reset(ShellState *p){ + if( p->outfile[0]=='|' ){ + pclose(p->out); + }else{ + output_file_close(p->out); + } + p->outfile[0] = 0; + p->out = stdout; +} + +/* ** If an input line begins with "." then invoke this routine to ** process that line. ** ** Return 1 on error, 2 to exit, and 0 otherwise. */ -static int do_meta_command(char *zLine, struct callback_data *p){ +static int do_meta_command(char *zLine, ShellState *p){ int i = 1; int nArg = 0; int n, c; @@ -2235,8 +2508,13 @@ sqlite3_close(pDest); }else - if( c=='b' && n>=3 && strncmp(azArg[0], "bail", n)==0 && nArg>1 && nArg<3 ){ - bail_on_error = booleanValue(azArg[1]); + if( c=='b' && n>=3 && strncmp(azArg[0], "bail", n)==0 ){ + if( nArg==2 ){ + bail_on_error = booleanValue(azArg[1]); + }else{ + fprintf(stderr, "Usage: .bail on|off\n"); + rc = 1; + } }else /* The undocumented ".breakpoint" command causes a call to the no-op @@ -2246,12 +2524,17 @@ test_breakpoint(); }else - if( c=='c' && strncmp(azArg[0], "clone", n)==0 && nArg>1 && nArg<3 ){ - tryToClone(p, azArg[1]); + if( c=='c' && strncmp(azArg[0], "clone", n)==0 ){ + if( nArg==2 ){ + tryToClone(p, azArg[1]); + }else{ + fprintf(stderr, "Usage: .clone FILENAME\n"); + rc = 1; + } }else - if( c=='d' && n>1 && strncmp(azArg[0], "databases", n)==0 && nArg==1 ){ - struct callback_data data; + if( c=='d' && n>1 && strncmp(azArg[0], "databases", n)==0 ){ + ShellState data; char *zErrMsg = 0; open_db(p, 0); memcpy(&data, p, sizeof(data)); @@ -2269,11 +2552,16 @@ } }else - if( c=='d' && strncmp(azArg[0], "dump", n)==0 && nArg<3 ){ + if( c=='d' && strncmp(azArg[0], "dump", n)==0 ){ open_db(p, 0); /* When playing back a "dump", the content might appear in an order ** which causes immediate foreign key constraints to be violated. ** So disable foreign-key constraint enforcement to prevent problems. */ + if( nArg!=1 && nArg!=2 ){ + fprintf(stderr, "Usage: .dump ?LIKE-PATTERN?\n"); + rc = 1; + goto meta_command_exit; + } fprintf(p->out, "PRAGMA foreign_keys=OFF;\n"); fprintf(p->out, "BEGIN TRANSACTION;\n"); p->writableSchema = 0; @@ -2318,27 +2606,37 @@ fprintf(p->out, p->nErr ? "ROLLBACK; -- due to errors\n" : "COMMIT;\n"); }else - if( c=='e' && strncmp(azArg[0], "echo", n)==0 && nArg>1 && nArg<3 ){ - p->echoOn = booleanValue(azArg[1]); + if( c=='e' && strncmp(azArg[0], "echo", n)==0 ){ + if( nArg==2 ){ + p->echoOn = booleanValue(azArg[1]); + }else{ + fprintf(stderr, "Usage: .echo on|off\n"); + rc = 1; + } }else - if( c=='e' && strncmp(azArg[0], "eqp", n)==0 && nArg>1 && nArg<3 ){ - p->autoEQP = booleanValue(azArg[1]); + if( c=='e' && strncmp(azArg[0], "eqp", n)==0 ){ + if( nArg==2 ){ + p->autoEQP = booleanValue(azArg[1]); + }else{ + fprintf(stderr, "Usage: .eqp on|off\n"); + rc = 1; + } }else if( c=='e' && strncmp(azArg[0], "exit", n)==0 ){ if( nArg>1 && (rc = (int)integerValue(azArg[1]))!=0 ) exit(rc); rc = 2; }else - if( c=='e' && strncmp(azArg[0], "explain", n)==0 && nArg<3 ){ + if( c=='e' && strncmp(azArg[0], "explain", n)==0 ){ int val = nArg>=2 ? booleanValue(azArg[1]) : 1; if(val == 1) { - if(!p->explainPrev.valid) { - p->explainPrev.valid = 1; - p->explainPrev.mode = p->mode; - p->explainPrev.showHeader = p->showHeader; - memcpy(p->explainPrev.colWidth,p->colWidth,sizeof(p->colWidth)); + if(!p->normalMode.valid) { + p->normalMode.valid = 1; + p->normalMode.mode = p->mode; + p->normalMode.showHeader = p->showHeader; + memcpy(p->normalMode.colWidth,p->colWidth,sizeof(p->colWidth)); } /* We could put this code under the !p->explainValid ** condition so that it does not execute if we are already in @@ -2358,88 +2656,172 @@ p->colWidth[5] = 13; /* P4 */ p->colWidth[6] = 2; /* P5 */ p->colWidth[7] = 13; /* Comment */ - }else if (p->explainPrev.valid) { - p->explainPrev.valid = 0; - p->mode = p->explainPrev.mode; - p->showHeader = p->explainPrev.showHeader; - memcpy(p->colWidth,p->explainPrev.colWidth,sizeof(p->colWidth)); + }else if (p->normalMode.valid) { + p->normalMode.valid = 0; + p->mode = p->normalMode.mode; + p->showHeader = p->normalMode.showHeader; + memcpy(p->colWidth,p->normalMode.colWidth,sizeof(p->colWidth)); } }else - if( c=='h' && (strncmp(azArg[0], "header", n)==0 || - strncmp(azArg[0], "headers", n)==0) && nArg>1 && nArg<3 ){ - p->showHeader = booleanValue(azArg[1]); + if( c=='f' && strncmp(azArg[0], "fullschema", n)==0 ){ + ShellState data; + char *zErrMsg = 0; + int doStats = 0; + if( nArg!=1 ){ + fprintf(stderr, "Usage: .fullschema\n"); + rc = 1; + goto meta_command_exit; + } + open_db(p, 0); + memcpy(&data, p, sizeof(data)); + data.showHeader = 0; + data.mode = MODE_Semi; + rc = sqlite3_exec(p->db, + "SELECT sql FROM" + " (SELECT sql sql, type type, tbl_name tbl_name, name name, rowid x" + " FROM sqlite_master UNION ALL" + " SELECT sql, type, tbl_name, name, rowid FROM sqlite_temp_master) " + "WHERE type!='meta' AND sql NOTNULL AND name NOT LIKE 'sqlite_%' " + "ORDER BY rowid", + callback, &data, &zErrMsg + ); + if( rc==SQLITE_OK ){ + sqlite3_stmt *pStmt; + rc = sqlite3_prepare_v2(p->db, + "SELECT rowid FROM sqlite_master" + " WHERE name GLOB 'sqlite_stat[134]'", + -1, &pStmt, 0); + doStats = sqlite3_step(pStmt)==SQLITE_ROW; + sqlite3_finalize(pStmt); + } + if( doStats==0 ){ + fprintf(p->out, "/* No STAT tables available */\n"); + }else{ + fprintf(p->out, "ANALYZE sqlite_master;\n"); + sqlite3_exec(p->db, "SELECT 'ANALYZE sqlite_master'", + callback, &data, &zErrMsg); + data.mode = MODE_Insert; + data.zDestTable = "sqlite_stat1"; + shell_exec(p->db, "SELECT * FROM sqlite_stat1", + shell_callback, &data,&zErrMsg); + data.zDestTable = "sqlite_stat3"; + shell_exec(p->db, "SELECT * FROM sqlite_stat3", + shell_callback, &data,&zErrMsg); + data.zDestTable = "sqlite_stat4"; + shell_exec(p->db, "SELECT * FROM sqlite_stat4", + shell_callback, &data, &zErrMsg); + fprintf(p->out, "ANALYZE sqlite_master;\n"); + } }else - if( c=='h' && strncmp(azArg[0], "help", n)==0 ){ - fprintf(stderr,"%s",zHelp); - if( HAS_TIMER ){ - fprintf(stderr,"%s",zTimerHelp); + if( c=='h' && strncmp(azArg[0], "headers", n)==0 ){ + if( nArg==2 ){ + p->showHeader = booleanValue(azArg[1]); + }else{ + fprintf(stderr, "Usage: .headers on|off\n"); + rc = 1; } }else - if( c=='i' && strncmp(azArg[0], "import", n)==0 && nArg==3 ){ - char *zTable = azArg[2]; /* Insert data into this table */ - char *zFile = azArg[1]; /* Name of file to extra content from */ + if( c=='h' && strncmp(azArg[0], "help", n)==0 ){ + fprintf(p->out, "%s", zHelp); + }else + + if( c=='i' && strncmp(azArg[0], "import", n)==0 ){ + char *zTable; /* Insert data into this table */ + char *zFile; /* Name of file to extra content from */ sqlite3_stmt *pStmt = NULL; /* A statement */ int nCol; /* Number of columns in the table */ int nByte; /* Number of bytes in an SQL string */ int i, j; /* Loop counters */ int needCommit; /* True to COMMIT or ROLLBACK at end */ - int nSep; /* Number of bytes in p->separator[] */ + int nSep; /* Number of bytes in p->colSeparator[] */ char *zSql; /* An SQL statement */ - CSVReader sCsv; /* Reader context */ + ImportCtx sCtx; /* Reader context */ + char *(*xRead)(ImportCtx*); /* Procedure to read one value */ int (*xCloser)(FILE*); /* Procedure to close th3 connection */ + if( nArg!=3 ){ + fprintf(stderr, "Usage: .import FILE TABLE\n"); + goto meta_command_exit; + } + zFile = azArg[1]; + zTable = azArg[2]; seenInterrupt = 0; - memset(&sCsv, 0, sizeof(sCsv)); + memset(&sCtx, 0, sizeof(sCtx)); open_db(p, 0); - nSep = strlen30(p->separator); + nSep = strlen30(p->colSeparator); if( nSep==0 ){ - fprintf(stderr, "Error: non-null separator required for import\n"); + fprintf(stderr, "Error: non-null column separator required for import\n"); return 1; } if( nSep>1 ){ - fprintf(stderr, "Error: multi-character separators not allowed" + fprintf(stderr, "Error: multi-character column separators not allowed" " for import\n"); return 1; } - sCsv.zFile = zFile; - sCsv.nLine = 1; - if( sCsv.zFile[0]=='|' ){ - sCsv.in = popen(sCsv.zFile+1, "r"); - sCsv.zFile = ""; + nSep = strlen30(p->rowSeparator); + if( nSep==0 ){ + fprintf(stderr, "Error: non-null row separator required for import\n"); + return 1; + } + if( nSep==2 && p->mode==MODE_Csv && strcmp(p->rowSeparator, SEP_CrLf)==0 ){ + /* When importing CSV (only), if the row separator is set to the + ** default output row separator, change it to the default input + ** row separator. This avoids having to maintain different input + ** and output row separators. */ + sqlite3_snprintf(sizeof(p->rowSeparator), p->rowSeparator, SEP_Row); + nSep = strlen30(p->rowSeparator); + } + if( nSep>1 ){ + fprintf(stderr, "Error: multi-character row separators not allowed" + " for import\n"); + return 1; + } + sCtx.zFile = zFile; + sCtx.nLine = 1; + if( sCtx.zFile[0]=='|' ){ + sCtx.in = popen(sCtx.zFile+1, "r"); + sCtx.zFile = ""; xCloser = pclose; }else{ - sCsv.in = fopen(sCsv.zFile, "rb"); + sCtx.in = fopen(sCtx.zFile, "rb"); xCloser = fclose; } - if( sCsv.in==0 ){ + if( p->mode==MODE_Ascii ){ + xRead = ascii_read_one_field; + }else{ + xRead = csv_read_one_field; + } + if( sCtx.in==0 ){ fprintf(stderr, "Error: cannot open \"%s\"\n", zFile); return 1; } - sCsv.cSeparator = p->separator[0]; + sCtx.cColSep = p->colSeparator[0]; + sCtx.cRowSep = p->rowSeparator[0]; zSql = sqlite3_mprintf("SELECT * FROM %s", zTable); if( zSql==0 ){ fprintf(stderr, "Error: out of memory\n"); - xCloser(sCsv.in); + xCloser(sCtx.in); return 1; } nByte = strlen30(zSql); rc = sqlite3_prepare_v2(p->db, zSql, -1, &pStmt, 0); + import_append_char(&sCtx, 0); /* To ensure sCtx.z is allocated */ if( rc && sqlite3_strglob("no such table: *", sqlite3_errmsg(db))==0 ){ char *zCreate = sqlite3_mprintf("CREATE TABLE %s", zTable); char cSep = '('; - while( csv_read_one_field(&sCsv) ){ - zCreate = sqlite3_mprintf("%z%c\n \"%s\" TEXT", zCreate, cSep, sCsv.z); + while( xRead(&sCtx) ){ + zCreate = sqlite3_mprintf("%z%c\n \"%s\" TEXT", zCreate, cSep, sCtx.z); cSep = ','; - if( sCsv.cTerm!=sCsv.cSeparator ) break; + if( sCtx.cTerm!=sCtx.cColSep ) break; } if( cSep=='(' ){ sqlite3_free(zCreate); - sqlite3_free(sCsv.z); - xCloser(sCsv.in); - fprintf(stderr,"%s: empty file\n", sCsv.zFile); + sqlite3_free(sCtx.z); + xCloser(sCtx.in); + fprintf(stderr,"%s: empty file\n", sCtx.zFile); return 1; } zCreate = sqlite3_mprintf("%z\n)", zCreate); @@ -2448,8 +2830,8 @@ if( rc ){ fprintf(stderr, "CREATE TABLE %s(...) failed: %s\n", zTable, sqlite3_errmsg(db)); - sqlite3_free(sCsv.z); - xCloser(sCsv.in); + sqlite3_free(sCtx.z); + xCloser(sCtx.in); return 1; } rc = sqlite3_prepare_v2(p->db, zSql, -1, &pStmt, 0); @@ -2458,7 +2840,7 @@ if( rc ){ if (pStmt) sqlite3_finalize(pStmt); fprintf(stderr,"Error: %s\n", sqlite3_errmsg(db)); - xCloser(sCsv.in); + xCloser(sCtx.in); return 1; } nCol = sqlite3_column_count(pStmt); @@ -2468,7 +2850,7 @@ zSql = sqlite3_malloc( nByte*2 + 20 + nCol*2 ); if( zSql==0 ){ fprintf(stderr, "Error: out of memory\n"); - xCloser(sCsv.in); + xCloser(sCtx.in); return 1; } sqlite3_snprintf(nByte+20, zSql, "INSERT INTO \"%w\" VALUES(?", zTable); @@ -2484,52 +2866,62 @@ if( rc ){ fprintf(stderr, "Error: %s\n", sqlite3_errmsg(db)); if (pStmt) sqlite3_finalize(pStmt); - xCloser(sCsv.in); + xCloser(sCtx.in); return 1; } needCommit = sqlite3_get_autocommit(db); if( needCommit ) sqlite3_exec(db, "BEGIN", 0, 0, 0); do{ - int startLine = sCsv.nLine; + int startLine = sCtx.nLine; for(i=0; imode==MODE_Ascii && (z==0 || z[0]==0) && i==0 ) break; sqlite3_bind_text(pStmt, i+1, z, -1, SQLITE_TRANSIENT); - if( i=nCol ){ sqlite3_step(pStmt); rc = sqlite3_reset(pStmt); if( rc!=SQLITE_OK ){ - fprintf(stderr, "%s:%d: INSERT failed: %s\n", sCsv.zFile, startLine, + fprintf(stderr, "%s:%d: INSERT failed: %s\n", sCtx.zFile, startLine, sqlite3_errmsg(db)); } } - }while( sCsv.cTerm!=EOF ); + }while( sCtx.cTerm!=EOF ); - xCloser(sCsv.in); - sqlite3_free(sCsv.z); + xCloser(sCtx.in); + sqlite3_free(sCtx.z); sqlite3_finalize(pStmt); if( needCommit ) sqlite3_exec(db, "COMMIT", 0, 0, 0); }else - if( c=='i' && strncmp(azArg[0], "indices", n)==0 && nArg<3 ){ - struct callback_data data; + if( c=='i' && strncmp(azArg[0], "indices", n)==0 ){ + ShellState data; char *zErrMsg = 0; open_db(p, 0); memcpy(&data, p, sizeof(data)); @@ -2545,7 +2937,7 @@ "ORDER BY 1", callback, &data, &zErrMsg ); - }else{ + }else if( nArg==2 ){ zShellStatic = azArg[1]; rc = sqlite3_exec(p->db, "SELECT name FROM sqlite_master " @@ -2557,6 +2949,10 @@ callback, &data, &zErrMsg ); zShellStatic = 0; + }else{ + fprintf(stderr, "Usage: .indices ?LIKE-PATTERN?\n"); + rc = 1; + goto meta_command_exit; } if( zErrMsg ){ fprintf(stderr,"Error: %s\n", zErrMsg); @@ -2592,9 +2988,14 @@ #endif #ifndef SQLITE_OMIT_LOAD_EXTENSION - if( c=='l' && strncmp(azArg[0], "load", n)==0 && nArg>=2 ){ + if( c=='l' && strncmp(azArg[0], "load", n)==0 ){ const char *zFile, *zProc; char *zErrMsg = 0; + if( nArg<2 ){ + fprintf(stderr, "Usage: .load FILE ?ENTRYPOINT?\n"); + rc = 1; + goto meta_command_exit; + } zFile = azArg[1]; zProc = nArg>=3 ? azArg[2] : 0; open_db(p, 0); @@ -2607,62 +3008,63 @@ }else #endif - if( c=='l' && strncmp(azArg[0], "log", n)==0 && nArg>=2 ){ - const char *zFile = azArg[1]; - output_file_close(p->pLog); - p->pLog = output_file_open(zFile); + if( c=='l' && strncmp(azArg[0], "log", n)==0 ){ + if( nArg!=2 ){ + fprintf(stderr, "Usage: .log FILENAME\n"); + rc = 1; + }else{ + const char *zFile = azArg[1]; + output_file_close(p->pLog); + p->pLog = output_file_open(zFile); + } }else - if( c=='m' && strncmp(azArg[0], "mode", n)==0 && nArg==2 ){ - int n2 = strlen30(azArg[1]); - if( (n2==4 && strncmp(azArg[1],"line",n2)==0) - || - (n2==5 && strncmp(azArg[1],"lines",n2)==0) ){ + if( c=='m' && strncmp(azArg[0], "mode", n)==0 ){ + const char *zMode = nArg>=2 ? azArg[1] : ""; + int n2 = (int)strlen(zMode); + int c2 = zMode[0]; + if( c2=='l' && n2>2 && strncmp(azArg[1],"lines",n2)==0 ){ p->mode = MODE_Line; - }else if( (n2==6 && strncmp(azArg[1],"column",n2)==0) - || - (n2==7 && strncmp(azArg[1],"columns",n2)==0) ){ + }else if( c2=='c' && strncmp(azArg[1],"columns",n2)==0 ){ p->mode = MODE_Column; - }else if( n2==4 && strncmp(azArg[1],"list",n2)==0 ){ + }else if( c2=='l' && n2>2 && strncmp(azArg[1],"list",n2)==0 ){ p->mode = MODE_List; - }else if( n2==4 && strncmp(azArg[1],"html",n2)==0 ){ + }else if( c2=='h' && strncmp(azArg[1],"html",n2)==0 ){ p->mode = MODE_Html; - }else if( n2==3 && strncmp(azArg[1],"tcl",n2)==0 ){ + }else if( c2=='t' && strncmp(azArg[1],"tcl",n2)==0 ){ p->mode = MODE_Tcl; - sqlite3_snprintf(sizeof(p->separator), p->separator, " "); - }else if( n2==3 && strncmp(azArg[1],"csv",n2)==0 ){ + sqlite3_snprintf(sizeof(p->colSeparator), p->colSeparator, SEP_Space); + }else if( c2=='c' && strncmp(azArg[1],"csv",n2)==0 ){ p->mode = MODE_Csv; - sqlite3_snprintf(sizeof(p->separator), p->separator, ","); - }else if( n2==4 && strncmp(azArg[1],"tabs",n2)==0 ){ + sqlite3_snprintf(sizeof(p->colSeparator), p->colSeparator, SEP_Comma); + sqlite3_snprintf(sizeof(p->rowSeparator), p->rowSeparator, SEP_CrLf); + }else if( c2=='t' && strncmp(azArg[1],"tabs",n2)==0 ){ p->mode = MODE_List; - sqlite3_snprintf(sizeof(p->separator), p->separator, "\t"); - }else if( n2==6 && strncmp(azArg[1],"insert",n2)==0 ){ + sqlite3_snprintf(sizeof(p->colSeparator), p->colSeparator, SEP_Tab); + }else if( c2=='i' && strncmp(azArg[1],"insert",n2)==0 ){ p->mode = MODE_Insert; - set_table_name(p, "table"); + set_table_name(p, nArg>=3 ? azArg[2] : "table"); + }else if( c2=='a' && strncmp(azArg[1],"ascii",n2)==0 ){ + p->mode = MODE_Ascii; + sqlite3_snprintf(sizeof(p->colSeparator), p->colSeparator, SEP_Unit); + sqlite3_snprintf(sizeof(p->rowSeparator), p->rowSeparator, SEP_Record); }else { fprintf(stderr,"Error: mode should be one of: " - "column csv html insert line list tabs tcl\n"); + "ascii column csv html insert line list tabs tcl\n"); rc = 1; } }else - if( c=='m' && strncmp(azArg[0], "mode", n)==0 && nArg==3 ){ - int n2 = strlen30(azArg[1]); - if( n2==6 && strncmp(azArg[1],"insert",n2)==0 ){ - p->mode = MODE_Insert; - set_table_name(p, azArg[2]); - }else { - fprintf(stderr, "Error: invalid arguments: " - " \"%s\". Enter \".help\" for help\n", azArg[2]); + if( c=='n' && strncmp(azArg[0], "nullvalue", n)==0 ){ + if( nArg==2 ){ + sqlite3_snprintf(sizeof(p->nullValue), p->nullValue, + "%.*s", (int)ArraySize(p->nullValue)-1, azArg[1]); + }else{ + fprintf(stderr, "Usage: .nullvalue STRING\n"); rc = 1; } }else - if( c=='n' && strncmp(azArg[0], "nullvalue", n)==0 && nArg==2 ) { - sqlite3_snprintf(sizeof(p->nullvalue), p->nullvalue, - "%.*s", (int)ArraySize(p->nullvalue)-1, azArg[1]); - }else - if( c=='o' && strncmp(azArg[0], "open", n)==0 && n>=2 ){ sqlite3 *savedDb = p->db; const char *zSavedFilename = p->zDbFilename; @@ -2683,32 +3085,45 @@ } }else - if( c=='o' && strncmp(azArg[0], "output", n)==0 && nArg==2 ){ - if( p->outfile[0]=='|' ){ - pclose(p->out); + if( c=='o' + && (strncmp(azArg[0], "output", n)==0 || strncmp(azArg[0], "once", n)==0) + ){ + const char *zFile = nArg>=2 ? azArg[1] : "stdout"; + if( nArg>2 ){ + fprintf(stderr, "Usage: .%s FILE\n", azArg[0]); + rc = 1; + goto meta_command_exit; + } + if( n>1 && strncmp(azArg[0], "once", n)==0 ){ + if( nArg<2 ){ + fprintf(stderr, "Usage: .once FILE\n"); + rc = 1; + goto meta_command_exit; + } + p->outCount = 2; }else{ - output_file_close(p->out); + p->outCount = 0; } - p->outfile[0] = 0; - if( azArg[1][0]=='|' ){ - p->out = popen(&azArg[1][1], "w"); + output_reset(p); + if( zFile[0]=='|' ){ + p->out = popen(zFile + 1, "w"); if( p->out==0 ){ - fprintf(stderr,"Error: cannot open pipe \"%s\"\n", &azArg[1][1]); + fprintf(stderr,"Error: cannot open pipe \"%s\"\n", zFile + 1); p->out = stdout; rc = 1; }else{ - sqlite3_snprintf(sizeof(p->outfile), p->outfile, "%s", azArg[1]); + sqlite3_snprintf(sizeof(p->outfile), p->outfile, "%s", zFile); } }else{ - p->out = output_file_open(azArg[1]); + p->out = output_file_open(zFile); if( p->out==0 ){ - if( strcmp(azArg[1],"off")!=0 ){ - fprintf(stderr,"Error: cannot write to \"%s\"\n", azArg[1]); + if( strcmp(zFile,"off")!=0 ){ + fprintf(stderr,"Error: cannot write to \"%s\"\n", zFile); } p->out = stdout; rc = 1; } else { - sqlite3_snprintf(sizeof(p->outfile), p->outfile, "%s", azArg[1]); + sqlite3_snprintf(sizeof(p->outfile), p->outfile, "%s", zFile); } } }else @@ -2722,7 +3137,7 @@ fprintf(p->out, "\n"); }else - if( c=='p' && strncmp(azArg[0], "prompt", n)==0 && (nArg==2 || nArg==3)){ + if( c=='p' && strncmp(azArg[0], "prompt", n)==0 ){ if( nArg >= 2) { strncpy(mainPrompt,azArg[1],(int)ArraySize(mainPrompt)-1); } @@ -2731,12 +3146,18 @@ } }else - if( c=='q' && strncmp(azArg[0], "quit", n)==0 && nArg==1 ){ + if( c=='q' && strncmp(azArg[0], "quit", n)==0 ){ rc = 2; }else - if( c=='r' && n>=3 && strncmp(azArg[0], "read", n)==0 && nArg==2 ){ - FILE *alt = fopen(azArg[1], "rb"); + if( c=='r' && n>=3 && strncmp(azArg[0], "read", n)==0 ){ + FILE *alt; + if( nArg!=2 ){ + fprintf(stderr, "Usage: .read FILE\n"); + rc = 1; + goto meta_command_exit; + } + alt = fopen(azArg[1], "rb"); if( alt==0 ){ fprintf(stderr,"Error: cannot open \"%s\"\n", azArg[1]); rc = 1; @@ -2746,7 +3167,7 @@ } }else - if( c=='r' && n>=3 && strncmp(azArg[0], "restore", n)==0 && nArg>1 && nArg<4){ + if( c=='r' && n>=3 && strncmp(azArg[0], "restore", n)==0 ){ const char *zSrcFile; const char *zDb; sqlite3 *pSrc; @@ -2756,9 +3177,13 @@ if( nArg==2 ){ zSrcFile = azArg[1]; zDb = "main"; - }else{ + }else if( nArg==3 ){ zSrcFile = azArg[2]; zDb = azArg[1]; + }else{ + fprintf(stderr, "Usage: .restore ?DB? FILE\n"); + rc = 1; + goto meta_command_exit; } rc = sqlite3_open(zSrcFile, &pSrc); if( rc!=SQLITE_OK ){ @@ -2793,14 +3218,27 @@ sqlite3_close(pSrc); }else - if( c=='s' && strncmp(azArg[0], "schema", n)==0 && nArg<3 ){ - struct callback_data data; + + if( c=='s' && strncmp(azArg[0], "scanstats", n)==0 ){ + if( nArg==2 ){ + p->scanstatsOn = booleanValue(azArg[1]); +#ifndef SQLITE_ENABLE_STMT_SCANSTATUS + fprintf(stderr, "Warning: .scanstats not available in this build.\n"); +#endif + }else{ + fprintf(stderr, "Usage: .scanstats on|off\n"); + rc = 1; + } + }else + + if( c=='s' && strncmp(azArg[0], "schema", n)==0 ){ + ShellState data; char *zErrMsg = 0; open_db(p, 0); memcpy(&data, p, sizeof(data)); data.showHeader = 0; data.mode = MODE_Semi; - if( nArg>1 ){ + if( nArg==2 ){ int i; for(i=0; azArg[1][i]; i++) azArg[1][i] = ToLower(azArg[1][i]); if( strcmp(azArg[1],"sqlite_master")==0 ){ @@ -2844,16 +3282,20 @@ callback, &data, &zErrMsg); zShellStatic = 0; } - }else{ + }else if( nArg==1 ){ rc = sqlite3_exec(p->db, "SELECT sql FROM " " (SELECT sql sql, type type, tbl_name tbl_name, name name, rowid x" " FROM sqlite_master UNION ALL" " SELECT sql, type, tbl_name, name, rowid FROM sqlite_temp_master) " - "WHERE type!='meta' AND sql NOTNULL AND name NOT LIKE 'sqlite_%'" + "WHERE type!='meta' AND sql NOTNULL AND name NOT LIKE 'sqlite_%' " "ORDER BY rowid", callback, &data, &zErrMsg ); + }else{ + fprintf(stderr, "Usage: .schema ?LIKE-PATTERN?\n"); + rc = 1; + goto meta_command_exit; } if( zErrMsg ){ fprintf(stderr,"Error: %s\n", zErrMsg); @@ -2867,6 +3309,15 @@ } }else + +#if defined(SQLITE_DEBUG) && defined(SQLITE_ENABLE_SELECTTRACE) + if( c=='s' && n==11 && strncmp(azArg[0], "selecttrace", n)==0 ){ + extern int sqlite3SelectTrace; + sqlite3SelectTrace = nArg>=2 ? booleanValue(azArg[1]) : 0xff; + }else +#endif + + #ifdef SQLITE_DEBUG /* Undocumented commands for internal testing. Subject to change ** without notice. */ @@ -2883,46 +3334,89 @@ for(i=1; iout, "%s", zBuf); } } }else #endif - if( c=='s' && strncmp(azArg[0], "separator", n)==0 && nArg==2 ){ - sqlite3_snprintf(sizeof(p->separator), p->separator, - "%.*s", (int)sizeof(p->separator)-1, azArg[1]); + if( c=='s' && strncmp(azArg[0], "separator", n)==0 ){ + if( nArg<2 || nArg>3 ){ + fprintf(stderr, "Usage: .separator COL ?ROW?\n"); + rc = 1; + } + if( nArg>=2 ){ + sqlite3_snprintf(sizeof(p->colSeparator), p->colSeparator, + "%.*s", (int)ArraySize(p->colSeparator)-1, azArg[1]); + } + if( nArg>=3 ){ + sqlite3_snprintf(sizeof(p->rowSeparator), p->rowSeparator, + "%.*s", (int)ArraySize(p->rowSeparator)-1, azArg[2]); + } }else - if( c=='s' && strncmp(azArg[0], "show", n)==0 && nArg==1 ){ + if( c=='s' + && (strncmp(azArg[0], "shell", n)==0 || strncmp(azArg[0],"system",n)==0) + ){ + char *zCmd; + int i, x; + if( nArg<2 ){ + fprintf(stderr, "Usage: .system COMMAND\n"); + rc = 1; + goto meta_command_exit; + } + zCmd = sqlite3_mprintf(strchr(azArg[1],' ')==0?"%s":"\"%s\"", azArg[1]); + for(i=2; iout,"%9.9s: %s\n","echo", p->echoOn ? "on" : "off"); - fprintf(p->out,"%9.9s: %s\n","eqp", p->autoEQP ? "on" : "off"); - fprintf(p->out,"%9.9s: %s\n","explain", p->explainPrev.valid ? "on" :"off"); - fprintf(p->out,"%9.9s: %s\n","headers", p->showHeader ? "on" : "off"); - fprintf(p->out,"%9.9s: %s\n","mode", modeDescr[p->mode]); - fprintf(p->out,"%9.9s: ", "nullvalue"); - output_c_string(p->out, p->nullvalue); + if( nArg!=1 ){ + fprintf(stderr, "Usage: .show\n"); + rc = 1; + goto meta_command_exit; + } + fprintf(p->out,"%12.12s: %s\n","echo", p->echoOn ? "on" : "off"); + fprintf(p->out,"%12.12s: %s\n","eqp", p->autoEQP ? "on" : "off"); + fprintf(p->out,"%9.9s: %s\n","explain", p->normalMode.valid ? "on" :"off"); + fprintf(p->out,"%12.12s: %s\n","headers", p->showHeader ? "on" : "off"); + fprintf(p->out,"%12.12s: %s\n","mode", modeDescr[p->mode]); + fprintf(p->out,"%12.12s: ", "nullvalue"); + output_c_string(p->out, p->nullValue); fprintf(p->out, "\n"); - fprintf(p->out,"%9.9s: %s\n","output", + fprintf(p->out,"%12.12s: %s\n","output", strlen30(p->outfile) ? p->outfile : "stdout"); - fprintf(p->out,"%9.9s: ", "separator"); - output_c_string(p->out, p->separator); + fprintf(p->out,"%12.12s: ", "colseparator"); + output_c_string(p->out, p->colSeparator); fprintf(p->out, "\n"); - fprintf(p->out,"%9.9s: %s\n","stats", p->statsOn ? "on" : "off"); - fprintf(p->out,"%9.9s: ","width"); + fprintf(p->out,"%12.12s: ", "rowseparator"); + output_c_string(p->out, p->rowSeparator); + fprintf(p->out, "\n"); + fprintf(p->out,"%12.12s: %s\n","stats", p->statsOn ? "on" : "off"); + fprintf(p->out,"%12.12s: ","width"); for (i=0;i<(int)ArraySize(p->colWidth) && p->colWidth[i] != 0;i++) { fprintf(p->out,"%d ",p->colWidth[i]); } fprintf(p->out,"\n"); }else - if( c=='s' && strncmp(azArg[0], "stats", n)==0 && nArg>1 && nArg<3 ){ - p->statsOn = booleanValue(azArg[1]); + if( c=='s' && strncmp(azArg[0], "stats", n)==0 ){ + if( nArg==2 ){ + p->statsOn = booleanValue(azArg[1]); + }else{ + fprintf(stderr, "Usage: .stats on|off\n"); + rc = 1; + } }else - if( c=='t' && n>1 && strncmp(azArg[0], "tables", n)==0 && nArg<3 ){ + if( c=='t' && n>1 && strncmp(azArg[0], "tables", n)==0 ){ sqlite3_stmt *pStmt; char **azResult; int nRow, nAlloc; @@ -2997,7 +3491,7 @@ for(i=0; iout, "%s%-*s", zSp, maxlen, azResult[j] ? azResult[j] : ""); + fprintf(p->out, "%s%-*s", zSp, maxlen, azResult[j] ? azResult[j]:""); } fprintf(p->out, "\n"); } @@ -3024,6 +3518,7 @@ { "optimizations", SQLITE_TESTCTRL_OPTIMIZATIONS }, { "iskeyword", SQLITE_TESTCTRL_ISKEYWORD }, { "scratchmalloc", SQLITE_TESTCTRL_SCRATCHMALLOC }, + { "byteorder", SQLITE_TESTCTRL_BYTEORDER }, }; int testctrl = -1; int rc = 0; @@ -3064,9 +3559,10 @@ break; /* sqlite3_test_control(int) */ - case SQLITE_TESTCTRL_PRNG_SAVE: - case SQLITE_TESTCTRL_PRNG_RESTORE: + case SQLITE_TESTCTRL_PRNG_SAVE: + case SQLITE_TESTCTRL_PRNG_RESTORE: case SQLITE_TESTCTRL_PRNG_RESET: + case SQLITE_TESTCTRL_BYTEORDER: if( nArg==2 ){ rc = sqlite3_test_control(testctrl); fprintf(p->out, "%d (0x%08x)\n", rc, rc); @@ -3126,20 +3622,32 @@ } }else - if( c=='t' && n>4 && strncmp(azArg[0], "timeout", n)==0 && nArg==2 ){ + if( c=='t' && n>4 && strncmp(azArg[0], "timeout", n)==0 ){ open_db(p, 0); - sqlite3_busy_timeout(p->db, (int)integerValue(azArg[1])); + sqlite3_busy_timeout(p->db, nArg>=2 ? (int)integerValue(azArg[1]) : 0); }else - if( HAS_TIMER && c=='t' && n>=5 && strncmp(azArg[0], "timer", n)==0 - && nArg==2 - ){ - enableTimer = booleanValue(azArg[1]); + if( c=='t' && n>=5 && strncmp(azArg[0], "timer", n)==0 ){ + if( nArg==2 ){ + enableTimer = booleanValue(azArg[1]); + if( enableTimer && !HAS_TIMER ){ + fprintf(stderr, "Error: timer not available on this system.\n"); + enableTimer = 0; + } + }else{ + fprintf(stderr, "Usage: .timer on|off\n"); + rc = 1; + } }else - if( c=='t' && strncmp(azArg[0], "trace", n)==0 && nArg>1 ){ + if( c=='t' && strncmp(azArg[0], "trace", n)==0 ){ open_db(p, 0); output_file_close(p->traceOut); + if( nArg!=2 ){ + fprintf(stderr, "Usage: .trace FILE|off\n"); + rc = 1; + goto meta_command_exit; + } p->traceOut = output_file_open(azArg[1]); #if !defined(SQLITE_OMIT_TRACE) && !defined(SQLITE_OMIT_FLOATING_POINT) if( p->traceOut==0 ){ @@ -3150,6 +3658,71 @@ #endif }else +#if SQLITE_USER_AUTHENTICATION + if( c=='u' && strncmp(azArg[0], "user", n)==0 ){ + if( nArg<2 ){ + fprintf(stderr, "Usage: .user SUBCOMMAND ...\n"); + rc = 1; + goto meta_command_exit; + } + open_db(p, 0); + if( strcmp(azArg[1],"login")==0 ){ + if( nArg!=4 ){ + fprintf(stderr, "Usage: .user login USER PASSWORD\n"); + rc = 1; + goto meta_command_exit; + } + rc = sqlite3_user_authenticate(p->db, azArg[2], azArg[3], + (int)strlen(azArg[3])); + if( rc ){ + fprintf(stderr, "Authentication failed for user %s\n", azArg[2]); + rc = 1; + } + }else if( strcmp(azArg[1],"add")==0 ){ + if( nArg!=5 ){ + fprintf(stderr, "Usage: .user add USER PASSWORD ISADMIN\n"); + rc = 1; + goto meta_command_exit; + } + rc = sqlite3_user_add(p->db, azArg[2], + azArg[3], (int)strlen(azArg[3]), + booleanValue(azArg[4])); + if( rc ){ + fprintf(stderr, "User-Add failed: %d\n", rc); + rc = 1; + } + }else if( strcmp(azArg[1],"edit")==0 ){ + if( nArg!=5 ){ + fprintf(stderr, "Usage: .user edit USER PASSWORD ISADMIN\n"); + rc = 1; + goto meta_command_exit; + } + rc = sqlite3_user_change(p->db, azArg[2], + azArg[3], (int)strlen(azArg[3]), + booleanValue(azArg[4])); + if( rc ){ + fprintf(stderr, "User-Edit failed: %d\n", rc); + rc = 1; + } + }else if( strcmp(azArg[1],"delete")==0 ){ + if( nArg!=3 ){ + fprintf(stderr, "Usage: .user delete USER\n"); + rc = 1; + goto meta_command_exit; + } + rc = sqlite3_user_delete(p->db, azArg[2]); + if( rc ){ + fprintf(stderr, "User-Delete failed: %d\n", rc); + rc = 1; + } + }else{ + fprintf(stderr, "Usage: .user login|add|edit|delete ...\n"); + rc = 1; + goto meta_command_exit; + } + }else +#endif /* SQLITE_USER_AUTHENTICATION */ + if( c=='v' && strncmp(azArg[0], "version", n)==0 ){ fprintf(p->out, "SQLite %s %s\n" /*extra-version-info*/, sqlite3_libversion(), sqlite3_sourceid()); @@ -3170,11 +3743,11 @@ #if defined(SQLITE_DEBUG) && defined(SQLITE_ENABLE_WHERETRACE) if( c=='w' && strncmp(azArg[0], "wheretrace", n)==0 ){ extern int sqlite3WhereTrace; - sqlite3WhereTrace = booleanValue(azArg[1]); + sqlite3WhereTrace = nArg>=2 ? booleanValue(azArg[1]) : 0xff; }else #endif - if( c=='w' && strncmp(azArg[0], "width", n)==0 && nArg>1 ){ + if( c=='w' && strncmp(azArg[0], "width", n)==0 ){ int j; assert( nArg<=ArraySize(azArg) ); for(j=1; jcolWidth); j++){ @@ -3188,6 +3761,11 @@ rc = 1; } +meta_command_exit: + if( p->outCount ){ + p->outCount--; + if( p->outCount==0 ) output_reset(p); + } return rc; } @@ -3265,7 +3843,7 @@ ** ** Return the number of errors. */ -static int process_input(struct callback_data *p, FILE *in){ +static int process_input(ShellState *p, FILE *in){ char *zLine = 0; /* A single input line */ char *zSql = 0; /* Accumulated SQL text */ int nLine; /* Length of current line */ @@ -3355,6 +3933,10 @@ errCnt++; } nSql = 0; + if( p->outCount ){ + output_reset(p); + p->outCount = 0; + } }else if( nSql && _all_whitespace(zSql) ){ if( p->echoOn ) printf("%s\n", zSql); nSql = 0; @@ -3363,6 +3945,7 @@ if( nSql ){ if( !_all_whitespace(zSql) ){ fprintf(stderr, "Error: incomplete SQL: %s\n", zSql); + errCnt++; } free(zSql); } @@ -3378,7 +3961,8 @@ static char *home_dir = NULL; if( home_dir ) return home_dir; -#if !defined(_WIN32) && !defined(WIN32) && !defined(_WIN32_WCE) && !defined(__RTP__) && !defined(_WRS_KERNEL) +#if !defined(_WIN32) && !defined(WIN32) && !defined(_WIN32_WCE) \ + && !defined(__RTP__) && !defined(_WRS_KERNEL) { struct passwd *pwent; uid_t uid = getuid(); @@ -3440,7 +4024,7 @@ ** Returns the number of errors. */ static int process_sqliterc( - struct callback_data *p, /* Configuration data */ + ShellState *p, /* Configuration data */ const char *sqliterc_override /* Name of config file. NULL to use default */ ){ char *home_dir = NULL; @@ -3477,6 +4061,7 @@ ** Show available command line options */ static const char zOptions[] = + " -ascii set output mode to 'ascii'\n" " -bail stop after hitting an error\n" " -batch force batch I/O\n" " -column set output mode to 'column'\n" @@ -3493,12 +4078,16 @@ " -interactive force interactive I/O\n" " -line set output mode to 'line'\n" " -list set output mode to 'list'\n" + " -lookaside SIZE N use N entries of SZ bytes for lookaside memory\n" " -mmap N default mmap size set to N\n" #ifdef SQLITE_ENABLE_MULTIPLEX " -multiplex enable the multiplexor VFS\n" #endif + " -newline SEP set output row separator. Default: '\\n'\n" " -nullvalue TEXT set text string for NULL values. Default ''\n" - " -separator SEP set output field separator. Default: '|'\n" + " -pagecache SIZE N use N slots of SZ bytes each for page cache memory\n" + " -scratch SIZE N use N slots of SZ bytes each for scratch memory\n" + " -separator SEP set output column separator. Default: '|'\n" " -stats print memory stats before each finalize\n" " -version show SQLite version\n" " -vfs NAME use NAME as the default VFS\n" @@ -3522,16 +4111,18 @@ /* ** Initialize the state information in data */ -static void main_init(struct callback_data *data) { +static void main_init(ShellState *data) { memset(data, 0, sizeof(*data)); data->mode = MODE_List; - memcpy(data->separator,"|", 2); + memcpy(data->colSeparator,SEP_Column, 2); + memcpy(data->rowSeparator,SEP_Row, 2); data->showHeader = 0; + data->shellFlgs = SHFLG_Lookaside; sqlite3_config(SQLITE_CONFIG_URI, 1); sqlite3_config(SQLITE_CONFIG_LOG, shellLog, data); + sqlite3_config(SQLITE_CONFIG_MULTITHREAD); sqlite3_snprintf(sizeof(mainPrompt), mainPrompt,"sqlite> "); sqlite3_snprintf(sizeof(continuePrompt), continuePrompt," ...> "); - sqlite3_config(SQLITE_CONFIG_SINGLETHREAD); } /* @@ -3569,12 +4160,14 @@ int main(int argc, char **argv){ char *zErrMsg = 0; - struct callback_data data; + ShellState data; const char *zInitFile = 0; - char *zFirstCmd = 0; int i; int rc = 0; int warnInmemoryDb = 0; + int readStdin = 1; + int nCmd = 0; + char **azCmd = 0; #if USE_SYSTEM_SQLITE+0!=1 if( strcmp(sqlite3_sourceid(),SQLITE_SOURCE_ID)!=0 ){ @@ -3594,6 +4187,18 @@ signal(SIGINT, interrupt_handler); #endif +#ifdef SQLITE_SHELL_DBNAME_PROC + { + /* If the SQLITE_SHELL_DBNAME_PROC macro is defined, then it is the name + ** of a C-function that will provide the name of the database file. Use + ** this compile-time option to embed this shell program in larger + ** applications. */ + extern void SQLITE_SHELL_DBNAME_PROC(const char**); + SQLITE_SHELL_DBNAME_PROC(&data.zDbFilename); + warnInmemoryDb = 0; + } +#endif + /* Do an initial pass through the command-line argument to locate ** the name of the database file, the name of the initialization file, ** the size of the alternative malloc heap, @@ -3605,19 +4210,23 @@ if( z[0]!='-' ){ if( data.zDbFilename==0 ){ data.zDbFilename = z; - continue; + }else{ + /* Excesss arguments are interpreted as SQL (or dot-commands) and + ** mean that nothing is read from stdin */ + readStdin = 0; + nCmd++; + azCmd = realloc(azCmd, sizeof(azCmd[0])*nCmd); + if( azCmd==0 ){ + fprintf(stderr, "out of memory\n"); + exit(1); + } + azCmd[nCmd-1] = z; } - if( zFirstCmd==0 ){ - zFirstCmd = z; - continue; - } - fprintf(stderr,"%s: Error: too many options: \"%s\"\n", Argv0, argv[i]); - fprintf(stderr,"Use -help for a list of options.\n"); - return 1; } if( z[1]=='-' ) z++; if( strcmp(z,"-separator")==0 || strcmp(z,"-nullvalue")==0 + || strcmp(z,"-newline")==0 || strcmp(z,"-cmd")==0 ){ (void)cmdline_option_value(argc, argv, ++i); @@ -3639,6 +4248,33 @@ if( szHeap>0x7fff0000 ) szHeap = 0x7fff0000; sqlite3_config(SQLITE_CONFIG_HEAP, malloc((int)szHeap), (int)szHeap, 64); #endif + }else if( strcmp(z,"-scratch")==0 ){ + int n, sz; + sz = (int)integerValue(cmdline_option_value(argc,argv,++i)); + if( sz>400000 ) sz = 400000; + if( sz<2500 ) sz = 2500; + n = (int)integerValue(cmdline_option_value(argc,argv,++i)); + if( n>10 ) n = 10; + if( n<1 ) n = 1; + sqlite3_config(SQLITE_CONFIG_SCRATCH, malloc(n*sz+1), sz, n); + data.shellFlgs |= SHFLG_Scratch; + }else if( strcmp(z,"-pagecache")==0 ){ + int n, sz; + sz = (int)integerValue(cmdline_option_value(argc,argv,++i)); + if( sz>70000 ) sz = 70000; + if( sz<800 ) sz = 800; + n = (int)integerValue(cmdline_option_value(argc,argv,++i)); + if( n<10 ) n = 10; + sqlite3_config(SQLITE_CONFIG_PAGECACHE, malloc(n*sz+1), sz, n); + data.shellFlgs |= SHFLG_Pagecache; + }else if( strcmp(z,"-lookaside")==0 ){ + int n, sz; + sz = (int)integerValue(cmdline_option_value(argc,argv,++i)); + if( sz<0 ) sz = 0; + n = (int)integerValue(cmdline_option_value(argc,argv,++i)); + if( n<0 ) n = 0; + sqlite3_config(SQLITE_CONFIG_LOOKASIDE, sz, n); + if( sz*n==0 ) data.shellFlgs &= ~SHFLG_Lookaside; #ifdef SQLITE_ENABLE_VFSTRACE }else if( strcmp(z,"-vfstrace")==0 ){ extern int vfstrace_register( @@ -3676,11 +4312,6 @@ fprintf(stderr,"%s: Error: no database filename specified\n", Argv0); return 1; #endif -#ifdef SQLITE_SHELL_DBNAME_PROC - { extern void SQLITE_SHELL_DBNAME_PROC(const char**); - SQLITE_SHELL_DBNAME_PROC(&data.zDbFilename); - warnInmemoryDb = 0; } -#endif } data.out = stdout; @@ -3723,12 +4354,21 @@ data.mode = MODE_Column; }else if( strcmp(z,"-csv")==0 ){ data.mode = MODE_Csv; - memcpy(data.separator,",",2); + memcpy(data.colSeparator,",",2); + }else if( strcmp(z,"-ascii")==0 ){ + data.mode = MODE_Ascii; + sqlite3_snprintf(sizeof(data.colSeparator), data.colSeparator, + SEP_Unit); + sqlite3_snprintf(sizeof(data.rowSeparator), data.rowSeparator, + SEP_Record); }else if( strcmp(z,"-separator")==0 ){ - sqlite3_snprintf(sizeof(data.separator), data.separator, + sqlite3_snprintf(sizeof(data.colSeparator), data.colSeparator, "%s",cmdline_option_value(argc,argv,++i)); + }else if( strcmp(z,"-newline")==0 ){ + sqlite3_snprintf(sizeof(data.rowSeparator), data.rowSeparator, + "%s",cmdline_option_value(argc,argv,++i)); }else if( strcmp(z,"-nullvalue")==0 ){ - sqlite3_snprintf(sizeof(data.nullvalue), data.nullvalue, + sqlite3_snprintf(sizeof(data.nullValue), data.nullValue, "%s",cmdline_option_value(argc,argv,++i)); }else if( strcmp(z,"-header")==0 ){ data.showHeader = 1; @@ -3740,6 +4380,8 @@ data.autoEQP = 1; }else if( strcmp(z,"-stats")==0 ){ data.statsOn = 1; + }else if( strcmp(z,"-scanstats")==0 ){ + data.scanstatsOn = 1; }else if( strcmp(z,"-bail")==0 ){ bail_on_error = 1; }else if( strcmp(z,"-version")==0 ){ @@ -3751,6 +4393,12 @@ stdin_is_interactive = 0; }else if( strcmp(z,"-heap")==0 ){ i++; + }else if( strcmp(z,"-scratch")==0 ){ + i+=2; + }else if( strcmp(z,"-pagecache")==0 ){ + i+=2; + }else if( strcmp(z,"-lookaside")==0 ){ + i+=2; }else if( strcmp(z,"-mmap")==0 ){ i++; }else if( strcmp(z,"-vfs")==0 ){ @@ -3766,6 +4414,10 @@ }else if( strcmp(z,"-help")==0 ){ usage(1); }else if( strcmp(z,"-cmd")==0 ){ + /* Run commands that follow -cmd first and separately from commands + ** that simply appear on the command-line. This seems goofy. It would + ** be better if all commands ran in the order that they appear. But + ** we retain the goofy behavior for historical compatibility. */ if( i==argc-1 ) break; z = cmdline_option_value(argc,argv,++i); if( z[0]=='.' ){ @@ -3789,23 +4441,28 @@ } } - if( zFirstCmd ){ - /* Run just the command that follows the database name + if( !readStdin ){ + /* Run all arguments that do not begin with '-' as if they were separate + ** command-line inputs, except for the argToSkip argument which contains + ** the database filename. */ - if( zFirstCmd[0]=='.' ){ - rc = do_meta_command(zFirstCmd, &data); - if( rc==2 ) rc = 0; - }else{ - open_db(&data, 0); - rc = shell_exec(data.db, zFirstCmd, shell_callback, &data, &zErrMsg); - if( zErrMsg!=0 ){ - fprintf(stderr,"Error: %s\n", zErrMsg); - return rc!=0 ? rc : 1; - }else if( rc!=0 ){ - fprintf(stderr,"Error: unable to process SQL \"%s\"\n", zFirstCmd); - return rc; + for(i=0; i