/ Check-in [67bb88e2]
Login

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: 67bb88e24c74d02ae0c4ac6ff2f873f6b0035ccefe5cccfc71c5686cbc76b4c3
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
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

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