1.1 --- a/src/main/java/de/uapcore/lightpit/dao/postgres/PGIssueDao.java Fri May 22 17:26:27 2020 +0200
1.2 +++ b/src/main/java/de/uapcore/lightpit/dao/postgres/PGIssueDao.java Fri May 22 21:23:57 2020 +0200
1.3 @@ -43,63 +43,92 @@
1.4
1.5 public final class PGIssueDao implements IssueDao {
1.6
1.7 - private final PreparedStatement insert, update, list, find;
1.8 + private final PreparedStatement insert, update, list, find, affectedVersions, scheduledVersions, resolvedVersions;
1.9
1.10 public PGIssueDao(Connection connection) throws SQLException {
1.11 list = connection.prepareStatement(
1.12 - "select issue.id, issue.project, issue.status, issue.category, issue.subject, issue.description, " +
1.13 - "vplan.id, vplan.name, vdone.id, vdone.name, " +
1.14 - "issue.created, issue.updated, issue.eta " +
1.15 - "from lpit_issue issue " +
1.16 - "left join lpit_version vplan on vplan.id = version_plan " +
1.17 - "left join lpit_version vdone on vdone.id = version_done " +
1.18 - "where issue.project = ? ");
1.19 + "select issueid, project, status, category, subject, description, " +
1.20 + "userid, username, givenname, lastname, mail, " +
1.21 + "created, updated, eta " +
1.22 + "from lpit_issue " +
1.23 + "left join lpit_user on userid = assignee " +
1.24 + "where project = ? ");
1.25
1.26 find = connection.prepareStatement(
1.27 - "select issue.id, issue.project, issue.status, issue.category, issue.subject, issue.description, " +
1.28 - "vplan.id, vplan.name, vdone.id, vdone.name, " +
1.29 - "issue.created, issue.updated, issue.eta " +
1.30 - "from lpit_issue issue " +
1.31 - "left join lpit_version vplan on vplan.id = version_plan " +
1.32 - "left join lpit_version vdone on vdone.id = version_done " +
1.33 - "where issue.id = ? ");
1.34 + "select issueid, project, status, category, subject, description, " +
1.35 + "userid, username, givenname, lastname, mail, " +
1.36 + "created, updated, eta " +
1.37 + "from lpit_issue " +
1.38 + "left join lpit_user on userid = assignee " +
1.39 + "where issueid = ? ");
1.40
1.41 insert = connection.prepareStatement(
1.42 - "insert into lpit_issue (project, status, category, subject, description, version_plan, version_done, eta) " +
1.43 - "values (?, ?::issue_status, ?::issue_category, ?, ?, ?, ?, ?)"
1.44 + "insert into lpit_issue (project, status, category, subject, description, assignee, eta) " +
1.45 + "values (?, ?::issue_status, ?::issue_category, ?, ?, ?, ?) returning issueid"
1.46 );
1.47 update = connection.prepareStatement(
1.48 "update lpit_issue set updated = now(), status = ?::issue_status, category = ?::issue_category, " +
1.49 - "subject = ?, description = ?, version_plan = ?, version_done = ?, eta = ? where id = ?"
1.50 + "subject = ?, description = ?, assignee = ?, eta = ? where issueid = ?"
1.51 + );
1.52 +
1.53 + affectedVersions = connection.prepareStatement(
1.54 + "select v.versionid, v.name, v.status, v.ordinal " +
1.55 + "from lpit_version v join lpit_issue_affected_version using (versionid) " +
1.56 + "where issueid = ? " +
1.57 + "order by v.ordinal, v.name"
1.58 + );
1.59 +
1.60 + scheduledVersions = connection.prepareStatement(
1.61 + "select v.versionid, v.name, v.status, v.ordinal " +
1.62 + "from lpit_version v join lpit_issue_scheduled_version using (versionid) " +
1.63 + "where issueid = ? " +
1.64 + "order by v.ordinal, v.name"
1.65 + );
1.66 +
1.67 + resolvedVersions = connection.prepareStatement(
1.68 + "select v.versionid, v.name, v.status, v.ordinal " +
1.69 + "from lpit_version v join lpit_issue_resolved_version using (versionid) " +
1.70 + "where issueid = ? " +
1.71 + "order by v.ordinal, v.name"
1.72 );
1.73 }
1.74
1.75 - private Version obtainVersion(ResultSet result, Project project, String prefix) throws SQLException {
1.76 - final int vplan = result.getInt(prefix + "id");
1.77 - if (vplan > 0) {
1.78 - final var ver = new Version(vplan, project);
1.79 - ver.setName(result.getString(prefix + "name"));
1.80 - return ver;
1.81 + private User obtainAssignee(ResultSet result) throws SQLException {
1.82 + final int id = result.getInt("userid");
1.83 + if (id != 0) {
1.84 + final var user = new User(id);
1.85 + user.setUsername(result.getString("username"));
1.86 + user.setGivenname(result.getString("givenname"));
1.87 + user.setLastname(result.getString("lastname"));
1.88 + user.setMail(result.getString("mail"));
1.89 + return user;
1.90 } else {
1.91 return null;
1.92 }
1.93 }
1.94
1.95 - public Issue mapColumns(ResultSet result) throws SQLException {
1.96 - final var project = new Project(result.getInt("issue.project"));
1.97 - final var issue = new Issue(result.getInt("issue.id"), project);
1.98 - issue.setStatus(IssueStatus.valueOf(result.getString("issue.status")));
1.99 - issue.setCategory(IssueCategory.valueOf(result.getString("issue.category")));
1.100 - issue.setSubject(result.getString("issue.subject"));
1.101 - issue.setDescription(result.getString("issue.description"));
1.102 - issue.setScheduledVersion(obtainVersion(result, project, "vplan."));
1.103 - issue.setResolvedVersion(obtainVersion(result, project, "vdone."));
1.104 - issue.setCreated(result.getTimestamp("issue.created"));
1.105 - issue.setUpdated(result.getTimestamp("issue.updated"));
1.106 - issue.setEta(result.getDate("issue.eta"));
1.107 + private Issue mapColumns(ResultSet result) throws SQLException {
1.108 + final var project = new Project(result.getInt("project"));
1.109 + final var issue = new Issue(result.getInt("issueid"), project);
1.110 + issue.setStatus(IssueStatus.valueOf(result.getString("status")));
1.111 + issue.setCategory(IssueCategory.valueOf(result.getString("category")));
1.112 + issue.setSubject(result.getString("subject"));
1.113 + issue.setDescription(result.getString("description"));
1.114 + issue.setAssignee(obtainAssignee(result));
1.115 + issue.setCreated(result.getTimestamp("created"));
1.116 + issue.setUpdated(result.getTimestamp("updated"));
1.117 + issue.setEta(result.getDate("eta"));
1.118 return issue;
1.119 }
1.120
1.121 + private Version mapVersion(ResultSet result, Project project) throws SQLException {
1.122 + final var version = new Version(result.getInt("v.versionid"), project);
1.123 + version.setName(result.getString("v.name"));
1.124 + version.setOrdinal(result.getInt("v.ordinal"));
1.125 + version.setStatus(VersionStatus.valueOf(result.getString("v.status")));
1.126 + return version;
1.127 + }
1.128 +
1.129 @Override
1.130 public void save(Issue instance) throws SQLException {
1.131 Objects.requireNonNull(instance.getSubject());
1.132 @@ -109,36 +138,38 @@
1.133 insert.setString(3, instance.getCategory().name());
1.134 insert.setString(4, instance.getSubject());
1.135 setStringOrNull(insert, 5, instance.getDescription());
1.136 - setForeignKeyOrNull(insert, 6, instance.getScheduledVersion(), Version::getId);
1.137 - setForeignKeyOrNull(insert, 7, instance.getResolvedVersion(), Version::getId);
1.138 - setDateOrNull(insert, 8, instance.getEta());
1.139 - insert.executeUpdate();
1.140 + setForeignKeyOrNull(insert, 6, instance.getAssignee(), User::getId);
1.141 + setDateOrNull(insert, 7, instance.getEta());
1.142 + // insert and retrieve the ID
1.143 + final var rs = insert.executeQuery();
1.144 + rs.next();
1.145 + instance.setId(rs.getInt(1));
1.146 }
1.147
1.148 @Override
1.149 public boolean update(Issue instance) throws SQLException {
1.150 + if (instance.getId() < 0) return false;
1.151 Objects.requireNonNull(instance.getSubject());
1.152 update.setString(1, instance.getStatus().name());
1.153 update.setString(2, instance.getCategory().name());
1.154 update.setString(3, instance.getSubject());
1.155 setStringOrNull(update, 4, instance.getDescription());
1.156 - setForeignKeyOrNull(update, 5, instance.getScheduledVersion(), Version::getId);
1.157 - setForeignKeyOrNull(update, 6, instance.getResolvedVersion(), Version::getId);
1.158 - setDateOrNull(update, 7, instance.getEta());
1.159 - update.setInt(8, instance.getId());
1.160 + setForeignKeyOrNull(update, 5, instance.getAssignee(), User::getId);
1.161 + setDateOrNull(update, 6, instance.getEta());
1.162 + update.setInt(7, instance.getId());
1.163 return update.executeUpdate() > 0;
1.164 }
1.165
1.166 @Override
1.167 public List<Issue> list(Project project) throws SQLException {
1.168 list.setInt(1, project.getId());
1.169 - List<Issue> versions = new ArrayList<>();
1.170 + List<Issue> issues = new ArrayList<>();
1.171 try (var result = list.executeQuery()) {
1.172 while (result.next()) {
1.173 - versions.add(mapColumns(result));
1.174 + issues.add(mapColumns(result));
1.175 }
1.176 }
1.177 - return versions;
1.178 + return issues;
1.179 }
1.180
1.181 @Override
1.182 @@ -152,4 +183,23 @@
1.183 }
1.184 }
1.185 }
1.186 +
1.187 + private List<Version> listVersions(PreparedStatement stmt, Issue issue) throws SQLException {
1.188 + stmt.setInt(1, issue.getId());
1.189 + List<Version> versions = new ArrayList<>();
1.190 + try (var result = stmt.executeQuery()) {
1.191 + while (result.next()) {
1.192 + versions.add(mapVersion(result, issue.getProject()));
1.193 + }
1.194 + }
1.195 + return versions;
1.196 + }
1.197 +
1.198 + @Override
1.199 + public void joinVersionInformation(Issue issue) throws SQLException {
1.200 + Objects.requireNonNull(issue.getProject());
1.201 + issue.setAffectedVersions(listVersions(affectedVersions, issue));
1.202 + issue.setScheduledVersions(listVersions(scheduledVersions, issue));
1.203 + issue.setResolvedVersions(listVersions(resolvedVersions, issue));
1.204 + }
1.205 }
