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 3}

   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     SELECT b FROM t1 ORDER BY a;

   536   }

   537 } {8 5 2 1 3 6 11 9 10 4 7}

   538 integrity_check index-15.1

   539 

   540 # The following tests - index-16.* - test that when a table definition

   541 # includes qualifications that specify the same constraint twice only a

   542 # single index is generated to enforce the constraint.

   543 #

   544 # For example: "CREATE TABLE abc( x PRIMARY KEY, UNIQUE(x) );"

   545 #

   546 do_test index-16.1 {

   547   execsql {

   548     CREATE TABLE t7(c UNIQUE PRIMARY KEY);

   549     SELECT count(*) FROM sqlite_master WHERE tbl_name = 't7' AND type = 'index';

   550   }

   551 } {1}

   552 do_test index-16.2 {

   553   execsql {

   554     DROP TABLE t7;

   555     CREATE TABLE t7(c UNIQUE PRIMARY KEY);

   556     SELECT count(*) FROM sqlite_master WHERE tbl_name = 't7' AND type = 'index';

   557   }

   558 } {1}

   559 do_test index-16.3 {

   560   execsql {

   561     DROP TABLE t7;

   562     CREATE TABLE t7(c PRIMARY KEY, UNIQUE(c) );

   563     SELECT count(*) FROM sqlite_master WHERE tbl_name = 't7' AND type = 'index';

   564   }

   565 } {1}

   566 do_test index-16.4 {

   567   execsql {

   568     DROP TABLE t7;

   569     CREATE TABLE t7(c, d , UNIQUE(c, d), PRIMARY KEY(c, d) );

   570     SELECT count(*) FROM sqlite_master WHERE tbl_name = 't7' AND type = 'index';

   571   }

   572 } {1}

   573 do_test index-16.5 {

   574   execsql {

   575     DROP TABLE t7;

   576     CREATE TABLE t7(c, d , UNIQUE(c), PRIMARY KEY(c, d) );

   577     SELECT count(*) FROM sqlite_master WHERE tbl_name = 't7' AND type = 'index';

   578   }

   579 } {2}

   580 

   581 # Test that automatically create indices are named correctly. The current

   582 # convention is: "sqlite_autoindex_<table name>_<integer>"

   583 #

   584 # Then check that it is an error to try to drop any automtically created

   585 # indices.

   586 do_test index-17.1 {

   587   execsql {

   588     DROP TABLE t7;

   589     CREATE TABLE t7(c, d UNIQUE, UNIQUE(c), PRIMARY KEY(c, d) );

   590     SELECT name FROM sqlite_master WHERE tbl_name = 't7' AND type = 'index';

   591   }

   592 } {sqlite_autoindex_t7_1 sqlite_autoindex_t7_2 sqlite_autoindex_t7_3}

   593 do_test index-17.2 {

   594   catchsql {

   595     DROP INDEX sqlite_autoindex_t7_1;

   596   }

   597 } {1 {index associated with UNIQUE or PRIMARY KEY constraint cannot be dropped}}

   598 do_test index-17.3 {

   599   catchsql {

   600     DROP INDEX IF EXISTS sqlite_autoindex_t7_1;

   601   }

   602 } {1 {index associated with UNIQUE or PRIMARY KEY constraint cannot be dropped}}

   603 do_test index-17.4 {

   604   catchsql {

   605     DROP INDEX IF EXISTS no_such_index;

   606   }

   607 } {0 {}}

   608 

   609 

   610 # The following tests ensure that it is not possible to explicitly name

   611 # a schema object with a name beginning with "sqlite_". Granted that is a

   612 # little outside the focus of this test scripts, but this has got to be

   613 # tested somewhere.

   614 do_test index-18.1 {

   615   catchsql {

   616     CREATE TABLE sqlite_t1(a, b, c);

   617   }

   618 } {1 {object name reserved for internal use: sqlite_t1}}

   619 do_test index-18.2 {

   620   catchsql {

   621     CREATE INDEX sqlite_i1 ON t7(c);

   622   }

   623 } {1 {object name reserved for internal use: sqlite_i1}}

   624 ifcapable view {

   625 do_test index-18.3 {

   626   catchsql {

   627     CREATE VIEW sqlite_v1 AS SELECT * FROM t7;

   628   }

   629 } {1 {object name reserved for internal use: sqlite_v1}}

   630 } ;# ifcapable view

   631 ifcapable {trigger} {

   632   do_test index-18.4 {

   633     catchsql {

   634       CREATE TRIGGER sqlite_tr1 BEFORE INSERT ON t7 BEGIN SELECT 1; END;

   635     }

   636   } {1 {object name reserved for internal use: sqlite_tr1}}

   637 }

   638 do_test index-18.5 {

   639   execsql {

   640     DROP TABLE t7;

   641   }

   642 } {}

   643 

   644 # These tests ensure that if multiple table definition constraints are

   645 # implemented by a single indice, the correct ON CONFLICT policy applies.

   646 ifcapable conflict {

   647   do_test index-19.1 {

   648     execsql {

   649       CREATE TABLE t7(a UNIQUE PRIMARY KEY);

   650       CREATE TABLE t8(a UNIQUE PRIMARY KEY ON CONFLICT ROLLBACK);

   651       INSERT INTO t7 VALUES(1);

   652       INSERT INTO t8 VALUES(1);

   653     }

   654   } {}

   655   do_test index-19.2 {

   656     catchsql {

   657       BEGIN;

   658       INSERT INTO t7 VALUES(1);

   659     }

   660   } {1 {column a is not unique}}

   661   do_test index-19.3 {

   662     catchsql {

   663       BEGIN;

   664     }

   665   } {1 {cannot start a transaction within a transaction}}

   666   do_test index-19.4 {

   667     catchsql {

   668       INSERT INTO t8 VALUES(1);

   669     }

   670   } {1 {column a is not unique}}

   671   do_test index-19.5 {

   672     catchsql {

   673       BEGIN;

   674       COMMIT;

   675     }

   676   } {0 {}}

   677   do_test index-19.6 {

   678     catchsql {

   679       DROP TABLE t7;

   680       DROP TABLE t8;

   681       CREATE TABLE t7(

   682          a PRIMARY KEY ON CONFLICT FAIL, 

   683          UNIQUE(a) ON CONFLICT IGNORE

   684       );

   685     }

   686   } {1 {conflicting ON CONFLICT clauses specified}}

   687 } ; # end of "ifcapable conflict" block

   688 

   689 ifcapable {reindex} {

   690   do_test index-19.7 {

   691     execsql REINDEX

   692   } {}

   693 }

   694 integrity_check index-19.8

   695 

   696 # Drop index with a quoted name.  Ticket #695.

   697 #

   698 do_test index-20.1 {

   699   execsql {

   700     CREATE INDEX "t6i2" ON t6(c);

   701     DROP INDEX "t6i2";

   702   }

   703 } {}

   704 do_test index-20.2 {

   705   execsql {

   706     DROP INDEX "t6i1";

   707   }

   708 } {}

   709    

   710 

   711 finish_test