Debian Codegraph @

ext/fts5/test/fts5connect.test

raw
1 # 2017 August 17
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 #
12
13
14
15 source [file join [file dirname [info script]] fts5_common.tcl]
16 set testprefix fts5connect
17
18 ifcapable !fts5 {
19 finish_test
20 return
21 }
22
23 #-------------------------------------------------------------------------
24 # The tests in this file test the outcome of a schema-reset happening
25 # within the xConnect() method of an FTS5 table. At one point this
26 # was causing a problem in SQLite. Each test proceeds as follows:
27 #
28 # 1. Connection [db] opens the db and reads from some unrelated, non-FTS5
29 # table causing SQLite to load the db schema into memory.
30 #
31 # 2. Connection [db2] opens the db and modifies the db schema.
32 #
33 # 3. Connection [db] reads or writes an existing fts5 table. That the
34 # schema has been modified is detected inside the fts5 xConnect()
35 # callback that is invoked by sqlite3_prepare().
36 #
37 # 4. Verify that the statement in 3 has worked. SQLite should detect
38 # that the schema has changed and successfully prepare the
39 # statement against the new schema.
40 #
41 # Test plan:
42 #
43 # 1.*: Trigger the xConnect()/schema-reset using statements executed
44 # directly against an FTS5 table.
45 #
46 # 2.*: Using various statements executed by various BEFORE triggers.
47 #
48 # 3.*: Using various statements executed by various AFTER triggers.
49 #
50 # 4.*: Using various statements executed by various INSTEAD OF triggers.
51 #
52
53
54
55 do_execsql_test 1.0 {
56 CREATE VIRTUAL TABLE ft1 USING fts5(a, b);
57 CREATE TABLE abc(x INTEGER PRIMARY KEY);
58 CREATE TABLE t1(i INTEGER PRIMARY KEY, a, b);
59
60 INSERT INTO ft1 VALUES('one', 'two');
61 INSERT INTO ft1 VALUES('three', 'four');
62 }
63
64 foreach {tn sql res} {
65 1 "SELECT * FROM ft1" {one two three four}
66 2 "REPLACE INTO ft1(rowid, a, b) VALUES(1, 'five', 'six')" {}
67 3 "SELECT * FROM ft1" {five six three four}
68 4 "INSERT INTO ft1 VALUES('seven', 'eight')" {}
69 5 "SELECT * FROM ft1" {five six three four seven eight}
70 6 "DELETE FROM ft1 WHERE rowid=2" {}
71 7 "UPDATE ft1 SET b='nine' WHERE rowid=1" {}
72 8 "SELECT * FROM ft1" {five nine seven eight}
73 } {
74
75 catch { db close }
76 catch { db2 close }
77 sqlite3 db test.db
78 sqlite3 db2 test.db
79
80 do_test 1.$tn.1 {
81 db eval { INSERT INTO abc DEFAULT VALUES }
82 db2 eval { CREATE TABLE newtable(x,y); DROP TABLE newtable }
83 } {}
84
85 do_execsql_test 1.$tn.2 $sql $res
86
87 do_execsql_test 1.$tn.3 {
88 INSERT INTO ft1(ft1) VALUES('integrity-check');
89 }
90 }
91
92 do_execsql_test 2.0 {
93 CREATE VIRTUAL TABLE ft2 USING fts5(a, b);
94 CREATE TABLE t2(a, b);
95 CREATE TABLE log(txt);
96
97 CREATE TRIGGER t2_ai AFTER INSERT ON t2 BEGIN
98 INSERT INTO ft2(rowid, a, b) VALUES(new.rowid, new.a, new.b);
99 INSERT INTO log VALUES('insert');
100 END;
101
102 CREATE TRIGGER t2_ad AFTER DELETE ON t2 BEGIN
103 DELETE FROM ft2 WHERE rowid = old.rowid;
104 INSERT INTO log VALUES('delete');
105 END;
106
107 CREATE TRIGGER t2_au AFTER UPDATE ON t2 BEGIN
108 UPDATE ft2 SET a=new.a, b=new.b WHERE rowid=new.rowid;
109 INSERT INTO log VALUES('update');
110 END;
111
112 INSERT INTO t2 VALUES('one', 'two');
113 INSERT INTO t2 VALUES('three', 'four');
114 }
115
116 foreach {tn sql res} {
117 1 "SELECT * FROM t2" {one two three four}
118 2 "REPLACE INTO t2(rowid, a, b) VALUES(1, 'five', 'six')" {}
119 3 "SELECT * FROM ft2" {five six three four}
120 4 "INSERT INTO t2 VALUES('seven', 'eight')" {}
121 5 "SELECT * FROM ft2" {five six three four seven eight}
122 6 "DELETE FROM t2 WHERE rowid=2" {}
123 7 "UPDATE t2 SET b='nine' WHERE rowid=1" {}
124 8 "SELECT * FROM ft2" {five nine seven eight}
125 } {
126
127 catch { db close }
128 catch { db2 close }
129 sqlite3 db test.db
130 sqlite3 db2 test.db
131
132 do_test 2.$tn.1 {
133 db eval { INSERT INTO abc DEFAULT VALUES }
134 db2 eval { CREATE TABLE newtable(x,y); DROP TABLE newtable }
135 } {}
136
137 do_execsql_test 2.$tn.2 $sql $res
138
139 do_execsql_test 2.$tn.3 {
140 INSERT INTO ft2(ft2) VALUES('integrity-check');
141 }
142 }
143
144 do_execsql_test 3.0 {
145 CREATE VIRTUAL TABLE ft3 USING fts5(a, b);
146 CREATE TABLE t3(a, b);
147
148 CREATE TRIGGER t3_ai BEFORE INSERT ON t3 BEGIN
149 INSERT INTO ft3(rowid, a, b) VALUES(new.rowid, new.a, new.b);
150 INSERT INTO log VALUES('insert');
151 END;
152
153 CREATE TRIGGER t3_ad BEFORE DELETE ON t3 BEGIN
154 DELETE FROM ft3 WHERE rowid = old.rowid;
155 INSERT INTO log VALUES('delete');
156 END;
157
158 CREATE TRIGGER t3_au BEFORE UPDATE ON t3 BEGIN
159 UPDATE ft3 SET a=new.a, b=new.b WHERE rowid=new.rowid;
160 INSERT INTO log VALUES('update');
161 END;
162
163 INSERT INTO t3(rowid, a, b) VALUES(1, 'one', 'two');
164 INSERT INTO t3(rowid, a, b) VALUES(2, 'three', 'four');
165 }
166
167 foreach {tn sql res} {
168 1 "SELECT * FROM t3" {one two three four}
169 2 "REPLACE INTO t3(rowid, a, b) VALUES(1, 'five', 'six')" {}
170 3 "SELECT * FROM ft3" {five six three four}
171 4 "INSERT INTO t3(rowid, a, b) VALUES(3, 'seven', 'eight')" {}
172 5 "SELECT * FROM ft3" {five six three four seven eight}
173 6 "DELETE FROM t3 WHERE rowid=2" {}
174 7 "UPDATE t3 SET b='nine' WHERE rowid=1" {}
175 8 "SELECT * FROM ft3" {five nine seven eight}
176 } {
177
178 catch { db close }
179 catch { db2 close }
180 sqlite3 db test.db
181 sqlite3 db2 test.db
182
183 do_test 3.$tn.1 {
184 db eval { INSERT INTO abc DEFAULT VALUES }
185 db2 eval { CREATE TABLE newtable(x,y); DROP TABLE newtable }
186 } {}
187
188 do_execsql_test 3.$tn.2 $sql $res
189
190 do_execsql_test 3.$tn.3 {
191 INSERT INTO ft3(ft3) VALUES('integrity-check');
192 }
193 }
194
195 do_execsql_test 4.0 {
196 CREATE VIRTUAL TABLE ft4 USING fts5(a, b);
197 CREATE VIEW v4 AS SELECT rowid, * FROM ft4;
198
199 CREATE TRIGGER t4_ai INSTEAD OF INSERT ON v4 BEGIN
200 INSERT INTO ft4(rowid, a, b) VALUES(new.rowid, new.a, new.b);
201 INSERT INTO log VALUES('insert');
202 END;
203
204 CREATE TRIGGER t4_ad INSTEAD OF DELETE ON v4 BEGIN
205 DELETE FROM ft4 WHERE rowid = old.rowid;
206 INSERT INTO log VALUES('delete');
207 END;
208
209 CREATE TRIGGER t4_au INSTEAD OF UPDATE ON v4 BEGIN
210 UPDATE ft4 SET a=new.a, b=new.b WHERE rowid=new.rowid;
211 INSERT INTO log VALUES('update');
212 END;
213
214 INSERT INTO ft4(rowid, a, b) VALUES(1, 'one', 'two');
215 INSERT INTO ft4(rowid, a, b) VALUES(2, 'three', 'four');
216 }
217
218 foreach {tn sql res} {
219 1 "SELECT * FROM ft4" {one two three four}
220 2 "REPLACE INTO v4(rowid, a, b) VALUES(1, 'five', 'six')" {}
221 3 "SELECT * FROM ft4" {five six three four}
222 4 "INSERT INTO v4(rowid, a, b) VALUES(3, 'seven', 'eight')" {}
223 5 "SELECT * FROM ft4" {five six three four seven eight}
224 6 "DELETE FROM v4 WHERE rowid=2" {}
225 7 "UPDATE v4 SET b='nine' WHERE rowid=1" {}
226 8 "SELECT * FROM ft4" {five nine seven eight}
227 } {
228
229 catch { db close }
230 catch { db2 close }
231 sqlite3 db test.db
232 sqlite3 db2 test.db
233
234 do_test 4.$tn.1 {
235 db eval { INSERT INTO abc DEFAULT VALUES }
236 db2 eval { CREATE TABLE newtable(x,y); DROP TABLE newtable }
237 } {}
238
239 do_execsql_test 4.$tn.2 $sql $res
240
241 do_execsql_test 4.$tn.3 {
242 INSERT INTO ft3(ft3) VALUES('integrity-check');
243 }
244 }
245
246 finish_test