test_entity_versioning.sql

USE _caosdb_schema_unit_tests;
BEGIN;
CALL tap.no_plan();



-- #####################################################################
-- TEST insert_single_child_version, get_primary_parent_version and
-- delete_all_entity_versions in isolation
-- #####################################################################

-- SETUP

-- Disable versioning because we want to test `insert_single_child_version`
-- separately from `insertEntity` but the former is called inside the latter
-- when versioning is enabled.
DELETE FROM feature_config WHERE _key = "ENTITY_VERSIONING";
INSERT INTO transactions (srid,seconds,nanos,username,realm) VALUES 
("SRIDbla", 1234, 2345, "me", "home"),
("SRIDblub", 2345, 3465, "me", "home"),
("SRIDblieb", 3456, 4576, "you", "home");
DELETE FROM entities WHERE name="EntityName";
CALL entityACL(@ACLID1, "{acl1}");
CALL insertEntity("EntityName", "EntityDesc", "RECORDTYPE", "{acl1}");
SELECT id INTO @EntityID FROM entities WHERE name="EntityName";


-- TEST insert_single_child_version
SELECT count(*) INTO @x FROM entity_version;
SELECT tap.eq(@x, 0, "no versions there yet");

CALL insert_single_child_version(@EntityID, "hashbla", "versionbla", NULL, "SRIDbla");
SELECT tap.eq(_ipparent, NULL, "no parent for the first version")
    FROM entity_version WHERE version="versionbla" AND _is_head IS TRUE;
SELECT tap.eq(_ipparent, NULL, "no parent for the first version")
    FROM entity_version WHERE version="versionbla" AND _is_head IS FALSE;

-- add a second version
SELECT tap.eq(count(*), 1, "one version there yet")
    FROM entity_version WHERE _is_head IS FALSE;
CALL insert_single_child_version(@EntityID, "hashblub", "versionblub", "versionbla", "SRIDblub");

SELECT _ipparent INTO @x from entity_version WHERE version="versionblub" AND _is_head IS TRUE;
SELECT tap.eq(@x, 1, "the original entity is the parent");

-- test error: parent does not exist
SELECT count(DISTINCT version) INTO @x FROM entity_version;
SELECT tap.eq(@x, 2, "two version there yet");

CALL tap._assert_throws(concat("CALL insert_single_child_version(", @EntityID, ', "hashblieb", "versionblieb", "non-existing-parent", "SRIDBlieb")'), "non existing parent throws");

SELECT tap.eq(count(DISTINCT version), 2, "still two version there")
    FROM entity_version;


-- TEST get_primary_parent_version
SELECT tap.eq(get_primary_parent_version(@EntityID, "versionblub"), "versionbla", "returns correct parent for versionblub");
SELECT tap.eq(get_primary_parent_version(@EntityID, "versionbla"), NULL, "versionbla has no parent");


-- TEST delete_all_entity_versions
SELECT count(*) INTO @x FROM entity_version;
SELECT tap.ok(@x > 0, "several versions in the table");

CALL delete_all_entity_versions(@EntityID);
SELECT count(*) INTO @x FROM entity_version;
SELECT tap.eq(@x, 0, "no versions there any more");


-- TEARDOWN clean up a litte
DELETE FROM entities WHERE name="EntityName";

-- #####################################################################
-- TEST the call of insert_single_child_version from within insertEntity
-- #####################################################################

-- SETUP switch on the feature
INSERT INTO feature_config (_key, _value) VALUES ("ENTITY_VERSIONING", "ENABLED");
SELECT count(*) INTO @x FROM entity_version;
SELECT tap.eq(@x, 0, "before insertEntity, no versions there");

-- TEST insertEntity - should produce a version w/o parent
SET @SRID = "SRIDbla";
CALL insertEntity("EntityName", "EntityDesc", "RECORDTYPE", "{acl1}");
SELECT id INTO @EntityID FROM entities WHERE name="EntityName";
CALL insertEntity("ParentName", "ParentDesc", "RECORDTYPE", "{acl1}");
SELECT id INTO @ParentID FROM entities WHERE name="ParentName";
CALL insertIsa(@EntityID, @ParentID);
CALL insertEntityProperty(0, @EntityID, 17, "null_data", NULL, NULL,
    "RECOMMENDED", NULL, "DescOverride", NULL, NULL, 0);

SELECT tap.eq(count(DISTINCT version), 1,
    "after insertEntity, a version is there.")
    FROM entity_version WHERE entity_id = @EntityID;
SELECT _iversion INTO @x FROM entity_version WHERE entity_id = @EntityID and _ipparent is NULL AND _is_head IS FALSE;
SELECT tap.eq(@x, 1, "after insertEntity, the _iversion number is 1.");

SELECT _ipparent INTO @x from entity_version WHERE entity_id = @EntityID AND _is_head IS TRUE;
SELECT tap.eq(@x, NULL, "no parent for the freshly inserted entity");
SELECT tap.eq(count(*), 0, "no entity in archive_entities before first update")
    FROM archive_entities;


-- TEST updateEntity - should produce a version with a parent
SET @SRID = "SRIDblub";
CALL deleteEntityProperties(@EntityID);
CALL updateEntity(@EntityID, "NewEntityName", "NewEntityDesc", "RECORD", NULL, NULL, "{acl1}");
SELECT tap.eq(count(DISTINCT version), 2,
    "after updateEntity, a second version is there.")
    FROM entity_version WHERE entity_id = @EntityID;

SELECT tap.eq(_iversion, 2,
        "after updateEntity, the _iversion number incremented."),
    tap.eq(_ipparent, 1,
        "after updateEntity, the _ipparent points to the first version")
    FROM entity_version WHERE entity_id = @EntityID and _ipparent = 1 
        AND _is_head IS FALSE;

SELECT tap.eq(count(*), 1, "after updateEntity, one entity in archive_entities")
    FROM archive_entities;


-- TEST retrieveEntity

SELECT version INTO @x FROM entity_version
    WHERE entity_id = @EntityID
    AND _iversion = 2;
SELECT get_head_version(@EntityID) AS "HERE";
CALL retrieveEntity(@EntityID, get_head_version(@EntityID));
CALL retrieveEntity(@EntityID, NULL);
CALL retrieveEntity(@EntityID, "non-existing-version");
CALL retrieveEntity(@EntityID, @x);

-- TEST retrieveEntityParents

CALL retrieveEntityParents(@EntityID, NULL);
CALL retrieveEntityParents(@EntityID, "non-existing-version");
CALL retrieveEntityParents(@EntityID, get_head_version(@EntityID));
CALL retrieveEntityParents(@EntityID, @x);

-- TEST retrieveEntityProperties

CALL retrieveEntityProperties(0, @EntityID, NULL);
CALL retrieveEntityProperties(0, @EntityID, "non-existing-version");
CALL retrieveEntityProperties(0, @EntityID, get_head_version(@EntityID));
CALL retrieveEntityProperties(0, @EntityID, @x);

-- TEST retrieveOverrides

CALL retrieveOverrides(0, @EntityID, NULL);
CALL retrieveOverrides(0, @EntityID, "non-existing-version");
CALL retrieveOverrides(0, @EntityID, get_head_version(@EntityID));
CALL retrieveOverrides(0, @EntityID, @x);


-- and 2nd updateEntity
SET @SRID = "SRIDblieb";
CALL updateEntity(@EntityID, "EntityName", "EntityDesc", "RECORDTYPE", NULL, NULL, "{acl1}");

SELECT tap.eq(count(DISTINCT version), 3,
    "after 2nd updateEntity, a 3rd version is there.")
    FROM entity_version WHERE entity_id = @EntityID;

SELECT tap.eq(count(*), 1, "after 2nd updateEntity, a single HEAD version is there.")
    FROM entity_version WHERE entity_id = @EntityID AND _is_head IS TRUE;

SELECT _iversion INTO @x FROM entity_version WHERE entity_id = @EntityID and _ipparent = 2 AND _is_head IS FALSE;
SELECT tap.eq(@x, 3, "after 2nd updateEntity, the _iversion number incremented again.");
SELECT _ipparent INTO @x FROM entity_version WHERE entity_id = @EntityID and _iversion = 3;
SELECT tap.eq(@x, 2, "after 2nd updateEntity, the _pparent points to the 2nd version");
SELECT tap.eq("SRIDblieb", srid, "correct transaction was stored") FROM entity_version WHERE entity_id = @EntityID AND _ipparent = 2 AND _is_head IS FALSE;


-- TEST deleteEntity - should remove all versions
CALL deleteIsa(@EntityID);
CALL deleteEntity(@EntityID);
SELECT count(*) INTO @x FROM entity_version WHERE entity_id = @EntityID;
SELECT tap.eq(@x, 0, "no versions there any more");

CALL deleteEntity(@ParentID);



CALL tap.finish();
ROLLBACK;