Debian Codegraph @

test/trans.test

raw
1 # 2001 September 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 # This file implements regression tests for SQLite library. The
12 # focus of this script is database locks.
13 #
14
15
16 set testdir [file dirname $argv0]
17 source $testdir/tester.tcl
18
19 # Create several tables to work with.
20 #
21 wal_set_journal_mode
22 do_test trans-1.0 {
23 execsql {
24 CREATE TABLE one(a int PRIMARY KEY, b text);
25 INSERT INTO one VALUES(1,'one');
26 INSERT INTO one VALUES(2,'two');
27 INSERT INTO one VALUES(3,'three');
28 SELECT b FROM one ORDER BY a;
29 }
30 } {one two three}
31 integrity_check trans-1.0.1
32 do_test trans-1.1 {
33 execsql {
34 CREATE TABLE two(a int PRIMARY KEY, b text);
35 INSERT INTO two VALUES(1,'I');
36 INSERT INTO two VALUES(5,'V');
37 INSERT INTO two VALUES(10,'X');
38 SELECT b FROM two ORDER BY a;
39 }
40 } {I V X}
41 do_test trans-1.2.1 {
42 sqlite3_txn_state db
43 } {0}
44 do_test trans-1.2.2 {
45 sqlite3_txn_state db main
46 } {0}
47 do_test trans-1.2.3 {
48 sqlite3_txn_state db temp
49 } {0}
50 do_test trans-1.2.4 {
51 sqlite3_txn_state db no-such-schema
52 } {-1}
53
54 do_test trans-1.9 {
55 sqlite3 altdb test.db
56 execsql {SELECT b FROM one ORDER BY a} altdb
57 } {one two three}
58 do_test trans-1.10 {
59 execsql {SELECT b FROM two ORDER BY a} altdb
60 } {I V X}
61 integrity_check trans-1.11
62 wal_check_journal_mode trans-1.12
63
64 # Basic transactions
65 #
66 do_test trans-2.1 {
67 set v [catch {execsql {BEGIN}} msg]
68 lappend v $msg
69 } {0 {}}
70 do_test trans-2.1b {
71 sqlite3_txn_state db
72 } {0}
73 do_test trans-2.2 {
74 set v [catch {execsql {END}} msg]
75 lappend v $msg
76 } {0 {}}
77 do_test trans-2.3 {
78 set v [catch {execsql {BEGIN TRANSACTION}} msg]
79 lappend v $msg
80 } {0 {}}
81 do_test trans-2.4 {
82 set v [catch {execsql {COMMIT TRANSACTION}} msg]
83 lappend v $msg
84 } {0 {}}
85 do_test trans-2.5 {
86 set v [catch {execsql {BEGIN TRANSACTION 'foo'}} msg]
87 lappend v $msg
88 } {0 {}}
89 do_test trans-2.6 {
90 set v [catch {execsql {ROLLBACK TRANSACTION 'foo'}} msg]
91 lappend v $msg
92 } {0 {}}
93 do_test trans-2.10 {
94 execsql {
95 BEGIN;
96 SELECT a FROM one ORDER BY a;
97 SELECT a FROM two ORDER BY a;
98 END;
99 }
100 } {1 2 3 1 5 10}
101 integrity_check trans-2.11
102 wal_check_journal_mode trans-2.12
103
104 # Check the locking behavior
105 #
106 do_test trans-3.1 {
107 execsql {
108 BEGIN;
109 UPDATE one SET a = 0 WHERE 0;
110 SELECT a FROM one ORDER BY a;
111 }
112 } {1 2 3}
113 do_test trans-3.1b {
114 sqlite3_txn_state db
115 } {2}
116 do_test trans-3.1c {
117 sqlite3_txn_state db main
118 } {2}
119 do_test trans-3.1d {
120 sqlite3_txn_state db temp
121 } {0}
122
123 do_test trans-3.2 {
124 catchsql {
125 SELECT a FROM two ORDER BY a;
126 } altdb
127 } {0 {1 5 10}}
128
129 do_test trans-3.3 {
130 catchsql {
131 SELECT a FROM one ORDER BY a;
132 } altdb
133 } {0 {1 2 3}}
134 do_test trans-3.4 {
135 catchsql {
136 INSERT INTO one VALUES(4,'four');
137 }
138 } {0 {}}
139 do_test trans-3.5 {
140 catchsql {
141 SELECT a FROM two ORDER BY a;
142 } altdb
143 } {0 {1 5 10}}
144 do_test trans-3.6 {
145 catchsql {
146 SELECT a FROM one ORDER BY a;
147 } altdb
148 } {0 {1 2 3}}
149 do_test trans-3.7 {
150 catchsql {
151 INSERT INTO two VALUES(4,'IV');
152 }
153 } {0 {}}
154 do_test trans-3.8 {
155 catchsql {
156 SELECT a FROM two ORDER BY a;
157 } altdb
158 } {0 {1 5 10}}
159 do_test trans-3.9 {
160 catchsql {
161 SELECT a FROM one ORDER BY a;
162 } altdb
163 } {0 {1 2 3}}
164 do_test trans-3.10 {
165 execsql {END TRANSACTION}
166 } {}
167 do_test trans-3.10b {
168 sqlite3_txn_state db
169 } {0}
170
171
172 do_test trans-3.11 {
173 set v [catch {execsql {
174 SELECT a FROM two ORDER BY a;
175 } altdb} msg]
176 lappend v $msg
177 } {0 {1 4 5 10}}
178 do_test trans-3.12 {
179 set v [catch {execsql {
180 SELECT a FROM one ORDER BY a;
181 } altdb} msg]
182 lappend v $msg
183 } {0 {1 2 3 4}}
184 do_test trans-3.13 {
185 set v [catch {execsql {
186 SELECT a FROM two ORDER BY a;
187 } db} msg]
188 lappend v $msg
189 } {0 {1 4 5 10}}
190 do_test trans-3.14 {
191 set v [catch {execsql {
192 SELECT a FROM one ORDER BY a;
193 } db} msg]
194 lappend v $msg
195 } {0 {1 2 3 4}}
196 integrity_check trans-3.15
197 wal_check_journal_mode trans-3.16
198
199 do_test trans-4.1 {
200 set v [catch {execsql {
201 COMMIT;
202 } db} msg]
203 lappend v $msg
204 } {1 {cannot commit - no transaction is active}}
205 do_test trans-4.2 {
206 set v [catch {execsql {
207 ROLLBACK;
208 } db} msg]
209 lappend v $msg
210 } {1 {cannot rollback - no transaction is active}}
211 do_test trans-4.3 {
212 catchsql {
213 BEGIN TRANSACTION;
214 UPDATE two SET a = 0 WHERE 0;
215 SELECT a FROM two ORDER BY a;
216 } db
217 } {0 {1 4 5 10}}
218 do_test trans-4.4 {
219 catchsql {
220 SELECT a FROM two ORDER BY a;
221 } altdb
222 } {0 {1 4 5 10}}
223 do_test trans-4.5 {
224 catchsql {
225 SELECT a FROM one ORDER BY a;
226 } altdb
227 } {0 {1 2 3 4}}
228 do_test trans-4.6 {
229 catchsql {
230 BEGIN TRANSACTION;
231 SELECT a FROM one ORDER BY a;
232 } db
233 } {1 {cannot start a transaction within a transaction}}
234 do_test trans-4.7 {
235 catchsql {
236 SELECT a FROM two ORDER BY a;
237 } altdb
238 } {0 {1 4 5 10}}
239 do_test trans-4.8 {
240 catchsql {
241 SELECT a FROM one ORDER BY a;
242 } altdb
243 } {0 {1 2 3 4}}
244 do_test trans-4.9 {
245 set v [catch {execsql {
246 END TRANSACTION;
247 SELECT a FROM two ORDER BY a;
248 } db} msg]
249 lappend v $msg
250 } {0 {1 4 5 10}}
251 do_test trans-4.10 {
252 set v [catch {execsql {
253 SELECT a FROM two ORDER BY a;
254 } altdb} msg]
255 lappend v $msg
256 } {0 {1 4 5 10}}
257 do_test trans-4.11 {
258 set v [catch {execsql {
259 SELECT a FROM one ORDER BY a;
260 } altdb} msg]
261 lappend v $msg
262 } {0 {1 2 3 4}}
263 integrity_check trans-4.12
264 wal_check_journal_mode trans-4.13
265 wal_check_journal_mode trans-4.14 altdb
266 do_test trans-4.98 {
267 altdb close
268 execsql {
269 DROP TABLE one;
270 DROP TABLE two;
271 }
272 } {}
273 integrity_check trans-4.99
274
275 # Check out the commit/rollback behavior of the database
276 #
277 do_test trans-5.1 {
278 execsql {SELECT name FROM sqlite_master WHERE type='table' ORDER BY name}
279 } {}
280 do_test trans-5.2 {
281 execsql {BEGIN TRANSACTION}
282 execsql {SELECT name FROM sqlite_master WHERE type='table' ORDER BY name}
283 } {}
284 do_test trans-5.2b {
285 sqlite3_txn_state db
286 } {1}
287 do_test trans-5.2c {
288 sqlite3_txn_state db main
289 } {1}
290 do_test trans-5.2d {
291 sqlite3_txn_state db temp
292 } {0}
293 do_test trans-5.3 {
294 execsql {CREATE TABLE one(a text, b int)}
295 execsql {SELECT name FROM sqlite_master WHERE type='table' ORDER BY name}
296 } {one}
297 do_test trans-5.4 {
298 execsql {SELECT a,b FROM one ORDER BY b}
299 } {}
300 do_test trans-5.5 {
301 execsql {INSERT INTO one(a,b) VALUES('hello', 1)}
302 execsql {SELECT a,b FROM one ORDER BY b}
303 } {hello 1}
304 do_test trans-5.6 {
305 execsql {ROLLBACK}
306 execsql {SELECT name FROM sqlite_master WHERE type='table' ORDER BY name}
307 } {}
308 do_test trans-5.7 {
309 set v [catch {
310 execsql {SELECT a,b FROM one ORDER BY b}
311 } msg]
312 lappend v $msg
313 } {1 {no such table: one}}
314
315 # Test commits and rollbacks of table CREATE TABLEs, CREATE INDEXs
316 # DROP TABLEs and DROP INDEXs
317 #
318 do_test trans-5.8 {
319 execsql {
320 SELECT name fROM sqlite_master
321 WHERE type='table' OR type='index'
322 ORDER BY name
323 }
324 } {}
325 do_test trans-5.9 {
326 execsql {
327 BEGIN TRANSACTION;
328 CREATE TABLE t1(a int, b int, c int);
329 SELECT name fROM sqlite_master
330 WHERE type='table' OR type='index'
331 ORDER BY name;
332 }
333 } {t1}
334 do_test trans-5.10 {
335 execsql {
336 CREATE INDEX i1 ON t1(a);
337 SELECT name fROM sqlite_master
338 WHERE type='table' OR type='index'
339 ORDER BY name;
340 }
341 } {i1 t1}
342 do_test trans-5.11 {
343 execsql {
344 COMMIT;
345 SELECT name fROM sqlite_master
346 WHERE type='table' OR type='index'
347 ORDER BY name;
348 }
349 } {i1 t1}
350 do_test trans-5.12 {
351 execsql {
352 BEGIN TRANSACTION;
353 CREATE TABLE t2(a int, b int, c int);
354 CREATE INDEX i2a ON t2(a);
355 CREATE INDEX i2b ON t2(b);
356 DROP TABLE t1;
357 SELECT name fROM sqlite_master
358 WHERE type='table' OR type='index'
359 ORDER BY name;
360 }
361 } {i2a i2b t2}
362 do_test trans-5.13 {
363 execsql {
364 ROLLBACK;
365 SELECT name fROM sqlite_master
366 WHERE type='table' OR type='index'
367 ORDER BY name;
368 }
369 } {i1 t1}
370 do_test trans-5.14 {
371 execsql {
372 BEGIN TRANSACTION;
373 DROP INDEX i1;
374 SELECT name fROM sqlite_master
375 WHERE type='table' OR type='index'
376 ORDER BY name;
377 }
378 } {t1}
379 do_test trans-5.15 {
380 execsql {
381 ROLLBACK;
382 SELECT name fROM sqlite_master
383 WHERE type='table' OR type='index'
384 ORDER BY name;
385 }
386 } {i1 t1}
387 do_test trans-5.16 {
388 execsql {
389 BEGIN TRANSACTION;
390 DROP INDEX i1;
391 CREATE TABLE t2(x int, y int, z int);
392 CREATE INDEX i2x ON t2(x);
393 CREATE INDEX i2y ON t2(y);
394 INSERT INTO t2 VALUES(1,2,3);
395 SELECT name fROM sqlite_master
396 WHERE type='table' OR type='index'
397 ORDER BY name;
398 }
399 } {i2x i2y t1 t2}
400 do_test trans-5.17 {
401 execsql {
402 COMMIT;
403 SELECT name fROM sqlite_master
404 WHERE type='table' OR type='index'
405 ORDER BY name;
406 }
407 } {i2x i2y t1 t2}
408 do_test trans-5.18 {
409 execsql {
410 SELECT * FROM t2;
411 }
412 } {1 2 3}
413 do_test trans-5.19 {
414 execsql {
415 SELECT x FROM t2 WHERE y=2;
416 }
417 } {1}
418 do_test trans-5.20 {
419 execsql {
420 BEGIN TRANSACTION;
421 DROP TABLE t1;
422 DROP TABLE t2;
423 SELECT name fROM sqlite_master
424 WHERE type='table' OR type='index'
425 ORDER BY name;
426 }
427 } {}
428 do_test trans-5.21 {
429 set r [catch {execsql {
430 SELECT * FROM t2
431 }} msg]
432 lappend r $msg
433 } {1 {no such table: t2}}
434 do_test trans-5.22 {
435 execsql {
436 ROLLBACK;
437 SELECT name fROM sqlite_master
438 WHERE type='table' OR type='index'
439 ORDER BY name;
440 }
441 } {i2x i2y t1 t2}
442 do_test trans-5.23 {
443 execsql {
444 SELECT * FROM t2;
445 }
446 } {1 2 3}
447 integrity_check trans-5.23
448
449
450 # Try to DROP and CREATE tables and indices with the same name
451 # within a transaction. Make sure ROLLBACK works.
452 #
453 do_test trans-6.1 {
454 execsql2 {
455 INSERT INTO t1 VALUES(1,2,3);
456 BEGIN TRANSACTION;
457 DROP TABLE t1;
458 CREATE TABLE t1(p,q,r);
459 ROLLBACK;
460 SELECT * FROM t1;
461 }
462 } {a 1 b 2 c 3}
463 do_test trans-6.2 {
464 execsql2 {
465 INSERT INTO t1 VALUES(1,2,3);
466 BEGIN TRANSACTION;
467 DROP TABLE t1;
468 CREATE TABLE t1(p,q,r);
469 COMMIT;
470 SELECT * FROM t1;
471 }
472 } {}
473 do_test trans-6.3 {
474 execsql2 {
475 INSERT INTO t1 VALUES(1,2,3);
476 SELECT * FROM t1;
477 }
478 } {p 1 q 2 r 3}
479 do_test trans-6.4 {
480 execsql2 {
481 BEGIN TRANSACTION;
482 DROP TABLE t1;
483 CREATE TABLE t1(a,b,c);
484 INSERT INTO t1 VALUES(4,5,6);
485 SELECT * FROM t1;
486 DROP TABLE t1;
487 }
488 } {a 4 b 5 c 6}
489 do_test trans-6.5 {
490 execsql2 {
491 ROLLBACK;
492 SELECT * FROM t1;
493 }
494 } {p 1 q 2 r 3}
495 do_test trans-6.6 {
496 execsql2 {
497 BEGIN TRANSACTION;
498 DROP TABLE t1;
499 CREATE TABLE t1(a,b,c);
500 INSERT INTO t1 VALUES(4,5,6);
501 SELECT * FROM t1;
502 DROP TABLE t1;
503 }
504 } {a 4 b 5 c 6}
505 do_test trans-6.7 {
506 catchsql {
507 COMMIT;
508 SELECT * FROM t1;
509 }
510 } {1 {no such table: t1}}
511
512 # Repeat on a table with an automatically generated index.
513 #
514 do_test trans-6.10 {
515 execsql2 {
516 CREATE TABLE t1(a unique,b,c);
517 INSERT INTO t1 VALUES(1,2,3);
518 BEGIN TRANSACTION;
519 DROP TABLE t1;
520 CREATE TABLE t1(p unique,q,r);
521 ROLLBACK;
522 SELECT * FROM t1;
523 }
524 } {a 1 b 2 c 3}
525 do_test trans-6.11 {
526 execsql2 {
527 BEGIN TRANSACTION;
528 DROP TABLE t1;
529 CREATE TABLE t1(p unique,q,r);
530 COMMIT;
531 SELECT * FROM t1;
532 }
533 } {}
534 do_test trans-6.12 {
535 execsql2 {
536 INSERT INTO t1 VALUES(1,2,3);
537 SELECT * FROM t1;
538 }
539 } {p 1 q 2 r 3}
540 do_test trans-6.13 {
541 execsql2 {
542 BEGIN TRANSACTION;
543 DROP TABLE t1;
544 CREATE TABLE t1(a unique,b,c);
545 INSERT INTO t1 VALUES(4,5,6);
546 SELECT * FROM t1;
547 DROP TABLE t1;
548 }
549 } {a 4 b 5 c 6}
550 do_test trans-6.14 {
551 execsql2 {
552 ROLLBACK;
553 SELECT * FROM t1;
554 }
555 } {p 1 q 2 r 3}
556 do_test trans-6.15 {
557 execsql2 {
558 BEGIN TRANSACTION;
559 DROP TABLE t1;
560 CREATE TABLE t1(a unique,b,c);
561 INSERT INTO t1 VALUES(4,5,6);
562 SELECT * FROM t1;
563 DROP TABLE t1;
564 }
565 } {a 4 b 5 c 6}
566 do_test trans-6.16 {
567 catchsql {
568 COMMIT;
569 SELECT * FROM t1;
570 }
571 } {1 {no such table: t1}}
572
573 do_test trans-6.20 {
574 execsql {
575 CREATE TABLE t1(a integer primary key,b,c);
576 INSERT INTO t1 VALUES(1,-2,-3);
577 INSERT INTO t1 VALUES(4,-5,-6);
578 SELECT * FROM t1;
579 }
580 } {1 -2 -3 4 -5 -6}
581 do_test trans-6.21 {
582 execsql {
583 CREATE INDEX i1 ON t1(b);
584 SELECT * FROM t1 WHERE b<1;
585 }
586 } {4 -5 -6 1 -2 -3}
587 do_test trans-6.22 {
588 execsql {
589 BEGIN TRANSACTION;
590 DROP INDEX i1;
591 SELECT * FROM t1 WHERE b<1;
592 ROLLBACK;
593 }
594 } {1 -2 -3 4 -5 -6}
595 do_test trans-6.23 {
596 execsql {
597 SELECT * FROM t1 WHERE b<1;
598 }
599 } {4 -5 -6 1 -2 -3}
600 do_test trans-6.24 {
601 execsql {
602 BEGIN TRANSACTION;
603 DROP TABLE t1;
604 ROLLBACK;
605 SELECT * FROM t1 WHERE b<1;
606 }
607 } {4 -5 -6 1 -2 -3}
608
609 do_test trans-6.25 {
610 execsql {
611 BEGIN TRANSACTION;
612 DROP INDEX i1;
613 CREATE INDEX i1 ON t1(c);
614 SELECT * FROM t1 WHERE b<1;
615 }
616 } {1 -2 -3 4 -5 -6}
617 do_test trans-6.26 {
618 execsql {
619 SELECT * FROM t1 WHERE c<1;
620 }
621 } {4 -5 -6 1 -2 -3}
622 do_test trans-6.27 {
623 execsql {
624 ROLLBACK;
625 SELECT * FROM t1 WHERE b<1;
626 }
627 } {4 -5 -6 1 -2 -3}
628 do_test trans-6.28 {
629 execsql {
630 SELECT * FROM t1 WHERE c<1;
631 }
632 } {1 -2 -3 4 -5 -6}
633
634 # The following repeats steps 6.20 through 6.28, but puts a "unique"
635 # constraint the first field of the table in order to generate an
636 # automatic index.
637 #
638 do_test trans-6.30 {
639 execsql {
640 BEGIN TRANSACTION;
641 DROP TABLE t1;
642 CREATE TABLE t1(a int unique,b,c);
643 COMMIT;
644 INSERT INTO t1 VALUES(1,-2,-3);
645 INSERT INTO t1 VALUES(4,-5,-6);
646 SELECT * FROM t1 ORDER BY a;
647 }
648 } {1 -2 -3 4 -5 -6}
649 do_test trans-6.31 {
650 execsql {
651 CREATE INDEX i1 ON t1(b);
652 SELECT * FROM t1 WHERE b<1;
653 }
654 } {4 -5 -6 1 -2 -3}
655 do_test trans-6.32 {
656 execsql {
657 BEGIN TRANSACTION;
658 DROP INDEX i1;
659 SELECT * FROM t1 WHERE b<1;
660 ROLLBACK;
661 }
662 } {1 -2 -3 4 -5 -6}
663 do_test trans-6.33 {
664 execsql {
665 SELECT * FROM t1 WHERE b<1;
666 }
667 } {4 -5 -6 1 -2 -3}
668 do_test trans-6.34 {
669 execsql {
670 BEGIN TRANSACTION;
671 DROP TABLE t1;
672 ROLLBACK;
673 SELECT * FROM t1 WHERE b<1;
674 }
675 } {4 -5 -6 1 -2 -3}
676
677 do_test trans-6.35 {
678 execsql {
679 BEGIN TRANSACTION;
680 DROP INDEX i1;
681 CREATE INDEX i1 ON t1(c);
682 SELECT * FROM t1 WHERE b<1;
683 }
684 } {1 -2 -3 4 -5 -6}
685 do_test trans-6.36 {
686 execsql {
687 SELECT * FROM t1 WHERE c<1;
688 }
689 } {4 -5 -6 1 -2 -3}
690 do_test trans-6.37 {
691 execsql {
692 DROP INDEX i1;
693 SELECT * FROM t1 WHERE c<1;
694 }
695 } {1 -2 -3 4 -5 -6}
696 do_test trans-6.38 {
697 execsql {
698 ROLLBACK;
699 SELECT * FROM t1 WHERE b<1;
700 }
701 } {4 -5 -6 1 -2 -3}
702 do_test trans-6.39 {
703 execsql {
704 SELECT * FROM t1 WHERE c<1;
705 }
706 } {1 -2 -3 4 -5 -6}
707 integrity_check trans-6.40
708
709 # Test to make sure rollback restores the database back to its original
710 # state.
711 #
712 do_test trans-7.1 {
713 execsql {BEGIN}
714 for {set i 0} {$i<1000} {incr i} {
715 set r1 [expr {rand()}]
716 set r2 [expr {rand()}]
717 set r3 [expr {rand()}]
718 execsql "INSERT INTO t2 VALUES($r1,$r2,$r3)"
719 }
720 execsql {COMMIT}
721 set ::checksum [execsql {SELECT md5sum(x,y,z) FROM t2}]
722 set ::checksum2 [
723 execsql {SELECT md5sum(type,name,tbl_name,rootpage,sql) FROM sqlite_master}
724 ]
725 execsql {SELECT count(*) FROM t2}
726 } {1001}
727 do_test trans-7.2 {
728 execsql {SELECT md5sum(x,y,z) FROM t2}
729 } $checksum
730 do_test trans-7.2.1 {
731 execsql {SELECT md5sum(type,name,tbl_name,rootpage,sql) FROM sqlite_master}
732 } $checksum2
733 do_test trans-7.3 {
734 execsql {
735 BEGIN;
736 DELETE FROM t2;
737 ROLLBACK;
738 SELECT md5sum(x,y,z) FROM t2;
739 }
740 } $checksum
741 do_test trans-7.4 {
742 execsql {
743 BEGIN;
744 INSERT INTO t2 SELECT * FROM t2;
745 ROLLBACK;
746 SELECT md5sum(x,y,z) FROM t2;
747 }
748 } $checksum
749 do_test trans-7.5 {
750 execsql {
751 BEGIN;
752 DELETE FROM t2;
753 ROLLBACK;
754 SELECT md5sum(x,y,z) FROM t2;
755 }
756 } $checksum
757 do_test trans-7.6 {
758 execsql {
759 BEGIN;
760 INSERT INTO t2 SELECT * FROM t2;
761 ROLLBACK;
762 SELECT md5sum(x,y,z) FROM t2;
763 }
764 } $checksum
765 do_test trans-7.7 {
766 execsql {
767 BEGIN;
768 CREATE TABLE t3 AS SELECT * FROM t2;
769 INSERT INTO t2 SELECT * FROM t3;
770 ROLLBACK;
771 SELECT md5sum(x,y,z) FROM t2;
772 }
773 } $checksum
774 do_test trans-7.8 {
775 execsql {SELECT md5sum(type,name,tbl_name,rootpage,sql) FROM sqlite_master}
776 } $checksum2
777 ifcapable tempdb {
778 do_test trans-7.9 {
779 execsql {
780 BEGIN;
781 CREATE TEMP TABLE t3 AS SELECT * FROM t2;
782 INSERT INTO t2 SELECT * FROM t3;
783 ROLLBACK;
784 SELECT md5sum(x,y,z) FROM t2;
785 }
786 } $checksum
787 }
788 do_test trans-7.10 {
789 execsql {SELECT md5sum(type,name,tbl_name,rootpage,sql) FROM sqlite_master}
790 } $checksum2
791 ifcapable tempdb {
792 do_test trans-7.11 {
793 execsql {
794 BEGIN;
795 CREATE TEMP TABLE t3 AS SELECT * FROM t2;
796 INSERT INTO t2 SELECT * FROM t3;
797 DROP INDEX i2x;
798 DROP INDEX i2y;
799 CREATE INDEX i3a ON t3(x);
800 ROLLBACK;
801 SELECT md5sum(x,y,z) FROM t2;
802 }
803 } $checksum
804 }
805 do_test trans-7.12 {
806 execsql {SELECT md5sum(type,name,tbl_name,rootpage,sql) FROM sqlite_master}
807 } $checksum2
808 ifcapable tempdb {
809 do_test trans-7.13 {
810 execsql {
811 BEGIN;
812 DROP TABLE t2;
813 ROLLBACK;
814 SELECT md5sum(x,y,z) FROM t2;
815 }
816 } $checksum
817 }
818 do_test trans-7.14 {
819 execsql {SELECT md5sum(type,name,tbl_name,rootpage,sql) FROM sqlite_master}
820 } $checksum2
821 integrity_check trans-7.15
822 wal_check_journal_mode trans-7.16
823
824 # Arrange for another process to begin modifying the database but abort
825 # and die in the middle of the modification. Then have this process read
826 # the database. This process should detect the journal file and roll it
827 # back. Verify that this happens correctly.
828 #
829 set fd [open test.tcl w]
830 puts $fd {
831 sqlite3_test_control_pending_byte 0x0010000
832 sqlite3 db test.db
833 db eval {
834 PRAGMA default_cache_size=20;
835 BEGIN;
836 CREATE TABLE t3 AS SELECT * FROM t2;
837 DELETE FROM t2;
838 }
839 sqlite_abort
840 }
841 close $fd
842 do_test trans-8.1 {
843 catch {exec [info nameofexec] test.tcl}
844 execsql {SELECT md5sum(x,y,z) FROM t2}
845 } $checksum
846 do_test trans-8.2 {
847 execsql {SELECT md5sum(type,name,tbl_name,rootpage,sql) FROM sqlite_master}
848 } $checksum2
849 integrity_check trans-8.3
850 set fd [open test.tcl w]
851 puts $fd {
852 sqlite3_test_control_pending_byte 0x0010000
853 sqlite3 db test.db
854 db eval {
855 PRAGMA journal_mode=persist;
856 PRAGMA default_cache_size=20;
857 BEGIN;
858 CREATE TABLE t3 AS SELECT * FROM t2;
859 DELETE FROM t2;
860 }
861 sqlite_abort
862 }
863 close $fd
864 do_test trans-8.4 {
865 catch {exec [info nameofexec] test.tcl}
866 execsql {SELECT md5sum(x,y,z) FROM t2}
867 } $checksum
868 do_test trans-8.5 {
869 execsql {SELECT md5sum(type,name,tbl_name,rootpage,sql) FROM sqlite_master}
870 } $checksum2
871 integrity_check trans-8.6
872 wal_check_journal_mode trans-8.7
873
874 # In the following sequence of tests, compute the MD5 sum of the content
875 # of a table, make lots of modifications to that table, then do a rollback.
876 # Verify that after the rollback, the MD5 checksum is unchanged.
877 #
878 do_test trans-9.1 {
879 execsql {
880 PRAGMA default_cache_size=10;
881 }
882 db close
883 sqlite3 db test.db
884 execsql {
885 BEGIN;
886 CREATE TABLE t3(x TEXT);
887 INSERT INTO t3 VALUES(randstr(10,400));
888 INSERT INTO t3 VALUES(randstr(10,400));
889 INSERT INTO t3 SELECT randstr(10,400) FROM t3;
890 INSERT INTO t3 SELECT randstr(10,400) FROM t3;
891 INSERT INTO t3 SELECT randstr(10,400) FROM t3;
892 INSERT INTO t3 SELECT randstr(10,400) FROM t3;
893 INSERT INTO t3 SELECT randstr(10,400) FROM t3;
894 INSERT INTO t3 SELECT randstr(10,400) FROM t3;
895 INSERT INTO t3 SELECT randstr(10,400) FROM t3;
896 INSERT INTO t3 SELECT randstr(10,400) FROM t3;
897 INSERT INTO t3 SELECT randstr(10,400) FROM t3;
898 COMMIT;
899 SELECT count(*) FROM t3;
900 }
901 } {1024}
902 wal_check_journal_mode trans-9.1.1
903
904 # The following procedure computes a "signature" for table "t3". If
905 # T3 changes in any way, the signature should change.
906 #
907 # This is used to test ROLLBACK. We gather a signature for t3, then
908 # make lots of changes to t3, then rollback and take another signature.
909 # The two signatures should be the same.
910 #
911 proc signature {} {
912 return [db eval {SELECT count(*), md5sum(x) FROM t3}]
913 }
914
915 # Repeat the following group of tests 20 times for quick testing and
916 # 40 times for full testing. Each iteration of the test makes table
917 # t3 a little larger, and thus takes a little longer, so doing 40 tests
918 # is more than 2.0 times slower than doing 20 tests. Considerably more.
919 #
920 # Also, if temporary tables are stored in memory and the test pcache
921 # is in use, only 20 iterations. Otherwise the test pcache runs out
922 # of page slots and SQLite reports "out of memory".
923 #
924 if {[info exists G(isquick)] || (
925 $TEMP_STORE==3 && [regexp {^pcache[[:digit:]]*$} [permutation]]
926 ) } {
927 set limit 20
928 } elseif {[info exists G(issoak)]} {
929 set limit 100
930 } else {
931 set limit 40
932 }
933
934 # Do rollbacks. Make sure the signature does not change.
935 #
936 for {set i 2} {$i<=$limit} {incr i} {
937 set ::sig [signature]
938 set cnt [lindex $::sig 0]
939 if {$i%2==0} {
940 execsql {PRAGMA fullfsync=ON}
941 } else {
942 execsql {PRAGMA fullfsync=OFF}
943 }
944 set sqlite_sync_count 0
945 set sqlite_fullsync_count 0
946 do_test trans-9.$i.1-$cnt {
947 execsql {
948 BEGIN;
949 DELETE FROM t3 WHERE random()%10!=0;
950 INSERT INTO t3 SELECT randstr(10,10)||x FROM t3;
951 INSERT INTO t3 SELECT randstr(10,10)||x FROM t3;
952 ROLLBACK;
953 }
954 signature
955 } $sig
956 do_test trans-9.$i.2-$cnt {
957 execsql {
958 BEGIN;
959 DELETE FROM t3 WHERE random()%10!=0;
960 INSERT INTO t3 SELECT randstr(10,10)||x FROM t3;
961 DELETE FROM t3 WHERE random()%10!=0;
962 INSERT INTO t3 SELECT randstr(10,10)||x FROM t3;
963 ROLLBACK;
964 }
965 signature
966 } $sig
967 if {$i<$limit} {
968 do_test trans-9.$i.3-$cnt {
969 execsql {
970 INSERT INTO t3 SELECT randstr(10,400) FROM t3 WHERE random()%10==0;
971 }
972 } {}
973 catch flush_async_queue
974 if {$tcl_platform(platform)=="unix"} {
975 do_test trans-9.$i.4-$cnt {
976 expr {$sqlite_sync_count>0}
977 } 1
978 ifcapable pager_pragmas {
979 do_test trans-9.$i.5-$cnt {
980 expr {$sqlite_fullsync_count>0}
981 } [expr {$i%2==0}]
982 } else {
983 do_test trans-9.$i.5-$cnt {
984 expr {$sqlite_fullsync_count==0}
985 } {1}
986 }
987 }
988 }
989
990 wal_check_journal_mode trans-9.$i.6-$cnt
991 set ::pager_old_format 0
992 }
993
994 finish_test