makeStmt.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 FUNCTION IF EXISTS db_2_0.makeStmt;
DELIMITER //

/**
 * Create and return a statement string which generalizes the source -> target procedure.
 *
 * Parameters
 * ----------
 *
 * sourceSet : table
 * The source table.
 *
 * targetSet : table
 * The table into which the results shall be copied.  If `NULL`, delete all non-matching rows from
 * `sourceSet`.
 *
 * data : table
 * Table whose IDs are a whitelist for which IDs shall be copied from `sourceSet` to `targetSet`.
 *
 * properties : table
 * Table with a whitelist of properties.  If not `NULL`, the `property_id` of `data` must be an ID
 * in `properties`.
 * TODO Add documentation about the `id2` meaning of `properties`.
 *
 * versiones : boolean
 * If `True` and if `sourceSet` is the `entities` table, the ID and version are copied directly from
 * `data` to `targetSet`, which must not be `NULL`.  Property filtering is done as usual.
 */
CREATE FUNCTION db_2_0.makeStmt(sourceSet VARCHAR(255), targetSet VARCHAR(255), data VARCHAR(20000),
                                properties VARCHAR(20000), versioned BOOLEAN)
RETURNS VARCHAR(20000) NO SQL
BEGIN
        IF sourceSet = "entities" AND versioned THEN
            RETURN CONCAT('INSERT IGNORE INTO `',
                targetSet,
                '` (id, _iversion) SELECT entity_id, _iversion FROM ',
                data,
                IF(properties IS NULL, '',
                    CONCAT(' AS data JOIN `', properties, '` AS prop ON (data.property_id = prop.id) WHERE ',
                           'data.entity_id = prop.id2 OR prop.id2 = 0')));
        END IF;
        RETURN 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 ')),
            IF(properties IS NULL,
                CONCAT(data,' as data WHERE '),
                CONCAT('`',properties,'` as prop JOIN ',data,' as data ON (data.property_id=prop.id) WHERE ',
                       '(data.entity_id=prop.id2 OR prop.id2=0) AND ')),
            'data.entity_id=`', sourceSet, '`.`id` LIMIT 1)'
        );

END;
//
DELIMITER ;