1: # 2008 December 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: #
12: # $Id: savepoint.test,v 1.1.1.1 2012/02/21 17:04:16 misho Exp $
13:
14: set testdir [file dirname $argv0]
15: source $testdir/tester.tcl
16: source $testdir/lock_common.tcl
17: source $testdir/malloc_common.tcl
18:
19: #----------------------------------------------------------------------
20: # The following tests - savepoint-1.* - test that the SAVEPOINT, RELEASE
21: # and ROLLBACK TO comands are correctly parsed, and that the auto-commit
22: # flag is correctly set and unset as a result.
23: #
24: do_test savepoint-1.1 {
25: wal_set_journal_mode
26: execsql {
27: SAVEPOINT sp1;
28: RELEASE sp1;
29: }
30: } {}
31: do_test savepoint-1.2 {
32: execsql {
33: SAVEPOINT sp1;
34: ROLLBACK TO sp1;
35: }
36: } {}
37: do_test savepoint-1.3 {
38: execsql { SAVEPOINT sp1 }
39: db close
40: } {}
41: sqlite3 db test.db
42: do_test savepoint-1.4.1 {
43: execsql {
44: SAVEPOINT sp1;
45: SAVEPOINT sp2;
46: RELEASE sp1;
47: }
48: sqlite3_get_autocommit db
49: } {1}
50: do_test savepoint-1.4.2 {
51: execsql {
52: SAVEPOINT sp1;
53: SAVEPOINT sp2;
54: RELEASE sp2;
55: }
56: sqlite3_get_autocommit db
57: } {0}
58: do_test savepoint-1.4.3 {
59: execsql { RELEASE sp1 }
60: sqlite3_get_autocommit db
61: } {1}
62: do_test savepoint-1.4.4 {
63: execsql {
64: SAVEPOINT sp1;
65: SAVEPOINT sp2;
66: ROLLBACK TO sp1;
67: }
68: sqlite3_get_autocommit db
69: } {0}
70: do_test savepoint-1.4.5 {
71: execsql { RELEASE SAVEPOINT sp1 }
72: sqlite3_get_autocommit db
73: } {1}
74: do_test savepoint-1.4.6 {
75: execsql {
76: SAVEPOINT sp1;
77: SAVEPOINT sp2;
78: SAVEPOINT sp3;
79: ROLLBACK TO SAVEPOINT sp3;
80: ROLLBACK TRANSACTION TO sp2;
81: ROLLBACK TRANSACTION TO SAVEPOINT sp1;
82: }
83: sqlite3_get_autocommit db
84: } {0}
85: do_test savepoint-1.4.7 {
86: execsql { RELEASE SAVEPOINT SP1 }
87: sqlite3_get_autocommit db
88: } {1}
89: do_test savepoint-1.5 {
90: execsql {
91: SAVEPOINT sp1;
92: ROLLBACK TO sp1;
93: }
94: } {}
95: do_test savepoint-1.6 {
96: execsql COMMIT
97: } {}
98: wal_check_journal_mode savepoint-1.7
99:
100: #------------------------------------------------------------------------
101: # These tests - savepoint-2.* - test rollbacks and releases of savepoints
102: # with a very simple data set.
103: #
104:
105: do_test savepoint-2.1 {
106: execsql {
107: CREATE TABLE t1(a, b, c);
108: BEGIN;
109: INSERT INTO t1 VALUES(1, 2, 3);
110: SAVEPOINT one;
111: UPDATE t1 SET a = 2, b = 3, c = 4;
112: }
113: execsql { SELECT * FROM t1 }
114: } {2 3 4}
115: do_test savepoint-2.2 {
116: execsql {
117: ROLLBACK TO one;
118: }
119: execsql { SELECT * FROM t1 }
120: } {1 2 3}
121: do_test savepoint-2.3 {
122: execsql {
123: INSERT INTO t1 VALUES(4, 5, 6);
124: }
125: execsql { SELECT * FROM t1 }
126: } {1 2 3 4 5 6}
127: do_test savepoint-2.4 {
128: execsql {
129: ROLLBACK TO one;
130: }
131: execsql { SELECT * FROM t1 }
132: } {1 2 3}
133:
134:
135: do_test savepoint-2.5 {
136: execsql {
137: INSERT INTO t1 VALUES(7, 8, 9);
138: SAVEPOINT two;
139: INSERT INTO t1 VALUES(10, 11, 12);
140: }
141: execsql { SELECT * FROM t1 }
142: } {1 2 3 7 8 9 10 11 12}
143: do_test savepoint-2.6 {
144: execsql {
145: ROLLBACK TO two;
146: }
147: execsql { SELECT * FROM t1 }
148: } {1 2 3 7 8 9}
149: do_test savepoint-2.7 {
150: execsql {
151: INSERT INTO t1 VALUES(10, 11, 12);
152: }
153: execsql { SELECT * FROM t1 }
154: } {1 2 3 7 8 9 10 11 12}
155: do_test savepoint-2.8 {
156: execsql {
157: ROLLBACK TO one;
158: }
159: execsql { SELECT * FROM t1 }
160: } {1 2 3}
161: do_test savepoint-2.9 {
162: execsql {
163: INSERT INTO t1 VALUES('a', 'b', 'c');
164: SAVEPOINT two;
165: INSERT INTO t1 VALUES('d', 'e', 'f');
166: }
167: execsql { SELECT * FROM t1 }
168: } {1 2 3 a b c d e f}
169: do_test savepoint-2.10 {
170: execsql {
171: RELEASE two;
172: }
173: execsql { SELECT * FROM t1 }
174: } {1 2 3 a b c d e f}
175: do_test savepoint-2.11 {
176: execsql {
177: ROLLBACK;
178: }
179: execsql { SELECT * FROM t1 }
180: } {}
181: wal_check_journal_mode savepoint-2.12
182:
183: #------------------------------------------------------------------------
184: # This block of tests - savepoint-3.* - test that when a transaction
185: # savepoint is rolled back, locks are not released from database files.
186: # And that when a transaction savepoint is released, they are released.
187: #
188: # These tests do not work in WAL mode. WAL mode does not take RESERVED
189: # locks on the database file.
190: #
191: if {[wal_is_wal_mode]==0} {
192: do_test savepoint-3.1 {
193: execsql { SAVEPOINT "transaction" }
194: execsql { PRAGMA lock_status }
195: } {main unlocked temp closed}
196:
197: do_test savepoint-3.2 {
198: execsql { INSERT INTO t1 VALUES(1, 2, 3) }
199: execsql { PRAGMA lock_status }
200: } {main reserved temp closed}
201:
202: do_test savepoint-3.3 {
203: execsql { ROLLBACK TO "transaction" }
204: execsql { PRAGMA lock_status }
205: } {main reserved temp closed}
206:
207: do_test savepoint-3.4 {
208: execsql { INSERT INTO t1 VALUES(1, 2, 3) }
209: execsql { PRAGMA lock_status }
210: } {main reserved temp closed}
211:
212: do_test savepoint-3.5 {
213: execsql { RELEASE "transaction" }
214: execsql { PRAGMA lock_status }
215: } {main unlocked temp closed}
216: }
217:
218: #------------------------------------------------------------------------
219: # Test that savepoints that include schema modifications are handled
220: # correctly. Test cases savepoint-4.*.
221: #
222: do_test savepoint-4.1 {
223: execsql {
224: CREATE TABLE t2(d, e, f);
225: SELECT sql FROM sqlite_master;
226: }
227: } {{CREATE TABLE t1(a, b, c)} {CREATE TABLE t2(d, e, f)}}
228: do_test savepoint-4.2 {
229: execsql {
230: BEGIN;
231: CREATE TABLE t3(g,h);
232: INSERT INTO t3 VALUES('I', 'II');
233: SAVEPOINT one;
234: DROP TABLE t3;
235: }
236: } {}
237: do_test savepoint-4.3 {
238: execsql {
239: CREATE TABLE t3(g, h, i);
240: INSERT INTO t3 VALUES('III', 'IV', 'V');
241: }
242: execsql {SELECT * FROM t3}
243: } {III IV V}
244: do_test savepoint-4.4 {
245: execsql { ROLLBACK TO one; }
246: execsql {SELECT * FROM t3}
247: } {I II}
248: do_test savepoint-4.5 {
249: execsql {
250: ROLLBACK;
251: SELECT sql FROM sqlite_master;
252: }
253: } {{CREATE TABLE t1(a, b, c)} {CREATE TABLE t2(d, e, f)}}
254:
255: do_test savepoint-4.6 {
256: execsql {
257: BEGIN;
258: INSERT INTO t1 VALUES('o', 't', 't');
259: SAVEPOINT sp1;
260: CREATE TABLE t3(a, b, c);
261: INSERT INTO t3 VALUES('z', 'y', 'x');
262: }
263: execsql {SELECT * FROM t3}
264: } {z y x}
265: do_test savepoint-4.7 {
266: execsql {
267: ROLLBACK TO sp1;
268: CREATE TABLE t3(a);
269: INSERT INTO t3 VALUES('value');
270: }
271: execsql {SELECT * FROM t3}
272: } {value}
273: do_test savepoint-4.8 {
274: execsql COMMIT
275: } {}
276: wal_check_journal_mode savepoint-4.9
277:
278: #------------------------------------------------------------------------
279: # Test some logic errors to do with the savepoint feature.
280: #
281:
282: ifcapable incrblob {
283: do_test savepoint-5.1.1 {
284: execsql {
285: CREATE TABLE blobs(x);
286: INSERT INTO blobs VALUES('a twentyeight character blob');
287: }
288: set fd [db incrblob blobs x 1]
289: puts -nonewline $fd "hello"
290: catchsql {SAVEPOINT abc}
291: } {1 {cannot open savepoint - SQL statements in progress}}
292: do_test savepoint-5.1.2 {
293: close $fd
294: catchsql {SAVEPOINT abc}
295: } {0 {}}
296:
297: do_test savepoint-5.2 {
298: execsql {RELEASE abc}
299: catchsql {RELEASE abc}
300: } {1 {no such savepoint: abc}}
301:
302: do_test savepoint-5.3.1 {
303: execsql {SAVEPOINT abc}
304: catchsql {ROLLBACK TO def}
305: } {1 {no such savepoint: def}}
306: do_test savepoint-5.3.2 {
307: execsql {SAVEPOINT def}
308: set fd [db incrblob -readonly blobs x 1]
309: catchsql {ROLLBACK TO def}
310: } {1 {cannot rollback savepoint - SQL statements in progress}}
311: do_test savepoint-5.3.3 {
312: catchsql {RELEASE def}
313: } {0 {}}
314: do_test savepoint-5.3.4 {
315: close $fd
316: execsql {savepoint def}
317: set fd [db incrblob blobs x 1]
318: catchsql {release def}
319: } {1 {cannot release savepoint - SQL statements in progress}}
320: do_test savepoint-5.3.5 {
321: close $fd
322: execsql {release abc}
323: } {}
324:
325: # Rollback mode:
326: #
327: # Open a savepoint transaction and insert a row into the database. Then,
328: # using a second database handle, open a read-only transaction on the
329: # database file. Check that the savepoint transaction cannot be committed
330: # until after the read-only transaction has been closed.
331: #
332: # WAL mode:
333: #
334: # As above, except that the savepoint transaction can be successfully
335: # committed before the read-only transaction has been closed.
336: #
337: do_test savepoint-5.4.1 {
338: execsql {
339: SAVEPOINT main;
340: INSERT INTO blobs VALUES('another blob');
341: }
342: } {}
343: do_test savepoint-5.4.2 {
344: sqlite3 db2 test.db
345: execsql { BEGIN ; SELECT count(*) FROM blobs } db2
346: } {1}
347: if {[wal_is_wal_mode]} {
348: do_test savepoint-5.4.3 { catchsql "RELEASE main" } {0 {}}
349: do_test savepoint-5.4.4 { db2 close } {}
350: } else {
351: do_test savepoint-5.4.3 {
352: catchsql { RELEASE main }
353: } {1 {database is locked}}
354: do_test savepoint-5.4.4 {
355: db2 close
356: catchsql { RELEASE main }
357: } {0 {}}
358: }
359: do_test savepoint-5.4.5 {
360: execsql { SELECT x FROM blobs WHERE rowid = 2 }
361: } {{another blob}}
362: do_test savepoint-5.4.6 {
363: execsql { SELECT count(*) FROM blobs }
364: } {2}
365: }
366: wal_check_journal_mode savepoint-5.5
367:
368: #-------------------------------------------------------------------------
369: # The following tests, savepoint-6.*, test an incr-vacuum inside of a
370: # couple of nested savepoints.
371: #
372: ifcapable {autovacuum && pragma} {
373: db close
374: forcedelete test.db
375: sqlite3 db test.db
376:
377: do_test savepoint-6.1 {
378: execsql { PRAGMA auto_vacuum = incremental }
379: wal_set_journal_mode
380: execsql {
381: CREATE TABLE t1(a, b, c);
382: CREATE INDEX i1 ON t1(a, b);
383: BEGIN;
384: INSERT INTO t1 VALUES(randstr(10,400),randstr(10,400),randstr(10,400));
385: }
386: set r "randstr(10,400)"
387: for {set ii 0} {$ii < 10} {incr ii} {
388: execsql "INSERT INTO t1 SELECT $r, $r, $r FROM t1"
389: }
390: execsql { COMMIT }
391: } {}
392:
393: integrity_check savepoint-6.2
394:
395: do_test savepoint-6.3 {
396: execsql {
397: PRAGMA cache_size = 10;
398: BEGIN;
399: UPDATE t1 SET a = randstr(10,10) WHERE (rowid%4)==0;
400: SAVEPOINT one;
401: DELETE FROM t1 WHERE rowid%2;
402: PRAGMA incr_vacuum;
403: SAVEPOINT two;
404: INSERT INTO t1 SELECT randstr(10,400), randstr(10,400), c FROM t1;
405: DELETE FROM t1 WHERE rowid%2;
406: PRAGMA incr_vacuum;
407: ROLLBACK TO one;
408: COMMIT;
409: }
410: } {}
411:
412: integrity_check savepoint-6.4
413:
414: wal_check_journal_mode savepoint-6.5
415: }
416:
417: #-------------------------------------------------------------------------
418: # The following tests, savepoint-7.*, attempt to break the logic
419: # surrounding savepoints by growing and shrinking the database file.
420: #
421: db close
422: forcedelete test.db
423: sqlite3 db test.db
424:
425: do_test savepoint-7.1 {
426: execsql { PRAGMA auto_vacuum = incremental }
427: wal_set_journal_mode
428: execsql {
429: PRAGMA cache_size = 10;
430: BEGIN;
431: CREATE TABLE t1(a PRIMARY KEY, b);
432: INSERT INTO t1(a) VALUES('alligator');
433: INSERT INTO t1(a) VALUES('angelfish');
434: INSERT INTO t1(a) VALUES('ant');
435: INSERT INTO t1(a) VALUES('antelope');
436: INSERT INTO t1(a) VALUES('ape');
437: INSERT INTO t1(a) VALUES('baboon');
438: INSERT INTO t1(a) VALUES('badger');
439: INSERT INTO t1(a) VALUES('bear');
440: INSERT INTO t1(a) VALUES('beetle');
441: INSERT INTO t1(a) VALUES('bird');
442: INSERT INTO t1(a) VALUES('bison');
443: UPDATE t1 SET b = randstr(1000,1000);
444: UPDATE t1 SET b = b||randstr(1000,1000);
445: UPDATE t1 SET b = b||randstr(1000,1000);
446: UPDATE t1 SET b = b||randstr(10,1000);
447: COMMIT;
448: }
449: expr ([execsql { PRAGMA page_count }] > 20)
450: } {1}
451: do_test savepoint-7.2.1 {
452: execsql {
453: BEGIN;
454: SAVEPOINT one;
455: CREATE TABLE t2(a, b);
456: INSERT INTO t2 SELECT a, b FROM t1;
457: ROLLBACK TO one;
458: }
459: execsql {
460: PRAGMA integrity_check;
461: }
462: } {ok}
463: do_test savepoint-7.2.2 {
464: execsql {
465: COMMIT;
466: PRAGMA integrity_check;
467: }
468: } {ok}
469:
470: do_test savepoint-7.3.1 {
471: execsql {
472: CREATE TABLE t2(a, b);
473: INSERT INTO t2 SELECT a, b FROM t1;
474: }
475: } {}
476: do_test savepoint-7.3.2 {
477: execsql {
478: BEGIN;
479: SAVEPOINT one;
480: DELETE FROM t2;
481: PRAGMA incremental_vacuum;
482: SAVEPOINT two;
483: INSERT INTO t2 SELECT a, b FROM t1;
484: ROLLBACK TO two;
485: COMMIT;
486: }
487: execsql { PRAGMA integrity_check }
488: } {ok}
489: wal_check_journal_mode savepoint-7.3.3
490:
491: do_test savepoint-7.4.1 {
492: db close
493: forcedelete test.db
494: sqlite3 db test.db
495: execsql { PRAGMA auto_vacuum = incremental }
496: wal_set_journal_mode
497: execsql {
498: CREATE TABLE t1(a, b, PRIMARY KEY(a, b));
499: INSERT INTO t1 VALUES(randstr(1000,1000), randstr(1000,1000));
500: BEGIN;
501: DELETE FROM t1;
502: SAVEPOINT one;
503: PRAGMA incremental_vacuum;
504: ROLLBACK TO one;
505: COMMIT;
506: }
507:
508: execsql { PRAGMA integrity_check }
509: } {ok}
510:
511: do_test savepoint-7.5.1 {
512: execsql {
513: PRAGMA incremental_vacuum;
514: CREATE TABLE t5(x, y);
515: INSERT INTO t5 VALUES(1, randstr(1000,1000));
516: INSERT INTO t5 VALUES(2, randstr(1000,1000));
517: INSERT INTO t5 VALUES(3, randstr(1000,1000));
518:
519: BEGIN;
520: INSERT INTO t5 VALUES(4, randstr(1000,1000));
521: INSERT INTO t5 VALUES(5, randstr(1000,1000));
522: DELETE FROM t5 WHERE x=1 OR x=2;
523: SAVEPOINT one;
524: PRAGMA incremental_vacuum;
525: SAVEPOINT two;
526: INSERT INTO t5 VALUES(1, randstr(1000,1000));
527: INSERT INTO t5 VALUES(2, randstr(1000,1000));
528: ROLLBACK TO two;
529: ROLLBACK TO one;
530: COMMIT;
531: PRAGMA integrity_check;
532: }
533: } {ok}
534: do_test savepoint-7.5.2 {
535: execsql {
536: DROP TABLE t5;
537: }
538: } {}
539: wal_check_journal_mode savepoint-7.5.3
540:
541: # Test oddly named and quoted savepoints.
542: #
543: do_test savepoint-8-1 {
544: execsql { SAVEPOINT "save1" }
545: execsql { RELEASE save1 }
546: } {}
547: do_test savepoint-8-2 {
548: execsql { SAVEPOINT "Including whitespace " }
549: execsql { RELEASE "including Whitespace " }
550: } {}
551:
552: # Test that the authorization callback works.
553: #
554: ifcapable auth {
555: proc auth {args} {
556: eval lappend ::authdata $args
557: return SQLITE_OK
558: }
559: db auth auth
560:
561: do_test savepoint-9.1 {
562: set ::authdata [list]
563: execsql { SAVEPOINT sp1 }
564: set ::authdata
565: } {SQLITE_SAVEPOINT BEGIN sp1 {} {}}
566: do_test savepoint-9.2 {
567: set ::authdata [list]
568: execsql { ROLLBACK TO sp1 }
569: set ::authdata
570: } {SQLITE_SAVEPOINT ROLLBACK sp1 {} {}}
571: do_test savepoint-9.3 {
572: set ::authdata [list]
573: execsql { RELEASE sp1 }
574: set ::authdata
575: } {SQLITE_SAVEPOINT RELEASE sp1 {} {}}
576:
577: proc auth {args} {
578: eval lappend ::authdata $args
579: return SQLITE_DENY
580: }
581: db auth auth
582:
583: do_test savepoint-9.4 {
584: set ::authdata [list]
585: set res [catchsql { SAVEPOINT sp1 }]
586: concat $::authdata $res
587: } {SQLITE_SAVEPOINT BEGIN sp1 {} {} 1 {not authorized}}
588: do_test savepoint-9.5 {
589: set ::authdata [list]
590: set res [catchsql { ROLLBACK TO sp1 }]
591: concat $::authdata $res
592: } {SQLITE_SAVEPOINT ROLLBACK sp1 {} {} 1 {not authorized}}
593: do_test savepoint-9.6 {
594: set ::authdata [list]
595: set res [catchsql { RELEASE sp1 }]
596: concat $::authdata $res
597: } {SQLITE_SAVEPOINT RELEASE sp1 {} {} 1 {not authorized}}
598:
599: catch { db eval ROLLBACK }
600: db auth ""
601: }
602:
603: #-------------------------------------------------------------------------
604: # The following tests - savepoint-10.* - test the interaction of
605: # savepoints and ATTACH statements.
606: #
607:
608: # First make sure it is not possible to attach or detach a database while
609: # a savepoint is open (it is not possible if any transaction is open).
610: #
611: do_test savepoint-10.1.1 {
612: catchsql {
613: SAVEPOINT one;
614: ATTACH 'test2.db' AS aux;
615: }
616: } {1 {cannot ATTACH database within transaction}}
617: do_test savepoint-10.1.2 {
618: execsql {
619: RELEASE one;
620: ATTACH 'test2.db' AS aux;
621: }
622: catchsql {
623: SAVEPOINT one;
624: DETACH aux;
625: }
626: } {1 {cannot DETACH database within transaction}}
627: do_test savepoint-10.1.3 {
628: execsql {
629: RELEASE one;
630: DETACH aux;
631: }
632: } {}
633:
634: # The lock state of the TEMP database can vary if SQLITE_TEMP_STORE=3
635: # And the following set of tests is only really interested in the status
636: # of the aux1 and aux2 locks. So record the current lock status of
637: # TEMP for use in the answers.
638: set templockstate [lindex [db eval {PRAGMA lock_status}] 3]
639:
640:
641: if {[wal_is_wal_mode]==0} {
642: do_test savepoint-10.2.1 {
643: forcedelete test3.db
644: forcedelete test2.db
645: execsql {
646: ATTACH 'test2.db' AS aux1;
647: ATTACH 'test3.db' AS aux2;
648: DROP TABLE t1;
649: CREATE TABLE main.t1(x, y);
650: CREATE TABLE aux1.t2(x, y);
651: CREATE TABLE aux2.t3(x, y);
652: SELECT name FROM sqlite_master
653: UNION ALL
654: SELECT name FROM aux1.sqlite_master
655: UNION ALL
656: SELECT name FROM aux2.sqlite_master;
657: }
658: } {t1 t2 t3}
659: do_test savepoint-10.2.2 {
660: execsql { PRAGMA lock_status }
661: } [list main unlocked temp $templockstate aux1 unlocked aux2 unlocked]
662:
663: do_test savepoint-10.2.3 {
664: execsql {
665: SAVEPOINT one;
666: INSERT INTO t1 VALUES(1, 2);
667: PRAGMA lock_status;
668: }
669: } [list main reserved temp $templockstate aux1 unlocked aux2 unlocked]
670: do_test savepoint-10.2.4 {
671: execsql {
672: INSERT INTO t3 VALUES(3, 4);
673: PRAGMA lock_status;
674: }
675: } [list main reserved temp $templockstate aux1 unlocked aux2 reserved]
676: do_test savepoint-10.2.5 {
677: execsql {
678: SAVEPOINT two;
679: INSERT INTO t2 VALUES(5, 6);
680: PRAGMA lock_status;
681: }
682: } [list main reserved temp $templockstate aux1 reserved aux2 reserved]
683: do_test savepoint-10.2.6 {
684: execsql { SELECT * FROM t2 }
685: } {5 6}
686: do_test savepoint-10.2.7 {
687: execsql { ROLLBACK TO two }
688: execsql { SELECT * FROM t2 }
689: } {}
690: do_test savepoint-10.2.8 {
691: execsql { PRAGMA lock_status }
692: } [list main reserved temp $templockstate aux1 reserved aux2 reserved]
693: do_test savepoint-10.2.9 {
694: execsql { SELECT 'a', * FROM t1 UNION ALL SELECT 'b', * FROM t3 }
695: } {a 1 2 b 3 4}
696: do_test savepoint-10.2.9 {
697: execsql {
698: INSERT INTO t2 VALUES(5, 6);
699: RELEASE one;
700: }
701: execsql {
702: SELECT * FROM t1;
703: SELECT * FROM t2;
704: SELECT * FROM t3;
705: }
706: } {1 2 5 6 3 4}
707: do_test savepoint-10.2.9 {
708: execsql { PRAGMA lock_status }
709: } [list main unlocked temp $templockstate aux1 unlocked aux2 unlocked]
710:
711: do_test savepoint-10.2.10 {
712: execsql {
713: SAVEPOINT one;
714: INSERT INTO t1 VALUES('a', 'b');
715: SAVEPOINT two;
716: INSERT INTO t2 VALUES('c', 'd');
717: SAVEPOINT three;
718: INSERT INTO t3 VALUES('e', 'f');
719: }
720: execsql {
721: SELECT * FROM t1;
722: SELECT * FROM t2;
723: SELECT * FROM t3;
724: }
725: } {1 2 a b 5 6 c d 3 4 e f}
726: do_test savepoint-10.2.11 {
727: execsql { ROLLBACK TO two }
728: execsql {
729: SELECT * FROM t1;
730: SELECT * FROM t2;
731: SELECT * FROM t3;
732: }
733: } {1 2 a b 5 6 3 4}
734: do_test savepoint-10.2.12 {
735: execsql {
736: INSERT INTO t3 VALUES('g', 'h');
737: ROLLBACK TO two;
738: }
739: execsql {
740: SELECT * FROM t1;
741: SELECT * FROM t2;
742: SELECT * FROM t3;
743: }
744: } {1 2 a b 5 6 3 4}
745: do_test savepoint-10.2.13 {
746: execsql { ROLLBACK }
747: execsql {
748: SELECT * FROM t1;
749: SELECT * FROM t2;
750: SELECT * FROM t3;
751: }
752: } {1 2 5 6 3 4}
753: do_test savepoint-10.2.14 {
754: execsql { PRAGMA lock_status }
755: } [list main unlocked temp $templockstate aux1 unlocked aux2 unlocked]
756: }
757:
758: #-------------------------------------------------------------------------
759: # The following tests - savepoint-11.* - test the interaction of
760: # savepoints and creating or dropping tables and indexes in
761: # auto-vacuum mode.
762: #
763: do_test savepoint-11.1 {
764: db close
765: forcedelete test.db
766: sqlite3 db test.db
767: execsql { PRAGMA auto_vacuum = full; }
768: wal_set_journal_mode
769: execsql {
770: CREATE TABLE t1(a, b, UNIQUE(a, b));
771: INSERT INTO t1 VALUES(1, randstr(1000,1000));
772: INSERT INTO t1 VALUES(2, randstr(1000,1000));
773: }
774: } {}
775: do_test savepoint-11.2 {
776: execsql {
777: SAVEPOINT one;
778: CREATE TABLE t2(a, b, UNIQUE(a, b));
779: SAVEPOINT two;
780: CREATE TABLE t3(a, b, UNIQUE(a, b));
781: }
782: } {}
783: integrity_check savepoint-11.3
784: do_test savepoint-11.4 {
785: execsql { ROLLBACK TO two }
786: } {}
787: integrity_check savepoint-11.5
788: do_test savepoint-11.6 {
789: execsql {
790: CREATE TABLE t3(a, b, UNIQUE(a, b));
791: ROLLBACK TO one;
792: }
793: } {}
794: integrity_check savepoint-11.7
795: do_test savepoint-11.8 {
796: execsql { ROLLBACK }
797: execsql { PRAGMA wal_checkpoint }
798: file size test.db
799: } {8192}
800:
801: do_test savepoint-11.9 {
802: execsql {
803: DROP TABLE IF EXISTS t1;
804: DROP TABLE IF EXISTS t2;
805: DROP TABLE IF EXISTS t3;
806: }
807: } {}
808: do_test savepoint-11.10 {
809: execsql {
810: BEGIN;
811: CREATE TABLE t1(a, b);
812: CREATE TABLE t2(x, y);
813: INSERT INTO t2 VALUES(1, 2);
814: SAVEPOINT one;
815: INSERT INTO t2 VALUES(3, 4);
816: SAVEPOINT two;
817: DROP TABLE t1;
818: ROLLBACK TO two;
819: }
820: execsql {SELECT * FROM t2}
821: } {1 2 3 4}
822: do_test savepoint-11.11 {
823: execsql COMMIT
824: } {}
825: do_test savepoint-11.12 {
826: execsql {SELECT * FROM t2}
827: } {1 2 3 4}
828: wal_check_journal_mode savepoint-11.13
829:
830: #-------------------------------------------------------------------------
831: # The following tests - savepoint-12.* - test the interaction of
832: # savepoints and "ON CONFLICT ROLLBACK" clauses.
833: #
834: do_test savepoint-12.1 {
835: execsql {
836: CREATE TABLE t4(a PRIMARY KEY, b);
837: INSERT INTO t4 VALUES(1, 'one');
838: }
839: } {}
840: do_test savepoint-12.2 {
841: # The final statement of the following SQL hits a constraint when the
842: # conflict handling mode is "OR ROLLBACK" and there are a couple of
843: # open savepoints. At one point this would fail to clear the internal
844: # record of the open savepoints, resulting in an assert() failure
845: # later on.
846: #
847: catchsql {
848: BEGIN;
849: INSERT INTO t4 VALUES(2, 'two');
850: SAVEPOINT sp1;
851: INSERT INTO t4 VALUES(3, 'three');
852: SAVEPOINT sp2;
853: INSERT OR ROLLBACK INTO t4 VALUES(1, 'one');
854: }
855: } {1 {column a is not unique}}
856: do_test savepoint-12.3 {
857: sqlite3_get_autocommit db
858: } {1}
859: do_test savepoint-12.4 {
860: execsql { SAVEPOINT one }
861: } {}
862: wal_check_journal_mode savepoint-12.5
863:
864: #-------------------------------------------------------------------------
865: # The following tests - savepoint-13.* - test the interaction of
866: # savepoints and "journal_mode = off".
867: #
868: if {[wal_is_wal_mode]==0} {
869: do_test savepoint-13.1 {
870: db close
871: catch {forcedelete test.db}
872: sqlite3 db test.db
873: execsql {
874: BEGIN;
875: CREATE TABLE t1(a PRIMARY KEY, b);
876: INSERT INTO t1 VALUES(1, 2);
877: COMMIT;
878: PRAGMA journal_mode = off;
879: }
880: } {off}
881: do_test savepoint-13.2 {
882: execsql {
883: BEGIN;
884: INSERT INTO t1 VALUES(3, 4);
885: INSERT INTO t1 SELECT a+4,b+4 FROM t1;
886: COMMIT;
887: }
888: } {}
889: do_test savepoint-13.3 {
890: execsql {
891: BEGIN;
892: INSERT INTO t1 VALUES(9, 10);
893: SAVEPOINT s1;
894: INSERT INTO t1 VALUES(11, 12);
895: COMMIT;
896: }
897: } {}
898: do_test savepoint-13.4 {
899: execsql {
900: BEGIN;
901: INSERT INTO t1 VALUES(13, 14);
902: SAVEPOINT s1;
903: INSERT INTO t1 VALUES(15, 16);
904: ROLLBACK TO s1;
905: ROLLBACK;
906: SELECT * FROM t1;
907: }
908: } {1 2 3 4 5 6 7 8 9 10 11 12}
909: }
910:
911: db close
912: delete_file test.db
913: do_multiclient_test tn {
914: do_test savepoint-14.$tn.1 {
915: sql1 {
916: CREATE TABLE foo(x);
917: INSERT INTO foo VALUES(1);
918: INSERT INTO foo VALUES(2);
919: }
920: sql2 {
921: BEGIN;
922: SELECT * FROM foo;
923: }
924: } {1 2}
925: do_test savepoint-14.$tn.2 {
926: sql1 {
927: SAVEPOINT one;
928: INSERT INTO foo VALUES(1);
929: }
930: csql1 { RELEASE one }
931: } {1 {database is locked}}
932: do_test savepoint-14.$tn.3 {
933: sql1 { ROLLBACK TO one }
934: sql2 { COMMIT }
935: sql1 { RELEASE one }
936: } {}
937:
938: do_test savepoint-14.$tn.4 {
939: sql2 {
940: BEGIN;
941: SELECT * FROM foo;
942: }
943: } {1 2}
944: do_test savepoint-14.$tn.5 {
945: sql1 {
946: SAVEPOINT one;
947: INSERT INTO foo VALUES(1);
948: }
949: csql1 { RELEASE one }
950: } {1 {database is locked}}
951: do_test savepoint-14.$tn.6 {
952: sql2 { COMMIT }
953: sql1 {
954: ROLLBACK TO one;
955: INSERT INTO foo VALUES(3);
956: INSERT INTO foo VALUES(4);
957: INSERT INTO foo VALUES(5);
958: RELEASE one;
959: }
960: } {}
961: do_test savepoint-14.$tn.7 {
962: sql2 { CREATE INDEX fooidx ON foo(x); }
963: sql3 { PRAGMA integrity_check }
964: } {ok}
965: }
966:
967: do_multiclient_test tn {
968: do_test savepoint-15.$tn.1 {
969: sql1 {
970: CREATE TABLE foo(x);
971: INSERT INTO foo VALUES(1);
972: INSERT INTO foo VALUES(2);
973: }
974: sql2 { BEGIN; SELECT * FROM foo; }
975: } {1 2}
976: do_test savepoint-15.$tn.2 {
977: sql1 {
978: PRAGMA locking_mode = EXCLUSIVE;
979: BEGIN;
980: INSERT INTO foo VALUES(3);
981: }
982: csql1 { COMMIT }
983: } {1 {database is locked}}
984: do_test savepoint-15.$tn.3 {
985: sql1 { ROLLBACK }
986: sql2 { COMMIT }
987: sql1 {
988: INSERT INTO foo VALUES(3);
989: PRAGMA locking_mode = NORMAL;
990: INSERT INTO foo VALUES(4);
991: }
992: sql2 { CREATE INDEX fooidx ON foo(x); }
993: sql3 { PRAGMA integrity_check }
994: } {ok}
995: }
996:
997: do_multiclient_test tn {
998: do_test savepoint-16.$tn.1 {
999: sql1 {
1000: CREATE TABLE foo(x);
1001: INSERT INTO foo VALUES(1);
1002: INSERT INTO foo VALUES(2);
1003: }
1004: } {}
1005: do_test savepoint-16.$tn.2 {
1006:
1007: db eval {SELECT * FROM foo} {
1008: sql1 { INSERT INTO foo VALUES(3) }
1009: sql2 { SELECT * FROM foo }
1010: sql1 { INSERT INTO foo VALUES(4) }
1011: break
1012: }
1013:
1014: sql2 { CREATE INDEX fooidx ON foo(x); }
1015: sql3 { PRAGMA integrity_check }
1016: } {ok}
1017: do_test savepoint-16.$tn.3 {
1018: sql1 { SELECT * FROM foo }
1019: } {1 2 3 4}
1020: }
1021:
1022: #-------------------------------------------------------------------------
1023: # This next block of tests verifies that a problem reported on the mailing
1024: # list has been resolved. At one point the second "CREATE TABLE t6" would
1025: # fail as table t6 still existed in the internal cache of the db schema
1026: # (even though it had been removed from the database by the ROLLBACK
1027: # command).
1028: #
1029: sqlite3 db test.db
1030: do_execsql_test savepoint-17.1 {
1031: BEGIN;
1032: CREATE TABLE t6(a, b);
1033: INSERT INTO t6 VALUES(1, 2);
1034: SAVEPOINT one;
1035: INSERT INTO t6 VALUES(3, 4);
1036: ROLLBACK TO one;
1037: SELECT * FROM t6;
1038: ROLLBACK;
1039: } {1 2}
1040:
1041: do_execsql_test savepoint-17.2 {
1042: CREATE TABLE t6(a, b);
1043: } {}
1044:
1045: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to savepoint.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