AryaWu/sqlite

1#2# 2010 September 173#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.  The10# 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.tcl16 17ifcapable !fts3||!shared_cache {18  finish_test19  return20}21set ::testprefix fts3shared22 23db close24set ::enable_shared_cache [sqlite3_enable_shared_cache 1]25 26# Open two connections to the database in shared-cache mode.27#28sqlite3 db test.db29sqlite3 db2 test.db30 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 takes44# 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 not52# read data from the %_content table. But it still attempts to obtain a lock53# 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  } db260} {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 } 065do_execsql_test fts3shared-1.5 { COMMIT } {}66do_test fts3shared-1.6 { sqlite3_get_autocommit db } 167 68# Verify that the second connection still has an open transaction.69#70do_test fts3shared-1.6 { sqlite3_get_autocommit db2 } 071 72db close73db2 close74 75#-------------------------------------------------------------------------76# The following tests - fts3shared-2.* - test that unless FTS is bypassed77# and the underlying tables accessed directly, it is not possible for an78# 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.db85sqlite3 dbR test.db86sqlite3 dbW test.db87do_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  } dbW98} {}99 100# Test that once [dbW] has written to the FTS table, no client may read101# from the FTS or fts4aux table.102do_test 2.2.1 {103  execsql {104    BEGIN;105      INSERT INTO t1 VALUES('j k l');106  } dbW107  execsql BEGIN dbR108} {}109do_test 2.2.2 { catchsql "SELECT * FROM t1 WHERE rowid=1"          dbR } $LOCKED110do_test 2.2.3 { catchsql "SELECT * FROM t1 WHERE t1 MATCH 'a'"     dbR } $LOCKED111do_test 2.2.4 { catchsql "SELECT rowid FROM t1 WHERE t1 MATCH 'a'" dbR } $LOCKED112do_test 2.2.5 { catchsql "SELECT * FROM t1"                        dbR } $LOCKED113do_test 2.2.6 { catchsql "SELECT * FROM t1aux"                     dbR } $LOCKED114do_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  } dbW124  execsql BEGIN dbR125} {}126do_test 2.3.3 { catchsql "SELECT * FROM t2 WHERE t2 MATCH 'a'"     dbR } $LOCKED127do_test 2.3.4 { catchsql "SELECT rowid FROM t2 WHERE t2 MATCH 'a'" dbR } $LOCKED128do_test 2.3.6 { catchsql "SELECT * FROM t2aux"                     dbR } $LOCKED129do_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 dbR145    execsql $::sql dbR146    execsql BEGIN dbW147    catchsql "INSERT INTO t1 VALUES('p q r')" dbW148  } $LOCKED149 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 dbR164    execsql $::sql dbR165    execsql BEGIN dbW166    catchsql "INSERT INTO t2(rowid, a, b) VALUES(3, 's t u', 'v w x')" dbW167  } $LOCKED168 169  execsql ROLLBACK dbR 170  execsql ROLLBACK dbW 171}172 173dbW close174dbR close175sqlite3_enable_shared_cache $::enable_shared_cache176finish_test177