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 ** $Id: vacuum.cpp 1282 2008-11-13 09:31:33Z LarsPson $

    18 */

    19 #include "sqliteInt.h"

    20 #include "vdbeInt.h"

    21 

    22 #if !defined(SQLITE_OMIT_VACUUM) && !defined(SQLITE_OMIT_ATTACH)

    23 /*

    24 ** Execute zSql on database db. Return an error code.

    25 */

    26 static int execSql(sqlite3 *db, const char *zSql){

    27   sqlite3_stmt *pStmt;

    28   if( !zSql ){

    29     return SQLITE_NOMEM;

    30   }

    31   if( SQLITE_OK!=sqlite3_prepare(db, zSql, -1, &pStmt, 0) ){

    32     return sqlite3_errcode(db);

    33   }

    34   while( SQLITE_ROW==sqlite3_step(pStmt) ){}

    35   return sqlite3_finalize(pStmt);

    36 }

    37 

    38 /*

    39 ** Execute zSql on database db. The statement returns exactly

    40 ** one column. Execute this as SQL on the same database.

    41 */

    42 static int execExecSql(sqlite3 *db, const char *zSql){

    43   sqlite3_stmt *pStmt;

    44   int rc;

    45 

    46   rc = sqlite3_prepare(db, zSql, -1, &pStmt, 0);

    47   if( rc!=SQLITE_OK ) return rc;

    48 

    49   while( SQLITE_ROW==sqlite3_step(pStmt) ){

    50     rc = execSql(db, (char*)sqlite3_column_text(pStmt, 0));

    51     if( rc!=SQLITE_OK ){

    52       sqlite3_finalize(pStmt);

    53       return rc;

    54     }

    55   }

    56 

    57   return sqlite3_finalize(pStmt);

    58 }

    59 

    60 /*

    61 ** The non-standard VACUUM command is used to clean up the database,

    62 ** collapse free space, etc.  It is modelled after the VACUUM command

    63 ** in PostgreSQL.

    64 **

    65 ** In version 1.0.x of SQLite, the VACUUM command would call

    66 ** gdbm_reorganize() on all the database tables.  But beginning

    67 ** with 2.0.0, SQLite no longer uses GDBM so this command has

    68 ** become a no-op.

    69 */

    70 void sqlite3Vacuum(Parse *pParse){

    71   Vdbe *v = sqlite3GetVdbe(pParse);

    72   if( v ){

    73     sqlite3VdbeAddOp(v, OP_Vacuum, 0, 0);

    74   }

    75   return;

    76 }

    77 

    78 /*

    79 ** This routine implements the OP_Vacuum opcode of the VDBE.

    80 */

    81 int sqlite3RunVacuum(char **pzErrMsg, sqlite3 *db){

    82   int rc = SQLITE_OK;     /* Return code from service routines */

    83   Btree *pMain;           /* The database being vacuumed */

    84   Btree *pTemp;           /* The temporary database we vacuum into */

    85   char *zSql = 0;         /* SQL statements */

    86   int saved_flags;        /* Saved value of the db->flags */

    87   Db *pDb = 0;            /* Database to detach at end of vacuum */

    88 

    89   /* Save the current value of the write-schema flag before setting it. */

    90   saved_flags = db->flags;

    91   db->flags |= SQLITE_WriteSchema | SQLITE_IgnoreChecks;

    92 

    93   if( !db->autoCommit ){

    94     sqlite3SetString(pzErrMsg, "cannot VACUUM from within a transaction", 

    95        (char*)0);

    96     rc = SQLITE_ERROR;

    97     goto end_of_vacuum;

    98   }

    99   pMain = db->aDb[0].pBt;

   100 

   101   /* Attach the temporary database as 'vacuum_db'. The synchronous pragma

   102   ** can be set to 'off' for this file, as it is not recovered if a crash

   103   ** occurs anyway. The integrity of the database is maintained by a

   104   ** (possibly synchronous) transaction opened on the main database before

   105   ** sqlite3BtreeCopyFile() is called.

   106   **

   107   ** An optimisation would be to use a non-journaled pager.

   108   */

   109   zSql = "ATTACH '' AS vacuum_db;";

   110   rc = execSql(db, zSql);

   111   if( rc!=SQLITE_OK ) goto end_of_vacuum;

   112   pDb = &db->aDb[db->nDb-1];

   113   assert( strcmp(db->aDb[db->nDb-1].zName,"vacuum_db")==0 );

   114   pTemp = db->aDb[db->nDb-1].pBt;

   115   sqlite3BtreeSetPageSize(pTemp, sqlite3BtreeGetPageSize(pMain),

   116      sqlite3BtreeGetReserve(pMain));

   117   if( db->mallocFailed ){

   118     rc = SQLITE_NOMEM;

   119     goto end_of_vacuum;

   120   }

   121   assert( sqlite3BtreeGetPageSize(pTemp)==sqlite3BtreeGetPageSize(pMain) );

   122   rc = execSql(db, "PRAGMA vacuum_db.synchronous=OFF");

   123   if( rc!=SQLITE_OK ){

   124     goto end_of_vacuum;

   125   }

   126 

   127 #ifndef SQLITE_OMIT_AUTOVACUUM

   128   sqlite3BtreeSetAutoVacuum(pTemp, db->nextAutovac>=0 ? db->nextAutovac :

   129                                            sqlite3BtreeGetAutoVacuum(pMain));

   130 #endif

   131 

   132   /* Begin a transaction */

   133   rc = execSql(db, "BEGIN EXCLUSIVE;");

   134   if( rc!=SQLITE_OK ) goto end_of_vacuum;

   135 

   136   /* Query the schema of the main database. Create a mirror schema

   137   ** in the temporary database.

   138   */

   139   rc = execExecSql(db, 

   140       "SELECT 'CREATE TABLE vacuum_db.' || substr(sql,14) "

   141       "  FROM sqlite_master WHERE type='table' AND name!='sqlite_sequence'"

   142       "   AND rootpage>0"

   143   );

   144   if( rc!=SQLITE_OK ) goto end_of_vacuum;

   145   rc = execExecSql(db, 

   146       "SELECT 'CREATE INDEX vacuum_db.' || substr(sql,14)"

   147       "  FROM sqlite_master WHERE sql LIKE 'CREATE INDEX %' ");

   148   if( rc!=SQLITE_OK ) goto end_of_vacuum;

   149   rc = execExecSql(db, 

   150       "SELECT 'CREATE UNIQUE INDEX vacuum_db.' || substr(sql,21) "

   151       "  FROM sqlite_master WHERE sql LIKE 'CREATE UNIQUE INDEX %'");

   152   if( rc!=SQLITE_OK ) goto end_of_vacuum;

   153 

   154   /* Loop through the tables in the main database. For each, do

   155   ** an "INSERT INTO vacuum_db.xxx SELECT * FROM xxx;" to copy

   156   ** the contents to the temporary database.

   157   */

   158   rc = execExecSql(db, 

   159       "SELECT 'INSERT INTO vacuum_db.' || quote(name) "

   160       "|| ' SELECT * FROM ' || quote(name) || ';'"

   161       "FROM sqlite_master "

   162       "WHERE type = 'table' AND name!='sqlite_sequence' "

   163       "  AND rootpage>0"

   164 

   165   );

   166   if( rc!=SQLITE_OK ) goto end_of_vacuum;

   167 

   168   /* Copy over the sequence table

   169   */

   170   rc = execExecSql(db, 

   171       "SELECT 'DELETE FROM vacuum_db.' || quote(name) || ';' "

   172       "FROM vacuum_db.sqlite_master WHERE name='sqlite_sequence' "

   173   );

   174   if( rc!=SQLITE_OK ) goto end_of_vacuum;

   175   rc = execExecSql(db, 

   176       "SELECT 'INSERT INTO vacuum_db.' || quote(name) "

   177       "|| ' SELECT * FROM ' || quote(name) || ';' "

   178       "FROM vacuum_db.sqlite_master WHERE name=='sqlite_sequence';"

   179   );

   180   if( rc!=SQLITE_OK ) goto end_of_vacuum;

   181 

   182 

   183   /* Copy the triggers, views, and virtual tables from the main database

   184   ** over to the temporary database.  None of these objects has any

   185   ** associated storage, so all we have to do is copy their entries

   186   ** from the SQLITE_MASTER table.

   187   */

   188   rc = execSql(db,

   189       "INSERT INTO vacuum_db.sqlite_master "

   190       "  SELECT type, name, tbl_name, rootpage, sql"

   191       "    FROM sqlite_master"

   192       "   WHERE type='view' OR type='trigger'"

   193       "      OR (type='table' AND rootpage=0)"

   194   );

   195   if( rc ) goto end_of_vacuum;

   196 

   197   /* At this point, unless the main db was completely empty, there is now a

   198   ** transaction open on the vacuum database, but not on the main database.

   199   ** Open a btree level transaction on the main database. This allows a

   200   ** call to sqlite3BtreeCopyFile(). The main database btree level

   201   ** transaction is then committed, so the SQL level never knows it was

   202   ** opened for writing. This way, the SQL transaction used to create the

   203   ** temporary database never needs to be committed.

   204   */

   205   if( rc==SQLITE_OK ){

   206     u32 meta;

   207     int i;

   208 

   209     /* This array determines which meta meta values are preserved in the

   210     ** vacuum.  Even entries are the meta value number and odd entries

   211     ** are an increment to apply to the meta value after the vacuum.

   212     ** The increment is used to increase the schema cookie so that other

   213     ** connections to the same database will know to reread the schema.

   214     */

   215     static const unsigned char aCopy[] = {

   216        1, 1,    /* Add one to the old schema cookie */

   217        3, 0,    /* Preserve the default page cache size */

   218        5, 0,    /* Preserve the default text encoding */

   219        6, 0,    /* Preserve the user version */

   220     };

   221 

   222     assert( 1==sqlite3BtreeIsInTrans(pTemp) );

   223     assert( 1==sqlite3BtreeIsInTrans(pMain) );

   224 

   225     /* Copy Btree meta values */

   226     for(i=0; i<sizeof(aCopy)/sizeof(aCopy[0]); i+=2){

   227       rc = sqlite3BtreeGetMeta(pMain, aCopy[i], &meta);

   228       if( rc!=SQLITE_OK ) goto end_of_vacuum;

   229       rc = sqlite3BtreeUpdateMeta(pTemp, aCopy[i], meta+aCopy[i+1]);

   230       if( rc!=SQLITE_OK ) goto end_of_vacuum;

   231     }

   232 

   233     rc = sqlite3BtreeCopyFile(pMain, pTemp);

   234     if( rc!=SQLITE_OK ) goto end_of_vacuum;

   235     rc = sqlite3BtreeCommit(pTemp);

   236     if( rc!=SQLITE_OK ) goto end_of_vacuum;

   237     rc = sqlite3BtreeCommit(pMain);

   238   }

   239 

   240 end_of_vacuum:

   241   /* Restore the original value of db->flags */

   242   db->flags = saved_flags;

   243 

   244   /* Currently there is an SQL level transaction open on the vacuum

   245   ** database. No locks are held on any other files (since the main file

   246   ** was committed at the btree level). So it safe to end the transaction

   247   ** by manually setting the autoCommit flag to true and detaching the

   248   ** vacuum database. The vacuum_db journal file is deleted when the pager

   249   ** is closed by the DETACH.

   250   */

   251   db->autoCommit = 1;

   252 

   253   if( pDb ){

   254     sqlite3BtreeClose(pDb->pBt);

   255     pDb->pBt = 0;

   256     pDb->pSchema = 0;

   257   }

   258 

   259   sqlite3ResetInternalSchema(db, 0);

   260 

   261   return rc;

   262 }

   263 #endif  /* SQLITE_OMIT_VACUUM && SQLITE_OMIT_ATTACH */