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