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();
47 // until we have better assurances about locking on network filesystems...
48 conn.prepareStatement("PRAGMA locking_mode = EXCLUSIVE").executeQuery();
50 conn.prepareStatement("PRAGMA temp_store = MEMORY").executeUpdate();
51 conn.prepareStatement("PRAGMA page_size=4096").executeUpdate();
52 conn.prepareStatement("PRAGMA cache_size=2000").executeUpdate();
53 ResultSet rs = conn.prepareStatement("PRAGMA integrity_check").executeQuery();
55 String result = rs.getString(1);
56 if (!result.equals("ok"))
57 throw new RuntimeException("PRAGMA integrity_check returned \""+result+"\"");
59 catch (SQLException e) { throw new RuntimeException(e); }
60 catch (ClassNotFoundException e) { throw new RuntimeException(e); }
63 public void setFastButDangerous(boolean on) throws SQLException {
64 conn.prepareStatement("PRAGMA synchronous = "+(on?"OFF":"ON")).executeUpdate();
68 public void dump(OutputStream os) {
72 public class SqliteTable {
73 public final String name;
74 private String reapColumn = null;
75 private SqliteTable(String name, String schema) throws SQLException {
77 PreparedStatement ps = conn.prepareStatement("create table if not exists " + name + " " + schema);
79 tables.put(name, this);
81 public void createIndex(String name, String column) throws SQLException {
82 PreparedStatement ps = conn.prepareStatement("create index if not exists "+name+" on "+column);
85 protected void reap(String reapColumn) {
86 if (this.reapColumn != null) throw new RuntimeException("reapColumn already set");
87 this.reapColumn = reapColumn;
88 Main.cron.executeLater(1000 * REAPER_INTERVAL_SECONDS, new Reaper(name, reapColumn));
92 // FIXME: desynchronized access to the conn?
93 private class Reaper implements Runnable {
94 public Reaper(String reapTable, String reapColumn) {
95 this.reapTable = reapTable;
96 this.reapColumn = reapColumn;
98 private String reapTable;
99 private String reapColumn;
102 Log.warn(Reaper.class, filename + " reaping...");
103 long when = System.currentTimeMillis();
104 when -= 5 * 24 * 60 * 60 * 1000;
105 synchronized(SqliteDB.this) {
106 PreparedStatement ps =
107 conn.prepareStatement("select count(*) from "+reapTable+" where "+reapColumn+"<?");
108 ps.setTimestamp(1, new Timestamp(when));
109 ResultSet rs = ps.executeQuery();
111 Log.warn(Reaper.class, filename + " reaping " + rs.getInt(1) + " entries");
112 Log.warn(Reaper.class, filename + ": " + "delete from "+reapTable+" where "+reapColumn+"<"+when);
113 ps = conn.prepareStatement("delete from "+reapTable+" where "+reapColumn+"<?");
114 ps.setTimestamp(1, new Timestamp(when));
115 int rows = ps.executeUpdate();
116 Log.warn(Reaper.class, filename + " done reaping; removed " + rows + " rows");
118 } catch (Exception e) { Log.error(Reaper.class, e); }
119 Main.cron.executeLater(1000 * REAPER_INTERVAL_SECONDS, this);