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
|
# 2015 Sep 27
#
# 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 fts5onepass
# If SQLITE_ENABLE_FTS3 is defined, omit this file.
ifcapable !fts5 {
finish_test
return
}
do_execsql_test 1.0 {
CREATE VIRTUAL TABLE ft USING fts5(content);
INSERT INTO ft(rowid, content) VALUES(1, '1 2 3');
INSERT INTO ft(rowid, content) VALUES(2, '4 5 6');
INSERT INTO ft(rowid, content) VALUES(3, '7 8 9');
}
#-------------------------------------------------------------------------
# Check that UPDATE and DELETE statements that feature "WHERE rowid=?" or
# or "WHERE rowid=?" clauses do not use statement journals. But that other
# DELETE and UPDATE statements do.
#
# Note: "MATCH ? AND rowid=?" does use a statement journal.
#
foreach {tn sql uses} {
1.1 { DELETE FROM ft } 1
1.2 { DELETE FROM ft WHERE rowid=? } 0
1.3 { DELETE FROM ft WHERE rowid=? } 0
1.4 { DELETE FROM ft WHERE ft MATCH '1' } 1
1.5 { DELETE FROM ft WHERE ft MATCH '1' AND rowid=? } 1
1.6 { DELETE FROM ft WHERE ft MATCH '1' AND rowid=? } 1
2.1 { UPDATE ft SET content='a b c' } 1
2.2 { UPDATE ft SET content='a b c' WHERE rowid=? } 0
2.3 { UPDATE ft SET content='a b c' WHERE rowid=? } 0
2.4 { UPDATE ft SET content='a b c' WHERE ft MATCH '1' } 1
2.5 { UPDATE ft SET content='a b c' WHERE ft MATCH '1' AND rowid=? } 1
2.6 { UPDATE ft SET content='a b c' WHERE ft MATCH '1' AND rowid=? } 1
} {
do_test 1.$tn { sql_uses_stmt db $sql } $uses
}
#-------------------------------------------------------------------------
# Check that putting a "DELETE/UPDATE ... WHERE rowid=?" statement in a
# trigger program does not prevent the VM from using a statement
# transaction. Even if the calling statement cannot hit a constraint.
#
do_execsql_test 2.0 {
CREATE TABLE t1(x);
CREATE TRIGGER t1_ai AFTER INSERT ON t1 BEGIN
DELETE FROM ft WHERE rowid=new.x;
END;
CREATE TRIGGER t1_ad AFTER DELETE ON t1 BEGIN
UPDATE ft SET content = 'a b c' WHERE rowid=old.x;
END;
CREATE TRIGGER t1_bu BEFORE UPDATE ON t1 BEGIN
DELETE FROM ft WHERE rowid=old.x;
END;
}
foreach {tn sql uses} {
1 { INSERT INTO t1 VALUES(1) } 1
2 { DELETE FROM t1 WHERE x=4 } 1
3 { UPDATE t1 SET x=10 WHERE x=11 } 1
} {
do_test 2.$tn { sql_uses_stmt db $sql } $uses
}
#-------------------------------------------------------------------------
# Test that an "UPDATE ... WHERE rowid=?" works and does not corrupt the
# index when it strikes a constraint. Both inside and outside a
# transaction.
#
foreach {tn tcl1 tcl2} {
1 {} {}
2 {
execsql BEGIN
} {
if {[sqlite3_get_autocommit db]==1} { error "transaction rolled back!" }
execsql COMMIT
}
} {
do_execsql_test 3.$tn.0 {
DROP TABLE IF EXISTS ft2;
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');
INSERT INTO ft2(rowid, content) VALUES(3, 'a b e');
}
eval $tcl1
foreach {tn2 sql content} {
1 { UPDATE ft2 SET rowid=2 WHERE rowid=1 }
{ 1 {a b c} 2 {a b d} 3 {a b e} }
2 {
INSERT INTO ft2(rowid, content) VALUES(4, 'a b f');
UPDATE ft2 SET rowid=5 WHERE rowid=4;
UPDATE ft2 SET rowid=3 WHERE rowid=5;
} { 1 {a b c} 2 {a b d} 3 {a b e} 5 {a b f} }
3 {
UPDATE ft2 SET rowid=3 WHERE rowid=4; -- matches 0 rows
UPDATE ft2 SET rowid=2 WHERE rowid=3;
} { 1 {a b c} 2 {a b d} 3 {a b e} 5 {a b f} }
4 {
INSERT INTO ft2(rowid, content) VALUES(4, 'a b g');
UPDATE ft2 SET rowid=-1 WHERE rowid=4;
UPDATE ft2 SET rowid=3 WHERE rowid=-1;
} {-1 {a b g} 1 {a b c} 2 {a b d} 3 {a b e} 5 {a b f} }
5 {
DELETE FROM ft2 WHERE rowid=451;
DELETE FROM ft2 WHERE rowid=-1;
UPDATE ft2 SET rowid = 2 WHERE rowid = 1;
} {1 {a b c} 2 {a b d} 3 {a b e} 5 {a b f} }
} {
do_catchsql_test 3.$tn.$tn2.a $sql {1 {constraint failed}}
do_execsql_test 3.$tn.$tn2.b { SELECT rowid, content FROM ft2 } $content
do_execsql_test 3.$tn.$tn2.c {
INSERT INTO ft2(ft2) VALUES('integrity-check');
}
}
eval $tcl2
}
#-------------------------------------------------------------------------
# Check that DELETE and UPDATE operations can be done without flushing
# the in-memory hash table to disk.
#
reset_db
do_execsql_test 4.1.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 4.1.2 { fts5_level_segs ttt } {1}
do_execsql_test 4.2.1 {
BEGIN;
DELETE FROM ttt WHERE rowid=1;
DELETE FROM ttt WHERE rowid=3;
INSERT INTO ttt(rowid, x) VALUES(4, 'd e f');
INSERT INTO ttt(rowid, x) VALUES(5, 'd e f');
COMMIT;
} {}
do_test 4.2.2 { fts5_level_segs ttt } {2}
do_execsql_test 4.3.1 {
BEGIN;
UPDATE ttt SET x = 'd e f' WHERE rowid = 2;
UPDATE ttt SET x = 'A B C' WHERE rowid = 4;
INSERT INTO ttt(rowid, x) VALUES(6, 'd e f');
COMMIT;
} {}
do_test 4.2.2 { fts5_level_segs ttt } {3}
finish_test
|
