1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000
1.2 +++ b/os/persistentdata/persistentstorage/sqlite3api/TEST/TclScript/notnull.test Fri Jun 15 03:10:57 2012 +0200
1.3 @@ -0,0 +1,505 @@
1.4 +# 2002 January 29
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 the NOT NULL constraint.
1.17 +#
1.18 +# $Id: notnull.test,v 1.4 2006/01/17 09:35:02 danielk1977 Exp $
1.19 +
1.20 +set testdir [file dirname $argv0]
1.21 +source $testdir/tester.tcl
1.22 +
1.23 +ifcapable !conflict {
1.24 + finish_test
1.25 + return
1.26 +}
1.27 +
1.28 +do_test notnull-1.0 {
1.29 + execsql {
1.30 + CREATE TABLE t1 (
1.31 + a NOT NULL,
1.32 + b NOT NULL DEFAULT 5,
1.33 + c NOT NULL ON CONFLICT REPLACE DEFAULT 6,
1.34 + d NOT NULL ON CONFLICT IGNORE DEFAULT 7,
1.35 + e NOT NULL ON CONFLICT ABORT DEFAULT 8
1.36 + );
1.37 + SELECT * FROM t1;
1.38 + }
1.39 +} {}
1.40 +do_test notnull-1.1 {
1.41 + catchsql {
1.42 + DELETE FROM t1;
1.43 + INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5);
1.44 + SELECT * FROM t1 order by a;
1.45 + }
1.46 +} {0 {1 2 3 4 5}}
1.47 +do_test notnull-1.2 {
1.48 + catchsql {
1.49 + DELETE FROM t1;
1.50 + INSERT INTO t1(b,c,d,e) VALUES(2,3,4,5);
1.51 + SELECT * FROM t1 order by a;
1.52 + }
1.53 +} {1 {t1.a may not be NULL}}
1.54 +do_test notnull-1.3 {
1.55 + catchsql {
1.56 + DELETE FROM t1;
1.57 + INSERT OR IGNORE INTO t1(b,c,d,e) VALUES(2,3,4,5);
1.58 + SELECT * FROM t1 order by a;
1.59 + }
1.60 +} {0 {}}
1.61 +do_test notnull-1.4 {
1.62 + catchsql {
1.63 + DELETE FROM t1;
1.64 + INSERT OR REPLACE INTO t1(b,c,d,e) VALUES(2,3,4,5);
1.65 + SELECT * FROM t1 order by a;
1.66 + }
1.67 +} {1 {t1.a may not be NULL}}
1.68 +do_test notnull-1.5 {
1.69 + catchsql {
1.70 + DELETE FROM t1;
1.71 + INSERT OR ABORT INTO t1(b,c,d,e) VALUES(2,3,4,5);
1.72 + SELECT * FROM t1 order by a;
1.73 + }
1.74 +} {1 {t1.a may not be NULL}}
1.75 +do_test notnull-1.6 {
1.76 + catchsql {
1.77 + DELETE FROM t1;
1.78 + INSERT INTO t1(a,c,d,e) VALUES(1,3,4,5);
1.79 + SELECT * FROM t1 order by a;
1.80 + }
1.81 +} {0 {1 5 3 4 5}}
1.82 +do_test notnull-1.7 {
1.83 + catchsql {
1.84 + DELETE FROM t1;
1.85 + INSERT OR IGNORE INTO t1(a,c,d,e) VALUES(1,3,4,5);
1.86 + SELECT * FROM t1 order by a;
1.87 + }
1.88 +} {0 {1 5 3 4 5}}
1.89 +do_test notnull-1.8 {
1.90 + catchsql {
1.91 + DELETE FROM t1;
1.92 + INSERT OR REPLACE INTO t1(a,c,d,e) VALUES(1,3,4,5);
1.93 + SELECT * FROM t1 order by a;
1.94 + }
1.95 +} {0 {1 5 3 4 5}}
1.96 +do_test notnull-1.9 {
1.97 + catchsql {
1.98 + DELETE FROM t1;
1.99 + INSERT OR ABORT INTO t1(a,c,d,e) VALUES(1,3,4,5);
1.100 + SELECT * FROM t1 order by a;
1.101 + }
1.102 +} {0 {1 5 3 4 5}}
1.103 +do_test notnull-1.10 {
1.104 + catchsql {
1.105 + DELETE FROM t1;
1.106 + INSERT INTO t1(a,b,c,d,e) VALUES(1,null,3,4,5);
1.107 + SELECT * FROM t1 order by a;
1.108 + }
1.109 +} {1 {t1.b may not be NULL}}
1.110 +do_test notnull-1.11 {
1.111 + catchsql {
1.112 + DELETE FROM t1;
1.113 + INSERT OR IGNORE INTO t1(a,b,c,d,e) VALUES(1,null,3,4,5);
1.114 + SELECT * FROM t1 order by a;
1.115 + }
1.116 +} {0 {}}
1.117 +do_test notnull-1.12 {
1.118 + catchsql {
1.119 + DELETE FROM t1;
1.120 + INSERT OR REPLACE INTO t1(a,b,c,d,e) VALUES(1,null,3,4,5);
1.121 + SELECT * FROM t1 order by a;
1.122 + }
1.123 +} {0 {1 5 3 4 5}}
1.124 +do_test notnull-1.13 {
1.125 + catchsql {
1.126 + DELETE FROM t1;
1.127 + INSERT INTO t1(a,b,c,d,e) VALUES(1,2,null,4,5);
1.128 + SELECT * FROM t1 order by a;
1.129 + }
1.130 +} {0 {1 2 6 4 5}}
1.131 +do_test notnull-1.14 {
1.132 + catchsql {
1.133 + DELETE FROM t1;
1.134 + INSERT OR IGNORE INTO t1(a,b,c,d,e) VALUES(1,2,null,4,5);
1.135 + SELECT * FROM t1 order by a;
1.136 + }
1.137 +} {0 {}}
1.138 +do_test notnull-1.15 {
1.139 + catchsql {
1.140 + DELETE FROM t1;
1.141 + INSERT OR REPLACE INTO t1(a,b,c,d,e) VALUES(1,2,null,4,5);
1.142 + SELECT * FROM t1 order by a;
1.143 + }
1.144 +} {0 {1 2 6 4 5}}
1.145 +do_test notnull-1.16 {
1.146 + catchsql {
1.147 + DELETE FROM t1;
1.148 + INSERT OR ABORT INTO t1(a,b,c,d,e) VALUES(1,2,null,4,5);
1.149 + SELECT * FROM t1 order by a;
1.150 + }
1.151 +} {1 {t1.c may not be NULL}}
1.152 +do_test notnull-1.17 {
1.153 + catchsql {
1.154 + DELETE FROM t1;
1.155 + INSERT OR ABORT INTO t1(a,b,c,d,e) VALUES(1,2,3,null,5);
1.156 + SELECT * FROM t1 order by a;
1.157 + }
1.158 +} {1 {t1.d may not be NULL}}
1.159 +do_test notnull-1.18 {
1.160 + catchsql {
1.161 + DELETE FROM t1;
1.162 + INSERT OR ABORT INTO t1(a,b,c,e) VALUES(1,2,3,5);
1.163 + SELECT * FROM t1 order by a;
1.164 + }
1.165 +} {0 {1 2 3 7 5}}
1.166 +do_test notnull-1.19 {
1.167 + catchsql {
1.168 + DELETE FROM t1;
1.169 + INSERT INTO t1(a,b,c,d) VALUES(1,2,3,4);
1.170 + SELECT * FROM t1 order by a;
1.171 + }
1.172 +} {0 {1 2 3 4 8}}
1.173 +do_test notnull-1.20 {
1.174 + catchsql {
1.175 + DELETE FROM t1;
1.176 + INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,null);
1.177 + SELECT * FROM t1 order by a;
1.178 + }
1.179 +} {1 {t1.e may not be NULL}}
1.180 +do_test notnull-1.21 {
1.181 + catchsql {
1.182 + DELETE FROM t1;
1.183 + INSERT OR REPLACE INTO t1(e,d,c,b,a) VALUES(1,2,3,null,5);
1.184 + SELECT * FROM t1 order by a;
1.185 + }
1.186 +} {0 {5 5 3 2 1}}
1.187 +
1.188 +do_test notnull-2.1 {
1.189 + catchsql {
1.190 + DELETE FROM t1;
1.191 + INSERT INTO t1 VALUES(1,2,3,4,5);
1.192 + UPDATE t1 SET a=null;
1.193 + SELECT * FROM t1 ORDER BY a;
1.194 + }
1.195 +} {1 {t1.a may not be NULL}}
1.196 +do_test notnull-2.2 {
1.197 + catchsql {
1.198 + DELETE FROM t1;
1.199 + INSERT INTO t1 VALUES(1,2,3,4,5);
1.200 + UPDATE OR REPLACE t1 SET a=null;
1.201 + SELECT * FROM t1 ORDER BY a;
1.202 + }
1.203 +} {1 {t1.a may not be NULL}}
1.204 +do_test notnull-2.3 {
1.205 + catchsql {
1.206 + DELETE FROM t1;
1.207 + INSERT INTO t1 VALUES(1,2,3,4,5);
1.208 + UPDATE OR IGNORE t1 SET a=null;
1.209 + SELECT * FROM t1 ORDER BY a;
1.210 + }
1.211 +} {0 {1 2 3 4 5}}
1.212 +do_test notnull-2.4 {
1.213 + catchsql {
1.214 + DELETE FROM t1;
1.215 + INSERT INTO t1 VALUES(1,2,3,4,5);
1.216 + UPDATE OR ABORT t1 SET a=null;
1.217 + SELECT * FROM t1 ORDER BY a;
1.218 + }
1.219 +} {1 {t1.a may not be NULL}}
1.220 +do_test notnull-2.5 {
1.221 + catchsql {
1.222 + DELETE FROM t1;
1.223 + INSERT INTO t1 VALUES(1,2,3,4,5);
1.224 + UPDATE t1 SET b=null;
1.225 + SELECT * FROM t1 ORDER BY a;
1.226 + }
1.227 +} {1 {t1.b may not be NULL}}
1.228 +do_test notnull-2.6 {
1.229 + catchsql {
1.230 + DELETE FROM t1;
1.231 + INSERT INTO t1 VALUES(1,2,3,4,5);
1.232 + UPDATE OR REPLACE t1 SET b=null, d=e, e=d;
1.233 + SELECT * FROM t1 ORDER BY a;
1.234 + }
1.235 +} {0 {1 5 3 5 4}}
1.236 +do_test notnull-2.7 {
1.237 + catchsql {
1.238 + DELETE FROM t1;
1.239 + INSERT INTO t1 VALUES(1,2,3,4,5);
1.240 + UPDATE OR IGNORE t1 SET b=null, d=e, e=d;
1.241 + SELECT * FROM t1 ORDER BY a;
1.242 + }
1.243 +} {0 {1 2 3 4 5}}
1.244 +do_test notnull-2.8 {
1.245 + catchsql {
1.246 + DELETE FROM t1;
1.247 + INSERT INTO t1 VALUES(1,2,3,4,5);
1.248 + UPDATE t1 SET c=null, d=e, e=d;
1.249 + SELECT * FROM t1 ORDER BY a;
1.250 + }
1.251 +} {0 {1 2 6 5 4}}
1.252 +do_test notnull-2.9 {
1.253 + catchsql {
1.254 + DELETE FROM t1;
1.255 + INSERT INTO t1 VALUES(1,2,3,4,5);
1.256 + UPDATE t1 SET d=null, a=b, b=a;
1.257 + SELECT * FROM t1 ORDER BY a;
1.258 + }
1.259 +} {0 {1 2 3 4 5}}
1.260 +do_test notnull-2.10 {
1.261 + catchsql {
1.262 + DELETE FROM t1;
1.263 + INSERT INTO t1 VALUES(1,2,3,4,5);
1.264 + UPDATE t1 SET e=null, a=b, b=a;
1.265 + SELECT * FROM t1 ORDER BY a;
1.266 + }
1.267 +} {1 {t1.e may not be NULL}}
1.268 +
1.269 +do_test notnull-3.0 {
1.270 + execsql {
1.271 + CREATE INDEX t1a ON t1(a);
1.272 + CREATE INDEX t1b ON t1(b);
1.273 + CREATE INDEX t1c ON t1(c);
1.274 + CREATE INDEX t1d ON t1(d);
1.275 + CREATE INDEX t1e ON t1(e);
1.276 + CREATE INDEX t1abc ON t1(a,b,c);
1.277 + }
1.278 +} {}
1.279 +do_test notnull-3.1 {
1.280 + catchsql {
1.281 + DELETE FROM t1;
1.282 + INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5);
1.283 + SELECT * FROM t1 order by a;
1.284 + }
1.285 +} {0 {1 2 3 4 5}}
1.286 +do_test notnull-3.2 {
1.287 + catchsql {
1.288 + DELETE FROM t1;
1.289 + INSERT INTO t1(b,c,d,e) VALUES(2,3,4,5);
1.290 + SELECT * FROM t1 order by a;
1.291 + }
1.292 +} {1 {t1.a may not be NULL}}
1.293 +do_test notnull-3.3 {
1.294 + catchsql {
1.295 + DELETE FROM t1;
1.296 + INSERT OR IGNORE INTO t1(b,c,d,e) VALUES(2,3,4,5);
1.297 + SELECT * FROM t1 order by a;
1.298 + }
1.299 +} {0 {}}
1.300 +do_test notnull-3.4 {
1.301 + catchsql {
1.302 + DELETE FROM t1;
1.303 + INSERT OR REPLACE INTO t1(b,c,d,e) VALUES(2,3,4,5);
1.304 + SELECT * FROM t1 order by a;
1.305 + }
1.306 +} {1 {t1.a may not be NULL}}
1.307 +do_test notnull-3.5 {
1.308 + catchsql {
1.309 + DELETE FROM t1;
1.310 + INSERT OR ABORT INTO t1(b,c,d,e) VALUES(2,3,4,5);
1.311 + SELECT * FROM t1 order by a;
1.312 + }
1.313 +} {1 {t1.a may not be NULL}}
1.314 +do_test notnull-3.6 {
1.315 + catchsql {
1.316 + DELETE FROM t1;
1.317 + INSERT INTO t1(a,c,d,e) VALUES(1,3,4,5);
1.318 + SELECT * FROM t1 order by a;
1.319 + }
1.320 +} {0 {1 5 3 4 5}}
1.321 +do_test notnull-3.7 {
1.322 + catchsql {
1.323 + DELETE FROM t1;
1.324 + INSERT OR IGNORE INTO t1(a,c,d,e) VALUES(1,3,4,5);
1.325 + SELECT * FROM t1 order by a;
1.326 + }
1.327 +} {0 {1 5 3 4 5}}
1.328 +do_test notnull-3.8 {
1.329 + catchsql {
1.330 + DELETE FROM t1;
1.331 + INSERT OR REPLACE INTO t1(a,c,d,e) VALUES(1,3,4,5);
1.332 + SELECT * FROM t1 order by a;
1.333 + }
1.334 +} {0 {1 5 3 4 5}}
1.335 +do_test notnull-3.9 {
1.336 + catchsql {
1.337 + DELETE FROM t1;
1.338 + INSERT OR ABORT INTO t1(a,c,d,e) VALUES(1,3,4,5);
1.339 + SELECT * FROM t1 order by a;
1.340 + }
1.341 +} {0 {1 5 3 4 5}}
1.342 +do_test notnull-3.10 {
1.343 + catchsql {
1.344 + DELETE FROM t1;
1.345 + INSERT INTO t1(a,b,c,d,e) VALUES(1,null,3,4,5);
1.346 + SELECT * FROM t1 order by a;
1.347 + }
1.348 +} {1 {t1.b may not be NULL}}
1.349 +do_test notnull-3.11 {
1.350 + catchsql {
1.351 + DELETE FROM t1;
1.352 + INSERT OR IGNORE INTO t1(a,b,c,d,e) VALUES(1,null,3,4,5);
1.353 + SELECT * FROM t1 order by a;
1.354 + }
1.355 +} {0 {}}
1.356 +do_test notnull-3.12 {
1.357 + catchsql {
1.358 + DELETE FROM t1;
1.359 + INSERT OR REPLACE INTO t1(a,b,c,d,e) VALUES(1,null,3,4,5);
1.360 + SELECT * FROM t1 order by a;
1.361 + }
1.362 +} {0 {1 5 3 4 5}}
1.363 +do_test notnull-3.13 {
1.364 + catchsql {
1.365 + DELETE FROM t1;
1.366 + INSERT INTO t1(a,b,c,d,e) VALUES(1,2,null,4,5);
1.367 + SELECT * FROM t1 order by a;
1.368 + }
1.369 +} {0 {1 2 6 4 5}}
1.370 +do_test notnull-3.14 {
1.371 + catchsql {
1.372 + DELETE FROM t1;
1.373 + INSERT OR IGNORE INTO t1(a,b,c,d,e) VALUES(1,2,null,4,5);
1.374 + SELECT * FROM t1 order by a;
1.375 + }
1.376 +} {0 {}}
1.377 +do_test notnull-3.15 {
1.378 + catchsql {
1.379 + DELETE FROM t1;
1.380 + INSERT OR REPLACE INTO t1(a,b,c,d,e) VALUES(1,2,null,4,5);
1.381 + SELECT * FROM t1 order by a;
1.382 + }
1.383 +} {0 {1 2 6 4 5}}
1.384 +do_test notnull-3.16 {
1.385 + catchsql {
1.386 + DELETE FROM t1;
1.387 + INSERT OR ABORT INTO t1(a,b,c,d,e) VALUES(1,2,null,4,5);
1.388 + SELECT * FROM t1 order by a;
1.389 + }
1.390 +} {1 {t1.c may not be NULL}}
1.391 +do_test notnull-3.17 {
1.392 + catchsql {
1.393 + DELETE FROM t1;
1.394 + INSERT OR ABORT INTO t1(a,b,c,d,e) VALUES(1,2,3,null,5);
1.395 + SELECT * FROM t1 order by a;
1.396 + }
1.397 +} {1 {t1.d may not be NULL}}
1.398 +do_test notnull-3.18 {
1.399 + catchsql {
1.400 + DELETE FROM t1;
1.401 + INSERT OR ABORT INTO t1(a,b,c,e) VALUES(1,2,3,5);
1.402 + SELECT * FROM t1 order by a;
1.403 + }
1.404 +} {0 {1 2 3 7 5}}
1.405 +do_test notnull-3.19 {
1.406 + catchsql {
1.407 + DELETE FROM t1;
1.408 + INSERT INTO t1(a,b,c,d) VALUES(1,2,3,4);
1.409 + SELECT * FROM t1 order by a;
1.410 + }
1.411 +} {0 {1 2 3 4 8}}
1.412 +do_test notnull-3.20 {
1.413 + catchsql {
1.414 + DELETE FROM t1;
1.415 + INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,null);
1.416 + SELECT * FROM t1 order by a;
1.417 + }
1.418 +} {1 {t1.e may not be NULL}}
1.419 +do_test notnull-3.21 {
1.420 + catchsql {
1.421 + DELETE FROM t1;
1.422 + INSERT OR REPLACE INTO t1(e,d,c,b,a) VALUES(1,2,3,null,5);
1.423 + SELECT * FROM t1 order by a;
1.424 + }
1.425 +} {0 {5 5 3 2 1}}
1.426 +
1.427 +do_test notnull-4.1 {
1.428 + catchsql {
1.429 + DELETE FROM t1;
1.430 + INSERT INTO t1 VALUES(1,2,3,4,5);
1.431 + UPDATE t1 SET a=null;
1.432 + SELECT * FROM t1 ORDER BY a;
1.433 + }
1.434 +} {1 {t1.a may not be NULL}}
1.435 +do_test notnull-4.2 {
1.436 + catchsql {
1.437 + DELETE FROM t1;
1.438 + INSERT INTO t1 VALUES(1,2,3,4,5);
1.439 + UPDATE OR REPLACE t1 SET a=null;
1.440 + SELECT * FROM t1 ORDER BY a;
1.441 + }
1.442 +} {1 {t1.a may not be NULL}}
1.443 +do_test notnull-4.3 {
1.444 + catchsql {
1.445 + DELETE FROM t1;
1.446 + INSERT INTO t1 VALUES(1,2,3,4,5);
1.447 + UPDATE OR IGNORE t1 SET a=null;
1.448 + SELECT * FROM t1 ORDER BY a;
1.449 + }
1.450 +} {0 {1 2 3 4 5}}
1.451 +do_test notnull-4.4 {
1.452 + catchsql {
1.453 + DELETE FROM t1;
1.454 + INSERT INTO t1 VALUES(1,2,3,4,5);
1.455 + UPDATE OR ABORT t1 SET a=null;
1.456 + SELECT * FROM t1 ORDER BY a;
1.457 + }
1.458 +} {1 {t1.a may not be NULL}}
1.459 +do_test notnull-4.5 {
1.460 + catchsql {
1.461 + DELETE FROM t1;
1.462 + INSERT INTO t1 VALUES(1,2,3,4,5);
1.463 + UPDATE t1 SET b=null;
1.464 + SELECT * FROM t1 ORDER BY a;
1.465 + }
1.466 +} {1 {t1.b may not be NULL}}
1.467 +do_test notnull-4.6 {
1.468 + catchsql {
1.469 + DELETE FROM t1;
1.470 + INSERT INTO t1 VALUES(1,2,3,4,5);
1.471 + UPDATE OR REPLACE t1 SET b=null, d=e, e=d;
1.472 + SELECT * FROM t1 ORDER BY a;
1.473 + }
1.474 +} {0 {1 5 3 5 4}}
1.475 +do_test notnull-4.7 {
1.476 + catchsql {
1.477 + DELETE FROM t1;
1.478 + INSERT INTO t1 VALUES(1,2,3,4,5);
1.479 + UPDATE OR IGNORE t1 SET b=null, d=e, e=d;
1.480 + SELECT * FROM t1 ORDER BY a;
1.481 + }
1.482 +} {0 {1 2 3 4 5}}
1.483 +do_test notnull-4.8 {
1.484 + catchsql {
1.485 + DELETE FROM t1;
1.486 + INSERT INTO t1 VALUES(1,2,3,4,5);
1.487 + UPDATE t1 SET c=null, d=e, e=d;
1.488 + SELECT * FROM t1 ORDER BY a;
1.489 + }
1.490 +} {0 {1 2 6 5 4}}
1.491 +do_test notnull-4.9 {
1.492 + catchsql {
1.493 + DELETE FROM t1;
1.494 + INSERT INTO t1 VALUES(1,2,3,4,5);
1.495 + UPDATE t1 SET d=null, a=b, b=a;
1.496 + SELECT * FROM t1 ORDER BY a;
1.497 + }
1.498 +} {0 {1 2 3 4 5}}
1.499 +do_test notnull-4.10 {
1.500 + catchsql {
1.501 + DELETE FROM t1;
1.502 + INSERT INTO t1 VALUES(1,2,3,4,5);
1.503 + UPDATE t1 SET e=null, a=b, b=a;
1.504 + SELECT * FROM t1 ORDER BY a;
1.505 + }
1.506 +} {1 {t1.e may not be NULL}}
1.507 +
1.508 +finish_test