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.147 2006/08/29 12:04:19 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 

    24 #if !defined(_WIN32) && !defined(WIN32) && !defined(__MACOS__) && !defined(__OS2__)

    25 # include <signal.h>

    26 # include <pwd.h>

    27 # include <unistd.h>

    28 # include <sys/types.h>

    29 #endif

    30 

    31 #ifdef __MACOS__

    32 # include <console.h>

    33 # include <signal.h>

    34 # include <unistd.h>

    35 # include <extras.h>

    36 # include <Files.h>

    37 # include <Folders.h>

    38 #endif

    39 

    40 #ifdef __OS2__

    41 # include <unistd.h>

    42 #endif

    43 

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

    45 # include <readline/readline.h>

    46 # include <readline/history.h>

    47 #else

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

    49 # define add_history(X)

    50 # define read_history(X)

    51 # define write_history(X)

    52 # define stifle_history(X)

    53 #endif

    54 

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

    56 */

    57 extern int isatty();

    58 

    59 /*

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

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

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

    63 */

    64 static sqlite3 *db = 0;

    65 

    66 /*

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

    68 */

    69 static volatile int seenInterrupt = 0;

    70 

    71 /*

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

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

    74 */

    75 static char *Argv0;

    76 

    77 /*

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

    79 **   .prompt main continue

    80 */

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

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

    83 

    84 

    85 /*

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

    87 */

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

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

    90   if( !isdigit(*z) ){

    91     return 0;

    92   }

    93   z++;

    94   if( realnum ) *realnum = 0;

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

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

    97     z++;

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

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

   100     if( realnum ) *realnum = 1;

   101   }

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

   103     z++;

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

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

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

   107     if( realnum ) *realnum = 1;

   108   }

   109   return *z==0;

   110 }

   111 

   112 /*

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

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

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

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

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

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

   119 */

   120 static const char *zShellStatic = 0;

   121 static void shellstaticFunc(

   122   sqlite3_context *context,

   123   int argc,

   124   sqlite3_value **argv

   125 ){

   126   assert( 0==argc );

   127   assert( zShellStatic );

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

   129 }

   130 

   131 

   132 /*

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

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

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

   136 ** fails.

   137 **

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

   139 ** is done.

   140 */

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

   142   char *zLine;

   143   int nLine;

   144   int n;

   145   int eol;

   146 

   147   if( zPrompt && *zPrompt ){

   148     printf("%s",zPrompt);

   149     fflush(stdout);

   150   }

   151   nLine = 100;

   152   zLine = malloc( nLine );

   153   if( zLine==0 ) return 0;

   154   n = 0;

   155   eol = 0;

   156   while( !eol ){

   157     if( n+100>nLine ){

   158       nLine = nLine*2 + 100;

   159       zLine = realloc(zLine, nLine);

   160       if( zLine==0 ) return 0;

   161     }

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

   163       if( n==0 ){

   164         free(zLine);

   165         return 0;

   166       }

   167       zLine[n] = 0;

   168       eol = 1;

   169       break;

   170     }

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

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

   173       n--;

   174       zLine[n] = 0;

   175       eol = 1;

   176     }

   177   }

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

   179   return zLine;

   180 }

   181 

   182 /*

   183 ** Retrieve a single line of input text.  "isatty" is true if text

   184 ** is coming from a terminal.  In that case, we issue a prompt and

   185 ** attempt to use "readline" for command-line editing.  If "isatty"

   186 ** is false, use "local_getline" instead of "readline" and issue no prompt.

   187 **

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

   189 ** string, then issue a continuation prompt.

   190 */

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

   192   char *zPrompt;

   193   char *zResult;

   194   if( in!=0 ){

   195     return local_getline(0, in);

   196   }

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

   198     zPrompt = continuePrompt;

   199   }else{

   200     zPrompt = mainPrompt;

   201   }

   202   zResult = readline(zPrompt);

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

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

   205 #endif

   206   return zResult;

   207 }

   208 

   209 struct previous_mode_data {

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

   211   int mode;

   212   int showHeader;

   213   int colWidth[100];

   214 };

   215 /*

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

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

   218 ** state and mode information.

   219 */

   220 struct callback_data {

   221   sqlite3 *db;            /* The database */

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

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

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

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

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

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

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

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

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

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

   232                          ** the database */

   233   struct previous_mode_data explainPrev;

   234                          /* Holds the mode information just before

   235                          ** .explain ON */

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

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

   238   char *zKey;                 /* Encryption key */

   239 };

   240 

   241 /*

   242 ** These are the allowed modes.

   243 */

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

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

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

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

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

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

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

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

   252 #define MODE_NUM_OF   8  /* The number of modes (not a mode itself) */

   253 

   254 static const char *modeDescr[MODE_NUM_OF] = {

   255   "line",

   256   "column",

   257   "list",

   258   "semi",

   259   "html",

   260   "insert",

   261   "tcl",

   262   "csv",

   263 };

   264 

   265 /*

   266 ** Number of elements in an array

   267 */

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

   269 

   270 /*

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

   272 */

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

   274   int i;

   275   int nSingle = 0;

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

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

   278   }

   279   if( nSingle==0 ){

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

   281   }else{

   282     fprintf(out,"'");

   283     while( *z ){

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

   285       if( i==0 ){

   286         fprintf(out,"''");

   287         z++;

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

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

   290         z += i+1;

   291       }else{

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

   293         break;

   294       }

   295     }

   296     fprintf(out,"'");

   297   }

   298 }

   299 

   300 /*

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

   302 */

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

   304   unsigned int c;

   305   fputc('"', out);

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

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

   308       fputc(c, out);

   309       fputc(c, out);

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

   311       fputc('\\', out);

   312       fputc('t', out);

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

   314       fputc('\\', out);

   315       fputc('n', out);

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

   317       fputc('\\', out);

   318       fputc('r', out);

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

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

   321     }else{

   322       fputc(c, out);

   323     }

   324   }

   325   fputc('"', out);

   326 }

   327 

   328 /*

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

   330 ** HTML escaped.

   331 */

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

   333   int i;

   334   while( *z ){

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

   336     if( i>0 ){

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

   338     }

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

   340       fprintf(out,"<");

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

   342       fprintf(out,"&");

   343     }else{

   344       break;

   345     }

   346     z += i + 1;

   347   }

   348 }

   349 

   350 /*

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

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

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

   354 ** appear outside of quotes.

   355 */

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

   357   if( z==0 ){

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

   359   }else if( isNumber(z, 0) ){

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

   361   }else{

   362     output_c_string(p->out, z);

   363   }

   364   if( bSep ){

   365     fprintf(p->out, p->separator);

   366   }

   367 }

   368 

   369 #ifdef SIGINT

   370 /*

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

   372 */

   373 static void interrupt_handler(int NotUsed){

   374   seenInterrupt = 1;

   375   if( db ) sqlite3_interrupt(db);

   376 }

   377 #endif

   378 

   379 /*

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

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

   382 */

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

   384   int i;

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

   386   switch( p->mode ){

   387     case MODE_Line: {

   388       int w = 5;

   389       if( azArg==0 ) break;

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

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

   392         if( len>w ) w = len;

   393       }

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

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

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

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

   398       }

   399       break;

   400     }

   401     case MODE_Column: {

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

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

   404           int w, n;

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

   406              w = p->colWidth[i];

   407           }else{

   408              w = 0;

   409           }

   410           if( w<=0 ){

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

   412             if( w<10 ) w = 10;

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

   414             if( w<n ) w = n;

   415           }

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

   417             p->actualWidth[i] = w;

   418           }

   419           if( p->showHeader ){

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

   421           }

   422         }

   423         if( p->showHeader ){

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

   425             int w;

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

   427                w = p->actualWidth[i];

   428             }else{

   429                w = 10;

   430             }

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

   432                    "----------------------------------------------------------",

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

   434           }

   435         }

   436       }

   437       if( azArg==0 ) break;

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

   439         int w;

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

   441            w = p->actualWidth[i];

   442         }else{

   443            w = 10;

   444         }

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

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

   447       }

   448       break;

   449     }

   450     case MODE_Semi:

   451     case MODE_List: {

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

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

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

   455         }

   456       }

   457       if( azArg==0 ) break;

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

   459         char *z = azArg[i];

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

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

   462         if( i<nArg-1 ){

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

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

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

   466         }else{

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

   468         }

   469       }

   470       break;

   471     }

   472     case MODE_Html: {

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

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

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

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

   477         }

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

   479       }

   480       if( azArg==0 ) break;

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

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

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

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

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

   486       }

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

   488       break;

   489     }

   490     case MODE_Tcl: {

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

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

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

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

   495         }

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

   497       }

   498       if( azArg==0 ) break;

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

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

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

   502       }

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

   504       break;

   505     }

   506     case MODE_Csv: {

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

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

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

   510         }

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

   512       }

   513       if( azArg==0 ) break;

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

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

   516       }

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

   518       break;

   519     }

   520     case MODE_Insert: {

   521       if( azArg==0 ) break;

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

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

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

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

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

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

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

   529         }else{

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

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

   532         }

   533       }

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

   535       break;

   536     }

   537   }

   538   return 0;

   539 }

   540 

   541 /*

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

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

   544 ** table name.

   545 */

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

   547   int i, n;

   548   int needQuote;

   549   char *z;

   550 

   551   if( p->zDestTable ){

   552     free(p->zDestTable);

   553     p->zDestTable = 0;

   554   }

   555   if( zName==0 ) return;

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

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

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

   559       needQuote = 1;

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

   561     }

   562   }

   563   if( needQuote ) n += 2;

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

   565   if( z==0 ){

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

   567     exit(1);

   568   }

   569   n = 0;

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

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

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

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

   574   }

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

   576   z[n] = 0;

   577 }

   578 

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

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

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

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

   583 **

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

   585 ** quote character for zAppend.

   586 */

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

   588   int len;

   589   int i;

   590   int nAppend = strlen(zAppend);

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

   592 

   593   len = nAppend+nIn+1;

   594   if( quote ){

   595     len += 2;

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

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

   598     }

   599   }

   600 

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

   602   if( !zIn ){

   603     return 0;

   604   }

   605 

   606   if( quote ){

   607     char *zCsr = &zIn[nIn];

   608     *zCsr++ = quote;

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

   610       *zCsr++ = zAppend[i];

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

   612     }

   613     *zCsr++ = quote;

   614     *zCsr++ = '\0';

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

   616   }else{

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

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

   619   }

   620 

   621   return zIn;

   622 }

   623 

   624 

   625 /*

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

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

   628 */

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

   630   sqlite3_stmt *pSelect;

   631   int rc;

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

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

   634     return rc;

   635   }

   636   rc = sqlite3_step(pSelect);

   637   while( rc==SQLITE_ROW ){

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

   639     rc = sqlite3_step(pSelect);

   640   }

   641   return sqlite3_finalize(pSelect);

   642 }

   643 

   644 

   645 /*

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

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

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

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

   650 */

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

   652   int rc;

   653   const char *zTable;

   654   const char *zType;

   655   const char *zSql;

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

   657 

   658   if( nArg!=3 ) return 1;

   659   zTable = azArg[0];

   660   zType = azArg[1];

   661   zSql = azArg[2];

   662   

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

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

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

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

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

   668     return 0;

   669   }else{

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

   671   }

   672 

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

   674     sqlite3_stmt *pTableInfo = 0;

   675     char *zSelect = 0;

   676     char *zTableInfo = 0;

   677     char *zTmp = 0;

   678    

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

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

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

   682 

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

   684     if( zTableInfo ) free(zTableInfo);

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

   686       return 1;

   687     }

   688 

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

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

   691     if( zTmp ){

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

   693     }

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

   695     rc = sqlite3_step(pTableInfo);

   696     while( rc==SQLITE_ROW ){

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

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

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

   700       rc = sqlite3_step(pTableInfo);

   701       if( rc==SQLITE_ROW ){

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

   703       }else{

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

   705       }

   706     }

   707     rc = sqlite3_finalize(pTableInfo);

   708     if( rc!=SQLITE_OK ){

   709       if( zSelect ) free(zSelect);

   710       return 1;

   711     }

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

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

   714 

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

   716     if( rc==SQLITE_CORRUPT ){

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

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

   719     }

   720     if( zSelect ) free(zSelect);

   721     if( rc!=SQLITE_OK ){

   722       return 1;

   723     }

   724   }

   725   return 0;

   726 }

   727 

   728 /*

   729 ** Run zQuery.  Update dump_callback() as the callback routine.

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

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

   732 */

   733 static int run_schema_dump_query(

   734   struct callback_data *p, 

   735   const char *zQuery,

   736   char **pzErrMsg

   737 ){

   738   int rc;

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

   740   if( rc==SQLITE_CORRUPT ){

   741     char *zQ2;

   742     int len = strlen(zQuery);

   743     if( pzErrMsg ) sqlite3_free(*pzErrMsg);

   744     zQ2 = malloc( len+100 );

   745     if( zQ2==0 ) return rc;

   746     sprintf(zQ2, "%s ORDER BY rowid DESC", zQuery);

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

   748     free(zQ2);

   749   }

   750   return rc;

   751 }

   752 

   753 /*

   754 ** Text of a help message

   755 */

   756 static char zHelp[] =

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

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

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

   760   ".exit                  Exit this program\n"

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

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

   763   ".help                  Show this message\n"

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

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

   766 #ifndef SQLITE_OMIT_LOAD_EXTENSION

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

   768 #endif

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

   770   "                         csv      Comma-separated values\n"

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

   772   "                         html     HTML <table> code\n"

   773   "                         insert   SQL insert statements for TABLE\n"

   774   "                         line     One value per line\n"

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

   776   "                         tabs     Tab-separated values\n"

   777   "                         tcl      TCL list elements\n"

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

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

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

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

   782   ".quit                  Exit this program\n"

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

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

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

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

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

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

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

   790 ;

   791 

   792 /* Forward reference */

   793 static void process_input(struct callback_data *p, FILE *in);

   794 

   795 /*

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

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

   798 */

   799 static void open_db(struct callback_data *p){

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

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

   802     db = p->db;

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

   804         shellstaticFunc, 0, 0);

   805     if( SQLITE_OK!=sqlite3_errcode(db) ){

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

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

   808       exit(1);

   809     }

   810 #ifndef SQLITE_OMIT_LOAD_EXTENSION

   811     sqlite3_enable_load_extension(p->db, 1);

   812 #endif

   813   }

   814 }

   815 

   816 /*

   817 ** Do C-language style dequoting.

   818 **

   819 **    \t    -> tab

   820 **    \n    -> newline

   821 **    \r    -> carriage return

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

   823 **    \\    -> backslash

   824 */

   825 static void resolve_backslashes(char *z){

   826   int i, j, c;

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

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

   829       c = z[++i];

   830       if( c=='n' ){

   831         c = '\n';

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

   833         c = '\t';

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

   835         c = '\r';

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

   837         c -= '0';

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

   839           i++;

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

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

   842             i++;

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

   844           }

   845         }

   846       }

   847     }

   848     z[j] = c;

   849   }

   850   z[j] = 0;

   851 }

   852 

   853 /*

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

   855 ** process that line.

   856 **

   857 ** Return 1 to exit and 0 to continue.

   858 */

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

   860   int i = 1;

   861   int nArg = 0;

   862   int n, c;

   863   int rc = 0;

   864   char *azArg[50];

   865 

   866   /* Parse the input line into tokens.

   867   */

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

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

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

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

   872       int delim = zLine[i++];

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

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

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

   876         zLine[i++] = 0;

   877       }

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

   879     }else{

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

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

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

   883       resolve_backslashes(azArg[nArg-1]);

   884     }

   885   }

   886 

   887   /* Process the input line.

   888   */

   889   if( nArg==0 ) return rc;

   890   n = strlen(azArg[0]);

   891   c = azArg[0][0];

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

   893     struct callback_data data;

   894     char *zErrMsg = 0;

   895     open_db(p);

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

   897     data.showHeader = 1;

   898     data.mode = MODE_Column;

   899     data.colWidth[0] = 3;

   900     data.colWidth[1] = 15;

   901     data.colWidth[2] = 58;

   902     data.cnt = 0;

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

   904     if( zErrMsg ){

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

   906       sqlite3_free(zErrMsg);

   907     }

   908   }else

   909 

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

   911     char *zErrMsg = 0;

   912     open_db(p);

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

   914     if( nArg==1 ){

   915       run_schema_dump_query(p, 

   916         "SELECT name, type, sql FROM sqlite_master "

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

   918       );

   919       run_schema_dump_query(p, 

   920         "SELECT name, type, sql FROM sqlite_master "

   921         "WHERE sql NOT NULL AND type!='table' AND type!='meta'", 0

   922       );

   923     }else{

   924       int i;

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

   926         zShellStatic = azArg[i];

   927         run_schema_dump_query(p,

   928           "SELECT name, type, sql FROM sqlite_master "

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

   930           "  AND sql NOT NULL", 0);

   931         run_schema_dump_query(p,

   932           "SELECT name, type, sql FROM sqlite_master "

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

   934           "  AND type!='meta' AND sql NOT NULL", 0);

   935         zShellStatic = 0;

   936       }

   937     }

   938     if( zErrMsg ){

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

   940       sqlite3_free(zErrMsg);

   941     }else{

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

   943     }

   944   }else

   945 

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

   947     int j;

   948     char *z = azArg[1];

   949     int val = atoi(azArg[1]);

   950     for(j=0; z[j]; j++){

   951       z[j] = tolower((unsigned char)z[j]);

   952     }

   953     if( strcmp(z,"on")==0 ){

   954       val = 1;

   955     }else if( strcmp(z,"yes")==0 ){

   956       val = 1;

   957     }

   958     p->echoOn = val;

   959   }else

   960 

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

   962     rc = 1;

   963   }else

   964 

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

   966     int j;

   967     static char zOne[] = "1";

   968     char *z = nArg>=2 ? azArg[1] : zOne;

   969     int val = atoi(z);

   970     for(j=0; z[j]; j++){

   971       z[j] = tolower((unsigned char)z[j]);

   972     }

   973     if( strcmp(z,"on")==0 ){

   974       val = 1;

   975     }else if( strcmp(z,"yes")==0 ){

   976       val = 1;

   977     }

   978     if(val == 1) {

   979       if(!p->explainPrev.valid) {

   980         p->explainPrev.valid = 1;

   981         p->explainPrev.mode = p->mode;

   982         p->explainPrev.showHeader = p->showHeader;

   983         memcpy(p->explainPrev.colWidth,p->colWidth,sizeof(p->colWidth));

   984       }

   985       /* We could put this code under the !p->explainValid

   986       ** condition so that it does not execute if we are already in

   987       ** explain mode. However, always executing it allows us an easy

   988       ** was to reset to explain mode in case the user previously

   989       ** did an .explain followed by a .width, .mode or .header

   990       ** command.

   991       */

   992       p->mode = MODE_Column;

   993       p->showHeader = 1;

   994       memset(p->colWidth,0,ArraySize(p->colWidth));

   995       p->colWidth[0] = 4;

   996       p->colWidth[1] = 14;

   997       p->colWidth[2] = 10;

   998       p->colWidth[3] = 10;

   999       p->colWidth[4] = 33;

  1000     }else if (p->explainPrev.valid) {

  1001       p->explainPrev.valid = 0;

  1002       p->mode = p->explainPrev.mode;

  1003       p->showHeader = p->explainPrev.showHeader;

  1004       memcpy(p->colWidth,p->explainPrev.colWidth,sizeof(p->colWidth));

  1005     }

  1006   }else

  1007 

  1008   if( c=='h' && (strncmp(azArg[0], "header", n)==0

  1009                  ||

  1010                  strncmp(azArg[0], "headers", n)==0 )&& nArg>1 ){

  1011     int j;

  1012     char *z = azArg[1];

  1013     int val = atoi(azArg[1]);

  1014     for(j=0; z[j]; j++){

  1015       z[j] = tolower((unsigned char)z[j]);

  1016     }

  1017     if( strcmp(z,"on")==0 ){

  1018       val = 1;

  1019     }else if( strcmp(z,"yes")==0 ){

  1020       val = 1;

  1021     }

  1022     p->showHeader = val;

  1023   }else

  1024 

  1025   if( c=='h' && strncmp(azArg[0], "help", n)==0 ){

  1026     fprintf(stderr,zHelp);

  1027   }else

  1028 

  1029   if( c=='i' && strncmp(azArg[0], "import", n)==0 && nArg>=3 ){

  1030     char *zTable = azArg[2];    /* Insert data into this table */

  1031     char *zFile = azArg[1];     /* The file from which to extract data */

  1032     sqlite3_stmt *pStmt;        /* A statement */

  1033     int rc;                     /* Result code */

  1034     int nCol;                   /* Number of columns in the table */

  1035     int nByte;                  /* Number of bytes in an SQL string */

  1036     int i, j;                   /* Loop counters */

  1037     int nSep;                   /* Number of bytes in p->separator[] */

  1038     char *zSql;                 /* An SQL statement */

  1039     char *zLine;                /* A single line of input from the file */

  1040     char **azCol;               /* zLine[] broken up into columns */

  1041     char *zCommit;              /* How to commit changes */   

  1042     FILE *in;                   /* The input file */

  1043     int lineno = 0;             /* Line number of input file */

  1044 

  1045     open_db(p);

  1046     nSep = strlen(p->separator);

  1047     if( nSep==0 ){

  1048       fprintf(stderr, "non-null separator required for import\n");

  1049       return 0;

  1050     }

  1051     zSql = sqlite3_mprintf("SELECT * FROM '%q'", zTable);

  1052     if( zSql==0 ) return 0;

  1053     nByte = strlen(zSql);

  1054     rc = sqlite3_prepare(p->db, zSql, -1, &pStmt, 0);

  1055     sqlite3_free(zSql);

  1056     if( rc ){

  1057       fprintf(stderr,"Error: %s\n", sqlite3_errmsg(db));

  1058       nCol = 0;

  1059     }else{

  1060       nCol = sqlite3_column_count(pStmt);

  1061     }

  1062     sqlite3_finalize(pStmt);

  1063     if( nCol==0 ) return 0;

  1064     zSql = malloc( nByte + 20 + nCol*2 );

  1065     if( zSql==0 ) return 0;

  1066     sqlite3_snprintf(nByte+20, zSql, "INSERT INTO '%q' VALUES(?", zTable);

  1067     j = strlen(zSql);

  1068     for(i=1; i<nCol; i++){

  1069       zSql[j++] = ',';

  1070       zSql[j++] = '?';

  1071     }

  1072     zSql[j++] = ')';

  1073     zSql[j] = 0;

  1074     rc = sqlite3_prepare(p->db, zSql, -1, &pStmt, 0);

  1075     free(zSql);

  1076     if( rc ){

  1077       fprintf(stderr, "Error: %s\n", sqlite3_errmsg(db));

  1078       sqlite3_finalize(pStmt);

  1079       return 0;

  1080     }

  1081     in = fopen(zFile, "rb");

  1082     if( in==0 ){

  1083       fprintf(stderr, "cannot open file: %s\n", zFile);

  1084       sqlite3_finalize(pStmt);

  1085       return 0;

  1086     }

  1087     azCol = malloc( sizeof(azCol[0])*(nCol+1) );

  1088     if( azCol==0 ){

  1089       fclose(in);

  1090       return 0;

  1091     }

  1092     sqlite3_exec(p->db, "BEGIN", 0, 0, 0);

  1093     zCommit = "COMMIT";

  1094     while( (zLine = local_getline(0, in))!=0 ){

  1095       char *z;

  1096       i = 0;

  1097       lineno++;

  1098       azCol[0] = zLine;

  1099       for(i=0, z=zLine; *z && *z!='\n' && *z!='\r'; z++){

  1100         if( *z==p->separator[0] && strncmp(z, p->separator, nSep)==0 ){

  1101           *z = 0;

  1102           i++;

  1103           if( i<nCol ){

  1104             azCol[i] = &z[nSep];

  1105             z += nSep-1;

  1106           }

  1107         }

  1108       }

  1109       *z = 0;

  1110       if( i+1!=nCol ){

  1111         fprintf(stderr,"%s line %d: expected %d columns of data but found %d\n",

  1112            zFile, lineno, nCol, i+1);

  1113         zCommit = "ROLLBACK";

  1114         break;

  1115       }

  1116       for(i=0; i<nCol; i++){

  1117         sqlite3_bind_text(pStmt, i+1, azCol[i], -1, SQLITE_STATIC);

  1118       }

  1119       sqlite3_step(pStmt);

  1120       rc = sqlite3_reset(pStmt);

  1121       free(zLine);

  1122       if( rc!=SQLITE_OK ){

  1123         fprintf(stderr,"Error: %s\n", sqlite3_errmsg(db));

  1124         zCommit = "ROLLBACK";

  1125         break;

  1126       }

  1127     }

  1128     free(azCol);

  1129     fclose(in);

  1130     sqlite3_finalize(pStmt);

  1131     sqlite3_exec(p->db, zCommit, 0, 0, 0);

  1132   }else

  1133 

  1134   if( c=='i' && strncmp(azArg[0], "indices", n)==0 && nArg>1 ){

  1135     struct callback_data data;