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@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@190: // universe@167: universe@167: // universe@167: //language=SQL universe@167: private val versionQuery = "select versionid, project, name, node, ordinal, status 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@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@184: """ universe@184: with version_map(issueid, versionid, isresolved) as ( universe@190: select issueid, versionid, true universe@184: from lpit_issue_resolved_version universe@184: union universe@190: select issueid, versionid, false universe@184: from lpit_issue_affected_version universe@184: ), universe@184: issues as ( universe@184: select versionid, phase, isresolved, count(issueid) as total universe@184: from lpit_issue universe@184: join version_map using (issueid) 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@190: select v.versionid, project, name, node, ordinal, status, 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@184: order by ordinal, name 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@189: withStatement("insert into lpit_version (name, node, ordinal, status, project) 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@189: } universe@189: executeUpdate() universe@189: } universe@189: universe@167: } universe@167: universe@167: override fun updateVersion(version: Version) { universe@189: withStatement("update lpit_version set name = ?, node = ?, ordinal = ?, status = ?::version_status 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@189: setInt(5, id) universe@189: } universe@189: executeUpdate() universe@189: } universe@167: } universe@190: // universe@167: universe@167: // universe@167: //language=SQL universe@167: private val componentQuery = universe@167: """ universe@167: select id, project, name, node, color, ordinal, description, 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@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@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@184: select c.id, project, name, node, color, ordinal, description, universe@190: userid, username, givenname, lastname, mail, universe@190: open.total as open, active.total as active, 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@190: left join summary active on c.id = active.id and active.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@190: issueSummary.active = rs.getInt("active") 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@189: withStatement("insert into lpit_component (name, node, color, ordinal, description, 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@189: withStatement("update lpit_component set name = ?, node = ?, color = ?, ordinal = ?, description = ?, 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@189: // universe@189: universe@189: // 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@189: // universe@189: universe@189: // 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@167: created, updated, eta 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@189: } universe@189: universe@189: fun versionQuery(table: String) = universe@167: """ universe@167: select versionid, project, name, status, ordinal, node universe@189: from lpit_version join $table using (versionid) universe@167: where issueid = ? universe@167: order by ordinal, name universe@189: """.trimIndent() universe@189: universe@189: issue.affectedVersions = withStatement(versionQuery("lpit_issue_affected_version")) { universe@189: setInt(1, issue.id) universe@189: queryAll { it.extractVersion() } universe@189: } universe@189: issue.resolvedVersions = withStatement(versionQuery("lpit_issue_resolved_version")) { universe@189: setInt(1, issue.id) universe@189: queryAll { it.extractVersion() } 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@189: } universe@189: return i universe@167: } universe@167: universe@189: override fun listIssues(filter: IssueFilter): List = universe@189: withStatement( universe@167: """ universe@167: with issue_version as ( universe@167: select issueid, versionid from lpit_issue_affected_version universe@167: union select issueid, versionid from lpit_issue_resolved_version universe@183: ), universe@189: filtered_issues as ( universe@183: select distinct issueid from lpit_issue universe@183: left join issue_version using (issueid) universe@183: where universe@183: (not ? or project = ?) and universe@183: (not ? or versionid = ?) and (not ? or versionid is null) and universe@183: (not ? or component = ?) and (not ? or component is null) universe@183: ) universe@189: $issueQuery join filtered_issues using (issueid) 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: private fun insertVersionInfo(id: Int, issue: Issue) { universe@189: withStatement("insert into lpit_issue_affected_version (issueid, versionid) values (?,?)") { universe@189: setInt(1, id) universe@189: issue.affectedVersions.forEach { universe@189: setInt(2, it.id) universe@189: executeUpdate() universe@167: } universe@189: } universe@189: withStatement("insert into lpit_issue_resolved_version (issueid, versionid) values (?,?)") { universe@189: setInt(1, id) universe@189: issue.resolvedVersions.forEach { universe@189: setInt(2, it.id) universe@189: executeUpdate() universe@167: } universe@167: } universe@167: } universe@167: universe@189: override fun insertIssue(issue: Issue): Int { universe@189: val id = withStatement( universe@167: """ universe@167: insert into lpit_issue (component, status, category, subject, description, assignee, eta, project) universe@167: 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@188: insertVersionInfo(id, issue) 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@189: description = ?, assignee = ?, eta = ? 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@189: universe@167: // TODO: improve by only inserting / deleting changed version information universe@189: withStatement("delete from lpit_issue_affected_version where issueid = ?") { universe@189: setInt(1, issue.id) universe@189: executeUpdate() universe@189: } universe@189: withStatement("delete from lpit_issue_resolved_version where issueid = ?") { universe@189: setInt(1, issue.id) universe@189: executeUpdate() universe@189: } universe@188: insertVersionInfo(issue.id, issue) universe@167: } universe@167: universe@189: // universe@167: universe@189: // 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@189: override fun insertComment(issueComment: IssueComment) { universe@189: useStatement("update lpit_issue set updated = now() where issueid = ?") { updateIssueDate -> universe@189: withStatement("insert into lpit_issue_comment (issueid, comment, userid) values (?, ? ,?)") { 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@189: executeUpdate() universe@189: updateIssueDate.executeUpdate() universe@167: } universe@167: } universe@167: } universe@189: // universe@167: }