1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
|
# 2006 June 10
#
# The author disclaims copyright to this source code. In place of
# a legal notice, here is a blessing:
#
# May you do good and not evil.
# May you find forgiveness for yourself and forgive others.
# May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# $Id: vtab5.test,v 1.8 2008/07/12 14:52:21 drh Exp $
set testdir [file dirname $argv0]
source $testdir/tester.tcl
ifcapable !vtab {
finish_test
return
}
# The following tests - vtab5-1.* - ensure that an INSERT, DELETE or UPDATE
# statement can be executed immediately after a CREATE or schema reload. The
# point here is testing that the parser always calls xConnect() before the
# schema of a virtual table is used.
#
register_echo_module [sqlite3_connection_pointer db]
do_test vtab5-1.1 {
execsql {
CREATE TABLE treal(a VARCHAR(16), b INTEGER, c FLOAT);
INSERT INTO treal VALUES('a', 'b', 'c');
CREATE VIRTUAL TABLE techo USING echo(treal);
}
} {}
do_test vtab5.1.2 {
execsql {
SELECT * FROM techo;
}
} {a b c}
do_test vtab5.1.3 {
db close
sqlite3 db test.db
register_echo_module [sqlite3_connection_pointer db]
execsql {
INSERT INTO techo VALUES('c', 'd', 'e');
SELECT * FROM techo;
}
} {a b c c d e}
do_test vtab5.1.4 {
db close
sqlite3 db test.db
register_echo_module [sqlite3_connection_pointer db]
execsql {
UPDATE techo SET a = 10;
SELECT * FROM techo;
}
} {10 b c 10 d e}
do_test vtab5.1.5 {
db close
sqlite3 db test.db
register_echo_module [sqlite3_connection_pointer db]
execsql {
DELETE FROM techo WHERE b > 'c';
SELECT * FROM techo;
}
} {10 b c}
do_test vtab5.1.X {
execsql {
DROP TABLE techo;
DROP TABLE treal;
}
} {}
# The following tests - vtab5-2.* - ensure that collation sequences
# assigned to virtual table columns via the "CREATE TABLE" statement
# passed to sqlite3_declare_vtab() are used correctly.
#
do_test vtab5.2.1 {
execsql {
CREATE TABLE strings(str COLLATE NOCASE);
INSERT INTO strings VALUES('abc1');
INSERT INTO strings VALUES('Abc3');
INSERT INTO strings VALUES('ABc2');
INSERT INTO strings VALUES('aBc4');
SELECT str FROM strings ORDER BY 1;
}
} {abc1 ABc2 Abc3 aBc4}
do_test vtab5.2.2 {
execsql {
CREATE VIRTUAL TABLE echo_strings USING echo(strings);
SELECT str FROM echo_strings ORDER BY 1;
}
} {abc1 ABc2 Abc3 aBc4}
do_test vtab5.2.3 {
execsql {
SELECT str||'' FROM echo_strings ORDER BY 1;
}
} {ABc2 Abc3 aBc4 abc1}
# Test that it is impossible to create a triggger on a virtual table.
#
ifcapable trigger {
do_test vtab5.3.1 {
catchsql {
CREATE TRIGGER trig INSTEAD OF INSERT ON echo_strings BEGIN
SELECT 1, 2, 3;
END;
}
} {1 {cannot create triggers on virtual tables}}
do_test vtab5.3.2 {
catchsql {
CREATE TRIGGER trig AFTER INSERT ON echo_strings BEGIN
SELECT 1, 2, 3;
END;
}
} {1 {cannot create triggers on virtual tables}}
do_test vtab5.3.2 {
catchsql {
CREATE TRIGGER trig BEFORE INSERT ON echo_strings BEGIN
SELECT 1, 2, 3;
END;
}
} {1 {cannot create triggers on virtual tables}}
}
# Test that it is impossible to create an index on a virtual table.
#
do_test vtab5.4.1 {
catchsql {
CREATE INDEX echo_strings_i ON echo_strings(str);
}
} {1 {virtual tables may not be indexed}}
# Test that it is impossible to add a column to a virtual table.
#
ifcapable altertable {
do_test vtab5.4.2 {
catchsql {
ALTER TABLE echo_strings ADD COLUMN col2;
}
} {1 {virtual tables may not be altered}}
}
# Test that it is impossible to rename a virtual table.
# UPDATE: It is now possible.
#
# do_test vtab5.4.3 {
# catchsql {
# ALTER TABLE echo_strings RENAME TO echo_strings2;
# }
# } {1 {virtual tables may not be altered}}
finish_test
|
