Locale-aware query expressions for Ecto.

Text sorts differently in different languages, and neither PostgreSQL nor SQLite sorts linguistically unless a query asks it to. This library resolves a Localize language tag to an ICU collation and applies it to a query expression, so that ordering, comparison and case mapping follow the user's locale.

There is one module per database, because Ecto requires the SQL of a fragment to be a compile-time literal and the two databases do not spell everything the same way:

• Localize.Ecto.Postgres — PostgreSQL. collate/1,2, lower/1,2, upper/1,2, initcap/1,2, plus at_time_zone/2 and ts_match/2,3.

• Localize.Ecto.SQLite3 — SQLite, via the localize_icu extension this library ships. collate/1,2, lower/1,2, upper/1,2 and initcap/1,2.

Both resolve collation names through the same Localize.Ecto.Collation, so a locale produces the same name — "de-x-icu", "sv-x-icu", "de-u-co-phonebk-x-icu" — on either database and a query written for one sorts identically on the other.

This module exposes the PostgreSQL macros directly, so existing code that imports it keeps working and PostgreSQL users need not think about the split:

import Ecto.Query
import Localize.Ecto

# Collate using the current locale (Localize.get_locale/0)
from p in Product, order_by: collate(p.name)

# Collate using an explicit locale
from p in Product, order_by: collate(p.name, "sv")

# Locale determined at runtime
def sorted_products(locale) do
  from p in Product, order_by: collate(p.name, ^locale)
end

# Collate a comparison: name < 'münchen' under German collation
from p in Product, where: collate(p.name < "münchen", "de"), select: p.name

# Use a collation created in a migration, by name
from p in Product, order_by: collate(p.name, collation: "german_phonebook")

For SQLite, import Localize.Ecto.SQLite3 instead. An application talking to both databases imports each where it needs it.

Collations for locales carrying a BCP 47 collation type, such as de-u-co-phonebk, are not preloaded by PostgreSQL. Create them once in a migration with Localize.Ecto.Migration.create_collation/2 and they resolve automatically thereafter. SQLite needs no such migration.