Mercurial > dive4elements > framework

comparison artifact-database/src/main/java/org/dive4elements/artifactdatabase/DatabaseCleaner.java @ 542:9497f58484a0

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Ugly: Filter collection items in user land.
author Sascha L. Teichmann <teichmann@intevation.de>
date Thu, 03 Sep 2015 17:00:30 +0200
parents 3b1e48d22ce0
children e9abf747f6ce
comparison
equal deleted inserted replaced
541:3b1e48d22ce0 542:9497f58484a0
23 import java.sql.SQLException; 23 import java.sql.SQLException;
24 24
25 import java.util.ArrayList; 25 import java.util.ArrayList;
26 import java.util.List; 26 import java.util.List;
27 import java.util.Set; 27 import java.util.Set;
28 import java.util.HashSet;
28 import java.util.Collections; 29 import java.util.Collections;
29 30
30 import javax.sql.DataSource; 31 import javax.sql.DataSource;
31 32
32 import org.apache.log4j.Logger; 33 import org.apache.log4j.Logger;
88 public String SQL_OUTDATED; 89 public String SQL_OUTDATED;
89 90
90 public String SQL_OUTDATED_COLLECTIONS; 91 public String SQL_OUTDATED_COLLECTIONS;
91 public String SQL_DELETE_COLLECTION_ITEMS; 92 public String SQL_DELETE_COLLECTION_ITEMS;
92 public String SQL_DELETE_COLLECTION; 93 public String SQL_DELETE_COLLECTION;
94 public String SQL_COLLECTION_ITEMS_ARTIFACT_IDS;
93 95
94 /** 96 /**
95 * The SQL statement to delete some artifacts from the database. 97 * The SQL statement to delete some artifacts from the database.
96 */ 98 */
97 public String SQL_DELETE_ARTIFACT; 99 public String SQL_DELETE_ARTIFACT;
163 this.sqlExecutor = sqlExecutor; 165 this.sqlExecutor = sqlExecutor;
164 setupSQL(config.getSQL()); 166 setupSQL(config.getSQL());
165 } 167 }
166 168
167 protected void setupSQL(SQL sql) { 169 protected void setupSQL(SQL sql) {
168 SQL_OUTDATED = sql.get("artifacts.outdated"); 170 SQL_OUTDATED = sql.get("artifacts.outdated");
169 SQL_OUTDATED_COLLECTIONS = sql.get("collections.outdated"); 171 SQL_OUTDATED_COLLECTIONS = sql.get("collections.outdated");
170 SQL_DELETE_COLLECTION_ITEMS = sql.get("delete.collection.items"); 172 SQL_DELETE_COLLECTION_ITEMS = sql.get("delete.collection.items");
171 SQL_DELETE_COLLECTION = sql.get("delete.collection"); 173 SQL_DELETE_COLLECTION = sql.get("delete.collection");
172 SQL_DELETE_ARTIFACT = sql.get("artifacts.delete"); 174 SQL_DELETE_ARTIFACT = sql.get("artifacts.delete");
175 SQL_COLLECTION_ITEMS_ARTIFACT_IDS = sql.get("collection.items.artifact.id");
173 } 176 }
174 177
175 /** 178 /**
176 * Sets the filter that prevents deletion of living artifacts. 179 * Sets the filter that prevents deletion of living artifacts.
177 * Living artifacts are artifacts which are currently active 180 * Living artifacts are artifacts which are currently active
269 SQLExecutor.Instance exec = sqlExecutor.new Instance() { 272 SQLExecutor.Instance exec = sqlExecutor.new Instance() {
270 273
271 @Override 274 @Override
272 public boolean doIt() throws SQLException { 275 public boolean doIt() throws SQLException {
273 276
274 PreparedStatement fetchIds = null; 277 PreparedStatement collectionItems = null;
275 PreparedStatement stmnt = null; 278 PreparedStatement fetchIds = null;
276 ResultSet result = null; 279 PreparedStatement stmnt = null;
280 ResultSet result = null;
281
282 HashSet<Integer> collectionItemsIds =
283 new HashSet<Integer>();
277 284
278 try { 285 try {
286 collectionItems = conn.prepareStatement(
287 SQL_COLLECTION_ITEMS_ARTIFACT_IDS);
288
289 result = collectionItems.executeQuery();
290
291 while (result.next()) {
292 collectionItemsIds.add(result.getInt(1));
293 }
294 result.close(); result = null;
295
279 fetchIds = conn.prepareStatement( 296 fetchIds = conn.prepareStatement(
280 SQL_OUTDATED.replace("$LOCKED_IDS$", questionMarks)); 297 SQL_OUTDATED.replace("$LOCKED_IDS$", questionMarks));
281 298
282 // some dbms like derby do not support LIMIT 299 // some dbms like derby do not support LIMIT
283 // in SQL statements. 300 // in SQL statements.
338 for (;;) { 355 for (;;) {
339 List<IdData> ids = new ArrayList<IdData>(); 356 List<IdData> ids = new ArrayList<IdData>();
340 357
341 result = fetchIds.executeQuery(); 358 result = fetchIds.executeQuery();
342 359
360 int total = 0;
361
343 while (result.next()) { 362 while (result.next()) {
344 ids.add(new IdData( 363 total++;
345 result.getInt(1), 364 int id = result.getInt(1);
346 result.getString(2), 365 if (!collectionItemsIds.contains(id)) {
347 result.getBytes(3), 366 ids.add(new IdData(
348 result.getString(4))); 367 id,
368 result.getString(2),
369 result.getBytes(3),
370 result.getString(4)));
371 }
349 } 372 }
350 373
351 result.close(); result = null; 374 result.close(); result = null;
352 375
376 if (total == 0) {
377 break;
378 }
379
353 if (ids.isEmpty()) { 380 if (ids.isEmpty()) {
354 break; 381 continue;
355 } 382 }
356 383
357 for (int i = ids.size()-1; i >= 0; --i) { 384 for (int i = ids.size()-1; i >= 0; --i) {
358 IdData idData = ids.get(i); 385 IdData idData = ids.get(i);
359 Artifact artifact = reviver.reviveArtifact( 386 Artifact artifact = reviver.reviveArtifact(
392 try { stmnt.close(); } 419 try { stmnt.close(); }
393 catch (SQLException sqle) {} 420 catch (SQLException sqle) {}
394 } 421 }
395 if (fetchIds != null) { 422 if (fetchIds != null) {
396 try { fetchIds.close(); } 423 try { fetchIds.close(); }
424 catch (SQLException sqle) {}
425 }
426 if (collectionItems != null) {
427 try { collectionItems.close(); }
397 catch (SQLException sqle) {} 428 catch (SQLException sqle) {}
398 } 429 }
399 } 430 }
400 return true; 431 return true;
401 } 432 }
http://dive4elements.wald.intevation.org