Finding invalid XML characters in Koha data via SQL
Hi all, I just wanted to share a (MariaDB) SQL report that I wrote for finding bib records with invalid XML characters: select biblionumber from biblio_metadata where metadata REGEXP '[^\\x{0009}\\x{000A}\\x{000D}\\x{0020}-\\x{D7FF}\\x{E000}-\\x{FFFD}\\x{1000 0}-\\x{10FFFF}]+'; Newer versions of Koha strip invalid character from the XML so that you can fix your records. I figure this report is very valuable when coupled with that functionality. In fact, I just advised a library today to use them together to fix up some bad data in their catalogue. -- On a related note, I've noticed that you can have a record with good bib XML but invalid item XML, and you won't notice until your record fails to be indexed. So I'm planning on writing a report for that too. I'm thinking it might be good to add these reports to core Koha, so that people can find and fix their own metadata problems. What do people think? David Cook Senior Software Engineer Prosentient Systems Suite 7.03 6a Glen St Milsons Point NSW 2061 Australia Office: 02 9212 0899 Online: 02 8005 0595
Alas, I couldn't think of a really clever way of doing the items table, so I think it'll need a Perl-based solution. I have a RepairRecord plugin, so I might do a version in that first, and if that goes well I could look at upstreaming a patch. David Cook Senior Software Engineer Prosentient Systems Suite 7.03 6a Glen St Milsons Point NSW 2061 Australia Office: 02 9212 0899 Online: 02 8005 0595 From: Koha-devel <koha-devel-bounces@lists.koha-community.org> On Behalf Of David Cook via Koha-devel Sent: Friday, 12 April 2024 11:36 AM To: 'Koha-devel' <koha-devel@lists.koha-community.org> Subject: [Koha-devel] Finding invalid XML characters in Koha data via SQL Hi all, I just wanted to share a (MariaDB) SQL report that I wrote for finding bib records with invalid XML characters: select biblionumber from biblio_metadata where metadata REGEXP '[^\\x{0009}\\x{000A}\\x{000D}\\x{0020}-\\x{D7FF}\\x{E000}-\\x{FFFD}\\x{1000 0}-\\x{10FFFF}]+'; Newer versions of Koha strip invalid character from the XML so that you can fix your records. I figure this report is very valuable when coupled with that functionality. In fact, I just advised a library today to use them together to fix up some bad data in their catalogue. -- On a related note, I've noticed that you can have a record with good bib XML but invalid item XML, and you won't notice until your record fails to be indexed. So I'm planning on writing a report for that too. I'm thinking it might be good to add these reports to core Koha, so that people can find and fix their own metadata problems. What do people think? David Cook Senior Software Engineer Prosentient Systems Suite 7.03 6a Glen St Milsons Point NSW 2061 Australia Office: 02 9212 0899 Online: 02 8005 0595
Not yet. At some point I need to get better at sharing Koha plugins more widely. David Cook Senior Software Engineer Prosentient Systems Suite 7.03 6a Glen St Milsons Point NSW 2061 Australia Office: 02 9212 0899 Online: 02 8005 0595 -----Original Message----- From: Koha-devel <koha-devel-bounces@lists.koha-community.org> On Behalf Of Magnus Enger via Koha-devel Sent: Friday, 12 April 2024 4:09 PM To: koha-devel@lists.koha-community.org Subject: Re: [Koha-devel] Finding invalid XML characters in Koha data via SQL Den 12.04.2024 04:13, skrev David Cook via Koha-devel:
I have a RepairRecord plugin, so I might do a version in that first, and if that goes well I could look at upstreaming a patch…
Intriguing! :-) Is it available somewhere? Best regards, Magnus _______________________________________________ Koha-devel mailing list Koha-devel@lists.koha-community.org https://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-devel website : https://www.koha-community.org/ git : https://git.koha-community.org/ bugs : https://bugs.koha-community.org/
+1 ________________________________ Van: Koha-devel <koha-devel-bounces@lists.koha-community.org> namens David Cook via Koha-devel <koha-devel@lists.koha-community.org> Verzonden: vrijdag 12 april 2024 03:36 Aan: 'Koha-devel' <koha-devel@lists.koha-community.org> Onderwerp: [Koha-devel] Finding invalid XML characters in Koha data via SQL Hi all, I just wanted to share a (MariaDB) SQL report that I wrote for finding bib records with invalid XML characters: select biblionumber from biblio_metadata where metadata REGEXP '[^\\x{0009}\\x{000A}\\x{000D}\\x{0020}-\\x{D7FF}\\x{E000}-\\x{FFFD}\\x{10000}-\\x{10FFFF}]+'; Newer versions of Koha strip invalid character from the XML so that you can fix your records. I figure this report is very valuable when coupled with that functionality. In fact, I just advised a library today to use them together to fix up some bad data in their catalogue. -- On a related note, I’ve noticed that you can have a record with good bib XML but invalid item XML, and you won’t notice until your record fails to be indexed. So I’m planning on writing a report for that too. I’m thinking it might be good to add these reports to core Koha, so that people can find and fix their own metadata problems. What do people think? David Cook Senior Software Engineer Prosentient Systems Suite 7.03 6a Glen St Milsons Point NSW 2061 Australia Office: 02 9212 0899 Online: 02 8005 0595
Hi! Den 12.04.2024 03:36, skrev David Cook via Koha-devel:
Hi all,
I just wanted to share a (MariaDB) SQL report that I wrote for finding bib records with invalid XML characters:
select biblionumber from biblio_metadata where metadata REGEXP '[^\\x{0009}\\x{000A}\\x{000D}\\x{0020}-\\x{D7FF}\\x{E000}-\\x{FFFD}\\x{10000}-\\x{10FFFF}]+';
Newer versions of Koha strip invalid character from the XML so that you can fix your records. I figure this report is very valuable when coupled with that functionality. In fact, I just advised a library today to use them together to fix up some bad data in their catalogue.
--
On a related note, I’ve noticed that you can have a record with good bib XML but invalid item XML, and you won’t notice until your record fails to be indexed. So I’m planning on writing a report for that too.
I’m thinking it might be good to add these reports to core Koha, so that people can find and fix their own metadata problems. What do people think?
Sounds like an excellent idea! Sounds kind of similar to "MARC bibliographic framework test" at /cgi-bin/koha/admin/checkmarc.pl The report could also be added to https://wiki.koha-community.org/wiki/SQL_Reports_Library for older Kohas and to be immediately useful. Best regards, Magnus
Yeah, I was thinking it would be good to add to the SQL Report Library. I've just been flat out today... (but wanted to make sure I shared it with you folk at least) David Cook Senior Software Engineer Prosentient Systems Suite 7.03 6a Glen St Milsons Point NSW 2061 Australia Office: 02 9212 0899 Online: 02 8005 0595 -----Original Message----- From: Koha-devel <koha-devel-bounces@lists.koha-community.org> On Behalf Of Magnus Enger via Koha-devel Sent: Friday, 12 April 2024 4:06 PM To: koha-devel@lists.koha-community.org Subject: Re: [Koha-devel] Finding invalid XML characters in Koha data via SQL Hi! Den 12.04.2024 03:36, skrev David Cook via Koha-devel:
Hi all,
I just wanted to share a (MariaDB) SQL report that I wrote for finding bib records with invalid XML characters:
select biblionumber from biblio_metadata where metadata REGEXP '[^\\x{0009}\\x{000A}\\x{000D}\\x{0020}-\\x{D7FF}\\x{E000}-\\x{FFFD}\\ x{10000}-\\x{10FFFF}]+';
Newer versions of Koha strip invalid character from the XML so that you can fix your records. I figure this report is very valuable when coupled with that functionality. In fact, I just advised a library today to use them together to fix up some bad data in their catalogue.
--
On a related note, I’ve noticed that you can have a record with good bib XML but invalid item XML, and you won’t notice until your record fails to be indexed. So I’m planning on writing a report for that too.
I’m thinking it might be good to add these reports to core Koha, so that people can find and fix their own metadata problems. What do people think?
Sounds like an excellent idea! Sounds kind of similar to "MARC bibliographic framework test" at /cgi-bin/koha/admin/checkmarc.pl The report could also be added to https://wiki.koha-community.org/wiki/SQL_Reports_Library for older Kohas and to be immediately useful. Best regards, Magnus _______________________________________________ Koha-devel mailing list Koha-devel@lists.koha-community.org https://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-devel website : https://www.koha-community.org/ git : https://git.koha-community.org/ bugs : https://bugs.koha-community.org/
Something else to add to search_for_data_inconsistencies.pl ? I like perl-based solution, and I appreciate centralized ones, even though I suppose what you're testing is not an "inconsistency". Logo inLibro <https://inLibro.com> Philippe Blouin Directeur de la technologie T 833-INLIBRO (465-4276) <tel:833-465-4276>, poste 230 C philippe.blouin@inLibro.com www.inLibro.com <https://inLibro.com> On 2024-04-11 21:36, David Cook via Koha-devel wrote:
Hi all,
I just wanted to share a (MariaDB) SQL report that I wrote for finding bib records with invalid XML characters:
select biblionumber from biblio_metadata where metadata REGEXP '[^\\x{0009}\\x{000A}\\x{000D}\\x{0020}-\\x{D7FF}\\x{E000}-\\x{FFFD}\\x{10000}-\\x{10FFFF}]+';
Newer versions of Koha strip invalid character from the XML so that you can fix your records. I figure this report is very valuable when coupled with that functionality. In fact, I just advised a library today to use them together to fix up some bad data in their catalogue.
--
On a related note, I’ve noticed that you can have a record with good bib XML but invalid item XML, and you won’t notice until your record fails to be indexed. So I’m planning on writing a report for that too.
I’m thinking it might be good to add these reports to core Koha, so that people can find and fix their own metadata problems. What do people think?
David Cook
Senior Software Engineer
Prosentient Systems
Suite 7.03
6a Glen St
Milsons Point NSW 2061
Australia
Office: 02 9212 0899
Online: 02 8005 0595
_______________________________________________ Koha-devel mailing list Koha-devel@lists.koha-community.org https://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-devel website :https://www.koha-community.org/ git :https://git.koha-community.org/ bugs :https://bugs.koha-community.org/
participants (4)
-
David Cook -
Magnus Enger -
Marcel de Rooy -
Philippe Blouin