Lines Matching refs:index
17 # $Id: index.test,v 1.24.2.1 2004/07/20 00:50:30 drh Exp $
22 # Create a basic index and verify it is added to sqlite_master
24 do_test index-1.1 {
29 do_test index-1.1b {
32 } {index1 {CREATE INDEX index1 ON test1(f1)} test1 index}
33 do_test index-1.1c {
38 } {index1 {CREATE INDEX index1 ON test1(f1)} test1 index}
39 do_test index-1.1d {
45 # Verify that the index dies with the table
47 do_test index-1.2 {
52 # Try adding an index to a table that does not exist
54 do_test index-2.1 {
59 # Try adding an index on a column of a table where the table
62 do_test index-2.1 {
68 # Try an index with some columns that match and others that do now.
70 do_test index-2.2 {
80 lappend r [format index%02d $i]
82 do_test index-3.1 {
85 set sql "CREATE INDEX [format index%02d $i] ON test1(f[expr {($i%5)+1}])"
89 WHERE type='index' AND tbl_name='test1'
96 do_test index-3.3 {
99 WHERE type='index' AND tbl_name='test1'
104 # an index on that table. Verify that we can select values
105 # from the table correctly using the index.
107 # Note that the index names "index9" and "indext" are chosen because
110 do_test index-4.1 {
119 do_test index-4.2 {
122 do_test index-4.3 {
125 do_test index-4.4 {
128 do_test index-4.5 {
132 do_test index-4.6 {
135 do_test index-4.7 {
139 do_test index-4.8 {
142 do_test index-4.9 {
146 do_test index-4.10 {
149 do_test index-4.11 {
153 do_test index-4.12 {
156 do_test index-4.13 {
160 integrity_check index-4.14
164 do_test index-5.1 {
168 do_test index-5.2 {
174 do_test index-6.1 {
180 } {1 {index index1 already exists}}
181 do_test index-6.1b {
184 do_test index-6.2 {
188 do_test index-6.2b {
191 do_test index-6.3 {
196 do_test index-6.4 {
206 integrity_check index-6.5
211 do_test index-7.1 {
218 do_test index-7.2 {
221 do_test index-7.3 {
224 WHERE type='index' AND tbl_name='test1'
227 do_test index-7.4 {
231 integrity_check index-7.5
233 # Make sure we cannot drop a non-existant index.
235 do_test index-8.1 {
238 } {1 {no such index: index1}}
240 # Make sure we don't actually create an index when the EXPLAIN keyword
243 do_test index-9.1 {
248 do_test index-9.2 {
252 integrity_check index-9.3
256 do_test index-10.0 {
267 do_test index-10.1 {
272 do_test index-10.2 {
278 do_test index-10.3 {
284 do_test index-10.4 {
300 do_test index-10.5 {
306 do_test index-10.6 {
312 do_test index-10.7 {
318 do_test index-10.8 {
323 integrity_check index-10.9
325 # Automatically create an index when we specify a primary key.
327 do_test index-11.1 {
342 integrity_check index-11.2
350 do_test index-12.1 {
363 do_test index-12.2 {
368 do_test index-12.3 {
373 do_test index-12.4 {
378 do_test index-12.5 {
384 do_test index-12.6 {
389 do_test index-12.7 {
394 integrity_check index-12.8
396 # Make sure we cannot drop an automatically created index.
398 do_test index-13.1 {
410 do_test index-13.2 {
412 SELECT name FROM sqlite_master WHERE type="index" AND tbl_name="t5";
417 do_test index-13.3.$i {
421 } {1 {index associated with UNIQUE or PRIMARY KEY constraint cannot be dropped}}
423 do_test index-13.4 {
429 integrity_check index-13.5
431 # Check the sort order of data in an index.
433 do_test index-14.1 {
445 do_test index-14.2 {
450 do_test index-14.3 {
455 do_test index-14.4 {
460 do_test index-14.5 {
465 do_test index-14.6 {
470 do_test index-14.7 {
475 do_test index-14.8 {
480 do_test index-14.9 {
485 do_test index-14.10 {
490 do_test index-14.11 {
495 integrity_check index-14.12
497 do_test index-15.1 {
503 do_test index-15.2 {
519 integrity_check index-15.1
521 # Drop index with a quoted name. Ticket #695.
523 do_test index-16.1 {
529 do_test index-16.2 {