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