1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000
1.2 +++ b/os/persistentdata/persistentstorage/sqlite3api/TEST/TclScript/check.test Fri Jun 15 03:10:57 2012 +0200
1.3 @@ -0,0 +1,372 @@
1.4 +# 2005 November 2
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 CHECK constraints
1.16 +#
1.17 +# $Id: check.test,v 1.11 2007/07/23 19:39:47 drh Exp $
1.18 +
1.19 +set testdir [file dirname $argv0]
1.20 +source $testdir/tester.tcl
1.21 +
1.22 +# Only run these tests if the build includes support for CHECK constraints
1.23 +ifcapable !check {
1.24 + finish_test
1.25 + return
1.26 +}
1.27 +
1.28 +do_test check-1.1 {
1.29 + execsql {
1.30 + CREATE TABLE t1(
1.31 + x INTEGER CHECK( x<5 ),
1.32 + y REAL CHECK( y>x )
1.33 + );
1.34 + }
1.35 +} {}
1.36 +do_test check-1.2 {
1.37 + execsql {
1.38 + INSERT INTO t1 VALUES(3,4);
1.39 + SELECT * FROM t1;
1.40 + }
1.41 +} {3 4.0}
1.42 +do_test check-1.3 {
1.43 + catchsql {
1.44 + INSERT INTO t1 VALUES(6,7);
1.45 + }
1.46 +} {1 {constraint failed}}
1.47 +do_test check-1.4 {
1.48 + execsql {
1.49 + SELECT * FROM t1;
1.50 + }
1.51 +} {3 4.0}
1.52 +do_test check-1.5 {
1.53 + catchsql {
1.54 + INSERT INTO t1 VALUES(4,3);
1.55 + }
1.56 +} {1 {constraint failed}}
1.57 +do_test check-1.6 {
1.58 + execsql {
1.59 + SELECT * FROM t1;
1.60 + }
1.61 +} {3 4.0}
1.62 +do_test check-1.7 {
1.63 + catchsql {
1.64 + INSERT INTO t1 VALUES(NULL,6);
1.65 + }
1.66 +} {0 {}}
1.67 +do_test check-1.8 {
1.68 + execsql {
1.69 + SELECT * FROM t1;
1.70 + }
1.71 +} {3 4.0 {} 6.0}
1.72 +do_test check-1.9 {
1.73 + catchsql {
1.74 + INSERT INTO t1 VALUES(2,NULL);
1.75 + }
1.76 +} {0 {}}
1.77 +do_test check-1.10 {
1.78 + execsql {
1.79 + SELECT * FROM t1;
1.80 + }
1.81 +} {3 4.0 {} 6.0 2 {}}
1.82 +do_test check-1.11 {
1.83 + execsql {
1.84 + DELETE FROM t1 WHERE x IS NULL OR x!=3;
1.85 + UPDATE t1 SET x=2 WHERE x==3;
1.86 + SELECT * FROM t1;
1.87 + }
1.88 +} {2 4.0}
1.89 +do_test check-1.12 {
1.90 + catchsql {
1.91 + UPDATE t1 SET x=7 WHERE x==2
1.92 + }
1.93 +} {1 {constraint failed}}
1.94 +do_test check-1.13 {
1.95 + execsql {
1.96 + SELECT * FROM t1;
1.97 + }
1.98 +} {2 4.0}
1.99 +do_test check-1.14 {
1.100 + catchsql {
1.101 + UPDATE t1 SET x=5 WHERE x==2
1.102 + }
1.103 +} {1 {constraint failed}}
1.104 +do_test check-1.15 {
1.105 + execsql {
1.106 + SELECT * FROM t1;
1.107 + }
1.108 +} {2 4.0}
1.109 +do_test check-1.16 {
1.110 + catchsql {
1.111 + UPDATE t1 SET x=4, y=11 WHERE x==2
1.112 + }
1.113 +} {0 {}}
1.114 +do_test check-1.17 {
1.115 + execsql {
1.116 + SELECT * FROM t1;
1.117 + }
1.118 +} {4 11.0}
1.119 +
1.120 +do_test check-2.1 {
1.121 + execsql {
1.122 + CREATE TABLE t2(
1.123 + x INTEGER CHECK( typeof(coalesce(x,0))=="integer" ),
1.124 + y REAL CHECK( typeof(coalesce(y,0.1))=="real" ),
1.125 + z TEXT CHECK( typeof(coalesce(z,''))=="text" )
1.126 + );
1.127 + }
1.128 +} {}
1.129 +do_test check-2.2 {
1.130 + execsql {
1.131 + INSERT INTO t2 VALUES(1,2.2,'three');
1.132 + SELECT * FROM t2;
1.133 + }
1.134 +} {1 2.2 three}
1.135 +do_test check-2.3 {
1.136 + execsql {
1.137 + INSERT INTO t2 VALUES(NULL, NULL, NULL);
1.138 + SELECT * FROM t2;
1.139 + }
1.140 +} {1 2.2 three {} {} {}}
1.141 +do_test check-2.4 {
1.142 + catchsql {
1.143 + INSERT INTO t2 VALUES(1.1, NULL, NULL);
1.144 + }
1.145 +} {1 {constraint failed}}
1.146 +do_test check-2.5 {
1.147 + catchsql {
1.148 + INSERT INTO t2 VALUES(NULL, 5, NULL);
1.149 + }
1.150 +} {1 {constraint failed}}
1.151 +do_test check-2.6 {
1.152 + catchsql {
1.153 + INSERT INTO t2 VALUES(NULL, NULL, 3.14159);
1.154 + }
1.155 +} {1 {constraint failed}}
1.156 +
1.157 +ifcapable subquery {
1.158 + do_test check-3.1 {
1.159 + catchsql {
1.160 + CREATE TABLE t3(
1.161 + x, y, z,
1.162 + CHECK( x<(SELECT min(x) FROM t1) )
1.163 + );
1.164 + }
1.165 + } {1 {subqueries prohibited in CHECK constraints}}
1.166 +}
1.167 +
1.168 +do_test check-3.2 {
1.169 + execsql {
1.170 + SELECT name FROM sqlite_master ORDER BY name
1.171 + }
1.172 +} {t1 t2}
1.173 +do_test check-3.3 {
1.174 + catchsql {
1.175 + CREATE TABLE t3(
1.176 + x, y, z,
1.177 + CHECK( q<x )
1.178 + );
1.179 + }
1.180 +} {1 {no such column: q}}
1.181 +do_test check-3.4 {
1.182 + execsql {
1.183 + SELECT name FROM sqlite_master ORDER BY name
1.184 + }
1.185 +} {t1 t2}
1.186 +do_test check-3.5 {
1.187 + catchsql {
1.188 + CREATE TABLE t3(
1.189 + x, y, z,
1.190 + CHECK( t2.x<x )
1.191 + );
1.192 + }
1.193 +} {1 {no such column: t2.x}}
1.194 +do_test check-3.6 {
1.195 + execsql {
1.196 + SELECT name FROM sqlite_master ORDER BY name
1.197 + }
1.198 +} {t1 t2}
1.199 +do_test check-3.7 {
1.200 + catchsql {
1.201 + CREATE TABLE t3(
1.202 + x, y, z,
1.203 + CHECK( t3.x<25 )
1.204 + );
1.205 + }
1.206 +} {0 {}}
1.207 +do_test check-3.8 {
1.208 + execsql {
1.209 + INSERT INTO t3 VALUES(1,2,3);
1.210 + SELECT * FROM t3;
1.211 + }
1.212 +} {1 2 3}
1.213 +do_test check-3.9 {
1.214 + catchsql {
1.215 + INSERT INTO t3 VALUES(111,222,333);
1.216 + }
1.217 +} {1 {constraint failed}}
1.218 +
1.219 +do_test check-4.1 {
1.220 + execsql {
1.221 + CREATE TABLE t4(x, y,
1.222 + CHECK (
1.223 + x+y==11
1.224 + OR x*y==12
1.225 + OR x/y BETWEEN 5 AND 8
1.226 + OR -x==y+10
1.227 + )
1.228 + );
1.229 + }
1.230 +} {}
1.231 +do_test check-4.2 {
1.232 + execsql {
1.233 + INSERT INTO t4 VALUES(1,10);
1.234 + SELECT * FROM t4
1.235 + }
1.236 +} {1 10}
1.237 +do_test check-4.3 {
1.238 + execsql {
1.239 + UPDATE t4 SET x=4, y=3;
1.240 + SELECT * FROM t4
1.241 + }
1.242 +} {4 3}
1.243 +do_test check-4.3 {
1.244 + execsql {
1.245 + UPDATE t4 SET x=12, y=2;
1.246 + SELECT * FROM t4
1.247 + }
1.248 +} {12 2}
1.249 +do_test check-4.4 {
1.250 + execsql {
1.251 + UPDATE t4 SET x=12, y=-22;
1.252 + SELECT * FROM t4
1.253 + }
1.254 +} {12 -22}
1.255 +do_test check-4.5 {
1.256 + catchsql {
1.257 + UPDATE t4 SET x=0, y=1;
1.258 + }
1.259 +} {1 {constraint failed}}
1.260 +do_test check-4.6 {
1.261 + execsql {
1.262 + SELECT * FROM t4;
1.263 + }
1.264 +} {12 -22}
1.265 +do_test check-4.7 {
1.266 + execsql {
1.267 + PRAGMA ignore_check_constraints=ON;
1.268 + UPDATE t4 SET x=0, y=1;
1.269 + SELECT * FROM t4;
1.270 + }
1.271 +} {0 1}
1.272 +do_test check-4.8 {
1.273 + catchsql {
1.274 + PRAGMA ignore_check_constraints=OFF;
1.275 + UPDATE t4 SET x=0, y=2;
1.276 + }
1.277 +} {1 {constraint failed}}
1.278 +ifcapable vacuum {
1.279 + do_test check_4.9 {
1.280 + catchsql {
1.281 + VACUUM
1.282 + }
1.283 + } {0 {}}
1.284 +}
1.285 +
1.286 +do_test check-5.1 {
1.287 + catchsql {
1.288 + CREATE TABLE t5(x, y,
1.289 + CHECK( x*y<:abc )
1.290 + );
1.291 + }
1.292 +} {1 {parameters prohibited in CHECK constraints}}
1.293 +do_test check-5.2 {
1.294 + catchsql {
1.295 + CREATE TABLE t5(x, y,
1.296 + CHECK( x*y<? )
1.297 + );
1.298 + }
1.299 +} {1 {parameters prohibited in CHECK constraints}}
1.300 +
1.301 +ifcapable conflict {
1.302 +
1.303 +do_test check-6.1 {
1.304 + execsql {SELECT * FROM t1}
1.305 +} {4 11.0}
1.306 +do_test check-6.2 {
1.307 + execsql {
1.308 + UPDATE OR IGNORE t1 SET x=5;
1.309 + SELECT * FROM t1;
1.310 + }
1.311 +} {4 11.0}
1.312 +do_test check-6.3 {
1.313 + execsql {
1.314 + INSERT OR IGNORE INTO t1 VALUES(5,4.0);
1.315 + SELECT * FROM t1;
1.316 + }
1.317 +} {4 11.0}
1.318 +do_test check-6.4 {
1.319 + execsql {
1.320 + INSERT OR IGNORE INTO t1 VALUES(2,20.0);
1.321 + SELECT * FROM t1;
1.322 + }
1.323 +} {4 11.0 2 20.0}
1.324 +do_test check-6.5 {
1.325 + catchsql {
1.326 + UPDATE OR FAIL t1 SET x=7-x, y=y+1;
1.327 + }
1.328 +} {1 {constraint failed}}
1.329 +do_test check-6.6 {
1.330 + execsql {
1.331 + SELECT * FROM t1;
1.332 + }
1.333 +} {3 12.0 2 20.0}
1.334 +do_test check-6.7 {
1.335 + catchsql {
1.336 + BEGIN;
1.337 + INSERT INTO t1 VALUES(1,30.0);
1.338 + INSERT OR ROLLBACK INTO t1 VALUES(8,40.0);
1.339 + }
1.340 +} {1 {constraint failed}}
1.341 +do_test check-6.8 {
1.342 + catchsql {
1.343 + COMMIT;
1.344 + }
1.345 +} {1 {cannot commit - no transaction is active}}
1.346 +do_test check-6.9 {
1.347 + execsql {
1.348 + SELECT * FROM t1
1.349 + }
1.350 +} {3 12.0 2 20.0}
1.351 +
1.352 +do_test check-6.11 {
1.353 + execsql {SELECT * FROM t1}
1.354 +} {3 12.0 2 20.0}
1.355 +do_test check-6.12 {
1.356 + catchsql {
1.357 + REPLACE INTO t1 VALUES(6,7);
1.358 + }
1.359 +} {1 {constraint failed}}
1.360 +do_test check-6.13 {
1.361 + execsql {SELECT * FROM t1}
1.362 +} {3 12.0 2 20.0}
1.363 +do_test check-6.14 {
1.364 + catchsql {
1.365 + INSERT OR IGNORE INTO t1 VALUES(6,7);
1.366 + }
1.367 +} {0 {}}
1.368 +do_test check-6.15 {
1.369 + execsql {SELECT * FROM t1}
1.370 +} {3 12.0 2 20.0}
1.371 +
1.372 +
1.373 +}
1.374 +
1.375 +finish_test