Debian Codegraph @

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