BitstreamInfoDAO


1   /*
2    * Copyright (c) 2004-2005, Hewlett-Packard Company and Massachusetts
3    * Institute of Technology.  All rights reserved.
4    *
5    * Redistribution and use in source and binary forms, with or without
6    * modification, are permitted provided that the following conditions are
7    * met:
8    *
9    * - Redistributions of source code must retain the above copyright
10   * notice, this list of conditions and the following disclaimer.
11   *
12   * - Redistributions in binary form must reproduce the above copyright
13   * notice, this list of conditions and the following disclaimer in the
14   * documentation and/or other materials provided with the distribution.
15   *
16   * - Neither the name of the Hewlett-Packard Company nor the name of the
17   * Massachusetts Institute of Technology nor the names of their
18   * contributors may be used to endorse or promote products derived from
19   * this software without specific prior written permission.
20   *
21   * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
22   * ``AS IS'' AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT
23   * LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR
24   * A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT
25   * HOLDERS OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT,
26   * INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING,
27   * BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS
28   * OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
29   * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR
30   * TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE
31   * USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH
32   * DAMAGE.
33   */
34  package org.dspace.checker;
35  
36  import java.sql.Connection  ;
37  import java.sql.PreparedStatement  ;
38  import java.sql.ResultSet  ;
39  import java.sql.SQLException  ;
40  import java.sql.Timestamp  ;
41  import java.util.ArrayList  ;
42  import java.util.Date  ;
43  import java.util.List  ;
44  
45  import org.apache.log4j.Logger;
46  import org.dspace.storage.rdbms.DatabaseManager;
47  
48  /**
49   * <p>
50   * Database Access Object for bitstream information (metadata). Also performs
51   * the needed insert/update/delete commands on the database for the checksum
52   * checker.
53   * </p>
54   * 
55   * @author Jim Downing
56   * @author Grace Carpenter
57   * @author Nathan Sarr
58   * 
59   */
60  public final class BitstreamInfoDAO extends DAOSupport
61  {
62      /**
63       * This value should be returned by <code>next()</code> to indicate that
64       * there are no more values.
65       */
66      public static int SENTINEL = -1;
67  
68      /** Query that gets bitstream information for a specified ID. */
69      private static final String   FIND_BY_BITSTREAM_ID = "select bitstream.deleted, bitstream.store_number, bitstream.size_bytes, "
70              + "bitstreamformatregistry.short_description, bitstream.bitstream_id,  "
71              + "bitstream.user_format_description, bitstream.internal_id, "
72              + "bitstream.source, bitstream.checksum_algorithm, bitstream.checksum, "
73              + "bitstream.name, most_recent_checksum.last_process_end_date,"
74              + "most_recent_checksum.to_be_processed "
75              + "from bitstream left outer join bitstreamformatregistry on "
76              + "bitstream.bitstream_format_id = bitstreamformatregistry.bitstream_format_id, "
77              + "most_recent_checksum "
78              + "where bitstream.bitstream_id = ? "
79              + "and bitstream.bitstream_id = most_recent_checksum.bitstream_id;";
80  
81      /**
82       * Query that selects bitstream IDs from bitstream table that are not yet in
83       * the most_recent_checksum table, and inserts them into
84       * most_recent_checksum.
85       */
86      private static final String   INSERT_MISSING_CHECKSUM_BITSTREAMS = "insert into most_recent_checksum ( "
87              + "bitstream_id, to_be_processed, expected_checksum, current_checksum, "
88              + "last_process_start_date, last_process_end_date, "
89              + "checksum_algorithm, matched_prev_checksum, result ) "
90              + "select bitstream.bitstream_id, "
91              + "CASE WHEN bitstream.deleted = false THEN true ELSE false END, "
92              + "CASE WHEN bitstream.checksum IS NULL THEN '' ELSE bitstream.checksum END, "
93              + "CASE WHEN bitstream.checksum IS NULL THEN '' ELSE bitstream.checksum END, "
94              + "?, ?, CASE WHEN bitstream.checksum_algorithm IS NULL "
95              + "THEN 'MD5' ELSE bitstream.checksum_algorithm END, true, "
96              + "CASE WHEN bitstream.deleted = true THEN 'BITSTREAM_MARKED_DELETED' else 'CHECKSUM_MATCH' END "
97              + "from bitstream where not exists( "
98              + "select 'x' from most_recent_checksum "
99              + "where most_recent_checksum.bitstream_id = bitstream.bitstream_id );";
100 
101     /**
102      * Query that updates most_recent_checksum table with checksum result for
103      * specified bitstream ID.
104      */
105     private static final String   UPDATE_CHECKSUM = "UPDATE  most_recent_checksum "
106             + "SET current_checksum = ?, expected_checksum = ?, matched_prev_checksum = ?, to_be_processed= ?, "
107             + "last_process_start_date=?, last_process_end_date=?, result=? WHERE bitstream_id = ? ";
108 
109     /**
110      * Deletes from the most_recent_checksum where the bitstream id is found
111      */
112     private static final String   DELETE_BITSTREAM_INFO = "Delete from most_recent_checksum "
113             + "where bitstream_id = ?";
114 
115     /**
116      * This selects the next bitstream in order of last processing end date. The
117      * timestamp is truncated to milliseconds this is because the Date for java
118      * does not support nanoseconds and milliseconds were considered accurate
119      * enough
120      */
121     public static final String   GET_OLDEST_BITSTREAM = "select bitstream_id  "
122             + "from most_recent_checksum " + "where to_be_processed = true "
123             + "order by date_trunc('milliseconds', last_process_end_date), "
124             + "bitstream_id " + "ASC LIMIT 1";
125 
126     /**
127      * Selects the next bitstream in order of last processing end date, ensuring
128      * that no bitstream is checked more than once since the date parameter
129      * used.
130      */
131     public static final String   GET_OLDEST_BITSTREAM_DATE = "select bitstream_id  "
132             + "from most_recent_checksum "
133             + "where to_be_processed = true "
134             + "and last_process_start_date < ? "
135             + "order by date_trunc('milliseconds', last_process_end_date), "
136             + "bitstream_id " + "ASC LIMIT 1";
137 
138     /** SQL query to retrieve bitstreams for a given item. */
139     private static final String   ITEM_BITSTREAMS = "SELECT b2b.bitstream_id "
140             + "FROM bundle2bitstream b2b, item2bundle i2b WHERE "
141             + "b2b.bundle_id=i2b.bundle_id AND i2b.item_id=?";
142 
143     /** SQL query to retrieve bitstreams for a given collection. */
144     private static final String   COLLECTION_BITSTREAMS = "SELECT b2b.bitstream_id "
145             + "FROM bundle2bitstream b2b, item2bundle i2b, collection2item c2i WHERE "
146             + "b2b.bundle_id=i2b.bundle_id AND c2i.item_id=i2b.item_id AND c2i.collection_id=?";
147 
148     /** SQL query to retrieve bitstreams for a given community. */
149     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=?";
150 
151     /** Standard Log4J logger. */
152     private static final Logger LOG = Logger.getLogger(BitstreamInfoDAO.class);
153 
154     /**
155      * History data access object for checksum_history table
156      */
157     private ChecksumHistoryDAO checksumHistoryDAO;
158 
159     /**
160      * Default constructor
161      */
162     public BitstreamInfoDAO()
163     {
164         checksumHistoryDAO = new ChecksumHistoryDAO();
165     }
166 
167     /**
168      * Updates most_recent_checksum with latest checksum and result of
169      * comparison with previous checksum.
170      * 
171      * @param info
172      *            The BitstreamInfo to update.
173      * 
174      * @throws IllegalArgumentException
175      *             if the BitstreamInfo given is null.
176      */
177     public void update(BitstreamInfo info)
178     {
179         if (info == null)
180         {
181             throw new IllegalArgumentException  (
182                     "BitstreamInfo parameter may not be null");
183         }
184 
185         Connection   conn = null;
186         PreparedStatement   stmt = null;
187 
188         try
189         {
190             conn = DatabaseManager.getConnection();
191             stmt = conn.prepareStatement(UPDATE_CHECKSUM);
192             stmt.setString(1, (info.getCalculatedChecksum() != null) ? info
193                     .getCalculatedChecksum() : "");
194             stmt.setString(2, info.getStoredChecksum());
195             stmt.setBoolean(3, ChecksumCheckResults.CHECKSUM_MATCH.equals(info
196                     .getChecksumCheckResult()));
197             stmt.setBoolean(4, info.getToBeProcessed());
198             stmt.setTimestamp(5, new Timestamp  (info.getProcessStartDate()
199                     .getTime()));
200             stmt.setTimestamp(6, new Timestamp  (info.getProcessEndDate()
201                     .getTime()));
202             stmt.setString(7, info.getChecksumCheckResult());
203             stmt.setInt(8, info.getBitstreamId());
204             stmt.executeUpdate();
205             conn.commit();
206         }
207         catch (SQLException   e)
208         {
209             LOG.error("Problem updating checksum row. " + e.getMessage(), e);
210             throw new RuntimeException  ("Problem updating checksum row. "
211                     + e.getMessage(), e);
212         }
213         finally
214         {
215             cleanup(stmt, conn);
216         }
217     }
218 
219     /**
220      * Find a bitstream by its id.
221      * 
222      * @param id
223      *            the bitstream id
224      * 
225      * @return the bitstream information needed for checksum validation. Returns
226      *         null if bitstream info isn't found.
227      */
228     public BitstreamInfo findByBitstreamId(int id)
229     {
230         Connection   conn = null;
231         BitstreamInfo info = null;
232         PreparedStatement   prepStmt = null;
233 
234         try
235         {
236             // create the connection and execute the statement
237             conn = DatabaseManager.getConnection();
238 
239             prepStmt = conn.prepareStatement(FIND_BY_BITSTREAM_ID);
240 
241             prepStmt.setInt(1, id);
242 
243             ResultSet   rs = prepStmt.executeQuery();
244 
245             // if the bitstream is found return it
246             if (rs.next())
247             {
248                 info = new BitstreamInfo(rs.getBoolean("deleted"), rs
249                         .getInt("store_number"), rs.getInt("size_bytes"), rs
250                         .getString("short_description"), rs
251                         .getInt("bitstream_id"), rs
252                         .getString("user_format_description"), rs
253                         .getString("internal_id"), rs.getString("source"), rs
254                         .getString("checksum_algorithm"), rs
255                         .getString("checksum"), rs.getString("name"), rs
256                         .getTimestamp("last_process_end_date"), rs
257                         .getBoolean("to_be_processed"), new Date  ());
258             }
259         }
260         catch (SQLException   e)
261         {
262             LOG.warn("Bitstream metadata could not be retrieved. "
263                     + e.getMessage(), e);
264         }
265         finally
266         {
267             cleanup(prepStmt, conn);
268         }
269 
270         return info;
271     }
272 
273     /**
274      * Queries the bitstream table for bitstream IDs that are not yet in the
275      * most_recent_checksum table, and inserts them into the
276      * most_recent_checksum and checksum_history tables.
277      */
278     public void updateMissingBitstreams()
279     {
280         Connection   conn = null;
281         PreparedStatement   stmt = null;
282 
283         try
284         {
285             LOG.debug("updating missing bitstreams");
286             conn = DatabaseManager.getConnection();
287             stmt = conn.prepareStatement(INSERT_MISSING_CHECKSUM_BITSTREAMS);
288             stmt.setTimestamp(1, new java.sql.Timestamp  (new Date  ().getTime()));
289             stmt.setTimestamp(2, new java.sql.Timestamp  (new Date  ().getTime()));
290             stmt.executeUpdate();
291 
292             checksumHistoryDAO.updateMissingBitstreams(conn);
293             conn.commit();
294         }
295         catch (SQLException   e)
296         {
297             LOG.error(
298                     "Problem inserting missing bitstreams. " + e.getMessage(),
299                     e);
300             throw new RuntimeException  ("Problem inserting missing bitstreams. "
301                     + e.getMessage(), e);
302         }
303         finally
304         {
305             cleanup(stmt, conn);
306         }
307     }
308 
309     /**
310      * Deletes the bitstream from the most_recent_checksum table if it exist.
311      * 
312      * @param id
313      *            the bitstream id.
314      * 
315      * @return number of records deleted
316      */
317     protected int deleteBitstreamInfo(int id, Connection   conn)
318     {
319         PreparedStatement   stmt = null;
320 
321         int numDeleted = 0;
322 
323         try
324         {
325             stmt = conn.prepareStatement(DELETE_BITSTREAM_INFO);
326             stmt.setInt(1, id);
327 
328             numDeleted = stmt.executeUpdate();
329 
330             if (numDeleted > 1)
331             {
332                 conn.rollback();
333                 throw new IllegalStateException  (
334                         "Too many rows deleted! Number of rows deleted: "
335                                 + numDeleted
336                                 + " only one row should be deleted for bitstream id "
337                                 + id);
338             }
339         }
340         catch (SQLException   e)
341         {
342             LOG.error("Problem deleting bitstream. " + e.getMessage(), e);
343             throw new RuntimeException  ("Problem deleting bitstream. "
344                     + e.getMessage(), e);
345         }
346         finally
347         {
348             cleanup(stmt);
349         }
350 
351         return numDeleted;
352     }
353 
354     public int deleteBitstreamInfoWithHistory(int id)
355     {
356         Connection   conn = null;
357         int numDeleted = 0;
358 
359         try
360         {
361             conn = DatabaseManager.getConnection();
362             deleteBitstreamInfo(id, conn);
363             checksumHistoryDAO.deleteHistoryForBitstreamInfo(id, conn);
364             conn.commit();
365         }
366         catch (SQLException   e)
367         {
368             LOG.error("Problem deleting bitstream. " + e.getMessage(), e);
369             throw new RuntimeException  ("Problem deleting bitstream. "
370                     + e.getMessage(), e);
371         }
372         finally
373         {
374             cleanup(conn);
375         }
376 
377         return numDeleted;
378 
379     }
380 
381     /**
382      * Get the oldest bitstream in the most recent checksum table. If more than
383      * one found the first one in the result set is returned.
384      * 
385      * @return the bitstream id or -1 if the no bitstreams are found
386      * 
387      */
388     public int getOldestBitstream()
389     {
390         Connection   conn = null;
391         PreparedStatement   prepStmt = null;
392         ResultSet   rs = null;
393 
394         try
395         {
396 
397             conn = DatabaseManager.getConnection();
398             prepStmt = conn.prepareStatement(GET_OLDEST_BITSTREAM);
399             rs = prepStmt.executeQuery();
400             if (rs.next())
401             {
402                 return rs.getInt(1);
403             }
404             else
405             {
406                 return SENTINEL;
407             }
408         }
409         catch (SQLException   e)
410         {
411             LOG.error("Problem with get oldest bitstream " + e.getMessage(), e);
412             throw new RuntimeException  ("Oldest bitstream error. "
413                     + e.getMessage(), e);
414 
415         }
416         finally
417         {
418             cleanup(prepStmt, conn);
419 
420         }
421     }
422 
423     /**
424      * Returns the oldest bistream that in the set of bitstreams that are less
425      * than the specified date. If no bitstreams are found -1 is returned.
426      * 
427      * @param lessThanDate
428      * @return id of olded bitstream or -1 if not bistreams are found
429      */
430     public int getOldestBitstream(Timestamp   lessThanDate)
431     {
432         Connection   conn = null;
433         PreparedStatement   prepStmt = null;
434         ResultSet   rs = null;
435 
436         try
437         {
438             conn = DatabaseManager.getConnection();
439             prepStmt = conn.prepareStatement(GET_OLDEST_BITSTREAM_DATE);
440             prepStmt.setTimestamp(1, lessThanDate);
441             rs = prepStmt.executeQuery();
442             if (rs.next())
443             {
444                 return rs.getInt(1);
445             }
446             else
447             {
448                 return SENTINEL;
449             }
450         }
451         catch (SQLException   e)
452         {
453             LOG.error("get oldest bitstream less than date " + e.getMessage(),
454                     e);
455             throw new RuntimeException  ("get oldest bitstream less than date. "
456                     + e.getMessage(), e);
457 
458         }
459         finally
460         {
461             cleanup(prepStmt, conn);
462 
463         }
464     }
465 
466     /**
467      * Get the bitstream ids for a given Item
468      * 
469      * @param itemId
470      * @return the list of bitstream ids for this item
471      */
472     public List   getItemBitstreams(int itemId)
473     {
474         List   ids = new ArrayList  ();
475 
476         Connection   conn = null;
477         PreparedStatement   ps = null;
478         ResultSet   rs = null;
479 
480         try
481         {
482             conn = DatabaseManager.getConnection();
483             ps = conn.prepareStatement(ITEM_BITSTREAMS);
484             ps.setInt(1, itemId);
485 
486             rs = ps.executeQuery();
487 
488             while (rs.next())
489             {
490                 ids.add(new Integer  (rs.getInt(1)));
491             }
492 
493         }
494         catch (SQLException   e)
495         {
496             LOG.error("get item bitstreams " + e.getMessage(), e);
497             throw new RuntimeException  (
498                     "get item bitstreams. " + e.getMessage(), e);
499 
500         }
501         finally
502         {
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     {
517         List   ids = new ArrayList  ();
518 
519         Connection   conn = null;
520         PreparedStatement   ps = null;
521         ResultSet   rs = null;
522 
523         try
524         {
525             conn = DatabaseManager.getConnection();
526             ps = conn.prepareStatement(COLLECTION_BITSTREAMS);
527             ps.setInt(1, collectionId);
528 
529             rs = ps.executeQuery();
530 
531             while (rs.next())
532             {
533                 ids.add(new Integer  (rs.getInt(1)));
534             }
535 
536         }
537         catch (SQLException   e)
538         {
539             LOG.error("get item bitstreams " + e.getMessage(), e);
540             throw new RuntimeException  (
541                     "get item bitstreams. " + e.getMessage(), e);
542 
543         }
544         finally
545         {
546             cleanup(ps, conn, rs);
547         }
548 
549         return ids;
550     }
551 
552     /**
553      * Get the bitstream ids for a given community
554      * 
555      * @param itemId
556      * @return the list of bitstream ids for this item
557      */
558     public List   getCommunityBitstreams(int communityId)
559     {
560         List   ids = new ArrayList  ();
561 
562         Connection   conn = null;
563         PreparedStatement   ps = null;
564         ResultSet   rs = null;
565 
566         try
567         {
568             conn = DatabaseManager.getConnection();
569             ps = conn.prepareStatement(COMMUNITY_BITSTREAMS);
570             ps.setInt(1, communityId);
571 
572             rs = ps.executeQuery();
573 
574             while (rs.next())
575             {
576                 ids.add(new Integer  (rs.getInt(1)));
577             }
578 
579         }
580         catch (SQLException   e)
581         {
582             LOG.error("get item bitstreams " + e.getMessage(), e);
583             throw new RuntimeException  (
584                     "get item bitstreams. " + e.getMessage(), e);
585 
586         }
587         finally
588         {
589             cleanup(ps, conn, rs);
590         }
591 
592         return ids;
593     }
594 
595 }
596
A to Z: JavaDoc & Examples Daily Java News & Articles Open Source Projects Open Source Codes Free Computer Books Remove Frame
Popular Tags