# 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