Mercurial > hg > LightPIT / changeset

     1.1 --- a/setup/postgres/psql_create_tables.sql	Thu Oct 22 12:00:34 2020 +0200
     1.2 +++ b/setup/postgres/psql_create_tables.sql	Thu Oct 22 13:03:26 2020 +0200
     1.3 @@ -12,6 +12,7 @@
     1.4  create table lpit_project (
     1.5      projectid       serial          primary key,
     1.6      name            varchar(20)     not null unique,
     1.7 +    node            varchar(20)     not null unique,
     1.8      description     varchar(200),
     1.9      repoUrl         varchar(50),
    1.10      owner           integer         references lpit_user(userid)
    1.11 @@ -29,21 +30,26 @@
    1.12      versionid       serial          primary key,
    1.13      project         integer         not null references lpit_project(projectid),
    1.14      name            varchar(20)     not null,
    1.15 +    node            varchar(20)     not null,
    1.16      ordinal         integer         not null default 0,
    1.17      status          version_status  not null default 'Future'
    1.18  );
    1.19  
    1.20 +create unique index lpit_version_node_unique on lpit_version(project, node);
    1.21  
    1.22  create table lpit_component (
    1.23      id              serial          primary key,
    1.24      project         integer         not null references lpit_project(projectid),
    1.25      name            varchar(20)     not null,
    1.26 +    node            varchar(20)     not null,
    1.27      color           char(6)         not null default '000000',
    1.28      ordinal         integer         not null default 0,
    1.29      description     text,
    1.30      lead            integer         references lpit_user(userid)
    1.31  );
    1.32  
    1.33 +create unique index lpit_component_node_unique on lpit_component(project, node);
    1.34 +
    1.35  create type issue_status as enum (
    1.36      'InSpecification',
    1.37      'ToDo',

     2.1 --- a/src/main/java/de/uapcore/lightpit/dao/ComponentDao.java	Thu Oct 22 12:00:34 2020 +0200
     2.2 +++ b/src/main/java/de/uapcore/lightpit/dao/ComponentDao.java	Thu Oct 22 13:03:26 2020 +0200
     2.3 @@ -31,6 +31,8 @@
     2.4  import de.uapcore.lightpit.entities.Component;
     2.5  import de.uapcore.lightpit.entities.Project;
     2.6  
     2.7 +import java.sql.SQLException;
     2.8 +
     2.9  public interface ComponentDao extends ChildEntityDao<Component, Project> {
    2.10 -
    2.11 +    Component findByNode(Project parent, String node) throws SQLException;
    2.12  }

     3.1 --- a/src/main/java/de/uapcore/lightpit/dao/Functions.java	Thu Oct 22 12:00:34 2020 +0200
     3.2 +++ b/src/main/java/de/uapcore/lightpit/dao/Functions.java	Thu Oct 22 13:03:26 2020 +0200
     3.3 @@ -28,10 +28,9 @@
     3.4   */
     3.5  package de.uapcore.lightpit.dao;
     3.6  
     3.7 -import java.sql.Date;
     3.8 -import java.sql.PreparedStatement;
     3.9 -import java.sql.SQLException;
    3.10 -import java.sql.Types;
    3.11 +import java.sql.*;
    3.12 +import java.util.ArrayList;
    3.13 +import java.util.List;
    3.14  import java.util.Optional;
    3.15  import java.util.function.Function;
    3.16  
    3.17 @@ -65,6 +64,33 @@
    3.18          }
    3.19      }
    3.20  
    3.21 +    @FunctionalInterface
    3.22 +    public interface ResultSetMapper<T> {
    3.23 +        T apply(ResultSet rs) throws SQLException;
    3.24 +    }
    3.25 +
    3.26 +    public static <T> List<T> list(PreparedStatement stmt, ResultSetMapper<T> mapper) throws SQLException {
    3.27 +        List<T> results = new ArrayList<>();
    3.28 +        try (var result = stmt.executeQuery()) {
    3.29 +            while (result.next()) {
    3.30 +                final var project = mapper.apply(result);
    3.31 +                results.add(project);
    3.32 +            }
    3.33 +        }
    3.34 +        return results;
    3.35 +    }
    3.36 +
    3.37 +    public static <T> T find(PreparedStatement stmt, ResultSetMapper<T> mapper) throws SQLException {
    3.38 +        try (var result = stmt.executeQuery()) {
    3.39 +            if (result.next()) {
    3.40 +                final var ent = mapper.apply(result);
    3.41 +                return ent;
    3.42 +            } else {
    3.43 +                return null;
    3.44 +            }
    3.45 +        }
    3.46 +    }
    3.47 +
    3.48      private Functions() {
    3.49  
    3.50      }

     4.1 --- a/src/main/java/de/uapcore/lightpit/dao/ProjectDao.java	Thu Oct 22 12:00:34 2020 +0200
     4.2 +++ b/src/main/java/de/uapcore/lightpit/dao/ProjectDao.java	Thu Oct 22 13:03:26 2020 +0200
     4.3 @@ -35,4 +35,6 @@
     4.4  
     4.5  public interface ProjectDao extends RootEntityDao<Project> {
     4.6      IssueSummary getIssueSummary(Project project) throws SQLException;
     4.7 +
     4.8 +    Project findByNode(String node) throws SQLException;
     4.9  }

     5.1 --- a/src/main/java/de/uapcore/lightpit/dao/VersionDao.java	Thu Oct 22 12:00:34 2020 +0200
     5.2 +++ b/src/main/java/de/uapcore/lightpit/dao/VersionDao.java	Thu Oct 22 13:03:26 2020 +0200
     5.3 @@ -31,5 +31,8 @@
     5.4  import de.uapcore.lightpit.entities.Project;
     5.5  import de.uapcore.lightpit.entities.Version;
     5.6  
     5.7 +import java.sql.SQLException;
     5.8 +
     5.9  public interface VersionDao extends ChildEntityDao<Version, Project> {
    5.10 +    Version findByNode(Project parent, String node) throws SQLException;
    5.11  }

     6.1 --- a/src/main/java/de/uapcore/lightpit/dao/postgres/PGComponentDao.java	Thu Oct 22 12:00:34 2020 +0200
     6.2 +++ b/src/main/java/de/uapcore/lightpit/dao/postgres/PGComponentDao.java	Thu Oct 22 13:03:26 2020 +0200
     6.3 @@ -39,42 +39,38 @@
     6.4  import java.sql.PreparedStatement;
     6.5  import java.sql.ResultSet;
     6.6  import java.sql.SQLException;
     6.7 -import java.util.ArrayList;
     6.8  import java.util.List;
     6.9 -import java.util.Objects;
    6.10  
    6.11  public final class PGComponentDao implements ComponentDao {
    6.12  
    6.13 -    private final PreparedStatement insert, update, list, find;
    6.14 +    private final PreparedStatement insert, update, list, find, findByNode;
    6.15  
    6.16      public PGComponentDao(Connection connection) throws SQLException {
    6.17 -        list = connection.prepareStatement(
    6.18 -                "select id, name, color, ordinal, description, " +
    6.19 -                        "userid, username, givenname, lastname, mail " +
    6.20 -                        "from lpit_component " +
    6.21 -                        "left join lpit_user on lead = userid " +
    6.22 -                        "where project = ? " +
    6.23 +        final var query = "select id, name, node, color, ordinal, description, " +
    6.24 +                "userid, username, givenname, lastname, mail " +
    6.25 +                "from lpit_component " +
    6.26 +                "left join lpit_user on lead = userid";
    6.27 +
    6.28 +        list = connection.prepareStatement(query + " where project = ? " +
    6.29                          "order by ordinal desc, lower(name) desc");
    6.30  
    6.31 -        find = connection.prepareStatement(
    6.32 -                "select id, name, color, ordinal, description, " +
    6.33 -                        "userid, username, givenname, lastname, mail " +
    6.34 -                        "from lpit_component " +
    6.35 -                        "left join lpit_user on lead = userid " +
    6.36 -                        "where id = ? ");
    6.37 +        find = connection.prepareStatement(query + " where id = ? ");
    6.38 +
    6.39 +        findByNode = connection.prepareStatement(query + " where project = ? and node = ?");
    6.40  
    6.41          insert = connection.prepareStatement(
    6.42 -                "insert into lpit_component (project, name, color, ordinal, description, lead) values (?, ?, ?, ?, ?, ?)"
    6.43 +                "insert into lpit_component (name, node, color, ordinal, description, lead, project) values (?, ?, ?, ?, ?, ?, ?)"
    6.44          );
    6.45  
    6.46          update = connection.prepareStatement(
    6.47 -                "update lpit_component set name = ?, color = ?, ordinal = ?, description = ?, lead = ? where id = ?"
    6.48 +                "update lpit_component set name = ?, node = ?, color = ?, ordinal = ?, description = ?, lead = ? where id = ?"
    6.49          );
    6.50      }
    6.51  
    6.52      private static Component mapColumns(ResultSet result) throws SQLException {
    6.53          final var component = new Component(result.getInt("id"));
    6.54          component.setName(result.getString("name"));
    6.55 +        component.setNode(result.getString("node"));
    6.56          try {
    6.57              component.setColor(new WebColor(result.getString("color")));
    6.58          } catch (IllegalArgumentException ex) {
    6.59 @@ -87,53 +83,49 @@
    6.60          return component;
    6.61      }
    6.62  
    6.63 +    private static int setColumns(PreparedStatement stmt, Component instance) throws SQLException {
    6.64 +        int column = 0;
    6.65 +        stmt.setString(++column, instance.getName());
    6.66 +        stmt.setString(++column, instance.getNode());
    6.67 +        stmt.setString(++column, instance.getColor().getHex());
    6.68 +        stmt.setInt(++column, instance.getOrdinal());
    6.69 +        Functions.setStringOrNull(stmt, ++column, instance.getDescription());
    6.70 +        Functions.setForeignKeyOrNull(stmt, ++column, instance.getLead(), User::getId);
    6.71 +        return column;
    6.72 +    }
    6.73 +
    6.74      @Override
    6.75      public void save(Component instance, Project project) throws SQLException {
    6.76 -        Objects.requireNonNull(instance.getName());
    6.77 -        insert.setInt(1, project.getId());
    6.78 -        insert.setString(2, instance.getName());
    6.79 -        insert.setString(3, instance.getColor().getHex());
    6.80 -        insert.setInt(4, instance.getOrdinal());
    6.81 -        Functions.setStringOrNull(insert, 5, instance.getDescription());
    6.82 -        Functions.setForeignKeyOrNull(insert, 6, instance.getLead(), User::getId);
    6.83 +        int column = setColumns(insert, instance);
    6.84 +        insert.setInt(++column, project.getId());
    6.85          insert.executeUpdate();
    6.86      }
    6.87  
    6.88      @Override
    6.89      public boolean update(Component instance) throws SQLException {
    6.90          if (instance.getId() < 0) return false;
    6.91 -        Objects.requireNonNull(instance.getName());
    6.92 -        Objects.requireNonNull(instance.getColor());
    6.93 -        update.setString(1, instance.getName());
    6.94 -        update.setString(2, instance.getColor().getHex());
    6.95 -        update.setInt(3, instance.getOrdinal());
    6.96 -        Functions.setStringOrNull(update, 4, instance.getDescription());
    6.97 -        Functions.setForeignKeyOrNull(update, 5, instance.getLead(), User::getId);
    6.98 -        update.setInt(6, instance.getId());
    6.99 +        int column = setColumns(update, instance);
   6.100 +        update.setInt(++column, instance.getId());
   6.101          return update.executeUpdate() > 0;
   6.102      }
   6.103  
   6.104 +
   6.105      @Override
   6.106      public List<Component> list(Project project) throws SQLException {
   6.107          list.setInt(1, project.getId());
   6.108 -        List<Component> components = new ArrayList<>();
   6.109 -        try (var result = list.executeQuery()) {
   6.110 -            while (result.next()) {
   6.111 -                components.add(mapColumns(result));
   6.112 -            }
   6.113 -        }
   6.114 -        return components;
   6.115 +        return Functions.list(list, PGComponentDao::mapColumns);
   6.116      }
   6.117  
   6.118      @Override
   6.119      public Component find(int id) throws SQLException {
   6.120          find.setInt(1, id);
   6.121 -        try (var result = find.executeQuery()) {
   6.122 -            if (result.next()) {
   6.123 -                return mapColumns(result);
   6.124 -            } else {
   6.125 -                return null;
   6.126 -            }
   6.127 -        }
   6.128 +        return Functions.find(find, PGComponentDao::mapColumns);
   6.129 +    }
   6.130 +
   6.131 +    @Override
   6.132 +    public Component findByNode(Project project, String node) throws SQLException {
   6.133 +        findByNode.setInt(1, project.getId());
   6.134 +        findByNode.setString(2, node);;
   6.135 +        return Functions.find(findByNode, PGComponentDao::mapColumns);
   6.136      }
   6.137  }

     7.1 --- a/src/main/java/de/uapcore/lightpit/dao/postgres/PGIssueDao.java	Thu Oct 22 12:00:34 2020 +0200
     7.2 +++ b/src/main/java/de/uapcore/lightpit/dao/postgres/PGIssueDao.java	Thu Oct 22 13:03:26 2020 +0200
     7.3 @@ -48,43 +48,29 @@
     7.4      private final PreparedStatement insertComment, updateComment, listComments;
     7.5  
     7.6      public PGIssueDao(Connection connection) throws SQLException {
     7.7 -        list = connection.prepareStatement(
     7.8 -                "select issueid, i.project, p.name as projectname, component, c.name as componentname, " +
     7.9 +        final var query = "select issueid, i.project, p.name as projectname, p.node as projectnode, "+
    7.10 +                        "component, c.name as componentname, c.node as componentnode, " +
    7.11                          "status, category, subject, i.description, " +
    7.12                          "userid, username, givenname, lastname, mail, " +
    7.13                          "created, updated, eta " +
    7.14                          "from lpit_issue i " +
    7.15                          "join lpit_project p on i.project = projectid " +
    7.16                          "left join lpit_component c on component = c.id " +
    7.17 -                        "left join lpit_user on userid = assignee " +
    7.18 +                        "left join lpit_user on userid = assignee ";
    7.19 +
    7.20 +        list = connection.prepareStatement(query +
    7.21                          "where i.project = ? and coalesce(component, -1) = coalesce(?, component, -1)");
    7.22  
    7.23          listForVersion = connection.prepareStatement(
    7.24                  "with issue_version as ( "+
    7.25                          "select issueid, versionid from lpit_issue_affected_version union "+
    7.26                          "select issueid, versionid from lpit_issue_resolved_version) "+
    7.27 -                        "select issueid, i.project, p.name as projectname, component, c.name as componentname, " +
    7.28 -                        "status, category, subject, i.description, " +
    7.29 -                        "userid, username, givenname, lastname, mail, " +
    7.30 -                        "created, updated, eta " +
    7.31 -                        "from lpit_issue i " +
    7.32 -                        "join lpit_project p on i.project = projectid " +
    7.33 -                        "left join lpit_component c on component = c.id " +
    7.34 +                        query +
    7.35                          "left join issue_version using (issueid) "+
    7.36 -                        "left join lpit_user on userid = assignee " +
    7.37                          "where coalesce(versionid,-1) = ? and coalesce(component, -1) = coalesce(?, component, -1)"
    7.38          );
    7.39  
    7.40 -        find = connection.prepareStatement(
    7.41 -                "select issueid, i.project, p.name as projectname, component, c.name as componentname, " +
    7.42 -                        "status, category, subject, i.description, " +
    7.43 -                        "userid, username, givenname, lastname, mail, " +
    7.44 -                        "created, updated, eta " +
    7.45 -                        "from lpit_issue i " +
    7.46 -                        "join lpit_project p on i.project = projectid " +
    7.47 -                        "left join lpit_component c on component = c.id " +
    7.48 -                        "left join lpit_user on userid = assignee " +
    7.49 -                        "where issueid = ? ");
    7.50 +        find = connection.prepareStatement(query + "where issueid = ? ");
    7.51  
    7.52          insert = connection.prepareStatement(
    7.53                  "insert into lpit_issue (project, component, status, category, subject, description, assignee, eta) " +
    7.54 @@ -128,11 +114,13 @@
    7.55      private Issue mapColumns(ResultSet result) throws SQLException {
    7.56          final var project = new Project(result.getInt("project"));
    7.57          project.setName(result.getString("projectname"));
    7.58 +        project.setNode(result.getString("projectnode"));
    7.59          var component = new Component(result.getInt("component"));
    7.60          if (result.wasNull()) {
    7.61              component = null;
    7.62          } else {
    7.63              component.setName(result.getString("componentname"));
    7.64 +            component.setNode(result.getString("componentnode"));
    7.65          }
    7.66          final var issue = new Issue(result.getInt("issueid"));
    7.67          issue.setProject(project);

     8.1 --- a/src/main/java/de/uapcore/lightpit/dao/postgres/PGProjectDao.java	Thu Oct 22 12:00:34 2020 +0200
     8.2 +++ b/src/main/java/de/uapcore/lightpit/dao/postgres/PGProjectDao.java	Thu Oct 22 13:03:26 2020 +0200
     8.3 @@ -28,6 +28,7 @@
     8.4   */
     8.5  package de.uapcore.lightpit.dao.postgres;
     8.6  
     8.7 +import de.uapcore.lightpit.dao.Functions;
     8.8  import de.uapcore.lightpit.dao.ProjectDao;
     8.9  import de.uapcore.lightpit.entities.IssueSummary;
    8.10  import de.uapcore.lightpit.entities.Project;
    8.11 @@ -37,32 +38,26 @@
    8.12  import java.sql.PreparedStatement;
    8.13  import java.sql.ResultSet;
    8.14  import java.sql.SQLException;
    8.15 -import java.util.ArrayList;
    8.16  import java.util.List;
    8.17 -import java.util.Objects;
    8.18  
    8.19  import static de.uapcore.lightpit.dao.Functions.setForeignKeyOrNull;
    8.20  import static de.uapcore.lightpit.dao.Functions.setStringOrNull;
    8.21  
    8.22  public final class PGProjectDao implements ProjectDao {
    8.23  
    8.24 -    private final PreparedStatement insert, update, list, find;
    8.25 +    private final PreparedStatement insert, update, list, find, findByNode;
    8.26      private final PreparedStatement issue_summary;
    8.27  
    8.28      public PGProjectDao(Connection connection) throws SQLException {
    8.29 -        list = connection.prepareStatement(
    8.30 -                "select projectid, name, description, repourl, " +
    8.31 -                        "userid, username, lastname, givenname, mail " +
    8.32 -                        "from lpit_project " +
    8.33 -                        "left join lpit_user owner on lpit_project.owner = owner.userid " +
    8.34 -                        "order by name");
    8.35 +        final var query = "select projectid, name, node, description, repourl, " +
    8.36 +                "userid, username, lastname, givenname, mail " +
    8.37 +                "from lpit_project " +
    8.38 +                "left join lpit_user owner on lpit_project.owner = owner.userid ";
    8.39  
    8.40 -        find = connection.prepareStatement(
    8.41 -                "select projectid, name, description, repourl, " +
    8.42 -                        "userid, username, lastname, givenname, mail " +
    8.43 -                        "from lpit_project " +
    8.44 -                        "left join lpit_user owner on lpit_project.owner = owner.userid " +
    8.45 -                        "where projectid = ?");
    8.46 +        list = connection.prepareStatement(query + " order by name");
    8.47 +
    8.48 +        find = connection.prepareStatement(query + " where projectid = ?");
    8.49 +        findByNode = connection.prepareStatement(query + " where node = ?");
    8.50  
    8.51          issue_summary = connection.prepareStatement(
    8.52                  "select phase, count(*) as total "+
    8.53 @@ -73,16 +68,17 @@
    8.54          );
    8.55  
    8.56          insert = connection.prepareStatement(
    8.57 -                "insert into lpit_project (name, description, repourl, owner) values (?, ?, ?, ?)"
    8.58 +                "insert into lpit_project (name, node, description, repourl, owner) values (?, ?, ?, ?, ?)"
    8.59          );
    8.60          update = connection.prepareStatement(
    8.61 -                "update lpit_project set name = ?, description = ?, repourl = ?, owner = ? where projectid = ?"
    8.62 +                "update lpit_project set name = ?, node = ?, description = ?, repourl = ?, owner = ? where projectid = ?"
    8.63          );
    8.64      }
    8.65  
    8.66 -    public Project mapColumns(ResultSet result) throws SQLException {
    8.67 +    private static Project mapColumns(ResultSet result) throws SQLException {
    8.68          final var proj = new Project(result.getInt("projectid"));
    8.69          proj.setName(result.getString("name"));
    8.70 +        proj.setNode(result.getString("node"));
    8.71          proj.setDescription(result.getString("description"));
    8.72          proj.setRepoUrl(result.getString("repourl"));
    8.73          proj.setOwner(PGUserDao.mapColumns(result));
    8.74 @@ -112,50 +108,44 @@
    8.75          return summary;
    8.76      }
    8.77  
    8.78 +    private static int setColumns(PreparedStatement stmt, Project instance) throws SQLException {
    8.79 +        int column = 0;
    8.80 +        stmt.setString(++column, instance.getName());
    8.81 +        stmt.setString(++column, instance.getNode());
    8.82 +        setStringOrNull(stmt, ++column, instance.getDescription());
    8.83 +        setStringOrNull(stmt, ++column, instance.getRepoUrl());
    8.84 +        setForeignKeyOrNull(stmt, ++column, instance.getOwner(), User::getId);
    8.85 +        return column;
    8.86 +    }
    8.87 +
    8.88      @Override
    8.89      public void save(Project instance) throws SQLException {
    8.90 -        Objects.requireNonNull(instance.getName());
    8.91 -        insert.setString(1, instance.getName());
    8.92 -        setStringOrNull(insert, 2, instance.getDescription());
    8.93 -        setStringOrNull(insert, 3, instance.getRepoUrl());
    8.94 -        setForeignKeyOrNull(insert, 4, instance.getOwner(), User::getId);
    8.95 +        setColumns(insert, instance);
    8.96          insert.executeUpdate();
    8.97      }
    8.98  
    8.99      @Override
   8.100      public boolean update(Project instance) throws SQLException {
   8.101          if (instance.getId() < 0) return false;
   8.102 -        Objects.requireNonNull(instance.getName());
   8.103 -        update.setString(1, instance.getName());
   8.104 -        setStringOrNull(update, 2, instance.getDescription());
   8.105 -        setStringOrNull(update, 3, instance.getRepoUrl());
   8.106 -        setForeignKeyOrNull(update, 4, instance.getOwner(), User::getId);
   8.107 -        update.setInt(5, instance.getId());
   8.108 +        int column = setColumns(update, instance);
   8.109 +        update.setInt(++column, instance.getId());
   8.110          return update.executeUpdate() > 0;
   8.111      }
   8.112  
   8.113      @Override
   8.114      public List<Project> list() throws SQLException {
   8.115 -        List<Project> projects = new ArrayList<>();
   8.116 -        try (var result = list.executeQuery()) {
   8.117 -            while (result.next()) {
   8.118 -                final var project = mapColumns(result);
   8.119 -                projects.add(project);
   8.120 -            }
   8.121 -        }
   8.122 -        return projects;
   8.123 +        return Functions.list(list, PGProjectDao::mapColumns);
   8.124      }
   8.125  
   8.126      @Override
   8.127      public Project find(int id) throws SQLException {
   8.128          find.setInt(1, id);
   8.129 -        try (var result = find.executeQuery()) {
   8.130 -            if (result.next()) {
   8.131 -                final var project = mapColumns(result);
   8.132 -                return project;
   8.133 -            } else {
   8.134 -                return null;
   8.135 -            }
   8.136 -        }
   8.137 +        return Functions.find(find, PGProjectDao::mapColumns);
   8.138 +    }
   8.139 +
   8.140 +    @Override
   8.141 +    public Project findByNode(String node) throws SQLException {
   8.142 +        findByNode.setString(1, node);
   8.143 +        return Functions.find(findByNode, PGProjectDao::mapColumns);
   8.144      }
   8.145  }

     9.1 --- a/src/main/java/de/uapcore/lightpit/dao/postgres/PGVersionDao.java	Thu Oct 22 12:00:34 2020 +0200
     9.2 +++ b/src/main/java/de/uapcore/lightpit/dao/postgres/PGVersionDao.java	Thu Oct 22 13:03:26 2020 +0200
     9.3 @@ -28,6 +28,7 @@
     9.4   */
     9.5  package de.uapcore.lightpit.dao.postgres;
     9.6  
     9.7 +import de.uapcore.lightpit.dao.Functions;
     9.8  import de.uapcore.lightpit.dao.VersionDao;
     9.9  import de.uapcore.lightpit.entities.Project;
    9.10  import de.uapcore.lightpit.entities.Version;
    9.11 @@ -37,84 +38,77 @@
    9.12  import java.sql.PreparedStatement;
    9.13  import java.sql.ResultSet;
    9.14  import java.sql.SQLException;
    9.15 -import java.util.ArrayList;
    9.16  import java.util.List;
    9.17 -import java.util.Objects;
    9.18  
    9.19  public final class PGVersionDao implements VersionDao {
    9.20  
    9.21 -    private final PreparedStatement insert, update, list, find;
    9.22 +    private final PreparedStatement insert, update, list, find, findByNode;
    9.23  
    9.24      public PGVersionDao(Connection connection) throws SQLException {
    9.25 -        list = connection.prepareStatement(
    9.26 -                "select versionid, project, name, ordinal, status " +
    9.27 -                        "from lpit_version " +
    9.28 -                        "where project = ? " +
    9.29 +        final var query = "select versionid, project, name, node, ordinal, status from lpit_version";
    9.30 +
    9.31 +        list = connection.prepareStatement(query + " where project = ? " +
    9.32                          "order by ordinal desc, lower(name) desc");
    9.33 -
    9.34 -        find = connection.prepareStatement(
    9.35 -                "select versionid, project, name, ordinal, status " +
    9.36 -                        "from lpit_version  " +
    9.37 -                        "where versionid = ?");
    9.38 +        find = connection.prepareStatement(query + " where versionid = ?");
    9.39 +        findByNode = connection.prepareStatement(query + " where project = ? and node = ?");
    9.40  
    9.41          insert = connection.prepareStatement(
    9.42 -                "insert into lpit_version (project, name, ordinal, status) values (?, ?, ?, ?::version_status)"
    9.43 +                "insert into lpit_version (name, node, ordinal, status, project) values (?, ?, ?, ?::version_status, ?)"
    9.44          );
    9.45          update = connection.prepareStatement(
    9.46 -                "update lpit_version set name = ?, ordinal = ?, status = ?::version_status where versionid = ?"
    9.47 +                "update lpit_version set name = ?, node = ?, ordinal = ?, status = ?::version_status where versionid = ?"
    9.48          );
    9.49      }
    9.50  
    9.51 -    private Version mapColumns(ResultSet result) throws SQLException {
    9.52 +    private static Version mapColumns(ResultSet result) throws SQLException {
    9.53          final var version = new Version(result.getInt("versionid"));
    9.54          version.setName(result.getString("name"));
    9.55 +        version.setNode(result.getString("node"));
    9.56          version.setOrdinal(result.getInt("ordinal"));
    9.57          version.setStatus(VersionStatus.valueOf(result.getString("status")));
    9.58          return version;
    9.59      }
    9.60  
    9.61 +    private static int setFields(PreparedStatement stmt, Version instance) throws SQLException {
    9.62 +        int column = 0;
    9.63 +        stmt.setString(++column, instance.getName());
    9.64 +        stmt.setString(++column, instance.getNode());
    9.65 +        stmt.setInt(++column, instance.getOrdinal());
    9.66 +        stmt.setString(++column, instance.getStatus().name());
    9.67 +        return column;
    9.68 +    }
    9.69 +
    9.70      @Override
    9.71      public void save(Version instance, Project project) throws SQLException {
    9.72 -        Objects.requireNonNull(instance.getName());
    9.73 -        insert.setInt(1, project.getId());
    9.74 -        insert.setString(2, instance.getName());
    9.75 -        insert.setInt(3, instance.getOrdinal());
    9.76 -        insert.setString(4, instance.getStatus().name());
    9.77 +        int column = setFields(insert, instance);
    9.78 +        insert.setInt(++column, project.getId());
    9.79          insert.executeUpdate();
    9.80      }
    9.81  
    9.82      @Override
    9.83      public boolean update(Version instance) throws SQLException {
    9.84          if (instance.getId() < 0) return false;
    9.85 -        Objects.requireNonNull(instance.getName());
    9.86 -        update.setString(1, instance.getName());
    9.87 -        update.setInt(2, instance.getOrdinal());
    9.88 -        update.setString(3, instance.getStatus().name());
    9.89 -        update.setInt(4, instance.getId());
    9.90 +        int column = setFields(update, instance);
    9.91 +        update.setInt(++column, instance.getId());
    9.92          return update.executeUpdate() > 0;
    9.93      }
    9.94  
    9.95      @Override
    9.96      public List<Version> list(Project project) throws SQLException {
    9.97          list.setInt(1, project.getId());
    9.98 -        List<Version> versions = new ArrayList<>();
    9.99 -        try (var result = list.executeQuery()) {
   9.100 -            while (result.next()) {
   9.101 -                versions.add(mapColumns(result));
   9.102 -            }
   9.103 -        }
   9.104 -        return versions;
   9.105 +        return Functions.list(list, PGVersionDao::mapColumns);
   9.106      }
   9.107  
   9.108      @Override
   9.109      public Version find(int id) throws SQLException {
   9.110          find.setInt(1, id);
   9.111 -        try (var result = find.executeQuery()) {
   9.112 -            if (result.next()) {
   9.113 -                return mapColumns(result);
   9.114 -            } else {
   9.115 -                return null;
   9.116 -            }
   9.117 -        }
   9.118 +        return Functions.find(find, PGVersionDao::mapColumns);
   9.119 +    }
   9.120 +
   9.121 +    @Override
   9.122 +    public Version findByNode(Project project, String node) throws SQLException {
   9.123 +        findByNode.setInt(1, project.getId());
   9.124 +        findByNode.setString(2, node);;
   9.125 +        return Functions.find(findByNode, PGVersionDao::mapColumns);
   9.126      }
   9.127  }

    10.1 --- a/src/main/java/de/uapcore/lightpit/entities/Component.java	Thu Oct 22 12:00:34 2020 +0200
    10.2 +++ b/src/main/java/de/uapcore/lightpit/entities/Component.java	Thu Oct 22 13:03:26 2020 +0200
    10.3 @@ -69,7 +69,7 @@
    10.4      }
    10.5  
    10.6      public String getNode() {
    10.7 -        return node == null ? String.valueOf(id) : node;
    10.8 +        return node;
    10.9      }
   10.10  
   10.11      public void setNode(String node) {

    11.1 --- a/src/main/java/de/uapcore/lightpit/entities/Project.java	Thu Oct 22 12:00:34 2020 +0200
    11.2 +++ b/src/main/java/de/uapcore/lightpit/entities/Project.java	Thu Oct 22 13:03:26 2020 +0200
    11.3 @@ -56,7 +56,7 @@
    11.4      }
    11.5  
    11.6      public String getNode() {
    11.7 -        return node == null ? String.valueOf(id) : node;
    11.8 +        return node;
    11.9      }
   11.10  
   11.11      public void setNode(String node) {

    12.1 --- a/src/main/java/de/uapcore/lightpit/entities/Version.java	Thu Oct 22 12:00:34 2020 +0200
    12.2 +++ b/src/main/java/de/uapcore/lightpit/entities/Version.java	Thu Oct 22 13:03:26 2020 +0200
    12.3 @@ -58,7 +58,7 @@
    12.4      }
    12.5  
    12.6      public String getNode() {
    12.7 -        return node == null ? String.valueOf(id) : node;
    12.8 +        return node;
    12.9      }
   12.10  
   12.11      public void setNode(String node) {

    13.1 --- a/src/main/java/de/uapcore/lightpit/modules/ProjectsModule.java	Thu Oct 22 12:00:34 2020 +0200
    13.2 +++ b/src/main/java/de/uapcore/lightpit/modules/ProjectsModule.java	Thu Oct 22 13:03:26 2020 +0200
    13.3 @@ -74,42 +74,44 @@
    13.4              return;
    13.5  
    13.6          // Select Project
    13.7 -        final int pid = Functions.parseIntOrZero(pathParameters.get("project"));
    13.8 -        if (pid > 0) {
    13.9 -            final var project = projectDao.find(pid);
   13.10 -            if (project != null) {
   13.11 -                final var info = new ProjectInfo(project);
   13.12 -                info.setVersions(versionDao.list(project));
   13.13 -                info.setComponents(componentDao.list(project));
   13.14 -                info.setIssueSummary(projectDao.getIssueSummary(project));
   13.15 -                viewModel.setProjectInfo(info);
   13.16 -            }
   13.17 -        }
   13.18 +        final var project = projectDao.findByNode(pathParameters.get("project"));
   13.19 +        if (project == null)
   13.20 +            return;
   13.21 +
   13.22 +        final var info = new ProjectInfo(project);
   13.23 +        info.setVersions(versionDao.list(project));
   13.24 +        info.setComponents(componentDao.list(project));
   13.25 +        info.setIssueSummary(projectDao.getIssueSummary(project));
   13.26 +        viewModel.setProjectInfo(info);
   13.27  
   13.28          // Select Version
   13.29 -        final var pathParamVersion = pathParameters.get("version");
   13.30 -        if ("no-version".equals(pathParamVersion)) {
   13.31 +        final var versionNode = pathParameters.get("version");
   13.32 +        if ("no-version".equals(versionNode)) {
   13.33              viewModel.setVersionFilter(ProjectView.NO_VERSION);
   13.34 -        } else if ("all-versions".equals(pathParamVersion)) {
   13.35 +        } else if ("all-versions".equals(versionNode)) {
   13.36              viewModel.setVersionFilter(ProjectView.ALL_VERSIONS);
   13.37          } else {
   13.38 -            final int vid = Functions.parseIntOrZero(pathParamVersion);
   13.39 -            if (vid > 0) {
   13.40 -                viewModel.setVersionFilter(versionDao.find(vid));
   13.41 -            }
   13.42 +            viewModel.setVersionFilter(versionDao.findByNode(project, versionNode));
   13.43          }
   13.44  
   13.45          // Select Component
   13.46 -        final var pathParamComponent = pathParameters.get("component");
   13.47 -        if ("no-component".equals(pathParamComponent)) {
   13.48 +        final var componentNode = pathParameters.get("component");
   13.49 +        if ("no-component".equals(componentNode)) {
   13.50              viewModel.setComponentFilter(ProjectView.NO_COMPONENT);
   13.51 -        } else if ("all-components".equals(pathParamComponent)) {
   13.52 +        } else if ("all-components".equals(componentNode)) {
   13.53              viewModel.setComponentFilter(ProjectView.ALL_COMPONENTS);
   13.54          } else {
   13.55 -            final int cid = Functions.parseIntOrZero(pathParamComponent);
   13.56 -            if (cid > 0) {
   13.57 -                viewModel.setComponentFilter(componentDao.find(cid));
   13.58 -            }
   13.59 +            viewModel.setComponentFilter(componentDao.findByNode(project, componentNode));
   13.60 +        }
   13.61 +    }
   13.62 +
   13.63 +    private static String sanitizeNode(String node, String defaultValue) {
   13.64 +        String result = node == null || node.isBlank() ? defaultValue : node;
   13.65 +        result = result.replace('/', '-');
   13.66 +        if (result.equals(".") || result.equals("..")) {
   13.67 +            return "_"+result;
   13.68 +        } else {
   13.69 +            return result;
   13.70          }
   13.71      }
   13.72  
   13.73 @@ -170,6 +172,10 @@
   13.74          try {
   13.75              final var project = new Project(getParameter(req, Integer.class, "pid").orElseThrow());
   13.76              project.setName(getParameter(req, String.class, "name").orElseThrow());
   13.77 +
   13.78 +            final var node = getParameter(req, String.class, "node").orElse(null);
   13.79 +            project.setNode(sanitizeNode(node, project.getName()));
   13.80 +
   13.81              getParameter(req, String.class, "description").ifPresent(project::setDescription);
   13.82              getParameter(req, String.class, "repoUrl").ifPresent(project::setRepoUrl);
   13.83              getParameter(req, Integer.class, "owner").map(
   13.84 @@ -301,14 +307,23 @@
   13.85      public ResponseType commitVersion(HttpServletRequest req, HttpServletResponse resp, DataAccessObjects dao) throws IOException {
   13.86  
   13.87          try {
   13.88 -            final var project = new Project(getParameter(req, Integer.class, "pid").orElseThrow());
   13.89 +            final var project = dao.getProjectDao().find(getParameter(req, Integer.class, "pid").orElseThrow());
   13.90 +            if (project == null) {
   13.91 +                // TODO: improve error handling, because not found is not correct for this POST request
   13.92 +                resp.sendError(HttpServletResponse.SC_NOT_FOUND);
   13.93 +                return ResponseType.NONE;
   13.94 +            }
   13.95              final var version = new Version(getParameter(req, Integer.class, "id").orElseThrow());
   13.96              version.setName(getParameter(req, String.class, "name").orElseThrow());
   13.97 +
   13.98 +            final var node = getParameter(req, String.class, "node").orElse(null);
   13.99 +            version.setNode(sanitizeNode(node, version.getName()));
  13.100 +
  13.101              getParameter(req, Integer.class, "ordinal").ifPresent(version::setOrdinal);
  13.102              version.setStatus(VersionStatus.valueOf(getParameter(req, String.class, "status").orElseThrow()));
  13.103              dao.getVersionDao().saveOrUpdate(version, project);
  13.104  
  13.105 -            setRedirectLocation(req, "./projects/" + project.getId() + "/versions/");
  13.106 +            setRedirectLocation(req, "./projects/" + project.getNode() + "/versions/");
  13.107              setContentPage(req, Constants.JSP_COMMIT_SUCCESSFUL);
  13.108          } catch (NoSuchElementException | IllegalArgumentException | SQLException ex) {
  13.109              resp.sendError(HttpServletResponse.SC_NOT_IMPLEMENTED);
  13.110 @@ -373,9 +388,18 @@
  13.111      public ResponseType commitComponent(HttpServletRequest req, HttpServletResponse resp, DataAccessObjects dao) throws IOException {
  13.112  
  13.113          try {
  13.114 -            final var project = new Project(getParameter(req, Integer.class, "pid").orElseThrow());
  13.115 +            final var project = dao.getProjectDao().find(getParameter(req, Integer.class, "pid").orElseThrow());
  13.116 +            if (project == null) {
  13.117 +                // TODO: improve error handling, because not found is not correct for this POST request
  13.118 +                resp.sendError(HttpServletResponse.SC_NOT_FOUND);
  13.119 +                return ResponseType.NONE;
  13.120 +            }
  13.121              final var component = new Component(getParameter(req, Integer.class, "id").orElseThrow());
  13.122              component.setName(getParameter(req, String.class, "name").orElseThrow());
  13.123 +
  13.124 +            final var node = getParameter(req, String.class, "node").orElse(null);
  13.125 +            component.setNode(sanitizeNode(node, component.getName()));
  13.126 +
  13.127              component.setColor(getParameter(req, WebColor.class, "color").orElseThrow());
  13.128              getParameter(req, Integer.class, "ordinal").ifPresent(component::setOrdinal);
  13.129              getParameter(req, Integer.class, "lead").map(
  13.130 @@ -385,7 +409,7 @@
  13.131  
  13.132              dao.getComponentDao().saveOrUpdate(component, project);
  13.133  
  13.134 -            setRedirectLocation(req, "./projects/" + project.getId() + "/components/");
  13.135 +            setRedirectLocation(req, "./projects/" + project.getNode() + "/components/");
  13.136              setContentPage(req, Constants.JSP_COMMIT_SUCCESSFUL);
  13.137          } catch (NoSuchElementException | IllegalArgumentException | SQLException ex) {
  13.138              resp.sendError(HttpServletResponse.SC_NOT_IMPLEMENTED);
  13.139 @@ -461,7 +485,13 @@
  13.140              } else {
  13.141                  component = null;
  13.142              }
  13.143 -            issue.setProject(new Project(getParameter(req, Integer.class, "pid").orElseThrow()));
  13.144 +            final var project = dao.getProjectDao().find(getParameter(req, Integer.class, "pid").orElseThrow());
  13.145 +            if (project == null) {
  13.146 +                // TODO: improve error handling, because not found is not correct for this POST request
  13.147 +                resp.sendError(HttpServletResponse.SC_NOT_FOUND);
  13.148 +                return ResponseType.NONE;
  13.149 +            }
  13.150 +            issue.setProject(project);
  13.151              getParameter(req, String.class, "category").map(IssueCategory::valueOf).ifPresent(issue::setCategory);
  13.152              getParameter(req, String.class, "status").map(IssueStatus::valueOf).ifPresent(issue::setStatus);
  13.153              issue.setSubject(getParameter(req, String.class, "subject").orElseThrow());
  13.154 @@ -493,7 +523,7 @@
  13.155              dao.getIssueDao().saveOrUpdate(issue, issue.getProject());
  13.156  
  13.157              // TODO: fix issue #14
  13.158 -            setRedirectLocation(req, "./projects/" + issue.getProject().getId() + "/all-components/all-versions/issues/");
  13.159 +            setRedirectLocation(req, "./projects/" + issue.getProject().getNode() + "/all-components/all-versions/issues/");
  13.160              setContentPage(req, Constants.JSP_COMMIT_SUCCESSFUL);
  13.161  
  13.162              return ResponseType.HTML;
  13.163 @@ -532,7 +562,7 @@
  13.164              dao.getIssueDao().saveComment(issueComment);
  13.165  
  13.166              // TODO: fix redirect location (e.g. after fixing #24)
  13.167 -            setRedirectLocation(req, "./projects/" + issue.getProject().getId()+"/issues/"+issue.getId()+"/edit");
  13.168 +            setRedirectLocation(req, "./projects/" + issue.getProject().getNode()+"/issues/"+issue.getId()+"/edit");
  13.169              setContentPage(req, Constants.JSP_COMMIT_SUCCESSFUL);
  13.170  
  13.171              return ResponseType.HTML;

    14.1 --- a/src/main/resources/localization/projects.properties	Thu Oct 22 12:00:34 2020 +0200
    14.2 +++ b/src/main/resources/localization/projects.properties	Thu Oct 22 13:03:26 2020 +0200
    14.3 @@ -40,6 +40,8 @@
    14.4  navmenu.all=all
    14.5  
    14.6  name=Name
    14.7 +node=Node
    14.8 +node.tooltip=Name of the path node that will be used in URL construction.
    14.9  description=Description
   14.10  repoUrl=Repository
   14.11  owner=Project Lead

    15.1 --- a/src/main/resources/localization/projects_de.properties	Thu Oct 22 12:00:34 2020 +0200
    15.2 +++ b/src/main/resources/localization/projects_de.properties	Thu Oct 22 13:03:26 2020 +0200
    15.3 @@ -40,6 +40,8 @@
    15.4  navmenu.all=Alle
    15.5  
    15.6  name=Name
    15.7 +node=Pfadname
    15.8 +node.tooltip=Name, der zur Konstruktion der URL genutzt werden soll.
    15.9  description=Beschreibung
   15.10  repoUrl=Repository
   15.11  owner=Projektleitung

    16.1 --- a/src/main/webapp/WEB-INF/jsp/component-form.jsp	Thu Oct 22 12:00:34 2020 +0200
    16.2 +++ b/src/main/webapp/WEB-INF/jsp/component-form.jsp	Thu Oct 22 13:03:26 2020 +0200
    16.3 @@ -50,6 +50,10 @@
    16.4              <th><fmt:message key="component.name"/></th>
    16.5              <td><input name="name" type="text" maxlength="20" required value="<c:out value="${component.name}"/>" /></td>
    16.6          </tr>
    16.7 +        <tr title="<fmt:message key="node.tooltip"/>">
    16.8 +            <th><fmt:message key="node"/></th>
    16.9 +            <td><input name="node" type="text" maxlength="20" required value="<c:out value="${component.node}"/>" /></td>
   16.10 +        </tr>
   16.11          <tr>
   16.12              <th><fmt:message key="component.color"/></th>
   16.13              <td><input name="color" type="color" required value="${component.color}" /></td>

    17.1 --- a/src/main/webapp/WEB-INF/jsp/project-form.jsp	Thu Oct 22 12:00:34 2020 +0200
    17.2 +++ b/src/main/webapp/WEB-INF/jsp/project-form.jsp	Thu Oct 22 13:03:26 2020 +0200
    17.3 @@ -42,6 +42,10 @@
    17.4              <th><fmt:message key="name"/></th>
    17.5              <td><input name="name" type="text" maxlength="20" required value="<c:out value="${project.name}"/>" /></td>
    17.6          </tr>
    17.7 +        <tr title="<fmt:message key="node.tooltip"/>">
    17.8 +            <th><fmt:message key="node"/></th>
    17.9 +            <td><input name="node" type="text" maxlength="20" required value="<c:out value="${project.node}"/>" /></td>
   17.10 +        </tr>
   17.11          <tr>
   17.12              <th><fmt:message key="description"/></th>
   17.13              <td><input type="text" name="description" maxlength="200" value="<c:out value="${project.description}"/>" /></td>

    18.1 --- a/src/main/webapp/WEB-INF/jsp/version-form.jsp	Thu Oct 22 12:00:34 2020 +0200
    18.2 +++ b/src/main/webapp/WEB-INF/jsp/version-form.jsp	Thu Oct 22 13:03:26 2020 +0200
    18.3 @@ -50,6 +50,10 @@
    18.4              <th><fmt:message key="version.name"/></th>
    18.5              <td><input name="name" type="text" maxlength="20" required value="<c:out value="${version.name}"/>" /></td>
    18.6          </tr>
    18.7 +        <tr title="<fmt:message key="node.tooltip"/>">
    18.8 +            <th><fmt:message key="node"/></th>
    18.9 +            <td><input name="node" type="text" maxlength="20" required value="<c:out value="${version.node}"/>" /></td>
   18.10 +        </tr>
   18.11          <tr>
   18.12              <th><fmt:message key="version.status"/></th>
   18.13              <td>