1# 2011 April 25 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 FTS3 module. 13 14 15set testdir [file dirname $argv0] 16source $testdir/tester.tcl 17set testprefix fts3conf 18 19# If SQLITE_ENABLE_FTS3 is defined, omit this file. 20ifcapable !fts3 { 21 finish_test 22 return 23} 24 25 26proc fts3_integrity {tn db tbl} { 27 28 if {[sqlite3_get_autocommit $db]==0} { 29 error "fts3_integrity does not work with an open transaction" 30 } 31 32 set sql [db one {SELECT sql FROM sqlite_master WHERE name = $tbl}] 33 regexp -nocase {[^(]* using (.*)} $sql -> tail 34 set cols [list] 35 $db eval "PRAGMA table_info($tbl)" { 36 lappend cols $name 37 } 38 set cols [join [concat docid $cols] ,] 39 40 $db eval [subst { 41 CREATE VIRTUAL TABLE fts3check USING fts4term($tbl); 42 CREATE VIRTUAL TABLE temp.fts3check2 USING $tail; 43 INSERT INTO temp.fts3check2($cols) SELECT docid, * FROM $tbl; 44 CREATE VIRTUAL TABLE temp.fts3check3 USING fts4term(fts3check2); 45 }] 46 47 set m1 [$db one {SELECT md5sum(term, docid, col, pos) FROM fts3check}] 48 set m2 [$db one {SELECT md5sum(term, docid, col, pos) FROM fts3check3}] 49 50 $db eval { 51 DROP TABLE fts3check; 52 DROP TABLE temp.fts3check2; 53 DROP TABLE temp.fts3check3; 54 } 55 56 uplevel [list do_test $tn [list set {} $m1] $m2] 57} 58 59do_execsql_test 1.0.1 { 60 CREATE VIRTUAL TABLE t1 USING fts3(x); 61 INSERT INTO t1(rowid, x) VALUES(1, 'a b c d'); 62 INSERT INTO t1(rowid, x) VALUES(2, 'e f g h'); 63 64 CREATE TABLE source(a, b); 65 INSERT INTO source VALUES(4, 'z'); 66 INSERT INTO source VALUES(2, 'y'); 67} 68db_save_and_close 69 70set T1 "INTO t1(rowid, x) VALUES(1, 'x')" 71set T2 "INTO t1(rowid, x) SELECT * FROM source" 72 73set T3 "t1 SET docid = 2 WHERE docid = 1" 74set T4 "t1 SET docid = CASE WHEN docid = 1 THEN 4 ELSE 3 END WHERE docid <=2" 75 76foreach {tn sql uses constraint data} [subst { 77 1 "INSERT OR ROLLBACK $T1" 0 1 {{a b c d} {e f g h}} 78 2 "INSERT OR ABORT $T1" 0 1 {{a b c d} {e f g h} {i j k l}} 79 3 "INSERT OR FAIL $T1" 0 1 {{a b c d} {e f g h} {i j k l}} 80 4 "INSERT OR IGNORE $T1" 0 0 {{a b c d} {e f g h} {i j k l}} 81 5 "INSERT OR REPLACE $T1" 0 0 {x {e f g h} {i j k l}} 82 83 6 "INSERT OR ROLLBACK $T2" 1 1 {{a b c d} {e f g h}} 84 7 "INSERT OR ABORT $T2" 1 1 {{a b c d} {e f g h} {i j k l}} 85 8 "INSERT OR FAIL $T2" 1 1 {{a b c d} {e f g h} {i j k l} z} 86 9 "INSERT OR IGNORE $T2" 1 0 {{a b c d} {e f g h} {i j k l} z} 87 10 "INSERT OR REPLACE $T2" 1 0 {{a b c d} y {i j k l} z} 88 89 11 "UPDATE OR ROLLBACK $T3" 0 1 {{a b c d} {e f g h}} 90 12 "UPDATE OR ABORT $T3" 0 1 {{a b c d} {e f g h} {i j k l}} 91 13 "UPDATE OR FAIL $T3" 0 1 {{a b c d} {e f g h} {i j k l}} 92 14 "UPDATE OR IGNORE $T3" 0 0 {{a b c d} {e f g h} {i j k l}} 93 15 "UPDATE OR REPLACE $T3" 0 0 {{a b c d} {i j k l}} 94 95 16 "UPDATE OR ROLLBACK $T4" 1 1 {{a b c d} {e f g h}} 96 17 "UPDATE OR ABORT $T4" 1 1 {{a b c d} {e f g h} {i j k l}} 97 18 "UPDATE OR FAIL $T4" 1 1 {{e f g h} {i j k l} {a b c d}} 98 19 "UPDATE OR IGNORE $T4" 1 0 {{e f g h} {i j k l} {a b c d}} 99 20 "UPDATE OR REPLACE $T4" 1 0 {{e f g h} {a b c d}} 100}] { 101 db_restore_and_reopen 102 execsql { 103 BEGIN; 104 INSERT INTO t1(rowid, x) VALUES(3, 'i j k l'); 105 } 106 set R(0) {0 {}} 107 set R(1) {1 {constraint failed}} 108 do_catchsql_test 1.$tn.1 $sql $R($constraint) 109 do_catchsql_test 1.$tn.2 { SELECT * FROM t1 } [list 0 $data] 110 catchsql COMMIT 111 112 fts3_integrity 1.$tn.3 db t1 113 114 do_test 1.$tn.4 [list sql_uses_stmt db $sql] $uses 115} 116 117do_execsql_test 2.1.1 { 118 DELETE FROM t1; 119 BEGIN; 120 INSERT INTO t1 VALUES('a b c'); 121 SAVEPOINT a; 122 INSERT INTO t1 VALUES('x y z'); 123 ROLLBACK TO a; 124 COMMIT; 125} 126fts3_integrity 2.1.2 db t1 127 128do_catchsql_test 2.2.1 { 129 DELETE FROM t1; 130 BEGIN; 131 INSERT INTO t1(docid, x) VALUES(0, 'a b c'); 132 INSERT INTO t1(docid, x) VALUES(1, 'a b c'); 133 REPLACE INTO t1(docid, x) VALUES('zero', 'd e f'); 134} {1 {datatype mismatch}} 135do_execsql_test 2.2.2 { COMMIT } 136do_execsql_test 2.2.3 { SELECT * FROM t1 } {{a b c} {a b c}} 137fts3_integrity 2.2.4 db t1 138 139if {$tcl_platform(byteOrder)=="littleEndian"} { 140 do_execsql_test 3.1 { 141 CREATE VIRTUAL TABLE t3 USING fts4; 142 REPLACE INTO t3(docid, content) VALUES (1, 'one two'); 143 SELECT quote(matchinfo(t3, 'na')) FROM t3 WHERE t3 MATCH 'one' 144 } {X'0100000002000000'} 145 146 do_execsql_test 3.2 { 147 REPLACE INTO t3(docid, content) VALUES (2, 'one two three four'); 148 SELECT quote(matchinfo(t3, 'na')) FROM t3 WHERE t3 MATCH 'four' 149 } {X'0200000003000000'} 150 151 do_execsql_test 3.3 { 152 REPLACE INTO t3(docid, content) VALUES (1, 'one two three four five six'); 153 SELECT quote(matchinfo(t3, 'na')) FROM t3 WHERE t3 MATCH 'six' 154 } {X'0200000005000000'} 155 156 do_execsql_test 3.4 { 157 UPDATE OR REPLACE t3 SET docid = 2 WHERE docid=1; 158 SELECT quote(matchinfo(t3, 'na')) FROM t3 WHERE t3 MATCH 'six' 159 } {X'0100000006000000'} 160 161 do_execsql_test 3.5 { 162 UPDATE OR REPLACE t3 SET docid = 3 WHERE docid=2; 163 SELECT quote(matchinfo(t3, 'na')) FROM t3 WHERE t3 MATCH 'six' 164 } {X'0100000006000000'} 165 166 do_execsql_test 3.6 { 167 REPLACE INTO t3(docid, content) VALUES (3, 'one two'); 168 SELECT quote(matchinfo(t3, 'na')) FROM t3 WHERE t3 MATCH 'one' 169 } {X'0100000002000000'} 170 171 do_execsql_test 3.7 { 172 REPLACE INTO t3(docid, content) VALUES(NULL,'one two three four'); 173 REPLACE INTO t3(docid, content) VALUES(NULL,'one two three four five six'); 174 SELECT docid FROM t3; 175 } {3 4 5} 176 177 do_execsql_test 3.8 { 178 UPDATE OR REPLACE t3 SET docid = 5, content='three four' WHERE docid = 4; 179 SELECT quote(matchinfo(t3, 'na')) FROM t3 WHERE t3 MATCH 'one' 180 } {X'0200000002000000'} 181} 182 183#------------------------------------------------------------------------- 184# Test that the xSavepoint is invoked correctly if the first write 185# operation within a transaction is to a virtual table. 186# 187do_catchsql_test 4.1.1 { 188 CREATE VIRTUAL TABLE t0 USING fts4; 189 BEGIN; 190 INSERT INTO t0(rowid, content) SELECT 191 1, 'abc' UNION ALL SELECT 192 2, 'def' UNION ALL SELECT 193 1, 'ghi'; 194} {1 {constraint failed}} 195do_execsql_test 4.1.2 { 196 COMMIT; 197} 198do_execsql_test 4.1.3 { 199 SELECT * FROM t0 WHERE t0 MATCH 'abc'; 200 INSERT INTO t0(t0) VALUES('integrity-check'); 201} {} 202 203do_execsql_test 4.2.1 { 204 CREATE VIRTUAL TABLE t01 USING fts4; 205 BEGIN; 206 SAVEPOINT abc; 207 INSERT INTO t01 VALUES('a b c'); 208 ROLLBACK TO abc; 209 COMMIT; 210} 211do_execsql_test 4.2.2 { 212 SELECT * FROM t01 WHERE t01 MATCH 'b'; 213 INSERT INTO t01(t01) VALUES('integrity-check'); 214} {} 215 216do_execsql_test 4.3.1 { 217 CREATE VIRTUAL TABLE t02 USING fts4; 218 INSERT INTO t01 VALUES('1 1 1'); 219 INSERT INTO t02 VALUES('2 2 2'); 220 BEGIN; 221 SAVEPOINT abc; 222 INSERT INTO t01 VALUES('a b c'); 223 INSERT INTO t02 VALUES('a b c'); 224 ROLLBACK TO abc; 225 COMMIT; 226} 227do_execsql_test 4.3.2 { 228 SELECT * FROM t01 WHERE t01 MATCH 'b'; 229 INSERT INTO t01(t01) VALUES('integrity-check'); 230} {} 231 232do_execsql_test 4.4.1 { 233 CREATE TABLE A(ID INTEGER PRIMARY KEY, AnotherID INTEGER, Notes TEXT); 234 CREATE VIRTUAL TABLE AFTS USING FTS4 (Notes); 235 CREATE TRIGGER A_DeleteTrigger AFTER DELETE ON A FOR EACH ROW BEGIN 236 DELETE FROM AFTS WHERE rowid=OLD.ID; 237 END; 238 CREATE TABLE B(ID INTEGER PRIMARY KEY,Notes TEXT); 239 CREATE VIRTUAL TABLE BFTS USING FTS3 (Notes); 240 CREATE TRIGGER B_DeleteTrigger AFTER DELETE ON B FOR EACH ROW BEGIN 241 DELETE FROM BFTS WHERE rowid=OLD.ID; 242 END; 243} 244 245do_execsql_test 4.4.2 { 246 BEGIN TRANSACTION; 247 DELETE FROM A WHERE AnotherID=1; 248 DELETE FROM B WHERE ID=1; 249 COMMIT; 250} 251 252 253 254finish_test 255