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 // check upstream: PRAGMA encoding = "UTF-8";
23 // PRAGMA auto_vacuum=1 (can only be set before any tables are created)
24 // periodic "PRAGMA integrity_check; "?
26 public void setCacheSize(int kilobytes) throws SQLException {
27 conn.prepareStatement("PRAGMA cache_size="+Math.ceil(kilobytes/1.5)+";").executeUpdate();
30 public SqliteTable(String filename, String[] tables) {
31 this(filename, tables, false);
33 public SqliteTable(String filename, String[] tables, boolean fastButDangerous) {
34 this.filename = filename;
36 Class.forName("org.sqlite.JDBC");
37 conn = DriverManager.getConnection("jdbc:sqlite:"+filename);
38 for(String s : tables)
39 conn.prepareStatement(s).executeUpdate();
40 conn.prepareStatement("PRAGMA temp_store = MEMORY").executeUpdate();
41 conn.prepareStatement("PRAGMA page_size=4096").executeUpdate();
42 conn.prepareStatement("PRAGMA cache_size=2000").executeUpdate();
44 conn.prepareStatement("PRAGMA synchronous = OFF").executeUpdate();
46 catch (SQLException e) { throw new RuntimeException(e); }
47 catch (ClassNotFoundException e) { throw new RuntimeException(e); }
50 protected void reap(String reapTable, String reapColumn) {
51 if (this.reapTable != null || this.reapColumn != null)
52 throw new RuntimeException("reapTable/reapColumn already set");
53 this.reapTable = reapTable;
54 this.reapColumn = reapColumn;
55 if (reapTable != null && reapColumn != null)
56 Main.cron.executeLater(1000 * REAPER_INTERVAL_SECONDS, new Reaper());
59 public static final int REAPER_INTERVAL_SECONDS = 60 * 60;
61 private class Reaper implements Runnable {
64 Log.warn(Reaper.class, filename + " reaping...");
65 long when = System.currentTimeMillis();
66 when -= 5 * 24 * 60 * 60 * 1000;
67 synchronized(SqliteTable.this) {
68 PreparedStatement ps =
69 conn.prepareStatement("select count(*) from "+reapTable+" where "+reapColumn+"<?");
70 ps.setTimestamp(1, new Timestamp(when));
71 ResultSet rs = ps.executeQuery();
73 Log.warn(Reaper.class, filename + " reaping " + rs.getInt(1) + " entries");
74 Log.warn(Reaper.class, filename + ": " + "delete from "+reapTable+" where "+reapColumn+"<"+when);
75 ps = conn.prepareStatement("delete from "+reapTable+" where "+reapColumn+"<?");
76 ps.setTimestamp(1, new Timestamp(when));
77 int rows = ps.executeUpdate();
78 Log.warn(Reaper.class, filename + " done reaping; removed " + rows + " rows");
80 } catch (Exception e) { Log.error(Reaper.class, e); }
81 Main.cron.executeLater(1000 * REAPER_INTERVAL_SECONDS, this);