/ Check-in [9c458acb]
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:Improve the ".recover" command so that it handles intkey pages linked into non-intkey b-trees, and vice-versa, better.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 9c458acba5a100a76148a3efb78ea9f57b85751e80788e4532694bd8976608a0
User & Date: dan 2019-07-24 20:10:27
Context
2019-07-24
23:15
Fix faulty asserts in the code generator. Ticket [c52b09c7f38903b1] check-in: 01cdc590 user: drh tags: trunk
20:10
Improve the ".recover" command so that it handles intkey pages linked into non-intkey b-trees, and vice-versa, better. check-in: 9c458acb user: dan tags: trunk
19:20
Improve the ".recover" command's handling of corrupt database schemas. check-in: becaaa4d user: dan tags: trunk
Changes
Hide Diffs Unified Diffs Show Whitespace Changes Patch

Changes to src/shell.c.in.

6736
6737
6738
6739
6740
6741
6742

6743
6744
6745
6746
6747
6748
6749
....
6754
6755
6756
6757
6758
6759
6760

6761
6762
6763
6764
6765
6766
6767
....
6775
6776
6777
6778
6779
6780
6781

6782
6783









6784
6785
6786
6787
6788
6789
6790
6791
6792
6793
6794
6795
6796
6797
6798
6799
6800
  pLoop = 0;

  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 != ?"
      "GROUP BY cell", &pCells
  );

  /* Loop through each root page. */
  shellPrepare(pState->db, &rc, 
      "SELECT root, intkey, max(maxlen) FROM recovery.map" 
................................................................................
  while( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pLoop) ){
    int iRoot = sqlite3_column_int(pLoop, 0);
    int bIntkey = sqlite3_column_int(pLoop, 1);
    int nCol = sqlite3_column_int(pLoop, 2);
    int bNoop = 0;
    RecoverTable *pTab;


    pTab = recoverFindTable(pState, &rc, iRoot, bIntkey, nCol, &bNoop);
    if( bNoop || rc ) continue;
    if( pTab==0 ){
      if( pOrphan==0 ){
        pOrphan = recoverOrphanTable(pState, &rc, zLostAndFound, nOrphan);
      }
      pTab = pOrphan;
................................................................................
    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);







>







 







>







 







>


>
>
>
>
>
>
>
>
>

|


|
|



|







6736
6737
6738
6739
6740
6741
6742
6743
6744
6745
6746
6747
6748
6749
6750
....
6755
6756
6757
6758
6759
6760
6761
6762
6763
6764
6765
6766
6767
6768
6769
....
6777
6778
6779
6780
6781
6782
6783
6784
6785
6786
6787
6788
6789
6790
6791
6792
6793
6794
6795
6796
6797
6798
6799
6800
6801
6802
6803
6804
6805
6806
6807
6808
6809
6810
6811
6812
  pLoop = 0;

  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)), ', ')"
      ", min(field) "
      "FROM sqlite_dbdata WHERE pgno = ? AND field != ?"
      "GROUP BY cell", &pCells
  );

  /* Loop through each root page. */
  shellPrepare(pState->db, &rc, 
      "SELECT root, intkey, max(maxlen) FROM recovery.map" 
................................................................................
  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;

    assert( bIntkey==0 || bIntkey==1 );
    pTab = recoverFindTable(pState, &rc, iRoot, bIntkey, nCol, &bNoop);
    if( bNoop || rc ) continue;
    if( pTab==0 ){
      if( pOrphan==0 ){
        pOrphan = recoverOrphanTable(pState, &rc, zLostAndFound, nOrphan);
      }
      pTab = pOrphan;
................................................................................
    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);
        int iMin = sqlite3_column_int(pCells, 2);
        const char *zVal = (const char*)sqlite3_column_text(pCells, 1);

        RecoverTable *pTab2 = pTab;
        if( pTab!=pOrphan && (iMin<0)!=bIntkey ){
          if( pOrphan==0 ){
            pOrphan = recoverOrphanTable(pState, &rc, zLostAndFound, nOrphan);
          }
          pTab2 = pOrphan;
          if( pTab2==0 ) break;
        }

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