From: Felix Dörre Date: Sun, 19 Jun 2016 17:10:13 +0000 (+0200) Subject: Merge "Suggestions to enhance the SQL call pattern." X-Git-Url: https://code.wpia.club/?p=gigi.git;a=commitdiff_plain;h=d7be034f96e06985f57d86d2779c434276b5bd4d;hp=78aea4e2c6a8e99ba546c4189d7071d57c1aaf3b Merge "Suggestions to enhance the SQL call pattern." --- diff --git a/src/org/cacert/gigi/Gigi.java b/src/org/cacert/gigi/Gigi.java index 5fda72d0..bf6c32ed 100644 --- a/src/org/cacert/gigi/Gigi.java +++ b/src/org/cacert/gigi/Gigi.java @@ -21,6 +21,7 @@ import javax.servlet.http.HttpServletResponse; import javax.servlet.http.HttpSession; import org.cacert.gigi.database.DatabaseConnection; +import org.cacert.gigi.database.DatabaseConnection.Link; import org.cacert.gigi.dbObjects.CACertificate; import org.cacert.gigi.dbObjects.CertificateProfile; import org.cacert.gigi.dbObjects.DomainPingConfiguration; @@ -245,8 +246,12 @@ public final class Gigi extends HttpServlet { return; } // ensure those static initializers are finished - CACertificate.getById(1); - CertificateProfile.getById(1); + try (Link l = DatabaseConnection.newLink(false)) { + CACertificate.getById(1); + CertificateProfile.getById(1); + } catch (InterruptedException e) { + throw new Error(e); + } MenuBuilder mb = new MenuBuilder(); rootMenu = mb.generateMenu(); @@ -301,6 +306,20 @@ public final class Gigi extends HttpServlet { @Override protected void service(final HttpServletRequest req, final HttpServletResponse resp) throws ServletException, IOException { + if ("/error".equals(req.getPathInfo()) || "/denied".equals(req.getPathInfo())) { + if (DatabaseConnection.hasInstance()) { + serviceWithConnection(req, resp); + return; + } + } + try (DatabaseConnection.Link l = DatabaseConnection.newLink( !req.getMethod().equals("POST"))) { + serviceWithConnection(req, resp); + } catch (InterruptedException e) { + e.printStackTrace(); + } + } + + protected void serviceWithConnection(final HttpServletRequest req, final HttpServletResponse resp) throws ServletException, IOException { boolean isSecure = req.isSecure(); addXSSHeaders(resp, isSecure); // Firefox only sends this, if it's a cross domain access; safari sends diff --git a/src/org/cacert/gigi/api/GigiAPI.java b/src/org/cacert/gigi/api/GigiAPI.java index ce2704de..dbac5a88 100644 --- a/src/org/cacert/gigi/api/GigiAPI.java +++ b/src/org/cacert/gigi/api/GigiAPI.java @@ -10,6 +10,9 @@ import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; +import org.cacert.gigi.database.DatabaseConnection; +import org.cacert.gigi.database.DatabaseConnection.Link; + public class GigiAPI extends HttpServlet { private static final long serialVersionUID = 659963677032635817L; @@ -43,8 +46,12 @@ public class GigiAPI extends HttpServlet { } APIPoint p = api.get(pi); - if (p != null) { - p.process(req, resp); + try (Link l = DatabaseConnection.newLink(false)) { + if (p != null) { + p.process(req, resp); + } + } catch (InterruptedException e) { + throw new Error(e); } } } diff --git a/src/org/cacert/gigi/database/DatabaseConnection.java b/src/org/cacert/gigi/database/DatabaseConnection.java index 09093669..a855d706 100644 --- a/src/org/cacert/gigi/database/DatabaseConnection.java +++ b/src/org/cacert/gigi/database/DatabaseConnection.java @@ -14,6 +14,7 @@ import java.util.HashSet; import java.util.Map.Entry; import java.util.Properties; import java.util.StringJoiner; +import java.util.concurrent.LinkedBlockingDeque; import java.util.regex.Matcher; import java.util.regex.Pattern; @@ -21,6 +22,28 @@ import org.cacert.gigi.database.SQLFileManager.ImportType; public class DatabaseConnection { + public static class Link implements AutoCloseable { + + private DatabaseConnection target; + + protected Link(DatabaseConnection target) { + this.target = target; + } + + @Override + public void close() { + synchronized (DatabaseConnection.class) { + Link i = instances.get(Thread.currentThread()); + if (i != this) { + throw new Error(); + } + instances.remove(Thread.currentThread()); + pool.add(target); + } + } + + } + public static final int MAX_CACHED_INSTANCES = 3; private static class StatementDescriptor { @@ -107,7 +130,7 @@ public class DatabaseConnection { private HashMap statements = new HashMap(); - HashSet underUse = new HashSet<>(); + private HashSet underUse = new HashSet<>(); private static Properties credentials; @@ -183,13 +206,23 @@ public class DatabaseConnection { lastAction = System.currentTimeMillis(); } - private static volatile DatabaseConnection instance; + private static HashMap instances = new HashMap<>(); + + private static LinkedBlockingDeque pool = new LinkedBlockingDeque<>(); + + private static int connCount = 0; public static synchronized DatabaseConnection getInstance() { - if (instance == null) { - instance = new DatabaseConnection(); + Link l = instances.get(Thread.currentThread()); + if (l == null) { + throw new Error("No database connection allocated"); } - return instance; + return l.target; + } + + public static synchronized boolean hasInstance() { + Link l = instances.get(Thread.currentThread()); + return l != null; } public static boolean isInited() { @@ -201,20 +234,24 @@ public class DatabaseConnection { throw new Error("Re-initiaizing is forbidden."); } credentials = conf; - int version = 0; - try (GigiPreparedStatement gigiPreparedStatement = new GigiPreparedStatement("SELECT version FROM \"schemeVersion\" ORDER BY version DESC LIMIT 1;")) { - GigiResultSet rs = gigiPreparedStatement.executeQuery(); - if (rs.next()) { - version = rs.getInt(1); + try (Link i = newLink(false)) { + int version = 0; + try (GigiPreparedStatement gigiPreparedStatement = new GigiPreparedStatement("SELECT version FROM \"schemeVersion\" ORDER BY version DESC LIMIT 1;")) { + GigiResultSet rs = gigiPreparedStatement.executeQuery(); + if (rs.next()) { + version = rs.getInt(1); + } } + if (version == CURRENT_SCHEMA_VERSION) { + return; // Good to go + } + if (version > CURRENT_SCHEMA_VERSION) { + throw new Error("Invalid database version. Please fix this."); + } + upgrade(version); + } catch (InterruptedException e) { + throw new Error(e); } - if (version == CURRENT_SCHEMA_VERSION) { - return; // Good to go - } - if (version > CURRENT_SCHEMA_VERSION) { - throw new Error("Invalid database version. Please fix this."); - } - upgrade(version); } private static void upgrade(int version) { @@ -306,4 +343,23 @@ public class DatabaseConnection { } } } + + public static synchronized Link newLink(boolean readOnly) throws InterruptedException { + if (instances.get(Thread.currentThread()) != null) { + throw new Error("There is already a connection allocated for this thread."); + } + if (pool.isEmpty() && connCount < 5) { + pool.addLast(new DatabaseConnection()); + connCount++; + } + DatabaseConnection conn = pool.takeFirst(); + try { + conn.c.setReadOnly(readOnly); + } catch (SQLException e) { + throw new Error(e); + } + Link l = new Link(conn); + instances.put(Thread.currentThread(), l); + return l; + } } diff --git a/src/org/cacert/gigi/ping/PingerDaemon.java b/src/org/cacert/gigi/ping/PingerDaemon.java index bc67e666..397ad58b 100644 --- a/src/org/cacert/gigi/ping/PingerDaemon.java +++ b/src/org/cacert/gigi/ping/PingerDaemon.java @@ -5,6 +5,8 @@ import java.util.HashMap; import java.util.LinkedList; import java.util.Queue; +import org.cacert.gigi.database.DatabaseConnection; +import org.cacert.gigi.database.DatabaseConnection.Link; import org.cacert.gigi.database.GigiPreparedStatement; import org.cacert.gigi.database.GigiResultSet; import org.cacert.gigi.dbObjects.Domain; @@ -28,6 +30,14 @@ public class PingerDaemon extends Thread { @Override public void run() { + try (Link l = DatabaseConnection.newLink(false)) { + runWithConnection(); + } catch (InterruptedException e) { + e.printStackTrace(); + } + } + + public void runWithConnection() { searchNeededPings = new GigiPreparedStatement("SELECT `pingconfig`.`id` FROM `pingconfig` LEFT JOIN `domainPinglog` ON `domainPinglog`.`configId` = `pingconfig`.`id` INNER JOIN `domains` ON `domains`.`id` = `pingconfig`.`domainid` WHERE ( `domainPinglog`.`configId` IS NULL OR `domainPinglog`.`when` < CURRENT_TIMESTAMP - interval '6 mons') AND `domains`.`deleted` IS NULL AND `pingconfig`.`deleted` IS NULL GROUP BY `pingconfig`.`id`"); pingers.put(DomainPingType.EMAIL, new EmailPinger()); pingers.put(DomainPingType.SSL, new SSLPinger(truststore)); diff --git a/tests/org/cacert/gigi/ping/TestSSL.java b/tests/org/cacert/gigi/ping/TestSSL.java index 62e652aa..ab41ca51 100644 --- a/tests/org/cacert/gigi/ping/TestSSL.java +++ b/tests/org/cacert/gigi/ping/TestSSL.java @@ -36,6 +36,8 @@ import javax.net.ssl.X509TrustManager; import javax.security.auth.x500.X500Principal; import org.cacert.gigi.GigiApiException; +import org.cacert.gigi.database.DatabaseConnection; +import org.cacert.gigi.database.DatabaseConnection.Link; import org.cacert.gigi.dbObjects.Certificate; import org.cacert.gigi.dbObjects.Certificate.CSRType; import org.cacert.gigi.dbObjects.CertificateProfile; @@ -131,6 +133,12 @@ public class TestSSL extends PingTest { testEmailAndSSL(1, 1, false); } + private void testEmailAndSSL(int sslVariant, int emailVariant, boolean successMail) throws IOException, InterruptedException, SQLException, GeneralSecurityException, GigiApiException { + try (Link link = DatabaseConnection.newLink(false)) { + testEmailAndSSLWithLink(sslVariant, emailVariant, successMail); + } + } + /** * @param sslVariant *
    @@ -149,7 +157,7 @@ public class TestSSL extends PingTest { * @throws GigiApiException */ - private void testEmailAndSSL(int sslVariant, int emailVariant, boolean successMail) throws IOException, InterruptedException, SQLException, GeneralSecurityException, GigiApiException { + private void testEmailAndSSLWithLink(int sslVariant, int emailVariant, boolean successMail) throws IOException, InterruptedException, SQLException, GeneralSecurityException, GigiApiException { String test = getTestProps().getProperty("domain.local"); assumeNotNull(test); Matcher m = initailizeDomainForm(); diff --git a/tests/org/cacert/gigi/testUtils/ConfiguredTest.java b/tests/org/cacert/gigi/testUtils/ConfiguredTest.java index 72461038..90b34682 100644 --- a/tests/org/cacert/gigi/testUtils/ConfiguredTest.java +++ b/tests/org/cacert/gigi/testUtils/ConfiguredTest.java @@ -16,6 +16,7 @@ import java.util.regex.Matcher; import java.util.regex.Pattern; import org.cacert.gigi.database.DatabaseConnection; +import org.cacert.gigi.database.DatabaseConnection.Link; import org.cacert.gigi.util.PEM; import org.junit.BeforeClass; @@ -49,6 +50,11 @@ public abstract class ConfiguredTest { } if ( !DatabaseConnection.isInited()) { DatabaseConnection.init(testProps); + try { + l = DatabaseConnection.newLink(false); + } catch (InterruptedException e) { + throw new Error(e); + } } } @@ -95,6 +101,8 @@ public abstract class ConfiguredTest { static int count = 0; + private static Link l; + public static String createUniqueName() { return "test" + System.currentTimeMillis() + "a" + (count++) + "u"; } diff --git a/util-testing/org/cacert/gigi/util/SimpleSigner.java b/util-testing/org/cacert/gigi/util/SimpleSigner.java index d23b78bc..296eb99e 100644 --- a/util-testing/org/cacert/gigi/util/SimpleSigner.java +++ b/util-testing/org/cacert/gigi/util/SimpleSigner.java @@ -40,6 +40,7 @@ import javax.security.auth.x500.X500Principal; import org.cacert.gigi.crypto.SPKAC; import org.cacert.gigi.database.DatabaseConnection; +import org.cacert.gigi.database.DatabaseConnection.Link; import org.cacert.gigi.database.GigiPreparedStatement; import org.cacert.gigi.database.GigiResultSet; import org.cacert.gigi.dbObjects.Certificate.CSRType; @@ -116,30 +117,35 @@ public class SimpleSigner { throw new IllegalStateException("already running"); } running = true; - readyCerts = new GigiPreparedStatement("SELECT certs.id AS id, certs.csr_name, jobs.id AS jobid, csr_type, md, `executeFrom`, `executeTo`, profile FROM jobs " + // - "INNER JOIN certs ON certs.id=jobs.`targetId` " + // - "INNER JOIN profiles ON profiles.id=certs.profile " + // - "WHERE jobs.state='open' "// - + "AND task='sign'"); - getSANSs = new GigiPreparedStatement("SELECT contents, type FROM `subjectAlternativeNames` " + // - "WHERE `certId`=?"); + runner = new Thread() { - updateMail = new GigiPreparedStatement("UPDATE certs SET crt_name=?," + " created=NOW(), serial=?, caid=? WHERE id=?"); - warnMail = new GigiPreparedStatement("UPDATE jobs SET warning=warning+1, state=IF(warning<3, 'open','error') WHERE id=?"); + @Override + public void run() { + try (Link l = DatabaseConnection.newLink(false)) { + readyCerts = new GigiPreparedStatement("SELECT certs.id AS id, certs.csr_name, jobs.id AS jobid, csr_type, md, `executeFrom`, `executeTo`, profile FROM jobs " + // + "INNER JOIN certs ON certs.id=jobs.`targetId` " + // + "INNER JOIN profiles ON profiles.id=certs.profile " + // + "WHERE jobs.state='open' "// + + "AND task='sign'"); - revoke = new GigiPreparedStatement("SELECT certs.id, certs.csr_name,jobs.id FROM jobs INNER JOIN certs ON jobs.`targetId`=certs.id" + " WHERE jobs.state='open' AND task='revoke'"); - revokeCompleted = new GigiPreparedStatement("UPDATE certs SET revoked=NOW() WHERE id=?"); + getSANSs = new GigiPreparedStatement("SELECT contents, type FROM `subjectAlternativeNames` " + // + "WHERE `certId`=?"); - finishJob = new GigiPreparedStatement("UPDATE jobs SET state='done' WHERE id=?"); + updateMail = new GigiPreparedStatement("UPDATE certs SET crt_name=?," + " created=NOW(), serial=?, caid=? WHERE id=?"); + warnMail = new GigiPreparedStatement("UPDATE jobs SET warning=warning+1, state=IF(warning<3, 'open','error') WHERE id=?"); - locateCA = new GigiPreparedStatement("SELECT id FROM cacerts WHERE keyname=?"); + revoke = new GigiPreparedStatement("SELECT certs.id, certs.csr_name,jobs.id FROM jobs INNER JOIN certs ON jobs.`targetId`=certs.id" + " WHERE jobs.state='open' AND task='revoke'"); + revokeCompleted = new GigiPreparedStatement("UPDATE certs SET revoked=NOW() WHERE id=?"); - runner = new Thread() { + finishJob = new GigiPreparedStatement("UPDATE jobs SET state='done' WHERE id=?"); - @Override - public void run() { - work(); + locateCA = new GigiPreparedStatement("SELECT id FROM cacerts WHERE keyname=?"); + + work(); + } catch (InterruptedException e) { + throw new Error(e); + } } };