tool/fast_vacuum.c

   1 /*
   2 ** 2013-10-01
   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 **
  13 ** This program implements a high-speed version of the VACUUM command.
  14 ** It repacks an SQLite database to remove as much unused space as
  15 ** possible and to relocate content sequentially in the file.
  16 **
  17 ** This program runs faster and uses less temporary disk space than the
  18 ** built-in VACUUM command.  On the other hand, this program has a number
  19 ** of important restrictions relative to the built-in VACUUM command.
  20 **
  21 **  (1)  The caller must ensure that no other processes are accessing the
  22 **       database file while the vacuum is taking place.  The usual SQLite
  23 **       file locking is insufficient for this.  The caller must use
  24 **       external means to make sure only this one routine is reading and
  25 **       writing the database.
  26 **
  27 **  (2)  Database reconfiguration such as page size or auto_vacuum changes
  28 **       are not supported by this utility.
  29 **
  30 **  (3)  The database file might be renamed if a power loss or crash
  31 **       occurs at just the wrong moment.  Recovery must be prepared to
  32 **       to deal with the possibly changed filename.
  33 **
  34 ** This program is intended as a *Demonstration Only*.  The intent of this
  35 ** program is to provide example code that application developers can use
  36 ** when creating similar functionality in their applications.
  37 **
  38 ** To compile this program:
  39 **
  40 **     cc fast_vacuum.c sqlite3.c
  41 **
  42 ** Add whatever linker options are required.  (Example: "-ldl -lpthread").
  43 ** Then to run the program:
  44 **
  45 **    ./a.out file-to-vacuum
  46 **
  47 */
  48 #include "sqlite3.h"
  49 #include <stdio.h>
  50 #include <stdlib.h>
  51
  52 /*
  53 ** Finalize a prepared statement.  If an error has occurred, print the
  54 ** error message and exit.
  55 */
  56 static void vacuumFinalize(sqlite3_stmt *pStmt){
  57   sqlite3 *db = sqlite3_db_handle(pStmt);
  58   int rc = sqlite3_finalize(pStmt);
  59   if( rc ){
  60     fprintf(stderr, "finalize error: %s\n", sqlite3_errmsg(db));
  61     exit(1);
  62   }
  63 }
  64
  65 /*
  66 ** Execute zSql on database db. The SQL text is printed to standard
  67 ** output.  If an error occurs, print an error message and exit the
  68 ** process.
  69 */
  70 static void execSql(sqlite3 *db, const char *zSql){
  71   sqlite3_stmt *pStmt;
  72   if( !zSql ){
  73     fprintf(stderr, "out of memory!\n");
  74     exit(1);
  75   }
  76   printf("%s;\n", zSql);
  77   if( SQLITE_OK!=sqlite3_prepare(db, zSql, -1, &pStmt, 0) ){
  78     fprintf(stderr, "Error: %s\n", sqlite3_errmsg(db));
  79     exit(1);
  80   }
  81   sqlite3_step(pStmt);
  82   vacuumFinalize(pStmt);
  83 }
  84
  85 /*
  86 ** Execute zSql on database db. The zSql statement returns exactly
  87 ** one column. Execute this return value as SQL on the same database.
  88 **
  89 ** The zSql statement is printed on standard output prior to being
  90 ** run.  If any errors occur, an error is printed and the process
  91 ** exits.
  92 */
  93 static void execExecSql(sqlite3 *db, const char *zSql){
  94   sqlite3_stmt *pStmt;
  95   int rc;
  96
  97   printf("%s;\n", zSql);
  98   rc = sqlite3_prepare(db, zSql, -1, &pStmt, 0);
  99   if( rc!=SQLITE_OK ){
 100     fprintf(stderr, "Error: %s\n", sqlite3_errmsg(db));
 101     exit(1);
 102   }
 103   while( SQLITE_ROW==sqlite3_step(pStmt) ){
 104     execSql(db, (char*)sqlite3_column_text(pStmt, 0));
 105   }
 106   vacuumFinalize(pStmt);
 107 }
 108
 109
 110 int main(int argc, char **argv){
 111   sqlite3 *db;                 /* Connection to the database file */
 112   int rc;                      /* Return code from SQLite interface calls */
 113   sqlite3_uint64 r;            /* A random number */
 114   const char *zDbToVacuum;     /* Database to be vacuumed */
 115   char *zBackupDb;             /* Backup copy of the original database */
 116   char *zTempDb;               /* Temporary database */
 117   char *zSql;                  /* An SQL statement */
 118
 119   if( argc!=2 ){
 120     fprintf(stderr, "Usage: %s DATABASE\n", argv[0]);
 121     return 1;
 122   }
 123
 124   /* Identify the database file to be vacuumed and open it.
 125   */
 126   zDbToVacuum = argv[1];
 127   printf("-- open database file \"%s\"\n", zDbToVacuum);
 128   rc = sqlite3_open(zDbToVacuum, &db);
 129   if( rc ){
 130     fprintf(stderr, "%s: %s\n", zDbToVacuum, sqlite3_errstr(rc));
 131     return 1;
 132   }
 133
 134   /* Create names for two other files.  zTempDb will be a new database
 135   ** into which we construct a vacuumed copy of zDbToVacuum.  zBackupDb
 136   ** will be a new name for zDbToVacuum after it is vacuumed.
 137   */
 138   sqlite3_randomness(sizeof(r), &r);
 139   zTempDb = sqlite3_mprintf("%s-vacuum-%016llx", zDbToVacuum, r);
 140   zBackupDb = sqlite3_mprintf("%s-backup-%016llx", zDbToVacuum, r);
 141
 142   /* Attach the zTempDb database to the database connection.
 143   */
 144   zSql = sqlite3_mprintf("ATTACH '%q' AS vacuum_db;", zTempDb);
 145   execSql(db, zSql);
 146   sqlite3_free(zSql);
 147
 148   /* TODO:
 149   ** Set the page_size and auto_vacuum mode for zTempDb here, if desired.
 150   */
 151
 152   /* The vacuum will occur inside of a transaction.  Set writable_schema
 153   ** to ON so that we can directly update the sqlite_schema table in the
 154   ** zTempDb database.
 155   */
 156   execSql(db, "PRAGMA writable_schema=ON");
 157   execSql(db, "BEGIN");
 158
 159
 160   /* Query the schema of the main database. Create a mirror schema
 161   ** in the temporary database.
 162   */
 163   execExecSql(db,
 164       "SELECT 'CREATE TABLE vacuum_db.' || substr(sql,14) "
 165       "  FROM sqlite_schema WHERE type='table' AND name!='sqlite_sequence'"
 166       "   AND rootpage>0"
 167   );
 168   execExecSql(db,
 169       "SELECT 'CREATE INDEX vacuum_db.' || substr(sql,14)"
 170       "  FROM sqlite_schema WHERE sql LIKE 'CREATE INDEX %'"
 171   );
 172   execExecSql(db,
 173       "SELECT 'CREATE UNIQUE INDEX vacuum_db.' || substr(sql,21) "
 174       "  FROM sqlite_schema WHERE sql LIKE 'CREATE UNIQUE INDEX %'"
 175   );
 176
 177   /* Loop through the tables in the main database. For each, do
 178   ** an "INSERT INTO vacuum_db.xxx SELECT * FROM main.xxx;" to copy
 179   ** the contents to the temporary database.
 180   */
 181   execExecSql(db,
 182       "SELECT 'INSERT INTO vacuum_db.' || quote(name) "
 183       "|| ' SELECT * FROM main.' || quote(name) "
 184       "FROM main.sqlite_schema "
 185       "WHERE type = 'table' AND name!='sqlite_sequence' "
 186       "  AND rootpage>0"
 187   );
 188
 189   /* Copy over the sequence table
 190   */
 191   execExecSql(db,
 192       "SELECT 'DELETE FROM vacuum_db.' || quote(name) "
 193       "FROM vacuum_db.sqlite_schema WHERE name='sqlite_sequence'"
 194   );
 195   execExecSql(db,
 196       "SELECT 'INSERT INTO vacuum_db.' || quote(name) "
 197       "|| ' SELECT * FROM main.' || quote(name) "
 198       "FROM vacuum_db.sqlite_schema WHERE name=='sqlite_sequence'"
 199   );
 200
 201   /* Copy the triggers, views, and virtual tables from the main database
 202   ** over to the temporary database.  None of these objects has any
 203   ** associated storage, so all we have to do is copy their entries
 204   ** from the SQLITE_MASTER table.
 205   */
 206   execSql(db,
 207       "INSERT INTO vacuum_db.sqlite_schema "
 208       "  SELECT type, name, tbl_name, rootpage, sql"
 209       "    FROM main.sqlite_schema"
 210       "   WHERE type='view' OR type='trigger'"
 211       "      OR (type='table' AND rootpage=0)"
 212   );
 213
 214   /* Commit the transaction and close the database
 215   */
 216   execSql(db, "COMMIT");
 217   printf("-- close database\n");
 218   sqlite3_close(db);
 219
 220
 221   /* At this point, zDbToVacuum is unchanged.  zTempDb contains a
 222   ** vacuumed copy of zDbToVacuum.  Rearrange filenames so that
 223   ** zTempDb becomes thenew zDbToVacuum.
 224   */
 225   printf("-- rename \"%s\" to \"%s\"\n", zDbToVacuum, zBackupDb);
 226   rename(zDbToVacuum, zBackupDb);
 227   printf("-- rename \"%s\" to \"%s\"\n", zTempDb, zDbToVacuum);
 228   rename(zTempDb, zDbToVacuum);
 229
 230   /* Release allocated memory */
 231   sqlite3_free(zTempDb);
 232   sqlite3_free(zBackupDb);
 233   return 0;
 234 }