sqlite3/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 ** $Id$
  18 */
  19 #include "sqliteInt.h"
  20 #include "os.h"
  21
  22 #ifndef SQLITE_OMIT_VACUUM
  23 /*
  24 ** Generate a random name of 20 character in length.
  25 */
  26 static void randomName(unsigned char *zBuf){
  27   static const unsigned char zChars[] =
  28     "abcdefghijklmnopqrstuvwxyz"
  29     "0123456789";
  30   int i;
  31   sqlite3Randomness(20, zBuf);
  32   for(i=0; i<20; i++){
  33     zBuf[i] = zChars[ zBuf[i]%(sizeof(zChars)-1) ];
  34   }
  35 }
  36
  37 /*
  38 ** Execute zSql on database db. Return an error code.
  39 */
  40 static int execSql(sqlite3 *db, const char *zSql){
  41   sqlite3_stmt *pStmt;
  42   if( SQLITE_OK!=sqlite3_prepare(db, zSql, -1, &pStmt, 0) ){
  43     return sqlite3_errcode(db);
  44   }
  45   while( SQLITE_ROW==sqlite3_step(pStmt) );
  46   return sqlite3_finalize(pStmt);
  47 }
  48
  49 /*
  50 ** Execute zSql on database db. The statement returns exactly
  51 ** one column. Execute this as SQL on the same database.
  52 */
  53 static int execExecSql(sqlite3 *db, const char *zSql){
  54   sqlite3_stmt *pStmt;
  55   int rc;
  56
  57   rc = sqlite3_prepare(db, zSql, -1, &pStmt, 0);
  58   if( rc!=SQLITE_OK ) return rc;
  59
  60   while( SQLITE_ROW==sqlite3_step(pStmt) ){
  61     rc = execSql(db, sqlite3_column_text(pStmt, 0));
  62     if( rc!=SQLITE_OK ){
  63       sqlite3_finalize(pStmt);
  64       return rc;
  65     }
  66   }
  67
  68   return sqlite3_finalize(pStmt);
  69 }
  70
  71 #endif
  72
  73 /*
  74 ** The non-standard VACUUM command is used to clean up the database,
  75 ** collapse free space, etc.  It is modelled after the VACUUM command
  76 ** in PostgreSQL.
  77 **
  78 ** In version 1.0.x of SQLite, the VACUUM command would call
  79 ** gdbm_reorganize() on all the database tables.  But beginning
  80 ** with 2.0.0, SQLite no longer uses GDBM so this command has
  81 ** become a no-op.
  82 */
  83 void sqlite3Vacuum(Parse *pParse, Token *pTableName){
  84   Vdbe *v = sqlite3GetVdbe(pParse);
  85   if( v ){
  86     sqlite3VdbeAddOp(v, OP_Vacuum, 0, 0);
  87   }
  88   return;
  89 }
  90
  91 /*
  92 ** This routine implements the OP_Vacuum opcode of the VDBE.
  93 */
  94 int sqlite3RunVacuum(STRPTR *pzErrMsg, sqlite3 *db){
  95   int rc = SQLITE_OK;     /* Return code from service routines */
  96 #ifndef SQLITE_OMIT_VACUUM
  97   const char *zFilename;  /* full pathname of the database file */
  98   int nFilename;          /* number of characters  in zFilename[] */
  99   char *zTemp = 0;        /* a temporary file in same directory as zFilename */
 100   Btree *pMain;           /* The database being vacuumed */
 101   Btree *pTemp;
 102   char *zSql = 0;
 103   int writeschema_flag;   /* Saved value of the write-schema flag */
 104
 105   /* Save the current value of the write-schema flag before setting it. */
 106   writeschema_flag = db->flags&SQLITE_WriteSchema;
 107   db->flags |= SQLITE_WriteSchema;
 108
 109   if( !db->autoCommit ){
 110     sqlite3SetString(pzErrMsg, "cannot VACUUM from within a transaction",
 111        (char*)0);
 112     rc = SQLITE_ERROR;
 113     goto end_of_vacuum;
 114   }
 115
 116   /* Get the full pathname of the database file and create a
 117   ** temporary filename in the same directory as the original file.
 118   */
 119   pMain = db->aDb[0].pBt;
 120   zFilename = sqlite3BtreeGetFilename(pMain);
 121   assert( zFilename );
 122   if( zFilename[0]=='\0' ){
 123     /* The in-memory database. Do nothing. Return directly to avoid causing
 124     ** an error trying to DETACH the vacuum_db (which never got attached)
 125     ** in the exit-handler.
 126     */
 127     return SQLITE_OK;
 128   }
 129   nFilename = strlen(zFilename);
 130   zTemp = sqliteMalloc( nFilename+100 );
 131   if( zTemp==0 ){
 132     rc = SQLITE_NOMEM;
 133     goto end_of_vacuum;
 134   }
 135   strcpy(zTemp, zFilename);
 136
 137   /* The randomName() procedure in the following loop uses an excellent
 138   ** source of randomness to generate a name from a space of 1.3e+31
 139   ** possibilities.  So unless the directory already contains on the order
 140   ** of 1.3e+31 files, the probability that the following loop will
 141   ** run more than once or twice is vanishingly small.  We are certain
 142   ** enough that this loop will always terminate (and terminate quickly)
 143   ** that we don't even bother to set a maximum loop count.
 144   */
 145   do {
 146     zTemp[nFilename] = '-';
 147     randomName((unsigned char*)&zTemp[nFilename+1]);
 148   } while( sqlite3OsFileExists(zTemp) );
 149
 150   /* Attach the temporary database as 'vacuum_db'. The synchronous pragma
 151   ** can be set to 'off' for this file, as it is not recovered if a crash
 152   ** occurs anyway. The integrity of the database is maintained by a
 153   ** (possibly synchronous) transaction opened on the main database before
 154   ** sqlite3BtreeCopyFile() is called.
 155   **
 156   ** An optimisation would be to use a non-journaled pager.
 157   */
 158   zSql = sqlite3MPrintf("ATTACH '%q' AS vacuum_db;", zTemp);
 159   if( !zSql ){
 160     rc = SQLITE_NOMEM;
 161     goto end_of_vacuum;
 162   }
 163   rc = execSql(db, zSql);
 164   sqliteFree(zSql);
 165   zSql = 0;
 166   if( rc!=SQLITE_OK ) goto end_of_vacuum;
 167   assert( strcmp(db->aDb[db->nDb-1].zName,"vacuum_db")==0 );
 168   pTemp = db->aDb[db->nDb-1].pBt;
 169   sqlite3BtreeSetPageSize(pTemp, sqlite3BtreeGetPageSize(pMain),
 170      sqlite3BtreeGetReserve(pMain));
 171   assert( sqlite3BtreeGetPageSize(pTemp)==sqlite3BtreeGetPageSize(pMain) );
 172   execSql(db, "PRAGMA vacuum_db.synchronous=OFF");
 173
 174 #ifndef SQLITE_OMIT_AUTOVACUUM
 175   sqlite3BtreeSetAutoVacuum(pTemp, sqlite3BtreeGetAutoVacuum(pMain));
 176 #endif
 177
 178   /* Begin a transaction */
 179   rc = execSql(db, "BEGIN;");
 180   if( rc!=SQLITE_OK ) goto end_of_vacuum;
 181
 182   /* Query the schema of the main database. Create a mirror schema
 183   ** in the temporary database.
 184   */
 185   rc = execExecSql(db,
 186       "SELECT 'CREATE TABLE vacuum_db.' || substr(sql,14,100000000) "
 187       "  FROM sqlite_master WHERE type='table' AND name!='sqlite_sequence'");
 188   if( rc!=SQLITE_OK ) goto end_of_vacuum;
 189   rc = execExecSql(db,
 190       "SELECT 'CREATE INDEX vacuum_db.' || substr(sql,14,100000000)"
 191       "  FROM sqlite_master WHERE sql LIKE 'CREATE INDEX %' ");
 192   if( rc!=SQLITE_OK ) goto end_of_vacuum;
 193   rc = execExecSql(db,
 194       "SELECT 'CREATE UNIQUE INDEX vacuum_db.' || substr(sql,21,100000000) "
 195       "  FROM sqlite_master WHERE sql LIKE 'CREATE UNIQUE INDEX %'");
 196   if( rc!=SQLITE_OK ) goto end_of_vacuum;
 197   rc = execExecSql(db,
 198       "SELECT 'CREATE VIEW vacuum_db.' || substr(sql,13,100000000) "
 199       "  FROM sqlite_master WHERE type='view'"
 200   );
 201   if( rc!=SQLITE_OK ) goto end_of_vacuum;
 202
 203   /* Loop through the tables in the main database. For each, do
 204   ** an "INSERT INTO vacuum_db.xxx SELECT * FROM xxx;" to copy
 205   ** the contents to the temporary database.
 206   */
 207   rc = execExecSql(db,
 208       "SELECT 'INSERT INTO vacuum_db.' || quote(name) "
 209       "|| ' SELECT * FROM ' || quote(name) || ';'"
 210       "FROM sqlite_master "
 211       "WHERE type = 'table' AND name!='sqlite_sequence';"
 212   );
 213   if( rc!=SQLITE_OK ) goto end_of_vacuum;
 214
 215   /* Copy over the sequence table
 216   */
 217   rc = execExecSql(db,
 218       "SELECT 'DELETE FROM vacuum_db.' || quote(name) || ';' "
 219       "FROM vacuum_db.sqlite_master WHERE name='sqlite_sequence' "
 220   );
 221   if( rc!=SQLITE_OK ) goto end_of_vacuum;
 222   rc = execExecSql(db,
 223       "SELECT 'INSERT INTO vacuum_db.' || quote(name) "
 224       "|| ' SELECT * FROM ' || quote(name) || ';' "
 225       "FROM vacuum_db.sqlite_master WHERE name=='sqlite_sequence';"
 226   );
 227   if( rc!=SQLITE_OK ) goto end_of_vacuum;
 228
 229
 230   /* Copy the triggers from the main database to the temporary database.
 231   ** This was deferred before in case the triggers interfered with copying
 232   ** the data. It's possible the indices should be deferred until this
 233   ** point also.
 234   */
 235   rc = execExecSql(db,
 236       "SELECT 'CREATE TRIGGER  vacuum_db.' || substr(sql, 16, 1000000) "
 237       "FROM sqlite_master WHERE type='trigger'"
 238   );
 239   if( rc!=SQLITE_OK ) goto end_of_vacuum;
 240
 241
 242   /* At this point, unless the main db was completely empty, there is now a
 243   ** transaction open on the vacuum database, but not on the main database.
 244   ** Open a btree level transaction on the main database. This allows a
 245   ** call to sqlite3BtreeCopyFile(). The main database btree level
 246   ** transaction is then committed, so the SQL level never knows it was
 247   ** opened for writing. This way, the SQL transaction used to create the
 248   ** temporary database never needs to be committed.
 249   */
 250   if( sqlite3BtreeIsInTrans(pTemp) ){
 251     u32 meta;
 252     int i;
 253
 254     /* This array determines which meta meta values are preserved in the
 255     ** vacuum.  Even entries are the meta value number and odd entries
 256     ** are an increment to apply to the meta value after the vacuum.
 257     ** The increment is used to increase the schema cookie so that other
 258     ** connections to the same database will know to reread the schema.
 259     */
 260     static const unsigned char aCopy[] = {
 261        1, 1,    /* Add one to the old schema cookie */
 262        3, 0,    /* Preserve the default page cache size */
 263        5, 0,    /* Preserve the default text encoding */
 264        6, 0,    /* Preserve the user version */
 265     };
 266
 267     assert( 0==sqlite3BtreeIsInTrans(pMain) );
 268     rc = sqlite3BtreeBeginTrans(pMain, 1);
 269     if( rc!=SQLITE_OK ) goto end_of_vacuum;
 270
 271     /* Copy Btree meta values */
 272     for(i=0; i<sizeof(aCopy)/sizeof(aCopy[0]); i+=2){
 273       rc = sqlite3BtreeGetMeta(pMain, aCopy[i], &meta);
 274       if( rc!=SQLITE_OK ) goto end_of_vacuum;
 275       rc = sqlite3BtreeUpdateMeta(pTemp, aCopy[i], meta+aCopy[i+1]);
 276     }
 277
 278     rc = sqlite3BtreeCopyFile(pMain, pTemp);
 279     if( rc!=SQLITE_OK ) goto end_of_vacuum;
 280     rc = sqlite3BtreeCommit(pMain);
 281   }
 282
 283 end_of_vacuum:
 284   /* Restore the original value of the write-schema flag. */
 285   db->flags &= ~SQLITE_WriteSchema;
 286   db->flags |= writeschema_flag;
 287
 288   /* Currently there is an SQL level transaction open on the vacuum
 289   ** database. No locks are held on any other files (since the main file
 290   ** was committed at the btree level). So it safe to end the transaction
 291   ** by manually setting the autoCommit flag to true and detaching the
 292   ** vacuum database. The vacuum_db journal file is deleted when the pager
 293   ** is closed by the DETACH.
 294   */
 295   db->autoCommit = 1;
 296   if( rc==SQLITE_OK ){
 297     rc = execSql(db, "DETACH vacuum_db;");
 298   }else{
 299     execSql(db, "DETACH vacuum_db;");
 300   }
 301   if( zTemp ){
 302     sqlite3OsDelete(zTemp);
 303     sqliteFree(zTemp);
 304   }
 305   if( zSql ) sqliteFree( zSql );
 306   sqlite3ResetInternalSchema(db, 0);
 307 #endif
 308
 309   return rc;
 310 }