Localize.Ecto.Migration (Localize SQL v1.0.0)

Migration helpers for creating PostgreSQL ICU collations.

PostgreSQL preloads ICU collations for plain locales, but not for locales carrying a BCP 47 collation type such as de-u-co-phonebk (German phonebook ordering) or zh-u-co-stroke (Chinese stroke ordering). Those must be created once per database, which belongs in a migration:

defmodule MyApp.Repo.Migrations.AddPhonebookCollation do
  use Ecto.Migration

  import Localize.Ecto.Migration

  def change do
    create_collation("de-u-co-phonebk")
  end
end

The default collation name is the one Localize.Ecto.Collation resolves the same locale to, so once created, the collation is used automatically by Localize.Ecto.collate/2:

from p in Product, order_by: collate(p.name, "de-u-co-phonebk")

The primary public API is create_collation/2 and drop_collation/2. The SQL they execute is also available from create_collation_sql/2 and drop_collation_sql/2.

create_collation/2 and drop_collation/2 are PostgreSQL-only: a SQLite collation is a per-connection runtime registration rather than a schema object, so there is nothing to create and the tailorings above work there with no migration at all. collated/2 emits valid index syntax for both databases.

Summary

Functions

collated(column, locale_or_options \\ Localize.get_locale())

Returns a collated column expression for use in an index definition.

create_collation(locale, options \\ [])

Creates an ICU collation in a migration.

create_collation_sql(locale, options \\ [])

Returns the CREATE COLLATION statement for a locale.

drop_collation(locale, options \\ [])

Drops an ICU collation in a migration.

drop_collation_sql(locale, options \\ [])

Returns the DROP COLLATION statement for a locale.

Functions

collated(column, locale_or_options \\ Localize.get_locale())

@spec collated(atom() | String.t(), Localize.locale() | String.t() | Keyword.t()) ::
  String.t()

Returns a collated column expression for use in an index definition.

PostgreSQL only uses an index for a collated sort or comparison when the index was created with the same collation. This function builds the column expression for such an index, for use with Ecto.Migration.index/3:

create index("products", [collated(:name, "de")])

create index("products", [collated(:name, collation: "german_phonebook")])

Note that if a PostgreSQL upgrade links a newer ICU library whose collation data changed — uncommon, but it happens — PostgreSQL warns of a collation version mismatch and indexes built with that collation must be reindexed (REINDEX INDEX index_name, then ALTER COLLATION collation_name REFRESH VERSION).

Arguments

column is the column name as an atom or string.
locale_or_options is a locale accepted by Localize.Ecto.Collation.collation_for!/2, or a keyword list with a :collation option naming a collation directly. The default is the current locale from Localize.get_locale/0.

Returns

A column expression string such as "name" COLLATE "de-x-icu".

Examples

iex> Localize.Ecto.Migration.collated(:name, "de")
~s["name" COLLATE "de-x-icu"]

iex> Localize.Ecto.Migration.collated(:name, collation: "german_phonebook")
~s["name" COLLATE "german_phonebook"]

create_collation(locale, options \\ [])

@spec create_collation(Localize.locale() | String.t(), Keyword.t()) :: :ok

Creates an ICU collation in a migration.

Runs CREATE COLLATION with the ICU provider. The operation is reversible: rolling back drops the collation.

Arguments

locale is a Localize.LanguageTag.t/0, or any locale identifier accepted by Localize.validate_locale/1.
options is a keyword list of options.

Options

:name is the collation name to create. The default is the name Localize.Ecto.Collation.collation_for!/1 resolves locale to, such as "de-u-co-phonebk-x-icu", so queries built with Localize.Ecto.collate/2 find the collation without further configuration.
:icu_locale is the ICU locale string for the collation definition. The default is the canonical locale identifier of locale, which preserves all BCP 47 extensions.
:strength is the comparison strength: :primary (base letters only — case- and accent-insensitive), :secondary (adds accents), :tertiary (adds case), :quaternary, or :identical. Encoded as the -u-ks- keyword.
:numeric as true enables natural sort, comparing digit runs by numeric value so "file2" sorts before "file10". Encoded as the -u-kn- keyword.
:case_level as true adds a case level between the accent and case strengths, making a :primary collation accent-insensitive but case-sensitive. Encoded as the -u-kc- keyword.
:case_first is :upper, :lower, or false, ordering one case before the other. Encoded as the -u-kf- keyword.
:alternate is :shifted (punctuation ignored at the primary strengths) or :non_ignorable. Encoded as the -u-ka- keyword.
:backwards as true compares accents from the end of the string (Canadian French). Encoded as the -u-kb- keyword.
:deterministic determines whether the collation is deterministic. The default is true except when the effective strength is :primary or :secondary, where it defaults to false — those strengths only compare case/accent variants as equal when the collation is nondeterministic. Nondeterministic collations work with =, DISTINCT, GROUP BY and unique indexes, but not with LIKE or pg_trgm (PostgreSQL 18 lifts the LIKE restriction).
:if_not_exists adds IF NOT EXISTS when true. The default is false.
:schema schema-qualifies the collation name.

Returns

:ok.

Examples

create_collation("de-u-co-phonebk")

create_collation("de-u-co-phonebk", name: "german_phonebook")

# Natural sort: "file2" before "file10"
create_collation("en", numeric: true)

# Case- and accent-insensitive; nondeterministic by default.
# Use in a unique index for case-insensitive uniqueness:
#   create index("users", [collated(:email, "und-u-ks-level1")], unique: true)
create_collation("und", strength: :primary)

create_collation("und", strength: :secondary, name: "case_insensitive")

create_collation_sql(locale, options \\ [])

@spec create_collation_sql(Localize.locale() | String.t(), Keyword.t()) :: String.t()

Returns the CREATE COLLATION statement for a locale.

Arguments

locale is a Localize.LanguageTag.t/0, or any locale identifier accepted by Localize.validate_locale/1.
options is a keyword list of options. See create_collation/2.

Returns

The SQL statement as a string.

Examples

iex> Localize.Ecto.Migration.create_collation_sql("de-u-co-phonebk")
~s[CREATE COLLATION "de-u-co-phonebk-x-icu" (provider = icu, locale = 'de-u-co-phonebk')]

iex> Localize.Ecto.Migration.create_collation_sql("de-u-co-phonebk", name: "german_phonebook")
~s[CREATE COLLATION "german_phonebook" (provider = icu, locale = 'de-u-co-phonebk')]

drop_collation(locale, options \\ [])

@spec drop_collation(Localize.locale() | String.t(), Keyword.t()) :: :ok

Drops an ICU collation in a migration.

The operation is reversible: rolling back recreates the collation from the same arguments.

Arguments

locale is a Localize.LanguageTag.t/0, or any locale identifier accepted by Localize.validate_locale/1.
options is a keyword list of options. See create_collation/2; :if_not_exists is replaced by :if_exists.

Returns

:ok.

Examples

drop_collation("de-u-co-phonebk")

drop_collation("de-u-co-phonebk", name: "german_phonebook")

drop_collation_sql(locale, options \\ [])

@spec drop_collation_sql(Localize.locale() | String.t(), Keyword.t()) :: String.t()

Returns the DROP COLLATION statement for a locale.

Arguments

locale is a Localize.LanguageTag.t/0, or any locale identifier accepted by Localize.validate_locale/1.
options is a keyword list of options. See drop_collation/2.

Returns

The SQL statement as a string.

Examples

iex> Localize.Ecto.Migration.drop_collation_sql("de-u-co-phonebk")
~s[DROP COLLATION "de-u-co-phonebk-x-icu"]

iex> Localize.Ecto.Migration.drop_collation_sql("de-u-co-phonebk", if_exists: true)
~s[DROP COLLATION IF EXISTS "de-u-co-phonebk-x-icu"]