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.9 2004/06/19 00:16:31 drh Exp $ 15 16 17set testdir [file dirname $argv0] 18source $testdir/tester.tcl 19 20# In the following sequence of tests, compute the MD5 sum of the content 21# of a table, make lots of modifications to that table, then do a rollback. 22# Verify that after the rollback, the MD5 checksum is unchanged. 23# 24# These tests were browed from trans.tcl. 25# 26do_test memdb-1.1 { 27 db close 28 sqlite3 db :memory: 29 # sqlite3 db test.db 30 execsql { 31 BEGIN; 32 CREATE TABLE t3(x TEXT); 33 INSERT INTO t3 VALUES(randstr(10,400)); 34 INSERT INTO t3 VALUES(randstr(10,400)); 35 INSERT INTO t3 SELECT randstr(10,400) FROM t3; 36 INSERT INTO t3 SELECT randstr(10,400) FROM t3; 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 COMMIT; 45 SELECT count(*) FROM t3; 46 } 47} {1024} 48 49# The following procedure computes a "signature" for table "t3". If 50# T3 changes in any way, the signature should change. 51# 52# This is used to test ROLLBACK. We gather a signature for t3, then 53# make lots of changes to t3, then rollback and take another signature. 54# The two signatures should be the same. 55# 56proc signature {{fn {}}} { 57 set rx [db eval {SELECT x FROM t3}] 58 # set r1 [md5 $rx\n] 59 if {$fn!=""} { 60 # set fd [open $fn w] 61 # puts $fd $rx 62 # close $fd 63 } 64 # set r [db eval {SELECT count(*), md5sum(x) FROM t3}] 65 # puts "SIG($fn)=$r1" 66 return [list [string length $rx] $rx] 67} 68 69# Do rollbacks. Make sure the signature does not change. 70# 71set limit 10 72for {set i 2} {$i<=$limit} {incr i} { 73 set ::sig [signature one] 74 # puts "sig=$sig" 75 set cnt [lindex $::sig 0] 76 if {$i%2==0} { 77 execsql {PRAGMA synchronous=FULL} 78 } else { 79 execsql {PRAGMA synchronous=NORMAL} 80 } 81 do_test memdb-1.$i.1-$cnt { 82 execsql { 83 BEGIN; 84 DELETE FROM t3 WHERE random()%10!=0; 85 INSERT INTO t3 SELECT randstr(10,10)||x FROM t3; 86 INSERT INTO t3 SELECT randstr(10,10)||x FROM t3; 87 ROLLBACK; 88 } 89 set sig2 [signature two] 90 } $sig 91 # puts "sig2=$sig2" 92 # if {$sig2!=$sig} exit 93 do_test memdb-1.$i.2-$cnt { 94 execsql { 95 BEGIN; 96 DELETE FROM t3 WHERE random()%10!=0; 97 INSERT INTO t3 SELECT randstr(10,10)||x FROM t3; 98 DELETE FROM t3 WHERE random()%10!=0; 99 INSERT INTO t3 SELECT randstr(10,10)||x FROM t3; 100 ROLLBACK; 101 } 102 signature 103 } $sig 104 if {$i<$limit} { 105 do_test memdb-1.$i.9-$cnt { 106 execsql { 107 INSERT INTO t3 SELECT randstr(10,400) FROM t3 WHERE random()%10==0; 108 } 109 } {} 110 } 111 set ::pager_old_format 0 112} 113 114do_test memdb-2.1 { 115 execsql { 116 PRAGMA integrity_check 117 } 118} {ok} 119 120do_test memdb-3.1 { 121 execsql { 122 CREATE TABLE t4(a,b,c,d); 123 BEGIN; 124 INSERT INTO t4 VALUES(1,2,3,4); 125 SELECT * FROM t4; 126 } 127} {1 2 3 4} 128do_test memdb-3.2 { 129 execsql { 130 SELECT name FROM sqlite_master WHERE type='table'; 131 } 132} {t3 t4} 133do_test memdb-3.3 { 134 execsql { 135 DROP TABLE t4; 136 SELECT name FROM sqlite_master WHERE type='table'; 137 } 138} {t3} 139do_test memdb-3.4 { 140 execsql { 141 ROLLBACK; 142 SELECT name FROM sqlite_master WHERE type='table'; 143 } 144} {t3 t4} 145 146# Create tables for the first group of tests. 147# 148do_test memdb-4.0 { 149 execsql { 150 CREATE TABLE t1(a, b, c, UNIQUE(a,b)); 151 CREATE TABLE t2(x); 152 SELECT c FROM t1 ORDER BY c; 153 } 154} {} 155 156# Six columns of configuration data as follows: 157# 158# i The reference number of the test 159# conf The conflict resolution algorithm on the BEGIN statement 160# cmd An INSERT or REPLACE command to execute against table t1 161# t0 True if there is an error from $cmd 162# t1 Content of "c" column of t1 assuming no error in $cmd 163# t2 Content of "x" column of t2 164# 165foreach {i conf cmd t0 t1 t2} { 166 1 {} INSERT 1 {} 1 167 2 {} {INSERT OR IGNORE} 0 3 1 168 3 {} {INSERT OR REPLACE} 0 4 1 169 4 {} REPLACE 0 4 1 170 5 {} {INSERT OR FAIL} 1 {} 1 171 6 {} {INSERT OR ABORT} 1 {} 1 172 7 {} {INSERT OR ROLLBACK} 1 {} {} 173} { 174 do_test memdb-4.$i { 175 if {$conf!=""} {set conf "ON CONFLICT $conf"} 176 set r0 [catch {execsql [subst { 177 DELETE FROM t1; 178 DELETE FROM t2; 179 INSERT INTO t1 VALUES(1,2,3); 180 BEGIN $conf; 181 INSERT INTO t2 VALUES(1); 182 $cmd INTO t1 VALUES(1,2,4); 183 }]} r1] 184 catch {execsql {COMMIT}} 185 if {$r0} {set r1 {}} {set r1 [execsql {SELECT c FROM t1}]} 186 set r2 [execsql {SELECT x FROM t2}] 187 list $r0 $r1 $r2 188 } [list $t0 $t1 $t2] 189} 190 191do_test memdb-5.0 { 192 execsql { 193 DROP TABLE t2; 194 DROP TABLE t3; 195 CREATE TABLE t2(a,b,c); 196 INSERT INTO t2 VALUES(1,2,1); 197 INSERT INTO t2 VALUES(2,3,2); 198 INSERT INTO t2 VALUES(3,4,1); 199 INSERT INTO t2 VALUES(4,5,4); 200 SELECT c FROM t2 ORDER BY b; 201 CREATE TABLE t3(x); 202 INSERT INTO t3 VALUES(1); 203 } 204} {1 2 1 4} 205 206# Six columns of configuration data as follows: 207# 208# i The reference number of the test 209# conf1 The conflict resolution algorithm on the UNIQUE constraint 210# conf2 The conflict resolution algorithm on the BEGIN statement 211# cmd An UPDATE command to execute against table t1 212# t0 True if there is an error from $cmd 213# t1 Content of "b" column of t1 assuming no error in $cmd 214# t2 Content of "x" column of t3 215# 216foreach {i conf1 conf2 cmd t0 t1 t2} { 217 1 {} {} UPDATE 1 {6 7 8 9} 1 218 2 REPLACE {} UPDATE 0 {7 6 9} 1 219 3 IGNORE {} UPDATE 0 {6 7 3 9} 1 220 4 FAIL {} UPDATE 1 {6 7 3 4} 1 221 5 ABORT {} UPDATE 1 {1 2 3 4} 1 222 6 ROLLBACK {} UPDATE 1 {1 2 3 4} 0 223 7 REPLACE {} {UPDATE OR IGNORE} 0 {6 7 3 9} 1 224 8 IGNORE {} {UPDATE OR REPLACE} 0 {7 6 9} 1 225 9 FAIL {} {UPDATE OR IGNORE} 0 {6 7 3 9} 1 226 10 ABORT {} {UPDATE OR REPLACE} 0 {7 6 9} 1 227 11 ROLLBACK {} {UPDATE OR IGNORE} 0 {6 7 3 9} 1 228 12 {} {} {UPDATE OR IGNORE} 0 {6 7 3 9} 1 229 13 {} {} {UPDATE OR REPLACE} 0 {7 6 9} 1 230 14 {} {} {UPDATE OR FAIL} 1 {6 7 3 4} 1 231 15 {} {} {UPDATE OR ABORT} 1 {1 2 3 4} 1 232 16 {} {} {UPDATE OR ROLLBACK} 1 {1 2 3 4} 0 233} { 234 if {$t0} {set t1 {column a is not unique}} 235 do_test memdb-5.$i { 236 if {$conf1!=""} {set conf1 "ON CONFLICT $conf1"} 237 if {$conf2!=""} {set conf2 "ON CONFLICT $conf2"} 238 set r0 [catch {execsql [subst { 239 DROP TABLE t1; 240 CREATE TABLE t1(a,b,c, UNIQUE(a) $conf1); 241 INSERT INTO t1 SELECT * FROM t2; 242 UPDATE t3 SET x=0; 243 BEGIN $conf2; 244 $cmd t3 SET x=1; 245 $cmd t1 SET b=b*2; 246 $cmd t1 SET a=c+5; 247 }]} r1] 248 catch {execsql {COMMIT}} 249 if {!$r0} {set r1 [execsql {SELECT a FROM t1 ORDER BY b}]} 250 set r2 [execsql {SELECT x FROM t3}] 251 list $r0 $r1 $r2 252 } [list $t0 $t1 $t2] 253} 254 255do_test memdb-6.1 { 256 execsql { 257 SELECT * FROM t2; 258 } 259} {1 2 1 2 3 2 3 4 1 4 5 4} 260do_test memdb-6.2 { 261 execsql { 262 BEGIN; 263 DROP TABLE t2; 264 SELECT name FROM sqlite_master WHERE type='table' ORDER BY 1; 265 } 266} {t1 t3 t4} 267do_test memdb-6.3 { 268 execsql { 269 ROLLBACK; 270 SELECT name FROM sqlite_master WHERE type='table' ORDER BY 1; 271 } 272} {t1 t2 t3 t4} 273do_test memdb-6.4 { 274 execsql { 275 SELECT * FROM t2; 276 } 277} {1 2 1 2 3 2 3 4 1 4 5 4} 278do_test memdb-6.5 { 279 execsql { 280 SELECT a FROM t2 UNION SELECT b FROM t2 ORDER BY 1; 281 } 282} {1 2 3 4 5} 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 351do_test memdb-7.1 { 352 execsql { 353 CREATE TABLE t6(x); 354 INSERT INTO t6 VALUES(1); 355 INSERT INTO t6 SELECT x+1 FROM t6; 356 INSERT INTO t6 SELECT x+2 FROM t6; 357 INSERT INTO t6 SELECT x+4 FROM t6; 358 INSERT INTO t6 SELECT x+8 FROM t6; 359 INSERT INTO t6 SELECT x+16 FROM t6; 360 INSERT INTO t6 SELECT x+32 FROM t6; 361 INSERT INTO t6 SELECT x+64 FROM t6; 362 INSERT INTO t6 SELECT x+128 FROM t6; 363 SELECT count(*) FROM (SELECT DISTINCT x FROM t6); 364 } 365} {256} 366for {set i 1} {$i<=256} {incr i} { 367 do_test memdb-7.2.$i { 368 execsql "DELETE FROM t6 WHERE x=\ 369 (SELECT x FROM t6 ORDER BY random() LIMIT 1)" 370 execsql {SELECT count(*) FROM t6} 371 } [expr {256-$i}] 372} 373 374finish_test 375