OK: I have completed my transfer of all my systems to EDB from DBISAM

Actually I am really happy ... a good number of systems & the whole process was really about adding functionality & ease of use the whole way ... very little about the process was onerous & the hard parts were hard because of extra functionality in EDB which my systems had to learn to use.

I am already using some really powerful server-based functionality with EDB for issues like centralized back-up & pruning & cleaning of the database.

Now I want to start on REPLICATION ... i.e. having remote users able to update their non-connected laptops from time to time to send and receive updates to the "main" database.

I have looked through the PUBLISH DATABASE, SAVE UPDATES and LOAD UPDATES topics and I am starting to understand them.

A few things are still not fully clear in my mind:

How exactly does EDB manage record contention or record clashes, i.e. User 1 changes row XX then User 2 changes row XX ... and you try to merge the resulting datasets.

I have created a fairly long SQL SCRIPT ... which does a micro-replication process. There are break-points in the script. I hope these open on other people's versions of EDBMgr! If they don't there are very clear comments and instructions in the text of the script.

You need to:
* open 2 EDBMgr windows.
* create a new session as per instructions ...
* in 1 window run the script with all the break-points in place.
* in the other window open the test table and edit / add records to create changes for the
  replication process to work with.

... I hope someone has time to play with it a bit. People who are not yet using EDB Replication might find it useful to play with in order to see the process in action.

The point is that it actually breaks, if you follow it through ... at a certain point (line 198 marked with --!!!!) the script generates an error relating to primary key conflicts.

I think that this conflict is probably happening because of the circular nature of the updates I have built into the script ... but I would like to know for sure exactly why it fails.

Adam,

<< OK: I have completed my transfer of all my systems to EDB from DBISAM
>>

Fantastic !

<< How exactly does EDB manage record contention or record clashes, i.e.
User 1 changes row XX then User 2 changes row XX ... and you try to merge
the resulting datasets. >>

You would use the LOAD UPDATE triggers to manage conflicts and errors during
the update loading process:

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

<< The point is that it actually breaks, if you follow it through ... at a
certain point (line 198 marked with --!!!!) the script generates an error
relating to primary key conflicts.

I think that this conflict is probably happening because of the circular
nature of the updates I have built into the script ... but I would like to
know for sure exactly why it fails. >>

Are you modifying the primary keys when making the manual changes when the
script is paused ?  If so, then yes, you will run into issues with doing so
with replication, and these will need to be removed by either going with
ranged identity columns, identify columns combined with site IDs for primary
keys, or extensive use of LOAD UPDATE triggers to handle any conflicts and
adjust row values accordingly as they are loaded.

--
Tim Young
Elevate Software
www.elevatesoft.com

Thank you Tim,

Firstly ... I am really impressed by the general functionality of the EDB update process. I have done simple tests editing 1 table in 3 databases and then reconciling/merging the changes & the process is pretty flawless.

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.

Note that user 3 has not touched record 100!

--

I have fully unique IDs on all my tables now & that is working fine, but I will often have situations similar to the above in my DB (i.e. Manager creates a record for some work to be done, then worker A edits this record, followed by worker B ... all in separate offices).

I try to keep records "atomistic" (i.e. most changes involve adding a new record to a "child" dataset) to avoid these types of conflict ... but I can't do it all the time.

Do I need to do all updates through 1 central point?

i.e. have a central server which acts to "clear" changes in all directions.

Yep, that's a problem!  You can solve it in one of two ways (well, probably
more, but 2 that I can think of):

1. Set ranges for your auto-generated IDs based on location.  I did this for
a while and the very first time that it gets messed up, you'll wonder why
you did it this way.  It's fine as long as those ranges stay there and
nothing else messes up the sequence.

2. Use GUIDs instead for your primary keys.  That's what I did and it was
beautiful.  No more worries at all.  Any record could be created from
anywhere without anything ever getting messed up.  Yeah, it's a big change
throughout your entire database, but in my opinion it's well worth the
effort.


David Cornelius
Cornelius Concepts

>>1. Set ranges for your auto-generated IDs based on location.  I did this for
>>a while and the very first time that it gets messed up, you'll wonder why
>>you did it this way.  It's fine as long as those ranges stay there and
>>nothing else messes up the sequence.

>>2. Use GUIDs instead for your primary keys.  That's what I did and it was
>>beautiful.  No more worries at all.  Any record could be created from
>>anywhere without anything ever getting messed up.  Yeah, it's a big change
>>throughout your entire database, but in my opinion it's well worth the
>>effort.

I am using integer-based ranges, not GUIDs, I would really like to avoid having to move to GUIDs as I use the concept of an "ID" (defined as an integer) through a hell of a lot of my application framework code !!

* It seems that the people who are serious about replication ALL use GUIDs ... so perhaps I have to bite the bullet?

* Perhaps I can develop a schema which uses GUIDs for replication & IDs in the application?

--

The thing is I have multiple users in multiple offices and more and more of them want to be able to be fully mobile and "cloud" based, I can foresee a situation where virtually every user of my database becomes a "node" needing to have a copy of the database locally to work with, and periodic updates to a central store.

This is the way things are going ... so I need to make sure my framework can handle it.

Is the collective opinion that I should go-GUID?

Adam


>Is the collective opinion that I should go-GUID?

You either need something that is collision proof or a mechanism to handle collisions. Autoincs are pretty much collision proof on a single access system but far from it when you go to multiple on-line / off-line access with several systems updating a central system in an asymmetric manner.

Unless you want to develop your own collision proof identifier (maybe a two part key say site plus autoinc) then GUIDs are the only thing I know of pretty much guaranteed to be unique.

Roy Lambert [Team Elevate]

Bite the bullet. Trust me. It's a huge switch--I used autoinc IDs
everywhere, too, but do it. BITE THE BULLET!

It'll save you many headaches down the road.


David Cornelius
Cornelius Concepts

Thanks Roy & David.

I have "big ranges" for my IDs (i.e. User1 = 1 - 1,000,000, User2 would then start at 1,000,001 - 2,000,000) & I pull a new "start ID" from my cloud server. I don't use the EDB AutoInc (which is great for single, centralized server situations) My "ranges" are working well, there are no collisions.

I think despite this I probably should "go GUID" ... but I am wondering whether I could keep IDs for the time being in the App. i.e. add a GUID field to every table, make it the primary key, keep it invisible to the user. Keep the ID, make it a UNIQUE constraint & keep using it in the DB.

The advantage of this is:
* No change to application code (lazy reason).
* Users do like having the IDs in the application ... they like a clean number they can refer to to uniquely identify any record. GUIDs are just horrid from this perspective. I know they work, but because they have no "order" at all & are huge & impossible to remember users really don't like having to refer to them.

Adam


I don't know about David but I'm not massively happy about having two unique indices on a table unless there's a very good reason for it.

Technically there's no reason why you shouldn't do what you suggest, however, I'd think long and hard about what users are using the unique number for before committing to retain it.

"No change to application code (lazy reason)." might be a good reason to leave it there as an interim measure so that you can make changes in an incremental manner but as soo as you can you want to be switching the applications dependence over to the GUID.

Hmmm. I wonder if it would be possible to decode the GUID and present it in a more friendly way?

Roy Lambert

Adam,

If your main goal is to just stick with integer so framework remains intact then you could do one of 2 things:
- guid is representable as 128bit integer so you could store it in multiple int values
- if multiple int keys is a problem you could try to roll your own unique like number

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.

Raul