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.c,v 1.59 2006/02/24 02:53:50 drh Exp $

    18 */

    19 #include "sqliteInt.h"

    20 #include "vdbeInt.h"

    21 #include "os.h"

    22 

    23 #ifndef SQLITE_OMIT_VACUUM

    24 /*

    25 ** Generate a random name of 20 character in length.

    26 */

    27 static void randomName(unsigned char *zBuf){

    28   static const unsigned char zChars[] =

    29     "abcdefghijklmnopqrstuvwxyz"

    30     "0123456789";

    31   int i;

    32   sqlite3Randomness(20, zBuf);

    33   for(i=0; i<20; i++){

    34     zBuf[i] = zChars[ zBuf[i]%(sizeof(zChars)-1) ];

    35   }

    36 }

    37 

    38 /*

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

    40 */

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

    42   sqlite3_stmt *pStmt;

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

    44     return sqlite3_errcode(db);

    45   }

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

    47   return sqlite3_finalize(pStmt);

    48 }

    49 

    50 /*

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

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

    53 */

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

    55   sqlite3_stmt *pStmt;

    56   int rc;

    57 

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

    59   if( rc!=SQLITE_OK ) return rc;

    60 

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

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

    63     if( rc!=SQLITE_OK ){

    64       sqlite3_finalize(pStmt);

    65       return rc;

    66     }

    67   }

    68 

    69   return sqlite3_finalize(pStmt);

    70 }

    71 

    72 #endif

    73 

    74 /*

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

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

    77 ** in PostgreSQL.

    78 **

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

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

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

    82 ** become a no-op.

    83 */

    84 void sqlite3Vacuum(Parse *pParse){

    85   Vdbe *v = sqlite3GetVdbe(pParse);

    86   if( v ){

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

    88   }

    89   return;

    90 }

    91 

    92 /*

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

    94 */

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

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

    97 #ifndef SQLITE_OMIT_VACUUM

    98   const char *zFilename;  /* full pathname of the database file */

    99   int nFilename;          /* number of characters  in zFilename[] */

   100   char *zTemp = 0;        /* a temporary file in same directory as zFilename */

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

   102   Btree *pTemp;

   103   char *zSql = 0;

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

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

   106 

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

   108   saved_flags = db->flags;

   109   db->flags |= SQLITE_WriteSchema | SQLITE_IgnoreChecks;

   110 

   111   if( !db->autoCommit ){

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

   113        (char*)0);

   114     rc = SQLITE_ERROR;

   115     goto end_of_vacuum;

   116   }

   117 

   118   /* Get the full pathname of the database file and create a

   119   ** temporary filename in the same directory as the original file.

   120   */

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

   122   zFilename = sqlite3BtreeGetFilename(pMain);

   123   assert( zFilename );

   124   if( zFilename[0]=='\0' ){

   125     /* The in-memory database. Do nothing. Return directly to avoid causing

   126     ** an error trying to DETACH the vacuum_db (which never got attached)

   127     ** in the exit-handler.

   128     */

   129     return SQLITE_OK;

   130   }

   131   nFilename = strlen(zFilename);

   132   zTemp = sqliteMalloc( nFilename+100 );

   133   if( zTemp==0 ){

   134     rc = SQLITE_NOMEM;

   135     goto end_of_vacuum;

   136   }

   137   strcpy(zTemp, zFilename);

   138 

   139   /* The randomName() procedure in the following loop uses an excellent

   140   ** source of randomness to generate a name from a space of 1.3e+31 

   141   ** possibilities.  So unless the directory already contains on the order

   142   ** of 1.3e+31 files, the probability that the following loop will

   143   ** run more than once or twice is vanishingly small.  We are certain

   144   ** enough that this loop will always terminate (and terminate quickly)

   145   ** that we don't even bother to set a maximum loop count.

   146   */

   147   do {

   148     zTemp[nFilename] = '-';

   149     randomName((unsigned char*)&zTemp[nFilename+1]);

   150   } while( sqlite3OsFileExists(zTemp) );

   151 

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

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

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

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

   156   ** sqlite3BtreeCopyFile() is called.

   157   **

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

   159   */

   160   zSql = sqlite3MPrintf("ATTACH '%q' AS vacuum_db;", zTemp);

   161   if( !zSql ){

   162     rc = SQLITE_NOMEM;

   163     goto end_of_vacuum;

   164   }

   165   rc = execSql(db, zSql);

   166   sqliteFree(zSql);

   167   zSql = 0;

   168   if( rc!=SQLITE_OK ) goto end_of_vacuum;

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

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

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

   172   sqlite3BtreeSetPageSize(pTemp, sqlite3BtreeGetPageSize(pMain),

   173      sqlite3BtreeGetReserve(pMain));

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

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

   176   if( rc!=SQLITE_OK ){

   177     goto end_of_vacuum;

   178   }

   179 

   180 #ifndef SQLITE_OMIT_AUTOVACUUM

   181   sqlite3BtreeSetAutoVacuum(pTemp, sqlite3BtreeGetAutoVacuum(pMain));

   182 #endif

   183 

   184   /* Begin a transaction */

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

   186   if( rc!=SQLITE_OK ) goto end_of_vacuum;

   187 

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

   189   ** in the temporary database.

   190   */

   191   rc = execExecSql(db, 

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

   193       "  FROM sqlite_master WHERE type='table' AND name!='sqlite_sequence'");

   194   if( rc!=SQLITE_OK ) goto end_of_vacuum;

   195   rc = execExecSql(db, 

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

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

   198   if( rc!=SQLITE_OK ) goto end_of_vacuum;

   199   rc = execExecSql(db, 

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

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

   202   if( rc!=SQLITE_OK ) goto end_of_vacuum;

   203   rc = execExecSql(db, 

   204       "SELECT 'CREATE VIEW vacuum_db.' || substr(sql,13,100000000) "

   205       "  FROM sqlite_master WHERE type='view'"

   206   );

   207   if( rc!=SQLITE_OK ) goto end_of_vacuum;

   208 

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

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

   211   ** the contents to the temporary database.

   212   */

   213   rc = execExecSql(db, 

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

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

   216       "FROM sqlite_master "

   217       "WHERE type = 'table' AND name!='sqlite_sequence';"

   218   );

   219   if( rc!=SQLITE_OK ) goto end_of_vacuum;

   220 

   221   /* Copy over the sequence table

   222   */

   223   rc = execExecSql(db, 

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

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

   226   );

   227   if( rc!=SQLITE_OK ) goto end_of_vacuum;

   228   rc = execExecSql(db, 

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

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

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

   232   );

   233   if( rc!=SQLITE_OK ) goto end_of_vacuum;

   234 

   235 

   236   /* Copy the triggers from the main database to the temporary database.

   237   ** This was deferred before in case the triggers interfered with copying

   238   ** the data. It's possible the indices should be deferred until this

   239   ** point also.

   240   */

   241   rc = execExecSql(db, 

   242       "SELECT 'CREATE TRIGGER  vacuum_db.' || substr(sql, 16, 1000000) "

   243       "FROM sqlite_master WHERE type='trigger'"

   244   );

   245   if( rc!=SQLITE_OK ) goto end_of_vacuum;

   246 

   247 

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

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

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

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

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

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

   254   ** temporary database never needs to be committed.

   255   */

   256   if( rc==SQLITE_OK ){

   257     u32 meta;

   258     int i;

   259 

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

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

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

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

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

   265     */

   266     static const unsigned char aCopy[] = {

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

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

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

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

   271     };

   272 

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

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

   275 

   276     /* Copy Btree meta values */

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

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

   279       if( rc!=SQLITE_OK ) goto end_of_vacuum;

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

   281       if( rc!=SQLITE_OK ) goto end_of_vacuum;

   282     }

   283 

   284     rc = sqlite3BtreeCopyFile(pMain, pTemp);

   285     if( rc!=SQLITE_OK ) goto end_of_vacuum;

   286     rc = sqlite3BtreeCommit(pTemp);

   287     if( rc!=SQLITE_OK ) goto end_of_vacuum;

   288     rc = sqlite3BtreeCommit(pMain);

   289   }

   290 

   291 end_of_vacuum:

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

   293   db->flags = saved_flags;

   294 

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

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

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

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

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

   300   ** is closed by the DETACH.

   301   */

   302   db->autoCommit = 1;

   303 

   304   if( pDb ){

   305     sqlite3MallocDisallow();

   306     sqlite3BtreeClose(pDb->pBt);

   307     sqlite3MallocAllow();

   308     pDb->pBt = 0;

   309     pDb->pSchema = 0;

   310   }

   311 

   312   /* If one of the execSql() calls above returned SQLITE_NOMEM, then the

   313   ** mallocFailed flag will be clear (because execSql() calls sqlite3_exec()).

   314   ** Fix this so the flag and return code match.

   315   */

   316   if( rc==SQLITE_NOMEM ){

   317     sqlite3MallocFailed();

   318   }

   319 

   320   if( zTemp ){

   321     sqlite3OsDelete(zTemp);

   322     sqliteFree(zTemp);

   323   }

   324   sqliteFree( zSql );

   325   sqlite3ResetInternalSchema(db, 0);

   326 #endif

   327 

   328   return rc;

   329 }