drh | 92054fe | 2013-09-30 17:37:15 +0000 | [diff] [blame] | 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 { |
drh | 3b6cb3c | 2013-09-30 19:05:03 +0000 | [diff] [blame] | 38 | INSERT INTO t1(t1) VALUES('optimize'); |
drh | 92054fe | 2013-09-30 17:37:15 +0000 | [diff] [blame] | 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 | |
drh | 3b6cb3c | 2013-09-30 19:05:03 +0000 | [diff] [blame] | 57 | do_execsql_test amatch1-2.0 { |
drh | 92054fe | 2013-09-30 17:37:15 +0000 | [diff] [blame] | 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); |
drh | 3b6cb3c | 2013-09-30 19:05:03 +0000 | [diff] [blame] | 62 | CREATE TABLE vocab(w TEXT UNIQUE); |
| 63 | INSERT OR IGNORE INTO vocab SELECT term FROM t1aux; |
drh | 92054fe | 2013-09-30 17:37:15 +0000 | [diff] [blame] | 64 | CREATE VIRTUAL TABLE t2 USING approximate_match( |
| 65 | vocabulary_table=t1aux, |
| 66 | vocabulary_word=term, |
| 67 | edit_distances=costs |
| 68 | ); |
drh | 3b6cb3c | 2013-09-30 19:05:03 +0000 | [diff] [blame] | 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 { |
drh | 92054fe | 2013-09-30 17:37:15 +0000 | [diff] [blame] | 101 | SELECT word, distance FROM t2 |
drh | 3b6cb3c | 2013-09-30 19:05:03 +0000 | [diff] [blame] | 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 | } {} |
drh | 92054fe | 2013-09-30 17:37:15 +0000 | [diff] [blame] | 116 | |
| 117 | finish_test |