1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000
1.2 +++ b/os/persistentdata/persistentstorage/sqlite3api/TEST/TclScript/insert4.test Fri Jun 15 03:10:57 2012 +0200
1.3 @@ -0,0 +1,306 @@
1.4 +# 2007 January 24
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 the INSERT transfer optimization.
1.16 +#
1.17 +# $Id: insert4.test,v 1.10 2008/01/21 16:22:46 drh Exp $
1.18 +
1.19 +set testdir [file dirname $argv0]
1.20 +source $testdir/tester.tcl
1.21 +
1.22 +ifcapable !view||!subquery {
1.23 + finish_test
1.24 + return
1.25 +}
1.26 +
1.27 +# The sqlite3_xferopt_count variable is incremented whenever the
1.28 +# insert transfer optimization applies.
1.29 +#
1.30 +# This procedure runs a test to see if the sqlite3_xferopt_count is
1.31 +# set to N.
1.32 +#
1.33 +proc xferopt_test {testname N} {
1.34 + do_test $testname {set ::sqlite3_xferopt_count} $N
1.35 +}
1.36 +
1.37 +# Create tables used for testing.
1.38 +#
1.39 +execsql {
1.40 + PRAGMA legacy_file_format = 0;
1.41 + CREATE TABLE t1(a int, b int, check(b>a));
1.42 + CREATE TABLE t2(x int, y int);
1.43 + CREATE VIEW v2 AS SELECT y, x FROM t2;
1.44 + CREATE TABLE t3(a int, b int);
1.45 +}
1.46 +
1.47 +# Ticket #2252. Make sure the an INSERT from identical tables
1.48 +# does not violate constraints.
1.49 +#
1.50 +do_test insert4-1.1 {
1.51 + set sqlite3_xferopt_count 0
1.52 + execsql {
1.53 + DELETE FROM t1;
1.54 + DELETE FROM t2;
1.55 + INSERT INTO t2 VALUES(9,1);
1.56 + }
1.57 + catchsql {
1.58 + INSERT INTO t1 SELECT * FROM t2;
1.59 + }
1.60 +} {1 {constraint failed}}
1.61 +xferopt_test insert4-1.2 0
1.62 +do_test insert4-1.3 {
1.63 + execsql {
1.64 + SELECT * FROM t1;
1.65 + }
1.66 +} {}
1.67 +
1.68 +# Tests to make sure that the transfer optimization is not occurring
1.69 +# when it is not a valid optimization.
1.70 +#
1.71 +# The SELECT must be against a real table.
1.72 +do_test insert4-2.1.1 {
1.73 + execsql {
1.74 + DELETE FROM t1;
1.75 + INSERT INTO t1 SELECT 4, 8;
1.76 + SELECT * FROM t1;
1.77 + }
1.78 +} {4 8}
1.79 +xferopt_test insert4-2.1.2 0
1.80 +do_test insert4-2.2.1 {
1.81 + catchsql {
1.82 + DELETE FROM t1;
1.83 + INSERT INTO t1 SELECT * FROM v2;
1.84 + SELECT * FROM t1;
1.85 + }
1.86 +} {0 {1 9}}
1.87 +xferopt_test insert4-2.2.2 0
1.88 +
1.89 +# Do not run the transfer optimization if there is a LIMIT clause
1.90 +#
1.91 +do_test insert4-2.3.1 {
1.92 + execsql {
1.93 + DELETE FROM t2;
1.94 + INSERT INTO t2 VALUES(9,1);
1.95 + INSERT INTO t2 SELECT y, x FROM t2;
1.96 + INSERT INTO t3 SELECT * FROM t2 LIMIT 1;
1.97 + SELECT * FROM t3;
1.98 + }
1.99 +} {9 1}
1.100 +xferopt_test insert4-2.3.2 0
1.101 +do_test insert4-2.3.3 {
1.102 + catchsql {
1.103 + DELETE FROM t1;
1.104 + INSERT INTO t1 SELECT * FROM t2 LIMIT 1;
1.105 + SELECT * FROM t1;
1.106 + }
1.107 +} {1 {constraint failed}}
1.108 +xferopt_test insert4-2.3.4 0
1.109 +
1.110 +# Do not run the transfer optimization if there is a DISTINCT
1.111 +#
1.112 +do_test insert4-2.4.1 {
1.113 + execsql {
1.114 + DELETE FROM t3;
1.115 + INSERT INTO t3 SELECT DISTINCT * FROM t2;
1.116 + SELECT * FROM t3;
1.117 + }
1.118 +} {1 9 9 1}
1.119 +xferopt_test insert4-2.4.2 0
1.120 +do_test insert4-2.4.3 {
1.121 + catchsql {
1.122 + DELETE FROM t1;
1.123 + INSERT INTO t1 SELECT DISTINCT * FROM t2;
1.124 + }
1.125 +} {1 {constraint failed}}
1.126 +xferopt_test insert4-2.4.4 0
1.127 +
1.128 +# The following procedure constructs two tables then tries to transfer
1.129 +# data from one table to the other. Checks are made to make sure the
1.130 +# transfer is successful and that the transfer optimization was used or
1.131 +# not, as appropriate.
1.132 +#
1.133 +# xfer_check TESTID XFER-USED INIT-DATA DEST-SCHEMA SRC-SCHEMA
1.134 +#
1.135 +# The TESTID argument is the symbolic name for this test. The XFER-USED
1.136 +# argument is true if the transfer optimization should be employed and
1.137 +# false if not. INIT-DATA is a single row of data that is to be
1.138 +# transfered. DEST-SCHEMA and SRC-SCHEMA are table declarations for
1.139 +# the destination and source tables.
1.140 +#
1.141 +proc xfer_check {testid xferused initdata destschema srcschema} {
1.142 + execsql "CREATE TABLE dest($destschema)"
1.143 + execsql "CREATE TABLE src($srcschema)"
1.144 + execsql "INSERT INTO src VALUES([join $initdata ,])"
1.145 + set ::sqlite3_xferopt_count 0
1.146 + do_test $testid.1 {
1.147 + execsql {
1.148 + INSERT INTO dest SELECT * FROM src;
1.149 + SELECT * FROM dest;
1.150 + }
1.151 + } $initdata
1.152 + do_test $testid.2 {
1.153 + set ::sqlite3_xferopt_count
1.154 + } $xferused
1.155 + execsql {
1.156 + DROP TABLE dest;
1.157 + DROP TABLE src;
1.158 + }
1.159 +}
1.160 +
1.161 +
1.162 +# Do run the transfer optimization if tables have identical
1.163 +# CHECK constraints.
1.164 +#
1.165 +xfer_check insert4-3.1 1 {1 9} \
1.166 + {a int, b int CHECK(b>a)} \
1.167 + {x int, y int CHECK(y>x)}
1.168 +xfer_check insert4-3.2 1 {1 9} \
1.169 + {a int, b int CHECK(b>a)} \
1.170 + {x int CHECK(y>x), y int}
1.171 +
1.172 +# Do run the transfer optimization if the destination table lacks
1.173 +# any CHECK constraints regardless of whether or not there are CHECK
1.174 +# constraints on the source table.
1.175 +#
1.176 +xfer_check insert4-3.3 1 {1 9} \
1.177 + {a int, b int} \
1.178 + {x int, y int CHECK(y>x)}
1.179 +
1.180 +# Do run the transfer optimization if the destination table omits
1.181 +# NOT NULL constraints that the source table has.
1.182 +#
1.183 +xfer_check insert4-3.4 0 {1 9} \
1.184 + {a int, b int CHECK(b>a)} \
1.185 + {x int, y int}
1.186 +
1.187 +# Do not run the optimization if the destination has NOT NULL
1.188 +# constraints that the source table lacks.
1.189 +#
1.190 +xfer_check insert4-3.5 0 {1 9} \
1.191 + {a int, b int NOT NULL} \
1.192 + {x int, y int}
1.193 +xfer_check insert4-3.6 0 {1 9} \
1.194 + {a int, b int NOT NULL} \
1.195 + {x int NOT NULL, y int}
1.196 +xfer_check insert4-3.7 0 {1 9} \
1.197 + {a int NOT NULL, b int NOT NULL} \
1.198 + {x int NOT NULL, y int}
1.199 +xfer_check insert4-3.8 0 {1 9} \
1.200 + {a int NOT NULL, b int} \
1.201 + {x int, y int}
1.202 +
1.203 +
1.204 +# Do run the transfer optimization if the destination table and
1.205 +# source table have the same NOT NULL constraints or if the
1.206 +# source table has extra NOT NULL constraints.
1.207 +#
1.208 +xfer_check insert4-3.9 1 {1 9} \
1.209 + {a int, b int} \
1.210 + {x int NOT NULL, y int}
1.211 +xfer_check insert4-3.10 1 {1 9} \
1.212 + {a int, b int} \
1.213 + {x int NOT NULL, y int NOT NULL}
1.214 +xfer_check insert4-3.11 1 {1 9} \
1.215 + {a int NOT NULL, b int} \
1.216 + {x int NOT NULL, y int NOT NULL}
1.217 +xfer_check insert4-3.12 1 {1 9} \
1.218 + {a int, b int NOT NULL} \
1.219 + {x int NOT NULL, y int NOT NULL}
1.220 +
1.221 +# Do not run the optimization if any corresponding table
1.222 +# columns have different affinities.
1.223 +#
1.224 +xfer_check insert4-3.20 0 {1 9} \
1.225 + {a text, b int} \
1.226 + {x int, b int}
1.227 +xfer_check insert4-3.21 0 {1 9} \
1.228 + {a int, b int} \
1.229 + {x text, b int}
1.230 +
1.231 +# "int" and "integer" are equivalent so the optimization should
1.232 +# run here.
1.233 +#
1.234 +xfer_check insert4-3.22 1 {1 9} \
1.235 + {a int, b int} \
1.236 + {x integer, b int}
1.237 +
1.238 +# Ticket #2291.
1.239 +#
1.240 +
1.241 +do_test insert4-4.1a {
1.242 + execsql {CREATE TABLE t4(a, b, UNIQUE(a,b))}
1.243 +} {}
1.244 +ifcapable vacuum {
1.245 + do_test insert4-4.1b {
1.246 + execsql {
1.247 + INSERT INTO t4 VALUES(NULL,0);
1.248 + INSERT INTO t4 VALUES(NULL,1);
1.249 + INSERT INTO t4 VALUES(NULL,1);
1.250 + VACUUM;
1.251 + }
1.252 + } {}
1.253 +}
1.254 +
1.255 +# Check some error conditions:
1.256 +#
1.257 +do_test insert4-5.1 {
1.258 + # Table does not exist.
1.259 + catchsql { INSERT INTO t2 SELECT * FROM nosuchtable }
1.260 +} {1 {no such table: nosuchtable}}
1.261 +do_test insert4-5.2 {
1.262 + # Number of columns does not match.
1.263 + catchsql {
1.264 + CREATE TABLE t5(a, b, c);
1.265 + INSERT INTO t4 SELECT * FROM t5;
1.266 + }
1.267 +} {1 {table t4 has 2 columns but 3 values were supplied}}
1.268 +
1.269 +do_test insert4-6.1 {
1.270 + set ::sqlite3_xferopt_count 0
1.271 + execsql {
1.272 + CREATE INDEX t2_i2 ON t2(x, y COLLATE nocase);
1.273 + CREATE INDEX t2_i1 ON t2(x ASC, y DESC);
1.274 + CREATE INDEX t3_i1 ON t3(a, b);
1.275 + INSERT INTO t2 SELECT * FROM t3;
1.276 + }
1.277 + set ::sqlite3_xferopt_count
1.278 +} {0}
1.279 +do_test insert4-6.2 {
1.280 + set ::sqlite3_xferopt_count 0
1.281 + execsql {
1.282 + DROP INDEX t2_i2;
1.283 + INSERT INTO t2 SELECT * FROM t3;
1.284 + }
1.285 + set ::sqlite3_xferopt_count
1.286 +} {0}
1.287 +do_test insert4-6.3 {
1.288 + set ::sqlite3_xferopt_count 0
1.289 + execsql {
1.290 + DROP INDEX t2_i1;
1.291 + CREATE INDEX t2_i1 ON t2(x ASC, y ASC);
1.292 + INSERT INTO t2 SELECT * FROM t3;
1.293 + }
1.294 + set ::sqlite3_xferopt_count
1.295 +} {1}
1.296 +do_test insert4-6.4 {
1.297 + set ::sqlite3_xferopt_count 0
1.298 + execsql {
1.299 + DROP INDEX t2_i1;
1.300 + CREATE INDEX t2_i1 ON t2(x ASC, y COLLATE RTRIM);
1.301 + INSERT INTO t2 SELECT * FROM t3;
1.302 + }
1.303 + set ::sqlite3_xferopt_count
1.304 +} {0}
1.305 +
1.306 +
1.307 +
1.308 +
1.309 +finish_test