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.7 2004/06/09 19:03:55 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 sqlite db :memory: 29 # sqlite 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 8 IGNORE INSERT 0 3 1 174 9 IGNORE {INSERT OR IGNORE} 0 3 1 175 10 IGNORE {INSERT OR REPLACE} 0 4 1 176 11 IGNORE REPLACE 0 4 1 177 12 IGNORE {INSERT OR FAIL} 1 {} 1 178 13 IGNORE {INSERT OR ABORT} 1 {} 1 179 14 IGNORE {INSERT OR ROLLBACK} 1 {} {} 180 15 REPLACE INSERT 0 4 1 181 16 FAIL INSERT 1 {} 1 182 17 ABORT INSERT 1 {} 1 183 18 ROLLBACK INSERT 1 {} {} 184} { 185 do_test memdb-4.$i { 186 if {$conf!=""} {set conf "ON CONFLICT $conf"} 187 set r0 [catch {execsql [subst { 188 DELETE FROM t1; 189 DELETE FROM t2; 190 INSERT INTO t1 VALUES(1,2,3); 191 BEGIN $conf; 192 INSERT INTO t2 VALUES(1); 193 $cmd INTO t1 VALUES(1,2,4); 194 }]} r1] 195 catch {execsql {COMMIT}} 196 if {$r0} {set r1 {}} {set r1 [execsql {SELECT c FROM t1}]} 197 set r2 [execsql {SELECT x FROM t2}] 198 list $r0 $r1 $r2 199 } [list $t0 $t1 $t2] 200} 201 202do_test memdb-5.0 { 203 execsql { 204 DROP TABLE t2; 205 DROP TABLE t3; 206 CREATE TABLE t2(a,b,c); 207 INSERT INTO t2 VALUES(1,2,1); 208 INSERT INTO t2 VALUES(2,3,2); 209 INSERT INTO t2 VALUES(3,4,1); 210 INSERT INTO t2 VALUES(4,5,4); 211 SELECT c FROM t2 ORDER BY b; 212 CREATE TABLE t3(x); 213 INSERT INTO t3 VALUES(1); 214 } 215} {1 2 1 4} 216 217# Six columns of configuration data as follows: 218# 219# i The reference number of the test 220# conf1 The conflict resolution algorithm on the UNIQUE constraint 221# conf2 The conflict resolution algorithm on the BEGIN statement 222# cmd An UPDATE command to execute against table t1 223# t0 True if there is an error from $cmd 224# t1 Content of "b" column of t1 assuming no error in $cmd 225# t2 Content of "x" column of t3 226# 227foreach {i conf1 conf2 cmd t0 t1 t2} { 228 1 {} {} UPDATE 1 {6 7 8 9} 1 229 2 REPLACE {} UPDATE 0 {7 6 9} 1 230 3 IGNORE {} UPDATE 0 {6 7 3 9} 1 231 4 FAIL {} UPDATE 1 {6 7 3 4} 1 232 5 ABORT {} UPDATE 1 {1 2 3 4} 1 233 6 ROLLBACK {} UPDATE 1 {1 2 3 4} 0 234 7 REPLACE {} {UPDATE OR IGNORE} 0 {6 7 3 9} 1 235 8 IGNORE {} {UPDATE OR REPLACE} 0 {7 6 9} 1 236 9 FAIL {} {UPDATE OR IGNORE} 0 {6 7 3 9} 1 237 10 ABORT {} {UPDATE OR REPLACE} 0 {7 6 9} 1 238 11 ROLLBACK {} {UPDATE OR IGNORE} 0 {6 7 3 9} 1 239 12 {} {} {UPDATE OR IGNORE} 0 {6 7 3 9} 1 240 13 {} {} {UPDATE OR REPLACE} 0 {7 6 9} 1 241 14 {} {} {UPDATE OR FAIL} 1 {6 7 3 4} 1 242 15 {} {} {UPDATE OR ABORT} 1 {1 2 3 4} 1 243 16 {} {} {UPDATE OR ROLLBACK} 1 {1 2 3 4} 0 244 17 {} IGNORE UPDATE 0 {6 7 3 9} 1 245 18 {} REPLACE UPDATE 0 {7 6 9} 1 246 19 {} FAIL UPDATE 1 {6 7 3 4} 1 247 20 {} ABORT UPDATE 1 {1 2 3 4} 1 248 21 {} ROLLBACK UPDATE 1 {1 2 3 4} 0 249 22 REPLACE IGNORE UPDATE 0 {6 7 3 9} 1 250 23 IGNORE REPLACE UPDATE 0 {7 6 9} 1 251 24 REPLACE FAIL UPDATE 1 {6 7 3 4} 1 252 25 IGNORE ABORT UPDATE 1 {1 2 3 4} 1 253 26 REPLACE ROLLBACK UPDATE 1 {1 2 3 4} 0 254} { 255 if {$t0} {set t1 {column a is not unique}} 256 do_test memdb-5.$i { 257 if {$conf1!=""} {set conf1 "ON CONFLICT $conf1"} 258 if {$conf2!=""} {set conf2 "ON CONFLICT $conf2"} 259 set r0 [catch {execsql [subst { 260 DROP TABLE t1; 261 CREATE TABLE t1(a,b,c, UNIQUE(a) $conf1); 262 INSERT INTO t1 SELECT * FROM t2; 263 UPDATE t3 SET x=0; 264 BEGIN $conf2; 265 $cmd t3 SET x=1; 266 $cmd t1 SET b=b*2; 267 $cmd t1 SET a=c+5; 268 }]} r1] 269 catch {execsql {COMMIT}} 270 if {!$r0} {set r1 [execsql {SELECT a FROM t1 ORDER BY b}]} 271 set r2 [execsql {SELECT x FROM t3}] 272 list $r0 $r1 $r2 273 } [list $t0 $t1 $t2] 274} 275 276do_test memdb-6.1 { 277 execsql { 278 SELECT * FROM t2; 279 } 280} {1 2 1 2 3 2 3 4 1 4 5 4} 281do_test memdb-6.2 { 282 execsql { 283 BEGIN; 284 DROP TABLE t2; 285 SELECT name FROM sqlite_master WHERE type='table' ORDER BY 1; 286 } 287} {t1 t3 t4} 288do_test memdb-6.3 { 289 execsql { 290 ROLLBACK; 291 SELECT name FROM sqlite_master WHERE type='table' ORDER BY 1; 292 } 293} {t1 t2 t3 t4} 294do_test memdb-6.4 { 295 execsql { 296 SELECT * FROM t2; 297 } 298} {1 2 1 2 3 2 3 4 1 4 5 4} 299do_test memdb-6.5 { 300 execsql { 301 SELECT a FROM t2 UNION SELECT b FROM t2 ORDER BY 1; 302 } 303} {1 2 3 4 5} 304do_test memdb-6.6 { 305 execsql { 306 CREATE INDEX i2 ON t2(c); 307 SELECT a FROM t2 ORDER BY c; 308 } 309} {1 3 2 4} 310do_test memdb-6.6 { 311 execsql { 312 SELECT a FROM t2 ORDER BY c DESC; 313 } 314} {4 2 3 1} 315do_test memdb-6.7 { 316 execsql { 317 BEGIN; 318 CREATE TABLE t5(x,y); 319 INSERT INTO t5 VALUES(1,2); 320 SELECT * FROM t5; 321 } 322} {1 2} 323do_test memdb-6.8 { 324 execsql { 325 SELECT name FROM sqlite_master WHERE type='table' ORDER BY 1; 326 } 327} {t1 t2 t3 t4 t5} 328do_test memdb-6.9 { 329 execsql { 330 ROLLBACK; 331 SELECT name FROM sqlite_master WHERE type='table' ORDER BY 1; 332 } 333} {t1 t2 t3 t4} 334do_test memdb-6.10 { 335 execsql { 336 CREATE TABLE t5(x PRIMARY KEY, y UNIQUE); 337 SELECT * FROM t5; 338 } 339} {} 340do_test memdb-6.11 { 341 execsql { 342 SELECT * FROM t5 ORDER BY y DESC; 343 } 344} {} 345do_test memdb-6.12 { 346 execsql { 347 INSERT INTO t5 VALUES(1,2); 348 INSERT INTO t5 VALUES(3,4); 349 REPLACE INTO t5 VALUES(1,4); 350 SELECT rowid,* FROM t5; 351 } 352} {3 1 4} 353do_test memdb-6.13 { 354 execsql { 355 DELETE FROM t5 WHERE x>5; 356 SELECT * FROM t5; 357 } 358} {1 4} 359do_test memdb-6.14 { 360 execsql { 361 DELETE FROM t5 WHERE y<3; 362 SELECT * FROM t5; 363 } 364} {1 4} 365do_test memdb-6.15 { 366 execsql { 367 DELETE FROM t5 WHERE x>0; 368 SELECT * FROM t5; 369 } 370} {} 371 372do_test memdb-7.1 { 373 execsql { 374 CREATE TABLE t6(x); 375 INSERT INTO t6 VALUES(1); 376 INSERT INTO t6 SELECT x+1 FROM t6; 377 INSERT INTO t6 SELECT x+2 FROM t6; 378 INSERT INTO t6 SELECT x+4 FROM t6; 379 INSERT INTO t6 SELECT x+8 FROM t6; 380 INSERT INTO t6 SELECT x+16 FROM t6; 381 INSERT INTO t6 SELECT x+32 FROM t6; 382 INSERT INTO t6 SELECT x+64 FROM t6; 383 INSERT INTO t6 SELECT x+128 FROM t6; 384 SELECT count(*) FROM (SELECT DISTINCT x FROM t6); 385 } 386} {256} 387for {set i 1} {$i<=256} {incr i} { 388 do_test memdb-7.2.$i { 389 execsql "DELETE FROM t6 WHERE x=\ 390 (SELECT x FROM t6 ORDER BY random() LIMIT 1)" 391 execsql {SELECT count(*) FROM t6} 392 } [expr {256-$i}] 393} 394 395finish_test 396