NodeDAO.java

/*
 * This file is part of vospace-rest
 * Copyright (C) 2021 Istituto Nazionale di Astrofisica
 * SPDX-License-Identifier: GPL-3.0-or-later
 */
package it.inaf.oats.vospace.persistence;

import it.inaf.oats.vospace.DeleteNodeController;
import it.inaf.oats.vospace.URIUtils;
import it.inaf.oats.vospace.datamodel.NodeProperties;
import it.inaf.oats.vospace.datamodel.NodeUtils;
import it.inaf.oats.vospace.exception.InternalFaultException;
import java.sql.Array;
import net.ivoa.xml.vospace.v2.Node;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Optional;
import java.util.Set;
import java.util.function.Function;
import java.util.stream.Collectors;
import javax.sql.DataSource;
import net.ivoa.xml.vospace.v2.ContainerNode;
import net.ivoa.xml.vospace.v2.DataNode;
import net.ivoa.xml.vospace.v2.LinkNode;
import net.ivoa.xml.vospace.v2.Property;
import net.ivoa.xml.vospace.v2.View;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

@Repository
public class NodeDAO {

    private static final Logger LOG = LoggerFactory.getLogger(DeleteNodeController.class);

    @Value("${vospace-authority}")
    private String authority;

    private final JdbcTemplate jdbcTemplate;

    @Autowired
    public NodeDAO(DataSource dataSource) {
        jdbcTemplate = new JdbcTemplate(dataSource);
    }

    public void createNode(Node node) {
        createNode(node, null);
    }

    public void createNode(Node myNode, String jobId) {

        String nodeVosPath = URIUtils.returnVosPathFromNodeURI(myNode, authority);

        List<NodePaths> paths = getNodePathsFromDB(nodeVosPath);

        if (paths.isEmpty()) {
            throw new IllegalStateException("Unable to find parent node during node creation");
        }

        StringBuilder sb = new StringBuilder();
        sb.append("INSERT INTO node");
        sb.append(" (name, job_id, creator_id, group_read, group_write,");
        sb.append(" is_public, parent_path, parent_relative_path, type, accept_views, provide_views, target)");
        sb.append(" VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");

        jdbcTemplate.update(conn -> {
            PreparedStatement ps = conn.prepareStatement(sb.toString());
            int i = 0;
            ps.setString(++i, NodeUtils.getNodeName(nodeVosPath));
            if (jobId == null) {
                ps.setNull(++i, Types.VARCHAR);
            } else {
                ps.setString(++i, jobId);
            }
            ps.setString(++i, NodeProperties.getNodePropertyByURI(myNode, NodeProperties.CREATOR_URI));
            ps.setArray(++i, fromPropertyToArray(ps, NodeProperties.getNodePropertyByURI(myNode, NodeProperties.GROUP_READ_URI)));
            ps.setArray(++i, fromPropertyToArray(ps, NodeProperties.getNodePropertyByURI(myNode, NodeProperties.GROUP_WRITE_URI)));
            ps.setBoolean(++i, Boolean.valueOf(NodeProperties.getNodePropertyByURI(myNode, NodeProperties.PUBLIC_READ_URI)));
            ps.setObject(++i, paths.get(0).getPath(), Types.OTHER);
            ps.setObject(++i, paths.get(0).getRelativePath(), Types.OTHER);
            ps.setObject(++i, NodeUtils.getDbNodeType(myNode), Types.OTHER);
            ps.setObject(++i, fromViewsToArray(ps, myNode, d -> d.getAccepts()), Types.OTHER);
            ps.setObject(++i, fromViewsToArray(ps, myNode, d -> d.getProvides()), Types.OTHER);
            if (myNode instanceof LinkNode) {
                ps.setString(++i, ((LinkNode) myNode).getTarget());
            } else {
                ps.setNull(++i, Types.VARCHAR);
            }
            return ps;
        });
    }

    public Optional<Node> listNode(String path) {

        String sql = "SELECT (CASE WHEN c.path = n.path THEN ? ELSE (? || ? || c.name) END) AS vos_path, c.node_id, c.name,\n"
                + "c.type, c.async_trans, c.sticky, c.job_id IS NOT NULL AS busy_state, c.creator_id, c.group_read, c.group_write,\n"
                + "c.is_public, c.content_length, c.created_on, c.last_modified, c.accept_views, c.provide_views, c.quota, c.content_md5, c.target\n"
                + "FROM node n\n"
                + "JOIN node c ON c.parent_path = n.path OR c.path = n.path\n"
                + "WHERE n.node_id = id_from_vos_path(?)\n"
                + "ORDER BY vos_path";

        List<Node> parentAndChildren = jdbcTemplate.query(conn -> {
            PreparedStatement ps = conn.prepareStatement(sql);
            int i = 0;
            ps.setString(++i, path);
            ps.setString(++i, path);
            ps.setString(++i, "/".equals(path) ? "" : "/");
            ps.setString(++i, path);
            return ps;
        }, (row, index) -> {
            return getNodeFromResultSet(row);
        });

        if (parentAndChildren.isEmpty()) {
            return Optional.empty();
        }

        // Query returns parent as first node
        Node node = parentAndChildren.get(0);

        // Fill children
        if (node instanceof ContainerNode && parentAndChildren.size() > 1) {
            ContainerNode parent = (ContainerNode) node;
            for (int i = 1; i < parentAndChildren.size(); i++) {
                parent.getNodes().add(parentAndChildren.get(i));
            }
        }

        return Optional.of(node);
    }

    public List<String> listNodeChildren(String path) {

        String sql = "SELECT n.name\n"
                + "FROM node n\n"
                + "WHERE n.path ~ ('*.' || id_from_vos_path(?) || '.*{1}')::lquery\n"
                + "ORDER BY n.path";

        List<String> childrenNames = jdbcTemplate.query(conn -> {
            PreparedStatement ps = conn.prepareStatement(sql);
            int i = 0;
            ps.setString(++i, path);
            return ps;
        }, (row, index) -> {
            return row.getString("name");
        });

        return childrenNames;
    }

    public Node setNode(Node newNode) {
        return setNode(newNode, false);
    }

    /**
     * If recursive flag is true the update is applied to children too.
     */
    public Node setNode(Node newNode, boolean recursive) {

        String vosPath = URIUtils.returnVosPathFromNodeURI(newNode, authority);

        boolean isLinkNode = newNode instanceof LinkNode;

        // If is link ignore recursive: LinkNodes are supposed to be childless
        if (recursive && !isLinkNode) {
            updatePermissionsRecursively(newNode, vosPath);
        } else {
            jdbcTemplate.update(conn -> {

                String sql = "UPDATE node\n"
                        + "SET group_read = ?, group_write = ?, is_public = ?\n";

                if (isLinkNode) {
                    sql += ", target = ?\n";
                }

                sql += "WHERE node_id = id_from_vos_path(?)\n";

                PreparedStatement ps = conn.prepareStatement(sql);
                int i = 0;
                ps.setArray(++i, fromPropertyToArray(ps, NodeProperties.getNodePropertyByURI(newNode, NodeProperties.GROUP_READ_URI)));
                ps.setArray(++i, fromPropertyToArray(ps, NodeProperties.getNodePropertyByURI(newNode, NodeProperties.GROUP_WRITE_URI)));
                ps.setBoolean(++i, Boolean.valueOf(NodeProperties.getNodePropertyByURI(newNode, NodeProperties.PUBLIC_READ_URI)));
                if (isLinkNode) {
                    ps.setString(++i, ((LinkNode) newNode).getTarget() );
                }
                ps.setString(++i, vosPath);
                return ps;
            });
        }

        return newNode;
    }

    private Node getNodeFromResultSet(ResultSet rs) throws SQLException {

        Node node = NodeUtils.getTypedNode(rs.getString("type"));

        if (node instanceof DataNode) {

            DataNode dataNode = (DataNode) node;

            dataNode.setBusy(rs.getBoolean("busy_state"));
            dataNode.setAccepts(getViews(rs.getArray("accept_views")));
            dataNode.setProvides(getViews(rs.getArray("provide_views")));
        } else if (node instanceof LinkNode) {
            LinkNode linkNode = (LinkNode) node;
            linkNode.setTarget(rs.getString("target"));
        }

        node.setUri(getUri(rs.getString("vos_path")));

        List<Property> properties = new ArrayList<>();

        // Content length is required for CADC client compatibility
        String contentLength = rs.getString("content_length");
        addProperty(NodeProperties.LENGTH_URI, contentLength == null ? "0" : contentLength, properties);

        String creationTime = rs.getString("created_on").replace(" ", "T");

        addProperty(NodeProperties.INITIAL_CREATION_TIME_URI, creationTime, properties);
        addProperty(NodeProperties.DATE_URI, creationTime, properties); // required by CADC

        addProperty(NodeProperties.CREATOR_URI, rs.getString("creator_id"),
                properties);

        addProperty(NodeProperties.MODIFICATION_TIME_URI, rs.getString("last_modified"),
                properties);

        addProperty(NodeProperties.GROUP_READ_URI, getGroupsString(rs, "group_read"),
                properties);

        addProperty(NodeProperties.GROUP_WRITE_URI, getGroupsString(rs, "group_write"),
                properties);

        addProperty(NodeProperties.PUBLIC_READ_URI, String.valueOf(rs.getBoolean("is_public")),
                properties);

        addProperty(NodeProperties.QUOTA_URI, rs.getString("quota"), properties);

        addProperty(NodeProperties.MD5_URI, rs.getString("content_md5"), properties);

        addProperty("urn:async_trans", String.valueOf(rs.getBoolean("async_trans")),
                properties);

        addProperty("urn:sticky", String.valueOf(rs.getBoolean("sticky")), properties);

        node.setProperties(properties);
        return node;
    }

    public Optional<Long> getNodeId(String nodeVosPath) {
        String sql = "SELECT id_from_vos_path(?) AS node_id";
        Long nodeId = jdbcTemplate.queryForObject(sql, Long.class, nodeVosPath);
        return Optional.ofNullable(nodeId);
    }

    public Optional<ShortNodeDescriptor> getShortNodeDescriptor(String nodeVosPath,
            String userId, List<String> userGroups) {

        String sql = "SELECT path,\n"
                + "NOT (n.async_trans OR COALESCE(location_type = 'async', FALSE)) AS is_writable,\n"
                + "n.sticky AS is_sticky,\n"
                + "((SELECT COUNT(*) FROM (SELECT UNNEST(?) INTERSECT SELECT UNNEST(n.group_write)) AS allowed_groups ) = 0 AND\n"
                + "n.creator_id <> ?) AS is_permission_denied,\n"
                + "n.type = 'container' AS is_container,\n"
                + "n.job_id IS NOT NULL AS busy_state\n"
                + "FROM node n \n"
                + "LEFT JOIN location loc ON loc.location_id = n.location_id\n"
                + "WHERE n.node_id = id_from_vos_path(?)\n";

        Optional<ShortNodeDescriptor> sndOpt = jdbcTemplate.query(conn -> {
            PreparedStatement ps = conn.prepareStatement(sql);

            String[] groups;
            if (userGroups == null) {
                groups = new String[0];
            } else {
                groups = userGroups.toArray(String[]::new);
            }
            ps.setArray(1, ps.getConnection().createArrayOf("varchar", groups));

            ps.setString(2, userId);
            ps.setString(3, nodeVosPath);
            return ps;
        }, rs -> {
            if (!rs.next()) {
                return Optional.empty();
            }

            String nodePath = rs.getString("path");
            Boolean isContainer = rs.getBoolean(("is_container"));
            Boolean isWritable = rs.getBoolean("is_writable");
            Boolean isBusy = rs.getBoolean("busy_state");
            Boolean isPermissionDenied = rs.getBoolean("is_permission_denied");
            Boolean isSticky = rs.getBoolean("is_sticky");

            ShortNodeDescriptor result = new ShortNodeDescriptor(nodePath, isContainer, isWritable, isBusy, isPermissionDenied, isSticky);

            return Optional.of(result);
        });

        return sndOpt;

    }

    public void renameNode(Long nodeId, String name) {
        String sql = "UPDATE node SET name = ?, os_name = COALESCE(os_name, name) "
                + "WHERE node_id = ?";

        jdbcTemplate.update(conn -> {
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, name);
            ps.setLong(2, nodeId);
            return ps;
        });

    }

    public void moveNodeBranch(Long sourceRootId, String destParentLtreePath) {

        String sql = "UPDATE node c SET "
                + "parent_path = (? || SUBPATH(c.path, (SELECT nlevel(parent_path) FROM node WHERE node_id = ?), -1))::ltree, "
                + "parent_relative_path = COALESCE(c.parent_relative_path, c.parent_path) " // not sure about this
                + "FROM node n "
                + "WHERE n.path @> c.path AND n.node_id = ?";

        jdbcTemplate.update(conn -> {
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, destParentLtreePath);
            ps.setLong(2, sourceRootId);
            ps.setLong(3, sourceRootId);
            return ps;
        });
    }

    public void copyBranch(String sourceVosPath, String destVosPath) {

        String destVosParentPath = NodeUtils.getParentPath(destVosPath);
        String destName = NodeUtils.getNodeName(destVosPath);

        String parentInsert = "INSERT INTO node (node_id, parent_path, parent_relative_path, name, type, location_id, creator_id, group_write, group_read, is_public,\n"
                + "job_id, tstamp_wrapper_dir, format, async_trans, sticky, accept_views, provide_views, protocols, target)\n";

        // If destination has path '' no prefix, else "destination_path."
        String ctePathPrefix = "SELECT CASE WHEN path::varchar = '' THEN '' ELSE (path::varchar || '.') END AS prefix\n"
                + "FROM node WHERE node_id = id_from_vos_path(?)";

        // Calculates also new path, even if it's usually generated by database functions
        String cteCopiedNodes = "SELECT nextval('node_node_id_seq') AS new_node_id,\n"
                + "((SELECT prefix FROM path_prefix) || currval('node_node_id_seq'))::ltree AS new_path,\n"
                + "path, relative_path, parent_path, parent_relative_path, ? AS name,\n"
                + "type, location_id, creator_id, group_write, group_read, is_public,\n"
                + "job_id, tstamp_wrapper_dir, format, async_trans, sticky, accept_views, provide_views, protocols, target\n"
                + "FROM node WHERE node_id = id_from_vos_path(?)\n"
                + "UNION ALL\n"
                + "SELECT nextval('node_node_id_seq') AS new_node_id,\n"
                + "(p.new_path::varchar || '.' || currval('node_node_id_seq'))::ltree,\n"
                + "n.path, n.relative_path, n.parent_path, n.parent_relative_path, n.name,\n"
                + "n.type, n.location_id, n.creator_id, n.group_write, n.group_read, n.is_public,\n"
                + "n.job_id, n.tstamp_wrapper_dir, n.format, n.async_trans, n.sticky, n.accept_views, n.provide_views, n.protocols, n.target\n"
                + "FROM node n\n"
                + "JOIN copied_nodes p ON p.path = n.parent_path";

        String cteCopiedNodesPaths = "SELECT subpath(new_path, 0, nlevel(new_path) - 1) AS new_parent_path,\n"
                + "nlevel(parent_path) - nlevel(parent_relative_path) AS rel_offset, * FROM copied_nodes";

        String parentSelect = "SELECT\n"
                + "new_node_id, new_parent_path,\n"
                + "CASE WHEN nlevel(new_parent_path) <= rel_offset THEN ''::ltree ELSE subpath(new_parent_path, rel_offset) END new_parent_relative_path,\n"
                + "name, type, location_id, creator_id, group_write, group_read, is_public,\n"
                + "job_id, tstamp_wrapper_dir, format, async_trans, sticky, accept_views, provide_views, protocols, target\n"
                + "FROM copied_nodes_paths\n";

        String sql = parentInsert
                + "WITH RECURSIVE path_prefix AS ("
                + ctePathPrefix + "),\n"
                + "copied_nodes AS ("
                + cteCopiedNodes + "),\n"
                + "copied_nodes_paths AS ("
                + cteCopiedNodesPaths + ")\n"
                + parentSelect;

        jdbcTemplate.update(conn -> {
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, destVosParentPath);
            ps.setString(2, destName);
            ps.setString(3, sourceVosPath);
            return ps;
        });
    }

    public boolean isBranchBusy(long parentNodeId) {

        String sql = "SELECT COUNT(c.node_id) > 0 "
                + "FROM node n "
                + "JOIN node c ON c.path <@ n.path "
                + "WHERE n.node_id = ? AND c.job_id IS NOT NULL";

        return jdbcTemplate.queryForObject(sql, new Object[]{parentNodeId}, new int[]{Types.BIGINT}, Boolean.class);
    }

    public void setBranchJobId(Long rootNodeId, String jobId) {
        String sql = "UPDATE node c SET job_id = ? "
                + "FROM node r "
                + "WHERE r.node_id = ? "
                + "AND r.path @> c.path";

        jdbcTemplate.update(conn -> {
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, jobId);
            ps.setLong(2, rootNodeId);
            return ps;
        });
    }

    public void releaseBusyNodesByJobId(String jobId) {
        String sql = "UPDATE node SET job_id = NULL WHERE job_id = ?";

        jdbcTemplate.update(conn -> {
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, jobId);
            return ps;
        });
    }

    public boolean isBranchWritable(long parentNodeId, String userId, List<String> userGroups) {

        String sql = "SELECT COUNT(c.node_id) = 0 "
                + "FROM node n "
                + "JOIN node c ON c.path <@ n.path "
                + "LEFT JOIN location loc ON c.location_id = loc.location_id "
                + "WHERE n.node_id = ? "
                + "AND (c.async_trans OR c.sticky OR location_type = 'async' OR "
                + "((SELECT COUNT(*) FROM (SELECT UNNEST(?) INTERSECT SELECT UNNEST(c.group_write)) AS allowed_groups) = 0 "
                + "AND c.creator_id <> ?))";

        return jdbcTemplate.query(sql, ps -> {
            ps.setLong(1, parentNodeId);

            String[] groups;
            if (userGroups == null) {
                groups = new String[0];
            } else {
                groups = userGroups.toArray(String[]::new);
            }
            ps.setArray(2, ps.getConnection().createArrayOf("varchar", groups));

            ps.setString(3, userId);
        }, row -> {
            if (!row.next()) {
                throw new IllegalStateException("Expected one result");
            }
            return row.getBoolean(1);
        });
    }

    public boolean isBranchReadable(long parentNodeId, String userId, List<String> userGroups) {

        String sql = "SELECT COUNT(c.node_id) = 0 "
                + "FROM node n "
                + "JOIN node c ON c.path <@ n.path "
                + "WHERE n.node_id = ? AND "
                + "NOT COALESCE(c.is_public, FALSE) "
                + "AND (SELECT COUNT(*) FROM (SELECT UNNEST(?) INTERSECT SELECT UNNEST(c.group_read)) AS allowed_groups) = 0 "
                + "AND c.creator_id <> ?";

        return jdbcTemplate.query(sql, ps -> {
            ps.setLong(1, parentNodeId);

            String[] groups;
            if (userGroups == null) {
                groups = new String[0];
            } else {
                groups = userGroups.toArray(String[]::new);
            }
            ps.setArray(2, ps.getConnection().createArrayOf("varchar", groups));

            ps.setString(3, userId);
        }, row -> {
            if (!row.next()) {
                throw new IllegalStateException("Expected one result");
            }
            return row.getBoolean(1);
        });
    }

    public void deleteNode(String path) {
        int nodesWithPath = countNodesWithPath(path);
        if (nodesWithPath == 0) {
            throw new IllegalStateException("Node at path "
                    + path + " not found");
        }

        if (nodesWithPath > 1) {
            throw new IllegalStateException("Multiple nodes at path " + path);
        }

        String insertSql = "INSERT INTO deleted_node "
                + "(node_id, parent_path, parent_relative_path, "
                + "name, os_name, tstamp_wrapper_dir, type, location_id, format, "
                + "async_trans, job_id, creator_id, group_read, "
                + "group_write, is_public, quota, content_type, content_encoding, "
                + "content_length, content_md5, created_on, last_modified, "
                + "accept_views, provide_views, protocols, target, sticky)\n";

        String deleteSql = "DELETE \n"
                + "FROM node n\n"
                + "USING node p\n"
                + "WHERE n.path <@ p.path AND p.node_id = id_from_vos_path(?)\n"
                + "RETURNING\n"
                + "n.node_id, n.parent_path, n.parent_relative_path, "
                + "n.name, n.os_name, n.tstamp_wrapper_dir, n.type, n.location_id, n.format, "
                + "n.async_trans, n.job_id, n.creator_id, n.group_read, "
                + "n.group_write, n.is_public, n.quota, n.content_type, n.content_encoding, "
                + "n.content_length, n.content_md5, n.created_on, n.last_modified, "
                + "n.accept_views, n.provide_views, n.protocols, n.target, n.sticky\n";

        String withSql = "WITH del AS (" + deleteSql + ")";

        String sql = withSql + insertSql + "SELECT * FROM del\n";

        jdbcTemplate.update(sql, path);
    }

    // utility method for deleteNode
    public int countNodesWithPath(String path) {
        String sql = "SELECT COUNT(*) FROM node WHERE node_id = id_from_vos_path(?)";

        Object[] args = {path};
        int[] types = {Types.VARCHAR};

        return jdbcTemplate.queryForObject(sql, args, types, Integer.class);
    }

    public String getNodeOsName(String vosPath) {
        String sql = "SELECT \n"
                + "COALESCE(os_name, name) AS os_name\n"
                + "FROM node n\n"
                + "WHERE node_id = id_from_vos_path(?)";

        Object[] args = {vosPath};
        int[] types = {Types.VARCHAR};

        return jdbcTemplate.queryForObject(sql, args, types, String.class);
    }

    public void setNodeLocation(String vosPath, int locationId, String nodeOsName) {

        String sql = "UPDATE node SET location_id = ?, os_name = ? WHERE node_id = id_from_vos_path(?)";

        int updated = jdbcTemplate.update(sql, ps -> {
            ps.setInt(1, locationId);
            ps.setString(2, nodeOsName);
            ps.setString(3, vosPath);
        });

        if (updated != 1) {
            throw new InternalFaultException("Unable to set node location for path " + vosPath);
        }
    }

    private String getGroupsString(ResultSet rs, String column) throws SQLException {
        Array array = rs.getArray(column);
        if (array == null) {
            return null;
        }
        return String.join(" ", (String[]) array.getArray());
    }

    // If value is null does nothing
    private void addProperty(String uri, String value, List<Property> list) {
        if (value != null) {
            Property prop = new Property();
            prop.setUri(uri);
            prop.setValue(value);
            list.add(prop);
        }
    }

    private String getUri(String path) {
        return URIUtils.returnURIFromVosPath(path, authority);
    }

    private NodePaths getPathsFromResultSet(ResultSet rs) throws SQLException {
        NodePaths paths = new NodePaths(rs.getString("path"), rs.getString("relative_path"));
        return paths;
    }

    private Array fromPropertyToArray(PreparedStatement ps, String myProperty) throws SQLException {
        if (myProperty == null || myProperty.isBlank()) {
            return null;
        } else {
            return ps.getConnection().createArrayOf("varchar", myProperty.split(" "));
        }
    }

    private Array fromViewsToArray(PreparedStatement ps, Node node, Function<DataNode, List<View>> viewsExtractor) throws SQLException {
        if (node instanceof DataNode) {
            DataNode dataNode = (DataNode) node;
            List<View> views = viewsExtractor.apply(dataNode);
            if (views != null && !views.isEmpty()) {
                Object[] array = views.stream().map(v -> v.getUri()).toArray();
                return ps.getConnection().createArrayOf("varchar", array);
            }
        }
        return null;
    }

    private List<View> getViews(Array array) {
        if (array == null) {
            return null;
        }
        try {
            return Arrays.stream((String[]) array.getArray())
                    .map(uri -> {
                        View view = new View();
                        view.setUri(uri);
                        return view;
                    })
                    .collect(Collectors.toList());
        } catch (SQLException ex) {
            throw new RuntimeException(ex);
        }

    }

    /*
    Map contains:
    Key             column
    column name     value
    column name     value
    and value is a String containing comma separated groups having permissions
     */
    private Map<String, List<String>> getPermissionsFromDB(String vosPath) {

        String sql = "SELECT group_read, group_write "
                + "FROM node n WHERE node_id = id_from_vos_path(?)";

        return jdbcTemplate.query(sql, new Object[]{vosPath}, new int[]{Types.VARCHAR}, rs -> {
            if (!rs.next()) {
                throw new InternalFaultException("No records found for " + vosPath);
            }
            return Map.of(
                    "group_read", getArrayValue(rs, "group_read"),
                    "group_write", getArrayValue(rs, "group_write")
            );
        });
    }

    private List<String> getArrayValue(ResultSet rs, String key) throws SQLException {
        Array array = rs.getArray(key);
        if (array == null) {
            return new ArrayList<>();
        }
        return Arrays.asList((String[]) array.getArray());
    }

    private void updatePermissionsRecursively(Node newNode, String vosPath) {

        Map<String, List<String>> permissions = getPermissionsFromDB(vosPath);

        List<String> existingGroupReadList = permissions.get("group_read");
        List<String> existingGroupWriteList = permissions.get("group_write");

        List<String> newGroupReadList = NodeProperties.getNodePropertyAsListByURI(newNode, NodeProperties.GROUP_READ_URI);
        List<String> newGroupWriteList = NodeProperties.getNodePropertyAsListByURI(newNode, NodeProperties.GROUP_WRITE_URI);

        Set<String> existingGroupRead = new HashSet<>(existingGroupReadList);
        Set<String> existingGroupWrite = new HashSet<>(existingGroupWriteList);

        Set<String> newGroupRead = new HashSet<>(newGroupReadList);
        Set<String> newGroupWrite = new HashSet<>(newGroupWriteList);

        Set<String> groupReadToAdd = differenceBetweenSets(newGroupRead, existingGroupRead);
        Set<String> groupReadToRemove = differenceBetweenSets(existingGroupRead, newGroupRead);

        Set<String> groupWriteToAdd = differenceBetweenSets(newGroupWrite, existingGroupWrite);
        Set<String> groupWriteToRemove = differenceBetweenSets(existingGroupWrite, newGroupWrite);

        String sql = "UPDATE node c SET "
                + "group_read = update_array(c.group_read, ?, ?), "
                + "group_write = update_array(c.group_write, ?, ?), "
                + "is_public = ? "
                + "FROM node n "
                + "WHERE n.node_id = id_from_vos_path(?) AND c.path <@ n.path";

        jdbcTemplate.update(conn -> {
            PreparedStatement ps = conn.prepareStatement(sql);
            int i = 0;
            ps.setArray(++i, ps.getConnection().createArrayOf("varchar", groupReadToAdd.toArray()));
            ps.setArray(++i, ps.getConnection().createArrayOf("varchar", groupReadToRemove.toArray()));
            ps.setArray(++i, ps.getConnection().createArrayOf("varchar", groupWriteToAdd.toArray()));
            ps.setArray(++i, ps.getConnection().createArrayOf("varchar", groupWriteToRemove.toArray()));
            ps.setBoolean(++i, Boolean.valueOf(NodeProperties.getNodePropertyByURI(newNode, NodeProperties.PUBLIC_READ_URI)));
            ps.setString(++i, vosPath);
            return ps;
        });
    }

    // Returns the difference a minus b
    private Set<String> differenceBetweenSets(Set<String> a, Set<String> b) {

        Set<String> diff = new HashSet<>(a);
        diff.removeAll(b);

        return diff;
    }

    private List<NodePaths> getNodePathsFromDB(String path) {

        String parentPath = NodeUtils.getParentPath(path);

        String sql = "SELECT path, relative_path "
                + "FROM node n "
                + "WHERE node_id = id_from_vos_path(?)";

        List<NodePaths> paths = jdbcTemplate.query(conn -> {
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, parentPath);
            return ps;
        }, (row, index) -> {
            return getPathsFromResultSet(row);
        });

        return paths;
    }

    public class ShortNodeDescriptor {

        private final String nodeLtreePath;

        private final boolean container;
        private final boolean writable;
        private final boolean busy;
        private final boolean permissionDenied;
        private final boolean sticky;

        public ShortNodeDescriptor(String nodeLtreePath, boolean container, boolean writable, boolean busy, boolean permissionDenied, boolean sticky) {
            this.nodeLtreePath = nodeLtreePath;
            this.container = container;
            this.writable = writable;
            this.busy = busy;
            this.permissionDenied = permissionDenied;
            this.sticky = sticky;
        }

        public String getDestinationNodeLtreePath() {
            return nodeLtreePath;
        }

        public boolean isContainer() {
            return container;
        }

        public boolean isWritable() {
            return writable;
        }

        public boolean isBusy() {
            return busy;
        }

        public boolean isPermissionDenied() {
            return permissionDenied;
        }

        public boolean isSticky() {
            return sticky;
        }

    }

    private class NodePaths {

        private final String path;
        private final String relativePath;

        public NodePaths(String myPath, String myRelativePath) {

            this.path = myPath;
            this.relativePath = myRelativePath;
        }

        public String getPath() {
            return this.path;
        }

        public String getRelativePath() {
            return this.relativePath;
        }
    }
}