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

Migration helpers for PostgreSQL range types.

PostgreSQL provides range types over numbers, dates and timestamps,
so most range columns need no migration beyond naming the type:

    add :period, :daterange

Two things do need a migration. There is no built-in range over time
of day, which `create_time_range/1` supplies, and the constraint that
makes ranges worth using — that no two rows may overlap — is an
exclusion constraint, which `no_overlap/3` builds:

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

      import Localize.Ecto.Range.Migration

      def change do
        create_btree_gist()

        create table(:bookings) do
          add :room_id, references(:rooms), null: false
          add :period, :tstzrange, null: false
        end

        create no_overlap(:bookings, :period, scope: [:room_id])
      end
    end

With that constraint in place, two bookings of the same room whose
periods overlap are rejected by the database, whatever the
application does.

# `create_btree_gist`

```elixir
@spec create_btree_gist() :: :ok
```

Enables the `btree_gist` extension.

An exclusion constraint is enforced by a GiST index, and GiST cannot
index equality on ordinary scalar types such as `integer` or `uuid`
without this extension. Any `no_overlap/3` constraint carrying a
`:scope` therefore needs it, and creating the constraint fails with
`data type integer has no default operator class` when it is absent.

The operation is reversible. Enabling an extension requires database
superuser privileges.

### Returns

* `:ok`.

### Examples

    create_btree_gist()

# `create_time_range`

```elixir
@spec create_time_range(Keyword.t()) :: :ok
```

Creates a range type over time of day.

PostgreSQL ships ranges over `integer`, `bigint`, `numeric`, `date`,
`timestamp` and `timestamptz`, but not over `time`. This creates one,
which is what opening hours and shift patterns need.

Postgrex discovers the new type when a connection pool starts, and
decodes it to a `t:Postgrex.Range.t/0` of `t:Time.t/0` with no
further configuration. Because discovery happens at connection time,
the type must be created in a migration rather than at runtime — a
pool already running when the type is created does not know it.

The operation is reversible.

### Arguments

* `options` is a keyword list of options.

### Options

* `:name` is the name of the range type. The default is
  `"timerange"`.

* `:subtype` is the SQL type the range spans. The default is
  `"time"`; pass `"timetz"` for a range over time with time zone.

### Returns

* `:ok`.

### Examples

    create_time_range()

    create_time_range(name: "opening_hours")

# `drop_time_range`

```elixir
@spec drop_time_range(Keyword.t()) :: :ok
```

Drops a range type over time of day.

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

### Arguments

* `options` is a keyword list of options. See `create_time_range/1`.

### Returns

* `:ok`.

### Examples

    drop_time_range()

    drop_time_range(name: "opening_hours")

# `no_overlap`

```elixir
@spec no_overlap(atom() | String.t(), atom() | String.t(), Keyword.t()) ::
  Ecto.Migration.Constraint.t()
```

Returns an exclusion constraint forbidding overlapping ranges.

The constraint rejects any two rows whose ranges overlap. A `:scope`
narrows that to rows agreeing on other columns, which is almost
always what is wanted — one room's bookings may not overlap each
other, but they may freely overlap another room's.

Pass the result to `Ecto.Migration.create/1`. A scoped constraint
needs the `btree_gist` extension; see `create_btree_gist/0`.

### Arguments

* `table` is the table name as an atom or string.

* `column` is the range column as an atom or string.

* `options` is a keyword list of options.

### Options

* `:scope` is a list of columns that must agree for two rows to be
  considered in conflict. The default is `[]`, which forbids any two
  rows in the table from overlapping.

* `:name` is the name of the constraint. The default is
  `"<table>_<column>_no_overlap"`.

* `:where` is a SQL predicate restricting the constraint to rows
  matching it, such as `"status <> 'cancelled'"`, so that rows
  failing the predicate are exempt. The default is `nil`.

### Returns

* An `t:Ecto.Migration.Constraint.t/0`.

### Examples

    create no_overlap(:bookings, :period, scope: [:room_id])

    create no_overlap(:bookings, :period, where: "status <> 'cancelled'")

---

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