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