| OLD | NEW |
|---|
| (Empty) |
| 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 VACUUM command is used to clean up the database, | |
| 76 ** collapse free space, etc. It is modelled after the VACUUM command | |
| 77 ** in PostgreSQL. The VACUUM command works as follows: | |
| 78 ** | |
| 79 ** (1) Create a new transient database file | |
| 80 ** (2) Copy all content from the database being vacuumed into | |
| 81 ** the new transient database file | |
| 82 ** (3) Copy content from the transient database back into the | |
| 83 ** original database. | |
| 84 ** | |
| 85 ** The transient database requires temporary disk space approximately | |
| 86 ** equal to the size of the original database. The copy operation of | |
| 87 ** step (3) requires additional temporary disk space approximately equal | |
| 88 ** to the size of the original database for the rollback journal. | |
| 89 ** Hence, temporary disk space that is approximately 2x the size of the | |
| 90 ** original database is required. Every page of the database is written | |
| 91 ** approximately 3 times: Once for step (2) and twice for step (3). | |
| 92 ** Two writes per page are required in step (3) because the original | |
| 93 ** database content must be written into the rollback journal prior to | |
| 94 ** overwriting the database with the vacuumed content. | |
| 95 ** | |
| 96 ** Only 1x temporary space and only 1x writes would be required if | |
| 97 ** the copy of step (3) were replaced by deleting the original database | |
| 98 ** and renaming the transient database as the original. But that will | |
| 99 ** not work if other processes are attached to the original database. | |
| 100 ** And a power loss in between deleting the original and renaming the | |
| 101 ** transient would cause the database file to appear to be deleted | |
| 102 ** following reboot. | |
| 103 */ | |
| 104 void sqlite3Vacuum(Parse *pParse){ | |
| 105 Vdbe *v = sqlite3GetVdbe(pParse); | |
| 106 if( v ){ | |
| 107 sqlite3VdbeAddOp2(v, OP_Vacuum, 0, 0); | |
| 108 sqlite3VdbeUsesBtree(v, 0); | |
| 109 } | |
| 110 return; | |
| 111 } | |
| 112 | |
| 113 /* | |
| 114 ** This routine implements the OP_Vacuum opcode of the VDBE. | |
| 115 */ | |
| 116 int sqlite3RunVacuum(char **pzErrMsg, sqlite3 *db){ | |
| 117 int rc = SQLITE_OK; /* Return code from service routines */ | |
| 118 Btree *pMain; /* The database being vacuumed */ | |
| 119 Btree *pTemp; /* The temporary database we vacuum into */ | |
| 120 char *zSql = 0; /* SQL statements */ | |
| 121 int saved_flags; /* Saved value of the db->flags */ | |
| 122 int saved_nChange; /* Saved value of db->nChange */ | |
| 123 int saved_nTotalChange; /* Saved value of db->nTotalChange */ | |
| 124 void (*saved_xTrace)(void*,const char*); /* Saved db->xTrace */ | |
| 125 Db *pDb = 0; /* Database to detach at end of vacuum */ | |
| 126 int isMemDb; /* True if vacuuming a :memory: database */ | |
| 127 int nRes; /* Bytes of reserved space at the end of each page */ | |
| 128 int nDb; /* Number of attached databases */ | |
| 129 | |
| 130 if( !db->autoCommit ){ | |
| 131 sqlite3SetString(pzErrMsg, db, "cannot VACUUM from within a transaction"); | |
| 132 return SQLITE_ERROR; | |
| 133 } | |
| 134 if( db->nVdbeActive>1 ){ | |
| 135 sqlite3SetString(pzErrMsg, db,"cannot VACUUM - SQL statements in progress"); | |
| 136 return SQLITE_ERROR; | |
| 137 } | |
| 138 | |
| 139 /* Save the current value of the database flags so that it can be | |
| 140 ** restored before returning. Then set the writable-schema flag, and | |
| 141 ** disable CHECK and foreign key constraints. */ | |
| 142 saved_flags = db->flags; | |
| 143 saved_nChange = db->nChange; | |
| 144 saved_nTotalChange = db->nTotalChange; | |
| 145 saved_xTrace = db->xTrace; | |
| 146 db->flags |= SQLITE_WriteSchema | SQLITE_IgnoreChecks | SQLITE_PreferBuiltin; | |
| 147 db->flags &= ~(SQLITE_ForeignKeys | SQLITE_ReverseOrder); | |
| 148 db->xTrace = 0; | |
| 149 | |
| 150 pMain = db->aDb[0].pBt; | |
| 151 isMemDb = sqlite3PagerIsMemdb(sqlite3BtreePager(pMain)); | |
| 152 | |
| 153 /* Attach the temporary database as 'vacuum_db'. The synchronous pragma | |
| 154 ** can be set to 'off' for this file, as it is not recovered if a crash | |
| 155 ** occurs anyway. The integrity of the database is maintained by a | |
| 156 ** (possibly synchronous) transaction opened on the main database before | |
| 157 ** sqlite3BtreeCopyFile() is called. | |
| 158 ** | |
| 159 ** An optimisation would be to use a non-journaled pager. | |
| 160 ** (Later:) I tried setting "PRAGMA vacuum_db.journal_mode=OFF" but | |
| 161 ** that actually made the VACUUM run slower. Very little journalling | |
| 162 ** actually occurs when doing a vacuum since the vacuum_db is initially | |
| 163 ** empty. Only the journal header is written. Apparently it takes more | |
| 164 ** time to parse and run the PRAGMA to turn journalling off than it does | |
| 165 ** to write the journal header file. | |
| 166 */ | |
| 167 nDb = db->nDb; | |
| 168 if( sqlite3TempInMemory(db) ){ | |
| 169 zSql = "ATTACH ':memory:' AS vacuum_db;"; | |
| 170 }else{ | |
| 171 zSql = "ATTACH '' AS vacuum_db;"; | |
| 172 } | |
| 173 rc = execSql(db, pzErrMsg, zSql); | |
| 174 if( db->nDb>nDb ){ | |
| 175 pDb = &db->aDb[db->nDb-1]; | |
| 176 assert( strcmp(pDb->zName,"vacuum_db")==0 ); | |
| 177 } | |
| 178 if( rc!=SQLITE_OK ) goto end_of_vacuum; | |
| 179 pTemp = db->aDb[db->nDb-1].pBt; | |
| 180 | |
| 181 /* The call to execSql() to attach the temp database has left the file | |
| 182 ** locked (as there was more than one active statement when the transaction | |
| 183 ** to read the schema was concluded. Unlock it here so that this doesn't | |
| 184 ** cause problems for the call to BtreeSetPageSize() below. */ | |
| 185 sqlite3BtreeCommit(pTemp); | |
| 186 | |
| 187 nRes = sqlite3BtreeGetOptimalReserve(pMain); | |
| 188 | |
| 189 /* A VACUUM cannot change the pagesize of an encrypted database. */ | |
| 190 #ifdef SQLITE_HAS_CODEC | |
| 191 if( db->nextPagesize ){ | |
| 192 extern void sqlite3CodecGetKey(sqlite3*, int, void**, int*); | |
| 193 int nKey; | |
| 194 char *zKey; | |
| 195 sqlite3CodecGetKey(db, 0, (void**)&zKey, &nKey); | |
| 196 if( nKey ) db->nextPagesize = 0; | |
| 197 } | |
| 198 #endif | |
| 199 | |
| 200 rc = execSql(db, pzErrMsg, "PRAGMA vacuum_db.synchronous=OFF"); | |
| 201 if( rc!=SQLITE_OK ) goto end_of_vacuum; | |
| 202 | |
| 203 /* Begin a transaction and take an exclusive lock on the main database | |
| 204 ** file. This is done before the sqlite3BtreeGetPageSize(pMain) call below, | |
| 205 ** to ensure that we do not try to change the page-size on a WAL database. | |
| 206 */ | |
| 207 rc = execSql(db, pzErrMsg, "BEGIN;"); | |
| 208 if( rc!=SQLITE_OK ) goto end_of_vacuum; | |
| 209 rc = sqlite3BtreeBeginTrans(pMain, 2); | |
| 210 if( rc!=SQLITE_OK ) goto end_of_vacuum; | |
| 211 | |
| 212 /* Do not attempt to change the page size for a WAL database */ | |
| 213 if( sqlite3PagerGetJournalMode(sqlite3BtreePager(pMain)) | |
| 214 ==PAGER_JOURNALMODE_WAL ){ | |
| 215 db->nextPagesize = 0; | |
| 216 } | |
| 217 | |
| 218 if( sqlite3BtreeSetPageSize(pTemp, sqlite3BtreeGetPageSize(pMain), nRes, 0) | |
| 219 || (!isMemDb && sqlite3BtreeSetPageSize(pTemp, db->nextPagesize, nRes, 0)) | |
| 220 || NEVER(db->mallocFailed) | |
| 221 ){ | |
| 222 rc = SQLITE_NOMEM; | |
| 223 goto end_of_vacuum; | |
| 224 } | |
| 225 | |
| 226 #ifndef SQLITE_OMIT_AUTOVACUUM | |
| 227 sqlite3BtreeSetAutoVacuum(pTemp, db->nextAutovac>=0 ? db->nextAutovac : | |
| 228 sqlite3BtreeGetAutoVacuum(pMain)); | |
| 229 #endif | |
| 230 | |
| 231 /* Query the schema of the main database. Create a mirror schema | |
| 232 ** in the temporary database. | |
| 233 */ | |
| 234 rc = execExecSql(db, pzErrMsg, | |
| 235 "SELECT 'CREATE TABLE vacuum_db.' || substr(sql,14) " | |
| 236 " FROM sqlite_master WHERE type='table' AND name!='sqlite_sequence'" | |
| 237 " AND coalesce(rootpage,1)>0" | |
| 238 ); | |
| 239 if( rc!=SQLITE_OK ) goto end_of_vacuum; | |
| 240 rc = execExecSql(db, pzErrMsg, | |
| 241 "SELECT 'CREATE INDEX vacuum_db.' || substr(sql,14)" | |
| 242 " FROM sqlite_master WHERE sql LIKE 'CREATE INDEX %' "); | |
| 243 if( rc!=SQLITE_OK ) goto end_of_vacuum; | |
| 244 rc = execExecSql(db, pzErrMsg, | |
| 245 "SELECT 'CREATE UNIQUE INDEX vacuum_db.' || substr(sql,21) " | |
| 246 " FROM sqlite_master WHERE sql LIKE 'CREATE UNIQUE INDEX %'"); | |
| 247 if( rc!=SQLITE_OK ) goto end_of_vacuum; | |
| 248 | |
| 249 /* Loop through the tables in the main database. For each, do | |
| 250 ** an "INSERT INTO vacuum_db.xxx SELECT * FROM main.xxx;" to copy | |
| 251 ** the contents to the temporary database. | |
| 252 */ | |
| 253 assert( (db->flags & SQLITE_Vacuum)==0 ); | |
| 254 db->flags |= SQLITE_Vacuum; | |
| 255 rc = execExecSql(db, pzErrMsg, | |
| 256 "SELECT 'INSERT INTO vacuum_db.' || quote(name) " | |
| 257 "|| ' SELECT * FROM main.' || quote(name) || ';'" | |
| 258 "FROM main.sqlite_master " | |
| 259 "WHERE type = 'table' AND name!='sqlite_sequence' " | |
| 260 " AND coalesce(rootpage,1)>0" | |
| 261 ); | |
| 262 assert( (db->flags & SQLITE_Vacuum)!=0 ); | |
| 263 db->flags &= ~SQLITE_Vacuum; | |
| 264 if( rc!=SQLITE_OK ) goto end_of_vacuum; | |
| 265 | |
| 266 /* Copy over the sequence table | |
| 267 */ | |
| 268 rc = execExecSql(db, pzErrMsg, | |
| 269 "SELECT 'DELETE FROM vacuum_db.' || quote(name) || ';' " | |
| 270 "FROM vacuum_db.sqlite_master WHERE name='sqlite_sequence' " | |
| 271 ); | |
| 272 if( rc!=SQLITE_OK ) goto end_of_vacuum; | |
| 273 rc = execExecSql(db, pzErrMsg, | |
| 274 "SELECT 'INSERT INTO vacuum_db.' || quote(name) " | |
| 275 "|| ' SELECT * FROM main.' || quote(name) || ';' " | |
| 276 "FROM vacuum_db.sqlite_master WHERE name=='sqlite_sequence';" | |
| 277 ); | |
| 278 if( rc!=SQLITE_OK ) goto end_of_vacuum; | |
| 279 | |
| 280 | |
| 281 /* Copy the triggers, views, and virtual tables from the main database | |
| 282 ** over to the temporary database. None of these objects has any | |
| 283 ** associated storage, so all we have to do is copy their entries | |
| 284 ** from the SQLITE_MASTER table. | |
| 285 */ | |
| 286 rc = execSql(db, pzErrMsg, | |
| 287 "INSERT INTO vacuum_db.sqlite_master " | |
| 288 " SELECT type, name, tbl_name, rootpage, sql" | |
| 289 " FROM main.sqlite_master" | |
| 290 " WHERE type='view' OR type='trigger'" | |
| 291 " OR (type='table' AND rootpage=0)" | |
| 292 ); | |
| 293 if( rc ) goto end_of_vacuum; | |
| 294 | |
| 295 /* At this point, there is a write transaction open on both the | |
| 296 ** vacuum database and the main database. Assuming no error occurs, | |
| 297 ** both transactions are closed by this block - the main database | |
| 298 ** transaction by sqlite3BtreeCopyFile() and the other by an explicit | |
| 299 ** call to sqlite3BtreeCommit(). | |
| 300 */ | |
| 301 { | |
| 302 u32 meta; | |
| 303 int i; | |
| 304 | |
| 305 /* This array determines which meta meta values are preserved in the | |
| 306 ** vacuum. Even entries are the meta value number and odd entries | |
| 307 ** are an increment to apply to the meta value after the vacuum. | |
| 308 ** The increment is used to increase the schema cookie so that other | |
| 309 ** connections to the same database will know to reread the schema. | |
| 310 */ | |
| 311 static const unsigned char aCopy[] = { | |
| 312 BTREE_SCHEMA_VERSION, 1, /* Add one to the old schema cookie */ | |
| 313 BTREE_DEFAULT_CACHE_SIZE, 0, /* Preserve the default page cache size */ | |
| 314 BTREE_TEXT_ENCODING, 0, /* Preserve the text encoding */ | |
| 315 BTREE_USER_VERSION, 0, /* Preserve the user version */ | |
| 316 BTREE_APPLICATION_ID, 0, /* Preserve the application id */ | |
| 317 }; | |
| 318 | |
| 319 assert( 1==sqlite3BtreeIsInTrans(pTemp) ); | |
| 320 assert( 1==sqlite3BtreeIsInTrans(pMain) ); | |
| 321 | |
| 322 /* Copy Btree meta values */ | |
| 323 for(i=0; i<ArraySize(aCopy); i+=2){ | |
| 324 /* GetMeta() and UpdateMeta() cannot fail in this context because | |
| 325 ** we already have page 1 loaded into cache and marked dirty. */ | |
| 326 sqlite3BtreeGetMeta(pMain, aCopy[i], &meta); | |
| 327 rc = sqlite3BtreeUpdateMeta(pTemp, aCopy[i], meta+aCopy[i+1]); | |
| 328 if( NEVER(rc!=SQLITE_OK) ) goto end_of_vacuum; | |
| 329 } | |
| 330 | |
| 331 rc = sqlite3BtreeCopyFile(pMain, pTemp); | |
| 332 if( rc!=SQLITE_OK ) goto end_of_vacuum; | |
| 333 rc = sqlite3BtreeCommit(pTemp); | |
| 334 if( rc!=SQLITE_OK ) goto end_of_vacuum; | |
| 335 #ifndef SQLITE_OMIT_AUTOVACUUM | |
| 336 sqlite3BtreeSetAutoVacuum(pMain, sqlite3BtreeGetAutoVacuum(pTemp)); | |
| 337 #endif | |
| 338 } | |
| 339 | |
| 340 assert( rc==SQLITE_OK ); | |
| 341 rc = sqlite3BtreeSetPageSize(pMain, sqlite3BtreeGetPageSize(pTemp), nRes,1); | |
| 342 | |
| 343 end_of_vacuum: | |
| 344 /* Restore the original value of db->flags */ | |
| 345 db->flags = saved_flags; | |
| 346 db->nChange = saved_nChange; | |
| 347 db->nTotalChange = saved_nTotalChange; | |
| 348 db->xTrace = saved_xTrace; | |
| 349 sqlite3BtreeSetPageSize(pMain, -1, -1, 1); | |
| 350 | |
| 351 /* Currently there is an SQL level transaction open on the vacuum | |
| 352 ** database. No locks are held on any other files (since the main file | |
| 353 ** was committed at the btree level). So it safe to end the transaction | |
| 354 ** by manually setting the autoCommit flag to true and detaching the | |
| 355 ** vacuum database. The vacuum_db journal file is deleted when the pager | |
| 356 ** is closed by the DETACH. | |
| 357 */ | |
| 358 db->autoCommit = 1; | |
| 359 | |
| 360 if( pDb ){ | |
| 361 sqlite3BtreeClose(pDb->pBt); | |
| 362 pDb->pBt = 0; | |
| 363 pDb->pSchema = 0; | |
| 364 } | |
| 365 | |
| 366 /* This both clears the schemas and reduces the size of the db->aDb[] | |
| 367 ** array. */ | |
| 368 sqlite3ResetAllSchemasOfConnection(db); | |
| 369 | |
| 370 return rc; | |
| 371 } | |
| 372 | |
| 373 #endif /* SQLITE_OMIT_VACUUM && SQLITE_OMIT_ATTACH */ | |
| OLD | NEW |
|---|
Chromium Code Reviews
Issue 2846743003:
[sql] Remove SQLite 3.10.2 reference directory. (Closed)
