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 | */ |
32 | static 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 | } |
62 | static 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 | */ |
105 | void 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 | } |
135 | build_vacuum_end: |
136 | sqlite3ExprDelete(pParse->db, pInto); |
137 | return; |
138 | } |
139 | |
140 | /* |
141 | ** This routine implements the OP_Vacuum opcode of the VDBE. |
142 | */ |
143 | SQLITE_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 | |
381 | end_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 | |