initSubEntity.sql

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


DROP PROCEDURE IF EXISTS db_5_0.initSubEntity;

DELIMITER //

/*
 * Initialize a new temporary by loading an entity and all of it's children
 * into the table (i.e. their internal ids).
 *
 * This is used by initPOVRefidsTable and initBackReference for sub-property filtering.
 *
 * Parameters
 * ----------
 * EntityID : VARCHAR(255)
 *     The entity's (external) id.
 * EntityName : VARCHAR(255)
 *     The entity's name.
 * tableName : VARCHAR(255)
 *     The table which is to be initialized.
 */
CREATE PROCEDURE db_5_0.initSubEntity(in EntityID VARCHAR(255), in ename VARCHAR(255), in tableName VARCHAR(255))
BEGIN
    DECLARE ecount INT DEFAULT 0;
    DECLARE op VARCHAR(255) DEFAULT '=';

    IF LOCATE("%", ename) > 0 THEN
        SET op = "LIKE";
    END IF;

    SET @stmtStr = CONCAT('INSERT IGNORE INTO `',
        tableName,
        '` (id) SELECT entity_id FROM name_data WHERE value ',
        op,
        ' ? AND domain_id=0;');

    PREPARE stmt FROM @stmtStr;
    SET @ename = ename;
    EXECUTE stmt USING @ename;
    SET ecount = ROW_COUNT();
    DEALLOCATE PREPARE stmt;

    IF EntityID IS NOT NULL THEN
        SET @stmtStr = CONCAT('INSERT IGNORE INTO `', tableName, '` (id) SELECT internal_id FROM entity_ids WHERE id = ?');
        PREPARE stmt FROM @stmtStr;
        SET @eid = EntityID;
        EXECUTE stmt USING @eid;
        SET ecount = ecount + ROW_COUNT();
        DEALLOCATE PREPARE stmt;
    END IF;

    IF ecount > 0 THEN
        -- TODO versioning
        call getChildren(tableName, False);
    END IF;

END;
//
DELIMITER ;