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