universe@167: /* universe@180: * Copyright 2021 Mike Becker. All rights reserved. universe@167: * universe@167: * Redistribution and use in source and binary forms, with or without universe@167: * modification, are permitted provided that the following conditions are met: universe@167: * universe@167: * 1. Redistributions of source code must retain the above copyright universe@167: * notice, this list of conditions and the following disclaimer. universe@167: * universe@167: * 2. Redistributions in binary form must reproduce the above copyright universe@167: * notice, this list of conditions and the following disclaimer in the universe@167: * documentation and/or other materials provided with the distribution. universe@167: * universe@167: * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" universe@167: * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE universe@167: * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE universe@167: * DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT HOLDER OR CONTRIBUTORS BE LIABLE universe@167: * FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL universe@167: * DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR universe@167: * SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER universe@167: * CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, universe@167: * OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE universe@167: * OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. universe@167: */ universe@167: universe@167: package de.uapcore.lightpit.dao universe@167: universe@167: import de.uapcore.lightpit.entities.* universe@232: import de.uapcore.lightpit.types.IssueHistoryType universe@167: import de.uapcore.lightpit.types.WebColor universe@184: import de.uapcore.lightpit.util.* universe@184: import de.uapcore.lightpit.viewmodel.ComponentSummary universe@184: import de.uapcore.lightpit.viewmodel.IssueSummary universe@184: import de.uapcore.lightpit.viewmodel.VersionSummary universe@189: import org.intellij.lang.annotations.Language universe@167: import java.sql.Connection universe@167: import java.sql.PreparedStatement universe@167: import java.sql.ResultSet universe@167: universe@167: class PostgresDataAccessObject(private val connection: Connection) : DataAccessObject { universe@167: universe@189: /** universe@189: * Prepares the given [sql] statement and executes the [block] function with the prepared statement as receiver. universe@189: * The statement is then closed properly. universe@189: */ universe@189: private fun withStatement(@Language("SQL") sql: String, block: PreparedStatement.() -> R) = universe@189: connection.prepareStatement(sql).use(block) universe@189: universe@189: /** universe@189: * Prepares the given [sql] statement and executes the [block] function on that statement. universe@189: * The statement is then closed properly. universe@189: */ universe@189: private fun useStatement(@Language("SQL") sql: String, block: (PreparedStatement) -> R) = universe@189: connection.prepareStatement(sql).use(block) universe@189: universe@189: /** universe@189: * Executes the statement and iterates the whole result set extracting the rows with the given [extractor] function. universe@189: */ universe@189: private fun PreparedStatement.queryAll(extractor: (ResultSet) -> T): List = executeQuery().use { universe@189: sequence { universe@189: while (it.next()) { universe@189: yield(extractor(it)) universe@189: } universe@189: }.toList() universe@189: } universe@189: universe@189: /** universe@189: * Executes the statement and extracts a single row with the given [extractor] function. universe@189: * If the result set is empty, null is returned. universe@189: */ universe@189: private fun PreparedStatement.querySingle(extractor: (ResultSet) -> T): T? = executeQuery().use { universe@189: return if (it.next()) extractor(it) else null universe@189: } universe@189: universe@167: // universe@189: //language=SQL universe@189: private val userQuery = "select userid, username, lastname, givenname, mail from lpit_user" universe@189: universe@189: private fun ResultSet.extractUser() = User(getInt("userid")).apply { universe@189: username = getString("username") universe@189: givenname = getString("givenname") universe@189: lastname = getString("lastname") universe@189: mail = getString("mail") universe@189: } universe@189: universe@189: private fun ResultSet.containsUserInfo(): Boolean { universe@189: getInt("userid") universe@189: return !wasNull() universe@189: } universe@189: universe@189: private fun ResultSet.extractOptionalUser() = if (containsUserInfo()) extractUser() else null universe@189: universe@189: override fun listUsers() = universe@189: withStatement("$userQuery where userid > 0 order by username") { universe@189: queryAll { it.extractUser() } universe@189: } universe@189: universe@189: override fun findUser(id: Int): User? = universe@189: withStatement("$userQuery where userid = ?") { universe@189: setInt(1, id) universe@189: querySingle { it.extractUser() } universe@189: } universe@189: universe@189: override fun findUserByName(username: String): User? = universe@189: withStatement("$userQuery where lower(username) = lower(?)") { universe@189: setString(1, username) universe@189: querySingle { it.extractUser() } universe@189: } universe@189: universe@189: override fun insertUser(user: User) { universe@189: withStatement("insert into lpit_user (username, lastname, givenname, mail) values (?, ?, ?, ?)") { universe@189: with(user) { universe@189: setStringSafe(1, username) universe@189: setStringOrNull(2, lastname) universe@189: setStringOrNull(3, givenname) universe@189: setStringOrNull(4, mail) universe@167: } universe@189: executeUpdate() universe@167: } universe@167: } universe@167: universe@189: override fun updateUser(user: User) { universe@189: withStatement("update lpit_user set lastname = ?, givenname = ?, mail = ? where userid = ?") { universe@189: with(user) { universe@189: setStringOrNull(1, lastname) universe@189: setStringOrNull(2, givenname) universe@189: setStringOrNull(3, mail) universe@189: setInt(4, id) universe@189: } universe@189: executeUpdate() universe@167: } universe@167: } universe@225: // universe@167: universe@167: // universe@167: //language=SQL universe@225: private val versionQuery = "select versionid, project, name, node, ordinal, status, release, eol from lpit_version" universe@167: universe@189: private fun ResultSet.extractVersion() = universe@189: Version(getInt("versionid"), getInt("project")).apply { universe@189: name = getString("name") universe@189: node = getString("node") universe@189: ordinal = getInt("ordinal") universe@225: release = getDate("release") universe@225: eol = getDate("eol") universe@189: status = getEnum("status") universe@189: } universe@189: universe@189: override fun listVersions(project: Project): List = universe@189: withStatement("$versionQuery where project = ? order by ordinal desc, lower(name) desc") { universe@189: setInt(1, project.id) universe@189: queryAll { it.extractVersion() } universe@189: } universe@189: universe@189: override fun listVersionSummaries(project: Project): List = universe@189: withStatement( universe@231: """with universe@231: version_map as ( universe@231: select issueid, status, resolved as versionid, true as isresolved from lpit_issue universe@231: union all universe@231: select issueid, status, affected as versionid, false as isresolved from lpit_issue universe@231: ), issues as ( universe@231: select versionid, phase, isresolved, count(issueid) as total from version_map universe@184: join lpit_issue_phases using (status) universe@184: group by versionid, phase, isresolved universe@184: ), universe@184: summary as ( universe@184: select versionid, phase, isresolved, total universe@184: from lpit_version v universe@184: left join issues using (versionid) universe@184: ) universe@225: select v.versionid, project, name, node, ordinal, status, release, eol, universe@190: ro.total as resolved_open, ra.total as resolved_active, rd.total as resolved_done, universe@190: ao.total as affected_open, aa.total as affected_active, ad.total as affected_done universe@190: from lpit_version v universe@190: left join summary ro on ro.versionid = v.versionid and ro.phase = 0 and ro.isresolved universe@190: left join summary ra on ra.versionid = v.versionid and ra.phase = 1 and ra.isresolved universe@190: left join summary rd on rd.versionid = v.versionid and rd.phase = 2 and rd.isresolved universe@190: left join summary ao on ao.versionid = v.versionid and ao.phase = 0 and not ao.isresolved universe@190: left join summary aa on aa.versionid = v.versionid and aa.phase = 1 and not aa.isresolved universe@190: left join summary ad on ad.versionid = v.versionid and ad.phase = 2 and not ad.isresolved universe@190: where v.project = ? universe@203: order by ordinal desc, lower(name) desc universe@189: """.trimIndent() universe@189: ) { universe@189: setInt(1, project.id) universe@190: queryAll { rs -> universe@190: VersionSummary(rs.extractVersion()).apply { universe@190: reportedTotal.open = rs.getInt("affected_open") universe@190: reportedTotal.active = rs.getInt("affected_active") universe@190: reportedTotal.done = rs.getInt("affected_done") universe@190: resolvedTotal.open = rs.getInt("resolved_open") universe@190: resolvedTotal.active = rs.getInt("resolved_active") universe@190: resolvedTotal.done = rs.getInt("resolved_done") universe@190: } universe@184: } universe@189: } universe@184: universe@189: override fun findVersion(id: Int): Version? = universe@189: withStatement("$versionQuery where versionid = ?") { universe@189: setInt(1, id) universe@189: querySingle { it.extractVersion() } universe@189: } universe@167: universe@189: override fun findVersionByNode(project: Project, node: String): Version? = universe@189: withStatement("$versionQuery where project = ? and node = ?") { universe@189: setInt(1, project.id) universe@189: setString(2, node) universe@189: querySingle { it.extractVersion() } universe@189: } universe@167: universe@167: override fun insertVersion(version: Version) { universe@225: withStatement("insert into lpit_version (name, node, ordinal, status, project, release, eol) values (?, ?, ?, ?::version_status, ?, ?, ?)") { universe@189: with(version) { universe@189: setStringSafe(1, name) universe@189: setStringSafe(2, node) universe@189: setInt(3, ordinal) universe@189: setEnum(4, status) universe@189: setInt(5, version.projectid) universe@225: setDateOrNull(6, version.release) universe@225: setDateOrNull(7, version.eol) universe@189: } universe@189: executeUpdate() universe@189: } universe@189: universe@167: } universe@167: universe@167: override fun updateVersion(version: Version) { universe@225: withStatement("update lpit_version set name = ?, node = ?, ordinal = ?, status = ?::version_status, release=?,eol=? where versionid = ?") { universe@189: with(version) { universe@189: setStringSafe(1, name) universe@189: setStringSafe(2, node) universe@189: setInt(3, ordinal) universe@189: setEnum(4, status) universe@225: setDateOrNull(5, version.release) universe@225: setDateOrNull(6, version.eol) universe@225: setInt(7, id) universe@189: } universe@189: executeUpdate() universe@189: } universe@167: } universe@225: // universe@167: universe@167: // universe@167: //language=SQL universe@167: private val componentQuery = universe@167: """ universe@227: select id, project, name, node, color, ordinal, description, active, universe@167: userid, username, givenname, lastname, mail universe@167: from lpit_component universe@167: left join lpit_user on lead = userid universe@189: """.trimIndent() universe@167: universe@189: private fun ResultSet.extractComponent(): Component = universe@189: Component(getInt("id"), getInt("project")).apply { universe@189: name = getString("name") universe@189: node = getString("node") universe@189: color = try { universe@189: WebColor(getString("color")) universe@189: } catch (ex: IllegalArgumentException) { universe@189: WebColor("000000") universe@189: } universe@189: ordinal = getInt("ordinal") universe@189: description = getString("description") universe@227: active = getBoolean("active") universe@189: lead = extractOptionalUser() universe@189: } universe@189: universe@189: private fun PreparedStatement.setComponent(index: Int, component: Component): Int { universe@189: with(component) { universe@189: var i = index universe@189: setStringSafe(i++, name) universe@189: setStringSafe(i++, node) universe@189: setStringSafe(i++, color.hex) universe@189: setInt(i++, ordinal) universe@189: setStringOrNull(i++, description) universe@227: setBoolean(i++, active) universe@189: setIntOrNull(i++, lead?.id) universe@189: return i universe@189: } universe@167: } universe@189: universe@189: override fun listComponents(project: Project): List = universe@189: withStatement("$componentQuery where project = ? order by ordinal, lower(name)") { universe@189: setInt(1, project.id) universe@189: queryAll { it.extractComponent() } universe@189: } universe@189: universe@189: override fun listComponentSummaries(project: Project): List = universe@189: withStatement( universe@184: """ universe@184: with issues as ( universe@184: select component, phase, count(issueid) as total universe@184: from lpit_issue universe@184: join lpit_issue_phases using (status) universe@184: group by component, phase universe@184: ), universe@184: summary as ( universe@184: select c.id, phase, total universe@184: from lpit_component c universe@184: left join issues i on c.id = i.component universe@184: ) universe@227: select c.id, project, name, node, color, ordinal, description, active, universe@190: userid, username, givenname, lastname, mail, universe@227: open.total as open, wip.total as wip, done.total as done universe@184: from lpit_component c universe@184: left join lpit_user on lead = userid universe@190: left join summary open on c.id = open.id and open.phase = 0 universe@227: left join summary wip on c.id = wip.id and wip.phase = 1 universe@190: left join summary done on c.id = done.id and done.phase = 2 universe@190: where c.project = ? universe@184: order by ordinal, name universe@189: """.trimIndent() universe@189: ) { universe@189: setInt(1, project.id) universe@190: queryAll { rs -> universe@190: ComponentSummary(rs.extractComponent()).apply { universe@190: issueSummary.open = rs.getInt("open") universe@227: issueSummary.active = rs.getInt("wip") universe@190: issueSummary.done = rs.getInt("done") universe@190: } universe@184: } universe@189: } universe@184: universe@189: override fun findComponent(id: Int): Component? = universe@189: withStatement("$componentQuery where id = ?") { universe@189: setInt(1, id) universe@189: querySingle { it.extractComponent() } universe@189: } universe@167: universe@189: override fun findComponentByNode(project: Project, node: String): Component? = universe@189: withStatement("$componentQuery where project = ? and node = ?") { universe@189: setInt(1, project.id) universe@189: setString(2, node) universe@189: querySingle { it.extractComponent() } universe@189: } universe@167: universe@167: override fun insertComponent(component: Component) { universe@227: withStatement("insert into lpit_component (name, node, color, ordinal, description, active, lead, project) values (?, ?, ?, ?, ?, ?, ?, ?)") { universe@189: val col = setComponent(1, component) universe@189: setInt(col, component.projectid) universe@189: executeUpdate() universe@189: } universe@167: } universe@167: universe@167: override fun updateComponent(component: Component) { universe@227: withStatement("update lpit_component set name = ?, node = ?, color = ?, ordinal = ?, description = ?, active = ?, lead = ? where id = ?") { universe@189: val col = setComponent(1, component) universe@189: setInt(col, component.id) universe@189: executeUpdate() universe@167: } universe@167: } universe@167: universe@225: // universe@189: universe@225: // universe@167: universe@167: //language=SQL universe@167: private val projectQuery = universe@167: """ universe@175: select projectid, name, node, ordinal, description, repourl, universe@167: userid, username, lastname, givenname, mail universe@167: from lpit_project universe@167: left join lpit_user owner on lpit_project.owner = owner.userid universe@189: """.trimIndent() universe@167: universe@189: private fun ResultSet.extractProject() = universe@189: Project(getInt("projectid")).apply { universe@189: name = getString("name") universe@189: node = getString("node") universe@189: ordinal = getInt("ordinal") universe@189: description = getString("description") universe@189: repoUrl = getString("repourl") universe@189: owner = extractOptionalUser() universe@189: } universe@189: universe@189: private fun PreparedStatement.setProject(index: Int, project: Project): Int { universe@189: var i = index universe@189: with(project) { universe@189: setStringSafe(i++, name) universe@189: setStringSafe(i++, node) universe@189: setInt(i++, ordinal) universe@189: setStringOrNull(i++, description) universe@189: setStringOrNull(i++, repoUrl) universe@189: setIntOrNull(i++, owner?.id) universe@189: } universe@189: return i universe@167: } universe@189: universe@189: override fun listProjects(): List = universe@189: withStatement("$projectQuery order by ordinal, lower(name)") { universe@189: queryAll { it.extractProject() } universe@189: } universe@189: universe@189: override fun findProject(id: Int): Project? = universe@189: withStatement("$projectQuery where projectid = ?") { universe@189: setInt(1, id) universe@189: querySingle { it.extractProject() } universe@189: } universe@189: universe@189: override fun findProjectByNode(node: String): Project? = universe@189: withStatement("$projectQuery where node = ?") { universe@189: setString(1, node) universe@189: querySingle { it.extractProject() } universe@189: } universe@189: universe@189: override fun insertProject(project: Project) { universe@189: withStatement("insert into lpit_project (name, node, ordinal, description, repourl, owner) values (?, ?, ?, ?, ?, ?)") { universe@189: setProject(1, project) universe@189: executeUpdate() universe@189: } universe@167: } universe@189: universe@189: override fun updateProject(project: Project) { universe@189: withStatement("update lpit_project set name = ?, node = ?, ordinal = ?, description = ?, repourl = ?, owner = ? where projectid = ?") { universe@189: val col = setProject(1, project) universe@189: setInt(col, project.id) universe@189: executeUpdate() universe@189: } universe@167: } universe@189: universe@189: override fun collectIssueSummary(project: Project): IssueSummary = universe@189: withStatement( universe@167: """ universe@167: select phase, count(*) as total universe@167: from lpit_issue universe@167: join lpit_issue_phases using(status) universe@167: where project = ? universe@167: group by phase universe@189: """.trimIndent() universe@189: ) { universe@189: setInt(1, project.id) universe@189: executeQuery().use { universe@189: val summary = IssueSummary() universe@189: while (it.next()) { universe@189: val phase = it.getInt("phase") universe@189: val total = it.getInt("total") universe@189: when (phase) { universe@189: 0 -> summary.open = total universe@189: 1 -> summary.active = total universe@189: 2 -> summary.done = total universe@189: } universe@167: } universe@189: summary universe@167: } universe@167: } universe@167: universe@225: // universe@189: universe@225: // universe@167: universe@167: //language=SQL universe@167: private val issueQuery = universe@167: """ universe@167: select issueid, universe@167: i.project, p.name as projectname, p.node as projectnode, universe@167: component, c.name as componentname, c.node as componentnode, universe@167: status, category, subject, i.description, universe@167: userid, username, givenname, lastname, mail, universe@231: created, updated, eta, affected, resolved universe@167: from lpit_issue i universe@167: join lpit_project p on i.project = projectid universe@167: left join lpit_component c on component = c.id universe@167: left join lpit_user on userid = assignee universe@189: """.trimIndent() universe@167: universe@189: private fun ResultSet.extractIssue(): Issue { universe@189: val proj = Project(getInt("project")).apply { universe@189: name = getString("projectname") universe@189: node = getString("projectnode") universe@189: } universe@189: val comp = getInt("component").let { universe@189: if (wasNull()) null else universe@189: Component(it, proj.id).apply { universe@189: name = getString("componentname") universe@189: node = getString("componentnode") universe@189: } universe@189: } universe@189: val issue = Issue(getInt("issueid"), proj).apply { universe@189: component = comp universe@189: status = getEnum("status") universe@189: category = getEnum("category") universe@189: subject = getString("subject") universe@189: description = getString("description") universe@189: assignee = extractOptionalUser() universe@189: created = getTimestamp("created") universe@189: updated = getTimestamp("updated") universe@189: eta = getDate("eta") universe@231: affected = getInt("affected").takeIf { it > 0 }?.let { findVersion(it) } universe@231: resolved = getInt("resolved").takeIf { it > 0 }?.let { findVersion(it) } universe@189: } universe@189: universe@189: return issue universe@167: } universe@167: universe@189: private fun PreparedStatement.setIssue(index: Int, issue: Issue): Int { universe@189: var i = index universe@189: with(issue) { universe@189: setIntOrNull(i++, component?.id) universe@189: setEnum(i++, status) universe@189: setEnum(i++, category) universe@189: setStringSafe(i++, subject) universe@189: setStringOrNull(i++, description) universe@189: setIntOrNull(i++, assignee?.id) universe@189: setDateOrNull(i++, eta) universe@231: setIntOrNull(i++, affected?.id) universe@231: setIntOrNull(i++, resolved?.id) universe@189: } universe@189: return i universe@167: } universe@167: universe@189: override fun listIssues(filter: IssueFilter): List = universe@189: withStatement( universe@231: """$issueQuery where universe@231: (not ? or i.project = ?) and universe@231: (not ? or ? in (resolved, affected)) and (not ? or (resolved is null and affected is null)) and universe@183: (not ? or component = ?) and (not ? or component is null) universe@189: """.trimIndent() universe@189: ) { universe@189: fun applyFilter(filter: Filter, fflag: Int, nflag: Int, idcol: Int) { universe@189: when (filter) { universe@189: is AllFilter -> { universe@189: setBoolean(fflag, false) universe@189: setBoolean(nflag, false) universe@189: setInt(idcol, 0) universe@189: } universe@189: is NoneFilter -> { universe@189: setBoolean(fflag, false) universe@189: setBoolean(nflag, true) universe@189: setInt(idcol, 0) universe@189: } universe@189: is SpecificFilter -> { universe@189: setBoolean(fflag, true) universe@189: setBoolean(nflag, false) universe@189: setInt(idcol, filter.obj.id) universe@189: } universe@189: else -> { universe@189: TODO("Implement range filter.") universe@189: } universe@189: } universe@189: } universe@189: when (filter.project) { universe@189: is AllFilter -> { universe@189: setBoolean(1, false) universe@189: setInt(2, 0) universe@189: } universe@189: is SpecificFilter -> { universe@189: setBoolean(1, true) universe@189: setInt(2, filter.project.obj.id) universe@189: } universe@189: else -> throw IllegalArgumentException() universe@189: } universe@189: applyFilter(filter.version, 3, 5, 4) universe@189: applyFilter(filter.component, 6, 8, 7) universe@167: universe@189: queryAll { it.extractIssue() } universe@189: } universe@167: universe@189: override fun findIssue(id: Int): Issue? = universe@189: withStatement("$issueQuery where issueid = ?") { universe@189: setInt(1, id) universe@189: querySingle { it.extractIssue() } universe@189: } universe@189: universe@189: override fun insertIssue(issue: Issue): Int { universe@189: val id = withStatement( universe@167: """ universe@231: insert into lpit_issue (component, status, category, subject, description, assignee, eta, affected, resolved, project) universe@232: values (?, ?::issue_status, ?::issue_category, ?, ?, ?, ?, ?, ?, ?) universe@167: returning issueid universe@189: """.trimIndent() universe@189: ) { universe@189: val col = setIssue(1, issue) universe@189: setInt(col, issue.project.id) universe@189: querySingle { it.getInt(1) }!! universe@167: } universe@184: return id universe@167: } universe@167: universe@167: override fun updateIssue(issue: Issue) { universe@189: withStatement( universe@189: """ universe@189: update lpit_issue set updated = now(), universe@189: component = ?, status = ?::issue_status, category = ?::issue_category, subject = ?, universe@231: description = ?, assignee = ?, eta = ?, affected = ?, resolved = ? universe@189: where issueid = ? universe@189: """.trimIndent() universe@189: ) { universe@189: val col = setIssue(1, issue) universe@189: setInt(col, issue.id) universe@189: executeUpdate() universe@189: } universe@167: } universe@167: universe@232: override fun insertHistoryEvent(issue: Issue, newId: Int) { universe@232: val type = if (newId > 0) IssueHistoryType.New else IssueHistoryType.Update universe@232: val issueid = if (newId > 0) newId else issue.id universe@232: universe@232: val eventid = universe@232: withStatement("insert into lpit_issue_history_event(issueid, type) values (?,?::issue_history_event) returning eventid") { universe@232: setInt(1, issueid) universe@232: setEnum(2, type) universe@232: querySingle { it.getInt(1) }!! universe@232: } universe@232: withStatement( universe@232: """ universe@232: insert into lpit_issue_history_data (component, status, category, subject, description, assignee, eta, affected, resolved, eventid) universe@232: values (?, ?::issue_status, ?::issue_category, ?, ?, ?, ?, ?, ?, ?) universe@232: """.trimIndent() universe@232: ) { universe@232: setStringOrNull(1, issue.component?.name) universe@232: setEnum(2, issue.status) universe@232: setEnum(3, issue.category) universe@232: setString(4, issue.subject) universe@232: setStringOrNull(5, issue.description) universe@232: setStringOrNull(6, issue.assignee?.shortDisplayname) universe@232: setDateOrNull(7, issue.eta) universe@232: setStringOrNull(8, issue.affected?.name) universe@232: setStringOrNull(9, issue.resolved?.name) universe@232: setInt(10, eventid) universe@232: executeUpdate() universe@232: } universe@232: } universe@232: universe@225: // universe@167: universe@225: // universe@167: universe@189: private fun ResultSet.extractIssueComment() = universe@189: IssueComment(getInt("commentid"), getInt("issueid")).apply { universe@189: created = getTimestamp("created") universe@189: updated = getTimestamp("updated") universe@189: updateCount = getInt("updatecount") universe@189: comment = getString("comment") universe@189: author = extractOptionalUser() universe@189: } universe@189: universe@189: override fun listComments(issue: Issue): List = universe@189: withStatement("select * from lpit_issue_comment left join lpit_user using (userid) where issueid = ? order by created") { universe@189: setInt(1, issue.id) universe@189: queryAll { it.extractIssueComment() } universe@189: } universe@189: universe@207: override fun findComment(id: Int): IssueComment? = universe@207: withStatement("select * from lpit_issue_comment left join lpit_user using (userid) where commentid = ?") { universe@207: setInt(1, id) universe@207: querySingle { it.extractIssueComment() } universe@207: } universe@207: universe@232: override fun insertComment(issueComment: IssueComment): Int = universe@189: useStatement("update lpit_issue set updated = now() where issueid = ?") { updateIssueDate -> universe@232: withStatement("insert into lpit_issue_comment (issueid, comment, userid) values (?, ? ,?) returning commentid") { universe@189: with(issueComment) { universe@189: updateIssueDate.setInt(1, issueid) universe@189: setInt(1, issueid) universe@189: setStringSafe(2, comment) universe@189: setIntOrNull(3, author?.id) universe@189: } universe@232: val commentid = querySingle { it.getInt(1) }!! universe@189: updateIssueDate.executeUpdate() universe@232: commentid universe@167: } universe@167: } universe@207: universe@207: override fun updateComment(issueComment: IssueComment) { universe@207: useStatement("update lpit_issue set updated = now() where issueid = ?") { updateIssueDate -> universe@207: withStatement("update lpit_issue_comment set comment = ?, updatecount = updatecount + 1, updated = now() where commentid = ?") { universe@207: with(issueComment) { universe@207: updateIssueDate.setInt(1, issueid) universe@207: setStringSafe(1, comment) universe@207: setInt(2, id) universe@207: } universe@207: executeUpdate() universe@207: updateIssueDate.executeUpdate() universe@207: } universe@207: } universe@207: } universe@232: universe@232: universe@232: override fun insertHistoryEvent(issueComment: IssueComment, newId: Int) { universe@232: val type = if (newId > 0) IssueHistoryType.NewComment else IssueHistoryType.UpdateComment universe@232: val commentid = if (newId > 0) newId else issueComment.id universe@232: universe@232: val eventid = universe@232: withStatement("insert into lpit_issue_history_event(issueid, type) values (?,?::issue_history_event) returning eventid") { universe@232: setInt(1, issueComment.issueid) universe@232: setEnum(2, type) universe@232: querySingle { it.getInt(1) }!! universe@232: } universe@232: withStatement("insert into lpit_issue_comment_history (commentid, eventid, comment) values (?,?,?)") { universe@232: setInt(1, commentid) universe@232: setInt(2, eventid) universe@232: setString(3, issueComment.comment) universe@232: executeUpdate() universe@232: } universe@232: } universe@232: universe@225: // universe@235: universe@235: // universe@235: universe@235: override fun listIssueHistory(projectId: Int, days: Int) = universe@235: withStatement( universe@235: """ universe@235: select evt.*, evtdata.* universe@235: from lpit_issue_history_event evt universe@235: join lpit_issue using (issueid) universe@235: join lpit_issue_history_data evtdata using (eventid) universe@235: where project = ? universe@235: and time > now() - (? * interval '1' day) universe@235: order by time desc universe@235: """.trimIndent() universe@235: ) { universe@235: setInt(1, projectId) universe@235: setInt(2, days) universe@235: queryAll { rs-> universe@235: with(rs) { universe@235: IssueHistoryEntry( universe@235: getTimestamp("time"), universe@235: getEnum("type"), universe@235: IssueHistoryData(getInt("issueid"), universe@235: component = getString("component") ?: "", universe@235: status = getEnum("status"), universe@235: category = getEnum("category"), universe@235: subject = getString("subject"), universe@235: description = getString("description") ?: "", universe@235: assignee = getString("assignee") ?: "", universe@235: eta = getDate("eta"), universe@235: affected = getString("affected") ?: "", universe@235: resolved = getString("resolved") ?: "" universe@235: ) universe@235: ) universe@235: } universe@235: } universe@235: } universe@235: universe@235: // universe@167: }