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) {
26 SqliteTable ret = tables.get(name);
27 if (ret==null) ret = new SqliteTable(name);
31 // check upstream: PRAGMA encoding = "UTF-8";
33 // PRAGMA auto_vacuum=1 (can only be set before any tables are created)
34 // periodic "PRAGMA integrity_check; "?
36 public void setCacheSize(int kilobytes) throws SQLException {
37 conn.prepareStatement("PRAGMA cache_size="+Math.ceil(kilobytes/1.5)+";").executeUpdate();
40 public SqliteDB(String filename, String[] tables) {
41 this(filename, tables, false);
43 public SqliteDB(String filename, String[] tables, boolean fastButDangerous) {
44 this.filename = filename;
46 Class.forName("org.sqlite.JDBC");
47 conn = DriverManager.getConnection("jdbc:sqlite:"+filename);
48 for(String s : tables)
49 conn.prepareStatement(s).executeUpdate();
50 conn.prepareStatement("PRAGMA temp_store = MEMORY").executeUpdate();
51 conn.prepareStatement("PRAGMA page_size=4096").executeUpdate();
52 conn.prepareStatement("PRAGMA cache_size=2000").executeUpdate();
54 conn.prepareStatement("PRAGMA synchronous = OFF").executeUpdate();
56 catch (SQLException e) { throw new RuntimeException(e); }
57 catch (ClassNotFoundException e) { throw new RuntimeException(e); }
60 public class SqliteTable {
61 public final String name;
62 private String reapColumn = null;
63 private SqliteTable(String name) {
65 tables.put(name, this);
67 protected void reap(String reapColumn) {
68 if (this.reapColumn != null) throw new RuntimeException("reapColumn already set");
69 this.reapColumn = reapColumn;
70 Main.cron.executeLater(1000 * REAPER_INTERVAL_SECONDS, new Reaper(name, reapColumn));
74 // FIXME: desynchronized access to the conn?
75 private class Reaper implements Runnable {
76 public Reaper(String reapTable, String reapColumn) {
77 this.reapTable = reapTable;
78 this.reapColumn = reapColumn;
80 private String reapTable;
81 private String reapColumn;
84 Log.warn(Reaper.class, filename + " reaping...");
85 long when = System.currentTimeMillis();
86 when -= 5 * 24 * 60 * 60 * 1000;
87 synchronized(SqliteDB.this) {
88 PreparedStatement ps =
89 conn.prepareStatement("select count(*) from "+reapTable+" where "+reapColumn+"<?");
90 ps.setTimestamp(1, new Timestamp(when));
91 ResultSet rs = ps.executeQuery();
93 Log.warn(Reaper.class, filename + " reaping " + rs.getInt(1) + " entries");
94 Log.warn(Reaper.class, filename + ": " + "delete from "+reapTable+" where "+reapColumn+"<"+when);
95 ps = conn.prepareStatement("delete from "+reapTable+" where "+reapColumn+"<?");
96 ps.setTimestamp(1, new Timestamp(when));
97 int rows = ps.executeUpdate();
98 Log.warn(Reaper.class, filename + " done reaping; removed " + rows + " rows");
100 } catch (Exception e) { Log.error(Reaper.class, e); }
101 Main.cron.executeLater(1000 * REAPER_INTERVAL_SECONDS, this);