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.6 2003/08/05 13:13:39 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 set ::journal_format [expr {($i%3)+1}] 77 if {$i%2==0} { 78 execsql {PRAGMA synchronous=FULL} 79 } else { 80 execsql {PRAGMA synchronous=NORMAL} 81 } 82 do_test memdb-1.$i.1-$cnt { 83 execsql { 84 BEGIN; 85 DELETE FROM t3 WHERE random()%10!=0; 86 INSERT INTO t3 SELECT randstr(10,10)||x FROM t3; 87 INSERT INTO t3 SELECT randstr(10,10)||x FROM t3; 88 ROLLBACK; 89 } 90 set sig2 [signature two] 91 } $sig 92 # puts "sig2=$sig2" 93 # if {$sig2!=$sig} exit 94 do_test memdb-1.$i.2-$cnt { 95 execsql { 96 BEGIN; 97 DELETE FROM t3 WHERE random()%10!=0; 98 INSERT INTO t3 SELECT randstr(10,10)||x FROM t3; 99 DELETE FROM t3 WHERE random()%10!=0; 100 INSERT INTO t3 SELECT randstr(10,10)||x FROM t3; 101 ROLLBACK; 102 } 103 signature 104 } $sig 105 if {$i<$limit} { 106 do_test memdb-1.$i.9-$cnt { 107 execsql { 108 INSERT INTO t3 SELECT randstr(10,400) FROM t3 WHERE random()%10==0; 109 } 110 } {} 111 } 112 set ::pager_old_format 0 113} 114 115do_test memdb-2.1 { 116 execsql { 117 PRAGMA integrity_check 118 } 119} {ok} 120 121do_test memdb-3.1 { 122 execsql { 123 CREATE TABLE t4(a,b,c,d); 124 BEGIN; 125 INSERT INTO t4 VALUES(1,2,3,4); 126 SELECT * FROM t4; 127 } 128} {1 2 3 4} 129do_test memdb-3.2 { 130 execsql { 131 SELECT name FROM sqlite_master WHERE type='table'; 132 } 133} {t3 t4} 134do_test memdb-3.3 { 135 execsql { 136 DROP TABLE t4; 137 SELECT name FROM sqlite_master WHERE type='table'; 138 } 139} {t3} 140do_test memdb-3.4 { 141 execsql { 142 ROLLBACK; 143 SELECT name FROM sqlite_master WHERE type='table'; 144 } 145} {t3 t4} 146 147# Create tables for the first group of tests. 148# 149do_test memdb-4.0 { 150 execsql { 151 CREATE TABLE t1(a, b, c, UNIQUE(a,b)); 152 CREATE TABLE t2(x); 153 SELECT c FROM t1 ORDER BY c; 154 } 155} {} 156 157# Six columns of configuration data as follows: 158# 159# i The reference number of the test 160# conf The conflict resolution algorithm on the BEGIN statement 161# cmd An INSERT or REPLACE command to execute against table t1 162# t0 True if there is an error from $cmd 163# t1 Content of "c" column of t1 assuming no error in $cmd 164# t2 Content of "x" column of t2 165# 166foreach {i conf cmd t0 t1 t2} { 167 1 {} INSERT 1 {} 1 168 2 {} {INSERT OR IGNORE} 0 3 1 169 3 {} {INSERT OR REPLACE} 0 4 1 170 4 {} REPLACE 0 4 1 171 5 {} {INSERT OR FAIL} 1 {} 1 172 6 {} {INSERT OR ABORT} 1 {} 1 173 7 {} {INSERT OR ROLLBACK} 1 {} {} 174 8 IGNORE INSERT 0 3 1 175 9 IGNORE {INSERT OR IGNORE} 0 3 1 176 10 IGNORE {INSERT OR REPLACE} 0 4 1 177 11 IGNORE REPLACE 0 4 1 178 12 IGNORE {INSERT OR FAIL} 1 {} 1 179 13 IGNORE {INSERT OR ABORT} 1 {} 1 180 14 IGNORE {INSERT OR ROLLBACK} 1 {} {} 181 15 REPLACE INSERT 0 4 1 182 16 FAIL INSERT 1 {} 1 183 17 ABORT INSERT 1 {} 1 184 18 ROLLBACK INSERT 1 {} {} 185} { 186 do_test memdb-4.$i { 187 if {$conf!=""} {set conf "ON CONFLICT $conf"} 188 set r0 [catch {execsql [subst { 189 DELETE FROM t1; 190 DELETE FROM t2; 191 INSERT INTO t1 VALUES(1,2,3); 192 BEGIN $conf; 193 INSERT INTO t2 VALUES(1); 194 $cmd INTO t1 VALUES(1,2,4); 195 }]} r1] 196 catch {execsql {COMMIT}} 197 if {$r0} {set r1 {}} {set r1 [execsql {SELECT c FROM t1}]} 198 set r2 [execsql {SELECT x FROM t2}] 199 list $r0 $r1 $r2 200 } [list $t0 $t1 $t2] 201} 202 203do_test memdb-5.0 { 204 execsql { 205 DROP TABLE t2; 206 DROP TABLE t3; 207 CREATE TABLE t2(a,b,c); 208 INSERT INTO t2 VALUES(1,2,1); 209 INSERT INTO t2 VALUES(2,3,2); 210 INSERT INTO t2 VALUES(3,4,1); 211 INSERT INTO t2 VALUES(4,5,4); 212 SELECT c FROM t2 ORDER BY b; 213 CREATE TABLE t3(x); 214 INSERT INTO t3 VALUES(1); 215 } 216} {1 2 1 4} 217 218# Six columns of configuration data as follows: 219# 220# i The reference number of the test 221# conf1 The conflict resolution algorithm on the UNIQUE constraint 222# conf2 The conflict resolution algorithm on the BEGIN statement 223# cmd An UPDATE command to execute against table t1 224# t0 True if there is an error from $cmd 225# t1 Content of "b" column of t1 assuming no error in $cmd 226# t2 Content of "x" column of t3 227# 228foreach {i conf1 conf2 cmd t0 t1 t2} { 229 1 {} {} UPDATE 1 {6 7 8 9} 1 230 2 REPLACE {} UPDATE 0 {7 6 9} 1 231 3 IGNORE {} UPDATE 0 {6 7 3 9} 1 232 4 FAIL {} UPDATE 1 {6 7 3 4} 1 233 5 ABORT {} UPDATE 1 {1 2 3 4} 1 234 6 ROLLBACK {} UPDATE 1 {1 2 3 4} 0 235 7 REPLACE {} {UPDATE OR IGNORE} 0 {6 7 3 9} 1 236 8 IGNORE {} {UPDATE OR REPLACE} 0 {7 6 9} 1 237 9 FAIL {} {UPDATE OR IGNORE} 0 {6 7 3 9} 1 238 10 ABORT {} {UPDATE OR REPLACE} 0 {7 6 9} 1 239 11 ROLLBACK {} {UPDATE OR IGNORE} 0 {6 7 3 9} 1 240 12 {} {} {UPDATE OR IGNORE} 0 {6 7 3 9} 1 241 13 {} {} {UPDATE OR REPLACE} 0 {7 6 9} 1 242 14 {} {} {UPDATE OR FAIL} 1 {6 7 3 4} 1 243 15 {} {} {UPDATE OR ABORT} 1 {1 2 3 4} 1 244 16 {} {} {UPDATE OR ROLLBACK} 1 {1 2 3 4} 0 245 17 {} IGNORE UPDATE 0 {6 7 3 9} 1 246 18 {} REPLACE UPDATE 0 {7 6 9} 1 247 19 {} FAIL UPDATE 1 {6 7 3 4} 1 248 20 {} ABORT UPDATE 1 {1 2 3 4} 1 249 21 {} ROLLBACK UPDATE 1 {1 2 3 4} 0 250 22 REPLACE IGNORE UPDATE 0 {6 7 3 9} 1 251 23 IGNORE REPLACE UPDATE 0 {7 6 9} 1 252 24 REPLACE FAIL UPDATE 1 {6 7 3 4} 1 253 25 IGNORE ABORT UPDATE 1 {1 2 3 4} 1 254 26 REPLACE ROLLBACK UPDATE 1 {1 2 3 4} 0 255} { 256 if {$t0} {set t1 {column a is not unique}} 257 do_test memdb-5.$i { 258 if {$conf1!=""} {set conf1 "ON CONFLICT $conf1"} 259 if {$conf2!=""} {set conf2 "ON CONFLICT $conf2"} 260 set r0 [catch {execsql [subst { 261 DROP TABLE t1; 262 CREATE TABLE t1(a,b,c, UNIQUE(a) $conf1); 263 INSERT INTO t1 SELECT * FROM t2; 264 UPDATE t3 SET x=0; 265 BEGIN $conf2; 266 $cmd t3 SET x=1; 267 $cmd t1 SET b=b*2; 268 $cmd t1 SET a=c+5; 269 }]} r1] 270 catch {execsql {COMMIT}} 271 if {!$r0} {set r1 [execsql {SELECT a FROM t1 ORDER BY b}]} 272 set r2 [execsql {SELECT x FROM t3}] 273 list $r0 $r1 $r2 274 } [list $t0 $t1 $t2] 275} 276 277do_test memdb-6.1 { 278 execsql { 279 SELECT * FROM t2; 280 } 281} {1 2 1 2 3 2 3 4 1 4 5 4} 282do_test memdb-6.2 { 283 execsql { 284 BEGIN; 285 DROP TABLE t2; 286 SELECT name FROM sqlite_master WHERE type='table' ORDER BY 1; 287 } 288} {t1 t3 t4} 289do_test memdb-6.3 { 290 execsql { 291 ROLLBACK; 292 SELECT name FROM sqlite_master WHERE type='table' ORDER BY 1; 293 } 294} {t1 t2 t3 t4} 295do_test memdb-6.4 { 296 execsql { 297 SELECT * FROM t2; 298 } 299} {1 2 1 2 3 2 3 4 1 4 5 4} 300do_test memdb-6.5 { 301 execsql { 302 SELECT a FROM t2 UNION SELECT b FROM t2 ORDER BY 1; 303 } 304} {1 2 3 4 5} 305do_test memdb-6.6 { 306 execsql { 307 CREATE INDEX i2 ON t2(c); 308 SELECT a FROM t2 ORDER BY c; 309 } 310} {1 3 2 4} 311do_test memdb-6.6 { 312 execsql { 313 SELECT a FROM t2 ORDER BY c DESC; 314 } 315} {4 2 3 1} 316do_test memdb-6.7 { 317 execsql { 318 BEGIN; 319 CREATE TABLE t5(x,y); 320 INSERT INTO t5 VALUES(1,2); 321 SELECT * FROM t5; 322 } 323} {1 2} 324do_test memdb-6.8 { 325 execsql { 326 SELECT name FROM sqlite_master WHERE type='table' ORDER BY 1; 327 } 328} {t1 t2 t3 t4 t5} 329do_test memdb-6.9 { 330 execsql { 331 ROLLBACK; 332 SELECT name FROM sqlite_master WHERE type='table' ORDER BY 1; 333 } 334} {t1 t2 t3 t4} 335do_test memdb-6.10 { 336 execsql { 337 CREATE TABLE t5(x PRIMARY KEY, y UNIQUE); 338 SELECT * FROM t5; 339 } 340} {} 341do_test memdb-6.11 { 342 execsql { 343 SELECT * FROM t5 ORDER BY y DESC; 344 } 345} {} 346do_test memdb-6.12 { 347 execsql { 348 INSERT INTO t5 VALUES(1,2); 349 INSERT INTO t5 VALUES(3,4); 350 REPLACE INTO t5 VALUES(1,4); 351 SELECT rowid,* FROM t5; 352 } 353} {3 1 4} 354do_test memdb-6.13 { 355 execsql { 356 DELETE FROM t5 WHERE x>5; 357 SELECT * FROM t5; 358 } 359} {1 4} 360do_test memdb-6.14 { 361 execsql { 362 DELETE FROM t5 WHERE y<3; 363 SELECT * FROM t5; 364 } 365} {1 4} 366do_test memdb-6.15 { 367 execsql { 368 DELETE FROM t5 WHERE x>0; 369 SELECT * FROM t5; 370 } 371} {} 372 373do_test memdb-7.1 { 374 execsql { 375 CREATE TABLE t6(x); 376 INSERT INTO t6 VALUES(1); 377 INSERT INTO t6 SELECT x+1 FROM t6; 378 INSERT INTO t6 SELECT x+2 FROM t6; 379 INSERT INTO t6 SELECT x+4 FROM t6; 380 INSERT INTO t6 SELECT x+8 FROM t6; 381 INSERT INTO t6 SELECT x+16 FROM t6; 382 INSERT INTO t6 SELECT x+32 FROM t6; 383 INSERT INTO t6 SELECT x+64 FROM t6; 384 INSERT INTO t6 SELECT x+128 FROM t6; 385 SELECT count(*) FROM (SELECT DISTINCT x FROM t6); 386 } 387} {256} 388for {set i 1} {$i<=256} {incr i} { 389 do_test memdb-7.2.$i { 390 execsql "DELETE FROM t6 WHERE x=\ 391 (SELECT x FROM t6 ORDER BY random() LIMIT 1)" 392 execsql {SELECT count(*) FROM t6} 393 } [expr {256-$i}] 394} 395 396finish_test 397