/ Check-in [7461d2e1]
Login

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

Overview
Comment:Add the ".recovery" command to the shell tool. For recovering the maximum amount data from corrupt databases. Still needs work.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | dbdata
Files: files | file ages | folders
SHA3-256: 7461d2e120f2149315ddac2676d51d7445bcdb8e97543effd9c30603517ef9da
User & Date: dan 2019-04-20 20:57:28
Context
2019-04-22
20:52
Enhance the ".recover" command. Fix a problem with overflow pages in dbdata.c. check-in: f193ca58 user: dan tags: dbdata
2019-04-20
20:57
Add the ".recovery" command to the shell tool. For recovering the maximum amount data from corrupt databases. Still needs work. check-in: 7461d2e1 user: dan tags: dbdata
2019-04-18
21:14
Add the sqlite_dbptr virtual table to the dbdata extension. For querying the links between b-tree pages. check-in: 3213a15f user: dan tags: dbdata
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to ext/misc/dbdata.c.

    59     59   **
    60     60   **     CREATE TABLE sqlite_dbptr(
    61     61   **       pgno INTEGER,
    62     62   **       child INTEGER,
    63     63   **       schema TEXT HIDDEN
    64     64   **     );
    65     65   */
    66         -#if !defined(SQLITEINT_H)
           66  +#if !defined(SQLITEINT_H) 
    67     67   #include "sqlite3ext.h"
    68     68   
    69     69   typedef unsigned char u8;
    70         -typedef unsigned int u32;
           70  +typedef unsigned long u32;
    71     71   
    72     72   #endif
    73     73   SQLITE_EXTENSION_INIT1
    74     74   #include <string.h>
    75     75   #include <assert.h>
    76     76   
    77     77   typedef struct DbdataTable DbdataTable;
................................................................................
    90     90     int iCell;                      /* Current cell number */
    91     91     int bOnePage;                   /* True to stop after one page */
    92     92     sqlite3_int64 iRowid;
    93     93   
    94     94     /* Only for the sqlite_dbdata table */
    95     95     u8 *pRec;                       /* Buffer containing current record */
    96     96     int nRec;                       /* Size of pRec[] in bytes */
    97         -  int nField;                     /* Number of fields in pRec */
           97  +  int nHdr;                       /* Size of header in bytes */
    98     98     int iField;                     /* Current field number */
           99  +  u8 *pHdrPtr;
          100  +  u8 *pPtr;
          101  +  
    99    102     sqlite3_int64 iIntkey;          /* Integer key value */
   100    103   };
   101    104   
   102    105   /* The sqlite_dbdata table */
   103    106   struct DbdataTable {
   104    107     sqlite3_vtab base;              /* Base class.  Must be first */
   105    108     sqlite3 *db;                    /* The database connection */
................................................................................
   301    304       v = (v<<7) + (z[i]&0x7f);
   302    305       if( (z[i]&0x80)==0 ){ *pVal = v; return i+1; }
   303    306     }
   304    307     v = (v<<8) + (z[i]&0xff);
   305    308     *pVal = v;
   306    309     return 9;
   307    310   }
          311  +
          312  +static int dbdataValueBytes(int eType){
          313  +  switch( eType ){
          314  +    case 0: case 8: case 9:
          315  +    case 10: case 11:
          316  +      return 0;
          317  +    case 1:
          318  +      return 1;
          319  +    case 2:
          320  +      return 2;
          321  +    case 3:
          322  +      return 3;
          323  +    case 4:
          324  +      return 4;
          325  +    case 5:
          326  +      return 6;
          327  +    case 6:
          328  +    case 7:
          329  +      return 8;
          330  +    default:
          331  +      return ((eType-12) / 2);
          332  +  }
          333  +}
          334  +
          335  +static void dbdataValue(sqlite3_context *pCtx, int eType, u8 *pData){
          336  +  switch( eType ){
          337  +    case 0: 
          338  +    case 10: 
          339  +    case 11: 
          340  +      sqlite3_result_null(pCtx);
          341  +      break;
          342  +    
          343  +    case 8: 
          344  +      sqlite3_result_int(pCtx, 0);
          345  +      break;
          346  +    case 9:
          347  +      sqlite3_result_int(pCtx, 1);
          348  +      break;
          349  +
          350  +    case 1: case 2: case 3: case 4: case 5: case 6: case 7: {
          351  +      sqlite3_uint64 v = (signed char)pData[0];
          352  +      pData++;
          353  +      switch( eType ){
          354  +        case 7:
          355  +        case 6:  v = (v<<16) + (pData[0]<<8) + pData[1];  pData += 2;
          356  +        case 5:  v = (v<<16) + (pData[0]<<8) + pData[1];  pData += 2;
          357  +        case 4:  v = (v<<8) + pData[0];  pData++;
          358  +        case 3:  v = (v<<8) + pData[0];  pData++;
          359  +        case 2:  v = (v<<8) + pData[0];  pData++;
          360  +      }
          361  +
          362  +      if( eType==7 ){
          363  +        double r;
          364  +        memcpy(&r, &v, sizeof(r));
          365  +        sqlite3_result_double(pCtx, r);
          366  +      }else{
          367  +        sqlite3_result_int64(pCtx, (sqlite3_int64)v);
          368  +      }
          369  +      break;
          370  +    }
          371  +
          372  +    default: {
          373  +      int n = ((eType-12) / 2);
          374  +      if( eType % 2 ){
          375  +        sqlite3_result_text(pCtx, (const char*)pData, n, SQLITE_TRANSIENT);
          376  +      }else{
          377  +        sqlite3_result_blob(pCtx, pData, n, SQLITE_TRANSIENT);
          378  +      }
          379  +    }
          380  +  }
          381  +}
          382  +
   308    383   
   309    384   /*
   310    385   ** Move a dbdata cursor to the next entry in the file.
   311    386   */
   312    387   static int dbdataNext(sqlite3_vtab_cursor *pCursor){
   313    388     DbdataCursor *pCsr = (DbdataCursor*)pCursor;
   314    389     DbdataTable *pTab = (DbdataTable*)pCursor->pVtab;
................................................................................
   431    506               memcpy(&pCsr->pRec[nPayload-nRem], &aOvfl[4], nCopy);
   432    507               nRem -= nCopy;
   433    508     
   434    509               sqlite3_free(aOvfl);
   435    510             }
   436    511           }
   437    512     
   438         -        /* Figure out how many fields in the record */
   439         -        pCsr->nField = 0;
   440    513           iHdr = dbdataGetVarint(pCsr->pRec, &nHdr);
   441         -        while( iHdr<nHdr ){
   442         -          sqlite3_int64 iDummy;
   443         -          iHdr += dbdataGetVarint(&pCsr->pRec[iHdr], &iDummy);
   444         -          pCsr->nField++;
          514  +        pCsr->nHdr = nHdr;
          515  +        pCsr->pHdrPtr = &pCsr->pRec[iHdr];
          516  +        pCsr->pPtr = &pCsr->pRec[pCsr->nHdr];
          517  +        pCsr->iField = (bHasRowid ? -1 : 0);
          518  +      }else{
          519  +        pCsr->iField++;
          520  +        if( pCsr->iField>0 ){
          521  +          sqlite3_int64 iType;
          522  +          pCsr->pHdrPtr += dbdataGetVarint(pCsr->pHdrPtr, &iType);
          523  +          pCsr->pPtr += dbdataValueBytes(iType);
   445    524           }
   446         -  
   447         -        pCsr->iField = (bHasRowid ? -2 : -1);
   448    525         }
   449         -  
   450         -      pCsr->iField++;
   451         -      if( pCsr->iField<pCsr->nField ) return SQLITE_OK;
          526  +
          527  +      if( pCsr->iField<0 || pCsr->pHdrPtr<&pCsr->pRec[pCsr->nHdr] ){
          528  +        return SQLITE_OK;
          529  +      }
   452    530     
   453    531         /* Advance to the next cell. The next iteration of the loop will load
   454    532         ** the record and so on. */
   455    533         sqlite3_free(pCsr->pRec);
   456    534         pCsr->pRec = 0;
   457    535         pCsr->iCell++;
   458    536       }
................................................................................
   481    559     DbdataTable *pTab = (DbdataTable*)pCursor->pVtab;
   482    560     int rc;
   483    561     const char *zSchema = "main";
   484    562   
   485    563     dbdataResetCursor(pCsr);
   486    564     assert( pCsr->iPgno==1 );
   487    565     if( idxNum & 0x01 ){
   488         -    zSchema = sqlite3_value_text(argv[0]);
          566  +    zSchema = (const char*)sqlite3_value_text(argv[0]);
   489    567     }
   490    568     if( idxNum & 0x02 ){
   491    569       pCsr->iPgno = sqlite3_value_int(argv[(idxNum & 0x01)]);
   492    570       pCsr->bOnePage = 1;
   493    571     }
   494    572   
   495    573     rc = sqlite3_prepare_v2(pTab->db, 
   496    574         "SELECT data FROM sqlite_dbpage(?) WHERE pgno=?", -1,
   497    575         &pCsr->pStmt, 0
   498    576     );
   499    577     if( rc==SQLITE_OK ){
   500    578       rc = sqlite3_bind_text(pCsr->pStmt, 1, zSchema, -1, SQLITE_TRANSIENT);
          579  +  }else{
          580  +    pTab->base.zErrMsg = sqlite3_mprintf("%s", sqlite3_errmsg(pTab->db));
   501    581     }
   502    582     if( rc==SQLITE_OK ){
   503    583       rc = dbdataNext(pCursor);
   504    584     }
   505    585     return rc;
   506    586   }
   507    587   
   508         -static int dbdataValueBytes(int eType){
   509         -  switch( eType ){
   510         -    case 0: case 8: case 9:
   511         -    case 10: case 11:
   512         -      return 0;
   513         -    case 1:
   514         -      return 1;
   515         -    case 2:
   516         -      return 2;
   517         -    case 3:
   518         -      return 3;
   519         -    case 4:
   520         -      return 4;
   521         -    case 5:
   522         -      return 6;
   523         -    case 6:
   524         -    case 7:
   525         -      return 8;
   526         -    default:
   527         -      return ((eType-12) / 2);
   528         -  }
   529         -}
   530         -
   531         -static void dbdataValue(sqlite3_context *pCtx, int eType, u8 *pData){
   532         -  switch( eType ){
   533         -    case 0: 
   534         -    case 10: 
   535         -    case 11: 
   536         -      sqlite3_result_null(pCtx);
   537         -      break;
   538         -    
   539         -    case 8: 
   540         -      sqlite3_result_int(pCtx, 0);
   541         -      break;
   542         -    case 9:
   543         -      sqlite3_result_int(pCtx, 1);
   544         -      break;
   545         -
   546         -    case 1: case 2: case 3: case 4: case 5: case 6: case 7: {
   547         -      sqlite3_uint64 v = (signed char)pData[0];
   548         -      pData++;
   549         -      switch( eType ){
   550         -        case 7:
   551         -        case 6:  v = (v<<16) + (pData[0]<<8) + pData[1];  pData += 2;
   552         -        case 5:  v = (v<<16) + (pData[0]<<8) + pData[1];  pData += 2;
   553         -        case 4:  v = (v<<8) + pData[0];  pData++;
   554         -        case 3:  v = (v<<8) + pData[0];  pData++;
   555         -        case 2:  v = (v<<8) + pData[0];  pData++;
   556         -      }
   557         -
   558         -      if( eType==7 ){
   559         -        double r;
   560         -        memcpy(&r, &v, sizeof(r));
   561         -        sqlite3_result_double(pCtx, r);
   562         -      }else{
   563         -        sqlite3_result_int64(pCtx, (sqlite3_int64)v);
   564         -      }
   565         -      break;
   566         -    }
   567         -
   568         -    default: {
   569         -      int n = ((eType-12) / 2);
   570         -      if( eType % 2 ){
   571         -        sqlite3_result_text(pCtx, pData, n, SQLITE_TRANSIENT);
   572         -      }else{
   573         -        sqlite3_result_blob(pCtx, pData, n, SQLITE_TRANSIENT);
   574         -      }
   575         -    }
   576         -  }
   577         -}
   578         -
   579    588   /* Return a column for the sqlite_dbdata table */
   580    589   static int dbdataColumn(
   581    590     sqlite3_vtab_cursor *pCursor, 
   582    591     sqlite3_context *ctx, 
   583    592     int i
   584    593   ){
   585    594     DbdataCursor *pCsr = (DbdataCursor*)pCursor;
................................................................................
   612    621         case DBDATA_COLUMN_FIELD:
   613    622           sqlite3_result_int(ctx, pCsr->iField);
   614    623           break;
   615    624         case DBDATA_COLUMN_VALUE: {
   616    625           if( pCsr->iField<0 ){
   617    626             sqlite3_result_int64(ctx, pCsr->iIntkey);
   618    627           }else{
   619         -          int iHdr;
   620    628             sqlite3_int64 iType;
   621         -          sqlite3_int64 iOff;
   622         -          int i;
   623         -          iHdr = dbdataGetVarint(pCsr->pRec, &iOff);
   624         -          for(i=0; i<pCsr->iField; i++){
   625         -            iHdr += dbdataGetVarint(&pCsr->pRec[iHdr], &iType);
   626         -            iOff += dbdataValueBytes(iType);
   627         -          }
   628         -          dbdataGetVarint(&pCsr->pRec[iHdr], &iType);
   629         -
   630         -          dbdataValue(ctx, iType, &pCsr->pRec[iOff]);
          629  +          dbdataGetVarint(pCsr->pHdrPtr, &iType);
          630  +          dbdataValue(ctx, iType, pCsr->pPtr);
   631    631           }
   632    632           break;
   633    633         }
   634    634       }
   635    635     }
   636    636     return SQLITE_OK;
   637    637   }

Changes to main.mk.

   734    734   	$(TOP)/ext/misc/fileio.c \
   735    735   	$(TOP)/ext/misc/completion.c \
   736    736   	$(TOP)/ext/misc/sqlar.c \
   737    737   	$(TOP)/ext/expert/sqlite3expert.c \
   738    738   	$(TOP)/ext/expert/sqlite3expert.h \
   739    739   	$(TOP)/ext/misc/zipfile.c \
   740    740   	$(TOP)/ext/misc/memtrace.c \
          741  +	$(TOP)/ext/misc/dbdata.c \
   741    742           $(TOP)/src/test_windirent.c
   742    743   
   743    744   shell.c:	$(SHELL_SRC) $(TOP)/tool/mkshellc.tcl
   744    745   	tclsh $(TOP)/tool/mkshellc.tcl >shell.c
   745    746   
   746    747   
   747    748   

Changes to src/shell.c.in.

   943    943   INCLUDE ../ext/misc/memtrace.c
   944    944   #ifdef SQLITE_HAVE_ZLIB
   945    945   INCLUDE ../ext/misc/zipfile.c
   946    946   INCLUDE ../ext/misc/sqlar.c
   947    947   #endif
   948    948   INCLUDE ../ext/expert/sqlite3expert.h
   949    949   INCLUDE ../ext/expert/sqlite3expert.c
          950  +
          951  +INCLUDE ../ext/misc/dbdata.c
   950    952   
   951    953   #if defined(SQLITE_ENABLE_SESSION)
   952    954   /*
   953    955   ** State information for a single open session
   954    956   */
   955    957   typedef struct OpenSession OpenSession;
   956    958   struct OpenSession {
................................................................................
  1098   1100   #define SHFLG_Pagecache      0x00000001 /* The --pagecache option is used */
  1099   1101   #define SHFLG_Lookaside      0x00000002 /* Lookaside memory is used */
  1100   1102   #define SHFLG_Backslash      0x00000004 /* The --backslash option is used */
  1101   1103   #define SHFLG_PreserveRowid  0x00000008 /* .dump preserves rowid values */
  1102   1104   #define SHFLG_Newlines       0x00000010 /* .dump --newline flag */
  1103   1105   #define SHFLG_CountChanges   0x00000020 /* .changes setting */
  1104   1106   #define SHFLG_Echo           0x00000040 /* .echo or --echo setting */
         1107  +#define SHFLG_Recover        0x00000080 /* .dump is --recover */
  1105   1108   
  1106   1109   /*
  1107   1110   ** Macros for testing and setting shellFlgs
  1108   1111   */
  1109   1112   #define ShellHasFlag(P,X)    (((P)->shellFlgs & (X))!=0)
  1110   1113   #define ShellSetFlag(P,X)    ((P)->shellFlgs|=(X))
  1111   1114   #define ShellClearFlag(P,X)  ((P)->shellFlgs&=(~(X)))
................................................................................
  3995   3998       }
  3996   3999   #ifndef SQLITE_OMIT_LOAD_EXTENSION
  3997   4000       sqlite3_enable_load_extension(p->db, 1);
  3998   4001   #endif
  3999   4002       sqlite3_fileio_init(p->db, 0, 0);
  4000   4003       sqlite3_shathree_init(p->db, 0, 0);
  4001   4004       sqlite3_completion_init(p->db, 0, 0);
         4005  +    sqlite3_dbdata_init(p->db, 0, 0);
  4002   4006   #ifdef SQLITE_HAVE_ZLIB
  4003   4007       sqlite3_zipfile_init(p->db, 0, 0);
  4004   4008       sqlite3_sqlar_init(p->db, 0, 0);
  4005   4009   #endif
  4006   4010       sqlite3_create_function(p->db, "shell_add_schema", 3, SQLITE_UTF8, 0,
  4007   4011                               shellAddSchemaName, 0, 0);
  4008   4012       sqlite3_create_function(p->db, "shell_module_schema", 1, SQLITE_UTF8, 0,
................................................................................
  6022   6026   
  6023   6027     return rc;
  6024   6028   }
  6025   6029   /* End of the ".archive" or ".ar" command logic
  6026   6030   **********************************************************************************/
  6027   6031   #endif /* !defined(SQLITE_OMIT_VIRTUALTABLE) && defined(SQLITE_HAVE_ZLIB) */
  6028   6032   
         6033  +static void shellExec(sqlite3 *db, int *pRc, const char *zSql){
         6034  +  int rc = *pRc;
         6035  +  if( rc==SQLITE_OK ){
         6036  +    char *zErr = 0;
         6037  +    rc = sqlite3_exec(db, zSql, 0, 0, &zErr);
         6038  +    if( rc!=SQLITE_OK ){
         6039  +      raw_printf(stderr, "SQL error: %s\n", zErr);
         6040  +    }
         6041  +    *pRc = rc;
         6042  +  }
         6043  +}
         6044  +
         6045  +static void *shellMalloc(int *pRc, sqlite3_int64 nByte){
         6046  +  void *pRet = 0;
         6047  +  if( *pRc==SQLITE_OK ){
         6048  +    pRet = sqlite3_malloc64(nByte);
         6049  +    if( pRet==0 ){
         6050  +      *pRc = SQLITE_NOMEM;
         6051  +    }else{
         6052  +      memset(pRet, 0, nByte);
         6053  +    }
         6054  +  }
         6055  +  return pRet;
         6056  +}
         6057  +
         6058  +static char *shellMPrintf(int *pRc, const char *zFmt, ...){
         6059  +  char *z = 0;
         6060  +  if( *pRc==SQLITE_OK ){
         6061  +    va_list ap;
         6062  +    va_start(ap, zFmt);
         6063  +    z = sqlite3_vmprintf(zFmt, ap);
         6064  +    va_end(ap);
         6065  +    if( z==0 ){
         6066  +      *pRc = SQLITE_NOMEM;
         6067  +    }
         6068  +  }
         6069  +  return z;
         6070  +}
         6071  +
         6072  +typedef struct RecoverTable RecoverTable;
         6073  +struct RecoverTable {
         6074  +  char *zName;                    /* Name of table */
         6075  +  char *zQuoted;                  /* Quoted version of zName */
         6076  +  char *zCreate;                  /* SQL to create table in default schema */
         6077  +  int nCol;                       /* Number of columns in table */
         6078  +  char **azlCol;                  /* Array of column lists */
         6079  +};
         6080  +
         6081  +/*
         6082  +** Free a RecoverTable object allocated by recoverNewTable()
         6083  +*/
         6084  +static void recoverFreeTable(RecoverTable *pTab){
         6085  +  if( pTab ){
         6086  +    sqlite3_free(pTab->zName);
         6087  +    sqlite3_free(pTab->zQuoted);
         6088  +    sqlite3_free(pTab->zCreate);
         6089  +    if( pTab->azlCol ){
         6090  +      int i;
         6091  +      for(i=0; i<pTab->nCol; i++){
         6092  +        sqlite3_free(pTab->azlCol[i]);
         6093  +      }
         6094  +      sqlite3_free(pTab->azlCol);
         6095  +    }
         6096  +    sqlite3_free(pTab);
         6097  +  }
         6098  +}
         6099  +
         6100  +static RecoverTable *recoverNewTable(
         6101  +  ShellState *pState, 
         6102  +  int *pRc,
         6103  +  int iRoot,
         6104  +  int nCol
         6105  +){
         6106  +  RecoverTable *pRet = 0;
         6107  +
         6108  +  pRet = (RecoverTable*)shellMalloc(pRc, sizeof(RecoverTable));
         6109  +  if( pRet ){
         6110  +    sqlite3_stmt *pStmt = 0;
         6111  +    pRet->zName = shellMPrintf(pRc, "orphan_%d_%d", nCol, iRoot);
         6112  +    pRet->zQuoted = shellMPrintf(pRc, "%Q", pRet->zName);
         6113  +    pRet->azlCol = (char**)shellMalloc(pRc, sizeof(char*) * nCol);
         6114  +    pRet->nCol = nCol;
         6115  +
         6116  +    shellPreparePrintf(pState->db, pRc, &pStmt, 
         6117  +      "WITH s(i) AS ("
         6118  +      "  SELECT 1 UNION ALL SELECT i+1 FROM s WHERE i<%d"
         6119  +      ")"
         6120  +      "SELECT i-1, group_concat('c' || i, ', ') OVER (ORDER BY i) FROM s",
         6121  +      nCol
         6122  +    );
         6123  +    while( *pRc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pStmt) ){
         6124  +      int idx = sqlite3_column_int(pStmt, 0);
         6125  +      const char *zText = (const char*)sqlite3_column_text(pStmt, 1);
         6126  +      pRet->azlCol[idx] = shellMPrintf(pRc, "%s", zText);
         6127  +    }
         6128  +    shellFinalize(pRc, pStmt);
         6129  +
         6130  +    pRet->zCreate = shellMPrintf(pRc, "CREATE TABLE %Q (id, %s)", 
         6131  +        pRet->zName, pRet->azlCol[nCol-1]
         6132  +    );
         6133  +  }
         6134  +
         6135  +  if( *pRc!=SQLITE_OK ){
         6136  +    recoverFreeTable(pRet);
         6137  +    pRet = 0;
         6138  +  }
         6139  +
         6140  +  return pRet;
         6141  +}
         6142  +
         6143  +/*
         6144  +** This function is called to recover data from the database. A script
         6145  +** to construct a new database containing all recovered data is output
         6146  +** on stream pState->out.
         6147  +*/
         6148  +static int recoverDatabaseCmd(ShellState *pState){
         6149  +  const char *zSql;
         6150  +  int rc = SQLITE_OK;
         6151  +  sqlite3_stmt *pLoop = 0;        /* Loop through all root pages */
         6152  +
         6153  +  shellExec(pState->db, &rc, 
         6154  +    /* Attach an in-memory database named 'recovery'. Create an indexed 
         6155  +    ** cache of the sqlite_dbptr virtual table. */
         6156  +    "ATTACH '' AS recovery;"
         6157  +    "CREATE TABLE recovery.dbptr("
         6158  +    "      pgno, child, PRIMARY KEY(child, pgno)"
         6159  +    ") WITHOUT ROWID;"
         6160  +    "INSERT OR IGNORE INTO dbptr(pgno, child) SELECT * FROM sqlite_dbptr;"
         6161  +
         6162  +    /* Delete any pointer to page 1. This ensures that page 1 is considered
         6163  +    ** a root page, regardless of how corrupt the db is. */
         6164  +    "DELETE FROM recovery.dbptr WHERE child = 1;"
         6165  +
         6166  +    /* Delete all pointers to any pages that have more than one pointer
         6167  +    ** to them. Such pages will be treated as root pages when recovering
         6168  +    ** data.  */
         6169  +    "DELETE FROM recovery.dbptr WHERE child IN ("
         6170  +    "  SELECT child FROM recovery.dbptr GROUP BY child HAVING count(*)>1"
         6171  +    ");"
         6172  +
         6173  +    /* Create the "map" table that will (eventually) contain instructions
         6174  +    ** for dealing with each page in the db that contains one or more 
         6175  +    ** records. */
         6176  +    "CREATE TABLE recovery.map(pgno INTEGER PRIMARY KEY, maxlen INT, root INT);"
         6177  +
         6178  +    /* Populate table [map]. If there are circular loops of pages in the
         6179  +    ** database, the following adds all pages in such a loop to the map
         6180  +    ** as individual root pages. This could be handled better.  */
         6181  +    "WITH pages(i, maxlen) AS ("
         6182  +    "  SELECT page_count, max(field+1) "
         6183  +    "      FROM pragma_page_count, sqlite_dbdata WHERE pgno=page_count"
         6184  +    "    UNION ALL"
         6185  +    "  SELECT * FROM (SELECT i-1, max(field+1)"
         6186  +    "      FROM pages, sqlite_dbdata WHERE pgno=i-1 AND i>=2)"
         6187  +    ")"
         6188  +    "INSERT INTO recovery.map(pgno, maxlen, root) SELECT i, maxlen, ("
         6189  +    "    WITH p(orig, pgno, parent) AS ("
         6190  +    "      SELECT 0, i, (SELECT pgno FROM recovery.dbptr WHERE child=i)"
         6191  +    "        UNION ALL"
         6192  +    "      SELECT i, p.parent, "
         6193  +    "        (SELECT pgno FROM recovery.dbptr WHERE child=p.parent) FROM p"
         6194  +    "    )"
         6195  +    "    SELECT pgno FROM p WHERE (parent IS NULL OR pgno = orig)"
         6196  +    ") "
         6197  +    "FROM pages WHERE maxlen > 0;"
         6198  +
         6199  +    /* Extract data from page 1 and any linked pages into table
         6200  +    ** recovery.schema. With the same schema as an sqlite_master table.  */
         6201  +    "CREATE TABLE recovery.schema(type, name, tbl_name, rootpage, sql);"
         6202  +    "INSERT INTO recovery.schema SELECT "
         6203  +    "  max(CASE WHEN field=0 THEN value ELSE NULL END),"
         6204  +    "  max(CASE WHEN field=1 THEN value ELSE NULL END),"
         6205  +    "  max(CASE WHEN field=2 THEN value ELSE NULL END),"
         6206  +    "  max(CASE WHEN field=3 THEN value ELSE NULL END),"
         6207  +    "  max(CASE WHEN field=4 THEN value ELSE NULL END)"
         6208  +    "FROM sqlite_dbdata WHERE pgno IN ("
         6209  +    "  SELECT pgno FROM recovery.map WHERE root=1"
         6210  +    ")"
         6211  +    "GROUP BY pgno, cell;"
         6212  +  );
         6213  +
         6214  +#if 0
         6215  +  zSql = "SELECT type ||','|| name ||','|| tbl_name ||','|| rootpage ||','|| sql FROM recovery.schema;";
         6216  +  shellPrepare(pState->db, &rc, zSql, &pLoop);
         6217  +  while( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pLoop) ){
         6218  +    raw_printf(pState->out, "%s\n", (const char*)sqlite3_column_text(pLoop, 0));
         6219  +  }
         6220  +  shellFinalize(&rc, pLoop);
         6221  +  return rc;
         6222  +#endif
         6223  +
         6224  +  /* Loop through each root page. */
         6225  +  zSql = "SELECT root,max(maxlen) FROM recovery.map WHERE root>1 GROUP BY root";
         6226  +  shellPrepare(pState->db, &rc, zSql, &pLoop);
         6227  +  while( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pLoop) ){
         6228  +    int iRoot = sqlite3_column_int(pLoop, 0);
         6229  +    int nCol = sqlite3_column_int(pLoop, 1);
         6230  +    RecoverTable *pTab;
         6231  +
         6232  +    pTab = recoverNewTable(pState, &rc, iRoot, nCol);
         6233  +    if( pTab ){
         6234  +      sqlite3_stmt *pData = 0;
         6235  +      raw_printf(pState->out, "%s;\n", pTab->zCreate);
         6236  +      shellPreparePrintf(pState->db, &rc, &pData, 
         6237  +        "SELECT max(field), group_concat(quote(value), ', ') "
         6238  +        "FROM sqlite_dbdata WHERE pgno IN ("
         6239  +        "  SELECT pgno FROM recovery.map WHERE root=%d"
         6240  +        ")"
         6241  +        "GROUP BY pgno, cell;", iRoot
         6242  +      );
         6243  +      while( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pData) ){
         6244  +        int iMax = sqlite3_column_int(pData, 0);
         6245  +        const char *zVal = (const char*)sqlite3_column_text(pData, 1);
         6246  +        if( iMax+1==pTab->nCol ){
         6247  +          raw_printf(pState->out, "INSERT INTO %s VALUES( %s );\n", 
         6248  +              pTab->zQuoted, zVal);
         6249  +        }else{
         6250  +          raw_printf(pState->out, "INSERT INTO %s(%s) VALUES( %s );\n", 
         6251  +              pTab->zQuoted, pTab->azlCol[iMax], zVal
         6252  +          );
         6253  +        }
         6254  +      }
         6255  +      shellFinalize(&rc, pData);
         6256  +    }
         6257  +    recoverFreeTable(pTab);
         6258  +  }
         6259  +  shellFinalize(&rc, pLoop);
         6260  +
         6261  +  sqlite3_exec(pState->db, "DETACH recovery", 0, 0, 0);
         6262  +  return rc;
         6263  +}
         6264  +
  6029   6265   
  6030   6266   /*
  6031   6267   ** If an input line begins with "." then invoke this routine to
  6032   6268   ** process that line.
  6033   6269   **
  6034   6270   ** Return 1 on error, 2 to exit, and 0 otherwise.
  6035   6271   */
................................................................................
  6308   6544         utf8_printf(stderr, "Enter \".dbconfig\" with no arguments for a list\n");
  6309   6545       }   
  6310   6546     }else
  6311   6547   
  6312   6548     if( c=='d' && n>=3 && strncmp(azArg[0], "dbinfo", n)==0 ){
  6313   6549       rc = shell_dbinfo_command(p, nArg, azArg);
  6314   6550     }else
         6551  +
         6552  +  if( c=='r' && strncmp(azArg[0], "recover", n)==0 ){
         6553  +    open_db(p, 0);
         6554  +    rc = recoverDatabaseCmd(p);
         6555  +  }else
  6315   6556   
  6316   6557     if( c=='d' && strncmp(azArg[0], "dump", n)==0 ){
  6317   6558       const char *zLike = 0;
  6318   6559       int i;
  6319   6560       int savedShowHeader = p->showHeader;
  6320   6561       int savedShellFlags = p->shellFlgs;
  6321   6562       ShellClearFlag(p, SHFLG_PreserveRowid|SHFLG_Newlines|SHFLG_Echo);
................................................................................
  6346   6587                              "?--newlines? ?LIKE-PATTERN?\n");
  6347   6588           rc = 1;
  6348   6589           goto meta_command_exit;
  6349   6590         }else{
  6350   6591           zLike = azArg[i];
  6351   6592         }
  6352   6593       }
         6594  +
  6353   6595       open_db(p, 0);
         6596  +
  6354   6597       /* When playing back a "dump", the content might appear in an order
  6355   6598       ** which causes immediate foreign key constraints to be violated.
  6356   6599       ** So disable foreign-key constraint enforcement to prevent problems. */
  6357   6600       raw_printf(p->out, "PRAGMA foreign_keys=OFF;\n");
  6358   6601       raw_printf(p->out, "BEGIN TRANSACTION;\n");
  6359   6602       p->writableSchema = 0;
  6360   6603       p->showHeader = 0;
................................................................................
  6361   6604       /* Set writable_schema=ON since doing so forces SQLite to initialize
  6362   6605       ** as much of the schema as it can even if the sqlite_master table is
  6363   6606       ** corrupt. */
  6364   6607       sqlite3_exec(p->db, "SAVEPOINT dump; PRAGMA writable_schema=ON", 0, 0, 0);
  6365   6608       p->nErr = 0;
  6366   6609       if( zLike==0 ){
  6367   6610         run_schema_dump_query(p,
  6368         -        "SELECT name, type, sql FROM sqlite_master "
  6369         -        "WHERE sql NOT NULL AND type=='table' AND name!='sqlite_sequence'"
  6370         -      );
         6611  +          "SELECT name, type, sql FROM sqlite_master "
         6612  +          "WHERE sql NOT NULL AND type=='table' AND name!='sqlite_sequence'"
         6613  +          );
  6371   6614         run_schema_dump_query(p,
  6372         -        "SELECT name, type, sql FROM sqlite_master "
  6373         -        "WHERE name=='sqlite_sequence'"
  6374         -      );
         6615  +          "SELECT name, type, sql FROM sqlite_master "
         6616  +          "WHERE name=='sqlite_sequence'"
         6617  +          );
  6375   6618         run_table_dump_query(p,
  6376         -        "SELECT sql FROM sqlite_master "
  6377         -        "WHERE sql NOT NULL AND type IN ('index','trigger','view')", 0
  6378         -      );
         6619  +          "SELECT sql FROM sqlite_master "
         6620  +          "WHERE sql NOT NULL AND type IN ('index','trigger','view')", 0
         6621  +          );
  6379   6622       }else{
  6380   6623         char *zSql;
  6381   6624         zSql = sqlite3_mprintf(
  6382         -        "SELECT name, type, sql FROM sqlite_master "
  6383         -        "WHERE tbl_name LIKE %Q AND type=='table'"
  6384         -        "  AND sql NOT NULL", zLike);
         6625  +          "SELECT name, type, sql FROM sqlite_master "
         6626  +          "WHERE tbl_name LIKE %Q AND type=='table'"
         6627  +          "  AND sql NOT NULL", zLike);
  6385   6628         run_schema_dump_query(p,zSql);
  6386   6629         sqlite3_free(zSql);
  6387   6630         zSql = sqlite3_mprintf(
  6388         -        "SELECT sql FROM sqlite_master "
  6389         -        "WHERE sql NOT NULL"
  6390         -        "  AND type IN ('index','trigger','view')"
  6391         -        "  AND tbl_name LIKE %Q", zLike);
         6631  +          "SELECT sql FROM sqlite_master "
         6632  +          "WHERE sql NOT NULL"
         6633  +          "  AND type IN ('index','trigger','view')"
         6634  +          "  AND tbl_name LIKE %Q", zLike);
  6392   6635         run_table_dump_query(p, zSql, 0);
  6393   6636         sqlite3_free(zSql);
  6394   6637       }
  6395   6638       if( p->writableSchema ){
  6396   6639         raw_printf(p->out, "PRAGMA writable_schema=OFF;\n");
  6397   6640         p->writableSchema = 0;
  6398   6641       }
  6399   6642       sqlite3_exec(p->db, "PRAGMA writable_schema=OFF;", 0, 0, 0);
  6400   6643       sqlite3_exec(p->db, "RELEASE dump;", 0, 0, 0);
  6401         -    raw_printf(p->out, p->nErr ? "ROLLBACK; -- due to errors\n" : "COMMIT;\n");
         6644  +    raw_printf(p->out, p->nErr?"ROLLBACK; -- due to errors\n":"COMMIT;\n");
  6402   6645       p->showHeader = savedShowHeader;
  6403   6646       p->shellFlgs = savedShellFlags;
  6404   6647     }else
  6405   6648   
  6406   6649     if( c=='e' && strncmp(azArg[0], "echo", n)==0 ){
  6407   6650       if( nArg==2 ){
  6408   6651         setOrClearFlag(p, SHFLG_Echo, azArg[1]);

Changes to tool/mkshellc.tcl.

    36     36       if {[info exists typedef_seen($line)]} {
    37     37         return "/* $line */"
    38     38       }
    39     39       set typedef_seen($line) 1
    40     40     }
    41     41     return $line
    42     42   }
           43  +set iLine 0
    43     44   while {1} {
    44     45     set lx [omit_redundant_typedefs [gets $in]]
    45     46     if {[eof $in]} break;
           47  +  incr iLine
    46     48     if {[regexp {^INCLUDE } $lx]} {
    47     49       set cfile [lindex $lx 1]
    48     50       puts $out "/************************* Begin $cfile ******************/"
           51  +    puts $out "#line 1 \"$cfile\""
    49     52       set in2 [open $topdir/src/$cfile rb]
    50     53       while {![eof $in2]} {
    51     54         set lx [omit_redundant_typedefs [gets $in2]]
    52         -      if {[regexp {^#include "sqlite} $lx]} continue
           55  +      if {[regexp {^#include "sqlite} $lx]} {
           56  +        set lx "/* $lx */"
           57  +      }
    53     58         if {[regexp {^# *include "test_windirent.h"} $lx]} {
    54     59           set lx "/* $lx */"
    55     60         }
    56     61         set lx [string map [list __declspec(dllexport) {}] $lx]
    57     62         puts $out $lx
    58     63       }
    59     64       close $in2
    60     65       puts $out "/************************* End $cfile ********************/"
           66  +    puts $out "#line [expr $iLine+1] \"shell.c.in\""
    61     67       continue
    62     68     }
    63     69     puts $out $lx
    64     70   }
    65     71   close $in
    66     72   close $out