insertEntityProperty.sql

/*
 * This file is a part of the CaosDB Project.
 *
 * Copyright (C) 2018 Research Group Biomedical Physics,
 * Max-Planck-Institute for Dynamics and Self-Organization Göttingen
 * Copyright (C) 2020,2023 IndiScale GmbH <info@indiscale.com>
 * Copyright (C) 2020,2023 Timm Fitschen <t.fitschen@indiscale
 *
 * 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_5_0.insertEntityProperty;
delimiter //


/*
 * Insert the property of an entity (level 1 and 2, only one *_data table entry at a time).
 *
 * Parameters
 * ----------
 * DomainID : VARCHAR(255)
 *     The domain id (0 or the entity's id for level-2-data)
 * EntityID : VARCHAR(255)
 *     The entity id (or the property's id for level-2-data)
 * PropertyID : VARCHAR(255)
 *     The property id (or the sub-property's id for level-2-data)
 * Datatable : VARCHAR(255)
 *     Name of the *_data table, e.g. 'double_data'.
 * PropertyValue : TEXT
 *     The property's value
 * PropertyUnitSig : BIGINT
 *     The unit signature.
 * PropertyStatus : VARCHAR(255)
 *     E.g. OBLIGATORY, FIX,...
 * NameOverride : VARCHAR(255)
 *     The overridden name
 * DescOverride : TEXT
 *     The overridden description
 * DatatypeOverride : VARCHAR(255)
 *     The overridden datatype
 * Collection : VARCHAR(255)
 *     The overridden collection (only if DatatypeOverride is present).
 * PropertyIndex : INT UNSIGNED
 *     The property's index (for ordering of properties and values).
 */
CREATE PROCEDURE db_5_0.insertEntityProperty(
    in DomainID VARCHAR(255),
    in EntityID VARCHAR(255),
    in PropertyID VARCHAR(255),
    in Datatable VARCHAR(255),
    in PropertyValue TEXT,
    in PropertyUnitSig BIGINT,
    in PropertyStatus VARCHAR(255),
    in NameOverride VARCHAR(255),
    in DescOverride TEXT,
    in DatatypeOverride VARCHAR(255),
    in Collection VARCHAR(255),
    in PropertyIndex INT UNSIGNED)
BEGIN
    DECLARE ReferenceValueIVersion INT UNSIGNED DEFAULT NULL;
    DECLARE ReferenceValue INT UNSIGNED DEFAULT NULL;
    DECLARE AT_PRESENT INTEGER DEFAULT NULL;
    DECLARE InternalDataTypeID INT UNSIGNED DEFAULT NULL;
    DECLARE InternalPropertyID INT UNSIGNED DEFAULT NULL;
    DECLARE InternalEntityID INT UNSIGNED DEFAULT NULL;
    DECLARE InternalDomainID INT UNSIGNED DEFAULT 0;

    SELECT temp.internal_id INTO InternalDomainID FROM (SELECT internal_id AS internal_id FROM entity_ids WHERE id = DomainID UNION SELECT DomainID AS internal_id) AS temp LIMIT 1;
    SELECT temp.internal_id INTO InternalEntityID FROM (SELECT internal_id AS internal_id FROM entity_ids WHERE id = EntityID UNION SELECT EntityID AS internal_id) AS temp LIMIT 1;
    SELECT temp.internal_id INTO InternalPropertyID FROM (SELECT internal_id AS internal_id FROM entity_ids WHERE id = PropertyID UNION SELECT PropertyID AS internal_id) AS temp LIMIT 1;

    CASE Datatable
    WHEN 'double_data' THEN
        INSERT INTO double_data
        (domain_id, entity_id, property_id, value, unit_sig, status, pidx)
        VALUES
        (InternalDomainID, InternalEntityID, InternalPropertyID, PropertyValue, PropertyUnitSig, PropertyStatus, PropertyIndex);
    WHEN 'integer_data' THEN
        INSERT INTO integer_data
        (domain_id, entity_id, property_id, value, unit_sig, status, pidx)
        VALUES
        (InternalDomainID, InternalEntityID, InternalPropertyID, PropertyValue, PropertyUnitSig, PropertyStatus, PropertyIndex);
    WHEN 'datetime_data' THEN
        INSERT INTO datetime_data
        (domain_id, entity_id, property_id, value, value_ns, status, pidx)
        VALUES
        (InternalDomainID, InternalEntityID, InternalPropertyID, SUBSTRING_INDEX(PropertyValue, 'UTC', 1), IF(SUBSTRING_INDEX(PropertyValue, 'UTC', -1)='',NULL,SUBSTRING_INDEX(PropertyValue, 'UTC', -1)), PropertyStatus, PropertyIndex);
    WHEN 'reference_data' THEN

        -- special handling if versioning enabled and specific version of referenced entity is given.
        SET AT_PRESENT=LOCATE("@", PropertyValue);
        IF is_feature_config("ENTITY_VERSIONING", "ENABLED") AND AT_PRESENT > 0 THEN
            SELECT internal_id INTO ReferenceValue FROM entity_ids WHERE id = SUBSTRING_INDEX(PropertyValue, '@', 1);
            SET ReferenceValueIVersion = get_iversion(ReferenceValue,
                SUBSTRING_INDEX(PropertyValue, '@', -1));
            IF ReferenceValueIVersion IS NULL THEN
                -- Raise error when @ present but iversion is null
                SELECT 0 from `ReferenceValueIVersion_WAS_NULL`;
            END IF;

        ELSE
            SELECT temp.internal_id INTO ReferenceValue FROM (SELECT internal_id AS internal_id FROM entity_ids WHERE id = PropertyValue UNION SELECT PropertyValue AS internal_id) AS temp LIMIT 1;
        END IF;


        INSERT INTO reference_data
            (domain_id, entity_id, property_id, value, value_iversion, status,
                pidx)
        VALUES
            (InternalDomainID, InternalEntityID, InternalPropertyID, ReferenceValue,
                ReferenceValueIVersion, PropertyStatus, PropertyIndex);
    WHEN 'enum_data' THEN
        INSERT INTO enum_data
        (domain_id, entity_id, property_id, value, status, pidx)
        VALUES
        (InternalDomainID, InternalEntityID, InternalPropertyID, PropertyValue, PropertyStatus, PropertyIndex);
    WHEN 'date_data' THEN
        INSERT INTO date_data
        (domain_id, entity_id, property_id, value, status, pidx)
        VALUES
        (InternalDomainID, InternalEntityID, InternalPropertyID, SUBSTRING_INDEX(PropertyValue, '.', 1), PropertyStatus, PropertyIndex);
    WHEN 'text_data' THEN
        INSERT INTO text_data
        (domain_id, entity_id, property_id, value, status, pidx)
        VALUES
        (InternalDomainID, InternalEntityID, InternalPropertyID, PropertyValue, PropertyStatus, PropertyIndex);
    WHEN 'null_data' THEN
        INSERT INTO null_data
        (domain_id, entity_id, property_id, status, pidx)
        VALUES
        (InternalDomainID, InternalEntityID, InternalPropertyID, PropertyStatus, PropertyIndex);
    WHEN 'name_data' THEN
        INSERT INTO name_data
        (domain_id, entity_id, property_id, value, status, pidx)
        VALUES
        (InternalDomainID, InternalEntityID, InternalPropertyID, PropertyValue, PropertyStatus, PropertyIndex);

    ELSE
        -- raise error
        SELECT * FROM table_does_not_exist;
    END CASE;

    IF DatatypeOverride IS NOT NULL THEN
        SELECT internal_id INTO InternalDataTypeID from entity_ids WHERE id = DatatypeOverride;
        call overrideType(InternalDomainID, InternalEntityID, InternalPropertyID, InternalDataTypeID);
        IF Collection IS NOT NULL THEN
            INSERT INTO collection_type (domain_id, entity_id, property_id, collection) VALUES (InternalDomainID, InternalEntityID, InternalPropertyID, Collection);
        END IF;
    END IF;

    IF NameOverride IS NOT NULL THEN
        call overrideName(InternalDomainID, InternalEntityID, InternalPropertyID, NameOverride);
    END IF;

    IF DescOverride IS NOT NULL THEN
        call overrideDesc(InternalDomainID, InternalEntityID, InternalPropertyID, DescOverride);
    END IF;

END;
//
delimiter ;