X-Git-Url: http://git.megacz.com/?a=blobdiff_plain;f=src%2Forg%2Fibex%2Fmail%2FSqliteMailbox.java;h=384dc4d73d76c65de78e7300c7f1f1a2b07bf99c;hb=f110e29a9029e8f12957edfcda0330a95c6814ee;hp=66ed67474183df007bed05b3d45bbdc2149639fd;hpb=67ca42596372ee8d715696fa4bd3ad91cfc2c4d1;p=org.ibex.mail.git diff --git a/src/org/ibex/mail/SqliteMailbox.java b/src/org/ibex/mail/SqliteMailbox.java index 66ed674..384dc4d 100644 --- a/src/org/ibex/mail/SqliteMailbox.java +++ b/src/org/ibex/mail/SqliteMailbox.java @@ -1,5 +1,6 @@ package org.ibex.mail; +import org.ibex.util.*; import org.ibex.io.Fountain; import org.ibex.io.Stream; import java.sql.Timestamp; @@ -8,15 +9,31 @@ import java.net.*; 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) { return null; } + 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 @@ -29,80 +46,332 @@ public class SqliteMailbox extends Mailbox.Default { * allowed and any attempt to insert a new row will fail with an * SQLITE_FULL error. */ + // FIXME: should messageid_ be decared unique? private static final String columns_ = "uid_ INTEGER PRIMARY KEY AUTOINCREMENT, messageid_ unique,from_,to_,date_,subject_,headers_,body_,flags_"; - public SqliteMailbox(String filename) { + private final int uidValidity; + private final File file; + public int uidValidity() { return uidValidity; } + + public String toString() { return file.getName(); } + public SqliteMailbox(String filename) throws SQLException { try { + this.file = new File(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.prepareStatement("create table if not exists uidvalidity (uidvalidity)").executeUpdate(); + ResultSet 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(); } catch (SQLException 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(); } + private HashMap imapToUid = new HashMap(); + private HashMap uidToImap = new HashMap(); + 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(); + int num = 1; + while(rs.next()) { + imapToUid.put(num, rs.getInt(1)); + uidToImap.put(rs.getInt(1), num); + num++; + } + imapNumberCacheValid = true; + } + } + 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; + if (!rs.next()) throw new RuntimeException("select max(uid_) returned no rows!"); + return rs.getInt(1)+1; + } 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[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 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(); + rs.next(); + return rs.getInt(1); + } catch (Exception e) { throw new RuntimeException(e); } + } catch (UnsupportedQueryException _) { + return super.count(q); + } + } public void insert(Message m, int flags) { - // FIXME: 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 messageid_=?"); + query.setString(1, rs.getString(1)); + Log.warn("SQL", "select headers_,flags_ from 'mail' where messageid_="+rs.getString(1)); + + 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)); + Log.warn("SQL", "select headers_,body_,flags_ from 'mail' where messageid_="+rs.getString(1)); + 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); } + } } + }