comparison: src/main/java/de/uapcore/lightpit/dao/postgres/PGIssueDao.java
src/main/java/de/uapcore/lightpit/dao/postgres/PGIssueDao.java
- changeset 159
- 86b5d8a1662f
- parent 154
- 3d10f2a390a1
equal
deleted
inserted
replaced
| 158:4f912cd42876 | 159:86b5d8a1662f |
|---|---|
| 1 /* | |
| 2 * DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS HEADER. | |
| 3 * | |
| 4 * Copyright 2018 Mike Becker. All rights reserved. | |
| 5 * | |
| 6 * Redistribution and use in source and binary forms, with or without | |
| 7 * modification, are permitted provided that the following conditions are met: | |
| 8 * | |
| 9 * 1. Redistributions of source code must retain the above copyright | |
| 10 * notice, this list of conditions and the following disclaimer. | |
| 11 * | |
| 12 * 2. Redistributions in binary form must reproduce the above copyright | |
| 13 * notice, this list of conditions and the following disclaimer in the | |
| 14 * documentation and/or other materials provided with the distribution. | |
| 15 * | |
| 16 * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" | |
| 17 * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE | |
| 18 * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE | |
| 19 * ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT HOLDER OR CONTRIBUTORS BE | |
| 20 * LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR | |
| 21 * CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF | |
| 22 * SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS | |
| 23 * INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN | |
| 24 * CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) | |
| 25 * ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE | |
| 26 * POSSIBILITY OF SUCH DAMAGE. | |
| 27 * | |
| 28 */ | |
| 29 package de.uapcore.lightpit.dao.postgres; | |
| 30 | |
| 31 import de.uapcore.lightpit.dao.IssueDao; | |
| 32 import de.uapcore.lightpit.entities.*; | |
| 33 | |
| 34 import java.sql.*; | |
| 35 import java.util.ArrayList; | |
| 36 import java.util.List; | |
| 37 import java.util.Objects; | |
| 38 import java.util.Optional; | |
| 39 | |
| 40 import static de.uapcore.lightpit.dao.Functions.*; | |
| 41 | |
| 42 public final class PGIssueDao implements IssueDao { | |
| 43 | |
| 44 private final PreparedStatement insert, update, list, listForVersion, find; | |
| 45 private final PreparedStatement affectedVersions, resolvedVersions; | |
| 46 private final PreparedStatement clearAffected, clearResolved; | |
| 47 private final PreparedStatement insertAffected, insertResolved; | |
| 48 private final PreparedStatement insertComment, updateComment, listComments; | |
| 49 | |
| 50 public PGIssueDao(Connection connection) throws SQLException { | |
| 51 final var query = "select issueid, i.project, p.name as projectname, p.node as projectnode, "+ | |
| 52 "component, c.name as componentname, c.node as componentnode, " + | |
| 53 "status, category, subject, i.description, " + | |
| 54 "userid, username, givenname, lastname, mail, " + | |
| 55 "created, updated, eta " + | |
| 56 "from lpit_issue i " + | |
| 57 "join lpit_project p on i.project = projectid " + | |
| 58 "left join lpit_component c on component = c.id " + | |
| 59 "left join lpit_user on userid = assignee "; | |
| 60 | |
| 61 list = connection.prepareStatement(query + | |
| 62 "where i.project = ? and coalesce(component, -1) = coalesce(?, component, -1)"); | |
| 63 | |
| 64 listForVersion = connection.prepareStatement( | |
| 65 "with issue_version as ( "+ | |
| 66 "select issueid, versionid from lpit_issue_affected_version union "+ | |
| 67 "select issueid, versionid from lpit_issue_resolved_version) "+ | |
| 68 query + | |
| 69 "left join issue_version using (issueid) "+ | |
| 70 "where i.project = ? "+ | |
| 71 "and coalesce(versionid,-1) = ? and coalesce(component, -1) = coalesce(?, component, -1)" | |
| 72 ); | |
| 73 | |
| 74 find = connection.prepareStatement(query + "where issueid = ? "); | |
| 75 | |
| 76 insert = connection.prepareStatement( | |
| 77 "insert into lpit_issue (project, component, status, category, subject, description, assignee, eta) " + | |
| 78 "values (?, ?, ?::issue_status, ?::issue_category, ?, ?, ?, ?) returning issueid" | |
| 79 ); | |
| 80 update = connection.prepareStatement( | |
| 81 "update lpit_issue set " + | |
| 82 "updated = now(), component = ?, status = ?::issue_status, category = ?::issue_category, " + | |
| 83 "subject = ?, description = ?, assignee = ?, eta = ? where issueid = ?" | |
| 84 ); | |
| 85 | |
| 86 affectedVersions = connection.prepareStatement( | |
| 87 "select versionid, name, status, ordinal " + | |
| 88 "from lpit_version join lpit_issue_affected_version using (versionid) " + | |
| 89 "where issueid = ? " + | |
| 90 "order by ordinal, name" | |
| 91 ); | |
| 92 clearAffected = connection.prepareStatement("delete from lpit_issue_affected_version where issueid = ?"); | |
| 93 insertAffected = connection.prepareStatement("insert into lpit_issue_affected_version (issueid, versionid) values (?,?)"); | |
| 94 | |
| 95 resolvedVersions = connection.prepareStatement( | |
| 96 "select versionid, name, status, ordinal " + | |
| 97 "from lpit_version v join lpit_issue_resolved_version using (versionid) " + | |
| 98 "where issueid = ? " + | |
| 99 "order by ordinal, name" | |
| 100 ); | |
| 101 clearResolved = connection.prepareStatement("delete from lpit_issue_resolved_version where issueid = ?"); | |
| 102 insertResolved = connection.prepareStatement("insert into lpit_issue_resolved_version (issueid, versionid) values (?,?)"); | |
| 103 | |
| 104 insertComment = connection.prepareStatement( | |
| 105 "insert into lpit_issue_comment (issueid, comment, userid) values (?, ? ,?)" | |
| 106 ); | |
| 107 updateComment = connection.prepareStatement( | |
| 108 "update lpit_issue_comment set comment = ?, updated = now(), updatecount = updatecount+1 where commentid = ?" | |
| 109 ); | |
| 110 listComments = connection.prepareStatement( | |
| 111 "select * from lpit_issue_comment left join lpit_user using (userid) where issueid = ? order by created" | |
| 112 ); | |
| 113 } | |
| 114 | |
| 115 private Issue mapColumns(ResultSet result) throws SQLException { | |
| 116 final var project = new Project(result.getInt("project")); | |
| 117 project.setName(result.getString("projectname")); | |
| 118 project.setNode(result.getString("projectnode")); | |
| 119 var component = new Component(result.getInt("component")); | |
| 120 if (result.wasNull()) { | |
| 121 component = null; | |
| 122 } else { | |
| 123 component.setName(result.getString("componentname")); | |
| 124 component.setNode(result.getString("componentnode")); | |
| 125 } | |
| 126 final var issue = new Issue(result.getInt("issueid")); | |
| 127 issue.setProject(project); | |
| 128 issue.setComponent(component); | |
| 129 issue.setStatus(IssueStatus.valueOf(result.getString("status"))); | |
| 130 issue.setCategory(IssueCategory.valueOf(result.getString("category"))); | |
| 131 issue.setSubject(result.getString("subject")); | |
| 132 issue.setDescription(result.getString("description")); | |
| 133 issue.setAssignee(PGUserDao.mapColumns(result)); | |
| 134 issue.setCreated(result.getTimestamp("created")); | |
| 135 issue.setUpdated(result.getTimestamp("updated")); | |
| 136 issue.setEta(result.getDate("eta")); | |
| 137 return issue; | |
| 138 } | |
| 139 | |
| 140 private Version mapVersion(ResultSet result) throws SQLException { | |
| 141 final var version = new Version(result.getInt("versionid")); | |
| 142 version.setName(result.getString("name")); | |
| 143 version.setOrdinal(result.getInt("ordinal")); | |
| 144 version.setStatus(VersionStatus.valueOf(result.getString("status"))); | |
| 145 return version; | |
| 146 } | |
| 147 | |
| 148 private void updateVersionLists(Issue instance) throws SQLException { | |
| 149 clearAffected.setInt(1, instance.getId()); | |
| 150 clearResolved.setInt(1, instance.getId()); | |
| 151 insertAffected.setInt(1, instance.getId()); | |
| 152 insertResolved.setInt(1, instance.getId()); | |
| 153 clearAffected.executeUpdate(); | |
| 154 clearResolved.executeUpdate(); | |
| 155 for (Version v : instance.getAffectedVersions()) { | |
| 156 insertAffected.setInt(2, v.getId()); | |
| 157 insertAffected.executeUpdate(); | |
| 158 } | |
| 159 for (Version v : instance.getResolvedVersions()) { | |
| 160 insertResolved.setInt(2, v.getId()); | |
| 161 insertResolved.executeUpdate(); | |
| 162 } | |
| 163 } | |
| 164 | |
| 165 private int setData(PreparedStatement stmt, int column, Issue instance) throws SQLException { | |
| 166 setForeignKeyOrNull(stmt, ++column, instance.getComponent(), Component::getId); | |
| 167 stmt.setString(++column, instance.getStatus().name()); | |
| 168 stmt.setString(++column, instance.getCategory().name()); | |
| 169 stmt.setString(++column, instance.getSubject()); | |
| 170 setStringOrNull(stmt, ++column, instance.getDescription()); | |
| 171 setForeignKeyOrNull(stmt, ++column, instance.getAssignee(), User::getId); | |
| 172 setDateOrNull(stmt, ++column, instance.getEta()); | |
| 173 return column; | |
| 174 } | |
| 175 | |
| 176 @Override | |
| 177 public void save(Issue instance, Project project) throws SQLException { | |
| 178 Objects.requireNonNull(instance.getSubject()); | |
| 179 instance.setProject(project); | |
| 180 int column = 0; | |
| 181 insert.setInt(++column, instance.getProject().getId()); | |
| 182 setData(insert, column, instance); | |
| 183 // insert and retrieve the ID | |
| 184 final var rs = insert.executeQuery(); | |
| 185 rs.next(); | |
| 186 instance.setId(rs.getInt(1)); | |
| 187 updateVersionLists(instance); | |
| 188 } | |
| 189 | |
| 190 @Override | |
| 191 public boolean update(Issue instance) throws SQLException { | |
| 192 if (instance.getId() < 0) return false; | |
| 193 Objects.requireNonNull(instance.getSubject()); | |
| 194 int column = setData(update, 0, instance); | |
| 195 update.setInt(++column, instance.getId()); | |
| 196 boolean success = update.executeUpdate() > 0; | |
| 197 if (success) { | |
| 198 updateVersionLists(instance); | |
| 199 return true; | |
| 200 } else { | |
| 201 return false; | |
| 202 } | |
| 203 } | |
| 204 | |
| 205 private List<Issue> executeQuery(PreparedStatement query) throws SQLException { | |
| 206 List<Issue> issues = new ArrayList<>(); | |
| 207 try (var result = query.executeQuery()) { | |
| 208 while (result.next()) { | |
| 209 issues.add(mapColumns(result)); | |
| 210 } | |
| 211 } | |
| 212 return issues; | |
| 213 } | |
| 214 | |
| 215 @Override | |
| 216 public List<Issue> list(Project project) throws SQLException { | |
| 217 list.setInt(1, project.getId()); | |
| 218 list.setNull(2, Types.INTEGER); | |
| 219 return executeQuery(list); | |
| 220 } | |
| 221 | |
| 222 @Override | |
| 223 public List<Issue> list(Project project, Component component, Version version) throws SQLException { | |
| 224 listForVersion.setInt(1, project.getId()); | |
| 225 listForVersion.setInt(2, Optional.ofNullable(version).map(Version::getId).orElse(-1)); | |
| 226 listForVersion.setInt(3, Optional.ofNullable(component).map(Component::getId).orElse(-1)); | |
| 227 return executeQuery(listForVersion); | |
| 228 } | |
| 229 | |
| 230 @Override | |
| 231 public List<Issue> list(Project project, Version version) throws SQLException { | |
| 232 listForVersion.setInt(1, project.getId()); | |
| 233 listForVersion.setInt(2, Optional.ofNullable(version).map(Version::getId).orElse(-1)); | |
| 234 listForVersion.setNull(3, Types.INTEGER); | |
| 235 return executeQuery(listForVersion); | |
| 236 } | |
| 237 | |
| 238 @Override | |
| 239 public List<Issue> list(Project project, Component component) throws SQLException { | |
| 240 list.setInt(1, project.getId()); | |
| 241 list.setInt(2, Optional.ofNullable(component).map(Component::getId).orElse(-1)); | |
| 242 return executeQuery(list); | |
| 243 } | |
| 244 | |
| 245 @Override | |
| 246 public Issue find(int id) throws SQLException { | |
| 247 find.setInt(1, id); | |
| 248 try (var result = find.executeQuery()) { | |
| 249 if (result.next()) { | |
| 250 return mapColumns(result); | |
| 251 } else { | |
| 252 return null; | |
| 253 } | |
| 254 } | |
| 255 } | |
| 256 | |
| 257 private List<Version> listVersions(PreparedStatement stmt, Issue issue) throws SQLException { | |
| 258 stmt.setInt(1, issue.getId()); | |
| 259 List<Version> versions = new ArrayList<>(); | |
| 260 try (var result = stmt.executeQuery()) { | |
| 261 while (result.next()) { | |
| 262 versions.add(mapVersion(result)); | |
| 263 } | |
| 264 } | |
| 265 return versions; | |
| 266 } | |
| 267 | |
| 268 @Override | |
| 269 public void joinVersionInformation(Issue issue) throws SQLException { | |
| 270 Objects.requireNonNull(issue.getProject()); | |
| 271 issue.setAffectedVersions(listVersions(affectedVersions, issue)); | |
| 272 issue.setResolvedVersions(listVersions(resolvedVersions, issue)); | |
| 273 } | |
| 274 | |
| 275 @Override | |
| 276 public List<IssueComment> listComments(Issue issue) throws SQLException { | |
| 277 listComments.setInt(1, issue.getId()); | |
| 278 List<IssueComment> comments = new ArrayList<>(); | |
| 279 try (var result = listComments.executeQuery()) { | |
| 280 while (result.next()) { | |
| 281 final var comment = new IssueComment(result.getInt("commentid")); | |
| 282 comment.setCreated(result.getTimestamp("created")); | |
| 283 comment.setUpdated(result.getTimestamp("updated")); | |
| 284 comment.setUpdateCount(result.getInt("updatecount")); | |
| 285 comment.setComment(result.getString("comment")); | |
| 286 comment.setAuthor(PGUserDao.mapColumns(result)); | |
| 287 comments.add(comment); | |
| 288 } | |
| 289 } | |
| 290 return comments; | |
| 291 } | |
| 292 | |
| 293 @Override | |
| 294 public void saveComment(Issue issue, IssueComment comment) throws SQLException { | |
| 295 if (comment.getId() >= 0) { | |
| 296 updateComment.setString(1, comment.getComment()); | |
| 297 updateComment.setInt(2, comment.getId()); | |
| 298 updateComment.execute(); | |
| 299 } else { | |
| 300 insertComment.setInt(1, issue.getId()); | |
| 301 insertComment.setString(2, comment.getComment()); | |
| 302 setForeignKeyOrNull(insertComment, 3, comment.getAuthor(), User::getId); | |
| 303 insertComment.execute(); | |
| 304 } | |
| 305 } | |
| 306 } |
