src/main/kotlin/de/uapcore/lightpit/dao/PostgresDataAccessObject.kt@c8e1b5282f69
src/main/kotlin/de/uapcore/lightpit/dao/PostgresDataAccessObject.kt
Wed, 18 Aug 2021 15:04:59 +0200
- author
- Mike Becker <universe@uap-core.de>
- date
- Wed, 18 Aug 2021 15:04:59 +0200
- changeset 226
- c8e1b5282f69
- parent 225
- 87328572e36f
- child 227
- f0ede8046b59
- permissions
- -rw-r--r--
adds mailto link to issue-view.jsp
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.WebColor
30 import de.uapcore.lightpit.util.*
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 """
157 with version_map(issueid, versionid, isresolved) as (
158 select issueid, versionid, true
159 from lpit_issue_resolved_version
160 union
161 select issueid, versionid, false
162 from lpit_issue_affected_version
163 ),
164 issues as (
165 select versionid, phase, isresolved, count(issueid) as total
166 from lpit_issue
167 join version_map using (issueid)
168 join lpit_issue_phases using (status)
169 group by versionid, phase, isresolved
170 ),
171 summary as (
172 select versionid, phase, isresolved, total
173 from lpit_version v
174 left join issues using (versionid)
175 )
176 select v.versionid, project, name, node, ordinal, status, release, eol,
177 ro.total as resolved_open, ra.total as resolved_active, rd.total as resolved_done,
178 ao.total as affected_open, aa.total as affected_active, ad.total as affected_done
179 from lpit_version v
180 left join summary ro on ro.versionid = v.versionid and ro.phase = 0 and ro.isresolved
181 left join summary ra on ra.versionid = v.versionid and ra.phase = 1 and ra.isresolved
182 left join summary rd on rd.versionid = v.versionid and rd.phase = 2 and rd.isresolved
183 left join summary ao on ao.versionid = v.versionid and ao.phase = 0 and not ao.isresolved
184 left join summary aa on aa.versionid = v.versionid and aa.phase = 1 and not aa.isresolved
185 left join summary ad on ad.versionid = v.versionid and ad.phase = 2 and not ad.isresolved
186 where v.project = ?
187 order by ordinal desc, lower(name) desc
188 """.trimIndent()
189 ) {
190 setInt(1, project.id)
191 queryAll { rs ->
192 VersionSummary(rs.extractVersion()).apply {
193 reportedTotal.open = rs.getInt("affected_open")
194 reportedTotal.active = rs.getInt("affected_active")
195 reportedTotal.done = rs.getInt("affected_done")
196 resolvedTotal.open = rs.getInt("resolved_open")
197 resolvedTotal.active = rs.getInt("resolved_active")
198 resolvedTotal.done = rs.getInt("resolved_done")
199 }
200 }
201 }
203 override fun findVersion(id: Int): Version? =
204 withStatement("$versionQuery where versionid = ?") {
205 setInt(1, id)
206 querySingle { it.extractVersion() }
207 }
209 override fun findVersionByNode(project: Project, node: String): Version? =
210 withStatement("$versionQuery where project = ? and node = ?") {
211 setInt(1, project.id)
212 setString(2, node)
213 querySingle { it.extractVersion() }
214 }
216 override fun insertVersion(version: Version) {
217 withStatement("insert into lpit_version (name, node, ordinal, status, project, release, eol) values (?, ?, ?, ?::version_status, ?, ?, ?)") {
218 with(version) {
219 setStringSafe(1, name)
220 setStringSafe(2, node)
221 setInt(3, ordinal)
222 setEnum(4, status)
223 setInt(5, version.projectid)
224 setDateOrNull(6, version.release)
225 setDateOrNull(7, version.eol)
226 }
227 executeUpdate()
228 }
230 }
232 override fun updateVersion(version: Version) {
233 withStatement("update lpit_version set name = ?, node = ?, ordinal = ?, status = ?::version_status, release=?,eol=? where versionid = ?") {
234 with(version) {
235 setStringSafe(1, name)
236 setStringSafe(2, node)
237 setInt(3, ordinal)
238 setEnum(4, status)
239 setDateOrNull(5, version.release)
240 setDateOrNull(6, version.eol)
241 setInt(7, id)
242 }
243 executeUpdate()
244 }
245 }
246 //</editor-fold>
248 //<editor-fold desc="Component">
249 //language=SQL
250 private val componentQuery =
251 """
252 select id, project, name, node, color, ordinal, description,
253 userid, username, givenname, lastname, mail
254 from lpit_component
255 left join lpit_user on lead = userid
256 """.trimIndent()
258 private fun ResultSet.extractComponent(): Component =
259 Component(getInt("id"), getInt("project")).apply {
260 name = getString("name")
261 node = getString("node")
262 color = try {
263 WebColor(getString("color"))
264 } catch (ex: IllegalArgumentException) {
265 WebColor("000000")
266 }
267 ordinal = getInt("ordinal")
268 description = getString("description")
269 lead = extractOptionalUser()
270 }
272 private fun PreparedStatement.setComponent(index: Int, component: Component): Int {
273 with(component) {
274 var i = index
275 setStringSafe(i++, name)
276 setStringSafe(i++, node)
277 setStringSafe(i++, color.hex)
278 setInt(i++, ordinal)
279 setStringOrNull(i++, description)
280 setIntOrNull(i++, lead?.id)
281 return i
282 }
283 }
285 override fun listComponents(project: Project): List<Component> =
286 withStatement("$componentQuery where project = ? order by ordinal, lower(name)") {
287 setInt(1, project.id)
288 queryAll { it.extractComponent() }
289 }
291 override fun listComponentSummaries(project: Project): List<ComponentSummary> =
292 withStatement(
293 """
294 with issues as (
295 select component, phase, count(issueid) as total
296 from lpit_issue
297 join lpit_issue_phases using (status)
298 group by component, phase
299 ),
300 summary as (
301 select c.id, phase, total
302 from lpit_component c
303 left join issues i on c.id = i.component
304 )
305 select c.id, project, name, node, color, ordinal, description,
306 userid, username, givenname, lastname, mail,
307 open.total as open, active.total as active, done.total as done
308 from lpit_component c
309 left join lpit_user on lead = userid
310 left join summary open on c.id = open.id and open.phase = 0
311 left join summary active on c.id = active.id and active.phase = 1
312 left join summary done on c.id = done.id and done.phase = 2
313 where c.project = ?
314 order by ordinal, name
315 """.trimIndent()
316 ) {
317 setInt(1, project.id)
318 queryAll { rs ->
319 ComponentSummary(rs.extractComponent()).apply {
320 issueSummary.open = rs.getInt("open")
321 issueSummary.active = rs.getInt("active")
322 issueSummary.done = rs.getInt("done")
323 }
324 }
325 }
327 override fun findComponent(id: Int): Component? =
328 withStatement("$componentQuery where id = ?") {
329 setInt(1, id)
330 querySingle { it.extractComponent() }
331 }
333 override fun findComponentByNode(project: Project, node: String): Component? =
334 withStatement("$componentQuery where project = ? and node = ?") {
335 setInt(1, project.id)
336 setString(2, node)
337 querySingle { it.extractComponent() }
338 }
340 override fun insertComponent(component: Component) {
341 withStatement("insert into lpit_component (name, node, color, ordinal, description, lead, project) values (?, ?, ?, ?, ?, ?, ?)") {
342 val col = setComponent(1, component)
343 setInt(col, component.projectid)
344 executeUpdate()
345 }
346 }
348 override fun updateComponent(component: Component) {
349 withStatement("update lpit_component set name = ?, node = ?, color = ?, ordinal = ?, description = ?, lead = ? where id = ?") {
350 val col = setComponent(1, component)
351 setInt(col, component.id)
352 executeUpdate()
353 }
354 }
356 //</editor-fold>
358 //<editor-fold desc="Project">
360 //language=SQL
361 private val projectQuery =
362 """
363 select projectid, name, node, ordinal, description, repourl,
364 userid, username, lastname, givenname, mail
365 from lpit_project
366 left join lpit_user owner on lpit_project.owner = owner.userid
367 """.trimIndent()
369 private fun ResultSet.extractProject() =
370 Project(getInt("projectid")).apply {
371 name = getString("name")
372 node = getString("node")
373 ordinal = getInt("ordinal")
374 description = getString("description")
375 repoUrl = getString("repourl")
376 owner = extractOptionalUser()
377 }
379 private fun PreparedStatement.setProject(index: Int, project: Project): Int {
380 var i = index
381 with(project) {
382 setStringSafe(i++, name)
383 setStringSafe(i++, node)
384 setInt(i++, ordinal)
385 setStringOrNull(i++, description)
386 setStringOrNull(i++, repoUrl)
387 setIntOrNull(i++, owner?.id)
388 }
389 return i
390 }
392 override fun listProjects(): List<Project> =
393 withStatement("$projectQuery order by ordinal, lower(name)") {
394 queryAll { it.extractProject() }
395 }
397 override fun findProject(id: Int): Project? =
398 withStatement("$projectQuery where projectid = ?") {
399 setInt(1, id)
400 querySingle { it.extractProject() }
401 }
403 override fun findProjectByNode(node: String): Project? =
404 withStatement("$projectQuery where node = ?") {
405 setString(1, node)
406 querySingle { it.extractProject() }
407 }
409 override fun insertProject(project: Project) {
410 withStatement("insert into lpit_project (name, node, ordinal, description, repourl, owner) values (?, ?, ?, ?, ?, ?)") {
411 setProject(1, project)
412 executeUpdate()
413 }
414 }
416 override fun updateProject(project: Project) {
417 withStatement("update lpit_project set name = ?, node = ?, ordinal = ?, description = ?, repourl = ?, owner = ? where projectid = ?") {
418 val col = setProject(1, project)
419 setInt(col, project.id)
420 executeUpdate()
421 }
422 }
424 override fun collectIssueSummary(project: Project): IssueSummary =
425 withStatement(
426 """
427 select phase, count(*) as total
428 from lpit_issue
429 join lpit_issue_phases using(status)
430 where project = ?
431 group by phase
432 """.trimIndent()
433 ) {
434 setInt(1, project.id)
435 executeQuery().use {
436 val summary = IssueSummary()
437 while (it.next()) {
438 val phase = it.getInt("phase")
439 val total = it.getInt("total")
440 when (phase) {
441 0 -> summary.open = total
442 1 -> summary.active = total
443 2 -> summary.done = total
444 }
445 }
446 summary
447 }
448 }
450 //</editor-fold>
452 //<editor-fold desc="Issue">
454 //language=SQL
455 private val issueQuery =
456 """
457 select issueid,
458 i.project, p.name as projectname, p.node as projectnode,
459 component, c.name as componentname, c.node as componentnode,
460 status, category, subject, i.description,
461 userid, username, givenname, lastname, mail,
462 created, updated, eta
463 from lpit_issue i
464 join lpit_project p on i.project = projectid
465 left join lpit_component c on component = c.id
466 left join lpit_user on userid = assignee
467 """.trimIndent()
469 private fun ResultSet.extractIssue(): Issue {
470 val proj = Project(getInt("project")).apply {
471 name = getString("projectname")
472 node = getString("projectnode")
473 }
474 val comp = getInt("component").let {
475 if (wasNull()) null else
476 Component(it, proj.id).apply {
477 name = getString("componentname")
478 node = getString("componentnode")
479 }
480 }
481 val issue = Issue(getInt("issueid"), proj).apply {
482 component = comp
483 status = getEnum("status")
484 category = getEnum("category")
485 subject = getString("subject")
486 description = getString("description")
487 assignee = extractOptionalUser()
488 created = getTimestamp("created")
489 updated = getTimestamp("updated")
490 eta = getDate("eta")
491 }
493 //language=SQL
494 val queryAffected =
495 """
496 $versionQuery join lpit_issue_affected_version using (versionid)
497 where issueid = ? order by ordinal, name
498 """.trimIndent()
500 //language=SQL
501 val queryResolved =
502 """
503 $versionQuery join lpit_issue_resolved_version using (versionid)
504 where issueid = ? order by ordinal, name
505 """.trimIndent()
507 issue.affectedVersions = withStatement(queryAffected) {
508 setInt(1, issue.id)
509 queryAll { it.extractVersion() }
510 }
511 issue.resolvedVersions = withStatement(queryResolved) {
512 setInt(1, issue.id)
513 queryAll { it.extractVersion() }
514 }
515 return issue
516 }
518 private fun PreparedStatement.setIssue(index: Int, issue: Issue): Int {
519 var i = index
520 with(issue) {
521 setIntOrNull(i++, component?.id)
522 setEnum(i++, status)
523 setEnum(i++, category)
524 setStringSafe(i++, subject)
525 setStringOrNull(i++, description)
526 setIntOrNull(i++, assignee?.id)
527 setDateOrNull(i++, eta)
528 }
529 return i
530 }
532 override fun listIssues(filter: IssueFilter): List<Issue> =
533 withStatement(
534 """
535 with issue_version as (
536 select issueid, versionid from lpit_issue_affected_version
537 union select issueid, versionid from lpit_issue_resolved_version
538 ),
539 filtered_issues as (
540 select distinct issueid from lpit_issue
541 left join issue_version using (issueid)
542 where
543 (not ? or project = ?) and
544 (not ? or versionid = ?) and (not ? or versionid is null) and
545 (not ? or component = ?) and (not ? or component is null)
546 )
547 $issueQuery join filtered_issues using (issueid)
548 """.trimIndent()
549 ) {
550 fun <T : Entity> applyFilter(filter: Filter<T>, fflag: Int, nflag: Int, idcol: Int) {
551 when (filter) {
552 is AllFilter -> {
553 setBoolean(fflag, false)
554 setBoolean(nflag, false)
555 setInt(idcol, 0)
556 }
557 is NoneFilter -> {
558 setBoolean(fflag, false)
559 setBoolean(nflag, true)
560 setInt(idcol, 0)
561 }
562 is SpecificFilter -> {
563 setBoolean(fflag, true)
564 setBoolean(nflag, false)
565 setInt(idcol, filter.obj.id)
566 }
567 else -> {
568 TODO("Implement range filter.")
569 }
570 }
571 }
572 when (filter.project) {
573 is AllFilter -> {
574 setBoolean(1, false)
575 setInt(2, 0)
576 }
577 is SpecificFilter -> {
578 setBoolean(1, true)
579 setInt(2, filter.project.obj.id)
580 }
581 else -> throw IllegalArgumentException()
582 }
583 applyFilter(filter.version, 3, 5, 4)
584 applyFilter(filter.component, 6, 8, 7)
586 queryAll { it.extractIssue() }
587 }
589 override fun findIssue(id: Int): Issue? =
590 withStatement("$issueQuery where issueid = ?") {
591 setInt(1, id)
592 querySingle { it.extractIssue() }
593 }
595 private fun insertVersionInfo(id: Int, issue: Issue) {
596 withStatement("insert into lpit_issue_affected_version (issueid, versionid) values (?,?)") {
597 setInt(1, id)
598 issue.affectedVersions.forEach {
599 setInt(2, it.id)
600 executeUpdate()
601 }
602 }
603 withStatement("insert into lpit_issue_resolved_version (issueid, versionid) values (?,?)") {
604 setInt(1, id)
605 issue.resolvedVersions.forEach {
606 setInt(2, it.id)
607 executeUpdate()
608 }
609 }
610 }
612 override fun insertIssue(issue: Issue): Int {
613 val id = withStatement(
614 """
615 insert into lpit_issue (component, status, category, subject, description, assignee, eta, project)
616 values (?, ?::issue_status, ?::issue_category, ?, ?, ?, ?, ?)
617 returning issueid
618 """.trimIndent()
619 ) {
620 val col = setIssue(1, issue)
621 setInt(col, issue.project.id)
622 querySingle { it.getInt(1) }!!
623 }
624 insertVersionInfo(id, issue)
625 return id
626 }
628 override fun updateIssue(issue: Issue) {
629 withStatement(
630 """
631 update lpit_issue set updated = now(),
632 component = ?, status = ?::issue_status, category = ?::issue_category, subject = ?,
633 description = ?, assignee = ?, eta = ?
634 where issueid = ?
635 """.trimIndent()
636 ) {
637 val col = setIssue(1, issue)
638 setInt(col, issue.id)
639 executeUpdate()
640 }
642 // TODO: improve by only inserting / deleting changed version information
643 withStatement("delete from lpit_issue_affected_version where issueid = ?") {
644 setInt(1, issue.id)
645 executeUpdate()
646 }
647 withStatement("delete from lpit_issue_resolved_version where issueid = ?") {
648 setInt(1, issue.id)
649 executeUpdate()
650 }
651 insertVersionInfo(issue.id, issue)
652 }
654 //</editor-fold>
656 //<editor-fold desc="IssueComment">
658 private fun ResultSet.extractIssueComment() =
659 IssueComment(getInt("commentid"), getInt("issueid")).apply {
660 created = getTimestamp("created")
661 updated = getTimestamp("updated")
662 updateCount = getInt("updatecount")
663 comment = getString("comment")
664 author = extractOptionalUser()
665 }
667 override fun listComments(issue: Issue): List<IssueComment> =
668 withStatement("select * from lpit_issue_comment left join lpit_user using (userid) where issueid = ? order by created") {
669 setInt(1, issue.id)
670 queryAll { it.extractIssueComment() }
671 }
673 override fun findComment(id: Int): IssueComment? =
674 withStatement("select * from lpit_issue_comment left join lpit_user using (userid) where commentid = ?") {
675 setInt(1, id)
676 querySingle { it.extractIssueComment() }
677 }
679 override fun insertComment(issueComment: IssueComment) {
680 useStatement("update lpit_issue set updated = now() where issueid = ?") { updateIssueDate ->
681 withStatement("insert into lpit_issue_comment (issueid, comment, userid) values (?, ? ,?)") {
682 with(issueComment) {
683 updateIssueDate.setInt(1, issueid)
684 setInt(1, issueid)
685 setStringSafe(2, comment)
686 setIntOrNull(3, author?.id)
687 }
688 executeUpdate()
689 updateIssueDate.executeUpdate()
690 }
691 }
692 }
694 override fun updateComment(issueComment: IssueComment) {
695 useStatement("update lpit_issue set updated = now() where issueid = ?") { updateIssueDate ->
696 withStatement("update lpit_issue_comment set comment = ?, updatecount = updatecount + 1, updated = now() where commentid = ?") {
697 with(issueComment) {
698 updateIssueDate.setInt(1, issueid)
699 setStringSafe(1, comment)
700 setInt(2, id)
701 }
702 executeUpdate()
703 updateIssueDate.executeUpdate()
704 }
705 }
706 }
707 //</editor-fold>
708 }
