1 files changed, 911 insertions, 0 deletions

diff --git a/libraries/sqlite/unix/sqlite-3.5.1/test/shared.test b/libraries/sqlite/unix/sqlite-3.5.1/test/shared.test
new file mode 100644
index 0000000..a69774f
--- /dev/null
+++ b/libraries/sqlite/unix/sqlite-3.5.1/test/shared.test
@@ -0,0 +1,911 @@
+# 2005 December 30
+#
+# 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.
+#
+#***********************************************************************
+#
+# $Id: shared.test,v 1.27 2007/09/12 17:01:45 danielk1977 Exp $
+
+set testdir [file dirname $argv0]
+source $testdir/tester.tcl
+db close
+
+ifcapable !shared_cache {
+  finish_test
+  return
+}
+
+set ::enable_shared_cache [sqlite3_enable_shared_cache 1]
+
+foreach av [list 0 1] {
+
+# Open the database connection and execute the auto-vacuum pragma
+file delete -force test.db
+sqlite3 db test.db
+
+ifcapable autovacuum {
+  do_test shared-[expr $av+1].1.0 {
+    execsql "pragma auto_vacuum=$::av"
+    execsql {pragma auto_vacuum}
+  } "$av"
+} else {
+  if {$av} {
+    db close
+    break
+  }
+}
+
+# $av is currently 0 if this loop iteration is to test with auto-vacuum turned
+# off, and 1 if it is turned on. Increment it so that (1 -> no auto-vacuum) 
+# and (2 -> auto-vacuum). The sole reason for this is so that it looks nicer
+# when we use this variable as part of test-case names.
+#
+incr av
+
+# Test organization:
+#
+# shared-1.*: Simple test to verify basic sanity of table level locking when
+#             two connections share a pager cache.
+# shared-2.*: Test that a read transaction can co-exist with a 
+#             write-transaction, including a simple test to ensure the 
+#             external locking protocol is still working.
+# shared-3.*: Simple test of read-uncommitted mode.
+# shared-4.*: Check that the schema is locked and unlocked correctly.
+# shared-5.*: Test that creating/dropping schema items works when databases
+#             are attached in different orders to different handles.
+# shared-6.*: Locking, UNION ALL queries and sub-queries.
+# shared-7.*: Autovacuum and shared-cache.
+# shared-8.*: Tests related to the text encoding of shared-cache databases.
+# shared-9.*: TEMP triggers and shared-cache databases.
+# shared-10.*: Tests of sqlite3_close().
+# shared-11.*: Test transaction locking.
+#
+
+do_test shared-$av.1.1 {
+  # Open a second database on the file test.db. It should use the same pager
+  # cache and schema as the original connection. Verify that only 1 file is 
+  # opened.
+  sqlite3 db2 test.db
+  set ::sqlite_open_file_count
+} {1}
+do_test shared-$av.1.2 {
+  # Add a table and a single row of data via the first connection. 
+  # Ensure that the second connection can see them.
+  execsql {
+    CREATE TABLE abc(a, b, c);
+    INSERT INTO abc VALUES(1, 2, 3);
+  } db
+  execsql {
+    SELECT * FROM abc;
+  } db2
+} {1 2 3}
+do_test shared-$av.1.3 {
+  # Have the first connection begin a transaction and obtain a read-lock
+  # on table abc. This should not prevent the second connection from 
+  # querying abc.
+  execsql {
+    BEGIN;
+    SELECT * FROM abc;
+  }
+  execsql {
+    SELECT * FROM abc;
+  } db2
+} {1 2 3}
+do_test shared-$av.1.4 {
+  # Try to insert a row into abc via connection 2. This should fail because
+  # of the read-lock connection 1 is holding on table abc (obtained in the
+  # previous test case).
+  catchsql {
+    INSERT INTO abc VALUES(4, 5, 6);
+  } db2
+} {1 {database table is locked: abc}}
+do_test shared-$av.1.5 {
+  # Using connection 2 (the one without the open transaction), try to create
+  # a new table. This should fail because of the open read transaction 
+  # held by connection 1.
+  catchsql {
+    CREATE TABLE def(d, e, f);
+  } db2
+} {1 {database table is locked: sqlite_master}}
+do_test shared-$av.1.6 {
+  # Upgrade connection 1's transaction to a write transaction. Create
+  # a new table - def - and insert a row into it. Because the connection 1
+  # transaction modifies the schema, it should not be possible for 
+  # connection 2 to access the database at all until the connection 1 
+  # has finished the transaction.
+  execsql {
+    CREATE TABLE def(d, e, f);
+    INSERT INTO def VALUES('IV', 'V', 'VI');
+  }
+} {}
+do_test shared-$av.1.7 {
+  # Read from the sqlite_master table with connection 1 (inside the 
+  # transaction). Then test that we can not do this with connection 2. This
+  # is because of the schema-modified lock established by connection 1 
+  # in the previous test case.
+  execsql {
+    SELECT * FROM sqlite_master;
+  }
+  catchsql {
+    SELECT * FROM sqlite_master;
+  } db2
+} {1 {database schema is locked: main}}
+do_test shared-$av.1.8 {
+  # Commit the connection 1 transaction.
+  execsql {
+    COMMIT;
+  }
+} {}
+
+do_test shared-$av.2.1 {
+  # Open connection db3 to the database. Use a different path to the same
+  # file so that db3 does *not* share the same pager cache as db and db2
+  # (there should be two open file handles).
+  if {$::tcl_platform(platform)=="unix"} {
+    sqlite3 db3 ./test.db
+  } else {
+    sqlite3 db3 TEST.DB
+  }
+  set ::sqlite_open_file_count
+} {2}
+do_test shared-$av.2.2 {
+  # Start read transactions on db and db2 (the shared pager cache). Ensure
+  # db3 cannot write to the database.
+  execsql {
+    BEGIN;
+    SELECT * FROM abc;
+  }
+  execsql {
+    BEGIN;
+    SELECT * FROM abc;
+  } db2
+  catchsql {
+    INSERT INTO abc VALUES(1, 2, 3);
+  } db2
+} {1 {database table is locked: abc}}
+do_test shared-$av.2.3 {
+  # Turn db's transaction into a write-transaction. db3 should still be
+  # able to read from table def (but will not see the new row). Connection
+  # db2 should not be able to read def (because of the write-lock).
+
+# Todo: The failed "INSERT INTO abc ..." statement in the above test
+# has started a write-transaction on db2 (should this be so?). This 
+# would prevent connection db from starting a write-transaction. So roll the
+# db2 transaction back and replace it with a new read transaction.
+  execsql {
+    ROLLBACK;
+    BEGIN;
+    SELECT * FROM abc;
+  } db2
+
+  execsql {
+    INSERT INTO def VALUES('VII', 'VIII', 'IX');
+  }
+  concat [
+    catchsql { SELECT * FROM def; } db3
+  ] [
+    catchsql { SELECT * FROM def; } db2
+  ]
+} {0 {IV V VI} 1 {database table is locked: def}}
+do_test shared-$av.2.4 {
+  # Commit the open transaction on db. db2 still holds a read-transaction.
+  # This should prevent db3 from writing to the database, but not from 
+  # reading.
+  execsql {
+    COMMIT;
+  }
+  concat [
+    catchsql { SELECT * FROM def; } db3
+  ] [
+    catchsql { INSERT INTO def VALUES('X', 'XI', 'XII'); } db3
+  ]
+} {0 {IV V VI VII VIII IX} 1 {database is locked}}
+
+catchsql COMMIT db2
+
+do_test shared-$av.3.1.1 {
+  # This test case starts a linear scan of table 'seq' using a 
+  # read-uncommitted connection. In the middle of the scan, rows are added
+  # to the end of the seq table (ahead of the current cursor position).
+  # The uncommitted rows should be included in the results of the scan.
+  execsql "
+    CREATE TABLE seq(i PRIMARY KEY, x);
+    INSERT INTO seq VALUES(1, '[string repeat X 500]');
+    INSERT INTO seq VALUES(2, '[string repeat X 500]');
+  "
+  execsql {SELECT * FROM sqlite_master} db2
+  execsql {PRAGMA read_uncommitted = 1} db2
+
+  set ret [list]
+  db2 eval {SELECT i FROM seq ORDER BY i} {
+    if {$i < 4} {
+      set max [execsql {SELECT max(i) FROM seq}]
+      db eval {
+        INSERT INTO seq SELECT i + :max, x FROM seq;
+      }
+    }
+    lappend ret $i
+  }
+  set ret
+} {1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16}
+do_test shared-$av.3.1.2 {
+  # Another linear scan through table seq using a read-uncommitted connection.
+  # This time, delete each row as it is read. Should not affect the results of
+  # the scan, but the table should be empty after the scan is concluded 
+  # (test 3.1.3 verifies this).
+  set ret [list]
+  db2 eval {SELECT i FROM seq} {
+    db eval {DELETE FROM seq WHERE i = :i}
+    lappend ret $i
+  }
+  set ret
+} {1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16}
+do_test shared-$av.3.1.3 {
+  execsql {
+    SELECT * FROM seq;
+  }
+} {}
+
+catch {db close}
+catch {db2 close}
+catch {db3 close}
+
+#--------------------------------------------------------------------------
+# Tests shared-4.* test that the schema locking rules are applied 
+# correctly. i.e.:
+#
+# 1. All transactions require a read-lock on the schemas of databases they
+#    access.
+# 2. Transactions that modify a database schema require a write-lock on that
+#    schema.
+# 3. It is not possible to compile a statement while another handle has a 
+#    write-lock on the schema.
+#
+
+# Open two database handles db and db2. Each has a single attach database
+# (as well as main):
+#
+#     db.main   ->   ./test.db
+#     db.test2  ->   ./test2.db
+#     db2.main  ->   ./test2.db
+#     db2.test  ->   ./test.db
+#
+file delete -force test.db
+file delete -force test2.db
+file delete -force test2.db-journal
+sqlite3 db  test.db
+sqlite3 db2 test2.db
+do_test shared-$av.4.1.1 {
+  set sqlite_open_file_count
+} {2}
+do_test shared-$av.4.1.2 {
+  execsql {ATTACH 'test2.db' AS test2}
+  set sqlite_open_file_count
+} {2}
+do_test shared-$av.4.1.3 {
+  execsql {ATTACH 'test.db' AS test} db2
+  set sqlite_open_file_count
+} {2}
+
+# Sanity check: Create a table in ./test.db via handle db, and test that handle
+# db2 can "see" the new table immediately. A handle using a seperate pager
+# cache would have to reload the database schema before this were possible.
+#
+do_test shared-$av.4.2.1 {
+  execsql {
+    CREATE TABLE abc(a, b, c);
+    CREATE TABLE def(d, e, f);
+    INSERT INTO abc VALUES('i', 'ii', 'iii');
+    INSERT INTO def VALUES('I', 'II', 'III');
+  }
+} {}
+do_test shared-$av.4.2.2 {
+  execsql {
+    SELECT * FROM test.abc;
+  } db2
+} {i ii iii}
+
+# Open a read-transaction and read from table abc via handle 2. Check that
+# handle 1 can read table abc. Check that handle 1 cannot modify table abc
+# or the database schema. Then check that handle 1 can modify table def.
+#
+do_test shared-$av.4.3.1 {
+  execsql {
+    BEGIN;
+    SELECT * FROM test.abc;
+  } db2
+} {i ii iii}
+do_test shared-$av.4.3.2 {
+  catchsql {
+    INSERT INTO abc VALUES('iv', 'v', 'vi');
+  }
+} {1 {database table is locked: abc}}
+do_test shared-$av.4.3.3 {
+  catchsql {
+    CREATE TABLE ghi(g, h, i);
+  }
+} {1 {database table is locked: sqlite_master}}
+do_test shared-$av.4.3.3 {
+  catchsql {
+    INSERT INTO def VALUES('IV', 'V', 'VI');
+  }
+} {0 {}}
+do_test shared-$av.4.3.4 {
+  # Cleanup: commit the transaction opened by db2.
+  execsql {
+    COMMIT
+  } db2
+} {}
+
+# Open a write-transaction using handle 1 and modify the database schema.
+# Then try to execute a compiled statement to read from the same 
+# database via handle 2 (fails to get the lock on sqlite_master). Also
+# try to compile a read of the same database using handle 2 (also fails).
+# Finally, compile a read of the other database using handle 2. This
+# should also fail.
+#
+ifcapable compound {
+  do_test shared-$av.4.4.1.2 {
+    # Sanity check 1: Check that the schema is what we think it is when viewed
+    # via handle 1.
+    execsql {
+      CREATE TABLE test2.ghi(g, h, i);
+      SELECT 'test.db:'||name FROM sqlite_master 
+      UNION ALL
+      SELECT 'test2.db:'||name FROM test2.sqlite_master;
+    }
+  } {test.db:abc test.db:def test2.db:ghi}
+  do_test shared-$av.4.4.1.2 {
+    # Sanity check 2: Check that the schema is what we think it is when viewed
+    # via handle 2.
+    execsql {
+      SELECT 'test2.db:'||name FROM sqlite_master 
+      UNION ALL
+      SELECT 'test.db:'||name FROM test.sqlite_master;
+    } db2
+  } {test2.db:ghi test.db:abc test.db:def}
+}
+
+do_test shared-$av.4.4.2 {
+  set ::DB2 [sqlite3_connection_pointer db2]
+  set sql {SELECT * FROM abc}
+  set ::STMT1 [sqlite3_prepare $::DB2 $sql -1 DUMMY]
+  execsql {
+    BEGIN;
+    CREATE TABLE jkl(j, k, l);
+  }
+  sqlite3_step $::STMT1
+} {SQLITE_ERROR}
+do_test shared-$av.4.4.3 {
+  sqlite3_finalize $::STMT1
+} {SQLITE_LOCKED}
+do_test shared-$av.4.4.4 {
+  set rc [catch {
+    set ::STMT1 [sqlite3_prepare $::DB2 $sql -1 DUMMY]
+  } msg]
+  list $rc $msg
+} {1 {(6) database schema is locked: test}}
+do_test shared-$av.4.4.5 {
+  set rc [catch {
+    set ::STMT1 [sqlite3_prepare $::DB2 "SELECT * FROM ghi" -1 DUMMY]
+  } msg]
+  list $rc $msg
+} {1 {(6) database schema is locked: test}}
+
+
+catch {db2 close}
+catch {db close}
+
+#--------------------------------------------------------------------------
+# Tests shared-5.* 
+#
+foreach db [list test.db test1.db test2.db test3.db] {
+  file delete -force $db ${db}-journal
+}
+do_test shared-$av.5.1.1 {
+  sqlite3 db1 test.db
+  sqlite3 db2 test.db
+  execsql {
+    ATTACH 'test1.db' AS test1;
+    ATTACH 'test2.db' AS test2;
+    ATTACH 'test3.db' AS test3;
+  } db1
+  execsql {
+    ATTACH 'test3.db' AS test3;
+    ATTACH 'test2.db' AS test2;
+    ATTACH 'test1.db' AS test1;
+  } db2
+} {}
+do_test shared-$av.5.1.2 {
+  execsql {
+    CREATE TABLE test1.t1(a, b);
+    CREATE INDEX test1.i1 ON t1(a, b);
+  } db1
+} {}
+ifcapable view {
+  do_test shared-$av.5.1.3 {
+    execsql {
+      CREATE VIEW test1.v1 AS SELECT * FROM t1;
+    } db1
+  } {}
+}
+ifcapable trigger {
+  do_test shared-$av.5.1.4 {
+    execsql {
+      CREATE TRIGGER test1.trig1 AFTER INSERT ON t1 BEGIN
+        INSERT INTO t1 VALUES(new.a, new.b);
+      END;
+    } db1
+  } {}
+}
+do_test shared-$av.5.1.5 {
+  execsql {
+    DROP INDEX i1;
+  } db2
+} {}
+ifcapable view {
+  do_test shared-$av.5.1.6 {
+    execsql {
+      DROP VIEW v1;
+    } db2
+  } {}
+}
+ifcapable trigger {
+  do_test shared-$av.5.1.7 {
+    execsql {
+      DROP TRIGGER trig1;
+    } db2
+  } {}
+}
+do_test shared-$av.5.1.8 {
+  execsql {
+    DROP TABLE t1;
+  } db2
+} {}
+ifcapable compound {
+  do_test shared-$av.5.1.9 {
+    execsql {
+      SELECT * FROM sqlite_master UNION ALL SELECT * FROM test1.sqlite_master
+    } db1
+  } {}
+}
+
+#--------------------------------------------------------------------------
+# Tests shared-6.* test that a query obtains all the read-locks it needs
+# before starting execution of the query. This means that there is no chance
+# some rows of data will be returned before a lock fails and SQLITE_LOCK
+# is returned.
+#
+do_test shared-$av.6.1.1 {
+  execsql {
+    CREATE TABLE t1(a, b);
+    CREATE TABLE t2(a, b);
+    INSERT INTO t1 VALUES(1, 2);
+    INSERT INTO t2 VALUES(3, 4);
+  } db1
+} {}
+ifcapable compound {
+  do_test shared-$av.6.1.2 {
+    execsql {
+      SELECT * FROM t1 UNION ALL SELECT * FROM t2;
+    } db2
+  } {1 2 3 4}
+}
+do_test shared-$av.6.1.3 {
+  # Establish a write lock on table t2 via connection db2. Then make a 
+  # UNION all query using connection db1 that first accesses t1, followed 
+  # by t2. If the locks are grabbed at the start of the statement (as 
+  # they should be), no rows are returned. If (as was previously the case)
+  # they are grabbed as the tables are accessed, the t1 rows will be 
+  # returned before the query fails.
+  #
+  execsql {
+    BEGIN;
+    INSERT INTO t2 VALUES(5, 6);
+  } db2
+  set ret [list]
+  catch {
+    db1 eval {SELECT * FROM t1 UNION ALL SELECT * FROM t2} {
+      lappend ret $a $b
+    }
+  }
+  set ret
+} {}
+do_test shared-$av.6.1.4 {
+  execsql {
+    COMMIT;
+    BEGIN;
+    INSERT INTO t1 VALUES(7, 8);
+  } db2
+  set ret [list]
+  catch {
+    db1 eval {
+      SELECT (CASE WHEN a>4 THEN (SELECT a FROM t1) ELSE 0 END) AS d FROM t2;
+    } {
+      lappend ret $d
+    }
+  }
+  set ret
+} {}
+
+catch {db1 close}
+catch {db2 close}
+foreach f [list test.db test2.db] {
+  file delete -force $f ${f}-journal
+}
+
+#--------------------------------------------------------------------------
+# Tests shared-7.* test auto-vacuum does not invalidate cursors from
+# other shared-cache users when it reorganizes the database on 
+# COMMIT.
+#
+do_test shared-$av.7.1 {
+  # This test case sets up a test database in auto-vacuum mode consisting 
+  # of two tables, t1 and t2. Both have a single index. Table t1 is 
+  # populated first (so consists of pages toward the start of the db file), 
+  # t2 second (pages toward the end of the file). 
+  sqlite3 db test.db
+  sqlite3 db2 test.db
+  execsql {
+    BEGIN;
+    CREATE TABLE t1(a PRIMARY KEY, b);
+    CREATE TABLE t2(a PRIMARY KEY, b);
+  }
+  set ::contents {}
+  for {set i 0} {$i < 100} {incr i} {
+    set a [string repeat "$i " 20]
+    set b [string repeat "$i " 20]
+    db eval {
+      INSERT INTO t1 VALUES(:a, :b);
+    }
+    lappend ::contents [list [expr $i+1] $a $b]
+  }
+  execsql {
+    INSERT INTO t2 SELECT * FROM t1;
+    COMMIT;
+  }
+} {}
+do_test shared-$av.7.2 {
+  # This test case deletes the contents of table t1 (the one at the start of
+  # the file) while many cursors are open on table t2 and it's index. All of
+  # the non-root pages will be moved from the end to the start of the file
+  # when the DELETE is committed - this test verifies that moving the pages
+  # does not disturb the open cursors.
+  #
+
+  proc lockrow {db tbl oids body} {
+    set ret [list]
+    db eval "SELECT oid AS i, a, b FROM $tbl ORDER BY a" {
+      if {$i==[lindex $oids 0]} {
+        set noids [lrange $oids 1 end]
+        if {[llength $noids]==0} {
+          set subret [eval $body]
+        } else {
+          set subret [lockrow $db $tbl $noids $body]
+        }
+      }
+      lappend ret [list $i $a $b]
+    }
+    return [linsert $subret 0 $ret]
+  }
+  proc locktblrows {db tbl body} {
+    set oids [db eval "SELECT oid FROM $tbl"]
+    lockrow $db $tbl $oids $body
+  }
+
+  set scans [locktblrows db t2 {
+    execsql {
+      DELETE FROM t1;
+    } db2
+  }]
+  set error 0
+
+  # Test that each SELECT query returned the expected contents of t2.
+  foreach s $scans {
+    if {[lsort -integer -index 0 $s]!=$::contents} {
+      set error 1
+    }
+  }
+  set error
+} {0}
+
+catch {db close}
+catch {db2 close}
+unset -nocomplain contents
+
+#--------------------------------------------------------------------------
+# The following tests try to trick the shared-cache code into assuming
+# the wrong encoding for a database.
+#
+file delete -force test.db test.db-journal
+ifcapable utf16 {
+  do_test shared-$av.8.1.1 {
+    sqlite3 db test.db
+    execsql {
+      PRAGMA encoding = 'UTF-16';
+      SELECT * FROM sqlite_master;
+    }
+  } {}
+  do_test shared-$av.8.1.2 {
+    string range [execsql {PRAGMA encoding;}] 0 end-2
+  } {UTF-16}
+  do_test shared-$av.8.1.3 {
+    sqlite3 db2 test.db
+    execsql {
+      PRAGMA encoding = 'UTF-8';
+      CREATE TABLE abc(a, b, c);
+    } db2
+  } {}
+  do_test shared-$av.8.1.4 {
+    execsql {
+      SELECT * FROM sqlite_master;
+    }
+  } "table abc abc [expr $AUTOVACUUM?3:2] {CREATE TABLE abc(a, b, c)}"
+  do_test shared-$av.8.1.5 {
+    db2 close
+    execsql {
+      PRAGMA encoding;
+    }
+  } {UTF-8}
+  file delete -force test2.db test2.db-journal
+  do_test shared-$av.8.2.1 {
+    execsql {
+      ATTACH 'test2.db' AS aux;
+      SELECT * FROM aux.sqlite_master;
+    }
+  } {}
+  do_test shared-$av.8.2.2 {
+    sqlite3 db2 test2.db
+    execsql {
+      PRAGMA encoding = 'UTF-16';
+      CREATE TABLE def(d, e, f);
+    } db2
+    string range [execsql {PRAGMA encoding;} db2] 0 end-2
+  } {UTF-16}
+
+# Bug #2547 is causing this to fail.
+if 0 {
+  do_test shared-$av.8.2.3 {
+    catchsql {
+      SELECT * FROM aux.sqlite_master;
+    }
+  } {1 {attached databases must use the same text encoding as main database}}
+}
+}
+
+catch {db close}
+catch {db2 close}
+file delete -force test.db test2.db
+
+#---------------------------------------------------------------------------
+# The following tests - shared-9.* - test interactions between TEMP triggers
+# and shared-schemas.
+#
+ifcapable trigger&&tempdb {
+
+do_test shared-$av.9.1 {
+  sqlite3 db test.db
+  sqlite3 db2 test.db
+  execsql {
+    CREATE TABLE abc(a, b, c);
+    CREATE TABLE abc_mirror(a, b, c);
+    CREATE TEMP TRIGGER BEFORE INSERT ON abc BEGIN 
+      INSERT INTO abc_mirror(a, b, c) VALUES(new.a, new.b, new.c);
+    END;
+    INSERT INTO abc VALUES(1, 2, 3);
+    SELECT * FROM abc_mirror;
+  }
+} {1 2 3}
+do_test shared-$av.9.2 {
+  execsql {
+    INSERT INTO abc VALUES(4, 5, 6);
+    SELECT * FROM abc_mirror;
+  } db2
+} {1 2 3}
+do_test shared-$av.9.3 {
+  db close
+  db2 close
+} {}
+
+} ; # End shared-9.*
+
+#---------------------------------------------------------------------------
+# The following tests - shared-10.* - test that the library behaves 
+# correctly when a connection to a shared-cache is closed. 
+#
+do_test shared-$av.10.1 {
+  # Create a small sample database with two connections to it (db and db2).
+  file delete -force test.db
+  sqlite3 db  test.db
+  sqlite3 db2 test.db
+  execsql {
+    CREATE TABLE ab(a PRIMARY KEY, b);
+    CREATE TABLE de(d PRIMARY KEY, e);
+    INSERT INTO ab VALUES('Chiang Mai', 100000);
+    INSERT INTO ab VALUES('Bangkok', 8000000);
+    INSERT INTO de VALUES('Ubon', 120000);
+    INSERT INTO de VALUES('Khon Kaen', 200000);
+  }
+} {}
+do_test shared-$av.10.2 {
+  # Open a read-transaction with the first connection, a write-transaction
+  # with the second.
+  execsql {
+    BEGIN;
+    SELECT * FROM ab;
+  }
+  execsql {
+    BEGIN;
+    INSERT INTO de VALUES('Pataya', 30000);
+  } db2
+} {}
+do_test shared-$av.10.3 {
+  # An external connection should be able to read the database, but not
+  # prepare a write operation.
+  if {$::tcl_platform(platform)=="unix"} {
+    sqlite3 db3 ./test.db
+  } else {
+    sqlite3 db3 TEST.DB
+  }
+  execsql {
+    SELECT * FROM ab;
+  } db3
+  catchsql {
+    BEGIN;
+    INSERT INTO de VALUES('Pataya', 30000);
+  } db3
+} {1 {database is locked}}
+do_test shared-$av.10.4 {
+  # Close the connection with the write-transaction open
+  db2 close
+} {}
+do_test shared-$av.10.5 {
+  # Test that the db2 transaction has been automatically rolled back.
+  # If it has not the ('Pataya', 30000) entry will still be in the table.
+  execsql {
+    SELECT * FROM de;
+  }
+} {Ubon 120000 {Khon Kaen} 200000}
+do_test shared-$av.10.5 {
+  # Closing db2 should have dropped the shared-cache back to a read-lock.
+  # So db3 should be able to prepare a write...
+  catchsql {INSERT INTO de VALUES('Pataya', 30000);} db3
+} {0 {}}
+do_test shared-$av.10.6 {
+  # ... but not commit it.
+  catchsql {COMMIT} db3
+} {1 {database is locked}}
+do_test shared-$av.10.7 {
+  # Commit the (read-only) db transaction. Check via db3 to make sure the 
+  # contents of table "de" are still as they should be.
+  execsql {
+    COMMIT;
+  }
+  execsql {
+    SELECT * FROM de;
+  } db3
+} {Ubon 120000 {Khon Kaen} 200000 Pataya 30000}
+do_test shared-$av.10.9 {
+  # Commit the external transaction.
+  catchsql {COMMIT} db3
+} {0 {}}
+integrity_check shared-$av.10.10
+do_test shared-$av.10.11 {
+  db close
+  db3 close
+} {}
+
+do_test shared-$av.11.1 {
+  file delete -force test.db
+  sqlite3 db  test.db
+  sqlite3 db2 test.db
+  execsql {
+    CREATE TABLE abc(a, b, c);
+    CREATE TABLE abc2(a, b, c);
+    BEGIN;
+    INSERT INTO abc VALUES(1, 2, 3);
+  }
+} {}
+do_test shared-$av.11.2 {
+  catchsql {BEGIN;} db2
+  catchsql {SELECT * FROM abc;} db2
+} {1 {database table is locked: abc}}
+do_test shared-$av.11.3 {
+  catchsql {BEGIN} db2
+} {1 {cannot start a transaction within a transaction}}
+do_test shared-$av.11.4 {
+  catchsql {SELECT * FROM abc2;} db2
+} {0 {}}
+do_test shared-$av.11.5 {
+  catchsql {INSERT INTO abc2 VALUES(1, 2, 3);} db2
+} {1 {database is locked}}
+do_test shared-$av.11.6 {
+  catchsql {SELECT * FROM abc2}
+} {0 {}}
+do_test shared-$av.11.6 {
+  execsql {
+    ROLLBACK;
+    PRAGMA read_uncommitted = 1;
+  } db2
+} {}
+do_test shared-$av.11.7 {
+  execsql {
+    INSERT INTO abc2 VALUES(4, 5, 6);
+    INSERT INTO abc2 VALUES(7, 8, 9);
+  }
+} {}
+do_test shared-$av.11.8 {
+  set res [list]
+  breakpoint
+  db2 eval {
+    SELECT abc.a as I, abc2.a as II FROM abc, abc2;
+  } {
+    execsql {
+      DELETE FROM abc WHERE 1;
+    }
+    lappend res $I $II
+  }
+  set res
+} {1 4 {} 7}
+if {[llength [info command sqlite3_shared_cache_report]]==1} {
+  do_test shared-$av.11.9 {
+    sqlite3_shared_cache_report 
+  } [list [file normalize test.db] 2]
+}
+
+do_test shared-$av.11.11 {
+  db close
+  db2 close
+} {}
+
+# This tests that if it is impossible to free any pages, SQLite will
+# exceed the limit set by PRAGMA cache_size.
+file delete -force test.db test.db-journal
+sqlite3 db test.db 
+ifcapable pager_pragmas {
+  do_test shared-$av.12.1 {
+    execsql {
+      PRAGMA cache_size = 10;
+      PRAGMA cache_size;
+    }
+  } {10}
+}
+do_test shared-$av.12.2 {
+  set ::db_handles [list]
+  for {set i 1} {$i < 15} {incr i} {
+    lappend ::db_handles db$i
+    sqlite3 db$i test.db 
+    execsql "CREATE TABLE db${i}(a, b, c)" db$i 
+    execsql "INSERT INTO db${i} VALUES(1, 2, 3)"
+  }
+} {}
+proc nested_select {handles} {
+  [lindex $handles 0] eval "SELECT * FROM [lindex $handles 0]" {
+    lappend ::res $a $b $c
+    if {[llength $handles]>1} {
+      nested_select [lrange $handles 1 end]
+    }
+  }
+}
+do_test shared-$av.12.3 {
+  set ::res [list]
+  nested_select $::db_handles
+  set ::res
+} [string range [string repeat "1 2 3 " [llength $::db_handles]] 0 end-1]
+
+do_test shared-$av.12.X {
+  db close
+  foreach h $::db_handles { 
+    $h close
+  }
+} {}
+
+}
+
+sqlite3_enable_shared_cache $::enable_shared_cache
+finish_test