/*

** 2001 September 15

**

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

** a legal notice, here is a blessing:

**

**    May you do good and not evil.

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

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

**

*************************************************************************

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

** utility for accessing SQLite databases.

**

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

*/

#include <stdlib.h>

#include <string.h>

#include <stdio.h>

#include <assert.h>

#include "sqlite3.h"

#include <ctype.h>

#include <stdarg.h>

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

# include <signal.h>

# include <pwd.h>

# include <unistd.h>

# include <sys/types.h>

#endif

#ifdef __OS2__

# include <unistd.h>

#endif

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

# include <readline/readline.h>

# include <readline/history.h>

#else

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

# define add_history(X)

# define read_history(X)

# define write_history(X)

# define stifle_history(X)

#endif

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

# include <io.h>

#else

/* Make sure isatty() has a prototype.

*/

extern int isatty();

#endif

#if defined(_WIN32_WCE)

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

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

 * overridden with the -batch command line option.

 */

#define isatty(x) 1

#endif

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

#include <sys/time.h>

#include <sys/resource.h>

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

static struct rusage sBegin;

/* True if the timer is enabled */

static int enableTimer = 0;

/*

** Begin timing an operation

*/

static void beginTimer(void){

  if( enableTimer ){

    getrusage(RUSAGE_SELF, &sBegin);

  }

}

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

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

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

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

}

/*

** Print the timing results.

*/

static void endTimer(void){

  if( enableTimer ){

    struct rusage sEnd;

    getrusage(RUSAGE_SELF, &sEnd);

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

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

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

  }

}

#define BEGIN_TIMER beginTimer()

#define END_TIMER endTimer()

#define HAS_TIMER 1

#else

#define BEGIN_TIMER 

#define END_TIMER

#define HAS_TIMER 0

#endif

/*

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

** at an error if we are not interactive.

*/

static int bail_on_error = 0;

/*

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

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

*/

static int stdin_is_interactive = 1;

/*

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

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

** by the SIGINT handler to interrupt database processing.

*/

static sqlite3 *db = 0;

/*

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

*/

static volatile int seenInterrupt = 0;

/*

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

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

*/

static char *Argv0;

/*

** Prompt strings. Initialized in main. Settable with

**   .prompt main continue

*/

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

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

/*

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

*/

#ifdef SQLITE_ENABLE_IOTRACE

static FILE *iotrace = 0;

#endif

/*

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

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

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

** is written to iotrace.

*/

#ifdef SQLITE_ENABLE_IOTRACE

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

  va_list ap;

  char *z;

  if( iotrace==0 ) return;

  va_start(ap, zFormat);

  z = sqlite3_vmprintf(zFormat, ap);

  va_end(ap);

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

  sqlite3_free(z);

}

#endif

/*

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

*/

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

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

  if( !isdigit(*z) ){

    return 0;

  }

  z++;

  if( realnum ) *realnum = 0;

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

  if( *z=='.' ){

    z++;

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

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

    if( realnum ) *realnum = 1;

  }

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

    z++;

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

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

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

    if( realnum ) *realnum = 1;

  }

  return *z==0;

}

/*

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

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

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

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

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

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

*/

static const char *zShellStatic = 0;

static void shellstaticFunc(

  sqlite3_context *context,

  int argc,

  sqlite3_value **argv

){

  assert( 0==argc );

  assert( zShellStatic );

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

}

/*

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

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

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

** fails.

**

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

** is done.

*/

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

  char *zLine;

  int nLine;

  int n;

  int eol;

  if( zPrompt && *zPrompt ){

    printf("%s",zPrompt);

    fflush(stdout);

  }

  nLine = 100;

  zLine = malloc( nLine );

  if( zLine==0 ) return 0;

  n = 0;

  eol = 0;

  while( !eol ){

    if( n+100>nLine ){

      nLine = nLine*2 + 100;

      zLine = realloc(zLine, nLine);

      if( zLine==0 ) return 0;

    }

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

      if( n==0 ){

        free(zLine);

        return 0;

      }

      zLine[n] = 0;

      eol = 1;

      break;

    }

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

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

      n--;

      zLine[n] = 0;

      eol = 1;

    }

  }

  zLine = realloc( zLine, n+1 );

  return zLine;

}

/*

** Retrieve a single line of input text.

**

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

** string, then issue a continuation prompt.

*/

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

  char *zPrompt;

  char *zResult;

  if( in!=0 ){

    return local_getline(0, in);

  }

  if( zPrior && zPrior[0] ){

    zPrompt = continuePrompt;

  }else{

    zPrompt = mainPrompt;

  }

  zResult = readline(zPrompt);

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

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

#endif

  return zResult;

}

struct previous_mode_data {

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

  int mode;

  int showHeader;

  int colWidth[100];

};

/*

** 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.

*/

struct callback_data {

  sqlite3 *db;            /* The database */

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

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

  FILE *out;             /* Write results here */

  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 */

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

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

  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

                         ** the database */

  struct previous_mode_data explainPrev;

                         /* Holds the mode information just before

                         ** .explain ON */

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

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

};

/*

** These are the allowed modes.

*/

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

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

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

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

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

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

#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 */

static const char *modeDescr[] = {

  "line",

  "column",

  "list",

  "semi",

  "html",

  "insert",

  "tcl",

  "csv",

  "explain",

};

/*

** Number of elements in an array

*/

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

/*

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

*/

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

  int i;

  int nSingle = 0;

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

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

  }

  if( nSingle==0 ){

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

  }else{

    fprintf(out,"'");

    while( *z ){

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

      if( i==0 ){

        fprintf(out,"''");

        z++;

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

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

        z += i+1;

      }else{

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

        break;

      }

    }

    fprintf(out,"'");

  }

}

/*

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

*/

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

  unsigned int c;

  fputc('"', out);

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

    if( c=='\\' ){

      fputc(c, out);

      fputc(c, out);

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

      fputc('\\', out);

      fputc('t', out);

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

      fputc('\\', out);

      fputc('n', out);

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

      fputc('\\', out);

      fputc('r', out);

    }else if( !isprint(c) ){

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

    }else{

      fputc(c, out);

    }

  }

  fputc('"', out);

}

/*

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

** HTML escaped.

*/

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

  int i;

  while( *z ){

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

    if( i>0 ){

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

    }

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

      fprintf(out,"<");

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

      fprintf(out,"&");

    }else{

      break;

    }

    z += i + 1;

  }

}

/*

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

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

*/

static const char needCsvQuote[] = {

  1, 1, 1, 1, 1, 1, 1, 1,   1, 1, 1, 1, 1, 1, 1, 1,   

  1, 1, 1, 1, 1, 1, 1, 1,   1, 1, 1, 1, 1, 1, 1, 1,   

  1, 0, 1, 0, 0, 0, 0, 1,   0, 0, 0, 0, 0, 0, 0, 0, 

  0, 0, 0, 0, 0, 0, 0, 0,   0, 0, 0, 0, 0, 0, 0, 0, 

  0, 0, 0, 0, 0, 0, 0, 0,   0, 0, 0, 0, 0, 0, 0, 0, 

  0, 0, 0, 0, 0, 0, 0, 0,   0, 0, 0, 0, 0, 0, 0, 0, 

  0, 0, 0, 0, 0, 0, 0, 0,   0, 0, 0, 0, 0, 0, 0, 0, 

  0, 0, 0, 0, 0, 0, 0, 0,   0, 0, 0, 0, 0, 0, 0, 1, 

  1, 1, 1, 1, 1, 1, 1, 1,   1, 1, 1, 1, 1, 1, 1, 1,   

  1, 1, 1, 1, 1, 1, 1, 1,   1, 1, 1, 1, 1, 1, 1, 1,   

  1, 1, 1, 1, 1, 1, 1, 1,   1, 1, 1, 1, 1, 1, 1, 1,   

  1, 1, 1, 1, 1, 1, 1, 1,   1, 1, 1, 1, 1, 1, 1, 1,   

  1, 1, 1, 1, 1, 1, 1, 1,   1, 1, 1, 1, 1, 1, 1, 1,   

  1, 1, 1, 1, 1, 1, 1, 1,   1, 1, 1, 1, 1, 1, 1, 1,   

  1, 1, 1, 1, 1, 1, 1, 1,   1, 1, 1, 1, 1, 1, 1, 1,   

  1, 1, 1, 1, 1, 1, 1, 1,   1, 1, 1, 1, 1, 1, 1, 1,   

};

/*

** 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 using ANSI-C rules.  Numbers

** appear outside of quotes.

*/

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

  FILE *out = p->out;

  if( z==0 ){

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

  }else{

    int i;

    int nSep = strlen(p->separator);

    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)) ){

        i = 0;

        break;

      }

    }

    if( i==0 ){

      putc('"', out);

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

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

        putc(z[i], out);

      }

      putc('"', out);

    }else{

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

    }

  }

  if( bSep ){

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

  }

}

#ifdef SIGINT

/*

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

*/

static void interrupt_handler(int NotUsed){

  seenInterrupt = 1;

  if( db ) sqlite3_interrupt(db);

}

#endif

/*

** This is the callback routine that the SQLite library

** invokes for each row of a query result.

*/

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

  int i;

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

  switch( p->mode ){

    case MODE_Line: {

      int w = 5;

      if( azArg==0 ) break;

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

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

        if( len>w ) w = len;

      }

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

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

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

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

      }

      break;