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_master 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_master 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_master WHERE sql LIKE 'CREATE INDEX %'" 171 ); 172 execExecSql(db, 173 "SELECT 'CREATE UNIQUE INDEX vacuum_db.' || substr(sql,21) " 174 " FROM sqlite_master 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_master " 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_master 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_master 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_master " 208 " SELECT type, name, tbl_name, rootpage, sql" 209 " FROM main.sqlite_master" 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 } 235