Bug 17721 - Do we need utf8_bin collation on tagsubfield?
Summary: Do we need utf8_bin collation on tagsubfield?
Status: CLOSED REMIND
Alias: None
Product: Koha
Classification: Unclassified
Component: Architecture, internals, and plumbing (show other bugs)
Version: unspecified
Hardware: All All
: P5 - low enhancement (vote)
Assignee: Galen Charlton
QA Contact: Testopia
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2016-12-05 12:55 UTC by Marcel de Rooy
Modified: 2020-11-30 21:45 UTC (History)
4 users (show)

See Also:
Change sponsored?: ---
Patch complexity: ---
Documentation contact:
Documentation submission:
Text to go in the release notes:
Version(s) released in:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Marcel de Rooy 2016-12-05 12:55:33 UTC
Comes from report 17676. This discussion should be on a new report.
===
Comment11
> Indeed we want to keep tagsubfield a utf8_bin (to allow lowercase and uppercase of the same letter for subfields).
Could you provide an example where we want to do that? Is that MARC conform?
And if so(!), why would you need utf8_bin to do so? You can still insert them, only finding the right a or A would be harder.
It seems to me that we should remove this strange exception, and make sure that all tagsubfields are saved lowercase. Should not be too hard.
Can MARC::Record handle subfields a and A btw?
But this discussion should be on a new report.

> if ( $table[1] !~ /COLLATE=utf8_unicode_ci/ and $table[1] !~ /COLLATE=utf8mb4_unicode_ci/ ) { #catches utf8mb4 collated tables
This is only 99,9% safe (as you are probably aware of).
If you find one column in unicode_ci, the table might still be something else (theoretically). Perhaps someone added a custom column with collation?
Since you only change the default here, why not always do it? Replacing X with X will not be a problem..

Comment12&13 (Katrin)
It's not strictly MARC conform, but we use this a lot and I have talked to others using it as well. Take the 952 field as the example - all subfield codes are taken. Using upper case letters works fantastically now and allows you to store and index data that we got no other sensible spot for. We got it all working perfectly, why break this feature without need?
Ah, and I think I have encountered upper case in German MARC - just can't find a documentation right now.
===
Even if we do not change it at all, it would be worth writing somewhere why we do not. (And where we can still find it later.)
Comment 1 Nicolas Legrand 2017-11-21 13:51:47 UTC
We also use it a lot to describe our items. We have 995a, 995A and so on. I'm not sure it's used with others french library with UNIMARC, but it's a possibility.
Comment 2 Nicolas Legrand 2017-11-21 13:55:20 UTC
ha and also no, you can't insert a 995a with a 995A when it's collate=utf8_general_ci and the field is a key because it consider that 995a is the same key as 995A.
Comment 3 Katrin Fischer 2017-11-21 14:44:52 UTC
We need the table or the column to be case sensitive in order to be able to configure, store and retrieve uppercase and lowercase subfields separately.

I am no MySQL expert, so can't tell what is the perfect type to use for that use case.

Documenting the behaviour is of course always a good idea. A comment in kohastructure.sql maybe?
Comment 4 Marcel de Rooy 2017-11-27 09:55:26 UTC
(In reply to Katrin Fischer from comment #3)
> We need the table or the column to be case sensitive in order to be able to
> configure, store and retrieve uppercase and lowercase subfields separately.
> 
> I am no MySQL expert, so can't tell what is the perfect type to use for that
> use case.
> 
> Documenting the behaviour is of course always a good idea. A comment in
> kohastructure.sql maybe?

I am all for a comment at least.
As mentioned before, using uppercase subfields is not MARC conform. https://www.loc.gov/marc/96principl.html tells me:
===
Subfield codes in the MARC 21 formats consist of two characters--a delimiter [1F(16), 8-bit], followed by a data element identifier. A data element identifier may be any lowercase alphabetic or numeric character.
===
Dont be confused about the delimiter. The last sentence about data element is important.

Since many libraries seem to use the concept, we could permit it (somewhat reluctantly). A comment that we use this collation for that reason should be added in kohastructure.
Comment 5 Nicolas Legrand 2017-11-29 12:53:33 UTC
I don't see any such limitation in UNIMARC standard. Unfortunately, I don't have a ISO 2709 version right now so I can't check there too.
Comment 6 Nicolas Legrand 2019-09-23 13:40:36 UTC
I didn't find explicitly such a limitation in ISO 2709.
Comment 7 Katrin Fischer 2020-04-19 01:02:07 UTC
I think we have answered the question - if capital subfields are allowed in UNIMARC, we need the case sensitive handling of subfield codes.