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 ** Finalize a prepared statement. If there was an error, store the
23 ** text of the error message in *pzErrMsg. Return the result code.
24 */
vacuumFinalize(sqlite3 * db,sqlite3_stmt * pStmt,char ** pzErrMsg)25 static int vacuumFinalize(sqlite3 *db, sqlite3_stmt *pStmt, char **pzErrMsg){
26 int rc;
27 rc = sqlite3VdbeFinalize((Vdbe*)pStmt);
28 if( rc ){
29 sqlite3SetString(pzErrMsg, db, sqlite3_errmsg(db));
30 }
31 return rc;
32 }
33
34 /*
35 ** Execute zSql on database db. Return an error code.
36 */
execSql(sqlite3 * db,char ** pzErrMsg,const char * zSql)37 static int execSql(sqlite3 *db, char **pzErrMsg, const char *zSql){
38 sqlite3_stmt *pStmt;
39 VVA_ONLY( int rc; )
40 if( !zSql ){
41 return SQLITE_NOMEM;
42 }
43 if( SQLITE_OK!=sqlite3_prepare(db, zSql, -1, &pStmt, 0) ){
44 sqlite3SetString(pzErrMsg, db, sqlite3_errmsg(db));
45 return sqlite3_errcode(db);
46 }
47 VVA_ONLY( rc = ) sqlite3_step(pStmt);
48 assert( rc!=SQLITE_ROW );
49 return vacuumFinalize(db, pStmt, pzErrMsg);
50 }
51
52 /*
53 ** Execute zSql on database db. The statement returns exactly
54 ** one column. Execute this as SQL on the same database.
55 */
execExecSql(sqlite3 * db,char ** pzErrMsg,const char * zSql)56 static int execExecSql(sqlite3 *db, char **pzErrMsg, const char *zSql){
57 sqlite3_stmt *pStmt;
58 int rc;
59
60 rc = sqlite3_prepare(db, zSql, -1, &pStmt, 0);
61 if( rc!=SQLITE_OK ) return rc;
62
63 while( SQLITE_ROW==sqlite3_step(pStmt) ){
64 rc = execSql(db, pzErrMsg, (char*)sqlite3_column_text(pStmt, 0));
65 if( rc!=SQLITE_OK ){
66 vacuumFinalize(db, pStmt, pzErrMsg);
67 return rc;
68 }
69 }
70
71 return vacuumFinalize(db, pStmt, pzErrMsg);
72 }
73
74 /*
75 ** The non-standard VACUUM command is used to clean up the database,
76 ** collapse free space, etc. It is modelled after the VACUUM command
77 ** in PostgreSQL.
78 **
79 ** In version 1.0.x of SQLite, the VACUUM command would call
80 ** gdbm_reorganize() on all the database tables. But beginning
81 ** with 2.0.0, SQLite no longer uses GDBM so this command has
82 ** become a no-op.
83 */
sqlite3Vacuum(Parse * pParse)84 void sqlite3Vacuum(Parse *pParse){
85 Vdbe *v = sqlite3GetVdbe(pParse);
86 if( v ){
87 sqlite3VdbeAddOp2(v, OP_Vacuum, 0, 0);
88 }
89 return;
90 }
91
92 /*
93 ** This routine implements the OP_Vacuum opcode of the VDBE.
94 */
sqlite3RunVacuum(char ** pzErrMsg,sqlite3 * db)95 int sqlite3RunVacuum(char **pzErrMsg, sqlite3 *db){
96 int rc = SQLITE_OK; /* Return code from service routines */
97 Btree *pMain; /* The database being vacuumed */
98 Btree *pTemp; /* The temporary database we vacuum into */
99 char *zSql = 0; /* SQL statements */
100 int saved_flags; /* Saved value of the db->flags */
101 int saved_nChange; /* Saved value of db->nChange */
102 int saved_nTotalChange; /* Saved value of db->nTotalChange */
103 void (*saved_xTrace)(void*,const char*); /* Saved db->xTrace */
104 Db *pDb = 0; /* Database to detach at end of vacuum */
105 int isMemDb; /* True if vacuuming a :memory: database */
106 int nRes; /* Bytes of reserved space at the end of each page */
107 int nDb; /* Number of attached databases */
108
109 if( !db->autoCommit ){
110 sqlite3SetString(pzErrMsg, db, "cannot VACUUM from within a transaction");
111 return SQLITE_ERROR;
112 }
113 if( db->activeVdbeCnt>1 ){
114 sqlite3SetString(pzErrMsg, db,"cannot VACUUM - SQL statements in progress");
115 return SQLITE_ERROR;
116 }
117
118 /* Save the current value of the database flags so that it can be
119 ** restored before returning. Then set the writable-schema flag, and
120 ** disable CHECK and foreign key constraints. */
121 saved_flags = db->flags;
122 saved_nChange = db->nChange;
123 saved_nTotalChange = db->nTotalChange;
124 saved_xTrace = db->xTrace;
125 db->flags |= SQLITE_WriteSchema | SQLITE_IgnoreChecks | SQLITE_PreferBuiltin;
126 db->flags &= ~(SQLITE_ForeignKeys | SQLITE_ReverseOrder);
127 db->xTrace = 0;
128
129 pMain = db->aDb[0].pBt;
130 isMemDb = sqlite3PagerIsMemdb(sqlite3BtreePager(pMain));
131
132 /* Attach the temporary database as 'vacuum_db'. The synchronous pragma
133 ** can be set to 'off' for this file, as it is not recovered if a crash
134 ** occurs anyway. The integrity of the database is maintained by a
135 ** (possibly synchronous) transaction opened on the main database before
136 ** sqlite3BtreeCopyFile() is called.
137 **
138 ** An optimisation would be to use a non-journaled pager.
139 ** (Later:) I tried setting "PRAGMA vacuum_db.journal_mode=OFF" but
140 ** that actually made the VACUUM run slower. Very little journalling
141 ** actually occurs when doing a vacuum since the vacuum_db is initially
142 ** empty. Only the journal header is written. Apparently it takes more
143 ** time to parse and run the PRAGMA to turn journalling off than it does
144 ** to write the journal header file.
145 */
146 nDb = db->nDb;
147 if( sqlite3TempInMemory(db) ){
148 zSql = "ATTACH ':memory:' AS vacuum_db;";
149 }else{
150 zSql = "ATTACH '' AS vacuum_db;";
151 }
152 rc = execSql(db, pzErrMsg, zSql);
153 if( db->nDb>nDb ){
154 pDb = &db->aDb[db->nDb-1];
155 assert( strcmp(pDb->zName,"vacuum_db")==0 );
156 }
157 if( rc!=SQLITE_OK ) goto end_of_vacuum;
158 pTemp = db->aDb[db->nDb-1].pBt;
159
160 /* The call to execSql() to attach the temp database has left the file
161 ** locked (as there was more than one active statement when the transaction
162 ** to read the schema was concluded. Unlock it here so that this doesn't
163 ** cause problems for the call to BtreeSetPageSize() below. */
164 sqlite3BtreeCommit(pTemp);
165
166 nRes = sqlite3BtreeGetReserve(pMain);
167
168 /* A VACUUM cannot change the pagesize of an encrypted database. */
169 #ifdef SQLITE_HAS_CODEC
170 if( db->nextPagesize ){
171 extern void sqlite3CodecGetKey(sqlite3*, int, void**, int*);
172 int nKey;
173 char *zKey;
174 sqlite3CodecGetKey(db, 0, (void**)&zKey, &nKey);
175 if( nKey ) db->nextPagesize = 0;
176 }
177 #endif
178
179 /* Do not attempt to change the page size for a WAL database */
180 if( sqlite3PagerGetJournalMode(sqlite3BtreePager(pMain))
181 ==PAGER_JOURNALMODE_WAL ){
182 db->nextPagesize = 0;
183 }
184
185 if( sqlite3BtreeSetPageSize(pTemp, sqlite3BtreeGetPageSize(pMain), nRes, 0)
186 || (!isMemDb && sqlite3BtreeSetPageSize(pTemp, db->nextPagesize, nRes, 0))
187 || NEVER(db->mallocFailed)
188 ){
189 rc = SQLITE_NOMEM;
190 goto end_of_vacuum;
191 }
192 rc = execSql(db, pzErrMsg, "PRAGMA vacuum_db.synchronous=OFF");
193 if( rc!=SQLITE_OK ){
194 goto end_of_vacuum;
195 }
196
197 #ifndef SQLITE_OMIT_AUTOVACUUM
198 sqlite3BtreeSetAutoVacuum(pTemp, db->nextAutovac>=0 ? db->nextAutovac :
199 sqlite3BtreeGetAutoVacuum(pMain));
200 #endif
201
202 /* Begin a transaction */
203 rc = execSql(db, pzErrMsg, "BEGIN EXCLUSIVE;");
204 if( rc!=SQLITE_OK ) goto end_of_vacuum;
205
206 /* Query the schema of the main database. Create a mirror schema
207 ** in the temporary database.
208 */
209 rc = execExecSql(db, pzErrMsg,
210 "SELECT 'CREATE TABLE vacuum_db.' || substr(sql,14) "
211 " FROM sqlite_master WHERE type='table' AND name!='sqlite_sequence'"
212 " AND rootpage>0"
213 );
214 if( rc!=SQLITE_OK ) goto end_of_vacuum;
215 rc = execExecSql(db, pzErrMsg,
216 "SELECT 'CREATE INDEX vacuum_db.' || substr(sql,14)"
217 " FROM sqlite_master WHERE sql LIKE 'CREATE INDEX %' ");
218 if( rc!=SQLITE_OK ) goto end_of_vacuum;
219 rc = execExecSql(db, pzErrMsg,
220 "SELECT 'CREATE UNIQUE INDEX vacuum_db.' || substr(sql,21) "
221 " FROM sqlite_master WHERE sql LIKE 'CREATE UNIQUE INDEX %'");
222 if( rc!=SQLITE_OK ) goto end_of_vacuum;
223
224 /* Loop through the tables in the main database. For each, do
225 ** an "INSERT INTO vacuum_db.xxx SELECT * FROM main.xxx;" to copy
226 ** the contents to the temporary database.
227 */
228 rc = execExecSql(db, pzErrMsg,
229 "SELECT 'INSERT INTO vacuum_db.' || quote(name) "
230 "|| ' SELECT * FROM main.' || quote(name) || ';'"
231 "FROM main.sqlite_master "
232 "WHERE type = 'table' AND name!='sqlite_sequence' "
233 " AND rootpage>0"
234 );
235 if( rc!=SQLITE_OK ) goto end_of_vacuum;
236
237 /* Copy over the sequence table
238 */
239 rc = execExecSql(db, pzErrMsg,
240 "SELECT 'DELETE FROM vacuum_db.' || quote(name) || ';' "
241 "FROM vacuum_db.sqlite_master WHERE name='sqlite_sequence' "
242 );
243 if( rc!=SQLITE_OK ) goto end_of_vacuum;
244 rc = execExecSql(db, pzErrMsg,
245 "SELECT 'INSERT INTO vacuum_db.' || quote(name) "
246 "|| ' SELECT * FROM main.' || quote(name) || ';' "
247 "FROM vacuum_db.sqlite_master WHERE name=='sqlite_sequence';"
248 );
249 if( rc!=SQLITE_OK ) goto end_of_vacuum;
250
251
252 /* Copy the triggers, views, and virtual tables from the main database
253 ** over to the temporary database. None of these objects has any
254 ** associated storage, so all we have to do is copy their entries
255 ** from the SQLITE_MASTER table.
256 */
257 rc = execSql(db, pzErrMsg,
258 "INSERT INTO vacuum_db.sqlite_master "
259 " SELECT type, name, tbl_name, rootpage, sql"
260 " FROM main.sqlite_master"
261 " WHERE type='view' OR type='trigger'"
262 " OR (type='table' AND rootpage=0)"
263 );
264 if( rc ) goto end_of_vacuum;
265
266 /* At this point, unless the main db was completely empty, there is now a
267 ** transaction open on the vacuum database, but not on the main database.
268 ** Open a btree level transaction on the main database. This allows a
269 ** call to sqlite3BtreeCopyFile(). The main database btree level
270 ** transaction is then committed, so the SQL level never knows it was
271 ** opened for writing. This way, the SQL transaction used to create the
272 ** temporary database never needs to be committed.
273 */
274 {
275 u32 meta;
276 int i;
277
278 /* This array determines which meta meta values are preserved in the
279 ** vacuum. Even entries are the meta value number and odd entries
280 ** are an increment to apply to the meta value after the vacuum.
281 ** The increment is used to increase the schema cookie so that other
282 ** connections to the same database will know to reread the schema.
283 */
284 static const unsigned char aCopy[] = {
285 BTREE_SCHEMA_VERSION, 1, /* Add one to the old schema cookie */
286 BTREE_DEFAULT_CACHE_SIZE, 0, /* Preserve the default page cache size */
287 BTREE_TEXT_ENCODING, 0, /* Preserve the text encoding */
288 BTREE_USER_VERSION, 0, /* Preserve the user version */
289 };
290
291 assert( 1==sqlite3BtreeIsInTrans(pTemp) );
292 assert( 1==sqlite3BtreeIsInTrans(pMain) );
293
294 /* Copy Btree meta values */
295 for(i=0; i<ArraySize(aCopy); i+=2){
296 /* GetMeta() and UpdateMeta() cannot fail in this context because
297 ** we already have page 1 loaded into cache and marked dirty. */
298 sqlite3BtreeGetMeta(pMain, aCopy[i], &meta);
299 rc = sqlite3BtreeUpdateMeta(pTemp, aCopy[i], meta+aCopy[i+1]);
300 if( NEVER(rc!=SQLITE_OK) ) goto end_of_vacuum;
301 }
302
303 rc = sqlite3BtreeCopyFile(pMain, pTemp);
304 if( rc!=SQLITE_OK ) goto end_of_vacuum;
305 rc = sqlite3BtreeCommit(pTemp);
306 if( rc!=SQLITE_OK ) goto end_of_vacuum;
307 #ifndef SQLITE_OMIT_AUTOVACUUM
308 sqlite3BtreeSetAutoVacuum(pMain, sqlite3BtreeGetAutoVacuum(pTemp));
309 #endif
310 }
311
312 assert( rc==SQLITE_OK );
313 rc = sqlite3BtreeSetPageSize(pMain, sqlite3BtreeGetPageSize(pTemp), nRes,1);
314
315 end_of_vacuum:
316 /* Restore the original value of db->flags */
317 db->flags = saved_flags;
318 db->nChange = saved_nChange;
319 db->nTotalChange = saved_nTotalChange;
320 db->xTrace = saved_xTrace;
321 sqlite3BtreeSetPageSize(pMain, -1, -1, 1);
322
323 /* Currently there is an SQL level transaction open on the vacuum
324 ** database. No locks are held on any other files (since the main file
325 ** was committed at the btree level). So it safe to end the transaction
326 ** by manually setting the autoCommit flag to true and detaching the
327 ** vacuum database. The vacuum_db journal file is deleted when the pager
328 ** is closed by the DETACH.
329 */
330 db->autoCommit = 1;
331
332 if( pDb ){
333 sqlite3BtreeClose(pDb->pBt);
334 pDb->pBt = 0;
335 pDb->pSchema = 0;
336 }
337
338 /* This both clears the schemas and reduces the size of the db->aDb[]
339 ** array. */
340 sqlite3ResetInternalSchema(db, -1);
341
342 return rc;
343 }
344
345 #endif /* SQLITE_OMIT_VACUUM && SQLITE_OMIT_ATTACH */
346