gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/savepoint.test (about) 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.1 { 307 execsql {SAVEPOINT def} 308 set fd [db incrblob -readonly blobs x 1] 309 set rc [catch {seek $fd 0;read $fd} res] 310 lappend rc $res 311 } {0 {hellontyeight character blob}} 312 do_test savepoint-5.3.2.2 { 313 catchsql {ROLLBACK TO def} 314 } {0 {}} 315 do_test savepoint-5.3.2.3 { 316 set rc [catch {seek $fd 0; read $fd} res] 317 set rc 318 } {0} 319 do_test savepoint-5.3.3 { 320 catchsql {RELEASE def} 321 } {0 {}} 322 do_test savepoint-5.3.4 { 323 close $fd 324 execsql {savepoint def} 325 set fd [db incrblob blobs x 1] 326 catchsql {release def} 327 } {1 {cannot release savepoint - SQL statements in progress}} 328 do_test savepoint-5.3.5 { 329 close $fd 330 execsql {release abc} 331 } {} 332 333 # Rollback mode: 334 # 335 # Open a savepoint transaction and insert a row into the database. Then, 336 # using a second database handle, open a read-only transaction on the 337 # database file. Check that the savepoint transaction cannot be committed 338 # until after the read-only transaction has been closed. 339 # 340 # WAL mode: 341 # 342 # As above, except that the savepoint transaction can be successfully 343 # committed before the read-only transaction has been closed. 344 # 345 do_test savepoint-5.4.1 { 346 execsql { 347 SAVEPOINT main; 348 INSERT INTO blobs VALUES('another blob'); 349 } 350 } {} 351 do_test savepoint-5.4.2 { 352 sqlite3 db2 test.db 353 execsql { BEGIN ; SELECT count(*) FROM blobs } db2 354 } {1} 355 if {[wal_is_wal_mode]} { 356 do_test savepoint-5.4.3 { catchsql "RELEASE main" } {0 {}} 357 do_test savepoint-5.4.4 { db2 close } {} 358 } else { 359 do_test savepoint-5.4.3 { 360 catchsql { RELEASE main } 361 } {1 {database is locked}} 362 do_test savepoint-5.4.4 { 363 db2 close 364 catchsql { RELEASE main } 365 } {0 {}} 366 } 367 do_test savepoint-5.4.5 { 368 execsql { SELECT x FROM blobs WHERE rowid = 2 } 369 } {{another blob}} 370 do_test savepoint-5.4.6 { 371 execsql { SELECT count(*) FROM blobs } 372 } {2} 373 } 374 wal_check_journal_mode savepoint-5.5 375 376 #------------------------------------------------------------------------- 377 # The following tests, savepoint-6.*, test an incr-vacuum inside of a 378 # couple of nested savepoints. 379 # 380 ifcapable {autovacuum && pragma} { 381 db close 382 forcedelete test.db 383 sqlite3 db test.db 384 385 do_test savepoint-6.1 { 386 execsql { PRAGMA auto_vacuum = incremental } 387 wal_set_journal_mode 388 execsql { 389 CREATE TABLE t1(a, b, c); 390 CREATE INDEX i1 ON t1(a, b); 391 BEGIN; 392 INSERT INTO t1 VALUES(randstr(10,400),randstr(10,400),randstr(10,400)); 393 } 394 set r "randstr(10,400)" 395 for {set ii 0} {$ii < 10} {incr ii} { 396 execsql "INSERT INTO t1 SELECT $r, $r, $r FROM t1" 397 } 398 execsql { COMMIT } 399 } {} 400 401 integrity_check savepoint-6.2 402 403 do_test savepoint-6.3 { 404 execsql { 405 PRAGMA cache_size = 10; 406 BEGIN; 407 UPDATE t1 SET a = randstr(10,10) WHERE (rowid%4)==0; 408 SAVEPOINT one; 409 DELETE FROM t1 WHERE rowid%2; 410 PRAGMA incr_vacuum; 411 SAVEPOINT two; 412 INSERT INTO t1 SELECT randstr(10,400), randstr(10,400), c FROM t1; 413 DELETE FROM t1 WHERE rowid%2; 414 PRAGMA incr_vacuum; 415 ROLLBACK TO one; 416 COMMIT; 417 } 418 } {} 419 420 integrity_check savepoint-6.4 421 422 wal_check_journal_mode savepoint-6.5 423 } 424 425 #------------------------------------------------------------------------- 426 # The following tests, savepoint-7.*, attempt to break the logic 427 # surrounding savepoints by growing and shrinking the database file. 428 # 429 db close 430 forcedelete test.db 431 sqlite3 db test.db 432 433 do_test savepoint-7.1 { 434 execsql { PRAGMA auto_vacuum = incremental } 435 wal_set_journal_mode 436 execsql { 437 PRAGMA cache_size = 10; 438 BEGIN; 439 CREATE TABLE t1(a PRIMARY KEY, b); 440 INSERT INTO t1(a) VALUES('alligator'); 441 INSERT INTO t1(a) VALUES('angelfish'); 442 INSERT INTO t1(a) VALUES('ant'); 443 INSERT INTO t1(a) VALUES('antelope'); 444 INSERT INTO t1(a) VALUES('ape'); 445 INSERT INTO t1(a) VALUES('baboon'); 446 INSERT INTO t1(a) VALUES('badger'); 447 INSERT INTO t1(a) VALUES('bear'); 448 INSERT INTO t1(a) VALUES('beetle'); 449 INSERT INTO t1(a) VALUES('bird'); 450 INSERT INTO t1(a) VALUES('bison'); 451 UPDATE t1 SET b = randstr(1000,1000); 452 UPDATE t1 SET b = b||randstr(1000,1000); 453 UPDATE t1 SET b = b||randstr(1000,1000); 454 UPDATE t1 SET b = b||randstr(10,1000); 455 COMMIT; 456 } 457 expr ([execsql { PRAGMA page_count }] > 20) 458 } {1} 459 do_test savepoint-7.2.1 { 460 execsql { 461 BEGIN; 462 SAVEPOINT one; 463 CREATE TABLE t2(a, b); 464 INSERT INTO t2 SELECT a, b FROM t1; 465 ROLLBACK TO one; 466 } 467 execsql { 468 PRAGMA integrity_check; 469 } 470 } {ok} 471 do_test savepoint-7.2.2 { 472 execsql { 473 COMMIT; 474 PRAGMA integrity_check; 475 } 476 } {ok} 477 478 do_test savepoint-7.3.1 { 479 execsql { 480 CREATE TABLE t2(a, b); 481 INSERT INTO t2 SELECT a, b FROM t1; 482 } 483 } {} 484 do_test savepoint-7.3.2 { 485 execsql { 486 BEGIN; 487 SAVEPOINT one; 488 DELETE FROM t2; 489 PRAGMA incremental_vacuum; 490 SAVEPOINT two; 491 INSERT INTO t2 SELECT a, b FROM t1; 492 ROLLBACK TO two; 493 COMMIT; 494 } 495 execsql { PRAGMA integrity_check } 496 } {ok} 497 wal_check_journal_mode savepoint-7.3.3 498 499 do_test savepoint-7.4.1 { 500 db close 501 forcedelete test.db 502 sqlite3 db test.db 503 execsql { PRAGMA auto_vacuum = incremental } 504 wal_set_journal_mode 505 execsql { 506 CREATE TABLE t1(a, b, PRIMARY KEY(a, b)); 507 INSERT INTO t1 VALUES(randstr(1000,1000), randstr(1000,1000)); 508 BEGIN; 509 DELETE FROM t1; 510 SAVEPOINT one; 511 PRAGMA incremental_vacuum; 512 ROLLBACK TO one; 513 COMMIT; 514 } 515 516 execsql { PRAGMA integrity_check } 517 } {ok} 518 519 do_test savepoint-7.5.1 { 520 execsql { 521 PRAGMA incremental_vacuum; 522 CREATE TABLE t5(x, y); 523 INSERT INTO t5 VALUES(1, randstr(1000,1000)); 524 INSERT INTO t5 VALUES(2, randstr(1000,1000)); 525 INSERT INTO t5 VALUES(3, randstr(1000,1000)); 526 527 BEGIN; 528 INSERT INTO t5 VALUES(4, randstr(1000,1000)); 529 INSERT INTO t5 VALUES(5, randstr(1000,1000)); 530 DELETE FROM t5 WHERE x=1 OR x=2; 531 SAVEPOINT one; 532 PRAGMA incremental_vacuum; 533 SAVEPOINT two; 534 INSERT INTO t5 VALUES(1, randstr(1000,1000)); 535 INSERT INTO t5 VALUES(2, randstr(1000,1000)); 536 ROLLBACK TO two; 537 ROLLBACK TO one; 538 COMMIT; 539 PRAGMA integrity_check; 540 } 541 } {ok} 542 do_test savepoint-7.5.2 { 543 execsql { 544 DROP TABLE t5; 545 } 546 } {} 547 wal_check_journal_mode savepoint-7.5.3 548 549 # Test oddly named and quoted savepoints. 550 # 551 do_test savepoint-8-1 { 552 execsql { SAVEPOINT "save1" } 553 execsql { RELEASE save1 } 554 } {} 555 do_test savepoint-8-2 { 556 execsql { SAVEPOINT "Including whitespace " } 557 execsql { RELEASE "including Whitespace " } 558 } {} 559 560 # Test that the authorization callback works. 561 # 562 ifcapable auth { 563 proc auth {args} { 564 eval lappend ::authdata [lrange $args 0 4] 565 return SQLITE_OK 566 } 567 db auth auth 568 569 do_test savepoint-9.1 { 570 set ::authdata [list] 571 execsql { SAVEPOINT sp1 } 572 set ::authdata 573 } {SQLITE_SAVEPOINT BEGIN sp1 {} {}} 574 do_test savepoint-9.2 { 575 set ::authdata [list] 576 execsql { ROLLBACK TO sp1 } 577 set ::authdata 578 } {SQLITE_SAVEPOINT ROLLBACK sp1 {} {}} 579 do_test savepoint-9.3 { 580 set ::authdata [list] 581 execsql { RELEASE sp1 } 582 set ::authdata 583 } {SQLITE_SAVEPOINT RELEASE sp1 {} {}} 584 585 proc auth {args} { 586 eval lappend ::authdata [lrange $args 0 4] 587 return SQLITE_DENY 588 } 589 db auth auth 590 591 do_test savepoint-9.4 { 592 set ::authdata [list] 593 set res [catchsql { SAVEPOINT sp1 }] 594 concat $::authdata $res 595 } {SQLITE_SAVEPOINT BEGIN sp1 {} {} 1 {not authorized}} 596 do_test savepoint-9.5 { 597 set ::authdata [list] 598 set res [catchsql { ROLLBACK TO sp1 }] 599 concat $::authdata $res 600 } {SQLITE_SAVEPOINT ROLLBACK sp1 {} {} 1 {not authorized}} 601 do_test savepoint-9.6 { 602 set ::authdata [list] 603 set res [catchsql { RELEASE sp1 }] 604 concat $::authdata $res 605 } {SQLITE_SAVEPOINT RELEASE sp1 {} {} 1 {not authorized}} 606 607 catch { db eval ROLLBACK } 608 db auth "" 609 } 610 611 #------------------------------------------------------------------------- 612 # The following tests - savepoint-10.* - test the interaction of 613 # savepoints and ATTACH statements. 614 # 615 616 # First make sure it is not possible to attach or detach a database while 617 # a savepoint is open (it is not possible if any transaction is open). 618 # 619 # UPDATE 2017-07-26: It is not possible to ATTACH and DETACH within a 620 # a transaction. 621 # 622 do_test savepoint-10.1.1 { 623 catchsql { 624 SAVEPOINT one; 625 ATTACH 'test2.db' AS aux; 626 DETACH aux; 627 } 628 } {0 {}} 629 do_test savepoint-10.1.2 { 630 execsql { 631 RELEASE one; 632 ATTACH 'test2.db' AS aux; 633 } 634 catchsql { 635 SAVEPOINT one; 636 DETACH aux; 637 ATTACH 'test2.db' AS aux; 638 } 639 } {0 {}} 640 do_test savepoint-10.1.3 { 641 execsql { 642 RELEASE one; 643 DETACH aux; 644 } 645 } {} 646 647 # The lock state of the TEMP database can vary if SQLITE_TEMP_STORE=3 648 # And the following set of tests is only really interested in the status 649 # of the aux1 and aux2 locks. So record the current lock status of 650 # TEMP for use in the answers. 651 set templockstate [lindex [db eval {PRAGMA lock_status}] 3] 652 653 654 if {[wal_is_wal_mode]==0} { 655 do_test savepoint-10.2.1 { 656 forcedelete test3.db 657 forcedelete test2.db 658 execsql { 659 ATTACH 'test2.db' AS aux1; 660 ATTACH 'test3.db' AS aux2; 661 DROP TABLE t1; 662 CREATE TABLE main.t1(x, y); 663 CREATE TABLE aux1.t2(x, y); 664 CREATE TABLE aux2.t3(x, y); 665 SELECT name FROM sqlite_master; 666 SELECT name FROM aux1.sqlite_master; 667 SELECT name FROM aux2.sqlite_master; 668 } 669 } {t1 t2 t3} 670 do_test savepoint-10.2.2 { 671 execsql { PRAGMA lock_status } 672 } [list main unlocked temp $templockstate aux1 unlocked aux2 unlocked] 673 674 do_test savepoint-10.2.3 { 675 execsql { 676 SAVEPOINT one; 677 INSERT INTO t1 VALUES(1, 2); 678 PRAGMA lock_status; 679 } 680 } [list main reserved temp $templockstate aux1 unlocked aux2 unlocked] 681 do_test savepoint-10.2.4 { 682 execsql { 683 INSERT INTO t3 VALUES(3, 4); 684 PRAGMA lock_status; 685 } 686 } [list main reserved temp $templockstate aux1 unlocked aux2 reserved] 687 do_test savepoint-10.2.5 { 688 execsql { 689 SAVEPOINT two; 690 INSERT INTO t2 VALUES(5, 6); 691 PRAGMA lock_status; 692 } 693 } [list main reserved temp $templockstate aux1 reserved aux2 reserved] 694 do_test savepoint-10.2.6 { 695 execsql { SELECT * FROM t2 } 696 } {5 6} 697 do_test savepoint-10.2.7 { 698 execsql { ROLLBACK TO two } 699 execsql { SELECT * FROM t2 } 700 } {} 701 do_test savepoint-10.2.8 { 702 execsql { PRAGMA lock_status } 703 } [list main reserved temp $templockstate aux1 reserved aux2 reserved] 704 do_test savepoint-10.2.9 { 705 execsql { SELECT 'a', * FROM t1 ; SELECT 'b', * FROM t3 } 706 } {a 1 2 b 3 4} 707 do_test savepoint-10.2.9 { 708 execsql { 709 INSERT INTO t2 VALUES(5, 6); 710 RELEASE one; 711 } 712 execsql { 713 SELECT * FROM t1; 714 SELECT * FROM t2; 715 SELECT * FROM t3; 716 } 717 } {1 2 5 6 3 4} 718 do_test savepoint-10.2.9 { 719 execsql { PRAGMA lock_status } 720 } [list main unlocked temp $templockstate aux1 unlocked aux2 unlocked] 721 722 do_test savepoint-10.2.10 { 723 execsql { 724 SAVEPOINT one; 725 INSERT INTO t1 VALUES('a', 'b'); 726 SAVEPOINT two; 727 INSERT INTO t2 VALUES('c', 'd'); 728 SAVEPOINT three; 729 INSERT INTO t3 VALUES('e', 'f'); 730 } 731 execsql { 732 SELECT * FROM t1; 733 SELECT * FROM t2; 734 SELECT * FROM t3; 735 } 736 } {1 2 a b 5 6 c d 3 4 e f} 737 do_test savepoint-10.2.11 { 738 execsql { ROLLBACK TO two } 739 execsql { 740 SELECT * FROM t1; 741 SELECT * FROM t2; 742 SELECT * FROM t3; 743 } 744 } {1 2 a b 5 6 3 4} 745 do_test savepoint-10.2.12 { 746 execsql { 747 INSERT INTO t3 VALUES('g', 'h'); 748 ROLLBACK TO two; 749 } 750 execsql { 751 SELECT * FROM t1; 752 SELECT * FROM t2; 753 SELECT * FROM t3; 754 } 755 } {1 2 a b 5 6 3 4} 756 do_test savepoint-10.2.13 { 757 execsql { ROLLBACK } 758 execsql { 759 SELECT * FROM t1; 760 SELECT * FROM t2; 761 SELECT * FROM t3; 762 } 763 } {1 2 5 6 3 4} 764 do_test savepoint-10.2.14 { 765 execsql { PRAGMA lock_status } 766 } [list main unlocked temp $templockstate aux1 unlocked aux2 unlocked] 767 } 768 769 #------------------------------------------------------------------------- 770 # The following tests - savepoint-11.* - test the interaction of 771 # savepoints and creating or dropping tables and indexes in 772 # auto-vacuum mode. 773 # 774 do_test savepoint-11.1 { 775 db close 776 forcedelete test.db 777 sqlite3 db test.db 778 execsql { PRAGMA auto_vacuum = full; } 779 wal_set_journal_mode 780 execsql { 781 CREATE TABLE t1(a, b, UNIQUE(a, b)); 782 INSERT INTO t1 VALUES(1, randstr(1000,1000)); 783 INSERT INTO t1 VALUES(2, randstr(1000,1000)); 784 } 785 } {} 786 do_test savepoint-11.2 { 787 execsql { 788 SAVEPOINT one; 789 CREATE TABLE t2(a, b, UNIQUE(a, b)); 790 SAVEPOINT two; 791 CREATE TABLE t3(a, b, UNIQUE(a, b)); 792 } 793 } {} 794 integrity_check savepoint-11.3 795 do_test savepoint-11.4 { 796 execsql { ROLLBACK TO two } 797 } {} 798 integrity_check savepoint-11.5 799 do_test savepoint-11.6 { 800 execsql { 801 CREATE TABLE t3(a, b, UNIQUE(a, b)); 802 ROLLBACK TO one; 803 } 804 } {} 805 integrity_check savepoint-11.7 806 do_test savepoint-11.8 { 807 execsql { ROLLBACK } 808 execsql { PRAGMA wal_checkpoint } 809 file size test.db 810 } {8192} 811 812 do_test savepoint-11.9 { 813 execsql { 814 DROP TABLE IF EXISTS t1; 815 DROP TABLE IF EXISTS t2; 816 DROP TABLE IF EXISTS t3; 817 } 818 } {} 819 do_test savepoint-11.10 { 820 execsql { 821 BEGIN; 822 CREATE TABLE t1(a, b); 823 CREATE TABLE t2(x, y); 824 INSERT INTO t2 VALUES(1, 2); 825 SAVEPOINT one; 826 INSERT INTO t2 VALUES(3, 4); 827 SAVEPOINT two; 828 DROP TABLE t1; 829 ROLLBACK TO two; 830 } 831 execsql {SELECT * FROM t2} 832 } {1 2 3 4} 833 do_test savepoint-11.11 { 834 execsql COMMIT 835 } {} 836 do_test savepoint-11.12 { 837 execsql {SELECT * FROM t2} 838 } {1 2 3 4} 839 wal_check_journal_mode savepoint-11.13 840 841 #------------------------------------------------------------------------- 842 # The following tests - savepoint-12.* - test the interaction of 843 # savepoints and "ON CONFLICT ROLLBACK" clauses. 844 # 845 do_test savepoint-12.1 { 846 execsql { 847 CREATE TABLE t4(a PRIMARY KEY, b); 848 INSERT INTO t4 VALUES(1, 'one'); 849 } 850 } {} 851 do_test savepoint-12.2 { 852 # The final statement of the following SQL hits a constraint when the 853 # conflict handling mode is "OR ROLLBACK" and there are a couple of 854 # open savepoints. At one point this would fail to clear the internal 855 # record of the open savepoints, resulting in an assert() failure 856 # later on. 857 # 858 catchsql { 859 BEGIN; 860 INSERT INTO t4 VALUES(2, 'two'); 861 SAVEPOINT sp1; 862 INSERT INTO t4 VALUES(3, 'three'); 863 SAVEPOINT sp2; 864 INSERT OR ROLLBACK INTO t4 VALUES(1, 'one'); 865 } 866 } {1 {UNIQUE constraint failed: t4.a}} 867 do_test savepoint-12.3 { 868 sqlite3_get_autocommit db 869 } {1} 870 do_test savepoint-12.4 { 871 execsql { SAVEPOINT one } 872 } {} 873 wal_check_journal_mode savepoint-12.5 874 875 #------------------------------------------------------------------------- 876 # The following tests - savepoint-13.* - test the interaction of 877 # savepoints and "journal_mode = off". 878 # 879 if {[wal_is_wal_mode]==0} { 880 do_test savepoint-13.1 { 881 db close 882 catch {forcedelete test.db} 883 sqlite3 db test.db 884 execsql { 885 BEGIN; 886 CREATE TABLE t1(a PRIMARY KEY, b); 887 INSERT INTO t1 VALUES(1, 2); 888 COMMIT; 889 PRAGMA journal_mode = off; 890 } 891 } {off} 892 do_test savepoint-13.2 { 893 execsql { 894 BEGIN; 895 INSERT INTO t1 VALUES(3, 4); 896 INSERT INTO t1 SELECT a+4,b+4 FROM t1; 897 COMMIT; 898 } 899 } {} 900 do_test savepoint-13.3 { 901 execsql { 902 BEGIN; 903 INSERT INTO t1 VALUES(9, 10); 904 SAVEPOINT s1; 905 INSERT INTO t1 VALUES(11, 12); 906 COMMIT; 907 } 908 } {} 909 do_test savepoint-13.4 { 910 execsql { 911 BEGIN; 912 INSERT INTO t1 VALUES(13, 14); 913 SAVEPOINT s1; 914 INSERT INTO t1 VALUES(15, 16); 915 ROLLBACK TO s1; 916 ROLLBACK; 917 SELECT * FROM t1; 918 } 919 } {1 2 3 4 5 6 7 8 9 10 11 12} 920 } 921 922 db close 923 delete_file test.db 924 do_multiclient_test tn { 925 do_test savepoint-14.$tn.1 { 926 sql1 { 927 CREATE TABLE foo(x); 928 INSERT INTO foo VALUES(1); 929 INSERT INTO foo VALUES(2); 930 } 931 sql2 { 932 BEGIN; 933 SELECT * FROM foo; 934 } 935 } {1 2} 936 do_test savepoint-14.$tn.2 { 937 sql1 { 938 SAVEPOINT one; 939 INSERT INTO foo VALUES(1); 940 } 941 csql1 { RELEASE one } 942 } {1 {database is locked}} 943 do_test savepoint-14.$tn.3 { 944 sql1 { ROLLBACK TO one } 945 sql2 { COMMIT } 946 sql1 { RELEASE one } 947 } {} 948 949 do_test savepoint-14.$tn.4 { 950 sql2 { 951 BEGIN; 952 SELECT * FROM foo; 953 } 954 } {1 2} 955 do_test savepoint-14.$tn.5 { 956 sql1 { 957 SAVEPOINT one; 958 INSERT INTO foo VALUES(1); 959 } 960 csql1 { RELEASE one } 961 } {1 {database is locked}} 962 do_test savepoint-14.$tn.6 { 963 sql2 { COMMIT } 964 sql1 { 965 ROLLBACK TO one; 966 INSERT INTO foo VALUES(3); 967 INSERT INTO foo VALUES(4); 968 INSERT INTO foo VALUES(5); 969 RELEASE one; 970 } 971 } {} 972 do_test savepoint-14.$tn.7 { 973 sql2 { CREATE INDEX fooidx ON foo(x); } 974 sql3 { PRAGMA integrity_check } 975 } {ok} 976 } 977 978 do_multiclient_test tn { 979 do_test savepoint-15.$tn.1 { 980 sql1 { 981 CREATE TABLE foo(x); 982 INSERT INTO foo VALUES(1); 983 INSERT INTO foo VALUES(2); 984 } 985 sql2 { BEGIN; SELECT * FROM foo; } 986 } {1 2} 987 do_test savepoint-15.$tn.2 { 988 sql1 { 989 PRAGMA locking_mode = EXCLUSIVE; 990 BEGIN; 991 INSERT INTO foo VALUES(3); 992 } 993 csql1 { COMMIT } 994 } {1 {database is locked}} 995 do_test savepoint-15.$tn.3 { 996 sql1 { ROLLBACK } 997 sql2 { COMMIT } 998 sql1 { 999 INSERT INTO foo VALUES(3); 1000 PRAGMA locking_mode = NORMAL; 1001 INSERT INTO foo VALUES(4); 1002 } 1003 sql2 { CREATE INDEX fooidx ON foo(x); } 1004 sql3 { PRAGMA integrity_check } 1005 } {ok} 1006 } 1007 1008 do_multiclient_test tn { 1009 do_test savepoint-16.$tn.1 { 1010 sql1 { 1011 CREATE TABLE foo(x); 1012 INSERT INTO foo VALUES(1); 1013 INSERT INTO foo VALUES(2); 1014 } 1015 } {} 1016 do_test savepoint-16.$tn.2 { 1017 1018 db eval {SELECT * FROM foo} { 1019 sql1 { INSERT INTO foo VALUES(3) } 1020 sql2 { SELECT * FROM foo } 1021 sql1 { INSERT INTO foo VALUES(4) } 1022 break 1023 } 1024 1025 sql2 { CREATE INDEX fooidx ON foo(x); } 1026 sql3 { PRAGMA integrity_check } 1027 } {ok} 1028 do_test savepoint-16.$tn.3 { 1029 sql1 { SELECT * FROM foo } 1030 } {1 2 3 4} 1031 } 1032 1033 #------------------------------------------------------------------------- 1034 # This next block of tests verifies that a problem reported on the mailing 1035 # list has been resolved. At one point the second "CREATE TABLE t6" would 1036 # fail as table t6 still existed in the internal cache of the db schema 1037 # (even though it had been removed from the database by the ROLLBACK 1038 # command). 1039 # 1040 sqlite3 db test.db 1041 do_execsql_test savepoint-17.1 { 1042 BEGIN; 1043 CREATE TABLE t6(a, b); 1044 INSERT INTO t6 VALUES(1, 2); 1045 SAVEPOINT one; 1046 INSERT INTO t6 VALUES(3, 4); 1047 ROLLBACK TO one; 1048 SELECT * FROM t6; 1049 ROLLBACK; 1050 } {1 2} 1051 1052 do_execsql_test savepoint-17.2 { 1053 CREATE TABLE t6(a, b); 1054 } {} 1055 1056 finish_test