Mercurial > hg > LightPIT / changeset

     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