src/main/kotlin/de/uapcore/lightpit/dao/PostgresDataAccessObject.kt@2979436edd9e
src/main/kotlin/de/uapcore/lightpit/dao/PostgresDataAccessObject.kt
Tue, 06 Apr 2021 09:08:54 +0200
- author
- Mike Becker <universe@uap-core.de>
- date
- Tue, 06 Apr 2021 09:08:54 +0200
- changeset 188
- 2979436edd9e
- parent 184
- e8eecee6aadf
- child 189
- f7de8158b41c
- permissions
- -rw-r--r--
fixes insertVersionInfo not using inserted issue id
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 java.sql.Connection
35 import java.sql.PreparedStatement
36 import java.sql.ResultSet
38 class PostgresDataAccessObject(private val connection: Connection) : DataAccessObject {
40 //<editor-fold desc="User">
41 private fun selectUserInfo(
42 rs: ResultSet,
43 idColumn: String = "userid",
44 usernameColumn: String = "username",
45 givennameColumn: String = "givenname",
46 lastnameColumn: String = "lastname",
47 mailColumn: String = "mail"
48 ): User? {
49 val idval = rs.getInt(idColumn)
50 return if (rs.wasNull()) null else {
51 User(idval).apply {
52 username = rs.getString(usernameColumn)
53 givenname = rs.getString(givennameColumn)
54 lastname = rs.getString(lastnameColumn)
55 mail = rs.getString(mailColumn)
56 }
57 }
58 }
60 private fun selectUsers(stmt: PreparedStatement) = sequence {
61 stmt.executeQuery().use { rs ->
62 while (rs.next()) selectUserInfo(rs)?.let { yield(it) }
63 }
64 }
66 //language=SQL
67 private val userQuery = "select userid, username, lastname, givenname, mail from lpit_user"
69 private val stmtUsers by lazy {
70 connection.prepareStatement(
71 """${userQuery}
72 where userid > 0
73 order by username
74 """
75 )
76 }
77 private val stmtUserByID by lazy {
78 connection.prepareStatement(
79 """${userQuery}
80 where userid = ?
81 """
82 )
83 }
84 private val stmtUserByName by lazy {
85 connection.prepareStatement(
86 """${userQuery}
87 where lower(username) = lower(?)
88 """
89 )
90 }
91 private val stmtInsertUser by lazy {
92 connection.prepareStatement(
93 "insert into lpit_user (username, lastname, givenname, mail) values (?, ?, ?, ?)"
94 )
95 }
96 private val stmtUpdateUser by lazy {
97 connection.prepareStatement(
98 "update lpit_user set lastname = ?, givenname = ?, mail = ? where userid = ?"
99 )
100 }
102 override fun listUsers() = selectUsers(stmtUsers).toList()
103 override fun findUser(id: Int): User? {
104 stmtUserByID.setInt(1, id)
105 return selectUsers(stmtUserByID).firstOrNull()
106 }
108 override fun findUserByName(username: String): User? {
109 stmtUserByName.setString(1, username)
110 return selectUsers(stmtUserByName).firstOrNull()
111 }
113 override fun insertUser(user: User) {
114 with(user) {
115 stmtInsertUser.setStringSafe(1, username)
116 stmtInsertUser.setStringOrNull(2, lastname)
117 stmtInsertUser.setStringOrNull(3, givenname)
118 stmtInsertUser.setStringOrNull(4, mail)
119 }
120 stmtInsertUser.execute()
121 }
123 override fun updateUser(user: User) {
124 with(user) {
125 stmtUpdateUser.setStringOrNull(1, lastname)
126 stmtUpdateUser.setStringOrNull(2, givenname)
127 stmtUpdateUser.setStringOrNull(3, mail)
128 stmtUpdateUser.setInt(4, id)
129 }
130 stmtUpdateUser.execute()
131 }
132 //</editor-fold>
134 //<editor-fold desc="Version">
136 private fun obtainVersion(rs: ResultSet) =
137 Version(rs.getInt("versionid"), rs.getInt("project")).apply {
138 name = rs.getString("name")
139 node = rs.getString("node")
140 ordinal = rs.getInt("ordinal")
141 status = rs.getEnum("status")
142 }
144 private fun selectVersions(stmt: PreparedStatement) = sequence {
145 stmt.executeQuery().use { rs ->
146 while (rs.next()) {
147 yield(obtainVersion(rs))
148 }
149 }
150 }
152 private fun setVersionFields(stmt: PreparedStatement, obj: Version): Int {
153 with(obj) {
154 stmt.setStringSafe(1, name)
155 stmt.setStringSafe(2, node)
156 stmt.setInt(3, ordinal)
157 stmt.setEnum(4, status)
158 }
159 return 5
160 }
162 //language=SQL
163 private val versionQuery = "select versionid, project, name, node, ordinal, status from lpit_version"
165 private val stmtVersions by lazy {
166 connection.prepareStatement(
167 """${versionQuery}
168 where project = ?
169 order by ordinal desc, lower(name) desc
170 """
171 )
172 }
173 private val stmtVersionSummaries by lazy {
174 connection.prepareStatement(
175 """
176 with version_map(issueid, versionid, isresolved) as (
177 select issueid, versionid, 1
178 from lpit_issue_resolved_version
179 union
180 select issueid, versionid, 0
181 from lpit_issue_affected_version
182 ),
183 issues as (
184 select versionid, phase, isresolved, count(issueid) as total
185 from lpit_issue
186 join version_map using (issueid)
187 join lpit_issue_phases using (status)
188 group by versionid, phase, isresolved
189 ),
190 summary as (
191 select versionid, phase, isresolved, total
192 from lpit_version v
193 left join issues using (versionid)
194 where v.project = ?
195 )
196 select versionid, project, name, node, ordinal, status, phase, isresolved, total
197 from lpit_version
198 join summary using (versionid)
199 order by ordinal, name
200 """
201 )
202 }
203 private val stmtVersionByID by lazy {
204 connection.prepareStatement(
205 """${versionQuery}
206 where versionid = ?
207 """
208 )
209 }
210 private val stmtVersionByNode by lazy {
211 connection.prepareStatement(
212 """${versionQuery}
213 where project = ? and node = ?
214 """
215 )
216 }
217 private val stmtInsertVersion by lazy {
218 connection.prepareStatement(
219 """
220 insert into lpit_version (name, node, ordinal, status, project)
221 values (?, ?, ?, ?::version_status, ?)
222 """
223 )
224 }
225 private val stmtUpdateVersion by lazy {
226 connection.prepareStatement(
227 """
228 update lpit_version set name = ?, node = ?, ordinal = ?, status = ?::version_status
229 where versionid = ?
230 """
231 )
232 }
234 override fun listVersions(project: Project): List<Version> {
235 stmtVersions.setInt(1, project.id)
236 return selectVersions(stmtVersions).toList()
237 }
239 override fun listVersionSummaries(project: Project): List<VersionSummary> {
240 stmtVersionSummaries.setInt(1, project.id)
241 return sequence {
242 stmtVersionSummaries.executeQuery().use { rs ->
243 while (rs.next()) {
244 val versionSummary = VersionSummary(obtainVersion(rs))
245 val phase = rs.getInt("phase")
246 val total = rs.getInt("total")
247 val issueSummary =
248 if (rs.getBoolean("isresolved")) versionSummary.resolvedTotal else versionSummary.reportedTotal
249 when (phase) {
250 0 -> issueSummary.open = total
251 1 -> issueSummary.active = total
252 2 -> issueSummary.done = total
253 }
254 yield(versionSummary)
255 }
256 }
257 }.toList()
258 }
260 override fun findVersion(id: Int): Version? {
261 stmtVersionByID.setInt(1, id)
262 return selectVersions(stmtVersionByID).firstOrNull()
263 }
265 override fun findVersionByNode(project: Project, node: String): Version? {
266 stmtVersionByNode.setInt(1, project.id)
267 stmtVersionByNode.setString(2, node)
268 return selectVersions(stmtVersionByNode).firstOrNull()
269 }
271 override fun insertVersion(version: Version) {
272 val col = setVersionFields(stmtInsertVersion, version)
273 stmtInsertVersion.setInt(col, version.projectid)
274 stmtInsertVersion.execute()
275 }
277 override fun updateVersion(version: Version) {
278 val col = setVersionFields(stmtUpdateVersion, version)
279 stmtUpdateVersion.setInt(col, version.id)
280 stmtUpdateVersion.execute()
281 }
282 //</editor-fold>
284 //<editor-fold desc="Component">
286 private fun obtainComponent(rs: ResultSet): Component =
287 Component(rs.getInt("id"), rs.getInt("project")).apply {
288 name = rs.getString("name")
289 node = rs.getString("node")
290 color = try {
291 WebColor(rs.getString("color"))
292 } catch (ex: IllegalArgumentException) {
293 WebColor("000000")
294 }
295 ordinal = rs.getInt("ordinal")
296 description = rs.getString("description")
297 lead = selectUserInfo(rs)
298 }
300 private fun selectComponents(stmt: PreparedStatement) = sequence {
301 stmt.executeQuery().use { rs ->
302 while (rs.next()) {
303 yield(obtainComponent(rs))
304 }
305 }
306 }
308 private fun setComponentFields(stmt: PreparedStatement, obj: Component): Int {
309 with(obj) {
310 stmt.setStringSafe(1, name)
311 stmt.setStringSafe(2, node)
312 stmt.setStringSafe(3, color.hex)
313 stmt.setInt(4, ordinal)
314 stmt.setStringOrNull(5, description)
315 stmt.setIntOrNull(6, obj.lead?.id)
316 }
317 return 7
318 }
320 //language=SQL
321 private val componentQuery =
322 """
323 select id, project, name, node, color, ordinal, description,
324 userid, username, givenname, lastname, mail
325 from lpit_component
326 left join lpit_user on lead = userid
327 """
329 private val stmtComponents by lazy {
330 connection.prepareStatement(
331 """${componentQuery}
332 where project = ?
333 order by ordinal, lower(name)
334 """
335 )
336 }
337 private val stmtComponentSummaries by lazy {
338 connection.prepareStatement(
339 """
340 with issues as (
341 select component, phase, count(issueid) as total
342 from lpit_issue
343 join lpit_issue_phases using (status)
344 group by component, phase
345 ),
346 summary as (
347 select c.id, phase, total
348 from lpit_component c
349 left join issues i on c.id = i.component
350 where c.project = ?
351 )
352 select c.id, project, name, node, color, ordinal, description,
353 userid, username, givenname, lastname, mail, phase, total
354 from lpit_component c
355 left join lpit_user on lead = userid
356 join summary s on c.id = s.id
357 order by ordinal, name
358 """
359 )
360 }
361 private val stmtComponentById by lazy {
362 connection.prepareStatement(
363 """${componentQuery}
364 where id = ?
365 """
366 )
367 }
368 private val stmtComponentByNode by lazy {
369 connection.prepareStatement(
370 """${componentQuery}
371 where project = ? and node = ?
372 """
373 )
374 }
375 private val stmtInsertComponent by lazy {
376 connection.prepareStatement(
377 """
378 insert into lpit_component (name, node, color, ordinal, description, lead, project)
379 values (?, ?, ?, ?, ?, ?, ?)
380 """
381 )
382 }
383 private val stmtUpdateComponent by lazy {
384 connection.prepareStatement(
385 "update lpit_component set name = ?, node = ?, color = ?, ordinal = ?, description = ?, lead = ? where id = ?"
386 )
387 }
389 override fun listComponents(project: Project): List<Component> {
390 stmtComponents.setInt(1, project.id)
391 return selectComponents(stmtComponents).toList()
392 }
394 override fun listComponentSummaries(project: Project): List<ComponentSummary> {
395 stmtComponentSummaries.setInt(1, project.id)
396 return sequence {
397 stmtComponentSummaries.executeQuery().use { rs ->
398 while (rs.next()) {
399 val componentSummary = ComponentSummary(obtainComponent(rs))
400 val phase = rs.getInt("phase")
401 val total = rs.getInt("total")
402 when (phase) {
403 0 -> componentSummary.issueSummary.open = total
404 1 -> componentSummary.issueSummary.active = total
405 2 -> componentSummary.issueSummary.done = total
406 }
407 yield(componentSummary)
408 }
409 }
410 }.toList()
411 }
413 override fun findComponent(id: Int): Component? {
414 stmtComponentById.setInt(1, id)
415 return selectComponents(stmtComponentById).firstOrNull()
416 }
418 override fun findComponentByNode(project: Project, node: String): Component? {
419 stmtComponentByNode.setInt(1, project.id)
420 stmtComponentByNode.setString(2, node)
421 return selectComponents(stmtComponentByNode).firstOrNull()
422 }
424 override fun insertComponent(component: Component) {
425 val col = setComponentFields(stmtInsertComponent, component)
426 stmtInsertComponent.setInt(col, component.projectid)
427 stmtInsertComponent.execute()
428 }
430 override fun updateComponent(component: Component) {
431 val col = setComponentFields(stmtUpdateComponent, component)
432 stmtUpdateComponent.setInt(col, component.id)
433 stmtUpdateComponent.execute()
434 }
436 //</editor-fold>
438 //<editor-fold desc="Project">
440 private fun selectProjects(stmt: PreparedStatement) = sequence {
441 stmt.executeQuery().use { rs ->
442 while (rs.next()) {
443 yield(Project(rs.getInt("projectid")).apply {
444 name = rs.getString("name")
445 node = rs.getString("node")
446 ordinal = rs.getInt("ordinal")
447 description = rs.getString("description")
448 repoUrl = rs.getString("repourl")
449 owner = selectUserInfo(rs)
450 })
451 }
452 }
453 }
455 private fun setProjectFields(stmt: PreparedStatement, obj: Project): Int {
456 with(obj) {
457 stmt.setStringSafe(1, name)
458 stmt.setStringSafe(2, node)
459 stmt.setInt(3, ordinal)
460 stmt.setStringOrNull(4, description)
461 stmt.setStringOrNull(5, repoUrl)
462 stmt.setIntOrNull(6, owner?.id)
463 }
464 return 7
465 }
467 //language=SQL
468 private val projectQuery =
469 """
470 select projectid, name, node, ordinal, description, repourl,
471 userid, username, lastname, givenname, mail
472 from lpit_project
473 left join lpit_user owner on lpit_project.owner = owner.userid
474 """
476 private val stmtProjects by lazy {
477 connection.prepareStatement(
478 """${projectQuery}
479 order by ordinal, lower(name)
480 """
481 )
482 }
483 private val stmtProjectByID by lazy {
484 connection.prepareStatement(
485 """${projectQuery}
486 where projectid = ?
487 """
488 )
489 }
490 private val stmtProjectByNode by lazy {
491 connection.prepareStatement(
492 """${projectQuery}
493 where node = ?
494 """
495 )
496 }
497 private val stmtInsertProject by lazy {
498 connection.prepareStatement(
499 "insert into lpit_project (name, node, ordinal, description, repourl, owner) values (?, ?, ?, ?, ?, ?)"
500 )
501 }
502 private val stmtUpdateProject by lazy {
503 connection.prepareStatement(
504 "update lpit_project set name = ?, node = ?, ordinal = ?, description = ?, repourl = ?, owner = ? where projectid = ?"
505 )
506 }
507 private val stmtIssueSummary by lazy {
508 connection.prepareStatement(
509 """
510 select phase, count(*) as total
511 from lpit_issue
512 join lpit_issue_phases using(status)
513 where project = ?
514 group by phase
515 """
516 )
517 }
519 override fun listProjects(): List<Project> {
520 return selectProjects(stmtProjects).toList()
521 }
523 override fun findProject(id: Int): Project? {
524 stmtProjectByID.setInt(1, id)
525 return selectProjects(stmtProjectByID).firstOrNull()
526 }
528 override fun findProjectByNode(node: String): Project? {
529 stmtProjectByNode.setString(1, node)
530 return selectProjects(stmtProjectByNode).firstOrNull()
531 }
533 override fun insertProject(project: Project) {
534 setProjectFields(stmtInsertProject, project)
535 stmtInsertProject.execute()
536 }
538 override fun updateProject(project: Project) {
539 val col = setProjectFields(stmtUpdateProject, project)
540 stmtUpdateProject.setInt(col, project.id)
541 stmtUpdateProject.execute()
542 }
544 override fun collectIssueSummary(project: Project): IssueSummary {
545 stmtIssueSummary.setInt(1, project.id)
546 return stmtIssueSummary.executeQuery().use { rs ->
547 val summary = IssueSummary()
548 while (rs.next()) {
549 val phase = rs.getInt("phase")
550 val total = rs.getInt("total")
551 when (phase) {
552 0 -> summary.open = total
553 1 -> summary.active = total
554 2 -> summary.done = total
555 }
556 }
557 summary
558 }
559 }
561 //</editor-fold>
563 //<editor-fold desc="Issue">
565 private fun selectIssues(stmt: PreparedStatement) = sequence {
566 stmt.executeQuery().use { rs ->
567 while (rs.next()) {
568 val proj = Project(rs.getInt("project")).apply {
569 name = rs.getString("projectname")
570 node = rs.getString("projectnode")
571 }
572 val comp = rs.getInt("component").let {
573 if (rs.wasNull()) null else
574 Component(it, proj.id).apply {
575 name = rs.getString("componentname")
576 node = rs.getString("componentnode")
577 }
578 }
579 val issue = Issue(rs.getInt("issueid"), proj).apply {
580 component = comp
581 status = rs.getEnum("status")
582 category = rs.getEnum("category")
583 subject = rs.getString("subject")
584 description = rs.getString("description")
585 assignee = selectUserInfo(rs)
586 created = rs.getTimestamp("created")
587 updated = rs.getTimestamp("updated")
588 eta = rs.getDate("eta")
589 }
590 queryAffectedVersions.setInt(1, issue.id)
591 issue.affectedVersions = selectVersions(queryAffectedVersions).toList()
592 queryResolvedVersions.setInt(1, issue.id)
593 issue.resolvedVersions = selectVersions(queryResolvedVersions).toList()
594 yield(issue)
595 }
596 }
597 }
599 private fun setIssueFields(stmt: PreparedStatement, obj: Issue): Int {
600 with(obj) {
601 stmt.setIntOrNull(1, component?.id)
602 stmt.setEnum(2, status)
603 stmt.setEnum(3, category)
604 stmt.setStringSafe(4, subject)
605 stmt.setStringOrNull(5, description)
606 stmt.setIntOrNull(6, assignee?.id)
607 stmt.setDateOrNull(7, eta)
608 }
609 return 8
610 }
612 //language=SQL
613 private val issueQuery =
614 """
615 select issueid,
616 i.project, p.name as projectname, p.node as projectnode,
617 component, c.name as componentname, c.node as componentnode,
618 status, category, subject, i.description,
619 userid, username, givenname, lastname, mail,
620 created, updated, eta
621 from lpit_issue i
622 join lpit_project p on i.project = projectid
623 left join lpit_component c on component = c.id
624 left join lpit_user on userid = assignee
625 """
627 private val queryResolvedVersions by lazy {
628 connection.prepareStatement(
629 """
630 select versionid, project, name, status, ordinal, node
631 from lpit_version v join lpit_issue_resolved_version using (versionid)
632 where issueid = ?
633 order by ordinal, name
634 """
635 )
636 }
638 private val queryAffectedVersions by lazy {
639 connection.prepareStatement(
640 """
641 select versionid, project, name, status, ordinal, node
642 from lpit_version join lpit_issue_affected_version using (versionid)
643 where issueid = ?
644 order by ordinal, name
645 """
646 )
647 }
649 private val stmtIssues by lazy {
650 connection.prepareStatement(
651 """
652 with issue_version as (
653 select issueid, versionid from lpit_issue_affected_version
654 union select issueid, versionid from lpit_issue_resolved_version
655 ),
656 filteterd_issues as (
657 select distinct issueid from lpit_issue
658 left join issue_version using (issueid)
659 where
660 (not ? or project = ?) and
661 (not ? or versionid = ?) and (not ? or versionid is null) and
662 (not ? or component = ?) and (not ? or component is null)
663 )
664 ${issueQuery} join filteterd_issues using (issueid)
665 """
666 )
667 }
669 private val fproj = 1
670 private val projectid = 2
671 private val fversion = 3
672 private val versionid = 4
673 private val nversion = 5
674 private val fcomp = 6
675 private val component = 7
676 private val ncomp = 8
678 private fun <T : Entity> applyFilter(filter: Filter<T>, fflag: Int, nflag: Int, idcol: Int) {
679 when (filter) {
680 is AllFilter -> {
681 stmtIssues.setBoolean(fflag, false)
682 stmtIssues.setBoolean(nflag, false)
683 stmtIssues.setInt(idcol, 0)
684 }
685 is NoneFilter -> {
686 stmtIssues.setBoolean(fflag, false)
687 stmtIssues.setBoolean(nflag, true)
688 stmtIssues.setInt(idcol, 0)
689 }
690 is SpecificFilter -> {
691 stmtIssues.setBoolean(fflag, true)
692 stmtIssues.setBoolean(nflag, false)
693 stmtIssues.setInt(idcol, filter.obj.id)
694 }
695 else -> {
696 TODO("Implement range filter.")
697 }
698 }
699 }
701 override fun listIssues(filter: IssueFilter): List<Issue> {
702 when (filter.project) {
703 is AllFilter -> {
704 stmtIssues.setBoolean(fproj, false)
705 stmtIssues.setInt(projectid, 0)
706 }
707 is SpecificFilter -> {
708 stmtIssues.setBoolean(fproj, true)
709 stmtIssues.setInt(projectid, filter.project.obj.id)
710 }
711 else -> throw IllegalArgumentException()
712 }
713 applyFilter(filter.version, fversion, nversion, versionid)
714 applyFilter(filter.component, fcomp, ncomp, component)
716 return selectIssues(stmtIssues).toList()
717 }
719 private val stmtFindIssueByID by lazy {
720 connection.prepareStatement(
721 """${issueQuery}
722 where issueid = ?
723 """
724 )
725 }
726 private val stmtInsertIssue by lazy {
727 connection.prepareStatement(
728 """
729 insert into lpit_issue (component, status, category, subject, description, assignee, eta, project)
730 values (?, ?::issue_status, ?::issue_category, ?, ?, ?, ?, ?)
731 returning issueid
732 """
733 )
734 }
735 private val stmtUpdateIssue by lazy {
736 connection.prepareStatement(
737 """
738 update lpit_issue set updated = now(),
739 component = ?, status = ?::issue_status, category = ?::issue_category, subject = ?,
740 description = ?, assignee = ?, eta = ?
741 where issueid = ?
742 """
743 )
744 }
745 private val stmtInsertAffectedVersion by lazy {
746 connection.prepareStatement(
747 "insert into lpit_issue_affected_version (issueid, versionid) values (?,?)"
748 )
749 }
750 private val stmtInsertResolvedVersion by lazy {
751 connection.prepareStatement(
752 "insert into lpit_issue_resolved_version (issueid, versionid) values (?,?)"
753 )
754 }
755 private val stmtClearAffectedVersions by lazy {
756 connection.prepareStatement("delete from lpit_issue_affected_version where issueid = ?")
757 }
758 private val stmtClearResolvedVersions by lazy {
759 connection.prepareStatement("delete from lpit_issue_resolved_version where issueid = ?")
760 }
762 override fun findIssue(id: Int): Issue? {
763 stmtFindIssueByID.setInt(1, id)
764 return selectIssues(stmtFindIssueByID).firstOrNull()
765 }
767 private fun insertVersionInfo(id: Int, issue: Issue) {
768 stmtInsertAffectedVersion.setInt(1, id)
769 stmtInsertResolvedVersion.setInt(1, id)
770 issue.affectedVersions.forEach {
771 stmtInsertAffectedVersion.setInt(2, it.id)
772 stmtInsertAffectedVersion.execute()
773 }
774 issue.resolvedVersions.forEach {
775 stmtInsertResolvedVersion.setInt(2, it.id)
776 stmtInsertResolvedVersion.execute()
777 }
778 }
780 override fun insertIssue(issue: Issue): Int {
781 val col = setIssueFields(stmtInsertIssue, issue)
782 stmtInsertIssue.setInt(col, issue.project.id)
783 val id = stmtInsertIssue.executeQuery().use { rs ->
784 rs.next()
785 rs.getInt(1)
786 }
787 insertVersionInfo(id, issue)
788 return id
789 }
791 override fun updateIssue(issue: Issue) {
792 val col = setIssueFields(stmtUpdateIssue, issue)
793 stmtUpdateIssue.setInt(col, issue.id)
794 stmtUpdateIssue.execute()
795 // TODO: improve by only inserting / deleting changed version information
796 stmtClearAffectedVersions.setInt(1, issue.id)
797 stmtClearResolvedVersions.setInt(1, issue.id)
798 stmtClearAffectedVersions.execute()
799 stmtClearResolvedVersions.execute()
800 insertVersionInfo(issue.id, issue)
801 }
803 //</editor-fold>
805 //<editor-fold desc="IssueComment">
807 private fun selectComments(stmt: PreparedStatement) = sequence {
808 stmt.executeQuery().use { rs ->
809 while (rs.next()) {
810 yield(IssueComment(rs.getInt("commentid"), rs.getInt("issueid")).apply {
811 created = rs.getTimestamp("created")
812 updated = rs.getTimestamp("updated")
813 updateCount = rs.getInt("updatecount")
814 comment = rs.getString("comment")
815 author = selectUserInfo(rs)
816 })
817 }
818 }
819 }
821 private val stmtComments by lazy {
822 connection.prepareStatement(
823 "select * from lpit_issue_comment left join lpit_user using (userid) where issueid = ? order by created"
824 )
825 }
826 private val stmtInsertComment by lazy {
827 connection.prepareStatement(
828 "insert into lpit_issue_comment (issueid, comment, userid) values (?, ? ,?)"
829 )
830 }
831 private val stmtUpdateIssueDate by lazy {
832 connection.prepareStatement(
833 "update lpit_issue set updated = now() where issueid = ?"
834 )
835 }
837 override fun listComments(issue: Issue): List<IssueComment> {
838 stmtComments.setInt(1, issue.id)
839 return selectComments(stmtComments).toList()
840 }
842 override fun insertComment(issueComment: IssueComment) {
843 with(issueComment) {
844 stmtUpdateIssueDate.setInt(1, issueid)
845 stmtInsertComment.setInt(1, issueid)
846 stmtInsertComment.setStringSafe(2, comment)
847 stmtInsertComment.setIntOrNull(3, author?.id)
848 }
849 stmtInsertComment.execute()
850 stmtUpdateIssueDate.execute()
851 }
852 //</editor-fold>
853 }
