github.com/jdgcs/sqlite3@v1.12.1-0.20210908114423-bc5f96e4dd51/testdata/tcl/index3.test (about)

     1  # 2005-02-14
     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  
    15  
    16  set testdir [file dirname $argv0]
    17  source $testdir/tester.tcl
    18  
    19  # Ticket #1115.  Make sure that when a UNIQUE index is created on a
    20  # non-unique column (or columns) that it fails and that it leaves no
    21  # residue behind.
    22  #
    23  do_test index3-1.1 {
    24    execsql {
    25      CREATE TABLE t1(a);
    26      INSERT INTO t1 VALUES(1);
    27      INSERT INTO t1 VALUES(1);
    28      SELECT * FROM t1;
    29    }
    30  } {1 1}
    31  do_test index3-1.2 {
    32    catchsql {
    33      BEGIN;
    34      CREATE UNIQUE INDEX i1 ON t1(a);
    35    }
    36  } {1 {UNIQUE constraint failed: t1.a}}
    37  do_test index3-1.3 {
    38    catchsql COMMIT;
    39  } {0 {}}
    40  integrity_check index3-1.4
    41  
    42  # Backwards compatibility test:
    43  #
    44  # Verify that CREATE INDEX statements that use strings instead of 
    45  # identifiers for the the column names continue to work correctly.
    46  # This is undocumented behavior retained for backwards compatiblity.
    47  #
    48  do_execsql_test index3-2.1 {
    49    DROP TABLE t1;
    50    CREATE TABLE t1(a, b, c, d, e, 
    51                    PRIMARY KEY('a'), UNIQUE('b' COLLATE nocase DESC));
    52    CREATE INDEX t1c ON t1('c');
    53    CREATE INDEX t1d ON t1('d' COLLATE binary ASC);
    54    WITH RECURSIVE c(x) AS (VALUES(1) UNION SELECT x+1 FROM c WHERE x<30)
    55      INSERT INTO t1(a,b,c,d,e) 
    56        SELECT x, printf('ab%03xxy',x), x, x, x FROM c;
    57  } {}
    58  do_execsql_test index3-2.2 {
    59    SELECT a FROM t1 WHERE b='ab005xy' COLLATE nocase;
    60  } {5}
    61  do_execsql_test index3-2.2eqp {
    62    EXPLAIN QUERY PLAN
    63    SELECT a FROM t1 WHERE b='ab005xy' COLLATE nocase;
    64  } {/USING INDEX/}
    65  do_execsql_test index3-2.3 {
    66    SELECT name FROM sqlite_master WHERE tbl_name='t1' ORDER BY name
    67  } {sqlite_autoindex_t1_1 sqlite_autoindex_t1_2 t1 t1c t1d}
    68  do_execsql_test index3-2.4 {
    69    CREATE TABLE t2a(a integer, b, PRIMARY KEY(a));
    70    CREATE TABLE t2b("a" integer, b, PRIMARY KEY("a"));
    71    CREATE TABLE t2c([a] integer, b, PRIMARY KEY([a]));
    72    CREATE TABLE t2d('a' integer, b, PRIMARY KEY('a'));
    73  }
    74  do_execsql_test index3-2.5 {
    75    SELECT name FROM sqlite_master WHERE tbl_name LIKE 't2_' ORDER BY name
    76  } {t2a t2b t2c t2d}
    77   
    78  
    79  
    80  
    81  
    82  # This test corrupts the database file so it must be the last test
    83  # in the series.
    84  #
    85  do_test index3-99.1 {
    86    sqlite3_db_config db DEFENSIVE 0
    87    execsql {
    88      PRAGMA writable_schema=on;
    89      UPDATE sqlite_master SET sql='nonsense' WHERE name='t1d'
    90    }
    91    db close
    92    catch { sqlite3 db test.db }
    93    catchsql { DROP INDEX t1c }
    94  } {1 {malformed database schema (t1d)}}
    95  
    96  finish_test