changeset
fixes #137 - leaking prepared statements
Tue, 11 May 2021 16:00:28 +0200
- author
- Mike Becker <universe@uap-core.de>
- date
- Tue, 11 May 2021 16:00:28 +0200
- changeset 189
- f7de8158b41c
- parent 188
- 2979436edd9e
- child 190
- a83f1ab56898
fixes #137 - leaking prepared statements
| src/main/kotlin/de/uapcore/lightpit/dao/PostgresDataAccessObject.kt | file | annotate | diff | comparison | revisions |
--- a/src/main/kotlin/de/uapcore/lightpit/dao/PostgresDataAccessObject.kt Tue Apr 06 09:08:54 2021 +0200 +++ b/src/main/kotlin/de/uapcore/lightpit/dao/PostgresDataAccessObject.kt Tue May 11 16:00:28 2021 +0200 @@ -31,147 +31,126 @@ 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 { - //<editor-fold desc="User"> - private fun selectUserInfo( - rs: ResultSet, - idColumn: String = "userid", - usernameColumn: String = "username", - givennameColumn: String = "givenname", - lastnameColumn: String = "lastname", - mailColumn: String = "mail" - ): User? { - val idval = rs.getInt(idColumn) - return if (rs.wasNull()) null else { - User(idval).apply { - username = rs.getString(usernameColumn) - givenname = rs.getString(givennameColumn) - lastname = rs.getString(lastnameColumn) - mail = rs.getString(mailColumn) + /** + * 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() } - private fun selectUsers(stmt: PreparedStatement) = sequence { - stmt.executeQuery().use { rs -> - while (rs.next()) selectUserInfo(rs)?.let { yield(it) } - } + /** + * 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 val stmtUsers by lazy { - connection.prepareStatement( - """${userQuery} - where userid > 0 - order by username - """ - ) - } - private val stmtUserByID by lazy { - connection.prepareStatement( - """${userQuery} - where userid = ? - """ - ) + private fun ResultSet.extractUser() = User(getInt("userid")).apply { + username = getString("username") + givenname = getString("givenname") + lastname = getString("lastname") + mail = getString("mail") } - private val stmtUserByName by lazy { - connection.prepareStatement( - """${userQuery} - where lower(username) = lower(?) - """ - ) - } - private val stmtInsertUser by lazy { - connection.prepareStatement( - "insert into lpit_user (username, lastname, givenname, mail) values (?, ?, ?, ?)" - ) - } - private val stmtUpdateUser by lazy { - connection.prepareStatement( - "update lpit_user set lastname = ?, givenname = ?, mail = ? where userid = ?" - ) + + private fun ResultSet.containsUserInfo(): Boolean { + getInt("userid") + return !wasNull() } - override fun listUsers() = selectUsers(stmtUsers).toList() - override fun findUser(id: Int): User? { - stmtUserByID.setInt(1, id) - return selectUsers(stmtUserByID).firstOrNull() - } + 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 findUserByName(username: String): User? { - stmtUserByName.setString(1, username) - return selectUsers(stmtUserByName).firstOrNull() - } + 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) { - with(user) { - stmtInsertUser.setStringSafe(1, username) - stmtInsertUser.setStringOrNull(2, lastname) - stmtInsertUser.setStringOrNull(3, givenname) - stmtInsertUser.setStringOrNull(4, mail) + 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() } - stmtInsertUser.execute() } override fun updateUser(user: User) { - with(user) { - stmtUpdateUser.setStringOrNull(1, lastname) - stmtUpdateUser.setStringOrNull(2, givenname) - stmtUpdateUser.setStringOrNull(3, mail) - stmtUpdateUser.setInt(4, id) + 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() } - stmtUpdateUser.execute() } //</editor-fold> //<editor-fold desc="Version"> - - private fun obtainVersion(rs: ResultSet) = - Version(rs.getInt("versionid"), rs.getInt("project")).apply { - name = rs.getString("name") - node = rs.getString("node") - ordinal = rs.getInt("ordinal") - status = rs.getEnum("status") - } - - private fun selectVersions(stmt: PreparedStatement) = sequence { - stmt.executeQuery().use { rs -> - while (rs.next()) { - yield(obtainVersion(rs)) - } - } - } - - private fun setVersionFields(stmt: PreparedStatement, obj: Version): Int { - with(obj) { - stmt.setStringSafe(1, name) - stmt.setStringSafe(2, node) - stmt.setInt(3, ordinal) - stmt.setEnum(4, status) - } - return 5 - } - //language=SQL private val versionQuery = "select versionid, project, name, node, ordinal, status from lpit_version" - private val stmtVersions by lazy { - connection.prepareStatement( - """${versionQuery} - where project = ? - order by ordinal desc, lower(name) desc - """ - ) - } - private val stmtVersionSummaries by lazy { - connection.prepareStatement( + 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, 1 @@ -197,51 +176,13 @@ from lpit_version join summary using (versionid) order by ordinal, name - """ - ) - } - private val stmtVersionByID by lazy { - connection.prepareStatement( - """${versionQuery} - where versionid = ? - """ - ) - } - private val stmtVersionByNode by lazy { - connection.prepareStatement( - """${versionQuery} - where project = ? and node = ? - """ - ) - } - private val stmtInsertVersion by lazy { - connection.prepareStatement( - """ - insert into lpit_version (name, node, ordinal, status, project) - values (?, ?, ?, ?::version_status, ?) - """ - ) - } - private val stmtUpdateVersion by lazy { - connection.prepareStatement( - """ - update lpit_version set name = ?, node = ?, ordinal = ?, status = ?::version_status - where versionid = ? - """ - ) - } - - override fun listVersions(project: Project): List<Version> { - stmtVersions.setInt(1, project.id) - return selectVersions(stmtVersions).toList() - } - - override fun listVersionSummaries(project: Project): List<VersionSummary> { - stmtVersionSummaries.setInt(1, project.id) - return sequence { - stmtVersionSummaries.executeQuery().use { rs -> - while (rs.next()) { - val versionSummary = VersionSummary(obtainVersion(rs)) + """.trimIndent() + ) { + setInt(1, project.id) + executeQuery().use { rs -> + sequence { + // TODO: fix bug: this extractor is not grouping the results + val versionSummary = VersionSummary(rs.extractVersion()) val phase = rs.getInt("phase") val total = rs.getInt("total") val issueSummary = @@ -252,71 +193,52 @@ 2 -> issueSummary.done = total } yield(versionSummary) - } + }.toList() } - }.toList() - } + } - override fun findVersion(id: Int): Version? { - stmtVersionByID.setInt(1, id) - return selectVersions(stmtVersionByID).firstOrNull() - } + override fun findVersion(id: Int): Version? = + withStatement("$versionQuery where versionid = ?") { + setInt(1, id) + querySingle { it.extractVersion() } + } - override fun findVersionByNode(project: Project, node: String): Version? { - stmtVersionByNode.setInt(1, project.id) - stmtVersionByNode.setString(2, node) - return selectVersions(stmtVersionByNode).firstOrNull() - } + 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) { - val col = setVersionFields(stmtInsertVersion, version) - stmtInsertVersion.setInt(col, version.projectid) - stmtInsertVersion.execute() + 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) { - val col = setVersionFields(stmtUpdateVersion, version) - stmtUpdateVersion.setInt(col, version.id) - stmtUpdateVersion.execute() + 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"> - - private fun obtainComponent(rs: ResultSet): Component = - Component(rs.getInt("id"), rs.getInt("project")).apply { - name = rs.getString("name") - node = rs.getString("node") - color = try { - WebColor(rs.getString("color")) - } catch (ex: IllegalArgumentException) { - WebColor("000000") - } - ordinal = rs.getInt("ordinal") - description = rs.getString("description") - lead = selectUserInfo(rs) - } - - private fun selectComponents(stmt: PreparedStatement) = sequence { - stmt.executeQuery().use { rs -> - while (rs.next()) { - yield(obtainComponent(rs)) - } - } - } - - private fun setComponentFields(stmt: PreparedStatement, obj: Component): Int { - with(obj) { - stmt.setStringSafe(1, name) - stmt.setStringSafe(2, node) - stmt.setStringSafe(3, color.hex) - stmt.setInt(4, ordinal) - stmt.setStringOrNull(5, description) - stmt.setIntOrNull(6, obj.lead?.id) - } - return 7 - } - //language=SQL private val componentQuery = """ @@ -324,18 +246,43 @@ 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 val stmtComponents by lazy { - connection.prepareStatement( - """${componentQuery} - where project = ? - order by ordinal, lower(name) - """ - ) + 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 + } } - private val stmtComponentSummaries by lazy { - connection.prepareStatement( + + 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 @@ -355,114 +302,58 @@ left join lpit_user on lead = userid join summary s on c.id = s.id order by ordinal, name - """ - ) - } - private val stmtComponentById by lazy { - connection.prepareStatement( - """${componentQuery} - where id = ? - """ - ) - } - private val stmtComponentByNode by lazy { - connection.prepareStatement( - """${componentQuery} - where project = ? and node = ? - """ - ) - } - private val stmtInsertComponent by lazy { - connection.prepareStatement( - """ - insert into lpit_component (name, node, color, ordinal, description, lead, project) - values (?, ?, ?, ?, ?, ?, ?) - """ - ) - } - private val stmtUpdateComponent by lazy { - connection.prepareStatement( - "update lpit_component set name = ?, node = ?, color = ?, ordinal = ?, description = ?, lead = ? where id = ?" - ) - } - - override fun listComponents(project: Project): List<Component> { - stmtComponents.setInt(1, project.id) - return selectComponents(stmtComponents).toList() - } - - override fun listComponentSummaries(project: Project): List<ComponentSummary> { - stmtComponentSummaries.setInt(1, project.id) - return sequence { - stmtComponentSummaries.executeQuery().use { rs -> - while (rs.next()) { - val componentSummary = ComponentSummary(obtainComponent(rs)) - val phase = rs.getInt("phase") - val total = rs.getInt("total") - when (phase) { - 0 -> componentSummary.issueSummary.open = total - 1 -> componentSummary.issueSummary.active = total - 2 -> componentSummary.issueSummary.done = total + """.trimIndent() + ) { + setInt(1, project.id) + executeQuery().use { rs -> + // TODO: fix bug: this extractor is not grouping the results + sequence { + val componentSummary = ComponentSummary(rs.extractComponent()).also { + val phase = rs.getInt("phase") + val total = rs.getInt("total") + when (phase) { + 0 -> it.issueSummary.open = total + 1 -> it.issueSummary.active = total + 2 -> it.issueSummary.done = total + } } yield(componentSummary) - } + }.toList() } - }.toList() - } + } - override fun findComponent(id: Int): Component? { - stmtComponentById.setInt(1, id) - return selectComponents(stmtComponentById).firstOrNull() - } + override fun findComponent(id: Int): Component? = + withStatement("$componentQuery where id = ?") { + setInt(1, id) + querySingle { it.extractComponent() } + } - override fun findComponentByNode(project: Project, node: String): Component? { - stmtComponentByNode.setInt(1, project.id) - stmtComponentByNode.setString(2, node) - return selectComponents(stmtComponentByNode).firstOrNull() - } + 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) { - val col = setComponentFields(stmtInsertComponent, component) - stmtInsertComponent.setInt(col, component.projectid) - stmtInsertComponent.execute() + 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) { - val col = setComponentFields(stmtUpdateComponent, component) - stmtUpdateComponent.setInt(col, component.id) - stmtUpdateComponent.execute() - } - - //</editor-fold> - - //<editor-fold desc="Project"> - - private fun selectProjects(stmt: PreparedStatement) = sequence { - stmt.executeQuery().use { rs -> - while (rs.next()) { - yield(Project(rs.getInt("projectid")).apply { - name = rs.getString("name") - node = rs.getString("node") - ordinal = rs.getInt("ordinal") - description = rs.getString("description") - repoUrl = rs.getString("repourl") - owner = selectUserInfo(rs) - }) - } + withStatement("update lpit_component set name = ?, node = ?, color = ?, ordinal = ?, description = ?, lead = ? where id = ?") { + val col = setComponent(1, component) + setInt(col, component.id) + executeUpdate() } } - private fun setProjectFields(stmt: PreparedStatement, obj: Project): Int { - with(obj) { - stmt.setStringSafe(1, name) - stmt.setStringSafe(2, node) - stmt.setInt(3, ordinal) - stmt.setStringOrNull(4, description) - stmt.setStringOrNull(5, repoUrl) - stmt.setIntOrNull(6, owner?.id) - } - return 7 - } +//</editor-fold> + +//<editor-fold desc="Project"> //language=SQL private val projectQuery = @@ -471,143 +362,92 @@ 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 val stmtProjects by lazy { - connection.prepareStatement( - """${projectQuery} - order by ordinal, lower(name) - """ - ) - } - private val stmtProjectByID by lazy { - connection.prepareStatement( - """${projectQuery} - where projectid = ? - """ - ) + 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 } - private val stmtProjectByNode by lazy { - connection.prepareStatement( - """${projectQuery} - where node = ? - """ - ) + + 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() + } } - private val stmtInsertProject by lazy { - connection.prepareStatement( - "insert into lpit_project (name, node, ordinal, description, repourl, owner) values (?, ?, ?, ?, ?, ?)" - ) + + 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() + } } - private val stmtUpdateProject by lazy { - connection.prepareStatement( - "update lpit_project set name = ?, node = ?, ordinal = ?, description = ?, repourl = ?, owner = ? where projectid = ?" - ) - } - private val stmtIssueSummary by lazy { - connection.prepareStatement( + + 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 - """ - ) - } - - override fun listProjects(): List<Project> { - return selectProjects(stmtProjects).toList() - } - - override fun findProject(id: Int): Project? { - stmtProjectByID.setInt(1, id) - return selectProjects(stmtProjectByID).firstOrNull() - } - - override fun findProjectByNode(node: String): Project? { - stmtProjectByNode.setString(1, node) - return selectProjects(stmtProjectByNode).firstOrNull() - } - - override fun insertProject(project: Project) { - setProjectFields(stmtInsertProject, project) - stmtInsertProject.execute() - } - - override fun updateProject(project: Project) { - val col = setProjectFields(stmtUpdateProject, project) - stmtUpdateProject.setInt(col, project.id) - stmtUpdateProject.execute() - } - - override fun collectIssueSummary(project: Project): IssueSummary { - stmtIssueSummary.setInt(1, project.id) - return stmtIssueSummary.executeQuery().use { rs -> - val summary = IssueSummary() - while (rs.next()) { - val phase = rs.getInt("phase") - val total = rs.getInt("total") - when (phase) { - 0 -> summary.open = total - 1 -> summary.active = total - 2 -> summary.done = total + """.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"> - - private fun selectIssues(stmt: PreparedStatement) = sequence { - stmt.executeQuery().use { rs -> - while (rs.next()) { - val proj = Project(rs.getInt("project")).apply { - name = rs.getString("projectname") - node = rs.getString("projectnode") - } - val comp = rs.getInt("component").let { - if (rs.wasNull()) null else - Component(it, proj.id).apply { - name = rs.getString("componentname") - node = rs.getString("componentnode") - } - } - val issue = Issue(rs.getInt("issueid"), proj).apply { - component = comp - status = rs.getEnum("status") - category = rs.getEnum("category") - subject = rs.getString("subject") - description = rs.getString("description") - assignee = selectUserInfo(rs) - created = rs.getTimestamp("created") - updated = rs.getTimestamp("updated") - eta = rs.getDate("eta") - } - queryAffectedVersions.setInt(1, issue.id) - issue.affectedVersions = selectVersions(queryAffectedVersions).toList() - queryResolvedVersions.setInt(1, issue.id) - issue.resolvedVersions = selectVersions(queryResolvedVersions).toList() - yield(issue) + summary } } - } - private fun setIssueFields(stmt: PreparedStatement, obj: Issue): Int { - with(obj) { - stmt.setIntOrNull(1, component?.id) - stmt.setEnum(2, status) - stmt.setEnum(3, category) - stmt.setStringSafe(4, subject) - stmt.setStringOrNull(5, description) - stmt.setIntOrNull(6, assignee?.id) - stmt.setDateOrNull(7, eta) - } - return 8 - } +//</editor-fold> + +//<editor-fold desc="Issue"> //language=SQL private val issueQuery = @@ -622,38 +462,73 @@ 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 val queryResolvedVersions by lazy { - connection.prepareStatement( + 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 v join lpit_issue_resolved_version using (versionid) + 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 val queryAffectedVersions by lazy { - connection.prepareStatement( - """ - select versionid, project, name, status, ordinal, node - from lpit_version join lpit_issue_affected_version using (versionid) - where issueid = ? - order by ordinal, name - """ - ) + 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 } - private val stmtIssues by lazy { - connection.prepareStatement( + 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 ), - filteterd_issues as ( + filtered_issues as ( select distinct issueid from lpit_issue left join issue_version using (issueid) where @@ -661,193 +536,145 @@ (not ? or versionid = ?) and (not ? or versionid is null) and (not ? or component = ?) and (not ? or component is null) ) - ${issueQuery} join filteterd_issues using (issueid) - """ - ) - } - - private val fproj = 1 - private val projectid = 2 - private val fversion = 3 - private val versionid = 4 - private val nversion = 5 - private val fcomp = 6 - private val component = 7 - private val ncomp = 8 + $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) - private fun <T : Entity> applyFilter(filter: Filter<T>, fflag: Int, nflag: Int, idcol: Int) { - when (filter) { - is AllFilter -> { - stmtIssues.setBoolean(fflag, false) - stmtIssues.setBoolean(nflag, false) - stmtIssues.setInt(idcol, 0) + 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() } - is NoneFilter -> { - stmtIssues.setBoolean(fflag, false) - stmtIssues.setBoolean(nflag, true) - stmtIssues.setInt(idcol, 0) - } - is SpecificFilter -> { - stmtIssues.setBoolean(fflag, true) - stmtIssues.setBoolean(nflag, false) - stmtIssues.setInt(idcol, filter.obj.id) - } - else -> { - TODO("Implement range filter.") + } + withStatement("insert into lpit_issue_resolved_version (issueid, versionid) values (?,?)") { + setInt(1, id) + issue.resolvedVersions.forEach { + setInt(2, it.id) + executeUpdate() } } } - override fun listIssues(filter: IssueFilter): List<Issue> { - when (filter.project) { - is AllFilter -> { - stmtIssues.setBoolean(fproj, false) - stmtIssues.setInt(projectid, 0) - } - is SpecificFilter -> { - stmtIssues.setBoolean(fproj, true) - stmtIssues.setInt(projectid, filter.project.obj.id) - } - else -> throw IllegalArgumentException() - } - applyFilter(filter.version, fversion, nversion, versionid) - applyFilter(filter.component, fcomp, ncomp, component) - - return selectIssues(stmtIssues).toList() - } - - private val stmtFindIssueByID by lazy { - connection.prepareStatement( - """${issueQuery} - where issueid = ? - """ - ) - } - private val stmtInsertIssue by lazy { - connection.prepareStatement( + 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 - """ - ) - } - private val stmtUpdateIssue by lazy { - connection.prepareStatement( - """ - update lpit_issue set updated = now(), - component = ?, status = ?::issue_status, category = ?::issue_category, subject = ?, - description = ?, assignee = ?, eta = ? - where issueid = ? - """ - ) - } - private val stmtInsertAffectedVersion by lazy { - connection.prepareStatement( - "insert into lpit_issue_affected_version (issueid, versionid) values (?,?)" - ) - } - private val stmtInsertResolvedVersion by lazy { - connection.prepareStatement( - "insert into lpit_issue_resolved_version (issueid, versionid) values (?,?)" - ) - } - private val stmtClearAffectedVersions by lazy { - connection.prepareStatement("delete from lpit_issue_affected_version where issueid = ?") - } - private val stmtClearResolvedVersions by lazy { - connection.prepareStatement("delete from lpit_issue_resolved_version where issueid = ?") - } - - override fun findIssue(id: Int): Issue? { - stmtFindIssueByID.setInt(1, id) - return selectIssues(stmtFindIssueByID).firstOrNull() - } - - private fun insertVersionInfo(id: Int, issue: Issue) { - stmtInsertAffectedVersion.setInt(1, id) - stmtInsertResolvedVersion.setInt(1, id) - issue.affectedVersions.forEach { - stmtInsertAffectedVersion.setInt(2, it.id) - stmtInsertAffectedVersion.execute() - } - issue.resolvedVersions.forEach { - stmtInsertResolvedVersion.setInt(2, it.id) - stmtInsertResolvedVersion.execute() - } - } - - override fun insertIssue(issue: Issue): Int { - val col = setIssueFields(stmtInsertIssue, issue) - stmtInsertIssue.setInt(col, issue.project.id) - val id = stmtInsertIssue.executeQuery().use { rs -> - rs.next() - rs.getInt(1) + """.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) { - val col = setIssueFields(stmtUpdateIssue, issue) - stmtUpdateIssue.setInt(col, issue.id) - stmtUpdateIssue.execute() + 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 - stmtClearAffectedVersions.setInt(1, issue.id) - stmtClearResolvedVersions.setInt(1, issue.id) - stmtClearAffectedVersions.execute() - stmtClearResolvedVersions.execute() + 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> + +//<editor-fold desc="IssueComment"> - //<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() + } - private fun selectComments(stmt: PreparedStatement) = sequence { - stmt.executeQuery().use { rs -> - while (rs.next()) { - yield(IssueComment(rs.getInt("commentid"), rs.getInt("issueid")).apply { - created = rs.getTimestamp("created") - updated = rs.getTimestamp("updated") - updateCount = rs.getInt("updatecount") - comment = rs.getString("comment") - author = selectUserInfo(rs) - }) + 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() } } } - - private val stmtComments by lazy { - connection.prepareStatement( - "select * from lpit_issue_comment left join lpit_user using (userid) where issueid = ? order by created" - ) - } - private val stmtInsertComment by lazy { - connection.prepareStatement( - "insert into lpit_issue_comment (issueid, comment, userid) values (?, ? ,?)" - ) - } - private val stmtUpdateIssueDate by lazy { - connection.prepareStatement( - "update lpit_issue set updated = now() where issueid = ?" - ) - } - - override fun listComments(issue: Issue): List<IssueComment> { - stmtComments.setInt(1, issue.id) - return selectComments(stmtComments).toList() - } - - override fun insertComment(issueComment: IssueComment) { - with(issueComment) { - stmtUpdateIssueDate.setInt(1, issueid) - stmtInsertComment.setInt(1, issueid) - stmtInsertComment.setStringSafe(2, comment) - stmtInsertComment.setIntOrNull(3, author?.id) - } - stmtInsertComment.execute() - stmtUpdateIssueDate.execute() - } - //</editor-fold> +//</editor-fold> } \ No newline at end of file
