| OLD | NEW |
|---|
| (Empty) |
| 1 # 2013 June 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 # This file implements regression tests for SQLite library. The | |
| 12 # focus of this script is testing the notindexed=xxx FTS4 option. | |
| 13 # | |
| 14 | |
| 15 set testdir [file dirname $argv0] | |
| 16 source $testdir/tester.tcl | |
| 17 set ::testprefix fts4noti | |
| 18 | |
| 19 # If SQLITE_ENABLE_FTS3 is defined, omit this file. | |
| 20 ifcapable !fts3 { | |
| 21 finish_test | |
| 22 return | |
| 23 } | |
| 24 | |
| 25 | |
| 26 #------------------------------------------------------------------------- | |
| 27 # Test that typos in "notindexed=" column names are detected. | |
| 28 # | |
| 29 do_execsql_test 1.0 { | |
| 30 CREATE TABLE cc(a, b, c); | |
| 31 } | |
| 32 foreach {tn arg res} { | |
| 33 1 "(b, c, notindexed=a)" {1 {no such column: a}} | |
| 34 2 "(a, b, notindexed=a)" {0 {}} | |
| 35 3 "(a, b, notindexed=a, notindexed=a)" {0 {}} | |
| 36 4 "(notindexed=a, a, b)" {0 {}} | |
| 37 5 "(notindexed=a, notindexed=b, notindexed=c, a, b, c, d)" {0 {}} | |
| 38 6 "(notindexed=a, notindexed=B, notindexed=c, a, b, c, d)" {0 {}} | |
| 39 7 "(notindexed=a, notindexed=b, notindexed=c, a, B, c, d)" {0 {}} | |
| 40 8 "(notindexed=d, content=cc)" {1 {no such column: d}} | |
| 41 9 "(notindexed=a, content=cc)" {0 {}} | |
| 42 10 "(notindexed=a, notindexed=b, a)" {1 {no such column: b}} | |
| 43 11 "(notindexed=a, notindexed=b, b)" {1 {no such column: a}} | |
| 44 } { | |
| 45 do_catchsql_test 1.$tn "CREATE VIRTUAL TABLE t1 USING fts4 $arg" $res | |
| 46 if {[lindex $res 0]==0} { execsql "DROP TABLE t1" } | |
| 47 } | |
| 48 | |
| 49 do_execsql_test 1.x { SELECT name FROM sqlite_master } {cc} | |
| 50 | |
| 51 | |
| 52 #------------------------------------------------------------------------- | |
| 53 # Test that notindexed columns are not indexed. | |
| 54 # | |
| 55 foreach {tn schema} { | |
| 56 1 { | |
| 57 CREATE VIRTUAL TABLE t1 USING fts4(a, b, c, notindexed=b); | |
| 58 } | |
| 59 2 { | |
| 60 CREATE TABLE c1(a, b, c); | |
| 61 INSERT INTO c1 VALUES('one two', 'three four', 'five six'); | |
| 62 INSERT INTO c1 VALUES('three four', 'five six', 'one two'); | |
| 63 CREATE VIRTUAL TABLE t1 USING fts4(content=c1, notindexed=b); | |
| 64 } | |
| 65 3 { | |
| 66 CREATE VIRTUAL TABLE t1 USING fts4(content="", a, b, c, notindexed=b); | |
| 67 } | |
| 68 } { | |
| 69 execsql $schema | |
| 70 | |
| 71 do_execsql_test 2.$tn.1 { | |
| 72 INSERT INTO t1(docid,a,b,c) VALUES(1, 'one two', 'three four', 'five six'); | |
| 73 INSERT INTO t1(docid,a,b,c) VALUES(2, 'three four', 'five six', 'one two'); | |
| 74 } | |
| 75 | |
| 76 do_execsql_test 2.$tn.2 { SELECT docid FROM t1 WHERE t1 MATCH 'one' } {1 2} | |
| 77 do_execsql_test 2.$tn.3 { SELECT docid FROM t1 WHERE t1 MATCH 'three' } {2} | |
| 78 do_execsql_test 2.$tn.4 { SELECT docid FROM t1 WHERE t1 MATCH 'five' } {1} | |
| 79 | |
| 80 do_execsql_test 2.$tn.5 { INSERT INTO t1(t1) VALUES('optimize') } | |
| 81 | |
| 82 do_execsql_test 2.$tn.6 { SELECT docid FROM t1 WHERE t1 MATCH 'one' } {1 2} | |
| 83 do_execsql_test 2.$tn.7 { SELECT docid FROM t1 WHERE t1 MATCH 'three' } {2} | |
| 84 do_execsql_test 2.$tn.8 { SELECT docid FROM t1 WHERE t1 MATCH 'five' } {1} | |
| 85 | |
| 86 if {$tn!=3} { | |
| 87 do_execsql_test 2.$tn.9 { INSERT INTO t1(t1) VALUES('rebuild') } | |
| 88 | |
| 89 do_execsql_test 2.$tn.10 { SELECT docid FROM t1 WHERE t1 MATCH 'one' } {1 2} | |
| 90 do_execsql_test 2.$tn.11 { SELECT docid FROM t1 WHERE t1 MATCH 'three' } {2} | |
| 91 do_execsql_test 2.$tn.12 { SELECT docid FROM t1 WHERE t1 MATCH 'five' } {1} | |
| 92 | |
| 93 do_execsql_test 2.$tn.13 { | |
| 94 SELECT a,b,c FROM t1 WHERE docid=1 | |
| 95 } {{one two} {three four} {five six}} | |
| 96 do_execsql_test 2.$tn.14 { | |
| 97 SELECT a,b,c FROM t1 WHERE docid=2 | |
| 98 } {{three four} {five six} {one two}} | |
| 99 } | |
| 100 | |
| 101 do_execsql_test 2.x { DROP TABLE t1 } | |
| 102 } | |
| 103 | |
| 104 #------------------------------------------------------------------------- | |
| 105 # Test that notindexed columns are not scanned for deferred tokens. | |
| 106 # | |
| 107 | |
| 108 do_execsql_test 3.1 { | |
| 109 CREATE VIRTUAL TABLE t2 USING fts4(x, y, notindexed=x); | |
| 110 } | |
| 111 do_test 3.2 { | |
| 112 set v [string repeat " 1" 50000] | |
| 113 set v1 "x $v" | |
| 114 set v2 "y $v" | |
| 115 execsql { | |
| 116 INSERT INTO t2 VALUES(1, 'x y z'); | |
| 117 INSERT INTO t2 VALUES(2, $v1); | |
| 118 INSERT INTO t2 VALUES(3, $v2); | |
| 119 INSERT INTO t2 VALUES(4, $v2); | |
| 120 INSERT INTO t2 VALUES(5, $v2); | |
| 121 INSERT INTO t2 VALUES(6, $v2); | |
| 122 } | |
| 123 } {} | |
| 124 | |
| 125 do_execsql_test 3.3 { SELECT x FROM t2 WHERE t2 MATCH '2' } {} | |
| 126 do_execsql_test 3.4 { SELECT x FROM t2 WHERE t2 MATCH '1' } {2 3 4 5 6} | |
| 127 do_execsql_test 3.5 { SELECT x FROM t2 WHERE t2 MATCH 'x' } {1 2} | |
| 128 do_execsql_test 3.6 { SELECT x FROM t2 WHERE t2 MATCH 'x 1' } {2} | |
| 129 | |
| 130 do_execsql_test 3.x { DROP TABLE t2 } | |
| 131 | |
| 132 #------------------------------------------------------------------------- | |
| 133 # Test that the types of notindexed columns are not modified. | |
| 134 # | |
| 135 do_execsql_test 4.1 { | |
| 136 CREATE VIRTUAL TABLE t2 USING fts4(poi, addr, notindexed=poi); | |
| 137 INSERT INTO t2 VALUES(114, 'x x x'); | |
| 138 INSERT INTO t2 VALUES(X'1234', 'y y y'); | |
| 139 INSERT INTO t2 VALUES(NULL, 'z z z'); | |
| 140 INSERT INTO t2 VALUES(113.2, 'w w w'); | |
| 141 INSERT INTO t2 VALUES('poi', 'v v v'); | |
| 142 } | |
| 143 do_execsql_test 4.2 { SELECT typeof(poi) FROM t2 WHERE t2 MATCH 'x' } {integer} | |
| 144 do_execsql_test 4.3 { SELECT typeof(poi) FROM t2 WHERE t2 MATCH 'y' } {blob} | |
| 145 do_execsql_test 4.4 { SELECT typeof(poi) FROM t2 WHERE t2 MATCH 'z' } {null} | |
| 146 do_execsql_test 4.5 { SELECT typeof(poi) FROM t2 WHERE t2 MATCH 'w' } {real} | |
| 147 do_execsql_test 4.6 { SELECT typeof(poi) FROM t2 WHERE t2 MATCH 'v' } {text} | |
| 148 | |
| 149 do_execsql_test 4.x { DROP TABLE t2 } | |
| 150 | |
| 151 #------------------------------------------------------------------------- | |
| 152 # Test that multiple notindexed options on a single table work as expected. | |
| 153 # | |
| 154 do_execsql_test 5.1 { | |
| 155 CREATE VIRTUAL TABLE t2 USING fts4( | |
| 156 notindexed="three", one, two, three, notindexed="one", | |
| 157 ); | |
| 158 INSERT INTO t2 VALUES('a', 'b', 'c'); | |
| 159 INSERT INTO t2 VALUES('c', 'a', 'b'); | |
| 160 INSERT INTO t2 VALUES('b', 'c', 'a'); | |
| 161 } | |
| 162 do_execsql_test 5.2 { SELECT docid FROM t2 WHERE t2 MATCH 'a' } {2} | |
| 163 do_execsql_test 5.3 { SELECT docid FROM t2 WHERE t2 MATCH 'b' } {1} | |
| 164 do_execsql_test 5.4 { SELECT docid FROM t2 WHERE t2 MATCH 'c' } {3} | |
| 165 | |
| 166 do_execsql_test 5.x { DROP TABLE t2 } | |
| 167 | |
| 168 #------------------------------------------------------------------------- | |
| 169 # Check that if an indexed column name is a prefix of a notindexed column | |
| 170 # name, the column is still correctly tokenized. This was a problem at one | |
| 171 # point. | |
| 172 do_execsql_test 6.1.1 { | |
| 173 CREATE VIRTUAL TABLE t1 USING fts4( | |
| 174 poiCategory, poiCategoryId, notindexed=poiCategoryId | |
| 175 ); | |
| 176 INSERT INTO t1(poiCategory, poiCategoryId) values ("Restaurant", 6021); | |
| 177 } | |
| 178 | |
| 179 do_execsql_test 6.1.2 { | |
| 180 SELECT * FROM t1 WHERE t1 MATCH 'restaurant'; | |
| 181 } { Restaurant 6021 } | |
| 182 do_execsql_test 6.1.3 { | |
| 183 SELECT * FROM t1 WHERE t1 MATCH 're*'; | |
| 184 } { Restaurant 6021 } | |
| 185 do_execsql_test 6.1.4 { | |
| 186 SELECT * FROM t1 WHERE t1 MATCH '6021'; | |
| 187 } {} | |
| 188 do_execsql_test 6.1.5 { | |
| 189 SELECT * FROM t1 WHERE t1 MATCH '60*'; | |
| 190 } {} | |
| 191 | |
| 192 do_execsql_test 6.2.1 { | |
| 193 DROP TABLE t1; | |
| 194 CREATE VIRTUAL TABLE t1 USING fts4( | |
| 195 poiCategory, poiCategoryId, notindexed=poiCategory | |
| 196 ); | |
| 197 INSERT INTO t1(poiCategory, poiCategoryId) values ("Restaurant", 6021); | |
| 198 } | |
| 199 | |
| 200 do_execsql_test 6.2.2 { | |
| 201 SELECT * FROM t1 WHERE t1 MATCH 'restaurant'; | |
| 202 } {} | |
| 203 do_execsql_test 6.2.3 { | |
| 204 SELECT * FROM t1 WHERE t1 MATCH 're*'; | |
| 205 } {} | |
| 206 do_execsql_test 6.2.4 { | |
| 207 SELECT * FROM t1 WHERE t1 MATCH '6021'; | |
| 208 } { Restaurant 6021 } | |
| 209 do_execsql_test 6.2.5 { | |
| 210 SELECT * FROM t1 WHERE t1 MATCH '60*'; | |
| 211 } { Restaurant 6021 } | |
| 212 | |
| 213 do_execsql_test 6.3.1 { | |
| 214 DROP TABLE t1; | |
| 215 CREATE VIRTUAL TABLE t1 USING fts4(abc, ab, a, notindexed=abc); | |
| 216 CREATE VIRTUAL TABLE t2 USING fts4(a, ab, abc, notindexed=abc); | |
| 217 | |
| 218 INSERT INTO t1 VALUES('no', 'yes', 'yep'); | |
| 219 INSERT INTO t2 VALUES('yep', 'yes', 'no'); | |
| 220 | |
| 221 SELECT count(*) FROM t1 WHERE t1 MATCH 'no'; | |
| 222 SELECT count(*) FROM t1 WHERE t1 MATCH 'yes'; | |
| 223 SELECT count(*) FROM t1 WHERE t1 MATCH 'yep'; | |
| 224 | |
| 225 SELECT count(*) FROM t2 WHERE t2 MATCH 'no'; | |
| 226 SELECT count(*) FROM t2 WHERE t2 MATCH 'yes'; | |
| 227 SELECT count(*) FROM t2 WHERE t2 MATCH 'yep'; | |
| 228 } {0 1 1 0 1 1} | |
| 229 | |
| 230 finish_test | |
| 231 | |
| 232 | |
| 233 | |
| OLD | NEW |
|---|
Chromium Code Reviews
Issue 2363173002:
[sqlite] Remove obsolete reference version 3.8.7.4. (Closed)
