Annotation of embedaddon/sqlite3/src/vacuum.c, revision 1.1.1.1
1.1 misho 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: #include "sqliteInt.h"
18: #include "vdbeInt.h"
19:
20: #if !defined(SQLITE_OMIT_VACUUM) && !defined(SQLITE_OMIT_ATTACH)
21: /*
22: ** Finalize a prepared statement. If there was an error, store the
23: ** text of the error message in *pzErrMsg. Return the result code.
24: */
25: static int vacuumFinalize(sqlite3 *db, sqlite3_stmt *pStmt, char **pzErrMsg){
26: int rc;
27: rc = sqlite3VdbeFinalize((Vdbe*)pStmt);
28: if( rc ){
29: sqlite3SetString(pzErrMsg, db, sqlite3_errmsg(db));
30: }
31: return rc;
32: }
33:
34: /*
35: ** Execute zSql on database db. Return an error code.
36: */
37: static int execSql(sqlite3 *db, char **pzErrMsg, const char *zSql){
38: sqlite3_stmt *pStmt;
39: VVA_ONLY( int rc; )
40: if( !zSql ){
41: return SQLITE_NOMEM;
42: }
43: if( SQLITE_OK!=sqlite3_prepare(db, zSql, -1, &pStmt, 0) ){
44: sqlite3SetString(pzErrMsg, db, sqlite3_errmsg(db));
45: return sqlite3_errcode(db);
46: }
47: VVA_ONLY( rc = ) sqlite3_step(pStmt);
48: assert( rc!=SQLITE_ROW || (db->flags&SQLITE_CountRows) );
49: return vacuumFinalize(db, pStmt, pzErrMsg);
50: }
51:
52: /*
53: ** Execute zSql on database db. The statement returns exactly
54: ** one column. Execute this as SQL on the same database.
55: */
56: static int execExecSql(sqlite3 *db, char **pzErrMsg, const char *zSql){
57: sqlite3_stmt *pStmt;
58: int rc;
59:
60: rc = sqlite3_prepare(db, zSql, -1, &pStmt, 0);
61: if( rc!=SQLITE_OK ) return rc;
62:
63: while( SQLITE_ROW==sqlite3_step(pStmt) ){
64: rc = execSql(db, pzErrMsg, (char*)sqlite3_column_text(pStmt, 0));
65: if( rc!=SQLITE_OK ){
66: vacuumFinalize(db, pStmt, pzErrMsg);
67: return rc;
68: }
69: }
70:
71: return vacuumFinalize(db, pStmt, pzErrMsg);
72: }
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: sqlite3VdbeAddOp2(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: Btree *pMain; /* The database being vacuumed */
98: Btree *pTemp; /* The temporary database we vacuum into */
99: char *zSql = 0; /* SQL statements */
100: int saved_flags; /* Saved value of the db->flags */
101: int saved_nChange; /* Saved value of db->nChange */
102: int saved_nTotalChange; /* Saved value of db->nTotalChange */
103: void (*saved_xTrace)(void*,const char*); /* Saved db->xTrace */
104: Db *pDb = 0; /* Database to detach at end of vacuum */
105: int isMemDb; /* True if vacuuming a :memory: database */
106: int nRes; /* Bytes of reserved space at the end of each page */
107: int nDb; /* Number of attached databases */
108:
109: if( !db->autoCommit ){
110: sqlite3SetString(pzErrMsg, db, "cannot VACUUM from within a transaction");
111: return SQLITE_ERROR;
112: }
113: if( db->activeVdbeCnt>1 ){
114: sqlite3SetString(pzErrMsg, db,"cannot VACUUM - SQL statements in progress");
115: return SQLITE_ERROR;
116: }
117:
118: /* Save the current value of the database flags so that it can be
119: ** restored before returning. Then set the writable-schema flag, and
120: ** disable CHECK and foreign key constraints. */
121: saved_flags = db->flags;
122: saved_nChange = db->nChange;
123: saved_nTotalChange = db->nTotalChange;
124: saved_xTrace = db->xTrace;
125: db->flags |= SQLITE_WriteSchema | SQLITE_IgnoreChecks | SQLITE_PreferBuiltin;
126: db->flags &= ~(SQLITE_ForeignKeys | SQLITE_ReverseOrder);
127: db->xTrace = 0;
128:
129: pMain = db->aDb[0].pBt;
130: isMemDb = sqlite3PagerIsMemdb(sqlite3BtreePager(pMain));
131:
132: /* Attach the temporary database as 'vacuum_db'. The synchronous pragma
133: ** can be set to 'off' for this file, as it is not recovered if a crash
134: ** occurs anyway. The integrity of the database is maintained by a
135: ** (possibly synchronous) transaction opened on the main database before
136: ** sqlite3BtreeCopyFile() is called.
137: **
138: ** An optimisation would be to use a non-journaled pager.
139: ** (Later:) I tried setting "PRAGMA vacuum_db.journal_mode=OFF" but
140: ** that actually made the VACUUM run slower. Very little journalling
141: ** actually occurs when doing a vacuum since the vacuum_db is initially
142: ** empty. Only the journal header is written. Apparently it takes more
143: ** time to parse and run the PRAGMA to turn journalling off than it does
144: ** to write the journal header file.
145: */
146: nDb = db->nDb;
147: if( sqlite3TempInMemory(db) ){
148: zSql = "ATTACH ':memory:' AS vacuum_db;";
149: }else{
150: zSql = "ATTACH '' AS vacuum_db;";
151: }
152: rc = execSql(db, pzErrMsg, zSql);
153: if( db->nDb>nDb ){
154: pDb = &db->aDb[db->nDb-1];
155: assert( strcmp(pDb->zName,"vacuum_db")==0 );
156: }
157: if( rc!=SQLITE_OK ) goto end_of_vacuum;
158: pTemp = db->aDb[db->nDb-1].pBt;
159:
160: /* The call to execSql() to attach the temp database has left the file
161: ** locked (as there was more than one active statement when the transaction
162: ** to read the schema was concluded. Unlock it here so that this doesn't
163: ** cause problems for the call to BtreeSetPageSize() below. */
164: sqlite3BtreeCommit(pTemp);
165:
166: nRes = sqlite3BtreeGetReserve(pMain);
167:
168: /* A VACUUM cannot change the pagesize of an encrypted database. */
169: #ifdef SQLITE_HAS_CODEC
170: if( db->nextPagesize ){
171: extern void sqlite3CodecGetKey(sqlite3*, int, void**, int*);
172: int nKey;
173: char *zKey;
174: sqlite3CodecGetKey(db, 0, (void**)&zKey, &nKey);
175: if( nKey ) db->nextPagesize = 0;
176: }
177: #endif
178:
179: /* Do not attempt to change the page size for a WAL database */
180: if( sqlite3PagerGetJournalMode(sqlite3BtreePager(pMain))
181: ==PAGER_JOURNALMODE_WAL ){
182: db->nextPagesize = 0;
183: }
184:
185: if( sqlite3BtreeSetPageSize(pTemp, sqlite3BtreeGetPageSize(pMain), nRes, 0)
186: || (!isMemDb && sqlite3BtreeSetPageSize(pTemp, db->nextPagesize, nRes, 0))
187: || NEVER(db->mallocFailed)
188: ){
189: rc = SQLITE_NOMEM;
190: goto end_of_vacuum;
191: }
192: rc = execSql(db, pzErrMsg, "PRAGMA vacuum_db.synchronous=OFF");
193: if( rc!=SQLITE_OK ){
194: goto end_of_vacuum;
195: }
196:
197: #ifndef SQLITE_OMIT_AUTOVACUUM
198: sqlite3BtreeSetAutoVacuum(pTemp, db->nextAutovac>=0 ? db->nextAutovac :
199: sqlite3BtreeGetAutoVacuum(pMain));
200: #endif
201:
202: /* Begin a transaction */
203: rc = execSql(db, pzErrMsg, "BEGIN EXCLUSIVE;");
204: if( rc!=SQLITE_OK ) goto end_of_vacuum;
205:
206: /* Query the schema of the main database. Create a mirror schema
207: ** in the temporary database.
208: */
209: rc = execExecSql(db, pzErrMsg,
210: "SELECT 'CREATE TABLE vacuum_db.' || substr(sql,14) "
211: " FROM sqlite_master WHERE type='table' AND name!='sqlite_sequence'"
212: " AND rootpage>0"
213: );
214: if( rc!=SQLITE_OK ) goto end_of_vacuum;
215: rc = execExecSql(db, pzErrMsg,
216: "SELECT 'CREATE INDEX vacuum_db.' || substr(sql,14)"
217: " FROM sqlite_master WHERE sql LIKE 'CREATE INDEX %' ");
218: if( rc!=SQLITE_OK ) goto end_of_vacuum;
219: rc = execExecSql(db, pzErrMsg,
220: "SELECT 'CREATE UNIQUE INDEX vacuum_db.' || substr(sql,21) "
221: " FROM sqlite_master WHERE sql LIKE 'CREATE UNIQUE INDEX %'");
222: if( rc!=SQLITE_OK ) goto end_of_vacuum;
223:
224: /* Loop through the tables in the main database. For each, do
225: ** an "INSERT INTO vacuum_db.xxx SELECT * FROM main.xxx;" to copy
226: ** the contents to the temporary database.
227: */
228: rc = execExecSql(db, pzErrMsg,
229: "SELECT 'INSERT INTO vacuum_db.' || quote(name) "
230: "|| ' SELECT * FROM main.' || quote(name) || ';'"
231: "FROM main.sqlite_master "
232: "WHERE type = 'table' AND name!='sqlite_sequence' "
233: " AND rootpage>0"
234: );
235: if( rc!=SQLITE_OK ) goto end_of_vacuum;
236:
237: /* Copy over the sequence table
238: */
239: rc = execExecSql(db, pzErrMsg,
240: "SELECT 'DELETE FROM vacuum_db.' || quote(name) || ';' "
241: "FROM vacuum_db.sqlite_master WHERE name='sqlite_sequence' "
242: );
243: if( rc!=SQLITE_OK ) goto end_of_vacuum;
244: rc = execExecSql(db, pzErrMsg,
245: "SELECT 'INSERT INTO vacuum_db.' || quote(name) "
246: "|| ' SELECT * FROM main.' || quote(name) || ';' "
247: "FROM vacuum_db.sqlite_master WHERE name=='sqlite_sequence';"
248: );
249: if( rc!=SQLITE_OK ) goto end_of_vacuum;
250:
251:
252: /* Copy the triggers, views, and virtual tables from the main database
253: ** over to the temporary database. None of these objects has any
254: ** associated storage, so all we have to do is copy their entries
255: ** from the SQLITE_MASTER table.
256: */
257: rc = execSql(db, pzErrMsg,
258: "INSERT INTO vacuum_db.sqlite_master "
259: " SELECT type, name, tbl_name, rootpage, sql"
260: " FROM main.sqlite_master"
261: " WHERE type='view' OR type='trigger'"
262: " OR (type='table' AND rootpage=0)"
263: );
264: if( rc ) goto end_of_vacuum;
265:
266: /* At this point, there is a write transaction open on both the
267: ** vacuum database and the main database. Assuming no error occurs,
268: ** both transactions are closed by this block - the main database
269: ** transaction by sqlite3BtreeCopyFile() and the other by an explicit
270: ** call to sqlite3BtreeCommit().
271: */
272: {
273: u32 meta;
274: int i;
275:
276: /* This array determines which meta meta values are preserved in the
277: ** vacuum. Even entries are the meta value number and odd entries
278: ** are an increment to apply to the meta value after the vacuum.
279: ** The increment is used to increase the schema cookie so that other
280: ** connections to the same database will know to reread the schema.
281: */
282: static const unsigned char aCopy[] = {
283: BTREE_SCHEMA_VERSION, 1, /* Add one to the old schema cookie */
284: BTREE_DEFAULT_CACHE_SIZE, 0, /* Preserve the default page cache size */
285: BTREE_TEXT_ENCODING, 0, /* Preserve the text encoding */
286: BTREE_USER_VERSION, 0, /* Preserve the user version */
287: };
288:
289: assert( 1==sqlite3BtreeIsInTrans(pTemp) );
290: assert( 1==sqlite3BtreeIsInTrans(pMain) );
291:
292: /* Copy Btree meta values */
293: for(i=0; i<ArraySize(aCopy); i+=2){
294: /* GetMeta() and UpdateMeta() cannot fail in this context because
295: ** we already have page 1 loaded into cache and marked dirty. */
296: sqlite3BtreeGetMeta(pMain, aCopy[i], &meta);
297: rc = sqlite3BtreeUpdateMeta(pTemp, aCopy[i], meta+aCopy[i+1]);
298: if( NEVER(rc!=SQLITE_OK) ) goto end_of_vacuum;
299: }
300:
301: rc = sqlite3BtreeCopyFile(pMain, pTemp);
302: if( rc!=SQLITE_OK ) goto end_of_vacuum;
303: rc = sqlite3BtreeCommit(pTemp);
304: if( rc!=SQLITE_OK ) goto end_of_vacuum;
305: #ifndef SQLITE_OMIT_AUTOVACUUM
306: sqlite3BtreeSetAutoVacuum(pMain, sqlite3BtreeGetAutoVacuum(pTemp));
307: #endif
308: }
309:
310: assert( rc==SQLITE_OK );
311: rc = sqlite3BtreeSetPageSize(pMain, sqlite3BtreeGetPageSize(pTemp), nRes,1);
312:
313: end_of_vacuum:
314: /* Restore the original value of db->flags */
315: db->flags = saved_flags;
316: db->nChange = saved_nChange;
317: db->nTotalChange = saved_nTotalChange;
318: db->xTrace = saved_xTrace;
319: sqlite3BtreeSetPageSize(pMain, -1, -1, 1);
320:
321: /* Currently there is an SQL level transaction open on the vacuum
322: ** database. No locks are held on any other files (since the main file
323: ** was committed at the btree level). So it safe to end the transaction
324: ** by manually setting the autoCommit flag to true and detaching the
325: ** vacuum database. The vacuum_db journal file is deleted when the pager
326: ** is closed by the DETACH.
327: */
328: db->autoCommit = 1;
329:
330: if( pDb ){
331: sqlite3BtreeClose(pDb->pBt);
332: pDb->pBt = 0;
333: pDb->pSchema = 0;
334: }
335:
336: /* This both clears the schemas and reduces the size of the db->aDb[]
337: ** array. */
338: sqlite3ResetInternalSchema(db, -1);
339:
340: return rc;
341: }
342:
343: #endif /* SQLITE_OMIT_VACUUM && SQLITE_OMIT_ATTACH */
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to vacuum.c CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / src