--- /dev/null
+package org.ibex.mail;
+
+import org.ibex.io.*;
+import org.ibex.mail.protocol.*;
+import org.ibex.util.*;
+import org.ibex.net.*;
+import java.sql.*;
+import java.net.*;
+import java.io.*;
+import java.util.*;
+import java.sql.Timestamp;
+import java.sql.Connection;
+
+public class SqliteDB {
+
+ protected Connection conn;
+ private String filename;
+ private String reapTable;
+ private String reapColumn;
+
+ // check upstream: PRAGMA encoding = "UTF-8";
+ // create indices
+ // PRAGMA auto_vacuum=1 (can only be set before any tables are created)
+ // periodic "PRAGMA integrity_check; "?
+
+ public void setCacheSize(int kilobytes) throws SQLException {
+ conn.prepareStatement("PRAGMA cache_size="+Math.ceil(kilobytes/1.5)+";").executeUpdate();
+ }
+
+ public SqliteDB(String filename, String[] tables) {
+ this(filename, tables, false);
+ }
+ public SqliteDB(String filename, String[] tables, boolean fastButDangerous) {
+ this.filename = filename;
+ try {
+ Class.forName("org.sqlite.JDBC");
+ conn = DriverManager.getConnection("jdbc:sqlite:"+filename);
+ for(String s : tables)
+ conn.prepareStatement(s).executeUpdate();
+ conn.prepareStatement("PRAGMA temp_store = MEMORY").executeUpdate();
+ conn.prepareStatement("PRAGMA page_size=4096").executeUpdate();
+ conn.prepareStatement("PRAGMA cache_size=2000").executeUpdate();
+ if (fastButDangerous)
+ conn.prepareStatement("PRAGMA synchronous = OFF").executeUpdate();
+ }
+ catch (SQLException e) { throw new RuntimeException(e); }
+ catch (ClassNotFoundException e) { throw new RuntimeException(e); }
+ }
+
+ protected void reap(String reapTable, String reapColumn) {
+ if (this.reapTable != null || this.reapColumn != null)
+ throw new RuntimeException("reapTable/reapColumn already set");
+ this.reapTable = reapTable;
+ this.reapColumn = reapColumn;
+ if (reapTable != null && reapColumn != null)
+ Main.cron.executeLater(1000 * REAPER_INTERVAL_SECONDS, new Reaper());
+ }
+
+ public static final int REAPER_INTERVAL_SECONDS = 60 * 60;
+
+ private class Reaper implements Runnable {
+ public void run() {
+ try {
+ Log.warn(Reaper.class, filename + " reaping...");
+ long when = System.currentTimeMillis();
+ when -= 5 * 24 * 60 * 60 * 1000;
+ synchronized(SqliteDB.this) {
+ PreparedStatement ps =
+ conn.prepareStatement("select count(*) from "+reapTable+" where "+reapColumn+"<?");
+ ps.setTimestamp(1, new Timestamp(when));
+ ResultSet rs = ps.executeQuery();
+ if (rs.next())
+ Log.warn(Reaper.class, filename + " reaping " + rs.getInt(1) + " entries");
+ Log.warn(Reaper.class, filename + ": " + "delete from "+reapTable+" where "+reapColumn+"<"+when);
+ ps = conn.prepareStatement("delete from "+reapTable+" where "+reapColumn+"<?");
+ ps.setTimestamp(1, new Timestamp(when));
+ int rows = ps.executeUpdate();
+ Log.warn(Reaper.class, filename + " done reaping; removed " + rows + " rows");
+ }
+ } catch (Exception e) { Log.error(Reaper.class, e); }
+ Main.cron.executeLater(1000 * REAPER_INTERVAL_SECONDS, this);
+ }
+ }
+
+}