SQLite

  if( rc==SQLITE_OK ){
    int nSqlCol = 0;
    int bSqlIntkey = 0;
    sqlite3_stmt *pStmt = 0;

    rc = sqlite3_open("", &dbtmp);
    if( rc==SQLITE_OK ){
      rc = sqlite3_exec(dbtmp, "PRAGMA writable_schema = on", 0, 0, 0);
    }
    if( rc==SQLITE_OK ){
      rc = sqlite3_exec(dbtmp, zSql, 0, 0, 0);
      if( rc==SQLITE_ERROR ){
        rc = SQLITE_OK;
        goto finished;
      }
    }

#endif

  /* Open a transaction, then print out all non-virtual, non-"sqlite_%" 
  ** CREATE TABLE statements that extracted from the existing schema.  */
  if( rc==SQLITE_OK ){
    sqlite3_stmt *pStmt = 0;
    raw_printf(pState->out, "BEGIN;\n");
    raw_printf(pState->out, "PRAGMA writable_schema = on;\n");
    shellPrepare(pState->db, &rc, 
    shellPrepare(pState->db, &rc,
        "SELECT sql FROM recovery.schema "
        "WHERE type='table' "
        "WHERE type='table' AND sql LIKE 'create table%'", &pStmt
        "  AND length(sql)>6"
        "  AND sql LIKE 'create table%'"
        "  AND name NOT LIKE 'sqliteX_%' ESCAPE 'X'", &pStmt
    );
    while( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pStmt) ){
      const char *zCreateTable = (const char*)sqlite3_column_text(pStmt, 0);
      raw_printf(pState->out, "%s;\n", zCreateTable);
      raw_printf(pState->out, "CREATE TABLE IF NOT EXISTS %s;\n", 
          &zCreateTable[12]
      );
    }
    shellFinalize(&rc, pStmt);
  }

  /* Loop through each root page. */
  shellPrepare(pState->db, &rc, 
      "SELECT root, intkey, max(maxlen) FROM recovery.map" 
      " WHERE root>1 GROUP BY root, intkey", &pLoop
      " WHERE root>1 GROUP BY root, intkey ORDER BY root=("
      "  SELECT rootpage FROM recovery.schema WHERE name='sqlite_sequence'"
      ")", &pLoop
  );
  while( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pLoop) ){
    int iRoot = sqlite3_column_int(pLoop, 0);
    int bIntkey = sqlite3_column_int(pLoop, 1);
    int nCol = sqlite3_column_int(pLoop, 2);
    RecoverTable *pTab;

    pTab = recoverNewTable(pState, &rc, iRoot, bIntkey, nCol);
    if( pTab ){
      sqlite3_stmt *pData = 0;
      if( 0==sqlite3_stricmp(pTab->zName, "sqlite_sequence") ){
        raw_printf(pState->out, "DELETE FROM sqlite_sequence;\n");
      }
      shellPreparePrintf(pState->db, &rc, &pData, 
        "SELECT max(field), group_concat(quote(value), ', ') "
        "FROM sqlite_dbdata WHERE pgno IN ("
        "  SELECT pgno FROM recovery.map WHERE root=%d"
        ")"
        " AND field!=%d "
        "GROUP BY pgno, cell;", iRoot, pTab->iPk
      );
      while( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pData) ){
        int iMax = sqlite3_column_int(pData, 0);
        const char *zVal = (const char*)sqlite3_column_text(pData, 1);
        raw_printf(pState->out, "INSERT INTO %s(%s) VALUES( %s );\n", 
            pTab->zQuoted, pTab->azlCol[iMax>0?iMax:0], zVal
        );
      }
      shellFinalize(&rc, pData);
    }
    recoverFreeTable(pTab);
  }
  shellFinalize(&rc, pLoop);

  /* The rest of the schema */
  if( rc==SQLITE_OK ){
    sqlite3_stmt *pStmt = 0;
    shellPrepare(pState->db, &rc, 
        "SELECT sql, name FROM recovery.schema "
        "WHERE (type='table' AND sql LIKE 'create table%') IS NOT TRUE", &pStmt
    );
    while( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pStmt) ){
      const char *zSql = (const char*)sqlite3_column_text(pStmt, 0);
      if( sqlite3_strnicmp(zSql, "create virt", 11)==0 ){
        const char *zName = (const char*)sqlite3_column_text(pStmt, 1);
        char *zPrint = shellMPrintf(&rc, 
          "INSERT INTO sqlite_master VALUES('table', %Q, %Q, 0, %Q)",
          zName, zName, zSql
        );
        raw_printf(pState->out, "%s;\n", zPrint);
        sqlite3_free(zPrint);
      }else{
        raw_printf(pState->out, "%s;\n", zSql);
      }
    }
    shellFinalize(&rc, pStmt);
  }

  if( rc==SQLITE_OK ){
    raw_printf(pState->out, "PRAGMA writable_schema = off;\n");
    raw_printf(pState->out, "COMMIT;\n");
  }
  sqlite3_exec(pState->db, "DETACH recovery", 0, 0, 0);
  return rc;
}

# 2019 April 23
#
# 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.
#
#***********************************************************************
#
# Test the shell tool ".ar" command.
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl
set testprefix recover

ifcapable !vtab {
  finish_test; return
}
set CLI [test_find_cli]

proc compare_result {db1 db2 sql} {
  set r1 [$db1 eval $sql]
  set r2 [$db2 eval $sql]
  if {$r1 != $r2} {
  puts "r1: $r1"
  puts "r2: $r2"
    error "mismatch for $sql"
  }
  return ""
}

proc compare_dbs {db1 db2} {
  compare_result $db1 $db2 "SELECT sql FROM sqlite_master ORDER BY 1"
  foreach tbl [$db1 eval {SELECT name FROM sqlite_master WHERE type='table'}] {
    compare_result $db1 $db2 "SELECT * FROM $tbl"
  }
}

proc do_recover_test {tn} {
  set fd [open "|$::CLI test.db .recover"]
  fconfigure $fd -encoding binary
  fconfigure $fd -translation binary
  set sql [read $fd]
  close $fd

  forcedelete test.db2
  sqlite3 db2 test.db2
  breakpoint
  execsql $sql db2
  uplevel [list do_test $tn [list compare_dbs db db2] {}]
  db2 close
}

set doc {
  hello
  world
}
do_execsql_test 1.1.1 {
  CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c);
  INSERT INTO t1 VALUES(1, 4, X'1234567800');
  INSERT INTO t1 VALUES(2, 'test', 8.1);
  INSERT INTO t1 VALUES(3, $doc, 8.4);
}
do_recover_test 1.1.2

do_execsql_test 1.2.1 "
  DELETE FROM t1;
  INSERT INTO t1 VALUES(13, 'hello\r\nworld', 13);
"
do_recover_test 1.2.2

do_execsql_test 1.3.1 "
  CREATE TABLE t2(i INTEGER PRIMARY KEY AUTOINCREMENT, b, c);
  INSERT INTO t2 VALUES(NULL, 1, 2);
  INSERT INTO t2 VALUES(NULL, 3, 4);
  INSERT INTO t2 VALUES(NULL, 5, 6);
  CREATE TABLE t3(i INTEGER PRIMARY KEY AUTOINCREMENT, b, c);
  INSERT INTO t3 VALUES(NULL, 1, 2);
  INSERT INTO t3 VALUES(NULL, 3, 4);
  INSERT INTO t3 VALUES(NULL, 5, 6);
  DELETE FROM t2;
"
do_recover_test 1.3.2

finish_test