Rick,
Thank you for the bug report. Unfortunately this patch did not make it
into the 1.5 release, which occurred earlier (before the original
email). The announcement of the release will be going out tomorrow
morning. The release has been picked up and is in the Central Maven
Repository and they have a strict policy about not allowing any
release to be revoked. The central repository plays a critical role in
maven build process, when you build DSpace with maven it will check
the repository for a specific version of a software package. Thus,
when someone downloads the DSpace release distribution (i.e. not the
source code) and runs 'mvn package' they will receive the version we
have already published into the central repository. This release of
DSpace is huge, by far the biggest release dspace has ever seen. Bugs
similar to this will likely be found in other "less commonly tested"
areas of DSpace in the coming few weeks. I expect there will be a
1.5.1 bug fix release soon.
This highlights how important it is for as many people as possible to
participate in the test-a-thon process, especially for those
administrators who use uncommon services such as Oracle, LDAP,
Shibboleth, or SRB. It is impossible for a developer to test these
services without access to: an Oracle database, an LDAP server, a
Shibboleth federation, or an SRB storage account. These options will
only receive as much testing as is provided by the community which
supports these options.
Until 1.5.1 is released, the work arounds available are an SVN
checkout or applying a patch. You can use the source version available
from SVN (dspace-1_5_x r2870) or apply the patch following this email.
As for a technical comment on the actual bug, there are other oracle
compatibility errors in that SQL statement beyond the ILIKE operator
in the statements that follow. The next sql statement issued after
this uses LIMIT and OFFSET, which are not supported by Oracle either.
Graham has posted a potential fix for this issue into the 1.5 branch,
however it has not been tested and may introduce pagination errors
into the UI.
Scott--
On Mar 25, 2008, at 2:09 PM, Rick Runyan wrote:
Hi –
I’m trying to install DSpace 1.5 using Oracle. It wasn’t until
yesterday that I saw this in the KNOWN_BUGS file:
“Oracle compatibility: this release does not support Oracle. Check
for patches or updates to this release to restore Oracle
compatibility.”
But nevertheless, I thought I had read that people had done
successful installs using Oracle, and I wound up also getting it
installed OK, but now I’m running into a problem in the eperson code
when I click the “People” link under “Access Control.”
Specifically, I get this exception:
java.sql.SQLException: ORA-00920: invalid relational operator
and looking through the stack trace, it appears to be coming from
org.dspace.eperson.EPerson.searchResultCount(EPerson.java:326)
where it says
TableRow row = DatabaseManager.querySingle(context,
"SELECT count(*) as count FROM eperson WHERE
eperson_id = ? OR " +
"firstname ILIKE ? OR lastname ILIKE ? OR
email ILIKE ?",
new Object[]
{int_param,dbquery,dbquery,dbquery});
I’m pretty sure “ILIKE” doesn’t work in Oracle, but I see this in
the 1.4.2 code and I’m pretty sure I was able t create some users in
that. Does anybody have any idea about what’s wrong here?
Thanks,
Rick Runyan
-------------------------------------------------------------------------
This SF.net email is sponsored by: Microsoft
Defy all challenges. Microsoft(R) Visual Studio 2008.
http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/_______________________________________________
DSpace-tech mailing list
DSpace-tech@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/dspace-tech
Index: dspace-api/src/main/java/org/dspace/browse/BrowseDAOOracle.java
===================================================================
--- dspace-api/src/main/java/org/dspace/browse/BrowseDAOOracle.java
(revision 2869)
+++ dspace-api/src/main/java/org/dspace/browse/BrowseDAOOracle.java
(revision 2870)
@@ -887,7 +887,7 @@
params.add(new Integer(limit));
}
- if (offset > -1)
+ if (offset > 0)
{
queryBuf.insert(0, "SELECT * FROM (");
queryBuf.append(") WHERE rnum>?");
Index: dspace-api/src/main/java/org/dspace/eperson/EPerson.java
===================================================================
--- dspace-api/src/main/java/org/dspace/eperson/EPerson.java (revision
2869)
+++ dspace-api/src/main/java/org/dspace/eperson/EPerson.java (revision
2870)
@@ -253,14 +253,48 @@
throws SQLException
{
String params = "%"+query.toLowerCase()+"%";
- String dbquery = "SELECT * FROM eperson WHERE eperson_id = ? OR
" +
- "firstname ILIKE ? OR lastname ILIKE ? OR email ILIKE ? ORDER BY
lastname, firstname ASC ";
-
- if (offset >= 0 && limit >0) {
- dbquery += "LIMIT " + limit + " OFFSET " + offset;
- }
-
- // When checking against the eperson-id, make sure the query can be
made into a number
+ StringBuffer queryBuf = new StringBuffer();
+ queryBuf.append("SELECT * FROM eperson WHERE eperson_id = ?
OR ");
+ queryBuf.append("LOWER(firstname) LIKE LOWER(?) OR
LOWER(lastname) LIKE LOWER(?) OR LOWER(email) LIKE LOWER(?) ORDER BY
lastname, firstname ASC ");
+
+ // Add offset and limit restrictions - Oracle requires
special code
+ if
("oracle".equals(ConfigurationManager.getProperty("db.name")))
+ {
+ // First prepare the query to generate row numbers
+ if (limit > 0 || offset > 0)
+ {
+ queryBuf.insert(0, "SELECT /*+ FIRST_ROWS(n) */
rec.*, ROWNUM rnum FROM (");
+ queryBuf.append(") ");
+ }
+
+ // Restrict the number of rows returned based on the limit
+ if (limit > 0)
+ {
+ queryBuf.append("rec WHERE rownum<=? ");
+ // If we also have an offset, then convert the limit
into the maximum row number
+ if (offset > 0)
+ limit += offset;
+ }
+
+ // Return only the records after the specified offset
(row number)
+ if (offset > 0)
+ {
+ queryBuf.insert(0, "SELECT * FROM (");
+ queryBuf.append(") WHERE rnum>?");
+ }
+ }
+ else
+ {
+ if (limit > 0)
+ queryBuf.append(" LIMIT ? ");
+
+ if (offset > 0)
+ queryBuf.append(" OFFSET ? ");
+ }
+
+ String dbquery = queryBuf.toString();
+
+ // When checking against the eperson-id, make sure the query
can be made into a number
Integer int_param;
try {
int_param = Integer.valueOf(query);
@@ -268,10 +302,19 @@
catch (NumberFormatException e) {
int_param = new Integer(-1);
}
+
+ // Create the parameter array, including limit and offset if
part of the query
+ Object[] paramArr = new Object[]
{int_param,params,params,params};
+ if (limit > 0 && offset > 0)
+ paramArr = new Object[]
{int_param,params,params,params,limit,offset};
+ else if (limit > 0)
+ paramArr = new Object[]
{int_param,params,params,params,limit};
+ else if (offset > 0)
+ paramArr = new Object[]
{int_param,params,params,params,offset};
+
+ // Get all the epeople that match the query
+ TableRowIterator rows = DatabaseManager.query(context, dbquery,
paramArr);
- // Get all the epeople that match the query
- TableRowIterator rows = DatabaseManager.query(context, dbquery, new
Object[] {int_param,params,params,params});
-
List epeopleRows = rows.toList();
EPerson[] epeople = new EPerson[epeopleRows.size()];
@@ -324,18 +367,18 @@
// Get all the epeople that match the query
TableRow row = DatabaseManager.querySingle(context,
- "SELECT count(*) as count FROM eperson WHERE eperson_id = ?
OR " +
- "firstname ILIKE ? OR lastname ILIKE ? OR email ILIKE
?",
+ "SELECT count(*) as epcount FROM eperson WHERE eperson_id
= ? OR " +
+ "LOWER(firstname) LIKE LOWER(?) OR LOWER(lastname) LIKE
LOWER(?) OR LOWER(email) LIKE LOWER(?)",
new Object[] {int_param,dbquery,dbquery,dbquery});
// use getIntColumn for Oracle count data
if
("oracle".equals(ConfigurationManager.getProperty("db.name")))
{
- count = new Long(row.getIntColumn("count"));
+ count = new Long(row.getIntColumn("epcount"));
}
else //getLongColumn works for postgres
{
- count = new Long(row.getLongColumn("count"));
+ count = new Long(row.getLongColumn("epcount"));
}
return count.intValue();
Index: dspace-api/src/main/java/org/dspace/eperson/Group.java
===================================================================
--- dspace-api/src/main/java/org/dspace/eperson/Group.java (revision
2869)
+++ dspace-api/src/main/java/org/dspace/eperson/Group.java (revision
2870)
@@ -794,13 +794,47 @@
throws SQLException
{
String params = "%"+query.toLowerCase()+"%";
- String dbquery = "SELECT * FROM epersongroup WHERE name ILIKE ? OR
eperson_group_id = ? ORDER BY name ASC ";
+ StringBuffer queryBuf = new StringBuffer();
+ queryBuf.append("SELECT * FROM epersongroup WHERE LOWER(name) LIKE
LOWER(?) OR eperson_group_id = ? ORDER BY name ASC ");
- if (offset >= 0 && limit > 0) {
- dbquery += "LIMIT " + limit + " OFFSET " + offset;
- }
-
- // When checking against the eperson-id, make sure the query can be
made into a number
+ // Add offset and limit restrictions - Oracle requires
special code
+ if
("oracle".equals(ConfigurationManager.getProperty("db.name")))
+ {
+ // First prepare the query to generate row numbers
+ if (limit > 0 || offset > 0)
+ {
+ queryBuf.insert(0, "SELECT /*+ FIRST_ROWS(n) */
rec.*, ROWNUM rnum FROM (");
+ queryBuf.append(") ");
+ }
+
+ // Restrict the number of rows returned based on the limit
+ if (limit > 0)
+ {
+ queryBuf.append("rec WHERE rownum<=? ");
+ // If we also have an offset, then convert the limit
into the maximum row number
+ if (offset > 0)
+ limit += offset;
+ }
+
+ // Return only the records after the specified offset
(row number)
+ if (offset > 0)
+ {
+ queryBuf.insert(0, "SELECT * FROM (");
+ queryBuf.append(") WHERE rnum>?");
+ }
+ }
+ else
+ {
+ if (limit > 0)
+ queryBuf.append(" LIMIT ? ");
+
+ if (offset > 0)
+ queryBuf.append(" OFFSET ? ");
+ }
+
+ String dbquery = queryBuf.toString();
+
+ // When checking against the eperson-id, make sure the query
can be made into a number
Integer int_param;
try {
int_param = Integer.valueOf(query);
@@ -808,10 +842,19 @@
catch (NumberFormatException e) {
int_param = new Integer(-1);
}
+
+ // Create the parameter array, including limit and offset if
part of the query
+ Object[] paramArr = new Object[]{params, int_param};
+ if (limit > 0 && offset > 0)
+ paramArr = new Object[] {params, int_param,limit,offset};
+ else if (limit > 0)
+ paramArr = new Object[] {params, int_param,limit};
+ else if (offset > 0)
+ paramArr = new Object[] {params, int_param,offset};
+
+ TableRowIterator rows =
+ DatabaseManager.query(context, dbquery, paramArr);
- TableRowIterator rows =
- DatabaseManager.query(context, dbquery, new Object[]{params,
int_param});
-
List groupRows = rows.toList();
Group[] groups = new Group[groupRows.size()];
@@ -850,7 +893,7 @@
throws SQLException
{
String params = "%"+query.toLowerCase()+"%";
- String dbquery = "SELECT count(*) as count FROM epersongroup WHERE
name ILIKE ? OR eperson_group_id = ? ";
+ String dbquery = "SELECT count(*) as gcount FROM epersongroup WHERE
LOWER(name) LIKE LOWER(?) OR eperson_group_id = ? ";
// When checking against the eperson-id, make sure the query can
be made into a number
Integer int_param;
@@ -868,11 +911,11 @@
Long count;
if
("oracle".equals(ConfigurationManager.getProperty("db.name")))
{
- count = new Long(row.getIntColumn("count"));
+ count = new Long(row.getIntColumn("gcount"));
}
else //getLongColumn works for postgres
{
- count = new Long(row.getLongColumn("count"));
+ count = new Long(row.getLongColumn("gcount"));
}
return count.intValue();
-------------------------------------------------------------------------
Check out the new SourceForge.net Marketplace.
It's the best place to buy or sell services for
just about anything Open Source.
http://ad.doubleclick.net/clk;164216239;13503038;w?http://sf.net/marketplace
_______________________________________________
DSpace-tech mailing list
DSpace-tech@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/dspace-tech
