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/*
23** Execute zSql on database db.
24**
25** If zSql returns rows, then each row will have exactly one
26** 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 accepts
30** a format string as its third argument
31*/
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 first
44 ** corrupt the sqlite_schema.sql field with other kinds of statements
45 ** then run VACUUM to get those statements to execute at inappropriate
46 ** times. */
47 if( zSubSql
48 && (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 command
78** in PostgreSQL. The VACUUM command works as follows:
79**
80** (1) Create a new transient database file
81** (2) Copy all content from the database being vacuumed into
82** the new transient database file
83** (3) Copy content from the transient database back into the
84** original database.
85**
86** The transient database requires temporary disk space approximately
87** equal to the size of the original database. The copy operation of
88** step (3) requires additional temporary disk space approximately equal
89** to the size of the original database for the rollback journal.
90** Hence, temporary disk space that is approximately 2x the size of the
91** original database is required. Every page of the database is written
92** approximately 3 times: Once for step (2) and twice for step (3).
93** Two writes per page are required in step (3) because the original
94** database content must be written into the rollback journal prior to
95** overwriting the database with the vacuumed content.
96**
97** Only 1x temporary space and only 1x writes would be required if
98** the copy of step (3) were replaced by deleting the original database
99** and renaming the transient database as the original. But that will
100** not work if other processes are attached to the original database.
101** And a power loss in between deleting the original and renaming the
102** transient would cause the database file to appear to be deleted
103** 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_20160819
112 /* Default behavior: Report an error if the argument to VACUUM is
113 ** not recognized */
114 iDb = sqlite3TwoPartName(pParse, pNm, pNm, &pNm);
115 if( iDb<0 ) goto build_vacuum_end;
116#else
117 /* When SQLITE_BUG_COMPATIBLE_20160819 is defined, unrecognized arguments
118 ** to VACUUM are silently ignored. This is a back-out of a bug fix that
119 ** occurred on 2016-08-19 (https://www.sqlite.org/src/info/083f9e6270).
120 ** The buggy behavior is required for binary compatibility with some
121 ** legacy applications. */
122 iDb = sqlite3FindDb(pParse->db, pNm);
123 if( iDb<0 ) iDb = 0;
124#endif
125 }
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
166 if( !db->autoCommit ){
167 sqlite3SetString(pzErrMsg, db, "cannot VACUUM from within a transaction");
168 return SQLITE_ERROR; /* IMP: R-12218-18073 */
169 }
170 if( db->nVdbeActive>1 ){
171 sqlite3SetString(pzErrMsg, db,"cannot VACUUM - SQL statements in progress");
172 return SQLITE_ERROR; /* IMP: R-15610-35227 */
173 }
174 saved_openFlags = db->openFlags;
175 if( pOut ){
176 if( sqlite3_value_type(pOut)!=SQLITE_TEXT ){
177 sqlite3SetString(pzErrMsg, db, "non-text filename");
178 return SQLITE_ERROR;
179 }
180 zOut = (const char*)sqlite3_value_text(pOut);
181 db->openFlags &= ~SQLITE_OPEN_READONLY;
182 db->openFlags |= SQLITE_OPEN_CREATE|SQLITE_OPEN_READWRITE;
183 }else{
184 zOut = "";
185 }
186
187 /* Save the current value of the database flags so that it can be
188 ** restored before returning. Then set the writable-schema flag, and
189 ** disable CHECK and foreign key constraints. */
190 saved_flags = db->flags;
191 saved_mDbFlags = db->mDbFlags;
192 saved_nChange = db->nChange;
193 saved_nTotalChange = db->nTotalChange;
194 saved_mTrace = db->mTrace;
195 db->flags |= SQLITE_WriteSchema | SQLITE_IgnoreChecks;
196 db->mDbFlags |= DBFLAG_PreferBuiltin | DBFLAG_Vacuum;
197 db->flags &= ~(u64)(SQLITE_ForeignKeys | SQLITE_ReverseOrder
198 | SQLITE_Defensive | SQLITE_CountRows);
199 db->mTrace = 0;
200
201 zDbMain = db->aDb[iDb].zDbSName;
202 pMain = db->aDb[iDb].pBt;
203 isMemDb = sqlite3PagerIsMemdb(sqlite3BtreePager(pMain));
204
205 /* Attach the temporary database as 'vacuum_db'. The synchronous pragma
206 ** can be set to 'off' for this file, as it is not recovered if a crash
207 ** occurs anyway. The integrity of the database is maintained by a
208 ** (possibly synchronous) transaction opened on the main database before
209 ** sqlite3BtreeCopyFile() is called.
210 **
211 ** An optimisation would be to use a non-journaled pager.
212 ** (Later:) I tried setting "PRAGMA vacuum_db.journal_mode=OFF" but
213 ** that actually made the VACUUM run slower. Very little journalling
214 ** actually occurs when doing a vacuum since the vacuum_db is initially
215 ** empty. Only the journal header is written. Apparently it takes more
216 ** time to parse and run the PRAGMA to turn journalling off than it does
217 ** to write the journal header file.
218 */
219 nDb = db->nDb;
220 rc = execSqlF(db, pzErrMsg, "ATTACH %Q AS vacuum_db", zOut);
221 db->openFlags = saved_openFlags;
222 if( rc!=SQLITE_OK ) goto end_of_vacuum;
223 assert( (db->nDb-1)==nDb );
224 pDb = &db->aDb[nDb];
225 assert( strcmp(pDb->zDbSName,"vacuum_db")==0 );
226 pTemp = pDb->pBt;
227 if( pOut ){
228 sqlite3_file *id = sqlite3PagerFile(sqlite3BtreePager(pTemp));
229 i64 sz = 0;
230 if( id->pMethods!=0 && (sqlite3OsFileSize(id, &sz)!=SQLITE_OK || sz>0) ){
231 rc = SQLITE_ERROR;
232 sqlite3SetString(pzErrMsg, db, "output file already exists");
233 goto end_of_vacuum;
234 }
235 db->mDbFlags |= DBFLAG_VacuumInto;
236
237 /* For a VACUUM INTO, the pager-flags are set to the same values as
238 ** they are for the database being vacuumed, except that PAGER_CACHESPILL
239 ** is always set. */
240 pgflags = db->aDb[iDb].safety_level | (db->flags & PAGER_FLAGS_MASK);
241 }
242 nRes = sqlite3BtreeGetRequestedReserve(pMain);
243
244 sqlite3BtreeSetCacheSize(pTemp, db->aDb[iDb].pSchema->cache_size);
245 sqlite3BtreeSetSpillSize(pTemp, sqlite3BtreeSetSpillSize(pMain,0));
246 sqlite3BtreeSetPagerFlags(pTemp, pgflags|PAGER_CACHESPILL);
247
248 /* Begin a transaction and take an exclusive lock on the main database
249 ** file. This is done before the sqlite3BtreeGetPageSize(pMain) call below,
250 ** to ensure that we do not try to change the page-size on a WAL database.
251 */
252 rc = execSql(db, pzErrMsg, "BEGIN");
253 if( rc!=SQLITE_OK ) goto end_of_vacuum;
254 rc = sqlite3BtreeBeginTrans(pMain, pOut==0 ? 2 : 0, 0);
255 if( rc!=SQLITE_OK ) goto end_of_vacuum;
256
257 /* Do not attempt to change the page size for a WAL database */
258 if( sqlite3PagerGetJournalMode(sqlite3BtreePager(pMain))
259 ==PAGER_JOURNALMODE_WAL
260 && pOut==0
261 ){
262 db->nextPagesize = 0;
263 }
264
265 if( sqlite3BtreeSetPageSize(pTemp, sqlite3BtreeGetPageSize(pMain), nRes, 0)
266 || (!isMemDb && sqlite3BtreeSetPageSize(pTemp, db->nextPagesize, nRes, 0))
267 || NEVER(db->mallocFailed)
268 ){
269 rc = SQLITE_NOMEM_BKPT;
270 goto end_of_vacuum;
271 }
272
273#ifndef SQLITE_OMIT_AUTOVACUUM
274 sqlite3BtreeSetAutoVacuum(pTemp, db->nextAutovac>=0 ? db->nextAutovac :
275 sqlite3BtreeGetAutoVacuum(pMain));
276#endif
277
278 /* Query the schema of the main database. Create a mirror schema
279 ** in the temporary database.
280 */
281 db->init.iDb = nDb; /* force new CREATE statements into vacuum_db */
282 rc = execSqlF(db, pzErrMsg,
283 "SELECT sql FROM \"%w\".sqlite_schema"
284 " WHERE type='table'AND name<>'sqlite_sequence'"
285 " AND coalesce(rootpage,1)>0",
286 zDbMain
287 );
288 if( rc!=SQLITE_OK ) goto end_of_vacuum;
289 rc = execSqlF(db, pzErrMsg,
290 "SELECT sql FROM \"%w\".sqlite_schema"
291 " WHERE type='index'",
292 zDbMain
293 );
294 if( rc!=SQLITE_OK ) goto end_of_vacuum;
295 db->init.iDb = 0;
296
297 /* Loop through the tables in the main database. For each, do
298 ** an "INSERT INTO vacuum_db.xxx SELECT * FROM main.xxx;" to copy
299 ** the contents to the temporary database.
300 */
301 rc = execSqlF(db, pzErrMsg,
302 "SELECT'INSERT INTO vacuum_db.'||quote(name)"
303 "||' SELECT*FROM\"%w\".'||quote(name)"
304 "FROM vacuum_db.sqlite_schema "
305 "WHERE type='table'AND coalesce(rootpage,1)>0",
306 zDbMain
307 );
308 assert( (db->mDbFlags & DBFLAG_Vacuum)!=0 );
309 db->mDbFlags &= ~DBFLAG_Vacuum;
310 if( rc!=SQLITE_OK ) goto end_of_vacuum;
311
312 /* Copy the triggers, views, and virtual tables from the main database
313 ** over to the temporary database. None of these objects has any
314 ** associated storage, so all we have to do is copy their entries
315 ** from the schema table.
316 */
317 rc = execSqlF(db, pzErrMsg,
318 "INSERT INTO vacuum_db.sqlite_schema"
319 " SELECT*FROM \"%w\".sqlite_schema"
320 " WHERE type IN('view','trigger')"
321 " OR(type='table'AND rootpage=0)",
322 zDbMain
323 );
324 if( rc ) goto end_of_vacuum;
325
326 /* At this point, there is a write transaction open on both the
327 ** vacuum database and the main database. Assuming no error occurs,
328 ** both transactions are closed by this block - the main database
329 ** transaction by sqlite3BtreeCopyFile() and the other by an explicit
330 ** call to sqlite3BtreeCommit().
331 */
332 {
333 u32 meta;
334 int i;
335
336 /* This array determines which meta meta values are preserved in the
337 ** vacuum. Even entries are the meta value number and odd entries
338 ** are an increment to apply to the meta value after the vacuum.
339 ** The increment is used to increase the schema cookie so that other
340 ** connections to the same database will know to reread the schema.
341 */
342 static const unsigned char aCopy[] = {
343 BTREE_SCHEMA_VERSION, 1, /* Add one to the old schema cookie */
344 BTREE_DEFAULT_CACHE_SIZE, 0, /* Preserve the default page cache size */
345 BTREE_TEXT_ENCODING, 0, /* Preserve the text encoding */
346 BTREE_USER_VERSION, 0, /* Preserve the user version */
347 BTREE_APPLICATION_ID, 0, /* Preserve the application id */
348 };
349
350 assert( SQLITE_TXN_WRITE==sqlite3BtreeTxnState(pTemp) );
351 assert( pOut!=0 || SQLITE_TXN_WRITE==sqlite3BtreeTxnState(pMain) );
352
353 /* Copy Btree meta values */
354 for(i=0; i<ArraySize(aCopy); i+=2){
355 /* GetMeta() and UpdateMeta() cannot fail in this context because
356 ** we already have page 1 loaded into cache and marked dirty. */
357 sqlite3BtreeGetMeta(pMain, aCopy[i], &meta);
358 rc = sqlite3BtreeUpdateMeta(pTemp, aCopy[i], meta+aCopy[i+1]);
359 if( NEVER(rc!=SQLITE_OK) ) goto end_of_vacuum;
360 }
361
362 if( pOut==0 ){
363 rc = sqlite3BtreeCopyFile(pMain, pTemp);
364 }
365 if( rc!=SQLITE_OK ) goto end_of_vacuum;
366 rc = sqlite3BtreeCommit(pTemp);
367 if( rc!=SQLITE_OK ) goto end_of_vacuum;
368#ifndef SQLITE_OMIT_AUTOVACUUM
369 if( pOut==0 ){
370 sqlite3BtreeSetAutoVacuum(pMain, sqlite3BtreeGetAutoVacuum(pTemp));
371 }
372#endif
373 }
374
375 assert( rc==SQLITE_OK );
376 if( pOut==0 ){
377 nRes = sqlite3BtreeGetRequestedReserve(pTemp);
378 rc = sqlite3BtreeSetPageSize(pMain, sqlite3BtreeGetPageSize(pTemp), nRes,1);
379 }
380
381end_of_vacuum:
382 /* Restore the original value of db->flags */
383 db->init.iDb = 0;
384 db->mDbFlags = saved_mDbFlags;
385 db->flags = saved_flags;
386 db->nChange = saved_nChange;
387 db->nTotalChange = saved_nTotalChange;
388 db->mTrace = saved_mTrace;
389 sqlite3BtreeSetPageSize(pMain, -1, 0, 1);
390
391 /* Currently there is an SQL level transaction open on the vacuum
392 ** database. No locks are held on any other files (since the main file
393 ** was committed at the btree level). So it safe to end the transaction
394 ** by manually setting the autoCommit flag to true and detaching the
395 ** vacuum database. The vacuum_db journal file is deleted when the pager
396 ** is closed by the DETACH.
397 */
398 db->autoCommit = 1;
399
400 if( pDb ){
401 sqlite3BtreeClose(pDb->pBt);
402 pDb->pBt = 0;
403 pDb->pSchema = 0;
404 }
405
406 /* This both clears the schemas and reduces the size of the db->aDb[]
407 ** array. */
408 sqlite3ResetAllSchemasOfConnection(db);
409
410 return rc;
411}
412
413#endif /* SQLITE_OMIT_VACUUM && SQLITE_OMIT_ATTACH */
414