summaryrefslogtreecommitdiffstats
path: root/ext/fts5/test/fts5simple2.test
diff options
context:
space:
mode:
Diffstat (limited to 'ext/fts5/test/fts5simple2.test')
-rw-r--r--ext/fts5/test/fts5simple2.test372
1 files changed, 372 insertions, 0 deletions
diff --git a/ext/fts5/test/fts5simple2.test b/ext/fts5/test/fts5simple2.test
new file mode 100644
index 0000000..e57cea7
--- /dev/null
+++ b/ext/fts5/test/fts5simple2.test
@@ -0,0 +1,372 @@
+# 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 fts5simple2
+
+# 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, detail=none);
+ INSERT INTO t1 VALUES('a b c');
+}
+do_execsql_test 1.1 {
+ SELECT rowid FROM t1('c a b')
+} {1}
+
+#-------------------------------------------------------------------------
+#
+reset_db
+do_execsql_test 2.0 {
+ CREATE VIRTUAL TABLE t1 USING fts5(a, detail=none);
+ BEGIN;
+ INSERT INTO t1 VALUES('b c d');
+ INSERT INTO t1 VALUES('b c d');
+ COMMIT;
+}
+do_execsql_test 2.1 {
+ SELECT rowid FROM t1('b c d')
+} {1 2}
+
+#-------------------------------------------------------------------------
+#
+reset_db
+do_execsql_test 3.0 {
+ CREATE VIRTUAL TABLE t1 USING fts5(a, detail=none);
+ BEGIN;
+ INSERT INTO t1 VALUES('b c d');
+ INSERT INTO t1 VALUES('b c d');
+}
+do_execsql_test 3.1 {
+ SELECT rowid FROM t1('b c d'); COMMIT;
+} {1 2}
+
+#-------------------------------------------------------------------------
+#
+reset_db
+do_execsql_test 4.0 {
+ CREATE VIRTUAL TABLE t1 USING fts5(a, detail=none);
+ BEGIN;
+ INSERT INTO t1 VALUES('a1 b1 c1');
+ INSERT INTO t1 VALUES('a2 b2 c2');
+ INSERT INTO t1 VALUES('a3 b3 c3');
+ COMMIT;
+}
+do_execsql_test 4.1 {
+ SELECT rowid FROM t1('b*');
+} {1 2 3}
+
+
+#-------------------------------------------------------------------------
+#
+reset_db
+do_execsql_test 5.0 {
+ CREATE VIRTUAL TABLE t1 USING fts5(a, detail=none);
+ BEGIN;
+ INSERT INTO t1 VALUES('a1 b1 c1');
+ INSERT INTO t1 VALUES('a2 b2 c2');
+ INSERT INTO t1 VALUES('a1 b1 c1');
+ COMMIT;
+}
+do_execsql_test 5.1 { SELECT rowid FROM t1('b*') } {1 2 3}
+
+#-------------------------------------------------------------------------
+#
+reset_db
+do_execsql_test 6.0 {
+ CREATE VIRTUAL TABLE t1 USING fts5(a, detail=full);
+ BEGIN;
+ INSERT INTO t1 VALUES('a1 b1 c1');
+ INSERT INTO t1 VALUES('a1 b1 c1');
+ INSERT INTO t1 VALUES('a1 b1 c1');
+ COMMIT;
+}
+
+do_execsql_test 6.1 { SELECT rowid FROM t1('a1') ORDER BY rowid DESC } {3 2 1}
+do_execsql_test 6.2 { SELECT rowid FROM t1('b1') ORDER BY rowid DESC } {3 2 1}
+do_execsql_test 6.3 { SELECT rowid FROM t1('c1') ORDER BY rowid DESC } {3 2 1}
+
+#-------------------------------------------------------------------------
+#
+reset_db
+do_execsql_test 7.0 {
+ CREATE VIRTUAL TABLE t1 USING fts5(a, detail=none);
+ BEGIN;
+ INSERT INTO t1 VALUES('a1 b1');
+ INSERT INTO t1 VALUES('a1 b2');
+ COMMIT;
+}
+do_execsql_test 7.1 { SELECT rowid FROM t1('b*') ORDER BY rowid DESC } {2 1}
+do_execsql_test 7.2 { SELECT rowid FROM t1('a1') ORDER BY rowid DESC } {2 1}
+
+#-------------------------------------------------------------------------
+#
+reset_db
+do_execsql_test 8.0 {
+ CREATE VIRTUAL TABLE t1 USING fts5(a, detail=none);
+ INSERT INTO t1 VALUES('a1 b1 c1');
+ INSERT INTO t1 VALUES('a2 b2 c2');
+ INSERT INTO t1 VALUES('a1 b1 c1');
+}
+do_execsql_test 8.0.1 { SELECT rowid FROM t1('b*') } {1 2 3}
+do_execsql_test 8.0.2 { SELECT rowid FROM t1('a1') } {1 3}
+do_execsql_test 8.0.3 { SELECT rowid FROM t1('c2') } {2}
+
+do_execsql_test 8.0.4 { SELECT rowid FROM t1('b*') ORDER BY rowid DESC } {3 2 1}
+do_execsql_test 8.0.5 { SELECT rowid FROM t1('a1') ORDER BY rowid DESC } {3 1}
+do_execsql_test 8.0.8 { SELECT rowid FROM t1('c2') ORDER BY rowid DESC } {2}
+
+do_execsql_test 8.1.0 { INSERT INTO t1(t1) VALUES('optimize') }
+
+do_execsql_test 8.1.1 { SELECT rowid FROM t1('b*') } {1 2 3}
+do_execsql_test 8.1.2 { SELECT rowid FROM t1('a1') } {1 3}
+do_execsql_test 8.1.3 { SELECT rowid FROM t1('c2') } {2}
+
+do_execsql_test 8.2.1 { SELECT rowid FROM t1('b*') ORDER BY rowid DESC} {3 2 1}
+do_execsql_test 8.2.2 { SELECT rowid FROM t1('a1') ORDER BY rowid DESC} {3 1}
+do_execsql_test 8.2.3 { SELECT rowid FROM t1('c2') ORDER BY rowid DESC} {2}
+
+#--------------------------------------------------------------------------
+#
+reset_db
+do_execsql_test 9.0.0 {
+ CREATE VIRTUAL TABLE t1 USING fts5(a, detail=none);
+ INSERT INTO t1 VALUES('a1 b1 c1');
+ INSERT INTO t1 VALUES('a2 b2 c2');
+ INSERT INTO t1 VALUES('a1 b1 c1');
+}
+do_execsql_test 9.0.1 {
+ INSERT INTO t1(t1) VALUES('integrity-check');
+} {}
+
+reset_db
+do_execsql_test 9.1.0 {
+ CREATE VIRTUAL TABLE t1 USING fts5(a, b, detail=none);
+ INSERT INTO t1 VALUES('a1 b1 c1', 'x y z');
+ INSERT INTO t1 VALUES('a2 b2 c2', '1 2 3');
+ INSERT INTO t1 VALUES('a1 b1 c1', 'x 2 z');
+}
+do_execsql_test 9.2.1 {
+ INSERT INTO t1(t1) VALUES('integrity-check');
+} {}
+
+#--------------------------------------------------------------------------
+#
+reset_db
+do_execsql_test 10.0 {
+ CREATE VIRTUAL TABLE t1 USING fts5(a, detail=none);
+ INSERT INTO t1 VALUES('b1');
+ INSERT INTO t1 VALUES('b1');
+ DELETE FROM t1 WHERE rowid=1;
+}
+
+do_execsql_test 10.1 {
+ SELECT rowid FROM t1('b1');
+} {2}
+
+do_execsql_test 10.2 {
+ SELECT rowid FROM t1('b1') ORDER BY rowid DESC;
+} {2}
+
+do_execsql_test 10.3 {
+ INSERT INTO t1(t1) VALUES('integrity-check');
+} {}
+
+#--------------------------------------------------------------------------
+#
+reset_db
+do_execsql_test 11.1 {
+ CREATE VIRTUAL TABLE t1 USING fts5(x, y, detail=none);
+ INSERT INTO t1(t1, rank) VALUES('pgsz', 32);
+ WITH d(x,y) AS (
+ SELECT NULL, 'xyz' UNION ALL SELECT NULL, 'xyz' FROM d
+ )
+ INSERT INTO t1 SELECT * FROM d LIMIT 23;
+}
+
+#db eval { SELECT rowid AS r, quote(block) AS b FROM t1_data } { puts "$r: $b" }
+do_execsql_test 11.2 {
+ SELECT rowid FROM t1;
+} {1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23}
+
+do_execsql_test 11.3 {
+ SELECT rowid FROM t1('xyz');
+} {1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23}
+
+do_execsql_test 11.4 {
+ INSERT INTO t1(t1) VALUES('integrity-check');
+}
+
+#-------------------------------------------------------------------------
+#
+reset_db
+do_execsql_test 12.0 {
+ CREATE VIRTUAL TABLE yy USING fts5(x, detail=none);
+ INSERT INTO yy VALUES('in if');
+ INSERT INTO yy VALUES('if');
+} {}
+
+do_execsql_test 12.1 {
+ SELECT rowid FROM yy('i*');
+} {1 2}
+
+#-------------------------------------------------------------------------
+#
+reset_db
+do_execsql_test 13.0 {
+ CREATE VIRTUAL TABLE t1 USING fts5(a, prefix=1, detail=none);
+} {}
+foreach {rowid a} {
+ 0 {f}
+ 1 {u}
+ 2 {k}
+ 3 {a}
+ 4 {a}
+ 5 {u}
+ 6 {u}
+ 7 {u}
+ 8 {f}
+ 9 {f}
+ 10 {a}
+ 11 {p}
+ 12 {f}
+ 13 {u}
+ 14 {a}
+ 15 {a}
+} {
+ do_execsql_test 13.1.$rowid {
+ INSERT INTO t1(rowid, a) VALUES($rowid, $a);
+ }
+}
+
+#-------------------------------------------------------------------------
+#
+reset_db
+fts5_aux_test_functions db
+do_execsql_test 14.0 {
+ CREATE VIRTUAL TABLE t1 USING fts5(a, detail=none);
+ INSERT INTO t1 VALUES('a b c d');
+} {}
+
+do_execsql_test 14.1 {
+ SELECT fts5_test_poslist(t1) FROM t1('b') ORDER BY rank;
+} {0.0.1}
+
+#-------------------------------------------------------------------------
+#
+reset_db
+do_execsql_test 15.1 {
+ CREATE VIRTUAL TABLE t1 USING fts5(x, detail=none);
+ BEGIN;
+ INSERT INTO t1(rowid, x) VALUES(1, 'sqlite');
+ INSERT INTO t1(rowid, x) VALUES(2, 'sqlite');
+ COMMIT;
+} {}
+
+do_test 15.1 {
+ execsql { INSERT INTO t1(t1) VALUES('integrity-check') }
+} {}
+
+do_test 15.2 {
+ execsql { DELETE FROM t1 }
+} {}
+
+do_execsql_test 15.3.1 {
+ SELECT rowid FROM t1('sqlite');
+} {}
+
+do_execsql_test 15.3.2 {
+ SELECT rowid FROM t1('sqlite') ORDER BY rowid DESC;
+} {}
+
+do_test 15.4 {
+ execsql { INSERT INTO t1(t1) VALUES('integrity-check') }
+} {}
+
+#-------------------------------------------------------------------------
+#
+reset_db
+do_execsql_test 16.0 {
+ CREATE VIRTUAL TABLE t2 USING fts5(x, detail=none);
+ BEGIN;
+ INSERT INTO t2(rowid, x) VALUES(1, 'a b c');
+ INSERT INTO t2(rowid, x) VALUES(456, 'a b c');
+ INSERT INTO t2(rowid, x) VALUES(1000, 'a b c');
+ COMMIT;
+ UPDATE t2 SET x=x;
+}
+
+do_execsql_test 16.1 {
+ INSERT INTO t2(t2) VALUES('integrity-check');
+} {}
+
+do_execsql_test 16.2 {
+ SELECT rowid FROM t2('b') ORDER BY rowid DESC
+} {1000 456 1}
+
+
+#-------------------------------------------------------------------------
+#
+reset_db
+do_execsql_test 16.0 {
+ CREATE VIRTUAL TABLE t2 USING fts5(x, detail=none);
+ BEGIN;
+ INSERT INTO t2(rowid, x) VALUES(1, 'a b c');
+ INSERT INTO t2(rowid, x) VALUES(456, 'a b c');
+ INSERT INTO t2(rowid, x) VALUES(1000, 'a b c');
+ COMMIT;
+ UPDATE t2 SET x=x;
+ DELETE FROM t2;
+}
+
+#-------------------------------------------------------------------------
+#
+reset_db
+do_execsql_test 17.0 {
+ CREATE VIRTUAL TABLE t2 USING fts5(x, y);
+ BEGIN;
+ INSERT INTO t2 VALUES('a aa aaa', 'b bb bbb');
+ INSERT INTO t2 VALUES('a aa aaa', 'b bb bbb');
+ INSERT INTO t2 VALUES('a aa aaa', 'b bb bbb');
+ COMMIT;
+}
+do_execsql_test 17.1 { SELECT * FROM t2('y:a*') WHERE rowid BETWEEN 10 AND 20 }
+do_execsql_test 17.2 {
+ BEGIN;
+ INSERT INTO t2 VALUES('a aa aaa', 'b bb bbb');
+ SELECT * FROM t2('y:a*') WHERE rowid BETWEEN 10 AND 20 ;
+}
+do_execsql_test 17.3 {
+ COMMIT
+}
+
+reset_db
+do_execsql_test 17.4 {
+ CREATE VIRTUAL TABLE t2 USING fts5(x, y);
+ BEGIN;
+ INSERT INTO t2 VALUES('a aa aaa', 'b bb bbb');
+ INSERT INTO t2 VALUES('a aa aaa', 'b bb bbb');
+ SELECT * FROM t2('y:a*') WHERE rowid>66;
+}
+do_execsql_test 17.5 { SELECT * FROM t2('x:b* OR y:a*') }
+do_execsql_test 17.5 { COMMIT ; SELECT * FROM t2('x:b* OR y:a*') }
+do_execsql_test 17.6 {
+ SELECT * FROM t2('x:b* OR y:a*') WHERE rowid>55
+}
+
+#db eval {SELECT rowid, fts5_decode_none(rowid, block) aS r FROM t2_data} {puts $r}
+
+finish_test
generated by cgit v1.2.3 (git 2.39.1) at 2024-12-27 03:33:33 +0000