[Koha-devel] Update the SQL reports (Remove biblioitems.marcxml field)
Jonathan Druart
jonathan.druart at bugs.koha-community.org
Fri Jan 13 12:33:44 CET 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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.koha-community.org/pipermail/koha-devel/attachments/20170113/da38bb49/attachment.html>
More information about the Koha-devel
mailing list