applyBackReference.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
 *
 * 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.applyBackReference;
DELIMITER //

CREATE PROCEDURE db_2_0.applyBackReference(in sourceSet VARCHAR(255), targetSet VARCHAR(255), in propertiesTable VARCHAR(255), in entitiesTable VARCHAR(255), in subQuery BOOLEAN) 
BEGIN
	DECLARE newTableName VARCHAR(255) DEFAULT NULL;


	IF subQuery IS TRUE THEN
		call registerTempTableName(newTableName);

		SET @createBackRefSubQueryTableStr = CONCAT('CREATE TEMPORARY TABLE `',newTableName,'` ( entity_id INT UNSIGNED NOT NULL, id INT UNSIGNED NOT NULL, CONSTRAINT `',newTableName,'PK` PRIMARY KEY (id, entity_id))');

		PREPARE createBackRefSubQueryTable FROM @createBackRefSubQueryTableStr;
		EXECUTE createBackRefSubQueryTable;
		DEALLOCATE PREPARE createBackRefSubQueryTable;

		SET @backRefSubResultSetStmtStr = CONCAT('INSERT IGNORE INTO `',newTableName,'` (id,entity_id) SELECT entity_id AS id, value AS entity_id FROM `reference_data` AS data WHERE EXISTS (SELECT 1 FROM `',sourceSet,'` AS source WHERE source.id=data.value LIMIT 1)', IF(propertiesTable IS NULL,'',CONCAT(' AND EXISTS (SELECT 1 FROM `',propertiesTable,'` AS p WHERE p.id=data.property_id LIMIT 1)')), IF(entitiesTable IS NULL,'',CONCAT(' AND EXISTS (SELECT 1 FROM `',entitiesTable,'` AS e WHERE e.id=data.entity_id LIMIT 1)')));

		PREPARE backRefSubResultSetStmt FROM @backRefSubResultSetStmtStr;
		EXECUTE backRefSubResultSetStmt;
		DEALLOCATE PREPARE backRefSubResultSetStmt;

		SELECT newTableName as list;	
	ELSE 
	    IF targetSet IS NULL OR sourceSet = targetSet THEN
        	SET @stmtBackRefStr = CONCAT('DELETE FROM `', sourceSet, '` WHERE NOT EXISTS (SELECT 1 FROM `reference_data` AS data WHERE data.value=`', sourceSet, '`.`id`', IF(entitiesTable IS NULL, '', CONCAT(' AND EXISTS (SELECT 1 FROM `', entitiesTable, '` AS e WHERE e.id=data.entity_id LIMIT 1)')), IF(propertiesTable IS NULL, '', CONCAT(' AND EXISTS (SELECT 1 FROM `', propertiesTable, '` AS p WHERE p.id=data.property_id LIMIT 1)')), ')');
    	ELSE
        	SET @stmtBackRefStr = CONCAT('INSERT IGNORE INTO `', targetSet, '` (id) SELECT id FROM `',sourceSet,'` AS source WHERE EXISTS (SELECT 1 FROM `reference_data` AS data WHERE data.value=source.id', IF(entitiesTable IS NULL, '', CONCAT(' AND EXISTS (SELECT 1 FROM `', entitiesTable, '` AS e WHERE e.id=data.entity_id LIMIT 1)')), IF(propertiesTable IS NULL, '', CONCAT(' AND EXISTS (SELECT 1 FROM `', propertiesTable, '` AS p WHERE p.id=data.property_id LIMIT 1)')), ')');
    	END IF;
    
    	PREPARE stmtBackRef FROM @stmtBackRefStr;
    	EXECUTE stmtBackRef;
    	DEALLOCATE PREPARE stmtBackRef;
	END IF;
	
END;
//

DELIMITER ;