Debian Codegraph @

test/upsert2.test

raw
1 # 2018-04-17
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 #
12 # Test cases for UPSERT
13
14 set testdir [file dirname $argv0]
15 source $testdir/tester.tcl
16 set testprefix zipfile
17
18 do_execsql_test upsert2-100 {
19 CREATE TABLE t1(a INTEGER PRIMARY KEY, b int, c DEFAULT 0);
20 INSERT INTO t1(a,b) VALUES(1,2),(3,4);
21 INSERT INTO t1(a,b) VALUES(1,8),(2,11),(3,1)
22 ON CONFLICT(a) DO UPDATE SET b=excluded.b, c=c+1 WHERE t1.b<excluded.b;
23 SELECT *, 'x' FROM t1 ORDER BY a;
24 } {1 8 1 x 2 11 0 x 3 4 0 x}
25 do_execsql_test upsert2-110 {
26 DROP TABLE t1;
27 CREATE TABLE t1(a INT PRIMARY KEY, b int, c DEFAULT 0) WITHOUT ROWID;
28 INSERT INTO t1(a,b) VALUES(1,2),(3,4);
29 INSERT INTO t1(a,b) VALUES(1,8),(2,11),(3,1)
30 ON CONFLICT(a) DO UPDATE SET b=excluded.b, c=c+1 WHERE t1.b<excluded.b;
31 SELECT *, 'x' FROM t1 ORDER BY a;
32 } {1 8 1 x 2 11 0 x 3 4 0 x}
33
34 do_execsql_test upsert2-200 {
35 DROP TABLE t1;
36 CREATE TABLE t1(a INTEGER PRIMARY KEY, b int, c DEFAULT 0);
37 INSERT INTO t1(a,b) VALUES(1,2),(3,4);
38 WITH nx(a,b) AS (VALUES(1,8),(2,11),(3,1),(2,15),(1,4),(1,99))
39 INSERT INTO t1(a,b) SELECT a, b FROM nx WHERE true
40 ON CONFLICT(a) DO UPDATE SET b=excluded.b, c=c+1 WHERE t1.b<excluded.b;
41 SELECT *, 'x' FROM t1 ORDER BY a;
42 } {1 99 2 x 2 15 1 x 3 4 0 x}
43 do_execsql_test upsert2-201 {
44 DELETE FROM t1;
45 INSERT INTO t1(a,b) VALUES(1,2),(3,4);
46 WITH nx(a,b) AS (VALUES(1,8),(2,11),(3,1),(2,15),(1,4),(1,99))
47 INSERT INTO main.t1 AS t2(a,b) SELECT a, b FROM nx WHERE true
48 ON CONFLICT(a) DO UPDATE SET b=excluded.b, c=t2.c+1 WHERE t2.b<excluded.b;
49 SELECT *, 'x' FROM t1 ORDER BY a;
50 } {1 99 2 x 2 15 1 x 3 4 0 x}
51 do_catchsql_test upsert2-202 {
52 WITH nx(a,b) AS (VALUES(1,8),(2,11),(3,1),(2,15),(1,4),(1,99))
53 INSERT INTO t1 AS t2(a,b) SELECT a, b FROM nx WHERE true
54 ON CONFLICT(a) DO UPDATE SET b=excluded.b, c=t1.c+1 WHERE t1.b<excluded.b;
55 } {1 {no such column: t1.c}}
56 do_execsql_test upsert2-210 {
57 DROP TABLE t1;
58 CREATE TABLE t1(a INT PRIMARY KEY, b int, c DEFAULT 0) WITHOUT ROWID;
59 INSERT INTO t1(a,b) VALUES(1,2),(3,4);
60 WITH nx(a,b) AS (VALUES(1,8),(2,11),(3,1),(2,15),(1,4),(1,99))
61 INSERT INTO t1(a,b) SELECT a, b FROM nx WHERE true
62 ON CONFLICT(a) DO UPDATE SET b=excluded.b, c=c+1 WHERE t1.b<excluded.b;
63 SELECT *, 'x' FROM t1 ORDER BY a;
64 } {1 99 2 x 2 15 1 x 3 4 0 x}
65
66 # On an ON CONFLICT DO UPDATE, the before-insert, before-update, and
67 # after-update triggers fire.
68 #
69 do_execsql_test upsert2-300 {
70 DROP TABLE t1;
71 CREATE TABLE t1(a INTEGER PRIMARY KEY, b int, c DEFAULT 0);
72 CREATE TABLE record(x TEXT, y TEXT);
73 CREATE TRIGGER r1 BEFORE INSERT ON t1 BEGIN
74 INSERT INTO record(x,y)
75 VALUES('before-insert',format('%d,%d,%d',new.a,new.b,new.c));
76 END;
77 CREATE TRIGGER r2 AFTER INSERT ON t1 BEGIN
78 INSERT INTO record(x,y)
79 VALUES('after-insert',printf('%d,%d,%d',new.a,new.b,new.c));
80 END;
81 CREATE TRIGGER r3 BEFORE UPDATE ON t1 BEGIN
82 INSERT INTO record(x,y)
83 VALUES('before-update',format('%d,%d,%d/%d,%d,%d',
84 old.a,old.b,old.c,new.a,new.b,new.c));
85 END;
86 CREATE TRIGGER r4 AFTER UPDATE ON t1 BEGIN
87 INSERT INTO record(x,y)
88 VALUES('after-update',printf('%d,%d,%d/%d,%d,%d',
89 old.a,old.b,old.c,new.a,new.b,new.c));
90 END;
91 INSERT INTO t1(a,b) VALUES(1,2);
92 DELETE FROM record;
93 INSERT INTO t1(a,b) VALUES(1,2)
94 ON CONFLICT(a) DO UPDATE SET c=t1.c+1;
95 SELECT * FROM record
96 } {before-insert 1,2,0 before-update 1,2,0/1,2,1 after-update 1,2,0/1,2,1}
97
98 # On an ON CONFLICT DO NOTHING, only the before-insert trigger fires.
99 #
100 do_execsql_test upsert2-310 {
101 DELETE FROM record;
102 INSERT INTO t1(a,b) VALUES(1,2) ON CONFLICT DO NOTHING;
103 SELECT * FROM record;
104 } {before-insert 1,2,0}
105
106 # With ON CONFLICT DO UPDATE and a failed WHERE, only the before-insert
107 # trigger fires.
108 #
109 do_execsql_test upsert2-320 {
110 DELETE FROM record;
111 INSERT INTO t1(a,b) VALUES(1,2)
112 ON CONFLICT(a) DO UPDATE SET c=c+1 WHERE c<0;
113 SELECT * FROM record;
114 } {before-insert 1,2,0}
115 do_execsql_test upsert2-321 {
116 SELECT * FROM t1;
117 } {1 2 1}
118
119 # Trigger tests repeated for a WITHOUT ROWID table.
120 #
121 do_execsql_test upsert2-400 {
122 DROP TABLE t1;
123 CREATE TABLE t1(a INT PRIMARY KEY, b int, c DEFAULT 0) WITHOUT ROWID;
124 CREATE TRIGGER r1 BEFORE INSERT ON t1 BEGIN
125 INSERT INTO record(x,y)
126 VALUES('before-insert',format('%d,%d,%d',new.a,new.b,new.c));
127 END;
128 CREATE TRIGGER r2 AFTER INSERT ON t1 BEGIN
129 INSERT INTO record(x,y)
130 VALUES('after-insert',printf('%d,%d,%d',new.a,new.b,new.c));
131 END;
132 CREATE TRIGGER r3 BEFORE UPDATE ON t1 BEGIN
133 INSERT INTO record(x,y)
134 VALUES('before-update',format('%d,%d,%d/%d,%d,%d',
135 old.a,old.b,old.c,new.a,new.b,new.c));
136 END;
137 CREATE TRIGGER r4 AFTER UPDATE ON t1 BEGIN
138 INSERT INTO record(x,y)
139 VALUES('after-update',printf('%d,%d,%d/%d,%d,%d',
140 old.a,old.b,old.c,new.a,new.b,new.c));
141 END;
142 INSERT INTO t1(a,b) VALUES(1,2);
143 DELETE FROM record;
144 INSERT INTO t1(a,b) VALUES(1,2)
145 ON CONFLICT(a) DO UPDATE SET c=t1.c+1;
146 SELECT * FROM record
147 } {before-insert 1,2,0 before-update 1,2,0/1,2,1 after-update 1,2,0/1,2,1}
148
149 # On an ON CONFLICT DO NOTHING, only the before-insert trigger fires.
150 #
151 do_execsql_test upsert2-410 {
152 DELETE FROM record;
153 INSERT INTO t1(a,b) VALUES(1,2) ON CONFLICT DO NOTHING;
154 SELECT * FROM record;
155 } {before-insert 1,2,0}
156
157 # With ON CONFLICT DO UPDATE and a failed WHERE, only the before-insert
158 # trigger fires.
159 #
160 do_execsql_test upsert2-420 {
161 DELETE FROM record;
162 INSERT INTO t1(a,b) VALUES(1,2)
163 ON CONFLICT(a) DO UPDATE SET c=c+1 WHERE c<0;
164 SELECT * FROM record;
165 } {before-insert 1,2,0}
166 do_execsql_test upsert2-421 {
167 SELECT * FROM t1;
168 } {1 2 1}
169
170 finish_test