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;
22 private static final int DAYS = 24 * 60 * 60 * 1000;
23 public static final int REAP_EXPIRATION = 5 * DAYS;
25 public Connection getConnection() { return conn; }
27 public synchronized SqliteTable getTable(String name, String schema) throws SQLException {
28 SqliteTable ret = tables.get(name);
29 if (ret==null) ret = new SqliteTable(name, schema);
33 // check upstream: PRAGMA encoding = "UTF-8";
35 // periodically run "analyze"?
37 public void setCacheSize(int kilobytes) throws SQLException {
38 conn.prepareStatement("PRAGMA cache_size="+Math.ceil(kilobytes/1.5)+";").executeUpdate();
41 public SqliteDB(String filename) {
42 this.filename = filename;
44 Class.forName("org.sqlite.JDBC");
45 conn = DriverManager.getConnection("jdbc:sqlite:"+filename);
46 conn.prepareStatement("PRAGMA auto_vacuum = 1").executeUpdate();
47 conn.prepareStatement("VACUUM").executeUpdate();
49 // until we have better assurances about locking on network filesystems...
50 conn.prepareStatement("PRAGMA locking_mode = EXCLUSIVE").executeQuery();
52 conn.prepareStatement("PRAGMA temp_store = MEMORY").executeUpdate();
53 conn.prepareStatement("PRAGMA page_size=4096").executeUpdate();
54 conn.prepareStatement("PRAGMA cache_size=2000").executeUpdate();
55 ResultSet rs = conn.prepareStatement("PRAGMA integrity_check").executeQuery();
57 String result = rs.getString(1);
58 if (!result.equals("ok"))
59 throw new RuntimeException("PRAGMA integrity_check returned \""+result+"\"");
61 catch (SQLException e) { throw new RuntimeException(e); }
62 catch (ClassNotFoundException e) { throw new RuntimeException(e); }
65 public void setFastButDangerous(boolean on) throws SQLException {
66 conn.prepareStatement("PRAGMA synchronous = "+(on?"OFF":"ON")).executeUpdate();
70 public void dump(OutputStream os) {
74 public class SqliteTable {
75 public final String name;
76 private String reapColumn = null;
77 private SqliteTable(String name, String schema) throws SQLException {
79 PreparedStatement ps = conn.prepareStatement("create table if not exists " + name + " " + schema);
81 tables.put(name, this);
83 public void createIndex(String column) throws SQLException { createIndex(column, column+"_index"); }
84 public void createIndex(String indexName, String column) throws SQLException {
85 PreparedStatement ps = conn.prepareStatement("create index if not exists "+column+" on "+name+" ("+indexName+")");
88 protected void reap(String reapColumn) {
89 if (this.reapColumn != null) throw new RuntimeException("reapColumn already set");
90 this.reapColumn = reapColumn;
91 Main.cron.executeLater(1000 * REAPER_INTERVAL_SECONDS, new Reaper(name, reapColumn));
95 // FIXME: desynchronized access to the conn?
96 private class Reaper implements Runnable {
97 public Reaper(String reapTable, String reapColumn) {
98 this.reapTable = reapTable;
99 this.reapColumn = reapColumn;
101 private String reapTable;
102 private String reapColumn;
105 Log.warn(Reaper.class, filename + " reaping...");
106 long when = System.currentTimeMillis();
107 when -= REAP_EXPIRATION;
108 synchronized(SqliteDB.this) {
109 PreparedStatement ps =
110 conn.prepareStatement("select count(*) from "+reapTable+" where "+reapColumn+"<?");
111 ps.setTimestamp(1, new Timestamp(when));
112 ResultSet rs = ps.executeQuery();
114 Log.warn(Reaper.class, filename + " reaping " + rs.getInt(1) + " entries");
115 Log.warn(Reaper.class, filename + ": " + "delete from "+reapTable+" where "+reapColumn+"<"+when);
116 ps = conn.prepareStatement("delete from "+reapTable+" where "+reapColumn+"<?");
117 ps.setTimestamp(1, new Timestamp(when));
118 int rows = ps.executeUpdate();
119 Log.warn(Reaper.class, filename + " done reaping; removed " + rows + " rows");
121 } catch (Exception e) { Log.error(Reaper.class, e); }
122 Main.cron.executeLater(1000 * REAPER_INTERVAL_SECONDS, this);
126 static String streamToString(Stream stream) throws Exception {
128 StringBuffer b = new StringBuffer();
129 for(String s = stream.readln(); s!=null; s=stream.readln())