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