Mailing List Archive

// Add your reply above here
==================================================
theory updated this ticket at August 6th, 2011 @ 12:15 AM
* Milestone changed from "" to "2.1.0"
* Assigned user changed from "Bugs (at bricolage)" to "theory"
* State changed from "new" to "open"
There are two disconnected places in the databases where the assets on desks are tracked. One is the `desk__id` column in the `story`, `media`, and `template` tables. The other is "asset groups." These are kind of nasty, but necessary in order for permissions to work properly.

Unfortunately, these two sources can drift from one another. I've written queries to repair the drifts. These should make things much more accurate:

-- Set desk ID to 0 where not on a desk.
UPDATE story SET desk__id = 0
WHERE desk__id > 0 AND id NOT IN (
SELECT story.id
FROM story
JOIN story_member ON story.id = story_member.object_id
JOIN member ON story_member.member__id = member.id
JOIN desk ON member.grp__id = desk.asset_grp
);

UPDATE media SET desk__id = 0
WHERE desk__id > 0 AND id NOT IN (
SELECT media.id
FROM media
JOIN media_member ON media.id = media_member.object_id
JOIN member ON media_member.member__id = member.id
JOIN desk ON member.grp__id = desk.asset_grp
);

UPDATE template SET desk__id = 0
WHERE desk__id > 0 AND id NOT IN (
SELECT template.id
FROM template
JOIN template_member ON template.id = template_member.object_id
JOIN member ON template_member.member__id = member.id
JOIN desk ON member.grp__id = desk.asset_grp
);

-- Set desk ID where asset is on a desk.
UPDATE story
SET desk__id = desk.id
FROM story_member
JOIN member ON story_member.member__id = member.id
JOIN desk ON member.grp__id = desk.asset_grp
WHERE story.id = story_member.object_id
AND story.desk__id <> desk.id;

UPDATE media
SET desk__id = desk.id
FROM media_member
JOIN member ON media_member.member__id = member.id
JOIN desk ON member.grp__id = desk.asset_grp
WHERE media.id = media_member.object_id
AND media.desk__id <> desk.id;

UPDATE template
SET desk__id = desk.id
FROM template_member
JOIN member ON template_member.member__id = member.id
JOIN desk ON member.grp__id = desk.asset_grp
WHERE template.id = template_member.object_id
AND template.desk__id <> desk.id;

As for a long-term fix, I'm working on some triggers to keep these two sources in sync. They may or may not be added in 2.1; I want to do some more testing and then discuss the cost on dev list.
--------------------------------------------------------------------------------

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