wasCSharpSQLite – Blame information for rev 7
?pathlinks?
Rev | Author | Line No. | Line |
---|---|---|---|
1 | office | 1 | # 2006 June 10 |
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 file is creating and dropping virtual tables. |
||
13 | # |
||
14 | # $Id: vtab1.test,v 1.57 2008/08/01 17:51:47 danielk1977 Exp $ |
||
15 | |||
16 | set testdir [file dirname $argv0] |
||
17 | source $testdir/tester.tcl |
||
18 | |||
19 | ifcapable !vtab||!schema_pragmas { |
||
20 | finish_test |
||
21 | return |
||
22 | } |
||
23 | |||
24 | #---------------------------------------------------------------------- |
||
25 | # Organization of tests in this file: |
||
26 | # |
||
27 | # vtab1-1.*: Error conditions and other issues surrounding creation/connection |
||
28 | # of a virtual module. |
||
29 | # vtab1-2.*: Test sqlite3_declare_vtab() and the xConnect/xDisconnect methods. |
||
30 | # vtab1-3.*: Table scans and WHERE clauses. |
||
31 | # vtab1-4.*: Table scans and ORDER BY clauses. |
||
32 | # vtab1-5.*: Test queries that include joins. This brings the |
||
33 | # sqlite3_index_info.estimatedCost variable into play. |
||
34 | # vtab1-6.*: Test UPDATE/INSERT/DELETE on vtables. |
||
35 | # vtab1-7.*: Test sqlite3_last_insert_rowid(). |
||
36 | # |
||
37 | # This file uses the "echo" module (see src/test8.c). Refer to comments |
||
38 | # in that file for the special behaviour of the Tcl $echo_module variable. |
||
39 | # |
||
40 | # TODO: |
||
41 | # * How to test the sqlite3_index_constraint_usage.omit field? |
||
42 | # * vtab1-5.* |
||
43 | # |
||
44 | # vtab1-14.*: Test 'IN' constraints - i.e. "SELECT * FROM t1 WHERE id IN(...)" |
||
45 | # |
||
46 | |||
47 | |||
48 | #---------------------------------------------------------------------- |
||
49 | # Test cases vtab1.1.* |
||
50 | # |
||
51 | |||
52 | # We cannot create a virtual table if the module has not been registered. |
||
53 | # |
||
54 | do_test vtab1-1.1 { |
||
55 | explain { |
||
56 | CREATE VIRTUAL TABLE t1 USING echo; |
||
57 | } |
||
58 | catchsql { |
||
59 | CREATE VIRTUAL TABLE t1 USING echo; |
||
60 | } |
||
61 | } {1 {no such module: echo}} |
||
62 | do_test vtab1-1.2 { |
||
63 | execsql { |
||
64 | SELECT name FROM sqlite_master ORDER BY 1 |
||
65 | } |
||
66 | } {} |
||
67 | |||
68 | # Register the module |
||
69 | register_echo_module [sqlite3_connection_pointer db] |
||
70 | |||
71 | # Once a module has been registered, virtual tables using that module |
||
72 | # may be created. However if a module xCreate() fails to call |
||
73 | # sqlite3_declare_vtab() an error will be raised and the table not created. |
||
74 | # |
||
75 | # The "echo" module does not invoke sqlite3_declare_vtab() if it is |
||
76 | # passed zero arguments. |
||
77 | # |
||
78 | do_test vtab1-1.3 { |
||
79 | catchsql { |
||
80 | CREATE VIRTUAL TABLE t1 USING echo; |
||
81 | } |
||
82 | } {1 {vtable constructor did not declare schema: t1}} |
||
83 | do_test vtab1-1.4 { |
||
84 | execsql { |
||
85 | SELECT name FROM sqlite_master ORDER BY 1 |
||
86 | } |
||
87 | } {} |
||
88 | |||
89 | # The "echo" module xCreate method returns an error and does not create |
||
90 | # the virtual table if it is passed an argument that does not correspond |
||
91 | # to an existing real table in the same database. |
||
92 | # |
||
93 | do_test vtab1-1.5 { |
||
94 | catchsql { |
||
95 | CREATE VIRTUAL TABLE t1 USING echo(no_such_table); |
||
96 | } |
||
97 | } {1 {vtable constructor failed: t1}} |
||
98 | do_test vtab1-1.6 { |
||
99 | execsql { |
||
100 | SELECT name FROM sqlite_master ORDER BY 1 |
||
101 | } |
||
102 | } {} |
||
103 | |||
104 | # Ticket #2156. Using the sqlite3_prepare_v2() API, make sure that |
||
105 | # a CREATE VIRTUAL TABLE statement can be used multiple times. |
||
106 | # |
||
107 | do_test vtab1-1.2152.1 { |
||
108 | set DB [sqlite3_connection_pointer db] |
||
109 | set sql {CREATE VIRTUAL TABLE t2152a USING echo(t2152b)} |
||
110 | set STMT [sqlite3_prepare_v2 $DB $sql -1 TAIL] |
||
111 | sqlite3_step $STMT |
||
112 | } SQLITE_ERROR |
||
113 | do_test vtab-1.2152.2 { |
||
114 | sqlite3_reset $STMT |
||
115 | sqlite3_step $STMT |
||
116 | } SQLITE_ERROR |
||
117 | do_test vtab-1.2152.3 { |
||
118 | sqlite3_reset $STMT |
||
119 | db eval {CREATE TABLE t2152b(x,y)} |
||
120 | sqlite3_step $STMT |
||
121 | } SQLITE_DONE |
||
122 | do_test vtab-1.2152.4 { |
||
123 | sqlite3_finalize $STMT |
||
124 | db eval {DROP TABLE t2152a; DROP TABLE t2152b} |
||
125 | } {} |
||
126 | |||
127 | # Test to make sure nothing goes wrong and no memory is leaked if we |
||
128 | # select an illegal table-name (i.e a reserved name or the name of a |
||
129 | # table that already exists). |
||
130 | # |
||
131 | do_test vtab1-1.7 { |
||
132 | catchsql { |
||
133 | CREATE VIRTUAL TABLE sqlite_master USING echo; |
||
134 | } |
||
135 | } {1 {object name reserved for internal use: sqlite_master}} |
||
136 | do_test vtab1-1.8 { |
||
137 | catchsql { |
||
138 | CREATE TABLE treal(a, b, c); |
||
139 | CREATE VIRTUAL TABLE treal USING echo(treal); |
||
140 | } |
||
141 | } {1 {table treal already exists}} |
||
142 | do_test vtab1-1.9 { |
||
143 | execsql { |
||
144 | DROP TABLE treal; |
||
145 | SELECT name FROM sqlite_master ORDER BY 1 |
||
146 | } |
||
147 | } {} |
||
148 | |||
149 | do_test vtab1-1.10 { |
||
150 | execsql { |
||
151 | CREATE TABLE treal(a, b, c); |
||
152 | CREATE VIRTUAL TABLE techo USING echo(treal); |
||
153 | } |
||
154 | db close |
||
155 | sqlite3 db test.db |
||
156 | catchsql { |
||
157 | SELECT * FROM techo; |
||
158 | } |
||
159 | } {1 {no such module: echo}} |
||
160 | do_test vtab1-1.11 { |
||
161 | catchsql { |
||
162 | INSERT INTO techo VALUES(1, 2, 3); |
||
163 | } |
||
164 | } {1 {no such module: echo}} |
||
165 | do_test vtab1-1.12 { |
||
166 | catchsql { |
||
167 | UPDATE techo SET a = 10; |
||
168 | } |
||
169 | } {1 {no such module: echo}} |
||
170 | do_test vtab1-1.13 { |
||
171 | catchsql { |
||
172 | DELETE FROM techo; |
||
173 | } |
||
174 | } {1 {no such module: echo}} |
||
175 | do_test vtab1-1.14 { |
||
176 | catchsql { |
||
177 | PRAGMA table_info(techo) |
||
178 | } |
||
179 | } {1 {no such module: echo}} |
||
180 | do_test vtab1-1.15 { |
||
181 | catchsql { |
||
182 | DROP TABLE techo; |
||
183 | } |
||
184 | } {1 {no such module: echo}} |
||
185 | |||
186 | register_echo_module [sqlite3_connection_pointer db] |
||
187 | register_echo_module [sqlite3_connection_pointer db] |
||
188 | |||
189 | # Test an error message returned from a v-table constructor. |
||
190 | # |
||
191 | do_test vtab1-1.16 { |
||
192 | execsql { |
||
193 | DROP TABLE techo; |
||
194 | CREATE TABLE logmsg(log); |
||
195 | } |
||
196 | catchsql { |
||
197 | CREATE VIRTUAL TABLE techo USING echo(treal, logmsg); |
||
198 | } |
||
199 | } {1 {table 'logmsg' already exists}} |
||
200 | |||
201 | do_test vtab1-1.17 { |
||
202 | execsql { |
||
203 | DROP TABLE treal; |
||
204 | DROP TABLE logmsg; |
||
205 | SELECT sql FROM sqlite_master; |
||
206 | } |
||
207 | } {} |
||
208 | |||
209 | #---------------------------------------------------------------------- |
||
210 | # Test cases vtab1.2.* |
||
211 | # |
||
212 | # At this point, the database is completely empty. The echo module |
||
213 | # has already been registered. |
||
214 | |||
215 | # If a single argument is passed to the echo module during table |
||
216 | # creation, it is assumed to be the name of a table in the same |
||
217 | # database. The echo module attempts to set the schema of the |
||
218 | # new virtual table to be the same as the existing database table. |
||
219 | # |
||
220 | do_test vtab1-2.1 { |
||
221 | execsql { |
||
222 | CREATE TABLE template(a, b, c); |
||
223 | } |
||
224 | execsql { PRAGMA table_info(template); } |
||
225 | } [list \ |
||
226 | |||
227 | 1 b {} 0 {} 0 \ |
||
228 | 2 c {} 0 {} 0 \ |
||
229 | ] |
||
230 | do_test vtab1-2.2 { |
||
231 | execsql { |
||
232 | CREATE VIRTUAL TABLE t1 USING echo(template); |
||
233 | } |
||
234 | execsql { PRAGMA table_info(t1); } |
||
235 | } [list \ |
||
236 | |||
237 | 1 b {} 0 {} 0 \ |
||
238 | 2 c {} 0 {} 0 \ |
||
239 | ] |
||
240 | |||
241 | # Test that the database can be unloaded. This should invoke the xDisconnect() |
||
242 | # callback for the successfully create virtual table (t1). |
||
243 | # |
||
244 | do_test vtab1-2.3 { |
||
245 | set echo_module [list] |
||
246 | db close |
||
247 | set echo_module |
||
248 | } [list xDisconnect] |
||
249 | |||
250 | # Re-open the database. This should not cause any virtual methods to |
||
251 | # be called. The invocation of xConnect() is delayed until the virtual |
||
252 | # table schema is first required by the compiler. |
||
253 | # |
||
254 | do_test vtab1-2.4 { |
||
255 | set echo_module [list] |
||
256 | sqlite3 db test.db |
||
257 | db cache size 0 |
||
258 | set echo_module |
||
259 | } {} |
||
260 | |||
261 | # Try to query the virtual table schema. This should fail, as the |
||
262 | # echo module has not been registered with this database connection. |
||
263 | # |
||
264 | do_test vtab1.2.6 { |
||
265 | catchsql { PRAGMA table_info(t1); } |
||
266 | } {1 {no such module: echo}} |
||
267 | |||
268 | # Register the module |
||
269 | register_echo_module [sqlite3_connection_pointer db] |
||
270 | |||
271 | # Try to query the virtual table schema again. This time it should |
||
272 | # invoke the xConnect method and succeed. |
||
273 | # |
||
274 | do_test vtab1.2.7 { |
||
275 | execsql { PRAGMA table_info(t1); } |
||
276 | } [list \ |
||
277 | |||
278 | 1 b {} 0 {} 0 \ |
||
279 | 2 c {} 0 {} 0 \ |
||
280 | ] |
||
281 | do_test vtab1.2.8 { |
||
282 | set echo_module |
||
283 | } {xConnect echo main t1 template} |
||
284 | |||
285 | # Drop table t1. This should cause the xDestroy (but not xDisconnect) method |
||
286 | # to be invoked. |
||
287 | do_test vtab1-2.5 { |
||
288 | set echo_module "" |
||
289 | execsql { |
||
290 | DROP TABLE t1; |
||
291 | } |
||
292 | set echo_module |
||
293 | } {xDestroy} |
||
294 | |||
295 | do_test vtab1-2.6 { |
||
296 | execsql { |
||
297 | PRAGMA table_info(t1); |
||
298 | } |
||
299 | } {} |
||
300 | do_test vtab1-2.7 { |
||
301 | execsql { |
||
302 | SELECT sql FROM sqlite_master; |
||
303 | } |
||
304 | } [list {CREATE TABLE template(a, b, c)}] |
||
305 | # Clean up other test artifacts: |
||
306 | do_test vtab1-2.8 { |
||
307 | execsql { |
||
308 | DROP TABLE template; |
||
309 | SELECT sql FROM sqlite_master; |
||
310 | } |
||
311 | } [list] |
||
312 | |||
313 | #---------------------------------------------------------------------- |
||
314 | # Test case vtab1-3 test table scans and the echo module's |
||
315 | # xBestIndex/xFilter handling of WHERE conditions. |
||
316 | |||
317 | do_test vtab1-3.1 { |
||
318 | set echo_module "" |
||
319 | execsql { |
||
320 | CREATE TABLE treal(a INTEGER, b INTEGER, c); |
||
321 | CREATE INDEX treal_idx ON treal(b); |
||
322 | CREATE VIRTUAL TABLE t1 USING echo(treal); |
||
323 | } |
||
324 | set echo_module |
||
325 | } [list xCreate echo main t1 treal \ |
||
326 | xSync echo(treal) \ |
||
327 | xCommit echo(treal) \ |
||
328 | ] |
||
329 | |||
330 | # Test that a SELECT on t1 doesn't crash. No rows are returned |
||
331 | # because the underlying real table is currently empty. |
||
332 | # |
||
333 | do_test vtab1-3.2 { |
||
334 | execsql { |
||
335 | SELECT a, b, c FROM t1; |
||
336 | } |
||
337 | } {} |
||
338 | |||
339 | # Put some data into the table treal. Then try a few simple SELECT |
||
340 | # statements on t1. |
||
341 | # |
||
342 | do_test vtab1-3.3 { |
||
343 | execsql { |
||
344 | INSERT INTO treal VALUES(1, 2, 3); |
||
345 | INSERT INTO treal VALUES(4, 5, 6); |
||
346 | SELECT * FROM t1; |
||
347 | } |
||
348 | } {1 2 3 4 5 6} |
||
349 | do_test vtab1-3.4 { |
||
350 | execsql { |
||
351 | SELECT a FROM t1; |
||
352 | } |
||
353 | } {1 4} |
||
354 | do_test vtab1-3.5 { |
||
355 | execsql { |
||
356 | SELECT rowid FROM t1; |
||
357 | } |
||
358 | } {1 2} |
||
359 | do_test vtab1-3.6 { |
||
360 | set echo_module "" |
||
361 | execsql { |
||
362 | SELECT * FROM t1; |
||
363 | } |
||
364 | } {1 2 3 4 5 6} |
||
365 | do_test vtab1-3.7 { |
||
366 | execsql { |
||
367 | SELECT rowid, * FROM t1; |
||
368 | } |
||
369 | } {1 1 2 3 2 4 5 6} |
||
370 | do_test vtab1-3.8 { |
||
371 | execsql { |
||
372 | SELECT a AS d, b AS e, c AS f FROM t1; |
||
373 | } |
||
374 | } {1 2 3 4 5 6} |
||
375 | |||
376 | # Execute some SELECT statements with WHERE clauses on the t1 table. |
||
377 | # Then check the echo_module variable (written to by the module methods |
||
378 | # in test8.c) to make sure the xBestIndex() and xFilter() methods were |
||
379 | # called correctly. |
||
380 | # |
||
381 | do_test vtab1-3.8 { |
||
382 | set echo_module "" |
||
383 | execsql { |
||
384 | SELECT * FROM t1; |
||
385 | } |
||
386 | set echo_module |
||
387 | } [list xBestIndex {SELECT rowid, * FROM 'treal'} \ |
||
388 | xFilter {SELECT rowid, * FROM 'treal'} ] |
||
389 | do_test vtab1-3.9 { |
||
390 | set echo_module "" |
||
391 | execsql { |
||
392 | SELECT * FROM t1 WHERE b = 5; |
||
393 | } |
||
394 | } {4 5 6} |
||
395 | do_test vtab1-3.10 { |
||
396 | set echo_module |
||
397 | } [list xBestIndex {SELECT rowid, * FROM 'treal' WHERE b = ?} \ |
||
398 | xFilter {SELECT rowid, * FROM 'treal' WHERE b = ?} 5 ] |
||
399 | do_test vtab1-3.10 { |
||
400 | set echo_module "" |
||
401 | execsql { |
||
402 | SELECT * FROM t1 WHERE b >= 5 AND b <= 10; |
||
403 | } |
||
404 | } {4 5 6} |
||
405 | do_test vtab1-3.11 { |
||
406 | set echo_module |
||
407 | } [list xBestIndex {SELECT rowid, * FROM 'treal' WHERE b >= ? AND b <= ?} \ |
||
408 | xFilter {SELECT rowid, * FROM 'treal' WHERE b >= ? AND b <= ?} 5 10 ] |
||
409 | do_test vtab1-3.12 { |
||
410 | set echo_module "" |
||
411 | execsql { |
||
412 | SELECT * FROM t1 WHERE b BETWEEN 2 AND 10; |
||
413 | } |
||
414 | } {1 2 3 4 5 6} |
||
415 | do_test vtab1-3.13 { |
||
416 | set echo_module |
||
417 | } [list xBestIndex {SELECT rowid, * FROM 'treal' WHERE b >= ? AND b <= ?} \ |
||
418 | xFilter {SELECT rowid, * FROM 'treal' WHERE b >= ? AND b <= ?} 2 10 ] |
||
419 | |||
420 | # Add a function for the MATCH operator. Everything always matches! |
||
421 | #proc test_match {lhs rhs} { |
||
422 | # lappend ::echo_module MATCH $lhs $rhs |
||
423 | # return 1 |
||
424 | #} |
||
425 | #db function match test_match |
||
426 | |||
427 | set echo_module "" |
||
428 | do_test vtab1-3.12 { |
||
429 | set echo_module "" |
||
430 | catchsql { |
||
431 | SELECT * FROM t1 WHERE a MATCH 'string'; |
||
432 | } |
||
433 | } {1 {unable to use function MATCH in the requested context}} |
||
434 | do_test vtab1-3.13 { |
||
435 | set echo_module |
||
436 | } [list xBestIndex {SELECT rowid, * FROM 'treal'} \ |
||
437 | xFilter {SELECT rowid, * FROM 'treal'}] |
||
438 | ifcapable subquery { |
||
439 | # The echo module uses a subquery internally to implement the MATCH operator. |
||
440 | do_test vtab1-3.14 { |
||
441 | set echo_module "" |
||
442 | execsql { |
||
443 | SELECT * FROM t1 WHERE b MATCH 'string'; |
||
444 | } |
||
445 | } {} |
||
446 | do_test vtab1-3.15 { |
||
447 | set echo_module |
||
448 | } [list xBestIndex \ |
||
449 | {SELECT rowid, * FROM 'treal' WHERE b LIKE (SELECT '%'||?||'%')} \ |
||
450 | xFilter \ |
||
451 | {SELECT rowid, * FROM 'treal' WHERE b LIKE (SELECT '%'||?||'%')} \ |
||
452 | string ] |
||
453 | }; #ifcapable subquery |
||
454 | |||
455 | #---------------------------------------------------------------------- |
||
456 | # Test case vtab1-3 test table scans and the echo module's |
||
457 | # xBestIndex/xFilter handling of ORDER BY clauses. |
||
458 | |||
459 | # This procedure executes the SQL. Then it checks to see if the OP_Sort |
||
460 | # opcode was executed. If an OP_Sort did occur, then "sort" is appended |
||
461 | # to the result. If no OP_Sort happened, then "nosort" is appended. |
||
462 | # |
||
463 | # This procedure is used to check to make sure sorting is or is not |
||
464 | # occurring as expected. |
||
465 | # |
||
466 | proc cksort {sql} { |
||
467 | set ::sqlite_sort_count 0 |
||
468 | set data [execsql $sql] |
||
469 | if {$::sqlite_sort_count} {set x sort} {set x nosort} |
||
470 | lappend data $x |
||
471 | return $data |
||
472 | } |
||
473 | |||
474 | do_test vtab1-4.1 { |
||
475 | set echo_module "" |
||
476 | cksort { |
||
477 | SELECT b FROM t1 ORDER BY b; |
||
478 | } |
||
479 | } {2 5 nosort} |
||
480 | do_test vtab1-4.2 { |
||
481 | set echo_module |
||
482 | } [list xBestIndex {SELECT rowid, * FROM 'treal' ORDER BY b ASC} \ |
||
483 | xFilter {SELECT rowid, * FROM 'treal' ORDER BY b ASC} ] |
||
484 | do_test vtab1-4.3 { |
||
485 | set echo_module "" |
||
486 | cksort { |
||
487 | SELECT b FROM t1 ORDER BY b DESC; |
||
488 | } |
||
489 | } {5 2 nosort} |
||
490 | do_test vtab1-4.4 { |
||
491 | set echo_module |
||
492 | } [list xBestIndex {SELECT rowid, * FROM 'treal' ORDER BY b DESC} \ |
||
493 | xFilter {SELECT rowid, * FROM 'treal' ORDER BY b DESC} ] |
||
494 | do_test vtab1-4.3 { |
||
495 | set echo_module "" |
||
496 | cksort { |
||
497 | SELECT b FROM t1 ORDER BY b||''; |
||
498 | } |
||
499 | } {2 5 sort} |
||
500 | do_test vtab1-4.4 { |
||
501 | set echo_module |
||
502 | } [list xBestIndex {SELECT rowid, * FROM 'treal'} \ |
||
503 | xFilter {SELECT rowid, * FROM 'treal'} ] |
||
504 | |||
505 | execsql { |
||
506 | DROP TABLE t1; |
||
507 | DROP TABLE treal; |
||
508 | } |
||
509 | |||
510 | #---------------------------------------------------------------------- |
||
511 | # Test cases vtab1-5 test SELECT queries that include joins on virtual |
||
512 | # tables. |
||
513 | |||
514 | proc filter {log} { |
||
515 | set out [list] |
||
516 | for {set ii 0} {$ii < [llength $log]} {incr ii} { |
||
517 | if {[lindex $log $ii] eq "xFilter"} { |
||
518 | lappend out xFilter |
||
519 | lappend out [lindex $log [expr $ii+1]] |
||
520 | } |
||
521 | } |
||
522 | return $out |
||
523 | } |
||
524 | |||
525 | do_test vtab1-5-1 { |
||
526 | execsql { |
||
527 | CREATE TABLE t1(a, b, c); |
||
528 | CREATE TABLE t2(d, e, f); |
||
529 | INSERT INTO t1 VALUES(1, 'red', 'green'); |
||
530 | INSERT INTO t1 VALUES(2, 'blue', 'black'); |
||
531 | INSERT INTO t2 VALUES(1, 'spades', 'clubs'); |
||
532 | INSERT INTO t2 VALUES(2, 'hearts', 'diamonds'); |
||
533 | CREATE VIRTUAL TABLE et1 USING echo(t1); |
||
534 | CREATE VIRTUAL TABLE et2 USING echo(t2); |
||
535 | } |
||
536 | } {} |
||
537 | |||
538 | do_test vtab1-5-2 { |
||
539 | set echo_module "" |
||
540 | execsql { |
||
541 | SELECT * FROM et1, et2; |
||
542 | } |
||
543 | } [list \ |
||
544 | 1 red green 1 spades clubs \ |
||
545 | 1 red green 2 hearts diamonds \ |
||
546 | 2 blue black 1 spades clubs \ |
||
547 | 2 blue black 2 hearts diamonds \ |
||
548 | ] |
||
549 | do_test vtab1-5-3 { |
||
550 | filter $echo_module |
||
551 | } [list \ |
||
552 | xFilter {SELECT rowid, * FROM 't1'} \ |
||
553 | xFilter {SELECT rowid, * FROM 't2'} \ |
||
554 | xFilter {SELECT rowid, * FROM 't2'} \ |
||
555 | ] |
||
556 | do_test vtab1-5-4 { |
||
557 | set echo_module "" |
||
558 | execsql { |
||
559 | SELECT * FROM et1, et2 WHERE et2.d = 2; |
||
560 | } |
||
561 | } [list \ |
||
562 | 1 red green 2 hearts diamonds \ |
||
563 | 2 blue black 2 hearts diamonds \ |
||
564 | ] |
||
565 | do_test vtab1-5-5 { |
||
566 | filter $echo_module |
||
567 | } [list \ |
||
568 | xFilter {SELECT rowid, * FROM 't1'} \ |
||
569 | xFilter {SELECT rowid, * FROM 't2'} \ |
||
570 | xFilter {SELECT rowid, * FROM 't2'} \ |
||
571 | ] |
||
572 | do_test vtab1-5-6 { |
||
573 | execsql { |
||
574 | CREATE INDEX i1 ON t2(d); |
||
575 | } |
||
576 | |||
577 | db close |
||
578 | sqlite3 db test.db |
||
579 | register_echo_module [sqlite3_connection_pointer db] |
||
580 | |||
581 | set ::echo_module "" |
||
582 | execsql { |
||
583 | SELECT * FROM et1, et2 WHERE et2.d = 2; |
||
584 | } |
||
585 | } [list \ |
||
586 | 1 red green 2 hearts diamonds \ |
||
587 | 2 blue black 2 hearts diamonds \ |
||
588 | ] |
||
589 | do_test vtab1-5-7 { |
||
590 | filter $::echo_module |
||
591 | } [list \ |
||
592 | xFilter {SELECT rowid, * FROM 't2' WHERE d = ?} \ |
||
593 | xFilter {SELECT rowid, * FROM 't1'} \ |
||
594 | ] |
||
595 | |||
596 | execsql { |
||
597 | DROP TABLE t1; |
||
598 | DROP TABLE t2; |
||
599 | DROP TABLE et1; |
||
600 | DROP TABLE et2; |
||
601 | } |
||
602 | |||
603 | #---------------------------------------------------------------------- |
||
604 | # Test cases vtab1-6 test INSERT, UPDATE and DELETE operations |
||
605 | # on virtual tables. |
||
606 | do_test vtab1-6-1 { |
||
607 | execsql { SELECT sql FROM sqlite_master } |
||
608 | } {} |
||
609 | do_test vtab1-6-2 { |
||
610 | execsql { |
||
611 | CREATE TABLE treal(a PRIMARY KEY, b, c); |
||
612 | CREATE VIRTUAL TABLE techo USING echo(treal); |
||
613 | SELECT name FROM sqlite_master WHERE type = 'table'; |
||
614 | } |
||
615 | } {treal techo} |
||
616 | do_test vtab1-6-3.1.1 { |
||
617 | execsql { |
||
618 | PRAGMA count_changes=ON; |
||
619 | INSERT INTO techo VALUES(1, 2, 3); |
||
620 | } |
||
621 | } {1} |
||
622 | do_test vtab1-6-3.1.2 { |
||
623 | db changes |
||
624 | } {1} |
||
625 | do_test vtab1-6-3.2 { |
||
626 | execsql { |
||
627 | SELECT * FROM techo; |
||
628 | } |
||
629 | } {1 2 3} |
||
630 | do_test vtab1-6-4.1 { |
||
631 | execsql { |
||
632 | UPDATE techo SET a = 5; |
||
633 | } |
||
634 | db changes |
||
635 | } {1} |
||
636 | do_test vtab1-6-4.2 { |
||
637 | execsql { |
||
638 | SELECT * FROM techo; |
||
639 | } |
||
640 | } {5 2 3} |
||
641 | do_test vtab1-6-4.3 { |
||
642 | execsql { |
||
643 | UPDATE techo SET a=6 WHERE a<0; |
||
644 | } |
||
645 | db changes |
||
646 | } {0} |
||
647 | do_test vtab1-6-4.4 { |
||
648 | execsql { |
||
649 | SELECT * FROM techo; |
||
650 | } |
||
651 | } {5 2 3} |
||
652 | |||
653 | do_test vtab1-6-5.1 { |
||
654 | execsql { |
||
655 | UPDATE techo set a = a||b||c; |
||
656 | } |
||
657 | db changes |
||
658 | } {1} |
||
659 | do_test vtab1-6-5.2 { |
||
660 | execsql { |
||
661 | SELECT * FROM techo; |
||
662 | } |
||
663 | } {523 2 3} |
||
664 | |||
665 | do_test vtab1-6-6.1 { |
||
666 | execsql { |
||
667 | UPDATE techo set rowid = 10; |
||
668 | } |
||
669 | db changes |
||
670 | } {1} |
||
671 | do_test vtab1-6-6.2 { |
||
672 | execsql { |
||
673 | SELECT rowid FROM techo; |
||
674 | } |
||
675 | } {10} |
||
676 | |||
677 | do_test vtab1-6-7.1.1 { |
||
678 | execsql { |
||
679 | INSERT INTO techo VALUES(11,12,13); |
||
680 | } |
||
681 | } {1} |
||
682 | do_test vtab1-6-7.1.2 { |
||
683 | db changes |
||
684 | } {1} |
||
685 | do_test vtab1-6-7.2 { |
||
686 | execsql { |
||
687 | SELECT * FROM techo ORDER BY a; |
||
688 | } |
||
689 | } {11 12 13 523 2 3} |
||
690 | do_test vtab1-6-7.3 { |
||
691 | execsql { |
||
692 | UPDATE techo SET b=b+1000 |
||
693 | } |
||
694 | db changes |
||
695 | } {2} |
||
696 | do_test vtab1-6-7.4 { |
||
697 | execsql { |
||
698 | SELECT * FROM techo ORDER BY a; |
||
699 | } |
||
700 | } {11 1012 13 523 1002 3} |
||
701 | |||
702 | |||
703 | do_test vtab1-6-8.1 { |
||
704 | execsql { |
||
705 | DELETE FROM techo WHERE a=5; |
||
706 | } |
||
707 | db changes |
||
708 | } {0} |
||
709 | do_test vtab1-6-8.2 { |
||
710 | execsql { |
||
711 | SELECT * FROM techo ORDER BY a; |
||
712 | } |
||
713 | } {11 1012 13 523 1002 3} |
||
714 | do_test vtab1-6-8.3 { |
||
715 | execsql { |
||
716 | DELETE FROM techo; |
||
717 | } |
||
718 | db changes |
||
719 | } {2} |
||
720 | do_test vtab1-6-8.4 { |
||
721 | execsql { |
||
722 | SELECT * FROM techo ORDER BY a; |
||
723 | } |
||
724 | } {} |
||
725 | execsql {PRAGMA count_changes=OFF} |
||
726 | |||
727 | file delete -force test2.db |
||
728 | file delete -force test2.db-journal |
||
729 | sqlite3 db2 test2.db |
||
730 | execsql { |
||
731 | CREATE TABLE techo(a PRIMARY KEY, b, c); |
||
732 | } db2 |
||
733 | proc check_echo_table {tn} { |
||
734 | set ::data1 [execsql {SELECT rowid, * FROM techo}] |
||
735 | set ::data2 [execsql {SELECT rowid, * FROM techo} db2] |
||
736 | do_test $tn { |
||
737 | string equal $::data1 $::data2 |
||
738 | } 1 |
||
739 | } |
||
740 | set tn 0 |
||
741 | foreach stmt [list \ |
||
742 | {INSERT INTO techo VALUES('abc', 'def', 'ghi')} \ |
||
743 | {INSERT INTO techo SELECT a||'.'||rowid, b, c FROM techo} \ |
||
744 | {INSERT INTO techo SELECT a||'x'||rowid, b, c FROM techo} \ |
||
745 | {INSERT INTO techo SELECT a||'y'||rowid, b, c FROM techo} \ |
||
746 | {DELETE FROM techo WHERE (oid % 3) = 0} \ |
||
747 | {UPDATE techo set rowid = 100 WHERE rowid = 1} \ |
||
748 | {INSERT INTO techo(a, b) VALUES('hello', 'world')} \ |
||
749 | {DELETE FROM techo} \ |
||
750 | ] { |
||
751 | execsql $stmt |
||
752 | execsql $stmt db2 |
||
753 | check_echo_table vtab1-6.8.[incr tn] |
||
754 | } |
||
755 | |||
756 | db2 close |
||
757 | |||
758 | |||
759 | |||
760 | #---------------------------------------------------------------------- |
||
761 | # Test cases vtab1-7 tests that the value returned by |
||
762 | # sqlite3_last_insert_rowid() is set correctly when rows are inserted |
||
763 | # into virtual tables. |
||
764 | do_test vtab1.7-1 { |
||
765 | execsql { |
||
766 | CREATE TABLE real_abc(a PRIMARY KEY, b, c); |
||
767 | CREATE VIRTUAL TABLE echo_abc USING echo(real_abc); |
||
768 | } |
||
769 | } {} |
||
770 | do_test vtab1.7-2 { |
||
771 | execsql { |
||
772 | INSERT INTO echo_abc VALUES(1, 2, 3); |
||
773 | SELECT last_insert_rowid(); |
||
774 | } |
||
775 | } {1} |
||
776 | do_test vtab1.7-3 { |
||
777 | execsql { |
||
778 | INSERT INTO echo_abc(rowid) VALUES(31427); |
||
779 | SELECT last_insert_rowid(); |
||
780 | } |
||
781 | } {31427} |
||
782 | do_test vtab1.7-4 { |
||
783 | execsql { |
||
784 | INSERT INTO echo_abc SELECT a||'.v2', b, c FROM echo_abc; |
||
785 | SELECT last_insert_rowid(); |
||
786 | } |
||
787 | } {31429} |
||
788 | do_test vtab1.7-5 { |
||
789 | execsql { |
||
790 | SELECT rowid, a, b, c FROM echo_abc |
||
791 | } |
||
792 | } [list 1 1 2 3 \ |
||
793 | 31427 {} {} {} \ |
||
794 | 31428 1.v2 2 3 \ |
||
795 | 31429 {} {} {} \ |
||
796 | ] |
||
797 | |||
798 | # Now test that DELETE and UPDATE operations do not modify the value. |
||
799 | do_test vtab1.7-6 { |
||
800 | execsql { |
||
801 | UPDATE echo_abc SET c = 5 WHERE b = 2; |
||
802 | SELECT last_insert_rowid(); |
||
803 | } |
||
804 | } {31429} |
||
805 | do_test vtab1.7-7 { |
||
806 | execsql { |
||
807 | UPDATE echo_abc SET rowid = 5 WHERE rowid = 1; |
||
808 | SELECT last_insert_rowid(); |
||
809 | } |
||
810 | } {31429} |
||
811 | do_test vtab1.7-8 { |
||
812 | execsql { |
||
813 | DELETE FROM echo_abc WHERE b = 2; |
||
814 | SELECT last_insert_rowid(); |
||
815 | } |
||
816 | } {31429} |
||
817 | do_test vtab1.7-9 { |
||
818 | execsql { |
||
819 | SELECT rowid, a, b, c FROM echo_abc |
||
820 | } |
||
821 | } [list 31427 {} {} {} \ |
||
822 | 31429 {} {} {} \ |
||
823 | ] |
||
824 | do_test vtab1.7-10 { |
||
825 | execsql { |
||
826 | DELETE FROM echo_abc WHERE b = 2; |
||
827 | SELECT last_insert_rowid(); |
||
828 | } |
||
829 | } {31429} |
||
830 | do_test vtab1.7-11 { |
||
831 | execsql { |
||
832 | SELECT rowid, a, b, c FROM real_abc |
||
833 | } |
||
834 | } [list 31427 {} {} {} \ |
||
835 | 31429 {} {} {} \ |
||
836 | ] |
||
837 | do_test vtab1.7-12 { |
||
838 | execsql { |
||
839 | DELETE FROM echo_abc; |
||
840 | SELECT last_insert_rowid(); |
||
841 | } |
||
842 | } {31429} |
||
843 | do_test vtab1.7-13 { |
||
844 | execsql { |
||
845 | SELECT rowid, a, b, c FROM real_abc |
||
846 | } |
||
847 | } {} |
||
848 | |||
849 | ifcapable attach { |
||
850 | do_test vtab1.8-1 { |
||
851 | set echo_module "" |
||
852 | execsql { |
||
853 | ATTACH 'test2.db' AS aux; |
||
854 | CREATE VIRTUAL TABLE aux.e2 USING echo(real_abc); |
||
855 | } |
||
856 | set echo_module |
||
857 | } [list xCreate echo aux e2 real_abc \ |
||
858 | xSync echo(real_abc) \ |
||
859 | xCommit echo(real_abc) \ |
||
860 | ] |
||
861 | } |
||
862 | do_test vtab1.8-2 { |
||
863 | catchsql { |
||
864 | DROP TABLE aux.e2; |
||
865 | } |
||
866 | execsql { |
||
867 | DROP TABLE treal; |
||
868 | DROP TABLE techo; |
||
869 | DROP TABLE echo_abc; |
||
870 | DROP TABLE real_abc; |
||
871 | } |
||
872 | } {} |
||
873 | |||
874 | do_test vtab1.9-1 { |
||
875 | set echo_module "" |
||
876 | execsql { |
||
877 | CREATE TABLE r(a, b, c); |
||
878 | CREATE VIRTUAL TABLE e USING echo(r, e_log); |
||
879 | SELECT name FROM sqlite_master; |
||
880 | } |
||
881 | } {r e e_log} |
||
882 | do_test vtab1.9-2 { |
||
883 | execsql { |
||
884 | DROP TABLE e; |
||
885 | SELECT name FROM sqlite_master; |
||
886 | } |
||
887 | } {r} |
||
888 | |||
889 | do_test vtab1.9-3 { |
||
890 | set echo_module "" |
||
891 | execsql { |
||
892 | CREATE VIRTUAL TABLE e USING echo(r, e_log, virtual 1 2 3 varchar(32)); |
||
893 | } |
||
894 | set echo_module |
||
895 | } [list \ |
||
896 | xCreate echo main e r e_log {virtual 1 2 3 varchar(32)} \ |
||
897 | xSync echo(r) \ |
||
898 | xCommit echo(r) \ |
||
899 | ] |
||
900 | |||
901 | do_test vtab1.10-1 { |
||
902 | execsql { |
||
903 | CREATE TABLE del(d); |
||
904 | CREATE VIRTUAL TABLE e2 USING echo(del); |
||
905 | } |
||
906 | db close |
||
907 | sqlite3 db test.db |
||
908 | register_echo_module [sqlite3_connection_pointer db] |
||
909 | execsql { |
||
910 | DROP TABLE del; |
||
911 | } |
||
912 | catchsql { |
||
913 | SELECT * FROM e2; |
||
914 | } |
||
915 | } {1 {vtable constructor failed: e2}} |
||
916 | do_test vtab1.10-2 { |
||
917 | set rc [catch { |
||
918 | set ptr [sqlite3_connection_pointer db] |
||
919 | sqlite3_declare_vtab $ptr {CREATE TABLE abc(a, b, c)} |
||
920 | } msg] |
||
921 | list $rc $msg |
||
922 | } {1 {library routine called out of sequence}} |
||
923 | do_test vtab1.10-3 { |
||
924 | set ::echo_module_begin_fail r |
||
925 | catchsql { |
||
926 | INSERT INTO e VALUES(1, 2, 3); |
||
927 | } |
||
928 | } {1 {SQL logic error or missing database}} |
||
929 | do_test vtab1.10-4 { |
||
930 | catch {execsql { |
||
931 | EXPLAIN SELECT * FROM e WHERE rowid = 2; |
||
932 | EXPLAIN QUERY PLAN SELECT * FROM e WHERE rowid = 2 ORDER BY rowid; |
||
933 | }} |
||
934 | } {0} |
||
935 | |||
936 | do_test vtab1.10-5 { |
||
937 | set echo_module "" |
||
938 | execsql { |
||
939 | SELECT * FROM e WHERE rowid||'' MATCH 'pattern'; |
||
940 | } |
||
941 | set echo_module |
||
942 | } [list \ |
||
943 | xBestIndex {SELECT rowid, * FROM 'r'} \ |
||
944 | xFilter {SELECT rowid, * FROM 'r'} \ |
||
945 | ] |
||
946 | proc match_func {args} {return ""} |
||
947 | do_test vtab1.10-6 { |
||
948 | set echo_module "" |
||
949 | db function match match_func |
||
950 | execsql { |
||
951 | SELECT * FROM e WHERE match('pattern', rowid, 'pattern2'); |
||
952 | } |
||
953 | set echo_module |
||
954 | } [list \ |
||
955 | xBestIndex {SELECT rowid, * FROM 'r'} \ |
||
956 | xFilter {SELECT rowid, * FROM 'r'} \ |
||
957 | ] |
||
958 | |||
959 | |||
960 | # Testing the xFindFunction interface |
||
961 | # |
||
962 | catch {rename ::echo_glob_overload {}} |
||
963 | do_test vtab1.11-1 { |
||
964 | execsql { |
||
965 | INSERT INTO r(a,b,c) VALUES(1,'?',99); |
||
966 | INSERT INTO r(a,b,c) VALUES(2,3,99); |
||
967 | SELECT a GLOB b FROM e |
||
968 | } |
||
969 | } {1 0} |
||
970 | proc ::echo_glob_overload {a b} { |
||
971 | return [list $b $a] |
||
972 | } |
||
973 | do_test vtab1.11-2 { |
||
974 | execsql { |
||
975 | SELECT a like 'b' FROM e |
||
976 | } |
||
977 | } {0 0} |
||
978 | do_test vtab1.11-3 { |
||
979 | execsql { |
||
980 | SELECT a glob '2' FROM e |
||
981 | } |
||
982 | } {{1 2} {2 2}} |
||
983 | do_test vtab1.11-4 { |
||
984 | execsql { |
||
985 | SELECT glob('2',a) FROM e |
||
986 | } |
||
987 | } {0 1} |
||
988 | do_test vtab1.11-5 { |
||
989 | execsql { |
||
990 | SELECT glob(a,'2') FROM e |
||
991 | } |
||
992 | } {{2 1} {2 2}} |
||
993 | |||
994 | #---------------------------------------------------------------------- |
||
995 | # Test the outcome if a constraint is encountered half-way through |
||
996 | # a multi-row INSERT that is inside a transaction |
||
997 | # |
||
998 | do_test vtab1.12-1 { |
||
999 | execsql { |
||
1000 | CREATE TABLE b(a, b, c); |
||
1001 | CREATE TABLE c(a UNIQUE, b, c); |
||
1002 | INSERT INTO b VALUES(1, 'A', 'B'); |
||
1003 | INSERT INTO b VALUES(2, 'C', 'D'); |
||
1004 | INSERT INTO b VALUES(3, 'E', 'F'); |
||
1005 | INSERT INTO c VALUES(3, 'G', 'H'); |
||
1006 | CREATE VIRTUAL TABLE echo_c USING echo(c); |
||
1007 | } |
||
1008 | } {} |
||
1009 | |||
1010 | # First test outside of a transaction. |
||
1011 | do_test vtab1.12-2 { |
||
1012 | catchsql { INSERT INTO echo_c SELECT * FROM b; } |
||
1013 | } {1 {echo-vtab-error: column a is not unique}} |
||
1014 | do_test vtab1.12-2.1 { |
||
1015 | sqlite3_errmsg db |
||
1016 | } {echo-vtab-error: column a is not unique} |
||
1017 | do_test vtab1.12-3 { |
||
1018 | execsql { SELECT * FROM c } |
||
1019 | } {3 G H} |
||
1020 | |||
1021 | # Now the real test - wrapped in a transaction. |
||
1022 | do_test vtab1.12-4 { |
||
1023 | execsql {BEGIN} |
||
1024 | catchsql { INSERT INTO echo_c SELECT * FROM b; } |
||
1025 | } {1 {echo-vtab-error: column a is not unique}} |
||
1026 | do_test vtab1.12-5 { |
||
1027 | execsql { SELECT * FROM c } |
||
1028 | } {3 G H} |
||
1029 | do_test vtab1.12-6 { |
||
1030 | execsql { COMMIT } |
||
1031 | execsql { SELECT * FROM c } |
||
1032 | } {3 G H} |
||
1033 | |||
1034 | # At one point (ticket #2759), a WHERE clause of the form "<column> IS NULL" |
||
1035 | # on a virtual table was causing an assert() to fail in the compiler. |
||
1036 | # |
||
1037 | # "IS NULL" clauses should not be passed through to the virtual table |
||
1038 | # implementation. They are handled by SQLite after the vtab returns its |
||
1039 | # data. |
||
1040 | # |
||
1041 | do_test vtab1.13-1 { |
||
1042 | execsql { |
||
1043 | SELECT * FROM echo_c WHERE a IS NULL |
||
1044 | } |
||
1045 | } {} |
||
1046 | do_test vtab1.13-2 { |
||
1047 | execsql { |
||
1048 | INSERT INTO c VALUES(NULL, 15, 16); |
||
1049 | SELECT * FROM echo_c WHERE a IS NULL |
||
1050 | } |
||
1051 | } {{} 15 16} |
||
1052 | do_test vtab1.13-3 { |
||
1053 | execsql { |
||
1054 | INSERT INTO c VALUES(15, NULL, 16); |
||
1055 | SELECT * FROM echo_c WHERE b IS NULL |
||
1056 | } |
||
1057 | } {15 {} 16} |
||
1058 | do_test vtab1.13-3 { |
||
1059 | execsql { |
||
1060 | SELECT * FROM echo_c WHERE b IS NULL AND a = 15; |
||
1061 | } |
||
1062 | } {15 {} 16} |
||
1063 | |||
1064 | |||
1065 | do_test vtab1-14.1 { |
||
1066 | execsql { DELETE FROM c } |
||
1067 | set echo_module "" |
||
1068 | execsql { SELECT * FROM echo_c WHERE rowid IN (1, 2, 3) } |
||
1069 | set echo_module |
||
1070 | } [list xBestIndex {SELECT rowid, * FROM 'c'} xFilter {SELECT rowid, * FROM 'c'}] |
||
1071 | |||
1072 | do_test vtab1-14.2 { |
||
1073 | set echo_module "" |
||
1074 | execsql { SELECT * FROM echo_c WHERE rowid = 1 } |
||
1075 | set echo_module |
||
1076 | } [list xBestIndex {SELECT rowid, * FROM 'c' WHERE rowid = ?} xFilter {SELECT rowid, * FROM 'c' WHERE rowid = ?} 1] |
||
1077 | |||
1078 | do_test vtab1-14.3 { |
||
1079 | set echo_module "" |
||
1080 | execsql { SELECT * FROM echo_c WHERE a = 1 } |
||
1081 | set echo_module |
||
1082 | } [list xBestIndex {SELECT rowid, * FROM 'c' WHERE a = ?} xFilter {SELECT rowid, * FROM 'c' WHERE a = ?} 1] |
||
1083 | |||
1084 | do_test vtab1-14.4 { |
||
1085 | set echo_module "" |
||
1086 | execsql { SELECT * FROM echo_c WHERE a IN (1, 2) } |
||
1087 | set echo_module |
||
1088 | } [list xBestIndex {SELECT rowid, * FROM 'c'} xFilter {SELECT rowid, * FROM 'c'}] |
||
1089 | |||
1090 | do_test vtab1-15.1 { |
||
1091 | execsql { |
||
1092 | CREATE TABLE t1(a, b, c); |
||
1093 | CREATE VIRTUAL TABLE echo_t1 USING echo(t1); |
||
1094 | } |
||
1095 | } {} |
||
1096 | do_test vtab1-15.2 { |
||
1097 | execsql { |
||
1098 | INSERT INTO echo_t1(rowid) VALUES(45); |
||
1099 | SELECT rowid, * FROM echo_t1; |
||
1100 | } |
||
1101 | } {45 {} {} {}} |
||
1102 | do_test vtab1-15.3 { |
||
1103 | execsql { |
||
1104 | INSERT INTO echo_t1(rowid) VALUES(NULL); |
||
1105 | SELECT rowid, * FROM echo_t1; |
||
1106 | } |
||
1107 | } {45 {} {} {} 46 {} {} {}} |
||
1108 | do_test vtab1-15.4 { |
||
1109 | catchsql { |
||
1110 | INSERT INTO echo_t1(rowid) VALUES('new rowid'); |
||
1111 | } |
||
1112 | } {1 {datatype mismatch}} |
||
1113 | |||
1114 | # The following tests - vtab1-16.* - are designed to test that setting |
||
1115 | # sqlite3_vtab.zErrMsg variable can be used by the vtab interface to |
||
1116 | # return an error message to the user. |
||
1117 | # |
||
1118 | do_test vtab1-16.1 { |
||
1119 | execsql { |
||
1120 | CREATE TABLE t2(a PRIMARY KEY, b, c); |
||
1121 | INSERT INTO t2 VALUES(1, 2, 3); |
||
1122 | INSERT INTO t2 VALUES(4, 5, 6); |
||
1123 | CREATE VIRTUAL TABLE echo_t2 USING echo(t2); |
||
1124 | } |
||
1125 | } {} |
||
1126 | |||
1127 | set tn 2 |
||
1128 | foreach method [list \ |
||
1129 | xBestIndex \ |
||
1130 | xOpen \ |
||
1131 | xFilter \ |
||
1132 | xNext \ |
||
1133 | xColumn \ |
||
1134 | xRowid \ |
||
1135 | ] { |
||
1136 | do_test vtab1-16.$tn { |
||
1137 | set echo_module_fail($method,t2) "the $method method has failed" |
||
1138 | catchsql { SELECT rowid, * FROM echo_t2 WHERE a >= 1 } |
||
1139 | } "1 {echo-vtab-error: the $method method has failed}" |
||
1140 | unset echo_module_fail($method,t2) |
||
1141 | incr tn |
||
1142 | } |
||
1143 | |||
1144 | foreach method [list \ |
||
1145 | xUpdate \ |
||
1146 | xBegin \ |
||
1147 | xSync \ |
||
1148 | ] { |
||
1149 | do_test vtab1-16.$tn { |
||
1150 | set echo_module_fail($method,t2) "the $method method has failed" |
||
1151 | catchsql { INSERT INTO echo_t2 VALUES(7, 8, 9) } |
||
1152 | } "1 {echo-vtab-error: the $method method has failed}" |
||
1153 | unset echo_module_fail($method,t2) |
||
1154 | incr tn |
||
1155 | } |
||
1156 | |||
1157 | ifcapable altertable { |
||
1158 | do_test vtab1-16.$tn { |
||
1159 | set echo_module_fail(xRename,t2) "the xRename method has failed" |
||
1160 | catchsql { ALTER TABLE echo_t2 RENAME TO another_name } |
||
1161 | } "1 {echo-vtab-error: the xRename method has failed}" |
||
1162 | unset echo_module_fail(xRename,t2) |
||
1163 | incr tn |
||
1164 | } |
||
1165 | |||
1166 | # The following test case exposes an instance in sqlite3_declare_vtab() |
||
1167 | # an error message was set using a call similar to sqlite3_mprintf(zErr), |
||
1168 | # where zErr is an arbitrary string. This is no good if the string contains |
||
1169 | # characters that can be mistaken for printf() formatting directives. |
||
1170 | # |
||
1171 | do_test vtab1-17.1 { |
||
1172 | execsql { |
||
1173 | PRAGMA writable_schema = 1; |
||
1174 | INSERT INTO sqlite_master VALUES( |
||
1175 | 'table', 't3', 't3', 0, 'INSERT INTO "%s%s" VALUES(1)' |
||
1176 | ); |
||
1177 | } |
||
1178 | catchsql { CREATE VIRTUAL TABLE t4 USING echo(t3); } |
||
1179 | } {1 {vtable constructor failed: t4}} |
||
1180 | |||
1181 | unset -nocomplain echo_module_begin_fail |
||
1182 | finish_test |