wasCSharpSQLite – Blame information for rev 1
?pathlinks?
Rev | Author | Line No. | Line |
---|---|---|---|
1 | office | 1 | using System; |
2 | using System.Diagnostics; |
||
3 | using System.Text; |
||
4 | |||
5 | using Pgno = System.UInt32; |
||
6 | |||
7 | using u32 = System.UInt32; |
||
8 | |||
9 | namespace Community.CsharpSqlite |
||
10 | { |
||
11 | using sqlite3_stmt = Sqlite3.Vdbe; |
||
12 | |||
13 | public partial class Sqlite3 |
||
14 | { |
||
15 | /* |
||
16 | ** 2003 April 6 |
||
17 | ** |
||
18 | ** The author disclaims copyright to this source code. In place of |
||
19 | ** a legal notice, here is a blessing: |
||
20 | ** |
||
21 | ** May you do good and not evil. |
||
22 | ** May you find forgiveness for yourself and forgive others. |
||
23 | ** May you share freely, never taking more than you give. |
||
24 | ** |
||
25 | ************************************************************************* |
||
26 | ** This file contains code used to implement the VACUUM command. |
||
27 | ** |
||
28 | ** Most of the code in this file may be omitted by defining the |
||
29 | ** SQLITE_OMIT_VACUUM macro. |
||
30 | ************************************************************************* |
||
31 | ** Included in SQLite3 port to C#-SQLite; 2008 Noah B Hart |
||
32 | ** C#-SQLite is an independent reimplementation of the SQLite software library |
||
33 | ** |
||
34 | ** SQLITE_SOURCE_ID: 2011-05-19 13:26:54 ed1da510a239ea767a01dc332b667119fa3c908e |
||
35 | ** |
||
36 | ************************************************************************* |
||
37 | */ |
||
38 | //#include "sqliteInt.h" |
||
39 | //#include "vdbeInt.h" |
||
40 | |||
41 | #if !SQLITE_OMIT_VACUUM && !SQLITE_OMIT_ATTACH |
||
42 | /* |
||
43 | ** Finalize a prepared statement. If there was an error, store the |
||
44 | ** text of the error message in *pzErrMsg. Return the result code. |
||
45 | */ |
||
46 | static int vacuumFinalize( sqlite3 db, sqlite3_stmt pStmt, string pzErrMsg ) |
||
47 | { |
||
48 | int rc; |
||
49 | rc = sqlite3VdbeFinalize( ref pStmt ); |
||
50 | if ( rc != 0 ) |
||
51 | { |
||
52 | sqlite3SetString( ref pzErrMsg, db, sqlite3_errmsg( db ) ); |
||
53 | } |
||
54 | return rc; |
||
55 | } |
||
56 | |||
57 | /* |
||
58 | ** Execute zSql on database db. Return an error code. |
||
59 | */ |
||
60 | static int execSql( sqlite3 db, string pzErrMsg, string zSql ) |
||
61 | { |
||
62 | sqlite3_stmt pStmt = null; |
||
63 | #if !NDEBUG |
||
64 | int rc; |
||
65 | //VVA_ONLY( int rc; ) |
||
66 | #endif |
||
67 | if ( zSql == null ) |
||
68 | { |
||
69 | return SQLITE_NOMEM; |
||
70 | } |
||
71 | if ( SQLITE_OK != sqlite3_prepare( db, zSql, -1, ref pStmt, 0 ) ) |
||
72 | { |
||
73 | sqlite3SetString( ref pzErrMsg, db, sqlite3_errmsg( db ) ); |
||
74 | return sqlite3_errcode( db ); |
||
75 | } |
||
76 | #if !NDEBUG |
||
77 | rc = sqlite3_step( pStmt ); |
||
78 | //VVA_ONLY( rc = ) sqlite3_step(pStmt); |
||
79 | Debug.Assert( rc != SQLITE_ROW ); |
||
80 | #else |
||
81 | sqlite3_step(pStmt); |
||
82 | #endif |
||
83 | return vacuumFinalize( db, pStmt, pzErrMsg ); |
||
84 | } |
||
85 | |||
86 | /* |
||
87 | ** Execute zSql on database db. The statement returns exactly |
||
88 | ** one column. Execute this as SQL on the same database. |
||
89 | */ |
||
90 | static int execExecSql( sqlite3 db, string pzErrMsg, string zSql ) |
||
91 | { |
||
92 | sqlite3_stmt pStmt = null; |
||
93 | int rc; |
||
94 | |||
95 | rc = sqlite3_prepare( db, zSql, -1, ref pStmt, 0 ); |
||
96 | if ( rc != SQLITE_OK ) |
||
97 | return rc; |
||
98 | |||
99 | while ( SQLITE_ROW == sqlite3_step( pStmt ) ) |
||
100 | { |
||
101 | rc = execSql( db, pzErrMsg, sqlite3_column_text( pStmt, 0 ) ); |
||
102 | if ( rc != SQLITE_OK ) |
||
103 | { |
||
104 | vacuumFinalize( db, pStmt, pzErrMsg ); |
||
105 | return rc; |
||
106 | } |
||
107 | } |
||
108 | |||
109 | return vacuumFinalize( db, pStmt, pzErrMsg ); |
||
110 | } |
||
111 | |||
112 | /* |
||
113 | ** The non-standard VACUUM command is used to clean up the database, |
||
114 | ** collapse free space, etc. It is modelled after the VACUUM command |
||
115 | ** in PostgreSQL. |
||
116 | ** |
||
117 | ** In version 1.0.x of SQLite, the VACUUM command would call |
||
118 | ** gdbm_reorganize() on all the database tables. But beginning |
||
119 | ** with 2.0.0, SQLite no longer uses GDBM so this command has |
||
120 | ** become a no-op. |
||
121 | */ |
||
122 | static void sqlite3Vacuum( Parse pParse ) |
||
123 | { |
||
124 | Vdbe v = sqlite3GetVdbe( pParse ); |
||
125 | if ( v != null ) |
||
126 | { |
||
127 | sqlite3VdbeAddOp2( v, OP_Vacuum, 0, 0 ); |
||
128 | } |
||
129 | return; |
||
130 | } |
||
131 | |||
132 | /* |
||
133 | ** This routine implements the OP_Vacuum opcode of the VDBE. |
||
134 | */ |
||
135 | static int sqlite3RunVacuum( ref string pzErrMsg, sqlite3 db ) |
||
136 | { |
||
137 | int rc = SQLITE_OK; /* Return code from service routines */ |
||
138 | Btree pMain; /* The database being vacuumed */ |
||
139 | Btree pTemp; /* The temporary database we vacuum into */ |
||
140 | int saved_flags; /* Saved value of the db.flags */ |
||
141 | int saved_nChange; /* Saved value of db.nChange */ |
||
142 | int saved_nTotalChange; /* Saved value of db.nTotalChange */ |
||
143 | dxTrace saved_xTrace; //void (*saved_xTrace)(void*,const char*); /* Saved db->xTrace */ |
||
144 | Db pDb = null; /* Database to detach at end of vacuum */ |
||
145 | bool isMemDb; /* True if vacuuming a :memory: database */ |
||
146 | int nRes; /* Bytes of reserved space at the end of each page */ |
||
147 | |||
148 | if ( 0 == db.autoCommit ) |
||
149 | { |
||
150 | sqlite3SetString( ref pzErrMsg, db, "cannot VACUUM from within a transaction" ); |
||
151 | return SQLITE_ERROR; |
||
152 | } |
||
153 | if ( db.activeVdbeCnt > 1 ) |
||
154 | { |
||
155 | sqlite3SetString( ref pzErrMsg, db, "cannot VACUUM - SQL statements in progress" ); |
||
156 | return SQLITE_ERROR; |
||
157 | } |
||
158 | |||
159 | /* Save the current value of the database flags so that it can be |
||
160 | ** restored before returning. Then set the writable-schema flag, and |
||
161 | ** disable CHECK and foreign key constraints. */ |
||
162 | saved_flags = db.flags; |
||
163 | saved_nChange = db.nChange; |
||
164 | saved_nTotalChange = db.nTotalChange; |
||
165 | saved_xTrace = db.xTrace; |
||
166 | db.flags |= SQLITE_WriteSchema | SQLITE_IgnoreChecks | SQLITE_PreferBuiltin; |
||
167 | db.flags &= ~( SQLITE_ForeignKeys | SQLITE_ReverseOrder ); |
||
168 | |||
169 | db.xTrace = null; |
||
170 | |||
171 | pMain = db.aDb[0].pBt; |
||
172 | isMemDb = sqlite3PagerIsMemdb( sqlite3BtreePager( pMain ) ); |
||
173 | |||
174 | /* Attach the temporary database as 'vacuum_db'. The synchronous pragma |
||
175 | ** can be set to 'off' for this file, as it is not recovered if a crash |
||
176 | ** occurs anyway. The integrity of the database is maintained by a |
||
177 | ** (possibly synchronous) transaction opened on the main database before |
||
178 | ** sqlite3BtreeCopyFile() is called. |
||
179 | ** |
||
180 | ** An optimisation would be to use a non-journaled pager. |
||
181 | ** (Later:) I tried setting "PRAGMA vacuum_db.journal_mode=OFF" but |
||
182 | ** that actually made the VACUUM run slower. Very little journalling |
||
183 | ** actually occurs when doing a vacuum since the vacuum_db is initially |
||
184 | ** empty. Only the journal header is written. Apparently it takes more |
||
185 | ** time to parse and run the PRAGMA to turn journalling off than it does |
||
186 | ** to write the journal header file. |
||
187 | */ |
||
188 | string zSql = string.Empty; /* SQL statements */ |
||
189 | if ( sqlite3TempInMemory( db ) ) |
||
190 | { |
||
191 | zSql = "ATTACH ':memory:' AS vacuum_db;"; |
||
192 | } |
||
193 | else |
||
194 | { |
||
195 | zSql = "ATTACH '' AS vacuum_db;"; |
||
196 | } |
||
197 | int nDb = db.nDb; /* Number of attached databases */ |
||
198 | rc = execSql( db, pzErrMsg, zSql ); |
||
199 | if ( db.nDb > nDb ) |
||
200 | { |
||
201 | pDb = db.aDb[db.nDb - 1]; |
||
202 | Debug.Assert( pDb.zName == "vacuum_db" ); |
||
203 | } |
||
204 | if ( rc != SQLITE_OK ) |
||
205 | goto end_of_vacuum; |
||
206 | pDb = db.aDb[db.nDb - 1]; |
||
207 | Debug.Assert( db.aDb[db.nDb - 1].zName == "vacuum_db" ); |
||
208 | pTemp = db.aDb[db.nDb - 1].pBt; |
||
209 | |||
210 | /* The call to execSql() to attach the temp database has left the file |
||
211 | ** locked (as there was more than one active statement when the transaction |
||
212 | ** to read the schema was concluded. Unlock it here so that this doesn't |
||
213 | ** cause problems for the call to BtreeSetPageSize() below. */ |
||
214 | sqlite3BtreeCommit( pTemp ); |
||
215 | |||
216 | nRes = sqlite3BtreeGetReserve( pMain ); |
||
217 | |||
218 | /* A VACUUM cannot change the pagesize of an encrypted database. */ |
||
219 | #if SQLITE_HAS_CODEC |
||
220 | if ( db.nextPagesize != 0 ) |
||
221 | { |
||
222 | //extern void sqlite3CodecGetKey(sqlite3*, int, void**, int*); |
||
223 | int nKey; |
||
224 | string zKey; |
||
225 | sqlite3CodecGetKey( db, 0, out zKey, out nKey ); // sqlite3CodecGetKey(db, 0, (void**)&zKey, nKey); |
||
226 | if ( nKey != 0 ) |
||
227 | db.nextPagesize = 0; |
||
228 | } |
||
229 | #endif |
||
230 | |||
231 | /* Do not attempt to change the page size for a WAL database */ |
||
232 | if ( sqlite3PagerGetJournalMode( sqlite3BtreePager( pMain ) ) |
||
233 | == PAGER_JOURNALMODE_WAL ) |
||
234 | { |
||
235 | db.nextPagesize = 0; |
||
236 | } |
||
237 | |||
238 | if ( sqlite3BtreeSetPageSize( pTemp, sqlite3BtreeGetPageSize( pMain ), nRes, 0 ) != 0 |
||
239 | || ( !isMemDb && sqlite3BtreeSetPageSize( pTemp, db.nextPagesize, nRes, 0 ) != 0 ) |
||
240 | //|| NEVER( db.mallocFailed != 0 ) |
||
241 | ) |
||
242 | { |
||
243 | rc = SQLITE_NOMEM; |
||
244 | goto end_of_vacuum; |
||
245 | } |
||
246 | rc = execSql( db, pzErrMsg, "PRAGMA vacuum_db.synchronous=OFF" ); |
||
247 | if ( rc != SQLITE_OK ) |
||
248 | { |
||
249 | goto end_of_vacuum; |
||
250 | } |
||
251 | |||
252 | #if !SQLITE_OMIT_AUTOVACUUM |
||
253 | sqlite3BtreeSetAutoVacuum( pTemp, db.nextAutovac >= 0 ? db.nextAutovac : |
||
254 | sqlite3BtreeGetAutoVacuum( pMain ) ); |
||
255 | #endif |
||
256 | |||
257 | /* Begin a transaction */ |
||
258 | rc = execSql( db, pzErrMsg, "BEGIN EXCLUSIVE;" ); |
||
259 | if ( rc != SQLITE_OK ) |
||
260 | goto end_of_vacuum; |
||
261 | |||
262 | /* Query the schema of the main database. Create a mirror schema |
||
263 | ** in the temporary database. |
||
264 | */ |
||
265 | rc = execExecSql( db, pzErrMsg, |
||
266 | "SELECT 'CREATE TABLE vacuum_db.' || substr(sql,14) " + |
||
267 | " FROM sqlite_master WHERE type='table' AND name!='sqlite_sequence'" + |
||
268 | " AND rootpage>0" |
||
269 | ); |
||
270 | if ( rc != SQLITE_OK ) |
||
271 | goto end_of_vacuum; |
||
272 | rc = execExecSql( db, pzErrMsg, |
||
273 | "SELECT 'CREATE INDEX vacuum_db.' || substr(sql,14)" + |
||
274 | " FROM sqlite_master WHERE sql LIKE 'CREATE INDEX %' " ); |
||
275 | if ( rc != SQLITE_OK ) |
||
276 | goto end_of_vacuum; |
||
277 | rc = execExecSql( db, pzErrMsg, |
||
278 | "SELECT 'CREATE UNIQUE INDEX vacuum_db.' || substr(sql,21) " + |
||
279 | " FROM sqlite_master WHERE sql LIKE 'CREATE UNIQUE INDEX %'" ); |
||
280 | if ( rc != SQLITE_OK ) |
||
281 | goto end_of_vacuum; |
||
282 | |||
283 | /* Loop through the tables in the main database. For each, do |
||
284 | ** an "INSERT INTO vacuum_db.xxx SELECT * FROM main.xxx;" to copy |
||
285 | ** the contents to the temporary database. |
||
286 | */ |
||
287 | rc = execExecSql( db, pzErrMsg, |
||
288 | "SELECT 'INSERT INTO vacuum_db.' || quote(name) " + |
||
289 | "|| ' SELECT * FROM main.' || quote(name) || ';'" + |
||
290 | "FROM main.sqlite_master " + |
||
291 | "WHERE type = 'table' AND name!='sqlite_sequence' " + |
||
292 | " AND rootpage>0" |
||
293 | |||
294 | ); |
||
295 | if ( rc != SQLITE_OK ) |
||
296 | goto end_of_vacuum; |
||
297 | |||
298 | /* Copy over the sequence table |
||
299 | */ |
||
300 | rc = execExecSql( db, pzErrMsg, |
||
301 | "SELECT 'DELETE FROM vacuum_db.' || quote(name) || ';' " + |
||
302 | "FROM vacuum_db.sqlite_master WHERE name='sqlite_sequence' " |
||
303 | ); |
||
304 | if ( rc != SQLITE_OK ) |
||
305 | goto end_of_vacuum; |
||
306 | rc = execExecSql( db, pzErrMsg, |
||
307 | "SELECT 'INSERT INTO vacuum_db.' || quote(name) " + |
||
308 | "|| ' SELECT * FROM main.' || quote(name) || ';' " + |
||
309 | "FROM vacuum_db.sqlite_master WHERE name=='sqlite_sequence';" |
||
310 | ); |
||
311 | if ( rc != SQLITE_OK ) |
||
312 | goto end_of_vacuum; |
||
313 | |||
314 | |||
315 | /* Copy the triggers, views, and virtual tables from the main database |
||
316 | ** over to the temporary database. None of these objects has any |
||
317 | ** associated storage, so all we have to do is copy their entries |
||
318 | ** from the SQLITE_MASTER table. |
||
319 | */ |
||
320 | rc = execSql( db, pzErrMsg, |
||
321 | "INSERT INTO vacuum_db.sqlite_master " + |
||
322 | " SELECT type, name, tbl_name, rootpage, sql" + |
||
323 | " FROM main.sqlite_master" + |
||
324 | " WHERE type='view' OR type='trigger'" + |
||
325 | " OR (type='table' AND rootpage=0)" |
||
326 | ); |
||
327 | if ( rc != 0 ) |
||
328 | goto end_of_vacuum; |
||
329 | |||
330 | /* At this point, unless the main db was completely empty, there is now a |
||
331 | ** transaction open on the vacuum database, but not on the main database. |
||
332 | ** Open a btree level transaction on the main database. This allows a |
||
333 | ** call to sqlite3BtreeCopyFile(). The main database btree level |
||
334 | ** transaction is then committed, so the SQL level never knows it was |
||
335 | ** opened for writing. This way, the SQL transaction used to create the |
||
336 | ** temporary database never needs to be committed. |
||
337 | */ |
||
338 | { |
||
339 | u32 meta = 0; |
||
340 | int i; |
||
341 | |||
342 | /* This array determines which meta meta values are preserved in the |
||
343 | ** vacuum. Even entries are the meta value number and odd entries |
||
344 | ** are an increment to apply to the meta value after the vacuum. |
||
345 | ** The increment is used to increase the schema cookie so that other |
||
346 | ** connections to the same database will know to reread the schema. |
||
347 | */ |
||
348 | byte[] aCopy = new byte[] { |
||
349 | BTREE_SCHEMA_VERSION, 1, /* Add one to the old schema cookie */ |
||
350 | BTREE_DEFAULT_CACHE_SIZE, 0, /* Preserve the default page cache size */ |
||
351 | BTREE_TEXT_ENCODING, 0, /* Preserve the text encoding */ |
||
352 | BTREE_USER_VERSION, 0, /* Preserve the user version */ |
||
353 | }; |
||
354 | |||
355 | Debug.Assert( sqlite3BtreeIsInTrans( pTemp ) ); |
||
356 | Debug.Assert( sqlite3BtreeIsInTrans( pMain ) ); |
||
357 | |||
358 | /* Copy Btree meta values */ |
||
359 | for ( i = 0; i < ArraySize( aCopy ); i += 2 ) |
||
360 | { |
||
361 | /* GetMeta() and UpdateMeta() cannot fail in this context because |
||
362 | ** we already have page 1 loaded into cache and marked dirty. */ |
||
363 | sqlite3BtreeGetMeta( pMain, aCopy[i], ref meta ); |
||
364 | rc = sqlite3BtreeUpdateMeta( pTemp, aCopy[i], (u32)( meta + aCopy[i + 1] ) ); |
||
365 | if ( NEVER( rc != SQLITE_OK ) ) |
||
366 | goto end_of_vacuum; |
||
367 | } |
||
368 | |||
369 | rc = sqlite3BtreeCopyFile( pMain, pTemp ); |
||
370 | if ( rc != SQLITE_OK ) |
||
371 | goto end_of_vacuum; |
||
372 | rc = sqlite3BtreeCommit( pTemp ); |
||
373 | if ( rc != SQLITE_OK ) |
||
374 | goto end_of_vacuum; |
||
375 | #if !SQLITE_OMIT_AUTOVACUUM |
||
376 | sqlite3BtreeSetAutoVacuum( pMain, sqlite3BtreeGetAutoVacuum( pTemp ) ); |
||
377 | #endif |
||
378 | } |
||
379 | Debug.Assert( rc == SQLITE_OK ); |
||
380 | rc = sqlite3BtreeSetPageSize( pMain, sqlite3BtreeGetPageSize( pTemp ), nRes, 1 ); |
||
381 | |||
382 | end_of_vacuum: |
||
383 | /* Restore the original value of db.flags */ |
||
384 | db.flags = saved_flags; |
||
385 | db.nChange = saved_nChange; |
||
386 | db.nTotalChange = saved_nTotalChange; |
||
387 | db.xTrace = saved_xTrace; |
||
388 | sqlite3BtreeSetPageSize( pMain, -1, -1, 1 ); |
||
389 | |||
390 | /* Currently there is an SQL level transaction open on the vacuum |
||
391 | ** database. No locks are held on any other files (since the main file |
||
392 | ** was committed at the btree level). So it safe to end the transaction |
||
393 | ** by manually setting the autoCommit flag to true and detaching the |
||
394 | ** vacuum database. The vacuum_db journal file is deleted when the pager |
||
395 | ** is closed by the DETACH. |
||
396 | */ |
||
397 | db.autoCommit = 1; |
||
398 | |||
399 | if ( pDb != null ) |
||
400 | { |
||
401 | sqlite3BtreeClose( ref pDb.pBt ); |
||
402 | pDb.pBt = null; |
||
403 | pDb.pSchema = null; |
||
404 | } |
||
405 | |||
406 | /* This both clears the schemas and reduces the size of the db->aDb[] |
||
407 | ** array. */ |
||
408 | sqlite3ResetInternalSchema( db, -1 ); |
||
409 | |||
410 | return rc; |
||
411 | } |
||
412 | #endif // * SQLITE_OMIT_VACUUM && SQLITE_OMIT_ATTACH */ |
||
413 | } |
||
414 | } |