1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
|
# 2007 June 20
#
# The author disclaims copyright to this source code. In place of
# a legal notice, here is a blessing:
#
# May you do good and not evil.
# May you find forgiveness for yourself and forgive others.
# May you share freely, never taking more than you give.
#
#*************************************************************************
# This file implements regression tests for SQLite library. The
# focus of this script is testing the FTS2 module.
#
# $Id: fts2o.test,v 1.4 2007/07/02 10:16:50 danielk1977 Exp $
#
set testdir [file dirname $argv0]
source $testdir/tester.tcl
# If SQLITE_ENABLE_FTS2 is not defined, omit this file.
ifcapable !fts2 {
finish_test
return
}
#---------------------------------------------------------------------
# These tests, fts2o-1.*, test that ticket #2429 is fixed.
#
db eval {
CREATE VIRTUAL TABLE t1 USING fts2(a, b, c);
INSERT INTO t1(a, b, c) VALUES('one three four', 'one four', 'one four two');
}
do_test fts2o-1.1 {
execsql {
SELECT rowid, snippet(t1) FROM t1 WHERE c MATCH 'four';
}
} {1 {one <b>four</b> two}}
do_test fts2o-1.2 {
execsql {
SELECT rowid, snippet(t1) FROM t1 WHERE b MATCH 'four';
}
} {1 {one <b>four</b>}}
do_test fts2o-1.3 {
execsql {
SELECT rowid, snippet(t1) FROM t1 WHERE a MATCH 'four';
}
} {1 {one three <b>four</b>}}
#---------------------------------------------------------------------
# Test that it is possible to rename an fts2 table.
#
do_test fts2o-2.1 {
execsql { SELECT tbl_name FROM sqlite_master WHERE type = 'table'}
} {t1 t1_content t1_segments t1_segdir}
do_test fts2o-2.2 {
execsql { ALTER TABLE t1 RENAME to fts_t1; }
} {}
do_test fts2o-2.3 {
execsql { SELECT rowid, snippet(fts_t1) FROM fts_t1 WHERE a MATCH 'four'; }
} {1 {one three <b>four</b>}}
do_test fts2o-2.4 {
execsql { SELECT tbl_name FROM sqlite_master WHERE type = 'table'}
} {fts_t1 fts_t1_content fts_t1_segments fts_t1_segdir}
# See what happens when renaming the fts2 table fails.
#
do_test fts2o-2.5 {
catchsql {
CREATE TABLE t1_segdir(a, b, c);
ALTER TABLE fts_t1 RENAME to t1;
}
} {1 {SQL logic error or missing database}}
do_test fts2o-2.6 {
execsql { SELECT rowid, snippet(fts_t1) FROM fts_t1 WHERE a MATCH 'four'; }
} {1 {one three <b>four</b>}}
do_test fts2o-2.7 {
execsql { SELECT tbl_name FROM sqlite_master WHERE type = 'table'}
} {fts_t1 fts_t1_content fts_t1_segments fts_t1_segdir t1_segdir}
# See what happens when renaming the fts2 table fails inside a transaction.
#
do_test fts2o-2.8 {
execsql {
BEGIN;
INSERT INTO fts_t1(a, b, c) VALUES('one two three', 'one four', 'one two');
}
} {}
do_test fts2o-2.9 {
catchsql {
ALTER TABLE fts_t1 RENAME to t1;
}
} {1 {SQL logic error or missing database}}
do_test fts2o-2.10 {
execsql { SELECT rowid, snippet(fts_t1) FROM fts_t1 WHERE a MATCH 'four'; }
} {1 {one three <b>four</b>}}
do_test fts2o-2.11 {
execsql { SELECT tbl_name FROM sqlite_master WHERE type = 'table'}
} {fts_t1 fts_t1_content fts_t1_segments fts_t1_segdir t1_segdir}
do_test fts2o-2.12 {
execsql COMMIT
execsql {SELECT a FROM fts_t1}
} {{one three four} {one two three}}
do_test fts2o-2.12 {
execsql { SELECT a, b, c FROM fts_t1 WHERE c MATCH 'four'; }
} {{one three four} {one four} {one four two}}
#-------------------------------------------------------------------
# Close, delete and reopen the database. The following test should
# be run on an initially empty db.
#
db close
forcedelete test.db test.db-journal
sqlite3 db test.db
do_test fts2o-3.1 {
execsql {
CREATE VIRTUAL TABLE t1 USING fts2(a, b, c);
INSERT INTO t1(a, b, c) VALUES('one three four', 'one four', 'one two');
SELECT a, b, c FROM t1 WHERE c MATCH 'two';
}
} {{one three four} {one four} {one two}}
# This test was crashing at one point.
#
do_test fts2o-3.2 {
execsql {
SELECT a, b, c FROM t1 WHERE c MATCH 'two';
CREATE TABLE t3(a, b, c);
SELECT a, b, c FROM t1 WHERE c MATCH 'two';
}
} {{one three four} {one four} {one two} {one three four} {one four} {one two}}
#---------------------------------------------------------------------
# Test that it is possible to rename an fts2 table in an attached
# database.
#
forcedelete test2.db test2.db-journal
do_test fts2o-3.1 {
execsql {
ATTACH 'test2.db' AS aux;
CREATE VIRTUAL TABLE aux.t1 USING fts2(a, b, c);
INSERT INTO aux.t1(a, b, c) VALUES(
'neung song sahm', 'neung see', 'neung see song'
);
}
} {}
do_test fts2o-3.2 {
execsql { SELECT a, b, c FROM aux.t1 WHERE a MATCH 'song'; }
} {{neung song sahm} {neung see} {neung see song}}
do_test fts2o-3.3 {
execsql { SELECT a, b, c FROM t1 WHERE c MATCH 'two'; }
} {{one three four} {one four} {one two}}
do_test fts2o-3.4 {
execsql { ALTER TABLE aux.t1 RENAME TO t2 }
} {}
do_test fts2o-3.2 {
execsql { SELECT a, b, c FROM t2 WHERE a MATCH 'song'; }
} {{neung song sahm} {neung see} {neung see song}}
do_test fts2o-3.3 {
execsql { SELECT a, b, c FROM t1 WHERE c MATCH 'two'; }
} {{one three four} {one four} {one two}}
finish_test
|
