1.1 --- a/src/main/java/de/uapcore/lightpit/dao/postgres/PGIssueDao.java Sat May 30 15:28:27 2020 +0200
1.2 +++ b/src/main/java/de/uapcore/lightpit/dao/postgres/PGIssueDao.java Sat May 30 18:05:06 2020 +0200
1.3 @@ -43,22 +43,27 @@
1.4
1.5 public final class PGIssueDao implements IssueDao {
1.6
1.7 - private final PreparedStatement insert, update, list, find, affectedVersions, scheduledVersions, resolvedVersions;
1.8 + private final PreparedStatement insert, update, list, 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
1.13 public PGIssueDao(Connection connection) throws SQLException {
1.14 list = connection.prepareStatement(
1.15 - "select issueid, project, status, category, subject, description, " +
1.16 + "select issueid, project, p.name as projectname, status, category, subject, i.description, " +
1.17 "userid, username, givenname, lastname, mail, " +
1.18 "created, updated, eta " +
1.19 - "from lpit_issue " +
1.20 + "from lpit_issue i " +
1.21 + "left join lpit_project p on project = projectid " +
1.22 "left join lpit_user on userid = assignee " +
1.23 "where project = ? ");
1.24
1.25 find = connection.prepareStatement(
1.26 - "select issueid, project, status, category, subject, description, " +
1.27 + "select issueid, project, p.name as projectname, status, category, subject, i.description, " +
1.28 "userid, username, givenname, lastname, mail, " +
1.29 "created, updated, eta " +
1.30 - "from lpit_issue " +
1.31 + "from lpit_issue i " +
1.32 + "left join lpit_project p on project = projectid " +
1.33 "left join lpit_user on userid = assignee " +
1.34 "where issueid = ? ");
1.35
1.36 @@ -72,25 +77,31 @@
1.37 );
1.38
1.39 affectedVersions = connection.prepareStatement(
1.40 - "select v.versionid, v.name, v.status, v.ordinal " +
1.41 - "from lpit_version v join lpit_issue_affected_version using (versionid) " +
1.42 + "select versionid, name, status, ordinal " +
1.43 + "from lpit_version join lpit_issue_affected_version using (versionid) " +
1.44 "where issueid = ? " +
1.45 - "order by v.ordinal, v.name"
1.46 + "order by ordinal, name"
1.47 );
1.48 + clearAffected = connection.prepareStatement("delete from lpit_issue_affected_version where issueid = ?");
1.49 + insertAffected = connection.prepareStatement("insert into lpit_issue_affected_version (issueid, versionid) values (?,?)");
1.50
1.51 scheduledVersions = connection.prepareStatement(
1.52 - "select v.versionid, v.name, v.status, v.ordinal " +
1.53 - "from lpit_version v join lpit_issue_scheduled_version using (versionid) " +
1.54 + "select versionid, name, status, ordinal " +
1.55 + "from lpit_version join lpit_issue_scheduled_version using (versionid) " +
1.56 "where issueid = ? " +
1.57 - "order by v.ordinal, v.name"
1.58 + "order by ordinal, name"
1.59 );
1.60 + clearScheduled = connection.prepareStatement("delete from lpit_issue_scheduled_version where issueid = ?");
1.61 + insertScheduled = connection.prepareStatement("insert into lpit_issue_scheduled_version (issueid, versionid) values (?,?)");
1.62
1.63 resolvedVersions = connection.prepareStatement(
1.64 - "select v.versionid, v.name, v.status, v.ordinal " +
1.65 + "select versionid, name, status, ordinal " +
1.66 "from lpit_version v join lpit_issue_resolved_version using (versionid) " +
1.67 "where issueid = ? " +
1.68 - "order by v.ordinal, v.name"
1.69 + "order by ordinal, name"
1.70 );
1.71 + clearResolved = connection.prepareStatement("delete from lpit_issue_resolved_version where issueid = ?");
1.72 + insertResolved = connection.prepareStatement("insert into lpit_issue_resolved_version (issueid, versionid) values (?,?)");
1.73 }
1.74
1.75 private User obtainAssignee(ResultSet result) throws SQLException {
1.76 @@ -109,6 +120,7 @@
1.77
1.78 private Issue mapColumns(ResultSet result) throws SQLException {
1.79 final var project = new Project(result.getInt("project"));
1.80 + project.setName(result.getString("projectname"));
1.81 final var issue = new Issue(result.getInt("issueid"), project);
1.82 issue.setStatus(IssueStatus.valueOf(result.getString("status")));
1.83 issue.setCategory(IssueCategory.valueOf(result.getString("category")));
1.84 @@ -122,13 +134,37 @@
1.85 }
1.86
1.87 private Version mapVersion(ResultSet result, Project project) throws SQLException {
1.88 - final var version = new Version(result.getInt("v.versionid"), project);
1.89 - version.setName(result.getString("v.name"));
1.90 - version.setOrdinal(result.getInt("v.ordinal"));
1.91 - version.setStatus(VersionStatus.valueOf(result.getString("v.status")));
1.92 + final var version = new Version(result.getInt("versionid"), project);
1.93 + version.setName(result.getString("name"));
1.94 + version.setOrdinal(result.getInt("ordinal"));
1.95 + version.setStatus(VersionStatus.valueOf(result.getString("status")));
1.96 return version;
1.97 }
1.98
1.99 + private void updateVersionLists(Issue instance) throws SQLException {
1.100 + clearAffected.setInt(1, instance.getId());
1.101 + clearScheduled.setInt(1, instance.getId());
1.102 + clearResolved.setInt(1, instance.getId());
1.103 + insertAffected.setInt(1, instance.getId());
1.104 + insertScheduled.setInt(1, instance.getId());
1.105 + insertResolved.setInt(1, instance.getId());
1.106 + clearAffected.executeUpdate();
1.107 + clearScheduled.executeUpdate();
1.108 + clearResolved.executeUpdate();
1.109 + for (Version v : instance.getAffectedVersions()) {
1.110 + insertAffected.setInt(2, v.getId());
1.111 + insertAffected.executeUpdate();
1.112 + }
1.113 + for (Version v : instance.getScheduledVersions()) {
1.114 + insertScheduled.setInt(2, v.getId());
1.115 + insertScheduled.executeUpdate();
1.116 + }
1.117 + for (Version v : instance.getResolvedVersions()) {
1.118 + insertResolved.setInt(2, v.getId());
1.119 + insertResolved.executeUpdate();
1.120 + }
1.121 + }
1.122 +
1.123 @Override
1.124 public void save(Issue instance) throws SQLException {
1.125 Objects.requireNonNull(instance.getSubject());
1.126 @@ -144,6 +180,7 @@
1.127 final var rs = insert.executeQuery();
1.128 rs.next();
1.129 instance.setId(rs.getInt(1));
1.130 + updateVersionLists(instance);
1.131 }
1.132
1.133 @Override
1.134 @@ -157,7 +194,13 @@
1.135 setForeignKeyOrNull(update, 5, instance.getAssignee(), User::getId);
1.136 setDateOrNull(update, 6, instance.getEta());
1.137 update.setInt(7, instance.getId());
1.138 - return update.executeUpdate() > 0;
1.139 + boolean success = update.executeUpdate() > 0;
1.140 + if (success) {
1.141 + updateVersionLists(instance);
1.142 + return true;
1.143 + } else {
1.144 + return false;
1.145 + }
1.146 }
1.147
1.148 @Override
