1 /*

     2 ** 2001 September 15

     3 **

     4 ** The author disclaims copyright to this source code.  In place of

     5 ** a legal notice, here is a blessing:

     6 **

     7 **    May you do good and not evil.

     8 **    May you find forgiveness for yourself and forgive others.

     9 **    May you share freely, never taking more than you give.

    10 **

    11 *************************************************************************

    12 ** This file contains code to implement the "sqlite" command line

    13 ** utility for accessing SQLite databases.

    14 **

    15 ** $Id: shell.c,v 1.178 2008/05/05 16:27:24 drh Exp $

    16 */

    17 #include <stdlib.h>

    18 #include <string.h>

    19 #include <stdio.h>

    20 #include <assert.h>

    21 #include "sqlite3.h"

    22 #include <ctype.h>

    23 #include <stdarg.h>

    24 

    25 #if !defined(_WIN32) && !defined(WIN32) && !defined(__OS2__)

    26 # include <signal.h>

    27 # include <pwd.h>

    28 # include <unistd.h>

    29 # include <sys/types.h>

    30 #endif

    31 

    32 #ifdef __OS2__

    33 # include <unistd.h>

    34 #endif

    35 

    36 #if defined(HAVE_READLINE) && HAVE_READLINE==1

    37 # include <readline/readline.h>

    38 # include <readline/history.h>

    39 #else

    40 # define readline(p) local_getline(p,stdin)

    41 # define add_history(X)

    42 # define read_history(X)

    43 # define write_history(X)

    44 # define stifle_history(X)

    45 #endif

    46 

    47 #if defined(_WIN32) || defined(WIN32)

    48 # include <io.h>

    49 #else

    50 /* Make sure isatty() has a prototype.

    51 */

    52 extern int isatty();

    53 #endif

    54 

    55 #if defined(_WIN32_WCE)

    56 /* Windows CE (arm-wince-mingw32ce-gcc) does not provide isatty()

    57  * thus we always assume that we have a console. That can be

    58  * overridden with the -batch command line option.

    59  */

    60 #define isatty(x) 1

    61 #endif

    62 

    63 #if !defined(_WIN32) && !defined(WIN32) && !defined(__OS2__)

    64 #include <sys/time.h>

    65 #include <sys/resource.h>

    66 

    67 /* Saved resource information for the beginning of an operation */

    68 static struct rusage sBegin;

    69 

    70 /* True if the timer is enabled */

    71 static int enableTimer = 0;

    72 

    73 /*

    74 ** Begin timing an operation

    75 */

    76 static void beginTimer(void){

    77   if( enableTimer ){

    78     getrusage(RUSAGE_SELF, &sBegin);

    79   }

    80 }

    81 

    82 /* Return the difference of two time_structs in microseconds */

    83 static int timeDiff(struct timeval *pStart, struct timeval *pEnd){

    84   return (pEnd->tv_usec - pStart->tv_usec) + 

    85          1000000*(pEnd->tv_sec - pStart->tv_sec);

    86 }

    87 

    88 /*

    89 ** Print the timing results.

    90 */

    91 static void endTimer(void){

    92   if( enableTimer ){

    93     struct rusage sEnd;

    94     getrusage(RUSAGE_SELF, &sEnd);

    95     printf("CPU Time: user %f sys %f\n",

    96        0.000001*timeDiff(&sBegin.ru_utime, &sEnd.ru_utime),

    97        0.000001*timeDiff(&sBegin.ru_stime, &sEnd.ru_stime));

    98   }

    99 }

   100 #define BEGIN_TIMER beginTimer()

   101 #define END_TIMER endTimer()

   102 #define HAS_TIMER 1

   103 #else

   104 #define BEGIN_TIMER 

   105 #define END_TIMER

   106 #define HAS_TIMER 0

   107 #endif

   108 

   109 

   110 /*

   111 ** If the following flag is set, then command execution stops

   112 ** at an error if we are not interactive.

   113 */

   114 static int bail_on_error = 0;

   115 

   116 /*

   117 ** Threat stdin as an interactive input if the following variable

   118 ** is true.  Otherwise, assume stdin is connected to a file or pipe.

   119 */

   120 static int stdin_is_interactive = 1;

   121 

   122 /*

   123 ** The following is the open SQLite database.  We make a pointer

   124 ** to this database a static variable so that it can be accessed

   125 ** by the SIGINT handler to interrupt database processing.

   126 */

   127 static sqlite3 *db = 0;

   128 

   129 /*

   130 ** True if an interrupt (Control-C) has been received.

   131 */

   132 static volatile int seenInterrupt = 0;

   133 

   134 /*

   135 ** This is the name of our program. It is set in main(), used

   136 ** in a number of other places, mostly for error messages.

   137 */

   138 static char *Argv0;

   139 

   140 /*

   141 ** Prompt strings. Initialized in main. Settable with

   142 **   .prompt main continue

   143 */

   144 static char mainPrompt[20];     /* First line prompt. default: "sqlite> "*/

   145 static char continuePrompt[20]; /* Continuation prompt. default: "   ...> " */

   146 

   147 /*

   148 ** Write I/O traces to the following stream.

   149 */

   150 #ifdef SQLITE_ENABLE_IOTRACE

   151 static FILE *iotrace = 0;

   152 #endif

   153 

   154 /*

   155 ** This routine works like printf in that its first argument is a

   156 ** format string and subsequent arguments are values to be substituted

   157 ** in place of % fields.  The result of formatting this string

   158 ** is written to iotrace.

   159 */

   160 #ifdef SQLITE_ENABLE_IOTRACE

   161 static void iotracePrintf(const char *zFormat, ...){

   162   va_list ap;

   163   char *z;

   164   if( iotrace==0 ) return;

   165   va_start(ap, zFormat);

   166   z = sqlite3_vmprintf(zFormat, ap);

   167   va_end(ap);

   168   fprintf(iotrace, "%s", z);

   169   sqlite3_free(z);

   170 }

   171 #endif

   172 

   173 

   174 /*

   175 ** Determines if a string is a number of not.

   176 */

   177 static int isNumber(const char *z, int *realnum){

   178   if( *z=='-' || *z=='+' ) z++;

   179   if( !isdigit(*z) ){

   180     return 0;

   181   }

   182   z++;

   183   if( realnum ) *realnum = 0;

   184   while( isdigit(*z) ){ z++; }

   185   if( *z=='.' ){

   186     z++;

   187     if( !isdigit(*z) ) return 0;

   188     while( isdigit(*z) ){ z++; }

   189     if( realnum ) *realnum = 1;

   190   }

   191   if( *z=='e' || *z=='E' ){

   192     z++;

   193     if( *z=='+' || *z=='-' ) z++;

   194     if( !isdigit(*z) ) return 0;

   195     while( isdigit(*z) ){ z++; }

   196     if( realnum ) *realnum = 1;

   197   }

   198   return *z==0;

   199 }

   200 

   201 /*

   202 ** A global char* and an SQL function to access its current value 

   203 ** from within an SQL statement. This program used to use the 

   204 ** sqlite_exec_printf() API to substitue a string into an SQL statement.

   205 ** The correct way to do this with sqlite3 is to use the bind API, but

   206 ** since the shell is built around the callback paradigm it would be a lot

   207 ** of work. Instead just use this hack, which is quite harmless.

   208 */

   209 static const char *zShellStatic = 0;

   210 static void shellstaticFunc(

   211   sqlite3_context *context,

   212   int argc,

   213   sqlite3_value **argv

   214 ){

   215   assert( 0==argc );

   216   assert( zShellStatic );

   217   sqlite3_result_text(context, zShellStatic, -1, SQLITE_STATIC);

   218 }

   219 

   220 

   221 /*

   222 ** This routine reads a line of text from FILE in, stores

   223 ** the text in memory obtained from malloc() and returns a pointer

   224 ** to the text.  NULL is returned at end of file, or if malloc()

   225 ** fails.

   226 **

   227 ** The interface is like "readline" but no command-line editing

   228 ** is done.

   229 */

   230 static char *local_getline(char *zPrompt, FILE *in){

   231   char *zLine;

   232   int nLine;

   233   int n;

   234   int eol;

   235 

   236   if( zPrompt && *zPrompt ){

   237     printf("%s",zPrompt);

   238     fflush(stdout);

   239   }

   240   nLine = 100;

   241   zLine = malloc( nLine );

   242   if( zLine==0 ) return 0;

   243   n = 0;

   244   eol = 0;

   245   while( !eol ){

   246     if( n+100>nLine ){

   247       nLine = nLine*2 + 100;

   248       zLine = realloc(zLine, nLine);

   249       if( zLine==0 ) return 0;

   250     }

   251     if( fgets(&zLine[n], nLine - n, in)==0 ){

   252       if( n==0 ){

   253         free(zLine);

   254         return 0;

   255       }

   256       zLine[n] = 0;

   257       eol = 1;

   258       break;

   259     }

   260     while( zLine[n] ){ n++; }

   261     if( n>0 && zLine[n-1]=='\n' ){

   262       n--;

   263       zLine[n] = 0;

   264       eol = 1;

   265     }

   266   }

   267   zLine = realloc( zLine, n+1 );

   268   return zLine;

   269 }

   270 

   271 /*

   272 ** Retrieve a single line of input text.

   273 **

   274 ** zPrior is a string of prior text retrieved.  If not the empty

   275 ** string, then issue a continuation prompt.

   276 */

   277 static char *one_input_line(const char *zPrior, FILE *in){

   278   char *zPrompt;

   279   char *zResult;

   280   if( in!=0 ){

   281     return local_getline(0, in);

   282   }

   283   if( zPrior && zPrior[0] ){

   284     zPrompt = continuePrompt;

   285   }else{

   286     zPrompt = mainPrompt;

   287   }

   288   zResult = readline(zPrompt);

   289 #if defined(HAVE_READLINE) && HAVE_READLINE==1

   290   if( zResult && *zResult ) add_history(zResult);

   291 #endif

   292   return zResult;

   293 }

   294 

   295 struct previous_mode_data {

   296   int valid;        /* Is there legit data in here? */

   297   int mode;

   298   int showHeader;

   299   int colWidth[100];

   300 };

   301 

   302 /*

   303 ** An pointer to an instance of this structure is passed from

   304 ** the main program to the callback.  This is used to communicate

   305 ** state and mode information.

   306 */

   307 struct callback_data {

   308   sqlite3 *db;            /* The database */

   309   int echoOn;            /* True to echo input commands */

   310   int cnt;               /* Number of records displayed so far */

   311   FILE *out;             /* Write results here */

   312   int mode;              /* An output mode setting */

   313   int writableSchema;    /* True if PRAGMA writable_schema=ON */

   314   int showHeader;        /* True to show column names in List or Column mode */

   315   char *zDestTable;      /* Name of destination table when MODE_Insert */

   316   char separator[20];    /* Separator character for MODE_List */

   317   int colWidth[100];     /* Requested width of each column when in column mode*/

   318   int actualWidth[100];  /* Actual width of each column */

   319   char nullvalue[20];    /* The text to print when a NULL comes back from

   320                          ** the database */

   321   struct previous_mode_data explainPrev;

   322                          /* Holds the mode information just before

   323                          ** .explain ON */

   324   char outfile[FILENAME_MAX]; /* Filename for *out */

   325   const char *zDbFilename;    /* name of the database file */

   326 };

   327 

   328 /*

   329 ** These are the allowed modes.

   330 */

   331 #define MODE_Line     0  /* One column per line.  Blank line between records */

   332 #define MODE_Column   1  /* One record per line in neat columns */

   333 #define MODE_List     2  /* One record per line with a separator */

   334 #define MODE_Semi     3  /* Same as MODE_List but append ";" to each line */

   335 #define MODE_Html     4  /* Generate an XHTML table */

   336 #define MODE_Insert   5  /* Generate SQL "insert" statements */

   337 #define MODE_Tcl      6  /* Generate ANSI-C or TCL quoted elements */

   338 #define MODE_Csv      7  /* Quote strings, numbers are plain */

   339 #define MODE_Explain  8  /* Like MODE_Column, but do not truncate data */

   340 

   341 static const char *modeDescr[] = {

   342   "line",

   343   "column",

   344   "list",

   345   "semi",

   346   "html",

   347   "insert",

   348   "tcl",

   349   "csv",

   350   "explain",

   351 };

   352 

   353 /*

   354 ** Number of elements in an array

   355 */

   356 #define ArraySize(X)  (sizeof(X)/sizeof(X[0]))

   357 

   358 /*

   359 ** Output the given string as a quoted string using SQL quoting conventions.

   360 */

   361 static void output_quoted_string(FILE *out, const char *z){

   362   int i;

   363   int nSingle = 0;

   364   for(i=0; z[i]; i++){

   365     if( z[i]=='\'' ) nSingle++;

   366   }

   367   if( nSingle==0 ){

   368     fprintf(out,"'%s'",z);

   369   }else{

   370     fprintf(out,"'");

   371     while( *z ){

   372       for(i=0; z[i] && z[i]!='\''; i++){}

   373       if( i==0 ){

   374         fprintf(out,"''");

   375         z++;

   376       }else if( z[i]=='\'' ){

   377         fprintf(out,"%.*s''",i,z);

   378         z += i+1;

   379       }else{

   380         fprintf(out,"%s",z);

   381         break;

   382       }

   383     }

   384     fprintf(out,"'");

   385   }

   386 }

   387 

   388 /*

   389 ** Output the given string as a quoted according to C or TCL quoting rules.

   390 */

   391 static void output_c_string(FILE *out, const char *z){

   392   unsigned int c;

   393   fputc('"', out);

   394   while( (c = *(z++))!=0 ){

   395     if( c=='\\' ){

   396       fputc(c, out);

   397       fputc(c, out);

   398     }else if( c=='\t' ){

   399       fputc('\\', out);

   400       fputc('t', out);

   401     }else if( c=='\n' ){

   402       fputc('\\', out);

   403       fputc('n', out);

   404     }else if( c=='\r' ){

   405       fputc('\\', out);

   406       fputc('r', out);

   407     }else if( !isprint(c) ){

   408       fprintf(out, "\\%03o", c&0xff);

   409     }else{

   410       fputc(c, out);

   411     }

   412   }

   413   fputc('"', out);

   414 }

   415 

   416 /*

   417 ** Output the given string with characters that are special to

   418 ** HTML escaped.

   419 */

   420 static void output_html_string(FILE *out, const char *z){

   421   int i;

   422   while( *z ){

   423     for(i=0; z[i] && z[i]!='<' && z[i]!='&'; i++){}

   424     if( i>0 ){

   425       fprintf(out,"%.*s",i,z);

   426     }

   427     if( z[i]=='<' ){

   428       fprintf(out,"<");

   429     }else if( z[i]=='&' ){

   430       fprintf(out,"&");

   431     }else{

   432       break;

   433     }

   434     z += i + 1;

   435   }

   436 }

   437 

   438 /*

   439 ** If a field contains any character identified by a 1 in the following

   440 ** array, then the string must be quoted for CSV.

   441 */

   442 static const char needCsvQuote[] = {

   443   1, 1, 1, 1, 1, 1, 1, 1,   1, 1, 1, 1, 1, 1, 1, 1,   

   444   1, 1, 1, 1, 1, 1, 1, 1,   1, 1, 1, 1, 1, 1, 1, 1,   

   445   1, 0, 1, 0, 0, 0, 0, 1,   0, 0, 0, 0, 0, 0, 0, 0, 

   446   0, 0, 0, 0, 0, 0, 0, 0,   0, 0, 0, 0, 0, 0, 0, 0, 

   447   0, 0, 0, 0, 0, 0, 0, 0,   0, 0, 0, 0, 0, 0, 0, 0, 

   448   0, 0, 0, 0, 0, 0, 0, 0,   0, 0, 0, 0, 0, 0, 0, 0, 

   449   0, 0, 0, 0, 0, 0, 0, 0,   0, 0, 0, 0, 0, 0, 0, 0, 

   450   0, 0, 0, 0, 0, 0, 0, 0,   0, 0, 0, 0, 0, 0, 0, 1, 

   451   1, 1, 1, 1, 1, 1, 1, 1,   1, 1, 1, 1, 1, 1, 1, 1,   

   452   1, 1, 1, 1, 1, 1, 1, 1,   1, 1, 1, 1, 1, 1, 1, 1,   

   453   1, 1, 1, 1, 1, 1, 1, 1,   1, 1, 1, 1, 1, 1, 1, 1,   

   454   1, 1, 1, 1, 1, 1, 1, 1,   1, 1, 1, 1, 1, 1, 1, 1,   

   455   1, 1, 1, 1, 1, 1, 1, 1,   1, 1, 1, 1, 1, 1, 1, 1,   

   456   1, 1, 1, 1, 1, 1, 1, 1,   1, 1, 1, 1, 1, 1, 1, 1,   

   457   1, 1, 1, 1, 1, 1, 1, 1,   1, 1, 1, 1, 1, 1, 1, 1,   

   458   1, 1, 1, 1, 1, 1, 1, 1,   1, 1, 1, 1, 1, 1, 1, 1,   

   459 };

   460 

   461 /*

   462 ** Output a single term of CSV.  Actually, p->separator is used for

   463 ** the separator, which may or may not be a comma.  p->nullvalue is

   464 ** the null value.  Strings are quoted using ANSI-C rules.  Numbers

   465 ** appear outside of quotes.

   466 */

   467 static void output_csv(struct callback_data *p, const char *z, int bSep){

   468   FILE *out = p->out;

   469   if( z==0 ){

   470     fprintf(out,"%s",p->nullvalue);

   471   }else{

   472     int i;

   473     int nSep = strlen(p->separator);

   474     for(i=0; z[i]; i++){

   475       if( needCsvQuote[((unsigned char*)z)[i]] 

   476          || (z[i]==p->separator[0] && 

   477              (nSep==1 || memcmp(z, p->separator, nSep)==0)) ){

   478         i = 0;

   479         break;

   480       }

   481     }

   482     if( i==0 ){

   483       putc('"', out);

   484       for(i=0; z[i]; i++){

   485         if( z[i]=='"' ) putc('"', out);

   486         putc(z[i], out);

   487       }

   488       putc('"', out);

   489     }else{

   490       fprintf(out, "%s", z);

   491     }

   492   }

   493   if( bSep ){

   494     fprintf(p->out, "%s", p->separator);

   495   }

   496 }

   497 

   498 #ifdef SIGINT

   499 /*

   500 ** This routine runs when the user presses Ctrl-C

   501 */

   502 static void interrupt_handler(int NotUsed){

   503   seenInterrupt = 1;

   504   if( db ) sqlite3_interrupt(db);

   505 }

   506 #endif

   507 

   508 /*

   509 ** This is the callback routine that the SQLite library

   510 ** invokes for each row of a query result.

   511 */

   512 static int callback(void *pArg, int nArg, char **azArg, char **azCol){

   513   int i;

   514   struct callback_data *p = (struct callback_data*)pArg;

   515   switch( p->mode ){

   516     case MODE_Line: {

   517       int w = 5;

   518       if( azArg==0 ) break;

   519       for(i=0; i<nArg; i++){

   520         int len = strlen(azCol[i] ? azCol[i] : "");

   521         if( len>w ) w = len;

   522       }

   523       if( p->cnt++>0 ) fprintf(p->out,"\n");

   524       for(i=0; i<nArg; i++){

   525         fprintf(p->out,"%*s = %s\n", w, azCol[i],

   526                 azArg[i] ? azArg[i] : p->nullvalue);

   527       }

   528       break;

   529     }

   530     case MODE_Explain:

   531     case MODE_Column: {

   532       if( p->cnt++==0 ){

   533         for(i=0; i<nArg; i++){

   534           int w, n;

   535           if( i<ArraySize(p->colWidth) ){

   536             w = p->colWidth[i];

   537           }else{

   538             w = 0;

   539           }

   540           if( w<=0 ){

   541             w = strlen(azCol[i] ? azCol[i] : "");

   542             if( w<10 ) w = 10;

   543             n = strlen(azArg && azArg[i] ? azArg[i] : p->nullvalue);

   544             if( w<n ) w = n;

   545           }

   546           if( i<ArraySize(p->actualWidth) ){

   547             p->actualWidth[i] = w;

   548           }

   549           if( p->showHeader ){

   550             fprintf(p->out,"%-*.*s%s",w,w,azCol[i], i==nArg-1 ? "\n": "  ");

   551           }

   552         }

   553         if( p->showHeader ){

   554           for(i=0; i<nArg; i++){

   555             int w;

   556             if( i<ArraySize(p->actualWidth) ){

   557                w = p->actualWidth[i];

   558             }else{

   559                w = 10;

   560             }

   561             fprintf(p->out,"%-*.*s%s",w,w,"-----------------------------------"

   562                    "----------------------------------------------------------",

   563                     i==nArg-1 ? "\n": "  ");

   564           }

   565         }

   566       }

   567       if( azArg==0 ) break;

   568       for(i=0; i<nArg; i++){

   569         int w;

   570         if( i<ArraySize(p->actualWidth) ){

   571            w = p->actualWidth[i];

   572         }else{

   573            w = 10;

   574         }

   575         if( p->mode==MODE_Explain && azArg[i] && strlen(azArg[i])>w ){

   576           w = strlen(azArg[i]);

   577         }

   578         fprintf(p->out,"%-*.*s%s",w,w,

   579             azArg[i] ? azArg[i] : p->nullvalue, i==nArg-1 ? "\n": "  ");

   580       }

   581       break;

   582     }

   583     case MODE_Semi:

   584     case MODE_List: {

   585       if( p->cnt++==0 && p->showHeader ){

   586         for(i=0; i<nArg; i++){

   587           fprintf(p->out,"%s%s",azCol[i], i==nArg-1 ? "\n" : p->separator);

   588         }

   589       }

   590       if( azArg==0 ) break;

   591       for(i=0; i<nArg; i++){

   592         char *z = azArg[i];

   593         if( z==0 ) z = p->nullvalue;

   594         fprintf(p->out, "%s", z);

   595         if( i<nArg-1 ){

   596           fprintf(p->out, "%s", p->separator);

   597         }else if( p->mode==MODE_Semi ){

   598           fprintf(p->out, ";\n");

   599         }else{

   600           fprintf(p->out, "\n");

   601         }

   602       }

   603       break;

   604     }

   605     case MODE_Html: {

   606       if( p->cnt++==0 && p->showHeader ){

   607         fprintf(p->out,"<TR>");

   608         for(i=0; i<nArg; i++){

   609           fprintf(p->out,"<TH>%s</TH>",azCol[i]);

   610         }

   611         fprintf(p->out,"</TR>\n");

   612       }

   613       if( azArg==0 ) break;

   614       fprintf(p->out,"<TR>");

   615       for(i=0; i<nArg; i++){

   616         fprintf(p->out,"<TD>");

   617         output_html_string(p->out, azArg[i] ? azArg[i] : p->nullvalue);

   618         fprintf(p->out,"</TD>\n");

   619       }

   620       fprintf(p->out,"</TR>\n");

   621       break;

   622     }

   623     case MODE_Tcl: {

   624       if( p->cnt++==0 && p->showHeader ){

   625         for(i=0; i<nArg; i++){

   626           output_c_string(p->out,azCol[i] ? azCol[i] : "");

   627           fprintf(p->out, "%s", p->separator);

   628         }

   629         fprintf(p->out,"\n");

   630       }

   631       if( azArg==0 ) break;

   632       for(i=0; i<nArg; i++){

   633         output_c_string(p->out, azArg[i] ? azArg[i] : p->nullvalue);

   634         fprintf(p->out, "%s", p->separator);

   635       }

   636       fprintf(p->out,"\n");

   637       break;

   638     }

   639     case MODE_Csv: {

   640       if( p->cnt++==0 && p->showHeader ){

   641         for(i=0; i<nArg; i++){

   642           output_csv(p, azCol[i] ? azCol[i] : "", i<nArg-1);

   643         }

   644         fprintf(p->out,"\n");

   645       }

   646       if( azArg==0 ) break;

   647       for(i=0; i<nArg; i++){

   648         output_csv(p, azArg[i], i<nArg-1);

   649       }

   650       fprintf(p->out,"\n");

   651       break;

   652     }

   653     case MODE_Insert: {

   654       if( azArg==0 ) break;

   655       fprintf(p->out,"INSERT INTO %s VALUES(",p->zDestTable);

   656       for(i=0; i<nArg; i++){

   657         char *zSep = i>0 ? ",": "";

   658         if( azArg[i]==0 ){

   659           fprintf(p->out,"%sNULL",zSep);

   660         }else if( isNumber(azArg[i], 0) ){

   661           fprintf(p->out,"%s%s",zSep, azArg[i]);

   662         }else{

   663           if( zSep[0] ) fprintf(p->out,"%s",zSep);

   664           output_quoted_string(p->out, azArg[i]);

   665         }

   666       }

   667       fprintf(p->out,");\n");

   668       break;

   669     }

   670   }

   671   return 0;

   672 }

   673 

   674 /*

   675 ** Set the destination table field of the callback_data structure to

   676 ** the name of the table given.  Escape any quote characters in the

   677 ** table name.

   678 */

   679 static void set_table_name(struct callback_data *p, const char *zName){

   680   int i, n;

   681   int needQuote;

   682   char *z;

   683 

   684   if( p->zDestTable ){

   685     free(p->zDestTable);

   686     p->zDestTable = 0;

   687   }

   688   if( zName==0 ) return;

   689   needQuote = !isalpha((unsigned char)*zName) && *zName!='_';

   690   for(i=n=0; zName[i]; i++, n++){

   691     if( !isalnum((unsigned char)zName[i]) && zName[i]!='_' ){

   692       needQuote = 1;

   693       if( zName[i]=='\'' ) n++;

   694     }

   695   }

   696   if( needQuote ) n += 2;

   697   z = p->zDestTable = malloc( n+1 );

   698   if( z==0 ){

   699     fprintf(stderr,"Out of memory!\n");

   700     exit(1);

   701   }

   702   n = 0;

   703   if( needQuote ) z[n++] = '\'';

   704   for(i=0; zName[i]; i++){

   705     z[n++] = zName[i];

   706     if( zName[i]=='\'' ) z[n++] = '\'';

   707   }

   708   if( needQuote ) z[n++] = '\'';

   709   z[n] = 0;

   710 }

   711 

   712 /* zIn is either a pointer to a NULL-terminated string in memory obtained

   713 ** from malloc(), or a NULL pointer. The string pointed to by zAppend is

   714 ** added to zIn, and the result returned in memory obtained from malloc().

   715 ** zIn, if it was not NULL, is freed.

   716 **

   717 ** If the third argument, quote, is not '\0', then it is used as a 

   718 ** quote character for zAppend.

   719 */

   720 static char *appendText(char *zIn, char const *zAppend, char quote){

   721   int len;

   722   int i;

   723   int nAppend = strlen(zAppend);

   724   int nIn = (zIn?strlen(zIn):0);

   725 

   726   len = nAppend+nIn+1;

   727   if( quote ){

   728     len += 2;

   729     for(i=0; i<nAppend; i++){

   730       if( zAppend[i]==quote ) len++;

   731     }

   732   }

   733 

   734   zIn = (char *)realloc(zIn, len);

   735   if( !zIn ){

   736     return 0;

   737   }

   738 

   739   if( quote ){

   740     char *zCsr = &zIn[nIn];

   741     *zCsr++ = quote;

   742     for(i=0; i<nAppend; i++){

   743       *zCsr++ = zAppend[i];

   744       if( zAppend[i]==quote ) *zCsr++ = quote;

   745     }

   746     *zCsr++ = quote;

   747     *zCsr++ = '\0';

   748     assert( (zCsr-zIn)==len );

   749   }else{

   750     memcpy(&zIn[nIn], zAppend, nAppend);

   751     zIn[len-1] = '\0';

   752   }

   753 

   754   return zIn;

   755 }

   756 

   757 

   758 /*

   759 ** Execute a query statement that has a single result column.  Print

   760 ** that result column on a line by itself with a semicolon terminator.

   761 **

   762 ** This is used, for example, to show the schema of the database by

   763 ** querying the SQLITE_MASTER table.

   764 */

   765 static int run_table_dump_query(FILE *out, sqlite3 *db, const char *zSelect){

   766   sqlite3_stmt *pSelect;

   767   int rc;

   768   rc = sqlite3_prepare(db, zSelect, -1, &pSelect, 0);

   769   if( rc!=SQLITE_OK || !pSelect ){

   770     return rc;

   771   }

   772   rc = sqlite3_step(pSelect);

   773   while( rc==SQLITE_ROW ){

   774     fprintf(out, "%s;\n", sqlite3_column_text(pSelect, 0));

   775     rc = sqlite3_step(pSelect);

   776   }

   777   return sqlite3_finalize(pSelect);

   778 }

   779 

   780 

   781 /*

   782 ** This is a different callback routine used for dumping the database.

   783 ** Each row received by this callback consists of a table name,

   784 ** the table type ("index" or "table") and SQL to create the table.

   785 ** This routine should print text sufficient to recreate the table.

   786 */

   787 static int dump_callback(void *pArg, int nArg, char **azArg, char **azCol){

   788   int rc;

   789   const char *zTable;

   790   const char *zType;

   791   const char *zSql;

   792   struct callback_data *p = (struct callback_data *)pArg;

   793 

   794   if( nArg!=3 ) return 1;

   795   zTable = azArg[0];

   796   zType = azArg[1];

   797   zSql = azArg[2];

   798   

   799   if( strcmp(zTable, "sqlite_sequence")==0 ){

   800     fprintf(p->out, "DELETE FROM sqlite_sequence;\n");

   801   }else if( strcmp(zTable, "sqlite_stat1")==0 ){

   802     fprintf(p->out, "ANALYZE sqlite_master;\n");

   803   }else if( strncmp(zTable, "sqlite_", 7)==0 ){

   804     return 0;

   805   }else if( strncmp(zSql, "CREATE VIRTUAL TABLE", 20)==0 ){

   806     char *zIns;

   807     if( !p->writableSchema ){

   808       fprintf(p->out, "PRAGMA writable_schema=ON;\n");

   809       p->writableSchema = 1;

   810     }

   811     zIns = sqlite3_mprintf(

   812        "INSERT INTO sqlite_master(type,name,tbl_name,rootpage,sql)"

   813        "VALUES('table','%q','%q',0,'%q');",

   814        zTable, zTable, zSql);

   815     fprintf(p->out, "%s\n", zIns);

   816     sqlite3_free(zIns);

   817     return 0;

   818   }else{

   819     fprintf(p->out, "%s;\n", zSql);

   820   }

   821 

   822   if( strcmp(zType, "table")==0 ){

   823     sqlite3_stmt *pTableInfo = 0;

   824     char *zSelect = 0;

   825     char *zTableInfo = 0;

   826     char *zTmp = 0;

   827    

   828     zTableInfo = appendText(zTableInfo, "PRAGMA table_info(", 0);

   829     zTableInfo = appendText(zTableInfo, zTable, '"');

   830     zTableInfo = appendText(zTableInfo, ");", 0);

   831 

   832     rc = sqlite3_prepare(p->db, zTableInfo, -1, &pTableInfo, 0);

   833     if( zTableInfo ) free(zTableInfo);

   834     if( rc!=SQLITE_OK || !pTableInfo ){

   835       return 1;

   836     }

   837 

   838     zSelect = appendText(zSelect, "SELECT 'INSERT INTO ' || ", 0);

   839     zTmp = appendText(zTmp, zTable, '"');

   840     if( zTmp ){

   841       zSelect = appendText(zSelect, zTmp, '\'');

   842     }

   843     zSelect = appendText(zSelect, " || ' VALUES(' || ", 0);

   844     rc = sqlite3_step(pTableInfo);

   845     while( rc==SQLITE_ROW ){

   846       const char *zText = (const char *)sqlite3_column_text(pTableInfo, 1);

   847       zSelect = appendText(zSelect, "quote(", 0);

   848       zSelect = appendText(zSelect, zText, '"');

   849       rc = sqlite3_step(pTableInfo);

   850       if( rc==SQLITE_ROW ){

   851         zSelect = appendText(zSelect, ") || ',' || ", 0);

   852       }else{

   853         zSelect = appendText(zSelect, ") ", 0);

   854       }

   855     }

   856     rc = sqlite3_finalize(pTableInfo);

   857     if( rc!=SQLITE_OK ){

   858       if( zSelect ) free(zSelect);

   859       return 1;

   860     }

   861     zSelect = appendText(zSelect, "|| ')' FROM  ", 0);

   862     zSelect = appendText(zSelect, zTable, '"');

   863 

   864     rc = run_table_dump_query(p->out, p->db, zSelect);

   865     if( rc==SQLITE_CORRUPT ){

   866       zSelect = appendText(zSelect, " ORDER BY rowid DESC", 0);

   867       rc = run_table_dump_query(p->out, p->db, zSelect);

   868     }

   869     if( zSelect ) free(zSelect);

   870   }

   871   return 0;

   872 }

   873 

   874 /*

   875 ** Run zQuery.  Use dump_callback() as the callback routine so that

   876 ** the contents of the query are output as SQL statements.

   877 **

   878 ** If we get a SQLITE_CORRUPT error, rerun the query after appending

   879 ** "ORDER BY rowid DESC" to the end.

   880 */

   881 static int run_schema_dump_query(

   882   struct callback_data *p, 

   883   const char *zQuery,

   884   char **pzErrMsg

   885 ){

   886   int rc;

   887   rc = sqlite3_exec(p->db, zQuery, dump_callback, p, pzErrMsg);

   888   if( rc==SQLITE_CORRUPT ){

   889     char *zQ2;

   890     int len = strlen(zQuery);

   891     if( pzErrMsg ) sqlite3_free(*pzErrMsg);

   892     zQ2 = malloc( len+100 );

   893     if( zQ2==0 ) return rc;

   894     sqlite3_snprintf(sizeof(zQ2), zQ2, "%s ORDER BY rowid DESC", zQuery);

   895     rc = sqlite3_exec(p->db, zQ2, dump_callback, p, pzErrMsg);

   896     free(zQ2);

   897   }

   898   return rc;

   899 }

   900 

   901 /*

   902 ** Text of a help message

   903 */

   904 static char zHelp[] =

   905   ".bail ON|OFF           Stop after hitting an error.  Default OFF\n"

   906   ".databases             List names and files of attached databases\n"

   907   ".dump ?TABLE? ...      Dump the database in an SQL text format\n"

   908   ".echo ON|OFF           Turn command echo on or off\n"

   909   ".exit                  Exit this program\n"

   910   ".explain ON|OFF        Turn output mode suitable for EXPLAIN on or off.\n"

   911   ".header(s) ON|OFF      Turn display of headers on or off\n"

   912   ".help                  Show this message\n"

   913   ".import FILE TABLE     Import data from FILE into TABLE\n"

   914   ".indices TABLE         Show names of all indices on TABLE\n"

   915 #ifdef SQLITE_ENABLE_IOTRACE

   916   ".iotrace FILE          Enable I/O diagnostic logging to FILE\n"

   917 #endif

   918 #ifndef SQLITE_OMIT_LOAD_EXTENSION

   919   ".load FILE ?ENTRY?     Load an extension library\n"

   920 #endif

   921   ".mode MODE ?TABLE?     Set output mode where MODE is one of:\n"

   922   "                         csv      Comma-separated values\n"

   923   "                         column   Left-aligned columns.  (See .width)\n"

   924   "                         html     HTML <table> code\n"

   925   "                         insert   SQL insert statements for TABLE\n"

   926   "                         line     One value per line\n"

   927   "                         list     Values delimited by .separator string\n"

   928   "                         tabs     Tab-separated values\n"

   929   "                         tcl      TCL list elements\n"

   930   ".nullvalue STRING      Print STRING in place of NULL values\n"

   931   ".output FILENAME       Send output to FILENAME\n"

   932   ".output stdout         Send output to the screen\n"

   933   ".prompt MAIN CONTINUE  Replace the standard prompts\n"

   934   ".quit                  Exit this program\n"

   935   ".read FILENAME         Execute SQL in FILENAME\n"

   936   ".schema ?TABLE?        Show the CREATE statements\n"

   937   ".separator STRING      Change separator used by output mode and .import\n"

   938   ".show                  Show the current values for various settings\n"

   939   ".tables ?PATTERN?      List names of tables matching a LIKE pattern\n"

   940   ".timeout MS            Try opening locked tables for MS milliseconds\n"

   941 #if HAS_TIMER

   942   ".timer ON|OFF          Turn the CPU timer measurement on or off\n"

   943 #endif

   944   ".width NUM NUM ...     Set column widths for \"column\" mode\n"

   945 ;

   946 

   947 /* Forward reference */

   948 static int process_input(struct callback_data *p, FILE *in);

   949 

   950 /*

   951 ** Make sure the database is open.  If it is not, then open it.  If

   952 ** the database fails to open, print an error message and exit.

   953 */

   954 static void open_db(struct callback_data *p){

   955   if( p->db==0 ){

   956     sqlite3_open(p->zDbFilename, &p->db);

   957     db = p->db;

   958     if( db && sqlite3_errcode(db)==SQLITE_OK ){

   959       sqlite3_create_function(db, "shellstatic", 0, SQLITE_UTF8, 0,

   960           shellstaticFunc, 0, 0);

   961     }

   962     if( db==0 || SQLITE_OK!=sqlite3_errcode(db) ){

   963       fprintf(stderr,"Unable to open database \"%s\": %s\n", 

   964           p->zDbFilename, sqlite3_errmsg(db));

   965       exit(1);

   966     }

   967 #ifndef SQLITE_OMIT_LOAD_EXTENSION

   968     sqlite3_enable_load_extension(p->db, 1);

   969 #endif

   970   }

   971 }

   972 

   973 /*

   974 ** Do C-language style dequoting.

   975 **

   976 **    \t    -> tab

   977 **    \n    -> newline

   978 **    \r    -> carriage return

   979 **    \NNN  -> ascii character NNN in octal

   980 **    \\    -> backslash

   981 */

   982 static void resolve_backslashes(char *z){

   983   int i, j, c;

   984   for(i=j=0; (c = z[i])!=0; i++, j++){

   985     if( c=='\\' ){

   986       c = z[++i];

   987       if( c=='n' ){

   988         c = '\n';

   989       }else if( c=='t' ){

   990         c = '\t';

   991       }else if( c=='r' ){

   992         c = '\r';

   993       }else if( c>='0' && c<='7' ){

   994         c -= '0';

   995         if( z[i+1]>='0' && z[i+1]<='7' ){

   996           i++;

   997           c = (c<<3) + z[i] - '0';

   998           if( z[i+1]>='0' && z[i+1]<='7' ){

   999             i++;

  1000             c = (c<<3) + z[i] - '0';

  1001           }

  1002         }

  1003       }

  1004     }

  1005     z[j] = c;

  1006   }

  1007   z[j] = 0;

  1008 }

  1009 

  1010 /*

  1011 ** Interpret zArg as a boolean value.  Return either 0 or 1.

  1012 */

  1013 static int booleanValue(char *zArg){

  1014   int val = atoi(zArg);

  1015   int j;

  1016   for(j=0; zArg[j]; j++){

  1017     zArg[j] = tolower(zArg[j]);

  1018   }

  1019   if( strcmp(zArg,"on")==0 ){

  1020     val = 1;

  1021   }else if( strcmp(zArg,"yes")==0 ){

  1022     val = 1;

  1023   }

  1024   return val;

  1025 }

  1026 

  1027 /*

  1028 ** If an input line begins with "." then invoke this routine to

  1029 ** process that line.

  1030 **

  1031 ** Return 1 on error, 2 to exit, and 0 otherwise.

  1032 */

  1033 static int do_meta_command(char *zLine, struct callback_data *p){

  1034   int i = 1;

  1035   int nArg = 0;

  1036   int n, c;

  1037   int rc = 0;

  1038   char *azArg[50];

  1039 

  1040   /* Parse the input line into tokens.

  1041   */

  1042   while( zLine[i] && nArg<ArraySize(azArg) ){

  1043     while( isspace((unsigned char)zLine[i]) ){ i++; }

  1044     if( zLine[i]==0 ) break;

  1045     if( zLine[i]=='\'' || zLine[i]=='"' ){

  1046       int delim = zLine[i++];

  1047       azArg[nArg++] = &zLine[i];

  1048       while( zLine[i] && zLine[i]!=delim ){ i++; }

  1049       if( zLine[i]==delim ){

  1050         zLine[i++] = 0;

  1051       }

  1052       if( delim=='"' ) resolve_backslashes(azArg[nArg-1]);

  1053     }else{

  1054       azArg[nArg++] = &zLine[i];

  1055       while( zLine[i] && !isspace((unsigned char)zLine[i]) ){ i++; }

  1056       if( zLine[i] ) zLine[i++] = 0;

  1057       resolve_backslashes(azArg[nArg-1]);

  1058     }

  1059   }

  1060 

  1061   /* Process the input line.

  1062   */

  1063   if( nArg==0 ) return rc;

  1064   n = strlen(azArg[0]);

  1065   c = azArg[0][0];

  1066   if( c=='b' && n>1 && strncmp(azArg[0], "bail", n)==0 && nArg>1 ){

  1067     bail_on_error = booleanValue(azArg[1]);

  1068   }else

  1069 

  1070   if( c=='d' && n>1 && strncmp(azArg[0], "databases", n)==0 ){

  1071     struct callback_data data;

  1072     char *zErrMsg = 0;

  1073     open_db(p);

  1074     memcpy(&data, p, sizeof(data));

  1075     data.showHeader = 1;

  1076     data.mode = MODE_Column;

  1077     data.colWidth[0] = 3;

  1078     data.colWidth[1] = 15;

  1079     data.colWidth[2] = 58;

  1080     data.cnt = 0;

  1081     sqlite3_exec(p->db, "PRAGMA database_list; ", callback, &data, &zErrMsg);

  1082     if( zErrMsg ){

  1083       fprintf(stderr,"Error: %s\n", zErrMsg);

  1084       sqlite3_free(zErrMsg);

  1085     }

  1086   }else

  1087 

  1088   if( c=='d' && strncmp(azArg[0], "dump", n)==0 ){

  1089     char *zErrMsg = 0;

  1090     open_db(p);

  1091     fprintf(p->out, "BEGIN TRANSACTION;\n");

  1092     p->writableSchema = 0;

  1093     if( nArg==1 ){

  1094       run_schema_dump_query(p, 

  1095         "SELECT name, type, sql FROM sqlite_master "

  1096         "WHERE sql NOT NULL AND type=='table'", 0

  1097       );

  1098       run_table_dump_query(p->out, p->db,

  1099         "SELECT sql FROM sqlite_master "

  1100         "WHERE sql NOT NULL AND type IN ('index','trigger','view')"

  1101       );

  1102     }else{

  1103       int i;

  1104       for(i=1; i<nArg; i++){

  1105         zShellStatic = azArg[i];

  1106         run_schema_dump_query(p,

  1107           "SELECT name, type, sql FROM sqlite_master "

  1108           "WHERE tbl_name LIKE shellstatic() AND type=='table'"

  1109           "  AND sql NOT NULL", 0);

  1110         run_table_dump_query(p->out, p->db,

  1111           "SELECT sql FROM sqlite_master "

  1112           "WHERE sql NOT NULL"

  1113           "  AND type IN ('index','trigger','view')"

  1114           "  AND tbl_name LIKE shellstatic()"

  1115         );

  1116         zShellStatic = 0;

  1117       }

  1118     }

  1119     if( p->writableSchema ){

  1120       fprintf(p->out, "PRAGMA writable_schema=OFF;\n");

  1121       p->writableSchema = 0;

  1122     }

  1123     if( zErrMsg ){

  1124       fprintf(stderr,"Error: %s\n", zErrMsg);

  1125       sqlite3_free(zErrMsg);

  1126     }else{

  1127       fprintf(p->out, "COMMIT;\n");

  1128     }

  1129   }else

  1130 

  1131   if( c=='e' && strncmp(azArg[0], "echo", n)==0 && nArg>1 ){

  1132     p->echoOn = booleanValue(azArg[1]);

  1133   }else

  1134 

  1135   if( c=='e' && strncmp(azArg[0], "exit", n)==0 ){