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.13 2008/01/19 20:11:26 drh 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 #----------------------------------------------------------------------------

    96 # Tests for the update-hook.

    97 #

    98 # 4.1.* - Very simple tests. Test that the update hook is invoked correctly 

    99 #         for INSERT, DELETE and UPDATE statements, including DELETE 

   100 #         statements with no WHERE clause.

   101 # 4.2.* - Check that the update-hook is invoked for rows modified by trigger

   102 #         bodies. Also that the database name is correctly reported when 

   103 #         an attached database is modified.

   104 # 4.3.* - Do some sorting, grouping, compound queries, population and 

   105 #         depopulation of indices, to make sure the update-hook is not 

   106 #         invoked incorrectly.

   107 #

   108 

   109 # Simple tests

   110 do_test hook-4.1.1 {

   111   catchsql {

   112     DROP TABLE t1;

   113   }

   114   execsql {

   115     CREATE TABLE t1(a INTEGER PRIMARY KEY, b);

   116     INSERT INTO t1 VALUES(1, 'one');

   117     INSERT INTO t1 VALUES(2, 'two');

   118     INSERT INTO t1 VALUES(3, 'three');

   119   }

   120   db update_hook [list lappend ::update_hook]

   121 } {}

   122 do_test hook-4.1.2 {

   123   execsql {

   124     INSERT INTO t1 VALUES(4, 'four');

   125     DELETE FROM t1 WHERE b = 'two';

   126     UPDATE t1 SET b = '' WHERE a = 1 OR a = 3;

   127     DELETE FROM t1 WHERE 1; -- Avoid the truncate optimization (for now)

   128   }

   129   set ::update_hook

   130 } [list \

   131     INSERT main t1 4 \

   132     DELETE main t1 2 \

   133     UPDATE main t1 1 \

   134     UPDATE main t1 3 \

   135     DELETE main t1 1 \

   136     DELETE main t1 3 \

   137     DELETE main t1 4 \

   138 ]

   139 

   140 # Update hook is not invoked for changes to sqlite_master

   141 #

   142 do_test hook-4.1.3 {

   143   set ::update_hook {}

   144   execsql {

   145     CREATE TRIGGER r1 AFTER INSERT ON t1 BEGIN SELECT RAISE(IGNORE); END;

   146   }

   147   set ::update_hook

   148 } {}

   149 do_test hook-4.1.4 {

   150   set ::update_hook {}

   151   execsql {

   152     DROP TRIGGER r1;

   153   }

   154   set ::update_hook

   155 } {}

   156 

   157 

   158 set ::update_hook {}

   159 ifcapable trigger {

   160   do_test hook-4.2.1 {

   161     catchsql {

   162       DROP TABLE t2;

   163     }

   164     execsql {

   165       CREATE TABLE t2(c INTEGER PRIMARY KEY, d);

   166       CREATE TRIGGER t1_trigger AFTER INSERT ON t1 BEGIN

   167         INSERT INTO t2 VALUES(new.a, new.b);

   168         UPDATE t2 SET d = d || ' via trigger' WHERE new.a = c;

   169         DELETE FROM t2 WHERE new.a = c;

   170       END;

   171     }

   172   } {}

   173   do_test hook-4.2.2 {

   174     execsql {

   175       INSERT INTO t1 VALUES(1, 'one');

   176       INSERT INTO t1 VALUES(2, 'two');

   177     }

   178     set ::update_hook

   179   } [list \

   180       INSERT main t1 1 \

   181       INSERT main t2 1 \

   182       UPDATE main t2 1 \

   183       DELETE main t2 1 \

   184       INSERT main t1 2 \

   185       INSERT main t2 2 \

   186       UPDATE main t2 2 \

   187       DELETE main t2 2 \

   188   ]

   189 } else {

   190   execsql {

   191     INSERT INTO t1 VALUES(1, 'one');

   192     INSERT INTO t1 VALUES(2, 'two');

   193   }

   194 }

   195 

   196 # Update-hook + ATTACH

   197 set ::update_hook {}

   198 ifcapable attach {

   199   do_test hook-4.2.3 {

   200     file delete -force test2.db

   201     execsql {

   202       ATTACH 'test2.db' AS aux;

   203       CREATE TABLE aux.t3(a INTEGER PRIMARY KEY, b);

   204       INSERT INTO aux.t3 SELECT * FROM t1;

   205       UPDATE t3 SET b = 'two or so' WHERE a = 2;

   206       DELETE FROM t3 WHERE 1; -- Avoid the truncate optimization (for now)

   207     }

   208     set ::update_hook

   209   } [list \

   210       INSERT aux t3 1 \

   211       INSERT aux t3 2 \

   212       UPDATE aux t3 2 \

   213       DELETE aux t3 1 \

   214       DELETE aux t3 2 \

   215   ]

   216 }

   217 

   218 ifcapable trigger {

   219   execsql {

   220     DROP TRIGGER t1_trigger;

   221   }

   222 }

   223 

   224 # Test that other vdbe operations involving btree structures do not 

   225 # incorrectly invoke the update-hook.

   226 set ::update_hook {}

   227 do_test hook-4.3.1 {

   228   execsql {

   229     CREATE INDEX t1_i ON t1(b);

   230     INSERT INTO t1 VALUES(3, 'three');

   231     UPDATE t1 SET b = '';

   232     DELETE FROM t1 WHERE a > 1;

   233   }

   234   set ::update_hook

   235 } [list \

   236     INSERT main t1 3 \

   237     UPDATE main t1 1 \

   238     UPDATE main t1 2 \

   239     UPDATE main t1 3 \

   240     DELETE main t1 2 \

   241     DELETE main t1 3 \

   242 ]

   243 set ::update_hook {}

   244 ifcapable compound&&attach {

   245   do_test hook-4.3.2 {

   246     execsql {

   247       SELECT * FROM t1 UNION SELECT * FROM t3;

   248       SELECT * FROM t1 UNION ALL SELECT * FROM t3;

   249       SELECT * FROM t1 INTERSECT SELECT * FROM t3;

   250       SELECT * FROM t1 EXCEPT SELECT * FROM t3;

   251       SELECT * FROM t1 ORDER BY b;

   252       SELECT * FROM t1 GROUP BY b;

   253     }

   254     set ::update_hook

   255   } [list]

   256 }

   257 db update_hook {}

   258 #

   259 #----------------------------------------------------------------------------

   260 

   261 #----------------------------------------------------------------------------

   262 # Test the rollback-hook. The rollback-hook is a bit more complicated than

   263 # either the commit or update hooks because a rollback can happen 

   264 # explicitly (an sql ROLLBACK statement) or implicitly (a constraint or 

   265 # error condition).

   266 #

   267 # hook-5.1.* - Test explicit rollbacks.

   268 # hook-5.2.* - Test implicit rollbacks caused by constraint failure.

   269 #

   270 # hook-5.3.* - Test implicit rollbacks caused by IO errors.

   271 # hook-5.4.* - Test implicit rollbacks caused by malloc() failure.

   272 # hook-5.5.* - Test hot-journal rollbacks. Or should the rollback hook 

   273 #              not be called for these?

   274 #

   275 

   276 do_test hook-5.0 {

   277   # Configure the rollback hook to increment global variable 

   278   # $::rollback_hook each time it is invoked.

   279   set ::rollback_hook 0

   280   db rollback_hook [list incr ::rollback_hook]

   281 } {}

   282 

   283 # Test explicit rollbacks. Not much can really go wrong here.

   284 #

   285 do_test hook-5.1.1 {

   286   set ::rollback_hook 0

   287   execsql {

   288     BEGIN;

   289     ROLLBACK;

   290   }

   291   set ::rollback_hook

   292 } {1}

   293 

   294 # Test implicit rollbacks caused by constraints.

   295 #

   296 do_test hook-5.2.1 {

   297   set ::rollback_hook 0

   298   catchsql {

   299     DROP TABLE t1;

   300     CREATE TABLE t1(a PRIMARY KEY, b);

   301     INSERT INTO t1 VALUES('one', 'I');

   302     INSERT INTO t1 VALUES('one', 'I');

   303   }

   304   set ::rollback_hook

   305 } {1}

   306 do_test hook-5.2.2 {

   307   # Check that the INSERT transaction above really was rolled back.

   308   execsql {

   309     SELECT count(*) FROM t1;

   310   }

   311 } {1}

   312 

   313 #

   314 # End rollback-hook testing.

   315 #----------------------------------------------------------------------------

   316 

   317 finish_test