# 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