The SQL UNIMARC complet sample authorities fails : installer/data/mysql/fr-FR/marcflavour/unimarc_complet/Obligatoire/autorites_norme_unimarc.sql Some lines of auth_subfield_structure use an authtypecode not existing in lines of authtypecode : AUTTIT and FA
Created attachment 52680 [details] [review] Bug 16797 - errors in UNIMARC complet sample authorities The SQL UNIMARC complet sample authorities fails : installer/data/mysql/fr-FR/marcflavour/unimarc_complet/Obligatoire/autorites_norme_unimarc.sql Some lines of auth_subfield_structure use an authtypecode not existing in lines of authtypecode : AUTTIT, SAUT, SCO and STU. Also, I have merged lines refering to authtypecode FA and FAM. Test plan : - On a UNIMARC database : - clear auth tables : mysql> delete from auth_subfield_structure; mysql> delete from auth_tag_structure; mysql> delete from auth_types; - import sql file : mysql < installer/data/mysql/fr-FR/marcflavour/unimarc_complet/Obligatoire/autorites_norme_unimarc.sql => Without patch you get error : ERROR 1452 (23000) at line 1132: Cannot add or update a child row: a foreign key constraint fails (`koha`.`auth_subfield_structure`, CONSTRAINT `auth_subfield_structure_ibfk_1` FOREIGN KEY (`authtypecode`) REFERENCES `auth_types` (`authtypecode`) ON DELETE CASCADE ON UPDATE CASCADE) => With patch no errors : > select distinct(authtypecode) from auth_subfield_structure; +--------------+ | authtypecode | +--------------+ | | | CO | | CO_UNI_TI | | FAM | | GENRE/FORM | | NAME_COL | | NP | | PA | | SAUTTIT | | SNC | | SNG | | TM | | TU | +--------------+ 13 rows in set (0.00 sec)
Feedback: Let's start positive. Nice clean up on the FAM 520 a missing accent. However... 1) Where is FAM 220 j? FA didn't have it, but pre-existing FAM did. 2) Why were the FAM 420/520 x-z descriptions & values replaced with the FA ones? How did you decide which were correct? If you can explain these, I'll sign off.
(In reply to M. Tompsett from comment #2) Do you think I should not have merged FAM and FA ?
Created attachment 52780 [details] [review] Bug 16797 - errors in UNIMARC complet sample authorities The SQL UNIMARC complet sample authorities fails : installer/data/mysql/fr-FR/marcflavour/unimarc_complet/Obligatoire/autorites_norme_unimarc.sql Some lines of auth_subfield_structure use an authtypecode not existing in lines of authtypecode : AUTTIT, SAUT, SCO and STU. Test plan : - On a UNIMARC database : - clear auth tables : mysql> delete from auth_subfield_structure; mysql> delete from auth_tag_structure; mysql> delete from auth_types; - import sql file : mysql < installer/data/mysql/fr-FR/marcflavour/unimarc_complet/Obligatoire/autorites_norme_unimarc.sql => Without patch you get error : ERROR 1452 (23000) at line 1132: Cannot add or update a child row: a foreign key constraint fails (`koha`.`auth_subfield_structure`, CONSTRAINT `auth_subfield_structure_ibfk_1` FOREIGN KEY (`authtypecode`) REFERENCES `auth_types` (`authtypecode`) ON DELETE CASCADE ON UPDATE CASCADE) => With patch no errors : > select distinct(authtypecode) from auth_subfield_structure; +--------------+ | authtypecode | +--------------+ | | | CO | | CO_UNI_TI | | FAM | | GENRE/FORM | | NAME_COL | | NP | | PA | | SAUTTIT | | SNC | | SNG | | TM | | TU | +--------------+ 13 rows in set (0.00 sec)
I have removed from patch the FA/FAM part. I will provide a followup
Field 220 : ('FAM', '220', '3', 'Identification autorité', '', 0, 0, -1, '', '', NULL, 0, 0, 0, '', '', ''); ('FAM', '220', '7', 'Écriture de catalogage et écriture de la racine de la vedette', '', 0, 0, -1, '', '', NULL, 0, 0, 0, NULL, '', ''); ('FAM', '220', '8', 'Langue de catalogage et langue de la racine de la vedette', '', 0, 0, -1, '', '', NULL, 0, 0, 0, NULL, '', ''); ('FAM', '220', 'a', 'Elément d''entrée', '', 0, 0, 0, '', '', NULL, 0, 0, 0, NULL, '', ''); ('FAM', '220', 'f', 'Dates', '', 0, 0, 0, '', '', NULL, 0, 0, 0, NULL, '', ''); ('FAM', '220', 'j', 'Subdivision de forme', '', 1, 0, 0, '', '', NULL, 0, 0, 0, NULL, '', ''); ('FAM', '220', 'x', 'Subdivision de sujet', '', 1, 0, 0, '', '', NULL, 0, 0, 0, NULL, '', ''); ('FAM', '220', 'y', 'Subdivision géographique', '', 1, 0, 0, '', '', NULL, 0, 0, 0, NULL, '', ''); ('FAM', '220', 'z', 'Subdivision chronologique', '', 1, 0, 0, '', '', NULL, 0, 0, 0, NULL, '', ''); ('FA', '220', 'a', 'Elément d''entrée', '', 0, 0, 0, '', '', NULL, 0, 0, 0, '', '', ''); ('FA', '220', 'f', 'Dates', '', 0, 0, 0, '', '', NULL, 0, 0, 0, NULL, '', ''); ('FA', '220', 'x', 'Subdivision de sujet', '', 0, 0, 0, '', '', NULL, 0, 0, 0, NULL, '', ''); ('FA', '220', 'y', 'Subdivision géographique', '', 0, 0, 0, '', '', NULL, 0, 0, 0, NULL, '', ''); ('FA', '220', 'z', 'Subdivision chronologique', '', 0, 0, 0, '', '', NULL, 0, 0, 0, NULL, '', ''); I think we can use everything from FAM lines.
('FAM', '420', 'a', 'Elément d''entrée', '', 0, 0, 0, '', '', NULL, 0, 0, 0, '', '', ''); ('FAM', '420', 'f', 'Dates', '', 0, 0, 0, '', '', NULL, 0, 0, 0, NULL, '', ''); ('FAM', '420', 'j', 'Subdivision de forme', '', 0, 0, 0, '', '', NULL, 0, 0, 0, NULL, '', ''); ('FAM', '420', 'x', 'Subdivision de sujet', '', 0, 0, 0, '', '', NULL, 0, 0, 0, NULL, '', ''); ('FAM', '420', 'y', 'Subdivision géographique', '', 0, 0, 0, '', '', NULL, 0, 0, 0, NULL, '', ''); ('FAM', '420', 'z', 'Subdivision chronologique', '', 0, 0, 0, '', '', NULL, 0, 0, 0, NULL, '', ''); ('FA', '420', '0', 'Formule introductive', '', 0, 0, -1, '', '', '', NULL, 0, 0, NULL, '', NULL); ('FA', '420', '2', 'Code du système d''indexation matière', '', 0, 0, -1, '', '', '', NULL, 0, 0, NULL, '', NULL); ('FA', '420', '3', 'Identificateur de la notice d''autorité', '', 0, 0, -1, '', '', '', NULL, 0, 0, NULL, '', NULL); ('FA', '420', '5', 'Données codées relatives aux mentions de formes rejetées ou associées 0', '', 0, 0, -1, '', '', '', NULL, 0, 0, NULL, '', NULL); ('FA', '420', '6', 'Données de lien entre zones', '', 0, 0, -1, '', '', '', NULL, 0, 0, NULL, '', NULL); ('FA', '420', '7', 'Ecriture de catalogage et écriture de la racine de la vedette', '', 0, 0, -1, '', '', '', NULL, 0, 0, NULL, '', NULL); ('FA', '420', '8', 'Langue de catalogage et langue de la racine de la vedette', '', 0, 0, -1, '', '', '', NULL, 0, 0, NULL, '', NULL); ('FA', '420', 'a', 'Elément d''entrée', '', 0, 0, 0, '', '', '', NULL, 0, 0, NULL, '', NULL); ('FA', '420', 'j', 'Subdivision de forme', '', 1, 0, -1, '', '', '', NULL, 0, 0, NULL, '', NULL); ('FA', '420', 'x', 'Subdiv. de sujet', '', 1, 0, 0, '', '', '', NULL, 0, 0, NULL, '', NULL); ('FA', '420', 'y', 'Subdiv. géographique', '', 1, 0, 0, '', '', '', NULL, 0, 0, NULL, '', NULL); ('FA', '420', 'z', 'Subdiv. chronologique', '', 1, 0, 0, '', '', '', NULL, 0, 0, NULL, '', NULL); I think we can use all line from FA and 420$f from FAM, not repeatable like 220$f. Last value should be '' instead of NULL, its the column default value.
('FAM', '520', '5', 'Type de lien', '', 0, 0, 0, '', '', NULL, 0, 0, 0, '', '', ''); ('FAM', '520', '9', 'Numéro autorité koha', '', 0, 0, 0, '', '', NULL, 0, 0, 0, NULL, '', ''); ('FAM', '520', 'a', 'Element d''entrée', '', 0, 0, 0, '', '', NULL, 0, 0, 0, NULL, 'FAM', ''); ('FAM', '520', 'f', 'Dates', '', 0, 0, 0, '', '', NULL, 0, 0, 0, NULL, '', ''); ('FAM', '520', 'j', 'Subdivision de forme', '', 1, 0, 0, '', '', NULL, 0, 0, 0, NULL, '', ''); ('FAM', '520', 'x', 'Subdivision de sujet', '', 1, 0, 0, '', '', NULL, 0, 0, 0, NULL, '', ''); ('FAM', '520', 'y', 'Subdivision géographique', '', 1, 0, 0, '', '', NULL, 0, 0, 0, NULL, '', ''); ('FAM', '520', 'z', 'Subdivision chronologique', '', 1, 0, 0, '', '', NULL, 0, 0, 0, NULL, '', ''); ('FA', '520', '0', 'Formule introductive', '', 0, 0, -1, '', '', NULL, 0, 0, 0, '', '', ''); ('FA', '520', '2', 'Code du système d''indexation matière', '', 0, 0, -1, '', '', NULL, 0, 0, 0, NULL, '', ''); ('FA', '520', '3', 'Identificateur de la notice d''autorité', '', 0, 0, 0, '', '', NULL, 0, 0, 0, NULL, '', ''); ('FA', '520', '5', 'Données codées relatives aux mentions de formes rejetées ou associées 0', '', 0, 0, 0, 'AUT_LIEN', '', NULL, 0, 0, 0, NULL, '', ''); ('FA', '520', '6', 'Données de lien entre zones', '', 0, 0, -1, '', '', NULL, 0, 0, 0, NULL, '', ''); ('FA', '520', '7', 'Ecriture de catalogage et écriture de la racine de la vedette', '', 0, 0, -1, '', '', NULL, 0, 0, 0, NULL, '', ''); ('FA', '520', '8', 'Langue de catalogage et langue de la racine de la vedette', '', 0, 0, -1, '', '', NULL, 0, 0, 0, NULL, '', ''); ('FA', '520', '9', 'N° autorité koha', '', 0, 0, 0, '', '', NULL, 0, 0, 0, NULL, '', ''); ('FA', '520', 'a', 'Elément d''entrée', '', 0, 0, 0, '', '', NULL, 0, 0, 0, NULL, 'FA', ''); ('FA', '520', 'j', 'Subdivision de forme', '', 1, 0, 0, '', '', NULL, 0, 0, 0, NULL, '', ''); ('FA', '520', 'x', 'Subdiv. de sujet', '', 1, 0, 0, '', '', NULL, 0, 0, 0, NULL, '', ''); ('FA', '520', 'y', 'Subdiv. géographique', '', 1, 0, 0, '', '', NULL, 0, 0, 0, NULL, '', ''); ('FA', '520', 'z', 'Subdiv. chronologique', '', 1, 0, 0, '', '', NULL, 0, 0, 0, NULL, '', ''); I think we can use all line from FA and 520$f from FAM, not repeatable like 220$f. FA 520$a contains 'FA' in frameworkcode, I think this is an error it should be empty. 520$5 as a different description in FA and FAM, Id say use FA like in 420$5.
('FAM', '720', 'a', 'Elément d''entrée', '', 0, 0, 0, '', '', NULL, 0, 0, 0, '', '', ''); ('FAM', '720', 'f', 'Dates', '', 0, 0, 0, '', '', NULL, 0, 0, 0, NULL, '', ''); ('FAM', '720', 'j', 'Subdivision de forme', '', 1, 0, 0, '', '', NULL, 0, 0, 0, NULL, '', ''); ('FAM', '720', 'x', 'Subdivision de sujet', '', 1, 0, 0, '', '', NULL, 0, 0, 0, NULL, '', ''); ('FAM', '720', 'y', 'Subdivision géographique', '', 1, 0, 0, '', '', NULL, 0, 0, 0, NULL, '', ''); ('FAM', '720', 'z', 'Subdivision chronologique', '', 1, 0, 0, '', '', NULL, 0, 0, 0, NULL, '', ''); 720 exists only in FAM, use it. Maybe some subfields are missing, 750 as $2 $3 $8
"I think we can use everything from FAM lines." Ok "I think we can use all line from FA and 420$f from FAM, not repeatable like 220$f. Last value should be '' instead of NULL, its the column default value." Ok "I think we can use all line from FA and 520$f from FAM, not repeatable like 220$f. FA 520$a contains 'FA' in frameworkcode, I think this is an error it should be empty." Ok Add if not exists ('FAM', '720', '2', 'Code du système d\'indexation matière', '', 0, 0, 0, '', '', NULL, 0, 0, 0, '', '', ''); ('FAM', '720', '3', 'Identificateur de la notice d\'autorité', '', 0, 0, 0, '', '', NULL, 0, 0, 0, NULL, '', ''); ('FAM', '720', '7', 'Écriture de catalogage et écriture de la racine de la vedette', '', 1, 0, 0, '', '', NULL, 0, 0, 0, NULL, '', ''); ('FAM', '720', '8', 'Langue de catalogage et langue de la racine de la vedette', '', 1, 0, 0, '', '', NULL, 0, 0, 0, NULL, '', ''); and ('FAM', '750', '2', 'Code du système d\'indexation matière', '', 0, 0, 0, '', '', NULL, 0, 0, 0, '', '', ''); ('FAM', '720', '3', 'Identificateur de la notice d\'autorité', '', 0, 0, 0, '', '', NULL, 0, 0, 0, NULL, '', ''); ('FAM', '750', '7', 'Écriture de catalogage et écriture de la racine de la vedette', '', 1, 0, 0, '', '', NULL, 0, 0, 0, NULL, '', ''); ('FAM', '750', '8', 'Langue de catalogage et langue de la racine de la vedette', '', 1, 0, 0, '', '', NULL, 0, 0, 0, NULL, '', ''); ('FAM', '750', 'a', 'Elément d''entrée', '', 0, 0, 0, '', '', NULL, 0, 0, 0, '', '', ''); ('FAM', '750', 'f', 'Dates', '', 0, 0, 0, '', '', NULL, 0, 0, 0, NULL, '', ''); ('FAM', '750', 'j', 'Subdivision de forme', '', 1, 0, 0, '', '', NULL, 0, 0, 0, NULL, '', ''); ('FAM', '750', 'x', 'Subdivision de sujet', '', 1, 0, 0, '', '', NULL, 0, 0, 0, NULL, '', ''); ('FAM', '750', 'y', 'Subdivision géographique', '', 1, 0, 0, '', '', NULL, 0, 0, 0, NULL, '', ''); ('FAM', '750', 'z', 'Subdivision chronologique', '', 1, 0, 0, '', '', NULL, 0, 0, 0, NULL, '', '');
(In reply to Fridolin SOMERS from comment #3) > (In reply to M. Tompsett from comment #2) > Do you think I should not have merged FAM and FA ? I have no issue with the merge. It seemed you lost data the 'j' when you did, and wanted to confirm that you really intended to change data when merging (i.e. FA values used over pre-existing FAM ones).
(In reply to M. Tompsett from comment #11) > I have no issue with the merge. It seemed you lost data the 'j' when you > did, -- and I should point out that I have no knowledge of UNIMARC. I was just trying to make sense of where data was coming from and where it went when comparing the old and new. For example, if the new version of UNIMARC says no 'j', but the old had one, that's great then.
(In reply to M. Tompsett from comment #11) > (In reply to Fridolin SOMERS from comment #3) > > (In reply to M. Tompsett from comment #2) > > Do you think I should not have merged FAM and FA ? > > I have no issue with the merge. It seemed you lost data the 'j' when you > did, and wanted to confirm that you really intended to change data when > merging (i.e. FA values used over pre-existing FAM ones). Yep that was a mistake. Thanks for testing. I've ask my collegues to read the comments.
(In reply to Fridolin SOMERS from comment #13) > Thanks for testing. > I've ask my collegues to read the comments. Tested the revised patch. This is purely the removal of: AUTTIT, SAUT, SCO and STU. This still has some constraint errors. Awaiting a follow-up for further testing. :) use kohadata; delete from auth_subfield_structure; delete from auth_tag_structure; delete from auth_types; source installer/data/mysql/fr-FR/marcflavour/unimarc_complet/Obligatoire/autorites_norme_unimarc.sql; -- Once you get "two" patches which handle the constraint issues and contain what you believe is correct. Could you add a third which turns all the single inserts into a multi-value insert, at least for the blocks (eg. FA, whatever else...)? Thanks for the efforts. :)
(In reply to M. Tompsett from comment #14) > > -- Once you get "two" patches which handle the constraint issues and contain > what you believe is correct. Could you add a third which turns all the > single inserts into a multi-value insert, at least for the blocks (eg. FA, > whatever else...)? Thanks for the efforts. :) You mean : INSERT INTO table (c1, c2, c3) VALUES (1, 'bla', 0), (2, 'bla', 1); Does it make a performance difference ?
(In reply to Fridolin SOMERS from comment #15) > Does it make a performance difference ? No, but it makes a vertical scroll difference when manually testing. Also, it helps keep everything the same. If a new field is introduced, it will force an update to keep consistency, rather than have some with no value and others with a value.
I have rebased after Bug 16554 was integrated
Created attachment 56505 [details] [review] Bug 16797 - errors in UNIMARC complet sample authorities The SQL UNIMARC complet sample authorities fails : installer/data/mysql/fr-FR/marcflavour/unimarc_complet/Obligatoire/autorites_norme_unimarc.sql Some lines of auth_subfield_structure use an authtypecode not existing in lines of authtypecode : AUTTIT, SAUT, SCO and STU. Test plan : - On a UNIMARC database : - clear auth tables : mysql> delete from auth_subfield_structure; mysql> delete from auth_tag_structure; mysql> delete from auth_types; - import sql file : mysql < installer/data/mysql/fr-FR/marcflavour/unimarc_complet/Obligatoire/autorites_norme_unimarc.sql => Without patch you get error : ERROR 1452 (23000) at line 1132: Cannot add or update a child row: a foreign key constraint fails (`koha`.`auth_subfield_structure`, CONSTRAINT `auth_subfield_structure_ibfk_1` FOREIGN KEY (`authtypecode`) REFERENCES `auth_types` (`authtypecode`) ON DELETE CASCADE ON UPDATE CASCADE) => With patch no errors : > select distinct(authtypecode) from auth_subfield_structure; +--------------+ | authtypecode | +--------------+ | | | CO | | CO_UNI_TI | | FAM | | GENRE/FORM | | NAME_COL | | NP | | PA | | SAUTTIT | | SNC | | SNG | | TM | | TU | +--------------+ 13 rows in set (0.00 sec) - Have a look at authoritie frameworks in Admin
Patch tested with a sandbox, by Laurence Rault <laurence.rault@biblibre.com>
Created attachment 65315 [details] [review] Bug 16797 - errors in UNIMARC complet sample authorities The SQL UNIMARC complet sample authorities fails : installer/data/mysql/fr-FR/marcflavour/unimarc_complet/Obligatoire/autorites_norme_unimarc.sql Some lines of auth_subfield_structure use an authtypecode not existing in lines of authtypecode : AUTTIT, SAUT, SCO and STU. Test plan : - On a UNIMARC database : - clear auth tables : mysql> delete from auth_subfield_structure; mysql> delete from auth_tag_structure; mysql> delete from auth_types; - import sql file : mysql < installer/data/mysql/fr-FR/marcflavour/unimarc_complet/Obligatoire/autorites_norme_unimarc.sql => Without patch you get error : ERROR 1452 (23000) at line 1132: Cannot add or update a child row: a foreign key constraint fails (`koha`.`auth_subfield_structure`, CONSTRAINT `auth_subfield_structure_ibfk_1` FOREIGN KEY (`authtypecode`) REFERENCES `auth_types` (`authtypecode`) ON DELETE CASCADE ON UPDATE CASCADE) => With patch no errors : > select distinct(authtypecode) from auth_subfield_structure; +--------------+ | authtypecode | +--------------+ | | | CO | | CO_UNI_TI | | FAM | | GENRE/FORM | | NAME_COL | | NP | | PA | | SAUTTIT | | SNC | | SNG | | TM | | TU | +--------------+ 13 rows in set (0.00 sec) - Have a look at authoritie frameworks in Admin Signed-off-by: Laurence Rault <laurence.rault@biblibre.com>
tested OK with the patch but seemed OK as well without : no sql error without the patch
Maybe a silly question but why do not you create the authtypecodez AUTTIT, SAUT, SCO and STU instead?
(In reply to Jonathan Druart from comment #22) > Maybe a silly question but why do not you create the authtypecodez AUTTIT, > SAUT, SCO and STU instead? Changing status to In Discussion to have an answer
(In reply to Jonathan Druart from comment #22) > Maybe a silly question but why do not you create the authtypecodez AUTTIT, > SAUT, SCO and STU instead? I bet those codes are obsolete. Those sample files must provide a small set of frameworks to start with. One can always create more specific frameworks.
(In reply to Fridolin SOMERS from comment #24) > (In reply to Jonathan Druart from comment #22) > > Maybe a silly question but why do not you create the authtypecodez AUTTIT, > > SAUT, SCO and STU instead? > > I bet those codes are obsolete. > Those sample files must provide a small set of frameworks to start with. > One can always create more specific frameworks. "I bet" is not convincing enough ;) I had the same question when glancing thru your patch. Please explain. When were these codes added, removed, etc. Did they forget to remove these lines ? Please do some git research to provide further evidence.
It looks like this has been resolved since, the .yml files for the UNIMARC installer no longer contain the removed authtypecodes.