1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000
1.2 +++ b/src/main/kotlin/de/uapcore/lightpit/dao/postgres/PGProjectDao.kt Thu Nov 19 13:58:54 2020 +0100
1.3 @@ -0,0 +1,122 @@
1.4 +/*
1.5 + * Copyright 2020 Mike Becker. All rights reserved.
1.6 + *
1.7 + * Redistribution and use in source and binary forms, with or without
1.8 + * modification, are permitted provided that the following conditions are met:
1.9 + *
1.10 + * 1. Redistributions of source code must retain the above copyright
1.11 + * notice, this list of conditions and the following disclaimer.
1.12 + *
1.13 + * 2. Redistributions in binary form must reproduce the above copyright
1.14 + * notice, this list of conditions and the following disclaimer in the
1.15 + * documentation and/or other materials provided with the distribution.
1.16 + *
1.17 + * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
1.18 + * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
1.19 + * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
1.20 + * DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT HOLDER OR CONTRIBUTORS BE LIABLE
1.21 + * FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL
1.22 + * DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR
1.23 + * SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER
1.24 + * CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY,
1.25 + * OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE
1.26 + * OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
1.27 + *
1.28 + */
1.29 +
1.30 +package de.uapcore.lightpit.dao.postgres
1.31 +
1.32 +import de.uapcore.lightpit.dao.AbstractProjectDao
1.33 +import de.uapcore.lightpit.dao.Functions
1.34 +import de.uapcore.lightpit.entities.IssueSummary
1.35 +import de.uapcore.lightpit.entities.Project
1.36 +import de.uapcore.lightpit.entities.User
1.37 +import java.sql.Connection
1.38 +import java.sql.PreparedStatement
1.39 +import java.sql.ResultSet
1.40 +
1.41 +class PGProjectDao(connection: Connection) : AbstractProjectDao() {
1.42 +
1.43 + private val query = "select projectid, name, node, description, repourl, " +
1.44 + "userid, username, lastname, givenname, mail " +
1.45 + "from lpit_project " +
1.46 + "left join lpit_user owner on lpit_project.owner = owner.userid "
1.47 +
1.48 + private val listStmt = connection.prepareStatement("$query order by name")
1.49 + private val findStmt = connection.prepareStatement("$query where projectid = ?")
1.50 + private val findByNodeStmt = connection.prepareStatement("$query where node = ?")
1.51 + private val issueSummaryStmt = connection.prepareStatement(
1.52 + "select phase, count(*) as total " +
1.53 + "from lpit_issue " +
1.54 + "join lpit_issue_phases using(status) " +
1.55 + "where project = ? " +
1.56 + "group by phase "
1.57 + )
1.58 + private val insertStmt = connection.prepareStatement(
1.59 + "insert into lpit_project (name, node, description, repourl, owner) values (?, ?, ?, ?, ?)"
1.60 + )
1.61 + private val updateStmt = connection.prepareStatement(
1.62 + "update lpit_project set name = ?, node = ?, description = ?, repourl = ?, owner = ? where projectid = ?"
1.63 + )
1.64 +
1.65 + override fun mapResult(rs: ResultSet): Project {
1.66 + val proj = Project(rs.getInt("projectid"))
1.67 + proj.name = rs.getString("name")
1.68 + proj.node = rs.getString("node")
1.69 + proj.description = rs.getString("description")
1.70 + proj.repoUrl = rs.getString("repourl")
1.71 + proj.owner = PGUserDao.mapResult(rs).takeUnless { rs.wasNull() }
1.72 + return proj
1.73 + }
1.74 +
1.75 + override fun getIssueSummary(project: Project): IssueSummary {
1.76 + issueSummaryStmt.setInt(1, project.id)
1.77 + val result = issueSummaryStmt.executeQuery()
1.78 + val summary = IssueSummary()
1.79 + while (result.next()) {
1.80 + val phase = result.getInt("phase")
1.81 + val total = result.getInt("total")
1.82 + when (phase) {
1.83 + 0 -> summary.open = total
1.84 + 1 -> summary.active = total
1.85 + 2 -> summary.done = total
1.86 + }
1.87 + }
1.88 + return summary
1.89 + }
1.90 +
1.91 + private fun setColumns(stmt: PreparedStatement, instance: Project): Int {
1.92 + var column = 0
1.93 + stmt.setString(++column, instance.name)
1.94 + stmt.setString(++column, instance.node)
1.95 + Functions.setStringOrNull(stmt, ++column, instance.description)
1.96 + Functions.setStringOrNull(stmt, ++column, instance.repoUrl)
1.97 + setForeignKeyOrNull(stmt, ++column, instance.owner, User::id)
1.98 + return column
1.99 + }
1.100 +
1.101 + override fun save(instance: Project) {
1.102 + setColumns(insertStmt, instance)
1.103 + insertStmt.executeUpdate()
1.104 + }
1.105 +
1.106 + override fun update(instance: Project): Boolean {
1.107 + var column = setColumns(updateStmt, instance)
1.108 + updateStmt.setInt(++column, instance.id)
1.109 + return updateStmt.executeUpdate() > 0
1.110 + }
1.111 +
1.112 + override fun list(): List<Project> {
1.113 + return super.list(listStmt)
1.114 + }
1.115 +
1.116 + override fun find(id: Int): Project? {
1.117 + findStmt.setInt(1, id)
1.118 + return super.find(findStmt)
1.119 + }
1.120 +
1.121 + override fun findByNode(node: String): Project? {
1.122 + findByNodeStmt.setString(1, node)
1.123 + return super.find(findByNodeStmt)
1.124 + }
1.125 +}
1.126 \ No newline at end of file
