# 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