mike@159: /*
mike@159:  * Copyright 2020 Mike Becker. All rights reserved.
mike@159:  *
mike@159:  * Redistribution and use in source and binary forms, with or without
mike@159:  * modification, are permitted provided that the following conditions are met:
mike@159:  *
mike@159:  * 1. Redistributions of source code must retain the above copyright
mike@159:  * notice, this list of conditions and the following disclaimer.
mike@159:  *
mike@159:  * 2. Redistributions in binary form must reproduce the above copyright
mike@159:  * notice, this list of conditions and the following disclaimer in the
mike@159:  * documentation and/or other materials provided with the distribution.
mike@159:  *
mike@159:  * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
mike@159:  * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
mike@159:  * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
mike@159:  * DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT HOLDER OR CONTRIBUTORS BE LIABLE
mike@159:  * FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL
mike@159:  * DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR
mike@159:  * SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER
mike@159:  * CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY,
mike@159:  * OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE
mike@159:  * OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
mike@159:  *
mike@159:  */
mike@159: 
mike@159: package de.uapcore.lightpit.dao.postgres
mike@159: 
mike@159: import de.uapcore.lightpit.dao.AbstractProjectDao
mike@159: import de.uapcore.lightpit.dao.Functions
mike@159: import de.uapcore.lightpit.entities.IssueSummary
mike@159: import de.uapcore.lightpit.entities.Project
mike@159: import de.uapcore.lightpit.entities.User
mike@159: import java.sql.Connection
mike@159: import java.sql.PreparedStatement
mike@159: import java.sql.ResultSet
mike@159: 
mike@159: class PGProjectDao(connection: Connection) : AbstractProjectDao() {
mike@159: 
mike@159:     private val query = "select projectid, name, node, description, repourl, " +
mike@159:             "userid, username, lastname, givenname, mail " +
mike@159:             "from lpit_project " +
mike@159:             "left join lpit_user owner on lpit_project.owner = owner.userid "
mike@159: 
mike@159:     private val listStmt = connection.prepareStatement("$query order by name")
mike@159:     private val findStmt = connection.prepareStatement("$query where projectid = ?")
mike@159:     private val findByNodeStmt = connection.prepareStatement("$query where node = ?")
mike@159:     private val issueSummaryStmt = connection.prepareStatement(
mike@159:             "select phase, count(*) as total " +
mike@159:                     "from lpit_issue " +
mike@159:                     "join lpit_issue_phases using(status) " +
mike@159:                     "where project = ? " +
mike@159:                     "group by phase "
mike@159:     )
mike@159:     private val insertStmt = connection.prepareStatement(
mike@159:             "insert into lpit_project (name, node, description, repourl, owner) values (?, ?, ?, ?, ?)"
mike@159:     )
mike@159:     private val updateStmt = connection.prepareStatement(
mike@159:             "update lpit_project set name = ?, node = ?, description = ?, repourl = ?, owner = ? where projectid = ?"
mike@159:     )
mike@159: 
mike@159:     override fun mapResult(rs: ResultSet): Project {
mike@159:         val proj = Project(rs.getInt("projectid"))
mike@159:         proj.name = rs.getString("name")
mike@159:         proj.node = rs.getString("node")
mike@159:         proj.description = rs.getString("description")
mike@159:         proj.repoUrl = rs.getString("repourl")
mike@159:         proj.owner = PGUserDao.mapResult(rs).takeUnless { rs.wasNull() }
mike@159:         return proj
mike@159:     }
mike@159: 
mike@159:     override fun getIssueSummary(project: Project): IssueSummary {
mike@159:         issueSummaryStmt.setInt(1, project.id)
mike@159:         val result = issueSummaryStmt.executeQuery()
mike@159:         val summary = IssueSummary()
mike@159:         while (result.next()) {
mike@159:             val phase = result.getInt("phase")
mike@159:             val total = result.getInt("total")
mike@159:             when (phase) {
mike@159:                 0 -> summary.open = total
mike@159:                 1 -> summary.active = total
mike@159:                 2 -> summary.done = total
mike@159:             }
mike@159:         }
mike@159:         return summary
mike@159:     }
mike@159: 
mike@159:     private fun setColumns(stmt: PreparedStatement, instance: Project): Int {
mike@159:         var column = 0
mike@159:         stmt.setString(++column, instance.name)
mike@159:         stmt.setString(++column, instance.node)
mike@159:         Functions.setStringOrNull(stmt, ++column, instance.description)
mike@159:         Functions.setStringOrNull(stmt, ++column, instance.repoUrl)
mike@159:         setForeignKeyOrNull(stmt, ++column, instance.owner, User::id)
mike@159:         return column
mike@159:     }
mike@159: 
mike@159:     override fun save(instance: Project) {
mike@159:         setColumns(insertStmt, instance)
mike@159:         insertStmt.executeUpdate()
mike@159:     }
mike@159: 
mike@159:     override fun update(instance: Project): Boolean {
mike@159:         var column = setColumns(updateStmt, instance)
mike@159:         updateStmt.setInt(++column, instance.id)
mike@159:         return updateStmt.executeUpdate() > 0
mike@159:     }
mike@159: 
mike@159:     override fun list(): List<Project> {
mike@159:         return super.list(listStmt)
mike@159:     }
mike@159: 
mike@159:     override fun find(id: Int): Project? {
mike@159:         findStmt.setInt(1, id)
mike@159:         return super.find(findStmt)
mike@159:     }
mike@159: 
mike@159:     override fun findByNode(node: String): Project? {
mike@159:         findByNodeStmt.setString(1, node)
mike@159:         return super.find(findByNodeStmt)
mike@159:     }
mike@159: }