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_schema.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 i64 saved_nChange; /* Saved value of db->nChange */
000155 i64 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 u32 pgflags = PAGER_SYNCHRONOUS_OFF; /* sync flags for output db */
000165 u64 iRandom; /* Random value used for zDbVacuum[] */
000166 char zDbVacuum[42]; /* Name of the ATTACH-ed database used for vacuum */
000167
000168
000169 if( !db->autoCommit ){
000170 sqlite3SetString(pzErrMsg, db, "cannot VACUUM from within a transaction");
000171 return SQLITE_ERROR; /* IMP: R-12218-18073 */
000172 }
000173 if( db->nVdbeActive>1 ){
000174 sqlite3SetString(pzErrMsg, db,"cannot VACUUM - SQL statements in progress");
000175 return SQLITE_ERROR; /* IMP: R-15610-35227 */
000176 }
000177 saved_openFlags = db->openFlags;
000178 if( pOut ){
000179 if( sqlite3_value_type(pOut)!=SQLITE_TEXT ){
000180 sqlite3SetString(pzErrMsg, db, "non-text filename");
000181 return SQLITE_ERROR;
000182 }
000183 zOut = (const char*)sqlite3_value_text(pOut);
000184 db->openFlags &= ~SQLITE_OPEN_READONLY;
000185 db->openFlags |= SQLITE_OPEN_CREATE|SQLITE_OPEN_READWRITE;
000186 }else{
000187 zOut = "";
000188 }
000189
000190 /* Save the current value of the database flags so that it can be
000191 ** restored before returning. Then set the writable-schema flag, and
000192 ** disable CHECK and foreign key constraints. */
000193 saved_flags = db->flags;
000194 saved_mDbFlags = db->mDbFlags;
000195 saved_nChange = db->nChange;
000196 saved_nTotalChange = db->nTotalChange;
000197 saved_mTrace = db->mTrace;
000198 db->flags |= SQLITE_WriteSchema | SQLITE_IgnoreChecks;
000199 db->mDbFlags |= DBFLAG_PreferBuiltin | DBFLAG_Vacuum;
000200 db->flags &= ~(u64)(SQLITE_ForeignKeys | SQLITE_ReverseOrder
000201 | SQLITE_Defensive | SQLITE_CountRows);
000202 db->mTrace = 0;
000203
000204 zDbMain = db->aDb[iDb].zDbSName;
000205 pMain = db->aDb[iDb].pBt;
000206 isMemDb = sqlite3PagerIsMemdb(sqlite3BtreePager(pMain));
000207
000208 /* Attach the temporary database as 'vacuum_XXXXXX'. The synchronous pragma
000209 ** can be set to 'off' for this file, as it is not recovered if a crash
000210 ** occurs anyway. The integrity of the database is maintained by a
000211 ** (possibly synchronous) transaction opened on the main database before
000212 ** sqlite3BtreeCopyFile() is called.
000213 **
000214 ** An optimization would be to use a non-journaled pager.
000215 ** (Later:) I tried setting "PRAGMA vacuum_XXXXXX.journal_mode=OFF" but
000216 ** that actually made the VACUUM run slower. Very little journalling
000217 ** actually occurs when doing a vacuum since the vacuum_db is initially
000218 ** empty. Only the journal header is written. Apparently it takes more
000219 ** time to parse and run the PRAGMA to turn journalling off than it does
000220 ** to write the journal header file.
000221 */
000222 sqlite3_randomness(sizeof(iRandom),&iRandom);
000223 sqlite3_snprintf(sizeof(zDbVacuum), zDbVacuum, "vacuum_%016llx", iRandom);
000224 nDb = db->nDb;
000225 rc = execSqlF(db, pzErrMsg, "ATTACH %Q AS %s", zOut, zDbVacuum);
000226 db->openFlags = saved_openFlags;
000227 if( rc!=SQLITE_OK ) goto end_of_vacuum;
000228 assert( (db->nDb-1)==nDb );
000229 pDb = &db->aDb[nDb];
000230 assert( strcmp(pDb->zDbSName,zDbVacuum)==0 );
000231 pTemp = pDb->pBt;
000232 if( pOut ){
000233 sqlite3_file *id = sqlite3PagerFile(sqlite3BtreePager(pTemp));
000234 i64 sz = 0;
000235 if( id->pMethods!=0 && (sqlite3OsFileSize(id, &sz)!=SQLITE_OK || sz>0) ){
000236 rc = SQLITE_ERROR;
000237 sqlite3SetString(pzErrMsg, db, "output file already exists");
000238 goto end_of_vacuum;
000239 }
000240 db->mDbFlags |= DBFLAG_VacuumInto;
000241
000242 /* For a VACUUM INTO, the pager-flags are set to the same values as
000243 ** they are for the database being vacuumed, except that PAGER_CACHESPILL
000244 ** is always set. */
000245 pgflags = db->aDb[iDb].safety_level | (db->flags & PAGER_FLAGS_MASK);
000246 }
000247 nRes = sqlite3BtreeGetRequestedReserve(pMain);
000248
000249 sqlite3BtreeSetCacheSize(pTemp, db->aDb[iDb].pSchema->cache_size);
000250 sqlite3BtreeSetSpillSize(pTemp, sqlite3BtreeSetSpillSize(pMain,0));
000251 sqlite3BtreeSetPagerFlags(pTemp, pgflags|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 && pOut==0
000266 ){
000267 db->nextPagesize = 0;
000268 }
000269
000270 if( sqlite3BtreeSetPageSize(pTemp, sqlite3BtreeGetPageSize(pMain), nRes, 0)
000271 || (!isMemDb && sqlite3BtreeSetPageSize(pTemp, db->nextPagesize, nRes, 0))
000272 || NEVER(db->mallocFailed)
000273 ){
000274 rc = SQLITE_NOMEM_BKPT;
000275 goto end_of_vacuum;
000276 }
000277
000278 #ifndef SQLITE_OMIT_AUTOVACUUM
000279 sqlite3BtreeSetAutoVacuum(pTemp, db->nextAutovac>=0 ? db->nextAutovac :
000280 sqlite3BtreeGetAutoVacuum(pMain));
000281 #endif
000282
000283 /* Query the schema of the main database. Create a mirror schema
000284 ** in the temporary database.
000285 */
000286 db->init.iDb = nDb; /* force new CREATE statements into vacuum_db */
000287 rc = execSqlF(db, pzErrMsg,
000288 "SELECT sql FROM \"%w\".sqlite_schema"
000289 " WHERE type='table'AND name<>'sqlite_sequence'"
000290 " AND coalesce(rootpage,1)>0",
000291 zDbMain
000292 );
000293 if( rc!=SQLITE_OK ) goto end_of_vacuum;
000294 rc = execSqlF(db, pzErrMsg,
000295 "SELECT sql FROM \"%w\".sqlite_schema"
000296 " WHERE type='index'",
000297 zDbMain
000298 );
000299 if( rc!=SQLITE_OK ) goto end_of_vacuum;
000300 db->init.iDb = 0;
000301
000302 /* Loop through the tables in the main database. For each, do
000303 ** an "INSERT INTO vacuum_db.xxx SELECT * FROM main.xxx;" to copy
000304 ** the contents to the temporary database.
000305 */
000306 rc = execSqlF(db, pzErrMsg,
000307 "SELECT'INSERT INTO %s.'||quote(name)"
000308 "||' SELECT*FROM\"%w\".'||quote(name)"
000309 "FROM %s.sqlite_schema "
000310 "WHERE type='table'AND coalesce(rootpage,1)>0",
000311 zDbVacuum, zDbMain, zDbVacuum
000312 );
000313 assert( (db->mDbFlags & DBFLAG_Vacuum)!=0 );
000314 db->mDbFlags &= ~DBFLAG_Vacuum;
000315 if( rc!=SQLITE_OK ) goto end_of_vacuum;
000316
000317 /* Copy the triggers, views, and virtual tables from the main database
000318 ** over to the temporary database. None of these objects has any
000319 ** associated storage, so all we have to do is copy their entries
000320 ** from the schema table.
000321 */
000322 rc = execSqlF(db, pzErrMsg,
000323 "INSERT INTO %s.sqlite_schema"
000324 " SELECT*FROM \"%w\".sqlite_schema"
000325 " WHERE type IN('view','trigger')"
000326 " OR(type='table'AND rootpage=0)",
000327 zDbVacuum, zDbMain
000328 );
000329 if( rc ) goto end_of_vacuum;
000330
000331 /* At this point, there is a write transaction open on both the
000332 ** vacuum database and the main database. Assuming no error occurs,
000333 ** both transactions are closed by this block - the main database
000334 ** transaction by sqlite3BtreeCopyFile() and the other by an explicit
000335 ** call to sqlite3BtreeCommit().
000336 */
000337 {
000338 u32 meta;
000339 int i;
000340
000341 /* This array determines which meta meta values are preserved in the
000342 ** vacuum. Even entries are the meta value number and odd entries
000343 ** are an increment to apply to the meta value after the vacuum.
000344 ** The increment is used to increase the schema cookie so that other
000345 ** connections to the same database will know to reread the schema.
000346 */
000347 static const unsigned char aCopy[] = {
000348 BTREE_SCHEMA_VERSION, 1, /* Add one to the old schema cookie */
000349 BTREE_DEFAULT_CACHE_SIZE, 0, /* Preserve the default page cache size */
000350 BTREE_TEXT_ENCODING, 0, /* Preserve the text encoding */
000351 BTREE_USER_VERSION, 0, /* Preserve the user version */
000352 BTREE_APPLICATION_ID, 0, /* Preserve the application id */
000353 };
000354
000355 assert( SQLITE_TXN_WRITE==sqlite3BtreeTxnState(pTemp) );
000356 assert( pOut!=0 || SQLITE_TXN_WRITE==sqlite3BtreeTxnState(pMain) );
000357
000358 /* Copy Btree meta values */
000359 for(i=0; i<ArraySize(aCopy); i+=2){
000360 /* GetMeta() and UpdateMeta() cannot fail in this context because
000361 ** we already have page 1 loaded into cache and marked dirty. */
000362 sqlite3BtreeGetMeta(pMain, aCopy[i], &meta);
000363 rc = sqlite3BtreeUpdateMeta(pTemp, aCopy[i], meta+aCopy[i+1]);
000364 if( NEVER(rc!=SQLITE_OK) ) goto end_of_vacuum;
000365 }
000366
000367 if( pOut==0 ){
000368 rc = sqlite3BtreeCopyFile(pMain, pTemp);
000369 }
000370 if( rc!=SQLITE_OK ) goto end_of_vacuum;
000371 rc = sqlite3BtreeCommit(pTemp);
000372 if( rc!=SQLITE_OK ) goto end_of_vacuum;
000373 #ifndef SQLITE_OMIT_AUTOVACUUM
000374 if( pOut==0 ){
000375 sqlite3BtreeSetAutoVacuum(pMain, sqlite3BtreeGetAutoVacuum(pTemp));
000376 }
000377 #endif
000378 }
000379
000380 assert( rc==SQLITE_OK );
000381 if( pOut==0 ){
000382 nRes = sqlite3BtreeGetRequestedReserve(pTemp);
000383 rc = sqlite3BtreeSetPageSize(pMain, sqlite3BtreeGetPageSize(pTemp), nRes,1);
000384 }
000385
000386 end_of_vacuum:
000387 /* Restore the original value of db->flags */
000388 db->init.iDb = 0;
000389 db->mDbFlags = saved_mDbFlags;
000390 db->flags = saved_flags;
000391 db->nChange = saved_nChange;
000392 db->nTotalChange = saved_nTotalChange;
000393 db->mTrace = saved_mTrace;
000394 sqlite3BtreeSetPageSize(pMain, -1, 0, 1);
000395
000396 /* Currently there is an SQL level transaction open on the vacuum
000397 ** database. No locks are held on any other files (since the main file
000398 ** was committed at the btree level). So it safe to end the transaction
000399 ** by manually setting the autoCommit flag to true and detaching the
000400 ** vacuum database. The vacuum_db journal file is deleted when the pager
000401 ** is closed by the DETACH.
000402 */
000403 db->autoCommit = 1;
000404
000405 if( pDb ){
000406 sqlite3BtreeClose(pDb->pBt);
000407 pDb->pBt = 0;
000408 pDb->pSchema = 0;
000409 }
000410
000411 /* This both clears the schemas and reduces the size of the db->aDb[]
000412 ** array. */
000413 sqlite3ResetAllSchemasOfConnection(db);
000414
000415 return rc;
000416 }
000417
000418 #endif /* SQLITE_OMIT_VACUUM && SQLITE_OMIT_ATTACH */