1.1 --- a/src/main/kotlin/de/uapcore/lightpit/dao/PostgresDataAccessObject.kt Sat Jan 23 14:47:59 2021 +0100
1.2 +++ b/src/main/kotlin/de/uapcore/lightpit/dao/PostgresDataAccessObject.kt Fri Apr 02 11:59:14 2021 +0200
1.3 @@ -26,8 +26,11 @@
1.4 package de.uapcore.lightpit.dao
1.5
1.6 import de.uapcore.lightpit.entities.*
1.7 -import de.uapcore.lightpit.filter.*
1.8 import de.uapcore.lightpit.types.WebColor
1.9 +import de.uapcore.lightpit.util.*
1.10 +import de.uapcore.lightpit.viewmodel.ComponentSummary
1.11 +import de.uapcore.lightpit.viewmodel.IssueSummary
1.12 +import de.uapcore.lightpit.viewmodel.VersionSummary
1.13 import java.sql.Connection
1.14 import java.sql.PreparedStatement
1.15 import java.sql.ResultSet
1.16 @@ -129,15 +132,19 @@
1.17 //</editor-fold>
1.18
1.19 //<editor-fold desc="Version">
1.20 +
1.21 + private fun obtainVersion(rs: ResultSet) =
1.22 + Version(rs.getInt("versionid"), rs.getInt("project")).apply {
1.23 + name = rs.getString("name")
1.24 + node = rs.getString("node")
1.25 + ordinal = rs.getInt("ordinal")
1.26 + status = rs.getEnum("status")
1.27 + }
1.28 +
1.29 private fun selectVersions(stmt: PreparedStatement) = sequence {
1.30 stmt.executeQuery().use { rs ->
1.31 while (rs.next()) {
1.32 - yield(Version(rs.getInt("versionid"), rs.getInt("project")).apply {
1.33 - name = rs.getString("name")
1.34 - node = rs.getString("node")
1.35 - ordinal = rs.getInt("ordinal")
1.36 - status = rs.getEnum("status")
1.37 - })
1.38 + yield(obtainVersion(rs))
1.39 }
1.40 }
1.41 }
1.42 @@ -163,6 +170,36 @@
1.43 """
1.44 )
1.45 }
1.46 + private val stmtVersionSummaries by lazy {
1.47 + connection.prepareStatement(
1.48 + """
1.49 + with version_map(issueid, versionid, isresolved) as (
1.50 + select issueid, versionid, 1
1.51 + from lpit_issue_resolved_version
1.52 + union
1.53 + select issueid, versionid, 0
1.54 + from lpit_issue_affected_version
1.55 + ),
1.56 + issues as (
1.57 + select versionid, phase, isresolved, count(issueid) as total
1.58 + from lpit_issue
1.59 + join version_map using (issueid)
1.60 + join lpit_issue_phases using (status)
1.61 + group by versionid, phase, isresolved
1.62 + ),
1.63 + summary as (
1.64 + select versionid, phase, isresolved, total
1.65 + from lpit_version v
1.66 + left join issues using (versionid)
1.67 + where v.project = ?
1.68 + )
1.69 + select versionid, project, name, node, ordinal, status, phase, isresolved, total
1.70 + from lpit_version
1.71 + join summary using (versionid)
1.72 + order by ordinal, name
1.73 + """
1.74 + )
1.75 + }
1.76 private val stmtVersionByID by lazy {
1.77 connection.prepareStatement(
1.78 """${versionQuery}
1.79 @@ -199,6 +236,27 @@
1.80 return selectVersions(stmtVersions).toList()
1.81 }
1.82
1.83 + override fun listVersionSummaries(project: Project): List<VersionSummary> {
1.84 + stmtVersionSummaries.setInt(1, project.id)
1.85 + return sequence {
1.86 + stmtVersionSummaries.executeQuery().use { rs ->
1.87 + while (rs.next()) {
1.88 + val versionSummary = VersionSummary(obtainVersion(rs))
1.89 + val phase = rs.getInt("phase")
1.90 + val total = rs.getInt("total")
1.91 + val issueSummary =
1.92 + if (rs.getBoolean("isresolved")) versionSummary.resolvedTotal else versionSummary.reportedTotal
1.93 + when (phase) {
1.94 + 0 -> issueSummary.open = total
1.95 + 1 -> issueSummary.active = total
1.96 + 2 -> issueSummary.done = total
1.97 + }
1.98 + yield(versionSummary)
1.99 + }
1.100 + }
1.101 + }.toList()
1.102 + }
1.103 +
1.104 override fun findVersion(id: Int): Version? {
1.105 stmtVersionByID.setInt(1, id)
1.106 return selectVersions(stmtVersionByID).firstOrNull()
1.107 @@ -224,21 +282,25 @@
1.108 //</editor-fold>
1.109
1.110 //<editor-fold desc="Component">
1.111 +
1.112 + private fun obtainComponent(rs: ResultSet): Component =
1.113 + Component(rs.getInt("id"), rs.getInt("project")).apply {
1.114 + name = rs.getString("name")
1.115 + node = rs.getString("node")
1.116 + color = try {
1.117 + WebColor(rs.getString("color"))
1.118 + } catch (ex: IllegalArgumentException) {
1.119 + WebColor("000000")
1.120 + }
1.121 + ordinal = rs.getInt("ordinal")
1.122 + description = rs.getString("description")
1.123 + lead = selectUserInfo(rs)
1.124 + }
1.125 +
1.126 private fun selectComponents(stmt: PreparedStatement) = sequence {
1.127 stmt.executeQuery().use { rs ->
1.128 while (rs.next()) {
1.129 - yield(Component(rs.getInt("id"), rs.getInt("project")).apply {
1.130 - name = rs.getString("name")
1.131 - node = rs.getString("node")
1.132 - color = try {
1.133 - WebColor(rs.getString("color"))
1.134 - } catch (ex: IllegalArgumentException) {
1.135 - WebColor("000000")
1.136 - }
1.137 - ordinal = rs.getInt("ordinal")
1.138 - description = rs.getString("description")
1.139 - lead = selectUserInfo(rs)
1.140 - })
1.141 + yield(obtainComponent(rs))
1.142 }
1.143 }
1.144 }
1.145 @@ -272,6 +334,30 @@
1.146 """
1.147 )
1.148 }
1.149 + private val stmtComponentSummaries by lazy {
1.150 + connection.prepareStatement(
1.151 + """
1.152 + with issues as (
1.153 + select component, phase, count(issueid) as total
1.154 + from lpit_issue
1.155 + join lpit_issue_phases using (status)
1.156 + group by component, phase
1.157 + ),
1.158 + summary as (
1.159 + select c.id, phase, total
1.160 + from lpit_component c
1.161 + left join issues i on c.id = i.component
1.162 + where c.project = ?
1.163 + )
1.164 + select c.id, project, name, node, color, ordinal, description,
1.165 + userid, username, givenname, lastname, mail, phase, total
1.166 + from lpit_component c
1.167 + left join lpit_user on lead = userid
1.168 + join summary s on c.id = s.id
1.169 + order by ordinal, name
1.170 + """
1.171 + )
1.172 + }
1.173 private val stmtComponentById by lazy {
1.174 connection.prepareStatement(
1.175 """${componentQuery}
1.176 @@ -305,6 +391,25 @@
1.177 return selectComponents(stmtComponents).toList()
1.178 }
1.179
1.180 + override fun listComponentSummaries(project: Project): List<ComponentSummary> {
1.181 + stmtComponentSummaries.setInt(1, project.id)
1.182 + return sequence {
1.183 + stmtComponentSummaries.executeQuery().use { rs ->
1.184 + while (rs.next()) {
1.185 + val componentSummary = ComponentSummary(obtainComponent(rs))
1.186 + val phase = rs.getInt("phase")
1.187 + val total = rs.getInt("total")
1.188 + when (phase) {
1.189 + 0 -> componentSummary.issueSummary.open = total
1.190 + 1 -> componentSummary.issueSummary.active = total
1.191 + 2 -> componentSummary.issueSummary.done = total
1.192 + }
1.193 + yield(componentSummary)
1.194 + }
1.195 + }
1.196 + }.toList()
1.197 + }
1.198 +
1.199 override fun findComponent(id: Int): Component? {
1.200 stmtComponentById.setInt(1, id)
1.201 return selectComponents(stmtComponentById).firstOrNull()
1.202 @@ -471,7 +576,7 @@
1.203 node = rs.getString("componentnode")
1.204 }
1.205 }
1.206 - val issue = Issue(rs.getInt("issueid"), proj, comp).apply {
1.207 + val issue = Issue(rs.getInt("issueid"), proj).apply {
1.208 component = comp
1.209 status = rs.getEnum("status")
1.210 category = rs.getEnum("category")
1.211 @@ -672,14 +777,15 @@
1.212 }
1.213 }
1.214
1.215 - override fun insertIssue(issue: Issue) {
1.216 + override fun insertIssue(issue: Issue): Int {
1.217 val col = setIssueFields(stmtInsertIssue, issue)
1.218 stmtInsertIssue.setInt(col, issue.project.id)
1.219 - stmtInsertIssue.executeQuery().use { rs ->
1.220 + val id = stmtInsertIssue.executeQuery().use { rs ->
1.221 rs.next()
1.222 - issue.id = rs.getInt(1)
1.223 + rs.getInt(1)
1.224 }
1.225 insertVersionInfo(issue)
1.226 + return id
1.227 }
1.228
1.229 override fun updateIssue(issue: Issue) {
