Complex Unique Constraints with PostgreSQL Triggers in Ecto

栏目: IT技术 · 发布时间: 4年前

内容简介:and a module with aThen, when you run:

Ecto makes it easy to work with typical uniqueness constraints in your database; you just define your table like this:

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

  def change do
    create table(:foos) do
      add :name, :text, null: false
    end

    create unique_index(:foos, :name)
  end
end

and a module with a changeset validation for the uniqueness constraint , perhaps like:

defmodule MyApp.Foo do
  use Ecto.Schema

  import Ecto.Changeset

  schema "foos" do
    field :name, :string
  end

  def changeset(%__MODULE__{} = foo, %{} = changes) do
      foo
      |> cast(changes, [:name])
      |> unique_constraint(:name)
  end
end

Then, when you run:

result = MyApp.Foo.changeset(%MyApp.Foo{}, %{name: "bar"})
         |> MyApp.Repo.insert()

The Ecto library will attempt to insert your record, and if there is already a record where the name column is set to “bar”, Ecto will see the uniqueness constraint violation error produced by the database and turn it into a validation error on your changeset, so that it would look something like:

{:error, %Ecto.Changeset{errors: [name: {"has already been taken", _}]}} = result

Something More Complex

I recently needed to enforce a database constraint similar in spirit to a unique index (if a record were in violation, I wanted the same behavior on the Elixir end of things—the changeset should report that the value for the field “has already been taken”) however the criteria for what should be considered “unique” was more complex than what a simple unique index in PostgreSQL would be able to deal with.

Let’s pretend we have a project that is managing meeting room reservations. The process for reserving a meeting room is as follows:

  1. The customer chooses a room and selects the time range for the reservation.
  2. Assuming the room is available at that time, the system places a hold on the room for 24 hours
  3. Within that 24-hour period, the customer pays for the room and completes some contractual information that must be signed by both the customer and the facility manager.
  4. Once the payment is complete and the contracts are signed, the reservation is confirmed.

Here, then, are the business rules for creating a reservation:

  • A room reservation cannot be made for a given room and time-period if there exists another room reservation for that same room with an overlapping time-period and the existing reservation is in a “confirmed” status.

  • A room reservation cannot be made for a given room and time-period if there exists another room reservation for that same room with an overlapping time-period, the existing reservation is in a “hold” status, and the existing reservation was created within the last 24 hours.

We decide to store the room reservations in a table defined as:

defmodule Meetings.Repo.Migrations.CreateRoomReservations do
  use Ecto.Migration

  def change do
    create table(:room_reservations) do
      add :customer_id, :binary_id, null: false
      add :room_id, :binary_id, null: false
      add :reservation_starts_at, :timestamp, null: false
      add :reservation_ends_at, :timestamp, null: false
      add :status, :text, null: false, default: "hold"
      timestamps()
    end
  end
end

and ideally, we’d like to be able to model the RoomReservation in Elixir as:

defmodule Meetings.RoomReservation do
  use Ecto.Schema

  import Ecto.Changeset

  schema "room_reservations" do
    field :customer_id, :binary_id
    field :room_id, :binary_id
    field :reservation_starts_at, :utc_datetime
    field :reservation_ends_at, :utc_datetime
    field :status, :string
    timestamps()
  end

  def create(%{} = res_data) do
    %__MODULE__{}
    |> cast(res_data, [:customer_id, :room_id, :reservation_starts_at, :reservation_ends_at])
    |> validate_required([:customer_id, :room_id, :reservation_starts_at, :reservation_ends_at])
    |> put_change(:status, "hold")
    |> unique_constraint(:room_id, message: "has already been reserved")
    |> Meeting.Repo.insert()
  end
end

with the interesting bit being the |> unique_constraint(:room_id) line. When a customer tries to reserve a room that is already reserved for the given time period, we want the Meeting.RoomReservation.create/1 function to return with a validation error:

%{:error, %Ecto.Changeset{errors: [room_id: {"has already been reserved", _}]}} =
  Meetings.RoomReservation.create(%{
    customer_id: "0af5716a-c3d2-4d4c-87f5-9fed9b2515d4",
    room_id: "2c0d6242-3585-40cb-be92-a1818c0f4a73",
    reservation_starts_at: DateTime.from_naive!(~N[2020-01-01 8:00:00], "Etc/UTC"),
    reservation_ends_at: DateTime.from_naive!(~N[2020-01-01 17:00:00], "Etc/UTC")
  })

Clearly, according to the business rules for the system, we can’t just put a unique index on the room_id column. To the best of my knowledge, an exclusion constraint also won’t work here, because of the need to not check against any rows that have a status of “hold” and an inserted_at timestamp that is more than 24 hours ago relative to the current time. It is however possible to use a trigger function to enforce the constraint in the database:

defmodule Meetings.Repo.Migrations.CreateRoomReservations do
  use Ecto.Migration

  def change do
    create table(:room_reservations) do
      add :customer_id, :binary_id, null: false
      add :room_id, :binary_id, null: false
      add :reservation_starts_at, :timestamp, null: false
      add :reservation_ends_at, :timestamp, null: false
      add :status, :text, null: false, default: "hold"
      timestamps()
    end

    execute(
      # up
      ~S"""
      CREATE FUNCTION room_reservations_check_room_availability() RETURNS TRIGGER AS
      $$
      BEGIN
      IF EXISTS(
        SELECT 1
        FROM room_reservations rr
        WHERE rr.room_id = NEW.room_id
          AND tsrange(rr.reservation_starts_at, rr.reservation_ends_at, '[]') &&
              tsrange(NEW.reservation_starts_at, NEW.reservation_ends_at, '[]')
          AND (
            rr.status = 'confirmed'
              OR rr.inserted_at > CURRENT_TIMESTAMP - interval '24 hours'
          )
      )
      THEN
        RAISE "room already reserved";
      END IF;
      RETURN NEW;
      END;
      $$ language plpgsql;
      """,

      # down
      "DROP FUNCTION room_reservations_check_room_availability;"
    )

    execute(
      # up
      ~S"""
      CREATE TRIGGER room_reservations_room_availability_check
      BEFORE INSERT ON room_reservations
      FOR EACH ROW
      EXECUTE PROCEDURE room_reservations_check_room_availability();
      """,

      # down
      "DROP TRIGGER room_reservations_room_availability_check ON room_reservations;"
    )
  end
end

The problem is that instead of that nice validation error on the changeset that we want to get, we instead end up with an unhandled Postgrex.Error exception. We could, of course, simply rescue that exception in our Meetings.RoomReservation.create/1 function and add the error to the changeset ourselves, but that starts to look a little ugly:

defmodule Meetings.RoomReservation do
  # ...

  def create(%{} = res_data) do
    changeset =
      %__MODULE__{}
      |> cast(res_data, [:customer_id, :room_id, :reservation_starts_at, :reservation_ends_at])
      |> validate_required([:customer_id, :room_id, :reservation_starts_at, :reservation_ends_at])
      |> put_change(:status, "hold")
    Meeting.Repo.insert(changeset)
  rescue
    error in Postgrex.Error ->
      case error do
        %{postgres: %{message: "room already reserved"}} ->
          changeset
          |> add_error(:room_id, "has already been reserved")
      end
  end
end

TL;DR - It’s All About the Raise

Knowing that Ecto.Changeset.unique_constraint/3 works by intercepting an error raised by the database, I set out to see if I could implement the complex unique constraint logic in the database and still be able to use the Ecto.Changeset.unique_constraint/3 validation without needing to modify any Elixir code. Looking at the relevant code in ecto_sql , we can see that the trick to getting the room_reservations_check_room_availability functionality to work with that function is to change the PostgreSQL exception to use the correct error code as well as a constraint name that is linked with the changeset validation. We can redifine the PostgreSQL function as:

CREATE FUNCTION room_reservations_check_room_availability() RETURNS TRIGGER AS
$$
BEGIN
IF EXISTS(
  SELECT 1
  FROM room_reservations rr
  WHERE rr.room_id = NEW.room_id
    AND tsrange(rr.reservation_starts_at, rr.reservation_ends_at, '[]') &&
        tsrange(NEW.reservation_starts_at, NEW.reservation_ends_at, '[]')
    AND (
      rr.status = 'confirmed'
        OR rr.inserted_at > CURRENT_TIMESTAMP - interval '24 hours'
    )
)
THEN
  RAISE unique_violation
    USING CONSTRAINT = 'room_reservations_room_reserved';
END IF;
RETURN NEW;
END;
$$ language plpgsql;

and then add the :name option to our unique_constraint in the changeset validation:

defmodule Meetings.RoomReservation do
  #...

  def create(%{} = res_data) do
    %__MODULE__{}
    |> cast(res_data, [:customer_id, :room_id, :reservation_starts_at, :reservation_ends_at])
    |> validate_required([:customer_id, :room_id, :reservation_starts_at, :reservation_ends_at])
    |> put_change(:status, "hold")
    |> unique_constraint(:room_id,
                         message: "has already been reserved",
                         name: "room_reservations_room_reserved")
    |> Meeting.Repo.insert()
  end
end

以上所述就是小编给大家介绍的《Complex Unique Constraints with PostgreSQL Triggers in Ecto》,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对 码农网 的支持!

查看所有标签

猜你喜欢:

本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们

Perl语言入门

Perl语言入门

[美] Randal L.Schwartz、Tom Phoenix / 李晓峰 / 中国电力出版社 / 2002-8 / 48.00元

本书第一版于1993年问世,并从此成为畅销书。本书由Perl社区最著名、最活跃的两位成员写成,是Perl程序设计语言的精髓指南。 Perl最初只是Unix系统管理员的一个工具,在工作日里被用在无数的小任务中。从那以后,它逐步发展成为一种全功能的程序设计语言,特别是在各种计算平台上,它被用作Web编程、数据库处理、XML处理以及系统管理——它能够完成所有这些工作,同时仍然是处理小的日常工作的完......一起来看看 《Perl语言入门》 这本书的介绍吧!

JS 压缩/解压工具
JS 压缩/解压工具

在线压缩/解压 JS 代码

XML 在线格式化
XML 在线格式化

在线 XML 格式化压缩工具

HSV CMYK 转换工具
HSV CMYK 转换工具

HSV CMYK互换工具