Re: Enforcing a maximum cardinality on a many-to-many relationship
Re: Enforcing a maximum cardinality on a many-to-many relationship
- Subject: Re: Enforcing a maximum cardinality on a many-to-many relationship
- From: Paul Hoadley <email@hidden>
- Date: Wed, 16 Aug 2017 11:03:49 +0930
Hi Chuck,
On 15 Aug 2017, at 16:44, Chuck Hill <email@hidden> wrote:
> For cases like this, the database is the right, and only, place to guarantee
> this.
Thanks. For what it’s worth, I agree. I think I just hesitated thinking it
might be painful to set up the stored procedure and trigger using migrations,
and wondered if there was a feasible EOF-level approach. Turns out it wasn’t
hard, though involved some trial and error with syntax for the stored procedure.
For the archives, here’s the PostgreSQL solution. This is
src/main/resources/job_workers_constraint.sql:
CREATE OR REPLACE FUNCTION job_max_worker_constraint() RETURNS trigger AS
'
BEGIN
IF (SELECT count(*) FROM jobworker jw WHERE jw.jobid = NEW.jobid)
>= (SELECT max_workers FROM job j WHERE j.id = NEW.jobid) THEN
RAISE EXCEPTION ''Job.workers.count exceeds Job.maxWorkers.'';
END IF;
RETURN NEW;
END
' LANGUAGE plpgsql;
Note carefully that this doesn’t use PostgreSQL’s ‘$$’ delimiters for the
function body—doing that causes ERXJDBCUtilities to split the function body on
its semi-colons, which is not what we want. (That was the trial and error
part.) Instead, you need to single-quote-delimit the function body, as above,
and escape any single quotes in the function body (the exception message above)
by double-single-quoting.
Then, this is src/main/resources/job_workers_trigger.sql:
CREATE TRIGGER job_workers_count_check
BEFORE INSERT ON jobworker
FOR EACH ROW EXECUTE PROCEDURE job_max_worker_constraint();
Execute these in a migration:
ERXJDBCUtilities.executeUpdateScriptFromResourceNamed(database.adaptorChannel(),
"job_workers_constraint.sql", null);
ERXJDBCUtilities.executeUpdateScriptFromResourceNamed(database.adaptorChannel(),
"job_workers_trigger.sql", null);
Handling the exception generated requires catching EOGeneralAdaptorException at
relevant calls to saveChanges(). I can’t see a more elegant approach than
seeing if getMessage().contains("Job.workers.count exceeds Job.maxWorkers.”),
but if anyone can think of one, I’m all ears.
--
Paul Hoadley
https://logicsquad.net/
https://www.linkedin.com/company/logic-squad/
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
This email sent to email@hidden