test_entity_versioning.sql

/**
 * ** header v3.0
 * 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/>.
 *
 * ** end header
 */

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 entity_version;
DELETE FROM transactions;
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 id > 99;
CALL entityACL(@ACLID1, "{acl1}");
CALL insertEntity("EntityName", "EntityDesc", "RECORDTYPE", "{acl1}");
SELECT entity_id INTO @EntityID FROM name_data WHERE value="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 _ipparent INTO @x from entity_version WHERE version="versionbla";
SELECT tap.eq(@x, NULL, "no parent for the first version");

-- add a second version
SELECT count(*) INTO @x FROM entity_version;
SELECT tap.eq(@x, 1, "one version there already");
CALL insert_single_child_version(@EntityID, "hashblub", "versionblub", "versionbla", "SRIDblub");

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

-- error: parent does not exist
SELECT count(*) INTO @x FROM entity_version;
SELECT tap.eq(@x, 2, "two versions there already");

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

SELECT count(*) INTO @x FROM entity_version;
SELECT tap.eq(@x, 2, "still two versions there");


-- 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
DELETE FROM name_data WHERE entity_id > 99;
DELETE FROM entities WHERE id > 99;

-- #####################################################################
-- 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 entity_id INTO @EntityID FROM name_data WHERE value="EntityName";
CALL insertEntity("ParentName", "ParentDesc", "RECORDTYPE", "{acl1}");
SELECT entity_id INTO @ParentID FROM name_data WHERE value="ParentName";
CALL insertIsa(@EntityID, @ParentID);
CALL insertEntityProperty(0, @EntityID, 17, "null_data", NULL, NULL,
    "RECOMMENDED", NULL, "DescOverride", NULL, NULL, 0);

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

SELECT _ipparent INTO @x from entity_version WHERE entity_id = @EntityID;
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 count(*) INTO @x FROM entity_version WHERE entity_id = @EntityID;
SELECT tap.eq(@x, 2, "after updateEntity, a second version is there.");

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

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;
CALL retrieveEntity(@EntityID, NULL);
CALL retrieveEntity(@EntityID, "non-existing-version");
CALL retrieveEntity(@EntityID, get_head_version(@EntityID));
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 count(*) INTO @x FROM entity_version WHERE entity_id = @EntityID;
SELECT tap.eq(@x, 3, "after 2nd updateEntity, a 3rd version is there.");

SELECT _iversion INTO @x FROM entity_version WHERE entity_id = @EntityID and _ipparent = 2;
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;


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

-- TEST _fix_unversioned
-- turn off version, insert entity, turn on version, call _fix_unversioned
-- The inserted entity should have no entry in entity_version at first and
-- after _fix_unversioned an entry is gerated.

BEGIN;
CALL tap.no_plan();


-- setup
DELETE FROM entity_version;
DELETE FROM transactions;
-- insert entity without versioning
DELETE FROM feature_config WHERE _key = "ENTITY_VERSIONING";
CALL entityACL(@ACLID1, "{acl1}");
CALL insertEntity("EntityName", "EntityDesc", "RECORDTYPE", "{acl1}");
SELECT count(*) INTO @NumOfEntities FROM entities;
SELECT entity_id INTO @EntityID FROM name_data WHERE value="EntityName";

SET @TheUser = "TheUser"; -- used to identify the matching entry in transaction_log
-- fill transaction_log: one entity with two updates (and one insert) and another entity with insert and delete.
INSERT INTO transaction_log (transaction, entity_id, username, realm, seconds, nanos)
    -- the first entry is the one which is be found by _fix_unversioned
    VALUES
        ("Update", @EntityID, @TheUser,     "CaosDB",  23458, 254),
        -- the rest is dummy data
        ("Update", @EntityID, "OtherUser",  "CaosDB",   2345, 633), -- not the latest transaction
        ("Insert", @EntityID, "OtherUser",  "CaosDB",    245, 633), -- not the latest transaction
        ("Insert", @EntityID + 1, @TheUser, "CaosDB",   2325, 653), -- not the right entity, inserted before our target
        ("Delete", @EntityID + 1, @TheUser, "CaosDB", 232526, 653); -- not the right entity, deleted after our target


SELECT tap.eq(COUNT(*), 5, "five entries in transaction_log") FROM transaction_log;
SELECT tap.eq(COUNT(*), 0, "no versioned entity there") FROM entity_version;
SELECT tap.eq(COUNT(*), 0, "no transaction there") FROM transactions;

INSERT INTO feature_config (_key, _value) VALUES ("ENTITY_VERSIONING", "ENABLED");
SELECT tap.eq(COUNT(*), 0, "still, no versioned entity there") FROM entity_version;
SELECT tap.eq(COUNT(*), 0, "still, no transaction there") FROM transactions;

-- call _fix_unversioned
CALL _fix_unversioned();
SELECT tap.eq(COUNT(*), @NumOfEntities,
    "after _fix_unversioned, one entry for our test entity in entity_version")
    FROM entity_version;
SELECT tap.eq(COUNT(*), 2,
    "after _fix_unversioned, one entry for our test entity in transactions, one for the standard entities.")
    FROM transactions;

SELECT tap.eq(entity_id, @EntityID, "versioned entity has correct id") FROM entity_version WHERE entity_id > 99;
SELECT tap.ok(srid IS NOT NULL, "srid was generated and user/time matches entries from transaction_log")
    FROM transactions AS t JOIN transaction_log AS l
    ON (l.seconds = t.seconds AND l.nanos = t.nanos AND l.username = t.username AND l.realm = t.realm)
    WHERE l.entity_id = @EntityID AND l.username = @TheUser;

CALL tap.finish();
ROLLBACK;


-- TEST _fix_unversioned 2
-- Nastier case: two entities. together inserted, one got updated afterwards.
-- Both should be in the entity_version table after execution _fix_unversioned.
BEGIN;
CALL tap.no_plan();


-- setup
DELETE FROM entity_version;
DELETE FROM transactions;
-- insert entity without versioning
DELETE FROM feature_config WHERE _key = "ENTITY_VERSIONING";

CALL entityACL(@ACLID1, "{acl1}");
CALL insertEntity("EntityName1", "EntityDesc1", "RECORDTYPE", "{acl1}");
CALL insertEntity("EntityName2", "EntityDesc2", "RECORDTYPE", "{acl1}");
SELECT count(*) INTO @NumOfEntities FROM entities;
SELECT entity_id INTO @EntityID1 FROM name_data WHERE value="EntityName1";
SELECT entity_id INTO @EntityID2 FROM name_data WHERE value="EntityName2";

INSERT INTO transaction_log (transaction, entity_id, username, realm, seconds,
        nanos)
    -- the first entry is the one which will be found by _fix_unversioned
    VALUES ("INSERT", @EntityID1, "User", "CaosDB", 10000, 250),
        ("INSERT", @EntityID2, "User", "CaosDB", 10000, 250),
        ("UPDATE", @EntityID2, "User", "CaosDB", 20000, 250);


SELECT tap.eq(COUNT(*), 3, "three entries in transaction_log") FROM transaction_log;
SELECT tap.eq(COUNT(*), 0, "no versioned entity there") FROM entity_version;
SELECT tap.eq(COUNT(*), 0, "no transaction there") FROM transactions;

INSERT INTO feature_config (_key, _value) VALUES ("ENTITY_VERSIONING", "ENABLED");
SELECT tap.eq(COUNT(*), 0, "still, no versioned entity there") FROM entity_version;
SELECT tap.eq(COUNT(*), 0, "still, no transaction there") FROM transactions;

-- call _fix_unversioned
CALL _fix_unversioned();
SELECT tap.eq(COUNT(*), @NumOfEntities,
        "after _fix_unversioned, one entry for our test entity in entity_version")
    FROM entity_version;
SELECT tap.eq(COUNT(*), 3,
        "after _fix_unversioned, one entry for each of our test entities in
        transactions, one for the standard entities.")
    FROM transactions;

SELECT tap.eq(seconds, 10000, "version seconds of entity 1 is correct")
    FROM entity_version AS v JOIN transactions AS t
    ON (v.srid = t.srid) WHERE v.entity_id = @EntityID1;
SELECT tap.eq(seconds, 20000, "version seconds of entity 2 is correct")
    FROM entity_version AS v JOIN transactions AS t
    ON (v.srid = t.srid) WHERE v.entity_id = @EntityID2;

CALL tap.finish();
ROLLBACK;