Add support for collation

[thibautd]

Hello,

I don't know for Cockroach DB, but I know that PostgreSQL supports collations. This is nice feature that replaces existing extensions like citext in order to have case insensitive columns (like an email).

I think we could have a collation= property to Varchar/Text columns. Collations can be created by a raw migration, but any better system would be welcome. Of course it could work with any collation, not only case insensitive.

Currently I implemented this using a Varchar subclass, as a proof of concept:

class VarcharCaseInsensitive(Varchar):
    @property
    def column_type(self):
        return super().column_type + " COLLATE case_insensitive"

And I added this collation using a migration:

async def create_ci_collation():
    await RawTable.raw(
        "CREATE COLLATION case_insensitive (provider = icu, locale = 'und-u-ks-level2', deterministic = false);"
    )

async def drop_ci_collation():
    await RawTable.raw("DROP COLLATION IF EXISTS case_insensitive")

manager.add_raw(create_ci_collation)
manager.add_raw_backwards(drop_ci_collation)

I'm new to Piccolo and I'm discovering the code base, their might be a better way of doing this.

[dantownsend]

It's an interesting idea.

It wouldn't be too hard to add collate as an argument to the order_by method. We have OrderByRaw, so it is kind of possible now.

I'm not sure if CockroachDB supports collate - i would have to check.