src/main/java/de/uapcore/lightpit/dao/postgres/PGIssueDao.java@3d10f2a390a1
src/main/java/de/uapcore/lightpit/dao/postgres/PGIssueDao.java
Sat, 31 Oct 2020 09:50:15 +0100
- author
- Mike Becker <universe@uap-core.de>
- date
- Sat, 31 Oct 2020 09:50:15 +0100
- changeset 154
- 3d10f2a390a1
- parent 150
- 822b7e3d064d
- permissions
- -rw-r--r--
fixes issues that are assigned no version and no component are listed under all projects
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;
31 import de.uapcore.lightpit.dao.IssueDao;
32 import de.uapcore.lightpit.entities.*;
34 import java.sql.*;
35 import java.util.ArrayList;
36 import java.util.List;
37 import java.util.Objects;
38 import java.util.Optional;
40 import static de.uapcore.lightpit.dao.Functions.*;
42 public final class PGIssueDao implements IssueDao {
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;
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 ";
61 list = connection.prepareStatement(query +
62 "where i.project = ? and coalesce(component, -1) = coalesce(?, component, -1)");
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 );
74 find = connection.prepareStatement(query + "where issueid = ? ");
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 );
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 (?,?)");
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 (?,?)");
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 }
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 }
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 }
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 }
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 }
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 }
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 }
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 }
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 }
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 }
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 }
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 }
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 }
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 }
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 }
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 }
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 }
