xref: /sqlite-3.40.0/test/vtab5.test (revision 3d5ff1c2) 
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.
12#
13# $Id: vtab5.test,v 1.4 2006/06/19 06:32:23 danielk1977 Exp $
14
15set testdir [file dirname $argv0]
16source $testdir/tester.tcl
17
18ifcapable !vtab {
19  finish_test
20  return
21}
22
23# The following tests - vtab5-1.* - ensure that an INSERT, DELETE or UPDATE
24# statement can be executed immediately after a CREATE or schema reload. The
25# point here is testing that the parser always calls xConnect() before the
26# schema of a virtual table is used.
27#
28register_echo_module [sqlite3_connection_pointer db]
29do_test vtab5-1.1 {
30  execsql {
31    CREATE TABLE treal(a VARCHAR(16), b INTEGER, c FLOAT);
32    INSERT INTO treal VALUES('a', 'b', 'c');
33    CREATE VIRTUAL TABLE techo USING echo(treal);
34  }
35} {}
36
37do_test vtab5.1.2 {
38  execsql {
39    SELECT * FROM techo;
40  }
41} {a b c}
42
43do_test vtab5.1.3 {
44  db close
45  sqlite3 db test.db
46  register_echo_module [sqlite3_connection_pointer db]
47  execsql {
48    INSERT INTO techo VALUES('c', 'd', 'e');
49    SELECT * FROM techo;
50  }
51} {a b c c d e}
52do_test vtab5.1.4 {
53  db close
54  sqlite3 db test.db
55  register_echo_module [sqlite3_connection_pointer db]
56  execsql {
57    UPDATE techo SET a = 10;
58    SELECT * FROM techo;
59  }
60} {10 b c 10 d e}
61do_test vtab5.1.5 {
62  db close
63  sqlite3 db test.db
64  register_echo_module [sqlite3_connection_pointer db]
65  execsql {
66    DELETE FROM techo WHERE b > 'c';
67    SELECT * FROM techo;
68  }
69} {10 b c}
70do_test vtab5.1.X {
71  execsql {
72    DROP TABLE techo;
73    DROP TABLE treal;
74  }
75} {}
76
77# The following tests - vtab5-2.* - ensure that collation sequences
78# assigned to virtual table columns via the "CREATE TABLE" statement
79# passed to sqlite3_declare_vtab() are used correctly.
80#
81do_test vtab5.2.1 {
82  execsql {
83    CREATE TABLE strings(str COLLATE NOCASE);
84    INSERT INTO strings VALUES('abc1');
85    INSERT INTO strings VALUES('Abc3');
86    INSERT INTO strings VALUES('ABc2');
87    INSERT INTO strings VALUES('aBc4');
88    SELECT str FROM strings ORDER BY 1;
89  }
90} {abc1 ABc2 Abc3 aBc4}
91do_test vtab5.2.2 {
92  execsql {
93    CREATE VIRTUAL TABLE echo_strings USING echo(strings);
94    SELECT str FROM echo_strings ORDER BY 1;
95  }
96} {abc1 ABc2 Abc3 aBc4}
97do_test vtab5.2.3 {
98  execsql {
99    SELECT str||'' FROM echo_strings ORDER BY 1;
100  }
101} {ABc2 Abc3 aBc4 abc1}
102
103# Test that it is impossible to create a triggger on a virtual table.
104#
105do_test vtab5.3.1 {
106  catchsql {
107    CREATE TRIGGER trig INSTEAD OF INSERT ON echo_strings BEGIN
108      SELECT 1, 2, 3;
109    END;
110  }
111} {1 {cannot create triggers on virtual tables}}
112do_test vtab5.3.2 {
113  catchsql {
114    CREATE TRIGGER trig AFTER INSERT ON echo_strings BEGIN
115      SELECT 1, 2, 3;
116    END;
117  }
118} {1 {cannot create triggers on virtual tables}}
119do_test vtab5.3.2 {
120  catchsql {
121    CREATE TRIGGER trig BEFORE INSERT ON echo_strings BEGIN
122      SELECT 1, 2, 3;
123    END;
124  }
125} {1 {cannot create triggers on virtual tables}}
126
127finish_test
128
129