1# 2# 2010 September 17 3# 4# May you do good and not evil. 5# May you find forgiveness for yourself and forgive others. 6# May you share freely, never taking more than you give. 7# 8#*********************************************************************** 9# This file implements regression tests for SQLite library. The 10# focus of this file is the interactions between the FTS3/4 module 11# and shared-cache mode. 12# 13 14set testdir [file dirname $argv0] 15source $testdir/tester.tcl 16 17ifcapable !fts3||!shared_cache { 18 finish_test 19 return 20} 21set ::testprefix fts3shared 22 23db close 24set ::enable_shared_cache [sqlite3_enable_shared_cache 1] 25 26# Open two connections to the database in shared-cache mode. 27# 28sqlite3 db test.db 29sqlite3 db2 test.db 30 31# Create a virtual FTS3 table. Populate it with some initial data. 32# 33do_execsql_test fts3shared-1.1 { 34 CREATE VIRTUAL TABLE t1 USING fts3(x); 35 BEGIN; 36 INSERT INTO t1 VALUES('We listened and looked sideways up!'); 37 INSERT INTO t1 VALUES('Fear at my heart, as at a cup,'); 38 INSERT INTO t1 VALUES('My life-blood seemed to sip!'); 39 INSERT INTO t1 VALUES('The stars were dim, and thick the night'); 40 COMMIT; 41} {} 42 43# Open a write transaction and insert rows into the FTS3 table. This takes 44# a write-lock on the underlying t1_content table. 45# 46do_execsql_test fts3shared-1.2 { 47 BEGIN; 48 INSERT INTO t1 VALUES('The steersman''s face by his lamp gleamed white;'); 49} {} 50 51# Now try a SELECT on the full-text table. This particular SELECT does not 52# read data from the %_content table. But it still attempts to obtain a lock 53# on that table and so the SELECT fails. 54# 55do_test fts3shared-1.3 { 56 catchsql { 57 BEGIN; 58 SELECT rowid FROM t1 WHERE t1 MATCH 'stars' 59 } db2 60} {1 {database table is locked}} 61 62# Verify that the first connection can commit its transaction. 63# 64do_test fts3shared-1.4 { sqlite3_get_autocommit db } 0 65do_execsql_test fts3shared-1.5 { COMMIT } {} 66do_test fts3shared-1.6 { sqlite3_get_autocommit db } 1 67 68# Verify that the second connection still has an open transaction. 69# 70do_test fts3shared-1.6 { sqlite3_get_autocommit db2 } 0 71 72db close 73db2 close 74 75#------------------------------------------------------------------------- 76# The following tests - fts3shared-2.* - test that unless FTS is bypassed 77# and the underlying tables accessed directly, it is not possible for an 78# SQLITE_LOCKED error to be enountered when committing an FTS transaction. 79# 80# Any SQLITE_LOCKED error should be returned when the fts4 (or fts4aux) 81# table is first read/written within a transaction, not later on. 82# 83set LOCKED {1 {database table is locked}} 84forcedelete test.db 85sqlite3 dbR test.db 86sqlite3 dbW test.db 87do_test 2.1 { 88 execsql { 89 CREATE VIRTUAL TABLE t1 USING fts4; 90 CREATE TABLE t2ext(a, b); 91 CREATE VIRTUAL TABLE t2 USING fts4(content=t2ext); 92 CREATE VIRTUAL TABLE t1aux USING fts4aux(t1); 93 CREATE VIRTUAL TABLE t2aux USING fts4aux(t2); 94 95 INSERT INTO t1 VALUES('a b c'); 96 INSERT INTO t2(rowid, a, b) VALUES(1, 'd e f', 'g h i'); 97 } dbW 98} {} 99 100# Test that once [dbW] has written to the FTS table, no client may read 101# from the FTS or fts4aux table. 102do_test 2.2.1 { 103 execsql { 104 BEGIN; 105 INSERT INTO t1 VALUES('j k l'); 106 } dbW 107 execsql BEGIN dbR 108} {} 109do_test 2.2.2 { catchsql "SELECT * FROM t1 WHERE rowid=1" dbR } $LOCKED 110do_test 2.2.3 { catchsql "SELECT * FROM t1 WHERE t1 MATCH 'a'" dbR } $LOCKED 111do_test 2.2.4 { catchsql "SELECT rowid FROM t1 WHERE t1 MATCH 'a'" dbR } $LOCKED 112do_test 2.2.5 { catchsql "SELECT * FROM t1" dbR } $LOCKED 113do_test 2.2.6 { catchsql "SELECT * FROM t1aux" dbR } $LOCKED 114do_test 2.2.7 { execsql COMMIT dbW } {} 115do_test 2.2.8 { execsql COMMIT dbR } {} 116 117# Same test as 2.2.*, except with a content= table. 118# 119do_test 2.3.1 { 120 execsql { 121 BEGIN; 122 INSERT INTO t2(rowid, a, b) VALUES(2, 'j k l', 'm n o'); 123 } dbW 124 execsql BEGIN dbR 125} {} 126do_test 2.3.3 { catchsql "SELECT * FROM t2 WHERE t2 MATCH 'a'" dbR } $LOCKED 127do_test 2.3.4 { catchsql "SELECT rowid FROM t2 WHERE t2 MATCH 'a'" dbR } $LOCKED 128do_test 2.3.6 { catchsql "SELECT * FROM t2aux" dbR } $LOCKED 129do_test 2.3.7 { execsql COMMIT dbW } {} 130do_test 2.3.8 { execsql COMMIT dbR } {} 131 132# Test that once a connection has read from the FTS or fts4aux table, 133# another connection may not write to the FTS table. 134# 135foreach {tn sql} { 136 1 "SELECT * FROM t1 WHERE rowid=1" 137 2 "SELECT * FROM t1 WHERE t1 MATCH 'a'" 138 3 "SELECT rowid FROM t1 WHERE t1 MATCH 'a'" 139 4 "SELECT * FROM t1" 140 5 "SELECT * FROM t1aux" 141} { 142 143 do_test 2.4.$tn { 144 execsql BEGIN dbR 145 execsql $::sql dbR 146 execsql BEGIN dbW 147 catchsql "INSERT INTO t1 VALUES('p q r')" dbW 148 } $LOCKED 149 150 execsql ROLLBACK dbR 151 execsql ROLLBACK dbW 152} 153 154# Same test as 2.4.*, except with a content= table. 155# 156foreach {tn sql} { 157 2 "SELECT * FROM t2 WHERE t2 MATCH 'a'" 158 3 "SELECT rowid FROM t2 WHERE t2 MATCH 'a'" 159 5 "SELECT * FROM t2aux" 160} { 161 162 do_test 2.5.$tn { 163 execsql BEGIN dbR 164 execsql $::sql dbR 165 execsql BEGIN dbW 166 catchsql "INSERT INTO t2(rowid, a, b) VALUES(3, 's t u', 'v w x')" dbW 167 } $LOCKED 168 169 execsql ROLLBACK dbR 170 execsql ROLLBACK dbW 171} 172 173dbW close 174dbR close 175sqlite3_enable_shared_cache $::enable_shared_cache 176finish_test 177