On Thu, 2007-07-05 at 17:40 +0100, Jim Downing wrote:
> Hi Rafa,
>
> Rafa Carreres wrote:
> > Running [dspace-src]/bin/checker, I get this error:
> >
> > "Exception in thread "main" java.lang.RuntimeException: Problem
> > inserting missing bitstreams. ORA-00911: invalid character
> >
> > at
> > org.dspace.checker.BitstreamInfoDAO.updateMissingBitstreams(BitstreamInfoDAO.java:300)
> > at
> >
> > My configuration: Dspace 1.4.1 and Oracle. Previously, I have done a
> > cleanup of the system.
> >
> > Any idea?
>
>
> The query being run there is a fairly involved chunk of SQL
> (BitstreamInfoDAO.INSERT_MISSING_CHECKSUM_BITSTREAMS). I can't see any
> particularly unusual characters in there, so I'd guess that the
> timestamp parameters set in .updateMissingBitstreams() are causing the
> problem. If you can log the exact query that's being run at the Oracle
> end that might give you a clue. Anyone with Oracle-fu able to shed any
> light here?
Yep, the checker is pretty bust for Oracle - it's using boolean types,
and LIMIT keyword that don't exist in Oracle.
I've attached a patch to resolve these issues - it's a bit hacky, simply
adding additional variants of the query and switching based on the
configuration parameter (it's going to take too long to properly
Interface the DAOs).
Although, when I was debugging it, the particular invalid character
issue appeared to be the '?' for paramters... but Oracle supports that.
It may just be a quirk of trying to interactively debug the query, and
the PreparedStatement may have actually been falling over on the
comparison of a numeric field to true / false.
Anyway, patch (generated from/against the current 1.4.x branch) is
attached.
G
This email has been scanned by Postini.
For more information please visit http://www.postini.com
Index: src/org/dspace/checker/BitstreamInfoDAO.java
===================================================================
--- src/org/dspace/checker/BitstreamInfoDAO.java (revision 2043)
+++ src/org/dspace/checker/BitstreamInfoDAO.java (working copy)
@@ -43,6 +43,7 @@
import java.util.List;
import org.apache.log4j.Logger;
+import org.dspace.core.ConfigurationManager;
import org.dspace.storage.rdbms.DatabaseManager;
/**
@@ -98,6 +99,21 @@
+ "select 'x' from most_recent_checksum "
+ "where most_recent_checksum.bitstream_id = bitstream.bitstream_id );";
+ private static final String INSERT_MISSING_CHECKSUM_BITSTREAMS_ORACLE = "insert into most_recent_checksum ( "
+ + "bitstream_id, to_be_processed, expected_checksum, current_checksum, "
+ + "last_process_start_date, last_process_end_date, "
+ + "checksum_algorithm, matched_prev_checksum, result ) "
+ + "select bitstream.bitstream_id, "
+ + "CASE WHEN bitstream.deleted = 0 THEN 1 ELSE 0 END, "
+ + "CASE WHEN bitstream.checksum IS NULL THEN '' ELSE bitstream.checksum END, "
+ + "CASE WHEN bitstream.checksum IS NULL THEN '' ELSE bitstream.checksum END, "
+ + "?, ?, CASE WHEN bitstream.checksum_algorithm IS NULL "
+ + "THEN 'MD5' ELSE bitstream.checksum_algorithm END, 1, "
+ + "CASE WHEN bitstream.deleted = 1 THEN 'BITSTREAM_MARKED_DELETED' else 'CHECKSUM_MATCH' END "
+ + "from bitstream where not exists( "
+ + "select 'x' from most_recent_checksum "
+ + "where most_recent_checksum.bitstream_id = bitstream.bitstream_id );";
+
/**
* Query that updates most_recent_checksum table with checksum result for
* specified bitstream ID.
@@ -123,6 +139,11 @@
+ "order by date_trunc('milliseconds', last_process_end_date), "
+ "bitstream_id " + "ASC LIMIT 1";
+ public static final String GET_OLDEST_BITSTREAM_ORACLE = "SELECT bitstream_id FROM (select bitstream_id "
+ + "from most_recent_checksum " + "where to_be_processed = 1 "
+ + "order by date_trunc('milliseconds', last_process_end_date), "
+ + "bitstream_id " + "ASC) WHERE rownum=1";
+
/**
* Selects the next bitstream in order of last processing end date, ensuring
* that no bitstream is checked more than once since the date parameter
@@ -135,6 +156,13 @@
+ "order by date_trunc('milliseconds', last_process_end_date), "
+ "bitstream_id " + "ASC LIMIT 1";
+ public static final String GET_OLDEST_BITSTREAM_DATE_ORACLE = "SELECT bitstream_id FROM (select bitstream_id "
+ + "from most_recent_checksum "
+ + "where to_be_processed = 1 "
+ + "and last_process_start_date < ? "
+ + "order by date_trunc('milliseconds', last_process_end_date), "
+ + "bitstream_id " + "ASC) WHERE rownum=1";
+
/** SQL query to retrieve bitstreams for a given item. */
private static final String ITEM_BITSTREAMS = "SELECT b2b.bitstream_id "
+ "FROM bundle2bitstream b2b, item2bundle i2b WHERE "
@@ -284,7 +312,10 @@
{
LOG.debug("updating missing bitstreams");
conn = DatabaseManager.getConnection();
- stmt = conn.prepareStatement(INSERT_MISSING_CHECKSUM_BITSTREAMS);
+ if ("oracle".equals(ConfigurationManager.getProperty("db.name")))
+ stmt = conn.prepareStatement(INSERT_MISSING_CHECKSUM_BITSTREAMS_ORACLE);
+ else
+ stmt = conn.prepareStatement(INSERT_MISSING_CHECKSUM_BITSTREAMS);
stmt.setTimestamp(1, new java.sql.Timestamp(new Date().getTime()));
stmt.setTimestamp(2, new java.sql.Timestamp(new Date().getTime()));
stmt.executeUpdate();
@@ -395,7 +426,10 @@
{
conn = DatabaseManager.getConnection();
- prepStmt = conn.prepareStatement(GET_OLDEST_BITSTREAM);
+ if ("oracle".equals(ConfigurationManager.getProperty("db.name")))
+ prepStmt = conn.prepareStatement(GET_OLDEST_BITSTREAM_ORACLE);
+ else
+ prepStmt = conn.prepareStatement(GET_OLDEST_BITSTREAM);
rs = prepStmt.executeQuery();
if (rs.next())
{
@@ -436,7 +470,10 @@
try
{
conn = DatabaseManager.getConnection();
- prepStmt = conn.prepareStatement(GET_OLDEST_BITSTREAM_DATE);
+ if ("oracle".equals(ConfigurationManager.getProperty("db.name")))
+ prepStmt = conn.prepareStatement(GET_OLDEST_BITSTREAM_DATE_ORACLE);
+ else
+ prepStmt = conn.prepareStatement(GET_OLDEST_BITSTREAM_DATE);
prepStmt.setTimestamp(1, lessThanDate);
rs = prepStmt.executeQuery();
if (rs.next())
Index: src/org/dspace/checker/ChecksumHistoryDAO.java
===================================================================
--- src/org/dspace/checker/ChecksumHistoryDAO.java (revision 2043)
+++ src/org/dspace/checker/ChecksumHistoryDAO.java (working copy)
@@ -9,6 +9,7 @@
import java.util.Map;
import org.apache.log4j.Logger;
+import org.dspace.core.ConfigurationManager;
import org.dspace.storage.rdbms.DatabaseManager;
/**
@@ -45,6 +46,20 @@
+ "most_recent_checksum.bitstream_id = checksum_history.bitstream_id ) "
+ "and most_recent_checksum.bitstream_id = bitstream.bitstream_id;";
+ private static final String INSERT_MISSING_HISTORY_BITSTREAMS_ORACLE = "insert into checksum_history ( "
+ + "bitstream_id, process_start_date, "
+ + "process_end_date, checksum_expected, "
+ + "checksum_calculated, result ) "
+ + "select most_recent_checksum.bitstream_id, "
+ + "most_recent_checksum.last_process_start_date, "
+ + "most_recent_checksum.last_process_end_date, "
+ + "most_recent_checksum.expected_checksum, most_recent_checksum.expected_checksum, "
+ + "CASE WHEN bitstream.deleted = 1 THEN 'BITSTREAM_MARKED_DELETED' else 'CHECKSUM_MATCH' END "
+ + "from most_recent_checksum, bitstream where "
+ + "not exists( select 'x' from checksum_history where "
+ + "most_recent_checksum.bitstream_id = checksum_history.bitstream_id ) "
+ + "and most_recent_checksum.bitstream_id = bitstream.bitstream_id;";
+
/** Query that inserts results of recent check into the history table. */
private static final String INSERT_HISTORY = "insert into checksum_history ( bitstream_id, process_start_date, "
+ " process_end_date, checksum_expected, checksum_calculated, result ) "
@@ -156,7 +171,10 @@
PreparedStatement stmt = null;
try
{
- stmt = conn.prepareStatement(INSERT_MISSING_HISTORY_BITSTREAMS);
+ if ("oracle".equals(ConfigurationManager.getProperty("db.name")))
+ stmt = conn.prepareStatement(INSERT_MISSING_HISTORY_BITSTREAMS_ORACLE);
+ else
+ stmt = conn.prepareStatement(INSERT_MISSING_HISTORY_BITSTREAMS);
stmt.executeUpdate();
}
catch (SQLException e)
Index: src/org/dspace/checker/ReporterDAO.java
===================================================================
--- src/org/dspace/checker/ReporterDAO.java (revision 2043)
+++ src/org/dspace/checker/ReporterDAO.java (working copy)
@@ -42,6 +42,7 @@
import java.util.List;
import org.apache.log4j.Logger;
+import org.dspace.core.ConfigurationManager;
import org.dspace.storage.rdbms.DatabaseManager;
/**
@@ -86,6 +87,17 @@
+ "and most_recent_checksum.last_process_start_date < ? "
+ "order by most_recent_checksum.bitstream_id;";
+ public static final String DATE_RANGE_NOT_PROCESSED_BITSTREAMS_ORACLE = "select most_recent_checksum.bitstream_id, "
+ + "most_recent_checksum.last_process_start_date, most_recent_checksum.last_process_end_date, "
+ + "most_recent_checksum.expected_checksum, most_recent_checksum.current_checksum, "
+ + "result_description "
+ + "from checksum_results, most_recent_checksum "
+ + "where most_recent_checksum.to_be_processed = 0 "
+ + "and most_recent_checksum.result = checksum_results.result_code "
+ + "and most_recent_checksum.last_process_start_date >= ? "
+ + "and most_recent_checksum.last_process_start_date < ? "
+ + "order by most_recent_checksum.bitstream_id;";
+
/**
* Find all bitstreams that the checksum checker is unaware of
*/
@@ -195,8 +207,10 @@
// create the connection and execute the statement
conn = DatabaseManager.getConnection();
- prepStmt = conn
- .prepareStatement(DATE_RANGE_NOT_PROCESSED_BITSTREAMS);
+ if ("oracle".equals(ConfigurationManager.getProperty("db.name")))
+ prepStmt = conn.prepareStatement(DATE_RANGE_NOT_PROCESSED_BITSTREAMS_ORACLE);
+ else
+ prepStmt = conn.prepareStatement(DATE_RANGE_NOT_PROCESSED_BITSTREAMS);
prepStmt.setDate(1, new java.sql.Date(startDate.getTime()));
prepStmt.setDate(2, new java.sql.Date(endDate.getTime()));
-------------------------------------------------------------------------
This SF.net email is sponsored by DB2 Express
Download DB2 Express C - the FREE version of DB2 express and take
control of your XML. No limits. Just data. Click to get it now.
http://sourceforge.net/powerbar/db2/
_______________________________________________
DSpace-tech mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/dspace-tech
