LightPIT: src/main/kotlin/de/uapcore/lightpit/dao/PostgresDataAccessObject.kt@671c1c8fbf1c (annotated)

src/main/kotlin/de/uapcore/lightpit/dao/PostgresDataAccessObject.kt@671c1c8fbf1c (annotated)

src/main/kotlin/de/uapcore/lightpit/dao/PostgresDataAccessObject.kt

Sat, 22 Jul 2023 22:32:04 +0200

author: Mike Becker <universe@uap-core.de>
date: Sat, 22 Jul 2023 22:32:04 +0200
changeset 284: 671c1c8fbf1c
parent 268: ca5501d851fa
child 292: 703591e739f4
permissions: -rw-r--r--

add full support for commit references - fixes #276

 /*
  * 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() =
         Project(getInt("projectid")).apply {
             name = getString("name")
             node = getString("node")
             ordinal = getInt("ordinal")
             description = getString("description")
             vcs = getEnum("vcs")
             repoUrl = getString("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) {
 -> summary.open = total
 -> summary.active = total
 -> 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) {
 -> summary.open = total
 -> summary.active = total
 -> 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.name as projectname, p.node as projectnode,
             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 = Project(getInt("project")).apply {
             name = getString("projectname")
             node = getString("projectnode")
         }
         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(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, version: Version?, 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()
         ) {
             fun <T : Entity> applyFilter(search: T?, fflag: Int, nflag: Int, idcol: Int) {
                 if (search == null) {
                     setBoolean(fflag, false)
                     setBoolean(nflag, false)
                     setInt(idcol, 0)
                 } else {
                     setBoolean(fflag, true)
                     setBoolean(nflag, false)
                     setInt(idcol, search.id)
                 }
             }
             setInt(1, project.id)
             setBoolean(2, includeDone)
             applyFilter(version, 3, 5, 4)
             applyFilter(component, 6, 8, 7)
             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 =
         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 i.project = ? and (? or p.phase < 2)
                 """.trimIndent()
         ) {
             setInt(1, project.id)
             setBoolean(2, 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(projectId: Int, 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_history_data evtdata using (eventid)
                 where project = ?
                 and time > now() - (? * interval '1' day)
                 order by time desc
             """.trimIndent()
         ) {
             setInt(1, projectId)
             setInt(2, days)
             queryAll { rs->
                 with(rs) {
                     IssueHistoryEntry(
                         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(projectId: Int, 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 project = ?
                 and time > now() - (? * interval '1' day)
                 order by time desc
             """.trimIndent()
         ) {
             setInt(1, projectId)
             setInt(2, 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>
 }

Mercurial > hg > LightPIT / annotate

src/main/kotlin/de/uapcore/lightpit/dao/PostgresDataAccessObject.kt@671c1c8fbf1c (annotated)

src/main/kotlin/de/uapcore/lightpit/dao/PostgresDataAccessObject.kt