1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000
1.2 +++ b/src/main/kotlin/de/uapcore/lightpit/dao/postgres/PGIssueDao.kt Thu Nov 19 13:58:54 2020 +0100
1.3 @@ -0,0 +1,249 @@
1.4 +/*
1.5 + * Copyright 2020 Mike Becker. All rights reserved.
1.6 + *
1.7 + * Redistribution and use in source and binary forms, with or without
1.8 + * modification, are permitted provided that the following conditions are met:
1.9 + *
1.10 + * 1. Redistributions of source code must retain the above copyright
1.11 + * notice, this list of conditions and the following disclaimer.
1.12 + *
1.13 + * 2. Redistributions in binary form must reproduce the above copyright
1.14 + * notice, this list of conditions and the following disclaimer in the
1.15 + * documentation and/or other materials provided with the distribution.
1.16 + *
1.17 + * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
1.18 + * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
1.19 + * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
1.20 + * DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT HOLDER OR CONTRIBUTORS BE LIABLE
1.21 + * FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL
1.22 + * DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR
1.23 + * SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER
1.24 + * CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY,
1.25 + * OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE
1.26 + * OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
1.27 + *
1.28 + */
1.29 +
1.30 +package de.uapcore.lightpit.dao.postgres
1.31 +
1.32 +import de.uapcore.lightpit.dao.AbstractIssueDao
1.33 +import de.uapcore.lightpit.dao.Functions
1.34 +import de.uapcore.lightpit.entities.*
1.35 +import java.sql.Connection
1.36 +import java.sql.PreparedStatement
1.37 +import java.sql.ResultSet
1.38 +import java.sql.Types
1.39 +
1.40 +class PGIssueDao(connection: Connection) : AbstractIssueDao() {
1.41 +
1.42 + private val query = "select issueid, i.project, p.name as projectname, p.node as projectnode, " +
1.43 + "component, c.name as componentname, c.node as componentnode, " +
1.44 + "status, category, subject, i.description, " +
1.45 + "userid, username, givenname, lastname, mail, " +
1.46 + "created, updated, eta " +
1.47 + "from lpit_issue i " +
1.48 + "join lpit_project p on i.project = projectid " +
1.49 + "left join lpit_component c on component = c.id " +
1.50 + "left join lpit_user on userid = assignee "
1.51 + private val list = connection.prepareStatement(query +
1.52 + "where i.project = ? and coalesce(component, -1) = coalesce(?, component, -1)")
1.53 + private val listForVersion = connection.prepareStatement(
1.54 + "with issue_version as ( " +
1.55 + "select issueid, versionid from lpit_issue_affected_version union " +
1.56 + "select issueid, versionid from lpit_issue_resolved_version) " +
1.57 + query +
1.58 + "left join issue_version using (issueid) " +
1.59 + "where i.project = ? " +
1.60 + "and coalesce(versionid,-1) = ? and coalesce(component, -1) = coalesce(?, component, -1)"
1.61 + )
1.62 + private val find = connection.prepareStatement(query + "where issueid = ? ")
1.63 + private val insert = connection.prepareStatement(
1.64 + "insert into lpit_issue (project, component, status, category, subject, description, assignee, eta) " +
1.65 + "values (?, ?, ?::issue_status, ?::issue_category, ?, ?, ?, ?) returning issueid"
1.66 + )
1.67 + private val update = connection.prepareStatement(
1.68 + "update lpit_issue set " +
1.69 + "updated = now(), component = ?, status = ?::issue_status, category = ?::issue_category, " +
1.70 + "subject = ?, description = ?, assignee = ?, eta = ? where issueid = ?"
1.71 + )
1.72 + private val affectedVersions = connection.prepareStatement(
1.73 + "select versionid, name, status, ordinal, node " +
1.74 + "from lpit_version join lpit_issue_affected_version using (versionid) " +
1.75 + "where issueid = ? " +
1.76 + "order by ordinal, name"
1.77 + )
1.78 + private val clearAffected = connection.prepareStatement("delete from lpit_issue_affected_version where issueid = ?")
1.79 + private val insertAffected = connection.prepareStatement("insert into lpit_issue_affected_version (issueid, versionid) values (?,?)")
1.80 +
1.81 + private val resolvedVersions = connection.prepareStatement(
1.82 + "select versionid, name, status, ordinal, node " +
1.83 + "from lpit_version v join lpit_issue_resolved_version using (versionid) " +
1.84 + "where issueid = ? " +
1.85 + "order by ordinal, name"
1.86 + )
1.87 + private val clearResolved = connection.prepareStatement("delete from lpit_issue_resolved_version where issueid = ?")
1.88 + private val insertResolved = connection.prepareStatement("insert into lpit_issue_resolved_version (issueid, versionid) values (?,?)")
1.89 + private val insertComment = connection.prepareStatement(
1.90 + "insert into lpit_issue_comment (issueid, comment, userid) values (?, ? ,?)"
1.91 + )
1.92 + private val updateComment = connection.prepareStatement(
1.93 + "update lpit_issue_comment set comment = ?, updated = now(), updatecount = updatecount+1 where commentid = ?"
1.94 + )
1.95 + private val listComments = connection.prepareStatement(
1.96 + "select * from lpit_issue_comment left join lpit_user using (userid) where issueid = ? order by created"
1.97 + )
1.98 +
1.99 + override fun mapResult(rs: ResultSet): Issue {
1.100 + val project = Project(rs.getInt("project"))
1.101 + project.name = rs.getString("projectname")
1.102 + project.node = rs.getString("projectnode")
1.103 + val issue = Issue(rs.getInt("issueid"))
1.104 + issue.project = project
1.105 + issue.component = rs.getInt("component").let { id ->
1.106 + if (rs.wasNull()) {
1.107 + null
1.108 + } else {
1.109 + val component = Component(id)
1.110 + component.name = rs.getString("componentname")
1.111 + component.node = rs.getString("componentnode")
1.112 + component
1.113 + }
1.114 + }
1.115 + issue.status = IssueStatus.valueOf(rs.getString("status"))
1.116 + issue.category = IssueCategory.valueOf(rs.getString("category"))
1.117 + issue.subject = rs.getString("subject")
1.118 + issue.description = rs.getString("description")
1.119 + issue.assignee = PGUserDao.mapResult(rs).takeUnless { rs.wasNull() }
1.120 + issue.created = rs.getTimestamp("created")
1.121 + issue.updated = rs.getTimestamp("updated")
1.122 + issue.eta = rs.getDate("eta")
1.123 + return issue
1.124 + }
1.125 +
1.126 + private fun updateVersionLists(instance: Issue) {
1.127 + clearAffected.setInt(1, instance.id)
1.128 + clearResolved.setInt(1, instance.id)
1.129 + insertAffected.setInt(1, instance.id)
1.130 + insertResolved.setInt(1, instance.id)
1.131 + clearAffected.executeUpdate()
1.132 + clearResolved.executeUpdate()
1.133 + for (v: Version in instance.affectedVersions) {
1.134 + insertAffected.setInt(2, v.id)
1.135 + insertAffected.executeUpdate()
1.136 + }
1.137 + for (v: Version in instance.resolvedVersions) {
1.138 + insertResolved.setInt(2, v.id)
1.139 + insertResolved.executeUpdate()
1.140 + }
1.141 + }
1.142 +
1.143 + private fun setData(stmt: PreparedStatement, column: Int, instance: Issue): Int {
1.144 + var col = column
1.145 + setForeignKeyOrNull(stmt, ++col, instance.component, Component::id)
1.146 + stmt.setString(++col, instance.status.name)
1.147 + stmt.setString(++col, instance.category.name)
1.148 + stmt.setString(++col, instance.subject)
1.149 + Functions.setStringOrNull(stmt, ++col, instance.description)
1.150 + setForeignKeyOrNull(stmt, ++col, instance.assignee, User::id)
1.151 + Functions.setDateOrNull(stmt, ++col, instance.eta)
1.152 + return col
1.153 + }
1.154 +
1.155 + override fun save(instance: Issue, parent: Project) {
1.156 + instance.project = parent
1.157 + var column = 0
1.158 + insert.setInt(++column, parent.id)
1.159 + setData(insert, column, instance)
1.160 + // insert and retrieve the ID
1.161 + val rs = insert.executeQuery()
1.162 + rs.next()
1.163 + instance.id = rs.getInt(1)
1.164 + updateVersionLists(instance)
1.165 + }
1.166 +
1.167 + override fun update(instance: Issue): Boolean {
1.168 + var column = setData(update, 0, instance)
1.169 + update.setInt(++column, instance.id)
1.170 + return if (update.executeUpdate() > 0) {
1.171 + updateVersionLists(instance)
1.172 + true
1.173 + } else {
1.174 + false
1.175 + }
1.176 + }
1.177 +
1.178 + override fun list(parent: Project): List<Issue> {
1.179 + list.setInt(1, parent.id)
1.180 + list.setNull(2, Types.INTEGER)
1.181 + return super.list(list)
1.182 + }
1.183 +
1.184 + override fun list(project: Project, component: Component?, version: Version?): List<Issue> {
1.185 + listForVersion.setInt(1, project.id)
1.186 + listForVersion.setInt(2, version?.id ?: -1)
1.187 + listForVersion.setInt(3, component?.id ?: -1)
1.188 + return super.list(listForVersion)
1.189 + }
1.190 +
1.191 + override fun list(project: Project, version: Version?): List<Issue> {
1.192 + listForVersion.setInt(1, project.id)
1.193 + listForVersion.setInt(2, version?.id ?: -1)
1.194 + listForVersion.setNull(3, Types.INTEGER)
1.195 + return super.list(listForVersion)
1.196 + }
1.197 +
1.198 + override fun list(project: Project, component: Component?): List<Issue> {
1.199 + list.setInt(1, project.id)
1.200 + list.setInt(2, component?.id ?: -1)
1.201 + return super.list(list)
1.202 + }
1.203 +
1.204 + override fun find(id: Int): Issue? {
1.205 + find.setInt(1, id)
1.206 + return super.find(find)
1.207 + }
1.208 +
1.209 + private fun listVersions(stmt: PreparedStatement, issue: Issue): List<Version> {
1.210 + stmt.setInt(1, issue.id)
1.211 + return sequence {
1.212 + stmt.executeQuery().use { result ->
1.213 + while (result.next()) yield(PGVersionDao.mapResult(result))
1.214 + }
1.215 + }.toList()
1.216 + }
1.217 +
1.218 + override fun joinVersionInformation(issue: Issue) {
1.219 + issue.affectedVersions = listVersions(affectedVersions, issue)
1.220 + issue.resolvedVersions = listVersions(resolvedVersions, issue)
1.221 + }
1.222 +
1.223 + override fun listComments(issue: Issue): List<IssueComment> {
1.224 + listComments.setInt(1, issue.id)
1.225 + return sequence {
1.226 + listComments.executeQuery().use { rs ->
1.227 + while (rs.next()) {
1.228 + val comment = IssueComment(rs.getInt("commentid"))
1.229 + comment.created = rs.getTimestamp("created")
1.230 + comment.updated = rs.getTimestamp("updated")
1.231 + comment.updateCount = rs.getInt("updatecount")
1.232 + comment.comment = rs.getString("comment")
1.233 + comment.author = PGUserDao.mapResult(rs).takeUnless { rs.wasNull() }
1.234 + yield(comment)
1.235 + }
1.236 + }
1.237 + }.toList()
1.238 + }
1.239 +
1.240 + override fun saveComment(issue: Issue, comment: IssueComment) {
1.241 + if (comment.id >= 0) {
1.242 + updateComment.setString(1, comment.comment)
1.243 + updateComment.setInt(2, comment.id)
1.244 + updateComment.execute()
1.245 + } else {
1.246 + insertComment.setInt(1, issue.id)
1.247 + insertComment.setString(2, comment.comment)
1.248 + setForeignKeyOrNull(insertComment, 3, comment.author, User::id)
1.249 + insertComment.execute()
1.250 + }
1.251 + }
1.252 +}
1.253 \ No newline at end of file
