Handling a custom SQL signal similar to Changeset constraint functions?

Questions / Help
, , ,
1

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.

2

It’s looks like I should be able to use the MySQL error number and :extra_error_codes on the MyXQL config.

I’ll see if that works reasonably. I haven’t found where in ecto_sql that it handles other types than ER_DUP_ENTRY and a couple others

3

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.

2 Likes
4

If someone comes across this in the future, I added constraint migration support, and check_constraint Changeset support to ecto_sql and myxql so that’s another option.

In MySQL the check constraint error number is 3819, and the name, and message can be gotten by calling perror 3819

$ perror 3819
> MySQL error code MY-003819 (ER_CHECK_CONSTRAINT_VIOLATED): Check constraint '%-.192s' is violated.

If a check constraint makes sense for you, you can do the solution above, and just construct your message to match that message string, and signal that error.

I’m going to try and make a demo PR for ecto_sql to implement an overriding mechanism for to_constraints in the adapter behaviours as well, so custom errors can have custom messages, and be handled by changesets “like normal”