wasCSharpSQLite – Blame information for rev

Subversion Repositories:
Rev:
Rev Author Line No. Line
1 office 1 # 2002 May 24
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. The focus of
12 # this file is testing the SQLite routines used for converting between the
13 # various suported unicode encodings (UTF-8, UTF-16, UTF-16le and
14 # UTF-16be).
15 #
16 # $Id: enc2.test,v 1.29 2007/10/09 08:29:32 danielk1977 Exp $
17  
18 set testdir [file dirname $argv0]
19 source $testdir/tester.tcl
20  
21 # If UTF16 support is disabled, ignore the tests in this file
22 #
23 ifcapable {!utf16} {
24 finish_test
25 return
26 }
27  
28 # The rough organisation of tests in this file is:
29 #
30 # enc2.1.*: Simple tests with a UTF-8 db.
31 # enc2.2.*: Simple tests with a UTF-16LE db.
32 # enc2.3.*: Simple tests with a UTF-16BE db.
33 # enc2.4.*: Test that attached databases must have the same text encoding
34 # as the main database.
35 # enc2.5.*: Test the behaviour of the library when a collation sequence is
36 # not available for the most desirable text encoding.
37 # enc2.6.*: Similar test for user functions.
38 # enc2.7.*: Test that the VerifyCookie opcode protects against assuming the
39 # wrong text encoding for the database.
40 # enc2.8.*: Test sqlite3_complete16()
41 #
42  
43 db close
44  
45 # Return the UTF-8 representation of the supplied UTF-16 string $str.
46 proc utf8 {str} {
47 # If $str ends in two 0x00 0x00 bytes, knock these off before
48 # converting to UTF-8 using TCL.
49 binary scan $str \c* vals
50 if {[lindex $vals end]==0 && [lindex $vals end-1]==0} {
51 set str [binary format \c* [lrange $vals 0 end-2]]
52 }
53  
54 set r [encoding convertfrom unicode $str]
55 return $r
56 }
57  
58 #
59 # This proc contains all the tests in this file. It is run
60 # three times. Each time the file 'test.db' contains a database
61 # with the following contents:
62 set dbcontents {
63 CREATE TABLE t1(a PRIMARY KEY, b, c);
64 INSERT INTO t1 VALUES('one', 'I', 1);
65 }
66 # This proc tests that we can open and manipulate the test.db
67 # database, and that it is possible to retreive values in
68 # various text encodings.
69 #
70 proc run_test_script {t enc} {
71  
72 # Open the database and pull out a (the) row.
73 do_test $t.1 {
74 sqlite3 db test.db; set DB [sqlite3_connection_pointer db]
75 execsql {SELECT * FROM t1}
76 } {one I 1}
77  
78 # Insert some data
79 do_test $t.2 {
80 execsql {INSERT INTO t1 VALUES('two', 'II', 2);}
81 execsql {SELECT * FROM t1}
82 } {one I 1 two II 2}
83  
84 # Insert some data
85 do_test $t.3 {
86 execsql {
87 INSERT INTO t1 VALUES('three','III',3);
88 INSERT INTO t1 VALUES('four','IV',4);
89 INSERT INTO t1 VALUES('five','V',5);
90 }
91 execsql {SELECT * FROM t1}
92 } {one I 1 two II 2 three III 3 four IV 4 five V 5}
93  
94 # Use the index
95 do_test $t.4 {
96 execsql {
97 SELECT * FROM t1 WHERE a = 'one';
98 }
99 } {one I 1}
100 do_test $t.5 {
101 execsql {
102 SELECT * FROM t1 WHERE a = 'four';
103 }
104 } {four IV 4}
105 ifcapable subquery {
106 do_test $t.6 {
107 execsql {
108 SELECT * FROM t1 WHERE a IN ('one', 'two');
109 }
110 } {one I 1 two II 2}
111 }
112  
113 # Now check that we can retrieve data in both UTF-16 and UTF-8
114 do_test $t.7 {
115 set STMT [sqlite3_prepare $DB "SELECT a FROM t1 WHERE c>3;" -1 TAIL]
116 sqlite3_step $STMT
117 sqlite3_column_text $STMT 0
118 } {four}
119  
120 do_test $t.8 {
121 sqlite3_step $STMT
122 utf8 [sqlite3_column_text16 $STMT 0]
123 } {five}
124  
125 do_test $t.9 {
126 sqlite3_finalize $STMT
127 } SQLITE_OK
128  
129 ifcapable vacuum {
130 execsql VACUUM
131 }
132  
133 do_test $t.10 {
134 db eval {PRAGMA encoding}
135 } $enc
136  
137 }
138  
139 # The three unicode encodings understood by SQLite.
140 set encodings [list UTF-8 UTF-16le UTF-16be]
141  
142 set sqlite_os_trace 0
143 set i 1
144 foreach enc $encodings {
145 file delete -force test.db
146 sqlite3 db test.db
147 db eval "PRAGMA encoding = \"$enc\""
148 execsql $dbcontents
149 do_test enc2-$i.0.1 {
150 db eval {PRAGMA encoding}
151 } $enc
152 do_test enc2-$i.0.2 {
153 db eval {PRAGMA encoding=UTF8}
154 db eval {PRAGMA encoding}
155 } $enc
156 do_test enc2-$i.0.3 {
157 db eval {PRAGMA encoding=UTF16le}
158 db eval {PRAGMA encoding}
159 } $enc
160 do_test enc2-$i.0.4 {
161 db eval {PRAGMA encoding=UTF16be}
162 db eval {PRAGMA encoding}
163 } $enc
164  
165 db close
166 run_test_script enc2-$i $enc
167 db close
168 incr i
169 }
170  
171 # Test that it is an error to try to attach a database with a different
172 # encoding to the main database.
173 ifcapable attach {
174 do_test enc2-4.1 {
175 file delete -force test.db
176 sqlite3 db test.db
177 db eval "PRAGMA encoding = 'UTF-8'"
178 db eval "CREATE TABLE abc(a, b, c);"
179 } {}
180 do_test enc2-4.2 {
181 file delete -force test2.db
182 sqlite3 db2 test2.db
183 db2 eval "PRAGMA encoding = 'UTF-16'"
184 db2 eval "CREATE TABLE abc(a, b, c);"
185 } {}
186 do_test enc2-4.3 {
187 catchsql {
188 ATTACH 'test2.db' as aux;
189 }
190 } {1 {attached databases must use the same text encoding as main database}}
191 db2 close
192 db close
193 }
194  
195 # The following tests - enc2-5.* - test that SQLite selects the correct
196 # collation sequence when more than one is available.
197  
198 set ::values [list one two three four five]
199 set ::test_collate_enc INVALID
200 proc test_collate {enc lhs rhs} {
201 set ::test_collate_enc $enc
202 set l [lsearch -exact $::values $lhs]
203 set r [lsearch -exact $::values $rhs]
204 set res [expr $l - $r]
205 # puts "enc=$enc lhs=$lhs/$l rhs=$rhs/$r res=$res"
206 return $res
207 }
208  
209 file delete -force test.db
210 sqlite3 db test.db; set DB [sqlite3_connection_pointer db]
211 do_test enc2-5.0 {
212 execsql {
213 CREATE TABLE t5(a);
214 INSERT INTO t5 VALUES('one');
215 INSERT INTO t5 VALUES('two');
216 INSERT INTO t5 VALUES('five');
217 INSERT INTO t5 VALUES('three');
218 INSERT INTO t5 VALUES('four');
219 }
220 } {}
221 do_test enc2-5.1 {
222 add_test_collate $DB 1 1 1
223 set res [execsql {SELECT * FROM t5 ORDER BY 1 COLLATE test_collate;}]
224 lappend res $::test_collate_enc
225 } {one two three four five UTF-8}
226 do_test enc2-5.2 {
227 add_test_collate $DB 0 1 0
228 set res [execsql {SELECT * FROM t5 ORDER BY 1 COLLATE test_collate}]
229 lappend res $::test_collate_enc
230 } {one two three four five UTF-16LE}
231 do_test enc2-5.3 {
232 add_test_collate $DB 0 0 1
233 set res [execsql {SELECT * FROM t5 ORDER BY 1 COLLATE test_collate}]
234 lappend res $::test_collate_enc
235 } {one two three four five UTF-16BE}
236  
237 db close
238 file delete -force test.db
239 sqlite3 db test.db; set DB [sqlite3_connection_pointer db]
240 execsql {pragma encoding = 'UTF-16LE'}
241 do_test enc2-5.4 {
242 execsql {
243 CREATE TABLE t5(a);
244 INSERT INTO t5 VALUES('one');
245 INSERT INTO t5 VALUES('two');
246 INSERT INTO t5 VALUES('five');
247 INSERT INTO t5 VALUES('three');
248 INSERT INTO t5 VALUES('four');
249 }
250 } {}
251 do_test enc2-5.5 {
252 add_test_collate $DB 1 1 1
253 set res [execsql {SELECT * FROM t5 ORDER BY 1 COLLATE test_collate}]
254 lappend res $::test_collate_enc
255 } {one two three four five UTF-16LE}
256 do_test enc2-5.6 {
257 add_test_collate $DB 1 0 1
258 set res [execsql {SELECT * FROM t5 ORDER BY 1 COLLATE test_collate}]
259 lappend res $::test_collate_enc
260 } {one two three four five UTF-16BE}
261 do_test enc2-5.7 {
262 add_test_collate $DB 1 0 0
263 set res [execsql {SELECT * FROM t5 ORDER BY 1 COLLATE test_collate}]
264 lappend res $::test_collate_enc
265 } {one two three four five UTF-8}
266  
267 db close
268 file delete -force test.db
269 sqlite3 db test.db; set DB [sqlite3_connection_pointer db]
270 execsql {pragma encoding = 'UTF-16BE'}
271 do_test enc2-5.8 {
272 execsql {
273 CREATE TABLE t5(a);
274 INSERT INTO t5 VALUES('one');
275 INSERT INTO t5 VALUES('two');
276 INSERT INTO t5 VALUES('five');
277 INSERT INTO t5 VALUES('three');
278 INSERT INTO t5 VALUES('four');
279 }
280 } {}
281 do_test enc2-5.9 {
282 add_test_collate $DB 1 1 1
283 set res [execsql {SELECT * FROM t5 ORDER BY 1 COLLATE test_collate}]
284 lappend res $::test_collate_enc
285 } {one two three four five UTF-16BE}
286 do_test enc2-5.10 {
287 add_test_collate $DB 1 1 0
288 set res [execsql {SELECT * FROM t5 ORDER BY 1 COLLATE test_collate}]
289 lappend res $::test_collate_enc
290 } {one two three four five UTF-16LE}
291 do_test enc2-5.11 {
292 add_test_collate $DB 1 0 0
293 set res [execsql {SELECT * FROM t5 ORDER BY 1 COLLATE test_collate}]
294 lappend res $::test_collate_enc
295 } {one two three four five UTF-8}
296  
297 # Also test that a UTF-16 collation factory works.
298 do_test enc2-5-12 {
299 add_test_collate $DB 0 0 0
300 catchsql {
301 SELECT * FROM t5 ORDER BY 1 COLLATE test_collate
302 }
303 } {1 {no such collation sequence: test_collate}}
304 do_test enc2-5.13 {
305 add_test_collate_needed $DB
306 set res [execsql {SELECT * FROM t5 ORDER BY 1 COLLATE test_collate; }]
307 lappend res $::test_collate_enc
308 } {one two three four five UTF-16BE}
309 do_test enc2-5.14 {
310 set ::sqlite_last_needed_collation
311 } test_collate
312  
313 db close
314 file delete -force test.db
315  
316 do_test enc2-5.15 {
317 sqlite3 db test.db; set ::DB [sqlite3_connection_pointer db]
318 add_test_collate_needed $::DB
319 set ::sqlite_last_needed_collation
320 } {}
321 do_test enc2-5.16 {
322 execsql {CREATE TABLE t1(a varchar collate test_collate);}
323 } {}
324 do_test enc2-5.17 {
325 set ::sqlite_last_needed_collation
326 } {test_collate}
327  
328 # The following tests - enc2-6.* - test that SQLite selects the correct
329 # user function when more than one is available.
330  
331 proc test_function {enc arg} {
332 return "$enc $arg"
333 }
334  
335 db close
336 file delete -force test.db
337 sqlite3 db test.db; set DB [sqlite3_connection_pointer db]
338 execsql {pragma encoding = 'UTF-8'}
339 do_test enc2-6.0 {
340 execsql {
341 CREATE TABLE t5(a);
342 INSERT INTO t5 VALUES('one');
343 }
344 } {}
345 do_test enc2-6.1 {
346 add_test_function $DB 1 1 1
347 execsql {
348 SELECT test_function('sqlite')
349 }
350 } {{UTF-8 sqlite}}
351 db close
352 sqlite3 db test.db; set DB [sqlite3_connection_pointer db]
353 do_test enc2-6.2 {
354 add_test_function $DB 0 1 0
355 execsql {
356 SELECT test_function('sqlite')
357 }
358 } {{UTF-16LE sqlite}}
359 db close
360 sqlite3 db test.db; set DB [sqlite3_connection_pointer db]
361 do_test enc2-6.3 {
362 add_test_function $DB 0 0 1
363 execsql {
364 SELECT test_function('sqlite')
365 }
366 } {{UTF-16BE sqlite}}
367  
368 db close
369 file delete -force test.db
370 sqlite3 db test.db; set DB [sqlite3_connection_pointer db]
371 execsql {pragma encoding = 'UTF-16LE'}
372 do_test enc2-6.3 {
373 execsql {
374 CREATE TABLE t5(a);
375 INSERT INTO t5 VALUES('sqlite');
376 }
377 } {}
378 do_test enc2-6.4 {
379 add_test_function $DB 1 1 1
380 execsql {
381 SELECT test_function('sqlite')
382 }
383 } {{UTF-16LE sqlite}}
384 db close
385 sqlite3 db test.db; set DB [sqlite3_connection_pointer db]
386 do_test enc2-6.5 {
387 add_test_function $DB 0 1 0
388 execsql {
389 SELECT test_function('sqlite')
390 }
391 } {{UTF-16LE sqlite}}
392 db close
393 sqlite3 db test.db; set DB [sqlite3_connection_pointer db]
394 do_test enc2-6.6 {
395 add_test_function $DB 0 0 1
396 execsql {
397 SELECT test_function('sqlite')
398 }
399 } {{UTF-16BE sqlite}}
400  
401 db close
402 file delete -force test.db
403 sqlite3 db test.db; set DB [sqlite3_connection_pointer db]
404 execsql {pragma encoding = 'UTF-16BE'}
405 do_test enc2-6.7 {
406 execsql {
407 CREATE TABLE t5(a);
408 INSERT INTO t5 VALUES('sqlite');
409 }
410 } {}
411 do_test enc2-6.8 {
412 add_test_function $DB 1 1 1
413 execsql {
414 SELECT test_function('sqlite')
415 }
416 } {{UTF-16BE sqlite}}
417 db close
418 sqlite3 db test.db; set DB [sqlite3_connection_pointer db]
419 do_test enc2-6.9 {
420 add_test_function $DB 0 1 0
421 execsql {
422 SELECT test_function('sqlite')
423 }
424 } {{UTF-16LE sqlite}}
425 db close
426 sqlite3 db test.db; set DB [sqlite3_connection_pointer db]
427 do_test enc2-6.10 {
428 add_test_function $DB 0 0 1
429 execsql {
430 SELECT test_function('sqlite')
431 }
432 } {{UTF-16BE sqlite}}
433  
434  
435 db close
436 file delete -force test.db
437  
438 # The following tests - enc2-7.* - function as follows:
439 #
440 # 1: Open an empty database file assuming UTF-16 encoding.
441 # 2: Open the same database with a different handle assuming UTF-8. Create
442 # a table using this handle.
443 # 3: Read the sqlite_master table from the first handle.
444 # 4: Ensure the first handle recognises the database encoding is UTF-8.
445 #
446 do_test enc2-7.1 {
447 sqlite3 db test.db
448 execsql {
449 PRAGMA encoding = 'UTF-16';
450 SELECT * FROM sqlite_master;
451 }
452 } {}
453 do_test enc2-7.2 {
454 set enc [execsql {
455 PRAGMA encoding;
456 }]
457 string range $enc 0 end-2 ;# Chop off the "le" or "be"
458 } {UTF-16}
459 do_test enc2-7.3 {
460 sqlite3 db2 test.db
461 execsql {
462 PRAGMA encoding = 'UTF-8';
463 CREATE TABLE abc(a, b, c);
464 } db2
465 } {}
466 do_test enc2-7.4 {
467 execsql {
468 SELECT * FROM sqlite_master;
469 }
470 } "table abc abc [expr $AUTOVACUUM?3:2] {CREATE TABLE abc(a, b, c)}"
471 do_test enc2-7.5 {
472 execsql {
473 PRAGMA encoding;
474 }
475 } {UTF-8}
476  
477 db close
478 db2 close
479  
480 proc utf16 {utf8} {
481 set utf16 [encoding convertto unicode $utf8]
482 append utf16 "\x00\x00"
483 return $utf16
484 }
485 ifcapable {complete} {
486 do_test enc2-8.1 {
487 sqlite3_complete16 [utf16 "SELECT * FROM t1;"]
488 } {1}
489 do_test enc2-8.2 {
490 sqlite3_complete16 [utf16 "SELECT * FROM"]
491 } {0}
492 }
493  
494 # Test that the encoding of an empty database may still be set after the
495 # (empty) schema has been initialized.
496 file delete -force test.db
497 do_test enc2-9.1 {
498 sqlite3 db test.db
499 execsql {
500 PRAGMA encoding = 'UTF-8';
501 PRAGMA encoding;
502 }
503 } {UTF-8}
504 do_test enc2-9.2 {
505 sqlite3 db test.db
506 execsql {
507 PRAGMA encoding = 'UTF-16le';
508 PRAGMA encoding;
509 }
510 } {UTF-16le}
511 do_test enc2-9.3 {
512 sqlite3 db test.db
513 execsql {
514 SELECT * FROM sqlite_master;
515 PRAGMA encoding = 'UTF-8';
516 PRAGMA encoding;
517 }
518 } {UTF-8}
519 do_test enc2-9.4 {
520 sqlite3 db test.db
521 execsql {
522 PRAGMA encoding = 'UTF-16le';
523 CREATE TABLE abc(a, b, c);
524 PRAGMA encoding;
525 }
526 } {UTF-16le}
527 do_test enc2-9.5 {
528 sqlite3 db test.db
529 execsql {
530 PRAGMA encoding = 'UTF-8';
531 PRAGMA encoding;
532 }
533 } {UTF-16le}
534  
535 # Ticket #1987.
536 # Disallow encoding changes once the encoding has been set.
537 #
538 do_test enc2-10.1 {
539 db close
540 file delete -force test.db test.db-journal
541 sqlite3 db test.db
542 db eval {
543 PRAGMA encoding=UTF16;
544 CREATE TABLE t1(a);
545 PRAGMA encoding=UTF8;
546 CREATE TABLE t2(b);
547 }
548 db close
549 sqlite3 db test.db
550 db eval {
551 SELECT name FROM sqlite_master
552 }
553 } {t1 t2}
554  
555 finish_test