Wed, 18 Aug 2021 15:30:49 +0200
#162 adds active flag to component
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, active,
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 active = getBoolean("active")
270 lead = extractOptionalUser()
271 }
273 private fun PreparedStatement.setComponent(index: Int, component: Component): Int {
274 with(component) {
275 var i = index
276 setStringSafe(i++, name)
277 setStringSafe(i++, node)
278 setStringSafe(i++, color.hex)
279 setInt(i++, ordinal)
280 setStringOrNull(i++, description)
281 setBoolean(i++, active)
282 setIntOrNull(i++, lead?.id)
283 return i
284 }
285 }
287 override fun listComponents(project: Project): List<Component> =
288 withStatement("$componentQuery where project = ? order by ordinal, lower(name)") {
289 setInt(1, project.id)
290 queryAll { it.extractComponent() }
291 }
293 override fun listComponentSummaries(project: Project): List<ComponentSummary> =
294 withStatement(
295 """
296 with issues as (
297 select component, phase, count(issueid) as total
298 from lpit_issue
299 join lpit_issue_phases using (status)
300 group by component, phase
301 ),
302 summary as (
303 select c.id, phase, total
304 from lpit_component c
305 left join issues i on c.id = i.component
306 )
307 select c.id, project, name, node, color, ordinal, description, active,
308 userid, username, givenname, lastname, mail,
309 open.total as open, wip.total as wip, done.total as done
310 from lpit_component c
311 left join lpit_user on lead = userid
312 left join summary open on c.id = open.id and open.phase = 0
313 left join summary wip on c.id = wip.id and wip.phase = 1
314 left join summary done on c.id = done.id and done.phase = 2
315 where c.project = ?
316 order by ordinal, name
317 """.trimIndent()
318 ) {
319 setInt(1, project.id)
320 queryAll { rs ->
321 ComponentSummary(rs.extractComponent()).apply {
322 issueSummary.open = rs.getInt("open")
323 issueSummary.active = rs.getInt("wip")
324 issueSummary.done = rs.getInt("done")
325 }
326 }
327 }
329 override fun findComponent(id: Int): Component? =
330 withStatement("$componentQuery where id = ?") {
331 setInt(1, id)
332 querySingle { it.extractComponent() }
333 }
335 override fun findComponentByNode(project: Project, node: String): Component? =
336 withStatement("$componentQuery where project = ? and node = ?") {
337 setInt(1, project.id)
338 setString(2, node)
339 querySingle { it.extractComponent() }
340 }
342 override fun insertComponent(component: Component) {
343 withStatement("insert into lpit_component (name, node, color, ordinal, description, active, lead, project) values (?, ?, ?, ?, ?, ?, ?, ?)") {
344 val col = setComponent(1, component)
345 setInt(col, component.projectid)
346 executeUpdate()
347 }
348 }
350 override fun updateComponent(component: Component) {
351 withStatement("update lpit_component set name = ?, node = ?, color = ?, ordinal = ?, description = ?, active = ?, lead = ? where id = ?") {
352 val col = setComponent(1, component)
353 setInt(col, component.id)
354 executeUpdate()
355 }
356 }
358 //</editor-fold>
360 //<editor-fold desc="Project">
362 //language=SQL
363 private val projectQuery =
364 """
365 select projectid, name, node, ordinal, description, repourl,
366 userid, username, lastname, givenname, mail
367 from lpit_project
368 left join lpit_user owner on lpit_project.owner = owner.userid
369 """.trimIndent()
371 private fun ResultSet.extractProject() =
372 Project(getInt("projectid")).apply {
373 name = getString("name")
374 node = getString("node")
375 ordinal = getInt("ordinal")
376 description = getString("description")
377 repoUrl = getString("repourl")
378 owner = extractOptionalUser()
379 }
381 private fun PreparedStatement.setProject(index: Int, project: Project): Int {
382 var i = index
383 with(project) {
384 setStringSafe(i++, name)
385 setStringSafe(i++, node)
386 setInt(i++, ordinal)
387 setStringOrNull(i++, description)
388 setStringOrNull(i++, repoUrl)
389 setIntOrNull(i++, owner?.id)
390 }
391 return i
392 }
394 override fun listProjects(): List<Project> =
395 withStatement("$projectQuery order by ordinal, lower(name)") {
396 queryAll { it.extractProject() }
397 }
399 override fun findProject(id: Int): Project? =
400 withStatement("$projectQuery where projectid = ?") {
401 setInt(1, id)
402 querySingle { it.extractProject() }
403 }
405 override fun findProjectByNode(node: String): Project? =
406 withStatement("$projectQuery where node = ?") {
407 setString(1, node)
408 querySingle { it.extractProject() }
409 }
411 override fun insertProject(project: Project) {
412 withStatement("insert into lpit_project (name, node, ordinal, description, repourl, owner) values (?, ?, ?, ?, ?, ?)") {
413 setProject(1, project)
414 executeUpdate()
415 }
416 }
418 override fun updateProject(project: Project) {
419 withStatement("update lpit_project set name = ?, node = ?, ordinal = ?, description = ?, repourl = ?, owner = ? where projectid = ?") {
420 val col = setProject(1, project)
421 setInt(col, project.id)
422 executeUpdate()
423 }
424 }
426 override fun collectIssueSummary(project: Project): IssueSummary =
427 withStatement(
428 """
429 select phase, count(*) as total
430 from lpit_issue
431 join lpit_issue_phases using(status)
432 where project = ?
433 group by phase
434 """.trimIndent()
435 ) {
436 setInt(1, project.id)
437 executeQuery().use {
438 val summary = IssueSummary()
439 while (it.next()) {
440 val phase = it.getInt("phase")
441 val total = it.getInt("total")
442 when (phase) {
443 0 -> summary.open = total
444 1 -> summary.active = total
445 2 -> summary.done = total
446 }
447 }
448 summary
449 }
450 }
452 //</editor-fold>
454 //<editor-fold desc="Issue">
456 //language=SQL
457 private val issueQuery =
458 """
459 select issueid,
460 i.project, p.name as projectname, p.node as projectnode,
461 component, c.name as componentname, c.node as componentnode,
462 status, category, subject, i.description,
463 userid, username, givenname, lastname, mail,
464 created, updated, eta
465 from lpit_issue i
466 join lpit_project p on i.project = projectid
467 left join lpit_component c on component = c.id
468 left join lpit_user on userid = assignee
469 """.trimIndent()
471 private fun ResultSet.extractIssue(): Issue {
472 val proj = Project(getInt("project")).apply {
473 name = getString("projectname")
474 node = getString("projectnode")
475 }
476 val comp = getInt("component").let {
477 if (wasNull()) null else
478 Component(it, proj.id).apply {
479 name = getString("componentname")
480 node = getString("componentnode")
481 }
482 }
483 val issue = Issue(getInt("issueid"), proj).apply {
484 component = comp
485 status = getEnum("status")
486 category = getEnum("category")
487 subject = getString("subject")
488 description = getString("description")
489 assignee = extractOptionalUser()
490 created = getTimestamp("created")
491 updated = getTimestamp("updated")
492 eta = getDate("eta")
493 }
495 //language=SQL
496 val queryAffected =
497 """
498 $versionQuery join lpit_issue_affected_version using (versionid)
499 where issueid = ? order by ordinal, name
500 """.trimIndent()
502 //language=SQL
503 val queryResolved =
504 """
505 $versionQuery join lpit_issue_resolved_version using (versionid)
506 where issueid = ? order by ordinal, name
507 """.trimIndent()
509 issue.affectedVersions = withStatement(queryAffected) {
510 setInt(1, issue.id)
511 queryAll { it.extractVersion() }
512 }
513 issue.resolvedVersions = withStatement(queryResolved) {
514 setInt(1, issue.id)
515 queryAll { it.extractVersion() }
516 }
517 return issue
518 }
520 private fun PreparedStatement.setIssue(index: Int, issue: Issue): Int {
521 var i = index
522 with(issue) {
523 setIntOrNull(i++, component?.id)
524 setEnum(i++, status)
525 setEnum(i++, category)
526 setStringSafe(i++, subject)
527 setStringOrNull(i++, description)
528 setIntOrNull(i++, assignee?.id)
529 setDateOrNull(i++, eta)
530 }
531 return i
532 }
534 override fun listIssues(filter: IssueFilter): List<Issue> =
535 withStatement(
536 """
537 with issue_version as (
538 select issueid, versionid from lpit_issue_affected_version
539 union select issueid, versionid from lpit_issue_resolved_version
540 ),
541 filtered_issues as (
542 select distinct issueid from lpit_issue
543 left join issue_version using (issueid)
544 where
545 (not ? or project = ?) and
546 (not ? or versionid = ?) and (not ? or versionid is null) and
547 (not ? or component = ?) and (not ? or component is null)
548 )
549 $issueQuery join filtered_issues using (issueid)
550 """.trimIndent()
551 ) {
552 fun <T : Entity> applyFilter(filter: Filter<T>, fflag: Int, nflag: Int, idcol: Int) {
553 when (filter) {
554 is AllFilter -> {
555 setBoolean(fflag, false)
556 setBoolean(nflag, false)
557 setInt(idcol, 0)
558 }
559 is NoneFilter -> {
560 setBoolean(fflag, false)
561 setBoolean(nflag, true)
562 setInt(idcol, 0)
563 }
564 is SpecificFilter -> {
565 setBoolean(fflag, true)
566 setBoolean(nflag, false)
567 setInt(idcol, filter.obj.id)
568 }
569 else -> {
570 TODO("Implement range filter.")
571 }
572 }
573 }
574 when (filter.project) {
575 is AllFilter -> {
576 setBoolean(1, false)
577 setInt(2, 0)
578 }
579 is SpecificFilter -> {
580 setBoolean(1, true)
581 setInt(2, filter.project.obj.id)
582 }
583 else -> throw IllegalArgumentException()
584 }
585 applyFilter(filter.version, 3, 5, 4)
586 applyFilter(filter.component, 6, 8, 7)
588 queryAll { it.extractIssue() }
589 }
591 override fun findIssue(id: Int): Issue? =
592 withStatement("$issueQuery where issueid = ?") {
593 setInt(1, id)
594 querySingle { it.extractIssue() }
595 }
597 private fun insertVersionInfo(id: Int, issue: Issue) {
598 withStatement("insert into lpit_issue_affected_version (issueid, versionid) values (?,?)") {
599 setInt(1, id)
600 issue.affectedVersions.forEach {
601 setInt(2, it.id)
602 executeUpdate()
603 }
604 }
605 withStatement("insert into lpit_issue_resolved_version (issueid, versionid) values (?,?)") {
606 setInt(1, id)
607 issue.resolvedVersions.forEach {
608 setInt(2, it.id)
609 executeUpdate()
610 }
611 }
612 }
614 override fun insertIssue(issue: Issue): Int {
615 val id = withStatement(
616 """
617 insert into lpit_issue (component, status, category, subject, description, assignee, eta, project)
618 values (?, ?::issue_status, ?::issue_category, ?, ?, ?, ?, ?)
619 returning issueid
620 """.trimIndent()
621 ) {
622 val col = setIssue(1, issue)
623 setInt(col, issue.project.id)
624 querySingle { it.getInt(1) }!!
625 }
626 insertVersionInfo(id, issue)
627 return id
628 }
630 override fun updateIssue(issue: Issue) {
631 withStatement(
632 """
633 update lpit_issue set updated = now(),
634 component = ?, status = ?::issue_status, category = ?::issue_category, subject = ?,
635 description = ?, assignee = ?, eta = ?
636 where issueid = ?
637 """.trimIndent()
638 ) {
639 val col = setIssue(1, issue)
640 setInt(col, issue.id)
641 executeUpdate()
642 }
644 // TODO: improve by only inserting / deleting changed version information
645 withStatement("delete from lpit_issue_affected_version where issueid = ?") {
646 setInt(1, issue.id)
647 executeUpdate()
648 }
649 withStatement("delete from lpit_issue_resolved_version where issueid = ?") {
650 setInt(1, issue.id)
651 executeUpdate()
652 }
653 insertVersionInfo(issue.id, issue)
654 }
656 //</editor-fold>
658 //<editor-fold desc="IssueComment">
660 private fun ResultSet.extractIssueComment() =
661 IssueComment(getInt("commentid"), getInt("issueid")).apply {
662 created = getTimestamp("created")
663 updated = getTimestamp("updated")
664 updateCount = getInt("updatecount")
665 comment = getString("comment")
666 author = extractOptionalUser()
667 }
669 override fun listComments(issue: Issue): List<IssueComment> =
670 withStatement("select * from lpit_issue_comment left join lpit_user using (userid) where issueid = ? order by created") {
671 setInt(1, issue.id)
672 queryAll { it.extractIssueComment() }
673 }
675 override fun findComment(id: Int): IssueComment? =
676 withStatement("select * from lpit_issue_comment left join lpit_user using (userid) where commentid = ?") {
677 setInt(1, id)
678 querySingle { it.extractIssueComment() }
679 }
681 override fun insertComment(issueComment: IssueComment) {
682 useStatement("update lpit_issue set updated = now() where issueid = ?") { updateIssueDate ->
683 withStatement("insert into lpit_issue_comment (issueid, comment, userid) values (?, ? ,?)") {
684 with(issueComment) {
685 updateIssueDate.setInt(1, issueid)
686 setInt(1, issueid)
687 setStringSafe(2, comment)
688 setIntOrNull(3, author?.id)
689 }
690 executeUpdate()
691 updateIssueDate.executeUpdate()
692 }
693 }
694 }
696 override fun updateComment(issueComment: IssueComment) {
697 useStatement("update lpit_issue set updated = now() where issueid = ?") { updateIssueDate ->
698 withStatement("update lpit_issue_comment set comment = ?, updatecount = updatecount + 1, updated = now() where commentid = ?") {
699 with(issueComment) {
700 updateIssueDate.setInt(1, issueid)
701 setStringSafe(1, comment)
702 setInt(2, id)
703 }
704 executeUpdate()
705 updateIssueDate.executeUpdate()
706 }
707 }
708 }
709 //</editor-fold>
710 }