1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000
1.2 +++ b/os/persistentdata/persistentstorage/sqlite3api/TEST/TclScript/misc1.test Fri Jun 15 03:10:57 2012 +0200
1.3 @@ -0,0 +1,585 @@
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.
1.15 +#
1.16 +# This file implements tests for miscellanous features that were
1.17 +# left out of other test files.
1.18 +#
1.19 +# $Id: misc1.test,v 1.42 2007/11/05 14:58:23 drh Exp $
1.20 +
1.21 +set testdir [file dirname $argv0]
1.22 +source $testdir/tester.tcl
1.23 +
1.24 +# Mimic the SQLite 2 collation type NUMERIC.
1.25 +db collate numeric numeric_collate
1.26 +proc numeric_collate {lhs rhs} {
1.27 + if {$lhs == $rhs} {return 0}
1.28 + return [expr ($lhs>$rhs)?1:-1]
1.29 +}
1.30 +
1.31 +# Mimic the SQLite 2 collation type TEXT.
1.32 +db collate text text_collate
1.33 +proc numeric_collate {lhs rhs} {
1.34 + return [string compare $lhs $rhs]
1.35 +}
1.36 +
1.37 +# Test the creation and use of tables that have a large number
1.38 +# of columns.
1.39 +#
1.40 +do_test misc1-1.1 {
1.41 + set cmd "CREATE TABLE manycol(x0 text"
1.42 + for {set i 1} {$i<=99} {incr i} {
1.43 + append cmd ",x$i text"
1.44 + }
1.45 + append cmd ")";
1.46 + execsql $cmd
1.47 + set cmd "INSERT INTO manycol VALUES(0"
1.48 + for {set i 1} {$i<=99} {incr i} {
1.49 + append cmd ",$i"
1.50 + }
1.51 + append cmd ")";
1.52 + execsql $cmd
1.53 + execsql "SELECT x99 FROM manycol"
1.54 +} 99
1.55 +do_test misc1-1.2 {
1.56 + execsql {SELECT x0, x10, x25, x50, x75 FROM manycol}
1.57 +} {0 10 25 50 75}
1.58 +do_test misc1-1.3.1 {
1.59 + for {set j 100} {$j<=1000} {incr j 100} {
1.60 + set cmd "INSERT INTO manycol VALUES($j"
1.61 + for {set i 1} {$i<=99} {incr i} {
1.62 + append cmd ",[expr {$i+$j}]"
1.63 + }
1.64 + append cmd ")"
1.65 + execsql $cmd
1.66 + }
1.67 + execsql {SELECT x50 FROM manycol ORDER BY x80+0}
1.68 +} {50 150 250 350 450 550 650 750 850 950 1050}
1.69 +do_test misc1-1.3.2 {
1.70 + execsql {SELECT x50 FROM manycol ORDER BY x80}
1.71 +} {1050 150 250 350 450 550 650 750 50 850 950}
1.72 +do_test misc1-1.4 {
1.73 + execsql {SELECT x75 FROM manycol WHERE x50=350}
1.74 +} 375
1.75 +do_test misc1-1.5 {
1.76 + execsql {SELECT x50 FROM manycol WHERE x99=599}
1.77 +} 550
1.78 +do_test misc1-1.6 {
1.79 + execsql {CREATE INDEX manycol_idx1 ON manycol(x99)}
1.80 + execsql {SELECT x50 FROM manycol WHERE x99=899}
1.81 +} 850
1.82 +do_test misc1-1.7 {
1.83 + execsql {SELECT count(*) FROM manycol}
1.84 +} 11
1.85 +do_test misc1-1.8 {
1.86 + execsql {DELETE FROM manycol WHERE x98=1234}
1.87 + execsql {SELECT count(*) FROM manycol}
1.88 +} 11
1.89 +do_test misc1-1.9 {
1.90 + execsql {DELETE FROM manycol WHERE x98=998}
1.91 + execsql {SELECT count(*) FROM manycol}
1.92 +} 10
1.93 +do_test misc1-1.10 {
1.94 + execsql {DELETE FROM manycol WHERE x99=500}
1.95 + execsql {SELECT count(*) FROM manycol}
1.96 +} 10
1.97 +do_test misc1-1.11 {
1.98 + execsql {DELETE FROM manycol WHERE x99=599}
1.99 + execsql {SELECT count(*) FROM manycol}
1.100 +} 9
1.101 +
1.102 +# Check GROUP BY expressions that name two or more columns.
1.103 +#
1.104 +do_test misc1-2.1 {
1.105 + execsql {
1.106 + BEGIN TRANSACTION;
1.107 + CREATE TABLE agger(one text, two text, three text, four text);
1.108 + INSERT INTO agger VALUES(1, 'one', 'hello', 'yes');
1.109 + INSERT INTO agger VALUES(2, 'two', 'howdy', 'no');
1.110 + INSERT INTO agger VALUES(3, 'thr', 'howareya', 'yes');
1.111 + INSERT INTO agger VALUES(4, 'two', 'lothere', 'yes');
1.112 + INSERT INTO agger VALUES(5, 'one', 'atcha', 'yes');
1.113 + INSERT INTO agger VALUES(6, 'two', 'hello', 'no');
1.114 + COMMIT
1.115 + }
1.116 + execsql {SELECT count(*) FROM agger}
1.117 +} 6
1.118 +do_test misc1-2.2 {
1.119 + execsql {SELECT sum(one), two, four FROM agger
1.120 + GROUP BY two, four ORDER BY sum(one) desc}
1.121 +} {8 two no 6 one yes 4 two yes 3 thr yes}
1.122 +do_test misc1-2.3 {
1.123 + execsql {SELECT sum((one)), (two), (four) FROM agger
1.124 + GROUP BY (two), (four) ORDER BY sum(one) desc}
1.125 +} {8 two no 6 one yes 4 two yes 3 thr yes}
1.126 +
1.127 +# Here's a test for a bug found by Joel Lucsy. The code below
1.128 +# was causing an assertion failure.
1.129 +#
1.130 +do_test misc1-3.1 {
1.131 + set r [execsql {
1.132 + CREATE TABLE t1(a);
1.133 + INSERT INTO t1 VALUES('hi');
1.134 + PRAGMA full_column_names=on;
1.135 + SELECT rowid, * FROM t1;
1.136 + }]
1.137 + lindex $r 1
1.138 +} {hi}
1.139 +
1.140 +# Here's a test for yet another bug found by Joel Lucsy. The code
1.141 +# below was causing an assertion failure.
1.142 +#
1.143 +do_test misc1-4.1 {
1.144 + execsql {
1.145 + BEGIN;
1.146 + CREATE TABLE t2(a);
1.147 + INSERT INTO t2 VALUES('This is a long string to use up a lot of disk -');
1.148 + UPDATE t2 SET a=a||a||a||a;
1.149 + INSERT INTO t2 SELECT '1 - ' || a FROM t2;
1.150 + INSERT INTO t2 SELECT '2 - ' || a FROM t2;
1.151 + INSERT INTO t2 SELECT '3 - ' || a FROM t2;
1.152 + INSERT INTO t2 SELECT '4 - ' || a FROM t2;
1.153 + INSERT INTO t2 SELECT '5 - ' || a FROM t2;
1.154 + INSERT INTO t2 SELECT '6 - ' || a FROM t2;
1.155 + COMMIT;
1.156 + SELECT count(*) FROM t2;
1.157 + }
1.158 +} {64}
1.159 +
1.160 +# Make sure we actually see a semicolon or end-of-file in the SQL input
1.161 +# before executing a command. Thus if "WHERE" is misspelled on an UPDATE,
1.162 +# the user won't accidently update every record.
1.163 +#
1.164 +do_test misc1-5.1 {
1.165 + catchsql {
1.166 + CREATE TABLE t3(a,b);
1.167 + INSERT INTO t3 VALUES(1,2);
1.168 + INSERT INTO t3 VALUES(3,4);
1.169 + UPDATE t3 SET a=0 WHEREwww b=2;
1.170 + }
1.171 +} {1 {near "WHEREwww": syntax error}}
1.172 +do_test misc1-5.2 {
1.173 + execsql {
1.174 + SELECT * FROM t3 ORDER BY a;
1.175 + }
1.176 +} {1 2 3 4}
1.177 +
1.178 +# Certain keywords (especially non-standard keywords like "REPLACE") can
1.179 +# also be used as identifiers. The way this works in the parser is that
1.180 +# the parser first detects a syntax error, the error handling routine
1.181 +# sees that the special keyword caused the error, then replaces the keyword
1.182 +# with "ID" and tries again.
1.183 +#
1.184 +# Check the operation of this logic.
1.185 +#
1.186 +do_test misc1-6.1 {
1.187 + catchsql {
1.188 + CREATE TABLE t4(
1.189 + abort, asc, begin, cluster, conflict, copy, delimiters, desc, end,
1.190 + explain, fail, ignore, key, offset, pragma, replace, temp,
1.191 + vacuum, view
1.192 + );
1.193 + }
1.194 +} {0 {}}
1.195 +do_test misc1-6.2 {
1.196 + catchsql {
1.197 + INSERT INTO t4
1.198 + VALUES(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19);
1.199 + }
1.200 +} {0 {}}
1.201 +do_test misc1-6.3 {
1.202 + execsql {
1.203 + SELECT * FROM t4
1.204 + }
1.205 +} {1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19}
1.206 +do_test misc1-6.4 {
1.207 + execsql {
1.208 + SELECT abort+asc,max(key,pragma,temp) FROM t4
1.209 + }
1.210 +} {3 17}
1.211 +
1.212 +# Test for multi-column primary keys, and for multiple primary keys.
1.213 +#
1.214 +do_test misc1-7.1 {
1.215 + catchsql {
1.216 + CREATE TABLE error1(
1.217 + a TYPE PRIMARY KEY,
1.218 + b TYPE PRIMARY KEY
1.219 + );
1.220 + }
1.221 +} {1 {table "error1" has more than one primary key}}
1.222 +do_test misc1-7.2 {
1.223 + catchsql {
1.224 + CREATE TABLE error1(
1.225 + a INTEGER PRIMARY KEY,
1.226 + b TYPE PRIMARY KEY
1.227 + );
1.228 + }
1.229 +} {1 {table "error1" has more than one primary key}}
1.230 +do_test misc1-7.3 {
1.231 + execsql {
1.232 + CREATE TABLE t5(a,b,c,PRIMARY KEY(a,b));
1.233 + INSERT INTO t5 VALUES(1,2,3);
1.234 + SELECT * FROM t5 ORDER BY a;
1.235 + }
1.236 +} {1 2 3}
1.237 +do_test misc1-7.4 {
1.238 + catchsql {
1.239 + INSERT INTO t5 VALUES(1,2,4);
1.240 + }
1.241 +} {1 {columns a, b are not unique}}
1.242 +do_test misc1-7.5 {
1.243 + catchsql {
1.244 + INSERT INTO t5 VALUES(0,2,4);
1.245 + }
1.246 +} {0 {}}
1.247 +do_test misc1-7.6 {
1.248 + execsql {
1.249 + SELECT * FROM t5 ORDER BY a;
1.250 + }
1.251 +} {0 2 4 1 2 3}
1.252 +
1.253 +do_test misc1-8.1 {
1.254 + catchsql {
1.255 + SELECT *;
1.256 + }
1.257 +} {1 {no tables specified}}
1.258 +do_test misc1-8.2 {
1.259 + catchsql {
1.260 + SELECT t1.*;
1.261 + }
1.262 +} {1 {no such table: t1}}
1.263 +
1.264 +execsql {
1.265 + DROP TABLE t1;
1.266 + DROP TABLE t2;
1.267 + DROP TABLE t3;
1.268 + DROP TABLE t4;
1.269 +}
1.270 +
1.271 +# 64-bit integers are represented exactly.
1.272 +#
1.273 +do_test misc1-9.1 {
1.274 + catchsql {
1.275 + CREATE TABLE t1(a unique not null, b unique not null);
1.276 + INSERT INTO t1 VALUES('a',1234567890123456789);
1.277 + INSERT INTO t1 VALUES('b',1234567891123456789);
1.278 + INSERT INTO t1 VALUES('c',1234567892123456789);
1.279 + SELECT * FROM t1;
1.280 + }
1.281 +} {0 {a 1234567890123456789 b 1234567891123456789 c 1234567892123456789}}
1.282 +
1.283 +# A WHERE clause is not allowed to contain more than 99 terms. Check to
1.284 +# make sure this limit is enforced.
1.285 +#
1.286 +# 2005-07-16: There is no longer a limit on the number of terms in a
1.287 +# WHERE clause. But keep these tests just so that we have some tests
1.288 +# that use a large number of terms in the WHERE clause.
1.289 +#
1.290 +do_test misc1-10.0 {
1.291 + execsql {SELECT count(*) FROM manycol}
1.292 +} {9}
1.293 +do_test misc1-10.1 {
1.294 + set ::where {WHERE x0>=0}
1.295 + for {set i 1} {$i<=99} {incr i} {
1.296 + append ::where " AND x$i<>0"
1.297 + }
1.298 + catchsql "SELECT count(*) FROM manycol $::where"
1.299 +} {0 9}
1.300 +do_test misc1-10.2 {
1.301 + catchsql "SELECT count(*) FROM manycol $::where AND rowid>0"
1.302 +} {0 9}
1.303 +do_test misc1-10.3 {
1.304 + regsub "x0>=0" $::where "x0=0" ::where
1.305 + catchsql "DELETE FROM manycol $::where"
1.306 +} {0 {}}
1.307 +do_test misc1-10.4 {
1.308 + execsql {SELECT count(*) FROM manycol}
1.309 +} {8}
1.310 +do_test misc1-10.5 {
1.311 + catchsql "DELETE FROM manycol $::where AND rowid>0"
1.312 +} {0 {}}
1.313 +do_test misc1-10.6 {
1.314 + execsql {SELECT x1 FROM manycol WHERE x0=100}
1.315 +} {101}
1.316 +do_test misc1-10.7 {
1.317 + regsub "x0=0" $::where "x0=100" ::where
1.318 + catchsql "UPDATE manycol SET x1=x1+1 $::where"
1.319 +} {0 {}}
1.320 +do_test misc1-10.8 {
1.321 + execsql {SELECT x1 FROM manycol WHERE x0=100}
1.322 +} {102}
1.323 +do_test misc1-10.9 {
1.324 + catchsql "UPDATE manycol SET x1=x1+1 $::where AND rowid>0"
1.325 +} {0 {}}
1.326 +do_test misc1-10.10 {
1.327 + execsql {SELECT x1 FROM manycol WHERE x0=100}
1.328 +} {103}
1.329 +
1.330 +# Make sure the initialization works even if a database is opened while
1.331 +# another process has the database locked.
1.332 +#
1.333 +# Update for v3: The BEGIN doesn't lock the database so the schema is read
1.334 +# and the SELECT returns successfully.
1.335 +do_test misc1-11.1 {
1.336 + execsql {BEGIN}
1.337 + execsql {UPDATE t1 SET a=0 WHERE 0}
1.338 + sqlite3 db2 test.db
1.339 + set rc [catch {db2 eval {SELECT count(*) FROM t1}} msg]
1.340 + lappend rc $msg
1.341 +# v2 result: {1 {database is locked}}
1.342 +} {0 3}
1.343 +do_test misc1-11.2 {
1.344 + execsql {COMMIT}
1.345 + set rc [catch {db2 eval {SELECT count(*) FROM t1}} msg]
1.346 + db2 close
1.347 + lappend rc $msg
1.348 +} {0 3}
1.349 +
1.350 +# Make sure string comparisons really do compare strings in format4+.
1.351 +# Similar tests in the format3.test file show that for format3 and earlier
1.352 +# all comparisions where numeric if either operand looked like a number.
1.353 +#
1.354 +do_test misc1-12.1 {
1.355 + execsql {SELECT '0'=='0.0'}
1.356 +} {0}
1.357 +do_test misc1-12.2 {
1.358 + execsql {SELECT '0'==0.0}
1.359 +} {0}
1.360 +do_test misc1-12.3 {
1.361 + execsql {SELECT '12345678901234567890'=='12345678901234567891'}
1.362 +} {0}
1.363 +do_test misc1-12.4 {
1.364 + execsql {
1.365 + CREATE TABLE t6(a INT UNIQUE, b TEXT UNIQUE);
1.366 + INSERT INTO t6 VALUES('0','0.0');
1.367 + SELECT * FROM t6;
1.368 + }
1.369 +} {0 0.0}
1.370 +ifcapable conflict {
1.371 + do_test misc1-12.5 {
1.372 + execsql {
1.373 + INSERT OR IGNORE INTO t6 VALUES(0.0,'x');
1.374 + SELECT * FROM t6;
1.375 + }
1.376 + } {0 0.0}
1.377 + do_test misc1-12.6 {
1.378 + execsql {
1.379 + INSERT OR IGNORE INTO t6 VALUES('y',0);
1.380 + SELECT * FROM t6;
1.381 + }
1.382 + } {0 0.0 y 0}
1.383 +}
1.384 +do_test misc1-12.7 {
1.385 + execsql {
1.386 + CREATE TABLE t7(x INTEGER, y TEXT, z);
1.387 + INSERT INTO t7 VALUES(0,0,1);
1.388 + INSERT INTO t7 VALUES(0.0,0,2);
1.389 + INSERT INTO t7 VALUES(0,0.0,3);
1.390 + INSERT INTO t7 VALUES(0.0,0.0,4);
1.391 + SELECT DISTINCT x, y FROM t7 ORDER BY z;
1.392 + }
1.393 +} {0 0 0 0.0}
1.394 +do_test misc1-12.8 {
1.395 + execsql {
1.396 + SELECT min(z), max(z), count(z) FROM t7 GROUP BY x ORDER BY 1;
1.397 + }
1.398 +} {1 4 4}
1.399 +do_test misc1-12.9 {
1.400 + execsql {
1.401 + SELECT min(z), max(z), count(z) FROM t7 GROUP BY y ORDER BY 1;
1.402 + }
1.403 +} {1 2 2 3 4 2}
1.404 +
1.405 +# This used to be an error. But we changed the code so that arbitrary
1.406 +# identifiers can be used as a collating sequence. Collation is by text
1.407 +# if the identifier contains "text", "blob", or "clob" and is numeric
1.408 +# otherwise.
1.409 +#
1.410 +# Update: In v3, it is an error again.
1.411 +#
1.412 +#do_test misc1-12.10 {
1.413 +# catchsql {
1.414 +# SELECT * FROM t6 ORDER BY a COLLATE unknown;
1.415 +# }
1.416 +#} {0 {0 0 y 0}}
1.417 +do_test misc1-12.11 {
1.418 + execsql {
1.419 + CREATE TABLE t8(x TEXT COLLATE numeric, y INTEGER COLLATE text, z);
1.420 + INSERT INTO t8 VALUES(0,0,1);
1.421 + INSERT INTO t8 VALUES(0.0,0,2);
1.422 + INSERT INTO t8 VALUES(0,0.0,3);
1.423 + INSERT INTO t8 VALUES(0.0,0.0,4);
1.424 + SELECT DISTINCT x, y FROM t8 ORDER BY z;
1.425 + }
1.426 +} {0 0 0.0 0}
1.427 +do_test misc1-12.12 {
1.428 + execsql {
1.429 + SELECT min(z), max(z), count(z) FROM t8 GROUP BY x ORDER BY 1;
1.430 + }
1.431 +} {1 3 2 2 4 2}
1.432 +do_test misc1-12.13 {
1.433 + execsql {
1.434 + SELECT min(z), max(z), count(z) FROM t8 GROUP BY y ORDER BY 1;
1.435 + }
1.436 +} {1 4 4}
1.437 +
1.438 +# There was a problem with realloc() in the OP_MemStore operation of
1.439 +# the VDBE. A buffer was being reallocated but some pointers into
1.440 +# the old copy of the buffer were not being moved over to the new copy.
1.441 +# The following code tests for the problem.
1.442 +#
1.443 +ifcapable subquery {
1.444 + do_test misc1-13.1 {
1.445 + execsql {
1.446 + CREATE TABLE t9(x,y);
1.447 + INSERT INTO t9 VALUES('one',1);
1.448 + INSERT INTO t9 VALUES('two',2);
1.449 + INSERT INTO t9 VALUES('three',3);
1.450 + INSERT INTO t9 VALUES('four',4);
1.451 + INSERT INTO t9 VALUES('five',5);
1.452 + INSERT INTO t9 VALUES('six',6);
1.453 + INSERT INTO t9 VALUES('seven',7);
1.454 + INSERT INTO t9 VALUES('eight',8);
1.455 + INSERT INTO t9 VALUES('nine',9);
1.456 + INSERT INTO t9 VALUES('ten',10);
1.457 + INSERT INTO t9 VALUES('eleven',11);
1.458 + SELECT y FROM t9
1.459 + WHERE x=(SELECT x FROM t9 WHERE y=1)
1.460 + OR x=(SELECT x FROM t9 WHERE y=2)
1.461 + OR x=(SELECT x FROM t9 WHERE y=3)
1.462 + OR x=(SELECT x FROM t9 WHERE y=4)
1.463 + OR x=(SELECT x FROM t9 WHERE y=5)
1.464 + OR x=(SELECT x FROM t9 WHERE y=6)
1.465 + OR x=(SELECT x FROM t9 WHERE y=7)
1.466 + OR x=(SELECT x FROM t9 WHERE y=8)
1.467 + OR x=(SELECT x FROM t9 WHERE y=9)
1.468 + OR x=(SELECT x FROM t9 WHERE y=10)
1.469 + OR x=(SELECT x FROM t9 WHERE y=11)
1.470 + OR x=(SELECT x FROM t9 WHERE y=12)
1.471 + OR x=(SELECT x FROM t9 WHERE y=13)
1.472 + OR x=(SELECT x FROM t9 WHERE y=14)
1.473 + ;
1.474 + }
1.475 + } {1 2 3 4 5 6 7 8 9 10 11}
1.476 +}
1.477 +
1.478 +# Make sure a database connection still works after changing the
1.479 +# working directory.
1.480 +#
1.481 +do_test misc1-14.1 {
1.482 + file mkdir tempdir
1.483 + cd tempdir
1.484 + execsql {BEGIN}
1.485 + file exists ./test.db-journal
1.486 +} {0}
1.487 +do_test misc1-14.2 {
1.488 + execsql {UPDATE t1 SET a=0 WHERE 0}
1.489 + file exists ../test.db-journal
1.490 +} {1}
1.491 +do_test misc1-14.3 {
1.492 + cd ..
1.493 + file delete -force tempdir
1.494 + execsql {COMMIT}
1.495 + file exists ./test.db-journal
1.496 +} {0}
1.497 +
1.498 +# A failed create table should not leave the table in the internal
1.499 +# data structures. Ticket #238.
1.500 +#
1.501 +do_test misc1-15.1.1 {
1.502 + catchsql {
1.503 + CREATE TABLE t10 AS SELECT c1;
1.504 + }
1.505 +} {1 {no such column: c1}}
1.506 +do_test misc1-15.1.2 {
1.507 + catchsql {
1.508 + CREATE TABLE t10 AS SELECT t9.c1;
1.509 + }
1.510 +} {1 {no such column: t9.c1}}
1.511 +do_test misc1-15.1.3 {
1.512 + catchsql {
1.513 + CREATE TABLE t10 AS SELECT main.t9.c1;
1.514 + }
1.515 +} {1 {no such column: main.t9.c1}}
1.516 +do_test misc1-15.2 {
1.517 + catchsql {
1.518 + CREATE TABLE t10 AS SELECT 1;
1.519 + }
1.520 + # The bug in ticket #238 causes the statement above to fail with
1.521 + # the error "table t10 alread exists"
1.522 +} {0 {}}
1.523 +
1.524 +# Test for memory leaks when a CREATE TABLE containing a primary key
1.525 +# fails. Ticket #249.
1.526 +#
1.527 +do_test misc1-16.1 {
1.528 + catchsql {SELECT name FROM sqlite_master LIMIT 1}
1.529 + catchsql {
1.530 + CREATE TABLE test(a integer, primary key(a));
1.531 + }
1.532 +} {0 {}}
1.533 +do_test misc1-16.2 {
1.534 + catchsql {
1.535 + CREATE TABLE test(a integer, primary key(a));
1.536 + }
1.537 +} {1 {table test already exists}}
1.538 +do_test misc1-16.3 {
1.539 + catchsql {
1.540 + CREATE TABLE test2(a text primary key, b text, primary key(a,b));
1.541 + }
1.542 +} {1 {table "test2" has more than one primary key}}
1.543 +do_test misc1-16.4 {
1.544 + execsql {
1.545 + INSERT INTO test VALUES(1);
1.546 + SELECT rowid, a FROM test;
1.547 + }
1.548 +} {1 1}
1.549 +do_test misc1-16.5 {
1.550 + execsql {
1.551 + INSERT INTO test VALUES(5);
1.552 + SELECT rowid, a FROM test;
1.553 + }
1.554 +} {1 1 5 5}
1.555 +do_test misc1-16.6 {
1.556 + execsql {
1.557 + INSERT INTO test VALUES(NULL);
1.558 + SELECT rowid, a FROM test;
1.559 + }
1.560 +} {1 1 5 5 6 6}
1.561 +
1.562 +ifcapable trigger&&tempdb {
1.563 +# Ticket #333: Temp triggers that modify persistent tables.
1.564 +#
1.565 +do_test misc1-17.1 {
1.566 + execsql {
1.567 + BEGIN;
1.568 + CREATE TABLE RealTable(TestID INTEGER PRIMARY KEY, TestString TEXT);
1.569 + CREATE TEMP TABLE TempTable(TestID INTEGER PRIMARY KEY, TestString TEXT);
1.570 + CREATE TEMP TRIGGER trigTest_1 AFTER UPDATE ON TempTable BEGIN
1.571 + INSERT INTO RealTable(TestString)
1.572 + SELECT new.TestString FROM TempTable LIMIT 1;
1.573 + END;
1.574 + INSERT INTO TempTable(TestString) VALUES ('1');
1.575 + INSERT INTO TempTable(TestString) VALUES ('2');
1.576 + UPDATE TempTable SET TestString = TestString + 1 WHERE TestID=1 OR TestId=2;
1.577 + COMMIT;
1.578 + SELECT TestString FROM RealTable ORDER BY 1;
1.579 + }
1.580 +} {2 3}
1.581 +}
1.582 +
1.583 +do_test misc1-18.1 {
1.584 + set n [sqlite3_sleep 100]
1.585 + expr {$n>=100}
1.586 +} {1}
1.587 +
1.588 +finish_test