deleteIsaCache.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
 */
SET GLOBAL log_bin_trust_function_creators = 1;

DROP PROCEDURE IF EXISTS db_2_0.deleteIsa;
DELIMITER //

/* Delete "is a" relations from the given entity towards ancestors.

Note that relations towards descendants are not deleted (they probably should have been deleted
before).

After this procedure, there are no more entries in `isa_cache`, where the
parameter entity is a child or inside the rpath.

Parameters
==========

EntityID : UNSIGNED
Child entity for which all parental relations should be deleted.
*/
CREATE PROCEDURE db_2_0.deleteIsa(IN EntityID INT UNSIGNED)
BEGIN
    DECLARE IVersion INT UNSIGNED DEFAULT NULL;

    IF is_feature_config("ENTITY_VERSIONING", "ENABLED") THEN
        SELECT max(_iversion) INTO IVersion
            FROM entity_version
            WHERE entity_id = EntityID;

        -- move to archive_isa before deleting
        INSERT IGNORE INTO archive_isa (child, child_iversion, parent, direct)
            SELECT e.child, IVersion AS child_iversion, e.parent, rpath = EntityID
            FROM isa_cache AS e
            WHERE e.child = EntityID;
    END IF;

    DELETE FROM isa_cache
        WHERE child = EntityID
        OR rpath = EntityID
        OR rpath LIKE concat('%>', EntityID)
        OR rpath LIKE concat('%>', EntityID, '>%');

END;
//


 
DELIMITER ;