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. The12# focus of this script is in-memory database backend.13#14# $Id: memdb.test,v 1.19 2009/05/18 16:04:38 danielk1977 Exp $15 16 17set testdir [file dirname $argv0]18source $testdir/tester.tcl19 20ifcapable memorydb {21 22# In the following sequence of tests, compute the MD5 sum of the content23# of a table, make lots of modifications to that table, then do a rollback.24# Verify that after the rollback, the MD5 checksum is unchanged.25#26# These tests were browed from trans.tcl.27#28do_test memdb-1.1 {29 db close30 sqlite3 db :memory:31 # sqlite3 db test.db32 execsql {33 BEGIN;34 CREATE TABLE t3(x TEXT);35 INSERT INTO t3 VALUES(randstr(10,400));36 INSERT INTO t3 VALUES(randstr(10,400));37 INSERT INTO t3 SELECT randstr(10,400) FROM t3;38 INSERT INTO t3 SELECT randstr(10,400) FROM t3;39 INSERT INTO t3 SELECT randstr(10,400) FROM t3;40 INSERT INTO t3 SELECT randstr(10,400) FROM t3;41 INSERT INTO t3 SELECT randstr(10,400) FROM t3;42 INSERT INTO t3 SELECT randstr(10,400) FROM t3;43 INSERT INTO t3 SELECT randstr(10,400) FROM t3;44 INSERT INTO t3 SELECT randstr(10,400) FROM t3;45 INSERT INTO t3 SELECT randstr(10,400) FROM t3;46 COMMIT;47 SELECT count(*) FROM t3;48 }49} {1024}50 51# The following procedure computes a "signature" for table "t3". If52# T3 changes in any way, the signature should change. 53#54# This is used to test ROLLBACK. We gather a signature for t3, then55# make lots of changes to t3, then rollback and take another signature.56# The two signatures should be the same.57#58proc signature {{fn {}}} {59 set rx [db eval {SELECT x FROM t3}]60 # set r1 [md5 $rx\n]61 if {$fn!=""} {62 # set fd [open $fn w]63 # puts $fd $rx64 # close $fd65 }66 # set r [db eval {SELECT count(*), md5sum(x) FROM t3}]67 # puts "SIG($fn)=$r1"68 return [list [string length $rx] $rx]69}70 71# Do rollbacks. Make sure the signature does not change.72#73set limit 1074for {set i 2} {$i<=$limit} {incr i} {75 set ::sig [signature one]76 # puts "sig=$sig"77 set cnt [lindex $::sig 0]78 if {$i%2==0} {79 execsql {PRAGMA synchronous=FULL}80 } else {81 execsql {PRAGMA synchronous=NORMAL}82 }83 do_test memdb-1.$i.1-$cnt {84 execsql {85 BEGIN;86 DELETE FROM t3 WHERE random()%10!=0;87 INSERT INTO t3 SELECT randstr(10,10)||x FROM t3;88 INSERT INTO t3 SELECT randstr(10,10)||x FROM t3;89 ROLLBACK;90 }91 set sig2 [signature two]92 } $sig93 # puts "sig2=$sig2"94 # if {$sig2!=$sig} exit95 do_test memdb-1.$i.2-$cnt {96 execsql {97 BEGIN;98 DELETE FROM t3 WHERE random()%10!=0;99 INSERT INTO t3 SELECT randstr(10,10)||x FROM t3;100 DELETE FROM t3 WHERE random()%10!=0;101 INSERT INTO t3 SELECT randstr(10,10)||x FROM t3;102 ROLLBACK;103 }104 signature105 } $sig106 if {$i<$limit} {107 do_test memdb-1.$i.9-$cnt {108 execsql {109 INSERT INTO t3 SELECT randstr(10,400) FROM t3 WHERE random()%10==0;110 }111 } {}112 }113 set ::pager_old_format 0114}115 116integrity_check memdb-2.1117 118do_test memdb-3.1 {119 execsql {120 CREATE TABLE t4(a,b,c,d);121 BEGIN;122 INSERT INTO t4 VALUES(1,2,3,4);123 SELECT * FROM t4;124 }125} {1 2 3 4}126do_test memdb-3.2 {127 execsql {128 SELECT name FROM sqlite_master WHERE type='table';129 }130} {t3 t4}131do_test memdb-3.3 {132 execsql {133 DROP TABLE t4;134 SELECT name FROM sqlite_master WHERE type='table';135 }136} {t3}137do_test memdb-3.4 {138 execsql {139 ROLLBACK;140 SELECT name FROM sqlite_master WHERE type='table';141 }142} {t3 t4}143 144# Create tables for the first group of tests.145#146do_test memdb-4.0 {147 execsql {148 CREATE TABLE t1(a, b, c, UNIQUE(a,b));149 CREATE TABLE t2(x);150 SELECT c FROM t1 ORDER BY c;151 }152} {}153 154# Six columns of configuration data as follows:155#156# i The reference number of the test157# conf The conflict resolution algorithm on the BEGIN statement158# cmd An INSERT or REPLACE command to execute against table t1159# t0 True if there is an error from $cmd160# t1 Content of "c" column of t1 assuming no error in $cmd161# t2 Content of "x" column of t2162#163foreach {i conf cmd t0 t1 t2} {164 1 {} INSERT 1 {} 1165 2 {} {INSERT OR IGNORE} 0 3 1166 3 {} {INSERT OR REPLACE} 0 4 1167 4 {} REPLACE 0 4 1168 5 {} {INSERT OR FAIL} 1 {} 1169 6 {} {INSERT OR ABORT} 1 {} 1170 7 {} {INSERT OR ROLLBACK} 1 {} {}171} {172 173 # All tests after test 1 depend on conflict resolution. So end the174 # loop if that is not available in this build.175 ifcapable !conflict {if {$i>1} break}176 177 do_test memdb-4.$i {178 if {$conf!=""} {set conf "ON CONFLICT $conf"}179 set r0 [catch {execsql [subst {180 DELETE FROM t1;181 DELETE FROM t2;182 INSERT INTO t1 VALUES(1,2,3);183 BEGIN $conf;184 INSERT INTO t2 VALUES(1); 185 $cmd INTO t1 VALUES(1,2,4);186 }]} r1]187 catch {execsql {COMMIT}}188 if {$r0} {set r1 {}} {set r1 [execsql {SELECT c FROM t1}]}189 set r2 [execsql {SELECT x FROM t2}]190 list $r0 $r1 $r2191 } [list $t0 $t1 $t2]192}193 194do_test memdb-5.0 {195 execsql {196 DROP TABLE t2;197 DROP TABLE t3;198 CREATE TABLE t2(a,b,c);199 INSERT INTO t2 VALUES(1,2,1);200 INSERT INTO t2 VALUES(2,3,2);201 INSERT INTO t2 VALUES(3,4,1);202 INSERT INTO t2 VALUES(4,5,4);203 SELECT c FROM t2 ORDER BY b;204 CREATE TABLE t3(x);205 INSERT INTO t3 VALUES(1);206 }207} {1 2 1 4}208 209# Six columns of configuration data as follows:210#211# i The reference number of the test212# conf1 The conflict resolution algorithm on the UNIQUE constraint213# conf2 The conflict resolution algorithm on the BEGIN statement214# cmd An UPDATE command to execute against table t1215# t0 True if there is an error from $cmd216# t1 Content of "b" column of t1 assuming no error in $cmd217# t2 Content of "x" column of t3218#219foreach {i conf1 conf2 cmd t0 t1 t2} {220 1 {} {} UPDATE 1 {6 7 8 9} 1221 2 REPLACE {} UPDATE 0 {7 6 9} 1222 3 IGNORE {} UPDATE 0 {6 7 3 9} 1223 4 FAIL {} UPDATE 1 {6 7 3 4} 1224 5 ABORT {} UPDATE 1 {1 2 3 4} 1225 6 ROLLBACK {} UPDATE 1 {1 2 3 4} 0226 7 REPLACE {} {UPDATE OR IGNORE} 0 {6 7 3 9} 1227 8 IGNORE {} {UPDATE OR REPLACE} 0 {7 6 9} 1228 9 FAIL {} {UPDATE OR IGNORE} 0 {6 7 3 9} 1229 10 ABORT {} {UPDATE OR REPLACE} 0 {7 6 9} 1230 11 ROLLBACK {} {UPDATE OR IGNORE} 0 {6 7 3 9} 1231 12 {} {} {UPDATE OR IGNORE} 0 {6 7 3 9} 1232 13 {} {} {UPDATE OR REPLACE} 0 {7 6 9} 1233 14 {} {} {UPDATE OR FAIL} 1 {6 7 3 4} 1234 15 {} {} {UPDATE OR ABORT} 1 {1 2 3 4} 1235 16 {} {} {UPDATE OR ROLLBACK} 1 {1 2 3 4} 0236} {237 # All tests after test 1 depend on conflict resolution. So end the238 # loop if that is not available in this build.239 ifcapable !conflict {240 if {$i>1} break241 }242 243 if {$t0} {set t1 {UNIQUE constraint failed: t1.a}}244 do_test memdb-5.$i {245 if {$conf1!=""} {set conf1 "ON CONFLICT $conf1"}246 if {$conf2!=""} {set conf2 "ON CONFLICT $conf2"}247 set r0 [catch {execsql "248 DROP TABLE t1;249 CREATE TABLE t1(a,b,c, UNIQUE(a) $conf1);250 INSERT INTO t1 SELECT * FROM t2;251 UPDATE t3 SET x=0;252 BEGIN $conf2;253 $cmd t3 SET x=1;254 $cmd t1 SET b=b*2;255 $cmd t1 SET a=c+5;256 "} r1]257 catch {execsql {COMMIT}}258 if {!$r0} {set r1 [execsql {SELECT a FROM t1 ORDER BY b}]}259 set r2 [execsql {SELECT x FROM t3}]260 list $r0 $r1 $r2261 } [list $t0 $t1 $t2]262}263 264do_test memdb-6.1 {265 execsql {266 SELECT * FROM t2;267 }268} {1 2 1 2 3 2 3 4 1 4 5 4}269do_test memdb-6.2 {270 execsql {271 BEGIN;272 DROP TABLE t2;273 SELECT name FROM sqlite_master WHERE type='table' ORDER BY 1;274 }275} {t1 t3 t4}276do_test memdb-6.3 {277 execsql {278 ROLLBACK;279 SELECT name FROM sqlite_master WHERE type='table' ORDER BY 1;280 }281} {t1 t2 t3 t4}282do_test memdb-6.4 {283 execsql {284 SELECT * FROM t2;285 }286} {1 2 1 2 3 2 3 4 1 4 5 4}287ifcapable compound {288do_test memdb-6.5 {289 execsql {290 SELECT a FROM t2 UNION SELECT b FROM t2 ORDER BY 1;291 }292} {1 2 3 4 5}293} ;# ifcapable compound 294do_test memdb-6.6 {295 execsql {296 CREATE INDEX i2 ON t2(c);297 SELECT a FROM t2 ORDER BY c;298 }299} {1 3 2 4}300do_test memdb-6.6 {301 execsql {302 SELECT a FROM t2 ORDER BY c DESC;303 }304} {4 2 3 1}305do_test memdb-6.7 {306 execsql {307 BEGIN;308 CREATE TABLE t5(x,y);309 INSERT INTO t5 VALUES(1,2);310 SELECT * FROM t5;311 }312} {1 2}313do_test memdb-6.8 {314 execsql {315 SELECT name FROM sqlite_master WHERE type='table' ORDER BY 1;316 }317} {t1 t2 t3 t4 t5}318do_test memdb-6.9 {319 execsql {320 ROLLBACK;321 SELECT name FROM sqlite_master WHERE type='table' ORDER BY 1;322 }323} {t1 t2 t3 t4}324do_test memdb-6.10 {325 execsql {326 CREATE TABLE t5(x PRIMARY KEY, y UNIQUE);327 SELECT * FROM t5;328 }329} {}330do_test memdb-6.11 {331 execsql {332 SELECT * FROM t5 ORDER BY y DESC;333 }334} {}335 336ifcapable conflict {337 do_test memdb-6.12 {338 execsql {339 INSERT INTO t5 VALUES(1,2);340 INSERT INTO t5 VALUES(3,4);341 REPLACE INTO t5 VALUES(1,4);342 SELECT rowid,* FROM t5;343 }344 } {3 1 4}345 do_test memdb-6.13 {346 execsql {347 DELETE FROM t5 WHERE x>5;348 SELECT * FROM t5;349 }350 } {1 4}351 do_test memdb-6.14 {352 execsql {353 DELETE FROM t5 WHERE y<3;354 SELECT * FROM t5;355 }356 } {1 4}357}358 359do_test memdb-6.15 {360 execsql {361 DELETE FROM t5 WHERE x>0;362 SELECT * FROM t5;363 }364} {}365 366ifcapable subquery&&vtab {367 do_test memdb-7.1 {368 load_static_extension db wholenumber369 execsql {370 CREATE TABLE t6(x);371 CREATE VIRTUAL TABLE nums USING wholenumber;372 INSERT INTO t6 SELECT value FROM nums WHERE value BETWEEN 1 AND 256;373 SELECT count(*) FROM (SELECT DISTINCT x FROM t6);374 }375 } {256}376 for {set i 1} {$i<=256} {incr i} {377 do_test memdb-7.2.$i {378 execsql "DELETE FROM t6 WHERE x=\379 (SELECT x FROM t6 ORDER BY random() LIMIT 1)"380 execsql {SELECT count(*) FROM t6}381 } [expr {256-$i}]382 }383}384 385# Ticket #1524386#387do_test memdb-8.1 {388 db close389 sqlite3 db {:memory:}390 execsql {391 PRAGMA auto_vacuum=TRUE;392 CREATE TABLE t1(a);393 INSERT INTO t1 VALUES(randstr(5000,6000));394 INSERT INTO t1 VALUES(randstr(5000,6000));395 INSERT INTO t1 VALUES(randstr(5000,6000));396 INSERT INTO t1 VALUES(randstr(5000,6000));397 INSERT INTO t1 VALUES(randstr(5000,6000));398 SELECT count(*) FROM t1;399 }400} 5401do_test memdb-8.2 {402 execsql {403 DELETE FROM t1;404 SELECT count(*) FROM t1;405 }406} 0407 408# Test that auto-vacuum works with in-memory databases.409# 410ifcapable autovacuum {411 do_test memdb-9.1 {412 db close413 sqlite3 db test.db414 db cache size 0415 execsql {416 PRAGMA auto_vacuum = full;417 CREATE TABLE t1(a);418 INSERT INTO t1 VALUES(randstr(1000,1000));419 INSERT INTO t1 VALUES(randstr(1000,1000));420 INSERT INTO t1 VALUES(randstr(1000,1000));421 }422 set before [db one {PRAGMA page_count}]423 execsql { DELETE FROM t1 }424 set after [db one {PRAGMA page_count}]425 expr {$before>$after}426 } {1}427}428 429} ;# ifcapable memorydb430 431finish_test432