IMPORT FOREIGN SCHEMA behaviour

[mkgrgis]

This is not real issue, but discussion about quote_identifiers to continue https://github.com/ibarwick/firebird_fdw/issues/15#issuecomment-671018068.

I think it will be simple for refactoring, if IMPORT FOREIGN SCHEMA behaviour with LIMIT TO and EXCEPT will be managed by servel-level option quote_identifiers. By ISO basal SQL behaviour with object's identifier is quoting letter to letter between different DBMSs. So, real Firebird or PostgreSQL behaviour (if there is no quoting characters "name" ) by ISO is modification of this basal behaviour. If we consider to automate IMPORT FOREIGN SCHEMA, we needn't precise naming management for separate tables or columns. At this moment user know naming style of Firebird database as value of option quote_identifiers. So, it is possible not to analyse the case of the object name if we know the value of quote_identifiers.

If quote_identifiers= false we always have a risk of unacessed lowercase name of table or column, but no way to provide some options for separate tables or columns for export.

[ibarwick]

I think it will be simple for refactoring, if IMPORT FOREIGN SCHEMA behaviour with LIMIT TO and EXCEPT will be managed by servel-level option quote_identifiers.

The issue with doing that is that it would be inconsistent with the general behaviour of firebird_fdw, which is that an unquoted object name in PostgreSQL is passed to Firebird as-is. Each applies their respective default folding to that unquoted name.

So in the same way that it's possible to write CREATE FOREIGN TABLE foo (...) in PostgreSQL (without any quoting specified), which maps to Firebird table FOO (or foo, but not "foo"), it's also possible to do IMPORT FOREIGN SCHEMA xxx (LIMIT TO foo) ..., which maps to the Firebird table FOO (or foo, but not "foo").

I am considering adding further options to IMPORT FOREIGN SCHEMA for finer control of object name quoting.

[mkgrgis]

Yes, there is an irreparable conflict between recommended ISO SQL behaviour and real PosgtreSQL+Firebird transformation of object names without quoting.

the general behaviour of firebird_fdw

must be respected as rational for quote_identifiers = false situation. So, there is no way to normalisation without adding any options to IMPORT FOREIGN SCHEMA.

[mkgrgis]

For cases: Upper Lower Mixed we have this transformation table

Fb quote_identifiers Pg
U  f                 L  
L  f                 n/a
M  f                 M
U  t                 U
L  t                 L
M  t                 M

[mkgrgis]

There is a bug: Firebird:

CREATE TABLE "AB" ("AB" int, "Ab" INT, "aB" INT, "ab" INT, "c" boolean);
CREATE TABLE "aB" ("AB" int, "Ab" INT, "aB" INT, "ab" INT, "c" boolean);
CREATE TABLE "Ab" ("AB" int, "Ab" INT, "aB" INT, "ab" INT, "c" boolean);
CREATE TABLE "ab" ("AB" int, "Ab" INT, "aB" INT, "ab" INT, "c" boolean);
INSERT INTO AB (AB,"Ab","aB","ab","c") VALUES
     (1,2,3,4,false);
INSERT INTO AB (AB,"Ab","aB","ab","c") VALUES   
     (5,6,7,8,true);
INSERT INTO AB (AB,"Ab","aB","ab","c") VALUES   
     (9,10,11,12,NULL);
INSERT INTO "aB" SELECT * FROM "AB";
INSERT INTO "Ab" SELECT * FROM "AB";
INSERT INTO "ab" SELECT * FROM "AB";

Correct foreign server in Postgres (the name is fb_Test)

CREATE SERVER "fb_Test"
    FOREIGN DATA WRAPPER firebird_fdw
    OPTIONS (address 'localhost', database '/tmp/Test.fdb');

Special schema CREATE SCHEMA "fbTest";in Postgres

On SQL

IMPORT FOREIGN SCHEMA x
  FROM SERVER "fb_Test"
  INTO "fbTest";

there is errors:

1) Something like duplicate "ab" column (ERROR) during "ab" table import(CONTEXT).

2) When i added CREATE TABLE "!AB" ("x" int, "y" INT); with no data

SQL Error [42704]: ERROR: server "fb_test" not exist Where: import of foreign table "!AB"

This errors don't depends on ALTER SERVER "fb_Test" options (add quote_identifiers 'true'); and server-level quote_identifiers at all.

Is this a problems in firebird_fdw or in PostgreSQL's IMPORT FOREIGN SCHEMA?

I am using Firebird 3.0+ and PostgreSQL 14 for this test.

[mkgrgis]

@ibarwick, have you got any time for tests around of previous comment https://github.com/ibarwick/firebird_fdw/issues/17#issuecomment-1035878298 ? There is debug output DEBUG.log for my test.

[mkgrgis]

Mixedcase problems fixed by https://github.com/ibarwick/firebird_fdw/pull/37