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.1.1.1 2012/02/21 17:04:16 misho Exp $
16:
17: set testdir [file dirname $argv0]
18: source $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.
41: db close
42: set DB [sqlite3 db test.db; sqlite3_connection_pointer db]
43: sqlite3_rekey $DB {}
44:
45: # Create a table with one column for each type of affinity
46: do_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.
58: set 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: }
70: ifcapable {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: #
80: set tnum 1
81: foreach 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: #
99: set tnum 1
100: foreach 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: #
118: set tnum 1
119: foreach 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:
130: execsql {
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.
137: proc 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.
154: do_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: } {}
162: do_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)
169: do_test types-2.1.3 {
170: execsql {
171: INSERT INTO t1 VALUES(30000);
172: INSERT INTO t1 VALUES(-30000);
173: }
174: } {}
175: do_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)
182: do_test types-2.1.5 {
183: execsql {
184: INSERT INTO t1 VALUES(2100000000);
185: INSERT INTO t1 VALUES(-2100000000);
186: }
187: } {}
188: do_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)
195: do_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: } {}
201: do_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.
209: ifcapable 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.
222: do_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: } {}
230: do_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.
237: ifcapable 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.
250: do_test types-2.3.1 {
251: execsql {
252: CREATE TABLE t3(a nullvalue);
253: INSERT INTO t3 VALUES(NULL);
254: }
255: } {}
256: do_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.
263: do_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.
269: do_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: } {}
281: do_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.
289: if { $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:
301: do_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: } {}
310: do_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: } {}
319: do_test types-2.5.3 {
320: execsql {
321: SELECT * FROM t1;
322: }
323: } [list {} $string10 4000 $string500 4000 {} 4000 {} $string500000]
324:
325: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to types.test CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test