From 3450e48824396ee8cda39aaf0caeb81bb5f09c8d Mon Sep 17 00:00:00 2001 From: adam Date: Sat, 1 Mar 2008 23:48:12 +0000 Subject: [PATCH] add SqliteDB.close(ResultSet) and use it darcs-hash:20080301234812-5007d-f9c61acc5cdc18b229183131cf62c0a7324e6b1c.gz --- src/org/ibex/mail/SqliteDB.java | 18 +++++++-- src/org/ibex/mail/SqliteMailbox.java | 70 +++++++++++++++++++++------------- 2 files changed, 59 insertions(+), 29 deletions(-) diff --git a/src/org/ibex/mail/SqliteDB.java b/src/org/ibex/mail/SqliteDB.java index 4b818ce..6167eec 100644 --- a/src/org/ibex/mail/SqliteDB.java +++ b/src/org/ibex/mail/SqliteDB.java @@ -38,18 +38,29 @@ public class SqliteDB { conn.prepareStatement("PRAGMA cache_size="+Math.ceil(kilobytes/1.5)+";").executeUpdate(); } + + public void close(ResultSet rs) { + if (rs==null) return; + try { + rs.close(); + } catch (SQLException s) { + Log.error(ResultSet.class, s); + } + } + public SqliteDB(String filename) { this.filename = filename; try { + Log.error("start", "initializing " + filename); Class.forName("org.sqlite.JDBC"); conn = DriverManager.getConnection("jdbc:sqlite:"+filename); conn.prepareStatement("PRAGMA auto_vacuum = 1").executeUpdate(); - conn.prepareStatement("VACUUM").executeUpdate(); + //conn.prepareStatement("VACUUM").executeUpdate(); // until we have better assurances about locking on network filesystems... conn.prepareStatement("PRAGMA locking_mode = EXCLUSIVE").executeQuery(); - conn.prepareStatement("PRAGMA temp_store = MEMORY").executeUpdate(); + //conn.prepareStatement("PRAGMA temp_store = MEMORY").executeUpdate(); conn.prepareStatement("PRAGMA page_size=4096").executeUpdate(); conn.prepareStatement("PRAGMA cache_size=2000").executeUpdate(); ResultSet rs = conn.prepareStatement("PRAGMA integrity_check").executeQuery(); @@ -57,6 +68,7 @@ public class SqliteDB { String result = rs.getString(1); if (!result.equals("ok")) throw new RuntimeException("PRAGMA integrity_check returned \""+result+"\""); + Log.error(".", "done initializing " + filename); } catch (SQLException e) { throw new RuntimeException(e); } catch (ClassNotFoundException e) { throw new RuntimeException(e); } @@ -124,7 +136,7 @@ public class SqliteDB { } static String streamToString(Stream stream) throws Exception { - // FIXME + // FIXME!!!! This is corrupting line endings!!!! StringBuffer b = new StringBuffer(); for(String s = stream.readln(); s!=null; s=stream.readln()) b.append(s+"\n"); diff --git a/src/org/ibex/mail/SqliteMailbox.java b/src/org/ibex/mail/SqliteMailbox.java index 49a2700..c6fb377 100644 --- a/src/org/ibex/mail/SqliteMailbox.java +++ b/src/org/ibex/mail/SqliteMailbox.java @@ -12,7 +12,11 @@ import java.util.*; public class SqliteMailbox extends Mailbox.Default implements MailTree { - public MailTree slash(String name, boolean create) { return null; } + public MailTree slash(String name, boolean create) { + String p = file.getAbsolutePath(); + if (p.endsWith(".sqlite")) p = p.substring(0, p.length()-".sqlite".length()); + return FileBasedMailbox.getFileBasedMailbox(p+"/"+name, create); + } 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"); } @@ -46,36 +50,44 @@ public class SqliteMailbox extends Mailbox.Default implements MailTree { * allowed and any attempt to insert a new row will fail with an * SQLITE_FULL error. */ - // FIXME: should messageid_ be decared unique? private static final String columns_ = "uid_ INTEGER PRIMARY KEY AUTOINCREMENT, messageid_,from_,to_,date_,subject_,headers_,body_,flags_"; private final int uidValidity; private final File file; + private final SqliteDB db; + public int uidValidity() { return uidValidity; } public String toString() { return file.getName(); } public SqliteMailbox(String filename) throws SQLException { + ResultSet rs = null; try { this.file = new File(filename); + this.db = new SqliteDB(filename); + /* Class.forName("org.sqlite.JDBC"); conn = DriverManager.getConnection("jdbc:sqlite:"+filename); + */ + conn = db.getConnection(); 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); - } - conn.prepareStatement("create table if not exists 'mail' ("+columns_+")").executeUpdate(); - for(String name : indexedColumns) - conn.prepareStatement("create index if not exists "+name+"index on mail("+name+");").executeUpdate(); + try { + 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); + } + conn.prepareStatement("create table if not exists 'mail' ("+columns_+")").executeUpdate(); + for(String name : indexedColumns) + conn.prepareStatement("create index if not exists "+name+"index on mail("+name+");").executeUpdate(); + } finally { db.close(rs); } } catch (SQLException e) { throw new RuntimeException(e); } - catch (ClassNotFoundException e) { throw new RuntimeException(e); } + //catch (ClassNotFoundException e) { throw new RuntimeException(e); } } private HashMap imapToUid = new HashMap(); @@ -88,13 +100,15 @@ public class SqliteMailbox extends Mailbox.Default implements MailTree { uidToImap.clear(); PreparedStatement q = conn.prepareStatement("select uid_ from mail"); ResultSet rs = q.executeQuery(); - int num = 1; - while(rs.next()) { - imapToUid.put(num, rs.getInt(1)); - uidToImap.put(rs.getInt(1), num); - num++; - } - imapNumberCacheValid = true; + try { + int num = 1; + while(rs.next()) { + imapToUid.put(num, rs.getInt(1)); + uidToImap.put(rs.getInt(1), num); + num++; + } + imapNumberCacheValid = true; + } finally { db.close(rs); } } } public int queryImapNumberCache(int uid) throws SQLException { @@ -121,8 +135,10 @@ public class SqliteMailbox extends Mailbox.Default implements MailTree { PreparedStatement q = conn.prepareStatement("select max(uid_) from mail"); ResultSet rs = q.executeQuery(); //if (!rs.next()) return -1; - if (!rs.next()) throw new RuntimeException("select max(uid_) returned no rows!"); - return rs.getInt(1)+1; + try { + if (!rs.next()) throw new RuntimeException("select max(uid_) returned no rows!"); + return rs.getInt(1)+1; + } finally { db.close(rs); } } catch (Exception e) { throw new RuntimeException(e); } } @@ -228,8 +244,10 @@ public class SqliteMailbox extends Mailbox.Default implements MailTree { try { Log.warn("SQL", "select count(*) from mail where " + whereClause); ResultSet rs = conn.prepareStatement("select count(*) from mail where " + whereClause).executeQuery(); - rs.next(); - return rs.getInt(1); + try { + rs.next(); + return rs.getInt(1); + } finally { db.close(rs); } } catch (Exception e) { throw new RuntimeException(e); } } catch (UnsupportedQueryException _) { return super.count(q); -- 1.7.10.4