Tue, 11 May 2021 16:00:28 +0200
fixes #137 - leaking prepared statements
src/main/kotlin/de/uapcore/lightpit/dao/PostgresDataAccessObject.kt | file | annotate | diff | comparison | revisions |
1.1 --- a/src/main/kotlin/de/uapcore/lightpit/dao/PostgresDataAccessObject.kt Tue Apr 06 09:08:54 2021 +0200 1.2 +++ b/src/main/kotlin/de/uapcore/lightpit/dao/PostgresDataAccessObject.kt Tue May 11 16:00:28 2021 +0200 1.3 @@ -31,147 +31,126 @@ 1.4 import de.uapcore.lightpit.viewmodel.ComponentSummary 1.5 import de.uapcore.lightpit.viewmodel.IssueSummary 1.6 import de.uapcore.lightpit.viewmodel.VersionSummary 1.7 +import org.intellij.lang.annotations.Language 1.8 import java.sql.Connection 1.9 import java.sql.PreparedStatement 1.10 import java.sql.ResultSet 1.11 1.12 class PostgresDataAccessObject(private val connection: Connection) : DataAccessObject { 1.13 1.14 + /** 1.15 + * Prepares the given [sql] statement and executes the [block] function with the prepared statement as receiver. 1.16 + * The statement is then closed properly. 1.17 + */ 1.18 + private fun <R> withStatement(@Language("SQL") sql: String, block: PreparedStatement.() -> R) = 1.19 + connection.prepareStatement(sql).use(block) 1.20 + 1.21 + /** 1.22 + * Prepares the given [sql] statement and executes the [block] function on that statement. 1.23 + * The statement is then closed properly. 1.24 + */ 1.25 + private fun <R> useStatement(@Language("SQL") sql: String, block: (PreparedStatement) -> R) = 1.26 + connection.prepareStatement(sql).use(block) 1.27 + 1.28 + /** 1.29 + * Executes the statement and iterates the whole result set extracting the rows with the given [extractor] function. 1.30 + */ 1.31 + private fun <T> PreparedStatement.queryAll(extractor: (ResultSet) -> T): List<T> = executeQuery().use { 1.32 + sequence { 1.33 + while (it.next()) { 1.34 + yield(extractor(it)) 1.35 + } 1.36 + }.toList() 1.37 + } 1.38 + 1.39 + /** 1.40 + * Executes the statement and extracts a single row with the given [extractor] function. 1.41 + * If the result set is empty, null is returned. 1.42 + */ 1.43 + private fun <T> PreparedStatement.querySingle(extractor: (ResultSet) -> T): T? = executeQuery().use { 1.44 + return if (it.next()) extractor(it) else null 1.45 + } 1.46 + 1.47 //<editor-fold desc="User"> 1.48 - private fun selectUserInfo( 1.49 - rs: ResultSet, 1.50 - idColumn: String = "userid", 1.51 - usernameColumn: String = "username", 1.52 - givennameColumn: String = "givenname", 1.53 - lastnameColumn: String = "lastname", 1.54 - mailColumn: String = "mail" 1.55 - ): User? { 1.56 - val idval = rs.getInt(idColumn) 1.57 - return if (rs.wasNull()) null else { 1.58 - User(idval).apply { 1.59 - username = rs.getString(usernameColumn) 1.60 - givenname = rs.getString(givennameColumn) 1.61 - lastname = rs.getString(lastnameColumn) 1.62 - mail = rs.getString(mailColumn) 1.63 + //language=SQL 1.64 + private val userQuery = "select userid, username, lastname, givenname, mail from lpit_user" 1.65 + 1.66 + private fun ResultSet.extractUser() = User(getInt("userid")).apply { 1.67 + username = getString("username") 1.68 + givenname = getString("givenname") 1.69 + lastname = getString("lastname") 1.70 + mail = getString("mail") 1.71 + } 1.72 + 1.73 + private fun ResultSet.containsUserInfo(): Boolean { 1.74 + getInt("userid") 1.75 + return !wasNull() 1.76 + } 1.77 + 1.78 + private fun ResultSet.extractOptionalUser() = if (containsUserInfo()) extractUser() else null 1.79 + 1.80 + override fun listUsers() = 1.81 + withStatement("$userQuery where userid > 0 order by username") { 1.82 + queryAll { it.extractUser() } 1.83 + } 1.84 + 1.85 + override fun findUser(id: Int): User? = 1.86 + withStatement("$userQuery where userid = ?") { 1.87 + setInt(1, id) 1.88 + querySingle { it.extractUser() } 1.89 + } 1.90 + 1.91 + override fun findUserByName(username: String): User? = 1.92 + withStatement("$userQuery where lower(username) = lower(?)") { 1.93 + setString(1, username) 1.94 + querySingle { it.extractUser() } 1.95 + } 1.96 + 1.97 + override fun insertUser(user: User) { 1.98 + withStatement("insert into lpit_user (username, lastname, givenname, mail) values (?, ?, ?, ?)") { 1.99 + with(user) { 1.100 + setStringSafe(1, username) 1.101 + setStringOrNull(2, lastname) 1.102 + setStringOrNull(3, givenname) 1.103 + setStringOrNull(4, mail) 1.104 } 1.105 + executeUpdate() 1.106 } 1.107 } 1.108 1.109 - private fun selectUsers(stmt: PreparedStatement) = sequence { 1.110 - stmt.executeQuery().use { rs -> 1.111 - while (rs.next()) selectUserInfo(rs)?.let { yield(it) } 1.112 + override fun updateUser(user: User) { 1.113 + withStatement("update lpit_user set lastname = ?, givenname = ?, mail = ? where userid = ?") { 1.114 + with(user) { 1.115 + setStringOrNull(1, lastname) 1.116 + setStringOrNull(2, givenname) 1.117 + setStringOrNull(3, mail) 1.118 + setInt(4, id) 1.119 + } 1.120 + executeUpdate() 1.121 } 1.122 } 1.123 - 1.124 - //language=SQL 1.125 - private val userQuery = "select userid, username, lastname, givenname, mail from lpit_user" 1.126 - 1.127 - private val stmtUsers by lazy { 1.128 - connection.prepareStatement( 1.129 - """${userQuery} 1.130 - where userid > 0 1.131 - order by username 1.132 - """ 1.133 - ) 1.134 - } 1.135 - private val stmtUserByID by lazy { 1.136 - connection.prepareStatement( 1.137 - """${userQuery} 1.138 - where userid = ? 1.139 - """ 1.140 - ) 1.141 - } 1.142 - private val stmtUserByName by lazy { 1.143 - connection.prepareStatement( 1.144 - """${userQuery} 1.145 - where lower(username) = lower(?) 1.146 - """ 1.147 - ) 1.148 - } 1.149 - private val stmtInsertUser by lazy { 1.150 - connection.prepareStatement( 1.151 - "insert into lpit_user (username, lastname, givenname, mail) values (?, ?, ?, ?)" 1.152 - ) 1.153 - } 1.154 - private val stmtUpdateUser by lazy { 1.155 - connection.prepareStatement( 1.156 - "update lpit_user set lastname = ?, givenname = ?, mail = ? where userid = ?" 1.157 - ) 1.158 - } 1.159 - 1.160 - override fun listUsers() = selectUsers(stmtUsers).toList() 1.161 - override fun findUser(id: Int): User? { 1.162 - stmtUserByID.setInt(1, id) 1.163 - return selectUsers(stmtUserByID).firstOrNull() 1.164 - } 1.165 - 1.166 - override fun findUserByName(username: String): User? { 1.167 - stmtUserByName.setString(1, username) 1.168 - return selectUsers(stmtUserByName).firstOrNull() 1.169 - } 1.170 - 1.171 - override fun insertUser(user: User) { 1.172 - with(user) { 1.173 - stmtInsertUser.setStringSafe(1, username) 1.174 - stmtInsertUser.setStringOrNull(2, lastname) 1.175 - stmtInsertUser.setStringOrNull(3, givenname) 1.176 - stmtInsertUser.setStringOrNull(4, mail) 1.177 - } 1.178 - stmtInsertUser.execute() 1.179 - } 1.180 - 1.181 - override fun updateUser(user: User) { 1.182 - with(user) { 1.183 - stmtUpdateUser.setStringOrNull(1, lastname) 1.184 - stmtUpdateUser.setStringOrNull(2, givenname) 1.185 - stmtUpdateUser.setStringOrNull(3, mail) 1.186 - stmtUpdateUser.setInt(4, id) 1.187 - } 1.188 - stmtUpdateUser.execute() 1.189 - } 1.190 //</editor-fold> 1.191 1.192 //<editor-fold desc="Version"> 1.193 - 1.194 - private fun obtainVersion(rs: ResultSet) = 1.195 - Version(rs.getInt("versionid"), rs.getInt("project")).apply { 1.196 - name = rs.getString("name") 1.197 - node = rs.getString("node") 1.198 - ordinal = rs.getInt("ordinal") 1.199 - status = rs.getEnum("status") 1.200 - } 1.201 - 1.202 - private fun selectVersions(stmt: PreparedStatement) = sequence { 1.203 - stmt.executeQuery().use { rs -> 1.204 - while (rs.next()) { 1.205 - yield(obtainVersion(rs)) 1.206 - } 1.207 - } 1.208 - } 1.209 - 1.210 - private fun setVersionFields(stmt: PreparedStatement, obj: Version): Int { 1.211 - with(obj) { 1.212 - stmt.setStringSafe(1, name) 1.213 - stmt.setStringSafe(2, node) 1.214 - stmt.setInt(3, ordinal) 1.215 - stmt.setEnum(4, status) 1.216 - } 1.217 - return 5 1.218 - } 1.219 - 1.220 //language=SQL 1.221 private val versionQuery = "select versionid, project, name, node, ordinal, status from lpit_version" 1.222 1.223 - private val stmtVersions by lazy { 1.224 - connection.prepareStatement( 1.225 - """${versionQuery} 1.226 - where project = ? 1.227 - order by ordinal desc, lower(name) desc 1.228 - """ 1.229 - ) 1.230 - } 1.231 - private val stmtVersionSummaries by lazy { 1.232 - connection.prepareStatement( 1.233 + private fun ResultSet.extractVersion() = 1.234 + Version(getInt("versionid"), getInt("project")).apply { 1.235 + name = getString("name") 1.236 + node = getString("node") 1.237 + ordinal = getInt("ordinal") 1.238 + status = getEnum("status") 1.239 + } 1.240 + 1.241 + override fun listVersions(project: Project): List<Version> = 1.242 + withStatement("$versionQuery where project = ? order by ordinal desc, lower(name) desc") { 1.243 + setInt(1, project.id) 1.244 + queryAll { it.extractVersion() } 1.245 + } 1.246 + 1.247 + override fun listVersionSummaries(project: Project): List<VersionSummary> = 1.248 + withStatement( 1.249 """ 1.250 with version_map(issueid, versionid, isresolved) as ( 1.251 select issueid, versionid, 1 1.252 @@ -197,51 +176,13 @@ 1.253 from lpit_version 1.254 join summary using (versionid) 1.255 order by ordinal, name 1.256 - """ 1.257 - ) 1.258 - } 1.259 - private val stmtVersionByID by lazy { 1.260 - connection.prepareStatement( 1.261 - """${versionQuery} 1.262 - where versionid = ? 1.263 - """ 1.264 - ) 1.265 - } 1.266 - private val stmtVersionByNode by lazy { 1.267 - connection.prepareStatement( 1.268 - """${versionQuery} 1.269 - where project = ? and node = ? 1.270 - """ 1.271 - ) 1.272 - } 1.273 - private val stmtInsertVersion by lazy { 1.274 - connection.prepareStatement( 1.275 - """ 1.276 - insert into lpit_version (name, node, ordinal, status, project) 1.277 - values (?, ?, ?, ?::version_status, ?) 1.278 - """ 1.279 - ) 1.280 - } 1.281 - private val stmtUpdateVersion by lazy { 1.282 - connection.prepareStatement( 1.283 - """ 1.284 - update lpit_version set name = ?, node = ?, ordinal = ?, status = ?::version_status 1.285 - where versionid = ? 1.286 - """ 1.287 - ) 1.288 - } 1.289 - 1.290 - override fun listVersions(project: Project): List<Version> { 1.291 - stmtVersions.setInt(1, project.id) 1.292 - return selectVersions(stmtVersions).toList() 1.293 - } 1.294 - 1.295 - override fun listVersionSummaries(project: Project): List<VersionSummary> { 1.296 - stmtVersionSummaries.setInt(1, project.id) 1.297 - return sequence { 1.298 - stmtVersionSummaries.executeQuery().use { rs -> 1.299 - while (rs.next()) { 1.300 - val versionSummary = VersionSummary(obtainVersion(rs)) 1.301 + """.trimIndent() 1.302 + ) { 1.303 + setInt(1, project.id) 1.304 + executeQuery().use { rs -> 1.305 + sequence { 1.306 + // TODO: fix bug: this extractor is not grouping the results 1.307 + val versionSummary = VersionSummary(rs.extractVersion()) 1.308 val phase = rs.getInt("phase") 1.309 val total = rs.getInt("total") 1.310 val issueSummary = 1.311 @@ -252,71 +193,52 @@ 1.312 2 -> issueSummary.done = total 1.313 } 1.314 yield(versionSummary) 1.315 - } 1.316 + }.toList() 1.317 } 1.318 - }.toList() 1.319 - } 1.320 + } 1.321 1.322 - override fun findVersion(id: Int): Version? { 1.323 - stmtVersionByID.setInt(1, id) 1.324 - return selectVersions(stmtVersionByID).firstOrNull() 1.325 - } 1.326 + override fun findVersion(id: Int): Version? = 1.327 + withStatement("$versionQuery where versionid = ?") { 1.328 + setInt(1, id) 1.329 + querySingle { it.extractVersion() } 1.330 + } 1.331 1.332 - override fun findVersionByNode(project: Project, node: String): Version? { 1.333 - stmtVersionByNode.setInt(1, project.id) 1.334 - stmtVersionByNode.setString(2, node) 1.335 - return selectVersions(stmtVersionByNode).firstOrNull() 1.336 - } 1.337 + override fun findVersionByNode(project: Project, node: String): Version? = 1.338 + withStatement("$versionQuery where project = ? and node = ?") { 1.339 + setInt(1, project.id) 1.340 + setString(2, node) 1.341 + querySingle { it.extractVersion() } 1.342 + } 1.343 1.344 override fun insertVersion(version: Version) { 1.345 - val col = setVersionFields(stmtInsertVersion, version) 1.346 - stmtInsertVersion.setInt(col, version.projectid) 1.347 - stmtInsertVersion.execute() 1.348 + withStatement("insert into lpit_version (name, node, ordinal, status, project) values (?, ?, ?, ?::version_status, ?)") { 1.349 + with(version) { 1.350 + setStringSafe(1, name) 1.351 + setStringSafe(2, node) 1.352 + setInt(3, ordinal) 1.353 + setEnum(4, status) 1.354 + setInt(5, version.projectid) 1.355 + } 1.356 + executeUpdate() 1.357 + } 1.358 + 1.359 } 1.360 1.361 override fun updateVersion(version: Version) { 1.362 - val col = setVersionFields(stmtUpdateVersion, version) 1.363 - stmtUpdateVersion.setInt(col, version.id) 1.364 - stmtUpdateVersion.execute() 1.365 + withStatement("update lpit_version set name = ?, node = ?, ordinal = ?, status = ?::version_status where versionid = ?") { 1.366 + with(version) { 1.367 + setStringSafe(1, name) 1.368 + setStringSafe(2, node) 1.369 + setInt(3, ordinal) 1.370 + setEnum(4, status) 1.371 + setInt(5, id) 1.372 + } 1.373 + executeUpdate() 1.374 + } 1.375 } 1.376 //</editor-fold> 1.377 1.378 //<editor-fold desc="Component"> 1.379 - 1.380 - private fun obtainComponent(rs: ResultSet): Component = 1.381 - Component(rs.getInt("id"), rs.getInt("project")).apply { 1.382 - name = rs.getString("name") 1.383 - node = rs.getString("node") 1.384 - color = try { 1.385 - WebColor(rs.getString("color")) 1.386 - } catch (ex: IllegalArgumentException) { 1.387 - WebColor("000000") 1.388 - } 1.389 - ordinal = rs.getInt("ordinal") 1.390 - description = rs.getString("description") 1.391 - lead = selectUserInfo(rs) 1.392 - } 1.393 - 1.394 - private fun selectComponents(stmt: PreparedStatement) = sequence { 1.395 - stmt.executeQuery().use { rs -> 1.396 - while (rs.next()) { 1.397 - yield(obtainComponent(rs)) 1.398 - } 1.399 - } 1.400 - } 1.401 - 1.402 - private fun setComponentFields(stmt: PreparedStatement, obj: Component): Int { 1.403 - with(obj) { 1.404 - stmt.setStringSafe(1, name) 1.405 - stmt.setStringSafe(2, node) 1.406 - stmt.setStringSafe(3, color.hex) 1.407 - stmt.setInt(4, ordinal) 1.408 - stmt.setStringOrNull(5, description) 1.409 - stmt.setIntOrNull(6, obj.lead?.id) 1.410 - } 1.411 - return 7 1.412 - } 1.413 - 1.414 //language=SQL 1.415 private val componentQuery = 1.416 """ 1.417 @@ -324,18 +246,43 @@ 1.418 userid, username, givenname, lastname, mail 1.419 from lpit_component 1.420 left join lpit_user on lead = userid 1.421 - """ 1.422 + """.trimIndent() 1.423 1.424 - private val stmtComponents by lazy { 1.425 - connection.prepareStatement( 1.426 - """${componentQuery} 1.427 - where project = ? 1.428 - order by ordinal, lower(name) 1.429 - """ 1.430 - ) 1.431 + private fun ResultSet.extractComponent(): Component = 1.432 + Component(getInt("id"), getInt("project")).apply { 1.433 + name = getString("name") 1.434 + node = getString("node") 1.435 + color = try { 1.436 + WebColor(getString("color")) 1.437 + } catch (ex: IllegalArgumentException) { 1.438 + WebColor("000000") 1.439 + } 1.440 + ordinal = getInt("ordinal") 1.441 + description = getString("description") 1.442 + lead = extractOptionalUser() 1.443 + } 1.444 + 1.445 + private fun PreparedStatement.setComponent(index: Int, component: Component): Int { 1.446 + with(component) { 1.447 + var i = index 1.448 + setStringSafe(i++, name) 1.449 + setStringSafe(i++, node) 1.450 + setStringSafe(i++, color.hex) 1.451 + setInt(i++, ordinal) 1.452 + setStringOrNull(i++, description) 1.453 + setIntOrNull(i++, lead?.id) 1.454 + return i 1.455 + } 1.456 } 1.457 - private val stmtComponentSummaries by lazy { 1.458 - connection.prepareStatement( 1.459 + 1.460 + override fun listComponents(project: Project): List<Component> = 1.461 + withStatement("$componentQuery where project = ? order by ordinal, lower(name)") { 1.462 + setInt(1, project.id) 1.463 + queryAll { it.extractComponent() } 1.464 + } 1.465 + 1.466 + override fun listComponentSummaries(project: Project): List<ComponentSummary> = 1.467 + withStatement( 1.468 """ 1.469 with issues as ( 1.470 select component, phase, count(issueid) as total 1.471 @@ -355,114 +302,58 @@ 1.472 left join lpit_user on lead = userid 1.473 join summary s on c.id = s.id 1.474 order by ordinal, name 1.475 - """ 1.476 - ) 1.477 - } 1.478 - private val stmtComponentById by lazy { 1.479 - connection.prepareStatement( 1.480 - """${componentQuery} 1.481 - where id = ? 1.482 - """ 1.483 - ) 1.484 - } 1.485 - private val stmtComponentByNode by lazy { 1.486 - connection.prepareStatement( 1.487 - """${componentQuery} 1.488 - where project = ? and node = ? 1.489 - """ 1.490 - ) 1.491 - } 1.492 - private val stmtInsertComponent by lazy { 1.493 - connection.prepareStatement( 1.494 - """ 1.495 - insert into lpit_component (name, node, color, ordinal, description, lead, project) 1.496 - values (?, ?, ?, ?, ?, ?, ?) 1.497 - """ 1.498 - ) 1.499 - } 1.500 - private val stmtUpdateComponent by lazy { 1.501 - connection.prepareStatement( 1.502 - "update lpit_component set name = ?, node = ?, color = ?, ordinal = ?, description = ?, lead = ? where id = ?" 1.503 - ) 1.504 - } 1.505 - 1.506 - override fun listComponents(project: Project): List<Component> { 1.507 - stmtComponents.setInt(1, project.id) 1.508 - return selectComponents(stmtComponents).toList() 1.509 - } 1.510 - 1.511 - override fun listComponentSummaries(project: Project): List<ComponentSummary> { 1.512 - stmtComponentSummaries.setInt(1, project.id) 1.513 - return sequence { 1.514 - stmtComponentSummaries.executeQuery().use { rs -> 1.515 - while (rs.next()) { 1.516 - val componentSummary = ComponentSummary(obtainComponent(rs)) 1.517 - val phase = rs.getInt("phase") 1.518 - val total = rs.getInt("total") 1.519 - when (phase) { 1.520 - 0 -> componentSummary.issueSummary.open = total 1.521 - 1 -> componentSummary.issueSummary.active = total 1.522 - 2 -> componentSummary.issueSummary.done = total 1.523 + """.trimIndent() 1.524 + ) { 1.525 + setInt(1, project.id) 1.526 + executeQuery().use { rs -> 1.527 + // TODO: fix bug: this extractor is not grouping the results 1.528 + sequence { 1.529 + val componentSummary = ComponentSummary(rs.extractComponent()).also { 1.530 + val phase = rs.getInt("phase") 1.531 + val total = rs.getInt("total") 1.532 + when (phase) { 1.533 + 0 -> it.issueSummary.open = total 1.534 + 1 -> it.issueSummary.active = total 1.535 + 2 -> it.issueSummary.done = total 1.536 + } 1.537 } 1.538 yield(componentSummary) 1.539 - } 1.540 + }.toList() 1.541 } 1.542 - }.toList() 1.543 - } 1.544 + } 1.545 1.546 - override fun findComponent(id: Int): Component? { 1.547 - stmtComponentById.setInt(1, id) 1.548 - return selectComponents(stmtComponentById).firstOrNull() 1.549 - } 1.550 + override fun findComponent(id: Int): Component? = 1.551 + withStatement("$componentQuery where id = ?") { 1.552 + setInt(1, id) 1.553 + querySingle { it.extractComponent() } 1.554 + } 1.555 1.556 - override fun findComponentByNode(project: Project, node: String): Component? { 1.557 - stmtComponentByNode.setInt(1, project.id) 1.558 - stmtComponentByNode.setString(2, node) 1.559 - return selectComponents(stmtComponentByNode).firstOrNull() 1.560 - } 1.561 + override fun findComponentByNode(project: Project, node: String): Component? = 1.562 + withStatement("$componentQuery where project = ? and node = ?") { 1.563 + setInt(1, project.id) 1.564 + setString(2, node) 1.565 + querySingle { it.extractComponent() } 1.566 + } 1.567 1.568 override fun insertComponent(component: Component) { 1.569 - val col = setComponentFields(stmtInsertComponent, component) 1.570 - stmtInsertComponent.setInt(col, component.projectid) 1.571 - stmtInsertComponent.execute() 1.572 + withStatement("insert into lpit_component (name, node, color, ordinal, description, lead, project) values (?, ?, ?, ?, ?, ?, ?)") { 1.573 + val col = setComponent(1, component) 1.574 + setInt(col, component.projectid) 1.575 + executeUpdate() 1.576 + } 1.577 } 1.578 1.579 override fun updateComponent(component: Component) { 1.580 - val col = setComponentFields(stmtUpdateComponent, component) 1.581 - stmtUpdateComponent.setInt(col, component.id) 1.582 - stmtUpdateComponent.execute() 1.583 - } 1.584 - 1.585 - //</editor-fold> 1.586 - 1.587 - //<editor-fold desc="Project"> 1.588 - 1.589 - private fun selectProjects(stmt: PreparedStatement) = sequence { 1.590 - stmt.executeQuery().use { rs -> 1.591 - while (rs.next()) { 1.592 - yield(Project(rs.getInt("projectid")).apply { 1.593 - name = rs.getString("name") 1.594 - node = rs.getString("node") 1.595 - ordinal = rs.getInt("ordinal") 1.596 - description = rs.getString("description") 1.597 - repoUrl = rs.getString("repourl") 1.598 - owner = selectUserInfo(rs) 1.599 - }) 1.600 - } 1.601 + withStatement("update lpit_component set name = ?, node = ?, color = ?, ordinal = ?, description = ?, lead = ? where id = ?") { 1.602 + val col = setComponent(1, component) 1.603 + setInt(col, component.id) 1.604 + executeUpdate() 1.605 } 1.606 } 1.607 1.608 - private fun setProjectFields(stmt: PreparedStatement, obj: Project): Int { 1.609 - with(obj) { 1.610 - stmt.setStringSafe(1, name) 1.611 - stmt.setStringSafe(2, node) 1.612 - stmt.setInt(3, ordinal) 1.613 - stmt.setStringOrNull(4, description) 1.614 - stmt.setStringOrNull(5, repoUrl) 1.615 - stmt.setIntOrNull(6, owner?.id) 1.616 - } 1.617 - return 7 1.618 - } 1.619 +//</editor-fold> 1.620 + 1.621 +//<editor-fold desc="Project"> 1.622 1.623 //language=SQL 1.624 private val projectQuery = 1.625 @@ -471,143 +362,92 @@ 1.626 userid, username, lastname, givenname, mail 1.627 from lpit_project 1.628 left join lpit_user owner on lpit_project.owner = owner.userid 1.629 - """ 1.630 + """.trimIndent() 1.631 1.632 - private val stmtProjects by lazy { 1.633 - connection.prepareStatement( 1.634 - """${projectQuery} 1.635 - order by ordinal, lower(name) 1.636 - """ 1.637 - ) 1.638 + private fun ResultSet.extractProject() = 1.639 + Project(getInt("projectid")).apply { 1.640 + name = getString("name") 1.641 + node = getString("node") 1.642 + ordinal = getInt("ordinal") 1.643 + description = getString("description") 1.644 + repoUrl = getString("repourl") 1.645 + owner = extractOptionalUser() 1.646 + } 1.647 + 1.648 + private fun PreparedStatement.setProject(index: Int, project: Project): Int { 1.649 + var i = index 1.650 + with(project) { 1.651 + setStringSafe(i++, name) 1.652 + setStringSafe(i++, node) 1.653 + setInt(i++, ordinal) 1.654 + setStringOrNull(i++, description) 1.655 + setStringOrNull(i++, repoUrl) 1.656 + setIntOrNull(i++, owner?.id) 1.657 + } 1.658 + return i 1.659 } 1.660 - private val stmtProjectByID by lazy { 1.661 - connection.prepareStatement( 1.662 - """${projectQuery} 1.663 - where projectid = ? 1.664 - """ 1.665 - ) 1.666 + 1.667 + override fun listProjects(): List<Project> = 1.668 + withStatement("$projectQuery order by ordinal, lower(name)") { 1.669 + queryAll { it.extractProject() } 1.670 + } 1.671 + 1.672 + override fun findProject(id: Int): Project? = 1.673 + withStatement("$projectQuery where projectid = ?") { 1.674 + setInt(1, id) 1.675 + querySingle { it.extractProject() } 1.676 + } 1.677 + 1.678 + override fun findProjectByNode(node: String): Project? = 1.679 + withStatement("$projectQuery where node = ?") { 1.680 + setString(1, node) 1.681 + querySingle { it.extractProject() } 1.682 + } 1.683 + 1.684 + override fun insertProject(project: Project) { 1.685 + withStatement("insert into lpit_project (name, node, ordinal, description, repourl, owner) values (?, ?, ?, ?, ?, ?)") { 1.686 + setProject(1, project) 1.687 + executeUpdate() 1.688 + } 1.689 } 1.690 - private val stmtProjectByNode by lazy { 1.691 - connection.prepareStatement( 1.692 - """${projectQuery} 1.693 - where node = ? 1.694 - """ 1.695 - ) 1.696 + 1.697 + override fun updateProject(project: Project) { 1.698 + withStatement("update lpit_project set name = ?, node = ?, ordinal = ?, description = ?, repourl = ?, owner = ? where projectid = ?") { 1.699 + val col = setProject(1, project) 1.700 + setInt(col, project.id) 1.701 + executeUpdate() 1.702 + } 1.703 } 1.704 - private val stmtInsertProject by lazy { 1.705 - connection.prepareStatement( 1.706 - "insert into lpit_project (name, node, ordinal, description, repourl, owner) values (?, ?, ?, ?, ?, ?)" 1.707 - ) 1.708 - } 1.709 - private val stmtUpdateProject by lazy { 1.710 - connection.prepareStatement( 1.711 - "update lpit_project set name = ?, node = ?, ordinal = ?, description = ?, repourl = ?, owner = ? where projectid = ?" 1.712 - ) 1.713 - } 1.714 - private val stmtIssueSummary by lazy { 1.715 - connection.prepareStatement( 1.716 + 1.717 + override fun collectIssueSummary(project: Project): IssueSummary = 1.718 + withStatement( 1.719 """ 1.720 select phase, count(*) as total 1.721 from lpit_issue 1.722 join lpit_issue_phases using(status) 1.723 where project = ? 1.724 group by phase 1.725 - """ 1.726 - ) 1.727 - } 1.728 - 1.729 - override fun listProjects(): List<Project> { 1.730 - return selectProjects(stmtProjects).toList() 1.731 - } 1.732 - 1.733 - override fun findProject(id: Int): Project? { 1.734 - stmtProjectByID.setInt(1, id) 1.735 - return selectProjects(stmtProjectByID).firstOrNull() 1.736 - } 1.737 - 1.738 - override fun findProjectByNode(node: String): Project? { 1.739 - stmtProjectByNode.setString(1, node) 1.740 - return selectProjects(stmtProjectByNode).firstOrNull() 1.741 - } 1.742 - 1.743 - override fun insertProject(project: Project) { 1.744 - setProjectFields(stmtInsertProject, project) 1.745 - stmtInsertProject.execute() 1.746 - } 1.747 - 1.748 - override fun updateProject(project: Project) { 1.749 - val col = setProjectFields(stmtUpdateProject, project) 1.750 - stmtUpdateProject.setInt(col, project.id) 1.751 - stmtUpdateProject.execute() 1.752 - } 1.753 - 1.754 - override fun collectIssueSummary(project: Project): IssueSummary { 1.755 - stmtIssueSummary.setInt(1, project.id) 1.756 - return stmtIssueSummary.executeQuery().use { rs -> 1.757 - val summary = IssueSummary() 1.758 - while (rs.next()) { 1.759 - val phase = rs.getInt("phase") 1.760 - val total = rs.getInt("total") 1.761 - when (phase) { 1.762 - 0 -> summary.open = total 1.763 - 1 -> summary.active = total 1.764 - 2 -> summary.done = total 1.765 + """.trimIndent() 1.766 + ) { 1.767 + setInt(1, project.id) 1.768 + executeQuery().use { 1.769 + val summary = IssueSummary() 1.770 + while (it.next()) { 1.771 + val phase = it.getInt("phase") 1.772 + val total = it.getInt("total") 1.773 + when (phase) { 1.774 + 0 -> summary.open = total 1.775 + 1 -> summary.active = total 1.776 + 2 -> summary.done = total 1.777 + } 1.778 } 1.779 - } 1.780 - summary 1.781 - } 1.782 - } 1.783 - 1.784 - //</editor-fold> 1.785 - 1.786 - //<editor-fold desc="Issue"> 1.787 - 1.788 - private fun selectIssues(stmt: PreparedStatement) = sequence { 1.789 - stmt.executeQuery().use { rs -> 1.790 - while (rs.next()) { 1.791 - val proj = Project(rs.getInt("project")).apply { 1.792 - name = rs.getString("projectname") 1.793 - node = rs.getString("projectnode") 1.794 - } 1.795 - val comp = rs.getInt("component").let { 1.796 - if (rs.wasNull()) null else 1.797 - Component(it, proj.id).apply { 1.798 - name = rs.getString("componentname") 1.799 - node = rs.getString("componentnode") 1.800 - } 1.801 - } 1.802 - val issue = Issue(rs.getInt("issueid"), proj).apply { 1.803 - component = comp 1.804 - status = rs.getEnum("status") 1.805 - category = rs.getEnum("category") 1.806 - subject = rs.getString("subject") 1.807 - description = rs.getString("description") 1.808 - assignee = selectUserInfo(rs) 1.809 - created = rs.getTimestamp("created") 1.810 - updated = rs.getTimestamp("updated") 1.811 - eta = rs.getDate("eta") 1.812 - } 1.813 - queryAffectedVersions.setInt(1, issue.id) 1.814 - issue.affectedVersions = selectVersions(queryAffectedVersions).toList() 1.815 - queryResolvedVersions.setInt(1, issue.id) 1.816 - issue.resolvedVersions = selectVersions(queryResolvedVersions).toList() 1.817 - yield(issue) 1.818 + summary 1.819 } 1.820 } 1.821 - } 1.822 1.823 - private fun setIssueFields(stmt: PreparedStatement, obj: Issue): Int { 1.824 - with(obj) { 1.825 - stmt.setIntOrNull(1, component?.id) 1.826 - stmt.setEnum(2, status) 1.827 - stmt.setEnum(3, category) 1.828 - stmt.setStringSafe(4, subject) 1.829 - stmt.setStringOrNull(5, description) 1.830 - stmt.setIntOrNull(6, assignee?.id) 1.831 - stmt.setDateOrNull(7, eta) 1.832 - } 1.833 - return 8 1.834 - } 1.835 +//</editor-fold> 1.836 + 1.837 +//<editor-fold desc="Issue"> 1.838 1.839 //language=SQL 1.840 private val issueQuery = 1.841 @@ -622,38 +462,73 @@ 1.842 join lpit_project p on i.project = projectid 1.843 left join lpit_component c on component = c.id 1.844 left join lpit_user on userid = assignee 1.845 - """ 1.846 + """.trimIndent() 1.847 1.848 - private val queryResolvedVersions by lazy { 1.849 - connection.prepareStatement( 1.850 + private fun ResultSet.extractIssue(): Issue { 1.851 + val proj = Project(getInt("project")).apply { 1.852 + name = getString("projectname") 1.853 + node = getString("projectnode") 1.854 + } 1.855 + val comp = getInt("component").let { 1.856 + if (wasNull()) null else 1.857 + Component(it, proj.id).apply { 1.858 + name = getString("componentname") 1.859 + node = getString("componentnode") 1.860 + } 1.861 + } 1.862 + val issue = Issue(getInt("issueid"), proj).apply { 1.863 + component = comp 1.864 + status = getEnum("status") 1.865 + category = getEnum("category") 1.866 + subject = getString("subject") 1.867 + description = getString("description") 1.868 + assignee = extractOptionalUser() 1.869 + created = getTimestamp("created") 1.870 + updated = getTimestamp("updated") 1.871 + eta = getDate("eta") 1.872 + } 1.873 + 1.874 + fun versionQuery(table: String) = 1.875 """ 1.876 select versionid, project, name, status, ordinal, node 1.877 - from lpit_version v join lpit_issue_resolved_version using (versionid) 1.878 + from lpit_version join $table using (versionid) 1.879 where issueid = ? 1.880 order by ordinal, name 1.881 - """ 1.882 - ) 1.883 + """.trimIndent() 1.884 + 1.885 + issue.affectedVersions = withStatement(versionQuery("lpit_issue_affected_version")) { 1.886 + setInt(1, issue.id) 1.887 + queryAll { it.extractVersion() } 1.888 + } 1.889 + issue.resolvedVersions = withStatement(versionQuery("lpit_issue_resolved_version")) { 1.890 + setInt(1, issue.id) 1.891 + queryAll { it.extractVersion() } 1.892 + } 1.893 + return issue 1.894 } 1.895 1.896 - private val queryAffectedVersions by lazy { 1.897 - connection.prepareStatement( 1.898 - """ 1.899 - select versionid, project, name, status, ordinal, node 1.900 - from lpit_version join lpit_issue_affected_version using (versionid) 1.901 - where issueid = ? 1.902 - order by ordinal, name 1.903 - """ 1.904 - ) 1.905 + private fun PreparedStatement.setIssue(index: Int, issue: Issue): Int { 1.906 + var i = index 1.907 + with(issue) { 1.908 + setIntOrNull(i++, component?.id) 1.909 + setEnum(i++, status) 1.910 + setEnum(i++, category) 1.911 + setStringSafe(i++, subject) 1.912 + setStringOrNull(i++, description) 1.913 + setIntOrNull(i++, assignee?.id) 1.914 + setDateOrNull(i++, eta) 1.915 + } 1.916 + return i 1.917 } 1.918 1.919 - private val stmtIssues by lazy { 1.920 - connection.prepareStatement( 1.921 + override fun listIssues(filter: IssueFilter): List<Issue> = 1.922 + withStatement( 1.923 """ 1.924 with issue_version as ( 1.925 select issueid, versionid from lpit_issue_affected_version 1.926 union select issueid, versionid from lpit_issue_resolved_version 1.927 ), 1.928 - filteterd_issues as ( 1.929 + filtered_issues as ( 1.930 select distinct issueid from lpit_issue 1.931 left join issue_version using (issueid) 1.932 where 1.933 @@ -661,193 +536,145 @@ 1.934 (not ? or versionid = ?) and (not ? or versionid is null) and 1.935 (not ? or component = ?) and (not ? or component is null) 1.936 ) 1.937 - ${issueQuery} join filteterd_issues using (issueid) 1.938 - """ 1.939 - ) 1.940 - } 1.941 + $issueQuery join filtered_issues using (issueid) 1.942 + """.trimIndent() 1.943 + ) { 1.944 + fun <T : Entity> applyFilter(filter: Filter<T>, fflag: Int, nflag: Int, idcol: Int) { 1.945 + when (filter) { 1.946 + is AllFilter -> { 1.947 + setBoolean(fflag, false) 1.948 + setBoolean(nflag, false) 1.949 + setInt(idcol, 0) 1.950 + } 1.951 + is NoneFilter -> { 1.952 + setBoolean(fflag, false) 1.953 + setBoolean(nflag, true) 1.954 + setInt(idcol, 0) 1.955 + } 1.956 + is SpecificFilter -> { 1.957 + setBoolean(fflag, true) 1.958 + setBoolean(nflag, false) 1.959 + setInt(idcol, filter.obj.id) 1.960 + } 1.961 + else -> { 1.962 + TODO("Implement range filter.") 1.963 + } 1.964 + } 1.965 + } 1.966 + when (filter.project) { 1.967 + is AllFilter -> { 1.968 + setBoolean(1, false) 1.969 + setInt(2, 0) 1.970 + } 1.971 + is SpecificFilter -> { 1.972 + setBoolean(1, true) 1.973 + setInt(2, filter.project.obj.id) 1.974 + } 1.975 + else -> throw IllegalArgumentException() 1.976 + } 1.977 + applyFilter(filter.version, 3, 5, 4) 1.978 + applyFilter(filter.component, 6, 8, 7) 1.979 1.980 - private val fproj = 1 1.981 - private val projectid = 2 1.982 - private val fversion = 3 1.983 - private val versionid = 4 1.984 - private val nversion = 5 1.985 - private val fcomp = 6 1.986 - private val component = 7 1.987 - private val ncomp = 8 1.988 + queryAll { it.extractIssue() } 1.989 + } 1.990 1.991 - private fun <T : Entity> applyFilter(filter: Filter<T>, fflag: Int, nflag: Int, idcol: Int) { 1.992 - when (filter) { 1.993 - is AllFilter -> { 1.994 - stmtIssues.setBoolean(fflag, false) 1.995 - stmtIssues.setBoolean(nflag, false) 1.996 - stmtIssues.setInt(idcol, 0) 1.997 + override fun findIssue(id: Int): Issue? = 1.998 + withStatement("$issueQuery where issueid = ?") { 1.999 + setInt(1, id) 1.1000 + querySingle { it.extractIssue() } 1.1001 + } 1.1002 + 1.1003 + private fun insertVersionInfo(id: Int, issue: Issue) { 1.1004 + withStatement("insert into lpit_issue_affected_version (issueid, versionid) values (?,?)") { 1.1005 + setInt(1, id) 1.1006 + issue.affectedVersions.forEach { 1.1007 + setInt(2, it.id) 1.1008 + executeUpdate() 1.1009 } 1.1010 - is NoneFilter -> { 1.1011 - stmtIssues.setBoolean(fflag, false) 1.1012 - stmtIssues.setBoolean(nflag, true) 1.1013 - stmtIssues.setInt(idcol, 0) 1.1014 - } 1.1015 - is SpecificFilter -> { 1.1016 - stmtIssues.setBoolean(fflag, true) 1.1017 - stmtIssues.setBoolean(nflag, false) 1.1018 - stmtIssues.setInt(idcol, filter.obj.id) 1.1019 - } 1.1020 - else -> { 1.1021 - TODO("Implement range filter.") 1.1022 + } 1.1023 + withStatement("insert into lpit_issue_resolved_version (issueid, versionid) values (?,?)") { 1.1024 + setInt(1, id) 1.1025 + issue.resolvedVersions.forEach { 1.1026 + setInt(2, it.id) 1.1027 + executeUpdate() 1.1028 } 1.1029 } 1.1030 } 1.1031 1.1032 - override fun listIssues(filter: IssueFilter): List<Issue> { 1.1033 - when (filter.project) { 1.1034 - is AllFilter -> { 1.1035 - stmtIssues.setBoolean(fproj, false) 1.1036 - stmtIssues.setInt(projectid, 0) 1.1037 - } 1.1038 - is SpecificFilter -> { 1.1039 - stmtIssues.setBoolean(fproj, true) 1.1040 - stmtIssues.setInt(projectid, filter.project.obj.id) 1.1041 - } 1.1042 - else -> throw IllegalArgumentException() 1.1043 - } 1.1044 - applyFilter(filter.version, fversion, nversion, versionid) 1.1045 - applyFilter(filter.component, fcomp, ncomp, component) 1.1046 - 1.1047 - return selectIssues(stmtIssues).toList() 1.1048 - } 1.1049 - 1.1050 - private val stmtFindIssueByID by lazy { 1.1051 - connection.prepareStatement( 1.1052 - """${issueQuery} 1.1053 - where issueid = ? 1.1054 - """ 1.1055 - ) 1.1056 - } 1.1057 - private val stmtInsertIssue by lazy { 1.1058 - connection.prepareStatement( 1.1059 + override fun insertIssue(issue: Issue): Int { 1.1060 + val id = withStatement( 1.1061 """ 1.1062 insert into lpit_issue (component, status, category, subject, description, assignee, eta, project) 1.1063 values (?, ?::issue_status, ?::issue_category, ?, ?, ?, ?, ?) 1.1064 returning issueid 1.1065 - """ 1.1066 - ) 1.1067 - } 1.1068 - private val stmtUpdateIssue by lazy { 1.1069 - connection.prepareStatement( 1.1070 - """ 1.1071 - update lpit_issue set updated = now(), 1.1072 - component = ?, status = ?::issue_status, category = ?::issue_category, subject = ?, 1.1073 - description = ?, assignee = ?, eta = ? 1.1074 - where issueid = ? 1.1075 - """ 1.1076 - ) 1.1077 - } 1.1078 - private val stmtInsertAffectedVersion by lazy { 1.1079 - connection.prepareStatement( 1.1080 - "insert into lpit_issue_affected_version (issueid, versionid) values (?,?)" 1.1081 - ) 1.1082 - } 1.1083 - private val stmtInsertResolvedVersion by lazy { 1.1084 - connection.prepareStatement( 1.1085 - "insert into lpit_issue_resolved_version (issueid, versionid) values (?,?)" 1.1086 - ) 1.1087 - } 1.1088 - private val stmtClearAffectedVersions by lazy { 1.1089 - connection.prepareStatement("delete from lpit_issue_affected_version where issueid = ?") 1.1090 - } 1.1091 - private val stmtClearResolvedVersions by lazy { 1.1092 - connection.prepareStatement("delete from lpit_issue_resolved_version where issueid = ?") 1.1093 - } 1.1094 - 1.1095 - override fun findIssue(id: Int): Issue? { 1.1096 - stmtFindIssueByID.setInt(1, id) 1.1097 - return selectIssues(stmtFindIssueByID).firstOrNull() 1.1098 - } 1.1099 - 1.1100 - private fun insertVersionInfo(id: Int, issue: Issue) { 1.1101 - stmtInsertAffectedVersion.setInt(1, id) 1.1102 - stmtInsertResolvedVersion.setInt(1, id) 1.1103 - issue.affectedVersions.forEach { 1.1104 - stmtInsertAffectedVersion.setInt(2, it.id) 1.1105 - stmtInsertAffectedVersion.execute() 1.1106 - } 1.1107 - issue.resolvedVersions.forEach { 1.1108 - stmtInsertResolvedVersion.setInt(2, it.id) 1.1109 - stmtInsertResolvedVersion.execute() 1.1110 - } 1.1111 - } 1.1112 - 1.1113 - override fun insertIssue(issue: Issue): Int { 1.1114 - val col = setIssueFields(stmtInsertIssue, issue) 1.1115 - stmtInsertIssue.setInt(col, issue.project.id) 1.1116 - val id = stmtInsertIssue.executeQuery().use { rs -> 1.1117 - rs.next() 1.1118 - rs.getInt(1) 1.1119 + """.trimIndent() 1.1120 + ) { 1.1121 + val col = setIssue(1, issue) 1.1122 + setInt(col, issue.project.id) 1.1123 + querySingle { it.getInt(1) }!! 1.1124 } 1.1125 insertVersionInfo(id, issue) 1.1126 return id 1.1127 } 1.1128 1.1129 override fun updateIssue(issue: Issue) { 1.1130 - val col = setIssueFields(stmtUpdateIssue, issue) 1.1131 - stmtUpdateIssue.setInt(col, issue.id) 1.1132 - stmtUpdateIssue.execute() 1.1133 + withStatement( 1.1134 + """ 1.1135 + update lpit_issue set updated = now(), 1.1136 + component = ?, status = ?::issue_status, category = ?::issue_category, subject = ?, 1.1137 + description = ?, assignee = ?, eta = ? 1.1138 + where issueid = ? 1.1139 + """.trimIndent() 1.1140 + ) { 1.1141 + val col = setIssue(1, issue) 1.1142 + setInt(col, issue.id) 1.1143 + executeUpdate() 1.1144 + } 1.1145 + 1.1146 // TODO: improve by only inserting / deleting changed version information 1.1147 - stmtClearAffectedVersions.setInt(1, issue.id) 1.1148 - stmtClearResolvedVersions.setInt(1, issue.id) 1.1149 - stmtClearAffectedVersions.execute() 1.1150 - stmtClearResolvedVersions.execute() 1.1151 + withStatement("delete from lpit_issue_affected_version where issueid = ?") { 1.1152 + setInt(1, issue.id) 1.1153 + executeUpdate() 1.1154 + } 1.1155 + withStatement("delete from lpit_issue_resolved_version where issueid = ?") { 1.1156 + setInt(1, issue.id) 1.1157 + executeUpdate() 1.1158 + } 1.1159 insertVersionInfo(issue.id, issue) 1.1160 } 1.1161 1.1162 - //</editor-fold> 1.1163 +//</editor-fold> 1.1164 1.1165 - //<editor-fold desc="IssueComment"> 1.1166 +//<editor-fold desc="IssueComment"> 1.1167 1.1168 - private fun selectComments(stmt: PreparedStatement) = sequence { 1.1169 - stmt.executeQuery().use { rs -> 1.1170 - while (rs.next()) { 1.1171 - yield(IssueComment(rs.getInt("commentid"), rs.getInt("issueid")).apply { 1.1172 - created = rs.getTimestamp("created") 1.1173 - updated = rs.getTimestamp("updated") 1.1174 - updateCount = rs.getInt("updatecount") 1.1175 - comment = rs.getString("comment") 1.1176 - author = selectUserInfo(rs) 1.1177 - }) 1.1178 + private fun ResultSet.extractIssueComment() = 1.1179 + IssueComment(getInt("commentid"), getInt("issueid")).apply { 1.1180 + created = getTimestamp("created") 1.1181 + updated = getTimestamp("updated") 1.1182 + updateCount = getInt("updatecount") 1.1183 + comment = getString("comment") 1.1184 + author = extractOptionalUser() 1.1185 + } 1.1186 + 1.1187 + override fun listComments(issue: Issue): List<IssueComment> = 1.1188 + withStatement("select * from lpit_issue_comment left join lpit_user using (userid) where issueid = ? order by created") { 1.1189 + setInt(1, issue.id) 1.1190 + queryAll { it.extractIssueComment() } 1.1191 + } 1.1192 + 1.1193 + override fun insertComment(issueComment: IssueComment) { 1.1194 + useStatement("update lpit_issue set updated = now() where issueid = ?") { updateIssueDate -> 1.1195 + withStatement("insert into lpit_issue_comment (issueid, comment, userid) values (?, ? ,?)") { 1.1196 + with(issueComment) { 1.1197 + updateIssueDate.setInt(1, issueid) 1.1198 + setInt(1, issueid) 1.1199 + setStringSafe(2, comment) 1.1200 + setIntOrNull(3, author?.id) 1.1201 + } 1.1202 + executeUpdate() 1.1203 + updateIssueDate.executeUpdate() 1.1204 } 1.1205 } 1.1206 } 1.1207 - 1.1208 - private val stmtComments by lazy { 1.1209 - connection.prepareStatement( 1.1210 - "select * from lpit_issue_comment left join lpit_user using (userid) where issueid = ? order by created" 1.1211 - ) 1.1212 - } 1.1213 - private val stmtInsertComment by lazy { 1.1214 - connection.prepareStatement( 1.1215 - "insert into lpit_issue_comment (issueid, comment, userid) values (?, ? ,?)" 1.1216 - ) 1.1217 - } 1.1218 - private val stmtUpdateIssueDate by lazy { 1.1219 - connection.prepareStatement( 1.1220 - "update lpit_issue set updated = now() where issueid = ?" 1.1221 - ) 1.1222 - } 1.1223 - 1.1224 - override fun listComments(issue: Issue): List<IssueComment> { 1.1225 - stmtComments.setInt(1, issue.id) 1.1226 - return selectComments(stmtComments).toList() 1.1227 - } 1.1228 - 1.1229 - override fun insertComment(issueComment: IssueComment) { 1.1230 - with(issueComment) { 1.1231 - stmtUpdateIssueDate.setInt(1, issueid) 1.1232 - stmtInsertComment.setInt(1, issueid) 1.1233 - stmtInsertComment.setStringSafe(2, comment) 1.1234 - stmtInsertComment.setIntOrNull(3, author?.id) 1.1235 - } 1.1236 - stmtInsertComment.execute() 1.1237 - stmtUpdateIssueDate.execute() 1.1238 - } 1.1239 - //</editor-fold> 1.1240 +//</editor-fold> 1.1241 } 1.1242 \ No newline at end of file