3
# The author disclaims copyright to this source code. In place of
4
# a legal notice, here is a blessing:
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.
10
#*************************************************************************
11
# This file implements regression tests for SQLite library. The
12
# focus of this script is testing the FTS2 module.
14
# $Id: fts2o.test,v 1.4 2007/07/02 10:16:50 danielk1977 Exp $
17
set testdir [file dirname $argv0]
18
source $testdir/tester.tcl
20
# If SQLITE_ENABLE_FTS2 is not defined, omit this file.
26
#---------------------------------------------------------------------
27
# These tests, fts2o-1.*, test that ticket #2429 is fixed.
30
CREATE VIRTUAL TABLE t1 USING fts2(a, b, c);
31
INSERT INTO t1(a, b, c) VALUES('one three four', 'one four', 'one four two');
35
SELECT rowid, snippet(t1) FROM t1 WHERE c MATCH 'four';
37
} {1 {one <b>four</b> two}}
40
SELECT rowid, snippet(t1) FROM t1 WHERE b MATCH 'four';
42
} {1 {one <b>four</b>}}
45
SELECT rowid, snippet(t1) FROM t1 WHERE a MATCH 'four';
47
} {1 {one three <b>four</b>}}
49
#---------------------------------------------------------------------
50
# Test that it is possible to rename an fts2 table.
53
execsql { SELECT tbl_name FROM sqlite_master WHERE type = 'table'}
54
} {t1 t1_content t1_segments t1_segdir}
56
execsql { ALTER TABLE t1 RENAME to fts_t1; }
59
execsql { SELECT rowid, snippet(fts_t1) FROM fts_t1 WHERE a MATCH 'four'; }
60
} {1 {one three <b>four</b>}}
62
execsql { SELECT tbl_name FROM sqlite_master WHERE type = 'table'}
63
} {fts_t1 fts_t1_content fts_t1_segments fts_t1_segdir}
65
# See what happens when renaming the fts2 table fails.
69
CREATE TABLE t1_segdir(a, b, c);
70
ALTER TABLE fts_t1 RENAME to t1;
72
} {1 {SQL logic error or missing database}}
74
execsql { SELECT rowid, snippet(fts_t1) FROM fts_t1 WHERE a MATCH 'four'; }
75
} {1 {one three <b>four</b>}}
77
execsql { SELECT tbl_name FROM sqlite_master WHERE type = 'table'}
78
} {fts_t1 fts_t1_content fts_t1_segments fts_t1_segdir t1_segdir}
80
# See what happens when renaming the fts2 table fails inside a transaction.
85
INSERT INTO fts_t1(a, b, c) VALUES('one two three', 'one four', 'one two');
90
ALTER TABLE fts_t1 RENAME to t1;
92
} {1 {SQL logic error or missing database}}
94
execsql { SELECT rowid, snippet(fts_t1) FROM fts_t1 WHERE a MATCH 'four'; }
95
} {1 {one three <b>four</b>}}
97
execsql { SELECT tbl_name FROM sqlite_master WHERE type = 'table'}
98
} {fts_t1 fts_t1_content fts_t1_segments fts_t1_segdir t1_segdir}
101
execsql {SELECT a FROM fts_t1}
102
} {{one three four} {one two three}}
104
execsql { SELECT a, b, c FROM fts_t1 WHERE c MATCH 'four'; }
105
} {{one three four} {one four} {one four two}}
107
#-------------------------------------------------------------------
108
# Close, delete and reopen the database. The following test should
109
# be run on an initially empty db.
112
file delete -force test.db test.db-journal
117
CREATE VIRTUAL TABLE t1 USING fts2(a, b, c);
118
INSERT INTO t1(a, b, c) VALUES('one three four', 'one four', 'one two');
119
SELECT a, b, c FROM t1 WHERE c MATCH 'two';
121
} {{one three four} {one four} {one two}}
123
# This test was crashing at one point.
127
SELECT a, b, c FROM t1 WHERE c MATCH 'two';
128
CREATE TABLE t3(a, b, c);
129
SELECT a, b, c FROM t1 WHERE c MATCH 'two';
131
} {{one three four} {one four} {one two} {one three four} {one four} {one two}}
133
#---------------------------------------------------------------------
134
# Test that it is possible to rename an fts2 table in an attached
137
file delete -force test2.db test2.db-journal
141
ATTACH 'test2.db' AS aux;
142
CREATE VIRTUAL TABLE aux.t1 USING fts2(a, b, c);
143
INSERT INTO aux.t1(a, b, c) VALUES(
144
'neung song sahm', 'neung see', 'neung see song'
150
execsql { SELECT a, b, c FROM aux.t1 WHERE a MATCH 'song'; }
151
} {{neung song sahm} {neung see} {neung see song}}
154
execsql { SELECT a, b, c FROM t1 WHERE c MATCH 'two'; }
155
} {{one three four} {one four} {one two}}
158
execsql { ALTER TABLE aux.t1 RENAME TO t2 }
162
execsql { SELECT a, b, c FROM t2 WHERE a MATCH 'song'; }
163
} {{neung song sahm} {neung see} {neung see song}}
166
execsql { SELECT a, b, c FROM t1 WHERE c MATCH 'two'; }
167
} {{one three four} {one four} {one two}}