diff -r 4f912cd42876 -r 86b5d8a1662f src/main/java/de/uapcore/lightpit/dao/postgres/PGIssueDao.java --- a/src/main/java/de/uapcore/lightpit/dao/postgres/PGIssueDao.java Fri Nov 06 10:50:32 2020 +0100 +++ /dev/null Thu Jan 01 00:00:00 1970 +0000 @@ -1,306 +0,0 @@ -/* - * DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS HEADER. - * - * Copyright 2018 Mike Becker. All rights reserved. - * - * Redistribution and use in source and binary forms, with or without - * modification, are permitted provided that the following conditions are met: - * - * 1. Redistributions of source code must retain the above copyright - * notice, this list of conditions and the following disclaimer. - * - * 2. Redistributions in binary form must reproduce the above copyright - * notice, this list of conditions and the following disclaimer in the - * documentation and/or other materials provided with the distribution. - * - * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" - * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE - * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE - * ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT HOLDER OR CONTRIBUTORS BE - * LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR - * CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF - * SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS - * INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN - * CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) - * ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE - * POSSIBILITY OF SUCH DAMAGE. - * - */ -package de.uapcore.lightpit.dao.postgres; - -import de.uapcore.lightpit.dao.IssueDao; -import de.uapcore.lightpit.entities.*; - -import java.sql.*; -import java.util.ArrayList; -import java.util.List; -import java.util.Objects; -import java.util.Optional; - -import static de.uapcore.lightpit.dao.Functions.*; - -public final class PGIssueDao implements IssueDao { - - private final PreparedStatement insert, update, list, listForVersion, find; - private final PreparedStatement affectedVersions, resolvedVersions; - private final PreparedStatement clearAffected, clearResolved; - private final PreparedStatement insertAffected, insertResolved; - private final PreparedStatement insertComment, updateComment, listComments; - - public PGIssueDao(Connection connection) throws SQLException { - final var query = "select issueid, i.project, p.name as projectname, p.node as projectnode, "+ - "component, c.name as componentname, c.node as componentnode, " + - "status, category, subject, i.description, " + - "userid, username, givenname, lastname, mail, " + - "created, updated, eta " + - "from lpit_issue i " + - "join lpit_project p on i.project = projectid " + - "left join lpit_component c on component = c.id " + - "left join lpit_user on userid = assignee "; - - list = connection.prepareStatement(query + - "where i.project = ? and coalesce(component, -1) = coalesce(?, component, -1)"); - - listForVersion = connection.prepareStatement( - "with issue_version as ( "+ - "select issueid, versionid from lpit_issue_affected_version union "+ - "select issueid, versionid from lpit_issue_resolved_version) "+ - query + - "left join issue_version using (issueid) "+ - "where i.project = ? "+ - "and coalesce(versionid,-1) = ? and coalesce(component, -1) = coalesce(?, component, -1)" - ); - - find = connection.prepareStatement(query + "where issueid = ? "); - - insert = connection.prepareStatement( - "insert into lpit_issue (project, component, status, category, subject, description, assignee, eta) " + - "values (?, ?, ?::issue_status, ?::issue_category, ?, ?, ?, ?) returning issueid" - ); - update = connection.prepareStatement( - "update lpit_issue set " + - "updated = now(), component = ?, status = ?::issue_status, category = ?::issue_category, " + - "subject = ?, description = ?, assignee = ?, eta = ? where issueid = ?" - ); - - affectedVersions = connection.prepareStatement( - "select versionid, name, status, ordinal " + - "from lpit_version join lpit_issue_affected_version using (versionid) " + - "where issueid = ? " + - "order by ordinal, name" - ); - clearAffected = connection.prepareStatement("delete from lpit_issue_affected_version where issueid = ?"); - insertAffected = connection.prepareStatement("insert into lpit_issue_affected_version (issueid, versionid) values (?,?)"); - - resolvedVersions = connection.prepareStatement( - "select versionid, name, status, ordinal " + - "from lpit_version v join lpit_issue_resolved_version using (versionid) " + - "where issueid = ? " + - "order by ordinal, name" - ); - clearResolved = connection.prepareStatement("delete from lpit_issue_resolved_version where issueid = ?"); - insertResolved = connection.prepareStatement("insert into lpit_issue_resolved_version (issueid, versionid) values (?,?)"); - - insertComment = connection.prepareStatement( - "insert into lpit_issue_comment (issueid, comment, userid) values (?, ? ,?)" - ); - updateComment = connection.prepareStatement( - "update lpit_issue_comment set comment = ?, updated = now(), updatecount = updatecount+1 where commentid = ?" - ); - listComments = connection.prepareStatement( - "select * from lpit_issue_comment left join lpit_user using (userid) where issueid = ? order by created" - ); - } - - private Issue mapColumns(ResultSet result) throws SQLException { - final var project = new Project(result.getInt("project")); - project.setName(result.getString("projectname")); - project.setNode(result.getString("projectnode")); - var component = new Component(result.getInt("component")); - if (result.wasNull()) { - component = null; - } else { - component.setName(result.getString("componentname")); - component.setNode(result.getString("componentnode")); - } - final var issue = new Issue(result.getInt("issueid")); - issue.setProject(project); - issue.setComponent(component); - issue.setStatus(IssueStatus.valueOf(result.getString("status"))); - issue.setCategory(IssueCategory.valueOf(result.getString("category"))); - issue.setSubject(result.getString("subject")); - issue.setDescription(result.getString("description")); - issue.setAssignee(PGUserDao.mapColumns(result)); - issue.setCreated(result.getTimestamp("created")); - issue.setUpdated(result.getTimestamp("updated")); - issue.setEta(result.getDate("eta")); - return issue; - } - - private Version mapVersion(ResultSet result) throws SQLException { - final var version = new Version(result.getInt("versionid")); - version.setName(result.getString("name")); - version.setOrdinal(result.getInt("ordinal")); - version.setStatus(VersionStatus.valueOf(result.getString("status"))); - return version; - } - - private void updateVersionLists(Issue instance) throws SQLException { - clearAffected.setInt(1, instance.getId()); - clearResolved.setInt(1, instance.getId()); - insertAffected.setInt(1, instance.getId()); - insertResolved.setInt(1, instance.getId()); - clearAffected.executeUpdate(); - clearResolved.executeUpdate(); - for (Version v : instance.getAffectedVersions()) { - insertAffected.setInt(2, v.getId()); - insertAffected.executeUpdate(); - } - for (Version v : instance.getResolvedVersions()) { - insertResolved.setInt(2, v.getId()); - insertResolved.executeUpdate(); - } - } - - private int setData(PreparedStatement stmt, int column, Issue instance) throws SQLException { - setForeignKeyOrNull(stmt, ++column, instance.getComponent(), Component::getId); - stmt.setString(++column, instance.getStatus().name()); - stmt.setString(++column, instance.getCategory().name()); - stmt.setString(++column, instance.getSubject()); - setStringOrNull(stmt, ++column, instance.getDescription()); - setForeignKeyOrNull(stmt, ++column, instance.getAssignee(), User::getId); - setDateOrNull(stmt, ++column, instance.getEta()); - return column; - } - - @Override - public void save(Issue instance, Project project) throws SQLException { - Objects.requireNonNull(instance.getSubject()); - instance.setProject(project); - int column = 0; - insert.setInt(++column, instance.getProject().getId()); - setData(insert, column, instance); - // insert and retrieve the ID - final var rs = insert.executeQuery(); - rs.next(); - instance.setId(rs.getInt(1)); - updateVersionLists(instance); - } - - @Override - public boolean update(Issue instance) throws SQLException { - if (instance.getId() < 0) return false; - Objects.requireNonNull(instance.getSubject()); - int column = setData(update, 0, instance); - update.setInt(++column, instance.getId()); - boolean success = update.executeUpdate() > 0; - if (success) { - updateVersionLists(instance); - return true; - } else { - return false; - } - } - - private List executeQuery(PreparedStatement query) throws SQLException { - List issues = new ArrayList<>(); - try (var result = query.executeQuery()) { - while (result.next()) { - issues.add(mapColumns(result)); - } - } - return issues; - } - - @Override - public List list(Project project) throws SQLException { - list.setInt(1, project.getId()); - list.setNull(2, Types.INTEGER); - return executeQuery(list); - } - - @Override - public List list(Project project, Component component, Version version) throws SQLException { - listForVersion.setInt(1, project.getId()); - listForVersion.setInt(2, Optional.ofNullable(version).map(Version::getId).orElse(-1)); - listForVersion.setInt(3, Optional.ofNullable(component).map(Component::getId).orElse(-1)); - return executeQuery(listForVersion); - } - - @Override - public List list(Project project, Version version) throws SQLException { - listForVersion.setInt(1, project.getId()); - listForVersion.setInt(2, Optional.ofNullable(version).map(Version::getId).orElse(-1)); - listForVersion.setNull(3, Types.INTEGER); - return executeQuery(listForVersion); - } - - @Override - public List list(Project project, Component component) throws SQLException { - list.setInt(1, project.getId()); - list.setInt(2, Optional.ofNullable(component).map(Component::getId).orElse(-1)); - return executeQuery(list); - } - - @Override - public Issue find(int id) throws SQLException { - find.setInt(1, id); - try (var result = find.executeQuery()) { - if (result.next()) { - return mapColumns(result); - } else { - return null; - } - } - } - - private List listVersions(PreparedStatement stmt, Issue issue) throws SQLException { - stmt.setInt(1, issue.getId()); - List versions = new ArrayList<>(); - try (var result = stmt.executeQuery()) { - while (result.next()) { - versions.add(mapVersion(result)); - } - } - return versions; - } - - @Override - public void joinVersionInformation(Issue issue) throws SQLException { - Objects.requireNonNull(issue.getProject()); - issue.setAffectedVersions(listVersions(affectedVersions, issue)); - issue.setResolvedVersions(listVersions(resolvedVersions, issue)); - } - - @Override - public List listComments(Issue issue) throws SQLException { - listComments.setInt(1, issue.getId()); - List comments = new ArrayList<>(); - try (var result = listComments.executeQuery()) { - while (result.next()) { - final var comment = new IssueComment(result.getInt("commentid")); - comment.setCreated(result.getTimestamp("created")); - comment.setUpdated(result.getTimestamp("updated")); - comment.setUpdateCount(result.getInt("updatecount")); - comment.setComment(result.getString("comment")); - comment.setAuthor(PGUserDao.mapColumns(result)); - comments.add(comment); - } - } - return comments; - } - - @Override - public void saveComment(Issue issue, IssueComment comment) throws SQLException { - if (comment.getId() >= 0) { - updateComment.setString(1, comment.getComment()); - updateComment.setInt(2, comment.getId()); - updateComment.execute(); - } else { - insertComment.setInt(1, issue.getId()); - insertComment.setString(2, comment.getComment()); - setForeignKeyOrNull(insertComment, 3, comment.getAuthor(), User::getId); - insertComment.execute(); - } - } -}