PRESERVE_CASE doesn't preserve case in VIEWs and CHECK constraints

[otterrisk]

Setting PRESERVE_CASE to 1 and migrating an Oracle schema gives expected results on CREATE/ALTER TABLE statements, etc., i.e. table names in the resulting table.sql are double quoted upper case (e.g. CREATE TABLE "MY_TABLE"). However, CREATE OR REPLACE VIEW statements in the resulting view.sql have unquoted table names in AS clauses, which results in:

psql:./schema/views/view.sql:X: ERROR:  relation "my_table" does not exist
LINE 2:      FROM MY_TABLE
                  ^
ERROR: an error occurs when importing file ./schema/views/view.sql.

kind of errors. Similar thing happens when table definitions contain CHECK constraints, which result in unquoted table names as well.

Ora2Pg v21.0

[darold]

Yes, Ora2Pg can only quote identifier when they are fully qualified otherwise there is nothing which makes it possible to distinguish an identifier from a keyword in a view definition. Ora2Pg doesn't embed a SQL parser. Won't be fixed.

[otterrisk]

Just an idea... Ora2Pg knows table and column names from the scanning stage anyway. It doesn't need to understand SQL in order to double-quote a set of known identifiers in the post-processing stage. Wouldn't that be the way to go?

[darold]

Yes why not, this will slow down the view export but once it is done it is done. But honestly why preserving the Oracle case? Does your queries systematically used double quote for identifiers? In all migration I have done PRESERVE_CASE was disabled because it has no interest in keeping thing upper cases only.

[otterrisk]

I am using an ORM framework in Python, which happens to be case sensitive with respect to table/column names. My code (18k LOC) used to work with an Oracle database, so all names (which are hard-coded for a good reason) are upper case. Now we are migrating to a PostgreSQL database, and changing table/column names in the code would break the Oracle connectivity, which we need to keep.

[darold]

Ok good to know that there is good reasons to have upper case forced in Oracle, I hope to never encounter such a case :-) However if there is a PRESERVE_CASE configuration directive this is to allow that, I mark the issue as needing enhancement to gather table+column names before migrating view to be able to find them in the view source code definition. Thanks for the report.

[otterrisk]

I meant hard-coding table/column names in general has a good reason ;-) The ORM framework derives table names from class names by default. Hard-coding them is a security measure, to prevent class name refactoring from braking the ORM mapping. But once you opted for hard-coding names, you need to decide for the case as well. It turns out that the particular framework I am using is case sensitive under the hood.

Thanks for considering my report as an enhancement! With some guidance from your side I could help getting it done.