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: |
8dcc1d89d955bf58c80a8c30a37960f0 |
| 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
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