public class Graylist extends SqliteDB {
- public Graylist(String filename) {
- super(filename,
- new String[] {
- "create table if not exists 'whitelist' (ip unique)",
- "create table if not exists 'graylist' (ip,fromaddr,toaddr,date, primary key(ip,fromaddr,toaddr))"
- });
- getTable("graylist").reap("date");
+ public Graylist(String filename) throws SQLException {
+ super(filename);
+ SqliteTable whitelist = getTable("whitelist", "(ip unique)");
+ SqliteTable graylist = getTable("graylist", "(ip,fromaddr,toaddr,date,PRIMARY KEY(ip,fromaddr,toaddr))");
+ graylist.reap("date");
}
public synchronized void addWhitelist(InetAddress ip) {
public static final int GRAYLIST_MINWAIT = 1000 * 60 * 60; // one hour
public static final int GRAYLIST_MAXWAIT = 1000 * 60 * 60 * 24 * 5; // five days
- public static final Graylist graylist =
- new Graylist(Mailbox.STORAGE_ROOT+"/db/graylist.sqlite");
-
- public static final Whitelist whitelist =
- new Whitelist(Mailbox.STORAGE_ROOT+"/db/whitelist.sqlite");
+ public static final Graylist graylist;
+ public static final Whitelist whitelist;
+ static {
+ try {
+ graylist = new Graylist(Mailbox.STORAGE_ROOT+"/db/graylist.sqlite");
+ whitelist = new Whitelist(Mailbox.STORAGE_ROOT+"/db/whitelist.sqlite");
+ } catch (Exception e) {
+ throw new RuntimeException(e);
+ }
+ }
public static final int MAX_MESSAGE_SIZE =
Integer.parseInt(System.getProperty("org.ibex.mail.smtp.maxMessageSize", "-1"));
public Connection getConnection() { return conn; }
- public synchronized SqliteTable getTable(String name) {
+ public synchronized SqliteTable getTable(String name, String schema) throws SQLException {
SqliteTable ret = tables.get(name);
- if (ret==null) ret = new SqliteTable(name);
+ if (ret==null) ret = new SqliteTable(name, schema);
return ret;
}
// 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; "?
+ // periodically run "analyze"?
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) {
+ public SqliteDB(String filename) {
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 auto_vacuum = 1").executeUpdate();
+ conn.prepareStatement("VACUUM").executeUpdate();
+ // until we have better assurances about locking on network filesystems...
+ conn.prepareStatement("PRAGMA locking_mode = EXCLUSIVE").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();
+ ResultSet rs = conn.prepareStatement("PRAGMA integrity_check").executeQuery();
+ rs.next();
+ String result = rs.getString(1);
+ if (!result.equals("ok"))
+ throw new RuntimeException("PRAGMA integrity_check returned \""+result+"\"");
}
catch (SQLException e) { throw new RuntimeException(e); }
catch (ClassNotFoundException e) { throw new RuntimeException(e); }
}
+ public void setFastButDangerous(boolean on) throws SQLException {
+ conn.prepareStatement("PRAGMA synchronous = "+(on?"OFF":"ON")).executeUpdate();
+ }
+
+ /*
+ public void dump(OutputStream os) {
+ }
+ */
+
public class SqliteTable {
public final String name;
private String reapColumn = null;
- private SqliteTable(String name) {
+ private SqliteTable(String name, String schema) throws SQLException {
this.name = name;
+ PreparedStatement ps = conn.prepareStatement("create table if not exists ?");
+ ps.setString(1, name+" "+schema);
+ ps.executeUpdate();
tables.put(name, this);
}
+ public void createIndex(String name, String column) throws SQLException {
+ PreparedStatement ps = conn.prepareStatement("create index if not exists ? on ?");
+ ps.setString(1, name);
+ ps.setString(2, column);
+ ps.executeUpdate();
+ }
protected void reap(String reapColumn) {
if (this.reapColumn != null) throw new RuntimeException("reapColumn already set");
this.reapColumn = reapColumn;
public class Whitelist extends SqliteDB {
- public Whitelist(String filename) {
- super(filename,
- new String[] {
- "create table if not exists 'whitelist' (email)",
- "create table if not exists 'pending' (spamid,email,message,date)"
- },
- true);
- getTable("pending").reap("date");
+ public Whitelist(String filename) throws SQLException {
+ super(filename);
+ SqliteTable whitelist = getTable("whitelist", "(email)");
+ SqliteTable pending = getTable("pending", "(spamid,email,message,date)");
+ pending.reap("date");
}
public boolean handleRequest(org.ibex.net.Connection c) {