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