Queries to fix duplicate privileges about avni-server HOT 12 CLOSED

• 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

from avni-server.

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

from avni-server.

@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 ~

from avni-server.

templateExpressionToGenerateUpdateScript.txt

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

from avni-server.

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.

from avni-server.

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.

from avni-server.

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

from avni-server.

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

from avni-server.

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)

from avni-server.

@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.

from avni-server.

Migration script available here

from avni-server.

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

from avni-server.