src/org/cacert/gigi/database/DatabaseConnection.java

   1 package org.cacert.gigi.database;
   2
   3 import java.io.IOException;
   4 import java.io.InputStream;
   5 import java.sql.Connection;
   6 import java.sql.DriverManager;
   7 import java.sql.PreparedStatement;
   8 import java.sql.ResultSet;
   9 import java.sql.SQLException;
  10 import java.sql.Statement;
  11 import java.util.HashMap;
  12 import java.util.Properties;
  13
  14 import org.cacert.gigi.database.SQLFileManager.ImportType;
  15
  16 public class DatabaseConnection {
  17
  18     public static final int CURRENT_SCHEMA_VERSION = 1;
  19
  20     public static final int CONNECTION_TIMEOUT = 24 * 60 * 60;
  21
  22     private Connection c;
  23
  24     private HashMap<String, GigiPreparedStatement> statements = new HashMap<String, GigiPreparedStatement>();
  25
  26     private static Properties credentials;
  27
  28     private Statement adHoc;
  29
  30     public DatabaseConnection() {
  31         try {
  32             Class.forName(credentials.getProperty("sql.driver"));
  33         } catch (ClassNotFoundException e) {
  34             e.printStackTrace();
  35         }
  36         tryConnect();
  37
  38     }
  39
  40     private void tryConnect() {
  41         try {
  42             c = DriverManager.getConnection(credentials.getProperty("sql.url") + "?zeroDateTimeBehavior=convertToNull", credentials.getProperty("sql.user"), credentials.getProperty("sql.password"));
  43             PreparedStatement ps = c.prepareStatement("SET SESSION wait_timeout=?, time_zone='+0:00';");
  44             ps.setInt(1, CONNECTION_TIMEOUT);
  45             ps.execute();
  46             ps.close();
  47             adHoc = c.createStatement();
  48         } catch (SQLException e) {
  49             e.printStackTrace();
  50         }
  51     }
  52
  53     public GigiPreparedStatement prepare(String query) {
  54         ensureOpen();
  55         GigiPreparedStatement statement = statements.get(query);
  56         if (statement == null) {
  57             try {
  58                 statement = new GigiPreparedStatement(c.prepareStatement(query, Statement.RETURN_GENERATED_KEYS));
  59             } catch (SQLException e) {
  60                 throw new Error(e);
  61             }
  62             statements.put(query, statement);
  63         }
  64         return statement;
  65     }
  66
  67     private long lastAction = System.currentTimeMillis();
  68
  69     private void ensureOpen() {
  70         if (System.currentTimeMillis() - lastAction > CONNECTION_TIMEOUT * 1000L) {
  71             try {
  72                 ResultSet rs = adHoc.executeQuery("SELECT 1");
  73                 rs.close();
  74                 lastAction = System.currentTimeMillis();
  75                 return;
  76             } catch (SQLException e) {
  77             }
  78             statements.clear();
  79             tryConnect();
  80         }
  81         lastAction = System.currentTimeMillis();
  82     }
  83
  84     private static ThreadLocal<DatabaseConnection> instances = new ThreadLocal<DatabaseConnection>() {
  85
  86         @Override
  87         protected DatabaseConnection initialValue() {
  88             return new DatabaseConnection();
  89         }
  90     };
  91
  92     public static DatabaseConnection getInstance() {
  93         return instances.get();
  94     }
  95
  96     public static boolean isInited() {
  97         return credentials != null;
  98     }
  99
 100     public static void init(Properties conf) {
 101         if (credentials != null) {
 102             throw new Error("Re-initiaizing is forbidden.");
 103         }
 104         credentials = conf;
 105         GigiResultSet rs = getInstance().prepare("SELECT version FROM schemeVersion ORDER BY version DESC LIMIT 1").executeQuery();
 106         int version = 0;
 107         if (rs.next()) {
 108             version = rs.getInt(1);
 109         }
 110         if (version == CURRENT_SCHEMA_VERSION) {
 111             return; // Good to go
 112         }
 113         if (version > CURRENT_SCHEMA_VERSION) {
 114             throw new Error("Invalid database version. Please fix this.");
 115         }
 116         upgrade(version);
 117     }
 118
 119     private static void upgrade(int version) {
 120         try {
 121             Statement s = getInstance().c.createStatement();
 122             while (version < CURRENT_SCHEMA_VERSION) {
 123                 try (InputStream resourceAsStream = DatabaseConnection.class.getResourceAsStream("upgrade/from_" + version + ".sql")) {
 124                     if (resourceAsStream == null) {
 125                         throw new Error("Upgrade script from version " + version + " was not found.");
 126                     }
 127                     SQLFileManager.addFile(s, resourceAsStream, ImportType.PRODUCTION);
 128                 }
 129                 version++;
 130             }
 131             s.addBatch("INSERT INTO schemeVersion SET version='" + version + "'");
 132             System.out.println("UPGRADING Database to version " + version);
 133             s.executeBatch();
 134             System.out.println("done.");
 135             s.close();
 136         } catch (SQLException e) {
 137             e.printStackTrace();
 138         } catch (IOException e) {
 139             e.printStackTrace();
 140         }
 141     }
 142
 143     public void beginTransaction() throws SQLException {
 144         c.setAutoCommit(false);
 145     }
 146
 147     public void commitTransaction() throws SQLException {
 148         c.commit();
 149         c.setAutoCommit(true);
 150     }
 151
 152     public void quitTransaction() {
 153         try {
 154             if ( !c.getAutoCommit()) {
 155                 c.rollback();
 156                 c.setAutoCommit(true);
 157             }
 158         } catch (SQLException e) {
 159             e.printStackTrace();
 160         }
 161     }
 162 }