4 import org.ibex.mail.protocol.*;
5 import org.ibex.util.*;
11 import java.sql.Timestamp;
12 import java.sql.Connection;
14 public class SqliteTable {
16 protected Connection conn;
17 private String filename;
18 private String reapTable;
19 private String reapColumn;
21 public SqliteTable(String filename, String[] tables, String reapTable, String reapColumn) {
22 this(filename, tables, false, reapTable, reapColumn);
24 public SqliteTable(String filename, String[] tables, boolean fastButDangerous,
25 String reapTable, String reapColumn) {
26 this.filename = filename;
28 Class.forName("org.sqlite.JDBC");
29 conn = DriverManager.getConnection("jdbc:sqlite:"+filename);
30 for(String s : tables)
31 conn.prepareStatement(s).executeUpdate();
32 conn.prepareStatement("PRAGMA temp_store = MEMORY").executeUpdate();
33 conn.prepareStatement("PRAGMA page_size=4096").executeUpdate();
34 conn.prepareStatement("PRAGMA cache_size=2000").executeUpdate();
36 conn.prepareStatement("PRAGMA synchronous = OFF").executeUpdate();
38 catch (SQLException e) { throw new RuntimeException(e); }
39 catch (ClassNotFoundException e) { throw new RuntimeException(e); }
40 this.reapTable = reapTable;
41 this.reapColumn = reapColumn;
42 if (reapTable != null && reapColumn != null)
46 public static final int REAPER_INTERVAL_SECONDS = 60 * 60;
48 private class Reaper extends Thread {
53 try { Thread.sleep(1000 * REAPER_INTERVAL_SECONDS); } catch (Exception e) { };
54 Log.warn(Reaper.class, filename + " reaping...");
55 long when = System.currentTimeMillis();
56 when -= 5 * 24 * 60 * 60 * 1000;
57 synchronized(SqliteTable.this) {
58 PreparedStatement ps = conn.prepareStatement("select count(*) from "+reapTable+" where "+reapColumn+"<?");
59 ps.setTimestamp(1, new Timestamp(when));
60 ResultSet rs = ps.executeQuery();
62 Log.warn(Reaper.class, filename + " reaping " + rs.getInt(1) + " entries");
63 Log.warn(Reaper.class, filename + ": " + "delete from "+reapTable+" where "+reapColumn+"<"+when);
64 ps = conn.prepareStatement("delete from "+reapTable+" where "+reapColumn+"<?");
65 ps.setTimestamp(1, new Timestamp(when));
66 int rows = ps.executeUpdate();
67 Log.warn(Reaper.class, filename + " done reaping; removed " + rows + " rows");
69 } catch (Exception e) { Log.error(Reaper.class, e); }