Select Git revision
xls_from_csv.py
Code owners
Assign users and groups as approvers for specific file changes. Learn more.
insertIsaCache.sql 3.22 KiB
/*
* ** 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>
*
* 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_2_0.insertIsa;
DELIMITER //
/**
* Insert an "is a" relation
*
* This procedure fills the isa_cache table. All passed entities must be existing
* in entities.id.
*
* Parameters
* ==========
*
* c : UNSIGNED
* The child entity
*
* p : UNSIGNED
* The parent entity
*/
CREATE PROCEDURE db_2_0.insertIsa(IN c INT UNSIGNED, IN p INT UNSIGNED)
insert_is_a_proc: BEGIN
INSERT INTO isa_cache (child, parent, rpath) VALUES (c, p, c);
IF p = c THEN
-- Any additional entries would be redundant.
LEAVE insert_is_a_proc;
END IF;
-- Note: also for the next insertions, ignore existing lines with p == c
-- Insert ancestors older than parents:
-- for each supertype of p
-- INSERT (c, supertype, p);
INSERT IGNORE INTO isa_cache SELECT
c
AS child, -- Current child
i.parent
AS parent, -- Existing supertype
IF(p=i.rpath or i.rpath=parent, -- If ancestor=parent or parent = grandparent:
p, -- New parent (directly)
concat(p, ">", i.rpath)) -- Else "p>super.rpath"
AS rpath
FROM isa_cache AS i WHERE i.child = p AND i.child != i.parent; -- Select rows with supertype
-- Propagate to descendants:
-- for each subtype of c: insert each supertype of p
INSERT IGNORE INTO isa_cache SELECT
l.child, -- Descendant as found in isa_cache
r.parent, -- Ancestor as found in isa_cache
if(l.rpath=l.child and r.rpath=c, -- if distance=1 for left and right:
c, -- rpath = current child
concat(if(l.rpath=l.child, -- if dist=1 for descendant:
c, -- rpath starts with c
concat(l.rpath, '>', c)), -- rpath starts with "desc.rpath > c"
if(r.rpath=c, -- if dist=1 for ancestor
'', -- rpath is finished
concat('>', r.rpath)))) -- rpath continuees with " > ancest.rpath"
AS rpath
FROM
isa_cache as l INNER JOIN isa_cache as r
ON (l.parent = c AND c = r.child AND l.child != l.parent); -- Left: descendants of c, right: ancestors
END;
//
DELIMITER ;