1# 2015 Sep 27
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#
12
13source [file join [file dirname [info script]] fts5_common.tcl]
14set testprefix fts5onepass
15
16# If SQLITE_ENABLE_FTS3 is defined, omit this file.
17ifcapable !fts5 {
18  finish_test
19  return
20}
21
22do_execsql_test 1.0 {
23  CREATE VIRTUAL TABLE ft USING fts5(content);
24  INSERT INTO ft(rowid, content) VALUES(1, '1 2 3');
25  INSERT INTO ft(rowid, content) VALUES(2, '4 5 6');
26  INSERT INTO ft(rowid, content) VALUES(3, '7 8 9');
27}
28
29#-------------------------------------------------------------------------
30# Check that UPDATE and DELETE statements that feature "WHERE rowid=?" or
31# or "WHERE rowid=?" clauses do not use statement journals. But that other
32# DELETE and UPDATE statements do.
33#
34# Note: "MATCH ? AND rowid=?" does use a statement journal.
35#
36foreach {tn sql uses} {
37  1.1 { DELETE FROM ft } 1
38  1.2 { DELETE FROM ft WHERE rowid=? } 0
39  1.3 { DELETE FROM ft WHERE rowid=? } 0
40  1.4 { DELETE FROM ft WHERE ft MATCH '1' } 1
41  1.5 { DELETE FROM ft WHERE ft MATCH '1' AND rowid=? } 1
42  1.6 { DELETE FROM ft WHERE ft MATCH '1' AND rowid=? } 1
43
44  2.1 { UPDATE ft SET content='a b c' } 1
45  2.2 { UPDATE ft SET content='a b c' WHERE rowid=? } 0
46  2.3 { UPDATE ft SET content='a b c' WHERE rowid=? } 0
47  2.4 { UPDATE ft SET content='a b c' WHERE ft MATCH '1' } 1
48  2.5 { UPDATE ft SET content='a b c' WHERE ft MATCH '1' AND rowid=? } 1
49  2.6 { UPDATE ft SET content='a b c' WHERE ft MATCH '1' AND rowid=? } 1
50} {
51  do_test 1.$tn { sql_uses_stmt db $sql } $uses
52}
53
54#-------------------------------------------------------------------------
55# Check that putting a "DELETE/UPDATE ... WHERE rowid=?" statement in a
56# trigger program does not prevent the VM from using a statement
57# transaction. Even if the calling statement cannot hit a constraint.
58#
59do_execsql_test 2.0 {
60  CREATE TABLE t1(x);
61
62  CREATE TRIGGER t1_ai AFTER INSERT ON t1 BEGIN
63    DELETE FROM ft WHERE rowid=new.x;
64  END;
65
66  CREATE TRIGGER t1_ad AFTER DELETE ON t1 BEGIN
67    UPDATE ft SET content = 'a b c' WHERE rowid=old.x;
68  END;
69
70  CREATE TRIGGER t1_bu BEFORE UPDATE ON t1 BEGIN
71    DELETE FROM ft WHERE rowid=old.x;
72  END;
73}
74
75foreach {tn sql uses} {
76  1 { INSERT INTO t1 VALUES(1)      } 1
77  2 { DELETE FROM t1 WHERE x=4      } 1
78  3 { UPDATE t1 SET x=10 WHERE x=11 } 1
79} {
80  do_test 2.$tn { sql_uses_stmt db $sql } $uses
81}
82
83#-------------------------------------------------------------------------
84# Test that an "UPDATE ... WHERE rowid=?" works and does not corrupt the
85# index when it strikes a constraint. Both inside and outside a
86# transaction.
87#
88foreach {tn tcl1 tcl2}  {
89  1 {} {}
90
91  2 {
92    execsql BEGIN
93  } {
94    if {[sqlite3_get_autocommit db]==1} { error "transaction rolled back!" }
95    execsql COMMIT
96  }
97} {
98
99  do_execsql_test 3.$tn.0 {
100    DROP TABLE IF EXISTS ft2;
101    CREATE VIRTUAL TABLE ft2 USING fts5(content);
102    INSERT INTO ft2(rowid, content) VALUES(1, 'a b c');
103    INSERT INTO ft2(rowid, content) VALUES(2, 'a b d');
104    INSERT INTO ft2(rowid, content) VALUES(3, 'a b e');
105  }
106
107  eval $tcl1
108  foreach {tn2 sql content} {
109    1 { UPDATE ft2 SET rowid=2 WHERE rowid=1 }
110      { 1 {a b c} 2 {a b d} 3 {a b e} }
111
112    2 {
113      INSERT INTO ft2(rowid, content) VALUES(4, 'a b f');
114      UPDATE ft2 SET rowid=5 WHERE rowid=4;
115      UPDATE ft2 SET rowid=3 WHERE rowid=5;
116    } { 1 {a b c} 2 {a b d} 3 {a b e} 5 {a b f} }
117
118    3 {
119      UPDATE ft2 SET rowid=3 WHERE rowid=4;           -- matches 0 rows
120      UPDATE ft2 SET rowid=2 WHERE rowid=3;
121    } { 1 {a b c} 2 {a b d} 3 {a b e} 5 {a b f} }
122
123    4 {
124      INSERT INTO ft2(rowid, content) VALUES(4, 'a b g');
125      UPDATE ft2 SET rowid=-1 WHERE rowid=4;
126      UPDATE ft2 SET rowid=3 WHERE rowid=-1;
127    } {-1 {a b g} 1 {a b c} 2 {a b d} 3 {a b e} 5 {a b f} }
128
129    5 {
130      DELETE FROM ft2 WHERE rowid=451;
131      DELETE FROM ft2 WHERE rowid=-1;
132      UPDATE ft2 SET rowid = 2 WHERE rowid = 1;
133    } {1 {a b c} 2 {a b d} 3 {a b e} 5 {a b f} }
134  } {
135    do_catchsql_test 3.$tn.$tn2.a $sql {1 {constraint failed}}
136    do_execsql_test  3.$tn.$tn2.b { SELECT rowid, content FROM ft2 } $content
137
138    do_execsql_test  3.$tn.$tn2.c {
139      INSERT INTO ft2(ft2) VALUES('integrity-check');
140    }
141  }
142  eval $tcl2
143}
144
145#-------------------------------------------------------------------------
146# Check that DELETE and UPDATE operations can be done without flushing
147# the in-memory hash table to disk.
148#
149reset_db
150do_execsql_test 4.1.1 {
151  CREATE VIRTUAL TABLE ttt USING fts5(x);
152  BEGIN;
153    INSERT INTO ttt(rowid, x) VALUES(1, 'a b c');
154    INSERT INTO ttt(rowid, x) VALUES(2, 'a b c');
155    INSERT INTO ttt(rowid, x) VALUES(3, 'a b c');
156  COMMIT
157}
158do_test 4.1.2 { fts5_level_segs ttt } {1}
159
160do_execsql_test 4.2.1 {
161  BEGIN;
162    DELETE FROM ttt WHERE rowid=1;
163    DELETE FROM ttt WHERE rowid=3;
164    INSERT INTO ttt(rowid, x) VALUES(4, 'd e f');
165    INSERT INTO ttt(rowid, x) VALUES(5, 'd e f');
166  COMMIT;
167} {}
168do_test 4.2.2 { fts5_level_segs ttt } {2}
169
170
171do_execsql_test 4.3.1 {
172  BEGIN;
173    UPDATE ttt SET x = 'd e f' WHERE rowid = 2;
174    UPDATE ttt SET x = 'A B C' WHERE rowid = 4;
175    INSERT INTO ttt(rowid, x) VALUES(6, 'd e f');
176  COMMIT;
177} {}
178do_test 4.2.2 { fts5_level_segs ttt } {3}
179
180finish_test
181