AryaWu/sqlite
0
1# 2001 September 152#3# The author disclaims copyright to this source code. In place of4# 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. This12# 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 18set testdir [file dirname $argv0]19source $testdir/tester.tcl20 21 22# Create several tables to work with.23#24do_test avtrans-1.0 {25 execsql { PRAGMA auto_vacuum=full }26 wal_set_journal_mode27 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}35do_test avtrans-1.0.1 { execsql { PRAGMA auto_vacuum } } 136do_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}45do_test avtrans-1.9 {46 sqlite3 altdb test.db47 execsql {SELECT b FROM one ORDER BY a} altdb48} {one two three}49do_test avtrans-1.10 {50 execsql {SELECT b FROM two ORDER BY a} altdb51} {I V X}52integrity_check avtrans-1.1153wal_check_journal_mode avtrans-1.1254 55# Basic transactions56#57do_test avtrans-2.1 {58 set v [catch {execsql {BEGIN}} msg]59 lappend v $msg60} {0 {}}61do_test avtrans-2.2 {62 set v [catch {execsql {END}} msg]63 lappend v $msg64} {0 {}}65do_test avtrans-2.3 {66 set v [catch {execsql {BEGIN TRANSACTION}} msg]67 lappend v $msg68} {0 {}}69do_test avtrans-2.4 {70 set v [catch {execsql {COMMIT TRANSACTION}} msg]71 lappend v $msg72} {0 {}}73do_test avtrans-2.5 {74 set v [catch {execsql {BEGIN TRANSACTION 'foo'}} msg]75 lappend v $msg76} {0 {}}77do_test avtrans-2.6 {78 set v [catch {execsql {ROLLBACK TRANSACTION 'foo'}} msg]79 lappend v $msg80} {0 {}}81do_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}89integrity_check avtrans-2.1190wal_check_journal_mode avtrans-2.1291 92# Check the locking behavior93#94sqlite3_soft_heap_limit 095do_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}102do_test avtrans-3.2 {103 catchsql {104 SELECT a FROM two ORDER BY a;105 } altdb106} {0 {1 5 10}}107do_test avtrans-3.3 {108 catchsql {109 SELECT a FROM one ORDER BY a;110 } altdb111} {0 {1 2 3}}112do_test avtrans-3.4 {113 catchsql {114 INSERT INTO one VALUES(4,'four');115 }116} {0 {}}117do_test avtrans-3.5 {118 catchsql {119 SELECT a FROM two ORDER BY a;120 } altdb121} {0 {1 5 10}}122do_test avtrans-3.6 {123 catchsql {124 SELECT a FROM one ORDER BY a;125 } altdb126} {0 {1 2 3}}127do_test avtrans-3.7 {128 catchsql {129 INSERT INTO two VALUES(4,'IV');130 }131} {0 {}}132do_test avtrans-3.8 {133 catchsql {134 SELECT a FROM two ORDER BY a;135 } altdb136} {0 {1 5 10}}137do_test avtrans-3.9 {138 catchsql {139 SELECT a FROM one ORDER BY a;140 } altdb141} {0 {1 2 3}}142do_test avtrans-3.10 {143 execsql {END TRANSACTION}144} {}145do_test avtrans-3.11 {146 set v [catch {execsql {147 SELECT a FROM two ORDER BY a;148 } altdb} msg]149 lappend v $msg150} {0 {1 4 5 10}}151do_test avtrans-3.12 {152 set v [catch {execsql {153 SELECT a FROM one ORDER BY a;154 } altdb} msg]155 lappend v $msg156} {0 {1 2 3 4}}157do_test avtrans-3.13 {158 set v [catch {execsql {159 SELECT a FROM two ORDER BY a;160 } db} msg]161 lappend v $msg162} {0 {1 4 5 10}}163do_test avtrans-3.14 {164 set v [catch {execsql {165 SELECT a FROM one ORDER BY a;166 } db} msg]167 lappend v $msg168} {0 {1 2 3 4}}169sqlite3_soft_heap_limit $cmdlinearg(soft-heap-limit)170integrity_check avtrans-3.15171 172do_test avtrans-4.1 {173 set v [catch {execsql {174 COMMIT;175 } db} msg]176 lappend v $msg177} {1 {cannot commit - no transaction is active}}178do_test avtrans-4.2 {179 set v [catch {execsql {180 ROLLBACK;181 } db} msg]182 lappend v $msg183} {1 {cannot rollback - no transaction is active}}184do_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 } db190} {0 {1 4 5 10}}191do_test avtrans-4.4 {192 catchsql {193 SELECT a FROM two ORDER BY a;194 } altdb195} {0 {1 4 5 10}}196do_test avtrans-4.5 {197 catchsql {198 SELECT a FROM one ORDER BY a;199 } altdb200} {0 {1 2 3 4}}201do_test avtrans-4.6 {202 catchsql {203 BEGIN TRANSACTION;204 SELECT a FROM one ORDER BY a;205 } db206} {1 {cannot start a transaction within a transaction}}207do_test avtrans-4.7 {208 catchsql {209 SELECT a FROM two ORDER BY a;210 } altdb211} {0 {1 4 5 10}}212do_test avtrans-4.8 {213 catchsql {214 SELECT a FROM one ORDER BY a;215 } altdb216} {0 {1 2 3 4}}217do_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 $msg223} {0 {1 4 5 10}}224do_test avtrans-4.10 {225 set v [catch {execsql {226 SELECT a FROM two ORDER BY a;227 } altdb} msg]228 lappend v $msg229} {0 {1 4 5 10}}230do_test avtrans-4.11 {231 set v [catch {execsql {232 SELECT a FROM one ORDER BY a;233 } altdb} msg]234 lappend v $msg235} {0 {1 2 3 4}}236integrity_check avtrans-4.12237do_test avtrans-4.98 {238 altdb close239 execsql {240 DROP TABLE one;241 DROP TABLE two;242 }243} {}244integrity_check avtrans-4.99245 246# Check out the commit/rollback behavior of the database247#248do_test avtrans-5.1 {249 execsql {SELECT name FROM sqlite_master WHERE type='table' ORDER BY name}250} {}251do_test avtrans-5.2 {252 execsql {BEGIN TRANSACTION}253 execsql {SELECT name FROM sqlite_master WHERE type='table' ORDER BY name}254} {}255do_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}259do_test avtrans-5.4 {260 execsql {SELECT a,b FROM one ORDER BY b}261} {}262do_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}266do_test avtrans-5.6 {267 execsql {ROLLBACK}268 execsql {SELECT name FROM sqlite_master WHERE type='table' ORDER BY name}269} {}270do_test avtrans-5.7 {271 set v [catch {272 execsql {SELECT a,b FROM one ORDER BY b}273 } msg]274 lappend v $msg275} {1 {no such table: one}}276 277# Test commits and rollbacks of table CREATE TABLEs, CREATE INDEXs278# DROP TABLEs and DROP INDEXs279#280do_test avtrans-5.8 {281 execsql {282 SELECT name fROM sqlite_master 283 WHERE type='table' OR type='index'284 ORDER BY name285 }286} {}287do_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}296do_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}304do_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}312do_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}324do_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}332do_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}341do_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}349do_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}362do_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}370do_test avtrans-5.18 {371 execsql {372 SELECT * FROM t2;373 }374} {1 2 3}375do_test avtrans-5.19 {376 execsql {377 SELECT x FROM t2 WHERE y=2;378 }379} {1}380do_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} {}390do_test avtrans-5.21 {391 set r [catch {execsql {392 SELECT * FROM t2393 }} msg]394 lappend r $msg395} {1 {no such table: t2}}396do_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}404do_test avtrans-5.23 {405 execsql {406 SELECT * FROM t2;407 }408} {1 2 3}409integrity_check avtrans-5.23410 411 412# Try to DROP and CREATE tables and indices with the same name413# within a transaction. Make sure ROLLBACK works.414#415do_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}425do_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} {}435do_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}441do_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}451do_test avtrans-6.5 {452 execsql2 {453 ROLLBACK;454 SELECT * FROM t1;455 }456} {p 1 q 2 r 3}457do_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}467do_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#476do_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}487do_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} {}496do_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}502do_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}512do_test avtrans-6.14 {513 execsql2 {514 ROLLBACK;515 SELECT * FROM t1;516 }517} {p 1 q 2 r 3}518do_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}528do_test avtrans-6.16 {529 catchsql {530 COMMIT;531 SELECT * FROM t1;532 }533} {1 {no such table: t1}}534 535do_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}543do_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}549do_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}557do_test avtrans-6.23 {558 execsql {559 SELECT * FROM t1 WHERE b<1;560 }561} {4 -5 -6 1 -2 -3}562do_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 571do_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}579do_test avtrans-6.26 {580 execsql {581 SELECT * FROM t1 WHERE c<1;582 }583} {4 -5 -6 1 -2 -3}584do_test avtrans-6.27 {585 execsql {586 ROLLBACK;587 SELECT * FROM t1 WHERE b<1;588 }589} {4 -5 -6 1 -2 -3}590do_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 an598# automatic index.599#600do_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}611do_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}617do_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}625do_test avtrans-6.33 {626 execsql {627 SELECT * FROM t1 WHERE b<1;628 }629} {4 -5 -6 1 -2 -3}630do_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 639do_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}647do_test avtrans-6.36 {648 execsql {649 SELECT * FROM t1 WHERE c<1;650 }651} {4 -5 -6 1 -2 -3}652do_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}658do_test avtrans-6.38 {659 execsql {660 ROLLBACK;661 SELECT * FROM t1 WHERE b<1;662 }663} {4 -5 -6 1 -2 -3}664do_test avtrans-6.39 {665 execsql {666 SELECT * FROM t1 WHERE c<1;667 }668} {1 -2 -3 4 -5 -6}669integrity_check avtrans-6.40670 671ifcapable !floatingpoint {672 finish_test673 return674}675 676# Test to make sure rollback restores the database back to its original677# state.678#679do_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}694do_test avtrans-7.2 {695 execsql {SELECT md5sum(x,y,z) FROM t2}696} $checksum697do_test avtrans-7.2.1 {698 execsql {SELECT md5sum(type,name,tbl_name,rootpage,sql) FROM sqlite_master}699} $checksum2700do_test avtrans-7.3 {701 execsql {702 BEGIN;703 DELETE FROM t2;704 ROLLBACK;705 SELECT md5sum(x,y,z) FROM t2;706 }707} $checksum708do_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} $checksum716do_test avtrans-7.5 {717 execsql {718 BEGIN;719 DELETE FROM t2;720 ROLLBACK;721 SELECT md5sum(x,y,z) FROM t2;722 }723} $checksum724do_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} $checksum732do_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} $checksum741do_test avtrans-7.8 {742 execsql {SELECT md5sum(type,name,tbl_name,rootpage,sql) FROM sqlite_master}743} $checksum2744ifcapable 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 } $checksum754}755do_test avtrans-7.10 {756 execsql {SELECT md5sum(type,name,tbl_name,rootpage,sql) FROM sqlite_master}757} $checksum2758ifcapable 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 } $checksum771}772do_test avtrans-7.12 {773 execsql {SELECT md5sum(type,name,tbl_name,rootpage,sql) FROM sqlite_master}774} $checksum2775ifcapable 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 } $checksum784}785do_test avtrans-7.14 {786 execsql {SELECT md5sum(type,name,tbl_name,rootpage,sql) FROM sqlite_master}787} $checksum2788integrity_check avtrans-7.15789 790# Arrange for another process to begin modifying the database but abort791# and die in the middle of the modification. Then have this process read792# the database. This process should detect the journal file and roll it793# back. Verify that this happens correctly.794#795set fd [open test.tcl w]796puts $fd {797 sqlite3 db test.db798 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_abort805}806close $fd807do_test avtrans-8.1 {808 catch {exec [info nameofexec] test.tcl}809 execsql {SELECT md5sum(x,y,z) FROM t2}810} $checksum811do_test avtrans-8.2 {812 execsql {SELECT md5sum(type,name,tbl_name,rootpage,sql) FROM sqlite_master}813} $checksum2814integrity_check avtrans-8.3815 816# In the following sequence of tests, compute the MD5 sum of the content817# 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#820do_test avtrans-9.1 {821 execsql {822 PRAGMA default_cache_size=10;823 }824 db close825 sqlite3 db test.db826 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". If846# T3 changes in any way, the signature should change. 847#848# This is used to test ROLLBACK. We gather a signature for t3, then849# make lots of changes to t3, then rollback and take another signature.850# The two signatures should be the same.851#852proc 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 and857# 40 times for full testing. Each iteration of the test makes table858# t3 a little larger, and thus takes a little longer, so doing 40 tests859# is more than 2.0 times slower than doing 20 tests. Considerably more.860#861if {[info exists G(isquick)]} {862 set limit 20863} else {864 set limit 40865}866 867# Do rollbacks. Make sure the signature does not change.868#869for {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 0878 set sqlite_fullsync_count 0879 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 signature888 } $sig889 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 signature899 } $sig900 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) eq "unix"} {907 do_test avtrans-9.$i.4-$cnt {908 expr {$sqlite_sync_count>0}909 } 1910 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.$cnt921 }922 set ::pager_old_format 0923}924integrity_check avtrans-10.1925wal_check_journal_mode avtrans-10.2926 927finish_test928