wasCSharpSQLite – Blame information for rev 4

Subversion Repositories:
Rev:
Rev Author Line No. Line
1 office 1 # 2001 September 15
2 #
3 # The author disclaims copyright to this source code. In place of
4 # a legal notice, here is a blessing:
5 #
6 # May you do good and not evil.
7 # May you find forgiveness for yourself and forgive others.
8 # May you share freely, never taking more than you give.
9 #
10 #***********************************************************************
11 # This file implements regression tests for SQLite library. The
12 # focus of this file is testing the CREATE INDEX statement.
13 #
14 # $Id: index.test,v 1.43 2008/01/16 18:20:42 danielk1977 Exp $
15  
16 set testdir [file dirname $argv0]
17 source $testdir/tester.tcl
18  
19 # Create a basic index and verify it is added to sqlite_master
20 #
21 do_test index-1.1 {
22 execsql {CREATE TABLE test1(f1 int, f2 int, f3 int)}
23 execsql {CREATE INDEX index1 ON test1(f1)}
24 execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
25 } {index1 test1}
26 do_test index-1.1b {
27 execsql {SELECT name, sql, tbl_name, type FROM sqlite_master
28 WHERE name='index1'}
29 } {index1 {CREATE INDEX index1 ON test1(f1)} test1 index}
30 do_test index-1.1c {
31 db close
32 sqlite3 db test.db
33 execsql {SELECT name, sql, tbl_name, type FROM sqlite_master
34 WHERE name='index1'}
35 } {index1 {CREATE INDEX index1 ON test1(f1)} test1 index}
36 do_test index-1.1d {
37 db close
38 sqlite3 db test.db
39 execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
40 } {index1 test1}
41  
42 # Verify that the index dies with the table
43 #
44 do_test index-1.2 {
45 execsql {DROP TABLE test1}
46 execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
47 } {}
48  
49 # Try adding an index to a table that does not exist
50 #
51 do_test index-2.1 {
52 set v [catch {execsql {CREATE INDEX index1 ON test1(f1)}} msg]
53 lappend v $msg
54 } {1 {no such table: main.test1}}
55  
56 # Try adding an index on a column of a table where the table
57 # exists but the column does not.
58 #
59 do_test index-2.1 {
60 execsql {CREATE TABLE test1(f1 int, f2 int, f3 int)}
61 set v [catch {execsql {CREATE INDEX index1 ON test1(f4)}} msg]
62 lappend v $msg
63 } {1 {table test1 has no column named f4}}
64  
65 # Try an index with some columns that match and others that do now.
66 #
67 do_test index-2.2 {
68 set v [catch {execsql {CREATE INDEX index1 ON test1(f1, f2, f4, f3)}} msg]
69 execsql {DROP TABLE test1}
70 lappend v $msg
71 } {1 {table test1 has no column named f4}}
72  
73 # Try creating a bunch of indices on the same table
74 #
75 set r {}
76 for {set i 1} {$i<100} {incr i} {
77 lappend r [format index%02d $i]
78 }
79 do_test index-3.1 {
80 execsql {CREATE TABLE test1(f1 int, f2 int, f3 int, f4 int, f5 int)}
81 for {set i 1} {$i<100} {incr i} {
82 set sql "CREATE INDEX [format index%02d $i] ON test1(f[expr {($i%5)+1}])"
83 execsql $sql
84 }
85 execsql {SELECT name FROM sqlite_master
86 WHERE type='index' AND tbl_name='test1'
87 ORDER BY name}
88 } $r
89 integrity_check index-3.2.1
90 ifcapable {reindex} {
91 do_test index-3.2.2 {
92 execsql REINDEX
93 } {}
94 }
95 integrity_check index-3.2.3
96  
97  
98 # Verify that all the indices go away when we drop the table.
99 #
100 do_test index-3.3 {
101 execsql {DROP TABLE test1}
102 execsql {SELECT name FROM sqlite_master
103 WHERE type='index' AND tbl_name='test1'
104 ORDER BY name}
105 } {}
106  
107 # Create a table and insert values into that table. Then create
108 # an index on that table. Verify that we can select values
109 # from the table correctly using the index.
110 #
111 # Note that the index names "index9" and "indext" are chosen because
112 # they both have the same hash.
113 #
114 do_test index-4.1 {
115 execsql {CREATE TABLE test1(cnt int, power int)}
116 for {set i 1} {$i<20} {incr i} {
117 execsql "INSERT INTO test1 VALUES($i,[expr {1<<$i}])"
118 }
119 execsql {CREATE INDEX index9 ON test1(cnt)}
120 execsql {CREATE INDEX indext ON test1(power)}
121 execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
122 } {index9 indext test1}
123 do_test index-4.2 {
124 execsql {SELECT cnt FROM test1 WHERE power=4}
125 } {2}
126 do_test index-4.3 {
127 execsql {SELECT cnt FROM test1 WHERE power=1024}
128 } {10}
129 do_test index-4.4 {
130 execsql {SELECT power FROM test1 WHERE cnt=6}
131 } {64}
132 do_test index-4.5 {
133 execsql {DROP INDEX indext}
134 execsql {SELECT power FROM test1 WHERE cnt=6}
135 } {64}
136 do_test index-4.6 {
137 execsql {SELECT cnt FROM test1 WHERE power=1024}
138 } {10}
139 do_test index-4.7 {
140 execsql {CREATE INDEX indext ON test1(cnt)}
141 execsql {SELECT power FROM test1 WHERE cnt=6}
142 } {64}
143 do_test index-4.8 {
144 execsql {SELECT cnt FROM test1 WHERE power=1024}
145 } {10}
146 do_test index-4.9 {
147 execsql {DROP INDEX index9}
148 execsql {SELECT power FROM test1 WHERE cnt=6}
149 } {64}
150 do_test index-4.10 {
151 execsql {SELECT cnt FROM test1 WHERE power=1024}
152 } {10}
153 do_test index-4.11 {
154 execsql {DROP INDEX indext}
155 execsql {SELECT power FROM test1 WHERE cnt=6}
156 } {64}
157 do_test index-4.12 {
158 execsql {SELECT cnt FROM test1 WHERE power=1024}
159 } {10}
160 do_test index-4.13 {
161 execsql {DROP TABLE test1}
162 execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
163 } {}
164 integrity_check index-4.14
165  
166 # Do not allow indices to be added to sqlite_master
167 #
168 do_test index-5.1 {
169 set v [catch {execsql {CREATE INDEX index1 ON sqlite_master(name)}} msg]
170 lappend v $msg
171 } {1 {table sqlite_master may not be indexed}}
172 do_test index-5.2 {
173 execsql {SELECT name FROM sqlite_master WHERE type!='meta'}
174 } {}
175  
176 # Do not allow indices with duplicate names to be added
177 #
178 do_test index-6.1 {
179 execsql {CREATE TABLE test1(f1 int, f2 int)}
180 execsql {CREATE TABLE test2(g1 real, g2 real)}
181 execsql {CREATE INDEX index1 ON test1(f1)}
182 set v [catch {execsql {CREATE INDEX index1 ON test2(g1)}} msg]
183 lappend v $msg
184 } {1 {index index1 already exists}}
185 do_test index-6.1.1 {
186 catchsql {CREATE INDEX [index1] ON test2(g1)}
187 } {1 {index index1 already exists}}
188 do_test index-6.1b {
189 execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
190 } {index1 test1 test2}
191 do_test index-6.1c {
192 catchsql {CREATE INDEX IF NOT EXISTS index1 ON test1(f1)}
193 } {0 {}}
194 do_test index-6.2 {
195 set v [catch {execsql {CREATE INDEX test1 ON test2(g1)}} msg]
196 lappend v $msg
197 } {1 {there is already a table named test1}}
198 do_test index-6.2b {
199 execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
200 } {index1 test1 test2}
201 do_test index-6.3 {
202 execsql {DROP TABLE test1}
203 execsql {DROP TABLE test2}
204 execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
205 } {}
206 do_test index-6.4 {
207 execsql {
208 CREATE TABLE test1(a,b);
209 CREATE INDEX index1 ON test1(a);
210 CREATE INDEX index2 ON test1(b);
211 CREATE INDEX index3 ON test1(a,b);
212 DROP TABLE test1;
213 SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name;
214 }
215 } {}
216 integrity_check index-6.5
217  
218  
219 # Create a primary key
220 #
221 do_test index-7.1 {
222 execsql {CREATE TABLE test1(f1 int, f2 int primary key)}
223 for {set i 1} {$i<20} {incr i} {
224 execsql "INSERT INTO test1 VALUES($i,[expr {1<<$i}])"
225 }
226 execsql {SELECT count(*) FROM test1}
227 } {19}
228 do_test index-7.2 {
229 execsql {SELECT f1 FROM test1 WHERE f2=65536}
230 } {16}
231 do_test index-7.3 {
232 execsql {
233 SELECT name FROM sqlite_master
234 WHERE type='index' AND tbl_name='test1'
235 }
236 } {sqlite_autoindex_test1_1}
237 do_test index-7.4 {
238 execsql {DROP table test1}
239 execsql {SELECT name FROM sqlite_master WHERE type!='meta'}
240 } {}
241 integrity_check index-7.5
242  
243 # Make sure we cannot drop a non-existant index.
244 #
245 do_test index-8.1 {
246 set v [catch {execsql {DROP INDEX index1}} msg]
247 lappend v $msg
248 } {1 {no such index: index1}}
249  
250 # Make sure we don't actually create an index when the EXPLAIN keyword
251 # is used.
252 #
253 do_test index-9.1 {
254 execsql {CREATE TABLE tab1(a int)}
255 ifcapable {explain} {
256 execsql {EXPLAIN CREATE INDEX idx1 ON tab1(a)}
257 }
258 execsql {SELECT name FROM sqlite_master WHERE tbl_name='tab1'}
259 } {tab1}
260 do_test index-9.2 {
261 execsql {CREATE INDEX idx1 ON tab1(a)}
262 execsql {SELECT name FROM sqlite_master WHERE tbl_name='tab1' ORDER BY name}
263 } {idx1 tab1}
264 integrity_check index-9.3
265  
266 # Allow more than one entry with the same key.
267 #
268 do_test index-10.0 {
269 execsql {
270 CREATE TABLE t1(a int, b int);
271 CREATE INDEX i1 ON t1(a);
272 INSERT INTO t1 VALUES(1,2);
273 INSERT INTO t1 VALUES(2,4);
274 INSERT INTO t1 VALUES(3,8);
275 INSERT INTO t1 VALUES(1,12);
276 SELECT b FROM t1 WHERE a=1 ORDER BY b;
277 }
278 } {2 12}
279 do_test index-10.1 {
280 execsql {
281 SELECT b FROM t1 WHERE a=2 ORDER BY b;
282 }
283 } {4}
284 do_test index-10.2 {
285 execsql {
286 DELETE FROM t1 WHERE b=12;
287 SELECT b FROM t1 WHERE a=1 ORDER BY b;
288 }
289 } {2}
290 do_test index-10.3 {
291 execsql {
292 DELETE FROM t1 WHERE b=2;
293 SELECT b FROM t1 WHERE a=1 ORDER BY b;
294 }
295 } {}
296 do_test index-10.4 {
297 execsql {
298 DELETE FROM t1;
299 INSERT INTO t1 VALUES (1,1);
300 INSERT INTO t1 VALUES (1,2);
301 INSERT INTO t1 VALUES (1,3);
302 INSERT INTO t1 VALUES (1,4);
303 INSERT INTO t1 VALUES (1,5);
304 INSERT INTO t1 VALUES (1,6);
305 INSERT INTO t1 VALUES (1,7);
306 INSERT INTO t1 VALUES (1,8);
307 INSERT INTO t1 VALUES (1,9);
308 INSERT INTO t1 VALUES (2,0);
309 SELECT b FROM t1 WHERE a=1 ORDER BY b;
310 }
311 } {1 2 3 4 5 6 7 8 9}
312 do_test index-10.5 {
313 ifcapable subquery {
314 execsql { DELETE FROM t1 WHERE b IN (2, 4, 6, 8); }
315 } else {
316 execsql { DELETE FROM t1 WHERE b = 2 OR b = 4 OR b = 6 OR b = 8; }
317 }
318 execsql {
319 SELECT b FROM t1 WHERE a=1 ORDER BY b;
320 }
321 } {1 3 5 7 9}
322 do_test index-10.6 {
323 execsql {
324 DELETE FROM t1 WHERE b>2;
325 SELECT b FROM t1 WHERE a=1 ORDER BY b;
326 }
327 } {1}
328 do_test index-10.7 {
329 execsql {
330 DELETE FROM t1 WHERE b=1;
331 SELECT b FROM t1 WHERE a=1 ORDER BY b;
332 }
333 } {}
334 do_test index-10.8 {
335 execsql {
336 SELECT b FROM t1 ORDER BY b;
337 }
338 } {0}
339 integrity_check index-10.9
340  
341 # Automatically create an index when we specify a primary key.
342 #
343 do_test index-11.1 {
344 execsql {
345 CREATE TABLE t3(
346 a text,
347 b int,
348 c float,
349 PRIMARY KEY(b)
350 );
351 }
352 for {set i 1} {$i<=50} {incr i} {
353 execsql "INSERT INTO t3 VALUES('x${i}x',$i,0.$i)"
354 }
355 set sqlite_search_count 0
356 concat [execsql {SELECT c FROM t3 WHERE b==10}] $sqlite_search_count
357 } {0.1 2}
358 integrity_check index-11.2
359  
360  
361 # Numeric strings should compare as if they were numbers. So even if the
362 # strings are not character-by-character the same, if they represent the
363 # same number they should compare equal to one another. Verify that this
364 # is true in indices.
365 #
366 # Updated for sqlite3 v3: SQLite will now store these values as numbers
367 # (because the affinity of column a is NUMERIC) so the quirky
368 # representations are not retained. i.e. '+1.0' becomes '1'.
369 do_test index-12.1 {
370 execsql {
371 CREATE TABLE t4(a NUM,b);
372 INSERT INTO t4 VALUES('0.0',1);
373 INSERT INTO t4 VALUES('0.00',2);
374 INSERT INTO t4 VALUES('abc',3);
375 INSERT INTO t4 VALUES('-1.0',4);
376 INSERT INTO t4 VALUES('+1.0',5);
377 INSERT INTO t4 VALUES('0',6);
378 INSERT INTO t4 VALUES('00000',7);
379 SELECT a FROM t4 ORDER BY b;
380 }
381 } {0 0 abc -1 1 0 0}
382 do_test index-12.2 {
383 execsql {
384 SELECT a FROM t4 WHERE a==0 ORDER BY b
385 }
386 } {0 0 0 0}
387 do_test index-12.3 {
388 execsql {
389 SELECT a FROM t4 WHERE a<0.5 ORDER BY b
390 }
391 } {0 0 -1 0 0}
392 do_test index-12.4 {
393 execsql {
394 SELECT a FROM t4 WHERE a>-0.5 ORDER BY b
395 }
396 } {0 0 abc 1 0 0}
397 do_test index-12.5 {
398 execsql {
399 CREATE INDEX t4i1 ON t4(a);
400 SELECT a FROM t4 WHERE a==0 ORDER BY b
401 }
402 } {0 0 0 0}
403 do_test index-12.6 {
404 execsql {
405 SELECT a FROM t4 WHERE a<0.5 ORDER BY b
406 }
407 } {0 0 -1 0 0}
408 do_test index-12.7 {
409 execsql {
410 SELECT a FROM t4 WHERE a>-0.5 ORDER BY b
411 }
412 } {0 0 abc 1 0 0}
413 integrity_check index-12.8
414  
415 # Make sure we cannot drop an automatically created index.
416 #
417 do_test index-13.1 {
418 execsql {
419 CREATE TABLE t5(
420 a int UNIQUE,
421 b float PRIMARY KEY,
422 c varchar(10),
423 UNIQUE(a,c)
424 );
425 INSERT INTO t5 VALUES(1,2,3);
426 SELECT * FROM t5;
427 }
428 } {1 2.0 3}
429 do_test index-13.2 {
430 set ::idxlist [execsql {
431 SELECT name FROM sqlite_master WHERE type="index" AND tbl_name="t5";
432 }]
433 llength $::idxlist
434 } {3}
435 for {set i 0} {$i<[llength $::idxlist]} {incr i} {
436 do_test index-13.3.$i {
437 catchsql "
438 DROP INDEX '[lindex $::idxlist $i]';
439 "
440 } {1 {index associated with UNIQUE or PRIMARY KEY constraint cannot be dropped}}
441 }
442 do_test index-13.4 {
443 execsql {
444 INSERT INTO t5 VALUES('a','b','c');
445 SELECT * FROM t5;
446 }
447 } {1 2.0 3 a b c}
448 integrity_check index-13.5
449  
450 # Check the sort order of data in an index.
451 #
452 do_test index-14.1 {
453 execsql {
454 CREATE TABLE t6(a,b,c);
455 CREATE INDEX t6i1 ON t6(a,b);
456 INSERT INTO t6 VALUES('','',1);
457 INSERT INTO t6 VALUES('',NULL,2);
458 INSERT INTO t6 VALUES(NULL,'',3);
459 INSERT INTO t6 VALUES('abc',123,4);
460 INSERT INTO t6 VALUES(123,'abc',5);
461 SELECT c FROM t6 ORDER BY a,b;
462 }
463 } {3 5 2 1 4}
464 do_test index-14.2 {
465 execsql {
466 SELECT c FROM t6 WHERE a='';
467 }
468 } {2 1}
469 do_test index-14.3 {
470 execsql {
471 SELECT c FROM t6 WHERE b='';
472 }
473 } {1 3}
474 do_test index-14.4 {
475 execsql {
476 SELECT c FROM t6 WHERE a>'';
477 }
478 } {4}
479 do_test index-14.5 {
480 execsql {
481 SELECT c FROM t6 WHERE a>='';
482 }
483 } {2 1 4}
484 do_test index-14.6 {
485 execsql {
486 SELECT c FROM t6 WHERE a>123;
487 }
488 } {2 1 4}
489 do_test index-14.7 {
490 execsql {
491 SELECT c FROM t6 WHERE a>=123;
492 }
493 } {5 2 1 4}
494 do_test index-14.8 {
495 execsql {
496 SELECT c FROM t6 WHERE a<'abc';
497 }
498 } {5 2 1}
499 do_test index-14.9 {
500 execsql {
501 SELECT c FROM t6 WHERE a<='abc';
502 }
503 } {5 2 1 4}
504 do_test index-14.10 {
505 execsql {
506 SELECT c FROM t6 WHERE a<='';
507 }
508 } {5 2 1}
509 do_test index-14.11 {
510 execsql {
511 SELECT c FROM t6 WHERE a<'';
512 }
513 } {5}
514 integrity_check index-14.12
515  
516 do_test index-15.1 {
517 execsql {
518 DELETE FROM t1;
519 SELECT * FROM t1;
520 }
521 } {}
522 do_test index-15.2 {
523 execsql {
524 INSERT INTO t1 VALUES('1.234e5',1);
525 INSERT INTO t1 VALUES('12.33e04',2);
526 INSERT INTO t1 VALUES('12.35E4',3);
527 INSERT INTO t1 VALUES('12.34e',4);
528 INSERT INTO t1 VALUES('12.32e+4',5);
529 INSERT INTO t1 VALUES('12.36E+04',6);
530 INSERT INTO t1 VALUES('12.36E+',7);
531 INSERT INTO t1 VALUES('+123.10000E+0003',8);
532 INSERT INTO t1 VALUES('+',9);
533 INSERT INTO t1 VALUES('+12347.E+02',10);
534 INSERT INTO t1 VALUES('+12347E+02',11);
535 INSERT INTO t1 VALUES('+.125E+04',12);
536 INSERT INTO t1 VALUES('-.125E+04',13);
537 INSERT INTO t1 VALUES('.125E+0',14);
538 INSERT INTO t1 VALUES('.125',15);
539 SELECT b FROM t1 ORDER BY a, b;
540 }
541 } {13 14 15 12 8 5 2 1 3 6 10 11 9 4 7}
542 do_test index-15.3 {
543 execsql {
544 SELECT b FROM t1 WHERE typeof(a) IN ('integer','real') ORDER BY b;
545 }
546 } {1 2 3 5 6 8 10 11 12 13 14 15}
547 integrity_check index-15.4
548  
549 # The following tests - index-16.* - test that when a table definition
550 # includes qualifications that specify the same constraint twice only a
551 # single index is generated to enforce the constraint.
552 #
553 # For example: "CREATE TABLE abc( x PRIMARY KEY, UNIQUE(x) );"
554 #
555 do_test index-16.1 {
556 execsql {
557 CREATE TABLE t7(c UNIQUE PRIMARY KEY);
558 SELECT count(*) FROM sqlite_master WHERE tbl_name = 't7' AND type = 'index';
559 }
560 } {1}
561 do_test index-16.2 {
562 execsql {
563 DROP TABLE t7;
564 CREATE TABLE t7(c UNIQUE PRIMARY KEY);
565 SELECT count(*) FROM sqlite_master WHERE tbl_name = 't7' AND type = 'index';
566 }
567 } {1}
568 do_test index-16.3 {
569 execsql {
570 DROP TABLE t7;
571 CREATE TABLE t7(c PRIMARY KEY, UNIQUE(c) );
572 SELECT count(*) FROM sqlite_master WHERE tbl_name = 't7' AND type = 'index';
573 }
574 } {1}
575 do_test index-16.4 {
576 execsql {
577 DROP TABLE t7;
578 CREATE TABLE t7(c, d , UNIQUE(c, d), PRIMARY KEY(c, d) );
579 SELECT count(*) FROM sqlite_master WHERE tbl_name = 't7' AND type = 'index';
580 }
581 } {1}
582 do_test index-16.5 {
583 execsql {
584 DROP TABLE t7;
585 CREATE TABLE t7(c, d , UNIQUE(c), PRIMARY KEY(c, d) );
586 SELECT count(*) FROM sqlite_master WHERE tbl_name = 't7' AND type = 'index';
587 }
588 } {2}
589  
590 # Test that automatically create indices are named correctly. The current
591 # convention is: "sqlite_autoindex_<table name>_<integer>"
592 #
593 # Then check that it is an error to try to drop any automtically created
594 # indices.
595 do_test index-17.1 {
596 execsql {
597 DROP TABLE t7;
598 CREATE TABLE t7(c, d UNIQUE, UNIQUE(c), PRIMARY KEY(c, d) );
599 SELECT name FROM sqlite_master WHERE tbl_name = 't7' AND type = 'index';
600 }
601 } {sqlite_autoindex_t7_1 sqlite_autoindex_t7_2 sqlite_autoindex_t7_3}
602 do_test index-17.2 {
603 catchsql {
604 DROP INDEX sqlite_autoindex_t7_1;
605 }
606 } {1 {index associated with UNIQUE or PRIMARY KEY constraint cannot be dropped}}
607 do_test index-17.3 {
608 catchsql {
609 DROP INDEX IF EXISTS sqlite_autoindex_t7_1;
610 }
611 } {1 {index associated with UNIQUE or PRIMARY KEY constraint cannot be dropped}}
612 do_test index-17.4 {
613 catchsql {
614 DROP INDEX IF EXISTS no_such_index;
615 }
616 } {0 {}}
617  
618  
619 # The following tests ensure that it is not possible to explicitly name
620 # a schema object with a name beginning with "sqlite_". Granted that is a
621 # little outside the focus of this test scripts, but this has got to be
622 # tested somewhere.
623 do_test index-18.1 {
624 catchsql {
625 CREATE TABLE sqlite_t1(a, b, c);
626 }
627 } {1 {object name reserved for internal use: sqlite_t1}}
628 do_test index-18.2 {
629 catchsql {
630 CREATE INDEX sqlite_i1 ON t7(c);
631 }
632 } {1 {object name reserved for internal use: sqlite_i1}}
633 ifcapable view {
634 do_test index-18.3 {
635 catchsql {
636 CREATE VIEW sqlite_v1 AS SELECT * FROM t7;
637 }
638 } {1 {object name reserved for internal use: sqlite_v1}}
639 } ;# ifcapable view
640 ifcapable {trigger} {
641 do_test index-18.4 {
642 catchsql {
643 CREATE TRIGGER sqlite_tr1 BEFORE INSERT ON t7 BEGIN SELECT 1; END;
644 }
645 } {1 {object name reserved for internal use: sqlite_tr1}}
646 }
647 do_test index-18.5 {
648 execsql {
649 DROP TABLE t7;
650 }
651 } {}
652  
653 # These tests ensure that if multiple table definition constraints are
654 # implemented by a single indice, the correct ON CONFLICT policy applies.
655 ifcapable conflict {
656 do_test index-19.1 {
657 execsql {
658 CREATE TABLE t7(a UNIQUE PRIMARY KEY);
659 CREATE TABLE t8(a UNIQUE PRIMARY KEY ON CONFLICT ROLLBACK);
660 INSERT INTO t7 VALUES(1);
661 INSERT INTO t8 VALUES(1);
662 }
663 } {}
664 do_test index-19.2 {
665 catchsql {
666 BEGIN;
667 INSERT INTO t7 VALUES(1);
668 }
669 } {1 {column a is not unique}}
670 do_test index-19.3 {
671 catchsql {
672 BEGIN;
673 }
674 } {1 {cannot start a transaction within a transaction}}
675 do_test index-19.4 {
676 catchsql {
677 INSERT INTO t8 VALUES(1);
678 }
679 } {1 {column a is not unique}}
680 do_test index-19.5 {
681 catchsql {
682 BEGIN;
683 COMMIT;
684 }
685 } {0 {}}
686 do_test index-19.6 {
687 catchsql {
688 DROP TABLE t7;
689 DROP TABLE t8;
690 CREATE TABLE t7(
691 a PRIMARY KEY ON CONFLICT FAIL,
692 UNIQUE(a) ON CONFLICT IGNORE
693 );
694 }
695 } {1 {conflicting ON CONFLICT clauses specified}}
696 } ; # end of "ifcapable conflict" block
697  
698 ifcapable {reindex} {
699 do_test index-19.7 {
700 execsql REINDEX
701 } {}
702 }
703 integrity_check index-19.8
704  
705 # Drop index with a quoted name. Ticket #695.
706 #
707 do_test index-20.1 {
708 execsql {
709 CREATE INDEX "t6i2" ON t6(c);
710 DROP INDEX "t6i2";
711 }
712 } {}
713 do_test index-20.2 {
714 execsql {
715 DROP INDEX "t6i1";
716 }
717 } {}
718  
719  
720 finish_test