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