From 7bb481fda9ecb134804b49c2ce77ca28f7eea583 Mon Sep 17 00:00:00 2001 From: Hans-Christoph Steiner Date: Fri, 30 Mar 2012 20:42:12 -0400 Subject: Imported Upstream version 2.0.3 --- test/insert3.test | 205 ++++++++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 205 insertions(+) create mode 100644 test/insert3.test (limited to 'test/insert3.test') diff --git a/test/insert3.test b/test/insert3.test new file mode 100644 index 0000000..6b253e0 --- /dev/null +++ b/test/insert3.test @@ -0,0 +1,205 @@ +# 2005 January 13 +# +# 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. +# +#*********************************************************************** +# This file implements regression tests for SQLite library. The +# focus of this file is testing corner cases of the INSERT statement. +# +# $Id: insert3.test,v 1.9 2009/04/23 14:58:40 danielk1977 Exp $ + +set testdir [file dirname $argv0] +source $testdir/tester.tcl + +# All the tests in this file require trigger support +# +ifcapable {trigger} { + +# Create a table and a corresponding insert trigger. Do a self-insert +# into the table. +# +do_test insert3-1.0 { + execsql { + CREATE TABLE t1(a,b); + CREATE TABLE log(x UNIQUE, y); + CREATE TRIGGER r1 AFTER INSERT ON t1 BEGIN + UPDATE log SET y=y+1 WHERE x=new.a; + INSERT OR IGNORE INTO log VALUES(new.a, 1); + END; + INSERT INTO t1 VALUES('hello','world'); + INSERT INTO t1 VALUES(5,10); + SELECT * FROM log ORDER BY x; + } +} {5 1 hello 1} +do_test insert3-1.1 { + execsql { + INSERT INTO t1 SELECT a, b+10 FROM t1; + SELECT * FROM log ORDER BY x; + } +} {5 2 hello 2} +do_test insert3-1.2 { + execsql { + CREATE TABLE log2(x PRIMARY KEY,y); + CREATE TRIGGER r2 BEFORE INSERT ON t1 BEGIN + UPDATE log2 SET y=y+1 WHERE x=new.b; + INSERT OR IGNORE INTO log2 VALUES(new.b,1); + END; + INSERT INTO t1 VALUES(453,'hi'); + SELECT * FROM log ORDER BY x; + } +} {5 2 453 1 hello 2} +do_test insert3-1.3 { + execsql { + SELECT * FROM log2 ORDER BY x; + } +} {hi 1} +ifcapable compound { + do_test insert3-1.4.1 { + execsql { + INSERT INTO t1 SELECT * FROM t1; + SELECT 'a:', x, y FROM log UNION ALL + SELECT 'b:', x, y FROM log2 ORDER BY x; + } + } {a: 5 4 b: 10 2 b: 20 1 a: 453 2 a: hello 4 b: hi 2 b: world 1} + do_test insert3-1.4.2 { + execsql { + SELECT 'a:', x, y FROM log UNION ALL + SELECT 'b:', x, y FROM log2 ORDER BY x, y; + } + } {a: 5 4 b: 10 2 b: 20 1 a: 453 2 a: hello 4 b: hi 2 b: world 1} + do_test insert3-1.5 { + execsql { + INSERT INTO t1(a) VALUES('xyz'); + SELECT * FROM log ORDER BY x; + } + } {5 4 453 2 hello 4 xyz 1} +} + +do_test insert3-2.1 { + execsql { + CREATE TABLE t2( + a INTEGER PRIMARY KEY, + b DEFAULT 'b', + c DEFAULT 'c' + ); + CREATE TABLE t2dup(a,b,c); + CREATE TRIGGER t2r1 BEFORE INSERT ON t2 BEGIN + INSERT INTO t2dup(a,b,c) VALUES(new.a,new.b,new.c); + END; + INSERT INTO t2(a) VALUES(123); + INSERT INTO t2(b) VALUES(234); + INSERT INTO t2(c) VALUES(345); + SELECT * FROM t2dup; + } +} {123 b c -1 234 c -1 b 345} +do_test insert3-2.2 { + execsql { + DELETE FROM t2dup; + INSERT INTO t2(a) SELECT 1 FROM t1 LIMIT 1; + INSERT INTO t2(b) SELECT 987 FROM t1 LIMIT 1; + INSERT INTO t2(c) SELECT 876 FROM t1 LIMIT 1; + SELECT * FROM t2dup; + } +} {1 b c -1 987 c -1 b 876} + +# Test for proper detection of malformed WHEN clauses on INSERT triggers. +# +do_test insert3-3.1 { + execsql { + CREATE TABLE t3(a,b,c); + CREATE TRIGGER t3r1 BEFORE INSERT on t3 WHEN nosuchcol BEGIN + SELECT 'illegal WHEN clause'; + END; + } +} {} +do_test insert3-3.2 { + catchsql { + INSERT INTO t3 VALUES(1,2,3) + } +} {1 {no such column: nosuchcol}} +do_test insert3-3.3 { + execsql { + CREATE TABLE t4(a,b,c); + CREATE TRIGGER t4r1 AFTER INSERT on t4 WHEN nosuchcol BEGIN + SELECT 'illegal WHEN clause'; + END; + } +} {} +do_test insert3-3.4 { + catchsql { + INSERT INTO t4 VALUES(1,2,3) + } +} {1 {no such column: nosuchcol}} + +} ;# ifcapable {trigger} + +# Tests for the INSERT INTO ... DEFAULT VALUES construct +# +do_test insert3-3.5 { + execsql { + CREATE TABLE t5( + a INTEGER PRIMARY KEY, + b DEFAULT 'xyz' + ); + INSERT INTO t5 DEFAULT VALUES; + SELECT * FROM t5; + } +} {1 xyz} +do_test insert3-3.6 { + execsql { + INSERT INTO t5 DEFAULT VALUES; + SELECT * FROM t5; + } +} {1 xyz 2 xyz} + +ifcapable bloblit { + do_test insert3-3.7 { + execsql { + CREATE TABLE t6(x,y DEFAULT 4.3, z DEFAULT x'6869'); + INSERT INTO t6 DEFAULT VALUES; + SELECT * FROM t6; + } + } {{} 4.3 hi} +} + +foreach tab [db eval {SELECT name FROM sqlite_master WHERE type = 'table'}] { + db eval "DROP TABLE $tab" +} +db close +sqlite3 db test.db + +#------------------------------------------------------------------------- +# While developing tests for a different feature (savepoint) the following +# sequence was found to cause an assert() in btree.c to fail. These +# tests are included to ensure that that bug is fixed. +# +do_test insert3-4.1 { + execsql { + CREATE TABLE t1(a, b, c); + CREATE INDEX i1 ON t1(a, b); + BEGIN; + INSERT INTO t1 VALUES(randstr(10,400),randstr(10,400),randstr(10,400)); + } + set r "randstr(10,400)" + for {set ii 0} {$ii < 10} {incr ii} { + execsql "INSERT INTO t1 SELECT $r, $r, $r FROM t1" + } + execsql { COMMIT } +} {} +do_test insert3-4.2 { + execsql { + PRAGMA cache_size = 10; + BEGIN; + UPDATE t1 SET a = randstr(10,10) WHERE (rowid%4)==0; + DELETE FROM t1 WHERE rowid%2; + INSERT INTO t1 SELECT randstr(10,400), randstr(10,400), c FROM t1; + COMMIT; + } +} {} + +finish_test -- cgit v1.2.3