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

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

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

Tue, 11 May 2021 16:00:28 +0200

author: Mike Becker <universe@uap-core.de>
date: Tue, 11 May 2021 16:00:28 +0200
changeset 189: f7de8158b41c
parent 188: 2979436edd9e
child 190: a83f1ab56898
permissions: -rw-r--r--

fixes #137 - leaking prepared statements

 /*
  * 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.WebColor
 import de.uapcore.lightpit.util.*
 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 from lpit_version"
     private fun ResultSet.extractVersion() =
         Version(getInt("versionid"), getInt("project")).apply {
             name = getString("name")
             node = getString("node")
             ordinal = getInt("ordinal")
             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(issueid, versionid, isresolved) as (
                 select issueid, versionid, 1
                 from lpit_issue_resolved_version
                 union
                 select issueid, versionid, 0
                 from lpit_issue_affected_version
             ),
             issues as (
                 select versionid, phase, isresolved, count(issueid) as total
                 from lpit_issue
                 join version_map using (issueid)
                 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)
                 where v.project = ?
             )
             select versionid, project, name, node, ordinal, status, phase, isresolved, total
             from lpit_version
             join summary using (versionid)
             order by ordinal, name
             """.trimIndent()
         ) {
             setInt(1, project.id)
             executeQuery().use { rs ->
                 sequence {
                     // TODO: fix bug: this extractor is not grouping the results
                     val versionSummary = VersionSummary(rs.extractVersion())
                     val phase = rs.getInt("phase")
                     val total = rs.getInt("total")
                     val issueSummary =
                         if (rs.getBoolean("isresolved")) versionSummary.resolvedTotal else versionSummary.reportedTotal
                     when (phase) {
 -> issueSummary.open = total
 -> issueSummary.active = total
 -> issueSummary.done = total
                     }
                     yield(versionSummary)
                 }.toList()
             }
         }
     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) values (?, ?, ?, ?::version_status, ?)") {
             with(version) {
                 setStringSafe(1, name)
                 setStringSafe(2, node)
                 setInt(3, ordinal)
                 setEnum(4, status)
                 setInt(5, version.projectid)
             }
             executeUpdate()
         }
     }
     override fun updateVersion(version: Version) {
         withStatement("update lpit_version set name = ?, node = ?, ordinal = ?, status = ?::version_status where versionid = ?") {
             with(version) {
                 setStringSafe(1, name)
                 setStringSafe(2, node)
                 setInt(3, ordinal)
                 setEnum(4, status)
                 setInt(5, id)
             }
             executeUpdate()
         }
     }
     //</editor-fold>
     //<editor-fold desc="Component">
     //language=SQL
     private val componentQuery =
         """
         select id, project, name, node, color, ordinal, description,
             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")
             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)
             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
                 where c.project = ?
             )
             select c.id, project, name, node, color, ordinal, description,
                 userid, username, givenname, lastname, mail, phase, total
             from lpit_component c
             left join lpit_user on lead = userid
             join summary s on c.id = s.id
             order by ordinal, name
             """.trimIndent()
         ) {
             setInt(1, project.id)
             executeQuery().use { rs ->
                 // TODO: fix bug: this extractor is not grouping the results
                 sequence {
                     val componentSummary = ComponentSummary(rs.extractComponent()).also {
                         val phase = rs.getInt("phase")
                         val total = rs.getInt("total")
                         when (phase) {
 -> it.issueSummary.open = total
 -> it.issueSummary.active = total
 -> it.issueSummary.done = total
                         }
                     }
                     yield(componentSummary)
                 }.toList()
             }
         }
     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, 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 = ?, 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, 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")
             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)
             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, repourl, owner) values (?, ?, ?, ?, ?, ?)") {
             setProject(1, project)
             executeUpdate()
         }
     }
     override fun updateProject(project: Project) {
         withStatement("update lpit_project set name = ?, node = ?, ordinal = ?, description = ?, 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
             }
         }
 //</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, 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
         """.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")
         }
         fun versionQuery(table: String) =
             """
             select versionid, project, name, status, ordinal, node
             from lpit_version join $table using (versionid)
             where issueid = ?
             order by ordinal, name
             """.trimIndent()
         issue.affectedVersions = withStatement(versionQuery("lpit_issue_affected_version")) {
             setInt(1, issue.id)
             queryAll { it.extractVersion() }
         }
         issue.resolvedVersions = withStatement(versionQuery("lpit_issue_resolved_version")) {
             setInt(1, issue.id)
             queryAll { it.extractVersion() }
         }
         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)
         }
         return i
     }
     override fun listIssues(filter: IssueFilter): List<Issue> =
         withStatement(
             """
             with issue_version as (
                 select issueid, versionid from lpit_issue_affected_version
                 union select issueid, versionid from lpit_issue_resolved_version
             ),
             filtered_issues as (
                 select distinct issueid from lpit_issue
                 left join issue_version using (issueid)
                 where
                 (not ? or project = ?) and
                 (not ? or versionid = ?) and (not ? or versionid is null) and
                 (not ? or component = ?) and (not ? or component is null)
             )
             $issueQuery join filtered_issues using (issueid)
             """.trimIndent()
         ) {
             fun <T : Entity> applyFilter(filter: Filter<T>, fflag: Int, nflag: Int, idcol: Int) {
                 when (filter) {
                     is AllFilter -> {
                         setBoolean(fflag, false)
                         setBoolean(nflag, false)
                         setInt(idcol, 0)
                     }
                     is NoneFilter -> {
                         setBoolean(fflag, false)
                         setBoolean(nflag, true)
                         setInt(idcol, 0)
                     }
                     is SpecificFilter -> {
                         setBoolean(fflag, true)
                         setBoolean(nflag, false)
                         setInt(idcol, filter.obj.id)
                     }
                     else -> {
                         TODO("Implement range filter.")
                     }
                 }
             }
             when (filter.project) {
                 is AllFilter -> {
                     setBoolean(1, false)
                     setInt(2, 0)
                 }
                 is SpecificFilter -> {
                     setBoolean(1, true)
                     setInt(2, filter.project.obj.id)
                 }
                 else -> throw IllegalArgumentException()
             }
             applyFilter(filter.version, 3, 5, 4)
             applyFilter(filter.component, 6, 8, 7)
             queryAll { it.extractIssue() }
         }
     override fun findIssue(id: Int): Issue? =
         withStatement("$issueQuery where issueid = ?") {
             setInt(1, id)
             querySingle { it.extractIssue() }
         }
     private fun insertVersionInfo(id: Int, issue: Issue) {
         withStatement("insert into lpit_issue_affected_version (issueid, versionid) values (?,?)") {
             setInt(1, id)
             issue.affectedVersions.forEach {
                 setInt(2, it.id)
                 executeUpdate()
             }
         }
         withStatement("insert into lpit_issue_resolved_version (issueid, versionid) values (?,?)") {
             setInt(1, id)
             issue.resolvedVersions.forEach {
                 setInt(2, it.id)
                 executeUpdate()
             }
         }
     }
     override fun insertIssue(issue: Issue): Int {
         val id = withStatement(
             """
             insert into lpit_issue (component, status, category, subject, description, assignee, eta, project)
             values (?, ?::issue_status, ?::issue_category, ?, ?, ?, ?, ?)
             returning issueid
             """.trimIndent()
         ) {
             val col = setIssue(1, issue)
             setInt(col, issue.project.id)
             querySingle { it.getInt(1) }!!
         }
         insertVersionInfo(id, issue)
         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 = ?
             where issueid = ?
             """.trimIndent()
         ) {
             val col = setIssue(1, issue)
             setInt(col, issue.id)
             executeUpdate()
         }
         // TODO: improve by only inserting / deleting changed version information
         withStatement("delete from lpit_issue_affected_version where issueid = ?") {
             setInt(1, issue.id)
             executeUpdate()
         }
         withStatement("delete from lpit_issue_resolved_version where issueid = ?") {
             setInt(1, issue.id)
             executeUpdate()
         }
         insertVersionInfo(issue.id, issue)
     }
 //</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 insertComment(issueComment: IssueComment) {
         useStatement("update lpit_issue set updated = now() where issueid = ?") { updateIssueDate ->
             withStatement("insert into lpit_issue_comment (issueid, comment, userid) values (?, ? ,?)") {
                 with(issueComment) {
                     updateIssueDate.setInt(1, issueid)
                     setInt(1, issueid)
                     setStringSafe(2, comment)
                     setIntOrNull(3, author?.id)
                 }
                 executeUpdate()
                 updateIssueDate.executeUpdate()
             }
         }
     }
 //</editor-fold>
 }

Mercurial > hg > LightPIT / annotate

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

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