Create and use a speciall COLLATION under FB3 or FB4

[Dany40]

Hello!

I have a special collation defined for my firebird databases: 

CREATE COLLATION ES_ES_AI

 FOR ISO8859_1

 FROM ES_ES

 NO PAD

 CASE SENSITIVE

 ACCENT INSENSITIVE;

Under Firebird 2.5 it was possible to define this collation as the default for all the purposes in the whole database, and I don't need any special command to have all my talbes an all the funcions unsing this collation. Fore example, each time I create a new table it become defined with all the fields with that collation. When I verify the DDL I see:

CREATE TABLE ABOG

(

  ABOG Char(10) NOT NULL COLLATE ES_ES_AI,

  DSCR Varchar(240) NOT NULL COLLATE ES_ES_AI,

  CONSTRAINT ABOG_PK PRIMARY KEY (ABOG)

);

Also, all the funcions works naturally using my collation as a default.

But under Firebird 3 and Firebird 4 I can’t get same result. For example the tables are not create by default with that collation. When I verify the DDL I see:

CREATE TABLE ABOG

(

  ABOG Char(10) NOT NULL,

  DSCR Varchar(240) NOT NULL,

  CONSTRAINT ABOG_PK PRIMARY KEY (ABOG)

);

Also, if I use functions like "lower" or "upper" it seems they are not using my collation.

How can I get same solution on FB3 and FB4?. I tried to set a default collation for ISO8859_1 but then I get all the tables with all the fields defined with a special code (instead of the standard field type), for example "ABOG RDB$4 NOT NULL" or "DSCR RDB$5 NOT NULL".

THANK YOU in advanced!!!

Best regards,

[Mark Rotteveel]

In Firebird 2.5, you probably made the collation the default collation,
and did not do so under Firebird 3.0 and 4.0. You need to use ALTER
CHARACTER SET ISO8859_1 SET DEFAULT COLLATION ES_ES_AI [1]. Also make
sure that the character set is set as the default character set of your
database using ALTER DATABASE SET DEFAULT CHARACTER SET ISO8859_1 [2].
And keep in mind, such changes do not apply retroactively; they only
apply to columns created after these changes.

Mark

[1]:
https://www.firebirdsql.org/file/documentation/html/en/refdocs/fblangref40/firebird-40-language-reference.html#fblangref40-ddl-charset
[2]:
https://www.firebirdsql.org/file/documentation/html/en/refdocs/fblangref40/firebird-40-language-reference.html#fblangref40-ddl-db-alter

[Dany40]

I really thank you Mark. Please note I already set it as default collation for   ISO8859_1, but after doing that then all my fields are defined like  " DSCR RDB$5  ..." instead of " DSCR Varchar(240) ..."; I cannot understand why.

[Mark Rotteveel]

On 2021-08-02 19:13, Dany40 wrote:
> I really thank you Mark. Please note I already set it as default
> collation for ISO8859_1, but after doing that then all my fields are
> defined like " DSCR RDB$5 ..." instead of " DSCR Varchar(240) ...";
> I cannot understand why.

That is an issue that you did not mention in your original question. In
any case, `RDB$<nnn>` are generated domains that are created for all
fields that do not have an explicit (custom) domain (it's the name of a
record in `RDB$FIELDS`, which through `RDB$RELATION_FIELDS` is linked to
the a column of your table).

What tool are you using to inspect/extract the DDL? It sounds like it
doesn't correctly handle all cases of generated domains.

Mark

[Vlad Khorsun]

On Monday, 2 August 2021 at 19:53:30 UTC+3 Dany40 wrote:

Hello!

I have a special collation defined for my firebird databases: 

CREATE COLLATION ES_ES_AI

 FOR ISO8859_1

 FROM ES_ES

 NO PAD

 CASE SENSITIVE

 ACCENT INSENSITIVE;

 

  The problem is because you specified "NO PAD". Now, when default and connection charset is ISO8859_1

and default collation of ISO8859_1 is ES_ES_AI, comparison of metadata names with literals uses this collation

and takes into account trailing spaces (stored in metadata) making result FALSE.

  For example, run

SELECT RDB$FIELD_NAME FROM RDB$RELATION_FIELDS RF
 WHERE RF.RDB$RELATION_NAME = 'ABOG' COLLATE ES_ES_AI;

and

SELECT RDB$FIELD_NAME FROM RDB$RELATION_FIELDS RF
 WHERE RF.RDB$RELATION_NAME = 'ABOG' COLLATE  ISO8859_1;

First query returns no records, while second one returns two records, as expected.

Regards,

Vlad

[Mark Rotteveel]

On 03-08-2021 16:27, Vlad Khorsun wrote:
>   The problem is because you specified "NO PAD". Now, when default and
> connection charset is ISO8859_1
> and default collation of ISO8859_1 is ES_ES_AI, comparison of metadata
> names with literals uses this collation
> and takes into account trailing spaces (stored in metadata) making
> result FALSE.
>
>   For example, run
>
> SELECT RDB$FIELD_NAME FROM RDB$RELATION_FIELDS RF
>  WHERE RF.RDB$RELATION_NAME = 'ABOG' COLLATE ES_ES_AI;
>
> and
>
> SELECT RDB$FIELD_NAME FROM RDB$RELATION_FIELDS RF
>  WHERE RF.RDB$RELATION_NAME = 'ABOG' COLLATE ISO8859_1;
>
> First query returns no records, while second one returns two records, as
> expected.

Shouldn't the collation of RDB$RELATION_NAME be used for such comparisons?

Mark
--
Mark Rotteveel

[Dany40]

Thank you very much.

[Vlad Khorsun]

...

> SELECT RDB$FIELD_NAME FROM RDB$RELATION_FIELDS RF
>  WHERE RF.RDB$RELATION_NAME = 'ABOG' COLLATE ISO8859_1;
>
> First query returns no records, while second one returns two records, as
> expected.

Shouldn't the collation of RDB$RELATION_NAME be used for such comparisons?

  Why ? Is there any standard rules for it ?

  I have no exact answer, I just explain how it works.

Regards,

Vlad

[Mark Rotteveel]

I might look it up later, though these parts of the SQL standard usually
give me a headache (and I already have one...). However, at first
thought, it seems logical to me that if a column has a certain
collation, that is the collation that gets used for comparisons
involving that column and a literal, not the default collation of the
connection character set. Though, I am wondering what should happen when
comparing two columns with two different collations.


Mark
--
Mark Rotteveel

[Vlad Khorsun]

On Tuesday, 3 August 2021 at 18:57:58 UTC+3  Mark Rotteveel wrote:

On 03-08-2021 17:52, Vlad Khorsun wrote:
> ...
>
> > SELECT RDB$FIELD_NAME FROM RDB$RELATION_FIELDS RF
> >  WHERE RF.RDB$RELATION_NAME = 'ABOG' COLLATE ISO8859_1;
> >
> > First query returns no records, while second one returns two
> records, as
> > expected.
>
> Shouldn't the collation of RDB$RELATION_NAME be used for such
> comparisons?
>
>
>   Why ? Is there any standard rules for it ?
>
>   I have no exact answer, I just explain how it works.

I might look it up later, though these parts of the SQL standard usually
give me a headache (and I already have one...). However, at first
thought, it seems logical to me that if a column has a certain
collation, that is the collation that gets used for comparisons
involving that column and a literal, not the default collation of the
connection character set.

  String comparison routine have no idea of these strings origins. It works in generic way.

Comments (at INTL_compare()) say:

    // YYY - by SQL II compare_type must be explicit in the
    // SQL statement if there is any doubt

    USHORT compare_type = MAX(t1, t2);    // YYY

where t1 and t2 is text type ID's (combined charset id and collation id).

texttype for UTF8 with its default collation is 4, so it almost never chosen as comparison order.

 

Though, I am wondering what should happen when
comparing two columns with two different collations.

  Good question ;)

Regards,

Vlad