Bug 12478: Fix the UNIMARC and NORMARC indexing

1/ This patch fixes a major issue on UNIMARC and NORMARC installations
(Compulsive c/p on insert into insert into search_marc_to_field for unimarc and normarc)

2/ After fixing that, I got
invalid marc path at /usr/share/perl5/Catmandu/Fix/marc_map.pm line 35.
on reindexing.
It was caused by some "" values instead of NULL.

3/ Then, a "duplicate entry" error was caused by 3 lines for Local-number.
It seems to be an error, 1 is enough (I suppose 0909 was an error too).

Note that the following is not fixed by this patch:
MariaDB [koha_es_unimarc]>  insert into search_field (name, type) select distinct mapping, type from elasticsearch_mapping;
Query OK, 73 rows affected, 57 warnings (0.05 sec)
Records: 73  Duplicates: 0  Warnings: 57

MariaDB [koha_es_unimarc]> show warnings;
+---------+------+--------------------------------------------+
| Level   | Code | Message                                    |
+---------+------+--------------------------------------------+
| Warning | 1265 | Data truncated for column 'type' at row 1  |

Signed-off-by: Nick Clemens <nick@bywatersolutions.com>
Signed-off-by: Jesse Weaver <jweaver@bywatersolutions.com>
Signed-off-by: Tomas Cohen Arazi <tomascohen@theke.io>

Signed-off-by: Kyle M Hall <kyle@bywatersolutions.com>

diff --git a/installer/data/mysql/elasticsearch_mapping.sql b/installer/data/mysql/elasticsearch_mapping.sql
index b6a7e41..25f0d3c 100644 (file)
--- a/installer/data/mysql/elasticsearch_mapping.sql
+++ b/installer/data/mysql/elasticsearch_mapping.sql
@@ -67,17 +67,16 @@ INSERT INTO `elasticsearch_mapping` (`indexname`, `mapping`, `facet`, `suggestib
 INSERT INTO `elasticsearch_mapping` (`indexname`, `mapping`, `facet`, `suggestible`, `type`, `marc21`, `unimarc`, `normarc`) VALUES ('biblios','rtype',FALSE,FALSE,'','leader_/6',NULL,'leader_/6');
 INSERT INTO `elasticsearch_mapping` (`indexname`, `mapping`, `facet`, `suggestible`, `type`, `marc21`, `unimarc`, `normarc`) VALUES ('biblios','bib-level',FALSE,FALSE,'','leader_/7',NULL,'leader_/7');
 INSERT INTO `elasticsearch_mapping` (`indexname`, `mapping`, `facet`, `suggestible`, `type`, `marc21`, `unimarc`, `normarc`) VALUES ('biblios','control-number',FALSE,FALSE,'','001',NULL,'001');
-INSERT INTO `elasticsearch_mapping` (`indexname`, `mapping`, `facet`, `suggestible`, `type`, `marc21`, `unimarc`, `normarc`) VALUES ('biblios','Local-number',FALSE,FALSE,'string',NULL,'001',NULL);
 INSERT INTO `elasticsearch_mapping` (`indexname`, `mapping`, `facet`, `suggestible`, `type`, `marc21`, `unimarc`, `normarc`) VALUES ('biblios','date-time-last-modified',FALSE,FALSE,'','005','099d',NULL);
 INSERT INTO `elasticsearch_mapping` (`indexname`, `mapping`, `facet`, `suggestible`, `type`, `marc21`, `unimarc`, `normarc`) VALUES ('biblios','microform-generation',FALSE,FALSE,'','007_/11',NULL,'007_/11');
-INSERT INTO `elasticsearch_mapping` (`indexname`, `mapping`, `facet`, `suggestible`, `type`, `marc21`, `unimarc`, `normarc`) VALUES ('biblios','material-type',FALSE,FALSE,'','007','','007');
+INSERT INTO `elasticsearch_mapping` (`indexname`, `mapping`, `facet`, `suggestible`, `type`, `marc21`, `unimarc`, `normarc`) VALUES ('biblios','material-type',FALSE,FALSE,'','007',NULL,'007');
 INSERT INTO `elasticsearch_mapping` (`indexname`, `mapping`, `facet`, `suggestible`, `type`, `marc21`, `unimarc`, `normarc`) VALUES ('biblios','ff7-00',FALSE,FALSE,'','007_/1',NULL,'007_/1');
 INSERT INTO `elasticsearch_mapping` (`indexname`, `mapping`, `facet`, `suggestible`, `type`, `marc21`, `unimarc`, `normarc`) VALUES ('biblios','ff7-01',FALSE,FALSE,'','007_/2',NULL,'007_/2');
 INSERT INTO `elasticsearch_mapping` (`indexname`, `mapping`, `facet`, `suggestible`, `type`, `marc21`, `unimarc`, `normarc`) VALUES ('biblios','ff7-02',FALSE,FALSE,'','007_/3',NULL,'007_/3');
 INSERT INTO `elasticsearch_mapping` (`indexname`, `mapping`, `facet`, `suggestible`, `type`, `marc21`, `unimarc`, `normarc`) VALUES ('biblios','ff7-01-02',FALSE,FALSE,'','007_/1-2',NULL,'007_/1-2');
 INSERT INTO `elasticsearch_mapping` (`indexname`, `mapping`, `facet`, `suggestible`, `type`, `marc21`, `unimarc`, `normarc`) VALUES ('biblios','date-entered-on-file',FALSE,FALSE,'','008_/1-5','099c','008_/1-5');
 INSERT INTO `elasticsearch_mapping` (`indexname`, `mapping`, `facet`, `suggestible`, `type`, `marc21`, `unimarc`, `normarc`) VALUES ('biblios','pubdate',FALSE,FALSE,'','008_/7-10','100a_/9-12','008_/7-10');
-INSERT INTO `elasticsearch_mapping` (`indexname`, `mapping`, `facet`, `suggestible`, `type`, `marc21`, `unimarc`, `normarc`) VALUES ('biblios','pl',FALSE,FALSE,'','008_/15-17','','008_/15-17');
+INSERT INTO `elasticsearch_mapping` (`indexname`, `mapping`, `facet`, `suggestible`, `type`, `marc21`, `unimarc`, `normarc`) VALUES ('biblios','pl',FALSE,FALSE,'','008_/15-17',NULL,'008_/15-17');
 INSERT INTO `elasticsearch_mapping` (`indexname`, `mapping`, `facet`, `suggestible`, `type`, `marc21`, `unimarc`, `normarc`) VALUES ('biblios','ta',FALSE,FALSE,'','008_/22','100a_/17','008_/22');
 INSERT INTO `elasticsearch_mapping` (`indexname`, `mapping`, `facet`, `suggestible`, `type`, `marc21`, `unimarc`, `normarc`) VALUES ('biblios','ff8-23',FALSE,FALSE,'','008_/23',NULL,'008_/23');
 INSERT INTO `elasticsearch_mapping` (`indexname`, `mapping`, `facet`, `suggestible`, `type`, `marc21`, `unimarc`, `normarc`) VALUES ('biblios','ff8-29',FALSE,FALSE,'','008_/29','105a_/8','008_/29');
@@ -85,7 +84,7 @@ INSERT INTO `elasticsearch_mapping` (`indexname`, `mapping`, `facet`, `suggestib
 INSERT INTO `elasticsearch_mapping` (`indexname`, `mapping`, `facet`, `suggestible`, `type`, `marc21`, `unimarc`, `normarc`) VALUES ('biblios','bio',FALSE,FALSE,'','008_/34','105a_/12','008_/34');
 INSERT INTO `elasticsearch_mapping` (`indexname`, `mapping`, `facet`, `suggestible`, `type`, `marc21`, `unimarc`, `normarc`) VALUES ('biblios','ln',FALSE,FALSE,'','008_/35-37','101a','008_/35-37');
 INSERT INTO `elasticsearch_mapping` (`indexname`, `mapping`, `facet`, `suggestible`, `type`, `marc21`, `unimarc`, `normarc`) VALUES ('biblios','ctype',FALSE,FALSE,'','008_/24-27','105a_/4-7','008_/24-27');
-INSERT INTO `elasticsearch_mapping` (`indexname`, `mapping`, `facet`, `suggestible`, `type`, `marc21`, `unimarc`, `normarc`) VALUES ('biblios','record-source',FALSE,FALSE,'','008_/39','','008_/39');
+INSERT INTO `elasticsearch_mapping` (`indexname`, `mapping`, `facet`, `suggestible`, `type`, `marc21`, `unimarc`, `normarc`) VALUES ('biblios','record-source',FALSE,FALSE,'','008_/39',NULL,'008_/39');
 INSERT INTO `elasticsearch_mapping` (`indexname`, `mapping`, `facet`, `suggestible`, `type`, `marc21`, `unimarc`, `normarc`) VALUES ('biblios','lc-cardnumber',FALSE,FALSE,'','010','995j','010');
 INSERT INTO `elasticsearch_mapping` (`indexname`, `mapping`, `facet`, `suggestible`, `type`, `marc21`, `unimarc`, `normarc`) VALUES ('biblios','lc-cardnumber',FALSE,FALSE,'','011',NULL,NULL);
 INSERT INTO `elasticsearch_mapping` (`indexname`, `mapping`, `facet`, `suggestible`, `type`, `marc21`, `unimarc`, `normarc`) VALUES ('biblios','identifier-standard',FALSE,FALSE,'','010',NULL,'010');
@@ -185,7 +184,6 @@ INSERT INTO `elasticsearch_mapping` (`indexname`, `mapping`, `facet`, `suggestib
 INSERT INTO `elasticsearch_mapping` (`indexname`, `mapping`, `facet`, `suggestible`, `type`, `marc21`, `unimarc`, `normarc`) VALUES ('biblios','local-classification',FALSE,TRUE,'','952o','995k','952o');
 INSERT INTO `elasticsearch_mapping` (`indexname`, `mapping`, `facet`, `suggestible`, `type`, `marc21`, `unimarc`, `normarc`) VALUES ('biblios','local-classification',FALSE,FALSE,'',NULL,'686',NULL);
 INSERT INTO `elasticsearch_mapping` (`indexname`, `mapping`, `facet`, `suggestible`, `type`, `marc21`, `unimarc`, `normarc`) VALUES ('biblios','Local-number',FALSE,FALSE,'string','999c','001','999c');
-INSERT INTO `elasticsearch_mapping` (`indexname`, `mapping`, `facet`, `suggestible`, `type`, `marc21`, `unimarc`, `normarc`) VALUES ('biblios','Local-number',FALSE,FALSE,'string',NULL,'0909',NULL);
 INSERT INTO `elasticsearch_mapping` (`indexname`, `mapping`, `facet`, `suggestible`, `type`, `marc21`, `unimarc`, `normarc`) VALUES ('biblios','itype',TRUE,FALSE,'string','942c','200b','942c');
 INSERT INTO `elasticsearch_mapping` (`indexname`, `mapping`, `facet`, `suggestible`, `type`, `marc21`, `unimarc`, `normarc`) VALUES ('biblios','itype',TRUE,FALSE,'string','952y','995r','952y');
 INSERT INTO `elasticsearch_mapping` (`indexname`, `mapping`, `facet`, `suggestible`, `type`, `marc21`, `unimarc`, `normarc`) VALUES ('biblios','acqdate',FALSE,FALSE,'date','952d','9955','952d');
@@ -296,11 +294,11 @@ insert into search_marc_map(index_name, marc_type, marc_field, facet, suggestibl
 insert into search_field (name, type) select distinct mapping, type from elasticsearch_mapping;
 
 insert into search_marc_to_field(search_marc_map_id, search_field_id) select search_marc_map.id,search_field.id from search_field, search_marc_map, elasticsearch_mapping where elasticsearch_mapping.mapping=search_field.name AND elasticsearch_mapping.marc21=search_marc_map.marc_field AND search_marc_map.marc_type='marc21' AND indexname='biblios' AND index_name='biblios';
-insert into search_marc_to_field(search_marc_map_id, search_field_id) select search_marc_map.id,search_field.id from search_field, search_marc_map, elasticsearch_mapping where elasticsearch_mapping.mapping=search_field.name AND elasticsearch_mapping.marc21=search_marc_map.marc_field AND search_marc_map.marc_type='unimarc' AND indexname='biblios' AND index_name='biblios';
-insert into search_marc_to_field(search_marc_map_id, search_field_id) select search_marc_map.id,search_field.id from search_field, search_marc_map, elasticsearch_mapping where elasticsearch_mapping.mapping=search_field.name AND elasticsearch_mapping.marc21=search_marc_map.marc_field AND search_marc_map.marc_type='normarc' AND indexname='biblios' AND index_name='biblios';
+insert into search_marc_to_field(search_marc_map_id, search_field_id) select search_marc_map.id,search_field.id from search_field, search_marc_map, elasticsearch_mapping where elasticsearch_mapping.mapping=search_field.name AND elasticsearch_mapping.unimarc=search_marc_map.marc_field AND search_marc_map.marc_type='unimarc' AND indexname='biblios' AND index_name='biblios';
+insert into search_marc_to_field(search_marc_map_id, search_field_id) select search_marc_map.id,search_field.id from search_field, search_marc_map, elasticsearch_mapping where elasticsearch_mapping.mapping=search_field.name AND elasticsearch_mapping.normarc=search_marc_map.marc_field AND search_marc_map.marc_type='normarc' AND indexname='biblios' AND index_name='biblios';
 
 insert into search_marc_to_field(search_marc_map_id, search_field_id) select search_marc_map.id,search_field.id from search_field, search_marc_map, elasticsearch_mapping where elasticsearch_mapping.mapping=search_field.name AND elasticsearch_mapping.marc21=search_marc_map.marc_field AND search_marc_map.marc_type='marc21' AND indexname='authorities' AND index_name='authorities';
-insert into search_marc_to_field(search_marc_map_id, search_field_id) select search_marc_map.id,search_field.id from search_field, search_marc_map, elasticsearch_mapping where elasticsearch_mapping.mapping=search_field.name AND elasticsearch_mapping.marc21=search_marc_map.marc_field AND search_marc_map.marc_type='unimarc' AND indexname='authorities' AND index_name='authorities';
-insert into search_marc_to_field(search_marc_map_id, search_field_id) select search_marc_map.id,search_field.id from search_field, search_marc_map, elasticsearch_mapping where elasticsearch_mapping.mapping=search_field.name AND elasticsearch_mapping.marc21=search_marc_map.marc_field AND search_marc_map.marc_type='normarc' AND indexname='authorities' AND index_name='authorities';
+insert into search_marc_to_field(search_marc_map_id, search_field_id) select search_marc_map.id,search_field.id from search_field, search_marc_map, elasticsearch_mapping where elasticsearch_mapping.mapping=search_field.name AND elasticsearch_mapping.unimarc=search_marc_map.marc_field AND search_marc_map.marc_type='unimarc' AND indexname='authorities' AND index_name='authorities';
+insert into search_marc_to_field(search_marc_map_id, search_field_id) select search_marc_map.id,search_field.id from search_field, search_marc_map, elasticsearch_mapping where elasticsearch_mapping.mapping=search_field.name AND elasticsearch_mapping.normarc=search_marc_map.marc_field AND search_marc_map.marc_type='normarc' AND indexname='authorities' AND index_name='authorities';
 
 drop table elasticsearch_mapping;