Debian Codegraph @

ext/intck/intck1.test

raw
1 # 2008 Feb 19
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 # The focus of this file is testing the incremental integrity check
13 # (intck) extension.
14 #
15
16 source [file join [file dirname [info script]] intck_common.tcl]
17 set testprefix intck1
18 return_if_no_intck
19
20 foreach {tn sql} {
21 1 "CREATE TABLE t1(a PRIMARY KEY, b)"
22 2 "CREATE TABLE t2(a PRIMARY KEY, b) WITHOUT ROWID "
23 3 "CREATE TABLE t3(a PRIMARY KEY, b) WITHOUT rowID;"
24 4 "CREATE TABLE t4(a PRIMARY KEY, ROWID)"
25 5 {CREATE TABLE t5(a PRIMARY KEY, ROWID) WITHOUT ROWID
26 }
27 } {
28 do_test 1.1.$tn {
29 db eval $sql
30 set {} {}
31 } {}
32 }
33
34 set space " \n\v\t\r\f"
35
36 do_execsql_test 1.2 {
37 SELECT name, (rtrim(sql, $space) LIKE '%rowid')
38 FROM sqlite_schema WHERE type='table'
39 ORDER BY 1
40 } {
41 t1 0
42 t2 1
43 t3 1
44 t4 0
45 t5 1
46 }
47
48 do_execsql_test 1.3 {
49 CREATE TABLE x1(a COLLATE nocase, b INTEGER, c BLOB);
50 INSERT INTO x1 VALUES('lEtTeRs', 1234, 1234);
51 }
52 do_execsql_test 1.3.1 {
53 WITH wrapper(c1, c2, c3) AS (
54 SELECT a, b, c FROM x1
55 )
56 SELECT * FROM wrapper WHERE c1='letters';
57 } {lEtTeRs 1234 1234}
58 do_execsql_test 1.3.2 {
59 WITH wrapper(c1, c2, c3) AS (
60 SELECT a, b, c FROM x1
61 )
62 SELECT * FROM wrapper WHERE c2='1234';
63 } {lEtTeRs 1234 1234}
64 do_execsql_test 1.3.2 {
65 WITH wrapper(c1, c2, c3) AS (
66 SELECT a, b, c FROM x1
67 )
68 SELECT * FROM wrapper WHERE c3='1234';
69 } {}
70
71 do_execsql_test 1.4 {
72 CREATE TABLE z1(a, b);
73 CREATE INDEX z1ab ON z1(a+b COLLATE nocase);
74 }
75 do_execsql_test 1.4.1 {
76 SELECT * FROM z1 INDEXED BY z1ab
77 }
78
79 do_catchsql_test 1.5.1 {
80 CREATE INDEX z1b ON z1(b ASC NULLS LAST);
81 } {1 {unsupported use of NULLS LAST}}
82 do_catchsql_test 1.5.2 {
83 CREATE INDEX z1b ON z1(b DESC NULLS LAST);
84 } {1 {unsupported use of NULLS LAST}}
85 do_catchsql_test 1.5.3 {
86 CREATE INDEX z1b ON z1(b ASC NULLS FIRST);
87 } {1 {unsupported use of NULLS FIRST}}
88 do_catchsql_test 1.5.4 {
89 CREATE INDEX z1b ON z1(b DESC NULLS FIRST);
90 } {1 {unsupported use of NULLS FIRST}}
91
92
93 reset_db
94 do_execsql_test 1.6.1 {
95 CREATE TABLE t1(i INTEGER PRIMARY KEY, b, c);
96 CREATE INDEX i1 ON t1(b);
97 ANALYZE;
98 INSERT INTO sqlite_stat1 VALUES('t1', 'i1', '10000 10000');
99 ANALYZE sqlite_schema;
100 } {}
101 do_eqp_test 1.6.2 {
102 SELECT 1 FROM t1 INDEXED BY i1 WHERE (b, i) IS (?, ?);
103 } {SEARCH}
104
105
106
107 #-------------------------------------------------------------------------
108 reset_db
109
110 do_test 2.0 {
111 set ic [sqlite3_intck db main]
112 $ic close
113 } {}
114
115 do_execsql_test 2.1 {
116 CREATE TABLE t1(a, b);
117 INSERT INTO t1 VALUES(1, 2);
118 INSERT INTO t1 VALUES(3, 4);
119
120 CREATE INDEX i1 ON t1(a COLLATE nocase);
121 CREATE INDEX i2 ON t1(b, a);
122 CREATE INDEX i3 ON t1(b + a COLLATE nocase) WHERE a!=1;
123 }
124
125 do_intck_test 2.2 {
126 }
127
128 # Delete a row from each of the i1 and i2 indexes using the imposter
129 # table interface.
130 #
131 do_test 2.3 {
132 db eval {SELECT name, rootpage FROM sqlite_schema} {
133 set R($name) $rootpage
134 }
135 sqlite3_test_control SQLITE_TESTCTRL_IMPOSTER db main 1 $R(i1)
136 db eval { CREATE TABLE imp1(a PRIMARY KEY, rowid) WITHOUT ROWID; }
137 sqlite3_test_control SQLITE_TESTCTRL_IMPOSTER db main 1 $R(i2)
138 db eval { CREATE TABLE imp2(b, a, rowid, PRIMARY KEY(b, a)) WITHOUT ROWID; }
139 sqlite3_test_control SQLITE_TESTCTRL_IMPOSTER db main 0 0
140
141 db eval {
142 PRAGMA writable_schema=on;
143 DELETE FROM imp1 WHERE rowid=1;
144 DELETE FROM imp2 WHERE rowid=2;
145 }
146
147 db close
148 sqlite3 db test.db
149 } {}
150
151 do_intck_test 2.4 {
152 {entry (1,1) missing from index i1}
153 {entry (4,3,2) missing from index i2}
154 }
155
156 #-------------------------------------------------------------------------
157 reset_db
158 do_execsql_test 3.0 {
159 CREATE TABLE x1(a, b, c, PRIMARY KEY(c, b)) WITHOUT ROWID;
160 CREATE INDEX x1a ON x1(a COLLATE nocase);
161
162 INSERT INTO x1 VALUES(1, 2, 'three');
163 INSERT INTO x1 VALUES(4, 5, 'six');
164 INSERT INTO x1 VALUES(7, 8, 'nine');
165 }
166
167 do_intck_test 3.1 { }
168
169 do_test 3.2 {
170 db eval {SELECT name, rootpage FROM sqlite_schema} {
171 set R($name) $rootpage
172 }
173 sqlite3_test_control SQLITE_TESTCTRL_IMPOSTER db main 1 $R(x1a)
174 db eval { CREATE TABLE imp1(c, b, a, PRIMARY KEY(c, b)) WITHOUT ROWID }
175 sqlite3_test_control SQLITE_TESTCTRL_IMPOSTER db main 0 0
176
177 db eval {
178 PRAGMA writable_schema=on;
179 DELETE FROM imp1 WHERE a=5;
180 }
181 execsql_pp {
182 }
183
184 db close
185 sqlite3 db test.db
186 } {}
187
188 do_intck_test 3.3 {
189 {entry (4,'six',5) missing from index x1a}
190 }
191
192 #-------------------------------------------------------------------------
193 reset_db
194 do_execsql_test 4.0 {
195 CREATE TABLE www(x, y, z);
196 CREATE INDEX w1 ON www( (x+1), z );
197 INSERT INTO www VALUES(1, 1, 1), (2, 2, 2);
198 }
199
200 do_intck_test 4.1 { }
201
202 #-------------------------------------------------------------------------
203 reset_db
204 do_execsql_test 5.0 {
205 CREATE TABLE t1(a, b);
206 CREATE INDEX i1 ON t1(a COLLATE NOCASE);
207 INSERT INTO t1 VALUES(1, 1);
208 INSERT INTO t1 VALUES(2, 2);
209 }
210
211 do_test 5.1 {
212 set ic [sqlite3_intck db nosuchdb]
213 $ic step
214 } {SQLITE_ERROR}
215
216 do_test 5.2 {
217 $ic close
218 set ic [sqlite3_intck db {}]
219 while {[$ic step]=="SQLITE_OK"} {}
220 set res [$ic error]
221 $ic close
222 set res
223 } {SQLITE_OK {}}
224
225 do_test 5.3 { test_do_intck db "main" } {}
226
227 do_test 5.4 {
228 set ret {}
229 set ic [sqlite3_intck db main]
230 db eval [$ic test_sql t1] {
231 if {$error_message!=""} { lappend ret $error_message }
232 }
233 $ic close
234 set ret
235 } {}
236
237 do_test 5.5 {
238 set ret {}
239 set ic [sqlite3_intck db main]
240 db eval [$ic test_sql {}] {
241 if {$error_message!=""} { lappend ret $error_message }
242 }
243 $ic close
244 set ret
245 } {}
246
247 db cache flush
248
249 do_test 5.6 {
250 set ret {}
251 set ic [sqlite3_intck db main]
252 $ic step
253 db eval [$ic test_sql {}] {
254 if {$error_message!=""} { lappend ret $error_message }
255 }
256 $ic close
257 set ret
258 } {}
259
260 #-------------------------------------------------------------------------
261 reset_db
262
263 do_execsql_test 6.0 {
264 CREATE TABLE t1(x, y, PRIMARY KEY(x)) WITHOUT ROWID;
265 CREATE INDEX i1 ON t1(y, x);
266 INSERT INTO t1 VALUES(X'0000', X'1111');
267 }
268
269 do_intck_test 6.1 {}
270
271 do_execsql_test 6.2.1 {
272 PRAGMA writable_schema = 1;
273 UPDATE sqlite_schema SET sql = 'CREATE INDEX i1' WHERE name='i1';
274 } {}
275 do_intck_test 6.2.2 {}
276
277 do_execsql_test 6.3.1 {
278 UPDATE sqlite_schema SET sql = 'CREATE INDEX i1(y' WHERE name='i1';
279 } {}
280 do_intck_test 6.3.2 {}
281
282 do_execsql_test 6.4.1 {
283 UPDATE sqlite_schema
284 SET sql = 'CREATE INDEX i1(y) hello world'
285 WHERE name='i1';
286 } {}
287 do_intck_test 6.4.2 {}
288
289 do_execsql_test 6.5.1 {
290 UPDATE sqlite_schema
291 SET sql = 'CREATE INDEX i1(y, x) WHERE 1 '
292 WHERE name='i1';
293 } {}
294 do_intck_test 6.5.2 {}
295
296 do_execsql_test 6.6.1 {
297 UPDATE sqlite_schema
298 SET sql = 'CREATE INDEX i1( , ) WHERE 1 '
299 WHERE name='i1';
300 } {}
301
302 do_test 6.7.2 {
303 set ic [sqlite3_intck db main]
304 $ic step
305 } {SQLITE_ERROR}
306 do_test 6.5.3 {
307 $ic error
308 } {SQLITE_ERROR {near "AS": syntax error}}
309 $ic close
310
311 do_execsql_test 6.6.1 {
312 UPDATE sqlite_schema
313 SET sql = 'CREATE INDEX i1([y'
314 WHERE name='i1';
315 } {}
316 do_intck_test 6.6.2 {}
317
318 #-------------------------------------------------------------------------
319 reset_db
320 do_execsql_test 7.0 {
321 CREATE TABLE x1("1", "22", "3333", four);
322 CREATE INDEX i1 ON x1( "1" , "22", NULL);
323 INSERT INTO x1 VALUES(1, 22, 3333, NULL);
324 INSERT INTO x1 VALUES(1, 22, 3333, NULL);
325 }
326 do_execsql_test 7.1 " CREATE INDEX i2 ON x1( \"1\"\r\n\t ) "
327 do_execsql_test 7.2 { CREATE INDEX i3 ON x1( "22" || 'abc''def' || `1` ) }
328 do_execsql_test 7.3 { CREATE INDEX i4 ON x1( [22] + [1] ) }
329 do_execsql_test 7.4 { CREATE INDEX i5 ON x1( four||'hello' ) }
330
331 do_intck_test 7.5 {}
332
333
334 finish_test