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