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# 619# UPDATE 2017-07-26: It is not possible to ATTACH and DETACH within a 620# a transaction. 621# 622do_test savepoint-10.1.1 { 623 catchsql { 624 SAVEPOINT one; 625 ATTACH 'test2.db' AS aux; 626 DETACH aux; 627 } 628} {0 {}} 629do_test savepoint-10.1.2 { 630 execsql { 631 RELEASE one; 632 ATTACH 'test2.db' AS aux; 633 } 634 catchsql { 635 SAVEPOINT one; 636 DETACH aux; 637 ATTACH 'test2.db' AS aux; 638 } 639} {0 {}} 640do_test savepoint-10.1.3 { 641 execsql { 642 RELEASE one; 643 DETACH aux; 644 } 645} {} 646 647# The lock state of the TEMP database can vary if SQLITE_TEMP_STORE=3 648# And the following set of tests is only really interested in the status 649# of the aux1 and aux2 locks. So record the current lock status of 650# TEMP for use in the answers. 651set templockstate [lindex [db eval {PRAGMA lock_status}] 3] 652 653 654if {[wal_is_wal_mode]==0} { 655 do_test savepoint-10.2.1 { 656 forcedelete test3.db 657 forcedelete test2.db 658 execsql { 659 ATTACH 'test2.db' AS aux1; 660 ATTACH 'test3.db' AS aux2; 661 DROP TABLE t1; 662 CREATE TABLE main.t1(x, y); 663 CREATE TABLE aux1.t2(x, y); 664 CREATE TABLE aux2.t3(x, y); 665 SELECT name FROM sqlite_master; 666 SELECT name FROM aux1.sqlite_master; 667 SELECT name FROM aux2.sqlite_master; 668 } 669 } {t1 t2 t3} 670 do_test savepoint-10.2.2 { 671 execsql { PRAGMA lock_status } 672 } [list main unlocked temp $templockstate aux1 unlocked aux2 unlocked] 673 674 do_test savepoint-10.2.3 { 675 execsql { 676 SAVEPOINT one; 677 INSERT INTO t1 VALUES(1, 2); 678 PRAGMA lock_status; 679 } 680 } [list main reserved temp $templockstate aux1 unlocked aux2 unlocked] 681 do_test savepoint-10.2.4 { 682 execsql { 683 INSERT INTO t3 VALUES(3, 4); 684 PRAGMA lock_status; 685 } 686 } [list main reserved temp $templockstate aux1 unlocked aux2 reserved] 687 do_test savepoint-10.2.5 { 688 execsql { 689 SAVEPOINT two; 690 INSERT INTO t2 VALUES(5, 6); 691 PRAGMA lock_status; 692 } 693 } [list main reserved temp $templockstate aux1 reserved aux2 reserved] 694 do_test savepoint-10.2.6 { 695 execsql { SELECT * FROM t2 } 696 } {5 6} 697 do_test savepoint-10.2.7 { 698 execsql { ROLLBACK TO two } 699 execsql { SELECT * FROM t2 } 700 } {} 701 do_test savepoint-10.2.8 { 702 execsql { PRAGMA lock_status } 703 } [list main reserved temp $templockstate aux1 reserved aux2 reserved] 704 do_test savepoint-10.2.9 { 705 execsql { SELECT 'a', * FROM t1 ; SELECT 'b', * FROM t3 } 706 } {a 1 2 b 3 4} 707 do_test savepoint-10.2.9 { 708 execsql { 709 INSERT INTO t2 VALUES(5, 6); 710 RELEASE one; 711 } 712 execsql { 713 SELECT * FROM t1; 714 SELECT * FROM t2; 715 SELECT * FROM t3; 716 } 717 } {1 2 5 6 3 4} 718 do_test savepoint-10.2.9 { 719 execsql { PRAGMA lock_status } 720 } [list main unlocked temp $templockstate aux1 unlocked aux2 unlocked] 721 722 do_test savepoint-10.2.10 { 723 execsql { 724 SAVEPOINT one; 725 INSERT INTO t1 VALUES('a', 'b'); 726 SAVEPOINT two; 727 INSERT INTO t2 VALUES('c', 'd'); 728 SAVEPOINT three; 729 INSERT INTO t3 VALUES('e', 'f'); 730 } 731 execsql { 732 SELECT * FROM t1; 733 SELECT * FROM t2; 734 SELECT * FROM t3; 735 } 736 } {1 2 a b 5 6 c d 3 4 e f} 737 do_test savepoint-10.2.11 { 738 execsql { ROLLBACK TO two } 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.12 { 746 execsql { 747 INSERT INTO t3 VALUES('g', 'h'); 748 ROLLBACK TO two; 749 } 750 execsql { 751 SELECT * FROM t1; 752 SELECT * FROM t2; 753 SELECT * FROM t3; 754 } 755 } {1 2 a b 5 6 3 4} 756 do_test savepoint-10.2.13 { 757 execsql { ROLLBACK } 758 execsql { 759 SELECT * FROM t1; 760 SELECT * FROM t2; 761 SELECT * FROM t3; 762 } 763 } {1 2 5 6 3 4} 764 do_test savepoint-10.2.14 { 765 execsql { PRAGMA lock_status } 766 } [list main unlocked temp $templockstate aux1 unlocked aux2 unlocked] 767} 768 769#------------------------------------------------------------------------- 770# The following tests - savepoint-11.* - test the interaction of 771# savepoints and creating or dropping tables and indexes in 772# auto-vacuum mode. 773# 774do_test savepoint-11.1 { 775 db close 776 forcedelete test.db 777 sqlite3 db test.db 778 execsql { PRAGMA auto_vacuum = full; } 779 wal_set_journal_mode 780 execsql { 781 CREATE TABLE t1(a, b, UNIQUE(a, b)); 782 INSERT INTO t1 VALUES(1, randstr(1000,1000)); 783 INSERT INTO t1 VALUES(2, randstr(1000,1000)); 784 } 785} {} 786do_test savepoint-11.2 { 787 execsql { 788 SAVEPOINT one; 789 CREATE TABLE t2(a, b, UNIQUE(a, b)); 790 SAVEPOINT two; 791 CREATE TABLE t3(a, b, UNIQUE(a, b)); 792 } 793} {} 794integrity_check savepoint-11.3 795do_test savepoint-11.4 { 796 execsql { ROLLBACK TO two } 797} {} 798integrity_check savepoint-11.5 799do_test savepoint-11.6 { 800 execsql { 801 CREATE TABLE t3(a, b, UNIQUE(a, b)); 802 ROLLBACK TO one; 803 } 804} {} 805integrity_check savepoint-11.7 806do_test savepoint-11.8 { 807 execsql { ROLLBACK } 808 execsql { PRAGMA wal_checkpoint } 809 file size test.db 810} {8192} 811 812do_test savepoint-11.9 { 813 execsql { 814 DROP TABLE IF EXISTS t1; 815 DROP TABLE IF EXISTS t2; 816 DROP TABLE IF EXISTS t3; 817 } 818} {} 819do_test savepoint-11.10 { 820 execsql { 821 BEGIN; 822 CREATE TABLE t1(a, b); 823 CREATE TABLE t2(x, y); 824 INSERT INTO t2 VALUES(1, 2); 825 SAVEPOINT one; 826 INSERT INTO t2 VALUES(3, 4); 827 SAVEPOINT two; 828 DROP TABLE t1; 829 ROLLBACK TO two; 830 } 831 execsql {SELECT * FROM t2} 832} {1 2 3 4} 833do_test savepoint-11.11 { 834 execsql COMMIT 835} {} 836do_test savepoint-11.12 { 837 execsql {SELECT * FROM t2} 838} {1 2 3 4} 839wal_check_journal_mode savepoint-11.13 840 841#------------------------------------------------------------------------- 842# The following tests - savepoint-12.* - test the interaction of 843# savepoints and "ON CONFLICT ROLLBACK" clauses. 844# 845do_test savepoint-12.1 { 846 execsql { 847 CREATE TABLE t4(a PRIMARY KEY, b); 848 INSERT INTO t4 VALUES(1, 'one'); 849 } 850} {} 851do_test savepoint-12.2 { 852 # The final statement of the following SQL hits a constraint when the 853 # conflict handling mode is "OR ROLLBACK" and there are a couple of 854 # open savepoints. At one point this would fail to clear the internal 855 # record of the open savepoints, resulting in an assert() failure 856 # later on. 857 # 858 catchsql { 859 BEGIN; 860 INSERT INTO t4 VALUES(2, 'two'); 861 SAVEPOINT sp1; 862 INSERT INTO t4 VALUES(3, 'three'); 863 SAVEPOINT sp2; 864 INSERT OR ROLLBACK INTO t4 VALUES(1, 'one'); 865 } 866} {1 {UNIQUE constraint failed: t4.a}} 867do_test savepoint-12.3 { 868 sqlite3_get_autocommit db 869} {1} 870do_test savepoint-12.4 { 871 execsql { SAVEPOINT one } 872} {} 873wal_check_journal_mode savepoint-12.5 874 875#------------------------------------------------------------------------- 876# The following tests - savepoint-13.* - test the interaction of 877# savepoints and "journal_mode = off". 878# 879if {[wal_is_wal_mode]==0} { 880 do_test savepoint-13.1 { 881 db close 882 catch {forcedelete test.db} 883 sqlite3 db test.db 884 execsql { 885 BEGIN; 886 CREATE TABLE t1(a PRIMARY KEY, b); 887 INSERT INTO t1 VALUES(1, 2); 888 COMMIT; 889 PRAGMA journal_mode = off; 890 } 891 } {off} 892 do_test savepoint-13.2 { 893 execsql { 894 BEGIN; 895 INSERT INTO t1 VALUES(3, 4); 896 INSERT INTO t1 SELECT a+4,b+4 FROM t1; 897 COMMIT; 898 } 899 } {} 900 do_test savepoint-13.3 { 901 execsql { 902 BEGIN; 903 INSERT INTO t1 VALUES(9, 10); 904 SAVEPOINT s1; 905 INSERT INTO t1 VALUES(11, 12); 906 COMMIT; 907 } 908 } {} 909 do_test savepoint-13.4 { 910 execsql { 911 BEGIN; 912 INSERT INTO t1 VALUES(13, 14); 913 SAVEPOINT s1; 914 INSERT INTO t1 VALUES(15, 16); 915 ROLLBACK TO s1; 916 ROLLBACK; 917 SELECT * FROM t1; 918 } 919 } {1 2 3 4 5 6 7 8 9 10 11 12} 920} 921 922db close 923delete_file test.db 924do_multiclient_test tn { 925 do_test savepoint-14.$tn.1 { 926 sql1 { 927 CREATE TABLE foo(x); 928 INSERT INTO foo VALUES(1); 929 INSERT INTO foo VALUES(2); 930 } 931 sql2 { 932 BEGIN; 933 SELECT * FROM foo; 934 } 935 } {1 2} 936 do_test savepoint-14.$tn.2 { 937 sql1 { 938 SAVEPOINT one; 939 INSERT INTO foo VALUES(1); 940 } 941 csql1 { RELEASE one } 942 } {1 {database is locked}} 943 do_test savepoint-14.$tn.3 { 944 sql1 { ROLLBACK TO one } 945 sql2 { COMMIT } 946 sql1 { RELEASE one } 947 } {} 948 949 do_test savepoint-14.$tn.4 { 950 sql2 { 951 BEGIN; 952 SELECT * FROM foo; 953 } 954 } {1 2} 955 do_test savepoint-14.$tn.5 { 956 sql1 { 957 SAVEPOINT one; 958 INSERT INTO foo VALUES(1); 959 } 960 csql1 { RELEASE one } 961 } {1 {database is locked}} 962 do_test savepoint-14.$tn.6 { 963 sql2 { COMMIT } 964 sql1 { 965 ROLLBACK TO one; 966 INSERT INTO foo VALUES(3); 967 INSERT INTO foo VALUES(4); 968 INSERT INTO foo VALUES(5); 969 RELEASE one; 970 } 971 } {} 972 do_test savepoint-14.$tn.7 { 973 sql2 { CREATE INDEX fooidx ON foo(x); } 974 sql3 { PRAGMA integrity_check } 975 } {ok} 976} 977 978do_multiclient_test tn { 979 do_test savepoint-15.$tn.1 { 980 sql1 { 981 CREATE TABLE foo(x); 982 INSERT INTO foo VALUES(1); 983 INSERT INTO foo VALUES(2); 984 } 985 sql2 { BEGIN; SELECT * FROM foo; } 986 } {1 2} 987 do_test savepoint-15.$tn.2 { 988 sql1 { 989 PRAGMA locking_mode = EXCLUSIVE; 990 BEGIN; 991 INSERT INTO foo VALUES(3); 992 } 993 csql1 { COMMIT } 994 } {1 {database is locked}} 995 do_test savepoint-15.$tn.3 { 996 sql1 { ROLLBACK } 997 sql2 { COMMIT } 998 sql1 { 999 INSERT INTO foo VALUES(3); 1000 PRAGMA locking_mode = NORMAL; 1001 INSERT INTO foo VALUES(4); 1002 } 1003 sql2 { CREATE INDEX fooidx ON foo(x); } 1004 sql3 { PRAGMA integrity_check } 1005 } {ok} 1006} 1007 1008do_multiclient_test tn { 1009 do_test savepoint-16.$tn.1 { 1010 sql1 { 1011 CREATE TABLE foo(x); 1012 INSERT INTO foo VALUES(1); 1013 INSERT INTO foo VALUES(2); 1014 } 1015 } {} 1016 do_test savepoint-16.$tn.2 { 1017 1018 db eval {SELECT * FROM foo} { 1019 sql1 { INSERT INTO foo VALUES(3) } 1020 sql2 { SELECT * FROM foo } 1021 sql1 { INSERT INTO foo VALUES(4) } 1022 break 1023 } 1024 1025 sql2 { CREATE INDEX fooidx ON foo(x); } 1026 sql3 { PRAGMA integrity_check } 1027 } {ok} 1028 do_test savepoint-16.$tn.3 { 1029 sql1 { SELECT * FROM foo } 1030 } {1 2 3 4} 1031} 1032 1033#------------------------------------------------------------------------- 1034# This next block of tests verifies that a problem reported on the mailing 1035# list has been resolved. At one point the second "CREATE TABLE t6" would 1036# fail as table t6 still existed in the internal cache of the db schema 1037# (even though it had been removed from the database by the ROLLBACK 1038# command). 1039# 1040sqlite3 db test.db 1041do_execsql_test savepoint-17.1 { 1042 BEGIN; 1043 CREATE TABLE t6(a, b); 1044 INSERT INTO t6 VALUES(1, 2); 1045 SAVEPOINT one; 1046 INSERT INTO t6 VALUES(3, 4); 1047 ROLLBACK TO one; 1048 SELECT * FROM t6; 1049 ROLLBACK; 1050} {1 2} 1051 1052do_execsql_test savepoint-17.2 { 1053 CREATE TABLE t6(a, b); 1054} {} 1055 1056finish_test 1057