Mercurial > hg > LightPIT / file revision

/*
 * Copyright 2021 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

import de.uapcore.lightpit.entities.*
import de.uapcore.lightpit.types.CommitRef
import de.uapcore.lightpit.types.IssueHistoryType
import de.uapcore.lightpit.types.RelationType
import de.uapcore.lightpit.types.WebColor
import de.uapcore.lightpit.viewmodel.ComponentSummary
import de.uapcore.lightpit.viewmodel.IssueSummary
import de.uapcore.lightpit.viewmodel.VersionSummary
import org.intellij.lang.annotations.Language
import java.sql.Connection
import java.sql.PreparedStatement
import java.sql.ResultSet

class PostgresDataAccessObject(private val connection: Connection) : DataAccessObject {

    /**
     * Prepares the given [sql] statement and executes the [block] function with the prepared statement as receiver.
     * The statement is then closed properly.
     */
    private fun <R> withStatement(@Language("SQL") sql: String, block: PreparedStatement.() -> R) =
        connection.prepareStatement(sql).use(block)

    /**
     * Prepares the given [sql] statement and executes the [block] function on that statement.
     * The statement is then closed properly.
     */
    private fun <R> useStatement(@Language("SQL") sql: String, block: (PreparedStatement) -> R) =
        connection.prepareStatement(sql).use(block)

    /**
     * Executes the statement and iterates the whole result set extracting the rows with the given [extractor] function.
     */
    private fun <T> PreparedStatement.queryAll(extractor: (ResultSet) -> T): List<T> = executeQuery().use {
        sequence {
            while (it.next()) {
                yield(extractor(it))
            }
        }.toList()
    }

    /**
     * Executes the statement and extracts a single row with the given [extractor] function.
     * If the result set is empty, null is returned.
     */
    private fun <T> PreparedStatement.querySingle(extractor: (ResultSet) -> T): T? = executeQuery().use {
        return if (it.next()) extractor(it) else null
    }

    //<editor-fold desc="User">
    //language=SQL
    private val userQuery = "select userid, username, lastname, givenname, mail from lpit_user"

    private fun ResultSet.extractUser() = User(getInt("userid")).apply {
        username = getString("username")
        givenname = getString("givenname")
        lastname = getString("lastname")
        mail = getString("mail")
    }

    private fun ResultSet.containsUserInfo(): Boolean {
        getInt("userid")
        return !wasNull()
    }

    private fun ResultSet.extractOptionalUser() = if (containsUserInfo()) extractUser() else null

    override fun listUsers() =
        withStatement("$userQuery where userid > 0 order by username") {
            queryAll { it.extractUser() }
        }

    override fun findUser(id: Int): User? =
        withStatement("$userQuery where userid = ?") {
            setInt(1, id)
            querySingle { it.extractUser() }
        }

    override fun findUserByName(username: String): User? =
        withStatement("$userQuery where lower(username) = lower(?)") {
            setString(1, username)
            querySingle { it.extractUser() }
        }

    override fun insertUser(user: User) {
        withStatement("insert into lpit_user (username, lastname, givenname, mail) values (?, ?, ?, ?)") {
            with(user) {
                setStringSafe(1, username)
                setStringOrNull(2, lastname)
                setStringOrNull(3, givenname)
                setStringOrNull(4, mail)
            }
            executeUpdate()
        }
    }

    override fun updateUser(user: User) {
        withStatement("update lpit_user set lastname = ?, givenname = ?, mail = ? where userid = ?") {
            with(user) {
                setStringOrNull(1, lastname)
                setStringOrNull(2, givenname)
                setStringOrNull(3, mail)
                setInt(4, id)
            }
            executeUpdate()
        }
    }
    //</editor-fold>

    //<editor-fold desc="Version">
    //language=SQL
    private val versionQuery = "select versionid, project, name, node, ordinal, status, release, eol from lpit_version"

    private fun ResultSet.extractVersion() =
        Version(getInt("versionid"), getInt("project")).apply {
            name = getString("name")
            node = getString("node")
            ordinal = getInt("ordinal")
            release = getDate("release")
            eol = getDate("eol")
            status = getEnum("status")
        }

    override fun listVersions(project: Project): List<Version> =
        withStatement("$versionQuery where project = ? order by ordinal desc, lower(name) desc") {
            setInt(1, project.id)
            queryAll { it.extractVersion() }
        }

    override fun listVersionSummaries(project: Project): List<VersionSummary> =
        withStatement(
            """with
            version_map as (
                select issueid, status, resolved as versionid, true as isresolved from lpit_issue
                union all
                select issueid, status, affected as versionid, false as isresolved from lpit_issue
            ), issues as (
                select versionid, phase, isresolved, count(issueid) as total from version_map
                join lpit_issue_phases using (status)
                group by versionid, phase, isresolved
            ),
            summary as (
                select versionid, phase, isresolved, total
                from lpit_version v
                left join issues using (versionid)
            )
            select v.versionid, project, name, node, ordinal, status, release, eol,
                ro.total as resolved_open, ra.total as resolved_active, rd.total as resolved_done,
                ao.total as affected_open, aa.total as affected_active, ad.total as affected_done
            from lpit_version v
            left join summary ro on ro.versionid = v.versionid and ro.phase = 0 and ro.isresolved
            left join summary ra on ra.versionid = v.versionid and ra.phase = 1 and ra.isresolved
            left join summary rd on rd.versionid = v.versionid and rd.phase = 2 and rd.isresolved
            left join summary ao on ao.versionid = v.versionid and ao.phase = 0 and not ao.isresolved
            left join summary aa on aa.versionid = v.versionid and aa.phase = 1 and not aa.isresolved
            left join summary ad on ad.versionid = v.versionid and ad.phase = 2 and not ad.isresolved
            where v.project = ?
            order by ordinal desc, lower(name) desc
            """.trimIndent()
        ) {
            setInt(1, project.id)
            queryAll { rs ->
                VersionSummary(rs.extractVersion()).apply {
                    reportedTotal.open = rs.getInt("affected_open")
                    reportedTotal.active = rs.getInt("affected_active")
                    reportedTotal.done = rs.getInt("affected_done")
                    resolvedTotal.open = rs.getInt("resolved_open")
                    resolvedTotal.active = rs.getInt("resolved_active")
                    resolvedTotal.done = rs.getInt("resolved_done")
                }
            }
        }

    override fun findVersion(id: Int): Version? =
        withStatement("$versionQuery where versionid = ?") {
            setInt(1, id)
            querySingle { it.extractVersion() }
        }

    override fun findVersionByNode(project: Project, node: String): Version? =
        withStatement("$versionQuery where project = ? and node = ?") {
            setInt(1, project.id)
            setString(2, node)
            querySingle { it.extractVersion() }
        }

    override fun insertVersion(version: Version) {
        withStatement("insert into lpit_version (name, node, ordinal, status, project, release, eol) values (?, ?, ?, ?::version_status, ?, ?, ?)") {
            with(version) {
                setStringSafe(1, name)
                setStringSafe(2, node)
                setInt(3, ordinal)
                setEnum(4, status)
                setInt(5, projectid)
                setDateOrNull(6, release)
                setDateOrNull(7, eol)
            }
            executeUpdate()
        }

    }

    override fun updateVersion(version: Version) {
        withStatement("update lpit_version set name = ?, node = ?, ordinal = ?, status = ?::version_status, release=?,eol=? where versionid = ?") {
            with(version) {
                setStringSafe(1, name)
                setStringSafe(2, node)
                setInt(3, ordinal)
                setEnum(4, status)
                setDateOrNull(5, version.release)
                setDateOrNull(6, version.eol)
                setInt(7, id)
            }
            executeUpdate()
        }
    }
    //</editor-fold>

    //<editor-fold desc="Component">
    //language=SQL
    private val componentQuery =
        """
        select id, project, name, node, color, ordinal, description, active,
            userid, username, givenname, lastname, mail
        from lpit_component
        left join lpit_user on lead = userid
        """.trimIndent()

    private fun ResultSet.extractComponent(): Component =
        Component(getInt("id"), getInt("project")).apply {
            name = getString("name")
            node = getString("node")
            color = try {
                WebColor(getString("color"))
            } catch (ex: IllegalArgumentException) {
                WebColor("000000")
            }
            ordinal = getInt("ordinal")
            description = getString("description")
            active = getBoolean("active")
            lead = extractOptionalUser()
        }

    private fun PreparedStatement.setComponent(index: Int, component: Component): Int {
        with(component) {
            var i = index
            setStringSafe(i++, name)
            setStringSafe(i++, node)
            setStringSafe(i++, color.hex)
            setInt(i++, ordinal)
            setStringOrNull(i++, description)
            setBoolean(i++, active)
            setIntOrNull(i++, lead?.id)
            return i
        }
    }

    override fun listComponents(project: Project): List<Component> =
        withStatement("$componentQuery where project = ? order by ordinal, lower(name)") {
            setInt(1, project.id)
            queryAll { it.extractComponent() }
        }

    override fun listComponentSummaries(project: Project): List<ComponentSummary> =
        withStatement(
            """
            with issues as (
                select component, phase, count(issueid) as total
                from lpit_issue
                join lpit_issue_phases using (status)
                group by component, phase
            ),
            summary as (
                select c.id, phase, total
                from lpit_component c
                left join issues i on c.id = i.component 
            )
            select c.id, project, name, node, color, ordinal, description, active,
                userid, username, givenname, lastname, mail,
                open.total as open, wip.total as wip, done.total as done
            from lpit_component c
            left join lpit_user on lead = userid
            left join summary open on c.id = open.id and open.phase = 0
            left join summary wip on c.id = wip.id and wip.phase = 1
            left join summary done on c.id = done.id and done.phase = 2
            where c.project = ?
            order by ordinal, name
            """.trimIndent()
        ) {
            setInt(1, project.id)
            queryAll { rs ->
                ComponentSummary(rs.extractComponent()).apply {
                    issueSummary.open = rs.getInt("open")
                    issueSummary.active = rs.getInt("wip")
                    issueSummary.done = rs.getInt("done")
                }
            }
        }

    override fun findComponent(id: Int): Component? =
        withStatement("$componentQuery where id = ?") {
            setInt(1, id)
            querySingle { it.extractComponent() }
        }

    override fun findComponentByNode(project: Project, node: String): Component? =
        withStatement("$componentQuery where project = ? and node = ?") {
            setInt(1, project.id)
            setString(2, node)
            querySingle { it.extractComponent() }
        }

    override fun insertComponent(component: Component) {
        withStatement("insert into lpit_component (name, node, color, ordinal, description, active, lead, project) values (?, ?, ?, ?, ?, ?, ?, ?)") {
            val col = setComponent(1, component)
            setInt(col, component.projectid)
            executeUpdate()
        }
    }

    override fun updateComponent(component: Component) {
        withStatement("update lpit_component set name = ?, node = ?, color = ?, ordinal = ?, description = ?, active = ?, lead = ? where id = ?") {
            val col = setComponent(1, component)
            setInt(col, component.id)
            executeUpdate()
        }
    }

    //</editor-fold>

    //<editor-fold desc="Project">

    //language=SQL
    private val projectQuery =
        """
        select projectid, name, node, ordinal, description, vcs, repourl,
            userid, username, lastname, givenname, mail
        from lpit_project
        left join lpit_user owner on lpit_project.owner = owner.userid
        """.trimIndent()

    private fun ResultSet.extractProject(prefix:String = "") =
        Project(getInt("${prefix}projectid")).apply {
            name = getString("${prefix}name")
            node = getString("${prefix}node")
            ordinal = getInt("${prefix}ordinal")
            description = getString("${prefix}description")
            vcs = getEnum("${prefix}vcs")
            repoUrl = getString("${prefix}repourl")
            owner = extractOptionalUser()
        }

    private fun PreparedStatement.setProject(index: Int, project: Project): Int {
        var i = index
        with(project) {
            setStringSafe(i++, name)
            setStringSafe(i++, node)
            setInt(i++, ordinal)
            setStringOrNull(i++, description)
            setEnum(i++, vcs)
            setStringOrNull(i++, repoUrl)
            setIntOrNull(i++, owner?.id)
        }
        return i
    }

    override fun listProjects(): List<Project> =
        withStatement("$projectQuery order by ordinal, lower(name)") {
            queryAll { it.extractProject() }
        }

    override fun findProject(id: Int): Project? =
        withStatement("$projectQuery where projectid = ?") {
            setInt(1, id)
            querySingle { it.extractProject() }
        }

    override fun findProjectByNode(node: String): Project? =
        withStatement("$projectQuery where node = ?") {
            setString(1, node)
            querySingle { it.extractProject() }
        }

    override fun insertProject(project: Project) {
        withStatement("insert into lpit_project (name, node, ordinal, description, vcs, repourl, owner) values (?, ?, ?, ?, ?::vcstype, ?, ?)") {
            setProject(1, project)
            executeUpdate()
        }
    }

    override fun updateProject(project: Project) {
        withStatement("update lpit_project set name = ?, node = ?, ordinal = ?, description = ?, vcs = ?::vcstype, repourl = ?, owner = ? where projectid = ?") {
            val col = setProject(1, project)
            setInt(col, project.id)
            executeUpdate()
        }
    }

    override fun collectIssueSummary(project: Project): IssueSummary =
        withStatement(
            """
            select phase, count(*) as total
            from lpit_issue
            join lpit_issue_phases using(status)
            where project = ?
            group by phase  
            """.trimIndent()
        ) {
            setInt(1, project.id)
            executeQuery().use {
                val summary = IssueSummary()
                while (it.next()) {
                    val phase = it.getInt("phase")
                    val total = it.getInt("total")
                    when (phase) {
                        0 -> summary.open = total
                        1 -> summary.active = total
                        2 -> summary.done = total
                    }
                }
                summary
            }
        }

    override fun collectIssueSummary(assignee: User): IssueSummary =
        withStatement(
            """
            select phase, count(*) as total
            from lpit_issue
            join lpit_issue_phases using(status)
            where assignee = ?
            group by phase  
            """.trimIndent()
        ) {
            setInt(1, assignee.id)
            executeQuery().use {
                val summary = IssueSummary()
                while (it.next()) {
                    val phase = it.getInt("phase")
                    val total = it.getInt("total")
                    when (phase) {
                        0 -> summary.open = total
                        1 -> summary.active = total
                        2 -> summary.done = total
                    }
                }
                summary
            }
        }

    override fun mergeCommitRefs(refs: List<CommitRef>) {
        withStatement("insert into lpit_commit_ref (issueid, commit_hash, commit_brief) values (?,?,?) on conflict do nothing") {
            refs.forEach { ref ->
                setInt(1, ref.issueId)
                setString(2, ref.hash)
                setString(3, ref.message)
                executeUpdate()
            }
        }
    }

    //</editor-fold>

    //<editor-fold desc="Issue">

    //language=SQL
    private val issueQuery =
        """
        select issueid, i.project,
            p.projectid as project_projectid,
            p.name as project_name,
            p.node as project_node,
            p.ordinal as project_ordinal,
            p.description as project_description,
            p.vcs as project_vcs,
            p.repourl as project_repourl,
            component, c.name as componentname, c.node as componentnode,
            status, phase, category, subject, i.description,
            userid, username, givenname, lastname, mail,
            created, updated, eta, affected, resolved
        from lpit_issue i
        join lpit_project p on i.project = projectid
        join lpit_issue_phases using (status)
        left join lpit_component c on component = c.id
        left join lpit_user on userid = assignee 
        """.trimIndent()

    private fun ResultSet.extractIssue(): Issue {
        val proj = extractProject("project_")
        val comp = getInt("component").let {
            if (wasNull()) null else
                Component(it, proj.id).apply {
                    name = getString("componentname")
                    node = getString("componentnode")
                }
        }
        val issue = Issue(getInt("issueid"), proj).apply {
            component = comp
            status = getEnum("status")
            category = getEnum("category")
            subject = getString("subject")
            description = getString("description")
            assignee = extractOptionalUser()
            created = getTimestamp("created")
            updated = getTimestamp("updated")
            eta = getDate("eta")
            affected = getInt("affected").takeIf { it > 0 }?.let { findVersion(it) }
            resolved = getInt("resolved").takeIf { it > 0 }?.let { findVersion(it) }
        }

        return issue
    }

    private fun PreparedStatement.setIssue(index: Int, issue: Issue): Int {
        var i = index
        with(issue) {
            setIntOrNull(i++, component?.id)
            setEnum(i++, status)
            setEnum(i++, category)
            setStringSafe(i++, subject)
            setStringOrNull(i++, description)
            setIntOrNull(i++, assignee?.id)
            setDateOrNull(i++, eta)
            setIntOrNull(i++, affected?.id)
            setIntOrNull(i++, resolved?.id)
        }
        return i
    }

    override fun listIssues(includeDone: Boolean): List<Issue> =
        withStatement("$issueQuery where (? or phase < 2)") {
            setBoolean(1, includeDone)
            queryAll { it.extractIssue() }
        }

    override fun listIssues(project: Project, includeDone: Boolean): List<Issue> =
        withStatement("$issueQuery where i.project = ? and (? or phase < 2)") {
            setInt(1, project.id)
            setBoolean(2, includeDone)
            queryAll { it.extractIssue() }
        }

    override fun listIssues(
        project: Project,
        includeDone: Boolean,
        specificVersion: Boolean,
        version: Version?,
        specificComponent: Boolean,
        component: Component?
    ): List<Issue> =
        withStatement(
            """$issueQuery where i.project = ? and
                (? or phase < 2) and
                (not ? or ? in (resolved, affected)) and (not ? or (resolved is null and affected is null)) and
                (not ? or component = ?) and (not ? or component is null)
            """.trimIndent()
        ) {
            setInt(1, project.id)
            setBoolean(2, includeDone)

            setBoolean(3, specificVersion && version != null)
            setInt(4, version?.id ?: 0)
            setBoolean(5, specificVersion && version == null)

            setBoolean(6, specificComponent && component != null)
            setInt(7, component?.id ?: 0)
            setBoolean(8, specificComponent && component == null)

            queryAll { it.extractIssue() }
        }

    override fun findIssue(id: Int): Issue? =
        withStatement("$issueQuery where issueid = ?") {
            setInt(1, id)
            querySingle { it.extractIssue() }
        }

    override fun insertIssue(issue: Issue): Int {
        val id = withStatement(
            """
            insert into lpit_issue (component, status, category, subject, description, assignee, eta, affected, resolved, project)
            values (?, ?::issue_status, ?::issue_category, ?, ?, ?, ?, ?, ?, ?)
            returning issueid
            """.trimIndent()
        ) {
            val col = setIssue(1, issue)
            setInt(col, issue.project.id)
            querySingle { it.getInt(1) }!!
        }
        return id
    }

    override fun updateIssue(issue: Issue) {
        withStatement(
            """
            update lpit_issue set updated = now(),
                component = ?, status = ?::issue_status, category = ?::issue_category, subject = ?,
                description = ?, assignee = ?, eta = ?, affected = ?, resolved = ?
            where issueid = ?
            """.trimIndent()
        ) {
            val col = setIssue(1, issue)
            setInt(col, issue.id)
            executeUpdate()
        }
    }

    override fun insertHistoryEvent(issue: Issue, newId: Int) {
        val type = if (newId > 0) IssueHistoryType.New else IssueHistoryType.Update
        val issueid = if (newId > 0) newId else issue.id

        val eventid =
            withStatement("insert into lpit_issue_history_event(issueid, subject, type) values (?,?,?::issue_history_event) returning eventid") {
                setInt(1, issueid)
                setString(2, issue.subject)
                setEnum(3, type)
                querySingle { it.getInt(1) }!!
            }
        withStatement(
            """
            insert into lpit_issue_history_data (component, status, category, description, assignee, eta, affected, resolved, eventid)
            values (?, ?::issue_status, ?::issue_category, ?, ?, ?, ?, ?, ?)
            """.trimIndent()
        ) {
            setStringOrNull(1, issue.component?.name)
            setEnum(2, issue.status)
            setEnum(3, issue.category)
            setStringOrNull(4, issue.description)
            setStringOrNull(5, issue.assignee?.shortDisplayname)
            setDateOrNull(6, issue.eta)
            setStringOrNull(7, issue.affected?.name)
            setStringOrNull(8, issue.resolved?.name)
            setInt(9, eventid)
            executeUpdate()
        }
    }

    override fun listCommitRefs(issue: Issue): List<CommitRef> =
        withStatement("select commit_hash, commit_brief from lpit_commit_ref where issueid = ?") {
            setInt(1, issue.id)
            queryAll {
                CommitRef(
                    issueId = issue.id,
                    hash = it.getString("commit_hash"),
                    message = it.getString("commit_brief")
                )
            }
        }

    //</editor-fold>

    //<editor-fold desc="Issue Relations">
    override fun insertIssueRelation(rel: IssueRelation) {
        withStatement(
            """
            insert into lpit_issue_relation (from_issue, to_issue, type)
            values (?, ?, ?::relation_type)
            on conflict do nothing
            """.trimIndent()
        ) {
            if (rel.reverse) {
                setInt(2, rel.from.id)
                setInt(1, rel.to.id)
            } else {
                setInt(1, rel.from.id)
                setInt(2, rel.to.id)
            }
            setEnum(3, rel.type)
            executeUpdate()
        }
    }

    override fun deleteIssueRelation(rel: IssueRelation) {
        withStatement("delete from lpit_issue_relation where from_issue = ? and to_issue = ? and type=?::relation_type") {
            if (rel.reverse) {
                setInt(2, rel.from.id)
                setInt(1, rel.to.id)
            } else {
                setInt(1, rel.from.id)
                setInt(2, rel.to.id)
            }
            setEnum(3, rel.type)
            executeUpdate()
        }
    }

    override fun listIssueRelations(issue: Issue): List<IssueRelation> = buildList {
        withStatement("select to_issue, type from lpit_issue_relation where from_issue = ?") {
            setInt(1, issue.id)
            queryAll { IssueRelation(issue, findIssue(it.getInt("to_issue"))!!, it.getEnum("type"), false) }
        }.forEach(this::add)
        withStatement("select from_issue, type from lpit_issue_relation where to_issue = ?") {
            setInt(1, issue.id)
            queryAll { IssueRelation(issue, findIssue(it.getInt("from_issue"))!!, it.getEnum("type"), true) }
        }.forEach(this::add)
    }

    override fun getIssueRelationMap(project: Project, includeDone: Boolean): IssueRelationMap =
        getIssueRelationMapImpl(project, includeDone)

    override fun getIssueRelationMap(includeDone: Boolean): IssueRelationMap =
        getIssueRelationMapImpl(null, includeDone)

    private fun getIssueRelationMapImpl(project: Project?, includeDone: Boolean): IssueRelationMap =
        withStatement(
            """
                select r.from_issue, r.to_issue, r.type
                from lpit_issue_relation r
                join lpit_issue i on i.issueid = r.from_issue
                join lpit_issue_phases p on i.status = p.status
                where (? or i.project = ?) and (? or p.phase < 2)
                """.trimIndent()
        ) {
            setBoolean(1, project == null)
            setInt(2, project?.id ?: 0)
            setBoolean(3, includeDone)
            queryAll { Pair(it.getInt("from_issue"), Pair(it.getInt("to_issue"), it.getEnum<RelationType>("type"))) }
        }.groupBy({it.first},{it.second})
    //</editor-fold>

    //<editor-fold desc="IssueComment">

    private fun ResultSet.extractIssueComment() =
        IssueComment(getInt("commentid"), getInt("issueid")).apply {
            created = getTimestamp("created")
            updated = getTimestamp("updated")
            updateCount = getInt("updatecount")
            comment = getString("comment")
            author = extractOptionalUser()
        }

    override fun listComments(issue: Issue): List<IssueComment> =
        withStatement("select * from lpit_issue_comment left join lpit_user using (userid) where issueid = ? order by created") {
            setInt(1, issue.id)
            queryAll { it.extractIssueComment() }
        }

    override fun findComment(id: Int): IssueComment? =
        withStatement("select * from lpit_issue_comment left join lpit_user using (userid) where commentid = ?") {
            setInt(1, id)
            querySingle { it.extractIssueComment() }
        }

    override fun insertComment(issueComment: IssueComment): Int =
        useStatement("update lpit_issue set updated = now() where issueid = ?") { updateIssueDate ->
            withStatement("insert into lpit_issue_comment (issueid, comment, userid) values (?, ? ,?) returning commentid") {
                with(issueComment) {
                    updateIssueDate.setInt(1, issueid)
                    setInt(1, issueid)
                    setStringSafe(2, comment)
                    setIntOrNull(3, author?.id)
                }
                val commentid = querySingle { it.getInt(1) }!!
                updateIssueDate.executeUpdate()
                commentid
            }
        }

    override fun updateComment(issueComment: IssueComment) {
        useStatement("update lpit_issue set updated = now() where issueid = ?") { updateIssueDate ->
            withStatement("update lpit_issue_comment set comment = ?, updatecount = updatecount + 1, updated = now() where commentid = ?") {
                with(issueComment) {
                    updateIssueDate.setInt(1, issueid)
                    setStringSafe(1, comment)
                    setInt(2, id)
                }
                executeUpdate()
                updateIssueDate.executeUpdate()
            }
        }
    }


    override fun insertHistoryEvent(issue: Issue, issueComment: IssueComment, newId: Int) {
        val type = if (newId > 0) IssueHistoryType.NewComment else IssueHistoryType.UpdateComment
        val commentid = if (newId > 0) newId else issueComment.id

        val eventid =
            withStatement("insert into lpit_issue_history_event(issueid, subject, type) values (?,?,?::issue_history_event) returning eventid") {
                setInt(1, issueComment.issueid)
                setString(2, issue.subject)
                setEnum(3, type)
                querySingle { it.getInt(1) }!!
            }
        withStatement("insert into lpit_issue_comment_history (commentid, eventid, comment) values (?,?,?)") {
            setInt(1, commentid)
            setInt(2, eventid)
            setString(3, issueComment.comment)
            executeUpdate()
        }
    }

    //</editor-fold>

    //<editor-fold desc="Issue History">

    override fun listIssueHistory(project: Project?, days: Int) =
        withStatement(
            """
                select p.name as project_name, u.username as current_assignee, evt.*, evtdata.*
                from lpit_issue_history_event evt
                join lpit_issue issue using (issueid)
                join lpit_project p on project = p.projectid 
                left join lpit_user u on u.userid = issue.assignee
                join lpit_issue_history_data evtdata using (eventid)
                where (? or project = ?)
                and time > now() - (? * interval '1' day) 
                order by time desc
            """.trimIndent()
        ) {
            setBoolean(1, project == null)
            setInt(2, project?.id ?: -1)
            setInt(3, days)
            queryAll { rs->
                with(rs) {
                    IssueHistoryEntry(
                        project = getString("project_name"),
                        subject = getString("subject"),
                        time = getTimestamp("time"),
                        type = getEnum("type"),
                        currentAssignee = getString("current_assignee"),
                        issueid = getInt("issueid"),
                        component = getString("component") ?: "",
                        status = getEnum("status"),
                        category = getEnum("category"),
                        description = getString("description") ?: "",
                        assignee = getString("assignee") ?: "",
                        eta = getDate("eta"),
                        affected = getString("affected") ?: "",
                        resolved = getString("resolved") ?: ""
                    )
                }
            }
        }

    override fun listIssueCommentHistory(project: Project?, days: Int) =
        withStatement(
            """
                select u.username as current_assignee, evt.*, evtdata.*
                from lpit_issue_history_event evt
                join lpit_issue issue using (issueid)
                left join lpit_user u on u.userid = issue.assignee
                join lpit_issue_comment_history evtdata using (eventid)
                where (? or project = ?)
                and time > now() - (? * interval '1' day) 
                order by time desc
            """.trimIndent()
        ) {
            setBoolean(1, project == null)
            setInt(2, project?.id ?: -1)
            setInt(3, days)
            queryAll { rs->
                with(rs) {
                    IssueCommentHistoryEntry(
                        subject = getString("subject"),
                        time = getTimestamp("time"),
                        type = getEnum("type"),
                        currentAssignee = getString("current_assignee"),
                        issueid = getInt("issueid"),
                        commentid = getInt("commentid"),
                        comment = getString("comment")
                    )
                }
            }
        }

    //</editor-fold>
}