Debian Codegraph @

ext/fts5/test/fts5delete.test

raw
1 # 2017 May 12
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
15 source [file join [file dirname [info script]] fts5_common.tcl]
16 set testprefix fts5delete
17
18 # If SQLITE_ENABLE_FTS5 is not defined, omit this file.
19 ifcapable !fts5 {
20 finish_test
21 return
22 }
23 fts5_aux_test_functions db
24
25 do_execsql_test 1.0 {
26 CREATE VIRTUAL TABLE t1 USING fts5(x);
27 WITH s(i) AS (
28 SELECT 1 UNION ALL SELECT i+1 FROM s WHERE i<5000
29 )
30 INSERT INTO t1(rowid, x) SELECT i, (i/2)*2 FROM s;
31 }
32
33 do_test 1.1 {
34 execsql BEGIN
35 for {set i 1} {$i<=5000} {incr i} {
36 if {$i % 2} {
37 execsql { INSERT INTO t1 VALUES($i) }
38 } else {
39 execsql { DELETE FROM t1 WHERE rowid = $i }
40 }
41 }
42 execsql COMMIT
43 } {}
44
45 do_test 1.2 {
46 execsql { INSERT INTO t1(t1, rank) VALUES('usermerge', 2); }
47 for {set i 0} {$i < 5} {incr i} {
48 execsql { INSERT INTO t1(t1, rank) VALUES('merge', 1) }
49 execsql { INSERT INTO t1(t1) VALUES('integrity-check') }
50 }
51 } {}
52
53 #-------------------------------------------------------------------------
54 reset_db
55 do_execsql_test 2.0 {
56 CREATE TABLE test (
57 id INTEGER PRIMARY KEY,
58 name TEXT,
59 value TEXT
60 );
61 CREATE VIRTUAL TABLE test_idx USING fts5(
62 name, content=test, content_rowid=id
63 );
64 }
65
66 do_catchsql_test 2.1 {
67 INSERT INTO test_idx (test_idx, rowid, name) VALUES('delete', 1, 'quick');
68 } {1 {database disk image is malformed}}
69
70 do_catchsql_test 2.2 {
71 INSERT INTO test_idx(rowid, name) VALUES(123, 'one one one');
72 INSERT INTO test_idx (test_idx, rowid, name) VALUES('delete', 123, 'one');
73 INSERT INTO test_idx (test_idx, rowid, name) VALUES('delete', 123, 'one');
74 } {1 {database disk image is malformed}}
75
76 do_execsql_test 2.3 {
77 DROP TABLE test_idx;
78 CREATE VIRTUAL TABLE test_idx USING fts5(
79 name, content=test, content_rowid=id
80 );
81
82 INSERT INTO test_idx(rowid, name) VALUES(123, 'one one one');
83 INSERT INTO test_idx(rowid, name) VALUES(124, 'two two two');
84 INSERT INTO test_idx(rowid, name) VALUES(125, 'two two two');
85 INSERT INTO test_idx (test_idx, rowid, name) VALUES('delete', 123, 'one');
86 INSERT INTO test_idx (test_idx, rowid, name) VALUES('delete', 123, 'one');
87 INSERT INTO test_idx (test_idx, rowid, name) VALUES('delete', 123, 'one');
88 }
89
90 do_catchsql_test 2.4 {
91 SELECT rowid FROM test_idx WHERE test_idx MATCH 'two' ORDER BY rank;
92 } {1 {database disk image is malformed}}
93
94 #-------------------------------------------------------------------------
95 reset_db
96 do_execsql_test 3.0 {
97 CREATE VIRTUAL TABLE tx USING fts5(a, b, c, d, content=);
98 INSERT INTO tx(rowid, a, c) VALUES(1, 'abc def', 'a b c');
99 INSERT INTO tx(rowid, a, c) VALUES(5, 'a b c', 'a b d def');
100 }
101 do_execsql_test 3.1 {
102 INSERT INTO tx(tx, rowid, a, b, c, d)
103 VALUES('delete', 5, 'a b c', NULL, 'a b d def', NULL);
104 }
105 do_execsql_test 3.2 {
106 INSERT INTO tx(tx) VALUES('integrity-check');
107 }
108 do_execsql_test 3.3 {
109 INSERT INTO tx(tx, rowid, a, b, c, d)
110 VALUES('delete', 1, 'abc def', NULL, 'a b c', NULL);
111 }
112 do_execsql_test 3.4 {
113 INSERT INTO tx(tx) VALUES('integrity-check');
114 }
115
116 #-------------------------------------------------------------------------
117 reset_db
118 do_execsql_test 4.0 {
119 CREATE VIRTUAL TABLE ft1 USING fts5(a, b UNINDEXED,
120 content='', contentless_unindexed=1
121 );
122 CREATE VIRTUAL TABLE ft2 USING fts5(a, b UNINDEXED,
123 content='', contentless_unindexed=1, contentless_delete=1
124 );
125
126 INSERT INTO ft1(rowid, a, b) VALUES(1, 'one', 'i');
127 INSERT INTO ft1(rowid, a, b) VALUES(2, 'two', 'ii');
128 INSERT INTO ft1(rowid, a, b) VALUES(3, 'three', 'iii');
129 INSERT INTO ft2(rowid, a, b) VALUES(1, 'one', 'i');
130 INSERT INTO ft2(rowid, a, b) VALUES(2, 'two', 'ii');
131 INSERT INTO ft2(rowid, a, b) VALUES(3, 'three', 'iii');
132 }
133
134 do_catchsql_test 4.1 {
135 DELETE FROM ft1 WHERE rowid=2
136 } {1 {cannot DELETE from contentless fts5 table: ft1}}
137 do_catchsql_test 4.2 {
138 DELETE FROM ft2 WHERE rowid=2
139 } {0 {}}
140
141 do_catchsql_test 4.3 {
142 INSERT INTO ft1(ft1, rowid, a) VALUES('delete', 2, 'two');
143 } {0 {}}
144 do_catchsql_test 4.2 {
145 INSERT INTO ft2(ft2, rowid, a) VALUES('delete', 2, 'two');
146 } {1 {'delete' may not be used with a contentless_delete=1 table}}
147
148 do_execsql_test 4.3 {
149 SELECT rowid, * FROM ft1;
150 } {
151 1 {} i
152 3 {} iii
153 }
154 do_execsql_test 4.4 {
155 SELECT rowid, * FROM ft2;
156 } {
157 1 {} i
158 3 {} iii
159 }
160
161 do_execsql_test 4.5 {
162 SELECT * FROM ft1_content
163 } {1 i 3 iii}
164
165 do_execsql_test 4.6 {
166 SELECT * FROM ft2_content
167 } {1 i 3 iii}
168
169 finish_test
170