From 8543d1c7ae576fdbf13c6926be26cf323f3f75ea Mon Sep 17 00:00:00 2001 From: =?utf8?q?Felix=20D=C3=B6rre?= Date: Tue, 1 Aug 2017 20:28:28 +0200 Subject: [PATCH] upd: cleanup SQL statements Change-Id: If22b05270d1f097068e38f6ed860d52740926021 --- .../gigi/database/GigiPreparedStatement.java | 6 ++-- src/club/wpia/gigi/dbObjects/Certificate.java | 8 ++--- src/club/wpia/gigi/ping/PingerDaemon.java | 32 +++++++++---------- 3 files changed, 24 insertions(+), 22 deletions(-) diff --git a/src/club/wpia/gigi/database/GigiPreparedStatement.java b/src/club/wpia/gigi/database/GigiPreparedStatement.java index 4957d4d3..e8c0d832 100644 --- a/src/club/wpia/gigi/database/GigiPreparedStatement.java +++ b/src/club/wpia/gigi/database/GigiPreparedStatement.java @@ -1,6 +1,7 @@ package club.wpia.gigi.database; import java.sql.Date; +import java.sql.ParameterMetaData; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; @@ -138,10 +139,11 @@ public class GigiPreparedStatement implements AutoCloseable { } } - public int getParameterCount() { + public ParameterMetaData getParameterMetaData() { try { - return target.getParameterMetaData().getParameterCount(); + return target.getParameterMetaData(); } catch (SQLException e) { + handleSQL(e); throw new Error(e); } } diff --git a/src/club/wpia/gigi/dbObjects/Certificate.java b/src/club/wpia/gigi/dbObjects/Certificate.java index 72cd3c19..ffc51547 100644 --- a/src/club/wpia/gigi/dbObjects/Certificate.java +++ b/src/club/wpia/gigi/dbObjects/Certificate.java @@ -394,12 +394,13 @@ public class Certificate implements IdCachable { return profile; } + private static final String CONCAT = "string_agg(concat('/', `name`, '=', REPLACE(REPLACE(value, '\\\\', '\\\\\\\\'), '/', '\\\\/')), '')"; + public synchronized static Certificate getBySerial(String serial) { if (serial == null || "".equals(serial)) { return null; } - String concat = "string_agg(concat('/', `name`, '=', REPLACE(REPLACE(value, '\\\\', '\\\\\\\\'), '/', '\\\\/')), '')"; - try (GigiPreparedStatement ps = new GigiPreparedStatement("SELECT certs.id, " + concat + " as `subject`, `md`, `csr_name`, `crt_name`,`memid`, `profile`, `certs`.`serial` FROM `certs` LEFT JOIN `certAvas` ON `certAvas`.`certId`=`certs`.`id` WHERE `serial`=? GROUP BY `certs`.`id`")) { + try (GigiPreparedStatement ps = new GigiPreparedStatement("SELECT certs.id, " + CONCAT + " as `subject`, `md`, `csr_name`, `crt_name`,`memid`, `profile`, `certs`.`serial` FROM `certs` LEFT JOIN `certAvas` ON `certAvas`.`certId`=`certs`.`id` WHERE `serial`=? GROUP BY `certs`.`id`")) { ps.setString(1, serial); GigiResultSet rs = ps.executeQuery(); if ( !rs.next()) { @@ -425,8 +426,7 @@ public class Certificate implements IdCachable { } try { - String concat = "string_agg(concat('/', `name`, '=', REPLACE(REPLACE(value, '\\\\', '\\\\\\\\'), '/', '\\\\/')), '')"; - try (GigiPreparedStatement ps = new GigiPreparedStatement("SELECT certs.id, " + concat + " as subject, md, csr_name, crt_name,memid, profile, certs.serial FROM `certs` LEFT JOIN `certAvas` ON `certAvas`.`certId`=certs.id WHERE certs.id=? GROUP BY certs.id")) { + try (GigiPreparedStatement ps = new GigiPreparedStatement("SELECT certs.id, " + CONCAT + " as subject, md, csr_name, crt_name,memid, profile, certs.serial FROM `certs` LEFT JOIN `certAvas` ON `certAvas`.`certId`=certs.id WHERE certs.id=? GROUP BY certs.id")) { ps.setInt(1, id); GigiResultSet rs = ps.executeQuery(); if ( !rs.next()) { diff --git a/src/club/wpia/gigi/ping/PingerDaemon.java b/src/club/wpia/gigi/ping/PingerDaemon.java index 1cd48cf1..51cc9858 100644 --- a/src/club/wpia/gigi/ping/PingerDaemon.java +++ b/src/club/wpia/gigi/ping/PingerDaemon.java @@ -6,9 +6,9 @@ import java.util.LinkedList; import java.util.Queue; import club.wpia.gigi.database.DatabaseConnection; +import club.wpia.gigi.database.DatabaseConnection.Link; import club.wpia.gigi.database.GigiPreparedStatement; import club.wpia.gigi.database.GigiResultSet; -import club.wpia.gigi.database.DatabaseConnection.Link; import club.wpia.gigi.dbObjects.Domain; import club.wpia.gigi.dbObjects.DomainPingConfiguration; import club.wpia.gigi.dbObjects.DomainPingType; @@ -18,8 +18,6 @@ public class PingerDaemon extends Thread { HashMap pingers = new HashMap<>(); - private GigiPreparedStatement searchNeededPings; - private KeyStore truststore; private Queue toExecute = new LinkedList<>(); @@ -38,15 +36,7 @@ public class PingerDaemon extends Thread { } public void runWithConnection() { - searchNeededPings = new GigiPreparedStatement("SELECT `pc`.`id`" // - + " FROM `pingconfig` AS `pc`" // - + " INNER JOIN `domains` AS `d` ON `d`.`id` = `pc`.`domainid`" // - + " WHERE `d`.`deleted` IS NULL" // - + " AND `pc`.`deleted` IS NULL" // - + " AND NOT EXISTS (" // - + " SELECT 1 FROM `domainPinglog` AS `dpl`" // - + " WHERE `dpl`.`configId` = `pc`.`id`" // - + " AND `dpl`.`when` >= CURRENT_TIMESTAMP - interval '6 mons')"); + pingers.put(DomainPingType.EMAIL, new EmailPinger()); pingers.put(DomainPingType.SSL, new SSLPinger(truststore)); pingers.put(DomainPingType.HTTP, new HTTPFetch()); @@ -64,11 +54,21 @@ public class PingerDaemon extends Thread { } notifyAll(); } + try (GigiPreparedStatement searchNeededPings = new GigiPreparedStatement("SELECT `pc`.`id`" // + + " FROM `pingconfig` AS `pc`" // + + " INNER JOIN `domains` AS `d` ON `d`.`id` = `pc`.`domainid`" // + + " WHERE `d`.`deleted` IS NULL" // + + " AND `pc`.`deleted` IS NULL" // + + " AND NOT EXISTS (" // + + " SELECT 1 FROM `domainPinglog` AS `dpl`" // + + " WHERE `dpl`.`configId` = `pc`.`id`" // + + " AND `dpl`.`when` >= CURRENT_TIMESTAMP - interval '6 mons')")) { - GigiResultSet rs = searchNeededPings.executeQuery(); - while (rs.next()) { - worked = true; - handle(DomainPingConfiguration.getById(rs.getInt("id"))); + GigiResultSet rs = searchNeededPings.executeQuery(); + while (rs.next()) { + worked = true; + handle(DomainPingConfiguration.getById(rs.getInt("id"))); + } } try { if ( !worked) { -- 2.39.2