01: package com.technoetic.xplanner.upgrade;
02:
03: import java.sql.SQLException;
04: import java.util.Collection;
05: import java.util.Iterator;
06:
07: import net.sf.hibernate.HibernateException;
08: import org.apache.log4j.Logger;
09:
10: import com.technoetic.xplanner.util.LogUtil;
11:
12: public class CleanUpAttachments extends JdbcMigrationTaskSupport {
13: public static final Logger LOG = LogUtil.getLogger();
14: //DEBT(EXTERNALIZE QUERIES) move these query out to Note.xml
15: public static final String UPDATE_NOTE_SQL = "update note set attachment_Id = null where id = ?";
16: public static final String DELETE_ATTACHMENT_SQL = "delete from xfile where id = ?";
17: public static final String NOTE_IDS_WITH_FAKE_ATTACHMENTS_QUERY = "select note.id from note left join xfile on note.attachment_id = xfile.id "
18: + "where xfile.id is null and note.attachment_id is not null";
19: public static final String ORPHAN_ATTACHMENTS_QUERY = "select xfile.id from note right join xfile "
20: + "on note.attachment_id = xfile.id "
21: + "where note.id is null";
22:
23: public CleanUpAttachments() {
24: super ("cleanup_attachments", 8);
25: }
26:
27: public void migrate() throws HibernateException, SQLException {
28: cleanUpNotes();
29: cleanUpOrphanAttachments();
30: }
31:
32: /**
33: * Nulls attachmentId if there is no file with given id
34: *
35: * @throws HibernateException
36: */
37: public void cleanUpNotes() throws HibernateException, SQLException {
38: Collection notesToCleanUp = getNoteIdsWithFakeAttachment();
39: LOG.debug("Updating " + notesToCleanUp.size() + " notes.");
40: for (Iterator iterator = notesToCleanUp.iterator(); iterator
41: .hasNext();) {
42: Integer noteId = (Integer) iterator.next();
43: LOG.debug("Assigning null to attachmentId for noteId: "
44: + noteId);
45: template.update(UPDATE_NOTE_SQL, new Object[] { noteId });
46: }
47: }
48:
49: /**
50: * Deletes attachment which are not assigned to any note
51: *
52: * @throws HibernateException
53: */
54: public void cleanUpOrphanAttachments() throws HibernateException,
55: SQLException {
56: Collection attachmentsToDelete = getOrphanAttachments();
57: LOG.debug("Deleting " + attachmentsToDelete.size()
58: + " attachments.");
59: for (Iterator iterator = attachmentsToDelete.iterator(); iterator
60: .hasNext();) {
61: Integer fileId = (Integer) iterator.next();
62: template.update(DELETE_ATTACHMENT_SQL,
63: new Object[] { fileId });
64: LOG.debug("Deleting file id: " + fileId);
65: }
66: }
67:
68: private Collection getNoteIdsWithFakeAttachment() {
69: return template.queryForList(
70: NOTE_IDS_WITH_FAKE_ATTACHMENTS_QUERY, Integer.class);
71: }
72:
73: private Collection getOrphanAttachments() {
74: return template.queryForList(ORPHAN_ATTACHMENTS_QUERY,
75: Integer.class);
76:
77: }
78:
79: public static void main(String[] args) throws Exception {
80: new CleanUpAttachments().run();
81: }
82: }
|