1 # 2005 Jan 24

     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.

    15 #

    16 # $Id: schema.test,v 1.8 2007/10/09 08:29:33 danielk1977 Exp $

    17 

    18 #---------------------------------------------------------------------

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

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

    21 # to execute an invalidated statement always returns SQLITE_SCHEMA.

    22 #

    23 # CREATE/DROP TABLE...................................schema-1.*

    24 # CREATE/DROP VIEW....................................schema-2.*

    25 # CREATE/DROP TRIGGER.................................schema-3.*

    26 # CREATE/DROP INDEX...................................schema-4.*

    27 # DETACH..............................................schema-5.*

    28 # Deleting a user-function............................schema-6.*

    29 # Deleting a collation sequence.......................schema-7.*

    30 # Setting or changing the authorization function......schema-8.*

    31 # Rollback of a DDL statement.........................schema-12.*

    32 #

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

    34 # that came up during development.

    35 #

    36 # Test cases schema-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 schema-1.1 {

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

    46   execsql {

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

    48   }

    49   sqlite3_step $::STMT

    50 } {SQLITE_ERROR}

    51 do_test schema-1.2 {

    52   sqlite3_finalize $::STMT

    53 } {SQLITE_SCHEMA}

    54 do_test schema-1.3 {

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

    56   execsql {

    57     DROP TABLE abc;

    58   }

    59   sqlite3_step $::STMT

    60 } {SQLITE_ERROR}

    61 do_test schema-1.4 {

    62   sqlite3_finalize $::STMT

    63 } {SQLITE_SCHEMA}

    64 

    65 ifcapable view {

    66   do_test schema-2.1 {

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

    68     execsql {

    69       CREATE VIEW v1 AS SELECT * FROM sqlite_master;

    70     }

    71     sqlite3_step $::STMT

    72   } {SQLITE_ERROR}

    73   do_test schema-2.2 {

    74     sqlite3_finalize $::STMT

    75   } {SQLITE_SCHEMA}

    76   do_test schema-2.3 {

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

    78     execsql {

    79       DROP VIEW v1;

    80     }

    81     sqlite3_step $::STMT

    82   } {SQLITE_ERROR}

    83   do_test schema-2.4 {

    84     sqlite3_finalize $::STMT

    85   } {SQLITE_SCHEMA}

    86 }

    87 

    88 ifcapable trigger {

    89   do_test schema-3.1 {

    90     execsql {

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

    92     }

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

    94     execsql {

    95       CREATE TRIGGER abc_trig AFTER INSERT ON abc BEGIN

    96         SELECT 1, 2, 3;

    97       END;

    98     }

    99     sqlite3_step $::STMT

   100   } {SQLITE_ERROR}

   101   do_test schema-3.2 {

   102     sqlite3_finalize $::STMT

   103   } {SQLITE_SCHEMA}

   104   do_test schema-3.3 {

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

   106     execsql {

   107       DROP TRIGGER abc_trig;

   108     }

   109     sqlite3_step $::STMT

   110   } {SQLITE_ERROR}

   111   do_test schema-3.4 {

   112     sqlite3_finalize $::STMT

   113   } {SQLITE_SCHEMA}

   114 }

   115 

   116 do_test schema-4.1 {

   117   catchsql {

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

   119   }

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

   121   execsql {

   122     CREATE INDEX abc_index ON abc(a);

   123   }

   124   sqlite3_step $::STMT

   125 } {SQLITE_ERROR}

   126 do_test schema-4.2 {

   127   sqlite3_finalize $::STMT

   128 } {SQLITE_SCHEMA}

   129 do_test schema-4.3 {

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

   131   execsql {

   132     DROP INDEX abc_index;

   133   }

   134   sqlite3_step $::STMT

   135 } {SQLITE_ERROR}

   136 do_test schema-4.4 {

   137   sqlite3_finalize $::STMT

   138 } {SQLITE_SCHEMA}

   139 

   140 #---------------------------------------------------------------------

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

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

   143 #

   144 ifcapable attach {

   145   do_test schema-5.1 {

   146     set sql {SELECT * FROM abc;}

   147     set ::STMT [sqlite3_prepare $::DB $sql -1 TAIL]

   148     execsql {

   149       ATTACH 'test2.db' AS aux;

   150     }

   151     sqlite3_step $::STMT

   152   } {SQLITE_DONE}

   153   do_test schema-5.2 {

   154     sqlite3_reset $::STMT

   155   } {SQLITE_OK}

   156   do_test schema-5.3 {

   157     execsql {

   158       DETACH aux;

   159     }

   160     sqlite3_step $::STMT

   161   } {SQLITE_ERROR}

   162   do_test schema-5.4 {

   163     sqlite3_finalize $::STMT

   164   } {SQLITE_SCHEMA}

   165 }

   166 

   167 #---------------------------------------------------------------------

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

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

   170 do_test schema-6.1 {

   171   set sql {SELECT * FROM abc;}

   172   set ::STMT [sqlite3_prepare $::DB $sql -1 TAIL]

   173   db function hello_function {}

   174   sqlite3_step $::STMT

   175 } {SQLITE_DONE}

   176 do_test schema-6.2 {

   177   sqlite3_reset $::STMT

   178 } {SQLITE_OK}

   179 do_test schema-6.3 {

   180   sqlite_delete_function $::DB hello_function

   181   sqlite3_step $::STMT

   182 } {SQLITE_ERROR}

   183 do_test schema-6.4 {

   184   sqlite3_finalize $::STMT

   185 } {SQLITE_SCHEMA}

   186 

   187 #---------------------------------------------------------------------

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

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

   190 #

   191 ifcapable utf16 {

   192   do_test schema-7.1 {

   193     set sql {SELECT * FROM abc;}

   194     set ::STMT [sqlite3_prepare $::DB $sql -1 TAIL]

   195     add_test_collate $::DB 1 1 1

   196     sqlite3_step $::STMT

   197   } {SQLITE_DONE}

   198   do_test schema-7.2 {

   199     sqlite3_reset $::STMT

   200   } {SQLITE_OK}

   201   do_test schema-7.3 {

   202     add_test_collate $::DB 0 0 0 

   203     sqlite3_step $::STMT

   204   } {SQLITE_ERROR}

   205   do_test schema-7.4 {

   206     sqlite3_finalize $::STMT

   207   } {SQLITE_SCHEMA}

   208 }

   209 

   210 #---------------------------------------------------------------------

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

   212 # the authorization function is set.

   213 #

   214 ifcapable auth {

   215   do_test schema-8.1 {

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

   217     db auth {}

   218     sqlite3_step $::STMT

   219   } {SQLITE_ERROR}

   220   do_test schema-8.3 {

   221     sqlite3_finalize $::STMT

   222   } {SQLITE_SCHEMA}

   223 }

   224 

   225 #---------------------------------------------------------------------

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

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

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

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

   230 #

   231 do_test schema-9.1 {

   232   sqlite3 db2 test.db

   233   execsql {

   234     DROP TABLE abc;

   235   } db2

   236   db2 close

   237   catchsql {

   238     SELECT * FROM abc;

   239   }

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

   241 execsql {

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

   243 }

   244 ifcapable view {

   245   do_test schema-9.2 {

   246     execsql {

   247       CREATE VIEW abcview AS SELECT * FROM abc;

   248     }

   249     sqlite3 db2 test.db

   250     execsql {

   251       DROP VIEW abcview;

   252     } db2

   253     db2 close

   254     catchsql {

   255       SELECT * FROM abcview;

   256     }

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

   258 }

   259 

   260 #---------------------------------------------------------------------

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

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

   263 # the sqlite_master table.

   264 #

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

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

   267 # to keep the tests in the test suite.

   268 #

   269 do_test schema-10.1 {

   270   execsql {

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

   272   }

   273   set sql {SELECT * FROM abc}

   274   set ::STMT [sqlite3_prepare $::DB $sql -1 TAIL]

   275   sqlite3_step $::STMT

   276 } {SQLITE_ROW}

   277 do_test schema-10.2 {

   278   catchsql {

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

   280   }

   281 } {0 {}}

   282 do_test schema-10.3 {

   283   sqlite3_finalize $::STMT

   284 } {SQLITE_OK}

   285 do_test schema-10.4 {

   286   sqlite3 db2 test.db

   287   execsql {

   288     SELECT * FROM abc

   289   } db2

   290 } {1 2 3}

   291 do_test schema-10.5 {

   292   db2 close

   293 } {}

   294 

   295 #---------------------------------------------------------------------

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

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

   298 #

   299 # schema-11.1 - 11.4: User function.

   300 # schema-11.5 - 11.8: Collation sequence.

   301 #

   302 do_test schema-11.1 {

   303   db function tstfunc {}

   304   set sql {SELECT * FROM abc}

   305   set ::STMT [sqlite3_prepare $::DB $sql -1 TAIL]

   306   sqlite3_step $::STMT

   307 } {SQLITE_ROW}

   308 do_test schema-11.2 {

   309   sqlite_delete_function $::DB tstfunc

   310 } {SQLITE_BUSY}

   311 do_test schema-11.3 {

   312   set rc [catch {

   313     db function tstfunc {}

   314   } msg]

   315   list $rc $msg

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

   317 do_test schema-11.4 {

   318   sqlite3_finalize $::STMT

   319 } {SQLITE_OK}

   320 do_test schema-11.5 {

   321   db collate tstcollate {}

   322   set sql {SELECT * FROM abc}

   323   set ::STMT [sqlite3_prepare $::DB $sql -1 TAIL]

   324   sqlite3_step $::STMT

   325 } {SQLITE_ROW}

   326 do_test schema-11.6 {

   327   sqlite_delete_collation $::DB tstcollate

   328 } {SQLITE_BUSY}

   329 do_test schema-11.7 {

   330   set rc [catch {

   331     db collate tstcollate {}

   332   } msg]

   333   list $rc $msg

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

   335 do_test schema-11.8 {

   336   sqlite3_finalize $::STMT

   337 } {SQLITE_OK}

   338 

   339 # The following demonstrates why statements need to be expired whenever

   340 # there is a rollback (explicit or otherwise).

   341 #

   342 do_test schema-12.1 {

   343   # Begin a transaction and create a table. This increments 

   344   # the schema cookie. Then compile an SQL statement, using

   345   # the current (incremented) value of the cookie.

   346   execsql {

   347     BEGIN;

   348     CREATE TABLE t3(a, b, c);

   349   }

   350   set ::STMT [sqlite3_prepare $::DB "CREATE TABLE t4(a,b,c)" -1 TAIL]

   351 

   352   # Rollback the transaction, resetting the schema cookie to the value

   353   # it had at the start of this test case. Then create a table, 

   354   # incrementing the schema cookie.

   355   execsql {

   356     ROLLBACK;

   357     CREATE TABLE t4(a, b, c);

   358   }

   359 

   360   # The schema cookie now has the same value as it did when SQL statement

   361   # $::STMT was prepared. So unless it has been expired, it would be

   362   # possible to run the "CREATE TABLE t4" statement and create a

   363   # duplicate table.

   364   list [sqlite3_step $::STMT] [sqlite3_finalize $::STMT]

   365 } {SQLITE_ERROR SQLITE_SCHEMA}

   366 

   367 finish_test