# 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: collate3.test,v 1.11 2005/09/08 01:58:43 drh Exp $set testdir [file dirname $argv0]source $testdir/tester.tcl## Tests are organised as follows:## collate3.1.* - Errors related to unknown collation sequences.# collate3.2.* - Errors related to undefined collation sequences.# collate3.3.* - Writing to a table that has an index with an undefined c.s.# collate3.4.* - Misc errors.# collate3.5.* - Collation factory.### These tests ensure that when a user executes a statement with an # unknown collation sequence an error is returned.#do_test collate3-1.0 {  execsql {    CREATE TABLE collate3t1(c1);  }} {}do_test collate3-1.1 {  catchsql {    SELECT * FROM collate3t1 ORDER BY 1 collate garbage;  }} {1 {no such collation sequence: garbage}}do_test collate3-1.2 {  catchsql {    CREATE TABLE collate3t2(c1 collate garbage);  }} {1 {no such collation sequence: garbage}}do_test collate3-1.3 {  catchsql {    CREATE INDEX collate3i1 ON collate3t1(c1 COLLATE garbage);  }} {1 {no such collation sequence: garbage}}execsql {  DROP TABLE collate3t1;}## Create a table with a default collation sequence, then close# and re-open the database without re-registering the collation# sequence. Then make sure the library stops us from using# the collation sequence in:# * an explicitly collated ORDER BY# * an ORDER BY that uses the default collation sequence# * an expression (=)# * a CREATE TABLE statement# * a CREATE INDEX statement that uses a default collation sequence# * a GROUP BY that uses the default collation sequence# * a SELECT DISTINCT that uses the default collation sequence# * Compound SELECTs that uses the default collation sequence# * An ORDER BY on a compound SELECT with an explicit ORDER BY.#do_test collate3-2.0 {  db collate string_compare {string compare}  execsql {    CREATE TABLE collate3t1(c1 COLLATE string_compare, c2);  }  db close  sqlite3 db test.db  expr 0} 0do_test collate3-2.1 {  catchsql {    SELECT * FROM collate3t1 ORDER BY 1 COLLATE string_compare;  }} {1 {no such collation sequence: string_compare}} do_test collate3-2.2 {  catchsql {    SELECT * FROM collate3t1 ORDER BY c1;  }} {1 {no such collation sequence: string_compare}} do_test collate3-2.3 {  catchsql {    SELECT * FROM collate3t1 WHERE c1 = 'xxx';  }} {1 {no such collation sequence: string_compare}} do_test collate3-2.4 {  catchsql {    CREATE TABLE collate3t2(c1 COLLATE string_compare);  }} {1 {no such collation sequence: string_compare}} do_test collate3-2.5 {  catchsql {    CREATE INDEX collate3t1_i1 ON collate3t1(c1);  }} {1 {no such collation sequence: string_compare}}do_test collate3-2.6 {  catchsql {    SELECT * FROM collate3t1;  }} {0 {}}do_test collate3-2.7.1 {  catchsql {    SELECT count(*) FROM collate3t1 GROUP BY c1;  }} {1 {no such collation sequence: string_compare}} # do_test collate3-2.7.2 {#   catchsql {#     SELECT * FROM collate3t1 GROUP BY c1;#   }# } {1 {GROUP BY may only be used on aggregate queries}}do_test collate3-2.7.2 {  catchsql {    SELECT * FROM collate3t1 GROUP BY c1;  }} {1 {no such collation sequence: string_compare}} do_test collate3-2.8 {  catchsql {    SELECT DISTINCT c1 FROM collate3t1;  }} {1 {no such collation sequence: string_compare}} ifcapable compound {  do_test collate3-2.9 {    catchsql {      SELECT c1 FROM collate3t1 UNION SELECT c1 FROM collate3t1;    }  } {1 {no such collation sequence: string_compare}}   do_test collate3-2.10 {    catchsql {      SELECT c1 FROM collate3t1 EXCEPT SELECT c1 FROM collate3t1;    }  } {1 {no such collation sequence: string_compare}}   do_test collate3-2.11 {    catchsql {      SELECT c1 FROM collate3t1 INTERSECT SELECT c1 FROM collate3t1;    }  } {1 {no such collation sequence: string_compare}}   do_test collate3-2.12 {    catchsql {      SELECT c1 FROM collate3t1 UNION ALL SELECT c1 FROM collate3t1;    }  } {0 {}}  do_test collate3-2.13 {btree_breakpoint    catchsql {      SELECT 10 UNION ALL SELECT 20 ORDER BY 1 COLLATE string_compare;    }  } {1 {no such collation sequence: string_compare}}   do_test collate3-2.14 {    catchsql {      SELECT 10 INTERSECT SELECT 20 ORDER BY 1 COLLATE string_compare;    }  } {1 {no such collation sequence: string_compare}}   do_test collate3-2.15 {    catchsql {      SELECT 10 EXCEPT SELECT 20 ORDER BY 1 COLLATE string_compare;    }  } {1 {no such collation sequence: string_compare}}   do_test collate3-2.16 {    catchsql {      SELECT 10 UNION SELECT 20 ORDER BY 1 COLLATE string_compare;    }  } {1 {no such collation sequence: string_compare}}   do_test collate3-2.17 {    catchsql {      SELECT c1 FROM collate3t1 UNION ALL SELECT c1 FROM collate3t1 ORDER BY 1;    }  } {1 {no such collation sequence: string_compare}} } ;# ifcapable compound## Create an index that uses a collation sequence then close and# re-open the database without re-registering the collation# sequence. Then check that for the table with the index # * An INSERT fails,# * An UPDATE on the column with the index fails,# * An UPDATE on a different column succeeds.# * A DELETE with a WHERE clause fails# * A DELETE without a WHERE clause succeeds## Also, ensure that the restrictions tested by collate3-2.* still# apply after the index has been created.#do_test collate3-3.0 {  db collate string_compare {string compare}  execsql {    CREATE INDEX collate3t1_i1 ON collate3t1(c1);    INSERT INTO collate3t1 VALUES('xxx', 'yyy');  }  db close  sqlite3 db test.db  expr 0} 0db eval {select * from collate3t1}do_test collate3-3.1 {  catchsql {    INSERT INTO collate3t1 VALUES('xxx', 0);  }} {1 {no such collation sequence: string_compare}} do_test collate3-3.2 {  catchsql {    UPDATE collate3t1 SET c1 = 'xxx';  }} {1 {no such collation sequence: string_compare}} do_test collate3-3.3 {  catchsql {    UPDATE collate3t1 SET c2 = 'xxx';  }} {0 {}}do_test collate3-3.4 {  catchsql {    DELETE FROM collate3t1 WHERE 1;  }} {1 {no such collation sequence: string_compare}} do_test collate3-3.5 {  catchsql {    SELECT * FROM collate3t1;  }} {0 {xxx xxx}}do_test collate3-3.6 {  catchsql {    DELETE FROM collate3t1;  }} {0 {}}ifcapable {integrityck} {  do_test collate3-3.8 {    catchsql {      PRAGMA integrity_check    }  } {1 {no such collation sequence: string_compare}}}do_test collate3-3.9 {  catchsql {    SELECT * FROM collate3t1;  }} {0 {}}do_test collate3-3.10 {  catchsql {    SELECT * FROM collate3t1 ORDER BY 1 COLLATE string_compare;  }} {1 {no such collation sequence: string_compare}} do_test collate3-3.11 {  catchsql {    SELECT * FROM collate3t1 ORDER BY c1;  }} {1 {no such collation sequence: string_compare}} do_test collate3-3.12 {  catchsql {    SELECT * FROM collate3t1 WHERE c1 = 'xxx';  }} {1 {no such collation sequence: string_compare}} do_test collate3-3.13 {  catchsql {    CREATE TABLE collate3t2(c1 COLLATE string_compare);  }} {1 {no such collation sequence: string_compare}} do_test collate3-3.14 {  catchsql {    CREATE INDEX collate3t1_i2 ON collate3t1(c1);  }} {1 {no such collation sequence: string_compare}} do_test collate3-3.15 {  execsql {    DROP TABLE collate3t1;  }} {}# Check we can create an index that uses an explicit collation # sequence and then close and re-open the database.do_test collate3-4.6 {  db collate user_defined "string compare"  execsql {    CREATE TABLE collate3t1(a, b);    INSERT INTO collate3t1 VALUES('hello', NULL);    CREATE INDEX collate3i1 ON collate3t1(a COLLATE user_defined);  }} {}do_test collate3-4.7 {  db close  sqlite3 db test.db  catchsql {    SELECT * FROM collate3t1 ORDER BY a COLLATE user_defined;  }} {1 {no such collation sequence: user_defined}}do_test collate3-4.8 {  db collate user_defined "string compare"  catchsql {    SELECT * FROM collate3t1 ORDER BY a COLLATE user_defined;  }} {0 {hello {}}}do_test collate3-4.8 {  db close  lindex [catch {    sqlite3 db test.db  }] 0} {0}do_test collate3-4.8 {  execsql {    DROP TABLE collate3t1;  }} {}# Compare strings as numbers.proc numeric_compare {lhs rhs} {  if {$rhs > $lhs} {    set res -1  } else {    set res [expr ($lhs > $rhs)?1:0]  }  return $res}# Check we can create a view that uses an explicit collation # sequence and then close and re-open the database.ifcapable view {do_test collate3-4.9 {  db collate user_defined numeric_compare  execsql {    CREATE TABLE collate3t1(a, b);    INSERT INTO collate3t1 VALUES('2', NULL);    INSERT INTO collate3t1 VALUES('101', NULL);    INSERT INTO collate3t1 VALUES('12', NULL);    CREATE VIEW collate3v1 AS SELECT * FROM collate3t1         ORDER BY 1 COLLATE user_defined;    SELECT * FROM collate3v1;  }} {2 {} 12 {} 101 {}}do_test collate3-4.10 {  db close  sqlite3 db test.db  catchsql {    SELECT * FROM collate3v1;  }} {1 {no such collation sequence: user_defined}}do_test collate3-4.11 {  db collate user_defined numeric_compare  catchsql {    SELECT * FROM collate3v1;  }} {0 {2 {} 12 {} 101 {}}}do_test collate3-4.12 {  execsql {    DROP TABLE collate3t1;  }} {}} ;# ifcapable view## Test the collation factory. In the code, the "no such collation sequence"# message is only generated in two places. So these tests just test that# the collation factory can be called once from each of those points.#do_test collate3-5.0 {  catchsql {    CREATE TABLE collate3t1(a);    INSERT INTO collate3t1 VALUES(10);    SELECT a FROM collate3t1 ORDER BY 1 COLLATE unk;  }} {1 {no such collation sequence: unk}}do_test collate3-5.1 {  set ::cfact_cnt 0  proc cfact {nm} {    db collate $nm {string compare}    incr ::cfact_cnt  }  db collation_needed cfact} {}do_test collate3-5.2 {  catchsql {    SELECT a FROM collate3t1 ORDER BY 1 COLLATE unk;  }} {0 10}do_test collate3-5.3 {  set ::cfact_cnt} {1}do_test collate3-5.4 {  catchsql {    SELECT a FROM collate3t1 ORDER BY 1 COLLATE unk;  }} {0 10}do_test collate3-5.5 {  set ::cfact_cnt} {1}do_test collate3-5.6 {  catchsql {    SELECT a FROM collate3t1 ORDER BY 1 COLLATE unk;  }} {0 10}do_test collate3-5.7 {  execsql {    DROP TABLE collate3t1;    CREATE TABLE collate3t1(a COLLATE unk);  }  db close  sqlite3 db test.db  catchsql {    SELECT a FROM collate3t1 ORDER BY 1;  }} {1 {no such collation sequence: unk}}do_test collate3-5.8 {  set ::cfact_cnt 0  proc cfact {nm} {    db collate $nm {string compare}    incr ::cfact_cnt  }  db collation_needed cfact  catchsql {    SELECT a FROM collate3t1 ORDER BY 1;  }} {0 {}}do_test collate3-5.9 {  execsql {    DROP TABLE collate3t1;  }} {}finish_test