41 |
41 |
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, find; |
46 private final PreparedStatement insert, update, list, find, affectedVersions, scheduledVersions, resolvedVersions; |
47 |
47 |
48 public PGIssueDao(Connection connection) throws SQLException { |
48 public PGIssueDao(Connection connection) throws SQLException { |
49 list = connection.prepareStatement( |
49 list = connection.prepareStatement( |
50 "select issue.id, issue.project, issue.status, issue.category, issue.subject, issue.description, " + |
50 "select issueid, project, status, category, subject, description, " + |
51 "vplan.id, vplan.name, vdone.id, vdone.name, " + |
51 "userid, username, givenname, lastname, mail, " + |
52 "issue.created, issue.updated, issue.eta " + |
52 "created, updated, eta " + |
53 "from lpit_issue issue " + |
53 "from lpit_issue " + |
54 "left join lpit_version vplan on vplan.id = version_plan " + |
54 "left join lpit_user on userid = assignee " + |
55 "left join lpit_version vdone on vdone.id = version_done " + |
55 "where project = ? "); |
56 "where issue.project = ? "); |
|
57 |
56 |
58 find = connection.prepareStatement( |
57 find = connection.prepareStatement( |
59 "select issue.id, issue.project, issue.status, issue.category, issue.subject, issue.description, " + |
58 "select issueid, project, status, category, subject, description, " + |
60 "vplan.id, vplan.name, vdone.id, vdone.name, " + |
59 "userid, username, givenname, lastname, mail, " + |
61 "issue.created, issue.updated, issue.eta " + |
60 "created, updated, eta " + |
62 "from lpit_issue issue " + |
61 "from lpit_issue " + |
63 "left join lpit_version vplan on vplan.id = version_plan " + |
62 "left join lpit_user on userid = assignee " + |
64 "left join lpit_version vdone on vdone.id = version_done " + |
63 "where issueid = ? "); |
65 "where issue.id = ? "); |
|
66 |
64 |
67 insert = connection.prepareStatement( |
65 insert = connection.prepareStatement( |
68 "insert into lpit_issue (project, status, category, subject, description, version_plan, version_done, eta) " + |
66 "insert into lpit_issue (project, status, category, subject, description, assignee, eta) " + |
69 "values (?, ?::issue_status, ?::issue_category, ?, ?, ?, ?, ?)" |
67 "values (?, ?::issue_status, ?::issue_category, ?, ?, ?, ?) returning issueid" |
70 ); |
68 ); |
71 update = connection.prepareStatement( |
69 update = connection.prepareStatement( |
72 "update lpit_issue set updated = now(), status = ?::issue_status, category = ?::issue_category, " + |
70 "update lpit_issue set updated = now(), status = ?::issue_status, category = ?::issue_category, " + |
73 "subject = ?, description = ?, version_plan = ?, version_done = ?, eta = ? where id = ?" |
71 "subject = ?, description = ?, assignee = ?, eta = ? where issueid = ?" |
74 ); |
72 ); |
75 } |
73 |
76 |
74 affectedVersions = connection.prepareStatement( |
77 private Version obtainVersion(ResultSet result, Project project, String prefix) throws SQLException { |
75 "select v.versionid, v.name, v.status, v.ordinal " + |
78 final int vplan = result.getInt(prefix + "id"); |
76 "from lpit_version v join lpit_issue_affected_version using (versionid) " + |
79 if (vplan > 0) { |
77 "where issueid = ? " + |
80 final var ver = new Version(vplan, project); |
78 "order by v.ordinal, v.name" |
81 ver.setName(result.getString(prefix + "name")); |
79 ); |
82 return ver; |
80 |
|
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 ); |
|
87 |
|
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 } |
|
95 |
|
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; |
83 } else { |
105 } else { |
84 return null; |
106 return null; |
85 } |
107 } |
86 } |
108 } |
87 |
109 |
88 public Issue mapColumns(ResultSet result) throws SQLException { |
110 private Issue mapColumns(ResultSet result) throws SQLException { |
89 final var project = new Project(result.getInt("issue.project")); |
111 final var project = new Project(result.getInt("project")); |
90 final var issue = new Issue(result.getInt("issue.id"), project); |
112 final var issue = new Issue(result.getInt("issueid"), project); |
91 issue.setStatus(IssueStatus.valueOf(result.getString("issue.status"))); |
113 issue.setStatus(IssueStatus.valueOf(result.getString("status"))); |
92 issue.setCategory(IssueCategory.valueOf(result.getString("issue.category"))); |
114 issue.setCategory(IssueCategory.valueOf(result.getString("category"))); |
93 issue.setSubject(result.getString("issue.subject")); |
115 issue.setSubject(result.getString("subject")); |
94 issue.setDescription(result.getString("issue.description")); |
116 issue.setDescription(result.getString("description")); |
95 issue.setScheduledVersion(obtainVersion(result, project, "vplan.")); |
117 issue.setAssignee(obtainAssignee(result)); |
96 issue.setResolvedVersion(obtainVersion(result, project, "vdone.")); |
118 issue.setCreated(result.getTimestamp("created")); |
97 issue.setCreated(result.getTimestamp("issue.created")); |
119 issue.setUpdated(result.getTimestamp("updated")); |
98 issue.setUpdated(result.getTimestamp("issue.updated")); |
120 issue.setEta(result.getDate("eta")); |
99 issue.setEta(result.getDate("issue.eta")); |
|
100 return issue; |
121 return issue; |
|
122 } |
|
123 |
|
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; |
101 } |
130 } |
102 |
131 |
103 @Override |
132 @Override |
104 public void save(Issue instance) throws SQLException { |
133 public void save(Issue instance) throws SQLException { |
105 Objects.requireNonNull(instance.getSubject()); |
134 Objects.requireNonNull(instance.getSubject()); |
107 insert.setInt(1, instance.getProject().getId()); |
136 insert.setInt(1, instance.getProject().getId()); |
108 insert.setString(2, instance.getStatus().name()); |
137 insert.setString(2, instance.getStatus().name()); |
109 insert.setString(3, instance.getCategory().name()); |
138 insert.setString(3, instance.getCategory().name()); |
110 insert.setString(4, instance.getSubject()); |
139 insert.setString(4, instance.getSubject()); |
111 setStringOrNull(insert, 5, instance.getDescription()); |
140 setStringOrNull(insert, 5, instance.getDescription()); |
112 setForeignKeyOrNull(insert, 6, instance.getScheduledVersion(), Version::getId); |
141 setForeignKeyOrNull(insert, 6, instance.getAssignee(), User::getId); |
113 setForeignKeyOrNull(insert, 7, instance.getResolvedVersion(), Version::getId); |
142 setDateOrNull(insert, 7, instance.getEta()); |
114 setDateOrNull(insert, 8, instance.getEta()); |
143 // insert and retrieve the ID |
115 insert.executeUpdate(); |
144 final var rs = insert.executeQuery(); |
|
145 rs.next(); |
|
146 instance.setId(rs.getInt(1)); |
116 } |
147 } |
117 |
148 |
118 @Override |
149 @Override |
119 public boolean update(Issue instance) throws SQLException { |
150 public boolean update(Issue instance) throws SQLException { |
|
151 if (instance.getId() < 0) return false; |
120 Objects.requireNonNull(instance.getSubject()); |
152 Objects.requireNonNull(instance.getSubject()); |
121 update.setString(1, instance.getStatus().name()); |
153 update.setString(1, instance.getStatus().name()); |
122 update.setString(2, instance.getCategory().name()); |
154 update.setString(2, instance.getCategory().name()); |
123 update.setString(3, instance.getSubject()); |
155 update.setString(3, instance.getSubject()); |
124 setStringOrNull(update, 4, instance.getDescription()); |
156 setStringOrNull(update, 4, instance.getDescription()); |
125 setForeignKeyOrNull(update, 5, instance.getScheduledVersion(), Version::getId); |
157 setForeignKeyOrNull(update, 5, instance.getAssignee(), User::getId); |
126 setForeignKeyOrNull(update, 6, instance.getResolvedVersion(), Version::getId); |
158 setDateOrNull(update, 6, instance.getEta()); |
127 setDateOrNull(update, 7, instance.getEta()); |
159 update.setInt(7, instance.getId()); |
128 update.setInt(8, instance.getId()); |
|
129 return update.executeUpdate() > 0; |
160 return update.executeUpdate() > 0; |
130 } |
161 } |
131 |
162 |
132 @Override |
163 @Override |
133 public List<Issue> list(Project project) throws SQLException { |
164 public List<Issue> list(Project project) throws SQLException { |
134 list.setInt(1, project.getId()); |
165 list.setInt(1, project.getId()); |
135 List<Issue> versions = new ArrayList<>(); |
166 List<Issue> issues = new ArrayList<>(); |
136 try (var result = list.executeQuery()) { |
167 try (var result = list.executeQuery()) { |
137 while (result.next()) { |
168 while (result.next()) { |
138 versions.add(mapColumns(result)); |
169 issues.add(mapColumns(result)); |
139 } |
170 } |
140 } |
171 } |
141 return versions; |
172 return issues; |
142 } |
173 } |
143 |
174 |
144 @Override |
175 @Override |
145 public Issue find(int id) throws SQLException { |
176 public Issue find(int id) throws SQLException { |
146 find.setInt(1, id); |
177 find.setInt(1, id); |