Select Git revision
deleteEntityProperties.sql
-
Timm Fitschen authoredTimm Fitschen authored
Code owners
Assign users and groups as approvers for specific file changes. Learn more.
deleteEntityProperties.sql 8.38 KiB
/*
* 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,2023 IndiScale GmbH <info@indiscale.com>
* Copyright (C) 2020,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.deleteEntityProperties;
delimiter //
/*
* Delete all properties of an entity (i.e. remove them from the *_data tables
* and the isa_cache table).
*
* Parameters
* ==========
*
* EntityID : VARCHAR(255)
* The entity's id.
*/
CREATE PROCEDURE db_5_0.deleteEntityProperties(in EntityID VARCHAR(255))
BEGIN
DECLARE IVersion INT UNSIGNED DEFAULT NULL;
DECLARE InternalEntityID INT UNSIGNED DEFAULT NULL;
SELECT internal_id INTO InternalEntityID from entity_ids WHERE id = EntityID;
CALL deleteIsa(InternalEntityID);
IF is_feature_config("ENTITY_VERSIONING", "ENABLED") THEN
SELECT max(e._iversion) INTO IVersion -- What's the latest version?
FROM entity_version AS e
WHERE e.entity_id = InternalEntityID;
-- Copy the rows from *_data to archive_*_data ---------------------
INSERT INTO archive_reference_data (domain_id, entity_id,
property_id, value, value_iversion, status, pidx, _iversion)
SELECT domain_id, entity_id, property_id, value, value_iversion,
status, pidx, IVersion AS _iversion
FROM reference_data
WHERE (domain_id = 0 AND entity_id = InternalEntityID)
OR domain_id = InternalEntityID;
INSERT INTO archive_null_data (domain_id, entity_id,
property_id, status, pidx, _iversion)
SELECT domain_id, entity_id, property_id, status,
pidx, IVersion AS _iversion
FROM null_data
WHERE (domain_id = 0 AND entity_id = InternalEntityID)
OR domain_id = InternalEntityID;
INSERT INTO archive_text_data (domain_id, entity_id,
property_id, value, status, pidx, _iversion)
SELECT domain_id, entity_id, property_id, value, status,
pidx, IVersion AS _iversion
FROM text_data
WHERE (domain_id = 0 AND entity_id = InternalEntityID)
OR domain_id = InternalEntityID;
INSERT INTO archive_name_data (domain_id, entity_id,
property_id, value, status, pidx, _iversion)
SELECT domain_id, entity_id, property_id, value, status,
pidx, IVersion AS _iversion
FROM name_data
WHERE (domain_id = 0 AND entity_id = InternalEntityID)
OR domain_id = InternalEntityID;
INSERT INTO archive_enum_data (domain_id, entity_id,
property_id, value, status, pidx, _iversion)
SELECT domain_id, entity_id, property_id, value, status,
pidx, IVersion AS _iversion
FROM enum_data
WHERE (domain_id = 0 AND entity_id = InternalEntityID)
OR domain_id = InternalEntityID;
INSERT INTO archive_integer_data (domain_id, entity_id,
property_id, value, status, pidx, _iversion, unit_sig)
SELECT domain_id, entity_id, property_id, value, status,
pidx, IVersion AS _iversion, unit_sig
FROM integer_data
WHERE (domain_id = 0 AND entity_id = InternalEntityID)
OR domain_id = InternalEntityID;
INSERT INTO archive_double_data (domain_id, entity_id,
property_id, value, status, pidx, _iversion, unit_sig)
SELECT domain_id, entity_id, property_id, value, status,
pidx, IVersion AS _iversion, unit_sig
FROM double_data
WHERE (domain_id = 0 AND entity_id = InternalEntityID)
OR domain_id = InternalEntityID;
INSERT INTO archive_datetime_data (domain_id, entity_id,
property_id, value, value_ns, status, pidx, _iversion)
SELECT domain_id, entity_id, property_id, value, value_ns,
status, pidx, IVersion AS _iversion
FROM datetime_data
WHERE (domain_id = 0 AND entity_id = InternalEntityID)
OR domain_id = InternalEntityID;
INSERT INTO archive_date_data (domain_id, entity_id,
property_id, value, status, pidx, _iversion)
SELECT domain_id, entity_id, property_id, value, status,
pidx, IVersion AS _iversion
FROM date_data
WHERE (domain_id = 0 AND entity_id = InternalEntityID)
OR domain_id = InternalEntityID;
INSERT INTO archive_name_overrides (domain_id, entity_id,
property_id, name, _iversion)
SELECT domain_id, entity_id, property_id, name,
IVersion AS _iversion
FROM name_overrides
WHERE (domain_id = 0 AND entity_id = InternalEntityID)
OR domain_id = InternalEntityID;
INSERT INTO archive_desc_overrides (domain_id, entity_id,
property_id, description, _iversion)
SELECT domain_id, entity_id, property_id, description,
IVersion AS _iversion
FROM desc_overrides
WHERE (domain_id = 0 AND entity_id = InternalEntityID)
OR domain_id = InternalEntityID;
INSERT INTO archive_data_type (domain_id, entity_id,
property_id, datatype, _iversion)
SELECT domain_id, entity_id, property_id, datatype,
IVersion AS _iversion
FROM data_type
WHERE (domain_id = 0 AND entity_id = InternalEntityID)
OR domain_id = InternalEntityID;
INSERT INTO archive_collection_type (domain_id, entity_id,
property_id, collection, _iversion)
SELECT domain_id, entity_id, property_id, collection,
IVersion AS _iversion
FROM collection_type
WHERE (domain_id = 0 AND entity_id = InternalEntityID)
OR domain_id = InternalEntityID;
INSERT INTO archive_query_template_def (id, definition, _iversion)
SELECT id, definition, IVersion AS _iversion
FROM query_template_def
WHERE id = InternalEntityID;
END IF;
DELETE FROM reference_data
where (domain_id=0 AND entity_id=InternalEntityID) OR domain_id=InternalEntityID;
DELETE FROM null_data
where (domain_id=0 AND entity_id=InternalEntityID) OR domain_id=InternalEntityID;
DELETE FROM text_data
where (domain_id=0 AND entity_id=InternalEntityID) OR domain_id=InternalEntityID;
DELETE FROM name_data
where (domain_id=0 AND entity_id=InternalEntityID) OR domain_id=InternalEntityID;
DELETE FROM enum_data
where (domain_id=0 AND entity_id=InternalEntityID) OR domain_id=InternalEntityID;
DELETE FROM integer_data
where (domain_id=0 AND entity_id=InternalEntityID) OR domain_id=InternalEntityID;
DELETE FROM double_data
where (domain_id=0 AND entity_id=InternalEntityID) OR domain_id=InternalEntityID;
DELETE FROM datetime_data
where (domain_id=0 AND entity_id=InternalEntityID) OR domain_id=InternalEntityID;
DELETE FROM date_data
where (domain_id=0 AND entity_id=InternalEntityID) OR domain_id=InternalEntityID;
DELETE FROM name_overrides
WHERE (domain_id=0 AND entity_id=InternalEntityID) OR domain_id=InternalEntityID;
DELETE FROM desc_overrides
WHERE (domain_id=0 AND entity_id=InternalEntityID) OR domain_id=InternalEntityID;
DELETE FROM data_type
WHERE (domain_id=0 AND entity_id=InternalEntityID) OR domain_id=InternalEntityID;
DELETE FROM collection_type
WHERE (domain_id=0 AND entity_id=InternalEntityID) OR domain_id=InternalEntityID;
DELETE FROM query_template_def WHERE id=InternalEntityID;
END;
//
delimiter ;