AryaWu/sqlite

1# 2013-09-302#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 "approximate_match" virtual12# table that is in the "amatch.c" extension.13#14#15 16set testdir [file dirname $argv0]17source $testdir/tester.tcl18 19# If SQLITE_ENABLE_FTS4 is defined, omit this file.20ifcapable !fts3 {21  finish_test22  return23}24 25# Create the fts_kjv_genesis procedure which fills and FTS3/4 table with26# the complete text of the Book of Genesis.27#28source $testdir/genesis.tcl29 30 31 32do_test amatch1-1.0 {33  db eval {34    CREATE VIRTUAL TABLE t1 USING fts4(words); --, tokenize porter);35  }36  fts_kjv_genesis37  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 541  }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 546  }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 551  }52} {baalhanan babel back backward bad}53 54# Load the amatch extension55load_static_extension db amatch56 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=costs68    );69    CREATE VIRTUAL TABLE t3 USING approximate_match(70      vocabulary_table=vocab,71      vocabulary_word=w,72      edit_distances=costs73    );74    CREATE VIRTUAL TABLE t4 USING approximate_match(75        vocabulary_table=vtemp,76        vocabulary_word=w,77        edit_distances=costs78      );79} {}80puts "Query against fts4aux: [time {81  do_execsql_test amatch1-2.1 {82      SELECT word, distance FROM t283       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 t388       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 t498       WHERE word MATCH 'josxph' AND distance<300;99  } {joseph 150}} 1]"100do_execsql_test amatch1-2.11 {101    SELECT word, distance FROM t2102     WHERE word MATCH 'joxxph' AND distance<=300;103} {joseph 300}104do_execsql_test amatch1-2.12 {105    SELECT word, distance FROM t3106     WHERE word MATCH 'joxxph' AND distance<=300;107} {joseph 300}108do_execsql_test amatch1-2.21 {109    SELECT word, distance FROM t2110     WHERE word MATCH 'joxxph' AND distance<300;111} {}112do_execsql_test amatch1-2.22 {113    SELECT word, distance FROM t3114     WHERE word MATCH 'joxxph' AND distance<300;115} {}116 117finish_test118