3
# The author disclaims copyright to this source code. In place of
4
# a legal notice, here is a blessing:
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.
10
#***********************************************************************
11
# This file implements regression tests for SQLite library.
13
# $Id: vtab5.test,v 1.8 2008/07/12 14:52:21 drh Exp $
15
set testdir [file dirname $argv0]
16
source $testdir/tester.tcl
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.
28
register_echo_module [sqlite3_connection_pointer db]
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);
44
register_echo_module [sqlite3_connection_pointer db]
46
INSERT INTO techo VALUES('c', 'd', 'e');
53
register_echo_module [sqlite3_connection_pointer db]
55
UPDATE techo SET a = 10;
62
register_echo_module [sqlite3_connection_pointer db]
64
DELETE FROM techo WHERE b > 'c';
75
# The following tests - vtab5-2.* - ensure that collation sequences
76
# assigned to virtual table columns via the "CREATE TABLE" statement
77
# passed to sqlite3_declare_vtab() are used correctly.
81
CREATE TABLE strings(str COLLATE NOCASE);
82
INSERT INTO strings VALUES('abc1');
83
INSERT INTO strings VALUES('Abc3');
84
INSERT INTO strings VALUES('ABc2');
85
INSERT INTO strings VALUES('aBc4');
86
SELECT str FROM strings ORDER BY 1;
88
} {abc1 ABc2 Abc3 aBc4}
91
CREATE VIRTUAL TABLE echo_strings USING echo(strings);
92
SELECT str FROM echo_strings ORDER BY 1;
94
} {abc1 ABc2 Abc3 aBc4}
97
SELECT str||'' FROM echo_strings ORDER BY 1;
99
} {ABc2 Abc3 aBc4 abc1}
101
# Test that it is impossible to create a triggger on a virtual table.
106
CREATE TRIGGER trig INSTEAD OF INSERT ON echo_strings BEGIN
110
} {1 {cannot create triggers on virtual tables}}
113
CREATE TRIGGER trig AFTER INSERT ON echo_strings BEGIN
117
} {1 {cannot create triggers on virtual tables}}
120
CREATE TRIGGER trig BEFORE INSERT ON echo_strings BEGIN
124
} {1 {cannot create triggers on virtual tables}}
127
# Test that it is impossible to create an index on a virtual table.
131
CREATE INDEX echo_strings_i ON echo_strings(str);
133
} {1 {virtual tables may not be indexed}}
135
# Test that it is impossible to add a column to a virtual table.
139
ALTER TABLE echo_strings ADD COLUMN col2;
141
} {1 {virtual tables may not be altered}}
143
# Test that it is impossible to rename a virtual table.
144
# UPDATE: It is now possible.
146
# do_test vtab5.4.3 {
148
# ALTER TABLE echo_strings RENAME TO echo_strings2;
150
# } {1 {virtual tables may not be altered}}