1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000
1.2 +++ b/os/persistentdata/persistentstorage/sqlite3api/TEST/TclScript/index.test Fri Jun 15 03:10:57 2012 +0200
1.3 @@ -0,0 +1,711 @@
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 CREATE INDEX statement.
1.16 +#
1.17 +# $Id: index.test,v 1.43 2008/01/16 18:20:42 danielk1977 Exp $
1.18 +
1.19 +set testdir [file dirname $argv0]
1.20 +source $testdir/tester.tcl
1.21 +
1.22 +# Create a basic index and verify it is added to sqlite_master
1.23 +#
1.24 +do_test index-1.1 {
1.25 + execsql {CREATE TABLE test1(f1 int, f2 int, f3 int)}
1.26 + execsql {CREATE INDEX index1 ON test1(f1)}
1.27 + execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
1.28 +} {index1 test1}
1.29 +do_test index-1.1b {
1.30 + execsql {SELECT name, sql, tbl_name, type FROM sqlite_master
1.31 + WHERE name='index1'}
1.32 +} {index1 {CREATE INDEX index1 ON test1(f1)} test1 index}
1.33 +do_test index-1.1c {
1.34 + db close
1.35 + sqlite3 db test.db
1.36 + execsql {SELECT name, sql, tbl_name, type FROM sqlite_master
1.37 + WHERE name='index1'}
1.38 +} {index1 {CREATE INDEX index1 ON test1(f1)} test1 index}
1.39 +do_test index-1.1d {
1.40 + db close
1.41 + sqlite3 db test.db
1.42 + execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
1.43 +} {index1 test1}
1.44 +
1.45 +# Verify that the index dies with the table
1.46 +#
1.47 +do_test index-1.2 {
1.48 + execsql {DROP TABLE test1}
1.49 + execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
1.50 +} {}
1.51 +
1.52 +# Try adding an index to a table that does not exist
1.53 +#
1.54 +do_test index-2.1 {
1.55 + set v [catch {execsql {CREATE INDEX index1 ON test1(f1)}} msg]
1.56 + lappend v $msg
1.57 +} {1 {no such table: main.test1}}
1.58 +
1.59 +# Try adding an index on a column of a table where the table
1.60 +# exists but the column does not.
1.61 +#
1.62 +do_test index-2.1 {
1.63 + execsql {CREATE TABLE test1(f1 int, f2 int, f3 int)}
1.64 + set v [catch {execsql {CREATE INDEX index1 ON test1(f4)}} msg]
1.65 + lappend v $msg
1.66 +} {1 {table test1 has no column named f4}}
1.67 +
1.68 +# Try an index with some columns that match and others that do now.
1.69 +#
1.70 +do_test index-2.2 {
1.71 + set v [catch {execsql {CREATE INDEX index1 ON test1(f1, f2, f4, f3)}} msg]
1.72 + execsql {DROP TABLE test1}
1.73 + lappend v $msg
1.74 +} {1 {table test1 has no column named f4}}
1.75 +
1.76 +# Try creating a bunch of indices on the same table
1.77 +#
1.78 +set r {}
1.79 +for {set i 1} {$i<100} {incr i} {
1.80 + lappend r [format index%02d $i]
1.81 +}
1.82 +do_test index-3.1 {
1.83 + execsql {CREATE TABLE test1(f1 int, f2 int, f3 int, f4 int, f5 int)}
1.84 + for {set i 1} {$i<100} {incr i} {
1.85 + set sql "CREATE INDEX [format index%02d $i] ON test1(f[expr {($i%5)+1}])"
1.86 + execsql $sql
1.87 + }
1.88 + execsql {SELECT name FROM sqlite_master
1.89 + WHERE type='index' AND tbl_name='test1'
1.90 + ORDER BY name}
1.91 +} $r
1.92 +integrity_check index-3.2.1
1.93 +ifcapable {reindex} {
1.94 + do_test index-3.2.2 {
1.95 + execsql REINDEX
1.96 + } {}
1.97 +}
1.98 +integrity_check index-3.2.3
1.99 +
1.100 +
1.101 +# Verify that all the indices go away when we drop the table.
1.102 +#
1.103 +do_test index-3.3 {
1.104 + execsql {DROP TABLE test1}
1.105 + execsql {SELECT name FROM sqlite_master
1.106 + WHERE type='index' AND tbl_name='test1'
1.107 + ORDER BY name}
1.108 +} {}
1.109 +
1.110 +# Create a table and insert values into that table. Then create
1.111 +# an index on that table. Verify that we can select values
1.112 +# from the table correctly using the index.
1.113 +#
1.114 +# Note that the index names "index9" and "indext" are chosen because
1.115 +# they both have the same hash.
1.116 +#
1.117 +do_test index-4.1 {
1.118 + execsql {CREATE TABLE test1(cnt int, power int)}
1.119 + for {set i 1} {$i<20} {incr i} {
1.120 + execsql "INSERT INTO test1 VALUES($i,[expr {1<<$i}])"
1.121 + }
1.122 + execsql {CREATE INDEX index9 ON test1(cnt)}
1.123 + execsql {CREATE INDEX indext ON test1(power)}
1.124 + execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
1.125 +} {index9 indext test1}
1.126 +do_test index-4.2 {
1.127 + execsql {SELECT cnt FROM test1 WHERE power=4}
1.128 +} {2}
1.129 +do_test index-4.3 {
1.130 + execsql {SELECT cnt FROM test1 WHERE power=1024}
1.131 +} {10}
1.132 +do_test index-4.4 {
1.133 + execsql {SELECT power FROM test1 WHERE cnt=6}
1.134 +} {64}
1.135 +do_test index-4.5 {
1.136 + execsql {DROP INDEX indext}
1.137 + execsql {SELECT power FROM test1 WHERE cnt=6}
1.138 +} {64}
1.139 +do_test index-4.6 {
1.140 + execsql {SELECT cnt FROM test1 WHERE power=1024}
1.141 +} {10}
1.142 +do_test index-4.7 {
1.143 + execsql {CREATE INDEX indext ON test1(cnt)}
1.144 + execsql {SELECT power FROM test1 WHERE cnt=6}
1.145 +} {64}
1.146 +do_test index-4.8 {
1.147 + execsql {SELECT cnt FROM test1 WHERE power=1024}
1.148 +} {10}
1.149 +do_test index-4.9 {
1.150 + execsql {DROP INDEX index9}
1.151 + execsql {SELECT power FROM test1 WHERE cnt=6}
1.152 +} {64}
1.153 +do_test index-4.10 {
1.154 + execsql {SELECT cnt FROM test1 WHERE power=1024}
1.155 +} {10}
1.156 +do_test index-4.11 {
1.157 + execsql {DROP INDEX indext}
1.158 + execsql {SELECT power FROM test1 WHERE cnt=6}
1.159 +} {64}
1.160 +do_test index-4.12 {
1.161 + execsql {SELECT cnt FROM test1 WHERE power=1024}
1.162 +} {10}
1.163 +do_test index-4.13 {
1.164 + execsql {DROP TABLE test1}
1.165 + execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
1.166 +} {}
1.167 +integrity_check index-4.14
1.168 +
1.169 +# Do not allow indices to be added to sqlite_master
1.170 +#
1.171 +do_test index-5.1 {
1.172 + set v [catch {execsql {CREATE INDEX index1 ON sqlite_master(name)}} msg]
1.173 + lappend v $msg
1.174 +} {1 {table sqlite_master may not be indexed}}
1.175 +do_test index-5.2 {
1.176 + execsql {SELECT name FROM sqlite_master WHERE type!='meta'}
1.177 +} {}
1.178 +
1.179 +# Do not allow indices with duplicate names to be added
1.180 +#
1.181 +do_test index-6.1 {
1.182 + execsql {CREATE TABLE test1(f1 int, f2 int)}
1.183 + execsql {CREATE TABLE test2(g1 real, g2 real)}
1.184 + execsql {CREATE INDEX index1 ON test1(f1)}
1.185 + set v [catch {execsql {CREATE INDEX index1 ON test2(g1)}} msg]
1.186 + lappend v $msg
1.187 +} {1 {index index1 already exists}}
1.188 +do_test index-6.1.1 {
1.189 + catchsql {CREATE INDEX [index1] ON test2(g1)}
1.190 +} {1 {index index1 already exists}}
1.191 +do_test index-6.1b {
1.192 + execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
1.193 +} {index1 test1 test2}
1.194 +do_test index-6.1c {
1.195 + catchsql {CREATE INDEX IF NOT EXISTS index1 ON test1(f1)}
1.196 +} {0 {}}
1.197 +do_test index-6.2 {
1.198 + set v [catch {execsql {CREATE INDEX test1 ON test2(g1)}} msg]
1.199 + lappend v $msg
1.200 +} {1 {there is already a table named test1}}
1.201 +do_test index-6.2b {
1.202 + execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
1.203 +} {index1 test1 test2}
1.204 +do_test index-6.3 {
1.205 + execsql {DROP TABLE test1}
1.206 + execsql {DROP TABLE test2}
1.207 + execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
1.208 +} {}
1.209 +do_test index-6.4 {
1.210 + execsql {
1.211 + CREATE TABLE test1(a,b);
1.212 + CREATE INDEX index1 ON test1(a);
1.213 + CREATE INDEX index2 ON test1(b);
1.214 + CREATE INDEX index3 ON test1(a,b);
1.215 + DROP TABLE test1;
1.216 + SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name;
1.217 + }
1.218 +} {}
1.219 +integrity_check index-6.5
1.220 +
1.221 +
1.222 +# Create a primary key
1.223 +#
1.224 +do_test index-7.1 {
1.225 + execsql {CREATE TABLE test1(f1 int, f2 int primary key)}
1.226 + for {set i 1} {$i<20} {incr i} {
1.227 + execsql "INSERT INTO test1 VALUES($i,[expr {1<<$i}])"
1.228 + }
1.229 + execsql {SELECT count(*) FROM test1}
1.230 +} {19}
1.231 +do_test index-7.2 {
1.232 + execsql {SELECT f1 FROM test1 WHERE f2=65536}
1.233 +} {16}
1.234 +do_test index-7.3 {
1.235 + execsql {
1.236 + SELECT name FROM sqlite_master
1.237 + WHERE type='index' AND tbl_name='test1'
1.238 + }
1.239 +} {sqlite_autoindex_test1_1}
1.240 +do_test index-7.4 {
1.241 + execsql {DROP table test1}
1.242 + execsql {SELECT name FROM sqlite_master WHERE type!='meta'}
1.243 +} {}
1.244 +integrity_check index-7.5
1.245 +
1.246 +# Make sure we cannot drop a non-existant index.
1.247 +#
1.248 +do_test index-8.1 {
1.249 + set v [catch {execsql {DROP INDEX index1}} msg]
1.250 + lappend v $msg
1.251 +} {1 {no such index: index1}}
1.252 +
1.253 +# Make sure we don't actually create an index when the EXPLAIN keyword
1.254 +# is used.
1.255 +#
1.256 +do_test index-9.1 {
1.257 + execsql {CREATE TABLE tab1(a int)}
1.258 + ifcapable {explain} {
1.259 + execsql {EXPLAIN CREATE INDEX idx1 ON tab1(a)}
1.260 + }
1.261 + execsql {SELECT name FROM sqlite_master WHERE tbl_name='tab1'}
1.262 +} {tab1}
1.263 +do_test index-9.2 {
1.264 + execsql {CREATE INDEX idx1 ON tab1(a)}
1.265 + execsql {SELECT name FROM sqlite_master WHERE tbl_name='tab1' ORDER BY name}
1.266 +} {idx1 tab1}
1.267 +integrity_check index-9.3
1.268 +
1.269 +# Allow more than one entry with the same key.
1.270 +#
1.271 +do_test index-10.0 {
1.272 + execsql {
1.273 + CREATE TABLE t1(a int, b int);
1.274 + CREATE INDEX i1 ON t1(a);
1.275 + INSERT INTO t1 VALUES(1,2);
1.276 + INSERT INTO t1 VALUES(2,4);
1.277 + INSERT INTO t1 VALUES(3,8);
1.278 + INSERT INTO t1 VALUES(1,12);
1.279 + SELECT b FROM t1 WHERE a=1 ORDER BY b;
1.280 + }
1.281 +} {2 12}
1.282 +do_test index-10.1 {
1.283 + execsql {
1.284 + SELECT b FROM t1 WHERE a=2 ORDER BY b;
1.285 + }
1.286 +} {4}
1.287 +do_test index-10.2 {
1.288 + execsql {
1.289 + DELETE FROM t1 WHERE b=12;
1.290 + SELECT b FROM t1 WHERE a=1 ORDER BY b;
1.291 + }
1.292 +} {2}
1.293 +do_test index-10.3 {
1.294 + execsql {
1.295 + DELETE FROM t1 WHERE b=2;
1.296 + SELECT b FROM t1 WHERE a=1 ORDER BY b;
1.297 + }
1.298 +} {}
1.299 +do_test index-10.4 {
1.300 + execsql {
1.301 + DELETE FROM t1;
1.302 + INSERT INTO t1 VALUES (1,1);
1.303 + INSERT INTO t1 VALUES (1,2);
1.304 + INSERT INTO t1 VALUES (1,3);
1.305 + INSERT INTO t1 VALUES (1,4);
1.306 + INSERT INTO t1 VALUES (1,5);
1.307 + INSERT INTO t1 VALUES (1,6);
1.308 + INSERT INTO t1 VALUES (1,7);
1.309 + INSERT INTO t1 VALUES (1,8);
1.310 + INSERT INTO t1 VALUES (1,9);
1.311 + INSERT INTO t1 VALUES (2,0);
1.312 + SELECT b FROM t1 WHERE a=1 ORDER BY b;
1.313 + }
1.314 +} {1 2 3 4 5 6 7 8 9}
1.315 +do_test index-10.5 {
1.316 + ifcapable subquery {
1.317 + execsql { DELETE FROM t1 WHERE b IN (2, 4, 6, 8); }
1.318 + } else {
1.319 + execsql { DELETE FROM t1 WHERE b = 2 OR b = 4 OR b = 6 OR b = 8; }
1.320 + }
1.321 + execsql {
1.322 + SELECT b FROM t1 WHERE a=1 ORDER BY b;
1.323 + }
1.324 +} {1 3 5 7 9}
1.325 +do_test index-10.6 {
1.326 + execsql {
1.327 + DELETE FROM t1 WHERE b>2;
1.328 + SELECT b FROM t1 WHERE a=1 ORDER BY b;
1.329 + }
1.330 +} {1}
1.331 +do_test index-10.7 {
1.332 + execsql {
1.333 + DELETE FROM t1 WHERE b=1;
1.334 + SELECT b FROM t1 WHERE a=1 ORDER BY b;
1.335 + }
1.336 +} {}
1.337 +do_test index-10.8 {
1.338 + execsql {
1.339 + SELECT b FROM t1 ORDER BY b;
1.340 + }
1.341 +} {0}
1.342 +integrity_check index-10.9
1.343 +
1.344 +# Automatically create an index when we specify a primary key.
1.345 +#
1.346 +do_test index-11.1 {
1.347 + execsql {
1.348 + CREATE TABLE t3(
1.349 + a text,
1.350 + b int,
1.351 + c float,
1.352 + PRIMARY KEY(b)
1.353 + );
1.354 + }
1.355 + for {set i 1} {$i<=50} {incr i} {
1.356 + execsql "INSERT INTO t3 VALUES('x${i}x',$i,0.$i)"
1.357 + }
1.358 + set sqlite_search_count 0
1.359 + concat [execsql {SELECT c FROM t3 WHERE b==10}] $sqlite_search_count
1.360 +} {0.1 3}
1.361 +integrity_check index-11.2
1.362 +
1.363 +
1.364 +# Numeric strings should compare as if they were numbers. So even if the
1.365 +# strings are not character-by-character the same, if they represent the
1.366 +# same number they should compare equal to one another. Verify that this
1.367 +# is true in indices.
1.368 +#
1.369 +# Updated for sqlite3 v3: SQLite will now store these values as numbers
1.370 +# (because the affinity of column a is NUMERIC) so the quirky
1.371 +# representations are not retained. i.e. '+1.0' becomes '1'.
1.372 +do_test index-12.1 {
1.373 + execsql {
1.374 + CREATE TABLE t4(a NUM,b);
1.375 + INSERT INTO t4 VALUES('0.0',1);
1.376 + INSERT INTO t4 VALUES('0.00',2);
1.377 + INSERT INTO t4 VALUES('abc',3);
1.378 + INSERT INTO t4 VALUES('-1.0',4);
1.379 + INSERT INTO t4 VALUES('+1.0',5);
1.380 + INSERT INTO t4 VALUES('0',6);
1.381 + INSERT INTO t4 VALUES('00000',7);
1.382 + SELECT a FROM t4 ORDER BY b;
1.383 + }
1.384 +} {0 0 abc -1 1 0 0}
1.385 +do_test index-12.2 {
1.386 + execsql {
1.387 + SELECT a FROM t4 WHERE a==0 ORDER BY b
1.388 + }
1.389 +} {0 0 0 0}
1.390 +do_test index-12.3 {
1.391 + execsql {
1.392 + SELECT a FROM t4 WHERE a<0.5 ORDER BY b
1.393 + }
1.394 +} {0 0 -1 0 0}
1.395 +do_test index-12.4 {
1.396 + execsql {
1.397 + SELECT a FROM t4 WHERE a>-0.5 ORDER BY b
1.398 + }
1.399 +} {0 0 abc 1 0 0}
1.400 +do_test index-12.5 {
1.401 + execsql {
1.402 + CREATE INDEX t4i1 ON t4(a);
1.403 + SELECT a FROM t4 WHERE a==0 ORDER BY b
1.404 + }
1.405 +} {0 0 0 0}
1.406 +do_test index-12.6 {
1.407 + execsql {
1.408 + SELECT a FROM t4 WHERE a<0.5 ORDER BY b
1.409 + }
1.410 +} {0 0 -1 0 0}
1.411 +do_test index-12.7 {
1.412 + execsql {
1.413 + SELECT a FROM t4 WHERE a>-0.5 ORDER BY b
1.414 + }
1.415 +} {0 0 abc 1 0 0}
1.416 +integrity_check index-12.8
1.417 +
1.418 +# Make sure we cannot drop an automatically created index.
1.419 +#
1.420 +do_test index-13.1 {
1.421 + execsql {
1.422 + CREATE TABLE t5(
1.423 + a int UNIQUE,
1.424 + b float PRIMARY KEY,
1.425 + c varchar(10),
1.426 + UNIQUE(a,c)
1.427 + );
1.428 + INSERT INTO t5 VALUES(1,2,3);
1.429 + SELECT * FROM t5;
1.430 + }
1.431 +} {1 2.0 3}
1.432 +do_test index-13.2 {
1.433 + set ::idxlist [execsql {
1.434 + SELECT name FROM sqlite_master WHERE type="index" AND tbl_name="t5";
1.435 + }]
1.436 + llength $::idxlist
1.437 +} {3}
1.438 +for {set i 0} {$i<[llength $::idxlist]} {incr i} {
1.439 + do_test index-13.3.$i {
1.440 + catchsql "
1.441 + DROP INDEX '[lindex $::idxlist $i]';
1.442 + "
1.443 + } {1 {index associated with UNIQUE or PRIMARY KEY constraint cannot be dropped}}
1.444 +}
1.445 +do_test index-13.4 {
1.446 + execsql {
1.447 + INSERT INTO t5 VALUES('a','b','c');
1.448 + SELECT * FROM t5;
1.449 + }
1.450 +} {1 2.0 3 a b c}
1.451 +integrity_check index-13.5
1.452 +
1.453 +# Check the sort order of data in an index.
1.454 +#
1.455 +do_test index-14.1 {
1.456 + execsql {
1.457 + CREATE TABLE t6(a,b,c);
1.458 + CREATE INDEX t6i1 ON t6(a,b);
1.459 + INSERT INTO t6 VALUES('','',1);
1.460 + INSERT INTO t6 VALUES('',NULL,2);
1.461 + INSERT INTO t6 VALUES(NULL,'',3);
1.462 + INSERT INTO t6 VALUES('abc',123,4);
1.463 + INSERT INTO t6 VALUES(123,'abc',5);
1.464 + SELECT c FROM t6 ORDER BY a,b;
1.465 + }
1.466 +} {3 5 2 1 4}
1.467 +do_test index-14.2 {
1.468 + execsql {
1.469 + SELECT c FROM t6 WHERE a='';
1.470 + }
1.471 +} {2 1}
1.472 +do_test index-14.3 {
1.473 + execsql {
1.474 + SELECT c FROM t6 WHERE b='';
1.475 + }
1.476 +} {1 3}
1.477 +do_test index-14.4 {
1.478 + execsql {
1.479 + SELECT c FROM t6 WHERE a>'';
1.480 + }
1.481 +} {4}
1.482 +do_test index-14.5 {
1.483 + execsql {
1.484 + SELECT c FROM t6 WHERE a>='';
1.485 + }
1.486 +} {2 1 4}
1.487 +do_test index-14.6 {
1.488 + execsql {
1.489 + SELECT c FROM t6 WHERE a>123;
1.490 + }
1.491 +} {2 1 4}
1.492 +do_test index-14.7 {
1.493 + execsql {
1.494 + SELECT c FROM t6 WHERE a>=123;
1.495 + }
1.496 +} {5 2 1 4}
1.497 +do_test index-14.8 {
1.498 + execsql {
1.499 + SELECT c FROM t6 WHERE a<'abc';
1.500 + }
1.501 +} {5 2 1}
1.502 +do_test index-14.9 {
1.503 + execsql {
1.504 + SELECT c FROM t6 WHERE a<='abc';
1.505 + }
1.506 +} {5 2 1 4}
1.507 +do_test index-14.10 {
1.508 + execsql {
1.509 + SELECT c FROM t6 WHERE a<='';
1.510 + }
1.511 +} {5 2 1}
1.512 +do_test index-14.11 {
1.513 + execsql {
1.514 + SELECT c FROM t6 WHERE a<'';
1.515 + }
1.516 +} {5}
1.517 +integrity_check index-14.12
1.518 +
1.519 +do_test index-15.1 {
1.520 + execsql {
1.521 + DELETE FROM t1;
1.522 + SELECT * FROM t1;
1.523 + }
1.524 +} {}
1.525 +do_test index-15.2 {
1.526 + execsql {
1.527 + INSERT INTO t1 VALUES('1.234e5',1);
1.528 + INSERT INTO t1 VALUES('12.33e04',2);
1.529 + INSERT INTO t1 VALUES('12.35E4',3);
1.530 + INSERT INTO t1 VALUES('12.34e',4);
1.531 + INSERT INTO t1 VALUES('12.32e+4',5);
1.532 + INSERT INTO t1 VALUES('12.36E+04',6);
1.533 + INSERT INTO t1 VALUES('12.36E+',7);
1.534 + INSERT INTO t1 VALUES('+123.10000E+0003',8);
1.535 + INSERT INTO t1 VALUES('+',9);
1.536 + INSERT INTO t1 VALUES('+12347.E+02',10);
1.537 + INSERT INTO t1 VALUES('+12347E+02',11);
1.538 + SELECT b FROM t1 ORDER BY a;
1.539 + }
1.540 +} {8 5 2 1 3 6 11 9 10 4 7}
1.541 +integrity_check index-15.1
1.542 +
1.543 +# The following tests - index-16.* - test that when a table definition
1.544 +# includes qualifications that specify the same constraint twice only a
1.545 +# single index is generated to enforce the constraint.
1.546 +#
1.547 +# For example: "CREATE TABLE abc( x PRIMARY KEY, UNIQUE(x) );"
1.548 +#
1.549 +do_test index-16.1 {
1.550 + execsql {
1.551 + CREATE TABLE t7(c UNIQUE PRIMARY KEY);
1.552 + SELECT count(*) FROM sqlite_master WHERE tbl_name = 't7' AND type = 'index';
1.553 + }
1.554 +} {1}
1.555 +do_test index-16.2 {
1.556 + execsql {
1.557 + DROP TABLE t7;
1.558 + CREATE TABLE t7(c UNIQUE PRIMARY KEY);
1.559 + SELECT count(*) FROM sqlite_master WHERE tbl_name = 't7' AND type = 'index';
1.560 + }
1.561 +} {1}
1.562 +do_test index-16.3 {
1.563 + execsql {
1.564 + DROP TABLE t7;
1.565 + CREATE TABLE t7(c PRIMARY KEY, UNIQUE(c) );
1.566 + SELECT count(*) FROM sqlite_master WHERE tbl_name = 't7' AND type = 'index';
1.567 + }
1.568 +} {1}
1.569 +do_test index-16.4 {
1.570 + execsql {
1.571 + DROP TABLE t7;
1.572 + CREATE TABLE t7(c, d , UNIQUE(c, d), PRIMARY KEY(c, d) );
1.573 + SELECT count(*) FROM sqlite_master WHERE tbl_name = 't7' AND type = 'index';
1.574 + }
1.575 +} {1}
1.576 +do_test index-16.5 {
1.577 + execsql {
1.578 + DROP TABLE t7;
1.579 + CREATE TABLE t7(c, d , UNIQUE(c), PRIMARY KEY(c, d) );
1.580 + SELECT count(*) FROM sqlite_master WHERE tbl_name = 't7' AND type = 'index';
1.581 + }
1.582 +} {2}
1.583 +
1.584 +# Test that automatically create indices are named correctly. The current
1.585 +# convention is: "sqlite_autoindex_<table name>_<integer>"
1.586 +#
1.587 +# Then check that it is an error to try to drop any automtically created
1.588 +# indices.
1.589 +do_test index-17.1 {
1.590 + execsql {
1.591 + DROP TABLE t7;
1.592 + CREATE TABLE t7(c, d UNIQUE, UNIQUE(c), PRIMARY KEY(c, d) );
1.593 + SELECT name FROM sqlite_master WHERE tbl_name = 't7' AND type = 'index';
1.594 + }
1.595 +} {sqlite_autoindex_t7_1 sqlite_autoindex_t7_2 sqlite_autoindex_t7_3}
1.596 +do_test index-17.2 {
1.597 + catchsql {
1.598 + DROP INDEX sqlite_autoindex_t7_1;
1.599 + }
1.600 +} {1 {index associated with UNIQUE or PRIMARY KEY constraint cannot be dropped}}
1.601 +do_test index-17.3 {
1.602 + catchsql {
1.603 + DROP INDEX IF EXISTS sqlite_autoindex_t7_1;
1.604 + }
1.605 +} {1 {index associated with UNIQUE or PRIMARY KEY constraint cannot be dropped}}
1.606 +do_test index-17.4 {
1.607 + catchsql {
1.608 + DROP INDEX IF EXISTS no_such_index;
1.609 + }
1.610 +} {0 {}}
1.611 +
1.612 +
1.613 +# The following tests ensure that it is not possible to explicitly name
1.614 +# a schema object with a name beginning with "sqlite_". Granted that is a
1.615 +# little outside the focus of this test scripts, but this has got to be
1.616 +# tested somewhere.
1.617 +do_test index-18.1 {
1.618 + catchsql {
1.619 + CREATE TABLE sqlite_t1(a, b, c);
1.620 + }
1.621 +} {1 {object name reserved for internal use: sqlite_t1}}
1.622 +do_test index-18.2 {
1.623 + catchsql {
1.624 + CREATE INDEX sqlite_i1 ON t7(c);
1.625 + }
1.626 +} {1 {object name reserved for internal use: sqlite_i1}}
1.627 +ifcapable view {
1.628 +do_test index-18.3 {
1.629 + catchsql {
1.630 + CREATE VIEW sqlite_v1 AS SELECT * FROM t7;
1.631 + }
1.632 +} {1 {object name reserved for internal use: sqlite_v1}}
1.633 +} ;# ifcapable view
1.634 +ifcapable {trigger} {
1.635 + do_test index-18.4 {
1.636 + catchsql {
1.637 + CREATE TRIGGER sqlite_tr1 BEFORE INSERT ON t7 BEGIN SELECT 1; END;
1.638 + }
1.639 + } {1 {object name reserved for internal use: sqlite_tr1}}
1.640 +}
1.641 +do_test index-18.5 {
1.642 + execsql {
1.643 + DROP TABLE t7;
1.644 + }
1.645 +} {}
1.646 +
1.647 +# These tests ensure that if multiple table definition constraints are
1.648 +# implemented by a single indice, the correct ON CONFLICT policy applies.
1.649 +ifcapable conflict {
1.650 + do_test index-19.1 {
1.651 + execsql {
1.652 + CREATE TABLE t7(a UNIQUE PRIMARY KEY);
1.653 + CREATE TABLE t8(a UNIQUE PRIMARY KEY ON CONFLICT ROLLBACK);
1.654 + INSERT INTO t7 VALUES(1);
1.655 + INSERT INTO t8 VALUES(1);
1.656 + }
1.657 + } {}
1.658 + do_test index-19.2 {
1.659 + catchsql {
1.660 + BEGIN;
1.661 + INSERT INTO t7 VALUES(1);
1.662 + }
1.663 + } {1 {column a is not unique}}
1.664 + do_test index-19.3 {
1.665 + catchsql {
1.666 + BEGIN;
1.667 + }
1.668 + } {1 {cannot start a transaction within a transaction}}
1.669 + do_test index-19.4 {
1.670 + catchsql {
1.671 + INSERT INTO t8 VALUES(1);
1.672 + }
1.673 + } {1 {column a is not unique}}
1.674 + do_test index-19.5 {
1.675 + catchsql {
1.676 + BEGIN;
1.677 + COMMIT;
1.678 + }
1.679 + } {0 {}}
1.680 + do_test index-19.6 {
1.681 + catchsql {
1.682 + DROP TABLE t7;
1.683 + DROP TABLE t8;
1.684 + CREATE TABLE t7(
1.685 + a PRIMARY KEY ON CONFLICT FAIL,
1.686 + UNIQUE(a) ON CONFLICT IGNORE
1.687 + );
1.688 + }
1.689 + } {1 {conflicting ON CONFLICT clauses specified}}
1.690 +} ; # end of "ifcapable conflict" block
1.691 +
1.692 +ifcapable {reindex} {
1.693 + do_test index-19.7 {
1.694 + execsql REINDEX
1.695 + } {}
1.696 +}
1.697 +integrity_check index-19.8
1.698 +
1.699 +# Drop index with a quoted name. Ticket #695.
1.700 +#
1.701 +do_test index-20.1 {
1.702 + execsql {
1.703 + CREATE INDEX "t6i2" ON t6(c);
1.704 + DROP INDEX "t6i2";
1.705 + }
1.706 +} {}
1.707 +do_test index-20.2 {
1.708 + execsql {
1.709 + DROP INDEX "t6i1";
1.710 + }
1.711 +} {}
1.712 +
1.713 +
1.714 +finish_test