000001 /* 000002 ** 2003 April 6 000003 ** 000004 ** The author disclaims copyright to this source code. In place of 000005 ** a legal notice, here is a blessing: 000006 ** 000007 ** May you do good and not evil. 000008 ** May you find forgiveness for yourself and forgive others. 000009 ** May you share freely, never taking more than you give. 000010 ** 000011 ************************************************************************* 000012 ** This file contains code used to implement the VACUUM command. 000013 ** 000014 ** Most of the code in this file may be omitted by defining the 000015 ** SQLITE_OMIT_VACUUM macro. 000016 */ 000017 #include "sqliteInt.h" 000018 #include "vdbeInt.h" 000019 000020 #if !defined(SQLITE_OMIT_VACUUM) && !defined(SQLITE_OMIT_ATTACH) 000021 000022 /* 000023 ** Execute zSql on database db. 000024 ** 000025 ** If zSql returns rows, then each row will have exactly one 000026 ** column. (This will only happen if zSql begins with "SELECT".) 000027 ** Take each row of result and call execSql() again recursively. 000028 ** 000029 ** The execSqlF() routine does the same thing, except it accepts 000030 ** a format string as its third argument 000031 */ 000032 static int execSql(sqlite3 *db, char **pzErrMsg, const char *zSql){ 000033 sqlite3_stmt *pStmt; 000034 int rc; 000035 000036 /* printf("SQL: [%s]\n", zSql); fflush(stdout); */ 000037 rc = sqlite3_prepare_v2(db, zSql, -1, &pStmt, 0); 000038 if( rc!=SQLITE_OK ) return rc; 000039 while( SQLITE_ROW==(rc = sqlite3_step(pStmt)) ){ 000040 const char *zSubSql = (const char*)sqlite3_column_text(pStmt,0); 000041 assert( sqlite3_strnicmp(zSql,"SELECT",6)==0 ); 000042 /* The secondary SQL must be one of CREATE TABLE, CREATE INDEX, 000043 ** or INSERT. Historically there have been attacks that first 000044 ** corrupt the sqlite_master.sql field with other kinds of statements 000045 ** then run VACUUM to get those statements to execute at inappropriate 000046 ** times. */ 000047 if( zSubSql 000048 && (strncmp(zSubSql,"CRE",3)==0 || strncmp(zSubSql,"INS",3)==0) 000049 ){ 000050 rc = execSql(db, pzErrMsg, zSubSql); 000051 if( rc!=SQLITE_OK ) break; 000052 } 000053 } 000054 assert( rc!=SQLITE_ROW ); 000055 if( rc==SQLITE_DONE ) rc = SQLITE_OK; 000056 if( rc ){ 000057 sqlite3SetString(pzErrMsg, db, sqlite3_errmsg(db)); 000058 } 000059 (void)sqlite3_finalize(pStmt); 000060 return rc; 000061 } 000062 static int execSqlF(sqlite3 *db, char **pzErrMsg, const char *zSql, ...){ 000063 char *z; 000064 va_list ap; 000065 int rc; 000066 va_start(ap, zSql); 000067 z = sqlite3VMPrintf(db, zSql, ap); 000068 va_end(ap); 000069 if( z==0 ) return SQLITE_NOMEM; 000070 rc = execSql(db, pzErrMsg, z); 000071 sqlite3DbFree(db, z); 000072 return rc; 000073 } 000074 000075 /* 000076 ** The VACUUM command is used to clean up the database, 000077 ** collapse free space, etc. It is modelled after the VACUUM command 000078 ** in PostgreSQL. The VACUUM command works as follows: 000079 ** 000080 ** (1) Create a new transient database file 000081 ** (2) Copy all content from the database being vacuumed into 000082 ** the new transient database file 000083 ** (3) Copy content from the transient database back into the 000084 ** original database. 000085 ** 000086 ** The transient database requires temporary disk space approximately 000087 ** equal to the size of the original database. The copy operation of 000088 ** step (3) requires additional temporary disk space approximately equal 000089 ** to the size of the original database for the rollback journal. 000090 ** Hence, temporary disk space that is approximately 2x the size of the 000091 ** original database is required. Every page of the database is written 000092 ** approximately 3 times: Once for step (2) and twice for step (3). 000093 ** Two writes per page are required in step (3) because the original 000094 ** database content must be written into the rollback journal prior to 000095 ** overwriting the database with the vacuumed content. 000096 ** 000097 ** Only 1x temporary space and only 1x writes would be required if 000098 ** the copy of step (3) were replaced by deleting the original database 000099 ** and renaming the transient database as the original. But that will 000100 ** not work if other processes are attached to the original database. 000101 ** And a power loss in between deleting the original and renaming the 000102 ** transient would cause the database file to appear to be deleted 000103 ** following reboot. 000104 */ 000105 void sqlite3Vacuum(Parse *pParse, Token *pNm, Expr *pInto){ 000106 Vdbe *v = sqlite3GetVdbe(pParse); 000107 int iDb = 0; 000108 if( v==0 ) goto build_vacuum_end; 000109 if( pParse->nErr ) goto build_vacuum_end; 000110 if( pNm ){ 000111 #ifndef SQLITE_BUG_COMPATIBLE_20160819 000112 /* Default behavior: Report an error if the argument to VACUUM is 000113 ** not recognized */ 000114 iDb = sqlite3TwoPartName(pParse, pNm, pNm, &pNm); 000115 if( iDb<0 ) goto build_vacuum_end; 000116 #else 000117 /* When SQLITE_BUG_COMPATIBLE_20160819 is defined, unrecognized arguments 000118 ** to VACUUM are silently ignored. This is a back-out of a bug fix that 000119 ** occurred on 2016-08-19 (https://www.sqlite.org/src/info/083f9e6270). 000120 ** The buggy behavior is required for binary compatibility with some 000121 ** legacy applications. */ 000122 iDb = sqlite3FindDb(pParse->db, pNm); 000123 if( iDb<0 ) iDb = 0; 000124 #endif 000125 } 000126 if( iDb!=1 ){ 000127 int iIntoReg = 0; 000128 if( pInto && sqlite3ResolveSelfReference(pParse,0,0,pInto,0)==0 ){ 000129 iIntoReg = ++pParse->nMem; 000130 sqlite3ExprCode(pParse, pInto, iIntoReg); 000131 } 000132 sqlite3VdbeAddOp2(v, OP_Vacuum, iDb, iIntoReg); 000133 sqlite3VdbeUsesBtree(v, iDb); 000134 } 000135 build_vacuum_end: 000136 sqlite3ExprDelete(pParse->db, pInto); 000137 return; 000138 } 000139 000140 /* 000141 ** This routine implements the OP_Vacuum opcode of the VDBE. 000142 */ 000143 SQLITE_NOINLINE int sqlite3RunVacuum( 000144 char **pzErrMsg, /* Write error message here */ 000145 sqlite3 *db, /* Database connection */ 000146 int iDb, /* Which attached DB to vacuum */ 000147 sqlite3_value *pOut /* Write results here, if not NULL. VACUUM INTO */ 000148 ){ 000149 int rc = SQLITE_OK; /* Return code from service routines */ 000150 Btree *pMain; /* The database being vacuumed */ 000151 Btree *pTemp; /* The temporary database we vacuum into */ 000152 u32 saved_mDbFlags; /* Saved value of db->mDbFlags */ 000153 u64 saved_flags; /* Saved value of db->flags */ 000154 int saved_nChange; /* Saved value of db->nChange */ 000155 int saved_nTotalChange; /* Saved value of db->nTotalChange */ 000156 u32 saved_openFlags; /* Saved value of db->openFlags */ 000157 u8 saved_mTrace; /* Saved trace settings */ 000158 Db *pDb = 0; /* Database to detach at end of vacuum */ 000159 int isMemDb; /* True if vacuuming a :memory: database */ 000160 int nRes; /* Bytes of reserved space at the end of each page */ 000161 int nDb; /* Number of attached databases */ 000162 const char *zDbMain; /* Schema name of database to vacuum */ 000163 const char *zOut; /* Name of output file */ 000164 000165 if( !db->autoCommit ){ 000166 sqlite3SetString(pzErrMsg, db, "cannot VACUUM from within a transaction"); 000167 return SQLITE_ERROR; /* IMP: R-12218-18073 */ 000168 } 000169 if( db->nVdbeActive>1 ){ 000170 sqlite3SetString(pzErrMsg, db,"cannot VACUUM - SQL statements in progress"); 000171 return SQLITE_ERROR; /* IMP: R-15610-35227 */ 000172 } 000173 saved_openFlags = db->openFlags; 000174 if( pOut ){ 000175 if( sqlite3_value_type(pOut)!=SQLITE_TEXT ){ 000176 sqlite3SetString(pzErrMsg, db, "non-text filename"); 000177 return SQLITE_ERROR; 000178 } 000179 zOut = (const char*)sqlite3_value_text(pOut); 000180 db->openFlags &= ~SQLITE_OPEN_READONLY; 000181 db->openFlags |= SQLITE_OPEN_CREATE|SQLITE_OPEN_READWRITE; 000182 }else{ 000183 zOut = ""; 000184 } 000185 000186 /* Save the current value of the database flags so that it can be 000187 ** restored before returning. Then set the writable-schema flag, and 000188 ** disable CHECK and foreign key constraints. */ 000189 saved_flags = db->flags; 000190 saved_mDbFlags = db->mDbFlags; 000191 saved_nChange = db->nChange; 000192 saved_nTotalChange = db->nTotalChange; 000193 saved_mTrace = db->mTrace; 000194 db->flags |= SQLITE_WriteSchema | SQLITE_IgnoreChecks; 000195 db->mDbFlags |= DBFLAG_PreferBuiltin | DBFLAG_Vacuum; 000196 db->flags &= ~(u64)(SQLITE_ForeignKeys | SQLITE_ReverseOrder 000197 | SQLITE_Defensive | SQLITE_CountRows); 000198 db->mTrace = 0; 000199 000200 zDbMain = db->aDb[iDb].zDbSName; 000201 pMain = db->aDb[iDb].pBt; 000202 isMemDb = sqlite3PagerIsMemdb(sqlite3BtreePager(pMain)); 000203 000204 /* Attach the temporary database as 'vacuum_db'. The synchronous pragma 000205 ** can be set to 'off' for this file, as it is not recovered if a crash 000206 ** occurs anyway. The integrity of the database is maintained by a 000207 ** (possibly synchronous) transaction opened on the main database before 000208 ** sqlite3BtreeCopyFile() is called. 000209 ** 000210 ** An optimisation would be to use a non-journaled pager. 000211 ** (Later:) I tried setting "PRAGMA vacuum_db.journal_mode=OFF" but 000212 ** that actually made the VACUUM run slower. Very little journalling 000213 ** actually occurs when doing a vacuum since the vacuum_db is initially 000214 ** empty. Only the journal header is written. Apparently it takes more 000215 ** time to parse and run the PRAGMA to turn journalling off than it does 000216 ** to write the journal header file. 000217 */ 000218 nDb = db->nDb; 000219 rc = execSqlF(db, pzErrMsg, "ATTACH %Q AS vacuum_db", zOut); 000220 db->openFlags = saved_openFlags; 000221 if( rc!=SQLITE_OK ) goto end_of_vacuum; 000222 assert( (db->nDb-1)==nDb ); 000223 pDb = &db->aDb[nDb]; 000224 assert( strcmp(pDb->zDbSName,"vacuum_db")==0 ); 000225 pTemp = pDb->pBt; 000226 if( pOut ){ 000227 sqlite3_file *id = sqlite3PagerFile(sqlite3BtreePager(pTemp)); 000228 i64 sz = 0; 000229 if( id->pMethods!=0 && (sqlite3OsFileSize(id, &sz)!=SQLITE_OK || sz>0) ){ 000230 rc = SQLITE_ERROR; 000231 sqlite3SetString(pzErrMsg, db, "output file already exists"); 000232 goto end_of_vacuum; 000233 } 000234 db->mDbFlags |= DBFLAG_VacuumInto; 000235 } 000236 nRes = sqlite3BtreeGetOptimalReserve(pMain); 000237 000238 /* A VACUUM cannot change the pagesize of an encrypted database. */ 000239 #ifdef SQLITE_HAS_CODEC 000240 if( db->nextPagesize ){ 000241 extern void sqlite3CodecGetKey(sqlite3*, int, void**, int*); 000242 int nKey; 000243 char *zKey; 000244 sqlite3CodecGetKey(db, iDb, (void**)&zKey, &nKey); 000245 if( nKey ) db->nextPagesize = 0; 000246 } 000247 #endif 000248 000249 sqlite3BtreeSetCacheSize(pTemp, db->aDb[iDb].pSchema->cache_size); 000250 sqlite3BtreeSetSpillSize(pTemp, sqlite3BtreeSetSpillSize(pMain,0)); 000251 sqlite3BtreeSetPagerFlags(pTemp, PAGER_SYNCHRONOUS_OFF|PAGER_CACHESPILL); 000252 000253 /* Begin a transaction and take an exclusive lock on the main database 000254 ** file. This is done before the sqlite3BtreeGetPageSize(pMain) call below, 000255 ** to ensure that we do not try to change the page-size on a WAL database. 000256 */ 000257 rc = execSql(db, pzErrMsg, "BEGIN"); 000258 if( rc!=SQLITE_OK ) goto end_of_vacuum; 000259 rc = sqlite3BtreeBeginTrans(pMain, pOut==0 ? 2 : 0, 0); 000260 if( rc!=SQLITE_OK ) goto end_of_vacuum; 000261 000262 /* Do not attempt to change the page size for a WAL database */ 000263 if( sqlite3PagerGetJournalMode(sqlite3BtreePager(pMain)) 000264 ==PAGER_JOURNALMODE_WAL ){ 000265 db->nextPagesize = 0; 000266 } 000267 000268 if( sqlite3BtreeSetPageSize(pTemp, sqlite3BtreeGetPageSize(pMain), nRes, 0) 000269 || (!isMemDb && sqlite3BtreeSetPageSize(pTemp, db->nextPagesize, nRes, 0)) 000270 || NEVER(db->mallocFailed) 000271 ){ 000272 rc = SQLITE_NOMEM_BKPT; 000273 goto end_of_vacuum; 000274 } 000275 000276 #ifndef SQLITE_OMIT_AUTOVACUUM 000277 sqlite3BtreeSetAutoVacuum(pTemp, db->nextAutovac>=0 ? db->nextAutovac : 000278 sqlite3BtreeGetAutoVacuum(pMain)); 000279 #endif 000280 000281 /* Query the schema of the main database. Create a mirror schema 000282 ** in the temporary database. 000283 */ 000284 db->init.iDb = nDb; /* force new CREATE statements into vacuum_db */ 000285 rc = execSqlF(db, pzErrMsg, 000286 "SELECT sql FROM \"%w\".sqlite_master" 000287 " WHERE type='table'AND name<>'sqlite_sequence'" 000288 " AND coalesce(rootpage,1)>0", 000289 zDbMain 000290 ); 000291 if( rc!=SQLITE_OK ) goto end_of_vacuum; 000292 rc = execSqlF(db, pzErrMsg, 000293 "SELECT sql FROM \"%w\".sqlite_master" 000294 " WHERE type='index'", 000295 zDbMain 000296 ); 000297 if( rc!=SQLITE_OK ) goto end_of_vacuum; 000298 db->init.iDb = 0; 000299 000300 /* Loop through the tables in the main database. For each, do 000301 ** an "INSERT INTO vacuum_db.xxx SELECT * FROM main.xxx;" to copy 000302 ** the contents to the temporary database. 000303 */ 000304 rc = execSqlF(db, pzErrMsg, 000305 "SELECT'INSERT INTO vacuum_db.'||quote(name)" 000306 "||' SELECT*FROM\"%w\".'||quote(name)" 000307 "FROM vacuum_db.sqlite_master " 000308 "WHERE type='table'AND coalesce(rootpage,1)>0", 000309 zDbMain 000310 ); 000311 assert( (db->mDbFlags & DBFLAG_Vacuum)!=0 ); 000312 db->mDbFlags &= ~DBFLAG_Vacuum; 000313 if( rc!=SQLITE_OK ) goto end_of_vacuum; 000314 000315 /* Copy the triggers, views, and virtual tables from the main database 000316 ** over to the temporary database. None of these objects has any 000317 ** associated storage, so all we have to do is copy their entries 000318 ** from the SQLITE_MASTER table. 000319 */ 000320 rc = execSqlF(db, pzErrMsg, 000321 "INSERT INTO vacuum_db.sqlite_master" 000322 " SELECT*FROM \"%w\".sqlite_master" 000323 " WHERE type IN('view','trigger')" 000324 " OR(type='table'AND rootpage=0)", 000325 zDbMain 000326 ); 000327 if( rc ) goto end_of_vacuum; 000328 000329 /* At this point, there is a write transaction open on both the 000330 ** vacuum database and the main database. Assuming no error occurs, 000331 ** both transactions are closed by this block - the main database 000332 ** transaction by sqlite3BtreeCopyFile() and the other by an explicit 000333 ** call to sqlite3BtreeCommit(). 000334 */ 000335 { 000336 u32 meta; 000337 int i; 000338 000339 /* This array determines which meta meta values are preserved in the 000340 ** vacuum. Even entries are the meta value number and odd entries 000341 ** are an increment to apply to the meta value after the vacuum. 000342 ** The increment is used to increase the schema cookie so that other 000343 ** connections to the same database will know to reread the schema. 000344 */ 000345 static const unsigned char aCopy[] = { 000346 BTREE_SCHEMA_VERSION, 1, /* Add one to the old schema cookie */ 000347 BTREE_DEFAULT_CACHE_SIZE, 0, /* Preserve the default page cache size */ 000348 BTREE_TEXT_ENCODING, 0, /* Preserve the text encoding */ 000349 BTREE_USER_VERSION, 0, /* Preserve the user version */ 000350 BTREE_APPLICATION_ID, 0, /* Preserve the application id */ 000351 }; 000352 000353 assert( 1==sqlite3BtreeIsInTrans(pTemp) ); 000354 assert( pOut!=0 || 1==sqlite3BtreeIsInTrans(pMain) ); 000355 000356 /* Copy Btree meta values */ 000357 for(i=0; i<ArraySize(aCopy); i+=2){ 000358 /* GetMeta() and UpdateMeta() cannot fail in this context because 000359 ** we already have page 1 loaded into cache and marked dirty. */ 000360 sqlite3BtreeGetMeta(pMain, aCopy[i], &meta); 000361 rc = sqlite3BtreeUpdateMeta(pTemp, aCopy[i], meta+aCopy[i+1]); 000362 if( NEVER(rc!=SQLITE_OK) ) goto end_of_vacuum; 000363 } 000364 000365 if( pOut==0 ){ 000366 rc = sqlite3BtreeCopyFile(pMain, pTemp); 000367 } 000368 if( rc!=SQLITE_OK ) goto end_of_vacuum; 000369 rc = sqlite3BtreeCommit(pTemp); 000370 if( rc!=SQLITE_OK ) goto end_of_vacuum; 000371 #ifndef SQLITE_OMIT_AUTOVACUUM 000372 if( pOut==0 ){ 000373 sqlite3BtreeSetAutoVacuum(pMain, sqlite3BtreeGetAutoVacuum(pTemp)); 000374 } 000375 #endif 000376 } 000377 000378 assert( rc==SQLITE_OK ); 000379 if( pOut==0 ){ 000380 rc = sqlite3BtreeSetPageSize(pMain, sqlite3BtreeGetPageSize(pTemp), nRes,1); 000381 } 000382 000383 end_of_vacuum: 000384 /* Restore the original value of db->flags */ 000385 db->init.iDb = 0; 000386 db->mDbFlags = saved_mDbFlags; 000387 db->flags = saved_flags; 000388 db->nChange = saved_nChange; 000389 db->nTotalChange = saved_nTotalChange; 000390 db->mTrace = saved_mTrace; 000391 sqlite3BtreeSetPageSize(pMain, -1, -1, 1); 000392 000393 /* Currently there is an SQL level transaction open on the vacuum 000394 ** database. No locks are held on any other files (since the main file 000395 ** was committed at the btree level). So it safe to end the transaction 000396 ** by manually setting the autoCommit flag to true and detaching the 000397 ** vacuum database. The vacuum_db journal file is deleted when the pager 000398 ** is closed by the DETACH. 000399 */ 000400 db->autoCommit = 1; 000401 000402 if( pDb ){ 000403 sqlite3BtreeClose(pDb->pBt); 000404 pDb->pBt = 0; 000405 pDb->pSchema = 0; 000406 } 000407 000408 /* This both clears the schemas and reduces the size of the db->aDb[] 000409 ** array. */ 000410 sqlite3ResetAllSchemasOfConnection(db); 000411 000412 return rc; 000413 } 000414 000415 #endif /* SQLITE_OMIT_VACUUM && SQLITE_OMIT_ATTACH */