src/main/kotlin/de/uapcore/lightpit/dao/PostgresDataAccessObject.kt@a83f1ab56898
src/main/kotlin/de/uapcore/lightpit/dao/PostgresDataAccessObject.kt
Tue, 11 May 2021 17:30:36 +0200
- author
- Mike Becker <universe@uap-core.de>
- date
- Tue, 11 May 2021 17:30:36 +0200
- changeset 190
- a83f1ab56898
- parent 189
- f7de8158b41c
- child 203
- 7d5b16379768
- permissions
- -rw-r--r--
fixes #138 - ResultSet processing for summaries
1 /*
2 * Copyright 2021 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 */
26 package de.uapcore.lightpit.dao
28 import de.uapcore.lightpit.entities.*
29 import de.uapcore.lightpit.types.WebColor
30 import de.uapcore.lightpit.util.*
31 import de.uapcore.lightpit.viewmodel.ComponentSummary
32 import de.uapcore.lightpit.viewmodel.IssueSummary
33 import de.uapcore.lightpit.viewmodel.VersionSummary
34 import org.intellij.lang.annotations.Language
35 import java.sql.Connection
36 import java.sql.PreparedStatement
37 import java.sql.ResultSet
39 class PostgresDataAccessObject(private val connection: Connection) : DataAccessObject {
41 /**
42 * Prepares the given [sql] statement and executes the [block] function with the prepared statement as receiver.
43 * The statement is then closed properly.
44 */
45 private fun <R> withStatement(@Language("SQL") sql: String, block: PreparedStatement.() -> R) =
46 connection.prepareStatement(sql).use(block)
48 /**
49 * Prepares the given [sql] statement and executes the [block] function on that statement.
50 * The statement is then closed properly.
51 */
52 private fun <R> useStatement(@Language("SQL") sql: String, block: (PreparedStatement) -> R) =
53 connection.prepareStatement(sql).use(block)
55 /**
56 * Executes the statement and iterates the whole result set extracting the rows with the given [extractor] function.
57 */
58 private fun <T> PreparedStatement.queryAll(extractor: (ResultSet) -> T): List<T> = executeQuery().use {
59 sequence {
60 while (it.next()) {
61 yield(extractor(it))
62 }
63 }.toList()
64 }
66 /**
67 * Executes the statement and extracts a single row with the given [extractor] function.
68 * If the result set is empty, null is returned.
69 */
70 private fun <T> PreparedStatement.querySingle(extractor: (ResultSet) -> T): T? = executeQuery().use {
71 return if (it.next()) extractor(it) else null
72 }
74 //<editor-fold desc="User">
75 //language=SQL
76 private val userQuery = "select userid, username, lastname, givenname, mail from lpit_user"
78 private fun ResultSet.extractUser() = User(getInt("userid")).apply {
79 username = getString("username")
80 givenname = getString("givenname")
81 lastname = getString("lastname")
82 mail = getString("mail")
83 }
85 private fun ResultSet.containsUserInfo(): Boolean {
86 getInt("userid")
87 return !wasNull()
88 }
90 private fun ResultSet.extractOptionalUser() = if (containsUserInfo()) extractUser() else null
92 override fun listUsers() =
93 withStatement("$userQuery where userid > 0 order by username") {
94 queryAll { it.extractUser() }
95 }
97 override fun findUser(id: Int): User? =
98 withStatement("$userQuery where userid = ?") {
99 setInt(1, id)
100 querySingle { it.extractUser() }
101 }
103 override fun findUserByName(username: String): User? =
104 withStatement("$userQuery where lower(username) = lower(?)") {
105 setString(1, username)
106 querySingle { it.extractUser() }
107 }
109 override fun insertUser(user: User) {
110 withStatement("insert into lpit_user (username, lastname, givenname, mail) values (?, ?, ?, ?)") {
111 with(user) {
112 setStringSafe(1, username)
113 setStringOrNull(2, lastname)
114 setStringOrNull(3, givenname)
115 setStringOrNull(4, mail)
116 }
117 executeUpdate()
118 }
119 }
121 override fun updateUser(user: User) {
122 withStatement("update lpit_user set lastname = ?, givenname = ?, mail = ? where userid = ?") {
123 with(user) {
124 setStringOrNull(1, lastname)
125 setStringOrNull(2, givenname)
126 setStringOrNull(3, mail)
127 setInt(4, id)
128 }
129 executeUpdate()
130 }
131 }
132 //</editor-fold>
134 //<editor-fold desc="Version">
135 //language=SQL
136 private val versionQuery = "select versionid, project, name, node, ordinal, status from lpit_version"
138 private fun ResultSet.extractVersion() =
139 Version(getInt("versionid"), getInt("project")).apply {
140 name = getString("name")
141 node = getString("node")
142 ordinal = getInt("ordinal")
143 status = getEnum("status")
144 }
146 override fun listVersions(project: Project): List<Version> =
147 withStatement("$versionQuery where project = ? order by ordinal desc, lower(name) desc") {
148 setInt(1, project.id)
149 queryAll { it.extractVersion() }
150 }
152 override fun listVersionSummaries(project: Project): List<VersionSummary> =
153 withStatement(
154 """
155 with version_map(issueid, versionid, isresolved) as (
156 select issueid, versionid, true
157 from lpit_issue_resolved_version
158 union
159 select issueid, versionid, false
160 from lpit_issue_affected_version
161 ),
162 issues as (
163 select versionid, phase, isresolved, count(issueid) as total
164 from lpit_issue
165 join version_map using (issueid)
166 join lpit_issue_phases using (status)
167 group by versionid, phase, isresolved
168 ),
169 summary as (
170 select versionid, phase, isresolved, total
171 from lpit_version v
172 left join issues using (versionid)
173 )
174 select v.versionid, project, name, node, ordinal, status,
175 ro.total as resolved_open, ra.total as resolved_active, rd.total as resolved_done,
176 ao.total as affected_open, aa.total as affected_active, ad.total as affected_done
177 from lpit_version v
178 left join summary ro on ro.versionid = v.versionid and ro.phase = 0 and ro.isresolved
179 left join summary ra on ra.versionid = v.versionid and ra.phase = 1 and ra.isresolved
180 left join summary rd on rd.versionid = v.versionid and rd.phase = 2 and rd.isresolved
181 left join summary ao on ao.versionid = v.versionid and ao.phase = 0 and not ao.isresolved
182 left join summary aa on aa.versionid = v.versionid and aa.phase = 1 and not aa.isresolved
183 left join summary ad on ad.versionid = v.versionid and ad.phase = 2 and not ad.isresolved
184 where v.project = ?
185 order by ordinal, name
186 """.trimIndent()
187 ) {
188 setInt(1, project.id)
189 queryAll { rs ->
190 VersionSummary(rs.extractVersion()).apply {
191 reportedTotal.open = rs.getInt("affected_open")
192 reportedTotal.active = rs.getInt("affected_active")
193 reportedTotal.done = rs.getInt("affected_done")
194 resolvedTotal.open = rs.getInt("resolved_open")
195 resolvedTotal.active = rs.getInt("resolved_active")
196 resolvedTotal.done = rs.getInt("resolved_done")
197 }
198 }
199 }
201 override fun findVersion(id: Int): Version? =
202 withStatement("$versionQuery where versionid = ?") {
203 setInt(1, id)
204 querySingle { it.extractVersion() }
205 }
207 override fun findVersionByNode(project: Project, node: String): Version? =
208 withStatement("$versionQuery where project = ? and node = ?") {
209 setInt(1, project.id)
210 setString(2, node)
211 querySingle { it.extractVersion() }
212 }
214 override fun insertVersion(version: Version) {
215 withStatement("insert into lpit_version (name, node, ordinal, status, project) values (?, ?, ?, ?::version_status, ?)") {
216 with(version) {
217 setStringSafe(1, name)
218 setStringSafe(2, node)
219 setInt(3, ordinal)
220 setEnum(4, status)
221 setInt(5, version.projectid)
222 }
223 executeUpdate()
224 }
226 }
228 override fun updateVersion(version: Version) {
229 withStatement("update lpit_version set name = ?, node = ?, ordinal = ?, status = ?::version_status where versionid = ?") {
230 with(version) {
231 setStringSafe(1, name)
232 setStringSafe(2, node)
233 setInt(3, ordinal)
234 setEnum(4, status)
235 setInt(5, id)
236 }
237 executeUpdate()
238 }
239 }
240 //</editor-fold>
242 //<editor-fold desc="Component">
243 //language=SQL
244 private val componentQuery =
245 """
246 select id, project, name, node, color, ordinal, description,
247 userid, username, givenname, lastname, mail
248 from lpit_component
249 left join lpit_user on lead = userid
250 """.trimIndent()
252 private fun ResultSet.extractComponent(): Component =
253 Component(getInt("id"), getInt("project")).apply {
254 name = getString("name")
255 node = getString("node")
256 color = try {
257 WebColor(getString("color"))
258 } catch (ex: IllegalArgumentException) {
259 WebColor("000000")
260 }
261 ordinal = getInt("ordinal")
262 description = getString("description")
263 lead = extractOptionalUser()
264 }
266 private fun PreparedStatement.setComponent(index: Int, component: Component): Int {
267 with(component) {
268 var i = index
269 setStringSafe(i++, name)
270 setStringSafe(i++, node)
271 setStringSafe(i++, color.hex)
272 setInt(i++, ordinal)
273 setStringOrNull(i++, description)
274 setIntOrNull(i++, lead?.id)
275 return i
276 }
277 }
279 override fun listComponents(project: Project): List<Component> =
280 withStatement("$componentQuery where project = ? order by ordinal, lower(name)") {
281 setInt(1, project.id)
282 queryAll { it.extractComponent() }
283 }
285 override fun listComponentSummaries(project: Project): List<ComponentSummary> =
286 withStatement(
287 """
288 with issues as (
289 select component, phase, count(issueid) as total
290 from lpit_issue
291 join lpit_issue_phases using (status)
292 group by component, phase
293 ),
294 summary as (
295 select c.id, phase, total
296 from lpit_component c
297 left join issues i on c.id = i.component
298 )
299 select c.id, project, name, node, color, ordinal, description,
300 userid, username, givenname, lastname, mail,
301 open.total as open, active.total as active, done.total as done
302 from lpit_component c
303 left join lpit_user on lead = userid
304 left join summary open on c.id = open.id and open.phase = 0
305 left join summary active on c.id = active.id and active.phase = 1
306 left join summary done on c.id = done.id and done.phase = 2
307 where c.project = ?
308 order by ordinal, name
309 """.trimIndent()
310 ) {
311 setInt(1, project.id)
312 queryAll { rs ->
313 ComponentSummary(rs.extractComponent()).apply {
314 issueSummary.open = rs.getInt("open")
315 issueSummary.active = rs.getInt("active")
316 issueSummary.done = rs.getInt("done")
317 }
318 }
319 }
321 override fun findComponent(id: Int): Component? =
322 withStatement("$componentQuery where id = ?") {
323 setInt(1, id)
324 querySingle { it.extractComponent() }
325 }
327 override fun findComponentByNode(project: Project, node: String): Component? =
328 withStatement("$componentQuery where project = ? and node = ?") {
329 setInt(1, project.id)
330 setString(2, node)
331 querySingle { it.extractComponent() }
332 }
334 override fun insertComponent(component: Component) {
335 withStatement("insert into lpit_component (name, node, color, ordinal, description, lead, project) values (?, ?, ?, ?, ?, ?, ?)") {
336 val col = setComponent(1, component)
337 setInt(col, component.projectid)
338 executeUpdate()
339 }
340 }
342 override fun updateComponent(component: Component) {
343 withStatement("update lpit_component set name = ?, node = ?, color = ?, ordinal = ?, description = ?, lead = ? where id = ?") {
344 val col = setComponent(1, component)
345 setInt(col, component.id)
346 executeUpdate()
347 }
348 }
350 //</editor-fold>
352 //<editor-fold desc="Project">
354 //language=SQL
355 private val projectQuery =
356 """
357 select projectid, name, node, ordinal, description, repourl,
358 userid, username, lastname, givenname, mail
359 from lpit_project
360 left join lpit_user owner on lpit_project.owner = owner.userid
361 """.trimIndent()
363 private fun ResultSet.extractProject() =
364 Project(getInt("projectid")).apply {
365 name = getString("name")
366 node = getString("node")
367 ordinal = getInt("ordinal")
368 description = getString("description")
369 repoUrl = getString("repourl")
370 owner = extractOptionalUser()
371 }
373 private fun PreparedStatement.setProject(index: Int, project: Project): Int {
374 var i = index
375 with(project) {
376 setStringSafe(i++, name)
377 setStringSafe(i++, node)
378 setInt(i++, ordinal)
379 setStringOrNull(i++, description)
380 setStringOrNull(i++, repoUrl)
381 setIntOrNull(i++, owner?.id)
382 }
383 return i
384 }
386 override fun listProjects(): List<Project> =
387 withStatement("$projectQuery order by ordinal, lower(name)") {
388 queryAll { it.extractProject() }
389 }
391 override fun findProject(id: Int): Project? =
392 withStatement("$projectQuery where projectid = ?") {
393 setInt(1, id)
394 querySingle { it.extractProject() }
395 }
397 override fun findProjectByNode(node: String): Project? =
398 withStatement("$projectQuery where node = ?") {
399 setString(1, node)
400 querySingle { it.extractProject() }
401 }
403 override fun insertProject(project: Project) {
404 withStatement("insert into lpit_project (name, node, ordinal, description, repourl, owner) values (?, ?, ?, ?, ?, ?)") {
405 setProject(1, project)
406 executeUpdate()
407 }
408 }
410 override fun updateProject(project: Project) {
411 withStatement("update lpit_project set name = ?, node = ?, ordinal = ?, description = ?, repourl = ?, owner = ? where projectid = ?") {
412 val col = setProject(1, project)
413 setInt(col, project.id)
414 executeUpdate()
415 }
416 }
418 override fun collectIssueSummary(project: Project): IssueSummary =
419 withStatement(
420 """
421 select phase, count(*) as total
422 from lpit_issue
423 join lpit_issue_phases using(status)
424 where project = ?
425 group by phase
426 """.trimIndent()
427 ) {
428 setInt(1, project.id)
429 executeQuery().use {
430 val summary = IssueSummary()
431 while (it.next()) {
432 val phase = it.getInt("phase")
433 val total = it.getInt("total")
434 when (phase) {
435 0 -> summary.open = total
436 1 -> summary.active = total
437 2 -> summary.done = total
438 }
439 }
440 summary
441 }
442 }
444 //</editor-fold>
446 //<editor-fold desc="Issue">
448 //language=SQL
449 private val issueQuery =
450 """
451 select issueid,
452 i.project, p.name as projectname, p.node as projectnode,
453 component, c.name as componentname, c.node as componentnode,
454 status, category, subject, i.description,
455 userid, username, givenname, lastname, mail,
456 created, updated, eta
457 from lpit_issue i
458 join lpit_project p on i.project = projectid
459 left join lpit_component c on component = c.id
460 left join lpit_user on userid = assignee
461 """.trimIndent()
463 private fun ResultSet.extractIssue(): Issue {
464 val proj = Project(getInt("project")).apply {
465 name = getString("projectname")
466 node = getString("projectnode")
467 }
468 val comp = getInt("component").let {
469 if (wasNull()) null else
470 Component(it, proj.id).apply {
471 name = getString("componentname")
472 node = getString("componentnode")
473 }
474 }
475 val issue = Issue(getInt("issueid"), proj).apply {
476 component = comp
477 status = getEnum("status")
478 category = getEnum("category")
479 subject = getString("subject")
480 description = getString("description")
481 assignee = extractOptionalUser()
482 created = getTimestamp("created")
483 updated = getTimestamp("updated")
484 eta = getDate("eta")
485 }
487 fun versionQuery(table: String) =
488 """
489 select versionid, project, name, status, ordinal, node
490 from lpit_version join $table using (versionid)
491 where issueid = ?
492 order by ordinal, name
493 """.trimIndent()
495 issue.affectedVersions = withStatement(versionQuery("lpit_issue_affected_version")) {
496 setInt(1, issue.id)
497 queryAll { it.extractVersion() }
498 }
499 issue.resolvedVersions = withStatement(versionQuery("lpit_issue_resolved_version")) {
500 setInt(1, issue.id)
501 queryAll { it.extractVersion() }
502 }
503 return issue
504 }
506 private fun PreparedStatement.setIssue(index: Int, issue: Issue): Int {
507 var i = index
508 with(issue) {
509 setIntOrNull(i++, component?.id)
510 setEnum(i++, status)
511 setEnum(i++, category)
512 setStringSafe(i++, subject)
513 setStringOrNull(i++, description)
514 setIntOrNull(i++, assignee?.id)
515 setDateOrNull(i++, eta)
516 }
517 return i
518 }
520 override fun listIssues(filter: IssueFilter): List<Issue> =
521 withStatement(
522 """
523 with issue_version as (
524 select issueid, versionid from lpit_issue_affected_version
525 union select issueid, versionid from lpit_issue_resolved_version
526 ),
527 filtered_issues as (
528 select distinct issueid from lpit_issue
529 left join issue_version using (issueid)
530 where
531 (not ? or project = ?) and
532 (not ? or versionid = ?) and (not ? or versionid is null) and
533 (not ? or component = ?) and (not ? or component is null)
534 )
535 $issueQuery join filtered_issues using (issueid)
536 """.trimIndent()
537 ) {
538 fun <T : Entity> applyFilter(filter: Filter<T>, fflag: Int, nflag: Int, idcol: Int) {
539 when (filter) {
540 is AllFilter -> {
541 setBoolean(fflag, false)
542 setBoolean(nflag, false)
543 setInt(idcol, 0)
544 }
545 is NoneFilter -> {
546 setBoolean(fflag, false)
547 setBoolean(nflag, true)
548 setInt(idcol, 0)
549 }
550 is SpecificFilter -> {
551 setBoolean(fflag, true)
552 setBoolean(nflag, false)
553 setInt(idcol, filter.obj.id)
554 }
555 else -> {
556 TODO("Implement range filter.")
557 }
558 }
559 }
560 when (filter.project) {
561 is AllFilter -> {
562 setBoolean(1, false)
563 setInt(2, 0)
564 }
565 is SpecificFilter -> {
566 setBoolean(1, true)
567 setInt(2, filter.project.obj.id)
568 }
569 else -> throw IllegalArgumentException()
570 }
571 applyFilter(filter.version, 3, 5, 4)
572 applyFilter(filter.component, 6, 8, 7)
574 queryAll { it.extractIssue() }
575 }
577 override fun findIssue(id: Int): Issue? =
578 withStatement("$issueQuery where issueid = ?") {
579 setInt(1, id)
580 querySingle { it.extractIssue() }
581 }
583 private fun insertVersionInfo(id: Int, issue: Issue) {
584 withStatement("insert into lpit_issue_affected_version (issueid, versionid) values (?,?)") {
585 setInt(1, id)
586 issue.affectedVersions.forEach {
587 setInt(2, it.id)
588 executeUpdate()
589 }
590 }
591 withStatement("insert into lpit_issue_resolved_version (issueid, versionid) values (?,?)") {
592 setInt(1, id)
593 issue.resolvedVersions.forEach {
594 setInt(2, it.id)
595 executeUpdate()
596 }
597 }
598 }
600 override fun insertIssue(issue: Issue): Int {
601 val id = withStatement(
602 """
603 insert into lpit_issue (component, status, category, subject, description, assignee, eta, project)
604 values (?, ?::issue_status, ?::issue_category, ?, ?, ?, ?, ?)
605 returning issueid
606 """.trimIndent()
607 ) {
608 val col = setIssue(1, issue)
609 setInt(col, issue.project.id)
610 querySingle { it.getInt(1) }!!
611 }
612 insertVersionInfo(id, issue)
613 return id
614 }
616 override fun updateIssue(issue: Issue) {
617 withStatement(
618 """
619 update lpit_issue set updated = now(),
620 component = ?, status = ?::issue_status, category = ?::issue_category, subject = ?,
621 description = ?, assignee = ?, eta = ?
622 where issueid = ?
623 """.trimIndent()
624 ) {
625 val col = setIssue(1, issue)
626 setInt(col, issue.id)
627 executeUpdate()
628 }
630 // TODO: improve by only inserting / deleting changed version information
631 withStatement("delete from lpit_issue_affected_version where issueid = ?") {
632 setInt(1, issue.id)
633 executeUpdate()
634 }
635 withStatement("delete from lpit_issue_resolved_version where issueid = ?") {
636 setInt(1, issue.id)
637 executeUpdate()
638 }
639 insertVersionInfo(issue.id, issue)
640 }
642 //</editor-fold>
644 //<editor-fold desc="IssueComment">
646 private fun ResultSet.extractIssueComment() =
647 IssueComment(getInt("commentid"), getInt("issueid")).apply {
648 created = getTimestamp("created")
649 updated = getTimestamp("updated")
650 updateCount = getInt("updatecount")
651 comment = getString("comment")
652 author = extractOptionalUser()
653 }
655 override fun listComments(issue: Issue): List<IssueComment> =
656 withStatement("select * from lpit_issue_comment left join lpit_user using (userid) where issueid = ? order by created") {
657 setInt(1, issue.id)
658 queryAll { it.extractIssueComment() }
659 }
661 override fun insertComment(issueComment: IssueComment) {
662 useStatement("update lpit_issue set updated = now() where issueid = ?") { updateIssueDate ->
663 withStatement("insert into lpit_issue_comment (issueid, comment, userid) values (?, ? ,?)") {
664 with(issueComment) {
665 updateIssueDate.setInt(1, issueid)
666 setInt(1, issueid)
667 setStringSafe(2, comment)
668 setIntOrNull(3, author?.id)
669 }
670 executeUpdate()
671 updateIssueDate.executeUpdate()
672 }
673 }
674 }
675 //</editor-fold>
676 }
