--- a/src/main/java/de/uapcore/lightpit/dao/postgres/PGIssueDao.java Fri May 22 17:26:27 2020 +0200
+++ b/src/main/java/de/uapcore/lightpit/dao/postgres/PGIssueDao.java Fri May 22 21:23:57 2020 +0200
@@ -43,63 +43,92 @@
public final class PGIssueDao implements IssueDao {
- private final PreparedStatement insert, update, list, find;
+ private final PreparedStatement insert, update, list, find, affectedVersions, scheduledVersions, resolvedVersions;
public PGIssueDao(Connection connection) throws SQLException {
list = connection.prepareStatement(
- "select issue.id, issue.project, issue.status, issue.category, issue.subject, issue.description, " +
- "vplan.id, vplan.name, vdone.id, vdone.name, " +
- "issue.created, issue.updated, issue.eta " +
- "from lpit_issue issue " +
- "left join lpit_version vplan on vplan.id = version_plan " +
- "left join lpit_version vdone on vdone.id = version_done " +
- "where issue.project = ? ");
+ "select issueid, project, status, category, subject, description, " +
+ "userid, username, givenname, lastname, mail, " +
+ "created, updated, eta " +
+ "from lpit_issue " +
+ "left join lpit_user on userid = assignee " +
+ "where project = ? ");
find = connection.prepareStatement(
- "select issue.id, issue.project, issue.status, issue.category, issue.subject, issue.description, " +
- "vplan.id, vplan.name, vdone.id, vdone.name, " +
- "issue.created, issue.updated, issue.eta " +
- "from lpit_issue issue " +
- "left join lpit_version vplan on vplan.id = version_plan " +
- "left join lpit_version vdone on vdone.id = version_done " +
- "where issue.id = ? ");
+ "select issueid, project, status, category, subject, description, " +
+ "userid, username, givenname, lastname, mail, " +
+ "created, updated, eta " +
+ "from lpit_issue " +
+ "left join lpit_user on userid = assignee " +
+ "where issueid = ? ");
insert = connection.prepareStatement(
- "insert into lpit_issue (project, status, category, subject, description, version_plan, version_done, eta) " +
- "values (?, ?::issue_status, ?::issue_category, ?, ?, ?, ?, ?)"
+ "insert into lpit_issue (project, 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, " +
- "subject = ?, description = ?, version_plan = ?, version_done = ?, eta = ? where id = ?"
+ "subject = ?, description = ?, assignee = ?, eta = ? where issueid = ?"
+ );
+
+ affectedVersions = connection.prepareStatement(
+ "select v.versionid, v.name, v.status, v.ordinal " +
+ "from lpit_version v join lpit_issue_affected_version using (versionid) " +
+ "where issueid = ? " +
+ "order by v.ordinal, v.name"
+ );
+
+ scheduledVersions = connection.prepareStatement(
+ "select v.versionid, v.name, v.status, v.ordinal " +
+ "from lpit_version v join lpit_issue_scheduled_version using (versionid) " +
+ "where issueid = ? " +
+ "order by v.ordinal, v.name"
+ );
+
+ resolvedVersions = connection.prepareStatement(
+ "select v.versionid, v.name, v.status, v.ordinal " +
+ "from lpit_version v join lpit_issue_resolved_version using (versionid) " +
+ "where issueid = ? " +
+ "order by v.ordinal, v.name"
);
}
- private Version obtainVersion(ResultSet result, Project project, String prefix) throws SQLException {
- final int vplan = result.getInt(prefix + "id");
- if (vplan > 0) {
- final var ver = new Version(vplan, project);
- ver.setName(result.getString(prefix + "name"));
- return ver;
+ private User obtainAssignee(ResultSet result) throws SQLException {
+ final int id = result.getInt("userid");
+ if (id != 0) {
+ final var user = new User(id);
+ user.setUsername(result.getString("username"));
+ user.setGivenname(result.getString("givenname"));
+ user.setLastname(result.getString("lastname"));
+ user.setMail(result.getString("mail"));
+ return user;
} else {
return null;
}
}
- public Issue mapColumns(ResultSet result) throws SQLException {
- final var project = new Project(result.getInt("issue.project"));
- final var issue = new Issue(result.getInt("issue.id"), project);
- issue.setStatus(IssueStatus.valueOf(result.getString("issue.status")));
- issue.setCategory(IssueCategory.valueOf(result.getString("issue.category")));
- issue.setSubject(result.getString("issue.subject"));
- issue.setDescription(result.getString("issue.description"));
- issue.setScheduledVersion(obtainVersion(result, project, "vplan."));
- issue.setResolvedVersion(obtainVersion(result, project, "vdone."));
- issue.setCreated(result.getTimestamp("issue.created"));
- issue.setUpdated(result.getTimestamp("issue.updated"));
- issue.setEta(result.getDate("issue.eta"));
+ private Issue mapColumns(ResultSet result) throws SQLException {
+ final var project = new Project(result.getInt("project"));
+ final var issue = new Issue(result.getInt("issueid"), project);
+ issue.setStatus(IssueStatus.valueOf(result.getString("status")));
+ issue.setCategory(IssueCategory.valueOf(result.getString("category")));
+ issue.setSubject(result.getString("subject"));
+ issue.setDescription(result.getString("description"));
+ issue.setAssignee(obtainAssignee(result));
+ issue.setCreated(result.getTimestamp("created"));
+ issue.setUpdated(result.getTimestamp("updated"));
+ issue.setEta(result.getDate("eta"));
return issue;
}
+ private Version mapVersion(ResultSet result, Project project) throws SQLException {
+ final var version = new Version(result.getInt("v.versionid"), project);
+ version.setName(result.getString("v.name"));
+ version.setOrdinal(result.getInt("v.ordinal"));
+ version.setStatus(VersionStatus.valueOf(result.getString("v.status")));
+ return version;
+ }
+
@Override
public void save(Issue instance) throws SQLException {
Objects.requireNonNull(instance.getSubject());
@@ -109,36 +138,38 @@
insert.setString(3, instance.getCategory().name());
insert.setString(4, instance.getSubject());
setStringOrNull(insert, 5, instance.getDescription());
- setForeignKeyOrNull(insert, 6, instance.getScheduledVersion(), Version::getId);
- setForeignKeyOrNull(insert, 7, instance.getResolvedVersion(), Version::getId);
- setDateOrNull(insert, 8, instance.getEta());
- insert.executeUpdate();
+ setForeignKeyOrNull(insert, 6, instance.getAssignee(), User::getId);
+ setDateOrNull(insert, 7, instance.getEta());
+ // insert and retrieve the ID
+ final var rs = insert.executeQuery();
+ rs.next();
+ instance.setId(rs.getInt(1));
}
@Override
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.getScheduledVersion(), Version::getId);
- setForeignKeyOrNull(update, 6, instance.getResolvedVersion(), Version::getId);
- setDateOrNull(update, 7, instance.getEta());
- update.setInt(8, instance.getId());
+ setForeignKeyOrNull(update, 5, instance.getAssignee(), User::getId);
+ setDateOrNull(update, 6, instance.getEta());
+ update.setInt(7, instance.getId());
return update.executeUpdate() > 0;
}
@Override
public List<Issue> list(Project project) throws SQLException {
list.setInt(1, project.getId());
- List<Issue> versions = new ArrayList<>();
+ List<Issue> issues = new ArrayList<>();
try (var result = list.executeQuery()) {
while (result.next()) {
- versions.add(mapColumns(result));
+ issues.add(mapColumns(result));
}
}
- return versions;
+ return issues;
}
@Override
@@ -152,4 +183,23 @@
}
}
}
+
+ private List<Version> listVersions(PreparedStatement stmt, Issue issue) throws SQLException {
+ stmt.setInt(1, issue.getId());
+ List<Version> versions = new ArrayList<>();
+ try (var result = stmt.executeQuery()) {
+ while (result.next()) {
+ versions.add(mapVersion(result, issue.getProject()));
+ }
+ }
+ return versions;
+ }
+
+ @Override
+ public void joinVersionInformation(Issue issue) throws SQLException {
+ Objects.requireNonNull(issue.getProject());
+ issue.setAffectedVersions(listVersions(affectedVersions, issue));
+ issue.setScheduledVersions(listVersions(scheduledVersions, issue));
+ issue.setResolvedVersions(listVersions(resolvedVersions, issue));
+ }
}