/*

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

*/

#include <stdlib.h>

#include <string.h>

#include <stdio.h>

#include <assert.h>

#include "sqlite3.h"

#include <ctype.h>

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

# include <signal.h>

# include <pwd.h>

# include <unistd.h>

# include <sys/types.h>

#endif

#ifdef __MACOS__

# include <console.h>

# include <signal.h>

# include <unistd.h>

# include <extras.h>

# include <Files.h>

# include <Folders.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

/* Make sure isatty() has a prototype.

*/

extern int isatty();

/*

** 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: "   ...> " */

/*

** 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.  "isatty" is true if text

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

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

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

**

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

  char *zKey;                 /* Encryption key */

};

/*

** 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_NUM_OF   8  /* The number of modes (not a mode itself) */

static const char *modeDescr[MODE_NUM_OF] = {

  "line",

  "column",

  "list",

  "semi",

  "html",

  "insert",

  "tcl",

  "csv",

};

/*

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

  }

}

/*

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

  if( z==0 ){

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

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

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

  }else{

    output_c_string(p->out, z);

  }

  if( bSep ){

    fprintf(p->out, 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;

    }

    case MODE_Column: {

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

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

          int w, n;

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

             w = p->colWidth[i];

          }else{

             w = 0;

          }

          if( w<=0 ){

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

            if( w<10 ) w = 10;

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

            if( w<n ) w = n;

          }

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

            p->actualWidth[i] = w;

          }

          if( p->showHeader ){

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

          }

        }

        if( p->showHeader ){

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

            int w;

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

               w = p->actualWidth[i];

            }else{

               w = 10;

            }

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

                   "----------------------------------------------------------",

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

          }

        }

      }

      if( azArg==0 ) break;

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

        int w;

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

           w = p->actualWidth[i];

        }else{

           w = 10;

        }

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

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

      }

      break;

    }

    case MODE_Semi:

    case MODE_List: {

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

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

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

        }

      }

      if( azArg==0 ) break;

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

        char *z = azArg[i];

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

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

        if( i<nArg-1 ){

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

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

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

        }else{

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

        }

      }

      break;

    }

    case MODE_Html: {

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

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

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

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

        }

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

      }

      if( azArg==0 ) break;

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

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

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

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

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

      }

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

      break;

    }

    case MODE_Tcl: {

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

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

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

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

        }

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

      }

      if( azArg==0 ) break;

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

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

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

      }

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

      break;

    }

    case MODE_Csv: {

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

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

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

        }

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

      }

      if( azArg==0 ) break;

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

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

      }

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

      break;

    }

    case MODE_Insert: {

      if( azArg==0 ) break;

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

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

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

        if( azArg[i]==0 ){

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

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

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