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

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

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

Wed, 06 Jan 2021 15:39:56 +0100

author: Mike Becker <universe@uap-core.de>
date: Wed, 06 Jan 2021 15:39:56 +0100
changeset 180: 009700915269
parent 176: 4da5b783aa2d
child 183: 61669abf277f
permissions: -rw-r--r--

merge resource bundles

 /*
  * 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.filter.*
 import de.uapcore.lightpit.types.WebColor
 import java.sql.Connection
 import java.sql.PreparedStatement
 import java.sql.ResultSet
 class PostgresDataAccessObject(private val connection: Connection) : DataAccessObject {
     //<editor-fold desc="User">
     private fun selectUserInfo(
         rs: ResultSet,
         idColumn: String = "userid",
         usernameColumn: String = "username",
         givennameColumn: String = "givenname",
         lastnameColumn: String = "lastname",
         mailColumn: String = "mail"
     ): User? {
         val idval = rs.getInt(idColumn)
         return if (rs.wasNull()) null else {
             User(idval).apply {
                 username = rs.getString(usernameColumn)
                 givenname = rs.getString(givennameColumn)
                 lastname = rs.getString(lastnameColumn)
                 mail = rs.getString(mailColumn)
             }
         }
     }
     private fun selectUsers(stmt: PreparedStatement) = sequence {
         stmt.executeQuery().use { rs ->
             while (rs.next()) selectUserInfo(rs)?.let { yield(it) }
         }
     }
     //language=SQL
     private val userQuery = "select userid, username, lastname, givenname, mail from lpit_user"
     private val stmtUsers by lazy {
         connection.prepareStatement(
             """${userQuery}
             where userid > 0
             order by username
             """
         )
     }
     private val stmtUserByID by lazy {
         connection.prepareStatement(
             """${userQuery}
             where userid = ?
             """
         )
     }
     private val stmtUserByName by lazy {
         connection.prepareStatement(
             """${userQuery}
             where lower(username) = lower(?)
             """
         )
     }
     private val stmtInsertUser by lazy {
         connection.prepareStatement(
             "insert into lpit_user (username, lastname, givenname, mail) values (?, ?, ?, ?)"
         )
     }
     private val stmtUpdateUser by lazy {
         connection.prepareStatement(
             "update lpit_user set lastname = ?, givenname = ?, mail = ? where userid = ?"
         )
     }
     override fun listUsers() = selectUsers(stmtUsers).toList()
     override fun findUser(id: Int): User? {
         stmtUserByID.setInt(1, id)
         return selectUsers(stmtUserByID).firstOrNull()
     }
     override fun findUserByName(username: String): User? {
         stmtUserByName.setString(1, username)
         return selectUsers(stmtUserByName).firstOrNull()
     }
     override fun insertUser(user: User) {
         with(user) {
             stmtInsertUser.setStringSafe(1, username)
             stmtInsertUser.setStringOrNull(2, lastname)
             stmtInsertUser.setStringOrNull(3, givenname)
             stmtInsertUser.setStringOrNull(4, mail)
         }
         stmtInsertUser.execute()
     }
     override fun updateUser(user: User) {
         with(user) {
             stmtUpdateUser.setStringOrNull(1, lastname)
             stmtUpdateUser.setStringOrNull(2, givenname)
             stmtUpdateUser.setStringOrNull(3, mail)
             stmtUpdateUser.setInt(4, id)
         }
         stmtUpdateUser.execute()
     }
     //</editor-fold>
     //<editor-fold desc="Version">
     private fun selectVersions(stmt: PreparedStatement) = sequence {
         stmt.executeQuery().use { rs ->
             while (rs.next()) {
                 yield(Version(rs.getInt("versionid"), rs.getInt("project")).apply {
                     name = rs.getString("name")
                     node = rs.getString("node")
                     ordinal = rs.getInt("ordinal")
                     status = rs.getEnum("status")
                 })
             }
         }
     }
     private fun setVersionFields(stmt: PreparedStatement, obj: Version): Int {
         with(obj) {
             stmt.setStringSafe(1, name)
             stmt.setStringSafe(2, node)
             stmt.setInt(3, ordinal)
             stmt.setEnum(4, status)
         }
         return 5
     }
     //language=SQL
     private val versionQuery = "select versionid, project, name, node, ordinal, status from lpit_version"
     private val stmtVersions by lazy {
         connection.prepareStatement(
             """${versionQuery}
             where project = ?
             order by ordinal desc, lower(name) desc
             """
         )
     }
     private val stmtVersionByID by lazy {
         connection.prepareStatement(
             """${versionQuery}
             where versionid = ?
             """
         )
     }
     private val stmtVersionByNode by lazy {
         connection.prepareStatement(
             """${versionQuery}
             where project = ? and node = ?
             """
         )
     }
     private val stmtInsertVersion by lazy {
         connection.prepareStatement(
             """
             insert into lpit_version (name, node, ordinal, status, project)
             values (?, ?, ?, ?::version_status, ?)
             """
         )
     }
     private val stmtUpdateVersion by lazy {
         connection.prepareStatement(
             """
             update lpit_version set name = ?, node = ?, ordinal = ?, status = ?::version_status
             where versionid = ?
             """
         )
     }
     override fun listVersions(project: Project): List<Version> {
         stmtVersions.setInt(1, project.id)
         return selectVersions(stmtVersions).toList()
     }
     override fun findVersion(id: Int): Version? {
         stmtVersionByID.setInt(1, id)
         return selectVersions(stmtVersionByID).firstOrNull()
     }
     override fun findVersionByNode(project: Project, node: String): Version? {
         stmtVersionByNode.setInt(1, project.id)
         stmtVersionByNode.setString(2, node)
         return selectVersions(stmtVersionByNode).firstOrNull()
     }
     override fun insertVersion(version: Version) {
         val col = setVersionFields(stmtInsertVersion, version)
         stmtInsertVersion.setInt(col, version.projectid)
         stmtInsertVersion.execute()
     }
     override fun updateVersion(version: Version) {
         val col = setVersionFields(stmtUpdateVersion, version)
         stmtUpdateVersion.setInt(col, version.id)
         stmtUpdateVersion.execute()
     }
     //</editor-fold>
     //<editor-fold desc="Component">
     private fun selectComponents(stmt: PreparedStatement) = sequence {
         stmt.executeQuery().use { rs ->
             while (rs.next()) {
                 yield(Component(rs.getInt("id"), rs.getInt("project")).apply {
                     name = rs.getString("name")
                     node = rs.getString("node")
                     color = try {
                         WebColor(rs.getString("color"))
                     } catch (ex: IllegalArgumentException) {
                         WebColor("000000")
                     }
                     ordinal = rs.getInt("ordinal")
                     description = rs.getString("description")
                     lead = selectUserInfo(rs)
                 })
             }
         }
     }
     private fun setComponentFields(stmt: PreparedStatement, obj: Component): Int {
         with(obj) {
             stmt.setStringSafe(1, name)
             stmt.setStringSafe(2, node)
             stmt.setStringSafe(3, color.hex)
             stmt.setInt(4, ordinal)
             stmt.setStringOrNull(5, description)
             stmt.setIntOrNull(6, obj.lead?.id)
         }
         return 7
     }
     //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
         """
     private val stmtComponents by lazy {
         connection.prepareStatement(
             """${componentQuery}
             where project = ?
             order by ordinal, lower(name)
             """
         )
     }
     private val stmtComponentById by lazy {
         connection.prepareStatement(
             """${componentQuery}
             where id = ?
             """
         )
     }
     private val stmtComponentByNode by lazy {
         connection.prepareStatement(
             """${componentQuery}
             where project = ? and node = ?
             """
         )
     }
     private val stmtInsertComponent by lazy {
         connection.prepareStatement(
             """
             insert into lpit_component (name, node, color, ordinal, description, lead, project)
             values (?, ?, ?, ?, ?, ?, ?)
             """
         )
     }
     private val stmtUpdateComponent by lazy {
         connection.prepareStatement(
             "update lpit_component set name = ?, node = ?, color = ?, ordinal = ?, description = ?, lead = ? where id = ?"
         )
     }
     override fun listComponents(project: Project): List<Component> {
         stmtComponents.setInt(1, project.id)
         return selectComponents(stmtComponents).toList()
     }
     override fun findComponent(id: Int): Component? {
         stmtComponentById.setInt(1, id)
         return selectComponents(stmtComponentById).firstOrNull()
     }
     override fun findComponentByNode(project: Project, node: String): Component? {
         stmtComponentByNode.setInt(1, project.id)
         stmtComponentByNode.setString(2, node)
         return selectComponents(stmtComponentByNode).firstOrNull()
     }
     override fun insertComponent(component: Component) {
         val col = setComponentFields(stmtInsertComponent, component)
         stmtInsertComponent.setInt(col, component.projectid)
         stmtInsertComponent.execute()
     }
     override fun updateComponent(component: Component) {
         val col = setComponentFields(stmtUpdateComponent, component)
         stmtUpdateComponent.setInt(col, component.id)
         stmtUpdateComponent.execute()
     }
     //</editor-fold>
     //<editor-fold desc="Project">
     private fun selectProjects(stmt: PreparedStatement) = sequence {
         stmt.executeQuery().use { rs ->
             while (rs.next()) {
                 yield(Project(rs.getInt("projectid")).apply {
                     name = rs.getString("name")
                     node = rs.getString("node")
                     ordinal = rs.getInt("ordinal")
                     description = rs.getString("description")
                     repoUrl = rs.getString("repourl")
                     owner = selectUserInfo(rs)
                 })
             }
         }
     }
     private fun setProjectFields(stmt: PreparedStatement, obj: Project): Int {
         with(obj) {
             stmt.setStringSafe(1, name)
             stmt.setStringSafe(2, node)
             stmt.setInt(3, ordinal)
             stmt.setStringOrNull(4, description)
             stmt.setStringOrNull(5, repoUrl)
             stmt.setIntOrNull(6, owner?.id)
         }
         return 7
     }
     //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
         """
     private val stmtProjects by lazy {
         connection.prepareStatement(
             """${projectQuery}
             order by ordinal, lower(name)
             """
         )
     }
     private val stmtProjectByID by lazy {
         connection.prepareStatement(
             """${projectQuery}
             where projectid = ?
             """
         )
     }
     private val stmtProjectByNode by lazy {
         connection.prepareStatement(
             """${projectQuery}
             where node = ?
             """
         )
     }
     private val stmtInsertProject by lazy {
         connection.prepareStatement(
             "insert into lpit_project (name, node, ordinal, description, repourl, owner) values (?, ?, ?, ?, ?, ?)"
         )
     }
     private val stmtUpdateProject by lazy {
         connection.prepareStatement(
             "update lpit_project set name = ?, node = ?, ordinal = ?, description = ?, repourl = ?, owner = ? where projectid = ?"
         )
     }
     private val stmtIssueSummary by lazy {
         connection.prepareStatement(
             """
             select phase, count(*) as total
             from lpit_issue
             join lpit_issue_phases using(status)
             where project = ?
             group by phase
             """
         )
     }
     override fun listProjects(): List<Project> {
         return selectProjects(stmtProjects).toList()
     }
     override fun findProject(id: Int): Project? {
         stmtProjectByID.setInt(1, id)
         return selectProjects(stmtProjectByID).firstOrNull()
     }
     override fun findProjectByNode(node: String): Project? {
         stmtProjectByNode.setString(1, node)
         return selectProjects(stmtProjectByNode).firstOrNull()
     }
     override fun insertProject(project: Project) {
         setProjectFields(stmtInsertProject, project)
         stmtInsertProject.execute()
     }
     override fun updateProject(project: Project) {
         val col = setProjectFields(stmtUpdateProject, project)
         stmtUpdateProject.setInt(col, project.id)
         stmtUpdateProject.execute()
     }
     override fun collectIssueSummary(project: Project): IssueSummary {
         stmtIssueSummary.setInt(1, project.id)
         return stmtIssueSummary.executeQuery().use { rs ->
             val summary = IssueSummary()
             while (rs.next()) {
                 val phase = rs.getInt("phase")
                 val total = rs.getInt("total")
                 when (phase) {
 -> summary.open = total
 -> summary.active = total
 -> summary.done = total
                 }
             }
             summary
         }
     }
     //</editor-fold>
     //<editor-fold desc="Issue">
     private fun selectIssues(stmt: PreparedStatement) = sequence {
         stmt.executeQuery().use { rs ->
             while (rs.next()) {
                 val proj = Project(rs.getInt("project")).apply {
                     name = rs.getString("projectname")
                     node = rs.getString("projectnode")
                 }
                 val comp = rs.getInt("component").let {
                     if (rs.wasNull()) null else
                         Component(it, proj.id).apply {
                             name = rs.getString("componentname")
                             node = rs.getString("componentnode")
                         }
                 }
                 val issue = Issue(rs.getInt("issueid"), proj, comp).apply {
                     component = comp
                     status = rs.getEnum("status")
                     category = rs.getEnum("category")
                     subject = rs.getString("subject")
                     description = rs.getString("description")
                     assignee = selectUserInfo(rs)
                     created = rs.getTimestamp("created")
                     updated = rs.getTimestamp("updated")
                     eta = rs.getDate("eta")
                 }
                 queryAffectedVersions.setInt(1, issue.id)
                 issue.affectedVersions = selectVersions(queryAffectedVersions).toList()
                 queryResolvedVersions.setInt(1, issue.id)
                 issue.resolvedVersions = selectVersions(queryResolvedVersions).toList()
                 yield(issue)
             }
         }
     }
     private fun setIssueFields(stmt: PreparedStatement, obj: Issue): Int {
         with(obj) {
             stmt.setIntOrNull(1, component?.id)
             stmt.setEnum(2, status)
             stmt.setEnum(3, category)
             stmt.setStringSafe(4, subject)
             stmt.setStringOrNull(5, description)
             stmt.setIntOrNull(6, assignee?.id)
             stmt.setDateOrNull(7, eta)
         }
         return 8
     }
     //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
         """
     private val queryResolvedVersions by lazy {
         connection.prepareStatement(
             """
             select versionid, project, name, status, ordinal, node
             from lpit_version v join lpit_issue_resolved_version using (versionid)
             where issueid = ?
             order by ordinal, name
             """
         )
     }
     private val queryAffectedVersions by lazy {
         connection.prepareStatement(
             """
             select versionid, project, name, status, ordinal, node
             from lpit_version join lpit_issue_affected_version using (versionid)
             where issueid = ?
             order by ordinal, name
             """
         )
     }
     private val stmtIssues by lazy {
         connection.prepareStatement(
             """
             with issue_version as (
                 select issueid, versionid from lpit_issue_affected_version
                 union select issueid, versionid from lpit_issue_resolved_version
             ) ${issueQuery} left join issue_version using (issueid)
             where
             (not ? or projectid = ?) and
             (not ? or versionid = ?) and (not ? or versionid is null) and
             (not ? or component = ?) and (not ? or component is null)
             """
         )
     }
     private val fproj = 1
     private val projectid = 2
     private val fversion = 3
     private val versionid = 4
     private val nversion = 5
     private val fcomp = 6
     private val component = 7
     private val ncomp = 8
     private fun <T : Entity> applyFilter(filter: Filter<T>, fflag: Int, nflag: Int, idcol: Int) {
         when (filter) {
             is AllFilter -> {
                 stmtIssues.setBoolean(fflag, false)
                 stmtIssues.setBoolean(nflag, false)
                 stmtIssues.setInt(idcol, 0)
             }
             is NoneFilter -> {
                 stmtIssues.setBoolean(fflag, false)
                 stmtIssues.setBoolean(nflag, true)
                 stmtIssues.setInt(idcol, 0)
             }
             is SpecificFilter -> {
                 stmtIssues.setBoolean(fflag, true)
                 stmtIssues.setBoolean(nflag, false)
                 stmtIssues.setInt(idcol, filter.obj.id)
             }
             else -> {
                 TODO("Implement range filter.")
             }
         }
     }
     override fun listIssues(filter: IssueFilter): List<Issue> {
         when (filter.project) {
             is AllFilter -> {
                 stmtIssues.setBoolean(fproj, false)
                 stmtIssues.setInt(projectid, 0)
             }
             is SpecificFilter -> {
                 stmtIssues.setBoolean(fproj, true)
                 stmtIssues.setInt(projectid, filter.project.obj.id)
             }
             else -> throw IllegalArgumentException()
         }
         applyFilter(filter.version, fversion, nversion, versionid)
         applyFilter(filter.component, fcomp, ncomp, component)
         return selectIssues(stmtIssues).toList()
     }
     private val stmtFindIssueByID by lazy {
         connection.prepareStatement(
             """${issueQuery}
             where issueid = ?
             """
         )
     }
     private val stmtInsertIssue by lazy {
         connection.prepareStatement(
             """
             insert into lpit_issue (component, status, category, subject, description, assignee, eta, project)
             values (?, ?::issue_status, ?::issue_category, ?, ?, ?, ?, ?)
             returning issueid
             """
         )
     }
     private val stmtUpdateIssue by lazy {
         connection.prepareStatement(
             """
             update lpit_issue set updated = now(),
                 component = ?, status = ?::issue_status, category = ?::issue_category, subject = ?,
                 description = ?, assignee = ?, eta = ?
             where issueid = ?
             """
         )
     }
     private val stmtInsertAffectedVersion by lazy {
         connection.prepareStatement(
             "insert into lpit_issue_affected_version (issueid, versionid) values (?,?)"
         )
     }
     private val stmtInsertResolvedVersion by lazy {
         connection.prepareStatement(
             "insert into lpit_issue_resolved_version (issueid, versionid) values (?,?)"
         )
     }
     private val stmtClearAffectedVersions by lazy {
         connection.prepareStatement("delete from lpit_issue_affected_version where issueid = ?")
     }
     private val stmtClearResolvedVersions by lazy {
         connection.prepareStatement("delete from lpit_issue_resolved_version where issueid = ?")
     }
     override fun findIssue(id: Int): Issue? {
         stmtFindIssueByID.setInt(1, id)
         return selectIssues(stmtFindIssueByID).firstOrNull()
     }
     private fun insertVersionInfo(issue: Issue) {
         stmtInsertAffectedVersion.setInt(1, issue.id)
         stmtInsertResolvedVersion.setInt(1, issue.id)
         issue.affectedVersions.forEach {
             stmtInsertAffectedVersion.setInt(2, it.id)
             stmtInsertAffectedVersion.execute()
         }
         issue.resolvedVersions.forEach {
             stmtInsertResolvedVersion.setInt(2, it.id)
             stmtInsertResolvedVersion.execute()
         }
     }
     override fun insertIssue(issue: Issue) {
         val col = setIssueFields(stmtInsertIssue, issue)
         stmtInsertIssue.setInt(col, issue.project.id)
         stmtInsertIssue.executeQuery().use { rs ->
             rs.next()
             issue.id = rs.getInt(1)
         }
         insertVersionInfo(issue)
     }
     override fun updateIssue(issue: Issue) {
         val col = setIssueFields(stmtUpdateIssue, issue)
         stmtUpdateIssue.setInt(col, issue.id)
         stmtUpdateIssue.execute()
         // TODO: improve by only inserting / deleting changed version information
         stmtClearAffectedVersions.setInt(1, issue.id)
         stmtClearResolvedVersions.setInt(1, issue.id)
         stmtClearAffectedVersions.execute()
         stmtClearResolvedVersions.execute()
         insertVersionInfo(issue)
     }
     //</editor-fold>
     //<editor-fold desc="IssueComment">
     private fun selectComments(stmt: PreparedStatement) = sequence {
         stmt.executeQuery().use { rs ->
             while (rs.next()) {
                 yield(IssueComment(rs.getInt("commentid"), rs.getInt("issueid")).apply {
                     created = rs.getTimestamp("created")
                     updated = rs.getTimestamp("updated")
                     updateCount = rs.getInt("updatecount")
                     comment = rs.getString("comment")
                     author = selectUserInfo(rs)
                 })
             }
         }
     }
     private val stmtComments by lazy {
         connection.prepareStatement(
             "select * from lpit_issue_comment left join lpit_user using (userid) where issueid = ? order by created"
         )
     }
     private val stmtInsertComment by lazy {
         connection.prepareStatement(
             "insert into lpit_issue_comment (issueid, comment, userid) values (?, ? ,?)"
         )
     }
     private val stmtUpdateIssueDate by lazy {
         connection.prepareStatement(
             "update lpit_issue set updated = now() where issueid = ?"
         )
     }
     override fun listComments(issue: Issue): List<IssueComment> {
         stmtComments.setInt(1, issue.id)
         return selectComments(stmtComments).toList()
     }
     override fun insertComment(issueComment: IssueComment) {
         with(issueComment) {
             stmtUpdateIssueDate.setInt(1, issueid)
             stmtInsertComment.setInt(1, issueid)
             stmtInsertComment.setStringSafe(2, comment)
             stmtInsertComment.setIntOrNull(3, author?.id)
         }
         stmtInsertComment.execute()
         stmtUpdateIssueDate.execute()
     }
     //</editor-fold>
 }

Mercurial > hg > LightPIT / annotate

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

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