Raul

>GUID is basically unique hw info + timestamp so something like unix timestamp gets you almost there (right now for example is 1328864280) - unfortunately it's unique only to a second so collisions are still possible if 2 users happen to create records on same exact second. You could add some random element or move up to 64bit number in which case you have another 32bits you can use to ensure uniqeness (say site assigned number + random number ni high order 32bits + unix timestamp is last 32bits) but you need to be very sure it always generates unique which of course guid resolves.

Using timestamps of any type unless you reference back to a highly accurate clock are a bit of a nightmare. I do use a timestamp myself for creating in-memory tables but since they are isolated on a machine that's fine. However, consider:

1. Is the PC's clock running accurately
2. Has the PC's clock been reset

and you have a problem.

Roy Lambert

Raul

I have thought of relatively straightforward "GUID alternatives", these would usually be A Unique UserID + timestamp represented as integer ... with adequate accuracy on the timestamp to make it almost unique ... then throw in an other number to _really_ try to guarantee its unique.

In the end you have something which is almost as ugly as the GUID anyway!

Generally virtually all my tables have a DateCreated field (TIMESTAMP) which at least gives a clear "ordinality" to all data ...

--

I think I will probably go with Roy's suggestion, i.e. put a GUID in place & use it for replication, then  phase out the ID over time.

Actually for unique keys it's not really an issue  (unless you also use the values to know order of creation).

Goal is to have no collisions so whether those are due to inaccurate clock or multiple changes happening at the exact same time (with clocks synched) the end result is same - you need a way to make unique values which is bit tricky.

GUID does solve the problems but is a major PITA to read - especially when one looks at sql output direct to troubleshoot. Then again the pain is worth it.

Raul

<<Using timestamps of any type unless you reference back to a highly accurate clock are a bit of a nightmare. I do use a timestamp myself for creating in-memory tables but since they are isolated on a machine that's fine. However, consider:

1. Is the PC's clock running accurately
2. Has the PC's clock been reset

and you have a problem.
>>

>>1. Is the PC's clock running accurately
>>2. Has the PC's clock been reset

Hum yes ... I had a user enter 20,000 records into a table with her laptop time set to "1911" ... you're right Timestamps are only a "sort-of" solution!

--

I've had another look at my own script & realised that I can work around the error.

I was trying something too complex with the script:

User1 Updates User2 Updates User3 Updates User1.

... this fails.

However:

User1 Updates User2 Updates User3 Updates User2 Updates User1

... this _seems_ to always work.

that being the case (i.e. 1 central node for replication, with others working off it) I can easily make it work with my UID integers, rather than GUIDs.

Sorry--I've been out of touch the last week or so.

It sounds like you already have a good idea of the direction, but I just
wanted to respond to the ID vs GUID concept. I, too, had big ranges--that
wasn't the problem.  My problem was that my ranges got messed up with
another user (in one case) and reset to a default (in another case) and I
could never get the ID's squared away again. Now since you're pulling your
"start ID" from the server, you might not have that problem.

Anyway, about the two primary keys?  No, I wouldn't do that.  The GUID
should become your new primary key.  Whether or not you keep the other ID is
irrelevant.  In my case, the customer very much wanted to have a number to
refer to their Purchase Orders, Invoices, etc.  In fact, that number was
already separate from the auto-increment ID.  I just put a unique constraint
on the number and it allows the customer to see the number, but keep it
unique.  But the GUID is the king for the primary key.

Feel free to keep and show the IDs, but never show the GUIDs--your users
will freak out!

David Cornelius
Cornelius Concepts

Adam,

<< However, the following seems to generate a problem:

RecordID 100 inserted by user 1.
Same record LOADED to user 2, user 2 edits this record.
RecordID 101 inserted by user 3.

Update from User 2 passed to User 3 which contains "update" of Record 100
causes an error. >>

This doesn't make any sense to me.  If User 2 has the same tables published
as all other users, then any load of the record ID 100 from User 1 will
*also* generate a corresponding entry in User 2's update file that is passed
on to User 3, like this:

1) Record ID 100 is inserted by User 1.  User 1 saves updates and sends them
to User 2.
2) User 2 loads the User 1 updates, and then edits Record ID 100.  User 2
then saves the updates and sends them to User 3.  This update file will
contain 2 entries, one for the record ID 100 insert and one for the record
ID 100 update.
3) User 3 inserts record ID 101.  User 3 then loads the updates from User 2,
and should then have a table with 2 records - 100 and 101, with 100
reflecting the edits of user 2.

In general, EDB simply will not touch the same update (insert, update, or
delete) twice, so as long as the published table IDs are all unique, this
process should work fine.

--
Tim Young
Elevate Software
www.elevatesoft.com

>>In general, EDB simply will not touch the same update (insert, update, or
>>delete) twice, so as long as the published table IDs are all unique, this
>>process should work fine.

I have got this working now Tim, but if you have time have a look at the script I posted at the start of this thread ... and follow the instructions laid out in the comments to create conflicting records. Having a "circular" update process (where updates propagate around all users) rather than a "star-shaped" update process (where all updates go through a single central server) did seem to create some conflicts. However, I am using "star-shaped" now & it seems to work fine ... I'm really happy with it actually.

Adam

Adam,

<< I have got this working now Tim, but if you have time have a look at the
script I posted at the start of this thread ... and follow the instructions
laid out in the comments to create conflicting records. Having a "circular"
update process (where updates propagate around all users) rather than a
"star-shaped" update process (where all updates go through a single central
server) did seem to create some conflicts. However, I am using "star-shaped"
now & it seems to work fine ... I'm really happy with it actually. >>

Did you re-visit this to make sure that you didn't have any issues with the
published table IDs ?  It certainly is possible to have conflicts with a
circular update process if update files are loaded "out of sequence", but
there's really nothing that can be done about this in EDB.  It's core
requirement for replication is that update files are loaded in the order in
which they are created.  If that requirement isn't met, then the incoming
updates simply won't reflect the original transaction order and you'll see
conflicts.

Thanks,

--
Tim Young
Elevate Software
www.elevatesoft.com