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