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