> 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.dru...@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 >
_______________________________________________ Koha-devel mailing list Koha-devel@lists.koha-community.org http://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-devel website : http://www.koha-community.org/ git : http://git.koha-community.org/ bugs : http://bugs.koha-community.org/
