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.5 2005/12/06 12:53:01 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# 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# 262do_test schema-10.1 { 263 execsql { 264 INSERT INTO abc VALUES(1, 2, 3); 265 } 266 set sql {SELECT * FROM abc} 267 set ::STMT [sqlite3_prepare $::DB $sql -1 TAIL] 268 sqlite3_step $::STMT 269} {SQLITE_ROW} 270do_test schema-10.2 { 271 catchsql { 272 CREATE TABLE t2(a, b, c); 273 } 274} {1 {database table is locked}} 275do_test schema-10.3 { 276 sqlite3_finalize $::STMT 277} {SQLITE_OK} 278do_test schema-10.4 { 279 sqlite3 db2 test.db 280 execsql { 281 SELECT * FROM abc 282 } db2 283} {1 2 3} 284do_test schema-10.5 { 285 db2 close 286} {} 287 288#--------------------------------------------------------------------- 289# Attempting to delete or replace a user-function or collation sequence 290# while there are active statements returns an SQLITE_BUSY error. 291# 292# schema-11.1 - 11.4: User function. 293# schema-11.5 - 11.8: Collation sequence. 294# 295do_test schema-11.1 { 296 db function tstfunc {} 297 set sql {SELECT * FROM abc} 298 set ::STMT [sqlite3_prepare $::DB $sql -1 TAIL] 299 sqlite3_step $::STMT 300} {SQLITE_ROW} 301do_test schema-11.2 { 302 sqlite_delete_function $::DB tstfunc 303} {SQLITE_BUSY} 304do_test schema-11.3 { 305 set rc [catch { 306 db function tstfunc {} 307 } msg] 308 list $rc $msg 309} {1 {Unable to delete/modify user-function due to active statements}} 310do_test schema-11.4 { 311 sqlite3_finalize $::STMT 312} {SQLITE_OK} 313do_test schema-11.5 { 314 db collate tstcollate {} 315 set sql {SELECT * FROM abc} 316 set ::STMT [sqlite3_prepare $::DB $sql -1 TAIL] 317 sqlite3_step $::STMT 318} {SQLITE_ROW} 319do_test schema-11.6 { 320 sqlite_delete_collation $::DB tstcollate 321} {SQLITE_BUSY} 322do_test schema-11.7 { 323 set rc [catch { 324 db collate tstcollate {} 325 } msg] 326 list $rc $msg 327} {1 {Unable to delete/modify collation sequence due to active statements}} 328do_test schema-11.8 { 329 sqlite3_finalize $::STMT 330} {SQLITE_OK} 331 332finish_test 333 334