Invalid handling of collCode, collNumb
Genesys partners sometimes submit collCode
as an MCPD array string COOD003;BEN089
instead of ["COOD003", "BEN089"]
array. Same for collName
.
In API v0 please add to upgradeToV2
method some logic that will ensure these are converted to String[]
.
Update liquibase-changeLog.yml
with SQL that converts current incorrect data for collCode
(e.g. COOD003;BEN089
) to two records.
Not sure how valid this SQL is, but it seems to work
- sql:
drop table if exists t_collCode;
create table if not exists t_collCode as
select
@num_e := 1 + LENGTH(collCode) - LENGTH(REPLACE(collCode, ';', '')) AS num_e,
collectId, collCode,
SUBSTRING_INDEX(collCode,';',1) as coll1,
IF(@num_e > 1, SUBSTRING_INDEX(SUBSTRING_INDEX(collCode,';',2),';',-1), null) coll2,
IF(@num_e > 2, SUBSTRING_INDEX(SUBSTRING_INDEX(collCode,';',3),';',-1), null) coll3,
IF(@num_e > 3, SUBSTRING_INDEX(SUBSTRING_INDEX(collCode,';',3),';',-1), null) coll4
from accession_collect_code where collCode is not null and (LENGTH(collCode) - LENGTH(REPLACE(collCode, ';', ''))) > 0;
delete from accession_collect_code where collectId in (select distinct collectId from t_collCode);
insert into accession_collect_code (collectId,collCode)
(select distinct collectId, coll1 from
(select collectId, coll1 from t_collCode where coll1 is not null
union all select collectId, coll2 from t_collCode where coll2 is not null
union all select collectId, coll3 from t_collCode where coll3 is not null
union all select collectId, coll4 from t_collCode where coll4 is not null) Q);
drop table t_collCode;
Apparently we don't have any such problems in accession_collect_name
table.