Return to temptrigger.test CVS log

Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test

sqlite3

    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.1.1.1 2012/02/21 17:04:16 misho 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