summaryrefslogtreecommitdiffstats
path: root/test/upsert2.test
diff options
context:
space:
mode:
Diffstat (limited to 'test/upsert2.test')
-rw-r--r--test/upsert2.test170
1 files changed, 170 insertions, 0 deletions
diff --git a/test/upsert2.test b/test/upsert2.test
new file mode 100644
index 0000000..5cbc465
--- /dev/null
+++ b/test/upsert2.test
@@ -0,0 +1,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
generated by cgit v1.2.3 (git 2.39.1) at 2024-09-21 08:08:27 +0000