Add support for collation
thibautd opened this issue · 1 comments
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.
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.