1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000
1.2 +++ b/os/persistentdata/persistentstorage/sqlite3api/TEST/TclScript/insert3.test Fri Jun 15 03:10:57 2012 +0200
1.3 @@ -0,0 +1,171 @@
1.4 +# 2005 January 13
1.5 +#
1.6 +# The author disclaims copyright to this source code. In place of
1.7 +# a legal notice, here is a blessing:
1.8 +#
1.9 +# May you do good and not evil.
1.10 +# May you find forgiveness for yourself and forgive others.
1.11 +# May you share freely, never taking more than you give.
1.12 +#
1.13 +#***********************************************************************
1.14 +# This file implements regression tests for SQLite library. The
1.15 +# focus of this file is testing corner cases of the INSERT statement.
1.16 +#
1.17 +# $Id: insert3.test,v 1.7 2007/09/12 17:01:45 danielk1977 Exp $
1.18 +
1.19 +set testdir [file dirname $argv0]
1.20 +source $testdir/tester.tcl
1.21 +
1.22 +# All the tests in this file require trigger support
1.23 +#
1.24 +ifcapable {trigger} {
1.25 +
1.26 +# Create a table and a corresponding insert trigger. Do a self-insert
1.27 +# into the table.
1.28 +#
1.29 +do_test insert3-1.0 {
1.30 + execsql {
1.31 + CREATE TABLE t1(a,b);
1.32 + CREATE TABLE log(x UNIQUE, y);
1.33 + CREATE TRIGGER r1 AFTER INSERT ON t1 BEGIN
1.34 + UPDATE log SET y=y+1 WHERE x=new.a;
1.35 + INSERT OR IGNORE INTO log VALUES(new.a, 1);
1.36 + END;
1.37 + INSERT INTO t1 VALUES('hello','world');
1.38 + INSERT INTO t1 VALUES(5,10);
1.39 + SELECT * FROM log ORDER BY x;
1.40 + }
1.41 +} {5 1 hello 1}
1.42 +do_test insert3-1.1 {
1.43 + execsql {
1.44 + INSERT INTO t1 SELECT a, b+10 FROM t1;
1.45 + SELECT * FROM log ORDER BY x;
1.46 + }
1.47 +} {5 2 hello 2}
1.48 +do_test insert3-1.2 {
1.49 + execsql {
1.50 + CREATE TABLE log2(x PRIMARY KEY,y);
1.51 + CREATE TRIGGER r2 BEFORE INSERT ON t1 BEGIN
1.52 + UPDATE log2 SET y=y+1 WHERE x=new.b;
1.53 + INSERT OR IGNORE INTO log2 VALUES(new.b,1);
1.54 + END;
1.55 + INSERT INTO t1 VALUES(453,'hi');
1.56 + SELECT * FROM log ORDER BY x;
1.57 + }
1.58 +} {5 2 453 1 hello 2}
1.59 +do_test insert3-1.3 {
1.60 + execsql {
1.61 + SELECT * FROM log2 ORDER BY x;
1.62 + }
1.63 +} {hi 1}
1.64 +ifcapable compound {
1.65 + do_test insert3-1.4.1 {
1.66 + execsql {
1.67 + INSERT INTO t1 SELECT * FROM t1;
1.68 + SELECT 'a:', x, y FROM log UNION ALL
1.69 + SELECT 'b:', x, y FROM log2 ORDER BY x;
1.70 + }
1.71 + } {a: 5 4 b: 10 2 b: 20 1 a: 453 2 a: hello 4 b: hi 2 b: world 1}
1.72 + do_test insert3-1.4.2 {
1.73 + execsql {
1.74 + SELECT 'a:', x, y FROM log UNION ALL
1.75 + SELECT 'b:', x, y FROM log2 ORDER BY x, y;
1.76 + }
1.77 + } {a: 5 4 b: 10 2 b: 20 1 a: 453 2 a: hello 4 b: hi 2 b: world 1}
1.78 + do_test insert3-1.5 {
1.79 + execsql {
1.80 + INSERT INTO t1(a) VALUES('xyz');
1.81 + SELECT * FROM log ORDER BY x;
1.82 + }
1.83 + } {5 4 453 2 hello 4 xyz 1}
1.84 +}
1.85 +
1.86 +do_test insert3-2.1 {
1.87 + execsql {
1.88 + CREATE TABLE t2(
1.89 + a INTEGER PRIMARY KEY,
1.90 + b DEFAULT 'b',
1.91 + c DEFAULT 'c'
1.92 + );
1.93 + CREATE TABLE t2dup(a,b,c);
1.94 + CREATE TRIGGER t2r1 BEFORE INSERT ON t2 BEGIN
1.95 + INSERT INTO t2dup(a,b,c) VALUES(new.a,new.b,new.c);
1.96 + END;
1.97 + INSERT INTO t2(a) VALUES(123);
1.98 + INSERT INTO t2(b) VALUES(234);
1.99 + INSERT INTO t2(c) VALUES(345);
1.100 + SELECT * FROM t2dup;
1.101 + }
1.102 +} {123 b c -1 234 c -1 b 345}
1.103 +do_test insert3-2.2 {
1.104 + execsql {
1.105 + DELETE FROM t2dup;
1.106 + INSERT INTO t2(a) SELECT 1 FROM t1 LIMIT 1;
1.107 + INSERT INTO t2(b) SELECT 987 FROM t1 LIMIT 1;
1.108 + INSERT INTO t2(c) SELECT 876 FROM t1 LIMIT 1;
1.109 + SELECT * FROM t2dup;
1.110 + }
1.111 +} {1 b c -1 987 c -1 b 876}
1.112 +
1.113 +# Test for proper detection of malformed WHEN clauses on INSERT triggers.
1.114 +#
1.115 +do_test insert3-3.1 {
1.116 + execsql {
1.117 + CREATE TABLE t3(a,b,c);
1.118 + CREATE TRIGGER t3r1 BEFORE INSERT on t3 WHEN nosuchcol BEGIN
1.119 + SELECT 'illegal WHEN clause';
1.120 + END;
1.121 + }
1.122 +} {}
1.123 +do_test insert3-3.2 {
1.124 + catchsql {
1.125 + INSERT INTO t3 VALUES(1,2,3)
1.126 + }
1.127 +} {1 {no such column: nosuchcol}}
1.128 +do_test insert3-3.3 {
1.129 + execsql {
1.130 + CREATE TABLE t4(a,b,c);
1.131 + CREATE TRIGGER t4r1 AFTER INSERT on t4 WHEN nosuchcol BEGIN
1.132 + SELECT 'illegal WHEN clause';
1.133 + END;
1.134 + }
1.135 +} {}
1.136 +do_test insert3-3.4 {
1.137 + catchsql {
1.138 + INSERT INTO t4 VALUES(1,2,3)
1.139 + }
1.140 +} {1 {no such column: nosuchcol}}
1.141 +
1.142 +} ;# ifcapable {trigger}
1.143 +
1.144 +# Tests for the INSERT INTO ... DEFAULT VALUES construct
1.145 +#
1.146 +do_test insert3-3.5 {
1.147 + execsql {
1.148 + CREATE TABLE t5(
1.149 + a INTEGER PRIMARY KEY,
1.150 + b DEFAULT 'xyz'
1.151 + );
1.152 + INSERT INTO t5 DEFAULT VALUES;
1.153 + SELECT * FROM t5;
1.154 + }
1.155 +} {1 xyz}
1.156 +do_test insert3-3.6 {
1.157 + execsql {
1.158 + INSERT INTO t5 DEFAULT VALUES;
1.159 + SELECT * FROM t5;
1.160 + }
1.161 +} {1 xyz 2 xyz}
1.162 +
1.163 +ifcapable bloblit {
1.164 + do_test insert3-3.7 {
1.165 + execsql {
1.166 + CREATE TABLE t6(x,y DEFAULT 4.3, z DEFAULT x'6869');
1.167 + INSERT INTO t6 DEFAULT VALUES;
1.168 + SELECT * FROM t6;
1.169 + }
1.170 + } {{} 4.3 hi}
1.171 +}
1.172 +db close
1.173 +
1.174 +finish_test