Thu, 19 Nov 2020 13:58:54 +0100
migrates DAO classes
mike@159 | 1 | /* |
mike@159 | 2 | * Copyright 2020 Mike Becker. All rights reserved. |
mike@159 | 3 | * |
mike@159 | 4 | * Redistribution and use in source and binary forms, with or without |
mike@159 | 5 | * modification, are permitted provided that the following conditions are met: |
mike@159 | 6 | * |
mike@159 | 7 | * 1. Redistributions of source code must retain the above copyright |
mike@159 | 8 | * notice, this list of conditions and the following disclaimer. |
mike@159 | 9 | * |
mike@159 | 10 | * 2. Redistributions in binary form must reproduce the above copyright |
mike@159 | 11 | * notice, this list of conditions and the following disclaimer in the |
mike@159 | 12 | * documentation and/or other materials provided with the distribution. |
mike@159 | 13 | * |
mike@159 | 14 | * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" |
mike@159 | 15 | * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE |
mike@159 | 16 | * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE |
mike@159 | 17 | * DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT HOLDER OR CONTRIBUTORS BE LIABLE |
mike@159 | 18 | * FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL |
mike@159 | 19 | * DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR |
mike@159 | 20 | * SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER |
mike@159 | 21 | * CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, |
mike@159 | 22 | * OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE |
mike@159 | 23 | * OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. |
mike@159 | 24 | * |
mike@159 | 25 | */ |
mike@159 | 26 | |
mike@159 | 27 | package de.uapcore.lightpit.dao.postgres |
mike@159 | 28 | |
mike@159 | 29 | import de.uapcore.lightpit.dao.AbstractProjectDao |
mike@159 | 30 | import de.uapcore.lightpit.dao.Functions |
mike@159 | 31 | import de.uapcore.lightpit.entities.IssueSummary |
mike@159 | 32 | import de.uapcore.lightpit.entities.Project |
mike@159 | 33 | import de.uapcore.lightpit.entities.User |
mike@159 | 34 | import java.sql.Connection |
mike@159 | 35 | import java.sql.PreparedStatement |
mike@159 | 36 | import java.sql.ResultSet |
mike@159 | 37 | |
mike@159 | 38 | class PGProjectDao(connection: Connection) : AbstractProjectDao() { |
mike@159 | 39 | |
mike@159 | 40 | private val query = "select projectid, name, node, description, repourl, " + |
mike@159 | 41 | "userid, username, lastname, givenname, mail " + |
mike@159 | 42 | "from lpit_project " + |
mike@159 | 43 | "left join lpit_user owner on lpit_project.owner = owner.userid " |
mike@159 | 44 | |
mike@159 | 45 | private val listStmt = connection.prepareStatement("$query order by name") |
mike@159 | 46 | private val findStmt = connection.prepareStatement("$query where projectid = ?") |
mike@159 | 47 | private val findByNodeStmt = connection.prepareStatement("$query where node = ?") |
mike@159 | 48 | private val issueSummaryStmt = connection.prepareStatement( |
mike@159 | 49 | "select phase, count(*) as total " + |
mike@159 | 50 | "from lpit_issue " + |
mike@159 | 51 | "join lpit_issue_phases using(status) " + |
mike@159 | 52 | "where project = ? " + |
mike@159 | 53 | "group by phase " |
mike@159 | 54 | ) |
mike@159 | 55 | private val insertStmt = connection.prepareStatement( |
mike@159 | 56 | "insert into lpit_project (name, node, description, repourl, owner) values (?, ?, ?, ?, ?)" |
mike@159 | 57 | ) |
mike@159 | 58 | private val updateStmt = connection.prepareStatement( |
mike@159 | 59 | "update lpit_project set name = ?, node = ?, description = ?, repourl = ?, owner = ? where projectid = ?" |
mike@159 | 60 | ) |
mike@159 | 61 | |
mike@159 | 62 | override fun mapResult(rs: ResultSet): Project { |
mike@159 | 63 | val proj = Project(rs.getInt("projectid")) |
mike@159 | 64 | proj.name = rs.getString("name") |
mike@159 | 65 | proj.node = rs.getString("node") |
mike@159 | 66 | proj.description = rs.getString("description") |
mike@159 | 67 | proj.repoUrl = rs.getString("repourl") |
mike@159 | 68 | proj.owner = PGUserDao.mapResult(rs).takeUnless { rs.wasNull() } |
mike@159 | 69 | return proj |
mike@159 | 70 | } |
mike@159 | 71 | |
mike@159 | 72 | override fun getIssueSummary(project: Project): IssueSummary { |
mike@159 | 73 | issueSummaryStmt.setInt(1, project.id) |
mike@159 | 74 | val result = issueSummaryStmt.executeQuery() |
mike@159 | 75 | val summary = IssueSummary() |
mike@159 | 76 | while (result.next()) { |
mike@159 | 77 | val phase = result.getInt("phase") |
mike@159 | 78 | val total = result.getInt("total") |
mike@159 | 79 | when (phase) { |
mike@159 | 80 | 0 -> summary.open = total |
mike@159 | 81 | 1 -> summary.active = total |
mike@159 | 82 | 2 -> summary.done = total |
mike@159 | 83 | } |
mike@159 | 84 | } |
mike@159 | 85 | return summary |
mike@159 | 86 | } |
mike@159 | 87 | |
mike@159 | 88 | private fun setColumns(stmt: PreparedStatement, instance: Project): Int { |
mike@159 | 89 | var column = 0 |
mike@159 | 90 | stmt.setString(++column, instance.name) |
mike@159 | 91 | stmt.setString(++column, instance.node) |
mike@159 | 92 | Functions.setStringOrNull(stmt, ++column, instance.description) |
mike@159 | 93 | Functions.setStringOrNull(stmt, ++column, instance.repoUrl) |
mike@159 | 94 | setForeignKeyOrNull(stmt, ++column, instance.owner, User::id) |
mike@159 | 95 | return column |
mike@159 | 96 | } |
mike@159 | 97 | |
mike@159 | 98 | override fun save(instance: Project) { |
mike@159 | 99 | setColumns(insertStmt, instance) |
mike@159 | 100 | insertStmt.executeUpdate() |
mike@159 | 101 | } |
mike@159 | 102 | |
mike@159 | 103 | override fun update(instance: Project): Boolean { |
mike@159 | 104 | var column = setColumns(updateStmt, instance) |
mike@159 | 105 | updateStmt.setInt(++column, instance.id) |
mike@159 | 106 | return updateStmt.executeUpdate() > 0 |
mike@159 | 107 | } |
mike@159 | 108 | |
mike@159 | 109 | override fun list(): List<Project> { |
mike@159 | 110 | return super.list(listStmt) |
mike@159 | 111 | } |
mike@159 | 112 | |
mike@159 | 113 | override fun find(id: Int): Project? { |
mike@159 | 114 | findStmt.setInt(1, id) |
mike@159 | 115 | return super.find(findStmt) |
mike@159 | 116 | } |
mike@159 | 117 | |
mike@159 | 118 | override fun findByNode(node: String): Project? { |
mike@159 | 119 | findByNodeStmt.setString(1, node) |
mike@159 | 120 | return super.find(findByNodeStmt) |
mike@159 | 121 | } |
mike@159 | 122 | } |