getDependentEntities.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
 * Copyright (C) 2023 IndiScale GmbH <www.indiscale.com>
 * Copyright (C) 2023 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/>.
 *
 */


DROP PROCEDURE IF EXISTS db_5_0.getDependentEntities;
delimiter //

/*
 * Return all entities which either reference the given entity, use the given
 * reference as data type, or are direct children of the given entity.
 *
 * This function used to make sure that no entity can be deleted which
 * is still needed by others.
 *
 * Parameters
 * ----------
 * EntityID : VARCHAR(255)
 *    The entity id.
 *
 * ResultSet
 * ---------
 * EntityID : VARCHAR(255)
 *
 */
CREATE PROCEDURE db_5_0.getDependentEntities(in EntityID VARCHAR(255))
BEGIN

    DECLARE InternalEntityID INT UNSIGNED DEFAULT NULL;

    DROP TEMPORARY TABLE IF EXISTS referring;
    CREATE TEMPORARY TABLE referring (
        id INT UNSIGNED UNIQUE
    );

    SELECT internal_id INTO InternalEntityID from entity_ids WHERE id = EntityID;

    INSERT IGNORE INTO referring (id) SELECT entity_id FROM reference_data WHERE (value=InternalEntityID OR property_id=InternalEntityID) AND domain_id=0 AND entity_id!=InternalEntityID;
    INSERT IGNORE INTO referring (id) SELECT domain_id FROM reference_data WHERE (value=InternalEntityID OR property_id=InternalEntityID) AND domain_id!=InternalEntityID AND entity_id!=InternalEntityID AND domain_id!=0;

    INSERT IGNORE INTO referring (id) SELECT entity_id FROM text_data WHERE property_id=InternalEntityID AND domain_id=0 AND entity_id!=InternalEntityID;
    INSERT IGNORE INTO referring (id) SELECT domain_id FROM text_data WHERE property_id=InternalEntityID AND domain_id!=InternalEntityID AND entity_id!=InternalEntityID AND domain_id!=0;

    INSERT IGNORE INTO referring (id) SELECT entity_id FROM enum_data WHERE property_id=InternalEntityID AND domain_id=0 AND entity_id!=InternalEntityID;
    INSERT IGNORE INTO referring (id) SELECT domain_id FROM enum_data WHERE property_id=InternalEntityID AND domain_id!=InternalEntityID AND entity_id!=InternalEntityID AND domain_id!=0;

    INSERT IGNORE INTO referring (id) SELECT entity_id FROM name_data WHERE property_id=InternalEntityID AND domain_id=0 AND entity_id!=InternalEntityID;
    INSERT IGNORE INTO referring (id) SELECT domain_id FROM name_data WHERE property_id=InternalEntityID AND domain_id!=InternalEntityID AND entity_id!=InternalEntityID AND domain_id!=0;

    INSERT IGNORE INTO referring (id) SELECT entity_id FROM integer_data WHERE property_id=InternalEntityID AND domain_id=0 AND entity_id!=InternalEntityID;
    INSERT IGNORE INTO referring (id) SELECT domain_id FROM integer_data WHERE property_id=InternalEntityID AND domain_id!=InternalEntityID AND entity_id!=InternalEntityID AND domain_id!=0;

    INSERT IGNORE INTO referring (id) SELECT entity_id FROM double_data WHERE property_id=InternalEntityID AND domain_id=0 AND entity_id!=InternalEntityID;
    INSERT IGNORE INTO referring (id) SELECT domain_id FROM double_data WHERE property_id=InternalEntityID AND domain_id!=InternalEntityID AND entity_id!=InternalEntityID AND domain_id!=0;

    INSERT IGNORE INTO referring (id) SELECT entity_id FROM datetime_data WHERE property_id=InternalEntityID AND domain_id=0 AND entity_id!=InternalEntityID;
    INSERT IGNORE INTO referring (id) SELECT domain_id FROM datetime_data WHERE property_id=InternalEntityID AND domain_id!=InternalEntityID AND entity_id!=InternalEntityID AND domain_id!=0;

    INSERT IGNORE INTO referring (id) SELECT entity_id FROM date_data WHERE property_id=InternalEntityID AND domain_id=0 AND entity_id!=InternalEntityID;
    INSERT IGNORE INTO referring (id) SELECT domain_id FROM date_data WHERE property_id=InternalEntityID AND domain_id!=InternalEntityID AND entity_id!=InternalEntityID AND domain_id!=0;

    INSERT IGNORE INTO referring (id) SELECT entity_id FROM null_data WHERE property_id=InternalEntityID AND domain_id=0 AND entity_id!=InternalEntityID;
    INSERT IGNORE INTO referring (id) SELECT domain_id FROM null_data WHERE property_id=InternalEntityID AND domain_id!=InternalEntityID AND entity_id!=InternalEntityID AND domain_id!=0;

    INSERT IGNORE INTO referring (id) SELECT entity_id from data_type WHERE datatype=InternalEntityID AND domain_id=0 AND entity_id!=InternalEntityID;
    INSERT IGNORE INTO referring (id) SELECT domain_id from data_type WHERE datatype=InternalEntityID;

    INSERT IGNORE INTO referring (id) SELECT child FROM isa_cache WHERE parent = InternalEntityID AND rpath = child;

    SELECT e.id FROM referring AS r LEFT JOIN entity_ids AS e ON r.id = e.internal_id WHERE r.id!=0 AND e.internal_id!=InternalEntityID;

    DROP TEMPORARY TABLE referring;

END;
//
delimiter ;