/*
* 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 ;