// Add your reply above here
==================================================
theory updated this ticket at September 29th, 2011 @ 05:29 PM
> I haven't found any desk-asset sync issues on my test server after running a number of api scripts and bric_soap bulk operations. We've been moving a lot of assets between categories lately, and I usually run the operation against the test server first to see how it goes. The triggers also don't seem to induce any noticeable performance issues.
Excellent!
> Remember how you patched the desk_has_story method because it threw an error any time a user attempted to create a new story? That error happens with new media and new templates, too, so I haven't actually pushed the triggers into the production server (it's probably a really easy fix, but haven't had much time lately).
Oops, sorry I didn't do it before. Here you go:
CREATE OR REPLACE FUNCTION desk_has_media(
) RETURNS TRIGGER LANGUAGE PLPGSQL AS $$
BEGIN
IF TG_OP = 'UPDATE' THEN
IF NEW.desk__id = OLD.desk__id THEN RETURN NEW; END IF;
END IF;
IF NEW.desk__id > 0 THEN
IF EXISTS (
SELECT media_member.object_id
FROM desk
JOIN member ON member.grp__id = desk.asset_grp
JOIN media_member ON media_member.member__id = member.id
WHERE desk.id = NEW.desk__id
AND media_member.object_id = NEW.id
) THEN RETURN NEW; END IF;
RAISE EXCEPTION 'Desk % should have media % in its group but does not',
NEW.desk__id, NEW.id;
ELSIF TG_OP = 'UPDATE' THEN
IF NOT EXISTS (
SELECT media_member.object_id
FROM desk
JOIN member ON member.grp__id = desk.asset_grp
JOIN media_member ON media_member.member__id = member.id
WHERE desk.id = OLD.desk__id
AND media_member.object_id = NEW.id
) THEN RETURN NEW; END IF;
RAISE EXCEPTION 'Desk % should not have media % in its group but does',
OLD.desk__id, NEW.id;
ELSE
RETURN NEW;
END IF;
END;
$$;
CREATE OR REPLACE FUNCTION desk_has_media(
) RETURNS TRIGGER LANGUAGE PLPGSQL AS $$
BEGIN
IF TG_OP = 'UPDATE' THEN
IF NEW.desk__id = OLD.desk__id THEN RETURN NEW; END IF;
END IF;
IF NEW.desk__id > 0 THEN
IF EXISTS (
SELECT media_member.object_id
FROM desk
JOIN member ON member.grp__id = desk.asset_grp
JOIN media_member ON media_member.member__id = member.id
WHERE desk.id = NEW.desk__id
AND media_member.object_id = NEW.id
) THEN RETURN NEW; END IF;
RAISE EXCEPTION 'Desk % should have media % in its group but does not',
NEW.desk__id, NEW.id;
ELSIF TG_OP = 'UPDATE' THEN
IF NOT EXISTS (
SELECT media_member.object_id
FROM desk
JOIN member ON member.grp__id = desk.asset_grp
JOIN media_member ON media_member.member__id = member.id
WHERE desk.id = OLD.desk__id
AND media_member.object_id = NEW.id
) THEN RETURN NEW; END IF;
RAISE EXCEPTION 'Desk % should not have media % in its group but does',
OLD.desk__id, NEW.id;
ELSE
RETURN NEW;
END IF;
END;
$$;
> Pending fixes for those issues, the triggers are probably ready for wider testing.
Great. I think I'd get them committed, and the update the installer to require PL/pgSQL. Might be annoying for folks, but in truth will give us a lot more power on the backend to do other stuff, should we so choose.
--------------------------------------------------------------------------------
Assigned: theory
State: open
Milestone: 2.1.0
View this ticket online:
http://bricolage.lighthouseapp.com/projects/29601/tickets/272-crash-when-accessing-a-desk Stop being notified of this ticket's changes:
http://bricolage.lighthouseapp.com/watching/3769b563149c133b788665e1974a03a8a714b96c Update your Profile:
http://bricolage.lighthouseapp.com/profile Support: support@lighthouseapp.com or
http://help.lighthouseapp.com