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 14set testdir [file dirname $argv0] 15source $testdir/tester.tcl 16set testprefix upsert4 17 18foreach {tn sql} { 19 1 { CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c UNIQUE) } 20 2 { CREATE TABLE t1(a INT PRIMARY KEY, b, c UNIQUE) } 21 3 { CREATE TABLE t1(a INT PRIMARY KEY, b, c UNIQUE) WITHOUT ROWID} 22} { 23 reset_db 24 execsql $sql 25 26 do_execsql_test 1.$tn.0 { 27 INSERT INTO t1 VALUES(1, NULL, 'one'); 28 INSERT INTO t1 VALUES(2, NULL, 'two'); 29 INSERT INTO t1 VALUES(3, NULL, 'three'); 30 } 31 32 do_execsql_test 1.$tn.1 { 33 INSERT INTO t1 VALUES(1, NULL, 'xyz') ON CONFLICT DO NOTHING; 34 SELECT * FROM t1; 35 } { 36 1 {} one 2 {} two 3 {} three 37 } 38 39 do_execsql_test 1.$tn.2 { 40 INSERT INTO t1 VALUES(4, NULL, 'two') ON CONFLICT DO NOTHING; 41 SELECT * FROM t1; 42 } { 43 1 {} one 2 {} two 3 {} three 44 } 45 46 do_execsql_test 1.$tn.3 { 47 INSERT INTO t1 VALUES(4, NULL, 'two') ON CONFLICT (c) DO UPDATE SET b = 1; 48 SELECT * FROM t1; 49 } { 50 1 {} one 2 1 two 3 {} three 51 } 52 53 do_execsql_test 1.$tn.4 { 54 INSERT INTO t1 VALUES(2, NULL, 'zero') ON CONFLICT (a) DO UPDATE SET b=2; 55 SELECT * FROM t1; 56 } {1 {} one 2 2 two 3 {} three} 57 58 do_catchsql_test 1.$tn.5 { 59 INSERT INTO t1 VALUES(2, NULL, 'zero') ON CONFLICT (a) 60 DO UPDATE SET c = 'one'; 61 } {1 {UNIQUE constraint failed: t1.c}} 62 63 do_execsql_test 1.$tn.6 { 64 SELECT * FROM t1; 65 } {1 {} one 2 2 two 3 {} three} 66 67 do_execsql_test 1.$tn.7 { 68 INSERT INTO t1 VALUES(2, NULL, 'zero') ON CONFLICT (a) 69 DO UPDATE SET (b, c) = (SELECT 'x', 'y'); 70 SELECT * FROM t1; 71 } {1 {} one 2 x y 3 {} three} 72 73 do_execsql_test 1.$tn.8 { 74 INSERT INTO t1 VALUES(1, NULL, NULL) ON CONFLICT (a) 75 DO UPDATE SET (c, a) = ('four', 4); 76 SELECT * FROM t1 ORDER BY 1; 77 } {2 x y 3 {} three 4 {} four} 78} 79 80#------------------------------------------------------------------------- 81# Test target analysis. 82# 83set rtbl(0) {0 {}} 84set rtbl(1) {/1 .*failed.*/} 85set rtbl(2) {1 {ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint}} 86 87foreach {tn sql} { 88 1 { 89 CREATE TABLE xyz(a INTEGER PRIMARY KEY, b, c, d); 90 CREATE UNIQUE INDEX xyz1 ON xyz(d, c, b COLLATE nocase); 91 } 92 93 2 { 94 CREATE TABLE xyz(a INT PRIMARY KEY, b, c, d); 95 CREATE UNIQUE INDEX xyz1 ON xyz(d, c, b COLLATE nocase); 96 } 97 98 3 { 99 CREATE TABLE xyz(a INT PRIMARY KEY, b, c, d) WITHOUT ROWID; 100 CREATE UNIQUE INDEX xyz1 ON xyz(d, c, b COLLATE nocase); 101 } 102} { 103 reset_db 104 execsql $sql 105 do_execsql_test 2.$tn.1 { 106 INSERT INTO xyz VALUES(10, 1, 1, 'one'); 107 } 108 109 110 foreach {tn2 oc res} { 111 1 "ON CONFLICT (b COLLATE nocase, c, d) DO NOTHING" 0 112 2 "ON CONFLICT (b, c, d) DO NOTHING" 0 113 3 "ON CONFLICT (b, c COLLATE nocase, d) DO NOTHING" 2 114 4 "ON CONFLICT (a) DO NOTHING" 1 115 5 "ON CONFLICT DO NOTHING" 0 116 6 "ON CONFLICT (b, c, d) WHERE a!=0 DO NOTHING" 0 117 7 "ON CONFLICT (d, c, c) WHERE a!=0 DO NOTHING" 2 118 8 "ON CONFLICT (b COLLATE nocase, c COLLATE nocase, d) DO NOTHING" 2 119 } { 120 121 do_catchsql_test 2.$tn.2.$tn2 " 122 INSERT INTO xyz VALUES(11, 1, 1, 'one') $oc 123 " $rtbl($res) 124 } 125 126 do_execsql_test 2.$tn.3 { 127 SELECT * FROM xyz; 128 } {10 1 1 one} 129} 130 131foreach {tn sql} { 132 1 { 133 CREATE TABLE abc(a INTEGER PRIMARY KEY, x, y); 134 CREATE UNIQUE INDEX abc1 ON abc(('x' || x) COLLATE nocase); 135 } 136 2 { 137 CREATE TABLE abc(a INT PRIMARY KEY, x, y); 138 CREATE UNIQUE INDEX abc1 ON abc(('x' || x) COLLATE nocase); 139 } 140 3 { 141 CREATE TABLE abc(a INT PRIMARY KEY, x, y) WITHOUT ROWID; 142 CREATE UNIQUE INDEX abc1 ON abc(('x' || x) COLLATE nocase); 143 } 144} { 145 reset_db 146 execsql $sql 147 do_execsql_test 2.$tn.1 { 148 INSERT INTO abc VALUES(1, 'one', 'two'); 149 } 150 151 foreach {tn2 oc res} { 152 1 "ON CONFLICT DO NOTHING" 0 153 2 "ON CONFLICT ('x' || x) DO NOTHING" 0 154 3 "ON CONFLICT (('x' || x) COLLATE nocase) DO NOTHING" 0 155 4 "ON CONFLICT (('x' || x) COLLATE binary) DO NOTHING" 2 156 5 "ON CONFLICT (x || 'x') DO NOTHING" 2 157 6 "ON CONFLICT ((('x' || x))) DO NOTHING" 0 158 } { 159 do_catchsql_test 2.$tn.2.$tn2 " 160 INSERT INTO abc VALUES(2, 'one', NULL) $oc; 161 " $rtbl($res) 162 } 163 164 do_execsql_test 2.$tn.3 { 165 SELECT * FROM abc 166 } {1 one two} 167} 168 169do_catchsql_test 3.0 { 170 CREATE TABLE w1(a INT PRIMARY KEY, x, y); 171 CREATE UNIQUE INDEX w1expr ON w1(('x' || x)); 172 INSERT INTO w1 VALUES(2, 'one', NULL) 173 ON CONFLICT (('x' || x) COLLATE nocase) DO NOTHING; 174} {1 {ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint}} 175 176finish_test 177 178