Explicit unicode column names are converted to lowercase when using postgresql

[majkinetor]

Bug Description

Serbian cyrilic: Опис is converted to опис.

select description as "Опис"
from forms f

Serbian latin:

select description as "Šuma"
from forms f

Severity

• Low - I'm able to keep using Evidence as normal, but flagging this for the team to fix

Expected Behavior

Workarounds
There doesn't seem to be workaround

Environment Information
Operating System: Windows 10
Node version (node -v): 19.1.0
npm version (npm -v): 8.9.13
Package versions (npm list --depth=0): `@evidence-dev/evidence@5.0.12

Database:

• Postgres

[archiewood]

Hi @majkinetor,
Confirm this does not happen with other connectors (eg SQLite).

I think the specific piece of code here is this line

@hughess Is there a specific reason we lowercase the keys like this?

[hughess]

@archiewood I actually used the same function for the postgres connector as we already had for the snowflake connector - I believe there was a reason to do that for snowflake but I'm not sure if postgres has the same requirement.

@mcrascal was there a reason for lowercasing the snowflake column names in the standardizeResult function?

If there's no specific reason for postgres, we should be good to remove that line

[hughess]

2 other ways we can solve this issue:

1. Change the function that formats column titles to correctly handle cyrillic input (I think this might be difficult to accomplish)
2. Allow an override for column titles in viz components - this exists in charts now (can override xAxisTitle and yAxisTitle), but doesn't exist yet for tables. It's something we're including in the new tables we're building, so that should be an option to use soon.
   • E.g., you could do something like <Column id=опис title="Опис"/>

[mcrascal]

As far as I recall, the Snowflake API returns uppercased column names, regardless of how you write your query. It was quite annoying to need to uppercase everything when referencing columns in components. Just a preference thing -- either we get all uppercase, or all lowercase.

[hughess]

Ok perfect, thanks.

I've just confirmed the behaviour of the postgres db package:

• Column names passed using double quotes are returned with the same cases as what's in the double quotes (e.g., "One" --> One)
• All other column names are returned in lowercase (e.g., ONE --> one)

So I think that means we're good to remove the standardizeResult function for the postgres connector!

[hughess]

It looks like even with the removal of the lowercasing, we'll have an issue with the title formatting function - it's picking up whatever the first A-Z character is and it uppercasing that. We'll need to get it to stop at the first character in the string:

[hughess]

As it turns out, there's an issue in our function for formatting titles. The regex in that function was filtering out the non A-Z characters. With that fixed, we get the following:

[hughess]

@majkinetor I've added a fix for the title formatting issue in #522.

Does #522 solve your problem, or only part of it? If needed, we can fix the lowercase issue in postgres, but I'm hesitant to change that at the moment in case of unintended consequences.

[majkinetor]

Good enough. Thank you.