| # 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 |