Debian Codegraph @

ext/fts5/test/fts5update2.test

raw
1 # 2024 Sep 27
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 # This file implements regression tests for SQLite library. The
12 # focus of this script is testing the FTS5 module.
13 #
14
15 source [file join [file dirname [info script]] fts5_common.tcl]
16 set testprefix fts5update2
17
18 # If SQLITE_ENABLE_FTS5 is not defined, omit this file.
19 ifcapable !fts5 {
20 finish_test
21 return
22 }
23
24
25 #-------------------------------------------------------------------------
26 # Test that the various types of UPDATE statement are handled correctly
27 # by different table types.
28 #
29 foreach_detail_mode $testprefix {
30 foreach {tn cu} {
31 1 0
32 2 1
33 } {
34 reset_db
35 do_execsql_test 1.$tn.1 "
36 CREATE VIRTUAL TABLE ft1 USING fts5(a, b UNINDEXED, c UNINDEXED, d,
37 content='',
38 contentless_unindexed=$cu,
39 detail=%DETAIL%
40 );
41 CREATE VIRTUAL TABLE ft2 USING fts5(a, b UNINDEXED, c UNINDEXED, d,
42 content='',
43 contentless_unindexed=$cu, contentless_delete=1,
44 detail=%DETAIL%
45 );
46 "
47
48 do_execsql_test 1.$tn.2 {
49 INSERT INTO ft1(rowid, a, b, c, d) VALUES(1, 'a1', 'b1', 'c1', 'd1');
50 INSERT INTO ft1(rowid, a, b, c, d) VALUES(2, 'a2', 'b2', 'c2', 'd2');
51 INSERT INTO ft1(rowid, a, b, c, d) VALUES(3, 'a3', 'b3', 'c3', 'd3');
52
53 INSERT INTO ft2(rowid, a, b, c, d) VALUES(1, 'a1', 'b1', 'c1', 'd1');
54 INSERT INTO ft2(rowid, a, b, c, d) VALUES(2, 'a2', 'b2', 'c2', 'd2');
55 INSERT INTO ft2(rowid, a, b, c, d) VALUES(3, 'a3', 'b3', 'c3', 'd3');
56 }
57
58 # It should be possible to update a subset of the UNINDEXED columns of
59 # a contentless table. Regardless of whether or not contentless_unindexed=1
60 # or contentless_delete=1 is set.
61 do_execsql_test 1.$tn.3 {
62 UPDATE ft1 SET b=b||'.1';
63 UPDATE ft2 SET b=b||'.1';
64 }
65 do_execsql_test 1.$tn.4 {
66 UPDATE ft1 SET b=b||'.2', c=c||'.2';
67 UPDATE ft2 SET b=b||'.2', c=c||'.2';
68 }
69
70 set res(0) {
71 1 {} {} {} {}
72 2 {} {} {} {}
73 3 {} {} {} {}
74 }
75 set res(1) {
76 1 {} b1.1.2 c1.2 {}
77 2 {} b2.1.2 c2.2 {}
78 3 {} b3.1.2 c3.2 {}
79 }
80
81 do_execsql_test 1.$tn.5 {
82 SELECT rowid, * FROM ft2
83 } $res($cu)
84
85 do_execsql_test 1.6.1 { SELECT rowid FROM ft1('a2') } {2}
86 do_execsql_test 1.6.2 { SELECT rowid FROM ft2('a2') } {2}
87
88 # It should be possible to update all indexed columns (but no other subset)
89 # if the contentless_delete=1 option is set, as it is for "ft2".
90 do_execsql_test 1.$tn.7 {
91 UPDATE ft2 SET a='a22', d='d22' WHERE rowid=2;
92 }
93 do_execsql_test 1.$tn.8 { SELECT rowid FROM ft2('a22 AND d22') } {2}
94
95 do_execsql_test 1.$tn.9 {
96 UPDATE ft2 SET a='a33', d='d33', b='b3' WHERE rowid=3;
97 }
98
99 set res(1) {
100 1 {} b1.1.2 c1.2 {}
101 2 {} b2.1.2 c2.2 {}
102 3 {} b3 c3.2 {}
103 }
104 do_execsql_test 1.$tn.10 {
105 SELECT rowid, * FROM ft2
106 } $res($cu)
107
108 do_catchsql_test 1.$tn.11 {
109 UPDATE ft2 SET a='a11' WHERE rowid=1
110 } {1 {cannot UPDATE a subset of columns on fts5 contentless-delete table: ft2}}
111 do_catchsql_test 1.$tn.12 {
112 UPDATE ft2 SET d='d11' WHERE rowid=1
113 } {1 {cannot UPDATE a subset of columns on fts5 contentless-delete table: ft2}}
114
115 # It is not possible to update the values of indexed columns if
116 # contentless_delete=1 is not set.
117 do_catchsql_test 1.$tn.13 {
118 UPDATE ft1 SET a='a11' WHERE rowid=1
119 } {1 {cannot UPDATE contentless fts5 table: ft1}}
120 do_catchsql_test 1.$tn.14 {
121 UPDATE ft1 SET d='d11' WHERE rowid=1
122 } {1 {cannot UPDATE contentless fts5 table: ft1}}
123
124 # It should be possible to update the rowid if contentless_delete=1 is
125 # set and all indexed columns are updated.
126 do_execsql_test 1.$tn.15 {
127 UPDATE ft2 SET a='aXone', d='dXone', rowid=11 WHERE rowid=1
128 }
129
130 set res(0) {
131 2 {} {} {} {}
132 3 {} {} {} {}
133 11 {} {} {} {}
134 }
135 set res(1) {
136 2 {} b2.1.2 c2.2 {}
137 3 {} b3 c3.2 {}
138 11 {} b1.1.2 c1.2 {}
139 }
140 do_execsql_test 1.$tn.16 {
141 SELECT rowid, * FROM ft2
142 } $res($cu)
143
144 # Should not be possible to update the rowid of a contentless_delete=1
145 # table if no indexed columns are updated.
146 do_catchsql_test 1.$tn.17 {
147 UPDATE ft2 SET rowid=12 WHERE rowid=11
148 } {1 {cannot UPDATE a subset of columns on fts5 contentless-delete table: ft2}}
149 do_catchsql_test 1.$tn.18 {
150 UPDATE ft1 SET rowid=12 WHERE rowid=1
151 } {1 {cannot UPDATE contentless fts5 table: ft1}}
152
153 do_execsql_test 1.$tn.19 {
154 UPDATE ft2 SET a='aXtwo', d='dXtwo', c='newval', rowid=12 WHERE rowid=2
155 } {}
156
157 set res(0) {
158 3 {} {} {} {}
159 11 {} {} {} {}
160 12 {} {} {} {}
161 }
162 set res(1) {
163 3 {} b3 c3.2 {}
164 11 {} b1.1.2 c1.2 {}
165 12 {} b2.1.2 newval {}
166 }
167 do_execsql_test 1.$tn.20 {
168 SELECT rowid, * FROM ft2
169 } $res($cu)
170
171 do_execsql_test 1.$tn.21 {
172 SELECT rowid, * FROM ft2('aXtwo AND dXtwo')
173 } [lrange $res($cu) 10 end]
174
175 }} ;# end of [foreach_detail_mode] loop
176
177 finish_test