1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
|
# 2023 July 19
#
# 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.
#
#***********************************************************************
#
# This file contains tests for the content= and content_rowid= options.
#
source [file join [file dirname [info script]] fts5_common.tcl]
set testprefix fts5contentless2
# If SQLITE_ENABLE_FTS5 is defined, omit this file.
ifcapable !fts5 {
finish_test
return
}
proc vocab {} {
list aaa bbb ccc ddd eee fff ggg hhh iii jjj kkk lll mmm nnn ooo ppp
}
proc document {nToken} {
set doc [list]
set vocab [vocab]
for {set ii 0} {$ii < $nToken} {incr ii} {
lappend doc [lindex $vocab [expr int(rand()*[llength $vocab])]]
}
set doc
}
db func document document
proc contains {doc token} {
expr {[lsearch $doc $token]>=0}
}
db func contains contains
proc do_compare_tables_test {tn} {
uplevel [list do_test $tn {
foreach v [vocab] {
set l1 [execsql { SELECT rowid FROM t1 WHERE contains(doc, $v) }]
set l2 [execsql { SELECT rowid FROM t2($v) }]
if {$l1!=$l2} { error "1: query mismatch ($l1) ($l2)" }
set w "[string range $v 0 1]*"
set l1 [execsql { SELECT rowid FROM t1 WHERE contains(doc, $w) }]
set l2 [execsql { SELECT rowid FROM t2($w) }]
if {$l1!=$l2} { error "2: query mismatch ($l1) ($l2)" }
set w "[string range $v 0 0]*"
set l1 [execsql { SELECT rowid FROM t1 WHERE contains(doc, $w) }]
set l2 [execsql { SELECT rowid FROM t2($w) }]
if {$l1!=$l2} { error "2: query mismatch ($l1) ($l2)" }
set l1 [execsql {
SELECT rowid FROM t1 WHERE contains(doc, $v) ORDER BY rowid DESC
}]
set l2 [execsql { SELECT rowid FROM t2($v) ORDER BY rowid DESC }]
if {$l1!=$l2} { error "1: query mismatch ($l1) ($l2)" }
}
set {} {}
} {}]
}
proc lshuffle {in} {
set L [list]
set ret [list]
foreach elem $in { lappend L [list [expr rand()] $elem] }
foreach pair [lsort -index 0 $L] { lappend ret [lindex $pair 1] }
set ret
}
expr srand(0)
do_execsql_test 1.0 {
CREATE VIRTUAL TABLE t2 USING fts5(
doc, prefix=2, content=, contentless_delete=1
);
CREATE TABLE t1(doc);
CREATE TRIGGER tr1 AFTER DELETE ON t1 BEGIN
DELETE FROM t2 WHERE rowid = old.rowid;
END;
}
set SMALLEST64 -9223372036854775808
set LARGEST64 9223372036854775807
foreach {tn r1 r2} {
1 0 50
2 $SMALLEST64 $SMALLEST64+50
3 $LARGEST64-50 $LARGEST64
4 -50 -1
} {
set r1 [expr $r1]
set r2 [expr $r2]
do_test 1.1.$tn {
execsql BEGIN
for {set ii $r1} {$ii <= $r2} {incr ii} {
execsql { INSERT INTO t1(rowid, doc) VALUES ($ii, document(8)); }
}
execsql COMMIT
} {}
}
do_test 1.2 {
db eval { SELECT rowid, doc FROM t1 } {
execsql { INSERT INTO t2(rowid, doc) VALUES($rowid, $doc) }
}
} {}
foreach {tn rowid} {
1 $SMALLEST64
2 0
3 -5
4 -30
5 $LARGEST64
6 $LARGEST64-1
} {
set rowid [expr $rowid]
do_execsql_test 1.3.$tn.1 {
DELETE FROM t1 WHERE rowid=$rowid
}
do_compare_tables_test 1.3.$tn.2
}
set iTest 1
foreach r [lshuffle [execsql {SELECT rowid FROM t1}]] {
if {($iTest % 50)==0} {
execsql { INSERT INTO t2(t2) VALUES('optimize') }
}
if {($iTest % 5)==0} {
execsql { INSERT INTO t2(t2, rank) VALUES('merge', 5) }
}
do_execsql_test 1.4.$iTest.1($r) {
DELETE FROM t1 WHERE rowid=$r
}
do_compare_tables_test 1.4.$iTest.2
incr iTest
}
do_execsql_test 1.5 {
SELECT * FROM t1
} {}
#-------------------------------------------------------------------------
reset_db
db func document document
do_execsql_test 2.0 {
CREATE VIRTUAL TABLE t2 USING fts5(doc, content=, contentless_delete=1);
WITH s(i) AS (
SELECT 1 UNION ALL SELECT i+1 FROM s WHERE i<1000
)
INSERT INTO t2(rowid, doc) SELECT i, i || ' ' || i FROM s;
}
do_execsql_test 2.1 {
BEGIN;
DELETE FROM t2 WHERE rowid=32;
DELETE FROM t2 WHERE rowid=64;
DELETE FROM t2 WHERE rowid=96;
DELETE FROM t2 WHERE rowid=128;
DELETE FROM t2 WHERE rowid=160;
DELETE FROM t2 WHERE rowid=192;
COMMIT;
}
do_execsql_test 2.2 {
SELECT * FROM t2('128');
} {}
#-------------------------------------------------------------------------
foreach {tn step} {
1 3
2 7
3 15
} {
set step [expr $step]
reset_db
db func document document
do_execsql_test 3.$tn.0 {
CREATE VIRTUAL TABLE t2 USING fts5(doc, content=, contentless_delete=1);
INSERT INTO t2(t2, rank) VALUES('pgsz', 100);
WITH s(i) AS (
SELECT 1 UNION ALL SELECT i+1 FROM s WHERE i<1000
)
INSERT INTO t2(rowid, doc) SELECT i, i || ' ' || i FROM s;
}
do_execsql_test 3.$tn.1 {
DELETE FROM t2 WHERE (rowid % $step)==0
}
do_execsql_test 3.$tn.2 {
SELECT * FROM t2( $step * 5 )
} {}
}
finish_test
|
