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.4 2005/01/29 01:54:18 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# 32# Note: Test cases schema-6.* are missing right now. 33# 34# Test cases schema-9.* and schema-10.* test some specific bugs 35# that came up during development. 36# 37# Test cases schema-11.* test that it is impossible to delete or 38# change a collation sequence or user-function while SQL statements 39# are executing. Adding new collations or functions is allowed. 40# 41# Note: Test cases schema-11.* are also missing right now. 42 43set testdir [file dirname $argv0] 44source $testdir/tester.tcl 45 46do_test schema-1.1 { 47 set ::STMT [sqlite3_prepare $::DB {SELECT * FROM sqlite_master} -1 TAIL] 48 execsql { 49 CREATE TABLE abc(a, b, c); 50 } 51 sqlite3_step $::STMT 52} {SQLITE_ERROR} 53do_test schema-1.2 { 54 sqlite3_finalize $::STMT 55} {SQLITE_SCHEMA} 56do_test schema-1.3 { 57 set ::STMT [sqlite3_prepare $::DB {SELECT * FROM sqlite_master} -1 TAIL] 58 execsql { 59 DROP TABLE abc; 60 } 61 sqlite3_step $::STMT 62} {SQLITE_ERROR} 63do_test schema-1.4 { 64 sqlite3_finalize $::STMT 65} {SQLITE_SCHEMA} 66 67ifcapable view { 68 do_test schema-2.1 { 69 set ::STMT [sqlite3_prepare $::DB {SELECT * FROM sqlite_master} -1 TAIL] 70 execsql { 71 CREATE VIEW v1 AS SELECT * FROM sqlite_master; 72 } 73 sqlite3_step $::STMT 74 } {SQLITE_ERROR} 75 do_test schema-2.2 { 76 sqlite3_finalize $::STMT 77 } {SQLITE_SCHEMA} 78 do_test schema-2.3 { 79 set ::STMT [sqlite3_prepare $::DB {SELECT * FROM sqlite_master} -1 TAIL] 80 execsql { 81 DROP VIEW v1; 82 } 83 sqlite3_step $::STMT 84 } {SQLITE_ERROR} 85 do_test schema-2.4 { 86 sqlite3_finalize $::STMT 87 } {SQLITE_SCHEMA} 88} 89 90ifcapable trigger { 91 do_test schema-3.1 { 92 execsql { 93 CREATE TABLE abc(a, b, c); 94 } 95 set ::STMT [sqlite3_prepare $::DB {SELECT * FROM sqlite_master} -1 TAIL] 96 execsql { 97 CREATE TRIGGER abc_trig AFTER INSERT ON abc BEGIN 98 SELECT 1, 2, 3; 99 END; 100 } 101 sqlite3_step $::STMT 102 } {SQLITE_ERROR} 103 do_test schema-3.2 { 104 sqlite3_finalize $::STMT 105 } {SQLITE_SCHEMA} 106 do_test schema-3.3 { 107 set ::STMT [sqlite3_prepare $::DB {SELECT * FROM sqlite_master} -1 TAIL] 108 execsql { 109 DROP TRIGGER abc_trig; 110 } 111 sqlite3_step $::STMT 112 } {SQLITE_ERROR} 113 do_test schema-3.4 { 114 sqlite3_finalize $::STMT 115 } {SQLITE_SCHEMA} 116} 117 118do_test schema-4.1 { 119 catchsql { 120 CREATE TABLE abc(a, b, c); 121 } 122 set ::STMT [sqlite3_prepare $::DB {SELECT * FROM sqlite_master} -1 TAIL] 123 execsql { 124 CREATE INDEX abc_index ON abc(a); 125 } 126 sqlite3_step $::STMT 127} {SQLITE_ERROR} 128do_test schema-4.2 { 129 sqlite3_finalize $::STMT 130} {SQLITE_SCHEMA} 131do_test schema-4.3 { 132 set ::STMT [sqlite3_prepare $::DB {SELECT * FROM sqlite_master} -1 TAIL] 133 execsql { 134 DROP INDEX abc_index; 135 } 136 sqlite3_step $::STMT 137} {SQLITE_ERROR} 138do_test schema-4.4 { 139 sqlite3_finalize $::STMT 140} {SQLITE_SCHEMA} 141 142#--------------------------------------------------------------------- 143# Tests 5.1 to 5.4 check that prepared statements are invalidated when 144# a database is DETACHed (but not when one is ATTACHed). 145# 146do_test schema-5.1 { 147 set sql {SELECT * FROM abc;} 148 set ::STMT [sqlite3_prepare $::DB $sql -1 TAIL] 149 execsql { 150 ATTACH 'test2.db' AS aux; 151 } 152 sqlite3_step $::STMT 153} {SQLITE_DONE} 154do_test schema-5.2 { 155 sqlite3_reset $::STMT 156} {SQLITE_OK} 157do_test schema-5.3 { 158 execsql { 159 DETACH aux; 160 } 161 sqlite3_step $::STMT 162} {SQLITE_ERROR} 163do_test schema-5.4 { 164 sqlite3_finalize $::STMT 165} {SQLITE_SCHEMA} 166 167#--------------------------------------------------------------------- 168# Tests 6.* check that prepared statements are invalidated when 169# a user-function is deleted (but not when one is added). 170do_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} 176do_test schema-6.2 { 177 sqlite3_reset $::STMT 178} {SQLITE_OK} 179do_test schema-6.3 { 180 sqlite_delete_function $::DB hello_function 181 sqlite3_step $::STMT 182} {SQLITE_ERROR} 183do_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# 191ifcapable 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# 214ifcapable 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# 231do_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}} 241execsql { 242 CREATE TABLE abc(a, b, c); 243} 244ifcapable 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# 265do_test schema-10.1 { 266 execsql { 267 INSERT INTO abc VALUES(1, 2, 3); 268 } 269 set sql {SELECT * FROM abc} 270 set ::STMT [sqlite3_prepare $::DB $sql -1 TAIL] 271 sqlite3_step $::STMT 272} {SQLITE_ROW} 273do_test schema-10.2 { 274 catchsql { 275 CREATE TABLE t2(a, b, c); 276 } 277} {1 {database table is locked}} 278do_test schema-10.3 { 279 sqlite3_finalize $::STMT 280} {SQLITE_OK} 281do_test schema-10.4 { 282 sqlite3 db2 test.db 283 execsql { 284 SELECT * FROM abc 285 } db2 286} {1 2 3} 287do_test schema-10.5 { 288 db2 close 289} {} 290 291#--------------------------------------------------------------------- 292# Attempting to delete or replace a user-function or collation sequence 293# while there are active statements returns an SQLITE_BUSY error. 294# 295# schema-11.1 - 11.4: User function. 296# schema-11.5 - 11.8: Collation sequence. 297# 298do_test schema-11.1 { 299 db function tstfunc {} 300 set sql {SELECT * FROM abc} 301 set ::STMT [sqlite3_prepare $::DB $sql -1 TAIL] 302 sqlite3_step $::STMT 303} {SQLITE_ROW} 304do_test schema-11.2 { 305 sqlite_delete_function $::DB tstfunc 306} {SQLITE_BUSY} 307do_test schema-11.3 { 308 set rc [catch { 309 db function tstfunc {} 310 } msg] 311 list $rc $msg 312} {1 {Unable to delete/modify user-function due to active statements}} 313do_test schema-11.4 { 314 sqlite3_finalize $::STMT 315} {SQLITE_OK} 316do_test schema-11.5 { 317 db collate tstcollate {} 318 set sql {SELECT * FROM abc} 319 set ::STMT [sqlite3_prepare $::DB $sql -1 TAIL] 320 sqlite3_step $::STMT 321} {SQLITE_ROW} 322do_test schema-11.6 { 323 sqlite_delete_collation $::DB tstcollate 324} {SQLITE_BUSY} 325do_test schema-11.7 { 326 set rc [catch { 327 db collate tstcollate {} 328 } msg] 329 list $rc $msg 330} {1 {Unable to delete/modify collation sequence due to active statements}} 331do_test schema-11.8 { 332 sqlite3_finalize $::STMT 333} {SQLITE_OK} 334 335finish_test 336 337