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

CREATE PROCEDURE db_2_0.applyPOV(in sourceSet VARCHAR(255), in targetSet VARCHAR(255), in propertiesTable VARCHAR(255), in refIdsTable VARCHAR(255), in o CHAR(4), in vText VARCHAR(255), in vInt INT, in vDouble DOUBLE, in unit_sig BIGINT, in vDoubleStdUnit DOUBLE, in stdUnit_sig BIGINT, in vDateTime VARCHAR(255), in vDateTimeDotNotation VARCHAR(255), in agg CHAR(3), in pname VARCHAR(255))
POV_LABEL: BEGIN
    DECLARE data TEXT DEFAULT NULL; /*data subselect statement string*/
    DECLARE sTextData VARCHAR(20000) DEFAULT NULL; /*SELECT PREFIX for data subselect plus WHERE CLAUSE for text_data*/
    DECLARE sEnumData VARCHAR(20000) DEFAULT NULL; /*WHERE CLAUSE for enum_data*/
    DECLARE sIntData VARCHAR(20000) DEFAULT NULL; /*WHERE CLAUSE for integer_data*/
    DECLARE sDoubleData VARCHAR(20000) DEFAULT NULL; /*WHERE CLAUSE for double_data*/
    DECLARE sDatetimeData VARCHAR(20000) DEFAULT NULL; /*WHERE CLAUSE for datetime_data*/
    DECLARE sNullData VARCHAR(20000) DEFAULT NULL; /*WHERE CLAUSE for null_data*/
    DECLARE sDateData VARCHAR(20000) DEFAULT NULL; /*WHERE CLAUSE for date_data*/
    DECLARE sRefData VARCHAR(20000) DEFAULT NULL; /*WHERE CLAUSE for reference_data*/
    DECLARE aggValue VARCHAR(255) DEFAULT NULL;
    DECLARE aggValueWhereClause VARCHAR(20000) DEFAULT NULL;
    DECLARE distinctUnits INT DEFAULT 0;
    DECLARE usedStdUnit BIGINT DEFAULT NULL;
    DECLARE keepTabl VARCHAR(255) DEFAULT NULL;

    IF o = '->' THEN
        #-- special case: pure reference property
        call applyRefPOV(sourceSet,targetSet, propertiesTable, refIdsTable);
        LEAVE POV_LABEL;
    ELSEIF o = '0' THEN
        #-- special case: property IS NULL
        #-- generate statement parts
        SET vText = NULL;
        SET sTextData = 'SELECT domain_id, entity_id, property_id FROM `null_data` AS subdata';

    ELSEIF o = '!0' THEN
        #-- special case: property IS NOT NULL
        #-- generate statement parts
        SET vText = NULL;
        SET sTextData = 'SELECT DISTINCT domain_id, entity_id, property_id FROM `text_data` AS subdata WHERE subdata.value IS NOT NULL UNION ALL SELECT DISTINCT domain_id, entity_id, property_id FROM `enum_data` AS subdata WHERE subdata.value IS NOT NULL UNION ALL SELECT DISTINCT domain_id, entity_id, property_id FROM `integer_data` AS subdata WHERE subdata.value IS NOT NULL UNION ALL SELECT DISTINCT domain_id, entity_id, property_id FROM `double_data` AS subdata WHERE subdata.value IS NOT NULL UNION ALL SELECT DISTINCT domain_id, entity_id, property_id FROM `date_data` AS subdata WHERE subdata.value IS NOT NULL UNION ALL SELECT DISTINCT domain_id, entity_id, property_id FROM `datetime_data` AS subdata WHERE subdata.value IS NOT NULL UNION ALL SELECT DISTINCT domain_id, entity_id, property_id FROM `reference_data` AS subdata WHERE subdata.value IS NOT NULL';

    ELSEIF o = "(" or o = "!(" THEN
        SET sTextData = IF(vText IS NULL,' SELECT DISTINCT domain_id, entity_id, property_id FROM `date_data`', IF(vDateTimeDotNotation IS NULL, NULL, CONCAT(' SELECT DISTINCT domain_id, entity_id, property_id FROM `date_data` AS subdata WHERE ',getDateWhereClause(vDateTimeDotNotation,o))));
        SET sDatetimeData = IF(vText IS NULL,' UNION ALL SELECT DISTINCT domain_id, entity_id, property_id FROM `datetime_data`', IF(vDateTime IS NULL, NULL, CONCAT(' UNION ALL SELECT DISTINCT domain_id, entity_id, property_id FROM `datetime_data` AS subdata WHERE ',getDateTimeWhereClause(vDateTime,o))));
        SET vText = NULL;
    ELSEIF agg IS NOT NULL THEN
        #-- find aggregated value (min/max...)

        #-- construct where clause
        SET aggValueWhereClause = CONCAT(getDoubleWhereClause(vDouble, unit_sig, vDoubleStdUnit, stdUnit_sig, o), ' AND ');
        SET aggValueWhereClause = CONCAT(IF(aggValueWhereClause IS NULL, '', aggValueWhereClause), getAggValueWhereClause(sourceSet, propertiesTable));

        #-- construct statement
        SET @aggValueStmtStr = CONCAT('SELECT ',agg,'(subdata.value), ', agg, '(convert_unit(subdata.unit_sig,subdata.value)), COUNT(DISTINCT standard_unit(subdata.unit_sig)), max(standard_unit(subdata.unit_sig)) INTO @sAggValue, @sAggValueConvert, @distinctUnits, @StdUnitSig FROM (SELECT entity_id, property_id, value, unit_sig FROM `integer_data` UNION SELECT entity_id, property_id, value, unit_sig FROM `double_data`) AS subdata WHERE ', aggValueWhereClause);

        #-- run statement
        PREPARE stmtAggValueStmt FROM @aggValueStmtStr;
        EXECUTE stmtAggValueStmt;
        DEALLOCATE PREPARE stmtAggValueStmt;

        SET distinctUnits = @distinctUnits;
        SET aggValue = @sAggValue;

        #-- if distinctUnits = 1, there was exactly one common base unit -> everything is comprehensible. Otherwise, only the numeric value of each entry can be taken into account (which raises a warning).
        IF distinctUnits = 1 THEN
            SET aggValue = @sAggValueConvert;
            SET usedStdUnit = @StdUnitSig;
        ELSE
            call raiseWarning(CONCAT("The filter POV(",IF(pname IS NULL, 'NULL', pname),",",IF(o IS NULL, 'NULL', o),",",IF(vText IS NULL, 'NULL', vText),") with the aggregate function '", agg, "' could not match the values against each other with their units. The values had different base units. Only their numric value had been taken into account." ));
        END IF;

        IF aggValue IS NULL THEN
            SET sTextData = 'SELECT NULL as domain_id, NULL as entity_id, NULL as property_id';
        ELSE
            SET sTextData = '';
            SET sIntData = CONCAT(' UNION ALL SELECT DISTINCT domain_id, entity_id, property_id FROM `integer_data` as subdata WHERE ', getDoubleWhereClause(aggValue, usedStdUnit, aggValue, usedStdUnit, '='));
            SET sDoubleData = CONCAT(' SELECT DISTINCT domain_id, entity_id, property_id FROM `double_data` as subdata WHERE ', getDoubleWhereClause(aggValue, usedStdUnit, aggValue, usedStdUnit, '='));
        END IF;

        SET vText = NULL;
    ELSE
        #-- generate statement parts
        SET sTextData = IF(vText IS NULL, 'SELECT DISTINCT domain_id, entity_id, property_id FROM `text_data`', CONCAT('SELECT DISTINCT domain_id, entity_id, property_id FROM `text_data` AS subdata WHERE subdata.value ',o,' ?'));
        SET sEnumData = IF(vText IS NULL, ' UNION ALL SELECT DISTINCT domain_id, entity_id, property_id FROM `enum_data`', CONCAT(' UNION SELECT DISTINCT domain_id, entity_id, property_id FROM `enum_data` AS subdata WHERE subdata.value ', o, ' ?'));
        SET sIntData = IF(vText IS NULL, ' UNION ALL SELECT DISTINCT subdata.domain_id, subdata.entity_id, subdata.property_id FROM `integer_data` AS subdata', IF(vInt IS NULL AND vDoubleStdUnit IS NULL, NULL, CONCAT(' UNION ALL SELECT DISTINCT domain_id, entity_id, property_id FROM `integer_data` AS subdata WHERE ', getDoubleWhereClause(vInt, unit_sig, vDoubleStdUnit, stdUnit_sig, o))));
        SET sDoubleData = IF(vText IS NULL, ' UNION ALL SELECT DISTINCT subdata.domain_id, subdata.entity_id, subdata.property_id FROM `double_data` AS subdata', IF(vDouble IS NULL, NULL, CONCAT(' UNION ALL SELECT DISTINCT domain_id, entity_id, property_id FROM `double_data` AS subdata WHERE ', getDoubleWhereClause(vDouble,unit_sig,vDoubleStdUnit,stdUnit_sig,o))));
        SET sDatetimeData = IF(vText IS NULL,' UNION ALL SELECT DISTINCT domain_id, entity_id, property_id FROM `datetime_data`', IF(vDateTime IS NULL, NULL, CONCAT(' UNION ALL SELECT DISTINCT domain_id, entity_id, property_id FROM `datetime_data` AS subdata WHERE ',getDateTimeWhereClause(vDateTime,o))));
        SET sDateData = IF(vText IS NULL,' UNION ALL SELECT DISTINCT domain_id, entity_id, property_id FROM `date_data`', IF(vDateTimeDotNotation IS NULL, NULL, CONCAT(' UNION ALL SELECT DISTINCT domain_id, entity_id, property_id FROM `date_data` AS subdata WHERE ',getDateWhereClause(vDateTimeDotNotation,o))));
        SET sRefData = IF(vText IS NULL, ' UNION ALL SELECT DISTINCT domain_id, entity_id, property_id FROM `reference_data`', IF(refIdsTable IS NULL, NULL, CONCAT(' UNION ALL SELECT DISTINCT domain_id, entity_id, property_id FROM `reference_data` AS subdata WHERE EXISTS (SELECT 1 FROM `', refIdsTable, '` AS refIdsTable WHERE subdata.value=refIdsTable.id LIMIT 1)')));
        SET sNullData = IF(vText IS NULL, ' UNION ALL SELECT DISTINCT domain_id, entity_id, property_id FROM `null_data`', NULL);

    END IF;

    SET data = CONCAT('(',sTextData,
                IF(sEnumData IS NULL, '', sEnumData),
                IF(sDoubleData IS NULL, '', sDoubleData),
                IF(sIntData IS NULL, '', sIntData),
                IF(sDatetimeData IS NULL, '', sDatetimeData),
                IF(sDateData IS NULL, '', sDateData),
                IF(sRefData IS NULL, '', sRefData),
                IF(sNullData IS NULL, '', sNullData),
                ')'
            );


    call createTmpTable(keepTabl);

    #-- generate statement from statement parts
    SET @stmtPOVkeepTblStr = CONCAT("INSERT IGNORE INTO `", keepTabl, "` (id) SELECT DISTINCT entity_id AS id FROM ", data, " as data", IF(propertiesTable IS NULL, '', CONCAT(' WHERE EXISTS (Select 1 from `', propertiesTable, '` AS prop WHERE prop.id = data.property_id AND (prop.id2=data.entity_id OR prop.id2=0))')));

    SET @stmtPOVStr = CONCAT(
            IF(targetSet IS NULL,
                CONCAT('DELETE FROM `',sourceSet,'` WHERE NOT EXISTS (SELECT 1 FROM `'),
                CONCAT('INSERT IGNORE INTO `',targetSet,'` (id) SELECT id FROM `',sourceSet,'` WHERE EXISTS (SELECT 1 FROM `')),
            keepTabl,
            '` AS data WHERE data.id=`', sourceSet, '`.`id` LIMIT 1)'
        );


    #-- prepare and execute
    PREPARE stmt3 FROM @stmtPOVStr;
    PREPARE stmtPOVkeepTbl FROM @stmtPOVkeepTblStr;
    IF vText IS NULL THEN
        EXECUTE stmtPOVkeepTbl;
    ELSE
        SET @vText = vText;
        EXECUTE stmtPOVkeepTbl USING @vText, @vText;
    END IF;
    EXECUTE stmt3;
    DEALLOCATE PREPARE stmt3;
    DEALLOCATE PREPARE stmtPOVkeepTbl;

    SELECT @stmtPOVkeepTblStr as applyPOVStmt1, @stmtPOVStr as applyPOVStmt2, keepTabl as applyPOVIntermediateResultSet;


END;
//
DELIMITER ;