LightPIT: src/main/kotlin/de/uapcore/lightpit/dao/postgres/PGIssueDao.kt@86b5d8a1662f (annotated)

src/main/kotlin/de/uapcore/lightpit/dao/postgres/PGIssueDao.kt@86b5d8a1662f (annotated)

src/main/kotlin/de/uapcore/lightpit/dao/postgres/PGIssueDao.kt

Thu, 19 Nov 2020 13:58:54 +0100

author: mike@uapl01.localdomain
date: Thu, 19 Nov 2020 13:58:54 +0100
changeset 159: 86b5d8a1662f
child 164: 003b08bb3f25
permissions: -rw-r--r--

migrates DAO classes

 /*
  * Copyright 2020 Mike Becker. All rights reserved.
  *
  * Redistribution and use in source and binary forms, with or without
  * modification, are permitted provided that the following conditions are met:
  *
  * 1. Redistributions of source code must retain the above copyright
  * notice, this list of conditions and the following disclaimer.
  *
  * 2. Redistributions in binary form must reproduce the above copyright
  * notice, this list of conditions and the following disclaimer in the
  * documentation and/or other materials provided with the distribution.
  *
  * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
  * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
  * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
  * DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT HOLDER OR CONTRIBUTORS BE LIABLE
  * FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL
  * DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR
  * SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER
  * CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY,
  * OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE
  * OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
  *
  */
 package de.uapcore.lightpit.dao.postgres
 import de.uapcore.lightpit.dao.AbstractIssueDao
 import de.uapcore.lightpit.dao.Functions
 import de.uapcore.lightpit.entities.*
 import java.sql.Connection
 import java.sql.PreparedStatement
 import java.sql.ResultSet
 import java.sql.Types
 class PGIssueDao(connection: Connection) : AbstractIssueDao() {
     private val query = "select issueid, i.project, p.name as projectname, p.node as projectnode, " +
             "component, c.name as componentname, c.node as componentnode, " +
             "status, category, subject, i.description, " +
             "userid, username, givenname, lastname, mail, " +
             "created, updated, eta " +
             "from lpit_issue i " +
             "join lpit_project p on i.project = projectid " +
             "left join lpit_component c on component = c.id " +
             "left join lpit_user on userid = assignee "
     private val list = connection.prepareStatement(query +
             "where i.project = ? and coalesce(component, -1) = coalesce(?, component, -1)")
     private val listForVersion = connection.prepareStatement(
             "with issue_version as ( " +
                     "select issueid, versionid from lpit_issue_affected_version union " +
                     "select issueid, versionid from lpit_issue_resolved_version) " +
                     query +
                     "left join issue_version using (issueid) " +
                     "where i.project = ? " +
                     "and coalesce(versionid,-1) = ? and coalesce(component, -1) = coalesce(?, component, -1)"
     )
     private val find = connection.prepareStatement(query + "where issueid = ? ")
     private val insert = connection.prepareStatement(
             "insert into lpit_issue (project, component, status, category, subject, description, assignee, eta) " +
                     "values (?, ?, ?::issue_status, ?::issue_category, ?, ?, ?, ?) returning issueid"
     )
     private val update = connection.prepareStatement(
             "update lpit_issue set " +
                     "updated = now(), component = ?, status = ?::issue_status, category = ?::issue_category, " +
                     "subject = ?, description = ?, assignee = ?, eta = ? where issueid = ?"
     )
     private val affectedVersions = connection.prepareStatement(
             "select versionid, name, status, ordinal, node " +
                     "from lpit_version join lpit_issue_affected_version using (versionid) " +
                     "where issueid = ? " +
                     "order by ordinal, name"
     )
     private val clearAffected = connection.prepareStatement("delete from lpit_issue_affected_version where issueid = ?")
     private val insertAffected = connection.prepareStatement("insert into lpit_issue_affected_version (issueid, versionid) values (?,?)")
     private val resolvedVersions = connection.prepareStatement(
             "select versionid, name, status, ordinal, node " +
                     "from lpit_version v join lpit_issue_resolved_version using (versionid) " +
                     "where issueid = ? " +
                     "order by ordinal, name"
     )
     private val clearResolved = connection.prepareStatement("delete from lpit_issue_resolved_version where issueid = ?")
     private val insertResolved = connection.prepareStatement("insert into lpit_issue_resolved_version (issueid, versionid) values (?,?)")
     private val insertComment = connection.prepareStatement(
             "insert into lpit_issue_comment (issueid, comment, userid) values (?, ? ,?)"
     )
     private val updateComment = connection.prepareStatement(
             "update lpit_issue_comment set comment = ?, updated = now(), updatecount = updatecount+1 where commentid = ?"
     )
     private val listComments = connection.prepareStatement(
             "select * from lpit_issue_comment left join lpit_user using (userid) where issueid = ? order by created"
     )
     override fun mapResult(rs: ResultSet): Issue {
         val project = Project(rs.getInt("project"))
         project.name = rs.getString("projectname")
         project.node = rs.getString("projectnode")
         val issue = Issue(rs.getInt("issueid"))
         issue.project = project
         issue.component = rs.getInt("component").let { id ->
             if (rs.wasNull()) {
                 null
             } else {
                 val component = Component(id)
                 component.name = rs.getString("componentname")
                 component.node = rs.getString("componentnode")
                 component
             }
         }
         issue.status = IssueStatus.valueOf(rs.getString("status"))
         issue.category = IssueCategory.valueOf(rs.getString("category"))
         issue.subject = rs.getString("subject")
         issue.description = rs.getString("description")
         issue.assignee = PGUserDao.mapResult(rs).takeUnless { rs.wasNull() }
         issue.created = rs.getTimestamp("created")
         issue.updated = rs.getTimestamp("updated")
         issue.eta = rs.getDate("eta")
         return issue
     }
     private fun updateVersionLists(instance: Issue) {
         clearAffected.setInt(1, instance.id)
         clearResolved.setInt(1, instance.id)
         insertAffected.setInt(1, instance.id)
         insertResolved.setInt(1, instance.id)
         clearAffected.executeUpdate()
         clearResolved.executeUpdate()
         for (v: Version in instance.affectedVersions) {
             insertAffected.setInt(2, v.id)
             insertAffected.executeUpdate()
         }
         for (v: Version in instance.resolvedVersions) {
             insertResolved.setInt(2, v.id)
             insertResolved.executeUpdate()
         }
     }
     private fun setData(stmt: PreparedStatement, column: Int, instance: Issue): Int {
         var col = column
         setForeignKeyOrNull(stmt, ++col, instance.component, Component::id)
         stmt.setString(++col, instance.status.name)
         stmt.setString(++col, instance.category.name)
         stmt.setString(++col, instance.subject)
         Functions.setStringOrNull(stmt, ++col, instance.description)
         setForeignKeyOrNull(stmt, ++col, instance.assignee, User::id)
         Functions.setDateOrNull(stmt, ++col, instance.eta)
         return col
     }
     override fun save(instance: Issue, parent: Project) {
         instance.project = parent
         var column = 0
         insert.setInt(++column, parent.id)
         setData(insert, column, instance)
         // insert and retrieve the ID
         val rs = insert.executeQuery()
         rs.next()
         instance.id = rs.getInt(1)
         updateVersionLists(instance)
     }
     override fun update(instance: Issue): Boolean {
         var column = setData(update, 0, instance)
         update.setInt(++column, instance.id)
         return if (update.executeUpdate() > 0) {
             updateVersionLists(instance)
             true
         } else {
             false
         }
     }
     override fun list(parent: Project): List<Issue> {
         list.setInt(1, parent.id)
         list.setNull(2, Types.INTEGER)
         return super.list(list)
     }
     override fun list(project: Project, component: Component?, version: Version?): List<Issue> {
         listForVersion.setInt(1, project.id)
         listForVersion.setInt(2, version?.id ?: -1)
         listForVersion.setInt(3, component?.id ?: -1)
         return super.list(listForVersion)
     }
     override fun list(project: Project, version: Version?): List<Issue> {
         listForVersion.setInt(1, project.id)
         listForVersion.setInt(2, version?.id ?: -1)
         listForVersion.setNull(3, Types.INTEGER)
         return super.list(listForVersion)
     }
     override fun list(project: Project, component: Component?): List<Issue> {
         list.setInt(1, project.id)
         list.setInt(2, component?.id ?: -1)
         return super.list(list)
     }
     override fun find(id: Int): Issue? {
         find.setInt(1, id)
         return super.find(find)
     }
     private fun listVersions(stmt: PreparedStatement, issue: Issue): List<Version> {
         stmt.setInt(1, issue.id)
         return sequence {
             stmt.executeQuery().use { result ->
                 while (result.next()) yield(PGVersionDao.mapResult(result))
             }
         }.toList()
     }
     override fun joinVersionInformation(issue: Issue) {
         issue.affectedVersions = listVersions(affectedVersions, issue)
         issue.resolvedVersions = listVersions(resolvedVersions, issue)
     }
     override fun listComments(issue: Issue): List<IssueComment> {
         listComments.setInt(1, issue.id)
         return sequence {
             listComments.executeQuery().use { rs ->
                 while (rs.next()) {
                     val comment = IssueComment(rs.getInt("commentid"))
                     comment.created = rs.getTimestamp("created")
                     comment.updated = rs.getTimestamp("updated")
                     comment.updateCount = rs.getInt("updatecount")
                     comment.comment = rs.getString("comment")
                     comment.author = PGUserDao.mapResult(rs).takeUnless { rs.wasNull() }
                     yield(comment)
                 }
             }
         }.toList()
     }
     override fun saveComment(issue: Issue, comment: IssueComment) {
         if (comment.id >= 0) {
             updateComment.setString(1, comment.comment)
             updateComment.setInt(2, comment.id)
             updateComment.execute()
         } else {
             insertComment.setInt(1, issue.id)
             insertComment.setString(2, comment.comment)
             setForeignKeyOrNull(insertComment, 3, comment.author, User::id)
             insertComment.execute()
         }
     }
 }

Mercurial > hg > LightPIT / annotate

src/main/kotlin/de/uapcore/lightpit/dao/postgres/PGIssueDao.kt@86b5d8a1662f (annotated)

src/main/kotlin/de/uapcore/lightpit/dao/postgres/PGIssueDao.kt