index.test

sqlite-3.4.1,嵌入式数据库.是一个功能强大的开源数据库,给学习和研发以及小型公司的发展带来了全所未有的好处.

} {0.1 3}
integrity_check index-11.2


# Numeric strings should compare as if they were numbers.  So even if the
# strings are not character-by-character the same, if they represent the
# same number they should compare equal to one another.  Verify that this
# is true in indices.
#
# Updated for sqlite3 v3: SQLite will now store these values as numbers
# (because the affinity of column a is NUMERIC) so the quirky
# representations are not retained. i.e. '+1.0' becomes '1'.
do_test index-12.1 {
  execsql {
    CREATE TABLE t4(a NUM,b);
    INSERT INTO t4 VALUES('0.0',1);
    INSERT INTO t4 VALUES('0.00',2);
    INSERT INTO t4 VALUES('abc',3);
    INSERT INTO t4 VALUES('-1.0',4);
    INSERT INTO t4 VALUES('+1.0',5);
    INSERT INTO t4 VALUES('0',6);
    INSERT INTO t4 VALUES('00000',7);
    SELECT a FROM t4 ORDER BY b;
  }
} {0 0 abc -1 1 0 0}
do_test index-12.2 {
  execsql {
    SELECT a FROM t4 WHERE a==0 ORDER BY b
  }
} {0 0 0 0}
do_test index-12.3 {
  execsql {
    SELECT a FROM t4 WHERE a<0.5 ORDER BY b
  }
} {0 0 -1 0 0}
do_test index-12.4 {
  execsql {
    SELECT a FROM t4 WHERE a>-0.5 ORDER BY b
  }
} {0 0 abc 1 0 0}
do_test index-12.5 {
  execsql {
    CREATE INDEX t4i1 ON t4(a);
    SELECT a FROM t4 WHERE a==0 ORDER BY b
  }
} {0 0 0 0}
do_test index-12.6 {
  execsql {
    SELECT a FROM t4 WHERE a<0.5 ORDER BY b
  }
} {0 0 -1 0 0}
do_test index-12.7 {
  execsql {
    SELECT a FROM t4 WHERE a>-0.5 ORDER BY b
  }
} {0 0 abc 1 0 0}
integrity_check index-12.8

# Make sure we cannot drop an automatically created index.
#
do_test index-13.1 {
  execsql {
   CREATE TABLE t5(
      a int UNIQUE,
      b float PRIMARY KEY,
      c varchar(10),
      UNIQUE(a,c)
   );
   INSERT INTO t5 VALUES(1,2,3);
   SELECT * FROM t5;
  }
} {1 2.0 3}
do_test index-13.2 {
  set ::idxlist [execsql {
    SELECT name FROM sqlite_master WHERE type="index" AND tbl_name="t5";
  }]
  llength $::idxlist
} {3}
for {set i 0} {$i<[llength $::idxlist]} {incr i} {
  do_test index-13.3.$i {
    catchsql "
      DROP INDEX '[lindex $::idxlist $i]';
    "
  } {1 {index associated with UNIQUE or PRIMARY KEY constraint cannot be dropped}}
}
do_test index-13.4 {
  execsql {
    INSERT INTO t5 VALUES('a','b','c');
    SELECT * FROM t5;
  }
} {1 2.0 3 a b c}
integrity_check index-13.5

# Check the sort order of data in an index.
#
do_test index-14.1 {
  execsql {
    CREATE TABLE t6(a,b,c);
    CREATE INDEX t6i1 ON t6(a,b);
    INSERT INTO t6 VALUES('','',1);
    INSERT INTO t6 VALUES('',NULL,2);
    INSERT INTO t6 VALUES(NULL,'',3);
    INSERT INTO t6 VALUES('abc',123,4);
    INSERT INTO t6 VALUES(123,'abc',5);
    SELECT c FROM t6 ORDER BY a,b;
  }
} {3 5 2 1 4}
do_test index-14.2 {
  execsql {
    SELECT c FROM t6 WHERE a='';
  }
} {2 1}
do_test index-14.3 {
  execsql {
    SELECT c FROM t6 WHERE b='';
  }
} {1 3}
do_test index-14.4 {
  execsql {
    SELECT c FROM t6 WHERE a>'';
  }
} {4}
do_test index-14.5 {
  execsql {
    SELECT c FROM t6 WHERE a>='';
  }
} {2 1 4}
do_test index-14.6 {
  execsql {
    SELECT c FROM t6 WHERE a>123;
  }
} {2 1 4}
do_test index-14.7 {
  execsql {
    SELECT c FROM t6 WHERE a>=123;
  }
} {5 2 1 4}
do_test index-14.8 {
  execsql {
    SELECT c FROM t6 WHERE a<'abc';
  }
} {5 2 1}
do_test index-14.9 {
  execsql {
    SELECT c FROM t6 WHERE a<='abc';
  }
} {5 2 1 4}
do_test index-14.10 {
  execsql {
    SELECT c FROM t6 WHERE a<='';
  }
} {5 2 1}
do_test index-14.11 {
  execsql {
    SELECT c FROM t6 WHERE a<'';
  }
} {5}
integrity_check index-14.12

do_test index-15.1 {
  execsql {
    DELETE FROM t1;
    SELECT * FROM t1;
  }
} {}
do_test index-15.2 {
  execsql {
    INSERT INTO t1 VALUES('1.234e5',1);
    INSERT INTO t1 VALUES('12.33e04',2);
    INSERT INTO t1 VALUES('12.35E4',3);
    INSERT INTO t1 VALUES('12.34e',4);
    INSERT INTO t1 VALUES('12.32e+4',5);
    INSERT INTO t1 VALUES('12.36E+04',6);
    INSERT INTO t1 VALUES('12.36E+',7);
    INSERT INTO t1 VALUES('+123.10000E+0003',8);
    INSERT INTO t1 VALUES('+',9);
    INSERT INTO t1 VALUES('+12347.E+02',10);
    INSERT INTO t1 VALUES('+12347E+02',11);
    SELECT b FROM t1 ORDER BY a;
  }
} {8 5 2 1 3 6 11 9 10 4 7}
integrity_check index-15.1

# The following tests - index-16.* - test that when a table definition
# includes qualifications that specify the same constraint twice only a
# single index is generated to enforce the constraint.
#
# For example: "CREATE TABLE abc( x PRIMARY KEY, UNIQUE(x) );"
#
do_test index-16.1 {
  execsql {
    CREATE TABLE t7(c UNIQUE PRIMARY KEY);
    SELECT count(*) FROM sqlite_master WHERE tbl_name = 't7' AND type = 'index';
  }
} {1}
do_test index-16.2 {
  execsql {
    DROP TABLE t7;
    CREATE TABLE t7(c UNIQUE PRIMARY KEY);
    SELECT count(*) FROM sqlite_master WHERE tbl_name = 't7' AND type = 'index';
  }
} {1}
do_test index-16.3 {
  execsql {
    DROP TABLE t7;
    CREATE TABLE t7(c PRIMARY KEY, UNIQUE(c) );
    SELECT count(*) FROM sqlite_master WHERE tbl_name = 't7' AND type = 'index';
  }
} {1}
do_test index-16.4 {
  execsql {
    DROP TABLE t7;
    CREATE TABLE t7(c, d , UNIQUE(c, d), PRIMARY KEY(c, d) );
    SELECT count(*) FROM sqlite_master WHERE tbl_name = 't7' AND type = 'index';
  }
} {1}
do_test index-16.5 {
  execsql {
    DROP TABLE t7;
    CREATE TABLE t7(c, d , UNIQUE(c), PRIMARY KEY(c, d) );
    SELECT count(*) FROM sqlite_master WHERE tbl_name = 't7' AND type = 'index';
  }
} {2}

# Test that automatically create indices are named correctly. The current
# convention is: "sqlite_autoindex_<table name>_<integer>"
#
# Then check that it is an error to try to drop any automtically created
# indices.
do_test index-17.1 {
  execsql {
    DROP TABLE t7;
    CREATE TABLE t7(c, d UNIQUE, UNIQUE(c), PRIMARY KEY(c, d) );
    SELECT name FROM sqlite_master WHERE tbl_name = 't7' AND type = 'index';
  }
} {sqlite_autoindex_t7_1 sqlite_autoindex_t7_2 sqlite_autoindex_t7_3}
do_test index-17.2 {
  catchsql {
    DROP INDEX sqlite_autoindex_t7_1;
  }
} {1 {index associated with UNIQUE or PRIMARY KEY constraint cannot be dropped}}
do_test index-17.3 {
  catchsql {
    DROP INDEX IF EXISTS sqlite_autoindex_t7_1;
  }
} {1 {index associated with UNIQUE or PRIMARY KEY constraint cannot be dropped}}
do_test index-17.4 {
  catchsql {
    DROP INDEX IF EXISTS no_such_index;
  }
} {0 {}}


# The following tests ensure that it is not possible to explicitly name
# a schema object with a name beginning with "sqlite_". Granted that is a
# little outside the focus of this test scripts, but this has got to be
# tested somewhere.
do_test index-18.1 {
  catchsql {
    CREATE TABLE sqlite_t1(a, b, c);
  }
} {1 {object name reserved for internal use: sqlite_t1}}
do_test index-18.2 {
  catchsql {
    CREATE INDEX sqlite_i1 ON t7(c);
  }
} {1 {object name reserved for internal use: sqlite_i1}}
ifcapable view {
do_test index-18.3 {
  catchsql {
    CREATE VIEW sqlite_v1 AS SELECT * FROM t7;
  }
} {1 {object name reserved for internal use: sqlite_v1}}
} ;# ifcapable view
ifcapable {trigger} {
  do_test index-18.4 {
    catchsql {
      CREATE TRIGGER sqlite_tr1 BEFORE INSERT ON t7 BEGIN SELECT 1; END;
    }
  } {1 {object name reserved for internal use: sqlite_tr1}}
}
do_test index-18.5 {
  execsql {
    DROP TABLE t7;
  }
} {}

# These tests ensure that if multiple table definition constraints are
# implemented by a single indice, the correct ON CONFLICT policy applies.
ifcapable conflict {
  do_test index-19.1 {
    execsql {
      CREATE TABLE t7(a UNIQUE PRIMARY KEY);
      CREATE TABLE t8(a UNIQUE PRIMARY KEY ON CONFLICT ROLLBACK);
      INSERT INTO t7 VALUES(1);
      INSERT INTO t8 VALUES(1);
    }
  } {}
  do_test index-19.2 {
    catchsql {
      BEGIN;
      INSERT INTO t7 VALUES(1);
    }
  } {1 {column a is not unique}}
  do_test index-19.3 {
    catchsql {
      BEGIN;
    }
  } {1 {cannot start a transaction within a transaction}}
  do_test index-19.4 {
    catchsql {
      INSERT INTO t8 VALUES(1);
    }
  } {1 {column a is not unique}}
  do_test index-19.5 {
    catchsql {
      BEGIN;
      COMMIT;
    }
  } {0 {}}
  do_test index-19.6 {
    catchsql {
      DROP TABLE t7;
      DROP TABLE t8;
      CREATE TABLE t7(
         a PRIMARY KEY ON CONFLICT FAIL, 
         UNIQUE(a) ON CONFLICT IGNORE
      );
    }
  } {1 {conflicting ON CONFLICT clauses specified}}
} ; # end of "ifcapable conflict" block

ifcapable {reindex} {
  do_test index-19.7 {
    execsql REINDEX
  } {}
}
integrity_check index-19.8

# Drop index with a quoted name.  Ticket #695.
#
do_test index-20.1 {
  execsql {
    CREATE INDEX "t6i2" ON t6(c);
    DROP INDEX "t6i2";
  }
} {}
do_test index-20.2 {
  execsql {
    DROP INDEX "t6i1";
  }
} {}
   

finish_test