GroupsDAO.java

package it.inaf.ia2.gms.persistence;

import it.inaf.ia2.gms.model.GroupBreadcrumb;
import it.inaf.ia2.gms.persistence.model.GroupEntity;
import it.inaf.ia2.gms.service.hook.GroupsHook;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Optional;
import java.util.Set;
import java.util.stream.Collectors;
import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

@Component
public class GroupsDAO {

    @Autowired(required = false)
    protected GroupsHook groupsHook;

    private final JdbcTemplate jdbcTemplate;

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

    public GroupEntity createGroup(GroupEntity group) {

        if (groupsHook != null) {
            groupsHook.beforeCreate(group);
        }

        String sql = "INSERT INTO gms_group (id, name, path, is_leaf) VALUES (?, ?, ?, ?)";

        jdbcTemplate.update(conn -> {
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, group.getId());
            ps.setString(2, group.getName());
            ps.setObject(3, group.getPath(), Types.OTHER);
            ps.setBoolean(4, group.isLeaf());
            return ps;
        });

        return group;
    }

    public GroupEntity updateGroup(GroupEntity group) {

        if (groupsHook != null) {
            groupsHook.beforeUpdate(group);
        }

        String sql = "UPDATE gms_group SET name = ?, path = ?, is_leaf = ? WHERE id = ?";

        jdbcTemplate.update(conn -> {
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, group.getName());
            ps.setObject(2, group.getPath(), Types.OTHER);
            ps.setBoolean(3, group.isLeaf());
            ps.setString(4, group.getId());
            return ps;
        });

        return group;
    }

    public void deleteGroup(GroupEntity group) {

        if (groupsHook != null) {
            groupsHook.beforeDelete(group);
        }

        String sql = "DELETE FROM gms_group WHERE id = ?";
        jdbcTemplate.update(sql, group.getId());
    }

    public Optional<GroupEntity> findGroupById(String groupId) {

        String sql = "SELECT id, name, path, is_leaf from gms_group WHERE id = ?";

        return jdbcTemplate.query(conn -> {
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, groupId);
            return ps;
        }, resultSet -> {
            if (resultSet.next()) {
                GroupEntity group = new GroupEntity();
                group.setId(resultSet.getString("id"));
                group.setName(resultSet.getString("name"));
                group.setPath(resultSet.getString("path"));
                group.setLeaf(resultSet.getBoolean("is_leaf"));
                return Optional.of(group);
            }
            return Optional.empty();
        });
    }

    public Optional<GroupEntity> findGroupByPath(String path) {

        String sql = "SELECT id, name, is_leaf from gms_group WHERE path = ?";

        return jdbcTemplate.query(conn -> {
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setObject(1, path, Types.OTHER);
            return ps;
        }, resultSet -> {
            if (resultSet.next()) {
                GroupEntity group = new GroupEntity();
                group.setId(resultSet.getString("id"));
                group.setName(resultSet.getString("name"));
                group.setLeaf(resultSet.getBoolean("is_leaf"));
                group.setPath(path);
                return Optional.of(group);
            }
            return Optional.empty();
        });
    }

    public List<GroupEntity> findGroupsByIds(Set<String> identifiers) {

        if (identifiers.isEmpty()) {
            return new ArrayList<>();
        }

        return jdbcTemplate.query(conn -> {

            String sql = "SELECT id, name, path, is_leaf from gms_group WHERE id IN (";
            sql += String.join(",", identifiers.stream().map(p -> "?").collect(Collectors.toList()));
            sql += ")";

            PreparedStatement ps = conn.prepareStatement(sql);
            int i = 0;
            for (String id : identifiers) {
                ps.setString(++i, id);
            }
            return ps;
        }, resultSet -> {
            List<GroupEntity> groups = new ArrayList<>();
            while (resultSet.next()) {
                GroupEntity group = new GroupEntity();
                group.setId(resultSet.getString("id"));
                group.setName(resultSet.getString("name"));
                group.setPath(resultSet.getString("path"));
                group.setLeaf(resultSet.getBoolean("is_leaf"));
                groups.add(group);
            }
            return groups;
        });
    }

    public Optional<GroupEntity> findGroupByParentAndName(String parentPath, String childName) {

        String sql = "SELECT id, path, is_leaf from gms_group WHERE name = ? AND path ~ ?";

        return jdbcTemplate.query(conn -> {
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, childName);
            ps.setObject(2, getSubGroupsPath(parentPath), Types.OTHER);
            return ps;
        }, resultSet -> {
            if (resultSet.next()) {
                GroupEntity group = new GroupEntity();
                group.setId(resultSet.getString("id"));
                group.setName(childName);
                group.setPath(resultSet.getString("path"));
                group.setLeaf(resultSet.getBoolean("is_leaf"));
                return Optional.of(group);
            }
            return Optional.empty();
        });
    }

    public List<GroupEntity> getDirectSubGroups(String path) {
        return getDirectSubGroups(path, null);
    }

    /**
     * Returns the direct children (one level).
     */
    public List<GroupEntity> getDirectSubGroups(String path, String searchFilter) {

        boolean hasSearchFilter = searchFilter != null && !searchFilter.trim().isEmpty();

        String sql;
        if (hasSearchFilter) {
            sql = "SELECT id, name, path, is_leaf FROM gms_group WHERE path ~ ? AND name ILIKE ? ORDER BY name";
        } else {
            sql = "SELECT id, name, path, is_leaf FROM gms_group WHERE path ~ ? ORDER BY name";
        }

        return jdbcTemplate.query(conn -> {
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setObject(1, getSubGroupsPath(path), Types.OTHER);
            if (hasSearchFilter) {
                ps.setObject(2, "%" + searchFilter + "%");
            }
            return ps;
        }, resultSet -> {
            return getGroupsFromResultSet(resultSet);
        });
    }

    private String getSubGroupsPath(String path) {
        if (!path.isEmpty()) {
            path += ".";
        }
        path += "*{1}";
        return path;
    }

    public List<GroupEntity> getAllChildren(String path) {

        String sql = "SELECT id, name, path, is_leaf FROM gms_group WHERE path <@ ? AND path <> ? ORDER BY nlevel(path) DESC";

        return jdbcTemplate.query(conn -> {
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setObject(1, path, Types.OTHER);
            ps.setObject(2, path, Types.OTHER);
            return ps;
        }, resultSet -> {
            return getGroupsFromResultSet(resultSet);
        });
    }

    public List<GroupEntity> findGroupsByNames(List<String> names) {

        String sql = "SELECT id, name, path, is_leaf from gms_group WHERE name IN ("
                + String.join(",", names.stream().map(g -> "?").collect(Collectors.toList()))
                + ")";

        return jdbcTemplate.query(conn -> {
            PreparedStatement ps = conn.prepareStatement(sql);
            int i = 0;
            for (String name : names) {
                ps.setString(++i, name);
            }
            return ps;
        }, resultSet -> {
            return getGroupsFromResultSet(resultSet);
        });
    }

    private List<GroupEntity> getGroupsFromResultSet(ResultSet resultSet) throws SQLException {
        List<GroupEntity> groups = new ArrayList<>();
        while (resultSet.next()) {
            GroupEntity group = new GroupEntity();
            group.setId(resultSet.getString("id"));
            group.setName(resultSet.getString("name"));
            group.setPath(resultSet.getString("path"));
            group.setLeaf(resultSet.getBoolean("is_leaf"));
            groups.add(group);
        }
        return groups;
    }

    public Map<String, Boolean> getHasChildrenMap(Set<String> groupIds) {

        if (groupIds.isEmpty()) {
            return new HashMap<>();
        }

        String sql = "SELECT g.id, COUNT(s.*) > 0 AS has_children \n"
                + "FROM gms_group g\n"
                + "LEFT JOIN gms_group s ON s.path <@ g.path AND s.path <> g.path\n"
                + "WHERE g.id IN("
                + String.join(",", groupIds.stream().map(g -> "?").collect(Collectors.toList()))
                + ") GROUP BY g.id";

        return jdbcTemplate.query(conn -> {
            PreparedStatement ps = conn.prepareStatement(sql);
            int i = 0;
            for (String groupId : groupIds) {
                ps.setString(++i, groupId);
            }

            return ps;
        }, resultSet -> {
            Map<String, Boolean> map = new HashMap<>();
            while (resultSet.next()) {
                map.put(resultSet.getString("id"), resultSet.getBoolean("has_children"));
            }
            return map;
        });
    }

    /**
     * Returns the number of groups.
     */
    public long count() {

        String sql = "SELECT COUNT(*) FROM gms_group";

        return jdbcTemplate.query(sql, resultSet -> {
            resultSet.next();
            return resultSet.getLong(1);
        });
    }

    public List<GroupBreadcrumb> getBreadcrumbs(String path) {

        String sql = "SELECT id, name FROM gms_group WHERE path @> ? ORDER BY length(path::varchar) ASC";

        return jdbcTemplate.query(conn -> {
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setObject(1, path, Types.OTHER);
            return ps;
        }, resultSet -> {
            List<GroupBreadcrumb> breadcrumbs = new ArrayList<>();
            while (resultSet.next()) {
                GroupBreadcrumb bc = new GroupBreadcrumb();
                bc.setGroupId(resultSet.getString("id"));
                bc.setGroupName(resultSet.getString("name"));
                breadcrumbs.add(bc);
            }
            return breadcrumbs;
        });
    }

    public List<GroupEntity> searchGroups(String searchText) {

        String sql = "SELECT id, name, path, is_leaf from gms_group WHERE name ILIKE ?";

        return jdbcTemplate.query(conn -> {
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, "%" + searchText + "%");
            return ps;
        }, resultSet -> {
            return getGroupsFromResultSet(resultSet);
        });
    }
}