Select Git revision
insertEntity.sql
-
Timm Fitschen authored
AGPLv3 Veröffentlichung gemäß Dienstanweisung vom 15. August 2018.
Timm Fitschen authoredAGPLv3 Veröffentlichung gemäß Dienstanweisung vom 15. August 2018.
Code owners
Assign users and groups as approvers for specific file changes. Learn more.
applyPOV.sql 25.47 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
*
* 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/>.
*/
/* Documentation including (?) has to be checked by an expert. */
/* Question: What is datetime dot notation? */
DROP PROCEDURE IF EXISTS db_5_0.applyPOV;
DELIMITER //
/**
* Apply a Property-Operator-Value filter to sourceSet.
*
* The result is written to targetSet (non-matching rows are deleted in sourceSet if no targetSet is
* given).
*
* Parameters
* ----------
*
* versioned : boolean
* If True, sourceSet and targetSet have an _iversion column, otherwise that column will be ignored
* (or only HEAD will be inserted into targetSet).
TODO description of sourceSet and targetSet is insufficient!
*/
CREATE PROCEDURE db_5_0.applyPOV(in sourceSet VARCHAR(255), /* (?) Name of the table that the POV will be applied to. This can be a temporary table. */
in targetSet VARCHAR(255), /* (?) Name of the result table of this POV. */
in propertiesTable VARCHAR(255),
in refIdsTable VARCHAR(255), /* (?) Name of a tmp table that contains all ids of children of the value interpreted as entity */
in o CHAR(4), /* The operator for this operation. can be one of:
0 check for "equals NULL"
!0 check for "not NULL"
-> check whether a reference exists
( (?) check for datetime intervals
!( (?) check for being outside of datetime intervals
other operators (all SQL operators (?))
these other operators can be used either with or without an aggregation
to use an aggregation set agg to non-zero
all SQL aggregations can be used
*/
in vText VARCHAR(255), /* (?) the text value to be checked against using operator o */
in vInt INT, /* the integer value to be checked against using operator o */
in vDouble DOUBLE, /* the double value to be checked against using operator o */
in unit_sig BIGINT,
in vDoubleStdUnit DOUBLE, /* The numeric value, converted according to the unit rules. */
in stdUnit_sig BIGINT,
in vDateTime VARCHAR(255),
in vDateTimeDotNotation VARCHAR(255),
in agg CHAR(3), /* an SQL aggregate function or NULL when no aggregation should be used */
in pname VARCHAR(255), /* name of the property, this name is only used for reporting more meaningful warnings */
in versioned BOOLEAN)
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 sNameData VARCHAR(20000) DEFAULT NULL; /*WHERE CLAUSE for name_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;
DECLARE existence_op VARCHAR(255) DEFAULT "EXISTS";
#-- ######### HINT ##############
#-- first the appropriate statement is created which in the end prepared and executed
#-- #############################
IF o = '->' THEN
#-- special case: pure reference property
call applyRefPOV(sourceSet,targetSet, propertiesTable, refIdsTable, versioned);
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';
/* Question: in !0 the query is explicitly checked for NOT NULL. Aren't these NULL entries forgotten here? */
ELSEIF o = '!0' THEN
#-- special case: property IS NOT NULL
#-- generate statement parts
SET vText = NULL;
/* Union of the following tables:
text_data
name_data
enum_data
integer_data
date_data
datetime_data
reference_data
double_data
Notes:
SELECT DISTINCT -> No duplicate values
UNION ALL -> Allow also duplicate values
*/
SET sTextData = CONCAT(
'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 `name_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 -- datetime intervals
IF versioned THEN
SET sTextData = IF(vText IS NULL, CONCAT(
' SELECT DISTINCT domain_id, entity_id, _get_head_iversion(entity_id) ',
'AS _iversion, property_id FROM `date_data` UNION ALL ',
'SELECT DISTINCT domain_id, entity_id, _iversion, property_id FROM `archive_date_data`'),
IF(vDateTimeDotNotation IS NULL, NULL, -- vText and vDateTimeDotNotation are both given
CONCAT(' SELECT DISTINCT domain_id, entity_id, _get_head_iversion(entity_id) ',
'AS _iversion, property_id FROM `date_data` AS subdata WHERE ',
getDateWhereClause(vDateTimeDotNotation, o), ' UNION ALL ',
'SELECT DISTINCT domain_id, entity_id, _iversion, property_id FROM `archive_date_data` ',
'AS subdata WHERE ', getDateWhereClause(vDateTimeDotNotation, o))));
SET sDatetimeData = IF(vText IS NULL, ' UNION ALL SELECT DISTINCT domain_id, entity_id, _get_head_iversion(entity_id) AS _iversion, property_id FROM `datetime_data` UNION ALL SELECT DISTINCT domain_id, entity_id, _iversion, property_id FROM `archive_datetime_data`',
IF(vDateTime IS NULL, NULL,
CONCAT(' UNION ALL SELECT DISTINCT domain_id, entity_id, _get_head_iversion(entity_id) AS _iversion, property_id FROM `datetime_data` AS subdata WHERE ', getDateTimeWhereClause(vDateTime, o), ' UNION ALL SELECT DISTINCT domain_id, entity_id,_iversion, property_id FROM `archive_datetime_data` AS subdata WHERE ', getDateTimeWhereClause(vDateTime, o))));
ELSE -- unversioned
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))));
END IF;
SET vText = NULL;
ELSEIF agg IS NOT NULL THEN
IF versioned THEN
SELECT 1 FROM versioned_agg_pov_filter_not_implemented;
END IF;
#-- 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 ALL 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
IF versioned THEN
SET sTextData = IF(vText IS NULL,
'SELECT DISTINCT domain_id, entity_id, _get_head_iversion(entity_id) AS _iversion, property_id FROM `text_data` UNION ALL SELECT DISTINCT domain_id, entity_id, _iversion, property_id FROM `archive_text_data` ',
CONCAT( 'SELECT DISTINCT domain_id, entity_id, _get_head_iversion(entity_id) AS _iversion, property_id ',
'FROM `text_data` AS subdata WHERE subdata.value ', o,' ? ',
'UNION ALL SELECT DISTINCT domain_id, entity_id, _iversion, property_id ',
'FROM `archive_text_data` AS subdata WHERE subdata.value ', o, '?'
));
SET sNameData = IF(vText IS NULL, ' UNION ALL SELECT DISTINCT domain_id, entity_id, _get_head_iversion(entity_id) AS _iversion, property_id FROM `name_data` UNION ALL SELECT DISTINCT domain_id, entity_id, _iversion, property_id FROM `archive_name_data` ', CONCAT(' UNION ALL SELECT DISTINCT domain_id, entity_id, _get_head_iversion(entity_id) AS _iversion, property_id FROM `name_data` AS subdata WHERE subdata.value ', o, ' ? UNION ALL SELECT DISTINCT domain_id, entity_id, _iversion, property_id FROM `archive_name_data` AS subdata WHERE subdata.value ', o, '?'));
SET sEnumData = IF(vText IS NULL, ' UNION ALL SELECT DISTINCT domain_id, entity_id, _get_head_iversion(entity_id) AS _iversion, property_id FROM `enum_data` UNION ALL SELECT DISTINCT domain_id, entity_id, _iversion, property_id FROM `archive_enum_data` ', CONCAT(' UNION ALL SELECT DISTINCT domain_id, entity_id, _get_head_iversion(entity_id) AS _iversion, property_id FROM `enum_data` AS subdata WHERE subdata.value ', o, ' ? UNION ALL SELECT DISTINCT domain_id, entity_id, _iversion, property_id FROM `archive_enum_data` AS subdata WHERE subdata.value ', o, '?'));
IF o = "!=" AND refIdsTable IS NOT NULL THEN
SET existence_op = "NOT EXISTS";
END IF;
SET sRefData = IF(vText IS NULL,
' UNION ALL SELECT DISTINCT domain_id, entity_id, _get_head_iversion(entity_id) AS _iversion, property_id FROM `reference_data` UNION ALL SELECT DISTINCT domain_id, entity_id, _iversion, property_id FROM `archive_reference_data`',
IF(refIdsTable IS NULL,
NULL,
CONCAT(' UNION ALL SELECT DISTINCT domain_id, entity_id, _get_head_iversion(entity_id) AS _iversion, property_id FROM `reference_data` AS subdata WHERE ', existence_op, ' (SELECT 1 FROM `', refIdsTable, '` AS refIdsTable WHERE subdata.value=refIdsTable.id LIMIT 1) AND subdata.status != "REPLACEMENT" UNION ALL SELECT DISTINCT domain_id, entity_id, _iversion, property_id FROM `archive_reference_data` AS subdata WHERE ', existence_op, ' (SELECT 1 FROM `', refIdsTable, '` AS refIdsTable WHERE subdata.value=refIdsTable.id LIMIT 1) AND subdata.status != "REPLACEMENT"')));
SET sDoubleData = IF(vText IS NULL, ' UNION ALL SELECT DISTINCT subdata.domain_id, subdata.entity_id, _get_head_iversion(subdata.entity_id) AS _iversion, subdata.property_id FROM `double_data` AS subdata UNION ALL SELECT DISTINCT domain_id, entity_id, _iversion, property_id FROM `archive_double_data` ', IF(vDouble IS NULL, NULL, CONCAT(' UNION ALL SELECT DISTINCT domain_id, entity_id, _get_head_iversion(entity_id), property_id FROM `double_data` AS subdata WHERE ', getDoubleWhereClause(vDouble,unit_sig,vDoubleStdUnit,stdUnit_sig,o), ' UNION ALL SELECT DISTINCT domain_id, entity_id, _iversion, property_id FROM `archive_double_data` AS subdata WHERE ', getDoubleWhereClause(vDouble, unit_sig, vDoubleStdUnit, stdUnit_sig, o))));
SET sIntData = IF(vText IS NULL, ' UNION ALL SELECT DISTINCT subdata.domain_id, subdata.entity_id, _get_head_iversion(subdata.entity_id) AS _iversion, subdata.property_id FROM `integer_data` AS subdata UNION ALL SELECT DISTINCT domain_id, entity_id, _iversion, property_id FROM `archive_integer_data`', IF(vInt IS NULL AND vDoubleStdUnit IS NULL, NULL, CONCAT(' UNION ALL SELECT DISTINCT domain_id, entity_id, _get_head_iversion(entity_id) AS _iversion, property_id FROM `integer_data` AS subdata WHERE ', getDoubleWhereClause(vInt, unit_sig, vDoubleStdUnit, stdUnit_sig, o), ' UNION ALL SELECT DISTINCT domain_id, entity_id, _iversion, property_id FROM `archive_integer_data` AS subdata WHERE ', getDoubleWhereClause(vInt, unit_sig, vDoubleStdUnit, stdUnit_sig, o))));
SET sDatetimeData = IF(vText IS NULL,' UNION ALL SELECT DISTINCT domain_id, entity_id, _get_head_iversion(entity_id) AS _iversion, property_id FROM `datetime_data` UNION ALL SELECT DISTINCT domain_id, entity_id, _iversion, property_id FROM `archive_datetime_data`', IF(vDateTime IS NULL, NULL, CONCAT(' UNION ALL SELECT DISTINCT domain_id, entity_id, _get_head_iversion(entity_id) AS _iversion, property_id FROM `datetime_data` AS subdata WHERE ',getDateTimeWhereClause(vDateTime,o), ' UNION ALL SELECT DISTINCT domain_id, entity_id, _iversion, property_id FROM `archive_datetime_data` AS subdata WHERE ',getDateTimeWhereClause(vDateTime,o))));
SET sDateData = IF(vText IS NULL,' UNION ALL SELECT DISTINCT domain_id, entity_id, _get_head_iversion(entity_id) AS _iversion, property_id FROM `date_data` UNION ALL SELECT DISTINCT domain_id, entity_id, _iversion, property_id FROM `archive_date_data`', IF(vDateTimeDotNotation IS NULL, NULL, CONCAT(' UNION ALL SELECT DISTINCT domain_id, entity_id, _get_head_iversion(entity_id) AS _iversion, property_id FROM `date_data` AS subdata WHERE ', getDateWhereClause(vDateTimeDotNotation,o), ' UNION ALL SELECT DISTINCT domain_id, entity_id, _iversion, property_id FROM `archive_date_data` AS subdata WHERE ', getDateWhereClause(vDateTimeDotNotation,o))));
SET sNullData = IF(vText IS NULL, ' UNION ALL SELECT DISTINCT domain_id, entity_id, _get_head_iversion(entity_id) AS _iversion, property_id FROM `null_data` UNION ALL SELECT DISTINCT domain_id, entity_id, _iversion, property_id FROM `archive_null_data`', NULL);
-- unversioned query --------------------------------------------
ELSE
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 sNameData = IF(vText IS NULL, ' UNION ALL SELECT DISTINCT domain_id, entity_id, property_id FROM `name_data`', CONCAT(' UNION ALL SELECT DISTINCT domain_id, entity_id, property_id FROM `name_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 ALL SELECT DISTINCT domain_id, entity_id, property_id FROM `enum_data` AS subdata WHERE subdata.value ', o, ' ?'));
IF o = "!=" AND refIdsTable IS NOT NULL THEN
SET existence_op = "NOT EXISTS";
END IF;
/* select all entities that reference the entity or a child of the
* value interpreted as ID*/
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 ',existence_op ,' (SELECT 1 FROM `', refIdsTable, '` AS refIdsTable WHERE subdata.value=refIdsTable.id LIMIT 1) AND subdata.status != "REPLACEMENT"')));
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 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 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 sNullData = IF(vText IS NULL, ' UNION ALL SELECT DISTINCT domain_id, entity_id, property_id FROM `null_data`', NULL);
END IF;
END IF;
#-- create array of statement parts (and replace null with empty string) (?)
SET data = CONCAT('(',sTextData,
IF(sNameData IS NULL, '', sNameData),
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, versioned);
IF versioned THEN
#-- generate statement from statement parts
#-- (versioned section)
SET @stmtPOVkeepTblStr = CONCAT(
'INSERT IGNORE INTO `', keepTabl, '` (id, _iversion) SELECT entity_id AS id, _iversion 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))')));
IF targetSet IS NOT NULL THEN
SET @stmtPOVStr = CONCAT('INSERT IGNORE INTO `', targetSet,
'` (id, _iversion) SELECT source.id, source._iversion FROM `',
keepTabl,
'` AS source');
ELSE
-- unversioned shall not return any versions other than HEAD
SET @stmtPOVStr = CONCAT('DELETE FROM `',
sourceSet,
'` WHERE NOT EXISTS (SELECT 1 FROM `',
keepTabl,
'` AS data WHERE data.id=`',
sourceSet,
'`.`id` AND data._iversion=`',
sourceSet,
'`._iversion LIMIT 1)');
END IF;
#-- 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, @vText, @vText, @vText, @vText;
END IF;
EXECUTE stmt3;
DEALLOCATE PREPARE stmt3;
DEALLOCATE PREPARE stmtPOVkeepTbl;
ELSE
#-- 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))')));
#-- (unversioned section)
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, @vText;
END IF;
EXECUTE stmt3;
DEALLOCATE PREPARE stmt3;
DEALLOCATE PREPARE stmtPOVkeepTbl;
END IF;
SELECT @stmtPOVkeepTblStr as applyPOVStmt1, @stmtPOVStr as applyPOVStmt2, keepTabl as applyPOVIntermediateResultSet;
END;
//
DELIMITER ;