1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000
1.2 +++ b/os/persistentdata/persistentstorage/sqlite3api/TEST/TclScript/memdb.test Fri Jun 15 03:10:57 2012 +0200
1.3 @@ -0,0 +1,417 @@
1.4 +# 2001 September 15
1.5 +#
1.6 +# The author disclaims copyright to this source code. In place of
1.7 +# a legal notice, here is a blessing:
1.8 +#
1.9 +# May you do good and not evil.
1.10 +# May you find forgiveness for yourself and forgive others.
1.11 +# May you share freely, never taking more than you give.
1.12 +#
1.13 +#***********************************************************************
1.14 +# This file implements regression tests for SQLite library. The
1.15 +# focus of this script is in-memory database backend.
1.16 +#
1.17 +# $Id: memdb.test,v 1.15 2006/01/30 22:48:44 drh Exp $
1.18 +
1.19 +
1.20 +set testdir [file dirname $argv0]
1.21 +source $testdir/tester.tcl
1.22 +
1.23 +ifcapable memorydb {
1.24 +
1.25 +# In the following sequence of tests, compute the MD5 sum of the content
1.26 +# of a table, make lots of modifications to that table, then do a rollback.
1.27 +# Verify that after the rollback, the MD5 checksum is unchanged.
1.28 +#
1.29 +# These tests were browed from trans.tcl.
1.30 +#
1.31 +do_test memdb-1.1 {
1.32 + db close
1.33 + sqlite3 db :memory:
1.34 + # sqlite3 db test.db
1.35 + execsql {
1.36 + BEGIN;
1.37 + CREATE TABLE t3(x TEXT);
1.38 + INSERT INTO t3 VALUES(randstr(10,400));
1.39 + INSERT INTO t3 VALUES(randstr(10,400));
1.40 + INSERT INTO t3 SELECT randstr(10,400) FROM t3;
1.41 + INSERT INTO t3 SELECT randstr(10,400) FROM t3;
1.42 + INSERT INTO t3 SELECT randstr(10,400) FROM t3;
1.43 + INSERT INTO t3 SELECT randstr(10,400) FROM t3;
1.44 + INSERT INTO t3 SELECT randstr(10,400) FROM t3;
1.45 + INSERT INTO t3 SELECT randstr(10,400) FROM t3;
1.46 + INSERT INTO t3 SELECT randstr(10,400) FROM t3;
1.47 + INSERT INTO t3 SELECT randstr(10,400) FROM t3;
1.48 + INSERT INTO t3 SELECT randstr(10,400) FROM t3;
1.49 + COMMIT;
1.50 + SELECT count(*) FROM t3;
1.51 + }
1.52 +} {1024}
1.53 +
1.54 +# The following procedure computes a "signature" for table "t3". If
1.55 +# T3 changes in any way, the signature should change.
1.56 +#
1.57 +# This is used to test ROLLBACK. We gather a signature for t3, then
1.58 +# make lots of changes to t3, then rollback and take another signature.
1.59 +# The two signatures should be the same.
1.60 +#
1.61 +proc signature {{fn {}}} {
1.62 + set rx [db eval {SELECT x FROM t3}]
1.63 + # set r1 [md5 $rx\n]
1.64 + if {$fn!=""} {
1.65 + # set fd [open $fn w]
1.66 + # puts $fd $rx
1.67 + # close $fd
1.68 + }
1.69 + # set r [db eval {SELECT count(*), md5sum(x) FROM t3}]
1.70 + # puts "SIG($fn)=$r1"
1.71 + return [list [string length $rx] $rx]
1.72 +}
1.73 +
1.74 +# Do rollbacks. Make sure the signature does not change.
1.75 +#
1.76 +set limit 10
1.77 +for {set i 2} {$i<=$limit} {incr i} {
1.78 + set ::sig [signature one]
1.79 + # puts "sig=$sig"
1.80 + set cnt [lindex $::sig 0]
1.81 + if {$i%2==0} {
1.82 + execsql {PRAGMA synchronous=FULL}
1.83 + } else {
1.84 + execsql {PRAGMA synchronous=NORMAL}
1.85 + }
1.86 + do_test memdb-1.$i.1-$cnt {
1.87 + execsql {
1.88 + BEGIN;
1.89 + DELETE FROM t3 WHERE random()%10!=0;
1.90 + INSERT INTO t3 SELECT randstr(10,10)||x FROM t3;
1.91 + INSERT INTO t3 SELECT randstr(10,10)||x FROM t3;
1.92 + ROLLBACK;
1.93 + }
1.94 + set sig2 [signature two]
1.95 + } $sig
1.96 + # puts "sig2=$sig2"
1.97 + # if {$sig2!=$sig} exit
1.98 + do_test memdb-1.$i.2-$cnt {
1.99 + execsql {
1.100 + BEGIN;
1.101 + DELETE FROM t3 WHERE random()%10!=0;
1.102 + INSERT INTO t3 SELECT randstr(10,10)||x FROM t3;
1.103 + DELETE FROM t3 WHERE random()%10!=0;
1.104 + INSERT INTO t3 SELECT randstr(10,10)||x FROM t3;
1.105 + ROLLBACK;
1.106 + }
1.107 + signature
1.108 + } $sig
1.109 + if {$i<$limit} {
1.110 + do_test memdb-1.$i.9-$cnt {
1.111 + execsql {
1.112 + INSERT INTO t3 SELECT randstr(10,400) FROM t3 WHERE random()%10==0;
1.113 + }
1.114 + } {}
1.115 + }
1.116 + set ::pager_old_format 0
1.117 +}
1.118 +
1.119 +integrity_check memdb-2.1
1.120 +
1.121 +do_test memdb-3.1 {
1.122 + execsql {
1.123 + CREATE TABLE t4(a,b,c,d);
1.124 + BEGIN;
1.125 + INSERT INTO t4 VALUES(1,2,3,4);
1.126 + SELECT * FROM t4;
1.127 + }
1.128 +} {1 2 3 4}
1.129 +do_test memdb-3.2 {
1.130 + execsql {
1.131 + SELECT name FROM sqlite_master WHERE type='table';
1.132 + }
1.133 +} {t3 t4}
1.134 +do_test memdb-3.3 {
1.135 + execsql {
1.136 + DROP TABLE t4;
1.137 + SELECT name FROM sqlite_master WHERE type='table';
1.138 + }
1.139 +} {t3}
1.140 +do_test memdb-3.4 {
1.141 + execsql {
1.142 + ROLLBACK;
1.143 + SELECT name FROM sqlite_master WHERE type='table';
1.144 + }
1.145 +} {t3 t4}
1.146 +
1.147 +# Create tables for the first group of tests.
1.148 +#
1.149 +do_test memdb-4.0 {
1.150 + execsql {
1.151 + CREATE TABLE t1(a, b, c, UNIQUE(a,b));
1.152 + CREATE TABLE t2(x);
1.153 + SELECT c FROM t1 ORDER BY c;
1.154 + }
1.155 +} {}
1.156 +
1.157 +# Six columns of configuration data as follows:
1.158 +#
1.159 +# i The reference number of the test
1.160 +# conf The conflict resolution algorithm on the BEGIN statement
1.161 +# cmd An INSERT or REPLACE command to execute against table t1
1.162 +# t0 True if there is an error from $cmd
1.163 +# t1 Content of "c" column of t1 assuming no error in $cmd
1.164 +# t2 Content of "x" column of t2
1.165 +#
1.166 +foreach {i conf cmd t0 t1 t2} {
1.167 + 1 {} INSERT 1 {} 1
1.168 + 2 {} {INSERT OR IGNORE} 0 3 1
1.169 + 3 {} {INSERT OR REPLACE} 0 4 1
1.170 + 4 {} REPLACE 0 4 1
1.171 + 5 {} {INSERT OR FAIL} 1 {} 1
1.172 + 6 {} {INSERT OR ABORT} 1 {} 1
1.173 + 7 {} {INSERT OR ROLLBACK} 1 {} {}
1.174 +} {
1.175 +
1.176 + # All tests after test 1 depend on conflict resolution. So end the
1.177 + # loop if that is not available in this build.
1.178 + ifcapable !conflict {if {$i>1} break}
1.179 +
1.180 + do_test memdb-4.$i {
1.181 + if {$conf!=""} {set conf "ON CONFLICT $conf"}
1.182 + set r0 [catch {execsql [subst {
1.183 + DELETE FROM t1;
1.184 + DELETE FROM t2;
1.185 + INSERT INTO t1 VALUES(1,2,3);
1.186 + BEGIN $conf;
1.187 + INSERT INTO t2 VALUES(1);
1.188 + $cmd INTO t1 VALUES(1,2,4);
1.189 + }]} r1]
1.190 + catch {execsql {COMMIT}}
1.191 + if {$r0} {set r1 {}} {set r1 [execsql {SELECT c FROM t1}]}
1.192 + set r2 [execsql {SELECT x FROM t2}]
1.193 + list $r0 $r1 $r2
1.194 + } [list $t0 $t1 $t2]
1.195 +}
1.196 +
1.197 +do_test memdb-5.0 {
1.198 + execsql {
1.199 + DROP TABLE t2;
1.200 + DROP TABLE t3;
1.201 + CREATE TABLE t2(a,b,c);
1.202 + INSERT INTO t2 VALUES(1,2,1);
1.203 + INSERT INTO t2 VALUES(2,3,2);
1.204 + INSERT INTO t2 VALUES(3,4,1);
1.205 + INSERT INTO t2 VALUES(4,5,4);
1.206 + SELECT c FROM t2 ORDER BY b;
1.207 + CREATE TABLE t3(x);
1.208 + INSERT INTO t3 VALUES(1);
1.209 + }
1.210 +} {1 2 1 4}
1.211 +
1.212 +# Six columns of configuration data as follows:
1.213 +#
1.214 +# i The reference number of the test
1.215 +# conf1 The conflict resolution algorithm on the UNIQUE constraint
1.216 +# conf2 The conflict resolution algorithm on the BEGIN statement
1.217 +# cmd An UPDATE command to execute against table t1
1.218 +# t0 True if there is an error from $cmd
1.219 +# t1 Content of "b" column of t1 assuming no error in $cmd
1.220 +# t2 Content of "x" column of t3
1.221 +#
1.222 +foreach {i conf1 conf2 cmd t0 t1 t2} {
1.223 + 1 {} {} UPDATE 1 {6 7 8 9} 1
1.224 + 2 REPLACE {} UPDATE 0 {7 6 9} 1
1.225 + 3 IGNORE {} UPDATE 0 {6 7 3 9} 1
1.226 + 4 FAIL {} UPDATE 1 {6 7 3 4} 1
1.227 + 5 ABORT {} UPDATE 1 {1 2 3 4} 1
1.228 + 6 ROLLBACK {} UPDATE 1 {1 2 3 4} 0
1.229 + 7 REPLACE {} {UPDATE OR IGNORE} 0 {6 7 3 9} 1
1.230 + 8 IGNORE {} {UPDATE OR REPLACE} 0 {7 6 9} 1
1.231 + 9 FAIL {} {UPDATE OR IGNORE} 0 {6 7 3 9} 1
1.232 + 10 ABORT {} {UPDATE OR REPLACE} 0 {7 6 9} 1
1.233 + 11 ROLLBACK {} {UPDATE OR IGNORE} 0 {6 7 3 9} 1
1.234 + 12 {} {} {UPDATE OR IGNORE} 0 {6 7 3 9} 1
1.235 + 13 {} {} {UPDATE OR REPLACE} 0 {7 6 9} 1
1.236 + 14 {} {} {UPDATE OR FAIL} 1 {6 7 3 4} 1
1.237 + 15 {} {} {UPDATE OR ABORT} 1 {1 2 3 4} 1
1.238 + 16 {} {} {UPDATE OR ROLLBACK} 1 {1 2 3 4} 0
1.239 +} {
1.240 + # All tests after test 1 depend on conflict resolution. So end the
1.241 + # loop if that is not available in this build.
1.242 + ifcapable !conflict {
1.243 + if {$i>1} break
1.244 + }
1.245 +
1.246 + if {$t0} {set t1 {column a is not unique}}
1.247 + do_test memdb-5.$i {
1.248 + if {$conf1!=""} {set conf1 "ON CONFLICT $conf1"}
1.249 + if {$conf2!=""} {set conf2 "ON CONFLICT $conf2"}
1.250 + set r0 [catch {execsql [subst {
1.251 + DROP TABLE t1;
1.252 + CREATE TABLE t1(a,b,c, UNIQUE(a) $conf1);
1.253 + INSERT INTO t1 SELECT * FROM t2;
1.254 + UPDATE t3 SET x=0;
1.255 + BEGIN $conf2;
1.256 + $cmd t3 SET x=1;
1.257 + $cmd t1 SET b=b*2;
1.258 + $cmd t1 SET a=c+5;
1.259 + }]} r1]
1.260 + catch {execsql {COMMIT}}
1.261 + if {!$r0} {set r1 [execsql {SELECT a FROM t1 ORDER BY b}]}
1.262 + set r2 [execsql {SELECT x FROM t3}]
1.263 + list $r0 $r1 $r2
1.264 + } [list $t0 $t1 $t2]
1.265 +}
1.266 +
1.267 +do_test memdb-6.1 {
1.268 + execsql {
1.269 + SELECT * FROM t2;
1.270 + }
1.271 +} {1 2 1 2 3 2 3 4 1 4 5 4}
1.272 +do_test memdb-6.2 {
1.273 + execsql {
1.274 + BEGIN;
1.275 + DROP TABLE t2;
1.276 + SELECT name FROM sqlite_master WHERE type='table' ORDER BY 1;
1.277 + }
1.278 +} {t1 t3 t4}
1.279 +do_test memdb-6.3 {
1.280 + execsql {
1.281 + ROLLBACK;
1.282 + SELECT name FROM sqlite_master WHERE type='table' ORDER BY 1;
1.283 + }
1.284 +} {t1 t2 t3 t4}
1.285 +do_test memdb-6.4 {
1.286 + execsql {
1.287 + SELECT * FROM t2;
1.288 + }
1.289 +} {1 2 1 2 3 2 3 4 1 4 5 4}
1.290 +ifcapable compound {
1.291 +do_test memdb-6.5 {
1.292 + execsql {
1.293 + SELECT a FROM t2 UNION SELECT b FROM t2 ORDER BY 1;
1.294 + }
1.295 +} {1 2 3 4 5}
1.296 +} ;# ifcapable compound
1.297 +do_test memdb-6.6 {
1.298 + execsql {
1.299 + CREATE INDEX i2 ON t2(c);
1.300 + SELECT a FROM t2 ORDER BY c;
1.301 + }
1.302 +} {1 3 2 4}
1.303 +do_test memdb-6.6 {
1.304 + execsql {
1.305 + SELECT a FROM t2 ORDER BY c DESC;
1.306 + }
1.307 +} {4 2 3 1}
1.308 +do_test memdb-6.7 {
1.309 + execsql {
1.310 + BEGIN;
1.311 + CREATE TABLE t5(x,y);
1.312 + INSERT INTO t5 VALUES(1,2);
1.313 + SELECT * FROM t5;
1.314 + }
1.315 +} {1 2}
1.316 +do_test memdb-6.8 {
1.317 + execsql {
1.318 + SELECT name FROM sqlite_master WHERE type='table' ORDER BY 1;
1.319 + }
1.320 +} {t1 t2 t3 t4 t5}
1.321 +do_test memdb-6.9 {
1.322 + execsql {
1.323 + ROLLBACK;
1.324 + SELECT name FROM sqlite_master WHERE type='table' ORDER BY 1;
1.325 + }
1.326 +} {t1 t2 t3 t4}
1.327 +do_test memdb-6.10 {
1.328 + execsql {
1.329 + CREATE TABLE t5(x PRIMARY KEY, y UNIQUE);
1.330 + SELECT * FROM t5;
1.331 + }
1.332 +} {}
1.333 +do_test memdb-6.11 {
1.334 + execsql {
1.335 + SELECT * FROM t5 ORDER BY y DESC;
1.336 + }
1.337 +} {}
1.338 +
1.339 +ifcapable conflict {
1.340 + do_test memdb-6.12 {
1.341 + execsql {
1.342 + INSERT INTO t5 VALUES(1,2);
1.343 + INSERT INTO t5 VALUES(3,4);
1.344 + REPLACE INTO t5 VALUES(1,4);
1.345 + SELECT rowid,* FROM t5;
1.346 + }
1.347 + } {3 1 4}
1.348 + do_test memdb-6.13 {
1.349 + execsql {
1.350 + DELETE FROM t5 WHERE x>5;
1.351 + SELECT * FROM t5;
1.352 + }
1.353 + } {1 4}
1.354 + do_test memdb-6.14 {
1.355 + execsql {
1.356 + DELETE FROM t5 WHERE y<3;
1.357 + SELECT * FROM t5;
1.358 + }
1.359 + } {1 4}
1.360 +}
1.361 +
1.362 +do_test memdb-6.15 {
1.363 + execsql {
1.364 + DELETE FROM t5 WHERE x>0;
1.365 + SELECT * FROM t5;
1.366 + }
1.367 +} {}
1.368 +
1.369 +ifcapable subquery {
1.370 + do_test memdb-7.1 {
1.371 + execsql {
1.372 + CREATE TABLE t6(x);
1.373 + INSERT INTO t6 VALUES(1);
1.374 + INSERT INTO t6 SELECT x+1 FROM t6;
1.375 + INSERT INTO t6 SELECT x+2 FROM t6;
1.376 + INSERT INTO t6 SELECT x+4 FROM t6;
1.377 + INSERT INTO t6 SELECT x+8 FROM t6;
1.378 + INSERT INTO t6 SELECT x+16 FROM t6;
1.379 + INSERT INTO t6 SELECT x+32 FROM t6;
1.380 + INSERT INTO t6 SELECT x+64 FROM t6;
1.381 + INSERT INTO t6 SELECT x+128 FROM t6;
1.382 + SELECT count(*) FROM (SELECT DISTINCT x FROM t6);
1.383 + }
1.384 + } {256}
1.385 + for {set i 1} {$i<=256} {incr i} {
1.386 + do_test memdb-7.2.$i {
1.387 + execsql "DELETE FROM t6 WHERE x=\
1.388 + (SELECT x FROM t6 ORDER BY random() LIMIT 1)"
1.389 + execsql {SELECT count(*) FROM t6}
1.390 + } [expr {256-$i}]
1.391 + }
1.392 +}
1.393 +
1.394 +# Ticket #1524
1.395 +#
1.396 +do_test memdb-8.1 {
1.397 + db close
1.398 + sqlite3 db {:memory:}
1.399 + execsql {
1.400 + PRAGMA auto_vacuum=TRUE;
1.401 + CREATE TABLE t1(a);
1.402 + INSERT INTO t1 VALUES(randstr(5000,6000));
1.403 + INSERT INTO t1 VALUES(randstr(5000,6000));
1.404 + INSERT INTO t1 VALUES(randstr(5000,6000));
1.405 + INSERT INTO t1 VALUES(randstr(5000,6000));
1.406 + INSERT INTO t1 VALUES(randstr(5000,6000));
1.407 + SELECT count(*) FROM t1;
1.408 + }
1.409 +} 5
1.410 +do_test memdb-8.2 {
1.411 + execsql {
1.412 + DELETE FROM t1;
1.413 + SELECT count(*) FROM t1;
1.414 + }
1.415 +} 0
1.416 +
1.417 +
1.418 +} ;# ifcapable memorydb
1.419 +
1.420 +finish_test