wasCSharpSQLite – Blame information for rev

Subversion Repositories:
Rev:
Rev Author Line No. Line
1 office 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  
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