Skip to content
Snippets Groups Projects
Select Git revision
  • 7495c0c115f11b804b2c6c494fb9b6f5f0cc9d3f
  • main default protected
  • dev protected
  • f-linkahead-rename
  • f-real-id
  • f-filesystem-import
  • f-filesystem-link
  • f-filesystem-directory
  • f-filesystem-core
  • f-filesystem-cleanup
  • f-filesystem-main
  • f-name
  • keep_changes
  • f-permission-checks-2
  • f-mysql8-tests
  • f-retrieve-history
  • t-distinct-parents
  • v8.1.0
  • v8.0.0
  • v7.0.2
  • v7.0.1
  • v7.0.0
  • v6.0.1
  • v6.0.0
  • v5.0.0
  • v4.1.0
  • v4.0.0
  • v3.0
  • v2.0.30
29 results

initPOV.sql

Blame
  • Code owners
    Assign users and groups as approvers for specific file changes. Learn more.
    initPOV.sql 7.06 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) 2023 IndiScale GmbH <info@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/>.
     */
    DELIMITER //
    
    
    DROP PROCEDURE IF EXISTS db_5_0.initPOVPropertiesTable//
    
    /*
     * Create and initialize a new temporary table.
     *
     * This is used to initialize the POV filtering. The resulting table
     * contains all properties matching the Property component of the POV filter.
     *
     * Parameters
     * ----------
     * PropertyID : VARCHAR(255)
     *     The property's (external) id.
     * PropertyName : VARCHAR(255)
     *     The property's name.
     * sourceSet : VARCHAR(255)
     *     The name of the table which will be filtered by the POV filter.
     *
     * Select
     * ------
     * Tuple (propertiesTable, ... and a lot of debug output)
     */
    CREATE PROCEDURE db_5_0.initPOVPropertiesTable(in PropertyID VARCHAR(255), in PropertyName VARCHAR(255), in sourceSet VARCHAR(255))
    BEGIN
        DECLARE propertiesTable VARCHAR(255) DEFAULT NULL; /*table for property ids*/
        DECLARE replTbl VARCHAR(255) DEFAULT NULL;
        DECLARE ecount INT DEFAULT 0;
        DECLARE t1 BIGINT DEFAULT 0;
        DECLARE t2 BIGINT DEFAULT 0;
        DECLARE t3 BIGINT DEFAULT 0;
        DECLARE t4 BIGINT DEFAULT 0;
        DECLARE t5 BIGINT DEFAULT 0;
        DECLARE t6 BIGINT DEFAULT 0;
    
    
        IF PropertyName is NOT NULL THEN 
            SELECT conv( concat( substring(uid,16,3), substring(uid,10,4), substring(uid,1,8)),16,10) div 10000 - (141427 * 24 * 60 * 60 * 1000) as current_mills INTO t1 from (select uuid() uid) as alias;
            call createTmpTable2(propertiesTable);
    
            -- Fill in all properties (plus the domain id and entity id) named
            -- "PropertyName", be it their default name or their overridden name.
            -- We need *all* properties (mind, the properties of entities not
            -- abstract properties) with the given name. If an abstract
            -- property has the name "person" it might have the name "author" in
            -- the "Article" record type. Now, if I want to collect all properties
            -- of the Article named "author" I need to look into the name_overrides
            -- table.
            SET @initPOVPropertiesTableStmt1 = CONCAT('INSERT IGNORE INTO `', propertiesTable, '` (id, id2, domain) SELECT property_id, entity_id, domain_id from name_overrides WHERE name = ? UNION ALL SELECT entity_id, domain_id, 0 FROM name_data WHERE value = ?;');
            PREPARE stmt FROM @initPOVPropertiesTableStmt1;
            SET @PropertyName = PropertyName;
            EXECUTE stmt USING @PropertyName, @PropertyName;
            SET ecount = ROW_COUNT();
    
            -- fill in all properties with id="PropertyID"
            SELECT conv( concat( substring(uid,16,3), substring(uid,10,4), substring(uid,1,8)),16,10) div 10000 - (141427 * 24 * 60 * 60 * 1000) as current_mills INTO t2 from (select uuid() uid) as alias;
            IF PropertyID IS NOT NULL THEN
                SET @initPOVPropertiesTableStmt2 = CONCAT('INSERT IGNORE INTO `', propertiesTable, '` (id, id2, domain) VALUES (?, 0, 0)');
                PREPARE stmt FROM @initPOVPropertiesTableStmt2;
                SET @PropertyID = PropertyID;
                EXECUTE stmt USING @PropertyID;
                SET ecount = ecount + ROW_COUNT();
            END IF;
    
            -- expand with all children
            SELECT conv( concat( substring(uid,16,3), substring(uid,10,4), substring(uid,1,8)),16,10) div 10000 - (141427 * 24 * 60 * 60 * 1000) as current_mills INTO t3 from (select uuid() uid) as alias;
            IF ecount > 0 THEN
                -- TODO versioned queries
                call getChildren(propertiesTable, False);
            END IF;
    
            -- expand with all replacements
            SELECT conv( concat( substring(uid,16,3), substring(uid,10,4), substring(uid,1,8)),16,10) div 10000 - (141427 * 24 * 60 * 60 * 1000) as current_mills INTO t4 from (select uuid() uid) as alias;
            IF ecount > 0 THEN
                call createTmpTable2(replTbl);
                SET @replTblStmt1 := CONCAT('INSERT IGNORE INTO `',replTbl, '` (id, id2, domain) SELECT r.value as id, r.entity_id as id2, 0 as domain_id FROM reference_data AS r WHERE status="REPLACEMENT" AND domain_id=0 AND EXISTS (SELECT * FROM `', sourceSet, '` AS s WHERE s.id=r.entity_id) AND EXISTS (SELECT * FROM `', propertiesTable, '` AS p WHERE p.domain = 0 AND p.id2=0 AND p.id=r.property_id);');
                PREPARE replStmt1 FROM @replTblStmt1;
                EXECUTE replStmt1;
                DEALLOCATE PREPARE replStmt1;
                SELECT conv( concat( substring(uid,16,3), substring(uid,10,4), substring(uid,1,8)),16,10) div 10000 - (141427 * 24 * 60 * 60 * 1000) as current_mills INTO t5 from (select uuid() uid) as alias;
    
                SET @replTblStmt2 := CONCAT('INSERT IGNORE INTO `', propertiesTable, '` SELECT id, id2, domain FROM `', replTbl, '`;');
                PREPARE replStmt2 FROM @replTblStmt2;
                EXECUTE replStmt2;
                DEALLOCATE PREPARE replStmt2;
                SELECT conv( concat( substring(uid,16,3), substring(uid,10,4), substring(uid,1,8)),16,10) div 10000 - (141427 * 24 * 60 * 60 * 1000) as current_mills INTO t6 from (select uuid() uid) as alias;
            END IF;
        END IF;
        SELECT propertiesTable, t1, t2, t3, t4, t5, t6, @initPOVPropertiesTableStmt1 as initPOVPropertiesTableStmt1, @initPOVPropertiesTableStmt2 as initPOVPropertiesTableStmt2, @replTblStmt1 as replTblStmt1, @replTblStmt2 as replTblStmt2;
    END //
    
    
    
    
    DROP PROCEDURE IF EXISTS db_5_0.initPOVRefidsTable //
    
    /*
     * Create and initialize a new temporary table.
     *
     * This used to initialize the POV filtering. The resulting table
     * contains all entities matching the Value component of the POV filter (i.e.
     * the referenced entities).
     *
     * Parameters
     * ----------
     * PropertyID : VARCHAR(255)
     *     The property's (external) id.
     * PropertyName : VARCHAR(255)
     *     The property's name.
     *
     * Select
     * ------
     * Tuple (refIdsTable)
     */
    CREATE PROCEDURE db_5_0.initPOVRefidsTable(in PropertyID VARCHAR(255), in PropertyName VARCHAR(255))
    BEGIN
        DECLARE refIdsTable VARCHAR(255) DEFAULT NULL; /*table for referenced entity ids*/
    
        #-- for reference properties: the value is interpreted as a record type name.
        IF PropertyName IS NOT NULL THEN
            -- TODO versioned queries
            call createTmpTable(refIdsTable, FALSE);
            call initSubEntity(PropertyID, PropertyName, refIdsTable);
            #-- now, all ids are in the refIdsTable
        END IF;
        SELECT refIdsTable;
    
    END //
    
    DELIMITER ;