AryaWu/sqlite
0
1# 2012 March 062#3# The author disclaims copyright to this source code. In place of4# 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. The12# focus of this script is testing the incremental merge function.13#14 15set testdir [file dirname $argv0]16source $testdir/tester.tcl17source $testdir/fts3_common.tcl18 19# If SQLITE_ENABLE_FTS3 is defined, omit this file.20ifcapable !fts3 {21 finish_test22 return23}24 25proc fts3_integrity_check {tbl} {26 db eval "INSERT INTO $tbl ($tbl) VALUES('integrity-check')"27 return "ok"28}29 30foreach mod {fts3 fts4} {31 set ::testprefix fts4merge-$mod32 reset_db33 34 #-------------------------------------------------------------------------35 # Test cases 1.*36 #37 do_test 1.0 { fts3_build_db_1 -module $mod 1004 } {}38 do_test 1.1 { fts3_integrity_check t1 } {ok}39 do_execsql_test 1.1 { 40 SELECT level, string_agg(idx, ' ') FROM t1_segdir GROUP BY level 41 } {42 0 {0 1 2 3 4 5 6 7 8 9 10 11} 43 1 {0 1 2 3 4 5 6 7 8 9 10 11 12 13}44 2 {0 1 2}45 }46 47 for {set i 0} {$i<20} {incr i} {48 do_execsql_test 1.2.$i.1 { INSERT INTO t1(t1) VALUES('merge=1') }49 do_test 1.2.$i.2 { fts3_integrity_check t1 } ok50 do_execsql_test 1.2.$i.3 { 51 SELECT docid FROM t1 WHERE t1 MATCH 'zero one two three'52 } {123 132 213 231 312 321}53 }54 55 do_execsql_test 1.3 { 56 SELECT level, group_concat(idx, ' ') FROM t1_segdir GROUP BY level 57 } {58 2 {0 1 2 3}59 }60 61 for {set i 0} {$i<100} {incr i} {62 do_execsql_test 1.4.$i { INSERT INTO t1(t1) VALUES('merge=1,4') }63 do_test 1.4.$i.2 { fts3_integrity_check t1 } ok64 do_execsql_test 1.4.$i.3 { 65 SELECT docid FROM t1 WHERE t1 MATCH 'zero one two three'66 } {123 132 213 231 312 321}67 }68 69 do_execsql_test 1.5 { 70 SELECT level, string_agg(idx, ' ') FROM t1_segdir GROUP BY level 71 } {72 3 073 }74 75 #-------------------------------------------------------------------------76 # Test cases 2.* test that errors in the xxx part of the 'merge=xxx' are77 # handled correctly.78 #79 do_execsql_test 2.0 "CREATE VIRTUAL TABLE t2 USING $mod"80 81 foreach {tn arg} {82 1 {merge=abc}83 2 {merge=%%%}84 3 {merge=,}85 4 {merge=5,}86 5 {merge=6,%}87 6 {merge=6,six}88 7 {merge=6,1}89 } {90 do_catchsql_test 2.$tn { 91 INSERT INTO t2(t2) VALUES($arg);92 } {1 {SQL logic error}}93 }94 95 #-------------------------------------------------------------------------96 # Test cases 3.*97 #98 do_test 3.0 { 99 reset_db100 execsql { PRAGMA page_size = 512 }101 fts3_build_db_2 -module $mod 30040 102 } {}103 do_test 3.1 { fts3_integrity_check t2 } {ok}104 105 do_execsql_test 3.2 { 106 SELECT level, string_agg(idx, ' ') FROM t2_segdir GROUP BY level 107 } {108 0 {0 1 2 3 4 5 6} 109 1 {0 1 2 3 4} 110 2 {0 1 2 3 4} 111 3 {0 1 2 3 4 5 6}112 }113 114 do_execsql_test 3.3 { 115 INSERT INTO t2(t2) VALUES('merge=1000000,2');116 SELECT level, group_concat(idx, ' ') FROM t2_segdir GROUP BY level 117 } {118 4 0119 }120 121 #-------------------------------------------------------------------------122 # Test cases 4.*123 #124 reset_db125 do_execsql_test 4.1 "126 PRAGMA page_size = 512;127 CREATE VIRTUAL TABLE t4 USING $mod;128 PRAGMA main.page_size;129 " {512}130 131 do_test 4.2 {132 foreach x {a c b d e f g h i j k l m n o p} {133 execsql "INSERT INTO t4 VALUES('[string repeat $x 600]')"134 }135 execsql {SELECT level, string_agg(idx, ' ') FROM t4_segdir GROUP BY level}136 } {0 {0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15}}137 138 foreach {tn expect} {139 1 "0 {0 1 2 3 4 5 6 7 8 9 10 11 12 13} 1 0"140 2 "0 {0 1 2 3 4 5 6 7 8 9 10 11 12} 1 0"141 3 "0 {0 1 2 3 4 5 6 7 8 9 10 11} 1 0"142 4 "0 {0 1 2 3 4 5 6 7 8 9 10} 1 0"143 5 "0 {0 1 2 3 4 5 6 7 8 9} 1 0"144 6 "0 {0 1 2 3 4 5 6 7 8} 1 0"145 7 "0 {0 1 2 3 4 5 6 7} 1 0"146 8 "0 {0 1 2 3 4 5 6} 1 0"147 9 "0 {0 1 2 3 4 5} 1 0"148 } {149 do_execsql_test 4.3.$tn {150 INSERT INTO t4(t4) VALUES('merge=1,16');151 SELECT level, group_concat(idx, ' ') FROM t4_segdir GROUP BY level;152 } $expect153 }154 155 do_execsql_test 4.4.1 {156 SELECT quote(value) FROM t4_stat WHERE rowid=1157 } {X'0006'}158 159 sqlite3_db_config db DEFENSIVE 0160 do_execsql_test 4.4.2 {161 DELETE FROM t4_stat WHERE rowid=1;162 INSERT INTO t4(t4) VALUES('merge=1,12');163 SELECT level, string_agg(idx, ' ') FROM t4_segdir GROUP BY level;164 } "0 {0 1 2 3 4 5} 1 0"165 166 167 #-------------------------------------------------------------------------168 # Test cases 5.*169 #170 # Test that if a crisis-merge occurs that disrupts an ongoing incremental171 # merge, the next call to "merge=A,B" identifies this and starts a new172 # incremental merge. There are two scenarios:173 #174 # * There are less segments on the input level that the disrupted175 # incremental merge operated on, or176 # 177 # * Sufficient segments exist on the input level but the segments 178 # contain keys smaller than the largest key in the potential output 179 # segment.180 # 181 do_test 5.1 {182 reset_db183 fts3_build_db_1 -module $mod 1000184 } {}185 186 do_execsql_test 5.2 {187 SELECT level, group_concat(idx, ' ') FROM t1_segdir GROUP BY level;188 } {189 0 {0 1 2 3 4 5 6 7} 190 1 {0 1 2 3 4 5 6 7 8 9 10 11 12 13} 191 2 {0 1 2}192 }193 194 do_execsql_test 5.3 {195 INSERT INTO t1(t1) VALUES('merge=1,5');196 INSERT INTO t1(t1) VALUES('merge=1,5');197 SELECT level, string_agg(idx, ' ') FROM t1_segdir GROUP BY level;198 } {199 1 {0 1 2 3 4 5 6 7 8 9 10 11 12 13 14} 200 2 {0 1 2 3}201 }202 203 do_execsql_test 5.4 {SELECT quote(value) from t1_stat WHERE rowid=1} {X'010F'}204 do_test 5.5 {205 foreach docid [execsql {SELECT docid FROM t1}] {206 execsql {INSERT INTO t1 SELECT * FROM t1 WHERE docid=$docid}207 }208 } {}209 210 do_execsql_test 5.6 {SELECT quote(value) from t1_stat WHERE rowid=1} {X'010F'}211 212 do_execsql_test 5.7 {213 SELECT level, group_concat(idx, ' ') FROM t1_segdir GROUP BY level;214 SELECT quote(value) from t1_stat WHERE rowid=1;215 } {216 0 {0 1 2 3 4 5 6 7} 217 1 {0 1 2 3 4 5 6 7 8 9 10 11 12} 218 2 {0 1 2 3 4 5 6 7} 219 X'010F'220 }221 222 do_execsql_test 5.8 {223 INSERT INTO t1(t1) VALUES('merge=1,6');224 INSERT INTO t1(t1) VALUES('merge=1,6');225 SELECT level, group_concat(idx, ' ') FROM t1_segdir GROUP BY level;226 SELECT quote(value) from t1_stat WHERE rowid=1;227 } {228 1 {0 1 2 3 4 5 6 7 8 9 10 11 12 13} 229 2 {0 1 2 3 4 5 6 7 8} X'010E'230 }231 232 do_test 5.8.1 { fts3_integrity_check t1 } ok233 234 do_test 5.9 {235 set L [expr 16*16*7 + 16*3 + 12]236 foreach docid [execsql {237 SELECT docid FROM t1 UNION ALL SELECT docid FROM t1 LIMIT $L238 }] {239 execsql {INSERT INTO t1 SELECT * FROM t1 WHERE docid=$docid}240 }241 } {}242 243 do_execsql_test 5.10 {244 SELECT level, group_concat(idx, ' ') FROM t1_segdir GROUP BY level;245 SELECT quote(value) from t1_stat WHERE rowid=1;246 } {247 0 {0 1 2 3 4 5 6 7 8 9 10 11} 1 0 2 0 3 0 X'010E'248 }249 250 do_execsql_test 5.11 {251 INSERT INTO t1(t1) VALUES('merge=1,6');252 SELECT level, string_agg(idx, ' ') FROM t1_segdir GROUP BY level;253 SELECT quote(value) from t1_stat WHERE rowid=1;254 } {255 1 {0 1} 2 0 3 0 X'010E'256 }257 258 #-------------------------------------------------------------------------259 # Test cases 6.*260 #261 # At one point the following test caused an assert() to fail (because the262 # second 'merge=1,2' operation below actually "merges" a single input263 # segment, which was unexpected).264 #265 do_test 6.1 {266 reset_db267 set a [string repeat a 900]268 set b [string repeat b 900]269 set c [string repeat c 900]270 set d [string repeat d 900]271 272 execsql "CREATE VIRTUAL TABLE t1 USING $mod"273 execsql {274 BEGIN;275 INSERT INTO t1 VALUES($a);276 INSERT INTO t1 VALUES($b);277 COMMIT;278 BEGIN;279 INSERT INTO t1 VALUES($c);280 INSERT INTO t1 VALUES($d);281 COMMIT;282 }283 284 execsql {285 INSERT INTO t1(t1) VALUES('merge=1,2');286 INSERT INTO t1(t1) VALUES('merge=1,2');287 }288 } {}289 290 #-------------------------------------------------------------------------291 # Test cases 7.*292 #293 # Test that the value returned by sqlite3_total_changes() increases by294 # 1 following a no-op "merge=A,B", or by more than 1 if actual work is295 # performed.296 #297 do_test 7.0 {298 reset_db299 fts3_build_db_1 -module $mod 1000300 } {}301 302 do_execsql_test 7.1 {303 SELECT level, group_concat(idx, ' ') FROM t1_segdir GROUP BY level304 } {305 0 {0 1 2 3 4 5 6 7} 306 1 {0 1 2 3 4 5 6 7 8 9 10 11 12 13} 307 2 {0 1 2}308 }309 do_test 7.2 {310 set x [db total_changes]311 execsql { INSERT INTO t1(t1) VALUES('merge=2,10') }312 expr { ([db total_changes] - $x)>1 }313 } {1}314 do_test 7.3 {315 set x [db total_changes]316 execsql { INSERT INTO t1(t1) VALUES('merge=200,10') }317 expr { ([db total_changes] - $x)>1 }318 } {1}319 do_test 7.4 {320 set x [db total_changes]321 execsql { INSERT INTO t1(t1) VALUES('merge=200,10') }322 expr { ([db total_changes] - $x)>1 }323 } {0}324 do_test 7.5 {325 set x [db total_changes]326 execsql { INSERT INTO t1(t1) VALUES('merge=200,10') }327 expr { ([db total_changes] - $x)>1 }328 } {0}329}330 331#-------------------------------------------------------------------------332# Test cases 8.* - ticket [bf1aab89].333#334set testprefix fts4merge335reset_db336do_execsql_test 8.0 {337 CREATE VIRTUAL TABLE t1 USING fts4(a, order=DESC);338 INSERT INTO t1(a) VALUES (0);339 INSERT INTO t1(a) VALUES (0);340 UPDATE t1 SET a = NULL;341} 342 343do_execsql_test 8.1 {344 INSERT INTO t1(t1) VALUES('merge=1,4');345}346 347finish_test348