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.1.1.1 2012/02/21 17:04:16 misho 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
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to index.test CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test