Debian Codegraph @

test/fts4opt.test

raw
1 # 2016 March 8
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 set testdir [file dirname $argv0]
14 source $testdir/tester.tcl
15 source $testdir/fts3_common.tcl
16 set ::testprefix fts4opt
17
18 # If SQLITE_ENABLE_FTS3 is defined, omit this file.
19 ifcapable !fts3 {
20 finish_test
21 return
22 }
23
24 # Create the fts_kjv_genesis procedure which fills and FTS3/4 table
25 # with the complete text of the Book of Genesis.
26 #
27 source $testdir/genesis.tcl
28
29 do_execsql_test 1.0 { CREATE TABLE t1(docid, words) }
30 fts_kjv_genesis
31
32 #-------------------------------------------------------------------------
33 # Argument $db is an open database handle. $tbl is the name of an FTS3/4
34 # table with the database. This command rearranges the contents of the
35 # %_segdir table so that all segments within each index are on the same
36 # level. This means that the 'merge' command can then be used for an
37 # incremental optimize routine.
38 #
39 proc prepare_for_optimize {db tbl} {
40 sqlite3_db_config $db DEFENSIVE 0
41 $db eval [string map [list % $tbl] {
42 BEGIN;
43 CREATE TEMP TABLE tmp_segdir(
44 level, idx, start_block, leaves_end_block, end_block, root
45 );
46
47 INSERT INTO temp.tmp_segdir
48 SELECT
49 1024*(o.level / 1024) + 32, -- level
50 sum(o.level<i.level OR (o.level=i.level AND o.idx>i.idx)), -- idx
51 o.start_block, o.leaves_end_block, o.end_block, o.root -- other
52 FROM %_segdir o, %_segdir i
53 WHERE (o.level / 1024) = (i.level / 1024)
54 GROUP BY o.level, o.idx;
55
56 DELETE FROM %_segdir;
57 INSERT INTO %_segdir SELECT * FROM temp.tmp_segdir;
58 DROP TABLE temp.tmp_segdir;
59
60 COMMIT;
61 }]
62 }
63
64 do_test 1.1 {
65 execsql { CREATE VIRTUAL TABLE t2 USING fts4(words, prefix="1,2,3") }
66 foreach {docid words} [db eval { SELECT * FROM t1 }] {
67 execsql { INSERT INTO t2(docid, words) VALUES($docid, $words) }
68 }
69 } {}
70
71 do_execsql_test 1.2 {
72 SELECT level, count(*) FROM t2_segdir GROUP BY level
73 } {
74 0 13 1 15 2 5
75 1024 13 1025 15 1026 5
76 2048 13 2049 15 2050 5
77 3072 13 3073 15 3074 5
78 }
79
80 do_execsql_test 1.3 { INSERT INTO t2(t2) VALUES('integrity-check') }
81 prepare_for_optimize db t2
82 do_execsql_test 1.4 { INSERT INTO t2(t2) VALUES('integrity-check') }
83
84 do_execsql_test 1.5 {
85 SELECT level, count(*) FROM t2_segdir GROUP BY level
86 } {
87 32 33
88 1056 33
89 2080 33
90 3104 33
91 }
92
93 do_test 1.6 {
94 while 1 {
95 set tc1 [db total_changes]
96 execsql { INSERT INTO t2(t2) VALUES('merge=5,2') }
97 set tc2 [db total_changes]
98 if {($tc2 - $tc1) < 2} break
99 }
100 execsql { SELECT level, count(*) FROM t2_segdir GROUP BY level }
101 } {33 1 1057 1 2081 1 3105 1}
102 do_execsql_test 1.7 { INSERT INTO t2(t2) VALUES('integrity-check') }
103
104 do_execsql_test 1.8 {
105 INSERT INTO t2(words) SELECT words FROM t1;
106 SELECT level, count(*) FROM t2_segdir GROUP BY level;
107 } {0 2 1024 2 2048 2 3072 2}
108
109 #-------------------------------------------------------------------------
110
111 do_execsql_test 2.0 {
112 DELETE FROM t2;
113 }
114 do_test 2.1 {
115 foreach {docid words} [db eval { SELECT * FROM t1 }] {
116 execsql { INSERT INTO t2(docid, words) VALUES($docid, $words) }
117 }
118
119 set i 0
120 foreach {docid words} [db eval { SELECT * FROM t1 }] {
121 if {[incr i] % 2} { execsql { DELETE FROM t2 WHERE docid = $docid } }
122 }
123
124 set i 0
125 foreach {docid words} [db eval { SELECT * FROM t1 }] {
126 if {[incr i] % 3} {
127 execsql { INSERT OR REPLACE INTO t2(docid, words) VALUES($docid, $words) }
128 }
129 }
130 } {}
131
132 do_execsql_test 2.2 {
133 SELECT level, count(*) FROM t2_segdir GROUP BY level
134 } {
135 0 10 1 15 2 12
136 1024 10 1025 15 1026 12
137 2048 10 2049 15 2050 12
138 3072 10 3073 15 3074 12
139 }
140
141 do_execsql_test 2.3 { INSERT INTO t2(t2) VALUES('integrity-check') }
142 prepare_for_optimize db t2
143 do_execsql_test 2.4 { INSERT INTO t2(t2) VALUES('integrity-check') }
144
145 do_execsql_test 2.5 {
146 SELECT level, count(*) FROM t2_segdir GROUP BY level
147 } {
148 32 37
149 1056 37
150 2080 37
151 3104 37
152 }
153
154 do_test 2.6 {
155 while 1 {
156 set tc1 [db total_changes]
157 execsql { INSERT INTO t2(t2) VALUES('merge=5,2') }
158 set tc2 [db total_changes]
159 if {($tc2 - $tc1) < 2} break
160 }
161 execsql { SELECT level, count(*) FROM t2_segdir GROUP BY level }
162 } {33 1 1057 1 2081 1 3105 1}
163 do_execsql_test 2.7 { INSERT INTO t2(t2) VALUES('integrity-check') }
164
165 do_execsql_test 2.8 {
166 INSERT INTO t2(words) SELECT words FROM t1;
167 SELECT level, count(*) FROM t2_segdir GROUP BY level;
168 } {0 2 1024 2 2048 2 3072 2}
169
170 #-------------------------------------------------------------------------
171 # Check that 'optimize' works when there is data in the in-memory hash
172 # table, but no segments at all on disk.
173 #
174 do_execsql_test 3.1 {
175 CREATE VIRTUAL TABLE fts USING fts4 (t);
176 INSERT INTO fts (fts) VALUES ('optimize');
177 }
178 do_execsql_test 3.2 {
179 INSERT INTO fts(fts) VALUES('integrity-check');
180 SELECT count(*) FROM fts_segdir;
181 } {0}
182 do_execsql_test 3.3 {
183 BEGIN;
184 INSERT INTO fts (rowid, t) VALUES (2, 'test');
185 INSERT INTO fts (fts) VALUES ('optimize');
186 COMMIT;
187 SELECT level, idx FROM fts_segdir;
188 } {0 0}
189 do_execsql_test 3.4 {
190 INSERT INTO fts(fts) VALUES('integrity-check');
191 SELECT rowid FROM fts WHERE fts MATCH 'test';
192 } {2}
193 do_execsql_test 3.5 {
194 INSERT INTO fts (fts) VALUES ('optimize');
195 INSERT INTO fts(fts) VALUES('integrity-check');
196 }
197 do_test 3.6 {
198 set c1 [db total_changes]
199 execsql { INSERT INTO fts (fts) VALUES ('optimize') }
200 expr {[db total_changes] - $c1}
201 } {1}
202 do_test 3.7 {
203 execsql { INSERT INTO fts (rowid, t) VALUES (3, 'xyz') }
204 set c1 [db total_changes]
205 execsql { INSERT INTO fts (fts) VALUES ('optimize') }
206 expr {([db total_changes] - $c1) > 1}
207 } {1}
208 do_test 3.8 {
209 set c1 [db total_changes]
210 execsql { INSERT INTO fts (fts) VALUES ('optimize') }
211 expr {[db total_changes] - $c1}
212 } {1}
213
214 finish_test