diff -r 6eede6088d41 -r 3f30adba1c63 src/main/kotlin/de/uapcore/lightpit/dao/postgres/PGIssueDao.kt --- a/src/main/kotlin/de/uapcore/lightpit/dao/postgres/PGIssueDao.kt Sun Dec 20 11:06:25 2020 +0100 +++ /dev/null Thu Jan 01 00:00:00 1970 +0000 @@ -1,255 +0,0 @@ -/* - * 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" - ) - - private val updateIssueLastModified = connection.prepareStatement( - "update lpit_issue set updated = now() where issueid = ?" - ); - - 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 { - list.setInt(1, parent.id) - list.setNull(2, Types.INTEGER) - return super.list(list) - } - - override fun list(project: Project, component: Component?, version: Version?): List { - 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 { - 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 { - 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 { - 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 { - 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() - } - updateIssueLastModified.setInt(1, issue.id); - updateIssueLastModified.execute(); - } -} \ No newline at end of file