X-Git-Url: https://code.wpia.club/?p=gigi.git;a=blobdiff_plain;f=src%2Forg%2Fcacert%2Fgigi%2Fdatabase%2FDatabaseConnection.java;h=bccae86faebb950f1ea82da64aca0e90db8d58b2;hp=eac822bd5ae50465cbe4cb78845987797d1fa364;hb=a0232b6e40e7e09767f0444d24e18bf12dafc362;hpb=226dd3a5e589ad8269585a0767819619166eebf4 diff --git a/src/org/cacert/gigi/database/DatabaseConnection.java b/src/org/cacert/gigi/database/DatabaseConnection.java index eac822bd..bccae86f 100644 --- a/src/org/cacert/gigi/database/DatabaseConnection.java +++ b/src/org/cacert/gigi/database/DatabaseConnection.java @@ -2,6 +2,7 @@ package org.cacert.gigi.database; import java.io.IOException; import java.io.InputStream; +import java.io.PrintWriter; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; @@ -9,19 +10,26 @@ import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.HashMap; +import java.util.HashSet; +import java.util.Map.Entry; import java.util.Properties; +import java.util.StringJoiner; +import java.util.regex.Matcher; +import java.util.regex.Pattern; import org.cacert.gigi.database.SQLFileManager.ImportType; public class DatabaseConnection { - public static final int CURRENT_SCHEMA_VERSION = 1; + public static final int CURRENT_SCHEMA_VERSION = 6; public static final int CONNECTION_TIMEOUT = 24 * 60 * 60; private Connection c; - private HashMap statements = new HashMap(); + private HashMap statements = new HashMap(); + + HashSet underUse = new HashSet<>(); private static Properties credentials; @@ -39,32 +47,51 @@ public class DatabaseConnection { private void tryConnect() { try { - c = DriverManager.getConnection(credentials.getProperty("sql.url") + "?zeroDateTimeBehavior=convertToNull", credentials.getProperty("sql.user"), credentials.getProperty("sql.password")); - PreparedStatement ps = c.prepareStatement("SET SESSION wait_timeout=?, time_zone='+0:00';"); - try { - ps.setInt(1, CONNECTION_TIMEOUT); - ps.execute(); - adHoc = c.createStatement(); - } finally { - ps.close(); - } + c = DriverManager.getConnection(credentials.getProperty("sql.url") + "?socketTimeout=" + CONNECTION_TIMEOUT, credentials.getProperty("sql.user"), credentials.getProperty("sql.password")); + adHoc = c.createStatement(); } catch (SQLException e) { e.printStackTrace(); } } - public GigiPreparedStatement prepare(String query) { + protected synchronized PreparedStatement prepareInternal(String query) throws SQLException { ensureOpen(); - GigiPreparedStatement statement = statements.get(query); - if (statement == null) { - try { - statement = new GigiPreparedStatement(c.prepareStatement(query, Statement.RETURN_GENERATED_KEYS)); - } catch (SQLException e) { - throw new Error(e); + query = preprocessQuery(query); + PreparedStatement statement = statements.get(query); + if (statement != null) { + if (underUse.add(statement)) { + return statement; + } else { + throw new Error("Statement in Use"); + } + } + statement = c.prepareStatement(query, query.startsWith("SELECT ") ? Statement.NO_GENERATED_KEYS : Statement.RETURN_GENERATED_KEYS); + statements.put(query, statement); + if (underUse.add(statement)) { + return statement; + } else { + throw new Error("Statement in Use"); + } + } + + protected synchronized PreparedStatement prepareInternalScrollable(String query) throws SQLException { + ensureOpen(); + query = preprocessQuery(query); + PreparedStatement statement = statements.get("__SCROLLABLE__! " + query); + if (statement != null) { + if (underUse.add(statement)) { + return statement; + } else { + throw new Error("Statement in Use"); } - statements.put(query, statement); } - return statement; + statement = c.prepareStatement(query, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); + statements.put("__SCROLLABLE__! " + query, statement); + if (underUse.add(statement)) { + return statement; + } else { + throw new Error("Statement in Use"); + } } private long lastAction = System.currentTimeMillis(); @@ -84,16 +111,17 @@ public class DatabaseConnection { lastAction = System.currentTimeMillis(); } - private static ThreadLocal instances = new ThreadLocal() { - - @Override - protected DatabaseConnection initialValue() { - return new DatabaseConnection(); - } - }; + private static DatabaseConnection instance; public static DatabaseConnection getInstance() { - return instances.get(); + if (instance == null) { + synchronized (DatabaseConnection.class) { + if (instance == null) { + instance = new DatabaseConnection(); + } + } + } + return instance; } public static boolean isInited() { @@ -105,10 +133,12 @@ public class DatabaseConnection { throw new Error("Re-initiaizing is forbidden."); } credentials = conf; - GigiResultSet rs = getInstance().prepare("SELECT version FROM schemeVersion ORDER BY version DESC LIMIT 1").executeQuery(); int version = 0; - if (rs.next()) { - version = rs.getInt(1); + 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 @@ -119,6 +149,10 @@ public class DatabaseConnection { upgrade(version); } + public void beginTransaction() throws SQLException { + c.setAutoCommit(false); + } + private static void upgrade(int version) { try { Statement s = getInstance().c.createStatement(); @@ -132,7 +166,7 @@ public class DatabaseConnection { } version++; } - s.addBatch("INSERT INTO schemeVersion SET version='" + version + "'"); + s.addBatch("UPDATE \"schemeVersion\" SET version='" + version + "'"); System.out.println("UPGRADING Database to version " + version); s.executeBatch(); System.out.println("done."); @@ -146,10 +180,6 @@ public class DatabaseConnection { } } - public void beginTransaction() throws SQLException { - c.setAutoCommit(false); - } - public void commitTransaction() throws SQLException { c.commit(); c.setAutoCommit(true); @@ -165,4 +195,55 @@ public class DatabaseConnection { e.printStackTrace(); } } + + public static final String preprocessQuery(String originalQuery) { + originalQuery = originalQuery.replace('`', '"'); + if (originalQuery.matches("^INSERT INTO [^ ]+ SET .*")) { + Pattern p = Pattern.compile("INSERT INTO ([^ ]+) SET (.*)"); + Matcher m = p.matcher(originalQuery); + if (m.matches()) { + String replacement = "INSERT INTO " + toIdentifier(m.group(1)); + String[] parts = m.group(2).split(","); + StringJoiner columns = new StringJoiner(", "); + StringJoiner values = new StringJoiner(", "); + for (int i = 0; i < parts.length; i++) { + String[] split = parts[i].split("=", 2); + columns.add(toIdentifier(split[0])); + values.add(split[1]); + } + replacement += "(" + columns.toString() + ") VALUES(" + values.toString() + ")"; + return replacement; + } + } + + // + return originalQuery; + } + + private static CharSequence toIdentifier(String ident) { + ident = ident.trim(); + if ( !ident.startsWith("\"")) { + ident = "\"" + ident; + } + if ( !ident.endsWith("\"")) { + ident = ident + "\""; + } + return ident; + } + + protected synchronized void returnStatement(PreparedStatement target) { + underUse.remove(target); + } + + public void lockedStatements(PrintWriter writer) { + writer.println(underUse.size()); + for (PreparedStatement ps : underUse) { + for (Entry e : statements.entrySet()) { + if (e.getValue() == ps) { + writer.println("
"); + writer.println(e.getKey()); + } + } + } + } }