Hi Chuck,
On 15 Aug 2017, at 16:44, Chuck Hill <[email protected]> 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 protected])
Help/Unsubscribe/Update your Subscription:
https://lists.apple.com/mailman/options/webobjects-dev/archive%40mail-archive.com
This email sent to [email protected]
