Subject Re: [firebird-support] Changing collation
Author Tomasz Tyrakowski
On 28.06.2017 at 22:58, Ann Harrison aharrison@...
[firebird-support] wrote:
> Not if you have indexes, a declared primary key, or a declared unique
> constraint. The index keys are a transformation of the value into a format
> that produces the desired ordering through binary comparisons.

Any chance that setting the RDB$RELATION_FIELDS.RDB$COLLATION_ID and
then backing up and restoring the whole database would re-create the
index trees properly?

> Not safe, won't work, and will produce very odd results on range queries on
> any
> index. Or, say, a query that causes Firebird to return rows in index
> order.

Agreed, but again... restoring the database from a backup rebuilds all
indices anyway, the piece of information I lack is: would they be
rebuilt in the proper (new) collation?

>> Also, does the RDB$RELATION_FIELDS.RDB$COLLATION somehow depend on the
>> default character set of the database, or is it universal (i.e. does the
>> collation code from RDB$RELATION_FIELDS.RDB$COLLATION always mean the
>> same, or does a single code mean different collations in databases with
>> different default character sets)?
> The format of the keys depends on the character set - different character
> sets
> have different meanings for some byte values, which collate differently.

Yes, that's clear, but my question concerned the collation id values
kept in RDB$COLLATION_ID. Suppose you have a database with the default
character set WIN1250. Setting the PXW_PLK collation results in
RDB$COLLATION_ID set to 3 (checked that). Is it possible that the same
value 3 would correspond to a different collation in another database,
e.g. one with the default character set WIN1252? Or is 3 strictly bound
to PXW_PLK and always means PXW_PLK, regardless of the character set of
the database? I suppose this is not the case, 'cause setting a column
collation to WIN1250 in a database with the default character set
WIN1250 results in RDB$COLLATION_ID = 0 (and I don't think WIN1250 is so
special as to be given the value of 0).
On a second thought, don't waste your time on it. I'll just create those
two DBs, see for myself, and post a quick update.

> Good luck,
> Ann

Thank you for your time and valuable information.

Best regards