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 = 4;
  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             try {
  45                 ps.setInt(1, CONNECTION_TIMEOUT);
  46                 ps.execute();
  47                 adHoc = c.createStatement();
  48             } finally {
  49                 ps.close();
  50             }
  51         } catch (SQLException e) {
  52             e.printStackTrace();
  53         }
  54     }
  55
  56     public GigiPreparedStatement prepare(String query) {
  57         ensureOpen();
  58         GigiPreparedStatement statement = statements.get(query);
  59         if (statement == null) {
  60             try {
  61                 statement = new GigiPreparedStatement(c.prepareStatement(query, Statement.RETURN_GENERATED_KEYS));
  62             } catch (SQLException e) {
  63                 throw new Error(e);
  64             }
  65             statements.put(query, statement);
  66         }
  67         return statement;
  68     }
  69
  70     private long lastAction = System.currentTimeMillis();
  71
  72     private void ensureOpen() {
  73         if (System.currentTimeMillis() - lastAction > CONNECTION_TIMEOUT * 1000L) {
  74             try {
  75                 ResultSet rs = adHoc.executeQuery("SELECT 1");
  76                 rs.close();
  77                 lastAction = System.currentTimeMillis();
  78                 return;
  79             } catch (SQLException e) {
  80             }
  81             statements.clear();
  82             tryConnect();
  83         }
  84         lastAction = System.currentTimeMillis();
  85     }
  86
  87     private static ThreadLocal<DatabaseConnection> instances = new ThreadLocal<DatabaseConnection>() {
  88
  89         @Override
  90         protected DatabaseConnection initialValue() {
  91             return new DatabaseConnection();
  92         }
  93     };
  94
  95     public static DatabaseConnection getInstance() {
  96         return instances.get();
  97     }
  98
  99     public static boolean isInited() {
 100         return credentials != null;
 101     }
 102
 103     public static void init(Properties conf) {
 104         if (credentials != null) {
 105             throw new Error("Re-initiaizing is forbidden.");
 106         }
 107         credentials = conf;
 108         GigiResultSet rs = getInstance().prepare("SELECT version FROM schemeVersion ORDER BY version DESC LIMIT 1").executeQuery();
 109         int version = 0;
 110         if (rs.next()) {
 111             version = rs.getInt(1);
 112         }
 113         if (version == CURRENT_SCHEMA_VERSION) {
 114             return; // Good to go
 115         }
 116         if (version > CURRENT_SCHEMA_VERSION) {
 117             throw new Error("Invalid database version. Please fix this.");
 118         }
 119         upgrade(version);
 120     }
 121
 122     private static void upgrade(int version) {
 123         try {
 124             Statement s = getInstance().c.createStatement();
 125             try {
 126                 while (version < CURRENT_SCHEMA_VERSION) {
 127                     try (InputStream resourceAsStream = DatabaseConnection.class.getResourceAsStream("upgrade/from_" + version + ".sql")) {
 128                         if (resourceAsStream == null) {
 129                             throw new Error("Upgrade script from version " + version + " was not found.");
 130                         }
 131                         SQLFileManager.addFile(s, resourceAsStream, ImportType.PRODUCTION);
 132                     }
 133                     version++;
 134                 }
 135                 s.addBatch("UPDATE schemeVersion SET version='" + version + "'");
 136                 System.out.println("UPGRADING Database to version " + version);
 137                 s.executeBatch();
 138                 System.out.println("done.");
 139             } finally {
 140                 s.close();
 141             }
 142         } catch (SQLException e) {
 143             e.printStackTrace();
 144         } catch (IOException e) {
 145             e.printStackTrace();
 146         }
 147     }
 148
 149     public void beginTransaction() throws SQLException {
 150         c.setAutoCommit(false);
 151     }
 152
 153     public void commitTransaction() throws SQLException {
 154         c.commit();
 155         c.setAutoCommit(true);
 156     }
 157
 158     public void quitTransaction() {
 159         try {
 160             if ( !c.getAutoCommit()) {
 161                 c.rollback();
 162                 c.setAutoCommit(true);
 163             }
 164         } catch (SQLException e) {
 165             e.printStackTrace();
 166         }
 167     }
 168 }