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) {
16 String p = file.getAbsolutePath();
17 if (p.endsWith(".sqlite")) p = p.substring(0, p.length()-".sqlite".length());
18 return FileBasedMailbox.getFileBasedMailbox(p+"/"+name, create);
20 public String[] children() { return new String[0]; }
21 public void rmdir(String subdir) { throw new RuntimeException("invalid"); }
22 public void rename(String subdir, MailTree newParent, String newName) { throw new RuntimeException("invalid"); }
23 public Mailbox getMailbox() { return this; }
26 private Connection conn;
27 private static final String columns =
28 " messageid_, from_,to_,date_,subject_,headers_,body_,flags_";
29 private static final String[] indexedColumns = new String[] {
42 * from http://www.sqlite.org/autoinc.html
43 * "If a column has the type INTEGER PRIMARY KEY AUTOINCREMENT
44 * then a slightly different ROWID selection algorithm is
45 * used. The ROWID chosen for the new row is one larger than the
46 * largest ROWID that has ever before existed in that same
47 * table. If the table has never before contained any data, then
48 * a ROWID of 1 is used. If the table has previously held a row
49 * with the largest possible ROWID, then new INSERTs are not
50 * allowed and any attempt to insert a new row will fail with an
53 private static final String columns_ =
54 "uid_ INTEGER PRIMARY KEY AUTOINCREMENT, messageid_,from_,to_,date_,subject_,headers_,body_,flags_";
56 private final int uidValidity;
57 private final File file;
58 private final SqliteDB db;
60 public int uidValidity() { return uidValidity; }
62 public String toString() { return file.getName(); }
63 public SqliteMailbox(String filename) throws SQLException {
66 this.file = new File(filename);
67 this.db = new SqliteDB(filename);
68 this.db.setFastButDangerous(false); // actually changes from FULL to ON
70 Class.forName("org.sqlite.JDBC");
71 conn = DriverManager.getConnection("jdbc:sqlite:"+filename);
73 conn = db.getConnection();
74 conn.prepareStatement("create table if not exists uidvalidity (uidvalidity)").executeUpdate();
76 rs = conn.prepareStatement("select uidvalidity from uidvalidity").executeQuery();
78 this.uidValidity = new Random().nextInt();
79 PreparedStatement ps = conn.prepareStatement("insert into uidvalidity (uidvalidity) values (?)");
80 ps.setInt(1, uidValidity);
83 this.uidValidity = rs.getInt(1);
85 conn.prepareStatement("create table if not exists 'mail' ("+columns_+")").executeUpdate();
86 for(String name : indexedColumns)
87 conn.prepareStatement("create index if not exists "+name+"index on mail("+name+");").executeUpdate();
88 } finally { db.close(rs); }
90 catch (SQLException e) { throw new RuntimeException(e); }
91 //catch (ClassNotFoundException e) { throw new RuntimeException(e); }
94 private HashMap<Integer,Integer> imapToUid = new HashMap<Integer,Integer>();
95 private HashMap<Integer,Integer> uidToImap = new HashMap<Integer,Integer>();
96 private boolean imapNumberCacheValid = false;
97 public void updateImapNumberCache() throws SQLException {
99 Log.warn(this+"", "rebuilding imapNumberCache...");
102 PreparedStatement q = conn.prepareStatement("select uid_ from mail");
103 ResultSet rs = q.executeQuery();
107 imapToUid.put(num, rs.getInt(1));
108 uidToImap.put(rs.getInt(1), num);
111 imapNumberCacheValid = true;
112 } finally { db.close(rs); }
115 public int queryImapNumberCache(int uid) throws SQLException {
117 if (!imapNumberCacheValid) updateImapNumberCache();
118 Integer ret = uidToImap.get(uid);
119 if (ret == null) return -1;
123 public int queryUidForImapNum(int imapNumber) throws SQLException {
125 if (!imapNumberCacheValid) updateImapNumberCache();
126 Integer ret = imapToUid.get(imapNumber);
127 if (ret == null) return -1;
133 public int maxuid() { return uidNext(); }
134 public int uidNext() {
136 PreparedStatement q = conn.prepareStatement("select max(uid_) from mail");
137 ResultSet rs = q.executeQuery();
138 //if (!rs.next()) return -1;
140 if (!rs.next()) throw new RuntimeException("select max(uid_) returned no rows!");
141 return rs.getInt(1)+1;
142 } finally { db.close(rs); }
143 } catch (Exception e) { throw new RuntimeException(e); }
146 public Mailbox.Iterator iterator() {
147 Log.warn(this, "performance warning: called iterator() on entire mailbox");
148 Log.printStackTrace(this, Log.WARN);
149 return new SqliteJdbcIterator();
151 private String set(int[] set, String arg) {
152 String whereClause = "";
153 boolean needsOr = false;
154 for(int i=0; i<set.length; i+=2) {
155 if (needsOr) whereClause += " or ";
157 whereClause += arg+">=" + set[i];
158 whereClause += " and ";
159 while(i+2 < set.length && set[i+2] == (set[i+1]+1)) i += 2;
160 whereClause += arg+"<=" + set[i+1];
166 private String getWhereClause(Query q) throws UnsupportedQueryException {
169 case Query.NOT: return "not ("+getWhereClause(q.q[0])+")";
170 case Query.AND: op = "and";
171 case Query.OR: op = "or";
174 StringBuffer sb = new StringBuffer();
175 for(int i=0; i<q.q.length; i++) {
176 if (add) sb.append(" " + op);
178 sb.append(getWhereClause(q.q[i]));
182 return sb.toString();
184 case Query.ALL: return "1=1";
185 case Query.UID: return set(q.set, "uid_");
186 case Query.DELETED: return "((flags_ & "+(Mailbox.Flag.DELETED)+")!=0)";
187 case Query.SEEN: return "((flags_ & "+(Mailbox.Flag.SEEN)+")!=0)";
188 case Query.FLAGGED: return "((flags_ & "+(Mailbox.Flag.FLAGGED)+")!=0)";
189 case Query.DRAFT: return "((flags_ & "+(Mailbox.Flag.DRAFT)+")!=0)";
190 case Query.ANSWERED: return "((flags_ & "+(Mailbox.Flag.ANSWERED)+")!=0)";
191 case Query.RECENT: return "((flags_ & "+(Mailbox.Flag.RECENT)+")!=0)";
193 public static final int SENT = 5;
194 public static final int ARRIVAL = 6;
195 public static final int HEADER = 7;
196 public static final int SIZE = 8;
197 public static final int BODY = 9;
198 public static final int FULL = 10;
199 public static final int IMAPNUM = 11;
202 case Query.IMAPNUM: {
204 // translate queries in terms of imap numbers into queries in terms of uids
205 // RELIES ON THE FACT THAT UIDS ARE MONOTONICALLY INCREASING
206 int[] set = new int[q.set==null ? 2 : q.set.length];
207 if (q.set==null) { set[0] = q.min; set[1] = q.max; }
208 else System.arraycopy(q.set, 0, set, 0, q.set.length);
209 for(int i=0; i<set.length; i++) {
210 int uid = queryUidForImapNum(set[i]);
212 Log.info(SqliteMailbox.class, "PROBLEM => resorting to superclass: " + q);
213 throw new UnsupportedQueryException();
217 return getWhereClause(Query.uid(set));
218 } catch (SQLException e) {
220 Log.info(SqliteMailbox.class, "resorting to superclass: " + q);
221 throw new UnsupportedQueryException();
226 Log.info(SqliteMailbox.class, "resorting to superclass: " + q);
227 throw new UnsupportedQueryException();
231 private static class UnsupportedQueryException extends Exception { }
232 public Mailbox.Iterator iterator(Query q) {
234 String whereClause = getWhereClause(q);
235 Log.info(this, "whereClause = " + whereClause);
236 return new SqliteJdbcIterator("where "+whereClause+";");
237 } catch (UnsupportedQueryException _) {
238 return super.iterator(q);
241 public int count(Query q) {
243 String whereClause = getWhereClause(q);
244 Log.info(this, "whereClause = " + whereClause);
246 Log.warn("SQL", "select count(*) from mail where " + whereClause);
247 ResultSet rs = conn.prepareStatement("select count(*) from mail where " + whereClause).executeQuery();
251 } finally { db.close(rs); }
252 } catch (Exception e) { throw new RuntimeException(e); }
253 } catch (UnsupportedQueryException _) {
254 return super.count(q);
257 public synchronized void insert(Message m, int flags) {
261 PreparedStatement query = conn.prepareStatement("select headers_,body_,flags_ from 'mail' where messageid_=?");
262 query.setString(1, m.messageid);
263 Log.warn("SQL", "select headers_,body_,flags_ from 'mail' where messageid_="+m.messageid);
264 ResultSet rs2 = query.executeQuery();
266 Message m2 = Message.newMessage(Fountain.Util.concat(Fountain.Util.create(rs2.getString(1)),
267 Fountain.Util.create("\r\n\r\n"),
268 Fountain.Util.create(rs2.getString(2))));
269 StringBuffer s1 = new StringBuffer();
270 m.getBody().getStream().transcribe(s1);
271 StringBuffer s2 = new StringBuffer();
272 m2.getBody().getStream().transcribe(s2);
273 if (!s1.toString().equals(s2.toString())) {
274 Log.error(this.toString(),
275 "attempt to insert two messages with identical messageid ("+m.messageid+") but different bodies:\n"+
276 " (body length="+s1.length()+") "+m.summary()+"\n"+
277 " (body length="+s2.length()+") "+m2.summary()+"\n");
279 Log.warn(this.toString(),
280 "silently dropping duplicate insert() [messageids and bodies match]: " + m.summary());
285 PreparedStatement add =
286 conn.prepareStatement("insert "+/*"or replace "+*/"into 'mail' ("+columns+") values (?,?,?,?,?,?,?,?)");
287 add.setString(1, m.messageid+"");
288 add.setString(2, m.from+"");
289 add.setString(3, m.to+"");
290 add.setString(4, m.date+"");
291 add.setString(5, m.subject+"");
292 add.setString(6, SqliteDB.streamToString(m.headers.getStream()));
293 add.setString(7, SqliteDB.streamToString(m.getBody().getStream()));
294 add.setInt (8, flags);
297 // FIXME: be smarter here?
298 imapNumberCacheValid = false;
299 } catch (Exception e) { throw new RuntimeException(e); }
302 private class SqliteJdbcIterator implements Mailbox.Iterator {
303 // could be more efficient in a ton of ways
304 private ResultSet rs;
305 private int count = 0;
307 private Message m = null;
308 private int uid = -1;
309 private String whereClause;
310 public SqliteJdbcIterator() { this(""); }
311 public SqliteJdbcIterator(String whereClause) {
314 if (whereClause.equals(""))
315 Log.warn(this, "performance warning: empty whereClause");
317 this.whereClause = whereClause;
318 Log.warn("SQL", "select messageid_,uid_,flags_ from 'mail' "+whereClause);
319 PreparedStatement query = conn.prepareStatement("select messageid_,uid_,flags_ from 'mail' "+whereClause);
320 rs = query.executeQuery();
321 } catch (Exception e) { throw new RuntimeException(e); }
323 public Headers head() {
324 if (m != null) return m.headers;
326 PreparedStatement query = conn.prepareStatement("select headers_,flags_ from 'mail' where uid_=?");
327 query.setString(1, rs.getString(2));
328 Log.warn("SQL", "select headers_,flags_ from 'mail' where uid_="+rs.getString(2));
330 ResultSet rs2 = query.executeQuery();
331 if (!rs2.next()) { Log.error("XXX", "should not happen"); return null; }
332 flags = rs2.getInt(2);
333 return new Headers(Fountain.Util.create(rs2.getString(1)));
334 } catch (Exception e) { throw new RuntimeException(e); }
336 public Message cur() {
338 if (m!=null) return m;
339 PreparedStatement query = conn.prepareStatement("select headers_,body_,flags_ from 'mail' where uid_=?");
340 query.setString(1, rs.getString(2));
341 Log.warn("SQL", "select headers_,body_,flags_ from 'mail' where uid_="+rs.getString(2));
343 ResultSet rs2 = query.executeQuery();
344 if (!rs2.next()) { Log.error("XXX", "should not happen"); return null; }
345 m = Message.newMessage(Fountain.Util.concat(Fountain.Util.create(rs2.getString(1)),
346 Fountain.Util.create("\r\n\r\n"),
347 Fountain.Util.create(rs2.getString(2))));
348 flags = rs2.getInt(3);
350 } catch (Exception e) { throw new RuntimeException(e); }
352 public int getFlags() {
353 try { return rs.getInt("flags_"); } catch (Exception e) { throw new RuntimeException(e); }
355 public void setFlags(int flags) {
357 int oldflags = rs.getInt("flags_");
358 if (oldflags==flags) return;
359 Log.info(this, "setflags (old="+oldflags+")" + "update mail set flags_="+(flags)+" where uid_="+uid()+"");
360 if ((flags & Mailbox.Flag.DELETED) != 0) Log.printStackTrace("deletion", Log.WARN);
361 PreparedStatement update = conn.prepareStatement("update mail set flags_=? where uid_=?");
362 update.setInt(1, flags);
363 update.setInt(2, uid());
364 update.executeUpdate();
365 } catch (Exception e) { throw new RuntimeException(e); }
367 public boolean next() {
368 try { m = null; uid = -1; count++;
369 boolean ret = rs.next();
371 } catch (Exception e) { throw new RuntimeException(e); } }
374 try { uid = rs.getInt("uid_"); } catch (Exception e) { throw new RuntimeException(e); }
377 public int imapNumber() {
378 if ("".equals(whereClause)) return count;
379 try { return queryImapNumberCache(uid()); } catch (SQLException s) { throw new RuntimeException(s); }
381 public int nntpNumber() { return uid(); }
382 public void delete() {
384 Log.error("sqlite", "actually deleting message "+uid()+" "+head().get("subject"));
385 Log.printStackTrace("sqlite", Log.ERROR);
387 PreparedStatement update = conn.prepareStatement("delete from mail where uid_=?");
388 update.setInt(1, uid());
389 update.executeUpdate();
391 // FIXME: be smarter here?
392 imapNumberCacheValid = false;
394 } catch (Exception e) { throw new RuntimeException(e); }