Skip to content
Snippets Groups Projects
Select Git revision
  • 00fc3a2ce7e1c2b8291d21abe319f12a5ae68151
  • main default protected
  • f-better-sss-bin-dir
  • dev protected
  • f-remove-dropoffbox
  • f-sss4grpc
  • f-refactor-compose
  • f-real-id
  • f-doip
  • f-filesystem-import
  • henrik-tmp
  • f-filesystem-link
  • f-filesystem-directory
  • f-filesystem-core
  • f-filesystem-cleanup
  • f-string-ids
  • f-filesystem-main
  • f-linkahead-rename-before
  • f-linkahead-rename
  • f-rename-li
  • f-experiment-trino
  • v0.13.0 protected
  • v0.12.3 protected
  • v0.12.2 protected
  • v0.12.1 protected
  • v0.12.0 protected
  • v0.11.0 protected
  • v0.10.0 protected
  • v0.9.0 protected
  • v0.8.1 protected
  • v0.8.0 protected
  • v0.7.3 protected
  • v0.7.2 protected
  • v0.7.1 protected
  • v0.6.0 protected
  • v0.5.0 protected
  • v0.4.0 protected
  • v0.3.0 protected
  • working_sss protected
  • v0.1 protected
40 results

xls_from_csv.py

Blame
  • 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 ;