001: /*
002: * Copyright (c) 2004-2005, Hewlett-Packard Company and Massachusetts
003: * Institute of Technology. All rights reserved.
004: *
005: * Redistribution and use in source and binary forms, with or without
006: * modification, are permitted provided that the following conditions are
007: * met:
008: *
009: * - Redistributions of source code must retain the above copyright
010: * notice, this list of conditions and the following disclaimer.
011: *
012: * - Redistributions in binary form must reproduce the above copyright
013: * notice, this list of conditions and the following disclaimer in the
014: * documentation and/or other materials provided with the distribution.
015: *
016: * - Neither the name of the Hewlett-Packard Company nor the name of the
017: * Massachusetts Institute of Technology nor the names of their
018: * contributors may be used to endorse or promote products derived from
019: * this software without specific prior written permission.
020: *
021: * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
022: * ``AS IS'' AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT
023: * LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR
024: * A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT
025: * HOLDERS OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT,
026: * INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING,
027: * BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS
028: * OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
029: * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR
030: * TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE
031: * USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH
032: * DAMAGE.
033: */
034: package org.dspace.checker;
035:
036: import java.sql.Connection;
037: import java.sql.PreparedStatement;
038: import java.sql.ResultSet;
039: import java.sql.SQLException;
040: import java.sql.Timestamp;
041: import java.util.ArrayList;
042: import java.util.Date;
043: import java.util.List;
044:
045: import org.apache.log4j.Logger;
046: import org.dspace.core.ConfigurationManager;
047: import org.dspace.storage.rdbms.DatabaseManager;
048:
049: /**
050: * <p>
051: * Database Access Object for bitstream information (metadata). Also performs
052: * the needed insert/update/delete commands on the database for the checksum
053: * checker.
054: * </p>
055: *
056: * @author Jim Downing
057: * @author Grace Carpenter
058: * @author Nathan Sarr
059: *
060: */
061: public final class BitstreamInfoDAO extends DAOSupport {
062: /**
063: * This value should be returned by <code>next()</code> to indicate that
064: * there are no more values.
065: */
066: public static int SENTINEL = -1;
067:
068: /** Query that gets bitstream information for a specified ID. */
069: private static final String FIND_BY_BITSTREAM_ID = "select bitstream.deleted, bitstream.store_number, bitstream.size_bytes, "
070: + "bitstreamformatregistry.short_description, bitstream.bitstream_id, "
071: + "bitstream.user_format_description, bitstream.internal_id, "
072: + "bitstream.source, bitstream.checksum_algorithm, bitstream.checksum, "
073: + "bitstream.name, most_recent_checksum.last_process_end_date,"
074: + "most_recent_checksum.to_be_processed "
075: + "from bitstream left outer join bitstreamformatregistry on "
076: + "bitstream.bitstream_format_id = bitstreamformatregistry.bitstream_format_id, "
077: + "most_recent_checksum "
078: + "where bitstream.bitstream_id = ? "
079: + "and bitstream.bitstream_id = most_recent_checksum.bitstream_id";
080:
081: /**
082: * Query that selects bitstream IDs from bitstream table that are not yet in
083: * the most_recent_checksum table, and inserts them into
084: * most_recent_checksum.
085: */
086: private static final String INSERT_MISSING_CHECKSUM_BITSTREAMS = "insert into most_recent_checksum ( "
087: + "bitstream_id, to_be_processed, expected_checksum, current_checksum, "
088: + "last_process_start_date, last_process_end_date, "
089: + "checksum_algorithm, matched_prev_checksum, result ) "
090: + "select bitstream.bitstream_id, "
091: + "CASE WHEN bitstream.deleted = false THEN true ELSE false END, "
092: + "CASE WHEN bitstream.checksum IS NULL THEN '' ELSE bitstream.checksum END, "
093: + "CASE WHEN bitstream.checksum IS NULL THEN '' ELSE bitstream.checksum END, "
094: + "?, ?, CASE WHEN bitstream.checksum_algorithm IS NULL "
095: + "THEN 'MD5' ELSE bitstream.checksum_algorithm END, true, "
096: + "CASE WHEN bitstream.deleted = true THEN 'BITSTREAM_MARKED_DELETED' else 'CHECKSUM_MATCH' END "
097: + "from bitstream where not exists( "
098: + "select 'x' from most_recent_checksum "
099: + "where most_recent_checksum.bitstream_id = bitstream.bitstream_id )";
100:
101: private static final String INSERT_MISSING_CHECKSUM_BITSTREAMS_ORACLE = "insert into most_recent_checksum ( "
102: + "bitstream_id, to_be_processed, expected_checksum, current_checksum, "
103: + "last_process_start_date, last_process_end_date, "
104: + "checksum_algorithm, matched_prev_checksum, result ) "
105: + "select bitstream.bitstream_id, "
106: + "CASE WHEN bitstream.deleted = 0 THEN 1 ELSE 0 END, "
107: + "CASE WHEN bitstream.checksum IS NULL THEN '' ELSE bitstream.checksum END, "
108: + "CASE WHEN bitstream.checksum IS NULL THEN '' ELSE bitstream.checksum END, "
109: + "? AS last_process_start_date, ? AS last_process_end_date, CASE WHEN bitstream.checksum_algorithm IS NULL "
110: + "THEN 'MD5' ELSE bitstream.checksum_algorithm END, 1, "
111: + "CASE WHEN bitstream.deleted = 1 THEN 'BITSTREAM_MARKED_DELETED' else 'CHECKSUM_MATCH' END "
112: + "from bitstream where not exists( "
113: + "select 'x' from most_recent_checksum "
114: + "where most_recent_checksum.bitstream_id = bitstream.bitstream_id )";
115:
116: /**
117: * Query that updates most_recent_checksum table with checksum result for
118: * specified bitstream ID.
119: */
120: private static final String UPDATE_CHECKSUM = "UPDATE most_recent_checksum "
121: + "SET current_checksum = ?, expected_checksum = ?, matched_prev_checksum = ?, to_be_processed= ?, "
122: + "last_process_start_date=?, last_process_end_date=?, result=? WHERE bitstream_id = ? ";
123:
124: /**
125: * Deletes from the most_recent_checksum where the bitstream id is found
126: */
127: private static final String DELETE_BITSTREAM_INFO = "Delete from most_recent_checksum "
128: + "where bitstream_id = ?";
129:
130: /**
131: * This selects the next bitstream in order of last processing end date. The
132: * timestamp is truncated to milliseconds this is because the Date for java
133: * does not support nanoseconds and milliseconds were considered accurate
134: * enough
135: */
136: public static final String GET_OLDEST_BITSTREAM = "select bitstream_id "
137: + "from most_recent_checksum "
138: + "where to_be_processed = true "
139: + "order by date_trunc('milliseconds', last_process_end_date), "
140: + "bitstream_id " + "ASC LIMIT 1";
141:
142: public static final String GET_OLDEST_BITSTREAM_ORACLE = "SELECT bitstream_id FROM (select bitstream_id "
143: + "from most_recent_checksum "
144: + "where to_be_processed = 1 "
145: + "order by trunc(last_process_end_date, 'mi'), "
146: + "bitstream_id " + "ASC) WHERE rownum=1";
147:
148: /**
149: * Selects the next bitstream in order of last processing end date, ensuring
150: * that no bitstream is checked more than once since the date parameter
151: * used.
152: */
153: public static final String GET_OLDEST_BITSTREAM_DATE = "select bitstream_id "
154: + "from most_recent_checksum "
155: + "where to_be_processed = true "
156: + "and last_process_start_date < ? "
157: + "order by date_trunc('milliseconds', last_process_end_date), "
158: + "bitstream_id " + "ASC LIMIT 1";
159:
160: public static final String GET_OLDEST_BITSTREAM_DATE_ORACLE = "SELECT bitstream_id FROM (select bitstream_id "
161: + "from most_recent_checksum "
162: + "where to_be_processed = 1 "
163: + "and last_process_start_date < ? "
164: + "order by trunc(last_process_end_date, 'mi'), "
165: + "bitstream_id " + "ASC) WHERE rownum=1";
166:
167: /** SQL query to retrieve bitstreams for a given item. */
168: private static final String ITEM_BITSTREAMS = "SELECT b2b.bitstream_id "
169: + "FROM bundle2bitstream b2b, item2bundle i2b WHERE "
170: + "b2b.bundle_id=i2b.bundle_id AND i2b.item_id=?";
171:
172: /** SQL query to retrieve bitstreams for a given collection. */
173: private static final String COLLECTION_BITSTREAMS = "SELECT b2b.bitstream_id "
174: + "FROM bundle2bitstream b2b, item2bundle i2b, collection2item c2i WHERE "
175: + "b2b.bundle_id=i2b.bundle_id AND c2i.item_id=i2b.item_id AND c2i.collection_id=?";
176:
177: /** SQL query to retrieve bitstreams for a given community. */
178: private static final String COMMUNITY_BITSTREAMS = "SELECT b2b.bitstream_id FROM bundle2bitstream b2b, item2bundle i2b, collection2item c2i, community2collection c2c WHERE b2b.bundle_id=i2b.bundle_id AND c2i.item_id=i2b.item_id AND c2c.collection_id=c2i.collection_id AND c2c.community_id=?";
179:
180: /** Standard Log4J logger. */
181: private static final Logger LOG = Logger
182: .getLogger(BitstreamInfoDAO.class);
183:
184: /**
185: * History data access object for checksum_history table
186: */
187: private ChecksumHistoryDAO checksumHistoryDAO;
188:
189: /**
190: * Default constructor
191: */
192: public BitstreamInfoDAO() {
193: checksumHistoryDAO = new ChecksumHistoryDAO();
194: }
195:
196: /**
197: * Updates most_recent_checksum with latest checksum and result of
198: * comparison with previous checksum.
199: *
200: * @param info
201: * The BitstreamInfo to update.
202: *
203: * @throws IllegalArgumentException
204: * if the BitstreamInfo given is null.
205: */
206: public void update(BitstreamInfo info) {
207: if (info == null) {
208: throw new IllegalArgumentException(
209: "BitstreamInfo parameter may not be null");
210: }
211:
212: Connection conn = null;
213: PreparedStatement stmt = null;
214:
215: try {
216: conn = DatabaseManager.getConnection();
217: stmt = conn.prepareStatement(UPDATE_CHECKSUM);
218: stmt.setString(1,
219: (info.getCalculatedChecksum() != null) ? info
220: .getCalculatedChecksum() : "");
221: stmt.setString(2, info.getStoredChecksum());
222: stmt.setBoolean(3, ChecksumCheckResults.CHECKSUM_MATCH
223: .equals(info.getChecksumCheckResult()));
224: stmt.setBoolean(4, info.getToBeProcessed());
225: stmt.setTimestamp(5, new Timestamp(info
226: .getProcessStartDate().getTime()));
227: stmt.setTimestamp(6, new Timestamp(info.getProcessEndDate()
228: .getTime()));
229: stmt.setString(7, info.getChecksumCheckResult());
230: stmt.setInt(8, info.getBitstreamId());
231: stmt.executeUpdate();
232: conn.commit();
233: } catch (SQLException e) {
234: LOG.error("Problem updating checksum row. "
235: + e.getMessage(), e);
236: throw new RuntimeException(
237: "Problem updating checksum row. " + e.getMessage(),
238: e);
239: } finally {
240: cleanup(stmt, conn);
241: }
242: }
243:
244: /**
245: * Find a bitstream by its id.
246: *
247: * @param id
248: * the bitstream id
249: *
250: * @return the bitstream information needed for checksum validation. Returns
251: * null if bitstream info isn't found.
252: */
253: public BitstreamInfo findByBitstreamId(int id) {
254: Connection conn = null;
255: BitstreamInfo info = null;
256: PreparedStatement prepStmt = null;
257:
258: try {
259: // create the connection and execute the statement
260: conn = DatabaseManager.getConnection();
261:
262: prepStmt = conn.prepareStatement(FIND_BY_BITSTREAM_ID);
263:
264: prepStmt.setInt(1, id);
265:
266: ResultSet rs = prepStmt.executeQuery();
267:
268: // if the bitstream is found return it
269: if (rs.next()) {
270: info = new BitstreamInfo(rs.getBoolean("deleted"), rs
271: .getInt("store_number"), rs
272: .getInt("size_bytes"), rs
273: .getString("short_description"), rs
274: .getInt("bitstream_id"), rs
275: .getString("user_format_description"), rs
276: .getString("internal_id"), rs
277: .getString("source"), rs
278: .getString("checksum_algorithm"), rs
279: .getString("checksum"), rs.getString("name"),
280: rs.getTimestamp("last_process_end_date"), rs
281: .getBoolean("to_be_processed"),
282: new Date());
283: }
284: } catch (SQLException e) {
285: LOG.warn("Bitstream metadata could not be retrieved. "
286: + e.getMessage(), e);
287: } finally {
288: cleanup(prepStmt, conn);
289: }
290:
291: return info;
292: }
293:
294: /**
295: * Queries the bitstream table for bitstream IDs that are not yet in the
296: * most_recent_checksum table, and inserts them into the
297: * most_recent_checksum and checksum_history tables.
298: */
299: public void updateMissingBitstreams() {
300: Connection conn = null;
301: PreparedStatement stmt = null;
302:
303: try {
304: LOG.debug("updating missing bitstreams");
305: conn = DatabaseManager.getConnection();
306: if ("oracle".equals(ConfigurationManager
307: .getProperty("db.name")))
308: stmt = conn
309: .prepareStatement(INSERT_MISSING_CHECKSUM_BITSTREAMS_ORACLE);
310: else
311: stmt = conn
312: .prepareStatement(INSERT_MISSING_CHECKSUM_BITSTREAMS);
313: stmt.setTimestamp(1, new java.sql.Timestamp(new Date()
314: .getTime()));
315: stmt.setTimestamp(2, new java.sql.Timestamp(new Date()
316: .getTime()));
317: stmt.executeUpdate();
318:
319: checksumHistoryDAO.updateMissingBitstreams(conn);
320: conn.commit();
321: } catch (SQLException e) {
322: LOG.error("Problem inserting missing bitstreams. "
323: + e.getMessage(), e);
324: throw new RuntimeException(
325: "Problem inserting missing bitstreams. "
326: + e.getMessage(), e);
327: } finally {
328: cleanup(stmt, conn);
329: }
330: }
331:
332: /**
333: * Deletes the bitstream from the most_recent_checksum table if it exist.
334: *
335: * @param id
336: * the bitstream id.
337: *
338: * @return number of records deleted
339: */
340: protected int deleteBitstreamInfo(int id, Connection conn) {
341: PreparedStatement stmt = null;
342:
343: int numDeleted = 0;
344:
345: try {
346: stmt = conn.prepareStatement(DELETE_BITSTREAM_INFO);
347: stmt.setInt(1, id);
348:
349: numDeleted = stmt.executeUpdate();
350:
351: if (numDeleted > 1) {
352: conn.rollback();
353: throw new IllegalStateException(
354: "Too many rows deleted! Number of rows deleted: "
355: + numDeleted
356: + " only one row should be deleted for bitstream id "
357: + id);
358: }
359: } catch (SQLException e) {
360: LOG.error("Problem deleting bitstream. " + e.getMessage(),
361: e);
362: throw new RuntimeException("Problem deleting bitstream. "
363: + e.getMessage(), e);
364: } finally {
365: cleanup(stmt);
366: }
367:
368: return numDeleted;
369: }
370:
371: public int deleteBitstreamInfoWithHistory(int id) {
372: Connection conn = null;
373: int numDeleted = 0;
374:
375: try {
376: conn = DatabaseManager.getConnection();
377: deleteBitstreamInfo(id, conn);
378: checksumHistoryDAO.deleteHistoryForBitstreamInfo(id, conn);
379: conn.commit();
380: } catch (SQLException e) {
381: LOG.error("Problem deleting bitstream. " + e.getMessage(),
382: e);
383: throw new RuntimeException("Problem deleting bitstream. "
384: + e.getMessage(), e);
385: } finally {
386: cleanup(conn);
387: }
388:
389: return numDeleted;
390:
391: }
392:
393: /**
394: * Get the oldest bitstream in the most recent checksum table. If more than
395: * one found the first one in the result set is returned.
396: *
397: * @return the bitstream id or -1 if the no bitstreams are found
398: *
399: */
400: public int getOldestBitstream() {
401: Connection conn = null;
402: PreparedStatement prepStmt = null;
403: ResultSet rs = null;
404:
405: try {
406:
407: conn = DatabaseManager.getConnection();
408: if ("oracle".equals(ConfigurationManager
409: .getProperty("db.name")))
410: prepStmt = conn
411: .prepareStatement(GET_OLDEST_BITSTREAM_ORACLE);
412: else
413: prepStmt = conn.prepareStatement(GET_OLDEST_BITSTREAM);
414: rs = prepStmt.executeQuery();
415: if (rs.next()) {
416: return rs.getInt(1);
417: } else {
418: return SENTINEL;
419: }
420: } catch (SQLException e) {
421: LOG.error("Problem with get oldest bitstream "
422: + e.getMessage(), e);
423: throw new RuntimeException("Oldest bitstream error. "
424: + e.getMessage(), e);
425:
426: } finally {
427: cleanup(prepStmt, conn);
428:
429: }
430: }
431:
432: /**
433: * Returns the oldest bistream that in the set of bitstreams that are less
434: * than the specified date. If no bitstreams are found -1 is returned.
435: *
436: * @param lessThanDate
437: * @return id of olded bitstream or -1 if not bistreams are found
438: */
439: public int getOldestBitstream(Timestamp lessThanDate) {
440: Connection conn = null;
441: PreparedStatement prepStmt = null;
442: ResultSet rs = null;
443:
444: try {
445: conn = DatabaseManager.getConnection();
446: if ("oracle".equals(ConfigurationManager
447: .getProperty("db.name")))
448: prepStmt = conn
449: .prepareStatement(GET_OLDEST_BITSTREAM_DATE_ORACLE);
450: else
451: prepStmt = conn
452: .prepareStatement(GET_OLDEST_BITSTREAM_DATE);
453: prepStmt.setTimestamp(1, lessThanDate);
454: rs = prepStmt.executeQuery();
455: if (rs.next()) {
456: return rs.getInt(1);
457: } else {
458: return SENTINEL;
459: }
460: } catch (SQLException e) {
461: LOG.error("get oldest bitstream less than date "
462: + e.getMessage(), e);
463: throw new RuntimeException(
464: "get oldest bitstream less than date. "
465: + e.getMessage(), e);
466:
467: } finally {
468: cleanup(prepStmt, conn);
469:
470: }
471: }
472:
473: /**
474: * Get the bitstream ids for a given Item
475: *
476: * @param itemId
477: * @return the list of bitstream ids for this item
478: */
479: public List getItemBitstreams(int itemId) {
480: List ids = new ArrayList();
481:
482: Connection conn = null;
483: PreparedStatement ps = null;
484: ResultSet rs = null;
485:
486: try {
487: conn = DatabaseManager.getConnection();
488: ps = conn.prepareStatement(ITEM_BITSTREAMS);
489: ps.setInt(1, itemId);
490:
491: rs = ps.executeQuery();
492:
493: while (rs.next()) {
494: ids.add(new Integer(rs.getInt(1)));
495: }
496:
497: } catch (SQLException e) {
498: LOG.error("get item bitstreams " + e.getMessage(), e);
499: throw new RuntimeException("get item bitstreams. "
500: + e.getMessage(), e);
501:
502: } finally {
503: cleanup(ps, conn, rs);
504: }
505:
506: return ids;
507: }
508:
509: /**
510: * Get the bitstream ids for a given collection
511: *
512: * @param itemId
513: * @return the list of bitstream ids for this item
514: */
515: public List getCollectionBitstreams(int collectionId) {
516: List ids = new ArrayList();
517:
518: Connection conn = null;
519: PreparedStatement ps = null;
520: ResultSet rs = null;
521:
522: try {
523: conn = DatabaseManager.getConnection();
524: ps = conn.prepareStatement(COLLECTION_BITSTREAMS);
525: ps.setInt(1, collectionId);
526:
527: rs = ps.executeQuery();
528:
529: while (rs.next()) {
530: ids.add(new Integer(rs.getInt(1)));
531: }
532:
533: } catch (SQLException e) {
534: LOG.error("get item bitstreams " + e.getMessage(), e);
535: throw new RuntimeException("get item bitstreams. "
536: + e.getMessage(), e);
537:
538: } finally {
539: cleanup(ps, conn, rs);
540: }
541:
542: return ids;
543: }
544:
545: /**
546: * Get the bitstream ids for a given community
547: *
548: * @param itemId
549: * @return the list of bitstream ids for this item
550: */
551: public List getCommunityBitstreams(int communityId) {
552: List ids = new ArrayList();
553:
554: Connection conn = null;
555: PreparedStatement ps = null;
556: ResultSet rs = null;
557:
558: try {
559: conn = DatabaseManager.getConnection();
560: ps = conn.prepareStatement(COMMUNITY_BITSTREAMS);
561: ps.setInt(1, communityId);
562:
563: rs = ps.executeQuery();
564:
565: while (rs.next()) {
566: ids.add(new Integer(rs.getInt(1)));
567: }
568:
569: } catch (SQLException e) {
570: LOG.error("get item bitstreams " + e.getMessage(), e);
571: throw new RuntimeException("get item bitstreams. "
572: + e.getMessage(), e);
573:
574: } finally {
575: cleanup(ps, conn, rs);
576: }
577:
578: return ids;
579: }
580:
581: }
|