felix-starman

Handling a custom SQL signal similar to Changeset constraint functions?

So, I’m using MySQL, and having a table where I’m restricting the ability to insert/update records to have overlaps, but end_date is allowed to be NULL.

Since MySQL doesn’t have extensions around this, the easiest way to do it is with a trigger.

I have the following triggers:

CREATE TRIGGER memberships_insert_overlap
    BEFORE INSERT
    ON team_memberships FOR EACH ROW
BEGIN
    DECLARE rowcount INT;

    SELECT COUNT(*) INTO rowcount FROM team_memberships
    WHERE person_id = NEW.person_id
        AND (NEW.start_date <= COALESCE(end_date, '9999-12-31')) and (COALESCE(NEW.end_date, '9999-12-31') >= start_date)
        AND (NEW.start_date <= COALESCE(NEW.end_date, '9999-12-31')) and (start_date <= COALESCE(end_date, '9999-12-31'));

    IF rowcount > 0 THEN
        signal sqlstate '45000' set message_text = 'overlap not allowed team_memberships.no_overlap';
    END IF;

END;


CREATE TRIGGER memberships_update_overlap
    BEFORE UPDATE
    ON team_memberships FOR EACH ROW
BEGIN
    DECLARE rowcount INT;

    SELECT COUNT(*) INTO rowcount FROM team_memberships
    WHERE person_id = NEW.person_id AND id != OLD.id
        AND (NEW.start_date <= COALESCE(end_date, '9999-12-31')) and (COALESCE(NEW.end_date, '9999-12-31') >= start_date)
        AND (NEW.start_date <= COALESCE(NEW.end_date, '9999-12-31')) and (start_date <= COALESCE(end_date, '9999-12-31'));

    IF rowcount > 0 THEN
        signal sqlstate '45000' set message_text = 'overlap not allowed team_memberships.no_overlap';
    END IF;
END;

I’m getting the typical/expected (MyXQL.Error) (1644) overlap not allowed team_memberships.no_overlap

I feel like there’s probably something I’m missing with what sqlstate should be set to for “emulating” a constraint, or that there’s some place in MyXQL where I can register a custom handler.

Anyone have any ideas?

I’d rather not litter my code w/ error-catching statements wherever we do inserts/updates.

EDIT: I should note, I’m not concerned about the trigger logic itself. That works fine. I’m wondering if there’s a better way to wrap up this interface.

3 comments

#mysql

2 211 3

2024-07-07 19:41:41 UTC

Marked As Solved

felix-starman

For anyone who comes across this in the future, extra_error_codes currently only allows the raised error to include a name: i.e. ** (MyXQL.Error) (1644) (ER_SIGNAL_EXCEPTION) overlap not allowed instead of just ** (MyXQL.Error) (1644) overlap not allowed .

I’m not sure what follows was the “right” way to do it and I’m sure MySQL DBAs would be shaking their heads, but it’s what I did.

Since it’s still similar enough to a duplicate entry/unique constraint if you turn your head sideways and squint at it, I just updated the SQLSTATE to 23000, and MYSQL_ERRNO to 1062, which maps to ER_DUP_ENTRY (used for unique constaints).

For completeness, here’s the migration.
This basically rejects any insert/update that is overlapping on date ranges, including nulls on the end_date.

I wouldn’t say it’s “good”. But it gets the job done.

defmodule MyApp.Repo.Migrations.AddNonoverlapTriggerToTeamMemberships do
  @moduledoc """
  From https://stackoverflow.com/questions/325933/determine-whether-two-date-ranges-overlap

  Proof:
  Let ConditionA Mean that DateRange A Completely After DateRange B

  _                        |---- DateRange A ------|
  |---Date Range B -----|                          _
  (True if StartA > EndB)

  Let ConditionB Mean that DateRange A is Completely Before DateRange B

  |---- DateRange A -----|                        _
  _                          |---Date Range B ----|
  (True if EndA < StartB)

  Then Overlap exists if Neither A Nor B is true -
  (If one range is neither completely after the other,
  nor completely before the other, then they must overlap.)

  Now one of De Morgan's laws says that:

  Not (A Or B) <=> Not A And Not B

  Which translates to: (StartA <= EndB)  and  (EndA >= StartB)
  """

  use MyApp.Migration

  def up do
    insert_trigger_sql = ~s"""
    CREATE TRIGGER memberships_insert_overlap
      BEFORE INSERT
      ON team_memberships FOR EACH ROW
    BEGIN
      DECLARE rowcount INT;
      DECLARE msg VARCHAR(200);

      SELECT COUNT(*) INTO rowcount FROM team_memberships
      WHERE person_id = NEW.person_id
        AND (NEW.start_date <= COALESCE(end_date, '9999-12-31')) AND (COALESCE(NEW.end_date, '9999-12-31') >= start_date)
        AND (NEW.start_date <= COALESCE(NEW.end_date, '9999-12-31')) AND (start_date <= COALESCE(end_date, '9999-12-31'));

      IF rowcount > 0 THEN
          set msg = CONCAT('Duplicate entry \\'', COALESCE(NEW.end_date, 'NULL'), '\\' for key \\'team_memberships.no_overlap\\'');
          signal sqlstate '23000' set MESSAGE_TEXT = msg, MYSQL_ERRNO = 1062;
      END IF;
    END;
    """

    update_trigger_sql = ~s"""
    CREATE TRIGGER memberships_update_overlap
      BEFORE UPDATE
      ON team_memberships FOR EACH ROW
    BEGIN
      DECLARE rowcount INT;
      DECLARE msg VARCHAR(200);

      SELECT COUNT(*) INTO rowcount FROM team_memberships
      WHERE person_id = NEW.person_id AND id != OLD.id
        AND (NEW.start_date <= COALESCE(end_date, '9999-12-31')) and (COALESCE(NEW.end_date, '9999-12-31') >= start_date)
        AND (NEW.start_date <= COALESCE(NEW.end_date, '9999-12-31')) and (start_date <= COALESCE(end_date, '9999-12-31'));

      IF rowcount > 0 THEN
          set msg = CONCAT('Duplicate entry \\'', COALESCE(NEW.end_date, 'NULL'), '\\' for key \\'team_memberships.no_overlap\\'');
          signal sqlstate '23000' set MESSAGE_TEXT = msg, MYSQL_ERRNO = 1062;
      END IF;
    END;
    """

    drop_triggers()
    repo().query!(insert_trigger_sql)
    repo().query!(update_trigger_sql)
  end

  def down do
    drop_triggers()
  end

  defp drop_triggers do
    repo().query!("DROP TRIGGER IF EXISTS memberships_insert_overlap")
    repo().query!("DROP TRIGGER IF EXISTS memberships_update_overlap")
  end
end

I may open a proposal thread for discussion about the ecto_sql adapters to allow custom handling of error codes through something like an mfa tuple from config.

Post #3