insertIsaCache.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>
 *
 * 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 ;