| # 2002 May 24 |
| # |
| # The author disclaims copyright to this source code. In place of |
| # a legal notice, here is a blessing: |
| # |
| # May you do good and not evil. |
| # May you find forgiveness for yourself and forgive others. |
| # May you share freely, never taking more than you give. |
| # |
| #*********************************************************************** |
| # This file implements regression tests for SQLite library. The focus of |
| # this file is testing the SQLite routines used for converting between the |
| # various suported unicode encodings (UTF-8, UTF-16, UTF-16le and |
| # UTF-16be). |
| # |
| # $Id: enc2.test,v 1.29 2007/10/09 08:29:32 danielk1977 Exp $ |
| |
| set testdir [file dirname $argv0] |
| source $testdir/tester.tcl |
| |
| # If UTF16 support is disabled, ignore the tests in this file |
| # |
| ifcapable {!utf16} { |
| finish_test |
| return |
| } |
| |
| # The rough organisation of tests in this file is: |
| # |
| # enc2.1.*: Simple tests with a UTF-8 db. |
| # enc2.2.*: Simple tests with a UTF-16LE db. |
| # enc2.3.*: Simple tests with a UTF-16BE db. |
| # enc2.4.*: Test that attached databases must have the same text encoding |
| # as the main database. |
| # enc2.5.*: Test the behaviour of the library when a collation sequence is |
| # not available for the most desirable text encoding. |
| # enc2.6.*: Similar test for user functions. |
| # enc2.7.*: Test that the VerifyCookie opcode protects against assuming the |
| # wrong text encoding for the database. |
| # enc2.8.*: Test sqlite3_complete16() |
| # |
| |
| db close |
| |
| # Return the UTF-8 representation of the supplied UTF-16 string $str. |
| proc utf8 {str} { |
| # If $str ends in two 0x00 0x00 bytes, knock these off before |
| # converting to UTF-8 using TCL. |
| binary scan $str \c* vals |
| if {[lindex $vals end]==0 && [lindex $vals end-1]==0} { |
| set str [binary format \c* [lrange $vals 0 end-2]] |
| } |
| |
| set r [encoding convertfrom unicode $str] |
| return $r |
| } |
| |
| # |
| # This proc contains all the tests in this file. It is run |
| # three times. Each time the file 'test.db' contains a database |
| # with the following contents: |
| set dbcontents { |
| CREATE TABLE t1(a PRIMARY KEY, b, c); |
| INSERT INTO t1 VALUES('one', 'I', 1); |
| } |
| # This proc tests that we can open and manipulate the test.db |
| # database, and that it is possible to retreive values in |
| # various text encodings. |
| # |
| proc run_test_script {t enc} { |
| |
| # Open the database and pull out a (the) row. |
| do_test $t.1 { |
| sqlite3 db test.db; set DB [sqlite3_connection_pointer db] |
| execsql {SELECT * FROM t1} |
| } {one I 1} |
| |
| # Insert some data |
| do_test $t.2 { |
| execsql {INSERT INTO t1 VALUES('two', 'II', 2);} |
| execsql {SELECT * FROM t1} |
| } {one I 1 two II 2} |
| |
| # Insert some data |
| do_test $t.3 { |
| execsql { |
| INSERT INTO t1 VALUES('three','III',3); |
| INSERT INTO t1 VALUES('four','IV',4); |
| INSERT INTO t1 VALUES('five','V',5); |
| } |
| execsql {SELECT * FROM t1} |
| } {one I 1 two II 2 three III 3 four IV 4 five V 5} |
| |
| # Use the index |
| do_test $t.4 { |
| execsql { |
| SELECT * FROM t1 WHERE a = 'one'; |
| } |
| } {one I 1} |
| do_test $t.5 { |
| execsql { |
| SELECT * FROM t1 WHERE a = 'four'; |
| } |
| } {four IV 4} |
| ifcapable subquery { |
| do_test $t.6 { |
| execsql { |
| SELECT * FROM t1 WHERE a IN ('one', 'two'); |
| } |
| } {one I 1 two II 2} |
| } |
| |
| # Now check that we can retrieve data in both UTF-16 and UTF-8 |
| do_test $t.7 { |
| set STMT [sqlite3_prepare $DB "SELECT a FROM t1 WHERE c>3;" -1 TAIL] |
| sqlite3_step $STMT |
| sqlite3_column_text $STMT 0 |
| } {four} |
| |
| do_test $t.8 { |
| sqlite3_step $STMT |
| utf8 [sqlite3_column_text16 $STMT 0] |
| } {five} |
| |
| do_test $t.9 { |
| sqlite3_finalize $STMT |
| } SQLITE_OK |
| |
| ifcapable vacuum { |
| execsql VACUUM |
| } |
| |
| do_test $t.10 { |
| db eval {PRAGMA encoding} |
| } $enc |
| |
| } |
| |
| # The three unicode encodings understood by SQLite. |
| set encodings [list UTF-8 UTF-16le UTF-16be] |
| |
| set sqlite_os_trace 0 |
| set i 1 |
| foreach enc $encodings { |
| file delete -force test.db |
| sqlite3 db test.db |
| db eval "PRAGMA encoding = \"$enc\"" |
| execsql $dbcontents |
| do_test enc2-$i.0.1 { |
| db eval {PRAGMA encoding} |
| } $enc |
| do_test enc2-$i.0.2 { |
| db eval {PRAGMA encoding=UTF8} |
| db eval {PRAGMA encoding} |
| } $enc |
| do_test enc2-$i.0.3 { |
| db eval {PRAGMA encoding=UTF16le} |
| db eval {PRAGMA encoding} |
| } $enc |
| do_test enc2-$i.0.4 { |
| db eval {PRAGMA encoding=UTF16be} |
| db eval {PRAGMA encoding} |
| } $enc |
| |
| db close |
| run_test_script enc2-$i $enc |
| db close |
| incr i |
| } |
| |
| # Test that it is an error to try to attach a database with a different |
| # encoding to the main database. |
| ifcapable attach { |
| do_test enc2-4.1 { |
| file delete -force test.db |
| sqlite3 db test.db |
| db eval "PRAGMA encoding = 'UTF-8'" |
| db eval "CREATE TABLE abc(a, b, c);" |
| } {} |
| do_test enc2-4.2 { |
| file delete -force test2.db |
| sqlite3 db2 test2.db |
| db2 eval "PRAGMA encoding = 'UTF-16'" |
| db2 eval "CREATE TABLE abc(a, b, c);" |
| } {} |
| do_test enc2-4.3 { |
| catchsql { |
| ATTACH 'test2.db' as aux; |
| } |
| } {1 {attached databases must use the same text encoding as main database}} |
| db2 close |
| db close |
| } |
| |
| # The following tests - enc2-5.* - test that SQLite selects the correct |
| # collation sequence when more than one is available. |
| |
| set ::values [list one two three four five] |
| set ::test_collate_enc INVALID |
| proc test_collate {enc lhs rhs} { |
| set ::test_collate_enc $enc |
| set l [lsearch -exact $::values $lhs] |
| set r [lsearch -exact $::values $rhs] |
| set res [expr $l - $r] |
| # puts "enc=$enc lhs=$lhs/$l rhs=$rhs/$r res=$res" |
| return $res |
| } |
| |
| file delete -force test.db |
| sqlite3 db test.db; set DB [sqlite3_connection_pointer db] |
| do_test enc2-5.0 { |
| execsql { |
| CREATE TABLE t5(a); |
| INSERT INTO t5 VALUES('one'); |
| INSERT INTO t5 VALUES('two'); |
| INSERT INTO t5 VALUES('five'); |
| INSERT INTO t5 VALUES('three'); |
| INSERT INTO t5 VALUES('four'); |
| } |
| } {} |
| do_test enc2-5.1 { |
| add_test_collate $DB 1 1 1 |
| set res [execsql {SELECT * FROM t5 ORDER BY 1 COLLATE test_collate;}] |
| lappend res $::test_collate_enc |
| } {one two three four five UTF-8} |
| do_test enc2-5.2 { |
| add_test_collate $DB 0 1 0 |
| set res [execsql {SELECT * FROM t5 ORDER BY 1 COLLATE test_collate}] |
| lappend res $::test_collate_enc |
| } {one two three four five UTF-16LE} |
| do_test enc2-5.3 { |
| add_test_collate $DB 0 0 1 |
| set res [execsql {SELECT * FROM t5 ORDER BY 1 COLLATE test_collate}] |
| lappend res $::test_collate_enc |
| } {one two three four five UTF-16BE} |
| |
| db close |
| file delete -force test.db |
| sqlite3 db test.db; set DB [sqlite3_connection_pointer db] |
| execsql {pragma encoding = 'UTF-16LE'} |
| do_test enc2-5.4 { |
| execsql { |
| CREATE TABLE t5(a); |
| INSERT INTO t5 VALUES('one'); |
| INSERT INTO t5 VALUES('two'); |
| INSERT INTO t5 VALUES('five'); |
| INSERT INTO t5 VALUES('three'); |
| INSERT INTO t5 VALUES('four'); |
| } |
| } {} |
| do_test enc2-5.5 { |
| add_test_collate $DB 1 1 1 |
| set res [execsql {SELECT * FROM t5 ORDER BY 1 COLLATE test_collate}] |
| lappend res $::test_collate_enc |
| } {one two three four five UTF-16LE} |
| do_test enc2-5.6 { |
| add_test_collate $DB 1 0 1 |
| set res [execsql {SELECT * FROM t5 ORDER BY 1 COLLATE test_collate}] |
| lappend res $::test_collate_enc |
| } {one two three four five UTF-16BE} |
| do_test enc2-5.7 { |
| add_test_collate $DB 1 0 0 |
| set res [execsql {SELECT * FROM t5 ORDER BY 1 COLLATE test_collate}] |
| lappend res $::test_collate_enc |
| } {one two three four five UTF-8} |
| |
| db close |
| file delete -force test.db |
| sqlite3 db test.db; set DB [sqlite3_connection_pointer db] |
| execsql {pragma encoding = 'UTF-16BE'} |
| do_test enc2-5.8 { |
| execsql { |
| CREATE TABLE t5(a); |
| INSERT INTO t5 VALUES('one'); |
| INSERT INTO t5 VALUES('two'); |
| INSERT INTO t5 VALUES('five'); |
| INSERT INTO t5 VALUES('three'); |
| INSERT INTO t5 VALUES('four'); |
| } |
| } {} |
| do_test enc2-5.9 { |
| add_test_collate $DB 1 1 1 |
| set res [execsql {SELECT * FROM t5 ORDER BY 1 COLLATE test_collate}] |
| lappend res $::test_collate_enc |
| } {one two three four five UTF-16BE} |
| do_test enc2-5.10 { |
| add_test_collate $DB 1 1 0 |
| set res [execsql {SELECT * FROM t5 ORDER BY 1 COLLATE test_collate}] |
| lappend res $::test_collate_enc |
| } {one two three four five UTF-16LE} |
| do_test enc2-5.11 { |
| add_test_collate $DB 1 0 0 |
| set res [execsql {SELECT * FROM t5 ORDER BY 1 COLLATE test_collate}] |
| lappend res $::test_collate_enc |
| } {one two three four five UTF-8} |
| |
| # Also test that a UTF-16 collation factory works. |
| do_test enc2-5-12 { |
| add_test_collate $DB 0 0 0 |
| catchsql { |
| SELECT * FROM t5 ORDER BY 1 COLLATE test_collate |
| } |
| } {1 {no such collation sequence: test_collate}} |
| do_test enc2-5.13 { |
| add_test_collate_needed $DB |
| set res [execsql {SELECT * FROM t5 ORDER BY 1 COLLATE test_collate; }] |
| lappend res $::test_collate_enc |
| } {one two three four five UTF-16BE} |
| do_test enc2-5.14 { |
| set ::sqlite_last_needed_collation |
| } test_collate |
| |
| db close |
| file delete -force test.db |
| |
| do_test enc2-5.15 { |
| sqlite3 db test.db; set ::DB [sqlite3_connection_pointer db] |
| add_test_collate_needed $::DB |
| set ::sqlite_last_needed_collation |
| } {} |
| do_test enc2-5.16 { |
| execsql {CREATE TABLE t1(a varchar collate test_collate);} |
| } {} |
| do_test enc2-5.17 { |
| set ::sqlite_last_needed_collation |
| } {test_collate} |
| |
| # The following tests - enc2-6.* - test that SQLite selects the correct |
| # user function when more than one is available. |
| |
| proc test_function {enc arg} { |
| return "$enc $arg" |
| } |
| |
| db close |
| file delete -force test.db |
| sqlite3 db test.db; set DB [sqlite3_connection_pointer db] |
| execsql {pragma encoding = 'UTF-8'} |
| do_test enc2-6.0 { |
| execsql { |
| CREATE TABLE t5(a); |
| INSERT INTO t5 VALUES('one'); |
| } |
| } {} |
| do_test enc2-6.1 { |
| add_test_function $DB 1 1 1 |
| execsql { |
| SELECT test_function('sqlite') |
| } |
| } {{UTF-8 sqlite}} |
| db close |
| sqlite3 db test.db; set DB [sqlite3_connection_pointer db] |
| do_test enc2-6.2 { |
| add_test_function $DB 0 1 0 |
| execsql { |
| SELECT test_function('sqlite') |
| } |
| } {{UTF-16LE sqlite}} |
| db close |
| sqlite3 db test.db; set DB [sqlite3_connection_pointer db] |
| do_test enc2-6.3 { |
| add_test_function $DB 0 0 1 |
| execsql { |
| SELECT test_function('sqlite') |
| } |
| } {{UTF-16BE sqlite}} |
| |
| db close |
| file delete -force test.db |
| sqlite3 db test.db; set DB [sqlite3_connection_pointer db] |
| execsql {pragma encoding = 'UTF-16LE'} |
| do_test enc2-6.3 { |
| execsql { |
| CREATE TABLE t5(a); |
| INSERT INTO t5 VALUES('sqlite'); |
| } |
| } {} |
| do_test enc2-6.4 { |
| add_test_function $DB 1 1 1 |
| execsql { |
| SELECT test_function('sqlite') |
| } |
| } {{UTF-16LE sqlite}} |
| db close |
| sqlite3 db test.db; set DB [sqlite3_connection_pointer db] |
| do_test enc2-6.5 { |
| add_test_function $DB 0 1 0 |
| execsql { |
| SELECT test_function('sqlite') |
| } |
| } {{UTF-16LE sqlite}} |
| db close |
| sqlite3 db test.db; set DB [sqlite3_connection_pointer db] |
| do_test enc2-6.6 { |
| add_test_function $DB 0 0 1 |
| execsql { |
| SELECT test_function('sqlite') |
| } |
| } {{UTF-16BE sqlite}} |
| |
| db close |
| file delete -force test.db |
| sqlite3 db test.db; set DB [sqlite3_connection_pointer db] |
| execsql {pragma encoding = 'UTF-16BE'} |
| do_test enc2-6.7 { |
| execsql { |
| CREATE TABLE t5(a); |
| INSERT INTO t5 VALUES('sqlite'); |
| } |
| } {} |
| do_test enc2-6.8 { |
| add_test_function $DB 1 1 1 |
| execsql { |
| SELECT test_function('sqlite') |
| } |
| } {{UTF-16BE sqlite}} |
| db close |
| sqlite3 db test.db; set DB [sqlite3_connection_pointer db] |
| do_test enc2-6.9 { |
| add_test_function $DB 0 1 0 |
| execsql { |
| SELECT test_function('sqlite') |
| } |
| } {{UTF-16LE sqlite}} |
| db close |
| sqlite3 db test.db; set DB [sqlite3_connection_pointer db] |
| do_test enc2-6.10 { |
| add_test_function $DB 0 0 1 |
| execsql { |
| SELECT test_function('sqlite') |
| } |
| } {{UTF-16BE sqlite}} |
| |
| |
| db close |
| file delete -force test.db |
| |
| # The following tests - enc2-7.* - function as follows: |
| # |
| # 1: Open an empty database file assuming UTF-16 encoding. |
| # 2: Open the same database with a different handle assuming UTF-8. Create |
| # a table using this handle. |
| # 3: Read the sqlite_master table from the first handle. |
| # 4: Ensure the first handle recognises the database encoding is UTF-8. |
| # |
| do_test enc2-7.1 { |
| sqlite3 db test.db |
| execsql { |
| PRAGMA encoding = 'UTF-16'; |
| SELECT * FROM sqlite_master; |
| } |
| } {} |
| do_test enc2-7.2 { |
| set enc [execsql { |
| PRAGMA encoding; |
| }] |
| string range $enc 0 end-2 ;# Chop off the "le" or "be" |
| } {UTF-16} |
| do_test enc2-7.3 { |
| sqlite3 db2 test.db |
| execsql { |
| PRAGMA encoding = 'UTF-8'; |
| CREATE TABLE abc(a, b, c); |
| } db2 |
| } {} |
| do_test enc2-7.4 { |
| execsql { |
| SELECT * FROM sqlite_master; |
| } |
| } "table abc abc [expr $AUTOVACUUM?3:2] {CREATE TABLE abc(a, b, c)}" |
| do_test enc2-7.5 { |
| execsql { |
| PRAGMA encoding; |
| } |
| } {UTF-8} |
| |
| db close |
| db2 close |
| |
| proc utf16 {utf8} { |
| set utf16 [encoding convertto unicode $utf8] |
| append utf16 "\x00\x00" |
| return $utf16 |
| } |
| ifcapable {complete} { |
| do_test enc2-8.1 { |
| sqlite3_complete16 [utf16 "SELECT * FROM t1;"] |
| } {1} |
| do_test enc2-8.2 { |
| sqlite3_complete16 [utf16 "SELECT * FROM"] |
| } {0} |
| } |
| |
| # Test that the encoding of an empty database may still be set after the |
| # (empty) schema has been initialized. |
| file delete -force test.db |
| do_test enc2-9.1 { |
| sqlite3 db test.db |
| execsql { |
| PRAGMA encoding = 'UTF-8'; |
| PRAGMA encoding; |
| } |
| } {UTF-8} |
| do_test enc2-9.2 { |
| sqlite3 db test.db |
| execsql { |
| PRAGMA encoding = 'UTF-16le'; |
| PRAGMA encoding; |
| } |
| } {UTF-16le} |
| do_test enc2-9.3 { |
| sqlite3 db test.db |
| execsql { |
| SELECT * FROM sqlite_master; |
| PRAGMA encoding = 'UTF-8'; |
| PRAGMA encoding; |
| } |
| } {UTF-8} |
| do_test enc2-9.4 { |
| sqlite3 db test.db |
| execsql { |
| PRAGMA encoding = 'UTF-16le'; |
| CREATE TABLE abc(a, b, c); |
| PRAGMA encoding; |
| } |
| } {UTF-16le} |
| do_test enc2-9.5 { |
| sqlite3 db test.db |
| execsql { |
| PRAGMA encoding = 'UTF-8'; |
| PRAGMA encoding; |
| } |
| } {UTF-16le} |
| |
| # Ticket #1987. |
| # Disallow encoding changes once the encoding has been set. |
| # |
| do_test enc2-10.1 { |
| db close |
| file delete -force test.db test.db-journal |
| sqlite3 db test.db |
| db eval { |
| PRAGMA encoding=UTF16; |
| CREATE TABLE t1(a); |
| PRAGMA encoding=UTF8; |
| CREATE TABLE t2(b); |
| } |
| db close |
| sqlite3 db test.db |
| db eval { |
| SELECT name FROM sqlite_master |
| } |
| } {t1 t2} |
| |
| finish_test |