comparison: src/main/kotlin/de/uapcore/lightpit/dao/postgres/PGIssueDao.kt
src/main/kotlin/de/uapcore/lightpit/dao/postgres/PGIssueDao.kt
- changeset 167
- 3f30adba1c63
- parent 164
- 003b08bb3f25
equal
deleted
inserted
replaced
| 166:6eede6088d41 | 167:3f30adba1c63 |
|---|---|
| 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 } |
