Annotation of embedaddon/sqlite3/test/hook.test, revision 1.1

1.1     ! misho       1: # 2004 Jan 14
        !             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 TCL interface to the
        !            12: # SQLite library. 
        !            13: #
        !            14: # The focus of the tests in this file is the  following interface:
        !            15: #
        !            16: #      sqlite_commit_hook    (tests hook-1..hook-3 inclusive)
        !            17: #      sqlite_update_hook    (tests hook-4-*)
        !            18: #      sqlite_rollback_hook  (tests hook-5.*)
        !            19: #
        !            20: # $Id: hook.test,v 1.15 2009/04/07 14:14:23 danielk1977 Exp $
        !            21: 
        !            22: set testdir [file dirname $argv0]
        !            23: source $testdir/tester.tcl
        !            24: 
        !            25: do_test hook-1.2 {
        !            26:   db commit_hook
        !            27: } {}
        !            28: 
        !            29: 
        !            30: do_test hook-3.1 {
        !            31:   set commit_cnt 0
        !            32:   proc commit_hook {} {
        !            33:     incr ::commit_cnt
        !            34:     return 0
        !            35:   }
        !            36:   db commit_hook ::commit_hook
        !            37:   db commit_hook
        !            38: } {::commit_hook}
        !            39: do_test hook-3.2 {
        !            40:   set commit_cnt
        !            41: } {0}
        !            42: do_test hook-3.3 {
        !            43:   execsql {
        !            44:     CREATE TABLE t2(a,b);
        !            45:   }
        !            46:   set commit_cnt
        !            47: } {1}
        !            48: do_test hook-3.4 {
        !            49:   execsql {
        !            50:     INSERT INTO t2 VALUES(1,2);
        !            51:     INSERT INTO t2 SELECT a+1, b+1 FROM t2;
        !            52:     INSERT INTO t2 SELECT a+2, b+2 FROM t2;
        !            53:   }
        !            54:   set commit_cnt
        !            55: } {4}
        !            56: do_test hook-3.5 {
        !            57:   set commit_cnt {}
        !            58:   proc commit_hook {} {
        !            59:     set ::commit_cnt [execsql {SELECT * FROM t2}]
        !            60:     return 0
        !            61:   }
        !            62:   execsql {
        !            63:     INSERT INTO t2 VALUES(5,6);
        !            64:   }
        !            65:   set commit_cnt
        !            66: } {1 2 2 3 3 4 4 5 5 6}
        !            67: do_test hook-3.6 {
        !            68:   set commit_cnt {}
        !            69:   proc commit_hook {} {
        !            70:     set ::commit_cnt [execsql {SELECT * FROM t2}] 
        !            71:     return 1
        !            72:   }
        !            73:   catchsql {
        !            74:     INSERT INTO t2 VALUES(6,7);
        !            75:   }
        !            76: } {1 {constraint failed}}
        !            77: do_test hook-3.7 {
        !            78:   set ::commit_cnt
        !            79: } {1 2 2 3 3 4 4 5 5 6 6 7}
        !            80: do_test hook-3.8 {
        !            81:   execsql {SELECT * FROM t2}
        !            82: } {1 2 2 3 3 4 4 5 5 6}
        !            83: 
        !            84: # Test turnning off the commit hook
        !            85: #
        !            86: do_test hook-3.9 {
        !            87:   db commit_hook {}
        !            88:   set ::commit_cnt {}
        !            89:   execsql {
        !            90:     INSERT INTO t2 VALUES(7,8);
        !            91:   }
        !            92:   set ::commit_cnt
        !            93: } {}
        !            94: 
        !            95: # Ticket #3564.
        !            96: #
        !            97: do_test hook-3.10 {
        !            98:   forcedelete test2.db test2.db-journal
        !            99:   sqlite3 db2 test2.db
        !           100:   proc commit_hook {} {
        !           101:     set y [db2 one {SELECT y FROM t3 WHERE y>10}]
        !           102:     return [expr {$y>10}]
        !           103:   }
        !           104:   db2 eval {CREATE TABLE t3(x,y)}
        !           105:   db2 commit_hook commit_hook
        !           106:   catchsql {INSERT INTO t3 VALUES(1,2)} db2
        !           107:   catchsql {INSERT INTO t3 VALUES(11,12)} db2
        !           108:   catchsql {INSERT INTO t3 VALUES(3,4)} db2
        !           109:   db2 eval {
        !           110:     SELECT * FROM t3 ORDER BY x;
        !           111:   }
        !           112: } {1 2 3 4}
        !           113: db2 close
        !           114: 
        !           115: 
        !           116: #----------------------------------------------------------------------------
        !           117: # Tests for the update-hook.
        !           118: #
        !           119: # 4.1.* - Very simple tests. Test that the update hook is invoked correctly 
        !           120: #         for INSERT, DELETE and UPDATE statements, including DELETE 
        !           121: #         statements with no WHERE clause.
        !           122: # 4.2.* - Check that the update-hook is invoked for rows modified by trigger
        !           123: #         bodies. Also that the database name is correctly reported when 
        !           124: #         an attached database is modified.
        !           125: # 4.3.* - Do some sorting, grouping, compound queries, population and 
        !           126: #         depopulation of indices, to make sure the update-hook is not 
        !           127: #         invoked incorrectly.
        !           128: #
        !           129: 
        !           130: # Simple tests
        !           131: do_test hook-4.1.1 {
        !           132:   catchsql {
        !           133:     DROP TABLE t1;
        !           134:   }
        !           135:   execsql {
        !           136:     CREATE TABLE t1(a INTEGER PRIMARY KEY, b);
        !           137:     INSERT INTO t1 VALUES(1, 'one');
        !           138:     INSERT INTO t1 VALUES(2, 'two');
        !           139:     INSERT INTO t1 VALUES(3, 'three');
        !           140:   }
        !           141:   db update_hook [list lappend ::update_hook]
        !           142: } {}
        !           143: do_test hook-4.1.2 {
        !           144:   execsql {
        !           145:     INSERT INTO t1 VALUES(4, 'four');
        !           146:     DELETE FROM t1 WHERE b = 'two';
        !           147:     UPDATE t1 SET b = '' WHERE a = 1 OR a = 3;
        !           148:     DELETE FROM t1 WHERE 1; -- Avoid the truncate optimization (for now)
        !           149:   }
        !           150:   set ::update_hook
        !           151: } [list \
        !           152:     INSERT main t1 4 \
        !           153:     DELETE main t1 2 \
        !           154:     UPDATE main t1 1 \
        !           155:     UPDATE main t1 3 \
        !           156:     DELETE main t1 1 \
        !           157:     DELETE main t1 3 \
        !           158:     DELETE main t1 4 \
        !           159: ]
        !           160: 
        !           161: ifcapable trigger {
        !           162:   # Update hook is not invoked for changes to sqlite_master
        !           163:   #
        !           164:   do_test hook-4.1.3 {
        !           165:     set ::update_hook {}
        !           166:     execsql {
        !           167:       CREATE TRIGGER r1 AFTER INSERT ON t1 BEGIN SELECT RAISE(IGNORE); END;
        !           168:     }
        !           169:     set ::update_hook
        !           170:   } {}
        !           171:   do_test hook-4.1.4 {
        !           172:     set ::update_hook {}
        !           173:     execsql {
        !           174:       DROP TRIGGER r1;
        !           175:     }
        !           176:     set ::update_hook
        !           177:   } {}
        !           178:   
        !           179:   set ::update_hook {}
        !           180:   do_test hook-4.2.1 {
        !           181:     catchsql {
        !           182:       DROP TABLE t2;
        !           183:     }
        !           184:     execsql {
        !           185:       CREATE TABLE t2(c INTEGER PRIMARY KEY, d);
        !           186:       CREATE TRIGGER t1_trigger AFTER INSERT ON t1 BEGIN
        !           187:         INSERT INTO t2 VALUES(new.a, new.b);
        !           188:         UPDATE t2 SET d = d || ' via trigger' WHERE new.a = c;
        !           189:         DELETE FROM t2 WHERE new.a = c;
        !           190:       END;
        !           191:     }
        !           192:   } {}
        !           193:   do_test hook-4.2.2 {
        !           194:     execsql {
        !           195:       INSERT INTO t1 VALUES(1, 'one');
        !           196:       INSERT INTO t1 VALUES(2, 'two');
        !           197:     }
        !           198:     set ::update_hook
        !           199:   } [list \
        !           200:       INSERT main t1 1 \
        !           201:       INSERT main t2 1 \
        !           202:       UPDATE main t2 1 \
        !           203:       DELETE main t2 1 \
        !           204:       INSERT main t1 2 \
        !           205:       INSERT main t2 2 \
        !           206:       UPDATE main t2 2 \
        !           207:       DELETE main t2 2 \
        !           208:   ]
        !           209: } else {
        !           210:   execsql {
        !           211:     INSERT INTO t1 VALUES(1, 'one');
        !           212:     INSERT INTO t1 VALUES(2, 'two');
        !           213:   }
        !           214: }
        !           215: 
        !           216: # Update-hook + ATTACH
        !           217: set ::update_hook {}
        !           218: ifcapable attach {
        !           219:   do_test hook-4.2.3 {
        !           220:     forcedelete test2.db
        !           221:     execsql {
        !           222:       ATTACH 'test2.db' AS aux;
        !           223:       CREATE TABLE aux.t3(a INTEGER PRIMARY KEY, b);
        !           224:       INSERT INTO aux.t3 SELECT * FROM t1;
        !           225:       UPDATE t3 SET b = 'two or so' WHERE a = 2;
        !           226:       DELETE FROM t3 WHERE 1; -- Avoid the truncate optimization (for now)
        !           227:     }
        !           228:     set ::update_hook
        !           229:   } [list \
        !           230:       INSERT aux t3 1 \
        !           231:       INSERT aux t3 2 \
        !           232:       UPDATE aux t3 2 \
        !           233:       DELETE aux t3 1 \
        !           234:       DELETE aux t3 2 \
        !           235:   ]
        !           236: }
        !           237: 
        !           238: ifcapable trigger {
        !           239:   execsql {
        !           240:     DROP TRIGGER t1_trigger;
        !           241:   }
        !           242: }
        !           243: 
        !           244: # Test that other vdbe operations involving btree structures do not 
        !           245: # incorrectly invoke the update-hook.
        !           246: set ::update_hook {}
        !           247: do_test hook-4.3.1 {
        !           248:   execsql {
        !           249:     CREATE INDEX t1_i ON t1(b);
        !           250:     INSERT INTO t1 VALUES(3, 'three');
        !           251:     UPDATE t1 SET b = '';
        !           252:     DELETE FROM t1 WHERE a > 1;
        !           253:   }
        !           254:   set ::update_hook
        !           255: } [list \
        !           256:     INSERT main t1 3 \
        !           257:     UPDATE main t1 1 \
        !           258:     UPDATE main t1 2 \
        !           259:     UPDATE main t1 3 \
        !           260:     DELETE main t1 2 \
        !           261:     DELETE main t1 3 \
        !           262: ]
        !           263: set ::update_hook {}
        !           264: ifcapable compound&&attach {
        !           265:   do_test hook-4.3.2 {
        !           266:     execsql {
        !           267:       SELECT * FROM t1 UNION SELECT * FROM t3;
        !           268:       SELECT * FROM t1 UNION ALL SELECT * FROM t3;
        !           269:       SELECT * FROM t1 INTERSECT SELECT * FROM t3;
        !           270:       SELECT * FROM t1 EXCEPT SELECT * FROM t3;
        !           271:       SELECT * FROM t1 ORDER BY b;
        !           272:       SELECT * FROM t1 GROUP BY b;
        !           273:     }
        !           274:     set ::update_hook
        !           275:   } [list]
        !           276: }
        !           277: 
        !           278: do_test hook-4.4 {
        !           279:   execsql {
        !           280:     CREATE TABLE t4(a UNIQUE, b);
        !           281:     INSERT INTO t4 VALUES(1, 'a');
        !           282:     INSERT INTO t4 VALUES(2, 'b');
        !           283:   }
        !           284:   set ::update_hook [list]
        !           285:   execsql {
        !           286:     REPLACE INTO t4 VALUES(1, 'c');
        !           287:   }
        !           288:   set ::update_hook
        !           289: } [list INSERT main t4 3 ]
        !           290: do_execsql_test hook-4.4.1 {
        !           291:   SELECT * FROM t4 ORDER BY a;
        !           292: } {1 c 2 b}
        !           293: do_test hook-4.4.2 {
        !           294:   set ::update_hook [list]
        !           295:   execsql {
        !           296:     PRAGMA recursive_triggers = on;
        !           297:     REPLACE INTO t4 VALUES(1, 'd');
        !           298:   }
        !           299:   set ::update_hook
        !           300: } [list INSERT main t4 4 ]
        !           301: do_execsql_test hook-4.4.3 {
        !           302:   SELECT * FROM t4 ORDER BY a;
        !           303: } {1 d 2 b}
        !           304: 
        !           305: db update_hook {}
        !           306: #
        !           307: #----------------------------------------------------------------------------
        !           308: 
        !           309: #----------------------------------------------------------------------------
        !           310: # Test the rollback-hook. The rollback-hook is a bit more complicated than
        !           311: # either the commit or update hooks because a rollback can happen 
        !           312: # explicitly (an sql ROLLBACK statement) or implicitly (a constraint or 
        !           313: # error condition).
        !           314: #
        !           315: # hook-5.1.* - Test explicit rollbacks.
        !           316: # hook-5.2.* - Test implicit rollbacks caused by constraint failure.
        !           317: #
        !           318: # hook-5.3.* - Test implicit rollbacks caused by IO errors.
        !           319: # hook-5.4.* - Test implicit rollbacks caused by malloc() failure.
        !           320: # hook-5.5.* - Test hot-journal rollbacks. Or should the rollback hook 
        !           321: #              not be called for these?
        !           322: #
        !           323: 
        !           324: do_test hook-5.0 {
        !           325:   # Configure the rollback hook to increment global variable 
        !           326:   # $::rollback_hook each time it is invoked.
        !           327:   set ::rollback_hook 0
        !           328:   db rollback_hook [list incr ::rollback_hook]
        !           329: } {}
        !           330: 
        !           331: # Test explicit rollbacks. Not much can really go wrong here.
        !           332: #
        !           333: do_test hook-5.1.1 {
        !           334:   set ::rollback_hook 0
        !           335:   execsql {
        !           336:     BEGIN;
        !           337:     ROLLBACK;
        !           338:   }
        !           339:   set ::rollback_hook
        !           340: } {1}
        !           341: 
        !           342: # Test implicit rollbacks caused by constraints.
        !           343: #
        !           344: do_test hook-5.2.1 {
        !           345:   set ::rollback_hook 0
        !           346:   catchsql {
        !           347:     DROP TABLE t1;
        !           348:     CREATE TABLE t1(a PRIMARY KEY, b);
        !           349:     INSERT INTO t1 VALUES('one', 'I');
        !           350:     INSERT INTO t1 VALUES('one', 'I');
        !           351:   }
        !           352:   set ::rollback_hook
        !           353: } {1}
        !           354: do_test hook-5.2.2 {
        !           355:   # Check that the INSERT transaction above really was rolled back.
        !           356:   execsql {
        !           357:     SELECT count(*) FROM t1;
        !           358:   }
        !           359: } {1}
        !           360: 
        !           361: #
        !           362: # End rollback-hook testing.
        !           363: #----------------------------------------------------------------------------
        !           364: 
        !           365: #----------------------------------------------------------------------------
        !           366: # Test that if a commit-hook returns non-zero (causing a rollback), the
        !           367: # rollback-hook is invoked.
        !           368: #
        !           369: proc commit_hook {} {
        !           370:   lappend ::hooks COMMIT
        !           371:   return 1
        !           372: }
        !           373: proc rollback_hook {} {
        !           374:   lappend ::hooks ROLLBACK
        !           375: }
        !           376: do_test hook-6.1 {
        !           377:   set ::hooks [list]
        !           378:   db commit_hook commit_hook
        !           379:   db rollback_hook rollback_hook
        !           380:   catchsql {
        !           381:     BEGIN;
        !           382:       INSERT INTO t1 VALUES('two', 'II');
        !           383:     COMMIT;
        !           384:   }
        !           385:   execsql { SELECT * FROM t1 }
        !           386: } {one I}
        !           387: do_test hook-6.2 {
        !           388:   set ::hooks
        !           389: } {COMMIT ROLLBACK}
        !           390: unset ::hooks
        !           391: 
        !           392: finish_test

FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>