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.3 2005/01/25 04:27:55 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 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# 188do_test schema-7.1 { 189 set sql {SELECT * FROM abc;} 190 set ::STMT [sqlite3_prepare $::DB $sql -1 TAIL] 191 add_test_collate $::DB 1 1 1 192 sqlite3_step $::STMT 193} {SQLITE_DONE} 194do_test schema-7.2 { 195 sqlite3_reset $::STMT 196} {SQLITE_OK} 197do_test schema-7.3 { 198 add_test_collate $::DB 0 0 0 199 sqlite3_step $::STMT 200} {SQLITE_ERROR} 201do_test schema-7.4 { 202 sqlite3_finalize $::STMT 203} {SQLITE_SCHEMA} 204 205#--------------------------------------------------------------------- 206# Tests 8.1 and 8.2 check that prepared statements are invalidated when 207# the authorization function is set. 208# 209ifcapable auth { 210 do_test schema-8.1 { 211 set ::STMT [sqlite3_prepare $::DB {SELECT * FROM sqlite_master} -1 TAIL] 212 db auth {} 213 sqlite3_step $::STMT 214 } {SQLITE_ERROR} 215 do_test schema-8.3 { 216 sqlite3_finalize $::STMT 217 } {SQLITE_SCHEMA} 218} 219 220#--------------------------------------------------------------------- 221# schema-9.1: Test that if a table is dropped by one database connection, 222# other database connections are aware of the schema change. 223# schema-9.2: Test that if a view is dropped by one database connection, 224# other database connections are aware of the schema change. 225# 226do_test schema-9.1 { 227 sqlite3 db2 test.db 228 execsql { 229 DROP TABLE abc; 230 } db2 231 db2 close 232 catchsql { 233 SELECT * FROM abc; 234 } 235} {1 {no such table: abc}} 236execsql { 237 CREATE TABLE abc(a, b, c); 238} 239ifcapable view { 240 do_test schema-9.2 { 241 execsql { 242 CREATE VIEW abcview AS SELECT * FROM abc; 243 } 244 sqlite3 db2 test.db 245 execsql { 246 DROP VIEW abcview; 247 } db2 248 db2 close 249 catchsql { 250 SELECT * FROM abcview; 251 } 252 } {1 {no such table: abcview}} 253} 254 255#--------------------------------------------------------------------- 256# Test that if a CREATE TABLE statement fails because there are other 257# btree cursors open on the same database file it does not corrupt 258# the sqlite_master table. 259# 260do_test schema-10.1 { 261 execsql { 262 INSERT INTO abc VALUES(1, 2, 3); 263 } 264 set sql {SELECT * FROM abc} 265 set ::STMT [sqlite3_prepare $::DB $sql -1 TAIL] 266 sqlite3_step $::STMT 267} {SQLITE_ROW} 268do_test schema-10.2 { 269 catchsql { 270 CREATE TABLE t2(a, b, c); 271 } 272} {1 {database table is locked}} 273do_test schema-10.3 { 274 sqlite3_finalize $::STMT 275} {SQLITE_OK} 276do_test schema-10.4 { 277 sqlite3 db2 test.db 278 execsql { 279 SELECT * FROM abc 280 } db2 281} {1 2 3} 282do_test schema-10.5 { 283 db2 close 284} {} 285 286#--------------------------------------------------------------------- 287# Attempting to delete or replace a user-function or collation sequence 288# while there are active statements returns an SQLITE_BUSY error. 289# 290# schema-11.1 - 11.4: User function. 291# schema-11.5 - 11.8: Collation sequence. 292# 293do_test schema-11.1 { 294 db function tstfunc {} 295 set sql {SELECT * FROM abc} 296 set ::STMT [sqlite3_prepare $::DB $sql -1 TAIL] 297 sqlite3_step $::STMT 298} {SQLITE_ROW} 299do_test schema-11.2 { 300 sqlite_delete_function $::DB tstfunc 301} {SQLITE_BUSY} 302do_test schema-11.3 { 303 set rc [catch { 304 db function tstfunc {} 305 } msg] 306 list $rc $msg 307} {1 {Unable to delete/modify user-function due to active statements}} 308do_test schema-11.4 { 309 sqlite3_finalize $::STMT 310} {SQLITE_OK} 311do_test schema-11.5 { 312 db collate tstcollate {} 313 set sql {SELECT * FROM abc} 314 set ::STMT [sqlite3_prepare $::DB $sql -1 TAIL] 315 sqlite3_step $::STMT 316} {SQLITE_ROW} 317do_test schema-11.6 { 318 sqlite_delete_collation $::DB tstcollate 319} {SQLITE_BUSY} 320do_test schema-11.7 { 321 set rc [catch { 322 db collate tstcollate {} 323 } msg] 324 list $rc $msg 325} {1 {Unable to delete/modify collation sequence due to active statements}} 326do_test schema-11.8 { 327 sqlite3_finalize $::STMT 328} {SQLITE_OK} 329 330finish_test 331 332