Locale-aware query expressions for Ecto on SQLite.
The macros mirror Localize.Ecto.Postgres and resolve collation names
through the same Localize.Ecto.Collation.resolve!/1, so a locale
produces the same collation name — "de-x-icu", "sv-x-icu",
"de-u-co-phonebk-x-icu" — on both databases and a query ported
between them sorts identically.
SQLite has no ICU collations of its own, so these macros require the
localize_icu extension this library ships. It is opt-in at build
time and loaded on every pooled connection:
# mix.exs, or LOCALIZE_SQL_SQLITE_ICU=true when compiling
config :localize_sql, :sqlite_icu, true
# config/runtime.exs
config :my_app, MyApp.Repo,
load_extensions: Localize.Ecto.SQLite3.Extension.load_extensions()See Localize.Ecto.SQLite3.Extension and the
Collations in SQLite guide for the full
setup, including what happens to a database indexed with an ICU
collation when the extension is absent.
With the extension loaded, usage is the same as on PostgreSQL:
import Ecto.Query
import Localize.Ecto.SQLite3
from p in Product, order_by: collate(p.name, "sv")
from p in Product, where: collate(p.name < "münchen", "de"), select: p.nameUnlike PostgreSQL, collations carrying a BCP 47 collation type need
no migration. SQLite collations are per-connection runtime
registrations rather than schema objects, and the extension builds
each one the first time a statement names it, so
collate(p.name, "de-u-co-phonebk") works with no setup beyond
loading the extension.
Differences from PostgreSQL
lower/2,upper/2andinitcap/2expand to the ICU functions the extension registers, not to a collated argument. SQLite'sCOLLATEaffects comparison only and never case mapping, and SQLite has noinitcap— it istitle()here.Localize.Ecto.Postgres.at_time_zone/2andLocalize.Ecto.Postgres.ts_match/2,3have no SQLite counterpart. SQLite has no time zone support, and its full-text search is FTS5, which needs a virtual table per searched column rather than an expression over an ordinary column.Localize.Ecto.Migration.create_collation/2is PostgreSQL-only, as described above.Localize.Ecto.Migration.collated/2emits valid SQLite index syntax and works on both.Canonically equivalent strings compare equal here and unequal on PostgreSQL. SQLite has no deterministic collations, so a comparison returns what ICU says, and ICU considers the NFC and NFD forms of
caféthe same string; PostgreSQL breaks that tie bytewise. Ordering agrees on both — it is equality,DISTINCT,GROUP BYand unique indexes that differ. Normalizing on write removes the difference.
Summary
Functions
Applies a COLLATE clause for the current locale.
Applies a COLLATE clause for the given locale or collation.
Locale-aware title casing using the current locale.
Locale-aware title casing, the equivalent of PostgreSQL's initcap().
Locale-aware lower() using the current locale.
Locale-aware lower().
Locale-aware upper() using the current locale.
Locale-aware upper(). See lower/2 for the arguments and semantics.
Functions
Applies a COLLATE clause for the current locale.
The collation is resolved from Localize.get_locale/0 at the time
the query is built.
Arguments
expressionis any Ecto query expression that evaluates to a string value, or a comparison (<,<=,>,>=,==,!=) between two such expressions.
Returns
- A query fragment
expression COLLATE "collation", or for a comparisonleft OP right COLLATE "collation".
Examples
iex> import Ecto.Query
iex> query = from p in "products", order_by: collate(p.name), select: p.name
iex> match?(%Ecto.Query{}, query)
trueApplies a COLLATE clause for the given locale or collation.
Arguments
expressionis any Ecto query expression that evaluates to a string value, or a comparison (<,<=,>,>=,==,!=) between two such expressions.locale_or_optionsis aLocalize.LanguageTag.t/0, any locale identifier accepted byLocalize.validate_locale/1, or a keyword list of options. A pinned expression (^locale) is also accepted, so runtime locale values read naturally in query syntax.
Options
:collationis a collation name used verbatim, bypassing locale resolution. On SQLite the name is itself the BCP 47 tag, so this is mainly useful for names produced elsewhere.Any other options are passed to
Localize.Ecto.Collation.collation_for!/2.
Returns
- A query fragment
expression COLLATE "collation", or for a comparisonleft OP right COLLATE "collation".
Examples
iex> import Ecto.Query
iex> query = from p in "products", order_by: collate(p.name, "sv"), select: p.name
iex> match?(%Ecto.Query{}, query)
true
iex> import Ecto.Query
iex> query = from p in "products", select: collate(p.name < p.description, "de")
iex> match?(%Ecto.Query{}, query)
trueLocale-aware title casing using the current locale.
See initcap/2.
Locale-aware title casing, the equivalent of PostgreSQL's initcap().
Expands to the title(expression, locale) function the localize_icu extension registers. SQLite has no initcap; the macro keeps the PostgreSQL name so queries port unchanged. See lower/2 for the arguments and semantics.
Examples
iex> import Ecto.Query
iex> query = from p in "products", select: initcap(p.name, "nl")
iex> match?(%Ecto.Query{}, query)
trueLocale-aware lower() using the current locale.
See lower/2.
Locale-aware lower().
Expands to the two-argument lower(expression, locale) the localize_icu extension registers, which maps case with ICU for the locale — under Turkish, lower("I") is the dotless "ı". SQLite's one-argument lower() is unaffected and still maps ASCII only.
Arguments
expressionis an Ecto query expression that evaluates to a string.locale_or_optionsis a locale or keyword list as accepted bycollate/2. A pinned expression (^locale) is also accepted.
Returns
- A query fragment
lower(expression, 'collation').
Examples
iex> import Ecto.Query
iex> query = from p in "products", select: lower(p.name, "tr")
iex> match?(%Ecto.Query{}, query)
trueLocale-aware upper() using the current locale.
See lower/2.
Locale-aware upper(). See lower/2 for the arguments and semantics.