| 1 |
# 2013-09-30 |
| 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 "approximate_match" virtual |
| 12 |
# table that is in the "amatch.c" extension. |
| 13 |
# |
| 14 |
# |
| 15 |
|
| 16 |
set testdir [file dirname $argv0] |
| 17 |
source $testdir/tester.tcl |
| 18 |
|
| 19 |
# If SQLITE_ENABLE_FTS4 is defined, omit this file. |
| 20 |
ifcapable !fts3 { |
| 21 |
finish_test |
| 22 |
return |
| 23 |
} |
| 24 |
|
| 25 |
# Create the fts_kjv_genesis procedure which fills and FTS3/4 table with |
| 26 |
# the complete text of the Book of Genesis. |
| 27 |
# |
| 28 |
source $testdir/genesis.tcl |
| 29 |
|
| 30 |
|
| 31 |
|
| 32 |
do_test amatch1-1.0 { |
| 33 |
db eval { |
| 34 |
CREATE VIRTUAL TABLE t1 USING fts4(words); --, tokenize porter); |
| 35 |
} |
| 36 |
fts_kjv_genesis |
| 37 |
db eval { |
| 38 |
INSERT INTO t1(t1) VALUES('optimize'); |
| 39 |
CREATE VIRTUAL TABLE temp.t1aux USING fts4aux(main, t1); |
| 40 |
SELECT term FROM t1aux WHERE col=0 ORDER BY 1 LIMIT 5 |
| 41 |
} |
| 42 |
} {a abated abel abelmizraim abidah} |
| 43 |
do_test amatch1-1.1 { |
| 44 |
db eval { |
| 45 |
SELECT term FROM t1aux WHERE term>'b' AND col=0 ORDER BY 1 LIMIT 5 |
| 46 |
} |
| 47 |
} {baalhanan babel back backward bad} |
| 48 |
do_test amatch1-1.2 { |
| 49 |
db eval { |
| 50 |
SELECT term FROM t1aux WHERE term>'b' AND col=0 LIMIT 5 |
| 51 |
} |
| 52 |
} {baalhanan babel back backward bad} |
| 53 |
|
| 54 |
# Load the amatch extension |
| 55 |
load_static_extension db amatch |
| 56 |
|
| 57 |
do_execsql_test amatch1-2.0 { |
| 58 |
CREATE TABLE costs(iLang, cFrom, cTo, Cost); |
| 59 |
INSERT INTO costs VALUES(0, '', '?', 100); |
| 60 |
INSERT INTO costs VALUES(0, '?', '', 100); |
| 61 |
INSERT INTO costs VALUES(0, '?', '?', 150); |
| 62 |
CREATE TABLE vocab(w TEXT UNIQUE); |
| 63 |
INSERT OR IGNORE INTO vocab SELECT term FROM t1aux; |
| 64 |
CREATE VIRTUAL TABLE t2 USING approximate_match( |
| 65 |
vocabulary_table=t1aux, |
| 66 |
vocabulary_word=term, |
| 67 |
edit_distances=costs |
| 68 |
); |
| 69 |
CREATE VIRTUAL TABLE t3 USING approximate_match( |
| 70 |
vocabulary_table=vocab, |
| 71 |
vocabulary_word=w, |
| 72 |
edit_distances=costs |
| 73 |
); |
| 74 |
CREATE VIRTUAL TABLE t4 USING approximate_match( |
| 75 |
vocabulary_table=vtemp, |
| 76 |
vocabulary_word=w, |
| 77 |
edit_distances=costs |
| 78 |
); |
| 79 |
} {} |
| 80 |
puts "Query against fts4aux: [time { |
| 81 |
do_execsql_test amatch1-2.1 { |
| 82 |
SELECT word, distance FROM t2 |
| 83 |
WHERE word MATCH 'josxph' AND distance<300; |
| 84 |
} {joseph 150}} 1]" |
| 85 |
puts "Query against ordinary table: [time { |
| 86 |
do_execsql_test amatch1-2.2 { |
| 87 |
SELECT word, distance FROM t3 |
| 88 |
WHERE word MATCH 'josxph' AND distance<300; |
| 89 |
} {joseph 150}} 1]" |
| 90 |
puts "Temp table initialized from fts4aux: [time { |
| 91 |
do_execsql_test amatch1-2.3a { |
| 92 |
CREATE TEMP TABLE vtemp(w TEXT UNIQUE); |
| 93 |
INSERT OR IGNORE INTO vtemp SELECT term FROM t1aux; |
| 94 |
} {}} 1]" |
| 95 |
puts "Query against temp table: [time { |
| 96 |
do_execsql_test amatch1-2.3b { |
| 97 |
SELECT word, distance FROM t4 |
| 98 |
WHERE word MATCH 'josxph' AND distance<300; |
| 99 |
} {joseph 150}} 1]" |
| 100 |
do_execsql_test amatch1-2.11 { |
| 101 |
SELECT word, distance FROM t2 |
| 102 |
WHERE word MATCH 'joxxph' AND distance<=300; |
| 103 |
} {joseph 300} |
| 104 |
do_execsql_test amatch1-2.12 { |
| 105 |
SELECT word, distance FROM t3 |
| 106 |
WHERE word MATCH 'joxxph' AND distance<=300; |
| 107 |
} {joseph 300} |
| 108 |
do_execsql_test amatch1-2.21 { |
| 109 |
SELECT word, distance FROM t2 |
| 110 |
WHERE word MATCH 'joxxph' AND distance<300; |
| 111 |
} {} |
| 112 |
do_execsql_test amatch1-2.22 { |
| 113 |
SELECT word, distance FROM t3 |
| 114 |
WHERE word MATCH 'joxxph' AND distance<300; |
| 115 |
} {} |
| 116 |
|
| 117 |
finish_test |