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_";
27 * from http://www.sqlite.org/autoinc.html
28 * "If a column has the type INTEGER PRIMARY KEY AUTOINCREMENT
29 * then a slightly different ROWID selection algorithm is
30 * used. The ROWID chosen for the new row is one larger than the
31 * largest ROWID that has ever before existed in that same
32 * table. If the table has never before contained any data, then
33 * a ROWID of 1 is used. If the table has previously held a row
34 * with the largest possible ROWID, then new INSERTs are not
35 * allowed and any attempt to insert a new row will fail with an
38 private static final String columns_ =
39 "uid_ INTEGER PRIMARY KEY AUTOINCREMENT, messageid_ unique,from_,to_,date_,subject_,headers_,body_,flags_";
41 private final int uidValidity;
42 private final File file;
43 public int uidValidity() { return uidValidity; }
45 public SqliteMailbox(String filename) throws SQLException {
47 this.file = new File(filename);
48 Class.forName("org.sqlite.JDBC");
49 conn = DriverManager.getConnection("jdbc:sqlite:"+filename);
50 conn.prepareStatement("create table if not exists uidvalidity (uidvalidity)").executeUpdate();
51 ResultSet rs = conn.prepareStatement("select uidvalidity from uidvalidity").executeQuery();
53 this.uidValidity = new Random().nextInt();
54 PreparedStatement ps = conn.prepareStatement("insert into uidvalidity (uidvalidity) values (?)");
55 ps.setInt(1, uidValidity);
58 this.uidValidity = rs.getInt(1);
61 //conn.prepareStatement("create virtual table 'mail' using FTS2("+columns_+")").executeUpdate();
62 conn.prepareStatement("create table 'mail' ("+columns_+")").executeUpdate();
63 } catch (SQLException e) {
66 conn.prepareStatement("create index if not exists uid_index on mail(uid_);").executeUpdate();
68 catch (SQLException e) { throw new RuntimeException(e); }
69 catch (ClassNotFoundException e) { throw new RuntimeException(e); }
72 public int uidNext() {
74 PreparedStatement q = conn.prepareStatement("select max(uid_) from mail");
75 ResultSet rs = q.executeQuery();
76 if (!rs.next()) return -1;
77 return rs.getInt(1)+1;
78 } catch (Exception e) { throw new RuntimeException(e); }
81 public Mailbox.Iterator iterator() { return new SqliteJdbcIterator(); }
82 private static String set(int[] set, String arg) {
83 String whereClause = "";
84 boolean needsOr = false;
85 for(int i=0; i<set.length; i+=2) {
86 if (needsOr) whereClause += " or ";
88 whereClause += arg+">=" + set[i];
89 whereClause += " and ";
90 while(i+2 < set.length && set[i+2] == (set[i+1]+1)) i += 2;
91 whereClause += arg+"<=" + set[i+1];
97 private static String joinWith(String op, Query[] q) throws UnsupportedQueryException {
99 StringBuffer sb = new StringBuffer();
100 for(int i=0; i<q.length; i++) {
101 if (add) sb.append(" " + op);
103 sb.append(getWhereClause(q[i]));
107 return sb.toString();
109 private static String getWhereClause(Query q) throws UnsupportedQueryException {
111 case Query.NOT: return "not ("+getWhereClause(q.q[0])+")";
112 case Query.AND: return joinWith("and", q.q);
113 case Query.OR: return joinWith("or", q.q);
114 case Query.ALL: return "1=1";
115 case Query.UID: return set(q.set, "uid_");
116 case Query.DELETED: return "((flags_ & "+(Mailbox.Flag.DELETED)+")!=0)";
117 case Query.SEEN: return "((flags_ & "+(Mailbox.Flag.SEEN)+")!=0)";
118 case Query.FLAGGED: return "((flags_ & "+(Mailbox.Flag.FLAGGED)+")!=0)";
119 case Query.DRAFT: return "((flags_ & "+(Mailbox.Flag.DRAFT)+")!=0)";
120 case Query.ANSWERED: return "((flags_ & "+(Mailbox.Flag.ANSWERED)+")!=0)";
121 case Query.RECENT: return "((flags_ & "+(Mailbox.Flag.RECENT)+")!=0)";
123 public static final int SENT = 5;
124 public static final int ARRIVAL = 6;
125 public static final int HEADER = 7;
126 public static final int SIZE = 8;
127 public static final int BODY = 9;
128 public static final int FULL = 10;
129 public static final int IMAPNUM = 11;
132 Log.info(SqliteMailbox.class, "resorting to superclass: " + q.type);
133 throw new UnsupportedQueryException();
137 private static class UnsupportedQueryException extends Exception { }
138 public Mailbox.Iterator iterator(Query q) {
140 String whereClause = getWhereClause(q);
141 Log.info(this, "whereClause = " + whereClause);
142 return new SqliteJdbcIterator("where "+whereClause+";");
143 } catch (UnsupportedQueryException _) {
144 return super.iterator(q);
147 public int count(Query q) {
149 String whereClause = getWhereClause(q);
150 Log.info(this, "whereClause = " + whereClause);
152 ResultSet rs = conn.prepareStatement("select count(*) from mail where " + whereClause).executeQuery();
155 } catch (Exception e) { throw new RuntimeException(e); }
156 } catch (UnsupportedQueryException _) {
157 return super.count(q);
160 public void insert(Message m, int flags) {
162 PreparedStatement add =
163 conn.prepareStatement("insert or replace into 'mail' ("+columns+") values (?,?,?,?,?,?,?,?)");
164 add.setString(1, m.messageid+"");
165 add.setString(2, m.from+"");
166 add.setString(3, m.to+"");
167 add.setString(4, m.date+"");
168 add.setString(5, m.subject+"");
169 add.setString(6, streamToString(m.headers.getStream()));
170 add.setString(7, streamToString(m.getBody().getStream()));
171 add.setInt (8, flags);
173 } catch (Exception e) { throw new RuntimeException(e); }
176 private class SqliteJdbcIterator implements Mailbox.Iterator {
177 // could be more efficient in a ton of ways
178 private ResultSet rs;
179 private int count = 0;
181 private Message m = null;
182 private int uid = -1;
183 private String whereClause;
184 public SqliteJdbcIterator() { this(""); }
185 public SqliteJdbcIterator(String whereClause) {
187 this.whereClause = whereClause;
188 PreparedStatement query = conn.prepareStatement("select messageid_,uid_,flags_ from 'mail' "+whereClause);
189 rs = query.executeQuery();
190 } catch (Exception e) { throw new RuntimeException(e); }
192 public Message cur() {
194 if (m!=null) return m;
195 PreparedStatement query = conn.prepareStatement("select headers_,body_,flags_ from 'mail' where messageid_=?");
196 query.setString(1, rs.getString(1));
198 ResultSet rs2 = query.executeQuery();
200 Log.error("XXX", "should not happen");
203 m = Message.newMessage(Fountain.Util.concat(Fountain.Util.create(rs2.getString(1)),
204 Fountain.Util.create("\r\n\r\n"),
205 Fountain.Util.create(rs2.getString(2))));
206 flags = rs2.getInt(3);
209 } catch (Exception e) { throw new RuntimeException(e); }
211 public int getFlags() {
212 try { return rs.getInt("flags_"); } catch (Exception e) { throw new RuntimeException(e); }
214 public void setFlags(int flags) {
216 int oldflags = rs.getInt("flags_");
217 if (oldflags==flags) return;
218 Log.info(this, "setflags (old="+oldflags+")" + "update mail set flags_="+(flags)+" where uid_="+uid()+"");
219 PreparedStatement update = conn.prepareStatement("update mail set flags_=? where uid_=?");
220 update.setInt(1, flags);
221 update.setInt(2, uid());
222 update.executeUpdate();
223 } catch (Exception e) { throw new RuntimeException(e); }
225 public Headers head() { return cur().headers; }
226 public boolean next() {
227 try { m = null; uid = -1; count++;
228 boolean ret = rs.next();
230 } catch (Exception e) { throw new RuntimeException(e); } }
233 try { uid = rs.getInt("uid_"); } catch (Exception e) { throw new RuntimeException(e); }
236 public int imapNumber() {
237 if ("".equals(whereClause)) return count;
239 ResultSet rs = conn.prepareStatement("select count(*) from mail where uid_ <= " + uid()).executeQuery();
242 } catch (Exception e) { throw new RuntimeException(e); }
244 public int nntpNumber() { return uid(); }
245 public void delete() {
247 PreparedStatement update = conn.prepareStatement("delete from mail where uid_=?");
248 update.setInt(1, uid());
249 update.executeUpdate();
250 } catch (Exception e) { throw new RuntimeException(e); }
254 private static String streamToString(Stream stream) throws Exception {
256 StringBuffer b = new StringBuffer();
257 for(String s = stream.readln(); s!=null; s=stream.readln())