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
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
|
# 2017 August 17
#
# 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.
#
#*************************************************************************
#
source [file join [file dirname [info script]] fts5_common.tcl]
set testprefix fts5connect
ifcapable !fts5 {
finish_test
return
}
#-------------------------------------------------------------------------
# The tests in this file test the outcome of a schema-reset happening
# within the xConnect() method of an FTS5 table. At one point this
# was causing a problem in SQLite. Each test proceeds as follows:
#
# 1. Connection [db] opens the db and reads from some unrelated, non-FTS5
# table causing SQLite to load the db schema into memory.
#
# 2. Connection [db2] opens the db and modifies the db schema.
#
# 3. Connection [db] reads or writes an existing fts5 table. That the
# schema has been modified is detected inside the fts5 xConnect()
# callback that is invoked by sqlite3_prepare().
#
# 4. Verify that the statement in 3 has worked. SQLite should detect
# that the schema has changed and successfully prepare the
# statement against the new schema.
#
# Test plan:
#
# 1.*: Trigger the xConnect()/schema-reset using statements executed
# directly against an FTS5 table.
#
# 2.*: Using various statements executed by various BEFORE triggers.
#
# 3.*: Using various statements executed by various AFTER triggers.
#
# 4.*: Using various statements executed by various INSTEAD OF triggers.
#
do_execsql_test 1.0 {
CREATE VIRTUAL TABLE ft1 USING fts5(a, b);
CREATE TABLE abc(x INTEGER PRIMARY KEY);
CREATE TABLE t1(i INTEGER PRIMARY KEY, a, b);
INSERT INTO ft1 VALUES('one', 'two');
INSERT INTO ft1 VALUES('three', 'four');
}
foreach {tn sql res} {
1 "SELECT * FROM ft1" {one two three four}
2 "REPLACE INTO ft1(rowid, a, b) VALUES(1, 'five', 'six')" {}
3 "SELECT * FROM ft1" {five six three four}
4 "INSERT INTO ft1 VALUES('seven', 'eight')" {}
5 "SELECT * FROM ft1" {five six three four seven eight}
6 "DELETE FROM ft1 WHERE rowid=2" {}
7 "UPDATE ft1 SET b='nine' WHERE rowid=1" {}
8 "SELECT * FROM ft1" {five nine seven eight}
} {
catch { db close }
catch { db2 close }
sqlite3 db test.db
sqlite3 db2 test.db
do_test 1.$tn.1 {
db eval { INSERT INTO abc DEFAULT VALUES }
db2 eval { CREATE TABLE newtable(x,y); DROP TABLE newtable }
} {}
do_execsql_test 1.$tn.2 $sql $res
do_execsql_test 1.$tn.3 {
INSERT INTO ft1(ft1) VALUES('integrity-check');
}
}
do_execsql_test 2.0 {
CREATE VIRTUAL TABLE ft2 USING fts5(a, b);
CREATE TABLE t2(a, b);
CREATE TABLE log(txt);
CREATE TRIGGER t2_ai AFTER INSERT ON t2 BEGIN
INSERT INTO ft2(rowid, a, b) VALUES(new.rowid, new.a, new.b);
INSERT INTO log VALUES('insert');
END;
CREATE TRIGGER t2_ad AFTER DELETE ON t2 BEGIN
DELETE FROM ft2 WHERE rowid = old.rowid;
INSERT INTO log VALUES('delete');
END;
CREATE TRIGGER t2_au AFTER UPDATE ON t2 BEGIN
UPDATE ft2 SET a=new.a, b=new.b WHERE rowid=new.rowid;
INSERT INTO log VALUES('update');
END;
INSERT INTO t2 VALUES('one', 'two');
INSERT INTO t2 VALUES('three', 'four');
}
foreach {tn sql res} {
1 "SELECT * FROM t2" {one two three four}
2 "REPLACE INTO t2(rowid, a, b) VALUES(1, 'five', 'six')" {}
3 "SELECT * FROM ft2" {five six three four}
4 "INSERT INTO t2 VALUES('seven', 'eight')" {}
5 "SELECT * FROM ft2" {five six three four seven eight}
6 "DELETE FROM t2 WHERE rowid=2" {}
7 "UPDATE t2 SET b='nine' WHERE rowid=1" {}
8 "SELECT * FROM ft2" {five nine seven eight}
} {
catch { db close }
catch { db2 close }
sqlite3 db test.db
sqlite3 db2 test.db
do_test 2.$tn.1 {
db eval { INSERT INTO abc DEFAULT VALUES }
db2 eval { CREATE TABLE newtable(x,y); DROP TABLE newtable }
} {}
do_execsql_test 2.$tn.2 $sql $res
do_execsql_test 2.$tn.3 {
INSERT INTO ft2(ft2) VALUES('integrity-check');
}
}
do_execsql_test 3.0 {
CREATE VIRTUAL TABLE ft3 USING fts5(a, b);
CREATE TABLE t3(a, b);
CREATE TRIGGER t3_ai BEFORE INSERT ON t3 BEGIN
INSERT INTO ft3(rowid, a, b) VALUES(new.rowid, new.a, new.b);
INSERT INTO log VALUES('insert');
END;
CREATE TRIGGER t3_ad BEFORE DELETE ON t3 BEGIN
DELETE FROM ft3 WHERE rowid = old.rowid;
INSERT INTO log VALUES('delete');
END;
CREATE TRIGGER t3_au BEFORE UPDATE ON t3 BEGIN
UPDATE ft3 SET a=new.a, b=new.b WHERE rowid=new.rowid;
INSERT INTO log VALUES('update');
END;
INSERT INTO t3(rowid, a, b) VALUES(1, 'one', 'two');
INSERT INTO t3(rowid, a, b) VALUES(2, 'three', 'four');
}
foreach {tn sql res} {
1 "SELECT * FROM t3" {one two three four}
2 "REPLACE INTO t3(rowid, a, b) VALUES(1, 'five', 'six')" {}
3 "SELECT * FROM ft3" {five six three four}
4 "INSERT INTO t3(rowid, a, b) VALUES(3, 'seven', 'eight')" {}
5 "SELECT * FROM ft3" {five six three four seven eight}
6 "DELETE FROM t3 WHERE rowid=2" {}
7 "UPDATE t3 SET b='nine' WHERE rowid=1" {}
8 "SELECT * FROM ft3" {five nine seven eight}
} {
catch { db close }
catch { db2 close }
sqlite3 db test.db
sqlite3 db2 test.db
do_test 3.$tn.1 {
db eval { INSERT INTO abc DEFAULT VALUES }
db2 eval { CREATE TABLE newtable(x,y); DROP TABLE newtable }
} {}
do_execsql_test 3.$tn.2 $sql $res
do_execsql_test 3.$tn.3 {
INSERT INTO ft3(ft3) VALUES('integrity-check');
}
}
do_execsql_test 4.0 {
CREATE VIRTUAL TABLE ft4 USING fts5(a, b);
CREATE VIEW v4 AS SELECT rowid, * FROM ft4;
CREATE TRIGGER t4_ai INSTEAD OF INSERT ON v4 BEGIN
INSERT INTO ft4(rowid, a, b) VALUES(new.rowid, new.a, new.b);
INSERT INTO log VALUES('insert');
END;
CREATE TRIGGER t4_ad INSTEAD OF DELETE ON v4 BEGIN
DELETE FROM ft4 WHERE rowid = old.rowid;
INSERT INTO log VALUES('delete');
END;
CREATE TRIGGER t4_au INSTEAD OF UPDATE ON v4 BEGIN
UPDATE ft4 SET a=new.a, b=new.b WHERE rowid=new.rowid;
INSERT INTO log VALUES('update');
END;
INSERT INTO ft4(rowid, a, b) VALUES(1, 'one', 'two');
INSERT INTO ft4(rowid, a, b) VALUES(2, 'three', 'four');
}
foreach {tn sql res} {
1 "SELECT * FROM ft4" {one two three four}
2 "REPLACE INTO v4(rowid, a, b) VALUES(1, 'five', 'six')" {}
3 "SELECT * FROM ft4" {five six three four}
4 "INSERT INTO v4(rowid, a, b) VALUES(3, 'seven', 'eight')" {}
5 "SELECT * FROM ft4" {five six three four seven eight}
6 "DELETE FROM v4 WHERE rowid=2" {}
7 "UPDATE v4 SET b='nine' WHERE rowid=1" {}
8 "SELECT * FROM ft4" {five nine seven eight}
} {
catch { db close }
catch { db2 close }
sqlite3 db test.db
sqlite3 db2 test.db
do_test 4.$tn.1 {
db eval { INSERT INTO abc DEFAULT VALUES }
db2 eval { CREATE TABLE newtable(x,y); DROP TABLE newtable }
} {}
do_execsql_test 4.$tn.2 $sql $res
do_execsql_test 4.$tn.3 {
INSERT INTO ft3(ft3) VALUES('integrity-check');
}
}
finish_test
|
