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