Dalton,

> Hi Jason

Howdy

> > 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.

That's what BorCon is best for...

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

I try to make it simpler than that. I think of it in terms of RAD/ROI. If
there isn't a healthy combination of these essential elements then your
goose is cooked.

> I like a very powerful language, with everything to be controlled by the
> servers scripting language.

Yep. But, the server should never pre-empt transactions. I agree it would be
nice to kick off a process, detach and then log in again to check status of
it. But, when you think about it, one should simply write a service/daemon
application instead of making the IB engine have to duplicate such a system
of things.

> 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.

I don't see where generators and auto-incrementing have anything to do with
this. Obviously we each have a view from a different angle here.

> 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 unnecessary 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.

I would much prefer that an abstract interface to such be given. Making the
TID visible in the implementation of an application is IMO bad design.
Exposing a bookmark through a clean interface for a row so that it can
persistently be uniquely identified is a much better, cleaner, more generic
way to provide a foundation to solve many aching problems that are painful
to solve otherwise. Plus, the engine internals remain independent of
application implementations. This increases the possibility of it being more
portable to other systems as well.

> > 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.

That is a valid point but in all of the problems I look to this to provide a
foundation for a backup and restore is an indication that the whole system
needs to wash. In the case of having a clustered array of servers you would
restore a backup file once and file copy it to all of the other servers and
kick it on-line. End of fuss. It would know how to sync up and go from there
without a hitch.

> 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
> us.

You totally lost me here. Frankly this sounds awful. The app should not even
consider tweaking the TID. This should stay in the engine untouched by
anything in the application except through the normal expected published
transaction interface.

> 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.

Dalton, here's the essence of our differences: You are writing a database
with a database and I am just trying to make a good database better.

> 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.
> ;)

I love how my wife gives me the "Uh Huh" with a glazed over look in her eyes
not more than 4 or 5 words into it when using her as a sounding board...

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

If you are thinking of things like BEFORE COMMIT and stuff like that I don't
think these things should be applied at the table level. These need to be at
the global database level. I'm also skeptical of them being included in the
regular SQL interface where triggers are as they are something that is
technically "outside" of the client's transaction context.

I'd prefer some "pluggable" hooks that allow binary code to be plugged into
the engine for things like the ability to respond to a transaction
committing and have the structs necessary to walk the committed records and
replicate them elsewhere in committed order.

What I need in essence is a way to stream out row level changes in their
committed order. I think these should be system pluggable modules that know
how to snuggle in with the engine as appropriate.

> More Global Variables

Agreed. User defined variables (for both connection and transaction
contexts) along with a little more info from the system would be splendid.

> 64 bit pointers for tables (the 32bit limit on table size is restrictive
> to some of us)

Agreed.

> A true 64 bit integer.

What's wrong with what we got?

> A true 64 bit unsigned.

What's wrong with ISC_QUAD or CHAR(8) with OCTETS?

> Full manipulation of all datatypes within stored procedures/triggers.

I presume you mean BLOB and ARRAY columns? Not gonna happen right away I
assure.

> 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)

That's what your BOTS are for.

> best regards
>
> Dalton

FWIW,
Jason Wharton
CPS - Mesa AZ
http://www.ibobjects.com