Debian Codegraph @

ext/session/sessionstat1.test

raw
1 # 2018 January 12
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
13 if {![info exists testdir]} {
14 set testdir [file join [file dirname [info script]] .. .. test]
15 }
16 source [file join [file dirname [info script]] session_common.tcl]
17 source $testdir/tester.tcl
18 ifcapable !session {finish_test; return}
19
20 set testprefix sessionstat1
21
22 do_execsql_test 1.0 {
23 CREATE TABLE t1(a PRIMARY KEY, b, c);
24 CREATE INDEX t1b ON t1(b);
25 CREATE INDEX t1c ON t1(c);
26
27 WITH s(i) AS (
28 SELECT 0 UNION ALL SELECT i+1 FROM s WHERE (i+1)<32
29 )
30 INSERT INTO t1 SELECT i, i%8, i%2 FROM s;
31 }
32
33 do_iterator_test 1.1 {} {
34 ANALYZE
35 } {
36 {INSERT sqlite_stat1 0 XX. {} {t t1 t sqlite_autoindex_t1_1 t {32 1}}}
37 {INSERT sqlite_stat1 0 XX. {} {t t1 t t1b t {32 4}}}
38 {INSERT sqlite_stat1 0 XX. {} {t t1 t t1c t {32 16}}}
39 }
40
41 do_execsql_test 1.2 {
42 WITH s(i) AS (
43 SELECT 32 UNION ALL SELECT i+1 FROM s WHERE (i+1)<64
44 )
45 INSERT INTO t1 SELECT i, i%8, i%2 FROM s;
46 }
47
48 do_iterator_test 1.3 {} {
49 ANALYZE
50 } {
51 {UPDATE sqlite_stat1 0 XX. {t t1 t sqlite_autoindex_t1_1 t {32 1}} {{} {} {} {} t {64 1}}}
52 {UPDATE sqlite_stat1 0 XX. {t t1 t t1b t {32 4}} {{} {} {} {} t {64 8}}}
53 {UPDATE sqlite_stat1 0 XX. {t t1 t t1c t {32 16}} {{} {} {} {} t {64 32}}}
54 }
55
56 do_iterator_test 1.5 {} {
57 DROP INDEX t1b;
58 } {
59 {DELETE sqlite_stat1 0 XX. {t t1 t t1b t {64 8}} {}}
60 }
61
62 do_iterator_test 1.6 {} {
63 DROP TABLE t1;
64 } {
65 {DELETE sqlite_stat1 0 XX. {t t1 t sqlite_autoindex_t1_1 t {64 1}} {}}
66 {DELETE sqlite_stat1 0 XX. {t t1 t t1c t {64 32}} {}}
67 }
68
69 #-------------------------------------------------------------------------
70 #
71 catch { db2 close }
72 forcedelete test.db2
73 sqlite3 db2 test.db2
74
75 do_test 2.0 {
76 do_common_sql {
77 CREATE TABLE t1(a PRIMARY KEY, b, c);
78 CREATE INDEX t1b ON t1(b);
79 CREATE INDEX t1c ON t1(c);
80 ANALYZE;
81 }
82 } {}
83
84 do_test 2.1 {
85 do_then_apply_sql -ignorenoop {
86 WITH s(i) AS (
87 SELECT 0 UNION ALL SELECT i+1 FROM s WHERE (i+1)<32
88 )
89 INSERT INTO t1 SELECT i, i%8, i%2 FROM s;
90 ANALYZE;
91 }
92 } {}
93
94 do_execsql_test -db db2 2.2 {
95 SELECT * FROM sqlite_stat1 ORDER BY tbl, idx
96 } {
97 t1 sqlite_autoindex_t1_1 {32 1}
98 t1 t1b {32 4}
99 t1 t1c {32 16}
100 }
101
102 do_test 2.3 {
103 do_then_apply_sql -ignorenoop { DROP INDEX t1c }
104 } {}
105
106 do_execsql_test -db db2 2.4 {
107 SELECT * FROM sqlite_stat1 ORDER BY tbl, idx;
108 } {
109 t1 sqlite_autoindex_t1_1 {32 1}
110 t1 t1b {32 4}
111 }
112
113 do_test 2.3 {
114 do_then_apply_sql -ignorenoop { DROP TABLE t1 }
115 } {}
116
117 do_execsql_test -db db2 2.4 {
118 SELECT * FROM sqlite_stat1
119 } {
120 }
121
122 do_execsql_test -db db2 2.5 { SELECT count(*) FROM t1 } 32
123
124 #-------------------------------------------------------------------------
125 db2 close
126 forcedelete test.db2
127 reset_db
128 sqlite3 db2 test.db2
129
130 do_test 3.0 {
131 do_common_sql {
132 CREATE TABLE t1(a, b, c);
133 ANALYZE;
134 DELETE FROM sqlite_stat1;
135 }
136 execsql {
137 INSERT INTO t1 VALUES(1, 1, 1);
138 INSERT INTO t1 VALUES(2, 2, 2);
139 INSERT INTO t1 VALUES(3, 3, 3);
140 INSERT INTO t1 VALUES(4, 4, 4);
141 }
142 } {}
143
144 do_iterator_test 3.1 {} {
145 ANALYZE
146 } {
147 {INSERT sqlite_stat1 0 XX. {} {t t1 b {} t 4}}
148 }
149 db null null
150 db2 null null
151 do_execsql_test 3.2 {
152 SELECT * FROM sqlite_stat1;
153 } {t1 null 4}
154 do_test 3.3 {
155 execsql { DELETE FROM sqlite_stat1 }
156 do_then_apply_sql -ignorenoop { ANALYZE }
157 execsql { SELECT * FROM sqlite_stat1 } db2
158 } {t1 null 4}
159 do_test 3.4 {
160 execsql { INSERT INTO t1 VALUES(5,5,5) }
161 do_then_apply_sql -ignorenoop { ANALYZE }
162 execsql { SELECT * FROM sqlite_stat1 } db2
163 } {t1 null 5}
164 do_test 3.5 {
165 do_then_apply_sql -ignorenoop { DROP TABLE t1 }
166 execsql { SELECT * FROM sqlite_stat1 } db2
167 } {}
168
169 do_test 3.6.1 {
170 execsql {
171 CREATE TABLE t1(a, b, c);
172 CREATE TABLE t2(x, y, z);
173 INSERT INTO t1 VALUES(1,1,1), (2,2,2), (3,3,3), (4,4,4), (5,5,5);
174 INSERT INTO t2 SELECT * FROM t1;
175 DELETE FROM sqlite_stat1;
176 }
177 sqlite3session S db main
178 S attach sqlite_stat1
179 execsql { ANALYZE }
180 } {}
181 do_changeset_test 3.6.2 S {
182 {INSERT sqlite_stat1 0 XX. {} {t t2 b {} t 5}}
183 {INSERT sqlite_stat1 0 XX. {} {t t1 b {} t 5}}
184 }
185 do_changeset_invert_test 3.6.3 S {
186 {DELETE sqlite_stat1 0 XX. {t t2 b {} t 5} {}}
187 {DELETE sqlite_stat1 0 XX. {t t1 b {} t 5} {}}
188 }
189 do_test 3.6.4 { S delete } {}
190
191 proc sql_changeset_concat {args} {
192 foreach sql $args {
193 sqlite3session S db main
194 S attach sqlite_stat1
195 execsql $sql
196 set change [S changeset]
197 S delete
198
199 if {[info vars ret]!=""} {
200 set ret [sqlite3changeset_concat $ret $change]
201 } else {
202 set ret $change
203 }
204 }
205
206 changeset_to_list $ret
207 }
208
209 proc do_scc_test {tn args} {
210 uplevel [list \
211 do_test $tn [concat sql_changeset_concat [lrange $args 0 end-1]] \
212 [list {*}[ lindex $args end ]]
213 ]
214 }
215
216 do_execsql_test 3.7.0 {
217 DELETE FROM sqlite_stat1;
218 }
219 do_scc_test 3.7.1 {
220 ANALYZE;
221 } {
222 INSERT INTO t2 VALUES(6,6,6);
223 ANALYZE;
224 } {
225 {INSERT sqlite_stat1 0 XX. {} {t t1 b {} t 5}}
226 {INSERT sqlite_stat1 0 XX. {} {t t2 b {} t 6}}
227 }
228
229 #-------------------------------------------------------------------------
230 catch { db2 close }
231 reset_db
232 forcedelete test.db2
233 sqlite3 db2 test.db2
234
235 do_test 4.1.0 {
236 do_common_sql {
237 CREATE TABLE t1(a, b);
238 CREATE INDEX i1 ON t1(a);
239 CREATE INDEX i2 ON t1(b);
240 INSERT INTO t1 VALUES(1,1), (2,2);
241 ANALYZE;
242 }
243 execsql { DELETE FROM sqlite_stat1 }
244 } {}
245
246 do_test 4.1.1 {
247 execsql { INSERT INTO t1 VALUES(3,3); }
248 set C [changeset_from_sql {ANALYZE}]
249 set ::c [list]
250 proc xConflict {args} {
251 lappend ::c $args
252 return "OMIT"
253 }
254 sqlite3changeset_apply db2 $C xConflict
255 set ::c
256 } [list {*}{
257 {INSERT sqlite_stat1 CONFLICT {t t1 t i1 t {3 1}} {t t1 t i1 t {2 1}}}
258 {INSERT sqlite_stat1 CONFLICT {t t1 t i2 t {3 1}} {t t1 t i2 t {2 1}}}
259 }]
260
261 do_execsql_test -db db2 4.1.2 {
262 SELECT * FROM sqlite_stat1 ORDER BY 1,2;
263 } {t1 i1 {2 1} t1 i2 {2 1}}
264
265 do_test 4.1.3 {
266 proc xConflict {args} {
267 return "REPLACE"
268 }
269 sqlite3changeset_apply db2 $C xConflict
270 execsql { SELECT * FROM sqlite_stat1 ORDER BY 1,2 } db2
271 } {t1 i1 {3 1} t1 i2 {3 1}}
272
273 do_test 4.2.0 {
274 do_common_sql {
275 DROP TABLE t1;
276 CREATE TABLE t3(x,y);
277 INSERT INTO t3 VALUES('a','a');
278 INSERT INTO t3 VALUES('b','b');
279 ANALYZE;
280 }
281 execsql { DELETE FROM sqlite_stat1 }
282 } {}
283 do_test 4.2.1 {
284 execsql { INSERT INTO t3 VALUES('c','c'); }
285 set C [changeset_from_sql {ANALYZE}]
286 set ::c [list]
287 proc xConflict {args} {
288 lappend ::c $args
289 return "OMIT"
290 }
291 sqlite3changeset_apply db2 $C xConflict
292 set ::c
293 } [list {*}{
294 {INSERT sqlite_stat1 CONFLICT {t t3 b {} t 3} {t t3 b {} t 2}}
295 }]
296
297 db2 null null
298 do_execsql_test -db db2 4.2.2 {
299 SELECT * FROM sqlite_stat1 ORDER BY 1,2;
300 } {t3 null 2}
301
302 do_test 4.2.3 {
303 proc xConflict {args} {
304 return "REPLACE"
305 }
306 sqlite3changeset_apply db2 $C xConflict
307 execsql { SELECT * FROM sqlite_stat1 ORDER BY 1,2 } db2
308 } {t3 null 3}
309
310 finish_test