# 2015 September 05
#
# 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.
#
#*************************************************************************
#
source [file join [file dirname [info script]] fts5_common.tcl]
set testprefix fts5simple
# If SQLITE_ENABLE_FTS5 is defined, omit this file.
ifcapable !fts5 {
finish_test
return
}
if 1 {
#-------------------------------------------------------------------------
#
set doc "x x [string repeat {y } 50]z z"
do_execsql_test 1.0 {
CREATE VIRTUAL TABLE t1 USING fts5(x);
INSERT INTO t1(t1, rank) VALUES('pgsz', 32);
BEGIN;
INSERT INTO t1 VALUES($doc);
COMMIT;
}
do_execsql_test 1.1 {
INSERT INTO t1(t1) VALUES('integrity-check');
}
#-------------------------------------------------------------------------
#
reset_db
do_execsql_test 2.0 {
CREATE VIRTUAL TABLE t1 USING fts5(x);
INSERT INTO t1(t1, rank) VALUES('pgsz', 32);
INSERT INTO t1 VALUES('a b c');
INSERT INTO t1 VALUES('d e f');
INSERT INTO t1(t1) VALUES('optimize');
}
do_execsql_test 2.1 {
INSERT INTO t1(t1) VALUES('integrity-check');
} {}
#-------------------------------------------------------------------------
#
reset_db
do_execsql_test 3.0 {
CREATE VIRTUAL TABLE t1 USING fts5(x, prefix='1,2');
INSERT INTO t1(t1, rank) VALUES('pgsz', 32);
BEGIN;
INSERT INTO t1 VALUES('one');
SELECT * FROM t1 WHERE t1 MATCH 'o*';
} {one}
do_execsql_test 3.1 {
INSERT INTO t1(t1) VALUES('integrity-check');
} {}
#-------------------------------------------------------------------------
reset_db
do_execsql_test 4.1 {
CREATE VIRTUAL TABLE t11 USING fts5(content);
INSERT INTO t11(t11, rank) VALUES('pgsz', 32);
INSERT INTO t11 VALUES('another');
INSERT INTO t11 VALUES('string');
INSERT INTO t11 VALUES('of');
INSERT INTO t11 VALUES('text');
}
do_test 4.2 {
execsql { INSERT INTO t11(t11) VALUES('optimize') }
} {}
do_execsql_test 4.3 {
INSERT INTO t11(t11) VALUES('integrity-check');
} {}
#db eval { SELECT fts5_decode(rowid, block) as x FROM t11_data } { puts $x }
#-------------------------------------------------------------------------
reset_db
set doc [string repeat "x y " 5]
do_execsql_test 5.1 {
CREATE VIRTUAL TABLE yy USING fts5(content);
INSERT INTO yy(yy, rank) VALUES('pgsz', 32);
BEGIN;
INSERT INTO yy VALUES($doc);
INSERT INTO yy VALUES($doc);
INSERT INTO yy VALUES($doc);
INSERT INTO yy VALUES($doc);
INSERT INTO yy VALUES($doc);
INSERT INTO yy VALUES($doc);
INSERT INTO yy VALUES($doc);
INSERT INTO yy VALUES($doc);
COMMIT;
}
do_execsql_test 5.2 {
SELECT rowid FROM yy WHERE yy MATCH 'y' ORDER BY rowid ASC
} {1 2 3 4 5 6 7 8}
do_execsql_test 5.3 {
SELECT rowid FROM yy WHERE yy MATCH 'y' ORDER BY rowid DESC
} {8 7 6 5 4 3 2 1}
#db eval { SELECT fts5_decode(rowid, block) as x FROM yy_data } { puts $x }
#-------------------------------------------------------------------------
reset_db
do_execsql_test 5.1 {
CREATE VIRTUAL TABLE tt USING fts5(content);
INSERT INTO tt(tt, rank) VALUES('pgsz', 32);
INSERT INTO tt VALUES('aa');
}
do_execsql_test 5.2 {
SELECT rowid FROM tt WHERE tt MATCH 'a*';
} {1}
do_execsql_test 5.3 {
DELETE FROM tt;
BEGIN;
INSERT INTO tt VALUES('aa');
INSERT INTO tt VALUES('ab');
COMMIT;
} {}
do_execsql_test 5.4 {
SELECT rowid FROM tt WHERE tt MATCH 'a*';
} {1 2}
do_execsql_test 5.5 {
DELETE FROM tt;
BEGIN;
INSERT INTO tt VALUES('aa');
INSERT INTO tt VALUES('ab');
INSERT INTO tt VALUES('aa');
INSERT INTO tt VALUES('ab');
INSERT INTO tt VALUES('aa');
INSERT INTO tt VALUES('ab');
INSERT INTO tt VALUES('aa');
INSERT INTO tt VALUES('ab');
COMMIT;
SELECT rowid FROM tt WHERE tt MATCH 'a*';
} {1 2 3 4 5 6 7 8}
do_execsql_test 5.6 {
INSERT INTO tt(tt) VALUES('integrity-check');
}
reset_db
do_execsql_test 5.7 {
CREATE VIRTUAL TABLE tt USING fts5(content);
INSERT INTO tt(tt, rank) VALUES('pgsz', 32);
INSERT INTO tt VALUES('aa ab ac ad ae af');
}
do_execsql_test 5.8 {
SELECT rowid FROM tt WHERE tt MATCH 'a*';
} {1}
#-------------------------------------------------------------------------
reset_db
do_execsql_test 6.1 {
CREATE VIRTUAL TABLE xyz USING fts5(x, y, z);
INSERT INTO xyz VALUES('x', 'y', 'z');
}
do_catchsql_test 6.2 {
SELECT * FROM xyz WHERE xyz MATCH ''
} {1 {fts5: syntax error near ""}}
do_catchsql_test 6.3 {
SELECT * FROM xyz WHERE xyz MATCH NULL
} {1 {fts5: syntax error near ""}}
#-------------------------------------------------------------------------
do_execsql_test 7.1 {
CREATE VIRTUAL TABLE ft2 USING fts5(content);
INSERT INTO ft2(rowid, content) VALUES(1, 'a b c');
INSERT INTO ft2(rowid, content) VALUES(2, 'a b d');
}
do_catchsql_test 7.2 {
BEGIN;
UPDATE ft2 SET rowid=2 WHERE rowid=1;
} {1 {constraint failed}}
do_execsql_test 7.3 {
COMMIT;
INSERT INTO ft2(ft2) VALUES('integrity-check');
} {}
do_execsql_test 7.4 {
SELECT * FROM ft2;
} {{a b c} {a b d}}
#-------------------------------------------------------------------------
#
reset_db
do_execsql_test 8.1 {
CREATE VIRTUAL TABLE ft2 USING fts5(content);
INSERT INTO ft2(rowid, content) VALUES(1, 'a b');
}
do_execsql_test 8.2 {
BEGIN;
INSERT INTO ft2(rowid, content) VALUES(4, 'a x');
}
do_execsql_test 8.3 {
INSERT INTO ft2(ft2) VALUES('integrity-check');
}
#-------------------------------------------------------------------------
# Check that the "table function" syntax works.
#
reset_db
do_execsql_test 9.1 {
CREATE VIRTUAL TABLE ft2 USING fts5(content);
INSERT INTO ft2(rowid, content) VALUES(1, 'a b');
INSERT INTO ft2(rowid, content) VALUES(2, 'a b c d');
INSERT INTO ft2(rowid, content) VALUES(3, 'c d e f');
}
do_execsql_test 9.2 {
SELECT rowid FROM ft2('a');
} {1 2}
do_execsql_test 9.3 {
SELECT rowid FROM ft2('b AND c');
} {2}
#-------------------------------------------------------------------------
#
do_execsql_test 10.0 {
CREATE VIRTUAL TABLE t3 USING fts5(a, b, c);
INSERT INTO t3 VALUES('bac aab bab', 'c bac c', 'acb aba abb'); -- 1
INSERT INTO t3 VALUES('bab abc c', 'acb c abb', 'c aaa c'); -- 2
}
do_execsql_test 10.1 {
SELECT rowid FROM t3('c: c*');
} {2}
do_execsql_test 10.2 {
SELECT rowid FROM t3('b: acb');
} {2}
#-------------------------------------------------------------------------
# Test that character 0x1A is allowed in fts5 barewords.
#
do_test 11.0 {
execsql "CREATE VIRTUAL TABLE t4 USING fts5(x, tokenize=\"ascii tokenchars '\x1A'\")"
execsql "
INSERT INTO t4 VALUES('a b c \x1A');
INSERT INTO t4 VALUES('a b c d\x1A');
INSERT INTO t4 VALUES('a b c \x1Ad');
INSERT INTO t4 VALUES('a b c d');
"
} {}
do_test 11.1 {
execsql "SELECT rowid FROM t4('\x1A')"
} {1}
do_test 11.2 {
execsql "SELECT rowid FROM t4('\x1A*')"
} {1 3}
do_test 11.3 {
execsql "SELECT rowid FROM t4('d\x1A')"
} {2}
do_test 11.4 {
catchsql "SELECT rowid FROM t4('d\x1B')"
} {/fts5: syntax error/}
do_test 11.5 {
catchsql "SELECT rowid FROM t4('d\x19')"
} {/fts5: syntax error/}
#-------------------------------------------------------------------------
#
do_test 12.1 {
execsql {
CREATE VIRTUAL TABLE xx USING fts5(x,y);
BEGIN;
INSERT INTO xx VALUES('1 2 3', 'a b c');
}
} {}
do_execsql_test 12.2 {
SELECT rowid FROM xx('x:a');
COMMIT;
} {}
#-------------------------------------------------------------------------
# Try an UPDATE OR REPLACE query.
#
do_execsql_test 13.1 {
CREATE VIRTUAL TABLE xy USING fts5(x);
INSERT INTO xy(rowid, x) VALUES(1, '1 2 3');
INSERT INTO xy(rowid, x) VALUES(2, '2 3 4');
INSERT INTO xy(rowid, x) VALUES(3, '3 4 5');
}
do_execsql_test 13.2 {
UPDATE OR REPLACE xy SET rowid=3 WHERE rowid = 2;
SELECT rowid, x FROM xy;
} {
1 {1 2 3}
3 {2 3 4}
}
do_execsql_test 13.3 {
INSERT INTO xy(xy) VALUES('integrity-check');
}
#-------------------------------------------------------------------------
#
do_execsql_test 14.1 {
CREATE VIRTUAL TABLE ttt USING fts5(x);
BEGIN;
INSERT INTO ttt(rowid, x) VALUES(1, 'a b c');
INSERT INTO ttt(rowid, x) VALUES(2, 'a b c');
INSERT INTO ttt(rowid, x) VALUES(3, 'a b c');
COMMIT;
}
do_test 14.2 {
fts5_level_segs ttt
} {1}
#-------------------------------------------------------------------------
db func rnddoc fts5_rnddoc
do_execsql_test 4.0 {
CREATE VIRTUAL TABLE x1 USING fts5(x);
INSERT INTO x1(x1, rank) VALUES('pgsz', 32);
WITH ii(i) AS ( SELECT 1 UNION ALL SELECT i+1 FROM ii WHERE i<10 )
INSERT INTO x1 SELECT rnddoc(5) FROM ii;
}
do_execsql_test 4.1 {
SELECT rowid, x, x1 FROM x1 WHERE x1 MATCH '*reads'
} {0 {} 4}
#-------------------------------------------------------------------------
reset_db
do_execsql_test 15.0 {
CREATE VIRTUAL TABLE x2 USING fts5(x, prefix=1);
INSERT INTO x2 VALUES('ab');
}
do_execsql_test 15.1 {
INSERT INTO x2(x2) VALUES('integrity-check');
}
}
#-------------------------------------------------------------------------
foreach_detail_mode $testprefix {
reset_db
fts5_aux_test_functions db
do_execsql_test 16.0 {
CREATE VIRTUAL TABLE x3 USING fts5(x, detail=%DETAIL%);
INSERT INTO x3 VALUES('a b c d e f');
}
do_execsql_test 16.1 {
SELECT fts5_test_poslist(x3) FROM x3('(a NOT b) OR c');
} {2.0.2}
do_execsql_test 16.1 {
SELECT fts5_test_poslist(x3) FROM x3('a OR c');
} {{0.0.0 1.0.2}}
}
finish_test