1# 2001 September 15
2#
3# The author disclaims copyright to this source code.  In place of
4# a legal notice, here is a blessing:
5#
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.
9#
10#***********************************************************************
11# This file implements regression tests for SQLite library. Specfically
12# it tests that the different storage classes (integer, real, text etc.)
13# all work correctly.
14#
15# $Id: types.test,v 1.20 2009/06/29 06:00:37 danielk1977 Exp $
16
17set testdir [file dirname $argv0]
18source $testdir/tester.tcl
19
20# Tests in this file are organized roughly as follows:
21#
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...
28#
29# types-2.*.*: Check that values can be stored and retrieving using the
30#              various storage classes.
31# types-2.1.*: INTEGER
32# types-2.2.*: REAL
33# types-2.3.*: NULL
34# types-2.4.*: TEXT
35# types-2.5.*: Records with a few different storage classes.
36#
37# types-3.*: Test that the '=' operator respects manifest types.
38#
39
40# Disable encryption on the database for this test.
41db close
42set DB [sqlite3 db test.db; sqlite3_connection_pointer db]
43sqlite3_rekey $DB {}
44
45# Create a table with one column for each type of affinity
46do_test types-1.1.0 {
47  execsql {
48    CREATE TABLE t1(i integer, n numeric, t text, o blob);
49  }
50} {}
51
52# Each element of the following list represents one test case.
53#
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.
58set values {
59  { 5.0    integer integer text real    }
60  { 5.1    real    real    text real    }
61  { 5      integer integer text integer }
62  { '5.0'  integer integer text text    }
63  { '5.1'  real    real    text text    }
64  { '-5.0' integer integer text text    }
65  { '-5.0' integer integer text text    }
66  { '5'    integer integer text text    }
67  { 'abc'  text    text    text text    }
68  { NULL   null    null    null null    }
69}
70ifcapable {bloblit} {
71  lappend values  { X'00'  blob    blob    blob blob    }
72}
73
74# This code tests that the storage classes specified above (in the $values
75# table) are correctly assigned when values are inserted using a statement
76# of the form:
77#
78# INSERT INTO <table> VALUE(<values>);
79#
80set tnum 1
81foreach val $values {
82  set lit [lindex $val 0]
83  execsql "DELETE FROM t1;"
84  execsql "INSERT INTO t1 VALUES($lit, $lit, $lit, $lit);"
85  do_test types-1.1.$tnum {
86    execsql {
87      SELECT typeof(i), typeof(n), typeof(t), typeof(o) FROM t1;
88    }
89  } [lrange $val 1 end]
90  incr tnum
91}
92
93# This code tests that the storage classes specified above (in the $values
94# table) are correctly assigned when values are inserted using a statement
95# of the form:
96#
97# INSERT INTO t1 SELECT ....
98#
99set tnum 1
100foreach val $values {
101  set lit [lindex $val 0]
102  execsql "DELETE FROM t1;"
103  execsql "INSERT INTO t1 SELECT $lit, $lit, $lit, $lit;"
104  do_test types-1.2.$tnum {
105    execsql {
106      SELECT typeof(i), typeof(n), typeof(t), typeof(o) FROM t1;
107    }
108  } [lrange $val 1 end]
109  incr tnum
110}
111
112# This code tests that the storage classes specified above (in the $values
113# table) are correctly assigned when values are inserted using a statement
114# of the form:
115#
116# UPDATE <table> SET <column> = <value>;
117#
118set tnum 1
119foreach val $values {
120  set lit [lindex $val 0]
121  execsql "UPDATE t1 SET i = $lit, n = $lit, t = $lit, o = $lit;"
122  do_test types-1.3.$tnum {
123    execsql {
124      SELECT typeof(i), typeof(n), typeof(t), typeof(o) FROM t1;
125    }
126  } [lrange $val 1 end]
127  incr tnum
128}
129
130execsql {
131  DROP TABLE t1;
132}
133
134# Open the table with root-page $rootpage at the btree
135# level. Return a list that is the length of each record
136# in the table, in the tables default scanning order.
137proc record_sizes {rootpage} {
138  set bt [btree_open test.db 10]
139  btree_begin_transaction $bt
140  set c [btree_cursor $bt $rootpage 0]
141  btree_first $c
142  while 1 {
143    lappend res [btree_payload_size $c]
144    if {[btree_next $c]} break
145  }
146  btree_close_cursor $c
147  btree_close $bt
148  set res
149}
150
151
152# Create a table and insert some 1-byte integers. Make sure they
153# can be read back OK. These should be 3 byte records.
154do_test types-2.1.1 {
155  execsql {
156    CREATE TABLE t1(a integer);
157    INSERT INTO t1 VALUES(0);
158    INSERT INTO t1 VALUES(120);
159    INSERT INTO t1 VALUES(-120);
160  }
161} {}
162do_test types-2.1.2 {
163  execsql {
164    SELECT a FROM t1;
165  }
166} {0 120 -120}
167
168# Try some 2-byte integers (4 byte records)
169do_test types-2.1.3 {
170  execsql {
171    INSERT INTO t1 VALUES(30000);
172    INSERT INTO t1 VALUES(-30000);
173  }
174} {}
175do_test types-2.1.4 {
176  execsql {
177    SELECT a FROM t1;
178  }
179} {0 120 -120 30000 -30000}
180
181# 4-byte integers (6 byte records)
182do_test types-2.1.5 {
183  execsql {
184    INSERT INTO t1 VALUES(2100000000);
185    INSERT INTO t1 VALUES(-2100000000);
186  }
187} {}
188do_test types-2.1.6 {
189  execsql {
190    SELECT a FROM t1;
191  }
192} {0 120 -120 30000 -30000 2100000000 -2100000000}
193
194# 8-byte integers (10 byte records)
195do_test types-2.1.7 {
196  execsql {
197    INSERT INTO t1 VALUES(9000000*1000000*1000000);
198    INSERT INTO t1 VALUES(-9000000*1000000*1000000);
199  }
200} {}
201do_test types-2.1.8 {
202  execsql {
203    SELECT a FROM t1;
204  }
205} [list 0 120 -120 30000 -30000 2100000000 -2100000000 \
206        9000000000000000000 -9000000000000000000]
207
208# Check that all the record sizes are as we expected.
209ifcapable legacyformat {
210  do_test types-2.1.9 {
211    set root [db eval {select rootpage from sqlite_master where name = 't1'}]
212    record_sizes $root
213  } {3 3 3 4 4 6 6 10 10}
214} else {
215  do_test types-2.1.9 {
216    set root [db eval {select rootpage from sqlite_master where name = 't1'}]
217    record_sizes $root
218  } {2 3 3 4 4 6 6 10 10}
219}
220
221# Insert some reals. These should be 10 byte records.
222do_test types-2.2.1 {
223  execsql {
224    CREATE TABLE t2(a float);
225    INSERT INTO t2 VALUES(0.0);
226    INSERT INTO t2 VALUES(12345.678);
227    INSERT INTO t2 VALUES(-12345.678);
228  }
229} {}
230do_test types-2.2.2 {
231  execsql {
232    SELECT a FROM t2;
233  }
234} {0.0 12345.678 -12345.678}
235
236# Check that all the record sizes are as we expected.
237ifcapable legacyformat {
238  do_test types-2.2.3 {
239    set root [db eval {select rootpage from sqlite_master where name = 't2'}]
240    record_sizes $root
241  } {3 10 10}
242} else {
243  do_test types-2.2.3 {
244    set root [db eval {select rootpage from sqlite_master where name = 't2'}]
245    record_sizes $root
246  } {2 10 10}
247}
248
249# Insert a NULL. This should be a two byte record.
250do_test types-2.3.1 {
251  execsql {
252    CREATE TABLE t3(a nullvalue);
253    INSERT INTO t3 VALUES(NULL);
254  }
255} {}
256do_test types-2.3.2 {
257  execsql {
258    SELECT a ISNULL FROM t3;
259  }
260} {1}
261
262# Check that all the record sizes are as we expected.
263do_test types-2.3.3 {
264  set root [db eval {select rootpage from sqlite_master where name = 't3'}]
265  record_sizes $root
266} {2}
267
268# Insert a couple of strings.
269do_test types-2.4.1 {
270  set string10 abcdefghij
271  set string500 [string repeat $string10 50]
272  set string500000 [string repeat $string10 50000]
273
274  execsql "
275    CREATE TABLE t4(a string);
276    INSERT INTO t4 VALUES('$string10');
277    INSERT INTO t4 VALUES('$string500');
278    INSERT INTO t4 VALUES('$string500000');
279  "
280} {}
281do_test types-2.4.2 {
282  execsql {
283    SELECT a FROM t4;
284  }
285} [list $string10 $string500 $string500000]
286
287# Check that all the record sizes are as we expected. This is dependant on
288# the database encoding.
289if { $sqlite_options(utf16)==0 || [execsql {pragma encoding}] == "UTF-8" } {
290  do_test types-2.4.3 {
291    set root [db eval {select rootpage from sqlite_master where name = 't4'}]
292    record_sizes $root
293  } {12 503 500004}
294} else {
295  do_test types-2.4.3 {
296    set root [db eval {select rootpage from sqlite_master where name = 't4'}]
297    record_sizes $root
298  } {22 1003 1000004}
299}
300
301do_test types-2.5.1 {
302  execsql {
303    DROP TABLE t1;
304    DROP TABLE t2;
305    DROP TABLE t3;
306    DROP TABLE t4;
307    CREATE TABLE t1(a, b, c);
308  }
309} {}
310do_test types-2.5.2 {
311  set string10 abcdefghij
312  set string500 [string repeat $string10 50]
313  set string500000 [string repeat $string10 50000]
314
315  execsql "INSERT INTO t1 VALUES(NULL, '$string10', 4000);"
316  execsql "INSERT INTO t1 VALUES('$string500', 4000, NULL);"
317  execsql "INSERT INTO t1 VALUES(4000, NULL, '$string500000');"
318} {}
319do_test types-2.5.3 {
320  execsql {
321    SELECT * FROM t1;
322  }
323} [list {} $string10 4000 $string500 4000 {} 4000 {} $string500000]
324
325finish_test
326