/trunk/src/sqlite/test/types.test
Unknown | 325 lines | 299 code | 26 blank | 0 comment | 0 complexity | 3108cfa6e31516761431c39609adbaa7 MD5 | raw file
Possible License(s): BSD-3-Clause
- # 2001 September 15
- #
- # The author disclaims copyright to this source code. In place of
- # a legal notice, here is a blessing:
- #
- # May you do good and not evil.
- # May you find forgiveness for yourself and forgive others.
- # May you share freely, never taking more than you give.
- #
- #***********************************************************************
- # This file implements regression tests for SQLite library. Specfically
- # it tests that the different storage classes (integer, real, text etc.)
- # all work correctly.
- #
- # $Id: types.test,v 1.20 2009/06/29 06:00:37 danielk1977 Exp $
- set testdir [file dirname $argv0]
- source $testdir/tester.tcl
- # Tests in this file are organized roughly as follows:
- #
- # types-1.*.*: Test that values are stored using the expected storage
- # classes when various forms of literals are inserted into
- # columns with different affinities.
- # types-1.1.*: INSERT INTO <table> VALUES(...)
- # types-1.2.*: INSERT INTO <table> SELECT...
- # types-1.3.*: UPDATE <table> SET...
- #
- # types-2.*.*: Check that values can be stored and retrieving using the
- # various storage classes.
- # types-2.1.*: INTEGER
- # types-2.2.*: REAL
- # types-2.3.*: NULL
- # types-2.4.*: TEXT
- # types-2.5.*: Records with a few different storage classes.
- #
- # types-3.*: Test that the '=' operator respects manifest types.
- #
- # Disable encryption on the database for this test.
- db close
- set DB [sqlite3 db test.db; sqlite3_connection_pointer db]
- sqlite3_rekey $DB {}
- # Create a table with one column for each type of affinity
- do_test types-1.1.0 {
- execsql {
- CREATE TABLE t1(i integer, n numeric, t text, o blob);
- }
- } {}
- # Each element of the following list represents one test case.
- #
- # The first value of each sub-list is an SQL literal. The following
- # four value are the storage classes that would be used if the
- # literal were inserted into a column with affinity INTEGER, NUMERIC, TEXT
- # or NONE, respectively.
- set values {
- { 5.0 integer integer text real }
- { 5.1 real real text real }
- { 5 integer integer text integer }
- { '5.0' integer integer text text }
- { '5.1' real real text text }
- { '-5.0' integer integer text text }
- { '-5.0' integer integer text text }
- { '5' integer integer text text }
- { 'abc' text text text text }
- { NULL null null null null }
- }
- ifcapable {bloblit} {
- lappend values { X'00' blob blob blob blob }
- }
- # This code tests that the storage classes specified above (in the $values
- # table) are correctly assigned when values are inserted using a statement
- # of the form:
- #
- # INSERT INTO <table> VALUE(<values>);
- #
- set tnum 1
- foreach val $values {
- set lit [lindex $val 0]
- execsql "DELETE FROM t1;"
- execsql "INSERT INTO t1 VALUES($lit, $lit, $lit, $lit);"
- do_test types-1.1.$tnum {
- execsql {
- SELECT typeof(i), typeof(n), typeof(t), typeof(o) FROM t1;
- }
- } [lrange $val 1 end]
- incr tnum
- }
- # This code tests that the storage classes specified above (in the $values
- # table) are correctly assigned when values are inserted using a statement
- # of the form:
- #
- # INSERT INTO t1 SELECT ....
- #
- set tnum 1
- foreach val $values {
- set lit [lindex $val 0]
- execsql "DELETE FROM t1;"
- execsql "INSERT INTO t1 SELECT $lit, $lit, $lit, $lit;"
- do_test types-1.2.$tnum {
- execsql {
- SELECT typeof(i), typeof(n), typeof(t), typeof(o) FROM t1;
- }
- } [lrange $val 1 end]
- incr tnum
- }
- # This code tests that the storage classes specified above (in the $values
- # table) are correctly assigned when values are inserted using a statement
- # of the form:
- #
- # UPDATE <table> SET <column> = <value>;
- #
- set tnum 1
- foreach val $values {
- set lit [lindex $val 0]
- execsql "UPDATE t1 SET i = $lit, n = $lit, t = $lit, o = $lit;"
- do_test types-1.3.$tnum {
- execsql {
- SELECT typeof(i), typeof(n), typeof(t), typeof(o) FROM t1;
- }
- } [lrange $val 1 end]
- incr tnum
- }
- execsql {
- DROP TABLE t1;
- }
- # Open the table with root-page $rootpage at the btree
- # level. Return a list that is the length of each record
- # in the table, in the tables default scanning order.
- proc record_sizes {rootpage} {
- set bt [btree_open test.db 10]
- btree_begin_transaction $bt
- set c [btree_cursor $bt $rootpage 0]
- btree_first $c
- while 1 {
- lappend res [btree_payload_size $c]
- if {[btree_next $c]} break
- }
- btree_close_cursor $c
- btree_close $bt
- set res
- }
- # Create a table and insert some 1-byte integers. Make sure they
- # can be read back OK. These should be 3 byte records.
- do_test types-2.1.1 {
- execsql {
- CREATE TABLE t1(a integer);
- INSERT INTO t1 VALUES(0);
- INSERT INTO t1 VALUES(120);
- INSERT INTO t1 VALUES(-120);
- }
- } {}
- do_test types-2.1.2 {
- execsql {
- SELECT a FROM t1;
- }
- } {0 120 -120}
- # Try some 2-byte integers (4 byte records)
- do_test types-2.1.3 {
- execsql {
- INSERT INTO t1 VALUES(30000);
- INSERT INTO t1 VALUES(-30000);
- }
- } {}
- do_test types-2.1.4 {
- execsql {
- SELECT a FROM t1;
- }
- } {0 120 -120 30000 -30000}
- # 4-byte integers (6 byte records)
- do_test types-2.1.5 {
- execsql {
- INSERT INTO t1 VALUES(2100000000);
- INSERT INTO t1 VALUES(-2100000000);
- }
- } {}
- do_test types-2.1.6 {
- execsql {
- SELECT a FROM t1;
- }
- } {0 120 -120 30000 -30000 2100000000 -2100000000}
- # 8-byte integers (10 byte records)
- do_test types-2.1.7 {
- execsql {
- INSERT INTO t1 VALUES(9000000*1000000*1000000);
- INSERT INTO t1 VALUES(-9000000*1000000*1000000);
- }
- } {}
- do_test types-2.1.8 {
- execsql {
- SELECT a FROM t1;
- }
- } [list 0 120 -120 30000 -30000 2100000000 -2100000000 \
- 9000000000000000000 -9000000000000000000]
- # Check that all the record sizes are as we expected.
- ifcapable legacyformat {
- do_test types-2.1.9 {
- set root [db eval {select rootpage from sqlite_master where name = 't1'}]
- record_sizes $root
- } {3 3 3 4 4 6 6 10 10}
- } else {
- do_test types-2.1.9 {
- set root [db eval {select rootpage from sqlite_master where name = 't1'}]
- record_sizes $root
- } {2 3 3 4 4 6 6 10 10}
- }
-
- # Insert some reals. These should be 10 byte records.
- do_test types-2.2.1 {
- execsql {
- CREATE TABLE t2(a float);
- INSERT INTO t2 VALUES(0.0);
- INSERT INTO t2 VALUES(12345.678);
- INSERT INTO t2 VALUES(-12345.678);
- }
- } {}
- do_test types-2.2.2 {
- execsql {
- SELECT a FROM t2;
- }
- } {0.0 12345.678 -12345.678}
- # Check that all the record sizes are as we expected.
- ifcapable legacyformat {
- do_test types-2.2.3 {
- set root [db eval {select rootpage from sqlite_master where name = 't2'}]
- record_sizes $root
- } {3 10 10}
- } else {
- do_test types-2.2.3 {
- set root [db eval {select rootpage from sqlite_master where name = 't2'}]
- record_sizes $root
- } {2 10 10}
- }
-
- # Insert a NULL. This should be a two byte record.
- do_test types-2.3.1 {
- execsql {
- CREATE TABLE t3(a nullvalue);
- INSERT INTO t3 VALUES(NULL);
- }
- } {}
- do_test types-2.3.2 {
- execsql {
- SELECT a ISNULL FROM t3;
- }
- } {1}
- # Check that all the record sizes are as we expected.
- do_test types-2.3.3 {
- set root [db eval {select rootpage from sqlite_master where name = 't3'}]
- record_sizes $root
- } {2}
- # Insert a couple of strings.
- do_test types-2.4.1 {
- set string10 abcdefghij
- set string500 [string repeat $string10 50]
- set string500000 [string repeat $string10 50000]
- execsql "
- CREATE TABLE t4(a string);
- INSERT INTO t4 VALUES('$string10');
- INSERT INTO t4 VALUES('$string500');
- INSERT INTO t4 VALUES('$string500000');
- "
- } {}
- do_test types-2.4.2 {
- execsql {
- SELECT a FROM t4;
- }
- } [list $string10 $string500 $string500000]
- # Check that all the record sizes are as we expected. This is dependant on
- # the database encoding.
- if { $sqlite_options(utf16)==0 || [execsql {pragma encoding}] == "UTF-8" } {
- do_test types-2.4.3 {
- set root [db eval {select rootpage from sqlite_master where name = 't4'}]
- record_sizes $root
- } {12 503 500004}
- } else {
- do_test types-2.4.3 {
- set root [db eval {select rootpage from sqlite_master where name = 't4'}]
- record_sizes $root
- } {22 1003 1000004}
- }
- do_test types-2.5.1 {
- execsql {
- DROP TABLE t1;
- DROP TABLE t2;
- DROP TABLE t3;
- DROP TABLE t4;
- CREATE TABLE t1(a, b, c);
- }
- } {}
- do_test types-2.5.2 {
- set string10 abcdefghij
- set string500 [string repeat $string10 50]
- set string500000 [string repeat $string10 50000]
- execsql "INSERT INTO t1 VALUES(NULL, '$string10', 4000);"
- execsql "INSERT INTO t1 VALUES('$string500', 4000, NULL);"
- execsql "INSERT INTO t1 VALUES(4000, NULL, '$string500000');"
- } {}
- do_test types-2.5.3 {
- execsql {
- SELECT * FROM t1;
- }
- } [list {} $string10 4000 $string500 4000 {} 4000 {} $string500000]
- finish_test