upd: cleanup SQL statements

Change-Id: If22b05270d1f097068e38f6ed860d52740926021

diff --git a/src/club/wpia/gigi/database/GigiPreparedStatement.java b/src/club/wpia/gigi/database/GigiPreparedStatement.java
index 4957d4d3edbb8221b53fa3ad369dab99b1698759..e8c0d832d1877166b30a45b39beeb5b2defa5b19 100644 (file)
--- 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 72cd3c19d57ad77c7adf8efc7f2e3adfc54c033c..ffc5154766f19ef7039be8346b68f5fd2516dfb0 100644 (file)
--- 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 1cd48cf17f6158a42428c4f80c0d47973a91b88f..51cc985838b23cd318274e4dcdddbbefcf13ca86 100644 (file)
--- 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<DomainPingType, DomainPinger> pingers = new HashMap<>();
 
-    private GigiPreparedStatement searchNeededPings;
-
     private KeyStore truststore;
 
     private Queue<DomainPingConfiguration> 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) {