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