--- a/Changelog	Thu Sep 10 15:49:17 2009 +0000
+++ b/Changelog	Thu Sep 10 23:16:18 2009 +0000
@@ -1,3 +1,37 @@
+2009-09-11	Sascha L. Teichmann	<sascha.teichmann@intevation.de>
+
+	* artifact-database/doc/example-conf/conf.xml: Added
+	sleep time for cleanup thread.
+
+	* artifact-database/src/main/java/de/intevation/artifactdatabase/App.java:
+	Start cleanup thread at startup.
+
+	* artifact-database/src/main/java/de/intevation/artifactdatabase/Backend.java:
+	Make artifact restoring bit more robust, added ref to
+	cleanup thread to do an immediate cleanup when dead artifact
+	is found by lookup.
+
+	* artifact-database/src/main/java/de/intevation/artifactdatabase/DatabaseCleaner.java:
+	New. The cleanup thread. It cleans up the artifact database
+	perodically and calls the endOfLife() methods of the dead
+	artifacts. Default cleanup interval is 5 minutes.
+
+	* artifact-database/src/main/resources/sql/org-h2-driver.properties:
+	Tuned SQL a bit to fetch only a 50 artifacts in search for
+	outdated artifacts to avoid too much memory consumption.
+
+	* contrib/run.sh: Little tweak to work with more than one
+	commons-pool version installed.
+
+	* TODO: Removed remarks about the now implemented killer 
+	thread and the moves XML stuff.
+
+	* README: Changed text how to create a H2 database from
+	command line only. XXX: This is still broken. The best
+	way to set up the database by now is to start the Console 
+	client, connect to the database, c&p the schema.sql 
+	into the SQL text area and execute it.
+
 2009-09-10	Sascha L. Teichmann	<sascha.teichmann@intevation.de>
 
 	* artifact-database/src/main/java/de/intevation/artifactdatabase/rest/ArtifactOutResource.java:

--- a/README	Thu Sep 10 15:49:17 2009 +0000
+++ b/README	Thu Sep 10 23:16:18 2009 +0000
@@ -1,8 +1,8 @@
 
 Create a new H2 database for usage in the artifact database:
 
-$ java -cp `find ~/.m2/ -name h2\*.jar` org.h2.tools.Shell \
+$ java -cp `find ~/.m2/ -name h2\*.jar` org.h2.tools.RunScript \
     -user USER \
     -password PASSWORD \
-    -url jdbc:h2:PATH_TO_FILE \
-    < artifact-database/doc/schema.sql
+    -url jdbc:h2:artifact-database/doc/example-conf/artifacts.db \
+    -script artifact-database/doc/schema.sql

--- a/TODO	Thu Sep 10 15:49:17 2009 +0000
+++ b/TODO	Thu Sep 10 23:16:18 2009 +0000
@@ -1,5 +1,2 @@
 TODO:
 	* Document the XML of the configuration file.
-	* Implement a "killer" thread in artifact database which
-	  periodically kills all outdated artifacts.
-    * move XML stuff from Config to XMLUtils.

--- a/artifact-database/doc/example-conf/conf.xml	Thu Sep 10 15:49:17 2009 +0000
+++ b/artifact-database/doc/example-conf/conf.xml	Thu Sep 10 23:16:18 2009 +0000
@@ -18,6 +18,10 @@
     <rest-server>
         <port>8181</port>
     </rest-server>
+    <!-- garbage collection of outdated artifacts -->
+    <cleaner>
+        <sleep-time>60000</sleep-time>
+    </cleaner>
     <database>
         <user></user>
         <password></password>

--- a/artifact-database/src/main/java/de/intevation/artifactdatabase/App.java	Thu Sep 10 15:49:17 2009 +0000
+++ b/artifact-database/src/main/java/de/intevation/artifactdatabase/App.java	Thu Sep 10 23:16:18 2009 +0000
@@ -40,7 +40,12 @@
 
         bootstrap.boot();
 
-        Backend backend = new Backend();
+        DatabaseCleaner cleaner = new DatabaseCleaner(
+            bootstrap.getContext());
+
+        cleaner.start();
+
+        Backend backend = new Backend(cleaner);
 
         ArtifactDatabaseImpl db = new ArtifactDatabaseImpl(
             bootstrap, backend);

--- a/artifact-database/src/main/java/de/intevation/artifactdatabase/Backend.java	Thu Sep 10 15:49:17 2009 +0000
+++ b/artifact-database/src/main/java/de/intevation/artifactdatabase/Backend.java	Thu Sep 10 23:16:18 2009 +0000
@@ -48,6 +48,8 @@
     public static final String SQL_LOAD_BY_GID =
         SQL.get("artifacts.select.gid");
 
+    protected DatabaseCleaner cleaner;
+
     /**
      * Used to wrap the calls to invole database actions.
      */
@@ -153,6 +155,10 @@
     public Backend() {
     }
 
+    public Backend(DatabaseCleaner cleaner) {
+        this.cleaner = cleaner;
+    }
+
     public Artifact getArtifact(String idenitfier) {
         UUID uuid;
 
@@ -251,6 +257,10 @@
 
     public static Artifact restoreArtifact(byte [] bytes) {
 
+        if (bytes == null) {
+            return null;
+        }
+
         ObjectInputStream ois = null;
 
         try {
@@ -281,6 +291,9 @@
 
     protected void artifactOutdated(int id) {
         logger.info("artifactOutdated: id = " + id);
+        if (cleaner != null) {
+            cleaner.wakeup();
+        }
     }
 
     protected int insertDatabase(UUID uuid, Long ttl) {

--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/artifact-database/src/main/java/de/intevation/artifactdatabase/DatabaseCleaner.java	Thu Sep 10 23:16:18 2009 +0000
@@ -0,0 +1,195 @@
+package de.intevation.artifactdatabase;
+
+import de.intevation.artifacts.Artifact;
+
+import java.sql.Connection;
+import java.sql.SQLException;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+
+import javax.sql.DataSource;
+
+import org.apache.log4j.Logger;
+
+import java.util.ArrayList;
+
+/**
+ *  @author Sascha L. Teichmann
+ */
+public class DatabaseCleaner
+extends      Thread
+{
+    private static Logger logger = Logger.getLogger(DatabaseCleaner.class);
+
+    public static final int MAX_ROWS = 50;
+
+    public static final String SQL_OUTDATED =
+        SQL.get("artifacts.outdated");
+
+    public static final String SQL_DELETE =
+        SQL.get("artifacts.delete");
+
+    public static final String SLEEP_XPATH =
+        "/artifact-database/cleaner/sleep-time/text()";
+
+    public static final long SLEEP_DEFAULT =
+        5 * 60 * 1000L; // 5 minutes
+
+    protected long sleepTime;
+
+    protected Object sleepLock = new Object();
+
+    protected Object context;
+
+    public DatabaseCleaner() {
+    }
+
+    public DatabaseCleaner(Object context) {
+        setDaemon(true);
+        sleepTime = getSleepTime();
+        this.context = context;
+    }
+
+    public void wakeup() {
+        synchronized (sleepLock) {
+            sleepLock.notify();
+        }
+    }
+
+    protected static long getSleepTime() {
+        String sleepTimeString = Config.getStringXPath(SLEEP_XPATH);
+
+        if (sleepTimeString == null) {
+            return SLEEP_DEFAULT;
+        }
+        try {
+            // sleep at least one second
+            return Math.max(Long.parseLong(sleepTimeString), 1000L);
+        }
+        catch (NumberFormatException nfe) {
+            logger.warn("Cleaner sleep time defaults to " + SLEEP_DEFAULT);
+        }
+        return SLEEP_DEFAULT;
+    }
+
+    private static final class IdData {
+
+        int     id;
+        byte [] data;
+
+        public IdData(int id, byte [] data) {
+            this.id   = id;
+            this.data = data;
+        }
+    } // class IdData
+
+    /**
+     * Cleaning is done in two phases. First we fetch a list of ids
+     * of artifacts. If there are artifacts the cleaning is done.
+     * Second we load the artifacts one by one one and call there
+     * endOfLife() method. In this loop we remove them from database, too.
+     * Each deletion is commited to ensure that a sudden failure
+     * of the artifact database server does delete artifacts twice
+     * or does not delete them at all. After this the first step
+     * is repeated.
+     */
+    protected void cleanup() {
+        logger.info("database cleanup");
+
+        Connection connection      = null;
+        PreparedStatement fetchIds = null;
+        PreparedStatement deleteId = null;
+        ResultSet         result   = null;
+
+        int removedArtifacts = 0;
+
+        DataSource dataSource = DBConnection.getDataSource();
+        try {
+            connection = dataSource.getConnection();
+            connection.setAutoCommit(false);
+            fetchIds = connection.prepareStatement(SQL_OUTDATED);
+            deleteId = connection.prepareStatement(SQL_DELETE);
+
+            // some dbms like derby do not support LIMIT
+            // in SQL statements.
+            fetchIds.setMaxRows(MAX_ROWS);
+
+            for (;;) {
+                ArrayList ids = new ArrayList();
+
+                result = fetchIds.executeQuery();
+
+                while (result.next()) {
+                    ids.add(new IdData(
+                        result.getInt(1), result.getBytes(2)));
+                }
+
+                result.close(); result = null;
+
+                if (ids.isEmpty()) {
+                    break;
+                }
+
+                for (int i = ids.size()-1; i >= 0; --i) {
+                    IdData idData = (IdData)ids.get(i);
+                    Artifact artifact = Backend.restoreArtifact(
+                        idData.data);
+                    idData.data = null;
+
+                    deleteId.setInt(1, idData.id);
+                    deleteId.execute();
+                    connection.commit();
+
+                    try {
+                        if (artifact != null) {
+                            artifact.endOfLife(context);
+                        }
+                    }
+                    catch (Exception e) {
+                        logger.error(e.getLocalizedMessage(), e);
+                    }
+                } // for all fetched data
+
+                removedArtifacts += ids.size();
+            }
+        }
+        catch (SQLException sqle) {
+            logger.error(sqle.getLocalizedMessage(), sqle);
+        }
+        finally {
+            if (result != null) {
+                try { result.close(); }
+                catch (SQLException sqle) {}
+            }
+            if (fetchIds != null) {
+                try { fetchIds.close(); }
+                catch (SQLException sqle) {}
+            }
+            if (deleteId != null) {
+                try { deleteId.close(); }
+                catch (SQLException sqle) {}
+            }
+            if (connection != null) {
+                try { connection.close(); }
+                catch (SQLException sqle) {}
+            }
+        }
+
+        logger.info("artifacts removed: " + removedArtifacts);
+    }
+
+    public void run() {
+        logger.info("sleep time: " + sleepTime + "ms");
+        for (;;) {
+            cleanup();
+            try {
+                synchronized (sleepLock) {
+                    sleepLock.wait(sleepTime);
+                }
+            }
+            catch (InterruptedException ie) {
+            }
+        } // for (;;)
+    }
+}
+// vim:set ts=4 sw=4 si et sta sts=4 fenc=utf8:

--- a/artifact-database/src/main/resources/sql/org-h2-driver.properties	Thu Sep 10 15:49:17 2009 +0000
+++ b/artifact-database/src/main/resources/sql/org-h2-driver.properties	Thu Sep 10 23:16:18 2009 +0000
@@ -9,10 +9,8 @@
 
 artifacts.touch=UPDATE last_access = CURRENT_TIMESTAMP WHERE id = ?
 
-artifacts.outdated=SELECT id FROM artifacts WHERE ttl IS NOT NULL \
-    AND CURRENT_TIMESTAMP - last_access > ttl
-
-artifacts.select.id=SELECT gid, last_access, ttl, data FROM artifacts WHERE id = ?
+artifacts.outdated=SELECT id, data FROM artifacts WHERE ttl IS NOT NULL \
+    AND CURRENT_TIMESTAMP - last_access > ttl LIMIT 50
 
 artifacts.select.gid=SELECT id, last_access, ttl, data FROM artifacts WHERE gid = ?
 

--- a/contrib/run.sh	Thu Sep 10 15:49:17 2009 +0000
+++ b/contrib/run.sh	Thu Sep 10 23:16:18 2009 +0000
@@ -4,7 +4,7 @@
 H2=`find -L ~/.m2 -name h2-\*.jar`
 LOG4J=`find -L ~/.m2 -name log4j-1.2.13\*.jar`
 DBCP=`find -L ~/.m2 -name commons-dbcp-\*.jar`
-POOL=`find -L ~/.m2 -name commons-pool-1.5\*.jar`
+POOL=`find -L ~/.m2 -name commons-pool-*.jar | head -1`
 DIR=`dirname $0`/..
 CLASSPATH=$DIR/artifact-database/target/classes
 CLASSPATH=$CLASSPATH:$DIR/artifacts/target/classes