Annotation of embedaddon/sqlite3/test/temptrigger.test, revision 1.1
1.1 ! misho 1: # 2009 February 27
! 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: #
! 12: # $Id: temptrigger.test,v 1.3 2009/04/15 13:07:19 drh Exp $
! 13:
! 14: set testdir [file dirname $argv0]
! 15: source $testdir/tester.tcl
! 16:
! 17: ifcapable {!trigger || !shared_cache} { finish_test ; return }
! 18:
! 19: # Test cases:
! 20: #
! 21: # temptrigger-1.*: Shared cache problem.
! 22: # temptrigger-2.*: A similar shared cache problem.
! 23: # temptrigger-3.*: Attached database problem.
! 24: #
! 25:
! 26: #-------------------------------------------------------------------------
! 27: # Test case temptrigger-1.* demonstrates a problem with temp triggers
! 28: # in shared-cache mode. If process 1 connections to a shared-cache and
! 29: # creates a temp trigger, the temp trigger is linked into the shared-cache
! 30: # schema. If process 2 reloads the shared-cache schema from disk, then
! 31: # it does not recreate the temp trigger belonging to process 1. From the
! 32: # point of view of process 1, the temp trigger just disappeared.
! 33: #
! 34: # temptrigger-1.1: In shared cache mode, create a table in the main
! 35: # database and add a temp trigger to it.
! 36: #
! 37: # temptrigger-1.2: Check that the temp trigger is correctly fired. Check
! 38: # that the temp trigger is not fired by statements
! 39: # executed by a second connection connected to the
! 40: # same shared cache.
! 41: #
! 42: # temptrigger-1.3: Using the second connection to the shared-cache, cause
! 43: # the shared-cache schema to be reloaded.
! 44: #
! 45: # temptrigger-1.4: Check that the temp trigger is still fired correctly.
! 46: #
! 47: # temptrigger-1.5: Check that the temp trigger can be dropped without error.
! 48: #
! 49: db close
! 50: set ::enable_shared_cache [sqlite3_enable_shared_cache]
! 51: sqlite3_enable_shared_cache 1
! 52:
! 53: sqlite3 db test.db
! 54: sqlite3 db2 test.db
! 55:
! 56: do_test temptrigger-1.1 {
! 57: execsql {
! 58: CREATE TABLE t1(a, b);
! 59: CREATE TEMP TABLE tt1(a, b);
! 60: CREATE TEMP TRIGGER tr1 AFTER INSERT ON t1 BEGIN
! 61: INSERT INTO tt1 VALUES(new.a, new.b);
! 62: END;
! 63: }
! 64: } {}
! 65:
! 66: do_test temptrigger-1.2.1 {
! 67: execsql { INSERT INTO t1 VALUES(1, 2) }
! 68: execsql { SELECT * FROM t1 }
! 69: } {1 2}
! 70: do_test temptrigger-1.2.2 {
! 71: execsql { SELECT * FROM tt1 }
! 72: } {1 2}
! 73: do_test temptrigger-1.2.3 {
! 74: execsql { INSERT INTO t1 VALUES(3, 4) } db2
! 75: execsql { SELECT * FROM t1 }
! 76: } {1 2 3 4}
! 77: do_test temptrigger-1.2.4 {
! 78: execsql { SELECT * FROM tt1 }
! 79: } {1 2}
! 80:
! 81: # Cause the shared-cache schema to be reloaded.
! 82: #
! 83: do_test temptrigger-1.3 {
! 84: execsql { BEGIN; CREATE TABLE t3(a, b); ROLLBACK; } db2
! 85: } {}
! 86:
! 87: do_test temptrigger-1.4 {
! 88: execsql { INSERT INTO t1 VALUES(5, 6) }
! 89: execsql { SELECT * FROM tt1 }
! 90: } {1 2 5 6}
! 91:
! 92: do_test temptrigger-1.5 {
! 93: # Before the bug was fixed, the following 'DROP TRIGGER' hit an
! 94: # assert if executed.
! 95: #execsql { DROP TRIGGER tr1 }
! 96: } {}
! 97:
! 98: catch {db close}
! 99: catch {db2 close}
! 100:
! 101: #-------------------------------------------------------------------------
! 102: # Tests temptrigger-2.* are similar to temptrigger-1.*, except that
! 103: # temptrigger-2.3 simply opens and closes a connection to the shared-cache.
! 104: # It does not do anything special to cause the schema to be reloaded.
! 105: #
! 106: do_test temptrigger-2.1 {
! 107: sqlite3 db test.db
! 108: execsql {
! 109: DELETE FROM t1;
! 110: CREATE TEMP TABLE tt1(a, b);
! 111: CREATE TEMP TRIGGER tr1 AFTER INSERT ON t1 BEGIN
! 112: INSERT INTO tt1 VALUES(new.a, new.b);
! 113: END;
! 114: }
! 115: } {}
! 116: do_test temptrigger-2.2 {
! 117: execsql {
! 118: INSERT INTO t1 VALUES(10, 20);
! 119: SELECT * FROM tt1;
! 120: }
! 121: } {10 20}
! 122: do_test temptrigger-2.3 {
! 123: sqlite3 db2 test.db
! 124: db2 close
! 125: } {}
! 126: do_test temptrigger-2.4 {
! 127: execsql {
! 128: INSERT INTO t1 VALUES(30, 40);
! 129: SELECT * FROM tt1;
! 130: }
! 131: } {10 20 30 40}
! 132: do_test temptrigger-2.5 {
! 133: #execsql { DROP TRIGGER tr1 }
! 134: } {}
! 135:
! 136: catch {db close}
! 137: catch {db2 close}
! 138: sqlite3_enable_shared_cache $::enable_shared_cache
! 139:
! 140: #-------------------------------------------------------------------------
! 141: # Test case temptrigger-3.* demonstrates a problem with temp triggers
! 142: # on tables located in attached databases. At one point when SQLite reloaded
! 143: # the schema of an attached database (because some other connection had
! 144: # changed the schema cookie) it was not re-creating temp triggers attached
! 145: # to tables located within the attached database.
! 146: #
! 147: # temptrigger-3.1: Attach database 'test2.db' to connection [db]. Add a
! 148: # temp trigger to a table in 'test2.db'.
! 149: #
! 150: # temptrigger-3.2: Check that the temp trigger is correctly fired.
! 151: #
! 152: # temptrigger-3.3: Update the schema of 'test2.db' using an external
! 153: # connection. This forces [db] to reload the 'test2.db'
! 154: # schema. Check that the temp trigger is still fired
! 155: # correctly.
! 156: #
! 157: # temptrigger-3.4: Check that the temp trigger can be dropped without error.
! 158: #
! 159: do_test temptrigger-3.1 {
! 160: catch { forcedelete test2.db test2.db-journal }
! 161: catch { forcedelete test.db test.db-journal }
! 162: sqlite3 db test.db
! 163: sqlite3 db2 test2.db
! 164: execsql { CREATE TABLE t2(a, b) } db2
! 165: execsql {
! 166: ATTACH 'test2.db' AS aux;
! 167: CREATE TEMP TABLE tt2(a, b);
! 168: CREATE TEMP TRIGGER tr2 AFTER INSERT ON aux.t2 BEGIN
! 169: INSERT INTO tt2 VALUES(new.a, new.b);
! 170: END;
! 171: }
! 172: } {}
! 173:
! 174: do_test temptrigger-3.2.1 {
! 175: execsql {
! 176: INSERT INTO aux.t2 VALUES(1, 2);
! 177: SELECT * FROM aux.t2;
! 178: }
! 179: } {1 2}
! 180: do_test temptrigger-3.2.2 {
! 181: execsql { SELECT * FROM tt2 }
! 182: } {1 2}
! 183:
! 184: do_test temptrigger-3.3.1 {
! 185: execsql { CREATE TABLE t3(a, b) } db2
! 186: execsql {
! 187: INSERT INTO aux.t2 VALUES(3, 4);
! 188: SELECT * FROM aux.t2;
! 189: }
! 190: } {1 2 3 4}
! 191: do_test temptrigger-3.3.2 {
! 192: execsql { SELECT * FROM tt2 }
! 193: } {1 2 3 4}
! 194:
! 195: do_test temptrigger-3.4 {
! 196: # Before the bug was fixed, the following 'DROP TRIGGER' hit an
! 197: # assert if executed.
! 198: #execsql { DROP TRIGGER tr2 }
! 199: } {}
! 200:
! 201: catch { db close }
! 202: catch { db2 close }
! 203:
! 204: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to temptrigger.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