From: adam <adam@megacz.com>
Date: Sun, 15 Jul 2007 23:08:29 +0000 (+0000)
Subject: SqliteTable: fixups to schema handling
X-Git-Url: http://git.megacz.com/?p=org.ibex.mail.git;a=commitdiff_plain;h=36e19d8a6c5c04fc1d48c166ca7d951b763093a1

SqliteTable: fixups to schema handling

darcs-hash:20070715230829-5007d-c3143e9e1b40ce66398b28d98b24b33a9f9911a2.gz
---

diff --git a/src/org/ibex/mail/Graylist.java b/src/org/ibex/mail/Graylist.java
index 0a0ad53..a23581e 100644
--- a/src/org/ibex/mail/Graylist.java
+++ b/src/org/ibex/mail/Graylist.java
@@ -9,13 +9,11 @@ import java.sql.Timestamp;
 
 public class Graylist extends SqliteDB {
 
-    public Graylist(String filename) {
-        super(filename,
-              new String[] {
-                  "create table if not exists 'whitelist' (ip unique)",
-                  "create table if not exists 'graylist' (ip,fromaddr,toaddr,date, primary key(ip,fromaddr,toaddr))"
-              });
-        getTable("graylist").reap("date");
+    public Graylist(String filename) throws SQLException {
+        super(filename);
+        SqliteTable whitelist = getTable("whitelist", "(ip unique)");
+        SqliteTable graylist  = getTable("graylist",  "(ip,fromaddr,toaddr,date,PRIMARY KEY(ip,fromaddr,toaddr))");
+        graylist.reap("date");
     }
 
     public synchronized void addWhitelist(InetAddress ip) {
diff --git a/src/org/ibex/mail/SMTP.java b/src/org/ibex/mail/SMTP.java
index 039b3a7..a426987 100644
--- a/src/org/ibex/mail/SMTP.java
+++ b/src/org/ibex/mail/SMTP.java
@@ -46,11 +46,16 @@ public class SMTP {
     public static final int GRAYLIST_MINWAIT =  1000 * 60 * 60;           // one hour
     public static final int GRAYLIST_MAXWAIT =  1000 * 60 * 60 * 24 * 5;  // five days
 
-    public static final Graylist graylist =
-        new Graylist(Mailbox.STORAGE_ROOT+"/db/graylist.sqlite");
-
-    public static final Whitelist whitelist =
-        new Whitelist(Mailbox.STORAGE_ROOT+"/db/whitelist.sqlite");
+    public static final Graylist graylist;
+    public static final Whitelist whitelist;
+    static {
+        try {
+            graylist =  new Graylist(Mailbox.STORAGE_ROOT+"/db/graylist.sqlite");
+            whitelist = new Whitelist(Mailbox.STORAGE_ROOT+"/db/whitelist.sqlite");
+        } catch (Exception e) {
+            throw new RuntimeException(e);
+        }
+    }
 
     public static final int MAX_MESSAGE_SIZE =
         Integer.parseInt(System.getProperty("org.ibex.mail.smtp.maxMessageSize", "-1"));
diff --git a/src/org/ibex/mail/SqliteDB.java b/src/org/ibex/mail/SqliteDB.java
index e5fbc68..900e07c 100644
--- a/src/org/ibex/mail/SqliteDB.java
+++ b/src/org/ibex/mail/SqliteDB.java
@@ -22,48 +22,67 @@ public class SqliteDB {
 
     public Connection getConnection() { return conn; }
 
-    public synchronized SqliteTable getTable(String name) {
+    public synchronized SqliteTable getTable(String name, String schema) throws SQLException {
         SqliteTable ret = tables.get(name);
-        if (ret==null) ret = new SqliteTable(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 SqliteDB(String filename, String[] tables, boolean fastButDangerous) {
+    public SqliteDB(String filename) {
         this.filename = filename;
         try {
             Class.forName("org.sqlite.JDBC");
             conn = DriverManager.getConnection("jdbc:sqlite:"+filename);
-            for(String s : tables)
-                conn.prepareStatement(s).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").executeUpdate();
             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+"\"");
         }
         catch (SQLException e) { throw new RuntimeException(e); }
         catch (ClassNotFoundException e) { throw new RuntimeException(e); }
     }
 
+    public void setFastButDangerous(boolean on) throws SQLException {
+        conn.prepareStatement("PRAGMA synchronous = "+(on?"OFF":"ON")).executeUpdate();
+    }
+
+    /*
+    public void dump(OutputStream os) {
+    }
+    */
+
     public class SqliteTable {
         public final String name;
         private String reapColumn = null;
-        private SqliteTable(String name) {
+        private SqliteTable(String name, String schema) throws SQLException {
             this.name = name;
+            PreparedStatement ps = conn.prepareStatement("create table if not exists ?");
+            ps.setString(1, name+" "+schema);
+            ps.executeUpdate();
             tables.put(name, this);
         }
+        public void createIndex(String name, String column) throws SQLException {
+            PreparedStatement ps = conn.prepareStatement("create index if not exists ? on ?");
+            ps.setString(1, name);
+            ps.setString(2, column);
+            ps.executeUpdate();
+        }
         protected void reap(String reapColumn) {
             if (this.reapColumn != null) throw new RuntimeException("reapColumn already set");
             this.reapColumn = reapColumn;
diff --git a/src/org/ibex/mail/Whitelist.java b/src/org/ibex/mail/Whitelist.java
index f8fc4b1..81e4b64 100644
--- a/src/org/ibex/mail/Whitelist.java
+++ b/src/org/ibex/mail/Whitelist.java
@@ -14,14 +14,11 @@ import java.sql.Connection;
 
 public class Whitelist extends SqliteDB {
 
-    public Whitelist(String filename) {
-        super(filename,
-              new String[] {
-                  "create table if not exists 'whitelist' (email)",
-                  "create table if not exists 'pending' (spamid,email,message,date)"
-              },
-              true);
-        getTable("pending").reap("date");
+    public Whitelist(String filename) throws SQLException {
+        super(filename);
+        SqliteTable whitelist = getTable("whitelist", "(email)");
+        SqliteTable pending   = getTable("pending",   "(spamid,email,message,date)");
+        pending.reap("date");
     }
 
     public boolean handleRequest(org.ibex.net.Connection c) {