--- a/src/main/java/de/uapcore/lightpit/dao/postgres/PGIssueDao.java Sat Oct 17 15:21:56 2020 +0200
+++ b/src/main/java/de/uapcore/lightpit/dao/postgres/PGIssueDao.java Sat Oct 17 19:56:50 2020 +0200
@@ -31,13 +31,11 @@
import de.uapcore.lightpit.dao.IssueDao;
import de.uapcore.lightpit.entities.*;
-import java.sql.Connection;
-import java.sql.PreparedStatement;
-import java.sql.ResultSet;
-import java.sql.SQLException;
+import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Objects;
+import java.util.Optional;
import static de.uapcore.lightpit.dao.Functions.*;
@@ -51,43 +49,50 @@
public PGIssueDao(Connection connection) throws SQLException {
list = connection.prepareStatement(
- "select issueid, project, p.name as projectname, status, category, subject, i.description, " +
+ "select issueid, i.project, p.name as projectname, component, c.name as componentname, " +
+ "status, category, subject, i.description, " +
"userid, username, givenname, lastname, mail, " +
"created, updated, eta " +
"from lpit_issue i " +
- "join lpit_project p on project = projectid " +
+ "join lpit_project p on i.project = projectid " +
+ "left join lpit_component c on component = c.id " +
"left join lpit_user on userid = assignee " +
- "where project = ? ");
+ "where i.project = ? and coalesce(component, -1) = coalesce(?, component, -1)");
listForVersion = connection.prepareStatement(
"with issue_version as ( "+
"select issueid, versionid from lpit_issue_affected_version union "+
"select issueid, versionid from lpit_issue_resolved_version) "+
- "select issueid, project, p.name as projectname, status, category, subject, i.description, " +
+ "select issueid, i.project, p.name as projectname, component, c.name as componentname, " +
+ "status, category, subject, i.description, " +
"userid, username, givenname, lastname, mail, " +
"created, updated, eta " +
"from lpit_issue i " +
- "join lpit_project p on project = projectid " +
+ "join lpit_project p on i.project = projectid " +
+ "left join lpit_component c on component = c.id " +
"left join issue_version using (issueid) "+
"left join lpit_user on userid = assignee " +
- "where coalesce(versionid,-1) = ? "
+ "where coalesce(versionid,-1) = ? and coalesce(component, -1) = coalesce(?, component, -1)"
);
find = connection.prepareStatement(
- "select issueid, project, p.name as projectname, status, category, subject, i.description, " +
+ "select issueid, i.project, p.name as projectname, component, c.name as componentname, " +
+ "status, category, subject, i.description, " +
"userid, username, givenname, lastname, mail, " +
"created, updated, eta " +
"from lpit_issue i " +
- "left join lpit_project p on project = projectid " +
+ "join lpit_project p on i.project = projectid " +
+ "left join lpit_component c on component = c.id " +
"left join lpit_user on userid = assignee " +
"where issueid = ? ");
insert = connection.prepareStatement(
- "insert into lpit_issue (project, status, category, subject, description, assignee, eta) " +
+ "insert into lpit_issue (project, component, status, category, subject, description, assignee, eta) " +
"values (?, ?::issue_status, ?::issue_category, ?, ?, ?, ?) returning issueid"
);
update = connection.prepareStatement(
- "update lpit_issue set updated = now(), status = ?::issue_status, category = ?::issue_category, " +
+ "update lpit_issue set " +
+ "updated = now(), component = ?, status = ?::issue_status, category = ?::issue_category, " +
"subject = ?, description = ?, assignee = ?, eta = ? where issueid = ?"
);
@@ -123,8 +128,15 @@
private Issue mapColumns(ResultSet result) throws SQLException {
final var project = new Project(result.getInt("project"));
project.setName(result.getString("projectname"));
+ var component = new Component(result.getInt("component"));
+ if (result.wasNull()) {
+ component = null;
+ } else {
+ component.setName(result.getString("componentname"));
+ }
final var issue = new Issue(result.getInt("issueid"));
issue.setProject(project);
+ issue.setComponent(component);
issue.setStatus(IssueStatus.valueOf(result.getString("status")));
issue.setCategory(IssueCategory.valueOf(result.getString("category")));
issue.setSubject(result.getString("subject"));
@@ -161,17 +173,24 @@
}
}
+ private int setData(PreparedStatement stmt, int column, Issue instance) throws SQLException {
+ setForeignKeyOrNull(stmt, ++column, instance.getComponent(), Component::getId);
+ stmt.setString(++column, instance.getStatus().name());
+ stmt.setString(++column, instance.getCategory().name());
+ stmt.setString(++column, instance.getSubject());
+ setStringOrNull(stmt, ++column, instance.getDescription());
+ setForeignKeyOrNull(stmt, ++column, instance.getAssignee(), User::getId);
+ setDateOrNull(stmt, ++column, instance.getEta());
+ return column;
+ }
+
@Override
public void save(Issue instance, Project project) throws SQLException {
Objects.requireNonNull(instance.getSubject());
instance.setProject(project);
- insert.setInt(1, instance.getProject().getId());
- insert.setString(2, instance.getStatus().name());
- insert.setString(3, instance.getCategory().name());
- insert.setString(4, instance.getSubject());
- setStringOrNull(insert, 5, instance.getDescription());
- setForeignKeyOrNull(insert, 6, instance.getAssignee(), User::getId);
- setDateOrNull(insert, 7, instance.getEta());
+ int column = 0;
+ insert.setInt(++column, instance.getProject().getId());
+ setData(insert, column, instance);
// insert and retrieve the ID
final var rs = insert.executeQuery();
rs.next();
@@ -183,13 +202,8 @@
public boolean update(Issue instance) throws SQLException {
if (instance.getId() < 0) return false;
Objects.requireNonNull(instance.getSubject());
- update.setString(1, instance.getStatus().name());
- update.setString(2, instance.getCategory().name());
- update.setString(3, instance.getSubject());
- setStringOrNull(update, 4, instance.getDescription());
- setForeignKeyOrNull(update, 5, instance.getAssignee(), User::getId);
- setDateOrNull(update, 6, instance.getEta());
- update.setInt(7, instance.getId());
+ int column = setData(update, 0, instance);
+ update.setInt(++column, instance.getId());
boolean success = update.executeUpdate() > 0;
if (success) {
updateVersionLists(instance);
@@ -199,8 +213,7 @@
}
}
- private List<Issue> list(PreparedStatement query, int arg) throws SQLException {
- query.setInt(1, arg);
+ private List<Issue> executeQuery(PreparedStatement query) throws SQLException {
List<Issue> issues = new ArrayList<>();
try (var result = query.executeQuery()) {
while (result.next()) {
@@ -212,12 +225,30 @@
@Override
public List<Issue> list(Project project) throws SQLException {
- return list(list, project.getId());
+ list.setInt(1, project.getId());
+ list.setNull(2, Types.INTEGER);
+ return executeQuery(list);
}
@Override
- public List<Issue> list(Version version) throws SQLException {
- return list(listForVersion, version == null ? -1 : version.getId());
+ public List<Issue> list(Project project, Component component, Version version) throws SQLException {
+ listForVersion.setInt(1, Optional.ofNullable(version).map(Version::getId).orElse(-1));
+ listForVersion.setInt(2, Optional.ofNullable(component).map(Component::getId).orElse(-1));
+ return executeQuery(listForVersion);
+ }
+
+ @Override
+ public List<Issue> list(Project project, Version version) throws SQLException {
+ listForVersion.setInt(1, Optional.ofNullable(version).map(Version::getId).orElse(-1));
+ listForVersion.setNull(2, Types.INTEGER);
+ return executeQuery(listForVersion);
+ }
+
+ @Override
+ public List<Issue> list(Project project, Component component) throws SQLException {
+ list.setInt(1, project.getId());
+ list.setInt(2, Optional.ofNullable(component).map(Component::getId).orElse(-1));
+ return executeQuery(list);
}
@Override