AryaWu/sqlite

1# 2011 May 042#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 FTS3 module.13#14 15set testdir [file dirname $argv0]16source $testdir/tester.tcl17 18# If SQLITE_ENABLE_FTS3 is defined, omit this file.19ifcapable !fts3 {20  finish_test21  return22}23 24 25proc build_database {nRow param} {26  db close27  forcedelete test.db28  sqlite3 db test.db29 30  set vocab [list    aa ab ac   ba bb bc    ca cb cc   da]31  expr srand(0)32 33  execsql "CREATE VIRTUAL TABLE t1 USING fts4($param)"34  for {set i 0} {$i < $nRow} {incr i} {35    set v [expr int(rand()*1000000)]36    set doc [list]37    for {set div 1} {$div < 1000000} {set div [expr $div*10]} {38      lappend doc [lindex $vocab [expr ($v/$div) % 10]]39    }40    execsql { INSERT INTO t1 VALUES($doc) }41  }42}43 44set testprefix fts3sort45 46unset -nocomplain CONTROL47foreach {t param} {48  1     ""49  2     "order=asc"50  3     "order=desc"51} {52 53  set testprefix fts3sort-1.$t54 55  set nRow 100056  do_test 1.0 {57    build_database $nRow $param58    execsql { SELECT count(*) FROM t1 }59  } $nRow60  61  foreach {tn query} {62  1   "SELECT docid, * FROM t1"63  2   "SELECT docid, * FROM t1 WHERE t1 MATCH 'aa'"64  3   "SELECT docid, * FROM t1 WHERE t1 MATCH 'a*'"65  4   "SELECT docid, quote(matchinfo(t1)) FROM t1 WHERE t1 MATCH 'a*'"66  5   "SELECT docid, quote(matchinfo(t1,'pcnxals')) FROM t1 WHERE t1 MATCH 'b*'"67  6   "SELECT docid, * FROM t1 WHERE t1 MATCH 'a* b* c*'"68  7   "SELECT docid, * FROM t1 WHERE t1 MATCH 'aa OR da'"69  8   "SELECT docid, * FROM t1 WHERE t1 MATCH 'nosuchtoken'"70  9   "SELECT docid, snippet(t1) FROM t1 WHERE t1 MATCH 'aa OR da'"71  10  "SELECT docid, snippet(t1) FROM t1 WHERE t1 MATCH 'aa OR nosuchtoken'"72  11  "SELECT docid, snippet(t1) FROM t1 WHERE t1 MATCH 'aa NEAR bb'"73  12  "SELECT docid, snippet(t1) FROM t1 WHERE t1 MATCH '\"aa bb\"'"74  13  "SELECT docid, content FROM t1 WHERE t1 MATCH 'aa NEAR/2 bb NEAR/3 cc'"75  14  "SELECT docid, content FROM t1 WHERE t1 MATCH '\"aa bb cc\"'"76  } {77  78    unset -nocomplain A B C D79    set A_list [list]80    set B_list [list]81    set C_list [list]82    set D_list [list]83  84    unset -nocomplain X85    db eval "$query ORDER BY rowid ASC"  X  { 86      set A($X(docid)) [array get X] 87      lappend A_list $X(docid)88    }89    unset -nocomplain X90    db eval "$query ORDER BY rowid DESC" X  { 91      set B($X(docid)) [array get X] 92      lappend B_list $X(docid)93    }94    unset -nocomplain X95    db eval "$query ORDER BY docid ASC"  X  { 96      set C($X(docid)) [array get X] 97      lappend C_list $X(docid)98    }99    unset -nocomplain X100    db eval "$query ORDER BY docid DESC" X  { 101      set D($X(docid)) [array get X] 102      lappend D_list $X(docid)103    }104  105    do_test $tn.1 { set A_list } [lsort -integer -increasing $A_list]106    do_test $tn.2 { set B_list } [lsort -integer -decreasing $B_list]107    do_test $tn.3 { set C_list } [lsort -integer -increasing $C_list]108    do_test $tn.4 { set D_list } [lsort -integer -decreasing $D_list]109  110    unset -nocomplain DATA111    unset -nocomplain X112    db eval "$query" X  { 113      set DATA($X(docid)) [array get X] 114    }115  116    do_test $tn.5 { lsort [array get A] } [lsort [array get DATA]]117    do_test $tn.6 { lsort [array get B] } [lsort [array get DATA]]118    do_test $tn.7 { lsort [array get C] } [lsort [array get DATA]]119    do_test $tn.8 { lsort [array get D] } [lsort [array get DATA]]120 121    if {[info exists CONTROL($tn)]} {122      do_test $tn.9 { set CONTROL($tn) } [lsort [array get DATA]]123    } else {124      set CONTROL($tn) [lsort [array get DATA]]125    }126  }127}128unset -nocomplain CONTROL129 130set testprefix fts3sort131 132#-------------------------------------------------------------------------133# Tests for parsing the "order=asc" and "order=desc" directives.134#135foreach {tn param res} {136  1 "order=asc"             {0 {}}137  2 "order=desc"            {0 {}}138  3 "order=dec"             {1 {unrecognized order: dec}}139  4 "order=xxx, order=asc"  {1 {unrecognized order: xxx}}140  5 "order=desc, order=asc" {0 {}}141  6 "order=xxxx, order=asc" {1 {unrecognized order: xxxx}}142  7 "order=desk"            {1 {unrecognized order: desk}}143} {144  execsql { DROP TABLE IF EXISTS t1 }145  do_catchsql_test 2.1.$tn "146    CREATE VIRTUAL TABLE t1 USING fts4(a, b, $param)147  " $res148}149 150do_execsql_test 2.2 {151  BEGIN;152    CREATE VIRTUAL TABLE t2 USING fts4(order=desc);153    INSERT INTO t2 VALUES('aa bb');154    INSERT INTO t2 VALUES('bb cc');155    INSERT INTO t2 VALUES('cc aa');156    SELECT docid FROM t2 WHERE t2 MATCH 'aa';157  END;158} {3 1}159do_execsql_test 2.3 {160  SELECT docid FROM t2 WHERE t2 MATCH 'aa';161} {3 1}162do_execsql_test 2.4 {163  SELECT docid FROM t2 WHERE t2 MATCH 'aa' ORDER BY content;164} {1 3}165 166#-------------------------------------------------------------------------167# Test that ticket [56be976859] has been fixed.168#169do_execsql_test 3.1 {170  CREATE VIRTUAL TABLE t3 USING fts4(x, order=DESC);171  INSERT INTO t3(docid, x) VALUES(113382409004785664, 'aa');172  INSERT INTO t3(docid, x) VALUES(1, 'ab');173  SELECT rowid FROM t3 WHERE x MATCH 'a*' ORDER BY docid DESC;174} {113382409004785664 1}175do_execsql_test 3.2 {176  CREATE VIRTUAL TABLE t4 USING fts4(x);177  INSERT INTO t4(docid, x) VALUES(-113382409004785664, 'aa');178  INSERT INTO t4(docid, x) VALUES(1, 'ab');179  SELECT rowid FROM t4 WHERE x MATCH 'a*';180} {-113382409004785664 1}181 182 183 184finish_test185