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