Ann W. Harrison wrote:

> There is a surrogate key which is used for some purposes, but it's
> not stable across backup/restore, so it's not suitable for procedures,
> views, and other stable structures.

didn't know about it, but anyway that is not the key that is necessary
to acomplish the task. The Surrogate key I suggested would be part of
the system tables and preserved the same way the
RDB$.Relations.RDB$Relation_Name is preserved.

> You're right that a surrogate
> key would make this much simpler, but it would have to work both
> for backup/restore and for extracting the metadata and recreating
> the database. And probably for extracting metadata from two databases
> and merging them - done by a person with a text editor, not
> automatically. ... names are pretty easy to track in that case and
> surrogates are not.
>

The surrogate key would be transparent to the user POV, let me try to
make an example:

create table foo(Bar int);

Would insert a record on RDB$Relations without providing the value for
surrogate key, so it will be populated trough a generator. On the
back-up process, the surrogate key will be saved with the rest of the
RDB$Relations fields so on the restore process it will be restored with
the same value (would not be supplied by a generator since it has a
explicit value).

The "Bar"field would be inserted on RDB$Relation_Fields table without a
value too so it would be populated by generator, the
RDB$Relation_Fields.RDB$Relation_Name would go away and a
RDB$Relation_ID would take it's place.

On procedure compilation time (sorry don't know if it is the correct
technical term used inside FB Internals) the Object's ID would be stored
instead of the object names, so changing the name of a field would not
impact on the compiled code and would be preserved on back-up/restore cycle.

The metada extraction tools would generate standard DDL instructions,
when one extract the metadata from Table Foo it will generate

Create Table Foo(Bar int)

No objects ID's would be preserved since it will be new objects on the
destination/new database it will get new Objects ID's. The user would
not need to know or specify the object id inside his scripts (and don't
know why he should want it)

The dependencies would be tracked by Objects ID's instead of objects names.

The problem would be the FB engine to change the procedure (view,
trigger and so on) source code to swap the names (exactly it has to be
done as if it as now). Don't know how hard would be to have the source
code stored inside the engine to hold reference to objects Id's instead
of names and when someone asks for RDB$Procedures.RDB$Procedure_Source
substitute the Objects ID to the Objects Name (a Blob Filter perhaps).

Something like:
Create Procedure MyProc(i int) returns (Bar int) as
begin
for
select
Foo.Bar
from
Foo
where
Foo.Bar = :i
into
:Bar
begin
suspend;
end
end

would be stored on RDB$Procedures.RDB$Procedure_Source as:

Create Procedure MyProc(i int) returns (Bar int) as
begin
for
select
<2>
from
<1>
where
<2> = :i
into
:Bar
begin
suspend;
end
end

Assuming <1> is the Table Foo Object ID and <2> is Field Foo.Bar Object
ID. Probably the Parameters would have Objects ID's too, but I think the
sample above give the idea I want to pass.

What I meant to say is:
The user will see always Names, never objects ID's, the compiled code
will use objects id's so any change on the name means nothing to
compiled code. If Possible the source code (views, procedures, triggers
and so on) will store objects ID's internally and transparently convert
to objects names on the fly when requested by the user.

Don't know if I made myself clear, and if this is a good thing or hard
to implement.

>
> Best,
>
>
> Ann
>
>

thanks Ann.

--
Alexandre Benson Smith
Development
THOR Software e Comercial Ltda
Santo Andre - Sao Paulo - Brazil
www.thorsoftware.com.br