ds@1952: /*
ds@1952: ** 2003 April 6
ds@1952: **
ds@1952: ** The author disclaims copyright to this source code.  In place of
ds@1952: ** a legal notice, here is a blessing:
ds@1952: **
ds@1952: **    May you do good and not evil.
ds@1952: **    May you find forgiveness for yourself and forgive others.
ds@1952: **    May you share freely, never taking more than you give.
ds@1952: **
ds@1952: *************************************************************************
ds@1952: ** This file contains code used to implement the VACUUM command.
ds@1952: **
ds@1952: ** Most of the code in this file may be omitted by defining the
ds@1952: ** SQLITE_OMIT_VACUUM macro.
ds@1952: **
ds@1952: ** $Id$
ds@1952: */
ds@1952: #include "sqliteInt.h"
ds@1952: #include "vdbeInt.h"
ds@1952: 
ds@1952: #if !defined(SQLITE_OMIT_VACUUM) && !defined(SQLITE_OMIT_ATTACH)
ds@1952: /*
ds@1952: ** Execute zSql on database db. Return an error code.
ds@1952: */
ds@1952: static int execSql(sqlite3 *db, const char *zSql){
ds@1952:   sqlite3_stmt *pStmt;
ds@1952:   if( !zSql ){
ds@1952:     return SQLITE_NOMEM;
ds@1952:   }
ds@1952:   if( SQLITE_OK!=sqlite3_prepare(db, zSql, -1, &pStmt, 0) ){
ds@1952:     return sqlite3_errcode(db);
ds@1952:   }
ds@1952:   while( SQLITE_ROW==sqlite3_step(pStmt) ){}
ds@1952:   return sqlite3_finalize(pStmt);
ds@1952: }
ds@1952: 
ds@1952: /*
ds@1952: ** Execute zSql on database db. The statement returns exactly
ds@1952: ** one column. Execute this as SQL on the same database.
ds@1952: */
ds@1952: static int execExecSql(sqlite3 *db, const char *zSql){
ds@1952:   sqlite3_stmt *pStmt;
ds@1952:   int rc;
ds@1952: 
ds@1952:   rc = sqlite3_prepare(db, zSql, -1, &pStmt, 0);
ds@1952:   if( rc!=SQLITE_OK ) return rc;
ds@1952: 
ds@1952:   while( SQLITE_ROW==sqlite3_step(pStmt) ){
ds@1952:     rc = execSql(db, (char*)sqlite3_column_text(pStmt, 0));
ds@1952:     if( rc!=SQLITE_OK ){
ds@1952:       sqlite3_finalize(pStmt);
ds@1952:       return rc;
ds@1952:     }
ds@1952:   }
ds@1952: 
ds@1952:   return sqlite3_finalize(pStmt);
ds@1952: }
ds@1952: 
ds@1952: /*
ds@1952: ** The non-standard VACUUM command is used to clean up the database,
ds@1952: ** collapse free space, etc.  It is modelled after the VACUUM command
ds@1952: ** in PostgreSQL.
ds@1952: **
ds@1952: ** In version 1.0.x of SQLite, the VACUUM command would call
ds@1952: ** gdbm_reorganize() on all the database tables.  But beginning
ds@1952: ** with 2.0.0, SQLite no longer uses GDBM so this command has
ds@1952: ** become a no-op.
ds@1952: */
ds@1952: void sqlite3Vacuum(Parse *pParse){
ds@1952:   Vdbe *v = sqlite3GetVdbe(pParse);
ds@1952:   if( v ){
ds@1952:     sqlite3VdbeAddOp(v, OP_Vacuum, 0, 0);
ds@1952:   }
ds@1952:   return;
ds@1952: }
ds@1952: 
ds@1952: /*
ds@1952: ** This routine implements the OP_Vacuum opcode of the VDBE.
ds@1952: */
ds@1952: int sqlite3RunVacuum(char **pzErrMsg, sqlite3 *db){
ds@1952:   int rc = SQLITE_OK;     /* Return code from service routines */
ds@1952:   Btree *pMain;           /* The database being vacuumed */
ds@1952:   Btree *pTemp;           /* The temporary database we vacuum into */
ds@1952:   char *zSql = 0;         /* SQL statements */
ds@1952:   int saved_flags;        /* Saved value of the db->flags */
ds@1952:   Db *pDb = 0;            /* Database to detach at end of vacuum */
ds@1952: 
ds@1952:   /* Save the current value of the write-schema flag before setting it. */
ds@1952:   saved_flags = db->flags;
ds@1952:   db->flags |= SQLITE_WriteSchema | SQLITE_IgnoreChecks;
ds@1952: 
ds@1952:   if( !db->autoCommit ){
ds@1952:     sqlite3SetString(pzErrMsg, "cannot VACUUM from within a transaction", 
ds@1952:        (char*)0);
ds@1952:     rc = SQLITE_ERROR;
ds@1952:     goto end_of_vacuum;
ds@1952:   }
ds@1952:   pMain = db->aDb[0].pBt;
ds@1952: 
ds@1952:   /* Attach the temporary database as 'vacuum_db'. The synchronous pragma
ds@1952:   ** can be set to 'off' for this file, as it is not recovered if a crash
ds@1952:   ** occurs anyway. The integrity of the database is maintained by a
ds@1952:   ** (possibly synchronous) transaction opened on the main database before
ds@1952:   ** sqlite3BtreeCopyFile() is called.
ds@1952:   **
ds@1952:   ** An optimisation would be to use a non-journaled pager.
ds@1952:   */
ds@1952:   zSql = "ATTACH '' AS vacuum_db;";
ds@1952:   rc = execSql(db, zSql);
ds@1952:   if( rc!=SQLITE_OK ) goto end_of_vacuum;
ds@1952:   pDb = &db->aDb[db->nDb-1];
ds@1952:   assert( strcmp(db->aDb[db->nDb-1].zName,"vacuum_db")==0 );
ds@1952:   pTemp = db->aDb[db->nDb-1].pBt;
ds@1952:   sqlite3BtreeSetPageSize(pTemp, sqlite3BtreeGetPageSize(pMain),
ds@1952:      sqlite3BtreeGetReserve(pMain));
ds@1952:   if( db->mallocFailed ){
ds@1952:     rc = SQLITE_NOMEM;
ds@1952:     goto end_of_vacuum;
ds@1952:   }
ds@1952:   assert( sqlite3BtreeGetPageSize(pTemp)==sqlite3BtreeGetPageSize(pMain) );
ds@1952:   rc = execSql(db, "PRAGMA vacuum_db.synchronous=OFF");
ds@1952:   if( rc!=SQLITE_OK ){
ds@1952:     goto end_of_vacuum;
ds@1952:   }
ds@1952: 
ds@1952: #ifndef SQLITE_OMIT_AUTOVACUUM
ds@1952:   sqlite3BtreeSetAutoVacuum(pTemp, sqlite3BtreeGetAutoVacuum(pMain));
ds@1952: #endif
ds@1952: 
ds@1952:   /* Begin a transaction */
ds@1952:   rc = execSql(db, "BEGIN EXCLUSIVE;");
ds@1952:   if( rc!=SQLITE_OK ) goto end_of_vacuum;
ds@1952: 
ds@1952:   /* Query the schema of the main database. Create a mirror schema
ds@1952:   ** in the temporary database.
ds@1952:   */
ds@1952:   rc = execExecSql(db, 
ds@1952:       "SELECT 'CREATE TABLE vacuum_db.' || substr(sql,14,100000000) "
ds@1952:       "  FROM sqlite_master WHERE type='table' AND name!='sqlite_sequence'"
ds@1952:       "   AND rootpage>0"
ds@1952:   );
ds@1952:   if( rc!=SQLITE_OK ) goto end_of_vacuum;
ds@1952:   rc = execExecSql(db, 
ds@1952:       "SELECT 'CREATE INDEX vacuum_db.' || substr(sql,14,100000000)"
ds@1952:       "  FROM sqlite_master WHERE sql LIKE 'CREATE INDEX %' ");
ds@1952:   if( rc!=SQLITE_OK ) goto end_of_vacuum;
ds@1952:   rc = execExecSql(db, 
ds@1952:       "SELECT 'CREATE UNIQUE INDEX vacuum_db.' || substr(sql,21,100000000) "
ds@1952:       "  FROM sqlite_master WHERE sql LIKE 'CREATE UNIQUE INDEX %'");
ds@1952:   if( rc!=SQLITE_OK ) goto end_of_vacuum;
ds@1952: 
ds@1952:   /* Loop through the tables in the main database. For each, do
ds@1952:   ** an "INSERT INTO vacuum_db.xxx SELECT * FROM xxx;" to copy
ds@1952:   ** the contents to the temporary database.
ds@1952:   */
ds@1952:   rc = execExecSql(db, 
ds@1952:       "SELECT 'INSERT INTO vacuum_db.' || quote(name) "
ds@1952:       "|| ' SELECT * FROM ' || quote(name) || ';'"
ds@1952:       "FROM sqlite_master "
ds@1952:       "WHERE type = 'table' AND name!='sqlite_sequence' "
ds@1952:       "  AND rootpage>0"
ds@1952: 
ds@1952:   );
ds@1952:   if( rc!=SQLITE_OK ) goto end_of_vacuum;
ds@1952: 
ds@1952:   /* Copy over the sequence table
ds@1952:   */
ds@1952:   rc = execExecSql(db, 
ds@1952:       "SELECT 'DELETE FROM vacuum_db.' || quote(name) || ';' "
ds@1952:       "FROM vacuum_db.sqlite_master WHERE name='sqlite_sequence' "
ds@1952:   );
ds@1952:   if( rc!=SQLITE_OK ) goto end_of_vacuum;
ds@1952:   rc = execExecSql(db, 
ds@1952:       "SELECT 'INSERT INTO vacuum_db.' || quote(name) "
ds@1952:       "|| ' SELECT * FROM ' || quote(name) || ';' "
ds@1952:       "FROM vacuum_db.sqlite_master WHERE name=='sqlite_sequence';"
ds@1952:   );
ds@1952:   if( rc!=SQLITE_OK ) goto end_of_vacuum;
ds@1952: 
ds@1952: 
ds@1952:   /* Copy the triggers, views, and virtual tables from the main database
ds@1952:   ** over to the temporary database.  None of these objects has any
ds@1952:   ** associated storage, so all we have to do is copy their entries
ds@1952:   ** from the SQLITE_MASTER table.
ds@1952:   */
ds@1952:   rc = execSql(db,
ds@1952:       "INSERT INTO vacuum_db.sqlite_master "
ds@1952:       "  SELECT type, name, tbl_name, rootpage, sql"
ds@1952:       "    FROM sqlite_master"
ds@1952:       "   WHERE type='view' OR type='trigger'"
ds@1952:       "      OR (type='table' AND rootpage=0)"
ds@1952:   );
ds@1952:   if( rc ) goto end_of_vacuum;
ds@1952: 
ds@1952:   /* At this point, unless the main db was completely empty, there is now a
ds@1952:   ** transaction open on the vacuum database, but not on the main database.
ds@1952:   ** Open a btree level transaction on the main database. This allows a
ds@1952:   ** call to sqlite3BtreeCopyFile(). The main database btree level
ds@1952:   ** transaction is then committed, so the SQL level never knows it was
ds@1952:   ** opened for writing. This way, the SQL transaction used to create the
ds@1952:   ** temporary database never needs to be committed.
ds@1952:   */
ds@1952:   if( rc==SQLITE_OK ){
ds@1952:     u32 meta;
ds@1952:     int i;
ds@1952: 
ds@1952:     /* This array determines which meta meta values are preserved in the
ds@1952:     ** vacuum.  Even entries are the meta value number and odd entries
ds@1952:     ** are an increment to apply to the meta value after the vacuum.
ds@1952:     ** The increment is used to increase the schema cookie so that other
ds@1952:     ** connections to the same database will know to reread the schema.
ds@1952:     */
ds@1952:     static const unsigned char aCopy[] = {
ds@1952:        1, 1,    /* Add one to the old schema cookie */
ds@1952:        3, 0,    /* Preserve the default page cache size */
ds@1952:        5, 0,    /* Preserve the default text encoding */
ds@1952:        6, 0,    /* Preserve the user version */
ds@1952:     };
ds@1952: 
ds@1952:     assert( 1==sqlite3BtreeIsInTrans(pTemp) );
ds@1952:     assert( 1==sqlite3BtreeIsInTrans(pMain) );
ds@1952: 
ds@1952:     /* Copy Btree meta values */
ds@1952:     for(i=0; i<sizeof(aCopy)/sizeof(aCopy[0]); i+=2){
ds@1952:       rc = sqlite3BtreeGetMeta(pMain, aCopy[i], &meta);
ds@1952:       if( rc!=SQLITE_OK ) goto end_of_vacuum;
ds@1952:       rc = sqlite3BtreeUpdateMeta(pTemp, aCopy[i], meta+aCopy[i+1]);
ds@1952:       if( rc!=SQLITE_OK ) goto end_of_vacuum;
ds@1952:     }
ds@1952: 
ds@1952:     rc = sqlite3BtreeCopyFile(pMain, pTemp);
ds@1952:     if( rc!=SQLITE_OK ) goto end_of_vacuum;
ds@1952:     rc = sqlite3BtreeCommit(pTemp);
ds@1952:     if( rc!=SQLITE_OK ) goto end_of_vacuum;
ds@1952:     rc = sqlite3BtreeCommit(pMain);
ds@1952:   }
ds@1952: 
ds@1952: end_of_vacuum:
ds@1952:   /* Restore the original value of db->flags */
ds@1952:   db->flags = saved_flags;
ds@1952: 
ds@1952:   /* Currently there is an SQL level transaction open on the vacuum
ds@1952:   ** database. No locks are held on any other files (since the main file
ds@1952:   ** was committed at the btree level). So it safe to end the transaction
ds@1952:   ** by manually setting the autoCommit flag to true and detaching the
ds@1952:   ** vacuum database. The vacuum_db journal file is deleted when the pager
ds@1952:   ** is closed by the DETACH.
ds@1952:   */
ds@1952:   db->autoCommit = 1;
ds@1952: 
ds@1952:   if( pDb ){
ds@1952:     sqlite3BtreeClose(pDb->pBt);
ds@1952:     pDb->pBt = 0;
ds@1952:     pDb->pSchema = 0;
ds@1952:   }
ds@1952: 
ds@1952:   sqlite3ResetInternalSchema(db, 0);
ds@1952: 
ds@1952:   return rc;
ds@1952: }
ds@1952: #endif  /* SQLITE_OMIT_VACUUM && SQLITE_OMIT_ATTACH */