Thu, 19 Nov 2020 13:58:54 +0100
migrates DAO classes
/* * 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.postgres import de.uapcore.lightpit.dao.AbstractIssueDao import de.uapcore.lightpit.dao.Functions import de.uapcore.lightpit.entities.* import java.sql.Connection import java.sql.PreparedStatement import java.sql.ResultSet import java.sql.Types class PGIssueDao(connection: Connection) : AbstractIssueDao() { private val query = "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 list = connection.prepareStatement(query + "where i.project = ? and coalesce(component, -1) = coalesce(?, component, -1)") private val listForVersion = connection.prepareStatement( "with issue_version as ( " + "select issueid, versionid from lpit_issue_affected_version union " + "select issueid, versionid from lpit_issue_resolved_version) " + query + "left join issue_version using (issueid) " + "where i.project = ? " + "and coalesce(versionid,-1) = ? and coalesce(component, -1) = coalesce(?, component, -1)" ) private val find = connection.prepareStatement(query + "where issueid = ? ") private val insert = connection.prepareStatement( "insert into lpit_issue (project, component, status, category, subject, description, assignee, eta) " + "values (?, ?, ?::issue_status, ?::issue_category, ?, ?, ?, ?) returning issueid" ) private val update = connection.prepareStatement( "update lpit_issue set " + "updated = now(), component = ?, status = ?::issue_status, category = ?::issue_category, " + "subject = ?, description = ?, assignee = ?, eta = ? where issueid = ?" ) private val affectedVersions = connection.prepareStatement( "select versionid, name, status, ordinal, node " + "from lpit_version join lpit_issue_affected_version using (versionid) " + "where issueid = ? " + "order by ordinal, name" ) private val clearAffected = connection.prepareStatement("delete from lpit_issue_affected_version where issueid = ?") private val insertAffected = connection.prepareStatement("insert into lpit_issue_affected_version (issueid, versionid) values (?,?)") private val resolvedVersions = connection.prepareStatement( "select versionid, name, status, ordinal, node " + "from lpit_version v join lpit_issue_resolved_version using (versionid) " + "where issueid = ? " + "order by ordinal, name" ) private val clearResolved = connection.prepareStatement("delete from lpit_issue_resolved_version where issueid = ?") private val insertResolved = connection.prepareStatement("insert into lpit_issue_resolved_version (issueid, versionid) values (?,?)") private val insertComment = connection.prepareStatement( "insert into lpit_issue_comment (issueid, comment, userid) values (?, ? ,?)" ) private val updateComment = connection.prepareStatement( "update lpit_issue_comment set comment = ?, updated = now(), updatecount = updatecount+1 where commentid = ?" ) private val listComments = connection.prepareStatement( "select * from lpit_issue_comment left join lpit_user using (userid) where issueid = ? order by created" ) override fun mapResult(rs: ResultSet): Issue { val project = Project(rs.getInt("project")) project.name = rs.getString("projectname") project.node = rs.getString("projectnode") val issue = Issue(rs.getInt("issueid")) issue.project = project issue.component = rs.getInt("component").let { id -> if (rs.wasNull()) { null } else { val component = Component(id) component.name = rs.getString("componentname") component.node = rs.getString("componentnode") component } } issue.status = IssueStatus.valueOf(rs.getString("status")) issue.category = IssueCategory.valueOf(rs.getString("category")) issue.subject = rs.getString("subject") issue.description = rs.getString("description") issue.assignee = PGUserDao.mapResult(rs).takeUnless { rs.wasNull() } issue.created = rs.getTimestamp("created") issue.updated = rs.getTimestamp("updated") issue.eta = rs.getDate("eta") return issue } private fun updateVersionLists(instance: Issue) { clearAffected.setInt(1, instance.id) clearResolved.setInt(1, instance.id) insertAffected.setInt(1, instance.id) insertResolved.setInt(1, instance.id) clearAffected.executeUpdate() clearResolved.executeUpdate() for (v: Version in instance.affectedVersions) { insertAffected.setInt(2, v.id) insertAffected.executeUpdate() } for (v: Version in instance.resolvedVersions) { insertResolved.setInt(2, v.id) insertResolved.executeUpdate() } } private fun setData(stmt: PreparedStatement, column: Int, instance: Issue): Int { var col = column setForeignKeyOrNull(stmt, ++col, instance.component, Component::id) stmt.setString(++col, instance.status.name) stmt.setString(++col, instance.category.name) stmt.setString(++col, instance.subject) Functions.setStringOrNull(stmt, ++col, instance.description) setForeignKeyOrNull(stmt, ++col, instance.assignee, User::id) Functions.setDateOrNull(stmt, ++col, instance.eta) return col } override fun save(instance: Issue, parent: Project) { instance.project = parent var column = 0 insert.setInt(++column, parent.id) setData(insert, column, instance) // insert and retrieve the ID val rs = insert.executeQuery() rs.next() instance.id = rs.getInt(1) updateVersionLists(instance) } override fun update(instance: Issue): Boolean { var column = setData(update, 0, instance) update.setInt(++column, instance.id) return if (update.executeUpdate() > 0) { updateVersionLists(instance) true } else { false } } override fun list(parent: Project): List<Issue> { list.setInt(1, parent.id) list.setNull(2, Types.INTEGER) return super.list(list) } override fun list(project: Project, component: Component?, version: Version?): List<Issue> { listForVersion.setInt(1, project.id) listForVersion.setInt(2, version?.id ?: -1) listForVersion.setInt(3, component?.id ?: -1) return super.list(listForVersion) } override fun list(project: Project, version: Version?): List<Issue> { listForVersion.setInt(1, project.id) listForVersion.setInt(2, version?.id ?: -1) listForVersion.setNull(3, Types.INTEGER) return super.list(listForVersion) } override fun list(project: Project, component: Component?): List<Issue> { list.setInt(1, project.id) list.setInt(2, component?.id ?: -1) return super.list(list) } override fun find(id: Int): Issue? { find.setInt(1, id) return super.find(find) } private fun listVersions(stmt: PreparedStatement, issue: Issue): List<Version> { stmt.setInt(1, issue.id) return sequence { stmt.executeQuery().use { result -> while (result.next()) yield(PGVersionDao.mapResult(result)) } }.toList() } override fun joinVersionInformation(issue: Issue) { issue.affectedVersions = listVersions(affectedVersions, issue) issue.resolvedVersions = listVersions(resolvedVersions, issue) } override fun listComments(issue: Issue): List<IssueComment> { listComments.setInt(1, issue.id) return sequence { listComments.executeQuery().use { rs -> while (rs.next()) { val comment = IssueComment(rs.getInt("commentid")) comment.created = rs.getTimestamp("created") comment.updated = rs.getTimestamp("updated") comment.updateCount = rs.getInt("updatecount") comment.comment = rs.getString("comment") comment.author = PGUserDao.mapResult(rs).takeUnless { rs.wasNull() } yield(comment) } } }.toList() } override fun saveComment(issue: Issue, comment: IssueComment) { if (comment.id >= 0) { updateComment.setString(1, comment.comment) updateComment.setInt(2, comment.id) updateComment.execute() } else { insertComment.setInt(1, issue.id) insertComment.setString(2, comment.comment) setForeignKeyOrNull(insertComment, 3, comment.author, User::id) insertComment.execute() } } }