Fix import of main ISIS and CLF questionnaires into dev

[simonfernandes]

We need to be able to export questionnaire templates on prod and import them into dev to allow us to test.

We're not currently able to do so because of errors, possibly related to the imported new questions conflicting with the existing ones. The errors are too long to post here, but to replicate export the ISIS Direct questionnaire from prod and import it into dev.

The main questionnaires to import are probably:

• ISIS Direct
• ISIS Rapid
• LSF Direct
• LSF Programme
• HPL
• Artemis

[mutambaraf]

From my opinion looking at the logs we can just prompt the user to either accept or decline the changes from the imported questionnaire. If possible to also just list the conflicting questions_natural_key_key(s). { "message": "insert into \"questions\" (\"category_id\", \"data_type\", \"default_config\", \"natural_key\", \"question\", \"question_id\") values ($1, $2, $3, $4, $5, $6) on conflict (\"question_id\") do update set \"category_id\" = excluded.\"category_id\", \"data_type\" = excluded.\"data_type\", \"default_config\" = excluded.\"default_config\", \"natural_key\" = excluded.\"natural_key\", \"question\" = excluded.\"question\", \"question_id\" = excluded.\"question_id\" returning * - duplicate key value violates unique constraint \"questions_natural_key_key\"", "error": { "length": 240, "name": "error", "severity": "ERROR", "code": "23505", "detail": "Key (natural_key)=(publication_author) already exists.", "schema": "public", "table": "questions", "constraint": "questions_natural_key_key", "file": "nbtinsert.c", "line": "670", "routine": "_bt_check_unique" }, "obj": { "__knexUid": "__knexUid58", "__knexTxId": "__knexUid58", "method": "insert", "options": {}, "timeout": false, "cancelOnTimeout": false, "bindings": [7, "TEXT_INPUT", { "required": false, "small_label": "", "tooltip": "", "htmlQuestion": "", "isHtmlQuestion": false, "min": null, "max": null, "multiline": false, "placeholder": "", "isCounterHidden": true }, "publication_author", "Additional authors", "text_input_1654783089371"], "__knexQueryUid": "dLhoMNHSEEd0bFQSaORM4", "sql": "insert into \"questions\" (\"category_id\", \"data_type\", \"default_config\", \"natural_key\", \"question\", \"question_id\") values ($1, $2, $3, $4, $5, $6) on conflict (\"question_id\") do update set \"category_id\" = excluded.\"category_id\", \"data_type\" = excluded.\"data_type\", \"default_config\" = excluded.\"default_config\", \"natural_key\" = excluded.\"natural_key\", \"question\" = excluded.\"question\", \"question_id\" = excluded.\"question_id\" returning *", "returning": ["*"] }, "QueryName": "insert into \"questions\" (\"category_id\", \"data_type\", \"default_config\", \"natural_key\", \"question\", \"question_id\") values ($1, $2, $3, $4, $5, $6) on conflict (\"question_id\") do update set \"category_id\" = excluded.\"category_id\", \"data_type\" = excluded.\"data_type\", \"default_config\" = excluded.\"default_config\", \"natural_key\" = excluded.\"natural_key\", \"question\" = excluded.\"question\", \"question_id\" = excluded.\"question_id\" returning *" }

[deepaksftc]

@mutambaraf, just to confirm, we need to display a message like below, with option to overwrite and ignore these questions right? "Below mentioned questions already exists in records. Do you wish to overwrite/ignore these questions? publication_author publication_doi publication_year Button to overwrite Button to ignore"

[mutambaraf]

@mutambaraf, just to confirm, we need to display a message like below, with option to overwrite and ignore these questions right? "Below mentioned questions already exists in records. Do you wish to overwrite/ignore these questions? publication_author publication_doi publication_year Button to overwrite Button to ignore"

Yes correct that is my thought.

[simonfernandes]

A button to overwrite seems good. What would the button to ignore do? It seems that if we try to import and choose "existing question" for all conflicts it will still fail, is that the same as ignoring?

I think we need to also ensure these changes work with importing a sub template. as some of the questions imported have a reference to a sub template ID in them

[deepaksftc]

A button to overwrite seems good. What would the button to ignore do? It seems that if we try to import and choose "existing question" for all conflicts it will still fail, is that the same as ignoring?

I think we need to also ensure these changes work with importing a sub template. as some of the questions imported have a reference to a sub template ID in them

By ignore, what I understand is, not to save those particular questions and reusing what ever already there in db for the new template imported. Just for the questions that has duplicate keys in db. Regarding whether the sub templates import getting effected, I am not sure. I need to try and verify that.

[simonfernandes]

By ignore, what I understand is, not to save those particular questions and reusing what ever already there in db for the new template imported.

Would this be the equivalent of selecting the existing question in the conflict picker or would it differ in some way?

[deepaksftc]

By ignore, what I understand is, not to save those particular questions and reusing what ever already there in db for the new template imported.

Would this be the equivalent of selecting the existing question in the conflict picker or would it differ in some way?

I tried importing a template, selecting the existing question in conflict picker. It is still throwing same error.

[deepaksftc]

@mutambaraf @simonfernandes, While testing the approach to allow user-office to accept/decline questions having duplicate natural_key issues, I could find some challenges. question_id being primary key and natural_key being unique key, unless if the combination of both are same, we won't be able to overwrite any record, as question_id is also used as foriegn key (have associated values in other tables) and natural_key need to be unique. Also editing the questions may impact the other templates already using the existing questions right. I could think of some suggestions. Please share your thoughts.

1. Remove unique key constraint on natural key column.
2. Make the error message more informative by providing the list of question_ids which has duplicate natural_keys in db in alert. Then the user-officer have to edit the template manually for these question_ids and try import again.
3. If this error occurs, concat the natural_key(of new template) value with a fixed constant and insert/update again in code.

[mutambaraf]

I would prefer option three .Manual editing will be another task which l think is not necessary. I will also prefer to keep constrains on the database natural key.