000001  /*
000002  ** 2003 April 6
000003  **
000004  ** The author disclaims copyright to this source code.  In place of
000005  ** a legal notice, here is a blessing:
000006  **
000007  **    May you do good and not evil.
000008  **    May you find forgiveness for yourself and forgive others.
000009  **    May you share freely, never taking more than you give.
000010  **
000011  *************************************************************************
000012  ** This file contains code used to implement the VACUUM command.
000013  **
000014  ** Most of the code in this file may be omitted by defining the
000015  ** SQLITE_OMIT_VACUUM macro.
000016  */
000017  #include "sqliteInt.h"
000018  #include "vdbeInt.h"
000019  
000020  #if !defined(SQLITE_OMIT_VACUUM) && !defined(SQLITE_OMIT_ATTACH)
000021  
000022  /*
000023  ** Execute zSql on database db.
000024  **
000025  ** If zSql returns rows, then each row will have exactly one
000026  ** column.  (This will only happen if zSql begins with "SELECT".)
000027  ** Take each row of result and call execSql() again recursively.
000028  **
000029  ** The execSqlF() routine does the same thing, except it accepts
000030  ** a format string as its third argument
000031  */
000032  static int execSql(sqlite3 *db, char **pzErrMsg, const char *zSql){
000033    sqlite3_stmt *pStmt;
000034    int rc;
000035  
000036    /* printf("SQL: [%s]\n", zSql); fflush(stdout); */
000037    rc = sqlite3_prepare_v2(db, zSql, -1, &pStmt, 0);
000038    if( rc!=SQLITE_OK ) return rc;
000039    while( SQLITE_ROW==(rc = sqlite3_step(pStmt)) ){
000040      const char *zSubSql = (const char*)sqlite3_column_text(pStmt,0);
000041      assert( sqlite3_strnicmp(zSql,"SELECT",6)==0 );
000042      /* The secondary SQL must be one of CREATE TABLE, CREATE INDEX,
000043      ** or INSERT.  Historically there have been attacks that first
000044      ** corrupt the sqlite_master.sql field with other kinds of statements
000045      ** then run VACUUM to get those statements to execute at inappropriate
000046      ** times. */
000047      if( zSubSql
000048       && (strncmp(zSubSql,"CRE",3)==0 || strncmp(zSubSql,"INS",3)==0)
000049      ){
000050        rc = execSql(db, pzErrMsg, zSubSql);
000051        if( rc!=SQLITE_OK ) break;
000052      }
000053    }
000054    assert( rc!=SQLITE_ROW );
000055    if( rc==SQLITE_DONE ) rc = SQLITE_OK;
000056    if( rc ){
000057      sqlite3SetString(pzErrMsg, db, sqlite3_errmsg(db));
000058    }
000059    (void)sqlite3_finalize(pStmt);
000060    return rc;
000061  }
000062  static int execSqlF(sqlite3 *db, char **pzErrMsg, const char *zSql, ...){
000063    char *z;
000064    va_list ap;
000065    int rc;
000066    va_start(ap, zSql);
000067    z = sqlite3VMPrintf(db, zSql, ap);
000068    va_end(ap);
000069    if( z==0 ) return SQLITE_NOMEM;
000070    rc = execSql(db, pzErrMsg, z);
000071    sqlite3DbFree(db, z);
000072    return rc;
000073  }
000074  
000075  /*
000076  ** The VACUUM command is used to clean up the database,
000077  ** collapse free space, etc.  It is modelled after the VACUUM command
000078  ** in PostgreSQL.  The VACUUM command works as follows:
000079  **
000080  **   (1)  Create a new transient database file
000081  **   (2)  Copy all content from the database being vacuumed into
000082  **        the new transient database file
000083  **   (3)  Copy content from the transient database back into the
000084  **        original database.
000085  **
000086  ** The transient database requires temporary disk space approximately
000087  ** equal to the size of the original database.  The copy operation of
000088  ** step (3) requires additional temporary disk space approximately equal
000089  ** to the size of the original database for the rollback journal.
000090  ** Hence, temporary disk space that is approximately 2x the size of the
000091  ** original database is required.  Every page of the database is written
000092  ** approximately 3 times:  Once for step (2) and twice for step (3).
000093  ** Two writes per page are required in step (3) because the original
000094  ** database content must be written into the rollback journal prior to
000095  ** overwriting the database with the vacuumed content.
000096  **
000097  ** Only 1x temporary space and only 1x writes would be required if
000098  ** the copy of step (3) were replaced by deleting the original database
000099  ** and renaming the transient database as the original.  But that will
000100  ** not work if other processes are attached to the original database.
000101  ** And a power loss in between deleting the original and renaming the
000102  ** transient would cause the database file to appear to be deleted
000103  ** following reboot.
000104  */
000105  void sqlite3Vacuum(Parse *pParse, Token *pNm, Expr *pInto){
000106    Vdbe *v = sqlite3GetVdbe(pParse);
000107    int iDb = 0;
000108    if( v==0 ) goto build_vacuum_end;
000109    if( pParse->nErr ) goto build_vacuum_end;
000110    if( pNm ){
000111  #ifndef SQLITE_BUG_COMPATIBLE_20160819
000112      /* Default behavior:  Report an error if the argument to VACUUM is
000113      ** not recognized */
000114      iDb = sqlite3TwoPartName(pParse, pNm, pNm, &pNm);
000115      if( iDb<0 ) goto build_vacuum_end;
000116  #else
000117      /* When SQLITE_BUG_COMPATIBLE_20160819 is defined, unrecognized arguments
000118      ** to VACUUM are silently ignored.  This is a back-out of a bug fix that
000119      ** occurred on 2016-08-19 (https://www.sqlite.org/src/info/083f9e6270).
000120      ** The buggy behavior is required for binary compatibility with some
000121      ** legacy applications. */
000122      iDb = sqlite3FindDb(pParse->db, pNm);
000123      if( iDb<0 ) iDb = 0;
000124  #endif
000125    }
000126    if( iDb!=1 ){
000127      int iIntoReg = 0;
000128      if( pInto && sqlite3ResolveSelfReference(pParse,0,0,pInto,0)==0 ){
000129        iIntoReg = ++pParse->nMem;
000130        sqlite3ExprCode(pParse, pInto, iIntoReg);
000131      }
000132      sqlite3VdbeAddOp2(v, OP_Vacuum, iDb, iIntoReg);
000133      sqlite3VdbeUsesBtree(v, iDb);
000134    }
000135  build_vacuum_end:
000136    sqlite3ExprDelete(pParse->db, pInto);
000137    return;
000138  }
000139  
000140  /*
000141  ** This routine implements the OP_Vacuum opcode of the VDBE.
000142  */
000143  SQLITE_NOINLINE int sqlite3RunVacuum(
000144    char **pzErrMsg,        /* Write error message here */
000145    sqlite3 *db,            /* Database connection */
000146    int iDb,                /* Which attached DB to vacuum */
000147    sqlite3_value *pOut     /* Write results here, if not NULL. VACUUM INTO */
000148  ){
000149    int rc = SQLITE_OK;     /* Return code from service routines */
000150    Btree *pMain;           /* The database being vacuumed */
000151    Btree *pTemp;           /* The temporary database we vacuum into */
000152    u32 saved_mDbFlags;     /* Saved value of db->mDbFlags */
000153    u64 saved_flags;        /* Saved value of db->flags */
000154    int saved_nChange;      /* Saved value of db->nChange */
000155    int saved_nTotalChange; /* Saved value of db->nTotalChange */
000156    u32 saved_openFlags;    /* Saved value of db->openFlags */
000157    u8 saved_mTrace;        /* Saved trace settings */
000158    Db *pDb = 0;            /* Database to detach at end of vacuum */
000159    int isMemDb;            /* True if vacuuming a :memory: database */
000160    int nRes;               /* Bytes of reserved space at the end of each page */
000161    int nDb;                /* Number of attached databases */
000162    const char *zDbMain;    /* Schema name of database to vacuum */
000163    const char *zOut;       /* Name of output file */
000164  
000165    if( !db->autoCommit ){
000166      sqlite3SetString(pzErrMsg, db, "cannot VACUUM from within a transaction");
000167      return SQLITE_ERROR; /* IMP: R-12218-18073 */
000168    }
000169    if( db->nVdbeActive>1 ){
000170      sqlite3SetString(pzErrMsg, db,"cannot VACUUM - SQL statements in progress");
000171      return SQLITE_ERROR; /* IMP: R-15610-35227 */
000172    }
000173    saved_openFlags = db->openFlags;
000174    if( pOut ){
000175      if( sqlite3_value_type(pOut)!=SQLITE_TEXT ){
000176        sqlite3SetString(pzErrMsg, db, "non-text filename");
000177        return SQLITE_ERROR;
000178      }
000179      zOut = (const char*)sqlite3_value_text(pOut);
000180      db->openFlags &= ~SQLITE_OPEN_READONLY;
000181      db->openFlags |= SQLITE_OPEN_CREATE|SQLITE_OPEN_READWRITE;
000182    }else{
000183      zOut = "";
000184    }
000185  
000186    /* Save the current value of the database flags so that it can be 
000187    ** restored before returning. Then set the writable-schema flag, and
000188    ** disable CHECK and foreign key constraints.  */
000189    saved_flags = db->flags;
000190    saved_mDbFlags = db->mDbFlags;
000191    saved_nChange = db->nChange;
000192    saved_nTotalChange = db->nTotalChange;
000193    saved_mTrace = db->mTrace;
000194    db->flags |= SQLITE_WriteSchema | SQLITE_IgnoreChecks;
000195    db->mDbFlags |= DBFLAG_PreferBuiltin | DBFLAG_Vacuum;
000196    db->flags &= ~(u64)(SQLITE_ForeignKeys | SQLITE_ReverseOrder
000197                     | SQLITE_Defensive | SQLITE_CountRows);
000198    db->mTrace = 0;
000199  
000200    zDbMain = db->aDb[iDb].zDbSName;
000201    pMain = db->aDb[iDb].pBt;
000202    isMemDb = sqlite3PagerIsMemdb(sqlite3BtreePager(pMain));
000203  
000204    /* Attach the temporary database as 'vacuum_db'. The synchronous pragma
000205    ** can be set to 'off' for this file, as it is not recovered if a crash
000206    ** occurs anyway. The integrity of the database is maintained by a
000207    ** (possibly synchronous) transaction opened on the main database before
000208    ** sqlite3BtreeCopyFile() is called.
000209    **
000210    ** An optimisation would be to use a non-journaled pager.
000211    ** (Later:) I tried setting "PRAGMA vacuum_db.journal_mode=OFF" but
000212    ** that actually made the VACUUM run slower.  Very little journalling
000213    ** actually occurs when doing a vacuum since the vacuum_db is initially
000214    ** empty.  Only the journal header is written.  Apparently it takes more
000215    ** time to parse and run the PRAGMA to turn journalling off than it does
000216    ** to write the journal header file.
000217    */
000218    nDb = db->nDb;
000219    rc = execSqlF(db, pzErrMsg, "ATTACH %Q AS vacuum_db", zOut);
000220    db->openFlags = saved_openFlags;
000221    if( rc!=SQLITE_OK ) goto end_of_vacuum;
000222    assert( (db->nDb-1)==nDb );
000223    pDb = &db->aDb[nDb];
000224    assert( strcmp(pDb->zDbSName,"vacuum_db")==0 );
000225    pTemp = pDb->pBt;
000226    if( pOut ){
000227      sqlite3_file *id = sqlite3PagerFile(sqlite3BtreePager(pTemp));
000228      i64 sz = 0;
000229      if( id->pMethods!=0 && (sqlite3OsFileSize(id, &sz)!=SQLITE_OK || sz>0) ){
000230        rc = SQLITE_ERROR;
000231        sqlite3SetString(pzErrMsg, db, "output file already exists");
000232        goto end_of_vacuum;
000233      }
000234      db->mDbFlags |= DBFLAG_VacuumInto;
000235    }
000236    nRes = sqlite3BtreeGetOptimalReserve(pMain);
000237  
000238    /* A VACUUM cannot change the pagesize of an encrypted database. */
000239  #ifdef SQLITE_HAS_CODEC
000240    if( db->nextPagesize ){
000241      extern void sqlite3CodecGetKey(sqlite3*, int, void**, int*);
000242      int nKey;
000243      char *zKey;
000244      sqlite3CodecGetKey(db, iDb, (void**)&zKey, &nKey);
000245      if( nKey ) db->nextPagesize = 0;
000246    }
000247  #endif
000248  
000249    sqlite3BtreeSetCacheSize(pTemp, db->aDb[iDb].pSchema->cache_size);
000250    sqlite3BtreeSetSpillSize(pTemp, sqlite3BtreeSetSpillSize(pMain,0));
000251    sqlite3BtreeSetPagerFlags(pTemp, PAGER_SYNCHRONOUS_OFF|PAGER_CACHESPILL);
000252  
000253    /* Begin a transaction and take an exclusive lock on the main database
000254    ** file. This is done before the sqlite3BtreeGetPageSize(pMain) call below,
000255    ** to ensure that we do not try to change the page-size on a WAL database.
000256    */
000257    rc = execSql(db, pzErrMsg, "BEGIN");
000258    if( rc!=SQLITE_OK ) goto end_of_vacuum;
000259    rc = sqlite3BtreeBeginTrans(pMain, pOut==0 ? 2 : 0, 0);
000260    if( rc!=SQLITE_OK ) goto end_of_vacuum;
000261  
000262    /* Do not attempt to change the page size for a WAL database */
000263    if( sqlite3PagerGetJournalMode(sqlite3BtreePager(pMain))
000264                                                 ==PAGER_JOURNALMODE_WAL ){
000265      db->nextPagesize = 0;
000266    }
000267  
000268    if( sqlite3BtreeSetPageSize(pTemp, sqlite3BtreeGetPageSize(pMain), nRes, 0)
000269     || (!isMemDb && sqlite3BtreeSetPageSize(pTemp, db->nextPagesize, nRes, 0))
000270     || NEVER(db->mallocFailed)
000271    ){
000272      rc = SQLITE_NOMEM_BKPT;
000273      goto end_of_vacuum;
000274    }
000275  
000276  #ifndef SQLITE_OMIT_AUTOVACUUM
000277    sqlite3BtreeSetAutoVacuum(pTemp, db->nextAutovac>=0 ? db->nextAutovac :
000278                                             sqlite3BtreeGetAutoVacuum(pMain));
000279  #endif
000280  
000281    /* Query the schema of the main database. Create a mirror schema
000282    ** in the temporary database.
000283    */
000284    db->init.iDb = nDb; /* force new CREATE statements into vacuum_db */
000285    rc = execSqlF(db, pzErrMsg,
000286        "SELECT sql FROM \"%w\".sqlite_master"
000287        " WHERE type='table'AND name<>'sqlite_sequence'"
000288        " AND coalesce(rootpage,1)>0",
000289        zDbMain
000290    );
000291    if( rc!=SQLITE_OK ) goto end_of_vacuum;
000292    rc = execSqlF(db, pzErrMsg,
000293        "SELECT sql FROM \"%w\".sqlite_master"
000294        " WHERE type='index'",
000295        zDbMain
000296    );
000297    if( rc!=SQLITE_OK ) goto end_of_vacuum;
000298    db->init.iDb = 0;
000299  
000300    /* Loop through the tables in the main database. For each, do
000301    ** an "INSERT INTO vacuum_db.xxx SELECT * FROM main.xxx;" to copy
000302    ** the contents to the temporary database.
000303    */
000304    rc = execSqlF(db, pzErrMsg,
000305        "SELECT'INSERT INTO vacuum_db.'||quote(name)"
000306        "||' SELECT*FROM\"%w\".'||quote(name)"
000307        "FROM vacuum_db.sqlite_master "
000308        "WHERE type='table'AND coalesce(rootpage,1)>0",
000309        zDbMain
000310    );
000311    assert( (db->mDbFlags & DBFLAG_Vacuum)!=0 );
000312    db->mDbFlags &= ~DBFLAG_Vacuum;
000313    if( rc!=SQLITE_OK ) goto end_of_vacuum;
000314  
000315    /* Copy the triggers, views, and virtual tables from the main database
000316    ** over to the temporary database.  None of these objects has any
000317    ** associated storage, so all we have to do is copy their entries
000318    ** from the SQLITE_MASTER table.
000319    */
000320    rc = execSqlF(db, pzErrMsg,
000321        "INSERT INTO vacuum_db.sqlite_master"
000322        " SELECT*FROM \"%w\".sqlite_master"
000323        " WHERE type IN('view','trigger')"
000324        " OR(type='table'AND rootpage=0)",
000325        zDbMain
000326    );
000327    if( rc ) goto end_of_vacuum;
000328  
000329    /* At this point, there is a write transaction open on both the 
000330    ** vacuum database and the main database. Assuming no error occurs,
000331    ** both transactions are closed by this block - the main database
000332    ** transaction by sqlite3BtreeCopyFile() and the other by an explicit
000333    ** call to sqlite3BtreeCommit().
000334    */
000335    {
000336      u32 meta;
000337      int i;
000338  
000339      /* This array determines which meta meta values are preserved in the
000340      ** vacuum.  Even entries are the meta value number and odd entries
000341      ** are an increment to apply to the meta value after the vacuum.
000342      ** The increment is used to increase the schema cookie so that other
000343      ** connections to the same database will know to reread the schema.
000344      */
000345      static const unsigned char aCopy[] = {
000346         BTREE_SCHEMA_VERSION,     1,  /* Add one to the old schema cookie */
000347         BTREE_DEFAULT_CACHE_SIZE, 0,  /* Preserve the default page cache size */
000348         BTREE_TEXT_ENCODING,      0,  /* Preserve the text encoding */
000349         BTREE_USER_VERSION,       0,  /* Preserve the user version */
000350         BTREE_APPLICATION_ID,     0,  /* Preserve the application id */
000351      };
000352  
000353      assert( 1==sqlite3BtreeIsInTrans(pTemp) );
000354      assert( pOut!=0 || 1==sqlite3BtreeIsInTrans(pMain) );
000355  
000356      /* Copy Btree meta values */
000357      for(i=0; i<ArraySize(aCopy); i+=2){
000358        /* GetMeta() and UpdateMeta() cannot fail in this context because
000359        ** we already have page 1 loaded into cache and marked dirty. */
000360        sqlite3BtreeGetMeta(pMain, aCopy[i], &meta);
000361        rc = sqlite3BtreeUpdateMeta(pTemp, aCopy[i], meta+aCopy[i+1]);
000362        if( NEVER(rc!=SQLITE_OK) ) goto end_of_vacuum;
000363      }
000364  
000365      if( pOut==0 ){
000366        rc = sqlite3BtreeCopyFile(pMain, pTemp);
000367      }
000368      if( rc!=SQLITE_OK ) goto end_of_vacuum;
000369      rc = sqlite3BtreeCommit(pTemp);
000370      if( rc!=SQLITE_OK ) goto end_of_vacuum;
000371  #ifndef SQLITE_OMIT_AUTOVACUUM
000372      if( pOut==0 ){
000373        sqlite3BtreeSetAutoVacuum(pMain, sqlite3BtreeGetAutoVacuum(pTemp));
000374      }
000375  #endif
000376    }
000377  
000378    assert( rc==SQLITE_OK );
000379    if( pOut==0 ){
000380      rc = sqlite3BtreeSetPageSize(pMain, sqlite3BtreeGetPageSize(pTemp), nRes,1);
000381    }
000382  
000383  end_of_vacuum:
000384    /* Restore the original value of db->flags */
000385    db->init.iDb = 0;
000386    db->mDbFlags = saved_mDbFlags;
000387    db->flags = saved_flags;
000388    db->nChange = saved_nChange;
000389    db->nTotalChange = saved_nTotalChange;
000390    db->mTrace = saved_mTrace;
000391    sqlite3BtreeSetPageSize(pMain, -1, -1, 1);
000392  
000393    /* Currently there is an SQL level transaction open on the vacuum
000394    ** database. No locks are held on any other files (since the main file
000395    ** was committed at the btree level). So it safe to end the transaction
000396    ** by manually setting the autoCommit flag to true and detaching the
000397    ** vacuum database. The vacuum_db journal file is deleted when the pager
000398    ** is closed by the DETACH.
000399    */
000400    db->autoCommit = 1;
000401  
000402    if( pDb ){
000403      sqlite3BtreeClose(pDb->pBt);
000404      pDb->pBt = 0;
000405      pDb->pSchema = 0;
000406    }
000407  
000408    /* This both clears the schemas and reduces the size of the db->aDb[]
000409    ** array. */ 
000410    sqlite3ResetAllSchemasOfConnection(db);
000411  
000412    return rc;
000413  }
000414  
000415  #endif  /* SQLITE_OMIT_VACUUM && SQLITE_OMIT_ATTACH */