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 in-memory database backend. 13# 14# $Id: memdb.test,v 1.13 2005/01/21 04:25:47 danielk1977 Exp $ 15 16 17set testdir [file dirname $argv0] 18source $testdir/tester.tcl 19 20ifcapable memorydb { 21 22# In the following sequence of tests, compute the MD5 sum of the content 23# 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 close 30 sqlite3 db :memory: 31 # sqlite3 db test.db 32 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". If 52# T3 changes in any way, the signature should change. 53# 54# This is used to test ROLLBACK. We gather a signature for t3, then 55# 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 $rx 64 # close $fd 65 } 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 10 74for {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 } $sig 93 # puts "sig2=$sig2" 94 # if {$sig2!=$sig} exit 95 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 signature 105 } $sig 106 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 0 114} 115 116integrity_check memdb-2.1 117 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 test 157# conf The conflict resolution algorithm on the BEGIN statement 158# cmd An INSERT or REPLACE command to execute against table t1 159# t0 True if there is an error from $cmd 160# t1 Content of "c" column of t1 assuming no error in $cmd 161# t2 Content of "x" column of t2 162# 163foreach {i conf cmd t0 t1 t2} { 164 1 {} INSERT 1 {} 1 165 2 {} {INSERT OR IGNORE} 0 3 1 166 3 {} {INSERT OR REPLACE} 0 4 1 167 4 {} REPLACE 0 4 1 168 5 {} {INSERT OR FAIL} 1 {} 1 169 6 {} {INSERT OR ABORT} 1 {} 1 170 7 {} {INSERT OR ROLLBACK} 1 {} {} 171} { 172 do_test memdb-4.$i { 173 if {$conf!=""} {set conf "ON CONFLICT $conf"} 174 set r0 [catch {execsql [subst { 175 DELETE FROM t1; 176 DELETE FROM t2; 177 INSERT INTO t1 VALUES(1,2,3); 178 BEGIN $conf; 179 INSERT INTO t2 VALUES(1); 180 $cmd INTO t1 VALUES(1,2,4); 181 }]} r1] 182 catch {execsql {COMMIT}} 183 if {$r0} {set r1 {}} {set r1 [execsql {SELECT c FROM t1}]} 184 set r2 [execsql {SELECT x FROM t2}] 185 list $r0 $r1 $r2 186 } [list $t0 $t1 $t2] 187} 188 189do_test memdb-5.0 { 190 execsql { 191 DROP TABLE t2; 192 DROP TABLE t3; 193 CREATE TABLE t2(a,b,c); 194 INSERT INTO t2 VALUES(1,2,1); 195 INSERT INTO t2 VALUES(2,3,2); 196 INSERT INTO t2 VALUES(3,4,1); 197 INSERT INTO t2 VALUES(4,5,4); 198 SELECT c FROM t2 ORDER BY b; 199 CREATE TABLE t3(x); 200 INSERT INTO t3 VALUES(1); 201 } 202} {1 2 1 4} 203 204# Six columns of configuration data as follows: 205# 206# i The reference number of the test 207# conf1 The conflict resolution algorithm on the UNIQUE constraint 208# conf2 The conflict resolution algorithm on the BEGIN statement 209# cmd An UPDATE command to execute against table t1 210# t0 True if there is an error from $cmd 211# t1 Content of "b" column of t1 assuming no error in $cmd 212# t2 Content of "x" column of t3 213# 214foreach {i conf1 conf2 cmd t0 t1 t2} { 215 1 {} {} UPDATE 1 {6 7 8 9} 1 216 2 REPLACE {} UPDATE 0 {7 6 9} 1 217 3 IGNORE {} UPDATE 0 {6 7 3 9} 1 218 4 FAIL {} UPDATE 1 {6 7 3 4} 1 219 5 ABORT {} UPDATE 1 {1 2 3 4} 1 220 6 ROLLBACK {} UPDATE 1 {1 2 3 4} 0 221 7 REPLACE {} {UPDATE OR IGNORE} 0 {6 7 3 9} 1 222 8 IGNORE {} {UPDATE OR REPLACE} 0 {7 6 9} 1 223 9 FAIL {} {UPDATE OR IGNORE} 0 {6 7 3 9} 1 224 10 ABORT {} {UPDATE OR REPLACE} 0 {7 6 9} 1 225 11 ROLLBACK {} {UPDATE OR IGNORE} 0 {6 7 3 9} 1 226 12 {} {} {UPDATE OR IGNORE} 0 {6 7 3 9} 1 227 13 {} {} {UPDATE OR REPLACE} 0 {7 6 9} 1 228 14 {} {} {UPDATE OR FAIL} 1 {6 7 3 4} 1 229 15 {} {} {UPDATE OR ABORT} 1 {1 2 3 4} 1 230 16 {} {} {UPDATE OR ROLLBACK} 1 {1 2 3 4} 0 231} { 232 if {$t0} {set t1 {column a is not unique}} 233 do_test memdb-5.$i { 234 if {$conf1!=""} {set conf1 "ON CONFLICT $conf1"} 235 if {$conf2!=""} {set conf2 "ON CONFLICT $conf2"} 236 set r0 [catch {execsql [subst { 237 DROP TABLE t1; 238 CREATE TABLE t1(a,b,c, UNIQUE(a) $conf1); 239 INSERT INTO t1 SELECT * FROM t2; 240 UPDATE t3 SET x=0; 241 BEGIN $conf2; 242 $cmd t3 SET x=1; 243 $cmd t1 SET b=b*2; 244 $cmd t1 SET a=c+5; 245 }]} r1] 246 catch {execsql {COMMIT}} 247 if {!$r0} {set r1 [execsql {SELECT a FROM t1 ORDER BY b}]} 248 set r2 [execsql {SELECT x FROM t3}] 249 list $r0 $r1 $r2 250 } [list $t0 $t1 $t2] 251} 252 253do_test memdb-6.1 { 254 execsql { 255 SELECT * FROM t2; 256 } 257} {1 2 1 2 3 2 3 4 1 4 5 4} 258do_test memdb-6.2 { 259 execsql { 260 BEGIN; 261 DROP TABLE t2; 262 SELECT name FROM sqlite_master WHERE type='table' ORDER BY 1; 263 } 264} {t1 t3 t4} 265do_test memdb-6.3 { 266 execsql { 267 ROLLBACK; 268 SELECT name FROM sqlite_master WHERE type='table' ORDER BY 1; 269 } 270} {t1 t2 t3 t4} 271do_test memdb-6.4 { 272 execsql { 273 SELECT * FROM t2; 274 } 275} {1 2 1 2 3 2 3 4 1 4 5 4} 276ifcapable compound { 277do_test memdb-6.5 { 278 execsql { 279 SELECT a FROM t2 UNION SELECT b FROM t2 ORDER BY 1; 280 } 281} {1 2 3 4 5} 282} ;# ifcapable compound 283do_test memdb-6.6 { 284 execsql { 285 CREATE INDEX i2 ON t2(c); 286 SELECT a FROM t2 ORDER BY c; 287 } 288} {1 3 2 4} 289do_test memdb-6.6 { 290 execsql { 291 SELECT a FROM t2 ORDER BY c DESC; 292 } 293} {4 2 3 1} 294do_test memdb-6.7 { 295 execsql { 296 BEGIN; 297 CREATE TABLE t5(x,y); 298 INSERT INTO t5 VALUES(1,2); 299 SELECT * FROM t5; 300 } 301} {1 2} 302do_test memdb-6.8 { 303 execsql { 304 SELECT name FROM sqlite_master WHERE type='table' ORDER BY 1; 305 } 306} {t1 t2 t3 t4 t5} 307do_test memdb-6.9 { 308 execsql { 309 ROLLBACK; 310 SELECT name FROM sqlite_master WHERE type='table' ORDER BY 1; 311 } 312} {t1 t2 t3 t4} 313do_test memdb-6.10 { 314 execsql { 315 CREATE TABLE t5(x PRIMARY KEY, y UNIQUE); 316 SELECT * FROM t5; 317 } 318} {} 319do_test memdb-6.11 { 320 execsql { 321 SELECT * FROM t5 ORDER BY y DESC; 322 } 323} {} 324do_test memdb-6.12 { 325 execsql { 326 INSERT INTO t5 VALUES(1,2); 327 INSERT INTO t5 VALUES(3,4); 328 REPLACE INTO t5 VALUES(1,4); 329 SELECT rowid,* FROM t5; 330 } 331} {3 1 4} 332do_test memdb-6.13 { 333 execsql { 334 DELETE FROM t5 WHERE x>5; 335 SELECT * FROM t5; 336 } 337} {1 4} 338do_test memdb-6.14 { 339 execsql { 340 DELETE FROM t5 WHERE y<3; 341 SELECT * FROM t5; 342 } 343} {1 4} 344do_test memdb-6.15 { 345 execsql { 346 DELETE FROM t5 WHERE x>0; 347 SELECT * FROM t5; 348 } 349} {} 350 351ifcapable subquery { 352 do_test memdb-7.1 { 353 execsql { 354 CREATE TABLE t6(x); 355 INSERT INTO t6 VALUES(1); 356 INSERT INTO t6 SELECT x+1 FROM t6; 357 INSERT INTO t6 SELECT x+2 FROM t6; 358 INSERT INTO t6 SELECT x+4 FROM t6; 359 INSERT INTO t6 SELECT x+8 FROM t6; 360 INSERT INTO t6 SELECT x+16 FROM t6; 361 INSERT INTO t6 SELECT x+32 FROM t6; 362 INSERT INTO t6 SELECT x+64 FROM t6; 363 INSERT INTO t6 SELECT x+128 FROM t6; 364 SELECT count(*) FROM (SELECT DISTINCT x FROM t6); 365 } 366 } {256} 367 for {set i 1} {$i<=256} {incr i} { 368 do_test memdb-7.2.$i { 369 execsql "DELETE FROM t6 WHERE x=\ 370 (SELECT x FROM t6 ORDER BY random() LIMIT 1)" 371 execsql {SELECT count(*) FROM t6} 372 } [expr {256-$i}] 373 } 374} 375 376} ;# ifcapable memorydb 377 378finish_test 379