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 /*
  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     if( zSubSql ){
  43       assert( zSubSql[0]!='S' );
  44       rc = execSql(db, pzErrMsg, zSubSql);
  45       if( rc!=SQLITE_OK ) break;
  46     }
  47   }
  48   assert( rc!=SQLITE_ROW );
  49   if( rc==SQLITE_DONE ) rc = SQLITE_OK;
  50   if( rc ){
  51     sqlite3SetString(pzErrMsg, db, sqlite3_errmsg(db));
  52   }
  53   (void)sqlite3_finalize(pStmt);
  54   return rc;
  55 }
  56 static int execSqlF(sqlite3 *db, char **pzErrMsg, const char *zSql, ...){
  57   char *z;
  58   va_list ap;
  59   int rc;
  60   va_start(ap, zSql);
  61   z = sqlite3VMPrintf(db, zSql, ap);
  62   va_end(ap);
  63   if( z==0 ) return SQLITE_NOMEM;
  64   rc = execSql(db, pzErrMsg, z);
  65   sqlite3DbFree(db, z);
  66   return rc;
  67 }
  68
  69 /*
  70 ** The VACUUM command is used to clean up the database,
  71 ** collapse free space, etc.  It is modelled after the VACUUM command
  72 ** in PostgreSQL.  The VACUUM command works as follows:
  73 **
  74 **   (1)  Create a new transient database file
  75 **   (2)  Copy all content from the database being vacuumed into
  76 **        the new transient database file
  77 **   (3)  Copy content from the transient database back into the
  78 **        original database.
  79 **
  80 ** The transient database requires temporary disk space approximately
  81 ** equal to the size of the original database.  The copy operation of
  82 ** step (3) requires additional temporary disk space approximately equal
  83 ** to the size of the original database for the rollback journal.
  84 ** Hence, temporary disk space that is approximately 2x the size of the
  85 ** original database is required.  Every page of the database is written
  86 ** approximately 3 times:  Once for step (2) and twice for step (3).
  87 ** Two writes per page are required in step (3) because the original
  88 ** database content must be written into the rollback journal prior to
  89 ** overwriting the database with the vacuumed content.
  90 **
  91 ** Only 1x temporary space and only 1x writes would be required if
  92 ** the copy of step (3) were replaced by deleting the original database
  93 ** and renaming the transient database as the original.  But that will
  94 ** not work if other processes are attached to the original database.
  95 ** And a power loss in between deleting the original and renaming the
  96 ** transient would cause the database file to appear to be deleted
  97 ** following reboot.
  98 */
  99 void sqlite3Vacuum(Parse *pParse, Token *pNm){
 100   Vdbe *v = sqlite3GetVdbe(pParse);
 101   int iDb = 0;
 102   if( v==0 ) return;
 103   if( pNm ){
 104 #ifndef SQLITE_BUG_COMPATIBLE_20160819
 105     /* Default behavior:  Report an error if the argument to VACUUM is
 106     ** not recognized */
 107     iDb = sqlite3TwoPartName(pParse, pNm, pNm, &pNm);
 108     if( iDb<0 ) return;
 109 #else
 110     /* When SQLITE_BUG_COMPATIBLE_20160819 is defined, unrecognized arguments
 111     ** to VACUUM are silently ignored.  This is a back-out of a bug fix that
 112     ** occurred on 2016-08-19 (https://www.sqlite.org/src/info/083f9e6270).
 113     ** The buggy behavior is required for binary compatibility with some
 114     ** legacy applications. */
 115     iDb = sqlite3FindDb(pParse->db, pNm);
 116     if( iDb<0 ) iDb = 0;
 117 #endif
 118   }
 119   if( iDb!=1 ){
 120     sqlite3VdbeAddOp1(v, OP_Vacuum, iDb);
 121     sqlite3VdbeUsesBtree(v, iDb);
 122   }
 123   return;
 124 }
 125
 126 /*
 127 ** This routine implements the OP_Vacuum opcode of the VDBE.
 128 */
 129 int sqlite3RunVacuum(char **pzErrMsg, sqlite3 *db, int iDb){
 130   int rc = SQLITE_OK;     /* Return code from service routines */
 131   Btree *pMain;           /* The database being vacuumed */
 132   Btree *pTemp;           /* The temporary database we vacuum into */
 133   u16 saved_mDbFlags;     /* Saved value of db->mDbFlags */
 134   u32 saved_flags;        /* Saved value of db->flags */
 135   int saved_nChange;      /* Saved value of db->nChange */
 136   int saved_nTotalChange; /* Saved value of db->nTotalChange */
 137   u8 saved_mTrace;        /* Saved trace settings */
 138   Db *pDb = 0;            /* Database to detach at end of vacuum */
 139   int isMemDb;            /* True if vacuuming a :memory: database */
 140   int nRes;               /* Bytes of reserved space at the end of each page */
 141   int nDb;                /* Number of attached databases */
 142   const char *zDbMain;    /* Schema name of database to vacuum */
 143
 144   if( !db->autoCommit ){
 145     sqlite3SetString(pzErrMsg, db, "cannot VACUUM from within a transaction");
 146     return SQLITE_ERROR;
 147   }
 148   if( db->nVdbeActive>1 ){
 149     sqlite3SetString(pzErrMsg, db,"cannot VACUUM - SQL statements in progress");
 150     return SQLITE_ERROR;
 151   }
 152
 153   /* Save the current value of the database flags so that it can be
 154   ** restored before returning. Then set the writable-schema flag, and
 155   ** disable CHECK and foreign key constraints.  */
 156   saved_flags = db->flags;
 157   saved_mDbFlags = db->mDbFlags;
 158   saved_nChange = db->nChange;
 159   saved_nTotalChange = db->nTotalChange;
 160   saved_mTrace = db->mTrace;
 161   db->flags |= SQLITE_WriteSchema | SQLITE_IgnoreChecks;
 162   db->mDbFlags |= DBFLAG_PreferBuiltin | DBFLAG_Vacuum;
 163   db->flags &= ~(SQLITE_ForeignKeys | SQLITE_ReverseOrder | SQLITE_CountRows);
 164   db->mTrace = 0;
 165
 166   zDbMain = db->aDb[iDb].zDbSName;
 167   pMain = db->aDb[iDb].pBt;
 168   isMemDb = sqlite3PagerIsMemdb(sqlite3BtreePager(pMain));
 169
 170   /* Attach the temporary database as 'vacuum_db'. The synchronous pragma
 171   ** can be set to 'off' for this file, as it is not recovered if a crash
 172   ** occurs anyway. The integrity of the database is maintained by a
 173   ** (possibly synchronous) transaction opened on the main database before
 174   ** sqlite3BtreeCopyFile() is called.
 175   **
 176   ** An optimisation would be to use a non-journaled pager.
 177   ** (Later:) I tried setting "PRAGMA vacuum_db.journal_mode=OFF" but
 178   ** that actually made the VACUUM run slower.  Very little journalling
 179   ** actually occurs when doing a vacuum since the vacuum_db is initially
 180   ** empty.  Only the journal header is written.  Apparently it takes more
 181   ** time to parse and run the PRAGMA to turn journalling off than it does
 182   ** to write the journal header file.
 183   */
 184   nDb = db->nDb;
 185   rc = execSql(db, pzErrMsg, "ATTACH''AS vacuum_db");
 186   if( rc!=SQLITE_OK ) goto end_of_vacuum;
 187   assert( (db->nDb-1)==nDb );
 188   pDb = &db->aDb[nDb];
 189   assert( strcmp(pDb->zDbSName,"vacuum_db")==0 );
 190   pTemp = pDb->pBt;
 191
 192   /* The call to execSql() to attach the temp database has left the file
 193   ** locked (as there was more than one active statement when the transaction
 194   ** to read the schema was concluded. Unlock it here so that this doesn't
 195   ** cause problems for the call to BtreeSetPageSize() below.  */
 196   sqlite3BtreeCommit(pTemp);
 197
 198   nRes = sqlite3BtreeGetOptimalReserve(pMain);
 199
 200   /* A VACUUM cannot change the pagesize of an encrypted database. */
 201 #ifdef SQLITE_HAS_CODEC
 202   if( db->nextPagesize ){
 203     extern void sqlite3CodecGetKey(sqlite3*, int, void**, int*);
 204     int nKey;
 205     char *zKey;
 206     sqlite3CodecGetKey(db, iDb, (void**)&zKey, &nKey);
 207     if( nKey ) db->nextPagesize = 0;
 208   }
 209 #endif
 210
 211   sqlite3BtreeSetCacheSize(pTemp, db->aDb[iDb].pSchema->cache_size);
 212   sqlite3BtreeSetSpillSize(pTemp, sqlite3BtreeSetSpillSize(pMain,0));
 213   sqlite3BtreeSetPagerFlags(pTemp, PAGER_SYNCHRONOUS_OFF|PAGER_CACHESPILL);
 214
 215   /* Begin a transaction and take an exclusive lock on the main database
 216   ** file. This is done before the sqlite3BtreeGetPageSize(pMain) call below,
 217   ** to ensure that we do not try to change the page-size on a WAL database.
 218   */
 219   rc = execSql(db, pzErrMsg, "BEGIN");
 220   if( rc!=SQLITE_OK ) goto end_of_vacuum;
 221   rc = sqlite3BtreeBeginTrans(pMain, 2);
 222   if( rc!=SQLITE_OK ) goto end_of_vacuum;
 223
 224   /* Do not attempt to change the page size for a WAL database */
 225   if( sqlite3PagerGetJournalMode(sqlite3BtreePager(pMain))
 226                                                ==PAGER_JOURNALMODE_WAL ){
 227     db->nextPagesize = 0;
 228   }
 229
 230   if( sqlite3BtreeSetPageSize(pTemp, sqlite3BtreeGetPageSize(pMain), nRes, 0)
 231    || (!isMemDb && sqlite3BtreeSetPageSize(pTemp, db->nextPagesize, nRes, 0))
 232    || NEVER(db->mallocFailed)
 233   ){
 234     rc = SQLITE_NOMEM_BKPT;
 235     goto end_of_vacuum;
 236   }
 237
 238 #ifndef SQLITE_OMIT_AUTOVACUUM
 239   sqlite3BtreeSetAutoVacuum(pTemp, db->nextAutovac>=0 ? db->nextAutovac :
 240                                            sqlite3BtreeGetAutoVacuum(pMain));
 241 #endif
 242
 243   /* Query the schema of the main database. Create a mirror schema
 244   ** in the temporary database.
 245   */
 246   db->init.iDb = nDb; /* force new CREATE statements into vacuum_db */
 247   rc = execSqlF(db, pzErrMsg,
 248       "SELECT sql FROM \"%w\".sqlite_master"
 249       " WHERE type='table'AND name<>'sqlite_sequence'"
 250       " AND coalesce(rootpage,1)>0",
 251       zDbMain
 252   );
 253   if( rc!=SQLITE_OK ) goto end_of_vacuum;
 254   rc = execSqlF(db, pzErrMsg,
 255       "SELECT sql FROM \"%w\".sqlite_master"
 256       " WHERE type='index' AND length(sql)>10",
 257       zDbMain
 258   );
 259   if( rc!=SQLITE_OK ) goto end_of_vacuum;
 260   db->init.iDb = 0;
 261
 262   /* Loop through the tables in the main database. For each, do
 263   ** an "INSERT INTO vacuum_db.xxx SELECT * FROM main.xxx;" to copy
 264   ** the contents to the temporary database.
 265   */
 266   rc = execSqlF(db, pzErrMsg,
 267       "SELECT'INSERT INTO vacuum_db.'||quote(name)"
 268       "||' SELECT*FROM\"%w\".'||quote(name)"
 269       "FROM vacuum_db.sqlite_master "
 270       "WHERE type='table'AND coalesce(rootpage,1)>0",
 271       zDbMain
 272   );
 273   assert( (db->mDbFlags & DBFLAG_Vacuum)!=0 );
 274   db->mDbFlags &= ~DBFLAG_Vacuum;
 275   if( rc!=SQLITE_OK ) goto end_of_vacuum;
 276
 277   /* Copy the triggers, views, and virtual tables from the main database
 278   ** over to the temporary database.  None of these objects has any
 279   ** associated storage, so all we have to do is copy their entries
 280   ** from the SQLITE_MASTER table.
 281   */
 282   rc = execSqlF(db, pzErrMsg,
 283       "INSERT INTO vacuum_db.sqlite_master"
 284       " SELECT*FROM \"%w\".sqlite_master"
 285       " WHERE type IN('view','trigger')"
 286       " OR(type='table'AND rootpage=0)",
 287       zDbMain
 288   );
 289   if( rc ) goto end_of_vacuum;
 290
 291   /* At this point, there is a write transaction open on both the
 292   ** vacuum database and the main database. Assuming no error occurs,
 293   ** both transactions are closed by this block - the main database
 294   ** transaction by sqlite3BtreeCopyFile() and the other by an explicit
 295   ** call to sqlite3BtreeCommit().
 296   */
 297   {
 298     u32 meta;
 299     int i;
 300
 301     /* This array determines which meta meta values are preserved in the
 302     ** vacuum.  Even entries are the meta value number and odd entries
 303     ** are an increment to apply to the meta value after the vacuum.
 304     ** The increment is used to increase the schema cookie so that other
 305     ** connections to the same database will know to reread the schema.
 306     */
 307     static const unsigned char aCopy[] = {
 308        BTREE_SCHEMA_VERSION,     1,  /* Add one to the old schema cookie */
 309        BTREE_DEFAULT_CACHE_SIZE, 0,  /* Preserve the default page cache size */
 310        BTREE_TEXT_ENCODING,      0,  /* Preserve the text encoding */
 311        BTREE_USER_VERSION,       0,  /* Preserve the user version */
 312        BTREE_APPLICATION_ID,     0,  /* Preserve the application id */
 313     };
 314
 315     assert( 1==sqlite3BtreeIsInTrans(pTemp) );
 316     assert( 1==sqlite3BtreeIsInTrans(pMain) );
 317
 318     /* Copy Btree meta values */
 319     for(i=0; i<ArraySize(aCopy); i+=2){
 320       /* GetMeta() and UpdateMeta() cannot fail in this context because
 321       ** we already have page 1 loaded into cache and marked dirty. */
 322       sqlite3BtreeGetMeta(pMain, aCopy[i], &meta);
 323       rc = sqlite3BtreeUpdateMeta(pTemp, aCopy[i], meta+aCopy[i+1]);
 324       if( NEVER(rc!=SQLITE_OK) ) goto end_of_vacuum;
 325     }
 326
 327     rc = sqlite3BtreeCopyFile(pMain, pTemp);
 328     if( rc!=SQLITE_OK ) goto end_of_vacuum;
 329     rc = sqlite3BtreeCommit(pTemp);
 330     if( rc!=SQLITE_OK ) goto end_of_vacuum;
 331 #ifndef SQLITE_OMIT_AUTOVACUUM
 332     sqlite3BtreeSetAutoVacuum(pMain, sqlite3BtreeGetAutoVacuum(pTemp));
 333 #endif
 334   }
 335
 336   assert( rc==SQLITE_OK );
 337   rc = sqlite3BtreeSetPageSize(pMain, sqlite3BtreeGetPageSize(pTemp), nRes,1);
 338
 339 end_of_vacuum:
 340   /* Restore the original value of db->flags */
 341   db->init.iDb = 0;
 342   db->mDbFlags = saved_mDbFlags;
 343   db->flags = saved_flags;
 344   db->nChange = saved_nChange;
 345   db->nTotalChange = saved_nTotalChange;
 346   db->mTrace = saved_mTrace;
 347   sqlite3BtreeSetPageSize(pMain, -1, -1, 1);
 348
 349   /* Currently there is an SQL level transaction open on the vacuum
 350   ** database. No locks are held on any other files (since the main file
 351   ** was committed at the btree level). So it safe to end the transaction
 352   ** by manually setting the autoCommit flag to true and detaching the
 353   ** vacuum database. The vacuum_db journal file is deleted when the pager
 354   ** is closed by the DETACH.
 355   */
 356   db->autoCommit = 1;
 357
 358   if( pDb ){
 359     sqlite3BtreeClose(pDb->pBt);
 360     pDb->pBt = 0;
 361     pDb->pSchema = 0;
 362   }
 363
 364   /* This both clears the schemas and reduces the size of the db->aDb[]
 365   ** array. */
 366   sqlite3ResetAllSchemasOfConnection(db);
 367
 368   return rc;
 369 }
 370
 371 #endif  /* SQLITE_OMIT_VACUUM && SQLITE_OMIT_ATTACH */