We could almost script it!
What an awesome idea, isn't it? So let's make it real! See bug 17898 (Add a way to automatically convert SQL reports). Have a great week-end! On Fri, 13 Jan 2017 at 12:33 Jonathan Druart < jonathan.druart@bugs.koha-community.org> wrote:
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