PageRenderTime 131ms CodeModel.GetById 18ms app.highlight 34ms RepoModel.GetById 55ms app.codeStats 0ms

/trunk/src/sqlite/test/index.test

#
Unknown | 720 lines | 688 code | 32 blank | 0 comment | 0 complexity | 6ca1c2c4f8d204a509610bcf89589101 MD5 | raw file
Possible License(s): BSD-3-Clause
  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
 16set testdir [file dirname $argv0]
 17source $testdir/tester.tcl
 18
 19# Create a basic index and verify it is added to sqlite_master
 20#
 21do_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}
 26do_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}
 30do_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}
 36do_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#
 44do_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#
 51do_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#
 59do_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#
 67do_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#
 75set r {}
 76for {set i 1} {$i<100} {incr i} {
 77  lappend r [format index%02d $i]
 78}
 79do_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
 89integrity_check index-3.2.1
 90ifcapable {reindex} {
 91  do_test index-3.2.2 {
 92    execsql REINDEX
 93  } {}
 94}
 95integrity_check index-3.2.3
 96
 97
 98# Verify that all the indices go away when we drop the table.
 99#
100do_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#
114do_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}
123do_test index-4.2 {
124  execsql {SELECT cnt FROM test1 WHERE power=4}
125} {2}
126do_test index-4.3 {
127  execsql {SELECT cnt FROM test1 WHERE power=1024}
128} {10}
129do_test index-4.4 {
130  execsql {SELECT power FROM test1 WHERE cnt=6}
131} {64}
132do_test index-4.5 {
133  execsql {DROP INDEX indext}
134  execsql {SELECT power FROM test1 WHERE cnt=6}
135} {64}
136do_test index-4.6 {
137  execsql {SELECT cnt FROM test1 WHERE power=1024}
138} {10}
139do_test index-4.7 {
140  execsql {CREATE INDEX indext ON test1(cnt)}
141  execsql {SELECT power FROM test1 WHERE cnt=6}
142} {64}
143do_test index-4.8 {
144  execsql {SELECT cnt FROM test1 WHERE power=1024}
145} {10}
146do_test index-4.9 {
147  execsql {DROP INDEX index9}
148  execsql {SELECT power FROM test1 WHERE cnt=6}
149} {64}
150do_test index-4.10 {
151  execsql {SELECT cnt FROM test1 WHERE power=1024}
152} {10}
153do_test index-4.11 {
154  execsql {DROP INDEX indext}
155  execsql {SELECT power FROM test1 WHERE cnt=6}
156} {64}
157do_test index-4.12 {
158  execsql {SELECT cnt FROM test1 WHERE power=1024}
159} {10}
160do_test index-4.13 {
161  execsql {DROP TABLE test1}
162  execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
163} {}
164integrity_check index-4.14
165
166# Do not allow indices to be added to sqlite_master
167#
168do_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}}
172do_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#
178do_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}}
185do_test index-6.1.1 {
186  catchsql {CREATE INDEX [index1] ON test2(g1)}
187} {1 {index index1 already exists}}
188do_test index-6.1b {
189  execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
190} {index1 test1 test2}
191do_test index-6.1c {
192  catchsql {CREATE INDEX IF NOT EXISTS index1 ON test1(f1)}
193} {0 {}}
194do_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}}
198do_test index-6.2b {
199  execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
200} {index1 test1 test2}
201do_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} {}
206do_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} {}
216integrity_check index-6.5
217
218
219# Create a primary key
220#
221do_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}
228do_test index-7.2 {
229  execsql {SELECT f1 FROM test1 WHERE f2=65536}
230} {16}
231do_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}
237do_test index-7.4 {
238  execsql {DROP table test1}
239  execsql {SELECT name FROM sqlite_master WHERE type!='meta'}
240} {}
241integrity_check index-7.5
242
243# Make sure we cannot drop a non-existant index.
244#
245do_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#
253do_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}
260do_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}
264integrity_check index-9.3
265
266# Allow more than one entry with the same key.
267#
268do_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}
279do_test index-10.1 {
280  execsql {
281    SELECT b FROM t1 WHERE a=2 ORDER BY b;
282  }
283} {4}
284do_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}
290do_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} {}
296do_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}
312do_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}
322do_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}
328do_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} {}
334do_test index-10.8 {
335  execsql {
336    SELECT b FROM t1 ORDER BY b;
337  }
338} {0}
339integrity_check index-10.9
340
341# Automatically create an index when we specify a primary key.
342#
343do_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}
358integrity_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'.
369do_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}
382do_test index-12.2 {
383  execsql {
384    SELECT a FROM t4 WHERE a==0 ORDER BY b
385  }
386} {0 0 0 0}
387do_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}
392do_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}
397do_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}
403do_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}
408do_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}
413integrity_check index-12.8
414
415# Make sure we cannot drop an automatically created index.
416#
417do_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}
429do_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}
435for {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}
442do_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}
448integrity_check index-13.5
449
450# Check the sort order of data in an index.
451#
452do_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}
464do_test index-14.2 {
465  execsql {
466    SELECT c FROM t6 WHERE a='';
467  }
468} {2 1}
469do_test index-14.3 {
470  execsql {
471    SELECT c FROM t6 WHERE b='';
472  }
473} {1 3}
474do_test index-14.4 {
475  execsql {
476    SELECT c FROM t6 WHERE a>'';
477  }
478} {4}
479do_test index-14.5 {
480  execsql {
481    SELECT c FROM t6 WHERE a>='';
482  }
483} {2 1 4}
484do_test index-14.6 {
485  execsql {
486    SELECT c FROM t6 WHERE a>123;
487  }
488} {2 1 4}
489do_test index-14.7 {
490  execsql {
491    SELECT c FROM t6 WHERE a>=123;
492  }
493} {5 2 1 4}
494do_test index-14.8 {
495  execsql {
496    SELECT c FROM t6 WHERE a<'abc';
497  }
498} {5 2 1}
499do_test index-14.9 {
500  execsql {
501    SELECT c FROM t6 WHERE a<='abc';
502  }
503} {5 2 1 4}
504do_test index-14.10 {
505  execsql {
506    SELECT c FROM t6 WHERE a<='';
507  }
508} {5 2 1}
509do_test index-14.11 {
510  execsql {
511    SELECT c FROM t6 WHERE a<'';
512  }
513} {5}
514integrity_check index-14.12
515
516do_test index-15.1 {
517  execsql {
518    DELETE FROM t1;
519    SELECT * FROM t1;
520  }
521} {}
522do_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}
542do_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}
547integrity_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#
555do_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}
561do_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}
568do_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}
575do_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}
582do_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.
595do_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}
602do_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}}
607do_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}}
612do_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.
623do_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}}
628do_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}}
633ifcapable view {
634do_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
640ifcapable {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}
647do_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.
655ifcapable 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
698ifcapable {reindex} {
699  do_test index-19.7 {
700    execsql REINDEX
701  } {}
702}
703integrity_check index-19.8
704
705# Drop index with a quoted name.  Ticket #695.
706#
707do_test index-20.1 {
708  execsql {
709    CREATE INDEX "t6i2" ON t6(c);
710    DROP INDEX "t6i2";
711  }
712} {}
713do_test index-20.2 {
714  execsql {
715    DROP INDEX "t6i1";
716  }
717} {}
718   
719
720finish_test