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.1.1.1 2012/02/21 17:04:16 misho 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
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to trans.test CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test