eac822bd5ae50465cbe4cb78845987797d1fa364
[gigi.git] / 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             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("INSERT INTO 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 }