entityVersioning.sql

/*
 * This file is a part of the LinkAhead Project.
 *
 * Copyright (C) 2020-2024 IndiScale GmbH <info@indiscale.com>
 * Copyright (C) 2020,2023 Timm Fitschen <t.fitschen@indiscale.com>
 * Copyright (C) 2024 Daniel Hornung <d.hornung@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/>.
 */

delimiter //

DROP PROCEDURE IF EXISTS db_5_0.insert_single_child_version //

/**
 * Creates a new version record in the `entity_version` table.
 *
 * Parameters
 * ----------
 * InternalEntityID : INT UNSIGNED
 *   The internal ID of the versioned entity.
 * Hash : VARBINARY(255)
 *   A hash of the entity. This is currently not implemented properly and only
 *   there for future use.
 * Version : VARBINARY(255)
 *   The new version ID of the entity, must be produced by the caller.  Must be unique for each
 *   EntityID.
 * Parent : VARBINARY(255)
 *   The version ID of the primary parent (i.e. predecessor).  May be NULL; but if given, it must
 *   exist.
 * Transaction : VARBINARY(255)
 *   The transaction ID which created this entity version (by inserting
 *   or updating an entity).
 */
CREATE PROCEDURE db_5_0.insert_single_child_version(
    in InternalEntityID 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 = InternalEntityID
            AND e.version = Parent;
        IF newipparent IS NULL THEN
            -- throw error;
            SELECT concat("This parent does not exists: ", Parent)
            FROM parent_version_does_not_exist;
        END IF;
    END IF;


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

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



END;
//

DROP PROCEDURE IF EXISTS db_5_0.delete_all_entity_versions //

/* THIS PROCEDURE HAS NEVER BEEN USED (outside of the tests). Reactivate when
 * versioning's FORGET is being implemented. */
/**
 * Remove all records in the entity_version table for the given entity.
 *
 * Parameters
 * ----------
 * EntityID : VARCHAR(255)
 *   The id of the versioned entity.
 */
/* CREATE PROCEDURE db_5_0.delete_all_entity_versions(
    in EntityID VARCHAR(255))
BEGIN
    DECLARE InternalEntityID INT UNSIGNED DEFAULT NULL;

    SELECT internal_id INTO InternalEntityID FROM entity_ids WHERE id = EntityID;

    DELETE FROM entity_version WHERE entity_id = InternalEntityID;

END;
//
*/

DROP FUNCTION IF EXISTS db_5_0.get_iversion //

/**
 * Get the internal version id (an integer) of the (API-)version id.
 *
 * Parameters
 * ----------
 * InternalEntityID : INT UNSIGNED
 *   The entity's internal id.
 * Version : VARBINARY(255)
 *   The (official, externally used) version id.
 *
 * Returns
 * -------
 * The internal version id.
 */
CREATE FUNCTION db_5_0.get_iversion(
    InternalEntityID 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 = InternalEntityID
                AND e.version = Version
        );
END;
//


DROP FUNCTION IF EXISTS db_5_0.get_primary_parent_version //

/**
 * Get the version ID of the primary parent (i.e. predecessor) of a version.
 *
 * Parameters
 * ----------
 * EntityID : VARCHAR(255)
 *   The entity id.
 * Version : VARBINARY(255)
 *   The version id.
 *
 * Returns
 * -------
 * The id of the given version's primary parent version.
 */
CREATE FUNCTION db_5_0.get_primary_parent_version(
    EntityID VARCHAR(255),
    Version VARBINARY(255))
RETURNS VARBINARY(255)
READS SQL DATA
BEGIN
    DECLARE InternalEntityID INT UNSIGNED DEFAULT NULL;

    SELECT internal_id INTO InternalEntityID FROM entity_ids WHERE id = EntityID;

    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 = InternalEntityID
                AND e.version = Version
        );
END;
//


DROP FUNCTION IF EXISTS db_5_0.get_version_timestamp //
/**
 * Get the timestamp of a version by retrieving the timestamp of the
 * transaction which created the version.
 *
 * Parameters
 * ----------
 * EntityID : VARCHAR(255)
 *   The entity id.
 * Version : VARBINARY(255)
 *   The version id.
 *
 * Returns
 * -------
 * The versions' timestamp in <seconds-since-epoch>.<nanoseconds> format.
 * Note that the dot `.` here is not necessarily a decimal separator.
 */
CREATE FUNCTION db_5_0.get_version_timestamp(
    EntityID VARCHAR(255),
    Version VARBINARY(255))
RETURNS VARCHAR(255)
READS SQL DATA
BEGIN
    DECLARE InternalEntityID INT UNSIGNED DEFAULT NULL;

    SELECT internal_id INTO InternalEntityID FROM entity_ids WHERE id = EntityID;

    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 = InternalEntityID
            AND e.version = Version
    );
END;
//

DROP FUNCTION IF EXISTS db_5_0.get_head_version //

/**
 * Get the version id of the HEAD of an entity.
 *
 * Parameters
 * ----------
 * EntityID : VARCHAR(255)
 *   The entity id.
 *
 * Returns
 * -------
 * The version id of the HEAD.
 */
CREATE FUNCTION db_5_0.get_head_version(
    EntityID VARCHAR(255))
RETURNS VARBINARY(255)
READS SQL DATA
BEGIN
    RETURN get_head_relative(EntityID, 0);
END;
//

DROP FUNCTION IF EXISTS db_5_0._get_head_iversion //

/**
 * Get the _iversion number of the HEAD of an entity.
 *
 * Parameters
 * ----------
 * InternalEntityID : INT UNSIGNED
 *   The entity's internal id.
 *
 * Returns
 * -------
 * The _iversion of the HEAD.
 */
CREATE FUNCTION db_5_0._get_head_iversion(
    InternalEntityID INT UNSIGNED)
RETURNS INT UNSIGNED
READS SQL DATA
BEGIN
    -- This implementation assumes that the history is linear and the highest
    -- _iversion number is always the head. This will not be correct anymore
    -- as soon as branches may split and merge. Then, a walk over the primary
    -- parents (_ipparent) will be necessary.
    RETURN (
        SELECT e._iversion
            FROM entity_version AS e
            WHERE e.entity_id = InternalEntityID
            ORDER BY e._iversion DESC
            LIMIT 1
        );
END;


DROP FUNCTION IF EXISTS db_5_0.get_head_relative //

/**
 * Get the id of a version which is specified relative to the HEAD of an
 * entity.
 *
 * Parameters
 * ----------
 * EntityID : VARCHAR(255)
 *   The entity id.
 * HeadOffset : INT UNSIGNED
 *   Distance in the sequence of primary parents of the entity. E.g. `0` is the
 *   HEAD itself. `1` is the primary parent of the HEAD. `2` is the primary
 *   parent of the primary parent of the HEAD, and so on.
 *
 * Returns
 * -------
 * The version id of the HEAD.
 */
CREATE FUNCTION db_5_0.get_head_relative(
    EntityID VARCHAR(255),
    HeadOffset INT UNSIGNED)
RETURNS VARBINARY(255)
READS SQL DATA
BEGIN
    DECLARE InternalEntityID INT UNSIGNED DEFAULT NULL;

    SELECT internal_id INTO InternalEntityID FROM entity_ids WHERE id = EntityID;

    -- 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, a walk over the primary
    -- parents (_ipparent) will be necessary.
    RETURN (
        SELECT e.version
            FROM entity_version AS e
            WHERE e.entity_id = InternalEntityID
            ORDER BY e._iversion DESC
            LIMIT 1 OFFSET HeadOffset
        );
END;
//

DROP FUNCTION IF EXISTS db_5_0._get_version //
/**
 * The inverse function of get_iversion. Resolves the version id.
 *
 * Parameters
 * ----------
 * InternalEntityID : INT UNSIGNED
 *   The entity's internal id.
 * IVersion
 *   Internal version id (integer).
 *
 * Returns
 * -------
 * The (external) version id.
 */
CREATE FUNCTION db_5_0._get_version(
    InternalEntityID INT UNSIGNED,
    IVersion INT UNSIGNED)
RETURNS VARBINARY(255)
READS SQL DATA
BEGIN
    RETURN (
        SELECT version FROM entity_version
            WHERE entity_id = InternalEntityID
            AND _iversion = IVersion
        );
END;
//

DROP PROCEDURE IF EXISTS db_5_0.get_version_history //

/**
 * Select the complete transaction history of an entity (Who created which
 * version and when).
 *
 * Parameters
 * ----------
 * EntityID : VARCHAR(255)
 *   The entity id.
 *
 * Selects
 * -------
 * Tuples (child, parent, child_seconds, child_nanos, child_username,
 *   child_realm). `child` and `parent` are version IDs.
 */
CREATE PROCEDURE db_5_0.get_version_history(
    in EntityID VARCHAR(255))
BEGIN
    DECLARE InternalEntityID INT UNSIGNED DEFAULT NULL;

    SELECT internal_id INTO InternalEntityID FROM entity_ids WHERE id = EntityID;

    -- retrieve root(s) (initial versions)
    SELECT c.version AS child,
            NULL as parent,
            t.seconds AS child_seconds,
            t.nanos AS child_nanos,
            t.username AS child_username,
            t.realm AS child_realm
        FROM entity_version AS c INNER JOIN transactions as t
        ON ( c.srid = t.srid )
        WHERE c.entity_id = InternalEntityID
        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 instead of an inner join(?).

    -- retrieve branches (later versions)
    UNION SELECT c.version AS child,
            p.version AS parent,
            t.seconds AS child_seconds,
            t.nanos AS child_nanos,
            t.username AS child_username,
            t.realm AS child_realm
        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 = InternalEntityID;
END;
//


DROP PROCEDURE IF EXISTS set_transaction //
/**
 * Called when a new transaction begins. Inserts the transaction metadata into
 * the transactions table.
 *
 * Parameters
 * ----------
 * srid
 *   The server-generated Server Request ID.
 * username
 *   The username of the subject who/which is responsible for the transaction.
 * realm
 *   The realm of the subject.
 * seconds
 *   Unix timestamp (seconds).
 * nanos
 *   Nanoseconds precision for the timestamp.
 */
CREATE PROCEDURE set_transaction(
    srid VARBINARY(255),
    username VARCHAR(255),
    realm VARCHAR(255),
    seconds BIGINT UNSIGNED,
    nanos INT(10) UNSIGNED)
BEGIN

    SET @SRID = srid;  -- Make the transaction / server request ID globally available.
    INSERT INTO transactions (srid, username, realm, seconds, nanos)
        VALUES (srid, username, realm, seconds, nanos);
END //


DROP PROCEDURE IF EXISTS retrieveQueryTemplateDef //

/**
 * Select a query string from the templates tables.
 *
 * Parameters
 * ----------
 * EntityID : VARCHAR(255)
 *   The QueryTemplate's id.
 * Version : VARBINARY(255)
 *   The QueryTemplate's version's id.
 *
 * Returns
 * -------
 * Tuple (definition). A CQL query string (-fragment) which defines this
 * QueryTemplate.
 */
CREATE PROCEDURE retrieveQueryTemplateDef (
    in EntityID VARCHAR(255),
    in Version VARBINARY(255))
retrieveQueryTemplateDefBody: BEGIN

    DECLARE IVersion INT UNSIGNED DEFAULT NULL;
    DECLARE IsHead BOOLEAN DEFAULT TRUE;
    DECLARE InternalEntityID INT UNSIGNED DEFAULT NULL;

    SELECT internal_id INTO InternalEntityID FROM entity_ids WHERE id = EntityID;


    IF is_feature_config("ENTITY_VERSIONING", "ENABLED") THEN
        -- Are we at the head?
        IF Version IS NOT NULL THEN
            SELECT get_head_version(EntityID) = Version INTO IsHead;
        END IF;

        IF IsHead IS FALSE THEN
            SET IVersion = get_iversion(InternalEntityID, Version);

            IF IVersion IS NULL THEN
                -- RETURN EARLY - Version does not exist.
                LEAVE retrieveQueryTemplateDefBody;
            END IF;

            SELECT definition
            FROM archive_query_template_def
            WHERE id = InternalEntityID
            AND _iversion = IVersion;

            LEAVE retrieveQueryTemplateDefBody;
        END IF;
    END IF;

    SELECT definition
    FROM query_template_def
    WHERE id = InternalEntityID;

END //


delimiter ;