SQLite

Check-in [8dcc1d89d9]
Login

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

Overview
Comment:Fixes for the ".recover" shell command.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | dbdata
Files: files | file ages | folders
SHA3-256: 8dcc1d89d955bf58c80a8c30a37960f0cf95719953951a92626cc332cc75ec60
User & Date: dan 2019-04-23 18:03:02.318
Context
2019-04-23
20:48
Have ".recover" handle "\r" and "\n" in the same way as ".dump". (check-in: f95f0f02ab user: dan tags: dbdata)
18:03
Fixes for the ".recover" shell command. (check-in: 8dcc1d89d9 user: dan tags: dbdata)
2019-04-22
20:52
Enhance the ".recover" command. Fix a problem with overflow pages in dbdata.c. (check-in: f193ca587f user: dan tags: dbdata)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/shell.c.in.
6109
6110
6111
6112
6113
6114
6115



6116
6117
6118
6119
6120
6121
6122

  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, zSql, 0, 0, 0);
      if( rc==SQLITE_ERROR ){
        rc = SQLITE_OK;
        goto finished;
      }
    }







>
>
>







6109
6110
6111
6112
6113
6114
6115
6116
6117
6118
6119
6120
6121
6122
6123
6124
6125

  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;
      }
    }
6357
6358
6359
6360
6361
6362
6363

6364
6365
6366
6367
6368
6369
6370
6371
6372
6373


6374
6375
6376
6377
6378
6379
6380
6381


6382
6383
6384
6385
6386
6387
6388
6389
6390
6391



6392
6393
6394
6395
6396
6397
6398
6399
6400
6401
6402
6403
6404
6405
6406
6407
6408
6409
6410
6411
6412

6413
























6414
6415
6416
6417
6418
6419
6420
#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");

    shellPrepare(pState->db, &rc, 
        "SELECT sql FROM recovery.schema "
        "WHERE type='table' "
        "  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);


    }
    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


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



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


  if( rc==SQLITE_OK ){
























    raw_printf(pState->out, "COMMIT;\n");
  }
  sqlite3_exec(pState->db, "DETACH recovery", 0, 0, 0);
  return rc;
}









>
|

|
<
<
<



|
>
>







|
>
>










>
>
>





















>

>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







6360
6361
6362
6363
6364
6365
6366
6367
6368
6369
6370



6371
6372
6373
6374
6375
6376
6377
6378
6379
6380
6381
6382
6383
6384
6385
6386
6387
6388
6389
6390
6391
6392
6393
6394
6395
6396
6397
6398
6399
6400
6401
6402
6403
6404
6405
6406
6407
6408
6409
6410
6411
6412
6413
6414
6415
6416
6417
6418
6419
6420
6421
6422
6423
6424
6425
6426
6427
6428
6429
6430
6431
6432
6433
6434
6435
6436
6437
6438
6439
6440
6441
6442
6443
6444
6445
6446
6447
6448
6449
6450
6451
6452
6453
#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,
        "SELECT sql FROM recovery.schema "
        "WHERE type='table' AND sql LIKE 'create table%'", &pStmt



    );
    while( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pStmt) ){
      const char *zCreateTable = (const char*)sqlite3_column_text(pStmt, 0);
      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 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;
}


Added test/recover.test.
















































































































































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
# 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