retrieveEntityProperties.sql

/*
 * ** 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
 */


delimiter //

drop procedure if exists db_5_0.retrieveEntityProperties //

create procedure db_5_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;
    DECLARE InternalEntityID INT UNSIGNED DEFAULT NULL;
    DECLARE InternalDomainID INT UNSIGNED DEFAULT 0;

    SELECT internal_id INTO InternalEntityID from entity_ids WHERE id = EntityID;
    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(InternalEntityID) = Version INTO IsHead;
            ELSE
                SELECT get_head_version(InternalDomainID) = 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
                ( 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
                ( 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
                ( 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
                ( 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
                ( 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
                ( 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
                ( SELECT id FROM entity_ids WHERE internal_id = property_id ) AS PropertyID,
                IF(value_iversion IS NULL, 
                    ( 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
                ( 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
                ( 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
        ( 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
        ( 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
        ( 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
        ( 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
        ( 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
        ( 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
        ( SELECT id FROM entity_ids WHERE internal_id = property_id ) AS PropertyID,
        IF(value_iversion IS NULL,
            ( 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
        ( 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
        ( 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 ;