1 # 2006 November 08

     2 #

     3 # The author disclaims copyright to this source code.  In place of

     4 # a legal notice, here is a blessing:

     5 #

     6 #    May you do good and not evil.

     7 #    May you find forgiveness for yourself and forgive others.

     8 #    May you share freely, never taking more than you give.

     9 #

    10 #***********************************************************************

    11 # This file implements regression tests for SQLite library.

    12 #

    13 # This file tests the various conditions under which an SQLITE_SCHEMA

    14 # error should be returned.  This is a copy of schema.test that

    15 # has been altered to use sqlite3_prepare_v2 instead of sqlite3_prepare

    16 #

    17 # $Id: schema2.test,v 1.3 2007/10/09 08:29:33 danielk1977 Exp $

    18 

    19 #---------------------------------------------------------------------

    20 # When any of the following types of SQL statements or actions are 

    21 # executed, all pre-compiled statements are invalidated. An attempt

    22 # to execute an invalidated statement always returns SQLITE_SCHEMA.

    23 #

    24 # CREATE/DROP TABLE...................................schema2-1.*

    25 # CREATE/DROP VIEW....................................schema2-2.*

    26 # CREATE/DROP TRIGGER.................................schema2-3.*

    27 # CREATE/DROP INDEX...................................schema2-4.*

    28 # DETACH..............................................schema2-5.*

    29 # Deleting a user-function............................schema2-6.*

    30 # Deleting a collation sequence.......................schema2-7.*

    31 # Setting or changing the authorization function......schema2-8.*

    32 #

    33 # Test cases schema2-9.* and schema2-10.* test some specific bugs

    34 # that came up during development.

    35 #

    36 # Test cases schema2-11.* test that it is impossible to delete or

    37 # change a collation sequence or user-function while SQL statements

    38 # are executing. Adding new collations or functions is allowed.

    39 #

    40 

    41 set testdir [file dirname $argv0]

    42 source $testdir/tester.tcl

    43 

    44 do_test schema2-1.1 {

    45   set ::STMT [sqlite3_prepare_v2 $::DB {SELECT * FROM sqlite_master} -1 TAIL]

    46   execsql {

    47     CREATE TABLE abc(a, b, c);

    48   }

    49   sqlite3_step $::STMT

    50 } {SQLITE_ROW}

    51 do_test schema2-1.2 {

    52   sqlite3_finalize $::STMT

    53 } {SQLITE_OK}

    54 do_test schema2-1.3 {

    55   set ::STMT [sqlite3_prepare_v2 $::DB {SELECT * FROM sqlite_master} -1 TAIL]

    56   execsql {

    57     DROP TABLE abc;

    58   }

    59   sqlite3_step $::STMT

    60 } {SQLITE_DONE}

    61 do_test schema2-1.4 {

    62   sqlite3_finalize $::STMT

    63 } {SQLITE_OK}

    64 

    65 

    66 ifcapable view {

    67   do_test schema2-2.1 {

    68     set ::STMT [sqlite3_prepare_v2 $::DB {SELECT * FROM sqlite_master} -1 TAIL]

    69     execsql {

    70       CREATE VIEW v1 AS SELECT * FROM sqlite_master;

    71     }

    72     sqlite3_step $::STMT

    73   } {SQLITE_ROW}

    74   do_test schema2-2.2 {

    75     sqlite3_finalize $::STMT

    76   } {SQLITE_OK}

    77   do_test schema2-2.3 {

    78     set ::STMT [sqlite3_prepare_v2 $::DB {SELECT * FROM sqlite_master} -1 TAIL]

    79     execsql {

    80       DROP VIEW v1;

    81     }

    82     sqlite3_step $::STMT

    83   } {SQLITE_DONE}

    84   do_test schema2-2.4 {

    85     sqlite3_finalize $::STMT

    86   } {SQLITE_OK}

    87 }

    88 

    89 ifcapable trigger {

    90   do_test schema2-3.1 {

    91     execsql {

    92       CREATE TABLE abc(a, b, c);

    93     }

    94     set ::STMT [sqlite3_prepare_v2 $::DB {SELECT * FROM sqlite_master} -1 TAIL]

    95     execsql {

    96       CREATE TRIGGER abc_trig AFTER INSERT ON abc BEGIN

    97         SELECT 1, 2, 3;

    98       END;

    99     }

   100     sqlite3_step $::STMT

   101   } {SQLITE_ROW}

   102   do_test schema2-3.2 {

   103     sqlite3_finalize $::STMT

   104   } {SQLITE_OK}

   105   do_test schema2-3.3 {

   106     set ::STMT [sqlite3_prepare_v2 $::DB {SELECT * FROM sqlite_master} -1 TAIL]

   107     execsql {

   108       DROP TRIGGER abc_trig;

   109     }

   110     sqlite3_step $::STMT

   111   } {SQLITE_ROW}

   112   do_test schema2-3.4 {

   113     sqlite3_finalize $::STMT

   114   } {SQLITE_OK}

   115 }

   116 

   117 do_test schema2-4.1 {

   118   catchsql {

   119     CREATE TABLE abc(a, b, c);

   120   }

   121   set ::STMT [sqlite3_prepare_v2 $::DB {SELECT * FROM sqlite_master} -1 TAIL]

   122   execsql {

   123     CREATE INDEX abc_index ON abc(a);

   124   }

   125   sqlite3_step $::STMT

   126 } {SQLITE_ROW}

   127 do_test schema2-4.2 {

   128   sqlite3_finalize $::STMT

   129 } {SQLITE_OK}

   130 do_test schema2-4.3 {

   131   set ::STMT [sqlite3_prepare_v2 $::DB {SELECT * FROM sqlite_master} -1 TAIL]

   132   execsql {

   133     DROP INDEX abc_index;

   134   }

   135   sqlite3_step $::STMT

   136 } {SQLITE_ROW}

   137 do_test schema2-4.4 {

   138   sqlite3_finalize $::STMT

   139 } {SQLITE_OK}

   140 

   141 #---------------------------------------------------------------------

   142 # Tests 5.1 to 5.4 check that prepared statements are invalidated when

   143 # a database is DETACHed (but not when one is ATTACHed).

   144 #

   145 ifcapable attach {

   146   do_test schema2-5.1 {

   147     set sql {SELECT * FROM abc;}

   148     set ::STMT [sqlite3_prepare_v2 $::DB $sql -1 TAIL]

   149     execsql {

   150       ATTACH 'test2.db' AS aux;

   151     }

   152     sqlite3_step $::STMT

   153   } {SQLITE_DONE}

   154   do_test schema2-5.2 {

   155     sqlite3_reset $::STMT

   156   } {SQLITE_OK}

   157   do_test schema2-5.3 {

   158     execsql {

   159       DETACH aux;

   160     }

   161     sqlite3_step $::STMT

   162   } {SQLITE_DONE}

   163   do_test schema2-5.4 {

   164     sqlite3_finalize $::STMT

   165   } {SQLITE_OK}

   166 }

   167 

   168 #---------------------------------------------------------------------

   169 # Tests 6.* check that prepared statements are invalidated when

   170 # a user-function is deleted (but not when one is added).

   171 do_test schema2-6.1 {

   172   set sql {SELECT * FROM abc;}

   173   set ::STMT [sqlite3_prepare_v2 $::DB $sql -1 TAIL]

   174   db function hello_function {}

   175   sqlite3_step $::STMT

   176 } {SQLITE_DONE}

   177 do_test schema2-6.2 {

   178   sqlite3_reset $::STMT

   179 } {SQLITE_OK}

   180 do_test schema2-6.3 {

   181   sqlite_delete_function $::DB hello_function

   182   sqlite3_step $::STMT

   183 } {SQLITE_DONE}

   184 do_test schema2-6.4 {

   185   sqlite3_finalize $::STMT

   186 } {SQLITE_OK}

   187 

   188 #---------------------------------------------------------------------

   189 # Tests 7.* check that prepared statements are invalidated when

   190 # a collation sequence is deleted (but not when one is added).

   191 #

   192 ifcapable utf16 {

   193   do_test schema2-7.1 {

   194     set sql {SELECT * FROM abc;}

   195     set ::STMT [sqlite3_prepare_v2 $::DB $sql -1 TAIL]

   196     add_test_collate $::DB 1 1 1

   197     sqlite3_step $::STMT

   198   } {SQLITE_DONE}

   199   do_test schema2-7.2 {

   200     sqlite3_reset $::STMT

   201   } {SQLITE_OK}

   202   do_test schema2-7.3 {

   203     add_test_collate $::DB 0 0 0 

   204     sqlite3_step $::STMT

   205   } {SQLITE_DONE}

   206   do_test schema2-7.4 {

   207     sqlite3_finalize $::STMT

   208   } {SQLITE_OK}

   209 }

   210 

   211 #---------------------------------------------------------------------

   212 # Tests 8.1 and 8.2 check that prepared statements are invalidated when

   213 # the authorization function is set.

   214 #

   215 ifcapable auth {

   216   do_test schema2-8.1 {

   217     set ::STMT [sqlite3_prepare_v2 $::DB {SELECT * FROM sqlite_master} -1 TAIL]

   218     db auth {}

   219     sqlite3_step $::STMT

   220   } {SQLITE_ROW}

   221   do_test schema2-8.3 {

   222     sqlite3_finalize $::STMT

   223   } {SQLITE_OK}

   224 }

   225 

   226 #---------------------------------------------------------------------

   227 # schema2-9.1: Test that if a table is dropped by one database connection, 

   228 #             other database connections are aware of the schema change.

   229 # schema2-9.2: Test that if a view is dropped by one database connection,

   230 #             other database connections are aware of the schema change.

   231 #

   232 do_test schema2-9.1 {

   233   sqlite3 db2 test.db

   234   execsql {

   235     DROP TABLE abc;

   236   } db2

   237   db2 close

   238   catchsql {

   239     SELECT * FROM abc;

   240   }

   241 } {1 {no such table: abc}}

   242 execsql {

   243   CREATE TABLE abc(a, b, c);

   244 }

   245 ifcapable view {

   246   do_test schema2-9.2 {

   247     execsql {

   248       CREATE VIEW abcview AS SELECT * FROM abc;

   249     }

   250     sqlite3 db2 test.db

   251     execsql {

   252       DROP VIEW abcview;

   253     } db2

   254     db2 close

   255     catchsql {

   256       SELECT * FROM abcview;

   257     }

   258   } {1 {no such table: abcview}}

   259 }

   260 

   261 #---------------------------------------------------------------------

   262 # Test that if a CREATE TABLE statement fails because there are other

   263 # btree cursors open on the same database file it does not corrupt

   264 # the sqlite_master table.

   265 #

   266 # 2007-05-02: These tests have been overcome by events.  Open btree

   267 # cursors no longer block CREATE TABLE.  But there is no reason not

   268 # to keep the tests in the test suite.

   269 #

   270 do_test schema2-10.1 {

   271   execsql {

   272     INSERT INTO abc VALUES(1, 2, 3);

   273   }

   274   set sql {SELECT * FROM abc}

   275   set ::STMT [sqlite3_prepare_v2 $::DB $sql -1 TAIL]

   276   sqlite3_step $::STMT

   277 } {SQLITE_ROW}

   278 do_test schema2-10.2 {

   279   catchsql {

   280     CREATE TABLE t2(a, b, c);

   281   }

   282 } {0 {}}

   283 do_test schema2-10.3 {

   284   sqlite3_finalize $::STMT

   285 } {SQLITE_OK}

   286 do_test schema2-10.4 {

   287   sqlite3 db2 test.db

   288   execsql {

   289     SELECT * FROM abc

   290   } db2

   291 } {1 2 3}

   292 do_test schema2-10.5 {

   293   db2 close

   294 } {}

   295 

   296 #---------------------------------------------------------------------

   297 # Attempting to delete or replace a user-function or collation sequence 

   298 # while there are active statements returns an SQLITE_BUSY error.

   299 #

   300 # schema2-11.1 - 11.4: User function.

   301 # schema2-11.5 - 11.8: Collation sequence.

   302 #

   303 do_test schema2-11.1 {

   304   db function tstfunc {}

   305   set sql {SELECT * FROM abc}

   306   set ::STMT [sqlite3_prepare_v2 $::DB $sql -1 TAIL]

   307   sqlite3_step $::STMT

   308 } {SQLITE_ROW}

   309 do_test schema2-11.2 {

   310   sqlite_delete_function $::DB tstfunc

   311 } {SQLITE_BUSY}

   312 do_test schema2-11.3 {

   313   set rc [catch {

   314     db function tstfunc {}

   315   } msg]

   316   list $rc $msg

   317 } {1 {Unable to delete/modify user-function due to active statements}}

   318 do_test schema2-11.4 {

   319   sqlite3_finalize $::STMT

   320 } {SQLITE_OK}

   321 do_test schema2-11.5 {

   322   db collate tstcollate {}

   323   set sql {SELECT * FROM abc}

   324   set ::STMT [sqlite3_prepare_v2 $::DB $sql -1 TAIL]

   325   sqlite3_step $::STMT

   326 } {SQLITE_ROW}

   327 do_test schema2-11.6 {

   328   sqlite_delete_collation $::DB tstcollate

   329 } {SQLITE_BUSY}

   330 do_test schema2-11.7 {

   331   set rc [catch {

   332     db collate tstcollate {}

   333   } msg]

   334   list $rc $msg

   335 } {1 {Unable to delete/modify collation sequence due to active statements}}

   336 do_test schema2-11.8 {

   337   sqlite3_finalize $::STMT

   338 } {SQLITE_OK}

   339 

   340 finish_test