sqlite/libsql
0
0
Fork 0
mirror of https://github.com/tursodatabase/libsql.git synced 2024-12-15 22:39:05 +00:00
Code
libsql/libsql-sqlite3/ext/fts5/test/fts5simple2.test
Pekka Enberg 9ed72eb5ae Merge upstream SQLite 3.45.1 
This merges the version-3.45.1 tag from upstream SQLite git repository
to libSQL with the following conflicts resolved:

Conflicts:
      README.md
      ext/jni/src/org/sqlite/jni/capi/ConfigSqllogCallback.java
      libsql-sqlite3/configure
      libsql-sqlite3/doc/jsonb.md
      libsql-sqlite3/ext/fts5/test/fts5faultH.test
      libsql-sqlite3/ext/fts5/test/fts5origintext.test
      libsql-sqlite3/ext/fts5/test/fts5origintext2.test
      libsql-sqlite3/ext/fts5/test/fts5origintext3.test
      libsql-sqlite3/ext/fts5/test/fts5origintext4.test
      libsql-sqlite3/ext/fts5/test/fts5origintext5.test
      libsql-sqlite3/ext/fts5/test/fts5secure8.test
      libsql-sqlite3/ext/fts5/test/fts5tokenizer2.test
      libsql-sqlite3/ext/fts5/test/fts5trigram2.test
      libsql-sqlite3/ext/jni/src/org/sqlite/jni/annotation/Experimental.java
      libsql-sqlite3/ext/jni/src/org/sqlite/jni/capi/ConfigSqlLogCallback.java
      libsql-sqlite3/ext/jni/src/org/sqlite/jni/capi/ConfigSqllogCallback.java
      libsql-sqlite3/ext/jni/src/org/sqlite/jni/wrapper1/WindowFunction.java
      libsql-sqlite3/ext/wasm/GNUmakefile
      libsql-sqlite3/ext/wasm/batch-runner-sahpool.html
      libsql-sqlite3/ext/wasm/batch-runner-sahpool.js
      libsql-sqlite3/src/pager.c
      libsql-sqlite3/src/shell.c.in
      libsql-sqlite3/src/sqliteInt.h
      libsql-sqlite3/src/wal.c
      libsql-sqlite3/test/fts3integrity.test
      libsql-sqlite3/test/json/jsonb-q1.txt
      libsql-sqlite3/test/json106.test
      libsql-sqlite3/test/json107.test
      libsql-sqlite3/test/jsonb01.test
      libsql-sqlite3/test/mmapcorrupt.test
      libsql-sqlite3/test/releasetest_data.tcl
      libsql-sqlite3/test/shell9.test
      libsql-sqlite3/test/wapp.tcl
      libsql-sqlite3/test/wapptest.tcl
2024-07-25 13:45:06 +03:00

375 lines
9.6 KiB
Plaintext
Raw Permalink Blame History

# 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
View Git Blame Copy Permalink