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 |
Timelines: | family | ancestors | descendants | both | dbdata |
Files: | files | file ages | folders |
SHA3-256: |
7221f6e33ed6a5a96ec61e25f2a1f70b |
User & Date: | dan 2019-04-26 21:11:37.615 |
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: 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) | |
15:40 | Fix another problem with database freelist handling in the ".recover" command. (check-in: bee2652ac2 user: dan tags: dbdata) | |
Changes
Changes to src/shell.c.in.
︙ | ︙ | |||
6222 6223 6224 6225 6226 6227 6228 | */ static void recoverFreeTable(RecoverTable *pTab){ if( pTab ){ sqlite3_free(pTab->zName); sqlite3_free(pTab->zQuoted); if( pTab->azlCol ){ int i; | | | < > > | | 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 | */ 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 ){ |
︙ | ︙ | |||
6281 6282 6283 6284 6285 6286 6287 6288 6289 6290 | ); 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; | > > > | | | | | | | > | | > > > | < | | < | | | < | | > | | | > > > > > | > < < | > | < | | | | | | | | | > > > > > > > | | < < < < < < < < < < | < | | | | > > | > | | < > > > | | | | | | | | | > > | 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 | ); 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); |
︙ | ︙ | |||
6541 6542 6543 6544 6545 6546 6547 6548 6549 6550 6551 6552 6553 6554 6555 6556 6557 6558 6559 6560 6561 6562 6563 6564 6565 6566 6567 6568 6569 6570 6571 6572 6573 6574 | " 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"); raw_printf(pState->out, "PRAGMA writable_schema = on;\n"); shellPrepare(pState->db, &rc, "SELECT sql FROM recovery.schema " "WHERE type='table' AND sql LIKE 'create table%'", &pStmt ); while( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pStmt) ){ 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 != ?" | > > > > > > > > > > > > > > > | 6554 6555 6556 6557 6558 6559 6560 6561 6562 6563 6564 6565 6566 6567 6568 6569 6570 6571 6572 6573 6574 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 | " 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"); raw_printf(pState->out, "PRAGMA writable_schema = on;\n"); shellPrepare(pState->db, &rc, "SELECT sql FROM recovery.schema " "WHERE type='table' AND sql LIKE 'create table%'", &pStmt ); while( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pStmt) ){ 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 != ?" |
︙ | ︙ | |||
6582 6583 6584 6585 6586 6587 6588 6589 6590 | " 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; | > | > | > | | | | | > | > | | | | > > > > > > > > > | > | | | < | > | 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 | " 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 |
︙ | ︙ |