3 # The author disclaims copyright to this source code. In place of
4 # a legal notice, here is a blessing:
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.
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
16 # $Id: enc2.test,v 1.22 2005/02/26 17:31:28 drh Exp $
18 set testdir [file dirname $argv0]
19 source $testdir/tester.tcl
21 # If UTF16 support is disabled, ignore the tests in this file
28 # The rough organisation of tests in this file is:
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()
45 # Return the UTF-8 representation of the supplied UTF-16 string $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]]
54 set r [encoding convertfrom unicode $str]
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:
63 CREATE TABLE t1(a PRIMARY KEY, b, c);
64 INSERT INTO t1 VALUES('one', 'I', 1);
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.
70 proc run_test_script {t enc} {
72 # Open the database and pull out a (the) row.
74 set DB [sqlite3 db test.db]
75 execsql {SELECT * FROM t1}
80 execsql {INSERT INTO t1 VALUES('two', 'II', 2);}
81 execsql {SELECT * FROM t1}
87 INSERT INTO t1 VALUES('three','III',3);
88 INSERT INTO t1 VALUES('four','IV',4);
89 INSERT INTO t1 VALUES('five','V',5);
91 execsql {SELECT * FROM t1}
92 } {one I 1 two II 2 three III 3 four IV 4 five V 5}
97 SELECT * FROM t1 WHERE a = 'one';
102 SELECT * FROM t1 WHERE a = 'four';
108 SELECT * FROM t1 WHERE a IN ('one', 'two');
113 # Now check that we can retrieve data in both UTF-16 and UTF-8
115 set STMT [sqlite3_prepare $DB "SELECT a FROM t1 WHERE c>3;" -1 TAIL]
117 sqlite3_column_text $STMT 0
122 utf8 [sqlite3_column_text16 $STMT 0]
126 sqlite3_finalize $STMT
134 db eval {PRAGMA encoding}
139 # The three unicode encodings understood by SQLite.
140 set encodings [list UTF-8 UTF-16le UTF-16be]
142 set sqlite_os_trace 0
144 foreach enc $encodings {
145 file delete -force test.db
147 db eval "PRAGMA encoding = \"$enc\""
150 run_test_script enc2-$i $enc
155 # Test that it is an error to try to attach a database with a different
156 # encoding to the main database.
158 file delete -force test.db
160 db eval "PRAGMA encoding = 'UTF-8'"
161 db eval "CREATE TABLE abc(a, b, c);"
164 file delete -force test2.db
166 db2 eval "PRAGMA encoding = 'UTF-16'"
167 db2 eval "CREATE TABLE abc(a, b, c);"
171 ATTACH 'test2.db' as aux;
173 } {1 {attached databases must use the same text encoding as main database}}
178 # The following tests - enc2-5.* - test that SQLite selects the correct
179 # collation sequence when more than one is available.
181 set ::values [list one two three four five]
182 set ::test_collate_enc INVALID
183 proc test_collate {enc lhs rhs} {
184 set ::test_collate_enc $enc
185 set l [lsearch -exact $::values $lhs]
186 set r [lsearch -exact $::values $rhs]
187 set res [expr $l - $r]
191 file delete -force test.db
192 set DB [sqlite3 db test.db]
196 INSERT INTO t5 VALUES('one');
197 INSERT INTO t5 VALUES('two');
198 INSERT INTO t5 VALUES('five');
199 INSERT INTO t5 VALUES('three');
200 INSERT INTO t5 VALUES('four');
204 add_test_collate $DB 1 1 1
205 set res [execsql {SELECT * FROM t5 ORDER BY 1 COLLATE test_collate}]
206 lappend res $::test_collate_enc
207 } {one two three four five UTF-8}
209 add_test_collate $DB 0 1 0
210 set res [execsql {SELECT * FROM t5 ORDER BY 1 COLLATE test_collate}]
211 lappend res $::test_collate_enc
212 } {one two three four five UTF-16LE}
214 add_test_collate $DB 0 0 1
215 set res [execsql {SELECT * FROM t5 ORDER BY 1 COLLATE test_collate}]
216 lappend res $::test_collate_enc
217 } {one two three four five UTF-16BE}
220 file delete -force test.db
221 set DB [sqlite3 db test.db]
222 execsql {pragma encoding = 'UTF-16LE'}
226 INSERT INTO t5 VALUES('one');
227 INSERT INTO t5 VALUES('two');
228 INSERT INTO t5 VALUES('five');
229 INSERT INTO t5 VALUES('three');
230 INSERT INTO t5 VALUES('four');
234 add_test_collate $DB 1 1 1
235 set res [execsql {SELECT * FROM t5 ORDER BY 1 COLLATE test_collate}]
236 lappend res $::test_collate_enc
237 } {one two three four five UTF-16LE}
239 add_test_collate $DB 1 0 1
240 set res [execsql {SELECT * FROM t5 ORDER BY 1 COLLATE test_collate}]
241 lappend res $::test_collate_enc
242 } {one two three four five UTF-16BE}
244 add_test_collate $DB 1 0 0
245 set res [execsql {SELECT * FROM t5 ORDER BY 1 COLLATE test_collate}]
246 lappend res $::test_collate_enc
247 } {one two three four five UTF-8}
250 file delete -force test.db
251 set DB [sqlite3 db test.db]
252 execsql {pragma encoding = 'UTF-16BE'}
256 INSERT INTO t5 VALUES('one');
257 INSERT INTO t5 VALUES('two');
258 INSERT INTO t5 VALUES('five');
259 INSERT INTO t5 VALUES('three');
260 INSERT INTO t5 VALUES('four');
264 add_test_collate $DB 1 1 1
265 set res [execsql {SELECT * FROM t5 ORDER BY 1 COLLATE test_collate}]
266 lappend res $::test_collate_enc
267 } {one two three four five UTF-16BE}
269 add_test_collate $DB 1 1 0
270 set res [execsql {SELECT * FROM t5 ORDER BY 1 COLLATE test_collate}]
271 lappend res $::test_collate_enc
272 } {one two three four five UTF-16LE}
274 add_test_collate $DB 1 0 0
275 set res [execsql {SELECT * FROM t5 ORDER BY 1 COLLATE test_collate}]
276 lappend res $::test_collate_enc
277 } {one two three four five UTF-8}
279 # Also test that a UTF-16 collation factory works.
281 add_test_collate $DB 0 0 0
283 SELECT * FROM t5 ORDER BY 1 COLLATE test_collate
285 } {1 {no such collation sequence: test_collate}}
287 add_test_collate_needed $DB
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-16BE}
293 file delete -force test.db
295 # The following tests - enc2-6.* - test that SQLite selects the correct
296 # user function when more than one is available.
298 proc test_function {enc arg} {
302 file delete -force test.db
303 set DB [sqlite3 db test.db]
304 execsql {pragma encoding = 'UTF-8'}
308 INSERT INTO t5 VALUES('one');
312 add_test_function $DB 1 1 1
314 SELECT test_function('sqlite')
318 set DB [sqlite3 db test.db]
320 add_test_function $DB 0 1 0
322 SELECT test_function('sqlite')
324 } {{UTF-16LE sqlite}}
326 set DB [sqlite3 db test.db]
328 add_test_function $DB 0 0 1
330 SELECT test_function('sqlite')
332 } {{UTF-16BE sqlite}}
335 file delete -force test.db
336 set DB [sqlite3 db test.db]
337 execsql {pragma encoding = 'UTF-16LE'}
341 INSERT INTO t5 VALUES('sqlite');
345 add_test_function $DB 1 1 1
347 SELECT test_function('sqlite')
349 } {{UTF-16LE sqlite}}
351 set DB [sqlite3 db test.db]
353 add_test_function $DB 0 1 0
355 SELECT test_function('sqlite')
357 } {{UTF-16LE sqlite}}
359 set DB [sqlite3 db test.db]
361 add_test_function $DB 0 0 1
363 SELECT test_function('sqlite')
365 } {{UTF-16BE sqlite}}
368 file delete -force test.db
369 set DB [sqlite3 db test.db]
370 execsql {pragma encoding = 'UTF-16BE'}
374 INSERT INTO t5 VALUES('sqlite');
378 add_test_function $DB 1 1 1
380 SELECT test_function('sqlite')
382 } {{UTF-16BE sqlite}}
384 set DB [sqlite3 db test.db]
386 add_test_function $DB 0 1 0
388 SELECT test_function('sqlite')
390 } {{UTF-16LE sqlite}}
392 set DB [sqlite3 db test.db]
394 add_test_function $DB 0 0 1
396 SELECT test_function('sqlite')
398 } {{UTF-16BE sqlite}}
402 file delete -force test.db
404 # The following tests - enc2-7.* - function as follows:
406 # 1: Open an empty database file assuming UTF-16 encoding.
407 # 2: Open the same database with a different handle assuming UTF-8. Create
408 # a table using this handle.
409 # 3: Read the sqlite_master table from the first handle.
410 # 4: Ensure the first handle recognises the database encoding is UTF-8.
415 PRAGMA encoding = 'UTF-16';
416 SELECT * FROM sqlite_master;
423 string range $enc 0 end-2 ;# Chop off the "le" or "be"
428 PRAGMA encoding = 'UTF-8';
429 CREATE TABLE abc(a, b, c);
434 SELECT * FROM sqlite_master;
436 } "table abc abc [expr $AUTOVACUUM?3:2] {CREATE TABLE abc(a, b, c)}"
447 set utf16 [encoding convertto unicode $utf8]
448 append utf16 "\x00\x00"
451 ifcapable {complete} {
453 sqlite3_complete16 [utf16 "SELECT * FROM t1;"]
456 sqlite3_complete16 [utf16 "SELECT * FROM"]