Debian Codegraph @

ext/fts5/test/fts5contentless3.test

raw
1 # 2023 July 21
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 # This file contains tests for the content= and content_rowid= options.
13 #
14
15 source [file join [file dirname [info script]] fts5_common.tcl]
16 set testprefix fts5contentless3
17
18 # If SQLITE_ENABLE_FTS5 is not defined, omit this file.
19 ifcapable !fts5 {
20 finish_test
21 return
22 }
23
24 do_execsql_test 1.0 {
25 CREATE VIRTUAL TABLE ft USING fts5(x, content=, contentless_delete=1);
26 BEGIN;
27 INSERT INTO ft VALUES('one one one');
28 INSERT INTO ft VALUES('two two two');
29 INSERT INTO ft VALUES('three three three');
30 INSERT INTO ft VALUES('four four four');
31 INSERT INTO ft VALUES('five five five');
32 INSERT INTO ft VALUES('six six six');
33 INSERT INTO ft VALUES('seven seven seven');
34 INSERT INTO ft VALUES('eight eight eight');
35 INSERT INTO ft VALUES('nine nine nine');
36 COMMIT;
37
38 DELETE FROM ft WHERE rowid=3;
39 }
40
41 proc myhex {hex} { binary decode hex $hex }
42 db func myhex myhex
43
44 do_execsql_test 1.1 {
45 UPDATE ft_data SET block =
46 myhex('04000000 00000001' ||
47 '01020304 01020304 01020304 01020304' ||
48 '01020304 01020304 01020304 01020304'
49 )
50 WHERE id = (SELECT max(id) FROM ft_data);
51 }
52
53 do_execsql_test 1.2 {
54 DELETE FROM ft WHERE rowid=1
55 }
56
57 do_execsql_test 1.3 {
58 SELECT rowid FROM ft('two');
59 } {2}
60
61 do_execsql_test 1.3 {
62 UPDATE ft_data SET block =
63 myhex('08000000 00000001' ||
64 '0000000001020304 0000000001020304 0000000001020304 0000000001020304' ||
65 '0000000001020304 0000000001020304 0000000001020304 0000000001020304'
66 )
67 WHERE id = (SELECT max(id) FROM ft_data);
68 }
69
70 do_execsql_test 1.4 {
71 SELECT rowid FROM ft('two');
72 } {2}
73
74 do_execsql_test 1.5 {
75 DELETE FROM ft WHERE rowid=4
76 }
77
78 do_execsql_test 1.6 {
79 UPDATE ft_data SET block = myhex('04000000 00000000')
80 WHERE id = (SELECT max(id) FROM ft_data);
81 }
82 do_execsql_test 1.7 {
83 SELECT rowid FROM ft('two');
84 } {2}
85
86 do_execsql_test 1.8 {
87 UPDATE ft_data SET block = myhex('04000000 00000000')
88 WHERE id = (SELECT max(id) FROM ft_data);
89 }
90 do_execsql_test 1.9 {
91 DELETE FROM ft WHERE rowid=8
92 } {}
93
94 #-------------------------------------------------------------------------
95 reset_db
96 do_execsql_test 2.0 {
97 CREATE VIRTUAL TABLE ft USING fts5(x, content=, contentless_delete=1);
98 INSERT INTO ft VALUES('one one one');
99 INSERT INTO ft VALUES('two two two');
100 INSERT INTO ft VALUES('three three three');
101 INSERT INTO ft VALUES('four four four');
102 INSERT INTO ft VALUES('five five five');
103 INSERT INTO ft VALUES('six six six');
104 INSERT INTO ft VALUES('seven seven seven');
105 INSERT INTO ft VALUES('eight eight eight');
106 INSERT INTO ft VALUES('nine nine nine');
107 }
108
109 do_execsql_test 2.1 {
110 INSERT INTO ft(ft) VALUES('optimize');
111 }
112 do_execsql_test 2.2 {
113 SELECT count(*) FROM ft_data
114 } {3}
115 do_execsql_test 2.3 {
116 DELETE FROM ft WHERE rowid=5
117 }
118 do_execsql_test 2.4 {
119 SELECT count(*) FROM ft_data
120 } {4}
121
122 # Check that an 'optimize' works (rewrites the index) if there is a single
123 # segment with one or more tombstone hash pages.
124 do_execsql_test 2.5 {
125 INSERT INTO ft(ft) VALUES('optimize');
126 }
127 do_execsql_test 2.6 {
128 SELECT count(*) FROM ft_data
129 } {3}
130
131 # Check that an 'optimize' is a no-op if there is a single segment
132 # and no tombstone hash pages.
133 do_execsql_test 2.7 {
134 INSERT INTO ft(ft) VALUES('optimize');
135 SELECT rowid FROM ft_data;
136 } [db eval {SELECT rowid FROM ft_data}]
137
138 #-------------------------------------------------------------------------
139 reset_db
140 do_execsql_test 3.0 {
141 CREATE VIRTUAL TABLE ft USING fts5(x, content=, contentless_delete=1);
142 INSERT INTO ft(ft, rank) VALUES('pgsz', 64);
143 WITH s(i) AS (
144 SELECT 1 UNION ALL SELECT i+1 FROM s WHERE i<1000
145 )
146 INSERT INTO ft(rowid, x) SELECT i, i||' '||i||' '||i||' '||i FROM s;
147 INSERT INTO ft(ft) VALUES('optimize');
148 }
149
150 do_execsql_test 3.1 {
151 SELECT count(*) FROM ft_data
152 } {200}
153
154 do_execsql_test 3.2 {
155 DELETE FROM ft WHERE (rowid % 50)==0;
156 SELECT count(*) FROM ft_data;
157 } {203}
158
159 do_execsql_test 3.3 {
160 INSERT INTO ft(ft, rank) VALUES('merge', 500);
161 SELECT rowid FROM ft_data;
162 } [db eval {SELECT rowid FROM ft_data}]
163
164 do_execsql_test 3.4 {
165 INSERT INTO ft(ft, rank) VALUES('merge', -1000);
166 SELECT count(*) FROM ft_data;
167 } {197}
168
169 do_execsql_test 3.5 {
170 DELETE FROM ft WHERE (rowid % 50)==1;
171 SELECT count(*) FROM ft_data;
172 } {200}
173
174 do_execsql_test 3.6 {
175 SELECT level, segment, npgtombstone FROM fts5_structure(
176 (SELECT block FROM ft_data WHERE id=10)
177 )
178 } {1 0 3}
179
180 do_test 3.6 {
181 while 1 {
182 set nChange [db total_changes]
183 execsql { INSERT INTO ft(ft, rank) VALUES('merge', -5) }
184 if {([db total_changes] - $nChange)<2} break
185 }
186 } {}
187
188 do_execsql_test 3.7 {
189 SELECT level, segment, npgtombstone FROM fts5_structure(
190 (SELECT block FROM ft_data WHERE id=10)
191 )
192 } {2 0 0}
193
194
195 finish_test