src/main/kotlin/de/uapcore/lightpit/dao/PostgresDataAccessObject.kt@aa22103809cd
src/main/kotlin/de/uapcore/lightpit/dao/PostgresDataAccessObject.kt
Fri, 30 Dec 2022 19:04:34 +0100
- author
- Mike Becker <universe@uap-core.de>
- date
- Fri, 30 Dec 2022 19:04:34 +0100
- changeset 263
- aa22103809cd
- parent 260
- fb2ae2d63a56
- child 268
- ca5501d851fa
- permissions
- -rw-r--r--
#29 add possibility to relate issues
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.WebColor
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, release, eol 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 release = getDate("release")
144 eol = getDate("eol")
145 status = getEnum("status")
146 }
148 override fun listVersions(project: Project): List<Version> =
149 withStatement("$versionQuery where project = ? order by ordinal desc, lower(name) desc") {
150 setInt(1, project.id)
151 queryAll { it.extractVersion() }
152 }
154 override fun listVersionSummaries(project: Project): List<VersionSummary> =
155 withStatement(
156 """with
157 version_map as (
158 select issueid, status, resolved as versionid, true as isresolved from lpit_issue
159 union all
160 select issueid, status, affected as versionid, false as isresolved from lpit_issue
161 ), issues as (
162 select versionid, phase, isresolved, count(issueid) as total from version_map
163 join lpit_issue_phases using (status)
164 group by versionid, phase, isresolved
165 ),
166 summary as (
167 select versionid, phase, isresolved, total
168 from lpit_version v
169 left join issues using (versionid)
170 )
171 select v.versionid, project, name, node, ordinal, status, release, eol,
172 ro.total as resolved_open, ra.total as resolved_active, rd.total as resolved_done,
173 ao.total as affected_open, aa.total as affected_active, ad.total as affected_done
174 from lpit_version v
175 left join summary ro on ro.versionid = v.versionid and ro.phase = 0 and ro.isresolved
176 left join summary ra on ra.versionid = v.versionid and ra.phase = 1 and ra.isresolved
177 left join summary rd on rd.versionid = v.versionid and rd.phase = 2 and rd.isresolved
178 left join summary ao on ao.versionid = v.versionid and ao.phase = 0 and not ao.isresolved
179 left join summary aa on aa.versionid = v.versionid and aa.phase = 1 and not aa.isresolved
180 left join summary ad on ad.versionid = v.versionid and ad.phase = 2 and not ad.isresolved
181 where v.project = ?
182 order by ordinal desc, lower(name) desc
183 """.trimIndent()
184 ) {
185 setInt(1, project.id)
186 queryAll { rs ->
187 VersionSummary(rs.extractVersion()).apply {
188 reportedTotal.open = rs.getInt("affected_open")
189 reportedTotal.active = rs.getInt("affected_active")
190 reportedTotal.done = rs.getInt("affected_done")
191 resolvedTotal.open = rs.getInt("resolved_open")
192 resolvedTotal.active = rs.getInt("resolved_active")
193 resolvedTotal.done = rs.getInt("resolved_done")
194 }
195 }
196 }
198 override fun findVersion(id: Int): Version? =
199 withStatement("$versionQuery where versionid = ?") {
200 setInt(1, id)
201 querySingle { it.extractVersion() }
202 }
204 override fun findVersionByNode(project: Project, node: String): Version? =
205 withStatement("$versionQuery where project = ? and node = ?") {
206 setInt(1, project.id)
207 setString(2, node)
208 querySingle { it.extractVersion() }
209 }
211 override fun insertVersion(version: Version) {
212 withStatement("insert into lpit_version (name, node, ordinal, status, project, release, eol) values (?, ?, ?, ?::version_status, ?, ?, ?)") {
213 with(version) {
214 setStringSafe(1, name)
215 setStringSafe(2, node)
216 setInt(3, ordinal)
217 setEnum(4, status)
218 setInt(5, projectid)
219 setDateOrNull(6, release)
220 setDateOrNull(7, eol)
221 }
222 executeUpdate()
223 }
225 }
227 override fun updateVersion(version: Version) {
228 withStatement("update lpit_version set name = ?, node = ?, ordinal = ?, status = ?::version_status, release=?,eol=? where versionid = ?") {
229 with(version) {
230 setStringSafe(1, name)
231 setStringSafe(2, node)
232 setInt(3, ordinal)
233 setEnum(4, status)
234 setDateOrNull(5, version.release)
235 setDateOrNull(6, version.eol)
236 setInt(7, id)
237 }
238 executeUpdate()
239 }
240 }
241 //</editor-fold>
243 //<editor-fold desc="Component">
244 //language=SQL
245 private val componentQuery =
246 """
247 select id, project, name, node, color, ordinal, description, active,
248 userid, username, givenname, lastname, mail
249 from lpit_component
250 left join lpit_user on lead = userid
251 """.trimIndent()
253 private fun ResultSet.extractComponent(): Component =
254 Component(getInt("id"), getInt("project")).apply {
255 name = getString("name")
256 node = getString("node")
257 color = try {
258 WebColor(getString("color"))
259 } catch (ex: IllegalArgumentException) {
260 WebColor("000000")
261 }
262 ordinal = getInt("ordinal")
263 description = getString("description")
264 active = getBoolean("active")
265 lead = extractOptionalUser()
266 }
268 private fun PreparedStatement.setComponent(index: Int, component: Component): Int {
269 with(component) {
270 var i = index
271 setStringSafe(i++, name)
272 setStringSafe(i++, node)
273 setStringSafe(i++, color.hex)
274 setInt(i++, ordinal)
275 setStringOrNull(i++, description)
276 setBoolean(i++, active)
277 setIntOrNull(i++, lead?.id)
278 return i
279 }
280 }
282 override fun listComponents(project: Project): List<Component> =
283 withStatement("$componentQuery where project = ? order by ordinal, lower(name)") {
284 setInt(1, project.id)
285 queryAll { it.extractComponent() }
286 }
288 override fun listComponentSummaries(project: Project): List<ComponentSummary> =
289 withStatement(
290 """
291 with issues as (
292 select component, phase, count(issueid) as total
293 from lpit_issue
294 join lpit_issue_phases using (status)
295 group by component, phase
296 ),
297 summary as (
298 select c.id, phase, total
299 from lpit_component c
300 left join issues i on c.id = i.component
301 )
302 select c.id, project, name, node, color, ordinal, description, active,
303 userid, username, givenname, lastname, mail,
304 open.total as open, wip.total as wip, done.total as done
305 from lpit_component c
306 left join lpit_user on lead = userid
307 left join summary open on c.id = open.id and open.phase = 0
308 left join summary wip on c.id = wip.id and wip.phase = 1
309 left join summary done on c.id = done.id and done.phase = 2
310 where c.project = ?
311 order by ordinal, name
312 """.trimIndent()
313 ) {
314 setInt(1, project.id)
315 queryAll { rs ->
316 ComponentSummary(rs.extractComponent()).apply {
317 issueSummary.open = rs.getInt("open")
318 issueSummary.active = rs.getInt("wip")
319 issueSummary.done = rs.getInt("done")
320 }
321 }
322 }
324 override fun findComponent(id: Int): Component? =
325 withStatement("$componentQuery where id = ?") {
326 setInt(1, id)
327 querySingle { it.extractComponent() }
328 }
330 override fun findComponentByNode(project: Project, node: String): Component? =
331 withStatement("$componentQuery where project = ? and node = ?") {
332 setInt(1, project.id)
333 setString(2, node)
334 querySingle { it.extractComponent() }
335 }
337 override fun insertComponent(component: Component) {
338 withStatement("insert into lpit_component (name, node, color, ordinal, description, active, lead, project) values (?, ?, ?, ?, ?, ?, ?, ?)") {
339 val col = setComponent(1, component)
340 setInt(col, component.projectid)
341 executeUpdate()
342 }
343 }
345 override fun updateComponent(component: Component) {
346 withStatement("update lpit_component set name = ?, node = ?, color = ?, ordinal = ?, description = ?, active = ?, lead = ? where id = ?") {
347 val col = setComponent(1, component)
348 setInt(col, component.id)
349 executeUpdate()
350 }
351 }
353 //</editor-fold>
355 //<editor-fold desc="Project">
357 //language=SQL
358 private val projectQuery =
359 """
360 select projectid, name, node, ordinal, description, repourl,
361 userid, username, lastname, givenname, mail
362 from lpit_project
363 left join lpit_user owner on lpit_project.owner = owner.userid
364 """.trimIndent()
366 private fun ResultSet.extractProject() =
367 Project(getInt("projectid")).apply {
368 name = getString("name")
369 node = getString("node")
370 ordinal = getInt("ordinal")
371 description = getString("description")
372 repoUrl = getString("repourl")
373 owner = extractOptionalUser()
374 }
376 private fun PreparedStatement.setProject(index: Int, project: Project): Int {
377 var i = index
378 with(project) {
379 setStringSafe(i++, name)
380 setStringSafe(i++, node)
381 setInt(i++, ordinal)
382 setStringOrNull(i++, description)
383 setStringOrNull(i++, repoUrl)
384 setIntOrNull(i++, owner?.id)
385 }
386 return i
387 }
389 override fun listProjects(): List<Project> =
390 withStatement("$projectQuery order by ordinal, lower(name)") {
391 queryAll { it.extractProject() }
392 }
394 override fun findProject(id: Int): Project? =
395 withStatement("$projectQuery where projectid = ?") {
396 setInt(1, id)
397 querySingle { it.extractProject() }
398 }
400 override fun findProjectByNode(node: String): Project? =
401 withStatement("$projectQuery where node = ?") {
402 setString(1, node)
403 querySingle { it.extractProject() }
404 }
406 override fun insertProject(project: Project) {
407 withStatement("insert into lpit_project (name, node, ordinal, description, repourl, owner) values (?, ?, ?, ?, ?, ?)") {
408 setProject(1, project)
409 executeUpdate()
410 }
411 }
413 override fun updateProject(project: Project) {
414 withStatement("update lpit_project set name = ?, node = ?, ordinal = ?, description = ?, repourl = ?, owner = ? where projectid = ?") {
415 val col = setProject(1, project)
416 setInt(col, project.id)
417 executeUpdate()
418 }
419 }
421 override fun collectIssueSummary(project: Project): IssueSummary =
422 withStatement(
423 """
424 select phase, count(*) as total
425 from lpit_issue
426 join lpit_issue_phases using(status)
427 where project = ?
428 group by phase
429 """.trimIndent()
430 ) {
431 setInt(1, project.id)
432 executeQuery().use {
433 val summary = IssueSummary()
434 while (it.next()) {
435 val phase = it.getInt("phase")
436 val total = it.getInt("total")
437 when (phase) {
438 0 -> summary.open = total
439 1 -> summary.active = total
440 2 -> summary.done = total
441 }
442 }
443 summary
444 }
445 }
447 override fun collectIssueSummary(assignee: User): IssueSummary =
448 withStatement(
449 """
450 select phase, count(*) as total
451 from lpit_issue
452 join lpit_issue_phases using(status)
453 where assignee = ?
454 group by phase
455 """.trimIndent()
456 ) {
457 setInt(1, assignee.id)
458 executeQuery().use {
459 val summary = IssueSummary()
460 while (it.next()) {
461 val phase = it.getInt("phase")
462 val total = it.getInt("total")
463 when (phase) {
464 0 -> summary.open = total
465 1 -> summary.active = total
466 2 -> summary.done = total
467 }
468 }
469 summary
470 }
471 }
473 //</editor-fold>
475 //<editor-fold desc="Issue">
477 //language=SQL
478 private val issueQuery =
479 """
480 select issueid,
481 i.project, p.name as projectname, p.node as projectnode,
482 component, c.name as componentname, c.node as componentnode,
483 status, category, subject, i.description,
484 userid, username, givenname, lastname, mail,
485 created, updated, eta, affected, resolved
486 from lpit_issue i
487 join lpit_project p on i.project = projectid
488 left join lpit_component c on component = c.id
489 left join lpit_user on userid = assignee
490 """.trimIndent()
492 private fun ResultSet.extractIssue(): Issue {
493 val proj = Project(getInt("project")).apply {
494 name = getString("projectname")
495 node = getString("projectnode")
496 }
497 val comp = getInt("component").let {
498 if (wasNull()) null else
499 Component(it, proj.id).apply {
500 name = getString("componentname")
501 node = getString("componentnode")
502 }
503 }
504 val issue = Issue(getInt("issueid"), proj).apply {
505 component = comp
506 status = getEnum("status")
507 category = getEnum("category")
508 subject = getString("subject")
509 description = getString("description")
510 assignee = extractOptionalUser()
511 created = getTimestamp("created")
512 updated = getTimestamp("updated")
513 eta = getDate("eta")
514 affected = getInt("affected").takeIf { it > 0 }?.let { findVersion(it) }
515 resolved = getInt("resolved").takeIf { it > 0 }?.let { findVersion(it) }
516 }
518 return issue
519 }
521 private fun PreparedStatement.setIssue(index: Int, issue: Issue): Int {
522 var i = index
523 with(issue) {
524 setIntOrNull(i++, component?.id)
525 setEnum(i++, status)
526 setEnum(i++, category)
527 setStringSafe(i++, subject)
528 setStringOrNull(i++, description)
529 setIntOrNull(i++, assignee?.id)
530 setDateOrNull(i++, eta)
531 setIntOrNull(i++, affected?.id)
532 setIntOrNull(i++, resolved?.id)
533 }
534 return i
535 }
537 override fun listIssues(project: Project): List<Issue> =
538 withStatement("$issueQuery where i.project = ?") {
539 setInt(1, project.id)
540 queryAll { it.extractIssue() }
541 }
543 override fun listIssues(project: Project, version: Version?, component: Component?): List<Issue> =
544 withStatement(
545 """$issueQuery where i.project = ? and
546 (not ? or ? in (resolved, affected)) and (not ? or (resolved is null and affected is null)) and
547 (not ? or component = ?) and (not ? or component is null)
548 """.trimIndent()
549 ) {
550 fun <T : Entity> applyFilter(search: T?, fflag: Int, nflag: Int, idcol: Int) {
551 if (search == null) {
552 setBoolean(fflag, false)
553 setBoolean(nflag, false)
554 setInt(idcol, 0)
555 } else {
556 setBoolean(fflag, true)
557 setBoolean(nflag, false)
558 setInt(idcol, search.id)
559 }
560 }
561 setInt(1, project.id)
562 applyFilter(version, 2, 4, 3)
563 applyFilter(component, 5, 7, 6)
565 queryAll { it.extractIssue() }
566 }
568 override fun findIssue(id: Int): Issue? =
569 withStatement("$issueQuery where issueid = ?") {
570 setInt(1, id)
571 querySingle { it.extractIssue() }
572 }
574 override fun insertIssue(issue: Issue): Int {
575 val id = withStatement(
576 """
577 insert into lpit_issue (component, status, category, subject, description, assignee, eta, affected, resolved, project)
578 values (?, ?::issue_status, ?::issue_category, ?, ?, ?, ?, ?, ?, ?)
579 returning issueid
580 """.trimIndent()
581 ) {
582 val col = setIssue(1, issue)
583 setInt(col, issue.project.id)
584 querySingle { it.getInt(1) }!!
585 }
586 return id
587 }
589 override fun updateIssue(issue: Issue) {
590 withStatement(
591 """
592 update lpit_issue set updated = now(),
593 component = ?, status = ?::issue_status, category = ?::issue_category, subject = ?,
594 description = ?, assignee = ?, eta = ?, affected = ?, resolved = ?
595 where issueid = ?
596 """.trimIndent()
597 ) {
598 val col = setIssue(1, issue)
599 setInt(col, issue.id)
600 executeUpdate()
601 }
602 }
604 override fun insertHistoryEvent(issue: Issue, newId: Int) {
605 val type = if (newId > 0) IssueHistoryType.New else IssueHistoryType.Update
606 val issueid = if (newId > 0) newId else issue.id
608 val eventid =
609 withStatement("insert into lpit_issue_history_event(issueid, subject, type) values (?,?,?::issue_history_event) returning eventid") {
610 setInt(1, issueid)
611 setString(2, issue.subject)
612 setEnum(3, type)
613 querySingle { it.getInt(1) }!!
614 }
615 withStatement(
616 """
617 insert into lpit_issue_history_data (component, status, category, description, assignee, eta, affected, resolved, eventid)
618 values (?, ?::issue_status, ?::issue_category, ?, ?, ?, ?, ?, ?)
619 """.trimIndent()
620 ) {
621 setStringOrNull(1, issue.component?.name)
622 setEnum(2, issue.status)
623 setEnum(3, issue.category)
624 setStringOrNull(4, issue.description)
625 setStringOrNull(5, issue.assignee?.shortDisplayname)
626 setDateOrNull(6, issue.eta)
627 setStringOrNull(7, issue.affected?.name)
628 setStringOrNull(8, issue.resolved?.name)
629 setInt(9, eventid)
630 executeUpdate()
631 }
632 }
634 //</editor-fold>
636 //<editor-fold desc="Issue Relations">
637 override fun insertIssueRelation(rel: IssueRelation) {
638 withStatement(
639 """
640 insert into lpit_issue_relation (from_issue, to_issue, type)
641 values (?, ?, ?::relation_type)
642 on conflict do nothing
643 """.trimIndent()
644 ) {
645 if (rel.reverse) {
646 setInt(2, rel.from.id)
647 setInt(1, rel.to.id)
648 } else {
649 setInt(1, rel.from.id)
650 setInt(2, rel.to.id)
651 }
652 setEnum(3, rel.type)
653 executeUpdate()
654 }
655 }
657 override fun deleteIssueRelation(rel: IssueRelation) {
658 withStatement("delete from lpit_issue_relation where from_issue = ? and to_issue = ? and type=?::relation_type") {
659 if (rel.reverse) {
660 setInt(2, rel.from.id)
661 setInt(1, rel.to.id)
662 } else {
663 setInt(1, rel.from.id)
664 setInt(2, rel.to.id)
665 }
666 setEnum(3, rel.type)
667 executeUpdate()
668 }
669 }
671 override fun listIssueRelations(issue: Issue): List<IssueRelation> = buildList {
672 withStatement("select to_issue, type from lpit_issue_relation where from_issue = ?") {
673 setInt(1, issue.id)
674 queryAll { IssueRelation(issue, findIssue(it.getInt("to_issue"))!!, it.getEnum("type"), false) }
675 }.forEach(this::add)
676 withStatement("select from_issue, type from lpit_issue_relation where to_issue = ?") {
677 setInt(1, issue.id)
678 queryAll { IssueRelation(issue, findIssue(it.getInt("from_issue"))!!, it.getEnum("type"), true) }
679 }.forEach(this::add)
680 }
681 //</editor-fold>
683 //<editor-fold desc="IssueComment">
685 private fun ResultSet.extractIssueComment() =
686 IssueComment(getInt("commentid"), getInt("issueid")).apply {
687 created = getTimestamp("created")
688 updated = getTimestamp("updated")
689 updateCount = getInt("updatecount")
690 comment = getString("comment")
691 author = extractOptionalUser()
692 }
694 override fun listComments(issue: Issue): List<IssueComment> =
695 withStatement("select * from lpit_issue_comment left join lpit_user using (userid) where issueid = ? order by created") {
696 setInt(1, issue.id)
697 queryAll { it.extractIssueComment() }
698 }
700 override fun findComment(id: Int): IssueComment? =
701 withStatement("select * from lpit_issue_comment left join lpit_user using (userid) where commentid = ?") {
702 setInt(1, id)
703 querySingle { it.extractIssueComment() }
704 }
706 override fun insertComment(issueComment: IssueComment): Int =
707 useStatement("update lpit_issue set updated = now() where issueid = ?") { updateIssueDate ->
708 withStatement("insert into lpit_issue_comment (issueid, comment, userid) values (?, ? ,?) returning commentid") {
709 with(issueComment) {
710 updateIssueDate.setInt(1, issueid)
711 setInt(1, issueid)
712 setStringSafe(2, comment)
713 setIntOrNull(3, author?.id)
714 }
715 val commentid = querySingle { it.getInt(1) }!!
716 updateIssueDate.executeUpdate()
717 commentid
718 }
719 }
721 override fun updateComment(issueComment: IssueComment) {
722 useStatement("update lpit_issue set updated = now() where issueid = ?") { updateIssueDate ->
723 withStatement("update lpit_issue_comment set comment = ?, updatecount = updatecount + 1, updated = now() where commentid = ?") {
724 with(issueComment) {
725 updateIssueDate.setInt(1, issueid)
726 setStringSafe(1, comment)
727 setInt(2, id)
728 }
729 executeUpdate()
730 updateIssueDate.executeUpdate()
731 }
732 }
733 }
736 override fun insertHistoryEvent(issue: Issue, issueComment: IssueComment, newId: Int) {
737 val type = if (newId > 0) IssueHistoryType.NewComment else IssueHistoryType.UpdateComment
738 val commentid = if (newId > 0) newId else issueComment.id
740 val eventid =
741 withStatement("insert into lpit_issue_history_event(issueid, subject, type) values (?,?,?::issue_history_event) returning eventid") {
742 setInt(1, issueComment.issueid)
743 setString(2, issue.subject)
744 setEnum(3, type)
745 querySingle { it.getInt(1) }!!
746 }
747 withStatement("insert into lpit_issue_comment_history (commentid, eventid, comment) values (?,?,?)") {
748 setInt(1, commentid)
749 setInt(2, eventid)
750 setString(3, issueComment.comment)
751 executeUpdate()
752 }
753 }
755 //</editor-fold>
757 //<editor-fold desc="Issue History">
759 override fun listIssueHistory(projectId: Int, days: Int) =
760 withStatement(
761 """
762 select u.username as current_assignee, evt.*, evtdata.*
763 from lpit_issue_history_event evt
764 join lpit_issue issue using (issueid)
765 left join lpit_user u on u.userid = issue.assignee
766 join lpit_issue_history_data evtdata using (eventid)
767 where project = ?
768 and time > now() - (? * interval '1' day)
769 order by time desc
770 """.trimIndent()
771 ) {
772 setInt(1, projectId)
773 setInt(2, days)
774 queryAll { rs->
775 with(rs) {
776 IssueHistoryEntry(
777 subject = getString("subject"),
778 time = getTimestamp("time"),
779 type = getEnum("type"),
780 currentAssignee = getString("current_assignee"),
781 issueid = getInt("issueid"),
782 component = getString("component") ?: "",
783 status = getEnum("status"),
784 category = getEnum("category"),
785 description = getString("description") ?: "",
786 assignee = getString("assignee") ?: "",
787 eta = getDate("eta"),
788 affected = getString("affected") ?: "",
789 resolved = getString("resolved") ?: ""
790 )
791 }
792 }
793 }
795 override fun listIssueCommentHistory(projectId: Int, days: Int) =
796 withStatement(
797 """
798 select u.username as current_assignee, evt.*, evtdata.*
799 from lpit_issue_history_event evt
800 join lpit_issue issue using (issueid)
801 left join lpit_user u on u.userid = issue.assignee
802 join lpit_issue_comment_history evtdata using (eventid)
803 where project = ?
804 and time > now() - (? * interval '1' day)
805 order by time desc
806 """.trimIndent()
807 ) {
808 setInt(1, projectId)
809 setInt(2, days)
810 queryAll { rs->
811 with(rs) {
812 IssueCommentHistoryEntry(
813 subject = getString("subject"),
814 time = getTimestamp("time"),
815 type = getEnum("type"),
816 currentAssignee = getString("current_assignee"),
817 issueid = getInt("issueid"),
818 commentid = getInt("commentid"),
819 comment = getString("comment")
820 )
821 }
822 }
823 }
825 //</editor-fold>
826 }
