# HG changeset patch # User Sascha L. Teichmann # Date 1301591662 0 # Node ID 202b6ae2fcad162214bf20b69325ca6a9d23f2e8 # Parent 1ea35226a6dea61ef490c95c3cc0e7490bba7482 Database cleaner: Removes outdated collections, too. artifacts/trunk@1636 c6561f87-3c4e-4783-a992-168aeb5c3f6f diff -r 1ea35226a6de -r 202b6ae2fcad artifact-database/src/main/java/de/intevation/artifactdatabase/DatabaseCleaner.java --- 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 cs = new ArrayList(); + 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 ids = new ArrayList(); @@ -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); } /** diff -r 1ea35226a6de -r 202b6ae2fcad artifact-database/src/main/resources/sql/org-h2-driver.properties --- 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 = ? diff -r 1ea35226a6de -r 202b6ae2fcad artifact-database/src/main/resources/sql/org-postgresql-driver.properties --- 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