Mon, 21 Dec 2020 18:29:34 +0100
major refactoring of DAO architecture - also fixes #114
/* * Copyright 2020 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") 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.setStringOrNull(3, description) stmt.setStringOrNull(4, repoUrl) stmt.setIntOrNull(5, owner?.id) } return 6 } //language=SQL private val projectQuery = """ select projectid, name, node, 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 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, description, repourl, owner) values (?, ?, ?, ?, ?)" ) } private val stmtUpdateProject by lazy { connection.prepareStatement( "update lpit_project set name = ?, node = ?, 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) { 0 -> summary.open = total 1 -> summary.active = total 2 -> 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) // 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> }