src/main/kotlin/de/uapcore/lightpit/dao/PostgresDataAccessObject.kt@fb2ae2d63a56
src/main/kotlin/de/uapcore/lightpit/dao/PostgresDataAccessObject.kt
Thu, 29 Dec 2022 15:04:21 +0100
- author
- Mike Becker <universe@uap-core.de>
- date
- Thu, 29 Dec 2022 15:04:21 +0100
- changeset 260
- fb2ae2d63a56
- parent 257
- c1be672af7ff
- child 263
- aa22103809cd
- permissions
- -rw-r--r--
some minor style fixes
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, version: Version?, component: Component?): List<Issue> =
538 withStatement(
539 """$issueQuery where
540 (not ? or i.project = ?) and
541 (not ? or ? in (resolved, affected)) and (not ? or (resolved is null and affected is null)) and
542 (not ? or component = ?) and (not ? or component is null)
543 """.trimIndent()
544 ) {
545 fun <T : Entity> applyFilter(search: T?, fflag: Int, nflag: Int, idcol: Int) {
546 if (search == null) {
547 setBoolean(fflag, false)
548 setBoolean(nflag, false)
549 setInt(idcol, 0)
550 } else {
551 setBoolean(fflag, true)
552 setBoolean(nflag, false)
553 setInt(idcol, search.id)
554 }
555 }
556 setBoolean(1, true)
557 setInt(2, project.id)
558 applyFilter(version, 3, 5, 4)
559 applyFilter(component, 6, 8, 7)
561 queryAll { it.extractIssue() }
562 }
564 override fun findIssue(id: Int): Issue? =
565 withStatement("$issueQuery where issueid = ?") {
566 setInt(1, id)
567 querySingle { it.extractIssue() }
568 }
570 override fun insertIssue(issue: Issue): Int {
571 val id = withStatement(
572 """
573 insert into lpit_issue (component, status, category, subject, description, assignee, eta, affected, resolved, project)
574 values (?, ?::issue_status, ?::issue_category, ?, ?, ?, ?, ?, ?, ?)
575 returning issueid
576 """.trimIndent()
577 ) {
578 val col = setIssue(1, issue)
579 setInt(col, issue.project.id)
580 querySingle { it.getInt(1) }!!
581 }
582 return id
583 }
585 override fun updateIssue(issue: Issue) {
586 withStatement(
587 """
588 update lpit_issue set updated = now(),
589 component = ?, status = ?::issue_status, category = ?::issue_category, subject = ?,
590 description = ?, assignee = ?, eta = ?, affected = ?, resolved = ?
591 where issueid = ?
592 """.trimIndent()
593 ) {
594 val col = setIssue(1, issue)
595 setInt(col, issue.id)
596 executeUpdate()
597 }
598 }
600 override fun insertHistoryEvent(issue: Issue, newId: Int) {
601 val type = if (newId > 0) IssueHistoryType.New else IssueHistoryType.Update
602 val issueid = if (newId > 0) newId else issue.id
604 val eventid =
605 withStatement("insert into lpit_issue_history_event(issueid, subject, type) values (?,?,?::issue_history_event) returning eventid") {
606 setInt(1, issueid)
607 setString(2, issue.subject)
608 setEnum(3, type)
609 querySingle { it.getInt(1) }!!
610 }
611 withStatement(
612 """
613 insert into lpit_issue_history_data (component, status, category, description, assignee, eta, affected, resolved, eventid)
614 values (?, ?::issue_status, ?::issue_category, ?, ?, ?, ?, ?, ?)
615 """.trimIndent()
616 ) {
617 setStringOrNull(1, issue.component?.name)
618 setEnum(2, issue.status)
619 setEnum(3, issue.category)
620 setStringOrNull(4, issue.description)
621 setStringOrNull(5, issue.assignee?.shortDisplayname)
622 setDateOrNull(6, issue.eta)
623 setStringOrNull(7, issue.affected?.name)
624 setStringOrNull(8, issue.resolved?.name)
625 setInt(9, eventid)
626 executeUpdate()
627 }
628 }
630 //</editor-fold>
632 //<editor-fold desc="IssueComment">
634 private fun ResultSet.extractIssueComment() =
635 IssueComment(getInt("commentid"), getInt("issueid")).apply {
636 created = getTimestamp("created")
637 updated = getTimestamp("updated")
638 updateCount = getInt("updatecount")
639 comment = getString("comment")
640 author = extractOptionalUser()
641 }
643 override fun listComments(issue: Issue): List<IssueComment> =
644 withStatement("select * from lpit_issue_comment left join lpit_user using (userid) where issueid = ? order by created") {
645 setInt(1, issue.id)
646 queryAll { it.extractIssueComment() }
647 }
649 override fun findComment(id: Int): IssueComment? =
650 withStatement("select * from lpit_issue_comment left join lpit_user using (userid) where commentid = ?") {
651 setInt(1, id)
652 querySingle { it.extractIssueComment() }
653 }
655 override fun insertComment(issueComment: IssueComment): Int =
656 useStatement("update lpit_issue set updated = now() where issueid = ?") { updateIssueDate ->
657 withStatement("insert into lpit_issue_comment (issueid, comment, userid) values (?, ? ,?) returning commentid") {
658 with(issueComment) {
659 updateIssueDate.setInt(1, issueid)
660 setInt(1, issueid)
661 setStringSafe(2, comment)
662 setIntOrNull(3, author?.id)
663 }
664 val commentid = querySingle { it.getInt(1) }!!
665 updateIssueDate.executeUpdate()
666 commentid
667 }
668 }
670 override fun updateComment(issueComment: IssueComment) {
671 useStatement("update lpit_issue set updated = now() where issueid = ?") { updateIssueDate ->
672 withStatement("update lpit_issue_comment set comment = ?, updatecount = updatecount + 1, updated = now() where commentid = ?") {
673 with(issueComment) {
674 updateIssueDate.setInt(1, issueid)
675 setStringSafe(1, comment)
676 setInt(2, id)
677 }
678 executeUpdate()
679 updateIssueDate.executeUpdate()
680 }
681 }
682 }
685 override fun insertHistoryEvent(issue: Issue, issueComment: IssueComment, newId: Int) {
686 val type = if (newId > 0) IssueHistoryType.NewComment else IssueHistoryType.UpdateComment
687 val commentid = if (newId > 0) newId else issueComment.id
689 val eventid =
690 withStatement("insert into lpit_issue_history_event(issueid, subject, type) values (?,?,?::issue_history_event) returning eventid") {
691 setInt(1, issueComment.issueid)
692 setString(2, issue.subject)
693 setEnum(3, type)
694 querySingle { it.getInt(1) }!!
695 }
696 withStatement("insert into lpit_issue_comment_history (commentid, eventid, comment) values (?,?,?)") {
697 setInt(1, commentid)
698 setInt(2, eventid)
699 setString(3, issueComment.comment)
700 executeUpdate()
701 }
702 }
704 //</editor-fold>
706 //<editor-fold desc="Issue History">
708 override fun listIssueHistory(projectId: Int, days: Int) =
709 withStatement(
710 """
711 select u.username as current_assignee, evt.*, evtdata.*
712 from lpit_issue_history_event evt
713 join lpit_issue issue using (issueid)
714 left join lpit_user u on u.userid = issue.assignee
715 join lpit_issue_history_data evtdata using (eventid)
716 where project = ?
717 and time > now() - (? * interval '1' day)
718 order by time desc
719 """.trimIndent()
720 ) {
721 setInt(1, projectId)
722 setInt(2, days)
723 queryAll { rs->
724 with(rs) {
725 IssueHistoryEntry(
726 subject = getString("subject"),
727 time = getTimestamp("time"),
728 type = getEnum("type"),
729 currentAssignee = getString("current_assignee"),
730 issueid = getInt("issueid"),
731 component = getString("component") ?: "",
732 status = getEnum("status"),
733 category = getEnum("category"),
734 description = getString("description") ?: "",
735 assignee = getString("assignee") ?: "",
736 eta = getDate("eta"),
737 affected = getString("affected") ?: "",
738 resolved = getString("resolved") ?: ""
739 )
740 }
741 }
742 }
744 override fun listIssueCommentHistory(projectId: Int, days: Int) =
745 withStatement(
746 """
747 select u.username as current_assignee, evt.*, evtdata.*
748 from lpit_issue_history_event evt
749 join lpit_issue issue using (issueid)
750 left join lpit_user u on u.userid = issue.assignee
751 join lpit_issue_comment_history evtdata using (eventid)
752 where project = ?
753 and time > now() - (? * interval '1' day)
754 order by time desc
755 """.trimIndent()
756 ) {
757 setInt(1, projectId)
758 setInt(2, days)
759 queryAll { rs->
760 with(rs) {
761 IssueCommentHistoryEntry(
762 subject = getString("subject"),
763 time = getTimestamp("time"),
764 type = getEnum("type"),
765 currentAssignee = getString("current_assignee"),
766 issueid = getInt("issueid"),
767 commentid = getInt("commentid"),
768 comment = getString("comment")
769 )
770 }
771 }
772 }
774 //</editor-fold>
775 }
