Set collation with SQLite in various contexts

[hut8]

Motivation

In SQLite, the collation for string columns is case-sensitive by default. However, when using LIKE, the comparison is case insensitive. When trying to do a case-insensitive query, you could use:

SELECT *
FROM `table`
WHERE `string_column` = 'CASE INSENSITIVE PLEASE' COLLATE NOCASE

Or, the collation can be set on a per-column basis during CREATE TABLE:

CREATE TABLE `table` (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    string_column VARCHAR(25) NOT NULL COLLATE NOCASE
);

Then you won't have to use COLLATE NOCASE in every query for that column.

Indexes are the same. If you create a regular string column (with COLLATE BINARY or by omitting COLLATE NOCASE), then either:

• query on it using COLLATE NOCASE
• query on it using LIKE which is case-insensitive by default

you will not be able to use an index if one was built on it before. Instead, to index that column, you have to use:

CREATE INDEX `string_column_nocase_idx`
ON `table` (`string_column` COLLATE NOCASE);

As far as I can tell, this isn't supported in SeaQUERY/SeaORM. But it's possible that I missed it in the docs, in which case I would gladly improve them.

In addition, I think every RDBMS supports configurable collations like this to some degree. https://www.postgresql.org/docs/current/collation.html https://dev.mysql.com/doc/refman/8.0/en/charset-column.html

With Postgres and SQLite particularly, this is a problem, because otherwise case-insensitive comparisons require a workaround if you want to use an index (and the workaround requires a workaround during index creation, too).

Proposed Solutions

Add the notion of collation to query types (SELECT, CREATE TABLE, CREATE INDEX, etc) that can use them.

Additional Information

It is unclear to me whether I should file this in SeaORM or SeaQuery. Sorry if it is in the wrong repo; I will gladly file it in the other one if necessary. Also, I can take a shot at implementing this, but I'm not much of a rustacean. Also, thanks for making such a great library!