Cacher is the code snippet organizer for pro developers

-- Delete exact duplicates
UPDATE relationship r1
RIGHT JOIN
(
SELECT max(id) AS rid
FROM relationship r
WHERE r.is_deleted = 0
AND relationship_definition_keyname IN ('viewer','expander','editor','owner')
GROUP BY r.entity_id, r.related_entity_id, r.relationship_definition_keyname
HAVING count(1) > 1
) r2 ON r2.rid = r1.id
SET r1.deleted = now(), is_deleted = 1, deleted_by = 'remove duplicates'
;


-- For every duplicate right
--   delete one lower level copy.
-- Might want to execute multiple times
-- On 1,6M relationships it takes
-- LIMIT 1000 - 84s
UPDATE relationship
SET deleted = now(), is_deleted = 1, deleted_by = 'remove duplicates'
WHERE id IN
(
    SELECT id
    FROM
    (
        SELECT min(id) AS id
        FROM
        (
            SELECT r1.id,
                CASE r1.relationship_definition_keyname
                    WHEN 'viewer' THEN 1
                    WHEN 'expander' THEN 2
                    WHEN 'editor' THEN 3
                    WHEN 'owner' THEN 4
                    ELSE r1.relationship_definition_keyname
                END AS 'rlev', r2.*
            FROM relationship r1
            RIGHT JOIN
            (
                SELECT r.entity_id, r.related_entity_id
                FROM relationship r
                WHERE r.is_deleted = 0
                AND r.relationship_definition_keyname IN ('viewer','expander','editor','owner')
                GROUP BY r.entity_id, r.related_entity_id
                HAVING count(1) > 1
                LIMIT 3000
            ) r2 ON r2.entity_id = r1.entity_id AND r1.related_entity_id = r2.related_entity_id
            WHERE r1.is_deleted = 0
            AND   r1.relationship_definition_keyname IN ('viewer', 'expander', 'editor', 'owner')
            ORDER BY rlev
        ) foo
        GROUP BY entity_id
    ) baz
)
;

TRUNCATE TABLE dag_entity
;

INSERT INTO dag_entity (entity_id, related_entity_id, distance)
SELECT r.entity_id, r.related_entity_id, 1
FROM relationship r
LEFT JOIN entity eleft ON eleft.id = r.entity_id
LEFT JOIN entity eright ON eright.id = r.related_entity_id
WHERE r.relationship_definition_keyname = 'child'
AND r.is_deleted = 0
AND eleft.is_deleted = 0
AND eright.is_deleted = 0
ON DUPLICATE KEY UPDATE dag_entity.distance = 1
;

INSERT ignore INTO dag_entity (entity_id, related_entity_id, distance)
SELECT dleft.entity_id, dright.related_entity_id, dleft.distance + dright.distance as sum_distance
from dag_entity as dleft
 join dag_entity as dright on dleft.related_entity_id = dright.entity_id
ON DUPLICATE KEY UPDATE dag_entity.distance = least(dag_entity.distance, dleft.distance + dright.distance)
;

--
-- Give recursive rights to person on entity
--
SELECT id INTO @mihkel FROM entity WHERE old_id = 'mihkel';
SET @person_id = 415167;
SET @root_entity_id = 4173;
SET @right = 'editor';
SET @edk = 'folder';

INSERT INTO relationship (relationship_definition_keyname, entity_id, related_entity_id, created, created_by)
SELECT @right, de.related_entity_id AS entity_id
     , @person_id
     , now(), @mihkel
FROM dag_entity de
LEFT JOIN entity e ON e.id = de.related_entity_id
WHERE de.entity_id = @root_entity_id
AND e.is_deleted = 0
AND e.entity_definition_keyname = @edk
;

INSERT INTO relationship (relationship_definition_keyname, entity_id, related_entity_id, created, created_by)
SELECT @right, @root_entity_id AS entity_id, @person_id, now(), @mihkel
;

-- Now run script
--   Delete duplicate rights.sql