/ Check-in [8dcc1d89]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Fixes for the ".recover" shell command.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | dbdata
Files: files | file ages | folders
SHA3-256: 8dcc1d89d955bf58c80a8c30a37960f0cf95719953951a92626cc332cc75ec60
User & Date: dan 2019-04-23 18:03:02
Context
2019-04-23
20:48
Have ".recover" handle "\r" and "\n" in the same way as ".dump". check-in: f95f0f02 user: dan tags: dbdata
18:03
Fixes for the ".recover" shell command. check-in: 8dcc1d89 user: dan tags: dbdata
2019-04-22
20:52
Enhance the ".recover" command. Fix a problem with overflow pages in dbdata.c. check-in: f193ca58 user: dan tags: dbdata
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/shell.c.in.

  6109   6109   
  6110   6110     if( rc==SQLITE_OK ){
  6111   6111       int nSqlCol = 0;
  6112   6112       int bSqlIntkey = 0;
  6113   6113       sqlite3_stmt *pStmt = 0;
  6114   6114   
  6115   6115       rc = sqlite3_open("", &dbtmp);
         6116  +    if( rc==SQLITE_OK ){
         6117  +      rc = sqlite3_exec(dbtmp, "PRAGMA writable_schema = on", 0, 0, 0);
         6118  +    }
  6116   6119       if( rc==SQLITE_OK ){
  6117   6120         rc = sqlite3_exec(dbtmp, zSql, 0, 0, 0);
  6118   6121         if( rc==SQLITE_ERROR ){
  6119   6122           rc = SQLITE_OK;
  6120   6123           goto finished;
  6121   6124         }
  6122   6125       }
................................................................................
  6357   6360   #endif
  6358   6361   
  6359   6362     /* Open a transaction, then print out all non-virtual, non-"sqlite_%" 
  6360   6363     ** CREATE TABLE statements that extracted from the existing schema.  */
  6361   6364     if( rc==SQLITE_OK ){
  6362   6365       sqlite3_stmt *pStmt = 0;
  6363   6366       raw_printf(pState->out, "BEGIN;\n");
  6364         -    shellPrepare(pState->db, &rc, 
         6367  +    raw_printf(pState->out, "PRAGMA writable_schema = on;\n");
         6368  +    shellPrepare(pState->db, &rc,
  6365   6369           "SELECT sql FROM recovery.schema "
  6366         -        "WHERE type='table' "
  6367         -        "  AND length(sql)>6"
  6368         -        "  AND sql LIKE 'create table%'"
  6369         -        "  AND name NOT LIKE 'sqliteX_%' ESCAPE 'X'", &pStmt
         6370  +        "WHERE type='table' AND sql LIKE 'create table%'", &pStmt
  6370   6371       );
  6371   6372       while( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pStmt) ){
  6372   6373         const char *zCreateTable = (const char*)sqlite3_column_text(pStmt, 0);
  6373         -      raw_printf(pState->out, "%s;\n", zCreateTable);
         6374  +      raw_printf(pState->out, "CREATE TABLE IF NOT EXISTS %s;\n", 
         6375  +          &zCreateTable[12]
         6376  +      );
  6374   6377       }
  6375   6378       shellFinalize(&rc, pStmt);
  6376   6379     }
  6377   6380   
  6378   6381     /* Loop through each root page. */
  6379   6382     shellPrepare(pState->db, &rc, 
  6380   6383         "SELECT root, intkey, max(maxlen) FROM recovery.map" 
  6381         -      " WHERE root>1 GROUP BY root, intkey", &pLoop
         6384  +      " WHERE root>1 GROUP BY root, intkey ORDER BY root=("
         6385  +      "  SELECT rootpage FROM recovery.schema WHERE name='sqlite_sequence'"
         6386  +      ")", &pLoop
  6382   6387     );
  6383   6388     while( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pLoop) ){
  6384   6389       int iRoot = sqlite3_column_int(pLoop, 0);
  6385   6390       int bIntkey = sqlite3_column_int(pLoop, 1);
  6386   6391       int nCol = sqlite3_column_int(pLoop, 2);
  6387   6392       RecoverTable *pTab;
  6388   6393   
  6389   6394       pTab = recoverNewTable(pState, &rc, iRoot, bIntkey, nCol);
  6390   6395       if( pTab ){
  6391   6396         sqlite3_stmt *pData = 0;
         6397  +      if( 0==sqlite3_stricmp(pTab->zName, "sqlite_sequence") ){
         6398  +        raw_printf(pState->out, "DELETE FROM sqlite_sequence;\n");
         6399  +      }
  6392   6400         shellPreparePrintf(pState->db, &rc, &pData, 
  6393   6401           "SELECT max(field), group_concat(quote(value), ', ') "
  6394   6402           "FROM sqlite_dbdata WHERE pgno IN ("
  6395   6403           "  SELECT pgno FROM recovery.map WHERE root=%d"
  6396   6404           ")"
  6397   6405           " AND field!=%d "
  6398   6406           "GROUP BY pgno, cell;", iRoot, pTab->iPk
................................................................................
  6405   6413           );
  6406   6414         }
  6407   6415         shellFinalize(&rc, pData);
  6408   6416       }
  6409   6417       recoverFreeTable(pTab);
  6410   6418     }
  6411   6419     shellFinalize(&rc, pLoop);
         6420  +
         6421  +  /* The rest of the schema */
         6422  +  if( rc==SQLITE_OK ){
         6423  +    sqlite3_stmt *pStmt = 0;
         6424  +    shellPrepare(pState->db, &rc, 
         6425  +        "SELECT sql, name FROM recovery.schema "
         6426  +        "WHERE (type='table' AND sql LIKE 'create table%') IS NOT TRUE", &pStmt
         6427  +    );
         6428  +    while( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pStmt) ){
         6429  +      const char *zSql = (const char*)sqlite3_column_text(pStmt, 0);
         6430  +      if( sqlite3_strnicmp(zSql, "create virt", 11)==0 ){
         6431  +        const char *zName = (const char*)sqlite3_column_text(pStmt, 1);
         6432  +        char *zPrint = shellMPrintf(&rc, 
         6433  +          "INSERT INTO sqlite_master VALUES('table', %Q, %Q, 0, %Q)",
         6434  +          zName, zName, zSql
         6435  +        );
         6436  +        raw_printf(pState->out, "%s;\n", zPrint);
         6437  +        sqlite3_free(zPrint);
         6438  +      }else{
         6439  +        raw_printf(pState->out, "%s;\n", zSql);
         6440  +      }
         6441  +    }
         6442  +    shellFinalize(&rc, pStmt);
         6443  +  }
  6412   6444   
  6413   6445     if( rc==SQLITE_OK ){
         6446  +    raw_printf(pState->out, "PRAGMA writable_schema = off;\n");
  6414   6447       raw_printf(pState->out, "COMMIT;\n");
  6415   6448     }
  6416   6449     sqlite3_exec(pState->db, "DETACH recovery", 0, 0, 0);
  6417   6450     return rc;
  6418   6451   }
  6419   6452   
  6420   6453   

Added test/recover.test.

            1  +# 2019 April 23
            2  +#
            3  +# The author disclaims copyright to this source code.  In place of
            4  +# a legal notice, here is a blessing:
            5  +#
            6  +#    May you do good and not evil.
            7  +#    May you find forgiveness for yourself and forgive others.
            8  +#    May you share freely, never taking more than you give.
            9  +#
           10  +#***********************************************************************
           11  +#
           12  +# Test the shell tool ".ar" command.
           13  +#
           14  +
           15  +set testdir [file dirname $argv0]
           16  +source $testdir/tester.tcl
           17  +set testprefix recover
           18  +
           19  +ifcapable !vtab {
           20  +  finish_test; return
           21  +}
           22  +set CLI [test_find_cli]
           23  +
           24  +proc compare_result {db1 db2 sql} {
           25  +  set r1 [$db1 eval $sql]
           26  +  set r2 [$db2 eval $sql]
           27  +  if {$r1 != $r2} {
           28  +  puts "r1: $r1"
           29  +  puts "r2: $r2"
           30  +    error "mismatch for $sql"
           31  +  }
           32  +  return ""
           33  +}
           34  +
           35  +proc compare_dbs {db1 db2} {
           36  +  compare_result $db1 $db2 "SELECT sql FROM sqlite_master ORDER BY 1"
           37  +  foreach tbl [$db1 eval {SELECT name FROM sqlite_master WHERE type='table'}] {
           38  +    compare_result $db1 $db2 "SELECT * FROM $tbl"
           39  +  }
           40  +}
           41  +
           42  +proc do_recover_test {tn} {
           43  +  set fd [open "|$::CLI test.db .recover"]
           44  +  fconfigure $fd -encoding binary
           45  +  fconfigure $fd -translation binary
           46  +  set sql [read $fd]
           47  +  close $fd
           48  +
           49  +  forcedelete test.db2
           50  +  sqlite3 db2 test.db2
           51  +  breakpoint
           52  +  execsql $sql db2
           53  +  uplevel [list do_test $tn [list compare_dbs db db2] {}]
           54  +  db2 close
           55  +}
           56  +
           57  +set doc {
           58  +  hello
           59  +  world
           60  +}
           61  +do_execsql_test 1.1.1 {
           62  +  CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c);
           63  +  INSERT INTO t1 VALUES(1, 4, X'1234567800');
           64  +  INSERT INTO t1 VALUES(2, 'test', 8.1);
           65  +  INSERT INTO t1 VALUES(3, $doc, 8.4);
           66  +}
           67  +do_recover_test 1.1.2
           68  +
           69  +do_execsql_test 1.2.1 "
           70  +  DELETE FROM t1;
           71  +  INSERT INTO t1 VALUES(13, 'hello\r\nworld', 13);
           72  +"
           73  +do_recover_test 1.2.2
           74  +
           75  +do_execsql_test 1.3.1 "
           76  +  CREATE TABLE t2(i INTEGER PRIMARY KEY AUTOINCREMENT, b, c);
           77  +  INSERT INTO t2 VALUES(NULL, 1, 2);
           78  +  INSERT INTO t2 VALUES(NULL, 3, 4);
           79  +  INSERT INTO t2 VALUES(NULL, 5, 6);
           80  +  CREATE TABLE t3(i INTEGER PRIMARY KEY AUTOINCREMENT, b, c);
           81  +  INSERT INTO t3 VALUES(NULL, 1, 2);
           82  +  INSERT INTO t3 VALUES(NULL, 3, 4);
           83  +  INSERT INTO t3 VALUES(NULL, 5, 6);
           84  +  DELETE FROM t2;
           85  +"
           86  +do_recover_test 1.3.2
           87  +
           88  +finish_test