Add unique constraint on FormMapping table

mahalakshme commented 1 year ago

Acceptance criteria:

On FormMapping table, add unique constraint around (subject_type_id, observations_type_entity_id, entity_id, form_id ,organisation_id, is_voided and task_type_id)
For fixing the existing data, we need to partition and void the duplicates to prevent them for causing issues and debugging time,

WHY?

Looks like sometimes same form mapping are adding both in UAT org and corresponding prod org. Hence when bundle upload is done, this results in duplicate form mappings with different uuids. This in turn results in unexpected behavior leading to cards like https://github.com/avniproject/avni-client/issues/1134 and larger turn-around time.

Out of scope:

Already handled in webapp when tried to add duplicate form mappings via UI
Bundle upload already handles violoation of unique constraints

mohan-13 commented 1 year ago

We have also faced this sometimes in our environments. As a temporary workaround we have to set the duplicate mappings as voided in the form_mappings table.

vinayvenu commented 1 year ago

How do you handle duplicates?

select sum(cnt) from
(select subject_type_id,
       observations_type_entity_id,
       entity_id,
       form_id,
       organisation_id,
       is_voided,
       task_type_id,
       count(*) cnt
from form_mapping
group by subject_type_id, observations_type_entity_id, entity_id, form_id, organisation_id, is_voided, task_type_id
having count(*) > 1) a;
-- 21082

mahalakshme commented 1 year ago

We need to partition and void the duplicates to prevent them for causing issues and debugging time, will add in the AC

petmongrels commented 1 year ago

Query to view the the duplicate rows that you will have to delete or fix manually first depending on the organisation

select o.name, fm.subject_type_id, fm.entity_id, fm.observations_type_entity_id, fm.task_type_id, f.form_type, count(*) from form_mapping fm join form f on fm.form_id = f.id join organisation o on f.organisation_id = o.id where fm.is_voided = false group by o.name, fm.subject_type_id, fm.entity_id, fm.observations_type_entity_id, fm.task_type_id, f.form_type having count(*) > 1 order by 1;

once you have fixed the duplicates for prod organisations, rest can be deleted using below

delete from form_mapping where id in ( select fm.id from form_mapping fm inner join form f on fm.form_id = f.id inner join organisation o on f.organisation_id = o.id inner join (select o.name, fm.subject_type_id, fm.entity_id, fm.observations_type_entity_id, fm.task_type_id, f.form_type, count(*) from form_mapping fm join form f on fm.form_id = f.id join organisation o on f.organisation_id = o.id where fm.is_voided = false group by o.name, fm.subject_type_id, fm.entity_id, fm.observations_type_entity_id, fm.task_type_id, f.form_type having count(*) > 1 order by 1) x on x.name = o.name and x.subject_type_id = fm.subject_type_id and (x.entity_id = fm.entity_id or (x.entity_id is null and fm.entity_id is null)) and (x.observations_type_entity_id = fm.observations_type_entity_id or (x.observations_type_entity_id is null or fm.observations_type_entity_id is null)) and (x.task_type_id = fm.task_type_id or (x.task_type_id is null and fm.task_type_id is null)) and x.form_type = f.form_type and fm.is_voided = false);

ashusvnath commented 1 year ago

@petmongrels : The SQL's need to be updated on the release card https://github.com/avniproject/avni-product/issues/1411

petmongrels commented 1 year ago

noticed error during bundle import in form mapping entries

mahalakshme commented 1 year ago

@petmongrels On deploying to prerelease, the below migration failed:

2023-10-30 08:39:11.281 INFO 31856 --- [main] o.f.c.internal.dbsupport.JdbcTemplate : DB: setting value of audit to 12574635 2023-10-30 08:39:11.281 INFO 31856 --- [main] o.f.c.internal.dbsupport.JdbcTemplate : DB: setting value of audit to 12574636 2023-10-30 08:39:11.376 INFO 31856 --- [main] o.f.core.internal.command.DbMigrate : Migrating schema "public" to version 1.316 - FormMappingDuplicationCheckChanges1 2023-10-30 08:39:31.632 INFO 31856 --- [main] o.f.core.internal.command.DbMigrate : Migrating schema "public" to version 1.317 - FormMappingDuplicationCheckChanges2 2023-10-30 08:39:32.545 ERROR 31856 --- [main] o.f.core.internal.command.DbMigrate : Migration of schema "public" to version 1.317 - FormMappingDuplicationCheckChanges2 failed! Changes successfully rolled back. 2023-10-30 08:39:32.554 INFO 31856 --- [main] o.apache.catalina.core.StandardService : Stopping service [Tomcat]

vinayvenu commented 1 year ago

@mahalakshme see release card - https://github.com/avniproject/avni-product/issues/1411 or the fix required here - https://github.com/avniproject/avni-server/issues/628#issuecomment-1772547300 .

avniproject / avni-server