|
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 override fun mapResult(rs: ResultSet): Issue { |
|
97 val project = Project(rs.getInt("project")) |
|
98 project.name = rs.getString("projectname") |
|
99 project.node = rs.getString("projectnode") |
|
100 val issue = Issue(rs.getInt("issueid")) |
|
101 issue.project = project |
|
102 issue.component = rs.getInt("component").let { id -> |
|
103 if (rs.wasNull()) { |
|
104 null |
|
105 } else { |
|
106 val component = Component(id) |
|
107 component.name = rs.getString("componentname") |
|
108 component.node = rs.getString("componentnode") |
|
109 component |
|
110 } |
|
111 } |
|
112 issue.status = IssueStatus.valueOf(rs.getString("status")) |
|
113 issue.category = IssueCategory.valueOf(rs.getString("category")) |
|
114 issue.subject = rs.getString("subject") |
|
115 issue.description = rs.getString("description") |
|
116 issue.assignee = PGUserDao.mapResult(rs).takeUnless { rs.wasNull() } |
|
117 issue.created = rs.getTimestamp("created") |
|
118 issue.updated = rs.getTimestamp("updated") |
|
119 issue.eta = rs.getDate("eta") |
|
120 return issue |
|
121 } |
|
122 |
|
123 private fun updateVersionLists(instance: Issue) { |
|
124 clearAffected.setInt(1, instance.id) |
|
125 clearResolved.setInt(1, instance.id) |
|
126 insertAffected.setInt(1, instance.id) |
|
127 insertResolved.setInt(1, instance.id) |
|
128 clearAffected.executeUpdate() |
|
129 clearResolved.executeUpdate() |
|
130 for (v: Version in instance.affectedVersions) { |
|
131 insertAffected.setInt(2, v.id) |
|
132 insertAffected.executeUpdate() |
|
133 } |
|
134 for (v: Version in instance.resolvedVersions) { |
|
135 insertResolved.setInt(2, v.id) |
|
136 insertResolved.executeUpdate() |
|
137 } |
|
138 } |
|
139 |
|
140 private fun setData(stmt: PreparedStatement, column: Int, instance: Issue): Int { |
|
141 var col = column |
|
142 setForeignKeyOrNull(stmt, ++col, instance.component, Component::id) |
|
143 stmt.setString(++col, instance.status.name) |
|
144 stmt.setString(++col, instance.category.name) |
|
145 stmt.setString(++col, instance.subject) |
|
146 Functions.setStringOrNull(stmt, ++col, instance.description) |
|
147 setForeignKeyOrNull(stmt, ++col, instance.assignee, User::id) |
|
148 Functions.setDateOrNull(stmt, ++col, instance.eta) |
|
149 return col |
|
150 } |
|
151 |
|
152 override fun save(instance: Issue, parent: Project) { |
|
153 instance.project = parent |
|
154 var column = 0 |
|
155 insert.setInt(++column, parent.id) |
|
156 setData(insert, column, instance) |
|
157 // insert and retrieve the ID |
|
158 val rs = insert.executeQuery() |
|
159 rs.next() |
|
160 instance.id = rs.getInt(1) |
|
161 updateVersionLists(instance) |
|
162 } |
|
163 |
|
164 override fun update(instance: Issue): Boolean { |
|
165 var column = setData(update, 0, instance) |
|
166 update.setInt(++column, instance.id) |
|
167 return if (update.executeUpdate() > 0) { |
|
168 updateVersionLists(instance) |
|
169 true |
|
170 } else { |
|
171 false |
|
172 } |
|
173 } |
|
174 |
|
175 override fun list(parent: Project): List<Issue> { |
|
176 list.setInt(1, parent.id) |
|
177 list.setNull(2, Types.INTEGER) |
|
178 return super.list(list) |
|
179 } |
|
180 |
|
181 override fun list(project: Project, component: Component?, version: Version?): List<Issue> { |
|
182 listForVersion.setInt(1, project.id) |
|
183 listForVersion.setInt(2, version?.id ?: -1) |
|
184 listForVersion.setInt(3, component?.id ?: -1) |
|
185 return super.list(listForVersion) |
|
186 } |
|
187 |
|
188 override fun list(project: Project, version: Version?): List<Issue> { |
|
189 listForVersion.setInt(1, project.id) |
|
190 listForVersion.setInt(2, version?.id ?: -1) |
|
191 listForVersion.setNull(3, Types.INTEGER) |
|
192 return super.list(listForVersion) |
|
193 } |
|
194 |
|
195 override fun list(project: Project, component: Component?): List<Issue> { |
|
196 list.setInt(1, project.id) |
|
197 list.setInt(2, component?.id ?: -1) |
|
198 return super.list(list) |
|
199 } |
|
200 |
|
201 override fun find(id: Int): Issue? { |
|
202 find.setInt(1, id) |
|
203 return super.find(find) |
|
204 } |
|
205 |
|
206 private fun listVersions(stmt: PreparedStatement, issue: Issue): List<Version> { |
|
207 stmt.setInt(1, issue.id) |
|
208 return sequence { |
|
209 stmt.executeQuery().use { result -> |
|
210 while (result.next()) yield(PGVersionDao.mapResult(result)) |
|
211 } |
|
212 }.toList() |
|
213 } |
|
214 |
|
215 override fun joinVersionInformation(issue: Issue) { |
|
216 issue.affectedVersions = listVersions(affectedVersions, issue) |
|
217 issue.resolvedVersions = listVersions(resolvedVersions, issue) |
|
218 } |
|
219 |
|
220 override fun listComments(issue: Issue): List<IssueComment> { |
|
221 listComments.setInt(1, issue.id) |
|
222 return sequence { |
|
223 listComments.executeQuery().use { rs -> |
|
224 while (rs.next()) { |
|
225 val comment = IssueComment(rs.getInt("commentid")) |
|
226 comment.created = rs.getTimestamp("created") |
|
227 comment.updated = rs.getTimestamp("updated") |
|
228 comment.updateCount = rs.getInt("updatecount") |
|
229 comment.comment = rs.getString("comment") |
|
230 comment.author = PGUserDao.mapResult(rs).takeUnless { rs.wasNull() } |
|
231 yield(comment) |
|
232 } |
|
233 } |
|
234 }.toList() |
|
235 } |
|
236 |
|
237 override fun saveComment(issue: Issue, comment: IssueComment) { |
|
238 if (comment.id >= 0) { |
|
239 updateComment.setString(1, comment.comment) |
|
240 updateComment.setInt(2, comment.id) |
|
241 updateComment.execute() |
|
242 } else { |
|
243 insertComment.setInt(1, issue.id) |
|
244 insertComment.setString(2, comment.comment) |
|
245 setForeignKeyOrNull(insertComment, 3, comment.author, User::id) |
|
246 insertComment.execute() |
|
247 } |
|
248 } |
|
249 } |