gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/fts4opt.test (about) 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