Sun, 24 May 2020 15:30:43 +0200
adds project overview page
1 /*
2 * DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS HEADER.
3 *
4 * Copyright 2018 Mike Becker. All rights reserved.
5 *
6 * Redistribution and use in source and binary forms, with or without
7 * modification, are permitted provided that the following conditions are met:
8 *
9 * 1. Redistributions of source code must retain the above copyright
10 * notice, this list of conditions and the following disclaimer.
11 *
12 * 2. Redistributions in binary form must reproduce the above copyright
13 * notice, this list of conditions and the following disclaimer in the
14 * documentation and/or other materials provided with the distribution.
15 *
16 * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
17 * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
18 * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
19 * ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT HOLDER OR CONTRIBUTORS BE
20 * LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR
21 * CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF
22 * SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS
23 * INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN
24 * CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE)
25 * ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE
26 * POSSIBILITY OF SUCH DAMAGE.
27 *
28 */
29 package de.uapcore.lightpit.dao.postgres;
31 import de.uapcore.lightpit.dao.VersionDao;
32 import de.uapcore.lightpit.entities.*;
34 import java.sql.Connection;
35 import java.sql.PreparedStatement;
36 import java.sql.ResultSet;
37 import java.sql.SQLException;
38 import java.util.ArrayList;
39 import java.util.List;
40 import java.util.Objects;
42 public final class PGVersionDao implements VersionDao {
44 private final PreparedStatement insert, update, list, find;
45 private final PreparedStatement issuesAffected, issuesScheduled, issuesResolved;
47 public PGVersionDao(Connection connection) throws SQLException {
48 list = connection.prepareStatement(
49 "select versionid, project, name, ordinal, status " +
50 "from lpit_version " +
51 "where project = ? " +
52 "order by ordinal desc, lower(name) desc");
54 find = connection.prepareStatement(
55 "select versionid, project, name, ordinal, status " +
56 "from lpit_version " +
57 "where versionid = ?");
59 insert = connection.prepareStatement(
60 "insert into lpit_version (project, name, ordinal, status) values (?, ?, ?, ?::version_status)"
61 );
62 update = connection.prepareStatement(
63 "update lpit_version set name = ?, ordinal = ?, status = ?::version_status where versionid = ?"
64 );
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 );
87 }
89 private Version mapColumns(ResultSet result) throws SQLException {
90 final var project = new Project(result.getInt("project"));
91 final var version = new Version(result.getInt("versionid"), project);
92 version.setName(result.getString("name"));
93 version.setOrdinal(result.getInt("ordinal"));
94 version.setStatus(VersionStatus.valueOf(result.getString("status")));
95 return version;
96 }
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;
110 }
112 @Override
113 public void save(Version instance) throws SQLException {
114 Objects.requireNonNull(instance.getName());
115 Objects.requireNonNull(instance.getProject());
116 insert.setInt(1, instance.getProject().getId());
117 insert.setString(2, instance.getName());
118 insert.setInt(3, instance.getOrdinal());
119 insert.setString(4, instance.getStatus().name());
120 insert.executeUpdate();
121 }
123 @Override
124 public boolean update(Version instance) throws SQLException {
125 if (instance.getId() < 0) return false;
126 Objects.requireNonNull(instance.getName());
127 update.setString(1, instance.getName());
128 update.setInt(2, instance.getOrdinal());
129 update.setString(3, instance.getStatus().name());
130 update.setInt(4, instance.getId());
131 return update.executeUpdate() > 0;
132 }
134 @Override
135 public List<Version> list(Project project) throws SQLException {
136 list.setInt(1, project.getId());
137 List<Version> versions = new ArrayList<>();
138 try (var result = list.executeQuery()) {
139 while (result.next()) {
140 final var v = mapColumns(result);
141 v.setProject(project);
142 versions.add(v);
143 }
144 }
145 return versions;
146 }
148 @Override
149 public Version find(int id) throws SQLException {
150 find.setInt(1, id);
151 try (var result = find.executeQuery()) {
152 if (result.next()) {
153 return mapColumns(result);
154 } else {
155 return null;
156 }
157 }
158 }
160 @Override
161 public VersionStatistics statsOpenedIssues(Version version) throws SQLException {
162 return versionStatistics(version, issuesAffected);
163 }
165 @Override
166 public VersionStatistics statsScheduledIssues(Version version) throws SQLException {
167 return versionStatistics(version, issuesScheduled);
168 }
170 @Override
171 public VersionStatistics statsResolvedIssues(Version version) throws SQLException {
172 return versionStatistics(version, issuesResolved);
173 }
174 }