xref: /sqlite-3.40.0/test/types.test (revision 8d059845) 
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.
12#
13# $Id:
14
15
16set testdir [file dirname $argv0]
17source $testdir/tester.tcl
18
19#
20# Test cases are organized as follows:
21#
22# types-1.*: Insert some records with integers of various sizes, checking
23#            that the integers are stored correctly and can be retrieved.
24# types-2.*: Insert and retrieve some records with reals.
25# types-3.*: Insert and retrieve some records with NULLs.
26# types-4.*: Insert and retrieve some records with strings of various sizes.
27# types-5.*: Some tests inserting and retrieving records with several
28#            fields each.
29#
30
31# Open the table with root-page $rootpage at the btree
32# level. Return a list that is the length of each record
33# in the table, in the tables default scanning order.
34proc record_sizes {rootpage} {
35  set bt [btree_open test.db 10 0]
36  set c [btree_cursor $bt $rootpage 0]
37  btree_first $c
38  while 1 {
39    lappend res [btree_payload_size $c]
40    if {[btree_next $c]} break
41  }
42  btree_close_cursor $c
43  btree_close $bt
44  set res
45}
46
47
48# Create a table and insert some 1-byte integers. Make sure they
49# can be read back OK. These should be 3 byte records.
50do_test types-1.1 {
51  execsql {
52    CREATE TABLE t1(a integer);
53    INSERT INTO t1 VALUES(0);
54    INSERT INTO t1 VALUES(120);
55    INSERT INTO t1 VALUES(-120);
56  }
57} {}
58do_test types-1.2 {
59  execsql {
60    SELECT a FROM t1;
61  }
62} {0 120 -120}
63
64# Try some 2-byte integers (4 byte records)
65do_test types-1.3 {
66  execsql {
67    INSERT INTO t1 VALUES(30000);
68    INSERT INTO t1 VALUES(-30000);
69  }
70} {}
71do_test types-1.4 {
72  execsql {
73    SELECT a FROM t1;
74  }
75} {0 120 -120 30000 -30000}
76
77# 4-byte integers (6 byte records)
78do_test types-1.5 {
79  execsql {
80    INSERT INTO t1 VALUES(2100000000);
81    INSERT INTO t1 VALUES(-2100000000);
82  }
83} {}
84do_test types-1.6 {
85  execsql {
86    SELECT a FROM t1;
87  }
88} {0 120 -120 30000 -30000 2100000000 -2100000000}
89
90# 8-byte integers (10 byte records)
91do_test types-1.7 {
92  execsql {
93    INSERT INTO t1 VALUES(9000000*1000000*1000000);
94    INSERT INTO t1 VALUES(-9000000*1000000*1000000);
95  }
96} {}
97do_test types-1.8 {
98  execsql {
99    SELECT a FROM t1;
100  }
101} [list 0 120 -120 30000 -30000 2100000000 -2100000000 \
102        9000000000000000000 -9000000000000000000]
103
104# Check that all the record sizes are as we expected.
105do_test types-1.9 {
106  set root [db eval {select rootpage from sqlite_master where name = 't1'}]
107  record_sizes $root
108} {3 3 3 4 4 6 6 10 10}
109
110# Insert some reals. These should be 10 byte records.
111do_test types-2.1 {
112  execsql {
113    CREATE TABLE t2(a float);
114    INSERT INTO t2 VALUES(0.0 + 0.0);
115    INSERT INTO t2 VALUES(12345.678 + 0.0);
116    INSERT INTO t2 VALUES(-12345.678 + 0.0);
117  }
118} {}
119do_test types-2.2 {
120  execsql {
121    SELECT a FROM t2;
122  }
123} {0 12345.678 -12345.678}
124
125# Check that all the record sizes are as we expected.
126do_test types-2.3 {
127  set root [db eval {select rootpage from sqlite_master where name = 't2'}]
128  record_sizes $root
129} {10 10 10}
130
131# Insert a NULL. This should be a two byte record.
132do_test types-3.1 {
133  execsql {
134    CREATE TABLE t3(a nullvalue);
135    INSERT INTO t3 VALUES(NULL);
136  }
137} {}
138do_test types-3.2 {
139  execsql {
140    SELECT a ISNULL FROM t3;
141  }
142} {1}
143
144# Check that all the record sizes are as we expected.
145do_test types-3.3 {
146  set root [db eval {select rootpage from sqlite_master where name = 't3'}]
147  record_sizes $root
148} {2}
149
150# Insert a couple of strings.
151do_test types-4.1 {
152  set string10 abcdefghij
153  set string500 [string repeat $string10 50]
154  set string500000 [string repeat $string10 50000]
155
156  execsql "
157    CREATE TABLE t4(a string);
158    INSERT INTO t4 VALUES('$string10');
159    INSERT INTO t4 VALUES('$string500');
160    INSERT INTO t4 VALUES('$string500000');
161  "
162} {}
163do_test types-4.2 {
164  execsql {
165    SELECT a FROM t4;
166  }
167} [list $string10 $string500 $string500000]
168
169# Check that all the record sizes are as we expected.
170do_test types-4.3 {
171  set root [db eval {select rootpage from sqlite_master where name = 't4'}]
172  record_sizes $root
173} {13 504 500005}
174
175do_test types-5.1 {
176  execsql {
177    DROP TABLE t1;
178    DROP TABLE t2;
179    DROP TABLE t3;
180    DROP TABLE t4;
181    CREATE TABLE t1(a, b, c);
182  }
183} {}
184do_test types-5.2 {
185  set string10 abcdefghij
186  set string500 [string repeat $string10 50]
187  set string500000 [string repeat $string10 50000]
188
189  execsql "INSERT INTO t1 VALUES(NULL, '$string10', 4000);"
190  execsql "INSERT INTO t1 VALUES('$string500', 4000, NULL);"
191  execsql "INSERT INTO t1 VALUES(4000, NULL, '$string500000');"
192} {}
193do_test types-5.3 {
194  execsql {
195    SELECT * FROM t1;
196  }
197} [list {} $string10 4000 $string500 4000 {} 4000 {} $string500000]
198
199
200finish_test
201