Mercurial > hg > LightPIT / file revision

 /*

  * 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"

     )

     private val updateIssueLastModified = connection.prepareStatement(

         "update lpit_issue set updated = now() where issueid = ?"

     );

     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()

         }

         updateIssueLastModified.setInt(1, issue.id);

         updateIssueLastModified.execute();

     }

 }