comparison: src/main/kotlin/de/uapcore/lightpit/dao/PostgresDataAccessObject.kt
src/main/kotlin/de/uapcore/lightpit/dao/PostgresDataAccessObject.kt
- changeset 231
- dcb1d5a7ea3a
- parent 227
- f0ede8046b59
- child 232
- 296e12ff8d1c
equal
deleted
inserted
replaced
| 230:95b419e054fa | 231:dcb1d5a7ea3a |
|---|---|
| 151 queryAll { it.extractVersion() } | 151 queryAll { it.extractVersion() } |
| 152 } | 152 } |
| 153 | 153 |
| 154 override fun listVersionSummaries(project: Project): List<VersionSummary> = | 154 override fun listVersionSummaries(project: Project): List<VersionSummary> = |
| 155 withStatement( | 155 withStatement( |
| 156 """ | 156 """with |
| 157 with version_map(issueid, versionid, isresolved) as ( | 157 version_map as ( |
| 158 select issueid, versionid, true | 158 select issueid, status, resolved as versionid, true as isresolved from lpit_issue |
| 159 from lpit_issue_resolved_version | 159 union all |
| 160 union | 160 select issueid, status, affected as versionid, false as isresolved from lpit_issue |
| 161 select issueid, versionid, false | 161 ), issues as ( |
| 162 from lpit_issue_affected_version | 162 select versionid, phase, isresolved, count(issueid) as total from version_map |
| 163 ), | |
| 164 issues as ( | |
| 165 select versionid, phase, isresolved, count(issueid) as total | |
| 166 from lpit_issue | |
| 167 join version_map using (issueid) | |
| 168 join lpit_issue_phases using (status) | 163 join lpit_issue_phases using (status) |
| 169 group by versionid, phase, isresolved | 164 group by versionid, phase, isresolved |
| 170 ), | 165 ), |
| 171 summary as ( | 166 summary as ( |
| 172 select versionid, phase, isresolved, total | 167 select versionid, phase, isresolved, total |
| 459 select issueid, | 454 select issueid, |
| 460 i.project, p.name as projectname, p.node as projectnode, | 455 i.project, p.name as projectname, p.node as projectnode, |
| 461 component, c.name as componentname, c.node as componentnode, | 456 component, c.name as componentname, c.node as componentnode, |
| 462 status, category, subject, i.description, | 457 status, category, subject, i.description, |
| 463 userid, username, givenname, lastname, mail, | 458 userid, username, givenname, lastname, mail, |
| 464 created, updated, eta | 459 created, updated, eta, affected, resolved |
| 465 from lpit_issue i | 460 from lpit_issue i |
| 466 join lpit_project p on i.project = projectid | 461 join lpit_project p on i.project = projectid |
| 467 left join lpit_component c on component = c.id | 462 left join lpit_component c on component = c.id |
| 468 left join lpit_user on userid = assignee | 463 left join lpit_user on userid = assignee |
| 469 """.trimIndent() | 464 """.trimIndent() |
| 488 description = getString("description") | 483 description = getString("description") |
| 489 assignee = extractOptionalUser() | 484 assignee = extractOptionalUser() |
| 490 created = getTimestamp("created") | 485 created = getTimestamp("created") |
| 491 updated = getTimestamp("updated") | 486 updated = getTimestamp("updated") |
| 492 eta = getDate("eta") | 487 eta = getDate("eta") |
| 493 } | 488 affected = getInt("affected").takeIf { it > 0 }?.let { findVersion(it) } |
| 494 | 489 resolved = getInt("resolved").takeIf { it > 0 }?.let { findVersion(it) } |
| 495 //language=SQL | 490 } |
| 496 val queryAffected = | 491 |
| 497 """ | |
| 498 $versionQuery join lpit_issue_affected_version using (versionid) | |
| 499 where issueid = ? order by ordinal, name | |
| 500 """.trimIndent() | |
| 501 | |
| 502 //language=SQL | |
| 503 val queryResolved = | |
| 504 """ | |
| 505 $versionQuery join lpit_issue_resolved_version using (versionid) | |
| 506 where issueid = ? order by ordinal, name | |
| 507 """.trimIndent() | |
| 508 | |
| 509 issue.affectedVersions = withStatement(queryAffected) { | |
| 510 setInt(1, issue.id) | |
| 511 queryAll { it.extractVersion() } | |
| 512 } | |
| 513 issue.resolvedVersions = withStatement(queryResolved) { | |
| 514 setInt(1, issue.id) | |
| 515 queryAll { it.extractVersion() } | |
| 516 } | |
| 517 return issue | 492 return issue |
| 518 } | 493 } |
| 519 | 494 |
| 520 private fun PreparedStatement.setIssue(index: Int, issue: Issue): Int { | 495 private fun PreparedStatement.setIssue(index: Int, issue: Issue): Int { |
| 521 var i = index | 496 var i = index |
| 525 setEnum(i++, category) | 500 setEnum(i++, category) |
| 526 setStringSafe(i++, subject) | 501 setStringSafe(i++, subject) |
| 527 setStringOrNull(i++, description) | 502 setStringOrNull(i++, description) |
| 528 setIntOrNull(i++, assignee?.id) | 503 setIntOrNull(i++, assignee?.id) |
| 529 setDateOrNull(i++, eta) | 504 setDateOrNull(i++, eta) |
| 505 setIntOrNull(i++, affected?.id) | |
| 506 setIntOrNull(i++, resolved?.id) | |
| 530 } | 507 } |
| 531 return i | 508 return i |
| 532 } | 509 } |
| 533 | 510 |
| 534 override fun listIssues(filter: IssueFilter): List<Issue> = | 511 override fun listIssues(filter: IssueFilter): List<Issue> = |
| 535 withStatement( | 512 withStatement( |
| 536 """ | 513 """$issueQuery where |
| 537 with issue_version as ( | 514 (not ? or i.project = ?) and |
| 538 select issueid, versionid from lpit_issue_affected_version | 515 (not ? or ? in (resolved, affected)) and (not ? or (resolved is null and affected is null)) and |
| 539 union select issueid, versionid from lpit_issue_resolved_version | |
| 540 ), | |
| 541 filtered_issues as ( | |
| 542 select distinct issueid from lpit_issue | |
| 543 left join issue_version using (issueid) | |
| 544 where | |
| 545 (not ? or project = ?) and | |
| 546 (not ? or versionid = ?) and (not ? or versionid is null) and | |
| 547 (not ? or component = ?) and (not ? or component is null) | 516 (not ? or component = ?) and (not ? or component is null) |
| 548 ) | |
| 549 $issueQuery join filtered_issues using (issueid) | |
| 550 """.trimIndent() | 517 """.trimIndent() |
| 551 ) { | 518 ) { |
| 552 fun <T : Entity> applyFilter(filter: Filter<T>, fflag: Int, nflag: Int, idcol: Int) { | 519 fun <T : Entity> applyFilter(filter: Filter<T>, fflag: Int, nflag: Int, idcol: Int) { |
| 553 when (filter) { | 520 when (filter) { |
| 554 is AllFilter -> { | 521 is AllFilter -> { |
| 592 withStatement("$issueQuery where issueid = ?") { | 559 withStatement("$issueQuery where issueid = ?") { |
| 593 setInt(1, id) | 560 setInt(1, id) |
| 594 querySingle { it.extractIssue() } | 561 querySingle { it.extractIssue() } |
| 595 } | 562 } |
| 596 | 563 |
| 597 private fun insertVersionInfo(id: Int, issue: Issue) { | |
| 598 withStatement("insert into lpit_issue_affected_version (issueid, versionid) values (?,?)") { | |
| 599 setInt(1, id) | |
| 600 issue.affectedVersions.forEach { | |
| 601 setInt(2, it.id) | |
| 602 executeUpdate() | |
| 603 } | |
| 604 } | |
| 605 withStatement("insert into lpit_issue_resolved_version (issueid, versionid) values (?,?)") { | |
| 606 setInt(1, id) | |
| 607 issue.resolvedVersions.forEach { | |
| 608 setInt(2, it.id) | |
| 609 executeUpdate() | |
| 610 } | |
| 611 } | |
| 612 } | |
| 613 | |
| 614 override fun insertIssue(issue: Issue): Int { | 564 override fun insertIssue(issue: Issue): Int { |
| 615 val id = withStatement( | 565 val id = withStatement( |
| 616 """ | 566 """ |
| 617 insert into lpit_issue (component, status, category, subject, description, assignee, eta, project) | 567 insert into lpit_issue (component, status, category, subject, description, assignee, eta, affected, resolved, project) |
| 618 values (?, ?::issue_status, ?::issue_category, ?, ?, ?, ?, ?) | 568 values (?, ?::issue_status, ?::issue_category, ?, ?, ?, ?, ?) |
| 619 returning issueid | 569 returning issueid |
| 620 """.trimIndent() | 570 """.trimIndent() |
| 621 ) { | 571 ) { |
| 622 val col = setIssue(1, issue) | 572 val col = setIssue(1, issue) |
| 623 setInt(col, issue.project.id) | 573 setInt(col, issue.project.id) |
| 624 querySingle { it.getInt(1) }!! | 574 querySingle { it.getInt(1) }!! |
| 625 } | 575 } |
| 626 insertVersionInfo(id, issue) | |
| 627 return id | 576 return id |
| 628 } | 577 } |
| 629 | 578 |
| 630 override fun updateIssue(issue: Issue) { | 579 override fun updateIssue(issue: Issue) { |
| 631 withStatement( | 580 withStatement( |
| 632 """ | 581 """ |
| 633 update lpit_issue set updated = now(), | 582 update lpit_issue set updated = now(), |
| 634 component = ?, status = ?::issue_status, category = ?::issue_category, subject = ?, | 583 component = ?, status = ?::issue_status, category = ?::issue_category, subject = ?, |
| 635 description = ?, assignee = ?, eta = ? | 584 description = ?, assignee = ?, eta = ?, affected = ?, resolved = ? |
| 636 where issueid = ? | 585 where issueid = ? |
| 637 """.trimIndent() | 586 """.trimIndent() |
| 638 ) { | 587 ) { |
| 639 val col = setIssue(1, issue) | 588 val col = setIssue(1, issue) |
| 640 setInt(col, issue.id) | 589 setInt(col, issue.id) |
| 641 executeUpdate() | 590 executeUpdate() |
| 642 } | 591 } |
| 643 | |
| 644 // TODO: improve by only inserting / deleting changed version information | |
| 645 withStatement("delete from lpit_issue_affected_version where issueid = ?") { | |
| 646 setInt(1, issue.id) | |
| 647 executeUpdate() | |
| 648 } | |
| 649 withStatement("delete from lpit_issue_resolved_version where issueid = ?") { | |
| 650 setInt(1, issue.id) | |
| 651 executeUpdate() | |
| 652 } | |
| 653 insertVersionInfo(issue.id, issue) | |
| 654 } | 592 } |
| 655 | 593 |
| 656 //</editor-fold> | 594 //</editor-fold> |
| 657 | 595 |
| 658 //<editor-fold desc="IssueComment"> | 596 //<editor-fold desc="IssueComment"> |
