Tue, 11 May 2021 17:36:00 +0200
fixes #134 - automatic version selection
/* * 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, true from lpit_issue_resolved_version union select issueid, versionid, false 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) ) select v.versionid, project, name, node, ordinal, status, 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, name """.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) 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 ) select c.id, project, name, node, color, ordinal, description, userid, username, givenname, lastname, mail, open.total as open, active.total as active, 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 active on c.id = active.id and active.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("active") 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, 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) { 0 -> summary.open = total 1 -> summary.active = total 2 -> 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> }