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.12 2009/02/04 10:09:04 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 272do_test savepoint-5.1.1 { 273 execsql { 274 CREATE TABLE blobs(x); 275 INSERT INTO blobs VALUES('a twentyeight character blob'); 276 } 277 set fd [db incrblob blobs x 1] 278 puts -nonewline $fd "hello" 279 catchsql {SAVEPOINT abc} 280} {1 {cannot open savepoint - SQL statements in progress}} 281do_test savepoint-5.1.2 { 282 close $fd 283 catchsql {SAVEPOINT abc} 284} {0 {}} 285 286do_test savepoint-5.2 { 287 execsql {RELEASE abc} 288 catchsql {RELEASE abc} 289} {1 {no such savepoint: abc}} 290 291do_test savepoint-5.3.1 { 292 execsql {SAVEPOINT abc} 293 catchsql {ROLLBACK TO def} 294} {1 {no such savepoint: def}} 295do_test savepoint-5.3.2 { 296 execsql {SAVEPOINT def} 297 set fd [db incrblob -readonly blobs x 1] 298 catchsql {ROLLBACK TO def} 299} {1 {cannot rollback savepoint - SQL statements in progress}} 300do_test savepoint-5.3.3 { 301 catchsql {RELEASE def} 302} {0 {}} 303do_test savepoint-5.3.4 { 304 close $fd 305 execsql {savepoint def} 306 set fd [db incrblob blobs x 1] 307 catchsql {release def} 308} {1 {cannot release savepoint - SQL statements in progress}} 309do_test savepoint-5.3.5 { 310 close $fd 311 execsql {release abc} 312} {} 313 314do_test savepoint-5.4.1 { 315 execsql { 316 SAVEPOINT main; 317 INSERT INTO blobs VALUES('another blob'); 318 } 319} {} 320do_test savepoint-5.4.2 { 321 sqlite3 db2 test.db 322 execsql { BEGIN ; SELECT * FROM blobs } db2 323 catchsql { RELEASE main } 324} {1 {database is locked}} 325do_test savepoint-5.4.3 { 326 db2 close 327 catchsql { RELEASE main } 328} {0 {}} 329do_test savepoint-5.4.4 { 330 execsql { SELECT x FROM blobs WHERE rowid = 2 } 331} {{another blob}} 332 333#------------------------------------------------------------------------- 334# The following tests, savepoint-6.*, test an incr-vacuum inside of a 335# couple of nested savepoints. 336# 337ifcapable {autovacuum && pragma} { 338 db close 339 file delete -force test.db 340 sqlite3 db test.db 341 342 do_test savepoint-6.1 { 343 execsql { 344 PRAGMA auto_vacuum = incremental; 345 CREATE TABLE t1(a, b, c); 346 CREATE INDEX i1 ON t1(a, b); 347 BEGIN; 348 INSERT INTO t1 VALUES(randstr(10,400),randstr(10,400),randstr(10,400)); 349 } 350 set r "randstr(10,400)" 351 for {set ii 0} {$ii < 10} {incr ii} { 352 execsql "INSERT INTO t1 SELECT $r, $r, $r FROM t1" 353 } 354 execsql { COMMIT } 355 } {} 356 357 integrity_check savepoint-6.2 358 359 do_test savepoint-6.3 { 360 execsql { 361 PRAGMA cache_size = 10; 362 BEGIN; 363 UPDATE t1 SET a = randstr(10,10) WHERE (rowid%4)==0; 364 SAVEPOINT one; 365 DELETE FROM t1 WHERE rowid%2; 366 PRAGMA incr_vacuum; 367 SAVEPOINT two; 368 INSERT INTO t1 SELECT randstr(10,400), randstr(10,400), c FROM t1; 369 DELETE FROM t1 WHERE rowid%2; 370 PRAGMA incr_vacuum; 371 ROLLBACK TO one; 372 COMMIT; 373 } 374 } {} 375 376 integrity_check savepoint-6.4 377} 378 379#------------------------------------------------------------------------- 380# The following tests, savepoint-7.*, attempt to break the logic 381# surrounding savepoints by growing and shrinking the database file. 382# 383db close 384file delete -force test.db 385sqlite3 db test.db 386 387do_test savepoint-7.1 { 388 execsql { 389 PRAGMA auto_vacuum = incremental; 390 PRAGMA cache_size = 10; 391 BEGIN; 392 CREATE TABLE t1(a PRIMARY KEY, b); 393 INSERT INTO t1(a) VALUES('alligator'); 394 INSERT INTO t1(a) VALUES('angelfish'); 395 INSERT INTO t1(a) VALUES('ant'); 396 INSERT INTO t1(a) VALUES('antelope'); 397 INSERT INTO t1(a) VALUES('ape'); 398 INSERT INTO t1(a) VALUES('baboon'); 399 INSERT INTO t1(a) VALUES('badger'); 400 INSERT INTO t1(a) VALUES('bear'); 401 INSERT INTO t1(a) VALUES('beetle'); 402 INSERT INTO t1(a) VALUES('bird'); 403 INSERT INTO t1(a) VALUES('bison'); 404 UPDATE t1 SET b = randstr(1000,1000); 405 UPDATE t1 SET b = b||randstr(1000,1000); 406 UPDATE t1 SET b = b||randstr(1000,1000); 407 UPDATE t1 SET b = b||randstr(10,1000); 408 COMMIT; 409 } 410 expr ([execsql { PRAGMA page_count }] > 20) 411} {1} 412do_test savepoint-7.2.1 { 413 execsql { 414 BEGIN; 415 SAVEPOINT one; 416 CREATE TABLE t2(a, b); 417 INSERT INTO t2 SELECT a, b FROM t1; 418 ROLLBACK TO one; 419 } 420 execsql { 421 PRAGMA integrity_check; 422 } 423} {ok} 424do_test savepoint-7.2.2 { 425 execsql { 426 COMMIT; 427 PRAGMA integrity_check; 428 } 429} {ok} 430 431do_test savepoint-7.3.1 { 432 execsql { 433 CREATE TABLE t2(a, b); 434 INSERT INTO t2 SELECT a, b FROM t1; 435 } 436} {} 437do_test savepoint-7.3.2 { 438 execsql { 439 BEGIN; 440 SAVEPOINT one; 441 DELETE FROM t2; 442 PRAGMA incremental_vacuum; 443 SAVEPOINT two; 444 INSERT INTO t2 SELECT a, b FROM t1; 445 ROLLBACK TO two; 446 COMMIT; 447 } 448 execsql { PRAGMA integrity_check } 449} {ok} 450 451do_test savepoint-7.4.1 { 452 db close 453 file delete -force test.db 454 sqlite3 db test.db 455 execsql { 456 PRAGMA auto_vacuum = incremental; 457 CREATE TABLE t1(a, b, PRIMARY KEY(a, b)); 458 INSERT INTO t1 VALUES(randstr(1000,1000), randstr(1000,1000)); 459 BEGIN; 460 DELETE FROM t1; 461 SAVEPOINT one; 462 PRAGMA incremental_vacuum; 463 ROLLBACK TO one; 464 COMMIT; 465 } 466 467 execsql { PRAGMA integrity_check } 468} {ok} 469 470do_test savepoint-7.5.1 { 471 execsql { 472 PRAGMA incremental_vacuum; 473 CREATE TABLE t5(x, y); 474 INSERT INTO t5 VALUES(1, randstr(1000,1000)); 475 INSERT INTO t5 VALUES(2, randstr(1000,1000)); 476 INSERT INTO t5 VALUES(3, randstr(1000,1000)); 477 478 BEGIN; 479 INSERT INTO t5 VALUES(4, randstr(1000,1000)); 480 INSERT INTO t5 VALUES(5, randstr(1000,1000)); 481 DELETE FROM t5 WHERE x=1 OR x=2; 482 SAVEPOINT one; 483 PRAGMA incremental_vacuum; 484 SAVEPOINT two; 485 INSERT INTO t5 VALUES(1, randstr(1000,1000)); 486 INSERT INTO t5 VALUES(2, randstr(1000,1000)); 487 ROLLBACK TO two; 488 ROLLBACK TO one; 489 COMMIT; 490 PRAGMA integrity_check; 491 } 492} {ok} 493do_test savepoint-7.5.2 { 494 execsql { 495 DROP TABLE t5; 496 } 497} {} 498 499# Test oddly named and quoted savepoints. 500# 501do_test savepoint-8-1 { 502 execsql { SAVEPOINT "save1" } 503 execsql { RELEASE save1 } 504} {} 505do_test savepoint-8-2 { 506 execsql { SAVEPOINT "Including whitespace " } 507 execsql { RELEASE "including Whitespace " } 508} {} 509 510# Test that the authorization callback works. 511# 512ifcapable auth { 513 proc auth {args} { 514 eval lappend ::authdata $args 515 return SQLITE_OK 516 } 517 db auth auth 518 519 do_test savepoint-9.1 { 520 set ::authdata [list] 521 execsql { SAVEPOINT sp1 } 522 set ::authdata 523 } {SQLITE_SAVEPOINT BEGIN sp1 {} {}} 524 do_test savepoint-9.2 { 525 set ::authdata [list] 526 execsql { ROLLBACK TO sp1 } 527 set ::authdata 528 } {SQLITE_SAVEPOINT ROLLBACK sp1 {} {}} 529 do_test savepoint-9.3 { 530 set ::authdata [list] 531 execsql { RELEASE sp1 } 532 set ::authdata 533 } {SQLITE_SAVEPOINT RELEASE sp1 {} {}} 534 535 proc auth {args} { 536 eval lappend ::authdata $args 537 return SQLITE_DENY 538 } 539 db auth auth 540 541 do_test savepoint-9.4 { 542 set ::authdata [list] 543 set res [catchsql { SAVEPOINT sp1 }] 544 concat $::authdata $res 545 } {SQLITE_SAVEPOINT BEGIN sp1 {} {} 1 {not authorized}} 546 do_test savepoint-9.5 { 547 set ::authdata [list] 548 set res [catchsql { ROLLBACK TO sp1 }] 549 concat $::authdata $res 550 } {SQLITE_SAVEPOINT ROLLBACK sp1 {} {} 1 {not authorized}} 551 do_test savepoint-9.6 { 552 set ::authdata [list] 553 set res [catchsql { RELEASE sp1 }] 554 concat $::authdata $res 555 } {SQLITE_SAVEPOINT RELEASE sp1 {} {} 1 {not authorized}} 556 557 catch { db eval ROLLBACK } 558 db auth "" 559} 560 561#------------------------------------------------------------------------- 562# The following tests - savepoint-10.* - test the interaction of 563# savepoints and ATTACH statements. 564# 565 566# First make sure it is not possible to attach or detach a database while 567# a savepoint is open (it is not possible if any transaction is open). 568# 569do_test savepoint-10.1.1 { 570 catchsql { 571 SAVEPOINT one; 572 ATTACH 'test2.db' AS aux; 573 } 574} {1 {cannot ATTACH database within transaction}} 575do_test savepoint-10.1.2 { 576 execsql { 577 RELEASE one; 578 ATTACH 'test2.db' AS aux; 579 } 580 catchsql { 581 SAVEPOINT one; 582 DETACH aux; 583 } 584} {1 {cannot DETACH database within transaction}} 585do_test savepoint-10.1.3 { 586 execsql { 587 RELEASE one; 588 DETACH aux; 589 } 590} {} 591 592# The lock state of the TEMP database can vary if SQLITE_TEMP_STORE=3 593# And the following set of tests is only really interested in the status 594# of the aux1 and aux2 locks. So record the current lock status of 595# TEMP for use in the answers. 596set templockstate [lindex [db eval {PRAGMA lock_status}] 3] 597 598 599do_test savepoint-10.2.1 { 600 file delete -force test3.db 601 file delete -force test2.db 602 execsql { 603 ATTACH 'test2.db' AS aux1; 604 ATTACH 'test3.db' AS aux2; 605 DROP TABLE t1; 606 CREATE TABLE main.t1(x, y); 607 CREATE TABLE aux1.t2(x, y); 608 CREATE TABLE aux2.t3(x, y); 609 SELECT name FROM sqlite_master 610 UNION ALL 611 SELECT name FROM aux1.sqlite_master 612 UNION ALL 613 SELECT name FROM aux2.sqlite_master; 614 } 615} {t1 t2 t3} 616do_test savepoint-10.2.2 { 617 execsql { PRAGMA lock_status } 618} [list main unlocked temp $templockstate aux1 unlocked aux2 unlocked] 619 620do_test savepoint-10.2.3 { 621 execsql { 622 SAVEPOINT one; 623 INSERT INTO t1 VALUES(1, 2); 624 PRAGMA lock_status; 625 } 626} [list main reserved temp $templockstate aux1 unlocked aux2 unlocked] 627do_test savepoint-10.2.4 { 628 execsql { 629 INSERT INTO t3 VALUES(3, 4); 630 PRAGMA lock_status; 631 } 632} [list main reserved temp $templockstate aux1 unlocked aux2 reserved] 633do_test savepoint-10.2.5 { 634 execsql { 635 SAVEPOINT two; 636 INSERT INTO t2 VALUES(5, 6); 637 PRAGMA lock_status; 638 } 639} [list main reserved temp $templockstate aux1 reserved aux2 reserved] 640do_test savepoint-10.2.6 { 641 execsql { SELECT * FROM t2 } 642} {5 6} 643do_test savepoint-10.2.7 { 644 execsql { ROLLBACK TO two } 645 execsql { SELECT * FROM t2 } 646} {} 647do_test savepoint-10.2.8 { 648 execsql { PRAGMA lock_status } 649} [list main reserved temp $templockstate aux1 reserved aux2 reserved] 650do_test savepoint-10.2.9 { 651 execsql { SELECT 'a', * FROM t1 UNION ALL SELECT 'b', * FROM t3 } 652} {a 1 2 b 3 4} 653do_test savepoint-10.2.9 { 654 execsql { 655 INSERT INTO t2 VALUES(5, 6); 656 RELEASE one; 657 } 658 execsql { 659 SELECT * FROM t1; 660 SELECT * FROM t2; 661 SELECT * FROM t3; 662 } 663} {1 2 5 6 3 4} 664do_test savepoint-10.2.9 { 665 execsql { PRAGMA lock_status } 666} [list main unlocked temp $templockstate aux1 unlocked aux2 unlocked] 667 668do_test savepoint-10.2.10 { 669 execsql { 670 SAVEPOINT one; 671 INSERT INTO t1 VALUES('a', 'b'); 672 SAVEPOINT two; 673 INSERT INTO t2 VALUES('c', 'd'); 674 SAVEPOINT three; 675 INSERT INTO t3 VALUES('e', 'f'); 676 } 677 execsql { 678 SELECT * FROM t1; 679 SELECT * FROM t2; 680 SELECT * FROM t3; 681 } 682} {1 2 a b 5 6 c d 3 4 e f} 683do_test savepoint-10.2.11 { 684 execsql { ROLLBACK TO two } 685 execsql { 686 SELECT * FROM t1; 687 SELECT * FROM t2; 688 SELECT * FROM t3; 689 } 690} {1 2 a b 5 6 3 4} 691do_test savepoint-10.2.12 { 692 execsql { 693 INSERT INTO t3 VALUES('g', 'h'); 694 ROLLBACK TO two; 695 } 696 execsql { 697 SELECT * FROM t1; 698 SELECT * FROM t2; 699 SELECT * FROM t3; 700 } 701} {1 2 a b 5 6 3 4} 702do_test savepoint-10.2.13 { 703 execsql { ROLLBACK } 704 execsql { 705 SELECT * FROM t1; 706 SELECT * FROM t2; 707 SELECT * FROM t3; 708 } 709} {1 2 5 6 3 4} 710do_test savepoint-10.2.14 { 711 execsql { PRAGMA lock_status } 712} [list main unlocked temp $templockstate aux1 unlocked aux2 unlocked] 713 714#------------------------------------------------------------------------- 715# The following tests - savepoint-11.* - test the interaction of 716# savepoints and creating or dropping tables and indexes in 717# auto-vacuum mode. 718# 719do_test savepoint-11.1 { 720 db close 721 file delete -force test.db 722 sqlite3 db test.db 723 execsql { 724 PRAGMA auto_vacuum = full; 725 CREATE TABLE t1(a, b, UNIQUE(a, b)); 726 INSERT INTO t1 VALUES(1, randstr(1000,1000)); 727 INSERT INTO t1 VALUES(2, randstr(1000,1000)); 728 } 729} {} 730do_test savepoint-11.2 { 731 execsql { 732 SAVEPOINT one; 733 CREATE TABLE t2(a, b, UNIQUE(a, b)); 734 SAVEPOINT two; 735 CREATE TABLE t3(a, b, UNIQUE(a, b)); 736 } 737} {} 738integrity_check savepoint-11.3 739do_test savepoint-11.4 { 740 execsql { ROLLBACK TO two } 741} {} 742integrity_check savepoint-11.5 743do_test savepoint-11.6 { 744 execsql { 745 CREATE TABLE t3(a, b, UNIQUE(a, b)); 746 ROLLBACK TO one; 747 } 748} {} 749integrity_check savepoint-11.7 750do_test savepoint-11.8 { 751 execsql { ROLLBACK } 752 file size test.db 753} {8192} 754 755 756do_test savepoint-11.9 { 757 execsql { 758 DROP TABLE IF EXISTS t1; 759 DROP TABLE IF EXISTS t2; 760 DROP TABLE IF EXISTS t3; 761 } 762} {} 763do_test savepoint-11.10 { 764 execsql { 765 BEGIN; 766 CREATE TABLE t1(a, b); 767 CREATE TABLE t2(x, y); 768 INSERT INTO t2 VALUES(1, 2); 769 SAVEPOINT one; 770 INSERT INTO t2 VALUES(3, 4); 771 SAVEPOINT two; 772 DROP TABLE t1; 773 ROLLBACK TO two; 774 } 775 execsql {SELECT * FROM t2} 776} {1 2 3 4} 777do_test savepoint-11.11 { 778 execsql COMMIT 779} {} 780do_test savepoint-11.12 { 781 execsql {SELECT * FROM t2} 782} {1 2 3 4} 783 784#------------------------------------------------------------------------- 785# The following tests - savepoint-12.* - test the interaction of 786# savepoints and "ON CONFLICT ROLLBACK" clauses. 787# 788do_test savepoint-12.1 { 789 execsql { 790 CREATE TABLE t4(a PRIMARY KEY, b); 791 INSERT INTO t4 VALUES(1, 'one'); 792 } 793} {} 794do_test savepoint-12.2 { 795 # The final statement of the following SQL hits a constraint when the 796 # conflict handling mode is "OR ROLLBACK" and there are a couple of 797 # open savepoints. At one point this would fail to clear the internal 798 # record of the open savepoints, resulting in an assert() failure 799 # later on. 800 # 801 catchsql { 802 BEGIN; 803 INSERT INTO t4 VALUES(2, 'two'); 804 SAVEPOINT sp1; 805 INSERT INTO t4 VALUES(3, 'three'); 806 SAVEPOINT sp2; 807 INSERT OR ROLLBACK INTO t4 VALUES(1, 'one'); 808 } 809} {1 {column a is not unique}} 810do_test savepoint-12.3 { 811 sqlite3_get_autocommit db 812} {1} 813do_test savepoint-12.4 { 814 execsql { SAVEPOINT one } 815} {} 816 817#------------------------------------------------------------------------- 818# The following tests - savepoint-13.* - test the interaction of 819# savepoints and "journal_mode = off". 820# 821do_test savepoint-13.1 { 822 db close 823 catch {file delete -force test.db} 824 sqlite3 db test.db 825 execsql { 826 BEGIN; 827 CREATE TABLE t1(a PRIMARY KEY, b); 828 INSERT INTO t1 VALUES(1, 2); 829 COMMIT; 830 PRAGMA journal_mode = off; 831 } 832} {off} 833do_test savepoint-13.2 { 834 execsql { 835 BEGIN; 836 INSERT INTO t1 VALUES(3, 4); 837 INSERT INTO t1 SELECT a+4,b+4 FROM t1; 838 COMMIT; 839 } 840} {} 841do_test savepoint-13.3 { 842 execsql { 843 BEGIN; 844 INSERT INTO t1 VALUES(9, 10); 845 SAVEPOINT s1; 846 INSERT INTO t1 VALUES(11, 12); 847 COMMIT; 848 } 849} {} 850do_test savepoint-13.4 { 851 execsql { 852 BEGIN; 853 INSERT INTO t1 VALUES(13, 14); 854 SAVEPOINT s1; 855 INSERT INTO t1 VALUES(15, 16); 856 ROLLBACK TO s1; 857 ROLLBACK; 858 SELECT * FROM t1; 859 } 860} {1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16} 861 862finish_test 863