# 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.13 2008/08/20 16:35:10 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

 } 0

 do_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 {

     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

 } 0

 db 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.1 {

   db collate user_defined "string compare"

   catchsql {

     SELECT * FROM collate3t1 ORDER BY a COLLATE user_defined;

   }

 } {0 {hello {}}}

 do_test collate3-4.8.2 {

   db close

   lindex [catch {

     sqlite3 db test.db

   }] 0

 } {0}

 do_test collate3-4.8.3 {

   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