Select Git revision
retrieveEntityProperties.sql
-
Timm Fitschen authoredTimm Fitschen authored
Code owners
Assign users and groups as approvers for specific file changes. Learn more.
retrieveEntityProperties.sql 12.25 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/>.
*/
delimiter //
drop procedure if exists db_5_0.retrieveEntityProperties //
/*
* Retrieve the properties of an entity (level 1 and 2).
*
* Parameters
* ----------
*
* DomainID : VARCHAR(255)
* The domain id (0 or the entity's id for level-2-data)
* EntityID : VARCHAR(255)
* The entity id (or the property's id for level-2-data)
* Version : VARBINARY(255)
* The version of the entity. Optional
*
* ResultSet
* ---------
* InternalPropertyID
* Internal property id, to be used when PropertyID
* is NULL because a replacement is being using.
* PropertyID
* The property id
* PropertyValue
* The property value
* PropertyStatus
* E.g. OBLIGATORY, FIX, ...
* PropertyIndex
* The index of the property (for ordering).
* )
*/
create procedure db_5_0.retrieveEntityProperties(
in DomainID VARCHAR(255),
in EntityID VARCHAR(255),
in Version VARBINARY(255))
retrieveEntityPropertiesBody: BEGIN
DECLARE IVersion INT UNSIGNED DEFAULT NULL;
DECLARE IsHead BOOLEAN DEFAULT TRUE;
DECLARE InternalEntityID INT UNSIGNED DEFAULT NULL;
DECLARE InternalDomainID INT UNSIGNED DEFAULT 0;
-- When DomainID != 0 the EntityID could possibly be a 'replacement id'
-- which are internal ids by definition (and do not have external
-- equivalents). That's why we do the UNION here, falling back to the
-- EntityID. 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;
-- DomainID != 0 are always normal (i.e. external) Entity ids.
SELECT internal_id INTO InternalDomainID from entity_ids WHERE id = DomainID;
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 retrieveEntityPropertiesBody;
END IF;
#-- double properties
SELECT
property_id AS InternalPropertyID,
( SELECT id FROM entity_ids WHERE internal_id = property_id ) AS PropertyID,
value AS PropertyValue,
status AS PropertyStatus,
pidx AS PropertyIndex
FROM archive_double_data
WHERE domain_id = InternalDomainID
AND entity_id = InternalEntityID
AND _iversion = IVersion
UNION ALL
#-- integer properties
SELECT
property_id AS InternalPropertyID,
( SELECT id FROM entity_ids WHERE internal_id = property_id ) AS PropertyID,
value AS PropertyValue,
status AS PropertyStatus,
pidx AS PropertyIndex
FROM archive_integer_data
WHERE domain_id = InternalDomainID
AND entity_id = InternalEntityID
AND _iversion = IVersion
UNION ALL
#-- date properties
SELECT
property_id AS InternalPropertyID,
( SELECT id FROM entity_ids WHERE internal_id = property_id ) AS PropertyID,
CONCAT(value, '.NULL.NULL') AS PropertyValue,
status AS PropertyStatus,
pidx AS PropertyIndex
FROM archive_date_data
WHERE domain_id = InternalDomainID
AND entity_id = InternalEntityID
AND _iversion = IVersion
UNION ALL
#-- datetime properties
SELECT
property_id AS InternalPropertyID, ( SELECT id FROM entity_ids WHERE internal_id = property_id ) AS PropertyID,
CONCAT(value, 'UTC', IF(value_ns IS NULL, '', value_ns))
AS PropertyValue,
status AS PropertyStatus,
pidx AS PropertyIndex
FROM archive_datetime_data
WHERE domain_id = InternalDomainID
AND entity_id = InternalEntityID
AND _iversion = IVersion
UNION ALL
#-- text properties
SELECT
property_id AS InternalPropertyID,
( SELECT id FROM entity_ids WHERE internal_id = property_id ) AS PropertyID,
value AS PropertyValue,
status AS PropertyStatus,
pidx AS PropertyIndex
FROM archive_text_data
WHERE domain_id = InternalDomainID
AND entity_id = InternalEntityID
AND _iversion = IVersion
UNION ALL
#-- enum properties
SELECT
property_id AS InternalPropertyID,
( SELECT id FROM entity_ids WHERE internal_id = property_id ) AS PropertyID,
value AS PropertyValue,
status AS PropertyStatus,
pidx AS PropertyIndex
FROM archive_enum_data
WHERE domain_id = InternalDomainID
AND entity_id = InternalEntityID
AND _iversion = IVersion
UNION ALL
#-- reference properties
SELECT
property_id AS InternalPropertyID,
( SELECT id FROM entity_ids WHERE internal_id = property_id ) AS PropertyID,
IF(value_iversion IS NULL,
IF(status = "REPLACEMENT",
value,
( SELECT id FROM entity_ids WHERE internal_id = value )),
-- make it "value@version" if necessary
CONCAT(
( SELECT id FROM entity_ids WHERE internal_id = value ),
"@", _get_version(value, value_iversion)))
AS PropertyValue,
status AS PropertyStatus,
pidx AS PropertyIndex
FROM archive_reference_data
WHERE domain_id = InternalDomainID
AND entity_id = InternalEntityID
AND _iversion = IVersion
UNION ALL
#-- null properties
SELECT
property_id AS InternalPropertyID,
( SELECT id FROM entity_ids WHERE internal_id = property_id ) AS PropertyID,
NULL AS PropertyValue,
status AS PropertyStatus,
pidx AS PropertyIndex
FROM archive_null_data WHERE domain_id = InternalDomainID
AND entity_id = InternalEntityID
AND _iversion = IVersion
UNION ALL
#-- name properties
SELECT
property_id AS InternalPropertyID,
( SELECT id FROM entity_ids WHERE internal_id = property_id ) AS PropertyID,
value AS PropertyValue,
status AS PropertyStatus,
pidx AS PropertyIndex
FROM archive_name_data
WHERE domain_id = InternalDomainID
AND entity_id = InternalEntityID
AND property_id != 20
AND _iversion = IVersion;
LEAVE retrieveEntityPropertiesBody;
END IF;
END IF;
#-- double properties
SELECT
property_id AS InternalPropertyID,
( SELECT id FROM entity_ids WHERE internal_id = property_id ) AS PropertyID,
value AS PropertyValue,
status AS PropertyStatus,
pidx AS PropertyIndex
FROM double_data
WHERE domain_id = InternalDomainID
AND entity_id = InternalEntityID
UNION ALL
#-- integer properties
SELECT
property_id AS InternalPropertyID,
( SELECT id FROM entity_ids WHERE internal_id = property_id ) AS PropertyID,
value AS PropertyValue,
status AS PropertyStatus,
pidx AS PropertyIndex
FROM integer_data
WHERE domain_id = InternalDomainID
AND entity_id = InternalEntityID
UNION ALL
#-- date properties
SELECT
property_id AS InternalPropertyID,
( SELECT id FROM entity_ids WHERE internal_id = property_id ) AS PropertyID,
CONCAT(value, '.NULL.NULL') AS PropertyValue,
status AS PropertyStatus,
pidx AS PropertyIndex
FROM date_data
WHERE domain_id = InternalDomainID
AND entity_id = InternalEntityID
UNION ALL
#-- datetime properties
SELECT
property_id AS InternalPropertyID,
( SELECT id FROM entity_ids WHERE internal_id = property_id ) AS PropertyID,
CONCAT(value, 'UTC', IF(value_ns IS NULL, '', value_ns))
AS PropertyValue,
status AS PropertyStatus,
pidx AS PropertyIndex FROM datetime_data
WHERE domain_id = InternalDomainID
AND entity_id = InternalEntityID
UNION ALL
#-- text properties
SELECT
property_id AS InternalPropertyID,
( SELECT id FROM entity_ids WHERE internal_id = property_id ) AS PropertyID,
value AS PropertyValue,
status AS PropertyStatus,
pidx AS PropertyIndex
FROM text_data
WHERE domain_id = InternalDomainID
AND entity_id = InternalEntityID
UNION ALL
#-- enum properties
SELECT
property_id AS InternalPropertyID,
( SELECT id FROM entity_ids WHERE internal_id = property_id ) AS PropertyID,
value AS PropertyValue,
status AS PropertyStatus,
pidx AS PropertyIndex
FROM enum_data
WHERE domain_id = InternalDomainID
AND entity_id = InternalEntityID
UNION ALL
#-- reference properties
SELECT
property_id AS InternalPropertyID,
( SELECT id FROM entity_ids WHERE internal_id = property_id ) AS PropertyID,
IF(value_iversion IS NULL,
IF(status = "REPLACEMENT",
value,
( SELECT id FROM entity_ids WHERE internal_id = value )),
-- make it "value@version" if necessary
CONCAT(
( SELECT id FROM entity_ids WHERE internal_id = value ),
"@", _get_version(value, value_iversion)))
AS PropertyValue,
status AS PropertyStatus,
pidx AS PropertyIndex
FROM reference_data
WHERE domain_id = InternalDomainID
AND entity_id = InternalEntityID
UNION ALL
#-- null properties
SELECT
property_id AS InternalPropertyID,
( SELECT id FROM entity_ids WHERE internal_id = property_id ) AS PropertyID,
NULL AS PropertyValue,
status AS PropertyStatus,
pidx AS PropertyIndex
FROM null_data
WHERE domain_id = InternalDomainID
AND entity_id = InternalEntityID
UNION ALL
#-- name properties
SELECT
property_id AS InternalPropertyID,
( SELECT id FROM entity_ids WHERE internal_id = property_id ) AS PropertyID, value AS PropertyValue,
status AS PropertyStatus,
pidx AS PropertyIndex
FROM name_data
WHERE domain_id = InternalDomainID
AND entity_id = InternalEntityID
AND property_id != 20;
END;
//
delimiter ;