Lines Matching refs:index
14 # $Id: index.test,v 1.43 2008/01/16 18:20:42 danielk1977 Exp $
19 # Create a basic index and verify it is added to sqlite_master
21 do_test index-1.1 {
26 do_test index-1.1b {
29 } {index1 {CREATE INDEX index1 ON test1(f1)} test1 index}
30 do_test index-1.1c {
35 } {index1 {CREATE INDEX index1 ON test1(f1)} test1 index}
36 do_test index-1.1d {
42 # Verify that the index dies with the table
44 do_test index-1.2 {
49 # Try adding an index to a table that does not exist
51 do_test index-2.1 {
56 # Try adding an index on a column of a table where the table
59 do_test index-2.1b {
65 # Try an index with some columns that match and others that do now.
67 do_test index-2.2 {
77 lappend r [format index%02d $i]
79 do_test index-3.1 {
82 set sql "CREATE INDEX [format index%02d $i] ON test1(f[expr {($i%5)+1}])"
86 WHERE type='index' AND tbl_name='test1'
89 integrity_check index-3.2.1
91 do_test index-3.2.2 {
95 integrity_check index-3.2.3
100 do_test index-3.3 {
103 WHERE type='index' AND tbl_name='test1'
108 # an index on that table. Verify that we can select values
109 # from the table correctly using the index.
111 # Note that the index names "index9" and "indext" are chosen because
114 do_test index-4.1 {
123 do_test index-4.2 {
126 do_test index-4.3 {
129 do_test index-4.4 {
132 do_test index-4.5 {
136 do_test index-4.6 {
139 do_test index-4.7 {
143 do_test index-4.8 {
146 do_test index-4.9 {
150 do_test index-4.10 {
153 do_test index-4.11 {
157 do_test index-4.12 {
160 do_test index-4.13 {
164 integrity_check index-4.14
168 do_test index-5.1 {
172 do_test index-5.2 {
178 do_test index-6.1 {
184 } {1 {index index1 already exists}}
185 do_test index-6.1.1 {
187 } {1 {index index1 already exists}}
188 do_test index-6.1b {
191 do_test index-6.1c {
194 do_test index-6.2 {
198 do_test index-6.2b {
201 do_test index-6.3 {
206 do_test index-6.4 {
216 integrity_check index-6.5
221 do_test index-7.1 {
228 do_test index-7.2 {
231 do_test index-7.3 {
234 WHERE type='index' AND tbl_name='test1'
237 do_test index-7.4 {
241 integrity_check index-7.5
243 # Make sure we cannot drop a non-existant index.
245 do_test index-8.1 {
248 } {1 {no such index: index1}}
250 # Make sure we don't actually create an index when the EXPLAIN keyword
253 do_test index-9.1 {
260 do_test index-9.2 {
264 integrity_check index-9.3
268 do_test index-10.0 {
279 do_test index-10.1 {
284 do_test index-10.2 {
290 do_test index-10.3 {
296 do_test index-10.4 {
312 do_test index-10.5 {
322 do_test index-10.6 {
328 do_test index-10.7 {
334 do_test index-10.8 {
339 integrity_check index-10.9
341 # Automatically create an index when we specify a primary key.
343 do_test index-11.1 {
358 integrity_check index-11.2
369 do_test index-12.1 {
382 do_test index-12.2 {
387 do_test index-12.3 {
392 do_test index-12.4 {
397 do_test index-12.5 {
403 do_test index-12.6 {
408 do_test index-12.7 {
413 integrity_check index-12.8
415 # Make sure we cannot drop an automatically created index.
417 do_test index-13.1 {
429 do_test index-13.2 {
431 SELECT name FROM sqlite_master WHERE type='index' AND tbl_name='t5';
436 do_test index-13.3.$i {
440 } {1 {index associated with UNIQUE or PRIMARY KEY constraint cannot be dropped}}
442 do_test index-13.4 {
448 integrity_check index-13.5
450 # Check the sort order of data in an index.
452 do_test index-14.1 {
464 do_test index-14.2 {
469 do_test index-14.3 {
474 do_test index-14.4 {
479 do_test index-14.5 {
484 do_test index-14.6 {
489 do_test index-14.7 {
494 do_test index-14.8 {
499 do_test index-14.9 {
504 do_test index-14.10 {
509 do_test index-14.11 {
514 integrity_check index-14.12
516 do_test index-15.1 {
522 do_test index-15.2 {
542 do_test index-15.3 {
547 integrity_check index-15.4
549 # The following tests - index-16.* - test that when a table definition
551 # single index is generated to enforce the constraint.
555 do_test index-16.1 {
558 SELECT count(*) FROM sqlite_master WHERE tbl_name = 't7' AND type = 'index';
561 do_test index-16.2 {
565 SELECT count(*) FROM sqlite_master WHERE tbl_name = 't7' AND type = 'index';
568 do_test index-16.3 {
572 SELECT count(*) FROM sqlite_master WHERE tbl_name = 't7' AND type = 'index';
575 do_test index-16.4 {
579 SELECT count(*) FROM sqlite_master WHERE tbl_name = 't7' AND type = 'index';
582 do_test index-16.5 {
586 SELECT count(*) FROM sqlite_master WHERE tbl_name = 't7' AND type = 'index';
595 do_test index-17.1 {
599 SELECT name FROM sqlite_master WHERE tbl_name = 't7' AND type = 'index';
602 do_test index-17.2 {
606 } {1 {index associated with UNIQUE or PRIMARY KEY constraint cannot be dropped}}
607 do_test index-17.3 {
611 } {1 {index associated with UNIQUE or PRIMARY KEY constraint cannot be dropped}}
612 do_test index-17.4 {
623 do_test index-18.1 {
628 do_test index-18.1.2 {
634 do_test index-18.2 {
640 do_test index-18.3 {
647 do_test index-18.4 {
653 do_test index-18.5 {
662 do_test index-19.1 {
670 do_test index-19.2 {
676 do_test index-19.3 {
681 do_test index-19.4 {
686 do_test index-19.5 {
692 do_test index-19.6 {
705 do_test index-19.7 {
709 integrity_check index-19.8
711 # Drop index with a quoted name. Ticket #695.
713 do_test index-20.1 {
719 do_test index-20.2 {
725 # Try to create a TEMP index on a non-TEMP table. */
727 do_test index-21.1 {
731 } {1 {cannot create a TEMP index on non-TEMP table "t6"}}
732 do_test index-21.2 {
742 do_execsql_test index-22.0 {
752 do_execsql_test index-23.0 {
760 do_execsql_test index-23.1 {