src/main/kotlin/de/uapcore/lightpit/dao/PostgresDataAccessObject.kt@f7de8158b41c
src/main/kotlin/de/uapcore/lightpit/dao/PostgresDataAccessObject.kt
Tue, 11 May 2021 16:00:28 +0200
- author
- Mike Becker <universe@uap-core.de>
- date
- Tue, 11 May 2021 16:00:28 +0200
- changeset 189
- f7de8158b41c
- parent 188
- 2979436edd9e
- child 190
- a83f1ab56898
- permissions
- -rw-r--r--
fixes #137 - leaking prepared statements
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 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 status = getEnum("status")
144 }
146 override fun listVersions(project: Project): List<Version> =
147 withStatement("$versionQuery where project = ? order by ordinal desc, lower(name) desc") {
148 setInt(1, project.id)
149 queryAll { it.extractVersion() }
150 }
152 override fun listVersionSummaries(project: Project): List<VersionSummary> =
153 withStatement(
154 """
155 with version_map(issueid, versionid, isresolved) as (
156 select issueid, versionid, 1
157 from lpit_issue_resolved_version
158 union
159 select issueid, versionid, 0
160 from lpit_issue_affected_version
161 ),
162 issues as (
163 select versionid, phase, isresolved, count(issueid) as total
164 from lpit_issue
165 join version_map using (issueid)
166 join lpit_issue_phases using (status)
167 group by versionid, phase, isresolved
168 ),
169 summary as (
170 select versionid, phase, isresolved, total
171 from lpit_version v
172 left join issues using (versionid)
173 where v.project = ?
174 )
175 select versionid, project, name, node, ordinal, status, phase, isresolved, total
176 from lpit_version
177 join summary using (versionid)
178 order by ordinal, name
179 """.trimIndent()
180 ) {
181 setInt(1, project.id)
182 executeQuery().use { rs ->
183 sequence {
184 // TODO: fix bug: this extractor is not grouping the results
185 val versionSummary = VersionSummary(rs.extractVersion())
186 val phase = rs.getInt("phase")
187 val total = rs.getInt("total")
188 val issueSummary =
189 if (rs.getBoolean("isresolved")) versionSummary.resolvedTotal else versionSummary.reportedTotal
190 when (phase) {
191 0 -> issueSummary.open = total
192 1 -> issueSummary.active = total
193 2 -> issueSummary.done = total
194 }
195 yield(versionSummary)
196 }.toList()
197 }
198 }
200 override fun findVersion(id: Int): Version? =
201 withStatement("$versionQuery where versionid = ?") {
202 setInt(1, id)
203 querySingle { it.extractVersion() }
204 }
206 override fun findVersionByNode(project: Project, node: String): Version? =
207 withStatement("$versionQuery where project = ? and node = ?") {
208 setInt(1, project.id)
209 setString(2, node)
210 querySingle { it.extractVersion() }
211 }
213 override fun insertVersion(version: Version) {
214 withStatement("insert into lpit_version (name, node, ordinal, status, project) values (?, ?, ?, ?::version_status, ?)") {
215 with(version) {
216 setStringSafe(1, name)
217 setStringSafe(2, node)
218 setInt(3, ordinal)
219 setEnum(4, status)
220 setInt(5, version.projectid)
221 }
222 executeUpdate()
223 }
225 }
227 override fun updateVersion(version: Version) {
228 withStatement("update lpit_version set name = ?, node = ?, ordinal = ?, status = ?::version_status where versionid = ?") {
229 with(version) {
230 setStringSafe(1, name)
231 setStringSafe(2, node)
232 setInt(3, ordinal)
233 setEnum(4, status)
234 setInt(5, id)
235 }
236 executeUpdate()
237 }
238 }
239 //</editor-fold>
241 //<editor-fold desc="Component">
242 //language=SQL
243 private val componentQuery =
244 """
245 select id, project, name, node, color, ordinal, description,
246 userid, username, givenname, lastname, mail
247 from lpit_component
248 left join lpit_user on lead = userid
249 """.trimIndent()
251 private fun ResultSet.extractComponent(): Component =
252 Component(getInt("id"), getInt("project")).apply {
253 name = getString("name")
254 node = getString("node")
255 color = try {
256 WebColor(getString("color"))
257 } catch (ex: IllegalArgumentException) {
258 WebColor("000000")
259 }
260 ordinal = getInt("ordinal")
261 description = getString("description")
262 lead = extractOptionalUser()
263 }
265 private fun PreparedStatement.setComponent(index: Int, component: Component): Int {
266 with(component) {
267 var i = index
268 setStringSafe(i++, name)
269 setStringSafe(i++, node)
270 setStringSafe(i++, color.hex)
271 setInt(i++, ordinal)
272 setStringOrNull(i++, description)
273 setIntOrNull(i++, lead?.id)
274 return i
275 }
276 }
278 override fun listComponents(project: Project): List<Component> =
279 withStatement("$componentQuery where project = ? order by ordinal, lower(name)") {
280 setInt(1, project.id)
281 queryAll { it.extractComponent() }
282 }
284 override fun listComponentSummaries(project: Project): List<ComponentSummary> =
285 withStatement(
286 """
287 with issues as (
288 select component, phase, count(issueid) as total
289 from lpit_issue
290 join lpit_issue_phases using (status)
291 group by component, phase
292 ),
293 summary as (
294 select c.id, phase, total
295 from lpit_component c
296 left join issues i on c.id = i.component
297 where c.project = ?
298 )
299 select c.id, project, name, node, color, ordinal, description,
300 userid, username, givenname, lastname, mail, phase, total
301 from lpit_component c
302 left join lpit_user on lead = userid
303 join summary s on c.id = s.id
304 order by ordinal, name
305 """.trimIndent()
306 ) {
307 setInt(1, project.id)
308 executeQuery().use { rs ->
309 // TODO: fix bug: this extractor is not grouping the results
310 sequence {
311 val componentSummary = ComponentSummary(rs.extractComponent()).also {
312 val phase = rs.getInt("phase")
313 val total = rs.getInt("total")
314 when (phase) {
315 0 -> it.issueSummary.open = total
316 1 -> it.issueSummary.active = total
317 2 -> it.issueSummary.done = total
318 }
319 }
320 yield(componentSummary)
321 }.toList()
322 }
323 }
325 override fun findComponent(id: Int): Component? =
326 withStatement("$componentQuery where id = ?") {
327 setInt(1, id)
328 querySingle { it.extractComponent() }
329 }
331 override fun findComponentByNode(project: Project, node: String): Component? =
332 withStatement("$componentQuery where project = ? and node = ?") {
333 setInt(1, project.id)
334 setString(2, node)
335 querySingle { it.extractComponent() }
336 }
338 override fun insertComponent(component: Component) {
339 withStatement("insert into lpit_component (name, node, color, ordinal, description, lead, project) values (?, ?, ?, ?, ?, ?, ?)") {
340 val col = setComponent(1, component)
341 setInt(col, component.projectid)
342 executeUpdate()
343 }
344 }
346 override fun updateComponent(component: Component) {
347 withStatement("update lpit_component set name = ?, node = ?, color = ?, ordinal = ?, description = ?, lead = ? where id = ?") {
348 val col = setComponent(1, component)
349 setInt(col, component.id)
350 executeUpdate()
351 }
352 }
354 //</editor-fold>
356 //<editor-fold desc="Project">
358 //language=SQL
359 private val projectQuery =
360 """
361 select projectid, name, node, ordinal, description, repourl,
362 userid, username, lastname, givenname, mail
363 from lpit_project
364 left join lpit_user owner on lpit_project.owner = owner.userid
365 """.trimIndent()
367 private fun ResultSet.extractProject() =
368 Project(getInt("projectid")).apply {
369 name = getString("name")
370 node = getString("node")
371 ordinal = getInt("ordinal")
372 description = getString("description")
373 repoUrl = getString("repourl")
374 owner = extractOptionalUser()
375 }
377 private fun PreparedStatement.setProject(index: Int, project: Project): Int {
378 var i = index
379 with(project) {
380 setStringSafe(i++, name)
381 setStringSafe(i++, node)
382 setInt(i++, ordinal)
383 setStringOrNull(i++, description)
384 setStringOrNull(i++, repoUrl)
385 setIntOrNull(i++, owner?.id)
386 }
387 return i
388 }
390 override fun listProjects(): List<Project> =
391 withStatement("$projectQuery order by ordinal, lower(name)") {
392 queryAll { it.extractProject() }
393 }
395 override fun findProject(id: Int): Project? =
396 withStatement("$projectQuery where projectid = ?") {
397 setInt(1, id)
398 querySingle { it.extractProject() }
399 }
401 override fun findProjectByNode(node: String): Project? =
402 withStatement("$projectQuery where node = ?") {
403 setString(1, node)
404 querySingle { it.extractProject() }
405 }
407 override fun insertProject(project: Project) {
408 withStatement("insert into lpit_project (name, node, ordinal, description, repourl, owner) values (?, ?, ?, ?, ?, ?)") {
409 setProject(1, project)
410 executeUpdate()
411 }
412 }
414 override fun updateProject(project: Project) {
415 withStatement("update lpit_project set name = ?, node = ?, ordinal = ?, description = ?, repourl = ?, owner = ? where projectid = ?") {
416 val col = setProject(1, project)
417 setInt(col, project.id)
418 executeUpdate()
419 }
420 }
422 override fun collectIssueSummary(project: Project): IssueSummary =
423 withStatement(
424 """
425 select phase, count(*) as total
426 from lpit_issue
427 join lpit_issue_phases using(status)
428 where project = ?
429 group by phase
430 """.trimIndent()
431 ) {
432 setInt(1, project.id)
433 executeQuery().use {
434 val summary = IssueSummary()
435 while (it.next()) {
436 val phase = it.getInt("phase")
437 val total = it.getInt("total")
438 when (phase) {
439 0 -> summary.open = total
440 1 -> summary.active = total
441 2 -> summary.done = total
442 }
443 }
444 summary
445 }
446 }
448 //</editor-fold>
450 //<editor-fold desc="Issue">
452 //language=SQL
453 private val issueQuery =
454 """
455 select issueid,
456 i.project, p.name as projectname, p.node as projectnode,
457 component, c.name as componentname, c.node as componentnode,
458 status, category, subject, i.description,
459 userid, username, givenname, lastname, mail,
460 created, updated, eta
461 from lpit_issue i
462 join lpit_project p on i.project = projectid
463 left join lpit_component c on component = c.id
464 left join lpit_user on userid = assignee
465 """.trimIndent()
467 private fun ResultSet.extractIssue(): Issue {
468 val proj = Project(getInt("project")).apply {
469 name = getString("projectname")
470 node = getString("projectnode")
471 }
472 val comp = getInt("component").let {
473 if (wasNull()) null else
474 Component(it, proj.id).apply {
475 name = getString("componentname")
476 node = getString("componentnode")
477 }
478 }
479 val issue = Issue(getInt("issueid"), proj).apply {
480 component = comp
481 status = getEnum("status")
482 category = getEnum("category")
483 subject = getString("subject")
484 description = getString("description")
485 assignee = extractOptionalUser()
486 created = getTimestamp("created")
487 updated = getTimestamp("updated")
488 eta = getDate("eta")
489 }
491 fun versionQuery(table: String) =
492 """
493 select versionid, project, name, status, ordinal, node
494 from lpit_version join $table using (versionid)
495 where issueid = ?
496 order by ordinal, name
497 """.trimIndent()
499 issue.affectedVersions = withStatement(versionQuery("lpit_issue_affected_version")) {
500 setInt(1, issue.id)
501 queryAll { it.extractVersion() }
502 }
503 issue.resolvedVersions = withStatement(versionQuery("lpit_issue_resolved_version")) {
504 setInt(1, issue.id)
505 queryAll { it.extractVersion() }
506 }
507 return issue
508 }
510 private fun PreparedStatement.setIssue(index: Int, issue: Issue): Int {
511 var i = index
512 with(issue) {
513 setIntOrNull(i++, component?.id)
514 setEnum(i++, status)
515 setEnum(i++, category)
516 setStringSafe(i++, subject)
517 setStringOrNull(i++, description)
518 setIntOrNull(i++, assignee?.id)
519 setDateOrNull(i++, eta)
520 }
521 return i
522 }
524 override fun listIssues(filter: IssueFilter): List<Issue> =
525 withStatement(
526 """
527 with issue_version as (
528 select issueid, versionid from lpit_issue_affected_version
529 union select issueid, versionid from lpit_issue_resolved_version
530 ),
531 filtered_issues as (
532 select distinct issueid from lpit_issue
533 left join issue_version using (issueid)
534 where
535 (not ? or project = ?) and
536 (not ? or versionid = ?) and (not ? or versionid is null) and
537 (not ? or component = ?) and (not ? or component is null)
538 )
539 $issueQuery join filtered_issues using (issueid)
540 """.trimIndent()
541 ) {
542 fun <T : Entity> applyFilter(filter: Filter<T>, fflag: Int, nflag: Int, idcol: Int) {
543 when (filter) {
544 is AllFilter -> {
545 setBoolean(fflag, false)
546 setBoolean(nflag, false)
547 setInt(idcol, 0)
548 }
549 is NoneFilter -> {
550 setBoolean(fflag, false)
551 setBoolean(nflag, true)
552 setInt(idcol, 0)
553 }
554 is SpecificFilter -> {
555 setBoolean(fflag, true)
556 setBoolean(nflag, false)
557 setInt(idcol, filter.obj.id)
558 }
559 else -> {
560 TODO("Implement range filter.")
561 }
562 }
563 }
564 when (filter.project) {
565 is AllFilter -> {
566 setBoolean(1, false)
567 setInt(2, 0)
568 }
569 is SpecificFilter -> {
570 setBoolean(1, true)
571 setInt(2, filter.project.obj.id)
572 }
573 else -> throw IllegalArgumentException()
574 }
575 applyFilter(filter.version, 3, 5, 4)
576 applyFilter(filter.component, 6, 8, 7)
578 queryAll { it.extractIssue() }
579 }
581 override fun findIssue(id: Int): Issue? =
582 withStatement("$issueQuery where issueid = ?") {
583 setInt(1, id)
584 querySingle { it.extractIssue() }
585 }
587 private fun insertVersionInfo(id: Int, issue: Issue) {
588 withStatement("insert into lpit_issue_affected_version (issueid, versionid) values (?,?)") {
589 setInt(1, id)
590 issue.affectedVersions.forEach {
591 setInt(2, it.id)
592 executeUpdate()
593 }
594 }
595 withStatement("insert into lpit_issue_resolved_version (issueid, versionid) values (?,?)") {
596 setInt(1, id)
597 issue.resolvedVersions.forEach {
598 setInt(2, it.id)
599 executeUpdate()
600 }
601 }
602 }
604 override fun insertIssue(issue: Issue): Int {
605 val id = withStatement(
606 """
607 insert into lpit_issue (component, status, category, subject, description, assignee, eta, project)
608 values (?, ?::issue_status, ?::issue_category, ?, ?, ?, ?, ?)
609 returning issueid
610 """.trimIndent()
611 ) {
612 val col = setIssue(1, issue)
613 setInt(col, issue.project.id)
614 querySingle { it.getInt(1) }!!
615 }
616 insertVersionInfo(id, issue)
617 return id
618 }
620 override fun updateIssue(issue: Issue) {
621 withStatement(
622 """
623 update lpit_issue set updated = now(),
624 component = ?, status = ?::issue_status, category = ?::issue_category, subject = ?,
625 description = ?, assignee = ?, eta = ?
626 where issueid = ?
627 """.trimIndent()
628 ) {
629 val col = setIssue(1, issue)
630 setInt(col, issue.id)
631 executeUpdate()
632 }
634 // TODO: improve by only inserting / deleting changed version information
635 withStatement("delete from lpit_issue_affected_version where issueid = ?") {
636 setInt(1, issue.id)
637 executeUpdate()
638 }
639 withStatement("delete from lpit_issue_resolved_version where issueid = ?") {
640 setInt(1, issue.id)
641 executeUpdate()
642 }
643 insertVersionInfo(issue.id, issue)
644 }
646 //</editor-fold>
648 //<editor-fold desc="IssueComment">
650 private fun ResultSet.extractIssueComment() =
651 IssueComment(getInt("commentid"), getInt("issueid")).apply {
652 created = getTimestamp("created")
653 updated = getTimestamp("updated")
654 updateCount = getInt("updatecount")
655 comment = getString("comment")
656 author = extractOptionalUser()
657 }
659 override fun listComments(issue: Issue): List<IssueComment> =
660 withStatement("select * from lpit_issue_comment left join lpit_user using (userid) where issueid = ? order by created") {
661 setInt(1, issue.id)
662 queryAll { it.extractIssueComment() }
663 }
665 override fun insertComment(issueComment: IssueComment) {
666 useStatement("update lpit_issue set updated = now() where issueid = ?") { updateIssueDate ->
667 withStatement("insert into lpit_issue_comment (issueid, comment, userid) values (?, ? ,?)") {
668 with(issueComment) {
669 updateIssueDate.setInt(1, issueid)
670 setInt(1, issueid)
671 setStringSafe(2, comment)
672 setIntOrNull(3, author?.id)
673 }
674 executeUpdate()
675 updateIssueDate.executeUpdate()
676 }
677 }
678 }
679 //</editor-fold>
680 }
