Koha 17.05 Upgrade Issues with the biblioitems.marcxml table

In Koha 17.05, the Koha community decided to move the marcxml field out of the biblioitems table, into a table called biblio_metadata. (For more details, see Bug 17196: Move marcxml out of the biblioitems table.) This was done for good technical reasons, but unfortunately, it breaks a lot of reports that pull data from fields in the marc record that are not mapped to the Koha database. In order to make the transition somewhat easier, the community created Bug 17898: Add a way to automatically convert SQL reports. As you can see in the image below, this patch adds an Update SQL button to Home > Reports > Guided Reports Wizard > Saved Reports for each affected query.

Clicking the Update SQL button brings up a window which will allow the user to inspect the changes to the query, and accept the changes by clicking update if they’re acceptable.The Update SQL button will replace every occurrence of biblioitems with biblio_metadata and every marcxml with metadata. This will work perfectly as long as marcxml is the only field used from the biblioitems table.

SELECT
    biblio.title,
    ExtractValue(biblioitems.marcxml,'//controlfield[@tag="001"]') AS "field_001"
FROM
    biblio
    LEFT JOIN biblioitems USING (biblionumber)

will be converted to

SELECT
    biblio.title,    
    ExtractValue(biblio_metadata.metadata,'//controlfield[@tag="001"]') AS "field_001"
FROM
    biblio
    LEFT JOIN biblio_metadata USING (biblionumber)

Adding an extra field, like ISBN spoils the party.

SELECT
    biblio.title,
    biblioitems.isbn,
    ExtractValue(biblioitems.marcxml,'//controlfield[@tag="001"]') AS "field_001",
FROM
    biblio
    LEFT JOIN biblioitems USING (biblionumber)

 

… because the metadata table does not contain an ISBN column, this query will fail:

SELECT
    biblio.title,
    metadata.isbn,
    ExtractValue(biblio_metadata.metadata,'//controlfield[@tag="001"]') AS "field_001",
FROM
    biblio
    LEFT JOIN biblio_metadata USING (biblionumber)

So, these queries will need to be adjusted by hand.

Any biblioitems columns other than marcxml or biblionumber must be called from the biblioitems table i.e. biblioitems.isbn stays biblioitems.isbn. Both the biblio_metadata and biblioitems need to be joined in order for the columns to be available. You should be fairly safe copying the biblioitems join, then changing biblioitems to biblio_metadata.

LEFT JOIN biblioitems USING (biblionumber)

Becomes…

    LEFT JOIN biblioitems USING (biblionumber)
    LEFT JOIN biblio_metadata USING (biblionumber)

The edited query looks like this:

SELECT
    biblio.title,
    biblioitems.isbn,
    ExtractValue(biblio_metadata.metadata,'//controlfield[@tag="001"]') AS "field_001",
FROM
    biblio
    LEFT JOIN biblio_metadata USING (biblionumber)
    LEFT JOIN biblioitems USING (biblionumber)

Our Plan for Upgrading Reports