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.4 2009/02/04 17:40:58 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 41 set testdir [file dirname $argv0] 42 source $testdir/tester.tcl 43 44 do_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} 51 do_test schema2-1.2 { 52 sqlite3_finalize $::STMT 53 } {SQLITE_OK} 54 do_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} 61 do_test schema2-1.4 { 62 sqlite3_finalize $::STMT 63 } {SQLITE_OK} 64 65 66 ifcapable 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 89 ifcapable 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 117 do_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} 127 do_test schema2-4.2 { 128 sqlite3_finalize $::STMT 129 } {SQLITE_OK} 130 do_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} 137 do_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 # 145 ifcapable attach { 146 do_test schema2-5.1 { 147 set sql {SELECT * FROM abc;} 148 set ::STMT [sqlite3_prepare_v2 $::DB $sql -1 TAIL] 149 execsql { 150 ATTACH 'test2.db' AS aux; 151 } 152 sqlite3_step $::STMT 153 } {SQLITE_DONE} 154 do_test schema2-5.2 { 155 sqlite3_reset $::STMT 156 } {SQLITE_OK} 157 do_test schema2-5.3 { 158 execsql { 159 DETACH aux; 160 } 161 sqlite3_step $::STMT 162 } {SQLITE_DONE} 163 do_test schema2-5.4 { 164 sqlite3_finalize $::STMT 165 } {SQLITE_OK} 166 } 167 168 #--------------------------------------------------------------------- 169 # Tests 6.* check that prepared statements are invalidated when 170 # a user-function is deleted (but not when one is added). 171 do_test schema2-6.1 { 172 set sql {SELECT * FROM abc;} 173 set ::STMT [sqlite3_prepare_v2 $::DB $sql -1 TAIL] 174 db function hello_function {} 175 sqlite3_step $::STMT 176 } {SQLITE_DONE} 177 do_test schema2-6.2 { 178 sqlite3_reset $::STMT 179 } {SQLITE_OK} 180 do_test schema2-6.3 { 181 sqlite_delete_function $::DB hello_function 182 sqlite3_step $::STMT 183 } {SQLITE_DONE} 184 do_test schema2-6.4 { 185 sqlite3_finalize $::STMT 186 } {SQLITE_OK} 187 188 #--------------------------------------------------------------------- 189 # Tests 7.* check that prepared statements are invalidated when 190 # a collation sequence is deleted (but not when one is added). 191 # 192 ifcapable utf16 { 193 do_test schema2-7.1 { 194 set sql {SELECT * FROM abc;} 195 set ::STMT [sqlite3_prepare_v2 $::DB $sql -1 TAIL] 196 add_test_collate $::DB 1 1 1 197 sqlite3_step $::STMT 198 } {SQLITE_DONE} 199 do_test schema2-7.2 { 200 sqlite3_reset $::STMT 201 } {SQLITE_OK} 202 do_test schema2-7.3 { 203 add_test_collate $::DB 0 0 0 204 sqlite3_step $::STMT 205 } {SQLITE_DONE} 206 do_test schema2-7.4 { 207 sqlite3_finalize $::STMT 208 } {SQLITE_OK} 209 } 210 211 #--------------------------------------------------------------------- 212 # Tests 8.1 and 8.2 check that prepared statements are invalidated when 213 # the authorization function is set. 214 # 215 ifcapable auth { 216 do_test schema2-8.1 { 217 set ::STMT [sqlite3_prepare_v2 $::DB {SELECT * FROM sqlite_master} -1 TAIL] 218 db auth {} 219 sqlite3_step $::STMT 220 } {SQLITE_ROW} 221 do_test schema2-8.3 { 222 sqlite3_finalize $::STMT 223 } {SQLITE_OK} 224 } 225 226 #--------------------------------------------------------------------- 227 # schema2-9.1: Test that if a table is dropped by one database connection, 228 # other database connections are aware of the schema change. 229 # schema2-9.2: Test that if a view is dropped by one database connection, 230 # other database connections are aware of the schema change. 231 # 232 do_test schema2-9.1 { 233 sqlite3 db2 test.db 234 execsql { 235 DROP TABLE abc; 236 } db2 237 db2 close 238 catchsql { 239 SELECT * FROM abc; 240 } 241 } {1 {no such table: abc}} 242 execsql { 243 CREATE TABLE abc(a, b, c); 244 } 245 ifcapable view { 246 do_test schema2-9.2 { 247 execsql { 248 CREATE VIEW abcview AS SELECT * FROM abc; 249 } 250 sqlite3 db2 test.db 251 execsql { 252 DROP VIEW abcview; 253 } db2 254 db2 close 255 catchsql { 256 SELECT * FROM abcview; 257 } 258 } {1 {no such table: abcview}} 259 } 260 261 #--------------------------------------------------------------------- 262 # Test that if a CREATE TABLE statement fails because there are other 263 # btree cursors open on the same database file it does not corrupt 264 # the sqlite_master table. 265 # 266 # 2007-05-02: These tests have been overcome by events. Open btree 267 # cursors no longer block CREATE TABLE. But there is no reason not 268 # to keep the tests in the test suite. 269 # 270 do_test schema2-10.1 { 271 execsql { 272 INSERT INTO abc VALUES(1, 2, 3); 273 } 274 set sql {SELECT * FROM abc} 275 set ::STMT [sqlite3_prepare_v2 $::DB $sql -1 TAIL] 276 sqlite3_step $::STMT 277 } {SQLITE_ROW} 278 do_test schema2-10.2 { 279 catchsql { 280 CREATE TABLE t2(a, b, c); 281 } 282 } {0 {}} 283 do_test schema2-10.3 { 284 sqlite3_finalize $::STMT 285 } {SQLITE_OK} 286 do_test schema2-10.4 { 287 sqlite3 db2 test.db 288 execsql { 289 SELECT * FROM abc 290 } db2 291 } {1 2 3} 292 do_test schema2-10.5 { 293 db2 close 294 } {} 295 296 #--------------------------------------------------------------------- 297 # Attempting to delete or replace a user-function or collation sequence 298 # while there are active statements returns an SQLITE_BUSY error. 299 # 300 # schema2-11.1 - 11.4: User function. 301 # schema2-11.5 - 11.8: Collation sequence. 302 # 303 do_test schema2-11.1 { 304 db function tstfunc {} 305 set sql {SELECT * FROM abc} 306 set ::STMT [sqlite3_prepare_v2 $::DB $sql -1 TAIL] 307 sqlite3_step $::STMT 308 } {SQLITE_ROW} 309 do_test schema2-11.2 { 310 sqlite_delete_function $::DB tstfunc 311 } {SQLITE_BUSY} 312 do_test schema2-11.3 { 313 set rc [catch { 314 db function tstfunc {} 315 } msg] 316 list $rc $msg 317 } {1 {unable to delete/modify user-function due to active statements}} 318 do_test schema2-11.4 { 319 sqlite3_finalize $::STMT 320 } {SQLITE_OK} 321 do_test schema2-11.5 { 322 db collate tstcollate {} 323 set sql {SELECT * FROM abc} 324 set ::STMT [sqlite3_prepare_v2 $::DB $sql -1 TAIL] 325 sqlite3_step $::STMT 326 } {SQLITE_ROW} 327 do_test schema2-11.6 { 328 sqlite_delete_collation $::DB tstcollate 329 } {SQLITE_BUSY} 330 do_test schema2-11.7 { 331 set rc [catch { 332 db collate tstcollate {} 333 } msg] 334 list $rc $msg 335 } {1 {unable to delete/modify collation sequence due to active statements}} 336 do_test schema2-11.8 { 337 sqlite3_finalize $::STMT 338 } {SQLITE_OK} 339 340 finish_test 341
