File indexing completed on 2024-05-12 16:13:59

 /*
 ** 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.
 */
 #if defined(_WIN32) || defined(WIN32)
 # if !defined(_CRT_SECURE_NO_WARNINGS)
 /* This needs to come before any includes for MSVC compiler */
 #  define _CRT_SECURE_NO_WARNINGS
 # endif
 # ifndef access
 # include <io.h>
 #  define access(f, m) _access_s(f, m)
 # endif
 #endif
 
 /*
 ** Enable large-file support for fopen() and friends on unix.
 */
 #ifndef SQLITE_DISABLE_LFS
 # define _LARGE_FILE       1
 # ifndef _FILE_OFFSET_BITS
 #   define _FILE_OFFSET_BITS 64
 # endif
 # define _LARGEFILE_SOURCE 1
 #endif
 
 #include <stdlib.h>
 #include <string.h>
 #include <stdio.h>
 #include <assert.h>
 #include "sqlite3.h"
 #include <ctype.h>
 #include <stdarg.h>
 #include <fcntl.h>
 
 #if !defined(_WIN32) && !defined(WIN32) && !defined(__OS2__)
 # include <signal.h>
 # if !defined(__RTP__) && !defined(_WRS_KERNEL)
 #  include <pwd.h>
 # endif
 # include <unistd.h>
 # include <sys/types.h>
 #endif
 
 #ifdef __OS2__
 # include <unistd.h>
 #endif
 
 /*
 ** Used to prevent warnings about unused parameters
 */
 #define UNUSED_PARAMETER(x) (void)(x)
 
 /*
 ** 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;
 
 /*
 ** 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 );
   UNUSED_PARAMETER(argc);
   UNUSED_PARAMETER(argv);
   sqlite3_result_text(context, zShellStatic, -1, SQLITE_STATIC);
 }
 
 /*
 ** 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 statsOn;           /* True to display memory stats before each finalize */
   int cnt;               /* Number of records displayed so far */
   FILE *out;             /* Write results here */
   int nErr;              /* Number of errors seen */
   int mode;              /* An output mode setting */
   int writableSchema;    /* True if PRAGMA writable_schema=ON */
   int showHeader;        /* True to show column names in List or Column mode */
   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 */
   char outfile[FILENAME_MAX]; /* Filename for *out */
   const char *zDbFilename;    /* name of the database file */
   const char *zVfs;           /* Name of VFS to use */
   sqlite3_stmt *pStmt;   /* Current statement if any. */
   FILE *pLog;            /* Write log output here */
 };
 
 /*
 ** 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 */
 
 /*
 ** Number of elements in an array
 */
 #define ArraySize(X)  (int)(sizeof(X)/sizeof(X[0]))
 
 /*
 ** Compute a string length that is limited to what can be stored in
 ** lower 30 bits of a 32-bit signed integer.
 */
 static int strlen30(const char *z){
   const char *z2 = z;
   while( *z2 ){ z2++; }
   return 0x3fffffff & (int)(z2 - z);
 }
 
 /*
 ** A callback for the sqlite3_log() interface.
 */
 static void shellLog(void *pArg, int iErrCode, const char *zMsg){
   struct callback_data *p = (struct callback_data*)pArg;
   if( p->pLog==0 ) return;
   fprintf(p->pLog, "(%d) %s\n", iErrCode, zMsg);
   fflush(p->pLog);
 }
 
 /* zIn is either a pointer to a NULL-terminated string in memory obtained
 ** from malloc(), or a NULL pointer. The string pointed to by zAppend is
 ** added to zIn, and the result returned in memory obtained from malloc().
 ** zIn, if it was not NULL, is freed.
 **
 ** If the third argument, quote, is not '\0', then it is used as a
 ** quote character for zAppend.
 */
 static char *appendText(char *zIn, char const *zAppend, char quote){
   int len;
   int i;
   int nAppend = strlen30(zAppend);
   int nIn = (zIn?strlen30(zIn):0);
 
   len = nAppend+nIn+1;
   if( quote ){
     len += 2;
     for(i=0; i<nAppend; i++){
       if( zAppend[i]==quote ) len++;
     }
   }
 
   zIn = (char *)realloc(zIn, len);
   if( !zIn ){
     return 0;
   }
 
   if( quote ){
     char *zCsr = &zIn[nIn];
     *zCsr++ = quote;
     for(i=0; i<nAppend; i++){
       *zCsr++ = zAppend[i];
       if( zAppend[i]==quote ) *zCsr++ = quote;
     }
     *zCsr++ = quote;
     *zCsr++ = '\0';
     assert( (zCsr-zIn)==len );
   }else{
     memcpy(&zIn[nIn], zAppend, nAppend);
     zIn[len-1] = '\0';
   }
 
   return zIn;
 }
 
 /*
 ** Execute a query statement that has a single result column.  Print
 ** that result column on a line by itself with a semicolon terminator.
 **
 ** This is used, for example, to show the schema of the database by
 ** querying the SQLITE_MASTER table.
 */
 static int run_table_dump_query(
   struct callback_data *p, /* Query context */
   const char *zSelect,     /* SELECT statement to extract content */
   const char *zFirstRow    /* Print before first row, if not NULL */
 ){
   sqlite3_stmt *pSelect;
   int rc;
   rc = sqlite3_prepare(p->db, zSelect, -1, &pSelect, 0);
   if( rc!=SQLITE_OK || !pSelect ){
     fprintf(p->out, "/**** ERROR: (%d) %s *****/\n", rc, sqlite3_errmsg(p->db));
     p->nErr++;
     return rc;
   }
   rc = sqlite3_step(pSelect);
   while( rc==SQLITE_ROW ){
     if( zFirstRow ){
       fprintf(p->out, "%s", zFirstRow);
       zFirstRow = 0;
     }
     fprintf(p->out, "%s;\n", sqlite3_column_text(pSelect, 0));
     rc = sqlite3_step(pSelect);
   }
   rc = sqlite3_finalize(pSelect);
   if( rc!=SQLITE_OK ){
     fprintf(p->out, "/**** ERROR: (%d) %s *****/\n", rc, sqlite3_errmsg(p->db));
     p->nErr++;
   }
   return rc;
 }
 
 /*
 ** This is a different callback routine used for dumping the database.
 ** Each row received by this callback consists of a table name,
 ** the table type ("index" or "table") and SQL to create the table.
 ** This routine should print text sufficient to recreate the table.
 */
 static int dump_callback(void *pArg, int nArg, char **azArg, char **azCol){
   int rc;
   const char *zTable;
   const char *zType;
   const char *zSql;
   const char *zPrepStmt = 0;
   struct callback_data *p = (struct callback_data *)pArg;
 
   UNUSED_PARAMETER(azCol);
   if( nArg!=3 ) return 1;
   zTable = azArg[0];
   zType = azArg[1];
   zSql = azArg[2];
 
   if( strcmp(zTable, "sqlite_sequence")==0 ){
     zPrepStmt = "DELETE FROM sqlite_sequence;\n";
   }else if( strcmp(zTable, "sqlite_stat1")==0 ){
     fprintf(p->out, "ANALYZE sqlite_master;\n");
   }else if( strncmp(zTable, "sqlite_", 7)==0 ){
     return 0;
   }else if( strncmp(zSql, "CREATE VIRTUAL TABLE", 20)==0 ){
     char *zIns;
     if( !p->writableSchema ){
       fprintf(p->out, "PRAGMA writable_schema=ON;\n");
       p->writableSchema = 1;
     }
     zIns = sqlite3_mprintf(
        "INSERT INTO sqlite_master(type,name,tbl_name,rootpage,sql)"
        "VALUES('table','%q','%q',0,'%q');",
        zTable, zTable, zSql);
     fprintf(p->out, "%s\n", zIns);
     sqlite3_free(zIns);
     return 0;
   }else{
     fprintf(p->out, "%s;\n", zSql);
   }
 
   if( strcmp(zType, "table")==0 ){
     sqlite3_stmt *pTableInfo = 0;
     char *zSelect = 0;
     char *zTableInfo = 0;
     char *zTmp = 0;
     int nRow = 0;
 
     zTableInfo = appendText(zTableInfo, "PRAGMA table_info(", 0);
     zTableInfo = appendText(zTableInfo, zTable, '"');
     zTableInfo = appendText(zTableInfo, ");", 0);
 
     rc = sqlite3_prepare(p->db, zTableInfo, -1, &pTableInfo, 0);
     free(zTableInfo);
     if( rc!=SQLITE_OK || !pTableInfo ){
       return 1;
     }
 
     zSelect = appendText(zSelect, "SELECT 'INSERT INTO ' || ", 0);
     zTmp = appendText(zTmp, zTable, '"');
     if( zTmp ){
       zSelect = appendText(zSelect, zTmp, '\'');
       free(zTmp);
     }
     zSelect = appendText(zSelect, " || ' VALUES(' || ", 0);
     rc = sqlite3_step(pTableInfo);
     while( rc==SQLITE_ROW ){
       const char *zText = (const char *)sqlite3_column_text(pTableInfo, 1);
       zSelect = appendText(zSelect, "quote(", 0);
       zSelect = appendText(zSelect, zText, '"');
       rc = sqlite3_step(pTableInfo);
       if( rc==SQLITE_ROW ){
         zSelect = appendText(zSelect, ") || ',' || ", 0);
       }else{
         zSelect = appendText(zSelect, ") ", 0);
       }
       nRow++;
     }
     rc = sqlite3_finalize(pTableInfo);
     if( rc!=SQLITE_OK || nRow==0 ){
       free(zSelect);
       return 1;
     }
     zSelect = appendText(zSelect, "|| ')' FROM  ", 0);
     zSelect = appendText(zSelect, zTable, '"');
 
     rc = run_table_dump_query(p, zSelect, zPrepStmt);
     if( rc==SQLITE_CORRUPT ){
       zSelect = appendText(zSelect, " ORDER BY rowid DESC", 0);
       run_table_dump_query(p, zSelect, 0);
     }
     free(zSelect);
   }
   return 0;
 }
 
 /*
 ** Run zQuery.  Use dump_callback() as the callback routine so that
 ** the contents of the query are output as SQL statements.
 **
 ** If we get a SQLITE_CORRUPT error, rerun the query after appending
 ** "ORDER BY rowid DESC" to the end.
 */
 static int run_schema_dump_query(
   struct callback_data *p,
   const char *zQuery
 ){
   int rc;
   char *zErr = 0;
   rc = sqlite3_exec(p->db, zQuery, dump_callback, p, &zErr);
   if( rc==SQLITE_CORRUPT ){
     char *zQ2;
     int len = strlen30(zQuery);
     fprintf(p->out, "/****** CORRUPTION ERROR *******/\n");
     if( zErr ){
       fprintf(p->out, "/****** %s ******/\n", zErr);
       sqlite3_free(zErr);
       zErr = 0;
     }
     zQ2 = malloc( len+100 );
     if( zQ2==0 ) return rc;
     sqlite3_snprintf(len+100, zQ2, "%s ORDER BY rowid DESC", zQuery);
     rc = sqlite3_exec(p->db, zQ2, dump_callback, p, &zErr);
     if( rc ){
       fprintf(p->out, "/****** ERROR: %s ******/\n", zErr);
     }else{
       rc = SQLITE_CORRUPT;
     }
     sqlite3_free(zErr);
     free(zQ2);
   }
   return rc;
 }
 
 /*
 ** Make sure the database is open.  If it is not, then open it.  If
 ** the database fails to open, print an error message and exit.
 */
 static void open_db(struct callback_data *p){
   if( p->db==0 ){
     sqlite3_open(p->zDbFilename, &p->db);
     db = p->db;
     if( db && sqlite3_errcode(db)==SQLITE_OK ){
       sqlite3_create_function(db, "shellstatic", 0, SQLITE_UTF8, 0,
           shellstaticFunc, 0, 0);
     }
     if( db==0 || SQLITE_OK!=sqlite3_errcode(db) ){
       fprintf(stderr,"Error: unable to open database \"%s\": %s\n",
           p->zDbFilename, sqlite3_errmsg(db));
       exit(1);
     }
 #ifndef SQLITE_OMIT_LOAD_EXTENSION
     sqlite3_enable_load_extension(p->db, 1);
 #endif
   }
 }
 
 static int table_size(struct callback_data *p, const unsigned char* table)
 {
   int rc;
   sqlite3_stmt *pSelect;
   const char *sqlPref = "SELECT COUNT() FROM ";
   char *sql = malloc(strlen(sqlPref) + 1 + strlen((const char*)table));
   strcpy(sql, sqlPref);
   strcat(sql, (const char*)table);
   /*fprintf(stderr, "%s", sql);*/
   rc = sqlite3_prepare(p->db, sql, -1, &pSelect, 0);
   free(sql);
   if( rc!=SQLITE_OK || !pSelect ){
     fprintf(p->out, "/**** ERROR: (%d) %s *****/\n", rc, sqlite3_errmsg(p->db));
     return -1;
   }
   rc = sqlite3_step(pSelect);
   if( rc==SQLITE_ROW ){
     const unsigned char *countStr = sqlite3_column_text(pSelect, 0);
     int res = atoi((const char*)countStr);
     rc = sqlite3_finalize(pSelect);
     if( rc!=SQLITE_OK ){
         fprintf(p->out, "/**** ERROR: (%d) %s *****/\n", rc, sqlite3_errmsg(p->db));
         return -1;
     }
     /*fprintf(stderr, "\n%d?\n", res);*/
     return res;
   }
   rc = sqlite3_finalize(pSelect);
   if( rc!=SQLITE_OK ){
     fprintf(p->out, "/**** ERROR: (%d) %s *****/\n", rc, sqlite3_errmsg(p->db));
   }
   return -1;
 }
 
 /*
 ** If an input line begins with "." then invoke this routine to
 ** process that line.
 **
 ** Return 1 on error, 2 to exit, and 0 otherwise.
 */
 static int do_meta_command(char *zLine, struct callback_data *p){
   int i = 1;
   sqlite3_stmt *pSelect;
   int totalRecords;
   int prevPercent;
   int size;
   int percent = 100;
   int rc = 0;
   UNUSED_PARAMETER(zLine);
 
     open_db(p);
     /* When playing back a "dump", the content might appear in an order
     ** which causes immediate foreign key constraints to be violated.
     ** So disable foreign-key constraint enforcement to prevent problems. */
     fprintf(p->out, "PRAGMA foreign_keys=OFF;\n");
     fprintf(p->out, "BEGIN TRANSACTION;\n");
     p->writableSchema = 0;
     sqlite3_exec(p->db, "SAVEPOINT dump; PRAGMA writable_schema=ON", 0, 0, 0);
     p->nErr = 0;
 
   /* get table sizes */
   rc = sqlite3_prepare(p->db,
         "SELECT name FROM sqlite_master "
         "WHERE sql NOT NULL AND type=='table' AND name!='sqlite_sequence'", -1, &pSelect, 0);
   if( rc!=SQLITE_OK || !pSelect ){
     fprintf(p->out, "/**** ERROR: (%d) %s *****/\n", rc, sqlite3_errmsg(p->db));
     return rc;
   }
   rc = sqlite3_step(pSelect);
   totalRecords = 0;
   for(i=0; rc==SQLITE_ROW; i++){
     int size = table_size(p, sqlite3_column_text(pSelect, 0));
     if (size < 0) {
       sqlite3_finalize(pSelect);
       return 1;
     }
     totalRecords += size;
     rc = sqlite3_step(pSelect);
   }
   /*fprintf(stderr, "totalRecords=%d\n", totalRecords);*/
   rc = sqlite3_finalize(pSelect);
   if( rc!=SQLITE_OK ){
     fprintf(p->out, "/**** ERROR: (%d) %s *****/\n", rc, sqlite3_errmsg(p->db));
     return 1;
   }
 
   /* dump tables */
   rc = sqlite3_prepare(p->db,
         "SELECT name FROM sqlite_master "
         "WHERE sql NOT NULL AND type=='table' AND name!='sqlite_sequence'", -1, &pSelect, 0);
   if( rc!=SQLITE_OK || !pSelect ){
     fprintf(p->out, "/**** ERROR: (%d) %s *****/\n", rc, sqlite3_errmsg(p->db));
     return rc;
   }
   rc = sqlite3_step(pSelect);
   prevPercent = -1;
   for(i=0; rc==SQLITE_ROW;){
     zShellStatic = (const char*)sqlite3_column_text(pSelect, 0);
     run_schema_dump_query(p,
       "SELECT name, type, sql FROM sqlite_master "
       "WHERE tbl_name LIKE shellstatic() AND type=='table'"
       "  AND sql NOT NULL");
     run_table_dump_query(p,
       "SELECT sql FROM sqlite_master "
       "WHERE sql NOT NULL"
       "  AND type IN ('index','trigger','view')"
       "  AND tbl_name LIKE shellstatic()", 0
     );
 
     size = table_size(p, sqlite3_column_text(pSelect, 0));
     if (size < 0) {
       sqlite3_finalize(pSelect);
       return 1;
     }
     if (totalRecords > 0) {
         percent = 100 * i / totalRecords;
     }
     if(prevPercent < percent){
       fprintf(stderr, "DUMP: %d%%\n", percent);
       prevPercent = percent;
     }
     i += size;
     rc = sqlite3_step(pSelect);
   }
   zShellStatic = 0;
   rc = sqlite3_finalize(pSelect);
   if( rc!=SQLITE_OK ){
     fprintf(p->out, "/**** ERROR: (%d) %s *****/\n", rc, sqlite3_errmsg(p->db));
     return 1;
   }
 
   run_schema_dump_query(p,
     "SELECT name, type, sql FROM sqlite_master "
     "WHERE name=='sqlite_sequence'"
   );
   run_table_dump_query(p,
     "SELECT sql FROM sqlite_master "
     "WHERE sql NOT NULL AND type IN ('index','trigger','view')", 0
   );
   if( p->writableSchema ){
     fprintf(p->out, "PRAGMA writable_schema=OFF;\n");
     p->writableSchema = 0;
   }
   sqlite3_exec(p->db, "PRAGMA writable_schema=OFF;", 0, 0, 0);
   sqlite3_exec(p->db, "RELEASE dump;", 0, 0, 0);
   fprintf(p->out, p->nErr ? "ROLLBACK; -- due to errors\n" : "COMMIT;\n");
 
   return rc;
 }
 
 /*
 ** Initialize the state information in data
 */
 static void main_init(struct callback_data *data) {
   memset(data, 0, sizeof(*data));
   data->mode = MODE_List;
   memcpy(data->separator,"|", 2);
   data->showHeader = 0;
 #ifdef SQLITE_CONFIG_URI
   sqlite3_config(SQLITE_CONFIG_URI, 1);
 #endif
 #ifdef SQLITE_CONFIG_LOG
   sqlite3_config(SQLITE_CONFIG_LOG, shellLog, data);
 #endif
   sqlite3_config(SQLITE_CONFIG_SINGLETHREAD);
 }
 
 int shell_main(const char *inFilename) {
   struct callback_data data;
   int rc = 0;
 
   main_init(&data);
   data.zDbFilename = inFilename;
 
   /* Go ahead and open the database file if it already exists.  If the
   ** file does not exist, delay opening it.  This prevents empty database
   ** files from being created if a user mistypes the database name argument
   ** to the sqlite command-line tool.
   */
   if( access(data.zDbFilename, 0)==0 ){
     open_db(&data);
   }
 
   data.out = stdout;
   rc = do_meta_command(0, &data);
 
   if( data.db ){
     sqlite3_close(data.db);
   }
   return rc;
 }