X-Git-Url: http://git.megacz.com/?a=blobdiff_plain;f=src%2Forg%2Fibex%2Fmail%2FSqliteMailbox.java;h=c880f752899164ae3bd1f0efd81274b534f3e926;hb=ac8259d995898c4ee36b18b7f2a065e00ed78af4;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..c880f75 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,26 +46,172 @@ 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<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();
+            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;
+            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.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 static String joinWith(String op, Query[] q) throws UnsupportedQueryException {
+        boolean add = false;
+        StringBuffer sb = new StringBuffer();
+        for(int i=0; i<q.length; i++) {
+            if (add) sb.append(" " + op);
+            sb.append(" (");
+            sb.append(getWhereClause(q[i]));
+            sb.append(")");
+            add = true;
+        }
+        return sb.toString();
+    }
+    private static String getWhereClause(Query q) throws UnsupportedQueryException {
+        switch(q.type) {
+            case Query.NOT:      return "not ("+getWhereClause(q.q[0])+")";
+            case Query.AND:      return joinWith("and", q.q);
+            case Query.OR:       return joinWith("or", q.q);
+            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;
+                */
+            default: {
+                Log.info(SqliteMailbox.class, "resorting to superclass: " + q.type);
+                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 {
+                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 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+"");
@@ -58,51 +221,89 @@ public class SqliteMailbox extends Mailbox.Default {
             add.setString(7, 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'");
+                this.whereClause = 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 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));
+
                 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 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()+"");
+                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 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 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 {
+                ResultSet rs = conn.prepareStatement("select count(*) from mail where uid_ <= " + uid()).executeQuery();
+                rs.next();
+                return rs.getInt(1);
+            } catch (Exception e) { throw new RuntimeException(e); }
+        }
+        public int     nntpNumber() { return uid(); }
+        public void    delete()     {
+            try {
+                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); }
+        }
     }
 
+
 }