"alter domain" supporting collation change [CORE1202]

[firebird-automations]

Submitted by: Jorge Andres Brugger (jbrugger)

Relate to CORE6324

Votes: 12

Related to CORE1058: "ALTER DOMAIN and ALTER TABLE don't allow to change character set and/or collation"

CREATE DOMAIN D_TEST AS
VARCHAR(10) CHARACTER SET ISO8859_1
COLLATE ES_ES

You can do:
alter DOMAIN d_test type
VARCHAR(10) CHARACTER SET iso8859_1

But you can't do:
alter DOMAIN d_test type
VARCHAR(10) CHARACTER SET iso8859_1
COLLATE ES_ES

Adding collate support for alter domain would be useful

[firebird-automations]

Modified by: @pcisar

Version: 2.1 Initial [ 10160 ]

Version: 2.1 Beta 1 [ 10141 ] =>

Version: 2.1.0 [ 10041 ] =>

[firebird-automations]

Modified by: @pcisar

Workflow: jira [ 11751 ] => Firebird [ 14319 ]

[firebird-automations]

Commented by: @sim1984

As recently direct editing system tables is prohibited, the need for this sql statement became more evident. In particular IBExpert previously allowed to change the collation modifying system tables. Now that opportunity taken away.

[firebird-automations]

Commented by: Radek Palmowski (palma)

This is a very serious restriction that inhibits migration to FB3.
We have a database in which we sometimes want to change the domain XXX from WIN1250 PXW_PLK to UTF8 UNICODE.
Because the domain is used in tables(14), views(126), triggers(15) and procedures(59) in parameters and local variables in body, so to do this we must:
- create a new domain XXX_UTF with character set UTF8 and collate UNICODE
- drop all triggers and procedures which uses XXX domain,
- change all fields from the XXX domain to the new one XXX_UTF in all tables
- delete the old domain
- change the name of the new domain from XXX_UTF to XXX
- restore all procedures and triggers

It is possible to change priority of this issue?

[firebird-automations]

Commented by: @livius2

It is not only limitation it work now wrongly as it alter collation to some default.
e.g.

CREATE DOMAIN XXX Varchar(1000) CHARACTER SET WIN1250 COLLATE PXW_PLK;

now when you alter its e.g. size

ALTER DOMAIN XXX TYPE VARCHAR(2000);

it will be after alter equivalent to

CREATE DOMAIN XXX Varchar(2000) CHARACTER SET WIN1250 COLLATE WIN1250;

as you can see it change PXW_PLK to WIN1250;
And as we cannot specify collation all benefits from domain dissapear here.
As we must now create new domain and assingn it to all fields, stored procedure parameters and variables by hand.

[firebird-automations]

Modified by: Sean Leyne (seanleyne)

Link: This issue relate to CORE6324 [ CORE6324 ]