# HG changeset patch
# User Sascha L. Teichmann <sascha.teichmann@intevation.de>
# Date 1301587010 0
# Node ID 1ea35226a6dea61ef490c95c3cc0e7490bba7482
# Parent  fbd57d2eeaef3b40223dd0e80e372d2a47d2b9de
Database cleaner: Ignoring locked artifacts is now done on database level.


artifacts/trunk@1635 c6561f87-3c4e-4783-a992-168aeb5c3f6f

diff -r fbd57d2eeaef -r 1ea35226a6de ChangeLog
--- a/ChangeLog	Thu Mar 31 14:48:28 2011 +0000
+++ b/ChangeLog	Thu Mar 31 15:56:50 2011 +0000
@@ -1,3 +1,16 @@
+2011-03-31	Sascha L. Teichmann	<sascha.teichmann@intevation.de>
+
+	* artifact-database/src/main/java/de/intevation/artifactdatabase/StringUtils.java:
+	  Added methods to repeat chars or strings.
+
+	* artifact-database/src/main/java/de/intevation/artifactdatabase/DatabaseCleaner.java:
+	  Filtering out locked artifact ids is now done on database level.
+
+	* artifact-database/src/main/resources/sql/org-h2-driver.properties,
+	  artifact-database/src/main/resources/sql/org-postgresql-driver.properties:
+	  Added $LOCKED_ID$ template for the prepared statements to figure
+	  out the locked ids.
+
 2011-03-31	Sascha L. Teichmann	<sascha.teichmann@intevation.de>
 
 	* artifact-database/src/main/java/de/intevation/artifactdatabase/DatabaseCleaner.java:
diff -r fbd57d2eeaef -r 1ea35226a6de artifact-database/src/main/java/de/intevation/artifactdatabase/DatabaseCleaner.java
--- a/artifact-database/src/main/java/de/intevation/artifactdatabase/DatabaseCleaner.java	Thu Mar 31 14:48:28 2011 +0000
+++ b/artifact-database/src/main/java/de/intevation/artifactdatabase/DatabaseCleaner.java	Thu Mar 31 15:56:50 2011 +0000
@@ -221,10 +221,25 @@
         int removedArtifacts = 0;
 
         DataSource dataSource = DBConnection.getDataSource();
+
+        Set<Integer> lockedIds = lockedIdsProvider != null
+            ? lockedIdsProvider.getLockedIds()
+            : EMPTY_IDS;
+
+        String questionMarks = StringUtils.repeat('?', lockedIds.size(), ',');
+
         try {
             connection = dataSource.getConnection();
             connection.setAutoCommit(false);
-            fetchIds = connection.prepareStatement(SQL_OUTDATED);
+
+            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
@@ -234,20 +249,13 @@
             for (;;) {
                 List<IdData> ids = new ArrayList<IdData>();
 
-                Set<Integer> filter = lockedIdsProvider != null
-                    ? lockedIdsProvider.getLockedIds()
-                    : EMPTY_IDS;
-
                 result = fetchIds.executeQuery();
 
                 while (result.next()) {
-                    int id = result.getInt(1);
-                    if (!filter.contains(id)) {
-                        ids.add(new IdData(
-                            id,
-                            result.getString(2),
-                            result.getBytes(3)));
-                    }
+                    ids.add(new IdData(
+                        result.getInt(1),
+                        result.getString(2),
+                        result.getBytes(3)));
                 }
 
                 result.close(); result = null;
diff -r fbd57d2eeaef -r 1ea35226a6de artifact-database/src/main/java/de/intevation/artifactdatabase/StringUtils.java
--- a/artifact-database/src/main/java/de/intevation/artifactdatabase/StringUtils.java	Thu Mar 31 14:48:28 2011 +0000
+++ b/artifact-database/src/main/java/de/intevation/artifactdatabase/StringUtils.java	Thu Mar 31 15:56:50 2011 +0000
@@ -81,5 +81,27 @@
             return null;
         }
     }
+
+    public static final String repeat(String s, int count, String sep) {
+        if (count <= 0) {
+            return "";
+        }
+        StringBuilder sb = new StringBuilder(s);
+        for (--count; count >= 0; --count) {
+            sb.append(sep).append(s);
+        }
+        return sb.toString();
+    }
+
+    public static final String repeat(char c, int count, char sep) {
+        if (count <= 0) {
+            return "";
+        }
+        StringBuilder sb = new StringBuilder(2*count-1).append(c);
+        for (--count; count >= 0; --count) {
+            sb.append(sep).append(c);
+        }
+        return sb.toString();
+    }
 }
 // vim:set ts=4 sw=4 si et sta sts=4 fenc=utf8 :
diff -r fbd57d2eeaef -r 1ea35226a6de artifact-database/src/main/resources/sql/org-h2-driver.properties
--- a/artifact-database/src/main/resources/sql/org-h2-driver.properties	Thu Mar 31 14:48:28 2011 +0000
+++ b/artifact-database/src/main/resources/sql/org-h2-driver.properties	Thu Mar 31 15:56:50 2011 +0000
@@ -12,6 +12,7 @@
 artifacts.outdated=SELECT id, factory, data FROM artifacts WHERE ttl IS NOT NULL \
     AND DATEDIFF('MILLISECOND', last_access, CURRENT_TIMESTAMP) > ttl \
     AND id NOT IN (SELECT DISTINCT artifact_id FROM collection_items) \
+    AND id NOT IN ($LOCKED_IDS$) \
     LIMIT 50
 
 artifacts.select.gid=SELECT id, last_access, ttl, factory, data FROM artifacts WHERE gid = ?
diff -r fbd57d2eeaef -r 1ea35226a6de artifact-database/src/main/resources/sql/org-postgresql-driver.properties
--- a/artifact-database/src/main/resources/sql/org-postgresql-driver.properties	Thu Mar 31 14:48:28 2011 +0000
+++ b/artifact-database/src/main/resources/sql/org-postgresql-driver.properties	Thu Mar 31 15:56:50 2011 +0000
@@ -12,6 +12,7 @@
 artifacts.outdated=SELECT id, factory, data FROM artifacts WHERE ttl IS NOT NULL \
     AND CURRENT_TIMESTAMP - last_access > (ttl || ' microseconds')::interval \
     AND id NOT IN (SELECT DISTINCT artifact_id FROM collection_items) \
+    AND id NOT IN ($LOCKED_IDS$) \
     LIMIT 50
 
 artifacts.select.gid=SELECT id, last_access, ttl, factory, data FROM artifacts WHERE gid = ?::uuid