test/upsert2.test

   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