# 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 } #------------------------------------------------------------------------- # 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} finish_test