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.6 2007/05/02 17:54:56 drh 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# 32# Test cases schema-9.* and schema-10.* test some specific bugs 33# that came up during development. 34# 35# Test cases schema-11.* test that it is impossible to delete or 36# change a collation sequence or user-function while SQL statements 37# are executing. Adding new collations or functions is allowed. 38# 39 40set testdir [file dirname $argv0] 41source $testdir/tester.tcl 42 43do_test schema-1.1 { 44 set ::STMT [sqlite3_prepare $::DB {SELECT * FROM sqlite_master} -1 TAIL] 45 execsql { 46 CREATE TABLE abc(a, b, c); 47 } 48 sqlite3_step $::STMT 49} {SQLITE_ERROR} 50do_test schema-1.2 { 51 sqlite3_finalize $::STMT 52} {SQLITE_SCHEMA} 53do_test schema-1.3 { 54 set ::STMT [sqlite3_prepare $::DB {SELECT * FROM sqlite_master} -1 TAIL] 55 execsql { 56 DROP TABLE abc; 57 } 58 sqlite3_step $::STMT 59} {SQLITE_ERROR} 60do_test schema-1.4 { 61 sqlite3_finalize $::STMT 62} {SQLITE_SCHEMA} 63 64ifcapable view { 65 do_test schema-2.1 { 66 set ::STMT [sqlite3_prepare $::DB {SELECT * FROM sqlite_master} -1 TAIL] 67 execsql { 68 CREATE VIEW v1 AS SELECT * FROM sqlite_master; 69 } 70 sqlite3_step $::STMT 71 } {SQLITE_ERROR} 72 do_test schema-2.2 { 73 sqlite3_finalize $::STMT 74 } {SQLITE_SCHEMA} 75 do_test schema-2.3 { 76 set ::STMT [sqlite3_prepare $::DB {SELECT * FROM sqlite_master} -1 TAIL] 77 execsql { 78 DROP VIEW v1; 79 } 80 sqlite3_step $::STMT 81 } {SQLITE_ERROR} 82 do_test schema-2.4 { 83 sqlite3_finalize $::STMT 84 } {SQLITE_SCHEMA} 85} 86 87ifcapable trigger { 88 do_test schema-3.1 { 89 execsql { 90 CREATE TABLE abc(a, b, c); 91 } 92 set ::STMT [sqlite3_prepare $::DB {SELECT * FROM sqlite_master} -1 TAIL] 93 execsql { 94 CREATE TRIGGER abc_trig AFTER INSERT ON abc BEGIN 95 SELECT 1, 2, 3; 96 END; 97 } 98 sqlite3_step $::STMT 99 } {SQLITE_ERROR} 100 do_test schema-3.2 { 101 sqlite3_finalize $::STMT 102 } {SQLITE_SCHEMA} 103 do_test schema-3.3 { 104 set ::STMT [sqlite3_prepare $::DB {SELECT * FROM sqlite_master} -1 TAIL] 105 execsql { 106 DROP TRIGGER abc_trig; 107 } 108 sqlite3_step $::STMT 109 } {SQLITE_ERROR} 110 do_test schema-3.4 { 111 sqlite3_finalize $::STMT 112 } {SQLITE_SCHEMA} 113} 114 115do_test schema-4.1 { 116 catchsql { 117 CREATE TABLE abc(a, b, c); 118 } 119 set ::STMT [sqlite3_prepare $::DB {SELECT * FROM sqlite_master} -1 TAIL] 120 execsql { 121 CREATE INDEX abc_index ON abc(a); 122 } 123 sqlite3_step $::STMT 124} {SQLITE_ERROR} 125do_test schema-4.2 { 126 sqlite3_finalize $::STMT 127} {SQLITE_SCHEMA} 128do_test schema-4.3 { 129 set ::STMT [sqlite3_prepare $::DB {SELECT * FROM sqlite_master} -1 TAIL] 130 execsql { 131 DROP INDEX abc_index; 132 } 133 sqlite3_step $::STMT 134} {SQLITE_ERROR} 135do_test schema-4.4 { 136 sqlite3_finalize $::STMT 137} {SQLITE_SCHEMA} 138 139#--------------------------------------------------------------------- 140# Tests 5.1 to 5.4 check that prepared statements are invalidated when 141# a database is DETACHed (but not when one is ATTACHed). 142# 143do_test schema-5.1 { 144 set sql {SELECT * FROM abc;} 145 set ::STMT [sqlite3_prepare $::DB $sql -1 TAIL] 146 execsql { 147 ATTACH 'test2.db' AS aux; 148 } 149 sqlite3_step $::STMT 150} {SQLITE_DONE} 151do_test schema-5.2 { 152 sqlite3_reset $::STMT 153} {SQLITE_OK} 154do_test schema-5.3 { 155 execsql { 156 DETACH aux; 157 } 158 sqlite3_step $::STMT 159} {SQLITE_ERROR} 160do_test schema-5.4 { 161 sqlite3_finalize $::STMT 162} {SQLITE_SCHEMA} 163 164#--------------------------------------------------------------------- 165# Tests 6.* check that prepared statements are invalidated when 166# a user-function is deleted (but not when one is added). 167do_test schema-6.1 { 168 set sql {SELECT * FROM abc;} 169 set ::STMT [sqlite3_prepare $::DB $sql -1 TAIL] 170 db function hello_function {} 171 sqlite3_step $::STMT 172} {SQLITE_DONE} 173do_test schema-6.2 { 174 sqlite3_reset $::STMT 175} {SQLITE_OK} 176do_test schema-6.3 { 177 sqlite_delete_function $::DB hello_function 178 sqlite3_step $::STMT 179} {SQLITE_ERROR} 180do_test schema-6.4 { 181 sqlite3_finalize $::STMT 182} {SQLITE_SCHEMA} 183 184#--------------------------------------------------------------------- 185# Tests 7.* check that prepared statements are invalidated when 186# a collation sequence is deleted (but not when one is added). 187# 188ifcapable utf16 { 189 do_test schema-7.1 { 190 set sql {SELECT * FROM abc;} 191 set ::STMT [sqlite3_prepare $::DB $sql -1 TAIL] 192 add_test_collate $::DB 1 1 1 193 sqlite3_step $::STMT 194 } {SQLITE_DONE} 195 do_test schema-7.2 { 196 sqlite3_reset $::STMT 197 } {SQLITE_OK} 198 do_test schema-7.3 { 199 add_test_collate $::DB 0 0 0 200 sqlite3_step $::STMT 201 } {SQLITE_ERROR} 202 do_test schema-7.4 { 203 sqlite3_finalize $::STMT 204 } {SQLITE_SCHEMA} 205} 206 207#--------------------------------------------------------------------- 208# Tests 8.1 and 8.2 check that prepared statements are invalidated when 209# the authorization function is set. 210# 211ifcapable auth { 212 do_test schema-8.1 { 213 set ::STMT [sqlite3_prepare $::DB {SELECT * FROM sqlite_master} -1 TAIL] 214 db auth {} 215 sqlite3_step $::STMT 216 } {SQLITE_ERROR} 217 do_test schema-8.3 { 218 sqlite3_finalize $::STMT 219 } {SQLITE_SCHEMA} 220} 221 222#--------------------------------------------------------------------- 223# schema-9.1: Test that if a table is dropped by one database connection, 224# other database connections are aware of the schema change. 225# schema-9.2: Test that if a view is dropped by one database connection, 226# other database connections are aware of the schema change. 227# 228do_test schema-9.1 { 229 sqlite3 db2 test.db 230 execsql { 231 DROP TABLE abc; 232 } db2 233 db2 close 234 catchsql { 235 SELECT * FROM abc; 236 } 237} {1 {no such table: abc}} 238execsql { 239 CREATE TABLE abc(a, b, c); 240} 241ifcapable view { 242 do_test schema-9.2 { 243 execsql { 244 CREATE VIEW abcview AS SELECT * FROM abc; 245 } 246 sqlite3 db2 test.db 247 execsql { 248 DROP VIEW abcview; 249 } db2 250 db2 close 251 catchsql { 252 SELECT * FROM abcview; 253 } 254 } {1 {no such table: abcview}} 255} 256 257#--------------------------------------------------------------------- 258# Test that if a CREATE TABLE statement fails because there are other 259# btree cursors open on the same database file it does not corrupt 260# the sqlite_master table. 261# 262# 2007-05-02: These tests have been overcome by events. Open btree 263# cursors no longer block CREATE TABLE. But there is no reason not 264# to keep the tests in the test suite. 265# 266do_test schema-10.1 { 267 execsql { 268 INSERT INTO abc VALUES(1, 2, 3); 269 } 270 set sql {SELECT * FROM abc} 271 set ::STMT [sqlite3_prepare $::DB $sql -1 TAIL] 272 sqlite3_step $::STMT 273} {SQLITE_ROW} 274do_test schema-10.2 { 275 catchsql { 276 CREATE TABLE t2(a, b, c); 277 } 278} {0 {}} 279do_test schema-10.3 { 280 sqlite3_finalize $::STMT 281} {SQLITE_OK} 282do_test schema-10.4 { 283 sqlite3 db2 test.db 284 execsql { 285 SELECT * FROM abc 286 } db2 287} {1 2 3} 288do_test schema-10.5 { 289 db2 close 290} {} 291 292#--------------------------------------------------------------------- 293# Attempting to delete or replace a user-function or collation sequence 294# while there are active statements returns an SQLITE_BUSY error. 295# 296# schema-11.1 - 11.4: User function. 297# schema-11.5 - 11.8: Collation sequence. 298# 299do_test schema-11.1 { 300 db function tstfunc {} 301 set sql {SELECT * FROM abc} 302 set ::STMT [sqlite3_prepare $::DB $sql -1 TAIL] 303 sqlite3_step $::STMT 304} {SQLITE_ROW} 305do_test schema-11.2 { 306 sqlite_delete_function $::DB tstfunc 307} {SQLITE_BUSY} 308do_test schema-11.3 { 309 set rc [catch { 310 db function tstfunc {} 311 } msg] 312 list $rc $msg 313} {1 {Unable to delete/modify user-function due to active statements}} 314do_test schema-11.4 { 315 sqlite3_finalize $::STMT 316} {SQLITE_OK} 317do_test schema-11.5 { 318 db collate tstcollate {} 319 set sql {SELECT * FROM abc} 320 set ::STMT [sqlite3_prepare $::DB $sql -1 TAIL] 321 sqlite3_step $::STMT 322} {SQLITE_ROW} 323do_test schema-11.6 { 324 sqlite_delete_collation $::DB tstcollate 325} {SQLITE_BUSY} 326do_test schema-11.7 { 327 set rc [catch { 328 db collate tstcollate {} 329 } msg] 330 list $rc $msg 331} {1 {Unable to delete/modify collation sequence due to active statements}} 332do_test schema-11.8 { 333 sqlite3_finalize $::STMT 334} {SQLITE_OK} 335 336finish_test 337