15821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)/* 25821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)** 2003 April 6 35821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)** 45821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)** The author disclaims copyright to this source code. In place of 55821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)** a legal notice, here is a blessing: 65821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)** 75821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)** May you do good and not evil. 85821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)** May you find forgiveness for yourself and forgive others. 95821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)** May you share freely, never taking more than you give. 105821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)** 115821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)************************************************************************* 125821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)** This file contains code used to implement the VACUUM command. 135821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)** 145821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)** Most of the code in this file may be omitted by defining the 155821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)** SQLITE_OMIT_VACUUM macro. 165821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)*/ 175821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)#include "sqliteInt.h" 185821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)#include "vdbeInt.h" 195821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 205821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)#if !defined(SQLITE_OMIT_VACUUM) && !defined(SQLITE_OMIT_ATTACH) 215821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)/* 225821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)** Finalize a prepared statement. If there was an error, store the 235821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)** text of the error message in *pzErrMsg. Return the result code. 245821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)*/ 255821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)static int vacuumFinalize(sqlite3 *db, sqlite3_stmt *pStmt, char **pzErrMsg){ 265821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) int rc; 275821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) rc = sqlite3VdbeFinalize((Vdbe*)pStmt); 285821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) if( rc ){ 295821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) sqlite3SetString(pzErrMsg, db, sqlite3_errmsg(db)); 305821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) } 315821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) return rc; 325821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)} 335821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 345821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)/* 355821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)** Execute zSql on database db. Return an error code. 365821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)*/ 375821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)static int execSql(sqlite3 *db, char **pzErrMsg, const char *zSql){ 385821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) sqlite3_stmt *pStmt; 395821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) VVA_ONLY( int rc; ) 405821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) if( !zSql ){ 415821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) return SQLITE_NOMEM; 425821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) } 435821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) if( SQLITE_OK!=sqlite3_prepare(db, zSql, -1, &pStmt, 0) ){ 445821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) sqlite3SetString(pzErrMsg, db, sqlite3_errmsg(db)); 455821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) return sqlite3_errcode(db); 465821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) } 475821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) VVA_ONLY( rc = ) sqlite3_step(pStmt); 485821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) assert( rc!=SQLITE_ROW ); 495821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) return vacuumFinalize(db, pStmt, pzErrMsg); 505821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)} 515821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 525821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)/* 535821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)** Execute zSql on database db. The statement returns exactly 545821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)** one column. Execute this as SQL on the same database. 555821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)*/ 565821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)static int execExecSql(sqlite3 *db, char **pzErrMsg, const char *zSql){ 575821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) sqlite3_stmt *pStmt; 585821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) int rc; 595821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 605821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) rc = sqlite3_prepare(db, zSql, -1, &pStmt, 0); 615821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) if( rc!=SQLITE_OK ) return rc; 625821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 635821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) while( SQLITE_ROW==sqlite3_step(pStmt) ){ 645821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) rc = execSql(db, pzErrMsg, (char*)sqlite3_column_text(pStmt, 0)); 655821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) if( rc!=SQLITE_OK ){ 665821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) vacuumFinalize(db, pStmt, pzErrMsg); 675821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) return rc; 685821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) } 695821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) } 705821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 715821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) return vacuumFinalize(db, pStmt, pzErrMsg); 725821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)} 735821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 745821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)/* 755821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)** The non-standard VACUUM command is used to clean up the database, 765821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)** collapse free space, etc. It is modelled after the VACUUM command 775821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)** in PostgreSQL. 785821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)** 795821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)** In version 1.0.x of SQLite, the VACUUM command would call 805821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)** gdbm_reorganize() on all the database tables. But beginning 815821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)** with 2.0.0, SQLite no longer uses GDBM so this command has 825821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)** become a no-op. 835821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)*/ 845821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)void sqlite3Vacuum(Parse *pParse){ 855821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) Vdbe *v = sqlite3GetVdbe(pParse); 865821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) if( v ){ 875821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) sqlite3VdbeAddOp2(v, OP_Vacuum, 0, 0); 885821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) } 895821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) return; 905821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)} 915821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 925821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)/* 935821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)** This routine implements the OP_Vacuum opcode of the VDBE. 945821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)*/ 955821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)int sqlite3RunVacuum(char **pzErrMsg, sqlite3 *db){ 965821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) int rc = SQLITE_OK; /* Return code from service routines */ 975821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) Btree *pMain; /* The database being vacuumed */ 985821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) Btree *pTemp; /* The temporary database we vacuum into */ 995821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) char *zSql = 0; /* SQL statements */ 1005821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) int saved_flags; /* Saved value of the db->flags */ 1015821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) int saved_nChange; /* Saved value of db->nChange */ 1025821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) int saved_nTotalChange; /* Saved value of db->nTotalChange */ 1035821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) void (*saved_xTrace)(void*,const char*); /* Saved db->xTrace */ 1045821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) Db *pDb = 0; /* Database to detach at end of vacuum */ 1055821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) int isMemDb; /* True if vacuuming a :memory: database */ 1065821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) int nRes; /* Bytes of reserved space at the end of each page */ 1075821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) int nDb; /* Number of attached databases */ 1085821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 1095821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) if( !db->autoCommit ){ 1105821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) sqlite3SetString(pzErrMsg, db, "cannot VACUUM from within a transaction"); 1115821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) return SQLITE_ERROR; 1125821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) } 1135821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) if( db->activeVdbeCnt>1 ){ 1145821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) sqlite3SetString(pzErrMsg, db,"cannot VACUUM - SQL statements in progress"); 1155821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) return SQLITE_ERROR; 1165821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) } 1175821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 1185821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) /* Save the current value of the database flags so that it can be 1195821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) ** restored before returning. Then set the writable-schema flag, and 1205821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) ** disable CHECK and foreign key constraints. */ 1215821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) saved_flags = db->flags; 1225821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) saved_nChange = db->nChange; 1235821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) saved_nTotalChange = db->nTotalChange; 1245821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) saved_xTrace = db->xTrace; 1255821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) db->flags |= SQLITE_WriteSchema | SQLITE_IgnoreChecks | SQLITE_PreferBuiltin; 1265821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) db->flags &= ~(SQLITE_ForeignKeys | SQLITE_ReverseOrder); 1275821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) db->xTrace = 0; 1285821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 1295821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) pMain = db->aDb[0].pBt; 1305821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) isMemDb = sqlite3PagerIsMemdb(sqlite3BtreePager(pMain)); 1315821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 1325821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) /* Attach the temporary database as 'vacuum_db'. The synchronous pragma 1335821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) ** can be set to 'off' for this file, as it is not recovered if a crash 1345821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) ** occurs anyway. The integrity of the database is maintained by a 1355821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) ** (possibly synchronous) transaction opened on the main database before 1365821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) ** sqlite3BtreeCopyFile() is called. 1375821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) ** 1385821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) ** An optimisation would be to use a non-journaled pager. 1395821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) ** (Later:) I tried setting "PRAGMA vacuum_db.journal_mode=OFF" but 1405821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) ** that actually made the VACUUM run slower. Very little journalling 1415821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) ** actually occurs when doing a vacuum since the vacuum_db is initially 1425821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) ** empty. Only the journal header is written. Apparently it takes more 1435821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) ** time to parse and run the PRAGMA to turn journalling off than it does 1445821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) ** to write the journal header file. 1455821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) */ 1465821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) nDb = db->nDb; 1475821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) if( sqlite3TempInMemory(db) ){ 1485821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) zSql = "ATTACH ':memory:' AS vacuum_db;"; 1495821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) }else{ 1505821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) zSql = "ATTACH '' AS vacuum_db;"; 1515821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) } 1525821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) rc = execSql(db, pzErrMsg, zSql); 1535821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) if( db->nDb>nDb ){ 1545821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) pDb = &db->aDb[db->nDb-1]; 1555821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) assert( strcmp(pDb->zName,"vacuum_db")==0 ); 1565821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) } 1575821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) if( rc!=SQLITE_OK ) goto end_of_vacuum; 1585821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) pTemp = db->aDb[db->nDb-1].pBt; 1595821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 1605821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) /* The call to execSql() to attach the temp database has left the file 1615821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) ** locked (as there was more than one active statement when the transaction 1625821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) ** to read the schema was concluded. Unlock it here so that this doesn't 1635821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) ** cause problems for the call to BtreeSetPageSize() below. */ 1645821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) sqlite3BtreeCommit(pTemp); 1655821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 1665821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) nRes = sqlite3BtreeGetReserve(pMain); 1675821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 1685821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) /* A VACUUM cannot change the pagesize of an encrypted database. */ 1695821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)#ifdef SQLITE_HAS_CODEC 1705821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) if( db->nextPagesize ){ 1715821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) extern void sqlite3CodecGetKey(sqlite3*, int, void**, int*); 1725821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) int nKey; 1735821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) char *zKey; 1745821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) sqlite3CodecGetKey(db, 0, (void**)&zKey, &nKey); 1755821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) if( nKey ) db->nextPagesize = 0; 1765821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) } 1775821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)#endif 1785821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 1795821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) /* Do not attempt to change the page size for a WAL database */ 1805821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) if( sqlite3PagerGetJournalMode(sqlite3BtreePager(pMain)) 1815821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) ==PAGER_JOURNALMODE_WAL ){ 1825821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) db->nextPagesize = 0; 1835821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) } 1845821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 1855821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) if( sqlite3BtreeSetPageSize(pTemp, sqlite3BtreeGetPageSize(pMain), nRes, 0) 1865821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) || (!isMemDb && sqlite3BtreeSetPageSize(pTemp, db->nextPagesize, nRes, 0)) 1875821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) || NEVER(db->mallocFailed) 1885821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) ){ 1895821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) rc = SQLITE_NOMEM; 1905821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) goto end_of_vacuum; 1915821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) } 1925821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) rc = execSql(db, pzErrMsg, "PRAGMA vacuum_db.synchronous=OFF"); 1935821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) if( rc!=SQLITE_OK ){ 1945821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) goto end_of_vacuum; 1955821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) } 1965821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 1975821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)#ifndef SQLITE_OMIT_AUTOVACUUM 1985821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) sqlite3BtreeSetAutoVacuum(pTemp, db->nextAutovac>=0 ? db->nextAutovac : 1995821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) sqlite3BtreeGetAutoVacuum(pMain)); 2005821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)#endif 2015821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 2025821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) /* Begin a transaction */ 2035821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) rc = execSql(db, pzErrMsg, "BEGIN EXCLUSIVE;"); 2045821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) if( rc!=SQLITE_OK ) goto end_of_vacuum; 2055821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 2065821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) /* Query the schema of the main database. Create a mirror schema 2075821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) ** in the temporary database. 2085821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) */ 2095821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) rc = execExecSql(db, pzErrMsg, 2105821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) "SELECT 'CREATE TABLE vacuum_db.' || substr(sql,14) " 2115821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) " FROM sqlite_master WHERE type='table' AND name!='sqlite_sequence'" 2125821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) " AND rootpage>0" 2135821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) ); 2145821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) if( rc!=SQLITE_OK ) goto end_of_vacuum; 2155821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) rc = execExecSql(db, pzErrMsg, 2165821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) "SELECT 'CREATE INDEX vacuum_db.' || substr(sql,14)" 2175821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) " FROM sqlite_master WHERE sql LIKE 'CREATE INDEX %' "); 2185821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) if( rc!=SQLITE_OK ) goto end_of_vacuum; 2195821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) rc = execExecSql(db, pzErrMsg, 2205821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) "SELECT 'CREATE UNIQUE INDEX vacuum_db.' || substr(sql,21) " 2215821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) " FROM sqlite_master WHERE sql LIKE 'CREATE UNIQUE INDEX %'"); 2225821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) if( rc!=SQLITE_OK ) goto end_of_vacuum; 2235821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 2245821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) /* Loop through the tables in the main database. For each, do 2255821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) ** an "INSERT INTO vacuum_db.xxx SELECT * FROM main.xxx;" to copy 2265821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) ** the contents to the temporary database. 2275821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) */ 2285821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) rc = execExecSql(db, pzErrMsg, 2295821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) "SELECT 'INSERT INTO vacuum_db.' || quote(name) " 2305821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) "|| ' SELECT * FROM main.' || quote(name) || ';'" 2315821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) "FROM main.sqlite_master " 2325821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) "WHERE type = 'table' AND name!='sqlite_sequence' " 2335821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) " AND rootpage>0" 2345821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) ); 2355821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) if( rc!=SQLITE_OK ) goto end_of_vacuum; 2365821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 2375821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) /* Copy over the sequence table 2385821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) */ 2395821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) rc = execExecSql(db, pzErrMsg, 2405821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) "SELECT 'DELETE FROM vacuum_db.' || quote(name) || ';' " 2415821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) "FROM vacuum_db.sqlite_master WHERE name='sqlite_sequence' " 2425821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) ); 2435821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) if( rc!=SQLITE_OK ) goto end_of_vacuum; 2445821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) rc = execExecSql(db, pzErrMsg, 2455821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) "SELECT 'INSERT INTO vacuum_db.' || quote(name) " 2465821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) "|| ' SELECT * FROM main.' || quote(name) || ';' " 2475821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) "FROM vacuum_db.sqlite_master WHERE name=='sqlite_sequence';" 2485821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) ); 2495821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) if( rc!=SQLITE_OK ) goto end_of_vacuum; 2505821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 2515821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 2525821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) /* Copy the triggers, views, and virtual tables from the main database 2535821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) ** over to the temporary database. None of these objects has any 2545821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) ** associated storage, so all we have to do is copy their entries 2555821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) ** from the SQLITE_MASTER table. 2565821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) */ 2575821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) rc = execSql(db, pzErrMsg, 2585821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) "INSERT INTO vacuum_db.sqlite_master " 2595821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) " SELECT type, name, tbl_name, rootpage, sql" 2605821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) " FROM main.sqlite_master" 2615821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) " WHERE type='view' OR type='trigger'" 2625821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) " OR (type='table' AND rootpage=0)" 2635821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) ); 2645821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) if( rc ) goto end_of_vacuum; 2655821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 2665821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) /* At this point, unless the main db was completely empty, there is now a 2675821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) ** transaction open on the vacuum database, but not on the main database. 2685821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) ** Open a btree level transaction on the main database. This allows a 2695821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) ** call to sqlite3BtreeCopyFile(). The main database btree level 2705821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) ** transaction is then committed, so the SQL level never knows it was 2715821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) ** opened for writing. This way, the SQL transaction used to create the 2725821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) ** temporary database never needs to be committed. 2735821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) */ 2745821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) { 2755821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) u32 meta; 2765821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) int i; 2775821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 2785821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) /* This array determines which meta meta values are preserved in the 2795821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) ** vacuum. Even entries are the meta value number and odd entries 2805821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) ** are an increment to apply to the meta value after the vacuum. 2815821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) ** The increment is used to increase the schema cookie so that other 2825821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) ** connections to the same database will know to reread the schema. 2835821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) */ 2845821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) static const unsigned char aCopy[] = { 2855821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) BTREE_SCHEMA_VERSION, 1, /* Add one to the old schema cookie */ 2865821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) BTREE_DEFAULT_CACHE_SIZE, 0, /* Preserve the default page cache size */ 2875821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) BTREE_TEXT_ENCODING, 0, /* Preserve the text encoding */ 2885821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) BTREE_USER_VERSION, 0, /* Preserve the user version */ 2895821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) }; 2905821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 2915821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) assert( 1==sqlite3BtreeIsInTrans(pTemp) ); 2925821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) assert( 1==sqlite3BtreeIsInTrans(pMain) ); 2935821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 2945821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) /* Copy Btree meta values */ 2955821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) for(i=0; i<ArraySize(aCopy); i+=2){ 2965821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) /* GetMeta() and UpdateMeta() cannot fail in this context because 2975821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) ** we already have page 1 loaded into cache and marked dirty. */ 2985821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) sqlite3BtreeGetMeta(pMain, aCopy[i], &meta); 2995821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) rc = sqlite3BtreeUpdateMeta(pTemp, aCopy[i], meta+aCopy[i+1]); 3005821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) if( NEVER(rc!=SQLITE_OK) ) goto end_of_vacuum; 3015821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) } 3025821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 3035821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) rc = sqlite3BtreeCopyFile(pMain, pTemp); 3045821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) if( rc!=SQLITE_OK ) goto end_of_vacuum; 3055821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) rc = sqlite3BtreeCommit(pTemp); 3065821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) if( rc!=SQLITE_OK ) goto end_of_vacuum; 3075821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)#ifndef SQLITE_OMIT_AUTOVACUUM 3085821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) sqlite3BtreeSetAutoVacuum(pMain, sqlite3BtreeGetAutoVacuum(pTemp)); 3095821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)#endif 3105821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) } 3115821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 3125821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) assert( rc==SQLITE_OK ); 3135821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) rc = sqlite3BtreeSetPageSize(pMain, sqlite3BtreeGetPageSize(pTemp), nRes,1); 3145821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 3155821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)end_of_vacuum: 3165821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) /* Restore the original value of db->flags */ 3175821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) db->flags = saved_flags; 3185821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) db->nChange = saved_nChange; 3195821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) db->nTotalChange = saved_nTotalChange; 3205821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) db->xTrace = saved_xTrace; 3215821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) sqlite3BtreeSetPageSize(pMain, -1, -1, 1); 3225821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 3235821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) /* Currently there is an SQL level transaction open on the vacuum 3245821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) ** database. No locks are held on any other files (since the main file 3255821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) ** was committed at the btree level). So it safe to end the transaction 3265821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) ** by manually setting the autoCommit flag to true and detaching the 3275821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) ** vacuum database. The vacuum_db journal file is deleted when the pager 3285821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) ** is closed by the DETACH. 3295821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) */ 3305821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) db->autoCommit = 1; 3315821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 3325821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) if( pDb ){ 3335821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) sqlite3BtreeClose(pDb->pBt); 3345821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) pDb->pBt = 0; 3355821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) pDb->pSchema = 0; 3365821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) } 3375821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 3385821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) /* This both clears the schemas and reduces the size of the db->aDb[] 3395821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) ** array. */ 3405821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) sqlite3ResetInternalSchema(db, -1); 3415821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 3425821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) return rc; 3435821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)} 3445821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 3455821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)#endif /* SQLITE_OMIT_VACUUM && SQLITE_OMIT_ATTACH */ 346