xref: /titanic_44/usr/src/lib/libsqlite/test/trans.test (revision fdd1ecae0dfe07e6aa8ee90687e2e91c876dc189)
1
2#pragma ident	"%Z%%M%	%I%	%E% SMI"
3
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 $
18
19
20set testdir [file dirname $argv0]
21source $testdir/tester.tcl
22
23
24# Create several tables to work with.
25#
26do_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}
35do_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}
44do_test trans-1.9 {
45  sqlite altdb test.db
46  execsql {SELECT b FROM one ORDER BY a} altdb
47} {one two three}
48do_test trans-1.10 {
49  execsql {SELECT b FROM two ORDER BY a} altdb
50} {I V X}
51integrity_check trans-1.11
52
53# Basic transactions
54#
55do_test trans-2.1 {
56  set v [catch {execsql {BEGIN}} msg]
57  lappend v $msg
58} {0 {}}
59do_test trans-2.2 {
60  set v [catch {execsql {END}} msg]
61  lappend v $msg
62} {0 {}}
63do_test trans-2.3 {
64  set v [catch {execsql {BEGIN TRANSACTION}} msg]
65  lappend v $msg
66} {0 {}}
67do_test trans-2.4 {
68  set v [catch {execsql {COMMIT TRANSACTION}} msg]
69  lappend v $msg
70} {0 {}}
71do_test trans-2.5 {
72  set v [catch {execsql {BEGIN TRANSACTION 'foo'}} msg]
73  lappend v $msg
74} {0 {}}
75do_test trans-2.6 {
76  set v [catch {execsql {ROLLBACK TRANSACTION 'foo'}} msg]
77  lappend v $msg
78} {0 {}}
79do_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}
87integrity_check trans-2.11
88
89# Check the locking behavior
90#
91do_test trans-3.1 {
92  execsql {
93    BEGIN;
94    SELECT a FROM one ORDER BY a;
95  }
96} {1 2 3}
97do_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}}
103do_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}}
109do_test trans-3.4 {
110  set v [catch {execsql {
111    INSERT INTO one VALUES(4,'four');
112  }} msg]
113  lappend v $msg
114} {0 {}}
115do_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}}
121do_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}}
127do_test trans-3.7 {
128  set v [catch {execsql {
129    INSERT INTO two VALUES(4,'IV');
130  }} msg]
131  lappend v $msg
132} {0 {}}
133do_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}}
139do_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}}
145do_test trans-3.10 {
146  execsql {END TRANSACTION}
147} {}
148do_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}}
154do_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}}
160do_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}}
166do_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}}
172integrity_check trans-3.15
173
174do_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}}
180do_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}}
186do_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}}
193do_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}}
199do_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}}
205do_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}}
212do_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}}
218do_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}}
224do_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}}
231do_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}}
237do_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}}
243integrity_check trans-4.12
244do_test trans-4.98 {
245  altdb close
246  execsql {
247    DROP TABLE one;
248    DROP TABLE two;
249  }
250} {}
251integrity_check trans-4.99
252
253# Check out the commit/rollback behavior of the database
254#
255do_test trans-5.1 {
256  execsql {SELECT name FROM sqlite_master WHERE type='table' ORDER BY name}
257} {}
258do_test trans-5.2 {
259  execsql {BEGIN TRANSACTION}
260  execsql {SELECT name FROM sqlite_master WHERE type='table' ORDER BY name}
261} {}
262do_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}
266do_test trans-5.4 {
267  execsql {SELECT a,b FROM one ORDER BY b}
268} {}
269do_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}
273do_test trans-5.6 {
274  execsql {ROLLBACK}
275  execsql {SELECT name FROM sqlite_master WHERE type='table' ORDER BY name}
276} {}
277do_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}}
283
284# Test commits and rollbacks of table CREATE TABLEs, CREATE INDEXs
285# DROP TABLEs and DROP INDEXs
286#
287do_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} {}
294do_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}
303do_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}
311do_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}
319do_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}
331do_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}
339do_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}
348do_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}
356do_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}
369do_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}
377do_test trans-5.18 {
378  execsql {
379    SELECT * FROM t2;
380  }
381} {1 2 3}
382do_test trans-5.19 {
383  execsql {
384    SELECT x FROM t2 WHERE y=2;
385  }
386} {1}
387do_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} {}
397do_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}}
403do_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}
411do_test trans-5.23 {
412  execsql {
413    SELECT * FROM t2;
414  }
415} {1 2 3}
416integrity_check trans-5.23
417
418
419# Try to DROP and CREATE tables and indices with the same name
420# within a transaction.  Make sure ROLLBACK works.
421#
422do_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}
432do_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} {}
442do_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}
448do_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}
458do_test trans-6.5 {
459  execsql2 {
460    ROLLBACK;
461    SELECT * FROM t1;
462  }
463} {p 1 q 2 r 3}
464do_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}
474do_test trans-6.7 {
475  catchsql {
476    COMMIT;
477    SELECT * FROM t1;
478  }
479} {1 {no such table: t1}}
480
481# Repeat on a table with an automatically generated index.
482#
483do_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}
494do_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} {}
503do_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}
509do_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}
519do_test trans-6.14 {
520  execsql2 {
521    ROLLBACK;
522    SELECT * FROM t1;
523  }
524} {p 1 q 2 r 3}
525do_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}
535do_test trans-6.16 {
536  catchsql {
537    COMMIT;
538    SELECT * FROM t1;
539  }
540} {1 {no such table: t1}}
541
542do_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}
550do_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}
556do_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}
564do_test trans-6.23 {
565  execsql {
566    SELECT * FROM t1 WHERE b<1;
567  }
568} {4 -5 -6 1 -2 -3}
569do_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}
577
578do_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}
586do_test trans-6.26 {
587  execsql {
588    SELECT * FROM t1 WHERE c<1;
589  }
590} {4 -5 -6 1 -2 -3}
591do_test trans-6.27 {
592  execsql {
593    ROLLBACK;
594    SELECT * FROM t1 WHERE b<1;
595  }
596} {4 -5 -6 1 -2 -3}
597do_test trans-6.28 {
598  execsql {
599    SELECT * FROM t1 WHERE c<1;
600  }
601} {1 -2 -3 4 -5 -6}
602
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#
607do_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}
618do_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}
624do_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}
632do_test trans-6.33 {
633  execsql {
634    SELECT * FROM t1 WHERE b<1;
635  }
636} {4 -5 -6 1 -2 -3}
637do_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}
645
646do_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}
654do_test trans-6.36 {
655  execsql {
656    SELECT * FROM t1 WHERE c<1;
657  }
658} {4 -5 -6 1 -2 -3}
659do_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}
665do_test trans-6.38 {
666  execsql {
667    ROLLBACK;
668    SELECT * FROM t1 WHERE b<1;
669  }
670} {4 -5 -6 1 -2 -3}
671do_test trans-6.39 {
672  execsql {
673    SELECT * FROM t1 WHERE c<1;
674  }
675} {1 -2 -3 4 -5 -6}
676integrity_check trans-6.40
677
678# Test to make sure rollback restores the database back to its original
679# state.
680#
681do_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}
696do_test trans-7.2 {
697  execsql {SELECT md5sum(x,y,z) FROM t2}
698} $checksum
699do_test trans-7.2.1 {
700  execsql {SELECT md5sum(type,name,tbl_name,rootpage,sql) FROM sqlite_master}
701} $checksum2
702do_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
710do_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
718do_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
726do_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
734do_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
743do_test trans-7.8 {
744  execsql {SELECT md5sum(type,name,tbl_name,rootpage,sql) FROM sqlite_master}
745} $checksum2
746do_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
755do_test trans-7.10 {
756  execsql {SELECT md5sum(type,name,tbl_name,rootpage,sql) FROM sqlite_master}
757} $checksum2
758do_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
770do_test trans-7.12 {
771  execsql {SELECT md5sum(type,name,tbl_name,rootpage,sql) FROM sqlite_master}
772} $checksum2
773do_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
781do_test trans-7.14 {
782  execsql {SELECT md5sum(type,name,tbl_name,rootpage,sql) FROM sqlite_master}
783} $checksum2
784integrity_check trans-7.15
785
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#
791set fd [open test.tcl w]
792puts $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}
802close $fd
803do_test trans-8.1 {
804  catch {exec [info nameofexec] test.tcl}
805  execsql {SELECT md5sum(x,y,z) FROM t2}
806} $checksum
807do_test trans-8.2 {
808  execsql {SELECT md5sum(type,name,tbl_name,rootpage,sql) FROM sqlite_master}
809} $checksum2
810integrity_check trans-8.3
811
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#
816do_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}
840
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#
848proc signature {} {
849  return [db eval {SELECT count(*), md5sum(x) FROM t3}]
850}
851
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#
857if {[info exists ISQUICK]} {
858  set limit 20
859} else {
860  set limit 40
861}
862
863# Do rollbacks.  Make sure the signature does not change.
864#
865for {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}
904
905finish_test
906