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