Select Git revision
Install_develop.rst
-
Joscha Schmiedt authoredJoscha Schmiedt authored
Code owners
Assign users and groups as approvers for specific file changes. Learn more.
retrieveEntityProperties.sql 8.23 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
*
* 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
*/
delimiter //
drop procedure if exists db_2_0.retrieveEntityProperties //
create procedure db_2_0.retrieveEntityProperties(
in DomainID INT UNSIGNED,
in EntityID INT UNSIGNED,
in Version VARBINARY(255))
retrieveEntityPropertiesBody: BEGIN
DECLARE IVersion INT UNSIGNED DEFAULT NULL;
DECLARE IsHead BOOLEAN DEFAULT TRUE;
IF is_feature_config("ENTITY_VERSIONING", "ENABLED") THEN
IF Version IS NOT NULL THEN
IF DomainID = 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 = EntityID AND DomainID = 0)
OR (e.entity_id = DomainID))
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 PropertyID,
value AS PropertyValue,
status AS PropertyStatus,
pidx AS PropertyIndex
FROM archive_double_data
WHERE domain_id = DomainID
AND entity_id = EntityID
AND _iversion = IVersion
UNION ALL
#-- integer properties
SELECT
property_id AS PropertyID,
value AS PropertyValue,
status AS PropertyStatus,
pidx AS PropertyIndex
FROM archive_integer_data
WHERE domain_id = DomainID
AND entity_id = EntityID
AND _iversion = IVersion
UNION ALL
#-- date properties
SELECT
property_id AS PropertyID,
CONCAT(value, '.NULL.NULL') AS PropertyValue,
status AS PropertyStatus,
pidx AS PropertyIndex
FROM archive_date_data
WHERE domain_id = DomainID
AND entity_id = EntityID
AND _iversion = IVersion
UNION ALL
#-- datetime properties
SELECT
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 = DomainID
AND entity_id = EntityID
AND _iversion = IVersion
UNION ALL
#-- text properties
SELECT
property_id AS PropertyID,
value AS PropertyValue,
status AS PropertyStatus,
pidx AS PropertyIndex
FROM archive_text_data
WHERE domain_id = DomainID
AND entity_id = EntityID
AND _iversion = IVersion
UNION ALL
#-- enum properties
SELECT
property_id AS PropertyID,
value AS PropertyValue,
status AS PropertyStatus,
pidx AS PropertyIndex
FROM archive_enum_data
WHERE domain_id = DomainID
AND entity_id = EntityID
AND _iversion = IVersion
UNION ALL
#-- reference properties
SELECT property_id AS PropertyID,
IF(value_iversion IS NULL, value,
CONCAT(value, "@", _get_version(value, value_iversion)))
AS PropertyValue,
status AS PropertyStatus,
pidx AS PropertyIndex
FROM archive_reference_data
WHERE domain_id = DomainID
AND entity_id = EntityID
AND _iversion = IVersion
UNION ALL
#-- null properties
SELECT
property_id AS PropertyID,
NULL AS PropertyValue,
status AS PropertyStatus,
pidx AS PropertyIndex
FROM archive_null_data
WHERE domain_id = DomainID
AND entity_id = EntityID
AND _iversion = IVersion
UNION ALL
#-- name properties
SELECT
property_id AS PropertyID,
value AS PropertyValue,
status AS PropertyStatus,
pidx AS PropertyIndex
FROM archive_name_data
WHERE domain_id = DomainID
AND entity_id = EntityID
AND property_id != 20
AND _iversion = IVersion;
LEAVE retrieveEntityPropertiesBody;
END IF;
END IF;
#-- double properties
SELECT
property_id AS PropertyID,
value AS PropertyValue,
status AS PropertyStatus,
pidx AS PropertyIndex
FROM double_data
WHERE domain_id = DomainID
AND entity_id = EntityID
UNION ALL
#-- integer properties
SELECT
property_id AS PropertyID,
value AS PropertyValue,
status AS PropertyStatus,
pidx AS PropertyIndex
FROM integer_data
WHERE domain_id = DomainID
AND entity_id = EntityID
UNION ALL
#-- date properties
SELECT
property_id AS PropertyID,
CONCAT(value, '.NULL.NULL') AS PropertyValue, status AS PropertyStatus,
pidx AS PropertyIndex
FROM date_data
WHERE domain_id = DomainID
AND entity_id = EntityID
UNION ALL
#-- datetime properties
SELECT
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 = DomainID
AND entity_id = EntityID
UNION ALL
#-- text properties
SELECT
property_id AS PropertyID,
value AS PropertyValue,
status AS PropertyStatus,
pidx AS PropertyIndex
FROM text_data
WHERE domain_id = DomainID
AND entity_id = EntityID
UNION ALL
#-- enum properties
SELECT
property_id AS PropertyID,
value AS PropertyValue,
status AS PropertyStatus,
pidx AS PropertyIndex
FROM enum_data
WHERE domain_id = DomainID
AND entity_id = EntityID
UNION ALL
#-- reference properties
SELECT
property_id AS PropertyID,
IF(value_iversion IS NULL, value,
CONCAT(value, "@", _get_version(value, value_iversion)))
AS PropertyValue,
status AS PropertyStatus,
pidx AS PropertyIndex
FROM reference_data
WHERE domain_id = DomainID
AND entity_id = EntityID
UNION ALL
#-- null properties
SELECT
property_id AS PropertyID,
NULL AS PropertyValue,
status AS PropertyStatus,
pidx AS PropertyIndex
FROM null_data
WHERE domain_id = DomainID
AND entity_id = EntityID
UNION ALL
#-- name properties
SELECT
property_id AS PropertyID,
value AS PropertyValue,
status AS PropertyStatus,
pidx AS PropertyIndex
FROM name_data
WHERE domain_id = DomainID
AND entity_id = EntityID
AND property_id != 20;
END;
//
delimiter ;