|
1 /* |
|
2 * Copyright 2020 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 */ |
|
25 |
|
26 package de.uapcore.lightpit.dao |
|
27 |
|
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 |
|
34 |
|
35 class PostgresDataAccessObject(private val connection: Connection) : DataAccessObject { |
|
36 |
|
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 } |
|
56 |
|
57 private fun selectUsers(stmt: PreparedStatement) = sequence { |
|
58 stmt.executeQuery().use { rs -> |
|
59 while (rs.next()) selectUserInfo(rs)?.let { yield(it) } |
|
60 } |
|
61 } |
|
62 |
|
63 //language=SQL |
|
64 private val userQuery = "select userid, username, lastname, givenname, mail from lpit_user" |
|
65 |
|
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 } |
|
98 |
|
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 } |
|
104 |
|
105 override fun findUserByName(username: String): User? { |
|
106 stmtUserByName.setString(1, username) |
|
107 return selectUsers(stmtUserByName).firstOrNull() |
|
108 } |
|
109 |
|
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 } |
|
119 |
|
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> |
|
130 |
|
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 } |
|
144 |
|
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 } |
|
154 |
|
155 //language=SQL |
|
156 private val versionQuery = "select versionid, project, name, node, ordinal, status from lpit_version" |
|
157 |
|
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 } |
|
196 |
|
197 override fun listVersions(project: Project): List<Version> { |
|
198 stmtVersions.setInt(1, project.id) |
|
199 return selectVersions(stmtVersions).toList() |
|
200 } |
|
201 |
|
202 override fun findVersion(id: Int): Version? { |
|
203 stmtVersionByID.setInt(1, id) |
|
204 return selectVersions(stmtVersionByID).firstOrNull() |
|
205 } |
|
206 |
|
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 } |
|
212 |
|
213 override fun insertVersion(version: Version) { |
|
214 val col = setVersionFields(stmtInsertVersion, version) |
|
215 stmtInsertVersion.setInt(col, version.projectid) |
|
216 stmtInsertVersion.execute() |
|
217 } |
|
218 |
|
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> |
|
225 |
|
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 } |
|
245 |
|
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 } |
|
257 |
|
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 """ |
|
266 |
|
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 } |
|
302 |
|
303 override fun listComponents(project: Project): List<Component> { |
|
304 stmtComponents.setInt(1, project.id) |
|
305 return selectComponents(stmtComponents).toList() |
|
306 } |
|
307 |
|
308 override fun findComponent(id: Int): Component? { |
|
309 stmtComponentById.setInt(1, id) |
|
310 return selectComponents(stmtComponentById).firstOrNull() |
|
311 } |
|
312 |
|
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 } |
|
318 |
|
319 override fun insertComponent(component: Component) { |
|
320 val col = setComponentFields(stmtInsertComponent, component) |
|
321 stmtInsertComponent.setInt(col, component.projectid) |
|
322 stmtInsertComponent.execute() |
|
323 } |
|
324 |
|
325 override fun updateComponent(component: Component) { |
|
326 val col = setComponentFields(stmtUpdateComponent, component) |
|
327 stmtUpdateComponent.setInt(col, component.id) |
|
328 stmtUpdateComponent.execute() |
|
329 } |
|
330 |
|
331 //</editor-fold> |
|
332 |
|
333 //<editor-fold desc="Project"> |
|
334 |
|
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 description = rs.getString("description") |
|
342 repoUrl = rs.getString("repourl") |
|
343 owner = selectUserInfo(rs) |
|
344 }) |
|
345 } |
|
346 } |
|
347 } |
|
348 |
|
349 private fun setProjectFields(stmt: PreparedStatement, obj: Project): Int { |
|
350 with(obj) { |
|
351 stmt.setStringSafe(1, name) |
|
352 stmt.setStringSafe(2, node) |
|
353 stmt.setStringOrNull(3, description) |
|
354 stmt.setStringOrNull(4, repoUrl) |
|
355 stmt.setIntOrNull(5, owner?.id) |
|
356 } |
|
357 return 6 |
|
358 } |
|
359 |
|
360 //language=SQL |
|
361 private val projectQuery = |
|
362 """ |
|
363 select projectid, name, node, 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 """ |
|
368 |
|
369 private val stmtProjects by lazy { |
|
370 connection.prepareStatement( |
|
371 """${projectQuery} |
|
372 order by lower(name) |
|
373 """ |
|
374 ) |
|
375 } |
|
376 private val stmtProjectByID by lazy { |
|
377 connection.prepareStatement( |
|
378 """${projectQuery} |
|
379 where projectid = ? |
|
380 """ |
|
381 ) |
|
382 } |
|
383 private val stmtProjectByNode by lazy { |
|
384 connection.prepareStatement( |
|
385 """${projectQuery} |
|
386 where node = ? |
|
387 """ |
|
388 ) |
|
389 } |
|
390 private val stmtInsertProject by lazy { |
|
391 connection.prepareStatement( |
|
392 "insert into lpit_project (name, node, description, repourl, owner) values (?, ?, ?, ?, ?)" |
|
393 ) |
|
394 } |
|
395 private val stmtUpdateProject by lazy { |
|
396 connection.prepareStatement( |
|
397 "update lpit_project set name = ?, node = ?, description = ?, repourl = ?, owner = ? where projectid = ?" |
|
398 ) |
|
399 } |
|
400 private val stmtIssueSummary by lazy { |
|
401 connection.prepareStatement( |
|
402 """ |
|
403 select phase, count(*) as total |
|
404 from lpit_issue |
|
405 join lpit_issue_phases using(status) |
|
406 where project = ? |
|
407 group by phase |
|
408 """ |
|
409 ) |
|
410 } |
|
411 |
|
412 override fun listProjects(): List<Project> { |
|
413 return selectProjects(stmtProjects).toList() |
|
414 } |
|
415 |
|
416 override fun findProject(id: Int): Project? { |
|
417 stmtProjectByID.setInt(1, id) |
|
418 return selectProjects(stmtProjectByID).firstOrNull() |
|
419 } |
|
420 |
|
421 override fun findProjectByNode(node: String): Project? { |
|
422 stmtProjectByNode.setString(1, node) |
|
423 return selectProjects(stmtProjectByNode).firstOrNull() |
|
424 } |
|
425 |
|
426 override fun insertProject(project: Project) { |
|
427 setProjectFields(stmtInsertProject, project) |
|
428 stmtInsertProject.execute() |
|
429 } |
|
430 |
|
431 override fun updateProject(project: Project) { |
|
432 val col = setProjectFields(stmtUpdateProject, project) |
|
433 stmtUpdateProject.setInt(col, project.id) |
|
434 stmtUpdateProject.execute() |
|
435 } |
|
436 |
|
437 override fun collectIssueSummary(project: Project): IssueSummary { |
|
438 stmtIssueSummary.setInt(1, project.id) |
|
439 return stmtIssueSummary.executeQuery().use { rs -> |
|
440 val summary = IssueSummary() |
|
441 while (rs.next()) { |
|
442 val phase = rs.getInt("phase") |
|
443 val total = rs.getInt("total") |
|
444 when (phase) { |
|
445 0 -> summary.open = total |
|
446 1 -> summary.active = total |
|
447 2 -> summary.done = total |
|
448 } |
|
449 } |
|
450 summary |
|
451 } |
|
452 } |
|
453 |
|
454 //</editor-fold> |
|
455 |
|
456 //<editor-fold desc="Issue"> |
|
457 |
|
458 private fun selectIssues(stmt: PreparedStatement) = sequence { |
|
459 stmt.executeQuery().use { rs -> |
|
460 while (rs.next()) { |
|
461 val proj = Project(rs.getInt("project")).apply { |
|
462 name = rs.getString("projectname") |
|
463 node = rs.getString("projectnode") |
|
464 } |
|
465 val comp = rs.getInt("component").let { |
|
466 if (rs.wasNull()) null else |
|
467 Component(it, proj.id).apply { |
|
468 name = rs.getString("componentname") |
|
469 node = rs.getString("componentnode") |
|
470 } |
|
471 } |
|
472 val issue = Issue(rs.getInt("issueid"), proj, comp).apply { |
|
473 component = comp |
|
474 status = rs.getEnum("status") |
|
475 category = rs.getEnum("category") |
|
476 subject = rs.getString("subject") |
|
477 description = rs.getString("description") |
|
478 assignee = selectUserInfo(rs) |
|
479 created = rs.getTimestamp("created") |
|
480 updated = rs.getTimestamp("updated") |
|
481 eta = rs.getDate("eta") |
|
482 } |
|
483 queryAffectedVersions.setInt(1, issue.id) |
|
484 issue.affectedVersions = selectVersions(queryAffectedVersions).toList() |
|
485 queryResolvedVersions.setInt(1, issue.id) |
|
486 issue.resolvedVersions = selectVersions(queryResolvedVersions).toList() |
|
487 yield(issue) |
|
488 } |
|
489 } |
|
490 } |
|
491 |
|
492 private fun setIssueFields(stmt: PreparedStatement, obj: Issue): Int { |
|
493 with(obj) { |
|
494 stmt.setIntOrNull(1, component?.id) |
|
495 stmt.setEnum(2, status) |
|
496 stmt.setEnum(3, category) |
|
497 stmt.setStringSafe(4, subject) |
|
498 stmt.setStringOrNull(5, description) |
|
499 stmt.setIntOrNull(6, assignee?.id) |
|
500 stmt.setDateOrNull(7, eta) |
|
501 } |
|
502 return 8 |
|
503 } |
|
504 |
|
505 //language=SQL |
|
506 private val issueQuery = |
|
507 """ |
|
508 select issueid, |
|
509 i.project, p.name as projectname, p.node as projectnode, |
|
510 component, c.name as componentname, c.node as componentnode, |
|
511 status, category, subject, i.description, |
|
512 userid, username, givenname, lastname, mail, |
|
513 created, updated, eta |
|
514 from lpit_issue i |
|
515 join lpit_project p on i.project = projectid |
|
516 left join lpit_component c on component = c.id |
|
517 left join lpit_user on userid = assignee |
|
518 """ |
|
519 |
|
520 private val queryResolvedVersions by lazy { |
|
521 connection.prepareStatement( |
|
522 """ |
|
523 select versionid, project, name, status, ordinal, node |
|
524 from lpit_version v join lpit_issue_resolved_version using (versionid) |
|
525 where issueid = ? |
|
526 order by ordinal, name |
|
527 """ |
|
528 ) |
|
529 } |
|
530 |
|
531 private val queryAffectedVersions by lazy { |
|
532 connection.prepareStatement( |
|
533 """ |
|
534 select versionid, project, name, status, ordinal, node |
|
535 from lpit_version join lpit_issue_affected_version using (versionid) |
|
536 where issueid = ? |
|
537 order by ordinal, name |
|
538 """ |
|
539 ) |
|
540 } |
|
541 |
|
542 private val stmtIssues by lazy { |
|
543 connection.prepareStatement( |
|
544 """ |
|
545 with issue_version as ( |
|
546 select issueid, versionid from lpit_issue_affected_version |
|
547 union select issueid, versionid from lpit_issue_resolved_version |
|
548 ) ${issueQuery} left join issue_version using (issueid) |
|
549 where |
|
550 (not ? or projectid = ?) and |
|
551 (not ? or versionid = ?) and (not ? or versionid is null) and |
|
552 (not ? or component = ?) and (not ? or component is null) |
|
553 """ |
|
554 ) |
|
555 } |
|
556 |
|
557 private val fproj = 1 |
|
558 private val projectid = 2 |
|
559 private val fversion = 3 |
|
560 private val versionid = 4 |
|
561 private val nversion = 5 |
|
562 private val fcomp = 6 |
|
563 private val component = 7 |
|
564 private val ncomp = 8 |
|
565 |
|
566 private fun <T : Entity> applyFilter(filter: Filter<T>, fflag: Int, nflag: Int, idcol: Int) { |
|
567 when (filter) { |
|
568 is AllFilter -> { |
|
569 stmtIssues.setBoolean(fflag, false) |
|
570 stmtIssues.setBoolean(nflag, false) |
|
571 stmtIssues.setInt(idcol, 0) |
|
572 } |
|
573 is NoneFilter -> { |
|
574 stmtIssues.setBoolean(fflag, false) |
|
575 stmtIssues.setBoolean(nflag, true) |
|
576 stmtIssues.setInt(idcol, 0) |
|
577 } |
|
578 is SpecificFilter -> { |
|
579 stmtIssues.setBoolean(fflag, true) |
|
580 stmtIssues.setBoolean(nflag, false) |
|
581 stmtIssues.setInt(idcol, filter.obj.id) |
|
582 } |
|
583 else -> { |
|
584 TODO("Implement range filter.") |
|
585 } |
|
586 } |
|
587 } |
|
588 |
|
589 override fun listIssues(filter: IssueFilter): List<Issue> { |
|
590 when (filter.project) { |
|
591 is AllFilter -> { |
|
592 stmtIssues.setBoolean(fproj, false) |
|
593 stmtIssues.setInt(projectid, 0) |
|
594 } |
|
595 is SpecificFilter -> { |
|
596 stmtIssues.setBoolean(fproj, true) |
|
597 stmtIssues.setInt(projectid, filter.project.obj.id) |
|
598 } |
|
599 else -> throw IllegalArgumentException() |
|
600 } |
|
601 applyFilter(filter.version, fversion, nversion, versionid) |
|
602 applyFilter(filter.component, fcomp, ncomp, component) |
|
603 |
|
604 return selectIssues(stmtIssues).toList() |
|
605 } |
|
606 |
|
607 private val stmtFindIssueByID by lazy { |
|
608 connection.prepareStatement( |
|
609 """${issueQuery} |
|
610 where issueid = ? |
|
611 """ |
|
612 ) |
|
613 } |
|
614 private val stmtInsertIssue by lazy { |
|
615 connection.prepareStatement( |
|
616 """ |
|
617 insert into lpit_issue (component, status, category, subject, description, assignee, eta, project) |
|
618 values (?, ?::issue_status, ?::issue_category, ?, ?, ?, ?, ?) |
|
619 returning issueid |
|
620 """ |
|
621 ) |
|
622 } |
|
623 private val stmtUpdateIssue by lazy { |
|
624 connection.prepareStatement( |
|
625 """ |
|
626 update lpit_issue set updated = now(), |
|
627 component = ?, status = ?::issue_status, category = ?::issue_category, subject = ?, |
|
628 description = ?, assignee = ?, eta = ? |
|
629 where issueid = ? |
|
630 """ |
|
631 ) |
|
632 } |
|
633 private val stmtInsertAffectedVersion by lazy { |
|
634 connection.prepareStatement( |
|
635 "insert into lpit_issue_affected_version (issueid, versionid) values (?,?)" |
|
636 ) |
|
637 } |
|
638 private val stmtInsertResolvedVersion by lazy { |
|
639 connection.prepareStatement( |
|
640 "insert into lpit_issue_resolved_version (issueid, versionid) values (?,?)" |
|
641 ) |
|
642 } |
|
643 private val stmtClearAffectedVersions by lazy { |
|
644 connection.prepareStatement("delete from lpit_issue_affected_version where issueid = ?") |
|
645 } |
|
646 private val stmtClearResolvedVersions by lazy { |
|
647 connection.prepareStatement("delete from lpit_issue_resolved_version where issueid = ?") |
|
648 } |
|
649 |
|
650 override fun findIssue(id: Int): Issue? { |
|
651 stmtFindIssueByID.setInt(1, id) |
|
652 return selectIssues(stmtFindIssueByID).firstOrNull() |
|
653 } |
|
654 |
|
655 private fun insertVersionInfo(issue: Issue) { |
|
656 stmtInsertAffectedVersion.setInt(1, issue.id) |
|
657 stmtInsertResolvedVersion.setInt(1, issue.id) |
|
658 issue.affectedVersions.forEach { |
|
659 stmtInsertAffectedVersion.setInt(2, it.id) |
|
660 stmtInsertAffectedVersion.execute() |
|
661 } |
|
662 issue.resolvedVersions.forEach { |
|
663 stmtInsertResolvedVersion.setInt(2, it.id) |
|
664 stmtInsertResolvedVersion.execute() |
|
665 } |
|
666 } |
|
667 |
|
668 override fun insertIssue(issue: Issue) { |
|
669 val col = setIssueFields(stmtInsertIssue, issue) |
|
670 stmtInsertIssue.setInt(col, issue.project.id) |
|
671 stmtInsertIssue.executeQuery().use { rs -> |
|
672 rs.next() |
|
673 issue.id = rs.getInt(1) |
|
674 } |
|
675 insertVersionInfo(issue) |
|
676 } |
|
677 |
|
678 override fun updateIssue(issue: Issue) { |
|
679 val col = setIssueFields(stmtUpdateIssue, issue) |
|
680 stmtUpdateIssue.setInt(col, issue.id) |
|
681 // TODO: improve by only inserting / deleting changed version information |
|
682 stmtClearAffectedVersions.setInt(1, issue.id) |
|
683 stmtClearResolvedVersions.setInt(1, issue.id) |
|
684 stmtClearAffectedVersions.execute() |
|
685 stmtClearResolvedVersions.execute() |
|
686 insertVersionInfo(issue) |
|
687 } |
|
688 |
|
689 //</editor-fold> |
|
690 |
|
691 //<editor-fold desc="IssueComment"> |
|
692 |
|
693 private fun selectComments(stmt: PreparedStatement) = sequence { |
|
694 stmt.executeQuery().use { rs -> |
|
695 while (rs.next()) { |
|
696 yield(IssueComment(rs.getInt("commentid"), rs.getInt("issueid")).apply { |
|
697 created = rs.getTimestamp("created") |
|
698 updated = rs.getTimestamp("updated") |
|
699 updateCount = rs.getInt("updatecount") |
|
700 comment = rs.getString("comment") |
|
701 author = selectUserInfo(rs) |
|
702 }) |
|
703 } |
|
704 } |
|
705 } |
|
706 |
|
707 private val stmtComments by lazy { |
|
708 connection.prepareStatement( |
|
709 "select * from lpit_issue_comment left join lpit_user using (userid) where issueid = ? order by created" |
|
710 ) |
|
711 } |
|
712 private val stmtInsertComment by lazy { |
|
713 connection.prepareStatement( |
|
714 "insert into lpit_issue_comment (issueid, comment, userid) values (?, ? ,?)" |
|
715 ) |
|
716 } |
|
717 private val stmtUpdateIssueDate by lazy { |
|
718 connection.prepareStatement( |
|
719 "update lpit_issue set updated = now() where issueid = ?" |
|
720 ) |
|
721 } |
|
722 |
|
723 override fun listComments(issue: Issue): List<IssueComment> { |
|
724 stmtComments.setInt(1, issue.id) |
|
725 return selectComments(stmtComments).toList() |
|
726 } |
|
727 |
|
728 override fun insertComment(issueComment: IssueComment) { |
|
729 with(issueComment) { |
|
730 stmtUpdateIssueDate.setInt(1, issueid) |
|
731 stmtInsertComment.setInt(1, issueid) |
|
732 stmtInsertComment.setStringSafe(2, comment) |
|
733 stmtInsertComment.setIntOrNull(3, author?.id) |
|
734 } |
|
735 stmtInsertComment.execute() |
|
736 stmtUpdateIssueDate.execute() |
|
737 } |
|
738 //</editor-fold> |
|
739 } |