gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/sharedA.test

# 2013 May 14
#
# 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.
#
#***********************************************************************
#
# Test some specific circumstances to do with shared cache mode.
#


set testdir [file dirname $argv0]
source $testdir/tester.tcl
if {[run_thread_tests]==0} { finish_test ; return }
db close
set ::testprefix sharedA

if {[atomic_batch_write test.db]} {
  finish_test
  return
}

set ::enable_shared_cache [sqlite3_enable_shared_cache 1]

#-------------------------------------------------------------------------
#
do_test 0.1 {
  sqlite3 db1 test.db
  sqlite3 db2 test.db

  db1 eval {
    CREATE TABLE t1(x);
    INSERT INTO t1 VALUES(randomblob(100));
    INSERT INTO t1 SELECT randomblob(100) FROM t1;
    INSERT INTO t1 SELECT randomblob(100) FROM t1;
    INSERT INTO t1 SELECT randomblob(100) FROM t1;
    INSERT INTO t1 SELECT randomblob(100) FROM t1;
    INSERT INTO t1 SELECT randomblob(100) FROM t1;
    INSERT INTO t1 SELECT randomblob(100) FROM t1;
    CREATE INDEX i1 ON t1(x);
  }

  db1 eval {
    BEGIN;
    DROP INDEX i1;
  }

  db2 close

  db1 eval {
    INSERT INTO t1 SELECT randomblob(100) FROM t1;
    ROLLBACK;
    PRAGMA integrity_check;
  }
} {ok}

db1 close
forcedelete test.db


#-------------------------------------------------------------------------
#
do_test 1.1 {
  sqlite3 db1 test.db
  sqlite3 db2 test.db
  db2 eval {
    CREATE TABLE t1(x);
    INSERT INTO t1 VALUES(123);
  }
  db1 eval { 
    SELECT * FROM t1;
    CREATE INDEX i1 ON t1(x);
  }
} {123}

do_test 1.2 {
  db2 eval { SELECT * FROM t1 ORDER BY x; }

  db1 eval {
    BEGIN; DROP INDEX i1;
  }
  db1 close

  db2 eval { SELECT * FROM t1 ORDER BY x; }
} {123}

do_test 1.3 {
  db2 close
} {}

#-------------------------------------------------------------------------
#
# sqlite3RollbackAll() loops through all attached b-trees and rolls
# back each one separately.  Then if the SQLITE_InternChanges flag is
# set, it resets the schema.  Both of the above steps must be done
# while holding a mutex, otherwise another thread might slip in and
# try to use the new schema with the old data.
#
# The following sequence of tests attempt to verify that the actions
# taken by sqlite3RollbackAll() are thread-atomic (that they cannot be
# interrupted by a separate thread.)  
#
# Note that a TCL interpreter can only be used within the thread in which
# it was originally created (because it uses thread-local-storage).  
# The tvfs callbacks must therefore only run on the main thread.  
# There is some trickery in the read_callback procedure to ensure that
# this is the case.
#
testvfs tvfs

# Set up two databases and two database connections.
#
#   db1:  main(test.db), two(test2.db)
#   db2:  main(test.db)
#
# The cache for test.db is shared between db1 and db2.
#
do_test 2.1 {
  forcedelete test.db test.db2
  sqlite3 db1 test.db -vfs tvfs
  db1 eval { ATTACH 'test.db2' AS two }

  db1 eval {
    CREATE TABLE t1(x);
    INSERT INTO t1 VALUES(1);
    INSERT INTO t1 VALUES(2);
    INSERT INTO t1 VALUES(3);
    CREATE TABLE two.t2(x);
    INSERT INTO t2 SELECT * FROM t1;
  }

  sqlite3 db2 test.db -vfs tvfs
  db2 eval { SELECT * FROM t1 }
} {1 2 3}

# Create a prepared statement on db2 that will attempt a schema change
# in test.db.  Meanwhile, start a transaction on db1 that changes
# the schema of test.db and that creates a rollback journal on test2.db
#
do_test 2.2 {
  set ::STMT [sqlite3_prepare db2 "CREATE INDEX i1 ON t1(x)" -1 tail]
  db1 eval {
    BEGIN;
      CREATE INDEX i1 ON t1(x);
      INSERT INTO t2 VALUES('value!');
  }
} {}

# Set up a callback that will cause db2 to try to execute its
# schema change when db1 accesses the journal file of test2.db.
#
# This callback will be invoked after the content of test.db has
# be rolled back but before the schema has been reset.  If the
# sqlite3RollbackAll() operation is not thread-atomic, then the
# db2 statement in the callback will see old content with the newer
# schema, which is wrong.
#
tvfs filter xRead
tvfs script read_callback
unset -nocomplain ::some_time_laster
unset -nocomplain ::thread_result
proc read_callback {call file args} { 
  if {[string match *test.db2-journal $file]} {
    tvfs filter {}   ;# Ensure that tvfs callbacks to do run on the
                      # child thread
    sqlthread spawn ::thread_result [subst -nocommands {
      sqlite3_step $::STMT
      set rc [sqlite3_finalize $::STMT]
    }]
    after 1000 { set ::some_time_later 1 }
    vwait ::some_time_later
  }
}
do_test 2.3 { db1 eval ROLLBACK } {}

# Verify that the db2 statement invoked by the callback detected the
# schema change.
#
if {[info exists ::thread_result]==0} { vwait ::thread_result }
do_test 2.4 { 
  list $::thread_result [sqlite3_errmsg db2] 
} {SQLITE_SCHEMA {database schema has changed}}

db1 close
db2 close
tvfs delete

sqlite3_enable_shared_cache $::enable_shared_cache
finish_test