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     /* 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   /* A VACUUM cannot change the pagesize of an encrypted database. */
 245 /* BEGIN SQLCIPHER */
 246 #ifdef SQLITE_HAS_CODEC
 247   if( db->nextPagesize ){
 248     extern void sqlcipherCodecGetKey(sqlite3*, int, void**, int*);
 249     int nKey;
 250     char *zKey;
 251     sqlcipherCodecGetKey(db, iDb, (void**)&zKey, &nKey);
 252     if( nKey ) db->nextPagesize = 0;
 253   }
 254 #endif
 255 /* END SQLCIPHER */
 256
 257   sqlite3BtreeSetCacheSize(pTemp, db->aDb[iDb].pSchema->cache_size);
 258   sqlite3BtreeSetSpillSize(pTemp, sqlite3BtreeSetSpillSize(pMain,0));
 259   sqlite3BtreeSetPagerFlags(pTemp, pgflags|PAGER_CACHESPILL);
 260
 261   /* Begin a transaction and take an exclusive lock on the main database
 262   ** file. This is done before the sqlite3BtreeGetPageSize(pMain) call below,
 263   ** to ensure that we do not try to change the page-size on a WAL database.
 264   */
 265   rc = execSql(db, pzErrMsg, "BEGIN");
 266   if( rc!=SQLITE_OK ) goto end_of_vacuum;
 267   rc = sqlite3BtreeBeginTrans(pMain, pOut==0 ? 2 : 0, 0);
 268   if( rc!=SQLITE_OK ) goto end_of_vacuum;
 269
 270   /* Do not attempt to change the page size for a WAL database */
 271   if( sqlite3PagerGetJournalMode(sqlite3BtreePager(pMain))
 272                                                ==PAGER_JOURNALMODE_WAL
 273    && pOut==0
 274   ){
 275     db->nextPagesize = 0;
 276   }
 277
 278   if( sqlite3BtreeSetPageSize(pTemp, sqlite3BtreeGetPageSize(pMain), nRes, 0)
 279    || (!isMemDb && sqlite3BtreeSetPageSize(pTemp, db->nextPagesize, nRes, 0))
 280    || NEVER(db->mallocFailed)
 281   ){
 282     rc = SQLITE_NOMEM_BKPT;
 283     goto end_of_vacuum;
 284   }
 285
 286 #ifndef SQLITE_OMIT_AUTOVACUUM
 287   sqlite3BtreeSetAutoVacuum(pTemp, db->nextAutovac>=0 ? db->nextAutovac :
 288                                            sqlite3BtreeGetAutoVacuum(pMain));
 289 #endif
 290
 291   /* Query the schema of the main database. Create a mirror schema
 292   ** in the temporary database.
 293   */
 294   db->init.iDb = nDb; /* force new CREATE statements into vacuum_db */
 295   rc = execSqlF(db, pzErrMsg,
 296       "SELECT sql FROM \"%w\".sqlite_schema"
 297       " WHERE type='table'AND name<>'sqlite_sequence'"
 298       " AND coalesce(rootpage,1)>0",
 299       zDbMain
 300   );
 301   if( rc!=SQLITE_OK ) goto end_of_vacuum;
 302   rc = execSqlF(db, pzErrMsg,
 303       "SELECT sql FROM \"%w\".sqlite_schema"
 304       " WHERE type='index'",
 305       zDbMain
 306   );
 307   if( rc!=SQLITE_OK ) goto end_of_vacuum;
 308   db->init.iDb = 0;
 309
 310   /* Loop through the tables in the main database. For each, do
 311   ** an "INSERT INTO vacuum_db.xxx SELECT * FROM main.xxx;" to copy
 312   ** the contents to the temporary database.
 313   */
 314   rc = execSqlF(db, pzErrMsg,
 315       "SELECT'INSERT INTO vacuum_db.'||quote(name)"
 316       "||' SELECT*FROM\"%w\".'||quote(name)"
 317       "FROM vacuum_db.sqlite_schema "
 318       "WHERE type='table'AND coalesce(rootpage,1)>0",
 319       zDbMain
 320   );
 321   assert( (db->mDbFlags & DBFLAG_Vacuum)!=0 );
 322   db->mDbFlags &= ~DBFLAG_Vacuum;
 323   if( rc!=SQLITE_OK ) goto end_of_vacuum;
 324
 325   /* Copy the triggers, views, and virtual tables from the main database
 326   ** over to the temporary database.  None of these objects has any
 327   ** associated storage, so all we have to do is copy their entries
 328   ** from the schema table.
 329   */
 330   rc = execSqlF(db, pzErrMsg,
 331       "INSERT INTO vacuum_db.sqlite_schema"
 332       " SELECT*FROM \"%w\".sqlite_schema"
 333       " WHERE type IN('view','trigger')"
 334       " OR(type='table'AND rootpage=0)",
 335       zDbMain
 336   );
 337   if( rc ) goto end_of_vacuum;
 338
 339   /* At this point, there is a write transaction open on both the
 340   ** vacuum database and the main database. Assuming no error occurs,
 341   ** both transactions are closed by this block - the main database
 342   ** transaction by sqlite3BtreeCopyFile() and the other by an explicit
 343   ** call to sqlite3BtreeCommit().
 344   */
 345   {
 346     u32 meta;
 347     int i;
 348
 349     /* This array determines which meta meta values are preserved in the
 350     ** vacuum.  Even entries are the meta value number and odd entries
 351     ** are an increment to apply to the meta value after the vacuum.
 352     ** The increment is used to increase the schema cookie so that other
 353     ** connections to the same database will know to reread the schema.
 354     */
 355     static const unsigned char aCopy[] = {
 356        BTREE_SCHEMA_VERSION,     1,  /* Add one to the old schema cookie */
 357        BTREE_DEFAULT_CACHE_SIZE, 0,  /* Preserve the default page cache size */
 358        BTREE_TEXT_ENCODING,      0,  /* Preserve the text encoding */
 359        BTREE_USER_VERSION,       0,  /* Preserve the user version */
 360        BTREE_APPLICATION_ID,     0,  /* Preserve the application id */
 361     };
 362
 363     assert( SQLITE_TXN_WRITE==sqlite3BtreeTxnState(pTemp) );
 364     assert( pOut!=0 || SQLITE_TXN_WRITE==sqlite3BtreeTxnState(pMain) );
 365
 366     /* Copy Btree meta values */
 367     for(i=0; i<ArraySize(aCopy); i+=2){
 368       /* GetMeta() and UpdateMeta() cannot fail in this context because
 369       ** we already have page 1 loaded into cache and marked dirty. */
 370       sqlite3BtreeGetMeta(pMain, aCopy[i], &meta);
 371       rc = sqlite3BtreeUpdateMeta(pTemp, aCopy[i], meta+aCopy[i+1]);
 372       if( NEVER(rc!=SQLITE_OK) ) goto end_of_vacuum;
 373     }
 374
 375     if( pOut==0 ){
 376       rc = sqlite3BtreeCopyFile(pMain, pTemp);
 377     }
 378     if( rc!=SQLITE_OK ) goto end_of_vacuum;
 379     rc = sqlite3BtreeCommit(pTemp);
 380     if( rc!=SQLITE_OK ) goto end_of_vacuum;
 381 #ifndef SQLITE_OMIT_AUTOVACUUM
 382     if( pOut==0 ){
 383       sqlite3BtreeSetAutoVacuum(pMain, sqlite3BtreeGetAutoVacuum(pTemp));
 384     }
 385 #endif
 386   }
 387
 388   assert( rc==SQLITE_OK );
 389   if( pOut==0 ){
 390     nRes = sqlite3BtreeGetRequestedReserve(pTemp);
 391     rc = sqlite3BtreeSetPageSize(pMain, sqlite3BtreeGetPageSize(pTemp), nRes,1);
 392   }
 393
 394 end_of_vacuum:
 395   /* Restore the original value of db->flags */
 396   db->init.iDb = 0;
 397   db->mDbFlags = saved_mDbFlags;
 398   db->flags = saved_flags;
 399   db->nChange = saved_nChange;
 400   db->nTotalChange = saved_nTotalChange;
 401   db->mTrace = saved_mTrace;
 402   sqlite3BtreeSetPageSize(pMain, -1, 0, 1);
 403
 404   /* Currently there is an SQL level transaction open on the vacuum
 405   ** database. No locks are held on any other files (since the main file
 406   ** was committed at the btree level). So it safe to end the transaction
 407   ** by manually setting the autoCommit flag to true and detaching the
 408   ** vacuum database. The vacuum_db journal file is deleted when the pager
 409   ** is closed by the DETACH.
 410   */
 411   db->autoCommit = 1;
 412
 413   if( pDb ){
 414     sqlite3BtreeClose(pDb->pBt);
 415     pDb->pBt = 0;
 416     pDb->pSchema = 0;
 417   }
 418
 419   /* This both clears the schemas and reduces the size of the db->aDb[]
 420   ** array. */
 421   sqlite3ResetAllSchemasOfConnection(db);
 422
 423   return rc;
 424 }
 425
 426 #endif  /* SQLITE_OMIT_VACUUM && SQLITE_OMIT_ATTACH */