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 16set testdir [file dirname $argv0] 17source $testdir/tester.tcl 18 19# If SQLITE_ENABLE_FTS4 is defined, omit this file. 20ifcapable !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# 28source $testdir/genesis.tcl 29 30 31 32do_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} 43do_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} 48do_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 55load_static_extension db amatch 56 57do_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} {} 80puts "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]" 85puts "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]" 90puts "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]" 95puts "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]" 100do_execsql_test amatch1-2.11 { 101 SELECT word, distance FROM t2 102 WHERE word MATCH 'joxxph' AND distance<=300; 103} {joseph 300} 104do_execsql_test amatch1-2.12 { 105 SELECT word, distance FROM t3 106 WHERE word MATCH 'joxxph' AND distance<=300; 107} {joseph 300} 108do_execsql_test amatch1-2.21 { 109 SELECT word, distance FROM t2 110 WHERE word MATCH 'joxxph' AND distance<300; 111} {} 112do_execsql_test amatch1-2.22 { 113 SELECT word, distance FROM t3 114 WHERE word MATCH 'joxxph' AND distance<300; 115} {} 116 117finish_test 118