# 2014 June 17
#
# 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 FTS5 module.
#
#
source [file join [file dirname [info script]] fts5_common.tcl]
set testprefix fts5ab
# If SQLITE_ENABLE_FTS5 is defined, omit this file.
ifcapable !fts5 {
finish_test
return
}
do_execsql_test 1.0 {
CREATE VIRTUAL TABLE t1 USING fts5(a, b);
INSERT INTO t1 VALUES('hello', 'world');
INSERT INTO t1 VALUES('one two', 'three four');
INSERT INTO t1(rowid, a, b) VALUES(45, 'forty', 'five');
}
do_execsql_test 1.1 {
SELECT * FROM t1 ORDER BY rowid DESC;
} { forty five {one two} {three four} hello world }
do_execsql_test 1.2 {
SELECT rowid FROM t1 ORDER BY rowid DESC;
} {45 2 1}
do_execsql_test 1.3 {
SELECT rowid FROM t1 ORDER BY rowid ASC;
} {1 2 45}
do_execsql_test 1.4 {
SELECT * FROM t1 WHERE rowid=2;
} {{one two} {three four}}
do_execsql_test 1.5 {
SELECT * FROM t1 WHERE rowid=2.01;
} {}
do_execsql_test 1.6 {
SELECT * FROM t1 WHERE rowid=1.99;
} {}
#-------------------------------------------------------------------------
reset_db
do_execsql_test 2.1 {
CREATE VIRTUAL TABLE t1 USING fts5(x);
INSERT INTO t1(t1, rank) VALUES('pgsz', 32);
INSERT INTO t1 VALUES('one');
INSERT INTO t1 VALUES('two');
INSERT INTO t1 VALUES('three');
}
do_catchsql_test 2.2 {
SELECT rowid, * FROM t1 WHERE t1 MATCH 'AND AND'
} {1 {fts5: syntax error near "AND"}}
do_execsql_test 2.3 { SELECT rowid, * FROM t1 WHERE t1 MATCH 'two' } {2 two}
do_execsql_test 2.4 { SELECT rowid, * FROM t1 WHERE t1 MATCH 'three' } {3 three}
do_execsql_test 2.5 { SELECT rowid, * FROM t1 WHERE t1 MATCH 'one' } {1 one}
do_execsql_test 2.6 {
INSERT INTO t1 VALUES('a b c d e f g');
INSERT INTO t1 VALUES('b d e a a a i');
INSERT INTO t1 VALUES('x y z b c c c');
}
foreach {tn expr res} {
1 a {5 4}
2 b {6 5 4}
3 c {6 4}
4 d {5 4}
5 e {5 4}
6 f {4}
7 g {4}
8 x {6}
9 y {6}
10 z {6}
} {
do_execsql_test 2.7.$tn.1 {
SELECT rowid FROM t1 WHERE t1 MATCH $expr ORDER BY rowid DESC
} $res
do_execsql_test 2.7.$tn.2 {
SELECT rowid FROM t1 WHERE t1 MATCH $expr ORDER BY rowid ASC
} [lsort -integer $res]
}
#-------------------------------------------------------------------------
#
reset_db
do_execsql_test 3.0 {
CREATE VIRTUAL TABLE t1 USING fts5(a,b);
INSERT INTO t1(t1, rank) VALUES('pgsz', 32);
}
foreach {tn a b} {
1 {abashed abandons abase abash abaft} {abases abased}
2 {abasing abases abaft abated abandons} {abases abandoned}
3 {abatement abash abash abated abase} {abasements abashing}
4 {abaft abasements abase abasement abasing} {abasement abases}
5 {abaft abashing abatement abash abasements} {abandons abandoning}
6 {aback abate abasements abashes abandoned} {abasement abased}
7 {abandons abated abased aback abandoning} {abases abandoned}
8 {abashing abases abasement abaft abashing} {abashed abate}
9 {abash abase abate abashing abashed} {abandon abandoned}
10 {abate abandoning abandons abasement aback} {abandon abandoning}
} {
do_execsql_test 3.1.$tn.1 { INSERT INTO t1 VALUES($a, $b) }
do_execsql_test 3.1.$tn.2 { INSERT INTO t1(t1) VALUES('integrity-check') }
}
foreach {tn expr res} {
1 {abash} {9 5 3 1}
2 {abase} {9 4 3 1}
3 {abase + abash} {1}
4 {abash + abase} {9}
5 {abaft + abashing} {8 5}
6 {abandon + abandoning} {10}
7 {"abashing abases abasement abaft abashing"} {8}
} {
do_execsql_test 3.2.$tn {
SELECT rowid FROM t1 WHERE t1 MATCH $expr ORDER BY rowid DESC
} $res
}
do_execsql_test 3.3 {
SELECT rowid FROM t1 WHERE t1 MATCH 'NEAR(aback abate, 2)'
} {6}
foreach {tn expr res} {
1 {abash} {1 3 5 9}
2 {abase} {1 3 4 9}
3 {abase + abash} {1}
4 {abash + abase} {9}
5 {abaft + abashing} {5 8}
6 {abandon + abandoning} {10}
7 {"abashing abases abasement abaft abashing"} {8}
} {
do_execsql_test 3.4.$tn {
SELECT rowid FROM t1 WHERE t1 MATCH $expr
} $res
}
#-------------------------------------------------------------------------
# Documents with more than 2M tokens.
#
do_execsql_test 4.0 {
CREATE VIRTUAL TABLE s1 USING fts5(x);
}
foreach {tn doc} [list \
1 [string repeat {a x } 1500000] \
2 "[string repeat {a a } 1500000] x" \
] {
do_execsql_test 4.$tn { INSERT INTO s1 VALUES($doc) }
}
do_execsql_test 4.3 {
SELECT rowid FROM s1 WHERE s1 MATCH 'x'
} {1 2}
do_execsql_test 4.4 {
SELECT rowid FROM s1 WHERE s1 MATCH '"a x"'
} {1 2}
#-------------------------------------------------------------------------
# Check that a special case of segment promotion works. The case is where
# a new segment is written to level L, but the oldest segment within level
# (L-2) is larger than it.
#
do_execsql_test 5.0 {
CREATE VIRTUAL TABLE s2 USING fts5(x);
INSERT INTO s2(s2, rank) VALUES('pgsz', 32);
INSERT INTO s2(s2, rank) VALUES('automerge', 0);
}
proc rnddoc {n} {
set map [list 0 a 1 b 2 c 3 d 4 e 5 f 6 g 7 h 8 i 9 j]
set doc [list]
for {set i 0} {$i < $n} {incr i} {
lappend doc [string map $map [format %.3d [expr int(rand()*1000)]]]
}
set doc
}
db func rnddoc rnddoc
do_test 5.1 {
for {set i 1} {$i <= 65} {incr i} {
execsql { INSERT INTO s2 VALUES(rnddoc(10)) }
}
for {set i 1} {$i <= 63} {incr i} {
execsql { DELETE FROM s2 WHERE rowid = $i }
}
fts5_level_segs s2
} {0 8}
do_test 5.2 {
execsql {
INSERT INTO s2(s2, rank) VALUES('automerge', 8);
}
for {set i 0} {$i < 7} {incr i} {
execsql { INSERT INTO s2 VALUES(rnddoc(50)) }
}
fts5_level_segs s2
} {8 0 0}
# Test also the other type of segment promotion - when a new segment is written
# that is larger than segments immediately following it.
do_test 5.3 {
execsql {
DROP TABLE s2;
CREATE VIRTUAL TABLE s2 USING fts5(x);
INSERT INTO s2(s2, rank) VALUES('pgsz', 32);
INSERT INTO s2(s2, rank) VALUES('automerge', 0);
}
for {set i 1} {$i <= 16} {incr i} {
execsql { INSERT INTO s2 VALUES(rnddoc(5)) }
}
fts5_level_segs s2
} {0 1}
do_test 5.4 {
execsql { INSERT INTO s2 VALUES(rnddoc(160)) }
fts5_level_segs s2
} {2 0}
#-------------------------------------------------------------------------
#
do_execsql_test 6.0 {
CREATE VIRTUAL TABLE s3 USING fts5(x);
BEGIN;
INSERT INTO s3 VALUES('a b c');
INSERT INTO s3 VALUES('A B C');
}
do_execsql_test 6.1.1 {
SELECT rowid FROM s3 WHERE s3 MATCH 'a'
} {1 2}
do_execsql_test 6.1.2 {
SELECT rowid FROM s3 WHERE s3 MATCH 'a' ORDER BY rowid DESC
} {2 1}
do_execsql_test 6.2 {
COMMIT;
}
do_execsql_test 6.3 {
SELECT rowid FROM s3 WHERE s3 MATCH 'a'
} {1 2}
do_test 6.4 {
db close
sqlite3 db test.db
execsql {
BEGIN;
INSERT INTO s3(s3) VALUES('optimize');
ROLLBACK;
}
} {}
#-------------------------------------------------------------------------
#
set doc [string repeat "a b c " 500]
breakpoint
do_execsql_test 7.0 {
CREATE VIRTUAL TABLE x1 USING fts5(x);
INSERT INTO x1(x1, rank) VALUES('pgsz', 32);
INSERT INTO x1 VALUES($doc);
}
finish_test