wasCSharpSQLite – Blame information for rev 7
?pathlinks?
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 |