3 import org.ibex.util.*;
4 import org.ibex.io.Fountain;
5 import org.ibex.io.Stream;
6 import java.sql.Timestamp;
13 public class SqliteMailbox extends Mailbox.Default implements MailTree {
15 public MailTree slash(String name, boolean create) { return null; }
16 public String[] children() { return new String[0]; }
17 public void rmdir(String subdir) { throw new RuntimeException("invalid"); }
18 public void rename(String subdir, MailTree newParent, String newName) { throw new RuntimeException("invalid"); }
19 public Mailbox getMailbox() { return this; }
22 private Connection conn;
23 private static final String columns =
24 " messageid_, from_,to_,date_,subject_,headers_,body_,flags_";
25 private static final String[] indexedColumns = new String[] {
38 * from http://www.sqlite.org/autoinc.html
39 * "If a column has the type INTEGER PRIMARY KEY AUTOINCREMENT
40 * then a slightly different ROWID selection algorithm is
41 * used. The ROWID chosen for the new row is one larger than the
42 * largest ROWID that has ever before existed in that same
43 * table. If the table has never before contained any data, then
44 * a ROWID of 1 is used. If the table has previously held a row
45 * with the largest possible ROWID, then new INSERTs are not
46 * allowed and any attempt to insert a new row will fail with an
49 // FIXME: should messageid_ be decared unique?
50 private static final String columns_ =
51 "uid_ INTEGER PRIMARY KEY AUTOINCREMENT, messageid_ unique,from_,to_,date_,subject_,headers_,body_,flags_";
53 private final int uidValidity;
54 private final File file;
55 public int uidValidity() { return uidValidity; }
57 public String toString() { return file.getName(); }
58 public SqliteMailbox(String filename) throws SQLException {
60 this.file = new File(filename);
61 Class.forName("org.sqlite.JDBC");
62 conn = DriverManager.getConnection("jdbc:sqlite:"+filename);
63 conn.prepareStatement("create table if not exists uidvalidity (uidvalidity)").executeUpdate();
64 ResultSet rs = conn.prepareStatement("select uidvalidity from uidvalidity").executeQuery();
66 this.uidValidity = new Random().nextInt();
67 PreparedStatement ps = conn.prepareStatement("insert into uidvalidity (uidvalidity) values (?)");
68 ps.setInt(1, uidValidity);
71 this.uidValidity = rs.getInt(1);
73 conn.prepareStatement("create table if not exists 'mail' ("+columns_+")").executeUpdate();
74 for(String name : indexedColumns)
75 conn.prepareStatement("create index if not exists "+name+"index on mail("+name+");").executeUpdate();
77 catch (SQLException e) { throw new RuntimeException(e); }
78 catch (ClassNotFoundException e) { throw new RuntimeException(e); }
81 private HashMap<Integer,Integer> imapToUid = new HashMap<Integer,Integer>();
82 private HashMap<Integer,Integer> uidToImap = new HashMap<Integer,Integer>();
83 private boolean imapNumberCacheValid = false;
84 public void updateImapNumberCache() throws SQLException {
86 Log.warn(this+"", "rebuilding imapNumberCache...");
89 PreparedStatement q = conn.prepareStatement("select uid_ from mail");
90 ResultSet rs = q.executeQuery();
93 imapToUid.put(num, rs.getInt(1));
94 uidToImap.put(rs.getInt(1), num);
97 imapNumberCacheValid = true;
100 public int queryImapNumberCache(int uid) throws SQLException {
102 if (!imapNumberCacheValid) updateImapNumberCache();
103 Integer ret = uidToImap.get(uid);
104 if (ret == null) return -1;
108 public int queryUidForImapNum(int imapNumber) throws SQLException {
110 if (!imapNumberCacheValid) updateImapNumberCache();
111 Integer ret = imapToUid.get(imapNumber);
112 if (ret == null) return -1;
118 public int maxuid() { return uidNext(); }
119 public int uidNext() {
121 PreparedStatement q = conn.prepareStatement("select max(uid_) from mail");
122 ResultSet rs = q.executeQuery();
123 if (!rs.next()) return -1;
124 return rs.getInt(1)+1;
125 } catch (Exception e) { throw new RuntimeException(e); }
128 public Mailbox.Iterator iterator() {
129 Log.warn(this, "performance warning: called iterator() on entire mailbox");
130 Log.printStackTrace(this, Log.WARN);
131 return new SqliteJdbcIterator();
133 private String set(int[] set, String arg) {
134 String whereClause = "";
135 boolean needsOr = false;
136 for(int i=0; i<set.length; i+=2) {
137 if (needsOr) whereClause += " or ";
139 whereClause += arg+">=" + set[i];
140 whereClause += " and ";
141 while(i+2 < set.length && set[i+2] == (set[i+1]+1)) i += 2;
142 whereClause += arg+"<=" + set[i+1];
148 private static String joinWith(String op, Query[] q) throws UnsupportedQueryException {
150 StringBuffer sb = new StringBuffer();
151 for(int i=0; i<q.length; i++) {
152 if (add) sb.append(" " + op);
154 sb.append(getWhereClause(q[i]));
158 return sb.toString();
160 private static String getWhereClause(Query q) throws UnsupportedQueryException {
162 case Query.NOT: return "not ("+getWhereClause(q.q[0])+")";
163 case Query.AND: return joinWith("and", q.q);
164 case Query.OR: return joinWith("or", q.q);
165 case Query.ALL: return "1=1";
166 case Query.UID: return set(q.set, "uid_");
167 case Query.DELETED: return "((flags_ & "+(Mailbox.Flag.DELETED)+")!=0)";
168 case Query.SEEN: return "((flags_ & "+(Mailbox.Flag.SEEN)+")!=0)";
169 case Query.FLAGGED: return "((flags_ & "+(Mailbox.Flag.FLAGGED)+")!=0)";
170 case Query.DRAFT: return "((flags_ & "+(Mailbox.Flag.DRAFT)+")!=0)";
171 case Query.ANSWERED: return "((flags_ & "+(Mailbox.Flag.ANSWERED)+")!=0)";
172 case Query.RECENT: return "((flags_ & "+(Mailbox.Flag.RECENT)+")!=0)";
174 public static final int SENT = 5;
175 public static final int ARRIVAL = 6;
176 public static final int HEADER = 7;
177 public static final int SIZE = 8;
178 public static final int BODY = 9;
179 public static final int FULL = 10;
180 public static final int IMAPNUM = 11;
183 Log.info(SqliteMailbox.class, "resorting to superclass: " + q.type);
184 throw new UnsupportedQueryException();
188 private static class UnsupportedQueryException extends Exception { }
189 public Mailbox.Iterator iterator(Query q) {
191 String whereClause = getWhereClause(q);
192 Log.info(this, "whereClause = " + whereClause);
193 return new SqliteJdbcIterator("where "+whereClause+";");
194 } catch (UnsupportedQueryException _) {
195 return super.iterator(q);
198 public int count(Query q) {
200 String whereClause = getWhereClause(q);
201 Log.info(this, "whereClause = " + whereClause);
203 ResultSet rs = conn.prepareStatement("select count(*) from mail where " + whereClause).executeQuery();
206 } catch (Exception e) { throw new RuntimeException(e); }
207 } catch (UnsupportedQueryException _) {
208 return super.count(q);
211 public void insert(Message m, int flags) {
213 PreparedStatement add =
214 conn.prepareStatement("insert or replace into 'mail' ("+columns+") values (?,?,?,?,?,?,?,?)");
215 add.setString(1, m.messageid+"");
216 add.setString(2, m.from+"");
217 add.setString(3, m.to+"");
218 add.setString(4, m.date+"");
219 add.setString(5, m.subject+"");
220 add.setString(6, streamToString(m.headers.getStream()));
221 add.setString(7, streamToString(m.getBody().getStream()));
222 add.setInt (8, flags);
225 // FIXME: be smarter here?
226 imapNumberCacheValid = false;
227 } catch (Exception e) { throw new RuntimeException(e); }
230 private class SqliteJdbcIterator implements Mailbox.Iterator {
231 // could be more efficient in a ton of ways
232 private ResultSet rs;
233 private int count = 0;
235 private Message m = null;
236 private int uid = -1;
237 private String whereClause;
238 public SqliteJdbcIterator() { this(""); }
239 public SqliteJdbcIterator(String whereClause) {
241 this.whereClause = whereClause;
242 PreparedStatement query = conn.prepareStatement("select messageid_,uid_,flags_ from 'mail' "+whereClause);
243 rs = query.executeQuery();
244 } catch (Exception e) { throw new RuntimeException(e); }
246 public Message cur() {
248 if (m!=null) return m;
249 PreparedStatement query = conn.prepareStatement("select headers_,body_,flags_ from 'mail' where messageid_=?");
250 query.setString(1, rs.getString(1));
252 ResultSet rs2 = query.executeQuery();
253 if (!rs2.next()) { Log.error("XXX", "should not happen"); return null; }
254 m = Message.newMessage(Fountain.Util.concat(Fountain.Util.create(rs2.getString(1)),
255 Fountain.Util.create("\r\n\r\n"),
256 Fountain.Util.create(rs2.getString(2))));
257 flags = rs2.getInt(3);
259 } catch (Exception e) { throw new RuntimeException(e); }
261 public int getFlags() {
262 try { return rs.getInt("flags_"); } catch (Exception e) { throw new RuntimeException(e); }
264 public void setFlags(int flags) {
266 int oldflags = rs.getInt("flags_");
267 if (oldflags==flags) return;
268 Log.info(this, "setflags (old="+oldflags+")" + "update mail set flags_="+(flags)+" where uid_="+uid()+"");
269 PreparedStatement update = conn.prepareStatement("update mail set flags_=? where uid_=?");
270 update.setInt(1, flags);
271 update.setInt(2, uid());
272 update.executeUpdate();
273 } catch (Exception e) { throw new RuntimeException(e); }
275 public Headers head() { return cur().headers; }
276 public boolean next() {
277 try { m = null; uid = -1; count++;
278 boolean ret = rs.next();
280 } catch (Exception e) { throw new RuntimeException(e); } }
283 try { uid = rs.getInt("uid_"); } catch (Exception e) { throw new RuntimeException(e); }
286 public int imapNumber() {
287 if ("".equals(whereClause)) return count;
289 ResultSet rs = conn.prepareStatement("select count(*) from mail where uid_ <= " + uid()).executeQuery();
292 } catch (Exception e) { throw new RuntimeException(e); }
294 public int nntpNumber() { return uid(); }
295 public void delete() {
297 PreparedStatement update = conn.prepareStatement("delete from mail where uid_=?");
298 update.setInt(1, uid());
299 update.executeUpdate();
301 // FIXME: be smarter here?
302 imapNumberCacheValid = false;
304 } catch (Exception e) { throw new RuntimeException(e); }