1# 2012 March 06 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 incremental merge function. 13# 14 15set testdir [file dirname $argv0] 16source $testdir/tester.tcl 17source $testdir/fts3_common.tcl 18 19# If SQLITE_ENABLE_FTS3 is defined, omit this file. 20ifcapable !fts3 { 21 finish_test 22 return 23} 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-$mod 32 reset_db 33 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, group_concat(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 } ok 50 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 } ok 64 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, group_concat(idx, ' ') FROM t1_segdir GROUP BY level 71 } { 72 3 0 73 } 74 75 #------------------------------------------------------------------------- 76 # Test cases 2.* test that errors in the xxx part of the 'merge=xxx' are 77 # 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_db 100 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, group_concat(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 0 119 } 120 121 #------------------------------------------------------------------------- 122 # Test cases 4.* 123 # 124 reset_db 125 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, group_concat(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 } $expect 153 } 154 155 do_execsql_test 4.4.1 { 156 SELECT quote(value) FROM t4_stat WHERE rowid=1 157 } {X'0006'} 158 159 sqlite3_db_config db DEFENSIVE 0 160 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, group_concat(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 incremental 171 # merge, the next call to "merge=A,B" identifies this and starts a new 172 # incremental merge. There are two scenarios: 173 # 174 # * There are less segments on the input level that the disrupted 175 # incremental merge operated on, or 176 # 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_db 183 fts3_build_db_1 -module $mod 1000 184 } {} 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, group_concat(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 } ok 233 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 $L 238 }] { 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, group_concat(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 the 262 # second 'merge=1,2' operation below actually "merges" a single input 263 # segment, which was unexpected). 264 # 265 do_test 6.1 { 266 reset_db 267 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 by 294 # 1 following a no-op "merge=A,B", or by more than 1 if actual work is 295 # performed. 296 # 297 do_test 7.0 { 298 reset_db 299 fts3_build_db_1 -module $mod 1000 300 } {} 301 302 do_execsql_test 7.1 { 303 SELECT level, group_concat(idx, ' ') FROM t1_segdir GROUP BY level 304 } { 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 fts4merge 335reset_db 336do_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_test 348