package org.ibex.mail;
+import org.ibex.util.*;
import org.ibex.io.Fountain;
import org.ibex.io.Stream;
import java.sql.Timestamp;
import java.io.*;
import java.util.*;
-// nntpNumber (column)
-// uid (column)
-// uidvalidity
-public class SqliteMailbox extends Mailbox.Default {
+
+public class SqliteMailbox extends Mailbox.Default implements MailTree {
+
+ public MailTree slash(String name, boolean create) {
+ String p = file.getAbsolutePath();
+ if (p.endsWith(".sqlite")) p = p.substring(0, p.length()-".sqlite".length());
+ return FileBasedMailbox.getFileBasedMailbox(p+"/"+name, create);
+ }
+ public String[] children() { return new String[0]; }
+ public void rmdir(String subdir) { throw new RuntimeException("invalid"); }
+ public void rename(String subdir, MailTree newParent, String newName) { throw new RuntimeException("invalid"); }
+ public Mailbox getMailbox() { return this; }
+
private Connection conn;
private static final String columns =
" messageid_, from_,to_,date_,subject_,headers_,body_,flags_";
-
+ private static final String[] indexedColumns = new String[] {
+ "uid_",
+ "messageid_",
+ "flags_",
+ /*
+ "from_",
+ "to_",
+ "subject_",
+ "date_"
+ */
+ };
+
/**
* from http://www.sqlite.org/autoinc.html
* "If a column has the type INTEGER PRIMARY KEY AUTOINCREMENT
* SQLITE_FULL error.
*/
private static final String columns_ =
- "uid_ INTEGER PRIMARY KEY AUTOINCREMENT, messageid_ unique,from_,to_,date_,subject_,headers_,body_,flags_";
+ "uid_ INTEGER PRIMARY KEY AUTOINCREMENT, messageid_,from_,to_,date_,subject_,headers_,body_,flags_";
+
+ private final int uidValidity;
+ private final File file;
+ private final SqliteDB db;
- public SqliteMailbox(String filename) {
+ public int uidValidity() { return uidValidity; }
+
+ public String toString() { return file.getName(); }
+ public SqliteMailbox(String filename) throws SQLException {
+ ResultSet rs = null;
try {
+ this.file = new File(filename);
+ this.db = new SqliteDB(filename);
+ /*
Class.forName("org.sqlite.JDBC");
conn = DriverManager.getConnection("jdbc:sqlite:"+filename);
- conn.prepareStatement("create virtual table if not exists 'mail' using FTS2("+columns_+")").executeUpdate();
+ */
+ conn = db.getConnection();
+ conn.prepareStatement("create table if not exists uidvalidity (uidvalidity)").executeUpdate();
+ try {
+ rs = conn.prepareStatement("select uidvalidity from uidvalidity").executeQuery();
+ if (!rs.next()) {
+ this.uidValidity = new Random().nextInt();
+ PreparedStatement ps = conn.prepareStatement("insert into uidvalidity (uidvalidity) values (?)");
+ ps.setInt(1, uidValidity);
+ ps.executeUpdate();
+ } else {
+ this.uidValidity = rs.getInt(1);
+ }
+ conn.prepareStatement("create table if not exists 'mail' ("+columns_+")").executeUpdate();
+ for(String name : indexedColumns)
+ conn.prepareStatement("create index if not exists "+name+"index on mail("+name+");").executeUpdate();
+ } finally { db.close(rs); }
}
catch (SQLException e) { throw new RuntimeException(e); }
- catch (ClassNotFoundException e) { throw new RuntimeException(e); }
+ //catch (ClassNotFoundException e) { throw new RuntimeException(e); }
}
- public int uidNext() { throw new RuntimeException("not supported"); }
- public Mailbox.Iterator iterator() { return new SqliteJdbcIterator(); }
- public void insert(Message m, int flags) {
- // FIXME: flags
+ private HashMap<Integer,Integer> imapToUid = new HashMap<Integer,Integer>();
+ private HashMap<Integer,Integer> uidToImap = new HashMap<Integer,Integer>();
+ private boolean imapNumberCacheValid = false;
+ public void updateImapNumberCache() throws SQLException {
+ synchronized(this) {
+ Log.warn(this+"", "rebuilding imapNumberCache...");
+ imapToUid.clear();
+ uidToImap.clear();
+ PreparedStatement q = conn.prepareStatement("select uid_ from mail");
+ ResultSet rs = q.executeQuery();
+ try {
+ int num = 1;
+ while(rs.next()) {
+ imapToUid.put(num, rs.getInt(1));
+ uidToImap.put(rs.getInt(1), num);
+ num++;
+ }
+ imapNumberCacheValid = true;
+ } finally { db.close(rs); }
+ }
+ }
+ public int queryImapNumberCache(int uid) throws SQLException {
+ synchronized(this) {
+ if (!imapNumberCacheValid) updateImapNumberCache();
+ Integer ret = uidToImap.get(uid);
+ if (ret == null) return -1;
+ return ret;
+ }
+ }
+ public int queryUidForImapNum(int imapNumber) throws SQLException {
+ synchronized(this) {
+ if (!imapNumberCacheValid) updateImapNumberCache();
+ Integer ret = imapToUid.get(imapNumber);
+ if (ret == null) return -1;
+ return ret;
+ }
+ }
+
+
+ public int maxuid() { return uidNext(); }
+ public int uidNext() {
+ try {
+ PreparedStatement q = conn.prepareStatement("select max(uid_) from mail");
+ ResultSet rs = q.executeQuery();
+ //if (!rs.next()) return -1;
+ try {
+ if (!rs.next()) throw new RuntimeException("select max(uid_) returned no rows!");
+ return rs.getInt(1)+1;
+ } finally { db.close(rs); }
+ } catch (Exception e) { throw new RuntimeException(e); }
+ }
+
+ public Mailbox.Iterator iterator() {
+ Log.warn(this, "performance warning: called iterator() on entire mailbox");
+ Log.printStackTrace(this, Log.WARN);
+ return new SqliteJdbcIterator();
+ }
+ private String set(int[] set, String arg) {
+ String whereClause = "";
+ boolean needsOr = false;
+ for(int i=0; i<set.length; i+=2) {
+ if (needsOr) whereClause += " or ";
+ whereClause += "(";
+ whereClause += arg+">=" + set[i];
+ whereClause += " and ";
+ while(i+2 < set.length && set[i+2] == (set[i+1]+1)) i += 2;
+ whereClause += arg+"<=" + set[i+1];
+ whereClause += ")";
+ needsOr = true;
+ }
+ return whereClause;
+ }
+ private String getWhereClause(Query q) throws UnsupportedQueryException {
+ String op;
+ switch(q.type) {
+ case Query.NOT: return "not ("+getWhereClause(q.q[0])+")";
+ case Query.AND: op = "and";
+ case Query.OR: op = "or";
+ {
+ boolean add = false;
+ StringBuffer sb = new StringBuffer();
+ for(int i=0; i<q.q.length; i++) {
+ if (add) sb.append(" " + op);
+ sb.append(" (");
+ sb.append(getWhereClause(q.q[i]));
+ sb.append(")");
+ add = true;
+ }
+ return sb.toString();
+ }
+ case Query.ALL: return "1=1";
+ case Query.UID: return set(q.set, "uid_");
+ case Query.DELETED: return "((flags_ & "+(Mailbox.Flag.DELETED)+")!=0)";
+ case Query.SEEN: return "((flags_ & "+(Mailbox.Flag.SEEN)+")!=0)";
+ case Query.FLAGGED: return "((flags_ & "+(Mailbox.Flag.FLAGGED)+")!=0)";
+ case Query.DRAFT: return "((flags_ & "+(Mailbox.Flag.DRAFT)+")!=0)";
+ case Query.ANSWERED: return "((flags_ & "+(Mailbox.Flag.ANSWERED)+")!=0)";
+ case Query.RECENT: return "((flags_ & "+(Mailbox.Flag.RECENT)+")!=0)";
+ /*
+ public static final int SENT = 5;
+ public static final int ARRIVAL = 6;
+ public static final int HEADER = 7;
+ public static final int SIZE = 8;
+ public static final int BODY = 9;
+ public static final int FULL = 10;
+ public static final int IMAPNUM = 11;
+ */
+
+ case Query.IMAPNUM: {
+ try {
+ // translate queries in terms of imap numbers into queries in terms of uids
+ // RELIES ON THE FACT THAT UIDS ARE MONOTONICALLY INCREASING
+ int[] set = new int[q.set==null ? 2 : q.set.length];
+ if (q.set==null) { set[0] = q.min; set[1] = q.max; }
+ else System.arraycopy(q.set, 0, set, 0, q.set.length);
+ for(int i=0; i<set.length; i++) {
+ int uid = queryUidForImapNum(set[i]);
+ if (uid==-1) {
+ Log.info(SqliteMailbox.class, "PROBLEM => resorting to superclass: " + q);
+ throw new UnsupportedQueryException();
+ }
+ set[i] = uid;
+ }
+ return getWhereClause(Query.uid(set));
+ } catch (SQLException e) {
+ Log.error(this, e);
+ Log.info(SqliteMailbox.class, "resorting to superclass: " + q);
+ throw new UnsupportedQueryException();
+ }
+ }
+
+ default: {
+ Log.info(SqliteMailbox.class, "resorting to superclass: " + q);
+ throw new UnsupportedQueryException();
+ }
+ }
+ }
+ private static class UnsupportedQueryException extends Exception { }
+ public Mailbox.Iterator iterator(Query q) {
try {
+ String whereClause = getWhereClause(q);
+ Log.info(this, "whereClause = " + whereClause);
+ return new SqliteJdbcIterator("where "+whereClause+";");
+ } catch (UnsupportedQueryException _) {
+ return super.iterator(q);
+ }
+ }
+ public int count(Query q) {
+ try {
+ String whereClause = getWhereClause(q);
+ Log.info(this, "whereClause = " + whereClause);
+ try {
+ Log.warn("SQL", "select count(*) from mail where " + whereClause);
+ ResultSet rs = conn.prepareStatement("select count(*) from mail where " + whereClause).executeQuery();
+ try {
+ rs.next();
+ return rs.getInt(1);
+ } finally { db.close(rs); }
+ } catch (Exception e) { throw new RuntimeException(e); }
+ } catch (UnsupportedQueryException _) {
+ return super.count(q);
+ }
+ }
+ public synchronized void insert(Message m, int flags) {
+ try {
+
+ /*
+ PreparedStatement query = conn.prepareStatement("select headers_,body_,flags_ from 'mail' where messageid_=?");
+ query.setString(1, m.messageid);
+ Log.warn("SQL", "select headers_,body_,flags_ from 'mail' where messageid_="+m.messageid);
+ ResultSet rs2 = query.executeQuery();
+ if (rs2.next()) {
+ Message m2 = Message.newMessage(Fountain.Util.concat(Fountain.Util.create(rs2.getString(1)),
+ Fountain.Util.create("\r\n\r\n"),
+ Fountain.Util.create(rs2.getString(2))));
+ StringBuffer s1 = new StringBuffer();
+ m.getBody().getStream().transcribe(s1);
+ StringBuffer s2 = new StringBuffer();
+ m2.getBody().getStream().transcribe(s2);
+ if (!s1.toString().equals(s2.toString())) {
+ Log.error(this.toString(),
+ "attempt to insert two messages with identical messageid ("+m.messageid+") but different bodies:\n"+
+ " (body length="+s1.length()+") "+m.summary()+"\n"+
+ " (body length="+s2.length()+") "+m2.summary()+"\n");
+ } else {
+ Log.warn(this.toString(),
+ "silently dropping duplicate insert() [messageids and bodies match]: " + m.summary());
+ return;
+ }
+ }
+ */
PreparedStatement add =
- conn.prepareStatement("insert into 'mail' ("+columns+") values (?,?,?,?,?,?,?,?)");
+ conn.prepareStatement("insert "+/*"or replace "+*/"into 'mail' ("+columns+") values (?,?,?,?,?,?,?,?)");
add.setString(1, m.messageid+"");
add.setString(2, m.from+"");
add.setString(3, m.to+"");
add.setString(4, m.date+"");
add.setString(5, m.subject+"");
- add.setString(6, streamToString(m.headers.getStream()));
- add.setString(7, streamToString(m.getBody().getStream()));
+ add.setString(6, SqliteDB.streamToString(m.headers.getStream()));
+ add.setString(7, SqliteDB.streamToString(m.getBody().getStream()));
add.setInt (8, flags);
add.executeUpdate();
+
+ // FIXME: be smarter here?
+ imapNumberCacheValid = false;
} catch (Exception e) { throw new RuntimeException(e); }
}
- private class SqliteJdbcIterator extends Mailbox.Default.Iterator {
+ private class SqliteJdbcIterator implements Mailbox.Iterator {
// could be more efficient in a ton of ways
private ResultSet rs;
- private int count = 1;
+ private int count = 0;
private int flags;
private Message m = null;
- public SqliteJdbcIterator() {
+ private int uid = -1;
+ private String whereClause;
+ public SqliteJdbcIterator() { this(""); }
+ public SqliteJdbcIterator(String whereClause) {
try {
- PreparedStatement query = conn.prepareStatement("select messageid_ from 'mail'");
+ /*
+ if (whereClause.equals(""))
+ Log.warn(this, "performance warning: empty whereClause");
+ */
+ this.whereClause = whereClause;
+ Log.warn("SQL", "select messageid_,uid_,flags_ from 'mail' "+whereClause);
+ PreparedStatement query = conn.prepareStatement("select messageid_,uid_,flags_ from 'mail' "+whereClause);
rs = query.executeQuery();
- rs.next();
+ } catch (Exception e) { throw new RuntimeException(e); }
+ }
+ public Headers head() {
+ if (m != null) return m.headers;
+ try {
+ PreparedStatement query = conn.prepareStatement("select headers_,flags_ from 'mail' where uid_=?");
+ query.setString(1, rs.getString(2));
+ Log.warn("SQL", "select headers_,flags_ from 'mail' where uid_="+rs.getString(2));
+
+ ResultSet rs2 = query.executeQuery();
+ if (!rs2.next()) { Log.error("XXX", "should not happen"); return null; }
+ flags = rs2.getInt(2);
+ return new Headers(Fountain.Util.create(rs2.getString(1)));
} catch (Exception e) { throw new RuntimeException(e); }
}
public Message cur() {
try {
if (m!=null) return m;
- rs.next();
- PreparedStatement query = conn.prepareStatement("select headers_,body_,flags_ from 'mail' where messageid_=?");
- query.setString(1, rs.getString(1));
+ PreparedStatement query = conn.prepareStatement("select headers_,body_,flags_ from 'mail' where uid_=?");
+ query.setString(1, rs.getString(2));
+ Log.warn("SQL", "select headers_,body_,flags_ from 'mail' where uid_="+rs.getString(2));
+
ResultSet rs2 = query.executeQuery();
- if (!rs.next()) return null;
- m = Message.newMessage(Fountain.Util.concat(Fountain.Util.create(rs.getString(1)),
+ if (!rs2.next()) { Log.error("XXX", "should not happen"); return null; }
+ m = Message.newMessage(Fountain.Util.concat(Fountain.Util.create(rs2.getString(1)),
Fountain.Util.create("\r\n\r\n"),
- Fountain.Util.create(rs.getString(2))));
- flags = rs.getInt(3);
+ Fountain.Util.create(rs2.getString(2))));
+ flags = rs2.getInt(3);
return m;
} catch (Exception e) { throw new RuntimeException(e); }
}
- public int getFlags() { if (m==null) /* could be more efficient */ cur(); return flags; }
- public Headers head() { return cur().headers; }
- public boolean next() { try { m = null; count++; return rs.next(); } catch (Exception e) { throw new RuntimeException(e); } }
- public int uid() { throw new RuntimeException("not supported"); }
- public int imapNumber() { return count; }
- public int nntpNumber() { throw new RuntimeException("not supported"); }
- public void delete() { throw new RuntimeException("not supported"); }
- }
+ public int getFlags() {
+ try { return rs.getInt("flags_"); } catch (Exception e) { throw new RuntimeException(e); }
+ }
+ public void setFlags(int flags) {
+ try {
+ int oldflags = rs.getInt("flags_");
+ if (oldflags==flags) return;
+ Log.info(this, "setflags (old="+oldflags+")" + "update mail set flags_="+(flags)+" where uid_="+uid()+"");
+ if ((flags & Mailbox.Flag.DELETED) != 0) Log.printStackTrace("deletion", Log.WARN);
+ PreparedStatement update = conn.prepareStatement("update mail set flags_=? where uid_=?");
+ update.setInt(1, flags);
+ update.setInt(2, uid());
+ update.executeUpdate();
+ } catch (Exception e) { throw new RuntimeException(e); }
+ }
+ public boolean next() {
+ try { m = null; uid = -1; count++;
+ boolean ret = rs.next();
+ return ret;
+ } catch (Exception e) { throw new RuntimeException(e); } }
+ public int uid() {
+ if (uid == -1)
+ try { uid = rs.getInt("uid_"); } catch (Exception e) { throw new RuntimeException(e); }
+ return uid;
+ }
+ public int imapNumber() {
+ if ("".equals(whereClause)) return count;
+ try { return queryImapNumberCache(uid()); } catch (SQLException s) { throw new RuntimeException(s); }
+ }
+ public int nntpNumber() { return uid(); }
+ public void delete() {
+ try {
+ Log.error("sqlite", "actually deleting message "+uid()+" "+head().get("subject"));
+ Log.printStackTrace("sqlite", Log.ERROR);
+
+ PreparedStatement update = conn.prepareStatement("delete from mail where uid_=?");
+ update.setInt(1, uid());
+ update.executeUpdate();
+
+ // FIXME: be smarter here?
+ imapNumberCacheValid = false;
- private static String streamToString(Stream stream) throws Exception {
- StringBuffer b = new StringBuffer();
- for(String s = stream.readln(); s!=null; s=stream.readln())
- b.append(s+"\n");
- return b.toString();
+ } catch (Exception e) { throw new RuntimeException(e); }
+ }
}
+
}
projects / org.ibex.mail.git / blobdiff