comparison: src/main/java/de/uapcore/lightpit/dao/postgres/PGIssueDao.java
src/main/java/de/uapcore/lightpit/dao/postgres/PGIssueDao.java
- changeset 75
- 33b6843fdf8a
- parent 72
- 0646c14e36fb
- child 83
- 24a3596b8f98
equal
deleted
inserted
replaced
| 74:91d1fc2a3a14 | 75:33b6843fdf8a |
|---|---|
| 41 | 41 |
| 42 import static de.uapcore.lightpit.dao.Functions.*; | 42 import static de.uapcore.lightpit.dao.Functions.*; |
| 43 | 43 |
| 44 public final class PGIssueDao implements IssueDao { | 44 public final class PGIssueDao implements IssueDao { |
| 45 | 45 |
| 46 private final PreparedStatement insert, update, list, find; | 46 private final PreparedStatement insert, update, list, find, affectedVersions, scheduledVersions, resolvedVersions; |
| 47 | 47 |
| 48 public PGIssueDao(Connection connection) throws SQLException { | 48 public PGIssueDao(Connection connection) throws SQLException { |
| 49 list = connection.prepareStatement( | 49 list = connection.prepareStatement( |
| 50 "select issue.id, issue.project, issue.status, issue.category, issue.subject, issue.description, " + | 50 "select issueid, project, status, category, subject, description, " + |
| 51 "vplan.id, vplan.name, vdone.id, vdone.name, " + | 51 "userid, username, givenname, lastname, mail, " + |
| 52 "issue.created, issue.updated, issue.eta " + | 52 "created, updated, eta " + |
| 53 "from lpit_issue issue " + | 53 "from lpit_issue " + |
| 54 "left join lpit_version vplan on vplan.id = version_plan " + | 54 "left join lpit_user on userid = assignee " + |
| 55 "left join lpit_version vdone on vdone.id = version_done " + | 55 "where project = ? "); |
| 56 "where issue.project = ? "); | |
| 57 | 56 |
| 58 find = connection.prepareStatement( | 57 find = connection.prepareStatement( |
| 59 "select issue.id, issue.project, issue.status, issue.category, issue.subject, issue.description, " + | 58 "select issueid, project, status, category, subject, description, " + |
| 60 "vplan.id, vplan.name, vdone.id, vdone.name, " + | 59 "userid, username, givenname, lastname, mail, " + |
| 61 "issue.created, issue.updated, issue.eta " + | 60 "created, updated, eta " + |
| 62 "from lpit_issue issue " + | 61 "from lpit_issue " + |
| 63 "left join lpit_version vplan on vplan.id = version_plan " + | 62 "left join lpit_user on userid = assignee " + |
| 64 "left join lpit_version vdone on vdone.id = version_done " + | 63 "where issueid = ? "); |
| 65 "where issue.id = ? "); | |
| 66 | 64 |
| 67 insert = connection.prepareStatement( | 65 insert = connection.prepareStatement( |
| 68 "insert into lpit_issue (project, status, category, subject, description, version_plan, version_done, eta) " + | 66 "insert into lpit_issue (project, status, category, subject, description, assignee, eta) " + |
| 69 "values (?, ?::issue_status, ?::issue_category, ?, ?, ?, ?, ?)" | 67 "values (?, ?::issue_status, ?::issue_category, ?, ?, ?, ?) returning issueid" |
| 70 ); | 68 ); |
| 71 update = connection.prepareStatement( | 69 update = connection.prepareStatement( |
| 72 "update lpit_issue set updated = now(), status = ?::issue_status, category = ?::issue_category, " + | 70 "update lpit_issue set updated = now(), status = ?::issue_status, category = ?::issue_category, " + |
| 73 "subject = ?, description = ?, version_plan = ?, version_done = ?, eta = ? where id = ?" | 71 "subject = ?, description = ?, assignee = ?, eta = ? where issueid = ?" |
| 74 ); | 72 ); |
| 75 } | 73 |
| 76 | 74 affectedVersions = connection.prepareStatement( |
| 77 private Version obtainVersion(ResultSet result, Project project, String prefix) throws SQLException { | 75 "select v.versionid, v.name, v.status, v.ordinal " + |
| 78 final int vplan = result.getInt(prefix + "id"); | 76 "from lpit_version v join lpit_issue_affected_version using (versionid) " + |
| 79 if (vplan > 0) { | 77 "where issueid = ? " + |
| 80 final var ver = new Version(vplan, project); | 78 "order by v.ordinal, v.name" |
| 81 ver.setName(result.getString(prefix + "name")); | 79 ); |
| 82 return ver; | 80 |
| 81 scheduledVersions = connection.prepareStatement( | |
| 82 "select v.versionid, v.name, v.status, v.ordinal " + | |
| 83 "from lpit_version v join lpit_issue_scheduled_version using (versionid) " + | |
| 84 "where issueid = ? " + | |
| 85 "order by v.ordinal, v.name" | |
| 86 ); | |
| 87 | |
| 88 resolvedVersions = connection.prepareStatement( | |
| 89 "select v.versionid, v.name, v.status, v.ordinal " + | |
| 90 "from lpit_version v join lpit_issue_resolved_version using (versionid) " + | |
| 91 "where issueid = ? " + | |
| 92 "order by v.ordinal, v.name" | |
| 93 ); | |
| 94 } | |
| 95 | |
| 96 private User obtainAssignee(ResultSet result) throws SQLException { | |
| 97 final int id = result.getInt("userid"); | |
| 98 if (id != 0) { | |
| 99 final var user = new User(id); | |
| 100 user.setUsername(result.getString("username")); | |
| 101 user.setGivenname(result.getString("givenname")); | |
| 102 user.setLastname(result.getString("lastname")); | |
| 103 user.setMail(result.getString("mail")); | |
| 104 return user; | |
| 83 } else { | 105 } else { |
| 84 return null; | 106 return null; |
| 85 } | 107 } |
| 86 } | 108 } |
| 87 | 109 |
| 88 public Issue mapColumns(ResultSet result) throws SQLException { | 110 private Issue mapColumns(ResultSet result) throws SQLException { |
| 89 final var project = new Project(result.getInt("issue.project")); | 111 final var project = new Project(result.getInt("project")); |
| 90 final var issue = new Issue(result.getInt("issue.id"), project); | 112 final var issue = new Issue(result.getInt("issueid"), project); |
| 91 issue.setStatus(IssueStatus.valueOf(result.getString("issue.status"))); | 113 issue.setStatus(IssueStatus.valueOf(result.getString("status"))); |
| 92 issue.setCategory(IssueCategory.valueOf(result.getString("issue.category"))); | 114 issue.setCategory(IssueCategory.valueOf(result.getString("category"))); |
| 93 issue.setSubject(result.getString("issue.subject")); | 115 issue.setSubject(result.getString("subject")); |
| 94 issue.setDescription(result.getString("issue.description")); | 116 issue.setDescription(result.getString("description")); |
| 95 issue.setScheduledVersion(obtainVersion(result, project, "vplan.")); | 117 issue.setAssignee(obtainAssignee(result)); |
| 96 issue.setResolvedVersion(obtainVersion(result, project, "vdone.")); | 118 issue.setCreated(result.getTimestamp("created")); |
| 97 issue.setCreated(result.getTimestamp("issue.created")); | 119 issue.setUpdated(result.getTimestamp("updated")); |
| 98 issue.setUpdated(result.getTimestamp("issue.updated")); | 120 issue.setEta(result.getDate("eta")); |
| 99 issue.setEta(result.getDate("issue.eta")); | |
| 100 return issue; | 121 return issue; |
| 122 } | |
| 123 | |
| 124 private Version mapVersion(ResultSet result, Project project) throws SQLException { | |
| 125 final var version = new Version(result.getInt("v.versionid"), project); | |
| 126 version.setName(result.getString("v.name")); | |
| 127 version.setOrdinal(result.getInt("v.ordinal")); | |
| 128 version.setStatus(VersionStatus.valueOf(result.getString("v.status"))); | |
| 129 return version; | |
| 101 } | 130 } |
| 102 | 131 |
| 103 @Override | 132 @Override |
| 104 public void save(Issue instance) throws SQLException { | 133 public void save(Issue instance) throws SQLException { |
| 105 Objects.requireNonNull(instance.getSubject()); | 134 Objects.requireNonNull(instance.getSubject()); |
| 107 insert.setInt(1, instance.getProject().getId()); | 136 insert.setInt(1, instance.getProject().getId()); |
| 108 insert.setString(2, instance.getStatus().name()); | 137 insert.setString(2, instance.getStatus().name()); |
| 109 insert.setString(3, instance.getCategory().name()); | 138 insert.setString(3, instance.getCategory().name()); |
| 110 insert.setString(4, instance.getSubject()); | 139 insert.setString(4, instance.getSubject()); |
| 111 setStringOrNull(insert, 5, instance.getDescription()); | 140 setStringOrNull(insert, 5, instance.getDescription()); |
| 112 setForeignKeyOrNull(insert, 6, instance.getScheduledVersion(), Version::getId); | 141 setForeignKeyOrNull(insert, 6, instance.getAssignee(), User::getId); |
| 113 setForeignKeyOrNull(insert, 7, instance.getResolvedVersion(), Version::getId); | 142 setDateOrNull(insert, 7, instance.getEta()); |
| 114 setDateOrNull(insert, 8, instance.getEta()); | 143 // insert and retrieve the ID |
| 115 insert.executeUpdate(); | 144 final var rs = insert.executeQuery(); |
| 145 rs.next(); | |
| 146 instance.setId(rs.getInt(1)); | |
| 116 } | 147 } |
| 117 | 148 |
| 118 @Override | 149 @Override |
| 119 public boolean update(Issue instance) throws SQLException { | 150 public boolean update(Issue instance) throws SQLException { |
| 151 if (instance.getId() < 0) return false; | |
| 120 Objects.requireNonNull(instance.getSubject()); | 152 Objects.requireNonNull(instance.getSubject()); |
| 121 update.setString(1, instance.getStatus().name()); | 153 update.setString(1, instance.getStatus().name()); |
| 122 update.setString(2, instance.getCategory().name()); | 154 update.setString(2, instance.getCategory().name()); |
| 123 update.setString(3, instance.getSubject()); | 155 update.setString(3, instance.getSubject()); |
| 124 setStringOrNull(update, 4, instance.getDescription()); | 156 setStringOrNull(update, 4, instance.getDescription()); |
| 125 setForeignKeyOrNull(update, 5, instance.getScheduledVersion(), Version::getId); | 157 setForeignKeyOrNull(update, 5, instance.getAssignee(), User::getId); |
| 126 setForeignKeyOrNull(update, 6, instance.getResolvedVersion(), Version::getId); | 158 setDateOrNull(update, 6, instance.getEta()); |
| 127 setDateOrNull(update, 7, instance.getEta()); | 159 update.setInt(7, instance.getId()); |
| 128 update.setInt(8, instance.getId()); | |
| 129 return update.executeUpdate() > 0; | 160 return update.executeUpdate() > 0; |
| 130 } | 161 } |
| 131 | 162 |
| 132 @Override | 163 @Override |
| 133 public List<Issue> list(Project project) throws SQLException { | 164 public List<Issue> list(Project project) throws SQLException { |
| 134 list.setInt(1, project.getId()); | 165 list.setInt(1, project.getId()); |
| 135 List<Issue> versions = new ArrayList<>(); | 166 List<Issue> issues = new ArrayList<>(); |
| 136 try (var result = list.executeQuery()) { | 167 try (var result = list.executeQuery()) { |
| 137 while (result.next()) { | 168 while (result.next()) { |
| 138 versions.add(mapColumns(result)); | 169 issues.add(mapColumns(result)); |
| 139 } | 170 } |
| 140 } | 171 } |
| 141 return versions; | 172 return issues; |
| 142 } | 173 } |
| 143 | 174 |
| 144 @Override | 175 @Override |
| 145 public Issue find(int id) throws SQLException { | 176 public Issue find(int id) throws SQLException { |
| 146 find.setInt(1, id); | 177 find.setInt(1, id); |
| 150 } else { | 181 } else { |
| 151 return null; | 182 return null; |
| 152 } | 183 } |
| 153 } | 184 } |
| 154 } | 185 } |
| 186 | |
| 187 private List<Version> listVersions(PreparedStatement stmt, Issue issue) throws SQLException { | |
| 188 stmt.setInt(1, issue.getId()); | |
| 189 List<Version> versions = new ArrayList<>(); | |
| 190 try (var result = stmt.executeQuery()) { | |
| 191 while (result.next()) { | |
| 192 versions.add(mapVersion(result, issue.getProject())); | |
| 193 } | |
| 194 } | |
| 195 return versions; | |
| 196 } | |
| 197 | |
| 198 @Override | |
| 199 public void joinVersionInformation(Issue issue) throws SQLException { | |
| 200 Objects.requireNonNull(issue.getProject()); | |
| 201 issue.setAffectedVersions(listVersions(affectedVersions, issue)); | |
| 202 issue.setScheduledVersions(listVersions(scheduledVersions, issue)); | |
| 203 issue.setResolvedVersions(listVersions(resolvedVersions, issue)); | |
| 204 } | |
| 155 } | 205 } |
