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