gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/trans.test (about) 1 # 2001 September 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 # This file implements regression tests for SQLite library. The 12 # focus of this script is database locks. 13 # 14 15 16 set testdir [file dirname $argv0] 17 source $testdir/tester.tcl 18 19 # Create several tables to work with. 20 # 21 wal_set_journal_mode 22 do_test trans-1.0 { 23 execsql { 24 CREATE TABLE one(a int PRIMARY KEY, b text); 25 INSERT INTO one VALUES(1,'one'); 26 INSERT INTO one VALUES(2,'two'); 27 INSERT INTO one VALUES(3,'three'); 28 SELECT b FROM one ORDER BY a; 29 } 30 } {one two three} 31 integrity_check trans-1.0.1 32 do_test trans-1.1 { 33 execsql { 34 CREATE TABLE two(a int PRIMARY KEY, b text); 35 INSERT INTO two VALUES(1,'I'); 36 INSERT INTO two VALUES(5,'V'); 37 INSERT INTO two VALUES(10,'X'); 38 SELECT b FROM two ORDER BY a; 39 } 40 } {I V X} 41 do_test trans-1.2.1 { 42 sqlite3_txn_state db 43 } {0} 44 do_test trans-1.2.2 { 45 sqlite3_txn_state db main 46 } {0} 47 do_test trans-1.2.3 { 48 sqlite3_txn_state db temp 49 } {0} 50 do_test trans-1.2.4 { 51 sqlite3_txn_state db no-such-schema 52 } {-1} 53 54 do_test trans-1.9 { 55 sqlite3 altdb test.db 56 execsql {SELECT b FROM one ORDER BY a} altdb 57 } {one two three} 58 do_test trans-1.10 { 59 execsql {SELECT b FROM two ORDER BY a} altdb 60 } {I V X} 61 integrity_check trans-1.11 62 wal_check_journal_mode trans-1.12 63 64 # Basic transactions 65 # 66 do_test trans-2.1 { 67 set v [catch {execsql {BEGIN}} msg] 68 lappend v $msg 69 } {0 {}} 70 do_test trans-2.1b { 71 sqlite3_txn_state db 72 } {0} 73 do_test trans-2.2 { 74 set v [catch {execsql {END}} msg] 75 lappend v $msg 76 } {0 {}} 77 do_test trans-2.3 { 78 set v [catch {execsql {BEGIN TRANSACTION}} msg] 79 lappend v $msg 80 } {0 {}} 81 do_test trans-2.4 { 82 set v [catch {execsql {COMMIT TRANSACTION}} msg] 83 lappend v $msg 84 } {0 {}} 85 do_test trans-2.5 { 86 set v [catch {execsql {BEGIN TRANSACTION 'foo'}} msg] 87 lappend v $msg 88 } {0 {}} 89 do_test trans-2.6 { 90 set v [catch {execsql {ROLLBACK TRANSACTION 'foo'}} msg] 91 lappend v $msg 92 } {0 {}} 93 do_test trans-2.10 { 94 execsql { 95 BEGIN; 96 SELECT a FROM one ORDER BY a; 97 SELECT a FROM two ORDER BY a; 98 END; 99 } 100 } {1 2 3 1 5 10} 101 integrity_check trans-2.11 102 wal_check_journal_mode trans-2.12 103 104 # Check the locking behavior 105 # 106 do_test trans-3.1 { 107 execsql { 108 BEGIN; 109 UPDATE one SET a = 0 WHERE 0; 110 SELECT a FROM one ORDER BY a; 111 } 112 } {1 2 3} 113 do_test trans-3.1b { 114 sqlite3_txn_state db 115 } {2} 116 do_test trans-3.1c { 117 sqlite3_txn_state db main 118 } {2} 119 do_test trans-3.1d { 120 sqlite3_txn_state db temp 121 } {0} 122 123 do_test trans-3.2 { 124 catchsql { 125 SELECT a FROM two ORDER BY a; 126 } altdb 127 } {0 {1 5 10}} 128 129 do_test trans-3.3 { 130 catchsql { 131 SELECT a FROM one ORDER BY a; 132 } altdb 133 } {0 {1 2 3}} 134 do_test trans-3.4 { 135 catchsql { 136 INSERT INTO one VALUES(4,'four'); 137 } 138 } {0 {}} 139 do_test trans-3.5 { 140 catchsql { 141 SELECT a FROM two ORDER BY a; 142 } altdb 143 } {0 {1 5 10}} 144 do_test trans-3.6 { 145 catchsql { 146 SELECT a FROM one ORDER BY a; 147 } altdb 148 } {0 {1 2 3}} 149 do_test trans-3.7 { 150 catchsql { 151 INSERT INTO two VALUES(4,'IV'); 152 } 153 } {0 {}} 154 do_test trans-3.8 { 155 catchsql { 156 SELECT a FROM two ORDER BY a; 157 } altdb 158 } {0 {1 5 10}} 159 do_test trans-3.9 { 160 catchsql { 161 SELECT a FROM one ORDER BY a; 162 } altdb 163 } {0 {1 2 3}} 164 do_test trans-3.10 { 165 execsql {END TRANSACTION} 166 } {} 167 do_test trans-3.10b { 168 sqlite3_txn_state db 169 } {0} 170 171 172 do_test trans-3.11 { 173 set v [catch {execsql { 174 SELECT a FROM two ORDER BY a; 175 } altdb} msg] 176 lappend v $msg 177 } {0 {1 4 5 10}} 178 do_test trans-3.12 { 179 set v [catch {execsql { 180 SELECT a FROM one ORDER BY a; 181 } altdb} msg] 182 lappend v $msg 183 } {0 {1 2 3 4}} 184 do_test trans-3.13 { 185 set v [catch {execsql { 186 SELECT a FROM two ORDER BY a; 187 } db} msg] 188 lappend v $msg 189 } {0 {1 4 5 10}} 190 do_test trans-3.14 { 191 set v [catch {execsql { 192 SELECT a FROM one ORDER BY a; 193 } db} msg] 194 lappend v $msg 195 } {0 {1 2 3 4}} 196 integrity_check trans-3.15 197 wal_check_journal_mode trans-3.16 198 199 do_test trans-4.1 { 200 set v [catch {execsql { 201 COMMIT; 202 } db} msg] 203 lappend v $msg 204 } {1 {cannot commit - no transaction is active}} 205 do_test trans-4.2 { 206 set v [catch {execsql { 207 ROLLBACK; 208 } db} msg] 209 lappend v $msg 210 } {1 {cannot rollback - no transaction is active}} 211 do_test trans-4.3 { 212 catchsql { 213 BEGIN TRANSACTION; 214 UPDATE two SET a = 0 WHERE 0; 215 SELECT a FROM two ORDER BY a; 216 } db 217 } {0 {1 4 5 10}} 218 do_test trans-4.4 { 219 catchsql { 220 SELECT a FROM two ORDER BY a; 221 } altdb 222 } {0 {1 4 5 10}} 223 do_test trans-4.5 { 224 catchsql { 225 SELECT a FROM one ORDER BY a; 226 } altdb 227 } {0 {1 2 3 4}} 228 do_test trans-4.6 { 229 catchsql { 230 BEGIN TRANSACTION; 231 SELECT a FROM one ORDER BY a; 232 } db 233 } {1 {cannot start a transaction within a transaction}} 234 do_test trans-4.7 { 235 catchsql { 236 SELECT a FROM two ORDER BY a; 237 } altdb 238 } {0 {1 4 5 10}} 239 do_test trans-4.8 { 240 catchsql { 241 SELECT a FROM one ORDER BY a; 242 } altdb 243 } {0 {1 2 3 4}} 244 do_test trans-4.9 { 245 set v [catch {execsql { 246 END TRANSACTION; 247 SELECT a FROM two ORDER BY a; 248 } db} msg] 249 lappend v $msg 250 } {0 {1 4 5 10}} 251 do_test trans-4.10 { 252 set v [catch {execsql { 253 SELECT a FROM two ORDER BY a; 254 } altdb} msg] 255 lappend v $msg 256 } {0 {1 4 5 10}} 257 do_test trans-4.11 { 258 set v [catch {execsql { 259 SELECT a FROM one ORDER BY a; 260 } altdb} msg] 261 lappend v $msg 262 } {0 {1 2 3 4}} 263 integrity_check trans-4.12 264 wal_check_journal_mode trans-4.13 265 wal_check_journal_mode trans-4.14 altdb 266 do_test trans-4.98 { 267 altdb close 268 execsql { 269 DROP TABLE one; 270 DROP TABLE two; 271 } 272 } {} 273 integrity_check trans-4.99 274 275 # Check out the commit/rollback behavior of the database 276 # 277 do_test trans-5.1 { 278 execsql {SELECT name FROM sqlite_master WHERE type='table' ORDER BY name} 279 } {} 280 do_test trans-5.2 { 281 execsql {BEGIN TRANSACTION} 282 execsql {SELECT name FROM sqlite_master WHERE type='table' ORDER BY name} 283 } {} 284 do_test trans-5.2b { 285 sqlite3_txn_state db 286 } {1} 287 do_test trans-5.2c { 288 sqlite3_txn_state db main 289 } {1} 290 do_test trans-5.2d { 291 sqlite3_txn_state db temp 292 } {0} 293 do_test trans-5.3 { 294 execsql {CREATE TABLE one(a text, b int)} 295 execsql {SELECT name FROM sqlite_master WHERE type='table' ORDER BY name} 296 } {one} 297 do_test trans-5.4 { 298 execsql {SELECT a,b FROM one ORDER BY b} 299 } {} 300 do_test trans-5.5 { 301 execsql {INSERT INTO one(a,b) VALUES('hello', 1)} 302 execsql {SELECT a,b FROM one ORDER BY b} 303 } {hello 1} 304 do_test trans-5.6 { 305 execsql {ROLLBACK} 306 execsql {SELECT name FROM sqlite_master WHERE type='table' ORDER BY name} 307 } {} 308 do_test trans-5.7 { 309 set v [catch { 310 execsql {SELECT a,b FROM one ORDER BY b} 311 } msg] 312 lappend v $msg 313 } {1 {no such table: one}} 314 315 # Test commits and rollbacks of table CREATE TABLEs, CREATE INDEXs 316 # DROP TABLEs and DROP INDEXs 317 # 318 do_test trans-5.8 { 319 execsql { 320 SELECT name fROM sqlite_master 321 WHERE type='table' OR type='index' 322 ORDER BY name 323 } 324 } {} 325 do_test trans-5.9 { 326 execsql { 327 BEGIN TRANSACTION; 328 CREATE TABLE t1(a int, b int, c int); 329 SELECT name fROM sqlite_master 330 WHERE type='table' OR type='index' 331 ORDER BY name; 332 } 333 } {t1} 334 do_test trans-5.10 { 335 execsql { 336 CREATE INDEX i1 ON t1(a); 337 SELECT name fROM sqlite_master 338 WHERE type='table' OR type='index' 339 ORDER BY name; 340 } 341 } {i1 t1} 342 do_test trans-5.11 { 343 execsql { 344 COMMIT; 345 SELECT name fROM sqlite_master 346 WHERE type='table' OR type='index' 347 ORDER BY name; 348 } 349 } {i1 t1} 350 do_test trans-5.12 { 351 execsql { 352 BEGIN TRANSACTION; 353 CREATE TABLE t2(a int, b int, c int); 354 CREATE INDEX i2a ON t2(a); 355 CREATE INDEX i2b ON t2(b); 356 DROP TABLE t1; 357 SELECT name fROM sqlite_master 358 WHERE type='table' OR type='index' 359 ORDER BY name; 360 } 361 } {i2a i2b t2} 362 do_test trans-5.13 { 363 execsql { 364 ROLLBACK; 365 SELECT name fROM sqlite_master 366 WHERE type='table' OR type='index' 367 ORDER BY name; 368 } 369 } {i1 t1} 370 do_test trans-5.14 { 371 execsql { 372 BEGIN TRANSACTION; 373 DROP INDEX i1; 374 SELECT name fROM sqlite_master 375 WHERE type='table' OR type='index' 376 ORDER BY name; 377 } 378 } {t1} 379 do_test trans-5.15 { 380 execsql { 381 ROLLBACK; 382 SELECT name fROM sqlite_master 383 WHERE type='table' OR type='index' 384 ORDER BY name; 385 } 386 } {i1 t1} 387 do_test trans-5.16 { 388 execsql { 389 BEGIN TRANSACTION; 390 DROP INDEX i1; 391 CREATE TABLE t2(x int, y int, z int); 392 CREATE INDEX i2x ON t2(x); 393 CREATE INDEX i2y ON t2(y); 394 INSERT INTO t2 VALUES(1,2,3); 395 SELECT name fROM sqlite_master 396 WHERE type='table' OR type='index' 397 ORDER BY name; 398 } 399 } {i2x i2y t1 t2} 400 do_test trans-5.17 { 401 execsql { 402 COMMIT; 403 SELECT name fROM sqlite_master 404 WHERE type='table' OR type='index' 405 ORDER BY name; 406 } 407 } {i2x i2y t1 t2} 408 do_test trans-5.18 { 409 execsql { 410 SELECT * FROM t2; 411 } 412 } {1 2 3} 413 do_test trans-5.19 { 414 execsql { 415 SELECT x FROM t2 WHERE y=2; 416 } 417 } {1} 418 do_test trans-5.20 { 419 execsql { 420 BEGIN TRANSACTION; 421 DROP TABLE t1; 422 DROP TABLE t2; 423 SELECT name fROM sqlite_master 424 WHERE type='table' OR type='index' 425 ORDER BY name; 426 } 427 } {} 428 do_test trans-5.21 { 429 set r [catch {execsql { 430 SELECT * FROM t2 431 }} msg] 432 lappend r $msg 433 } {1 {no such table: t2}} 434 do_test trans-5.22 { 435 execsql { 436 ROLLBACK; 437 SELECT name fROM sqlite_master 438 WHERE type='table' OR type='index' 439 ORDER BY name; 440 } 441 } {i2x i2y t1 t2} 442 do_test trans-5.23 { 443 execsql { 444 SELECT * FROM t2; 445 } 446 } {1 2 3} 447 integrity_check trans-5.23 448 449 450 # Try to DROP and CREATE tables and indices with the same name 451 # within a transaction. Make sure ROLLBACK works. 452 # 453 do_test trans-6.1 { 454 execsql2 { 455 INSERT INTO t1 VALUES(1,2,3); 456 BEGIN TRANSACTION; 457 DROP TABLE t1; 458 CREATE TABLE t1(p,q,r); 459 ROLLBACK; 460 SELECT * FROM t1; 461 } 462 } {a 1 b 2 c 3} 463 do_test trans-6.2 { 464 execsql2 { 465 INSERT INTO t1 VALUES(1,2,3); 466 BEGIN TRANSACTION; 467 DROP TABLE t1; 468 CREATE TABLE t1(p,q,r); 469 COMMIT; 470 SELECT * FROM t1; 471 } 472 } {} 473 do_test trans-6.3 { 474 execsql2 { 475 INSERT INTO t1 VALUES(1,2,3); 476 SELECT * FROM t1; 477 } 478 } {p 1 q 2 r 3} 479 do_test trans-6.4 { 480 execsql2 { 481 BEGIN TRANSACTION; 482 DROP TABLE t1; 483 CREATE TABLE t1(a,b,c); 484 INSERT INTO t1 VALUES(4,5,6); 485 SELECT * FROM t1; 486 DROP TABLE t1; 487 } 488 } {a 4 b 5 c 6} 489 do_test trans-6.5 { 490 execsql2 { 491 ROLLBACK; 492 SELECT * FROM t1; 493 } 494 } {p 1 q 2 r 3} 495 do_test trans-6.6 { 496 execsql2 { 497 BEGIN TRANSACTION; 498 DROP TABLE t1; 499 CREATE TABLE t1(a,b,c); 500 INSERT INTO t1 VALUES(4,5,6); 501 SELECT * FROM t1; 502 DROP TABLE t1; 503 } 504 } {a 4 b 5 c 6} 505 do_test trans-6.7 { 506 catchsql { 507 COMMIT; 508 SELECT * FROM t1; 509 } 510 } {1 {no such table: t1}} 511 512 # Repeat on a table with an automatically generated index. 513 # 514 do_test trans-6.10 { 515 execsql2 { 516 CREATE TABLE t1(a unique,b,c); 517 INSERT INTO t1 VALUES(1,2,3); 518 BEGIN TRANSACTION; 519 DROP TABLE t1; 520 CREATE TABLE t1(p unique,q,r); 521 ROLLBACK; 522 SELECT * FROM t1; 523 } 524 } {a 1 b 2 c 3} 525 do_test trans-6.11 { 526 execsql2 { 527 BEGIN TRANSACTION; 528 DROP TABLE t1; 529 CREATE TABLE t1(p unique,q,r); 530 COMMIT; 531 SELECT * FROM t1; 532 } 533 } {} 534 do_test trans-6.12 { 535 execsql2 { 536 INSERT INTO t1 VALUES(1,2,3); 537 SELECT * FROM t1; 538 } 539 } {p 1 q 2 r 3} 540 do_test trans-6.13 { 541 execsql2 { 542 BEGIN TRANSACTION; 543 DROP TABLE t1; 544 CREATE TABLE t1(a unique,b,c); 545 INSERT INTO t1 VALUES(4,5,6); 546 SELECT * FROM t1; 547 DROP TABLE t1; 548 } 549 } {a 4 b 5 c 6} 550 do_test trans-6.14 { 551 execsql2 { 552 ROLLBACK; 553 SELECT * FROM t1; 554 } 555 } {p 1 q 2 r 3} 556 do_test trans-6.15 { 557 execsql2 { 558 BEGIN TRANSACTION; 559 DROP TABLE t1; 560 CREATE TABLE t1(a unique,b,c); 561 INSERT INTO t1 VALUES(4,5,6); 562 SELECT * FROM t1; 563 DROP TABLE t1; 564 } 565 } {a 4 b 5 c 6} 566 do_test trans-6.16 { 567 catchsql { 568 COMMIT; 569 SELECT * FROM t1; 570 } 571 } {1 {no such table: t1}} 572 573 do_test trans-6.20 { 574 execsql { 575 CREATE TABLE t1(a integer primary key,b,c); 576 INSERT INTO t1 VALUES(1,-2,-3); 577 INSERT INTO t1 VALUES(4,-5,-6); 578 SELECT * FROM t1; 579 } 580 } {1 -2 -3 4 -5 -6} 581 do_test trans-6.21 { 582 execsql { 583 CREATE INDEX i1 ON t1(b); 584 SELECT * FROM t1 WHERE b<1; 585 } 586 } {4 -5 -6 1 -2 -3} 587 do_test trans-6.22 { 588 execsql { 589 BEGIN TRANSACTION; 590 DROP INDEX i1; 591 SELECT * FROM t1 WHERE b<1; 592 ROLLBACK; 593 } 594 } {1 -2 -3 4 -5 -6} 595 do_test trans-6.23 { 596 execsql { 597 SELECT * FROM t1 WHERE b<1; 598 } 599 } {4 -5 -6 1 -2 -3} 600 do_test trans-6.24 { 601 execsql { 602 BEGIN TRANSACTION; 603 DROP TABLE t1; 604 ROLLBACK; 605 SELECT * FROM t1 WHERE b<1; 606 } 607 } {4 -5 -6 1 -2 -3} 608 609 do_test trans-6.25 { 610 execsql { 611 BEGIN TRANSACTION; 612 DROP INDEX i1; 613 CREATE INDEX i1 ON t1(c); 614 SELECT * FROM t1 WHERE b<1; 615 } 616 } {1 -2 -3 4 -5 -6} 617 do_test trans-6.26 { 618 execsql { 619 SELECT * FROM t1 WHERE c<1; 620 } 621 } {4 -5 -6 1 -2 -3} 622 do_test trans-6.27 { 623 execsql { 624 ROLLBACK; 625 SELECT * FROM t1 WHERE b<1; 626 } 627 } {4 -5 -6 1 -2 -3} 628 do_test trans-6.28 { 629 execsql { 630 SELECT * FROM t1 WHERE c<1; 631 } 632 } {1 -2 -3 4 -5 -6} 633 634 # The following repeats steps 6.20 through 6.28, but puts a "unique" 635 # constraint the first field of the table in order to generate an 636 # automatic index. 637 # 638 do_test trans-6.30 { 639 execsql { 640 BEGIN TRANSACTION; 641 DROP TABLE t1; 642 CREATE TABLE t1(a int unique,b,c); 643 COMMIT; 644 INSERT INTO t1 VALUES(1,-2,-3); 645 INSERT INTO t1 VALUES(4,-5,-6); 646 SELECT * FROM t1 ORDER BY a; 647 } 648 } {1 -2 -3 4 -5 -6} 649 do_test trans-6.31 { 650 execsql { 651 CREATE INDEX i1 ON t1(b); 652 SELECT * FROM t1 WHERE b<1; 653 } 654 } {4 -5 -6 1 -2 -3} 655 do_test trans-6.32 { 656 execsql { 657 BEGIN TRANSACTION; 658 DROP INDEX i1; 659 SELECT * FROM t1 WHERE b<1; 660 ROLLBACK; 661 } 662 } {1 -2 -3 4 -5 -6} 663 do_test trans-6.33 { 664 execsql { 665 SELECT * FROM t1 WHERE b<1; 666 } 667 } {4 -5 -6 1 -2 -3} 668 do_test trans-6.34 { 669 execsql { 670 BEGIN TRANSACTION; 671 DROP TABLE t1; 672 ROLLBACK; 673 SELECT * FROM t1 WHERE b<1; 674 } 675 } {4 -5 -6 1 -2 -3} 676 677 do_test trans-6.35 { 678 execsql { 679 BEGIN TRANSACTION; 680 DROP INDEX i1; 681 CREATE INDEX i1 ON t1(c); 682 SELECT * FROM t1 WHERE b<1; 683 } 684 } {1 -2 -3 4 -5 -6} 685 do_test trans-6.36 { 686 execsql { 687 SELECT * FROM t1 WHERE c<1; 688 } 689 } {4 -5 -6 1 -2 -3} 690 do_test trans-6.37 { 691 execsql { 692 DROP INDEX i1; 693 SELECT * FROM t1 WHERE c<1; 694 } 695 } {1 -2 -3 4 -5 -6} 696 do_test trans-6.38 { 697 execsql { 698 ROLLBACK; 699 SELECT * FROM t1 WHERE b<1; 700 } 701 } {4 -5 -6 1 -2 -3} 702 do_test trans-6.39 { 703 execsql { 704 SELECT * FROM t1 WHERE c<1; 705 } 706 } {1 -2 -3 4 -5 -6} 707 integrity_check trans-6.40 708 709 # Test to make sure rollback restores the database back to its original 710 # state. 711 # 712 do_test trans-7.1 { 713 execsql {BEGIN} 714 for {set i 0} {$i<1000} {incr i} { 715 set r1 [expr {rand()}] 716 set r2 [expr {rand()}] 717 set r3 [expr {rand()}] 718 execsql "INSERT INTO t2 VALUES($r1,$r2,$r3)" 719 } 720 execsql {COMMIT} 721 set ::checksum [execsql {SELECT md5sum(x,y,z) FROM t2}] 722 set ::checksum2 [ 723 execsql {SELECT md5sum(type,name,tbl_name,rootpage,sql) FROM sqlite_master} 724 ] 725 execsql {SELECT count(*) FROM t2} 726 } {1001} 727 do_test trans-7.2 { 728 execsql {SELECT md5sum(x,y,z) FROM t2} 729 } $checksum 730 do_test trans-7.2.1 { 731 execsql {SELECT md5sum(type,name,tbl_name,rootpage,sql) FROM sqlite_master} 732 } $checksum2 733 do_test trans-7.3 { 734 execsql { 735 BEGIN; 736 DELETE FROM t2; 737 ROLLBACK; 738 SELECT md5sum(x,y,z) FROM t2; 739 } 740 } $checksum 741 do_test trans-7.4 { 742 execsql { 743 BEGIN; 744 INSERT INTO t2 SELECT * FROM t2; 745 ROLLBACK; 746 SELECT md5sum(x,y,z) FROM t2; 747 } 748 } $checksum 749 do_test trans-7.5 { 750 execsql { 751 BEGIN; 752 DELETE FROM t2; 753 ROLLBACK; 754 SELECT md5sum(x,y,z) FROM t2; 755 } 756 } $checksum 757 do_test trans-7.6 { 758 execsql { 759 BEGIN; 760 INSERT INTO t2 SELECT * FROM t2; 761 ROLLBACK; 762 SELECT md5sum(x,y,z) FROM t2; 763 } 764 } $checksum 765 do_test trans-7.7 { 766 execsql { 767 BEGIN; 768 CREATE TABLE t3 AS SELECT * FROM t2; 769 INSERT INTO t2 SELECT * FROM t3; 770 ROLLBACK; 771 SELECT md5sum(x,y,z) FROM t2; 772 } 773 } $checksum 774 do_test trans-7.8 { 775 execsql {SELECT md5sum(type,name,tbl_name,rootpage,sql) FROM sqlite_master} 776 } $checksum2 777 ifcapable tempdb { 778 do_test trans-7.9 { 779 execsql { 780 BEGIN; 781 CREATE TEMP TABLE t3 AS SELECT * FROM t2; 782 INSERT INTO t2 SELECT * FROM t3; 783 ROLLBACK; 784 SELECT md5sum(x,y,z) FROM t2; 785 } 786 } $checksum 787 } 788 do_test trans-7.10 { 789 execsql {SELECT md5sum(type,name,tbl_name,rootpage,sql) FROM sqlite_master} 790 } $checksum2 791 ifcapable tempdb { 792 do_test trans-7.11 { 793 execsql { 794 BEGIN; 795 CREATE TEMP TABLE t3 AS SELECT * FROM t2; 796 INSERT INTO t2 SELECT * FROM t3; 797 DROP INDEX i2x; 798 DROP INDEX i2y; 799 CREATE INDEX i3a ON t3(x); 800 ROLLBACK; 801 SELECT md5sum(x,y,z) FROM t2; 802 } 803 } $checksum 804 } 805 do_test trans-7.12 { 806 execsql {SELECT md5sum(type,name,tbl_name,rootpage,sql) FROM sqlite_master} 807 } $checksum2 808 ifcapable tempdb { 809 do_test trans-7.13 { 810 execsql { 811 BEGIN; 812 DROP TABLE t2; 813 ROLLBACK; 814 SELECT md5sum(x,y,z) FROM t2; 815 } 816 } $checksum 817 } 818 do_test trans-7.14 { 819 execsql {SELECT md5sum(type,name,tbl_name,rootpage,sql) FROM sqlite_master} 820 } $checksum2 821 integrity_check trans-7.15 822 wal_check_journal_mode trans-7.16 823 824 # Arrange for another process to begin modifying the database but abort 825 # and die in the middle of the modification. Then have this process read 826 # the database. This process should detect the journal file and roll it 827 # back. Verify that this happens correctly. 828 # 829 set fd [open test.tcl w] 830 puts $fd { 831 sqlite3_test_control_pending_byte 0x0010000 832 sqlite3 db test.db 833 db eval { 834 PRAGMA default_cache_size=20; 835 BEGIN; 836 CREATE TABLE t3 AS SELECT * FROM t2; 837 DELETE FROM t2; 838 } 839 sqlite_abort 840 } 841 close $fd 842 do_test trans-8.1 { 843 catch {exec [info nameofexec] test.tcl} 844 execsql {SELECT md5sum(x,y,z) FROM t2} 845 } $checksum 846 do_test trans-8.2 { 847 execsql {SELECT md5sum(type,name,tbl_name,rootpage,sql) FROM sqlite_master} 848 } $checksum2 849 integrity_check trans-8.3 850 set fd [open test.tcl w] 851 puts $fd { 852 sqlite3_test_control_pending_byte 0x0010000 853 sqlite3 db test.db 854 db eval { 855 PRAGMA journal_mode=persist; 856 PRAGMA default_cache_size=20; 857 BEGIN; 858 CREATE TABLE t3 AS SELECT * FROM t2; 859 DELETE FROM t2; 860 } 861 sqlite_abort 862 } 863 close $fd 864 do_test trans-8.4 { 865 catch {exec [info nameofexec] test.tcl} 866 execsql {SELECT md5sum(x,y,z) FROM t2} 867 } $checksum 868 do_test trans-8.5 { 869 execsql {SELECT md5sum(type,name,tbl_name,rootpage,sql) FROM sqlite_master} 870 } $checksum2 871 integrity_check trans-8.6 872 wal_check_journal_mode trans-8.7 873 874 # In the following sequence of tests, compute the MD5 sum of the content 875 # of a table, make lots of modifications to that table, then do a rollback. 876 # Verify that after the rollback, the MD5 checksum is unchanged. 877 # 878 do_test trans-9.1 { 879 execsql { 880 PRAGMA default_cache_size=10; 881 } 882 db close 883 sqlite3 db test.db 884 execsql { 885 BEGIN; 886 CREATE TABLE t3(x TEXT); 887 INSERT INTO t3 VALUES(randstr(10,400)); 888 INSERT INTO t3 VALUES(randstr(10,400)); 889 INSERT INTO t3 SELECT randstr(10,400) FROM t3; 890 INSERT INTO t3 SELECT randstr(10,400) FROM t3; 891 INSERT INTO t3 SELECT randstr(10,400) FROM t3; 892 INSERT INTO t3 SELECT randstr(10,400) FROM t3; 893 INSERT INTO t3 SELECT randstr(10,400) FROM t3; 894 INSERT INTO t3 SELECT randstr(10,400) FROM t3; 895 INSERT INTO t3 SELECT randstr(10,400) FROM t3; 896 INSERT INTO t3 SELECT randstr(10,400) FROM t3; 897 INSERT INTO t3 SELECT randstr(10,400) FROM t3; 898 COMMIT; 899 SELECT count(*) FROM t3; 900 } 901 } {1024} 902 wal_check_journal_mode trans-9.1.1 903 904 # The following procedure computes a "signature" for table "t3". If 905 # T3 changes in any way, the signature should change. 906 # 907 # This is used to test ROLLBACK. We gather a signature for t3, then 908 # make lots of changes to t3, then rollback and take another signature. 909 # The two signatures should be the same. 910 # 911 proc signature {} { 912 return [db eval {SELECT count(*), md5sum(x) FROM t3}] 913 } 914 915 # Repeat the following group of tests 20 times for quick testing and 916 # 40 times for full testing. Each iteration of the test makes table 917 # t3 a little larger, and thus takes a little longer, so doing 40 tests 918 # is more than 2.0 times slower than doing 20 tests. Considerably more. 919 # 920 # Also, if temporary tables are stored in memory and the test pcache 921 # is in use, only 20 iterations. Otherwise the test pcache runs out 922 # of page slots and SQLite reports "out of memory". 923 # 924 if {[info exists G(isquick)] || ( 925 $TEMP_STORE==3 && [regexp {^pcache[[:digit:]]*$} [permutation]] 926 ) } { 927 set limit 20 928 } elseif {[info exists G(issoak)]} { 929 set limit 100 930 } else { 931 set limit 40 932 } 933 934 # Do rollbacks. Make sure the signature does not change. 935 # 936 for {set i 2} {$i<=$limit} {incr i} { 937 set ::sig [signature] 938 set cnt [lindex $::sig 0] 939 if {$i%2==0} { 940 execsql {PRAGMA fullfsync=ON} 941 } else { 942 execsql {PRAGMA fullfsync=OFF} 943 } 944 set sqlite_sync_count 0 945 set sqlite_fullsync_count 0 946 do_test trans-9.$i.1-$cnt { 947 execsql { 948 BEGIN; 949 DELETE FROM t3 WHERE random()%10!=0; 950 INSERT INTO t3 SELECT randstr(10,10)||x FROM t3; 951 INSERT INTO t3 SELECT randstr(10,10)||x FROM t3; 952 ROLLBACK; 953 } 954 signature 955 } $sig 956 do_test trans-9.$i.2-$cnt { 957 execsql { 958 BEGIN; 959 DELETE FROM t3 WHERE random()%10!=0; 960 INSERT INTO t3 SELECT randstr(10,10)||x FROM t3; 961 DELETE FROM t3 WHERE random()%10!=0; 962 INSERT INTO t3 SELECT randstr(10,10)||x FROM t3; 963 ROLLBACK; 964 } 965 signature 966 } $sig 967 if {$i<$limit} { 968 do_test trans-9.$i.3-$cnt { 969 execsql { 970 INSERT INTO t3 SELECT randstr(10,400) FROM t3 WHERE random()%10==0; 971 } 972 } {} 973 catch flush_async_queue 974 if {$tcl_platform(platform)=="unix"} { 975 do_test trans-9.$i.4-$cnt { 976 expr {$sqlite_sync_count>0} 977 } 1 978 ifcapable pager_pragmas { 979 do_test trans-9.$i.5-$cnt { 980 expr {$sqlite_fullsync_count>0} 981 } [expr {$i%2==0}] 982 } else { 983 do_test trans-9.$i.5-$cnt { 984 expr {$sqlite_fullsync_count==0} 985 } {1} 986 } 987 } 988 } 989 990 wal_check_journal_mode trans-9.$i.6-$cnt 991 set ::pager_old_format 0 992 } 993 994 finish_test