Queries to fix duplicate privileges

[mahalakshme]

https://avni.freshdesk.com/a/tickets/4053

AC:

• For Mobile Creches and Shelter here - encounter type, not included(include it). Update last_modified_by_user_id to your super_admin. And review other queries if they are fine.
• Across all orgs, wherever there is duplicate group_privilege which are not voided and have same values for "allow",
  • make an update to void all except one,
  • for each of the entries to be voided, update the last_modified_date and last_modified_by_id and set "allow" to false
• Add query to void all the duplicates and also set the "allow" field to "False" always for all voided groupPrivilges [ Note: Change as per details specified here]
• Add query to update the impl_version of all voided(independent of allow is true or false) to 0 and non-voided to 1

[himeshr]

• Add query to update the version of all voided(independent of allow is true or false) to 0 and non-voided to 1

@mahalakshme Will not do this part as per discussion in Avni product group

[mahalakshme]

@himeshr no Joy mentioned so, because of AC for his card. It will all work together correctly

[himeshr]

@himeshr no Joy mentioned so, because of AC for his card. It will all work together correctly

As per discussion, will update impl_version instead of earlier mentioned version column in group_privilege table.

~Depends on #721 ~

[himeshr]

templateExpressionToGenerateUpdateScript.txt

Used this to auto-generate the sql script for all org-sheets mentioned in this card's description. update script sheet

[1t5j0y]

the manually run script (created via this card) should only update is_voided (and get the db to a valid state where we can apply the constraint). Updating impl_version also here creates a circular dependency.

the migration (in #721) will introduce the new impl_version column, mark all voided rows with impl_version 0 and add the constraint.

[himeshr]

the manually run script (created via this card) should only update is_voided (and get the db to a valid state where we can apply the constraint). Updating impl_version also here creates a circular dependency.

the migration (in #721) will introduce the new impl_version column, mark all voided rows with impl_version 0 and add the constraint.

Updated the script to not set the impl_version, and just retain 1 non-voided entry as part of the update script.

[himeshr]

Commands to find duplicate ids.txt
WIP: query to void all duplicates after setting allow to false

[himeshr]

Local testing on pre-release for "Mobile Creches" org was successful, with following sql commands executed and output recieved.
sqlOutputMobileCrechesRemovalOfDuplicatePrivileges.txt
mobileCrechesRemovalOfDuplicatePrivileges.txt

[petmongrels]

For QA

• the scripts will need to be run on production
• testing and verification should be done in prerelease environment. When verifying we should verify whether the privileges for users are same as they are in production. This is required because the privileges will get updated when the deployment is done for this.
• It would be good to refresh pre-release before testing this (we should make sure that the downtime doesn't block other people)

[mahalakshme]

@himeshr

• check this comment
• I have updated the privileges with discrepancies for all active organisations here in the sheet: 'For all active orgs with discrepancies'.
• The privileges with discrepancies have changed. So I have used the same formula in the above sheet.
• In the 'Active orgs' sheet have added the final list of active orgs that have discrepancies.

[himeshr]

Migration script available here

[himeshr]

Executed the group privileges updation script in production.
ProdSqlsLogs.txt
ProdSqlsExecuted.txt