fix_unversioned.sql

/*
 * This file is a part of the CaosDB Project.
 *
 * Copyright (C) 2020 IndiScale GmbH <info@indiscale.com>
 * Copyright (C) 2020 Timm Fitschen <t.fitschen@indiscale.com>
 *
 * 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/>.
 */

DROP PROCEDURE IF EXISTS _fix_unversioned;
delimiter //

/*
 * Generate entries in the entity_version table for all entities which are
 * stored in the database and have no entry in the entity_version table yet.
 */
CREATE PROCEDURE _fix_unversioned()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE unversioned_id INT UNSIGNED;
    -- cursor for unversioned entities
    DECLARE entities_cur CURSOR FOR SELECT e.id
        FROM entities AS e
        WHERE NOT EXISTS (
            SELECT 1 FROM entity_version AS v WHERE v.entity_id = e.id);
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;


    OPEN entities_cur;
    insert_loop: LOOP
        FETCH entities_cur INTO unversioned_id;
        IF done THEN
            LEAVE insert_loop;
        END IF;

        -- create an entry in transaction from the latest log in
        -- transaction_log, hence the latest update of an entity
        INSERT IGNORE INTO transactions (srid, username, realm, seconds, nanos)
            SELECT tmp.srid, tmp.username, tmp.realm, tmp.seconds, tmp.nanos FROM (
                SELECT
                    SHA2(CONCAT(username, realm, seconds, nanos), 512) AS srid,
                    username AS username,
                    realm AS realm,
                    seconds AS seconds,
                    nanos AS nanos
                FROM transaction_log
                WHERE entity_id = unversioned_id
                UNION SELECT
                    -- this is for the standard entities (id<100)
                    SHA2("", 512) AS srid,
                    "administration" As username,
                    "CaosDB" AS realm,
                    0 as seconds,
                    0 as nanos
            ) AS tmp ORDER BY tmp.seconds DESC, tmp.nanos DESC LIMIT 1;

        -- insert single version into entity_version
        INSERT INTO entity_version (entity_id, hash, version, _iversion,
            _ipparent, srid)
            SELECT tmp.entity_id, tmp.hash, tmp.version, tmp._iversion,
                tmp._ipparent, tmp.srid
                FROM (
                    SELECT
                        l.entity_id AS entity_id,
                        NULL as hash,
                        SHA1(UUID()) as version,
                        1 AS _iversion,
                        NULL AS _ipparent,
                        t.srid AS srid,
                        t.seconds AS seconds,
                        t.nanos AS nanos
                    FROM transactions AS t JOIN transaction_log as l
                    ON ( t.seconds = l.seconds
                        AND t.nanos = l.nanos
                        AND t.username = l.username
                        AND t.realm  = l.realm )
                    WHERE l.entity_id = unversioned_id AND unversioned_id > 99
                    UNION SELECT
                        unversioned_id AS entity_id,
                        NULL AS hash,
                        SHA1(UUID()) AS version,
                        1 AS _iversion,
                        NULL AS _ipparent,
                        t.srid AS srid,
                        t.seconds AS seconds,
                        t.nanos AS nanos
                    FROM transactions AS t
                    WHERE t.seconds = 0 AND t.nanos = 0 AND unversioned_id<100
                ) AS tmp ORDER BY tmp.seconds DESC, tmp.nanos DESC LIMIT 1;


    END LOOP;
    CLOSE entities_cur;
END;
//

delimiter ;

CALL _fix_unversioned();

DROP PROCEDURE _fix_unversioned;