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