xref: /sqlite-3.40.0/test/upsert2.test (revision 6bcd5857)

# 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