universe@38: /* universe@38: * DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS HEADER. universe@38: * universe@38: * Copyright 2018 Mike Becker. All rights reserved. universe@38: * universe@38: * Redistribution and use in source and binary forms, with or without universe@38: * modification, are permitted provided that the following conditions are met: universe@38: * universe@38: * 1. Redistributions of source code must retain the above copyright universe@38: * notice, this list of conditions and the following disclaimer. universe@38: * universe@38: * 2. Redistributions in binary form must reproduce the above copyright universe@38: * notice, this list of conditions and the following disclaimer in the universe@38: * documentation and/or other materials provided with the distribution. universe@38: * universe@38: * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" universe@38: * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE universe@38: * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE universe@38: * ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT HOLDER OR CONTRIBUTORS BE universe@38: * LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR universe@38: * CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF universe@38: * SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS universe@38: * INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN universe@38: * CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) universe@38: * ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE universe@38: * POSSIBILITY OF SUCH DAMAGE. universe@38: * universe@38: */ universe@38: package de.uapcore.lightpit.dao.postgres; universe@38: universe@38: import de.uapcore.lightpit.dao.ProjectDao; universe@86: import de.uapcore.lightpit.entities.IssueSummary; universe@38: import de.uapcore.lightpit.entities.Project; universe@38: import de.uapcore.lightpit.entities.User; universe@38: universe@38: import java.sql.Connection; universe@38: import java.sql.PreparedStatement; universe@38: import java.sql.ResultSet; universe@38: import java.sql.SQLException; universe@47: import java.util.ArrayList; universe@47: import java.util.List; universe@38: import java.util.Objects; universe@38: universe@47: import static de.uapcore.lightpit.dao.Functions.setForeignKeyOrNull; universe@47: import static de.uapcore.lightpit.dao.Functions.setStringOrNull; universe@38: universe@59: public final class PGProjectDao implements ProjectDao { universe@38: universe@47: private final PreparedStatement insert, update, list, find; universe@81: private final PreparedStatement issue_summary; universe@38: universe@47: public PGProjectDao(Connection connection) throws SQLException { universe@47: list = connection.prepareStatement( universe@75: "select projectid, name, description, repourl, " + universe@47: "userid, username, lastname, givenname, mail " + universe@47: "from lpit_project " + universe@47: "left join lpit_user owner on lpit_project.owner = owner.userid " + universe@47: "order by name"); universe@47: universe@47: find = connection.prepareStatement( universe@75: "select projectid, name, description, repourl, " + universe@47: "userid, username, lastname, givenname, mail " + universe@47: "from lpit_project " + universe@47: "left join lpit_user owner on lpit_project.owner = owner.userid " + universe@75: "where projectid = ?"); universe@38: universe@81: issue_summary = connection.prepareStatement( universe@81: "select phase, count(*) as total "+ universe@81: "from lpit_issue " + universe@81: "join lpit_issue_phases using(status) " + universe@81: "where project = ? "+ universe@81: "group by phase " universe@81: ); universe@81: universe@38: insert = connection.prepareStatement( universe@38: "insert into lpit_project (name, description, repourl, owner) values (?, ?, ?, ?)" universe@38: ); universe@38: update = connection.prepareStatement( universe@75: "update lpit_project set name = ?, description = ?, repourl = ?, owner = ? where projectid = ?" universe@38: ); universe@38: } universe@38: universe@47: public Project mapColumns(ResultSet result) throws SQLException { universe@75: final var proj = new Project(result.getInt("projectid")); universe@47: proj.setName(result.getString("name")); universe@47: proj.setDescription(result.getString("description")); universe@47: proj.setRepoUrl(result.getString("repourl")); universe@47: universe@47: final int id = result.getInt("userid"); universe@47: if (id != 0) { universe@47: final var user = new User(id); universe@47: user.setUsername(result.getString("username")); universe@47: user.setGivenname(result.getString("givenname")); universe@47: user.setLastname(result.getString("lastname")); universe@47: user.setMail(result.getString("mail")); universe@47: proj.setOwner(user); universe@47: } universe@47: universe@38: return proj; universe@38: } universe@38: universe@86: public IssueSummary getIssueSummary(Project project) throws SQLException { universe@86: issue_summary.setInt(1, project.getId()); universe@81: final var result = issue_summary.executeQuery(); universe@86: final var summary = new IssueSummary(); universe@81: while (result.next()) { universe@81: final var phase = result.getInt("phase"); universe@81: final var total = result.getInt("total"); universe@81: switch(phase) { universe@81: case 0: universe@86: summary.setOpen(total); universe@81: break; universe@81: case 1: universe@86: summary.setActive(total); universe@81: break; universe@81: case 2: universe@86: summary.setDone(total); universe@81: break; universe@81: } universe@81: } universe@86: return summary; universe@81: } universe@81: universe@38: @Override universe@38: public void save(Project instance) throws SQLException { universe@38: Objects.requireNonNull(instance.getName()); universe@38: insert.setString(1, instance.getName()); universe@38: setStringOrNull(insert, 2, instance.getDescription()); universe@38: setStringOrNull(insert, 3, instance.getRepoUrl()); universe@51: setForeignKeyOrNull(insert, 4, instance.getOwner(), User::getId); universe@38: insert.executeUpdate(); universe@38: } universe@38: universe@38: @Override universe@38: public boolean update(Project instance) throws SQLException { universe@75: if (instance.getId() < 0) return false; universe@38: Objects.requireNonNull(instance.getName()); universe@38: update.setString(1, instance.getName()); universe@38: setStringOrNull(update, 2, instance.getDescription()); universe@38: setStringOrNull(update, 3, instance.getRepoUrl()); universe@51: setForeignKeyOrNull(update, 4, instance.getOwner(), User::getId); universe@47: update.setInt(5, instance.getId()); universe@38: return update.executeUpdate() > 0; universe@38: } universe@47: universe@47: @Override universe@47: public List list() throws SQLException { universe@47: List projects = new ArrayList<>(); universe@47: try (var result = list.executeQuery()) { universe@47: while (result.next()) { universe@81: final var project = mapColumns(result); universe@81: projects.add(project); universe@47: } universe@47: } universe@47: return projects; universe@47: } universe@47: universe@47: @Override universe@47: public Project find(int id) throws SQLException { universe@47: find.setInt(1, id); universe@47: try (var result = find.executeQuery()) { universe@47: if (result.next()) { universe@81: final var project = mapColumns(result); universe@81: return project; universe@47: } else { universe@47: return null; universe@47: } universe@47: } universe@47: } universe@38: }