src/org/ibex/mail/SqliteTable.java

   1 package org.ibex.mail;
   2
   3 import org.ibex.io.*;
   4 import org.ibex.mail.protocol.*;
   5 import org.ibex.util.*;
   6 import org.ibex.net.*;
   7 import java.sql.*;
   8 import java.net.*;
   9 import java.io.*;
  10 import java.util.*;
  11 import java.sql.Timestamp;
  12 import java.sql.Connection;
  13
  14 public class SqliteTable {
  15
  16     protected Connection conn;
  17     private String filename;
  18     private String reapTable;
  19     private String reapColumn;
  20
  21     public SqliteTable(String filename, String[] tables, String reapTable, String reapColumn) {
  22         this.filename = filename;
  23         try {
  24             Class.forName("org.sqlite.JDBC");
  25             conn = DriverManager.getConnection("jdbc:sqlite:"+filename);
  26             for(String s : tables)
  27                 conn.prepareStatement(s).executeUpdate();
  28         }
  29         catch (SQLException e) { throw new RuntimeException(e); }
  30         catch (ClassNotFoundException e) { throw new RuntimeException(e); }
  31         this.reapTable = reapTable;
  32         this.reapColumn = reapColumn;
  33         if (reapTable != null && reapColumn != null)
  34             new Reaper().start();
  35     }
  36
  37     public static final int REAPER_INTERVAL_SECONDS = 60 * 60;
  38
  39     private class Reaper extends Thread {
  40
  41         public void run() {
  42             while(true) {
  43                 try {
  44                     try { Thread.sleep(1000 * REAPER_INTERVAL_SECONDS); } catch (Exception e) { };
  45                     Log.warn(Reaper.class, filename + " reaping...");
  46                     long when = System.currentTimeMillis();
  47                     when -= 5 * 24 * 60 * 60 * 1000;
  48                     synchronized(SqliteTable.this) {
  49                         PreparedStatement ps = conn.prepareStatement("select count(*) from "+reapTable+" where "+reapColumn+"<?");
  50                         ps.setTimestamp(1, new Timestamp(when));
  51                         ResultSet rs = ps.executeQuery();
  52                         if (rs.next())
  53                             Log.warn(Reaper.class, filename + " reaping " + rs.getInt(1) + " entries");
  54                         Log.warn(Reaper.class, filename + ": " + "delete from "+reapTable+" where "+reapColumn+"<"+when);
  55                         ps = conn.prepareStatement("delete from "+reapTable+" where "+reapColumn+"<?");
  56                         ps.setTimestamp(1, new Timestamp(when));
  57                         int rows = ps.executeUpdate();
  58                         Log.warn(Reaper.class, filename + " done reaping; removed " + rows + " rows");
  59                     }
  60                 } catch (Exception e) { Log.error(Reaper.class, e); }
  61             }
  62         }
  63     }
  64
  65 }