Fri, 02 Apr 2021 11:59:14 +0200
completes kotlin migration
universe@167 | 1 | /* |
universe@180 | 2 | * Copyright 2021 Mike Becker. All rights reserved. |
universe@167 | 3 | * |
universe@167 | 4 | * Redistribution and use in source and binary forms, with or without |
universe@167 | 5 | * modification, are permitted provided that the following conditions are met: |
universe@167 | 6 | * |
universe@167 | 7 | * 1. Redistributions of source code must retain the above copyright |
universe@167 | 8 | * notice, this list of conditions and the following disclaimer. |
universe@167 | 9 | * |
universe@167 | 10 | * 2. Redistributions in binary form must reproduce the above copyright |
universe@167 | 11 | * notice, this list of conditions and the following disclaimer in the |
universe@167 | 12 | * documentation and/or other materials provided with the distribution. |
universe@167 | 13 | * |
universe@167 | 14 | * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" |
universe@167 | 15 | * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE |
universe@167 | 16 | * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE |
universe@167 | 17 | * DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT HOLDER OR CONTRIBUTORS BE LIABLE |
universe@167 | 18 | * FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL |
universe@167 | 19 | * DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR |
universe@167 | 20 | * SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER |
universe@167 | 21 | * CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, |
universe@167 | 22 | * OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE |
universe@167 | 23 | * OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. |
universe@167 | 24 | */ |
universe@167 | 25 | |
universe@167 | 26 | package de.uapcore.lightpit.dao |
universe@167 | 27 | |
universe@167 | 28 | import de.uapcore.lightpit.entities.* |
universe@167 | 29 | import de.uapcore.lightpit.types.WebColor |
universe@184 | 30 | import de.uapcore.lightpit.util.* |
universe@184 | 31 | import de.uapcore.lightpit.viewmodel.ComponentSummary |
universe@184 | 32 | import de.uapcore.lightpit.viewmodel.IssueSummary |
universe@184 | 33 | import de.uapcore.lightpit.viewmodel.VersionSummary |
universe@167 | 34 | import java.sql.Connection |
universe@167 | 35 | import java.sql.PreparedStatement |
universe@167 | 36 | import java.sql.ResultSet |
universe@167 | 37 | |
universe@167 | 38 | class PostgresDataAccessObject(private val connection: Connection) : DataAccessObject { |
universe@167 | 39 | |
universe@167 | 40 | //<editor-fold desc="User"> |
universe@167 | 41 | private fun selectUserInfo( |
universe@167 | 42 | rs: ResultSet, |
universe@167 | 43 | idColumn: String = "userid", |
universe@167 | 44 | usernameColumn: String = "username", |
universe@167 | 45 | givennameColumn: String = "givenname", |
universe@167 | 46 | lastnameColumn: String = "lastname", |
universe@167 | 47 | mailColumn: String = "mail" |
universe@167 | 48 | ): User? { |
universe@167 | 49 | val idval = rs.getInt(idColumn) |
universe@167 | 50 | return if (rs.wasNull()) null else { |
universe@167 | 51 | User(idval).apply { |
universe@167 | 52 | username = rs.getString(usernameColumn) |
universe@167 | 53 | givenname = rs.getString(givennameColumn) |
universe@167 | 54 | lastname = rs.getString(lastnameColumn) |
universe@167 | 55 | mail = rs.getString(mailColumn) |
universe@167 | 56 | } |
universe@167 | 57 | } |
universe@167 | 58 | } |
universe@167 | 59 | |
universe@167 | 60 | private fun selectUsers(stmt: PreparedStatement) = sequence { |
universe@167 | 61 | stmt.executeQuery().use { rs -> |
universe@167 | 62 | while (rs.next()) selectUserInfo(rs)?.let { yield(it) } |
universe@167 | 63 | } |
universe@167 | 64 | } |
universe@167 | 65 | |
universe@167 | 66 | //language=SQL |
universe@167 | 67 | private val userQuery = "select userid, username, lastname, givenname, mail from lpit_user" |
universe@167 | 68 | |
universe@167 | 69 | private val stmtUsers by lazy { |
universe@167 | 70 | connection.prepareStatement( |
universe@167 | 71 | """${userQuery} |
universe@167 | 72 | where userid > 0 |
universe@167 | 73 | order by username |
universe@167 | 74 | """ |
universe@167 | 75 | ) |
universe@167 | 76 | } |
universe@167 | 77 | private val stmtUserByID by lazy { |
universe@167 | 78 | connection.prepareStatement( |
universe@167 | 79 | """${userQuery} |
universe@167 | 80 | where userid = ? |
universe@167 | 81 | """ |
universe@167 | 82 | ) |
universe@167 | 83 | } |
universe@167 | 84 | private val stmtUserByName by lazy { |
universe@167 | 85 | connection.prepareStatement( |
universe@167 | 86 | """${userQuery} |
universe@167 | 87 | where lower(username) = lower(?) |
universe@167 | 88 | """ |
universe@167 | 89 | ) |
universe@167 | 90 | } |
universe@167 | 91 | private val stmtInsertUser by lazy { |
universe@167 | 92 | connection.prepareStatement( |
universe@167 | 93 | "insert into lpit_user (username, lastname, givenname, mail) values (?, ?, ?, ?)" |
universe@167 | 94 | ) |
universe@167 | 95 | } |
universe@167 | 96 | private val stmtUpdateUser by lazy { |
universe@167 | 97 | connection.prepareStatement( |
universe@167 | 98 | "update lpit_user set lastname = ?, givenname = ?, mail = ? where userid = ?" |
universe@167 | 99 | ) |
universe@167 | 100 | } |
universe@167 | 101 | |
universe@167 | 102 | override fun listUsers() = selectUsers(stmtUsers).toList() |
universe@167 | 103 | override fun findUser(id: Int): User? { |
universe@167 | 104 | stmtUserByID.setInt(1, id) |
universe@167 | 105 | return selectUsers(stmtUserByID).firstOrNull() |
universe@167 | 106 | } |
universe@167 | 107 | |
universe@167 | 108 | override fun findUserByName(username: String): User? { |
universe@167 | 109 | stmtUserByName.setString(1, username) |
universe@167 | 110 | return selectUsers(stmtUserByName).firstOrNull() |
universe@167 | 111 | } |
universe@167 | 112 | |
universe@167 | 113 | override fun insertUser(user: User) { |
universe@167 | 114 | with(user) { |
universe@167 | 115 | stmtInsertUser.setStringSafe(1, username) |
universe@167 | 116 | stmtInsertUser.setStringOrNull(2, lastname) |
universe@167 | 117 | stmtInsertUser.setStringOrNull(3, givenname) |
universe@167 | 118 | stmtInsertUser.setStringOrNull(4, mail) |
universe@167 | 119 | } |
universe@167 | 120 | stmtInsertUser.execute() |
universe@167 | 121 | } |
universe@167 | 122 | |
universe@167 | 123 | override fun updateUser(user: User) { |
universe@167 | 124 | with(user) { |
universe@167 | 125 | stmtUpdateUser.setStringOrNull(1, lastname) |
universe@167 | 126 | stmtUpdateUser.setStringOrNull(2, givenname) |
universe@167 | 127 | stmtUpdateUser.setStringOrNull(3, mail) |
universe@167 | 128 | stmtUpdateUser.setInt(4, id) |
universe@167 | 129 | } |
universe@167 | 130 | stmtUpdateUser.execute() |
universe@167 | 131 | } |
universe@167 | 132 | //</editor-fold> |
universe@167 | 133 | |
universe@167 | 134 | //<editor-fold desc="Version"> |
universe@184 | 135 | |
universe@184 | 136 | private fun obtainVersion(rs: ResultSet) = |
universe@184 | 137 | Version(rs.getInt("versionid"), rs.getInt("project")).apply { |
universe@184 | 138 | name = rs.getString("name") |
universe@184 | 139 | node = rs.getString("node") |
universe@184 | 140 | ordinal = rs.getInt("ordinal") |
universe@184 | 141 | status = rs.getEnum("status") |
universe@184 | 142 | } |
universe@184 | 143 | |
universe@167 | 144 | private fun selectVersions(stmt: PreparedStatement) = sequence { |
universe@167 | 145 | stmt.executeQuery().use { rs -> |
universe@167 | 146 | while (rs.next()) { |
universe@184 | 147 | yield(obtainVersion(rs)) |
universe@167 | 148 | } |
universe@167 | 149 | } |
universe@167 | 150 | } |
universe@167 | 151 | |
universe@167 | 152 | private fun setVersionFields(stmt: PreparedStatement, obj: Version): Int { |
universe@167 | 153 | with(obj) { |
universe@167 | 154 | stmt.setStringSafe(1, name) |
universe@167 | 155 | stmt.setStringSafe(2, node) |
universe@167 | 156 | stmt.setInt(3, ordinal) |
universe@167 | 157 | stmt.setEnum(4, status) |
universe@167 | 158 | } |
universe@167 | 159 | return 5 |
universe@167 | 160 | } |
universe@167 | 161 | |
universe@167 | 162 | //language=SQL |
universe@167 | 163 | private val versionQuery = "select versionid, project, name, node, ordinal, status from lpit_version" |
universe@167 | 164 | |
universe@167 | 165 | private val stmtVersions by lazy { |
universe@167 | 166 | connection.prepareStatement( |
universe@167 | 167 | """${versionQuery} |
universe@167 | 168 | where project = ? |
universe@167 | 169 | order by ordinal desc, lower(name) desc |
universe@167 | 170 | """ |
universe@167 | 171 | ) |
universe@167 | 172 | } |
universe@184 | 173 | private val stmtVersionSummaries by lazy { |
universe@184 | 174 | connection.prepareStatement( |
universe@184 | 175 | """ |
universe@184 | 176 | with version_map(issueid, versionid, isresolved) as ( |
universe@184 | 177 | select issueid, versionid, 1 |
universe@184 | 178 | from lpit_issue_resolved_version |
universe@184 | 179 | union |
universe@184 | 180 | select issueid, versionid, 0 |
universe@184 | 181 | from lpit_issue_affected_version |
universe@184 | 182 | ), |
universe@184 | 183 | issues as ( |
universe@184 | 184 | select versionid, phase, isresolved, count(issueid) as total |
universe@184 | 185 | from lpit_issue |
universe@184 | 186 | join version_map using (issueid) |
universe@184 | 187 | join lpit_issue_phases using (status) |
universe@184 | 188 | group by versionid, phase, isresolved |
universe@184 | 189 | ), |
universe@184 | 190 | summary as ( |
universe@184 | 191 | select versionid, phase, isresolved, total |
universe@184 | 192 | from lpit_version v |
universe@184 | 193 | left join issues using (versionid) |
universe@184 | 194 | where v.project = ? |
universe@184 | 195 | ) |
universe@184 | 196 | select versionid, project, name, node, ordinal, status, phase, isresolved, total |
universe@184 | 197 | from lpit_version |
universe@184 | 198 | join summary using (versionid) |
universe@184 | 199 | order by ordinal, name |
universe@184 | 200 | """ |
universe@184 | 201 | ) |
universe@184 | 202 | } |
universe@167 | 203 | private val stmtVersionByID by lazy { |
universe@167 | 204 | connection.prepareStatement( |
universe@167 | 205 | """${versionQuery} |
universe@167 | 206 | where versionid = ? |
universe@167 | 207 | """ |
universe@167 | 208 | ) |
universe@167 | 209 | } |
universe@167 | 210 | private val stmtVersionByNode by lazy { |
universe@167 | 211 | connection.prepareStatement( |
universe@167 | 212 | """${versionQuery} |
universe@167 | 213 | where project = ? and node = ? |
universe@167 | 214 | """ |
universe@167 | 215 | ) |
universe@167 | 216 | } |
universe@167 | 217 | private val stmtInsertVersion by lazy { |
universe@167 | 218 | connection.prepareStatement( |
universe@167 | 219 | """ |
universe@167 | 220 | insert into lpit_version (name, node, ordinal, status, project) |
universe@167 | 221 | values (?, ?, ?, ?::version_status, ?) |
universe@167 | 222 | """ |
universe@167 | 223 | ) |
universe@167 | 224 | } |
universe@167 | 225 | private val stmtUpdateVersion by lazy { |
universe@167 | 226 | connection.prepareStatement( |
universe@167 | 227 | """ |
universe@167 | 228 | update lpit_version set name = ?, node = ?, ordinal = ?, status = ?::version_status |
universe@167 | 229 | where versionid = ? |
universe@167 | 230 | """ |
universe@167 | 231 | ) |
universe@167 | 232 | } |
universe@167 | 233 | |
universe@167 | 234 | override fun listVersions(project: Project): List<Version> { |
universe@167 | 235 | stmtVersions.setInt(1, project.id) |
universe@167 | 236 | return selectVersions(stmtVersions).toList() |
universe@167 | 237 | } |
universe@167 | 238 | |
universe@184 | 239 | override fun listVersionSummaries(project: Project): List<VersionSummary> { |
universe@184 | 240 | stmtVersionSummaries.setInt(1, project.id) |
universe@184 | 241 | return sequence { |
universe@184 | 242 | stmtVersionSummaries.executeQuery().use { rs -> |
universe@184 | 243 | while (rs.next()) { |
universe@184 | 244 | val versionSummary = VersionSummary(obtainVersion(rs)) |
universe@184 | 245 | val phase = rs.getInt("phase") |
universe@184 | 246 | val total = rs.getInt("total") |
universe@184 | 247 | val issueSummary = |
universe@184 | 248 | if (rs.getBoolean("isresolved")) versionSummary.resolvedTotal else versionSummary.reportedTotal |
universe@184 | 249 | when (phase) { |
universe@184 | 250 | 0 -> issueSummary.open = total |
universe@184 | 251 | 1 -> issueSummary.active = total |
universe@184 | 252 | 2 -> issueSummary.done = total |
universe@184 | 253 | } |
universe@184 | 254 | yield(versionSummary) |
universe@184 | 255 | } |
universe@184 | 256 | } |
universe@184 | 257 | }.toList() |
universe@184 | 258 | } |
universe@184 | 259 | |
universe@167 | 260 | override fun findVersion(id: Int): Version? { |
universe@167 | 261 | stmtVersionByID.setInt(1, id) |
universe@167 | 262 | return selectVersions(stmtVersionByID).firstOrNull() |
universe@167 | 263 | } |
universe@167 | 264 | |
universe@167 | 265 | override fun findVersionByNode(project: Project, node: String): Version? { |
universe@167 | 266 | stmtVersionByNode.setInt(1, project.id) |
universe@167 | 267 | stmtVersionByNode.setString(2, node) |
universe@167 | 268 | return selectVersions(stmtVersionByNode).firstOrNull() |
universe@167 | 269 | } |
universe@167 | 270 | |
universe@167 | 271 | override fun insertVersion(version: Version) { |
universe@167 | 272 | val col = setVersionFields(stmtInsertVersion, version) |
universe@167 | 273 | stmtInsertVersion.setInt(col, version.projectid) |
universe@167 | 274 | stmtInsertVersion.execute() |
universe@167 | 275 | } |
universe@167 | 276 | |
universe@167 | 277 | override fun updateVersion(version: Version) { |
universe@167 | 278 | val col = setVersionFields(stmtUpdateVersion, version) |
universe@167 | 279 | stmtUpdateVersion.setInt(col, version.id) |
universe@167 | 280 | stmtUpdateVersion.execute() |
universe@167 | 281 | } |
universe@167 | 282 | //</editor-fold> |
universe@167 | 283 | |
universe@167 | 284 | //<editor-fold desc="Component"> |
universe@184 | 285 | |
universe@184 | 286 | private fun obtainComponent(rs: ResultSet): Component = |
universe@184 | 287 | Component(rs.getInt("id"), rs.getInt("project")).apply { |
universe@184 | 288 | name = rs.getString("name") |
universe@184 | 289 | node = rs.getString("node") |
universe@184 | 290 | color = try { |
universe@184 | 291 | WebColor(rs.getString("color")) |
universe@184 | 292 | } catch (ex: IllegalArgumentException) { |
universe@184 | 293 | WebColor("000000") |
universe@184 | 294 | } |
universe@184 | 295 | ordinal = rs.getInt("ordinal") |
universe@184 | 296 | description = rs.getString("description") |
universe@184 | 297 | lead = selectUserInfo(rs) |
universe@184 | 298 | } |
universe@184 | 299 | |
universe@167 | 300 | private fun selectComponents(stmt: PreparedStatement) = sequence { |
universe@167 | 301 | stmt.executeQuery().use { rs -> |
universe@167 | 302 | while (rs.next()) { |
universe@184 | 303 | yield(obtainComponent(rs)) |
universe@167 | 304 | } |
universe@167 | 305 | } |
universe@167 | 306 | } |
universe@167 | 307 | |
universe@167 | 308 | private fun setComponentFields(stmt: PreparedStatement, obj: Component): Int { |
universe@167 | 309 | with(obj) { |
universe@167 | 310 | stmt.setStringSafe(1, name) |
universe@167 | 311 | stmt.setStringSafe(2, node) |
universe@167 | 312 | stmt.setStringSafe(3, color.hex) |
universe@167 | 313 | stmt.setInt(4, ordinal) |
universe@167 | 314 | stmt.setStringOrNull(5, description) |
universe@167 | 315 | stmt.setIntOrNull(6, obj.lead?.id) |
universe@167 | 316 | } |
universe@167 | 317 | return 7 |
universe@167 | 318 | } |
universe@167 | 319 | |
universe@167 | 320 | //language=SQL |
universe@167 | 321 | private val componentQuery = |
universe@167 | 322 | """ |
universe@167 | 323 | select id, project, name, node, color, ordinal, description, |
universe@167 | 324 | userid, username, givenname, lastname, mail |
universe@167 | 325 | from lpit_component |
universe@167 | 326 | left join lpit_user on lead = userid |
universe@167 | 327 | """ |
universe@167 | 328 | |
universe@167 | 329 | private val stmtComponents by lazy { |
universe@167 | 330 | connection.prepareStatement( |
universe@167 | 331 | """${componentQuery} |
universe@167 | 332 | where project = ? |
universe@167 | 333 | order by ordinal, lower(name) |
universe@167 | 334 | """ |
universe@167 | 335 | ) |
universe@167 | 336 | } |
universe@184 | 337 | private val stmtComponentSummaries by lazy { |
universe@184 | 338 | connection.prepareStatement( |
universe@184 | 339 | """ |
universe@184 | 340 | with issues as ( |
universe@184 | 341 | select component, phase, count(issueid) as total |
universe@184 | 342 | from lpit_issue |
universe@184 | 343 | join lpit_issue_phases using (status) |
universe@184 | 344 | group by component, phase |
universe@184 | 345 | ), |
universe@184 | 346 | summary as ( |
universe@184 | 347 | select c.id, phase, total |
universe@184 | 348 | from lpit_component c |
universe@184 | 349 | left join issues i on c.id = i.component |
universe@184 | 350 | where c.project = ? |
universe@184 | 351 | ) |
universe@184 | 352 | select c.id, project, name, node, color, ordinal, description, |
universe@184 | 353 | userid, username, givenname, lastname, mail, phase, total |
universe@184 | 354 | from lpit_component c |
universe@184 | 355 | left join lpit_user on lead = userid |
universe@184 | 356 | join summary s on c.id = s.id |
universe@184 | 357 | order by ordinal, name |
universe@184 | 358 | """ |
universe@184 | 359 | ) |
universe@184 | 360 | } |
universe@167 | 361 | private val stmtComponentById by lazy { |
universe@167 | 362 | connection.prepareStatement( |
universe@167 | 363 | """${componentQuery} |
universe@167 | 364 | where id = ? |
universe@167 | 365 | """ |
universe@167 | 366 | ) |
universe@167 | 367 | } |
universe@167 | 368 | private val stmtComponentByNode by lazy { |
universe@167 | 369 | connection.prepareStatement( |
universe@167 | 370 | """${componentQuery} |
universe@167 | 371 | where project = ? and node = ? |
universe@167 | 372 | """ |
universe@167 | 373 | ) |
universe@167 | 374 | } |
universe@167 | 375 | private val stmtInsertComponent by lazy { |
universe@167 | 376 | connection.prepareStatement( |
universe@167 | 377 | """ |
universe@167 | 378 | insert into lpit_component (name, node, color, ordinal, description, lead, project) |
universe@167 | 379 | values (?, ?, ?, ?, ?, ?, ?) |
universe@167 | 380 | """ |
universe@167 | 381 | ) |
universe@167 | 382 | } |
universe@167 | 383 | private val stmtUpdateComponent by lazy { |
universe@167 | 384 | connection.prepareStatement( |
universe@167 | 385 | "update lpit_component set name = ?, node = ?, color = ?, ordinal = ?, description = ?, lead = ? where id = ?" |
universe@167 | 386 | ) |
universe@167 | 387 | } |
universe@167 | 388 | |
universe@167 | 389 | override fun listComponents(project: Project): List<Component> { |
universe@167 | 390 | stmtComponents.setInt(1, project.id) |
universe@167 | 391 | return selectComponents(stmtComponents).toList() |
universe@167 | 392 | } |
universe@167 | 393 | |
universe@184 | 394 | override fun listComponentSummaries(project: Project): List<ComponentSummary> { |
universe@184 | 395 | stmtComponentSummaries.setInt(1, project.id) |
universe@184 | 396 | return sequence { |
universe@184 | 397 | stmtComponentSummaries.executeQuery().use { rs -> |
universe@184 | 398 | while (rs.next()) { |
universe@184 | 399 | val componentSummary = ComponentSummary(obtainComponent(rs)) |
universe@184 | 400 | val phase = rs.getInt("phase") |
universe@184 | 401 | val total = rs.getInt("total") |
universe@184 | 402 | when (phase) { |
universe@184 | 403 | 0 -> componentSummary.issueSummary.open = total |
universe@184 | 404 | 1 -> componentSummary.issueSummary.active = total |
universe@184 | 405 | 2 -> componentSummary.issueSummary.done = total |
universe@184 | 406 | } |
universe@184 | 407 | yield(componentSummary) |
universe@184 | 408 | } |
universe@184 | 409 | } |
universe@184 | 410 | }.toList() |
universe@184 | 411 | } |
universe@184 | 412 | |
universe@167 | 413 | override fun findComponent(id: Int): Component? { |
universe@167 | 414 | stmtComponentById.setInt(1, id) |
universe@167 | 415 | return selectComponents(stmtComponentById).firstOrNull() |
universe@167 | 416 | } |
universe@167 | 417 | |
universe@167 | 418 | override fun findComponentByNode(project: Project, node: String): Component? { |
universe@167 | 419 | stmtComponentByNode.setInt(1, project.id) |
universe@167 | 420 | stmtComponentByNode.setString(2, node) |
universe@167 | 421 | return selectComponents(stmtComponentByNode).firstOrNull() |
universe@167 | 422 | } |
universe@167 | 423 | |
universe@167 | 424 | override fun insertComponent(component: Component) { |
universe@167 | 425 | val col = setComponentFields(stmtInsertComponent, component) |
universe@167 | 426 | stmtInsertComponent.setInt(col, component.projectid) |
universe@167 | 427 | stmtInsertComponent.execute() |
universe@167 | 428 | } |
universe@167 | 429 | |
universe@167 | 430 | override fun updateComponent(component: Component) { |
universe@167 | 431 | val col = setComponentFields(stmtUpdateComponent, component) |
universe@167 | 432 | stmtUpdateComponent.setInt(col, component.id) |
universe@167 | 433 | stmtUpdateComponent.execute() |
universe@167 | 434 | } |
universe@167 | 435 | |
universe@167 | 436 | //</editor-fold> |
universe@167 | 437 | |
universe@167 | 438 | //<editor-fold desc="Project"> |
universe@167 | 439 | |
universe@167 | 440 | private fun selectProjects(stmt: PreparedStatement) = sequence { |
universe@167 | 441 | stmt.executeQuery().use { rs -> |
universe@167 | 442 | while (rs.next()) { |
universe@167 | 443 | yield(Project(rs.getInt("projectid")).apply { |
universe@167 | 444 | name = rs.getString("name") |
universe@167 | 445 | node = rs.getString("node") |
universe@175 | 446 | ordinal = rs.getInt("ordinal") |
universe@167 | 447 | description = rs.getString("description") |
universe@167 | 448 | repoUrl = rs.getString("repourl") |
universe@167 | 449 | owner = selectUserInfo(rs) |
universe@167 | 450 | }) |
universe@167 | 451 | } |
universe@167 | 452 | } |
universe@167 | 453 | } |
universe@167 | 454 | |
universe@167 | 455 | private fun setProjectFields(stmt: PreparedStatement, obj: Project): Int { |
universe@167 | 456 | with(obj) { |
universe@167 | 457 | stmt.setStringSafe(1, name) |
universe@167 | 458 | stmt.setStringSafe(2, node) |
universe@175 | 459 | stmt.setInt(3, ordinal) |
universe@175 | 460 | stmt.setStringOrNull(4, description) |
universe@175 | 461 | stmt.setStringOrNull(5, repoUrl) |
universe@175 | 462 | stmt.setIntOrNull(6, owner?.id) |
universe@167 | 463 | } |
universe@175 | 464 | return 7 |
universe@167 | 465 | } |
universe@167 | 466 | |
universe@167 | 467 | //language=SQL |
universe@167 | 468 | private val projectQuery = |
universe@167 | 469 | """ |
universe@175 | 470 | select projectid, name, node, ordinal, description, repourl, |
universe@167 | 471 | userid, username, lastname, givenname, mail |
universe@167 | 472 | from lpit_project |
universe@167 | 473 | left join lpit_user owner on lpit_project.owner = owner.userid |
universe@167 | 474 | """ |
universe@167 | 475 | |
universe@167 | 476 | private val stmtProjects by lazy { |
universe@167 | 477 | connection.prepareStatement( |
universe@167 | 478 | """${projectQuery} |
universe@175 | 479 | order by ordinal, lower(name) |
universe@167 | 480 | """ |
universe@167 | 481 | ) |
universe@167 | 482 | } |
universe@167 | 483 | private val stmtProjectByID by lazy { |
universe@167 | 484 | connection.prepareStatement( |
universe@167 | 485 | """${projectQuery} |
universe@167 | 486 | where projectid = ? |
universe@167 | 487 | """ |
universe@167 | 488 | ) |
universe@167 | 489 | } |
universe@167 | 490 | private val stmtProjectByNode by lazy { |
universe@167 | 491 | connection.prepareStatement( |
universe@167 | 492 | """${projectQuery} |
universe@167 | 493 | where node = ? |
universe@167 | 494 | """ |
universe@167 | 495 | ) |
universe@167 | 496 | } |
universe@167 | 497 | private val stmtInsertProject by lazy { |
universe@167 | 498 | connection.prepareStatement( |
universe@175 | 499 | "insert into lpit_project (name, node, ordinal, description, repourl, owner) values (?, ?, ?, ?, ?, ?)" |
universe@167 | 500 | ) |
universe@167 | 501 | } |
universe@167 | 502 | private val stmtUpdateProject by lazy { |
universe@167 | 503 | connection.prepareStatement( |
universe@175 | 504 | "update lpit_project set name = ?, node = ?, ordinal = ?, description = ?, repourl = ?, owner = ? where projectid = ?" |
universe@167 | 505 | ) |
universe@167 | 506 | } |
universe@167 | 507 | private val stmtIssueSummary by lazy { |
universe@167 | 508 | connection.prepareStatement( |
universe@167 | 509 | """ |
universe@167 | 510 | select phase, count(*) as total |
universe@167 | 511 | from lpit_issue |
universe@167 | 512 | join lpit_issue_phases using(status) |
universe@167 | 513 | where project = ? |
universe@167 | 514 | group by phase |
universe@167 | 515 | """ |
universe@167 | 516 | ) |
universe@167 | 517 | } |
universe@167 | 518 | |
universe@167 | 519 | override fun listProjects(): List<Project> { |
universe@167 | 520 | return selectProjects(stmtProjects).toList() |
universe@167 | 521 | } |
universe@167 | 522 | |
universe@167 | 523 | override fun findProject(id: Int): Project? { |
universe@167 | 524 | stmtProjectByID.setInt(1, id) |
universe@167 | 525 | return selectProjects(stmtProjectByID).firstOrNull() |
universe@167 | 526 | } |
universe@167 | 527 | |
universe@167 | 528 | override fun findProjectByNode(node: String): Project? { |
universe@167 | 529 | stmtProjectByNode.setString(1, node) |
universe@167 | 530 | return selectProjects(stmtProjectByNode).firstOrNull() |
universe@167 | 531 | } |
universe@167 | 532 | |
universe@167 | 533 | override fun insertProject(project: Project) { |
universe@167 | 534 | setProjectFields(stmtInsertProject, project) |
universe@167 | 535 | stmtInsertProject.execute() |
universe@167 | 536 | } |
universe@167 | 537 | |
universe@167 | 538 | override fun updateProject(project: Project) { |
universe@167 | 539 | val col = setProjectFields(stmtUpdateProject, project) |
universe@167 | 540 | stmtUpdateProject.setInt(col, project.id) |
universe@167 | 541 | stmtUpdateProject.execute() |
universe@167 | 542 | } |
universe@167 | 543 | |
universe@167 | 544 | override fun collectIssueSummary(project: Project): IssueSummary { |
universe@167 | 545 | stmtIssueSummary.setInt(1, project.id) |
universe@167 | 546 | return stmtIssueSummary.executeQuery().use { rs -> |
universe@167 | 547 | val summary = IssueSummary() |
universe@167 | 548 | while (rs.next()) { |
universe@167 | 549 | val phase = rs.getInt("phase") |
universe@167 | 550 | val total = rs.getInt("total") |
universe@167 | 551 | when (phase) { |
universe@167 | 552 | 0 -> summary.open = total |
universe@167 | 553 | 1 -> summary.active = total |
universe@167 | 554 | 2 -> summary.done = total |
universe@167 | 555 | } |
universe@167 | 556 | } |
universe@167 | 557 | summary |
universe@167 | 558 | } |
universe@167 | 559 | } |
universe@167 | 560 | |
universe@167 | 561 | //</editor-fold> |
universe@167 | 562 | |
universe@167 | 563 | //<editor-fold desc="Issue"> |
universe@167 | 564 | |
universe@167 | 565 | private fun selectIssues(stmt: PreparedStatement) = sequence { |
universe@167 | 566 | stmt.executeQuery().use { rs -> |
universe@167 | 567 | while (rs.next()) { |
universe@167 | 568 | val proj = Project(rs.getInt("project")).apply { |
universe@167 | 569 | name = rs.getString("projectname") |
universe@167 | 570 | node = rs.getString("projectnode") |
universe@167 | 571 | } |
universe@167 | 572 | val comp = rs.getInt("component").let { |
universe@167 | 573 | if (rs.wasNull()) null else |
universe@167 | 574 | Component(it, proj.id).apply { |
universe@167 | 575 | name = rs.getString("componentname") |
universe@167 | 576 | node = rs.getString("componentnode") |
universe@167 | 577 | } |
universe@167 | 578 | } |
universe@184 | 579 | val issue = Issue(rs.getInt("issueid"), proj).apply { |
universe@167 | 580 | component = comp |
universe@167 | 581 | status = rs.getEnum("status") |
universe@167 | 582 | category = rs.getEnum("category") |
universe@167 | 583 | subject = rs.getString("subject") |
universe@167 | 584 | description = rs.getString("description") |
universe@167 | 585 | assignee = selectUserInfo(rs) |
universe@167 | 586 | created = rs.getTimestamp("created") |
universe@167 | 587 | updated = rs.getTimestamp("updated") |
universe@167 | 588 | eta = rs.getDate("eta") |
universe@167 | 589 | } |
universe@167 | 590 | queryAffectedVersions.setInt(1, issue.id) |
universe@167 | 591 | issue.affectedVersions = selectVersions(queryAffectedVersions).toList() |
universe@167 | 592 | queryResolvedVersions.setInt(1, issue.id) |
universe@167 | 593 | issue.resolvedVersions = selectVersions(queryResolvedVersions).toList() |
universe@167 | 594 | yield(issue) |
universe@167 | 595 | } |
universe@167 | 596 | } |
universe@167 | 597 | } |
universe@167 | 598 | |
universe@167 | 599 | private fun setIssueFields(stmt: PreparedStatement, obj: Issue): Int { |
universe@167 | 600 | with(obj) { |
universe@167 | 601 | stmt.setIntOrNull(1, component?.id) |
universe@167 | 602 | stmt.setEnum(2, status) |
universe@167 | 603 | stmt.setEnum(3, category) |
universe@167 | 604 | stmt.setStringSafe(4, subject) |
universe@167 | 605 | stmt.setStringOrNull(5, description) |
universe@167 | 606 | stmt.setIntOrNull(6, assignee?.id) |
universe@167 | 607 | stmt.setDateOrNull(7, eta) |
universe@167 | 608 | } |
universe@167 | 609 | return 8 |
universe@167 | 610 | } |
universe@167 | 611 | |
universe@167 | 612 | //language=SQL |
universe@167 | 613 | private val issueQuery = |
universe@167 | 614 | """ |
universe@167 | 615 | select issueid, |
universe@167 | 616 | i.project, p.name as projectname, p.node as projectnode, |
universe@167 | 617 | component, c.name as componentname, c.node as componentnode, |
universe@167 | 618 | status, category, subject, i.description, |
universe@167 | 619 | userid, username, givenname, lastname, mail, |
universe@167 | 620 | created, updated, eta |
universe@167 | 621 | from lpit_issue i |
universe@167 | 622 | join lpit_project p on i.project = projectid |
universe@167 | 623 | left join lpit_component c on component = c.id |
universe@167 | 624 | left join lpit_user on userid = assignee |
universe@167 | 625 | """ |
universe@167 | 626 | |
universe@167 | 627 | private val queryResolvedVersions by lazy { |
universe@167 | 628 | connection.prepareStatement( |
universe@167 | 629 | """ |
universe@167 | 630 | select versionid, project, name, status, ordinal, node |
universe@167 | 631 | from lpit_version v join lpit_issue_resolved_version using (versionid) |
universe@167 | 632 | where issueid = ? |
universe@167 | 633 | order by ordinal, name |
universe@167 | 634 | """ |
universe@167 | 635 | ) |
universe@167 | 636 | } |
universe@167 | 637 | |
universe@167 | 638 | private val queryAffectedVersions by lazy { |
universe@167 | 639 | connection.prepareStatement( |
universe@167 | 640 | """ |
universe@167 | 641 | select versionid, project, name, status, ordinal, node |
universe@167 | 642 | from lpit_version join lpit_issue_affected_version using (versionid) |
universe@167 | 643 | where issueid = ? |
universe@167 | 644 | order by ordinal, name |
universe@167 | 645 | """ |
universe@167 | 646 | ) |
universe@167 | 647 | } |
universe@167 | 648 | |
universe@167 | 649 | private val stmtIssues by lazy { |
universe@167 | 650 | connection.prepareStatement( |
universe@167 | 651 | """ |
universe@167 | 652 | with issue_version as ( |
universe@167 | 653 | select issueid, versionid from lpit_issue_affected_version |
universe@167 | 654 | union select issueid, versionid from lpit_issue_resolved_version |
universe@183 | 655 | ), |
universe@183 | 656 | filteterd_issues as ( |
universe@183 | 657 | select distinct issueid from lpit_issue |
universe@183 | 658 | left join issue_version using (issueid) |
universe@183 | 659 | where |
universe@183 | 660 | (not ? or project = ?) and |
universe@183 | 661 | (not ? or versionid = ?) and (not ? or versionid is null) and |
universe@183 | 662 | (not ? or component = ?) and (not ? or component is null) |
universe@183 | 663 | ) |
universe@183 | 664 | ${issueQuery} join filteterd_issues using (issueid) |
universe@167 | 665 | """ |
universe@167 | 666 | ) |
universe@167 | 667 | } |
universe@167 | 668 | |
universe@167 | 669 | private val fproj = 1 |
universe@167 | 670 | private val projectid = 2 |
universe@167 | 671 | private val fversion = 3 |
universe@167 | 672 | private val versionid = 4 |
universe@167 | 673 | private val nversion = 5 |
universe@167 | 674 | private val fcomp = 6 |
universe@167 | 675 | private val component = 7 |
universe@167 | 676 | private val ncomp = 8 |
universe@167 | 677 | |
universe@167 | 678 | private fun <T : Entity> applyFilter(filter: Filter<T>, fflag: Int, nflag: Int, idcol: Int) { |
universe@167 | 679 | when (filter) { |
universe@167 | 680 | is AllFilter -> { |
universe@167 | 681 | stmtIssues.setBoolean(fflag, false) |
universe@167 | 682 | stmtIssues.setBoolean(nflag, false) |
universe@167 | 683 | stmtIssues.setInt(idcol, 0) |
universe@167 | 684 | } |
universe@167 | 685 | is NoneFilter -> { |
universe@167 | 686 | stmtIssues.setBoolean(fflag, false) |
universe@167 | 687 | stmtIssues.setBoolean(nflag, true) |
universe@167 | 688 | stmtIssues.setInt(idcol, 0) |
universe@167 | 689 | } |
universe@167 | 690 | is SpecificFilter -> { |
universe@167 | 691 | stmtIssues.setBoolean(fflag, true) |
universe@167 | 692 | stmtIssues.setBoolean(nflag, false) |
universe@167 | 693 | stmtIssues.setInt(idcol, filter.obj.id) |
universe@167 | 694 | } |
universe@167 | 695 | else -> { |
universe@167 | 696 | TODO("Implement range filter.") |
universe@167 | 697 | } |
universe@167 | 698 | } |
universe@167 | 699 | } |
universe@167 | 700 | |
universe@167 | 701 | override fun listIssues(filter: IssueFilter): List<Issue> { |
universe@167 | 702 | when (filter.project) { |
universe@167 | 703 | is AllFilter -> { |
universe@167 | 704 | stmtIssues.setBoolean(fproj, false) |
universe@167 | 705 | stmtIssues.setInt(projectid, 0) |
universe@167 | 706 | } |
universe@167 | 707 | is SpecificFilter -> { |
universe@167 | 708 | stmtIssues.setBoolean(fproj, true) |
universe@167 | 709 | stmtIssues.setInt(projectid, filter.project.obj.id) |
universe@167 | 710 | } |
universe@167 | 711 | else -> throw IllegalArgumentException() |
universe@167 | 712 | } |
universe@167 | 713 | applyFilter(filter.version, fversion, nversion, versionid) |
universe@167 | 714 | applyFilter(filter.component, fcomp, ncomp, component) |
universe@167 | 715 | |
universe@167 | 716 | return selectIssues(stmtIssues).toList() |
universe@167 | 717 | } |
universe@167 | 718 | |
universe@167 | 719 | private val stmtFindIssueByID by lazy { |
universe@167 | 720 | connection.prepareStatement( |
universe@167 | 721 | """${issueQuery} |
universe@167 | 722 | where issueid = ? |
universe@167 | 723 | """ |
universe@167 | 724 | ) |
universe@167 | 725 | } |
universe@167 | 726 | private val stmtInsertIssue by lazy { |
universe@167 | 727 | connection.prepareStatement( |
universe@167 | 728 | """ |
universe@167 | 729 | insert into lpit_issue (component, status, category, subject, description, assignee, eta, project) |
universe@167 | 730 | values (?, ?::issue_status, ?::issue_category, ?, ?, ?, ?, ?) |
universe@167 | 731 | returning issueid |
universe@167 | 732 | """ |
universe@167 | 733 | ) |
universe@167 | 734 | } |
universe@167 | 735 | private val stmtUpdateIssue by lazy { |
universe@167 | 736 | connection.prepareStatement( |
universe@167 | 737 | """ |
universe@167 | 738 | update lpit_issue set updated = now(), |
universe@167 | 739 | component = ?, status = ?::issue_status, category = ?::issue_category, subject = ?, |
universe@167 | 740 | description = ?, assignee = ?, eta = ? |
universe@167 | 741 | where issueid = ? |
universe@167 | 742 | """ |
universe@167 | 743 | ) |
universe@167 | 744 | } |
universe@167 | 745 | private val stmtInsertAffectedVersion by lazy { |
universe@167 | 746 | connection.prepareStatement( |
universe@167 | 747 | "insert into lpit_issue_affected_version (issueid, versionid) values (?,?)" |
universe@167 | 748 | ) |
universe@167 | 749 | } |
universe@167 | 750 | private val stmtInsertResolvedVersion by lazy { |
universe@167 | 751 | connection.prepareStatement( |
universe@167 | 752 | "insert into lpit_issue_resolved_version (issueid, versionid) values (?,?)" |
universe@167 | 753 | ) |
universe@167 | 754 | } |
universe@167 | 755 | private val stmtClearAffectedVersions by lazy { |
universe@167 | 756 | connection.prepareStatement("delete from lpit_issue_affected_version where issueid = ?") |
universe@167 | 757 | } |
universe@167 | 758 | private val stmtClearResolvedVersions by lazy { |
universe@167 | 759 | connection.prepareStatement("delete from lpit_issue_resolved_version where issueid = ?") |
universe@167 | 760 | } |
universe@167 | 761 | |
universe@167 | 762 | override fun findIssue(id: Int): Issue? { |
universe@167 | 763 | stmtFindIssueByID.setInt(1, id) |
universe@167 | 764 | return selectIssues(stmtFindIssueByID).firstOrNull() |
universe@167 | 765 | } |
universe@167 | 766 | |
universe@167 | 767 | private fun insertVersionInfo(issue: Issue) { |
universe@167 | 768 | stmtInsertAffectedVersion.setInt(1, issue.id) |
universe@167 | 769 | stmtInsertResolvedVersion.setInt(1, issue.id) |
universe@167 | 770 | issue.affectedVersions.forEach { |
universe@167 | 771 | stmtInsertAffectedVersion.setInt(2, it.id) |
universe@167 | 772 | stmtInsertAffectedVersion.execute() |
universe@167 | 773 | } |
universe@167 | 774 | issue.resolvedVersions.forEach { |
universe@167 | 775 | stmtInsertResolvedVersion.setInt(2, it.id) |
universe@167 | 776 | stmtInsertResolvedVersion.execute() |
universe@167 | 777 | } |
universe@167 | 778 | } |
universe@167 | 779 | |
universe@184 | 780 | override fun insertIssue(issue: Issue): Int { |
universe@167 | 781 | val col = setIssueFields(stmtInsertIssue, issue) |
universe@167 | 782 | stmtInsertIssue.setInt(col, issue.project.id) |
universe@184 | 783 | val id = stmtInsertIssue.executeQuery().use { rs -> |
universe@167 | 784 | rs.next() |
universe@184 | 785 | rs.getInt(1) |
universe@167 | 786 | } |
universe@167 | 787 | insertVersionInfo(issue) |
universe@184 | 788 | return id |
universe@167 | 789 | } |
universe@167 | 790 | |
universe@167 | 791 | override fun updateIssue(issue: Issue) { |
universe@167 | 792 | val col = setIssueFields(stmtUpdateIssue, issue) |
universe@167 | 793 | stmtUpdateIssue.setInt(col, issue.id) |
universe@176 | 794 | stmtUpdateIssue.execute() |
universe@167 | 795 | // TODO: improve by only inserting / deleting changed version information |
universe@167 | 796 | stmtClearAffectedVersions.setInt(1, issue.id) |
universe@167 | 797 | stmtClearResolvedVersions.setInt(1, issue.id) |
universe@167 | 798 | stmtClearAffectedVersions.execute() |
universe@167 | 799 | stmtClearResolvedVersions.execute() |
universe@167 | 800 | insertVersionInfo(issue) |
universe@167 | 801 | } |
universe@167 | 802 | |
universe@167 | 803 | //</editor-fold> |
universe@167 | 804 | |
universe@167 | 805 | //<editor-fold desc="IssueComment"> |
universe@167 | 806 | |
universe@167 | 807 | private fun selectComments(stmt: PreparedStatement) = sequence { |
universe@167 | 808 | stmt.executeQuery().use { rs -> |
universe@167 | 809 | while (rs.next()) { |
universe@167 | 810 | yield(IssueComment(rs.getInt("commentid"), rs.getInt("issueid")).apply { |
universe@167 | 811 | created = rs.getTimestamp("created") |
universe@167 | 812 | updated = rs.getTimestamp("updated") |
universe@167 | 813 | updateCount = rs.getInt("updatecount") |
universe@167 | 814 | comment = rs.getString("comment") |
universe@167 | 815 | author = selectUserInfo(rs) |
universe@167 | 816 | }) |
universe@167 | 817 | } |
universe@167 | 818 | } |
universe@167 | 819 | } |
universe@167 | 820 | |
universe@167 | 821 | private val stmtComments by lazy { |
universe@167 | 822 | connection.prepareStatement( |
universe@167 | 823 | "select * from lpit_issue_comment left join lpit_user using (userid) where issueid = ? order by created" |
universe@167 | 824 | ) |
universe@167 | 825 | } |
universe@167 | 826 | private val stmtInsertComment by lazy { |
universe@167 | 827 | connection.prepareStatement( |
universe@167 | 828 | "insert into lpit_issue_comment (issueid, comment, userid) values (?, ? ,?)" |
universe@167 | 829 | ) |
universe@167 | 830 | } |
universe@167 | 831 | private val stmtUpdateIssueDate by lazy { |
universe@167 | 832 | connection.prepareStatement( |
universe@167 | 833 | "update lpit_issue set updated = now() where issueid = ?" |
universe@167 | 834 | ) |
universe@167 | 835 | } |
universe@167 | 836 | |
universe@167 | 837 | override fun listComments(issue: Issue): List<IssueComment> { |
universe@167 | 838 | stmtComments.setInt(1, issue.id) |
universe@167 | 839 | return selectComments(stmtComments).toList() |
universe@167 | 840 | } |
universe@167 | 841 | |
universe@167 | 842 | override fun insertComment(issueComment: IssueComment) { |
universe@167 | 843 | with(issueComment) { |
universe@167 | 844 | stmtUpdateIssueDate.setInt(1, issueid) |
universe@167 | 845 | stmtInsertComment.setInt(1, issueid) |
universe@167 | 846 | stmtInsertComment.setStringSafe(2, comment) |
universe@167 | 847 | stmtInsertComment.setIntOrNull(3, author?.id) |
universe@167 | 848 | } |
universe@167 | 849 | stmtInsertComment.execute() |
universe@167 | 850 | stmtUpdateIssueDate.execute() |
universe@167 | 851 | } |
universe@167 | 852 | //</editor-fold> |
universe@167 | 853 | } |