src/main/kotlin/de/uapcore/lightpit/dao/PostgresDataAccessObject.kt@b7f3e972b13c
src/main/kotlin/de/uapcore/lightpit/dao/PostgresDataAccessObject.kt
Sat, 27 Nov 2021 13:03:57 +0100
- author
- Mike Becker <universe@uap-core.de>
- date
- Sat, 27 Nov 2021 13:03:57 +0100
- changeset 242
- b7f3e972b13c
- parent 241
- 1ca4f27cefe8
- child 244
- 28052f3b9cf9
- permissions
- -rw-r--r--
#109 add comment history
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.util.*
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, version.projectid)
220 setDateOrNull(6, version.release)
221 setDateOrNull(7, version.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 //</editor-fold>
450 //<editor-fold desc="Issue">
452 //language=SQL
453 private val issueQuery =
454 """
455 select issueid,
456 i.project, p.name as projectname, p.node as projectnode,
457 component, c.name as componentname, c.node as componentnode,
458 status, category, subject, i.description,
459 userid, username, givenname, lastname, mail,
460 created, updated, eta, affected, resolved
461 from lpit_issue i
462 join lpit_project p on i.project = projectid
463 left join lpit_component c on component = c.id
464 left join lpit_user on userid = assignee
465 """.trimIndent()
467 private fun ResultSet.extractIssue(): Issue {
468 val proj = Project(getInt("project")).apply {
469 name = getString("projectname")
470 node = getString("projectnode")
471 }
472 val comp = getInt("component").let {
473 if (wasNull()) null else
474 Component(it, proj.id).apply {
475 name = getString("componentname")
476 node = getString("componentnode")
477 }
478 }
479 val issue = Issue(getInt("issueid"), proj).apply {
480 component = comp
481 status = getEnum("status")
482 category = getEnum("category")
483 subject = getString("subject")
484 description = getString("description")
485 assignee = extractOptionalUser()
486 created = getTimestamp("created")
487 updated = getTimestamp("updated")
488 eta = getDate("eta")
489 affected = getInt("affected").takeIf { it > 0 }?.let { findVersion(it) }
490 resolved = getInt("resolved").takeIf { it > 0 }?.let { findVersion(it) }
491 }
493 return issue
494 }
496 private fun PreparedStatement.setIssue(index: Int, issue: Issue): Int {
497 var i = index
498 with(issue) {
499 setIntOrNull(i++, component?.id)
500 setEnum(i++, status)
501 setEnum(i++, category)
502 setStringSafe(i++, subject)
503 setStringOrNull(i++, description)
504 setIntOrNull(i++, assignee?.id)
505 setDateOrNull(i++, eta)
506 setIntOrNull(i++, affected?.id)
507 setIntOrNull(i++, resolved?.id)
508 }
509 return i
510 }
512 override fun listIssues(filter: IssueFilter): List<Issue> =
513 withStatement(
514 """$issueQuery where
515 (not ? or i.project = ?) and
516 (not ? or ? in (resolved, affected)) and (not ? or (resolved is null and affected is null)) and
517 (not ? or component = ?) and (not ? or component is null)
518 """.trimIndent()
519 ) {
520 fun <T : Entity> applyFilter(filter: Filter<T>, fflag: Int, nflag: Int, idcol: Int) {
521 when (filter) {
522 is AllFilter -> {
523 setBoolean(fflag, false)
524 setBoolean(nflag, false)
525 setInt(idcol, 0)
526 }
527 is NoneFilter -> {
528 setBoolean(fflag, false)
529 setBoolean(nflag, true)
530 setInt(idcol, 0)
531 }
532 is SpecificFilter -> {
533 setBoolean(fflag, true)
534 setBoolean(nflag, false)
535 setInt(idcol, filter.obj.id)
536 }
537 else -> {
538 TODO("Implement range filter.")
539 }
540 }
541 }
542 when (filter.project) {
543 is AllFilter -> {
544 setBoolean(1, false)
545 setInt(2, 0)
546 }
547 is SpecificFilter -> {
548 setBoolean(1, true)
549 setInt(2, filter.project.obj.id)
550 }
551 else -> throw IllegalArgumentException()
552 }
553 applyFilter(filter.version, 3, 5, 4)
554 applyFilter(filter.component, 6, 8, 7)
556 queryAll { it.extractIssue() }
557 }
559 override fun findIssue(id: Int): Issue? =
560 withStatement("$issueQuery where issueid = ?") {
561 setInt(1, id)
562 querySingle { it.extractIssue() }
563 }
565 override fun insertIssue(issue: Issue): Int {
566 val id = withStatement(
567 """
568 insert into lpit_issue (component, status, category, subject, description, assignee, eta, affected, resolved, project)
569 values (?, ?::issue_status, ?::issue_category, ?, ?, ?, ?, ?, ?, ?)
570 returning issueid
571 """.trimIndent()
572 ) {
573 val col = setIssue(1, issue)
574 setInt(col, issue.project.id)
575 querySingle { it.getInt(1) }!!
576 }
577 return id
578 }
580 override fun updateIssue(issue: Issue) {
581 withStatement(
582 """
583 update lpit_issue set updated = now(),
584 component = ?, status = ?::issue_status, category = ?::issue_category, subject = ?,
585 description = ?, assignee = ?, eta = ?, affected = ?, resolved = ?
586 where issueid = ?
587 """.trimIndent()
588 ) {
589 val col = setIssue(1, issue)
590 setInt(col, issue.id)
591 executeUpdate()
592 }
593 }
595 override fun insertHistoryEvent(issue: Issue, newId: Int) {
596 val type = if (newId > 0) IssueHistoryType.New else IssueHistoryType.Update
597 val issueid = if (newId > 0) newId else issue.id
599 val eventid =
600 withStatement("insert into lpit_issue_history_event(issueid, subject, type) values (?,?,?::issue_history_event) returning eventid") {
601 setInt(1, issueid)
602 setString(2, issue.subject)
603 setEnum(3, type)
604 querySingle { it.getInt(1) }!!
605 }
606 withStatement(
607 """
608 insert into lpit_issue_history_data (component, status, category, description, assignee, eta, affected, resolved, eventid)
609 values (?, ?::issue_status, ?::issue_category, ?, ?, ?, ?, ?, ?)
610 """.trimIndent()
611 ) {
612 setStringOrNull(1, issue.component?.name)
613 setEnum(2, issue.status)
614 setEnum(3, issue.category)
615 setStringOrNull(4, issue.description)
616 setStringOrNull(5, issue.assignee?.shortDisplayname)
617 setDateOrNull(6, issue.eta)
618 setStringOrNull(7, issue.affected?.name)
619 setStringOrNull(8, issue.resolved?.name)
620 setInt(9, eventid)
621 executeUpdate()
622 }
623 }
625 //</editor-fold>
627 //<editor-fold desc="IssueComment">
629 private fun ResultSet.extractIssueComment() =
630 IssueComment(getInt("commentid"), getInt("issueid")).apply {
631 created = getTimestamp("created")
632 updated = getTimestamp("updated")
633 updateCount = getInt("updatecount")
634 comment = getString("comment")
635 author = extractOptionalUser()
636 }
638 override fun listComments(issue: Issue): List<IssueComment> =
639 withStatement("select * from lpit_issue_comment left join lpit_user using (userid) where issueid = ? order by created") {
640 setInt(1, issue.id)
641 queryAll { it.extractIssueComment() }
642 }
644 override fun findComment(id: Int): IssueComment? =
645 withStatement("select * from lpit_issue_comment left join lpit_user using (userid) where commentid = ?") {
646 setInt(1, id)
647 querySingle { it.extractIssueComment() }
648 }
650 override fun insertComment(issueComment: IssueComment): Int =
651 useStatement("update lpit_issue set updated = now() where issueid = ?") { updateIssueDate ->
652 withStatement("insert into lpit_issue_comment (issueid, comment, userid) values (?, ? ,?) returning commentid") {
653 with(issueComment) {
654 updateIssueDate.setInt(1, issueid)
655 setInt(1, issueid)
656 setStringSafe(2, comment)
657 setIntOrNull(3, author?.id)
658 }
659 val commentid = querySingle { it.getInt(1) }!!
660 updateIssueDate.executeUpdate()
661 commentid
662 }
663 }
665 override fun updateComment(issueComment: IssueComment) {
666 useStatement("update lpit_issue set updated = now() where issueid = ?") { updateIssueDate ->
667 withStatement("update lpit_issue_comment set comment = ?, updatecount = updatecount + 1, updated = now() where commentid = ?") {
668 with(issueComment) {
669 updateIssueDate.setInt(1, issueid)
670 setStringSafe(1, comment)
671 setInt(2, id)
672 }
673 executeUpdate()
674 updateIssueDate.executeUpdate()
675 }
676 }
677 }
680 override fun insertHistoryEvent(issue: Issue, issueComment: IssueComment, newId: Int) {
681 val type = if (newId > 0) IssueHistoryType.NewComment else IssueHistoryType.UpdateComment
682 val commentid = if (newId > 0) newId else issueComment.id
684 val eventid =
685 withStatement("insert into lpit_issue_history_event(issueid, subject, type) values (?,?,?::issue_history_event) returning eventid") {
686 setInt(1, issueComment.issueid)
687 setString(1, issue.subject)
688 setEnum(3, type)
689 querySingle { it.getInt(1) }!!
690 }
691 withStatement("insert into lpit_issue_comment_history (commentid, eventid, comment) values (?,?,?,?)") {
692 setInt(1, commentid)
693 setInt(2, eventid)
694 setString(3, issueComment.comment)
695 executeUpdate()
696 }
697 }
699 //</editor-fold>
701 //<editor-fold desc="Issue History">
703 override fun listIssueHistory(projectId: Int, days: Int) =
704 withStatement(
705 """
706 select u.username as current_assignee, evt.*, evtdata.*
707 from lpit_issue_history_event evt
708 join lpit_issue issue using (issueid)
709 left join lpit_user u on u.userid = issue.assignee
710 join lpit_issue_history_data evtdata using (eventid)
711 where project = ?
712 and time > now() - (? * interval '1' day)
713 order by time desc
714 """.trimIndent()
715 ) {
716 setInt(1, projectId)
717 setInt(2, days)
718 queryAll { rs->
719 with(rs) {
720 IssueHistoryEntry(
721 subject = getString("subject"),
722 time = getTimestamp("time"),
723 type = getEnum("type"),
724 currentAssignee = getString("current_assignee"),
725 issueid = getInt("issueid"),
726 component = getString("component") ?: "",
727 status = getEnum("status"),
728 category = getEnum("category"),
729 description = getString("description") ?: "",
730 assignee = getString("assignee") ?: "",
731 eta = getDate("eta"),
732 affected = getString("affected") ?: "",
733 resolved = getString("resolved") ?: ""
734 )
735 }
736 }
737 }
739 override fun listIssueCommentHistory(projectId: Int, days: Int) =
740 withStatement(
741 """
742 select u.username as current_assignee, evt.*, evtdata.*
743 from lpit_issue_history_event evt
744 join lpit_issue issue using (issueid)
745 left join lpit_user u on u.userid = issue.assignee
746 join lpit_issue_comment_history evtdata using (eventid)
747 where project = ?
748 and time > now() - (? * interval '1' day)
749 order by time desc
750 """.trimIndent()
751 ) {
752 setInt(1, projectId)
753 setInt(2, days)
754 queryAll { rs->
755 with(rs) {
756 IssueCommentHistoryEntry(
757 subject = getString("subject"),
758 time = getTimestamp("time"),
759 type = getEnum("type"),
760 currentAssignee = getString("current_assignee"),
761 issueid = getInt("issueid"),
762 commentid = getInt("commentid"),
763 comment = getString("comment")
764 )
765 }
766 }
767 }
769 //</editor-fold>
770 }
