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 column) throws SQLException { createIndex(column, column+"_index"); }
82 public void createIndex(String indexName, String column) throws SQLException {
83 PreparedStatement ps = conn.prepareStatement("create index if not exists "+column+" on "+name+" ("+indexName+")");
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);
124 static String streamToString(Stream stream) throws Exception {
126 StringBuffer b = new StringBuffer();
127 for(String s = stream.readln(); s!=null; s=stream.readln())