# 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 VALUES(...) # types-1.2.*: INSERT INTO
SELECT... # types-1.3.*: UPDATE
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
VALUE(); # 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
SET = ; # 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 0] 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