wasCSharpSQLite – Blame information for rev 4

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