Sun, 20 Dec 2020 11:06:25 +0100
minimize footprint of flexmark - fixes #116
159 | 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 | ||
164
003b08bb3f25
Update issue "updated" date when a comment is added or changed - fixes #111
Mike Becker <universe@uap-core.de>
parents:
159
diff
changeset
|
96 | private val updateIssueLastModified = connection.prepareStatement( |
003b08bb3f25
Update issue "updated" date when a comment is added or changed - fixes #111
Mike Becker <universe@uap-core.de>
parents:
159
diff
changeset
|
97 | "update lpit_issue set updated = now() where issueid = ?" |
003b08bb3f25
Update issue "updated" date when a comment is added or changed - fixes #111
Mike Becker <universe@uap-core.de>
parents:
159
diff
changeset
|
98 | ); |
003b08bb3f25
Update issue "updated" date when a comment is added or changed - fixes #111
Mike Becker <universe@uap-core.de>
parents:
159
diff
changeset
|
99 | |
159 | 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 | } | |
164
003b08bb3f25
Update issue "updated" date when a comment is added or changed - fixes #111
Mike Becker <universe@uap-core.de>
parents:
159
diff
changeset
|
252 | updateIssueLastModified.setInt(1, issue.id); |
003b08bb3f25
Update issue "updated" date when a comment is added or changed - fixes #111
Mike Becker <universe@uap-core.de>
parents:
159
diff
changeset
|
253 | updateIssueLastModified.execute(); |
159 | 254 | } |
255 | } |