Oracle Support - Forcing SELECT'ed field names to lower-case is bad mojo

raku-community-modules / DBIish

Database interface for Raku

89 stars 31 forks source link

Oracle Support - Forcing SELECT'ed field names to lower-case is bad mojo #207

Closed ancientwizard closed 3 years ago

ancientwizard commented 3 years ago

I found this in DBDish::Oracle::StatementHandle; after wondering why nothing in my program could find the selected data @!column-name.push: $col_name.decode.lc;

While Oracle may not be case sensitive to such things within a SQL query; programming languages are, therefore code everywhere is expecting fields to expressed exactly as Oracle returned them. It is also not uncommon to specifically create a SELECT F1 as "Camel Cased Name(spaced)" FROM JUNK and expect to not have it twisted in the middle. What if this data was driving a text report but the column header is now displayed all lower case.

my app was looking for $data but it had unexpectedly been smashed into $data.

rbt commented 3 years ago

Removing .lc breaks backward compatibility.

I suspect whomever added it felt it was ugly to have nearly all unquoted columns (commonly used) as uppercase within their Raku code, so a few more opinions from Oracle users would be useful.

The SQLite, MySQL, and PostgreSQL all maintain the casing provided by the database, but those databases also lower-case unquoted identifiers. Upper-case conversion via database round-trip would need some adjustments to the common test.

A few potential solutions:

A flag at connect time which determines whether down-casing is applied; enabled by default.
Only down-case columns which are wholly UPPER-CASED. A quoted uppercase name would still be converted which is unfortunate.
Both of the above?

ancientwizard commented 3 years ago

I agree with you about backward compatibility as far as DBIish but at the time I didn't care as I was very put out by what I see as a very bad design flaw. I'll add a switch. BTW this should not be a question about Raku, it's about playing nice with Oracle and the DBD layer needs to stay out of the way. Consider this, perl-5's DBD didn't coerce Oracle field names to lower-case.

rbt commented 3 years ago

Perl5 DBD has a wide variety of ways of returning column names to the perl code which are implemented by most drivers. NAME, NAME_lc, NAME_uc, NAME_hash, NAME_lc_hash, NAME_uc_hash. All of these are available on every statement handle; _lc and _uc work as implied.

That said, I agree DBIish ideally would not modify what the database transmits. I would be in favour of a deprecation plan of some type. Set a behaviour flag for now, then warn if it isn't provided, then set disabled by default?

ancientwizard commented 3 years ago

:) I added a switch; my app is ~300k lines of source and I never felt the need to have Perl-5 DBD to use its optional manipulators. I guess I figured I'd force the DB to send exactly what I wanted. We'll get there. I did it different from your suggestion as I read it after I submitted. At least consumers have a choice.

ancientwizard commented 3 years ago

I think this explains my feelings on the matter of .lc; let the sarcasm begin

Out there beyond that "Oracle" fence every "DB" thing that crawls, flies, or squats in the mud wants to kill you and eat your eyes out for jujube. You forget that for one second "Oracle" will sh** you out dead with zero warning!