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

Copy Markdown View Source

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.

Summary

Functions

create_btree_gist()

Enables the btree_gist extension.

create_time_range(options \\ [])

Creates a range type over time of day.

drop_time_range(options \\ [])

Drops a range type over time of day.

no_overlap(table, column, options \\ [])

Returns an exclusion constraint forbidding overlapping ranges.

Functions

create_btree_gist()

@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(options \\ [])

@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 Postgrex.Range.t/0 of 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(options \\ [])

@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

Returns

  • :ok.

Examples

drop_time_range()

drop_time_range(name: "opening_hours")

no_overlap(table, column, options \\ [])

@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

Examples

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

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