Subject Re: [IB-Architect] Next ODS change (was: System table change)
Author Dalton Calford
Hi Jason

Jason Wharton wrote:
> Too bad you didn't attend the conference session directed by Jim Starkey,
> Ann Harrison and Charlie Caro that covered this very challenge. We discussed
> at length how to implement such a feature. This capability is needed for
> InterBase to effectively grow. Unfortunately we didn't reach a conclusion as
> a whole as to how it should be done.

When did this conference take place??
I would have loved to be there if for no other purpose but to meet
everyone face to face.

As to the problem at hand, it comes down to a phylisophical question as
to what you want IB to become.

I like a very powerful language, with everything to be controlled by the
servers scripting language. I have always wanted to be able to get the
transaction ID within a stored procedure, as well as have a unique row
id. BUT, it comes down to the argument of having a auto-incrementing
datatype versus having a trigger/procedure combined with a generator.
With the generator approach, I can perform alot more 'magic' with the
database and I have alot more flexibility. It takes more work, but, in
the end, you can get alot more elegant solutions. I am also against
having both because it adds unneccessary complexity because you can do
the job with the tools already available (even if it is more work).
So when it comes to fixed RB_KEY values, I much prefer a generated
surrogate key - but have the engine understand that it is a walkable
cursor (since it is the primary key). This gives the benefits of the
fixed RB_KEY but, leaves the implementation to the end developer.
I think we both want the same thing, but, how we implement the change is
where we may have a disagreement (and I bet many lively discussions)

> Keep in mind, I believe the TID is only 4 bytes and it would (hopefully) be
> possible to flag the record with a bit such that it would only be necessary
> to store the original TID when the record had actually been touched. This
> way, it would only be an increase in disk space once the record had actually
> been updated. I really don't think that 4 bytes max per record is all that
> significant...

You see, If I wanted that information, I would create a column to hold
it and I would have a trigger to populate the value, and use a view on
the table to stop the end user from seeing the extra columns (this is
how I keep track of a records create time, last update time, updated by,
deleted flag etc...)
So, I too want to see the TID, only, I would want to see it as a global
value that can be accessed from within a SP/Trigger so that I can use it
if I wish.

> It's not adding a tool that you have to use. It's firming up the foundation
> of InterBase so that it has some growing room in areas that are absolutely
> critical for its long-term success. IMHO

I agree that such a value is important, but, I also see a problem.
Transaction ID's do not survive a backup/restore cycle. This is a good
thing. If we relied upon the TID's not being refreshed in this way, we
would have crashed IB years ago.

But it would still be nice to have a fixed transaction ID.
With a trigger based solution, we have the best of both worlds.
The trigger can modify the TID so that it is in the context of a
timeframe as well. A numeric only/fixed result would limit us vs help
> For crying out loud, someone who uses IB at an Enterprise level like
> yourself, would be the last person I would suspect disagreement from when
> suggesting practical solutions to things that, although I'm sure you enjoyed
> inventing alternatives on your own, would perhaps spared much time and
> nerves to spend with your family instead. Not to mention your company's
> budget.


Jason, the big reason I love IB is that so few pre-concieved solutions
were built into it. In my mind, it was the 'C' of the database world.
It took more work to get where you wanted, but, in the end, you got
exactly what you wanted.
I like the idea of TID's and Fixed RB_KEYs and other such things, but,
as a developer, I would rather have the values available so that I can
manipulate them inside of a trigger than have them automatically appear
for the use of client programs.
As for family time, my wife is becoming a good sql programmer because in
order to make sure my ideas are ok, I use her as a sounding board.

The changes I would like to see in IB are as follows

More Triggers
More Global Variables
64 bit pointers for tables (the 32bit limit on table size is restrictive
to some of us)
A true 64 bit integer.
A true 64 bit unsigned.
Full manipulation of all datatypes within stored procedures/triggers.

and finally but with a view of version 9 of Interbase....
Full client connectivity within SP/Trigger language (connect to database
B from database A and perform operations)

best regards