Annotation of embedaddon/sqlite3/test/schema2.test, revision 1.1
1.1 ! misho 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
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to schema2.test CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test