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 9 "ON CONFLICT (b, c, d) WHERE b==45 DO NOTHING" 0 120 } { 121 122 do_catchsql_test 2.$tn.2.$tn2 " 123 INSERT INTO xyz VALUES(11, 1, 1, 'one') $oc 124 " $rtbl($res) 125 } 126 127 do_execsql_test 2.$tn.3 { 128 SELECT * FROM xyz; 129 } {10 1 1 one} 130} 131 132foreach {tn sql} { 133 1 { 134 CREATE TABLE abc(a INTEGER PRIMARY KEY, x, y); 135 CREATE UNIQUE INDEX abc1 ON abc(('x' || x) COLLATE nocase); 136 } 137 2 { 138 CREATE TABLE abc(a INT PRIMARY KEY, x, y); 139 CREATE UNIQUE INDEX abc1 ON abc(('x' || x) COLLATE nocase); 140 } 141 3 { 142 CREATE TABLE abc(a INT PRIMARY KEY, x, y) WITHOUT ROWID; 143 CREATE UNIQUE INDEX abc1 ON abc(('x' || x) COLLATE nocase); 144 } 145} { 146 reset_db 147 execsql $sql 148 do_execsql_test 3.$tn.1 { 149 INSERT INTO abc VALUES(1, 'one', 'two'); 150 } 151 152 foreach {tn2 oc res} { 153 1 "ON CONFLICT DO NOTHING" 0 154 2 "ON CONFLICT ('x' || x) DO NOTHING" 0 155 3 "ON CONFLICT (('x' || x) COLLATE nocase) DO NOTHING" 0 156 4 "ON CONFLICT (('x' || x) COLLATE binary) DO NOTHING" 2 157 5 "ON CONFLICT (x || 'x') DO NOTHING" 2 158 6 "ON CONFLICT ((('x' || x))) DO NOTHING" 0 159 } { 160 do_catchsql_test 3.$tn.2.$tn2 " 161 INSERT INTO abc VALUES(2, 'one', NULL) $oc; 162 " $rtbl($res) 163 } 164 165 do_execsql_test 3.$tn.3 { 166 SELECT * FROM abc 167 } {1 one two} 168} 169 170foreach {tn sql} { 171 1 { 172 CREATE TABLE abc(a INTEGER PRIMARY KEY, x, y); 173 CREATE UNIQUE INDEX abc1 ON abc(x) WHERE y>0; 174 CREATE UNIQUE INDEX abc2 ON abc(y) WHERE x='xyz' COLLATE nocase; 175 } 176} { 177 reset_db 178 execsql $sql 179 do_execsql_test 4.$tn.1 { 180 INSERT INTO abc VALUES(1, 'one', 1); 181 INSERT INTO abc VALUES(2, 'two', 2); 182 INSERT INTO abc VALUES(3, 'xyz', 3); 183 INSERT INTO abc VALUES(4, 'XYZ', 4); 184 } 185 186 foreach {tn2 oc res} { 187 1 "ON CONFLICT DO NOTHING" 0 188 2 "ON CONFLICT(x) WHERE y>0 DO NOTHING" 0 189 3 "ON CONFLICT(x) DO NOTHING" 2 190 4 "ON CONFLICT(x) WHERE y>=0 DO NOTHING" 2 191 5 "ON CONFLICT(y) WHERE x='xyz' COLLATE nocase DO NOTHING" 1 192 } { 193 do_catchsql_test 4.$tn.2.$tn2 " 194 INSERT INTO abc VALUES(5, 'one', 10) $oc 195 " $rtbl($res) 196 } 197 198 do_execsql_test 4.$tn.3 { 199 SELECT * FROM abc 200 } {1 one 1 2 two 2 3 xyz 3 4 XYZ 4} 201 202 foreach {tn2 oc res} { 203 1 "ON CONFLICT DO NOTHING" 0 204 2 "ON CONFLICT(y) WHERE x='xyz' COLLATE nocase DO NOTHING" 0 205 3 "ON CONFLICT(y) WHERE x='xyz' COLLATE binary DO NOTHING" 2 206 4 "ON CONFLICT(x) WHERE y>0 DO NOTHING" 1 207 } { 208 do_catchsql_test 4.$tn.2.$tn2 " 209 INSERT INTO abc VALUES(5, 'xYz', 3) $oc 210 " $rtbl($res) 211 } 212} 213 214do_catchsql_test 5.0 { 215 CREATE TABLE w1(a INT PRIMARY KEY, x, y); 216 CREATE UNIQUE INDEX w1expr ON w1(('x' || x)); 217 INSERT INTO w1 VALUES(2, 'one', NULL) 218 ON CONFLICT (('x' || x) COLLATE nocase) DO NOTHING; 219} {1 {ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint}} 220 221#------------------------------------------------------------------------- 222# 223do_execsql_test 6.0 { 224 CREATE TABLE t1(a INTEGER PRIMARY KEY, b UNIQUE, c); 225 INSERT INTO t1 VALUES(1, 1, 'one'); 226 INSERT INTO t1 VALUES(2, 2, 'two'); 227 INSERT OR REPLACE INTO t1 VALUES(1, 2, 'two') ON CONFLICT(b) DO NOTHING; 228 PRAGMA integrity_check; 229} {ok} 230 231finish_test 232 233