27 * |
27 * |
28 */ |
28 */ |
29 package de.uapcore.lightpit.dao.postgres; |
29 package de.uapcore.lightpit.dao.postgres; |
30 |
30 |
31 import de.uapcore.lightpit.dao.VersionDao; |
31 import de.uapcore.lightpit.dao.VersionDao; |
32 import de.uapcore.lightpit.entities.Project; |
32 import de.uapcore.lightpit.entities.*; |
33 import de.uapcore.lightpit.entities.Version; |
|
34 import de.uapcore.lightpit.entities.VersionStatus; |
|
35 |
33 |
36 import java.sql.Connection; |
34 import java.sql.Connection; |
37 import java.sql.PreparedStatement; |
35 import java.sql.PreparedStatement; |
38 import java.sql.ResultSet; |
36 import java.sql.ResultSet; |
39 import java.sql.SQLException; |
37 import java.sql.SQLException; |
42 import java.util.Objects; |
40 import java.util.Objects; |
43 |
41 |
44 public final class PGVersionDao implements VersionDao { |
42 public final class PGVersionDao implements VersionDao { |
45 |
43 |
46 private final PreparedStatement insert, update, list, find; |
44 private final PreparedStatement insert, update, list, find; |
|
45 private final PreparedStatement issuesAffected, issuesScheduled, issuesResolved; |
47 |
46 |
48 public PGVersionDao(Connection connection) throws SQLException { |
47 public PGVersionDao(Connection connection) throws SQLException { |
49 list = connection.prepareStatement( |
48 list = connection.prepareStatement( |
50 "select versionid, project, name, ordinal, status " + |
49 "select versionid, project, name, ordinal, status " + |
51 "from lpit_version " + |
50 "from lpit_version " + |
52 "where project = ? " + |
51 "where project = ? " + |
53 "order by ordinal, lower(name)"); |
52 "order by ordinal desc, lower(name) desc"); |
54 |
53 |
55 find = connection.prepareStatement( |
54 find = connection.prepareStatement( |
56 "select versionid, project, name, ordinal, status " + |
55 "select versionid, project, name, ordinal, status " + |
57 "from lpit_version " + |
56 "from lpit_version " + |
58 "where versionid = ?"); |
57 "where versionid = ?"); |
61 "insert into lpit_version (project, name, ordinal, status) values (?, ?, ?, ?::version_status)" |
60 "insert into lpit_version (project, name, ordinal, status) values (?, ?, ?, ?::version_status)" |
62 ); |
61 ); |
63 update = connection.prepareStatement( |
62 update = connection.prepareStatement( |
64 "update lpit_version set name = ?, ordinal = ?, status = ?::version_status where versionid = ?" |
63 "update lpit_version set name = ?, ordinal = ?, status = ?::version_status where versionid = ?" |
65 ); |
64 ); |
|
65 |
|
66 issuesAffected = connection.prepareStatement( |
|
67 "select category, status, count(*) as issuecount " + |
|
68 "from lpit_issue_affected_version " + |
|
69 "join lpit_issue using (issueid) " + |
|
70 "where versionid = ? " + |
|
71 "group by category, status" |
|
72 ); |
|
73 issuesScheduled = connection.prepareStatement( |
|
74 "select category, status, count(*) as issuecount " + |
|
75 "from lpit_issue_scheduled_version " + |
|
76 "join lpit_issue using (issueid) " + |
|
77 "where versionid = ? " + |
|
78 "group by category, status" |
|
79 ); |
|
80 issuesResolved = connection.prepareStatement( |
|
81 "select category, status, count(*) as issuecount " + |
|
82 "from lpit_issue_resolved_version " + |
|
83 "join lpit_issue using (issueid) " + |
|
84 "where versionid = ? " + |
|
85 "group by category, status" |
|
86 ); |
66 } |
87 } |
67 |
88 |
68 private Version mapColumns(ResultSet result) throws SQLException { |
89 private Version mapColumns(ResultSet result) throws SQLException { |
69 final var project = new Project(result.getInt("project")); |
90 final var project = new Project(result.getInt("project")); |
70 final var version = new Version(result.getInt("versionid"), project); |
91 final var version = new Version(result.getInt("versionid"), project); |
71 version.setName(result.getString("name")); |
92 version.setName(result.getString("name")); |
72 version.setOrdinal(result.getInt("ordinal")); |
93 version.setOrdinal(result.getInt("ordinal")); |
73 version.setStatus(VersionStatus.valueOf(result.getString("status"))); |
94 version.setStatus(VersionStatus.valueOf(result.getString("status"))); |
74 return version; |
95 return version; |
|
96 } |
|
97 |
|
98 private VersionStatistics versionStatistics(Version version, PreparedStatement stmt) throws SQLException { |
|
99 stmt.setInt(1, version.getId()); |
|
100 final var result = stmt.executeQuery(); |
|
101 final var stats = new VersionStatistics(version); |
|
102 while (result.next()) { |
|
103 stats.setIssueCount( |
|
104 IssueCategory.valueOf(result.getString("category")), |
|
105 IssueStatus.valueOf(result.getString("status")), |
|
106 result.getInt("issuecount") |
|
107 ); |
|
108 } |
|
109 return stats; |
75 } |
110 } |
76 |
111 |
77 @Override |
112 @Override |
78 public void save(Version instance) throws SQLException { |
113 public void save(Version instance) throws SQLException { |
79 Objects.requireNonNull(instance.getName()); |
114 Objects.requireNonNull(instance.getName()); |