|
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.83 2008/08/26 21:07:27 drh Exp $ |
|
18 */ |
|
19 #include "sqliteInt.h" |
|
20 #include "vdbeInt.h" |
|
21 |
|
22 #if !defined(SQLITE_OMIT_VACUUM) && !defined(SQLITE_OMIT_ATTACH) |
|
23 /* |
|
24 ** Execute zSql on database db. Return an error code. |
|
25 */ |
|
26 static int execSql(sqlite3 *db, const char *zSql){ |
|
27 sqlite3_stmt *pStmt; |
|
28 if( !zSql ){ |
|
29 return SQLITE_NOMEM; |
|
30 } |
|
31 if( SQLITE_OK!=sqlite3_prepare(db, zSql, -1, &pStmt, 0) ){ |
|
32 return sqlite3_errcode(db); |
|
33 } |
|
34 while( SQLITE_ROW==sqlite3_step(pStmt) ){} |
|
35 return sqlite3_finalize(pStmt); |
|
36 } |
|
37 |
|
38 /* |
|
39 ** Execute zSql on database db. The statement returns exactly |
|
40 ** one column. Execute this as SQL on the same database. |
|
41 */ |
|
42 static int execExecSql(sqlite3 *db, const char *zSql){ |
|
43 sqlite3_stmt *pStmt; |
|
44 int rc; |
|
45 |
|
46 rc = sqlite3_prepare(db, zSql, -1, &pStmt, 0); |
|
47 if( rc!=SQLITE_OK ) return rc; |
|
48 |
|
49 while( SQLITE_ROW==sqlite3_step(pStmt) ){ |
|
50 rc = execSql(db, (char*)sqlite3_column_text(pStmt, 0)); |
|
51 if( rc!=SQLITE_OK ){ |
|
52 sqlite3_finalize(pStmt); |
|
53 return rc; |
|
54 } |
|
55 } |
|
56 |
|
57 return sqlite3_finalize(pStmt); |
|
58 } |
|
59 |
|
60 /* |
|
61 ** The non-standard VACUUM command is used to clean up the database, |
|
62 ** collapse free space, etc. It is modelled after the VACUUM command |
|
63 ** in PostgreSQL. |
|
64 ** |
|
65 ** In version 1.0.x of SQLite, the VACUUM command would call |
|
66 ** gdbm_reorganize() on all the database tables. But beginning |
|
67 ** with 2.0.0, SQLite no longer uses GDBM so this command has |
|
68 ** become a no-op. |
|
69 */ |
|
70 void sqlite3Vacuum(Parse *pParse){ |
|
71 Vdbe *v = sqlite3GetVdbe(pParse); |
|
72 if( v ){ |
|
73 sqlite3VdbeAddOp2(v, OP_Vacuum, 0, 0); |
|
74 } |
|
75 return; |
|
76 } |
|
77 |
|
78 /* |
|
79 ** This routine implements the OP_Vacuum opcode of the VDBE. |
|
80 */ |
|
81 int sqlite3RunVacuum(char **pzErrMsg, sqlite3 *db){ |
|
82 int rc = SQLITE_OK; /* Return code from service routines */ |
|
83 Btree *pMain; /* The database being vacuumed */ |
|
84 Pager *pMainPager; /* Pager for database being vacuumed */ |
|
85 Btree *pTemp; /* The temporary database we vacuum into */ |
|
86 char *zSql = 0; /* SQL statements */ |
|
87 int saved_flags; /* Saved value of the db->flags */ |
|
88 int saved_nChange; /* Saved value of db->nChange */ |
|
89 int saved_nTotalChange; /* Saved value of db->nTotalChange */ |
|
90 Db *pDb = 0; /* Database to detach at end of vacuum */ |
|
91 int isMemDb; /* True is vacuuming a :memory: database */ |
|
92 int nRes; |
|
93 |
|
94 /* Save the current value of the write-schema flag before setting it. */ |
|
95 saved_flags = db->flags; |
|
96 saved_nChange = db->nChange; |
|
97 saved_nTotalChange = db->nTotalChange; |
|
98 db->flags |= SQLITE_WriteSchema | SQLITE_IgnoreChecks; |
|
99 |
|
100 if( !db->autoCommit ){ |
|
101 sqlite3SetString(pzErrMsg, db, "cannot VACUUM from within a transaction"); |
|
102 rc = SQLITE_ERROR; |
|
103 goto end_of_vacuum; |
|
104 } |
|
105 pMain = db->aDb[0].pBt; |
|
106 pMainPager = sqlite3BtreePager(pMain); |
|
107 isMemDb = sqlite3PagerFile(pMainPager)->pMethods==0; |
|
108 |
|
109 /* Attach the temporary database as 'vacuum_db'. The synchronous pragma |
|
110 ** can be set to 'off' for this file, as it is not recovered if a crash |
|
111 ** occurs anyway. The integrity of the database is maintained by a |
|
112 ** (possibly synchronous) transaction opened on the main database before |
|
113 ** sqlite3BtreeCopyFile() is called. |
|
114 ** |
|
115 ** An optimisation would be to use a non-journaled pager. |
|
116 ** (Later:) I tried setting "PRAGMA vacuum_db.journal_mode=OFF" but |
|
117 ** that actually made the VACUUM run slower. Very little journalling |
|
118 ** actually occurs when doing a vacuum since the vacuum_db is initially |
|
119 ** empty. Only the journal header is written. Apparently it takes more |
|
120 ** time to parse and run the PRAGMA to turn journalling off than it does |
|
121 ** to write the journal header file. |
|
122 */ |
|
123 zSql = "ATTACH '' AS vacuum_db;"; |
|
124 rc = execSql(db, zSql); |
|
125 if( rc!=SQLITE_OK ) goto end_of_vacuum; |
|
126 pDb = &db->aDb[db->nDb-1]; |
|
127 assert( strcmp(db->aDb[db->nDb-1].zName,"vacuum_db")==0 ); |
|
128 pTemp = db->aDb[db->nDb-1].pBt; |
|
129 |
|
130 nRes = sqlite3BtreeGetReserve(pMain); |
|
131 |
|
132 /* A VACUUM cannot change the pagesize of an encrypted database. */ |
|
133 #ifdef SQLITE_HAS_CODEC |
|
134 if( db->nextPagesize ){ |
|
135 extern void sqlite3CodecGetKey(sqlite3*, int, void**, int*); |
|
136 int nKey; |
|
137 char *zKey; |
|
138 sqlite3CodecGetKey(db, 0, (void**)&zKey, &nKey); |
|
139 if( nKey ) db->nextPagesize = 0; |
|
140 } |
|
141 #endif |
|
142 |
|
143 if( sqlite3BtreeSetPageSize(pTemp, sqlite3BtreeGetPageSize(pMain), nRes) |
|
144 || (!isMemDb && sqlite3BtreeSetPageSize(pTemp, db->nextPagesize, nRes)) |
|
145 || db->mallocFailed |
|
146 ){ |
|
147 rc = SQLITE_NOMEM; |
|
148 goto end_of_vacuum; |
|
149 } |
|
150 rc = execSql(db, "PRAGMA vacuum_db.synchronous=OFF"); |
|
151 if( rc!=SQLITE_OK ){ |
|
152 goto end_of_vacuum; |
|
153 } |
|
154 |
|
155 #ifndef SQLITE_OMIT_AUTOVACUUM |
|
156 sqlite3BtreeSetAutoVacuum(pTemp, db->nextAutovac>=0 ? db->nextAutovac : |
|
157 sqlite3BtreeGetAutoVacuum(pMain)); |
|
158 #endif |
|
159 |
|
160 /* Begin a transaction */ |
|
161 rc = execSql(db, "BEGIN EXCLUSIVE;"); |
|
162 if( rc!=SQLITE_OK ) goto end_of_vacuum; |
|
163 |
|
164 /* Query the schema of the main database. Create a mirror schema |
|
165 ** in the temporary database. |
|
166 */ |
|
167 rc = execExecSql(db, |
|
168 "SELECT 'CREATE TABLE vacuum_db.' || substr(sql,14) " |
|
169 " FROM sqlite_master WHERE type='table' AND name!='sqlite_sequence'" |
|
170 " AND rootpage>0" |
|
171 ); |
|
172 if( rc!=SQLITE_OK ) goto end_of_vacuum; |
|
173 rc = execExecSql(db, |
|
174 "SELECT 'CREATE INDEX vacuum_db.' || substr(sql,14)" |
|
175 " FROM sqlite_master WHERE sql LIKE 'CREATE INDEX %' "); |
|
176 if( rc!=SQLITE_OK ) goto end_of_vacuum; |
|
177 rc = execExecSql(db, |
|
178 "SELECT 'CREATE UNIQUE INDEX vacuum_db.' || substr(sql,21) " |
|
179 " FROM sqlite_master WHERE sql LIKE 'CREATE UNIQUE INDEX %'"); |
|
180 if( rc!=SQLITE_OK ) goto end_of_vacuum; |
|
181 |
|
182 /* Loop through the tables in the main database. For each, do |
|
183 ** an "INSERT INTO vacuum_db.xxx SELECT * FROM xxx;" to copy |
|
184 ** the contents to the temporary database. |
|
185 */ |
|
186 rc = execExecSql(db, |
|
187 "SELECT 'INSERT INTO vacuum_db.' || quote(name) " |
|
188 "|| ' SELECT * FROM ' || quote(name) || ';'" |
|
189 "FROM sqlite_master " |
|
190 "WHERE type = 'table' AND name!='sqlite_sequence' " |
|
191 " AND rootpage>0" |
|
192 |
|
193 ); |
|
194 if( rc!=SQLITE_OK ) goto end_of_vacuum; |
|
195 |
|
196 /* Copy over the sequence table |
|
197 */ |
|
198 rc = execExecSql(db, |
|
199 "SELECT 'DELETE FROM vacuum_db.' || quote(name) || ';' " |
|
200 "FROM vacuum_db.sqlite_master WHERE name='sqlite_sequence' " |
|
201 ); |
|
202 if( rc!=SQLITE_OK ) goto end_of_vacuum; |
|
203 rc = execExecSql(db, |
|
204 "SELECT 'INSERT INTO vacuum_db.' || quote(name) " |
|
205 "|| ' SELECT * FROM ' || quote(name) || ';' " |
|
206 "FROM vacuum_db.sqlite_master WHERE name=='sqlite_sequence';" |
|
207 ); |
|
208 if( rc!=SQLITE_OK ) goto end_of_vacuum; |
|
209 |
|
210 |
|
211 /* Copy the triggers, views, and virtual tables from the main database |
|
212 ** over to the temporary database. None of these objects has any |
|
213 ** associated storage, so all we have to do is copy their entries |
|
214 ** from the SQLITE_MASTER table. |
|
215 */ |
|
216 rc = execSql(db, |
|
217 "INSERT INTO vacuum_db.sqlite_master " |
|
218 " SELECT type, name, tbl_name, rootpage, sql" |
|
219 " FROM sqlite_master" |
|
220 " WHERE type='view' OR type='trigger'" |
|
221 " OR (type='table' AND rootpage=0)" |
|
222 ); |
|
223 if( rc ) goto end_of_vacuum; |
|
224 |
|
225 /* At this point, unless the main db was completely empty, there is now a |
|
226 ** transaction open on the vacuum database, but not on the main database. |
|
227 ** Open a btree level transaction on the main database. This allows a |
|
228 ** call to sqlite3BtreeCopyFile(). The main database btree level |
|
229 ** transaction is then committed, so the SQL level never knows it was |
|
230 ** opened for writing. This way, the SQL transaction used to create the |
|
231 ** temporary database never needs to be committed. |
|
232 */ |
|
233 if( rc==SQLITE_OK ){ |
|
234 u32 meta; |
|
235 int i; |
|
236 |
|
237 /* This array determines which meta meta values are preserved in the |
|
238 ** vacuum. Even entries are the meta value number and odd entries |
|
239 ** are an increment to apply to the meta value after the vacuum. |
|
240 ** The increment is used to increase the schema cookie so that other |
|
241 ** connections to the same database will know to reread the schema. |
|
242 */ |
|
243 static const unsigned char aCopy[] = { |
|
244 1, 1, /* Add one to the old schema cookie */ |
|
245 3, 0, /* Preserve the default page cache size */ |
|
246 5, 0, /* Preserve the default text encoding */ |
|
247 6, 0, /* Preserve the user version */ |
|
248 }; |
|
249 |
|
250 assert( 1==sqlite3BtreeIsInTrans(pTemp) ); |
|
251 assert( 1==sqlite3BtreeIsInTrans(pMain) ); |
|
252 |
|
253 /* Copy Btree meta values */ |
|
254 for(i=0; i<sizeof(aCopy)/sizeof(aCopy[0]); i+=2){ |
|
255 rc = sqlite3BtreeGetMeta(pMain, aCopy[i], &meta); |
|
256 if( rc!=SQLITE_OK ) goto end_of_vacuum; |
|
257 rc = sqlite3BtreeUpdateMeta(pTemp, aCopy[i], meta+aCopy[i+1]); |
|
258 if( rc!=SQLITE_OK ) goto end_of_vacuum; |
|
259 } |
|
260 |
|
261 rc = sqlite3BtreeCopyFile(pMain, pTemp); |
|
262 if( rc!=SQLITE_OK ) goto end_of_vacuum; |
|
263 rc = sqlite3BtreeCommit(pTemp); |
|
264 if( rc!=SQLITE_OK ) goto end_of_vacuum; |
|
265 #ifndef SQLITE_OMIT_AUTOVACUUM |
|
266 sqlite3BtreeSetAutoVacuum(pMain, sqlite3BtreeGetAutoVacuum(pTemp)); |
|
267 #endif |
|
268 rc = sqlite3BtreeCommit(pMain); |
|
269 } |
|
270 |
|
271 if( rc==SQLITE_OK ){ |
|
272 rc = sqlite3BtreeSetPageSize(pMain, sqlite3BtreeGetPageSize(pTemp), nRes); |
|
273 } |
|
274 |
|
275 end_of_vacuum: |
|
276 /* Restore the original value of db->flags */ |
|
277 db->flags = saved_flags; |
|
278 db->nChange = saved_nChange; |
|
279 db->nTotalChange = saved_nTotalChange; |
|
280 |
|
281 /* Currently there is an SQL level transaction open on the vacuum |
|
282 ** database. No locks are held on any other files (since the main file |
|
283 ** was committed at the btree level). So it safe to end the transaction |
|
284 ** by manually setting the autoCommit flag to true and detaching the |
|
285 ** vacuum database. The vacuum_db journal file is deleted when the pager |
|
286 ** is closed by the DETACH. |
|
287 */ |
|
288 db->autoCommit = 1; |
|
289 |
|
290 if( pDb ){ |
|
291 sqlite3BtreeClose(pDb->pBt); |
|
292 pDb->pBt = 0; |
|
293 pDb->pSchema = 0; |
|
294 } |
|
295 |
|
296 sqlite3ResetInternalSchema(db, 0); |
|
297 |
|
298 return rc; |
|
299 } |
|
300 #endif /* SQLITE_OMIT_VACUUM && SQLITE_OMIT_ATTACH */ |