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 */
vacuumFinalize(sqlite3_stmt * pStmt)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 */
execSql(sqlite3 * db,const char * zSql)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 */
execExecSql(sqlite3 * db,const char * zSql)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
main(int argc,char ** argv)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 }
235