Problems with quoted names of objects in Postgres and Firebird

[mkgrgis]

Continue for tests from https://github.com/ibarwick/firebird_fdw/issues/13.

There is no correct form in PostgreSQL for this Firebird table

CREATE TABLE "fb_T"
(
  "i1" integer NOT NULL,
  "i0" integer,
  "t"  character varying(16)
);

My propositions for You

1. New options allowed for foreign server, table and column "preserve_table_case" (for server and foreign table) and "preserve_column_case"(for server, foreign table and column). Options must have overlapping "preserve_table_case" for a table and overlapping "preserve_column_case" for a column.
2. Allow quoting in literal of options "table_name" and "column_name" with new c error "quote not closed". Will it rational?

What is recommended default behaviour by ISO if case not preserved? All PostgreSQL names uppercased to Firebird, all Firebird names lowercased to PostgreSQL. Transformation of character by ISO must pass "turkish test" by current locale of PostgreSQL database. If locale=tr_TR there is special toupper/tolower rules.

As discussed by lots and lots of people, the “I” in Turkish behaves differently than in most languages. Per the Unicode standard, our lowercase “i” becomes “İ” (U+0130 “Latin Capital Letter I With Dot Above”) when it moves to uppercase. Similarly, our uppercase “I” becomes “ı” (U+0131 “Latin Small Letter Dotless I”) when it moves to lowercase.

See http://www.moserware.com/2008/02/does-your-code-pass-turkey-test.html

[ibarwick]

The underlying issue here is that Firebird table and column names are generated using PostgreSQL's internal quote_identifier() function, which by default will quote identifiers according to PostgreSQL's rules (i.e. anything which contains characters other than lower-case numbers, digits and underscores). This works for most cases, except when the Firebird table or column name was created with a quoted lower-case identifier, e.g.

CREATE TABLE "lowercasetest" (
  "col1" INT
)

To enable firebird_fdw to handle this kind of case, I've added the table/column level option quote_identifier, which will be available from v1.2.0, i.e.:

CREATE FOREIGN TABLE lowercasetest (
  col1 INT OPTIONS (quote_identifier 'true')
)
SERVER fb_test
OPTIONS (quote_identifier 'true')

See README for details.

Note that as far as I can tell from the documentation (Firebird 2.5, can't find any documentation for later version), unquoted Firebird identifiers can only contain a subset of ASCII characters anyway, so it should be able to handle any unquoted identifiers passed by PostgreSQL. It seems quoted identifiers may contain "characters from any Latin character set", but in that case the user should explicitly specify the Firebird table/column name when creating the foreign table.

[mkgrgis]

Many thanks to You, @ibarwick ! The quote_identifier option is an elegant solution for this issue and it's compatible with ISO SQL behaviour! Is it hard to write FOREIGN SERVER-level option quote_identifier? In my experience quote_identifier rule usually is server-level.

[ibarwick]

Does that mean your Firebird databases use object names which are mainly quoted identifiers? I.e. table and column names etc. are mainly mixed or lower case?

[mkgrgis]

Yes, it does. Usually naming style belongs to separate database (fdb file). Most of tables of my known databases usually use the same style: UPPERCASE, MiXeDcAsE or lowercase. Databases with both MiXeDcAsE or lowercase naming is 20-40% of total.

[ibarwick]

I see. I don't recall encountering quoted identifiers other than as very occasional exceptions, so have not previously put much effort into handling them.

Is it hard to write FOREIGN SERVER-level option quote_identifier? In my experience quote_identifier rule usually is server-level.

Not much, it turns out; see: 57dbdcac85dcab0b0007ecd2030efba7556194fe . Note the server-level option is quote_identifiers.

As noted in the README, IMPORT FOREIGN SCHEMA does not yet handle quoted identifiers, but work is in progress to fix that as well.

[ibarwick]

As noted in the README, IMPORT FOREIGN SCHEMA does not yet handle quoted identifiers, but work is in progress to fix that as well.

This is now implemented, with a couple of caveats.

[mkgrgis]

Many thanks, @ibarwick ! The functions works fine. The table from https://github.com/ibarwick/firebird_fdw/issues/15#issue-640708387 is visible now. I don't use IMPORT FOREIGN SCHEMA often, but we can discuss behaviour of this SQL command in a new issue for a long consideration. I've checked your new implementations, but I have no idea yet what is the best behaviour for IMPORT FOREIGN SCHEMA by the ISO SQL. I need to read more.