1 # 2008 January 1

     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 SQLite library. Specifically,

    12 # it tests some compiler optimizations for SQL statements featuring

    13 # triggers:

    14 #

    15 #

    16 #

    17 

    18 # trigger9-1.* -   Test that if there are no references to OLD.* cols, or a

    19 #                  reference to only OLD.rowid, the data is not loaded.

    20 #

    21 # trigger9-2.* -   Test that for NEW.* records populated by UPDATE 

    22 #                  statements, unused fields are populated with NULL values. 

    23 #

    24 # trigger9-3.* -   Test that the temporary tables used for OLD.* references

    25 #                  in "INSTEAD OF" triggers have NULL values in unused 

    26 #                  fields.

    27 #

    28 

    29 set testdir [file dirname $argv0]

    30 source $testdir/tester.tcl

    31 ifcapable {!trigger} {

    32   finish_test

    33   return

    34 }

    35 

    36 proc has_rowdata {sql} {

    37   expr {[lsearch [execsql "explain $sql"] RowData]>=0}

    38 }

    39 

    40 do_test trigger9-1.1 {

    41   execsql {

    42     PRAGMA page_size = 1024;

    43     CREATE TABLE t1(x, y, z);

    44     INSERT INTO t1 VALUES('1', randstr(10000,10000), '2');

    45     INSERT INTO t1 VALUES('2', randstr(10000,10000), '4');

    46     INSERT INTO t1 VALUES('3', randstr(10000,10000), '6');

    47     CREATE TABLE t2(x);

    48   }

    49 } {}

    50 

    51 do_test trigger9-1.2.1 {

    52   execsql {

    53     BEGIN;

    54       CREATE TRIGGER trig1 BEFORE DELETE ON t1 BEGIN

    55         INSERT INTO t2 VALUES(old.rowid);

    56       END;

    57       DELETE FROM t1;

    58       SELECT * FROM t2;

    59   }

    60 } {1 2 3}

    61 do_test trigger9-1.2.3 {

    62   has_rowdata {DELETE FROM t1}

    63 } 0

    64 do_test trigger9-1.2.4 { execsql { ROLLBACK } } {}

    65 

    66 do_test trigger9-1.3.1 {

    67   execsql {

    68     BEGIN;

    69       CREATE TRIGGER trig1 BEFORE DELETE ON t1 BEGIN

    70         INSERT INTO t2 VALUES(old.x);

    71       END;

    72       DELETE FROM t1;

    73       SELECT * FROM t2;

    74   }

    75 } {1 2 3}

    76 do_test trigger9-1.3.2 {

    77   has_rowdata {DELETE FROM t1}

    78 } 1

    79 do_test trigger9-1.3.3 { execsql { ROLLBACK } } {}

    80 

    81 do_test trigger9-1.4.1 {

    82   execsql {

    83     BEGIN;

    84       CREATE TRIGGER trig1 BEFORE DELETE ON t1 WHEN old.x='1' BEGIN

    85         INSERT INTO t2 VALUES(old.rowid);

    86       END;

    87       DELETE FROM t1;

    88       SELECT * FROM t2;

    89   }

    90 } {1}

    91 do_test trigger9-1.4.2 {

    92   has_rowdata {DELETE FROM t1}

    93 } 1

    94 do_test trigger9-1.4.3 { execsql { ROLLBACK } } {}

    95 

    96 do_test trigger9-1.5.1 {

    97   execsql {

    98     BEGIN;

    99       CREATE TRIGGER trig1 BEFORE UPDATE ON t1 BEGIN

   100         INSERT INTO t2 VALUES(old.rowid);

   101       END;

   102       UPDATE t1 SET y = '';

   103       SELECT * FROM t2;

   104   }

   105 } {1 2 3}

   106 do_test trigger9-1.5.2 {

   107   has_rowdata {UPDATE t1 SET y = ''}

   108 } 0

   109 do_test trigger9-1.5.3 { execsql { ROLLBACK } } {}

   110 

   111 do_test trigger9-1.6.1 {

   112   execsql {

   113     BEGIN;

   114       CREATE TRIGGER trig1 BEFORE UPDATE ON t1 BEGIN

   115         INSERT INTO t2 VALUES(old.x);

   116       END;

   117       UPDATE t1 SET y = '';

   118       SELECT * FROM t2;

   119   }

   120 } {1 2 3}

   121 do_test trigger9-1.6.2 {

   122   has_rowdata {UPDATE t1 SET y = ''}

   123 } 1

   124 do_test trigger9-1.6.3 { execsql { ROLLBACK } } {}

   125 

   126 do_test trigger9-1.7.1 {

   127   execsql {

   128     BEGIN;

   129       CREATE TRIGGER trig1 BEFORE UPDATE ON t1 WHEN old.x>='2' BEGIN

   130         INSERT INTO t2 VALUES(old.x);

   131       END;

   132       UPDATE t1 SET y = '';

   133       SELECT * FROM t2;

   134   }

   135 } {2 3}

   136 do_test trigger9-1.7.2 {

   137   has_rowdata {UPDATE t1 SET y = ''}

   138 } 1

   139 do_test trigger9-1.7.3 { execsql { ROLLBACK } } {}

   140 

   141 do_test trigger9-3.1 {

   142   execsql {

   143     CREATE TABLE t3(a, b);

   144     INSERT INTO t3 VALUES(1, 'one');

   145     INSERT INTO t3 VALUES(2, 'two');

   146     INSERT INTO t3 VALUES(3, 'three');

   147   }

   148 } {}

   149 do_test trigger9-3.2 {

   150   execsql {

   151     BEGIN;

   152       CREATE VIEW v1 AS SELECT * FROM t3;

   153       CREATE TRIGGER trig1 INSTEAD OF UPDATE ON v1 BEGIN

   154         INSERT INTO t2 VALUES(old.a);

   155       END;

   156       UPDATE v1 SET b = 'hello';

   157       SELECT * FROM t2;

   158     ROLLBACK;

   159   }

   160 } {1 2 3}

   161 do_test trigger9-3.3 {

   162   # In this test the 'c' column of the view is not required by

   163   # the INSTEAD OF trigger, but the expression is reused internally as

   164   # part of the view's WHERE clause. Check that this does not cause

   165   # a problem.

   166   #

   167   execsql {

   168     BEGIN;

   169       CREATE VIEW v1 AS SELECT a, b AS c FROM t3 WHERE c > 'one';

   170       CREATE TRIGGER trig1 INSTEAD OF UPDATE ON v1 BEGIN

   171         INSERT INTO t2 VALUES(old.a);

   172       END;

   173       UPDATE v1 SET c = 'hello';

   174       SELECT * FROM t2;

   175     ROLLBACK;

   176   }

   177 } {2 3}

   178 do_test trigger9-3.4 {

   179   execsql {

   180     BEGIN;

   181       INSERT INTO t3 VALUES(3, 'three');

   182       INSERT INTO t3 VALUES(3, 'four');

   183       CREATE VIEW v1 AS SELECT DISTINCT a, b FROM t3;

   184       CREATE TRIGGER trig1 INSTEAD OF UPDATE ON v1 BEGIN

   185         INSERT INTO t2 VALUES(old.a);

   186       END;

   187       UPDATE v1 SET b = 'hello';

   188       SELECT * FROM t2;

   189     ROLLBACK;

   190   }

   191 } {1 2 3 3}

   192 

   193 ifcapable compound {

   194   do_test trigger9-3.5 {

   195     execsql {

   196       BEGIN;

   197         INSERT INTO t3 VALUES(1, 'uno');

   198         CREATE VIEW v1 AS SELECT a, b FROM t3 EXCEPT SELECT 1, 'one';

   199         CREATE TRIGGER trig1 INSTEAD OF UPDATE ON v1 BEGIN

   200           INSERT INTO t2 VALUES(old.a);

   201         END;

   202         UPDATE v1 SET b = 'hello';

   203         SELECT * FROM t2;

   204       ROLLBACK;

   205     }

   206   } {1 2 3}

   207   do_test trigger9-3.6 {

   208     execsql {

   209       BEGIN;

   210         INSERT INTO t3 VALUES(1, 'zero');

   211         CREATE VIEW v1 AS 

   212           SELECT sum(a) AS a, max(b) AS b FROM t3 GROUP BY t3.a HAVING b>'two';

   213         CREATE TRIGGER trig1 INSTEAD OF UPDATE ON v1 BEGIN

   214           INSERT INTO t2 VALUES(old.a);

   215         END;

   216         UPDATE v1 SET b = 'hello';

   217         SELECT * FROM t2;

   218       ROLLBACK;

   219     }

   220   } {2}

   221 }

   222 

   223 finish_test