Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | New test cases for upsert. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | upsert |
| Files: | files | file ages | folders |
| SHA3-256: |
907b5a37c539ea67c285c87399b494c7 |
| User & Date: | drh 2018-04-17 16:16:40 |
Context
|
2018-04-17
| ||
| 18:16 | Add some more simple test cases for UPSERT. And a minor fix. (check-in: 27cd3b2f user: dan tags: upsert) | |
| 16:16 | New test cases for upsert. (check-in: 907b5a37 user: drh tags: upsert) | |
|
2018-04-16
| ||
| 14:36 | Merge the Lemon enhancement from trunk, resulting in smaller parser tables. (check-in: 641f4172 user: drh tags: upsert) | |
Changes
Changes to test/distinct.test.
| ︙ | ︙ | |||
164 165 166 167 168 169 170 |
2 "b, a FROM t1" {} {B A b a}
3 "a, b, c FROM t1" {hash} {A B C a b c}
4 "a, b, c FROM t1 ORDER BY a, b, c" {btree} {A B C a b c}
5 "b FROM t1 WHERE a = 'a'" {} {b}
6 "b FROM t1 ORDER BY +b COLLATE binary" {btree hash} {B b}
7 "a FROM t1" {} {A a}
8 "b COLLATE nocase FROM t1" {} {b}
| | | 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 |
2 "b, a FROM t1" {} {B A b a}
3 "a, b, c FROM t1" {hash} {A B C a b c}
4 "a, b, c FROM t1 ORDER BY a, b, c" {btree} {A B C a b c}
5 "b FROM t1 WHERE a = 'a'" {} {b}
6 "b FROM t1 ORDER BY +b COLLATE binary" {btree hash} {B b}
7 "a FROM t1" {} {A a}
8 "b COLLATE nocase FROM t1" {} {b}
9 "b COLLATE nocase FROM t1 ORDER BY b COLLATE nocase" {} {B}
} {
do_execsql_test 2.$tn.1 "SELECT DISTINCT $sql" $res
do_temptables_test 2.$tn.2 "SELECT DISTINCT $sql" $temptables
}
do_execsql_test 2.A {
SELECT (SELECT DISTINCT o.a FROM t1 AS i) FROM t1 AS o ORDER BY rowid;
|
| ︙ | ︙ |
Added test/upsert2.test.
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 |
# 2018-04-17
#
# The author disclaims copyright to this source code. In place of
# a legal notice, here is a blessing:
#
# May you do good and not evil.
# May you find forgiveness for yourself and forgive others.
# May you share freely, never taking more than you give.
#
#***********************************************************************
#
# Test cases for UPSERT
set testdir [file dirname $argv0]
source $testdir/tester.tcl
set testprefix zipfile
do_execsql_test upsert2-100 {
CREATE TABLE t1(a INTEGER PRIMARY KEY, b int, c DEFAULT 0);
INSERT INTO t1(a,b) VALUES(1,2),(3,4);
INSERT INTO t1(a,b) VALUES(1,8),(2,11),(3,1)
ON CONFLICT(a) DO UPDATE SET b=excluded.b, c=c+1 WHERE t1.b<excluded.b;
SELECT *, 'x' FROM t1 ORDER BY a;
} {1 8 1 x 2 11 0 x 3 4 0 x}
do_execsql_test upsert2-110 {
DROP TABLE t1;
CREATE TABLE t1(a INT PRIMARY KEY, b int, c DEFAULT 0) WITHOUT ROWID;
INSERT INTO t1(a,b) VALUES(1,2),(3,4);
INSERT INTO t1(a,b) VALUES(1,8),(2,11),(3,1)
ON CONFLICT(a) DO UPDATE SET b=excluded.b, c=c+1 WHERE t1.b<excluded.b;
SELECT *, 'x' FROM t1 ORDER BY a;
} {1 8 1 x 2 11 0 x 3 4 0 x}
do_execsql_test upsert2-200 {
DROP TABLE t1;
CREATE TABLE t1(a INTEGER PRIMARY KEY, b int, c DEFAULT 0);
INSERT INTO t1(a,b) VALUES(1,2),(3,4);
WITH nx(a,b) AS (VALUES(1,8),(2,11),(3,1),(2,15),(1,4),(1,99))
INSERT INTO t1(a,b) SELECT a, b FROM nx WHERE true
ON CONFLICT(a) DO UPDATE SET b=excluded.b, c=c+1 WHERE t1.b<excluded.b;
SELECT *, 'x' FROM t1 ORDER BY a;
} {1 99 2 x 2 15 1 x 3 4 0 x}
do_execsql_test upsert2-201 {
DELETE FROM t1;
INSERT INTO t1(a,b) VALUES(1,2),(3,4);
WITH nx(a,b) AS (VALUES(1,8),(2,11),(3,1),(2,15),(1,4),(1,99))
INSERT INTO t1(a,b) AS t2 SELECT a, b FROM nx WHERE true
ON CONFLICT(a) DO UPDATE SET b=excluded.b, c=t2.c+1 WHERE t2.b<excluded.b;
SELECT *, 'x' FROM t1 ORDER BY a;
} {1 99 2 x 2 15 1 x 3 4 0 x}
do_catchsql_test upsert2-202 {
WITH nx(a,b) AS (VALUES(1,8),(2,11),(3,1),(2,15),(1,4),(1,99))
INSERT INTO t1(a,b) AS t2 SELECT a, b FROM nx WHERE true
ON CONFLICT(a) DO UPDATE SET b=excluded.b, c=t1.c+1 WHERE t1.b<excluded.b;
} {1 {no such column: t1.c}}
do_execsql_test upsert2-210 {
DROP TABLE t1;
CREATE TABLE t1(a INT PRIMARY KEY, b int, c DEFAULT 0) WITHOUT ROWID;
INSERT INTO t1(a,b) VALUES(1,2),(3,4);
WITH nx(a,b) AS (VALUES(1,8),(2,11),(3,1),(2,15),(1,4),(1,99))
INSERT INTO t1(a,b) SELECT a, b FROM nx WHERE true
ON CONFLICT(a) DO UPDATE SET b=excluded.b, c=c+1 WHERE t1.b<excluded.b;
SELECT *, 'x' FROM t1 ORDER BY a;
} {1 99 2 x 2 15 1 x 3 4 0 x}
# On an ON CONFLICT DO UPDATE, the before-insert, before-update, and
# after-update triggers fire.
#
do_execsql_test upsert2-300 {
DROP TABLE t1;
CREATE TABLE t1(a INTEGER PRIMARY KEY, b int, c DEFAULT 0);
CREATE TABLE record(x TEXT, y TEXT);
CREATE TRIGGER r1 BEFORE INSERT ON t1 BEGIN
INSERT INTO record(x,y)
VALUES('before-insert',printf('%d,%d,%d',new.a,new.b,new.c));
END;
CREATE TRIGGER r2 AFTER INSERT ON t1 BEGIN
INSERT INTO record(x,y)
VALUES('after-insert',printf('%d,%d,%d',new.a,new.b,new.c));
END;
CREATE TRIGGER r3 BEFORE UPDATE ON t1 BEGIN
INSERT INTO record(x,y)
VALUES('before-update',printf('%d,%d,%d/%d,%d,%d',
old.a,old.b,old.c,new.a,new.b,new.c));
END;
CREATE TRIGGER r4 AFTER UPDATE ON t1 BEGIN
INSERT INTO record(x,y)
VALUES('after-update',printf('%d,%d,%d/%d,%d,%d',
old.a,old.b,old.c,new.a,new.b,new.c));
END;
INSERT INTO t1(a,b) VALUES(1,2);
DELETE FROM record;
INSERT INTO t1(a,b) VALUES(1,2)
ON CONFLICT(a) DO UPDATE SET c=t1.c+1;
SELECT * FROM record
} {before-insert 1,2,0 before-update 1,2,0/1,2,1 after-update 1,2,0/1,2,1}
# On an ON CONFLICT DO NOTHING, only the before-insert trigger fires.
#
do_execsql_test upsert2-310 {
DELETE FROM record;
INSERT INTO t1(a,b) VALUES(1,2) ON CONFLICT DO NOTHING;
SELECT * FROM record;
} {before-insert 1,2,0}
# With ON CONFLICT DO UPDATE and a failed WHERE, only the before-insert
# trigger fires.
#
do_execsql_test upsert2-320 {
DELETE FROM record;
INSERT INTO t1(a,b) VALUES(1,2)
ON CONFLICT(a) DO UPDATE SET c=c+1 WHERE c<0;
SELECT * FROM record;
} {before-insert 1,2,0}
do_execsql_test upsert2-321 {
SELECT * FROM t1;
} {1 2 1}
# Trigger tests repeated for a WITHOUT ROWID table.
#
do_execsql_test upsert2-400 {
DROP TABLE t1;
CREATE TABLE t1(a INT PRIMARY KEY, b int, c DEFAULT 0) WITHOUT ROWID;
CREATE TRIGGER r1 BEFORE INSERT ON t1 BEGIN
INSERT INTO record(x,y)
VALUES('before-insert',printf('%d,%d,%d',new.a,new.b,new.c));
END;
CREATE TRIGGER r2 AFTER INSERT ON t1 BEGIN
INSERT INTO record(x,y)
VALUES('after-insert',printf('%d,%d,%d',new.a,new.b,new.c));
END;
CREATE TRIGGER r3 BEFORE UPDATE ON t1 BEGIN
INSERT INTO record(x,y)
VALUES('before-update',printf('%d,%d,%d/%d,%d,%d',
old.a,old.b,old.c,new.a,new.b,new.c));
END;
CREATE TRIGGER r4 AFTER UPDATE ON t1 BEGIN
INSERT INTO record(x,y)
VALUES('after-update',printf('%d,%d,%d/%d,%d,%d',
old.a,old.b,old.c,new.a,new.b,new.c));
END;
INSERT INTO t1(a,b) VALUES(1,2);
DELETE FROM record;
INSERT INTO t1(a,b) VALUES(1,2)
ON CONFLICT(a) DO UPDATE SET c=t1.c+1;
SELECT * FROM record
} {before-insert 1,2,0 before-update 1,2,0/1,2,1 after-update 1,2,0/1,2,1}
# On an ON CONFLICT DO NOTHING, only the before-insert trigger fires.
#
do_execsql_test upsert2-410 {
DELETE FROM record;
INSERT INTO t1(a,b) VALUES(1,2) ON CONFLICT DO NOTHING;
SELECT * FROM record;
} {before-insert 1,2,0}
# With ON CONFLICT DO UPDATE and a failed WHERE, only the before-insert
# trigger fires.
#
do_execsql_test upsert2-420 {
DELETE FROM record;
INSERT INTO t1(a,b) VALUES(1,2)
ON CONFLICT(a) DO UPDATE SET c=c+1 WHERE c<0;
SELECT * FROM record;
} {before-insert 1,2,0}
do_execsql_test upsert2-421 {
SELECT * FROM t1;
} {1 2 1}
finish_test
|
Added test/upsert3.test.
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 |
# 2018-04-17
#
# The author disclaims copyright to this source code. In place of
# a legal notice, here is a blessing:
#
# May you do good and not evil.
# May you find forgiveness for yourself and forgive others.
# May you share freely, never taking more than you give.
#
#***********************************************************************
#
# Test cases for UPSERT
#
set testdir [file dirname $argv0]
source $testdir/tester.tcl
set testprefix zipfile
do_execsql_test upsert3-100 {
CREATE TABLE t1(k int, v text);
CREATE UNIQUE INDEX x1 ON t1(k, v);
} {}
do_catchsql_test upsert3-110 {
INSERT INTO t1 VALUES(0,'abcdefghij')
ON CONFLICT(k) DO NOTHING;
} {1 {ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint}}
do_catchsql_test upsert3-120 {
INSERT INTO t1 VALUES(0,'abcdefghij')
ON CONFLICT(v) DO NOTHING;
} {1 {ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint}}
do_execsql_test upsert3-130 {
INSERT INTO t1 VALUES(0, 'abcdefghij')
ON CONFLICT(k,v) DO NOTHING;
SELECT * FROM t1;
} {0 abcdefghij}
do_execsql_test upsert3-140 {
INSERT INTO t1 VALUES(0, 'abcdefghij')
ON CONFLICT(v,k) DO NOTHING;
SELECT * FROM t1;
} {0 abcdefghij}
do_execsql_test upsert3-200 {
CREATE TABLE excluded(a INT, b INT, c INT DEFAULT 0);
CREATE UNIQUE INDEX excludedab ON excluded(a,b);
INSERT INTO excluded(a,b) VALUES(1,2),(1,2),(3,4),(1,2),(5,6),(3,4)
ON CONFLICT(b,a) DO UPDATE SET c=excluded.c+1;
SELECT *, 'x' FROM excluded ORDER BY a;
} {1 2 2 x 3 4 1 x 5 6 0 x}
do_execsql_test upsert3-210 {
INSERT INTO excluded(a,b,c) AS base VALUES(1,2,8),(1,2,3)
ON CONFLICT(b,a) DO UPDATE SET c=excluded.c+1 WHERE base.c<excluded.c;
SELECT *, 'x' FROM excluded ORDER BY a;
} {1 2 9 x 3 4 1 x 5 6 0 x}
finish_test
|