# 2005 January 13

 #

 # 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.  The

 # focus of this file is testing corner cases of the INSERT statement.

 #

 # $Id: insert3.test,v 1.7 2007/09/12 17:01:45 danielk1977 Exp $

 set testdir [file dirname $argv0]

 source $testdir/tester.tcl

 # All the tests in this file require trigger support

 #

 ifcapable {trigger} {

 # Create a table and a corresponding insert trigger.  Do a self-insert

 # into the table.

 #

 do_test insert3-1.0 {

   execsql {

     CREATE TABLE t1(a,b);

     CREATE TABLE log(x UNIQUE, y);

     CREATE TRIGGER r1 AFTER INSERT ON t1 BEGIN

       UPDATE log SET y=y+1 WHERE x=new.a;

       INSERT OR IGNORE INTO log VALUES(new.a, 1);

     END;

     INSERT INTO t1 VALUES('hello','world');

     INSERT INTO t1 VALUES(5,10);

     SELECT * FROM log ORDER BY x;

   }

 } {5 1 hello 1}

 do_test insert3-1.1 {

   execsql {

     INSERT INTO t1 SELECT a, b+10 FROM t1;

     SELECT * FROM log ORDER BY x;

   }

 } {5 2 hello 2}

 do_test insert3-1.2 {

   execsql {

     CREATE TABLE log2(x PRIMARY KEY,y);

     CREATE TRIGGER r2 BEFORE INSERT ON t1 BEGIN

       UPDATE log2 SET y=y+1 WHERE x=new.b;

       INSERT OR IGNORE INTO log2 VALUES(new.b,1);

     END;

     INSERT INTO t1 VALUES(453,'hi');

     SELECT * FROM log ORDER BY x;

   }

 } {5 2 453 1 hello 2}

 do_test insert3-1.3 {

   execsql {

     SELECT * FROM log2 ORDER BY x;

   }

 } {hi 1}

 ifcapable compound {

   do_test insert3-1.4.1 {

     execsql {

       INSERT INTO t1 SELECT * FROM t1;

       SELECT 'a:', x, y FROM log UNION ALL 

           SELECT 'b:', x, y FROM log2 ORDER BY x;

     }

   } {a: 5 4 b: 10 2 b: 20 1 a: 453 2 a: hello 4 b: hi 2 b: world 1}

   do_test insert3-1.4.2 {

     execsql {

       SELECT 'a:', x, y FROM log UNION ALL 

           SELECT 'b:', x, y FROM log2 ORDER BY x, y;

     }

   } {a: 5 4 b: 10 2 b: 20 1 a: 453 2 a: hello 4 b: hi 2 b: world 1}

   do_test insert3-1.5 {

     execsql {

       INSERT INTO t1(a) VALUES('xyz');

       SELECT * FROM log ORDER BY x;

     }

   } {5 4 453 2 hello 4 xyz 1}

 }

 do_test insert3-2.1 {

   execsql {

     CREATE TABLE t2(

       a INTEGER PRIMARY KEY,

       b DEFAULT 'b',

       c DEFAULT 'c'

     );

     CREATE TABLE t2dup(a,b,c);

     CREATE TRIGGER t2r1 BEFORE INSERT ON t2 BEGIN

       INSERT INTO t2dup(a,b,c) VALUES(new.a,new.b,new.c);

     END;

     INSERT INTO t2(a) VALUES(123);

     INSERT INTO t2(b) VALUES(234);

     INSERT INTO t2(c) VALUES(345);

     SELECT * FROM t2dup;

   }

 } {123 b c -1 234 c -1 b 345}

 do_test insert3-2.2 {

   execsql {

     DELETE FROM t2dup;

     INSERT INTO t2(a) SELECT 1 FROM t1 LIMIT 1;

     INSERT INTO t2(b) SELECT 987 FROM t1 LIMIT 1;

     INSERT INTO t2(c) SELECT 876 FROM t1 LIMIT 1;

     SELECT * FROM t2dup;

   }

 } {1 b c -1 987 c -1 b 876}

 # Test for proper detection of malformed WHEN clauses on INSERT triggers.

 #

 do_test insert3-3.1 {

   execsql {

     CREATE TABLE t3(a,b,c);

     CREATE TRIGGER t3r1 BEFORE INSERT on t3 WHEN nosuchcol BEGIN

       SELECT 'illegal WHEN clause';

     END;

   }

 } {}

 do_test insert3-3.2 {

   catchsql {

     INSERT INTO t3 VALUES(1,2,3)

   }

 } {1 {no such column: nosuchcol}}

 do_test insert3-3.3 {

   execsql {

     CREATE TABLE t4(a,b,c);

     CREATE TRIGGER t4r1 AFTER INSERT on t4 WHEN nosuchcol BEGIN

       SELECT 'illegal WHEN clause';

     END;

   }

 } {}

 do_test insert3-3.4 {

   catchsql {

     INSERT INTO t4 VALUES(1,2,3)

   }

 } {1 {no such column: nosuchcol}}

 } ;# ifcapable {trigger}

 # Tests for the INSERT INTO ... DEFAULT VALUES construct

 #

 do_test insert3-3.5 {

   execsql {

     CREATE TABLE t5(

       a INTEGER PRIMARY KEY,

       b DEFAULT 'xyz'

     );

     INSERT INTO t5 DEFAULT VALUES;

     SELECT * FROM t5;

   }

 } {1 xyz}

 do_test insert3-3.6 {

   execsql {

     INSERT INTO t5 DEFAULT VALUES;

     SELECT * FROM t5;

   }

 } {1 xyz 2 xyz}

 ifcapable bloblit {

   do_test insert3-3.7 {

     execsql {

       CREATE TABLE t6(x,y DEFAULT 4.3, z DEFAULT x'6869');

       INSERT INTO t6 DEFAULT VALUES;

       SELECT * FROM t6;

     }

   } {{} 4.3 hi}

 }

 db close

 finish_test