createTmpTable.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.registerTempTableName;

DELIMITER //
CREATE PROCEDURE db_2_0.registerTempTableName(out newTableName VARCHAR(255))
BEGIN
    SET newTableName = md5(CONCAT(RAND(),CURRENT_TIMESTAMP()));
    SET @tempTableList = IF(@tempTableList IS NULL,
        CONCAT('`',newTableName,'`'),
        CONCAT(@tempTableList, ',`', newTableName, '`')
    );
END;
//
DELIMITER ;

DROP PROCEDURE IF EXISTS db_2_0.createTmpTable;
DELIMITER //

/**
 * If not versioned: Creates a temporary table for query results with only a single `id` column.
 *
 * If versioned: Creates a temporary table for query results with an `id`
 * column and an `_iversion` column.
 */
CREATE PROCEDURE db_2_0.createTmpTable(out newTableName VARCHAR(255), in versioned BOOLEAN)
BEGIN
    call registerTempTableName(newTableName);

    IF versioned THEN
        SET @createTableStmtStr = CONCAT('CREATE TEMPORARY TABLE `', newTableName,
            '` ( id INT UNSIGNED, _iversion INT UNSIGNED, PRIMARY KEY (id, _iversion))' );
    ELSE
        SET @createTableStmtStr = CONCAT('CREATE TEMPORARY TABLE `', newTableName,'` ( id INT UNSIGNED PRIMARY KEY)' );
    END IF;

    PREPARE createTableStmt FROM @createTableStmtStr; 
    EXECUTE createTableStmt;
    DEALLOCATE PREPARE createTableStmt;
END;
//

DELIMITER ;



/**
 * Creates a temporary table for intermediate query results with three columns
 *     id - for entity ids (e.g. property ids or reference values)
 *     id2 - for entity ids (usually those entities which have the other id as property or value)
 *     domain - for domain ids
 */
DROP PROCEDURE IF EXISTS db_2_0.createTmpTable2;
DELIMITER //

CREATE PROCEDURE db_2_0.createTmpTable2(out newTableName VARCHAR(255))
BEGIN
    call registerTempTableName(newTableName);
    SET @createTableStmtStr = CONCAT('CREATE TEMPORARY TABLE `', newTableName,
        '` ( id INT UNSIGNED, id2 INT UNSIGNED, domain INT UNSIGNED, CONSTRAINT `',
        newTableName,'PK` PRIMARY KEY (id,id2,domain) )' );

    PREPARE createTableStmt FROM @createTableStmtStr; 
    EXECUTE createTableStmt;
    DEALLOCATE PREPARE createTableStmt;
END;
//

DELIMITER ;