From 2e23236c9f29cd59d9dc9d9df7ab6da49f256699 Mon Sep 17 00:00:00 2001 From: David Cook Date: Thu, 5 Sep 2013 15:21:51 +1000 Subject: [PATCH] Bug 10824: OAI-PMH repository/server not handling time in 'until' and 'from' This patch removes the DATE() function from a query on timestamp, and adds a sub that strips the UTC designators "T" and "Z" from incoming "from" and "until" arguments in OAI-PMH requests so that they're more compliant with MySQL (and probably other databases as well). This means that the date and time for the 'from' and 'until' arguments will be matched correctly in the database. This patch also adds 'T00:00:00Z' to 'from' arguments and 'T23:59:59Z' to until arguments, when only dates are provided via the OAI parameters. The zero time isn't necessary, since MySQL treats '2013-09-30' as '2013-09-30 00:00:00' by default. However, the near midnight time is needed for 'until'. Otherwise, you'll never be able to retrieve a record with a date/time matching the 'until' argument. In summary, this patch adds handling for times as well as dates, which is necessary so that Koha is closer to meeting the actual OAI-PMH spec. TEST PLAN: 0) Note down a selection of timestamps from your biblio table 1) Enable your OAI-PMH server through the global system preferences Web services tab. 2) Craft and submit a similar request to the following in your browser: KOHAINSTANCE/cgi-bin/koha/oai.pl?verb=ListRecords&metadataPrefix=oai_dc& from=2013-09-02T13:44:33Z&until=2013-09-05T13:44:33Z Change the exact dates to accord with your timestamps, but keep the YYYY-MM-DDTHH:MM:SSZ format. 3) Note the unexpected behaviour. A "from" argument with the timestamp 2013-09-02T13:44:33Z will show records from 2013-09-03 but not records from 2013-09-02 even though the timestamp in the database will say "2013-09-02 13:44:33". Also note that records with a timestamp later than 13:44:33 will show up for the day 2013-09-05, even though they shouldn't. 4) APPLY THE PATCH 5) Resubmit the links you tried above 6) Note that the applicable records now appear (or do not appear) in accordance with the precise date/time ranges! -- Developer Note: We could've not stripped the UTC designators and used DATE() around the parameters in the SQL queries, but that would have lost the whole purpose of using times in the "from" arguments, since they would've been generalized to just the dates. I think this is probably the best solution. Admittedly, creating "form_arg" and "until_arg" hashrefs in the ResumptionToken object might not be ideal, but I preferred that to copying the _strip_UTC_designator subroutine into two other objects. Perhaps this sub could go somewhere else and be imported into those other two objects but this seemed to be the most sensible decision. I'm open to other opinions though. Signed-off-by: Bernardo Gonzalez Kriegel Works, find results with correct timestamp No koha-qa errors Signed-off-by: Kyle M Hall Signed-off-by: Tomas Cohen Arazi --- opac/oai.pl | 20 ++++++++++++++++---- 1 file changed, 16 insertions(+), 4 deletions(-) diff --git a/opac/oai.pl b/opac/oai.pl index c4f4e8bff0..c80ebfa99c 100755 --- a/opac/oai.pl +++ b/opac/oai.pl @@ -99,6 +99,9 @@ sub new { my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday) = gmtime( time ); $until = sprintf( "%.4d-%.2d-%.2d", $year+1900, $mon+1,$mday ); } + #Add times to the arguments, when necessary, so they correctly match against the DB timestamps + $from .= 'T00:00:00Z' if length($from) == 10; + $until .= 'T23:59:59Z' if length($until) == 10; $offset = $args{ offset } || 0; $set = $args{set}; } @@ -108,6 +111,8 @@ sub new { $self->{ from } = $from; $self->{ until } = $until; $self->{ set } = $set; + $self->{ from_arg } = _strip_UTC_designators($from); + $self->{ until_arg } = _strip_UTC_designators($until); $self->resumptionToken( join( '/', $metadata_prefix, $offset, $from, $until, $set ) ); @@ -116,6 +121,13 @@ sub new { return $self; } +sub _strip_UTC_designators { + my ( $timestamp ) = @_; + $timestamp =~ s/T/ /g; + $timestamp =~ s/Z//g; + return $timestamp; +} + # __END__ C4::OAI::ResumptionToken @@ -320,14 +332,14 @@ sub new { FROM biblioitems "; $sql .= " JOIN oai_sets_biblios ON biblioitems.biblionumber = oai_sets_biblios.biblionumber " if defined $set; - $sql .= " WHERE DATE(timestamp) >= ? AND DATE(timestamp) <= ? "; + $sql .= " WHERE timestamp >= ? AND timestamp <= ? "; $sql .= " AND oai_sets_biblios.set_id = ? " if defined $set; $sql .= " LIMIT " . ($max+1) . " OFFSET $token->{offset} "; my $sth = $dbh->prepare( $sql ); - my @bind_params = ($token->{'from'}, $token->{'until'}); + my @bind_params = ($token->{'from_arg'}, $token->{'until_arg'}); push @bind_params, $set->{'id'} if defined $set; $sth->execute( @bind_params ); @@ -477,7 +489,7 @@ sub new { FROM biblioitems "; $sql .= " JOIN oai_sets_biblios ON biblioitems.biblionumber = oai_sets_biblios.biblionumber " if defined $set; - $sql .= " WHERE DATE(timestamp) >= ? AND DATE(timestamp) <= ? "; + $sql .= " WHERE timestamp >= ? AND timestamp <= ? "; $sql .= " AND oai_sets_biblios.set_id = ? " if defined $set; $sql .= " LIMIT " . ($max + 1) . " @@ -485,7 +497,7 @@ sub new { "; my $sth = $dbh->prepare( $sql ); - my @bind_params = ($token->{'from'}, $token->{'until'}); + my @bind_params = ($token->{'from_arg'}, $token->{'until_arg'}); push @bind_params, $set->{'id'} if defined $set; $sth->execute( @bind_params ); -- 2.39.5