Bug 21105 - oai.pl returns invalid earliestDatestamp
Summary: oai.pl returns invalid earliestDatestamp
Status: Needs Signoff
Alias: None
Product: Koha
Classification: Unclassified
Component: Web services (show other bugs)
Version: master
Hardware: All All
: P5 - low normal with 1 vote (vote)
Assignee: Rudolf Byker
QA Contact: Testopia
URL:
Keywords:
Depends on:
Blocks: 21102
  Show dependency treegraph
 
Reported: 2018-07-23 19:42 UTC by Pablo AB
Modified: 2021-06-06 22:39 UTC (History)
3 users (show)

See Also:
Change sponsored?: Sponsored
Patch complexity: Small patch
Text to go in the release notes:
Version(s) released in:


Attachments
Proposed patch (1.62 KB, patch)
2021-04-15 07:57 UTC, Rudolf Byker
Details | Diff | Splinter Review
New patch. Fixed quote problem and tested. (1.62 KB, patch)
2021-04-16 06:22 UTC, Rudolf Byker
Details | Diff | Splinter Review

Note You need to log in before you can comment on or make changes to this bug.
Description Pablo AB 2018-07-23 19:42:32 UTC
From: https://www.openarchives.org/OAI/openarchivesprotocol.html#DatestampsResponses
Dates should be YYYY-MM-DDThh:mm:ssZ
curl -s "baseURL?verb=Identify" | xmllint --format - | grep Date
responseDate -> 2018-07-23T18:34:15Z : OK
earliestDatestamp -> 2010-09-30 16:04:37 : FAIL

Originally deteced with official validator:
https://www.openarchives.org/Register/ValidateSite

Related: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=17785#c13
Comment 1 Rudolf Byker 2018-10-08 15:22:05 UTC
This makes a HUGE difference for anyone not in a UTC timezone. I think this should be higher priority...
Comment 2 Rudolf Byker 2021-04-14 21:51:16 UTC
Currently, the code to get the earliestDatestamp looks like this:

earliestDatestamp   => _get_earliest_datestamp() || '0001-01-01T00:00:00Z',

and

sub _get_earliest_datestamp {
    my $dbh = C4::Context->dbh;
    my ( $earliest ) = $dbh->selectrow_array("SELECT MIN(timestamp) AS earliest FROM biblio" );
    return $earliest
}

There are two problems here:

1. From the MySQL docs: "MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval." Similarly for MariaDB: "If a column uses the TIMESTAMP data type, then any inserted values are converted from the session's time zone to Coordinated Universal Time (UTC) when stored, and converted back to the session's time zone when retrieved." But we MUST get this as a UTC value according to the OAI-PMH spec (see http://www.openarchives.org/OAI/openarchivesprotocol.html#Identify and http://www.openarchives.org/OAI/openarchivesprotocol.html#Dates )

2. It's formatted in the default MySQL way, which is YYYY-MM-DD hh:mm:ss (local time) rather than YYYY-MM-DDThh:mm:ssZ (UTC).

I don't really know Perl, but I can suggest a SQL query that should fix the problem:

SELECT DATE_FORMAT(CONVERT_TZ(MIN(timestamp), 'SYSTEM', '+00:00'), '%Y-%m-%dT%H:%i:%SZ') AS earliest FROM biblio;

Example results:

Before:

+---------------------+
| earliest            |
+---------------------+
| 2012-11-03 13:41:32 |
+---------------------+

After:

+----------------------+
| earliest             |
+----------------------+
| 2012-11-03T11:41:32Z |
+----------------------+
Comment 3 David Cook 2021-04-14 23:37:49 UTC
Rudolf: It's just a formatting issue. If you examine your data, you'll see that the timestamp reported by oai.pl is in UTC.

This is thanks to some cleverness in "Koha/OAI/Server/Repository.pm" that sets the the time zone to UTC in the MySQL client session. (Thanks Ere :D)

So the following should be sufficient:

SELECT DATE_FORMAT(MIN(timestamp),'%Y-%m-%dT%H:%i:%SZ')  AS earliest FROM biblio;
Comment 4 Rudolf Byker 2021-04-15 07:57:43 UTC
Created attachment 119604 [details] [review]
Proposed patch
Comment 5 David Cook 2021-04-16 00:42:01 UTC
Thanks for writing the patch, Rudolf. 

Have you tested it out? I haven't tried it myself yet, but at a glance it contains a syntax error. You've used single quotes around the SQL, but the SQL contains single quotes, so it will bust.

Take a look at https://perldoc.perl.org/perlop#Quote-and-Quote-like-Operators. You could replace the outer single quotes with something like q{}.
Comment 6 Rudolf Byker 2021-04-16 06:22:57 UTC
Created attachment 119692 [details] [review]
New patch. Fixed quote problem and tested.

Sorry, I did not test the previous patch properly. Here is a new one. I tested it this time. It fixes the problem in the XML response, but it stills seems to be formatted as YYYY-MM-DD hh:mm:ss instead of YYYY-MM-DDThh:mm:ssZ in the browser. Not sure if that's a (separate) bug or a feature.
Comment 7 Katrin Fischer 2021-04-17 13:16:07 UTC
Setting back to Needs Sign-off as the comment from David has been taken care of in the new patch.
Comment 8 David Cook 2021-04-19 23:38:13 UTC
(In reply to Rudolf Byker from comment #6)
> Created attachment 119692 [details] [review] [review]
> New patch. Fixed quote problem and tested.
> 
> Sorry, I did not test the previous patch properly. Here is a new one. I
> tested it this time. It fixes the problem in the XML response, but it stills
> seems to be formatted as YYYY-MM-DD hh:mm:ss instead of YYYY-MM-DDThh:mm:ssZ
> in the browser. Not sure if that's a (separate) bug or a feature.

I think that it would be reasonable to handle it here.

Take a look at ./koha-tmpl/opac-tmpl/xslt/OAI.xslt. You'll see that the characters T and Z are being stripped out of the datestamps.
Comment 9 Rudolf Byker 2021-04-20 10:08:04 UTC
Do you mean that they are only stripped out when displayed in the browser? In that case, all is well.
Comment 10 David Cook 2021-04-20 23:38:44 UTC
(In reply to Rudolf Byker from comment #9)
> Do you mean that they are only stripped out when displayed in the browser?
> In that case, all is well.

Yes, the XSLT is sent in the browser response, and the browser applies the XSLT to the XML to generate a HTML document. That XSLT strips out the 'T' and the 'Z'.
Comment 11 Rudolf Byker 2021-06-01 19:45:15 UTC
Hi there. Who needs to sign off on this issue? Is there anything else I can do to help speed up the process?
Comment 12 Katrin Fischer 2021-06-01 21:16:37 UTC
(In reply to Rudolf Byker from comment #11)
> Hi there. Who needs to sign off on this issue? Is there anything else I can
> do to help speed up the process?

Hi Rudolf, at this stage, anyone can sign off - there is not much you can do, but maybe talk to people who might be interested in this patch got gain a sign off. Sometimes an email to the mailing list might help.
Comment 13 David Nind 2021-06-06 22:39:23 UTC
Hi Rudolf.

Could you write up a test plan so that I could test? This should be a step by step list of things to do, for example:

Test plan:
1. [Steps to replicate the issue]
2. Apply the patch
3. [Steps that indicate the issue is fixed, such as repeating steps in 1 and the result expected]

Here is a good example:
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=28408#c3

And also: https://wiki.koha-community.org/wiki/Commit_messages#Test_plan

Bearing in mind that those testing (like me) may not be developers or familiar with this area of Koha.

David Nind