src/main/kotlin/de/uapcore/lightpit/dao/PostgresDataAccessObject.kt@671c1c8fbf1c
src/main/kotlin/de/uapcore/lightpit/dao/PostgresDataAccessObject.kt
Sat, 22 Jul 2023 22:32:04 +0200
- author
- Mike Becker <universe@uap-core.de>
- date
- Sat, 22 Jul 2023 22:32:04 +0200
- changeset 284
- 671c1c8fbf1c
- parent 268
- ca5501d851fa
- child 292
- 703591e739f4
- permissions
- -rw-r--r--
add full support for commit references - fixes #276
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(project: Project, includeDone: Boolean, version: Version?, component: Component?): List<Issue> =
561 withStatement(
562 """$issueQuery where i.project = ? and
563 (? or phase < 2) and
564 (not ? or ? in (resolved, affected)) and (not ? or (resolved is null and affected is null)) and
565 (not ? or component = ?) and (not ? or component is null)
566 """.trimIndent()
567 ) {
568 fun <T : Entity> applyFilter(search: T?, fflag: Int, nflag: Int, idcol: Int) {
569 if (search == null) {
570 setBoolean(fflag, false)
571 setBoolean(nflag, false)
572 setInt(idcol, 0)
573 } else {
574 setBoolean(fflag, true)
575 setBoolean(nflag, false)
576 setInt(idcol, search.id)
577 }
578 }
579 setInt(1, project.id)
580 setBoolean(2, includeDone)
581 applyFilter(version, 3, 5, 4)
582 applyFilter(component, 6, 8, 7)
584 queryAll { it.extractIssue() }
585 }
587 override fun findIssue(id: Int): Issue? =
588 withStatement("$issueQuery where issueid = ?") {
589 setInt(1, id)
590 querySingle { it.extractIssue() }
591 }
593 override fun insertIssue(issue: Issue): Int {
594 val id = withStatement(
595 """
596 insert into lpit_issue (component, status, category, subject, description, assignee, eta, affected, resolved, project)
597 values (?, ?::issue_status, ?::issue_category, ?, ?, ?, ?, ?, ?, ?)
598 returning issueid
599 """.trimIndent()
600 ) {
601 val col = setIssue(1, issue)
602 setInt(col, issue.project.id)
603 querySingle { it.getInt(1) }!!
604 }
605 return id
606 }
608 override fun updateIssue(issue: Issue) {
609 withStatement(
610 """
611 update lpit_issue set updated = now(),
612 component = ?, status = ?::issue_status, category = ?::issue_category, subject = ?,
613 description = ?, assignee = ?, eta = ?, affected = ?, resolved = ?
614 where issueid = ?
615 """.trimIndent()
616 ) {
617 val col = setIssue(1, issue)
618 setInt(col, issue.id)
619 executeUpdate()
620 }
621 }
623 override fun insertHistoryEvent(issue: Issue, newId: Int) {
624 val type = if (newId > 0) IssueHistoryType.New else IssueHistoryType.Update
625 val issueid = if (newId > 0) newId else issue.id
627 val eventid =
628 withStatement("insert into lpit_issue_history_event(issueid, subject, type) values (?,?,?::issue_history_event) returning eventid") {
629 setInt(1, issueid)
630 setString(2, issue.subject)
631 setEnum(3, type)
632 querySingle { it.getInt(1) }!!
633 }
634 withStatement(
635 """
636 insert into lpit_issue_history_data (component, status, category, description, assignee, eta, affected, resolved, eventid)
637 values (?, ?::issue_status, ?::issue_category, ?, ?, ?, ?, ?, ?)
638 """.trimIndent()
639 ) {
640 setStringOrNull(1, issue.component?.name)
641 setEnum(2, issue.status)
642 setEnum(3, issue.category)
643 setStringOrNull(4, issue.description)
644 setStringOrNull(5, issue.assignee?.shortDisplayname)
645 setDateOrNull(6, issue.eta)
646 setStringOrNull(7, issue.affected?.name)
647 setStringOrNull(8, issue.resolved?.name)
648 setInt(9, eventid)
649 executeUpdate()
650 }
651 }
653 override fun listCommitRefs(issue: Issue): List<CommitRef> =
654 withStatement("select commit_hash, commit_brief from lpit_commit_ref where issueid = ?") {
655 setInt(1, issue.id)
656 queryAll {
657 CommitRef(
658 issueId = issue.id,
659 hash = it.getString("commit_hash"),
660 message = it.getString("commit_brief")
661 )
662 }
663 }
665 //</editor-fold>
667 //<editor-fold desc="Issue Relations">
668 override fun insertIssueRelation(rel: IssueRelation) {
669 withStatement(
670 """
671 insert into lpit_issue_relation (from_issue, to_issue, type)
672 values (?, ?, ?::relation_type)
673 on conflict do nothing
674 """.trimIndent()
675 ) {
676 if (rel.reverse) {
677 setInt(2, rel.from.id)
678 setInt(1, rel.to.id)
679 } else {
680 setInt(1, rel.from.id)
681 setInt(2, rel.to.id)
682 }
683 setEnum(3, rel.type)
684 executeUpdate()
685 }
686 }
688 override fun deleteIssueRelation(rel: IssueRelation) {
689 withStatement("delete from lpit_issue_relation where from_issue = ? and to_issue = ? and type=?::relation_type") {
690 if (rel.reverse) {
691 setInt(2, rel.from.id)
692 setInt(1, rel.to.id)
693 } else {
694 setInt(1, rel.from.id)
695 setInt(2, rel.to.id)
696 }
697 setEnum(3, rel.type)
698 executeUpdate()
699 }
700 }
702 override fun listIssueRelations(issue: Issue): List<IssueRelation> = buildList {
703 withStatement("select to_issue, type from lpit_issue_relation where from_issue = ?") {
704 setInt(1, issue.id)
705 queryAll { IssueRelation(issue, findIssue(it.getInt("to_issue"))!!, it.getEnum("type"), false) }
706 }.forEach(this::add)
707 withStatement("select from_issue, type from lpit_issue_relation where to_issue = ?") {
708 setInt(1, issue.id)
709 queryAll { IssueRelation(issue, findIssue(it.getInt("from_issue"))!!, it.getEnum("type"), true) }
710 }.forEach(this::add)
711 }
713 override fun getIssueRelationMap(project: Project, includeDone: Boolean): IssueRelationMap =
714 withStatement(
715 """
716 select r.from_issue, r.to_issue, r.type
717 from lpit_issue_relation r
718 join lpit_issue i on i.issueid = r.from_issue
719 join lpit_issue_phases p on i.status = p.status
720 where i.project = ? and (? or p.phase < 2)
721 """.trimIndent()
722 ) {
723 setInt(1, project.id)
724 setBoolean(2, includeDone)
725 queryAll { Pair(it.getInt("from_issue"), Pair(it.getInt("to_issue"), it.getEnum<RelationType>("type"))) }
726 }.groupBy({it.first},{it.second})
727 //</editor-fold>
729 //<editor-fold desc="IssueComment">
731 private fun ResultSet.extractIssueComment() =
732 IssueComment(getInt("commentid"), getInt("issueid")).apply {
733 created = getTimestamp("created")
734 updated = getTimestamp("updated")
735 updateCount = getInt("updatecount")
736 comment = getString("comment")
737 author = extractOptionalUser()
738 }
740 override fun listComments(issue: Issue): List<IssueComment> =
741 withStatement("select * from lpit_issue_comment left join lpit_user using (userid) where issueid = ? order by created") {
742 setInt(1, issue.id)
743 queryAll { it.extractIssueComment() }
744 }
746 override fun findComment(id: Int): IssueComment? =
747 withStatement("select * from lpit_issue_comment left join lpit_user using (userid) where commentid = ?") {
748 setInt(1, id)
749 querySingle { it.extractIssueComment() }
750 }
752 override fun insertComment(issueComment: IssueComment): Int =
753 useStatement("update lpit_issue set updated = now() where issueid = ?") { updateIssueDate ->
754 withStatement("insert into lpit_issue_comment (issueid, comment, userid) values (?, ? ,?) returning commentid") {
755 with(issueComment) {
756 updateIssueDate.setInt(1, issueid)
757 setInt(1, issueid)
758 setStringSafe(2, comment)
759 setIntOrNull(3, author?.id)
760 }
761 val commentid = querySingle { it.getInt(1) }!!
762 updateIssueDate.executeUpdate()
763 commentid
764 }
765 }
767 override fun updateComment(issueComment: IssueComment) {
768 useStatement("update lpit_issue set updated = now() where issueid = ?") { updateIssueDate ->
769 withStatement("update lpit_issue_comment set comment = ?, updatecount = updatecount + 1, updated = now() where commentid = ?") {
770 with(issueComment) {
771 updateIssueDate.setInt(1, issueid)
772 setStringSafe(1, comment)
773 setInt(2, id)
774 }
775 executeUpdate()
776 updateIssueDate.executeUpdate()
777 }
778 }
779 }
782 override fun insertHistoryEvent(issue: Issue, issueComment: IssueComment, newId: Int) {
783 val type = if (newId > 0) IssueHistoryType.NewComment else IssueHistoryType.UpdateComment
784 val commentid = if (newId > 0) newId else issueComment.id
786 val eventid =
787 withStatement("insert into lpit_issue_history_event(issueid, subject, type) values (?,?,?::issue_history_event) returning eventid") {
788 setInt(1, issueComment.issueid)
789 setString(2, issue.subject)
790 setEnum(3, type)
791 querySingle { it.getInt(1) }!!
792 }
793 withStatement("insert into lpit_issue_comment_history (commentid, eventid, comment) values (?,?,?)") {
794 setInt(1, commentid)
795 setInt(2, eventid)
796 setString(3, issueComment.comment)
797 executeUpdate()
798 }
799 }
801 //</editor-fold>
803 //<editor-fold desc="Issue History">
805 override fun listIssueHistory(projectId: Int, days: Int) =
806 withStatement(
807 """
808 select u.username as current_assignee, evt.*, evtdata.*
809 from lpit_issue_history_event evt
810 join lpit_issue issue using (issueid)
811 left join lpit_user u on u.userid = issue.assignee
812 join lpit_issue_history_data evtdata using (eventid)
813 where project = ?
814 and time > now() - (? * interval '1' day)
815 order by time desc
816 """.trimIndent()
817 ) {
818 setInt(1, projectId)
819 setInt(2, days)
820 queryAll { rs->
821 with(rs) {
822 IssueHistoryEntry(
823 subject = getString("subject"),
824 time = getTimestamp("time"),
825 type = getEnum("type"),
826 currentAssignee = getString("current_assignee"),
827 issueid = getInt("issueid"),
828 component = getString("component") ?: "",
829 status = getEnum("status"),
830 category = getEnum("category"),
831 description = getString("description") ?: "",
832 assignee = getString("assignee") ?: "",
833 eta = getDate("eta"),
834 affected = getString("affected") ?: "",
835 resolved = getString("resolved") ?: ""
836 )
837 }
838 }
839 }
841 override fun listIssueCommentHistory(projectId: Int, days: Int) =
842 withStatement(
843 """
844 select u.username as current_assignee, evt.*, evtdata.*
845 from lpit_issue_history_event evt
846 join lpit_issue issue using (issueid)
847 left join lpit_user u on u.userid = issue.assignee
848 join lpit_issue_comment_history evtdata using (eventid)
849 where project = ?
850 and time > now() - (? * interval '1' day)
851 order by time desc
852 """.trimIndent()
853 ) {
854 setInt(1, projectId)
855 setInt(2, days)
856 queryAll { rs->
857 with(rs) {
858 IssueCommentHistoryEntry(
859 subject = getString("subject"),
860 time = getTimestamp("time"),
861 type = getEnum("type"),
862 currentAssignee = getString("current_assignee"),
863 issueid = getInt("issueid"),
864 commentid = getInt("commentid"),
865 comment = getString("comment")
866 )
867 }
868 }
869 }
871 //</editor-fold>
872 }
