| 1 |
# 2014 November 24 |
| 2 |
# |
| 3 |
# The author disclaims copyright to this source code. In place of |
| 4 |
# a legal notice, here is a blessing: |
| 5 |
# |
| 6 |
# May you do good and not evil. |
| 7 |
# May you find forgiveness for yourself and forgive others. |
| 8 |
# May you share freely, never taking more than you give. |
| 9 |
# |
| 10 |
#************************************************************************* |
| 11 |
# This file implements regression tests for SQLite library. The |
| 12 |
# focus of this script is testing the FTS5 module. |
| 13 |
# |
| 14 |
# Specifically, the auxiliary function "highlight". |
| 15 |
# |
| 16 |
|
| 17 |
source [file join [file dirname [info script]] fts5_common.tcl] |
| 18 |
set testprefix fts5ak |
| 19 |
|
| 20 |
# If SQLITE_ENABLE_FTS5 is not defined, omit this file. |
| 21 |
ifcapable !fts5 { |
| 22 |
finish_test |
| 23 |
return |
| 24 |
} |
| 25 |
|
| 26 |
foreach_detail_mode $testprefix { |
| 27 |
|
| 28 |
do_execsql_test 1.1 { |
| 29 |
CREATE VIRTUAL TABLE ft1 USING fts5(x, detail=%DETAIL%); |
| 30 |
INSERT INTO ft1 VALUES('i d d a g i b g d d'); |
| 31 |
INSERT INTO ft1 VALUES('h d b j c c g a c a'); |
| 32 |
INSERT INTO ft1 VALUES('e j a e f h b f h h'); |
| 33 |
INSERT INTO ft1 VALUES('j f h d g h i b d f'); |
| 34 |
INSERT INTO ft1 VALUES('d c j d c j b c g e'); |
| 35 |
INSERT INTO ft1 VALUES('i a d e g j g d a a'); |
| 36 |
INSERT INTO ft1 VALUES('j f c e d a h j d b'); |
| 37 |
INSERT INTO ft1 VALUES('i c c f a d g h j e'); |
| 38 |
INSERT INTO ft1 VALUES('i d i g c d c h b f'); |
| 39 |
INSERT INTO ft1 VALUES('g d a e h a b c f j'); |
| 40 |
|
| 41 |
CREATE VIRTUAL TABLE ft2 USING fts5(x, detail=%DETAIL%); |
| 42 |
INSERT INTO ft2 VALUES('a b c d e f g h i j'); |
| 43 |
} |
| 44 |
|
| 45 |
do_execsql_test 1.2 { |
| 46 |
SELECT highlight(ft1, 0, '[', ']') FROM ft1 WHERE ft1 MATCH 'e'; |
| 47 |
} { |
| 48 |
{[e] j a [e] f h b f h h} |
| 49 |
{d c j d c j b c g [e]} |
| 50 |
{i a d [e] g j g d a a} |
| 51 |
{j f c [e] d a h j d b} |
| 52 |
{i c c f a d g h j [e]} |
| 53 |
{g d a [e] h a b c f j} |
| 54 |
} |
| 55 |
|
| 56 |
do_execsql_test 1.3 { |
| 57 |
SELECT highlight(ft1, 0, '[', ']') FROM ft1 WHERE ft1 MATCH 'e e e' |
| 58 |
} { |
| 59 |
{[e] j a [e] f h b f h h} |
| 60 |
{d c j d c j b c g [e]} |
| 61 |
{i a d [e] g j g d a a} |
| 62 |
{j f c [e] d a h j d b} |
| 63 |
{i c c f a d g h j [e]} |
| 64 |
{g d a [e] h a b c f j} |
| 65 |
} |
| 66 |
|
| 67 |
do_execsql_test 1.4 { |
| 68 |
SELECT highlight(ft2, 0, '[', ']') FROM ft2 WHERE ft2 MATCH 'f d' |
| 69 |
} { |
| 70 |
{a b c [d] e [f] g h i j} |
| 71 |
} |
| 72 |
|
| 73 |
do_execsql_test 1.5 { |
| 74 |
SELECT highlight(ft2, 0, '[', ']') FROM ft2 WHERE ft2 MATCH 'd f' |
| 75 |
} { |
| 76 |
{a b c [d] e [f] g h i j} |
| 77 |
} |
| 78 |
|
| 79 |
#------------------------------------------------------------------------- |
| 80 |
# Tests below this point require detail=full. |
| 81 |
#------------------------------------------------------------------------- |
| 82 |
if {[detail_is_full]==0} continue |
| 83 |
|
| 84 |
|
| 85 |
do_execsql_test 2.1 { |
| 86 |
SELECT highlight(ft1, 0, '[', ']') FROM ft1 WHERE ft1 MATCH 'h + d'; |
| 87 |
} { |
| 88 |
{[h d] b j c c g a c a} |
| 89 |
{j f [h d] g h i b d f} |
| 90 |
} |
| 91 |
|
| 92 |
do_execsql_test 2.2 { |
| 93 |
SELECT highlight(ft1, 0, '[', ']') FROM ft1 WHERE ft1 MATCH 'd + d'; |
| 94 |
} { |
| 95 |
{i [d d] a g i b g [d d]} |
| 96 |
} |
| 97 |
|
| 98 |
do_execsql_test 2.3 { |
| 99 |
SELECT highlight(ft1, 0, '[', ']') FROM ft1 WHERE ft1 MATCH 'd + d d + d'; |
| 100 |
} { |
| 101 |
{i [d d] a g i b g [d d]} |
| 102 |
} |
| 103 |
|
| 104 |
do_execsql_test 2.4 { |
| 105 |
SELECT highlight(ft2, 0, '[', ']') FROM ft2 WHERE ft2 MATCH 'b+c+d c+d+e' |
| 106 |
} {{a [b c d e] f g h i j}} |
| 107 |
|
| 108 |
do_execsql_test 2.5 { |
| 109 |
SELECT highlight(ft2, 0, '[', ']') FROM ft2 WHERE ft2 MATCH 'b+c+d e+f+g' |
| 110 |
} { |
| 111 |
{a [b c d] [e f g] h i j} |
| 112 |
} |
| 113 |
|
| 114 |
do_execsql_test 2.6 { |
| 115 |
SELECT highlight(ft2, 0, '[', ']') FROM ft2 WHERE ft2 MATCH 'b+c+d c' |
| 116 |
} { |
| 117 |
{a [b c d] e f g h i j} |
| 118 |
} |
| 119 |
|
| 120 |
do_execsql_test 2.7 { |
| 121 |
SELECT highlight(ft2, 0, '[', ']') FROM ft2 WHERE ft2 MATCH 'b+c c+d+e' |
| 122 |
} { |
| 123 |
{a [b c d e] f g h i j} |
| 124 |
} |
| 125 |
|
| 126 |
#------------------------------------------------------------------------- |
| 127 |
# The example from the docs. |
| 128 |
# |
| 129 |
do_execsql_test 3.1 { |
| 130 |
-- Assuming this: |
| 131 |
CREATE VIRTUAL TABLE ft USING fts5(a, detail=%DETAIL%); |
| 132 |
INSERT INTO ft VALUES('a b c x c d e'); |
| 133 |
INSERT INTO ft VALUES('a b c c d e'); |
| 134 |
INSERT INTO ft VALUES('a b c d e'); |
| 135 |
|
| 136 |
-- The following SELECT statement returns these three rows: |
| 137 |
-- '[a b c] x [c d e]' |
| 138 |
-- '[a b c] [c d e]' |
| 139 |
-- '[a b c d e]' |
| 140 |
SELECT highlight(ft, 0, '[', ']') FROM ft WHERE ft MATCH 'a+b+c AND c+d+e'; |
| 141 |
} { |
| 142 |
{[a b c] x [c d e]} |
| 143 |
{[a b c] [c d e]} |
| 144 |
{[a b c d e]} |
| 145 |
} |
| 146 |
|
| 147 |
do_execsql_test 3.2 { |
| 148 |
SELECT highlight(ft, 0, NULL, NULL) FROM ft WHERE ft MATCH 'a+b+c AND c+d+e'; |
| 149 |
} { |
| 150 |
{a b c x c d e} |
| 151 |
{a b c c d e} |
| 152 |
{a b c d e} |
| 153 |
} |
| 154 |
|
| 155 |
} |
| 156 |
|
| 157 |
# 2023-04-06 https://sqlite.org/forum/forumpost/cae4367d9b |
| 158 |
# |
| 159 |
# This is not a test of FTS5, but rather a test of the of what happens to |
| 160 |
# prepared statements that encounter SQLITE_SCHEMA while other prepared |
| 161 |
# statements are running. The original problem POC used FTS5, and so |
| 162 |
# is seems reasonable to put the test here. |
| 163 |
# |
| 164 |
# The vdbeaux24.test module in TH3 also tests this same behavior but |
| 165 |
# without requiring FTS5 or an other extension. |
| 166 |
# |
| 167 |
reset_db |
| 168 |
db null NULL |
| 169 |
do_execsql_test 4.0 { |
| 170 |
CREATE TABLE t5(a PRIMARY KEY); |
| 171 |
INSERT INTO t5 VALUES(0); |
| 172 |
CREATE VIRTUAL TABLE t6 USING fts5(0); |
| 173 |
DELETE FROM t6; |
| 174 |
CREATE TABLE t7(x); |
| 175 |
WITH cte(a) AS ( |
| 176 |
SELECT a FROM t5 |
| 177 |
WHERE ((0,0) IN (SELECT 0, LAG(0) OVER (PARTITION BY 0) FROM t6), 0) |
| 178 |
< (a,0) |
| 179 |
) |
| 180 |
SELECT max(a) FROM cte; |
| 181 |
} NULL |
| 182 |
|
| 183 |
finish_test |