Using MySQL reserved word as Field Name.

Development
1

rank is an SQL reserved word since MySQL 8.0.2: but we are using it as a field name in the Diagnosis class

Had the below SQL error while trying to create a Visit Note via the OWA; the whole exercise was part of testing my newly added Diagnosis Attribute/Type classes

mysql
mysql1256×836 135 KB
(NOT YET MERGED). See TRUNK-6038

Caused by: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; 
check the manual that corresponds to your MySQL server version 
for the right syntax to use near 'rank) 
values ('09aafee4-c8a1-4f17-a185-c9e2cda72363', 
null, 1, null, '2021-10-31' at line 1

mysql --version mysql Ver 8.0.26 for macos10.15 on x86_64 (Homebrew)

2

does the error clear when you try to use a different name around here than rank TRUNK-6038:Add Diagnosis Attribute to data model by miirochristopher · Pull Request #3896 · openmrs/openmrs-core · GitHub

3

@herbert24, the field named rank was already in the Diagnosis class prior to the changes made to make the Diagnoses Attributable. It may be just a variable but because of usage in other classes and possibly clients, I was hesitant to just rename it.

Renaming rank would imply renaming the associated setters and getters which more certainly than not will be problematic for the Diagnosis clients.

rank
rank1280×800 187 KB

I also overloaded and refactored the constructor after this comment from @dkayiwa

	public Diagnosis(Encounter encounter, CodedOrFreeText diagnosis, ConditionVerificationStatus certainty, Integer rank, Patient patient, String formNamespaceAndPath) {
		this.encounter = encounter;
		this.diagnosis = diagnosis;
		this.certainty = certainty;
		this.rank = rank;
		this.patient = patient;
		this.formNamespaceAndPath = formNamespaceAndPath;
	}
1 Like
4

Rank is what FHIR uses for ranking encounter diagnoses. Perhaps the db column could be renamed to dx_rank to avoid the reserved word.

2 Likes
5

thanks burke

6

Shouldn’t the fieldnames be quoted in the query? Using backpacks as quotes around fieldnames removes the risk of a fieldname becoming a reserved word in the future.

I do not know how OpenMRS is building and executing queries, but this is how the Python Django framework handles this issue.

7

Thanks @burke that should be the most viable and safest solution since we use Liquibase for schema evolution @tansentim OpenMRS core uses Hibernate for mapping Java classes to database tables and Java data types to SQL data types. (The equivalent of SQL Alchemy in the python world). Handling this at query level may require a lot of trivial changes.

8

This has been fixed, see [TRUNK-6044] Avoid using MySQL reserved word "rank" as Column Name - OpenMRS Issues

1 Like