src/vacuum.c

   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 */
  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 */
  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 || (db->flags&SQLITE_CountRows) );
  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 */
  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 */
  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 */
  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   rc = execSql(db, pzErrMsg, "PRAGMA vacuum_db.synchronous=OFF");
 180   if( rc!=SQLITE_OK ) goto end_of_vacuum;
 181
 182   /* Begin a transaction and take an exclusive lock on the main database
 183   ** file. This is done before the sqlite3BtreeGetPageSize(pMain) call below,
 184   ** to ensure that we do not try to change the page-size on a WAL database.
 185   */
 186   rc = execSql(db, pzErrMsg, "BEGIN;");
 187   if( rc!=SQLITE_OK ) goto end_of_vacuum;
 188   rc = sqlite3BtreeBeginTrans(pMain, 2);
 189   if( rc!=SQLITE_OK ) goto end_of_vacuum;
 190
 191   /* Do not attempt to change the page size for a WAL database */
 192   if( sqlite3PagerGetJournalMode(sqlite3BtreePager(pMain))
 193                                                ==PAGER_JOURNALMODE_WAL ){
 194     db->nextPagesize = 0;
 195   }
 196
 197   if( sqlite3BtreeSetPageSize(pTemp, sqlite3BtreeGetPageSize(pMain), nRes, 0)
 198    || (!isMemDb && sqlite3BtreeSetPageSize(pTemp, db->nextPagesize, nRes, 0))
 199    || NEVER(db->mallocFailed)
 200   ){
 201     rc = SQLITE_NOMEM;
 202     goto end_of_vacuum;
 203   }
 204
 205 #ifndef SQLITE_OMIT_AUTOVACUUM
 206   sqlite3BtreeSetAutoVacuum(pTemp, db->nextAutovac>=0 ? db->nextAutovac :
 207                                            sqlite3BtreeGetAutoVacuum(pMain));
 208 #endif
 209
 210   /* Query the schema of the main database. Create a mirror schema
 211   ** in the temporary database.
 212   */
 213   rc = execExecSql(db, pzErrMsg,
 214       "SELECT 'CREATE TABLE vacuum_db.' || substr(sql,14) "
 215       "  FROM sqlite_master WHERE type='table' AND name!='sqlite_sequence'"
 216       "   AND rootpage>0"
 217   );
 218   if( rc!=SQLITE_OK ) goto end_of_vacuum;
 219   rc = execExecSql(db, pzErrMsg,
 220       "SELECT 'CREATE INDEX vacuum_db.' || substr(sql,14)"
 221       "  FROM sqlite_master WHERE sql LIKE 'CREATE INDEX %' ");
 222   if( rc!=SQLITE_OK ) goto end_of_vacuum;
 223   rc = execExecSql(db, pzErrMsg,
 224       "SELECT 'CREATE UNIQUE INDEX vacuum_db.' || substr(sql,21) "
 225       "  FROM sqlite_master WHERE sql LIKE 'CREATE UNIQUE INDEX %'");
 226   if( rc!=SQLITE_OK ) goto end_of_vacuum;
 227
 228   /* Loop through the tables in the main database. For each, do
 229   ** an "INSERT INTO vacuum_db.xxx SELECT * FROM main.xxx;" to copy
 230   ** the contents to the temporary database.
 231   */
 232   rc = execExecSql(db, pzErrMsg,
 233       "SELECT 'INSERT INTO vacuum_db.' || quote(name) "
 234       "|| ' SELECT * FROM main.' || quote(name) || ';'"
 235       "FROM main.sqlite_master "
 236       "WHERE type = 'table' AND name!='sqlite_sequence' "
 237       "  AND rootpage>0"
 238   );
 239   if( rc!=SQLITE_OK ) goto end_of_vacuum;
 240
 241   /* Copy over the sequence table
 242   */
 243   rc = execExecSql(db, pzErrMsg,
 244       "SELECT 'DELETE FROM vacuum_db.' || quote(name) || ';' "
 245       "FROM vacuum_db.sqlite_master WHERE name='sqlite_sequence' "
 246   );
 247   if( rc!=SQLITE_OK ) goto end_of_vacuum;
 248   rc = execExecSql(db, pzErrMsg,
 249       "SELECT 'INSERT INTO vacuum_db.' || quote(name) "
 250       "|| ' SELECT * FROM main.' || quote(name) || ';' "
 251       "FROM vacuum_db.sqlite_master WHERE name=='sqlite_sequence';"
 252   );
 253   if( rc!=SQLITE_OK ) goto end_of_vacuum;
 254
 255
 256   /* Copy the triggers, views, and virtual tables from the main database
 257   ** over to the temporary database.  None of these objects has any
 258   ** associated storage, so all we have to do is copy their entries
 259   ** from the SQLITE_MASTER table.
 260   */
 261   rc = execSql(db, pzErrMsg,
 262       "INSERT INTO vacuum_db.sqlite_master "
 263       "  SELECT type, name, tbl_name, rootpage, sql"
 264       "    FROM main.sqlite_master"
 265       "   WHERE type='view' OR type='trigger'"
 266       "      OR (type='table' AND rootpage=0)"
 267   );
 268   if( rc ) goto end_of_vacuum;
 269
 270   /* At this point, there is a write transaction open on both the
 271   ** vacuum database and the main database. Assuming no error occurs,
 272   ** both transactions are closed by this block - the main database
 273   ** transaction by sqlite3BtreeCopyFile() and the other by an explicit
 274   ** call to sqlite3BtreeCommit().
 275   */
 276   {
 277     u32 meta;
 278     int i;
 279
 280     /* This array determines which meta meta values are preserved in the
 281     ** vacuum.  Even entries are the meta value number and odd entries
 282     ** are an increment to apply to the meta value after the vacuum.
 283     ** The increment is used to increase the schema cookie so that other
 284     ** connections to the same database will know to reread the schema.
 285     */
 286     static const unsigned char aCopy[] = {
 287        BTREE_SCHEMA_VERSION,     1,  /* Add one to the old schema cookie */
 288        BTREE_DEFAULT_CACHE_SIZE, 0,  /* Preserve the default page cache size */
 289        BTREE_TEXT_ENCODING,      0,  /* Preserve the text encoding */
 290        BTREE_USER_VERSION,       0,  /* Preserve the user version */
 291     };
 292
 293     assert( 1==sqlite3BtreeIsInTrans(pTemp) );
 294     assert( 1==sqlite3BtreeIsInTrans(pMain) );
 295
 296     /* Copy Btree meta values */
 297     for(i=0; i<ArraySize(aCopy); i+=2){
 298       /* GetMeta() and UpdateMeta() cannot fail in this context because
 299       ** we already have page 1 loaded into cache and marked dirty. */
 300       sqlite3BtreeGetMeta(pMain, aCopy[i], &meta);
 301       rc = sqlite3BtreeUpdateMeta(pTemp, aCopy[i], meta+aCopy[i+1]);
 302       if( NEVER(rc!=SQLITE_OK) ) goto end_of_vacuum;
 303     }
 304
 305     rc = sqlite3BtreeCopyFile(pMain, pTemp);
 306     if( rc!=SQLITE_OK ) goto end_of_vacuum;
 307     rc = sqlite3BtreeCommit(pTemp);
 308     if( rc!=SQLITE_OK ) goto end_of_vacuum;
 309 #ifndef SQLITE_OMIT_AUTOVACUUM
 310     sqlite3BtreeSetAutoVacuum(pMain, sqlite3BtreeGetAutoVacuum(pTemp));
 311 #endif
 312   }
 313
 314   assert( rc==SQLITE_OK );
 315   rc = sqlite3BtreeSetPageSize(pMain, sqlite3BtreeGetPageSize(pTemp), nRes,1);
 316
 317 end_of_vacuum:
 318   /* Restore the original value of db->flags */
 319   db->flags = saved_flags;
 320   db->nChange = saved_nChange;
 321   db->nTotalChange = saved_nTotalChange;
 322   db->xTrace = saved_xTrace;
 323   sqlite3BtreeSetPageSize(pMain, -1, -1, 1);
 324
 325   /* Currently there is an SQL level transaction open on the vacuum
 326   ** database. No locks are held on any other files (since the main file
 327   ** was committed at the btree level). So it safe to end the transaction
 328   ** by manually setting the autoCommit flag to true and detaching the
 329   ** vacuum database. The vacuum_db journal file is deleted when the pager
 330   ** is closed by the DETACH.
 331   */
 332   db->autoCommit = 1;
 333
 334   if( pDb ){
 335     sqlite3BtreeClose(pDb->pBt);
 336     pDb->pBt = 0;
 337     pDb->pSchema = 0;
 338   }
 339
 340   /* This both clears the schemas and reduces the size of the db->aDb[]
 341   ** array. */
 342   sqlite3ResetAllSchemasOfConnection(db);
 343
 344   return rc;
 345 }
 346
 347 #endif  /* SQLITE_OMIT_VACUUM && SQLITE_OMIT_ATTACH */