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*/
25static 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*/
37static 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*/
56static 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*/
84void 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*/
95int 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
315end_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