github.com/jdgcs/sqlite3@v1.12.1-0.20210908114423-bc5f96e4dd51/testdata/tcl/upsert2.test (about)

     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',printf('%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',printf('%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',printf('%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',printf('%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