1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000
1.2 +++ b/os/persistentdata/persistentstorage/sqlite3api/TEST/TclScript/insert2.test Fri Jun 15 03:10:57 2012 +0200
1.3 @@ -0,0 +1,278 @@
1.4 +# 2001 September 15
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 statement that takes is
1.16 +# result from a SELECT.
1.17 +#
1.18 +# $Id: insert2.test,v 1.19 2008/01/16 18:20:42 danielk1977 Exp $
1.19 +
1.20 +set testdir [file dirname $argv0]
1.21 +source $testdir/tester.tcl
1.22 +
1.23 +# Create some tables with data that we can select against
1.24 +#
1.25 +do_test insert2-1.0 {
1.26 + execsql {CREATE TABLE d1(n int, log int);}
1.27 + for {set i 1} {$i<=20} {incr i} {
1.28 + for {set j 0} {(1<<$j)<$i} {incr j} {}
1.29 + execsql "INSERT INTO d1 VALUES($i,$j)"
1.30 + }
1.31 + execsql {SELECT * FROM d1 ORDER BY n}
1.32 +} {1 0 2 1 3 2 4 2 5 3 6 3 7 3 8 3 9 4 10 4 11 4 12 4 13 4 14 4 15 4 16 4 17 5 18 5 19 5 20 5}
1.33 +
1.34 +# Insert into a new table from the old one.
1.35 +#
1.36 +do_test insert2-1.1.1 {
1.37 + execsql {
1.38 + CREATE TABLE t1(log int, cnt int);
1.39 + PRAGMA count_changes=on;
1.40 + }
1.41 + ifcapable explain {
1.42 + execsql {
1.43 + EXPLAIN INSERT INTO t1 SELECT log, count(*) FROM d1 GROUP BY log;
1.44 + }
1.45 + }
1.46 + execsql {
1.47 + INSERT INTO t1 SELECT log, count(*) FROM d1 GROUP BY log;
1.48 + }
1.49 +} {6}
1.50 +do_test insert2-1.1.2 {
1.51 + db changes
1.52 +} {6}
1.53 +do_test insert2-1.1.3 {
1.54 + execsql {SELECT * FROM t1 ORDER BY log}
1.55 +} {0 1 1 1 2 2 3 4 4 8 5 4}
1.56 +
1.57 +ifcapable compound {
1.58 +do_test insert2-1.2.1 {
1.59 + catch {execsql {DROP TABLE t1}}
1.60 + execsql {
1.61 + CREATE TABLE t1(log int, cnt int);
1.62 + INSERT INTO t1
1.63 + SELECT log, count(*) FROM d1 GROUP BY log
1.64 + EXCEPT SELECT n-1,log FROM d1;
1.65 + }
1.66 +} {4}
1.67 +do_test insert2-1.2.2 {
1.68 + execsql {
1.69 + SELECT * FROM t1 ORDER BY log;
1.70 + }
1.71 +} {0 1 3 4 4 8 5 4}
1.72 +do_test insert2-1.3.1 {
1.73 + catch {execsql {DROP TABLE t1}}
1.74 + execsql {
1.75 + CREATE TABLE t1(log int, cnt int);
1.76 + PRAGMA count_changes=off;
1.77 + INSERT INTO t1
1.78 + SELECT log, count(*) FROM d1 GROUP BY log
1.79 + INTERSECT SELECT n-1,log FROM d1;
1.80 + }
1.81 +} {}
1.82 +do_test insert2-1.3.2 {
1.83 + execsql {
1.84 + SELECT * FROM t1 ORDER BY log;
1.85 + }
1.86 +} {1 1 2 2}
1.87 +} ;# ifcapable compound
1.88 +execsql {PRAGMA count_changes=off;}
1.89 +
1.90 +do_test insert2-1.4 {
1.91 + catch {execsql {DROP TABLE t1}}
1.92 + set r [execsql {
1.93 + CREATE TABLE t1(log int, cnt int);
1.94 + CREATE INDEX i1 ON t1(log);
1.95 + CREATE INDEX i2 ON t1(cnt);
1.96 + INSERT INTO t1 SELECT log, count() FROM d1 GROUP BY log;
1.97 + SELECT * FROM t1 ORDER BY log;
1.98 + }]
1.99 + lappend r [execsql {SELECT cnt FROM t1 WHERE log=3}]
1.100 + lappend r [execsql {SELECT log FROM t1 WHERE cnt=4 ORDER BY log}]
1.101 +} {0 1 1 1 2 2 3 4 4 8 5 4 4 {3 5}}
1.102 +
1.103 +do_test insert2-2.0 {
1.104 + execsql {
1.105 + CREATE TABLE t3(a,b,c);
1.106 + CREATE TABLE t4(x,y);
1.107 + INSERT INTO t4 VALUES(1,2);
1.108 + SELECT * FROM t4;
1.109 + }
1.110 +} {1 2}
1.111 +do_test insert2-2.1 {
1.112 + execsql {
1.113 + INSERT INTO t3(a,c) SELECT * FROM t4;
1.114 + SELECT * FROM t3;
1.115 + }
1.116 +} {1 {} 2}
1.117 +do_test insert2-2.2 {
1.118 + execsql {
1.119 + DELETE FROM t3;
1.120 + INSERT INTO t3(c,b) SELECT * FROM t4;
1.121 + SELECT * FROM t3;
1.122 + }
1.123 +} {{} 2 1}
1.124 +do_test insert2-2.3 {
1.125 + execsql {
1.126 + DELETE FROM t3;
1.127 + INSERT INTO t3(c,a,b) SELECT x, 'hi', y FROM t4;
1.128 + SELECT * FROM t3;
1.129 + }
1.130 +} {hi 2 1}
1.131 +
1.132 +integrity_check insert2-3.0
1.133 +
1.134 +# File table t4 with lots of data
1.135 +#
1.136 +do_test insert2-3.1 {
1.137 + execsql {
1.138 + SELECT * from t4;
1.139 + }
1.140 +} {1 2}
1.141 +do_test insert2-3.2 {
1.142 + set x [db total_changes]
1.143 + execsql {
1.144 + BEGIN;
1.145 + INSERT INTO t4 VALUES(2,4);
1.146 + INSERT INTO t4 VALUES(3,6);
1.147 + INSERT INTO t4 VALUES(4,8);
1.148 + INSERT INTO t4 VALUES(5,10);
1.149 + INSERT INTO t4 VALUES(6,12);
1.150 + INSERT INTO t4 VALUES(7,14);
1.151 + INSERT INTO t4 VALUES(8,16);
1.152 + INSERT INTO t4 VALUES(9,18);
1.153 + INSERT INTO t4 VALUES(10,20);
1.154 + COMMIT;
1.155 + }
1.156 + expr [db total_changes] - $x
1.157 +} {9}
1.158 +do_test insert2-3.2.1 {
1.159 + execsql {
1.160 + SELECT count(*) FROM t4;
1.161 + }
1.162 +} {10}
1.163 +do_test insert2-3.3 {
1.164 + ifcapable subquery {
1.165 + execsql {
1.166 + BEGIN;
1.167 + INSERT INTO t4 SELECT x+(SELECT max(x) FROM t4),y FROM t4;
1.168 + INSERT INTO t4 SELECT x+(SELECT max(x) FROM t4),y FROM t4;
1.169 + INSERT INTO t4 SELECT x+(SELECT max(x) FROM t4),y FROM t4;
1.170 + INSERT INTO t4 SELECT x+(SELECT max(x) FROM t4),y FROM t4;
1.171 + COMMIT;
1.172 + SELECT count(*) FROM t4;
1.173 + }
1.174 + } else {
1.175 + db function max_x_t4 {execsql {SELECT max(x) FROM t4}}
1.176 + execsql {
1.177 + BEGIN;
1.178 + INSERT INTO t4 SELECT x+max_x_t4() ,y FROM t4;
1.179 + INSERT INTO t4 SELECT x+max_x_t4() ,y FROM t4;
1.180 + INSERT INTO t4 SELECT x+max_x_t4() ,y FROM t4;
1.181 + INSERT INTO t4 SELECT x+max_x_t4() ,y FROM t4;
1.182 + COMMIT;
1.183 + SELECT count(*) FROM t4;
1.184 + }
1.185 + }
1.186 +} {160}
1.187 +do_test insert2-3.4 {
1.188 + execsql {
1.189 + BEGIN;
1.190 + UPDATE t4 SET y='lots of data for the row where x=' || x
1.191 + || ' and y=' || y || ' - even more data to fill space';
1.192 + COMMIT;
1.193 + SELECT count(*) FROM t4;
1.194 + }
1.195 +} {160}
1.196 +do_test insert2-3.5 {
1.197 + ifcapable subquery {
1.198 + execsql {
1.199 + BEGIN;
1.200 + INSERT INTO t4 SELECT x+(SELECT max(x)+1 FROM t4),y FROM t4;
1.201 + SELECT count(*) from t4;
1.202 + ROLLBACK;
1.203 + }
1.204 + } else {
1.205 + execsql {
1.206 + BEGIN;
1.207 + INSERT INTO t4 SELECT x+max_x_t4()+1,y FROM t4;
1.208 + SELECT count(*) from t4;
1.209 + ROLLBACK;
1.210 + }
1.211 + }
1.212 +} {320}
1.213 +do_test insert2-3.6 {
1.214 + execsql {
1.215 + SELECT count(*) FROM t4;
1.216 + }
1.217 +} {160}
1.218 +do_test insert2-3.7 {
1.219 + execsql {
1.220 + BEGIN;
1.221 + DELETE FROM t4 WHERE x!=123;
1.222 + SELECT count(*) FROM t4;
1.223 + ROLLBACK;
1.224 + }
1.225 +} {1}
1.226 +do_test insert2-3.8 {
1.227 + db changes
1.228 +} {159}
1.229 +integrity_check insert2-3.9
1.230 +
1.231 +# Ticket #901
1.232 +#
1.233 +ifcapable tempdb {
1.234 + do_test insert2-4.1 {
1.235 + execsql {
1.236 + CREATE TABLE Dependencies(depId integer primary key,
1.237 + class integer, name str, flag str);
1.238 + CREATE TEMPORARY TABLE DepCheck(troveId INT, depNum INT,
1.239 + flagCount INT, isProvides BOOL, class INTEGER, name STRING,
1.240 + flag STRING);
1.241 + INSERT INTO DepCheck
1.242 + VALUES(-1, 0, 1, 0, 2, 'libc.so.6', 'GLIBC_2.0');
1.243 + INSERT INTO Dependencies
1.244 + SELECT DISTINCT
1.245 + NULL,
1.246 + DepCheck.class,
1.247 + DepCheck.name,
1.248 + DepCheck.flag
1.249 + FROM DepCheck LEFT OUTER JOIN Dependencies ON
1.250 + DepCheck.class == Dependencies.class AND
1.251 + DepCheck.name == Dependencies.name AND
1.252 + DepCheck.flag == Dependencies.flag
1.253 + WHERE
1.254 + Dependencies.depId is NULL;
1.255 + };
1.256 + } {}
1.257 +}
1.258 +
1.259 +#--------------------------------------------------------------------
1.260 +# Test that the INSERT works when the SELECT statement (a) references
1.261 +# the table being inserted into and (b) is optimized to use an index
1.262 +# only.
1.263 +do_test insert2-5.1 {
1.264 + execsql {
1.265 + CREATE TABLE t2(a, b);
1.266 + INSERT INTO t2 VALUES(1, 2);
1.267 + CREATE INDEX t2i1 ON t2(a);
1.268 + INSERT INTO t2 SELECT a, 3 FROM t2 WHERE a = 1;
1.269 + SELECT * FROM t2;
1.270 + }
1.271 +} {1 2 1 3}
1.272 +ifcapable subquery {
1.273 + do_test insert2-5.2 {
1.274 + execsql {
1.275 + INSERT INTO t2 SELECT (SELECT a FROM t2), 4;
1.276 + SELECT * FROM t2;
1.277 + }
1.278 + } {1 2 1 3 1 4}
1.279 +}
1.280 +
1.281 +finish_test