Rapid Database Application Development
"Microsoft Access for Linux"
Database Collation Issues
started by: jstaniek status of ideas mentioned here: planed for 1.1
Collation type is a rule used when comparing (or sorting) text strings in a database. Lets assume we have three database records in a name column of a table:
Joe Adam joan
Joe Adam joan
Affected SQL Operators
Following SQL comparison operators are affected:
< > <= >= <> != = == LIKE.^ toc
Implications For Data Integrity
Example for MySQL:
create table dictionary ( word varchar(255) COLLATE latin2_bin NOT NULL, ); ALTER TABLE dictionary ADD UNIQUE INDEX (word);
Having BINARY collation latin2_bin defined now it's possible to insert
while, with NOCASE collation, the unique index would not allow this.^ toc
Problem with Non-latin1 Character Sets
Daabase backend needs to offer built-in support for Non-latin1 character collations. Kexi could not implement a workaround at the client side if a query has to be executed at the server side.^ toc
Collation in SQLite Backend
SQLite supports BINARY collation by default.
Non-latin1 Character Sets: we can patch SQLite to add such a support. See ser-defined Collation Sequences.^ toc
Collation in MySQL Backend
'By default, MySQL searches are not case sensitive.
To force BINARY collation, we will add COLLATE clause, e.g.:
select * from table where name>='abc' COLLATE latin1_bin
Notes: latin1_bin collation name always works. Collation and character sets can be properly selected on db creation or connecting only for MySQL >= 4.1. See also Table Character Set and Collation (mysql manual). So for newer MySQL versions use specific collations, not just latin1_bin.
Good default for collation/character set seem to be: utf8_general_ci/utf8.
Collation in PostgreSQL Backend
Implications For Query Designer
Good default is a #1 priority here. In most cases users expect NOCASE collation to be the default, so we're: