Attachment #125688 for bug #29135

View | Details | Raw Unified | Return to bug 29135
Collapse All | Expand All




    my ($biblionumber) = $args{identifier} =~ /^$prefix(.*)/;
    my $items_included = $repository->items_included( $args{metadataPrefix} );
    my $dbh = C4::Context->dbh;
    my $sql;
        SELECT timestamp
        FROM   biblioitems
        WHERE  biblionumber=?
    ";
    my @bind_params = ($biblionumber);
    if ( $items_included ) {
        # Take latest timestamp of biblio and any items
        # Or timestamp of deleted items where bib not deleted
        $sql .= "
            SELECT timestamp
            FROM   biblio_metadata
            WHERE  biblionumber=?
              UNION
            SELECT deleteditems.timestamp FROM deleteditems JOIN biblio USING (biblionumber)
            WHERE  deleteditems.biblionumber=?
              UNION
            SELECT timestamp from items
            WHERE  biblionumber=?
        ";
        push @bind_params, $biblionumber;
        push @bind_params, $biblionumber;
        $sql = "
            SELECT max(timestamp) as timestamp
            FROM ($sql) bib
        ";
    } else {
        $sql = "
            SELECT max(timestamp) as timestamp
            FROM   biblio_metadata
            WHERE  biblionumber=?
        ";
    }

    my $sth = $dbh->prepare( $sql ) || die( 'Could not prepare statement: ' . $dbh->errstr );
    $sth->execute( @bind_params ) || die( 'Could not execute statement: ' . $sth->errstr );
    my ($timestamp, $deleted);
    # If there are no rows in biblio_metadata, try deletedbiblio_metadata
    unless ( ($timestamp = $sth->fetchrow) ) {
        $sql = "
            SELECT max(timestamp)
            FROM   deletedbiblio_metadata
            WHERE  biblionumber=?
        ";
        @bind_params = ($biblionumber);

        if ( $items_included ) {
            # Take latest timestamp among biblio and items
            $sql .= "
                UNION
                SELECT timestamp from deleteditems
                WHERE biblionumber=?
            ";
            push @bind_params, $biblionumber;
            $sql = "
                SELECT max(timestamp)
                FROM ($sql) bib
            ";
        }

        $sth = $dbh->prepare($sql) || die('Could not prepare statement: ' . $dbh->errstr);
        $sth->execute( @bind_params ) || die('Could not execute statement: ' . $sth->errstr);
        unless ( ($timestamp = $sth->fetchrow) )

    } else {
        # We fetch it using this method, rather than the database directly,
        # so it'll include the item data
        my $marcxml = $repository->get_biblio_marcxml($biblionumber, $args{metadataPrefix});
        $marcxml = $repository->get_biblio_marcxml($biblionumber, $args{metadataPrefix})
            unless $deleted;

        $self->record(
            Koha::OAI::Server::Record->new($repository, $marcxml, $timestamp, \@setSpecs, %args)
        );





        my @part_bind_params = ($next_id);

        # Note: "main" is alias of the main table in the SELECT statement to avoid ambiquity with joined tables
        my $where = "main.biblionumber >= ?";
        if ( $from ) {
            $where .= " AND main.timestamp >= ?";
            push @part_bind_params, $from;
        }
        if ( $until ) {
            $where .= " AND main.timestamp <= ?";
            push @part_bind_params, $until;
        }
        if ( defined $set ) {
            $where .= " AND main.biblionumber in (SELECT osb.biblionumber FROM oai_sets_biblios osb WHERE osb.set_id = ?)";
            push @part_bind_params, $set->{'id'};
        }

        my @bind_params = @part_bind_params;

        my $order_limit = 'ORDER BY main.biblionumber LIMIT ' . ($max + 1);

        my $sql;
        my $ts_sql;
            FROM $table
            $criteria
        ";

        # If items are included, fetch a set of potential biblionumbers from items tables as well.
        # Then merge them, sort them and take the required number of them from the resulting list.
        # This may seem counter-intuitive as in worst case we fetch 3 times the biblionumbers needed,
        # but avoiding joins or subqueries makes this so much faster that it does not matter.
        if ( $include_items && !$deleted )  {
            $sql = "
                (SELECT biblionumber
                FROM $table main
                WHERE $where $order_limit)
                  UNION
                (SELECT DISTINCT(biblionumber) FROM deleteditems main JOIN biblio USING (biblionumber) WHERE $where
                $order_limit)
                  UNION
                (SELECT DISTINCT(biblionumber) FROM items main WHERE $where $order_limit)";
            push @bind_params, @part_bind_params;
            push @bind_params, @part_bind_params;
            $sql = "SELECT biblionumber FROM ($sql) main $order_limit";

            $ts_sql = "
                SELECT MAX(timestamp)
                FROM (
                    SELECT timestamp FROM biblio_metadata WHERE biblionumber = ?
                    UNION
                    SELECT timestamp FROM deleteditems WHERE biblionumber = ?
                    UNION
                    SELECT timestamp FROM items WHERE biblionumber = ?
                ) bi
            ";
        } else {
            $sql = "
                SELECT biblionumber
                FROM $table main
                WHERE $where
            ";

            $ts_sql = "SELECT max(timestamp) FROM $table WHERE biblionumber = ?";
        }

        my $sth = $dbh->prepare( $sql ) || die( 'Could not prepare statement: ' . $dbh->errstr );
        $sth->execute( @bind_params ) || die( 'Could not execute statement: ' . $sth->errstr );

        my $ts_sql;
        if ( $include_items ) {
            if ( $deleted ) {
                $ts_sql = "
                    SELECT MAX(timestamp)
                    FROM (
                        SELECT timestamp FROM deletedbiblio_metadata WHERE biblionumber = ?
                        UNION
                        SELECT timestamp FROM deleteditems WHERE biblionumber = ?
                    ) bis
                ";
            } else {
                $ts_sql = "
                    SELECT MAX(timestamp)
                    FROM (
                        SELECT timestamp FROM biblio_metadata WHERE biblionumber = ?
                        UNION
                        SELECT timestamp FROM deleteditems WHERE biblionumber = ?
                        UNION
                        SELECT timestamp FROM items WHERE biblionumber = ?
                    ) bi
                ";
            }
        } else {
            $ts_sql = "SELECT timestamp FROM $table WHERE biblionumber = ?";
        }
        my $ts_sth = $dbh->prepare( $ts_sql ) || die( 'Could not prepare statement: ' . $dbh->errstr );

        $sth->execute( @bind_params ) || die( 'Could not execute statement: ' . $sth->errstr );
        foreach my $row (@{ $sth->fetchall_arrayref() }) {
            my $biblionumber = $row->[0];
            $count++;

                last STAGELOOP;
            }
            my @params = ($biblionumber);
            if ( $include_items && !$deleted ) {
                push @params, $deleted ? ( $biblionumber ) : ( $biblionumber, $biblionumber );
            }
            $ts_sth->execute( @params ) || die( 'Could not execute statement: ' . $ts_sth->errstr );





use Modern::Perl;
use Test::Deep qw( cmp_deeply re );
use Test::MockTime qw/set_fixed_time set_relative_time restore_time/;

use Test::More tests => 33;
use DateTime;
use File::Basename;
use File::Spec;

use YAML::XS;

use t::lib::Mocks;
use t::lib::TestBuilder;

use C4::Biblio qw( AddBiblio GetMarcBiblio ModBiblio DelBiblio );
use C4::Context;
use C4::OAI::Sets qw(AddOAISet);



    $schema->storage->txn_rollback;
};

subtest 'Tests for timestamp handling' => sub {

    plan tests => 27;

    t::lib::Mocks::mock_preference( 'OAI::PMH'         => 1 );
    t::lib::Mocks::mock_preference( 'OAI-PMH:MaxCount' => 3 );
    t::lib::Mocks::mock_preference( 'OAI-PMH:ConfFile' =>  File::Spec->rel2abs(dirname(__FILE__)) . '/oaiconf_items.yaml' );

    $schema->storage->txn_begin;

    my $sth_metadata = $dbh->prepare('UPDATE biblio_metadata SET timestamp=? WHERE biblionumber=?');
    my $sth_del_metadata = $dbh->prepare('UPDATE deletedbiblio_metadata SET timestamp=? WHERE biblionumber=?');
    my $sth_item = $dbh->prepare('UPDATE items SET timestamp=? WHERE itemnumber=?');
    my $sth_del_item = $dbh->prepare('UPDATE deleteditems SET timestamp=? WHERE itemnumber=?');

    my $builder = t::lib::TestBuilder->new;

    set_fixed_time(CORE::time());

    my $utc_datetime = dt_from_string(undef, undef, 'UTC');
    my $utc_timestamp = $utc_datetime->ymd . 'T' . $utc_datetime->hms . 'Z';
    my $timestamp = dt_from_string(undef, 'sql');

    # Test a bib with one item
    my $biblio1 = $builder->build_sample_biblio();
    $sth_metadata->execute($timestamp, $biblio1->biblionumber);
    my $item1 = $builder->build_sample_item(
        {
            biblionumber => $biblio1->biblionumber
        }
    );
    $sth_item->execute($timestamp, $item1->itemnumber);

    my $list_items = {
        verb => 'ListRecords',
        metadataPrefix => 'marc21',
        from => $utc_timestamp
    };
    my $list_no_items = {
        verb => 'ListRecords',
        metadataPrefix => 'marcxml',
        from => $utc_timestamp
    };

    my $get_items = {
        verb => 'GetRecord',
        metadataPrefix => 'marc21',
        identifier => 'TEST:' . $biblio1->biblionumber
    };
    my $get_no_items = {
        verb => 'GetRecord',
        metadataPrefix => 'marcxml',
        identifier => 'TEST:' . $biblio1->biblionumber
    };

    my $expected = {
        record => {
            header => {
                datestamp => $utc_timestamp,
                identifier => 'TEST:' . $biblio1->biblionumber
            },
            metadata => {
                record => XMLin(
                    GetMarcBiblio({ biblionumber => $biblio1->biblionumber, embed_items => 1, opac => 1 })->as_xml_record()
                )
            }
        }
    };
    my $expected_no_items = {
        record => {
            header => {
                datestamp => $utc_timestamp,
                identifier => 'TEST:' . $biblio1->biblionumber
            },
            metadata => {
                record => XMLin(
                    GetMarcBiblio({ biblionumber => $biblio1->biblionumber, embed_items => 0, opac => 1 })->as_xml_record()
                )
            }
        }
    };

    test_query(
        'ListRecords - biblio with a single item',
        $list_items,
        { ListRecords => $expected }
    );
    test_query(
        'ListRecords - biblio with a single item (items not returned)',
        $list_no_items,
        { ListRecords => $expected_no_items }
    );
    test_query(
        'GetRecord - biblio with a single item',
        $get_items,
        { GetRecord => $expected }
    );
    test_query(
        'GetRecord - biblio with a single item (items not returned)',
        $get_no_items,
        { GetRecord => $expected_no_items }
    );

    # Add an item 10 seconds later and check results
    set_relative_time(10);

    $utc_datetime = dt_from_string(undef, undef, 'UTC');
    $utc_timestamp = $utc_datetime->ymd . 'T' . $utc_datetime->hms . 'Z';
    $timestamp = dt_from_string(undef, 'sql');

    my $item2 = $builder->build_sample_item(
        {
            biblionumber => $biblio1->biblionumber
        }
    );
    $sth_item->execute($timestamp, $item2->itemnumber);

    $expected->{record}{header}{datestamp} = $utc_timestamp;
    $expected->{record}{metadata}{record} = XMLin(
        GetMarcBiblio({ biblionumber => $biblio1->biblionumber, embed_items => 1, opac => 1 })->as_xml_record()
    );

    test_query(
        'ListRecords - biblio with two items',
        $list_items,
        { ListRecords => $expected }
    );
    test_query(
        'ListRecords - biblio with two items (items not returned)',
        $list_no_items,
        { ListRecords => $expected_no_items }
    );
    test_query(
        'GetRecord - biblio with a two items',
        $get_items,
        { GetRecord => $expected }
    );
    test_query(
        'GetRecord - biblio with a two items (items not returned)',
        $get_no_items,
        { GetRecord => $expected_no_items }
    );

    # Set biblio timestamp 10 seconds later and check results
    set_relative_time(10);
    $utc_datetime = dt_from_string(undef, undef, 'UTC');
    $utc_timestamp= $utc_datetime->ymd . 'T' . $utc_datetime->hms . 'Z';
    $timestamp = dt_from_string(undef, 'sql');

    $sth_metadata->execute($timestamp, $biblio1->biblionumber);

    $expected->{record}{header}{datestamp} = $utc_timestamp;
    $expected_no_items->{record}{header}{datestamp} = $utc_timestamp;

    test_query(
        "ListRecords - biblio with timestamp higher than item's",
        $list_items,
        { ListRecords => $expected }
    );
    test_query(
        "ListRecords - biblio with timestamp higher than item's (items not returned)",
        $list_no_items,
        { ListRecords => $expected_no_items }
    );
    test_query(
        "GetRecord - biblio with timestamp higher than item's",
        $get_items,
        { GetRecord => $expected }
    );
    test_query(
        "GetRecord - biblio with timestamp higher than item's (items not returned)",
        $get_no_items,
        { GetRecord => $expected_no_items }
    );

    # Delete an item 10 seconds later and check results
    set_relative_time(10);
    $utc_datetime = dt_from_string(undef, undef, 'UTC');
    $utc_timestamp = $utc_datetime->ymd . 'T' . $utc_datetime->hms . 'Z';

    $item1->safe_delete({ skip_record_index =>1 });
    $sth_del_item->execute($timestamp, $item1->itemnumber);

    $expected->{record}{header}{datestamp} = $utc_timestamp;
    $expected->{record}{metadata}{record} = XMLin(
        GetMarcBiblio({ biblionumber => $biblio1->biblionumber, embed_items => 1, opac => 1 })->as_xml_record()
    );

    test_query(
        'ListRecords - biblio with existing and deleted item',
        $list_items,
        { ListRecords => $expected }
    );
    test_query(
        'ListRecords - biblio with existing and deleted item (items not returned)',
        $list_no_items,
        { ListRecords => $expected_no_items }
    );
    test_query(
        'GetRecord - biblio with existing and deleted item',
        $get_items,
        { GetRecord => $expected }
    );
    test_query(
        'GetRecord - biblio with existing and deleted item (items not returned)',
        $get_no_items,
        { GetRecord => $expected_no_items }
    );

    # Delete also the second item and verify results
    $item2->safe_delete({ skip_record_index =>1 });
    $sth_del_item->execute($timestamp, $item2->itemnumber);

    $expected->{record}{metadata}{record} = XMLin(
        GetMarcBiblio({ biblionumber => $biblio1->biblionumber, embed_items => 1, opac => 1 })->as_xml_record()
    );

    test_query(
        'ListRecords - biblio with two deleted items',
        $list_items,
        { ListRecords => $expected }
    );
    test_query(
        'ListRecords - biblio with two deleted items (items not returned)',
        $list_no_items,
        { ListRecords => $expected_no_items }
    );
    test_query(
        'GetRecord - biblio with two deleted items',
        $get_items,
        { GetRecord => $expected }
    );
    test_query(
        'GetRecord - biblio with two deleted items (items not returned)',
        $get_no_items,
        { GetRecord => $expected_no_items }
    );

    # Delete the biblio 10 seconds later and check results
    set_relative_time(10);
    $utc_datetime = dt_from_string(undef, undef, 'UTC');
    $utc_timestamp = $utc_datetime->ymd . 'T' . $utc_datetime->hms . 'Z';
    $timestamp = dt_from_string(undef, 'sql');

    is(undef, DelBiblio($biblio1->biblionumber, { skip_record_index =>1 }), 'Biblio deleted');
    $sth_del_metadata->execute($timestamp, $biblio1->biblionumber);

    my $expected_header = {
        record => {
            header => {
                datestamp => $utc_timestamp,
                identifier => 'TEST:' . $biblio1->biblionumber,
                status => 'deleted'
            }
        }
    };

    test_query(
        'ListRecords - deleted biblio with two deleted items',
        $list_items,
        { ListRecords => $expected_header }
    );
    test_query(
        'ListRecords - deleted biblio with two deleted items (items not returned)',
        $list_no_items,
        { ListRecords => $expected_header }
    );
    test_query(
        'GetRecord - deleted biblio with two deleted items',
        $get_items,
        { GetRecord => $expected_header }
    );
    test_query(
        'GetRecord - deleted biblio with two deleted items (items not returned)',
        $get_no_items,
        { GetRecord => $expected_header }
    );

    # Add a second biblio 10 seconds later and check that both are returned properly
    set_relative_time(10);
    $utc_datetime = dt_from_string(undef, undef, 'UTC');
    $utc_timestamp = $utc_datetime->ymd . 'T' . $utc_datetime->hms . 'Z';
    $timestamp = dt_from_string(undef, 'sql');

    my $biblio2 = $builder->build_sample_biblio();
    $sth_metadata->execute($timestamp, $biblio2->biblionumber);

    my $expected2 = {
        record => [
            $expected_header->{record},
            {
                header => {
                    datestamp => $utc_timestamp,
                    identifier => 'TEST:' . $biblio2->biblionumber
                },
                metadata => {
                    record => XMLin(
                        GetMarcBiblio({ biblionumber => $biblio2->biblionumber, embed_items => 1, opac => 1 })->as_xml_record()
                    )
                }
            }
        ]
    };
    my $expected2_no_items = {
        record => [
            $expected_header->{record},
            {
                header => {
                    datestamp => $utc_timestamp,
                    identifier => 'TEST:' . $biblio2->biblionumber
                },
                metadata => {
                    record => XMLin(
                        GetMarcBiblio({ biblionumber => $biblio2->biblionumber, embed_items => 0, opac => 1 })->as_xml_record()
                    )
                }
            }
        ]
    };

    test_query(
        'ListRecords - deleted biblio and normal biblio',
        $list_items,
        { ListRecords => $expected2 }
    );
    test_query(
        'ListRecords - deleted biblio and normal biblio (items not returned)',
        $list_no_items,
        { ListRecords => $expected2_no_items }
    );

    restore_time();

    $schema->storage->txn_rollback;
};


Line 0 Link Here

(-)a/t/db_dependent/OAI/oaiconf_items.yaml (-1 / +11 lines)
0	-	1	format:
		2	marcxml:
		3	metadataPrefix: marcxml
		4	metadataNamespace: http://www.loc.gov/MARC21/slim http://www.loc.gov/standards/marcxml/schema/MARC21slim
		5	schema: http://www.loc.gov/MARC21/slim http://www.loc.gov/standards/marcxml/schema/MARC21slim.xsd
		6	include_items: 0
		7	marc21:
		8	metadataPrefix: marc21
		9	metadataNamespace: http://www.loc.gov/MARC21/slim http://www.loc.gov/standards/marcxml/schema/MARC21slim
		10	schema: http://www.loc.gov/MARC21/slim http://www.loc.gov/standards/marcxml/schema/MARC21slim.xsd
		11	include_items: 1

Return to bug 29135

Lines 35-90 sub new { Link Here

(-)a/Koha/OAI/Server/GetRecord.pm (-31 / +20 lines)
35	my ($biblionumber) = $args{identifier} =~ /^$prefix(.*)/;	35	my ($biblionumber) = $args{identifier} =~ /^$prefix(.*)/;
36	my $items_included = $repository->items_included( $args{metadataPrefix} );	36	my $items_included = $repository->items_included( $args{metadataPrefix} );
37	my $dbh = C4::Context->dbh;	37	my $dbh = C4::Context->dbh;
38	my $sql = "	38	my $sql;
39	SELECT timestamp
40	FROM biblioitems
41	WHERE biblionumber=?
42	";
43	my @bind_params = ($biblionumber);	39	my @bind_params = ($biblionumber);
44	if ( $items_included ) {	40	if ( $items_included ) {
45	# Take latest timestamp of biblio and any items	41	# Take latest timestamp of biblio and any items
46	# Or timestamp of deleted items where bib not deleted	42	# Or timestamp of deleted items where bib not deleted
47	$sql .= "	43	$sql .= "
48	UNION	44	SELECT timestamp
		45	FROM biblio_metadata
		46	WHERE biblionumber=?
		47	UNION
49	SELECT deleteditems.timestamp FROM deleteditems JOIN biblio USING (biblionumber)	48	SELECT deleteditems.timestamp FROM deleteditems JOIN biblio USING (biblionumber)
50	WHERE deleteditems.biblionumber=?	49	WHERE deleteditems.biblionumber=?
51	UNION	50	UNION
52	SELECT timestamp from items	51	SELECT timestamp from items
53	WHERE biblionumber=?	52	WHERE biblionumber=?
54	";	53	";
55	push @bind_params, $biblionumber;	54	push @bind_params, $biblionumber;
56	push @bind_params, $biblionumber;	55	push @bind_params, $biblionumber;
57	$sql = "	56	$sql = "
58	SELECT max(timestamp)	57	SELECT max(timestamp) as timestamp
59	FROM ($sql) bib	58	FROM ($sql) bib
60	";	59	";
		60	} else {
		61	$sql = "
		62	SELECT max(timestamp) as timestamp
		63	FROM biblio_metadata
		64	WHERE biblionumber=?
		65	";
61	}	66	}
62		67
63	my $sth = $dbh->prepare( $sql ) \|\| die( 'Could not prepare statement: ' . $dbh->errstr );	68	my $sth = $dbh->prepare( $sql ) \|\| die( 'Could not prepare statement: ' . $dbh->errstr );
64	$sth->execute( @bind_params ) \|\| die( 'Could not execute statement: ' . $sth->errstr );	69	$sth->execute( @bind_params ) \|\| die( 'Could not execute statement: ' . $sth->errstr );
65	my ($timestamp, $deleted);	70	my ($timestamp, $deleted);
		71	# If there are no rows in biblio_metadata, try deletedbiblio_metadata
66	unless ( ($timestamp = $sth->fetchrow) ) {	72	unless ( ($timestamp = $sth->fetchrow) ) {
67	$sql = "	73	$sql = "
68	SELECT timestamp	74	SELECT max(timestamp)
69	FROM deletedbiblio	75	FROM deletedbiblio_metadata
70	WHERE biblionumber=?	76	WHERE biblionumber=?
71	";	77	";
72	@bind_params = ($biblionumber);	78	@bind_params = ($biblionumber);
73		79
74	if ( $items_included ) {
75	# Take latest timestamp among biblio and items
76	$sql .= "
77	UNION
78	SELECT timestamp from deleteditems
79	WHERE biblionumber=?
80	";
81	push @bind_params, $biblionumber;
82	$sql = "
83	SELECT max(timestamp)
84	FROM ($sql) bib
85	";
86	}
87
88	$sth = $dbh->prepare($sql) \|\| die('Could not prepare statement: ' . $dbh->errstr);	80	$sth = $dbh->prepare($sql) \|\| die('Could not prepare statement: ' . $dbh->errstr);
89	$sth->execute( @bind_params ) \|\| die('Could not execute statement: ' . $sth->errstr);	81	$sth->execute( @bind_params ) \|\| die('Could not execute statement: ' . $sth->errstr);
90	unless ( ($timestamp = $sth->fetchrow) )	82	unless ( ($timestamp = $sth->fetchrow) )
Lines 113-122 sub new { Link Here
113	} else {	105	} else {
114	# We fetch it using this method, rather than the database directly,	106	# We fetch it using this method, rather than the database directly,
115	# so it'll include the item data	107	# so it'll include the item data
116	my $marcxml;	108	my $marcxml = $repository->get_biblio_marcxml($biblionumber, $args{metadataPrefix});
117	$marcxml = $repository->get_biblio_marcxml($biblionumber, $args{metadataPrefix})
118	unless $deleted;
119
120	$self->record(	109	$self->record(
121	Koha::OAI::Server::Record->new($repository, $marcxml, $timestamp, \@setSpecs, %args)	110	Koha::OAI::Server::Record->new($repository, $marcxml, $timestamp, \@setSpecs, %args)
122	);	111	);

Lines 62-136 sub GetRecords { Link Here

(-)a/Koha/OAI/Server/ListBase.pm (-46 / +41 lines)
62		62
63	my @part_bind_params = ($next_id);	63	my @part_bind_params = ($next_id);
64		64
65	my $where = "biblionumber >= ?";	65	# Note: "main" is alias of the main table in the SELECT statement to avoid ambiquity with joined tables
		66	my $where = "main.biblionumber >= ?";
66	if ( $from ) {	67	if ( $from ) {
67	$where .= " AND timestamp >= ?";	68	$where .= " AND main.timestamp >= ?";
68	push @part_bind_params, $from;	69	push @part_bind_params, $from;
69	}	70	}
70	if ( $until ) {	71	if ( $until ) {
71	$where .= " AND timestamp <= ?";	72	$where .= " AND main.timestamp <= ?";
72	push @part_bind_params, $until;	73	push @part_bind_params, $until;
73	}	74	}
74	if ( defined $set ) {	75	if ( defined $set ) {
75	$where .= " AND biblionumber in (SELECT osb.biblionumber FROM oai_sets_biblios osb WHERE osb.set_id = ?)";	76	$where .= " AND main.biblionumber in (SELECT osb.biblionumber FROM oai_sets_biblios osb WHERE osb.set_id = ?)";
76	push @part_bind_params, $set->{'id'};	77	push @part_bind_params, $set->{'id'};
77	}	78	}
78		79
79	my @bind_params = @part_bind_params;	80	my @bind_params = @part_bind_params;
80		81
81	my $criteria = "WHERE $where ORDER BY biblionumber LIMIT " . ($max + 1);	82	my $order_limit = 'ORDER BY main.biblionumber LIMIT ' . ($max + 1);
82		83
83	my $sql = "	84	my $sql;
84	SELECT biblionumber	85	my $ts_sql;
85	FROM $table
86	$criteria
87	";
88		86
89	# If items are included, fetch a set of potential biblionumbers from items tables as well.	87	# If items are included, fetch a set of potential biblionumbers from items tables as well.
90	# Then merge them, sort them and take the required number of them from the resulting list.	88	# Then merge them, sort them and take the required number of them from the resulting list.
91	# This may seem counter-intuitive as in worst case we fetch 3 times the biblionumbers needed,	89	# This may seem counter-intuitive as in worst case we fetch 3 times the biblionumbers needed,
92	# but avoiding joins or subqueries makes this so much faster that it does not matter.	90	# but avoiding joins or subqueries makes this so much faster that it does not matter.
93	if ( $include_items ) {	91	if ( $include_items && !$deleted ) {
94	$sql = "($sql) UNION (SELECT DISTINCT(biblionumber) FROM deleteditems $criteria)";	92	$sql = "
		93	(SELECT biblionumber
		94	FROM $table main
		95	WHERE $where $order_limit)
		96	UNION
		97	(SELECT DISTINCT(biblionumber) FROM deleteditems main JOIN biblio USING (biblionumber) WHERE $where
		98	$order_limit)
		99	UNION
		100	(SELECT DISTINCT(biblionumber) FROM items main WHERE $where $order_limit)";
95	push @bind_params, @part_bind_params;	101	push @bind_params, @part_bind_params;
96	if (!$deleted) {	102	push @bind_params, @part_bind_params;
97	$sql .= " UNION (SELECT DISTINCT(biblionumber) FROM items $criteria)";	103	$sql = "SELECT biblionumber FROM ($sql) main $order_limit";
98	push @bind_params, @part_bind_params;	104
99	}	105	$ts_sql = "
100	$sql = "SELECT biblionumber FROM ($sql) bibnos ORDER BY biblionumber LIMIT " . ($max + 1);	106	SELECT MAX(timestamp)
		107	FROM (
		108	SELECT timestamp FROM biblio_metadata WHERE biblionumber = ?
		109	UNION
		110	SELECT timestamp FROM deleteditems WHERE biblionumber = ?
		111	UNION
		112	SELECT timestamp FROM items WHERE biblionumber = ?
		113	) bi
		114	";
		115	} else {
		116	$sql = "
		117	SELECT biblionumber
		118	FROM $table main
		119	WHERE $where
		120	";
		121
		122	$ts_sql = "SELECT max(timestamp) FROM $table WHERE biblionumber = ?";
101	}	123	}
102		124
103	my $sth = $dbh->prepare( $sql ) \|\| die( 'Could not prepare statement: ' . $dbh->errstr );	125	my $sth = $dbh->prepare( $sql ) \|\| die( 'Could not prepare statement: ' . $dbh->errstr );
104	$sth->execute( @bind_params ) \|\| die( 'Could not execute statement: ' . $sth->errstr );
105
106	my $ts_sql;
107	if ( $include_items ) {
108	if ( $deleted ) {
109	$ts_sql = "
110	SELECT MAX(timestamp)
111	FROM (
112	SELECT timestamp FROM deletedbiblio_metadata WHERE biblionumber = ?
113	UNION
114	SELECT timestamp FROM deleteditems WHERE biblionumber = ?
115	) bis
116	";
117	} else {
118	$ts_sql = "
119	SELECT MAX(timestamp)
120	FROM (
121	SELECT timestamp FROM biblio_metadata WHERE biblionumber = ?
122	UNION
123	SELECT timestamp FROM deleteditems WHERE biblionumber = ?
124	UNION
125	SELECT timestamp FROM items WHERE biblionumber = ?
126	) bi
127	";
128	}
129	} else {
130	$ts_sql = "SELECT timestamp FROM $table WHERE biblionumber = ?";
131	}
132	my $ts_sth = $dbh->prepare( $ts_sql ) \|\| die( 'Could not prepare statement: ' . $dbh->errstr );	126	my $ts_sth = $dbh->prepare( $ts_sql ) \|\| die( 'Could not prepare statement: ' . $dbh->errstr );
133		127
		128	$sth->execute( @bind_params ) \|\| die( 'Could not execute statement: ' . $sth->errstr );
134	foreach my $row (@{ $sth->fetchall_arrayref() }) {	129	foreach my $row (@{ $sth->fetchall_arrayref() }) {
135	my $biblionumber = $row->[0];	130	my $biblionumber = $row->[0];
136	$count++;	131	$count++;
Lines 149-155 sub GetRecords { Link Here
149	last STAGELOOP;	144	last STAGELOOP;
150	}	145	}
151	my @params = ($biblionumber);	146	my @params = ($biblionumber);
152	if ( $include_items ) {	147	if ( $include_items && !$deleted ) {
153	push @params, $deleted ? ( $biblionumber ) : ( $biblionumber, $biblionumber );	148	push @params, $deleted ? ( $biblionumber ) : ( $biblionumber, $biblionumber );
154	}	149	}
155	$ts_sth->execute( @params ) \|\| die( 'Could not execute statement: ' . $ts_sth->errstr );	150	$ts_sth->execute( @params ) \|\| die( 'Could not execute statement: ' . $ts_sth->errstr );

Lines 19-27 Link Here

(-)a/t/db_dependent/OAI/Server.t (-3 / +341 lines)
19		19
20	use Modern::Perl;	20	use Modern::Perl;
21	use Test::Deep qw( cmp_deeply re );	21	use Test::Deep qw( cmp_deeply re );
22	use Test::MockTime qw/set_fixed_time restore_time/;	22	use Test::MockTime qw/set_fixed_time set_relative_time restore_time/;
23		23
24	use Test::More tests => 32;	24	use Test::More tests => 33;
25	use DateTime;	25	use DateTime;
26	use File::Basename;	26	use File::Basename;
27	use File::Spec;	27	use File::Spec;
Lines 31-38 use XML::Simple; Link Here
31	use YAML::XS;	31	use YAML::XS;
32		32
33	use t::lib::Mocks;	33	use t::lib::Mocks;
		34	use t::lib::TestBuilder;
34		35
35	use C4::Biblio qw( AddBiblio GetMarcBiblio ModBiblio );	36	use C4::Biblio qw( AddBiblio GetMarcBiblio ModBiblio DelBiblio );
36	use C4::Context;	37	use C4::Context;
37	use C4::OAI::Sets qw(AddOAISet);	38	use C4::OAI::Sets qw(AddOAISet);
38		39
Lines 510-512 subtest 'ListSets tests' => sub { Link Here
510		511
511	$schema->storage->txn_rollback;	512	$schema->storage->txn_rollback;
512	};	513	};
		514
		515	subtest 'Tests for timestamp handling' => sub {
		516
		517	plan tests => 27;
		518
		519	t::lib::Mocks::mock_preference( 'OAI::PMH' => 1 );
		520	t::lib::Mocks::mock_preference( 'OAI-PMH:MaxCount' => 3 );
		521	t::lib::Mocks::mock_preference( 'OAI-PMH:ConfFile' => File::Spec->rel2abs(dirname(__FILE__)) . '/oaiconf_items.yaml' );
		522
		523	$schema->storage->txn_begin;
		524
		525	my $sth_metadata = $dbh->prepare('UPDATE biblio_metadata SET timestamp=? WHERE biblionumber=?');
		526	my $sth_del_metadata = $dbh->prepare('UPDATE deletedbiblio_metadata SET timestamp=? WHERE biblionumber=?');
		527	my $sth_item = $dbh->prepare('UPDATE items SET timestamp=? WHERE itemnumber=?');
		528	my $sth_del_item = $dbh->prepare('UPDATE deleteditems SET timestamp=? WHERE itemnumber=?');
		529
		530	my $builder = t::lib::TestBuilder->new;
		531
		532	set_fixed_time(CORE::time());
		533
		534	my $utc_datetime = dt_from_string(undef, undef, 'UTC');
		535	my $utc_timestamp = $utc_datetime->ymd . 'T' . $utc_datetime->hms . 'Z';
		536	my $timestamp = dt_from_string(undef, 'sql');
		537
		538	# Test a bib with one item
		539	my $biblio1 = $builder->build_sample_biblio();
		540	$sth_metadata->execute($timestamp, $biblio1->biblionumber);
		541	my $item1 = $builder->build_sample_item(
		542	{
		543	biblionumber => $biblio1->biblionumber
		544	}
		545	);
		546	$sth_item->execute($timestamp, $item1->itemnumber);
		547
		548	my $list_items = {
		549	verb => 'ListRecords',
		550	metadataPrefix => 'marc21',
		551	from => $utc_timestamp
		552	};
		553	my $list_no_items = {
		554	verb => 'ListRecords',
		555	metadataPrefix => 'marcxml',
		556	from => $utc_timestamp
		557	};
		558
		559	my $get_items = {
		560	verb => 'GetRecord',
		561	metadataPrefix => 'marc21',
		562	identifier => 'TEST:' . $biblio1->biblionumber
		563	};
		564	my $get_no_items = {
		565	verb => 'GetRecord',
		566	metadataPrefix => 'marcxml',
		567	identifier => 'TEST:' . $biblio1->biblionumber
		568	};
		569
		570	my $expected = {
		571	record => {
		572	header => {
		573	datestamp => $utc_timestamp,
		574	identifier => 'TEST:' . $biblio1->biblionumber
		575	},
		576	metadata => {
		577	record => XMLin(
578	GetMarcBiblio({ biblionumber => $biblio1->biblionumber, embed_items => 1, opac => 1 })->as_xml_record()
579	)
580	}
581	}
582	};
583	my $expected_no_items = {
584	record => {
585	header => {
586	datestamp => $utc_timestamp,
587	identifier => 'TEST:' . $biblio1->biblionumber
588	},
589	metadata => {
590	record => XMLin(
591	GetMarcBiblio({ biblionumber => $biblio1->biblionumber, embed_items => 0, opac => 1 })->as_xml_record()
592	)
593	}
594	}
595	};
596
597	test_query(
598	'ListRecords - biblio with a single item',
599	$list_items,
600	{ ListRecords => $expected }
601	);
602	test_query(
603	'ListRecords - biblio with a single item (items not returned)',
604	$list_no_items,
605	{ ListRecords => $expected_no_items }
606	);
607	test_query(
608	'GetRecord - biblio with a single item',
609	$get_items,
610	{ GetRecord => $expected }
611	);
612	test_query(
613	'GetRecord - biblio with a single item (items not returned)',
614	$get_no_items,
615	{ GetRecord => $expected_no_items }
616	);
617
618	# Add an item 10 seconds later and check results
619	set_relative_time(10);
620
621	$utc_datetime = dt_from_string(undef, undef, 'UTC');
622	$utc_timestamp = $utc_datetime->ymd . 'T' . $utc_datetime->hms . 'Z';
623	$timestamp = dt_from_string(undef, 'sql');
624
625	my $item2 = $builder->build_sample_item(
626	{
627	biblionumber => $biblio1->biblionumber
628	}
629	);
630	$sth_item->execute($timestamp, $item2->itemnumber);
631
632	$expected->{record}{header}{datestamp} = $utc_timestamp;
633	$expected->{record}{metadata}{record} = XMLin(
634	GetMarcBiblio({ biblionumber => $biblio1->biblionumber, embed_items => 1, opac => 1 })->as_xml_record()
635	);
636
637	test_query(
638	'ListRecords - biblio with two items',
639	$list_items,
640	{ ListRecords => $expected }
641	);
642	test_query(
643	'ListRecords - biblio with two items (items not returned)',
644	$list_no_items,
645	{ ListRecords => $expected_no_items }
646	);
647	test_query(
648	'GetRecord - biblio with a two items',
649	$get_items,
650	{ GetRecord => $expected }
651	);
652	test_query(
653	'GetRecord - biblio with a two items (items not returned)',
654	$get_no_items,
655	{ GetRecord => $expected_no_items }
656	);
657
658	# Set biblio timestamp 10 seconds later and check results
659	set_relative_time(10);
660	$utc_datetime = dt_from_string(undef, undef, 'UTC');
661	$utc_timestamp= $utc_datetime->ymd . 'T' . $utc_datetime->hms . 'Z';
662	$timestamp = dt_from_string(undef, 'sql');
663
664	$sth_metadata->execute($timestamp, $biblio1->biblionumber);
665
666	$expected->{record}{header}{datestamp} = $utc_timestamp;
667	$expected_no_items->{record}{header}{datestamp} = $utc_timestamp;
668
669	test_query(
670	"ListRecords - biblio with timestamp higher than item's",
671	$list_items,
672	{ ListRecords => $expected }
673	);
674	test_query(
675	"ListRecords - biblio with timestamp higher than item's (items not returned)",
676	$list_no_items,
677	{ ListRecords => $expected_no_items }
678	);
679	test_query(
680	"GetRecord - biblio with timestamp higher than item's",
681	$get_items,
682	{ GetRecord => $expected }
683	);
684	test_query(
685	"GetRecord - biblio with timestamp higher than item's (items not returned)",
686	$get_no_items,
687	{ GetRecord => $expected_no_items }
688	);
689
690	# Delete an item 10 seconds later and check results
691	set_relative_time(10);
692	$utc_datetime = dt_from_string(undef, undef, 'UTC');
693	$utc_timestamp = $utc_datetime->ymd . 'T' . $utc_datetime->hms . 'Z';
694
695	$item1->safe_delete({ skip_record_index =>1 });
696	$sth_del_item->execute($timestamp, $item1->itemnumber);
697
698	$expected->{record}{header}{datestamp} = $utc_timestamp;
699	$expected->{record}{metadata}{record} = XMLin(
700	GetMarcBiblio({ biblionumber => $biblio1->biblionumber, embed_items => 1, opac => 1 })->as_xml_record()
701	);
702
703	test_query(
704	'ListRecords - biblio with existing and deleted item',
705	$list_items,
706	{ ListRecords => $expected }
707	);
708	test_query(
709	'ListRecords - biblio with existing and deleted item (items not returned)',
710	$list_no_items,
711	{ ListRecords => $expected_no_items }
712	);
713	test_query(
714	'GetRecord - biblio with existing and deleted item',
715	$get_items,
716	{ GetRecord => $expected }
717	);
718	test_query(
719	'GetRecord - biblio with existing and deleted item (items not returned)',
720	$get_no_items,
721	{ GetRecord => $expected_no_items }
722	);
723
724	# Delete also the second item and verify results
725	$item2->safe_delete({ skip_record_index =>1 });
726	$sth_del_item->execute($timestamp, $item2->itemnumber);
727
728	$expected->{record}{metadata}{record} = XMLin(
729	GetMarcBiblio({ biblionumber => $biblio1->biblionumber, embed_items => 1, opac => 1 })->as_xml_record()
730	);
731
732	test_query(
733	'ListRecords - biblio with two deleted items',
734	$list_items,
735	{ ListRecords => $expected }
736	);
737	test_query(
738	'ListRecords - biblio with two deleted items (items not returned)',
739	$list_no_items,
740	{ ListRecords => $expected_no_items }
741	);
742	test_query(
743	'GetRecord - biblio with two deleted items',
744	$get_items,
745	{ GetRecord => $expected }
746	);
747	test_query(
748	'GetRecord - biblio with two deleted items (items not returned)',
749	$get_no_items,
750	{ GetRecord => $expected_no_items }
751	);
752
753	# Delete the biblio 10 seconds later and check results
754	set_relative_time(10);
755	$utc_datetime = dt_from_string(undef, undef, 'UTC');
756	$utc_timestamp = $utc_datetime->ymd . 'T' . $utc_datetime->hms . 'Z';
757	$timestamp = dt_from_string(undef, 'sql');
758
759	is(undef, DelBiblio($biblio1->biblionumber, { skip_record_index =>1 }), 'Biblio deleted');
760	$sth_del_metadata->execute($timestamp, $biblio1->biblionumber);
761
762	my $expected_header = {
763	record => {
764	header => {
765	datestamp => $utc_timestamp,
766	identifier => 'TEST:' . $biblio1->biblionumber,
767	status => 'deleted'
768	}
769	}
770	};
771
772	test_query(
773	'ListRecords - deleted biblio with two deleted items',
774	$list_items,
775	{ ListRecords => $expected_header }
776	);
777	test_query(
778	'ListRecords - deleted biblio with two deleted items (items not returned)',
779	$list_no_items,
780	{ ListRecords => $expected_header }
781	);
782	test_query(
783	'GetRecord - deleted biblio with two deleted items',
784	$get_items,
785	{ GetRecord => $expected_header }
786	);
787	test_query(
788	'GetRecord - deleted biblio with two deleted items (items not returned)',
789	$get_no_items,
790	{ GetRecord => $expected_header }
791	);
792
793	# Add a second biblio 10 seconds later and check that both are returned properly
794	set_relative_time(10);
795	$utc_datetime = dt_from_string(undef, undef, 'UTC');
796	$utc_timestamp = $utc_datetime->ymd . 'T' . $utc_datetime->hms . 'Z';
797	$timestamp = dt_from_string(undef, 'sql');
798
799	my $biblio2 = $builder->build_sample_biblio();
800	$sth_metadata->execute($timestamp, $biblio2->biblionumber);
801
802	my $expected2 = {
803	record => [
804	$expected_header->{record},
805	{
806	header => {
807	datestamp => $utc_timestamp,
808	identifier => 'TEST:' . $biblio2->biblionumber
809	},
810	metadata => {
811	record => XMLin(
812	GetMarcBiblio({ biblionumber => $biblio2->biblionumber, embed_items => 1, opac => 1 })->as_xml_record()
813	)
814	}
815	}
816	]
817	};
818	my $expected2_no_items = {
819	record => [
820	$expected_header->{record},
821	{
822	header => {
823	datestamp => $utc_timestamp,
824	identifier => 'TEST:' . $biblio2->biblionumber
825	},
826	metadata => {
827	record => XMLin(
828	GetMarcBiblio({ biblionumber => $biblio2->biblionumber, embed_items => 0, opac => 1 })->as_xml_record()
829	)
830	}
831	}
832	]
833	};
834
835	test_query(
836	'ListRecords - deleted biblio and normal biblio',
837	$list_items,
838	{ ListRecords => $expected2 }
839	);
840	test_query(
841	'ListRecords - deleted biblio and normal biblio (items not returned)',
842	$list_no_items,
843	{ ListRecords => $expected2_no_items }
844	);
845
846	restore_time();
847
848	$schema->storage->txn_rollback;
849	};
850