From: adam <adam@megacz.com>
Date: Sun, 22 Jul 2007 02:28:56 +0000 (+0000)
Subject: SqliteMailbox: massive overhauld
X-Git-Url: http://git.megacz.com/?p=org.ibex.mail.git;a=commitdiff_plain;h=1bd96ef617f2749c253c722d51890fcfc8b29ac4

SqliteMailbox: massive overhauld

darcs-hash:20070722022856-5007d-1f080c1fc194728431f83cc867f274934da8706f.gz
---

diff --git a/src/org/ibex/mail/SqliteMailbox.java b/src/org/ibex/mail/SqliteMailbox.java
index 66ed674..b28ef7f 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,10 +9,15 @@ 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  =
@@ -32,23 +38,129 @@ public class SqliteMailbox extends Mailbox.Default {
     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 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);
+            }
+            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();
         }
         catch (SQLException e) { throw new RuntimeException(e); }
         catch (ClassNotFoundException e) { throw new RuntimeException(e); }
     }
 
-    public int              uidNext()                    { throw new RuntimeException("not supported"); }
+    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()                   { return new SqliteJdbcIterator(); }
+    private static 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+"");
@@ -61,44 +173,86 @@ public class SqliteMailbox extends Mailbox.Default {
         } 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();
+            } 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");