diff -r 91d1fc2a3a14 -r 33b6843fdf8a src/main/java/de/uapcore/lightpit/dao/postgres/PGIssueDao.java --- a/src/main/java/de/uapcore/lightpit/dao/postgres/PGIssueDao.java Fri May 22 17:26:27 2020 +0200 +++ b/src/main/java/de/uapcore/lightpit/dao/postgres/PGIssueDao.java Fri May 22 21:23:57 2020 +0200 @@ -43,63 +43,92 @@ public final class PGIssueDao implements IssueDao { - private final PreparedStatement insert, update, list, find; + private final PreparedStatement insert, update, list, find, affectedVersions, scheduledVersions, resolvedVersions; public PGIssueDao(Connection connection) throws SQLException { list = connection.prepareStatement( - "select issue.id, issue.project, issue.status, issue.category, issue.subject, issue.description, " + - "vplan.id, vplan.name, vdone.id, vdone.name, " + - "issue.created, issue.updated, issue.eta " + - "from lpit_issue issue " + - "left join lpit_version vplan on vplan.id = version_plan " + - "left join lpit_version vdone on vdone.id = version_done " + - "where issue.project = ? "); + "select issueid, project, status, category, subject, description, " + + "userid, username, givenname, lastname, mail, " + + "created, updated, eta " + + "from lpit_issue " + + "left join lpit_user on userid = assignee " + + "where project = ? "); find = connection.prepareStatement( - "select issue.id, issue.project, issue.status, issue.category, issue.subject, issue.description, " + - "vplan.id, vplan.name, vdone.id, vdone.name, " + - "issue.created, issue.updated, issue.eta " + - "from lpit_issue issue " + - "left join lpit_version vplan on vplan.id = version_plan " + - "left join lpit_version vdone on vdone.id = version_done " + - "where issue.id = ? "); + "select issueid, project, status, category, subject, description, " + + "userid, username, givenname, lastname, mail, " + + "created, updated, eta " + + "from lpit_issue " + + "left join lpit_user on userid = assignee " + + "where issueid = ? "); insert = connection.prepareStatement( - "insert into lpit_issue (project, status, category, subject, description, version_plan, version_done, eta) " + - "values (?, ?::issue_status, ?::issue_category, ?, ?, ?, ?, ?)" + "insert into lpit_issue (project, status, category, subject, description, assignee, eta) " + + "values (?, ?::issue_status, ?::issue_category, ?, ?, ?, ?) returning issueid" ); update = connection.prepareStatement( "update lpit_issue set updated = now(), status = ?::issue_status, category = ?::issue_category, " + - "subject = ?, description = ?, version_plan = ?, version_done = ?, eta = ? where id = ?" + "subject = ?, description = ?, assignee = ?, eta = ? where issueid = ?" + ); + + affectedVersions = connection.prepareStatement( + "select v.versionid, v.name, v.status, v.ordinal " + + "from lpit_version v join lpit_issue_affected_version using (versionid) " + + "where issueid = ? " + + "order by v.ordinal, v.name" + ); + + scheduledVersions = connection.prepareStatement( + "select v.versionid, v.name, v.status, v.ordinal " + + "from lpit_version v join lpit_issue_scheduled_version using (versionid) " + + "where issueid = ? " + + "order by v.ordinal, v.name" + ); + + resolvedVersions = connection.prepareStatement( + "select v.versionid, v.name, v.status, v.ordinal " + + "from lpit_version v join lpit_issue_resolved_version using (versionid) " + + "where issueid = ? " + + "order by v.ordinal, v.name" ); } - private Version obtainVersion(ResultSet result, Project project, String prefix) throws SQLException { - final int vplan = result.getInt(prefix + "id"); - if (vplan > 0) { - final var ver = new Version(vplan, project); - ver.setName(result.getString(prefix + "name")); - return ver; + private User obtainAssignee(ResultSet result) throws SQLException { + final int id = result.getInt("userid"); + if (id != 0) { + final var user = new User(id); + user.setUsername(result.getString("username")); + user.setGivenname(result.getString("givenname")); + user.setLastname(result.getString("lastname")); + user.setMail(result.getString("mail")); + return user; } else { return null; } } - public Issue mapColumns(ResultSet result) throws SQLException { - final var project = new Project(result.getInt("issue.project")); - final var issue = new Issue(result.getInt("issue.id"), project); - issue.setStatus(IssueStatus.valueOf(result.getString("issue.status"))); - issue.setCategory(IssueCategory.valueOf(result.getString("issue.category"))); - issue.setSubject(result.getString("issue.subject")); - issue.setDescription(result.getString("issue.description")); - issue.setScheduledVersion(obtainVersion(result, project, "vplan.")); - issue.setResolvedVersion(obtainVersion(result, project, "vdone.")); - issue.setCreated(result.getTimestamp("issue.created")); - issue.setUpdated(result.getTimestamp("issue.updated")); - issue.setEta(result.getDate("issue.eta")); + private Issue mapColumns(ResultSet result) throws SQLException { + final var project = new Project(result.getInt("project")); + final var issue = new Issue(result.getInt("issueid"), project); + issue.setStatus(IssueStatus.valueOf(result.getString("status"))); + issue.setCategory(IssueCategory.valueOf(result.getString("category"))); + issue.setSubject(result.getString("subject")); + issue.setDescription(result.getString("description")); + issue.setAssignee(obtainAssignee(result)); + issue.setCreated(result.getTimestamp("created")); + issue.setUpdated(result.getTimestamp("updated")); + issue.setEta(result.getDate("eta")); return issue; } + 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"))); + return version; + } + @Override public void save(Issue instance) throws SQLException { Objects.requireNonNull(instance.getSubject()); @@ -109,36 +138,38 @@ insert.setString(3, instance.getCategory().name()); insert.setString(4, instance.getSubject()); setStringOrNull(insert, 5, instance.getDescription()); - setForeignKeyOrNull(insert, 6, instance.getScheduledVersion(), Version::getId); - setForeignKeyOrNull(insert, 7, instance.getResolvedVersion(), Version::getId); - setDateOrNull(insert, 8, instance.getEta()); - insert.executeUpdate(); + setForeignKeyOrNull(insert, 6, instance.getAssignee(), User::getId); + setDateOrNull(insert, 7, instance.getEta()); + // insert and retrieve the ID + final var rs = insert.executeQuery(); + rs.next(); + instance.setId(rs.getInt(1)); } @Override public boolean update(Issue instance) throws SQLException { + if (instance.getId() < 0) return false; Objects.requireNonNull(instance.getSubject()); update.setString(1, instance.getStatus().name()); update.setString(2, instance.getCategory().name()); update.setString(3, instance.getSubject()); setStringOrNull(update, 4, instance.getDescription()); - setForeignKeyOrNull(update, 5, instance.getScheduledVersion(), Version::getId); - setForeignKeyOrNull(update, 6, instance.getResolvedVersion(), Version::getId); - setDateOrNull(update, 7, instance.getEta()); - update.setInt(8, instance.getId()); + setForeignKeyOrNull(update, 5, instance.getAssignee(), User::getId); + setDateOrNull(update, 6, instance.getEta()); + update.setInt(7, instance.getId()); return update.executeUpdate() > 0; } @Override public List list(Project project) throws SQLException { list.setInt(1, project.getId()); - List versions = new ArrayList<>(); + List issues = new ArrayList<>(); try (var result = list.executeQuery()) { while (result.next()) { - versions.add(mapColumns(result)); + issues.add(mapColumns(result)); } } - return versions; + return issues; } @Override @@ -152,4 +183,23 @@ } } } + + private List listVersions(PreparedStatement stmt, Issue issue) throws SQLException { + stmt.setInt(1, issue.getId()); + List versions = new ArrayList<>(); + try (var result = stmt.executeQuery()) { + while (result.next()) { + versions.add(mapVersion(result, issue.getProject())); + } + } + return versions; + } + + @Override + public void joinVersionInformation(Issue issue) throws SQLException { + Objects.requireNonNull(issue.getProject()); + issue.setAffectedVersions(listVersions(affectedVersions, issue)); + issue.setScheduledVersions(listVersions(scheduledVersions, issue)); + issue.setResolvedVersions(listVersions(resolvedVersions, issue)); + } }