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: |
91df4b8e0386105d01614921e8410994 |
| 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
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