sl@0: /* sl@0: ** 2003 April 6 sl@0: ** sl@0: ** The author disclaims copyright to this source code. In place of sl@0: ** a legal notice, here is a blessing: sl@0: ** sl@0: ** May you do good and not evil. sl@0: ** May you find forgiveness for yourself and forgive others. sl@0: ** May you share freely, never taking more than you give. sl@0: ** sl@0: ************************************************************************* sl@0: ** This file contains code used to implement the VACUUM command. sl@0: ** sl@0: ** Most of the code in this file may be omitted by defining the sl@0: ** SQLITE_OMIT_VACUUM macro. sl@0: ** sl@0: ** $Id: vacuum.c,v 1.83 2008/08/26 21:07:27 drh Exp $ sl@0: */ sl@0: #include "sqliteInt.h" sl@0: #include "vdbeInt.h" sl@0: sl@0: #if !defined(SQLITE_OMIT_VACUUM) && !defined(SQLITE_OMIT_ATTACH) sl@0: /* sl@0: ** Execute zSql on database db. Return an error code. sl@0: */ sl@0: static int execSql(sqlite3 *db, const char *zSql){ sl@0: sqlite3_stmt *pStmt; sl@0: if( !zSql ){ sl@0: return SQLITE_NOMEM; sl@0: } sl@0: if( SQLITE_OK!=sqlite3_prepare(db, zSql, -1, &pStmt, 0) ){ sl@0: return sqlite3_errcode(db); sl@0: } sl@0: while( SQLITE_ROW==sqlite3_step(pStmt) ){} sl@0: return sqlite3_finalize(pStmt); sl@0: } sl@0: sl@0: /* sl@0: ** Execute zSql on database db. The statement returns exactly sl@0: ** one column. Execute this as SQL on the same database. sl@0: */ sl@0: static int execExecSql(sqlite3 *db, const char *zSql){ sl@0: sqlite3_stmt *pStmt; sl@0: int rc; sl@0: sl@0: rc = sqlite3_prepare(db, zSql, -1, &pStmt, 0); sl@0: if( rc!=SQLITE_OK ) return rc; sl@0: sl@0: while( SQLITE_ROW==sqlite3_step(pStmt) ){ sl@0: rc = execSql(db, (char*)sqlite3_column_text(pStmt, 0)); sl@0: if( rc!=SQLITE_OK ){ sl@0: sqlite3_finalize(pStmt); sl@0: return rc; sl@0: } sl@0: } sl@0: sl@0: return sqlite3_finalize(pStmt); sl@0: } sl@0: sl@0: /* sl@0: ** The non-standard VACUUM command is used to clean up the database, sl@0: ** collapse free space, etc. It is modelled after the VACUUM command sl@0: ** in PostgreSQL. sl@0: ** sl@0: ** In version 1.0.x of SQLite, the VACUUM command would call sl@0: ** gdbm_reorganize() on all the database tables. But beginning sl@0: ** with 2.0.0, SQLite no longer uses GDBM so this command has sl@0: ** become a no-op. sl@0: */ sl@0: void sqlite3Vacuum(Parse *pParse){ sl@0: Vdbe *v = sqlite3GetVdbe(pParse); sl@0: if( v ){ sl@0: sqlite3VdbeAddOp2(v, OP_Vacuum, 0, 0); sl@0: } sl@0: return; sl@0: } sl@0: sl@0: /* sl@0: ** This routine implements the OP_Vacuum opcode of the VDBE. sl@0: */ sl@0: int sqlite3RunVacuum(char **pzErrMsg, sqlite3 *db){ sl@0: int rc = SQLITE_OK; /* Return code from service routines */ sl@0: Btree *pMain; /* The database being vacuumed */ sl@0: Pager *pMainPager; /* Pager for database being vacuumed */ sl@0: Btree *pTemp; /* The temporary database we vacuum into */ sl@0: char *zSql = 0; /* SQL statements */ sl@0: int saved_flags; /* Saved value of the db->flags */ sl@0: int saved_nChange; /* Saved value of db->nChange */ sl@0: int saved_nTotalChange; /* Saved value of db->nTotalChange */ sl@0: Db *pDb = 0; /* Database to detach at end of vacuum */ sl@0: int isMemDb; /* True is vacuuming a :memory: database */ sl@0: int nRes; sl@0: sl@0: /* Save the current value of the write-schema flag before setting it. */ sl@0: saved_flags = db->flags; sl@0: saved_nChange = db->nChange; sl@0: saved_nTotalChange = db->nTotalChange; sl@0: db->flags |= SQLITE_WriteSchema | SQLITE_IgnoreChecks; sl@0: sl@0: if( !db->autoCommit ){ sl@0: sqlite3SetString(pzErrMsg, db, "cannot VACUUM from within a transaction"); sl@0: rc = SQLITE_ERROR; sl@0: goto end_of_vacuum; sl@0: } sl@0: pMain = db->aDb[0].pBt; sl@0: pMainPager = sqlite3BtreePager(pMain); sl@0: isMemDb = sqlite3PagerFile(pMainPager)->pMethods==0; sl@0: sl@0: /* Attach the temporary database as 'vacuum_db'. The synchronous pragma sl@0: ** can be set to 'off' for this file, as it is not recovered if a crash sl@0: ** occurs anyway. The integrity of the database is maintained by a sl@0: ** (possibly synchronous) transaction opened on the main database before sl@0: ** sqlite3BtreeCopyFile() is called. sl@0: ** sl@0: ** An optimisation would be to use a non-journaled pager. sl@0: ** (Later:) I tried setting "PRAGMA vacuum_db.journal_mode=OFF" but sl@0: ** that actually made the VACUUM run slower. Very little journalling sl@0: ** actually occurs when doing a vacuum since the vacuum_db is initially sl@0: ** empty. Only the journal header is written. Apparently it takes more sl@0: ** time to parse and run the PRAGMA to turn journalling off than it does sl@0: ** to write the journal header file. sl@0: */ sl@0: zSql = "ATTACH '' AS vacuum_db;"; sl@0: rc = execSql(db, zSql); sl@0: if( rc!=SQLITE_OK ) goto end_of_vacuum; sl@0: pDb = &db->aDb[db->nDb-1]; sl@0: assert( strcmp(db->aDb[db->nDb-1].zName,"vacuum_db")==0 ); sl@0: pTemp = db->aDb[db->nDb-1].pBt; sl@0: sl@0: nRes = sqlite3BtreeGetReserve(pMain); sl@0: sl@0: /* A VACUUM cannot change the pagesize of an encrypted database. */ sl@0: #ifdef SQLITE_HAS_CODEC sl@0: if( db->nextPagesize ){ sl@0: extern void sqlite3CodecGetKey(sqlite3*, int, void**, int*); sl@0: int nKey; sl@0: char *zKey; sl@0: sqlite3CodecGetKey(db, 0, (void**)&zKey, &nKey); sl@0: if( nKey ) db->nextPagesize = 0; sl@0: } sl@0: #endif sl@0: sl@0: if( sqlite3BtreeSetPageSize(pTemp, sqlite3BtreeGetPageSize(pMain), nRes) sl@0: || (!isMemDb && sqlite3BtreeSetPageSize(pTemp, db->nextPagesize, nRes)) sl@0: || db->mallocFailed sl@0: ){ sl@0: rc = SQLITE_NOMEM; sl@0: goto end_of_vacuum; sl@0: } sl@0: rc = execSql(db, "PRAGMA vacuum_db.synchronous=OFF"); sl@0: if( rc!=SQLITE_OK ){ sl@0: goto end_of_vacuum; sl@0: } sl@0: sl@0: #ifndef SQLITE_OMIT_AUTOVACUUM sl@0: sqlite3BtreeSetAutoVacuum(pTemp, db->nextAutovac>=0 ? db->nextAutovac : sl@0: sqlite3BtreeGetAutoVacuum(pMain)); sl@0: #endif sl@0: sl@0: /* Begin a transaction */ sl@0: rc = execSql(db, "BEGIN EXCLUSIVE;"); sl@0: if( rc!=SQLITE_OK ) goto end_of_vacuum; sl@0: sl@0: /* Query the schema of the main database. Create a mirror schema sl@0: ** in the temporary database. sl@0: */ sl@0: rc = execExecSql(db, sl@0: "SELECT 'CREATE TABLE vacuum_db.' || substr(sql,14) " sl@0: " FROM sqlite_master WHERE type='table' AND name!='sqlite_sequence'" sl@0: " AND rootpage>0" sl@0: ); sl@0: if( rc!=SQLITE_OK ) goto end_of_vacuum; sl@0: rc = execExecSql(db, sl@0: "SELECT 'CREATE INDEX vacuum_db.' || substr(sql,14)" sl@0: " FROM sqlite_master WHERE sql LIKE 'CREATE INDEX %' "); sl@0: if( rc!=SQLITE_OK ) goto end_of_vacuum; sl@0: rc = execExecSql(db, sl@0: "SELECT 'CREATE UNIQUE INDEX vacuum_db.' || substr(sql,21) " sl@0: " FROM sqlite_master WHERE sql LIKE 'CREATE UNIQUE INDEX %'"); sl@0: if( rc!=SQLITE_OK ) goto end_of_vacuum; sl@0: sl@0: /* Loop through the tables in the main database. For each, do sl@0: ** an "INSERT INTO vacuum_db.xxx SELECT * FROM xxx;" to copy sl@0: ** the contents to the temporary database. sl@0: */ sl@0: rc = execExecSql(db, sl@0: "SELECT 'INSERT INTO vacuum_db.' || quote(name) " sl@0: "|| ' SELECT * FROM ' || quote(name) || ';'" sl@0: "FROM sqlite_master " sl@0: "WHERE type = 'table' AND name!='sqlite_sequence' " sl@0: " AND rootpage>0" sl@0: sl@0: ); sl@0: if( rc!=SQLITE_OK ) goto end_of_vacuum; sl@0: sl@0: /* Copy over the sequence table sl@0: */ sl@0: rc = execExecSql(db, sl@0: "SELECT 'DELETE FROM vacuum_db.' || quote(name) || ';' " sl@0: "FROM vacuum_db.sqlite_master WHERE name='sqlite_sequence' " sl@0: ); sl@0: if( rc!=SQLITE_OK ) goto end_of_vacuum; sl@0: rc = execExecSql(db, sl@0: "SELECT 'INSERT INTO vacuum_db.' || quote(name) " sl@0: "|| ' SELECT * FROM ' || quote(name) || ';' " sl@0: "FROM vacuum_db.sqlite_master WHERE name=='sqlite_sequence';" sl@0: ); sl@0: if( rc!=SQLITE_OK ) goto end_of_vacuum; sl@0: sl@0: sl@0: /* Copy the triggers, views, and virtual tables from the main database sl@0: ** over to the temporary database. None of these objects has any sl@0: ** associated storage, so all we have to do is copy their entries sl@0: ** from the SQLITE_MASTER table. sl@0: */ sl@0: rc = execSql(db, sl@0: "INSERT INTO vacuum_db.sqlite_master " sl@0: " SELECT type, name, tbl_name, rootpage, sql" sl@0: " FROM sqlite_master" sl@0: " WHERE type='view' OR type='trigger'" sl@0: " OR (type='table' AND rootpage=0)" sl@0: ); sl@0: if( rc ) goto end_of_vacuum; sl@0: sl@0: /* At this point, unless the main db was completely empty, there is now a sl@0: ** transaction open on the vacuum database, but not on the main database. sl@0: ** Open a btree level transaction on the main database. This allows a sl@0: ** call to sqlite3BtreeCopyFile(). The main database btree level sl@0: ** transaction is then committed, so the SQL level never knows it was sl@0: ** opened for writing. This way, the SQL transaction used to create the sl@0: ** temporary database never needs to be committed. sl@0: */ sl@0: if( rc==SQLITE_OK ){ sl@0: u32 meta; sl@0: int i; sl@0: sl@0: /* This array determines which meta meta values are preserved in the sl@0: ** vacuum. Even entries are the meta value number and odd entries sl@0: ** are an increment to apply to the meta value after the vacuum. sl@0: ** The increment is used to increase the schema cookie so that other sl@0: ** connections to the same database will know to reread the schema. sl@0: */ sl@0: static const unsigned char aCopy[] = { sl@0: 1, 1, /* Add one to the old schema cookie */ sl@0: 3, 0, /* Preserve the default page cache size */ sl@0: 5, 0, /* Preserve the default text encoding */ sl@0: 6, 0, /* Preserve the user version */ sl@0: }; sl@0: sl@0: assert( 1==sqlite3BtreeIsInTrans(pTemp) ); sl@0: assert( 1==sqlite3BtreeIsInTrans(pMain) ); sl@0: sl@0: /* Copy Btree meta values */ sl@0: for(i=0; iflags */ sl@0: db->flags = saved_flags; sl@0: db->nChange = saved_nChange; sl@0: db->nTotalChange = saved_nTotalChange; sl@0: sl@0: /* Currently there is an SQL level transaction open on the vacuum sl@0: ** database. No locks are held on any other files (since the main file sl@0: ** was committed at the btree level). So it safe to end the transaction sl@0: ** by manually setting the autoCommit flag to true and detaching the sl@0: ** vacuum database. The vacuum_db journal file is deleted when the pager sl@0: ** is closed by the DETACH. sl@0: */ sl@0: db->autoCommit = 1; sl@0: sl@0: if( pDb ){ sl@0: sqlite3BtreeClose(pDb->pBt); sl@0: pDb->pBt = 0; sl@0: pDb->pSchema = 0; sl@0: } sl@0: sl@0: sqlite3ResetInternalSchema(db, 0); sl@0: sl@0: return rc; sl@0: } sl@0: #endif /* SQLITE_OMIT_VACUUM && SQLITE_OMIT_ATTACH */