AryaWu/sqlite
0
1# 2011 April 252#3# The author disclaims copyright to this source code. In place of4# 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. The12# focus of this script is testing the FTS3 module.13 14 15set testdir [file dirname $argv0]16source $testdir/tester.tcl17set testprefix fts3conf18 19# If SQLITE_ENABLE_FTS3 is defined, omit this file.20ifcapable !fts3 {21 finish_test22 return23}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 -> tail34 set cols [list]35 $db eval "PRAGMA table_info($tbl)" {36 lappend cols $name37 }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_close69 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_reopen102 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 COMMIT111 112 fts3_integrity 1.$tn.3 db t1113 114 do_test 1.$tn.4 [list sql_uses_stmt db $sql] $uses115}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 t1127 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 t1138 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) SELECT191 1, 'abc' UNION ALL SELECT192 2, 'def' UNION ALL SELECT193 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 PRAGMA integrity_check;202} {ok}203 204do_execsql_test 4.2.1 {205 CREATE VIRTUAL TABLE t01 USING fts4;206 BEGIN;207 SAVEPOINT abc;208 INSERT INTO t01 VALUES('a b c');209 ROLLBACK TO abc;210 COMMIT;211}212do_execsql_test 4.2.2 {213 SELECT * FROM t01 WHERE t01 MATCH 'b';214 INSERT INTO t01(t01) VALUES('integrity-check');215 PRAGMA integrity_check;216} {ok}217 218do_execsql_test 4.3.1 {219 CREATE VIRTUAL TABLE t02 USING fts4;220 INSERT INTO t01 VALUES('1 1 1');221 INSERT INTO t02 VALUES('2 2 2');222 BEGIN;223 SAVEPOINT abc;224 INSERT INTO t01 VALUES('a b c');225 INSERT INTO t02 VALUES('a b c');226 ROLLBACK TO abc;227 COMMIT;228}229do_execsql_test 4.3.2 {230 SELECT * FROM t01 WHERE t01 MATCH 'b';231 INSERT INTO t01(t01) VALUES('integrity-check');232} {}233 234do_execsql_test 4.4.1 {235 CREATE TABLE A(ID INTEGER PRIMARY KEY, AnotherID INTEGER, Notes TEXT);236 CREATE VIRTUAL TABLE AFTS USING FTS4 (Notes);237 CREATE TRIGGER A_DeleteTrigger AFTER DELETE ON A FOR EACH ROW BEGIN 238 DELETE FROM AFTS WHERE rowid=OLD.ID; 239 END;240 CREATE TABLE B(ID INTEGER PRIMARY KEY,Notes TEXT);241 CREATE VIRTUAL TABLE BFTS USING FTS3 (Notes);242 CREATE TRIGGER B_DeleteTrigger AFTER DELETE ON B FOR EACH ROW BEGIN 243 DELETE FROM BFTS WHERE rowid=OLD.ID; 244 END;245}246 247do_execsql_test 4.4.2 {248 BEGIN TRANSACTION;249 DELETE FROM A WHERE AnotherID=1;250 DELETE FROM B WHERE ID=1;251 COMMIT;252}253 254 255 256finish_test257