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 { 307 execsql {SAVEPOINT def} 308 set fd [db incrblob -readonly blobs x 1] 309 catchsql {ROLLBACK TO def} 310 } {1 {cannot rollback savepoint - SQL statements in progress}} 311 do_test savepoint-5.3.3 { 312 catchsql {RELEASE def} 313 } {0 {}} 314 do_test savepoint-5.3.4 { 315 close $fd 316 execsql {savepoint def} 317 set fd [db incrblob blobs x 1] 318 catchsql {release def} 319 } {1 {cannot release savepoint - SQL statements in progress}} 320 do_test savepoint-5.3.5 { 321 close $fd 322 execsql {release abc} 323 } {} 324 325 # Rollback mode: 326 # 327 # Open a savepoint transaction and insert a row into the database. Then, 328 # using a second database handle, open a read-only transaction on the 329 # database file. Check that the savepoint transaction cannot be committed 330 # until after the read-only transaction has been closed. 331 # 332 # WAL mode: 333 # 334 # As above, except that the savepoint transaction can be successfully 335 # committed before the read-only transaction has been closed. 336 # 337 do_test savepoint-5.4.1 { 338 execsql { 339 SAVEPOINT main; 340 INSERT INTO blobs VALUES('another blob'); 341 } 342 } {} 343 do_test savepoint-5.4.2 { 344 sqlite3 db2 test.db 345 execsql { BEGIN ; SELECT count(*) FROM blobs } db2 346 } {1} 347 if {[wal_is_wal_mode]} { 348 do_test savepoint-5.4.3 { catchsql "RELEASE main" } {0 {}} 349 do_test savepoint-5.4.4 { db2 close } {} 350 } else { 351 do_test savepoint-5.4.3 { 352 catchsql { RELEASE main } 353 } {1 {database is locked}} 354 do_test savepoint-5.4.4 { 355 db2 close 356 catchsql { RELEASE main } 357 } {0 {}} 358 } 359 do_test savepoint-5.4.5 { 360 execsql { SELECT x FROM blobs WHERE rowid = 2 } 361 } {{another blob}} 362 do_test savepoint-5.4.6 { 363 execsql { SELECT count(*) FROM blobs } 364 } {2} 365} 366wal_check_journal_mode savepoint-5.5 367 368#------------------------------------------------------------------------- 369# The following tests, savepoint-6.*, test an incr-vacuum inside of a 370# couple of nested savepoints. 371# 372ifcapable {autovacuum && pragma} { 373 db close 374 forcedelete test.db 375 sqlite3 db test.db 376 377 do_test savepoint-6.1 { 378 execsql { PRAGMA auto_vacuum = incremental } 379 wal_set_journal_mode 380 execsql { 381 CREATE TABLE t1(a, b, c); 382 CREATE INDEX i1 ON t1(a, b); 383 BEGIN; 384 INSERT INTO t1 VALUES(randstr(10,400),randstr(10,400),randstr(10,400)); 385 } 386 set r "randstr(10,400)" 387 for {set ii 0} {$ii < 10} {incr ii} { 388 execsql "INSERT INTO t1 SELECT $r, $r, $r FROM t1" 389 } 390 execsql { COMMIT } 391 } {} 392 393 integrity_check savepoint-6.2 394 395 do_test savepoint-6.3 { 396 execsql { 397 PRAGMA cache_size = 10; 398 BEGIN; 399 UPDATE t1 SET a = randstr(10,10) WHERE (rowid%4)==0; 400 SAVEPOINT one; 401 DELETE FROM t1 WHERE rowid%2; 402 PRAGMA incr_vacuum; 403 SAVEPOINT two; 404 INSERT INTO t1 SELECT randstr(10,400), randstr(10,400), c FROM t1; 405 DELETE FROM t1 WHERE rowid%2; 406 PRAGMA incr_vacuum; 407 ROLLBACK TO one; 408 COMMIT; 409 } 410 } {} 411 412 integrity_check savepoint-6.4 413 414 wal_check_journal_mode savepoint-6.5 415} 416 417#------------------------------------------------------------------------- 418# The following tests, savepoint-7.*, attempt to break the logic 419# surrounding savepoints by growing and shrinking the database file. 420# 421db close 422forcedelete test.db 423sqlite3 db test.db 424 425do_test savepoint-7.1 { 426 execsql { PRAGMA auto_vacuum = incremental } 427 wal_set_journal_mode 428 execsql { 429 PRAGMA cache_size = 10; 430 BEGIN; 431 CREATE TABLE t1(a PRIMARY KEY, b); 432 INSERT INTO t1(a) VALUES('alligator'); 433 INSERT INTO t1(a) VALUES('angelfish'); 434 INSERT INTO t1(a) VALUES('ant'); 435 INSERT INTO t1(a) VALUES('antelope'); 436 INSERT INTO t1(a) VALUES('ape'); 437 INSERT INTO t1(a) VALUES('baboon'); 438 INSERT INTO t1(a) VALUES('badger'); 439 INSERT INTO t1(a) VALUES('bear'); 440 INSERT INTO t1(a) VALUES('beetle'); 441 INSERT INTO t1(a) VALUES('bird'); 442 INSERT INTO t1(a) VALUES('bison'); 443 UPDATE t1 SET b = randstr(1000,1000); 444 UPDATE t1 SET b = b||randstr(1000,1000); 445 UPDATE t1 SET b = b||randstr(1000,1000); 446 UPDATE t1 SET b = b||randstr(10,1000); 447 COMMIT; 448 } 449 expr ([execsql { PRAGMA page_count }] > 20) 450} {1} 451do_test savepoint-7.2.1 { 452 execsql { 453 BEGIN; 454 SAVEPOINT one; 455 CREATE TABLE t2(a, b); 456 INSERT INTO t2 SELECT a, b FROM t1; 457 ROLLBACK TO one; 458 } 459 execsql { 460 PRAGMA integrity_check; 461 } 462} {ok} 463do_test savepoint-7.2.2 { 464 execsql { 465 COMMIT; 466 PRAGMA integrity_check; 467 } 468} {ok} 469 470do_test savepoint-7.3.1 { 471 execsql { 472 CREATE TABLE t2(a, b); 473 INSERT INTO t2 SELECT a, b FROM t1; 474 } 475} {} 476do_test savepoint-7.3.2 { 477 execsql { 478 BEGIN; 479 SAVEPOINT one; 480 DELETE FROM t2; 481 PRAGMA incremental_vacuum; 482 SAVEPOINT two; 483 INSERT INTO t2 SELECT a, b FROM t1; 484 ROLLBACK TO two; 485 COMMIT; 486 } 487 execsql { PRAGMA integrity_check } 488} {ok} 489wal_check_journal_mode savepoint-7.3.3 490 491do_test savepoint-7.4.1 { 492 db close 493 forcedelete test.db 494 sqlite3 db test.db 495 execsql { PRAGMA auto_vacuum = incremental } 496 wal_set_journal_mode 497 execsql { 498 CREATE TABLE t1(a, b, PRIMARY KEY(a, b)); 499 INSERT INTO t1 VALUES(randstr(1000,1000), randstr(1000,1000)); 500 BEGIN; 501 DELETE FROM t1; 502 SAVEPOINT one; 503 PRAGMA incremental_vacuum; 504 ROLLBACK TO one; 505 COMMIT; 506 } 507 508 execsql { PRAGMA integrity_check } 509} {ok} 510 511do_test savepoint-7.5.1 { 512 execsql { 513 PRAGMA incremental_vacuum; 514 CREATE TABLE t5(x, y); 515 INSERT INTO t5 VALUES(1, randstr(1000,1000)); 516 INSERT INTO t5 VALUES(2, randstr(1000,1000)); 517 INSERT INTO t5 VALUES(3, randstr(1000,1000)); 518 519 BEGIN; 520 INSERT INTO t5 VALUES(4, randstr(1000,1000)); 521 INSERT INTO t5 VALUES(5, randstr(1000,1000)); 522 DELETE FROM t5 WHERE x=1 OR x=2; 523 SAVEPOINT one; 524 PRAGMA incremental_vacuum; 525 SAVEPOINT two; 526 INSERT INTO t5 VALUES(1, randstr(1000,1000)); 527 INSERT INTO t5 VALUES(2, randstr(1000,1000)); 528 ROLLBACK TO two; 529 ROLLBACK TO one; 530 COMMIT; 531 PRAGMA integrity_check; 532 } 533} {ok} 534do_test savepoint-7.5.2 { 535 execsql { 536 DROP TABLE t5; 537 } 538} {} 539wal_check_journal_mode savepoint-7.5.3 540 541# Test oddly named and quoted savepoints. 542# 543do_test savepoint-8-1 { 544 execsql { SAVEPOINT "save1" } 545 execsql { RELEASE save1 } 546} {} 547do_test savepoint-8-2 { 548 execsql { SAVEPOINT "Including whitespace " } 549 execsql { RELEASE "including Whitespace " } 550} {} 551 552# Test that the authorization callback works. 553# 554ifcapable auth { 555 proc auth {args} { 556 eval lappend ::authdata $args 557 return SQLITE_OK 558 } 559 db auth auth 560 561 do_test savepoint-9.1 { 562 set ::authdata [list] 563 execsql { SAVEPOINT sp1 } 564 set ::authdata 565 } {SQLITE_SAVEPOINT BEGIN sp1 {} {}} 566 do_test savepoint-9.2 { 567 set ::authdata [list] 568 execsql { ROLLBACK TO sp1 } 569 set ::authdata 570 } {SQLITE_SAVEPOINT ROLLBACK sp1 {} {}} 571 do_test savepoint-9.3 { 572 set ::authdata [list] 573 execsql { RELEASE sp1 } 574 set ::authdata 575 } {SQLITE_SAVEPOINT RELEASE sp1 {} {}} 576 577 proc auth {args} { 578 eval lappend ::authdata $args 579 return SQLITE_DENY 580 } 581 db auth auth 582 583 do_test savepoint-9.4 { 584 set ::authdata [list] 585 set res [catchsql { SAVEPOINT sp1 }] 586 concat $::authdata $res 587 } {SQLITE_SAVEPOINT BEGIN sp1 {} {} 1 {not authorized}} 588 do_test savepoint-9.5 { 589 set ::authdata [list] 590 set res [catchsql { ROLLBACK TO sp1 }] 591 concat $::authdata $res 592 } {SQLITE_SAVEPOINT ROLLBACK sp1 {} {} 1 {not authorized}} 593 do_test savepoint-9.6 { 594 set ::authdata [list] 595 set res [catchsql { RELEASE sp1 }] 596 concat $::authdata $res 597 } {SQLITE_SAVEPOINT RELEASE sp1 {} {} 1 {not authorized}} 598 599 catch { db eval ROLLBACK } 600 db auth "" 601} 602 603#------------------------------------------------------------------------- 604# The following tests - savepoint-10.* - test the interaction of 605# savepoints and ATTACH statements. 606# 607 608# First make sure it is not possible to attach or detach a database while 609# a savepoint is open (it is not possible if any transaction is open). 610# 611do_test savepoint-10.1.1 { 612 catchsql { 613 SAVEPOINT one; 614 ATTACH 'test2.db' AS aux; 615 } 616} {1 {cannot ATTACH database within transaction}} 617do_test savepoint-10.1.2 { 618 execsql { 619 RELEASE one; 620 ATTACH 'test2.db' AS aux; 621 } 622 catchsql { 623 SAVEPOINT one; 624 DETACH aux; 625 } 626} {1 {cannot DETACH database within transaction}} 627do_test savepoint-10.1.3 { 628 execsql { 629 RELEASE one; 630 DETACH aux; 631 } 632} {} 633 634# The lock state of the TEMP database can vary if SQLITE_TEMP_STORE=3 635# And the following set of tests is only really interested in the status 636# of the aux1 and aux2 locks. So record the current lock status of 637# TEMP for use in the answers. 638set templockstate [lindex [db eval {PRAGMA lock_status}] 3] 639 640 641if {[wal_is_wal_mode]==0} { 642 do_test savepoint-10.2.1 { 643 forcedelete test3.db 644 forcedelete test2.db 645 execsql { 646 ATTACH 'test2.db' AS aux1; 647 ATTACH 'test3.db' AS aux2; 648 DROP TABLE t1; 649 CREATE TABLE main.t1(x, y); 650 CREATE TABLE aux1.t2(x, y); 651 CREATE TABLE aux2.t3(x, y); 652 SELECT name FROM sqlite_master; 653 SELECT name FROM aux1.sqlite_master; 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 ; 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 forcedelete 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 wal_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 {forcedelete 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} 907} 908 909db close 910delete_file test.db 911do_multiclient_test tn { 912 do_test savepoint-14.$tn.1 { 913 sql1 { 914 CREATE TABLE foo(x); 915 INSERT INTO foo VALUES(1); 916 INSERT INTO foo VALUES(2); 917 } 918 sql2 { 919 BEGIN; 920 SELECT * FROM foo; 921 } 922 } {1 2} 923 do_test savepoint-14.$tn.2 { 924 sql1 { 925 SAVEPOINT one; 926 INSERT INTO foo VALUES(1); 927 } 928 csql1 { RELEASE one } 929 } {1 {database is locked}} 930 do_test savepoint-14.$tn.3 { 931 sql1 { ROLLBACK TO one } 932 sql2 { COMMIT } 933 sql1 { RELEASE one } 934 } {} 935 936 do_test savepoint-14.$tn.4 { 937 sql2 { 938 BEGIN; 939 SELECT * FROM foo; 940 } 941 } {1 2} 942 do_test savepoint-14.$tn.5 { 943 sql1 { 944 SAVEPOINT one; 945 INSERT INTO foo VALUES(1); 946 } 947 csql1 { RELEASE one } 948 } {1 {database is locked}} 949 do_test savepoint-14.$tn.6 { 950 sql2 { COMMIT } 951 sql1 { 952 ROLLBACK TO one; 953 INSERT INTO foo VALUES(3); 954 INSERT INTO foo VALUES(4); 955 INSERT INTO foo VALUES(5); 956 RELEASE one; 957 } 958 } {} 959 do_test savepoint-14.$tn.7 { 960 sql2 { CREATE INDEX fooidx ON foo(x); } 961 sql3 { PRAGMA integrity_check } 962 } {ok} 963} 964 965do_multiclient_test tn { 966 do_test savepoint-15.$tn.1 { 967 sql1 { 968 CREATE TABLE foo(x); 969 INSERT INTO foo VALUES(1); 970 INSERT INTO foo VALUES(2); 971 } 972 sql2 { BEGIN; SELECT * FROM foo; } 973 } {1 2} 974 do_test savepoint-15.$tn.2 { 975 sql1 { 976 PRAGMA locking_mode = EXCLUSIVE; 977 BEGIN; 978 INSERT INTO foo VALUES(3); 979 } 980 csql1 { COMMIT } 981 } {1 {database is locked}} 982 do_test savepoint-15.$tn.3 { 983 sql1 { ROLLBACK } 984 sql2 { COMMIT } 985 sql1 { 986 INSERT INTO foo VALUES(3); 987 PRAGMA locking_mode = NORMAL; 988 INSERT INTO foo VALUES(4); 989 } 990 sql2 { CREATE INDEX fooidx ON foo(x); } 991 sql3 { PRAGMA integrity_check } 992 } {ok} 993} 994 995do_multiclient_test tn { 996 do_test savepoint-16.$tn.1 { 997 sql1 { 998 CREATE TABLE foo(x); 999 INSERT INTO foo VALUES(1); 1000 INSERT INTO foo VALUES(2); 1001 } 1002 } {} 1003 do_test savepoint-16.$tn.2 { 1004 1005 db eval {SELECT * FROM foo} { 1006 sql1 { INSERT INTO foo VALUES(3) } 1007 sql2 { SELECT * FROM foo } 1008 sql1 { INSERT INTO foo VALUES(4) } 1009 break 1010 } 1011 1012 sql2 { CREATE INDEX fooidx ON foo(x); } 1013 sql3 { PRAGMA integrity_check } 1014 } {ok} 1015 do_test savepoint-16.$tn.3 { 1016 sql1 { SELECT * FROM foo } 1017 } {1 2 3 4} 1018} 1019 1020#------------------------------------------------------------------------- 1021# This next block of tests verifies that a problem reported on the mailing 1022# list has been resolved. At one point the second "CREATE TABLE t6" would 1023# fail as table t6 still existed in the internal cache of the db schema 1024# (even though it had been removed from the database by the ROLLBACK 1025# command). 1026# 1027sqlite3 db test.db 1028do_execsql_test savepoint-17.1 { 1029 BEGIN; 1030 CREATE TABLE t6(a, b); 1031 INSERT INTO t6 VALUES(1, 2); 1032 SAVEPOINT one; 1033 INSERT INTO t6 VALUES(3, 4); 1034 ROLLBACK TO one; 1035 SELECT * FROM t6; 1036 ROLLBACK; 1037} {1 2} 1038 1039do_execsql_test savepoint-17.2 { 1040 CREATE TABLE t6(a, b); 1041} {} 1042 1043finish_test 1044