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 19forcedelete test2.db 20 21#---------------------------------------------------------------------- 22# The following tests - savepoint-1.* - test that the SAVEPOINT, RELEASE 23# and ROLLBACK TO comands are correctly parsed, and that the auto-commit 24# flag is correctly set and unset as a result. 25# 26do_test savepoint-1.1 { 27 wal_set_journal_mode 28 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 close 42} {} 43sqlite3 db test.db 44do_test savepoint-1.4.1 { 45 execsql { 46 SAVEPOINT sp1; 47 SAVEPOINT sp2; 48 RELEASE sp1; 49 } 50 sqlite3_get_autocommit db 51} {1} 52do_test savepoint-1.4.2 { 53 execsql { 54 SAVEPOINT sp1; 55 SAVEPOINT sp2; 56 RELEASE sp2; 57 } 58 sqlite3_get_autocommit db 59} {0} 60do_test savepoint-1.4.3 { 61 execsql { RELEASE sp1 } 62 sqlite3_get_autocommit db 63} {1} 64do_test savepoint-1.4.4 { 65 execsql { 66 SAVEPOINT sp1; 67 SAVEPOINT sp2; 68 ROLLBACK TO sp1; 69 } 70 sqlite3_get_autocommit db 71} {0} 72do_test savepoint-1.4.5 { 73 execsql { RELEASE SAVEPOINT sp1 } 74 sqlite3_get_autocommit db 75} {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 db 86} {0} 87do_test savepoint-1.4.7 { 88 execsql { RELEASE SAVEPOINT SP1 } 89 sqlite3_get_autocommit db 90} {1} 91do_test savepoint-1.5 { 92 execsql { 93 SAVEPOINT sp1; 94 ROLLBACK TO sp1; 95 } 96} {} 97do_test savepoint-1.6 { 98 execsql COMMIT 99} {} 100wal_check_journal_mode savepoint-1.7 101 102#------------------------------------------------------------------------ 103# These tests - savepoint-2.* - test rollbacks and releases of savepoints 104# 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.12 184 185#------------------------------------------------------------------------ 186# This block of tests - savepoint-3.* - test that when a transaction 187# 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 RESERVED 191# 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 handled 222# 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 COMMIT 277} {} 278wal_check_journal_mode savepoint-4.9 279 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 $fd 296 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 $res 313 } {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 rc 320 } {0} 321 do_test savepoint-5.3.3 { 322 catchsql {RELEASE def} 323 } {0 {}} 324 do_test savepoint-5.3.4 { 325 close $fd 326 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 $fd 332 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 the 339 # database file. Check that the savepoint transaction cannot be committed 340 # until after the read-only transaction has been closed. 341 # 342 # WAL mode: 343 # 344 # As above, except that the savepoint transaction can be successfully 345 # 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.db 355 execsql { BEGIN ; SELECT count(*) FROM blobs } db2 356 } {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 close 366 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.5 377 378#------------------------------------------------------------------------- 379# The following tests, savepoint-6.*, test an incr-vacuum inside of a 380# couple of nested savepoints. 381# 382ifcapable {autovacuum && pragma} { 383 db close 384 forcedelete test.db 385 sqlite3 db test.db 386 387 do_test savepoint-6.1 { 388 execsql { PRAGMA auto_vacuum = incremental } 389 wal_set_journal_mode 390 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.2 404 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.4 423 424 wal_check_journal_mode savepoint-6.5 425} 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 close 432forcedelete test.db 433sqlite3 db test.db 434 435do_test savepoint-7.1 { 436 execsql { PRAGMA auto_vacuum = incremental } 437 wal_set_journal_mode 438 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.3 500 501do_test savepoint-7.4.1 { 502 db close 503 forcedelete test.db 504 sqlite3 db test.db 505 execsql { PRAGMA auto_vacuum = incremental } 506 wal_set_journal_mode 507 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.3 550 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_OK 568 } 569 db auth auth 570 571 do_test savepoint-9.1 { 572 set ::authdata [list] 573 execsql { SAVEPOINT sp1 } 574 set ::authdata 575 } {SQLITE_SAVEPOINT BEGIN sp1 {} {}} 576 do_test savepoint-9.2 { 577 set ::authdata [list] 578 execsql { ROLLBACK TO sp1 } 579 set ::authdata 580 } {SQLITE_SAVEPOINT ROLLBACK sp1 {} {}} 581 do_test savepoint-9.3 { 582 set ::authdata [list] 583 execsql { RELEASE sp1 } 584 set ::authdata 585 } {SQLITE_SAVEPOINT RELEASE sp1 {} {}} 586 587 proc auth {args} { 588 eval lappend ::authdata [lrange $args 0 4] 589 return SQLITE_DENY 590 } 591 db auth auth 592 593 do_test savepoint-9.4 { 594 set ::authdata [list] 595 set res [catchsql { SAVEPOINT sp1 }] 596 concat $::authdata $res 597 } {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 $res 602 } {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 $res 607 } {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 while 619# 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 a 622# 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=3 650# And the following set of tests is only really interested in the status 651# of the aux1 and aux2 locks. So record the current lock status of 652# 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.db 659 forcedelete test2.db 660 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 close 778 forcedelete test.db 779 sqlite3 db test.db 780 execsql { PRAGMA auto_vacuum = full; } 781 wal_set_journal_mode 782 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.3 797do_test savepoint-11.4 { 798 execsql { ROLLBACK TO two } 799} {} 800integrity_check savepoint-11.5 801do_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.7 808do_test savepoint-11.8 { 809 execsql { ROLLBACK } 810 execsql { PRAGMA wal_checkpoint } 811 file size test.db 812} {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 COMMIT 837} {} 838do_test savepoint-11.12 { 839 execsql {SELECT * FROM t2} 840} {1 2 3 4} 841wal_check_journal_mode savepoint-11.13 842 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 the 855 # conflict handling mode is "OR ROLLBACK" and there are a couple of 856 # open savepoints. At one point this would fail to clear the internal 857 # 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 db 871} {1} 872do_test savepoint-12.4 { 873 execsql { SAVEPOINT one } 874} {} 875wal_check_journal_mode savepoint-12.5 876 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 close 884 catch {forcedelete test.db} 885 sqlite3 db test.db 886 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 close 925delete_file test.db 926do_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 break 1025 } 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 mailing 1037# list has been resolved. At one point the second "CREATE TABLE t6" would 1038# fail as table t6 still existed in the internal cache of the db schema 1039# (even though it had been removed from the database by the ROLLBACK 1040# command). 1041# 1042sqlite3 db test.db 1043do_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_test 1059