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