X-Git-Url: http://git.megacz.com/?a=blobdiff_plain;f=src%2Forg%2Fibex%2Fmail%2FSqliteMailbox.java;h=5f2370650bac2e6308bf7b3b03cad7720cf4fe9f;hb=b99b78d5717429b5c1380cfe6f13fe8d55d5c8b1;hp=b28ef7f71a463118cf666821297e41447d3d1ac9;hpb=1bd96ef617f2749c253c722d51890fcfc8b29ac4;p=org.ibex.mail.git diff --git a/src/org/ibex/mail/SqliteMailbox.java b/src/org/ibex/mail/SqliteMailbox.java index b28ef7f..5f23706 100644 --- a/src/org/ibex/mail/SqliteMailbox.java +++ b/src/org/ibex/mail/SqliteMailbox.java @@ -22,7 +22,18 @@ public class SqliteMailbox extends Mailbox.Default implements MailTree { 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 @@ -35,6 +46,7 @@ public class SqliteMailbox extends Mailbox.Default implements MailTree { * 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_"; @@ -42,6 +54,7 @@ public class SqliteMailbox extends Mailbox.Default implements MailTree { 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); @@ -57,29 +70,68 @@ public class SqliteMailbox extends Mailbox.Default implements MailTree { } else { this.uidValidity = rs.getInt(1); } - try { - //conn.prepareStatement("create virtual table 'mail' using FTS2("+columns_+")").executeUpdate(); - conn.prepareStatement("create table 'mail' ("+columns_+")").executeUpdate(); - } catch (SQLException e) { - /* FIXME */ - } - conn.prepareStatement("create index if not exists uid_index on mail(uid_);").executeUpdate(); + 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); } } + 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()) 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() { return new SqliteJdbcIterator(); } - private static String set(int[] set, String arg) { + 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 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.type); + Log.info(SqliteMailbox.class, "resorting to superclass: " + q); throw new UnsupportedQueryException(); } } @@ -149,6 +226,7 @@ public class SqliteMailbox extends Mailbox.Default implements MailTree { 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); @@ -159,17 +237,43 @@ public class SqliteMailbox extends Mailbox.Default implements MailTree { } public 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 or replace 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); } } @@ -184,27 +288,42 @@ public class SqliteMailbox extends Mailbox.Default implements MailTree { public SqliteJdbcIterator() { this(""); } public SqliteJdbcIterator(String whereClause) { try { + /* + 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(); } 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; 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 (!rs2.next()) { - Log.error("XXX", "should not happen"); - return null; - } + 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(rs2.getString(2)))); flags = rs2.getInt(3); - return m; } catch (Exception e) { throw new RuntimeException(e); } } @@ -222,7 +341,6 @@ public class SqliteMailbox extends Mailbox.Default implements MailTree { update.executeUpdate(); } catch (Exception e) { throw new RuntimeException(e); } } - public Headers head() { return cur().headers; } public boolean next() { try { m = null; uid = -1; count++; boolean ret = rs.next(); @@ -244,19 +362,19 @@ public class SqliteMailbox extends Mailbox.Default implements MailTree { 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; + } catch (Exception e) { throw new RuntimeException(e); } } } - private static String streamToString(Stream stream) throws Exception { - // FIXME - StringBuffer b = new StringBuffer(); - for(String s = stream.readln(); s!=null; s=stream.readln()) - b.append(s+"\n"); - return b.toString(); - } }