--- /dev/null Thu Jan 01 00:00:00 1970 +0000
+++ b/persistentstorage/sqlite3api/TEST/TclScript/insert4.test Fri Jan 22 11:06:30 2010 +0200
@@ -0,0 +1,306 @@
+# 2007 January 24
+#
+# 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 the INSERT transfer optimization.
+#
+# $Id: insert4.test,v 1.10 2008/01/21 16:22:46 drh Exp $
+
+set testdir [file dirname $argv0]
+source $testdir/tester.tcl
+
+ifcapable !view||!subquery {
+ finish_test
+ return
+}
+
+# The sqlite3_xferopt_count variable is incremented whenever the
+# insert transfer optimization applies.
+#
+# This procedure runs a test to see if the sqlite3_xferopt_count is
+# set to N.
+#
+proc xferopt_test {testname N} {
+ do_test $testname {set ::sqlite3_xferopt_count} $N
+}
+
+# Create tables used for testing.
+#
+execsql {
+ PRAGMA legacy_file_format = 0;
+ CREATE TABLE t1(a int, b int, check(b>a));
+ CREATE TABLE t2(x int, y int);
+ CREATE VIEW v2 AS SELECT y, x FROM t2;
+ CREATE TABLE t3(a int, b int);
+}
+
+# Ticket #2252. Make sure the an INSERT from identical tables
+# does not violate constraints.
+#
+do_test insert4-1.1 {
+ set sqlite3_xferopt_count 0
+ execsql {
+ DELETE FROM t1;
+ DELETE FROM t2;
+ INSERT INTO t2 VALUES(9,1);
+ }
+ catchsql {
+ INSERT INTO t1 SELECT * FROM t2;
+ }
+} {1 {constraint failed}}
+xferopt_test insert4-1.2 0
+do_test insert4-1.3 {
+ execsql {
+ SELECT * FROM t1;
+ }
+} {}
+
+# Tests to make sure that the transfer optimization is not occurring
+# when it is not a valid optimization.
+#
+# The SELECT must be against a real table.
+do_test insert4-2.1.1 {
+ execsql {
+ DELETE FROM t1;
+ INSERT INTO t1 SELECT 4, 8;
+ SELECT * FROM t1;
+ }
+} {4 8}
+xferopt_test insert4-2.1.2 0
+do_test insert4-2.2.1 {
+ catchsql {
+ DELETE FROM t1;
+ INSERT INTO t1 SELECT * FROM v2;
+ SELECT * FROM t1;
+ }
+} {0 {1 9}}
+xferopt_test insert4-2.2.2 0
+
+# Do not run the transfer optimization if there is a LIMIT clause
+#
+do_test insert4-2.3.1 {
+ execsql {
+ DELETE FROM t2;
+ INSERT INTO t2 VALUES(9,1);
+ INSERT INTO t2 SELECT y, x FROM t2;
+ INSERT INTO t3 SELECT * FROM t2 LIMIT 1;
+ SELECT * FROM t3;
+ }
+} {9 1}
+xferopt_test insert4-2.3.2 0
+do_test insert4-2.3.3 {
+ catchsql {
+ DELETE FROM t1;
+ INSERT INTO t1 SELECT * FROM t2 LIMIT 1;
+ SELECT * FROM t1;
+ }
+} {1 {constraint failed}}
+xferopt_test insert4-2.3.4 0
+
+# Do not run the transfer optimization if there is a DISTINCT
+#
+do_test insert4-2.4.1 {
+ execsql {
+ DELETE FROM t3;
+ INSERT INTO t3 SELECT DISTINCT * FROM t2;
+ SELECT * FROM t3;
+ }
+} {1 9 9 1}
+xferopt_test insert4-2.4.2 0
+do_test insert4-2.4.3 {
+ catchsql {
+ DELETE FROM t1;
+ INSERT INTO t1 SELECT DISTINCT * FROM t2;
+ }
+} {1 {constraint failed}}
+xferopt_test insert4-2.4.4 0
+
+# The following procedure constructs two tables then tries to transfer
+# data from one table to the other. Checks are made to make sure the
+# transfer is successful and that the transfer optimization was used or
+# not, as appropriate.
+#
+# xfer_check TESTID XFER-USED INIT-DATA DEST-SCHEMA SRC-SCHEMA
+#
+# The TESTID argument is the symbolic name for this test. The XFER-USED
+# argument is true if the transfer optimization should be employed and
+# false if not. INIT-DATA is a single row of data that is to be
+# transfered. DEST-SCHEMA and SRC-SCHEMA are table declarations for
+# the destination and source tables.
+#
+proc xfer_check {testid xferused initdata destschema srcschema} {
+ execsql "CREATE TABLE dest($destschema)"
+ execsql "CREATE TABLE src($srcschema)"
+ execsql "INSERT INTO src VALUES([join $initdata ,])"
+ set ::sqlite3_xferopt_count 0
+ do_test $testid.1 {
+ execsql {
+ INSERT INTO dest SELECT * FROM src;
+ SELECT * FROM dest;
+ }
+ } $initdata
+ do_test $testid.2 {
+ set ::sqlite3_xferopt_count
+ } $xferused
+ execsql {
+ DROP TABLE dest;
+ DROP TABLE src;
+ }
+}
+
+
+# Do run the transfer optimization if tables have identical
+# CHECK constraints.
+#
+xfer_check insert4-3.1 1 {1 9} \
+ {a int, b int CHECK(b>a)} \
+ {x int, y int CHECK(y>x)}
+xfer_check insert4-3.2 1 {1 9} \
+ {a int, b int CHECK(b>a)} \
+ {x int CHECK(y>x), y int}
+
+# Do run the transfer optimization if the destination table lacks
+# any CHECK constraints regardless of whether or not there are CHECK
+# constraints on the source table.
+#
+xfer_check insert4-3.3 1 {1 9} \
+ {a int, b int} \
+ {x int, y int CHECK(y>x)}
+
+# Do run the transfer optimization if the destination table omits
+# NOT NULL constraints that the source table has.
+#
+xfer_check insert4-3.4 0 {1 9} \
+ {a int, b int CHECK(b>a)} \
+ {x int, y int}
+
+# Do not run the optimization if the destination has NOT NULL
+# constraints that the source table lacks.
+#
+xfer_check insert4-3.5 0 {1 9} \
+ {a int, b int NOT NULL} \
+ {x int, y int}
+xfer_check insert4-3.6 0 {1 9} \
+ {a int, b int NOT NULL} \
+ {x int NOT NULL, y int}
+xfer_check insert4-3.7 0 {1 9} \
+ {a int NOT NULL, b int NOT NULL} \
+ {x int NOT NULL, y int}
+xfer_check insert4-3.8 0 {1 9} \
+ {a int NOT NULL, b int} \
+ {x int, y int}
+
+
+# Do run the transfer optimization if the destination table and
+# source table have the same NOT NULL constraints or if the
+# source table has extra NOT NULL constraints.
+#
+xfer_check insert4-3.9 1 {1 9} \
+ {a int, b int} \
+ {x int NOT NULL, y int}
+xfer_check insert4-3.10 1 {1 9} \
+ {a int, b int} \
+ {x int NOT NULL, y int NOT NULL}
+xfer_check insert4-3.11 1 {1 9} \
+ {a int NOT NULL, b int} \
+ {x int NOT NULL, y int NOT NULL}
+xfer_check insert4-3.12 1 {1 9} \
+ {a int, b int NOT NULL} \
+ {x int NOT NULL, y int NOT NULL}
+
+# Do not run the optimization if any corresponding table
+# columns have different affinities.
+#
+xfer_check insert4-3.20 0 {1 9} \
+ {a text, b int} \
+ {x int, b int}
+xfer_check insert4-3.21 0 {1 9} \
+ {a int, b int} \
+ {x text, b int}
+
+# "int" and "integer" are equivalent so the optimization should
+# run here.
+#
+xfer_check insert4-3.22 1 {1 9} \
+ {a int, b int} \
+ {x integer, b int}
+
+# Ticket #2291.
+#
+
+do_test insert4-4.1a {
+ execsql {CREATE TABLE t4(a, b, UNIQUE(a,b))}
+} {}
+ifcapable vacuum {
+ do_test insert4-4.1b {
+ execsql {
+ INSERT INTO t4 VALUES(NULL,0);
+ INSERT INTO t4 VALUES(NULL,1);
+ INSERT INTO t4 VALUES(NULL,1);
+ VACUUM;
+ }
+ } {}
+}
+
+# Check some error conditions:
+#
+do_test insert4-5.1 {
+ # Table does not exist.
+ catchsql { INSERT INTO t2 SELECT * FROM nosuchtable }
+} {1 {no such table: nosuchtable}}
+do_test insert4-5.2 {
+ # Number of columns does not match.
+ catchsql {
+ CREATE TABLE t5(a, b, c);
+ INSERT INTO t4 SELECT * FROM t5;
+ }
+} {1 {table t4 has 2 columns but 3 values were supplied}}
+
+do_test insert4-6.1 {
+ set ::sqlite3_xferopt_count 0
+ execsql {
+ CREATE INDEX t2_i2 ON t2(x, y COLLATE nocase);
+ CREATE INDEX t2_i1 ON t2(x ASC, y DESC);
+ CREATE INDEX t3_i1 ON t3(a, b);
+ INSERT INTO t2 SELECT * FROM t3;
+ }
+ set ::sqlite3_xferopt_count
+} {0}
+do_test insert4-6.2 {
+ set ::sqlite3_xferopt_count 0
+ execsql {
+ DROP INDEX t2_i2;
+ INSERT INTO t2 SELECT * FROM t3;
+ }
+ set ::sqlite3_xferopt_count
+} {0}
+do_test insert4-6.3 {
+ set ::sqlite3_xferopt_count 0
+ execsql {
+ DROP INDEX t2_i1;
+ CREATE INDEX t2_i1 ON t2(x ASC, y ASC);
+ INSERT INTO t2 SELECT * FROM t3;
+ }
+ set ::sqlite3_xferopt_count
+} {1}
+do_test insert4-6.4 {
+ set ::sqlite3_xferopt_count 0
+ execsql {
+ DROP INDEX t2_i1;
+ CREATE INDEX t2_i1 ON t2(x ASC, y COLLATE RTRIM);
+ INSERT INTO t2 SELECT * FROM t3;
+ }
+ set ::sqlite3_xferopt_count
+} {0}
+
+
+
+
+finish_test