Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Add the "--lost-and-found" option to the ".recover" command. For setting the name of the orphaned rows table. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | dbdata |
| Files: | files | file ages | folders |
| SHA3-256: |
67bb88e24c74d02ae0c4ac6ff2f873f6 |
| User & Date: | dan 2019-04-27 18:47:03 |
Context
|
2019-04-27
| ||
| 19:36 | Add comments and fix formatting issues in new code in shell.c.in. (check-in: b91d819b user: dan tags: dbdata) | |
| 18:47 | Add the "--lost-and-found" option to the ".recover" command. For setting the name of the orphaned rows table. (check-in: 67bb88e2 user: dan tags: dbdata) | |
| 15:35 | Fix a problem in the .recover command with recovering WITHOUT ROWID tables where the PK columns are not the leftmost in the CREATE TABLE statement. (check-in: 91df4b8e user: dan tags: dbdata) | |
Changes
Changes to ext/misc/dbdata.c.
6 6 ** 7 7 ** May you do good and not evil. 8 8 ** May you find forgiveness for yourself and forgive others. 9 9 ** May you share freely, never taking more than you give. 10 10 ** 11 11 ****************************************************************************** 12 12 ** 13 -** This file contains an implementation of the eponymous "sqlite_dbdata" 14 -** virtual table. sqlite_dbdata is used to extract data directly from a 15 -** database b-tree page and its associated overflow pages, bypassing the b-tree 16 -** layer. The table schema is equivalent to: 13 +** This file contains an implementation of two eponymous virtual tables, 14 +** "sqlite_dbdata" and "sqlite_dbptr". Both modules require that the 15 +** "sqlite_dbpage" eponymous virtual table be available. 16 +** 17 +** SQLITE_DBDATA: 18 +** sqlite_dbdata is used to extract data directly from a database b-tree 19 +** page and its associated overflow pages, bypassing the b-tree layer. 20 +** The table schema is equivalent to: 17 21 ** 18 22 ** CREATE TABLE sqlite_dbdata( 19 23 ** pgno INTEGER, 20 24 ** cell INTEGER, 21 25 ** field INTEGER, 22 26 ** value ANY, 23 27 ** schema TEXT HIDDEN 24 28 ** ); 25 29 ** 26 -** IMPORTANT: THE VIRTUAL TABLE SCHEMA ABOVE IS SUBJECT TO CHANGE. IN THE 27 -** FUTURE NEW NON-HIDDEN COLUMNS MAY BE ADDED BETWEEN "value" AND "schema". 30 +** IMPORTANT: THE VIRTUAL TABLE SCHEMA ABOVE IS SUBJECT TO CHANGE. IN THE 31 +** FUTURE NEW NON-HIDDEN COLUMNS MAY BE ADDED BETWEEN "value" AND 32 +** "schema". 28 33 ** 29 -** Each page of the database is inspected. If it cannot be interpreted as a 30 -** b-tree page, or if it is a b-tree page containing 0 entries, the 31 -** sqlite_dbdata table contains no rows for that page. Otherwise, the table 32 -** contains one row for each field in the record associated with each 33 -** cell on the page. For intkey b-trees, the key value is stored in field -1. 34 +** Each page of the database is inspected. If it cannot be interpreted as 35 +** a b-tree page, or if it is a b-tree page containing 0 entries, the 36 +** sqlite_dbdata table contains no rows for that page. Otherwise, the 37 +** table contains one row for each field in the record associated with 38 +** each cell on the page. For intkey b-trees, the key value is stored in 39 +** field -1. 34 40 ** 35 -** For example, for the database: 41 +** For example, for the database: 36 42 ** 37 43 ** CREATE TABLE t1(a, b); -- root page is page 2 38 44 ** INSERT INTO t1(rowid, a, b) VALUES(5, 'v', 'five'); 39 45 ** INSERT INTO t1(rowid, a, b) VALUES(10, 'x', 'ten'); 40 46 ** 41 -** the sqlite_dbdata table contains, as well as from entries related to 42 -** page 1, content equivalent to: 47 +** the sqlite_dbdata table contains, as well as from entries related to 48 +** page 1, content equivalent to: 43 49 ** 44 50 ** INSERT INTO sqlite_dbdata(pgno, cell, field, value) VALUES 45 51 ** (2, 0, -1, 5 ), 46 52 ** (2, 0, 0, 'v' ), 47 53 ** (2, 0, 1, 'five'), 48 54 ** (2, 1, -1, 10 ), 49 55 ** (2, 1, 0, 'x' ), 50 56 ** (2, 1, 1, 'ten' ); 51 57 ** 52 -** If database corruption is encountered, this module does not report an 53 -** error. Instead, it attempts to extract as much data as possible and 54 -** ignores the corruption. 58 +** If database corruption is encountered, this module does not report an 59 +** error. Instead, it attempts to extract as much data as possible and 60 +** ignores the corruption. 55 61 ** 56 -** This module requires that the "sqlite_dbpage" eponymous virtual table be 57 -** available. 58 -** 62 +** SQLITE_DBPTR: 63 +** The sqlite_dbptr table has the following schema: 59 64 ** 60 65 ** CREATE TABLE sqlite_dbptr( 61 66 ** pgno INTEGER, 62 67 ** child INTEGER, 63 68 ** schema TEXT HIDDEN 64 69 ** ); 70 +** 71 +** It contains one entry for each b-tree pointer between a parent and 72 +** child page in the database. 65 73 */ 66 74 #if !defined(SQLITEINT_H) 67 75 #include "sqlite3ext.h" 68 76 69 77 typedef unsigned char u8; 70 78 typedef unsigned long u32; 71 79 ................................................................................ 73 81 SQLITE_EXTENSION_INIT1 74 82 #include <string.h> 75 83 #include <assert.h> 76 84 77 85 typedef struct DbdataTable DbdataTable; 78 86 typedef struct DbdataCursor DbdataCursor; 79 87 80 - 81 -/* A cursor for the sqlite_dbdata table */ 88 +/* Cursor object */ 82 89 struct DbdataCursor { 83 90 sqlite3_vtab_cursor base; /* Base class. Must be first */ 84 91 sqlite3_stmt *pStmt; /* For fetching database pages */ 85 92 86 93 int iPgno; /* Current page number */ 87 94 u8 *aPage; /* Buffer containing page */ 88 95 int nPage; /* Size of aPage[] in bytes */ ................................................................................ 99 106 int iField; /* Current field number */ 100 107 u8 *pHdrPtr; 101 108 u8 *pPtr; 102 109 103 110 sqlite3_int64 iIntkey; /* Integer key value */ 104 111 }; 105 112 106 -/* The sqlite_dbdata table */ 113 +/* Table object */ 107 114 struct DbdataTable { 108 115 sqlite3_vtab base; /* Base class. Must be first */ 109 116 sqlite3 *db; /* The database connection */ 110 117 sqlite3_stmt *pStmt; /* For fetching database pages */ 111 118 int bPtr; /* True for sqlite3_dbptr table */ 112 119 }; 113 120 121 +/* Column and schema definitions for sqlite_dbdata */ 114 122 #define DBDATA_COLUMN_PGNO 0 115 123 #define DBDATA_COLUMN_CELL 1 116 124 #define DBDATA_COLUMN_FIELD 2 117 125 #define DBDATA_COLUMN_VALUE 3 118 126 #define DBDATA_COLUMN_SCHEMA 4 119 - 120 -#define DBPTR_COLUMN_PGNO 0 121 -#define DBPTR_COLUMN_CHILD 1 122 -#define DBPTR_COLUMN_SCHEMA 2 123 - 124 127 #define DBDATA_SCHEMA \ 125 128 "CREATE TABLE x(" \ 126 129 " pgno INTEGER," \ 127 130 " cell INTEGER," \ 128 131 " field INTEGER," \ 129 132 " value ANY," \ 130 133 " schema TEXT HIDDEN" \ 131 134 ")" 132 135 136 +/* Column and schema definitions for sqlite_dbptr */ 137 +#define DBPTR_COLUMN_PGNO 0 138 +#define DBPTR_COLUMN_CHILD 1 139 +#define DBPTR_COLUMN_SCHEMA 2 133 140 #define DBPTR_SCHEMA \ 134 141 "CREATE TABLE x(" \ 135 142 " pgno INTEGER," \ 136 143 " child INTEGER," \ 137 144 " schema TEXT HIDDEN" \ 138 145 ")" 139 146 140 147 /* 141 -** Connect to the sqlite_dbdata virtual table. 148 +** Connect to an sqlite_dbdata (pAux==0) or sqlite_dbptr (pAux!=0) virtual 149 +** table. 142 150 */ 143 151 static int dbdataConnect( 144 152 sqlite3 *db, 145 153 void *pAux, 146 154 int argc, const char *const*argv, 147 155 sqlite3_vtab **ppVtab, 148 156 char **pzErr ................................................................................ 162 170 } 163 171 164 172 *ppVtab = (sqlite3_vtab*)pTab; 165 173 return rc; 166 174 } 167 175 168 176 /* 169 -** Disconnect from or destroy a dbdata virtual table. 177 +** Disconnect from or destroy a sqlite_dbdata or sqlite_dbptr virtual table. 170 178 */ 171 179 static int dbdataDisconnect(sqlite3_vtab *pVtab){ 172 180 DbdataTable *pTab = (DbdataTable*)pVtab; 173 181 if( pTab ){ 174 182 sqlite3_finalize(pTab->pStmt); 175 183 sqlite3_free(pVtab); 176 184 } 177 185 return SQLITE_OK; 178 186 } 179 187 180 188 /* 181 -** 182 189 ** This function interprets two types of constraints: 183 190 ** 184 191 ** schema=? 185 192 ** pgno=? 186 193 ** 187 194 ** If neither are present, idxNum is set to 0. If schema=? is present, 188 195 ** the 0x01 bit in idxNum is set. If pgno=? is present, the 0x02 bit ................................................................................ 235 242 pIdx->estimatedRows = 1000000000; 236 243 } 237 244 pIdx->idxNum = (iSchema>=0 ? 0x01 : 0x00) | (iPgno>=0 ? 0x02 : 0x00); 238 245 return SQLITE_OK; 239 246 } 240 247 241 248 /* 242 -** Open a new dbdata cursor. 249 +** Open a new sqlite_dbdata or sqlite_dbptr cursor. 243 250 */ 244 251 static int dbdataOpen(sqlite3_vtab *pVTab, sqlite3_vtab_cursor **ppCursor){ 245 252 DbdataCursor *pCsr; 246 253 247 254 pCsr = (DbdataCursor*)sqlite3_malloc64(sizeof(DbdataCursor)); 248 255 if( pCsr==0 ){ 249 256 return SQLITE_NOMEM; ................................................................................ 252 259 pCsr->base.pVtab = pVTab; 253 260 } 254 261 255 262 *ppCursor = (sqlite3_vtab_cursor *)pCsr; 256 263 return SQLITE_OK; 257 264 } 258 265 266 +/* 267 +** Restore a cursor object to the state it was in when first allocated 268 +** by dbdataOpen(). 269 +*/ 259 270 static void dbdataResetCursor(DbdataCursor *pCsr){ 260 271 DbdataTable *pTab = (DbdataTable*)(pCsr->base.pVtab); 261 272 if( pTab->pStmt==0 ){ 262 273 pTab->pStmt = pCsr->pStmt; 263 274 }else{ 264 275 sqlite3_finalize(pCsr->pStmt); 265 276 } ................................................................................ 267 278 pCsr->iPgno = 1; 268 279 pCsr->iCell = 0; 269 280 pCsr->iField = 0; 270 281 pCsr->bOnePage = 0; 271 282 } 272 283 273 284 /* 274 -** Close a dbdata cursor. 285 +** Close an sqlite_dbdata or sqlite_dbptr cursor. 275 286 */ 276 287 static int dbdataClose(sqlite3_vtab_cursor *pCursor){ 277 288 DbdataCursor *pCsr = (DbdataCursor*)pCursor; 278 289 dbdataResetCursor(pCsr); 279 290 sqlite3_free(pCsr); 280 291 return SQLITE_OK; 281 292 } 282 293 283 - 284 -/* Decode big-endian integers */ 294 +/* 295 +** Utility methods to decode 16 and 32-bit big-endian unsigned integers. 296 +*/ 285 297 static unsigned int get_uint16(unsigned char *a){ 286 298 return (a[0]<<8)|a[1]; 287 299 } 288 300 static unsigned int get_uint32(unsigned char *a){ 289 301 return (a[0]<<24)|(a[1]<<16)|(a[2]<<8)|a[3]; 290 302 } 291 303 304 +/* 305 +** Load page pgno from the database via the sqlite_dbpage virtual table. 306 +** If successful, set (*ppPage) to point to a buffer containing the page 307 +** data, (*pnPage) to the size of that buffer in bytes and return 308 +** SQLITE_OK. In this case it is the responsibility of the caller to 309 +** eventually free the buffer using sqlite3_free(). 310 +** 311 +** Or, if an error occurs, set both (*ppPage) and (*pnPage) to 0 and 312 +** return an SQLite error code. 313 +*/ 292 314 static int dbdataLoadPage( 293 - DbdataCursor *pCsr, 294 - u32 pgno, 295 - u8 **ppPage, 296 - int *pnPage 315 + DbdataCursor *pCsr, /* Cursor object */ 316 + u32 pgno, /* Page number of page to load */ 317 + u8 **ppPage, /* OUT: pointer to page buffer */ 318 + int *pnPage /* OUT: Size of (*ppPage) in bytes */ 297 319 ){ 298 320 int rc2; 299 321 int rc = SQLITE_OK; 300 322 sqlite3_stmt *pStmt = pCsr->pStmt; 301 323 302 324 *ppPage = 0; 303 325 *pnPage = 0; ................................................................................ 334 356 if( (z[i]&0x80)==0 ){ *pVal = v; return i+1; } 335 357 } 336 358 v = (v<<8) + (z[i]&0xff); 337 359 *pVal = v; 338 360 return 9; 339 361 } 340 362 363 +/* 364 +** Return the number of bytes of space used by an SQLite value of type 365 +** eType. 366 +*/ 341 367 static int dbdataValueBytes(int eType){ 342 368 switch( eType ){ 343 369 case 0: case 8: case 9: 344 370 case 10: case 11: 345 371 return 0; 346 372 case 1: 347 373 return 1; ................................................................................ 357 383 case 7: 358 384 return 8; 359 385 default: 360 386 return ((eType-12) / 2); 361 387 } 362 388 } 363 389 390 +/* 391 +** Load a value of type eType from buffer pData and use it to set the 392 +** result of context object pCtx. 393 +*/ 364 394 static void dbdataValue(sqlite3_context *pCtx, int eType, u8 *pData){ 365 395 switch( eType ){ 366 396 case 0: 367 397 case 10: 368 398 case 11: 369 399 sqlite3_result_null(pCtx); 370 400 break; ................................................................................ 407 437 } 408 438 } 409 439 } 410 440 } 411 441 412 442 413 443 /* 414 -** Move a dbdata cursor to the next entry in the file. 444 +** Move an sqlite_dbdata or sqlite_dbptr cursor to the next entry. 415 445 */ 416 446 static int dbdataNext(sqlite3_vtab_cursor *pCursor){ 417 447 DbdataCursor *pCsr = (DbdataCursor*)pCursor; 418 448 DbdataTable *pTab = (DbdataTable*)pCursor->pVtab; 419 449 420 450 pCsr->iRowid++; 421 451 while( 1 ){ ................................................................................ 571 601 } 572 602 } 573 603 574 604 assert( !"can't get here" ); 575 605 return SQLITE_OK; 576 606 } 577 607 578 -/* We have reached EOF if previous sqlite3_step() returned 579 -** anything other than SQLITE_ROW; 608 +/* 609 +** Return true if the cursor is at EOF. 580 610 */ 581 611 static int dbdataEof(sqlite3_vtab_cursor *pCursor){ 582 612 DbdataCursor *pCsr = (DbdataCursor*)pCursor; 583 613 return pCsr->aPage==0; 584 614 } 585 615 616 +/* 617 +** Determine the size in pages of database zSchema (where zSchema is 618 +** "main", "temp" or the name of an attached database) and set 619 +** pCsr->szDb accordingly. If successful, return SQLITE_OK. Otherwise, 620 +** an SQLite error code. 621 +*/ 586 622 static int dbdataDbsize(DbdataCursor *pCsr, const char *zSchema){ 587 623 DbdataTable *pTab = (DbdataTable*)pCsr->base.pVtab; 588 624 char *zSql = 0; 589 625 int rc, rc2; 590 626 sqlite3_stmt *pStmt = 0; 591 627 592 628 zSql = sqlite3_mprintf("PRAGMA %Q.page_count", zSchema); ................................................................................ 597 633 pCsr->szDb = sqlite3_column_int(pStmt, 0); 598 634 } 599 635 rc2 = sqlite3_finalize(pStmt); 600 636 if( rc==SQLITE_OK ) rc = rc2; 601 637 return rc; 602 638 } 603 639 604 -/* Position a cursor back to the beginning. 640 +/* 641 +** xFilter method for sqlite_dbdata and sqlite_dbptr. 605 642 */ 606 643 static int dbdataFilter( 607 644 sqlite3_vtab_cursor *pCursor, 608 645 int idxNum, const char *idxStr, 609 646 int argc, sqlite3_value **argv 610 647 ){ 611 648 DbdataCursor *pCsr = (DbdataCursor*)pCursor; ................................................................................ 644 681 } 645 682 if( rc==SQLITE_OK ){ 646 683 rc = dbdataNext(pCursor); 647 684 } 648 685 return rc; 649 686 } 650 687 651 -/* Return a column for the sqlite_dbdata table */ 688 +/* 689 +** Return a column for the sqlite_dbdata or sqlite_dbptr table. 690 +*/ 652 691 static int dbdataColumn( 653 692 sqlite3_vtab_cursor *pCursor, 654 693 sqlite3_context *ctx, 655 694 int i 656 695 ){ 657 696 DbdataCursor *pCsr = (DbdataCursor*)pCursor; 658 697 DbdataTable *pTab = (DbdataTable*)pCursor->pVtab; ................................................................................ 695 734 break; 696 735 } 697 736 } 698 737 } 699 738 return SQLITE_OK; 700 739 } 701 740 702 -/* Return the ROWID for the sqlite_dbdata table */ 741 +/* 742 +** Return the rowid for an sqlite_dbdata or sqlite_dptr table. 743 +*/ 703 744 static int dbdataRowid(sqlite3_vtab_cursor *pCursor, sqlite_int64 *pRowid){ 704 745 DbdataCursor *pCsr = (DbdataCursor*)pCursor; 705 746 *pRowid = pCsr->iRowid; 706 747 return SQLITE_OK; 707 748 } 708 749 709 750
Changes to src/shell.c.in.
1100 1100 #define SHFLG_Pagecache 0x00000001 /* The --pagecache option is used */ 1101 1101 #define SHFLG_Lookaside 0x00000002 /* Lookaside memory is used */ 1102 1102 #define SHFLG_Backslash 0x00000004 /* The --backslash option is used */ 1103 1103 #define SHFLG_PreserveRowid 0x00000008 /* .dump preserves rowid values */ 1104 1104 #define SHFLG_Newlines 0x00000010 /* .dump --newline flag */ 1105 1105 #define SHFLG_CountChanges 0x00000020 /* .changes setting */ 1106 1106 #define SHFLG_Echo 0x00000040 /* .echo or --echo setting */ 1107 -#define SHFLG_Recover 0x00000080 /* .dump is --recover */ 1108 1107 1109 1108 /* 1110 1109 ** Macros for testing and setting shellFlgs 1111 1110 */ 1112 1111 #define ShellHasFlag(P,X) (((P)->shellFlgs & (X))!=0) 1113 1112 #define ShellSetFlag(P,X) ((P)->shellFlgs|=(X)) 1114 1113 #define ShellClearFlag(P,X) ((P)->shellFlgs&=(~(X))) ................................................................................ 3573 3572 " --once Do no more than one progress interrupt", 3574 3573 " --quiet|-q No output except at interrupts", 3575 3574 " --reset Reset the count for each input and interrupt", 3576 3575 #endif 3577 3576 ".prompt MAIN CONTINUE Replace the standard prompts", 3578 3577 ".quit Exit this program", 3579 3578 ".read FILE Read input from FILE", 3579 + ".recover Recover as much data as possible from corrupt db.", 3580 3580 ".restore ?DB? FILE Restore content of DB (default \"main\") from FILE", 3581 3581 ".save FILE Write in-memory database into FILE", 3582 3582 ".scanstats on|off Turn sqlite3_stmt_scanstatus() metrics on or off", 3583 3583 ".schema ?PATTERN? Show the CREATE statements matching PATTERN", 3584 3584 " Options:", 3585 3585 " --indent Try to pretty-print the schema", 3586 3586 ".selftest ?OPTIONS? Run tests defined in the SELFTEST table", ................................................................................ 6149 6149 6150 6150 return rc; 6151 6151 } 6152 6152 /* End of the ".archive" or ".ar" command logic 6153 6153 **********************************************************************************/ 6154 6154 #endif /* !defined(SQLITE_OMIT_VIRTUALTABLE) && defined(SQLITE_HAVE_ZLIB) */ 6155 6155 6156 +/* 6157 +** If (*pRc) is not SQLITE_OK when this function is called, it is a no-op. 6158 +** Otherwise, the SQL statement or statements in zSql are executed using 6159 +** database connection db and the error code written to *pRc before 6160 +** this function returns. 6161 +*/ 6156 6162 static void shellExec(sqlite3 *db, int *pRc, const char *zSql){ 6157 6163 int rc = *pRc; 6158 6164 if( rc==SQLITE_OK ){ 6159 6165 char *zErr = 0; 6160 6166 rc = sqlite3_exec(db, zSql, 0, 0, &zErr); 6161 6167 if( rc!=SQLITE_OK ){ 6162 6168 raw_printf(stderr, "SQL error: %s\n", zErr); 6163 6169 } 6164 6170 *pRc = rc; 6165 6171 } 6166 6172 } 6167 6173 6174 +/* 6175 +** Like shellExec(), except that zFmt is a printf() style format string. 6176 +*/ 6168 6177 static void shellExecPrintf(sqlite3 *db, int *pRc, const char *zFmt, ...){ 6169 6178 char *z = 0; 6170 6179 if( *pRc==SQLITE_OK ){ 6171 6180 va_list ap; 6172 6181 va_start(ap, zFmt); 6173 6182 z = sqlite3_vmprintf(zFmt, ap); 6174 6183 va_end(ap); ................................................................................ 6177 6186 }else{ 6178 6187 shellExec(db, pRc, z); 6179 6188 } 6180 6189 sqlite3_free(z); 6181 6190 } 6182 6191 } 6183 6192 6193 +/* 6194 +** If *pRc is not SQLITE_OK when this function is called, it is a no-op. 6195 +** Otherwise, an attempt is made to allocate, zero and return a pointer 6196 +** to a buffer nByte bytes in size. If an OOM error occurs, *pRc is set 6197 +** to SQLITE_NOMEM and NULL returned. 6198 +*/ 6184 6199 static void *shellMalloc(int *pRc, sqlite3_int64 nByte){ 6185 6200 void *pRet = 0; 6186 6201 if( *pRc==SQLITE_OK ){ 6187 6202 pRet = sqlite3_malloc64(nByte); 6188 6203 if( pRet==0 ){ 6189 6204 *pRc = SQLITE_NOMEM; 6190 6205 }else{ 6191 6206 memset(pRet, 0, nByte); 6192 6207 } 6193 6208 } 6194 6209 return pRet; 6195 6210 } 6196 6211 6212 +/* 6213 +** If *pRc is not SQLITE_OK when this function is called, it is a no-op. 6214 +** Otherwise, zFmt is treated as a printf() style string. The result of 6215 +** formatting it along with any trailing arguments is written into a 6216 +** buffer obtained from sqlite3_malloc(), and pointer to which is returned. 6217 +** It is the responsibility of the caller to eventually free this buffer 6218 +** using a call to sqlite3_free(). 6219 +** 6220 +** If an OOM error occurs, (*pRc) is set to SQLITE_NOMEM and a NULL 6221 +** pointer returned. 6222 +*/ 6197 6223 static char *shellMPrintf(int *pRc, const char *zFmt, ...){ 6198 6224 char *z = 0; 6199 6225 if( *pRc==SQLITE_OK ){ 6200 6226 va_list ap; 6201 6227 va_start(ap, zFmt); 6202 6228 z = sqlite3_vmprintf(zFmt, ap); 6203 6229 va_end(ap); ................................................................................ 6204 6230 if( z==0 ){ 6205 6231 *pRc = SQLITE_NOMEM; 6206 6232 } 6207 6233 } 6208 6234 return z; 6209 6235 } 6210 6236 6237 +/* 6238 +** When running the ".recover" command, each output table, and the special 6239 +** orphaned row table if it is required, is represented by an instance 6240 +** of the following struct. 6241 +*/ 6211 6242 typedef struct RecoverTable RecoverTable; 6212 6243 struct RecoverTable { 6213 - char *zName; /* Name of table */ 6214 - char *zQuoted; /* Quoted version of zName */ 6244 + char *zQuoted; /* Quoted version of table name */ 6215 6245 int nCol; /* Number of columns in table */ 6216 6246 char **azlCol; /* Array of column lists */ 6217 - int iPk; 6247 + int iPk; /* Index of IPK column */ 6218 6248 }; 6219 6249 6220 6250 /* 6221 -** Free a RecoverTable object allocated by recoverNewTable() 6251 +** Free a RecoverTable object allocated by recoverFindTable() or 6252 +** recoverOrphanTable(). 6222 6253 */ 6223 6254 static void recoverFreeTable(RecoverTable *pTab){ 6224 6255 if( pTab ){ 6225 - sqlite3_free(pTab->zName); 6226 6256 sqlite3_free(pTab->zQuoted); 6227 6257 if( pTab->azlCol ){ 6228 6258 int i; 6229 6259 for(i=0; i<=pTab->nCol; i++){ 6230 6260 sqlite3_free(pTab->azlCol[i]); 6231 6261 } 6232 6262 sqlite3_free(pTab->azlCol); 6233 6263 } 6234 6264 sqlite3_free(pTab); 6235 6265 } 6236 6266 } 6237 6267 6238 -static RecoverTable *recoverOldTable( 6268 +/* 6269 +** This function is a no-op if (*pRc) is not SQLITE_OK when it is called. 6270 +** Otherwise, it allocates and returns a RecoverTable object based on the 6271 +** final four arguments passed to this function. It is the responsibility 6272 +** of the caller to eventually free the returned object using 6273 +** recoverFreeTable(). 6274 +*/ 6275 +static RecoverTable *recoverNewTable( 6239 6276 int *pRc, /* IN/OUT: Error code */ 6240 6277 const char *zName, /* Name of table */ 6241 6278 const char *zSql, /* CREATE TABLE statement */ 6242 6279 int bIntkey, 6243 6280 int nCol 6244 6281 ){ 6245 6282 sqlite3 *dbtmp = 0; /* sqlite3 handle for testing CREATE TABLE */ ................................................................................ 6305 6342 ); 6306 6343 if( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pPkFinder) ){ 6307 6344 pTab->iPk = sqlite3_column_int(pPkFinder, 0); 6308 6345 zPk = (const char*)sqlite3_column_text(pPkFinder, 1); 6309 6346 } 6310 6347 } 6311 6348 6312 - pTab->zName = shellMPrintf(&rc, "%s", zName); 6313 - pTab->zQuoted = shellMPrintf(&rc, "%Q", pTab->zName); 6349 + pTab->zQuoted = shellMPrintf(&rc, "%Q", zName); 6314 6350 pTab->azlCol = (char**)shellMalloc(&rc, sizeof(char*) * (nSqlCol+1)); 6315 6351 pTab->nCol = nSqlCol; 6316 6352 6317 6353 if( bIntkey ){ 6318 6354 pTab->azlCol[0] = shellMPrintf(&rc, "%Q", zPk); 6319 6355 }else{ 6320 6356 pTab->azlCol[0] = shellMPrintf(&rc, ""); ................................................................................ 6345 6381 if( rc!=SQLITE_OK ){ 6346 6382 recoverFreeTable(pTab); 6347 6383 pTab = 0; 6348 6384 } 6349 6385 return pTab; 6350 6386 } 6351 6387 6352 -static RecoverTable *recoverNewTable( 6388 +static RecoverTable *recoverFindTable( 6353 6389 ShellState *pState, 6354 6390 int *pRc, 6355 6391 int iRoot, 6356 6392 int bIntkey, 6357 6393 int nCol, 6358 6394 int *pbNoop 6359 6395 ){ 6360 6396 sqlite3_stmt *pStmt = 0; 6361 6397 RecoverTable *pRet = 0; 6362 6398 int bNoop = 0; 6363 6399 const char *zSql = 0; 6364 6400 const char *zName = 0; 6365 - 6366 6401 6367 6402 /* Search the recovered schema for an object with root page iRoot. */ 6368 6403 shellPreparePrintf(pState->db, pRc, &pStmt, 6369 6404 "SELECT type, name, sql FROM recovery.schema WHERE rootpage=%d", iRoot 6370 6405 ); 6371 6406 while( *pRc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pStmt) ){ 6372 6407 const char *zType = (const char*)sqlite3_column_text(pStmt, 0); ................................................................................ 6373 6408 if( bIntkey==0 && sqlite3_stricmp(zType, "index")==0 ){ 6374 6409 bNoop = 1; 6375 6410 break; 6376 6411 } 6377 6412 if( sqlite3_stricmp(zType, "table")==0 ){ 6378 6413 zName = (const char*)sqlite3_column_text(pStmt, 1); 6379 6414 zSql = (const char*)sqlite3_column_text(pStmt, 2); 6380 - pRet = recoverOldTable(pRc, zName, zSql, bIntkey, nCol); 6415 + pRet = recoverNewTable(pRc, zName, zSql, bIntkey, nCol); 6381 6416 break; 6382 6417 } 6383 6418 } 6384 6419 6385 6420 shellFinalize(pRc, pStmt); 6386 6421 *pbNoop = bNoop; 6387 6422 return pRet; 6388 6423 } 6389 6424 6390 6425 static RecoverTable *recoverOrphanTable( 6391 6426 ShellState *pState, 6392 6427 int *pRc, 6428 + const char *zLostAndFound, 6393 6429 int nCol 6394 6430 ){ 6395 6431 RecoverTable *pTab = 0; 6396 6432 if( nCol>=0 && *pRc==SQLITE_OK ){ 6397 6433 int i; 6398 - raw_printf(pState->out, 6399 - "CREATE TABLE recover_orphan(rootpgno INTEGER, " 6400 - "pgno INTEGER, nfield INTEGER, id INTEGER" 6434 + 6435 + /* This block determines the name of the orphan table. The prefered 6436 + ** name is zLostAndFound. But if that clashes with another name 6437 + ** in the recovered schema, try zLostAndFound_0, zLostAndFound_1 6438 + ** and so on until a non-clashing name is found. */ 6439 + int iTab = 0; 6440 + char *zTab = shellMPrintf(pRc, "%s", zLostAndFound); 6441 + sqlite3_stmt *pTest = 0; 6442 + shellPrepare(pState->db, pRc, 6443 + "SELECT 1 FROM recovery.schema WHERE name=?", &pTest 6401 6444 ); 6402 - for(i=0; i<nCol; i++){ 6403 - raw_printf(pState->out, ", c%d", i); 6445 + if( pTest ) sqlite3_bind_text(pTest, 1, zTab, -1, SQLITE_TRANSIENT); 6446 + while( *pRc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pTest) ){ 6447 + shellReset(pRc, pTest); 6448 + sqlite3_free(zTab); 6449 + zTab = shellMPrintf(pRc, "%s_%d", zLostAndFound, iTab++); 6450 + sqlite3_bind_text(pTest, 1, zTab, -1, SQLITE_TRANSIENT); 6404 6451 } 6405 - raw_printf(pState->out, ");\n"); 6452 + shellFinalize(pRc, pTest); 6406 6453 6407 6454 pTab = (RecoverTable*)shellMalloc(pRc, sizeof(RecoverTable)); 6408 6455 if( pTab ){ 6409 - pTab->zName = shellMPrintf(pRc, "%s", "recover_orphan"); 6410 - pTab->zQuoted = shellMPrintf(pRc, "%Q", pTab->zName); 6456 + pTab->zQuoted = shellMPrintf(pRc, "%Q", zTab); 6411 6457 pTab->nCol = nCol; 6412 6458 pTab->iPk = -2; 6413 6459 if( nCol>0 ){ 6414 6460 pTab->azlCol = (char**)shellMalloc(pRc, sizeof(char*) * (nCol+1)); 6415 6461 if( pTab->azlCol ){ 6416 6462 pTab->azlCol[nCol] = shellMPrintf(pRc, ""); 6417 6463 for(i=nCol-1; i>=0; i--){ 6418 6464 pTab->azlCol[i] = shellMPrintf(pRc, "%s, NULL", pTab->azlCol[i+1]); 6419 6465 } 6420 6466 } 6421 6467 } 6468 + 6469 + if( *pRc!=SQLITE_OK ){ 6470 + recoverFreeTable(pTab); 6471 + pTab = 0; 6472 + }else{ 6473 + raw_printf(pState->out, 6474 + "CREATE TABLE %s(rootpgno INTEGER, " 6475 + "pgno INTEGER, nfield INTEGER, id INTEGER", pTab->zQuoted 6476 + ); 6477 + for(i=0; i<nCol; i++){ 6478 + raw_printf(pState->out, ", c%d", i); 6479 + } 6480 + raw_printf(pState->out, ");\n"); 6481 + } 6422 6482 } 6423 - 6424 - if( *pRc!=SQLITE_OK ){ 6425 - recoverFreeTable(pTab); 6426 - pTab = 0; 6427 - } 6483 + sqlite3_free(zTab); 6428 6484 } 6429 6485 return pTab; 6430 6486 } 6431 6487 6432 6488 /* 6433 6489 ** This function is called to recover data from the database. A script 6434 6490 ** to construct a new database containing all recovered data is output ................................................................................ 6436 6492 */ 6437 6493 static int recoverDatabaseCmd(ShellState *pState, int nArg, char **azArg){ 6438 6494 int rc = SQLITE_OK; 6439 6495 sqlite3_stmt *pLoop = 0; /* Loop through all root pages */ 6440 6496 sqlite3_stmt *pPages = 0; /* Loop through all pages in a group */ 6441 6497 sqlite3_stmt *pCells = 0; /* Loop through all cells in a page */ 6442 6498 const char *zRecoveryDb = ""; /* Name of "recovery" database */ 6499 + const char *zLostAndFound = "lost_and_found"; 6443 6500 int i; 6444 6501 int nOrphan = -1; 6445 6502 RecoverTable *pOrphan = 0; 6446 6503 6447 6504 int bFreelist = 1; /* 0 if --freelist-corrupt is specified */ 6448 6505 for(i=1; i<nArg; i++){ 6449 6506 char *z = azArg[i]; 6450 6507 int n; 6451 6508 if( z[0]=='-' && z[1]=='-' ) z++; 6452 6509 n = strlen(z); 6453 6510 if( n<=17 && memcmp("-freelist-corrupt", z, n)==0 ){ 6454 6511 bFreelist = 0; 6455 - } 6512 + }else 6456 6513 if( n<=12 && memcmp("-recovery-db", z, n)==0 && i<(nArg-1) ){ 6457 6514 i++; 6458 6515 zRecoveryDb = azArg[i]; 6516 + }else 6517 + if( n<=15 && memcmp("-lost-and-found", z, n)==0 && i<(nArg-1) ){ 6518 + i++; 6519 + zLostAndFound = azArg[i]; 6459 6520 } 6460 6521 else{ 6461 6522 raw_printf(stderr, "unexpected option: %s\n", azArg[i]); 6462 6523 raw_printf(stderr, "options are:\n"); 6463 6524 raw_printf(stderr, " --freelist-corrupt\n"); 6464 6525 raw_printf(stderr, " --recovery-db DATABASE\n"); 6526 + raw_printf(stderr, " --lost-and-found TABLE-NAME\n"); 6465 6527 return 1; 6466 6528 } 6467 6529 } 6468 6530 6469 6531 shellExecPrintf(pState->db, &rc, 6470 6532 /* Attach an in-memory database named 'recovery'. Create an indexed 6471 6533 ** cache of the sqlite_dbptr virtual table. */ ................................................................................ 6595 6657 , &pLoop 6596 6658 ); 6597 6659 if( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pLoop) ){ 6598 6660 nOrphan = sqlite3_column_int(pLoop, 0); 6599 6661 } 6600 6662 shellFinalize(&rc, pLoop); 6601 6663 pLoop = 0; 6602 - pOrphan = recoverOrphanTable(pState, &rc, nOrphan); 6664 + pOrphan = recoverOrphanTable(pState, &rc, zLostAndFound, nOrphan); 6603 6665 6604 6666 shellPrepare(pState->db, &rc, 6605 6667 "SELECT pgno FROM recovery.map WHERE root=?", &pPages 6606 6668 ); 6607 6669 shellPrepare(pState->db, &rc, 6608 6670 "SELECT max(field), group_concat(shell_escape_crnl(quote(value)), ', ')" 6609 6671 "FROM sqlite_dbdata WHERE pgno = ? AND field != ?" ................................................................................ 6620 6682 while( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pLoop) ){ 6621 6683 int iRoot = sqlite3_column_int(pLoop, 0); 6622 6684 int bIntkey = sqlite3_column_int(pLoop, 1); 6623 6685 int nCol = sqlite3_column_int(pLoop, 2); 6624 6686 int bNoop = 0; 6625 6687 RecoverTable *pTab; 6626 6688 6627 - pTab = recoverNewTable(pState, &rc, iRoot, bIntkey, nCol, &bNoop); 6689 + pTab = recoverFindTable(pState, &rc, iRoot, bIntkey, nCol, &bNoop); 6628 6690 if( bNoop || rc ) continue; 6629 6691 if( pTab==0 ) pTab = pOrphan; 6630 6692 6631 - if( 0==sqlite3_stricmp(pTab->zName, "sqlite_sequence") ){ 6693 + if( 0==sqlite3_stricmp(pTab->zQuoted, "'sqlite_sequence'") ){ 6632 6694 raw_printf(pState->out, "DELETE FROM sqlite_sequence;\n"); 6633 6695 } 6634 6696 sqlite3_bind_int(pPages, 1, iRoot); 6635 6697 sqlite3_bind_int(pCells, 2, pTab->iPk); 6636 6698 6637 6699 while( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pPages) ){ 6638 6700 int iPgno = sqlite3_column_int(pPages, 0); ................................................................................ 7038 7100 /* Set writable_schema=ON since doing so forces SQLite to initialize 7039 7101 ** as much of the schema as it can even if the sqlite_master table is 7040 7102 ** corrupt. */ 7041 7103 sqlite3_exec(p->db, "SAVEPOINT dump; PRAGMA writable_schema=ON", 0, 0, 0); 7042 7104 p->nErr = 0; 7043 7105 if( zLike==0 ){ 7044 7106 run_schema_dump_query(p, 7045 - "SELECT name, type, sql FROM sqlite_master " 7046 - "WHERE sql NOT NULL AND type=='table' AND name!='sqlite_sequence'" 7047 - ); 7107 + "SELECT name, type, sql FROM sqlite_master " 7108 + "WHERE sql NOT NULL AND type=='table' AND name!='sqlite_sequence'" 7109 + ); 7048 7110 run_schema_dump_query(p, 7049 - "SELECT name, type, sql FROM sqlite_master " 7050 - "WHERE name=='sqlite_sequence'" 7051 - ); 7111 + "SELECT name, type, sql FROM sqlite_master " 7112 + "WHERE name=='sqlite_sequence'" 7113 + ); 7052 7114 run_table_dump_query(p, 7053 - "SELECT sql FROM sqlite_master " 7054 - "WHERE sql NOT NULL AND type IN ('index','trigger','view')", 0 7055 - ); 7115 + "SELECT sql FROM sqlite_master " 7116 + "WHERE sql NOT NULL AND type IN ('index','trigger','view')", 0 7117 + ); 7056 7118 }else{ 7057 7119 char *zSql; 7058 7120 zSql = sqlite3_mprintf( 7059 - "SELECT name, type, sql FROM sqlite_master " 7060 - "WHERE tbl_name LIKE %Q AND type=='table'" 7061 - " AND sql NOT NULL", zLike); 7121 + "SELECT name, type, sql FROM sqlite_master " 7122 + "WHERE tbl_name LIKE %Q AND type=='table'" 7123 + " AND sql NOT NULL", zLike); 7062 7124 run_schema_dump_query(p,zSql); 7063 7125 sqlite3_free(zSql); 7064 7126 zSql = sqlite3_mprintf( 7065 - "SELECT sql FROM sqlite_master " 7066 - "WHERE sql NOT NULL" 7067 - " AND type IN ('index','trigger','view')" 7068 - " AND tbl_name LIKE %Q", zLike); 7127 + "SELECT sql FROM sqlite_master " 7128 + "WHERE sql NOT NULL" 7129 + " AND type IN ('index','trigger','view')" 7130 + " AND tbl_name LIKE %Q", zLike); 7069 7131 run_table_dump_query(p, zSql, 0); 7070 7132 sqlite3_free(zSql); 7071 7133 } 7072 7134 if( p->writableSchema ){ 7073 7135 raw_printf(p->out, "PRAGMA writable_schema=OFF;\n"); 7074 7136 p->writableSchema = 0; 7075 7137 }
Changes to test/recover.test.
35 35 proc compare_dbs {db1 db2} { 36 36 compare_result $db1 $db2 "SELECT sql FROM sqlite_master ORDER BY 1" 37 37 foreach tbl [$db1 eval {SELECT name FROM sqlite_master WHERE type='table'}] { 38 38 compare_result $db1 $db2 "SELECT * FROM $tbl" 39 39 } 40 40 } 41 41 42 -proc do_recover_test {tn} { 42 +proc do_recover_test {tn {tsql {}} {res {}}} { 43 43 set fd [open "|$::CLI test.db .recover"] 44 44 fconfigure $fd -encoding binary 45 45 fconfigure $fd -translation binary 46 46 set sql [read $fd] 47 47 close $fd 48 48 49 49 forcedelete test.db2 50 50 sqlite3 db2 test.db2 51 - breakpoint 52 51 execsql $sql db2 53 - uplevel [list do_test $tn [list compare_dbs db db2] {}] 52 + if {$tsql==""} { 53 + uplevel [list do_test $tn [list compare_dbs db db2] {}] 54 + } else { 55 + uplevel [list do_execsql_test -db db2 $tn $tsql $res] 56 + } 54 57 db2 close 55 58 } 56 59 57 60 set doc { 58 61 hello 59 62 world 60 63 } ................................................................................ 91 94 CREATE TABLE t1(a, b, c, PRIMARY KEY(b, c)) WITHOUT ROWID; 92 95 INSERT INTO t1 VALUES(1, 2, 3); 93 96 INSERT INTO t1 VALUES(4, 5, 6); 94 97 INSERT INTO t1 VALUES(7, 8, 9); 95 98 } 96 99 97 100 do_recover_test 2.1.1 101 + 102 +do_execsql_test 2.2.0 { 103 + PRAGMA writable_schema = 1; 104 + DELETE FROM sqlite_master WHERE name='t1'; 105 +} 106 +do_recover_test 2.2.1 { 107 + SELECT name FROM sqlite_master 108 +} {lost_and_found} 109 + 110 +do_execsql_test 2.3.0 { 111 + CREATE TABLE lost_and_found(a, b, c); 112 +} 113 +do_recover_test 2.3.1 { 114 + SELECT name FROM sqlite_master 115 +} {lost_and_found lost_and_found_0} 116 + 117 +do_execsql_test 2.4.0 { 118 + CREATE TABLE lost_and_found_0(a, b, c); 119 +} 120 +do_recover_test 2.4.1 { 121 + SELECT name FROM sqlite_master; 122 + SELECT * FROM lost_and_found_1; 123 +} {lost_and_found lost_and_found_0 lost_and_found_1 124 + 2 2 3 {} 2 3 1 125 + 2 2 3 {} 5 6 4 126 + 2 2 3 {} 8 9 7 127 +} 98 128 99 129 finish_test