add SqliteDB.close(ResultSet) and use it

[org.ibex.mail.git] / src / org / ibex / mail / SqliteDB.java

diff --git a/src/org/ibex/mail/SqliteDB.java b/src/org/ibex/mail/SqliteDB.java
index 39acded..6167eec 100644 (file)
--- a/src/org/ibex/mail/SqliteDB.java
+++ b/src/org/ibex/mail/SqliteDB.java
@@ -15,55 +15,108 @@ public class SqliteDB {
 
     protected Connection conn;
     private String filename;
-    private String reapTable;
-    private String reapColumn;
+
+    private HashMap<String,SqliteTable> tables = new HashMap<String,SqliteTable>();
+
+    public static final int REAPER_INTERVAL_SECONDS = 60 * 60;
+    private static final int DAYS = 24 * 60 * 60 * 1000;
+    public static final int REAP_EXPIRATION = 5 * DAYS;
+
+    public Connection getConnection() { return conn; }
+
+    public synchronized SqliteTable getTable(String name, String schema) throws SQLException {
+        SqliteTable ret = tables.get(name);
+        if (ret==null) ret = new SqliteTable(name, schema);
+        return ret;
+    }
 
     // check upstream: PRAGMA encoding = "UTF-8"; 
     // create indices
-    // PRAGMA auto_vacuum=1  (can only be set before any tables are created)
-    // periodic "PRAGMA integrity_check; "?
+    // periodically run "analyze"?
 
     public void setCacheSize(int kilobytes) throws SQLException {
         conn.prepareStatement("PRAGMA cache_size="+Math.ceil(kilobytes/1.5)+";").executeUpdate();
     }
 
-    public SqliteDB(String filename, String[] tables) {
-        this(filename, tables, false);
+
+    public void close(ResultSet rs) {
+        if (rs==null) return;
+        try {
+            rs.close();
+        } catch (SQLException s) {
+            Log.error(ResultSet.class, s);
+        }
     }
-    public SqliteDB(String filename, String[] tables, boolean fastButDangerous) {
+
+    public SqliteDB(String filename) {
         this.filename = filename;
         try {
+            Log.error("start", "initializing " + filename);
             Class.forName("org.sqlite.JDBC");
             conn = DriverManager.getConnection("jdbc:sqlite:"+filename);
-            for(String s : tables)
-                conn.prepareStatement(s).executeUpdate();
-            conn.prepareStatement("PRAGMA temp_store = MEMORY").executeUpdate();
+            conn.prepareStatement("PRAGMA auto_vacuum = 1").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 page_size=4096").executeUpdate();
             conn.prepareStatement("PRAGMA cache_size=2000").executeUpdate();
-            if (fastButDangerous)
-                conn.prepareStatement("PRAGMA synchronous = OFF").executeUpdate();
+            ResultSet rs = conn.prepareStatement("PRAGMA integrity_check").executeQuery();
+            rs.next();
+            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); }
     }
 
-    protected void reap(String reapTable, String reapColumn) {
-        if (this.reapTable != null || this.reapColumn != null)
-            throw new RuntimeException("reapTable/reapColumn already set");
-        this.reapTable = reapTable;
-        this.reapColumn = reapColumn;
-        if (reapTable != null && reapColumn != null)
-            Main.cron.executeLater(1000 * REAPER_INTERVAL_SECONDS, new Reaper());
+    public void setFastButDangerous(boolean on) throws SQLException {
+        conn.prepareStatement("PRAGMA synchronous = "+(on?"OFF":"ON")).executeUpdate();
     }
 
-    public static final int REAPER_INTERVAL_SECONDS = 60 * 60;
+    /*
+    public void dump(OutputStream os) {
+    }
+    */
+
+    public class SqliteTable {
+        public final String name;
+        private String reapColumn = null;
+        private SqliteTable(String name, String schema) throws SQLException {
+            this.name = name;
+            PreparedStatement ps = conn.prepareStatement("create table if not exists " + name + " " + schema);
+            ps.executeUpdate();
+            tables.put(name, this);
+        }
+        public void createIndex(String column) throws SQLException { createIndex(column, column+"_index"); }
+        public void createIndex(String indexName, String column) throws SQLException {
+            PreparedStatement ps = conn.prepareStatement("create index if not exists "+column+" on "+name+" ("+indexName+")");
+            ps.executeUpdate();
+        }
+        protected void reap(String reapColumn) {
+            if (this.reapColumn != null) throw new RuntimeException("reapColumn already set");
+            this.reapColumn = reapColumn;
+            Main.cron.executeLater(1000 * REAPER_INTERVAL_SECONDS, new Reaper(name, reapColumn));
+        }
+    }
 
+    // FIXME: desynchronized access to the conn?
     private class Reaper implements Runnable {
+        public Reaper(String reapTable, String reapColumn) {
+            this.reapTable = reapTable;
+            this.reapColumn = reapColumn;
+        }
+        private String reapTable;
+        private String reapColumn;
         public void run() {
             try {
                 Log.warn(Reaper.class, filename + " reaping...");
                 long when = System.currentTimeMillis();
-                when -= 5 * 24 * 60 * 60 * 1000;
+                when -= REAP_EXPIRATION;
                 synchronized(SqliteDB.this) {
                     PreparedStatement ps =
                         conn.prepareStatement("select count(*) from "+reapTable+" where "+reapColumn+"<?");
@@ -82,4 +135,11 @@ public class SqliteDB {
         }
     }
 
+    static String streamToString(Stream stream) throws Exception {
+        // FIXME!!!! This is corrupting line endings!!!!
+        StringBuffer b = new StringBuffer();
+        for(String s = stream.readln(); s!=null; s=stream.readln())
+            b.append(s+"\n");
+        return b.toString();
+    }
 }