1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000
1.2 +++ b/src/main/java/de/uapcore/lightpit/dao/postgres/PGIssueDao.java Mon May 18 21:05:57 2020 +0200
1.3 @@ -0,0 +1,155 @@
1.4 +/*
1.5 + * DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS HEADER.
1.6 + *
1.7 + * Copyright 2018 Mike Becker. All rights reserved.
1.8 + *
1.9 + * Redistribution and use in source and binary forms, with or without
1.10 + * modification, are permitted provided that the following conditions are met:
1.11 + *
1.12 + * 1. Redistributions of source code must retain the above copyright
1.13 + * notice, this list of conditions and the following disclaimer.
1.14 + *
1.15 + * 2. Redistributions in binary form must reproduce the above copyright
1.16 + * notice, this list of conditions and the following disclaimer in the
1.17 + * documentation and/or other materials provided with the distribution.
1.18 + *
1.19 + * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
1.20 + * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
1.21 + * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
1.22 + * ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT HOLDER OR CONTRIBUTORS BE
1.23 + * LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR
1.24 + * CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF
1.25 + * SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS
1.26 + * INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN
1.27 + * CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE)
1.28 + * ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE
1.29 + * POSSIBILITY OF SUCH DAMAGE.
1.30 + *
1.31 + */
1.32 +package de.uapcore.lightpit.dao.postgres;
1.33 +
1.34 +import de.uapcore.lightpit.dao.IssueDao;
1.35 +import de.uapcore.lightpit.entities.*;
1.36 +
1.37 +import java.sql.Connection;
1.38 +import java.sql.PreparedStatement;
1.39 +import java.sql.ResultSet;
1.40 +import java.sql.SQLException;
1.41 +import java.util.ArrayList;
1.42 +import java.util.List;
1.43 +import java.util.Objects;
1.44 +
1.45 +import static de.uapcore.lightpit.dao.Functions.*;
1.46 +
1.47 +public final class PGIssueDao implements IssueDao {
1.48 +
1.49 + private final PreparedStatement insert, update, list, find;
1.50 +
1.51 + public PGIssueDao(Connection connection) throws SQLException {
1.52 + list = connection.prepareStatement(
1.53 + "select id, project, status, category, subject, description, " +
1.54 + "vplan.id, vplan.name, vdone.id, vdone.name, " +
1.55 + "created, updated, eta " +
1.56 + "from lpit_issue " +
1.57 + "left join lpit_version vplan on vplan.id = version_plan " +
1.58 + "left join lpit_version vdone on vdone.id = version_done " +
1.59 + "where project = ? ");
1.60 +
1.61 + find = connection.prepareStatement(
1.62 + "select id, project, status, category, subject, description, " +
1.63 + "vplan.id, vplan.name, vdone.id, vdone.name, " +
1.64 + "created, updated, eta " +
1.65 + "from lpit_issue " +
1.66 + "left join lpit_version vplan on vplan.id = version_plan " +
1.67 + "left join lpit_version vdone on vdone.id = version_done " +
1.68 + "where id = ? ");
1.69 +
1.70 + insert = connection.prepareStatement(
1.71 + "insert into lpit_issue (project, status, category, subject, description, version_plan, version_done, eta) " +
1.72 + "values (?, ?::issue_status, ?::issue_category, ?, ?, ?, ?, ?)"
1.73 + );
1.74 + update = connection.prepareStatement(
1.75 + "update lpit_issue set updated = now(), status = ?::issue_status, category = ?::issue_category, " +
1.76 + "subject = ?, description = ?, version_plan = ?, version_done = ?, eta = ? where id = ?"
1.77 + );
1.78 + }
1.79 +
1.80 + private Version obtainVersion(ResultSet result, Project project, String prefix) throws SQLException {
1.81 + final int vplan = result.getInt(prefix+"id");
1.82 + if (vplan > 0) {
1.83 + final var ver = new Version(vplan, project);
1.84 + ver.setName(result.getString(prefix+"name"));
1.85 + return ver;
1.86 + } else {
1.87 + return null;
1.88 + }
1.89 + }
1.90 +
1.91 + public Issue mapColumns(ResultSet result) throws SQLException {
1.92 + final var project = new Project(result.getInt("project"));
1.93 + final var issue = new Issue(result.getInt("id"), project);
1.94 + issue.setStatus(IssueStatus.valueOf(result.getString("status")));
1.95 + issue.setCategory(IssueCategory.valueOf(result.getString("category")));
1.96 + issue.setSubject(result.getString("subject"));
1.97 + issue.setDescription(result.getString("description"));
1.98 + issue.setScheduledVersion(obtainVersion(result, project, "vplan."));
1.99 + issue.setResolvedVersion(obtainVersion(result, project, "vdone."));
1.100 + issue.setCreated(result.getTimestamp("created"));
1.101 + issue.setUpdated(result.getTimestamp("updated"));
1.102 + issue.setEta(result.getDate("eta"));
1.103 + return issue;
1.104 + }
1.105 +
1.106 + @Override
1.107 + public void save(Issue instance) throws SQLException {
1.108 + Objects.requireNonNull(instance.getSubject());
1.109 + Objects.requireNonNull(instance.getProject());
1.110 + insert.setInt(1, instance.getProject().getId());
1.111 + insert.setString(2, instance.getStatus().name());
1.112 + insert.setString(3, instance.getCategory().name());
1.113 + insert.setString(4, instance.getSubject());
1.114 + setStringOrNull(insert, 5, instance.getDescription());
1.115 + setForeignKeyOrNull(insert, 6, instance.getScheduledVersion(), Version::getId);
1.116 + setForeignKeyOrNull(insert, 7, instance.getResolvedVersion(), Version::getId);
1.117 + setDateOrNull(insert, 8, instance.getEta());
1.118 + insert.executeUpdate();
1.119 + }
1.120 +
1.121 + @Override
1.122 + public boolean update(Issue instance) throws SQLException {
1.123 + Objects.requireNonNull(instance.getSubject());
1.124 + update.setString(1, instance.getStatus().name());
1.125 + update.setString(2, instance.getCategory().name());
1.126 + update.setString(3, instance.getSubject());
1.127 + setStringOrNull(update, 4, instance.getDescription());
1.128 + setForeignKeyOrNull(update, 5, instance.getScheduledVersion(), Version::getId);
1.129 + setForeignKeyOrNull(update, 6, instance.getResolvedVersion(), Version::getId);
1.130 + setDateOrNull(update, 7, instance.getEta());
1.131 + update.setInt(8, instance.getId());
1.132 + return update.executeUpdate() > 0;
1.133 + }
1.134 +
1.135 + @Override
1.136 + public List<Issue> list(Project project) throws SQLException {
1.137 + list.setInt(1, project.getId());
1.138 + List<Issue> versions = new ArrayList<>();
1.139 + try (var result = list.executeQuery()) {
1.140 + while (result.next()) {
1.141 + versions.add(mapColumns(result));
1.142 + }
1.143 + }
1.144 + return versions;
1.145 + }
1.146 +
1.147 + @Override
1.148 + public Issue find(int id) throws SQLException {
1.149 + find.setInt(1, id);
1.150 + try (var result = find.executeQuery()) {
1.151 + if (result.next()) {
1.152 + return mapColumns(result);
1.153 + } else {
1.154 + return null;
1.155 + }
1.156 + }
1.157 + }
1.158 +}
