Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Make possible to alter the default database character set without manual update of system table [CORE2777] #3168

Closed
firebird-automations opened this issue Nov 30, 2009 · 8 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: @asfernandes

Is related to QA649

Attachments:
core-2777.log.zip

Add ALTER DATABASE ... SET DEFAULT CHARACTER SET ...

Commits: ad4d4b2

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

Fix Version: 3.0 Alpha 1 [ 10331 ]

Component: Engine [ 10000 ]

assignee: Adriano dos Santos Fernandes [ asfernandes ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: 3.0 Alpha 1 [ 10331 ] =>

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Fix Version: 3.0 Alpha 1 [ 10331 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @pavel-zotov

Was it intentionally done that new character set take effect only for NEW attachments ?

Consider the following script (correct values in 'shell del ...' and 'create database' statements to yours):

shell del C:\MIX\firebird\QA\fbt-repo\tmp\c2777.fdb 1>nul 2>&1;
set echo on;
create database 'localhost/3333:C:\MIX\firebird\QA\fbt-repo\tmp\c2777.fdb' default character set dos850;

drop user admin2;
commit;
create user admin2 password '123';
commit;
grant create table to admin2;
commit;

set list on;
set width db_default_cset 15;

create or alter view v_table_field_cset as
select
rf.rdb$relation_name tab_name
,rf.rdb$field_name fld_name
,cs.rdb$character_set_name fld_cset
,rb.RDB$CHARACTER_SET_NAME db_default_cset
from rdb$relation_fields rf
join rdb$fields ff on rf.rdb$field_source = ff.rdb$field_name
join rdb$character_sets cs on ff.rdb$character_set_id = cs.rdb$character_set_id
cross join rdb$database rb
;
commit;

set term ^;
create or alter procedure sp_create_table(
a_tab_name varchar(31),
a_cset_mnemona varchar(15)
) as
declare v_dbname type of column mon$database.mon$database_name;
declare v_stt varchar(1024);
declare v_usr rdb$user = 'admin2';
declare v_pwd varchar(20) = '123';
begin
execute statement
( 'create table ' || a_tab_name || '( text_' || a_cset_mnemona || ' varchar(10) )' )
on external (v_dbname)
as user :v_usr password :v_pwd;

end
^
set term ;^
commit;

-------------------------- main phase --------------------------
set width fld_name 31;
set width fld_cset 31;

create table old_att_init(text_init varchar(10));
commit;

select *
from v_table_field_cset v
where v.tab_name = upper('old_att_init');

------------------------------------------------------------------

alter database set default character set utf8;
commit;

-- Make new table with default charset = UTF8 using the SAME
-- attachment as just used for changing default charset:
create table old_att_utf8(text_utf8 varchar(10));
commit;

select *
from v_table_field_cset v
where v.tab_name = upper('old_att_utf8');

-- Now try to create table with default charset = UTF8 using NEW attachment:
execute procedure sp_create_table('new_att_utf8','utf8');
commit;

select *
from v_table_field_cset v
where v.tab_name = upper('new_att_utf8');

------------------------------------------------------------------

alter database set default character set win1250;
commit;

-- Make new table with default charset = WIN1250 using the SAME
-- attachment as just used for changing default charset:
create table old_att_w1250(text_w1250 varchar(10));
commit;

select *
from v_table_field_cset v
where v.tab_name = upper('old_att_w1250');

-- Now try to create table with default charset = WIN1250 using NEW attachment:
execute procedure sp_create_table('new_att_w1250','w1250');
commit;

select *
from v_table_field_cset v
where v.tab_name = upper('new_att_w1250');

------------------------------------------------------------------

alter database set default character set dos866;
commit;

create table old_att_d866(text_d866 varchar(10));
commit;

-- Make new table with default charset = DOS866 using the SAME
-- attachment as just used for changing default charset:
select *
from v_table_field_cset v
where v.tab_name = upper('old_att_d866');

-- Now try to create table with default charset = dos866 using NEW attachment:
execute procedure sp_create_table('new_att_d866','d866');
commit;

select *
from v_table_field_cset v
where v.tab_name = upper('new_att_d866');

------------------------------------------------------------------
alter database set default character set win1252;
commit;

-- Make new table with default charset = WIN1252 using the SAME
-- attachment as just used for changing default charset:
create table old_att_w1252(text_w1252 varchar(10));
commit;

select *
from v_table_field_cset v
where v.tab_name = upper('old_att_w1252');

-- Now try to create table with default charset = WIN1252 using NEW attachment:
execute procedure sp_create_table('new_att_w1252','w1252');
commit;

select *
from v_table_field_cset v
where v.tab_name = upper('new_att_w1252');
------------------------------------------------------------------

When I run this script on WI-T3.0.0.31780 then result is evidence that new charset will be seen only for NEW attachment (which is done by ES EDS when calling sp_create_table) -- please see it in attached file.

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

Attachment: core-2777.log.zip [ 12702 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @asfernandes

It's not expected, please create a proper ticket for it.

And please, in this new ticket, mention also that the character set passed in alter database is not being validated to be an existing one.

@firebird-automations
Copy link
Collaborator Author

Commented by: @pavel-zotov

> create a proper ticket for it

CORE4744

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

Link: This issue is related to QA649 [ QA649 ]

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

2 participants