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