45 |
45 |
46 private final PreparedStatement insert, update, list, listForVersion, find; |
46 private final PreparedStatement insert, update, list, listForVersion, find; |
47 private final PreparedStatement affectedVersions, resolvedVersions; |
47 private final PreparedStatement affectedVersions, resolvedVersions; |
48 private final PreparedStatement clearAffected, clearResolved; |
48 private final PreparedStatement clearAffected, clearResolved; |
49 private final PreparedStatement insertAffected, insertResolved; |
49 private final PreparedStatement insertAffected, insertResolved; |
|
50 private final PreparedStatement insertComment, updateComment, listComments; |
50 |
51 |
51 public PGIssueDao(Connection connection) throws SQLException { |
52 public PGIssueDao(Connection connection) throws SQLException { |
52 list = connection.prepareStatement( |
53 list = connection.prepareStatement( |
53 "select issueid, project, p.name as projectname, status, category, subject, i.description, " + |
54 "select issueid, project, p.name as projectname, status, category, subject, i.description, " + |
54 "userid, username, givenname, lastname, mail, " + |
55 "userid, username, givenname, lastname, mail, " + |
105 "where issueid = ? " + |
106 "where issueid = ? " + |
106 "order by ordinal, name" |
107 "order by ordinal, name" |
107 ); |
108 ); |
108 clearResolved = connection.prepareStatement("delete from lpit_issue_resolved_version where issueid = ?"); |
109 clearResolved = connection.prepareStatement("delete from lpit_issue_resolved_version where issueid = ?"); |
109 insertResolved = connection.prepareStatement("insert into lpit_issue_resolved_version (issueid, versionid) values (?,?)"); |
110 insertResolved = connection.prepareStatement("insert into lpit_issue_resolved_version (issueid, versionid) values (?,?)"); |
110 } |
111 |
111 |
112 insertComment = connection.prepareStatement( |
112 private User obtainAssignee(ResultSet result) throws SQLException { |
113 "insert into lpit_issue_comment (issueid, comment, userid) values (?, ? ,?)" |
|
114 ); |
|
115 updateComment = connection.prepareStatement( |
|
116 "update lpit_issue_comment set comment = ?, updated = now(), updatecount = updatecount+1 where commentid = ?" |
|
117 ); |
|
118 listComments = connection.prepareStatement( |
|
119 "select * from lpit_issue_comment left join lpit_user using (userid) where issueid = ? order by created" |
|
120 ); |
|
121 } |
|
122 |
|
123 private User obtainUser(ResultSet result) throws SQLException { |
113 final int id = result.getInt("userid"); |
124 final int id = result.getInt("userid"); |
114 if (id != 0) { |
125 if (id != 0) { |
115 final var user = new User(id); |
126 final var user = new User(id); |
116 user.setUsername(result.getString("username")); |
127 user.setUsername(result.getString("username")); |
117 user.setGivenname(result.getString("givenname")); |
128 user.setGivenname(result.getString("givenname")); |
130 issue.setProject(project); |
141 issue.setProject(project); |
131 issue.setStatus(IssueStatus.valueOf(result.getString("status"))); |
142 issue.setStatus(IssueStatus.valueOf(result.getString("status"))); |
132 issue.setCategory(IssueCategory.valueOf(result.getString("category"))); |
143 issue.setCategory(IssueCategory.valueOf(result.getString("category"))); |
133 issue.setSubject(result.getString("subject")); |
144 issue.setSubject(result.getString("subject")); |
134 issue.setDescription(result.getString("description")); |
145 issue.setDescription(result.getString("description")); |
135 issue.setAssignee(obtainAssignee(result)); |
146 issue.setAssignee(obtainUser(result)); |
136 issue.setCreated(result.getTimestamp("created")); |
147 issue.setCreated(result.getTimestamp("created")); |
137 issue.setUpdated(result.getTimestamp("updated")); |
148 issue.setUpdated(result.getTimestamp("updated")); |
138 issue.setEta(result.getDate("eta")); |
149 issue.setEta(result.getDate("eta")); |
139 return issue; |
150 return issue; |
140 } |
151 } |
250 public void joinVersionInformation(Issue issue) throws SQLException { |
261 public void joinVersionInformation(Issue issue) throws SQLException { |
251 Objects.requireNonNull(issue.getProject()); |
262 Objects.requireNonNull(issue.getProject()); |
252 issue.setAffectedVersions(listVersions(affectedVersions, issue)); |
263 issue.setAffectedVersions(listVersions(affectedVersions, issue)); |
253 issue.setResolvedVersions(listVersions(resolvedVersions, issue)); |
264 issue.setResolvedVersions(listVersions(resolvedVersions, issue)); |
254 } |
265 } |
|
266 |
|
267 @Override |
|
268 public List<IssueComment> listComments(Issue issue) throws SQLException { |
|
269 listComments.setInt(1, issue.getId()); |
|
270 List<IssueComment> comments = new ArrayList<>(); |
|
271 try (var result = listComments.executeQuery()) { |
|
272 while (result.next()) { |
|
273 final var comment = new IssueComment(result.getInt("commentid"), issue); |
|
274 comment.setCreated(result.getTimestamp("created")); |
|
275 comment.setUpdated(result.getTimestamp("updated")); |
|
276 comment.setUpdateCount(result.getInt("updatecount")); |
|
277 comment.setComment(result.getString("comment")); |
|
278 comment.setAuthor(obtainUser(result)); |
|
279 comments.add(comment); |
|
280 } |
|
281 } |
|
282 return comments; |
|
283 } |
|
284 |
|
285 @Override |
|
286 public void saveComment(IssueComment comment) throws SQLException { |
|
287 Objects.requireNonNull(comment.getComment()); |
|
288 Objects.requireNonNull(comment.getIssue()); |
|
289 if (comment.getId() >= 0) { |
|
290 updateComment.setString(1, comment.getComment()); |
|
291 updateComment.setInt(2, comment.getId()); |
|
292 updateComment.execute(); |
|
293 } else { |
|
294 insertComment.setInt(1, comment.getIssue().getId()); |
|
295 insertComment.setString(2, comment.getComment()); |
|
296 setForeignKeyOrNull(insertComment, 3, comment.getAuthor(), User::getId); |
|
297 insertComment.execute(); |
|
298 } |
|
299 } |
255 } |
300 } |