1.1 --- a/src/main/java/de/uapcore/lightpit/dao/postgres/PGIssueDao.java Fri Nov 06 10:50:32 2020 +0100
1.2 +++ /dev/null Thu Jan 01 00:00:00 1970 +0000
1.3 @@ -1,306 +0,0 @@
1.4 -/*
1.5 - * DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS HEADER.
1.6 - *
1.7 - * Copyright 2018 Mike Becker. All rights reserved.
1.8 - *
1.9 - * Redistribution and use in source and binary forms, with or without
1.10 - * modification, are permitted provided that the following conditions are met:
1.11 - *
1.12 - * 1. Redistributions of source code must retain the above copyright
1.13 - * notice, this list of conditions and the following disclaimer.
1.14 - *
1.15 - * 2. Redistributions in binary form must reproduce the above copyright
1.16 - * notice, this list of conditions and the following disclaimer in the
1.17 - * documentation and/or other materials provided with the distribution.
1.18 - *
1.19 - * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
1.20 - * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
1.21 - * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
1.22 - * ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT HOLDER OR CONTRIBUTORS BE
1.23 - * LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR
1.24 - * CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF
1.25 - * SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS
1.26 - * INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN
1.27 - * CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE)
1.28 - * ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE
1.29 - * POSSIBILITY OF SUCH DAMAGE.
1.30 - *
1.31 - */
1.32 -package de.uapcore.lightpit.dao.postgres;
1.33 -
1.34 -import de.uapcore.lightpit.dao.IssueDao;
1.35 -import de.uapcore.lightpit.entities.*;
1.36 -
1.37 -import java.sql.*;
1.38 -import java.util.ArrayList;
1.39 -import java.util.List;
1.40 -import java.util.Objects;
1.41 -import java.util.Optional;
1.42 -
1.43 -import static de.uapcore.lightpit.dao.Functions.*;
1.44 -
1.45 -public final class PGIssueDao implements IssueDao {
1.46 -
1.47 - private final PreparedStatement insert, update, list, listForVersion, find;
1.48 - private final PreparedStatement affectedVersions, resolvedVersions;
1.49 - private final PreparedStatement clearAffected, clearResolved;
1.50 - private final PreparedStatement insertAffected, insertResolved;
1.51 - private final PreparedStatement insertComment, updateComment, listComments;
1.52 -
1.53 - public PGIssueDao(Connection connection) throws SQLException {
1.54 - final var query = "select issueid, i.project, p.name as projectname, p.node as projectnode, "+
1.55 - "component, c.name as componentname, c.node as componentnode, " +
1.56 - "status, category, subject, i.description, " +
1.57 - "userid, username, givenname, lastname, mail, " +
1.58 - "created, updated, eta " +
1.59 - "from lpit_issue i " +
1.60 - "join lpit_project p on i.project = projectid " +
1.61 - "left join lpit_component c on component = c.id " +
1.62 - "left join lpit_user on userid = assignee ";
1.63 -
1.64 - list = connection.prepareStatement(query +
1.65 - "where i.project = ? and coalesce(component, -1) = coalesce(?, component, -1)");
1.66 -
1.67 - listForVersion = connection.prepareStatement(
1.68 - "with issue_version as ( "+
1.69 - "select issueid, versionid from lpit_issue_affected_version union "+
1.70 - "select issueid, versionid from lpit_issue_resolved_version) "+
1.71 - query +
1.72 - "left join issue_version using (issueid) "+
1.73 - "where i.project = ? "+
1.74 - "and coalesce(versionid,-1) = ? and coalesce(component, -1) = coalesce(?, component, -1)"
1.75 - );
1.76 -
1.77 - find = connection.prepareStatement(query + "where issueid = ? ");
1.78 -
1.79 - insert = connection.prepareStatement(
1.80 - "insert into lpit_issue (project, component, status, category, subject, description, assignee, eta) " +
1.81 - "values (?, ?, ?::issue_status, ?::issue_category, ?, ?, ?, ?) returning issueid"
1.82 - );
1.83 - update = connection.prepareStatement(
1.84 - "update lpit_issue set " +
1.85 - "updated = now(), component = ?, status = ?::issue_status, category = ?::issue_category, " +
1.86 - "subject = ?, description = ?, assignee = ?, eta = ? where issueid = ?"
1.87 - );
1.88 -
1.89 - affectedVersions = connection.prepareStatement(
1.90 - "select versionid, name, status, ordinal " +
1.91 - "from lpit_version join lpit_issue_affected_version using (versionid) " +
1.92 - "where issueid = ? " +
1.93 - "order by ordinal, name"
1.94 - );
1.95 - clearAffected = connection.prepareStatement("delete from lpit_issue_affected_version where issueid = ?");
1.96 - insertAffected = connection.prepareStatement("insert into lpit_issue_affected_version (issueid, versionid) values (?,?)");
1.97 -
1.98 - resolvedVersions = connection.prepareStatement(
1.99 - "select versionid, name, status, ordinal " +
1.100 - "from lpit_version v join lpit_issue_resolved_version using (versionid) " +
1.101 - "where issueid = ? " +
1.102 - "order by ordinal, name"
1.103 - );
1.104 - clearResolved = connection.prepareStatement("delete from lpit_issue_resolved_version where issueid = ?");
1.105 - insertResolved = connection.prepareStatement("insert into lpit_issue_resolved_version (issueid, versionid) values (?,?)");
1.106 -
1.107 - insertComment = connection.prepareStatement(
1.108 - "insert into lpit_issue_comment (issueid, comment, userid) values (?, ? ,?)"
1.109 - );
1.110 - updateComment = connection.prepareStatement(
1.111 - "update lpit_issue_comment set comment = ?, updated = now(), updatecount = updatecount+1 where commentid = ?"
1.112 - );
1.113 - listComments = connection.prepareStatement(
1.114 - "select * from lpit_issue_comment left join lpit_user using (userid) where issueid = ? order by created"
1.115 - );
1.116 - }
1.117 -
1.118 - private Issue mapColumns(ResultSet result) throws SQLException {
1.119 - final var project = new Project(result.getInt("project"));
1.120 - project.setName(result.getString("projectname"));
1.121 - project.setNode(result.getString("projectnode"));
1.122 - var component = new Component(result.getInt("component"));
1.123 - if (result.wasNull()) {
1.124 - component = null;
1.125 - } else {
1.126 - component.setName(result.getString("componentname"));
1.127 - component.setNode(result.getString("componentnode"));
1.128 - }
1.129 - final var issue = new Issue(result.getInt("issueid"));
1.130 - issue.setProject(project);
1.131 - issue.setComponent(component);
1.132 - issue.setStatus(IssueStatus.valueOf(result.getString("status")));
1.133 - issue.setCategory(IssueCategory.valueOf(result.getString("category")));
1.134 - issue.setSubject(result.getString("subject"));
1.135 - issue.setDescription(result.getString("description"));
1.136 - issue.setAssignee(PGUserDao.mapColumns(result));
1.137 - issue.setCreated(result.getTimestamp("created"));
1.138 - issue.setUpdated(result.getTimestamp("updated"));
1.139 - issue.setEta(result.getDate("eta"));
1.140 - return issue;
1.141 - }
1.142 -
1.143 - private Version mapVersion(ResultSet result) throws SQLException {
1.144 - final var version = new Version(result.getInt("versionid"));
1.145 - version.setName(result.getString("name"));
1.146 - version.setOrdinal(result.getInt("ordinal"));
1.147 - version.setStatus(VersionStatus.valueOf(result.getString("status")));
1.148 - return version;
1.149 - }
1.150 -
1.151 - private void updateVersionLists(Issue instance) throws SQLException {
1.152 - clearAffected.setInt(1, instance.getId());
1.153 - clearResolved.setInt(1, instance.getId());
1.154 - insertAffected.setInt(1, instance.getId());
1.155 - insertResolved.setInt(1, instance.getId());
1.156 - clearAffected.executeUpdate();
1.157 - clearResolved.executeUpdate();
1.158 - for (Version v : instance.getAffectedVersions()) {
1.159 - insertAffected.setInt(2, v.getId());
1.160 - insertAffected.executeUpdate();
1.161 - }
1.162 - for (Version v : instance.getResolvedVersions()) {
1.163 - insertResolved.setInt(2, v.getId());
1.164 - insertResolved.executeUpdate();
1.165 - }
1.166 - }
1.167 -
1.168 - private int setData(PreparedStatement stmt, int column, Issue instance) throws SQLException {
1.169 - setForeignKeyOrNull(stmt, ++column, instance.getComponent(), Component::getId);
1.170 - stmt.setString(++column, instance.getStatus().name());
1.171 - stmt.setString(++column, instance.getCategory().name());
1.172 - stmt.setString(++column, instance.getSubject());
1.173 - setStringOrNull(stmt, ++column, instance.getDescription());
1.174 - setForeignKeyOrNull(stmt, ++column, instance.getAssignee(), User::getId);
1.175 - setDateOrNull(stmt, ++column, instance.getEta());
1.176 - return column;
1.177 - }
1.178 -
1.179 - @Override
1.180 - public void save(Issue instance, Project project) throws SQLException {
1.181 - Objects.requireNonNull(instance.getSubject());
1.182 - instance.setProject(project);
1.183 - int column = 0;
1.184 - insert.setInt(++column, instance.getProject().getId());
1.185 - setData(insert, column, instance);
1.186 - // insert and retrieve the ID
1.187 - final var rs = insert.executeQuery();
1.188 - rs.next();
1.189 - instance.setId(rs.getInt(1));
1.190 - updateVersionLists(instance);
1.191 - }
1.192 -
1.193 - @Override
1.194 - public boolean update(Issue instance) throws SQLException {
1.195 - if (instance.getId() < 0) return false;
1.196 - Objects.requireNonNull(instance.getSubject());
1.197 - int column = setData(update, 0, instance);
1.198 - update.setInt(++column, instance.getId());
1.199 - boolean success = update.executeUpdate() > 0;
1.200 - if (success) {
1.201 - updateVersionLists(instance);
1.202 - return true;
1.203 - } else {
1.204 - return false;
1.205 - }
1.206 - }
1.207 -
1.208 - private List<Issue> executeQuery(PreparedStatement query) throws SQLException {
1.209 - List<Issue> issues = new ArrayList<>();
1.210 - try (var result = query.executeQuery()) {
1.211 - while (result.next()) {
1.212 - issues.add(mapColumns(result));
1.213 - }
1.214 - }
1.215 - return issues;
1.216 - }
1.217 -
1.218 - @Override
1.219 - public List<Issue> list(Project project) throws SQLException {
1.220 - list.setInt(1, project.getId());
1.221 - list.setNull(2, Types.INTEGER);
1.222 - return executeQuery(list);
1.223 - }
1.224 -
1.225 - @Override
1.226 - public List<Issue> list(Project project, Component component, Version version) throws SQLException {
1.227 - listForVersion.setInt(1, project.getId());
1.228 - listForVersion.setInt(2, Optional.ofNullable(version).map(Version::getId).orElse(-1));
1.229 - listForVersion.setInt(3, Optional.ofNullable(component).map(Component::getId).orElse(-1));
1.230 - return executeQuery(listForVersion);
1.231 - }
1.232 -
1.233 - @Override
1.234 - public List<Issue> list(Project project, Version version) throws SQLException {
1.235 - listForVersion.setInt(1, project.getId());
1.236 - listForVersion.setInt(2, Optional.ofNullable(version).map(Version::getId).orElse(-1));
1.237 - listForVersion.setNull(3, Types.INTEGER);
1.238 - return executeQuery(listForVersion);
1.239 - }
1.240 -
1.241 - @Override
1.242 - public List<Issue> list(Project project, Component component) throws SQLException {
1.243 - list.setInt(1, project.getId());
1.244 - list.setInt(2, Optional.ofNullable(component).map(Component::getId).orElse(-1));
1.245 - return executeQuery(list);
1.246 - }
1.247 -
1.248 - @Override
1.249 - public Issue find(int id) throws SQLException {
1.250 - find.setInt(1, id);
1.251 - try (var result = find.executeQuery()) {
1.252 - if (result.next()) {
1.253 - return mapColumns(result);
1.254 - } else {
1.255 - return null;
1.256 - }
1.257 - }
1.258 - }
1.259 -
1.260 - private List<Version> listVersions(PreparedStatement stmt, Issue issue) throws SQLException {
1.261 - stmt.setInt(1, issue.getId());
1.262 - List<Version> versions = new ArrayList<>();
1.263 - try (var result = stmt.executeQuery()) {
1.264 - while (result.next()) {
1.265 - versions.add(mapVersion(result));
1.266 - }
1.267 - }
1.268 - return versions;
1.269 - }
1.270 -
1.271 - @Override
1.272 - public void joinVersionInformation(Issue issue) throws SQLException {
1.273 - Objects.requireNonNull(issue.getProject());
1.274 - issue.setAffectedVersions(listVersions(affectedVersions, issue));
1.275 - issue.setResolvedVersions(listVersions(resolvedVersions, issue));
1.276 - }
1.277 -
1.278 - @Override
1.279 - public List<IssueComment> listComments(Issue issue) throws SQLException {
1.280 - listComments.setInt(1, issue.getId());
1.281 - List<IssueComment> comments = new ArrayList<>();
1.282 - try (var result = listComments.executeQuery()) {
1.283 - while (result.next()) {
1.284 - final var comment = new IssueComment(result.getInt("commentid"));
1.285 - comment.setCreated(result.getTimestamp("created"));
1.286 - comment.setUpdated(result.getTimestamp("updated"));
1.287 - comment.setUpdateCount(result.getInt("updatecount"));
1.288 - comment.setComment(result.getString("comment"));
1.289 - comment.setAuthor(PGUserDao.mapColumns(result));
1.290 - comments.add(comment);
1.291 - }
1.292 - }
1.293 - return comments;
1.294 - }
1.295 -
1.296 - @Override
1.297 - public void saveComment(Issue issue, IssueComment comment) throws SQLException {
1.298 - if (comment.getId() >= 0) {
1.299 - updateComment.setString(1, comment.getComment());
1.300 - updateComment.setInt(2, comment.getId());
1.301 - updateComment.execute();
1.302 - } else {
1.303 - insertComment.setInt(1, issue.getId());
1.304 - insertComment.setString(2, comment.getComment());
1.305 - setForeignKeyOrNull(insertComment, 3, comment.getAuthor(), User::getId);
1.306 - insertComment.execute();
1.307 - }
1.308 - }
1.309 -}
