1 /* |
|
2 * Copyright 2020 Mike Becker. All rights reserved. |
|
3 * |
|
4 * Redistribution and use in source and binary forms, with or without |
|
5 * modification, are permitted provided that the following conditions are met: |
|
6 * |
|
7 * 1. Redistributions of source code must retain the above copyright |
|
8 * notice, this list of conditions and the following disclaimer. |
|
9 * |
|
10 * 2. Redistributions in binary form must reproduce the above copyright |
|
11 * notice, this list of conditions and the following disclaimer in the |
|
12 * documentation and/or other materials provided with the distribution. |
|
13 * |
|
14 * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" |
|
15 * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE |
|
16 * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE |
|
17 * DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT HOLDER OR CONTRIBUTORS BE LIABLE |
|
18 * FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL |
|
19 * DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR |
|
20 * SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER |
|
21 * CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, |
|
22 * OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE |
|
23 * OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. |
|
24 * |
|
25 */ |
|
26 |
|
27 package de.uapcore.lightpit.dao.postgres |
|
28 |
|
29 import de.uapcore.lightpit.dao.AbstractIssueDao |
|
30 import de.uapcore.lightpit.dao.Functions |
|
31 import de.uapcore.lightpit.entities.* |
|
32 import java.sql.Connection |
|
33 import java.sql.PreparedStatement |
|
34 import java.sql.ResultSet |
|
35 import java.sql.Types |
|
36 |
|
37 class PGIssueDao(connection: Connection) : AbstractIssueDao() { |
|
38 |
|
39 private val query = "select issueid, i.project, p.name as projectname, p.node as projectnode, " + |
|
40 "component, c.name as componentname, c.node as componentnode, " + |
|
41 "status, category, subject, i.description, " + |
|
42 "userid, username, givenname, lastname, mail, " + |
|
43 "created, updated, eta " + |
|
44 "from lpit_issue i " + |
|
45 "join lpit_project p on i.project = projectid " + |
|
46 "left join lpit_component c on component = c.id " + |
|
47 "left join lpit_user on userid = assignee " |
|
48 private val list = connection.prepareStatement(query + |
|
49 "where i.project = ? and coalesce(component, -1) = coalesce(?, component, -1)") |
|
50 private val listForVersion = connection.prepareStatement( |
|
51 "with issue_version as ( " + |
|
52 "select issueid, versionid from lpit_issue_affected_version union " + |
|
53 "select issueid, versionid from lpit_issue_resolved_version) " + |
|
54 query + |
|
55 "left join issue_version using (issueid) " + |
|
56 "where i.project = ? " + |
|
57 "and coalesce(versionid,-1) = ? and coalesce(component, -1) = coalesce(?, component, -1)" |
|
58 ) |
|
59 private val find = connection.prepareStatement(query + "where issueid = ? ") |
|
60 private val insert = connection.prepareStatement( |
|
61 "insert into lpit_issue (project, component, status, category, subject, description, assignee, eta) " + |
|
62 "values (?, ?, ?::issue_status, ?::issue_category, ?, ?, ?, ?) returning issueid" |
|
63 ) |
|
64 private val update = connection.prepareStatement( |
|
65 "update lpit_issue set " + |
|
66 "updated = now(), component = ?, status = ?::issue_status, category = ?::issue_category, " + |
|
67 "subject = ?, description = ?, assignee = ?, eta = ? where issueid = ?" |
|
68 ) |
|
69 private val affectedVersions = connection.prepareStatement( |
|
70 "select versionid, name, status, ordinal, node " + |
|
71 "from lpit_version join lpit_issue_affected_version using (versionid) " + |
|
72 "where issueid = ? " + |
|
73 "order by ordinal, name" |
|
74 ) |
|
75 private val clearAffected = connection.prepareStatement("delete from lpit_issue_affected_version where issueid = ?") |
|
76 private val insertAffected = connection.prepareStatement("insert into lpit_issue_affected_version (issueid, versionid) values (?,?)") |
|
77 |
|
78 private val resolvedVersions = connection.prepareStatement( |
|
79 "select versionid, name, status, ordinal, node " + |
|
80 "from lpit_version v join lpit_issue_resolved_version using (versionid) " + |
|
81 "where issueid = ? " + |
|
82 "order by ordinal, name" |
|
83 ) |
|
84 private val clearResolved = connection.prepareStatement("delete from lpit_issue_resolved_version where issueid = ?") |
|
85 private val insertResolved = connection.prepareStatement("insert into lpit_issue_resolved_version (issueid, versionid) values (?,?)") |
|
86 private val insertComment = connection.prepareStatement( |
|
87 "insert into lpit_issue_comment (issueid, comment, userid) values (?, ? ,?)" |
|
88 ) |
|
89 private val updateComment = connection.prepareStatement( |
|
90 "update lpit_issue_comment set comment = ?, updated = now(), updatecount = updatecount+1 where commentid = ?" |
|
91 ) |
|
92 private val listComments = connection.prepareStatement( |
|
93 "select * from lpit_issue_comment left join lpit_user using (userid) where issueid = ? order by created" |
|
94 ) |
|
95 |
|
96 private val updateIssueLastModified = connection.prepareStatement( |
|
97 "update lpit_issue set updated = now() where issueid = ?" |
|
98 ); |
|
99 |
|
100 override fun mapResult(rs: ResultSet): Issue { |
|
101 val project = Project(rs.getInt("project")) |
|
102 project.name = rs.getString("projectname") |
|
103 project.node = rs.getString("projectnode") |
|
104 val issue = Issue(rs.getInt("issueid")) |
|
105 issue.project = project |
|
106 issue.component = rs.getInt("component").let { id -> |
|
107 if (rs.wasNull()) { |
|
108 null |
|
109 } else { |
|
110 val component = Component(id) |
|
111 component.name = rs.getString("componentname") |
|
112 component.node = rs.getString("componentnode") |
|
113 component |
|
114 } |
|
115 } |
|
116 issue.status = IssueStatus.valueOf(rs.getString("status")) |
|
117 issue.category = IssueCategory.valueOf(rs.getString("category")) |
|
118 issue.subject = rs.getString("subject") |
|
119 issue.description = rs.getString("description") |
|
120 issue.assignee = PGUserDao.mapResult(rs).takeUnless { rs.wasNull() } |
|
121 issue.created = rs.getTimestamp("created") |
|
122 issue.updated = rs.getTimestamp("updated") |
|
123 issue.eta = rs.getDate("eta") |
|
124 return issue |
|
125 } |
|
126 |
|
127 private fun updateVersionLists(instance: Issue) { |
|
128 clearAffected.setInt(1, instance.id) |
|
129 clearResolved.setInt(1, instance.id) |
|
130 insertAffected.setInt(1, instance.id) |
|
131 insertResolved.setInt(1, instance.id) |
|
132 clearAffected.executeUpdate() |
|
133 clearResolved.executeUpdate() |
|
134 for (v: Version in instance.affectedVersions) { |
|
135 insertAffected.setInt(2, v.id) |
|
136 insertAffected.executeUpdate() |
|
137 } |
|
138 for (v: Version in instance.resolvedVersions) { |
|
139 insertResolved.setInt(2, v.id) |
|
140 insertResolved.executeUpdate() |
|
141 } |
|
142 } |
|
143 |
|
144 private fun setData(stmt: PreparedStatement, column: Int, instance: Issue): Int { |
|
145 var col = column |
|
146 setForeignKeyOrNull(stmt, ++col, instance.component, Component::id) |
|
147 stmt.setString(++col, instance.status.name) |
|
148 stmt.setString(++col, instance.category.name) |
|
149 stmt.setString(++col, instance.subject) |
|
150 Functions.setStringOrNull(stmt, ++col, instance.description) |
|
151 setForeignKeyOrNull(stmt, ++col, instance.assignee, User::id) |
|
152 Functions.setDateOrNull(stmt, ++col, instance.eta) |
|
153 return col |
|
154 } |
|
155 |
|
156 override fun save(instance: Issue, parent: Project) { |
|
157 instance.project = parent |
|
158 var column = 0 |
|
159 insert.setInt(++column, parent.id) |
|
160 setData(insert, column, instance) |
|
161 // insert and retrieve the ID |
|
162 val rs = insert.executeQuery() |
|
163 rs.next() |
|
164 instance.id = rs.getInt(1) |
|
165 updateVersionLists(instance) |
|
166 } |
|
167 |
|
168 override fun update(instance: Issue): Boolean { |
|
169 var column = setData(update, 0, instance) |
|
170 update.setInt(++column, instance.id) |
|
171 return if (update.executeUpdate() > 0) { |
|
172 updateVersionLists(instance) |
|
173 true |
|
174 } else { |
|
175 false |
|
176 } |
|
177 } |
|
178 |
|
179 override fun list(parent: Project): List<Issue> { |
|
180 list.setInt(1, parent.id) |
|
181 list.setNull(2, Types.INTEGER) |
|
182 return super.list(list) |
|
183 } |
|
184 |
|
185 override fun list(project: Project, component: Component?, version: Version?): List<Issue> { |
|
186 listForVersion.setInt(1, project.id) |
|
187 listForVersion.setInt(2, version?.id ?: -1) |
|
188 listForVersion.setInt(3, component?.id ?: -1) |
|
189 return super.list(listForVersion) |
|
190 } |
|
191 |
|
192 override fun list(project: Project, version: Version?): List<Issue> { |
|
193 listForVersion.setInt(1, project.id) |
|
194 listForVersion.setInt(2, version?.id ?: -1) |
|
195 listForVersion.setNull(3, Types.INTEGER) |
|
196 return super.list(listForVersion) |
|
197 } |
|
198 |
|
199 override fun list(project: Project, component: Component?): List<Issue> { |
|
200 list.setInt(1, project.id) |
|
201 list.setInt(2, component?.id ?: -1) |
|
202 return super.list(list) |
|
203 } |
|
204 |
|
205 override fun find(id: Int): Issue? { |
|
206 find.setInt(1, id) |
|
207 return super.find(find) |
|
208 } |
|
209 |
|
210 private fun listVersions(stmt: PreparedStatement, issue: Issue): List<Version> { |
|
211 stmt.setInt(1, issue.id) |
|
212 return sequence { |
|
213 stmt.executeQuery().use { result -> |
|
214 while (result.next()) yield(PGVersionDao.mapResult(result)) |
|
215 } |
|
216 }.toList() |
|
217 } |
|
218 |
|
219 override fun joinVersionInformation(issue: Issue) { |
|
220 issue.affectedVersions = listVersions(affectedVersions, issue) |
|
221 issue.resolvedVersions = listVersions(resolvedVersions, issue) |
|
222 } |
|
223 |
|
224 override fun listComments(issue: Issue): List<IssueComment> { |
|
225 listComments.setInt(1, issue.id) |
|
226 return sequence { |
|
227 listComments.executeQuery().use { rs -> |
|
228 while (rs.next()) { |
|
229 val comment = IssueComment(rs.getInt("commentid")) |
|
230 comment.created = rs.getTimestamp("created") |
|
231 comment.updated = rs.getTimestamp("updated") |
|
232 comment.updateCount = rs.getInt("updatecount") |
|
233 comment.comment = rs.getString("comment") |
|
234 comment.author = PGUserDao.mapResult(rs).takeUnless { rs.wasNull() } |
|
235 yield(comment) |
|
236 } |
|
237 } |
|
238 }.toList() |
|
239 } |
|
240 |
|
241 override fun saveComment(issue: Issue, comment: IssueComment) { |
|
242 if (comment.id >= 0) { |
|
243 updateComment.setString(1, comment.comment) |
|
244 updateComment.setInt(2, comment.id) |
|
245 updateComment.execute() |
|
246 } else { |
|
247 insertComment.setInt(1, issue.id) |
|
248 insertComment.setString(2, comment.comment) |
|
249 setForeignKeyOrNull(insertComment, 3, comment.author, User::id) |
|
250 insertComment.execute() |
|
251 } |
|
252 updateIssueLastModified.setInt(1, issue.id); |
|
253 updateIssueLastModified.execute(); |
|
254 } |
|
255 } |
|