set term !;
create or alter procedure rdb$fix_metadata
(charset varchar(31) character set ascii)
returns
(table_name char(31) character set unicode_fss,
field_name char(31) character set unicode_fss,
name1 char(31) character set unicode_fss,
name2 char(31) character set unicode_fss)
as
declare variable system integer;
declare variable field1 char(31) character set unicode_fss;
declare variable field2 char(31) character set unicode_fss;
declare variable has_records integer;
begin
for select rf.rdb$relation_name, rf.rdb$field_name,
(select 1 from rdb$relation_fields
where rdb$relation_name = rf.rdb$relation_name and
rdb$field_name = 'RDB$SYSTEM_FLAG'),
case rdb$relation_name
when 'RDB$CHARACTER_SETS' then 'RDB$CHARACTER_SET_NAME'
when 'RDB$COLLATIONS' then 'RDB$COLLATION_NAME'
when 'RDB$EXCEPTIONS' then 'RDB$EXCEPTION_NAME'
when 'RDB$FIELDS' then 'RDB$FIELD_NAME'
when 'RDB$FILTERS' then 'RDB$INPUT_SUB_TYPE'
when 'RDB$FUNCTIONS' then 'RDB$FUNCTION_NAME'
when 'RDB$GENERATORS' then 'RDB$GENERATOR_NAME'
when 'RDB$INDICES' then 'RDB$INDEX_NAME'
when 'RDB$PROCEDURES' then 'RDB$PROCEDURE_NAME'
when 'RDB$PROCEDURE_PARAMETERS' then 'RDB$PROCEDURE_NAME'
when 'RDB$RELATIONS' then 'RDB$RELATION_NAME'
when 'RDB$RELATION_FIELDS' then 'RDB$RELATION_NAME'
when 'RDB$ROLES' then 'RDB$ROLE_NAME'
when 'RDB$TRIGGERS' then 'RDB$TRIGGER_NAME'
else NULL
end,
case rdb$relation_name
when 'RDB$FILTERS' then 'RDB$OUTPUT_SUB_TYPE'
when 'RDB$PROCEDURE_PARAMETERS' then 'RDB$PARAMETER_NAME'
when 'RDB$RELATION_FIELDS' then 'RDB$FIELD_NAME'
else NULL
end
from rdb$relation_fields rf
join rdb$fields f
on (rf.rdb$field_source = f.rdb$field_name)
where f.rdb$field_type = 261 and f.rdb$field_sub_type = 1 and
f.rdb$field_name <> 'RDB$SPECIFIC_ATTRIBUTES' and
rf.rdb$relation_name starting with 'RDB$'
order by rf.rdb$relation_name
into :table_name, :field_name, :system, :field1, :field2
do
begin
name1 = null;
name2 = null;
if (field1 is null and field2 is null) then
begin
has_records = null;
execute statement
'select first 1 1 from ' || table_name ||
' where ' || field_name || ' is not null' ||
iif(system = 1, ' and coalesce(rdb$system_flag, 0) in (0, 3)', '')
into :has_records;
if (has_records = 1) then
begin
suspend;
execute statement
'update ' || table_name || ' set ' || field_name || ' = ' ||
' cast(cast(' || field_name || ' as blob sub_type text character set none) as ' ||
' blob sub_type text character set ' || charset || ') ' ||
iif(system = 1, 'where coalesce(rdb$system_flag, 0) in (0, 3)', '');
end
end
else
begin
for execute statement
'select ' || field1 || ', ' || coalesce(field2, ' null') || ' from ' || table_name ||
' where ' || field_name || ' is not null' ||
iif(system = 1, ' and coalesce(rdb$system_flag, 0) in (0, 3)', '')
into :name1, :name2
do
begin
suspend;
execute statement
'update ' || table_name || ' set ' || field_name || ' = ' ||
' cast(cast(' || field_name || ' as blob sub_type text character set none) as ' ||
' blob sub_type text character set ' || charset || ') ' ||
' where ' || field1 || ' = ''' || name1 || '''' ||
iif(name2 is null, '', ' and ' || field2 || ' = ''' || name2 || '''');
end
end
end
end!
commit!
set term ;!
|
