sqlite3 fix by problem such as “disk image is malformed” and “database is locked”; fix logical from sqlite3.7.9 shell.c

sqlite3_recover.c

#include <stdlib.h>
#include <string.h>
#include <stdio.h>
#include <assert.h>
#include "sqlite3.h"
#include <ctype.h>
#include <stdarg.h>
#include <time.h>
#include "sqlite3_recover.h"


/* ctype macros that work with signed characters */
#define IsSpace(X)  isspace((unsigned char)X)
#define IsDigit(X)  isdigit((unsigned char)X)
#define ToLower(X)  (char)tolower((unsigned char)X)

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 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 */
  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 */
  const char *zVfs;           /* Name of VFS to use */
  sqlite3_stmt *pStmt;   /* Current statement if any. */
  FILE *pLog;            /* Write log output here */
};

/*
 ** 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);
}

static int integrity_check_callback(void *pArg, int nArg, char **azArg, char **azCol);
static void open_db(struct callback_data *p);

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

/*
 ** 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;
  
  if( zPrompt && *zPrompt ){
    printf("%s",zPrompt);
    fflush(stdout);
  }
  nLine = 100;
  zLine = malloc( nLine );
  if( zLine==0 ) return 0;
  n = 0;
  while( 1 ){
    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;
      break;
    }
    while( zLine[n] ){ n++; }
    if( n>0 && zLine[n-1]=='\n' ){
      n--;
      if( n>0 && zLine[n-1]=='\r' ) n--;
      zLine[n] = 0;
      break;
    }
  }
  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){
  return local_getline(0, in);
}

/*
 ** Return TRUE if a semicolon occurs anywhere in the first N characters
 ** of string z[].
 */
static int _contains_semicolon(const char *z, int N){
  int i;
  for(i=0; i<N; i++){  if( z[i]==';' ) return 1; }
  return 0;
}

/*
 ** Test to see if a line consists entirely of whitespace.
 */
static int _all_whitespace(const char *z){
  for(; *z; z++){
    if( IsSpace(z[0]) ) continue;
    if( *z=='/' && z[1]=='*' ){
      z += 2;
      while( *z && (*z!='*' || z[1]!='/') ){ z++; }
      if( *z==0 ) return 0;
      z++;
      continue;
    }
    if( *z=='-' && z[1]=='-' ){
      z += 2;
      while( *z && *z!='\n' ){ z++; }
      if( *z==0 ) return 1;
      continue;
    }
    return 0;
  }
  return 1;
}

/*
 ** Return TRUE if the line typed in is an SQL command terminator other
 ** than a semi-colon.  The SQL Server style "go" command is understood
 ** as is the Oracle "/".
 */
static int _is_command_terminator(const char *zLine){
  while( IsSpace(zLine[0]) ){ zLine++; };
  if( zLine[0]=='/' && _all_whitespace(&zLine[1]) ){
    return 1;  /* Oracle */
  }
  if( ToLower(zLine[0])=='g' && ToLower(zLine[1])=='o'
     && _all_whitespace(&zLine[2]) ){
    return 1;  /* SQL Server */
  }
  return 0;
}

/*
 ** Return true if zSql is a complete SQL statement.  Return false if it
 ** ends in the middle of a string literal or C-style comment.
 */
static int _is_complete(char *zSql, int nSql){
  int rc;
  if( zSql==0 ) return 1;
  zSql[nSql] = ';';
  zSql[nSql+1] = 0;
  rc = sqlite3_complete(zSql);
  zSql[nSql] = 0;
  return rc;
}

/*
 ** Read input from *in and process it.  If *in==0 then input
 ** is interactive - the user is typing it it.  Otherwise, input
 ** is coming from a file or device.  A prompt is issued and history
 ** is saved only if input is interactive.  An interrupt signal will
 ** cause this routine to exit immediately, unless input is interactive.
 **
 ** Return the number of errors.
 */
static int process_input(struct callback_data *p, FILE *in){
  char *zLine = 0;
  char *zSql = 0;
  int nSql = 0;
  int nSqlPrior = 0;
  int errCnt = 0;
  int lineno = 0;
  int startline = 0;
  int bail_on_error = 0;
  
  while( errCnt==0 || !bail_on_error ){
    fflush(p->out);
    free(zLine);
    zLine = one_input_line(zSql, in);
    if( zLine==0 ){
      break;  /* We have reached EOF */
    }
    
    lineno++;
    if( (zSql==0 || zSql[0]==0) && _all_whitespace(zLine) ) continue;
    if( _is_command_terminator(zLine) && _is_complete(zSql, nSql) ){
      memcpy(zLine,";",2);
    }
    nSqlPrior = nSql;
    if( zSql==0 ){
      int i;
      for(i=0; zLine[i] && IsSpace(zLine[i]); i++){}
      if( zLine[i]!=0 ){
        nSql = strlen30(zLine);
        zSql = malloc( nSql+3 );
        if( zSql==0 ){
          fprintf(stderr, "Error: out of memory\n");
          exit(1);
        }
        memcpy(zSql, zLine, nSql+1);
        startline = lineno;
      }
    }else{
      int len = strlen30(zLine);
      zSql = realloc( zSql, nSql + len + 4 );
      if( zSql==0 ){
        fprintf(stderr,"Error: out of memory\n");
        exit(1);
      }
      zSql[nSql++] = '\n';
      memcpy(&zSql[nSql], zLine, len+1);
      nSql += len;
    }
    
    if( zSql && _contains_semicolon(&zSql[nSqlPrior], nSql-nSqlPrior)
       && sqlite3_complete(zSql) ){
      p->cnt = 0;
      open_db(p);
      int hasError = 0;
      char *zErr = 0;
      sqlite3_exec(p->db, zSql, integrity_check_callback, &hasError, &zErr);
      free(zSql);
      zSql = 0;
      nSql = 0;
    }
    
    
  }
  if( zSql ){
    if( !_all_whitespace(zSql) ){
      fprintf(stderr, "Error: incomplete SQL: %s\n", zSql);
    }
    free(zSql);
  }
  free(zLine);
  return errCnt;
}

/*
 ** 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;
  
  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, '\'');
    }
    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);
    }
    if( zSelect ) 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(sizeof(zQ2), 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){
  sqlite3 *db;
  if( p->db==0 ){
    sqlite3_open(p->zDbFilename, &p->db);
    db = p->db;
    if( db && sqlite3_errcode(db)==SQLITE_OK ){
      fprintf(stdout, "Info: open database success\n");
    }
    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);
    }
  }
}

/*
 ** Initialize the state information in data
 */
static void main_init(struct callback_data *data) {
  memset(data, 0, sizeof(*data));
  data->mode = 2;
  memcpy(data->separator,"|", 2);
  data->showHeader = 0;
  sqlite3_config(SQLITE_CONFIG_URI, 1);
  sqlite3_config(SQLITE_CONFIG_LOG, shellLog, data);
  sqlite3_config(SQLITE_CONFIG_SINGLETHREAD);
}

static int dump(struct callback_data *p) {
  
  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;
  run_schema_dump_query(p,
                        "SELECT name, type, sql FROM sqlite_master "
                        "WHERE sql NOT NULL AND type=='table' AND name!='sqlite_sequence'"
                        );
  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");
  if (p->nErr) {
    fprintf(p->out, "--ROLLBACK; -- due to errors\nCOMMIT;\n");
  }
  else {
    fprintf(p->out, "COMMIT;\n");
  }
  
  return 0;
}

//int foo(void) {
//  struct callback_data data;
//  main_init(&data);
//
//  const char* file_path = "x/sqlite_recover/dump.sql";
//  FILE *dumpFile = fopen(file_path, "w");
//  data.out = dumpFile;
//  //  data.out = stdout;
//  data.zDbFilename = "x/sqlite_recover/old.db";
//
//  dump(&data);
//
//  fclose(dumpFile);
//
//  sqlite3_close_v2(data.db);
//  return 0;
//}
//
//int bar(void) {
//  //  struct callback_data data;
//  //  main_init(&data);
//  //
//  //  data.out = stdout;
//  //  data.zDbFilename = "x/sqlite_recover/restored_from_dump.sqlite3";
//  //  open_db(&data);
//  //
//  //  const char* dumped_file_path = "x/sqlite_recover/dump.sql";
//  //  FILE *alt = fopen(dumped_file_path, "rb");
//  //  process_input(&data, alt);
//  //
//  //  fclose(alt);
//  //  sqlite3_close_v2(data.db);
//
//  return 0;
//}

static int integrity_check_callback(void *pArg, int nArg, char **azArg, char **azCol){
  int *p = (int *)pArg;
  int result = strcmp(*azArg, "ok");
  if (result == 0) {
    return 0;
  }
  *p = 1;
  return 0;
}

int database_repair(const char* database_file_path) {
  struct callback_data data;
  main_init(&data);
  // 打开当前数据库
  data.zDbFilename = database_file_path;
  open_db(&data);
  // 进行完整性检测
  int hasError = 0;
  char *zErr = 0;
  sqlite3_exec(data.db, "PRAGMA INTEGRITY_CHECK", integrity_check_callback, &hasError, &zErr);
  if (hasError != 0) {
    // 进行恢复操作
    
    // 1. 创建一个临时文件用于存储导出的SQL数据
    char *database_backup_sql_file_path = calloc(strlen(database_file_path) + strlen(".sql") + 1, sizeof(char));
    strcat(database_backup_sql_file_path, database_file_path);
    strcat(database_backup_sql_file_path, ".sql");
    
    // 打开导出文件
    FILE *dumpFile = fopen(database_backup_sql_file_path, "w");
    data.out = dumpFile;
    // 执行导出操作
    dump(&data);
    // 关闭导出文件
    fclose(dumpFile);
    
    // 关闭数据库文件
    sqlite3_close_v2(data.db);
    
    // 2. 重建数据库（恢复）
    // 重命名当前数据库文件，以备不时之需
    // todo:
    int timestamp = (int)time(NULL);
    int timestamp_length = snprintf( NULL, 0, "%d", timestamp );
    char* timestamp_string = calloc( timestamp_length + 1, sizeof(char) );
    snprintf( timestamp_string, timestamp_length + 1, "%d", timestamp );
    
    // 备份数据库文件名
    char *backuped_file_path = calloc(strlen(database_file_path) + timestamp_length + 1, sizeof(char));
    strcat(backuped_file_path, database_backup_sql_file_path);
    strcat(backuped_file_path, timestamp_string);
    
    // 重命名原文件
    rename(database_file_path, backuped_file_path);
    free(backuped_file_path);
    
    // 使用原数据库名来重建数据库
    main_init(&data);
    // 打开当前数据库
    data.zDbFilename = database_file_path;
    open_db(&data);
    FILE *alt = fopen(database_backup_sql_file_path, "rb");
    process_input(&data, alt);
    fclose(alt);
    sqlite3_close(data.db);
    
    // 操作完成
    free(timestamp_string);
    free(database_backup_sql_file_path);
    
    return 0;
  }
  
  return SQLITE_OK;
}