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>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to hook.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