1 # 2005 January 13

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

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

    13 #

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

    15 

    16 set testdir [file dirname $argv0]

    17 source $testdir/tester.tcl

    18 

    19 # All the tests in this file require trigger support

    20 #

    21 ifcapable {trigger} {

    22 

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

    24 # into the table.

    25 #

    26 do_test insert3-1.0 {

    27   execsql {

    28     CREATE TABLE t1(a,b);

    29     CREATE TABLE log(x UNIQUE, y);

    30     CREATE TRIGGER r1 AFTER INSERT ON t1 BEGIN

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

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

    33     END;

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

    35     INSERT INTO t1 VALUES(5,10);

    36     SELECT * FROM log ORDER BY x;

    37   }

    38 } {5 1 hello 1}

    39 do_test insert3-1.1 {

    40   execsql {

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

    42     SELECT * FROM log ORDER BY x;

    43   }

    44 } {5 2 hello 2}

    45 do_test insert3-1.2 {

    46   execsql {

    47     CREATE TABLE log2(x PRIMARY KEY,y);

    48     CREATE TRIGGER r2 BEFORE INSERT ON t1 BEGIN

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

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

    51     END;

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

    53     SELECT * FROM log ORDER BY x;

    54   }

    55 } {5 2 453 1 hello 2}

    56 do_test insert3-1.3 {

    57   execsql {

    58     SELECT * FROM log2 ORDER BY x;

    59   }

    60 } {hi 1}

    61 ifcapable compound {

    62   do_test insert3-1.4.1 {

    63     execsql {

    64       INSERT INTO t1 SELECT * FROM t1;

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

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

    67     }

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

    69   do_test insert3-1.4.2 {

    70     execsql {

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

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

    73     }

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

    75   do_test insert3-1.5 {

    76     execsql {

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

    78       SELECT * FROM log ORDER BY x;

    79     }

    80   } {5 4 453 2 hello 4 xyz 1}

    81 }

    82 

    83 do_test insert3-2.1 {

    84   execsql {

    85     CREATE TABLE t2(

    86       a INTEGER PRIMARY KEY,

    87       b DEFAULT 'b',

    88       c DEFAULT 'c'

    89     );

    90     CREATE TABLE t2dup(a,b,c);

    91     CREATE TRIGGER t2r1 BEFORE INSERT ON t2 BEGIN

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

    93     END;

    94     INSERT INTO t2(a) VALUES(123);

    95     INSERT INTO t2(b) VALUES(234);

    96     INSERT INTO t2(c) VALUES(345);

    97     SELECT * FROM t2dup;

    98   }

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

   100 do_test insert3-2.2 {

   101   execsql {

   102     DELETE FROM t2dup;

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

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

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

   106     SELECT * FROM t2dup;

   107   }

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

   109 

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

   111 #

   112 do_test insert3-3.1 {

   113   execsql {

   114     CREATE TABLE t3(a,b,c);

   115     CREATE TRIGGER t3r1 BEFORE INSERT on t3 WHEN nosuchcol BEGIN

   116       SELECT 'illegal WHEN clause';

   117     END;

   118   }

   119 } {}

   120 do_test insert3-3.2 {

   121   catchsql {

   122     INSERT INTO t3 VALUES(1,2,3)

   123   }

   124 } {1 {no such column: nosuchcol}}

   125 do_test insert3-3.3 {

   126   execsql {

   127     CREATE TABLE t4(a,b,c);

   128     CREATE TRIGGER t4r1 AFTER INSERT on t4 WHEN nosuchcol BEGIN

   129       SELECT 'illegal WHEN clause';

   130     END;

   131   }

   132 } {}

   133 do_test insert3-3.4 {

   134   catchsql {

   135     INSERT INTO t4 VALUES(1,2,3)

   136   }

   137 } {1 {no such column: nosuchcol}}

   138 

   139 } ;# ifcapable {trigger}

   140 

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

   142 #

   143 do_test insert3-3.5 {

   144   execsql {

   145     CREATE TABLE t5(

   146       a INTEGER PRIMARY KEY,

   147       b DEFAULT 'xyz'

   148     );

   149     INSERT INTO t5 DEFAULT VALUES;

   150     SELECT * FROM t5;

   151   }

   152 } {1 xyz}

   153 do_test insert3-3.6 {

   154   execsql {

   155     INSERT INTO t5 DEFAULT VALUES;

   156     SELECT * FROM t5;

   157   }

   158 } {1 xyz 2 xyz}

   159 

   160 ifcapable bloblit {

   161   do_test insert3-3.7 {

   162     execsql {

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

   164       INSERT INTO t6 DEFAULT VALUES;

   165       SELECT * FROM t6;

   166     }

   167   } {{} 4.3 hi}

   168 }

   169 db close

   170 

   171 finish_test