Can't Update Some Fields Due To case_insensitive Validation

[aaronskiba]

Please complete the following fields as applicable:

What version of the DMPRoadmap code are you running? (e.g. v2.2.0)

• v4.0.2 Upgrade
• (NOT an issue with current production version, 3.3.1+portage-3.2.2)

Example

• Within production, there exists some Orgs, with the same name, but in different casings:
• 
• However, the v4 upgrade sets case_sensitive: false, which prevents us from editing either of these orgs, unless we rename them.
• 

[aaronskiba]

@lagoan, how should we address this? Also, we need to check if case_sensitive: false has been added as a validation check for any other fields.

[lagoan]

@aaronskiba, we should check the reason for this new validation. We may not need it or want it since we already have values that have this conflict.

[aaronskiba]

The case insensitive uniqueness validation constraint has been added for the following (relevant commit https://github.com/portagenetwork/roadmap/commit/d54c68a7cf4c65dad42b2ef67242210667741790):

Department.name
Org.name
Perm.name
QuestionFormat.title
Region.name

Returned results after executing the following SQL query on a prod db dump from Jan 8, 2024:

SELECT column
FROM table
WHERE LOWER(column) IN (
    SELECT LOWER(column)
    FROM table
    GROUP BY LOWER(column)
    HAVING COUNT(*) > 1
)
GROUP BY column
ORDER BY column;

Org.name

Canadian Institutes of health Research
Canadian Institutes of Health Research
cihr
CIHR
Conseil de recherche en sciences humaines du Canada
Conseil de Recherche en Sciences Humaines du Canada
crsng
CRSNG
Environment and Climate Change Canada
Environment And Climate Change Canada
Food from Thought
Food From Thought
Ministère de la Sécurité publique
Ministère de la Sécurité Publique
Mitacs
MiTacs
MITACs
MITACS
n/a
N/a
N/A
National Cybersecurity consortium
National Cybersecurity Consortium
none
None
no one :'(
No one :'(
Not applicable
Not Applicable
nserc
NSERC
Ontario Agri-food Alliance
Ontario Agri-Food Alliance
ontario AgriFood Innovation Alliance
Ontario Agri-food Innovation Alliance
Ontario Agrifood Innovation Alliance
Ontario Agri-Food Innovation Alliance
Ontario AgriFood Innovation Alliance
Portage network
Portage Network
Social Sciences and Humanities Research Council
Social Sciences And Humanities Research Council
sshrc
SSHRC
test
Test
unfunded
Unfunded

(0 rows were returned for all of the others. Department.name includes some duplicates. However, the validation check specifies scope: :org_id, case_sensitive: false and none of these duplicates share the same org_id )

[aaronskiba]

Setting case_sensitive: true on the Org.name uniqueness validation check would solve this. Another potential solution is to set the validation as case_sensitive: false, if: :managed.

Of all the duplicate orgs listed above, Portage Network is the only managed one. If we deleted Portage network (lowercase 'n'), that would enable the validation to be set this way.

The Organisation dropdown in the Create Account section only lists managed Orgs:

presenter = OrgSelectionPresenter.new(orgs: orgs.select{|org| org.managed == true}, selection: default_org)