Annotation of embedaddon/sqlite3/test/memdb.test, revision 1.1
1.1 ! misho 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 {column a is not unique}}
! 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: register_wholenumber_module db
! 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
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to memdb.test CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test