AryaWu/sqlite

1# 2005 January 132#3# The author disclaims copyright to this source code.  In place of4# 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# This file implements regression tests for SQLite library.  The12# focus of this file is testing corner cases of the INSERT statement.13#14# $Id: insert3.test,v 1.9 2009/04/23 14:58:40 danielk1977 Exp $15 16set testdir [file dirname $argv0]17source $testdir/tester.tcl18 19# All the tests in this file require trigger support20#21ifcapable {trigger} {22 23# Create a table and a corresponding insert trigger.  Do a self-insert24# into the table.25#26do_test insert3-1.0 {27  execsql {28    CREATE TABLE t1(a,b);29    CREATE TABLE log(x UNIQUE, y);30    CREATE TRIGGER r1 AFTER INSERT ON t1 BEGIN31      UPDATE log SET y=y+1 WHERE x=new.a;32      INSERT OR IGNORE INTO log VALUES(new.a, 1);33    END;34    INSERT INTO t1 VALUES('hello','world');35    INSERT INTO t1 VALUES(5,10);36    SELECT * FROM log ORDER BY x;37  }38} {5 1 hello 1}39do_test insert3-1.1 {40  execsql {41    INSERT INTO t1 SELECT a, b+10 FROM t1;42    SELECT * FROM log ORDER BY x;43  }44} {5 2 hello 2}45do_test insert3-1.2 {46  execsql {47    CREATE TABLE log2(x PRIMARY KEY,y);48    CREATE TRIGGER r2 BEFORE INSERT ON t1 BEGIN49      UPDATE log2 SET y=y+1 WHERE x=new.b;50      INSERT OR IGNORE INTO log2 VALUES(new.b,1);51    END;52    INSERT INTO t1 VALUES(453,'hi');53    SELECT * FROM log ORDER BY x;54  }55} {5 2 453 1 hello 2}56do_test insert3-1.3 {57  execsql {58    SELECT * FROM log2 ORDER BY x;59  }60} {hi 1}61ifcapable compound {62  do_test insert3-1.4.1 {63    execsql {64      INSERT INTO t1 SELECT * FROM t1;65      SELECT 'a:', x, y FROM log UNION ALL 66          SELECT 'b:', x, y FROM log2 ORDER BY x;67    }68  } {a: 5 4 b: 10 2 b: 20 1 a: 453 2 a: hello 4 b: hi 2 b: world 1}69  do_test insert3-1.4.2 {70    execsql {71      SELECT 'a:', x, y FROM log UNION ALL 72          SELECT 'b:', x, y FROM log2 ORDER BY x, y;73    }74  } {a: 5 4 b: 10 2 b: 20 1 a: 453 2 a: hello 4 b: hi 2 b: world 1}75  do_test insert3-1.5 {76    execsql {77      INSERT INTO t1(a) VALUES('xyz');78      SELECT * FROM log ORDER BY x;79    }80  } {5 4 453 2 hello 4 xyz 1}81}82 83do_test insert3-2.1 {84  execsql {85    CREATE TABLE t2(86      a INTEGER PRIMARY KEY,87      b DEFAULT 'b',88      c DEFAULT 'c'89    );90    CREATE TABLE t2dup(a,b,c);91    CREATE TRIGGER t2r1 BEFORE INSERT ON t2 BEGIN92      INSERT INTO t2dup(a,b,c) VALUES(new.a,new.b,new.c);93    END;94    INSERT INTO t2(a) VALUES(123);95    INSERT INTO t2(b) VALUES(234);96    INSERT INTO t2(c) VALUES(345);97    SELECT * FROM t2dup;98  }99} {123 b c -1 234 c -1 b 345}100do_test insert3-2.2 {101  execsql {102    DELETE FROM t2dup;103    INSERT INTO t2(a) SELECT 1 FROM t1 LIMIT 1;104    INSERT INTO t2(b) SELECT 987 FROM t1 LIMIT 1;105    INSERT INTO t2(c) SELECT 876 FROM t1 LIMIT 1;106    SELECT * FROM t2dup;107  }108} {1 b c -1 987 c -1 b 876}109 110# Test for proper detection of malformed WHEN clauses on INSERT triggers.111#112do_test insert3-3.1 {113  execsql {114    CREATE TABLE t3(a,b,c);115    CREATE TRIGGER t3r1 BEFORE INSERT on t3 WHEN nosuchcol BEGIN116      SELECT 'illegal WHEN clause';117    END;118  }119} {}120do_test insert3-3.2 {121  catchsql {122    INSERT INTO t3 VALUES(1,2,3)123  }124} {1 {no such column: nosuchcol}}125do_test insert3-3.3 {126  execsql {127    CREATE TABLE t4(a,b,c);128    CREATE TRIGGER t4r1 AFTER INSERT on t4 WHEN nosuchcol BEGIN129      SELECT 'illegal WHEN clause';130    END;131  }132} {}133do_test insert3-3.4 {134  catchsql {135    INSERT INTO t4 VALUES(1,2,3)136  }137} {1 {no such column: nosuchcol}}138 139} ;# ifcapable {trigger}140 141# Tests for the INSERT INTO ... DEFAULT VALUES construct142#143do_test insert3-3.5 {144  execsql {145    CREATE TABLE t5(146      a INTEGER PRIMARY KEY,147      b DEFAULT 'xyz'148    );149    INSERT INTO t5 DEFAULT VALUES;150    SELECT * FROM t5;151  }152} {1 xyz}153do_test insert3-3.6 {154  execsql {155    INSERT INTO t5 DEFAULT VALUES;156    SELECT * FROM t5;157  }158} {1 xyz 2 xyz}159 160ifcapable bloblit {161  do_test insert3-3.7 {162    execsql {163      CREATE TABLE t6(x,y DEFAULT 4.3, z DEFAULT x'6869');164      INSERT INTO t6 DEFAULT VALUES;165      SELECT * FROM t6;166    }167  } {{} 4.3 hi}168}169 170foreach tab [db eval {SELECT name FROM sqlite_master WHERE type = 'table'}] {171  db eval "DROP TABLE $tab"172}173db close174sqlite3 db test.db175 176#-------------------------------------------------------------------------177# While developing tests for a different feature (savepoint) the following178# sequence was found to cause an assert() in btree.c to fail. These179# tests are included to ensure that that bug is fixed.180#181do_test insert3-4.1 {182  execsql { 183    CREATE TABLE t1(a, b, c);184    CREATE INDEX i1 ON t1(a, b);185    BEGIN;186    INSERT INTO t1 VALUES(randstr(10,400),randstr(10,400),randstr(10,400));187  }188  set r "randstr(10,400)"189  for {set ii 0} {$ii < 10} {incr ii} {190    execsql "INSERT INTO t1 SELECT $r, $r, $r FROM t1"191  }192  execsql { COMMIT }193} {}194do_test insert3-4.2 {195  execsql {196    PRAGMA cache_size = 10;197    BEGIN;198      UPDATE t1 SET a = randstr(10,10) WHERE (rowid%4)==0;199      DELETE FROM t1 WHERE rowid%2;200      INSERT INTO t1 SELECT randstr(10,400), randstr(10,400), c FROM t1;201    COMMIT;202  }203} {}204 205finish_test206