Update the SQL reports (Remove biblioitems.marcxml field)

13 Jan 2017

      Hi devs,

Bug 17196 is on its way to master, that means that some SQL reports from
our wiki will be obsolete.

https://wiki.koha-community.org/wiki/SQL_Reports_Library#Query_MARC

We need to provide to libraries the examples of equivalent reports to help
them updating their custom reports.

It seems very easy to update the reports from the wiki, but we will need to
propose the 2 versions (with and without the biblioitems.marcxml field).
As the marcxml will be moved to the biblio_metadata.metadata field, the
reports are very easy to update, for instance:

1/ Simple request on the biblioitems table:

    SELECT biblionumber, ExtractValue(marcxml,
'count(//datafield[@tag="505"])') AS count505
    FROM biblioitems
    HAVING count505 > 1;

Will become:

    SELECT biblionumber, ExtractValue(metadata,
'count(//datafield[@tag="505"])') AS count505
    FROM biblio_metadata
    HAVING count505 > 1;

2/ With info from the biblio table:

    SELECT biblionumber, substring(
ExtractValue(marcxml,'//controlfield[@tag="008"]'), 8,4 ) AS 'PUB DATE',
title
    FROM biblioitems
    INNER JOIN biblio USING (biblionumber)
    WHERE biblionumber = 14;

Will become:

    SELECT biblionumber, substring(
ExtractValue(metadata,'//controlfield[@tag="008"]'), 8,4 ) AS 'PUB DATE',
title
    FROM biblio_metadata
    INNER JOIN biblio USING (biblionumber)
    WHERE biblionumber = 14;

3/ Move complex query:

    SELECT concat(b.title, ' ', ExtractValue(m.marcxml,
'//datafield[@tag="245"]/subfield[@code="b"]')) AS title, b.author,
count(h.reservedate) AS 'holds'
    FROM biblio b
    LEFT JOIN biblioitems m USING (biblionumber)
    LEFT JOIN reserves h ON (b.biblionumber=h.biblionumber)
    GROUP BY b.biblionumber
    HAVING count(h.reservedate) >= 42;

Will become:

    SELECT concat(b.title, ' ', ExtractValue(m.metadata,
'//datafield[@tag="245"]/subfield[@code="b"]')) AS title, b.author,
count(h.reservedate) AS 'holds'
    FROM biblio b
    LEFT JOIN biblio_metadata m USING (biblionumber)
    LEFT JOIN reserves h ON (b.biblionumber=h.biblionumber)
    GROUP BY b.biblionumber
    HAVING count(h.reservedate) >= 42;

So basilly "biblioitems" becomes "biblio_metadata" and marcxml becomes
"metadata".
We could almost script it!
The only difficulty I see is when we will need infos from the biblioitems
table, we will need to add a join on biblio_metadata.

I was going to add the biblio_metadata version on the wiki, but, the first
example of ExtractValue is completely wrong. It says that 2 queries are
equivalent ("they are equivalent") and that "they return the whole 952
field". Which is totally wrong.

The work is a bit more complex than expected apparently, this wiki page
need to be updated and cleaned first. Then we will be able to provide
equivalent queries.

Cheers,
Jonathan

Jonathan Druart

Jonathan Druart

tags

participants (1)