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