engine/sqlite/src/vacuum.cpp
changeset 97 3903521a36da
parent 96 87e863f6f840
child 98 5f9e7e14eb11
equal deleted inserted replaced
96:87e863f6f840 97:3903521a36da
     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 */