Lines Matching refs:index
15 # $Id: index.test,v 1.24.2.1 2004/07/20 00:50:30 drh Exp $
20 # Create a basic index and verify it is added to sqlite_master
22 do_test index-1.1 {
27 do_test index-1.1b {
30 } {index1 {CREATE INDEX index1 ON test1(f1)} test1 index}
31 do_test index-1.1c {
36 } {index1 {CREATE INDEX index1 ON test1(f1)} test1 index}
37 do_test index-1.1d {
43 # Verify that the index dies with the table
45 do_test index-1.2 {
50 # Try adding an index to a table that does not exist
52 do_test index-2.1 {
57 # Try adding an index on a column of a table where the table
60 do_test index-2.1 {
66 # Try an index with some columns that match and others that do now.
68 do_test index-2.2 {
78 lappend r [format index%02d $i]
80 do_test index-3.1 {
83 set sql "CREATE INDEX [format index%02d $i] ON test1(f[expr {($i%5)+1}])"
87 WHERE type='index' AND tbl_name='test1'
94 do_test index-3.3 {
97 WHERE type='index' AND tbl_name='test1'
102 # an index on that table. Verify that we can select values
103 # from the table correctly using the index.
105 # Note that the index names "index9" and "indext" are chosen because
108 do_test index-4.1 {
117 do_test index-4.2 {
120 do_test index-4.3 {
123 do_test index-4.4 {
126 do_test index-4.5 {
130 do_test index-4.6 {
133 do_test index-4.7 {
137 do_test index-4.8 {
140 do_test index-4.9 {
144 do_test index-4.10 {
147 do_test index-4.11 {
151 do_test index-4.12 {
154 do_test index-4.13 {
158 integrity_check index-4.14
162 do_test index-5.1 {
166 do_test index-5.2 {
172 do_test index-6.1 {
178 } {1 {index index1 already exists}}
179 do_test index-6.1b {
182 do_test index-6.2 {
186 do_test index-6.2b {
189 do_test index-6.3 {
194 do_test index-6.4 {
204 integrity_check index-6.5
209 do_test index-7.1 {
216 do_test index-7.2 {
219 do_test index-7.3 {
222 WHERE type='index' AND tbl_name='test1'
225 do_test index-7.4 {
229 integrity_check index-7.5
231 # Make sure we cannot drop a non-existant index.
233 do_test index-8.1 {
236 } {1 {no such index: index1}}
238 # Make sure we don't actually create an index when the EXPLAIN keyword
241 do_test index-9.1 {
246 do_test index-9.2 {
250 integrity_check index-9.3
254 do_test index-10.0 {
265 do_test index-10.1 {
270 do_test index-10.2 {
276 do_test index-10.3 {
282 do_test index-10.4 {
298 do_test index-10.5 {
304 do_test index-10.6 {
310 do_test index-10.7 {
316 do_test index-10.8 {
321 integrity_check index-10.9
323 # Automatically create an index when we specify a primary key.
325 do_test index-11.1 {
340 integrity_check index-11.2
348 do_test index-12.1 {
361 do_test index-12.2 {
366 do_test index-12.3 {
371 do_test index-12.4 {
376 do_test index-12.5 {
382 do_test index-12.6 {
387 do_test index-12.7 {
392 integrity_check index-12.8
394 # Make sure we cannot drop an automatically created index.
396 do_test index-13.1 {
408 do_test index-13.2 {
410 SELECT name FROM sqlite_master WHERE type="index" AND tbl_name="t5";
415 do_test index-13.3.$i {
419 } {1 {index associated with UNIQUE or PRIMARY KEY constraint cannot be dropped}}
421 do_test index-13.4 {
427 integrity_check index-13.5
429 # Check the sort order of data in an index.
431 do_test index-14.1 {
443 do_test index-14.2 {
448 do_test index-14.3 {
453 do_test index-14.4 {
458 do_test index-14.5 {
463 do_test index-14.6 {
468 do_test index-14.7 {
473 do_test index-14.8 {
478 do_test index-14.9 {
483 do_test index-14.10 {
488 do_test index-14.11 {
493 integrity_check index-14.12
495 do_test index-15.1 {
501 do_test index-15.2 {
517 integrity_check index-15.1
519 # Drop index with a quoted name. Ticket #695.
521 do_test index-16.1 {
527 do_test index-16.2 {