test/schema.test

# 2004 Jun 27
#
# 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.
#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file tests the various conditions under which an SQLITE_SCHEMA
# error should be returned.
#
# $Id: schema.test,v 1.1 2005/01/24 10:26:00 danielk1977 Exp $

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

# schema-1.1: Test that if a table is dropped by one database connection, 
#             other database connections are aware of the schema change.
# schema-1.2: Test that if a view is dropped by one database connection,
#             other database connections are aware of the schema change.
#
do_test schema-1.1 {
  execsql {
    CREATE TABLE abc(a, b, c);
  }
  sqlite3 db2 test.db
  execsql {
    DROP TABLE abc;
  } db2
  catchsql {
    SELECT * FROM abc;
  }
} {1 {no such table: abc}}
do_test schema-1.2 {
  execsql {
    CREATE TABLE abc(a, b, c);
    CREATE VIEW abcview AS SELECT * FROM abc;
  }
  sqlite3 db2 test.db
  execsql {
    DROP VIEW abcview;
  } db2
  db2 close
  catchsql {
    SELECT * FROM abcview;
  }
} {1 {no such table: abcview}}

# Tests 2.1 to 2.4 check that prepared statements are invalidated when
# a collation sequence is deleted (but not when one is added).
#
do_test schema-2.1 {
  set sql {SELECT * FROM abc;}
  set ::STMT [sqlite3_prepare $::DB $sql -1 TAIL]
  add_test_collate $::DB 1 1 1
  sqlite3_step $::STMT
} {SQLITE_DONE}
do_test schema-2.2 {
  sqlite3_reset $::STMT
} {SQLITE_OK}
do_test schema-2.3 {
  add_test_collate $::DB 0 0 0 
  sqlite3_step $::STMT
} {SQLITE_ERROR}
do_test schema-2.4 {
  sqlite3_finalize $::STMT
} {SQLITE_SCHEMA}

# Tests 3.1 to 3.4 check that prepared statements are invalidated when
# a database is DETACHed (but not when one is ATTACHed).
#
do_test schema-3.1 {
  set sql {SELECT * FROM abc;}
  set ::STMT [sqlite3_prepare $::DB $sql -1 TAIL]
  execsql {
    ATTACH 'test2.db' AS aux;
  }
  sqlite3_step $::STMT
} {SQLITE_DONE}
do_test schema-3.2 {
  sqlite3_reset $::STMT
} {SQLITE_OK}
do_test schema-3.3 {
  execsql {
    DETACH aux;
  }
  sqlite3_step $::STMT
} {SQLITE_ERROR}
do_test schema-3.4 {
  sqlite3_finalize $::STMT
} {SQLITE_SCHEMA}


# Test that if a CREATE TABLE statement fails because there are other
# btree cursors open on the same database file it does not corrupt
# the sqlite_master table.
#
do_test schema-4.1 {
  execsql {
    INSERT INTO abc VALUES(1, 2, 3);
  }
  set sql {SELECT * FROM abc}
  set ::STMT [sqlite3_prepare $::DB $sql -1 TAIL]
  sqlite3_step $::STMT
} {SQLITE_ROW}
do_test schema-4.2 {
  catchsql {
    CREATE TABLE t2(a, b, c);
  }
} {1 {database table is locked}}
do_test schema-4.3 {
  sqlite3_finalize $::STMT
} {SQLITE_OK}
do_test schema-4.4 {
  sqlite3 db2 test.db
  execsql {
    SELECT * FROM abc
  } db2
} {1 2 3}
do_test schema-4.5 {
  db2 close
} {}

finish_test