Select Git revision
retrieveEntityOverrides.sql
-
Timm Fitschen authoredTimm Fitschen authored
Code owners
Assign users and groups as approvers for specific file changes. Learn more.
retrieveEntityOverrides.sql 6.85 KiB
/*
* ** header v3.0
* 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 Indiscale GmbH <info@indiscale.com>
* Copyright (C) 2020 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/>.
*
* ** end header
*/
DROP PROCEDURE IF EXISTS db_5_0.retrieveOverrides;
delimiter //
CREATE PROCEDURE db_5_0.retrieveOverrides(
in DomainID INT UNSIGNED,
in EntityID INT UNSIGNED,
in Version VARBINARY(255))
retrieveOverridesBody: BEGIN
DECLARE IVersion INT UNSIGNED DEFAULT NULL;
DECLARE IsHead BOOLEAN DEFAULT TRUE;
DECLARE InternalEntityID INT UNSIGNED DEFAULT NULL;
DECLARE InternalDomainID INT UNSIGNED DEFAULT NULL;
SELECT temp.internal_id INTO InternalDomainID FROM (SELECT internal_id AS internal_id FROM entity_ids WHERE id = DomainID UNION SELECT DomainID AS internal_id) AS temp LIMIT 1;
SELECT temp.internal_id INTO InternalEntityID FROM (SELECT internal_id AS internal_id FROM entity_ids WHERE id = EntityID UNION SELECT EntityID AS internal_id) AS temp LIMIT 1;
IF is_feature_config("ENTITY_VERSIONING", "ENABLED") THEN
IF Version IS NOT NULL THEN
IF InternalDomainID = 0 THEN
SELECT get_head_version(EntityID) = Version INTO IsHead;
ELSE
SELECT get_head_version(DomainID) = Version INTO IsHead;
END IF;
END IF;
IF IsHead IS FALSE THEN
SELECT e._iversion INTO IVersion
FROM entity_version as e
WHERE ((e.entity_id = InternalEntityID AND InternalDomainID = 0)
OR (e.entity_id = InternalDomainID))
AND e.version = Version;
IF IVersion IS NULL THEN
-- RETURN EARLY - Version does not exist.
LEAVE retrieveOverridesBody;
END IF;
-- name
SELECT
NULL AS collection_override,
name AS name_override,
NULL AS desc_override,
NULL AS type_override,
EntityID AS entity_id,
property_id AS InternalPropertyID,
( SELECT id FROM entity_ids WHERE internal_id = property_id ) AS property_id
FROM archive_name_overrides
WHERE domain_id = InternalDomainID
AND entity_id = InternalEntityID
AND _iversion = IVersion
UNION ALL
-- description
SELECT
NULL AS collection_override,
NULL AS name_override,
description AS desc_override,
NULL AS type_override,
EntityID AS entity_id,
property_id AS InternalPropertyID,
( SELECT id FROM entity_ids WHERE internal_id = property_id ) AS property_id
FROM archive_desc_overrides
WHERE domain_id = InternalDomainID
AND entity_id = InternalEntityID
AND _iversion = IVersion
UNION ALL
-- data type
SELECT
NULL AS collection_override,
NULL AS name_override,
NULL AS desc_override,
IFNULL((SELECT value FROM name_data
WHERE domain_id = 0
AND entity_id = datatype
AND property_id = 20
LIMIT 1), datatype) AS type_override,
EntityID AS entity_id,
property_id AS InternalPropertyID,
( SELECT id FROM entity_ids WHERE internal_id = property_id ) AS property_id
FROM archive_data_type
WHERE domain_id = InternalDomainID
AND entity_id = InternalEntityID
AND _iversion = IVersion
UNION ALL
-- collection
SELECT
collection AS collection_override,
NULL AS name_override,
NULL AS desc_override,
NULL AS type_override,
EntityID AS entity_id,
property_id AS InternalPropertyID,
( SELECT id FROM entity_ids WHERE internal_id = property_id ) AS property_id
FROM archive_collection_type
WHERE domain_id = InternalDomainID
AND entity_id = InternalEntityID
AND _iversion = IVersion;
LEAVE retrieveOverridesBody;
END IF;
END IF;
SELECT
NULL AS collection_override,
name AS name_override,
NULL AS desc_override,
NULL AS type_override,
EntityID AS entity_id,
property_id AS InternalPropertyID,
( SELECT id FROM entity_ids WHERE internal_id = property_id ) AS property_id
FROM name_overrides
WHERE domain_id = InternalDomainID
AND entity_id = InternalEntityID
UNION ALL
SELECT
NULL AS collection_override,
NULL AS name_override,
description AS desc_override,
NULL AS type_override,
EntityID AS entity_id,
property_id AS InternalPropertyID,
( SELECT id FROM entity_ids WHERE internal_id = property_id ) AS property_id
FROM desc_overrides
WHERE domain_id = InternalDomainID
AND entity_id = InternalEntityID
UNION ALL
SELECT
NULL AS collection_override,
NULL AS name_override,
NULL AS desc_override,
IFNULL((SELECT value FROM name_data
WHERE domain_id = 0
AND entity_ID = datatype
AND property_id = 20 LIMIT 1), datatype) AS type_override,
EntityID AS entity_id,
property_id AS InternalPropertyID,
( SELECT id FROM entity_ids WHERE internal_id = property_id ) AS property_id
FROM data_type
WHERE domain_id = InternalDomainID
AND entity_id = InternalEntityID
UNION ALL
SELECT
collection AS collection_override,
NULL AS name_override,
NULL AS desc_override,
NULL AS type_override,
EntityID AS entity_id,
property_id AS InternalPropertyID,
( SELECT id FROM entity_ids WHERE internal_id = property_id ) AS property_id
FROM collection_type
WHERE domain_id = InternalDomainID
AND entity_id = InternalEntityID;
END;
//
delimiter ;