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
13if {![info exists testdir]} {
14  set testdir [file join [file dirname [info script]] .. .. test]
15}
16source [file join [file dirname [info script]] session_common.tcl]
17source $testdir/tester.tcl
18ifcapable !session {finish_test; return}
19
20set testprefix sessionstat1
21
22do_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
33do_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
41do_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
48do_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
56do_iterator_test 1.5 {} {
57  DROP INDEX t1b;
58} {
59  {DELETE sqlite_stat1 0 XX. {t t1 t t1b t {64 8}} {}}
60}
61
62do_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#
71catch { db2 close }
72forcedelete test.db2
73sqlite3 db2 test.db2
74
75do_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
84do_test 2.1 {
85  do_then_apply_sql {
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
94do_execsql_test -db db2 2.2 {
95  SELECT * FROM sqlite_stat1
96} {
97  t1 sqlite_autoindex_t1_1 {32 1}
98  t1 t1b {32 4}
99  t1 t1c {32 16}
100}
101
102do_test 2.3 {
103  do_then_apply_sql { DROP INDEX t1c }
104} {}
105
106do_execsql_test -db db2 2.4 {
107  SELECT * FROM sqlite_stat1
108} {
109  t1 sqlite_autoindex_t1_1 {32 1}
110  t1 t1b {32 4}
111}
112
113do_test 2.3 {
114  do_then_apply_sql { DROP TABLE t1 }
115} {}
116
117do_execsql_test -db db2 2.4 {
118  SELECT * FROM sqlite_stat1
119} {
120}
121
122do_execsql_test -db db2 2.5 { SELECT count(*) FROM t1 } 32
123
124#-------------------------------------------------------------------------
125db2 close
126forcedelete test.db2
127reset_db
128sqlite3 db2 test.db2
129
130do_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
144do_iterator_test 3.1 {} {
145  ANALYZE
146} {
147  {INSERT sqlite_stat1 0 XX. {} {t t1 b {} t 4}}
148}
149db null null
150db2 null null
151do_execsql_test 3.2 {
152  SELECT * FROM sqlite_stat1;
153} {t1 null 4}
154do_test 3.3 {
155  execsql { DELETE FROM sqlite_stat1 }
156  do_then_apply_sql { ANALYZE }
157  execsql { SELECT * FROM sqlite_stat1 } db2
158} {t1 null 4}
159do_test 3.4 {
160  execsql { INSERT INTO t1 VALUES(5,5,5) }
161  do_then_apply_sql { ANALYZE }
162  execsql { SELECT * FROM sqlite_stat1 } db2
163} {t1 null 5}
164do_test 3.5 {
165  do_then_apply_sql { DROP TABLE t1 }
166  execsql { SELECT * FROM sqlite_stat1 } db2
167} {}
168
169do_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} {}
181do_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}
185do_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}
189do_test 3.6.4 { S delete } {}
190
191proc 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
209proc 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
216do_execsql_test 3.7.0 {
217  DELETE FROM sqlite_stat1;
218}
219do_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#-------------------------------------------------------------------------
230catch { db2 close }
231reset_db
232forcedelete test.db2
233sqlite3 db2 test.db2
234
235do_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
246do_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
261do_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
265do_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
273do_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} {}
283do_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
297db2 null null
298do_execsql_test -db db2 4.2.2 {
299  SELECT * FROM sqlite_stat1 ORDER BY 1,2;
300} {t3 null 2}
301
302do_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
310finish_test
311