xref: /sqlite-3.40.0/test/insert3.test (revision 4dcbdbff) 
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.3 2005/01/29 08:32:46 danielk1977 Exp $
15
16set testdir [file dirname $argv0]
17source $testdir/tester.tcl
18
19# All the tests in this file require trigger support
20#
21ifcapable {trigger} {
22
23# Create a table and a corresponding insert trigger.  Do a self-insert
24# into the table.
25#
26do_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}
39do_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}
45do_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}
56do_test insert3-1.3 {
57  execsql {
58    SELECT * FROM log2 ORDER BY x;
59  }
60} {hi 1}
61ifcapable compound {
62  do_test insert3-1.4 {
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.5 {
70    execsql {
71      INSERT INTO t1(a) VALUES('xyz');
72      SELECT * FROM log ORDER BY x;
73    }
74  } {5 4 453 2 hello 4 xyz 1}
75}
76
77do_test insert3-2.1 {
78  execsql {
79    CREATE TABLE t2(
80      a INTEGER PRIMARY KEY,
81      b DEFAULT 'b',
82      c DEFAULT 'c'
83    );
84    CREATE TABLE t2dup(a,b,c);
85    CREATE TRIGGER t2r1 BEFORE INSERT ON t2 BEGIN
86      INSERT INTO t2dup(a,b,c) VALUES(new.a,new.b,new.c);
87    END;
88    INSERT INTO t2(a) VALUES(123);
89    INSERT INTO t2(b) VALUES(234);
90    INSERT INTO t2(c) VALUES(345);
91    SELECT * FROM t2dup;
92  }
93} {123 b c -1 234 c -1 b 345}
94do_test insert3-2.2 {
95  execsql {
96    DELETE FROM t2dup;
97    INSERT INTO t2(a) SELECT 1 FROM t1 LIMIT 1;
98    INSERT INTO t2(b) SELECT 987 FROM t1 LIMIT 1;
99    INSERT INTO t2(c) SELECT 876 FROM t1 LIMIT 1;
100    SELECT * FROM t2dup;
101  }
102} {1 b c -1 987 c -1 b 876}
103
104# Test for proper detection of malformed WHEN clauses on INSERT triggers.
105#
106do_test insert3-3.1 {
107  execsql {
108    CREATE TABLE t3(a,b,c);
109    CREATE TRIGGER t3r1 BEFORE INSERT on t3 WHEN nosuchcol BEGIN
110      SELECT 'illegal WHEN clause';
111    END;
112  }
113} {}
114do_test insert3-3.2 {
115  catchsql {
116    INSERT INTO t3 VALUES(1,2,3)
117  }
118} {1 {no such column: nosuchcol}}
119do_test insert3-3.3 {
120  execsql {
121    CREATE TABLE t4(a,b,c);
122    CREATE TRIGGER t4r1 AFTER INSERT on t4 WHEN nosuchcol BEGIN
123      SELECT 'illegal WHEN clause';
124    END;
125  }
126} {}
127do_test insert3-3.4 {
128  catchsql {
129    INSERT INTO t4 VALUES(1,2,3)
130  }
131} {1 {no such column: nosuchcol}}
132
133} ;# ifcapable {trigger}
134
135finish_test
136