--- a/artifact-database/src/main/java/de/intevation/artifactdatabase/DatabaseCleaner.java	Thu Mar 31 15:56:50 2011 +0000
+++ b/artifact-database/src/main/java/de/intevation/artifactdatabase/DatabaseCleaner.java	Thu Mar 31 17:14:22 2011 +0000
@@ -80,10 +80,19 @@
     public static final String SQL_OUTDATED =
         SQL.get("artifacts.outdated");
 
+    public static final String SQL_OUTDATED_COLLECTIONS =
+        SQL.get("collections.outdated");
+
+    public static final String SQL_DELETE_COLLECTION_ITEMS =
+        SQL.get("delete.collection.items");
+
+    public static final String SQL_DELETE_COLLECTION =
+        SQL.get("delete.collection");
+
     /**
      * The SQL statement to delete some artifacts from the database.
      */
-    public static final String SQL_DELETE =
+    public static final String SQL_DELETE_ARTIFACT =
         SQL.get("artifacts.delete");
 
     /**
@@ -213,12 +222,13 @@
     protected void cleanup() {
         logger.info("database cleanup");
 
-        Connection connection      = null;
-        PreparedStatement fetchIds = null;
-        PreparedStatement deleteId = null;
-        ResultSet         result   = null;
+        Connection        connection = null;
+        PreparedStatement fetchIds   = null;
+        PreparedStatement stmnt      = null;
+        ResultSet         result     = null;
 
-        int removedArtifacts = 0;
+        int removedCollections = 0;
+        int removedArtifacts   = 0;
 
         DataSource dataSource = DBConnection.getDataSource();
 
@@ -235,17 +245,58 @@
             fetchIds = connection.prepareStatement(
                 SQL_OUTDATED.replace("$LOCKED_IDS$", questionMarks));
 
-            int idx = 1;
-            for (Integer id: lockedIds) {
-                fetchIds.setInt(idx++, id);
-            }
-
-            deleteId = connection.prepareStatement(SQL_DELETE);
-
             // some dbms like derby do not support LIMIT
             // in SQL statements.
             fetchIds.setMaxRows(MAX_ROWS);
 
+            // Fetch ids of outdated collections
+            stmnt = connection.prepareStatement(
+                SQL_OUTDATED_COLLECTIONS.replace(
+                    "$LOCKED_IDS$", questionMarks));
+
+            // fill in the locked ids
+            int idx = 1;
+            for (Integer id: lockedIds) {
+                fetchIds.setInt(idx, id);
+                stmnt   .setInt(idx, id);
+                ++idx;
+            }
+
+            ArrayList<Integer> cs = new ArrayList<Integer>();
+            result = stmnt.executeQuery();
+            while (result.next()) {
+                cs.add(result.getInt(1));
+            }
+
+            result.close(); result = null;
+            stmnt.close();  stmnt  = null;
+
+            // delete collection items
+            stmnt = connection.prepareStatement(SQL_DELETE_COLLECTION_ITEMS);
+
+            for (Integer id: cs) {
+                stmnt.setInt(1, id);
+                stmnt.execute();
+            }
+
+            stmnt.close(); stmnt = null;
+
+            // delete collections
+            stmnt = connection.prepareStatement(SQL_DELETE_COLLECTION);
+
+            for (Integer id: cs) {
+                stmnt.setInt(1, id);
+                stmnt.execute();
+            }
+
+            stmnt.close(); stmnt = null;
+            connection.commit();
+
+            removedCollections = cs.size(); cs = null;
+
+            // remove artifacts
+            stmnt = connection.prepareStatement(SQL_DELETE_ARTIFACT);
+
             for (;;) {
                 List<IdData> ids = new ArrayList<IdData>();
 
@@ -270,8 +321,8 @@
                         idData.factoryName, idData.data);
                     idData.data = null;
 
-                    deleteId.setInt(1, idData.id);
-                    deleteId.execute();
+                    stmnt.setInt(1, idData.id);
+                    stmnt.execute();
                     connection.commit();
 
                     try {
@@ -295,21 +346,22 @@
                 try { result.close(); }
                 catch (SQLException sqle) {}
             }
+            if (stmnt != null) {
+                try { stmnt.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);
+        logger.info("collections removed: " + removedCollections);
+        logger.info("artifacts removed: "   + removedArtifacts);
     }
 
     /**

--- a/artifact-database/src/main/resources/sql/org-h2-driver.properties	Thu Mar 31 15:56:50 2011 +0000
+++ b/artifact-database/src/main/resources/sql/org-h2-driver.properties	Thu Mar 31 17:14:22 2011 +0000
@@ -86,6 +86,14 @@
 
 # COLLECTIONS
 
+collections.outdated= \
+    SELECT c.id FROM collections c \
+        INNER JOIN collection_items ci ON c.id = ci.collection_id \
+        INNER JOIN artifacts        a  ON ci.artifact_id = a.id \
+        WHERE c.ttl IS NOT NULL \
+            AND DATEDIFF('MILLISECOND', c.last_access, CURRENT_TIMESTAMP) > c.ttl \
+            AND a.id NOT IN ($LOCKED_IDS$)
+
 collections.touch.trigger.function = \
    UPDATE collections SET last_access = current_timestamp \
    WHERE id IN \
@@ -94,7 +102,6 @@
          INNER JOIN artifacts a         ON a.id = ci.artifact_id \
          WHERE a.id = ?)
 
-
 collections.touch.by.gid =\
     UPDATE collections SET last_access = CURRENT_TIMESTAMP \
         WHERE gid = ?

--- a/artifact-database/src/main/resources/sql/org-postgresql-driver.properties	Thu Mar 31 15:56:50 2011 +0000
+++ b/artifact-database/src/main/resources/sql/org-postgresql-driver.properties	Thu Mar 31 17:14:22 2011 +0000
@@ -85,6 +85,15 @@
     WHERE ci.collection_id IN (SELECT id FROM collections WHERE gid = ?::uuid)
 
 # COLLECTIONS
+
+collections.outdated= \
+    SELECT c.id FROM collections c \
+        INNER JOIN collection_items ci ON c.id = ci.collection_id \
+        INNER JOIN artifacts        a  ON ci.artifact_id = a.id \
+        WHERE c.ttl IS NOT NULL \
+            AND CURRENT_TIMESTAMP - c.last_access > (c.ttl || ' microseconds')::interval \
+            AND a.id NOT IN ($LOCKED_IDS$)
+
 collections.touch.by.gid =\
     UPDATE collections SET last_access = CURRENT_TIMESTAMP \
         WHERE gid = ?::uuid