--- a/src/main/java/de/uapcore/lightpit/dao/postgres/PGIssueDao.java Sat May 30 15:28:27 2020 +0200
+++ b/src/main/java/de/uapcore/lightpit/dao/postgres/PGIssueDao.java Sat May 30 18:05:06 2020 +0200
@@ -43,22 +43,27 @@
public final class PGIssueDao implements IssueDao {
- private final PreparedStatement insert, update, list, find, affectedVersions, scheduledVersions, resolvedVersions;
+ private final PreparedStatement insert, update, list, find;
+ private final PreparedStatement affectedVersions, scheduledVersions, resolvedVersions;
+ private final PreparedStatement clearAffected, clearScheduled, clearResolved;
+ private final PreparedStatement insertAffected, insertScheduled, insertResolved;
public PGIssueDao(Connection connection) throws SQLException {
list = connection.prepareStatement(
- "select issueid, project, status, category, subject, description, " +
+ "select issueid, project, p.name as projectname, status, category, subject, i.description, " +
"userid, username, givenname, lastname, mail, " +
"created, updated, eta " +
- "from lpit_issue " +
+ "from lpit_issue i " +
+ "left join lpit_project p on project = projectid " +
"left join lpit_user on userid = assignee " +
"where project = ? ");
find = connection.prepareStatement(
- "select issueid, project, status, category, subject, description, " +
+ "select issueid, project, p.name as projectname, status, category, subject, i.description, " +
"userid, username, givenname, lastname, mail, " +
"created, updated, eta " +
- "from lpit_issue " +
+ "from lpit_issue i " +
+ "left join lpit_project p on project = projectid " +
"left join lpit_user on userid = assignee " +
"where issueid = ? ");
@@ -72,25 +77,31 @@
);
affectedVersions = connection.prepareStatement(
- "select v.versionid, v.name, v.status, v.ordinal " +
- "from lpit_version v join lpit_issue_affected_version using (versionid) " +
+ "select versionid, name, status, ordinal " +
+ "from lpit_version join lpit_issue_affected_version using (versionid) " +
"where issueid = ? " +
- "order by v.ordinal, v.name"
+ "order by ordinal, name"
);
+ clearAffected = connection.prepareStatement("delete from lpit_issue_affected_version where issueid = ?");
+ insertAffected = connection.prepareStatement("insert into lpit_issue_affected_version (issueid, versionid) values (?,?)");
scheduledVersions = connection.prepareStatement(
- "select v.versionid, v.name, v.status, v.ordinal " +
- "from lpit_version v join lpit_issue_scheduled_version using (versionid) " +
+ "select versionid, name, status, ordinal " +
+ "from lpit_version join lpit_issue_scheduled_version using (versionid) " +
"where issueid = ? " +
- "order by v.ordinal, v.name"
+ "order by ordinal, name"
);
+ clearScheduled = connection.prepareStatement("delete from lpit_issue_scheduled_version where issueid = ?");
+ insertScheduled = connection.prepareStatement("insert into lpit_issue_scheduled_version (issueid, versionid) values (?,?)");
resolvedVersions = connection.prepareStatement(
- "select v.versionid, v.name, v.status, v.ordinal " +
+ "select versionid, name, status, ordinal " +
"from lpit_version v join lpit_issue_resolved_version using (versionid) " +
"where issueid = ? " +
- "order by v.ordinal, v.name"
+ "order by ordinal, name"
);
+ clearResolved = connection.prepareStatement("delete from lpit_issue_resolved_version where issueid = ?");
+ insertResolved = connection.prepareStatement("insert into lpit_issue_resolved_version (issueid, versionid) values (?,?)");
}
private User obtainAssignee(ResultSet result) throws SQLException {
@@ -109,6 +120,7 @@
private Issue mapColumns(ResultSet result) throws SQLException {
final var project = new Project(result.getInt("project"));
+ project.setName(result.getString("projectname"));
final var issue = new Issue(result.getInt("issueid"), project);
issue.setStatus(IssueStatus.valueOf(result.getString("status")));
issue.setCategory(IssueCategory.valueOf(result.getString("category")));
@@ -122,13 +134,37 @@
}
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")));
+ final var version = new Version(result.getInt("versionid"), project);
+ version.setName(result.getString("name"));
+ version.setOrdinal(result.getInt("ordinal"));
+ version.setStatus(VersionStatus.valueOf(result.getString("status")));
return version;
}
+ private void updateVersionLists(Issue instance) throws SQLException {
+ clearAffected.setInt(1, instance.getId());
+ clearScheduled.setInt(1, instance.getId());
+ clearResolved.setInt(1, instance.getId());
+ insertAffected.setInt(1, instance.getId());
+ insertScheduled.setInt(1, instance.getId());
+ insertResolved.setInt(1, instance.getId());
+ clearAffected.executeUpdate();
+ clearScheduled.executeUpdate();
+ clearResolved.executeUpdate();
+ for (Version v : instance.getAffectedVersions()) {
+ insertAffected.setInt(2, v.getId());
+ insertAffected.executeUpdate();
+ }
+ for (Version v : instance.getScheduledVersions()) {
+ insertScheduled.setInt(2, v.getId());
+ insertScheduled.executeUpdate();
+ }
+ for (Version v : instance.getResolvedVersions()) {
+ insertResolved.setInt(2, v.getId());
+ insertResolved.executeUpdate();
+ }
+ }
+
@Override
public void save(Issue instance) throws SQLException {
Objects.requireNonNull(instance.getSubject());
@@ -144,6 +180,7 @@
final var rs = insert.executeQuery();
rs.next();
instance.setId(rs.getInt(1));
+ updateVersionLists(instance);
}
@Override
@@ -157,7 +194,13 @@
setForeignKeyOrNull(update, 5, instance.getAssignee(), User::getId);
setDateOrNull(update, 6, instance.getEta());
update.setInt(7, instance.getId());
- return update.executeUpdate() > 0;
+ boolean success = update.executeUpdate() > 0;
+ if (success) {
+ updateVersionLists(instance);
+ return true;
+ } else {
+ return false;
+ }
}
@Override