/ Check-in [91df4b8e]
Login

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

Overview
Comment: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.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | dbdata
Files: files | file ages | folders
SHA3-256: 91df4b8e0386105d01614921e8410994b621404a3d46ec4af8687b8743c52d52
User & Date: dan 2019-04-27 15:35:45
Context
2019-04-27
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
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
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/shell.c.in.

  6286   6286       shellFinalize(&rc, pStmt);
  6287   6287   
  6288   6288       if( bIntkey==bSqlIntkey ){
  6289   6289         int i;
  6290   6290         const char *zPk = "_rowid_";
  6291   6291         sqlite3_stmt *pPkFinder = 0;
  6292   6292   
         6293  +      /* If this is an intkey table and there is an INTEGER PRIMARY KEY,
         6294  +      ** set zPk to the name of the PK column, and pTab->iPk to the index
         6295  +      ** of the column, where columns are 0-numbered from left to right.
         6296  +      ** Or, if this is a WITHOUT ROWID table or if there is no IPK column,
         6297  +      ** leave zPk as "_rowid_" and pTab->iPk at -2.  */
  6293   6298         pTab->iPk = -2;
  6294   6299         if( bIntkey ){
  6295   6300           shellPreparePrintf(dbtmp, &rc, &pPkFinder, 
  6296   6301             "SELECT cid, name FROM pragma_table_info(%Q) "
  6297   6302             "  WHERE pk=1 AND type='integer' COLLATE nocase"
  6298   6303             "  AND NOT EXISTS (SELECT cid FROM pragma_table_info(%Q) WHERE pk=2)"
  6299   6304             , zName, zName
................................................................................
  6313   6318           pTab->azlCol[0] = shellMPrintf(&rc, "%Q", zPk);
  6314   6319         }else{
  6315   6320           pTab->azlCol[0] = shellMPrintf(&rc, "");
  6316   6321         }
  6317   6322         i = 1;
  6318   6323         shellPreparePrintf(dbtmp, &rc, &pStmt, 
  6319   6324             "SELECT %Q || group_concat(name, ', ') "
  6320         -          "  FILTER (WHERE cid!=%d) OVER (ORDER BY cid) "
         6325  +          "  FILTER (WHERE cid!=%d) OVER (ORDER BY %s cid) "
  6321   6326             "FROM pragma_table_info(%Q)", 
  6322         -          bIntkey ? ", " : "", pTab->iPk, zName
         6327  +          bIntkey ? ", " : "", pTab->iPk, 
         6328  +          bIntkey ? "" : "(CASE WHEN pk=0 THEN 1000000 ELSE pk END), ",
         6329  +          zName
  6323   6330         );
  6324   6331         while( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pStmt) ){
  6325   6332           const char *zText = (const char*)sqlite3_column_text(pStmt, 0);
  6326   6333           pTab->azlCol[i] = shellMPrintf(&rc, "%s%s", pTab->azlCol[0], zText);
  6327   6334           i++;
  6328   6335         }
  6329   6336         shellFinalize(&rc, pStmt);

Changes to test/recover.test.

    80     80     CREATE TABLE t3(i INTEGER PRIMARY KEY AUTOINCREMENT, b, c);
    81     81     INSERT INTO t3 VALUES(NULL, 1, 2);
    82     82     INSERT INTO t3 VALUES(NULL, 3, 4);
    83     83     INSERT INTO t3 VALUES(NULL, 5, 6);
    84     84     DELETE FROM t2;
    85     85   "
    86     86   do_recover_test 1.3.2
           87  +
           88  +#-------------------------------------------------------------------------
           89  +reset_db
           90  +do_execsql_test 2.1.0 {
           91  +  CREATE TABLE t1(a, b, c, PRIMARY KEY(b, c)) WITHOUT ROWID;
           92  +  INSERT INTO t1 VALUES(1, 2, 3);
           93  +  INSERT INTO t1 VALUES(4, 5, 6);
           94  +  INSERT INTO t1 VALUES(7, 8, 9);
           95  +}
           96  +
           97  +do_recover_test 2.1.1
    87     98   
    88     99   finish_test