gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/upsert3.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  
    15  set testdir [file dirname $argv0]
    16  source $testdir/tester.tcl
    17  set testprefix zipfile
    18  
    19  do_execsql_test upsert3-100 {
    20    CREATE TABLE t1(k int, v text);
    21    CREATE UNIQUE INDEX x1 ON t1(k, v);
    22  } {}
    23  do_catchsql_test upsert3-110 {
    24    INSERT INTO t1 VALUES(0,'abcdefghij')
    25       ON CONFLICT(k) DO NOTHING;
    26  } {1 {ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint}}
    27  do_catchsql_test upsert3-120 {
    28    INSERT INTO t1 VALUES(0,'abcdefghij')
    29       ON CONFLICT(v) DO NOTHING;
    30  } {1 {ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint}}
    31  
    32  do_execsql_test upsert3-130 {
    33    INSERT INTO t1 VALUES(0, 'abcdefghij')
    34        ON CONFLICT(k,v) DO NOTHING;
    35    SELECT * FROM t1;
    36  } {0 abcdefghij}
    37  do_execsql_test upsert3-140 {
    38    INSERT INTO t1 VALUES(0, 'abcdefghij')
    39        ON CONFLICT(v,k) DO NOTHING;
    40    SELECT * FROM t1;
    41  } {0 abcdefghij}
    42  
    43  do_execsql_test upsert3-200 {
    44    CREATE TABLE excluded(a INT, b INT, c INT DEFAULT 0);
    45    CREATE UNIQUE INDEX excludedab ON excluded(a,b);
    46    INSERT INTO excluded(a,b) VALUES(1,2),(1,2),(3,4),(1,2),(5,6),(3,4)
    47      ON CONFLICT(b,a) DO UPDATE SET c=excluded.c+1;
    48    SELECT *, 'x' FROM excluded ORDER BY a;
    49  } {1 2 2 x 3 4 1 x 5 6 0 x}
    50  do_execsql_test upsert3-210 {
    51    INSERT INTO excluded AS base(a,b,c) VALUES(1,2,8),(1,2,3)
    52      ON CONFLICT(b,a) DO UPDATE SET c=excluded.c+1 WHERE base.c<excluded.c;
    53    SELECT *, 'x' FROM excluded ORDER BY a;
    54  } {1 2 9 x 3 4 1 x 5 6 0 x}
    55  
    56  
    57  
    58  finish_test