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