SQLite

Check-in [91df4b8e03]
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
Timelines: family | ancestors | descendants | both | dbdata
Files: files | file ages | folders
SHA3-256: 91df4b8e0386105d01614921e8410994b621404a3d46ec4af8687b8743c52d52
User & Date: dan 2019-04-27 15:35:45.828
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: 67bb88e24c 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: 91df4b8e03 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: 7221f6e33e user: dan tags: dbdata)
Changes
Side-by-Side Diff Ignore Whitespace Patch
Changes to src/shell.c.in.
︙
6286
6287
6288
6289
6290
6291
6292





6293
6294
6295
6296
6297
6298
6299

6286
6287
6288
6289
6290
6291
6292
6293
6294
6295
6296
6297
6298
6299
6300
6301
6302
6303
6304








+
+
+
+
+








    shellFinalize(&rc, pStmt);

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

      /* If this is an intkey table and there is an INTEGER PRIMARY KEY,
      ** set zPk to the name of the PK column, and pTab->iPk to the index
      ** of the column, where columns are 0-numbered from left to right.
      ** Or, if this is a WITHOUT ROWID table or if there is no IPK column,
      ** leave zPk as "_rowid_" and pTab->iPk at -2.  */
      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
︙
6313
6314
6315
6316
6317
6318
6319
6320

6321
6322



6323
6324
6325
6326
6327
6328
6329

6318
6319
6320
6321
6322
6323
6324

6325
6326

6327
6328
6329
6330
6331
6332
6333
6334
6335
6336








-
+

-
+
+
+








        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) "
          "  FILTER (WHERE cid!=%d) OVER (ORDER BY %s cid) "
          "FROM pragma_table_info(%Q)", 
          bIntkey ? ", " : "", pTab->iPk, zName
          bIntkey ? ", " : "", pTab->iPk, 
          bIntkey ? "" : "(CASE WHEN pk=0 THEN 1000000 ELSE pk END), ",
          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);
︙
Changes to test/recover.test.
︙
80
81
82
83
84
85
86











87
88

80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99








+
+
+
+
+
+
+
+
+
+
+



  CREATE TABLE t3(i INTEGER PRIMARY KEY AUTOINCREMENT, b, c);
  INSERT INTO t3 VALUES(NULL, 1, 2);
  INSERT INTO t3 VALUES(NULL, 3, 4);
  INSERT INTO t3 VALUES(NULL, 5, 6);
  DELETE FROM t2;
"
do_recover_test 1.3.2

#-------------------------------------------------------------------------
reset_db
do_execsql_test 2.1.0 {
  CREATE TABLE t1(a, b, c, PRIMARY KEY(b, c)) WITHOUT ROWID;
  INSERT INTO t1 VALUES(1, 2, 3);
  INSERT INTO t1 VALUES(4, 5, 6);
  INSERT INTO t1 VALUES(7, 8, 9);
}

do_recover_test 2.1.1

finish_test