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