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 14 set testdir [file dirname $argv0] 15 source $testdir/tester.tcl 16 source $testdir/lock_common.tcl 17 source $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 # 24 do_test savepoint-1.1 { 25 wal_set_journal_mode 26 execsql { 27 SAVEPOINT sp1; 28 RELEASE sp1; 29 } 30 } {} 31 do_test savepoint-1.2 { 32 execsql { 33 SAVEPOINT sp1; 34 ROLLBACK TO sp1; 35 } 36 } {} 37 do_test savepoint-1.3 { 38 execsql { SAVEPOINT sp1 } 39 db close 40 } {} 41 sqlite3 db test.db 42 do_test savepoint-1.4.1 { 43 execsql { 44 SAVEPOINT sp1; 45 SAVEPOINT sp2; 46 RELEASE sp1; 47 } 48 sqlite3_get_autocommit db 49 } {1} 50 do_test savepoint-1.4.2 { 51 execsql { 52 SAVEPOINT sp1; 53 SAVEPOINT sp2; 54 RELEASE sp2; 55 } 56 sqlite3_get_autocommit db 57 } {0} 58 do_test savepoint-1.4.3 { 59 execsql { RELEASE sp1 } 60 sqlite3_get_autocommit db 61 } {1} 62 do_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} 70 do_test savepoint-1.4.5 { 71 execsql { RELEASE SAVEPOINT sp1 } 72 sqlite3_get_autocommit db 73 } {1} 74 do_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} 85 do_test savepoint-1.4.7 { 86 execsql { RELEASE SAVEPOINT SP1 } 87 sqlite3_get_autocommit db 88 } {1} 89 do_test savepoint-1.5 { 90 execsql { 91 SAVEPOINT sp1; 92 ROLLBACK TO sp1; 93 } 94 } {} 95 do_test savepoint-1.6 { 96 execsql COMMIT 97 } {} 98 wal_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 105 do_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} 115 do_test savepoint-2.2 { 116 execsql { 117 ROLLBACK TO one; 118 } 119 execsql { SELECT * FROM t1 } 120 } {1 2 3} 121 do_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} 127 do_test savepoint-2.4 { 128 execsql { 129 ROLLBACK TO one; 130 } 131 execsql { SELECT * FROM t1 } 132 } {1 2 3} 133 134 135 do_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} 143 do_test savepoint-2.6 { 144 execsql { 145 ROLLBACK TO two; 146 } 147 execsql { SELECT * FROM t1 } 148 } {1 2 3 7 8 9} 149 do_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} 155 do_test savepoint-2.8 { 156 execsql { 157 ROLLBACK TO one; 158 } 159 execsql { SELECT * FROM t1 } 160 } {1 2 3} 161 do_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} 169 do_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} 175 do_test savepoint-2.11 { 176 execsql { 177 ROLLBACK; 178 } 179 execsql { SELECT * FROM t1 } 180 } {} 181 wal_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 # 191 if {[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 # 222 do_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)}} 228 do_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 } {} 237 do_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} 244 do_test savepoint-4.4 { 245 execsql { ROLLBACK TO one; } 246 execsql {SELECT * FROM t3} 247 } {I II} 248 do_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 255 do_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} 265 do_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} 273 do_test savepoint-4.8 { 274 execsql COMMIT 275 } {} 276 wal_check_journal_mode savepoint-4.9 277 278 #------------------------------------------------------------------------ 279 # Test some logic errors to do with the savepoint feature. 280 # 281 282 ifcapable 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 } 366 wal_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 # 372 ifcapable {autovacuum && pragma} { 373 db close 374 file delete -force 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 # 421 db close 422 file delete -force test.db 423 sqlite3 db test.db 424 425 do_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} 451 do_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} 463 do_test savepoint-7.2.2 { 464 execsql { 465 COMMIT; 466 PRAGMA integrity_check; 467 } 468 } {ok} 469 470 do_test savepoint-7.3.1 { 471 execsql { 472 CREATE TABLE t2(a, b); 473 INSERT INTO t2 SELECT a, b FROM t1; 474 } 475 } {} 476 do_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} 489 wal_check_journal_mode savepoint-7.3.3 490 491 do_test savepoint-7.4.1 { 492 db close 493 file delete -force 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 511 do_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} 534 do_test savepoint-7.5.2 { 535 execsql { 536 DROP TABLE t5; 537 } 538 } {} 539 wal_check_journal_mode savepoint-7.5.3 540 541 # Test oddly named and quoted savepoints. 542 # 543 do_test savepoint-8-1 { 544 execsql { SAVEPOINT "save1" } 545 execsql { RELEASE save1 } 546 } {} 547 do_test savepoint-8-2 { 548 execsql { SAVEPOINT "Including whitespace " } 549 execsql { RELEASE "including Whitespace " } 550 } {} 551 552 # Test that the authorization callback works. 553 # 554 ifcapable 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 # 611 do_test savepoint-10.1.1 { 612 catchsql { 613 SAVEPOINT one; 614 ATTACH 'test2.db' AS aux; 615 } 616 } {1 {cannot ATTACH database within transaction}} 617 do_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}} 627 do_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. 638 set templockstate [lindex [db eval {PRAGMA lock_status}] 3] 639 640 641 if {[wal_is_wal_mode]==0} { 642 do_test savepoint-10.2.1 { 643 file delete -force test3.db 644 file delete -force 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 UNION ALL 654 SELECT name FROM aux1.sqlite_master 655 UNION ALL 656 SELECT name FROM aux2.sqlite_master; 657 } 658 } {t1 t2 t3} 659 do_test savepoint-10.2.2 { 660 execsql { PRAGMA lock_status } 661 } [list main unlocked temp $templockstate aux1 unlocked aux2 unlocked] 662 663 do_test savepoint-10.2.3 { 664 execsql { 665 SAVEPOINT one; 666 INSERT INTO t1 VALUES(1, 2); 667 PRAGMA lock_status; 668 } 669 } [list main reserved temp $templockstate aux1 unlocked aux2 unlocked] 670 do_test savepoint-10.2.4 { 671 execsql { 672 INSERT INTO t3 VALUES(3, 4); 673 PRAGMA lock_status; 674 } 675 } [list main reserved temp $templockstate aux1 unlocked aux2 reserved] 676 do_test savepoint-10.2.5 { 677 execsql { 678 SAVEPOINT two; 679 INSERT INTO t2 VALUES(5, 6); 680 PRAGMA lock_status; 681 } 682 } [list main reserved temp $templockstate aux1 reserved aux2 reserved] 683 do_test savepoint-10.2.6 { 684 execsql { SELECT * FROM t2 } 685 } {5 6} 686 do_test savepoint-10.2.7 { 687 execsql { ROLLBACK TO two } 688 execsql { SELECT * FROM t2 } 689 } {} 690 do_test savepoint-10.2.8 { 691 execsql { PRAGMA lock_status } 692 } [list main reserved temp $templockstate aux1 reserved aux2 reserved] 693 do_test savepoint-10.2.9 { 694 execsql { SELECT 'a', * FROM t1 UNION ALL SELECT 'b', * FROM t3 } 695 } {a 1 2 b 3 4} 696 do_test savepoint-10.2.9 { 697 execsql { 698 INSERT INTO t2 VALUES(5, 6); 699 RELEASE one; 700 } 701 execsql { 702 SELECT * FROM t1; 703 SELECT * FROM t2; 704 SELECT * FROM t3; 705 } 706 } {1 2 5 6 3 4} 707 do_test savepoint-10.2.9 { 708 execsql { PRAGMA lock_status } 709 } [list main unlocked temp $templockstate aux1 unlocked aux2 unlocked] 710 711 do_test savepoint-10.2.10 { 712 execsql { 713 SAVEPOINT one; 714 INSERT INTO t1 VALUES('a', 'b'); 715 SAVEPOINT two; 716 INSERT INTO t2 VALUES('c', 'd'); 717 SAVEPOINT three; 718 INSERT INTO t3 VALUES('e', 'f'); 719 } 720 execsql { 721 SELECT * FROM t1; 722 SELECT * FROM t2; 723 SELECT * FROM t3; 724 } 725 } {1 2 a b 5 6 c d 3 4 e f} 726 do_test savepoint-10.2.11 { 727 execsql { ROLLBACK TO two } 728 execsql { 729 SELECT * FROM t1; 730 SELECT * FROM t2; 731 SELECT * FROM t3; 732 } 733 } {1 2 a b 5 6 3 4} 734 do_test savepoint-10.2.12 { 735 execsql { 736 INSERT INTO t3 VALUES('g', 'h'); 737 ROLLBACK TO two; 738 } 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.13 { 746 execsql { ROLLBACK } 747 execsql { 748 SELECT * FROM t1; 749 SELECT * FROM t2; 750 SELECT * FROM t3; 751 } 752 } {1 2 5 6 3 4} 753 do_test savepoint-10.2.14 { 754 execsql { PRAGMA lock_status } 755 } [list main unlocked temp $templockstate aux1 unlocked aux2 unlocked] 756 } 757 758 #------------------------------------------------------------------------- 759 # The following tests - savepoint-11.* - test the interaction of 760 # savepoints and creating or dropping tables and indexes in 761 # auto-vacuum mode. 762 # 763 do_test savepoint-11.1 { 764 db close 765 file delete -force test.db 766 sqlite3 db test.db 767 execsql { PRAGMA auto_vacuum = full; } 768 wal_set_journal_mode 769 execsql { 770 CREATE TABLE t1(a, b, UNIQUE(a, b)); 771 INSERT INTO t1 VALUES(1, randstr(1000,1000)); 772 INSERT INTO t1 VALUES(2, randstr(1000,1000)); 773 } 774 } {} 775 do_test savepoint-11.2 { 776 execsql { 777 SAVEPOINT one; 778 CREATE TABLE t2(a, b, UNIQUE(a, b)); 779 SAVEPOINT two; 780 CREATE TABLE t3(a, b, UNIQUE(a, b)); 781 } 782 } {} 783 integrity_check savepoint-11.3 784 do_test savepoint-11.4 { 785 execsql { ROLLBACK TO two } 786 } {} 787 integrity_check savepoint-11.5 788 do_test savepoint-11.6 { 789 execsql { 790 CREATE TABLE t3(a, b, UNIQUE(a, b)); 791 ROLLBACK TO one; 792 } 793 } {} 794 integrity_check savepoint-11.7 795 do_test savepoint-11.8 { 796 execsql { ROLLBACK } 797 execsql { PRAGMA wal_checkpoint } 798 file size test.db 799 } {8192} 800 801 do_test savepoint-11.9 { 802 execsql { 803 DROP TABLE IF EXISTS t1; 804 DROP TABLE IF EXISTS t2; 805 DROP TABLE IF EXISTS t3; 806 } 807 } {} 808 do_test savepoint-11.10 { 809 execsql { 810 BEGIN; 811 CREATE TABLE t1(a, b); 812 CREATE TABLE t2(x, y); 813 INSERT INTO t2 VALUES(1, 2); 814 SAVEPOINT one; 815 INSERT INTO t2 VALUES(3, 4); 816 SAVEPOINT two; 817 DROP TABLE t1; 818 ROLLBACK TO two; 819 } 820 execsql {SELECT * FROM t2} 821 } {1 2 3 4} 822 do_test savepoint-11.11 { 823 execsql COMMIT 824 } {} 825 do_test savepoint-11.12 { 826 execsql {SELECT * FROM t2} 827 } {1 2 3 4} 828 wal_check_journal_mode savepoint-11.13 829 830 #------------------------------------------------------------------------- 831 # The following tests - savepoint-12.* - test the interaction of 832 # savepoints and "ON CONFLICT ROLLBACK" clauses. 833 # 834 do_test savepoint-12.1 { 835 execsql { 836 CREATE TABLE t4(a PRIMARY KEY, b); 837 INSERT INTO t4 VALUES(1, 'one'); 838 } 839 } {} 840 do_test savepoint-12.2 { 841 # The final statement of the following SQL hits a constraint when the 842 # conflict handling mode is "OR ROLLBACK" and there are a couple of 843 # open savepoints. At one point this would fail to clear the internal 844 # record of the open savepoints, resulting in an assert() failure 845 # later on. 846 # 847 catchsql { 848 BEGIN; 849 INSERT INTO t4 VALUES(2, 'two'); 850 SAVEPOINT sp1; 851 INSERT INTO t4 VALUES(3, 'three'); 852 SAVEPOINT sp2; 853 INSERT OR ROLLBACK INTO t4 VALUES(1, 'one'); 854 } 855 } {1 {column a is not unique}} 856 do_test savepoint-12.3 { 857 sqlite3_get_autocommit db 858 } {1} 859 do_test savepoint-12.4 { 860 execsql { SAVEPOINT one } 861 } {} 862 wal_check_journal_mode savepoint-12.5 863 864 #------------------------------------------------------------------------- 865 # The following tests - savepoint-13.* - test the interaction of 866 # savepoints and "journal_mode = off". 867 # 868 if {[wal_is_wal_mode]==0} { 869 do_test savepoint-13.1 { 870 db close 871 catch {file delete -force test.db} 872 sqlite3 db test.db 873 execsql { 874 BEGIN; 875 CREATE TABLE t1(a PRIMARY KEY, b); 876 INSERT INTO t1 VALUES(1, 2); 877 COMMIT; 878 PRAGMA journal_mode = off; 879 } 880 } {off} 881 do_test savepoint-13.2 { 882 execsql { 883 BEGIN; 884 INSERT INTO t1 VALUES(3, 4); 885 INSERT INTO t1 SELECT a+4,b+4 FROM t1; 886 COMMIT; 887 } 888 } {} 889 do_test savepoint-13.3 { 890 execsql { 891 BEGIN; 892 INSERT INTO t1 VALUES(9, 10); 893 SAVEPOINT s1; 894 INSERT INTO t1 VALUES(11, 12); 895 COMMIT; 896 } 897 } {} 898 do_test savepoint-13.4 { 899 execsql { 900 BEGIN; 901 INSERT INTO t1 VALUES(13, 14); 902 SAVEPOINT s1; 903 INSERT INTO t1 VALUES(15, 16); 904 ROLLBACK TO s1; 905 ROLLBACK; 906 SELECT * FROM t1; 907 } 908 } {1 2 3 4 5 6 7 8 9 10 11 12} 909 } 910 911 db close 912 file delete test.db 913 do_multiclient_test tn { 914 do_test savepoint-14.$tn.1 { 915 sql1 { 916 CREATE TABLE foo(x); 917 INSERT INTO foo VALUES(1); 918 INSERT INTO foo VALUES(2); 919 } 920 sql2 { 921 BEGIN; 922 SELECT * FROM foo; 923 } 924 } {1 2} 925 do_test savepoint-14.$tn.2 { 926 sql1 { 927 SAVEPOINT one; 928 INSERT INTO foo VALUES(1); 929 } 930 csql1 { RELEASE one } 931 } {1 {database is locked}} 932 do_test savepoint-14.$tn.3 { 933 sql1 { ROLLBACK TO one } 934 sql2 { COMMIT } 935 sql1 { RELEASE one } 936 } {} 937 938 do_test savepoint-14.$tn.4 { 939 sql2 { 940 BEGIN; 941 SELECT * FROM foo; 942 } 943 } {1 2} 944 do_test savepoint-14.$tn.5 { 945 sql1 { 946 SAVEPOINT one; 947 INSERT INTO foo VALUES(1); 948 } 949 csql1 { RELEASE one } 950 } {1 {database is locked}} 951 do_test savepoint-14.$tn.6 { 952 sql2 { COMMIT } 953 sql1 { 954 ROLLBACK TO one; 955 INSERT INTO foo VALUES(3); 956 INSERT INTO foo VALUES(4); 957 INSERT INTO foo VALUES(5); 958 RELEASE one; 959 } 960 } {} 961 do_test savepoint-14.$tn.7 { 962 sql2 { CREATE INDEX fooidx ON foo(x); } 963 sql3 { PRAGMA integrity_check } 964 } {ok} 965 } 966 967 do_multiclient_test tn { 968 do_test savepoint-15.$tn.1 { 969 sql1 { 970 CREATE TABLE foo(x); 971 INSERT INTO foo VALUES(1); 972 INSERT INTO foo VALUES(2); 973 } 974 sql2 { BEGIN; SELECT * FROM foo; } 975 } {1 2} 976 do_test savepoint-15.$tn.2 { 977 sql1 { 978 PRAGMA locking_mode = EXCLUSIVE; 979 BEGIN; 980 INSERT INTO foo VALUES(3); 981 } 982 csql1 { COMMIT } 983 } {1 {database is locked}} 984 do_test savepoint-15.$tn.3 { 985 sql1 { ROLLBACK } 986 sql2 { COMMIT } 987 sql1 { 988 INSERT INTO foo VALUES(3); 989 PRAGMA locking_mode = NORMAL; 990 INSERT INTO foo VALUES(4); 991 } 992 sql2 { CREATE INDEX fooidx ON foo(x); } 993 sql3 { PRAGMA integrity_check } 994 } {ok} 995 } 996 997 do_multiclient_test tn { 998 do_test savepoint-16.$tn.1 { 999 sql1 { 1000 CREATE TABLE foo(x); 1001 INSERT INTO foo VALUES(1); 1002 INSERT INTO foo VALUES(2); 1003 } 1004 } {} 1005 do_test savepoint-16.$tn.2 { 1006 1007 db eval {SELECT * FROM foo} { 1008 sql1 { INSERT INTO foo VALUES(3) } 1009 sql2 { SELECT * FROM foo } 1010 sql1 { INSERT INTO foo VALUES(4) } 1011 break 1012 } 1013 1014 sql2 { CREATE INDEX fooidx ON foo(x); } 1015 sql3 { PRAGMA integrity_check } 1016 } {ok} 1017 do_test savepoint-16.$tn.3 { 1018 sql1 { SELECT * FROM foo } 1019 } {1 2 3 4} 1020 } 1021 1022 #------------------------------------------------------------------------- 1023 # This next block of tests verifies that a problem reported on the mailing 1024 # list has been resolved. At one point the second "CREATE TABLE t6" would 1025 # fail as table t6 still existed in the internal cache of the db schema 1026 # (even though it had been removed from the database by the ROLLBACK 1027 # command). 1028 # 1029 sqlite3 db test.db 1030 do_execsql_test savepoint-17.1 { 1031 BEGIN; 1032 CREATE TABLE t6(a, b); 1033 INSERT INTO t6 VALUES(1, 2); 1034 SAVEPOINT one; 1035 INSERT INTO t6 VALUES(3, 4); 1036 ROLLBACK TO one; 1037 SELECT * FROM t6; 1038 ROLLBACK; 1039 } {1 2} 1040 1041 do_execsql_test savepoint-17.2 { 1042 CREATE TABLE t6(a, b); 1043 } {} 1044 1045 finish_test 1046
