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 ** $Id: vacuum.c,v 1.77 2008/03/20 11:04:21 danielk1977 Exp $ 18 */ 19 #include "sqliteInt.h" 20 #include "vdbeInt.h" 21 22 #if !defined(SQLITE_OMIT_VACUUM) && !defined(SQLITE_OMIT_ATTACH) 23 /* 24 ** Execute zSql on database db. Return an error code. 25 */ 26 static int execSql(sqlite3 *db, const char *zSql){ 27 sqlite3_stmt *pStmt; 28 if( !zSql ){ 29 return SQLITE_NOMEM; 30 } 31 if( SQLITE_OK!=sqlite3_prepare(db, zSql, -1, &pStmt, 0) ){ 32 return sqlite3_errcode(db); 33 } 34 while( SQLITE_ROW==sqlite3_step(pStmt) ){} 35 return sqlite3_finalize(pStmt); 36 } 37 38 /* 39 ** Execute zSql on database db. The statement returns exactly 40 ** one column. Execute this as SQL on the same database. 41 */ 42 static int execExecSql(sqlite3 *db, const char *zSql){ 43 sqlite3_stmt *pStmt; 44 int rc; 45 46 rc = sqlite3_prepare(db, zSql, -1, &pStmt, 0); 47 if( rc!=SQLITE_OK ) return rc; 48 49 while( SQLITE_ROW==sqlite3_step(pStmt) ){ 50 rc = execSql(db, (char*)sqlite3_column_text(pStmt, 0)); 51 if( rc!=SQLITE_OK ){ 52 sqlite3_finalize(pStmt); 53 return rc; 54 } 55 } 56 57 return sqlite3_finalize(pStmt); 58 } 59 60 /* 61 ** The non-standard VACUUM command is used to clean up the database, 62 ** collapse free space, etc. It is modelled after the VACUUM command 63 ** in PostgreSQL. 64 ** 65 ** In version 1.0.x of SQLite, the VACUUM command would call 66 ** gdbm_reorganize() on all the database tables. But beginning 67 ** with 2.0.0, SQLite no longer uses GDBM so this command has 68 ** become a no-op. 69 */ 70 void sqlite3Vacuum(Parse *pParse){ 71 Vdbe *v = sqlite3GetVdbe(pParse); 72 if( v ){ 73 sqlite3VdbeAddOp2(v, OP_Vacuum, 0, 0); 74 } 75 return; 76 } 77 78 /* 79 ** This routine implements the OP_Vacuum opcode of the VDBE. 80 */ 81 int sqlite3RunVacuum(char **pzErrMsg, sqlite3 *db){ 82 int rc = SQLITE_OK; /* Return code from service routines */ 83 Btree *pMain; /* The database being vacuumed */ 84 Btree *pTemp; /* The temporary database we vacuum into */ 85 char *zSql = 0; /* SQL statements */ 86 int saved_flags; /* Saved value of the db->flags */ 87 Db *pDb = 0; /* Database to detach at end of vacuum */ 88 int nRes; 89 90 /* Save the current value of the write-schema flag before setting it. */ 91 saved_flags = db->flags; 92 db->flags |= SQLITE_WriteSchema | SQLITE_IgnoreChecks; 93 94 if( !db->autoCommit ){ 95 sqlite3SetString(pzErrMsg, "cannot VACUUM from within a transaction", 96 (char*)0); 97 rc = SQLITE_ERROR; 98 goto end_of_vacuum; 99 } 100 pMain = db->aDb[0].pBt; 101 102 /* Attach the temporary database as 'vacuum_db'. The synchronous pragma 103 ** can be set to 'off' for this file, as it is not recovered if a crash 104 ** occurs anyway. The integrity of the database is maintained by a 105 ** (possibly synchronous) transaction opened on the main database before 106 ** sqlite3BtreeCopyFile() is called. 107 ** 108 ** An optimisation would be to use a non-journaled pager. 109 */ 110 zSql = "ATTACH '' AS vacuum_db;"; 111 rc = execSql(db, zSql); 112 if( rc!=SQLITE_OK ) goto end_of_vacuum; 113 pDb = &db->aDb[db->nDb-1]; 114 assert( strcmp(db->aDb[db->nDb-1].zName,"vacuum_db")==0 ); 115 pTemp = db->aDb[db->nDb-1].pBt; 116 117 nRes = sqlite3BtreeGetReserve(pMain); 118 if( sqlite3BtreeSetPageSize(pTemp, sqlite3BtreeGetPageSize(pMain), nRes) 119 || sqlite3BtreeSetPageSize(pTemp, db->nextPagesize, nRes) 120 || db->mallocFailed 121 ){ 122 rc = SQLITE_NOMEM; 123 goto end_of_vacuum; 124 } 125 rc = execSql(db, "PRAGMA vacuum_db.synchronous=OFF"); 126 if( rc!=SQLITE_OK ){ 127 goto end_of_vacuum; 128 } 129 130 #ifndef SQLITE_OMIT_AUTOVACUUM 131 sqlite3BtreeSetAutoVacuum(pTemp, db->nextAutovac>=0 ? db->nextAutovac : 132 sqlite3BtreeGetAutoVacuum(pMain)); 133 #endif 134 135 /* Begin a transaction */ 136 rc = execSql(db, "BEGIN EXCLUSIVE;"); 137 if( rc!=SQLITE_OK ) goto end_of_vacuum; 138 139 /* Query the schema of the main database. Create a mirror schema 140 ** in the temporary database. 141 */ 142 rc = execExecSql(db, 143 "SELECT 'CREATE TABLE vacuum_db.' || substr(sql,14) " 144 " FROM sqlite_master WHERE type='table' AND name!='sqlite_sequence'" 145 " AND rootpage>0" 146 ); 147 if( rc!=SQLITE_OK ) goto end_of_vacuum; 148 rc = execExecSql(db, 149 "SELECT 'CREATE INDEX vacuum_db.' || substr(sql,14)" 150 " FROM sqlite_master WHERE sql LIKE 'CREATE INDEX %' "); 151 if( rc!=SQLITE_OK ) goto end_of_vacuum; 152 rc = execExecSql(db, 153 "SELECT 'CREATE UNIQUE INDEX vacuum_db.' || substr(sql,21) " 154 " FROM sqlite_master WHERE sql LIKE 'CREATE UNIQUE INDEX %'"); 155 if( rc!=SQLITE_OK ) goto end_of_vacuum; 156 157 /* Loop through the tables in the main database. For each, do 158 ** an "INSERT INTO vacuum_db.xxx SELECT * FROM xxx;" to copy 159 ** the contents to the temporary database. 160 */ 161 rc = execExecSql(db, 162 "SELECT 'INSERT INTO vacuum_db.' || quote(name) " 163 "|| ' SELECT * FROM ' || quote(name) || ';'" 164 "FROM sqlite_master " 165 "WHERE type = 'table' AND name!='sqlite_sequence' " 166 " AND rootpage>0" 167 168 ); 169 if( rc!=SQLITE_OK ) goto end_of_vacuum; 170 171 /* Copy over the sequence table 172 */ 173 rc = execExecSql(db, 174 "SELECT 'DELETE FROM vacuum_db.' || quote(name) || ';' " 175 "FROM vacuum_db.sqlite_master WHERE name='sqlite_sequence' " 176 ); 177 if( rc!=SQLITE_OK ) goto end_of_vacuum; 178 rc = execExecSql(db, 179 "SELECT 'INSERT INTO vacuum_db.' || quote(name) " 180 "|| ' SELECT * FROM ' || quote(name) || ';' " 181 "FROM vacuum_db.sqlite_master WHERE name=='sqlite_sequence';" 182 ); 183 if( rc!=SQLITE_OK ) goto end_of_vacuum; 184 185 186 /* Copy the triggers, views, and virtual tables from the main database 187 ** over to the temporary database. None of these objects has any 188 ** associated storage, so all we have to do is copy their entries 189 ** from the SQLITE_MASTER table. 190 */ 191 rc = execSql(db, 192 "INSERT INTO vacuum_db.sqlite_master " 193 " SELECT type, name, tbl_name, rootpage, sql" 194 " FROM sqlite_master" 195 " WHERE type='view' OR type='trigger'" 196 " OR (type='table' AND rootpage=0)" 197 ); 198 if( rc ) goto end_of_vacuum; 199 200 /* At this point, unless the main db was completely empty, there is now a 201 ** transaction open on the vacuum database, but not on the main database. 202 ** Open a btree level transaction on the main database. This allows a 203 ** call to sqlite3BtreeCopyFile(). The main database btree level 204 ** transaction is then committed, so the SQL level never knows it was 205 ** opened for writing. This way, the SQL transaction used to create the 206 ** temporary database never needs to be committed. 207 */ 208 if( rc==SQLITE_OK ){ 209 u32 meta; 210 int i; 211 212 /* This array determines which meta meta values are preserved in the 213 ** vacuum. Even entries are the meta value number and odd entries 214 ** are an increment to apply to the meta value after the vacuum. 215 ** The increment is used to increase the schema cookie so that other 216 ** connections to the same database will know to reread the schema. 217 */ 218 static const unsigned char aCopy[] = { 219 1, 1, /* Add one to the old schema cookie */ 220 3, 0, /* Preserve the default page cache size */ 221 5, 0, /* Preserve the default text encoding */ 222 6, 0, /* Preserve the user version */ 223 }; 224 225 assert( 1==sqlite3BtreeIsInTrans(pTemp) ); 226 assert( 1==sqlite3BtreeIsInTrans(pMain) ); 227 228 /* Copy Btree meta values */ 229 for(i=0; i<sizeof(aCopy)/sizeof(aCopy[0]); i+=2){ 230 rc = sqlite3BtreeGetMeta(pMain, aCopy[i], &meta); 231 if( rc!=SQLITE_OK ) goto end_of_vacuum; 232 rc = sqlite3BtreeUpdateMeta(pTemp, aCopy[i], meta+aCopy[i+1]); 233 if( rc!=SQLITE_OK ) goto end_of_vacuum; 234 } 235 236 rc = sqlite3BtreeCopyFile(pMain, pTemp); 237 if( rc!=SQLITE_OK ) goto end_of_vacuum; 238 rc = sqlite3BtreeCommit(pTemp); 239 if( rc!=SQLITE_OK ) goto end_of_vacuum; 240 rc = sqlite3BtreeCommit(pMain); 241 } 242 243 if( rc==SQLITE_OK ){ 244 rc = sqlite3BtreeSetPageSize(pMain, sqlite3BtreeGetPageSize(pTemp), nRes); 245 } 246 247 end_of_vacuum: 248 /* Restore the original value of db->flags */ 249 db->flags = saved_flags; 250 251 /* Currently there is an SQL level transaction open on the vacuum 252 ** database. No locks are held on any other files (since the main file 253 ** was committed at the btree level). So it safe to end the transaction 254 ** by manually setting the autoCommit flag to true and detaching the 255 ** vacuum database. The vacuum_db journal file is deleted when the pager 256 ** is closed by the DETACH. 257 */ 258 db->autoCommit = 1; 259 260 if( pDb ){ 261 sqlite3BtreeClose(pDb->pBt); 262 pDb->pBt = 0; 263 pDb->pSchema = 0; 264 } 265 266 sqlite3ResetInternalSchema(db, 0); 267 268 return rc; 269 } 270 #endif /* SQLITE_OMIT_VACUUM && SQLITE_OMIT_ATTACH */ 271