-- --------------------------------------------------------------------------------
-- _001_views
-- DON'T put this file into workbench
-- --------------------------------------------------------------------------------

-- nodeType

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


CREATE VIEW nodeTypeVL AS
select ntl.*
from nodeTypeTL ntl;


-- nodeLink

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


-- ugly hack here - nodeLinkTypeId hardcoded!
CREATE 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;


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


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


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


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


-- node

create view _nodeV as
select *
from nodeT
where displayNode = 'y';


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


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


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


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


create view templateV as
select
    node.id nodeId,
    tmpl.srcNodeTypeId,
    tmpl.added
from nodeT node
left join templateT tmpl on node.id = tmpl.dstNodeId;


-- allowedNodeLink

CREATE VIEW allowedNodeLinkV AS
select *
from allowedNodeLinkT;


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


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


-- node recommend link

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


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


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


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


/*CREATE VIEW nodeRecommendLinkV as
select
    nrl.nodeLinkTypeId,
    nrl.nodeLinkTypeCode,
    nrl.srcNodeId,
    nrl.srcNodeTypeId,
    nrl.srcMetric,
    nrl.viaNodeId,
    nrl.dstNodeId,
    nrl.dstNodeTypeId,
    nrl.dstMetric,
    nrl.essential
from _nodeRecommendLinkV nrl
where nrl.existingMetric is null
    and nrl.srcMin is null
    and nrl.dstMin is null
    and nrl.singleSrcExists is null;*/

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


-- user

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


CREATE VIEW userOauthV AS
select *
from userOauthT;


-- tag and attribute

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


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


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


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


CREATE VIEW tagVL AS
select *
from tagTL;


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


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


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


create view nodeAncestryV as
select
    na.id nodeId,
    na.tagNames tagName
from nodeAncestryT na
left join languageT l on l.isDefault = 'y'
where na.languageId = coalesce(user_CurrentLanguage(), l.id);


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


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


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


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


-- object

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


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


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


create 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;
-- where (nrl.linked = 1 or nrl.canLink = 1)
--    and t.id is not null;


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


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


-- search

CREATE VIEW searchCriterionV AS
select sc.nodeId
from searchCriterionT sc
where sc.sessionId = search_CurrentId();


CREATE VIEW searchCriterionNumericV AS
select scn.nodeId, scn.valueLow, scn.valueHigh
from searchCriterionNumericT scn
where scn.sessionId = search_CurrentId();


CREATE VIEW searchCriterionDatetimeV AS
select scd.nodeId, scd.valueLow, scd.valueHigh
from searchCriterionDatetimeT scd
where scd.sessionId = search_CurrentId();


CREATE VIEW searchResultV AS
select *
from searchResultT sr
where sr.sessionId = search_CurrentId();


create view searchResultTagV as
select
    t.*,
    sr.*
from tagV t
join searchResultV sr on t.id = sr.nodeId;


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


create view searchResultObjectV as
select 
    o.*,
    sr.sessionId
from objectV o
join searchResultV sr on o.id = sr.nodeId
where o.public = 'y';


create view searchResultObjectSortedV as
select 
    sro.*,
    os.attributeId sortAttributeId,
    os.sorterAsc,
    os.sorterDesc
from searchResultObjectV sro
join cacheObjectSortT os on sro.id = os.objectId;


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


-- utilities

CREATE VIEW zzzNodeWorksetV AS
select *
from zzzNodeWorksetT
where connId = connection_id();


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


create view zzzInts100V as
select (i1.n + i2.n * 10) n
from zzzIntsV i1
join zzzIntsV i2 on 1=1;


create view zzzInts1000V as
select (i1.n + i2.n * 10) n
from zzzIntsV i1
join zzzInts100V i2 on 1=1;


create view zzzInts10000V as
select (i1.n + i2.n * 100) n
from zzzInts100V i1
join zzzInts100V i2 on 1=1;