1.1 --- a/src/main/java/de/uapcore/lightpit/dao/postgres/PGIssueDao.java Sat Oct 17 15:21:56 2020 +0200
1.2 +++ b/src/main/java/de/uapcore/lightpit/dao/postgres/PGIssueDao.java Sat Oct 17 19:56:50 2020 +0200
1.3 @@ -31,13 +31,11 @@
1.4 import de.uapcore.lightpit.dao.IssueDao;
1.5 import de.uapcore.lightpit.entities.*;
1.6
1.7 -import java.sql.Connection;
1.8 -import java.sql.PreparedStatement;
1.9 -import java.sql.ResultSet;
1.10 -import java.sql.SQLException;
1.11 +import java.sql.*;
1.12 import java.util.ArrayList;
1.13 import java.util.List;
1.14 import java.util.Objects;
1.15 +import java.util.Optional;
1.16
1.17 import static de.uapcore.lightpit.dao.Functions.*;
1.18
1.19 @@ -51,43 +49,50 @@
1.20
1.21 public PGIssueDao(Connection connection) throws SQLException {
1.22 list = connection.prepareStatement(
1.23 - "select issueid, project, p.name as projectname, status, category, subject, i.description, " +
1.24 + "select issueid, i.project, p.name as projectname, component, c.name as componentname, " +
1.25 + "status, category, subject, i.description, " +
1.26 "userid, username, givenname, lastname, mail, " +
1.27 "created, updated, eta " +
1.28 "from lpit_issue i " +
1.29 - "join lpit_project p on project = projectid " +
1.30 + "join lpit_project p on i.project = projectid " +
1.31 + "left join lpit_component c on component = c.id " +
1.32 "left join lpit_user on userid = assignee " +
1.33 - "where project = ? ");
1.34 + "where i.project = ? and coalesce(component, -1) = coalesce(?, component, -1)");
1.35
1.36 listForVersion = connection.prepareStatement(
1.37 "with issue_version as ( "+
1.38 "select issueid, versionid from lpit_issue_affected_version union "+
1.39 "select issueid, versionid from lpit_issue_resolved_version) "+
1.40 - "select issueid, project, p.name as projectname, status, category, subject, i.description, " +
1.41 + "select issueid, i.project, p.name as projectname, component, c.name as componentname, " +
1.42 + "status, category, subject, i.description, " +
1.43 "userid, username, givenname, lastname, mail, " +
1.44 "created, updated, eta " +
1.45 "from lpit_issue i " +
1.46 - "join lpit_project p on project = projectid " +
1.47 + "join lpit_project p on i.project = projectid " +
1.48 + "left join lpit_component c on component = c.id " +
1.49 "left join issue_version using (issueid) "+
1.50 "left join lpit_user on userid = assignee " +
1.51 - "where coalesce(versionid,-1) = ? "
1.52 + "where coalesce(versionid,-1) = ? and coalesce(component, -1) = coalesce(?, component, -1)"
1.53 );
1.54
1.55 find = connection.prepareStatement(
1.56 - "select issueid, project, p.name as projectname, status, category, subject, i.description, " +
1.57 + "select issueid, i.project, p.name as projectname, component, c.name as componentname, " +
1.58 + "status, category, subject, i.description, " +
1.59 "userid, username, givenname, lastname, mail, " +
1.60 "created, updated, eta " +
1.61 "from lpit_issue i " +
1.62 - "left join lpit_project p on project = projectid " +
1.63 + "join lpit_project p on i.project = projectid " +
1.64 + "left join lpit_component c on component = c.id " +
1.65 "left join lpit_user on userid = assignee " +
1.66 "where issueid = ? ");
1.67
1.68 insert = connection.prepareStatement(
1.69 - "insert into lpit_issue (project, status, category, subject, description, assignee, eta) " +
1.70 + "insert into lpit_issue (project, component, status, category, subject, description, assignee, eta) " +
1.71 "values (?, ?::issue_status, ?::issue_category, ?, ?, ?, ?) returning issueid"
1.72 );
1.73 update = connection.prepareStatement(
1.74 - "update lpit_issue set updated = now(), status = ?::issue_status, category = ?::issue_category, " +
1.75 + "update lpit_issue set " +
1.76 + "updated = now(), component = ?, status = ?::issue_status, category = ?::issue_category, " +
1.77 "subject = ?, description = ?, assignee = ?, eta = ? where issueid = ?"
1.78 );
1.79
1.80 @@ -123,8 +128,15 @@
1.81 private Issue mapColumns(ResultSet result) throws SQLException {
1.82 final var project = new Project(result.getInt("project"));
1.83 project.setName(result.getString("projectname"));
1.84 + var component = new Component(result.getInt("component"));
1.85 + if (result.wasNull()) {
1.86 + component = null;
1.87 + } else {
1.88 + component.setName(result.getString("componentname"));
1.89 + }
1.90 final var issue = new Issue(result.getInt("issueid"));
1.91 issue.setProject(project);
1.92 + issue.setComponent(component);
1.93 issue.setStatus(IssueStatus.valueOf(result.getString("status")));
1.94 issue.setCategory(IssueCategory.valueOf(result.getString("category")));
1.95 issue.setSubject(result.getString("subject"));
1.96 @@ -161,17 +173,24 @@
1.97 }
1.98 }
1.99
1.100 + private int setData(PreparedStatement stmt, int column, Issue instance) throws SQLException {
1.101 + setForeignKeyOrNull(stmt, ++column, instance.getComponent(), Component::getId);
1.102 + stmt.setString(++column, instance.getStatus().name());
1.103 + stmt.setString(++column, instance.getCategory().name());
1.104 + stmt.setString(++column, instance.getSubject());
1.105 + setStringOrNull(stmt, ++column, instance.getDescription());
1.106 + setForeignKeyOrNull(stmt, ++column, instance.getAssignee(), User::getId);
1.107 + setDateOrNull(stmt, ++column, instance.getEta());
1.108 + return column;
1.109 + }
1.110 +
1.111 @Override
1.112 public void save(Issue instance, Project project) throws SQLException {
1.113 Objects.requireNonNull(instance.getSubject());
1.114 instance.setProject(project);
1.115 - insert.setInt(1, instance.getProject().getId());
1.116 - insert.setString(2, instance.getStatus().name());
1.117 - insert.setString(3, instance.getCategory().name());
1.118 - insert.setString(4, instance.getSubject());
1.119 - setStringOrNull(insert, 5, instance.getDescription());
1.120 - setForeignKeyOrNull(insert, 6, instance.getAssignee(), User::getId);
1.121 - setDateOrNull(insert, 7, instance.getEta());
1.122 + int column = 0;
1.123 + insert.setInt(++column, instance.getProject().getId());
1.124 + setData(insert, column, instance);
1.125 // insert and retrieve the ID
1.126 final var rs = insert.executeQuery();
1.127 rs.next();
1.128 @@ -183,13 +202,8 @@
1.129 public boolean update(Issue instance) throws SQLException {
1.130 if (instance.getId() < 0) return false;
1.131 Objects.requireNonNull(instance.getSubject());
1.132 - update.setString(1, instance.getStatus().name());
1.133 - update.setString(2, instance.getCategory().name());
1.134 - update.setString(3, instance.getSubject());
1.135 - setStringOrNull(update, 4, instance.getDescription());
1.136 - setForeignKeyOrNull(update, 5, instance.getAssignee(), User::getId);
1.137 - setDateOrNull(update, 6, instance.getEta());
1.138 - update.setInt(7, instance.getId());
1.139 + int column = setData(update, 0, instance);
1.140 + update.setInt(++column, instance.getId());
1.141 boolean success = update.executeUpdate() > 0;
1.142 if (success) {
1.143 updateVersionLists(instance);
1.144 @@ -199,8 +213,7 @@
1.145 }
1.146 }
1.147
1.148 - private List<Issue> list(PreparedStatement query, int arg) throws SQLException {
1.149 - query.setInt(1, arg);
1.150 + private List<Issue> executeQuery(PreparedStatement query) throws SQLException {
1.151 List<Issue> issues = new ArrayList<>();
1.152 try (var result = query.executeQuery()) {
1.153 while (result.next()) {
1.154 @@ -212,12 +225,30 @@
1.155
1.156 @Override
1.157 public List<Issue> list(Project project) throws SQLException {
1.158 - return list(list, project.getId());
1.159 + list.setInt(1, project.getId());
1.160 + list.setNull(2, Types.INTEGER);
1.161 + return executeQuery(list);
1.162 }
1.163
1.164 @Override
1.165 - public List<Issue> list(Version version) throws SQLException {
1.166 - return list(listForVersion, version == null ? -1 : version.getId());
1.167 + public List<Issue> list(Project project, Component component, Version version) throws SQLException {
1.168 + listForVersion.setInt(1, Optional.ofNullable(version).map(Version::getId).orElse(-1));
1.169 + listForVersion.setInt(2, Optional.ofNullable(component).map(Component::getId).orElse(-1));
1.170 + return executeQuery(listForVersion);
1.171 + }
1.172 +
1.173 + @Override
1.174 + public List<Issue> list(Project project, Version version) throws SQLException {
1.175 + listForVersion.setInt(1, Optional.ofNullable(version).map(Version::getId).orElse(-1));
1.176 + listForVersion.setNull(2, Types.INTEGER);
1.177 + return executeQuery(listForVersion);
1.178 + }
1.179 +
1.180 + @Override
1.181 + public List<Issue> list(Project project, Component component) throws SQLException {
1.182 + list.setInt(1, project.getId());
1.183 + list.setInt(2, Optional.ofNullable(component).map(Component::getId).orElse(-1));
1.184 + return executeQuery(list);
1.185 }
1.186
1.187 @Override
