diff options
Diffstat (limited to 'test/types.test')
-rw-r--r-- | test/types.test | 325 |
1 files changed, 325 insertions, 0 deletions
diff --git a/test/types.test b/test/types.test new file mode 100644 index 0000000..62a8efc --- /dev/null +++ b/test/types.test @@ -0,0 +1,325 @@ +# 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 |