Dear Support!

I want to ask about EDB "Autoinc" because we are in dispute with my boss about ID Generation, and some things are not clear to me.

1.)
When I use "always" in ID generation, the EDBTable ID Field is maded Readonly or not (to protect ourself from mistakes) in VCL?

2.)
When I use "transaction" to I can rollback the full MD (Mail, Mail elements, Mail subelements), what happening with ID-s generated by EDB?
Do we get ID hole?
1
2
3. [New item]
4 Inserted after me
When we rollback item 3 with all subelements, then ID3 is seems to be deleted?

3.)
If we uses transaction then posted (but not commited) records are visible in by other users?

4.)
What do you think about "non-blocking" ID generation (for the tables are need to my owned ID) I describe now:

(First idea was a blocker solution with an ID generator that lock a row with tablename while it try to get new ID, and then check it is existing in the original table. But may a client dying lock whole database).

I have a table named GenID. It have one Field only, an autoinc value.
When I want to get a new ID for ANY table, I make an insert to it, and get the ID.
After this I check the original table for this ID, and if don't exists, I can use it.
{This method needs a utility that sometimes clear the old records to decrease this table size.}

Because insert is not blocking other users, they can concurrently get the ID-s.
This ID generator method is the first filter to avoid ID duplication.
Then next is when I check this ID in the original table to prevent problems (when the Last Generated ID is lower than placed in the table).

This method is usable for only tables that have very complex MD relations, and where we need to know all values before post.

Thanks for your help:
  KK

<< 1.) When I use "always" in ID generation, the EDBTable ID Field is maded
Readonly or not (to protect ourself from mistakes) in VCL? >>

It doesn't matter - whatever value is in the field is replaced with the
IDENTITY value during the execution of the INSERT.  If you overwrite the
value afterwards using an update, then that is completely up to you.  If you
don't want users writing to certain columns, then you should only give them
access to the table via views that constrain which columns they can update.

<< 2.) When I use "transaction" to I can rollback the full MD (Mail, Mail
elements, Mail subelements), what happening with ID-s generated by EDB?  Do
we get ID hole? >>

No.

<< 3.) If we uses transaction then posted (but not commited) records are
visible in by other users? >>

No.

<< 4.) What do you think about "non-blocking" ID generation (for the tables
are need to my owned ID) I describe now: >>

It doesn't really matter - transaction locking in ElevateDB is restrictive,
so other users/sessions cannot insert, update, or delete any rows in the
tables involved in a transaction.

http://www.elevatesoft.com/manual?action=viewtopic&id=edb2sql&topic=Transactions

--
Tim Young
Elevate Software
www.elevatesoft.com

Dear Tim!

First: thanks!

"Tim Young [Elevate Software]" wrote:

<< 1.) When I use "always" in ID generation, the EDBTable ID Field is maded
Readonly or not (to protect ourself from mistakes) in VCL?
It doesn't matter - whatever value is in the field is replaced with the
IDENTITY value during the execution of the INSERT.  If you overwrite the
value afterwards using an update, then that is completely up to you.  If you
don't want users writing to certain columns, then you should only give them
access to the table via views that constrain which columns they can update. >>

This good for us!

<< 2.) When I use "transaction" to I can rollback the full MD (Mail, Mail
elements, Mail subelements), what happening with ID-s generated by EDB?  Do
we get ID hole?
No. >>

How to handle this? For example we insert more records to Table A, ID 3, 4, 5.
But something is wrong, I must rollback it.

Then these records are rolled back - and last autoinc value rolled back too?

<< 3.) If we uses transaction then posted (but not commited) records are
visible in by other users?
No. >>

Thanks.

<< 4.) What do you think about "non-blocking" ID generation (for the tables
are need to my owned ID) I describe now:
It doesn't really matter - transaction locking in ElevateDB is restrictive,
so other users/sessions cannot insert, update, or delete any rows in the
tables involved in a transaction.>>

Thanks.
This is meaning that I need to use transactions only in fast write, because that is lock the DB!

Hmmmm...

Sorry for these questions, but I'm thinking in "all operations are exist in an active transaction" world (like PG, IB/FB). The reads are also in transaction.

I thinking about special element handling: the Cached Update - like in these databases.

For example: I have 3 level (MD) relationship. The main level named Mail.
Mail is the head, it have subelements, named Mail Main Items.
But these Items also have subelements, named Mail Sub Items.

In PG/IB, I can start a (long) transaction, make these elements in the real tables (Insert/Post), and generators makes the real id-s for these records.
I have filters, when Mail Items changed in view, I can see only Mail Sub Items it have.

When the user finished with the editing, he/she can determine what to do:
- Save
- Cancel

If he/she Cancel, I simply close these tables, and rollback the transaction. Then the inserted records are vanished.

If he/she want to Save, he can save with Commit without any special thing.

That is working in these DBs.

But if EDB supports only "lock-based", "fast modify"-kind transactions, then I have two ways to make same thing:

a.)
I post these elements, but I have a special field, named "posted".
If this field False, the records are not valid. But: I need to use this field in all Query, view, etc, to Filter only the Valid records...

b.)
I need to use cached update (like ClientDataSet).
I need to make a temp table with same fields as in original, and same EDBTable, with same fields, calc fields, etc.
When the user want to save, I need to copy all data into real tables.
In this case the program needs to "previously" generated real IDs; or negative pseudo ID-s with a transcoder that convert these, and child ID-s into real ID-s!

I think this thing good?

How do you, or other developers working with EDB if you need insert/edit elements that have more than 2 child levels and need use to "Save All/Cancel All" logic?

Thank you and for any user who will share his/her experience!

dd

<< How to handle this? For example we insert more records to Table A, ID 3,
4, 5. But something is wrong, I must rollback it.

Then these records are rolled back - and last autoinc value rolled back
too? >>

Yes.

<< This is meaning that I need to use transactions only in fast write,
because that is lock the DB! >>

Yes, transactions should be used in situations where you want to reliably
write to multiple tables as a serialized unit of work, and not in situations
where the transaction has to wait for user or external input.

<< Sorry for these questions, but I'm thinking in "all operations are exist
in an active transaction" world (like PG, IB/FB). The reads are also in
transaction. >>

ElevateDB's transactions do not operate like Interbase or Firebird.

<< How do you, or other developers working with EDB if you need insert/edit
elements that have more than 2 child levels and need use to "Save All/Cancel
All" logic? >>

You can use cached updates with IDENTITY columns, but you have to be
prepared to deal with conflicts in the assigned numbers and to retry any
INSERT operations with NULL values instead.  However, it becomes really
messy once you start trying to propogate IDENTITY column values that are
being assigned during cached updates to child tables, and I wouldn't
recommend trying to use cached updates with IDENTITY columns in such a
scenario.

--
Tim Young
Elevate Software
www.elevatesoft.com