Select Git revision
initPOV.sql
-
Timm Fitschen authoredTimm Fitschen authored
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 ;