/ Check-in [7221f6e3]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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

Overview
Comment:Have .recover store all orphaned rows in a single table, with extra columns to indicate the orphaned page and sub-tree they were discovered within.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | dbdata
Files: files | file ages | folders
SHA3-256: 7221f6e33ed6a5a96ec61e25f2a1f70b84aae66e503d897eb7b7ff1aec42355d
User & Date: dan 2019-04-26 21:11:37
Context
2019-04-27
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
2019-04-26
21:11
Have .recover store all orphaned rows in a single table, with extra columns to indicate the orphaned page and sub-tree they were discovered within. check-in: 7221f6e3 user: dan tags: dbdata
15:40
Fix another problem with database freelist handling in the ".recover" command. check-in: bee2652a user: dan tags: dbdata
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/shell.c.in.

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
....
6281
6282
6283
6284
6285
6286
6287

6288
6289
6290


6291
6292
6293
6294
6295
6296
6297
6298
6299

6300
6301
6302
6303
6304
6305
6306
6307
6308
6309



6310
6311
6312
6313
6314
6315
6316
6317
6318
6319
6320
6321

6322
6323
6324
6325
6326
6327
6328
6329
6330
6331





6332
6333
6334
6335
6336
6337
6338
6339

6340
6341
6342
6343
6344
6345
6346
6347
6348
6349
6350
6351
6352
6353
6354
6355
....
6356
6357
6358
6359
6360
6361
6362
6363
6364
6365
6366
6367
6368
6369
6370
6371
6372
6373
6374
6375
6376
6377
6378
6379
6380
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
....
6541
6542
6543
6544
6545
6546
6547

6548
6549
6550
6551
6552
6553
6554
....
6561
6562
6563
6564
6565
6566
6567














6568
6569
6570
6571
6572
6573
6574
....
6582
6583
6584
6585
6586
6587
6588

6589
6590
6591

6592

6593
6594
6595
6596
6597

6598

6599
6600
6601
6602









6603
6604
6605
6606

6607
6608
6609
6610
6611
6612
6613
6614
6615

6616
6617
6618
6619
6620
6621
6622
*/
static void recoverFreeTable(RecoverTable *pTab){
  if( pTab ){
    sqlite3_free(pTab->zName);
    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);
  }
}

static void recoverOldTable(
  int *pRc,                       /* IN/OUT: Error code */
  RecoverTable *pTab,
  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 */
  int rc = *pRc;



  if( rc==SQLITE_OK ){
    int nSqlCol = 0;
    int bSqlIntkey = 0;
    sqlite3_stmt *pStmt = 0;

    rc = sqlite3_open("", &dbtmp);
    if( rc==SQLITE_OK ){
      rc = sqlite3_exec(dbtmp, "PRAGMA writable_schema = on", 0, 0, 0);
    }
    if( rc==SQLITE_OK ){
      rc = sqlite3_exec(dbtmp, zSql, 0, 0, 0);
      if( rc==SQLITE_ERROR ){
................................................................................
    );
    if( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pStmt) ){
      bSqlIntkey = sqlite3_column_int(pStmt, 0);
    }
    shellFinalize(&rc, pStmt);

    if( bIntkey==bSqlIntkey ){

      const char *zPk = "_rowid_";
      sqlite3_stmt *pPkFinder = 0;



      shellPreparePrintf(dbtmp, &rc, &pPkFinder, 
          "SELECT cid, name FROM pragma_table_info(%Q) "
          "  WHERE pk=1 AND type='integer' COLLATE nocase"
          "  AND NOT EXISTS (SELECT cid FROM pragma_table_info(%Q) WHERE pk=2)",
          zName, zName
      );
      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->zName = shellMPrintf(&rc, "%s", zName);
      pTab->zQuoted = shellMPrintf(&rc, "%Q", pTab->zName);
      pTab->azlCol = (char**)shellMalloc(&rc, sizeof(char*) * nSqlCol);
      pTab->nCol = nSqlCol;

      if( nSqlCol==1 && pTab->iPk==0 ){
        pTab->azlCol[0] = shellMPrintf(&rc, "%Q", zPk);
      }else{



        shellPreparePrintf(dbtmp, &rc, &pStmt, 
          "SELECT -1+row_number()          OVER (ORDER BY cid),"
          "    %Q||%Q||group_concat(name, ', ') FILTER (WHERE cid!=%d) "
          "           OVER (ORDER BY cid) "
          "FROM pragma_table_info(%Q)", 
          (bIntkey ? zPk : ""), (bIntkey ? ", " : ""), 
          pTab->iPk, zName
        );
        while( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pStmt) ){
          int idx = sqlite3_column_int(pStmt, 0);
          const char *zText = (const char*)sqlite3_column_text(pStmt, 1);
          pTab->azlCol[idx] = shellMPrintf(&rc, "%s", zText);

        }
        shellFinalize(&rc, pStmt);
      }
      shellFinalize(&rc, pPkFinder);
    }
  }

 finished:
  sqlite3_close(dbtmp);
  *pRc = rc;





}

static RecoverTable *recoverNewTable(
  ShellState *pState, 
  int *pRc,
  int iRoot,
  int bIntkey,
  int nCol

){
  sqlite3_stmt *pStmt = 0;
  RecoverTable *pRet = 0;
  int bNoop = 0;
  const char *zSql = 0;
  const char *zName = 0;

  pRet = (RecoverTable*)shellMalloc(pRc, sizeof(RecoverTable));
  if( pRet ) pRet->iPk = -2;

  /* 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);
      recoverOldTable(pRc, pRet, zName, zSql, bIntkey, nCol);
      break;
    }
  }
  shellFinalize(pRc, pStmt);
  if( bNoop ){
    sqlite3_free(pRet);
    return 0;
  }

  if( pRet && pRet->zName==0 ){
    sqlite3_stmt *pStmt = 0;

    pRet->zName = shellMPrintf(pRc, "orphan_%d_%d", nCol, iRoot);
    pRet->zQuoted = shellMPrintf(pRc, "%Q", pRet->zName);
    pRet->azlCol = (char**)shellMalloc(pRc, sizeof(char*) * nCol);
    pRet->nCol = nCol;

    shellPreparePrintf(pState->db, pRc, &pStmt, 
      "WITH s(i) AS ("
      "  SELECT 1 UNION ALL SELECT i+1 FROM s WHERE i<%d"
      ")"
      "SELECT i-1, %Q || group_concat('c' || i, ', ') OVER (ORDER BY i) FROM s",
      nCol, (bIntkey ? "id, " : "")
    );
    while( *pRc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pStmt) ){
      int idx = sqlite3_column_int(pStmt, 0);
      const char *zText = (const char*)sqlite3_column_text(pStmt, 1);
      pRet->azlCol[idx] = shellMPrintf(pRc, "%s", zText);
    }
    shellFinalize(pRc, pStmt);

    if( *pRc==SQLITE_OK ){
      char *zCreate = shellMPrintf(pRc, "CREATE TABLE %Q (%s)", 
        pRet->zName, pRet->azlCol[nCol-1]
      );
      if( zCreate ){
        raw_printf(pState->out, "%s;\n", zCreate);
        sqlite3_free(zCreate);
      }
    }
  }

  if( *pRc!=SQLITE_OK ){
    recoverFreeTable(pRet);
    pRet = 0;
  }

  return pRet;

}

/*
** 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 */
  int i;



  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);
................................................................................
    "  max(CASE WHEN field=2 THEN value ELSE NULL END),"
    "  max(CASE WHEN field=3 THEN value ELSE NULL END),"
    "  max(CASE WHEN field=4 THEN value ELSE NULL END)"
    "FROM sqlite_dbdata WHERE pgno IN ("
    "  SELECT pgno FROM recovery.map WHERE root=1"
    ")"
    "GROUP BY pgno, cell;"

  );

  /* Open a transaction, then print out all non-virtual, non-"sqlite_%" 
  ** CREATE TABLE statements that extracted from the existing schema.  */
  if( rc==SQLITE_OK ){
    sqlite3_stmt *pStmt = 0;
    raw_printf(pState->out, "BEGIN;\n");
................................................................................
      const char *zCreateTable = (const char*)sqlite3_column_text(pStmt, 0);
      raw_printf(pState->out, "CREATE TABLE IF NOT EXISTS %s;\n", 
          &zCreateTable[12]
      );
    }
    shellFinalize(&rc, pStmt);
  }















  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 != ?"
................................................................................
      "  SELECT rootpage FROM recovery.schema WHERE name='sqlite_sequence'"
      ")", &pLoop
  );
  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);

    RecoverTable *pTab;

    pTab = recoverNewTable(pState, &rc, iRoot, bIntkey, nCol);

    if( pTab ){

      if( 0==sqlite3_stricmp(pTab->zName, "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) ){

        sqlite3_bind_int(pCells, 1, sqlite3_column_int(pPages, 0));
        while( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pCells) ){
          int iMax = sqlite3_column_int(pCells, 0);
          const char *zVal = (const char*)sqlite3_column_text(pCells, 1);









          raw_printf(pState->out, "INSERT INTO %s(%s) VALUES( %s );\n", 
              pTab->zQuoted, pTab->azlCol[iMax>0?iMax:0], zVal
          );
        }

        shellReset(&rc, pCells);
      }
      shellReset(&rc, pPages);
    }
    recoverFreeTable(pTab);
  }
  shellFinalize(&rc, pLoop);
  shellFinalize(&rc, pPages);
  shellFinalize(&rc, pCells);


  /* The rest of the schema */
  if( rc==SQLITE_OK ){
    sqlite3_stmt *pStmt = 0;
    shellPrepare(pState->db, &rc, 
        "SELECT sql, name FROM recovery.schema "
        "WHERE sql NOT LIKE 'create table%'", &pStmt







|








|

<







>

>




|







 







>



>
>
|


|
|
|
|
|
|
>




|


|


>
>
>
|
<
|
|

<
|
|
|
<
|
|
>
|
|
|







>
>
>
>
>







|
>







<
<







 







|



|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
>







 







>
>







 







>







 







>
>
>
>
>
>
>
>
>
>
>
>
>
>







 







>


|
>
|
>
|
|
|
|
|
>
|
>
|
|
|
|
>
>
>
>
>
>
>
>
>

|


>
|
|
|
<
|




>







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
....
6282
6283
6284
6285
6286
6287
6288
6289
6290
6291
6292
6293
6294
6295
6296
6297
6298
6299
6300
6301
6302
6303
6304
6305
6306
6307
6308
6309
6310
6311
6312
6313
6314
6315
6316
6317
6318

6319
6320
6321

6322
6323
6324

6325
6326
6327
6328
6329
6330
6331
6332
6333
6334
6335
6336
6337
6338
6339
6340
6341
6342
6343
6344
6345
6346
6347
6348
6349
6350
6351
6352
6353
6354
6355
6356
6357
6358


6359
6360
6361
6362
6363
6364
6365
....
6366
6367
6368
6369
6370
6371
6372
6373
6374
6375
6376
6377
6378
6379
6380
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
....
6554
6555
6556
6557
6558
6559
6560
6561
6562
6563
6564
6565
6566
6567
6568
....
6575
6576
6577
6578
6579
6580
6581
6582
6583
6584
6585
6586
6587
6588
6589
6590
6591
6592
6593
6594
6595
6596
6597
6598
6599
6600
6601
6602
....
6610
6611
6612
6613
6614
6615
6616
6617
6618
6619
6620
6621
6622
6623
6624
6625
6626
6627
6628
6629
6630
6631
6632
6633
6634
6635
6636
6637
6638
6639
6640
6641
6642
6643
6644
6645
6646
6647
6648
6649
6650
6651
6652

6653
6654
6655
6656
6657
6658
6659
6660
6661
6662
6663
6664
6665
*/
static void recoverFreeTable(RecoverTable *pTab){
  if( pTab ){
    sqlite3_free(pTab->zName);
    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);
  }
}

static RecoverTable *recoverOldTable(
  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 */
  int rc = *pRc;
  RecoverTable *pTab = 0;

  pTab = (RecoverTable*)shellMalloc(&rc, sizeof(RecoverTable));
  if( rc==SQLITE_OK ){
    int nSqlCol = 0;
    int bSqlIntkey = 0;
    sqlite3_stmt *pStmt = 0;
    
    rc = sqlite3_open("", &dbtmp);
    if( rc==SQLITE_OK ){
      rc = sqlite3_exec(dbtmp, "PRAGMA writable_schema = on", 0, 0, 0);
    }
    if( rc==SQLITE_OK ){
      rc = sqlite3_exec(dbtmp, zSql, 0, 0, 0);
      if( rc==SQLITE_ERROR ){
................................................................................
    );
    if( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pStmt) ){
      bSqlIntkey = sqlite3_column_int(pStmt, 0);
    }
    shellFinalize(&rc, pStmt);

    if( bIntkey==bSqlIntkey ){
      int i;
      const char *zPk = "_rowid_";
      sqlite3_stmt *pPkFinder = 0;

      pTab->iPk = -2;
      if( bIntkey ){
        shellPreparePrintf(dbtmp, &rc, &pPkFinder, 
          "SELECT cid, name FROM pragma_table_info(%Q) "
          "  WHERE pk=1 AND type='integer' COLLATE nocase"
          "  AND NOT EXISTS (SELECT cid FROM pragma_table_info(%Q) WHERE pk=2)"
          , zName, zName
        );
        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->zName = shellMPrintf(&rc, "%s", zName);
      pTab->zQuoted = shellMPrintf(&rc, "%Q", pTab->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, "");
      }
      i = 1;
      shellPreparePrintf(dbtmp, &rc, &pStmt, 

          "SELECT %Q || group_concat(name, ', ') "
          "  FILTER (WHERE cid!=%d) OVER (ORDER BY cid) "
          "FROM pragma_table_info(%Q)", 

          bIntkey ? ", " : "", pTab->iPk, zName
      );
      while( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pStmt) ){

        const char *zText = (const char*)sqlite3_column_text(pStmt, 0);
        pTab->azlCol[i] = shellMPrintf(&rc, "%s%s", pTab->azlCol[0], zText);
        i++;
      }
      shellFinalize(&rc, pStmt);

      shellFinalize(&rc, pPkFinder);
    }
  }

 finished:
  sqlite3_close(dbtmp);
  *pRc = rc;
  if( rc!=SQLITE_OK ){
    recoverFreeTable(pTab);
    pTab = 0;
  }
  return pTab;
}

static RecoverTable *recoverNewTable(
  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 = recoverOldTable(pRc, zName, zSql, bIntkey, nCol);
      break;
    }
  }

  shellFinalize(pRc, pStmt);
  *pbNoop = bNoop;
  return pRet;
}

static RecoverTable *recoverOrphanTable(
  ShellState *pState, 
  int *pRc, 
  int nCol
){
  RecoverTable *pTab = 0;
  if( nCol>=0 && *pRc==SQLITE_OK ){
    int i;
    raw_printf(pState->out, 
        "CREATE TABLE recover_orphan(rootpgno INTEGER, "
        "pgno INTEGER, nfield INTEGER, id INTEGER"
    );
    for(i=0; i<nCol; i++){
      raw_printf(pState->out, ", c%d", i);
    }
    raw_printf(pState->out, ");\n");

    pTab = (RecoverTable*)shellMalloc(pRc, sizeof(RecoverTable));
    if( pTab ){
      pTab->zName = shellMPrintf(pRc, "%s", "recover_orphan");
      pTab->zQuoted = shellMPrintf(pRc, "%Q", pTab->zName);
      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;
    }
  }
  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 */
  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);
................................................................................
    "  max(CASE WHEN field=2 THEN value ELSE NULL END),"
    "  max(CASE WHEN field=3 THEN value ELSE NULL END),"
    "  max(CASE WHEN field=4 THEN value ELSE NULL END)"
    "FROM sqlite_dbdata WHERE pgno IN ("
    "  SELECT pgno FROM recovery.map WHERE root=1"
    ")"
    "GROUP BY pgno, cell;"
    "CREATE INDEX recovery.schema_rootpage ON schema(rootpage);"
  );

  /* Open a transaction, then print out all non-virtual, non-"sqlite_%" 
  ** CREATE TABLE statements that extracted from the existing schema.  */
  if( rc==SQLITE_OK ){
    sqlite3_stmt *pStmt = 0;
    raw_printf(pState->out, "BEGIN;\n");
................................................................................
      const char *zCreateTable = (const char*)sqlite3_column_text(pStmt, 0);
      raw_printf(pState->out, "CREATE TABLE IF NOT EXISTS %s;\n", 
          &zCreateTable[12]
      );
    }
    shellFinalize(&rc, pStmt);
  }

  /* Figure out if an orphan table will be required. And if so, how many
  ** user columns it should contain */
  shellPrepare(pState->db, &rc, 
      "SELECT coalesce(max(maxlen), -2) FROM recovery.map" 
      "  WHERE root>1 AND root NOT IN (SELECT rootpage FROM recovery.schema)"
      , &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, 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 != ?"
................................................................................
      "  SELECT rootpage FROM recovery.schema WHERE name='sqlite_sequence'"
      ")", &pLoop
  );
  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 = recoverNewTable(pState, &rc, iRoot, bIntkey, nCol, &bNoop);
    if( bNoop || rc ) continue;
    if( pTab==0 ) pTab = pOrphan;

    if( 0==sqlite3_stricmp(pTab->zName, "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);
      sqlite3_bind_int(pCells, 1, iPgno);
      while( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pCells) ){
        int nField = sqlite3_column_int(pCells, 0);
        const char *zVal = (const char*)sqlite3_column_text(pCells, 1);

        nField = nField+1;
        if( pTab==pOrphan ){
          raw_printf(pState->out, 
              "INSERT INTO %s VALUES(%d, %d, %d, %s%s%s);\n",
              pTab->zQuoted, iRoot, iPgno, nField, 
              bIntkey ? "" : "NULL, ", zVal, pTab->azlCol[nField]
          );
        }else{
          raw_printf(pState->out, "INSERT INTO %s(%s) VALUES( %s );\n", 
              pTab->zQuoted, pTab->azlCol[nField], zVal
          );
        }
      }
      shellReset(&rc, pCells);
    }
    shellReset(&rc, pPages);

    if( pTab!=pOrphan ) recoverFreeTable(pTab);
  }
  shellFinalize(&rc, pLoop);
  shellFinalize(&rc, pPages);
  shellFinalize(&rc, pCells);
  recoverFreeTable(pOrphan);

  /* The rest of the schema */
  if( rc==SQLITE_OK ){
    sqlite3_stmt *pStmt = 0;
    shellPrepare(pState->db, &rc, 
        "SELECT sql, name FROM recovery.schema "
        "WHERE sql NOT LIKE 'create table%'", &pStmt