# 2018-04-12
#
# 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 upsert1-100 {
CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT, c DEFAULT 0);
CREATE UNIQUE INDEX t1x1 ON t1(b);
INSERT INTO t1(a,b) VALUES(1,2) ON CONFLICT DO NOTHING;
INSERT INTO t1(a,b) VALUES(1,99),(99,2) ON CONFLICT DO NOTHING;
SELECT * FROM t1;
} {1 2 0}
do_execsql_test upsert1-101 {
DELETE FROM t1;
INSERT INTO t1(a,b) VALUES(2,3) ON CONFLICT(a) DO NOTHING;
INSERT INTO t1(a,b) VALUES(2,99) ON CONFLICT(a) DO NOTHING;
SELECT * FROM t1;
} {2 3 0}
do_execsql_test upsert1-102 {
DELETE FROM t1;
INSERT INTO t1(a,b) VALUES(3,4) ON CONFLICT(b) DO NOTHING;
INSERT INTO t1(a,b) VALUES(99,4) ON CONFLICT(b) DO NOTHING;
SELECT * FROM t1;
} {3 4 0}
do_catchsql_test upsert1-110 {
INSERT INTO t1(a,b) VALUES(5,6) ON CONFLICT(x) DO NOTHING;
SELECT * FROM t1;
} {1 {no such column: x}}
do_catchsql_test upsert1-120 {
INSERT INTO t1(a,b) VALUES(5,6) ON CONFLICT(c) DO NOTHING;
SELECT * FROM t1;
} {1 {ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint}}
breakpoint
do_catchsql_test upsert1-130 {
INSERT INTO t1(a,b) VALUES(5,6) ON CONFLICT(b COLLATE nocase) DO NOTHING;
SELECT * FROM t1;
} {1 {ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint}}
do_execsql_test upsert1-140 {
DELETE FROM t1;
INSERT INTO t1(a,b) VALUES(5,6) ON CONFLICT(b COLLATE binary) DO NOTHING;
SELECT * FROM t1;
} {5 6 0}
do_catchsql_test upsert1-200 {
DROP TABLE t1;
CREATE TABLE t1(a INTEGER PRIMARY KEY, b INT, c DEFAULT 0);
CREATE UNIQUE INDEX t1x1 ON t1(a+b);
INSERT INTO t1(a,b) VALUES(7,8) ON CONFLICT(a+b) DO NOTHING;
INSERT INTO t1(a,b) VALUES(8,7),(9,6) ON CONFLICT(a+b) DO NOTHING;
SELECT * FROM t1;
} {0 {7 8 0}}
do_catchsql_test upsert1-201 {
INSERT INTO t1(a,b) VALUES(8,7),(9,6) ON CONFLICT(a) DO NOTHING;
} {1 {UNIQUE constraint failed: index 't1x1'}}
do_catchsql_test upsert1-210 {
DELETE FROM t1;
INSERT INTO t1(a,b) VALUES(9,10) ON CONFLICT(a+(+b)) DO NOTHING;
SELECT * FROM t1;
} {1 {ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint}}
do_catchsql_test upsert1-300 {
DROP INDEX t1x1;
DELETE FROM t1;
CREATE UNIQUE INDEX t1x1 ON t1(b) WHERE b>10;
INSERT INTO t1(a,b) VALUES(1,2),(3,2) ON CONFLICT(b) DO NOTHING;
SELECT * FROM t1;
} {1 {ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint}}
do_catchsql_test upsert1-310 {
DELETE FROM t1;
INSERT INTO t1(a,b) VALUES(1,2),(3,2) ON CONFLICT(b) WHERE b!=10 DO NOTHING;
SELECT * FROM t1;
} {1 {ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint}}
do_execsql_test upsert1-320 {
DELETE FROM t1;
INSERT INTO t1(a,b) VALUES(1,2),(3,2),(4,20),(5,20)
ON CONFLICT(b) WHERE b>10 DO NOTHING;
SELECT *, 'x' FROM t1 ORDER BY b, a;
} {1 2 0 x 3 2 0 x 4 20 0 x}
# Upsert works with count_changes=on;
do_execsql_test upsert1-400 {
DROP TABLE IF EXISTS t2;
CREATE TABLE t2(a TEXT UNIQUE, b INT DEFAULT 1);
INSERT INTO t2(a) VALUES('one'),('two'),('three');
PRAGMA count_changes=ON;
INSERT INTO t2(a) VALUES('one'),('one'),('three'),('four')
ON CONFLICT(a) DO UPDATE SET b=b+1;
} {1}
do_execsql_test upsert1-410 {
PRAGMA count_changes=OFF;
SELECT a, b FROM t2 ORDER BY a;
} {four 1 one 3 three 2 two 1}
finish_test