wasCSharpSQLite – Blame information for rev

Subversion Repositories:
Rev:
Rev Author Line No. Line
1 office 1 # 2010 June 15
2 #
3 # The author disclaims copyright to this source code. In place of
4 # a legal notice, here is a blessing:
5 #
6 # May you do good and not evil.
7 # May you find forgiveness for yourself and forgive others.
8 # May you share freely, never taking more than you give.
9 #
10 #***********************************************************************
11 #
12  
13 set testdir [file dirname $argv0]
14 source $testdir/tester.tcl
15 source $testdir/lock_common.tcl
16 source $testdir/malloc_common.tcl
17 source $testdir/wal_common.tcl
18  
19 # Do not use a codec for tests in this file, as the database file is
20 # manipulated directly using tcl scripts (using the [hexio_write] command).
21 #
22 do_not_use_codec
23  
24 #
25 # pager1-1.*: Test inter-process locking (clients in multiple processes).
26 #
27 # pager1-2.*: Test intra-process locking (multiple clients in this process).
28 #
29 # pager1-3.*: Savepoint related tests.
30 #
31 # pager1-4.*: Hot-journal related tests.
32 #
33 # pager1-5.*: Cases related to multi-file commits.
34 #
35 # pager1-6.*: Cases related to "PRAGMA max_page_count"
36 #
37 # pager1-7.*: Cases specific to "PRAGMA journal_mode=TRUNCATE"
38 #
39 # pager1-8.*: Cases using temporary and in-memory databases.
40 #
41 # pager1-9.*: Tests related to the backup API.
42 #
43 # pager1-10.*: Test that the assumed file-system sector-size is limited to
44 # 64KB.
45 #
46 # pager1-12.*: Tests involving "PRAGMA page_size"
47 #
48 # pager1-13.*: Cases specific to "PRAGMA journal_mode=PERSIST"
49 #
50 # pager1-14.*: Cases specific to "PRAGMA journal_mode=OFF"
51 #
52 # pager1-15.*: Varying sqlite3_vfs.szOsFile
53 #
54 # pager1-16.*: Varying sqlite3_vfs.mxPathname
55 #
56 # pager1-17.*: Tests related to "PRAGMA omit_readlock"
57 #
58 # pager1-18.*: Test that the pager layer responds correctly if the b-tree
59 # requests an invalid page number (due to db corruption).
60 #
61  
62 proc recursive_select {id table {script {}}} {
63 set cnt 0
64 db eval "SELECT rowid, * FROM $table WHERE rowid = ($id-1)" {
65 recursive_select $rowid $table $script
66 incr cnt
67 }
68 if {$cnt==0} { eval $script }
69 }
70  
71 set a_string_counter 1
72 proc a_string {n} {
73 global a_string_counter
74 incr a_string_counter
75 string range [string repeat "${a_string_counter}." $n] 1 $n
76 }
77 db func a_string a_string
78  
79 do_multiclient_test tn {
80  
81 # Create and populate a database table using connection [db]. Check
82 # that connections [db2] and [db3] can see the schema and content.
83 #
84 do_test pager1-$tn.1 {
85 sql1 {
86 CREATE TABLE t1(a PRIMARY KEY, b);
87 CREATE INDEX i1 ON t1(b);
88 INSERT INTO t1 VALUES(1, 'one'); INSERT INTO t1 VALUES(2, 'two');
89 }
90 } {}
91 do_test pager1-$tn.2 { sql2 { SELECT * FROM t1 } } {1 one 2 two}
92 do_test pager1-$tn.3 { sql3 { SELECT * FROM t1 } } {1 one 2 two}
93  
94 # Open a transaction and add a row using [db]. This puts [db] in
95 # RESERVED state. Check that connections [db2] and [db3] can still
96 # read the database content as it was before the transaction was
97 # opened. [db] should see the inserted row.
98 #
99 do_test pager1-$tn.4 {
100 sql1 {
101 BEGIN;
102 INSERT INTO t1 VALUES(3, 'three');
103 }
104 } {}
105 do_test pager1-$tn.5 { sql2 { SELECT * FROM t1 } } {1 one 2 two}
106 do_test pager1-$tn.7 { sql1 { SELECT * FROM t1 } } {1 one 2 two 3 three}
107  
108 # [db] still has an open write transaction. Check that this prevents
109 # other connections (specifically [db2]) from writing to the database.
110 #
111 # Even if [db2] opens a transaction first, it may not write to the
112 # database. After the attempt to write the db within a transaction,
113 # [db2] is left with an open transaction, but not a read-lock on
114 # the main database. So it does not prevent [db] from committing.
115 #
116 do_test pager1-$tn.8 {
117 csql2 { UPDATE t1 SET a = a + 10 }
118 } {1 {database is locked}}
119 do_test pager1-$tn.9 {
120 csql2 {
121 BEGIN;
122 UPDATE t1 SET a = a + 10;
123 }
124 } {1 {database is locked}}
125  
126 # Have [db] commit its transactions. Check the other connections can
127 # now see the new database content.
128 #
129 do_test pager1-$tn.10 { sql1 { COMMIT } } {}
130 do_test pager1-$tn.11 { sql1 { SELECT * FROM t1 } } {1 one 2 two 3 three}
131 do_test pager1-$tn.12 { sql2 { SELECT * FROM t1 } } {1 one 2 two 3 three}
132 do_test pager1-$tn.13 { sql3 { SELECT * FROM t1 } } {1 one 2 two 3 three}
133  
134 # Check that, as noted above, [db2] really did keep an open transaction
135 # after the attempt to write the database failed.
136 #
137 do_test pager1-$tn.14 {
138 csql2 { BEGIN }
139 } {1 {cannot start a transaction within a transaction}}
140 do_test pager1-$tn.15 { sql2 { ROLLBACK } } {}
141  
142 # Have [db2] open a transaction and take a read-lock on the database.
143 # Check that this prevents [db] from writing to the database (outside
144 # of any transaction). After this fails, check that [db3] can read
145 # the db (showing that [db] did not take a PENDING lock etc.)
146 #
147 do_test pager1-$tn.15 {
148 sql2 { BEGIN; SELECT * FROM t1; }
149 } {1 one 2 two 3 three}
150 do_test pager1-$tn.16 {
151 csql1 { UPDATE t1 SET a = a + 10 }
152 } {1 {database is locked}}
153 do_test pager1-$tn.17 { sql3 { SELECT * FROM t1 } } {1 one 2 two 3 three}
154  
155 # This time, have [db] open a transaction before writing the database.
156 # This works - [db] gets a RESERVED lock which does not conflict with
157 # the SHARED lock [db2] is holding.
158 #
159 do_test pager1-$tn.18 {
160 sql1 {
161 BEGIN;
162 UPDATE t1 SET a = a + 10;
163 }
164 } {}
165 do_test pager1-$tn-19 {
166 sql1 { PRAGMA lock_status }
167 } {main reserved temp closed}
168 do_test pager1-$tn-20 {
169 sql2 { PRAGMA lock_status }
170 } {main shared temp closed}
171  
172 # Check that all connections can still read the database. Only [db] sees
173 # the updated content (as the transaction has not been committed yet).
174 #
175 do_test pager1-$tn.21 { sql1 { SELECT * FROM t1 } } {11 one 12 two 13 three}
176 do_test pager1-$tn.22 { sql2 { SELECT * FROM t1 } } {1 one 2 two 3 three}
177 do_test pager1-$tn.23 { sql3 { SELECT * FROM t1 } } {1 one 2 two 3 three}
178  
179 # Because [db2] still has the SHARED lock, [db] is unable to commit the
180 # transaction. If it tries, an error is returned and the connection
181 # upgrades to a PENDING lock.
182 #
183 # Once this happens, [db] can read the database and see the new content,
184 # [db2] (still holding SHARED) can still read the old content, but [db3]
185 # (not holding any lock) is prevented by [db]'s PENDING from reading
186 # the database.
187 #
188 do_test pager1-$tn.24 { csql1 { COMMIT } } {1 {database is locked}}
189 do_test pager1-$tn-25 {
190 sql1 { PRAGMA lock_status }
191 } {main pending temp closed}
192 do_test pager1-$tn.26 { sql1 { SELECT * FROM t1 } } {11 one 12 two 13 three}
193 do_test pager1-$tn.27 { sql2 { SELECT * FROM t1 } } {1 one 2 two 3 three}
194 do_test pager1-$tn.28 { csql3 { SELECT * FROM t1 } } {1 {database is locked}}
195  
196 # Have [db2] commit its read transaction, releasing the SHARED lock it
197 # is holding. Now, neither [db2] nor [db3] may read the database (as [db]
198 # is still holding a PENDING).
199 #
200 do_test pager1-$tn.29 { sql2 { COMMIT } } {}
201 do_test pager1-$tn.30 { csql2 { SELECT * FROM t1 } } {1 {database is locked}}
202 do_test pager1-$tn.31 { csql3 { SELECT * FROM t1 } } {1 {database is locked}}
203  
204 # [db] is now able to commit the transaction. Once the transaction is
205 # committed, all three connections can read the new content.
206 #
207 do_test pager1-$tn.25 { sql1 { UPDATE t1 SET a = a+10 } } {}
208 do_test pager1-$tn.26 { sql1 { COMMIT } } {}
209 do_test pager1-$tn.27 { sql1 { SELECT * FROM t1 } } {21 one 22 two 23 three}
210 do_test pager1-$tn.27 { sql2 { SELECT * FROM t1 } } {21 one 22 two 23 three}
211 do_test pager1-$tn.28 { sql3 { SELECT * FROM t1 } } {21 one 22 two 23 three}
212  
213 # Install a busy-handler for connection [db].
214 #
215 set ::nbusy [list]
216 proc busy {n} {
217 lappend ::nbusy $n
218 if {$n>5} { sql2 COMMIT }
219 return 0
220 }
221 db busy busy
222  
223 do_test pager1-$tn.29 {
224 sql1 { BEGIN ; INSERT INTO t1 VALUES('x', 'y') }
225 } {}
226 do_test pager1-$tn.30 {
227 sql2 { BEGIN ; SELECT * FROM t1 }
228 } {21 one 22 two 23 three}
229 do_test pager1-$tn.31 { sql1 COMMIT } {}
230 do_test pager1-$tn.32 { set ::nbusy } {0 1 2 3 4 5 6}
231 }
232  
233 #-------------------------------------------------------------------------
234 # Savepoint related test cases.
235 #
236 # pager1-3.1.2.*: Force a savepoint rollback to cause the database file
237 # to grow.
238 #
239 # pager1-3.1.3.*: Use a journal created in synchronous=off mode as part
240 # of a savepoint rollback.
241 #
242 do_test pager1-3.1.1 {
243 faultsim_delete_and_reopen
244 execsql {
245 CREATE TABLE t1(a PRIMARY KEY, b);
246 CREATE TABLE counter(
247 i CHECK (i<5),
248 u CHECK (u<10)
249 );
250 INSERT INTO counter VALUES(0, 0);
251 CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN
252 UPDATE counter SET i = i+1;
253 END;
254 CREATE TRIGGER tr2 AFTER UPDATE ON t1 BEGIN
255 UPDATE counter SET u = u+1;
256 END;
257 }
258 execsql { SELECT * FROM counter }
259 } {0 0}
260  
261 do_execsql_test pager1-3.1.2 {
262 PRAGMA cache_size = 10;
263 BEGIN;
264 INSERT INTO t1 VALUES(1, randomblob(1500));
265 INSERT INTO t1 VALUES(2, randomblob(1500));
266 INSERT INTO t1 VALUES(3, randomblob(1500));
267 SELECT * FROM counter;
268 } {3 0}
269 do_catchsql_test pager1-3.1.3 {
270 INSERT INTO t1 SELECT a+3, randomblob(1500) FROM t1
271 } {1 {constraint failed}}
272 do_execsql_test pager1-3.4 { SELECT * FROM counter } {3 0}
273 do_execsql_test pager1-3.5 { SELECT a FROM t1 } {1 2 3}
274 do_execsql_test pager1-3.6 { COMMIT } {}
275  
276 foreach {tn sql tcl} {
277 7 { PRAGMA synchronous = NORMAL ; PRAGMA temp_store = 0 } {
278 testvfs tv -default 1
279 tv devchar safe_append
280 }
281 8 { PRAGMA synchronous = NORMAL ; PRAGMA temp_store = 2 } {
282 testvfs tv -default 1
283 tv devchar sequential
284 }
285 9 { PRAGMA synchronous = FULL } { }
286 10 { PRAGMA synchronous = NORMAL } { }
287 11 { PRAGMA synchronous = OFF } { }
288 12 { PRAGMA synchronous = FULL ; PRAGMA fullfsync = 1 } { }
289 13 { PRAGMA synchronous = FULL } {
290 testvfs tv -default 1
291 tv devchar sequential
292 }
293 14 { PRAGMA locking_mode = EXCLUSIVE } {
294 }
295 } {
296 do_test pager1-3.$tn.1 {
297 eval $tcl
298 faultsim_delete_and_reopen
299 db func a_string a_string
300 execsql $sql
301 execsql {
302 PRAGMA auto_vacuum = 2;
303 PRAGMA cache_size = 10;
304 CREATE TABLE z(x INTEGER PRIMARY KEY, y);
305 BEGIN;
306 INSERT INTO z VALUES(NULL, a_string(800));
307 INSERT INTO z SELECT NULL, a_string(800) FROM z; -- 2
308 INSERT INTO z SELECT NULL, a_string(800) FROM z; -- 4
309 INSERT INTO z SELECT NULL, a_string(800) FROM z; -- 8
310 INSERT INTO z SELECT NULL, a_string(800) FROM z; -- 16
311 INSERT INTO z SELECT NULL, a_string(800) FROM z; -- 32
312 INSERT INTO z SELECT NULL, a_string(800) FROM z; -- 64
313 INSERT INTO z SELECT NULL, a_string(800) FROM z; -- 128
314 INSERT INTO z SELECT NULL, a_string(800) FROM z; -- 256
315 COMMIT;
316 }
317 execsql { PRAGMA auto_vacuum }
318 } {2}
319 do_execsql_test pager1-3.$tn.2 {
320 BEGIN;
321 INSERT INTO z VALUES(NULL, a_string(800));
322 INSERT INTO z VALUES(NULL, a_string(800));
323 SAVEPOINT one;
324 UPDATE z SET y = NULL WHERE x>256;
325 PRAGMA incremental_vacuum;
326 SELECT count(*) FROM z WHERE x < 100;
327 ROLLBACK TO one;
328 COMMIT;
329 } {99}
330  
331 do_execsql_test pager1-3.$tn.3 {
332 BEGIN;
333 SAVEPOINT one;
334 UPDATE z SET y = y||x;
335 ROLLBACK TO one;
336 COMMIT;
337 SELECT count(*) FROM z;
338 } {258}
339  
340 do_execsql_test pager1-3.$tn.4 {
341 SAVEPOINT one;
342 UPDATE z SET y = y||x;
343 ROLLBACK TO one;
344 } {}
345 do_execsql_test pager1-3.$tn.5 {
346 SELECT count(*) FROM z;
347 RELEASE one;
348 PRAGMA integrity_check;
349 } {258 ok}
350  
351 do_execsql_test pager1-3.$tn.6 {
352 SAVEPOINT one;
353 RELEASE one;
354 } {}
355  
356 db close
357 catch { tv delete }
358 }
359  
360 #-------------------------------------------------------------------------
361 # Hot journal rollback related test cases.
362 #
363 # pager1.4.1.*: Test that the pager module deletes very small invalid
364 # journal files.
365 #
366 # pager1.4.2.*: Test that if the master journal pointer at the end of a
367 # hot-journal file appears to be corrupt (checksum does not
368 # compute) the associated journal is rolled back (and no
369 # xAccess() call to check for the presence of any master
370 # journal file is made).
371 #
372 # pager1.4.3.*: Test that the contents of a hot-journal are ignored if the
373 # page-size or sector-size in the journal header appear to
374 # be invalid (too large, too small or not a power of 2).
375 #
376 # pager1.4.4.*: Test hot-journal rollback of journal file with a master
377 # journal pointer generated in various "PRAGMA synchronous"
378 # modes.
379 #
380 # pager1.4.5.*: Test that hot-journal rollback stops if it encounters a
381 # journal-record for which the checksum fails.
382 #
383 # pager1.4.6.*: Test that when rolling back a hot-journal that contains a
384 # master journal pointer, the master journal file is deleted
385 # after all the hot-journals that refer to it are deleted.
386 #
387 # pager1.4.7.*: Test that if a hot-journal file exists but a client can
388 # open it for reading only, the database cannot be accessed and
389 # SQLITE_CANTOPEN is returned.
390 #
391 do_test pager1.4.1.1 {
392 faultsim_delete_and_reopen
393 execsql {
394 CREATE TABLE x(y, z);
395 INSERT INTO x VALUES(1, 2);
396 }
397 set fd [open test.db-journal w]
398 puts -nonewline $fd "helloworld"
399 close $fd
400 file exists test.db-journal
401 } {1}
402 do_test pager1.4.1.2 { execsql { SELECT * FROM x } } {1 2}
403 do_test pager1.4.1.3 { file exists test.db-journal } {0}
404  
405 # Set up a [testvfs] to snapshot the file-system just before SQLite
406 # deletes the master-journal to commit a multi-file transaction.
407 #
408 # In subsequent test cases, invoking [faultsim_restore_and_reopen] sets
409 # up the file system to contain two databases, two hot-journal files and
410 # a master-journal.
411 #
412 do_test pager1.4.2.1 {
413 testvfs tstvfs -default 1
414 tstvfs filter xDelete
415 tstvfs script xDeleteCallback
416 proc xDeleteCallback {method file args} {
417 set file [file tail $file]
418 if { [string match *mj* $file] } { faultsim_save }
419 }
420 faultsim_delete_and_reopen
421 db func a_string a_string
422 execsql {
423 ATTACH 'test.db2' AS aux;
424 PRAGMA journal_mode = DELETE;
425 PRAGMA main.cache_size = 10;
426 PRAGMA aux.cache_size = 10;
427 CREATE TABLE t1(a UNIQUE, b UNIQUE);
428 CREATE TABLE aux.t2(a UNIQUE, b UNIQUE);
429 INSERT INTO t1 VALUES(a_string(200), a_string(300));
430 INSERT INTO t1 SELECT a_string(200), a_string(300) FROM t1;
431 INSERT INTO t1 SELECT a_string(200), a_string(300) FROM t1;
432 INSERT INTO t2 SELECT * FROM t1;
433 BEGIN;
434 INSERT INTO t1 SELECT a_string(201), a_string(301) FROM t1;
435 INSERT INTO t1 SELECT a_string(202), a_string(302) FROM t1;
436 INSERT INTO t1 SELECT a_string(203), a_string(303) FROM t1;
437 INSERT INTO t1 SELECT a_string(204), a_string(304) FROM t1;
438 REPLACE INTO t2 SELECT * FROM t1;
439 COMMIT;
440 }
441 db close
442 tstvfs delete
443 } {}
444  
445 if {$::tcl_platform(platform)!="windows"} {
446 do_test pager1.4.2.2 {
447 faultsim_restore_and_reopen
448 execsql {
449 SELECT count(*) FROM t1;
450 PRAGMA integrity_check;
451 }
452 } {4 ok}
453 do_test pager1.4.2.3 {
454 faultsim_restore_and_reopen
455 foreach f [glob test.db-mj*] { file delete -force $f }
456 execsql {
457 SELECT count(*) FROM t1;
458 PRAGMA integrity_check;
459 }
460 } {64 ok}
461 do_test pager1.4.2.4 {
462 faultsim_restore_and_reopen
463 hexio_write test.db-journal [expr [file size test.db-journal]-20] 123456
464 execsql {
465 SELECT count(*) FROM t1;
466 PRAGMA integrity_check;
467 }
468 } {4 ok}
469 do_test pager1.4.2.5 {
470 faultsim_restore_and_reopen
471 hexio_write test.db-journal [expr [file size test.db-journal]-20] 123456
472 foreach f [glob test.db-mj*] { file delete -force $f }
473 execsql {
474 SELECT count(*) FROM t1;
475 PRAGMA integrity_check;
476 }
477 } {4 ok}
478 }
479  
480 do_test pager1.4.3.1 {
481 testvfs tstvfs -default 1
482 tstvfs filter xSync
483 tstvfs script xSyncCallback
484 proc xSyncCallback {method file args} {
485 set file [file tail $file]
486 if { 0==[string match *journal $file] } { faultsim_save }
487 }
488 faultsim_delete_and_reopen
489 execsql {
490 PRAGMA journal_mode = DELETE;
491 CREATE TABLE t1(a, b);
492 INSERT INTO t1 VALUES(1, 2);
493 INSERT INTO t1 VALUES(3, 4);
494 }
495 db close
496 tstvfs delete
497 } {}
498  
499 foreach {tn ofst value result} {
500 2 20 31 {1 2 3 4}
501 3 20 32 {1 2 3 4}
502 4 20 33 {1 2 3 4}
503 5 20 65536 {1 2 3 4}
504 6 20 131072 {1 2 3 4}
505  
506 7 24 511 {1 2 3 4}
507 8 24 513 {1 2 3 4}
508 9 24 131072 {1 2 3 4}
509  
510 10 32 65536 {1 2}
511 } {
512 do_test pager1.4.3.$tn {
513 faultsim_restore_and_reopen
514 hexio_write test.db-journal $ofst [format %.8x $value]
515 execsql { SELECT * FROM t1 }
516 } $result
517 }
518 db close
519  
520 # Set up a VFS that snapshots the file-system just before a master journal
521 # file is deleted to commit a multi-file transaction. Specifically, the
522 # file-system is saved just before the xDelete() call to remove the
523 # master journal file from the file-system.
524 #
525 testvfs tv -default 1
526 tv script copy_on_mj_delete
527 set ::mj_filename_length 0
528 proc copy_on_mj_delete {method filename args} {
529 if {[string match *mj* [file tail $filename]]} {
530 set ::mj_filename_length [string length $filename]
531 faultsim_save
532 }
533 return SQLITE_OK
534 }
535  
536 set pwd [pwd]
537 foreach {tn1 tcl} {
538 1 { set prefix "test.db" }
539 2 {
540 # This test depends on the underlying VFS being able to open paths
541 # 512 bytes in length. The idea is to create a hot-journal file that
542 # contains a master-journal pointer so large that it could contain
543 # a valid page record (if the file page-size is 512 bytes). So as to
544 # make sure SQLite doesn't get confused by this.
545 #
546 set nPadding [expr 511 - $::mj_filename_length]
547 if {$tcl_platform(platform)=="windows"} {
548 # TBD need to figure out how to do this correctly for Windows!!!
549 set nPadding [expr 255 - $::mj_filename_length]
550 }
551  
552 # We cannot just create a really long database file name to open, as
553 # Linux limits a single component of a path to 255 bytes by default
554 # (and presumably other systems have limits too). So create a directory
555 # hierarchy to work in.
556 #
557 set dirname "d123456789012345678901234567890/"
558 set nDir [expr $nPadding / 32]
559 if { $nDir } {
560 set p [string repeat $dirname $nDir]
561 file mkdir $p
562 cd $p
563 }
564  
565 set padding [string repeat x [expr $nPadding %32]]
566 set prefix "test.db${padding}"
567 }
568 } {
569 eval $tcl
570 foreach {tn2 sql} {
571 o {
572 PRAGMA main.synchronous=OFF;
573 PRAGMA aux.synchronous=OFF;
574 PRAGMA journal_mode = DELETE;
575 }
576 o512 {
577 PRAGMA main.synchronous=OFF;
578 PRAGMA aux.synchronous=OFF;
579 PRAGMA main.page_size = 512;
580 PRAGMA aux.page_size = 512;
581 PRAGMA journal_mode = DELETE;
582 }
583 n {
584 PRAGMA main.synchronous=NORMAL;
585 PRAGMA aux.synchronous=NORMAL;
586 PRAGMA journal_mode = DELETE;
587 }
588 f {
589 PRAGMA main.synchronous=FULL;
590 PRAGMA aux.synchronous=FULL;
591 PRAGMA journal_mode = DELETE;
592 }
593 } {
594  
595 set tn "${tn1}.${tn2}"
596  
597 # Set up a connection to have two databases, test.db (main) and
598 # test.db2 (aux). Then run a multi-file transaction on them. The
599 # VFS will snapshot the file-system just before the master-journal
600 # file is deleted to commit the transaction.
601 #
602 tv filter xDelete
603 do_test pager1-4.4.$tn.1 {
604 faultsim_delete_and_reopen $prefix
605 execsql "
606 ATTACH '${prefix}2' AS aux;
607 $sql
608 CREATE TABLE a(x);
609 CREATE TABLE aux.b(x);
610 INSERT INTO a VALUES('double-you');
611 INSERT INTO a VALUES('why');
612 INSERT INTO a VALUES('zed');
613 INSERT INTO b VALUES('won');
614 INSERT INTO b VALUES('too');
615 INSERT INTO b VALUES('free');
616 "
617 execsql {
618 BEGIN;
619 INSERT INTO a SELECT * FROM b WHERE rowid<=3;
620 INSERT INTO b SELECT * FROM a WHERE rowid<=3;
621 COMMIT;
622 }
623 } {}
624 tv filter {}
625  
626 # Check that the transaction was committed successfully.
627 #
628 do_execsql_test pager1-4.4.$tn.2 {
629 SELECT * FROM a
630 } {double-you why zed won too free}
631 do_execsql_test pager1-4.4.$tn.3 {
632 SELECT * FROM b
633 } {won too free double-you why zed}
634  
635 # Restore the file-system and reopen the databases. Check that it now
636 # appears that the transaction was not committed (because the file-system
637 # was restored to the state where it had not been).
638 #
639 do_test pager1-4.4.$tn.4 {
640 faultsim_restore_and_reopen $prefix
641 execsql "ATTACH '${prefix}2' AS aux"
642 } {}
643 do_execsql_test pager1-4.4.$tn.5 {SELECT * FROM a} {double-you why zed}
644 do_execsql_test pager1-4.4.$tn.6 {SELECT * FROM b} {won too free}
645  
646 # Restore the file-system again. This time, before reopening the databases,
647 # delete the master-journal file from the file-system. It now appears that
648 # the transaction was committed (no master-journal file == no rollback).
649 #
650 do_test pager1-4.4.$tn.7 {
651 faultsim_restore_and_reopen $prefix
652 foreach f [glob ${prefix}-mj*] { file delete -force $f }
653 execsql "ATTACH '${prefix}2' AS aux"
654 } {}
655 do_execsql_test pager1-4.4.$tn.8 {
656 SELECT * FROM a
657 } {double-you why zed won too free}
658 do_execsql_test pager1-4.4.$tn.9 {
659 SELECT * FROM b
660 } {won too free double-you why zed}
661 }
662  
663 cd $pwd
664 }
665 db close
666 tv delete
667 file delete -force $dirname
668  
669  
670 # Set up a VFS to make a copy of the file-system just before deleting a
671 # journal file to commit a transaction. The transaction modifies exactly
672 # two database pages (and page 1 - the change counter).
673 #
674 testvfs tv -default 1
675 tv sectorsize 512
676 tv script copy_on_journal_delete
677 tv filter xDelete
678 proc copy_on_journal_delete {method filename args} {
679 if {[string match *journal $filename]} faultsim_save
680 return SQLITE_OK
681 }
682 faultsim_delete_and_reopen
683 do_execsql_test pager1.4.5.1 {
684 PRAGMA journal_mode = DELETE;
685 PRAGMA page_size = 1024;
686 CREATE TABLE t1(a, b);
687 CREATE TABLE t2(a, b);
688 INSERT INTO t1 VALUES('I', 'II');
689 INSERT INTO t2 VALUES('III', 'IV');
690 BEGIN;
691 INSERT INTO t1 VALUES(1, 2);
692 INSERT INTO t2 VALUES(3, 4);
693 COMMIT;
694 } {delete}
695 tv filter {}
696  
697 # Check the transaction was committed:
698 #
699 do_execsql_test pager1.4.5.2 {
700 SELECT * FROM t1;
701 SELECT * FROM t2;
702 } {I II 1 2 III IV 3 4}
703  
704 # Now try four tests:
705 #
706 # pager1-4.5.3: Restore the file-system. Check that the whole transaction
707 # is rolled back.
708 #
709 # pager1-4.5.4: Restore the file-system. Corrupt the first record in the
710 # journal. Check the transaction is not rolled back.
711 #
712 # pager1-4.5.5: Restore the file-system. Corrupt the second record in the
713 # journal. Check that the first record in the transaction is
714 # played back, but not the second.
715 #
716 # pager1-4.5.6: Restore the file-system. Try to open the database with a
717 # readonly connection. This should fail, as a read-only
718 # connection cannot roll back the database file.
719 #
720 faultsim_restore_and_reopen
721 do_execsql_test pager1.4.5.3 {
722 SELECT * FROM t1;
723 SELECT * FROM t2;
724 } {I II III IV}
725 faultsim_restore_and_reopen
726 hexio_write test.db-journal [expr 512+4+1024 - 202] 0123456789ABCDEF
727 do_execsql_test pager1.4.5.4 {
728 SELECT * FROM t1;
729 SELECT * FROM t2;
730 } {I II 1 2 III IV 3 4}
731 faultsim_restore_and_reopen
732 hexio_write test.db-journal [expr 512+4+1024+4+4+1024 - 202] 0123456789ABCDEF
733 do_execsql_test pager1.4.5.5 {
734 SELECT * FROM t1;
735 SELECT * FROM t2;
736 } {I II III IV 3 4}
737  
738 faultsim_restore_and_reopen
739 db close
740 sqlite3 db test.db -readonly 1
741 do_catchsql_test pager1.4.5.6 {
742 SELECT * FROM t1;
743 SELECT * FROM t2;
744 } {1 {disk I/O error}}
745 db close
746  
747 # Snapshot the file-system just before multi-file commit. Save the name
748 # of the master journal file in $::mj_filename.
749 #
750 tv script copy_on_mj_delete
751 tv filter xDelete
752 proc copy_on_mj_delete {method filename args} {
753 if {[string match *mj* [file tail $filename]]} {
754 set ::mj_filename $filename
755 faultsim_save
756 }
757 return SQLITE_OK
758 }
759 do_test pager1.4.6.1 {
760 faultsim_delete_and_reopen
761 execsql {
762 PRAGMA journal_mode = DELETE;
763 ATTACH 'test.db2' AS two;
764 CREATE TABLE t1(a, b);
765 CREATE TABLE two.t2(a, b);
766 INSERT INTO t1 VALUES(1, 't1.1');
767 INSERT INTO t2 VALUES(1, 't2.1');
768 BEGIN;
769 UPDATE t1 SET b = 't1.2';
770 UPDATE t2 SET b = 't2.2';
771 COMMIT;
772 }
773 tv filter {}
774 db close
775 } {}
776  
777 faultsim_restore_and_reopen
778 do_execsql_test pager1.4.6.2 { SELECT * FROM t1 } {1 t1.1}
779 do_test pager1.4.6.3 { file exists $::mj_filename } {1}
780 do_execsql_test pager1.4.6.4 {
781 ATTACH 'test.db2' AS two;
782 SELECT * FROM t2;
783 } {1 t2.1}
784 do_test pager1.4.6.5 { file exists $::mj_filename } {0}
785  
786 faultsim_restore_and_reopen
787 db close
788 do_test pager1.4.6.8 {
789 set ::mj_filename1 $::mj_filename
790 tv filter xDelete
791 sqlite3 db test.db2
792 execsql {
793 PRAGMA journal_mode = DELETE;
794 ATTACH 'test.db3' AS three;
795 CREATE TABLE three.t3(a, b);
796 INSERT INTO t3 VALUES(1, 't3.1');
797 BEGIN;
798 UPDATE t2 SET b = 't2.3';
799 UPDATE t3 SET b = 't3.3';
800 COMMIT;
801 }
802 expr {$::mj_filename1 != $::mj_filename}
803 } {1}
804 faultsim_restore_and_reopen
805 tv filter {}
806  
807 # The file-system now contains:
808 #
809 # * three databases
810 # * three hot-journal files
811 # * two master-journal files.
812 #
813 # The hot-journals associated with test.db2 and test.db3 point to
814 # master journal $::mj_filename. The hot-journal file associated with
815 # test.db points to master journal $::mj_filename1. So reading from
816 # test.db should delete $::mj_filename1.
817 #
818 do_test pager1.4.6.9 {
819 lsort [glob test.db*]
820 } [lsort [list \
821 test.db test.db2 test.db3 \
822 test.db-journal test.db2-journal test.db3-journal \
823 [file tail $::mj_filename] [file tail $::mj_filename1]
824 ]]
825  
826 # The master-journal $::mj_filename1 contains pointers to test.db and
827 # test.db2. However the hot-journal associated with test.db2 points to
828 # a different master-journal. Therefore, reading from test.db only should
829 # be enough to cause SQLite to delete $::mj_filename1.
830 #
831 do_test pager1.4.6.10 { file exists $::mj_filename } {1}
832 do_test pager1.4.6.11 { file exists $::mj_filename1 } {1}
833 do_execsql_test pager1.4.6.12 { SELECT * FROM t1 } {1 t1.1}
834 do_test pager1.4.6.13 { file exists $::mj_filename } {1}
835 do_test pager1.4.6.14 { file exists $::mj_filename1 } {0}
836  
837 do_execsql_test pager1.4.6.12 {
838 ATTACH 'test.db2' AS two;
839 SELECT * FROM t2;
840 } {1 t2.1}
841 do_test pager1.4.6.13 { file exists $::mj_filename } {1}
842 do_execsql_test pager1.4.6.14 {
843 ATTACH 'test.db3' AS three;
844 SELECT * FROM t3;
845 } {1 t3.1}
846 do_test pager1.4.6.15 { file exists $::mj_filename } {0}
847  
848 db close
849 tv delete
850  
851 testvfs tv -default 1
852 tv sectorsize 512
853 tv script copy_on_journal_delete
854 tv filter xDelete
855 proc copy_on_journal_delete {method filename args} {
856 if {[string match *journal $filename]} faultsim_save
857 return SQLITE_OK
858 }
859 faultsim_delete_and_reopen
860 do_execsql_test pager1.4.7.1 {
861 PRAGMA journal_mode = DELETE;
862 CREATE TABLE t1(x PRIMARY KEY, y);
863 CREATE INDEX i1 ON t1(y);
864 INSERT INTO t1 VALUES('I', 'one');
865 INSERT INTO t1 VALUES('II', 'four');
866 INSERT INTO t1 VALUES('III', 'nine');
867 BEGIN;
868 INSERT INTO t1 VALUES('IV', 'sixteen');
869 INSERT INTO t1 VALUES('V' , 'twentyfive');
870 COMMIT;
871 } {delete}
872 tv filter {}
873 db close
874 tv delete
875 do_test pager1.4.7.2 {
876 faultsim_restore_and_reopen
877 catch {file attributes test.db-journal -permissions r--------}
878 catch {file attributes test.db-journal -readonly 1}
879 catchsql { SELECT * FROM t1 }
880 } {1 {unable to open database file}}
881 do_test pager1.4.7.3 {
882 db close
883 catch {file attributes test.db-journal -permissions rw-rw-rw-}
884 catch {file attributes test.db-journal -readonly 0}
885 file delete test.db-journal
886 file exists test.db-journal
887 } {0}
888  
889 #-------------------------------------------------------------------------
890 # The following tests deal with multi-file commits.
891 #
892 # pager1-5.1.*: The case where a multi-file cannot be committed because
893 # another connection is holding a SHARED lock on one of the
894 # files. After the SHARED lock is removed, the COMMIT succeeds.
895 #
896 # pager1-5.2.*: Multi-file commits with journal_mode=memory.
897 #
898 # pager1-5.3.*: Multi-file commits with journal_mode=memory.
899 #
900 # pager1-5.4.*: Check that with synchronous=normal, the master-journal file
901 # name is added to a journal file immediately after the last
902 # journal record. But with synchronous=full, extra unused space
903 # is allocated between the last journal record and the
904 # master-journal file name so that the master-journal file
905 # name does not lie on the same sector as the last journal file
906 # record.
907 #
908 # pager1-5.5.*: Check that in journal_mode=PERSIST mode, a journal file is
909 # truncated to zero bytes when a multi-file transaction is
910 # committed (instead of the first couple of bytes being zeroed).
911 #
912 #
913 do_test pager1-5.1.1 {
914 faultsim_delete_and_reopen
915 execsql {
916 ATTACH 'test.db2' AS aux;
917 CREATE TABLE t1(a, b);
918 CREATE TABLE aux.t2(a, b);
919 INSERT INTO t1 VALUES(17, 'Lenin');
920 INSERT INTO t1 VALUES(22, 'Stalin');
921 INSERT INTO t1 VALUES(53, 'Khrushchev');
922 }
923 } {}
924 do_test pager1-5.1.2 {
925 execsql {
926 BEGIN;
927 INSERT INTO t1 VALUES(64, 'Brezhnev');
928 INSERT INTO t2 SELECT * FROM t1;
929 }
930 sqlite3 db2 test.db2
931 execsql {
932 BEGIN;
933 SELECT * FROM t2;
934 } db2
935 } {}
936 do_test pager1-5.1.3 {
937 catchsql COMMIT
938 } {1 {database is locked}}
939 do_test pager1-5.1.4 {
940 execsql COMMIT db2
941 execsql COMMIT
942 execsql { SELECT * FROM t2 } db2
943 } {17 Lenin 22 Stalin 53 Khrushchev 64 Brezhnev}
944 do_test pager1-5.1.5 {
945 db2 close
946 } {}
947  
948 do_test pager1-5.2.1 {
949 execsql {
950 PRAGMA journal_mode = memory;
951 BEGIN;
952 INSERT INTO t1 VALUES(84, 'Andropov');
953 INSERT INTO t2 VALUES(84, 'Andropov');
954 COMMIT;
955 }
956 } {memory}
957 do_test pager1-5.3.1 {
958 execsql {
959 PRAGMA journal_mode = off;
960 BEGIN;
961 INSERT INTO t1 VALUES(85, 'Gorbachev');
962 INSERT INTO t2 VALUES(85, 'Gorbachev');
963 COMMIT;
964 }
965 } {off}
966  
967 do_test pager1-5.4.1 {
968 db close
969 testvfs tv
970 sqlite3 db test.db -vfs tv
971 execsql { ATTACH 'test.db2' AS aux }
972  
973 tv filter xDelete
974 tv script max_journal_size
975 tv sectorsize 512
976 set ::max_journal 0
977 proc max_journal_size {method args} {
978 set sz 0
979 catch { set sz [file size test.db-journal] }
980 if {$sz > $::max_journal} {
981 set ::max_journal $sz
982 }
983 return SQLITE_OK
984 }
985 execsql {
986 PRAGMA journal_mode = DELETE;
987 PRAGMA synchronous = NORMAL;
988 BEGIN;
989 INSERT INTO t1 VALUES(85, 'Gorbachev');
990 INSERT INTO t2 VALUES(85, 'Gorbachev');
991 COMMIT;
992 }
993 set ::max_journal
994 } [expr 2615+[string length [pwd]]]
995 do_test pager1-5.4.2 {
996 set ::max_journal 0
997 execsql {
998 PRAGMA synchronous = full;
999 BEGIN;
1000 DELETE FROM t1 WHERE b = 'Lenin';
1001 DELETE FROM t2 WHERE b = 'Lenin';
1002 COMMIT;
1003 }
1004 set ::max_journal
1005 } [expr 3111+[string length [pwd]]]
1006 db close
1007 tv delete
1008  
1009 do_test pager1-5.5.1 {
1010 sqlite3 db test.db
1011 execsql {
1012 ATTACH 'test.db2' AS aux;
1013 PRAGMA journal_mode = PERSIST;
1014 CREATE TABLE t3(a, b);
1015 INSERT INTO t3 SELECT randomblob(1500), randomblob(1500) FROM t1;
1016 UPDATE t3 SET b = randomblob(1500);
1017 }
1018 expr [file size test.db-journal] > 15000
1019 } {1}
1020 do_test pager1-5.5.2 {
1021 execsql {
1022 PRAGMA synchronous = full;
1023 BEGIN;
1024 DELETE FROM t1 WHERE b = 'Stalin';
1025 DELETE FROM t2 WHERE b = 'Stalin';
1026 COMMIT;
1027 }
1028 file size test.db-journal
1029 } {0}
1030  
1031  
1032 #-------------------------------------------------------------------------
1033 # The following tests work with "PRAGMA max_page_count"
1034 #
1035 do_test pager1-6.1 {
1036 faultsim_delete_and_reopen
1037 execsql {
1038 PRAGMA auto_vacuum = none;
1039 PRAGMA max_page_count = 10;
1040 CREATE TABLE t2(a, b);
1041 CREATE TABLE t3(a, b);
1042 CREATE TABLE t4(a, b);
1043 CREATE TABLE t5(a, b);
1044 CREATE TABLE t6(a, b);
1045 CREATE TABLE t7(a, b);
1046 CREATE TABLE t8(a, b);
1047 CREATE TABLE t9(a, b);
1048 CREATE TABLE t10(a, b);
1049 }
1050 } {10}
1051 do_catchsql_test pager1-6.2 {
1052 CREATE TABLE t11(a, b)
1053 } {1 {database or disk is full}}
1054 do_execsql_test pager1-6.4 { PRAGMA max_page_count } {10}
1055 do_execsql_test pager1-6.5 { PRAGMA max_page_count = 15 } {15}
1056 do_execsql_test pager1-6.6 { CREATE TABLE t11(a, b) } {}
1057 do_execsql_test pager1-6.7 {
1058 BEGIN;
1059 INSERT INTO t11 VALUES(1, 2);
1060 PRAGMA max_page_count = 13;
1061 } {13}
1062 do_execsql_test pager1-6.8 {
1063 INSERT INTO t11 VALUES(3, 4);
1064 PRAGMA max_page_count = 10;
1065 } {11}
1066 do_execsql_test pager1-6.9 { COMMIT } {}
1067  
1068 do_execsql_test pager1-6.10 { PRAGMA max_page_count = 10 } {11}
1069 do_execsql_test pager1-6.11 { SELECT * FROM t11 } {1 2 3 4}
1070 do_execsql_test pager1-6.12 { PRAGMA max_page_count } {11}
1071  
1072  
1073 #-------------------------------------------------------------------------
1074 # The following tests work with "PRAGMA journal_mode=TRUNCATE" and
1075 # "PRAGMA locking_mode=EXCLUSIVE".
1076 #
1077 # Each test is specified with 5 variables. As follows:
1078 #
1079 # $tn: Test Number. Used as part of the [do_test] test names.
1080 # $sql: SQL to execute.
1081 # $res: Expected result of executing $sql.
1082 # $js: The expected size of the journal file, in bytes, after executing
1083 # the SQL script. Or -1 if the journal is not expected to exist.
1084 # $ws: The expected size of the WAL file, in bytes, after executing
1085 # the SQL script. Or -1 if the WAL is not expected to exist.
1086 #
1087 ifcapable wal {
1088 faultsim_delete_and_reopen
1089 foreach {tn sql res js ws} [subst {
1090  
1091 1 {
1092 CREATE TABLE t1(a, b);
1093 PRAGMA auto_vacuum=OFF;
1094 PRAGMA synchronous=NORMAL;
1095 PRAGMA page_size=1024;
1096 PRAGMA locking_mode=EXCLUSIVE;
1097 PRAGMA journal_mode=TRUNCATE;
1098 INSERT INTO t1 VALUES(1, 2);
1099 } {exclusive truncate} 0 -1
1100  
1101 2 {
1102 BEGIN IMMEDIATE;
1103 SELECT * FROM t1;
1104 COMMIT;
1105 } {1 2} 0 -1
1106  
1107 3 {
1108 BEGIN;
1109 SELECT * FROM t1;
1110 COMMIT;
1111 } {1 2} 0 -1
1112  
1113 4 { PRAGMA journal_mode = WAL } wal -1 -1
1114 5 { INSERT INTO t1 VALUES(3, 4) } {} -1 [wal_file_size 1 1024]
1115 6 { PRAGMA locking_mode = NORMAL } exclusive -1 [wal_file_size 1 1024]
1116 7 { INSERT INTO t1 VALUES(5, 6); } {} -1 [wal_file_size 2 1024]
1117  
1118 8 { PRAGMA journal_mode = TRUNCATE } truncate 0 -1
1119 9 { INSERT INTO t1 VALUES(7, 8) } {} 0 -1
1120 10 { SELECT * FROM t1 } {1 2 3 4 5 6 7 8} 0 -1
1121  
1122 }] {
1123 do_execsql_test pager1-7.1.$tn.1 $sql $res
1124 catch { set J -1 ; set J [file size test.db-journal] }
1125 catch { set W -1 ; set W [file size test.db-wal] }
1126 do_test pager1-7.1.$tn.2 { list $J $W } [list $js $ws]
1127 }
1128 }
1129  
1130 do_test pager1-7.2.1 {
1131 faultsim_delete_and_reopen
1132 execsql {
1133 PRAGMA locking_mode = EXCLUSIVE;
1134 CREATE TABLE t1(a, b);
1135 BEGIN;
1136 PRAGMA journal_mode = delete;
1137 PRAGMA journal_mode = truncate;
1138 }
1139 } {exclusive delete truncate}
1140 do_test pager1-7.2.2 {
1141 execsql { INSERT INTO t1 VALUES(1, 2) }
1142 execsql { PRAGMA journal_mode = persist }
1143 } {truncate}
1144 do_test pager1-7.2.3 {
1145 execsql { COMMIT }
1146 execsql {
1147 PRAGMA journal_mode = persist;
1148 PRAGMA journal_size_limit;
1149 }
1150 } {persist -1}
1151  
1152 #-------------------------------------------------------------------------
1153 # The following tests, pager1-8.*, test that the special filenames
1154 # ":memory:" and "" open temporary databases.
1155 #
1156 foreach {tn filename} {
1157 1 :memory:
1158 2 ""
1159 } {
1160 do_test pager1-8.$tn.1 {
1161 faultsim_delete_and_reopen
1162 db close
1163 sqlite3 db $filename
1164 execsql {
1165 PRAGMA auto_vacuum = 1;
1166 CREATE TABLE x1(x);
1167 INSERT INTO x1 VALUES('Charles');
1168 INSERT INTO x1 VALUES('James');
1169 INSERT INTO x1 VALUES('Mary');
1170 SELECT * FROM x1;
1171 }
1172 } {Charles James Mary}
1173  
1174 do_test pager1-8.$tn.2 {
1175 sqlite3 db2 $filename
1176 catchsql { SELECT * FROM x1 } db2
1177 } {1 {no such table: x1}}
1178  
1179 do_execsql_test pager1-8.$tn.3 {
1180 BEGIN;
1181 INSERT INTO x1 VALUES('William');
1182 INSERT INTO x1 VALUES('Anne');
1183 ROLLBACK;
1184 } {}
1185 }
1186  
1187 #-------------------------------------------------------------------------
1188 # The next block of tests - pager1-9.* - deal with interactions between
1189 # the pager and the backup API. Test cases:
1190 #
1191 # pager1-9.1.*: Test that a backup completes successfully even if the
1192 # source db is written to during the backup op.
1193 #
1194 # pager1-9.2.*: Test that a backup completes successfully even if the
1195 # source db is written to and then rolled back during a
1196 # backup operation.
1197 #
1198 do_test pager1-9.0.1 {
1199 faultsim_delete_and_reopen
1200 db func a_string a_string
1201 execsql {
1202 PRAGMA cache_size = 10;
1203 BEGIN;
1204 CREATE TABLE ab(a, b, UNIQUE(a, b));
1205 INSERT INTO ab VALUES( a_string(200), a_string(300) );
1206 INSERT INTO ab SELECT a_string(200), a_string(300) FROM ab;
1207 INSERT INTO ab SELECT a_string(200), a_string(300) FROM ab;
1208 INSERT INTO ab SELECT a_string(200), a_string(300) FROM ab;
1209 INSERT INTO ab SELECT a_string(200), a_string(300) FROM ab;
1210 INSERT INTO ab SELECT a_string(200), a_string(300) FROM ab;
1211 INSERT INTO ab SELECT a_string(200), a_string(300) FROM ab;
1212 INSERT INTO ab SELECT a_string(200), a_string(300) FROM ab;
1213 COMMIT;
1214 }
1215 } {}
1216 do_test pager1-9.0.2 {
1217 sqlite3 db2 test.db2
1218 db2 eval { PRAGMA cache_size = 10 }
1219 sqlite3_backup B db2 main db main
1220 list [B step 10000] [B finish]
1221 } {SQLITE_DONE SQLITE_OK}
1222 do_test pager1-9.0.3 {
1223 db one {SELECT md5sum(a, b) FROM ab}
1224 } [db2 one {SELECT md5sum(a, b) FROM ab}]
1225  
1226 do_test pager1-9.1.1 {
1227 execsql { UPDATE ab SET a = a_string(201) }
1228 sqlite3_backup B db2 main db main
1229 B step 30
1230 } {SQLITE_OK}
1231 do_test pager1-9.1.2 {
1232 execsql { UPDATE ab SET b = a_string(301) }
1233 list [B step 10000] [B finish]
1234 } {SQLITE_DONE SQLITE_OK}
1235 do_test pager1-9.1.3 {
1236 db one {SELECT md5sum(a, b) FROM ab}
1237 } [db2 one {SELECT md5sum(a, b) FROM ab}]
1238 do_test pager1-9.1.4 { execsql { SELECT count(*) FROM ab } } {128}
1239  
1240 do_test pager1-9.2.1 {
1241 execsql { UPDATE ab SET a = a_string(202) }
1242 sqlite3_backup B db2 main db main
1243 B step 30
1244 } {SQLITE_OK}
1245 do_test pager1-9.2.2 {
1246 execsql {
1247 BEGIN;
1248 UPDATE ab SET b = a_string(301);
1249 ROLLBACK;
1250 }
1251 list [B step 10000] [B finish]
1252 } {SQLITE_DONE SQLITE_OK}
1253 do_test pager1-9.2.3 {
1254 db one {SELECT md5sum(a, b) FROM ab}
1255 } [db2 one {SELECT md5sum(a, b) FROM ab}]
1256 do_test pager1-9.2.4 { execsql { SELECT count(*) FROM ab } } {128}
1257 db close
1258 db2 close
1259  
1260 do_test pager1-9.3.1 {
1261 testvfs tv -default 1
1262 tv sectorsize 4096
1263 faultsim_delete_and_reopen
1264  
1265 execsql { PRAGMA page_size = 1024 }
1266 for {set ii 0} {$ii < 4} {incr ii} { execsql "CREATE TABLE t${ii}(a, b)" }
1267 } {}
1268 do_test pager1-9.3.2 {
1269 sqlite3 db2 test.db2
1270  
1271 execsql {
1272 PRAGMA page_size = 4096;
1273 PRAGMA synchronous = OFF;
1274 CREATE TABLE t1(a, b);
1275 CREATE TABLE t2(a, b);
1276 } db2
1277  
1278 sqlite3_backup B db2 main db main
1279 B step 30
1280 list [B step 10000] [B finish]
1281 } {SQLITE_DONE SQLITE_OK}
1282 do_test pager1-9.3.3 {
1283 db2 close
1284 db close
1285 tv delete
1286 file size test.db2
1287 } [file size test.db]
1288  
1289 do_test pager1-9.4.1 {
1290 faultsim_delete_and_reopen
1291 sqlite3 db2 test.db2
1292 execsql {
1293 PRAGMA page_size = 4096;
1294 CREATE TABLE t1(a, b);
1295 CREATE TABLE t2(a, b);
1296 } db2
1297 sqlite3_backup B db2 main db main
1298 list [B step 10000] [B finish]
1299 } {SQLITE_DONE SQLITE_OK}
1300 do_test pager1-9.4.2 {
1301 list [file size test.db2] [file size test.db]
1302 } {0 0}
1303 db2 close
1304  
1305 #-------------------------------------------------------------------------
1306 # Test that regardless of the value returned by xSectorSize(), the
1307 # minimum effective sector-size is 512 and the maximum 65536 bytes.
1308 #
1309 testvfs tv -default 1
1310 foreach sectorsize {
1311 32 64 128 256 512 1024 2048
1312 4096 8192 16384 32768 65536 131072 262144
1313 } {
1314 tv sectorsize $sectorsize
1315 set eff $sectorsize
1316 if {$sectorsize < 512} { set eff 512 }
1317 if {$sectorsize > 65536} { set eff 65536 }
1318  
1319 do_test pager1-10.$sectorsize.1 {
1320 faultsim_delete_and_reopen
1321 db func a_string a_string
1322 execsql {
1323 PRAGMA journal_mode = PERSIST;
1324 PRAGMA page_size = 1024;
1325 BEGIN;
1326 CREATE TABLE t1(a, b);
1327 CREATE TABLE t2(a, b);
1328 CREATE TABLE t3(a, b);
1329 COMMIT;
1330 }
1331 file size test.db-journal
1332 } [expr $sectorsize > 65536 ? 65536 : $sectorsize]
1333  
1334 do_test pager1-10.$sectorsize.2 {
1335 execsql {
1336 INSERT INTO t3 VALUES(a_string(300), a_string(300));
1337 INSERT INTO t3 SELECT * FROM t3; /* 2 */
1338 INSERT INTO t3 SELECT * FROM t3; /* 4 */
1339 INSERT INTO t3 SELECT * FROM t3; /* 8 */
1340 INSERT INTO t3 SELECT * FROM t3; /* 16 */
1341 INSERT INTO t3 SELECT * FROM t3; /* 32 */
1342 }
1343 } {}
1344  
1345 do_test pager1-10.$sectorsize.3 {
1346 db close
1347 sqlite3 db test.db
1348 execsql {
1349 PRAGMA cache_size = 10;
1350 BEGIN;
1351 }
1352 recursive_select 32 t3 {db eval "INSERT INTO t2 VALUES(1, 2)"}
1353 execsql {
1354 COMMIT;
1355 SELECT * FROM t2;
1356 }
1357 } {1 2}
1358  
1359 do_test pager1-10.$sectorsize.4 {
1360 execsql {
1361 CREATE TABLE t6(a, b);
1362 CREATE TABLE t7(a, b);
1363 CREATE TABLE t5(a, b);
1364 DROP TABLE t6;
1365 DROP TABLE t7;
1366 }
1367 execsql {
1368 BEGIN;
1369 CREATE TABLE t6(a, b);
1370 }
1371 recursive_select 32 t3 {db eval "INSERT INTO t5 VALUES(1, 2)"}
1372 execsql {
1373 COMMIT;
1374 SELECT * FROM t5;
1375 }
1376 } {1 2}
1377  
1378 }
1379 db close
1380  
1381 tv sectorsize 4096
1382 do_test pager1.10.x.1 {
1383 faultsim_delete_and_reopen
1384 execsql {
1385 PRAGMA auto_vacuum = none;
1386 PRAGMA page_size = 1024;
1387 CREATE TABLE t1(x);
1388 }
1389 for {set i 0} {$i<30} {incr i} {
1390 execsql { INSERT INTO t1 VALUES(zeroblob(900)) }
1391 }
1392 file size test.db
1393 } {32768}
1394 do_test pager1.10.x.2 {
1395 execsql {
1396 CREATE TABLE t2(x);
1397 DROP TABLE t2;
1398 }
1399 file size test.db
1400 } {33792}
1401 do_test pager1.10.x.3 {
1402 execsql {
1403 BEGIN;
1404 CREATE TABLE t2(x);
1405 }
1406 recursive_select 30 t1
1407 execsql {
1408 CREATE TABLE t3(x);
1409 COMMIT;
1410 }
1411 } {}
1412  
1413 db close
1414 tv delete
1415  
1416 testvfs tv -default 1
1417 faultsim_delete_and_reopen
1418 db func a_string a_string
1419 do_execsql_test pager1-11.1 {
1420 PRAGMA journal_mode = DELETE;
1421 PRAGMA cache_size = 10;
1422 BEGIN;
1423 CREATE TABLE zz(top PRIMARY KEY);
1424 INSERT INTO zz VALUES(a_string(222));
1425 INSERT INTO zz SELECT a_string((SELECT 222+max(rowid) FROM zz)) FROM zz;
1426 INSERT INTO zz SELECT a_string((SELECT 222+max(rowid) FROM zz)) FROM zz;
1427 INSERT INTO zz SELECT a_string((SELECT 222+max(rowid) FROM zz)) FROM zz;
1428 INSERT INTO zz SELECT a_string((SELECT 222+max(rowid) FROM zz)) FROM zz;
1429 INSERT INTO zz SELECT a_string((SELECT 222+max(rowid) FROM zz)) FROM zz;
1430 COMMIT;
1431 BEGIN;
1432 UPDATE zz SET top = a_string(345);
1433 } {delete}
1434  
1435 proc lockout {method args} { return SQLITE_IOERR }
1436 tv script lockout
1437 tv filter {xWrite xTruncate xSync}
1438 do_catchsql_test pager1-11.2 { COMMIT } {1 {disk I/O error}}
1439  
1440 tv script {}
1441 do_test pager1-11.3 {
1442 sqlite3 db2 test.db
1443 execsql {
1444 PRAGMA journal_mode = TRUNCATE;
1445 PRAGMA integrity_check;
1446 } db2
1447 } {truncate ok}
1448 do_test pager1-11.4 {
1449 db2 close
1450 file exists test.db-journal
1451 } {0}
1452 do_execsql_test pager1-11.5 { SELECT count(*) FROM zz } {32}
1453 db close
1454 tv delete
1455  
1456 #-------------------------------------------------------------------------
1457 # Test "PRAGMA page_size"
1458 #
1459 testvfs tv -default 1
1460 tv sectorsize 1024
1461 foreach pagesize {
1462 512 1024 2048 4096 8192 16384 32768
1463 } {
1464 faultsim_delete_and_reopen
1465  
1466 # The sector-size (according to the VFS) is 1024 bytes. So if the
1467 # page-size requested using "PRAGMA page_size" is greater than the
1468 # compile time value of SQLITE_MAX_PAGE_SIZE, then the effective
1469 # page-size remains 1024 bytes.
1470 #
1471 set eff $pagesize
1472 if {$eff > $::SQLITE_MAX_PAGE_SIZE} { set eff 1024 }
1473  
1474 do_test pager1-12.$pagesize.1 {
1475 sqlite3 db2 test.db
1476 execsql "
1477 PRAGMA page_size = $pagesize;
1478 CREATE VIEW v AS SELECT * FROM sqlite_master;
1479 " db2
1480 file size test.db
1481 } $eff
1482 do_test pager1-12.$pagesize.2 {
1483 sqlite3 db2 test.db
1484 execsql {
1485 SELECT count(*) FROM v;
1486 PRAGMA main.page_size;
1487 } db2
1488 } [list 1 $eff]
1489 do_test pager1-12.$pagesize.3 {
1490 execsql {
1491 SELECT count(*) FROM v;
1492 PRAGMA main.page_size;
1493 }
1494 } [list 1 $eff]
1495 db2 close
1496 }
1497 db close
1498 tv delete
1499  
1500 #-------------------------------------------------------------------------
1501 # Test specal "PRAGMA journal_mode=PERSIST" test cases.
1502 #
1503 # pager1-13.1.*: This tests a special case encountered in persistent
1504 # journal mode: If the journal associated with a transaction
1505 # is smaller than the journal file (because a previous
1506 # transaction left a very large non-hot journal file in the
1507 # file-system), then SQLite has to be careful that there is
1508 # not a journal-header left over from a previous transaction
1509 # immediately following the journal content just written.
1510 # If there is, and the process crashes so that the journal
1511 # becomes a hot-journal and must be rolled back by another
1512 # process, there is a danger that the other process may roll
1513 # back the aborted transaction, then continue copying data
1514 # from an older transaction from the remainder of the journal.
1515 # See the syncJournal() function for details.
1516 #
1517 # pager1-13.2.*: Same test as the previous. This time, throw an index into
1518 # the mix to make the integrity-check more likely to catch
1519 # errors.
1520 #
1521 testvfs tv -default 1
1522 tv script xSyncCb
1523 tv filter xSync
1524 proc xSyncCb {method filename args} {
1525 set t [file tail $filename]
1526 if {$t == "test.db"} faultsim_save
1527 return SQLITE_OK
1528 }
1529 faultsim_delete_and_reopen
1530 db func a_string a_string
1531  
1532 # The UPDATE statement at the end of this test case creates a really big
1533 # journal. Since the cache-size is only 10 pages, the journal contains
1534 # frequent journal headers.
1535 #
1536 do_execsql_test pager1-13.1.1 {
1537 PRAGMA page_size = 1024;
1538 PRAGMA journal_mode = PERSIST;
1539 PRAGMA cache_size = 10;
1540 BEGIN;
1541 CREATE TABLE t1(a INTEGER PRIMARY KEY, b BLOB);
1542 INSERT INTO t1 VALUES(NULL, a_string(400));
1543 INSERT INTO t1 SELECT NULL, a_string(400) FROM t1; /* 2 */
1544 INSERT INTO t1 SELECT NULL, a_string(400) FROM t1; /* 4 */
1545 INSERT INTO t1 SELECT NULL, a_string(400) FROM t1; /* 8 */
1546 INSERT INTO t1 SELECT NULL, a_string(400) FROM t1; /* 16 */
1547 INSERT INTO t1 SELECT NULL, a_string(400) FROM t1; /* 32 */
1548 INSERT INTO t1 SELECT NULL, a_string(400) FROM t1; /* 64 */
1549 INSERT INTO t1 SELECT NULL, a_string(400) FROM t1; /* 128 */
1550 COMMIT;
1551 UPDATE t1 SET b = a_string(400);
1552 } {persist}
1553  
1554 if {$::tcl_platform(platform)!="windows"} {
1555 # Run transactions of increasing sizes. Eventually, one (or more than one)
1556 # of these will write just enough content that one of the old headers created
1557 # by the transaction in the block above lies immediately after the content
1558 # journalled by the current transaction.
1559 #
1560 for {set nUp 1} {$nUp<64} {incr nUp} {
1561 do_execsql_test pager1-13.1.2.$nUp.1 {
1562 UPDATE t1 SET b = a_string(399) WHERE a <= $nUp
1563 } {}
1564 do_execsql_test pager1-13.1.2.$nUp.2 { PRAGMA integrity_check } {ok}
1565  
1566 # Try to access the snapshot of the file-system.
1567 #
1568 sqlite3 db2 sv_test.db
1569 do_test pager1-13.1.2.$nUp.3 {
1570 execsql { SELECT sum(length(b)) FROM t1 } db2
1571 } [expr {128*400 - ($nUp-1)}]
1572 do_test pager1-13.1.2.$nUp.4 {
1573 execsql { PRAGMA integrity_check } db2
1574 } {ok}
1575 db2 close
1576 }
1577 }
1578  
1579 if {$::tcl_platform(platform)!="windows"} {
1580 # Same test as above. But this time with an index on the table.
1581 #
1582 do_execsql_test pager1-13.2.1 {
1583 CREATE INDEX i1 ON t1(b);
1584 UPDATE t1 SET b = a_string(400);
1585 } {}
1586 for {set nUp 1} {$nUp<64} {incr nUp} {
1587 do_execsql_test pager1-13.2.2.$nUp.1 {
1588 UPDATE t1 SET b = a_string(399) WHERE a <= $nUp
1589 } {}
1590 do_execsql_test pager1-13.2.2.$nUp.2 { PRAGMA integrity_check } {ok}
1591 sqlite3 db2 sv_test.db
1592 do_test pager1-13.2.2.$nUp.3 {
1593 execsql { SELECT sum(length(b)) FROM t1 } db2
1594 } [expr {128*400 - ($nUp-1)}]
1595 do_test pager1-13.2.2.$nUp.4 {
1596 execsql { PRAGMA integrity_check } db2
1597 } {ok}
1598 db2 close
1599 }
1600 }
1601  
1602 db close
1603 tv delete
1604  
1605 #-------------------------------------------------------------------------
1606 # Test specal "PRAGMA journal_mode=OFF" test cases.
1607 #
1608 faultsim_delete_and_reopen
1609 do_execsql_test pager1-14.1.1 {
1610 PRAGMA journal_mode = OFF;
1611 CREATE TABLE t1(a, b);
1612 BEGIN;
1613 INSERT INTO t1 VALUES(1, 2);
1614 COMMIT;
1615 SELECT * FROM t1;
1616 } {off 1 2}
1617 do_catchsql_test pager1-14.1.2 {
1618 BEGIN;
1619 INSERT INTO t1 VALUES(3, 4);
1620 ROLLBACK;
1621 } {0 {}}
1622 do_execsql_test pager1-14.1.3 {
1623 SELECT * FROM t1;
1624 } {1 2}
1625 do_catchsql_test pager1-14.1.4 {
1626 BEGIN;
1627 INSERT INTO t1(rowid, a, b) SELECT a+3, b, b FROM t1;
1628 INSERT INTO t1(rowid, a, b) SELECT a+3, b, b FROM t1;
1629 } {1 {PRIMARY KEY must be unique}}
1630 do_execsql_test pager1-14.1.5 {
1631 COMMIT;
1632 SELECT * FROM t1;
1633 } {1 2 2 2}
1634  
1635 #-------------------------------------------------------------------------
1636 # Test opening and closing the pager sub-system with different values
1637 # for the sqlite3_vfs.szOsFile variable.
1638 #
1639 faultsim_delete_and_reopen
1640 do_execsql_test pager1-15.0 {
1641 CREATE TABLE tx(y, z);
1642 INSERT INTO tx VALUES('Ayutthaya', 'Beijing');
1643 INSERT INTO tx VALUES('London', 'Tokyo');
1644 } {}
1645 db close
1646 for {set i 0} {$i<513} {incr i 3} {
1647 testvfs tv -default 1 -szosfile $i
1648 sqlite3 db test.db
1649 do_execsql_test pager1-15.$i.1 {
1650 SELECT * FROM tx;
1651 } {Ayutthaya Beijing London Tokyo}
1652 db close
1653 tv delete
1654 }
1655  
1656 #-------------------------------------------------------------------------
1657 # Check that it is not possible to open a database file if the full path
1658 # to the associated journal file will be longer than sqlite3_vfs.mxPathname.
1659 #
1660 testvfs tv -default 1
1661 tv script xOpenCb
1662 tv filter xOpen
1663 proc xOpenCb {method filename args} {
1664 set ::file_len [string length $filename]
1665 }
1666 sqlite3 db test.db
1667 db close
1668 tv delete
1669  
1670 for {set ii [expr $::file_len-5]} {$ii < [expr $::file_len+20]} {incr ii} {
1671 testvfs tv -default 1 -mxpathname $ii
1672  
1673 # The length of the full path to file "test.db-journal" is ($::file_len+8).
1674 # If the configured sqlite3_vfs.mxPathname value greater than or equal to
1675 # this, then the file can be opened. Otherwise, it cannot.
1676 #
1677 if {$ii >= [expr $::file_len+8]} {
1678 set res {0 {}}
1679 } else {
1680 set res {1 {unable to open database file}}
1681 }
1682  
1683 do_test pager1-16.1.$ii {
1684 list [catch { sqlite3 db test.db } msg] $msg
1685 } $res
1686  
1687 catch {db close}
1688 tv delete
1689 }
1690  
1691 #-------------------------------------------------------------------------
1692 # Test "PRAGMA omit_readlock".
1693 #
1694 # pager1-17.$tn.1.*: Test that if a second connection has an open
1695 # read-transaction, it is not usually possible to write
1696 # the database.
1697 #
1698 # pager1-17.$tn.2.*: Test that if the second connection was opened with
1699 # the SQLITE_OPEN_READONLY flag, and
1700 # "PRAGMA omit_readlock = 1" is executed before attaching
1701 # the database and opening a read-transaction on it, it is
1702 # possible to write the db.
1703 #
1704 # pager1-17.$tn.3.*: Test that if the second connection was *not* opened with
1705 # the SQLITE_OPEN_READONLY flag, executing
1706 # "PRAGMA omit_readlock = 1" has no effect.
1707 #
1708 do_multiclient_test tn {
1709 do_test pager1-17.$tn.1.1 {
1710 sql1 {
1711 CREATE TABLE t1(a, b);
1712 INSERT INTO t1 VALUES(1, 2);
1713 }
1714 sql2 {
1715 BEGIN;
1716 SELECT * FROM t1;
1717 }
1718 } {1 2}
1719 do_test pager1-17.$tn.1.2 {
1720 csql1 { INSERT INTO t1 VALUES(3, 4) }
1721 } {1 {database is locked}}
1722 do_test pager1-17.$tn.1.3 {
1723 sql2 { COMMIT }
1724 sql1 { INSERT INTO t1 VALUES(3, 4) }
1725 } {}
1726  
1727 do_test pager1-17.$tn.2.1 {
1728 code2 {
1729 db2 close
1730 sqlite3 db2 :memory: -readonly 1
1731 }
1732 sql2 {
1733 PRAGMA omit_readlock = 1;
1734 ATTACH 'test.db' AS two;
1735 BEGIN;
1736 SELECT * FROM t1;
1737 }
1738 } {1 2 3 4}
1739 do_test pager1-17.$tn.2.2 { sql1 "INSERT INTO t1 VALUES(5, 6)" } {}
1740 do_test pager1-17.$tn.2.3 { sql2 "SELECT * FROM t1" } {1 2 3 4}
1741 do_test pager1-17.$tn.2.4 { sql2 "COMMIT ; SELECT * FROM t1" } {1 2 3 4 5 6}
1742  
1743 do_test pager1-17.$tn.3.1 {
1744 code2 {
1745 db2 close
1746 sqlite3 db2 :memory:
1747 }
1748 sql2 {
1749 PRAGMA omit_readlock = 1;
1750 ATTACH 'test.db' AS two;
1751 BEGIN;
1752 SELECT * FROM t1;
1753 }
1754 } {1 2 3 4 5 6}
1755 do_test pager1-17.$tn.3.2 {
1756 csql1 { INSERT INTO t1 VALUES(3, 4) }
1757 } {1 {database is locked}}
1758 do_test pager1-17.$tn.3.3 { sql2 COMMIT } {}
1759 }
1760  
1761 #-------------------------------------------------------------------------
1762 # Test the pagers response to the b-tree layer requesting illegal page
1763 # numbers:
1764 #
1765 # + The locking page,
1766 # + Page 0,
1767 # + A page with a page number greater than (2^31-1).
1768 #
1769 do_test pager1-18.1 {
1770 faultsim_delete_and_reopen
1771 db func a_string a_string
1772 execsql {
1773 PRAGMA page_size = 1024;
1774 CREATE TABLE t1(a, b);
1775 INSERT INTO t1 VALUES(a_string(500), a_string(200));
1776 INSERT INTO t1 SELECT a_string(500), a_string(200) FROM t1;
1777 INSERT INTO t1 SELECT a_string(500), a_string(200) FROM t1;
1778 INSERT INTO t1 SELECT a_string(500), a_string(200) FROM t1;
1779 INSERT INTO t1 SELECT a_string(500), a_string(200) FROM t1;
1780 INSERT INTO t1 SELECT a_string(500), a_string(200) FROM t1;
1781 INSERT INTO t1 SELECT a_string(500), a_string(200) FROM t1;
1782 INSERT INTO t1 SELECT a_string(500), a_string(200) FROM t1;
1783 }
1784 } {}
1785 do_test pager1-18.2 {
1786 set root [db one "SELECT rootpage FROM sqlite_master"]
1787 set lockingpage [expr (0x10000/1024) + 1]
1788 execsql {
1789 PRAGMA writable_schema = 1;
1790 UPDATE sqlite_master SET rootpage = $lockingpage;
1791 }
1792 sqlite3 db2 test.db
1793 catchsql { SELECT count(*) FROM t1 } db2
1794 } {1 {database disk image is malformed}}
1795 db2 close
1796 do_test pager1-18.3 {
1797 execsql {
1798 CREATE TABLE t2(x);
1799 INSERT INTO t2 VALUES(a_string(5000));
1800 }
1801 set pgno [expr ([file size test.db] / 1024)-2]
1802 hexio_write test.db [expr ($pgno-1)*1024] 00000000
1803 sqlite3 db2 test.db
1804 catchsql { SELECT length(x) FROM t2 } db2
1805 } {1 {database disk image is malformed}}
1806 db2 close
1807 do_test pager1-18.4 {
1808 hexio_write test.db [expr ($pgno-1)*1024] 90000000
1809 sqlite3 db2 test.db
1810 catchsql { SELECT length(x) FROM t2 } db2
1811 } {1 {database disk image is malformed}}
1812 db2 close
1813 do_test pager1-18.5 {
1814 sqlite3 db ""
1815 execsql {
1816 CREATE TABLE t1(a, b);
1817 CREATE TABLE t2(a, b);
1818 PRAGMA writable_schema = 1;
1819 UPDATE sqlite_master SET rootpage=5 WHERE tbl_name = 't1';
1820 PRAGMA writable_schema = 0;
1821 ALTER TABLE t1 RENAME TO x1;
1822 }
1823 catchsql { SELECT * FROM x1 }
1824 } {1 {database disk image is malformed}}
1825 db close
1826  
1827 do_test pager1-18.6 {
1828 faultsim_delete_and_reopen
1829 db func a_string a_string
1830 execsql {
1831 PRAGMA page_size = 1024;
1832 CREATE TABLE t1(x);
1833 INSERT INTO t1 VALUES(a_string(800));
1834 INSERT INTO t1 VALUES(a_string(800));
1835 }
1836  
1837 set root [db one "SELECT rootpage FROM sqlite_master"]
1838 db close
1839  
1840 hexio_write test.db [expr ($root-1)*1024 + 8] 00000000
1841 sqlite3 db test.db
1842 catchsql { SELECT length(x) FROM t1 }
1843 } {1 {database disk image is malformed}}
1844  
1845 do_test pager1-19.1 {
1846 sqlite3 db ""
1847 db func a_string a_string
1848 execsql {
1849 PRAGMA page_size = 512;
1850 PRAGMA auto_vacuum = 1;
1851 CREATE TABLE t1(aa, ab, ac, ad, ae, af, ag, ah, ai, aj, ak, al, am, an,
1852 ba, bb, bc, bd, be, bf, bg, bh, bi, bj, bk, bl, bm, bn,
1853 ca, cb, cc, cd, ce, cf, cg, ch, ci, cj, ck, cl, cm, cn,
1854 da, db, dc, dd, de, df, dg, dh, di, dj, dk, dl, dm, dn,
1855 ea, eb, ec, ed, ee, ef, eg, eh, ei, ej, ek, el, em, en,
1856 fa, fb, fc, fd, fe, ff, fg, fh, fi, fj, fk, fl, fm, fn,
1857 ga, gb, gc, gd, ge, gf, gg, gh, gi, gj, gk, gl, gm, gn,
1858 ha, hb, hc, hd, he, hf, hg, hh, hi, hj, hk, hl, hm, hn,
1859 ia, ib, ic, id, ie, if, ig, ih, ii, ij, ik, il, im, ix,
1860 ja, jb, jc, jd, je, jf, jg, jh, ji, jj, jk, jl, jm, jn,
1861 ka, kb, kc, kd, ke, kf, kg, kh, ki, kj, kk, kl, km, kn,
1862 la, lb, lc, ld, le, lf, lg, lh, li, lj, lk, ll, lm, ln,
1863 ma, mb, mc, md, me, mf, mg, mh, mi, mj, mk, ml, mm, mn
1864 );
1865 CREATE TABLE t2(aa, ab, ac, ad, ae, af, ag, ah, ai, aj, ak, al, am, an,
1866 ba, bb, bc, bd, be, bf, bg, bh, bi, bj, bk, bl, bm, bn,
1867 ca, cb, cc, cd, ce, cf, cg, ch, ci, cj, ck, cl, cm, cn,
1868 da, db, dc, dd, de, df, dg, dh, di, dj, dk, dl, dm, dn,
1869 ea, eb, ec, ed, ee, ef, eg, eh, ei, ej, ek, el, em, en,
1870 fa, fb, fc, fd, fe, ff, fg, fh, fi, fj, fk, fl, fm, fn,
1871 ga, gb, gc, gd, ge, gf, gg, gh, gi, gj, gk, gl, gm, gn,
1872 ha, hb, hc, hd, he, hf, hg, hh, hi, hj, hk, hl, hm, hn,
1873 ia, ib, ic, id, ie, if, ig, ih, ii, ij, ik, il, im, ix,
1874 ja, jb, jc, jd, je, jf, jg, jh, ji, jj, jk, jl, jm, jn,
1875 ka, kb, kc, kd, ke, kf, kg, kh, ki, kj, kk, kl, km, kn,
1876 la, lb, lc, ld, le, lf, lg, lh, li, lj, lk, ll, lm, ln,
1877 ma, mb, mc, md, me, mf, mg, mh, mi, mj, mk, ml, mm, mn
1878 );
1879 INSERT INTO t1(aa) VALUES( a_string(100000) );
1880 INSERT INTO t2(aa) VALUES( a_string(100000) );
1881 VACUUM;
1882 }
1883 } {}
1884  
1885 #-------------------------------------------------------------------------
1886 # Test a couple of special cases that come up while committing
1887 # transactions:
1888 #
1889 # pager1-20.1.*: Committing an in-memory database transaction when the
1890 # database has not been modified at all.
1891 #
1892 # pager1-20.2.*: As above, but with a normal db in exclusive-locking mode.
1893 #
1894 # pager1-20.3.*: Committing a transaction in WAL mode where the database has
1895 # been modified, but all dirty pages have been flushed to
1896 # disk before the commit.
1897 #
1898 do_test pager1-20.1.1 {
1899 catch {db close}
1900 sqlite3 db :memory:
1901 execsql {
1902 CREATE TABLE one(two, three);
1903 INSERT INTO one VALUES('a', 'b');
1904 }
1905 } {}
1906 do_test pager1-20.1.2 {
1907 execsql {
1908 BEGIN EXCLUSIVE;
1909 COMMIT;
1910 }
1911 } {}
1912  
1913 do_test pager1-20.2.1 {
1914 faultsim_delete_and_reopen
1915 execsql {
1916 PRAGMA locking_mode = exclusive;
1917 PRAGMA journal_mode = persist;
1918 CREATE TABLE one(two, three);
1919 INSERT INTO one VALUES('a', 'b');
1920 }
1921 } {exclusive persist}
1922 do_test pager1-20.2.2 {
1923 execsql {
1924 BEGIN EXCLUSIVE;
1925 COMMIT;
1926 }
1927 } {}
1928  
1929 ifcapable wal {
1930 do_test pager1-20.3.1 {
1931 faultsim_delete_and_reopen
1932 db func a_string a_string
1933 execsql {
1934 PRAGMA cache_size = 10;
1935 PRAGMA journal_mode = wal;
1936 BEGIN;
1937 CREATE TABLE t1(x);
1938 CREATE TABLE t2(y);
1939 INSERT INTO t1 VALUES(a_string(800));
1940 INSERT INTO t1 SELECT a_string(800) FROM t1; /* 2 */
1941 INSERT INTO t1 SELECT a_string(800) FROM t1; /* 4 */
1942 INSERT INTO t1 SELECT a_string(800) FROM t1; /* 8 */
1943 INSERT INTO t1 SELECT a_string(800) FROM t1; /* 16 */
1944 INSERT INTO t1 SELECT a_string(800) FROM t1; /* 32 */
1945 COMMIT;
1946 }
1947 } {wal}
1948 do_test pager1-20.3.2 {
1949 execsql {
1950 BEGIN;
1951 INSERT INTO t2 VALUES('xxxx');
1952 }
1953 recursive_select 32 t1
1954 execsql COMMIT
1955 } {}
1956 }
1957  
1958 #-------------------------------------------------------------------------
1959 # Test that a WAL database may not be opened if:
1960 #
1961 # pager1-21.1.*: The VFS has an iVersion less than 2, or
1962 # pager1-21.2.*: The VFS does not provide xShmXXX() methods.
1963 #
1964 ifcapable wal {
1965 do_test pager1-21.0 {
1966 faultsim_delete_and_reopen
1967 execsql {
1968 PRAGMA journal_mode = WAL;
1969 CREATE TABLE ko(c DEFAULT 'abc', b DEFAULT 'def');
1970 INSERT INTO ko DEFAULT VALUES;
1971 }
1972 } {wal}
1973 do_test pager1-21.1 {
1974 testvfs tv -noshm 1
1975 sqlite3 db2 test.db -vfs tv
1976 catchsql { SELECT * FROM ko } db2
1977 } {1 {unable to open database file}}
1978 db2 close
1979 tv delete
1980 do_test pager1-21.2 {
1981 testvfs tv -iversion 1
1982 sqlite3 db2 test.db -vfs tv
1983 catchsql { SELECT * FROM ko } db2
1984 } {1 {unable to open database file}}
1985 db2 close
1986 tv delete
1987 }
1988  
1989 #-------------------------------------------------------------------------
1990 # Test that a "PRAGMA wal_checkpoint":
1991 #
1992 # pager1-22.1.*: is a no-op on a non-WAL db, and
1993 # pager1-22.2.*: does not cause xSync calls with a synchronous=off db.
1994 #
1995 do_test pager1-22.1.1 {
1996 faultsim_delete_and_reopen
1997 execsql {
1998 CREATE TABLE ko(c DEFAULT 'abc', b DEFAULT 'def');
1999 INSERT INTO ko DEFAULT VALUES;
2000 }
2001 execsql { PRAGMA wal_checkpoint }
2002 } {0 -1 -1}
2003 do_test pager1-22.2.1 {
2004 testvfs tv -default 1
2005 tv filter xSync
2006 tv script xSyncCb
2007 proc xSyncCb {args} {incr ::synccount}
2008 set ::synccount 0
2009 sqlite3 db test.db
2010 execsql {
2011 PRAGMA synchronous = off;
2012 PRAGMA journal_mode = WAL;
2013 INSERT INTO ko DEFAULT VALUES;
2014 }
2015 execsql { PRAGMA wal_checkpoint }
2016 set synccount
2017 } {0}
2018 db close
2019 tv delete
2020  
2021 #-------------------------------------------------------------------------
2022 # Tests for changing journal mode.
2023 #
2024 # pager1-23.1.*: Test that when changing from PERSIST to DELETE mode,
2025 # the journal file is deleted.
2026 #
2027 # pager1-23.2.*: Same test as above, but while a shared lock is held
2028 # on the database file.
2029 #
2030 # pager1-23.3.*: Same test as above, but while a reserved lock is held
2031 # on the database file.
2032 #
2033 # pager1-23.4.*: And, for fun, while holding an exclusive lock.
2034 #
2035 # pager1-23.5.*: Try to set various different journal modes with an
2036 # in-memory database (only MEMORY and OFF should work).
2037 #
2038 # pager1-23.6.*: Try to set locking_mode=normal on an in-memory database
2039 # (doesn't work - in-memory databases always use
2040 # locking_mode=exclusive).
2041 #
2042 do_test pager1-23.1.1 {
2043 faultsim_delete_and_reopen
2044 execsql {
2045 PRAGMA journal_mode = PERSIST;
2046 CREATE TABLE t1(a, b);
2047 }
2048 file exists test.db-journal
2049 } {1}
2050 do_test pager1-23.1.2 {
2051 execsql { PRAGMA journal_mode = DELETE }
2052 file exists test.db-journal
2053 } {0}
2054  
2055 do_test pager1-23.2.1 {
2056 execsql {
2057 PRAGMA journal_mode = PERSIST;
2058 INSERT INTO t1 VALUES('Canberra', 'ACT');
2059 }
2060 db eval { SELECT * FROM t1 } {
2061 db eval { PRAGMA journal_mode = DELETE }
2062 }
2063 execsql { PRAGMA journal_mode }
2064 } {delete}
2065 do_test pager1-23.2.2 {
2066 file exists test.db-journal
2067 } {0}
2068  
2069 do_test pager1-23.3.1 {
2070 execsql {
2071 PRAGMA journal_mode = PERSIST;
2072 INSERT INTO t1 VALUES('Darwin', 'NT');
2073 BEGIN IMMEDIATE;
2074 }
2075 db eval { PRAGMA journal_mode = DELETE }
2076 execsql { PRAGMA journal_mode }
2077 } {delete}
2078 do_test pager1-23.3.2 {
2079 file exists test.db-journal
2080 } {0}
2081 do_test pager1-23.3.3 {
2082 execsql COMMIT
2083 } {}
2084  
2085 do_test pager1-23.4.1 {
2086 execsql {
2087 PRAGMA journal_mode = PERSIST;
2088 INSERT INTO t1 VALUES('Adelaide', 'SA');
2089 BEGIN EXCLUSIVE;
2090 }
2091 db eval { PRAGMA journal_mode = DELETE }
2092 execsql { PRAGMA journal_mode }
2093 } {delete}
2094 do_test pager1-23.4.2 {
2095 file exists test.db-journal
2096 } {0}
2097 do_test pager1-23.4.3 {
2098 execsql COMMIT
2099 } {}
2100  
2101 do_test pager1-23.5.1 {
2102 faultsim_delete_and_reopen
2103 sqlite3 db :memory:
2104 } {}
2105 foreach {tn mode possible} {
2106 2 off 1
2107 3 memory 1
2108 4 persist 0
2109 5 delete 0
2110 6 wal 0
2111 7 truncate 0
2112 } {
2113 do_test pager1-23.5.$tn.1 {
2114 execsql "PRAGMA journal_mode = off"
2115 execsql "PRAGMA journal_mode = $mode"
2116 } [if $possible {list $mode} {list off}]
2117 do_test pager1-23.5.$tn.2 {
2118 execsql "PRAGMA journal_mode = memory"
2119 execsql "PRAGMA journal_mode = $mode"
2120 } [if $possible {list $mode} {list memory}]
2121 }
2122 do_test pager1-23.6.1 {
2123 execsql {PRAGMA locking_mode = normal}
2124 } {exclusive}
2125 do_test pager1-23.6.2 {
2126 execsql {PRAGMA locking_mode = exclusive}
2127 } {exclusive}
2128 do_test pager1-23.6.3 {
2129 execsql {PRAGMA locking_mode}
2130 } {exclusive}
2131 do_test pager1-23.6.4 {
2132 execsql {PRAGMA main.locking_mode}
2133 } {exclusive}
2134  
2135 #-------------------------------------------------------------------------
2136 #
2137 do_test pager1-24.1.1 {
2138 faultsim_delete_and_reopen
2139 db func a_string a_string
2140 execsql {
2141 PRAGMA cache_size = 10;
2142 PRAGMA auto_vacuum = FULL;
2143 CREATE TABLE x1(x, y, z, PRIMARY KEY(y, z));
2144 CREATE TABLE x2(x, y, z, PRIMARY KEY(y, z));
2145 INSERT INTO x2 VALUES(a_string(400), a_string(500), a_string(600));
2146 INSERT INTO x2 SELECT a_string(600), a_string(400), a_string(500) FROM x2;
2147 INSERT INTO x2 SELECT a_string(500), a_string(600), a_string(400) FROM x2;
2148 INSERT INTO x2 SELECT a_string(400), a_string(500), a_string(600) FROM x2;
2149 INSERT INTO x2 SELECT a_string(600), a_string(400), a_string(500) FROM x2;
2150 INSERT INTO x2 SELECT a_string(500), a_string(600), a_string(400) FROM x2;
2151 INSERT INTO x2 SELECT a_string(400), a_string(500), a_string(600) FROM x2;
2152 INSERT INTO x1 SELECT * FROM x2;
2153 }
2154 } {}
2155 do_test pager1-24.1.2 {
2156 execsql {
2157 BEGIN;
2158 DELETE FROM x1 WHERE rowid<32;
2159 }
2160 recursive_select 64 x2
2161 } {}
2162 do_test pager1-24.1.3 {
2163 execsql {
2164 UPDATE x1 SET z = a_string(300) WHERE rowid>40;
2165 COMMIT;
2166 PRAGMA integrity_check;
2167 SELECT count(*) FROM x1;
2168 }
2169 } {ok 33}
2170  
2171 do_test pager1-24.1.4 {
2172 execsql {
2173 DELETE FROM x1;
2174 INSERT INTO x1 SELECT * FROM x2;
2175 BEGIN;
2176 DELETE FROM x1 WHERE rowid<32;
2177 UPDATE x1 SET z = a_string(299) WHERE rowid>40;
2178 }
2179 recursive_select 64 x2 {db eval COMMIT}
2180 execsql {
2181 PRAGMA integrity_check;
2182 SELECT count(*) FROM x1;
2183 }
2184 } {ok 33}
2185  
2186 do_test pager1-24.1.5 {
2187 execsql {
2188 DELETE FROM x1;
2189 INSERT INTO x1 SELECT * FROM x2;
2190 }
2191 recursive_select 64 x2 { db eval {CREATE TABLE x3(x, y, z)} }
2192 execsql { SELECT * FROM x3 }
2193 } {}
2194  
2195 #-------------------------------------------------------------------------
2196 #
2197 do_test pager1-25-1 {
2198 faultsim_delete_and_reopen
2199 execsql {
2200 BEGIN;
2201 SAVEPOINT abc;
2202 CREATE TABLE t1(a, b);
2203 ROLLBACK TO abc;
2204 COMMIT;
2205 }
2206 db close
2207 } {}
2208 breakpoint
2209 do_test pager1-25-2 {
2210 faultsim_delete_and_reopen
2211 execsql {
2212 SAVEPOINT abc;
2213 CREATE TABLE t1(a, b);
2214 ROLLBACK TO abc;
2215 COMMIT;
2216 }
2217 db close
2218 } {}
2219  
2220 #-------------------------------------------------------------------------
2221 # Sector-size tests.
2222 #
2223 do_test pager1-26.1 {
2224 testvfs tv -default 1
2225 tv sectorsize 4096
2226 faultsim_delete_and_reopen
2227 db func a_string a_string
2228 execsql {
2229 PRAGMA page_size = 512;
2230 CREATE TABLE tbl(a PRIMARY KEY, b UNIQUE);
2231 BEGIN;
2232 INSERT INTO tbl VALUES(a_string(25), a_string(600));
2233 INSERT INTO tbl SELECT a_string(25), a_string(600) FROM tbl;
2234 INSERT INTO tbl SELECT a_string(25), a_string(600) FROM tbl;
2235 INSERT INTO tbl SELECT a_string(25), a_string(600) FROM tbl;
2236 INSERT INTO tbl SELECT a_string(25), a_string(600) FROM tbl;
2237 INSERT INTO tbl SELECT a_string(25), a_string(600) FROM tbl;
2238 INSERT INTO tbl SELECT a_string(25), a_string(600) FROM tbl;
2239 INSERT INTO tbl SELECT a_string(25), a_string(600) FROM tbl;
2240 COMMIT;
2241 }
2242 } {}
2243 do_execsql_test pager1-26.1 {
2244 UPDATE tbl SET b = a_string(550);
2245 } {}
2246 db close
2247 tv delete
2248  
2249 #-------------------------------------------------------------------------
2250 #
2251 do_test pager1.27.1 {
2252 faultsim_delete_and_reopen
2253 sqlite3_pager_refcounts db
2254 execsql {
2255 BEGIN;
2256 CREATE TABLE t1(a, b);
2257 }
2258 sqlite3_pager_refcounts db
2259 execsql COMMIT
2260 } {}
2261  
2262 #-------------------------------------------------------------------------
2263 # Test that attempting to open a write-transaction with
2264 # locking_mode=exclusive in WAL mode fails if there are other clients on
2265 # the same database.
2266 #
2267 catch { db close }
2268 ifcapable wal {
2269 do_multiclient_test tn {
2270 do_test pager1-28.$tn.1 {
2271 sql1 {
2272 PRAGMA journal_mode = WAL;
2273 CREATE TABLE t1(a, b);
2274 INSERT INTO t1 VALUES('a', 'b');
2275 }
2276 } {wal}
2277 do_test pager1-28.$tn.2 { sql2 { SELECT * FROM t1 } } {a b}
2278  
2279 do_test pager1-28.$tn.3 { sql1 { PRAGMA locking_mode=exclusive } } {exclusive}
2280 do_test pager1-28.$tn.4 {
2281 csql1 { BEGIN; INSERT INTO t1 VALUES('c', 'd'); }
2282 } {1 {database is locked}}
2283 code2 { db2 close ; sqlite3 db2 test.db }
2284 do_test pager1-28.$tn.4 {
2285 sql1 { INSERT INTO t1 VALUES('c', 'd'); COMMIT }
2286 } {}
2287 }
2288 }
2289  
2290 #-------------------------------------------------------------------------
2291 # Normally, when changing from journal_mode=PERSIST to DELETE the pager
2292 # attempts to delete the journal file. However, if it cannot obtain a
2293 # RESERVED lock on the database file, this step is skipped.
2294 #
2295 do_multiclient_test tn {
2296 do_test pager1-28.$tn.1 {
2297 sql1 {
2298 PRAGMA journal_mode = PERSIST;
2299 CREATE TABLE t1(a, b);
2300 INSERT INTO t1 VALUES('a', 'b');
2301 }
2302 } {persist}
2303 do_test pager1-28.$tn.2 { file exists test.db-journal } 1
2304 do_test pager1-28.$tn.3 { sql1 { PRAGMA journal_mode = DELETE } } delete
2305 do_test pager1-28.$tn.4 { file exists test.db-journal } 0
2306  
2307 do_test pager1-28.$tn.5 {
2308 sql1 {
2309 PRAGMA journal_mode = PERSIST;
2310 INSERT INTO t1 VALUES('c', 'd');
2311 }
2312 } {persist}
2313 do_test pager1-28.$tn.6 { file exists test.db-journal } 1
2314 do_test pager1-28.$tn.7 {
2315 sql2 { BEGIN; INSERT INTO t1 VALUES('e', 'f'); }
2316 } {}
2317 do_test pager1-28.$tn.8 { file exists test.db-journal } 1
2318 do_test pager1-28.$tn.9 { sql1 { PRAGMA journal_mode = DELETE } } delete
2319 do_test pager1-28.$tn.10 { file exists test.db-journal } 1
2320  
2321 do_test pager1-28.$tn.11 { sql2 COMMIT } {}
2322 do_test pager1-28.$tn.12 { file exists test.db-journal } 0
2323  
2324 do_test pager1-28-$tn.13 {
2325 code1 { set channel [db incrblob -readonly t1 a 2] }
2326 sql1 {
2327 PRAGMA journal_mode = PERSIST;
2328 INSERT INTO t1 VALUES('g', 'h');
2329 }
2330 } {persist}
2331 do_test pager1-28.$tn.14 { file exists test.db-journal } 1
2332 do_test pager1-28.$tn.15 {
2333 sql2 { BEGIN; INSERT INTO t1 VALUES('e', 'f'); }
2334 } {}
2335 do_test pager1-28.$tn.16 { sql1 { PRAGMA journal_mode = DELETE } } delete
2336 do_test pager1-28.$tn.17 { file exists test.db-journal } 1
2337  
2338 do_test pager1-28.$tn.17 { csql2 { COMMIT } } {1 {database is locked}}
2339 do_test pager1-28-$tn.18 { code1 { read $channel } } c
2340 do_test pager1-28-$tn.19 { code1 { close $channel } } {}
2341 do_test pager1-28.$tn.20 { sql2 { COMMIT } } {}
2342 }
2343  
2344 do_test pager1-29.1 {
2345 faultsim_delete_and_reopen
2346 execsql {
2347 PRAGMA page_size = 1024;
2348 PRAGMA auto_vacuum = full;
2349 PRAGMA locking_mode=exclusive;
2350 CREATE TABLE t1(a, b);
2351 INSERT INTO t1 VALUES(1, 2);
2352 }
2353 file size test.db
2354 } [expr 1024*3]
2355 do_test pager1-29.2 {
2356 execsql {
2357 PRAGMA page_size = 4096;
2358 VACUUM;
2359 }
2360 file size test.db
2361 } [expr 4096*3]
2362  
2363 #-------------------------------------------------------------------------
2364 # Test that if an empty database file (size 0 bytes) is opened in
2365 # exclusive-locking mode, any journal file is deleted from the file-system
2366 # without being rolled back. And that the RESERVED lock obtained while
2367 # doing this is not released.
2368 #
2369 do_test pager1-30.1 {
2370 db close
2371 file delete test.db
2372 file delete test.db-journal
2373 set fd [open test.db-journal w]
2374 seek $fd [expr 512+1032*2]
2375 puts -nonewline $fd x
2376 close $fd
2377  
2378 sqlite3 db test.db
2379 execsql {
2380 PRAGMA locking_mode=EXCLUSIVE;
2381 SELECT count(*) FROM sqlite_master;
2382 PRAGMA lock_status;
2383 }
2384 } {exclusive 0 main reserved temp closed}
2385  
2386 #-------------------------------------------------------------------------
2387 # Test that if the "page-size" field in a journal-header is 0, the journal
2388 # file can still be rolled back. This is required for backward compatibility -
2389 # versions of SQLite prior to 3.5.8 always set this field to zero.
2390 #
2391 if {$tcl_platform(platform)=="unix"} {
2392 do_test pager1-31.1 {
2393 faultsim_delete_and_reopen
2394 execsql {
2395 PRAGMA cache_size = 10;
2396 PRAGMA page_size = 1024;
2397 CREATE TABLE t1(x, y, UNIQUE(x, y));
2398 INSERT INTO t1 VALUES(randomblob(1500), randomblob(1500));
2399 INSERT INTO t1 SELECT randomblob(1500), randomblob(1500) FROM t1;
2400 INSERT INTO t1 SELECT randomblob(1500), randomblob(1500) FROM t1;
2401 INSERT INTO t1 SELECT randomblob(1500), randomblob(1500) FROM t1;
2402 INSERT INTO t1 SELECT randomblob(1500), randomblob(1500) FROM t1;
2403 INSERT INTO t1 SELECT randomblob(1500), randomblob(1500) FROM t1;
2404 INSERT INTO t1 SELECT randomblob(1500), randomblob(1500) FROM t1;
2405 INSERT INTO t1 SELECT randomblob(1500), randomblob(1500) FROM t1;
2406 INSERT INTO t1 SELECT randomblob(1500), randomblob(1500) FROM t1;
2407 INSERT INTO t1 SELECT randomblob(1500), randomblob(1500) FROM t1;
2408 INSERT INTO t1 SELECT randomblob(1500), randomblob(1500) FROM t1;
2409 BEGIN;
2410 UPDATE t1 SET y = randomblob(1499);
2411 }
2412 file copy test.db test.db2
2413 file copy test.db-journal test.db2-journal
2414  
2415 hexio_write test.db2-journal 24 00000000
2416 sqlite3 db2 test.db2
2417 execsql { PRAGMA integrity_check } db2
2418 } {ok}
2419 }
2420  
2421  
2422 finish_test