| OLD | NEW |
|---|
| (Empty) |
| 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.19 2009/05/18 16:04:38 danielk1977 Exp $ | |
| 15 | |
| 16 | |
| 17 set testdir [file dirname $argv0] | |
| 18 source $testdir/tester.tcl | |
| 19 | |
| 20 ifcapable 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 # | |
| 28 do_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 # | |
| 58 proc 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 # | |
| 73 set limit 10 | |
| 74 for {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 | |
| 116 integrity_check memdb-2.1 | |
| 117 | |
| 118 do_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} | |
| 126 do_test memdb-3.2 { | |
| 127 execsql { | |
| 128 SELECT name FROM sqlite_master WHERE type='table'; | |
| 129 } | |
| 130 } {t3 t4} | |
| 131 do_test memdb-3.3 { | |
| 132 execsql { | |
| 133 DROP TABLE t4; | |
| 134 SELECT name FROM sqlite_master WHERE type='table'; | |
| 135 } | |
| 136 } {t3} | |
| 137 do_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 # | |
| 146 do_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 # | |
| 163 foreach {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 | |
| 173 # All tests after test 1 depend on conflict resolution. So end the | |
| 174 # 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 $r2 | |
| 191 } [list $t0 $t1 $t2] | |
| 192 } | |
| 193 | |
| 194 do_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 test | |
| 212 # conf1 The conflict resolution algorithm on the UNIQUE constraint | |
| 213 # conf2 The conflict resolution algorithm on the BEGIN statement | |
| 214 # cmd An UPDATE command to execute against table t1 | |
| 215 # t0 True if there is an error from $cmd | |
| 216 # t1 Content of "b" column of t1 assuming no error in $cmd | |
| 217 # t2 Content of "x" column of t3 | |
| 218 # | |
| 219 foreach {i conf1 conf2 cmd t0 t1 t2} { | |
| 220 1 {} {} UPDATE 1 {6 7 8 9} 1 | |
| 221 2 REPLACE {} UPDATE 0 {7 6 9} 1 | |
| 222 3 IGNORE {} UPDATE 0 {6 7 3 9} 1 | |
| 223 4 FAIL {} UPDATE 1 {6 7 3 4} 1 | |
| 224 5 ABORT {} UPDATE 1 {1 2 3 4} 1 | |
| 225 6 ROLLBACK {} UPDATE 1 {1 2 3 4} 0 | |
| 226 7 REPLACE {} {UPDATE OR IGNORE} 0 {6 7 3 9} 1 | |
| 227 8 IGNORE {} {UPDATE OR REPLACE} 0 {7 6 9} 1 | |
| 228 9 FAIL {} {UPDATE OR IGNORE} 0 {6 7 3 9} 1 | |
| 229 10 ABORT {} {UPDATE OR REPLACE} 0 {7 6 9} 1 | |
| 230 11 ROLLBACK {} {UPDATE OR IGNORE} 0 {6 7 3 9} 1 | |
| 231 12 {} {} {UPDATE OR IGNORE} 0 {6 7 3 9} 1 | |
| 232 13 {} {} {UPDATE OR REPLACE} 0 {7 6 9} 1 | |
| 233 14 {} {} {UPDATE OR FAIL} 1 {6 7 3 4} 1 | |
| 234 15 {} {} {UPDATE OR ABORT} 1 {1 2 3 4} 1 | |
| 235 16 {} {} {UPDATE OR ROLLBACK} 1 {1 2 3 4} 0 | |
| 236 } { | |
| 237 # All tests after test 1 depend on conflict resolution. So end the | |
| 238 # loop if that is not available in this build. | |
| 239 ifcapable !conflict { | |
| 240 if {$i>1} break | |
| 241 } | |
| 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 $r2 | |
| 261 } [list $t0 $t1 $t2] | |
| 262 } | |
| 263 | |
| 264 do_test memdb-6.1 { | |
| 265 execsql { | |
| 266 SELECT * FROM t2; | |
| 267 } | |
| 268 } {1 2 1 2 3 2 3 4 1 4 5 4} | |
| 269 do_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} | |
| 276 do_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} | |
| 282 do_test memdb-6.4 { | |
| 283 execsql { | |
| 284 SELECT * FROM t2; | |
| 285 } | |
| 286 } {1 2 1 2 3 2 3 4 1 4 5 4} | |
| 287 ifcapable compound { | |
| 288 do_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 | |
| 294 do_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} | |
| 300 do_test memdb-6.6 { | |
| 301 execsql { | |
| 302 SELECT a FROM t2 ORDER BY c DESC; | |
| 303 } | |
| 304 } {4 2 3 1} | |
| 305 do_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} | |
| 313 do_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} | |
| 318 do_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} | |
| 324 do_test memdb-6.10 { | |
| 325 execsql { | |
| 326 CREATE TABLE t5(x PRIMARY KEY, y UNIQUE); | |
| 327 SELECT * FROM t5; | |
| 328 } | |
| 329 } {} | |
| 330 do_test memdb-6.11 { | |
| 331 execsql { | |
| 332 SELECT * FROM t5 ORDER BY y DESC; | |
| 333 } | |
| 334 } {} | |
| 335 | |
| 336 ifcapable 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 | |
| 359 do_test memdb-6.15 { | |
| 360 execsql { | |
| 361 DELETE FROM t5 WHERE x>0; | |
| 362 SELECT * FROM t5; | |
| 363 } | |
| 364 } {} | |
| 365 | |
| 366 ifcapable subquery&&vtab { | |
| 367 do_test memdb-7.1 { | |
| 368 load_static_extension db wholenumber | |
| 369 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 #1524 | |
| 386 # | |
| 387 do_test memdb-8.1 { | |
| 388 db close | |
| 389 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 } 5 | |
| 401 do_test memdb-8.2 { | |
| 402 execsql { | |
| 403 DELETE FROM t1; | |
| 404 SELECT count(*) FROM t1; | |
| 405 } | |
| 406 } 0 | |
| 407 | |
| 408 # Test that auto-vacuum works with in-memory databases. | |
| 409 # | |
| 410 ifcapable autovacuum { | |
| 411 do_test memdb-9.1 { | |
| 412 db close | |
| 413 sqlite3 db test.db | |
| 414 db cache size 0 | |
| 415 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 memused [lindex [sqlite3_status SQLITE_STATUS_MEMORY_USED 0] 1] | |
| 423 set pgovfl [lindex [sqlite3_status SQLITE_STATUS_PAGECACHE_OVERFLOW 0] 1] | |
| 424 execsql { DELETE FROM t1 } | |
| 425 set memused2 [lindex [sqlite3_status SQLITE_STATUS_MEMORY_USED 0] 1] | |
| 426 expr {($memused2 + 2048 < $memused) || $pgovfl==0} | |
| 427 } {1} | |
| 428 } | |
| 429 | |
| 430 } ;# ifcapable memorydb | |
| 431 | |
| 432 finish_test | |
| OLD | NEW |
|---|
Chromium Code Reviews
Issue 2363173002:
[sqlite] Remove obsolete reference version 3.8.7.4. (Closed)
