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