1.1 --- a/src/main/kotlin/de/uapcore/lightpit/dao/PostgresDataAccessObject.kt Wed Aug 18 16:02:40 2021 +0200
1.2 +++ b/src/main/kotlin/de/uapcore/lightpit/dao/PostgresDataAccessObject.kt Thu Aug 19 14:51:04 2021 +0200
1.3 @@ -153,18 +153,13 @@
1.4
1.5 override fun listVersionSummaries(project: Project): List<VersionSummary> =
1.6 withStatement(
1.7 - """
1.8 - with version_map(issueid, versionid, isresolved) as (
1.9 - select issueid, versionid, true
1.10 - from lpit_issue_resolved_version
1.11 - union
1.12 - select issueid, versionid, false
1.13 - from lpit_issue_affected_version
1.14 - ),
1.15 - issues as (
1.16 - select versionid, phase, isresolved, count(issueid) as total
1.17 - from lpit_issue
1.18 - join version_map using (issueid)
1.19 + """with
1.20 + version_map as (
1.21 + select issueid, status, resolved as versionid, true as isresolved from lpit_issue
1.22 + union all
1.23 + select issueid, status, affected as versionid, false as isresolved from lpit_issue
1.24 + ), issues as (
1.25 + select versionid, phase, isresolved, count(issueid) as total from version_map
1.26 join lpit_issue_phases using (status)
1.27 group by versionid, phase, isresolved
1.28 ),
1.29 @@ -461,7 +456,7 @@
1.30 component, c.name as componentname, c.node as componentnode,
1.31 status, category, subject, i.description,
1.32 userid, username, givenname, lastname, mail,
1.33 - created, updated, eta
1.34 + created, updated, eta, affected, resolved
1.35 from lpit_issue i
1.36 join lpit_project p on i.project = projectid
1.37 left join lpit_component c on component = c.id
1.38 @@ -490,30 +485,10 @@
1.39 created = getTimestamp("created")
1.40 updated = getTimestamp("updated")
1.41 eta = getDate("eta")
1.42 + affected = getInt("affected").takeIf { it > 0 }?.let { findVersion(it) }
1.43 + resolved = getInt("resolved").takeIf { it > 0 }?.let { findVersion(it) }
1.44 }
1.45
1.46 - //language=SQL
1.47 - val queryAffected =
1.48 - """
1.49 - $versionQuery join lpit_issue_affected_version using (versionid)
1.50 - where issueid = ? order by ordinal, name
1.51 - """.trimIndent()
1.52 -
1.53 - //language=SQL
1.54 - val queryResolved =
1.55 - """
1.56 - $versionQuery join lpit_issue_resolved_version using (versionid)
1.57 - where issueid = ? order by ordinal, name
1.58 - """.trimIndent()
1.59 -
1.60 - issue.affectedVersions = withStatement(queryAffected) {
1.61 - setInt(1, issue.id)
1.62 - queryAll { it.extractVersion() }
1.63 - }
1.64 - issue.resolvedVersions = withStatement(queryResolved) {
1.65 - setInt(1, issue.id)
1.66 - queryAll { it.extractVersion() }
1.67 - }
1.68 return issue
1.69 }
1.70
1.71 @@ -527,26 +502,18 @@
1.72 setStringOrNull(i++, description)
1.73 setIntOrNull(i++, assignee?.id)
1.74 setDateOrNull(i++, eta)
1.75 + setIntOrNull(i++, affected?.id)
1.76 + setIntOrNull(i++, resolved?.id)
1.77 }
1.78 return i
1.79 }
1.80
1.81 override fun listIssues(filter: IssueFilter): List<Issue> =
1.82 withStatement(
1.83 - """
1.84 - with issue_version as (
1.85 - select issueid, versionid from lpit_issue_affected_version
1.86 - union select issueid, versionid from lpit_issue_resolved_version
1.87 - ),
1.88 - filtered_issues as (
1.89 - select distinct issueid from lpit_issue
1.90 - left join issue_version using (issueid)
1.91 - where
1.92 - (not ? or project = ?) and
1.93 - (not ? or versionid = ?) and (not ? or versionid is null) and
1.94 + """$issueQuery where
1.95 + (not ? or i.project = ?) and
1.96 + (not ? or ? in (resolved, affected)) and (not ? or (resolved is null and affected is null)) and
1.97 (not ? or component = ?) and (not ? or component is null)
1.98 - )
1.99 - $issueQuery join filtered_issues using (issueid)
1.100 """.trimIndent()
1.101 ) {
1.102 fun <T : Entity> applyFilter(filter: Filter<T>, fflag: Int, nflag: Int, idcol: Int) {
1.103 @@ -594,27 +561,10 @@
1.104 querySingle { it.extractIssue() }
1.105 }
1.106
1.107 - private fun insertVersionInfo(id: Int, issue: Issue) {
1.108 - withStatement("insert into lpit_issue_affected_version (issueid, versionid) values (?,?)") {
1.109 - setInt(1, id)
1.110 - issue.affectedVersions.forEach {
1.111 - setInt(2, it.id)
1.112 - executeUpdate()
1.113 - }
1.114 - }
1.115 - withStatement("insert into lpit_issue_resolved_version (issueid, versionid) values (?,?)") {
1.116 - setInt(1, id)
1.117 - issue.resolvedVersions.forEach {
1.118 - setInt(2, it.id)
1.119 - executeUpdate()
1.120 - }
1.121 - }
1.122 - }
1.123 -
1.124 override fun insertIssue(issue: Issue): Int {
1.125 val id = withStatement(
1.126 """
1.127 - insert into lpit_issue (component, status, category, subject, description, assignee, eta, project)
1.128 + insert into lpit_issue (component, status, category, subject, description, assignee, eta, affected, resolved, project)
1.129 values (?, ?::issue_status, ?::issue_category, ?, ?, ?, ?, ?)
1.130 returning issueid
1.131 """.trimIndent()
1.132 @@ -623,7 +573,6 @@
1.133 setInt(col, issue.project.id)
1.134 querySingle { it.getInt(1) }!!
1.135 }
1.136 - insertVersionInfo(id, issue)
1.137 return id
1.138 }
1.139
1.140 @@ -632,7 +581,7 @@
1.141 """
1.142 update lpit_issue set updated = now(),
1.143 component = ?, status = ?::issue_status, category = ?::issue_category, subject = ?,
1.144 - description = ?, assignee = ?, eta = ?
1.145 + description = ?, assignee = ?, eta = ?, affected = ?, resolved = ?
1.146 where issueid = ?
1.147 """.trimIndent()
1.148 ) {
1.149 @@ -640,17 +589,6 @@
1.150 setInt(col, issue.id)
1.151 executeUpdate()
1.152 }
1.153 -
1.154 - // TODO: improve by only inserting / deleting changed version information
1.155 - withStatement("delete from lpit_issue_affected_version where issueid = ?") {
1.156 - setInt(1, issue.id)
1.157 - executeUpdate()
1.158 - }
1.159 - withStatement("delete from lpit_issue_resolved_version where issueid = ?") {
1.160 - setInt(1, issue.id)
1.161 - executeUpdate()
1.162 - }
1.163 - insertVersionInfo(issue.id, issue)
1.164 }
1.165
1.166 //</editor-fold>
