42 import static de.uapcore.lightpit.dao.Functions.*; |
42 import static de.uapcore.lightpit.dao.Functions.*; |
43 |
43 |
44 public final class PGIssueDao implements IssueDao { |
44 public final class PGIssueDao implements IssueDao { |
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, scheduledVersions, resolvedVersions; |
47 private final PreparedStatement affectedVersions, resolvedVersions; |
48 private final PreparedStatement clearAffected, clearScheduled, clearResolved; |
48 private final PreparedStatement clearAffected, clearResolved; |
49 private final PreparedStatement insertAffected, insertScheduled, insertResolved; |
49 private final PreparedStatement insertAffected, insertResolved; |
50 |
50 |
51 public PGIssueDao(Connection connection) throws SQLException { |
51 public PGIssueDao(Connection connection) throws SQLException { |
52 list = connection.prepareStatement( |
52 list = connection.prepareStatement( |
53 "select issueid, project, p.name as projectname, status, category, subject, i.description, " + |
53 "select issueid, project, p.name as projectname, status, category, subject, i.description, " + |
54 "userid, username, givenname, lastname, mail, " + |
54 "userid, username, givenname, lastname, mail, " + |
60 "order by eta asc, updated desc"); |
60 "order by eta asc, updated desc"); |
61 |
61 |
62 listForVersion = connection.prepareStatement( |
62 listForVersion = connection.prepareStatement( |
63 "with issue_version as ( "+ |
63 "with issue_version as ( "+ |
64 "select issueid, versionid from lpit_issue_affected_version union "+ |
64 "select issueid, versionid from lpit_issue_affected_version union "+ |
65 "select issueid, versionid from lpit_issue_scheduled_version union "+ |
|
66 "select issueid, versionid from lpit_issue_resolved_version) "+ |
65 "select issueid, versionid from lpit_issue_resolved_version) "+ |
67 "select issueid, project, p.name as projectname, status, category, subject, i.description, " + |
66 "select issueid, project, p.name as projectname, status, category, subject, i.description, " + |
68 "userid, username, givenname, lastname, mail, " + |
67 "userid, username, givenname, lastname, mail, " + |
69 "created, updated, eta " + |
68 "created, updated, eta " + |
70 "from lpit_issue i " + |
69 "from lpit_issue i " + |
99 "where issueid = ? " + |
98 "where issueid = ? " + |
100 "order by ordinal, name" |
99 "order by ordinal, name" |
101 ); |
100 ); |
102 clearAffected = connection.prepareStatement("delete from lpit_issue_affected_version where issueid = ?"); |
101 clearAffected = connection.prepareStatement("delete from lpit_issue_affected_version where issueid = ?"); |
103 insertAffected = connection.prepareStatement("insert into lpit_issue_affected_version (issueid, versionid) values (?,?)"); |
102 insertAffected = connection.prepareStatement("insert into lpit_issue_affected_version (issueid, versionid) values (?,?)"); |
104 |
|
105 scheduledVersions = connection.prepareStatement( |
|
106 "select versionid, name, status, ordinal " + |
|
107 "from lpit_version join lpit_issue_scheduled_version using (versionid) " + |
|
108 "where issueid = ? " + |
|
109 "order by ordinal, name" |
|
110 ); |
|
111 clearScheduled = connection.prepareStatement("delete from lpit_issue_scheduled_version where issueid = ?"); |
|
112 insertScheduled = connection.prepareStatement("insert into lpit_issue_scheduled_version (issueid, versionid) values (?,?)"); |
|
113 |
103 |
114 resolvedVersions = connection.prepareStatement( |
104 resolvedVersions = connection.prepareStatement( |
115 "select versionid, name, status, ordinal " + |
105 "select versionid, name, status, ordinal " + |
116 "from lpit_version v join lpit_issue_resolved_version using (versionid) " + |
106 "from lpit_version v join lpit_issue_resolved_version using (versionid) " + |
117 "where issueid = ? " + |
107 "where issueid = ? " + |
159 return version; |
149 return version; |
160 } |
150 } |
161 |
151 |
162 private void updateVersionLists(Issue instance) throws SQLException { |
152 private void updateVersionLists(Issue instance) throws SQLException { |
163 clearAffected.setInt(1, instance.getId()); |
153 clearAffected.setInt(1, instance.getId()); |
164 clearScheduled.setInt(1, instance.getId()); |
|
165 clearResolved.setInt(1, instance.getId()); |
154 clearResolved.setInt(1, instance.getId()); |
166 insertAffected.setInt(1, instance.getId()); |
155 insertAffected.setInt(1, instance.getId()); |
167 insertScheduled.setInt(1, instance.getId()); |
|
168 insertResolved.setInt(1, instance.getId()); |
156 insertResolved.setInt(1, instance.getId()); |
169 clearAffected.executeUpdate(); |
157 clearAffected.executeUpdate(); |
170 clearScheduled.executeUpdate(); |
|
171 clearResolved.executeUpdate(); |
158 clearResolved.executeUpdate(); |
172 for (Version v : instance.getAffectedVersions()) { |
159 for (Version v : instance.getAffectedVersions()) { |
173 insertAffected.setInt(2, v.getId()); |
160 insertAffected.setInt(2, v.getId()); |
174 insertAffected.executeUpdate(); |
161 insertAffected.executeUpdate(); |
175 } |
|
176 for (Version v : instance.getScheduledVersions()) { |
|
177 insertScheduled.setInt(2, v.getId()); |
|
178 insertScheduled.executeUpdate(); |
|
179 } |
162 } |
180 for (Version v : instance.getResolvedVersions()) { |
163 for (Version v : instance.getResolvedVersions()) { |
181 insertResolved.setInt(2, v.getId()); |
164 insertResolved.setInt(2, v.getId()); |
182 insertResolved.executeUpdate(); |
165 insertResolved.executeUpdate(); |
183 } |
166 } |
267 |
250 |
268 @Override |
251 @Override |
269 public void joinVersionInformation(Issue issue) throws SQLException { |
252 public void joinVersionInformation(Issue issue) throws SQLException { |
270 Objects.requireNonNull(issue.getProject()); |
253 Objects.requireNonNull(issue.getProject()); |
271 issue.setAffectedVersions(listVersions(affectedVersions, issue)); |
254 issue.setAffectedVersions(listVersions(affectedVersions, issue)); |
272 issue.setScheduledVersions(listVersions(scheduledVersions, issue)); |
|
273 issue.setResolvedVersions(listVersions(resolvedVersions, issue)); |
255 issue.setResolvedVersions(listVersions(resolvedVersions, issue)); |
274 } |
256 } |
275 } |
257 } |