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

DROP PROCEDURE IF EXISTS db_5_0.initEntity;
DELIMITER //

/**
 * Insert the specified (by `ename`, `enameLike`, `enameRegexp` or `eid`) Entities into `resultset`.
 *
 * If `versioned` is `TRUE`, also add archived entities (for example if the name was changed in the
 * past).
 */
CREATE PROCEDURE db_5_0.initEntity(in eid VARCHAR(255), in ename VARCHAR(255),
                                   in enameLike VARCHAR(255), in enameRegexp VARCHAR(255),
                                   in resultset VARCHAR(255), in versioned BOOLEAN)
initEntityLabel: BEGIN
    DECLARE select_columns VARCHAR(255) DEFAULT '` (id) SELECT entity_id FROM name_data ';
    SET @initEntityStmtStr = NULL;

    -- Prepare a statement which resolves the name or pattern to ids. The ids
    -- are collected in a temporary table (resultset).
    IF versioned IS TRUE THEN
        SET select_columns = '` (id, _iversion) SELECT entity_id, _get_head_iversion(entity_id) FROM name_data ';
    END IF;
    IF ename IS NOT NULL THEN
        SET @initEntityStmtStr = CONCAT(
            'INSERT IGNORE INTO `',
            resultset,
            select_columns,
            'WHERE value=?; ');
        SET @query_param = ename;
    ELSEIF enameLike IS NOT NULL THEN
        SET @initEntityStmtStr = CONCAT(
            'INSERT IGNORE INTO `',
            resultset,
            select_columns,
            'WHERE value LIKE ?;');
        SET @query_param = enameLike;
    ELSEIF enameRegexp IS NOT NULL THEN 
        SET @initEntityStmtStr = CONCAT(
            'INSERT IGNORE INTO `',
            resultset,
            select_columns,
            'WHERE value REGEXP ?;');
        SET @query_param = enameRegexp;
    END IF;

    -- execute the statement
    IF @initEntityStmtStr IS NOT NULL THEN
        PREPARE initEntityStmt FROM @initEntityStmtStr;
        EXECUTE initEntityStmt USING @query_param;
        DEALLOCATE PREPARE initEntityStmt;
    END IF;

    IF eid IS NOT NULL THEN
        -- add an explicitely given id to the resultset (if it exists)
        SET @initEntityStmtStr = CONCAT(
            'INSERT IGNORE INTO `',
            resultset,
            IF(versioned,
                '` (id, _iversion) SELECT eids.internal_id, _get_head_iversion(eids.internal_id) ',
                '` (id) SELECT eids.internal_id '),
            'FROM entity_ids AS eids WHERE eids.id=?;');
        SET @query_param = eid;
        PREPARE initEntityStmt FROM @initEntityStmtStr;
        EXECUTE initEntityStmt USING @query_param;
        DEALLOCATE PREPARE initEntityStmt;
    END IF;


    -- ################# VERSIONING #####################
    -- Same as above, but from `archive_name_data` instead of `name_data`.
    IF versioned IS TRUE THEN
        SET select_columns = '` (id, _iversion) SELECT entity_id, _iversion FROM archive_name_data ';
        IF ename IS NOT NULL THEN
            SET @initEntityStmtStr = CONCAT(
                'INSERT IGNORE INTO `',
                resultset,
                select_columns,
                'WHERE value=?; ');
            SET @query_param = ename;
        ELSEIF enameLike IS NOT NULL THEN
            SET @initEntityStmtStr = CONCAT(
                'INSERT IGNORE INTO `',
                resultset,
                select_columns,
                'WHERE value LIKE ?;');
            SET @query_param = enameLike;
        ELSEIF enameRegexp IS NOT NULL THEN
            SET @initEntityStmtStr = CONCAT(
                'INSERT IGNORE INTO `',
                resultset,
                'WHERE value REGEXP ?;');
            SET @query_param = enameRegexp;
        END IF;

        -- execute the statement
        IF @initEntityStmtStr IS NOT NULL THEN
            PREPARE initEntityStmt FROM @initEntityStmtStr;
            EXECUTE initEntityStmt USING @query_param;
            DEALLOCATE PREPARE initEntityStmt;
        END IF;
    END IF;
    -- ##################################################


    IF @initEntityStmtStr IS NOT NULL THEN
        call getChildren(resultset, versioned);
    END IF;

END;
//
DELIMITER ;




DROP PROCEDURE IF EXISTS db_5_0.applyRole;