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 = coalesce(password_, 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_, ''), coalesce(action_, ''), 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_, ''), coalesce(action_, ''), 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 '' 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 ;