AryaWu/sqlite
0
1# 2013 June 212#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 notindexed=xxx FTS4 option.13#14 15set testdir [file dirname $argv0]16source $testdir/tester.tcl17set ::testprefix fts4noti18 19# If SQLITE_ENABLE_FTS3 is defined, omit this file.20ifcapable !fts3 {21 finish_test22 return23}24 25 26#-------------------------------------------------------------------------27# Test that typos in "notindexed=" column names are detected.28#29do_execsql_test 1.0 {30 CREATE TABLE cc(a, b, c);31}32foreach {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" $res46 if {[lindex $res 0]==0} { execsql "DROP TABLE t1" }47}48 49do_execsql_test 1.x { SELECT name FROM sqlite_master } {cc}50 51 52#-------------------------------------------------------------------------53# Test that notindexed columns are not indexed.54#55foreach {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 $schema70 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=195 } {{one two} {three four} {five six}}96 do_execsql_test 2.$tn.14 { 97 SELECT a,b,c FROM t1 WHERE docid=298 } {{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 108do_execsql_test 3.1 {109 CREATE VIRTUAL TABLE t2 USING fts4(x, y, notindexed=x);110}111do_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 125do_execsql_test 3.3 { SELECT x FROM t2 WHERE t2 MATCH '2' } {}126do_execsql_test 3.4 { SELECT x FROM t2 WHERE t2 MATCH '1' } {2 3 4 5 6}127do_execsql_test 3.5 { SELECT x FROM t2 WHERE t2 MATCH 'x' } {1 2}128do_execsql_test 3.6 { SELECT x FROM t2 WHERE t2 MATCH 'x 1' } {2}129 130do_execsql_test 3.x { DROP TABLE t2 }131 132#-------------------------------------------------------------------------133# Test that the types of notindexed columns are not modified.134#135do_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}143do_execsql_test 4.2 { SELECT typeof(poi) FROM t2 WHERE t2 MATCH 'x' } {integer}144do_execsql_test 4.3 { SELECT typeof(poi) FROM t2 WHERE t2 MATCH 'y' } {blob}145do_execsql_test 4.4 { SELECT typeof(poi) FROM t2 WHERE t2 MATCH 'z' } {null}146do_execsql_test 4.5 { SELECT typeof(poi) FROM t2 WHERE t2 MATCH 'w' } {real}147do_execsql_test 4.6 { SELECT typeof(poi) FROM t2 WHERE t2 MATCH 'v' } {text}148 149do_execsql_test 4.x { DROP TABLE t2 }150 151#-------------------------------------------------------------------------152# Test that multiple notindexed options on a single table work as expected.153#154do_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}162do_execsql_test 5.2 { SELECT docid FROM t2 WHERE t2 MATCH 'a' } {2}163do_execsql_test 5.3 { SELECT docid FROM t2 WHERE t2 MATCH 'b' } {1}164do_execsql_test 5.4 { SELECT docid FROM t2 WHERE t2 MATCH 'c' } {3}165 166do_execsql_test 5.x { DROP TABLE t2 }167 168#-------------------------------------------------------------------------169# Check that if an indexed column name is a prefix of a notindexed column170# name, the column is still correctly tokenized. This was a problem at one171# point.172do_execsql_test 6.1.1 {173 CREATE VIRTUAL TABLE t1 USING fts4(174 poiCategory, poiCategoryId, notindexed=poiCategoryId175 );176 INSERT INTO t1(poiCategory, poiCategoryId) values ('Restaurant', 6021);177}178 179do_execsql_test 6.1.2 {180 SELECT * FROM t1 WHERE t1 MATCH 'restaurant';181} { Restaurant 6021 }182do_execsql_test 6.1.3 {183 SELECT * FROM t1 WHERE t1 MATCH 're*';184} { Restaurant 6021 }185do_execsql_test 6.1.4 {186 SELECT * FROM t1 WHERE t1 MATCH '6021';187} {}188do_execsql_test 6.1.5 {189 SELECT * FROM t1 WHERE t1 MATCH '60*';190} {}191 192do_execsql_test 6.2.1 {193 DROP TABLE t1;194 CREATE VIRTUAL TABLE t1 USING fts4(195 poiCategory, poiCategoryId, notindexed=poiCategory196 );197 INSERT INTO t1(poiCategory, poiCategoryId) values ('Restaurant', 6021);198}199 200do_execsql_test 6.2.2 {201 SELECT * FROM t1 WHERE t1 MATCH 'restaurant';202} {}203do_execsql_test 6.2.3 {204 SELECT * FROM t1 WHERE t1 MATCH 're*';205} {}206do_execsql_test 6.2.4 {207 SELECT * FROM t1 WHERE t1 MATCH '6021';208} { Restaurant 6021 }209do_execsql_test 6.2.5 {210 SELECT * FROM t1 WHERE t1 MATCH '60*';211} { Restaurant 6021 }212 213do_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 230finish_test231