Debian Codegraph @

test/dbstatus2.test

raw
1 # 2011 September 20
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 #
12 # Tests for the sqlite3_db_status() function
13 #
14
15 set testdir [file dirname $argv0]
16 source $testdir/tester.tcl
17
18 set ::testprefix dbstatus2
19
20 do_execsql_test 1.0 {
21 PRAGMA page_size = 1024;
22 PRAGMA auto_vacuum = 0;
23
24 CREATE TABLE t1(a PRIMARY KEY, b);
25 INSERT INTO t1 VALUES(1, randomblob(600));
26 INSERT INTO t1 VALUES(2, randomblob(600));
27 INSERT INTO t1 VALUES(3, randomblob(600));
28 }
29
30 proc db_hit_miss {db {reset 0}} {
31 set nHit [sqlite3_db_status $db CACHE_HIT $reset]
32 set nMiss [sqlite3_db_status $db CACHE_MISS $reset]
33 list $nHit $nMiss
34 }
35
36 proc db_write {db {reset 0}} {
37 sqlite3_db_status $db CACHE_WRITE $reset
38 }
39
40 proc db_spill {db {reset 0}} {
41 sqlite3_db_status $db CACHE_SPILL $reset
42 }
43
44 proc db_temp_spill {db {reset 0}} {
45 sqlite3_db_status $db TEMPBUF_SPILL $reset
46 }
47
48 do_test 1.1 {
49 db close
50 sqlite3 db test.db
51 execsql { PRAGMA mmap_size = 0 }
52 expr {[file size test.db] / 1024}
53 } 6
54
55 do_test 1.2 {
56 execsql { SELECT b FROM t1 WHERE a=2 }
57 db_hit_miss db
58 } {{0 2 0} {0 4 0}}
59
60 do_test 1.3 {
61 execsql { SELECT b FROM t1 WHERE a=2 }
62 db_hit_miss db
63 } {{0 6 0} {0 4 0}}
64
65 do_test 1.4 {
66 execsql { SELECT b FROM t1 WHERE a=2 }
67 db_hit_miss db
68 } {{0 10 0} {0 4 0}}
69
70 do_test 1.5 {
71 db_hit_miss db 1
72 } {{0 10 0} {0 4 0}}
73
74 do_test 1.6 {
75 db_hit_miss db 0
76 } {{0 0 0} {0 0 0}}
77
78 do_test 1.7 {
79 set fd [db incrblob main t1 b 1]
80 fconfigure $fd -translation binary
81 set len [string length [read $fd]]
82 close $fd
83 set len
84 } 600
85 do_test 1.8 { sqlite3_db_status db CACHE_HIT 0 } {0 2 0}
86 do_test 1.9 { sqlite3_db_status db CACHE_MISS 0 } {0 1 0}
87
88 do_test 2.1 { db_write db } {0 0 0}
89 do_test 2.2 {
90 execsql { INSERT INTO t1 VALUES(4, randomblob(600)) }
91 db_write db
92 } {0 4 0}
93 do_test 2.3 { db_write db 1 } {0 4 0}
94 do_test 2.4 { db_write db 0 } {0 0 0}
95 do_test 2.5 { db_write db 1 } {0 0 0}
96
97 if {[wal_is_capable]} {
98 do_test 2.6 {
99 execsql { PRAGMA journal_mode = WAL }
100 db_write db 1
101 } {0 1 0}
102 }
103 do_test 2.7 {
104 execsql { INSERT INTO t1 VALUES(5, randomblob(600)) }
105 db_write db
106 } {0 4 0}
107 do_test 2.8 { db_write db 1 } {0 4 0}
108 do_test 2.9 { db_write db 0 } {0 0 0}
109
110 do_test 3.0 { db_spill db 1 } {0 0 0}
111 do_test 3.1 { db_spill db 0 } {0 0 0}
112 do_execsql_test 3.2 {
113 PRAGMA journal_mode=DELETE;
114 PRAGMA cache_size=3;
115 UPDATE t1 SET b=randomblob(1000);
116 } {delete}
117 do_test 3.3 { db_spill db 0 } {0 8 0}
118
119
120 if {$::TEMP_STORE<3} {
121 do_execsql_test 4.0 {
122 PRAGMA temp_store = file;
123 PRAGMA cache_size = -1024;
124 } {}
125
126 do_test 4.1 { db_temp_spill db 0 } {0 0 0}
127
128 do_execsql_test 4.2 {
129 CREATE TABLE data(a INTEGER, b BLOB);
130
131 -- Insert 5-6 MB of data.
132 WITH s(i) AS ( SELECT 1 UNION ALL SELECT i+1 FROM s WHERE i<75000 )
133 INSERT INTO data SELECT i, hex(randomblob(50)) FROM s;
134 }
135
136 do_test 4.3 { db_temp_spill db 0 } {0 0 0}
137
138 do_test 4.4 {
139 execsql { SELECT a, b FROM data ORDER BY a }
140 set nTmpSpill [lindex [db_temp_spill db 1] 1]
141 expr {($nTmpSpill>7*1000*1000) && ($nTmpSpill<10*1000*1000)?"ok":$nTmpSpill}
142 } ok
143
144 # The previous test case reset the status value.
145 do_test 4.5 { db_temp_spill db 0 } {0 0 0}
146
147 do_test 4.6 {
148 execsql { CREATE INDEX i1 ON data(a) }
149 set nTmpSpill [lindex [db_temp_spill db 1] 1]
150 expr {($nTmpSpill>384*1000) && ($nTmpSpill<768*1000)?"ok":$nTmpSpill}
151 } ok
152
153 # The previous test case reset the status value.
154 do_test 4.7 { db_temp_spill db 0 } {0 0 0}
155
156 # Same query as in (4.4). Now does not require temp space.
157 do_test 4.8 {
158 execsql { SELECT a, b FROM data ORDER BY a }
159 db_temp_spill db 0
160 } {0 0 0}
161 }
162
163
164 finish_test