## 2001 September 15## 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 script is page cache subsystem.## $Id: collate1.test,v 1.4 2005/11/01 15:48:25 drh Exp $set testdir [file dirname $argv0]source $testdir/tester.tcl## Tests are roughly organised as follows:## collate1-1.* - Single-field ORDER BY with an explicit COLLATE clause.# collate1-2.* - Multi-field ORDER BY with an explicit COLLATE clause.# collate1-3.* - ORDER BY using a default collation type. Also that an #                explict collate type overrides a default collate type.# collate1-4.* - ORDER BY using a data type.### Collation type 'HEX'. If an argument can be interpreted as a hexadecimal# number, then it is converted to one before the comparison is performed. # Numbers are less than other strings. If neither argument is a number, # [string compare] is used.#db collate HEX hex_collateproc hex_collate {lhs rhs} {  set lhs_ishex [regexp {^(0x|)[1234567890abcdefABCDEF]+$} $lhs]  set rhs_ishex [regexp {^(0x|)[1234567890abcdefABCDEF]+$} $rhs]  if {$lhs_ishex && $rhs_ishex} {     set lhsx [scan $lhs %x]    set rhsx [scan $rhs %x]    if {$lhs < $rhs} {return -1}    if {$lhs == $rhs} {return 0}    if {$lhs > $rhs} {return 1}  }  if {$lhs_ishex} {    return -1;  }  if {$rhs_ishex} {    return 1;  }  return [string compare $lhs $rhs]}db function hex {format 0x%X}# Mimic the SQLite 2 collation type NUMERIC.db collate numeric numeric_collateproc numeric_collate {lhs rhs} {  if {$lhs == $rhs} {return 0}   return [expr ($lhs>$rhs)?1:-1]}do_test collate1-1.0 {  execsql {    CREATE TABLE collate1t1(c1, c2);    INSERT INTO collate1t1 VALUES(45, hex(45));    INSERT INTO collate1t1 VALUES(NULL, NULL);    INSERT INTO collate1t1 VALUES(281, hex(281));  }} {}do_test collate1-1.1 {  execsql {    SELECT c2 FROM collate1t1 ORDER BY 1;  }} {{} 0x119 0x2D}do_test collate1-1.2 {  execsql {    SELECT c2 FROM collate1t1 ORDER BY 1 COLLATE hex;  }} {{} 0x2D 0x119}do_test collate1-1.3 {  execsql {    SELECT c2 FROM collate1t1 ORDER BY 1 COLLATE hex DESC;  }} {0x119 0x2D {}}do_test collate1-1.4 {  execsql {   SELECT c2 FROM collate1t1 ORDER BY 1 COLLATE hex ASC;  }} {{} 0x2D 0x119}do_test collate1-1.5 {  execsql {    DROP TABLE collate1t1;  }} {}do_test collate1-2.0 {  execsql {    CREATE TABLE collate1t1(c1, c2);    INSERT INTO collate1t1 VALUES('5', '0x11');    INSERT INTO collate1t1 VALUES('5', '0xA');    INSERT INTO collate1t1 VALUES(NULL, NULL);    INSERT INTO collate1t1 VALUES('7', '0xA');    INSERT INTO collate1t1 VALUES('11', '0x11');    INSERT INTO collate1t1 VALUES('11', '0x101');  }} {}do_test collate1-2.2 {  execsql {    SELECT c1, c2 FROM collate1t1 ORDER BY 1 COLLATE numeric, 2 COLLATE hex;  }} {{} {} 5 0xA 5 0x11 7 0xA 11 0x11 11 0x101}do_test collate1-2.3 {  execsql {    SELECT c1, c2 FROM collate1t1 ORDER BY 1 COLLATE binary, 2 COLLATE hex;  }} {{} {} 11 0x11 11 0x101 5 0xA 5 0x11 7 0xA}do_test collate1-2.4 {  execsql {    SELECT c1, c2 FROM collate1t1 ORDER BY 1 COLLATE binary DESC, 2 COLLATE hex;  }} {7 0xA 5 0xA 5 0x11 11 0x11 11 0x101 {} {}}do_test collate1-2.5 {  execsql {    SELECT c1, c2 FROM collate1t1         ORDER BY 1 COLLATE binary DESC, 2 COLLATE hex DESC;  }} {7 0xA 5 0x11 5 0xA 11 0x101 11 0x11 {} {}}do_test collate1-2.6 {  execsql {    SELECT c1, c2 FROM collate1t1         ORDER BY 1 COLLATE binary ASC, 2 COLLATE hex ASC;  }} {{} {} 11 0x11 11 0x101 5 0xA 5 0x11 7 0xA}do_test collate1-2.7 {  execsql {    DROP TABLE collate1t1;  }} {}## These tests ensure that the default collation type for a column is used # by an ORDER BY clause correctly. The focus is all the different ways# the column can be referenced. i.e. a, collate2t1.a, main.collate2t1.a etc.#do_test collate1-3.0 {  execsql {    CREATE TABLE collate1t1(a COLLATE hex, b);    INSERT INTO collate1t1 VALUES( '0x5', 5 );    INSERT INTO collate1t1 VALUES( '1', 1 );    INSERT INTO collate1t1 VALUES( '0x45', 69 );    INSERT INTO collate1t1 VALUES( NULL, NULL );    SELECT * FROM collate1t1 ORDER BY a;  }} {{} {} 1 1 0x5 5 0x45 69}do_test collate1-3.1 {  execsql {    SELECT * FROM collate1t1 ORDER BY 1;  }} {{} {} 1 1 0x5 5 0x45 69}do_test collate1-3.2 {  execsql {    SELECT * FROM collate1t1 ORDER BY collate1t1.a;  }} {{} {} 1 1 0x5 5 0x45 69}do_test collate1-3.3 {  execsql {    SELECT * FROM collate1t1 ORDER BY main.collate1t1.a;  }} {{} {} 1 1 0x5 5 0x45 69}do_test collate1-3.4 {  execsql {    SELECT a as c1, b as c2 FROM collate1t1 ORDER BY c1;  }} {{} {} 1 1 0x5 5 0x45 69}do_test collate1-3.5 {  execsql {    SELECT a as c1, b as c2 FROM collate1t1 ORDER BY c1 COLLATE binary;  }} {{} {} 0x45 69 0x5 5 1 1}do_test collate1-3.6 {  execsql {    DROP TABLE collate1t1;  }} {}# Update for SQLite version 3. The collate1-4.* test cases were written# before manifest types were introduced. The following test cases still# work, due to the 'affinity' mechanism, but they don't prove anything# about collation sequences.#do_test collate1-4.0 {  execsql {    CREATE TABLE collate1t1(c1 numeric, c2 text);    INSERT INTO collate1t1 VALUES(1, 1);    INSERT INTO collate1t1 VALUES(12, 12);    INSERT INTO collate1t1 VALUES(NULL, NULL);    INSERT INTO collate1t1 VALUES(101, 101);  }} {}do_test collate1-4.1 {  execsql {    SELECT c1 FROM collate1t1 ORDER BY 1;  }} {{} 1 12 101}do_test collate1-4.2 {  execsql {    SELECT c2 FROM collate1t1 ORDER BY 1;  }} {{} 1 101 12}do_test collate1-4.3 {  execsql {    SELECT c2+0 FROM collate1t1 ORDER BY 1;  }} {{} 1 12 101}do_test collate1-4.4 {  execsql {    SELECT c1||'' FROM collate1t1 ORDER BY 1;  }} {{} 1 101 12}do_test collate1-4.5 {  execsql {    DROP TABLE collate1t1;  }} {}finish_test