Subject Re: [firebird-support] update rdb$fields - is it safe?
Author Martijn Tonies

> >I've created a table in FB1.5.3CS with computed by fields referring
> >to UDF. Now the database cannot be resotred because gbak resores
> >tables before UDFs - looks like a known bug but not for me...
> >
> >Is it safe to do something like:
> >
> >update rdb$fields f
> >set
> > f.rdb$computed_source =
> > (select f1.rdb$computed_source
> > from rdb$fields f1
> > where f1.rdb$field_name = 'RDB$5035'),
> > f.rdb$computed_blr =
> > (select f1.rdb$computed_blr
> > from rdb$fields f1
> > where f1.rdb$field_name = 'RDB$5035')
> >where
> > f.rdb$computed_source is not null and
> > f.rdb$field_name <> 'RDB$5035'
> No!
> >where RDB$5035 is a field with COMPUTED BY without UDF and the same
> >length.
> >
> >Or there is some better way using ALTER TABLE? Simple ALTER MY_FIELD
> >CHAR(36) doesn't work - the field remains computed by.
> RDB$FIELDS contains domain definitions, not column definitions. The
metadata (system tables) comprehend a fully designed relational database
within the database. RDB$RELATION_FIELDS is a child of RDB$RELATIONS that
involves another relationship with RDB$FIELDS. RDB$FIELDS doesn't know
about the dependencies that pertain to a RDB$RELATION_FIELDS record, just as
a RDB$RELATION_FIELDS record doesn't know anything about other columns in
the database that might use the same domain as it uses.
> Use

Won't work with dependencies, cause Firebird still tracks dependencies
in a very rigid way.

I have to disagree here, this particular update of the system tables is safe
if you add a decent WHERE clause to the statement (now it will almost
update all RDB$FIELDS with a computed by clause).

> to delete the COLUMN from RDB$RELATION_FIELDS and also the domain
definition that was created for that field in RDB$FIELDS - provided of
course that you don't have any dependencies on the column. If there are
dependencies, you will get a message and you will know then what else you
have to do in order to make this change, e.g., drop the dependent
procedure[s] and/or view[s] first.

See above. With a future version of Firebird, we will have "invalid
procedures" and views so you can drop a field while not having to
drop the dependencies, which is much easier to use.

> Dropping a computed field won't lose any data.


Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle &
MS SQL Server
Upscene Productions
My thoughts:
Database development questions? Check the forum!