** The pager is used to access a database disk file.  It implements
** atomic commit and rollback through the use of a journal file that
** is separate from the database file.  The pager also implements file
** locking to prevent two processes from writing the same database
** file simultaneously, or one process from reading the database while
** another is writing.
**
** @(#) $Id: pager.c,v 1.238 2006/01/16 11:29:19 danielk1977 Exp $
*/
#ifndef SQLITE_OMIT_DISKIO
#include "sqliteInt.h"
#include "os.h"
#include "pager.h"
#include <assert.h>
#include <string.h>
................................................................................
  ac = &((unsigned char*)PGHDR_TO_DATA(p))[offset];
  return (ac[0]<<24) | (ac[1]<<16) | (ac[2]<<8) | ac[3];
}


/*
** This function should be called when an error occurs within the pager
** code to set Pager.errCode.






*/
static int pager_error(Pager *pPager, int rc){
  assert( pPager->errCode==SQLITE_FULL || pPager->errCode==SQLITE_OK );
  if( 
    rc==SQLITE_FULL || 
    rc==SQLITE_IOERR || 
    rc==SQLITE_CORRUPT ||

** The pager is used to access a database disk file.  It implements
** atomic commit and rollback through the use of a journal file that
** is separate from the database file.  The pager also implements file
** locking to prevent two processes from writing the same database
** file simultaneously, or one process from reading the database while
** another is writing.
**
** @(#) $Id: pager.c,v 1.239 2006/01/16 12:46:41 danielk1977 Exp $
*/
#ifndef SQLITE_OMIT_DISKIO
#include "sqliteInt.h"
#include "os.h"
#include "pager.h"
#include <assert.h>
#include <string.h>
................................................................................
  ac = &((unsigned char*)PGHDR_TO_DATA(p))[offset];
  return (ac[0]<<24) | (ac[1]<<16) | (ac[2]<<8) | ac[3];
}


/*
** This function should be called when an error occurs within the pager
** code. The first argument is a pointer to the pager structure, the
** second the error-code about to be returned by a pager API function. 
** The value returned is a copy of the second argument to this function. 
**
** If the second argument is SQLITE_IOERR, SQLITE_CORRUPT or SQLITE_PROTOCOL,
** the error becomes persistent. All subsequent API calls on this Pager
** will immediately return the same error code.
*/
static int pager_error(Pager *pPager, int rc){
  assert( pPager->errCode==SQLITE_FULL || pPager->errCode==SQLITE_OK );
  if( 
    rc==SQLITE_FULL || 
    rc==SQLITE_IOERR || 
    rc==SQLITE_CORRUPT ||

#
#    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.
#
#***********************************************************************
#
# This file runs the tests in the file crash.test with auto-vacuum enabled
# databases.
#
# $Id: autovacuum_ioerr.test,v 1.2 2005/01/16 09:06:34 danielk1977 Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl

# If this build of the library does not support auto-vacuum, omit this
# whole file.
ifcapable {!autovacuum} {

#
#    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.
#
#***********************************************************************
#
# This file runs the tests in the file ioerr.test with auto-vacuum enabled
# databases.
#
# $Id: autovacuum_ioerr.test,v 1.3 2006/01/16 12:46:41 danielk1977 Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl

# If this build of the library does not support auto-vacuum, omit this
# whole file.
ifcapable {!autovacuum} {

#
#    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.13 2006/01/15 11:39:18 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]
















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

do_test shared-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
  sqlite3 db  test.db
  set ::sqlite_open_file_count
} {1}
do_test shared-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-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-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-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-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-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-1.8 {
  # Commit the connection 1 transaction.
  execsql {
    COMMIT;
  }
} {}

do_test shared-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).
  sqlite3 db3 ./test.db
  set ::sqlite_open_file_count
} {2}
do_test shared-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-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
................................................................................
  }
  concat [
    catchsql { SELECT * FROM def; } db3
  ] [
    catchsql { SELECT * FROM def; } db2
  ]
} {0 {IV V VI} 1 {database table is locked: def}}
do_test shared-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 { 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-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, x);
    INSERT INTO seq VALUES(1, '[string repeat X 500]');
................................................................................
        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-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-3.1.3 {
  execsql {
    SELECT * FROM seq;
  }
} {}

catch {db close}
catch {db2 close}
................................................................................
#     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-4.1.1 {
  set sqlite_open_file_count
} {2}
do_test shared-4.1.2 {
  execsql {ATTACH 'test2.db' AS test2}
  set sqlite_open_file_count
} {2}
do_test shared-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-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-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-4.3.1 {
  execsql {
    BEGIN;
    SELECT * FROM test.abc;
  } db2
} {i ii iii}
do_test shared-4.3.2 {
  catchsql {
    INSERT INTO abc VALUES('iv', 'v', 'vi');
  }
} {1 {database table is locked: abc}}
do_test shared-4.3.3 {
  catchsql {
    CREATE TABLE ghi(g, h, i);
  }
} {1 {database table is locked: sqlite_master}}
do_test shared-4.3.3 {
  catchsql {
    INSERT INTO def VALUES('IV', 'V', 'VI');
  }
} {0 {}}
do_test shared-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.
#
do_test shared-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-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-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-4.4.3 {
  sqlite3_finalize $::STMT1
} {SQLITE_LOCKED}
do_test shared-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-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}}


................................................................................

#--------------------------------------------------------------------------
# Tests shared-5.* 
#
foreach db [list test.db test1.db test2.db test3.db] {
  file delete -force $db ${db}-journal
}
do_test shared-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-5.1.2 {
  execsql {
    CREATE TABLE test1.t1(a, b);
    CREATE INDEX test1.i1 ON t1(a, b);
    CREATE VIEW test1.v1 AS SELECT * FROM t1;
    CREATE TRIGGER test1.trig1 AFTER INSERT ON t1 BEGIN
      INSERT INTO t1 VALUES(new.a, new.b);
    END;
................................................................................
  execsql {
    DROP INDEX i1;
    DROP VIEW v1;
    DROP TRIGGER trig1;
    DROP TABLE t1;
  } db2
} {}
do_test shared-5.1.2 {
  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-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
  execsql {
    SELECT * FROM t1 UNION ALL SELECT * FROM t2;
  } db2
} {1 2 3 4}
do_test shared-6.1.2 {
  # 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.
  #
................................................................................
  catch {
    db1 eval {SELECT * FROM t1 UNION ALL SELECT * FROM t2} {
      lappend ret $a $b
    }
  }
  set ret
} {}
do_test shared-6.1.3 {
  execsql {
    COMMIT;
    BEGIN;
    INSERT INTO t1 VALUES(7, 8);
  } db2
  set ret [list]
  catch {
................................................................................
}

#--------------------------------------------------------------------------
# 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-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 {
................................................................................
    INSERT INTO t2 SELECT * FROM t1;
    COMMIT;
  }
  execsql {
    PRAGMA auto_vacuum;
  }
} {1}
do_test shared-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.
  #

................................................................................
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
do_test shared-8.1.1 {
  sqlite3 db test.db
  execsql {
    PRAGMA encoding = 'UTF-16';
    SELECT * FROM sqlite_master;
  }
} {}
do_test shared-8.1.2 {
  string range [execsql {PRAGMA encoding;}] 0 end-2
} {UTF-16}
do_test shared-8.1.3 {
  sqlite3 db2 test.db
  execsql {
    PRAGMA encoding = 'UTF-8';
    CREATE TABLE abc(a, b, c);
  } db2
} {}
do_test shared-8.1.4 {
  execsql {
    SELECT * FROM sqlite_master;
  }
} "table abc abc [expr $AUTOVACUUM?3:2] {CREATE TABLE abc(a, b, c)}"
do_test shared-8.1.5 {
  db2 close
  execsql {
    PRAGMA encoding;
  }
} {UTF-8}
file delete -force test2.db test2.db-journal
do_test shared-8.2.1 {
  execsql {
    ATTACH 'test2.db' AS aux;
    SELECT * FROM aux.sqlite_master;
  }
} {}
do_test shared-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}
do_test shared-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}
................................................................................

#---------------------------------------------------------------------------
# The following tests - shared-9.* - test interactions between TEMP triggers
# and shared-schemas.
#
ifcapable trigger&&tempdb {

do_test shared-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-9.2 {
  execsql {
    INSERT INTO abc VALUES(4, 5, 6);
    SELECT * FROM abc_mirror;
  } db2
} {1 2 3}
do_test shared-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-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-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-10.3 {
  # An external connection should be able to read the database, but not
  # prepare a write operation.
  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-10.4 {
  # Close the connection with the write-transaction open
  db2 close
} {}
do_test shared-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-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-10.6 {
  # ... but not commit it.
  catchsql {COMMIT} db3
} {1 {database is locked}}
do_test shared-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-10.9 {
  # Commit the external transaction.
  catchsql {COMMIT} db3
} {0 {}}
integrity_check shared-10.10
do_test shared-10.11 {
  db close
  db3 close
} {}

finish_test


sqlite3_enable_shared_cache $::enable_shared_cache

#
#    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.14 2006/01/16 12:46:41 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] {

ifcapable !autovacuum {
  if {$av} break
}

# Open the database connection and execute the auto-vacuum pragma
do_test shared-$av.1.0 {
  file delete -force test.db
  sqlite3 db test.db
  execsql "pragma auto_vacuum=$::av"
  execsql {pragma auto_vacuum}
} "$av"
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 
................................................................................
# 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.
#

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).
  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
................................................................................
  }
  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 { 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, x);
    INSERT INTO seq VALUES(1, '[string repeat X 500]');
................................................................................
        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}
................................................................................
#     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.
#
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}}


................................................................................

#--------------------------------------------------------------------------
# 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);
    CREATE VIEW test1.v1 AS SELECT * FROM t1;
    CREATE TRIGGER test1.trig1 AFTER INSERT ON t1 BEGIN
      INSERT INTO t1 VALUES(new.a, new.b);
    END;
................................................................................
  execsql {
    DROP INDEX i1;
    DROP VIEW v1;
    DROP TRIGGER trig1;
    DROP TABLE t1;
  } db2
} {}
do_test shared-$av.5.1.2 {
  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
  execsql {
    SELECT * FROM t1 UNION ALL SELECT * FROM t2;
  } db2
} {1 2 3 4}
do_test shared-$av.6.1.2 {
  # 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.
  #
................................................................................
  catch {
    db1 eval {SELECT * FROM t1 UNION ALL SELECT * FROM t2} {
      lappend ret $a $b
    }
  }
  set ret
} {}
do_test shared-$av.6.1.3 {
  execsql {
    COMMIT;
    BEGIN;
    INSERT INTO t1 VALUES(7, 8);
  } db2
  set ret [list]
  catch {
................................................................................
}

#--------------------------------------------------------------------------
# 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 {
................................................................................
    INSERT INTO t2 SELECT * FROM t1;
    COMMIT;
  }
  execsql {
    PRAGMA auto_vacuum;
  }
} {1}
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.
  #

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

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


}

sqlite3_enable_shared_cache $::enable_shared_cache
finish_test

#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements some common TCL routines used for regression
# testing the SQLite library
#
# $Id: tester.tcl,v 1.59 2006/01/11 23:40:34 drh Exp $

# Make sure tclsqlite3 was compiled correctly.  Abort now with an
# error message if not.
#
if {[sqlite3 -tcl-uses-utf]} {
  if {"\u1234"=="u1234"} {
    puts stderr "***** BUILD PROBLEM *****"
................................................................................
    # there are at least N IO operations performed by SQLite as
    # a result of the script, the Nth will fail.
    do_test $testname.$n.3 {
      set r [catch $::ioerrorbody msg]
      set ::go [expr {$::sqlite_io_error_pending<=0}]
      set s [expr $::sqlite_io_error_hit==0]
      # puts "$::sqlite_io_error_pending $r $msg"

      expr { ($s && !$r) || (!$s && $r) }
      # expr {$::sqlite_io_error_pending>0 || $r!=0}
    } {1}

    # If an IO error occured, then the checksum of the database should
    # be the same as before the script that caused the IO error was run.
    if {$::go && $::ioerropts(-cksum)} {

#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements some common TCL routines used for regression
# testing the SQLite library
#
# $Id: tester.tcl,v 1.60 2006/01/16 12:46:41 danielk1977 Exp $

# Make sure tclsqlite3 was compiled correctly.  Abort now with an
# error message if not.
#
if {[sqlite3 -tcl-uses-utf]} {
  if {"\u1234"=="u1234"} {
    puts stderr "***** BUILD PROBLEM *****"
................................................................................
    # there are at least N IO operations performed by SQLite as
    # a result of the script, the Nth will fail.
    do_test $testname.$n.3 {
      set r [catch $::ioerrorbody msg]
      set ::go [expr {$::sqlite_io_error_pending<=0}]
      set s [expr $::sqlite_io_error_hit==0]
      # puts "$::sqlite_io_error_pending $r $msg"
      # puts "r=$r s=$s msg=\"$msg\""
      expr { ($s && !$r) || (!$s && $r) }
      # expr {$::sqlite_io_error_pending>0 || $r!=0}
    } {1}

    # If an IO error occured, then the checksum of the database should
    # be the same as before the script that caused the IO error was run.
    if {$::go && $::ioerropts(-cksum)} {