Wed, 06 Jan 2021 15:39:56 +0100
merge resource bundles
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.filter.*
30 import de.uapcore.lightpit.types.WebColor
31 import java.sql.Connection
32 import java.sql.PreparedStatement
33 import java.sql.ResultSet
35 class PostgresDataAccessObject(private val connection: Connection) : DataAccessObject {
37 //<editor-fold desc="User">
38 private fun selectUserInfo(
39 rs: ResultSet,
40 idColumn: String = "userid",
41 usernameColumn: String = "username",
42 givennameColumn: String = "givenname",
43 lastnameColumn: String = "lastname",
44 mailColumn: String = "mail"
45 ): User? {
46 val idval = rs.getInt(idColumn)
47 return if (rs.wasNull()) null else {
48 User(idval).apply {
49 username = rs.getString(usernameColumn)
50 givenname = rs.getString(givennameColumn)
51 lastname = rs.getString(lastnameColumn)
52 mail = rs.getString(mailColumn)
53 }
54 }
55 }
57 private fun selectUsers(stmt: PreparedStatement) = sequence {
58 stmt.executeQuery().use { rs ->
59 while (rs.next()) selectUserInfo(rs)?.let { yield(it) }
60 }
61 }
63 //language=SQL
64 private val userQuery = "select userid, username, lastname, givenname, mail from lpit_user"
66 private val stmtUsers by lazy {
67 connection.prepareStatement(
68 """${userQuery}
69 where userid > 0
70 order by username
71 """
72 )
73 }
74 private val stmtUserByID by lazy {
75 connection.prepareStatement(
76 """${userQuery}
77 where userid = ?
78 """
79 )
80 }
81 private val stmtUserByName by lazy {
82 connection.prepareStatement(
83 """${userQuery}
84 where lower(username) = lower(?)
85 """
86 )
87 }
88 private val stmtInsertUser by lazy {
89 connection.prepareStatement(
90 "insert into lpit_user (username, lastname, givenname, mail) values (?, ?, ?, ?)"
91 )
92 }
93 private val stmtUpdateUser by lazy {
94 connection.prepareStatement(
95 "update lpit_user set lastname = ?, givenname = ?, mail = ? where userid = ?"
96 )
97 }
99 override fun listUsers() = selectUsers(stmtUsers).toList()
100 override fun findUser(id: Int): User? {
101 stmtUserByID.setInt(1, id)
102 return selectUsers(stmtUserByID).firstOrNull()
103 }
105 override fun findUserByName(username: String): User? {
106 stmtUserByName.setString(1, username)
107 return selectUsers(stmtUserByName).firstOrNull()
108 }
110 override fun insertUser(user: User) {
111 with(user) {
112 stmtInsertUser.setStringSafe(1, username)
113 stmtInsertUser.setStringOrNull(2, lastname)
114 stmtInsertUser.setStringOrNull(3, givenname)
115 stmtInsertUser.setStringOrNull(4, mail)
116 }
117 stmtInsertUser.execute()
118 }
120 override fun updateUser(user: User) {
121 with(user) {
122 stmtUpdateUser.setStringOrNull(1, lastname)
123 stmtUpdateUser.setStringOrNull(2, givenname)
124 stmtUpdateUser.setStringOrNull(3, mail)
125 stmtUpdateUser.setInt(4, id)
126 }
127 stmtUpdateUser.execute()
128 }
129 //</editor-fold>
131 //<editor-fold desc="Version">
132 private fun selectVersions(stmt: PreparedStatement) = sequence {
133 stmt.executeQuery().use { rs ->
134 while (rs.next()) {
135 yield(Version(rs.getInt("versionid"), rs.getInt("project")).apply {
136 name = rs.getString("name")
137 node = rs.getString("node")
138 ordinal = rs.getInt("ordinal")
139 status = rs.getEnum("status")
140 })
141 }
142 }
143 }
145 private fun setVersionFields(stmt: PreparedStatement, obj: Version): Int {
146 with(obj) {
147 stmt.setStringSafe(1, name)
148 stmt.setStringSafe(2, node)
149 stmt.setInt(3, ordinal)
150 stmt.setEnum(4, status)
151 }
152 return 5
153 }
155 //language=SQL
156 private val versionQuery = "select versionid, project, name, node, ordinal, status from lpit_version"
158 private val stmtVersions by lazy {
159 connection.prepareStatement(
160 """${versionQuery}
161 where project = ?
162 order by ordinal desc, lower(name) desc
163 """
164 )
165 }
166 private val stmtVersionByID by lazy {
167 connection.prepareStatement(
168 """${versionQuery}
169 where versionid = ?
170 """
171 )
172 }
173 private val stmtVersionByNode by lazy {
174 connection.prepareStatement(
175 """${versionQuery}
176 where project = ? and node = ?
177 """
178 )
179 }
180 private val stmtInsertVersion by lazy {
181 connection.prepareStatement(
182 """
183 insert into lpit_version (name, node, ordinal, status, project)
184 values (?, ?, ?, ?::version_status, ?)
185 """
186 )
187 }
188 private val stmtUpdateVersion by lazy {
189 connection.prepareStatement(
190 """
191 update lpit_version set name = ?, node = ?, ordinal = ?, status = ?::version_status
192 where versionid = ?
193 """
194 )
195 }
197 override fun listVersions(project: Project): List<Version> {
198 stmtVersions.setInt(1, project.id)
199 return selectVersions(stmtVersions).toList()
200 }
202 override fun findVersion(id: Int): Version? {
203 stmtVersionByID.setInt(1, id)
204 return selectVersions(stmtVersionByID).firstOrNull()
205 }
207 override fun findVersionByNode(project: Project, node: String): Version? {
208 stmtVersionByNode.setInt(1, project.id)
209 stmtVersionByNode.setString(2, node)
210 return selectVersions(stmtVersionByNode).firstOrNull()
211 }
213 override fun insertVersion(version: Version) {
214 val col = setVersionFields(stmtInsertVersion, version)
215 stmtInsertVersion.setInt(col, version.projectid)
216 stmtInsertVersion.execute()
217 }
219 override fun updateVersion(version: Version) {
220 val col = setVersionFields(stmtUpdateVersion, version)
221 stmtUpdateVersion.setInt(col, version.id)
222 stmtUpdateVersion.execute()
223 }
224 //</editor-fold>
226 //<editor-fold desc="Component">
227 private fun selectComponents(stmt: PreparedStatement) = sequence {
228 stmt.executeQuery().use { rs ->
229 while (rs.next()) {
230 yield(Component(rs.getInt("id"), rs.getInt("project")).apply {
231 name = rs.getString("name")
232 node = rs.getString("node")
233 color = try {
234 WebColor(rs.getString("color"))
235 } catch (ex: IllegalArgumentException) {
236 WebColor("000000")
237 }
238 ordinal = rs.getInt("ordinal")
239 description = rs.getString("description")
240 lead = selectUserInfo(rs)
241 })
242 }
243 }
244 }
246 private fun setComponentFields(stmt: PreparedStatement, obj: Component): Int {
247 with(obj) {
248 stmt.setStringSafe(1, name)
249 stmt.setStringSafe(2, node)
250 stmt.setStringSafe(3, color.hex)
251 stmt.setInt(4, ordinal)
252 stmt.setStringOrNull(5, description)
253 stmt.setIntOrNull(6, obj.lead?.id)
254 }
255 return 7
256 }
258 //language=SQL
259 private val componentQuery =
260 """
261 select id, project, name, node, color, ordinal, description,
262 userid, username, givenname, lastname, mail
263 from lpit_component
264 left join lpit_user on lead = userid
265 """
267 private val stmtComponents by lazy {
268 connection.prepareStatement(
269 """${componentQuery}
270 where project = ?
271 order by ordinal, lower(name)
272 """
273 )
274 }
275 private val stmtComponentById by lazy {
276 connection.prepareStatement(
277 """${componentQuery}
278 where id = ?
279 """
280 )
281 }
282 private val stmtComponentByNode by lazy {
283 connection.prepareStatement(
284 """${componentQuery}
285 where project = ? and node = ?
286 """
287 )
288 }
289 private val stmtInsertComponent by lazy {
290 connection.prepareStatement(
291 """
292 insert into lpit_component (name, node, color, ordinal, description, lead, project)
293 values (?, ?, ?, ?, ?, ?, ?)
294 """
295 )
296 }
297 private val stmtUpdateComponent by lazy {
298 connection.prepareStatement(
299 "update lpit_component set name = ?, node = ?, color = ?, ordinal = ?, description = ?, lead = ? where id = ?"
300 )
301 }
303 override fun listComponents(project: Project): List<Component> {
304 stmtComponents.setInt(1, project.id)
305 return selectComponents(stmtComponents).toList()
306 }
308 override fun findComponent(id: Int): Component? {
309 stmtComponentById.setInt(1, id)
310 return selectComponents(stmtComponentById).firstOrNull()
311 }
313 override fun findComponentByNode(project: Project, node: String): Component? {
314 stmtComponentByNode.setInt(1, project.id)
315 stmtComponentByNode.setString(2, node)
316 return selectComponents(stmtComponentByNode).firstOrNull()
317 }
319 override fun insertComponent(component: Component) {
320 val col = setComponentFields(stmtInsertComponent, component)
321 stmtInsertComponent.setInt(col, component.projectid)
322 stmtInsertComponent.execute()
323 }
325 override fun updateComponent(component: Component) {
326 val col = setComponentFields(stmtUpdateComponent, component)
327 stmtUpdateComponent.setInt(col, component.id)
328 stmtUpdateComponent.execute()
329 }
331 //</editor-fold>
333 //<editor-fold desc="Project">
335 private fun selectProjects(stmt: PreparedStatement) = sequence {
336 stmt.executeQuery().use { rs ->
337 while (rs.next()) {
338 yield(Project(rs.getInt("projectid")).apply {
339 name = rs.getString("name")
340 node = rs.getString("node")
341 ordinal = rs.getInt("ordinal")
342 description = rs.getString("description")
343 repoUrl = rs.getString("repourl")
344 owner = selectUserInfo(rs)
345 })
346 }
347 }
348 }
350 private fun setProjectFields(stmt: PreparedStatement, obj: Project): Int {
351 with(obj) {
352 stmt.setStringSafe(1, name)
353 stmt.setStringSafe(2, node)
354 stmt.setInt(3, ordinal)
355 stmt.setStringOrNull(4, description)
356 stmt.setStringOrNull(5, repoUrl)
357 stmt.setIntOrNull(6, owner?.id)
358 }
359 return 7
360 }
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 """
371 private val stmtProjects by lazy {
372 connection.prepareStatement(
373 """${projectQuery}
374 order by ordinal, lower(name)
375 """
376 )
377 }
378 private val stmtProjectByID by lazy {
379 connection.prepareStatement(
380 """${projectQuery}
381 where projectid = ?
382 """
383 )
384 }
385 private val stmtProjectByNode by lazy {
386 connection.prepareStatement(
387 """${projectQuery}
388 where node = ?
389 """
390 )
391 }
392 private val stmtInsertProject by lazy {
393 connection.prepareStatement(
394 "insert into lpit_project (name, node, ordinal, description, repourl, owner) values (?, ?, ?, ?, ?, ?)"
395 )
396 }
397 private val stmtUpdateProject by lazy {
398 connection.prepareStatement(
399 "update lpit_project set name = ?, node = ?, ordinal = ?, description = ?, repourl = ?, owner = ? where projectid = ?"
400 )
401 }
402 private val stmtIssueSummary by lazy {
403 connection.prepareStatement(
404 """
405 select phase, count(*) as total
406 from lpit_issue
407 join lpit_issue_phases using(status)
408 where project = ?
409 group by phase
410 """
411 )
412 }
414 override fun listProjects(): List<Project> {
415 return selectProjects(stmtProjects).toList()
416 }
418 override fun findProject(id: Int): Project? {
419 stmtProjectByID.setInt(1, id)
420 return selectProjects(stmtProjectByID).firstOrNull()
421 }
423 override fun findProjectByNode(node: String): Project? {
424 stmtProjectByNode.setString(1, node)
425 return selectProjects(stmtProjectByNode).firstOrNull()
426 }
428 override fun insertProject(project: Project) {
429 setProjectFields(stmtInsertProject, project)
430 stmtInsertProject.execute()
431 }
433 override fun updateProject(project: Project) {
434 val col = setProjectFields(stmtUpdateProject, project)
435 stmtUpdateProject.setInt(col, project.id)
436 stmtUpdateProject.execute()
437 }
439 override fun collectIssueSummary(project: Project): IssueSummary {
440 stmtIssueSummary.setInt(1, project.id)
441 return stmtIssueSummary.executeQuery().use { rs ->
442 val summary = IssueSummary()
443 while (rs.next()) {
444 val phase = rs.getInt("phase")
445 val total = rs.getInt("total")
446 when (phase) {
447 0 -> summary.open = total
448 1 -> summary.active = total
449 2 -> summary.done = total
450 }
451 }
452 summary
453 }
454 }
456 //</editor-fold>
458 //<editor-fold desc="Issue">
460 private fun selectIssues(stmt: PreparedStatement) = sequence {
461 stmt.executeQuery().use { rs ->
462 while (rs.next()) {
463 val proj = Project(rs.getInt("project")).apply {
464 name = rs.getString("projectname")
465 node = rs.getString("projectnode")
466 }
467 val comp = rs.getInt("component").let {
468 if (rs.wasNull()) null else
469 Component(it, proj.id).apply {
470 name = rs.getString("componentname")
471 node = rs.getString("componentnode")
472 }
473 }
474 val issue = Issue(rs.getInt("issueid"), proj, comp).apply {
475 component = comp
476 status = rs.getEnum("status")
477 category = rs.getEnum("category")
478 subject = rs.getString("subject")
479 description = rs.getString("description")
480 assignee = selectUserInfo(rs)
481 created = rs.getTimestamp("created")
482 updated = rs.getTimestamp("updated")
483 eta = rs.getDate("eta")
484 }
485 queryAffectedVersions.setInt(1, issue.id)
486 issue.affectedVersions = selectVersions(queryAffectedVersions).toList()
487 queryResolvedVersions.setInt(1, issue.id)
488 issue.resolvedVersions = selectVersions(queryResolvedVersions).toList()
489 yield(issue)
490 }
491 }
492 }
494 private fun setIssueFields(stmt: PreparedStatement, obj: Issue): Int {
495 with(obj) {
496 stmt.setIntOrNull(1, component?.id)
497 stmt.setEnum(2, status)
498 stmt.setEnum(3, category)
499 stmt.setStringSafe(4, subject)
500 stmt.setStringOrNull(5, description)
501 stmt.setIntOrNull(6, assignee?.id)
502 stmt.setDateOrNull(7, eta)
503 }
504 return 8
505 }
507 //language=SQL
508 private val issueQuery =
509 """
510 select issueid,
511 i.project, p.name as projectname, p.node as projectnode,
512 component, c.name as componentname, c.node as componentnode,
513 status, category, subject, i.description,
514 userid, username, givenname, lastname, mail,
515 created, updated, eta
516 from lpit_issue i
517 join lpit_project p on i.project = projectid
518 left join lpit_component c on component = c.id
519 left join lpit_user on userid = assignee
520 """
522 private val queryResolvedVersions by lazy {
523 connection.prepareStatement(
524 """
525 select versionid, project, name, status, ordinal, node
526 from lpit_version v join lpit_issue_resolved_version using (versionid)
527 where issueid = ?
528 order by ordinal, name
529 """
530 )
531 }
533 private val queryAffectedVersions by lazy {
534 connection.prepareStatement(
535 """
536 select versionid, project, name, status, ordinal, node
537 from lpit_version join lpit_issue_affected_version using (versionid)
538 where issueid = ?
539 order by ordinal, name
540 """
541 )
542 }
544 private val stmtIssues by lazy {
545 connection.prepareStatement(
546 """
547 with issue_version as (
548 select issueid, versionid from lpit_issue_affected_version
549 union select issueid, versionid from lpit_issue_resolved_version
550 ) ${issueQuery} left join issue_version using (issueid)
551 where
552 (not ? or projectid = ?) and
553 (not ? or versionid = ?) and (not ? or versionid is null) and
554 (not ? or component = ?) and (not ? or component is null)
555 """
556 )
557 }
559 private val fproj = 1
560 private val projectid = 2
561 private val fversion = 3
562 private val versionid = 4
563 private val nversion = 5
564 private val fcomp = 6
565 private val component = 7
566 private val ncomp = 8
568 private fun <T : Entity> applyFilter(filter: Filter<T>, fflag: Int, nflag: Int, idcol: Int) {
569 when (filter) {
570 is AllFilter -> {
571 stmtIssues.setBoolean(fflag, false)
572 stmtIssues.setBoolean(nflag, false)
573 stmtIssues.setInt(idcol, 0)
574 }
575 is NoneFilter -> {
576 stmtIssues.setBoolean(fflag, false)
577 stmtIssues.setBoolean(nflag, true)
578 stmtIssues.setInt(idcol, 0)
579 }
580 is SpecificFilter -> {
581 stmtIssues.setBoolean(fflag, true)
582 stmtIssues.setBoolean(nflag, false)
583 stmtIssues.setInt(idcol, filter.obj.id)
584 }
585 else -> {
586 TODO("Implement range filter.")
587 }
588 }
589 }
591 override fun listIssues(filter: IssueFilter): List<Issue> {
592 when (filter.project) {
593 is AllFilter -> {
594 stmtIssues.setBoolean(fproj, false)
595 stmtIssues.setInt(projectid, 0)
596 }
597 is SpecificFilter -> {
598 stmtIssues.setBoolean(fproj, true)
599 stmtIssues.setInt(projectid, filter.project.obj.id)
600 }
601 else -> throw IllegalArgumentException()
602 }
603 applyFilter(filter.version, fversion, nversion, versionid)
604 applyFilter(filter.component, fcomp, ncomp, component)
606 return selectIssues(stmtIssues).toList()
607 }
609 private val stmtFindIssueByID by lazy {
610 connection.prepareStatement(
611 """${issueQuery}
612 where issueid = ?
613 """
614 )
615 }
616 private val stmtInsertIssue by lazy {
617 connection.prepareStatement(
618 """
619 insert into lpit_issue (component, status, category, subject, description, assignee, eta, project)
620 values (?, ?::issue_status, ?::issue_category, ?, ?, ?, ?, ?)
621 returning issueid
622 """
623 )
624 }
625 private val stmtUpdateIssue by lazy {
626 connection.prepareStatement(
627 """
628 update lpit_issue set updated = now(),
629 component = ?, status = ?::issue_status, category = ?::issue_category, subject = ?,
630 description = ?, assignee = ?, eta = ?
631 where issueid = ?
632 """
633 )
634 }
635 private val stmtInsertAffectedVersion by lazy {
636 connection.prepareStatement(
637 "insert into lpit_issue_affected_version (issueid, versionid) values (?,?)"
638 )
639 }
640 private val stmtInsertResolvedVersion by lazy {
641 connection.prepareStatement(
642 "insert into lpit_issue_resolved_version (issueid, versionid) values (?,?)"
643 )
644 }
645 private val stmtClearAffectedVersions by lazy {
646 connection.prepareStatement("delete from lpit_issue_affected_version where issueid = ?")
647 }
648 private val stmtClearResolvedVersions by lazy {
649 connection.prepareStatement("delete from lpit_issue_resolved_version where issueid = ?")
650 }
652 override fun findIssue(id: Int): Issue? {
653 stmtFindIssueByID.setInt(1, id)
654 return selectIssues(stmtFindIssueByID).firstOrNull()
655 }
657 private fun insertVersionInfo(issue: Issue) {
658 stmtInsertAffectedVersion.setInt(1, issue.id)
659 stmtInsertResolvedVersion.setInt(1, issue.id)
660 issue.affectedVersions.forEach {
661 stmtInsertAffectedVersion.setInt(2, it.id)
662 stmtInsertAffectedVersion.execute()
663 }
664 issue.resolvedVersions.forEach {
665 stmtInsertResolvedVersion.setInt(2, it.id)
666 stmtInsertResolvedVersion.execute()
667 }
668 }
670 override fun insertIssue(issue: Issue) {
671 val col = setIssueFields(stmtInsertIssue, issue)
672 stmtInsertIssue.setInt(col, issue.project.id)
673 stmtInsertIssue.executeQuery().use { rs ->
674 rs.next()
675 issue.id = rs.getInt(1)
676 }
677 insertVersionInfo(issue)
678 }
680 override fun updateIssue(issue: Issue) {
681 val col = setIssueFields(stmtUpdateIssue, issue)
682 stmtUpdateIssue.setInt(col, issue.id)
683 stmtUpdateIssue.execute()
684 // TODO: improve by only inserting / deleting changed version information
685 stmtClearAffectedVersions.setInt(1, issue.id)
686 stmtClearResolvedVersions.setInt(1, issue.id)
687 stmtClearAffectedVersions.execute()
688 stmtClearResolvedVersions.execute()
689 insertVersionInfo(issue)
690 }
692 //</editor-fold>
694 //<editor-fold desc="IssueComment">
696 private fun selectComments(stmt: PreparedStatement) = sequence {
697 stmt.executeQuery().use { rs ->
698 while (rs.next()) {
699 yield(IssueComment(rs.getInt("commentid"), rs.getInt("issueid")).apply {
700 created = rs.getTimestamp("created")
701 updated = rs.getTimestamp("updated")
702 updateCount = rs.getInt("updatecount")
703 comment = rs.getString("comment")
704 author = selectUserInfo(rs)
705 })
706 }
707 }
708 }
710 private val stmtComments by lazy {
711 connection.prepareStatement(
712 "select * from lpit_issue_comment left join lpit_user using (userid) where issueid = ? order by created"
713 )
714 }
715 private val stmtInsertComment by lazy {
716 connection.prepareStatement(
717 "insert into lpit_issue_comment (issueid, comment, userid) values (?, ? ,?)"
718 )
719 }
720 private val stmtUpdateIssueDate by lazy {
721 connection.prepareStatement(
722 "update lpit_issue set updated = now() where issueid = ?"
723 )
724 }
726 override fun listComments(issue: Issue): List<IssueComment> {
727 stmtComments.setInt(1, issue.id)
728 return selectComments(stmtComments).toList()
729 }
731 override fun insertComment(issueComment: IssueComment) {
732 with(issueComment) {
733 stmtUpdateIssueDate.setInt(1, issueid)
734 stmtInsertComment.setInt(1, issueid)
735 stmtInsertComment.setStringSafe(2, comment)
736 stmtInsertComment.setIntOrNull(3, author?.id)
737 }
738 stmtInsertComment.execute()
739 stmtUpdateIssueDate.execute()
740 }
741 //</editor-fold>
742 }