1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000
1.2 +++ b/os/persistentdata/persistentstorage/sqlite3api/TEST/TclScript/unique.test Fri Jun 15 03:10:57 2012 +0200
1.3 @@ -0,0 +1,253 @@
1.4 +# 2001 September 27
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 CREATE UNIQUE INDEX statement,
1.16 +# and primary keys, and the UNIQUE constraint on table columns
1.17 +#
1.18 +# $Id: unique.test,v 1.8 2005/06/24 03:53:06 drh Exp $
1.19 +
1.20 +set testdir [file dirname $argv0]
1.21 +source $testdir/tester.tcl
1.22 +
1.23 +# Try to create a table with two primary keys.
1.24 +# (This is allowed in SQLite even that it is not valid SQL)
1.25 +#
1.26 +do_test unique-1.1 {
1.27 + catchsql {
1.28 + CREATE TABLE t1(
1.29 + a int PRIMARY KEY,
1.30 + b int PRIMARY KEY,
1.31 + c text
1.32 + );
1.33 + }
1.34 +} {1 {table "t1" has more than one primary key}}
1.35 +do_test unique-1.1b {
1.36 + catchsql {
1.37 + CREATE TABLE t1(
1.38 + a int PRIMARY KEY,
1.39 + b int UNIQUE,
1.40 + c text
1.41 + );
1.42 + }
1.43 +} {0 {}}
1.44 +do_test unique-1.2 {
1.45 + catchsql {
1.46 + INSERT INTO t1(a,b,c) VALUES(1,2,3)
1.47 + }
1.48 +} {0 {}}
1.49 +do_test unique-1.3 {
1.50 + catchsql {
1.51 + INSERT INTO t1(a,b,c) VALUES(1,3,4)
1.52 + }
1.53 +} {1 {column a is not unique}}
1.54 +do_test unique-1.4 {
1.55 + execsql {
1.56 + SELECT * FROM t1 ORDER BY a;
1.57 + }
1.58 +} {1 2 3}
1.59 +do_test unique-1.5 {
1.60 + catchsql {
1.61 + INSERT INTO t1(a,b,c) VALUES(3,2,4)
1.62 + }
1.63 +} {1 {column b is not unique}}
1.64 +do_test unique-1.6 {
1.65 + execsql {
1.66 + SELECT * FROM t1 ORDER BY a;
1.67 + }
1.68 +} {1 2 3}
1.69 +do_test unique-1.7 {
1.70 + catchsql {
1.71 + INSERT INTO t1(a,b,c) VALUES(3,4,5)
1.72 + }
1.73 +} {0 {}}
1.74 +do_test unique-1.8 {
1.75 + execsql {
1.76 + SELECT * FROM t1 ORDER BY a;
1.77 + }
1.78 +} {1 2 3 3 4 5}
1.79 +integrity_check unique-1.9
1.80 +
1.81 +do_test unique-2.0 {
1.82 + execsql {
1.83 + DROP TABLE t1;
1.84 + CREATE TABLE t2(a int, b int);
1.85 + INSERT INTO t2(a,b) VALUES(1,2);
1.86 + INSERT INTO t2(a,b) VALUES(3,4);
1.87 + SELECT * FROM t2 ORDER BY a;
1.88 + }
1.89 +} {1 2 3 4}
1.90 +do_test unique-2.1 {
1.91 + catchsql {
1.92 + CREATE UNIQUE INDEX i2 ON t2(a)
1.93 + }
1.94 +} {0 {}}
1.95 +do_test unique-2.2 {
1.96 + catchsql {
1.97 + SELECT * FROM t2 ORDER BY a
1.98 + }
1.99 +} {0 {1 2 3 4}}
1.100 +do_test unique-2.3 {
1.101 + catchsql {
1.102 + INSERT INTO t2 VALUES(1,5);
1.103 + }
1.104 +} {1 {column a is not unique}}
1.105 +do_test unique-2.4 {
1.106 + catchsql {
1.107 + SELECT * FROM t2 ORDER BY a
1.108 + }
1.109 +} {0 {1 2 3 4}}
1.110 +do_test unique-2.5 {
1.111 + catchsql {
1.112 + DROP INDEX i2;
1.113 + SELECT * FROM t2 ORDER BY a;
1.114 + }
1.115 +} {0 {1 2 3 4}}
1.116 +do_test unique-2.6 {
1.117 + catchsql {
1.118 + INSERT INTO t2 VALUES(1,5)
1.119 + }
1.120 +} {0 {}}
1.121 +do_test unique-2.7 {
1.122 + catchsql {
1.123 + SELECT * FROM t2 ORDER BY a, b;
1.124 + }
1.125 +} {0 {1 2 1 5 3 4}}
1.126 +do_test unique-2.8 {
1.127 + catchsql {
1.128 + CREATE UNIQUE INDEX i2 ON t2(a);
1.129 + }
1.130 +} {1 {indexed columns are not unique}}
1.131 +do_test unique-2.9 {
1.132 + catchsql {
1.133 + CREATE INDEX i2 ON t2(a);
1.134 + }
1.135 +} {0 {}}
1.136 +integrity_check unique-2.10
1.137 +
1.138 +# Test the UNIQUE keyword as used on two or more fields.
1.139 +#
1.140 +do_test unique-3.1 {
1.141 + catchsql {
1.142 + CREATE TABLE t3(
1.143 + a int,
1.144 + b int,
1.145 + c int,
1.146 + d int,
1.147 + unique(a,c,d)
1.148 + );
1.149 + }
1.150 +} {0 {}}
1.151 +do_test unique-3.2 {
1.152 + catchsql {
1.153 + INSERT INTO t3(a,b,c,d) VALUES(1,2,3,4);
1.154 + SELECT * FROM t3 ORDER BY a,b,c,d;
1.155 + }
1.156 +} {0 {1 2 3 4}}
1.157 +do_test unique-3.3 {
1.158 + catchsql {
1.159 + INSERT INTO t3(a,b,c,d) VALUES(1,2,3,5);
1.160 + SELECT * FROM t3 ORDER BY a,b,c,d;
1.161 + }
1.162 +} {0 {1 2 3 4 1 2 3 5}}
1.163 +do_test unique-3.4 {
1.164 + catchsql {
1.165 + INSERT INTO t3(a,b,c,d) VALUES(1,4,3,5);
1.166 + SELECT * FROM t3 ORDER BY a,b,c,d;
1.167 + }
1.168 +} {1 {columns a, c, d are not unique}}
1.169 +integrity_check unique-3.5
1.170 +
1.171 +# Make sure NULLs are distinct as far as the UNIQUE tests are
1.172 +# concerned.
1.173 +#
1.174 +do_test unique-4.1 {
1.175 + execsql {
1.176 + CREATE TABLE t4(a UNIQUE, b, c, UNIQUE(b,c));
1.177 + INSERT INTO t4 VALUES(1,2,3);
1.178 + INSERT INTO t4 VALUES(NULL, 2, NULL);
1.179 + SELECT * FROM t4;
1.180 + }
1.181 +} {1 2 3 {} 2 {}}
1.182 +do_test unique-4.2 {
1.183 + catchsql {
1.184 + INSERT INTO t4 VALUES(NULL, 3, 4);
1.185 + }
1.186 +} {0 {}}
1.187 +do_test unique-4.3 {
1.188 + execsql {
1.189 + SELECT * FROM t4
1.190 + }
1.191 +} {1 2 3 {} 2 {} {} 3 4}
1.192 +do_test unique-4.4 {
1.193 + catchsql {
1.194 + INSERT INTO t4 VALUES(2, 2, NULL);
1.195 + }
1.196 +} {0 {}}
1.197 +do_test unique-4.5 {
1.198 + execsql {
1.199 + SELECT * FROM t4
1.200 + }
1.201 +} {1 2 3 {} 2 {} {} 3 4 2 2 {}}
1.202 +
1.203 +# Ticket #1301. Any NULL value in a set of unique columns should
1.204 +# cause the rows to be distinct.
1.205 +#
1.206 +do_test unique-4.6 {
1.207 + catchsql {
1.208 + INSERT INTO t4 VALUES(NULL, 2, NULL);
1.209 + }
1.210 +} {0 {}}
1.211 +do_test unique-4.7 {
1.212 + execsql {SELECT * FROM t4}
1.213 +} {1 2 3 {} 2 {} {} 3 4 2 2 {} {} 2 {}}
1.214 +do_test unique-4.8 {
1.215 + catchsql {CREATE UNIQUE INDEX i4a ON t4(a,b)}
1.216 +} {0 {}}
1.217 +do_test unique-4.9 {
1.218 + catchsql {CREATE UNIQUE INDEX i4b ON t4(a,b,c)}
1.219 +} {0 {}}
1.220 +do_test unique-4.10 {
1.221 + catchsql {CREATE UNIQUE INDEX i4c ON t4(b)}
1.222 +} {1 {indexed columns are not unique}}
1.223 +integrity_check unique-4.99
1.224 +
1.225 +# Test the error message generation logic. In particular, make sure we
1.226 +# do not overflow the static buffer used to generate the error message.
1.227 +#
1.228 +do_test unique-5.1 {
1.229 + execsql {
1.230 + CREATE TABLE t5(
1.231 + first_column_with_long_name,
1.232 + second_column_with_long_name,
1.233 + third_column_with_long_name,
1.234 + fourth_column_with_long_name,
1.235 + fifth_column_with_long_name,
1.236 + sixth_column_with_long_name,
1.237 + UNIQUE(
1.238 + first_column_with_long_name,
1.239 + second_column_with_long_name,
1.240 + third_column_with_long_name,
1.241 + fourth_column_with_long_name,
1.242 + fifth_column_with_long_name,
1.243 + sixth_column_with_long_name
1.244 + )
1.245 + );
1.246 + INSERT INTO t5 VALUES(1,2,3,4,5,6);
1.247 + SELECT * FROM t5;
1.248 + }
1.249 +} {1 2 3 4 5 6}
1.250 +do_test unique-5.2 {
1.251 + catchsql {
1.252 + INSERT INTO t5 VALUES(1,2,3,4,5,6);
1.253 + }
1.254 +} {1 {columns first_column_with_long_name, second_column_with_long_name, third_column_with_long_name, fourth_column_with_long_name, fifth_column_with_long_name, ... are not unique}}
1.255 +
1.256 +finish_test