1.1 --- a/src/main/java/de/uapcore/lightpit/dao/postgres/PGIssueDao.java Sat May 30 18:12:38 2020 +0200
1.2 +++ b/src/main/java/de/uapcore/lightpit/dao/postgres/PGIssueDao.java Mon Jun 01 14:46:58 2020 +0200
1.3 @@ -43,7 +43,7 @@
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, listForVersion, find;
1.9 private final PreparedStatement affectedVersions, scheduledVersions, resolvedVersions;
1.10 private final PreparedStatement clearAffected, clearScheduled, clearResolved;
1.11 private final PreparedStatement insertAffected, insertScheduled, insertResolved;
1.12 @@ -56,7 +56,24 @@
1.13 "from lpit_issue i " +
1.14 "left join lpit_project p on project = projectid " +
1.15 "left join lpit_user on userid = assignee " +
1.16 - "where project = ? ");
1.17 + "where project = ? "+
1.18 + "order by eta asc, updated desc");
1.19 +
1.20 + listForVersion = connection.prepareStatement(
1.21 + "with issue_version as ( "+
1.22 + "select issueid, versionid from lpit_issue_affected_version union "+
1.23 + "select issueid, versionid from lpit_issue_scheduled_version union "+
1.24 + "select issueid, versionid from lpit_issue_resolved_version) "+
1.25 + "select issueid, project, p.name as projectname, 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 issue_version using (issueid) "+
1.30 + "left join lpit_project p on project = projectid " +
1.31 + "left join lpit_user on userid = assignee " +
1.32 + "where versionid = ? "+
1.33 + "order by eta asc, updated desc"
1.34 + );
1.35
1.36 find = connection.prepareStatement(
1.37 "select issueid, project, p.name as projectname, status, category, subject, i.description, " +
1.38 @@ -121,7 +138,8 @@
1.39 private Issue mapColumns(ResultSet result) throws SQLException {
1.40 final var project = new Project(result.getInt("project"));
1.41 project.setName(result.getString("projectname"));
1.42 - final var issue = new Issue(result.getInt("issueid"), project);
1.43 + final var issue = new Issue(result.getInt("issueid"));
1.44 + issue.setProject(project);
1.45 issue.setStatus(IssueStatus.valueOf(result.getString("status")));
1.46 issue.setCategory(IssueCategory.valueOf(result.getString("category")));
1.47 issue.setSubject(result.getString("subject"));
1.48 @@ -133,8 +151,8 @@
1.49 return issue;
1.50 }
1.51
1.52 - private Version mapVersion(ResultSet result, Project project) throws SQLException {
1.53 - final var version = new Version(result.getInt("versionid"), project);
1.54 + private Version mapVersion(ResultSet result) throws SQLException {
1.55 + final var version = new Version(result.getInt("versionid"));
1.56 version.setName(result.getString("name"));
1.57 version.setOrdinal(result.getInt("ordinal"));
1.58 version.setStatus(VersionStatus.valueOf(result.getString("status")));
1.59 @@ -203,11 +221,10 @@
1.60 }
1.61 }
1.62
1.63 - @Override
1.64 - public List<Issue> list(Project project) throws SQLException {
1.65 - list.setInt(1, project.getId());
1.66 + private List<Issue> list(PreparedStatement query, int arg) throws SQLException {
1.67 + query.setInt(1, arg);
1.68 List<Issue> issues = new ArrayList<>();
1.69 - try (var result = list.executeQuery()) {
1.70 + try (var result = query.executeQuery()) {
1.71 while (result.next()) {
1.72 issues.add(mapColumns(result));
1.73 }
1.74 @@ -216,6 +233,16 @@
1.75 }
1.76
1.77 @Override
1.78 + public List<Issue> list(Project project) throws SQLException {
1.79 + return list(list, project.getId());
1.80 + }
1.81 +
1.82 + @Override
1.83 + public List<Issue> list(Version version) throws SQLException {
1.84 + return list(listForVersion, version.getId());
1.85 + }
1.86 +
1.87 + @Override
1.88 public Issue find(int id) throws SQLException {
1.89 find.setInt(1, id);
1.90 try (var result = find.executeQuery()) {
1.91 @@ -232,7 +259,7 @@
1.92 List<Version> versions = new ArrayList<>();
1.93 try (var result = stmt.executeQuery()) {
1.94 while (result.next()) {
1.95 - versions.add(mapVersion(result, issue.getProject()));
1.96 + versions.add(mapVersion(result));
1.97 }
1.98 }
1.99 return versions;
