AryaWu/sqlite
0
1/*2** 2003 April 63**4** The author disclaims copyright to this source code. In place of5** 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 the15** 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/*23** Execute zSql on database db.24**25** If zSql returns rows, then each row will have exactly one26** column. (This will only happen if zSql begins with "SELECT".)27** Take each row of result and call execSql() again recursively.28**29** The execSqlF() routine does the same thing, except it accepts30** a format string as its third argument31*/32static int execSql(sqlite3 *db, char **pzErrMsg, const char *zSql){33 sqlite3_stmt *pStmt;34 int rc;35 36 /* printf("SQL: [%s]\n", zSql); fflush(stdout); */37 rc = sqlite3_prepare_v2(db, zSql, -1, &pStmt, 0);38 if( rc!=SQLITE_OK ) return rc;39 while( SQLITE_ROW==(rc = sqlite3_step(pStmt)) ){40 const char *zSubSql = (const char*)sqlite3_column_text(pStmt,0);41 assert( sqlite3_strnicmp(zSql,"SELECT",6)==0 );42 /* The secondary SQL must be one of CREATE TABLE, CREATE INDEX,43 ** or INSERT. Historically there have been attacks that first44 ** corrupt the sqlite_schema.sql field with other kinds of statements45 ** then run VACUUM to get those statements to execute at inappropriate46 ** times. */47 if( zSubSql48 && (strncmp(zSubSql,"CRE",3)==0 || strncmp(zSubSql,"INS",3)==0)49 ){50 rc = execSql(db, pzErrMsg, zSubSql);51 if( rc!=SQLITE_OK ) break;52 }53 }54 assert( rc!=SQLITE_ROW );55 if( rc==SQLITE_DONE ) rc = SQLITE_OK;56 if( rc ){57 sqlite3SetString(pzErrMsg, db, sqlite3_errmsg(db));58 }59 (void)sqlite3_finalize(pStmt);60 return rc;61}62static int execSqlF(sqlite3 *db, char **pzErrMsg, const char *zSql, ...){63 char *z;64 va_list ap;65 int rc;66 va_start(ap, zSql);67 z = sqlite3VMPrintf(db, zSql, ap);68 va_end(ap);69 if( z==0 ) return SQLITE_NOMEM;70 rc = execSql(db, pzErrMsg, z);71 sqlite3DbFree(db, z);72 return rc;73}74 75/*76** The VACUUM command is used to clean up the database,77** collapse free space, etc. It is modelled after the VACUUM command78** in PostgreSQL. The VACUUM command works as follows:79**80** (1) Create a new transient database file81** (2) Copy all content from the database being vacuumed into82** the new transient database file83** (3) Copy content from the transient database back into the84** original database.85**86** The transient database requires temporary disk space approximately87** equal to the size of the original database. The copy operation of88** step (3) requires additional temporary disk space approximately equal89** to the size of the original database for the rollback journal.90** Hence, temporary disk space that is approximately 2x the size of the91** original database is required. Every page of the database is written92** approximately 3 times: Once for step (2) and twice for step (3).93** Two writes per page are required in step (3) because the original94** database content must be written into the rollback journal prior to95** overwriting the database with the vacuumed content.96**97** Only 1x temporary space and only 1x writes would be required if98** the copy of step (3) were replaced by deleting the original database99** and renaming the transient database as the original. But that will100** not work if other processes are attached to the original database.101** And a power loss in between deleting the original and renaming the102** transient would cause the database file to appear to be deleted103** following reboot.104*/105void sqlite3Vacuum(Parse *pParse, Token *pNm, Expr *pInto){106 Vdbe *v = sqlite3GetVdbe(pParse);107 int iDb = 0;108 if( v==0 ) goto build_vacuum_end;109 if( pParse->nErr ) goto build_vacuum_end;110 if( pNm ){111#ifndef SQLITE_BUG_COMPATIBLE_20160819112 /* Default behavior: Report an error if the argument to VACUUM is113 ** not recognized */114 iDb = sqlite3TwoPartName(pParse, pNm, pNm, &pNm);115 if( iDb<0 ) goto build_vacuum_end;116#else117 /* When SQLITE_BUG_COMPATIBLE_20160819 is defined, unrecognized arguments118 ** to VACUUM are silently ignored. This is a back-out of a bug fix that119 ** occurred on 2016-08-19 (https://sqlite.org/src/info/083f9e6270).120 ** The buggy behavior is required for binary compatibility with some121 ** legacy applications. */122 iDb = sqlite3FindDb(pParse->db, pNm);123 if( iDb<0 ) iDb = 0;124#endif125 }126 if( iDb!=1 ){127 int iIntoReg = 0;128 if( pInto && sqlite3ResolveSelfReference(pParse,0,0,pInto,0)==0 ){129 iIntoReg = ++pParse->nMem;130 sqlite3ExprCode(pParse, pInto, iIntoReg);131 }132 sqlite3VdbeAddOp2(v, OP_Vacuum, iDb, iIntoReg);133 sqlite3VdbeUsesBtree(v, iDb);134 }135build_vacuum_end:136 sqlite3ExprDelete(pParse->db, pInto);137 return;138}139 140/*141** This routine implements the OP_Vacuum opcode of the VDBE.142*/143SQLITE_NOINLINE int sqlite3RunVacuum(144 char **pzErrMsg, /* Write error message here */145 sqlite3 *db, /* Database connection */146 int iDb, /* Which attached DB to vacuum */147 sqlite3_value *pOut /* Write results here, if not NULL. VACUUM INTO */148){149 int rc = SQLITE_OK; /* Return code from service routines */150 Btree *pMain; /* The database being vacuumed */151 Btree *pTemp; /* The temporary database we vacuum into */152 u32 saved_mDbFlags; /* Saved value of db->mDbFlags */153 u64 saved_flags; /* Saved value of db->flags */154 i64 saved_nChange; /* Saved value of db->nChange */155 i64 saved_nTotalChange; /* Saved value of db->nTotalChange */156 u32 saved_openFlags; /* Saved value of db->openFlags */157 u8 saved_mTrace; /* Saved trace settings */158 Db *pDb = 0; /* Database to detach at end of vacuum */159 int isMemDb; /* True if vacuuming a :memory: database */160 int nRes; /* Bytes of reserved space at the end of each page */161 int nDb; /* Number of attached databases */162 const char *zDbMain; /* Schema name of database to vacuum */163 const char *zOut; /* Name of output file */164 u32 pgflags = PAGER_SYNCHRONOUS_OFF; /* sync flags for output db */165 u64 iRandom; /* Random value used for zDbVacuum[] */166 char zDbVacuum[42]; /* Name of the ATTACH-ed database used for vacuum */167 168 169 if( !db->autoCommit ){170 sqlite3SetString(pzErrMsg, db, "cannot VACUUM from within a transaction");171 return SQLITE_ERROR; /* IMP: R-12218-18073 */172 }173 if( db->nVdbeActive>1 ){174 sqlite3SetString(pzErrMsg, db,"cannot VACUUM - SQL statements in progress");175 return SQLITE_ERROR; /* IMP: R-15610-35227 */176 }177 saved_openFlags = db->openFlags;178 if( pOut ){179 if( sqlite3_value_type(pOut)!=SQLITE_TEXT ){180 sqlite3SetString(pzErrMsg, db, "non-text filename");181 return SQLITE_ERROR;182 }183 zOut = (const char*)sqlite3_value_text(pOut);184 db->openFlags &= ~SQLITE_OPEN_READONLY;185 db->openFlags |= SQLITE_OPEN_CREATE|SQLITE_OPEN_READWRITE;186 }else{187 zOut = "";188 }189 190 /* Save the current value of the database flags so that it can be 191 ** restored before returning. Then set the writable-schema flag, and192 ** disable CHECK and foreign key constraints. */193 saved_flags = db->flags;194 saved_mDbFlags = db->mDbFlags;195 saved_nChange = db->nChange;196 saved_nTotalChange = db->nTotalChange;197 saved_mTrace = db->mTrace;198 db->flags |= SQLITE_WriteSchema | SQLITE_IgnoreChecks | SQLITE_Comments199 | SQLITE_AttachCreate | SQLITE_AttachWrite;200 db->mDbFlags |= DBFLAG_PreferBuiltin | DBFLAG_Vacuum;201 db->flags &= ~(u64)(SQLITE_ForeignKeys | SQLITE_ReverseOrder202 | SQLITE_Defensive | SQLITE_CountRows);203 db->mTrace = 0;204 205 zDbMain = db->aDb[iDb].zDbSName;206 pMain = db->aDb[iDb].pBt;207 isMemDb = sqlite3PagerIsMemdb(sqlite3BtreePager(pMain));208 209 /* Attach the temporary database as 'vacuum_XXXXXX'. The synchronous pragma210 ** can be set to 'off' for this file, as it is not recovered if a crash211 ** occurs anyway. The integrity of the database is maintained by a212 ** (possibly synchronous) transaction opened on the main database before213 ** sqlite3BtreeCopyFile() is called.214 **215 ** An optimization would be to use a non-journaled pager.216 ** (Later:) I tried setting "PRAGMA vacuum_XXXXXX.journal_mode=OFF" but217 ** that actually made the VACUUM run slower. Very little journalling218 ** actually occurs when doing a vacuum since the vacuum_db is initially219 ** empty. Only the journal header is written. Apparently it takes more220 ** time to parse and run the PRAGMA to turn journalling off than it does221 ** to write the journal header file.222 */223 sqlite3_randomness(sizeof(iRandom),&iRandom);224 sqlite3_snprintf(sizeof(zDbVacuum), zDbVacuum, "vacuum_%016llx", iRandom);225 nDb = db->nDb;226 rc = execSqlF(db, pzErrMsg, "ATTACH %Q AS %s", zOut, zDbVacuum);227 db->openFlags = saved_openFlags;228 if( rc!=SQLITE_OK ) goto end_of_vacuum;229 assert( (db->nDb-1)==nDb );230 pDb = &db->aDb[nDb];231 assert( strcmp(pDb->zDbSName,zDbVacuum)==0 );232 pTemp = pDb->pBt;233 if( pOut ){234 sqlite3_file *id = sqlite3PagerFile(sqlite3BtreePager(pTemp));235 i64 sz = 0;236 if( id->pMethods!=0 && (sqlite3OsFileSize(id, &sz)!=SQLITE_OK || sz>0) ){237 rc = SQLITE_ERROR;238 sqlite3SetString(pzErrMsg, db, "output file already exists");239 goto end_of_vacuum;240 }241 db->mDbFlags |= DBFLAG_VacuumInto;242 243 /* For a VACUUM INTO, the pager-flags are set to the same values as244 ** they are for the database being vacuumed, except that PAGER_CACHESPILL245 ** is always set. */246 pgflags = db->aDb[iDb].safety_level | (db->flags & PAGER_FLAGS_MASK);247 }248 nRes = sqlite3BtreeGetRequestedReserve(pMain);249 250 sqlite3BtreeSetCacheSize(pTemp, db->aDb[iDb].pSchema->cache_size);251 sqlite3BtreeSetSpillSize(pTemp, sqlite3BtreeSetSpillSize(pMain,0));252 sqlite3BtreeSetPagerFlags(pTemp, pgflags|PAGER_CACHESPILL);253 254 /* Begin a transaction and take an exclusive lock on the main database255 ** file. This is done before the sqlite3BtreeGetPageSize(pMain) call below,256 ** to ensure that we do not try to change the page-size on a WAL database.257 */258 rc = execSql(db, pzErrMsg, "BEGIN");259 if( rc!=SQLITE_OK ) goto end_of_vacuum;260 rc = sqlite3BtreeBeginTrans(pMain, pOut==0 ? 2 : 0, 0);261 if( rc!=SQLITE_OK ) goto end_of_vacuum;262 263 /* Do not attempt to change the page size for a WAL database */264 if( sqlite3PagerGetJournalMode(sqlite3BtreePager(pMain))265 ==PAGER_JOURNALMODE_WAL266 && pOut==0267 ){268 db->nextPagesize = 0;269 }270 271 if( sqlite3BtreeSetPageSize(pTemp, sqlite3BtreeGetPageSize(pMain), nRes, 0)272 || (!isMemDb && sqlite3BtreeSetPageSize(pTemp, db->nextPagesize, nRes, 0))273 || NEVER(db->mallocFailed)274 ){275 rc = SQLITE_NOMEM_BKPT;276 goto end_of_vacuum;277 }278 279#ifndef SQLITE_OMIT_AUTOVACUUM280 sqlite3BtreeSetAutoVacuum(pTemp, db->nextAutovac>=0 ? db->nextAutovac :281 sqlite3BtreeGetAutoVacuum(pMain));282#endif283 284 /* Query the schema of the main database. Create a mirror schema285 ** in the temporary database.286 */287 db->init.iDb = nDb; /* force new CREATE statements into vacuum_db */288 rc = execSqlF(db, pzErrMsg,289 "SELECT sql FROM \"%w\".sqlite_schema"290 " WHERE type='table'AND name<>'sqlite_sequence'"291 " AND coalesce(rootpage,1)>0",292 zDbMain293 );294 if( rc!=SQLITE_OK ) goto end_of_vacuum;295 rc = execSqlF(db, pzErrMsg,296 "SELECT sql FROM \"%w\".sqlite_schema"297 " WHERE type='index'",298 zDbMain299 );300 if( rc!=SQLITE_OK ) goto end_of_vacuum;301 db->init.iDb = 0;302 303 /* Loop through the tables in the main database. For each, do304 ** an "INSERT INTO vacuum_db.xxx SELECT * FROM main.xxx;" to copy305 ** the contents to the temporary database.306 */307 rc = execSqlF(db, pzErrMsg,308 "SELECT'INSERT INTO %s.'||quote(name)"309 "||' SELECT*FROM\"%w\".'||quote(name)"310 "FROM %s.sqlite_schema "311 "WHERE type='table'AND coalesce(rootpage,1)>0",312 zDbVacuum, zDbMain, zDbVacuum313 );314 assert( (db->mDbFlags & DBFLAG_Vacuum)!=0 );315 db->mDbFlags &= ~DBFLAG_Vacuum;316 if( rc!=SQLITE_OK ) goto end_of_vacuum;317 318 /* Copy the triggers, views, and virtual tables from the main database319 ** over to the temporary database. None of these objects has any320 ** associated storage, so all we have to do is copy their entries321 ** from the schema table.322 */323 rc = execSqlF(db, pzErrMsg,324 "INSERT INTO %s.sqlite_schema"325 " SELECT*FROM \"%w\".sqlite_schema"326 " WHERE type IN('view','trigger')"327 " OR(type='table'AND rootpage=0)",328 zDbVacuum, zDbMain329 );330 if( rc ) goto end_of_vacuum;331 332 /* At this point, there is a write transaction open on both the 333 ** vacuum database and the main database. Assuming no error occurs,334 ** both transactions are closed by this block - the main database335 ** transaction by sqlite3BtreeCopyFile() and the other by an explicit336 ** call to sqlite3BtreeCommit().337 */338 {339 u32 meta;340 int i;341 342 /* This array determines which meta meta values are preserved in the343 ** vacuum. Even entries are the meta value number and odd entries344 ** are an increment to apply to the meta value after the vacuum.345 ** The increment is used to increase the schema cookie so that other346 ** connections to the same database will know to reread the schema.347 */348 static const unsigned char aCopy[] = {349 BTREE_SCHEMA_VERSION, 1, /* Add one to the old schema cookie */350 BTREE_DEFAULT_CACHE_SIZE, 0, /* Preserve the default page cache size */351 BTREE_TEXT_ENCODING, 0, /* Preserve the text encoding */352 BTREE_USER_VERSION, 0, /* Preserve the user version */353 BTREE_APPLICATION_ID, 0, /* Preserve the application id */354 };355 356 assert( SQLITE_TXN_WRITE==sqlite3BtreeTxnState(pTemp) );357 assert( pOut!=0 || SQLITE_TXN_WRITE==sqlite3BtreeTxnState(pMain) );358 359 /* Copy Btree meta values */360 for(i=0; i<ArraySize(aCopy); i+=2){361 /* GetMeta() and UpdateMeta() cannot fail in this context because362 ** we already have page 1 loaded into cache and marked dirty. */363 sqlite3BtreeGetMeta(pMain, aCopy[i], &meta);364 rc = sqlite3BtreeUpdateMeta(pTemp, aCopy[i], meta+aCopy[i+1]);365 if( NEVER(rc!=SQLITE_OK) ) goto end_of_vacuum;366 }367 368 if( pOut==0 ){369 rc = sqlite3BtreeCopyFile(pMain, pTemp);370 }371 if( rc!=SQLITE_OK ) goto end_of_vacuum;372 rc = sqlite3BtreeCommit(pTemp);373 if( rc!=SQLITE_OK ) goto end_of_vacuum;374#ifndef SQLITE_OMIT_AUTOVACUUM375 if( pOut==0 ){376 sqlite3BtreeSetAutoVacuum(pMain, sqlite3BtreeGetAutoVacuum(pTemp));377 }378#endif379 }380 381 assert( rc==SQLITE_OK );382 if( pOut==0 ){383 nRes = sqlite3BtreeGetRequestedReserve(pTemp);384 rc = sqlite3BtreeSetPageSize(pMain, sqlite3BtreeGetPageSize(pTemp), nRes,1);385 }386 387end_of_vacuum:388 /* Restore the original value of db->flags */389 db->init.iDb = 0;390 db->mDbFlags = saved_mDbFlags;391 db->flags = saved_flags;392 db->nChange = saved_nChange;393 db->nTotalChange = saved_nTotalChange;394 db->mTrace = saved_mTrace;395 sqlite3BtreeSetPageSize(pMain, -1, 0, 1);396 397 /* Currently there is an SQL level transaction open on the vacuum398 ** database. No locks are held on any other files (since the main file399 ** was committed at the btree level). So it safe to end the transaction400 ** by manually setting the autoCommit flag to true and detaching the401 ** vacuum database. The vacuum_db journal file is deleted when the pager402 ** is closed by the DETACH.403 */404 db->autoCommit = 1;405 406 if( pDb ){407 sqlite3BtreeClose(pDb->pBt);408 pDb->pBt = 0;409 pDb->pSchema = 0;410 }411 412 /* This both clears the schemas and reduces the size of the db->aDb[]413 ** array. */ 414 sqlite3ResetAllSchemasOfConnection(db);415 416 return rc;417}418 419#endif /* SQLITE_OMIT_VACUUM && SQLITE_OMIT_ATTACH */420