src/main/kotlin/de/uapcore/lightpit/dao/PostgresDataAccessObject.kt@703591e739f4
src/main/kotlin/de/uapcore/lightpit/dao/PostgresDataAccessObject.kt
Mon, 30 Oct 2023 14:44:36 +0100
- author
- Mike Becker <universe@uap-core.de>
- date
- Mon, 30 Oct 2023 14:44:36 +0100
- changeset 292
- 703591e739f4
- parent 284
- 671c1c8fbf1c
- permissions
- -rw-r--r--
add possibility to show issues w/o version or component - fixes #335
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.CommitRef
30 import de.uapcore.lightpit.types.IssueHistoryType
31 import de.uapcore.lightpit.types.RelationType
32 import de.uapcore.lightpit.types.WebColor
33 import de.uapcore.lightpit.viewmodel.ComponentSummary
34 import de.uapcore.lightpit.viewmodel.IssueSummary
35 import de.uapcore.lightpit.viewmodel.VersionSummary
36 import org.intellij.lang.annotations.Language
37 import java.sql.Connection
38 import java.sql.PreparedStatement
39 import java.sql.ResultSet
41 class PostgresDataAccessObject(private val connection: Connection) : DataAccessObject {
43 /**
44 * Prepares the given [sql] statement and executes the [block] function with the prepared statement as receiver.
45 * The statement is then closed properly.
46 */
47 private fun <R> withStatement(@Language("SQL") sql: String, block: PreparedStatement.() -> R) =
48 connection.prepareStatement(sql).use(block)
50 /**
51 * Prepares the given [sql] statement and executes the [block] function on that statement.
52 * The statement is then closed properly.
53 */
54 private fun <R> useStatement(@Language("SQL") sql: String, block: (PreparedStatement) -> R) =
55 connection.prepareStatement(sql).use(block)
57 /**
58 * Executes the statement and iterates the whole result set extracting the rows with the given [extractor] function.
59 */
60 private fun <T> PreparedStatement.queryAll(extractor: (ResultSet) -> T): List<T> = executeQuery().use {
61 sequence {
62 while (it.next()) {
63 yield(extractor(it))
64 }
65 }.toList()
66 }
68 /**
69 * Executes the statement and extracts a single row with the given [extractor] function.
70 * If the result set is empty, null is returned.
71 */
72 private fun <T> PreparedStatement.querySingle(extractor: (ResultSet) -> T): T? = executeQuery().use {
73 return if (it.next()) extractor(it) else null
74 }
76 //<editor-fold desc="User">
77 //language=SQL
78 private val userQuery = "select userid, username, lastname, givenname, mail from lpit_user"
80 private fun ResultSet.extractUser() = User(getInt("userid")).apply {
81 username = getString("username")
82 givenname = getString("givenname")
83 lastname = getString("lastname")
84 mail = getString("mail")
85 }
87 private fun ResultSet.containsUserInfo(): Boolean {
88 getInt("userid")
89 return !wasNull()
90 }
92 private fun ResultSet.extractOptionalUser() = if (containsUserInfo()) extractUser() else null
94 override fun listUsers() =
95 withStatement("$userQuery where userid > 0 order by username") {
96 queryAll { it.extractUser() }
97 }
99 override fun findUser(id: Int): User? =
100 withStatement("$userQuery where userid = ?") {
101 setInt(1, id)
102 querySingle { it.extractUser() }
103 }
105 override fun findUserByName(username: String): User? =
106 withStatement("$userQuery where lower(username) = lower(?)") {
107 setString(1, username)
108 querySingle { it.extractUser() }
109 }
111 override fun insertUser(user: User) {
112 withStatement("insert into lpit_user (username, lastname, givenname, mail) values (?, ?, ?, ?)") {
113 with(user) {
114 setStringSafe(1, username)
115 setStringOrNull(2, lastname)
116 setStringOrNull(3, givenname)
117 setStringOrNull(4, mail)
118 }
119 executeUpdate()
120 }
121 }
123 override fun updateUser(user: User) {
124 withStatement("update lpit_user set lastname = ?, givenname = ?, mail = ? where userid = ?") {
125 with(user) {
126 setStringOrNull(1, lastname)
127 setStringOrNull(2, givenname)
128 setStringOrNull(3, mail)
129 setInt(4, id)
130 }
131 executeUpdate()
132 }
133 }
134 //</editor-fold>
136 //<editor-fold desc="Version">
137 //language=SQL
138 private val versionQuery = "select versionid, project, name, node, ordinal, status, release, eol from lpit_version"
140 private fun ResultSet.extractVersion() =
141 Version(getInt("versionid"), getInt("project")).apply {
142 name = getString("name")
143 node = getString("node")
144 ordinal = getInt("ordinal")
145 release = getDate("release")
146 eol = getDate("eol")
147 status = getEnum("status")
148 }
150 override fun listVersions(project: Project): List<Version> =
151 withStatement("$versionQuery where project = ? order by ordinal desc, lower(name) desc") {
152 setInt(1, project.id)
153 queryAll { it.extractVersion() }
154 }
156 override fun listVersionSummaries(project: Project): List<VersionSummary> =
157 withStatement(
158 """with
159 version_map as (
160 select issueid, status, resolved as versionid, true as isresolved from lpit_issue
161 union all
162 select issueid, status, affected as versionid, false as isresolved from lpit_issue
163 ), issues as (
164 select versionid, phase, isresolved, count(issueid) as total from version_map
165 join lpit_issue_phases using (status)
166 group by versionid, phase, isresolved
167 ),
168 summary as (
169 select versionid, phase, isresolved, total
170 from lpit_version v
171 left join issues using (versionid)
172 )
173 select v.versionid, project, name, node, ordinal, status, release, eol,
174 ro.total as resolved_open, ra.total as resolved_active, rd.total as resolved_done,
175 ao.total as affected_open, aa.total as affected_active, ad.total as affected_done
176 from lpit_version v
177 left join summary ro on ro.versionid = v.versionid and ro.phase = 0 and ro.isresolved
178 left join summary ra on ra.versionid = v.versionid and ra.phase = 1 and ra.isresolved
179 left join summary rd on rd.versionid = v.versionid and rd.phase = 2 and rd.isresolved
180 left join summary ao on ao.versionid = v.versionid and ao.phase = 0 and not ao.isresolved
181 left join summary aa on aa.versionid = v.versionid and aa.phase = 1 and not aa.isresolved
182 left join summary ad on ad.versionid = v.versionid and ad.phase = 2 and not ad.isresolved
183 where v.project = ?
184 order by ordinal desc, lower(name) desc
185 """.trimIndent()
186 ) {
187 setInt(1, project.id)
188 queryAll { rs ->
189 VersionSummary(rs.extractVersion()).apply {
190 reportedTotal.open = rs.getInt("affected_open")
191 reportedTotal.active = rs.getInt("affected_active")
192 reportedTotal.done = rs.getInt("affected_done")
193 resolvedTotal.open = rs.getInt("resolved_open")
194 resolvedTotal.active = rs.getInt("resolved_active")
195 resolvedTotal.done = rs.getInt("resolved_done")
196 }
197 }
198 }
200 override fun findVersion(id: Int): Version? =
201 withStatement("$versionQuery where versionid = ?") {
202 setInt(1, id)
203 querySingle { it.extractVersion() }
204 }
206 override fun findVersionByNode(project: Project, node: String): Version? =
207 withStatement("$versionQuery where project = ? and node = ?") {
208 setInt(1, project.id)
209 setString(2, node)
210 querySingle { it.extractVersion() }
211 }
213 override fun insertVersion(version: Version) {
214 withStatement("insert into lpit_version (name, node, ordinal, status, project, release, eol) values (?, ?, ?, ?::version_status, ?, ?, ?)") {
215 with(version) {
216 setStringSafe(1, name)
217 setStringSafe(2, node)
218 setInt(3, ordinal)
219 setEnum(4, status)
220 setInt(5, projectid)
221 setDateOrNull(6, release)
222 setDateOrNull(7, eol)
223 }
224 executeUpdate()
225 }
227 }
229 override fun updateVersion(version: Version) {
230 withStatement("update lpit_version set name = ?, node = ?, ordinal = ?, status = ?::version_status, release=?,eol=? where versionid = ?") {
231 with(version) {
232 setStringSafe(1, name)
233 setStringSafe(2, node)
234 setInt(3, ordinal)
235 setEnum(4, status)
236 setDateOrNull(5, version.release)
237 setDateOrNull(6, version.eol)
238 setInt(7, id)
239 }
240 executeUpdate()
241 }
242 }
243 //</editor-fold>
245 //<editor-fold desc="Component">
246 //language=SQL
247 private val componentQuery =
248 """
249 select id, project, name, node, color, ordinal, description, active,
250 userid, username, givenname, lastname, mail
251 from lpit_component
252 left join lpit_user on lead = userid
253 """.trimIndent()
255 private fun ResultSet.extractComponent(): Component =
256 Component(getInt("id"), getInt("project")).apply {
257 name = getString("name")
258 node = getString("node")
259 color = try {
260 WebColor(getString("color"))
261 } catch (ex: IllegalArgumentException) {
262 WebColor("000000")
263 }
264 ordinal = getInt("ordinal")
265 description = getString("description")
266 active = getBoolean("active")
267 lead = extractOptionalUser()
268 }
270 private fun PreparedStatement.setComponent(index: Int, component: Component): Int {
271 with(component) {
272 var i = index
273 setStringSafe(i++, name)
274 setStringSafe(i++, node)
275 setStringSafe(i++, color.hex)
276 setInt(i++, ordinal)
277 setStringOrNull(i++, description)
278 setBoolean(i++, active)
279 setIntOrNull(i++, lead?.id)
280 return i
281 }
282 }
284 override fun listComponents(project: Project): List<Component> =
285 withStatement("$componentQuery where project = ? order by ordinal, lower(name)") {
286 setInt(1, project.id)
287 queryAll { it.extractComponent() }
288 }
290 override fun listComponentSummaries(project: Project): List<ComponentSummary> =
291 withStatement(
292 """
293 with issues as (
294 select component, phase, count(issueid) as total
295 from lpit_issue
296 join lpit_issue_phases using (status)
297 group by component, phase
298 ),
299 summary as (
300 select c.id, phase, total
301 from lpit_component c
302 left join issues i on c.id = i.component
303 )
304 select c.id, project, name, node, color, ordinal, description, active,
305 userid, username, givenname, lastname, mail,
306 open.total as open, wip.total as wip, done.total as done
307 from lpit_component c
308 left join lpit_user on lead = userid
309 left join summary open on c.id = open.id and open.phase = 0
310 left join summary wip on c.id = wip.id and wip.phase = 1
311 left join summary done on c.id = done.id and done.phase = 2
312 where c.project = ?
313 order by ordinal, name
314 """.trimIndent()
315 ) {
316 setInt(1, project.id)
317 queryAll { rs ->
318 ComponentSummary(rs.extractComponent()).apply {
319 issueSummary.open = rs.getInt("open")
320 issueSummary.active = rs.getInt("wip")
321 issueSummary.done = rs.getInt("done")
322 }
323 }
324 }
326 override fun findComponent(id: Int): Component? =
327 withStatement("$componentQuery where id = ?") {
328 setInt(1, id)
329 querySingle { it.extractComponent() }
330 }
332 override fun findComponentByNode(project: Project, node: String): Component? =
333 withStatement("$componentQuery where project = ? and node = ?") {
334 setInt(1, project.id)
335 setString(2, node)
336 querySingle { it.extractComponent() }
337 }
339 override fun insertComponent(component: Component) {
340 withStatement("insert into lpit_component (name, node, color, ordinal, description, active, lead, project) values (?, ?, ?, ?, ?, ?, ?, ?)") {
341 val col = setComponent(1, component)
342 setInt(col, component.projectid)
343 executeUpdate()
344 }
345 }
347 override fun updateComponent(component: Component) {
348 withStatement("update lpit_component set name = ?, node = ?, color = ?, ordinal = ?, description = ?, active = ?, lead = ? where id = ?") {
349 val col = setComponent(1, component)
350 setInt(col, component.id)
351 executeUpdate()
352 }
353 }
355 //</editor-fold>
357 //<editor-fold desc="Project">
359 //language=SQL
360 private val projectQuery =
361 """
362 select projectid, name, node, ordinal, description, vcs, repourl,
363 userid, username, lastname, givenname, mail
364 from lpit_project
365 left join lpit_user owner on lpit_project.owner = owner.userid
366 """.trimIndent()
368 private fun ResultSet.extractProject() =
369 Project(getInt("projectid")).apply {
370 name = getString("name")
371 node = getString("node")
372 ordinal = getInt("ordinal")
373 description = getString("description")
374 vcs = getEnum("vcs")
375 repoUrl = getString("repourl")
376 owner = extractOptionalUser()
377 }
379 private fun PreparedStatement.setProject(index: Int, project: Project): Int {
380 var i = index
381 with(project) {
382 setStringSafe(i++, name)
383 setStringSafe(i++, node)
384 setInt(i++, ordinal)
385 setStringOrNull(i++, description)
386 setEnum(i++, vcs)
387 setStringOrNull(i++, repoUrl)
388 setIntOrNull(i++, owner?.id)
389 }
390 return i
391 }
393 override fun listProjects(): List<Project> =
394 withStatement("$projectQuery order by ordinal, lower(name)") {
395 queryAll { it.extractProject() }
396 }
398 override fun findProject(id: Int): Project? =
399 withStatement("$projectQuery where projectid = ?") {
400 setInt(1, id)
401 querySingle { it.extractProject() }
402 }
404 override fun findProjectByNode(node: String): Project? =
405 withStatement("$projectQuery where node = ?") {
406 setString(1, node)
407 querySingle { it.extractProject() }
408 }
410 override fun insertProject(project: Project) {
411 withStatement("insert into lpit_project (name, node, ordinal, description, vcs, repourl, owner) values (?, ?, ?, ?, ?::vcstype, ?, ?)") {
412 setProject(1, project)
413 executeUpdate()
414 }
415 }
417 override fun updateProject(project: Project) {
418 withStatement("update lpit_project set name = ?, node = ?, ordinal = ?, description = ?, vcs = ?::vcstype, repourl = ?, owner = ? where projectid = ?") {
419 val col = setProject(1, project)
420 setInt(col, project.id)
421 executeUpdate()
422 }
423 }
425 override fun collectIssueSummary(project: Project): IssueSummary =
426 withStatement(
427 """
428 select phase, count(*) as total
429 from lpit_issue
430 join lpit_issue_phases using(status)
431 where project = ?
432 group by phase
433 """.trimIndent()
434 ) {
435 setInt(1, project.id)
436 executeQuery().use {
437 val summary = IssueSummary()
438 while (it.next()) {
439 val phase = it.getInt("phase")
440 val total = it.getInt("total")
441 when (phase) {
442 0 -> summary.open = total
443 1 -> summary.active = total
444 2 -> summary.done = total
445 }
446 }
447 summary
448 }
449 }
451 override fun collectIssueSummary(assignee: User): IssueSummary =
452 withStatement(
453 """
454 select phase, count(*) as total
455 from lpit_issue
456 join lpit_issue_phases using(status)
457 where assignee = ?
458 group by phase
459 """.trimIndent()
460 ) {
461 setInt(1, assignee.id)
462 executeQuery().use {
463 val summary = IssueSummary()
464 while (it.next()) {
465 val phase = it.getInt("phase")
466 val total = it.getInt("total")
467 when (phase) {
468 0 -> summary.open = total
469 1 -> summary.active = total
470 2 -> summary.done = total
471 }
472 }
473 summary
474 }
475 }
477 override fun mergeCommitRefs(refs: List<CommitRef>) {
478 withStatement("insert into lpit_commit_ref (issueid, commit_hash, commit_brief) values (?,?,?) on conflict do nothing") {
479 refs.forEach { ref ->
480 setInt(1, ref.issueId)
481 setString(2, ref.hash)
482 setString(3, ref.message)
483 executeUpdate()
484 }
485 }
486 }
488 //</editor-fold>
490 //<editor-fold desc="Issue">
492 //language=SQL
493 private val issueQuery =
494 """
495 select issueid,
496 i.project, p.name as projectname, p.node as projectnode,
497 component, c.name as componentname, c.node as componentnode,
498 status, phase, category, subject, i.description,
499 userid, username, givenname, lastname, mail,
500 created, updated, eta, affected, resolved
501 from lpit_issue i
502 join lpit_project p on i.project = projectid
503 join lpit_issue_phases using (status)
504 left join lpit_component c on component = c.id
505 left join lpit_user on userid = assignee
506 """.trimIndent()
508 private fun ResultSet.extractIssue(): Issue {
509 val proj = Project(getInt("project")).apply {
510 name = getString("projectname")
511 node = getString("projectnode")
512 }
513 val comp = getInt("component").let {
514 if (wasNull()) null else
515 Component(it, proj.id).apply {
516 name = getString("componentname")
517 node = getString("componentnode")
518 }
519 }
520 val issue = Issue(getInt("issueid"), proj).apply {
521 component = comp
522 status = getEnum("status")
523 category = getEnum("category")
524 subject = getString("subject")
525 description = getString("description")
526 assignee = extractOptionalUser()
527 created = getTimestamp("created")
528 updated = getTimestamp("updated")
529 eta = getDate("eta")
530 affected = getInt("affected").takeIf { it > 0 }?.let { findVersion(it) }
531 resolved = getInt("resolved").takeIf { it > 0 }?.let { findVersion(it) }
532 }
534 return issue
535 }
537 private fun PreparedStatement.setIssue(index: Int, issue: Issue): Int {
538 var i = index
539 with(issue) {
540 setIntOrNull(i++, component?.id)
541 setEnum(i++, status)
542 setEnum(i++, category)
543 setStringSafe(i++, subject)
544 setStringOrNull(i++, description)
545 setIntOrNull(i++, assignee?.id)
546 setDateOrNull(i++, eta)
547 setIntOrNull(i++, affected?.id)
548 setIntOrNull(i++, resolved?.id)
549 }
550 return i
551 }
553 override fun listIssues(project: Project, includeDone: Boolean): List<Issue> =
554 withStatement("$issueQuery where i.project = ? and (? or phase < 2)") {
555 setInt(1, project.id)
556 setBoolean(2, includeDone)
557 queryAll { it.extractIssue() }
558 }
560 override fun listIssues(
561 project: Project,
562 includeDone: Boolean,
563 specificVersion: Boolean,
564 version: Version?,
565 specificComponent: Boolean,
566 component: Component?
567 ): List<Issue> =
568 withStatement(
569 """$issueQuery where i.project = ? and
570 (? or phase < 2) and
571 (not ? or ? in (resolved, affected)) and (not ? or (resolved is null and affected is null)) and
572 (not ? or component = ?) and (not ? or component is null)
573 """.trimIndent()
574 ) {
575 setInt(1, project.id)
576 setBoolean(2, includeDone)
578 setBoolean(3, specificVersion && version != null)
579 setInt(4, version?.id ?: 0)
580 setBoolean(5, specificVersion && version == null)
582 setBoolean(6, specificComponent && component != null)
583 setInt(7, component?.id ?: 0)
584 setBoolean(8, specificComponent && component == null)
586 queryAll { it.extractIssue() }
587 }
589 override fun findIssue(id: Int): Issue? =
590 withStatement("$issueQuery where issueid = ?") {
591 setInt(1, id)
592 querySingle { it.extractIssue() }
593 }
595 override fun insertIssue(issue: Issue): Int {
596 val id = withStatement(
597 """
598 insert into lpit_issue (component, status, category, subject, description, assignee, eta, affected, resolved, project)
599 values (?, ?::issue_status, ?::issue_category, ?, ?, ?, ?, ?, ?, ?)
600 returning issueid
601 """.trimIndent()
602 ) {
603 val col = setIssue(1, issue)
604 setInt(col, issue.project.id)
605 querySingle { it.getInt(1) }!!
606 }
607 return id
608 }
610 override fun updateIssue(issue: Issue) {
611 withStatement(
612 """
613 update lpit_issue set updated = now(),
614 component = ?, status = ?::issue_status, category = ?::issue_category, subject = ?,
615 description = ?, assignee = ?, eta = ?, affected = ?, resolved = ?
616 where issueid = ?
617 """.trimIndent()
618 ) {
619 val col = setIssue(1, issue)
620 setInt(col, issue.id)
621 executeUpdate()
622 }
623 }
625 override fun insertHistoryEvent(issue: Issue, newId: Int) {
626 val type = if (newId > 0) IssueHistoryType.New else IssueHistoryType.Update
627 val issueid = if (newId > 0) newId else issue.id
629 val eventid =
630 withStatement("insert into lpit_issue_history_event(issueid, subject, type) values (?,?,?::issue_history_event) returning eventid") {
631 setInt(1, issueid)
632 setString(2, issue.subject)
633 setEnum(3, type)
634 querySingle { it.getInt(1) }!!
635 }
636 withStatement(
637 """
638 insert into lpit_issue_history_data (component, status, category, description, assignee, eta, affected, resolved, eventid)
639 values (?, ?::issue_status, ?::issue_category, ?, ?, ?, ?, ?, ?)
640 """.trimIndent()
641 ) {
642 setStringOrNull(1, issue.component?.name)
643 setEnum(2, issue.status)
644 setEnum(3, issue.category)
645 setStringOrNull(4, issue.description)
646 setStringOrNull(5, issue.assignee?.shortDisplayname)
647 setDateOrNull(6, issue.eta)
648 setStringOrNull(7, issue.affected?.name)
649 setStringOrNull(8, issue.resolved?.name)
650 setInt(9, eventid)
651 executeUpdate()
652 }
653 }
655 override fun listCommitRefs(issue: Issue): List<CommitRef> =
656 withStatement("select commit_hash, commit_brief from lpit_commit_ref where issueid = ?") {
657 setInt(1, issue.id)
658 queryAll {
659 CommitRef(
660 issueId = issue.id,
661 hash = it.getString("commit_hash"),
662 message = it.getString("commit_brief")
663 )
664 }
665 }
667 //</editor-fold>
669 //<editor-fold desc="Issue Relations">
670 override fun insertIssueRelation(rel: IssueRelation) {
671 withStatement(
672 """
673 insert into lpit_issue_relation (from_issue, to_issue, type)
674 values (?, ?, ?::relation_type)
675 on conflict do nothing
676 """.trimIndent()
677 ) {
678 if (rel.reverse) {
679 setInt(2, rel.from.id)
680 setInt(1, rel.to.id)
681 } else {
682 setInt(1, rel.from.id)
683 setInt(2, rel.to.id)
684 }
685 setEnum(3, rel.type)
686 executeUpdate()
687 }
688 }
690 override fun deleteIssueRelation(rel: IssueRelation) {
691 withStatement("delete from lpit_issue_relation where from_issue = ? and to_issue = ? and type=?::relation_type") {
692 if (rel.reverse) {
693 setInt(2, rel.from.id)
694 setInt(1, rel.to.id)
695 } else {
696 setInt(1, rel.from.id)
697 setInt(2, rel.to.id)
698 }
699 setEnum(3, rel.type)
700 executeUpdate()
701 }
702 }
704 override fun listIssueRelations(issue: Issue): List<IssueRelation> = buildList {
705 withStatement("select to_issue, type from lpit_issue_relation where from_issue = ?") {
706 setInt(1, issue.id)
707 queryAll { IssueRelation(issue, findIssue(it.getInt("to_issue"))!!, it.getEnum("type"), false) }
708 }.forEach(this::add)
709 withStatement("select from_issue, type from lpit_issue_relation where to_issue = ?") {
710 setInt(1, issue.id)
711 queryAll { IssueRelation(issue, findIssue(it.getInt("from_issue"))!!, it.getEnum("type"), true) }
712 }.forEach(this::add)
713 }
715 override fun getIssueRelationMap(project: Project, includeDone: Boolean): IssueRelationMap =
716 withStatement(
717 """
718 select r.from_issue, r.to_issue, r.type
719 from lpit_issue_relation r
720 join lpit_issue i on i.issueid = r.from_issue
721 join lpit_issue_phases p on i.status = p.status
722 where i.project = ? and (? or p.phase < 2)
723 """.trimIndent()
724 ) {
725 setInt(1, project.id)
726 setBoolean(2, includeDone)
727 queryAll { Pair(it.getInt("from_issue"), Pair(it.getInt("to_issue"), it.getEnum<RelationType>("type"))) }
728 }.groupBy({it.first},{it.second})
729 //</editor-fold>
731 //<editor-fold desc="IssueComment">
733 private fun ResultSet.extractIssueComment() =
734 IssueComment(getInt("commentid"), getInt("issueid")).apply {
735 created = getTimestamp("created")
736 updated = getTimestamp("updated")
737 updateCount = getInt("updatecount")
738 comment = getString("comment")
739 author = extractOptionalUser()
740 }
742 override fun listComments(issue: Issue): List<IssueComment> =
743 withStatement("select * from lpit_issue_comment left join lpit_user using (userid) where issueid = ? order by created") {
744 setInt(1, issue.id)
745 queryAll { it.extractIssueComment() }
746 }
748 override fun findComment(id: Int): IssueComment? =
749 withStatement("select * from lpit_issue_comment left join lpit_user using (userid) where commentid = ?") {
750 setInt(1, id)
751 querySingle { it.extractIssueComment() }
752 }
754 override fun insertComment(issueComment: IssueComment): Int =
755 useStatement("update lpit_issue set updated = now() where issueid = ?") { updateIssueDate ->
756 withStatement("insert into lpit_issue_comment (issueid, comment, userid) values (?, ? ,?) returning commentid") {
757 with(issueComment) {
758 updateIssueDate.setInt(1, issueid)
759 setInt(1, issueid)
760 setStringSafe(2, comment)
761 setIntOrNull(3, author?.id)
762 }
763 val commentid = querySingle { it.getInt(1) }!!
764 updateIssueDate.executeUpdate()
765 commentid
766 }
767 }
769 override fun updateComment(issueComment: IssueComment) {
770 useStatement("update lpit_issue set updated = now() where issueid = ?") { updateIssueDate ->
771 withStatement("update lpit_issue_comment set comment = ?, updatecount = updatecount + 1, updated = now() where commentid = ?") {
772 with(issueComment) {
773 updateIssueDate.setInt(1, issueid)
774 setStringSafe(1, comment)
775 setInt(2, id)
776 }
777 executeUpdate()
778 updateIssueDate.executeUpdate()
779 }
780 }
781 }
784 override fun insertHistoryEvent(issue: Issue, issueComment: IssueComment, newId: Int) {
785 val type = if (newId > 0) IssueHistoryType.NewComment else IssueHistoryType.UpdateComment
786 val commentid = if (newId > 0) newId else issueComment.id
788 val eventid =
789 withStatement("insert into lpit_issue_history_event(issueid, subject, type) values (?,?,?::issue_history_event) returning eventid") {
790 setInt(1, issueComment.issueid)
791 setString(2, issue.subject)
792 setEnum(3, type)
793 querySingle { it.getInt(1) }!!
794 }
795 withStatement("insert into lpit_issue_comment_history (commentid, eventid, comment) values (?,?,?)") {
796 setInt(1, commentid)
797 setInt(2, eventid)
798 setString(3, issueComment.comment)
799 executeUpdate()
800 }
801 }
803 //</editor-fold>
805 //<editor-fold desc="Issue History">
807 override fun listIssueHistory(projectId: Int, days: Int) =
808 withStatement(
809 """
810 select u.username as current_assignee, evt.*, evtdata.*
811 from lpit_issue_history_event evt
812 join lpit_issue issue using (issueid)
813 left join lpit_user u on u.userid = issue.assignee
814 join lpit_issue_history_data evtdata using (eventid)
815 where project = ?
816 and time > now() - (? * interval '1' day)
817 order by time desc
818 """.trimIndent()
819 ) {
820 setInt(1, projectId)
821 setInt(2, days)
822 queryAll { rs->
823 with(rs) {
824 IssueHistoryEntry(
825 subject = getString("subject"),
826 time = getTimestamp("time"),
827 type = getEnum("type"),
828 currentAssignee = getString("current_assignee"),
829 issueid = getInt("issueid"),
830 component = getString("component") ?: "",
831 status = getEnum("status"),
832 category = getEnum("category"),
833 description = getString("description") ?: "",
834 assignee = getString("assignee") ?: "",
835 eta = getDate("eta"),
836 affected = getString("affected") ?: "",
837 resolved = getString("resolved") ?: ""
838 )
839 }
840 }
841 }
843 override fun listIssueCommentHistory(projectId: Int, days: Int) =
844 withStatement(
845 """
846 select u.username as current_assignee, evt.*, evtdata.*
847 from lpit_issue_history_event evt
848 join lpit_issue issue using (issueid)
849 left join lpit_user u on u.userid = issue.assignee
850 join lpit_issue_comment_history evtdata using (eventid)
851 where project = ?
852 and time > now() - (? * interval '1' day)
853 order by time desc
854 """.trimIndent()
855 ) {
856 setInt(1, projectId)
857 setInt(2, days)
858 queryAll { rs->
859 with(rs) {
860 IssueCommentHistoryEntry(
861 subject = getString("subject"),
862 time = getTimestamp("time"),
863 type = getEnum("type"),
864 currentAssignee = getString("current_assignee"),
865 issueid = getInt("issueid"),
866 commentid = getInt("commentid"),
867 comment = getString("comment")
868 )
869 }
870 }
871 }
873 //</editor-fold>
874 }
