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.1.1.1 2012/02/21 17:04:16 misho 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