Select Git revision
retrieveEntityParents.sql
-
Timm Fitschen authoredTimm Fitschen authored
Code owners
Assign users and groups as approvers for specific file changes. Learn more.
retrieveEntityParents.sql 4.13 KiB
/*
* This file is a part of the CaosDB Project.
*
* Copyright (C) 2018 Research Group Biomedical Physics,
* Max-Planck-Institute for Dynamics and Self-Organization Göttingen
* Copyright (C) 2020,2023 IndiScale GmbH <info@indiscale.com>
* Copyright (C) 2020,2023 Timm Fitschen <t.fitschen@indiscale.com>
*
* This program is free software: you can redistribute it and/or modify
* it under the terms of the GNU Affero General Public License as
* published by the Free Software Foundation, either version 3 of the
* License, or (at your option) any later version.
*
* This program is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
* GNU Affero General Public License for more details.
*
* You should have received a copy of the GNU Affero General Public License
* along with this program. If not, see <https://www.gnu.org/licenses/>.
*/
DROP PROCEDURE IF EXISTS db_5_0.retrieveEntityParents;
DELIMITER //
/* Retrieve the parents of an Entity.
*
* Parameters
* ----------
* EntityID : VARCHAR(255)
* Child entity for which all parental relations should be deleted.
*
* ResultSet
* ---------
* ParentID : VARCHAR(255)
* Each parent's ID
* ParentName : VARCHAR(255)
* The parent's name.
* ParentDescription : TEXT
* The parent's description.
* ParentRole : VARCHAR(255)
* The parent's Role.
* ACL : VARBINARY(65525)
* Access control list.
*/
CREATE PROCEDURE db_5_0.retrieveEntityParents(
in EntityID VARCHAR(255),
in Version VARBINARY(255))
retrieveEntityParentsBody: BEGIN
DECLARE IVersion INT UNSIGNED DEFAULT NULL;
DECLARE IsHead BOOLEAN DEFAULT TRUE;
DECLARE InternalEntityID INT UNSIGNED DEFAULT NULL;
SELECT internal_id INTO InternalEntityID from entity_ids WHERE id = EntityID;
IF is_feature_config("ENTITY_VERSIONING", "ENABLED") THEN
IF Version IS NOT NULL THEN
SELECT get_head_version(EntityID) = Version INTO IsHead;
END IF;
IF IsHead IS FALSE THEN
SELECT e._iversion INTO IVersion
FROM entity_version as e
WHERE e.entity_id = InternalEntityID
AND e.version = Version;
IF IVersion IS NULL THEN
-- RETURN EARLY - Version does not exist.
LEAVE retrieveEntityParentsBody;
END IF;
SELECT
( SELECT id FROM entity_ids WHERE internal_id = i.parent) AS ParentID,
( SELECT value FROM name_data
WHERE domain_id = 0
AND entity_id = i.parent
AND property_id = 20
) AS ParentName, -- This is not necessarily the name of the parent at the time of
-- IVersion but it is a good guess. Future implementations of the
-- archive_isa table should also store the IVersion of the
-- parents. Only then the historically correct ParentName can be
-- reconstructed.
e.description AS ParentDescription,
e.role AS ParentRole,
(SELECT acl FROM entity_acl AS a WHERE a.id = e.acl) AS ACL
FROM archive_isa AS i JOIN entities AS e
ON (i.parent = e.id)
WHERE i.child = InternalEntityID
AND i.child_iversion = IVersion
AND i.direct IS TRUE
;
LEAVE retrieveEntityParentsBody;
END IF;
END IF;
SELECT
( SELECT id FROM entity_ids WHERE internal_id = i.parent) AS ParentID,
( SELECT value FROM name_data
WHERE domain_id = 0
AND entity_id = i.parent
AND property_id = 20 ) AS ParentName,
e.description AS ParentDescription,
e.role AS ParentRole,
(SELECT acl FROM entity_acl AS a WHERE a.id = e.acl) AS ACL
FROM isa_cache AS i JOIN entities AS e
ON (i.parent = e.id)
WHERE i.child = InternalEntityID
AND i.rpath = InternalEntityID;
END
//
DELIMITER ;