3 import org.ibex.io.Fountain;
4 import org.ibex.io.Stream;
5 import java.sql.Timestamp;
11 // nntpNumber (column)
14 public class SqliteMailbox extends Mailbox.Default {
16 private Connection conn;
17 private static final String columns =
18 " messageid_, from_,to_,date_,subject_,headers_,body_,flags_";
21 * from http://www.sqlite.org/autoinc.html
22 * "If a column has the type INTEGER PRIMARY KEY AUTOINCREMENT
23 * then a slightly different ROWID selection algorithm is
24 * used. The ROWID chosen for the new row is one larger than the
25 * largest ROWID that has ever before existed in that same
26 * table. If the table has never before contained any data, then
27 * a ROWID of 1 is used. If the table has previously held a row
28 * with the largest possible ROWID, then new INSERTs are not
29 * allowed and any attempt to insert a new row will fail with an
32 private static final String columns_ =
33 "uid_ INTEGER PRIMARY KEY AUTOINCREMENT, messageid_ unique,from_,to_,date_,subject_,headers_,body_,flags_";
35 public SqliteMailbox(String filename) {
37 Class.forName("org.sqlite.JDBC");
38 conn = DriverManager.getConnection("jdbc:sqlite:"+filename);
39 conn.prepareStatement("create virtual table if not exists 'mail' using FTS2("+columns_+")").executeUpdate();
41 catch (SQLException e) { throw new RuntimeException(e); }
42 catch (ClassNotFoundException e) { throw new RuntimeException(e); }
45 public int uidNext() { throw new RuntimeException("not supported"); }
46 public Mailbox.Iterator iterator() { return new SqliteJdbcIterator(); }
47 public void insert(Message m, int flags) {
50 PreparedStatement add =
51 conn.prepareStatement("insert into 'mail' ("+columns+") values (?,?,?,?,?,?,?,?)");
52 add.setString(1, m.messageid+"");
53 add.setString(2, m.from+"");
54 add.setString(3, m.to+"");
55 add.setString(4, m.date+"");
56 add.setString(5, m.subject+"");
57 add.setString(6, streamToString(m.headers.getStream()));
58 add.setString(7, streamToString(m.getBody().getStream()));
59 add.setInt (8, flags);
61 } catch (Exception e) { throw new RuntimeException(e); }
64 private class SqliteJdbcIterator extends Mailbox.Default.Iterator {
65 // could be more efficient in a ton of ways
67 private int count = 1;
69 private Message m = null;
70 public SqliteJdbcIterator() {
72 PreparedStatement query = conn.prepareStatement("select messageid_ from 'mail'");
73 rs = query.executeQuery();
75 } catch (Exception e) { throw new RuntimeException(e); }
77 public Message cur() {
79 if (m!=null) return m;
81 PreparedStatement query = conn.prepareStatement("select headers_,body_,flags_ from 'mail' where messageid_=?");
82 query.setString(1, rs.getString(1));
83 ResultSet rs2 = query.executeQuery();
84 if (!rs.next()) return null;
85 m = Message.newMessage(Fountain.Util.concat(Fountain.Util.create(rs.getString(1)),
86 Fountain.Util.create("\r\n\r\n"),
87 Fountain.Util.create(rs.getString(2))));
90 } catch (Exception e) { throw new RuntimeException(e); }
92 public int getFlags() { if (m==null) /* could be more efficient */ cur(); return flags; }
93 public Headers head() { return cur().headers; }
94 public boolean next() { try { m = null; count++; return rs.next(); } catch (Exception e) { throw new RuntimeException(e); } }
95 public int uid() { throw new RuntimeException("not supported"); }
96 public int imapNumber() { return count; }
97 public int nntpNumber() { throw new RuntimeException("not supported"); }
98 public void delete() { throw new RuntimeException("not supported"); }
101 private static String streamToString(Stream stream) throws Exception {
102 StringBuffer b = new StringBuffer();
103 for(String s = stream.readln(); s!=null; s=stream.readln())