3 # The author disclaims copyright to this source code. In place of
4 # a legal notice, here is a blessing:
6 # May you do good and not evil.
7 # May you find forgiveness for yourself and forgive others.
8 # May you share freely, never taking more than you give.
10 #***********************************************************************
11 # This file implements regression tests for SQLite library. Specfically
12 # it tests that the different storage classes (integer, real, text etc.)
15 # $Id: types.test,v 1.14 2004/11/14 21:56:31 drh Exp $
17 set testdir [file dirname $argv0]
18 source $testdir/tester.tcl
20 # Tests in this file are organized roughly as follows:
22 # types-1.*.*: Test that values are stored using the expected storage
23 # classes when various forms of literals are inserted into
24 # columns with different affinities.
25 # types-1.1.*: INSERT INTO <table> VALUES(...)
26 # types-1.2.*: INSERT INTO <table> SELECT...
27 # types-1.3.*: UPDATE <table> SET...
29 # types-2.*.*: Check that values can be stored and retrieving using the
30 # various storage classes.
31 # types-2.1.*: INTEGER
35 # types-2.5.*: Records with a few different storage classes.
37 # types-3.*: Test that the '=' operator respects manifest types.
40 # Disable encryption on the database for this test.
42 set DB [sqlite3 db test.db]
45 # Create a table with one column for each type of affinity
48 CREATE TABLE t1(i integer, n numeric, t text, o blob);
52 # Each element of the following list represents one test case.
54 # The first value of each sub-list is an SQL literal. The following
55 # four value are the storage classes that would be used if the
56 # literal were inserted into a column with affinity INTEGER, NUMERIC, TEXT
57 # or NONE, respectively.
59 { 5.0 integer real text real }
60 { 5 integer integer text integer }
61 { '5.0' integer real text text }
62 { '-5.0' integer real text text }
63 { '-5.0' integer real text text }
64 { '5' integer integer text text }
65 { 'abc' text text text text }
66 { NULL null null null null }
69 lappend values { X'00' blob blob blob blob }
72 # This code tests that the storage classes specified above (in the $values
73 # table) are correctly assigned when values are inserted using a statement
76 # INSERT INTO <table> VALUE(<values>);
80 set lit [lindex $val 0]
81 execsql "DELETE FROM t1;"
82 execsql "INSERT INTO t1 VALUES($lit, $lit, $lit, $lit);"
83 do_test types-1.1.$tnum {
85 SELECT typeof(i), typeof(n), typeof(t), typeof(o) FROM t1;
91 # This code tests that the storage classes specified above (in the $values
92 # table) are correctly assigned when values are inserted using a statement
95 # INSERT INTO t1 SELECT ....
99 set lit [lindex $val 0]
100 execsql "DELETE FROM t1;"
101 execsql "INSERT INTO t1 SELECT $lit, $lit, $lit, $lit;"
102 do_test types-1.2.$tnum {
104 SELECT typeof(i), typeof(n), typeof(t), typeof(o) FROM t1;
106 } [lrange $val 1 end]
110 # This code tests that the storage classes specified above (in the $values
111 # table) are correctly assigned when values are inserted using a statement
114 # UPDATE <table> SET <column> = <value>;
117 foreach val $values {
118 set lit [lindex $val 0]
119 execsql "UPDATE t1 SET i = $lit, n = $lit, t = $lit, o = $lit;"
120 do_test types-1.3.$tnum {
122 SELECT typeof(i), typeof(n), typeof(t), typeof(o) FROM t1;
124 } [lrange $val 1 end]
132 # Open the table with root-page $rootpage at the btree
133 # level. Return a list that is the length of each record
134 # in the table, in the tables default scanning order.
135 proc record_sizes {rootpage} {
136 set bt [btree_open test.db 10 0]
137 set c [btree_cursor $bt $rootpage 0]
140 lappend res [btree_payload_size $c]
141 if {[btree_next $c]} break
143 btree_close_cursor $c
149 # Create a table and insert some 1-byte integers. Make sure they
150 # can be read back OK. These should be 3 byte records.
151 do_test types-2.1.1 {
153 CREATE TABLE t1(a integer);
154 INSERT INTO t1 VALUES(0);
155 INSERT INTO t1 VALUES(120);
156 INSERT INTO t1 VALUES(-120);
159 do_test types-2.1.2 {
165 # Try some 2-byte integers (4 byte records)
166 do_test types-2.1.3 {
168 INSERT INTO t1 VALUES(30000);
169 INSERT INTO t1 VALUES(-30000);
172 do_test types-2.1.4 {
176 } {0 120 -120 30000 -30000}
178 # 4-byte integers (6 byte records)
179 do_test types-2.1.5 {
181 INSERT INTO t1 VALUES(2100000000);
182 INSERT INTO t1 VALUES(-2100000000);
185 do_test types-2.1.6 {
189 } {0 120 -120 30000 -30000 2100000000 -2100000000}
191 # 8-byte integers (10 byte records)
192 do_test types-2.1.7 {
194 INSERT INTO t1 VALUES(9000000*1000000*1000000);
195 INSERT INTO t1 VALUES(-9000000*1000000*1000000);
198 do_test types-2.1.8 {
202 } [list 0 120 -120 30000 -30000 2100000000 -2100000000 \
203 9000000000000000000 -9000000000000000000]
205 # Check that all the record sizes are as we expected.
206 do_test types-2.1.9 {
207 set root [db eval {select rootpage from sqlite_master where name = 't1'}]
209 } {3 3 3 4 4 6 6 10 10}
211 # Insert some reals. These should be 10 byte records.
212 do_test types-2.2.1 {
214 CREATE TABLE t2(a float);
215 INSERT INTO t2 VALUES(0.0);
216 INSERT INTO t2 VALUES(12345.678);
217 INSERT INTO t2 VALUES(-12345.678);
220 do_test types-2.2.2 {
224 } {0.0 12345.678 -12345.678}
226 # Check that all the record sizes are as we expected.
227 do_test types-2.2.3 {
228 set root [db eval {select rootpage from sqlite_master where name = 't2'}]
232 # Insert a NULL. This should be a two byte record.
233 do_test types-2.3.1 {
235 CREATE TABLE t3(a nullvalue);
236 INSERT INTO t3 VALUES(NULL);
239 do_test types-2.3.2 {
241 SELECT a ISNULL FROM t3;
245 # Check that all the record sizes are as we expected.
246 do_test types-2.3.3 {
247 set root [db eval {select rootpage from sqlite_master where name = 't3'}]
251 # Insert a couple of strings.
252 do_test types-2.4.1 {
253 set string10 abcdefghij
254 set string500 [string repeat $string10 50]
255 set string500000 [string repeat $string10 50000]
258 CREATE TABLE t4(a string);
259 INSERT INTO t4 VALUES('$string10');
260 INSERT INTO t4 VALUES('$string500');
261 INSERT INTO t4 VALUES('$string500000');
264 do_test types-2.4.2 {
268 } [list $string10 $string500 $string500000]
270 # Check that all the record sizes are as we expected. This is dependant on
271 # the database encoding.
272 if { $sqlite_options(utf16)==0 || [execsql {pragma encoding}] == "UTF-8" } {
273 do_test types-2.4.3 {
274 set root [db eval {select rootpage from sqlite_master where name = 't4'}]
278 do_test types-2.4.3 {
279 set root [db eval {select rootpage from sqlite_master where name = 't4'}]
284 do_test types-2.5.1 {
290 CREATE TABLE t1(a, b, c);
293 do_test types-2.5.2 {
294 set string10 abcdefghij
295 set string500 [string repeat $string10 50]
296 set string500000 [string repeat $string10 50000]
298 execsql "INSERT INTO t1 VALUES(NULL, '$string10', 4000);"
299 execsql "INSERT INTO t1 VALUES('$string500', 4000, NULL);"
300 execsql "INSERT INTO t1 VALUES(4000, NULL, '$string500000');"
302 do_test types-2.5.3 {
306 } [list {} $string10 4000 $string500 4000 {} 4000 {} $string500000]