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
 * Copyright (C) 2020 IndiScale GmbH <info@indiscale.com>
 * Copyright (C) 2020 Timm Fitschen <t.fitschen@indiscale
 *
 * 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 //

/**
 * Select the content of an entity.
 *
 * By default the head is selected, but a specific version may be given.
 *
 * Parameters
 * ----------
 * EntityID
 *   The entity's id.
 * Version
 *   The version id.  In this procedure only, the version may also be given as
 *   `HEAD` for the latest version or as `HEAD~n`, which retrieves the n-th
 *   ancestor of `HEAD`.
 *
 * ResultSet
 * ---------
 * Tuple of (Datatype, Collection, EntityID, EntityName, EntityDesc,
 *           EntityRole, FileSize, FilePath, FileHash, ACL, Version)
 */
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 FileMimetype VARBINARY(255) DEFAULT NULL;
    DECLARE FileStorageId VARBINARY(255) DEFAULT NULL;
    DECLARE FileKey VARBINARY(65525) DEFAULT NULL;
    DECLARE DatatypeID INT UNSIGNED DEFAULT NULL;
    DECLARE CollectionName VARCHAR(255) DEFAULT NULL;
    DECLARE IsHead BOOLEAN DEFAULT TRUE;
    DECLARE IVersion INT UNSIGNED DEFAULT NULL;


    IF is_feature_config("ENTITY_VERSIONING", "ENABLED") THEN
        -- Find out head-ness and version
        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;

        IF IsHead IS FALSE THEN
            SET IVersion=get_iversion(EntityID, Version);

            IF IVersion IS NULL THEN
                -- RETURN EARLY - Version does not exist.
                SELECT 0 FROM entities WHERE 0 = 1;
                LEAVE retrieveEntityBody;
            END IF;

            SELECT path, size, hex(hash), mimetype, file_storage_id, file_key
                INTO FilePath, FileSize, FileHash, FileMimetype, FileStorageId, FileKey
                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;

            -- Final select before returning
            SELECT
                ( SELECT value FROM
                    ( SELECT value FROM name_data
                        WHERE domain_id = 0
                        AND entity_ID = DatatypeID
                        AND property_id = 20
                        UNION SELECT DatatypeID AS value
                    ) AS tmp LIMIT 1 ) AS Datatype,
                CollectionName AS Collection,
                EntityID AS EntityID,
                ( SELECT value FROM archive_name_data
                    WHERE domain_id = 0
                    AND entity_ID = EntityID
                    AND property_id = 20
                    AND _iversion = IVersion
                    -- LIMIT 1 -- TODO Remove this line if all tests pass.
                    ) AS EntityName,
                e.description AS EntityDesc,
                e.role AS EntityRole,
                FileSize AS FileSize,
                FilePath AS FilePath,
                FileHash AS FileHash,
                FileMimetype as FileMimetype,
                FileStorageId as FileStorageId,
                FileKey as FileKey,
                (SELECT acl FROM entity_acl AS a WHERE a.id = e.acl) AS ACL,
                Version AS Version
            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), mimetype, file_storage_id, file_key
        INTO FilePath, FileSize, FileHash, FileMimetype, FileStorageId, FileKey
        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 value FROM name_data
            WHERE domain_id = 0
            AND entity_ID = DatatypeID
            AND property_id = 20 LIMIT 1 ) AS Datatype,
        CollectionName AS Collection,
        EntityID AS EntityID,
        ( SELECT value FROM name_data
            WHERE domain_id = 0
            AND entity_ID = EntityID
            AND property_id = 20 LIMIT 1) AS EntityName,
        e.description AS EntityDesc,
        e.role AS EntityRole,
        FileSize AS FileSize,
        FilePath AS FilePath,
        FileHash AS FileHash,
        FileMimetype as FileMimetype,
        FileStorageId as FileStorageId,
        FileKey as FileKey,
        (SELECT acl FROM entity_acl AS a WHERE a.id = e.acl) AS ACL,
        Version AS Version
    FROM entities e WHERE id = EntityID LIMIT 1;
END;
//


delimiter ;