aboutsummaryrefslogtreecommitdiffstatshomepage
path: root/libraries/sqlite/unix/sqlite-3.5.1/test/vtab1.test
diff options
context:
space:
mode:
Diffstat (limited to 'libraries/sqlite/unix/sqlite-3.5.1/test/vtab1.test')
-rw-r--r--libraries/sqlite/unix/sqlite-3.5.1/test/vtab1.test946
1 files changed, 946 insertions, 0 deletions
diff --git a/libraries/sqlite/unix/sqlite-3.5.1/test/vtab1.test b/libraries/sqlite/unix/sqlite-3.5.1/test/vtab1.test
new file mode 100644
index 0000000..b86ca4a
--- /dev/null
+++ b/libraries/sqlite/unix/sqlite-3.5.1/test/vtab1.test
@@ -0,0 +1,946 @@
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.46 2007/09/03 15:03:21 danielk1977 Exp $
15
16set testdir [file dirname $argv0]
17source $testdir/tester.tcl
18
19ifcapable !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
45
46#----------------------------------------------------------------------
47# Test cases vtab1.1.*
48#
49
50# We cannot create a virtual table if the module has not been registered.
51#
52do_test vtab1-1.1 {
53 catchsql {
54 CREATE VIRTUAL TABLE t1 USING echo;
55 }
56} {1 {no such module: echo}}
57do_test vtab1-1.2 {
58 execsql {
59 SELECT name FROM sqlite_master ORDER BY 1
60 }
61} {}
62
63# Register the module
64register_echo_module [sqlite3_connection_pointer db]
65
66# Once a module has been registered, virtual tables using that module
67# may be created. However if a module xCreate() fails to call
68# sqlite3_declare_vtab() an error will be raised and the table not created.
69#
70# The "echo" module does not invoke sqlite3_declare_vtab() if it is
71# passed zero arguments.
72#
73do_test vtab1-1.3 {
74 catchsql {
75 CREATE VIRTUAL TABLE t1 USING echo;
76 }
77} {1 {vtable constructor did not declare schema: t1}}
78do_test vtab1-1.4 {
79 execsql {
80 SELECT name FROM sqlite_master ORDER BY 1
81 }
82} {}
83
84# The "echo" module xCreate method returns an error and does not create
85# the virtual table if it is passed an argument that does not correspond
86# to an existing real table in the same database.
87#
88do_test vtab1-1.5 {
89 catchsql {
90 CREATE VIRTUAL TABLE t1 USING echo(no_such_table);
91 }
92} {1 {vtable constructor failed: t1}}
93do_test vtab1-1.6 {
94 execsql {
95 SELECT name FROM sqlite_master ORDER BY 1
96 }
97} {}
98
99# Ticket #2156. Using the sqlite3_prepare_v2() API, make sure that
100# a CREATE VIRTUAL TABLE statement can be used multiple times.
101#
102do_test vtab1-1.2152.1 {
103 set DB [sqlite3_connection_pointer db]
104 set sql {CREATE VIRTUAL TABLE t2152a USING echo(t2152b)}
105 set STMT [sqlite3_prepare_v2 $DB $sql -1 TAIL]
106 sqlite3_step $STMT
107} SQLITE_ERROR
108do_test vtab-1.2152.2 {
109 sqlite3_reset $STMT
110 sqlite3_step $STMT
111} SQLITE_ERROR
112do_test vtab-1.2152.3 {
113 sqlite3_reset $STMT
114 db eval {CREATE TABLE t2152b(x,y)}
115 sqlite3_step $STMT
116} SQLITE_DONE
117do_test vtab-1.2152.4 {
118 sqlite3_finalize $STMT
119 db eval {DROP TABLE t2152a; DROP TABLE t2152b}
120} {}
121
122# Test to make sure nothing goes wrong and no memory is leaked if we
123# select an illegal table-name (i.e a reserved name or the name of a
124# table that already exists).
125#
126do_test vtab1-1.7 {
127 catchsql {
128 CREATE VIRTUAL TABLE sqlite_master USING echo;
129 }
130} {1 {object name reserved for internal use: sqlite_master}}
131do_test vtab1-1.8 {
132 catchsql {
133 CREATE TABLE treal(a, b, c);
134 CREATE VIRTUAL TABLE treal USING echo(treal);
135 }
136} {1 {table treal already exists}}
137do_test vtab1-1.9 {
138 execsql {
139 DROP TABLE treal;
140 SELECT name FROM sqlite_master ORDER BY 1
141 }
142} {}
143
144do_test vtab1-1.10 {
145 execsql {
146 CREATE TABLE treal(a, b, c);
147 CREATE VIRTUAL TABLE techo USING echo(treal);
148 }
149 db close
150 sqlite3 db test.db
151 catchsql {
152 SELECT * FROM techo;
153 }
154} {1 {no such module: echo}}
155do_test vtab1-1.11 {
156 catchsql {
157 INSERT INTO techo VALUES(1, 2, 3);
158 }
159} {1 {no such module: echo}}
160do_test vtab1-1.12 {
161 catchsql {
162 UPDATE techo SET a = 10;
163 }
164} {1 {no such module: echo}}
165do_test vtab1-1.13 {
166 catchsql {
167 DELETE FROM techo;
168 }
169} {1 {no such module: echo}}
170do_test vtab1-1.14 {
171 catchsql {
172 PRAGMA table_info(techo)
173 }
174} {1 {no such module: echo}}
175do_test vtab1-1.15 {
176 catchsql {
177 DROP TABLE techo;
178 }
179} {1 {no such module: echo}}
180
181register_echo_module [sqlite3_connection_pointer db]
182register_echo_module [sqlite3_connection_pointer db]
183
184# Test an error message returned from a v-table constructor.
185#
186do_test vtab1-1.16 {
187 execsql {
188 DROP TABLE techo;
189 CREATE TABLE logmsg(log);
190 }
191 catchsql {
192 CREATE VIRTUAL TABLE techo USING echo(treal, logmsg);
193 }
194} {1 {table 'logmsg' already exists}}
195
196do_test vtab1-1.17 {
197 execsql {
198 DROP TABLE treal;
199 DROP TABLE logmsg;
200 SELECT sql FROM sqlite_master;
201 }
202} {}
203
204#----------------------------------------------------------------------
205# Test cases vtab1.2.*
206#
207# At this point, the database is completely empty. The echo module
208# has already been registered.
209
210# If a single argument is passed to the echo module during table
211# creation, it is assumed to be the name of a table in the same
212# database. The echo module attempts to set the schema of the
213# new virtual table to be the same as the existing database table.
214#
215do_test vtab1-2.1 {
216 execsql {
217 CREATE TABLE template(a, b, c);
218 }
219 execsql { PRAGMA table_info(template); }
220} [list \
221 0 a {} 0 {} 0 \
222 1 b {} 0 {} 0 \
223 2 c {} 0 {} 0 \
224]
225do_test vtab1-2.2 {
226 execsql {
227 CREATE VIRTUAL TABLE t1 USING echo(template);
228 }
229 execsql { PRAGMA table_info(t1); }
230} [list \
231 0 a {} 0 {} 0 \
232 1 b {} 0 {} 0 \
233 2 c {} 0 {} 0 \
234]
235
236# Test that the database can be unloaded. This should invoke the xDisconnect()
237# callback for the successfully create virtual table (t1).
238#
239do_test vtab1-2.3 {
240 set echo_module [list]
241 db close
242 set echo_module
243} [list xDisconnect]
244
245# Re-open the database. This should not cause any virtual methods to
246# be called. The invocation of xConnect() is delayed until the virtual
247# table schema is first required by the compiler.
248#
249do_test vtab1-2.4 {
250 set echo_module [list]
251 sqlite3 db test.db
252 db cache size 0
253 set echo_module
254} {}
255
256# Try to query the virtual table schema. This should fail, as the
257# echo module has not been registered with this database connection.
258#
259do_test vtab1.2.6 {
260 catchsql { PRAGMA table_info(t1); }
261} {1 {no such module: echo}}
262
263# Register the module
264register_echo_module [sqlite3_connection_pointer db]
265
266# Try to query the virtual table schema again. This time it should
267# invoke the xConnect method and succeed.
268#
269do_test vtab1.2.7 {
270 execsql { PRAGMA table_info(t1); }
271} [list \
272 0 a {} 0 {} 0 \
273 1 b {} 0 {} 0 \
274 2 c {} 0 {} 0 \
275]
276do_test vtab1.2.8 {
277 set echo_module
278} {xConnect echo main t1 template}
279
280# Drop table t1. This should cause the xDestroy (but not xDisconnect) method
281# to be invoked.
282do_test vtab1-2.5 {
283 set echo_module ""
284 execsql {
285 DROP TABLE t1;
286 }
287 set echo_module
288} {xDestroy}
289
290do_test vtab1-2.6 {
291 execsql {
292 PRAGMA table_info(t1);
293 }
294} {}
295do_test vtab1-2.7 {
296 execsql {
297 SELECT sql FROM sqlite_master;
298 }
299} [list {CREATE TABLE template(a, b, c)}]
300# Clean up other test artifacts:
301do_test vtab1-2.8 {
302 execsql {
303 DROP TABLE template;
304 SELECT sql FROM sqlite_master;
305 }
306} [list]
307
308#----------------------------------------------------------------------
309# Test case vtab1-3 test table scans and the echo module's
310# xBestIndex/xFilter handling of WHERE conditions.
311
312do_test vtab1-3.1 {
313 set echo_module ""
314 execsql {
315 CREATE TABLE treal(a INTEGER, b INTEGER, c);
316 CREATE INDEX treal_idx ON treal(b);
317 CREATE VIRTUAL TABLE t1 USING echo(treal);
318 }
319 set echo_module
320} [list xCreate echo main t1 treal \
321 xSync echo(treal) \
322 xCommit echo(treal) \
323]
324
325# Test that a SELECT on t1 doesn't crash. No rows are returned
326# because the underlying real table is currently empty.
327#
328do_test vtab1-3.2 {
329 execsql {
330 SELECT a, b, c FROM t1;
331 }
332} {}
333
334# Put some data into the table treal. Then try a few simple SELECT
335# statements on t1.
336#
337do_test vtab1-3.3 {
338 execsql {
339 INSERT INTO treal VALUES(1, 2, 3);
340 INSERT INTO treal VALUES(4, 5, 6);
341 SELECT * FROM t1;
342 }
343} {1 2 3 4 5 6}
344do_test vtab1-3.4 {
345 execsql {
346 SELECT a FROM t1;
347 }
348} {1 4}
349do_test vtab1-3.5 {
350 execsql {
351 SELECT rowid FROM t1;
352 }
353} {1 2}
354do_test vtab1-3.6 {
355 set echo_module ""
356 execsql {
357 SELECT * FROM t1;
358 }
359} {1 2 3 4 5 6}
360do_test vtab1-3.7 {
361 execsql {
362 SELECT rowid, * FROM t1;
363 }
364} {1 1 2 3 2 4 5 6}
365do_test vtab1-3.8 {
366 execsql {
367 SELECT a AS d, b AS e, c AS f FROM t1;
368 }
369} {1 2 3 4 5 6}
370
371# Execute some SELECT statements with WHERE clauses on the t1 table.
372# Then check the echo_module variable (written to by the module methods
373# in test8.c) to make sure the xBestIndex() and xFilter() methods were
374# called correctly.
375#
376do_test vtab1-3.8 {
377 set echo_module ""
378 execsql {
379 SELECT * FROM t1;
380 }
381 set echo_module
382} [list xBestIndex {SELECT rowid, * FROM 'treal'} \
383 xFilter {SELECT rowid, * FROM 'treal'} ]
384do_test vtab1-3.9 {
385 set echo_module ""
386 execsql {
387 SELECT * FROM t1 WHERE b = 5;
388 }
389} {4 5 6}
390do_test vtab1-3.10 {
391 set echo_module
392} [list xBestIndex {SELECT rowid, * FROM 'treal' WHERE b = ?} \
393 xFilter {SELECT rowid, * FROM 'treal' WHERE b = ?} 5 ]
394do_test vtab1-3.10 {
395 set echo_module ""
396 execsql {
397 SELECT * FROM t1 WHERE b >= 5 AND b <= 10;
398 }
399} {4 5 6}
400do_test vtab1-3.11 {
401 set echo_module
402} [list xBestIndex {SELECT rowid, * FROM 'treal' WHERE b >= ? AND b <= ?} \
403 xFilter {SELECT rowid, * FROM 'treal' WHERE b >= ? AND b <= ?} 5 10 ]
404do_test vtab1-3.12 {
405 set echo_module ""
406 execsql {
407 SELECT * FROM t1 WHERE b BETWEEN 2 AND 10;
408 }
409} {1 2 3 4 5 6}
410do_test vtab1-3.13 {
411 set echo_module
412} [list xBestIndex {SELECT rowid, * FROM 'treal' WHERE b >= ? AND b <= ?} \
413 xFilter {SELECT rowid, * FROM 'treal' WHERE b >= ? AND b <= ?} 2 10 ]
414
415# Add a function for the MATCH operator. Everything always matches!
416#proc test_match {lhs rhs} {
417# lappend ::echo_module MATCH $lhs $rhs
418# return 1
419#}
420#db function match test_match
421
422set echo_module ""
423do_test vtab1-3.12 {
424 set echo_module ""
425 catchsql {
426 SELECT * FROM t1 WHERE a MATCH 'string';
427 }
428} {1 {unable to use function MATCH in the requested context}}
429do_test vtab1-3.13 {
430 set echo_module
431} [list xBestIndex {SELECT rowid, * FROM 'treal'} \
432 xFilter {SELECT rowid, * FROM 'treal'}]
433do_test vtab1-3.14 {
434 set echo_module ""
435 execsql {
436 SELECT * FROM t1 WHERE b MATCH 'string';
437 }
438} {}
439do_test vtab1-3.15 {
440 set echo_module
441} [list xBestIndex \
442 {SELECT rowid, * FROM 'treal' WHERE b LIKE (SELECT '%'||?||'%')} \
443 xFilter \
444 {SELECT rowid, * FROM 'treal' WHERE b LIKE (SELECT '%'||?||'%')} \
445 string ]
446
447#----------------------------------------------------------------------
448# Test case vtab1-3 test table scans and the echo module's
449# xBestIndex/xFilter handling of ORDER BY clauses.
450
451# This procedure executes the SQL. Then it checks to see if the OP_Sort
452# opcode was executed. If an OP_Sort did occur, then "sort" is appended
453# to the result. If no OP_Sort happened, then "nosort" is appended.
454#
455# This procedure is used to check to make sure sorting is or is not
456# occurring as expected.
457#
458proc cksort {sql} {
459 set ::sqlite_sort_count 0
460 set data [execsql $sql]
461 if {$::sqlite_sort_count} {set x sort} {set x nosort}
462 lappend data $x
463 return $data
464}
465
466do_test vtab1-4.1 {
467 set echo_module ""
468 cksort {
469 SELECT b FROM t1 ORDER BY b;
470 }
471} {2 5 nosort}
472do_test vtab1-4.2 {
473 set echo_module
474} [list xBestIndex {SELECT rowid, * FROM 'treal' ORDER BY b ASC} \
475 xFilter {SELECT rowid, * FROM 'treal' ORDER BY b ASC} ]
476do_test vtab1-4.3 {
477 set echo_module ""
478 cksort {
479 SELECT b FROM t1 ORDER BY b DESC;
480 }
481} {5 2 nosort}
482do_test vtab1-4.4 {
483 set echo_module
484} [list xBestIndex {SELECT rowid, * FROM 'treal' ORDER BY b DESC} \
485 xFilter {SELECT rowid, * FROM 'treal' ORDER BY b DESC} ]
486do_test vtab1-4.3 {
487 set echo_module ""
488 cksort {
489 SELECT b FROM t1 ORDER BY b||'';
490 }
491} {2 5 sort}
492do_test vtab1-4.4 {
493 set echo_module
494} [list xBestIndex {SELECT rowid, * FROM 'treal'} \
495 xFilter {SELECT rowid, * FROM 'treal'} ]
496
497execsql {
498 DROP TABLE t1;
499 DROP TABLE treal;
500}
501
502#----------------------------------------------------------------------
503# Test cases vtab1-5 test SELECT queries that include joins on virtual
504# tables.
505
506proc filter {log} {
507 set out [list]
508 for {set ii 0} {$ii < [llength $log]} {incr ii} {
509 if {[lindex $log $ii] eq "xFilter"} {
510 lappend out xFilter
511 lappend out [lindex $log [expr $ii+1]]
512 }
513 }
514 return $out
515}
516
517do_test vtab1-5-1 {
518 execsql {
519 CREATE TABLE t1(a, b, c);
520 CREATE TABLE t2(d, e, f);
521 INSERT INTO t1 VALUES(1, 'red', 'green');
522 INSERT INTO t1 VALUES(2, 'blue', 'black');
523 INSERT INTO t2 VALUES(1, 'spades', 'clubs');
524 INSERT INTO t2 VALUES(2, 'hearts', 'diamonds');
525 CREATE VIRTUAL TABLE et1 USING echo(t1);
526 CREATE VIRTUAL TABLE et2 USING echo(t2);
527 }
528} {}
529
530do_test vtab1-5-2 {
531 set echo_module ""
532 execsql {
533 SELECT * FROM et1, et2;
534 }
535} [list \
536 1 red green 1 spades clubs \
537 1 red green 2 hearts diamonds \
538 2 blue black 1 spades clubs \
539 2 blue black 2 hearts diamonds \
540]
541do_test vtab1-5-3 {
542 filter $echo_module
543} [list \
544 xFilter {SELECT rowid, * FROM 't1'} \
545 xFilter {SELECT rowid, * FROM 't2'} \
546 xFilter {SELECT rowid, * FROM 't2'} \
547]
548do_test vtab1-5-4 {
549 set echo_module ""
550 execsql {
551 SELECT * FROM et1, et2 WHERE et2.d = 2;
552 }
553} [list \
554 1 red green 2 hearts diamonds \
555 2 blue black 2 hearts diamonds \
556]
557do_test vtab1-5-5 {
558 filter $echo_module
559} [list \
560 xFilter {SELECT rowid, * FROM 't1'} \
561 xFilter {SELECT rowid, * FROM 't2'} \
562 xFilter {SELECT rowid, * FROM 't2'} \
563]
564do_test vtab1-5-6 {
565 execsql {
566 CREATE INDEX i1 ON t2(d);
567 }
568
569 db close
570 sqlite3 db test.db
571 register_echo_module [sqlite3_connection_pointer db]
572
573 set ::echo_module ""
574 execsql {
575 SELECT * FROM et1, et2 WHERE et2.d = 2;
576 }
577} [list \
578 1 red green 2 hearts diamonds \
579 2 blue black 2 hearts diamonds \
580]
581do_test vtab1-5-7 {
582 filter $::echo_module
583} [list \
584 xFilter {SELECT rowid, * FROM 't2' WHERE d = ?} \
585 xFilter {SELECT rowid, * FROM 't1'} \
586]
587
588execsql {
589 DROP TABLE t1;
590 DROP TABLE t2;
591 DROP TABLE et1;
592 DROP TABLE et2;
593}
594
595#----------------------------------------------------------------------
596# Test cases vtab1-6 test INSERT, UPDATE and DELETE operations
597# on virtual tables.
598do_test vtab1-6-1 {
599 execsql { SELECT sql FROM sqlite_master }
600} {}
601do_test vtab1-6-2 {
602 execsql {
603 CREATE TABLE treal(a PRIMARY KEY, b, c);
604 CREATE VIRTUAL TABLE techo USING echo(treal);
605 SELECT name FROM sqlite_master WHERE type = 'table';
606 }
607} {treal techo}
608do_test vtab1-6-3 {
609 execsql {
610 INSERT INTO techo VALUES(1, 2, 3);
611 SELECT * FROM techo;
612 }
613} {1 2 3}
614do_test vtab1-6-4 {
615 execsql {
616 UPDATE techo SET a = 5;
617 SELECT * FROM techo;
618 }
619} {5 2 3}
620
621do_test vtab1-6-5 {
622 execsql {
623 UPDATE techo set a = a||b||c;
624 SELECT * FROM techo;
625 }
626} {523 2 3}
627
628do_test vtab1-6-6 {
629 execsql {
630 UPDATE techo set rowid = 10;
631 SELECT rowid FROM techo;
632 }
633} {10}
634
635do_test vtab1-6-7 {
636 execsql {
637 DELETE FROM techo;
638 SELECT * FROM techo;
639 }
640} {}
641
642
643file delete -force test2.db
644file delete -force test2.db-journal
645sqlite3 db2 test2.db
646execsql {
647 CREATE TABLE techo(a PRIMARY KEY, b, c);
648} db2
649proc check_echo_table {tn} {
650 set ::data1 [execsql {SELECT rowid, * FROM techo}]
651 set ::data2 [execsql {SELECT rowid, * FROM techo} db2]
652 do_test $tn {
653 string equal $::data1 $::data2
654 } 1
655}
656set tn 0
657foreach stmt [list \
658 {INSERT INTO techo VALUES('abc', 'def', 'ghi')} \
659 {INSERT INTO techo SELECT a||'.'||rowid, b, c FROM techo} \
660 {INSERT INTO techo SELECT a||'x'||rowid, b, c FROM techo} \
661 {INSERT INTO techo SELECT a||'y'||rowid, b, c FROM techo} \
662 {DELETE FROM techo WHERE (oid % 3) = 0} \
663 {UPDATE techo set rowid = 100 WHERE rowid = 1} \
664 {INSERT INTO techo(a, b) VALUES('hello', 'world')} \
665 {DELETE FROM techo} \
666] {
667 execsql $stmt
668 execsql $stmt db2
669 check_echo_table vtab1-6.8.[incr tn]
670}
671
672db2 close
673
674
675
676#----------------------------------------------------------------------
677# Test cases vtab1-7 tests that the value returned by
678# sqlite3_last_insert_rowid() is set correctly when rows are inserted
679# into virtual tables.
680do_test vtab1.7-1 {
681 execsql {
682 CREATE TABLE real_abc(a PRIMARY KEY, b, c);
683 CREATE VIRTUAL TABLE echo_abc USING echo(real_abc);
684 }
685} {}
686do_test vtab1.7-2 {
687 execsql {
688 INSERT INTO echo_abc VALUES(1, 2, 3);
689 SELECT last_insert_rowid();
690 }
691} {1}
692do_test vtab1.7-3 {
693 execsql {
694 INSERT INTO echo_abc(rowid) VALUES(31427);
695 SELECT last_insert_rowid();
696 }
697} {31427}
698do_test vtab1.7-4 {
699 execsql {
700 INSERT INTO echo_abc SELECT a||'.v2', b, c FROM echo_abc;
701 SELECT last_insert_rowid();
702 }
703} {31429}
704do_test vtab1.7-5 {
705 execsql {
706 SELECT rowid, a, b, c FROM echo_abc
707 }
708} [list 1 1 2 3 \
709 31427 {} {} {} \
710 31428 1.v2 2 3 \
711 31429 {} {} {} \
712]
713
714# Now test that DELETE and UPDATE operations do not modify the value.
715do_test vtab1.7-6 {
716 execsql {
717 UPDATE echo_abc SET c = 5 WHERE b = 2;
718 SELECT last_insert_rowid();
719 }
720} {31429}
721do_test vtab1.7-7 {
722 execsql {
723 UPDATE echo_abc SET rowid = 5 WHERE rowid = 1;
724 SELECT last_insert_rowid();
725 }
726} {31429}
727do_test vtab1.7-8 {
728 execsql {
729 DELETE FROM echo_abc WHERE b = 2;
730 SELECT last_insert_rowid();
731 }
732} {31429}
733do_test vtab1.7-9 {
734 execsql {
735 SELECT rowid, a, b, c FROM echo_abc
736 }
737} [list 31427 {} {} {} \
738 31429 {} {} {} \
739]
740do_test vtab1.7-10 {
741 execsql {
742 DELETE FROM echo_abc WHERE b = 2;
743 SELECT last_insert_rowid();
744 }
745} {31429}
746do_test vtab1.7-11 {
747 execsql {
748 SELECT rowid, a, b, c FROM real_abc
749 }
750} [list 31427 {} {} {} \
751 31429 {} {} {} \
752]
753do_test vtab1.7-12 {
754 execsql {
755 DELETE FROM echo_abc;
756 SELECT last_insert_rowid();
757 }
758} {31429}
759do_test vtab1.7-13 {
760 execsql {
761 SELECT rowid, a, b, c FROM real_abc
762 }
763} {}
764
765do_test vtab1.8-1 {
766 set echo_module ""
767 execsql {
768 ATTACH 'test2.db' AS aux;
769 CREATE VIRTUAL TABLE aux.e2 USING echo(real_abc);
770 }
771 set echo_module
772} [list xCreate echo aux e2 real_abc \
773 xSync echo(real_abc) \
774 xCommit echo(real_abc) \
775]
776do_test vtab1.8-2 {
777 execsql {
778 DROP TABLE aux.e2;
779 DROP TABLE treal;
780 DROP TABLE techo;
781 DROP TABLE echo_abc;
782 DROP TABLE real_abc;
783 }
784} {}
785
786do_test vtab1.9-1 {
787 set echo_module ""
788 execsql {
789 CREATE TABLE r(a, b, c);
790 CREATE VIRTUAL TABLE e USING echo(r, e_log);
791 SELECT name FROM sqlite_master;
792 }
793} {r e e_log}
794do_test vtab1.9-2 {
795 execsql {
796 DROP TABLE e;
797 SELECT name FROM sqlite_master;
798 }
799} {r}
800
801do_test vtab1.9-3 {
802 set echo_module ""
803 execsql {
804 CREATE VIRTUAL TABLE e USING echo(r, e_log, virtual 1 2 3 varchar(32));
805 }
806 set echo_module
807} [list \
808 xCreate echo main e r e_log {virtual 1 2 3 varchar(32)} \
809 xSync echo(r) \
810 xCommit echo(r) \
811]
812
813do_test vtab1.10-1 {
814 execsql {
815 CREATE TABLE del(d);
816 CREATE VIRTUAL TABLE e2 USING echo(del);
817 }
818 db close
819 sqlite3 db test.db
820 register_echo_module [sqlite3_connection_pointer db]
821 execsql {
822 DROP TABLE del;
823 }
824 catchsql {
825 SELECT * FROM e2;
826 }
827} {1 {vtable constructor failed: e2}}
828do_test vtab1.10-2 {
829 set rc [catch {
830 set ptr [sqlite3_connection_pointer db]
831 sqlite3_declare_vtab $ptr {CREATE TABLE abc(a, b, c)}
832 } msg]
833 list $rc $msg
834} {1 {library routine called out of sequence}}
835do_test vtab1.10-3 {
836 set ::echo_module_begin_fail r
837 catchsql {
838 INSERT INTO e VALUES(1, 2, 3);
839 }
840} {1 {SQL logic error or missing database}}
841do_test vtab1.10-4 {
842 catch {execsql {
843 EXPLAIN SELECT * FROM e WHERE rowid = 2;
844 EXPLAIN QUERY PLAN SELECT * FROM e WHERE rowid = 2 ORDER BY rowid;
845 }}
846} {0}
847
848do_test vtab1.10-5 {
849 set echo_module ""
850 execsql {
851 SELECT * FROM e WHERE rowid||'' MATCH 'pattern';
852 }
853 set echo_module
854} [list \
855 xBestIndex {SELECT rowid, * FROM 'r'} \
856 xFilter {SELECT rowid, * FROM 'r'} \
857]
858proc match_func {args} {return ""}
859do_test vtab1.10-6 {
860 set echo_module ""
861 db function match match_func
862 execsql {
863 SELECT * FROM e WHERE match('pattern', rowid, 'pattern2');
864 }
865 set echo_module
866} [list \
867 xBestIndex {SELECT rowid, * FROM 'r'} \
868 xFilter {SELECT rowid, * FROM 'r'} \
869]
870
871
872# Testing the xFindFunction interface
873#
874catch {rename ::echo_glob_overload {}}
875do_test vtab1.11-1 {
876 execsql {
877 INSERT INTO r(a,b,c) VALUES(1,'?',99);
878 INSERT INTO r(a,b,c) VALUES(2,3,99);
879 SELECT a GLOB b FROM e
880 }
881} {1 0}
882proc ::echo_glob_overload {a b} {
883 return [list $b $a]
884}
885do_test vtab1.11-2 {
886 execsql {
887 SELECT a like 'b' FROM e
888 }
889} {0 0}
890do_test vtab1.11-3 {
891 execsql {
892 SELECT a glob '2' FROM e
893 }
894} {{1 2} {2 2}}
895do_test vtab1.11-4 {
896 execsql {
897 SELECT glob('2',a) FROM e
898 }
899} {0 1}
900do_test vtab1.11-5 {
901 execsql {
902 SELECT glob(a,'2') FROM e
903 }
904} {{2 1} {2 2}}
905
906#----------------------------------------------------------------------
907# Test the outcome if a constraint is encountered half-way through
908# a multi-row INSERT that is inside a transaction
909#
910do_test vtab1.12-1 {
911 execsql {
912 CREATE TABLE b(a, b, c);
913 CREATE TABLE c(a UNIQUE, b, c);
914 INSERT INTO b VALUES(1, 'A', 'B');
915 INSERT INTO b VALUES(2, 'C', 'D');
916 INSERT INTO b VALUES(3, 'E', 'F');
917 INSERT INTO c VALUES(3, 'G', 'H');
918 CREATE VIRTUAL TABLE echo_c USING echo(c);
919 }
920} {}
921
922# First test outside of a transaction.
923do_test vtab1.12-2 {
924 catchsql { INSERT INTO echo_c SELECT * FROM b; }
925} {1 {constraint failed}}
926do_test vtab1.12-3 {
927 execsql { SELECT * FROM c }
928} {3 G H}
929
930breakpoint
931
932# Now the real test - wrapped in a transaction.
933do_test vtab1.12-4 {
934 execsql {BEGIN}
935 catchsql { INSERT INTO echo_c SELECT * FROM b; }
936} {1 {constraint failed}}
937do_test vtab1.12-5 {
938 execsql { SELECT * FROM c }
939} {3 G H}
940do_test vtab1.12-6 {
941 execsql { COMMIT }
942 execsql { SELECT * FROM c }
943} {3 G H}
944
945unset -nocomplain echo_module_begin_fail
946finish_test
generated by cgit v1.1 at 2025-04-25 06:41:30 +0000