1 /* 2 ** 2003 April 6 3 ** 4 ** The author disclaims copyright to this source code. In place of 5 ** a legal notice, here is a blessing: 6 ** 7 ** May you do good and not evil. 8 ** May you find forgiveness for yourself and forgive others. 9 ** May you share freely, never taking more than you give. 10 ** 11 ************************************************************************* 12 ** This file contains code used to implement the VACUUM command. 13 ** 14 ** Most of the code in this file may be omitted by defining the 15 ** SQLITE_OMIT_VACUUM macro. 16 */ 17 #include "sqliteInt.h" 18 #include "vdbeInt.h" 19 20 #if !defined(SQLITE_OMIT_VACUUM) && !defined(SQLITE_OMIT_ATTACH) 21 /* 22 ** Finalize a prepared statement. If there was an error, store the 23 ** text of the error message in *pzErrMsg. Return the result code. 24 */ 25 static int vacuumFinalize(sqlite3 *db, sqlite3_stmt *pStmt, char **pzErrMsg){ 26 int rc; 27 rc = sqlite3VdbeFinalize((Vdbe*)pStmt); 28 if( rc ){ 29 sqlite3SetString(pzErrMsg, db, sqlite3_errmsg(db)); 30 } 31 return rc; 32 } 33 34 /* 35 ** Execute zSql on database db. Return an error code. 36 */ 37 static int execSql(sqlite3 *db, char **pzErrMsg, const char *zSql){ 38 sqlite3_stmt *pStmt; 39 VVA_ONLY( int rc; ) 40 if( !zSql ){ 41 return SQLITE_NOMEM; 42 } 43 if( SQLITE_OK!=sqlite3_prepare(db, zSql, -1, &pStmt, 0) ){ 44 sqlite3SetString(pzErrMsg, db, sqlite3_errmsg(db)); 45 return sqlite3_errcode(db); 46 } 47 VVA_ONLY( rc = ) sqlite3_step(pStmt); 48 assert( rc!=SQLITE_ROW || (db->flags&SQLITE_CountRows) ); 49 return vacuumFinalize(db, pStmt, pzErrMsg); 50 } 51 52 /* 53 ** Execute zSql on database db. The statement returns exactly 54 ** one column. Execute this as SQL on the same database. 55 */ 56 static int execExecSql(sqlite3 *db, char **pzErrMsg, const char *zSql){ 57 sqlite3_stmt *pStmt; 58 int rc; 59 60 rc = sqlite3_prepare(db, zSql, -1, &pStmt, 0); 61 if( rc!=SQLITE_OK ) return rc; 62 63 while( SQLITE_ROW==sqlite3_step(pStmt) ){ 64 rc = execSql(db, pzErrMsg, (char*)sqlite3_column_text(pStmt, 0)); 65 if( rc!=SQLITE_OK ){ 66 vacuumFinalize(db, pStmt, pzErrMsg); 67 return rc; 68 } 69 } 70 71 return vacuumFinalize(db, pStmt, pzErrMsg); 72 } 73 74 /* 75 ** The non-standard VACUUM command is used to clean up the database, 76 ** collapse free space, etc. It is modelled after the VACUUM command 77 ** in PostgreSQL. 78 ** 79 ** In version 1.0.x of SQLite, the VACUUM command would call 80 ** gdbm_reorganize() on all the database tables. But beginning 81 ** with 2.0.0, SQLite no longer uses GDBM so this command has 82 ** become a no-op. 83 */ 84 void sqlite3Vacuum(Parse *pParse){ 85 Vdbe *v = sqlite3GetVdbe(pParse); 86 if( v ){ 87 sqlite3VdbeAddOp2(v, OP_Vacuum, 0, 0); 88 } 89 return; 90 } 91 92 /* 93 ** This routine implements the OP_Vacuum opcode of the VDBE. 94 */ 95 int sqlite3RunVacuum(char **pzErrMsg, sqlite3 *db){ 96 int rc = SQLITE_OK; /* Return code from service routines */ 97 Btree *pMain; /* The database being vacuumed */ 98 Btree *pTemp; /* The temporary database we vacuum into */ 99 char *zSql = 0; /* SQL statements */ 100 int saved_flags; /* Saved value of the db->flags */ 101 int saved_nChange; /* Saved value of db->nChange */ 102 int saved_nTotalChange; /* Saved value of db->nTotalChange */ 103 void (*saved_xTrace)(void*,const char*); /* Saved db->xTrace */ 104 Db *pDb = 0; /* Database to detach at end of vacuum */ 105 int isMemDb; /* True if vacuuming a :memory: database */ 106 int nRes; /* Bytes of reserved space at the end of each page */ 107 int nDb; /* Number of attached databases */ 108 109 if( !db->autoCommit ){ 110 sqlite3SetString(pzErrMsg, db, "cannot VACUUM from within a transaction"); 111 return SQLITE_ERROR; 112 } 113 if( db->activeVdbeCnt>1 ){ 114 sqlite3SetString(pzErrMsg, db,"cannot VACUUM - SQL statements in progress"); 115 return SQLITE_ERROR; 116 } 117 118 /* Save the current value of the database flags so that it can be 119 ** restored before returning. Then set the writable-schema flag, and 120 ** disable CHECK and foreign key constraints. */ 121 saved_flags = db->flags; 122 saved_nChange = db->nChange; 123 saved_nTotalChange = db->nTotalChange; 124 saved_xTrace = db->xTrace; 125 db->flags |= SQLITE_WriteSchema | SQLITE_IgnoreChecks | SQLITE_PreferBuiltin; 126 db->flags &= ~(SQLITE_ForeignKeys | SQLITE_ReverseOrder); 127 db->xTrace = 0; 128 129 pMain = db->aDb[0].pBt; 130 isMemDb = sqlite3PagerIsMemdb(sqlite3BtreePager(pMain)); 131 132 /* Attach the temporary database as 'vacuum_db'. The synchronous pragma 133 ** can be set to 'off' for this file, as it is not recovered if a crash 134 ** occurs anyway. The integrity of the database is maintained by a 135 ** (possibly synchronous) transaction opened on the main database before 136 ** sqlite3BtreeCopyFile() is called. 137 ** 138 ** An optimisation would be to use a non-journaled pager. 139 ** (Later:) I tried setting "PRAGMA vacuum_db.journal_mode=OFF" but 140 ** that actually made the VACUUM run slower. Very little journalling 141 ** actually occurs when doing a vacuum since the vacuum_db is initially 142 ** empty. Only the journal header is written. Apparently it takes more 143 ** time to parse and run the PRAGMA to turn journalling off than it does 144 ** to write the journal header file. 145 */ 146 nDb = db->nDb; 147 if( sqlite3TempInMemory(db) ){ 148 zSql = "ATTACH ':memory:' AS vacuum_db;"; 149 }else{ 150 zSql = "ATTACH '' AS vacuum_db;"; 151 } 152 rc = execSql(db, pzErrMsg, zSql); 153 if( db->nDb>nDb ){ 154 pDb = &db->aDb[db->nDb-1]; 155 assert( strcmp(pDb->zName,"vacuum_db")==0 ); 156 } 157 if( rc!=SQLITE_OK ) goto end_of_vacuum; 158 pTemp = db->aDb[db->nDb-1].pBt; 159 160 /* The call to execSql() to attach the temp database has left the file 161 ** locked (as there was more than one active statement when the transaction 162 ** to read the schema was concluded. Unlock it here so that this doesn't 163 ** cause problems for the call to BtreeSetPageSize() below. */ 164 sqlite3BtreeCommit(pTemp); 165 166 nRes = sqlite3BtreeGetReserve(pMain); 167 168 /* A VACUUM cannot change the pagesize of an encrypted database. */ 169 #ifdef SQLITE_HAS_CODEC 170 if( db->nextPagesize ){ 171 extern void sqlite3CodecGetKey(sqlite3*, int, void**, int*); 172 int nKey; 173 char *zKey; 174 sqlite3CodecGetKey(db, 0, (void**)&zKey, &nKey); 175 if( nKey ) db->nextPagesize = 0; 176 } 177 #endif 178 179 rc = execSql(db, pzErrMsg, "PRAGMA vacuum_db.synchronous=OFF"); 180 if( rc!=SQLITE_OK ) goto end_of_vacuum; 181 182 /* Begin a transaction and take an exclusive lock on the main database 183 ** file. This is done before the sqlite3BtreeGetPageSize(pMain) call below, 184 ** to ensure that we do not try to change the page-size on a WAL database. 185 */ 186 rc = execSql(db, pzErrMsg, "BEGIN;"); 187 if( rc!=SQLITE_OK ) goto end_of_vacuum; 188 rc = sqlite3BtreeBeginTrans(pMain, 2); 189 if( rc!=SQLITE_OK ) goto end_of_vacuum; 190 191 /* Do not attempt to change the page size for a WAL database */ 192 if( sqlite3PagerGetJournalMode(sqlite3BtreePager(pMain)) 193 ==PAGER_JOURNALMODE_WAL ){ 194 db->nextPagesize = 0; 195 } 196 197 if( sqlite3BtreeSetPageSize(pTemp, sqlite3BtreeGetPageSize(pMain), nRes, 0) 198 || (!isMemDb && sqlite3BtreeSetPageSize(pTemp, db->nextPagesize, nRes, 0)) 199 || NEVER(db->mallocFailed) 200 ){ 201 rc = SQLITE_NOMEM; 202 goto end_of_vacuum; 203 } 204 205 #ifndef SQLITE_OMIT_AUTOVACUUM 206 sqlite3BtreeSetAutoVacuum(pTemp, db->nextAutovac>=0 ? db->nextAutovac : 207 sqlite3BtreeGetAutoVacuum(pMain)); 208 #endif 209 210 /* Query the schema of the main database. Create a mirror schema 211 ** in the temporary database. 212 */ 213 rc = execExecSql(db, pzErrMsg, 214 "SELECT 'CREATE TABLE vacuum_db.' || substr(sql,14) " 215 " FROM sqlite_master WHERE type='table' AND name!='sqlite_sequence'" 216 " AND rootpage>0" 217 ); 218 if( rc!=SQLITE_OK ) goto end_of_vacuum; 219 rc = execExecSql(db, pzErrMsg, 220 "SELECT 'CREATE INDEX vacuum_db.' || substr(sql,14)" 221 " FROM sqlite_master WHERE sql LIKE 'CREATE INDEX %' "); 222 if( rc!=SQLITE_OK ) goto end_of_vacuum; 223 rc = execExecSql(db, pzErrMsg, 224 "SELECT 'CREATE UNIQUE INDEX vacuum_db.' || substr(sql,21) " 225 " FROM sqlite_master WHERE sql LIKE 'CREATE UNIQUE INDEX %'"); 226 if( rc!=SQLITE_OK ) goto end_of_vacuum; 227 228 /* Loop through the tables in the main database. For each, do 229 ** an "INSERT INTO vacuum_db.xxx SELECT * FROM main.xxx;" to copy 230 ** the contents to the temporary database. 231 */ 232 rc = execExecSql(db, pzErrMsg, 233 "SELECT 'INSERT INTO vacuum_db.' || quote(name) " 234 "|| ' SELECT * FROM main.' || quote(name) || ';'" 235 "FROM main.sqlite_master " 236 "WHERE type = 'table' AND name!='sqlite_sequence' " 237 " AND rootpage>0" 238 ); 239 if( rc!=SQLITE_OK ) goto end_of_vacuum; 240 241 /* Copy over the sequence table 242 */ 243 rc = execExecSql(db, pzErrMsg, 244 "SELECT 'DELETE FROM vacuum_db.' || quote(name) || ';' " 245 "FROM vacuum_db.sqlite_master WHERE name='sqlite_sequence' " 246 ); 247 if( rc!=SQLITE_OK ) goto end_of_vacuum; 248 rc = execExecSql(db, pzErrMsg, 249 "SELECT 'INSERT INTO vacuum_db.' || quote(name) " 250 "|| ' SELECT * FROM main.' || quote(name) || ';' " 251 "FROM vacuum_db.sqlite_master WHERE name=='sqlite_sequence';" 252 ); 253 if( rc!=SQLITE_OK ) goto end_of_vacuum; 254 255 256 /* Copy the triggers, views, and virtual tables from the main database 257 ** over to the temporary database. None of these objects has any 258 ** associated storage, so all we have to do is copy their entries 259 ** from the SQLITE_MASTER table. 260 */ 261 rc = execSql(db, pzErrMsg, 262 "INSERT INTO vacuum_db.sqlite_master " 263 " SELECT type, name, tbl_name, rootpage, sql" 264 " FROM main.sqlite_master" 265 " WHERE type='view' OR type='trigger'" 266 " OR (type='table' AND rootpage=0)" 267 ); 268 if( rc ) goto end_of_vacuum; 269 270 /* At this point, there is a write transaction open on both the 271 ** vacuum database and the main database. Assuming no error occurs, 272 ** both transactions are closed by this block - the main database 273 ** transaction by sqlite3BtreeCopyFile() and the other by an explicit 274 ** call to sqlite3BtreeCommit(). 275 */ 276 { 277 u32 meta; 278 int i; 279 280 /* This array determines which meta meta values are preserved in the 281 ** vacuum. Even entries are the meta value number and odd entries 282 ** are an increment to apply to the meta value after the vacuum. 283 ** The increment is used to increase the schema cookie so that other 284 ** connections to the same database will know to reread the schema. 285 */ 286 static const unsigned char aCopy[] = { 287 BTREE_SCHEMA_VERSION, 1, /* Add one to the old schema cookie */ 288 BTREE_DEFAULT_CACHE_SIZE, 0, /* Preserve the default page cache size */ 289 BTREE_TEXT_ENCODING, 0, /* Preserve the text encoding */ 290 BTREE_USER_VERSION, 0, /* Preserve the user version */ 291 }; 292 293 assert( 1==sqlite3BtreeIsInTrans(pTemp) ); 294 assert( 1==sqlite3BtreeIsInTrans(pMain) ); 295 296 /* Copy Btree meta values */ 297 for(i=0; i<ArraySize(aCopy); i+=2){ 298 /* GetMeta() and UpdateMeta() cannot fail in this context because 299 ** we already have page 1 loaded into cache and marked dirty. */ 300 sqlite3BtreeGetMeta(pMain, aCopy[i], &meta); 301 rc = sqlite3BtreeUpdateMeta(pTemp, aCopy[i], meta+aCopy[i+1]); 302 if( NEVER(rc!=SQLITE_OK) ) goto end_of_vacuum; 303 } 304 305 rc = sqlite3BtreeCopyFile(pMain, pTemp); 306 if( rc!=SQLITE_OK ) goto end_of_vacuum; 307 rc = sqlite3BtreeCommit(pTemp); 308 if( rc!=SQLITE_OK ) goto end_of_vacuum; 309 #ifndef SQLITE_OMIT_AUTOVACUUM 310 sqlite3BtreeSetAutoVacuum(pMain, sqlite3BtreeGetAutoVacuum(pTemp)); 311 #endif 312 } 313 314 assert( rc==SQLITE_OK ); 315 rc = sqlite3BtreeSetPageSize(pMain, sqlite3BtreeGetPageSize(pTemp), nRes,1); 316 317 end_of_vacuum: 318 /* Restore the original value of db->flags */ 319 db->flags = saved_flags; 320 db->nChange = saved_nChange; 321 db->nTotalChange = saved_nTotalChange; 322 db->xTrace = saved_xTrace; 323 sqlite3BtreeSetPageSize(pMain, -1, -1, 1); 324 325 /* Currently there is an SQL level transaction open on the vacuum 326 ** database. No locks are held on any other files (since the main file 327 ** was committed at the btree level). So it safe to end the transaction 328 ** by manually setting the autoCommit flag to true and detaching the 329 ** vacuum database. The vacuum_db journal file is deleted when the pager 330 ** is closed by the DETACH. 331 */ 332 db->autoCommit = 1; 333 334 if( pDb ){ 335 sqlite3BtreeClose(pDb->pBt); 336 pDb->pBt = 0; 337 pDb->pSchema = 0; 338 } 339 340 /* This both clears the schemas and reduces the size of the db->aDb[] 341 ** array. */ 342 sqlite3ResetAllSchemasOfConnection(db); 343 344 return rc; 345 } 346 347 #endif /* SQLITE_OMIT_VACUUM && SQLITE_OMIT_ATTACH */ 348