entityVersioning.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 db_2_0.insert_single_child_version;
delimiter //

CREATE PROCEDURE db_2_0.insert_single_child_version(
    in EntityID INT UNSIGNED,
    in Hash VARBINARY(255),
    in Version VARBINARY(255),
    in Parent VARBINARY(255),
    in Transaction VARBINARY(255))

BEGIN
    DECLARE newiversion INT UNSIGNED DEFAULT NULL;
    DECLARE newipparent INT UNSIGNED DEFAULT NULL;

    -- find _ipparent
    IF Parent IS NOT NULL THEN
        SELECT e._iversion INTO newipparent
            FROM entity_version AS e
            WHERE e.entity_id = EntityID
            AND e.version = Parent;
        IF newipparent IS NULL THEN
            -- throw error;
            SELECT concat("This parent does not exists: ", Parent)
            FROM nonexisting;
        END IF;
    END IF;


    -- generate _iversion
    SELECT max(e._iversion)+1 INTO newiversion
        FROM entity_version AS e
        WHERE e.entity_id=EntityID;
    IF newiversion IS NULL THEN
        SET newiversion = 1;
    END IF;

    INSERT INTO entity_version
        (entity_id, hash, version, _iversion, _ipparent, srid)
        VALUES
        (EntityID, Hash, Version, newiversion, newipparent, Transaction);



END;
//

DROP PROCEDURE IF EXISTS db_2_0.delete_all_entity_versions //
CREATE PROCEDURE db_2_0.delete_all_entity_versions(
    in EntityID INT UNSIGNED)
BEGIN

    DELETE FROM entity_version WHERE entity_id = EntityID;

END;
//

DROP FUNCTION IF EXISTS db_2_0.get_iversion //
CREATE FUNCTION db_2_0.get_iversion(
    EntityID INT UNSIGNED,
    Version VARBINARY(255))
RETURNS INT UNSIGNED
READS SQL DATA
BEGIN
    RETURN (
        SELECT e._iversion
            FROM entity_version AS e
            WHERE e.entity_id = EntityID
                AND e.version = Version
        );
END;
//


DROP FUNCTION IF EXISTS db_2_0.get_primary_parent_version //
CREATE FUNCTION db_2_0.get_primary_parent_version(
    d INT UNSIGNED,
    v VARBINARY(255))
RETURNS VARBINARY(255)
READS SQL DATA
BEGIN
    RETURN (
        SELECT p.version
            FROM entity_version AS e
            INNER JOIN entity_version as p
            ON (e._ipparent = p._iversion
                AND e.entity_id = p.entity_id)
            WHERE e.entity_id = d
                AND e.version = v
        );
END;
//


DROP FUNCTION IF EXISTS db_2_0.get_version_timestamp //
CREATE FUNCTION db_2_0.get_version_timestamp(
    EntityID INT UNSIGNED,
    Version VARBINARY(255))
RETURNS VARCHAR(255)
READS SQL DATA
BEGIN
    RETURN (
        SELECT concat(t.seconds, '.', t.nanos)
            FROM entity_version AS e
            INNER JOIN transactions AS t
            ON ( e.srid = t.srid )
            WHERE e.entity_id = EntityID
            AND e.version = Version
    );
END;
//

DROP FUNCTION IF EXISTS db_2_0.get_head_version //
CREATE FUNCTION db_2_0.get_head_version(
    EntityID INT UNSIGNED)
RETURNS VARBINARY(255)
READS SQL DATA
BEGIN
    RETURN get_head_relative(EntityID, 0);
END;
//


-- This implementation assumes that the distance from the head equals the
-- difference between the _iversion numbers. This will not be correct anymore,
-- as soon as branches may split and merge. Then, an tree-walk will be
-- necessary, traversing the primary parents (_pparent), will be necessary.
DROP FUNCTION IF EXISTS db_2_0.get_head_relative //
CREATE FUNCTION db_2_0.get_head_relative(
    EntityID INT UNSIGNED,
    Offset INT UNSIGNED)
RETURNS VARBINARY(255)
READS SQL DATA
BEGIN
    RETURN (
        SELECT e.version
            FROM entity_version AS e
            WHERE e.entity_id = EntityID
            ORDER BY e._iversion DESC
            LIMIT 1 OFFSET Offset
        );
END;
//

DROP FUNCTION IF EXISTS db_2_0._get_version //
CREATE FUNCTION db_2_0._get_version(
    EntityID INT UNSIGNED,
    IVersion INT UNSIGNED)
RETURNS VARBINARY(255)
READS SQL DATA
BEGIN
    RETURN (
        SELECT version FROM entity_version
            WHERE entity_id = EntityID
            AND _iversion = IVersion
        );
END;
//

DROP PROCEDURE IF EXISTS db_2_0.get_version_history //
CREATE PROCEDURE db_2_0.get_version_history(
    in EntityID INT UNSIGNED)
BEGIN
    -- retrieve root(s)
    SELECT c.version AS child,
            NULL as parent,
            t.seconds AS child_seconds,
            t.nanos AS child_nanos
        FROM entity_version AS c
        INNER JOIN transactions as t
        ON ( c.srid = t.srid )
        WHERE c.entity_id = EntityID
        AND c._ipparent is Null

    -- TODO This first SELECT statement is necessary because the second one
    -- does not return the root. However, this should be doable in one go with
    -- a left join.

    -- retrieve branches
    UNION SELECT c.version AS child,
            p.version AS parent,
            t.seconds AS child_seconds,
            t.nanos AS child_nanos
        FROM entity_version AS p
        INNER JOIN entity_version as c
        INNER JOIN transactions AS t
        ON (c._ipparent = p._iversion
            AND c.entity_id = p.entity_id
            AND t.srid = c.srid)
        WHERE p.entity_id = EntityID;
END;
//



delimiter ;