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