4 import org.ibex.mail.protocol.*;
5 import org.ibex.util.*;
11 import java.sql.Timestamp;
12 import java.sql.Connection;
14 public class SqliteDB {
16 protected Connection conn;
17 private String filename;
19 private HashMap<String,SqliteTable> tables = new HashMap<String,SqliteTable>();
21 public static final int REAPER_INTERVAL_SECONDS = 60 * 60;
23 public Connection getConnection() { return conn; }
25 public synchronized SqliteTable getTable(String name, String schema) throws SQLException {
26 SqliteTable ret = tables.get(name);
27 if (ret==null) ret = new SqliteTable(name, schema);
31 // check upstream: PRAGMA encoding = "UTF-8";
33 // periodically run "analyze"?
35 public void setCacheSize(int kilobytes) throws SQLException {
36 conn.prepareStatement("PRAGMA cache_size="+Math.ceil(kilobytes/1.5)+";").executeUpdate();
39 public SqliteDB(String filename) {
40 this.filename = filename;
42 Class.forName("org.sqlite.JDBC");
43 conn = DriverManager.getConnection("jdbc:sqlite:"+filename);
44 conn.prepareStatement("PRAGMA auto_vacuum = 1").executeUpdate();
45 conn.prepareStatement("VACUUM").executeUpdate();
46 // until we have better assurances about locking on network filesystems...
47 conn.prepareStatement("PRAGMA locking_mode = EXCLUSIVE").executeUpdate();
48 conn.prepareStatement("PRAGMA temp_store = MEMORY").executeUpdate();
49 conn.prepareStatement("PRAGMA page_size=4096").executeUpdate();
50 conn.prepareStatement("PRAGMA cache_size=2000").executeUpdate();
51 ResultSet rs = conn.prepareStatement("PRAGMA integrity_check").executeQuery();
53 String result = rs.getString(1);
54 if (!result.equals("ok"))
55 throw new RuntimeException("PRAGMA integrity_check returned \""+result+"\"");
57 catch (SQLException e) { throw new RuntimeException(e); }
58 catch (ClassNotFoundException e) { throw new RuntimeException(e); }
61 public void setFastButDangerous(boolean on) throws SQLException {
62 conn.prepareStatement("PRAGMA synchronous = "+(on?"OFF":"ON")).executeUpdate();
66 public void dump(OutputStream os) {
70 public class SqliteTable {
71 public final String name;
72 private String reapColumn = null;
73 private SqliteTable(String name, String schema) throws SQLException {
75 PreparedStatement ps = conn.prepareStatement("create table if not exists ?");
76 ps.setString(1, name+" "+schema);
78 tables.put(name, this);
80 public void createIndex(String name, String column) throws SQLException {
81 PreparedStatement ps = conn.prepareStatement("create index if not exists ? on ?");
82 ps.setString(1, name);
83 ps.setString(2, column);
86 protected void reap(String reapColumn) {
87 if (this.reapColumn != null) throw new RuntimeException("reapColumn already set");
88 this.reapColumn = reapColumn;
89 Main.cron.executeLater(1000 * REAPER_INTERVAL_SECONDS, new Reaper(name, reapColumn));
93 // FIXME: desynchronized access to the conn?
94 private class Reaper implements Runnable {
95 public Reaper(String reapTable, String reapColumn) {
96 this.reapTable = reapTable;
97 this.reapColumn = reapColumn;
99 private String reapTable;
100 private String reapColumn;
103 Log.warn(Reaper.class, filename + " reaping...");
104 long when = System.currentTimeMillis();
105 when -= 5 * 24 * 60 * 60 * 1000;
106 synchronized(SqliteDB.this) {
107 PreparedStatement ps =
108 conn.prepareStatement("select count(*) from "+reapTable+" where "+reapColumn+"<?");
109 ps.setTimestamp(1, new Timestamp(when));
110 ResultSet rs = ps.executeQuery();
112 Log.warn(Reaper.class, filename + " reaping " + rs.getInt(1) + " entries");
113 Log.warn(Reaper.class, filename + ": " + "delete from "+reapTable+" where "+reapColumn+"<"+when);
114 ps = conn.prepareStatement("delete from "+reapTable+" where "+reapColumn+"<?");
115 ps.setTimestamp(1, new Timestamp(when));
116 int rows = ps.executeUpdate();
117 Log.warn(Reaper.class, filename + " done reaping; removed " + rows + " rows");
119 } catch (Exception e) { Log.error(Reaper.class, e); }
120 Main.cron.executeLater(1000 * REAPER_INTERVAL_SECONDS, this);