# 2008 January 1

 #

 # The author disclaims copyright to this source code.  In place of

 # a legal notice, here is a blessing:

 #

 #    May you do good and not evil.

 #    May you find forgiveness for yourself and forgive others.

 #    May you share freely, never taking more than you give.

 #

 #***********************************************************************

 # This file implements regression tests for SQLite library. Specifically,

 # it tests some compiler optimizations for SQL statements featuring

 # triggers:

 #

 #

 #

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

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

 #

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

 #                  statements, unused fields are populated with NULL values. 

 #

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

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

 #                  fields.

 #

 set testdir [file dirname $argv0]

 source $testdir/tester.tcl

 ifcapable {!trigger} {

   finish_test

   return

 }

 proc has_rowdata {sql} {

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

 }

 do_test trigger9-1.1 {

   execsql {

     PRAGMA page_size = 1024;

     CREATE TABLE t1(x, y, z);

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

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

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

     CREATE TABLE t2(x);

   }

 } {}

 do_test trigger9-1.2.1 {

   execsql {

     BEGIN;

       CREATE TRIGGER trig1 BEFORE DELETE ON t1 BEGIN

         INSERT INTO t2 VALUES(old.rowid);

       END;

       DELETE FROM t1;

       SELECT * FROM t2;

   }

 } {1 2 3}

 do_test trigger9-1.2.3 {

   has_rowdata {DELETE FROM t1}

 } 0

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

 do_test trigger9-1.3.1 {

   execsql {

     BEGIN;

       CREATE TRIGGER trig1 BEFORE DELETE ON t1 BEGIN

         INSERT INTO t2 VALUES(old.x);

       END;

       DELETE FROM t1;

       SELECT * FROM t2;

   }

 } {1 2 3}

 do_test trigger9-1.3.2 {

   has_rowdata {DELETE FROM t1}

 } 1

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

 do_test trigger9-1.4.1 {

   execsql {

     BEGIN;

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

         INSERT INTO t2 VALUES(old.rowid);

       END;

       DELETE FROM t1;

       SELECT * FROM t2;

   }

 } {1}

 do_test trigger9-1.4.2 {

   has_rowdata {DELETE FROM t1}

 } 1

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

 do_test trigger9-1.5.1 {

   execsql {

     BEGIN;

       CREATE TRIGGER trig1 BEFORE UPDATE ON t1 BEGIN

         INSERT INTO t2 VALUES(old.rowid);

       END;

       UPDATE t1 SET y = '';

       SELECT * FROM t2;

   }

 } {1 2 3}

 do_test trigger9-1.5.2 {

   has_rowdata {UPDATE t1 SET y = ''}

 } 0

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

 do_test trigger9-1.6.1 {

   execsql {

     BEGIN;

       CREATE TRIGGER trig1 BEFORE UPDATE ON t1 BEGIN

         INSERT INTO t2 VALUES(old.x);

       END;

       UPDATE t1 SET y = '';

       SELECT * FROM t2;

   }

 } {1 2 3}

 do_test trigger9-1.6.2 {

   has_rowdata {UPDATE t1 SET y = ''}

 } 1

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

 do_test trigger9-1.7.1 {

   execsql {

     BEGIN;

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

         INSERT INTO t2 VALUES(old.x);

       END;

       UPDATE t1 SET y = '';

       SELECT * FROM t2;

   }

 } {2 3}

 do_test trigger9-1.7.2 {

   has_rowdata {UPDATE t1 SET y = ''}

 } 1

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

 do_test trigger9-3.1 {

   execsql {

     CREATE TABLE t3(a, b);

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

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

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

   }

 } {}

 do_test trigger9-3.2 {

   execsql {

     BEGIN;

       CREATE VIEW v1 AS SELECT * FROM t3;

       CREATE TRIGGER trig1 INSTEAD OF UPDATE ON v1 BEGIN

         INSERT INTO t2 VALUES(old.a);

       END;

       UPDATE v1 SET b = 'hello';

       SELECT * FROM t2;

     ROLLBACK;

   }

 } {1 2 3}

 do_test trigger9-3.3 {

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

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

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

   # a problem.

   #

   execsql {

     BEGIN;

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

       CREATE TRIGGER trig1 INSTEAD OF UPDATE ON v1 BEGIN

         INSERT INTO t2 VALUES(old.a);

       END;

       UPDATE v1 SET c = 'hello';

       SELECT * FROM t2;

     ROLLBACK;

   }

 } {2 3}

 do_test trigger9-3.4 {

   execsql {

     BEGIN;

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

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

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

       CREATE TRIGGER trig1 INSTEAD OF UPDATE ON v1 BEGIN

         INSERT INTO t2 VALUES(old.a);

       END;

       UPDATE v1 SET b = 'hello';

       SELECT * FROM t2;

     ROLLBACK;

   }

 } {1 2 3 3}

 ifcapable compound {

   do_test trigger9-3.5 {

     execsql {

       BEGIN;

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

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

         CREATE TRIGGER trig1 INSTEAD OF UPDATE ON v1 BEGIN

           INSERT INTO t2 VALUES(old.a);

         END;

         UPDATE v1 SET b = 'hello';

         SELECT * FROM t2;

       ROLLBACK;

     }

   } {1 2 3}

   do_test trigger9-3.6 {

     execsql {

       BEGIN;

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

         CREATE VIEW v1 AS 

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

         CREATE TRIGGER trig1 INSTEAD OF UPDATE ON v1 BEGIN

           INSERT INTO t2 VALUES(old.a);

         END;

         UPDATE v1 SET b = 'hello';

         SELECT * FROM t2;

       ROLLBACK;

     }

   } {2}

 }

 finish_test