# 2011 April 25 # # 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. # #************************************************************************* # This file implements regression tests for SQLite library. The # focus of this script is testing the FTS3 module. set testdir [file dirname $argv0] source $testdir/tester.tcl set testprefix fts3conf # If SQLITE_ENABLE_FTS3 is defined, omit this file. ifcapable !fts3 { finish_test return } proc fts3_integrity {tn db tbl} { if {[sqlite3_get_autocommit $db]==0} { error "fts3_integrity does not work with an open transaction" } set sql [db one {SELECT sql FROM sqlite_master WHERE name = $tbl}] regexp -nocase {[^(]* using (.*)} $sql -> tail set cols [list] $db eval "PRAGMA table_info($tbl)" { lappend cols $name } set cols [join [concat docid $cols] ,] $db eval [subst { CREATE VIRTUAL TABLE fts3check USING fts4term($tbl); CREATE VIRTUAL TABLE temp.fts3check2 USING $tail; INSERT INTO temp.fts3check2($cols) SELECT docid, * FROM $tbl; CREATE VIRTUAL TABLE temp.fts3check3 USING fts4term(fts3check2); }] set m1 [$db one {SELECT md5sum(term, docid, col, pos) FROM fts3check}] set m2 [$db one {SELECT md5sum(term, docid, col, pos) FROM fts3check3}] $db eval { DROP TABLE fts3check; DROP TABLE temp.fts3check2; DROP TABLE temp.fts3check3; } uplevel [list do_test $tn [list set {} $m1] $m2] } do_execsql_test 1.0.1 { CREATE VIRTUAL TABLE t1 USING fts3(x); INSERT INTO t1(rowid, x) VALUES(1, 'a b c d'); INSERT INTO t1(rowid, x) VALUES(2, 'e f g h'); CREATE TABLE source(a, b); INSERT INTO source VALUES(4, 'z'); INSERT INTO source VALUES(2, 'y'); } db_save_and_close set T1 "INTO t1(rowid, x) VALUES(1, 'x')" set T2 "INTO t1(rowid, x) SELECT * FROM source" set T3 "t1 SET docid = 2 WHERE docid = 1" set T4 "t1 SET docid = CASE WHEN docid = 1 THEN 4 ELSE 3 END WHERE docid <=2" foreach {tn sql uses constraint data} [subst { 1 "INSERT OR ROLLBACK $T1" 0 1 {{a b c d} {e f g h}} 2 "INSERT OR ABORT $T1" 0 1 {{a b c d} {e f g h} {i j k l}} 3 "INSERT OR FAIL $T1" 0 1 {{a b c d} {e f g h} {i j k l}} 4 "INSERT OR IGNORE $T1" 0 0 {{a b c d} {e f g h} {i j k l}} 5 "INSERT OR REPLACE $T1" 0 0 {x {e f g h} {i j k l}} 6 "INSERT OR ROLLBACK $T2" 1 1 {{a b c d} {e f g h}} 7 "INSERT OR ABORT $T2" 1 1 {{a b c d} {e f g h} {i j k l}} 8 "INSERT OR FAIL $T2" 1 1 {{a b c d} {e f g h} {i j k l} z} 9 "INSERT OR IGNORE $T2" 1 0 {{a b c d} {e f g h} {i j k l} z} 10 "INSERT OR REPLACE $T2" 1 0 {{a b c d} y {i j k l} z} 11 "UPDATE OR ROLLBACK $T3" 1 1 {{a b c d} {e f g h}} 12 "UPDATE OR ABORT $T3" 1 1 {{a b c d} {e f g h} {i j k l}} 13 "UPDATE OR FAIL $T3" 1 1 {{a b c d} {e f g h} {i j k l}} 14 "UPDATE OR IGNORE $T3" 1 0 {{a b c d} {e f g h} {i j k l}} 15 "UPDATE OR REPLACE $T3" 1 0 {{a b c d} {i j k l}} 16 "UPDATE OR ROLLBACK $T4" 1 1 {{a b c d} {e f g h}} 17 "UPDATE OR ABORT $T4" 1 1 {{a b c d} {e f g h} {i j k l}} 18 "UPDATE OR FAIL $T4" 1 1 {{e f g h} {i j k l} {a b c d}} 19 "UPDATE OR IGNORE $T4" 1 0 {{e f g h} {i j k l} {a b c d}} 20 "UPDATE OR REPLACE $T4" 1 0 {{e f g h} {a b c d}} }] { db_restore_and_reopen execsql { BEGIN; INSERT INTO t1(rowid, x) VALUES(3, 'i j k l'); } set R(0) {0 {}} set R(1) {1 {constraint failed}} do_catchsql_test 1.$tn.1 $sql $R($constraint) do_catchsql_test 1.$tn.2 { SELECT * FROM t1 } [list 0 $data] catchsql COMMIT fts3_integrity 1.$tn.3 db t1 do_test 1.$tn.4 [list sql_uses_stmt db $sql] $uses } do_execsql_test 2.1.1 { DELETE FROM t1; BEGIN; INSERT INTO t1 VALUES('a b c'); SAVEPOINT a; INSERT INTO t1 VALUES('x y z'); ROLLBACK TO a; COMMIT; } fts3_integrity 2.1.2 db t1 do_catchsql_test 2.2.1 { DELETE FROM t1; BEGIN; INSERT INTO t1(docid, x) VALUES(0, 'a b c'); INSERT INTO t1(docid, x) VALUES(1, 'a b c'); REPLACE INTO t1(docid, x) VALUES('zero', 'd e f'); } {1 {datatype mismatch}} do_execsql_test 2.2.2 { COMMIT } do_execsql_test 2.2.3 { SELECT * FROM t1 } {{a b c} {a b c}} fts3_integrity 2.2.4 db t1 finish_test