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

create procedure db_2_0.retrieveEntity(in EntityID INT UNSIGNED, in Version VARBINARY(255))
retrieveEntityBody: BEGIN
    DECLARE FilePath VARCHAR(255) DEFAULT NULL;
    DECLARE FileSize VARCHAR(255) DEFAULT NULL;
    DECLARE FileHash VARCHAR(255) DEFAULT NULL;
    DECLARE DatatypeID INT UNSIGNED DEFAULT NULL;
    DECLARE CollectionName VARCHAR(255) DEFAULT NULL;
    DECLARE VersionSeconds BIGINT UNSIGNED DEFAULT NULL;
    DECLARE VersionNanos INT(10) UNSIGNED DEFAULT NULL;
    DECLARE IsHead BOOLEAN DEFAULT TRUE;
    DECLARE IVersion INT UNSIGNED DEFAULT NULL;


    IF is_feature_config("ENTITY_VERSIONING", "ENABLED") THEN
        IF Version IS NULL OR UPPER(Version) = "HEAD" THEN
            SET Version = get_head_version(EntityID);
        ELSEIF UPPER(LEFT(Version, 5)) = "HEAD~" THEN
            SET IsHead = FALSE;
            SET Version = get_head_relative(EntityID, SUBSTR(Version, 6));
        ELSE
            SELECT get_head_version(EntityID) = Version INTO IsHead;
        END IF;

        SELECT t.seconds, t.nanos
            INTO VersionSeconds, VersionNanos
            FROM entity_version AS e
            INNER JOIN transactions AS t
            ON ( e.srid = t.srid )
            WHERE e.entity_id = EntityID
            AND e.version = Version
            AND e._is_head IS FALSE;

        IF IsHead IS FALSE THEN
            SELECT e._iversion INTO IVersion
                FROM entity_version as e
                WHERE e.entity_id = EntityID
                AND e.version = Version
                AND e._is_head IS FALSE;

            IF IVersion IS NULL THEN
                -- Version does not exist.
                -- RETURN EARLY with empty result set.
                SELECT 0 WHERE 0 = 1;
                LEAVE retrieveEntityBody;
            END IF;

            SELECT path, size, HEX(hash)
                INTO FilePath, FileSize, FileHash
                FROM archive_files
                WHERE file_id = EntityID
                AND _iversion = IVersion
                LIMIT 1;

            SELECT datatype
                INTO DatatypeID
                FROM archive_data_type
                WHERE domain_id = 0
                AND entity_id = 0
                AND property_id = EntityID
                AND _iversion = IVersion
                LIMIT 1;

            SELECT collection
                INTO CollectionName
                FROM archive_collection_type
                WHERE domain_id = 0
                AND entity_id = 0
                AND property_id = EntityID
                AND _iversion = IVersion
                LIMIT 1;

            Select
                (SELECT Name FROM entities WHERE id=DatatypeID) AS Datatype,
                CollectionName AS Collection,
                EntityID AS EntityID,
                e.name AS EntityName,
                e.description AS EntityDesc,
                e.role AS EntityRole,
                FileSize AS FileSize,
                FilePath AS FilePath,
                FileHASh AS FileHASh,
                (SELECT acl FROM entity_acl AS a WHERE a.id = e.acl) AS ACL,
                Version AS Version,
                VersionSeconds AS VersionSeconds,
                VersionNanos AS VersionNanos
            FROM archive_entities AS e
            WHERE e.id = EntityID
            AND e._iversion = IVersion
            LIMIT 1;

            -- RETURN EARLY
            LEAVE retrieveEntityBody;

        END IF;
    END IF;

    Select path, size, hex(hash)
        into FilePath, FileSize, FileHash
        from files
        where file_id = EntityID
        LIMIT 1;

    Select datatype into DatatypeID
        from data_type
        where domain_id=0
        and entity_id=0
        and property_id=EntityID
        LIMIT 1;

    SELECT collection into CollectionName
        from collection_type
        where domain_id=0
        and entity_id=0
        and property_id=EntityID
        LIMIT 1;

    Select
        (Select name from entities where id=DatatypeID) as Datatype,
        CollectionName as Collection,
        EntityID as EntityID,
        e.name as EntityName,
        e.description as EntityDesc,
        e.role as EntityRole,
        FileSize as FileSize,
        FilePath as FilePath,
        FileHash as FileHash,
        (SELECT acl FROM entity_acl as a WHERE a.id = e.acl) as ACL,
        Version as Version,
        VersionSeconds as VersionSeconds,
        VersionNanos as VersionNanos
    from entities e where id = EntityID LIMIT 1;
END;
//


delimiter ;