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.
