SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,ALLOW_INVALID_DATES';


-- -----------------------------------------------------
-- Placeholder table for view `zzzNodeWorksetV`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `zzzNodeWorksetV` (`nodeId` INT, `connId` INT, `isMain` INT, `added` INT);

-- -----------------------------------------------------
-- Placeholder table for view `nodeTypeV`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `nodeTypeV` (`id` INT, `name` INT, `code` INT, `internalTypeId` INT, `forceSinglePhoto` INT, `canEditType` INT, `canEditNode` INT, `canHavePhoto` INT, `canHaveRatingPoints` INT, `canLinkFrom` INT, `canLinkTo` INT, `canSearch` INT, `displayInObject` INT, `displayInObjectList` INT, `searchAnd` INT, `displayNode` INT, `requiredForObject` INT, `sorter` INT, `languageId` INT, `added` INT, `changed` INT);

-- -----------------------------------------------------
-- Placeholder table for view `nodeTypeVL`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `nodeTypeVL` (`id` INT, `languageId` INT, `name` INT);

-- -----------------------------------------------------
-- Placeholder table for view `userV`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `userV` (`id` INT, `name` INT, `phone` INT, `email` INT, `login` INT, `password` INT, `roleId` INT, `enabled` INT, `birthDate` INT, `preferredLanguage` INT, `token` INT, `phoneConfirmationCode` INT, `phoneConfirmationExpiry` INT, `emailConfirmationCode` INT, `emailConfirmationExpiry` INT, `added` INT, `addedUserId` INT, `addedUserName` INT, `addedIp` INT, `changed` INT, `changedUserId` INT, `changedUserName` INT, `changedIp` INT);

-- -----------------------------------------------------
-- Placeholder table for view `userOauthV`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `userOauthV` (`userId` INT, `oauthProviderId` INT, `identity` INT, `added` INT, `changed` INT);

-- -----------------------------------------------------
-- Placeholder table for view `allowedNodeLinkCanCreateV`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `allowedNodeLinkCanCreateV` (`srcNodeTypeId` INT, `dstNodeTypeId` INT);

-- -----------------------------------------------------
-- Placeholder table for view `tagV`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `tagV` (`id` INT, `typeId` INT, `forceSingleNode` INT, `isRoot` INT, `typeCode` INT, `typeName` INT, `internalTypeId` INT, `typeCanEdit` INT, `typeCanHavePhoto` INT, `typeCanHaveRatingPoints` INT, `typeCanSearch` INT, `typeDisplayInObject` INT, `typeDisplayInObjectList` INT, `typeSearchAnd` INT, `typeRequiredForObject` INT, `typeSorter` INT, `name` INT, `sorter` INT, `canSearch` INT, `canSearchRaw` INT, `displayInObject` INT, `displayInObjectRaw` INT, `displayInObjectList` INT, `displayInObjectListRaw` INT, `factor` INT, `ratingPoints` INT, `enabled` INT, `attributeDataType` INT, `attributeCode` INT, `required` INT, `sortable` INT, `uofmPre` INT, `uofmPost` INT, `geoRoot` INT, `languageId` INT, `added` INT, `addedUserId` INT, `addedUserName` INT, `addedIp` INT, `changed` INT, `changedUserId` INT, `changedUserName` INT, `changedIp` INT);

-- -----------------------------------------------------
-- Placeholder table for view `tagVL`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `tagVL` (`id` INT, `languageId` INT, `name` INT);

-- -----------------------------------------------------
-- Placeholder table for view `allowedNodeLinkV`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `allowedNodeLinkV` (`srcNodeTypeId` INT, `dstNodeTypeId` INT, `essential` INT, `singleSrc` INT, `added` INT, `changed` INT);

-- -----------------------------------------------------
-- Placeholder table for view `searchResultV`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `searchResultV` (`sessionId` INT, `nodeId` INT, `collapsed` INT, `extraObjects` INT);

-- -----------------------------------------------------
-- Placeholder table for view `searchCriterionV`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `searchCriterionV` (`nodeId` INT);

-- -----------------------------------------------------
-- Placeholder table for view `searchCriterionNumericV`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `searchCriterionNumericV` (`nodeId` INT, `valueLow` INT, `valueHigh` INT);

-- -----------------------------------------------------
-- Placeholder table for view `searchCriterionDatetimeV`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `searchCriterionDatetimeV` (`nodeId` INT, `valueLow` INT, `valueHigh` INT);

-- -----------------------------------------------------
-- Placeholder table for view `nodeV`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `nodeV` (`id` INT, `nodeTypeId` INT, `forceSingleNode` INT, `name` INT, `isRoot` INT, `typeName` INT, `typeCode` INT, `internalTypeId` INT, `canEditType` INT, `canEditNode` INT, `canHavePhoto` INT, `canHaveRatingPoints` INT, `canLinkFrom` INT, `canLinkTo` INT, `canSearch` INT, `displayInObject` INT, `displayInObjectList` INT, `searchAnd` INT, `requiredForObject` INT, `sorter` INT, `languageId` INT, `added` INT, `addedUserId` INT, `addedUserName` INT, `addedIp` INT, `changed` INT, `changedUserId` INT, `changedUserName` INT, `changedIp` INT);

-- -----------------------------------------------------
-- Placeholder table for view `nodeLinkV`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `nodeLinkV` (`nodeLinkTypeId` INT, `code` INT, `isDefault` INT, `srcNodeId` INT, `dstNodeId` INT, `metric` INT, `added` INT);

-- -----------------------------------------------------
-- Placeholder table for view `nodeLinkOwnsV`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `nodeLinkOwnsV` (`nodeLinkTypeId` INT, `srcNodeId` INT, `dstNodeId` INT, `metric` INT, `added` INT);

-- -----------------------------------------------------
-- Placeholder table for view `allowedNodeLinkByNodeV`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `allowedNodeLinkByNodeV` (`srcNodeId` INT, `srcNodeTypeId` INT, `srcDisplayNode` INT, `dstNodeId` INT, `dstNodeTypeId` INT, `dstDisplayNode` INT, `essential` INT, `singleSrc` INT, `added` INT, `changed` INT);

-- -----------------------------------------------------
-- Placeholder table for view `nodeRootLinkV`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `nodeRootLinkV` (`id` INT, `nodeTypeId` INT, `forceSingleNode` INT, `name` INT, `isRoot` INT, `displayNode` INT, `typeCode` INT, `typeCanSearch` INT, `typeDisplayInObject` INT, `typeDisplayInObjectList` INT, `typeRequiredForObject` INT, `typeSorter` INT, `added` INT, `addedUserId` INT, `addedUserName` INT, `addedIp` INT, `changed` INT, `changedUserId` INT, `changedUserName` INT, `changedIp` INT, `nodeLinkTypeId` INT, `dstNodeId` INT, `metric` INT);

-- -----------------------------------------------------
-- Placeholder table for view `objectV`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `objectV` (`id` INT, `address` INT, `description` INT, `statusId` INT, `statusName` INT, `public` INT, `userEditable` INT, `userDraftable` INT, `latitude` INT, `longitude` INT, `rating` INT, `noteForModerator` INT, `noteForUser` INT, `alias` INT, `languageId` INT, `added` INT, `addedUserId` INT, `addedUserName` INT, `addedIp` INT, `changed` INT, `changedUserId` INT, `changedUserName` INT, `changedIp` INT);

-- -----------------------------------------------------
-- Placeholder table for view `objectForUserV`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `objectForUserV` (`id` INT);

-- -----------------------------------------------------
-- Placeholder table for view `_nodeRecommendLinkV`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `_nodeRecommendLinkV` (`nodeLinkTypeId` INT, `nodeLinkTypeCode` INT, `srcNodeId` INT, `srcNodeTypeId` INT, `srcMetric` INT, `srcMin` INT, `viaNodeId` INT, `dstNodeId` INT, `dstNodeTypeId` INT, `dstMetric` INT, `dstMin` INT, `essential` INT, `existingMetric` INT, `singleSrcExists` INT);

-- -----------------------------------------------------
-- Placeholder table for view `nodeRecommendLinkV`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `nodeRecommendLinkV` (`nodeLinkTypeId` INT, `nodeLinkTypeCode` INT, `srcNodeId` INT, `srcNodeTypeId` INT, `srcMetric` INT, `viaNodeId` INT, `dstNodeId` INT, `dstNodeTypeId` INT, `dstMetric` INT, `essential` INT, `singleSrc` INT);

-- -----------------------------------------------------
-- Placeholder table for view `tagForAdminV`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `tagForAdminV` (`tagNames` INT, `tagNamesSearch` INT, `attributeNames` INT, `attributeNamesSearch` INT);

-- -----------------------------------------------------
-- Placeholder table for view `nodeAncestryV`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `nodeAncestryV` (`nodeId` INT, `tagName` INT);

-- -----------------------------------------------------
-- Placeholder table for view `searchResultTagV`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `searchResultTagV` (`id` INT);

-- -----------------------------------------------------
-- Placeholder table for view `searchResultObjectV`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `searchResultObjectV` (`sessionId` INT);

-- -----------------------------------------------------
-- Placeholder table for view `tagChildV`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `tagChildV` (`srcId` INT, `srcTypeId` INT, `srcTypeCode` INT, `srcTypeName` INT, `srcInternalTypeId` INT, `srcCanEdit` INT, `srcTypeCanSearch` INT, `srcTypeDisplayInObject` INT, `srcTypeDisplayInObjectList` INT, `srcTypeSorter` INT, `srcName` INT, `srcSorter` INT, `srcCanSearch` INT, `srcCanSearchRaw` INT, `srcDisplayInObject` INT, `srcDisplayInObjectRaw` INT, `srcDisplayInObjectList` INT, `srcDisplayInObjectListRaw` INT, `srcFactor` INT, `srcRatingPoints` INT, `srcEnabled` INT, `srcAttributeDataType` INT, `srcRequired` INT, `dstId` INT, `dstTypeId` INT, `dstTypeCode` INT, `dstTypeName` INT, `dstInternalTypeId` INT, `dstCanEdit` INT, `dstTypeCanSearch` INT, `dstTypeDisplayInObject` INT, `dstTypeDisplayInObjectList` INT, `dstTypeSorter` INT, `dstName` INT, `dstSorter` INT, `dstCanSearch` INT, `dstCanSearchRaw` INT, `dstDisplayInObject` INT, `dstDisplayInObjectRaw` INT, `dstDisplayInObjectList` INT, `dstDisplayInObjectListRaw` INT, `dstFactor` INT, `dstRatingPoints` INT, `dstEnabled` INT, `dstAttributeDataType` INT, `dstRequired` INT, `srcIsRoot` INT, `srcGeoRoot` INT, `dstAncestry` INT);

-- -----------------------------------------------------
-- Placeholder table for view `tagRecommendChildV`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `tagRecommendChildV` (`srcId` INT, `srcTypeId` INT, `srcTypeCode` INT, `srcTypeName` INT, `srcInternalTypeId` INT, `srcCanEdit` INT, `srcTypeCanSearch` INT, `srcTypeDisplayInObject` INT, `srcTypeDisplayInObjectList` INT, `srcTypeSorter` INT, `srcName` INT, `srcSorter` INT, `srcCanSearch` INT, `srcCanSearchRaw` INT, `srcDisplayInObject` INT, `srcDisplayInObjectRaw` INT, `srcDisplayInObjectList` INT, `srcDisplayInObjectListRaw` INT, `srcFactor` INT, `srcRatingPoints` INT, `srcEnabled` INT, `srcAttributeDataType` INT, `srcRequired` INT, `dstId` INT, `dstTypeId` INT, `dstTypeCode` INT, `dstTypeName` INT, `dstInternalTypeId` INT, `dstCanEdit` INT, `dstTypeCanSearch` INT, `dstTypeDisplayInObject` INT, `dstTypeDisplayInObjectList` INT, `dstTypeSorter` INT, `dstName` INT, `dstSorter` INT, `dstCanSearch` INT, `dstCanSearchRaw` INT, `dstDisplayInObject` INT, `dstDisplayInObjectRaw` INT, `dstDisplayInObjectList` INT, `dstDisplayInObjectListRaw` INT, `dstFactor` INT, `dstRatingPoints` INT, `dstEnabled` INT, `dstAttributeDataType` INT, `dstRequired` INT, `srcIsRoot` INT, `srcGeoRoot` INT, `dstAncestry` INT, `srcMetric` INT, `dstMetric` INT, `essential` INT);

-- -----------------------------------------------------
-- Placeholder table for view `tagParentV`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `tagParentV` (`srcId` INT, `srcTypeId` INT, `srcTypeCode` INT, `srcTypeName` INT, `srcInternalTypeId` INT, `srcCanEdit` INT, `srcTypeCanSearch` INT, `srcTypeDisplayInObject` INT, `srcTypeDisplayInObjectList` INT, `srcTypeSorter` INT, `srcName` INT, `srcSorter` INT, `srcCanSearch` INT, `srcCanSearchRaw` INT, `srcDisplayInObject` INT, `srcDisplayInObjectRaw` INT, `srcDisplayInObjectList` INT, `srcDisplayInObjectListRaw` INT, `srcFactor` INT, `srcRatingPoints` INT, `srcEnabled` INT, `srcAttributeDataType` INT, `srcRequired` INT, `dstId` INT, `dstTypeId` INT, `dstTypeCode` INT, `dstTypeName` INT, `dstInternalTypeId` INT, `dstCanEdit` INT, `dstTypeCanSearch` INT, `dstTypeDisplayInObject` INT, `dstTypeDisplayInObjectList` INT, `dstTypeSorter` INT, `dstName` INT, `dstSorter` INT, `dstCanSearch` INT, `dstCanSearchRaw` INT, `dstDisplayInObject` INT, `dstDisplayInObjectRaw` INT, `dstDisplayInObjectList` INT, `dstDisplayInObjectListRaw` INT, `dstFactor` INT, `dstRatingPoints` INT, `dstEnabled` INT, `dstAttributeDataType` INT, `dstRequired` INT, `srcIsRoot` INT, `srcGeoRoot` INT, `srcAncestry` INT);

-- -----------------------------------------------------
-- Placeholder table for view `tagRecommendParentV`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `tagRecommendParentV` (`srcId` INT, `srcTypeId` INT, `srcTypeCode` INT, `srcTypeName` INT, `srcInternalTypeId` INT, `srcCanEdit` INT, `srcTypeCanSearch` INT, `srcTypeDisplayInObject` INT, `srcTypeDisplayInObjectList` INT, `srcTypeSorter` INT, `srcName` INT, `srcSorter` INT, `srcCanSearch` INT, `srcCanSearchRaw` INT, `srcDisplayInObject` INT, `srcDisplayInObjectRaw` INT, `srcDisplayInObjectList` INT, `srcDisplayInObjectListRaw` INT, `srcFactor` INT, `srcRatingPoints` INT, `srcEnabled` INT, `srcAttributeDataType` INT, `srcRequired` INT, `dstId` INT, `dstTypeId` INT, `dstTypeCode` INT, `dstTypeName` INT, `dstInternalTypeId` INT, `dstCanEdit` INT, `dstTypeCanSearch` INT, `dstTypeDisplayInObject` INT, `dstTypeDisplayInObjectList` INT, `dstTypeSorter` INT, `dstName` INT, `dstSorter` INT, `dstCanSearch` INT, `dstCanSearchRaw` INT, `dstDisplayInObject` INT, `dstDisplayInObjectRaw` INT, `dstDisplayInObjectList` INT, `dstDisplayInObjectListRaw` INT, `dstFactor` INT, `dstRatingPoints` INT, `dstEnabled` INT, `dstAttributeDataType` INT, `dstRequired` INT, `srcIsRoot` INT, `srcGeoRoot` INT, `srcAncestry` INT, `srcMetric` INT, `dstMetric` INT, `essential` INT);

-- -----------------------------------------------------
-- Placeholder table for view `objectStatusV`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `objectStatusV` (`id` INT, `name` INT, `public` INT, `userEditable` INT, `userDraftable` INT, `sorter` INT);

-- -----------------------------------------------------
-- Placeholder table for view `attributeV`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `attributeV` (`id` INT, `decimalMin` INT, `decimalMax` INT, `decimalRound` INT, `datetimeStorageId` INT, `textIsLong` INT);

-- -----------------------------------------------------
-- Placeholder table for view `_nodeRecommendLink2V`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `_nodeRecommendLink2V` (`nodeLinkTypeId` INT, `nodeLinkTypeCode` INT, `srcNodeId` INT, `srcNodeTypeId` INT, `dstNodeId` INT, `dstNodeTypeId` INT, `essential` INT, `singleSrc` INT, `linked` INT, `canLink` INT);

-- -----------------------------------------------------
-- Placeholder table for view `searchTagV`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `searchTagV` (`criterionId` INT, `valueLowNumeric` INT, `valueHighNumeric` INT, `valueLowDatetime` INT, `valueHighDatetime` INT, `resultId` INT, `collapsed` INT, `extraObjects` INT);

-- -----------------------------------------------------
-- Placeholder table for view `attributeDatetimeStorageFormatV`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `attributeDatetimeStorageFormatV` (`id` INT, `languageId` INT, `datetimeStorageId` INT, `storageFormat` INT, `name` INT, `outputFormat` INT, `outputFormatJs` INT);

-- -----------------------------------------------------
-- Placeholder table for view `objectWithTagV`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `objectWithTagV` (`srcId` INT, `srcTypeId` INT, `srcTypeCode` INT, `srcTypeName` INT, `srcInternalTypeId` INT, `srcCanEdit` INT, `srcTypeCanSearch` INT, `srcTypeDisplayInObject` INT, `srcTypeDisplayInObjectList` INT, `srcTypeSorter` INT, `srcName` INT, `srcSorter` INT, `srcCanSearch` INT, `srcCanSearchRaw` INT, `srcDisplayInObject` INT, `srcDisplayInObjectRaw` INT, `srcDisplayInObjectList` INT, `srcDisplayInObjectListRaw` INT, `srcFactor` INT, `srcRatingPoints` INT, `srcEnabled` INT, `srcAttributeDataType` INT, `srcAttributeCode` INT, `srcRequired` INT, `srcSortable` INT, `srcUofmPre` INT, `srcUofmPost` INT, `srcLanguageId` INT, `srcIsRoot` INT, `srcGeoRoot` INT, `srcDecimalMin` INT, `srcDecimalMax` INT, `srcDecimalRound` INT, `srcDatetimeStorageId` INT, `srcTextIsLong` INT, `srcDatetimeStorageFormat` INT, `srcDatetimeName` INT, `srcDatetimeOutputFormat` INT, `srcDatetimeOutputFormatJs` INT, `srcNumericValue` INT, `srcDatetimeValue` INT, `srcTextValue` INT, `linked` INT, `canLink` INT, `objectId` INT);

-- -----------------------------------------------------
-- Placeholder table for view `nodeRootGeoV`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `nodeRootGeoV` (`id` INT, `nodeTypeId` INT, `forceSingleNode` INT, `name` INT, `isRoot` INT, `displayNode` INT, `typeCode` INT, `typeCanSearch` INT, `typeDisplayInObject` INT, `typeDisplayInObjectList` INT, `typeRequiredForObject` INT, `typeSorter` INT, `added` INT, `addedUserId` INT, `addedUserName` INT, `addedIp` INT, `changed` INT, `changedUserId` INT, `changedUserName` INT, `changedIp` INT, `dstNodeId` INT, `metric` INT);

-- -----------------------------------------------------
-- Placeholder table for view `attributeDatetimeFormatV`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `attributeDatetimeFormatV` (`id` INT, `name` INT, `storageFormat` INT, `outputFormat` INT, `outputFormatJs` INT, `sorter` INT, `languageId` INT, `added` INT, `changed` INT);

-- -----------------------------------------------------
-- Placeholder table for view `imageV`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `imageV` (`id` INT, `imageId` INT, `nodeId` INT, `forceSinglePhoto` INT, `name` INT, `filenameOriginal` INT, `filenameFs` INT, `display` INT, `sorter` INT, `imageTypeId` INT, `videoType` INT, `url` INT, `extraId` INT, `added` INT, `addedUserId` INT, `addedUserName` INT, `addedIp` INT, `changed` INT, `changedUserId` INT, `changedUserName` INT, `changedIp` INT);

-- -----------------------------------------------------
-- Placeholder table for view `templateV`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `templateV` (`nodeId` INT, `srcNodeTypeId` INT, `added` INT);

-- -----------------------------------------------------
-- Placeholder table for view `_nodeRecommendLink3V`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `_nodeRecommendLink3V` (`nodeLinkTypeId` INT, `nodeLinkTypeCode` INT, `srcNodeId` INT, `srcNodeTypeId` INT, `dstNodeId` INT, `dstNodeTypeId` INT, `essential` INT, `singleSrc` INT, `linked` INT, `canLink` INT);

-- -----------------------------------------------------
-- Placeholder table for view `nodeLinkChainV`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `nodeLinkChainV` (`nodeLinkTypeId` INT, `srcNodeId` INT, `midNodeId` INT, `dstNodeId` INT, `metric1` INT, `metric2` INT, `added1` INT, `added2` INT);

-- -----------------------------------------------------
-- Placeholder table for view `nodeLinkForkSrcV`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `nodeLinkForkSrcV` (`nodeLinkTypeId` INT, `srcNodeId` INT, `dstNodeId1` INT, `dstNodeId2` INT, `metric1` INT, `metric2` INT, `added1` INT, `added2` INT);

-- -----------------------------------------------------
-- Placeholder table for view `_nodeRecommendLink4V`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `_nodeRecommendLink4V` (`nodeLinkTypeId` INT, `nodeLinkTypeCode` INT, `srcNodeId` INT, `srcNodeTypeId` INT, `dstNodeId` INT, `dstNodeTypeId` INT, `essential` INT, `singleSrc` INT, `linked` INT, `canLink` INT);

-- -----------------------------------------------------
-- Placeholder table for view `objectWithTagLinkedV`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `objectWithTagLinkedV` (`srcId` INT, `srcTypeId` INT, `srcTypeCode` INT, `srcTypeName` INT, `srcInternalTypeId` INT, `srcCanEdit` INT, `srcTypeCanSearch` INT, `srcTypeDisplayInObject` INT, `srcTypeDisplayInObjectList` INT, `srcTypeSorter` INT, `srcName` INT, `srcSorter` INT, `srcCanSearch` INT, `srcCanSearchRaw` INT, `srcDisplayInObject` INT, `srcDisplayInObjectRaw` INT, `srcDisplayInObjectList` INT, `srcDisplayInObjectListRaw` INT, `srcFactor` INT, `srcRatingPoints` INT, `srcEnabled` INT, `srcAttributeDataType` INT, `srcAttributeCode` INT, `srcRequired` INT, `srcSortable` INT, `srcUofmPre` INT, `srcUofmPost` INT, `srcLanguageId` INT, `srcIsRoot` INT, `srcGeoRoot` INT, `srcDecimalMin` INT, `srcDecimalMax` INT, `srcDecimalRound` INT, `srcDatetimeStorageId` INT, `srcTextIsLong` INT, `srcDatetimeStorageFormat` INT, `srcDatetimeName` INT, `srcDatetimeOutputFormat` INT, `srcDatetimeOutputFormatJs` INT, `srcNumericValue` INT, `srcDatetimeValue` INT, `srcTextValue` INT, `linked` INT, `canLink` INT, `objectId` INT);

-- -----------------------------------------------------
-- Placeholder table for view `nodeLinkChainTransitionV`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `nodeLinkChainTransitionV` (`nodeLinkTypeId` INT, `srcNodeId` INT, `midNodeId` INT, `dstNodeId` INT, `metric1` INT, `metric2` INT, `added1` INT, `added2` INT);

-- -----------------------------------------------------
-- Placeholder table for view `nodeLinkDirectV`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `nodeLinkDirectV` (`nodeLinkTypeId` INT, `srcNodeId` INT, `dstNodeId` INT, `metric` INT, `added` INT);

-- -----------------------------------------------------
-- Placeholder table for view `nodeAncestryDataV`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `nodeAncestryDataV` (`srcNodeId` INT, `srcName` INT, `srcTypeId` INT, `srcInternalTypeId` INT, `srcTypeName` INT, `srcNumericValue` INT, `srcDatetimeValue` INT, `srcDatetimeOutputFormat` INT, `srcTextValue` INT, `dstNodeId` INT, `dstTypeId` INT, `languageId` INT, `languageIsDefault` INT, `ancestrySorter` INT);

-- -----------------------------------------------------
-- Placeholder table for view `objectWithUserV`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `objectWithUserV` (`objectId` INT);

-- -----------------------------------------------------
-- Placeholder table for view `_nodeV`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `_nodeV` (`id` INT, `nodeTypeId` INT, `forceSingleNode` INT, `forceSinglePhoto` INT, `name` INT, `isRoot` INT, `displayNode` INT, `added` INT, `addedUserId` INT, `addedUserName` INT, `addedIp` INT, `changed` INT, `changedUserId` INT, `changedUserName` INT, `changedIp` INT);

-- -----------------------------------------------------
-- Placeholder table for view `attributeSortV`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `attributeSortV` (`id` INT, `typeName` INT, `typeCode` INT, `name` INT, `sorter` INT, `code` INT, `languageId` INT, `added` INT, `addedUserId` INT, `addedUserName` INT, `addedIp` INT, `changed` INT, `changedUserId` INT, `changedUserName` INT, `changedIp` INT);

-- -----------------------------------------------------
-- Placeholder table for view `searchResultObjectSortedV`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `searchResultObjectSortedV` (`sortAttributeId` INT, `sorterAsc` INT, `sorterDesc` INT);

-- -----------------------------------------------------
-- Placeholder table for view `searchAttributeSortV`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `searchAttributeSortV` (`id` INT);

-- -----------------------------------------------------
-- Placeholder table for view `zzzIntsV`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `zzzIntsV` (`n` INT);

-- -----------------------------------------------------
-- Placeholder table for view `zzzInts100V`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `zzzInts100V` (`n` INT);

-- -----------------------------------------------------
-- Placeholder table for view `zzzInts1000V`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `zzzInts1000V` (`n` INT);

-- -----------------------------------------------------
-- Placeholder table for view `zzzInts10000V`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `zzzInts10000V` (`n` INT);

-- -----------------------------------------------------
-- function __nodeWorkset_Cleanup
-- -----------------------------------------------------
DROP function IF EXISTS `__nodeWorkset_Cleanup`;

DELIMITER $$$



-- workset functions

create function __nodeWorkset_Cleanup() returns int unsigned
not deterministic
begin
    delete from zzzNodeWorksetT where added < date_sub(now(), interval 1 day);
    return 1;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function __nodeWorkset_GetLevel
-- -----------------------------------------------------
DROP function IF EXISTS `__nodeWorkset_GetLevel`;

DELIMITER $$$



create function __nodeWorkset_GetLevel() returns int unsigned
return coalesce(@__nodeWorksetLevel, 0)$$$

DELIMITER ;

-- -----------------------------------------------------
-- function __nodeWorkset_Start
-- -----------------------------------------------------
DROP function IF EXISTS `__nodeWorkset_Start`;

DELIMITER $$$



create function __nodeWorkset_Start() returns int unsigned
not deterministic modifies sql data
begin
    set @__nodeWorksetLevel = coalesce(@__nodeWorksetLevel, 0) + 1;
    
    if __nodeWorkset_GetLevel() = 1 then
        delete from zzzNodeWorksetT where connId = connection_id();
    end if;
    return 1;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function __nodeWorkset_Update
-- -----------------------------------------------------
DROP function IF EXISTS `__nodeWorkset_Update`;

DELIMITER $$$



create function __nodeWorkset_Update(node_id int unsigned, direction int) returns int unsigned
comment 'direction=-1 - downwards; direction=1 - upwards; direction=0 - both; others - undefined'
not deterministic modifies sql data
begin
    if __nodeWorkset_GetLevel() > 0 then 
        insert zzzNodeWorksetT(nodeId, connId, isMain) 
        values (node_id, connection_id(), 1)
        on duplicate key update isMain = 1;

        -- downwards from source node
        insert zzzNodeWorksetT(nodeId, connId, isMain) 
        select l.srcNodeId, connection_id(), direction < 1
        from nodeLinkT l 
        where l.dstNodeId = node_id 
            and metric > 0
        on duplicate key update isMain = isMain or (direction < 1);

        -- upwards from source node
        insert ignore zzzNodeWorksetT(nodeId, connId, isMain) 
        select l.dstNodeId, connection_id(), direction > -1
        from nodeLinkT l 
        where l.srcNodeId = node_id 
            and metric > 0
        on duplicate key update isMain = isMain or (direction > -1);
    end if;
    
    return node_id;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function __nodeWorkset_End
-- -----------------------------------------------------
DROP function IF EXISTS `__nodeWorkset_End`;

DELIMITER $$$



create function __nodeWorkset_End() returns int unsigned
not deterministic modifies sql data
begin
    if __nodeWorkset_GetLevel() = 1 then
        set @1 = __nodeWorkset_Cleanup();
        delete from zzzNodeWorksetT where connId = connection_id();
    end if;

    set @__nodeWorksetLevel = greatest(coalesce(@__nodeWorksetLevel, 0) - 1, 0);

    return 1;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function __nodeLink_UpdateCreateExtraLinks
-- -----------------------------------------------------
DROP function IF EXISTS `__nodeLink_UpdateCreateExtraLinks`;

DELIMITER $$$



create function __nodeLink_UpdateCreateExtraLinks() returns int unsigned
not deterministic modifies sql data
begin
    repeat
        insert nodeLinkT(nodeLinkTypeId, srcNodeId, dstNodeId, metric)
        select l1.nodeLinkTypeId, l1.srcNodeId, l2.dstNodeId, (l1.metric + l2.metric)
        from nodeLinkT l1
        join nodeLinkT l2 on l1.nodeLinkTypeId = l2.nodeLinkTypeId and l1.dstNodeId = l2.srcNodeId
        join zzzNodeWorksetT ws1 on l1.srcNodeId = ws1.nodeId
        join zzzNodeWorksetT ws2 on l2.dstNodeId = ws2.nodeId
        left join nodeLinkT l3 on l1.nodeLinkTypeId = l3.nodeLinkTypeId
            and l3.srcNodeId = l1.srcNodeId
            and l3.dstNodeId = l2.dstNodeId
            and l3.metric <= l1.metric + l2.metric
        where l1.srcNodeId != l2.dstNodeId
            and l1.metric > 0
            and l2.metric > 0
            and ws1.connId = connection_id()
            and ws2.connId = connection_id()
            and l3.srcNodeId is null
        on duplicate key update
            nodeLinkT.metric = least(nodeLinkT.metric, l1.metric + l2.metric);
    until row_count() < 1 end repeat;

    return 1;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function __nodeLink_UpdateDeleteExtraLinks
-- -----------------------------------------------------
DROP function IF EXISTS `__nodeLink_UpdateDeleteExtraLinks`;

DELIMITER $$$



create function __nodeLink_UpdateDeleteExtraLinks() returns int unsigned
not deterministic modifies sql data
begin
    declare row_cnt int unsigned default 0;

    create temporary table if not exists zzNodeLinkDelete (
        nodeLinkTypeId int unsigned not null,
        srcNodeId int unsigned not null,
        dstNodeId int unsigned not null,
        metric int not null default 1,
        primary key(nodeLinkTypeId, srcNodeId, dstNodeId),
        unique(nodeLinkTypeId, dstNodeId, srcNodeId)
    ) engine=MEMORY;

    create temporary table if not exists zzNodeLinkDelete2 like zzNodeLinkDelete;
    delete from zzNodeLinkDelete;
    delete from zzNodeLinkDelete2;

    insert zzNodeLinkDelete(nodeLinkTypeId, srcNodeId, dstNodeId, metric)
    select l.nodeLinkTypeId, l.srcNodeId, l.dstNodeId, l.metric
    from nodeLinkT l
    join zzzNodeWorksetT ws1 on l.srcNodeId = ws1.nodeId
    join zzzNodeWorksetT ws2 on l.dstNodeId = ws2.nodeId
    where l.metric > 1
        and ws1.connId = connection_id()
        and ws2.connId = connection_id()
        and not exists (
            select 1
            from nodeLinkT l1
            join nodeLinkT l2 on l1.nodeLinkTypeId = l2.nodeLinkTypeId and l1.dstNodeId = l2.srcNodeId
            where l1.nodeLinkTypeId = l.nodeLinkTypeId
                and l1.srcNodeId = l.srcNodeId
                and l2.dstNodeId = l.dstNodeId
                and l1.metric > 0
                and l2.metric > 0
                and l.metric = l1.metric + l2.metric);
    set row_cnt = row_count();

    while row_cnt > 0 do
        insert ignore zzNodeLinkDelete2
        select * from zzNodeLinkDelete ld;

        insert ignore zzNodeLinkDelete(nodeLinkTypeId, srcNodeId, dstNodeId, metric)
        select l.nodeLinkTypeId, l.srcNodeId, l.dstNodeId, l.metric
        from nodeLinkT l
        join zzzNodeWorksetT ws1 on l.srcNodeId = ws1.nodeId
        join zzzNodeWorksetT ws2 on l.dstNodeId = ws2.nodeId
        join zzNodeLinkDelete2 l1 on l.nodeLinkTypeId = l1.nodeLinkTypeId and l.srcNodeId = l1.srcNodeId
        join nodeLinkT l2 on l.nodeLinkTypeId = l2.nodeLinkTypeId and l1.dstNodeId = l2.srcNodeId and l.dstNodeId = l2.dstNodeId
        where l.metric > 1
            and l2.metric > 0
            and l.metric = l1.metric + l2.metric
            and ws1.connId = connection_id()
            and ws2.connId = connection_id();

        set row_cnt = row_count();

        insert ignore zzNodeLinkDelete2
        select * from zzNodeLinkDelete ld;

        insert ignore zzNodeLinkDelete(nodeLinkTypeId, srcNodeId, dstNodeId, metric)
        select l.nodeLinkTypeId, l.srcNodeId, l.dstNodeId, l.metric
        from nodeLinkT l
        join zzzNodeWorksetT ws1 on l.srcNodeId = ws1.nodeId
        join zzzNodeWorksetT ws2 on l.dstNodeId = ws2.nodeId
        join nodeLinkT l1 on l.nodeLinkTypeId = l1.nodeLinkTypeId and l.srcNodeId = l1.srcNodeId
        join zzNodeLinkDelete2 l2 on l.nodeLinkTypeId = l2.nodeLinkTypeId and l1.dstNodeId = l2.srcNodeId and l.dstNodeId = l2.dstNodeId
        where l.metric > 1
            and l1.metric > 0
            and l.metric = l1.metric + l2.metric
            and ws1.connId = connection_id()
            and ws2.connId = connection_id();

        set row_cnt = row_cnt + row_count();
    end while;

    delete from nodeLinkT
    using nodeLinkT
    join zzNodeLinkDelete l on nodeLinkT.nodeLinkTypeId = l.nodeLinkTypeId
        and nodeLinkT.srcNodeId = l.srcNodeId
        and nodeLinkT.dstNodeId = l.dstNodeId
        and nodeLinkT.metric = l.metric;

    return 1;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function __nodeLink_UpdateRefreshExtraLinks
-- -----------------------------------------------------
DROP function IF EXISTS `__nodeLink_UpdateRefreshExtraLinks`;

DELIMITER $$$



create function __nodeLink_UpdateRefreshExtraLinks() returns int unsigned
not deterministic modifies sql data
begin
    declare deleted, added int unsigned;
    set deleted = __nodeLink_UpdateDeleteExtraLinks();
    set added = __nodeLink_UpdateCreateExtraLinks();
    return deleted + added;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function nodeLink_IsUpdateOn
-- -----------------------------------------------------
DROP function IF EXISTS `nodeLink_IsUpdateOn`;

DELIMITER $$$



create function nodeLink_IsUpdateOn() returns int unsigned
not deterministic
begin 
    return (__nodeWorkset_GetLevel() > 0);
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function __nodeLink_UpdateWorkSet
-- -----------------------------------------------------
DROP function IF EXISTS `__nodeLink_UpdateWorkSet`;

DELIMITER $$$



create function __nodeLink_UpdateWorkSet(node_id int unsigned, direction int) returns int unsigned
comment 'direction=-1 - downwards; direction=1 - upwards; direction=0 - both; others - undefined'
not deterministic
begin
    return __nodeWorkset_Update(node_id, direction);
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function __nodeLink_Create
-- -----------------------------------------------------
DROP function IF EXISTS `__nodeLink_Create`;

DELIMITER $$$



create function __nodeLink_Create(
    link_code varchar(30) binary, 
    src_id int unsigned, 
    dst_id int unsigned
) returns int unsigned
not deterministic modifies sql data
begin
    declare res int unsigned;

    if not exists (select 1 
                    from allowedNodeLinkT anl 
                    join nodeT src on anl.srcNodeTypeId = src.nodeTypeId 
                    join nodeT dst on anl.dstNodeTypeId = dst.nodeTypeId
                    where src.id = src_id and dst.id = dst_id) 
    then
        call raiseError2('nodeLink', 'create', src_id, dst_id, 'cannot create link between');
    end if;
    
    insert nodeLinkT(nodeLinkTypeId, srcNodeId, dstNodeId, metric)
    select nlt.id, src_id, dst_id, 1
    from nodeLinkTypeT nlt
    join nodeLinkTypeT nltdef on nltdef.isDefault = 'y'
    where nlt.code = coalesce(link_code, nltdef.code)
        and not exists (select 1 
                        from nodeLinkT l 
                        where l.nodeLinkTypeId = nlt.id 
                            and l.srcNodeId = src_id 
                            and l.dstNodeId = dst_id 
                            and l.metric <= 1)
    on duplicate key update nodeLinkT.metric = 1;
    
    set res = row_count();
    if nodeLink_IsUpdateOn() = 1 and res > 0 then
        set @1 = __nodeLink_UpdateWorkSet(src_id, -1) and __nodeLink_UpdateWorkSet(dst_id, 1);
    end if;

    return res;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function __nodeLink_Delete
-- -----------------------------------------------------
DROP function IF EXISTS `__nodeLink_Delete`;

DELIMITER $$$



create function __nodeLink_Delete(
    link_code varchar(30) binary, 
    src_id int unsigned, 
    dst_id int unsigned
) returns int unsigned
not deterministic modifies sql data
begin
    declare res int unsigned;

    delete from nodeLinkT
    using nodeLinkT
    join nodeLinkTypeT nlt on nodeLinkT.nodeLinkTypeId = nlt.id
    join nodeLinkTypeT nltdef on nltdef.isDefault = 'y'
    where nlt.code = coalesce(link_code, nltdef.code)
        and nodeLinkT.srcNodeId = src_id 
        and nodeLinkT.dstNodeId = dst_id 
        and nodeLinkT.metric = 1;

    set res = row_count();
    if nodeLink_IsUpdateOn() = 1 and res > 0 then
        set @1 = __nodeLink_UpdateWorkSet(src_id, -1) and __nodeLink_UpdateWorkSet(dst_id, 1);
    end if;

    return res;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function nodeLink_UpdateStart
-- -----------------------------------------------------
DROP function IF EXISTS `nodeLink_UpdateStart`;

DELIMITER $$$
create function nodeLink_UpdateStart() returns int unsigned
not deterministic modifies sql data
begin
    return __nodeWorkset_Start();
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function nodeLink_UpdateEnd
-- -----------------------------------------------------
DROP function IF EXISTS `nodeLink_UpdateEnd`;

DELIMITER $$$



create function nodeLink_UpdateEnd() returns int unsigned
not deterministic modifies sql data
begin
    if __nodeWorkset_GetLevel() = 1 then
        set @1 = __nodeLink_UpdateRefreshExtraLinks();
        set @2 = __nodeAncestry_Update();
        -- FORWARD: __object_UpdateRatings is defined in _080_object
        set @3 = __object_UpdateRatings();
        -- FORWARD: __object_UpdateAddresses is defined in _080_object
        set @4 = __object_UpdateAddresses();
    end if;
    set @5 = __nodeWorkset_End();
    return 1;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function __nodeLink_CreateExtraLinks
-- -----------------------------------------------------
DROP function IF EXISTS `__nodeLink_CreateExtraLinks`;

DELIMITER $$$



create function __nodeLink_CreateExtraLinks() returns int unsigned
not deterministic modifies sql data
begin
    declare row_cnt int unsigned default 0;
    repeat
        insert nodeLinkT(nodeLinkTypeId, srcNodeId, dstNodeId, metric)
        select l1.nodeLinkTypeId, l1.srcNodeId, l2.dstNodeId, (l1.metric + l2.metric)
        from nodeLinkT l1
        join nodeLinkT l2 on l1.nodeLinkTypeId = l2.nodeLinkTypeId and l1.dstNodeId = l2.srcNodeId
        left join nodeLinkT l3 on l3.nodeLinkTypeId = l1.nodeLinkTypeId 
            and l3.srcNodeId = l1.srcNodeId
            and l3.dstNodeId = l2.dstNodeId
            and l3.metric <= l1.metric + l2.metric
        where l1.srcNodeId != l2.dstNodeId
            and l1.metric > 0
            and l2.metric > 0
            and l3.srcNodeId is null
        on duplicate key update 
            nodeLinkT.metric = least(nodeLinkT.metric, l1.metric + l2.metric);

        set row_cnt = row_cnt + row_count();
    until row_count() < 1 end repeat;
    return row_cnt;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function __nodeLink_DeleteExtraLinks
-- -----------------------------------------------------
DROP function IF EXISTS `__nodeLink_DeleteExtraLinks`;

DELIMITER $$$



create function __nodeLink_DeleteExtraLinks() returns int unsigned
not deterministic modifies sql data
begin
    declare row_cnt int unsigned default 0;
    create temporary table if not exists zzNodeLinkDelete (
        nodeLinkTypeId int unsigned not null,
        srcNodeId int unsigned not null, 
        dstNodeId int unsigned not null, 
        metric int not null default 1,
        primary key(nodeLinkTypeId, srcNodeId, dstNodeId),
        unique(nodeLinkTypeId, dstNodeId, srcNodeId)
    ) engine=MEMORY;
    
    repeat
        delete from zzNodeLinkDelete;
        
        insert zzNodeLinkDelete(nodeLinkTypeId, srcNodeId, dstNodeId, metric)
        select l.nodeLinkTypeId, l.srcNodeId, l.dstNodeId, l.metric
        from nodeLinkT l
        where l.metric > 1
            and not exists (
                select 1 
                from nodeLinkT l1 
                join nodeLinkT l2 on l1.nodeLinkTypeId = l2.nodeLinkTypeId and l1.dstNodeId = l2.srcNodeId 
                where l1.nodeLinkTypeId = l.nodeLinkTypeId
                    and l1.srcNodeId = l.srcNodeId 
                    and l2.dstNodeId = l.dstNodeId
                    and l1.metric > 0
                    and l2.metric > 0
                    and l.metric = l1.metric + l2.metric);

        insert ignore zzNodeLinkDelete(nodeLinkTypeId, srcNodeId, dstNodeId, metric)
        select l.nodeLinkTypeId, l.srcNodeId, l.dstNodeId, l.metric
        from nodeLinkT l
        join nodeLinkT l2 on l.nodeLinkTypeId = l2.nodeLinkTypeId 
            and l.srcNodeId = l2.srcNodeId 
            and l.dstNodeId = l2.dstNodeId 
            and l.metric > l2.metric
        where l.metric > 1;
        
        delete from nodeLinkT
        using nodeLinkT 
        join zzNodeLinkDelete l on nodeLinkT.nodeLinkTypeId = l.nodeLinkTypeId 
            and nodeLinkT.srcNodeId = l.srcNodeId 
            and nodeLinkT.dstNodeId = l.dstNodeId 
            and nodeLinkT.metric = l.metric;

        set row_cnt = row_cnt + row_count();
    until row_count() < 1 end repeat;
    return row_cnt;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function __nodeLink_RefreshExtraLinks
-- -----------------------------------------------------
DROP function IF EXISTS `__nodeLink_RefreshExtraLinks`;

DELIMITER $$$



create function __nodeLink_RefreshExtraLinks() returns int unsigned
not deterministic modifies sql data
begin
    declare deleted, added int unsigned;
    set deleted = __nodeLink_DeleteExtraLinks();
    set added = __nodeLink_CreateExtraLinks();
    return deleted + added;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function nodeLink_Create
-- -----------------------------------------------------
DROP function IF EXISTS `nodeLink_Create`;

DELIMITER $$$



create function nodeLink_Create(
    link_code varchar(30) binary, 
    src_id int unsigned, 
    dst_id int unsigned
) returns int unsigned
not deterministic modifies sql data
begin
    declare res int unsigned;
    set @1 = nodeLink_UpdateStart();
    set res = __nodeLink_Create(link_code, src_id, dst_id);
    set @2 = nodeLink_UpdateEnd();
    return 1;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function nodeLink_Delete
-- -----------------------------------------------------
DROP function IF EXISTS `nodeLink_Delete`;

DELIMITER $$$



create function nodeLink_Delete(
    link_code varchar(30) binary, 
    src_id int unsigned, 
    dst_id int unsigned
) returns int unsigned
not deterministic modifies sql data
begin
    declare res int unsigned;
    set @1 = nodeLink_UpdateStart();
    set res = __nodeLink_Delete(link_code, src_id, dst_id);
    set @2 = nodeLink_UpdateEnd();
    return 1;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function __node_Create
-- -----------------------------------------------------
DROP function IF EXISTS `__node_Create`;

DELIMITER $$$



create function __node_Create(
    type_id_ int unsigned, 
    type_code_ varchar(30) binary, 
    name_ varchar(50)
) returns int unsigned
not deterministic modifies sql data
begin
    declare node_id int unsigned;
    declare type_id int unsigned default coalesce(type_id_, nodeType_GetByCode(type_code_));

    -- FORWARD: user_CurrentId, user_CurrentName and user_CurrentIp are defined in _060_user
    insert nodeT(
        nodeTypeId, 
        forceSinglePhoto, 
        name, 
        displayNode, 
        addedUserId,
        addedUserName,
        addedIp,
        changedUserId,
        changedUserName,
        changedIp)
    select 
        type_id, 
        nt.forceSinglePhoto, 
        name_, 
        nt.displayNode,
        user_CurrentId(),
        user_CurrentName(),
        user_CurrentIp(),
        user_CurrentId(),
        user_CurrentName(),
        user_CurrentIp()
    from nodeTypeT nt
    where nt.id = type_id;
    
    set node_id = last_insert_id();
    
    insert nodeLinkT(nodeLinkTypeId, srcNodeId, dstNodeId, metric)
    select nlt.id, node_id, node_id, 0
    from nodeLinkTypeT nlt
    where nlt.forceSelfLink = 'y';
    
    return node_id;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function __node_Delete
-- -----------------------------------------------------
DROP function IF EXISTS `__node_Delete`;

DELIMITER $$$



create function __node_Delete(node_id_ int unsigned) returns int unsigned
not deterministic modifies sql data
begin
    declare res int unsigned default 0;
    
    set @4 = __node_CanUpdate(node_id_, 'delete');
    
    if node_id_ is not null then
        set @1 = nodeLink_UpdateStart();
        set @2 = __nodeLink_UpdateWorkSet(node_id_, 0);
        delete from nodeT where id = node_id_;
        set res = row_count();
        set @3 = nodeLink_UpdateEnd();
    end if;
    
    return res;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function __nodeType_CanUpdate
-- -----------------------------------------------------
DROP function IF EXISTS `__nodeType_CanUpdate`;

DELIMITER $$$
create function __nodeType_CanUpdate(type_id_ int unsigned, action_ varchar(20)) returns int unsigned
deterministic
begin
    if (select canEditType from nodeTypeT where id = type_id_) != 'y' then
        call raiseError1('nodeType', action_, type_id_, 'cannot change uneditable node type');
    end if;
    
    return 1;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function __nodeType_Touch
-- -----------------------------------------------------
DROP function IF EXISTS `__nodeType_Touch`;

DELIMITER $$$



create function __nodeType_Touch(type_id_ int unsigned) returns int unsigned
not deterministic modifies sql data
begin
    update nodeTypeT set
        changed = current_timestamp()
    where id = type_id_;
    
    return type_id_;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function nodeType_Create
-- -----------------------------------------------------
DROP function IF EXISTS `nodeType_Create`;

DELIMITER $$$



create function nodeType_Create(
    internal_type_id_ varchar(10) binary,
    code_ varchar(30) binary,
    name_ varchar(50),
    can_have_photo_ enum('n', 'y'),
    can_have_rating_points_ enum('n', 'y'),
    can_search_ enum('n', 'y'),
    display_in_object_ enum('n', 'y'),
    display_in_object_list_ enum('n', 'y'),
    search_and_ enum('n', 'y'),
    required_for_object_ enum('n', 'y'),
    sorter_ int
) returns int unsigned
not deterministic modifies sql data
begin
    declare type_id int unsigned;

    insert nodeTypeT(
        internalTypeId,
        code,
        forceSinglePhoto,
        canEditType,
        canEditNode,
        canHavePhoto,
        canHaveRatingPoints,
        canLinkFrom,
        canLinkTo,
        canSearch,
        displayInObject,
        displayInObjectList,
        searchAnd,
        displayNode,
        requiredForObject,
        sorter) 
    values (
        internal_type_id_,
        code_,
        'y',    -- forceSinglePhoto
        'y',    -- canEditType
        'y',    -- canEditNode
        can_have_photo_,
        can_have_rating_points_,
        'y',    -- canLinkFrom
        'y',    -- canLinkTo
        can_search_,
        display_in_object_,
        display_in_object_list_,
        search_and_,
        'y',    -- displayNode
        required_for_object_,
        coalesce(sorter_, __nodeType_DefaultSorter()));
    
    set type_id = last_insert_id();
    
    insert nodeTypeTL(id, languageId, name)
    select type_id, l.id, name_
    from languageT l;
    
    return type_id;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function nodeType_Delete
-- -----------------------------------------------------
DROP function IF EXISTS `nodeType_Delete`;

DELIMITER $$$



create function nodeType_Delete(type_id_ int unsigned) returns int unsigned
not deterministic modifies sql data
begin
    set @1 = __nodeType_CanUpdate(type_id_, 'delete');
    
    delete from nodeTypeT where id = type_id_;
    
    return 1;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function nodeType_Update
-- -----------------------------------------------------
DROP function IF EXISTS `nodeType_Update`;

DELIMITER $$$



create function nodeType_Update(
    type_id_ int unsigned,
    can_have_photo_ enum('n', 'y'),
    can_have_rating_points_ enum('n', 'y'),
    can_search_ enum('n', 'y'),
    display_in_object_ enum('n', 'y'),
    display_in_object_list_ enum('n', 'y'),
    search_and_ enum('n', 'y'),
    required_for_object_ enum('n', 'y'),
    sorter_ int
) returns int unsigned
not deterministic modifies sql data
begin
    set @1 = __nodeType_CanUpdate(type_id_, 'update');
    
    update nodeTypeT set
        canHavePhoto = coalesce(can_have_photo_, canHavePhoto),
        canHaveRatingPoints = coalesce(can_have_rating_points_, canHaveRatingPoints),
        canSearch = coalesce(can_search_, canSearch),
        displayInObject = coalesce(display_in_object_, displayInObject),
        displayInObjectList = coalesce(display_in_object_list_, displayInObjectList),
        searchAnd = coalesce(search_and_, searchAnd),
        requiredForObject = coalesce(required_for_object_, requiredForObject),
        sorter = coalesce(sorter_, sorter)
    where id = type_id_;
    
    -- FORWARD: __tag_UpdateDefaults is defined in _07_tag
    set @1 = __tag_UpdateDefaults(null, type_id_);
    
    return __nodeType_Touch(type_id_);
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function __allowedNodeLink_CanLinkFrom
-- -----------------------------------------------------
DROP function IF EXISTS `__allowedNodeLink_CanLinkFrom`;

DELIMITER $$$
create function __allowedNodeLink_CanLinkFrom(node_type_id_ int unsigned) returns int unsigned
deterministic
begin
    if (select canLinkFrom from nodeTypeT where id = node_type_id_) != 'y' then
        call raiseError1('allowedNodeLink', 'linkFrom', node_type_id_, 'cannot create allowed link from the node type');
    end if;
    
    return 1;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function __allowedNodeLink_CanLinkTo
-- -----------------------------------------------------
DROP function IF EXISTS `__allowedNodeLink_CanLinkTo`;

DELIMITER $$$



create function __allowedNodeLink_CanLinkTo(node_type_id_ int unsigned) returns int unsigned
deterministic
begin
    if (select canLinkTo from nodeTypeT where id = node_type_id_) != 'y' then
        call raiseError1('allowedNodeLink', 'linkTo', node_type_id_, 'cannot create allowed link to the node type');
    end if;
    
    return 1;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function allowedNodeLink_Create
-- -----------------------------------------------------
DROP function IF EXISTS `allowedNodeLink_Create`;

DELIMITER $$$



create function allowedNodeLink_Create(
    src_node_type_id_ int unsigned,
    dst_node_type_id_ int unsigned,
    essential_ enum('n', 'y'),
    single_src_ enum('n', 'y')
) returns int unsigned
not deterministic modifies sql data
begin
    set @1 = __allowedNodeLink_CanLinkFrom(src_node_type_id_) and __allowedNodeLink_CanLinkTo(dst_node_type_id_);
    
    insert allowedNodeLinkT (
        srcNodeTypeId,
        dstNodeTypeId,
        essential,
        singleSrc) 
    values (
        src_node_type_id_,
        dst_node_type_id_,
        essential_,
        single_src_) 
    on duplicate key update 
        allowedNodeLinkT.essential = essential_,
        allowedNodeLinkT.singleSrc = single_src_;
    
    return 1;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function allowedNodeLink_Delete
-- -----------------------------------------------------
DROP function IF EXISTS `allowedNodeLink_Delete`;

DELIMITER $$$



create function allowedNodeLink_Delete(
    src_node_type_id_ int unsigned,
    dst_node_type_id_ int unsigned
) returns int unsigned
not deterministic modifies sql data
begin
    set @1 = __allowedNodeLink_CanLinkFrom(src_node_type_id_) and __allowedNodeLink_CanLinkTo(dst_node_type_id_);

    delete from allowedNodeLinkT 
    where srcNodeTypeId = src_node_type_id_
        and dstNodeTypeId = dst_node_type_id_;
        
    return 1;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function allowedNodeLink_Update
-- -----------------------------------------------------
DROP function IF EXISTS `allowedNodeLink_Update`;

DELIMITER $$$



create function allowedNodeLink_Update(
    src_node_type_id_ int unsigned,
    dst_node_type_id_ int unsigned,
    essential_ enum('n', 'y'),
    single_src_ enum('n', 'y')
) returns int unsigned
begin
    update allowedNodeLinkT set
        essential = coalesce(essential_, essential),
        singleSrc = coalesce(single_src_, singleSrc),
        changed = current_timestamp()
    where srcNodeTypeId = src_node_type_id_
        and dstNodeTypeId = dst_node_type_id_;
    
    return 1;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function __user_ValidateId
-- -----------------------------------------------------
DROP function IF EXISTS `__user_ValidateId`;

DELIMITER $$$
create function __user_ValidateId(user_id_ int unsigned) returns int unsigned
deterministic reads sql data
begin
    declare user_id int unsigned;
    
    select u.id into user_id 
    from userT u
    join nodeT n on u.id = n.id
    join nodeTypeT nt on n.nodeTypeId = nt.id
    where u.id = user_id_ 
        and nt.internalTypeId = 'user';
    
    if user_id is null then
        call raiseError1('user', 'validate', user_id_, 'user does not exist');
    end if;

    return 1;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function user_Create
-- -----------------------------------------------------
DROP function IF EXISTS `user_Create`;

DELIMITER $$$



create function user_Create(
    name_ varchar(255), phone_ varchar(50), email_ varchar(255), login_ varchar(255), password_ varchar(100),
    role_id_ varchar(30) binary, enabled_ enum('n', 'y'),
    birthdate_ date, 
    preferred_language_ char(3), 
    token_ varchar(20)
) returns int unsigned
not deterministic modifies sql data
begin
    declare user_id int unsigned;
    
    set user_id = node_LinkDefaults(__node_Create(null, 'user', name_));
    
    insert userT(
        id,
        name, phone, email, login, password,
        roleId, enabled,
        birthDate, 
        preferredLanguage, 
        token,
        phoneConfirmationCode, emailConfirmationCode) 
    select
        user_id,
        name_, phone_, email_, login_, password_,
        coalesce(role_id_, ur.id), enabled_,
        birthdate_, 
        preferred_language_, 
        token_,
        '', ''
    from userRoleT ur
    where ur.isDefault = 'y';
    
    return user_id;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function user_Delete
-- -----------------------------------------------------
DROP function IF EXISTS `user_Delete`;

DELIMITER $$$



create function user_Delete(user_id_ int unsigned) returns int unsigned
not deterministic modifies sql data
begin
    set @1 = __user_ValidateId(user_id_);
    return __node_Delete(user_id_);
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function nodeType_UpdateLocal
-- -----------------------------------------------------
DROP function IF EXISTS `nodeType_UpdateLocal`;

DELIMITER $$$



create function nodeType_UpdateLocal(
    type_id_ int unsigned,
    lang_id_ char(3),
    name_ varchar(50)
) returns int unsigned
not deterministic modifies sql data
begin
    set @1 = __nodeType_CanUpdate(type_id_, 'update');
    
    insert nodeTypeTL(id, languageId, name)
    select type_id_, l.id, name_
    from languageT l
    where l.id = coalesce(lang_id_, l.id)
    on duplicate key update nodeTypeTL.name = coalesce(name_, nodeTypeTL.name);

    return __nodeType_Touch(type_id_);
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function user_Update
-- -----------------------------------------------------
DROP function IF EXISTS `user_Update`;

DELIMITER $$$



create function user_Update(
    user_id_ int unsigned,
    name_ varchar(255), phone_ varchar(50), 
    email_ varchar(255), reset_email_ enum('n', 'y'), 
    login_ varchar(255), password_ varchar(100),
    role_id_ varchar(30) binary, enabled_ enum('n', 'y'),
    birthdate_ date, reset_birthdate_ enum('n', 'y'), 
    preferred_language_ char(3), reset_preferred_language_ enum('n', 'y'),
    token_ varchar(20), reset_token_ enum('n', 'y')
) returns int unsigned
not deterministic modifies sql data
begin
    declare current_phone, new_phone varchar(50);
    declare current_email, new_email varchar(255);

    set @1 = __user_ValidateId(user_id_);
    
    select 
        phone, email, coalesce(phone_, phone), if(reset_email_ = 'y', null, coalesce(email_, email))
    into 
        current_phone, current_email, new_phone, new_email
    from userT
    where id = user_id_;
    
    set @2 = __node_Update(user_id_, name_);
    
    update userT set
        name = coalesce(name_, name), 
        phone = new_phone, 
        email = new_email, 
        login = coalesce(login_, login), 
        password = [REDACTED] password),
        
        roleId = coalesce(role_id_, roleId), 
        enabled = coalesce(enabled_, enabled),
        
        birthDate = if(reset_birthdate_ = 'y', null, coalesce(birthdate_, birthDate)),
        preferredLanguage = if(reset_preferred_language_ = 'y', null, coalesce(preferred_language_, preferredLanguage)),
        token = if(reset_token_ = 'y', null, coalesce(token_, token)),
        
        phoneConfirmationCode = if(
                                    phoneConfirmationCode is null 
                                        and current_phone != new_phone 
                                        and new_phone is not null, 
                                    '', 
                                    phoneConfirmationCode),
        emailConfirmationCode = if(
                                    emailConfirmationCode is null 
                                        and current_email != new_email
                                        and new_email is not null, 
                                    '', 
                                    emailConfirmationCode)
    where id = user_id_;
    
    return user_id_;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function user_SetConfirmationCode
-- -----------------------------------------------------
DROP function IF EXISTS `user_SetConfirmationCode`;

DELIMITER $$$



create function user_SetConfirmationCode(
    user_id_ int unsigned,
    phone_confirmation_code_ varchar(255),
    phone_confirmation_expiry_ datetime,
    reset_phone_code_ enum('n', 'y'),
    email_confirmation_code_ varchar(255),
    email_confirmation_expiry_ datetime,
    reset_email_code_ enum('n', 'y')
) returns int unsigned
not deterministic modifies sql data
begin
    set @1 = __user_ValidateId(user_id_);
    
    update userT set
        phoneConfirmationCode = if(reset_phone_code_ = 'y', null, coalesce(phone_confirmation_code_, phoneConfirmationCode)),
        phoneConfirmationExpiry = if(reset_phone_code_ = 'y', null, coalesce(phone_confirmation_expiry_, phoneConfirmationExpiry)),
        emailConfirmationCode = if(reset_email_code_ = 'y', null, coalesce(email_confirmation_code_, emailConfirmationCode)),
        emailConfirmationExpiry = if(reset_email_code_ = 'y', null, coalesce(email_confirmation_expiry_, emailConfirmationExpiry))
    where id = user_id_;
    
    return user_id_;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function user_CheckConfirmationCode
-- -----------------------------------------------------
DROP function IF EXISTS `user_CheckConfirmationCode`;

DELIMITER $$$



create function user_CheckConfirmationCode(
    user_id_ int unsigned,
    phone_confirmation_code_ varchar(255),
    email_confirmation_code_ varchar(255)
) returns int unsigned
not deterministic modifies sql data
begin
    declare res, phone_confirmed, email_confirmed int unsigned;

    set @1 = __user_ValidateId(user_id_);
    
    select 
        case when phone_confirmation_code_ is not null
            and phoneConfirmationCode = phone_confirmation_code_
            and phoneConfirmationExpiry >= now() then 1 else 0 end,
        case when email_confirmation_code_ is not null
            and emailConfirmationCode = email_confirmation_code_
            and emailConfirmationExpiry >= now() then 2 else 0 end
    into
        phone_confirmed, email_confirmed
    from userT
    where id = user_id_;
    
    set res = phone_confirmed | email_confirmed;
    return res;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function user_CurrentId
-- -----------------------------------------------------
DROP function IF EXISTS `user_CurrentId`;

DELIMITER $$$



create function user_CurrentId() returns int unsigned
deterministic no sql
return @__userId$$$

DELIMITER ;

-- -----------------------------------------------------
-- function user_SetOauth
-- -----------------------------------------------------
DROP function IF EXISTS `user_SetOauth`;

DELIMITER $$$



create function user_SetOauth(
    user_id_ int unsigned, 
    provider_id_ varchar(30) binary, 
    identity_ varchar(255)
) returns int unsigned
not deterministic modifies sql data
begin
    set @1 = __user_ValidateId(user_id_);
    
    if identity_ is null then
        delete from userOauthT 
        where userId = user_id_ 
            and oauthProviderId = coalesce(provider_id_, oauthProviderId);
    else
        insert userOauthT(userId, oauthProviderId, identity)
        values(user_id_, provider_id_, identity_)
        on duplicate key update userOauthT.identity = identity_;
    end if;
    
    return user_id_;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function user_Login
-- -----------------------------------------------------
DROP function IF EXISTS `user_Login`;

DELIMITER $$$



create function user_Login(user_id_ int unsigned, browser_langid_ char(3), user_ip_ varchar(40)) returns int unsigned
not deterministic
begin
    declare user_id int unsigned;
    declare user_name varchar(255);
    declare language_id char(3);
    
    set @1 = (user_id_ is null) or __user_ValidateId(user_id_);
    
    select 
        u.id, u.preferredLanguage, u.name
    into 
        user_id, language_id, user_name
    from userT u 
    where u.id = user_id_
        and u.enabled = 'y';
    
    set @__userId = user_id;
    set @__userName = user_name;
    set @__userIp = user_ip_;
    set @__userLanguage = coalesce(
        language_id, 
        (select id from languageT l where id = browser_langid_), 
        (select id from languageT l where isDefault = 'y'));
        
    if user_id is null and user_id_ is not null then
        call raiseError1('user', 'login', user_id_, 'user is disabled');
    end if;
    
    return user_id;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function user_CurrentLanguage
-- -----------------------------------------------------
DROP function IF EXISTS `user_CurrentLanguage`;

DELIMITER $$$



create function user_CurrentLanguage() returns char(3)
deterministic no sql
return @__userLanguage$$$

DELIMITER ;

-- -----------------------------------------------------
-- function __tag_ValidateId
-- -----------------------------------------------------
DROP function IF EXISTS `__tag_ValidateId`;

DELIMITER $$$
create function __tag_ValidateId(tag_id_ int unsigned) returns int unsigned
deterministic
begin
    declare tag_id int unsigned;
    
    select t.id into tag_id 
    from tagT t
    join nodeT n on t.id = n.id
    join nodeTypeT nt on n.nodeTypeId = nt.id
    where t.id = tag_id_ 
        and nt.internalTypeId = 'tag';
    
    if tag_id is null then 
        call raiseError1('tag', 'validate', tag_id_, 'tag does not exist');
    end if;

    return 1;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function __tag_Create
-- -----------------------------------------------------
DROP function IF EXISTS `__tag_Create`;

DELIMITER $$$



create function __tag_Create(
    type_id_ int unsigned,
    type_code_ varchar(30) binary,
    internal_type_id_ varchar(10) binary,
    name_ varchar(255),
    sorter_ int,
    can_search_ enum('n', 'y'),
    display_in_object_ enum('n', 'y'),
    display_in_object_list_ enum('n', 'y'),
    factor_ double,
    rating_points_ int unsigned,
    enabled_ enum('n', 'y')
) returns int unsigned
not deterministic modifies sql data
begin
    declare node_id, type_id int unsigned;
    declare name__ varchar(255);
    declare can_search__, display_in_object__, display_in_object_list__ enum('n', 'y');
    declare sorter__ int default coalesce(sorter_, __tag_DefaultSorter());
    declare enabled__ enum('n', 'y') default coalesce(enabled_, 'y');
    declare real_type_id int unsigned default coalesce(type_id_, nodeType_GetByCode(type_code_));
    
    select 
        nt.id, 
        __tag_CalcDefaults(nt.canSearch, nt.displayNode, can_search_, enabled__), 
        __tag_CalcDefaults(nt.displayInObject, nt.displayNode, display_in_object_, enabled__), 
        __tag_CalcDefaults(nt.displayInObjectList, nt.displayNode, display_in_object_list_, enabled__), 
        coalesce(name_, ntl.name)
    into 
        type_id, 
        can_search__, 
        display_in_object__,
        display_in_object_list__,
        name__
    from nodeTypeT nt
    join nodeTypeTL ntl on nt.id = ntl.id
    join languageT l on ntl.languageId = l.id and l.isDefault = 'y'
    where nt.id = real_type_id 
        and nt.internalTypeId = internal_type_id_;
    
    set node_id = node_LinkDefaults(__node_Create(type_id, null, name__));
    
    insert tagT(
        id, 
        sorter, 
        canSearch, 
        canSearchRaw, 
        displayInObject,
        displayInObjectRaw,
        displayInObjectList,
        displayInObjectListRaw,
        factor, 
        ratingPoints,
        enabled)
    values(
        node_id, 
        sorter__, 
        can_search__, 
        can_search_,
        display_in_object__,
        display_in_object_,
        display_in_object_list__,
        display_in_object_list_,
        __tag_ClampFactor(coalesce(factor_, 1.0)), 
        __tag_ClampRatingPoints(coalesce(rating_points_, 0)),
        enabled__);
    
    insert tagTL(id, languageId, name)
    select node_id, l.id, coalesce(name_, ntl.name)
    from languageT l
    join nodeTypeTL ntl on ntl.id = type_id and ntl.languageId = l.id;
    
    set @1 = __tag_UpdateCache(node_id);
    
    return node_id;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function tag_Create
-- -----------------------------------------------------
DROP function IF EXISTS `tag_Create`;

DELIMITER $$$



create function tag_Create(
    type_id_ int unsigned,
    type_code_ varchar(30) binary,
    name_ varchar(255),
    sorter_ int,
    can_search_ enum('n', 'y'),
    display_in_object_ enum('n', 'y'),
    display_in_object_list_ enum('n', 'y'),
    factor_ double,
    rating_points_ int unsigned,
    enabled_ enum('n', 'y')
) returns int unsigned
not deterministic modifies sql data
begin
    declare tag_id int unsigned;
    
    set tag_id = __tag_Create(
                        type_id_, 
                        type_code_, 
                        'tag', 
                        name_, 
                        sorter_, 
                        can_search_, 
                        display_in_object_, 
                        display_in_object_list_, 
                        factor_, 
                        rating_points_,
                        enabled_);
    
    -- FORWARD: template_CreateSubs is defined in _12_template
    set @1 = template_CreateSubs(tag_id);
    
    return tag_id;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function __tag_Delete
-- -----------------------------------------------------
DROP function IF EXISTS `__tag_Delete`;

DELIMITER $$$



create function __tag_Delete(tag_id_ int unsigned) returns int unsigned
not deterministic modifies sql data
return __node_Delete(tag_id_)$$$

DELIMITER ;

-- -----------------------------------------------------
-- function tag_Delete
-- -----------------------------------------------------
DROP function IF EXISTS `tag_Delete`;

DELIMITER $$$



create function tag_Delete(tag_id_ int unsigned) returns int unsigned
not deterministic modifies sql data
begin
    set @1 = __tag_ValidateId(tag_id_);
    return __tag_Delete(tag_id_);
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function __tag_Update
-- -----------------------------------------------------
DROP function IF EXISTS `__tag_Update`;

DELIMITER $$$



create function __tag_Update(
    tag_id_ int unsigned,
    sorter_ int,
    can_search_ enum('n', 'y'), reset_can_search_ enum('n', 'y'), 
    display_in_object_ enum('n', 'y'), reset_display_in_object_ enum('n', 'y'),
    display_in_object_list_ enum('n', 'y'), reset_display_in_object_list_ enum('n', 'y'),
    factor_ double,
    rating_points_ int unsigned,
    enabled_ enum('n', 'y')
) returns int unsigned
not deterministic modifies sql data
begin
    set @1 = __node_Update(tag_id_, null);
    
    update tagT set
        sorter = coalesce(sorter_, sorter),
        canSearchRaw = if(reset_can_search_ = 'y', null, coalesce(can_search_, canSearchRaw)),
        displayInObjectRaw = if(reset_display_in_object_ = 'y', null, coalesce(display_in_object_, displayInObjectRaw)),
        displayInObjectListRaw = if(reset_display_in_object_list_ = 'y', null, coalesce(display_in_object_list_, displayInObjectListRaw)),
        factor = __tag_ClampFactor(coalesce(factor_, factor)),
        ratingPoints = __tag_ClampRatingPoints(coalesce(rating_points_, ratingPoints)),
        enabled = coalesce(enabled_, enabled)
    where id = tag_id_;
    
    set @2 = __tag_UpdateDefaults(tag_id_, null);
    
    -- as of now - no __tag_UpdateCache here because cache does not depend on tagT
    
    return tag_id_;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function tag_Update
-- -----------------------------------------------------
DROP function IF EXISTS `tag_Update`;

DELIMITER $$$



create function tag_Update(
    tag_id_ int unsigned,
    sorter_ int,
    can_search_ enum('n', 'y'), reset_can_search_ enum('n', 'y'), 
    display_in_object_ enum('n', 'y'), reset_display_in_object_ enum('n', 'y'),
    display_in_object_list_ enum('n', 'y'), reset_display_in_object_list_ enum('n', 'y'),
    factor_ double,
    rating_points_ int unsigned,
    enabled_ enum('n', 'y')
) returns int unsigned
not deterministic modifies sql data
begin
    set @1 = __tag_ValidateId(tag_id_);
    return __tag_Update(
                    tag_id_, 
                    sorter_, 
                    can_search_, reset_can_search_, 
                    display_in_object_, reset_display_in_object_, 
                    display_in_object_list_, reset_display_in_object_list_, 
                    factor_, 
                    rating_points_,
                    enabled_);
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function __tag_UpdateLocal
-- -----------------------------------------------------
DROP function IF EXISTS `__tag_UpdateLocal`;

DELIMITER $$$



create function __tag_UpdateLocal(
    tag_id_ int unsigned,
    lang_id_ char(3),
    name_ varchar(255)
) returns int unsigned
not deterministic modifies sql data
begin
    set @1 = __node_Update(tag_id_, (select name_ from languageT where id = coalesce(lang_id_, id) and isDefault = 'y'));
    
    insert tagTL(id, languageId, name)
    select tag_id_, l.id, coalesce(name_, '')
    from languageT l
    where l.id = coalesce(lang_id_, l.id)
    on duplicate key update tagTL.name = coalesce(name_, tagTL.name);
    
    set @2 = __tag_UpdateCache(tag_id_);
    
    return tag_id_;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function tag_UpdateLocal
-- -----------------------------------------------------
DROP function IF EXISTS `tag_UpdateLocal`;

DELIMITER $$$



create function tag_UpdateLocal(
    tag_id_ int unsigned,
    lang_id_ char(3),
    name_ varchar(255)
) returns int unsigned
not deterministic modifies sql data
begin
    set @1 = __tag_ValidateId(tag_id_);
    return __tag_UpdateLocal(tag_id_, lang_id_, name_);
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function __object_ValidateId
-- -----------------------------------------------------
DROP function IF EXISTS `__object_ValidateId`;

DELIMITER $$$
-- validators

create function __object_ValidateId(object_id_ int unsigned) returns int unsigned
deterministic
begin
    declare object_id int unsigned;

    select o.id into object_id
    from objectT o
    join nodeT n on o.id = n.id
    join nodeTypeT nt on n.nodeTypeId = nt.id
    where o.id = object_id_
        and nt.internalTypeId = 'object';

    if object_id is null then 
        call raiseError1('object', 'validate', object_id_, 'object does not exist');
    end if;

    return 1;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function __object_AccessByUser
-- -----------------------------------------------------
DROP function IF EXISTS `__object_AccessByUser`;

DELIMITER $$$



create function __object_AccessByUser(
    object_id_ int unsigned,
    action_ enum('update', 'delete', 'status'),
    new_status_id_ char(10) binary
) returns int unsigned
not deterministic 
begin
    if not (
            __object_ValidateId(object_id_)
            and __object_AccessObjectByUser(object_id_, action_, new_status_id_)
            and __object_AccessByLoggedUser(object_id_))
    then
        call raiseError1('object', action_, object_id_, concat('cannot perform "', action_, '" on object'));
    end if;
    
    return 1;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function object_Create
-- -----------------------------------------------------
DROP function IF EXISTS `object_Create`;

DELIMITER $$$



-- main data manipulation

create function object_Create(
    address_ text,
    description_ text,
    status_id_ char(10) binary,
    latitude_ decimal(12, 7),
    longitude_ decimal(12, 7),
    rating_ double,
    note_for_moderator_ text,
    note_for_user_ text
) returns int unsigned
not deterministic modifies sql data
begin
    declare object_id int unsigned;
    declare new_address text;
    declare rating__ double default __object_ClampRating(rating_);

    set new_address = coalesce(address_, '');
    set object_id = node_LinkDefaults(__node_Create(null, 'object', substring(new_address, 1, 50)));

    insert objectT(
        id, 
        address, 
        description, 
        statusId,
        rating,
        noteForModerator, 
        noteForUser) 
    select
        object_id,
        new_address,
        description_,
        coalesce(status_id_, os.id),
        rating__,
        note_for_moderator_,
        note_for_user_
    from objectStatusT os
    where os.isDefault = 'y';
    
    set @1 = __object_UpdateSortCacheRating2(object_id, rating__);
    
    set @2 = __object_UpdateSortCacheChanged2(object_id, null);
    
    set @3 = __coord_Update(object_id, latitude_, longitude_);
    
    return object_id;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function __object_Delete
-- -----------------------------------------------------
DROP function IF EXISTS `__object_Delete`;

DELIMITER $$$



create function __object_Delete(object_id_ int unsigned) returns int unsigned
not deterministic modifies sql data
begin
    return __node_Delete(object_id_);
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function object_Delete
-- -----------------------------------------------------
DROP function IF EXISTS `object_Delete`;

DELIMITER $$$



create function object_Delete(object_id_ int unsigned) returns int unsigned
not deterministic modifies sql data
begin
    set @1 = __object_ValidateId(object_id_);
    return __object_Delete(object_id_);
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function object_DeleteByUser
-- -----------------------------------------------------
DROP function IF EXISTS `object_DeleteByUser`;

DELIMITER $$$



create function object_DeleteByUser(object_id_ int unsigned) returns int unsigned
not deterministic modifies sql data
begin
    set @1 = __object_AccessByUser(object_id_, 'delete', null);
    return __object_Delete(object_id_);
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function __object_Update
-- -----------------------------------------------------
DROP function IF EXISTS `__object_Update`;

DELIMITER $$$



create function __object_Update(
    object_id_ int unsigned,
    address_ text,
    description_ text,
    status_id_ char(10) binary,
    latitude_ decimal(12, 7), reset_latitude_ enum('n', 'y'),
    longitude_ decimal(12, 7), reset_longitude_ enum('n', 'y'),
    rating_ double,
    note_for_moderator_ text, reset_note_for_moderator_ enum('n', 'y'),
    note_for_user_ text, reset_note_for_user_ enum('n', 'y'),
    alias_ text
) returns int unsigned
not deterministic modifies sql data
begin
    set @1 = __node_Update(object_id_, substring(address_, 1, 50));
    
    update objectT set
        address = coalesce(address_, address),
        description = coalesce(description_, description),
        statusId = coalesce(status_id_, statusId),
        rating = __object_ClampRating(coalesce(rating_, rating)),
        noteForModerator = if(reset_note_for_moderator_ = 'y', null, coalesce(note_for_moderator_, noteForModerator)),
        noteForUser = if(reset_note_for_user_ = 'y', null, coalesce(note_for_user_, noteForUser)),
        alias = if(alias_ = '', null, coalesce(alias_, alias))
    where id = object_id_;
    
    set @2 = rating_ is not null and __object_UpdateSortCacheRating2(object_id_, null); -- assume we already have rating set
    
    if reset_latitude_ = 'y' and reset_longitude_ = 'y' then
        set @3 = __coord_Delete(object_id_);
    else
        set @4 = __coord_Update(object_id_, latitude_, longitude_);
    end if;

    return object_id_;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function object_Update
-- -----------------------------------------------------
DROP function IF EXISTS `object_Update`;

DELIMITER $$$



create function object_Update(
    object_id_ int unsigned,
    address_ text,
    description_ text,
    status_id_ char(10) binary,
    latitude_ decimal(12, 7), reset_latitude_ enum('n', 'y'),
    longitude_ decimal(12, 7), reset_longitude_ enum('n', 'y'),
    rating_ double,
    note_for_moderator_ text, reset_note_for_moderator_ enum('n', 'y'),
    note_for_user_ text, reset_note_for_user_ enum('n', 'y')
) returns int unsigned
not deterministic modifies sql data
begin
    declare result int unsigned;
    
    set @1 = __object_ValidateId(object_id_);
    set result = __object_Update(object_id_, address_, description_, status_id_, 
                            latitude_, reset_latitude_, 
                            longitude_, reset_longitude_,
                            rating_,
                            note_for_moderator_, reset_note_for_moderator_,
                            note_for_user_, reset_note_for_user_,
                            null);
    set @2 = __object_UpdateSortCacheChanged2(object_id_, null);
    
    return result;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function object_UpdateByUser
-- -----------------------------------------------------
DROP function IF EXISTS `object_UpdateByUser`;

DELIMITER $$$



create function object_UpdateByUser(
    object_id_ int unsigned,
    address_ text,
    description_ text,
    latitude_ decimal(12, 7), reset_latitude_ enum('n', 'y'),
    longitude_ decimal(12, 7), reset_longitude_ enum('n', 'y'),
    note_for_user_ text, reset_note_for_user_ enum('n', 'y')
) returns int unsigned
not deterministic modifies sql data
begin
    declare result int unsigned;

    set @1 = __object_AccessByUser(object_id_, 'update', null);
    set result = __object_Update(object_id_, address_, description_, null, 
                            latitude_, reset_latitude_,
                            longitude_, reset_longitude_,
                            null,
                            null, null,
                            note_for_user_, reset_note_for_user_,
                            null);
    set @2 = __object_UpdateSortCacheChanged2(object_id_, null);
    
    return result;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function object_SetStatusByUser
-- -----------------------------------------------------
DROP function IF EXISTS `object_SetStatusByUser`;

DELIMITER $$$



create function object_SetStatusByUser(
    object_id_ int unsigned,
    status_id_ char(10) binary
) returns int unsigned
not deterministic modifies sql data
begin
    declare result int unsigned;

    set @1 = __object_AccessByUser(object_id_, 'status', status_id_);
    set result = __object_Update(object_id_, null, null, status_id_, 
                            null, null,
                            null, null,
                            null,
                            null, null,
                            null, null,
                            null);
    set @2 = __object_UpdateSortCacheChanged2(object_id_, null);
    
    return result;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function __object_AccessObjectByUser
-- -----------------------------------------------------
DROP function IF EXISTS `__object_AccessObjectByUser`;

DELIMITER $$$



create function __object_AccessObjectByUser(
    object_id_ int unsigned,
    action_ enum('update', 'delete', 'status'),
    new_status_id_ char(10) binary
) returns int unsigned
not deterministic 
begin
    declare editable, draftable enum('n', 'y');
    
    select os.userEditable, os.userDraftable
    into editable, draftable
    from objectStatusT os
    join objectT o on os.id = o.statusId
    where o.id = object_id_;

    return 
        case action_ 
            when 'update' then editable = 'y'
            when 'delete' then editable = 'y' or draftable = 'y'
            when 'status' then 
                case new_status_id_ 
                    when 'draft' then draftable = 'y'
                    when 'review' then editable = 'y'
                    else 0
                end
            else 0
        end;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function __object_AccessByLoggedUser
-- -----------------------------------------------------
DROP function IF EXISTS `__object_AccessByLoggedUser`;

DELIMITER $$$



create function __object_AccessByLoggedUser(object_id_ int unsigned) returns int unsigned
not deterministic 
return exists (
            select 1 
            from nodeLinkT nl 
            join nodeLinkTypeT nlt on nl.nodeLinkTypeId = nlt.id 
            where nlt.code = 'owns'
                and nl.srcNodeId = user_CurrentId()
                and nl.dstNodeId = object_id_)$$$

DELIMITER ;

-- -----------------------------------------------------
-- function testOddRaiseError
-- -----------------------------------------------------
DROP function IF EXISTS `testOddRaiseError`;

DELIMITER $$$


                    
create function testOddRaiseError(n int) returns int
not deterministic
begin
    if (n % 2 = 1) then 
        call raiseError1('testObject', 'testAction', n, '<-- this is odd!');
    end if;
    return n;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure raiseError
-- -----------------------------------------------------
DROP procedure IF EXISTS `raiseError`;

DELIMITER $$$
create procedure raiseError(msg_ varchar(128))
begin
    declare msg varchar(128) default substring(msg_, 1, 128);

    signal sqlstate '45000'
    set message_text = msg;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure raiseError1
-- -----------------------------------------------------
DROP procedure IF EXISTS `raiseError1`;

DELIMITER $$$



create procedure raiseError1(
    object_ varchar(30), 
    action_ varchar(30), 
    subject_ varchar(30), 
    msg_ varchar(65))
begin   
    call raiseError(concat_ws('|', 
                    coalesce(object_, '<empty>'), 
                    coalesce(action_, '<empty>'),
                    coalesce(subject_, ''), 
                    coalesce(msg_, '')));
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure raiseError2
-- -----------------------------------------------------
DROP procedure IF EXISTS `raiseError2`;

DELIMITER $$$


create procedure raiseError2(
    object_ varchar(30), 
    action_ varchar(30), 
    subject1_ varchar(30), 
    subject2_ varchar(30), 
    msg_ varchar(65))
begin 
    call raiseError(concat_ws('|', 
                    coalesce(object_, '<empty>'), 
                    coalesce(action_, '<empty>'), 
                    coalesce(subject1_, ''), 
                    coalesce(subject2_, ''), 
                    coalesce(msg_, '')));
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function __node_CanUpdate
-- -----------------------------------------------------
DROP function IF EXISTS `__node_CanUpdate`;

DELIMITER $$$
create function __node_CanUpdate(node_id_ int unsigned, action_ varchar(20)) returns int unsigned
deterministic
begin
    if (select nt.canEditNode
        from nodeTypeT nt 
        join nodeT n on nt.id = n.nodeTypeId 
        where n.id = node_id_) != 'y' 
    then
        call raiseError1('node', action_, node_id_, 'cannot change uneditable node');
    end if;
    
    return 1;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function nodeType_GetByCode
-- -----------------------------------------------------
DROP function IF EXISTS `nodeType_GetByCode`;

DELIMITER $$$



create function nodeType_GetByCode(code_ varchar(30) binary) returns int unsigned
deterministic
begin
    return (select nt.id from nodeTypeT nt where nt.code = code_);
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function nodeType_UpdateCode
-- -----------------------------------------------------
DROP function IF EXISTS `nodeType_UpdateCode`;

DELIMITER $$$



create function nodeType_UpdateCode(
    type_id_ int unsigned,
    code_ varchar(30) binary
) returns int unsigned
not deterministic modifies sql data
begin
    set @1 = __nodeType_CanUpdate(type_id_, 'updateCode');
    
    update nodeTypeT set
        code = code_
    where id = type_id_;

    return __nodeType_Touch(type_id_);
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure __executeSQL
-- -----------------------------------------------------
DROP procedure IF EXISTS `__executeSQL`;

DELIMITER $$$



-- dynamic sql wrapper

create procedure __executeSQL(sql_query_ varchar(4000))
modifies sql data
begin
    set @executeSQL_q = sql_query_;
    prepare executeSQL_stmt from @executeSQL_q;
    execute executeSQL_stmt;
    deallocate prepare executeSQL_stmt;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function node_LinkDefaults
-- -----------------------------------------------------
DROP function IF EXISTS `node_LinkDefaults`;

DELIMITER $$$



create function node_LinkDefaults(node_id_ int unsigned) returns int unsigned
not deterministic modifies sql data
begin
    set @1 = __node_LinkDefaults('owns', node_id_);
    return node_id_;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function externalData_UpdateTags
-- -----------------------------------------------------
DROP function IF EXISTS `externalData_UpdateTags`;

DELIMITER $$$



create function externalData_UpdateTags(
    external_data_source_id_ varchar(30) binary,
    dataset_code_ varchar(255),
    force_update_ int unsigned
) returns varchar(50)
not deterministic modifies sql data
begin
    declare number_added, number_changed, number_disabled int unsigned;
    declare force_update int unsigned default coalesce(! ! force_update_, 0);
    declare parent_node_id int unsigned default (
        select edd.nodeId 
        from externalDataSourceDatasetT edd
        where edd.externalDataSourceId = external_data_source_id_
            and edd.code = dataset_code_);
    declare no_parent_node int unsigned default (parent_node_id is null);

    drop temporary table if exists externalDataInserted;
    create temporary table externalDataInserted(
        tagId int unsigned not null primary key
    ) engine=MEMORY;
    
    -- insert missing ones and link created tags
    
    set number_added = __externalData_CreateTags(external_data_source_id_, no_parent_node, parent_node_id);
    
    -- update names

    set number_changed = __externalData_UpdateTagNames(external_data_source_id_, no_parent_node, parent_node_id, force_update);
    
    -- disable "deleted"

    set number_disabled = __externalData_DisableTags(external_data_source_id_, no_parent_node, parent_node_id, force_update);

    drop temporary table externalDataInserted;

    return concat_ws('|', number_added, number_changed, number_disabled);
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function __external_OSM_UpdateLocal
-- -----------------------------------------------------
DROP function IF EXISTS `__external_OSM_UpdateLocal`;

DELIMITER $$$





-- --------------------------------------------------------------------------------
-- --------------------------------------------------------------------------------
-- OSM import
-- --------------------------------------------------------------------------------
-- import process: 
-- 1) load data infile into temp table - ONE entity (city/district/etc at a time)
-- 2) convert temp table -> externalDataT/externalDataTL by running external_OSM_Import
-- 3) run externalData_UpdateTags

-- temp table format for streets: 
-- 1) id - internal OSM way identifier, unique within OSM, but one street may have several ids
-- 2) highway - highway label, no use for us
-- 3) name_ukr - default name (ukrainian), also it is the local key, so we need to group by this field
-- 4) name_rus - russian name
-- 5) name_eng - english name

/*
create table tmpOsmDataT(
    id varchar(50) not null,
    highway varchar(100),
    name_ukr varchar(255),
    name_rus varchar(255),
    name_eng varchar(255),
    primary key(id),
    index(name_ukr)
) engine=InnoDB;

load data local infile '<file name>'
replace into table tmpOsmDataT
character set utf8
columns terminated by '\t'
lines terminated by '\r\n'
ignore 1 lines;
--------------------------------
truncate table tmpOsmDataT;

load data local infile '/home/castor/public_html/m2.te.ua/ternopil-streets.txt'
replace into table tmpOsmDataT
character set utf8
columns terminated by '\t'
lines terminated by '\r\n'
ignore 1 lines;

call external_OSM_Import('ternopil', 'street');

truncate table tmpOsmDataT;

load data local infile '/home/castor/public_html/m2.te.ua/kyiv-suburbs.txt'
replace into table tmpOsmDataT
character set utf8
columns terminated by '\t'
lines terminated by '\r\n'
ignore 1 lines;

call external_OSM_Import('kyiv', 'neighborhood');

truncate table tmpOsmDataT;

load data local infile '/home/castor/public_html/m2.te.ua/kyiv-streets.txt'
replace into table tmpOsmDataT
character set utf8
columns terminated by '\t'
lines terminated by '\r\n'
ignore 1 lines;

call external_OSM_Import('kyiv', 'street');

truncate table tmpOsmDataT;

*/


create function __external_OSM_UpdateLocal(
    external_data_id_ int unsigned,
    lang_id_ char(3),
    name_ varchar(255)
) returns int unsigned
not deterministic modifies sql data
begin
    insert externalDataTL(id, languageId, name)
    select external_data_id_, lang_id_, name_
    from dual
    where name_ is not null
    on duplicate key update externalDataTL.name = name_;

    return external_data_id_;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure __external_OSM_UpdateName
-- -----------------------------------------------------
DROP procedure IF EXISTS `__external_OSM_UpdateName`;

DELIMITER $$$



create procedure __external_OSM_UpdateName(parent_node_id_ int unsigned, type_code_ varchar(30) binary)
modifies sql data
begin
    declare lang_id char(3);
    
    declare sql_tmpl varchar(2000) default 
'insert zzzDevNullExternalDataT(n)
select __external_OSM_UpdateLocal(ed.id, ''{{0}}'', nullif(tod.name_{{0}}, ''''))
from externalDataT ed 
join tmpOsmDataT tod on ed.externalKey = tod.name_ukr
where ed.externalDataSourceId = ''osm''
    and ed.parentNodeId = {{1}}
    and ed.typeCode = ''{{2}}''
    and ed.enabled = ''y''
group by tod.name_ukr';
    declare sql_query, sql_tmpl2 varchar(2000);
    
    declare done int unsigned default 0;
    declare cur cursor for select l.id from languageT l;
    declare continue handler for not found set done = 1;

    set sql_tmpl2 = replace(replace(sql_tmpl, '{{1}}', parent_node_id_), '{{2}}', type_code_);
    
    open cur;
    main_loop: loop
        fetch cur into lang_id;
        if done then 
            leave main_loop;
        end if;
        
        set sql_query = replace(sql_tmpl2, '{{0}}', lang_id);
        call __executeSQL(sql_query);
    end loop;
    close cur;

end$$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure external_OSM_Import
-- -----------------------------------------------------
DROP procedure IF EXISTS `external_OSM_Import`;

DELIMITER $$$



create procedure external_OSM_Import(
    dictionary_id_ varchar(255) binary,
    type_code_ varchar(30) binary) 
modifies sql data
begin
    declare parent_node_id int unsigned default (
        select edd.nodeId 
        from externalDataSourceDatasetT edd
        where edd.externalDataSourceId = 'osm'
            and edd.code = dictionary_id_);

    -- insert
    
    insert externalDataT(
        externalDataSourceId, 
        parentNodeId, 
        externalKey, 
        typeCode, 
        enabled)
    select 
        'osm',
        parent_node_id,
        tod.name_ukr,
        type_code_,
        'y'
    from tmpOsmDataT tod
    where not exists (
            select 1 
            from externalDataT ed 
            where ed.externalDataSourceId = 'osm'
                and ed.parentNodeId = parent_node_id
                and ed.typeCode = type_code_
                and ed.externalKey = tod.name_ukr)
    group by tod.name_ukr;
    
    -- disable
    
    update externalDataT ed set
        ed.enabled = 'n'
    where ed.externalDataSourceId = 'osm'
        and ed.parentNodeId = parent_node_id
        and ed.typeCode = type_code_
        and not exists (select 1 from tmpOsmDataT tod where tod.name_ukr = ed.externalKey);

    -- update names
    call __external_OSM_UpdateName(parent_node_id, type_code_);
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function external_OSM_Help
-- -----------------------------------------------------
DROP function IF EXISTS `external_OSM_Help`;

DELIMITER $$$



create function external_OSM_Help() returns varchar(20000)
begin
    return '';
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure initScript_Start
-- -----------------------------------------------------
DROP procedure IF EXISTS `initScript_Start`;

DELIMITER $$$



create procedure initScript_Start()
modifies sql data
begin
    start transaction;

    call __initScript_010Root();
    call __initScript_015Templates();
    call __initScript_020Users();
    call __initScript_030Cities();
    call __initScript_040Amenities();
    call __initScript_050HiddenAttributes();

    commit;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function nodeLink_Create2
-- -----------------------------------------------------
DROP function IF EXISTS `nodeLink_Create2`;

DELIMITER $$$



create function nodeLink_Create2(
    link_code varchar(30) binary, 
    src_id int unsigned, 
    dst_id int unsigned
) returns int unsigned
not deterministic modifies sql data
begin
    set @1 = nodeLink_Create(link_code, src_id, dst_id);
    return dst_id;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function __externalData_UpdateTagNames
-- -----------------------------------------------------
DROP function IF EXISTS `__externalData_UpdateTagNames`;

DELIMITER $$$


create function __externalData_UpdateTagNames(
    external_data_source_id_ varchar(30) binary,
    no_parent_node_ int unsigned,
    parent_node_id_ int unsigned,
    force_update_ int unsigned) 
returns int unsigned
not deterministic modifies sql data
begin
    declare result int unsigned;
    
    drop temporary table if exists externalDataUpdateLocal;
    create temporary table externalDataUpdateLocal(
        tagId int unsigned not null,
        languageId char(3) not null,
        name varchar(255),
        primary key(tagId, languageId)
    ) engine=MEMORY;
    
    insert externalDataUpdateLocal(tagId, languageId, name)
    select ed.tagId, l.id, edl.name
    from externalDataT ed
    join nodeT n on ed.tagId = n.id
    join languageT l on 1=1
    left join externalDataTL edl on ed.id = edl.id and l.id = edl.languageId
    left join externalDataInserted edi on ed.tagId = edi.tagId
    where ed.externalDataSourceId = external_data_source_id_
        and (no_parent_node_ or ed.parentNodeId = parent_node_id_)
        and ed.enabled = 'y'
        and (force_update_ or ed.changed > n.changed or edi.tagId is not null);

    insert zzzDevNullExternalDataT(n)
    select tag_UpdateLocal(tagId, languageId, name)
    from externalDataUpdateLocal;
    
    set result = row_count() / (select count(*) from languageT);

    drop temporary table externalDataUpdateLocal;
    
    return result;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function __externalData_DisableTags
-- -----------------------------------------------------
DROP function IF EXISTS `__externalData_DisableTags`;

DELIMITER $$$



create function __externalData_DisableTags(
    external_data_source_id_ varchar(30) binary,
    no_parent_node_ int unsigned,
    parent_node_id_ int unsigned,
    force_update_ int unsigned) 
returns int unsigned
not deterministic modifies sql data
begin
    declare result int unsigned;
    
    drop temporary table if exists externalDataDisable;
    create temporary table externalDataDisable(
        tagId int unsigned not null primary key
    ) engine=MEMORY;
    
    insert externalDataDisable(tagId)
    select ed.tagId
    from externalDataT ed
    join nodeT n on ed.tagId = n.id
    where ed.externalDataSourceId = external_data_source_id_
        and (no_parent_node_ or ed.parentNodeId = parent_node_id_)
        and ed.enabled = 'n'
        and (force_update_ or ed.changed > n.changed);

    insert zzzDevNullExternalDataT(n)
    select tag_Update(tagId, null, null, null, null, null, null, null, null, null, 'n') -- disable and exclude from search
    from externalDataDisable;
    
    set result = row_count();
    
    drop temporary table externalDataDisable;
    
    return result;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function __externalData_CreateTags
-- -----------------------------------------------------
DROP function IF EXISTS `__externalData_CreateTags`;

DELIMITER $$$



create function __externalData_CreateTags(
    external_data_source_id_ varchar(30) binary,
    no_parent_node_ int unsigned,
    parent_node_id_ int unsigned)
returns int unsigned
not deterministic modifies sql data
begin
    declare result int unsigned;
    
    set @1 = nodeLink_UpdateStart();

    update externalDataT ed set
        ed.tagId = __externalData_TagHelper(
                        nodeLink_Relink2(
                            'owns', 
                            ed.parentNodeId, 
                            tag_Create(null, ed.typeCode, ed.externalKey, null, null, null, null, null, null, ed.enabled)))
    where ed.externalDataSourceId = external_data_source_id_
        and ed.tagId is null
        and (no_parent_node_ or ed.parentNodeId = parent_node_id_)
        and ed.enabled = 'y';
    
    set result = row_count();

    set @2 = nodeLink_UpdateEnd();
    
    return result;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function __externalData_TagHelper
-- -----------------------------------------------------
DROP function IF EXISTS `__externalData_TagHelper`;

DELIMITER $$$
create function __externalData_TagHelper(tag_id_ int unsigned) returns int unsigned
not deterministic modifies sql data
begin
    insert ignore externalDataInserted(tagId) -- externalDataInserted created in externalData_UpdateTags
    values(tag_id_);
    return tag_id_;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function object_CreateByUser
-- -----------------------------------------------------
DROP function IF EXISTS `object_CreateByUser`;

DELIMITER $$$



create function object_CreateByUser(
    address_ text,
    description_ text,
    status_id_ char(10) binary,
    latitude_ decimal(12, 7),
    longitude_ decimal(12, 7),
    note_for_moderator_ text,
    note_for_user_ text
) returns int unsigned
not deterministic modifies sql data
begin
    return nodeLink_Create2(
            'owns', 
            user_CurrentId(), 
            object_Create(
                address_,
                description_,
                status_id_,
                latitude_,
                longitude_,
                0,
                note_for_moderator_,
                note_for_user_));
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function search_CurrentId
-- -----------------------------------------------------
DROP function IF EXISTS `search_CurrentId`;

DELIMITER $$$
create function search_CurrentId() returns bigint unsigned
deterministic
return @__searchId$$$

DELIMITER ;

-- -----------------------------------------------------
-- function search_Reset
-- -----------------------------------------------------
DROP function IF EXISTS `search_Reset`;

DELIMITER $$$



create function search_Reset() returns int unsigned
not deterministic modifies sql data
begin
    delete from searchCriterionT where sessionId = search_CurrentId();
    delete from searchSessionT where added < date_sub(now(), interval 1 day);
    return 1;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function search_StartSession
-- -----------------------------------------------------
DROP function IF EXISTS `search_StartSession`;

DELIMITER $$$



create function search_StartSession(sid_ varchar(100) binary, reset_params_ int unsigned) returns bigint unsigned
not deterministic modifies sql data
begin
    insert searchSessionT(id, sid)
    values(uuid_short(), sid_)
    on duplicate key update searchSessionT.added = now();

    select id into @__searchId from searchSessionT where sid = sid_;

    set @1 = coalesce(reset_params_, 1) and search_Reset();

    return search_CurrentId();
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function __search_SetTag
-- -----------------------------------------------------
DROP function IF EXISTS `__search_SetTag`;

DELIMITER $$$



create function __search_SetTag(tag_id_ int unsigned, force_tag_ int) returns int unsigned
not deterministic modifies sql data
begin
    declare tag_id, node_type_id int unsigned;
    declare internal_type_id varchar(10) binary;
    declare force_tag int default coalesce(force_tag_, 0);

    select
        t.id, if(nt.searchAnd = 'y', null, nt.id), nt.internalTypeId -- attributes must have searchAnd = 'y'
    into
        tag_id, node_type_id, internal_type_id
    from tagT t
    join nodeT n on t.id = n.id
    join nodeTypeT nt on n.nodeTypeId = nt.id
    where t.id = tag_id_
        and (force_tag or t.canSearch = 'y');

    insert ignore searchCriterionT(sessionId, nodeId, nodeTypeId, internalTypeId)
    select search_CurrentId(), tag_id, node_type_id, internal_type_id
    from dual
    where tag_id is not null;

    return tag_id;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function search_SetTag
-- -----------------------------------------------------
DROP function IF EXISTS `search_SetTag`;

DELIMITER $$$



create function search_SetTag(tag_id_ int unsigned) returns int unsigned
not deterministic modifies sql data
begin
    set @1 = __search_SetTag(tag_id_, null);
    return 1;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function search_SetAttributeNumeric
-- -----------------------------------------------------
DROP function IF EXISTS `search_SetAttributeNumeric`;

DELIMITER $$$



create function search_SetAttributeNumeric(
    attr_id_ int unsigned, 
    value_low_ decimal(22, 6), 
    value_high_ decimal(22, 6)
) returns int unsigned
not deterministic modifies sql data
begin
    declare attr_id int unsigned default __search_SetTag(attr_id_, null);

    insert searchCriterionNumericT(
        sessionId, 
        nodeId, 
        valueLow, 
        valueHigh)
    select 
        search_CurrentId(), 
        a.id, 
        __attribute_ClampValueNumeric(attr_id, value_low_), 
        __attribute_ClampValueNumeric(attr_id, value_high_)
    from attributeT a
    where a.id = attr_id
        and attr_id is not null
        and a.dataTypeId = 'numeric'
    on duplicate key update
        searchCriterionNumericT.valueLow = __attribute_ClampValueNumeric(attr_id, value_low_),
        searchCriterionNumericT.valueHigh = __attribute_ClampValueNumeric(attr_id, value_high_);

    return 1;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function search_SetAttributeDatetime
-- -----------------------------------------------------
DROP function IF EXISTS `search_SetAttributeDatetime`;

DELIMITER $$$



create function search_SetAttributeDatetime(
    attr_id_ int unsigned, 
    value_low_ datetime, 
    value_high_ datetime
) returns int unsigned
not deterministic modifies sql data
begin
    declare attr_id int unsigned default __search_SetTag(attr_id_, null);

    insert searchCriterionDatetimeT(
        sessionId, 
        nodeId, 
        valueLow, 
        valueHigh)
    select 
        search_CurrentId(), 
        a.id, 
        __attribute_ClampValueDatetime(attr_id, value_low_), 
        __attribute_ClampValueDatetime(attr_id, value_high_)
    from attributeT a
    where a.id = attr_id
        and attr_id is not null
        and a.dataTypeId = 'datetime'
    on duplicate key update
        searchCriterionDatetimeT.valueLow = __attribute_ClampValueDatetime(attr_id, value_low_),
        searchCriterionDatetimeT.valueHigh = __attribute_ClampValueDatetime(attr_id, value_high_);

    return 1;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function __search_RectifyCriteria
-- -----------------------------------------------------
DROP function IF EXISTS `__search_RectifyCriteria`;

DELIMITER $$$



create function __search_RectifyCriteria(session_id_ bigint unsigned) returns int unsigned
not deterministic modifies sql data
begin
    drop temporary table if exists searchRectifyCriteriaDeletes;
    create temporary table searchRectifyCriteriaDeletes(
        nodeId int unsigned not null primary key
    ) engine=MEMORY;

--  remove tags whose parents are searchable but not included into the list
/*    insert ignore searchRectifyCriteriaDeletes(nodeId)
    select sc.nodeId
    from searchCriterionT sc
    where sc.sessionId = session_id_
        and exists (
            select 1 
            from nodeLinkOwnsV nlo1 
            join tagT t1 on nlo1.srcNodeId = t1.id
            where nlo1.dstNodeId = sc.nodeId
                and nlo1.metric = 1
                and t1.canSearch = 'y')
        and not exists (
            select 1 
            from nodeLinkOwnsV nlo2 
            join tagT t2 on nlo2.srcNodeId = t2.id
            join searchCriterionT absentc2 on t2.id = absentc2.nodeId
            where nlo2.dstNodeId = sc.nodeId
                and nlo2.metric = 1
                and t2.canSearch = 'y'
                and sc.sessionId = absentc2.sessionId);
*/
-- empty attribute criteria
    insert ignore searchRectifyCriteriaDeletes(nodeId)
    select scn.nodeId
    from searchCriterionNumericT scn
    where scn.sessionId = session_id_
        and scn.valueLow is null
        and scn.valueHigh is null;

    insert ignore searchRectifyCriteriaDeletes(nodeId)
    select scd.nodeId
    from searchCriterionDatetimeT scd
    where scd.sessionId = session_id_
        and scd.valueLow is null
        and scd.valueHigh is null;

-- remove unneeded criteria
    delete from searchCriterionT
    using searchCriterionT
    join searchRectifyCriteriaDeletes srcd on searchCriterionT.nodeId = srcd.nodeId
    where searchCriterionT.sessionId = session_id_;

    drop temporary table searchRectifyCriteriaDeletes;

    return 1;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure search_Search
-- -----------------------------------------------------
DROP procedure IF EXISTS `search_Search`;

DELIMITER $$$



create procedure search_Search()
modifies sql data
begin
    declare session_id bigint unsigned default search_CurrentId();
    declare 
        count_notnull, 
        count_null, 
        count_numerics, 
        count_datetimes int; 
--        search_objects int;
    declare sum_null double;

    start transaction;
    
    set @1 = __search_RectifyCriteria(session_id);

    select count(distinct sc.nodeTypeId) into count_notnull
    from searchCriterionT sc
    where sc.sessionId = session_id
        and sc.nodeTypeId is not null
        and sc.internalTypeId = 'tag';

    select count(*), coalesce(sum(t.factor), 0) into count_null, sum_null
    from searchCriterionT sc
    join tagT t on sc.nodeId = t.id
    where sc.sessionId = session_id
        and sc.nodeTypeId is null
        and sc.internalTypeId = 'tag';

    select count(*) into count_numerics
    from searchCriterionT sc
    join searchCriterionNumericT scn on sc.sessionId = scn.sessionId and sc.nodeId = scn.nodeId
    where sc.sessionId = session_id;

    select count(*) into count_datetimes
    from searchCriterionT sc
    join searchCriterionDatetimeT scd on sc.sessionId = scd.sessionId and sc.nodeId = scd.nodeId
    where sc.sessionId = session_id;

--    set search_objects = __search_CheckRequiredForObject(session_id);

    delete from searchResultT where sessionId = session_id;

    call __search_SearchTags(session_id, count_notnull, count_null);

    set @2 = __search_SetDefaultResult(session_id);
    
    set @3 = __search_RectifyCriteriaPost(session_id);
    
    -- CONST: sum_null_tolerance_ is 1
    call __search_SearchObjects(session_id, count_notnull, sum_null, 1, count_numerics, count_datetimes);
    
    call __search_UpdateExtraObjects(session_id);
    
    call __search_UpdateSortCache(session_id);
    
    commit;

end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function nodeLink_Relink
-- -----------------------------------------------------
DROP function IF EXISTS `nodeLink_Relink`;

DELIMITER $$$



-- ---------------------------------
-- nodeLink smart linking
-- ---------------------------------

create function nodeLink_Relink(
    link_code_ varchar(30) binary, 
    src_id_ int unsigned, 
    dst_id_ int unsigned
) returns int unsigned
comment 'links src_id and dst_id, detects and removes unnecessary linking'
not deterministic modifies sql data
begin
    declare link_code varchar(30) binary;
    declare link_id int unsigned;
    
    select nlt.id, nlt.code into link_id, link_code
    from nodeLinkTypeT nlt join nodeLinkTypeT nltdef on nltdef.isDefault = 'y'
    where nlt.code = coalesce(link_code_, nltdef.code);
    
    if src_id_ = dst_id_ 
        or exists(
            select 1 
            from nodeLinkT nl 
            where nl.nodeLinkTypeId = link_id
                and nl.srcNodeId = src_id_
                and nl.dstNodeId = dst_id_
                and nl.metric = 1)
    then
        return 1;
    end if;

    drop temporary table if exists nodeLinkRelinkInserts;

-- link to nodes, which are parents for src_id and children for parents of dst_id
    create temporary table nodeLinkRelinkInserts(srcNodeId int unsigned not null primary key) engine=MEMORY;

    insert nodeLinkRelinkInserts(srcNodeId)
    select distinct trglink.dstNodeId
    from nodeLinkT dstlink 
    join nodeLinkT trglink on dstlink.nodeLinkTypeId = trglink.nodeLinkTypeId
        and dstlink.srcNodeId = trglink.srcNodeId
    join allowedNodeLinkByNodeV dstallow on dstlink.srcNodeId = dstallow.srcNodeId 
        and dstlink.dstNodeId = dstallow.dstNodeId
    join allowedNodeLinkByNodeV trgallow on trglink.srcNodeId = trgallow.srcNodeId 
        and trglink.dstNodeId = trgallow.dstNodeId
    left join nodeLinkT exilink on dstlink.nodeLinkTypeId = exilink.nodeLinkTypeId
        and trglink.dstNodeId = exilink.srcNodeId
        and dst_id_ = exilink.dstNodeId
        and exilink.metric = 1
    where dstlink.nodeLinkTypeId = link_id
        and dstlink.dstNodeId = dst_id_
        and dstlink.metric = 1
        and trglink.dstNodeId != src_id_
        and trglink.metric = 1
        and dstallow.singleSrc = 'y'
        and trgallow.singleSrc = 'y'
        and exists (
                select 1
                from nodeLinkT srclink
                join allowedNodeLinkByNodeV srcallow on srclink.srcNodeId = srcallow.srcNodeId
                    and srclink.dstNodeId = srcallow.dstNodeId
                where srclink.nodeLinkTypeId = dstlink.nodeLinkTypeId
                    and srclink.srcNodeId = trglink.dstNodeId
                    and srclink.dstNodeId = src_id_
                    and srclink.metric = 1
                    and srcallow.singleSrc = 'y')
        and exilink.metric is null;
    
    set @1 = nodeLink_UpdateStart();
    
    insert zzzDevNullLinkT(n)
    select __nodeLink_Create(link_code, nlri.srcNodeId, dst_id_)
    from nodeLinkRelinkInserts nlri;

    set @2 = __nodeLink_Create(link_code, src_id_, dst_id_);
    
    set @3 = nodeLink_UpdateEnd();
    
    drop temporary table nodeLinkRelinkInserts;

    return 1; 
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function nodeLink_Relink2
-- -----------------------------------------------------
DROP function IF EXISTS `nodeLink_Relink2`;

DELIMITER $$$



create function nodeLink_Relink2(
    link_code_ varchar(30) binary, 
    src_id_ int unsigned, 
    dst_id_ int unsigned
) returns int unsigned
comment 'calls nodeLink_Relink and returns dst_id'
not deterministic modifies sql data
begin
    set @1 = nodeLink_Relink(link_code_, src_id_, dst_id_);
    return dst_id_;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function nodeLink_ResetLinks
-- -----------------------------------------------------
DROP function IF EXISTS `nodeLink_ResetLinks`;

DELIMITER $$$
create function nodeLink_ResetLinks(
    link_code_ varchar(30) binary, 
    root_type_code_ varchar(30) binary, 
    dst_id_ int unsigned,
    recreate_root_links_ int
) returns int unsigned
not deterministic modifies sql data
begin
    declare link_code varchar(30) binary;
    declare link_id int unsigned;
    declare recreate_root_links int default coalesce(recreate_root_links_, 1);
    
    select nlt.id, nlt.code into link_id, link_code
    from nodeLinkTypeT nlt join nodeLinkTypeT nltdef on nltdef.isDefault = 'y'
    where nlt.code = coalesce(link_code_, nltdef.code);

    drop temporary table if exists nodeLinkResetLinksDeletes;
    create temporary table nodeLinkResetLinksDeletes(
        srcNodeId int unsigned,
        primary key(srcNodeId)
    ) engine=MEMORY;
    
    insert nodeLinkResetLinksDeletes(srcNodeId)
    select nl.srcNodeId
    from nodeLinkT nl
    where nl.nodeLinkTypeId = link_id
        and nl.dstNodeId = dst_id_
        and nl.metric = 1;
/*        and exists(
                select 1 
                from nodeLinkT nl2 
                join nodeV n2 on nl2.srcNodeId = n2.id
                where nl2.nodeLinkTypeId = link_id
                    and nl2.dstNodeId = nl.srcNodeId
                    and nl2.metric > 0
                    and n2.isRoot = 'y'
                    and n2.typeCode = coalesce(root_type_code_, n2.typeCode));*/
    
    set @1 = nodeLink_UpdateStart();
    
    insert zzzDevNullLinkT(n)
    select __nodeLink_Delete(link_code, nlrld.srcNodeId, dst_id_)
    from nodeLinkResetLinksDeletes nlrld;

    set @2 = recreate_root_links and __node_LinkDefaults(link_code, dst_id_);
    
    set @3 = nodeLink_UpdateEnd();

    drop temporary table nodeLinkResetLinksDeletes;
    
    return 1;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function __node_LinkDefaults
-- -----------------------------------------------------
DROP function IF EXISTS `__node_LinkDefaults`;

DELIMITER $$$



create function __node_LinkDefaults(link_code_ varchar(30) binary, node_id_ int unsigned) returns int unsigned
not deterministic modifies sql data
begin
    declare link_code varchar(30) binary;
    declare link_id int unsigned;
    
    select nlt.id, nlt.code into link_id, link_code
    from nodeLinkTypeT nlt join nodeLinkTypeT nltdef on nltdef.isDefault = 'y'
    where nlt.code = coalesce(link_code_, nltdef.code);

    drop temporary table if exists nodeLinkDefaultsTmp;
    create temporary table nodeLinkDefaultsTmp(
        src_id int unsigned, 
        dst_id int unsigned, 
        primary key(src_id, dst_id)
    ) engine=MEMORY;
    
    insert nodeLinkDefaultsTmp(src_id, dst_id)
    select rootnode.id, n.id
    from nodeT rootnode
    join allowedNodeLinkT anl on rootnode.nodeTypeId = anl.srcNodeTypeId
    join nodeT n on anl.dstNodeTypeId = n.nodeTypeId and n.id = node_id_
    left join templateT t on n.id = t.dstNodeId
    where rootnode.isRoot = 'y'
        and t.dstNodeId is null
        and not exists (
            select 1 
            from nodeLinkT nl 
            where nl.nodeLinkTypeId = link_id
                and nl.srcNodeId = rootnode.id
                and nl.dstNodeId = n.id
                and nl.metric = 1);

    set @1 = nodeLink_UpdateStart();
    
    insert zzzDevNullNodeT(n)
    select __nodeLink_Create(link_code, src_id, dst_id)
    from nodeLinkDefaultsTmp;

    set @2 = nodeLink_UpdateEnd();

    drop temporary table nodeLinkDefaultsTmp;

    return node_id_;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function __search_SetDefaultResult
-- -----------------------------------------------------
DROP function IF EXISTS `__search_SetDefaultResult`;

DELIMITER $$$



create function __search_SetDefaultResult(session_id_ bigint unsigned) returns int unsigned
not deterministic modifies sql data
begin 
    insert searchResultT(sessionId, nodeId, collapsed)
    select session_id_, t.id, 'y'
    from tagV t
    join nodeRootLinkV nrl on t.id = nrl.dstNodeId and nrl.metric = 1
    where t.canSearch = 'y'
        and (nrl.typeRequiredForObject = 'y' or t.typeRequiredForObject = 'y')
        and nrl.displayNode = 'y'
        and not exists(
            select 1
            from nodeLinkChainTransitionV nl
            where nl.nodeLinkTypeId = nrl.nodeLinkTypeId
                and nl.srcNodeId = nrl.id
                and nl.dstNodeId = t.id)
    on duplicate key update collapsed = 'y';

    return 1;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function __search_CheckRequiredForObject
-- -----------------------------------------------------
DROP function IF EXISTS `__search_CheckRequiredForObject`;

DELIMITER $$$



-- must be called after __search_RectifyCriteria
create function __search_CheckRequiredForObject(session_id_ bigint unsigned) returns int unsigned
comment 'must be called after __search_RectifyCriteria'
not deterministic
begin
    declare session_id_ bigint unsigned default search_CurrentId();
    declare total_required, criterion_required, parent_required int unsigned;

    select count(*) into total_required
    from nodeTypeT
    where requiredForObject = 'y';

    select count(distinct nt.id) into criterion_required
    from searchCriterionT sc
    join nodeT n on sc.nodeId = n.id
    join nodeTypeT nt on n.nodeTypeId = nt.id
    where sc.sessionId = session_id_
        and nt.canSearch = 'y'
        and nt.requiredForObject = 'y';

    select count(distinct nt.id) into parent_required
    from searchCriterionT sc
    join nodeLinkOwnsV nlo on sc.nodeId = nlo.dstNodeId
    join nodeT n on nlo.srcNodeId = n.id
    join nodeTypeT nt on n.nodeTypeId = nt.id
    where sc.sessionId = session_id_
        and nlo.metric = 1
        and nt.canSearch = 'n'
        and nt.requiredForObject = 'y';

    return (criterion_required + parent_required) = total_required;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function tag_CreateDef
-- -----------------------------------------------------
DROP function IF EXISTS `tag_CreateDef`;

DELIMITER $$$



create function tag_CreateDef(
    type_id_ int unsigned,
    type_code_ varchar(30) binary,
    name_ varchar(255)
) returns int unsigned
not deterministic modifies sql data
return tag_Create(type_id_, type_code_, name_, null, null, null, null, null, null, null)$$$

DELIMITER ;

-- -----------------------------------------------------
-- function nodeLink_Unlink
-- -----------------------------------------------------
DROP function IF EXISTS `nodeLink_Unlink`;

DELIMITER $$$



create function nodeLink_Unlink(
    link_code_ varchar(30) binary, 
    src_id_ int unsigned, 
    dst_id_ int unsigned
) returns int unsigned
comment 'unlinks src_id and dst_id, links dst_id to parents of src_id'
not deterministic modifies sql data
begin
    declare link_code varchar(30) binary;
    declare link_id int unsigned;
    declare min_metric, link_to_parent int;
    
    select nlt.id, nlt.code into link_id, link_code
    from nodeLinkTypeT nlt join nodeLinkTypeT nltdef on nltdef.isDefault = 'y'
    where nlt.code = coalesce(link_code_, nltdef.code);
    
    if src_id_ = dst_id_ 
        or not exists(
            select 1 
            from nodeLinkT nl 
            where nl.nodeLinkTypeId = link_id
                and nl.srcNodeId = src_id_
                and nl.dstNodeId = dst_id_)
    then
        return 1;
    end if;

    drop temporary table if exists nodeLinkUnlinkDeletes;
    create temporary table nodeLinkUnlinkDeletes(srcNodeId int unsigned not null primary key) engine=MEMORY;

-- add nodes to remove list - child for src_id and parent for dst_id, but with special condition
    insert nodeLinkUnlinkDeletes(srcNodeId)
    select nl.srcNodeId
    from nodeLinkT nl
    where nl.nodeLinkTypeId = link_id
        and nl.dstNodeId = dst_id_
        and nl.metric = 1
        and exists (
            select 1 
            from nodeLinkT nl1
            where nl1.nodeLinkTypeId = link_id
                and nl1.srcNodeId = src_id_
                and nl1.dstNodeId = nl.srcNodeId
                and nl1.metric > 0)
-- check if node in question is not linked to other nodes of different types - if linked to more than 1 we cannot remove it
        and (select count(distinct n2.nodeTypeId)
            from nodeLinkT nl21
            join nodeLinkT nl22 on nl21.nodeLinkTypeId = nl22.nodeLinkTypeId and nl21.srcNodeId = nl22.srcNodeId
            join nodeT n2 on nl21.srcNodeId = n2.id
            where nl21.nodeLinkTypeId = link_id
                and nl21.srcNodeId != src_id_
                and nl21.dstNodeId = dst_id_
                and nl21.metric = 1
                and nl22.dstNodeId = nl.srcNodeId
                and nl22.metric = 1
                and n2.isRoot = 'n') < 2; -- 0 or 1

    set @1 = nodeLink_UpdateStart();
    
    set @2 = __nodeLink_Delete(link_code, src_id_, dst_id_);
    
    insert zzzDevNullLinkT(n)
    select __nodeLink_Delete(link_code, nlud.srcNodeId, dst_id_)
    from nodeLinkUnlinkDeletes nlud;

    -- FORWARD: __node_LinkDefaults is defined in _03_node
    set @3 = __node_LinkDefaults(link_code, dst_id_);
    
    set @4 = nodeLink_UpdateEnd();
    
    drop temporary table nodeLinkUnlinkDeletes;
    
    return 1;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function nodeLink_Unlink2
-- -----------------------------------------------------
DROP function IF EXISTS `nodeLink_Unlink2`;

DELIMITER $$$



create function nodeLink_Unlink2(
    link_code_ varchar(30) binary, 
    src_id_ int unsigned, 
    dst_id_ int unsigned
) returns int unsigned
comment 'calls nodeLink_Unlink and returns dst_id'
not deterministic modifies sql data
begin
    set @1 = nodeLink_Unlink(link_code_, src_id_, dst_id_);
    return dst_id_;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure __search_SearchObjects
-- -----------------------------------------------------
DROP procedure IF EXISTS `__search_SearchObjects`;

DELIMITER $$$



create procedure __search_SearchObjects(
    session_id_ bigint unsigned, 
    count_notnull_ int, 
    sum_null_ double, 
    sum_null_tolerance_ double,
    count_numerics_ int, 
    count_datetimes_ int)
modifies sql data
begin
    insert ignore searchResultT(sessionId, nodeId)
    select session_id_, n.id
    from nodeT n
    join nodeLinkTypeT nlt on nlt.isDefault = 'y'
    join objectT o on n.id = o.id
    join objectStatusT os on o.statusId = os.id
    where os.public = 'y'
-- if link exists at all
--        and exists(
--            select 1
--            from nodeLinkT nl1
--            join searchCriterionT sc1 on nl1.srcNodeId = sc1.nodeId
--            where sc1.sessionId = session_id_
--                and nl1.nodeLinkTypeId = nlt.id
--                and nl1.dstNodeId = n.id)
-- if properly linked to all non "searchAnd" tags
        and count_notnull_ =
            (select count(distinct sc3.nodeTypeId)
                from nodeLinkT nl3
                join searchCriterionT sc3 on nl3.srcNodeId = sc3.nodeId
                where sc3.sessionId = session_id_
                    and sc3.nodeTypeId is not null
                    and sc3.internalTypeId = 'tag'
                    and nl3.nodeLinkTypeId = nlt.id
                    and nl3.dstNodeId = n.id
                    and nl3.metric = 1)
-- if properly linked to all "searchAnd" tags and the sum of all factors greater than some threshold
        and (sum_null_ - sum_null_tolerance_) <
            coalesce(
                (select sum(t5.factor)
                from nodeLinkT nl5
                join searchCriterionT sc5 on nl5.srcNodeId = sc5.nodeId
                join tagT t5 on sc5.nodeId = t5.id
                where sc5.sessionId = session_id_
                    and sc5.nodeTypeId is null
                    and sc5.internalTypeId = 'tag'
                    and nl5.nodeLinkTypeId = nlt.id
                    and nl5.dstNodeId = n.id
                    and nl5.metric = 1),
                0)
-- if properly linked to all numeric tags
        and count_numerics_ = 
            (select count(*)
                from nodeLinkT nl7
                join searchCriterionNumericT scn7 on nl7.srcNodeId = scn7.nodeId
                join attributeValueNumericT avn7 on scn7.nodeId = avn7.attributeId
                where scn7.sessionId = session_id_
                    and nl7.nodeLinkTypeId = nlt.id
                    and nl7.dstNodeId = n.id
                    and nl7.metric = 1
                    and avn7.objectId = n.id
                    and avn7.value >= coalesce(scn7.valueLow, avn7.value)
                    and avn7.value <= coalesce(scn7.valueHigh, avn7.value))
-- if properly linked to all datetime tags
        and count_datetimes_ = 
            (select count(*)
                from nodeLinkT nl9
                join searchCriterionDatetimeT scd9 on nl9.srcNodeId = scd9.nodeId
                join attributeValueDatetimeT avd9 on scd9.nodeId = avd9.attributeId
                where scd9.sessionId = session_id_
                    and nl9.nodeLinkTypeId = nlt.id
                    and nl9.dstNodeId = n.id
                    and nl9.metric = 1
                    and avd9.objectId = n.id
                    and avd9.value >= coalesce(scd9.valueLow, avd9.value)
                    and avd9.value <= coalesce(scd9.valueHigh, avd9.value));
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure __search_SearchTags
-- -----------------------------------------------------
DROP procedure IF EXISTS `__search_SearchTags`;

DELIMITER $$$



create procedure __search_SearchTags(
    session_id_ bigint unsigned, 
    count_notnull_ int, 
    count_null_ int)
modifies sql data
begin
-- add searchable tags which don't have parents
    insert ignore searchResultT(sessionId, nodeId)
    select session_id_, n.id
    from nodeT n
    join nodeLinkTypeT nlt on nlt.isDefault = 'y'
    join nodeTypeT nt on n.nodeTypeId = nt.id
    join tagT t on n.id = t.id and t.canSearch = 'y'
    where nt.requiredForObject = 'n'
        and not exists(
            select 1
            from nodeLinkT nl
            join nodeT nonrootnode on nl.srcNodeId = nonrootnode.id
            join tagT nonroottag on nonrootnode.id = nonroottag.id
            where nl.nodeLinkTypeId = nlt.id
                and nl.dstNodeId = n.id
                and nl.metric = 1
                and nonrootnode.isRoot = 'n');

    insert ignore searchResultT(sessionId, nodeId)
    select session_id_, n.id
    from nodeT n
    join nodeLinkTypeT nlt on nlt.isDefault = 'y'
    join tagT t on n.id = t.id and t.canSearch = 'y'
    where 1=1
-- if link exists at all
        and exists(
            select 1
            from nodeLinkT nl1
            join searchCriterionT sc1 on nl1.srcNodeId = sc1.nodeId
            where sc1.sessionId = session_id_
                and nl1.nodeLinkTypeId = nlt.id
                and nl1.dstNodeId = n.id
                and nl1.metric = 1)
-- if properly linked to all non "searchAnd" tags
        and (select count(distinct n2.nodeTypeId)
                from nodeLinkT nl2
                join tagT t2 on nl2.srcNodeId = t2.id
                join nodeT n2 on t2.id = n2.id
                where nl2.nodeLinkTypeId = nlt.id
                    and nl2.dstNodeId = n.id
                    and nl2.metric = 1
                    and t2.canSearch = 'y') 
            =
            (select count(distinct sc3.nodeTypeId)
                from nodeLinkT nl3
                join searchCriterionT sc3 on nl3.srcNodeId = sc3.nodeId
                where sc3.sessionId = session_id_
                    and sc3.nodeTypeId is not null
                    and nl3.nodeLinkTypeId = nlt.id
                    and nl3.dstNodeId = n.id
                    and nl3.metric = 1);
-- if properly linked to all "searchAnd" tags
/*        and (select count(*)
                from nodeLinkDirectV nl5
                join searchCriterionT sc5 on nl5.srcNodeId = sc5.nodeId
                where sc5.sessionId = session_id_
                    and sc5.nodeTypeId is null
                    and nl5.nodeLinkTypeId = nlt.id
                    and nl5.dstNodeId = n.id
                    and nl5.metric = 1)
            = count_null_;*/
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function __nodeAncestry_Update
-- -----------------------------------------------------
DROP function IF EXISTS `__nodeAncestry_Update`;

DELIMITER $$$



-- node ancestry support, based on workset

create function __nodeAncestry_Update() returns int unsigned
not deterministic modifies sql data
begin
    insert nodeAncestryT(id, languageId, tagNames)
    select 
        nad.dstNodeId,
        nad.languageId,
        group_concat(
            concat_ws(
                0xb, 
                nad.srcTypeId,
                nad.srcTypeName,
                if(
                    nad.srcInternalTypeId = 'attribute',
                    coalesce(
                        nad.srcNumericValue,
                        date_format(nad.srcDatetimeValue, nad.srcDatetimeOutputFormat), 
                        replace(replace(nad.srcTextValue, 0xb, ''), '\0', ''), 
                        ''), 
                    nad.srcName))
            order by nad.ancestrySorter, nad.srcTypeName, nad.srcName
            separator '\0') tag_names
    from nodeAncestryDataV nad
    join zzzNodeWorksetV nws on nad.dstNodeId = nws.nodeId
    where nws.isMain = 1
    group by nad.dstNodeId, nad.languageId
    on duplicate key update
        nodeAncestryT.tagNames = values(tagNames);

    return 1;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function __nodeAncestry_UpdateAll
-- -----------------------------------------------------
DROP function IF EXISTS `__nodeAncestry_UpdateAll`;

DELIMITER $$$



create function __nodeAncestry_UpdateAll(reset_data_ int) returns int unsigned
not deterministic modifies sql data
comment 'updates all nodes ancestry; to be called by hand when needed'
begin
    delete from nodeAncestryT
    where coalesce(reset_data_, 0);
    
    insert nodeAncestryT(id, languageId, tagNames)
    select 
        nad.dstNodeId,
        nad.languageId,
        group_concat(
            concat_ws(
                0xb, 
                nad.srcTypeId,
                nad.srcTypeName,
                if(
                    nad.srcInternalTypeId = 'attribute',
                    coalesce(
                        nad.srcNumericValue,
                        date_format(nad.srcDatetimeValue, nad.srcDatetimeOutputFormat), 
                        replace(replace(nad.srcTextValue, 0xb, ''), '\0', ''), 
                        ''), 
                    nad.srcName))
            order by nad.ancestrySorter, nad.srcTypeName, nad.srcName
            separator '\0') tag_names
    from nodeAncestryDataV nad
    group by nad.dstNodeId, nad.languageId
    on duplicate key update
        nodeAncestryT.tagNames = values(tagNames);

    return 1;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function __attribute_ValidateId
-- -----------------------------------------------------
DROP function IF EXISTS `__attribute_ValidateId`;

DELIMITER $$$
create function __attribute_ValidateId(attribute_id_ int unsigned) returns int unsigned
deterministic
begin
    declare attribute_id int unsigned;
    
    select a.id into attribute_id
    from attributeT a
    join tagT t on a.id = t.id
    join nodeT n on a.id = n.id
    join nodeTypeT nt on n.nodeTypeId = nt.id
    where a.id = attribute_id_
        and nt.internalTypeId = 'attribute';
    
    if attribute_id is null then 
        call raiseError1('attribute', 'validate', attribute_id_, 'attribute does not exist');
    end if;

    return 1;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function __attribute_ValidateIdDataType
-- -----------------------------------------------------
DROP function IF EXISTS `__attribute_ValidateIdDataType`;

DELIMITER $$$



create function __attribute_ValidateIdDataType(
    attribute_id_ int unsigned, 
    type_name_ varchar(10) binary
) returns int unsigned
deterministic
begin
    set @1 = __attribute_ValidateId(attribute_id_);
    if (select a.dataTypeId from attributeT a where a.id = attribute_id_) != type_name_ then
        call raiseError2('attribute', 'typecheck', attribute_id_, type_name_, 'attribute mismatching type');
    end if;
    
    return 1;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function __attribute_ClampValueNumeric
-- -----------------------------------------------------
DROP function IF EXISTS `__attribute_ClampValueNumeric`;

DELIMITER $$$



create function __attribute_ClampValueNumeric(attribute_id_ int unsigned, value_ decimal(22, 6)) returns decimal(22, 6)
deterministic
begin
    declare res decimal(22, 6);
    
    select round(
                greatest(
                    least(
                        value_, 
                        coalesce(a.decimalMax, value_)), 
                    coalesce(a.decimalMin, value_)), 
                a.decimalRound)
    into res
    from attributeNumericT a
    where a.id = attribute_id_;
    
    return res;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function __attribute_ClampValueDatetime
-- -----------------------------------------------------
DROP function IF EXISTS `__attribute_ClampValueDatetime`;

DELIMITER $$$



create function __attribute_ClampValueDatetime(attribute_id_ int unsigned, value_ datetime) returns datetime
deterministic
begin
    declare res datetime;
    
    select cast(date_format(value_, adf.storageFormat) as datetime)
    into res
    from attributeDatetimeT a
    join attributeDatetimeFormatT adf on a.datetimeStorageId = adf.id
    where a.id = attribute_id_;
    
    return res;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function __attribute_Create
-- -----------------------------------------------------
DROP function IF EXISTS `__attribute_Create`;

DELIMITER $$$



create function __attribute_Create(
    type_id_ int unsigned,
    type_code_ varchar(30) binary,
    name_ varchar(255),
    sorter_ int,
    can_search_ enum('n', 'y'),
    display_in_object_ enum('n', 'y'),
    display_in_object_list_ enum('n', 'y'),
    factor_ double,
    enabled_ enum('n', 'y'),
    data_type_id_ varchar(10) binary,
    code_ varchar(30) binary,
    required_ enum('n', 'y'),
    sortable_ enum('n', 'y'),
    uofm_pre_ varchar(255),
    uofm_post_ varchar(255)
) returns int unsigned
not deterministic modifies sql data
begin
    declare attribute_id int unsigned;
    
    set attribute_id = __tag_Create(type_id_, type_code_, 'attribute', name_, sorter_, can_search_, display_in_object_, display_in_object_list_, factor_, null, enabled_);
    
    insert attributeT(id, dataTypeId, code, required, sortable)
    values(attribute_id, data_type_id_, code_, required_, sortable_);
        
    insert attributeTL(id, languageId, uofmPre, uofmPost)
    select attribute_id, l.id, coalesce(uofm_pre_, ''), coalesce(uofm_post_, '')
    from languageT l;
    
    set @1 = __attribute_UpdateCache(attribute_id);
    
    return attribute_id;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function __attribute_SetNumeric
-- -----------------------------------------------------
DROP function IF EXISTS `__attribute_SetNumeric`;

DELIMITER $$$



create function __attribute_SetNumeric(
    attribute_id_ int unsigned,
    decimal_min_ decimal(22, 6), reset_decimal_min_ enum('n', 'y'),
    decimal_max_ decimal(22, 6), reset_decimal_max_ enum('n', 'y'),
    decimal_round_ int
) returns int unsigned
not deterministic modifies sql data
begin
    insert attributeNumericT(
        id, 
        decimalMin, 
        decimalMax, 
        decimalRound)
    values(
        attribute_id_,
        decimal_min_,
        decimal_max_,
        decimal_round_)
    on duplicate key update
        attributeNumericT.decimalMin = if(reset_decimal_min_ = 'y', null, coalesce(decimal_min_, attributeNumericT.decimalMin)),
        attributeNumericT.decimalMax = if(reset_decimal_max_ = 'y', null, coalesce(decimal_max_, attributeNumericT.decimalMax)),
        attributeNumericT.decimalRound = coalesce(decimal_round_, attributeNumericT.decimalRound);

    return attribute_id_;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function __attribute_SetDatetime
-- -----------------------------------------------------
DROP function IF EXISTS `__attribute_SetDatetime`;

DELIMITER $$$



create function __attribute_SetDatetime(
    attribute_id_ int unsigned,
    datetime_storage_id_ varchar(10) binary
) returns int unsigned
not deterministic modifies sql data
begin
    insert attributeDatetimeT(
        id, 
        datetimeStorageId)
    values(
        attribute_id_,
        datetime_storage_id_)
    on duplicate key update
        attributeDatetimeT.datetimeStorageId = coalesce(datetime_storage_id_, attributeDatetimeT.datetimeStorageId);

    return attribute_id_;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function __attribute_SetText
-- -----------------------------------------------------
DROP function IF EXISTS `__attribute_SetText`;

DELIMITER $$$



create function __attribute_SetText(
    attribute_id_ int unsigned,
    text_is_long_ enum('n', 'y')
) returns int unsigned
not deterministic modifies sql data
begin
    insert attributeTextT(
        id, 
        textIsLong)
    values(
        attribute_id_,
        text_is_long_)
    on duplicate key update
        attributeTextT.textIsLong = coalesce(text_is_long_, attributeTextT.textIsLong);

    return attribute_id_;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function attribute_CreateNumeric
-- -----------------------------------------------------
DROP function IF EXISTS `attribute_CreateNumeric`;

DELIMITER $$$



create function attribute_CreateNumeric(
    type_id_ int unsigned,
    type_code_ varchar(30) binary,
    name_ varchar(255),
    sorter_ int,
    can_search_ enum('n', 'y'),
    display_in_object_ enum('n', 'y'),
    display_in_object_list_ enum('n', 'y'),
    factor_ double,
    enabled_ enum('n', 'y'),
    code_ varchar(30) binary,
    required_ enum('n', 'y'),
    sortable_ enum('n', 'y'),
    uofm_pre_ varchar(255),
    uofm_post_ varchar(255),
    decimal_min_ decimal(22, 6),
    decimal_max_ decimal(22, 6),
    decimal_round_ int
) returns int unsigned
not deterministic modifies sql data
begin
    declare attribute_id int unsigned;
    
    set attribute_id = __attribute_Create(
        type_id_, 
        type_code_, 
        name_, 
        sorter_, 
        can_search_, 
        display_in_object_,
        display_in_object_list_,
        factor_,
        enabled_, 
        'numeric', 
        code_,
        required_,
        sortable_,
        uofm_pre_, 
        uofm_post_);
    
    set @1 = __attribute_SetNumeric(attribute_id, decimal_min_, null, decimal_max_, null, decimal_round_);
    
    return attribute_id;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function attribute_CreateDatetime
-- -----------------------------------------------------
DROP function IF EXISTS `attribute_CreateDatetime`;

DELIMITER $$$



create function attribute_CreateDatetime(
    type_id_ int unsigned,
    type_code_ varchar(30) binary,
    name_ varchar(255),
    sorter_ int,
    can_search_ enum('n', 'y'),
    display_in_object_ enum('n', 'y'),
    display_in_object_list_ enum('n', 'y'),
    factor_ double,
    enabled_ enum('n', 'y'),
    code_ varchar(30) binary,
    required_ enum('n', 'y'),
    sortable_ enum('n', 'y'),
    uofm_pre_ varchar(255),
    uofm_post_ varchar(255),
    datetime_storage_id_ varchar(10) binary
) returns int unsigned
not deterministic modifies sql data
begin
    declare attribute_id int unsigned;
    
    set attribute_id = __attribute_Create(
        type_id_, 
        type_code_, 
        name_, 
        sorter_, 
        can_search_, 
        display_in_object_,
        display_in_object_list_,
        factor_,
        enabled_, 
        'datetime', 
        code_,
        required_,
        sortable_,
        uofm_pre_, 
        uofm_post_);
    
    set @1 = __attribute_SetDatetime(attribute_id, datetime_storage_id_);
    
    return attribute_id;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function attribute_CreateText
-- -----------------------------------------------------
DROP function IF EXISTS `attribute_CreateText`;

DELIMITER $$$



create function attribute_CreateText(
    type_id_ int unsigned,
    type_code_ varchar(30) binary,
    name_ varchar(255),
    sorter_ int,
    can_search_ enum('n', 'y'),
    display_in_object_ enum('n', 'y'),
    display_in_object_list_ enum('n', 'y'),
    factor_ double,
    enabled_ enum('n', 'y'),
    code_ varchar(30) binary,
    required_ enum('n', 'y'),
    sortable_ enum('n', 'y'),
    uofm_pre_ varchar(255),
    uofm_post_ varchar(255),
    text_is_long_ enum('n', 'y')
) returns int unsigned
not deterministic modifies sql data
begin
    declare attribute_id int unsigned;
    
    set attribute_id = __attribute_Create(
        type_id_, 
        type_code_, 
        name_, 
        sorter_, 
        can_search_, 
        display_in_object_,
        display_in_object_list_,
        factor_,
        enabled_, 
        'text', 
        code_,
        required_,
        sortable_,
        uofm_pre_, 
        uofm_post_);
    
    set @1 = __attribute_SetText(attribute_id, text_is_long_);
    
    return attribute_id;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function attribute_Delete
-- -----------------------------------------------------
DROP function IF EXISTS `attribute_Delete`;

DELIMITER $$$



create function attribute_Delete(attribute_id_ int unsigned) returns int unsigned
not deterministic modifies sql data
begin
    set @1 = __attribute_ValidateId(attribute_id_);
    return __tag_Delete(attribute_id_); -- call ancestor function
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function __attribute_Update
-- -----------------------------------------------------
DROP function IF EXISTS `__attribute_Update`;

DELIMITER $$$



create function __attribute_Update(
    attribute_id_ int unsigned,
    sorter_ int,
    can_search_ enum('n', 'y'), reset_can_search_ enum('n', 'y'), 
    display_in_object_ enum('n', 'y'), reset_display_in_object_ enum('n', 'y'),
    display_in_object_list_ enum('n', 'y'), reset_display_in_object_list_ enum('n', 'y'),
    factor_ double,
    enabled_ enum('n', 'y'),
    code_ varchar(30) binary,
    required_ enum('n', 'y'),
    sortable_ enum('n', 'y')
) returns int unsigned
not deterministic modifies sql data
begin
    set @1 = __tag_Update(
                    attribute_id_, 
                    sorter_, 
                    can_search_, reset_can_search_, 
                    display_in_object_, reset_display_in_object_,
                    display_in_object_list_, reset_display_in_object_list_,
                    factor_, 
                    null,
                    enabled_);
    
    update attributeT set
        code = if(code_ = '', null, coalesce(code_, code)),
        required = coalesce(required_, required),
        sortable = coalesce(sortable_, sortable)
    where id = attribute_id_;

    -- as of now - no __attribute_UpdateCache here because cache does not depend on attributeT

    return attribute_id_;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function attribute_Update
-- -----------------------------------------------------
DROP function IF EXISTS `attribute_Update`;

DELIMITER $$$



create function attribute_Update(
    attribute_id_ int unsigned,
    sorter_ int,
    can_search_ enum('n', 'y'), reset_can_search_ enum('n', 'y'), 
    display_in_object_ enum('n', 'y'), reset_display_in_object_ enum('n', 'y'),
    display_in_object_list_ enum('n', 'y'), reset_display_in_object_list_ enum('n', 'y'),
    factor_ double,
    enabled_ enum('n', 'y'),
    code_ varchar(30) binary,
    required_ enum('n', 'y'),
    sortable_ enum('n', 'y')
) returns int unsigned
not deterministic modifies sql data
begin
    set @1 = __attribute_ValidateId(attribute_id_);

    set @2 = __attribute_Update(
                        attribute_id_, 
                        sorter_, 
                        can_search_, reset_can_search_, 
                        display_in_object_, reset_display_in_object_,
                        display_in_object_list_, reset_display_in_object_list_,
                        factor_, 
                        enabled_, 
                        code_,
                        required_,
                        sortable_);

    set @3 = __object_UpdateSortCache(null, attribute_id_, null);

    return attribute_id_;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function __attribute_UpdateNumericValues
-- -----------------------------------------------------
DROP function IF EXISTS `__attribute_UpdateNumericValues`;

DELIMITER $$$



create function __attribute_UpdateNumericValues(attribute_id_ int unsigned) returns int unsigned
not deterministic modifies sql data
begin
    update attributeValueNumericT set
        value = __attribute_ClampValueNumeric(attribute_id_, valueRaw)
    where attributeId = attribute_id_;

    return attribute_id_;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function attribute_UpdateNumeric
-- -----------------------------------------------------
DROP function IF EXISTS `attribute_UpdateNumeric`;

DELIMITER $$$



create function attribute_UpdateNumeric(
    attribute_id_ int unsigned,
    sorter_ int,
    can_search_ enum('n', 'y'), reset_can_search_ enum('n', 'y'), 
    display_in_object_ enum('n', 'y'), reset_display_in_object_ enum('n', 'y'),
    display_in_object_list_ enum('n', 'y'), reset_display_in_object_list_ enum('n', 'y'),
    factor_ double,
    enabled_ enum('n', 'y'),
    code_ varchar(30) binary,
    required_ enum('n', 'y'),
    sortable_ enum('n', 'y'),
    decimal_min_ decimal(22, 6), reset_decimal_min_ enum('n', 'y'),
    decimal_max_ decimal(22, 6), reset_decimal_max_ enum('n', 'y'),
    decimal_round_ int
) returns int unsigned
not deterministic modifies sql data
begin
    set @1 = __attribute_ValidateIdDataType(attribute_id_, 'numeric');
    
    set @2 = __attribute_Update(
                        attribute_id_, 
                        sorter_, 
                        can_search_, reset_can_search_, 
                        display_in_object_, reset_display_in_object_,
                        display_in_object_list_, reset_display_in_object_list_,
                        factor_, 
                        enabled_, 
                        code_,
                        required_,
                        sortable_);
    
    set @3 = __attribute_SetNumeric(attribute_id_, decimal_min_, reset_decimal_min_, decimal_max_, reset_decimal_max_, decimal_round_);
    
    set @4 = __attribute_UpdateNumericValues(attribute_id_);

    set @5 = __object_UpdateSortCache(null, attribute_id_, null);

    return attribute_id_;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function __attribute_UpdateDatetimeValues
-- -----------------------------------------------------
DROP function IF EXISTS `__attribute_UpdateDatetimeValues`;

DELIMITER $$$



create function __attribute_UpdateDatetimeValues(attribute_id_ int unsigned) returns int unsigned
not deterministic modifies sql data
begin
    update attributeValueDatetimeT set
        value = __attribute_ClampValueDatetime(attribute_id_, valueRaw)
    where attributeId = attribute_id_;

    return attribute_id_;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function attribute_UpdateDatetime
-- -----------------------------------------------------
DROP function IF EXISTS `attribute_UpdateDatetime`;

DELIMITER $$$



create function attribute_UpdateDatetime(
    attribute_id_ int unsigned,
    sorter_ int,
    can_search_ enum('n', 'y'), reset_can_search_ enum('n', 'y'), 
    display_in_object_ enum('n', 'y'), reset_display_in_object_ enum('n', 'y'),
    display_in_object_list_ enum('n', 'y'), reset_display_in_object_list_ enum('n', 'y'),
    factor_ double,
    enabled_ enum('n', 'y'),
    code_ varchar(30) binary,
    required_ enum('n', 'y'),
    sortable_ enum('n', 'y'),
    datetime_storage_id_ varchar(10) binary
) returns int unsigned
not deterministic modifies sql data
begin
    set @1 = __attribute_ValidateIdDataType(attribute_id_, 'datetime');
    
    set @2 = __attribute_Update(
                        attribute_id_, 
                        sorter_, 
                        can_search_, reset_can_search_, 
                        display_in_object_, reset_display_in_object_,
                        display_in_object_list_, reset_display_in_object_list_,
                        factor_, 
                        enabled_, 
                        code_,
                        required_,
                        sortable_);
    
    set @3 = __attribute_SetDatetime(attribute_id_, datetime_storage_id_);
    
    set @4 = __attribute_UpdateDatetimeValues(attribute_id_);
    
    set @5 = __object_UpdateSortCache(null, attribute_id_, null);
    
    return attribute_id_;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function attribute_UpdateText
-- -----------------------------------------------------
DROP function IF EXISTS `attribute_UpdateText`;

DELIMITER $$$



create function attribute_UpdateText(
    attribute_id_ int unsigned,
    sorter_ int,
    can_search_ enum('n', 'y'), reset_can_search_ enum('n', 'y'), 
    display_in_object_ enum('n', 'y'), reset_display_in_object_ enum('n', 'y'),
    display_in_object_list_ enum('n', 'y'), reset_display_in_object_list_ enum('n', 'y'),
    factor_ double,
    enabled_ enum('n', 'y'),
    code_ varchar(30) binary,
    required_ enum('n', 'y'),
    sortable_ enum('n', 'y'),
    text_is_long_ enum('n', 'y')
) returns int unsigned
not deterministic modifies sql data
begin
    set @1 = __attribute_ValidateIdDataType(attribute_id_, 'text');
    
    set @2 = __attribute_Update(
                        attribute_id_, 
                        sorter_, 
                        can_search_, reset_can_search_, 
                        display_in_object_, reset_display_in_object_,
                        display_in_object_list_, reset_display_in_object_list_,
                        factor_, 
                        enabled_, 
                        code_,
                        required_,
                        sortable_);
    
    set @3 = __attribute_SetText(attribute_id_, text_is_long_);
    
    return attribute_id_;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function attribute_UpdateLocal
-- -----------------------------------------------------
DROP function IF EXISTS `attribute_UpdateLocal`;

DELIMITER $$$



create function attribute_UpdateLocal(
    attribute_id_ int unsigned,
    lang_id_ char(3),
    name_ varchar(255),
    uofm_pre_ varchar(255),
    uofm_post_ varchar(255)
) returns int unsigned
not deterministic modifies sql data
begin
    set @1 = __attribute_ValidateId(attribute_id_);
    
    set @2 = __tag_UpdateLocal(attribute_id_, lang_id_, name_);
    
    insert attributeTL(id, languageId, uofmPre, uofmPost)
    select attribute_id_, l.id, coalesce(uofm_pre_, ''), coalesce(uofm_post_, '')
    from languageT l
    where l.id = coalesce(lang_id_, l.id)
    on duplicate key update 
        attributeTL.uofmPre = coalesce(uofm_pre_, attributeTL.uofmPre),
        attributeTL.uofmPost = coalesce(uofm_post_, attributeTL.uofmPost);
        
    set @3 = __attribute_UpdateCache(attribute_id_);

    return attribute_id_;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function __attribute_ValidateValueByType
-- -----------------------------------------------------
DROP function IF EXISTS `__attribute_ValidateValueByType`;

DELIMITER $$$



-- --------------------------------------------------------------------------------
-- attribute values
-- --------------------------------------------------------------------------------

create function __attribute_ValidateValueByType(
    data_type_ varchar(10) binary,
    value_numeric_ decimal(22, 6),
    value_datetime_ datetime,
    value_text_ text
) returns int unsigned
deterministic
begin
    if (data_type_ = 'numeric' and value_numeric_ is null) then
        call raiseError2('attribute', 'validateValue', attribute_id_, 'numeric', 'no numeric value');
    elseif (data_type_ = 'datetime' and value_datetime_ is null) then
        call raiseError2('attribute', 'validateValue', attribute_id_, 'datetime', 'no datetime value');
    elseif (data_type_ = 'text' and value_text_ is null) then
        call raiseError2('attribute', 'validateValue', attribute_id_, 'text', 'no text value');
    end if;

    return 1;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function __attribute_SetValueNumeric
-- -----------------------------------------------------
DROP function IF EXISTS `__attribute_SetValueNumeric`;

DELIMITER $$$



create function __attribute_SetValueNumeric(
    attribute_id_ int unsigned,
    object_id_ int unsigned,
    value_numeric_ decimal(22, 6)
) returns int unsigned
not deterministic modifies sql data
begin
    if value_numeric_ is null then
        delete from attributeValueNumericT
        where attributeId = attribute_id_
            and objectId = object_id_;
    else
        insert attributeValueNumericT(attributeId, objectId, value, valueRaw)
        values(attribute_id_, object_id_, __attribute_ClampValueNumeric(attribute_id_, value_numeric_), value_numeric_)
        on duplicate key update 
            attributeValueNumericT.value = __attribute_ClampValueNumeric(attribute_id_, value_numeric_),
            attributeValueNumericT.valueRaw = value_numeric_;
    end if;
    
    return attribute_id_;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function __attribute_SetValueDatetime
-- -----------------------------------------------------
DROP function IF EXISTS `__attribute_SetValueDatetime`;

DELIMITER $$$



create function __attribute_SetValueDatetime(
    attribute_id_ int unsigned,
    object_id_ int unsigned,
    value_datetime_ datetime
) returns int unsigned
not deterministic modifies sql data
begin
    if value_datetime_ is null then
        delete from attributeValueDatetimeT
        where attributeId = attribute_id_
            and objectId = object_id_;
    else
        insert attributeValueDatetimeT(attributeId, objectId, value, valueRaw)
        values(attribute_id_, object_id_, __attribute_ClampValueDatetime(attribute_id_, value_datetime_), value_datetime_)
        on duplicate key update 
            attributeValueDatetimeT.value = __attribute_ClampValueDatetime(attribute_id_, value_datetime_),
            attributeValueDatetimeT.valueRaw = value_datetime_;
    end if;
        
    return attribute_id_;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function __attribute_SetValueText
-- -----------------------------------------------------
DROP function IF EXISTS `__attribute_SetValueText`;

DELIMITER $$$



create function __attribute_SetValueText(
    attribute_id_ int unsigned,
    object_id_ int unsigned,
    lang_id_ char(3),
    value_text_ text
) returns int unsigned
not deterministic modifies sql data
begin
    if value_text_ is null then
        delete from attributeValueTextT
        where attributeId = attribute_id_
            and objectId = object_id_;
    else
        insert attributeValueTextT(attributeId, objectId, languageId, value)
        select attribute_id_, object_id_, l.id, value_text_
        from languageT l
        where l.id = coalesce(lang_id_, l.id)
        on duplicate key update 
            attributeValueTextT.value = value_text_;
    end if;
        
    return attribute_id_;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function __attribute_SetLink
-- -----------------------------------------------------
DROP function IF EXISTS `__attribute_SetLink`;

DELIMITER $$$



create function __attribute_SetLink(
    attribute_id_ int unsigned,
    object_id_ int unsigned,
    do_link_setup_ int unsigned
) returns int unsigned
not deterministic modifies sql data
begin
    if do_link_setup_ then
        set @1 = nodeLink_UpdateStart();

        set @2 = nodeLink_Relink(null, attribute_id_, object_id_);

        set @3 = __nodeLink_UpdateWorkSet(attribute_id_, -1) and __nodeLink_UpdateWorkSet(object_id_, 1);

        set @4 = nodeLink_UpdateEnd();
    else
        set @1 = nodeLink_Unlink(null, attribute_id_, object_id_);
    end if;
    
    return attribute_id_;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function attribute_SetValueNumeric
-- -----------------------------------------------------
DROP function IF EXISTS `attribute_SetValueNumeric`;

DELIMITER $$$



create function attribute_SetValueNumeric(
    attribute_id_ int unsigned,
    object_id_ int unsigned,
    value_numeric_ decimal(22, 6) 
) returns int unsigned
not deterministic modifies sql data
begin
    set @1 = __attribute_ValidateIdDataType(attribute_id_, 'numeric') and __object_ValidateId(object_id_);
    
    set @3 = __attribute_SetValueNumeric(attribute_id_, object_id_, value_numeric_);

    set @2 = __attribute_SetLink(attribute_id_, object_id_, value_numeric_ is not null);
    
    set @4 = __object_UpdateSortCache(object_id_, attribute_id_, value_numeric_);

    return attribute_id_;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function attribute_SetValueDatetime
-- -----------------------------------------------------
DROP function IF EXISTS `attribute_SetValueDatetime`;

DELIMITER $$$



create function attribute_SetValueDatetime(
    attribute_id_ int unsigned,
    object_id_ int unsigned,
    value_datetime_ datetime
) returns int unsigned
not deterministic modifies sql data
begin
    set @1 = __attribute_ValidateIdDataType(attribute_id_, 'datetime') and __object_ValidateId(object_id_);
    
    set @3 = __attribute_SetValueDatetime(attribute_id_, object_id_, value_datetime_);

    set @2 = __attribute_SetLink(attribute_id_, object_id_, value_datetime_ is not null);

    set @4 = __object_UpdateSortCache(object_id_, attribute_id_, datetime_ToDouble(value_datetime_));

    return attribute_id_;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function attribute_SetValueText
-- -----------------------------------------------------
DROP function IF EXISTS `attribute_SetValueText`;

DELIMITER $$$



create function attribute_SetValueText(
    attribute_id_ int unsigned,
    object_id_ int unsigned,
    lang_id_ char(3),
    value_text_ text
) returns int unsigned
not deterministic modifies sql data
begin
    declare val text default if(value_text_ = '', null, value_text_);
    
    set @1 = __attribute_ValidateIdDataType(attribute_id_, 'text') and __object_ValidateId(object_id_);
    
    set @3 = __attribute_SetValueText(attribute_id_, object_id_, lang_id_, val);

    set @2 = __attribute_SetLink(attribute_id_, object_id_, val is not null);

    return attribute_id_;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function attribute_SetValue
-- -----------------------------------------------------
DROP function IF EXISTS `attribute_SetValue`;

DELIMITER $$$



create function attribute_SetValue(
    attribute_id_ int unsigned,
    object_id_ int unsigned,
    lang_id_ char(3),
    value_ text
) returns int unsigned
not deterministic modifies sql data
begin
    declare data_type varchar(10) default (select dataTypeId from attributeT where id = attribute_id_);
    declare val text default if(value_ = '', null, value_);
    
    set @1 = __attribute_ValidateId(attribute_id_) and __object_ValidateId(object_id_);
    
    case data_type
        when 'numeric' then
            set @3 = __attribute_SetValueNumeric(attribute_id_, object_id_, cast(val as decimal(22, 6)));
            set @4 = __object_UpdateSortCache(object_id_, attribute_id_, cast(val as decimal(22, 6)));
        when 'datetime' then
            set @3 = __attribute_SetValueDatetime(attribute_id_, object_id_, cast(val as datetime));
            set @4 = __object_UpdateSortCache(object_id_, attribute_id_, datetime_ToDouble(cast(val as datetime)));
        else
            set @3 = __attribute_SetValueText(attribute_id_, object_id_, lang_id_, val);
    end case;

    set @2 = __attribute_SetLink(attribute_id_, object_id_, val is not null);

    return attribute_id_;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function nodeLink_ResetLinks2
-- -----------------------------------------------------
DROP function IF EXISTS `nodeLink_ResetLinks2`;

DELIMITER $$$



create function nodeLink_ResetLinks2(
    link_code_ varchar(30) binary, 
    root_type_code_ varchar(30) binary, 
    dst_id_ int unsigned,
    recreate_root_links_ int
) returns int unsigned
comment 'calls nodeLink_ResetLinks and returns dst_id'
not deterministic modifies sql data
begin
    set @1 = nodeLink_ResetLinks(link_code_, root_type_code_, dst_id_, recreate_root_links_);
    return dst_id_;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function __tag_DuplicateInternal
-- -----------------------------------------------------
DROP function IF EXISTS `__tag_DuplicateInternal`;

DELIMITER $$$



create function __tag_DuplicateInternal(tag_id_ int unsigned) returns int unsigned
not deterministic modifies sql data
begin
    declare new_id int unsigned;
    
    set new_id = node_LinkDefaults(__node_Duplicate(tag_id_));
    
    insert tagT(
        id, 
        sorter, 
        canSearch, 
        canSearchRaw, 
        displayInObject,
        displayInObjectRaw,
        displayInObjectList,
        displayInObjectListRaw,
        factor, 
        ratingPoints,
        enabled)
    select 
        new_id, 
        t.sorter, 
        t.canSearch, 
        t.canSearchRaw, 
        t.displayInObject,
        t.displayInObjectRaw,
        t.displayInObjectList,
        t.displayInObjectListRaw,
        t.factor, 
        t.ratingPoints,
        t.enabled
    from tagT t
    where t.id = tag_id_;
    
    insert tagTL(id, languageId, name)
    select new_id, tl.languageId, tl.name
    from tagTL tl
    where tl.id = tag_id_;
    
    insert tagCacheT(id, tagNames, tagNamesSearch)
    select new_id, tc.tagNames, tc.tagNamesSearch
    from tagCacheT tc
    where tc.id = tag_id_;
    
    return new_id;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function __tag_Duplicate
-- -----------------------------------------------------
DROP function IF EXISTS `__tag_Duplicate`;

DELIMITER $$$



create function __tag_Duplicate(tag_id_ int unsigned) returns int unsigned
not deterministic modifies sql data
begin
    set @1 = __tag_ValidateId(tag_id_);
    
    return __tag_DuplicateInternal(tag_id_);
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function __attribute_Duplicate
-- -----------------------------------------------------
DROP function IF EXISTS `__attribute_Duplicate`;

DELIMITER $$$



create function __attribute_Duplicate(attribute_id_ int unsigned) returns int unsigned
not deterministic modifies sql data
begin
    declare new_id int unsigned;
    
    set @1 = __attribute_ValidateId(attribute_id_);
    
    set new_id = __tag_DuplicateInternal(attribute_id_);
    
    insert attributeT(id, dataTypeId, code, required, sortable)
    select new_id, a.dataTypeId, null, a.required, a.sortable
    from attributeT a
    where a.id = attribute_id_;
    
    insert attributeTL(id, languageId, uofmPre, uofmPost)
    select new_id, al.languageId, al.uofmPre, al.uofmPost
    from attributeTL al
    where al.id = attribute_id_;
    
    insert attributeCacheT(id, attributeNames, attributeNamesSearch)
    select new_id, ac.attributeNames, ac.attributeNamesSearch
    from attributeCacheT ac
    where ac.id = attribute_id_;
    
    insert attributeNumericT(id, decimalMin, decimalMax, decimalRound)
    select new_id, an.decimalMin, an.decimalMax, an.decimalRound
    from attributeNumericT an
    where an.id = attribute_id_;

    insert attributeDatetimeT(id, datetimeStorageId)
    select new_id, ad.datetimeStorageId
    from attributeDatetimeT ad
    where ad.id = attribute_id_;
    
    insert attributeTextT(id, textIsLong)
    select new_id, ax.textIsLong
    from attributeTextT ax
    where ax.id = attribute_id_;
    
    return new_id;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function template_Create
-- -----------------------------------------------------
DROP function IF EXISTS `template_Create`;

DELIMITER $$$
create function template_Create(
    type_id_ int unsigned, 
    type_code_ varchar(30) binary,
    node_id_ int unsigned
) returns int unsigned
comment 'template takes ownership on node_id_'
not deterministic modifies sql data
begin
    declare type_id int unsigned default coalesce(type_id_, nodeType_GetByCode(type_code_));
    
    -- FORWARD: nodeLink_ResetLinks2 is defined in _95_extraUtils
    insert templateT(srcNodeTypeId, dstNodeId, dstNodeTypeId)
    select type_id, nodeLink_ResetLinks2(null, null, node_id_, false), n.nodeTypeId
    from nodeT n
    where n.id = node_id_;
    
    return type_id;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function template_Delete
-- -----------------------------------------------------
DROP function IF EXISTS `template_Delete`;

DELIMITER $$$



create function template_Delete(
    type_id_ int unsigned, 
    type_code_ varchar(30) binary,
    node_id_ int unsigned
) returns int unsigned
not deterministic modifies sql data
begin
    declare type_id int unsigned default coalesce(type_id_, nodeType_GetByCode(type_code_));
    
    set @1 = nodeLink_UpdateStart();
    
    insert zzzDevNullTemplateT(n)
    select __node_Delete(t.dstNodeId)
    from templateT t
    where t.srcNodeTypeId = type_id
        and t.dstNodeId = coalesce(node_id_, t.dstNodeId);
    
    set @2 = nodeLink_UpdateEnd();

    -- deleted nodes will delete templateT record because of FK constraints
    /*delete from templateT
    where srcNodeTypeId = type_id
        and dstNodeId = coalesce(node_id_, dstNodeId);*/
    
    return 1;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function __template_DuplicateNode
-- -----------------------------------------------------
DROP function IF EXISTS `__template_DuplicateNode`;

DELIMITER $$$



create function __template_DuplicateNode(node_id_ int unsigned) returns int unsigned
not deterministic modifies sql data
begin
    declare res int unsigned;
    declare internal_type_id varchar(10) binary;
    
    select nt.internalTypeId 
    into internal_type_id
    from nodeT n 
    join nodeTypeT nt on n.nodeTypeId = nt.id 
    where n.id = node_id_;
    
    case internal_type_id
        when 'tag' then
            set res = __tag_Duplicate(node_id_);
        when 'attribute' then
            set res = __attribute_Duplicate(node_id_);
        else
            call raiseError1('template', 'duplicate', internal_type_id, 'cannot duplicate node of this type');
    end case;
    
    return res;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function template_CreateSubs
-- -----------------------------------------------------
DROP function IF EXISTS `template_CreateSubs`;

DELIMITER $$$



create function template_CreateSubs(node_id_ int unsigned) returns int unsigned
not deterministic modifies sql data
begin
    declare type_id int unsigned default (select n.nodeTypeId from nodeT n where n.id = node_id_);

    set @1 = nodeLink_UpdateStart();
    
    insert zzzDevNullTemplateT(n)
    select nodeLink_Relink(null, node_id_, __template_DuplicateNode(t.dstNodeId))
    from templateT t
    where t.srcNodeTypeId = type_id;

    set @2 = nodeLink_UpdateEnd();

    return node_id_;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function sorter_Set
-- -----------------------------------------------------
DROP function IF EXISTS `sorter_Set`;

DELIMITER $$$



-- automatic sorter numbering

create function sorter_Set(sorter_value_ int, sorter_increment_ int) returns int
not deterministic 
begin
    set @__sorterValue = coalesce(sorter_value_, @__sorterValue);
    set @__sorterIncrement = coalesce(sorter_increment_, @__sorterIncrement);
    return @__sorterValue;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function sorter_Get
-- -----------------------------------------------------
DROP function IF EXISTS `sorter_Get`;

DELIMITER $$$



create function sorter_Get() returns int
not deterministic
begin
    declare result int default @__sorterValue;
    set @__sorterValue = @__sorterValue + @__sorterIncrement;
    return result;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function sorter_GetIncrement
-- -----------------------------------------------------
DROP function IF EXISTS `sorter_GetIncrement`;

DELIMITER $$$



create function sorter_GetIncrement() returns int
deterministic
return @__sorterIncrement$$$

DELIMITER ;

-- -----------------------------------------------------
-- function __nodeType_DefaultSorter
-- -----------------------------------------------------
DROP function IF EXISTS `__nodeType_DefaultSorter`;

DELIMITER $$$



create function __nodeType_DefaultSorter() returns int
not deterministic
begin
    -- CONST: sorter step
    return coalesce(sorter_Get(), sorter_Set(coalesce((select max(sorter) from nodeTypeT), 0) + 10, 10));
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function __tag_DefaultSorter
-- -----------------------------------------------------
DROP function IF EXISTS `__tag_DefaultSorter`;

DELIMITER $$$



create function __tag_DefaultSorter() returns int
not deterministic
begin
    -- CONST: sorter step
    return coalesce(sorter_Get(), sorter_Set(coalesce((select max(sorter) from tagT), 0) + 10, 10));
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure __initScript_010Root
-- -----------------------------------------------------
DROP procedure IF EXISTS `__initScript_010Root`;

DELIMITER $$$
create procedure __initScript_010Root()
modifies sql data
begin
    set @1 = tag_CreateDef(null, 'rootgeo', null); -- all default values
    set @1 = tag_CreateDef(null, 'rootamenity', null);
    set @1 = tag_CreateDef(null, 'rootsubmission', null);
    set @1 = tag_CreateDef(null, 'rootuser', null);
    
    update nodeT n set
        n.isRoot = 'y'
    where n.forceSingleNode = 'y';
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure __initScript_015Templates
-- -----------------------------------------------------
DROP procedure IF EXISTS `__initScript_015Templates`;

DELIMITER $$$



create procedure __initScript_015Templates()
modifies sql data
begin
-- house numbers
    set @a1 = attribute_CreateText(
                                null,
                                'geoattribute',
                                'Номер будинку',
                                null,   -- sorter
                                null,   -- can_search
                                null,   -- display_in_object
                                null,   -- display_in_object_list
                                null,   -- factor
                                'y',    -- enabled
                                null,   -- code
                                'y',    -- required
                                'n',    -- sortable
                                '',     -- uofm_pre
                                '',     -- uofm_post
                                'n');   -- text_is_long
    set @1 = attribute_UpdateLocal(attribute_UpdateLocal(@a1, 'rus', 'Номер дома', null, null), 'eng', 'House Number', null, null);
    set @2 = template_Create(null, 'street', @a1);
    
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure __initScript_020Users
-- -----------------------------------------------------
DROP procedure IF EXISTS `__initScript_020Users`;

DELIMITER $$$



create procedure __initScript_020Users()
modifies sql data
begin
    set @1 = user_Create(
                    'Lazar Manager', '+380977907993', 'user@dev.adaptainc.com', 'lazar', '$2y$13$lNcXZwxiGH4nAXSpe6V63./22ZApYkLY48cFXlQnCTQzJLuPKiO82',
                    'user', 'y',
                    '1986-08-28',
                    'ukr',
                    null);
    set @1 = user_Create(
                    'Lazar Manager', null, 'manager@dev.adaptainc.com', 'lazar_manager', '$2y$13$lNcXZwxiGH4nAXSpe6V63./22ZApYkLY48cFXlQnCTQzJLuPKiO82',
                    'manager', 'y',
                    null,
                    'ukr',
                    null);
    set @1 = user_Create(
                    'Lazar Admin', null, 'admin@dev.adaptainc.com', 'lazar_admin', '$2y$13$lNcXZwxiGH4nAXSpe6V63./22ZApYkLY48cFXlQnCTQzJLuPKiO82',
                    'admin', 'y',
                    null,
                    'ukr',
                    null);
    set @u1 = user_Create(
                    'Борис Заїка', null, null, 'vkontakte_Borey', '$2y$13$gfU5gFaAQw0XOZ406XZ1TORd6UpOMHWh/sFflHQDrux1.le/TESDu',
                    'admin', 'y',
                    null,
                    null,
                    null);
    set @1 = user_SetOauth(@u1, 'vkontakte', 'http://vk.com/id14198259');
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure __initScript_030Cities
-- -----------------------------------------------------
DROP procedure IF EXISTS `__initScript_030Cities`;

DELIMITER $$$



create procedure __initScript_030Cities()
modifies sql data
begin
-- Ternopil
    set @city_id = tag_CreateDef(null, 'locality', 'Тернопіль');
    set @city_id = tag_UpdateLocal(tag_UpdateLocal(@city_id, 'rus', 'Тернополь'), 'eng', 'Ternopil');

    update externalDataSourceDatasetT set
        nodeId = @city_id
    where externalDataSourceId = 'osm'
        and code = 'ternopil';

-- Ternopil neighborhoods
    set @nbhd_id = nodeLink_Relink2(null, @city_id, tag_CreateDef(null, 'neighborhood', 'Центр'));
    set @nbhd_id = tag_UpdateLocal(tag_UpdateLocal(@nbhd_id, 'rus', 'Центр'), 'eng', 'Central');

    set @nbhd_id = nodeLink_Relink2(null, @city_id, tag_CreateDef(null, 'neighborhood', 'Оболоня (базар)'));
    set @nbhd_id = tag_UpdateLocal(tag_UpdateLocal(@nbhd_id, 'rus', 'Оболоня (базар)'), 'eng', 'Obolonya (marketplace)');

    set @nbhd_id = nodeLink_Relink2(null, @city_id, tag_CreateDef(null, 'neighborhood', 'Березовиця'));
    set @nbhd_id = tag_UpdateLocal(tag_UpdateLocal(@nbhd_id, 'rus', 'Березовица'), 'eng', 'Berezovytsya');

    set @nbhd_id = nodeLink_Relink2(null, @city_id, tag_CreateDef(null, 'neighborhood', 'Дружба'));
    set @nbhd_id = tag_UpdateLocal(tag_UpdateLocal(@nbhd_id, 'rus', 'Дружба'), 'eng', 'Druzhba');

    set @nbhd_id = nodeLink_Relink2(null, @city_id, tag_CreateDef(null, 'neighborhood', 'Кутківці'));
    set @nbhd_id = tag_UpdateLocal(tag_UpdateLocal(@nbhd_id, 'rus', 'Кутковцы'), 'eng', 'Kutkivtsi');

    set @nbhd_id = nodeLink_Relink2(null, @city_id, tag_CreateDef(null, 'neighborhood', 'Пронятин'));
    set @nbhd_id = tag_UpdateLocal(tag_UpdateLocal(@nbhd_id, 'rus', 'Пронятин'), 'eng', 'Pronyatyn');

    set @nbhd_id = nodeLink_Relink2(null, @city_id, tag_CreateDef(null, 'neighborhood', 'Новий Світ'));
    set @nbhd_id = tag_UpdateLocal(tag_UpdateLocal(@nbhd_id, 'rus', 'Новый Свет'), 'eng', 'Novy Svit');

    set @nbhd_id = nodeLink_Relink2(null, @city_id, tag_CreateDef(null, 'neighborhood', 'Промисловий'));
    set @nbhd_id = tag_UpdateLocal(tag_UpdateLocal(@nbhd_id, 'rus', 'Промышленный'), 'eng', 'Industrial');

    set @nbhd_id = nodeLink_Relink2(null, @city_id, tag_CreateDef(null, 'neighborhood', 'Північний'));
    set @nbhd_id = tag_UpdateLocal(tag_UpdateLocal(@nbhd_id, 'rus', 'Северный'), 'eng', 'Northern (Pivnichny)');

    set @nbhd_id = nodeLink_Relink2(null, @city_id, tag_CreateDef(null, 'neighborhood', 'Аляска'));
    set @nbhd_id = tag_UpdateLocal(tag_UpdateLocal(@nbhd_id, 'rus', 'Аляска'), 'eng', 'Alaska');

    set @nbhd_id = nodeLink_Relink2(null, @city_id, tag_CreateDef(null, 'neighborhood', 'Сонячний (БАМ)'));
    set @nbhd_id = tag_UpdateLocal(tag_UpdateLocal(@nbhd_id, 'rus', 'Солнечный (БАМ)'), 'eng', 'Sonyachny (BAM)');

    set @nbhd_id = nodeLink_Relink2(null, @city_id, tag_CreateDef(null, 'neighborhood', 'Канада'));
    set @nbhd_id = tag_UpdateLocal(tag_UpdateLocal(@nbhd_id, 'rus', 'Канада'), 'eng', 'Canada');

    set @nbhd_id = nodeLink_Relink2(null, @city_id, tag_CreateDef(null, 'neighborhood', 'Східний масив'));
    set @nbhd_id = tag_UpdateLocal(tag_UpdateLocal(@nbhd_id, 'rus', 'Восточный массив'), 'eng', 'Eastern neighborhood');

    set @nbhd_id = nodeLink_Relink2(null, @city_id, tag_CreateDef(null, 'neighborhood', 'Старий Парк'));
    set @nbhd_id = tag_UpdateLocal(tag_UpdateLocal(@nbhd_id, 'rus', 'Старый Парк'), 'eng', 'Old Park (Stary Park)');

-- Kyiv
    set @city_id = tag_CreateDef(null, 'locality', 'Київ');
    set @city_id = tag_UpdateLocal(tag_UpdateLocal(@city_id, 'rus', 'Киев'), 'eng', 'Kyiv');

    update externalDataSourceDatasetT set
        nodeId = @city_id
    where externalDataSourceId = 'osm'
        and code = 'kyiv';
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure __initScript_040Amenities
-- -----------------------------------------------------
DROP procedure IF EXISTS `__initScript_040Amenities`;

DELIMITER $$$



create procedure __initScript_040Amenities()
modifies sql data
begin
-- estate types
    set @estate_apt = tag_UpdateLocal(tag_UpdateLocal(tag_CreateDef(null, 'estatetype', 'Квартира'), 'rus', 'Квартира'), 'eng', 'Apartment');

    set @estate_cott = tag_UpdateLocal(tag_UpdateLocal(tag_CreateDef(null, 'estatetype', 'Дім'), 'rus', 'Дом'), 'eng', 'Cottage');

    set @estate_ofc = tag_UpdateLocal(tag_UpdateLocal(tag_CreateDef(null, 'estatetype', 'Офіс'), 'rus', 'Офис'), 'eng', 'Office');

    set @estate_com = tag_UpdateLocal(tag_UpdateLocal(tag_CreateDef(null, 'estatetype', 'Комерційна нерухомість'), 'rus', 'Коммерческая недвижимость'), 'eng', 'Commercial Estate');

    set @estate_gar = tag_UpdateLocal(tag_UpdateLocal(tag_CreateDef(null, 'estatetype', 'Гараж'), 'rus', 'Гараж'), 'eng', 'Garage');

-- submission types
    set @subm_buy = tag_UpdateLocal(tag_UpdateLocal(tag_CreateDef(null, 'submissiontype', 'Купити'), 'rus', 'Купить'), 'eng', 'Buy');

    set @subm_rent1 = tag_UpdateLocal(tag_UpdateLocal(tag_CreateDef(null, 'submissiontype', 'Оренда подобова'), 'rus', 'Аренда посуточная'), 'eng', 'Short-term Rent');

    set @subm_rentm = tag_UpdateLocal(tag_UpdateLocal(tag_CreateDef(null, 'submissiontype', 'Оренда довгострокова'), 'rus', 'Аренда долгосрочная'), 'eng', 'Rent (Long-term)');

-- wall materials
    set @u1 = tag_UpdateLocal(tag_UpdateLocal(tag_CreateDef(null, 'wallmaterial', 'Цегла'), 'rus', 'Кирпич'), 'eng', 'Brick');
    set @2 = nodeLink_Relink2(null, @estate_apt, @u1)
        and nodeLink_Relink2(null, @estate_cott, @u1)
        and nodeLink_Relink2(null, @estate_ofc, @u1)
        and nodeLink_Relink2(null, @estate_com, @u1)
        and nodeLink_Relink2(null, @estate_gar, @u1)
        and nodeLink_Relink2(null, @subm_buy, @u1);

    set @u1 = tag_UpdateLocal(tag_UpdateLocal(tag_CreateDef(null, 'wallmaterial', 'Силікатна цегла'), 'rus', 'Силикатный кирпич'), 'eng', 'Silicate Brick');
    set @2 = nodeLink_Relink2(null, @estate_apt, @u1)
        and nodeLink_Relink2(null, @estate_cott, @u1)
        and nodeLink_Relink2(null, @estate_ofc, @u1)
        and nodeLink_Relink2(null, @estate_com, @u1)
        and nodeLink_Relink2(null, @estate_gar, @u1)
        and nodeLink_Relink2(null, @subm_buy, @u1);

    set @u1 = tag_UpdateLocal(tag_UpdateLocal(tag_CreateDef(null, 'wallmaterial', 'Бетонна панель'), 'rus', 'Бетонная панель'), 'eng', 'Concrete Panel');
    set @2 = nodeLink_Relink2(null, @estate_apt, @u1)
        and nodeLink_Relink2(null, @estate_cott, @u1)
        and nodeLink_Relink2(null, @estate_ofc, @u1)
        and nodeLink_Relink2(null, @estate_com, @u1)
        and nodeLink_Relink2(null, @estate_gar, @u1)
        and nodeLink_Relink2(null, @subm_buy, @u1);

    set @u1 = tag_UpdateLocal(tag_UpdateLocal(tag_CreateDef(null, 'wallmaterial', 'Газобетонний блок'), 'rus', 'Газобетонный блок'), 'eng', 'Aerocrete Block');
    set @2 = nodeLink_Relink2(null, @estate_apt, @u1)
        and nodeLink_Relink2(null, @estate_cott, @u1)
        and nodeLink_Relink2(null, @estate_ofc, @u1)
        and nodeLink_Relink2(null, @estate_com, @u1)
        and nodeLink_Relink2(null, @estate_gar, @u1)
        and nodeLink_Relink2(null, @subm_buy, @u1);

    set @u1 = tag_UpdateLocal(tag_UpdateLocal(tag_CreateDef(null, 'wallmaterial', 'Сендвіч панель'), 'rus', 'Сендвич панель'), 'eng', 'Sandwich Panel');
    set @2 = nodeLink_Relink2(null, @estate_cott, @u1)
        and nodeLink_Relink2(null, @estate_com, @u1)
        and nodeLink_Relink2(null, @subm_buy, @u1);

-- roofing materials
    set @u1 = tag_UpdateLocal(tag_UpdateLocal(tag_CreateDef(null, 'roofmaterial', 'Металева'), 'rus', 'Металлическая'), 'eng', 'Metal');
    set @2 = nodeLink_Relink2(null, @estate_cott, @u1)
        and nodeLink_Relink2(null, @estate_com, @u1)
        and nodeLink_Relink2(null, @estate_gar, @u1)
        and nodeLink_Relink2(null, @subm_buy, @u1);

    set @u1 = tag_UpdateLocal(tag_UpdateLocal(tag_CreateDef(null, 'roofmaterial', 'Металочерепиця'), 'rus', 'Металочерепица'), 'eng', 'Metal Tiles');
    set @2 = nodeLink_Relink2(null, @estate_cott, @u1)
        and nodeLink_Relink2(null, @estate_com, @u1)
        and nodeLink_Relink2(null, @estate_gar, @u1)
        and nodeLink_Relink2(null, @subm_buy, @u1);

    set @u1 = tag_UpdateLocal(tag_UpdateLocal(tag_CreateDef(null, 'roofmaterial', 'Керамічна черепиця'), 'rus', 'Керамическая черепица'), 'eng', 'Ceramic Tiles');
    set @2 = nodeLink_Relink2(null, @estate_cott, @u1)
        and nodeLink_Relink2(null, @estate_com, @u1)
        and nodeLink_Relink2(null, @subm_buy, @u1);

    set @u1 = tag_UpdateLocal(tag_UpdateLocal(tag_CreateDef(null, 'roofmaterial', 'Руберойд'), 'rus', 'Рубероид'), 'eng', 'Tar Roofing');
    set @2 = nodeLink_Relink2(null, @estate_cott, @u1)
        and nodeLink_Relink2(null, @estate_com, @u1)
        and nodeLink_Relink2(null, @estate_gar, @u1)
        and nodeLink_Relink2(null, @subm_buy, @u1);

-- supplies
    set @u1 = tag_UpdateLocal(tag_UpdateLocal(tag_CreateDef(null, 'supply', 'Електрика'), 'rus', 'Электричество'), 'eng', 'Electricity');
    set @2 = nodeLink_Relink2(null, @estate_cott, @u1)
        and nodeLink_Relink2(null, @estate_com, @u1)
        and nodeLink_Relink2(null, @estate_gar, @u1)
        and nodeLink_Relink2(null, @subm_buy, @u1)
        and nodeLink_Relink2(null, @subm_rent1, @u1)
        and nodeLink_Relink2(null, @subm_rentm, @u1);

    set @u1 = tag_UpdateLocal(tag_UpdateLocal(tag_CreateDef(null, 'supply', 'Газ'), 'rus', 'Газ'), 'eng', 'Natural Gas');
    set @2 = nodeLink_Relink2(null, @estate_cott, @u1)
        and nodeLink_Relink2(null, @estate_com, @u1)
        and nodeLink_Relink2(null, @subm_buy, @u1)
        and nodeLink_Relink2(null, @subm_rent1, @u1)
        and nodeLink_Relink2(null, @subm_rentm, @u1);

    set @u1 = tag_UpdateLocal(tag_UpdateLocal(tag_CreateDef(null, 'supply', 'Водогін'), 'rus', 'Водопровод'), 'eng', 'Water Supply');
    set @2 = nodeLink_Relink2(null, @estate_cott, @u1)
        and nodeLink_Relink2(null, @estate_com, @u1)
        and nodeLink_Relink2(null, @subm_buy, @u1)
        and nodeLink_Relink2(null, @subm_rent1, @u1)
        and nodeLink_Relink2(null, @subm_rentm, @u1);

    set @u1 = tag_UpdateLocal(tag_UpdateLocal(tag_CreateDef(null, 'supply', 'Каналізація'), 'rus', 'Канализация'), 'eng', 'Sewer');
    set @2 = nodeLink_Relink2(null, @estate_cott, @u1)
        and nodeLink_Relink2(null, @estate_com, @u1)
        and nodeLink_Relink2(null, @subm_buy, @u1)
        and nodeLink_Relink2(null, @subm_rent1, @u1)
        and nodeLink_Relink2(null, @subm_rentm, @u1);

-- amenities
    set @u1 = tag_UpdateLocal(tag_UpdateLocal(tag_CreateDef(null, 'amenity', 'Меблі'), 'rus', 'Мебель'), 'eng', 'Furniture');
    set @2 = nodeLink_Relink2(null, @estate_apt, @u1)
        and nodeLink_Relink2(null, @estate_cott, @u1)
        and nodeLink_Relink2(null, @estate_ofc, @u1)
        and nodeLink_Relink2(null, @estate_com, @u1)
        and nodeLink_Relink2(null, @subm_rent1, @u1)
        and nodeLink_Relink2(null, @subm_rentm, @u1);

    set @u1 = tag_UpdateLocal(tag_UpdateLocal(tag_CreateDef(null, 'amenity', 'Холодильник'), 'rus', 'Холодильник'), 'eng', 'Refrigerator');
    set @2 = nodeLink_Relink2(null, @estate_apt, @u1)
        and nodeLink_Relink2(null, @estate_cott, @u1)
        and nodeLink_Relink2(null, @estate_ofc, @u1)
        and nodeLink_Relink2(null, @estate_com, @u1)
        and nodeLink_Relink2(null, @subm_rent1, @u1)
        and nodeLink_Relink2(null, @subm_rentm, @u1);

    set @u1 = tag_UpdateLocal(tag_UpdateLocal(tag_CreateDef(null, 'amenity', 'Мікрохвильова піч'), 'rus', 'Микроволновая печь'), 'eng', 'Microwave Oven');
    set @2 = nodeLink_Relink2(null, @estate_apt, @u1)
        and nodeLink_Relink2(null, @estate_cott, @u1)
        and nodeLink_Relink2(null, @estate_ofc, @u1)
        and nodeLink_Relink2(null, @estate_com, @u1)
        and nodeLink_Relink2(null, @subm_rent1, @u1)
        and nodeLink_Relink2(null, @subm_rentm, @u1);

    set @u1 = tag_UpdateLocal(tag_UpdateLocal(tag_CreateDef(null, 'amenity', 'Індивідуальне опалення'), 'rus', 'Индивидуальное отопление'), 'eng', 'Home heating');
    set @2 = nodeLink_Relink2(null, @estate_apt, @u1)
        and nodeLink_Relink2(null, @estate_cott, @u1)
        and nodeLink_Relink2(null, @estate_ofc, @u1)
        and nodeLink_Relink2(null, @estate_com, @u1)
        and nodeLink_Relink2(null, @subm_buy, @u1)
        and nodeLink_Relink2(null, @subm_rent1, @u1)
        and nodeLink_Relink2(null, @subm_rentm, @u1);

    set @u1 = tag_UpdateLocal(tag_UpdateLocal(tag_CreateDef(null, 'amenity', 'Інтернет/Wi-Fi'), 'rus', 'Интернет/Wi-Fi'), 'eng', 'Internet/Wi-Fi');
    set @2 = nodeLink_Relink2(null, @estate_apt, @u1)
        and nodeLink_Relink2(null, @estate_cott, @u1)
        and nodeLink_Relink2(null, @estate_ofc, @u1)
        and nodeLink_Relink2(null, @estate_com, @u1)
        and nodeLink_Relink2(null, @subm_buy, @u1)
        and nodeLink_Relink2(null, @subm_rent1, @u1)
        and nodeLink_Relink2(null, @subm_rentm, @u1);

    set @u1 = tag_UpdateLocal(tag_UpdateLocal(tag_CreateDef(null, 'amenity', 'Телефон (міський)'), 'rus', 'Телефон (городской)'), 'eng', 'Telephone (wired)');
    set @2 = nodeLink_Relink2(null, @estate_apt, @u1)
        and nodeLink_Relink2(null, @estate_cott, @u1)
        and nodeLink_Relink2(null, @estate_ofc, @u1)
        and nodeLink_Relink2(null, @estate_com, @u1)
        and nodeLink_Relink2(null, @subm_buy, @u1)
        and nodeLink_Relink2(null, @subm_rent1, @u1)
        and nodeLink_Relink2(null, @subm_rentm, @u1);

    set @u1 = tag_UpdateLocal(tag_UpdateLocal(tag_CreateDef(null, 'amenity', 'Сантехніка'), 'rus', 'Сантехника'), 'eng', 'Plumbing');
    set @2 = nodeLink_Relink2(null, @estate_apt, @u1)
        and nodeLink_Relink2(null, @estate_cott, @u1)
        and nodeLink_Relink2(null, @estate_ofc, @u1)
        and nodeLink_Relink2(null, @estate_com, @u1)
        and nodeLink_Relink2(null, @subm_buy, @u1)
        and nodeLink_Relink2(null, @subm_rent1, @u1)
        and nodeLink_Relink2(null, @subm_rentm, @u1);

    set @u1 = tag_UpdateLocal(tag_UpdateLocal(tag_CreateDef(null, 'amenity', 'Євроремонт'), 'rus', 'Евроремонт'), 'eng', 'Remodeled/Renovated');
    set @2 = nodeLink_Relink2(null, @estate_apt, @u1)
        and nodeLink_Relink2(null, @estate_cott, @u1)
        and nodeLink_Relink2(null, @estate_ofc, @u1)
        and nodeLink_Relink2(null, @estate_com, @u1)
        and nodeLink_Relink2(null, @subm_buy, @u1)
        and nodeLink_Relink2(null, @subm_rent1, @u1)
        and nodeLink_Relink2(null, @subm_rentm, @u1);

    set @u1 = tag_UpdateLocal(tag_UpdateLocal(tag_CreateDef(null, 'amenity', 'Оглядова яма'), 'rus', 'Смотровая яма'), 'eng', 'Inspection Pit');
    set @2 = nodeLink_Relink2(null, @estate_gar, @u1)
        and nodeLink_Relink2(null, @subm_buy, @u1)
        and nodeLink_Relink2(null, @subm_rent1, @u1)
        and nodeLink_Relink2(null, @subm_rentm, @u1);

--

    set @u1 = tag_UpdateLocal(tag_UpdateLocal(tag_CreateDef(null, 'extamenity', 'Конс''єрж'), 'rus', 'Консьерж'), 'eng', 'Concierge');
    set @2 = nodeLink_Relink2(null, @estate_apt, @u1)
        and nodeLink_Relink2(null, @estate_ofc, @u1)
        and nodeLink_Relink2(null, @estate_com, @u1)
        and nodeLink_Relink2(null, @subm_buy, @u1)
        and nodeLink_Relink2(null, @subm_rent1, @u1)
        and nodeLink_Relink2(null, @subm_rentm, @u1);

    set @u1 = tag_UpdateLocal(tag_UpdateLocal(tag_CreateDef(null, 'extamenity', 'Ліфт'), 'rus', 'Лифт'), 'eng', 'Elevator');
    set @2 = nodeLink_Relink2(null, @estate_apt, @u1)
        and nodeLink_Relink2(null, @estate_ofc, @u1)
        and nodeLink_Relink2(null, @estate_com, @u1)
        and nodeLink_Relink2(null, @subm_buy, @u1)
        and nodeLink_Relink2(null, @subm_rent1, @u1)
        and nodeLink_Relink2(null, @subm_rentm, @u1);

    set @u1 = tag_UpdateLocal(tag_UpdateLocal(tag_CreateDef(null, 'extamenity', 'Парковка'), 'rus', 'Парковка'), 'eng', 'Parking Lot');
    set @2 = nodeLink_Relink2(null, @estate_apt, @u1)
        and nodeLink_Relink2(null, @estate_ofc, @u1)
        and nodeLink_Relink2(null, @estate_com, @u1)
        and nodeLink_Relink2(null, @subm_buy, @u1)
        and nodeLink_Relink2(null, @subm_rent1, @u1)
        and nodeLink_Relink2(null, @subm_rentm, @u1);

    set @u1 = tag_UpdateLocal(tag_UpdateLocal(tag_CreateDef(null, 'extamenity', 'Дитячий майданчик'), 'rus', 'Детская площадка'), 'eng', 'Playground');
    set @2 = nodeLink_Relink2(null, @estate_apt, @u1)
        and nodeLink_Relink2(null, @subm_buy, @u1)
        and nodeLink_Relink2(null, @subm_rentm, @u1);


end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function __node_Duplicate
-- -----------------------------------------------------
DROP function IF EXISTS `__node_Duplicate`;

DELIMITER $$$



create function __node_Duplicate(node_id_ int unsigned) returns int unsigned
not deterministic modifies sql data
begin
    declare new_id, type_id int unsigned;
    declare display_node enum('n', 'y');
    declare name varchar(50);
    
    select n.nodeTypeId, n.name, n.displayNode
    into type_id, name, display_node
    from nodeT n
    where n.id = node_id_;

    set new_id = __node_Create(type_id, null, name);
    
    update nodeT n set
        n.displayNode = display_node
    where n.id = new_id;
    
    return new_id;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function __image_DefaultSorter
-- -----------------------------------------------------
DROP function IF EXISTS `__image_DefaultSorter`;

DELIMITER $$$
-- object and user specific image code is in _08_object

create function __image_DefaultSorter() returns int
not deterministic
begin
    -- CONST: sorter step
    return coalesce(sorter_Get(), sorter_Set(coalesce((select max(sorter) from imageT), 0) + 10, 10));
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function image_Create
-- -----------------------------------------------------
DROP function IF EXISTS `image_Create`;

DELIMITER $$$



create function image_Create(
    node_id_ int unsigned,
    name_ varchar(255),
    filename_original_ varchar(255),
    filename_fs_ varchar(255),
    display_ enum('n', 'y'),
    sorter_ int
) returns int unsigned
not deterministic modifies sql data
begin
    set @1 = node_id_ is not null and __node_CanUpdate(node_id_, 'imagecreate');
    return __image_Create(node_id_, name_, filename_original_, filename_fs_, display_, sorter_);
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function __image_Delete
-- -----------------------------------------------------
DROP function IF EXISTS `__image_Delete`;

DELIMITER $$$



create function __image_Delete(image_id_ int unsigned, node_id_ int unsigned) returns int unsigned
not deterministic modifies sql data
begin
    delete from imageT where id = image_id_;

    set @1 = (row_count() > 0) and node_id_ is not null and __node_Update(node_id_, null);
    
    return 1;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function image_Delete
-- -----------------------------------------------------
DROP function IF EXISTS `image_Delete`;

DELIMITER $$$



create function image_Delete(image_id_ int unsigned) returns int unsigned
not deterministic modifies sql data
begin
    declare node_id int unsigned default (select nodeId from imageT where id = image_id_);
    
    set @1 = node_id is not null and __node_CanUpdate(node_id, 'imagedelete');
    return __image_Delete(image_id_, node_id);
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function __image_Update
-- -----------------------------------------------------
DROP function IF EXISTS `__image_Update`;

DELIMITER $$$



create function __image_Update(
    image_id_ int unsigned,
    node_id_ int unsigned,
    name_ varchar(255),
    filename_original_ varchar(255),
    filename_fs_ varchar(255),
    display_ enum('n', 'y'),
    sorter_ int,
    image_type_id_ char(10) binary,
    video_type_ varchar(50),
    url_ varchar(255),
    extra_id_ varchar(255)
) returns int unsigned
not deterministic modifies sql data
begin
    set @1 = node_id_ is not null and __node_Update(node_id_, null);
    
    update imageT set
        name = coalesce(name_, name),
        filenameOriginal = coalesce(filename_original_, filenameOriginal),
        filenameFs = coalesce(filename_fs_, filenameFs),
        display = coalesce(display_, display),
        sorter = coalesce(sorter_, sorter),
        imageTypeId = coalesce(image_type_id_, imageTypeId),
        videoType = coalesce(video_type_, videoType),
        url = coalesce(url_, url),
        extraId = coalesce(extra_id_, extraId),
        changed = current_timestamp(),
        changedUserId = user_CurrentId(),
        changedUserName = user_CurrentName(),
        changedIp = user_CurrentIp()
    where id = image_id_;
    
    return image_id_;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function image_Update
-- -----------------------------------------------------
DROP function IF EXISTS `image_Update`;

DELIMITER $$$



create function image_Update(
    image_id_ int unsigned,
    name_ varchar(255),
    filename_original_ varchar(255),
    filename_fs_ varchar(255),
    display_ enum('n', 'y'),
    sorter_ int
) returns int unsigned
not deterministic modifies sql data
begin
    declare node_id int unsigned default (select nodeId from imageT where id = image_id_);
    
    set @1 = node_id is not null and __node_CanUpdate(node_id, 'imageupdate');
    return __image_Update(image_id_, node_id, name_, filename_original_, filename_fs_, display_, sorter_, null, null, null, null);
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function image_CreateByUserForObject
-- -----------------------------------------------------
DROP function IF EXISTS `image_CreateByUserForObject`;

DELIMITER $$$



-- object specific image code

create function image_CreateByUserForObject(
    node_id_ int unsigned,
    name_ varchar(255),
    filename_original_ varchar(255),
    filename_fs_ varchar(255),
    display_ enum('n', 'y'),
    sorter_ int
) returns int unsigned
not deterministic modifies sql data
begin
    set @1 = __object_AccessByUser(node_id_, 'update', null);
    return __image_Create(node_id_, name_, filename_original_, filename_fs_, display_, sorter_);
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function image_DeleteByUserForObject
-- -----------------------------------------------------
DROP function IF EXISTS `image_DeleteByUserForObject`;

DELIMITER $$$



create function image_DeleteByUserForObject(image_id_ int unsigned) returns int unsigned
not deterministic modifies sql data
begin
    declare node_id int unsigned default (select nodeId from imageT where id = image_id_);
    
    set @1 = __object_AccessByUser(node_id, 'update', null);
    return __image_Delete(image_id_, node_id);
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function image_UpdateByUserForObject
-- -----------------------------------------------------
DROP function IF EXISTS `image_UpdateByUserForObject`;

DELIMITER $$$



create function image_UpdateByUserForObject(
    image_id_ int unsigned,
    name_ varchar(255),
    filename_original_ varchar(255),
    filename_fs_ varchar(255),
    display_ enum('n', 'y'),
    sorter_ int
) returns int unsigned
not deterministic modifies sql data
begin
    declare node_id int unsigned default (select nodeId from imageT where id = image_id_);
    
    set @1 = __object_AccessByUser(node_id, 'update', null);
    return __image_Update(image_id_, node_id, name_, filename_original_, filename_fs_, display_, sorter_, null, null, null, null);
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function __tag_CalcDefaults
-- -----------------------------------------------------
DROP function IF EXISTS `__tag_CalcDefaults`;

DELIMITER $$$



create function __tag_CalcDefaults(
    type_field_ enum('n', 'y'),         -- not nullable
    type_display_node_ enum('n', 'y'),  -- not nullable
    field_raw_ enum('n', 'y'),          -- nullable
    enabled_ enum('n', 'y')             -- not nullable (hopefully)
) returns enum('n', 'y')
deterministic
begin
    return if(enabled_ = 'y' and type_display_node_ = 'y', coalesce(field_raw_, type_field_, 'n'), 'n');
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function __tag_UpdateDefaults
-- -----------------------------------------------------
DROP function IF EXISTS `__tag_UpdateDefaults`;

DELIMITER $$$



create function __tag_UpdateDefaults(tag_id_ int unsigned, type_id_ int unsigned) returns int unsigned
not deterministic modifies sql data
begin
    if tag_id_ is null and type_id_ is null then
        return 1;
    end if;
    
    update tagT t join nodeT n on t.id = n.id join nodeTypeT nt on n.nodeTypeId = nt.id set
        t.canSearch = __tag_CalcDefaults(nt.canSearch, n.displayNode, t.canSearchRaw, t.enabled),
        t.displayInObject = __tag_CalcDefaults(nt.displayInObject, n.displayNode, t.displayInObjectRaw, t.enabled),
        t.displayInObjectList = __tag_CalcDefaults(nt.displayInObjectList, n.displayNode, t.displayInObjectListRaw, t.enabled)
    where t.id = coalesce(tag_id_, t.id)
        and nt.id = coalesce(type_id_, nt.id);

    return coalesce(tag_id_, 1);
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function __tag_ClampFactor
-- -----------------------------------------------------
DROP function IF EXISTS `__tag_ClampFactor`;

DELIMITER $$$



create function __tag_ClampFactor(factor_ double) returns double
deterministic
-- CONST: tag factor min/max limits
return greatest(0.1, least(1.0, factor_))$$$

DELIMITER ;

-- -----------------------------------------------------
-- function __coord_Update
-- -----------------------------------------------------
DROP function IF EXISTS `__coord_Update`;

DELIMITER $$$



-- coordT code

create function __coord_Update(
    node_id_ int unsigned, 
    latitude_ decimal(12, 7), 
    longitude_ decimal(12, 7)
) returns int unsigned
not deterministic modifies sql data
begin
    insert coordT(nodeId, latitude, longitude)
    select node_id_, latitude_, longitude_
    from dual
    where latitude_ is not null and longitude_ is not null
    on duplicate key update
        coordT.latitude = coalesce(latitude_, coordT.latitude),
        coordT.longitude = coalesce(longitude_, coordT.longitude);
    
    return node_id_;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function coord_Update
-- -----------------------------------------------------
DROP function IF EXISTS `coord_Update`;

DELIMITER $$$



create function coord_Update(
    node_id_ int unsigned, 
    latitude_ decimal(12, 7), 
    longitude_ decimal(12, 7)
) returns int unsigned
not deterministic modifies sql data
begin
    set @1 = __node_Update(node_id_, null);
    return __coord_Update(node_id_, latitude_, longitude_);
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function __image_Create
-- -----------------------------------------------------
DROP function IF EXISTS `__image_Create`;

DELIMITER $$$



create function __image_Create(
    node_id_ int unsigned,
    name_ varchar(255),
    filename_original_ varchar(255),
    filename_fs_ varchar(255),
    display_ enum('n', 'y'),
    sorter_ int
) returns int unsigned
not deterministic modifies sql data
begin
    declare image_id int unsigned default(select i.id from imageT i where i.nodeId = node_id_ and i.forceSinglePhoto = 'y');
    
    -- if the node_id has the image and the node_id can have only 1 image - detach the current image from the node
    set @1 = image_id is not null and __image_UpdateNodeId(image_id, node_id_, null);
    
    -- FORWARD: user_CurrentId, user_CurrentName and user_CurrentIp are defined in _060_user
    insert imageT(
        nodeId,
        forceSinglePhoto,
        name,
        filenameOriginal,
        filenameFs,
        display,
        sorter,
        imageTypeId,
        addedUserId,
        addedUserName,
        addedIp,
        changedUserId,
        changedUserName,
        changedIp)
    values (
        node_id_,
        (select n.forceSinglePhoto from nodeT n where n.id = node_id_),
        name_,
        filename_original_,
        filename_fs_,
        display_,
        coalesce(sorter_, __image_DefaultSorter()),
        (select it.id from imageTypeT it where it.isDefault = 'y'),
        user_CurrentId(),
        user_CurrentName(),
        user_CurrentIp(),
        user_CurrentId(),
        user_CurrentName(),
        user_CurrentIp());
    
    set image_id = last_insert_id();

    set @2 = node_id_ is not null and __node_Update(node_id_, null);
    
    return image_id;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function __image_UpdateNodeId
-- -----------------------------------------------------
DROP function IF EXISTS `__image_UpdateNodeId`;

DELIMITER $$$



create function __image_UpdateNodeId(
    image_id_ int unsigned, 
    previous_node_id_ int unsigned, 
    node_id_ int unsigned
) returns int unsigned
not deterministic modifies sql data
begin
    set @1 = previous_node_id_ is not null and __node_Update(previous_node_id_, null);
    set @2 = node_id_ is not null and __node_Update(node_id_, null);
    
    update imageT set
        nodeId = node_id_,
        forceSinglePhoto = (select n.forceSinglePhoto from nodeT n where n.id = node_id_),
        changed = current_timestamp(),
        changedUserId = user_CurrentId(),
        changedUserName = user_CurrentName(),
        changedIp = user_CurrentIp()
    where id = image_id_;

    return image_id_;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function image_UpdateNodeId
-- -----------------------------------------------------
DROP function IF EXISTS `image_UpdateNodeId`;

DELIMITER $$$



create function image_UpdateNodeId(
    image_id_ int unsigned, 
    node_id_ int unsigned
) returns int unsigned
not deterministic modifies sql data
begin
    declare previous_node_id int unsigned default(select i.nodeId from imageT i where i.id = image_id_);
    
    set @1 = previous_node_id is not null and __node_CanUpdate(previous_node_id, 'imageupdate');
    set @2 = node_id_ is not null and __node_CanUpdate(node_id_, 'imageupdate');
    
    return __image_UpdateNodeId(image_id_, previous_node_id, node_id_);
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function __tag_ClampRatingPoints
-- -----------------------------------------------------
DROP function IF EXISTS `__tag_ClampRatingPoints`;

DELIMITER $$$



create function __tag_ClampRatingPoints(rating_points_ int unsigned) returns int unsigned
deterministic
-- CONST: rating points min limit
return greatest(0, rating_points_)$$$

DELIMITER ;

-- -----------------------------------------------------
-- function __object_ClampRating
-- -----------------------------------------------------
DROP function IF EXISTS `__object_ClampRating`;

DELIMITER $$$



-- rating maintenance

create function __object_ClampRating(rating_ double) returns double
deterministic
-- CONST: object rating min/max limits
return greatest(0.0, least(1.0, rating_))$$$

DELIMITER ;

-- -----------------------------------------------------
-- function __object_UpdateRating
-- -----------------------------------------------------
DROP function IF EXISTS `__object_UpdateRating`;

DELIMITER $$$



create function __object_UpdateRating(
    object_id_ int unsigned, 
    selected_ int unsigned, 
    total_ int unsigned
) returns int unsigned
not deterministic modifies sql data
begin
    update objectT o set
        o.rating = __object_UpdateSortCacheRating2(
                                            o.id, 
                                            coalesce(__object_ClampRating(selected_ / greatest(total_, 1)), 0))
    where o.id = object_id_;

    return object_id_;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function __object_UpdateRatings
-- -----------------------------------------------------
DROP function IF EXISTS `__object_UpdateRatings`;

DELIMITER $$$



create function __object_UpdateRatings() returns int unsigned
not deterministic modifies sql data
begin
    drop temporary table if exists objectUpdateRatingsData;
    create temporary table objectUpdateRatingsData(
        objectId int unsigned not null primary key,
        selectedPoints int not null,
        totalPoints int not null
    ) engine=MEMORY;
    
    insert objectUpdateRatingsData(objectId, selectedPoints, totalPoints)
    select 
        o.id,
        sum(if(nrl.linked = 1, srctag.ratingPoints, 0)),
        sum(srctag.ratingPoints)
    from objectT o
    join zzzNodeWorksetV workset on o.id = workset.nodeId
    join _nodeRecommendLink4V nrl on o.id = nrl.dstNodeId
    join tagT srctag on nrl.srcNodeId = srctag.id
    join nodeT srcnode on srctag.id = srcnode.id
    join nodeTypeT nodetype on srcnode.nodeTypeId = nodetype.id
    where workset.isMain = 1
        and nodetype.canHaveRatingPoints = 'y'
    group by o.id;
    
    insert zzzDevNullObjectT(n)
    select __object_UpdateRating(objectId, selectedPoints, totalPoints)
    from objectUpdateRatingsData;

    drop temporary table objectUpdateRatingsData;

    return 1;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function __object_UpdateAllRatings
-- -----------------------------------------------------
DROP function IF EXISTS `__object_UpdateAllRatings`;

DELIMITER $$$



create function __object_UpdateAllRatings() returns int unsigned
not deterministic modifies sql data
comment 'updates all object ratings; to be called manually'
begin
    drop temporary table if exists objectUpdateAllRatingsData;
    create temporary table objectUpdateAllRatingsData(
        objectId int unsigned not null primary key,
        selectedPoints int not null,
        totalPoints int not null
    ) engine=MEMORY;
    
    insert objectUpdateAllRatingsData(objectId, selectedPoints, totalPoints)
    select 
        o.id,
        sum(if(nrl.linked = 1, srctag.ratingPoints, 0)),
        sum(srctag.ratingPoints)
    from objectT o
    join _nodeRecommendLink4V nrl on o.id = nrl.dstNodeId
    join tagT srctag on nrl.srcNodeId = srctag.id
    join nodeT srcnode on srctag.id = srcnode.id
    join nodeTypeT nodetype on srcnode.nodeTypeId = nodetype.id
    where nodetype.canHaveRatingPoints = 'y'
    group by o.id;
    
    insert zzzDevNullObjectT(n)
    select __object_UpdateRating(objectId, selectedPoints, totalPoints)
    from objectUpdateAllRatingsData;

    drop temporary table objectUpdateAllRatingsData;

    return 1;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function __search_RectifyCriteriaPost
-- -----------------------------------------------------
DROP function IF EXISTS `__search_RectifyCriteriaPost`;

DELIMITER $$$



create function __search_RectifyCriteriaPost(session_id_ bigint unsigned) returns int unsigned
not deterministic modifies sql data
begin
    delete from searchCriterionT
    using searchCriterionT
    join tagT tag on searchCriterionT.nodeId = tag.id
    left join searchResultT sr on searchCriterionT.nodeId = sr.nodeId and searchCriterionT.sessionId = sr.sessionId
    where searchCriterionT.sessionId = session_id_
        and sr.nodeId is null;

    return 1;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function __object_UpdateAddresses
-- -----------------------------------------------------
DROP function IF EXISTS `__object_UpdateAddresses`;

DELIMITER $$$



-- address and alias maintenance

create function __object_UpdateAddresses() returns int unsigned
not deterministic modifies sql data
comment 'updates both address and alias'
begin
    drop temporary table if exists objectUpdateAddressesData;
    create temporary table objectUpdateAddressesData(
        objectId int unsigned not null primary key,
        address text null,
        alias text null
    ) engine=MyISAM;
    
    -- CONST: address field separator
    insert objectUpdateAddressesData(objectId, address)
    select 
        o.id,
        group_concat(
            if(
                nad.srcInternalTypeId = 'attribute',
                coalesce(
                    nad.srcNumericValue,
                    date_format(nad.srcDatetimeValue, nad.srcDatetimeOutputFormat), 
                    nad.srcTextValue, 
                    ''), 
                nad.srcName)
            order by nad.ancestrySorter, nad.srcTypeName, nad.srcName
            separator ', ') addr
    from objectT o
    join zzzNodeWorksetV nws on o.id = nws.nodeId
    left join nodeAncestryDataV nad on o.id = nad.dstNodeId and nad.languageIsDefault = 'y'
    where nws.isMain = 1
    group by o.id;

    -- CONST: address field separator and language id
    insert objectUpdateAddressesData(objectId, alias)
    select 
        o.id,
        concat_ws(
            '-', 
            o.id,
            group_concat(
                if(
                    nad.srcInternalTypeId = 'attribute',
                    coalesce(
                        nad.srcNumericValue,
                        date_format(nad.srcDatetimeValue, nad.srcDatetimeOutputFormat), 
                        nad.srcTextValue, 
                        ''), 
                    nad.srcName)
                order by nad.ancestrySorter, nad.srcTypeName, nad.srcName
                separator '-')) alias_
    from objectT o
    join zzzNodeWorksetV nws on o.id = nws.nodeId
    left join nodeAncestryDataV nad on o.id = nad.dstNodeId and nad.languageId = 'eng'
    where nws.isMain = 1
    group by o.id
    on duplicate key update 
        objectUpdateAddressesData.alias = values(alias);
    
    update objectT o join objectUpdateAddressesData ouad on o.id = ouad.objectId set
        o.address = coalesce(ouad.address, ''),
        o.alias = string_Alphanumeric(coalesce(ouad.alias, ''));

    drop temporary table objectUpdateAddressesData;

    return 1;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function __object_UpdateAllAddresses
-- -----------------------------------------------------
DROP function IF EXISTS `__object_UpdateAllAddresses`;

DELIMITER $$$



create function __object_UpdateAllAddresses() returns int unsigned
not deterministic modifies sql data
comment 'updates all object addresses and aliases; to be called manually'
begin
    drop temporary table if exists objectUpdateAllAddressesData;
    create temporary table objectUpdateAllAddressesData(
        objectId int unsigned not null primary key,
        address text null,
        alias text null
    ) engine=MyISAM;
    
    -- CONST: address field separator
    insert objectUpdateAllAddressesData(objectId, address)
    select 
        o.id,
        group_concat(
            if(
                nad.srcInternalTypeId = 'attribute',
                coalesce(
                    nad.srcNumericValue,
                    date_format(nad.srcDatetimeValue, nad.srcDatetimeOutputFormat), 
                    nad.srcTextValue, 
                    ''), 
                nad.srcName)
            order by nad.ancestrySorter, nad.srcTypeName, nad.srcName
            separator ', ') addr
    from objectT o 
    left join nodeAncestryDataV nad on o.id = nad.dstNodeId and nad.languageIsDefault = 'y'
    group by o.id;
    
    -- CONST: address field separator and language id
    insert objectUpdateAllAddressesData(objectId, alias)
    select 
        o.id,
        concat_ws(
            '-', 
            o.id,
            group_concat(
                if(
                    nad.srcInternalTypeId = 'attribute',
                    coalesce(
                        nad.srcNumericValue,
                        date_format(nad.srcDatetimeValue, nad.srcDatetimeOutputFormat), 
                        nad.srcTextValue, 
                        ''), 
                    nad.srcName)
                order by nad.ancestrySorter, nad.srcTypeName, nad.srcName
                separator '-')) alias_
    from objectT o
    left join nodeAncestryDataV nad on o.id = nad.dstNodeId and nad.languageId = 'eng'
    group by o.id
    on duplicate key update 
        objectUpdateAllAddressesData.alias = values(alias);

    update objectT o join objectUpdateAllAddressesData ouaad on o.id = ouaad.objectId set
        o.address = coalesce(ouaad.address, ''),
        o.alias = string_Alphanumeric(coalesce(ouaad.alias, ''));

    drop temporary table objectUpdateAllAddressesData;

    return 1;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure __initScript_050HiddenAttributes
-- -----------------------------------------------------
DROP procedure IF EXISTS `__initScript_050HiddenAttributes`;

DELIMITER $$$



create procedure __initScript_050HiddenAttributes()
modifies sql data
begin
-- by rating sort
    set @1 = attribute_CreateNumeric(
                                    null,
                                    'hiddenattribute',
                                    'Рейтинг',
                                    -100000001,     -- sorter
                                    null,           -- can_search
                                    null,           -- display_in_object
                                    null,           -- display_in_object_list
                                    0,              -- factor
                                    'y',            -- enabled
                                    '__byrating',   -- code
                                    'n',            -- required
                                    'y',            -- sortable
                                    '',             -- uofm_pre
                                    '',             -- uofm_post
                                    0,              -- decimal_min
                                    0,              -- decimal_max
                                    0);             -- decimal_round
    set @2 = attribute_UpdateLocal(attribute_UpdateLocal(@1, 'rus', 'Рейтинг', null, null), 'eng', 'Rating', null, null);

-- by changed sort
    set @1 = attribute_CreateNumeric(
                                    null,
                                    'hiddenattribute',
                                    'Дата оновлення',
                                    -100000000,     -- sorter
                                    null,           -- can_search
                                    null,           -- display_in_object
                                    null,           -- display_in_object_list
                                    0,              -- factor
                                    'y',            -- enabled
                                    '__bychanged',  -- code
                                    'n',            -- required
                                    'y',            -- sortable
                                    '',             -- uofm_pre
                                    '',             -- uofm_post
                                    0,              -- decimal_min
                                    0,              -- decimal_max
                                    0);             -- decimal_round
    set @2 = attribute_UpdateLocal(attribute_UpdateLocal(@1, 'rus', 'Дата обновления', null, null), 'eng', 'Date', null, null);

end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function string_Alphanumeric
-- -----------------------------------------------------
DROP function IF EXISTS `string_Alphanumeric`;

DELIMITER $$$



create function string_Alphanumeric(s varchar(2048)) returns varchar(2048)
deterministic
return __string_QuoteOut(s, 'alias', '-')$$$

DELIMITER ;

-- -----------------------------------------------------
-- function datetime_ToDouble
-- -----------------------------------------------------
DROP function IF EXISTS `datetime_ToDouble`;

DELIMITER $$$



-- datetime manipulation

create function datetime_ToDouble(t datetime) returns double
deterministic
return to_days(t) + (time_to_sec(t) + 0.0) / 86400.0$$$

DELIMITER ;

-- -----------------------------------------------------
-- function __coord_Delete
-- -----------------------------------------------------
DROP function IF EXISTS `__coord_Delete`;

DELIMITER $$$



create function __coord_Delete(
    node_id_ int unsigned
) returns int unsigned
not deterministic modifies sql data
begin
    delete from coordT where nodeId = node_id_;
    
    return node_id_;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function coord_Delete
-- -----------------------------------------------------
DROP function IF EXISTS `coord_Delete`;

DELIMITER $$$



create function coord_Delete(
    node_id_ int unsigned
) returns int unsigned
not deterministic modifies sql data
begin
    set @1 = __node_Update(node_id_, null);
    return __coord_Delete(node_id_);
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function __object_UpdateSortCache
-- -----------------------------------------------------
DROP function IF EXISTS `__object_UpdateSortCache`;

DELIMITER $$$



-- sort cache maintenance

create function __object_UpdateSortCache(
    object_id_ int unsigned,
    attribute_id_ int unsigned,
    value_ double
) returns int unsigned
not deterministic modifies sql data
comment 'using not documented trick of "order by -field desc" to put nulls last'
begin
    declare sortable int unsigned default (select a.sortable = 'y' from attributeT a where a.id = attribute_id_);
    
    if object_id_ is null then
        if sortable then
            insert cacheObjectSortT(objectId, attributeId, sorterAsc, sorterDesc)
            select objectn.id, attribute_id_, - coalesce(value_, avn.value), coalesce(value_, avn.value)
            from attributeT a
            join attributeValueNumericT avn on a.id = avn.attributeId
            join objectT objectn on avn.objectId = objectn.id
            where a.id = attribute_id_
                and a.dataTypeId = 'numeric'
            on duplicate key update 
                cacheObjectSortT.sorterAsc = values(sorterAsc),
                cacheObjectSortT.sorterDesc = values(sorterDesc);
            
            insert cacheObjectSortT(objectId, attributeId, sorterAsc, sorterDesc)
            select objectd.id, attribute_id_, - coalesce(value_, datetime_ToDouble(avd.value)), coalesce(value_, datetime_ToDouble(avd.value))
            from attributeT a
            join attributeValueDatetimeT avd on a.id = avd.attributeId
            join objectT objectd on avd.objectId = objectd.id
            where a.id = attribute_id_
                and a.dataTypeId = 'datetime'
            on duplicate key update 
                cacheObjectSortT.sorterAsc = values(sorterAsc),
                cacheObjectSortT.sorterDesc = values(sorterDesc);
        else
            delete from cacheObjectSortT where attributeId = attribute_id_;
        end if;
    else
        if sortable then 
            insert cacheObjectSortT(objectId, attributeId, sorterAsc, sorterDesc)
            select 
                o.id, 
                a.id, 
                - coalesce(value_, avn.value, datetime_ToDouble(avd.value)), 
                coalesce(value_, avn.value, datetime_ToDouble(avd.value))
            from attributeT a
            join objectT o on 1=1
            left join attributeValueNumericT avn on a.id = avn.attributeId and o.id = avn.objectId
            left join attributeValueDatetimeT avd on a.id = avd.attributeId and o.id = avd.objectId
            where a.id = attribute_id_
                and o.id = object_id_
            on duplicate key update 
                cacheObjectSortT.sorterAsc = values(sorterAsc),
                cacheObjectSortT.sorterDesc = values(sorterDesc);
        else
            delete from cacheObjectSortT where objectId = object_id_ and attributeId = attribute_id_;
        end if;
    end if;
    
    return object_id_;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function __object_UpdateSortCache2
-- -----------------------------------------------------
DROP function IF EXISTS `__object_UpdateSortCache2`;

DELIMITER $$$



create function __object_UpdateSortCache2(
    object_id_ int unsigned,
    attribute_id_ int unsigned,
    value_ double
) returns double
not deterministic modifies sql data
comment 'calls __object_UpdateSortCache and returns value_'
begin
    set @1 = __object_UpdateSortCache(object_id_, attribute_id_, value_);
    
    return value_;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function __object_UpdateAllSortCache
-- -----------------------------------------------------
DROP function IF EXISTS `__object_UpdateAllSortCache`;

DELIMITER $$$



create function __object_UpdateAllSortCache() returns int unsigned
not deterministic modifies sql data
comment 'updates all sort caches; to be called manually'
begin
    -- special attribute - rating; update all ratings as a side effect
    set @1 = __object_UpdateAllRatings();
    
    -- special attribute - last changed date
    insert zzzDevNullObjectT(n)
    select coalesce(__object_UpdateSortCacheChanged2(o.id, null), 0)
    from objectT o;
    
    -- all other attributes
    -- this query will select special attributes as well, 
    -- but won't update anything because special attributes have no attributeValueNumericT and attributeValueDatetimeT
    insert zzzDevNullObjectT(n)
    select coalesce(__object_UpdateSortCache(null, a.id, null), 0)
    from attributeT a
    where a.sortable = 'y';
    
    return 1;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function attribute_GetByCode
-- -----------------------------------------------------
DROP function IF EXISTS `attribute_GetByCode`;

DELIMITER $$$



create function attribute_GetByCode(code_ varchar(30) binary) returns int unsigned
deterministic
return (select a.id from attributeT a where a.code = code_)$$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure __search_UpdateSortCache
-- -----------------------------------------------------
DROP procedure IF EXISTS `__search_UpdateSortCache`;

DELIMITER $$$



create procedure __search_UpdateSortCache(session_id_ bigint unsigned)
modifies sql data
begin
    drop temporary table if exists searchUpdateSortCacheData;
    create temporary table searchUpdateSortCacheData(
        objectId int unsigned not null,
        attributeId int unsigned not null,
        primary key(objectId, attributeId)
    ) engine=MEMORY;

    insert searchUpdateSortCacheData(objectId, attributeId)
    select o.id, sas.id
    from searchResultV sr
    join objectT o on sr.nodeId = o.id
    join searchAttributeSortV sas on 1=1
    left join cacheObjectSortT os on o.id = os.objectId and sas.id = os.attributeId
    where os.objectId is null;
    
    insert zzzDevNullSearchT(n)
    select __object_UpdateSortCache(suscd.objectId, suscd.attributeId, null)
    from searchUpdateSortCacheData suscd;

    drop temporary table searchUpdateSortCacheData;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function __object_UpdateSortCacheRating2
-- -----------------------------------------------------
DROP function IF EXISTS `__object_UpdateSortCacheRating2`;

DELIMITER $$$



create function __object_UpdateSortCacheRating2(
    object_id_ int unsigned,
    value_ double
) returns double
not deterministic modifies sql data
begin
    -- NOTE: descending sort by default - note minus sign
    return - __object_UpdateSortCache2(
                                    object_id_, 
                                    attribute_GetByCode('__byrating'), 
                                    - coalesce(
                                            value_, 
                                            (select o.rating from objectT o where o.id = object_id_)));
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function __object_UpdateSortCacheChanged2
-- -----------------------------------------------------
DROP function IF EXISTS `__object_UpdateSortCacheChanged2`;

DELIMITER $$$



create function __object_UpdateSortCacheChanged2(
    object_id_ int unsigned,
    changed_ timestamp
) returns double
not deterministic modifies sql data
begin
    -- NOTE: descending sort by default - note minus sign
    return - __object_UpdateSortCache2(
                                    object_id_, 
                                    attribute_GetByCode('__bychanged'), 
                                    - datetime_ToDouble(
                                                coalesce(
                                                        changed_, 
                                                        (select n.changed from nodeT n where n.id = object_id_))));
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function user_CurrentName
-- -----------------------------------------------------
DROP function IF EXISTS `user_CurrentName`;

DELIMITER $$$



create function user_CurrentName() returns varchar(255)
deterministic no sql
return @__userName$$$

DELIMITER ;

-- -----------------------------------------------------
-- function user_CurrentIp
-- -----------------------------------------------------
DROP function IF EXISTS `user_CurrentIp`;

DELIMITER $$$



create function user_CurrentIp() returns varchar(40)
deterministic no sql
return @__userIp$$$

DELIMITER ;

-- -----------------------------------------------------
-- function __node_Update
-- -----------------------------------------------------
DROP function IF EXISTS `__node_Update`;

DELIMITER $$$



create function __node_Update(node_id_ int unsigned, name_ varchar(50)) returns int unsigned
not deterministic modifies sql data
begin
    set @1 = __node_CanUpdate(node_id_, 'update');
    
    update nodeT set
        name = coalesce(name_, name),
        changed = current_timestamp(),
        changedUserId = user_CurrentId(),
        changedUserName = user_CurrentName(),
        changedIp = user_CurrentIp()
    where id = node_id_;
    
    return node_id_;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure __search_UpdateExtraObjects
-- -----------------------------------------------------
DROP procedure IF EXISTS `__search_UpdateExtraObjects`;

DELIMITER $$$



create procedure __search_UpdateExtraObjects(session_id_ bigint unsigned)
modifies sql data
begin
    drop temporary table if exists searchUpdateExtraObjectsData;
    create temporary table searchUpdateExtraObjectsData(
        tagId int unsigned not null primary key,
        cnt int unsigned not null default 0
    ) engine=MEMORY;
    
    insert searchUpdateExtraObjectsData(tagId, cnt)
    select tagresult.nodeId, 0
    from searchResultT tagresult
    where tagresult.sessionId = session_id_;
    
-- all searchAnd tags
    insert searchUpdateExtraObjectsData(tagId, cnt)
    select tagresult.nodeId, 1
    from searchResultT tagresult
    join nodeLinkTypeT nlt on nlt.isDefault = 'y'
    join tagT t on tagresult.nodeId = t.id
    join nodeT n on t.id = n.id
    join nodeTypeT nt on n.nodeTypeId = nt.id
    where tagresult.sessionId = session_id_
        and nt.searchAnd = 'y'
        and exists (select 1 
                from nodeLinkT nl 
                join searchResultT objectresult on nl.dstNodeId = objectresult.nodeId
                join objectT o on objectresult.nodeId = o.id
                where nl.nodeLinkTypeId = nlt.id 
                    and nl.srcNodeId = t.id 
                    and nl.metric = 1
                    and objectresult.sessionId = tagresult.sessionId)
    on duplicate key update cnt = 1;

-- all non searchAnd tags, whose type is not selected
    insert searchUpdateExtraObjectsData(tagId, cnt)
    select tagresult.nodeId, 1
    from searchResultT tagresult
    join nodeLinkTypeT nlt on nlt.isDefault = 'y'
    join tagT t on tagresult.nodeId = t.id
    join nodeT n on t.id = n.id
    join nodeTypeT nt on n.nodeTypeId = nt.id
    where tagresult.sessionId = session_id_
        and nt.searchAnd = 'n'
        and exists (select 1 
                    from nodeLinkT nl 
                    join searchResultT objectresult on nl.dstNodeId = objectresult.nodeId
                    join objectT o on objectresult.nodeId = o.id
                    where objectresult.sessionId = tagresult.sessionId
                        and nl.nodeLinkTypeId = nlt.id 
                        and nl.srcNodeId = t.id 
                        and nl.metric = 1)
        and not exists (select 1
                    from searchCriterionT criteria
                    where criteria.sessionId = tagresult.sessionId
                        and criteria.nodeTypeId = nt.id)
    on duplicate key update cnt = 1;
    
-- all non searchAnd tags, whose type is selected, regardless if there are any objects
    insert searchUpdateExtraObjectsData(tagId, cnt)
    select tagresult.nodeId, 1
    from searchResultT tagresult
    join nodeLinkTypeT nlt on nlt.isDefault = 'y'
    join tagT t on tagresult.nodeId = t.id
    join nodeT n on t.id = n.id
    join nodeTypeT nt on n.nodeTypeId = nt.id
    where tagresult.sessionId = session_id_
        and nt.searchAnd = 'n'
        and exists (select 1
                    from searchCriterionT criteria
                    where criteria.sessionId = tagresult.sessionId
                        and criteria.nodeTypeId = nt.id)
    on duplicate key update cnt = 1;

    update searchResultT sr join searchUpdateExtraObjectsData sueod on sr.nodeId = sueod.tagId set
        sr.extraObjects = sueod.cnt
    where sr.sessionId = session_id_;

    drop temporary table searchUpdateExtraObjectsData;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function image_UpdateType
-- -----------------------------------------------------
DROP function IF EXISTS `image_UpdateType`;

DELIMITER $$$



create function image_UpdateType(
    image_id_ int unsigned,
    image_type_id_ char(10) binary,
    video_type_ varchar(50),
    url_ varchar(255),
    extra_id_ varchar(255)
) returns int unsigned
not deterministic modifies sql data
begin
    declare node_id int unsigned default (select nodeId from imageT where id = image_id_);
    
    set @1 = node_id is not null and __node_CanUpdate(node_id, 'imageupdate');
    return __image_Update(image_id_, node_id, null, null, null, null, null, image_type_id_, video_type_, url_, extra_id_);
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function image_UpdateTypeByUserForObject
-- -----------------------------------------------------
DROP function IF EXISTS `image_UpdateTypeByUserForObject`;

DELIMITER $$$



create function image_UpdateTypeByUserForObject(
    image_id_ int unsigned,
    image_type_id_ char(10) binary,
    video_type_ varchar(50),
    url_ varchar(255),
    extra_id_ varchar(255)
) returns int unsigned
not deterministic modifies sql data
begin
    declare node_id int unsigned default (select nodeId from imageT where id = image_id_);
    
    set @1 = __object_AccessByUser(node_id, 'update', null);
    return __image_Update(image_id_, node_id, null, null, null, null, null, image_type_id_, video_type_, url_, extra_id_);
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function __string_QuoteOut
-- -----------------------------------------------------
DROP function IF EXISTS `__string_QuoteOut`;

DELIMITER $$$



create function __string_QuoteOut(
    s varchar(2048), 
    purp varchar(10),
    rpl varchar(1)
) returns varchar(2048)
deterministic
begin
    declare result varchar(2048) default '';
    declare l int default char_length(s);
    
    set @__stringQuoteOutHelperPrevC = null; -- init
    
    select
        group_concat(__string_QuoteOutHelper(coalesce(vc.replacement, vc.id, rpl), coalesce(vc.forceSingle, 'y') = 'y') order by i.n separator '')
    into 
        result
    from zzzInts10000V i
    left join zzzValidCharT vc on substring(s, i.n + 1, 1) = vc.id and vc.purpose = purp
    where i.n < l;
    
    return result;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function string_Normalize
-- -----------------------------------------------------
DROP function IF EXISTS `string_Normalize`;

DELIMITER $$$



create function string_Normalize(s varchar(2048)) returns varchar(2048)
deterministic
return __string_QuoteOut(s, 'norm', '')$$$

DELIMITER ;

-- -----------------------------------------------------
-- function __string_QuoteOutHelper
-- -----------------------------------------------------
DROP function IF EXISTS `__string_QuoteOutHelper`;

DELIMITER $$$



-- string manipulation

create function __string_QuoteOutHelper(c_ varchar(1) binary, single_ int) returns varchar(1)
not deterministic
begin
    if single_ then
        if coalesce(@__stringQuoteOutHelperPrevC, '') = c_ then
            return '';
        else
            set @__stringQuoteOutHelperPrevC = c_;
            return c_;
        end if;
    else
        set @__stringQuoteOutHelperPrevC = c_;
        return c_;
    end if;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function __tag_UpdateCache
-- -----------------------------------------------------
DROP function IF EXISTS `__tag_UpdateCache`;

DELIMITER $$$



create function __tag_UpdateCache(tag_id_ int unsigned) returns int unsigned
not deterministic modifies sql data
begin
    insert tagCacheT(id, tagNames, tagNamesSearch)
    select 
        tl.id, 
        group_concat(concat_ws(0xb, tl.languageId, tl.name) separator '\0'),
        group_concat(concat_ws(0xb, tl.languageId, string_Normalize(tl.name)) separator '\0')
    from tagTL tl
    where tl.id = coalesce(tag_id_, tl.id)
    group by tl.id
    on duplicate key update
        tagCacheT.tagNames = values(tagNames),
        tagCacheT.tagNamesSearch = values(tagNamesSearch);
    
    return tag_id_;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- function __attribute_UpdateCache
-- -----------------------------------------------------
DROP function IF EXISTS `__attribute_UpdateCache`;

DELIMITER $$$



create function __attribute_UpdateCache(attribute_id_ int unsigned) returns int unsigned
not deterministic modifies sql data
begin
    insert attributeCacheT(id, attributeNames, attributeNamesSearch)
    select 
        al.id, 
        group_concat(concat_ws(0xb, al.languageId, al.uofmPre, al.uofmPost) separator '\0'),
        group_concat(concat_ws(0xb, al.languageId, string_Normalize(al.uofmPre), string_Normalize(al.uofmPost)) separator '\0')
    from attributeTL al
    where al.id = coalesce(attribute_id_, al.id)
    group by al.id
    on duplicate key update
        attributeCacheT.attributeNames = values(attributeNames),
        attributeCacheT.attributeNamesSearch = values(attributeNamesSearch);
    
    return attribute_id_;
end$$$

DELIMITER ;

-- -----------------------------------------------------
-- View `zzzNodeWorksetV`
-- -----------------------------------------------------
DROP VIEW IF EXISTS `zzzNodeWorksetV` ;
DROP TABLE IF EXISTS `zzzNodeWorksetV`;
CREATE  OR REPLACE VIEW `zzzNodeWorksetV` AS
select *
from zzzNodeWorksetT
where connId = connection_id();


-- -----------------------------------------------------
-- View `nodeTypeV`
-- -----------------------------------------------------
DROP VIEW IF EXISTS `nodeTypeV` ;
DROP TABLE IF EXISTS `nodeTypeV`;
CREATE  OR REPLACE VIEW nodeTypeV AS
select
    nt.id,
    ntl.name,
    nt.code,
    nt.internalTypeId,
    nt.forceSinglePhoto,
    nt.canEditType,
    nt.canEditNode,
    nt.canHavePhoto,
    nt.canHaveRatingPoints,
    nt.canLinkFrom,
    nt.canLinkTo,
    nt.canSearch,
    nt.displayInObject,
    nt.displayInObjectList,
    nt.searchAnd,
    nt.displayNode,
    nt.requiredForObject,
    nt.sorter,
    ntl.languageId,
    nt.added,
    nt.changed
from nodeTypeT nt
left join languageT l on l.isDefault = 'y'
left join nodeTypeTL ntl on nt.id = ntl.id and ntl.languageId = coalesce(user_CurrentLanguage(), l.id);


-- -----------------------------------------------------
-- View `nodeTypeVL`
-- -----------------------------------------------------
DROP VIEW IF EXISTS `nodeTypeVL` ;
DROP TABLE IF EXISTS `nodeTypeVL`;
CREATE  OR REPLACE VIEW nodeTypeVL AS
select ntl.*
from nodeTypeTL ntl;

-- -----------------------------------------------------
-- View `userV`
-- -----------------------------------------------------
DROP VIEW IF EXISTS `userV` ;
DROP TABLE IF EXISTS `userV`;
CREATE  OR REPLACE VIEW userV AS
select
    u.*,
    n.added,
    n.addedUserId,
    n.addedUserName,
    n.addedIp,
    n.changed,
    n.changedUserId,
    n.changedUserName,
    n.changedIp
from userT u
join _nodeV n on u.id = n.id;


-- -----------------------------------------------------
-- View `userOauthV`
-- -----------------------------------------------------
DROP VIEW IF EXISTS `userOauthV` ;
DROP TABLE IF EXISTS `userOauthV`;
CREATE  OR REPLACE VIEW userOauthV AS
select *
from userOauthT;



-- -----------------------------------------------------
-- View `allowedNodeLinkCanCreateV`
-- -----------------------------------------------------
DROP VIEW IF EXISTS `allowedNodeLinkCanCreateV` ;
DROP TABLE IF EXISTS `allowedNodeLinkCanCreateV`;
CREATE  OR REPLACE VIEW allowedNodeLinkCanCreateV AS
select 
    nt1.id srcNodeTypeId,
    nt2.id dstNodeTypeId
from nodeTypeT nt1
join nodeTypeT nt2 on 1=1
where nt1.canLinkFrom = 'y'
    and nt2.canLinkTo = 'y'
    and not exists (select 1 from allowedNodeLinkT anl where anl.srcNodeTypeId = nt1.id and anl.dstNodeTypeId = nt2.id);


-- -----------------------------------------------------
-- View `tagV`
-- -----------------------------------------------------
DROP VIEW IF EXISTS `tagV` ;
DROP TABLE IF EXISTS `tagV`;
CREATE  OR REPLACE VIEW tagV as
select
    t.id,
    n.nodeTypeId typeId,
    n.forceSingleNode,
    n.isRoot,
    n.typeCode,
    n.typeName,
    n.internalTypeId,
    n.canEditNode typeCanEdit,
    n.canHavePhoto typeCanHavePhoto,
    n.canHaveRatingPoints typeCanHaveRatingPoints,
    n.canSearch typeCanSearch,
    n.displayInObject typeDisplayInObject,
    n.displayInObjectList typeDisplayInObjectList,
    n.searchAnd typeSearchAnd,
    n.requiredForObject typeRequiredForObject,
    n.sorter typeSorter,
    tl.name,
    t.sorter,
    t.canSearch,
    t.canSearchRaw,
    t.displayInObject,
    t.displayInObjectRaw,
    t.displayInObjectList,
    t.displayInObjectListRaw,
    t.factor,
    t.ratingPoints,
    t.enabled,
    a.dataTypeId attributeDataType,
    a.code attributeCode,
    a.required,
    a.sortable,
    al.uofmPre,
    al.uofmPost,
    hasgeoroot.n geoRoot,
    n.languageId,
    n.added,
    n.addedUserId,
    n.addedUserName,
    n.addedIp,
    n.changed,
    n.changedUserId,
    n.changedUserName,
    n.changedIp
from tagT t
join nodeV n on t.id = n.id
left join tagTL tl on t.id = tl.id and n.languageId = tl.languageId
left join attributeT a on t.id = a.id
left join attributeTL al on a.id = al.id and n.languageId = al.languageId
left join zzzIntsT hasgeoroot on hasgeoroot.n = 1
    and exists (select 1 from nodeRootGeoV rootgeo where rootgeo.dstNodeId = n.id);


-- -----------------------------------------------------
-- View `tagVL`
-- -----------------------------------------------------
DROP VIEW IF EXISTS `tagVL` ;
DROP TABLE IF EXISTS `tagVL`;
CREATE  OR REPLACE VIEW tagVL AS
select *
from tagTL;

-- -----------------------------------------------------
-- View `allowedNodeLinkV`
-- -----------------------------------------------------
DROP VIEW IF EXISTS `allowedNodeLinkV` ;
DROP TABLE IF EXISTS `allowedNodeLinkV`;
CREATE  OR REPLACE VIEW allowedNodeLinkV AS
select *
from allowedNodeLinkT;


-- -----------------------------------------------------
-- View `searchResultV`
-- -----------------------------------------------------
DROP VIEW IF EXISTS `searchResultV` ;
DROP TABLE IF EXISTS `searchResultV`;
CREATE  OR REPLACE VIEW searchResultV AS
select *
from searchResultT sr
where sr.sessionId = search_CurrentId();

-- -----------------------------------------------------
-- View `searchCriterionV`
-- -----------------------------------------------------
DROP VIEW IF EXISTS `searchCriterionV` ;
DROP TABLE IF EXISTS `searchCriterionV`;
CREATE  OR REPLACE VIEW searchCriterionV AS
select sc.nodeId
from searchCriterionT sc
where sc.sessionId = search_CurrentId();

-- -----------------------------------------------------
-- View `searchCriterionNumericV`
-- -----------------------------------------------------
DROP VIEW IF EXISTS `searchCriterionNumericV` ;
DROP TABLE IF EXISTS `searchCriterionNumericV`;
CREATE  OR REPLACE VIEW searchCriterionNumericV AS
select scn.nodeId, scn.valueLow, scn.valueHigh
from searchCriterionNumericT scn
where scn.sessionId = search_CurrentId();

-- -----------------------------------------------------
-- View `searchCriterionDatetimeV`
-- -----------------------------------------------------
DROP VIEW IF EXISTS `searchCriterionDatetimeV` ;
DROP TABLE IF EXISTS `searchCriterionDatetimeV`;
CREATE  OR REPLACE VIEW searchCriterionDatetimeV AS
select scd.nodeId, scd.valueLow, scd.valueHigh
from searchCriterionDatetimeT scd
where scd.sessionId = search_CurrentId();

-- -----------------------------------------------------
-- View `nodeV`
-- -----------------------------------------------------
DROP VIEW IF EXISTS `nodeV` ;
DROP TABLE IF EXISTS `nodeV`;
CREATE  OR REPLACE VIEW nodeV AS
select
    n.id,
    n.nodeTypeId,
    n.forceSingleNode,
    n.name,
    n.isRoot,
    nt.name typeName,
    nt.code typeCode,
    nt.internalTypeId,
    nt.canEditType,
    nt.canEditNode,
    nt.canHavePhoto,
    nt.canHaveRatingPoints,
    nt.canLinkFrom,
    nt.canLinkTo,
    nt.canSearch,
    nt.displayInObject,
    nt.displayInObjectList,
    nt.searchAnd,
    nt.requiredForObject,
    nt.sorter,
    nt.languageId,
    n.added,
    n.addedUserId,
    n.addedUserName,
    n.addedIp,
    n.changed,
    n.changedUserId,
    n.changedUserName,
    n.changedIp
from _nodeV n
join nodeTypeV nt on n.nodeTypeId = nt.id;


-- -----------------------------------------------------
-- View `nodeLinkV`
-- -----------------------------------------------------
DROP VIEW IF EXISTS `nodeLinkV` ;
DROP TABLE IF EXISTS `nodeLinkV`;
CREATE  OR REPLACE VIEW nodeLinkV AS
select
    nlt.id nodeLinkTypeId,
    nlt.code,
    nlt.isDefault,
    nl.srcNodeId,
    nl.dstNodeId,
    nl.metric,
    nl.added
from nodeLinkT nl
join nodeLinkTypeT nlt on nl.nodeLinkTypeId = nlt.id;


-- -----------------------------------------------------
-- View `nodeLinkOwnsV`
-- -----------------------------------------------------
DROP VIEW IF EXISTS `nodeLinkOwnsV` ;
DROP TABLE IF EXISTS `nodeLinkOwnsV`;
-- ugly hack here - nodeLinkTypeId hardcoded!
CREATE  OR REPLACE VIEW nodeLinkOwnsV AS
select
    nl.nodeLinkTypeId,
    nl.srcNodeId,
    nl.dstNodeId,
    nl.metric,
    nl.added
from nodeLinkT nl
where nl.nodeLinkTypeId = 1
    and nl.metric > 0;


-- -----------------------------------------------------
-- View `allowedNodeLinkByNodeV`
-- -----------------------------------------------------
DROP VIEW IF EXISTS `allowedNodeLinkByNodeV` ;
DROP TABLE IF EXISTS `allowedNodeLinkByNodeV`;
CREATE  OR REPLACE VIEW allowedNodeLinkByNodeV AS
select
    srcNode.id srcNodeId,
    srcNode.nodeTypeId srcNodeTypeId,
    srcNode.displayNode srcDisplayNode,
    dstNode.id dstNodeId,
    dstNode.nodeTypeId dstNodeTypeId,
    dstNode.displayNode dstDisplayNode,
    anl.essential,
    anl.singleSrc,
    anl.added,
    anl.changed
from allowedNodeLinkT anl
join nodeT srcNode on anl.srcNodeTypeId = srcNode.nodeTypeId
join nodeT dstNode on anl.dstNodeTypeId = dstNode.nodeTypeId;


-- -----------------------------------------------------
-- View `nodeRootLinkV`
-- -----------------------------------------------------
DROP VIEW IF EXISTS `nodeRootLinkV` ;
DROP TABLE IF EXISTS `nodeRootLinkV`;
CREATE  OR REPLACE VIEW nodeRootLinkV AS
select
    rootnode.id,
    rootnode.nodeTypeId,
    rootnode.forceSingleNode,
    rootnode.name,
    rootnode.isRoot,
    rootnode.displayNode,
    rootnodetype.code typeCode,
    rootnodetype.canSearch typeCanSearch,
    rootnodetype.displayInObject typeDisplayInObject,
    rootnodetype.displayInObjectList typeDisplayInObjectList,
    rootnodetype.requiredForObject typeRequiredForObject,
    rootnodetype.sorter typeSorter,
    rootnode.added,
    rootnode.addedUserId,
    rootnode.addedUserName,
    rootnode.addedIp,
    rootnode.changed,
    rootnode.changedUserId,
    rootnode.changedUserName,
    rootnode.changedIp,
    rootlink.nodeLinkTypeId,
    rootlink.dstNodeId,
    rootlink.metric
from nodeT rootnode
join nodeTypeT rootnodetype on rootnode.nodeTypeId = rootnodetype.id
join nodeLinkOwnsV rootlink on rootnode.id = rootlink.srcNodeId
where rootnode.isRoot = 'y';


-- -----------------------------------------------------
-- View `objectV`
-- -----------------------------------------------------
DROP VIEW IF EXISTS `objectV` ;
DROP TABLE IF EXISTS `objectV`;
CREATE  OR REPLACE VIEW objectV AS
select
    o.id,
    o.address,
    o.description,
    o.statusId,
    osl.name statusName,
    os.public,
    os.userEditable,
    os.userDraftable,
    c.latitude,
    c.longitude,
    o.rating,
    o.noteForModerator,
    o.noteForUser,
    o.alias,
    n.languageId,
    n.added,
    n.addedUserId,
    n.addedUserName,
    n.addedIp,
    n.changed,
    n.changedUserId,
    n.changedUserName,
    n.changedIp
from objectT o
join nodeV n on o.id = n.id
join objectStatusT os on o.statusId = os.id
left join objectStatusTL osl on o.statusId = osl.id and n.languageId = osl.languageId
left join coordT c on o.id = c.nodeId;


-- -----------------------------------------------------
-- View `objectForUserV`
-- -----------------------------------------------------
DROP VIEW IF EXISTS `objectForUserV` ;
DROP TABLE IF EXISTS `objectForUserV`;
CREATE  OR REPLACE VIEW objectForUserV AS
select o.*
from objectV  o
where exists (
        select 1 
        from nodeLinkOwnsV nlo 
        where nlo.srcNodeId = user_CurrentId()
            and nlo.dstNodeId = o.id);


-- -----------------------------------------------------
-- View `_nodeRecommendLinkV`
-- -----------------------------------------------------
DROP VIEW IF EXISTS `_nodeRecommendLinkV` ;
DROP TABLE IF EXISTS `_nodeRecommendLinkV`;
CREATE  OR REPLACE VIEW _nodeRecommendLinkV as
select
    nlt.id nodeLinkTypeId,
    nlt.code nodeLinkTypeCode,
    srcnode.id srcNodeId,
    srcnode.nodeTypeId srcNodeTypeId,
    srclink.metric srcMetric,
    minsrclink.metric srcMin,
    srclink.srcNodeId viaNodeId,
    dstnode.id dstNodeId,
    dstnode.nodeTypeId dstNodeTypeId,
    dstlink.metric dstMetric,
    mindstlink.metric dstMin,
    anl.essential,
    nolink.metric existingMetric,
    i.n singleSrcExists
from nodeT srcnode
join nodeLinkTypeT nlt on 1=1
join nodeLinkT srclink on srcnode.id = srclink.dstNodeId and srclink.metric > 0 and nlt.id = srclink.nodeLinkTypeId
--    join nodeT nn on srclink.srcNodeId = nn.id
join nodeLinkT dstlink on srclink.srcNodeId = dstlink.srcNodeId and dstlink.metric > 0 and dstlink.nodeLinkTypeId = nlt.id
join nodeT dstnode on dstlink.dstNodeId = dstnode.id and srcnode.id != dstnode.id
join allowedNodeLinkT anl on srcnode.nodeTypeId = anl.srcNodeTypeId and dstnode.nodeTypeId = anl.dstNodeTypeId
left join nodeLinkT minsrclink on nlt.id = minsrclink.nodeLinkTypeId
    and srcnode.id = minsrclink.dstNodeId
    and minsrclink.metric > 0
    and minsrclink.metric < srclink.metric
left join nodeLinkT mindstlink on nlt.id = mindstlink.nodeLinkTypeId
    and dstnode.id = mindstlink.dstNodeId
    and minsrclink.srcNodeId = mindstlink.srcNodeId
    and mindstlink.metric > 0
    and mindstlink.metric < dstlink.metric
left join nodeLinkT nolink on srcnode.id = nolink.srcNodeId
    and dstnode.id = nolink.dstNodeId
    and nolink.nodeLinkTypeId = nlt.id
left join zzzIntsT i on i.n = 1
    and anl.singleSrc = 'y' 
    and exists(
        select 1 
        from nodeLinkT exilink 
        join nodeT exinode on exilink.srcNodeId = exinode.id
        where exilink.nodeLinkTypeId = nlt.id
            and exilink.dstNodeId = dstnode.id
            and exilink.metric > 0
            and exinode.nodeTypeId = srcnode.nodeTypeId);


-- -----------------------------------------------------
-- View `nodeRecommendLinkV`
-- -----------------------------------------------------
DROP VIEW IF EXISTS `nodeRecommendLinkV` ;
DROP TABLE IF EXISTS `nodeRecommendLinkV`;
CREATE  OR REPLACE VIEW nodeRecommendLinkV as
select
    nrl.nodeLinkTypeId,
    nrl.nodeLinkTypeCode,
    nrl.srcNodeId,
    nrl.srcNodeTypeId,
    1 srcMetric,
    1 viaNodeId,
    nrl.dstNodeId,
    nrl.dstNodeTypeId,
    1 dstMetric,
    nrl.essential,
    nrl.singleSrc
from _nodeRecommendLink4V nrl
where nrl.canLink = 1;


-- -----------------------------------------------------
-- View `tagForAdminV`
-- -----------------------------------------------------
DROP VIEW IF EXISTS `tagForAdminV` ;
DROP TABLE IF EXISTS `tagForAdminV`;
create  OR REPLACE view tagForAdminV as
select
    t.*,
    tc.tagNames,
    tc.tagNamesSearch,
    ac.attributeNames,
    ac.attributeNamesSearch
from tagV t
left join tagCacheT tc on t.id = tc.id
left join attributeCacheT ac on t.id = ac.id;


-- -----------------------------------------------------
-- View `nodeAncestryV`
-- -----------------------------------------------------
DROP VIEW IF EXISTS `nodeAncestryV` ;
DROP TABLE IF EXISTS `nodeAncestryV`;
create  OR REPLACE view nodeAncestryV as
select 
    na.id nodeId,
    na.tagNames tagName
from nodeAncestryT na
join languageT l on isDefault = 'y'
where na.languageId = coalesce(user_CurrentLanguage(), l.id);

-- -----------------------------------------------------
-- View `searchResultTagV`
-- -----------------------------------------------------
DROP VIEW IF EXISTS `searchResultTagV` ;
DROP TABLE IF EXISTS `searchResultTagV`;
create  OR REPLACE view searchResultTagV as
select
    t.*,
    sr.*
from tagV t
join searchResultV sr on t.id = sr.nodeId;


-- -----------------------------------------------------
-- View `searchResultObjectV`
-- -----------------------------------------------------
DROP VIEW IF EXISTS `searchResultObjectV` ;
DROP TABLE IF EXISTS `searchResultObjectV`;
create  OR REPLACE view searchResultObjectV as
select 
    o.*,
    sr.sessionId
from objectV o
join searchResultV sr on o.id = sr.nodeId
where o.public = 'y';


-- -----------------------------------------------------
-- View `tagChildV`
-- -----------------------------------------------------
DROP VIEW IF EXISTS `tagChildV` ;
DROP TABLE IF EXISTS `tagChildV`;
create  OR REPLACE view tagChildV as
select
    src.id srcId,
    src.typeId srcTypeId,
    src.typeCode srcTypeCode,
    src.typeName srcTypeName,
    src.internalTypeId srcInternalTypeId,
    src.typeCanEdit srcCanEdit,
    src.typeCanSearch srcTypeCanSearch,
    src.typeDisplayInObject srcTypeDisplayInObject,
    src.typeDisplayInObjectList srcTypeDisplayInObjectList,
    src.typeSorter srcTypeSorter,
    src.name srcName,
    src.sorter srcSorter,
    src.canSearch srcCanSearch,
    src.canSearchRaw srcCanSearchRaw,
    src.displayInObject srcDisplayInObject,
    src.displayInObjectRaw srcDisplayInObjectRaw,
    src.displayInObjectList srcDisplayInObjectList,
    src.displayInObjectListRaw srcDisplayInObjectListRaw,
    src.factor srcFactor,
    src.ratingPoints srcRatingPoints,
    src.enabled srcEnabled,
    src.attributeDataType srcAttributeDataType,
    src.required srcRequired,
    dst.id dstId,
    dst.typeId dstTypeId,
    dst.typeCode dstTypeCode,
    dst.typeName dstTypeName,
    dst.internalTypeId dstInternalTypeId,
    dst.typeCanEdit dstCanEdit,
    dst.typeCanSearch dstTypeCanSearch,
    dst.typeDisplayInObject dstTypeDisplayInObject,
    dst.typeDisplayInObjectList dstTypeDisplayInObjectList,
    dst.typeSorter dstTypeSorter,
    dst.name dstName,
    dst.sorter dstSorter,
    dst.canSearch dstCanSearch,
    dst.canSearchRaw dstCanSearchRaw,
    dst.displayInObject dstDisplayInObject,
    dst.displayInObjectRaw dstDisplayInObjectRaw,
    dst.displayInObjectList dstDisplayInObjectList,
    dst.displayInObjectListRaw dstDisplayInObjectListRaw,
    dst.factor dstFactor,
    dst.ratingPoints dstRatingPoints,
    dst.enabled dstEnabled,
    dst.attributeDataType dstAttributeDataType,
    dst.required dstRequired,
    src.isRoot srcIsRoot,
    src.geoRoot srcGeoRoot,
    na.tagNames dstAncestry
from tagV src
left join nodeLinkOwnsV nlo on src.id = nlo.srcNodeId and nlo.metric = 1
left join tagV dst on nlo.dstNodeId = dst.id
left join nodeAncestryT na on dst.id = na.id and dst.languageId = na.languageId
where src.isRoot = 'n';


-- -----------------------------------------------------
-- View `tagRecommendChildV`
-- -----------------------------------------------------
DROP VIEW IF EXISTS `tagRecommendChildV` ;
DROP TABLE IF EXISTS `tagRecommendChildV`;
create  OR REPLACE view tagRecommendChildV as
select
    src.id srcId,
    src.typeId srcTypeId,
    src.typeCode srcTypeCode,
    src.typeName srcTypeName,
    src.internalTypeId srcInternalTypeId,
    src.typeCanEdit srcCanEdit,
    src.typeCanSearch srcTypeCanSearch,
    src.typeDisplayInObject srcTypeDisplayInObject,
    src.typeDisplayInObjectList srcTypeDisplayInObjectList,
    src.typeSorter srcTypeSorter,
    src.name srcName,
    src.sorter srcSorter,
    src.canSearch srcCanSearch,
    src.canSearchRaw srcCanSearchRaw,
    src.displayInObject srcDisplayInObject,
    src.displayInObjectRaw srcDisplayInObjectRaw,
    src.displayInObjectList srcDisplayInObjectList,
    src.displayInObjectListRaw srcDisplayInObjectListRaw,
    src.factor srcFactor,
    src.ratingPoints srcRatingPoints,
    src.enabled srcEnabled,
    src.attributeDataType srcAttributeDataType,
    src.required srcRequired,
    dst.id dstId,
    dst.typeId dstTypeId,
    dst.typeCode dstTypeCode,
    dst.typeName dstTypeName,
    dst.internalTypeId dstInternalTypeId,
    dst.typeCanEdit dstCanEdit,
    dst.typeCanSearch dstTypeCanSearch,
    dst.typeDisplayInObject dstTypeDisplayInObject,
    dst.typeDisplayInObjectList dstTypeDisplayInObjectList,
    dst.typeSorter dstTypeSorter,
    dst.name dstName,
    dst.sorter dstSorter,
    dst.canSearch dstCanSearch,
    dst.canSearchRaw dstCanSearchRaw,
    dst.displayInObject dstDisplayInObject,
    dst.displayInObjectRaw dstDisplayInObjectRaw,
    dst.displayInObjectList dstDisplayInObjectList,
    dst.displayInObjectListRaw dstDisplayInObjectListRaw,
    dst.factor dstFactor,
    dst.ratingPoints dstRatingPoints,
    dst.enabled dstEnabled,
    dst.attributeDataType dstAttributeDataType,
    dst.required dstRequired,
    src.isRoot srcIsRoot,
    src.geoRoot srcGeoRoot,
    na.tagNames dstAncestry,
    nrl.srcMetric,
    nrl.dstMetric,
    nrl.essential
from tagV  src
left join nodeRecommendLinkV nrl on src.id = nrl.srcNodeId
left join tagV dst on nrl.dstNodeId = dst.id
left join nodeAncestryT na on dst.id = na.id and dst.languageId = na.languageId
where src.isRoot = 'n';


-- -----------------------------------------------------
-- View `tagParentV`
-- -----------------------------------------------------
DROP VIEW IF EXISTS `tagParentV` ;
DROP TABLE IF EXISTS `tagParentV`;
create  OR REPLACE view tagParentV as
select
    src.id srcId,
    src.typeId srcTypeId,
    src.typeCode srcTypeCode,
    src.typeName srcTypeName,
    src.internalTypeId srcInternalTypeId,
    src.typeCanEdit srcCanEdit,
    src.typeCanSearch srcTypeCanSearch,
    src.typeDisplayInObject srcTypeDisplayInObject,
    src.typeDisplayInObjectList srcTypeDisplayInObjectList,
    src.typeSorter srcTypeSorter,
    src.name srcName,
    src.sorter srcSorter,
    src.canSearch srcCanSearch,
    src.canSearchRaw srcCanSearchRaw,
    src.displayInObject srcDisplayInObject,
    src.displayInObjectRaw srcDisplayInObjectRaw,
    src.displayInObjectList srcDisplayInObjectList,
    src.displayInObjectListRaw srcDisplayInObjectListRaw,
    src.factor srcFactor,
    src.ratingPoints srcRatingPoints,
    src.enabled srcEnabled,
    src.attributeDataType srcAttributeDataType,
    src.required srcRequired,
    dst.id dstId,
    dst.typeId dstTypeId,
    dst.typeCode dstTypeCode,
    dst.typeName dstTypeName,
    dst.internalTypeId dstInternalTypeId,
    dst.typeCanEdit dstCanEdit,
    dst.typeCanSearch dstTypeCanSearch,
    dst.typeDisplayInObject dstTypeDisplayInObject,
    dst.typeDisplayInObjectList dstTypeDisplayInObjectList,
    dst.typeSorter dstTypeSorter,
    dst.name dstName,
    dst.sorter dstSorter,
    dst.canSearch dstCanSearch,
    dst.canSearchRaw dstCanSearchRaw,
    dst.displayInObject dstDisplayInObject,
    dst.displayInObjectRaw dstDisplayInObjectRaw,
    dst.displayInObjectList dstDisplayInObjectList,
    dst.displayInObjectListRaw dstDisplayInObjectListRaw,
    dst.factor dstFactor,
    dst.ratingPoints dstRatingPoints,
    dst.enabled dstEnabled,
    dst.attributeDataType dstAttributeDataType,
    dst.required dstRequired,
    src.isRoot srcIsRoot,
    src.geoRoot srcGeoRoot,
    na.tagNames srcAncestry
from tagV dst
left join nodeLinkOwnsV nlo on dst.id = nlo.dstNodeId and nlo.metric = 1
left join tagV src on nlo.srcNodeId = src.id
left join nodeAncestryT na on src.id = na.id and src.languageId = na.languageId
where src.isRoot = 'n';


-- -----------------------------------------------------
-- View `tagRecommendParentV`
-- -----------------------------------------------------
DROP VIEW IF EXISTS `tagRecommendParentV` ;
DROP TABLE IF EXISTS `tagRecommendParentV`;
create  OR REPLACE view tagRecommendParentV as
select
    src.id srcId,
    src.typeId srcTypeId,
    src.typeCode srcTypeCode,
    src.typeName srcTypeName,
    src.internalTypeId srcInternalTypeId,
    src.typeCanEdit srcCanEdit,
    src.typeCanSearch srcTypeCanSearch,
    src.typeDisplayInObject srcTypeDisplayInObject,
    src.typeDisplayInObjectList srcTypeDisplayInObjectList,
    src.typeSorter srcTypeSorter,
    src.name srcName,
    src.sorter srcSorter,
    src.canSearch srcCanSearch,
    src.canSearchRaw srcCanSearchRaw,
    src.displayInObject srcDisplayInObject,
    src.displayInObjectRaw srcDisplayInObjectRaw,
    src.displayInObjectList srcDisplayInObjectList,
    src.displayInObjectListRaw srcDisplayInObjectListRaw,
    src.factor srcFactor,
    src.ratingPoints srcRatingPoints,
    src.enabled srcEnabled,
    src.attributeDataType srcAttributeDataType,
    src.required srcRequired,
    dst.id dstId,
    dst.typeId dstTypeId,
    dst.typeCode dstTypeCode,
    dst.typeName dstTypeName,
    dst.internalTypeId dstInternalTypeId,
    dst.typeCanEdit dstCanEdit,
    dst.typeCanSearch dstTypeCanSearch,
    dst.typeDisplayInObject dstTypeDisplayInObject,
    dst.typeDisplayInObjectList dstTypeDisplayInObjectList,
    dst.typeSorter dstTypeSorter,
    dst.name dstName,
    dst.sorter dstSorter,
    dst.canSearch dstCanSearch,
    dst.canSearchRaw dstCanSearchRaw,
    dst.displayInObject dstDisplayInObject,
    dst.displayInObjectRaw dstDisplayInObjectRaw,
    dst.displayInObjectList dstDisplayInObjectList,
    dst.displayInObjectListRaw dstDisplayInObjectListRaw,
    dst.factor dstFactor,
    dst.ratingPoints dstRatingPoints,
    dst.enabled dstEnabled,
    dst.attributeDataType dstAttributeDataType,
    dst.required dstRequired,
    src.isRoot srcIsRoot,
    src.geoRoot srcGeoRoot,
    na.tagNames srcAncestry,
    nrl.srcMetric,
    nrl.dstMetric,
    nrl.essential
from tagV dst
left join nodeRecommendLinkV nrl on dst.id = nrl.dstNodeId
left join tagV src on nrl.srcNodeId = src.id
left join nodeAncestryT na on src.id = na.id and src.languageId = na.languageId
where src.isRoot = 'n';


-- -----------------------------------------------------
-- View `objectStatusV`
-- -----------------------------------------------------
DROP VIEW IF EXISTS `objectStatusV` ;
DROP TABLE IF EXISTS `objectStatusV`;
CREATE  OR REPLACE VIEW objectStatusV AS
select
    os.id,
    osl.name,
    os.public,
    os.userEditable,
    os.userDraftable,
    os.sorter
from objectStatusT os
left join languageT l on l.isDefault = 'y'
left join objectStatusTL osl ON osl.id = os.id and osl.languageId = coalesce(user_CurrentLanguage(), l.id);


-- -----------------------------------------------------
-- View `attributeV`
-- -----------------------------------------------------
DROP VIEW IF EXISTS `attributeV` ;
DROP TABLE IF EXISTS `attributeV`;
CREATE  OR REPLACE VIEW attributeV as
-- extra fields to be joined to tagV
select
    a.id, 
    an.decimalMin,
    an.decimalMax,
    an.decimalRound,
    ad.datetimeStorageId,
    ax.textIsLong
from attributeT a
left join attributeNumericT an on a.id = an.id
left join attributeDatetimeT ad on a.id = ad.id
left join attributeTextT ax on a.id = ax.id;


-- -----------------------------------------------------
-- View `_nodeRecommendLink2V`
-- -----------------------------------------------------
DROP VIEW IF EXISTS `_nodeRecommendLink2V` ;
DROP TABLE IF EXISTS `_nodeRecommendLink2V`;
CREATE  OR REPLACE VIEW _nodeRecommendLink2V as
select
    nlt.id nodeLinkTypeId,
    nlt.code nodeLinkTypeCode,
    srcnode.id srcNodeId,
    srcnode.nodeTypeId srcNodeTypeId,
    dstnode.id dstNodeId,
    dstnode.nodeTypeId dstNodeTypeId,
    anl.essential,
    anl.singleSrc,
    islinked.n linked,
    canlink.n canLink
from nodeT srcnode
join nodeLinkTypeT nlt on nlt.isDefault = 'y'
join allowedNodeLinkT anl on srcnode.nodeTypeId = anl.srcNodeTypeId
join nodeT dstnode on anl.dstNodeTypeId = dstnode.nodeTypeId and srcnode.id != dstnode.id
left join nodeLinkT linksingle on linksingle.nodeLinkTypeId = nlt.id 
    and linksingle.srcNodeId = srcnode.id 
    and linksingle.dstNodeId = dstnode.id 
    and anl.singleSrc = 'y'
left join nodeLinkT linkmult on linkmult.nodeLinkTypeId = nlt.id 
    and linkmult.srcNodeId = srcnode.id 
    and linkmult.dstNodeId = dstnode.id 
    and linkmult.metric = 1
    and anl.singleSrc = 'n'
left join zzzIntsT islinked on islinked.n = 1 and (linksingle.metric is not null or linkmult.metric is not null)
left join zzzIntsT canlink1 on canlink1.n = 1
    and anl.singleSrc = 'n'
    and linkmult.metric is null
    and (select count(distinct n1.nodeTypeId) 
            from nodeLinkT nl1 
            join nodeT n1 on nl1.srcNodeId = n1.id 
            where nl1.nodeLinkTypeId = nlt.id
                and nl1.dstNodeId = srcnode.id
                and nl1.metric = 1) =
        (select count(distinct n2.nodeTypeId)
            from nodeLinkT nl21
            join nodeT n2 on nl21.srcNodeId = n2.id
            join nodeLinkT nl22 on n2.id = nl22.srcNodeId
            where nl21.nodeLinkTypeId = nlt.id
                and nl21.dstNodeId = srcnode.id
                and nl21.metric = 1
                and nl22.nodeLinkTypeId = nlt.id
                and nl22.dstNodeId = dstnode.id
                and nl22.metric = 1)
left join zzzIntsT canlink3 on canlink3.n = 1
    and anl.singleSrc = 'y'
    and linksingle.metric is null
    and not exists(
        select 1 
        from nodeLinkT nl3 
        join nodeT n3 on nl3.srcNodeId = n3.id
        where nl3.nodeLinkTypeId = nlt.id
            and nl3.dstNodeId = dstnode.id
            and nl3.metric > 0
            and n3.nodeTypeId = srcnode.nodeTypeId)
    and exists(select 1 
                from nodeLinkT nl41
                join nodeLinkT nl42 on nl41.nodeLinkTypeId = nl42.nodeLinkTypeId and nl41.srcNodeId = nl42.srcNodeId
                join nodeT n4 on nl41.srcNodeId = n4.id
                join allowedNodeLinkT anl4 on n4.nodeTypeId = anl4.srcNodeTypeId
                left join nodeLinkT exilink41 on nl41.nodeLinkTypeId = exilink41.nodeLinkTypeId 
                    and nl41.dstNodeId = exilink41.dstNodeId
                    and exilink41.metric > 0
                    and exilink41.metric < nl41.metric
                left join nodeLinkT exilink42 on nl42.nodeLinkTypeId = exilink42.nodeLinkTypeId 
                    and nl42.dstNodeId = exilink42.dstNodeId
                    and exilink41.srcNodeId = exilink42.srcNodeId
                    and exilink42.metric > 0
                    and exilink42.metric < nl42.metric
                where nl41.nodeLinkTypeId = nlt.id
                    and nl41.dstNodeId = srcnode.id
                    and nl41.metric > 0
                    and nl42.dstNodeId = dstnode.id
                    and nl42.metric > 0
                    and n4.isRoot = 'n'
                    and (exilink41.metric is null or exilink42.metric is null)
                    and anl4.dstNodeTypeId = dstnode.nodeTypeId
                    and anl4.singleSrc = anl.singleSrc)
left join zzzIntsT canlink4 on canlink4.n = 1
    and anl.singleSrc = 'y'
    and linksingle.metric is null
    and canlink3.n is null
    and exists(select 1 
                from nodeLinkT nl51
                join nodeLinkT nl52 on nl51.nodeLinkTypeId = nl52.nodeLinkTypeId and nl51.srcNodeId = nl52.srcNodeId
                where nl51.nodeLinkTypeId = nlt.id
                    and nl51.dstNodeId = srcnode.id
                    and nl51.metric = 1
                    and nl52.dstNodeId = dstnode.id
                    and nl52.metric = 1)
left join zzzIntsT canlink on canlink.n = 1 and (canlink1.n = 1 or canlink3.n = 1 or canlink4.n = 1)
left join templateT srcTemplate on srcnode.id = srcTemplate.dstNodeId
left join templateT dstTemplate on dstnode.id = dstTemplate.dstNodeId
where srcTemplate.dstNodeId is null
    and dstTemplate.dstNodeId is null
    and (canlink.n = 1 or islinked.n = 1);


-- -----------------------------------------------------
-- View `searchTagV`
-- -----------------------------------------------------
DROP VIEW IF EXISTS `searchTagV` ;
DROP TABLE IF EXISTS `searchTagV`;
create  OR REPLACE view searchTagV as
select
    t.*,
    sc.nodeId criterionId,
    scn.valueLow valueLowNumeric,
    scn.valueHigh valueHighNumeric,
    scd.valueLow valueLowDatetime,
    scd.valueHigh valueHighDatetime,
    sr.nodeId resultId,
    sr.collapsed,
    sr.extraObjects
from tagV t
left join searchCriterionV sc on t.id = sc.nodeId
left join searchCriterionNumericV scn on t.id = scn.nodeId
left join searchCriterionDatetimeV scd on t.id = scd.nodeId
left join searchResultV sr on t.id = sr.nodeId;


-- -----------------------------------------------------
-- View `attributeDatetimeStorageFormatV`
-- -----------------------------------------------------
DROP VIEW IF EXISTS `attributeDatetimeStorageFormatV` ;
DROP TABLE IF EXISTS `attributeDatetimeStorageFormatV`;
CREATE  OR REPLACE VIEW attributeDatetimeStorageFormatV as
-- more extra fields to be joined to attributeV (and tagV)
select
    ad.id, 
    adfl.languageId,
    ad.datetimeStorageId,
    adf.storageFormat,
    adfl.name,
    adfl.outputFormat,
    adfl.outputFormatJs
from attributeDatetimeT ad 
join attributeDatetimeFormatT adf on ad.datetimeStorageId = adf.id
join attributeDatetimeFormatTL adfl on adf.id = adfl.id;


-- -----------------------------------------------------
-- View `objectWithTagV`
-- -----------------------------------------------------
DROP VIEW IF EXISTS `objectWithTagV` ;
DROP TABLE IF EXISTS `objectWithTagV`;
create  OR REPLACE view objectWithTagV as
select
    t.id srcId,
    t.typeId srcTypeId,
    t.typeCode srcTypeCode,
    t.typeName srcTypeName,
    t.internalTypeId srcInternalTypeId,
    t.typeCanEdit srcCanEdit,
    t.typeCanSearch srcTypeCanSearch,
    t.typeDisplayInObject srcTypeDisplayInObject,
    t.typeDisplayInObjectList srcTypeDisplayInObjectList,
    t.typeSorter srcTypeSorter,
    t.name srcName,
    t.sorter srcSorter,
    t.canSearch srcCanSearch,
    t.canSearchRaw srcCanSearchRaw,
    t.displayInObject srcDisplayInObject,
    t.displayInObjectRaw srcDisplayInObjectRaw,
    t.displayInObjectList srcDisplayInObjectList,
    t.displayInObjectListRaw srcDisplayInObjectListRaw,
    t.factor srcFactor,
    t.ratingPoints srcRatingPoints,
    t.enabled srcEnabled,
    t.attributeDataType srcAttributeDataType,
    t.attributeCode srcAttributeCode,
    t.required srcRequired,
    t.sortable srcSortable,
    t.uofmPre srcUofmPre,
    t.uofmPost srcUofmPost,
    t.languageId srcLanguageId,
    t.isRoot srcIsRoot,
    t.geoRoot srcGeoRoot,

    a.decimalMin srcDecimalMin,
    a.decimalMax srcDecimalMax,
    a.decimalRound srcDecimalRound,
    a.datetimeStorageId srcDatetimeStorageId,
    a.textIsLong srcTextIsLong,

    adsf.storageFormat srcDatetimeStorageFormat,
    adsf.name srcDatetimeName,
    adsf.outputFormat srcDatetimeOutputFormat,
    adsf.outputFormatJs srcDatetimeOutputFormatJs,

    avn.value srcNumericValue,
    avd.value srcDatetimeValue,
    avt.value srcTextValue,

    nrl.linked,
    nrl.canLink,

    o.id objectId
from objectT o
join _nodeRecommendLink4V nrl on o.id = nrl.dstNodeId
join tagV t on nrl.srcNodeId = t.id
left join attributeV a on t.id = a.id
left join attributeDatetimeStorageFormatV adsf on a.id = adsf.id and t.languageId = adsf.languageId
left join attributeValueNumericT avn on a.id = avn.attributeId and o.id = avn.objectId
left join attributeValueDatetimeT avd on a.id = avd.attributeId and o.id = avd.objectId
left join attributeValueTextT avt on a.id = avt.attributeId and o.id = avt.objectId and t.languageId = avt.languageId;


-- -----------------------------------------------------
-- View `nodeRootGeoV`
-- -----------------------------------------------------
DROP VIEW IF EXISTS `nodeRootGeoV` ;
DROP TABLE IF EXISTS `nodeRootGeoV`;
create  OR REPLACE view nodeRootGeoV as
select
    rootlink.id,
    rootlink.nodeTypeId,
    rootlink.forceSingleNode,
    rootlink.name,
    rootlink.isRoot,
    rootlink.displayNode,
    rootlink.typeCode,
    rootlink.typeCanSearch,
    rootlink.typeDisplayInObject,
    rootlink.typeDisplayInObjectList,
    rootlink.typeRequiredForObject,
    rootlink.typeSorter,
    rootlink.added,
    rootlink.addedUserId,
    rootlink.addedUserName,
    rootlink.addedIp,
    rootlink.changed,
    rootlink.changedUserId,
    rootlink.changedUserName,
    rootlink.changedIp,
    rootlink.dstNodeId,
    rootlink.metric
from nodeRootLinkV rootlink
where rootlink.typeCode = 'rootgeo';


-- -----------------------------------------------------
-- View `attributeDatetimeFormatV`
-- -----------------------------------------------------
DROP VIEW IF EXISTS `attributeDatetimeFormatV` ;
DROP TABLE IF EXISTS `attributeDatetimeFormatV`;
create  OR REPLACE view attributeDatetimeFormatV as
-- to be used as list of available datetime storage formats
select
    adf.id,
    adfl.name,
    adf.storageFormat,
    adfl.outputFormat,
    adfl.outputFormatJs,
    adf.sorter,
    adfl.languageId,
    adf.added,
    adf.changed
from attributeDatetimeFormatT adf
left join languageT l on l.isDefault = 'y'
left join attributeDatetimeFormatTL adfl on adf.id = adfl.id and adfl.languageId = coalesce(user_CurrentLanguage(), l.id);


-- -----------------------------------------------------
-- View `imageV`
-- -----------------------------------------------------
DROP VIEW IF EXISTS `imageV` ;
DROP TABLE IF EXISTS `imageV`;
create  OR REPLACE view imageV as
select
    img.id,
    img.id imageId,
    img.nodeId,
    img.forceSinglePhoto,
    img.name,
    img.filenameOriginal,
    img.filenameFs,
    img.display,
    img.sorter,
    img.imageTypeId,
    img.videoType,
    img.url,
    img.extraId,
    img.added,
    img.addedUserId,
    img.addedUserName,
    img.addedIp,
    img.changed,
    img.changedUserId,
    img.changedUserName,
    img.changedIp
from imageT img;


-- -----------------------------------------------------
-- View `templateV`
-- -----------------------------------------------------
DROP VIEW IF EXISTS `templateV` ;
DROP TABLE IF EXISTS `templateV`;
create  OR REPLACE view templateV as
select
    node.id nodeId,
    tmpl.srcNodeTypeId,
    tmpl.added
from nodeT node
left join templateT tmpl on node.id = tmpl.dstNodeId;


-- -----------------------------------------------------
-- View `_nodeRecommendLink3V`
-- -----------------------------------------------------
DROP VIEW IF EXISTS `_nodeRecommendLink3V` ;
DROP TABLE IF EXISTS `_nodeRecommendLink3V`;
CREATE  OR REPLACE VIEW _nodeRecommendLink3V as
select
    nlt.id nodeLinkTypeId,
    nlt.code nodeLinkTypeCode,
    srcnode.id srcNodeId,
    srcnode.nodeTypeId srcNodeTypeId,
    dstnode.id dstNodeId,
    dstnode.nodeTypeId dstNodeTypeId,
    anl.essential,
    anl.singleSrc,
    link.metric linked,
    canlink.n canLink
from nodeT srcnode
join nodeLinkTypeT nlt on nlt.isDefault = 'y'
join allowedNodeLinkT anl on srcnode.nodeTypeId = anl.srcNodeTypeId
join nodeT dstnode on anl.dstNodeTypeId = dstnode.nodeTypeId and srcnode.id != dstnode.id

left join nodeLinkT link on nlt.id = link.nodeLinkTypeId
    and srcnode.id = link.srcNodeId
    and dstnode.id = link.dstNodeId
    and link.metric = 1

left join zzzIntsT canlink1 on canlink1.n = 1
    and link.metric is null
    and anl.singleSrc = 'n'
    and (select count(distinct n1.nodeTypeId)
            from nodeLinkT nl1
            join nodeT n1 on nl1.srcNodeId = n1.id
            where nl1.nodeLinkTypeId = nlt.id
                and nl1.dstNodeId = srcnode.id
                and nl1.metric = 1) =
        (select count(distinct n2.nodeTypeId)
            from nodeLinkT nl21
            join nodeT n2 on nl21.srcNodeId = n2.id
            join nodeLinkT nl22 on n2.id = nl22.srcNodeId
            where nl21.nodeLinkTypeId = nlt.id
                and nl21.dstNodeId = srcnode.id
                and nl21.metric = 1
                and nl22.nodeLinkTypeId = nlt.id
                and nl22.dstNodeId = dstnode.id
                and nl22.metric = 1)

left join zzzIntsT canlink3 on canlink3.n = 1
    and link.metric is null
    and anl.singleSrc = 'y'
-- check if already linked to the node of the same type
    and not exists(
        select 1
        from nodeLinkT nl3
        join nodeT n3 on nl3.srcNodeId = n3.id
        where nl3.nodeLinkTypeId = nlt.id
            and nl3.dstNodeId = dstnode.id
            and nl3.metric = 1
            and n3.nodeTypeId = srcnode.nodeTypeId)
-- check if srcnode.id and dstnode.id have common parent node, with special conditions
    and exists(
        select 1
        from nodeLinkT nl41
        join nodeLinkT nl42 on nl41.nodeLinkTypeId = nl42.nodeLinkTypeId
            and nl41.srcNodeId = nl42.srcNodeId
        join nodeT n4 on nl41.srcNodeId = n4.id
        join allowedNodeLinkT anl4 on n4.nodeTypeId = anl4.srcNodeTypeId
        where nl41.nodeLinkTypeId = nlt.id
            and nl41.dstNodeId = srcnode.id
            and nl41.metric = 1
            and nl42.dstNodeId = dstnode.id
            and nl42.metric = 1
            and (anl4.essential = 'y'
-- if the link is not essential make sure we are not stepping over the srcnode.id
                or not exists(
                    select 1
                    from nodeLinkT exilink41
                    join nodeLinkT exilink42 on exilink41.nodeLinkTypeId = exilink42.nodeLinkTypeId
                        and exilink41.dstNodeId = exilink42.srcNodeId
                    where nl41.nodeLinkTypeId = exilink41.nodeLinkTypeId
                        and nl41.srcNodeId = exilink41.srcNodeId
                        and nl41.dstNodeId != exilink41.dstNodeId
                        and exilink42.dstNodeId = nl41.dstNodeId))
            and anl4.dstNodeTypeId = dstnode.nodeTypeId
            and anl4.singleSrc = anl.singleSrc
-- and make sure we use the nearest common parent, i.e. there are no more common parents up the tree
            and not exists(
                select 1
                from nodeLinkT exilink43
                join nodeLinkT exilink44 on exilink43.nodeLinkTypeId = exilink44.nodeLinkTypeId
                    and exilink43.dstNodeId = exilink44.srcNodeId
                where nl42.nodeLinkTypeId = exilink43.nodeLinkTypeId
                    and nl42.srcNodeId = exilink43.srcNodeId
                    and nl42.dstNodeId != exilink43.dstNodeId
                    and exilink44.dstNodeId = nl42.dstNodeId))
left join zzzIntsT canlink on canlink.n = 1 and (canlink1.n = 1 or canlink3.n = 1)
left join templateT srcTemplate on srcnode.id = srcTemplate.dstNodeId
left join templateT dstTemplate on dstnode.id = dstTemplate.dstNodeId
where srcTemplate.dstNodeId is null
    and dstTemplate.dstNodeId is null
    and (canlink.n = 1 or link.metric = 1);


-- -----------------------------------------------------
-- View `nodeLinkChainV`
-- -----------------------------------------------------
DROP VIEW IF EXISTS `nodeLinkChainV` ;
DROP TABLE IF EXISTS `nodeLinkChainV`;
create  OR REPLACE view nodeLinkChainV as
select
    chain1.nodeLinkTypeId,
    chain1.srcNodeId,
    chain1.dstNodeId midNodeId,
    chain2.dstNodeId,
    chain1.metric metric1,
    chain2.metric metric2,
    chain1.added added1,
    chain2.added added2
from nodeLinkT chain1
join nodeLinkT chain2 on chain1.nodeLinkTypeId = chain2.nodeLinkTypeId
    and chain1.dstNodeId = chain2.srcNodeId;


-- -----------------------------------------------------
-- View `nodeLinkForkSrcV`
-- -----------------------------------------------------
DROP VIEW IF EXISTS `nodeLinkForkSrcV` ;
DROP TABLE IF EXISTS `nodeLinkForkSrcV`;
create  OR REPLACE view nodeLinkForkSrcV as
select
    fork1.nodeLinkTypeId,
    fork1.srcNodeId,
    fork1.dstNodeId dstNodeId1,
    fork2.dstNodeId dstNodeId2,
    fork1.metric metric1,
    fork2.metric metric2,
    fork1.added added1,
    fork2.added added2
from nodeLinkT fork1
join nodeLinkT fork2 on fork1.nodeLinkTypeId = fork2.nodeLinkTypeId
    and fork1.srcNodeId = fork2.srcNodeId;


-- -----------------------------------------------------
-- View `_nodeRecommendLink4V`
-- -----------------------------------------------------
DROP VIEW IF EXISTS `_nodeRecommendLink4V` ;
DROP TABLE IF EXISTS `_nodeRecommendLink4V`;
CREATE  OR REPLACE VIEW _nodeRecommendLink4V as
select
    nlt.id nodeLinkTypeId,
    nlt.code nodeLinkTypeCode,
    srcnode.id srcNodeId,
    srcnode.nodeTypeId srcNodeTypeId,
    dstnode.id dstNodeId,
    dstnode.nodeTypeId dstNodeTypeId,
    anl.essential,
    anl.singleSrc,
    link.metric linked,
    canlink.n canLink
from nodeT srcnode
join nodeLinkTypeT nlt on nlt.isDefault = 'y'
join allowedNodeLinkT anl on srcnode.nodeTypeId = anl.srcNodeTypeId
join nodeT dstnode on anl.dstNodeTypeId = dstnode.nodeTypeId and srcnode.id != dstnode.id

left join nodeLinkT link on nlt.id = link.nodeLinkTypeId
    and srcnode.id = link.srcNodeId
    and dstnode.id = link.dstNodeId
    and link.metric = 1

left join zzzIntsT canlink1 on canlink1.n = 1
    and link.metric is null
    and anl.singleSrc = 'n'
    and (select count(distinct n1.nodeTypeId)
            from nodeLinkT nl1
            join nodeT n1 on nl1.srcNodeId = n1.id
            where nl1.nodeLinkTypeId = nlt.id
                and nl1.dstNodeId = srcnode.id
                and nl1.metric = 1) =
        (select count(distinct n2.nodeTypeId)
            from nodeLinkForkSrcV nl2
            join nodeT n2 on nl2.srcNodeId = n2.id
            where nl2.nodeLinkTypeId = nlt.id
                and nl2.dstNodeId1 = srcnode.id
                and nl2.metric1 = 1
                and nl2.dstNodeId2 = dstnode.id
                and nl2.metric2 = 1)

left join zzzIntsT canlink3 on canlink3.n = 1
    and link.metric is null
    and anl.singleSrc = 'y'
-- check if already linked to the node of the same type
    and not exists(
        select 1
        from nodeLinkT nl3
        join nodeT n3 on nl3.srcNodeId = n3.id
        where nl3.nodeLinkTypeId = nlt.id
            and nl3.dstNodeId = dstnode.id
            and nl3.metric = 1
            and n3.nodeTypeId = srcnode.nodeTypeId)
-- check if srcnode.id and dstnode.id have common parent node, with special conditions
    and exists(
        select 1
        from nodeLinkForkSrcV nl4
        join nodeT n4 on nl4.srcNodeId = n4.id
        join allowedNodeLinkT anl4 on n4.nodeTypeId = anl4.srcNodeTypeId
        where nl4.nodeLinkTypeId = nlt.id
            and nl4.dstNodeId1 = srcnode.id
            and nl4.metric1 = 1
            and nl4.dstNodeId2 = dstnode.id
            and nl4.metric2 = 1
            and anl4.dstNodeTypeId = dstnode.nodeTypeId
            and anl4.singleSrc = anl.singleSrc
            and (anl4.essential = 'y'
-- if the link is not essential make sure we are not stepping over the srcnode.id
                or not exists(
                    select 1
                    from nodeLinkChainTransitionV exilink41
                    where nl4.nodeLinkTypeId = exilink41.nodeLinkTypeId
                        and nl4.srcNodeId = exilink41.srcNodeId
                        and nl4.dstNodeId1 = exilink41.dstNodeId))
-- and make sure we use the nearest common parent, i.e. there are no more common parents up the tree
            and not exists(
                select 1
                from nodeLinkChainTransitionV exilink42
                where nl4.nodeLinkTypeId = exilink42.nodeLinkTypeId
                    and nl4.srcNodeId = exilink42.srcNodeId
                    and nl4.dstNodeId2 = exilink42.dstNodeId))
left join zzzIntsT canlink on canlink.n = 1 and (canlink1.n = 1 or canlink3.n = 1)
left join templateT srcTemplate on srcnode.id = srcTemplate.dstNodeId
left join templateT dstTemplate on dstnode.id = dstTemplate.dstNodeId
where srcTemplate.dstNodeId is null
    and dstTemplate.dstNodeId is null
    and (canlink.n = 1 or link.metric = 1);


-- -----------------------------------------------------
-- View `objectWithTagLinkedV`
-- -----------------------------------------------------
DROP VIEW IF EXISTS `objectWithTagLinkedV` ;
DROP TABLE IF EXISTS `objectWithTagLinkedV`;
create  OR REPLACE view objectWithTagLinkedV as
select
    t.id srcId,
    t.typeId srcTypeId,
    t.typeCode srcTypeCode,
    t.typeName srcTypeName,
    t.internalTypeId srcInternalTypeId,
    t.typeCanEdit srcCanEdit,
    t.typeCanSearch srcTypeCanSearch,
    t.typeDisplayInObject srcTypeDisplayInObject,
    t.typeDisplayInObjectList srcTypeDisplayInObjectList,
    t.typeSorter srcTypeSorter,
    t.name srcName,
    t.sorter srcSorter,
    t.canSearch srcCanSearch,
    t.canSearchRaw srcCanSearchRaw,
    t.displayInObject srcDisplayInObject,
    t.displayInObjectRaw srcDisplayInObjectRaw,
    t.displayInObjectList srcDisplayInObjectList,
    t.displayInObjectListRaw srcDisplayInObjectListRaw,
    t.factor srcFactor,
    t.ratingPoints srcRatingPoints,
    t.enabled srcEnabled,
    t.attributeDataType srcAttributeDataType,
    t.attributeCode srcAttributeCode,
    t.required srcRequired,
    t.sortable srcSortable,
    t.uofmPre srcUofmPre,
    t.uofmPost srcUofmPost,
    t.languageId srcLanguageId,
    t.isRoot srcIsRoot,
    t.geoRoot srcGeoRoot,

    a.decimalMin srcDecimalMin,
    a.decimalMax srcDecimalMax,
    a.decimalRound srcDecimalRound,
    a.datetimeStorageId srcDatetimeStorageId,
    a.textIsLong srcTextIsLong,

    adsf.storageFormat srcDatetimeStorageFormat,
    adsf.name srcDatetimeName,
    adsf.outputFormat srcDatetimeOutputFormat,
    adsf.outputFormatJs srcDatetimeOutputFormatJs,

    avn.value srcNumericValue,
    avd.value srcDatetimeValue,
    avt.value srcTextValue,

    1 linked,
    null canLink,

    o.id objectId
from objectT o
join nodeLinkOwnsV nlo on o.id = nlo.dstNodeId
join tagV t on nlo.srcNodeId = t.id
left join attributeV a on t.id = a.id
left join attributeDatetimeStorageFormatV adsf on a.id = adsf.id and t.languageId = adsf.languageId
left join attributeValueNumericT avn on a.id = avn.attributeId and o.id = avn.objectId
left join attributeValueDatetimeT avd on a.id = avd.attributeId and o.id = avd.objectId
left join attributeValueTextT avt on a.id = avt.attributeId and o.id = avt.objectId and t.languageId = avt.languageId
where nlo.metric = 1;


-- -----------------------------------------------------
-- View `nodeLinkChainTransitionV`
-- -----------------------------------------------------
DROP VIEW IF EXISTS `nodeLinkChainTransitionV` ;
DROP TABLE IF EXISTS `nodeLinkChainTransitionV`;
create  OR REPLACE view nodeLinkChainTransitionV as
select
    nodeLinkTypeId,
    srcNodeId,
    midNodeId,
    dstNodeId,
    metric1,
    metric2,
    added1,
    added2
from nodeLinkChainV
where srcNodeId != midNodeId
    and midNodeId != dstNodeId
    and metric1 = 1
    and metric2 = 1;


-- -----------------------------------------------------
-- View `nodeLinkDirectV`
-- -----------------------------------------------------
DROP VIEW IF EXISTS `nodeLinkDirectV` ;
DROP TABLE IF EXISTS `nodeLinkDirectV`;
create  OR REPLACE view nodeLinkDirectV as
select *
from nodeLinkT directlink
where directlink.metric = 1
    and not exists(
        select 1 
        from nodeLinkChainTransitionV exilink 
        where directlink.nodeLinkTypeId = exilink.nodeLinkTypeId
            and directlink.srcNodeId = exilink.srcNodeId
            and directlink.dstNodeId = exilink.dstNodeId);


-- -----------------------------------------------------
-- View `nodeAncestryDataV`
-- -----------------------------------------------------
DROP VIEW IF EXISTS `nodeAncestryDataV` ;
DROP TABLE IF EXISTS `nodeAncestryDataV`;
create  OR REPLACE view nodeAncestryDataV as
select 
    srcNode.id srcNodeId,
    tl.name srcName,
    nt.id srcTypeId,
    nt.internalTypeId srcInternalTypeId,
    ntl.name srcTypeName,
    avn.value srcNumericValue,
    avd.value srcDatetimeValue,
    adfl.outputFormat srcDatetimeOutputFormat,
    avt.value srcTextValue,
    
    dstNode.id dstNodeId,
    dstNode.nodeTypeId dstTypeId,
    l.id languageId,
    l.isDefault languageIsDefault,
    nas.sorter ancestrySorter
from nodeT dstNode 
join languageT l on 1=1
join nodeLinkTypeT nlt on nlt.isDefault = 'y'
join nodeLinkT nl on nlt.id = nl.nodeLinkTypeId and dstNode.id = nl.dstNodeId
join nodeT srcNode on nl.srcNodeId = srcNode.id
join nodeAncestrySourceT nas on dstNode.nodeTypeId = nas.nodeTypeId 
    and srcNode.nodeTypeId = nas.srcNodeTypeId
join nodeTypeT nt on srcNode.nodeTypeId = nt.id
join nodeTypeTL ntl on srcNode.nodeTypeId = ntl.id and l.id = ntl.languageId
join tagTL tl on srcNode.id = tl.id and l.id = tl.languageId
left join attributeValueNumericT avn on srcNode.id = avn.attributeId and dstNode.id = avn.objectId
left join attributeValueDatetimeT avd on srcNode.id = avd.attributeId and dstNode.id = avd.objectId
left join attributeDatetimeT ad on avd.attributeId = ad.id
left join attributeDatetimeFormatTL adfl on ad.datetimeStorageId = adfl.id and l.id = adfl.languageId
left join attributeValueTextT avt on srcNode.id = avt.attributeId and dstNode.id = avt.objectId and l.id = avt.languageId
where srcNode.displayNode = 'y'
    and nl.metric = 1;


-- -----------------------------------------------------
-- View `objectWithUserV`
-- -----------------------------------------------------
DROP VIEW IF EXISTS `objectWithUserV` ;
DROP TABLE IF EXISTS `objectWithUserV`;
create  OR REPLACE view objectWithUserV as
select
    u.*,
    o.id objectId
from userV u
join nodeLinkOwnsV nlo on u.id = nlo.srcNodeId
right join objectT o on nlo.dstNodeId = o.id;


-- -----------------------------------------------------
-- View `_nodeV`
-- -----------------------------------------------------
DROP VIEW IF EXISTS `_nodeV` ;
DROP TABLE IF EXISTS `_nodeV`;
create  OR REPLACE view _nodeV as
select *
from nodeT
where displayNode = 'y';


-- -----------------------------------------------------
-- View `attributeSortV`
-- -----------------------------------------------------
DROP VIEW IF EXISTS `attributeSortV` ;
DROP TABLE IF EXISTS `attributeSortV`;
create  OR REPLACE view attributeSortV as
-- intentionally disregard nodeT.displayNode in this view
select
    n.id,
    nt.name typeName,
    nt.code typeCode,
    tl.name,
    t.sorter,
    a.code,
    nt.languageId,
    n.added,
    n.addedUserId,
    n.addedUserName,
    n.addedIp,
    n.changed,
    n.changedUserId,
    n.changedUserName,
    n.changedIp
from nodeT n
join nodeTypeV nt on n.nodeTypeId = nt.id
join tagT t on n.id = t.id
join tagTL tl on t.id = tl.id and nt.languageId = tl.languageId
join attributeT a on t.id = a.id
where t.enabled = 'y'
    and a.sortable = 'y';


-- -----------------------------------------------------
-- View `searchResultObjectSortedV`
-- -----------------------------------------------------
DROP VIEW IF EXISTS `searchResultObjectSortedV` ;
DROP TABLE IF EXISTS `searchResultObjectSortedV`;
create  OR REPLACE view searchResultObjectSortedV as
select 
    sro.*,
    os.attributeId sortAttributeId,
    os.sorterAsc,
    os.sorterDesc
from searchResultObjectV sro
join cacheObjectSortT os on sro.id = os.objectId;


-- -----------------------------------------------------
-- View `searchAttributeSortV`
-- -----------------------------------------------------
DROP VIEW IF EXISTS `searchAttributeSortV` ;
DROP TABLE IF EXISTS `searchAttributeSortV`;
create  OR REPLACE view searchAttributeSortV as
select * 
from attributeSortV aso
where exists(
        select 1
        from searchResultV sr
        join cacheObjectSortT os on sr.nodeId = os.objectId
        where os.attributeId = aso.id);


-- -----------------------------------------------------
-- View `zzzIntsV`
-- -----------------------------------------------------
DROP VIEW IF EXISTS `zzzIntsV` ;
DROP TABLE IF EXISTS `zzzIntsV`;
create  OR REPLACE view zzzIntsV as
select 0 as n
union all select 1
union all select 2
union all select 3
union all select 4
union all select 5
union all select 6
union all select 7
union all select 8
union all select 9;


-- -----------------------------------------------------
-- View `zzzInts100V`
-- -----------------------------------------------------
DROP VIEW IF EXISTS `zzzInts100V` ;
DROP TABLE IF EXISTS `zzzInts100V`;
create  OR REPLACE view zzzInts100V as
select (i1.n + i2.n * 10) n
from zzzIntsV i1
join zzzIntsV i2 on 1=1;


-- -----------------------------------------------------
-- View `zzzInts1000V`
-- -----------------------------------------------------
DROP VIEW IF EXISTS `zzzInts1000V` ;
DROP TABLE IF EXISTS `zzzInts1000V`;
create  OR REPLACE view zzzInts1000V as
select (i1.n + i2.n * 10) n
from zzzIntsV i1
join zzzInts100V i2 on 1=1;


-- -----------------------------------------------------
-- View `zzzInts10000V`
-- -----------------------------------------------------
DROP VIEW IF EXISTS `zzzInts10000V` ;
DROP TABLE IF EXISTS `zzzInts10000V`;
create  OR REPLACE view zzzInts10000V as
select (i1.n + i2.n * 100) n
from zzzInts100V i1
join zzzInts100V i2 on 1=1;

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

DELIMITER $$$

DROP TRIGGER IF EXISTS `nodeTypeT_bi` $$$
CREATE TRIGGER `nodeTypeT_bi` BEFORE INSERT ON `nodeTypeT` FOR EACH ROW
-- Edit trigger body code below this line. Do not edit lines above this one
begin
    set new.added = now();

    set new.forceSingleType = (select it.forceSingleType from internalTypeT it where it.id = new.internalTypeId);
    set new.code = if(new.forceSingleType = 'y', new.internalTypeId, new.code);
end
$$$


DROP TRIGGER IF EXISTS `nodeT_bi` $$$
CREATE TRIGGER `nodeT_bi` BEFORE INSERT ON `nodeT` FOR EACH ROW
-- Edit trigger body code below this line. Do not edit lines above this one
begin
    set new.added = now();
    set new.forceSingleNode = (select nt.forceSingleNode from nodeTypeT nt where nt.id = new.nodeTypeId);
end$$$


DROP TRIGGER IF EXISTS `nodeLinkTypeT_bi` $$$
CREATE TRIGGER `nodeLinkTypeT_bi` BEFORE INSERT ON `nodeLinkTypeT` FOR EACH ROW
-- Edit trigger body code below this line. Do not edit lines above this one
begin
    set new.added = now();
end$$$


DROP TRIGGER IF EXISTS `languageT_bi` $$$
CREATE TRIGGER `languageT_bi` BEFORE INSERT ON `languageT` FOR EACH ROW
-- Edit trigger body code below this line. Do not edit lines above this one
begin
    set new.added = now();
end$$$


DROP TRIGGER IF EXISTS `allowedNodeLinkT_bi` $$$
CREATE TRIGGER `allowedNodeLinkT_bi` BEFORE INSERT ON `allowedNodeLinkT` FOR EACH ROW
-- Edit trigger body code below this line. Do not edit lines above this one
begin
	set new.added = now();
end$$$


DROP TRIGGER IF EXISTS `objectStatusT_bi` $$$
CREATE TRIGGER `objectStatusT_bi` BEFORE INSERT ON `objectStatusT` FOR EACH ROW
-- Edit trigger body code below this line. Do not edit lines above this one
begin
    set new.added = now();
end$$$


DROP TRIGGER IF EXISTS `imageT_bi` $$$
CREATE TRIGGER `imageT_bi` BEFORE INSERT ON `imageT` FOR EACH ROW
-- Edit trigger body code below this line. Do not edit lines above this one
begin
    set new.added = now();
end$$$


DROP TRIGGER IF EXISTS `attributeValueNumericT_bi` $$$
CREATE TRIGGER `attributeValueNumericT_bi` BEFORE INSERT ON `attributeValueNumericT` FOR EACH ROW
begin
    set new.added = now();
end$$$


DROP TRIGGER IF EXISTS `attributeDatetimeFormatT_bi` $$$
CREATE TRIGGER `attributeDatetimeFormatT_bi` BEFORE INSERT ON `attributeDatetimeFormatT` FOR EACH ROW
-- Edit trigger body code below this line. Do not edit lines above this one
begin
    set new.added = now();
end$$$


DROP TRIGGER IF EXISTS `attributeValueDatetimeT_bi` $$$
CREATE TRIGGER `attributeValueDatetimeT_bi` BEFORE INSERT ON `attributeValueDatetimeT` FOR EACH ROW
begin
    set new.added = now();
end$$$


DROP TRIGGER IF EXISTS `attributeValueTextT_bi` $$$
CREATE TRIGGER `attributeValueTextT_bi` BEFORE INSERT ON `attributeValueTextT` FOR EACH ROW
begin
    set new.added = now();
end$$$


DROP TRIGGER IF EXISTS `templateT_bi` $$$
CREATE TRIGGER `templateT_bi` BEFORE INSERT ON `templateT` FOR EACH ROW
-- Edit trigger body code below this line. Do not edit lines above this one
begin
    set new.dstNodeTypeId = coalesce(new.dstNodeTypeId, (select nodeTypeId from nodeT n where n.id = new.dstNodeId));
end$$$


DROP TRIGGER IF EXISTS `zzzNodeWorksetT_bi` $$$
CREATE TRIGGER `zzzNodeWorksetT_bi` BEFORE INSERT ON `zzzNodeWorksetT` FOR EACH ROW
begin
	set new.connId = connection_id();
end$$$


DROP TRIGGER IF EXISTS `oauthProviderT_bi` $$$
CREATE TRIGGER `oauthProviderT_bi` BEFORE INSERT ON `oauthProviderT` FOR EACH ROW
begin
    set new.added = now();
end$$$


DROP TRIGGER IF EXISTS `userOauthT_bi` $$$
CREATE TRIGGER `userOauthT_bi` BEFORE INSERT ON `userOauthT` FOR EACH ROW
begin
    set new.added = now();
end$$$


DROP TRIGGER IF EXISTS `externalDataSourceT_bi` $$$
CREATE TRIGGER `externalDataSourceT_bi` BEFORE INSERT ON `externalDataSourceT` FOR EACH ROW
begin
    set new.added = now();
end$$$


DROP TRIGGER IF EXISTS `externalDataT_bi` $$$
CREATE TRIGGER `externalDataT_bi` BEFORE INSERT ON `externalDataT` FOR EACH ROW
begin
    set new.added = now();
end$$$


DROP TRIGGER IF EXISTS `externalDataSourceDatasetT_bi` $$$
CREATE TRIGGER `externalDataSourceDatasetT_bi` BEFORE INSERT ON `externalDataSourceDatasetT` FOR EACH ROW
begin
    set new.added = now();
end$$$


DROP TRIGGER IF EXISTS `externalOsmDictionaryT_bi` $$$
CREATE TRIGGER `externalOsmDictionaryT_bi` BEFORE INSERT ON `externalOsmDictionaryT` FOR EACH ROW
begin
    set new.added = now();
end$$$


DROP TRIGGER IF EXISTS `searchSessionT_bi` $$$
CREATE TRIGGER `searchSessionT_bi` BEFORE INSERT ON `searchSessionT` FOR EACH ROW
begin
    set new.id = coalesce(new.id, uuid_short());
end$$$


DROP TRIGGER IF EXISTS `nodeAncestrySourceT_bi` $$$
CREATE TRIGGER `nodeAncestrySourceT_bi` BEFORE INSERT ON `nodeAncestrySourceT` FOR EACH ROW
begin
    set new.added = now();
end$$$


DELIMITER ;
