# `Localize.Ecto.Migration`
[🔗](https://github.com/elixir-localize/localize_sql/blob/v1.0.1/lib/localize/ecto/migration.ex#L2)

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.

# `collated`

```elixir
@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`

```elixir
@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 `t: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`

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

Returns the `CREATE COLLATION` statement for a locale.

### Arguments

* `locale` is a `t: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`

```elixir
@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 `t: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`

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

Returns the `DROP COLLATION` statement for a locale.

### Arguments

* `locale` is a `t: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"]

---

*Consult [api-reference.md](api-reference.md) for complete listing*