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
15source [file join [file dirname [info script]] fts5_common.tcl]
16set testprefix fts5connect
17
18ifcapable !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
55do_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
64foreach {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
92do_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
116foreach {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
144do_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
167foreach {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
195do_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
218foreach {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
246finish_test
247