[Dspace-tech] Problem with statistics

[Andrea Garrido Fernández]

Hi:
I have a problem when I try to execute the perl scripts for generating
statistics.
When I try to run the LogAnalyser class with a start or end date, I have an
error like this:

java.sql.SQLException: ORA-01841: el valor (completo) del año debe estar
entre -4713 y +9999, y no debe ser igual a 0

at
oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:745)
at
oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:216)
at
oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:966)
at
oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1062)
at
oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:850)
at
oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1134)
at
oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3339)
at
oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3384)
at
org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:92)
at
org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:92)
at org.dspace.storage.rdbms.DatabaseManager.query(DatabaseManager.java:259)
at
org.dspace.storage.rdbms.DatabaseManager.querySingle(DatabaseManager.java:384)
at
org.dspace.storage.rdbms.DatabaseManager.querySingle(DatabaseManager.java:395)
at org.dspace.app.statistics.LogAnalyser.getNumItems(LogAnalyser.java:1264)
at org.dspace.app.statistics.LogAnalyser.processLogs(LogAnalyser.java:535)
at org.dspace.app.statistics.LogAnalyser.main(LogAnalyser.java:327)
------------------------------------------------------------------------------------------

I have tried many things, incluiding the solution given in this forum
before, using the REGEXP_REPLACE , but I find it impossible because oracle
don´t recognize this expression.
Colud yo help me, please? I am a bit lost.
Thanks,
Andrea

_________________________________________________________________
Dale rienda suelta a tu tiempo libre. Mil ideas para exprimir tu ocio con
MSN Entretenimiento. http://entretenimiento.msn.es/

[Graham Triggs]

Hi,

Well, the relevant part of this is the only thing that's not in
English!! But the context is clear enough - it's having trouble parsing
a valid date.

Can you confirm that the start / end dates that you are passing to the
script are of the form 'yyyy-mm-dd'.

If your input is valid, then you've got a problem with one of the
metadata value fields. You'll need to check the text_value of all the
dc.date.accessioned entries (probably metadata_field_id=11). It looks
like the offending item is of the correct format (yyyy-mm-ddThh:mi:ssZ),
but contains invalid data.

G

> -------------------------------------------------------------------------
> This SF.net email is sponsored by: Microsoft
> Defy all challenges. Microsoft(R) Visual Studio 2005.
> http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/
> _______________________________________________
> DSpace-tech mailing list
> DSpac...@lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/dspace-tech
This email has been scanned by Postini.
For more information please visit http://www.postini.com

[Andrea Garrido Fernández]

Thanks for your information.
The dates I am passing to the scripts are in the correct format,and the
dates which are kept in my metadatavalue table whith the
metadata_field_id=11 are :

ETADATA_VALUE_ID ITEM_ID METADATA_FIELD_ID TEXT_VALUE
TEXT_LANG
PLACE
----------------- ---------- -----------------
--------------------------------------------------------------------------------
---------------------------------------------------------------- ----------
46 21 11 2007-09-06T10:22:34Z

1
420 42 11 2007-09-10T12:05:46Z

1
158 25 11 2007-09-06T14:58:42Z

1
292 23 11 2007-09-10T11:08:29Z

1
311 41 11 2007-09-10T11:08:49Z

1
529 43 11 2007-09-10T12:26:59Z

1
604 44 11 2007-09-10T12:41:50Z

1

------------------------------------------------------------------------------------

I can´t find what is wrong with this dates, I think they have the rigth
format.
When I copy the query that is being executed when I run the statistics in
the dspace in the sql+ worksheet, it runs correctly and gives me the right
result. This is the query:

SELECT COUNT(*) AS num FROM item WHERE in_archive = 1 AND withdrawn = 0 AND
item_id IN ( SELECT item_id FROM metadatavalue WHERE metadata_field_id = (
SELECT metadata_field_id FROM metadatafieldregistry WHERE element = 'date'
AND qualifier = 'accessioned') AND TO_TIMESTAMP( TO_CHAR(text_value),
'yyyy-mm-dd"T"hh24:mi:ss"Z"' ) > TO_DATE('2007-09-01', 'yyyy-MM-dd') AND
TO_TIMESTAMP( TO_CHAR(text_value), 'yyyy-mm-dd"T"hh24:mi:ss"Z"' ) <
TO_DATE('2007-10-21', 'yyyy-MM-dd') )

I am completely lost.

Thank you very much for your help.
Andrea Garrido

>From: Graham Triggs <gra...@biomedcentral.com>
>To: Andrea Garrido Fernández <dreac...@hotmail.com>
>CC: dspac...@lists.sourceforge.net
>Subject: Re: [Dspace-tech] Problem with statistics
>Date: Tue, 11 Sep 2007 09:42:50 +0100
>
>Hi,
>
>Well, the relevant part of this is the only thing that's not in
>English!! But the context is clear enough - it's having trouble parsing
>a valid date.
>
>Can you confirm that the start / end dates that you are passing to the
>script are of the form 'yyyy-mm-dd'.
>
>If your input is valid, then you've got a problem with one of the
>metadata value fields. You'll need to check the text_value of all the
>dc.date.accessioned entries (probably metadata_field_id=11). It looks
>like the offending item is of the correct format (yyyy-mm-ddThh:mi:ssZ),
>but contains invalid data.
>
>G
>

>On Mon, 2007-09-10 at 08:49 +0000, Andrea Garrido Fernández wrote:
> > Hi:
> > I have a problem when I try to execute the perl scripts for generating
> > statistics.
> > When I try to run the LogAnalyser class with a start or end date, I have
>an
> > error like this:
> >

> > java.sql.SQLException: ORA-01841: el valor (completo) del año debe

> > don´t recognize this expression.

> > Colud yo help me, please? I am a bit lost.
> > Thanks,
> > Andrea
> >
> > _________________________________________________________________
> > Dale rienda suelta a tu tiempo libre. Mil ideas para exprimir tu ocio
>con
> > MSN Entretenimiento. http://entretenimiento.msn.es/
> >
> >
> >
>-------------------------------------------------------------------------
> > This SF.net email is sponsored by: Microsoft
> > Defy all challenges. Microsoft(R) Visual Studio 2005.
> > http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/
> > _______________________________________________
> > DSpace-tech mailing list
> > DSpac...@lists.sourceforge.net
> > https://lists.sourceforge.net/lists/listinfo/dspace-tech
>This email has been scanned by Postini.
>For more information please visit http://www.postini.com
>

_________________________________________________________________
Acepta el reto MSN Premium: Correos más divertidos con fotos y textos
increíbles en MSN Premium. Descárgalo y pruébalo 2 meses gratis.
http://join.msn.com?XAPID=1697&DI=1055&HL=Footer_mailsenviados_correosmasdivertidos

[Andrea Garrido Fernández]

Sorry, I have realized that the query that doesn't work is :

SELECT COUNT(*) AS num FROM item WHERE in_archive = 1 AND withdrawn = 0 AND
item_id IN ( SELECT item_id FROM metadatavalue WHERE metadata_field_id = (
SELECT metadata_field_id FROM metadatafieldregistry WHERE element = 'date'
AND qualifier = 'accessioned') AND TO_TIMESTAMP( TO_CHAR(text_value),
'yyyy-mm-dd"T"hh24:mi:ss"Z"' ) > TO_DATE('2007-09-01', 'yyyy-MM-dd') AND
TO_TIMESTAMP( TO_CHAR(text_value), 'yyyy-mm-dd"T"hh24:mi:ss"Z"' ) <

TO_DATE('2007-10-21', 'yyyy-MM-dd') ) AND item_id IN ( SELECT item_id FROM
metadatavalue WHERE text_value LIKE '%Thesis or Dissertation%' AND

metadata_field_id = ( SELECT metadata_field_id FROM metadatafieldregistry

WHERE element = 'type' AND qualifier IS NULL) )

This query gives me error in dspace and in my sql+ worksheet.

Sorry and thank yoy very much,

Andrea Garrido





>From: Graham Triggs <gra...@biomedcentral.com>
>To: Andrea Garrido Fernández <dreac...@hotmail.com>
>CC: dspac...@lists.sourceforge.net
>Subject: Re: [Dspace-tech] Problem with statistics
>Date: Tue, 11 Sep 2007 09:42:50 +0100
>

>Hi,
>
>Well, the relevant part of this is the only thing that's not in
>English!! But the context is clear enough - it's having trouble parsing
>a valid date.
>
>Can you confirm that the start / end dates that you are passing to the
>script are of the form 'yyyy-mm-dd'.
>
>If your input is valid, then you've got a problem with one of the
>metadata value fields. You'll need to check the text_value of all the
>dc.date.accessioned entries (probably metadata_field_id=11). It looks
>like the offending item is of the correct format (yyyy-mm-ddThh:mi:ssZ),
>but contains invalid data.
>
>G
>

>On Mon, 2007-09-10 at 08:49 +0000, Andrea Garrido Fernández wrote:
> > Hi:
> > I have a problem when I try to execute the perl scripts for generating
> > statistics.
> > When I try to run the LogAnalyser class with a start or end date, I have
>an
> > error like this:
> >

> > java.sql.SQLException: ORA-01841: el valor (completo) del año debe

> > don´t recognize this expression.

> > Colud yo help me, please? I am a bit lost.
> > Thanks,
> > Andrea
> >
> > _________________________________________________________________
> > Dale rienda suelta a tu tiempo libre. Mil ideas para exprimir tu ocio
>con
> > MSN Entretenimiento. http://entretenimiento.msn.es/
> >
> >
> >
>-------------------------------------------------------------------------
> > This SF.net email is sponsored by: Microsoft
> > Defy all challenges. Microsoft(R) Visual Studio 2005.
> > http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/
> > _______________________________________________
> > DSpace-tech mailing list
> > DSpac...@lists.sourceforge.net
> > https://lists.sourceforge.net/lists/listinfo/dspace-tech
>This email has been scanned by Postini.
>For more information please visit http://www.postini.com
>

[Graham Triggs]

Andrea,

It looks like this is an optimizer issue in Oracle - ie. that it is
applying the TO_TIMESTAMP to all the records first, rather than just
those that match the metadata_field_id.

I've tried rewriting the query so that it uses an inline view to obtain
all the metadata records for the date accessioned field, and then apply
the TO_TIMESTAMP to that... but bizarrely that doesn't seem to be
working either.

For now, I suggest that you separate out:

SELECT metadata_field_id FROM metadatafieldregistry WHERE element =
'date' AND qualifier = 'accessioned'

As a distinct query, store the result in a variable, and then replace
the:

metadata_field_id = (
SELECT metadata_field_id FROM metadatafieldregistry WHERE element =
'date'
AND qualifier = 'accessioned')

with a "...metadata_field_id = " + variable + "..."

That should at least make it work. File a bug report, and add list a
reference to it under Bug Fixes / To Do here:

http://wiki.dspace.org/index.php/Next_Release_Status

and I'll address it properly for the 1.5 release when I check / add-in
the Oracle support.

G

[Andrea Garrido Fernández]

Thank you, I´ve solved it!
The problem was that in LogAnalyser.java there were two if clauses in a
wrong order, i.e, I changed
in the method "public static Integer getNumItems(Context context, String
type)" these if clauses:

if (type != null)
{
query.append(" AND item_id IN ( " +
typeQuery + ") ");

}


if (startDate != null || endDate != null)
{
query.append(" AND item_id IN ( " +
dateQuery.toString() + ") ");

}

This is the right order, before "if (startDate != null || endDate != null)
{....." was first, so when you ran the query it gave the error because it
was looking for in an empty set (or something similar).

Thank yoy very much for your help, I will fix it as a bug.

Best regards,
Andrea

_________________________________________________________________
¿Estás pensando en cambiar de coche? Todas los modelos de serie y extras en
MSN Motor. http://motor.msn.es/researchcentre/

[revskill]

Hi all , i'm new with dspace platform. My University 's using dspace version 5.2, and i have some questions:

- The statistics features of this version of dspace is weak and it's hard for administrators to monitor some stats such as number of downloads of items, or top viewed items,...
- How to upgrade from 5.2 to 6.x version ( rebuild index, set up the SOLR ) with minimal efforts
- Does the SOLR features of 6.x version solves our problems in statistics as above ? I just don't know how much the SOLR can do for us.

Can you show me some real world dspace projects with those stats features ?

Really appreciate  your help.

Rev
HPU ( Haiphong Private University of Vietnam )

[Bram Luyten]

Hi Rev,

the best way to explore whether the standard DSpace stats address your problems, is to experiment with them on http://demo.dspace.org

Make sure you do your testing with the User interface you wish to use (subtle differences in the XML User interface and in the JSP user interface.

Also, some more background information on the SOLR stats is available in Ben Bosman's presentation slides from last week:
Slides http://bit.ly/bXCyFb and
Whitepaper http://bit.ly/9QCwCg

If you're still confronted with specific questions that aren't addressed by these materials or the DSpace documentation, please shoot.

best regards,

Bram Luyten

@mire - http://www.atmire.com

Technologielaan 9 - 3001 Heverlee - Belgium
533 2nd Street - Encinitas, CA 92024 - USA

http://www.togather.eu - Before getting together, get Tog@ther

------------------------------------------------------------------------------
Start uncovering the many advantages of virtual appliances
and start using them to simplify application deployment and
accelerate your shift to cloud computing.
http://p.sf.net/sfu/novell-sfdev2dev