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*] { forcedelete $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*] { forcedelete $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*] { forcedelete $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: forcedelete $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: delete_file 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:
994: # The size of the journal file is now:
995: #
996: # 1) 512 byte header +
997: # 2) 2 * (1024+8) byte records +
998: # 3) 20+N bytes of master-journal pointer, where N is the size of
999: # the master-journal name encoded as utf-8 with no nul term.
1000: #
1001: set mj_pointer [expr {
1002: 20 + [string length [pwd]] + [string length "/test.db-mjXXXXXX9XX"]
1003: }]
1004: expr {$::max_journal==(512+2*(1024+8)+$mj_pointer)}
1005: } 1
1006: do_test pager1-5.4.2 {
1007: set ::max_journal 0
1008: execsql {
1009: PRAGMA synchronous = full;
1010: BEGIN;
1011: DELETE FROM t1 WHERE b = 'Lenin';
1012: DELETE FROM t2 WHERE b = 'Lenin';
1013: COMMIT;
1014: }
1015:
1016: # In synchronous=full mode, the master-journal pointer is not written
1017: # directly after the last record in the journal file. Instead, it is
1018: # written starting at the next (in this case 512 byte) sector boundary.
1019: #
1020: set mj_pointer [expr {
1021: 20 + [string length [pwd]] + [string length "/test.db-mjXXXXXX9XX"]
1022: }]
1023: expr {$::max_journal==(((512+2*(1024+8)+511)/512)*512 + $mj_pointer)}
1024: } 1
1025: db close
1026: tv delete
1027:
1028: do_test pager1-5.5.1 {
1029: sqlite3 db test.db
1030: execsql {
1031: ATTACH 'test.db2' AS aux;
1032: PRAGMA journal_mode = PERSIST;
1033: CREATE TABLE t3(a, b);
1034: INSERT INTO t3 SELECT randomblob(1500), randomblob(1500) FROM t1;
1035: UPDATE t3 SET b = randomblob(1500);
1036: }
1037: expr [file size test.db-journal] > 15000
1038: } {1}
1039: do_test pager1-5.5.2 {
1040: execsql {
1041: PRAGMA synchronous = full;
1042: BEGIN;
1043: DELETE FROM t1 WHERE b = 'Stalin';
1044: DELETE FROM t2 WHERE b = 'Stalin';
1045: COMMIT;
1046: }
1047: file size test.db-journal
1048: } {0}
1049:
1050:
1051: #-------------------------------------------------------------------------
1052: # The following tests work with "PRAGMA max_page_count"
1053: #
1054: do_test pager1-6.1 {
1055: faultsim_delete_and_reopen
1056: execsql {
1057: PRAGMA auto_vacuum = none;
1058: PRAGMA max_page_count = 10;
1059: CREATE TABLE t2(a, b);
1060: CREATE TABLE t3(a, b);
1061: CREATE TABLE t4(a, b);
1062: CREATE TABLE t5(a, b);
1063: CREATE TABLE t6(a, b);
1064: CREATE TABLE t7(a, b);
1065: CREATE TABLE t8(a, b);
1066: CREATE TABLE t9(a, b);
1067: CREATE TABLE t10(a, b);
1068: }
1069: } {10}
1070: do_catchsql_test pager1-6.2 {
1071: CREATE TABLE t11(a, b)
1072: } {1 {database or disk is full}}
1073: do_execsql_test pager1-6.4 { PRAGMA max_page_count } {10}
1074: do_execsql_test pager1-6.5 { PRAGMA max_page_count = 15 } {15}
1075: do_execsql_test pager1-6.6 { CREATE TABLE t11(a, b) } {}
1076: do_execsql_test pager1-6.7 {
1077: BEGIN;
1078: INSERT INTO t11 VALUES(1, 2);
1079: PRAGMA max_page_count = 13;
1080: } {13}
1081: do_execsql_test pager1-6.8 {
1082: INSERT INTO t11 VALUES(3, 4);
1083: PRAGMA max_page_count = 10;
1084: } {11}
1085: do_execsql_test pager1-6.9 { COMMIT } {}
1086:
1087: do_execsql_test pager1-6.10 { PRAGMA max_page_count = 10 } {11}
1088: do_execsql_test pager1-6.11 { SELECT * FROM t11 } {1 2 3 4}
1089: do_execsql_test pager1-6.12 { PRAGMA max_page_count } {11}
1090:
1091:
1092: #-------------------------------------------------------------------------
1093: # The following tests work with "PRAGMA journal_mode=TRUNCATE" and
1094: # "PRAGMA locking_mode=EXCLUSIVE".
1095: #
1096: # Each test is specified with 5 variables. As follows:
1097: #
1098: # $tn: Test Number. Used as part of the [do_test] test names.
1099: # $sql: SQL to execute.
1100: # $res: Expected result of executing $sql.
1101: # $js: The expected size of the journal file, in bytes, after executing
1102: # the SQL script. Or -1 if the journal is not expected to exist.
1103: # $ws: The expected size of the WAL file, in bytes, after executing
1104: # the SQL script. Or -1 if the WAL is not expected to exist.
1105: #
1106: ifcapable wal {
1107: faultsim_delete_and_reopen
1108: foreach {tn sql res js ws} [subst {
1109:
1110: 1 {
1111: CREATE TABLE t1(a, b);
1112: PRAGMA auto_vacuum=OFF;
1113: PRAGMA synchronous=NORMAL;
1114: PRAGMA page_size=1024;
1115: PRAGMA locking_mode=EXCLUSIVE;
1116: PRAGMA journal_mode=TRUNCATE;
1117: INSERT INTO t1 VALUES(1, 2);
1118: } {exclusive truncate} 0 -1
1119:
1120: 2 {
1121: BEGIN IMMEDIATE;
1122: SELECT * FROM t1;
1123: COMMIT;
1124: } {1 2} 0 -1
1125:
1126: 3 {
1127: BEGIN;
1128: SELECT * FROM t1;
1129: COMMIT;
1130: } {1 2} 0 -1
1131:
1132: 4 { PRAGMA journal_mode = WAL } wal -1 -1
1133: 5 { INSERT INTO t1 VALUES(3, 4) } {} -1 [wal_file_size 1 1024]
1134: 6 { PRAGMA locking_mode = NORMAL } exclusive -1 [wal_file_size 1 1024]
1135: 7 { INSERT INTO t1 VALUES(5, 6); } {} -1 [wal_file_size 2 1024]
1136:
1137: 8 { PRAGMA journal_mode = TRUNCATE } truncate 0 -1
1138: 9 { INSERT INTO t1 VALUES(7, 8) } {} 0 -1
1139: 10 { SELECT * FROM t1 } {1 2 3 4 5 6 7 8} 0 -1
1140:
1141: }] {
1142: do_execsql_test pager1-7.1.$tn.1 $sql $res
1143: catch { set J -1 ; set J [file size test.db-journal] }
1144: catch { set W -1 ; set W [file size test.db-wal] }
1145: do_test pager1-7.1.$tn.2 { list $J $W } [list $js $ws]
1146: }
1147: }
1148:
1149: do_test pager1-7.2.1 {
1150: faultsim_delete_and_reopen
1151: execsql {
1152: PRAGMA locking_mode = EXCLUSIVE;
1153: CREATE TABLE t1(a, b);
1154: BEGIN;
1155: PRAGMA journal_mode = delete;
1156: PRAGMA journal_mode = truncate;
1157: }
1158: } {exclusive delete truncate}
1159: do_test pager1-7.2.2 {
1160: execsql { INSERT INTO t1 VALUES(1, 2) }
1161: execsql { PRAGMA journal_mode = persist }
1162: } {truncate}
1163: do_test pager1-7.2.3 {
1164: execsql { COMMIT }
1165: execsql {
1166: PRAGMA journal_mode = persist;
1167: PRAGMA journal_size_limit;
1168: }
1169: } {persist -1}
1170:
1171: #-------------------------------------------------------------------------
1172: # The following tests, pager1-8.*, test that the special filenames
1173: # ":memory:" and "" open temporary databases.
1174: #
1175: foreach {tn filename} {
1176: 1 :memory:
1177: 2 ""
1178: } {
1179: do_test pager1-8.$tn.1 {
1180: faultsim_delete_and_reopen
1181: db close
1182: sqlite3 db $filename
1183: execsql {
1184: PRAGMA auto_vacuum = 1;
1185: CREATE TABLE x1(x);
1186: INSERT INTO x1 VALUES('Charles');
1187: INSERT INTO x1 VALUES('James');
1188: INSERT INTO x1 VALUES('Mary');
1189: SELECT * FROM x1;
1190: }
1191: } {Charles James Mary}
1192:
1193: do_test pager1-8.$tn.2 {
1194: sqlite3 db2 $filename
1195: catchsql { SELECT * FROM x1 } db2
1196: } {1 {no such table: x1}}
1197:
1198: do_execsql_test pager1-8.$tn.3 {
1199: BEGIN;
1200: INSERT INTO x1 VALUES('William');
1201: INSERT INTO x1 VALUES('Anne');
1202: ROLLBACK;
1203: } {}
1204: }
1205:
1206: #-------------------------------------------------------------------------
1207: # The next block of tests - pager1-9.* - deal with interactions between
1208: # the pager and the backup API. Test cases:
1209: #
1210: # pager1-9.1.*: Test that a backup completes successfully even if the
1211: # source db is written to during the backup op.
1212: #
1213: # pager1-9.2.*: Test that a backup completes successfully even if the
1214: # source db is written to and then rolled back during a
1215: # backup operation.
1216: #
1217: do_test pager1-9.0.1 {
1218: faultsim_delete_and_reopen
1219: db func a_string a_string
1220: execsql {
1221: PRAGMA cache_size = 10;
1222: BEGIN;
1223: CREATE TABLE ab(a, b, UNIQUE(a, b));
1224: INSERT INTO ab VALUES( a_string(200), a_string(300) );
1225: INSERT INTO ab SELECT a_string(200), a_string(300) FROM ab;
1226: INSERT INTO ab SELECT a_string(200), a_string(300) FROM ab;
1227: INSERT INTO ab SELECT a_string(200), a_string(300) FROM ab;
1228: INSERT INTO ab SELECT a_string(200), a_string(300) FROM ab;
1229: INSERT INTO ab SELECT a_string(200), a_string(300) FROM ab;
1230: INSERT INTO ab SELECT a_string(200), a_string(300) FROM ab;
1231: INSERT INTO ab SELECT a_string(200), a_string(300) FROM ab;
1232: COMMIT;
1233: }
1234: } {}
1235: do_test pager1-9.0.2 {
1236: sqlite3 db2 test.db2
1237: db2 eval { PRAGMA cache_size = 10 }
1238: sqlite3_backup B db2 main db main
1239: list [B step 10000] [B finish]
1240: } {SQLITE_DONE SQLITE_OK}
1241: do_test pager1-9.0.3 {
1242: db one {SELECT md5sum(a, b) FROM ab}
1243: } [db2 one {SELECT md5sum(a, b) FROM ab}]
1244:
1245: do_test pager1-9.1.1 {
1246: execsql { UPDATE ab SET a = a_string(201) }
1247: sqlite3_backup B db2 main db main
1248: B step 30
1249: } {SQLITE_OK}
1250: do_test pager1-9.1.2 {
1251: execsql { UPDATE ab SET b = a_string(301) }
1252: list [B step 10000] [B finish]
1253: } {SQLITE_DONE SQLITE_OK}
1254: do_test pager1-9.1.3 {
1255: db one {SELECT md5sum(a, b) FROM ab}
1256: } [db2 one {SELECT md5sum(a, b) FROM ab}]
1257: do_test pager1-9.1.4 { execsql { SELECT count(*) FROM ab } } {128}
1258:
1259: do_test pager1-9.2.1 {
1260: execsql { UPDATE ab SET a = a_string(202) }
1261: sqlite3_backup B db2 main db main
1262: B step 30
1263: } {SQLITE_OK}
1264: do_test pager1-9.2.2 {
1265: execsql {
1266: BEGIN;
1267: UPDATE ab SET b = a_string(301);
1268: ROLLBACK;
1269: }
1270: list [B step 10000] [B finish]
1271: } {SQLITE_DONE SQLITE_OK}
1272: do_test pager1-9.2.3 {
1273: db one {SELECT md5sum(a, b) FROM ab}
1274: } [db2 one {SELECT md5sum(a, b) FROM ab}]
1275: do_test pager1-9.2.4 { execsql { SELECT count(*) FROM ab } } {128}
1276: db close
1277: db2 close
1278:
1279: do_test pager1-9.3.1 {
1280: testvfs tv -default 1
1281: tv sectorsize 4096
1282: faultsim_delete_and_reopen
1283:
1284: execsql { PRAGMA page_size = 1024 }
1285: for {set ii 0} {$ii < 4} {incr ii} { execsql "CREATE TABLE t${ii}(a, b)" }
1286: } {}
1287: do_test pager1-9.3.2 {
1288: sqlite3 db2 test.db2
1289:
1290: execsql {
1291: PRAGMA page_size = 4096;
1292: PRAGMA synchronous = OFF;
1293: CREATE TABLE t1(a, b);
1294: CREATE TABLE t2(a, b);
1295: } db2
1296:
1297: sqlite3_backup B db2 main db main
1298: B step 30
1299: list [B step 10000] [B finish]
1300: } {SQLITE_DONE SQLITE_OK}
1301: do_test pager1-9.3.3 {
1302: db2 close
1303: db close
1304: tv delete
1305: file size test.db2
1306: } [file size test.db]
1307:
1308: do_test pager1-9.4.1 {
1309: faultsim_delete_and_reopen
1310: sqlite3 db2 test.db2
1311: execsql {
1312: PRAGMA page_size = 4096;
1313: CREATE TABLE t1(a, b);
1314: CREATE TABLE t2(a, b);
1315: } db2
1316: sqlite3_backup B db2 main db main
1317: list [B step 10000] [B finish]
1318: } {SQLITE_DONE SQLITE_OK}
1319: do_test pager1-9.4.2 {
1320: list [file size test.db2] [file size test.db]
1321: } {0 0}
1322: db2 close
1323:
1324: #-------------------------------------------------------------------------
1325: # Test that regardless of the value returned by xSectorSize(), the
1326: # minimum effective sector-size is 512 and the maximum 65536 bytes.
1327: #
1328: testvfs tv -default 1
1329: foreach sectorsize {
1330: 32 64 128 256 512 1024 2048
1331: 4096 8192 16384 32768 65536 131072 262144
1332: } {
1333: tv sectorsize $sectorsize
1334: tv devchar {}
1335: set eff $sectorsize
1336: if {$sectorsize < 512} { set eff 512 }
1337: if {$sectorsize > 65536} { set eff 65536 }
1338:
1339: do_test pager1-10.$sectorsize.1 {
1340: faultsim_delete_and_reopen
1341: db func a_string a_string
1342: execsql {
1343: PRAGMA journal_mode = PERSIST;
1344: PRAGMA page_size = 1024;
1345: BEGIN;
1346: CREATE TABLE t1(a, b);
1347: CREATE TABLE t2(a, b);
1348: CREATE TABLE t3(a, b);
1349: COMMIT;
1350: }
1351: file size test.db-journal
1352: } [expr $sectorsize > 65536 ? 65536 : $sectorsize]
1353:
1354: do_test pager1-10.$sectorsize.2 {
1355: execsql {
1356: INSERT INTO t3 VALUES(a_string(300), a_string(300));
1357: INSERT INTO t3 SELECT * FROM t3; /* 2 */
1358: INSERT INTO t3 SELECT * FROM t3; /* 4 */
1359: INSERT INTO t3 SELECT * FROM t3; /* 8 */
1360: INSERT INTO t3 SELECT * FROM t3; /* 16 */
1361: INSERT INTO t3 SELECT * FROM t3; /* 32 */
1362: }
1363: } {}
1364:
1365: do_test pager1-10.$sectorsize.3 {
1366: db close
1367: sqlite3 db test.db
1368: execsql {
1369: PRAGMA cache_size = 10;
1370: BEGIN;
1371: }
1372: recursive_select 32 t3 {db eval "INSERT INTO t2 VALUES(1, 2)"}
1373: execsql {
1374: COMMIT;
1375: SELECT * FROM t2;
1376: }
1377: } {1 2}
1378:
1379: do_test pager1-10.$sectorsize.4 {
1380: execsql {
1381: CREATE TABLE t6(a, b);
1382: CREATE TABLE t7(a, b);
1383: CREATE TABLE t5(a, b);
1384: DROP TABLE t6;
1385: DROP TABLE t7;
1386: }
1387: execsql {
1388: BEGIN;
1389: CREATE TABLE t6(a, b);
1390: }
1391: recursive_select 32 t3 {db eval "INSERT INTO t5 VALUES(1, 2)"}
1392: execsql {
1393: COMMIT;
1394: SELECT * FROM t5;
1395: }
1396: } {1 2}
1397:
1398: }
1399: db close
1400:
1401: tv sectorsize 4096
1402: do_test pager1.10.x.1 {
1403: faultsim_delete_and_reopen
1404: execsql {
1405: PRAGMA auto_vacuum = none;
1406: PRAGMA page_size = 1024;
1407: CREATE TABLE t1(x);
1408: }
1409: for {set i 0} {$i<30} {incr i} {
1410: execsql { INSERT INTO t1 VALUES(zeroblob(900)) }
1411: }
1412: file size test.db
1413: } {32768}
1414: do_test pager1.10.x.2 {
1415: execsql {
1416: CREATE TABLE t2(x);
1417: DROP TABLE t2;
1418: }
1419: file size test.db
1420: } {33792}
1421: do_test pager1.10.x.3 {
1422: execsql {
1423: BEGIN;
1424: CREATE TABLE t2(x);
1425: }
1426: recursive_select 30 t1
1427: execsql {
1428: CREATE TABLE t3(x);
1429: COMMIT;
1430: }
1431: } {}
1432:
1433: db close
1434: tv delete
1435:
1436: testvfs tv -default 1
1437: faultsim_delete_and_reopen
1438: db func a_string a_string
1439: do_execsql_test pager1-11.1 {
1440: PRAGMA journal_mode = DELETE;
1441: PRAGMA cache_size = 10;
1442: BEGIN;
1443: CREATE TABLE zz(top PRIMARY KEY);
1444: INSERT INTO zz VALUES(a_string(222));
1445: INSERT INTO zz SELECT a_string((SELECT 222+max(rowid) FROM zz)) FROM zz;
1446: INSERT INTO zz SELECT a_string((SELECT 222+max(rowid) FROM zz)) FROM zz;
1447: INSERT INTO zz SELECT a_string((SELECT 222+max(rowid) FROM zz)) FROM zz;
1448: INSERT INTO zz SELECT a_string((SELECT 222+max(rowid) FROM zz)) FROM zz;
1449: INSERT INTO zz SELECT a_string((SELECT 222+max(rowid) FROM zz)) FROM zz;
1450: COMMIT;
1451: BEGIN;
1452: UPDATE zz SET top = a_string(345);
1453: } {delete}
1454:
1455: proc lockout {method args} { return SQLITE_IOERR }
1456: tv script lockout
1457: tv filter {xWrite xTruncate xSync}
1458: do_catchsql_test pager1-11.2 { COMMIT } {1 {disk I/O error}}
1459:
1460: tv script {}
1461: do_test pager1-11.3 {
1462: sqlite3 db2 test.db
1463: execsql {
1464: PRAGMA journal_mode = TRUNCATE;
1465: PRAGMA integrity_check;
1466: } db2
1467: } {truncate ok}
1468: do_test pager1-11.4 {
1469: db2 close
1470: file exists test.db-journal
1471: } {0}
1472: do_execsql_test pager1-11.5 { SELECT count(*) FROM zz } {32}
1473: db close
1474: tv delete
1475:
1476: #-------------------------------------------------------------------------
1477: # Test "PRAGMA page_size"
1478: #
1479: testvfs tv -default 1
1480: tv sectorsize 1024
1481: foreach pagesize {
1482: 512 1024 2048 4096 8192 16384 32768
1483: } {
1484: faultsim_delete_and_reopen
1485:
1486: # The sector-size (according to the VFS) is 1024 bytes. So if the
1487: # page-size requested using "PRAGMA page_size" is greater than the
1488: # compile time value of SQLITE_MAX_PAGE_SIZE, then the effective
1489: # page-size remains 1024 bytes.
1490: #
1491: set eff $pagesize
1492: if {$eff > $::SQLITE_MAX_PAGE_SIZE} { set eff 1024 }
1493:
1494: do_test pager1-12.$pagesize.1 {
1495: sqlite3 db2 test.db
1496: execsql "
1497: PRAGMA page_size = $pagesize;
1498: CREATE VIEW v AS SELECT * FROM sqlite_master;
1499: " db2
1500: file size test.db
1501: } $eff
1502: do_test pager1-12.$pagesize.2 {
1503: sqlite3 db2 test.db
1504: execsql {
1505: SELECT count(*) FROM v;
1506: PRAGMA main.page_size;
1507: } db2
1508: } [list 1 $eff]
1509: do_test pager1-12.$pagesize.3 {
1510: execsql {
1511: SELECT count(*) FROM v;
1512: PRAGMA main.page_size;
1513: }
1514: } [list 1 $eff]
1515: db2 close
1516: }
1517: db close
1518: tv delete
1519:
1520: #-------------------------------------------------------------------------
1521: # Test specal "PRAGMA journal_mode=PERSIST" test cases.
1522: #
1523: # pager1-13.1.*: This tests a special case encountered in persistent
1524: # journal mode: If the journal associated with a transaction
1525: # is smaller than the journal file (because a previous
1526: # transaction left a very large non-hot journal file in the
1527: # file-system), then SQLite has to be careful that there is
1528: # not a journal-header left over from a previous transaction
1529: # immediately following the journal content just written.
1530: # If there is, and the process crashes so that the journal
1531: # becomes a hot-journal and must be rolled back by another
1532: # process, there is a danger that the other process may roll
1533: # back the aborted transaction, then continue copying data
1534: # from an older transaction from the remainder of the journal.
1535: # See the syncJournal() function for details.
1536: #
1537: # pager1-13.2.*: Same test as the previous. This time, throw an index into
1538: # the mix to make the integrity-check more likely to catch
1539: # errors.
1540: #
1541: testvfs tv -default 1
1542: tv script xSyncCb
1543: tv filter xSync
1544: proc xSyncCb {method filename args} {
1545: set t [file tail $filename]
1546: if {$t == "test.db"} faultsim_save
1547: return SQLITE_OK
1548: }
1549: faultsim_delete_and_reopen
1550: db func a_string a_string
1551:
1552: # The UPDATE statement at the end of this test case creates a really big
1553: # journal. Since the cache-size is only 10 pages, the journal contains
1554: # frequent journal headers.
1555: #
1556: do_execsql_test pager1-13.1.1 {
1557: PRAGMA page_size = 1024;
1558: PRAGMA journal_mode = PERSIST;
1559: PRAGMA cache_size = 10;
1560: BEGIN;
1561: CREATE TABLE t1(a INTEGER PRIMARY KEY, b BLOB);
1562: INSERT INTO t1 VALUES(NULL, a_string(400));
1563: INSERT INTO t1 SELECT NULL, a_string(400) FROM t1; /* 2 */
1564: INSERT INTO t1 SELECT NULL, a_string(400) FROM t1; /* 4 */
1565: INSERT INTO t1 SELECT NULL, a_string(400) FROM t1; /* 8 */
1566: INSERT INTO t1 SELECT NULL, a_string(400) FROM t1; /* 16 */
1567: INSERT INTO t1 SELECT NULL, a_string(400) FROM t1; /* 32 */
1568: INSERT INTO t1 SELECT NULL, a_string(400) FROM t1; /* 64 */
1569: INSERT INTO t1 SELECT NULL, a_string(400) FROM t1; /* 128 */
1570: COMMIT;
1571: UPDATE t1 SET b = a_string(400);
1572: } {persist}
1573:
1574: if {$::tcl_platform(platform)!="windows"} {
1575: # Run transactions of increasing sizes. Eventually, one (or more than one)
1576: # of these will write just enough content that one of the old headers created
1577: # by the transaction in the block above lies immediately after the content
1578: # journalled by the current transaction.
1579: #
1580: for {set nUp 1} {$nUp<64} {incr nUp} {
1581: do_execsql_test pager1-13.1.2.$nUp.1 {
1582: UPDATE t1 SET b = a_string(399) WHERE a <= $nUp
1583: } {}
1584: do_execsql_test pager1-13.1.2.$nUp.2 { PRAGMA integrity_check } {ok}
1585:
1586: # Try to access the snapshot of the file-system.
1587: #
1588: sqlite3 db2 sv_test.db
1589: do_test pager1-13.1.2.$nUp.3 {
1590: execsql { SELECT sum(length(b)) FROM t1 } db2
1591: } [expr {128*400 - ($nUp-1)}]
1592: do_test pager1-13.1.2.$nUp.4 {
1593: execsql { PRAGMA integrity_check } db2
1594: } {ok}
1595: db2 close
1596: }
1597: }
1598:
1599: if {$::tcl_platform(platform)!="windows"} {
1600: # Same test as above. But this time with an index on the table.
1601: #
1602: do_execsql_test pager1-13.2.1 {
1603: CREATE INDEX i1 ON t1(b);
1604: UPDATE t1 SET b = a_string(400);
1605: } {}
1606: for {set nUp 1} {$nUp<64} {incr nUp} {
1607: do_execsql_test pager1-13.2.2.$nUp.1 {
1608: UPDATE t1 SET b = a_string(399) WHERE a <= $nUp
1609: } {}
1610: do_execsql_test pager1-13.2.2.$nUp.2 { PRAGMA integrity_check } {ok}
1611: sqlite3 db2 sv_test.db
1612: do_test pager1-13.2.2.$nUp.3 {
1613: execsql { SELECT sum(length(b)) FROM t1 } db2
1614: } [expr {128*400 - ($nUp-1)}]
1615: do_test pager1-13.2.2.$nUp.4 {
1616: execsql { PRAGMA integrity_check } db2
1617: } {ok}
1618: db2 close
1619: }
1620: }
1621:
1622: db close
1623: tv delete
1624:
1625: #-------------------------------------------------------------------------
1626: # Test specal "PRAGMA journal_mode=OFF" test cases.
1627: #
1628: faultsim_delete_and_reopen
1629: do_execsql_test pager1-14.1.1 {
1630: PRAGMA journal_mode = OFF;
1631: CREATE TABLE t1(a, b);
1632: BEGIN;
1633: INSERT INTO t1 VALUES(1, 2);
1634: COMMIT;
1635: SELECT * FROM t1;
1636: } {off 1 2}
1637: do_catchsql_test pager1-14.1.2 {
1638: BEGIN;
1639: INSERT INTO t1 VALUES(3, 4);
1640: ROLLBACK;
1641: } {0 {}}
1642: do_execsql_test pager1-14.1.3 {
1643: SELECT * FROM t1;
1644: } {1 2}
1645: do_catchsql_test pager1-14.1.4 {
1646: BEGIN;
1647: INSERT INTO t1(rowid, a, b) SELECT a+3, b, b FROM t1;
1648: INSERT INTO t1(rowid, a, b) SELECT a+3, b, b FROM t1;
1649: } {1 {PRIMARY KEY must be unique}}
1650: do_execsql_test pager1-14.1.5 {
1651: COMMIT;
1652: SELECT * FROM t1;
1653: } {1 2 2 2}
1654:
1655: #-------------------------------------------------------------------------
1656: # Test opening and closing the pager sub-system with different values
1657: # for the sqlite3_vfs.szOsFile variable.
1658: #
1659: faultsim_delete_and_reopen
1660: do_execsql_test pager1-15.0 {
1661: CREATE TABLE tx(y, z);
1662: INSERT INTO tx VALUES('Ayutthaya', 'Beijing');
1663: INSERT INTO tx VALUES('London', 'Tokyo');
1664: } {}
1665: db close
1666: for {set i 0} {$i<513} {incr i 3} {
1667: testvfs tv -default 1 -szosfile $i
1668: sqlite3 db test.db
1669: do_execsql_test pager1-15.$i.1 {
1670: SELECT * FROM tx;
1671: } {Ayutthaya Beijing London Tokyo}
1672: db close
1673: tv delete
1674: }
1675:
1676: #-------------------------------------------------------------------------
1677: # Check that it is not possible to open a database file if the full path
1678: # to the associated journal file will be longer than sqlite3_vfs.mxPathname.
1679: #
1680: testvfs tv -default 1
1681: tv script xOpenCb
1682: tv filter xOpen
1683: proc xOpenCb {method filename args} {
1684: set ::file_len [string length $filename]
1685: }
1686: sqlite3 db test.db
1687: db close
1688: tv delete
1689:
1690: for {set ii [expr $::file_len-5]} {$ii < [expr $::file_len+20]} {incr ii} {
1691: testvfs tv -default 1 -mxpathname $ii
1692:
1693: # The length of the full path to file "test.db-journal" is ($::file_len+8).
1694: # If the configured sqlite3_vfs.mxPathname value greater than or equal to
1695: # this, then the file can be opened. Otherwise, it cannot.
1696: #
1697: if {$ii >= [expr $::file_len+8]} {
1698: set res {0 {}}
1699: } else {
1700: set res {1 {unable to open database file}}
1701: }
1702:
1703: do_test pager1-16.1.$ii {
1704: list [catch { sqlite3 db test.db } msg] $msg
1705: } $res
1706:
1707: catch {db close}
1708: tv delete
1709: }
1710:
1711: #-------------------------------------------------------------------------
1712: # Test "PRAGMA omit_readlock".
1713: #
1714: # pager1-17.$tn.1.*: Test that if a second connection has an open
1715: # read-transaction, it is not usually possible to write
1716: # the database.
1717: #
1718: # pager1-17.$tn.2.*: Test that if the second connection was opened with
1719: # the SQLITE_OPEN_READONLY flag, and
1720: # "PRAGMA omit_readlock = 1" is executed before attaching
1721: # the database and opening a read-transaction on it, it is
1722: # possible to write the db.
1723: #
1724: # pager1-17.$tn.3.*: Test that if the second connection was *not* opened with
1725: # the SQLITE_OPEN_READONLY flag, executing
1726: # "PRAGMA omit_readlock = 1" has no effect.
1727: #
1728: do_multiclient_test tn {
1729: do_test pager1-17.$tn.1.1 {
1730: sql1 {
1731: CREATE TABLE t1(a, b);
1732: INSERT INTO t1 VALUES(1, 2);
1733: }
1734: sql2 {
1735: BEGIN;
1736: SELECT * FROM t1;
1737: }
1738: } {1 2}
1739: do_test pager1-17.$tn.1.2 {
1740: csql1 { INSERT INTO t1 VALUES(3, 4) }
1741: } {1 {database is locked}}
1742: do_test pager1-17.$tn.1.3 {
1743: sql2 { COMMIT }
1744: sql1 { INSERT INTO t1 VALUES(3, 4) }
1745: } {}
1746:
1747: do_test pager1-17.$tn.2.1 {
1748: code2 {
1749: db2 close
1750: sqlite3 db2 :memory: -readonly 1
1751: }
1752: sql2 {
1753: PRAGMA omit_readlock = 1;
1754: ATTACH 'test.db' AS two;
1755: BEGIN;
1756: SELECT * FROM t1;
1757: }
1758: } {1 2 3 4}
1759: do_test pager1-17.$tn.2.2 { sql1 "INSERT INTO t1 VALUES(5, 6)" } {}
1760: do_test pager1-17.$tn.2.3 { sql2 "SELECT * FROM t1" } {1 2 3 4}
1761: do_test pager1-17.$tn.2.4 { sql2 "COMMIT ; SELECT * FROM t1" } {1 2 3 4 5 6}
1762:
1763: do_test pager1-17.$tn.3.1 {
1764: code2 {
1765: db2 close
1766: sqlite3 db2 :memory:
1767: }
1768: sql2 {
1769: PRAGMA omit_readlock = 1;
1770: ATTACH 'test.db' AS two;
1771: BEGIN;
1772: SELECT * FROM t1;
1773: }
1774: } {1 2 3 4 5 6}
1775: do_test pager1-17.$tn.3.2 {
1776: csql1 { INSERT INTO t1 VALUES(3, 4) }
1777: } {1 {database is locked}}
1778: do_test pager1-17.$tn.3.3 { sql2 COMMIT } {}
1779: }
1780:
1781: #-------------------------------------------------------------------------
1782: # Test the pagers response to the b-tree layer requesting illegal page
1783: # numbers:
1784: #
1785: # + The locking page,
1786: # + Page 0,
1787: # + A page with a page number greater than (2^31-1).
1788: #
1789: # These tests will not work if SQLITE_DIRECT_OVERFLOW_READ is defined. In
1790: # that case IO errors are sometimes reported instead of SQLITE_CORRUPT.
1791: #
1792: ifcapable !direct_read {
1793: do_test pager1-18.1 {
1794: faultsim_delete_and_reopen
1795: db func a_string a_string
1796: execsql {
1797: PRAGMA page_size = 1024;
1798: CREATE TABLE t1(a, b);
1799: INSERT INTO t1 VALUES(a_string(500), a_string(200));
1800: INSERT INTO t1 SELECT a_string(500), a_string(200) FROM t1;
1801: INSERT INTO t1 SELECT a_string(500), a_string(200) FROM t1;
1802: INSERT INTO t1 SELECT a_string(500), a_string(200) FROM t1;
1803: INSERT INTO t1 SELECT a_string(500), a_string(200) FROM t1;
1804: INSERT INTO t1 SELECT a_string(500), a_string(200) FROM t1;
1805: INSERT INTO t1 SELECT a_string(500), a_string(200) FROM t1;
1806: INSERT INTO t1 SELECT a_string(500), a_string(200) FROM t1;
1807: }
1808: } {}
1809: do_test pager1-18.2 {
1810: set root [db one "SELECT rootpage FROM sqlite_master"]
1811: set lockingpage [expr (0x10000/1024) + 1]
1812: execsql {
1813: PRAGMA writable_schema = 1;
1814: UPDATE sqlite_master SET rootpage = $lockingpage;
1815: }
1816: sqlite3 db2 test.db
1817: catchsql { SELECT count(*) FROM t1 } db2
1818: } {1 {database disk image is malformed}}
1819: db2 close
1820: do_test pager1-18.3 {
1821: execsql {
1822: CREATE TABLE t2(x);
1823: INSERT INTO t2 VALUES(a_string(5000));
1824: }
1825: set pgno [expr ([file size test.db] / 1024)-2]
1826: hexio_write test.db [expr ($pgno-1)*1024] 00000000
1827: sqlite3 db2 test.db
1828: catchsql { SELECT length(x) FROM t2 } db2
1829: } {1 {database disk image is malformed}}
1830: db2 close
1831: do_test pager1-18.4 {
1832: hexio_write test.db [expr ($pgno-1)*1024] 90000000
1833: sqlite3 db2 test.db
1834: catchsql { SELECT length(x) FROM t2 } db2
1835: } {1 {database disk image is malformed}}
1836: db2 close
1837: do_test pager1-18.5 {
1838: sqlite3 db ""
1839: execsql {
1840: CREATE TABLE t1(a, b);
1841: CREATE TABLE t2(a, b);
1842: PRAGMA writable_schema = 1;
1843: UPDATE sqlite_master SET rootpage=5 WHERE tbl_name = 't1';
1844: PRAGMA writable_schema = 0;
1845: ALTER TABLE t1 RENAME TO x1;
1846: }
1847: catchsql { SELECT * FROM x1 }
1848: } {1 {database disk image is malformed}}
1849: db close
1850:
1851: do_test pager1-18.6 {
1852: faultsim_delete_and_reopen
1853: db func a_string a_string
1854: execsql {
1855: PRAGMA page_size = 1024;
1856: CREATE TABLE t1(x);
1857: INSERT INTO t1 VALUES(a_string(800));
1858: INSERT INTO t1 VALUES(a_string(800));
1859: }
1860:
1861: set root [db one "SELECT rootpage FROM sqlite_master"]
1862: db close
1863:
1864: hexio_write test.db [expr ($root-1)*1024 + 8] 00000000
1865: sqlite3 db test.db
1866: catchsql { SELECT length(x) FROM t1 }
1867: } {1 {database disk image is malformed}}
1868: }
1869:
1870: do_test pager1-19.1 {
1871: sqlite3 db ""
1872: db func a_string a_string
1873: execsql {
1874: PRAGMA page_size = 512;
1875: PRAGMA auto_vacuum = 1;
1876: CREATE TABLE t1(aa, ab, ac, ad, ae, af, ag, ah, ai, aj, ak, al, am, an,
1877: ba, bb, bc, bd, be, bf, bg, bh, bi, bj, bk, bl, bm, bn,
1878: ca, cb, cc, cd, ce, cf, cg, ch, ci, cj, ck, cl, cm, cn,
1879: da, db, dc, dd, de, df, dg, dh, di, dj, dk, dl, dm, dn,
1880: ea, eb, ec, ed, ee, ef, eg, eh, ei, ej, ek, el, em, en,
1881: fa, fb, fc, fd, fe, ff, fg, fh, fi, fj, fk, fl, fm, fn,
1882: ga, gb, gc, gd, ge, gf, gg, gh, gi, gj, gk, gl, gm, gn,
1883: ha, hb, hc, hd, he, hf, hg, hh, hi, hj, hk, hl, hm, hn,
1884: ia, ib, ic, id, ie, if, ig, ih, ii, ij, ik, il, im, ix,
1885: ja, jb, jc, jd, je, jf, jg, jh, ji, jj, jk, jl, jm, jn,
1886: ka, kb, kc, kd, ke, kf, kg, kh, ki, kj, kk, kl, km, kn,
1887: la, lb, lc, ld, le, lf, lg, lh, li, lj, lk, ll, lm, ln,
1888: ma, mb, mc, md, me, mf, mg, mh, mi, mj, mk, ml, mm, mn
1889: );
1890: CREATE TABLE t2(aa, ab, ac, ad, ae, af, ag, ah, ai, aj, ak, al, am, an,
1891: ba, bb, bc, bd, be, bf, bg, bh, bi, bj, bk, bl, bm, bn,
1892: ca, cb, cc, cd, ce, cf, cg, ch, ci, cj, ck, cl, cm, cn,
1893: da, db, dc, dd, de, df, dg, dh, di, dj, dk, dl, dm, dn,
1894: ea, eb, ec, ed, ee, ef, eg, eh, ei, ej, ek, el, em, en,
1895: fa, fb, fc, fd, fe, ff, fg, fh, fi, fj, fk, fl, fm, fn,
1896: ga, gb, gc, gd, ge, gf, gg, gh, gi, gj, gk, gl, gm, gn,
1897: ha, hb, hc, hd, he, hf, hg, hh, hi, hj, hk, hl, hm, hn,
1898: ia, ib, ic, id, ie, if, ig, ih, ii, ij, ik, il, im, ix,
1899: ja, jb, jc, jd, je, jf, jg, jh, ji, jj, jk, jl, jm, jn,
1900: ka, kb, kc, kd, ke, kf, kg, kh, ki, kj, kk, kl, km, kn,
1901: la, lb, lc, ld, le, lf, lg, lh, li, lj, lk, ll, lm, ln,
1902: ma, mb, mc, md, me, mf, mg, mh, mi, mj, mk, ml, mm, mn
1903: );
1904: INSERT INTO t1(aa) VALUES( a_string(100000) );
1905: INSERT INTO t2(aa) VALUES( a_string(100000) );
1906: VACUUM;
1907: }
1908: } {}
1909:
1910: #-------------------------------------------------------------------------
1911: # Test a couple of special cases that come up while committing
1912: # transactions:
1913: #
1914: # pager1-20.1.*: Committing an in-memory database transaction when the
1915: # database has not been modified at all.
1916: #
1917: # pager1-20.2.*: As above, but with a normal db in exclusive-locking mode.
1918: #
1919: # pager1-20.3.*: Committing a transaction in WAL mode where the database has
1920: # been modified, but all dirty pages have been flushed to
1921: # disk before the commit.
1922: #
1923: do_test pager1-20.1.1 {
1924: catch {db close}
1925: sqlite3 db :memory:
1926: execsql {
1927: CREATE TABLE one(two, three);
1928: INSERT INTO one VALUES('a', 'b');
1929: }
1930: } {}
1931: do_test pager1-20.1.2 {
1932: execsql {
1933: BEGIN EXCLUSIVE;
1934: COMMIT;
1935: }
1936: } {}
1937:
1938: do_test pager1-20.2.1 {
1939: faultsim_delete_and_reopen
1940: execsql {
1941: PRAGMA locking_mode = exclusive;
1942: PRAGMA journal_mode = persist;
1943: CREATE TABLE one(two, three);
1944: INSERT INTO one VALUES('a', 'b');
1945: }
1946: } {exclusive persist}
1947: do_test pager1-20.2.2 {
1948: execsql {
1949: BEGIN EXCLUSIVE;
1950: COMMIT;
1951: }
1952: } {}
1953:
1954: ifcapable wal {
1955: do_test pager1-20.3.1 {
1956: faultsim_delete_and_reopen
1957: db func a_string a_string
1958: execsql {
1959: PRAGMA cache_size = 10;
1960: PRAGMA journal_mode = wal;
1961: BEGIN;
1962: CREATE TABLE t1(x);
1963: CREATE TABLE t2(y);
1964: INSERT INTO t1 VALUES(a_string(800));
1965: INSERT INTO t1 SELECT a_string(800) FROM t1; /* 2 */
1966: INSERT INTO t1 SELECT a_string(800) FROM t1; /* 4 */
1967: INSERT INTO t1 SELECT a_string(800) FROM t1; /* 8 */
1968: INSERT INTO t1 SELECT a_string(800) FROM t1; /* 16 */
1969: INSERT INTO t1 SELECT a_string(800) FROM t1; /* 32 */
1970: COMMIT;
1971: }
1972: } {wal}
1973: do_test pager1-20.3.2 {
1974: execsql {
1975: BEGIN;
1976: INSERT INTO t2 VALUES('xxxx');
1977: }
1978: recursive_select 32 t1
1979: execsql COMMIT
1980: } {}
1981: }
1982:
1983: #-------------------------------------------------------------------------
1984: # Test that a WAL database may not be opened if:
1985: #
1986: # pager1-21.1.*: The VFS has an iVersion less than 2, or
1987: # pager1-21.2.*: The VFS does not provide xShmXXX() methods.
1988: #
1989: ifcapable wal {
1990: do_test pager1-21.0 {
1991: faultsim_delete_and_reopen
1992: execsql {
1993: PRAGMA journal_mode = WAL;
1994: CREATE TABLE ko(c DEFAULT 'abc', b DEFAULT 'def');
1995: INSERT INTO ko DEFAULT VALUES;
1996: }
1997: } {wal}
1998: do_test pager1-21.1 {
1999: testvfs tv -noshm 1
2000: sqlite3 db2 test.db -vfs tv
2001: catchsql { SELECT * FROM ko } db2
2002: } {1 {unable to open database file}}
2003: db2 close
2004: tv delete
2005: do_test pager1-21.2 {
2006: testvfs tv -iversion 1
2007: sqlite3 db2 test.db -vfs tv
2008: catchsql { SELECT * FROM ko } db2
2009: } {1 {unable to open database file}}
2010: db2 close
2011: tv delete
2012: }
2013:
2014: #-------------------------------------------------------------------------
2015: # Test that a "PRAGMA wal_checkpoint":
2016: #
2017: # pager1-22.1.*: is a no-op on a non-WAL db, and
2018: # pager1-22.2.*: does not cause xSync calls with a synchronous=off db.
2019: #
2020: ifcapable wal {
2021: do_test pager1-22.1.1 {
2022: faultsim_delete_and_reopen
2023: execsql {
2024: CREATE TABLE ko(c DEFAULT 'abc', b DEFAULT 'def');
2025: INSERT INTO ko DEFAULT VALUES;
2026: }
2027: execsql { PRAGMA wal_checkpoint }
2028: } {0 -1 -1}
2029: do_test pager1-22.2.1 {
2030: testvfs tv -default 1
2031: tv filter xSync
2032: tv script xSyncCb
2033: proc xSyncCb {args} {incr ::synccount}
2034: set ::synccount 0
2035: sqlite3 db test.db
2036: execsql {
2037: PRAGMA synchronous = off;
2038: PRAGMA journal_mode = WAL;
2039: INSERT INTO ko DEFAULT VALUES;
2040: }
2041: execsql { PRAGMA wal_checkpoint }
2042: set synccount
2043: } {0}
2044: db close
2045: tv delete
2046: }
2047:
2048: #-------------------------------------------------------------------------
2049: # Tests for changing journal mode.
2050: #
2051: # pager1-23.1.*: Test that when changing from PERSIST to DELETE mode,
2052: # the journal file is deleted.
2053: #
2054: # pager1-23.2.*: Same test as above, but while a shared lock is held
2055: # on the database file.
2056: #
2057: # pager1-23.3.*: Same test as above, but while a reserved lock is held
2058: # on the database file.
2059: #
2060: # pager1-23.4.*: And, for fun, while holding an exclusive lock.
2061: #
2062: # pager1-23.5.*: Try to set various different journal modes with an
2063: # in-memory database (only MEMORY and OFF should work).
2064: #
2065: # pager1-23.6.*: Try to set locking_mode=normal on an in-memory database
2066: # (doesn't work - in-memory databases always use
2067: # locking_mode=exclusive).
2068: #
2069: do_test pager1-23.1.1 {
2070: faultsim_delete_and_reopen
2071: execsql {
2072: PRAGMA journal_mode = PERSIST;
2073: CREATE TABLE t1(a, b);
2074: }
2075: file exists test.db-journal
2076: } {1}
2077: do_test pager1-23.1.2 {
2078: execsql { PRAGMA journal_mode = DELETE }
2079: file exists test.db-journal
2080: } {0}
2081:
2082: do_test pager1-23.2.1 {
2083: execsql {
2084: PRAGMA journal_mode = PERSIST;
2085: INSERT INTO t1 VALUES('Canberra', 'ACT');
2086: }
2087: db eval { SELECT * FROM t1 } {
2088: db eval { PRAGMA journal_mode = DELETE }
2089: }
2090: execsql { PRAGMA journal_mode }
2091: } {delete}
2092: do_test pager1-23.2.2 {
2093: file exists test.db-journal
2094: } {0}
2095:
2096: do_test pager1-23.3.1 {
2097: execsql {
2098: PRAGMA journal_mode = PERSIST;
2099: INSERT INTO t1 VALUES('Darwin', 'NT');
2100: BEGIN IMMEDIATE;
2101: }
2102: db eval { PRAGMA journal_mode = DELETE }
2103: execsql { PRAGMA journal_mode }
2104: } {delete}
2105: do_test pager1-23.3.2 {
2106: file exists test.db-journal
2107: } {0}
2108: do_test pager1-23.3.3 {
2109: execsql COMMIT
2110: } {}
2111:
2112: do_test pager1-23.4.1 {
2113: execsql {
2114: PRAGMA journal_mode = PERSIST;
2115: INSERT INTO t1 VALUES('Adelaide', 'SA');
2116: BEGIN EXCLUSIVE;
2117: }
2118: db eval { PRAGMA journal_mode = DELETE }
2119: execsql { PRAGMA journal_mode }
2120: } {delete}
2121: do_test pager1-23.4.2 {
2122: file exists test.db-journal
2123: } {0}
2124: do_test pager1-23.4.3 {
2125: execsql COMMIT
2126: } {}
2127:
2128: do_test pager1-23.5.1 {
2129: faultsim_delete_and_reopen
2130: sqlite3 db :memory:
2131: } {}
2132: foreach {tn mode possible} {
2133: 2 off 1
2134: 3 memory 1
2135: 4 persist 0
2136: 5 delete 0
2137: 6 wal 0
2138: 7 truncate 0
2139: } {
2140: do_test pager1-23.5.$tn.1 {
2141: execsql "PRAGMA journal_mode = off"
2142: execsql "PRAGMA journal_mode = $mode"
2143: } [if $possible {list $mode} {list off}]
2144: do_test pager1-23.5.$tn.2 {
2145: execsql "PRAGMA journal_mode = memory"
2146: execsql "PRAGMA journal_mode = $mode"
2147: } [if $possible {list $mode} {list memory}]
2148: }
2149: do_test pager1-23.6.1 {
2150: execsql {PRAGMA locking_mode = normal}
2151: } {exclusive}
2152: do_test pager1-23.6.2 {
2153: execsql {PRAGMA locking_mode = exclusive}
2154: } {exclusive}
2155: do_test pager1-23.6.3 {
2156: execsql {PRAGMA locking_mode}
2157: } {exclusive}
2158: do_test pager1-23.6.4 {
2159: execsql {PRAGMA main.locking_mode}
2160: } {exclusive}
2161:
2162: #-------------------------------------------------------------------------
2163: #
2164: do_test pager1-24.1.1 {
2165: faultsim_delete_and_reopen
2166: db func a_string a_string
2167: execsql {
2168: PRAGMA cache_size = 10;
2169: PRAGMA auto_vacuum = FULL;
2170: CREATE TABLE x1(x, y, z, PRIMARY KEY(y, z));
2171: CREATE TABLE x2(x, y, z, PRIMARY KEY(y, z));
2172: INSERT INTO x2 VALUES(a_string(400), a_string(500), a_string(600));
2173: INSERT INTO x2 SELECT a_string(600), a_string(400), a_string(500) FROM x2;
2174: INSERT INTO x2 SELECT a_string(500), a_string(600), a_string(400) FROM x2;
2175: INSERT INTO x2 SELECT a_string(400), a_string(500), a_string(600) FROM x2;
2176: INSERT INTO x2 SELECT a_string(600), a_string(400), a_string(500) FROM x2;
2177: INSERT INTO x2 SELECT a_string(500), a_string(600), a_string(400) FROM x2;
2178: INSERT INTO x2 SELECT a_string(400), a_string(500), a_string(600) FROM x2;
2179: INSERT INTO x1 SELECT * FROM x2;
2180: }
2181: } {}
2182: do_test pager1-24.1.2 {
2183: execsql {
2184: BEGIN;
2185: DELETE FROM x1 WHERE rowid<32;
2186: }
2187: recursive_select 64 x2
2188: } {}
2189: do_test pager1-24.1.3 {
2190: execsql {
2191: UPDATE x1 SET z = a_string(300) WHERE rowid>40;
2192: COMMIT;
2193: PRAGMA integrity_check;
2194: SELECT count(*) FROM x1;
2195: }
2196: } {ok 33}
2197:
2198: do_test pager1-24.1.4 {
2199: execsql {
2200: DELETE FROM x1;
2201: INSERT INTO x1 SELECT * FROM x2;
2202: BEGIN;
2203: DELETE FROM x1 WHERE rowid<32;
2204: UPDATE x1 SET z = a_string(299) WHERE rowid>40;
2205: }
2206: recursive_select 64 x2 {db eval COMMIT}
2207: execsql {
2208: PRAGMA integrity_check;
2209: SELECT count(*) FROM x1;
2210: }
2211: } {ok 33}
2212:
2213: do_test pager1-24.1.5 {
2214: execsql {
2215: DELETE FROM x1;
2216: INSERT INTO x1 SELECT * FROM x2;
2217: }
2218: recursive_select 64 x2 { db eval {CREATE TABLE x3(x, y, z)} }
2219: execsql { SELECT * FROM x3 }
2220: } {}
2221:
2222: #-------------------------------------------------------------------------
2223: #
2224: do_test pager1-25-1 {
2225: faultsim_delete_and_reopen
2226: execsql {
2227: BEGIN;
2228: SAVEPOINT abc;
2229: CREATE TABLE t1(a, b);
2230: ROLLBACK TO abc;
2231: COMMIT;
2232: }
2233: db close
2234: } {}
2235: breakpoint
2236: do_test pager1-25-2 {
2237: faultsim_delete_and_reopen
2238: execsql {
2239: SAVEPOINT abc;
2240: CREATE TABLE t1(a, b);
2241: ROLLBACK TO abc;
2242: COMMIT;
2243: }
2244: db close
2245: } {}
2246:
2247: #-------------------------------------------------------------------------
2248: # Sector-size tests.
2249: #
2250: do_test pager1-26.1 {
2251: testvfs tv -default 1
2252: tv sectorsize 4096
2253: faultsim_delete_and_reopen
2254: db func a_string a_string
2255: execsql {
2256: PRAGMA page_size = 512;
2257: CREATE TABLE tbl(a PRIMARY KEY, b UNIQUE);
2258: BEGIN;
2259: INSERT INTO tbl VALUES(a_string(25), a_string(600));
2260: INSERT INTO tbl SELECT a_string(25), a_string(600) FROM tbl;
2261: INSERT INTO tbl SELECT a_string(25), a_string(600) FROM tbl;
2262: INSERT INTO tbl SELECT a_string(25), a_string(600) FROM tbl;
2263: INSERT INTO tbl SELECT a_string(25), a_string(600) FROM tbl;
2264: INSERT INTO tbl SELECT a_string(25), a_string(600) FROM tbl;
2265: INSERT INTO tbl SELECT a_string(25), a_string(600) FROM tbl;
2266: INSERT INTO tbl SELECT a_string(25), a_string(600) FROM tbl;
2267: COMMIT;
2268: }
2269: } {}
2270: do_execsql_test pager1-26.1 {
2271: UPDATE tbl SET b = a_string(550);
2272: } {}
2273: db close
2274: tv delete
2275:
2276: #-------------------------------------------------------------------------
2277: #
2278: do_test pager1.27.1 {
2279: faultsim_delete_and_reopen
2280: sqlite3_pager_refcounts db
2281: execsql {
2282: BEGIN;
2283: CREATE TABLE t1(a, b);
2284: }
2285: sqlite3_pager_refcounts db
2286: execsql COMMIT
2287: } {}
2288:
2289: #-------------------------------------------------------------------------
2290: # Test that attempting to open a write-transaction with
2291: # locking_mode=exclusive in WAL mode fails if there are other clients on
2292: # the same database.
2293: #
2294: catch { db close }
2295: ifcapable wal {
2296: do_multiclient_test tn {
2297: do_test pager1-28.$tn.1 {
2298: sql1 {
2299: PRAGMA journal_mode = WAL;
2300: CREATE TABLE t1(a, b);
2301: INSERT INTO t1 VALUES('a', 'b');
2302: }
2303: } {wal}
2304: do_test pager1-28.$tn.2 { sql2 { SELECT * FROM t1 } } {a b}
2305:
2306: do_test pager1-28.$tn.3 { sql1 { PRAGMA locking_mode=exclusive } } {exclusive}
2307: do_test pager1-28.$tn.4 {
2308: csql1 { BEGIN; INSERT INTO t1 VALUES('c', 'd'); }
2309: } {1 {database is locked}}
2310: code2 { db2 close ; sqlite3 db2 test.db }
2311: do_test pager1-28.$tn.4 {
2312: sql1 { INSERT INTO t1 VALUES('c', 'd'); COMMIT }
2313: } {}
2314: }
2315: }
2316:
2317: #-------------------------------------------------------------------------
2318: # Normally, when changing from journal_mode=PERSIST to DELETE the pager
2319: # attempts to delete the journal file. However, if it cannot obtain a
2320: # RESERVED lock on the database file, this step is skipped.
2321: #
2322: do_multiclient_test tn {
2323: do_test pager1-28.$tn.1 {
2324: sql1 {
2325: PRAGMA journal_mode = PERSIST;
2326: CREATE TABLE t1(a, b);
2327: INSERT INTO t1 VALUES('a', 'b');
2328: }
2329: } {persist}
2330: do_test pager1-28.$tn.2 { file exists test.db-journal } 1
2331: do_test pager1-28.$tn.3 { sql1 { PRAGMA journal_mode = DELETE } } delete
2332: do_test pager1-28.$tn.4 { file exists test.db-journal } 0
2333:
2334: do_test pager1-28.$tn.5 {
2335: sql1 {
2336: PRAGMA journal_mode = PERSIST;
2337: INSERT INTO t1 VALUES('c', 'd');
2338: }
2339: } {persist}
2340: do_test pager1-28.$tn.6 { file exists test.db-journal } 1
2341: do_test pager1-28.$tn.7 {
2342: sql2 { BEGIN; INSERT INTO t1 VALUES('e', 'f'); }
2343: } {}
2344: do_test pager1-28.$tn.8 { file exists test.db-journal } 1
2345: do_test pager1-28.$tn.9 { sql1 { PRAGMA journal_mode = DELETE } } delete
2346: do_test pager1-28.$tn.10 { file exists test.db-journal } 1
2347:
2348: do_test pager1-28.$tn.11 { sql2 COMMIT } {}
2349: do_test pager1-28.$tn.12 { file exists test.db-journal } 0
2350:
2351: do_test pager1-28-$tn.13 {
2352: code1 { set channel [db incrblob -readonly t1 a 2] }
2353: sql1 {
2354: PRAGMA journal_mode = PERSIST;
2355: INSERT INTO t1 VALUES('g', 'h');
2356: }
2357: } {persist}
2358: do_test pager1-28.$tn.14 { file exists test.db-journal } 1
2359: do_test pager1-28.$tn.15 {
2360: sql2 { BEGIN; INSERT INTO t1 VALUES('e', 'f'); }
2361: } {}
2362: do_test pager1-28.$tn.16 { sql1 { PRAGMA journal_mode = DELETE } } delete
2363: do_test pager1-28.$tn.17 { file exists test.db-journal } 1
2364:
2365: do_test pager1-28.$tn.17 { csql2 { COMMIT } } {1 {database is locked}}
2366: do_test pager1-28-$tn.18 { code1 { read $channel } } c
2367: do_test pager1-28-$tn.19 { code1 { close $channel } } {}
2368: do_test pager1-28.$tn.20 { sql2 { COMMIT } } {}
2369: }
2370:
2371: do_test pager1-29.1 {
2372: faultsim_delete_and_reopen
2373: execsql {
2374: PRAGMA page_size = 1024;
2375: PRAGMA auto_vacuum = full;
2376: PRAGMA locking_mode=exclusive;
2377: CREATE TABLE t1(a, b);
2378: INSERT INTO t1 VALUES(1, 2);
2379: }
2380: file size test.db
2381: } [expr 1024*3]
2382: do_test pager1-29.2 {
2383: execsql {
2384: PRAGMA page_size = 4096;
2385: VACUUM;
2386: }
2387: file size test.db
2388: } [expr 4096*3]
2389:
2390: #-------------------------------------------------------------------------
2391: # Test that if an empty database file (size 0 bytes) is opened in
2392: # exclusive-locking mode, any journal file is deleted from the file-system
2393: # without being rolled back. And that the RESERVED lock obtained while
2394: # doing this is not released.
2395: #
2396: do_test pager1-30.1 {
2397: db close
2398: delete_file test.db
2399: delete_file test.db-journal
2400: set fd [open test.db-journal w]
2401: seek $fd [expr 512+1032*2]
2402: puts -nonewline $fd x
2403: close $fd
2404:
2405: sqlite3 db test.db
2406: execsql {
2407: PRAGMA locking_mode=EXCLUSIVE;
2408: SELECT count(*) FROM sqlite_master;
2409: PRAGMA lock_status;
2410: }
2411: } {exclusive 0 main reserved temp closed}
2412:
2413: #-------------------------------------------------------------------------
2414: # Test that if the "page-size" field in a journal-header is 0, the journal
2415: # file can still be rolled back. This is required for backward compatibility -
2416: # versions of SQLite prior to 3.5.8 always set this field to zero.
2417: #
2418: if {$tcl_platform(platform)=="unix"} {
2419: do_test pager1-31.1 {
2420: faultsim_delete_and_reopen
2421: execsql {
2422: PRAGMA cache_size = 10;
2423: PRAGMA page_size = 1024;
2424: CREATE TABLE t1(x, y, UNIQUE(x, y));
2425: INSERT INTO t1 VALUES(randomblob(1500), randomblob(1500));
2426: INSERT INTO t1 SELECT randomblob(1500), randomblob(1500) FROM t1;
2427: INSERT INTO t1 SELECT randomblob(1500), randomblob(1500) FROM t1;
2428: INSERT INTO t1 SELECT randomblob(1500), randomblob(1500) FROM t1;
2429: INSERT INTO t1 SELECT randomblob(1500), randomblob(1500) FROM t1;
2430: INSERT INTO t1 SELECT randomblob(1500), randomblob(1500) FROM t1;
2431: INSERT INTO t1 SELECT randomblob(1500), randomblob(1500) FROM t1;
2432: INSERT INTO t1 SELECT randomblob(1500), randomblob(1500) FROM t1;
2433: INSERT INTO t1 SELECT randomblob(1500), randomblob(1500) FROM t1;
2434: INSERT INTO t1 SELECT randomblob(1500), randomblob(1500) FROM t1;
2435: INSERT INTO t1 SELECT randomblob(1500), randomblob(1500) FROM t1;
2436: BEGIN;
2437: UPDATE t1 SET y = randomblob(1499);
2438: }
2439: copy_file test.db test.db2
2440: copy_file test.db-journal test.db2-journal
2441:
2442: hexio_write test.db2-journal 24 00000000
2443: sqlite3 db2 test.db2
2444: execsql { PRAGMA integrity_check } db2
2445: } {ok}
2446: }
2447:
2448: #-------------------------------------------------------------------------
2449: # Test that a database file can be "pre-hinted" to a certain size and that
2450: # subsequent spilling of the pager cache does not result in the database
2451: # file being shrunk.
2452: #
2453: catch {db close}
2454: forcedelete test.db
2455:
2456: do_test pager1-32.1 {
2457: sqlite3 db test.db
2458: execsql {
2459: CREATE TABLE t1(x, y);
2460: }
2461: db close
2462: sqlite3 db test.db
2463: execsql {
2464: BEGIN;
2465: INSERT INTO t1 VALUES(1, randomblob(10000));
2466: }
2467: file_control_chunksize_test db main 1024
2468: file_control_sizehint_test db main 20971520; # 20MB
2469: execsql {
2470: PRAGMA cache_size = 10;
2471: INSERT INTO t1 VALUES(1, randomblob(10000));
2472: INSERT INTO t1 VALUES(2, randomblob(10000));
2473: INSERT INTO t1 SELECT x+2, randomblob(10000) from t1;
2474: INSERT INTO t1 SELECT x+4, randomblob(10000) from t1;
2475: INSERT INTO t1 SELECT x+8, randomblob(10000) from t1;
2476: INSERT INTO t1 SELECT x+16, randomblob(10000) from t1;
2477: SELECT count(*) FROM t1;
2478: COMMIT;
2479: }
2480: db close
2481: file size test.db
2482: } {20971520}
2483:
2484: # Cleanup 20MB file left by the previous test.
2485: forcedelete test.db
2486:
2487: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to pager1.test CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test