1# 2011 May 04
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 SQLite library.  The
12# focus of this script is testing the FTS3 module.
13#
14
15set testdir [file dirname $argv0]
16source $testdir/tester.tcl
17
18# If SQLITE_ENABLE_FTS3 is defined, omit this file.
19ifcapable !fts3 {
20  finish_test
21  return
22}
23
24
25proc build_database {nRow param} {
26  db close
27  forcedelete test.db
28  sqlite3 db test.db
29
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 fts3sort
45
46unset -nocomplain CONTROL
47foreach {t param} {
48  1     ""
49  2     "order=asc"
50  3     "order=desc"
51} {
52
53  set testprefix fts3sort-1.$t
54
55  set nRow 1000
56  do_test 1.0 {
57    build_database $nRow $param
58    execsql { SELECT count(*) FROM t1 }
59  } $nRow
60
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 D
79    set A_list [list]
80    set B_list [list]
81    set C_list [list]
82    set D_list [list]
83
84    unset -nocomplain X
85    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 X
90    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 X
95    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 X
100    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 DATA
111    unset -nocomplain X
112    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 CONTROL
129
130set testprefix fts3sort
131
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  " $res
148}
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_test
185