Fri, 22 May 2020 21:23:57 +0200
adds the ability to create and edit issues
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.Connection;
35 import java.sql.PreparedStatement;
36 import java.sql.ResultSet;
37 import java.sql.SQLException;
38 import java.util.ArrayList;
39 import java.util.List;
40 import java.util.Objects;
42 import static de.uapcore.lightpit.dao.Functions.*;
44 public final class PGIssueDao implements IssueDao {
46 private final PreparedStatement insert, update, list, find, affectedVersions, scheduledVersions, resolvedVersions;
48 public PGIssueDao(Connection connection) throws SQLException {
49 list = connection.prepareStatement(
50 "select issueid, project, status, category, subject, description, " +
51 "userid, username, givenname, lastname, mail, " +
52 "created, updated, eta " +
53 "from lpit_issue " +
54 "left join lpit_user on userid = assignee " +
55 "where project = ? ");
57 find = connection.prepareStatement(
58 "select issueid, project, status, category, subject, description, " +
59 "userid, username, givenname, lastname, mail, " +
60 "created, updated, eta " +
61 "from lpit_issue " +
62 "left join lpit_user on userid = assignee " +
63 "where issueid = ? ");
65 insert = connection.prepareStatement(
66 "insert into lpit_issue (project, status, category, subject, description, assignee, eta) " +
67 "values (?, ?::issue_status, ?::issue_category, ?, ?, ?, ?) returning issueid"
68 );
69 update = connection.prepareStatement(
70 "update lpit_issue set updated = now(), status = ?::issue_status, category = ?::issue_category, " +
71 "subject = ?, description = ?, assignee = ?, eta = ? where issueid = ?"
72 );
74 affectedVersions = connection.prepareStatement(
75 "select v.versionid, v.name, v.status, v.ordinal " +
76 "from lpit_version v join lpit_issue_affected_version using (versionid) " +
77 "where issueid = ? " +
78 "order by v.ordinal, v.name"
79 );
81 scheduledVersions = connection.prepareStatement(
82 "select v.versionid, v.name, v.status, v.ordinal " +
83 "from lpit_version v join lpit_issue_scheduled_version using (versionid) " +
84 "where issueid = ? " +
85 "order by v.ordinal, v.name"
86 );
88 resolvedVersions = connection.prepareStatement(
89 "select v.versionid, v.name, v.status, v.ordinal " +
90 "from lpit_version v join lpit_issue_resolved_version using (versionid) " +
91 "where issueid = ? " +
92 "order by v.ordinal, v.name"
93 );
94 }
96 private User obtainAssignee(ResultSet result) throws SQLException {
97 final int id = result.getInt("userid");
98 if (id != 0) {
99 final var user = new User(id);
100 user.setUsername(result.getString("username"));
101 user.setGivenname(result.getString("givenname"));
102 user.setLastname(result.getString("lastname"));
103 user.setMail(result.getString("mail"));
104 return user;
105 } else {
106 return null;
107 }
108 }
110 private Issue mapColumns(ResultSet result) throws SQLException {
111 final var project = new Project(result.getInt("project"));
112 final var issue = new Issue(result.getInt("issueid"), project);
113 issue.setStatus(IssueStatus.valueOf(result.getString("status")));
114 issue.setCategory(IssueCategory.valueOf(result.getString("category")));
115 issue.setSubject(result.getString("subject"));
116 issue.setDescription(result.getString("description"));
117 issue.setAssignee(obtainAssignee(result));
118 issue.setCreated(result.getTimestamp("created"));
119 issue.setUpdated(result.getTimestamp("updated"));
120 issue.setEta(result.getDate("eta"));
121 return issue;
122 }
124 private Version mapVersion(ResultSet result, Project project) throws SQLException {
125 final var version = new Version(result.getInt("v.versionid"), project);
126 version.setName(result.getString("v.name"));
127 version.setOrdinal(result.getInt("v.ordinal"));
128 version.setStatus(VersionStatus.valueOf(result.getString("v.status")));
129 return version;
130 }
132 @Override
133 public void save(Issue instance) throws SQLException {
134 Objects.requireNonNull(instance.getSubject());
135 Objects.requireNonNull(instance.getProject());
136 insert.setInt(1, instance.getProject().getId());
137 insert.setString(2, instance.getStatus().name());
138 insert.setString(3, instance.getCategory().name());
139 insert.setString(4, instance.getSubject());
140 setStringOrNull(insert, 5, instance.getDescription());
141 setForeignKeyOrNull(insert, 6, instance.getAssignee(), User::getId);
142 setDateOrNull(insert, 7, instance.getEta());
143 // insert and retrieve the ID
144 final var rs = insert.executeQuery();
145 rs.next();
146 instance.setId(rs.getInt(1));
147 }
149 @Override
150 public boolean update(Issue instance) throws SQLException {
151 if (instance.getId() < 0) return false;
152 Objects.requireNonNull(instance.getSubject());
153 update.setString(1, instance.getStatus().name());
154 update.setString(2, instance.getCategory().name());
155 update.setString(3, instance.getSubject());
156 setStringOrNull(update, 4, instance.getDescription());
157 setForeignKeyOrNull(update, 5, instance.getAssignee(), User::getId);
158 setDateOrNull(update, 6, instance.getEta());
159 update.setInt(7, instance.getId());
160 return update.executeUpdate() > 0;
161 }
163 @Override
164 public List<Issue> list(Project project) throws SQLException {
165 list.setInt(1, project.getId());
166 List<Issue> issues = new ArrayList<>();
167 try (var result = list.executeQuery()) {
168 while (result.next()) {
169 issues.add(mapColumns(result));
170 }
171 }
172 return issues;
173 }
175 @Override
176 public Issue find(int id) throws SQLException {
177 find.setInt(1, id);
178 try (var result = find.executeQuery()) {
179 if (result.next()) {
180 return mapColumns(result);
181 } else {
182 return null;
183 }
184 }
185 }
187 private List<Version> listVersions(PreparedStatement stmt, Issue issue) throws SQLException {
188 stmt.setInt(1, issue.getId());
189 List<Version> versions = new ArrayList<>();
190 try (var result = stmt.executeQuery()) {
191 while (result.next()) {
192 versions.add(mapVersion(result, issue.getProject()));
193 }
194 }
195 return versions;
196 }
198 @Override
199 public void joinVersionInformation(Issue issue) throws SQLException {
200 Objects.requireNonNull(issue.getProject());
201 issue.setAffectedVersions(listVersions(affectedVersions, issue));
202 issue.setScheduledVersions(listVersions(scheduledVersions, issue));
203 issue.setResolvedVersions(listVersions(resolvedVersions, issue));
204 }
205 }