# 2005 December 30

 #

 # Portions Copyright (c) 2007-2008 Nokia Corporation and/or its subsidiaries. All rights reserved.

 #

 # 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.34 2008/07/12 14:52:20 drh Exp $

 set testdir [file dirname $argv0]

 source $testdir/tester.tcl

 db close

 # These tests cannot be run without the ATTACH command.

 #

 ifcapable !shared_cache||!attach {

   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;

   }

 } {}

 #

 # Symbian: "stack overflow" if "shared-$av.7.2" is executed

 #   

 if {$tcl_platform(platform)!="symbian"} {

 	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 its 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}

   catch {db close}

   catch {db2 close}

   file delete -force test.db test2.db

   do_test shared-$av.8.3.2 {

     sqlite3 db test.db

     execsql { CREATE TABLE def(d, e, f) }

     execsql { PRAGMA encoding }

   } {UTF-8}

   do_test shared-$av.8.3.3 {

     set zDb16 "[encoding convertto unicode test.db]\x00\x00"

     set db16 [sqlite3_open16 $zDb16 {}]

     set stmt [sqlite3_prepare $db16 "SELECT sql FROM sqlite_master" -1 DUMMY]

     sqlite3_step $stmt

     set sql [sqlite3_column_text $stmt 0]

     sqlite3_finalize $stmt

     set sql

   } {CREATE TABLE def(d, e, f)}

   do_test shared-$av.8.3.4 {

     set stmt [sqlite3_prepare $db16 "PRAGMA encoding" -1 DUMMY]

     sqlite3_step $stmt

     set enc [sqlite3_column_text $stmt 0]

     sqlite3_finalize $stmt

     set enc

   } {UTF-8}

   sqlite3_close $db16

 # 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]

   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 {

     string tolower [sqlite3_shared_cache_report]

   } [string tolower [list [file nativename [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

   }

 } {}

 # Internally, locks are acquired on shared B-Tree structures in the order

 # that the structures appear in the virtual memory address space. This

 # test case attempts to cause the order of the structures in memory 

 # to be different from the order in which they are attached to a given

 # database handle. This covers an extra line or two.

 #

 do_test shared-$av.13.1 {

   file delete -force test2.db test3.db test4.db test5.db

   sqlite3 db :memory:

   execsql {

     ATTACH 'test2.db' AS aux2;

     ATTACH 'test3.db' AS aux3;

     ATTACH 'test4.db' AS aux4;

     ATTACH 'test5.db' AS aux5;

     DETACH aux2;

     DETACH aux3;

     DETACH aux4;

     ATTACH 'test2.db' AS aux2;

     ATTACH 'test3.db' AS aux3;

     ATTACH 'test4.db' AS aux4;

   }

 } {}

 do_test shared-$av.13.2 {

   execsql {

     CREATE TABLE t1(a, b, c);

     CREATE TABLE aux2.t2(a, b, c);

     CREATE TABLE aux3.t3(a, b, c);

     CREATE TABLE aux4.t4(a, b, c);

     CREATE TABLE aux5.t5(a, b, c);

     SELECT count(*) FROM 

       aux2.sqlite_master, 

       aux3.sqlite_master, 

       aux4.sqlite_master, 

       aux5.sqlite_master

   }

 } {1}

 do_test shared-$av.13.3 {

   db close

 } {}

 # Test that nothing horrible happens if a connection to a shared B-Tree 

 # structure is closed while some other connection has an open cursor.

 #

 do_test shared-$av.14.1 {

   sqlite3 db test.db

   sqlite3 db2 test.db

   execsql {SELECT name FROM sqlite_master}

 } {db1 db2 db3 db4 db5 db6 db7 db8 db9 db10 db11 db12 db13 db14}

 do_test shared-$av.14.2 {

   set res [list]

   db eval {SELECT name FROM sqlite_master} {

     if {$name eq "db7"} {

       db2 close

     }

     lappend res $name

   }

   set res

 } {db1 db2 db3 db4 db5 db6 db7 db8 db9 db10 db11 db12 db13 db14}

 do_test shared-$av.14.3 {

   db close

 } {}

 }

 sqlite3_enable_shared_cache $::enable_shared_cache

 finish_test