wasCSharpSQLite – Blame information for rev 1

Subversion Repositories:
Rev:
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 }