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();
42 public void close(ResultSet rs) {
46 } catch (SQLException s) {
47 Log.error(ResultSet.class, s);
51 public SqliteDB(String filename) {
52 this.filename = filename;
54 Log.error("start", "initializing " + filename);
55 Class.forName("org.sqlite.JDBC");
56 conn = DriverManager.getConnection("jdbc:sqlite:"+filename);
57 conn.prepareStatement("PRAGMA auto_vacuum = 1").executeUpdate();
58 //conn.prepareStatement("VACUUM").executeUpdate();
60 // until we have better assurances about locking on network filesystems...
61 conn.prepareStatement("PRAGMA locking_mode = EXCLUSIVE").executeQuery();
63 //conn.prepareStatement("PRAGMA temp_store = MEMORY").executeUpdate();
64 conn.prepareStatement("PRAGMA page_size=4096").executeUpdate();
65 conn.prepareStatement("PRAGMA cache_size=2000").executeUpdate();
66 ResultSet rs = conn.prepareStatement("PRAGMA integrity_check").executeQuery();
68 String result = rs.getString(1);
69 if (!result.equals("ok"))
70 throw new RuntimeException("PRAGMA integrity_check returned \""+result+"\"");
71 Log.error(".", "done initializing " + filename);
73 catch (SQLException e) { throw new RuntimeException(e); }
74 catch (ClassNotFoundException e) { throw new RuntimeException(e); }
77 public void setFastButDangerous(boolean on) throws SQLException {
78 conn.prepareStatement("PRAGMA synchronous = "+(on?"OFF":"ON")).executeUpdate();
82 public void dump(OutputStream os) {
86 public class SqliteTable {
87 public final String name;
88 private String reapColumn = null;
89 private SqliteTable(String name, String schema) throws SQLException {
91 PreparedStatement ps = conn.prepareStatement("create table if not exists " + name + " " + schema);
93 tables.put(name, this);
95 public void createIndex(String column) throws SQLException { createIndex(column, column+"_index"); }
96 public void createIndex(String indexName, String column) throws SQLException {
97 PreparedStatement ps = conn.prepareStatement("create index if not exists "+column+" on "+name+" ("+indexName+")");
100 protected void reap(String reapColumn) {
101 if (this.reapColumn != null) throw new RuntimeException("reapColumn already set");
102 this.reapColumn = reapColumn;
103 Main.cron.executeLater(1000 * REAPER_INTERVAL_SECONDS, new Reaper(name, reapColumn));
107 // FIXME: desynchronized access to the conn?
108 private class Reaper implements Runnable {
109 public Reaper(String reapTable, String reapColumn) {
110 this.reapTable = reapTable;
111 this.reapColumn = reapColumn;
113 private String reapTable;
114 private String reapColumn;
117 Log.warn(Reaper.class, filename + " reaping...");
118 long when = System.currentTimeMillis();
119 when -= REAP_EXPIRATION;
120 synchronized(SqliteDB.this) {
121 PreparedStatement ps =
122 conn.prepareStatement("select count(*) from "+reapTable+" where "+reapColumn+"<?");
123 ps.setTimestamp(1, new Timestamp(when));
124 ResultSet rs = ps.executeQuery();
126 Log.warn(Reaper.class, filename + " reaping " + rs.getInt(1) + " entries");
127 Log.warn(Reaper.class, filename + ": " + "delete from "+reapTable+" where "+reapColumn+"<"+when);
128 ps = conn.prepareStatement("delete from "+reapTable+" where "+reapColumn+"<?");
129 ps.setTimestamp(1, new Timestamp(when));
130 int rows = ps.executeUpdate();
131 Log.warn(Reaper.class, filename + " done reaping; removed " + rows + " rows");
133 } catch (Exception e) { Log.error(Reaper.class, e); }
134 Main.cron.executeLater(1000 * REAPER_INTERVAL_SECONDS, this);
138 static String streamToString(Stream stream) throws Exception {
139 // FIXME!!!! This is corrupting line endings!!!!
140 StringBuffer b = new StringBuffer();
141 for(String s = stream.readln(); s!=null; s=stream.readln())