Pete,

Firstly identify the data sets that you need to move between the systems.
Don't think 'database' here. Think datasets. That is the actual
information that will move from one system to another. When you receive the
dataset and update the database, that's a whole different story. But start
with the data that will move between the systems. Build a mechanism to
recognize what has changed that has to be moved (ie. Maybe a table
containing a record of each data add, update and delete that needs to move
over to the target system, or something within the records themselves that
will allow you to identify the 'delta').

Data updates that move from one system to another have three states: Add,
Update, Delete. When you add a new record to another system, that's pretty
straightforward unless the dataset refers to any foreign tables that must
already exists for the add to succeed. Deletes are also pretty
straightforward. In order for the delete to synch to the other system, the
record that is being requested must already exist.

Updates, are a whole different ball game. The problem you have is that when
you update a record where your application is pretty much in control of the
target record (ie. You use some form of record locking to disable other
users from updating the record while your update is in progress), you can
control the state of the records. In a synch model, you have no control.
Updates WILL occur on records that make copies of the records in slave
systems outdated. Therefore you need something that will either:

a. Stop the records from being updated other than by one 'master'
system all the time, or
b. Seperately store the row/column data that is being updated, so that
the same changes could be applied to an entirely different record (this
isn't a foolproof solution either because the operator is making
subconscious field update decisions based on incorrect data, and may not
have updated the record if they could have seen what other users have
updated the record to be first),
b. Flag the date/time that a record was last updated so that you know
who updated the record and when and protect your update from blowing out
someone else's.

This is the biggest issue in synchronization. Its not so much about
technology, but simply the laws of time. You will need a strategy to deal
with this.

Now assuming you have a strategy, you normally would create a mechanism to
recognize the changes from the source system that have happened since the
last update, and to then capture the data associated with these changes and
move this over, along with instructions on what to do with these changes, to
the target system.

A perfect mechanism for doing this would be XML. Mainly because you have
the freedom of changing database structures, but a tagged XML style allows
your receiving system to parse the information coming and from it to
determine what to do, independent of the database structure that its going
into. Moving data with XML is also something where you can use technologies
such as Web Services to assist in this process if needed.

Will any pre-built components help you with all of this? Maybe. But having
built this stuff before, you end up customizing most of what you want
pertinent to the exact need you have for the data synch.

This is all VERY different to replication, which simply works at a record
level in the database. A synchronization solution needs to accommodate data
update contention and providing a user interface where someone can make a
human decision as to what to do when a data update conflict exists. We've
avoid much of this in the past by not allowing slave systems to update
master records, but only to add information to the records. Then there is
little or no conflict issues during synchronization.

How you do all of this is up to you, but that's why you wear the badge of
'programmer', right?

BTW, I never mentioned the word 'Firebird' in this entire post. Hence I
recognize that this is OFF-TOPIC and probably should be moved off the list.
Everyone who was going to tell me this, forgive me in advance but I was
hoping it would help the original poster. Peace.

Myles

===========================
Myles Wakeham
Director of Engineering
Tech Solutions Inc.
Scottsdale, Arizona USA
Phone (480) 451-7440
Web: www.techsol.org

> -----Original Message-----
> From: petesouthwest [mailto:petesouthwest@...]
> Sent: Friday, August 27, 2004 9:54 AM
> To: firebird-support@yahoogroups.com
> Subject: [firebird-support] Re: synchronize data database tables
>
> Thanks for the help and advice :)
>
> I notice that you mention replication again. The IBO components that
> I am using have a TIB_RPL and TIB_RPL_Meta, that are meant for
> replication but looking at the online help i thought it was saying
> that these were for replicating the database structure. I think you
> mentioned the briefcase model earlier that also sounded useful. Are
> they basically the same thing?
>
> My delphi app uses a firbird db to store data. In using my app the
> users add and amend records but not the same records. Due to the
> nature of the records it is impossible for my users to add/amend the
> same record. The users will have my app and its associated firebird
> db installed on their LAN at work and on their home machines. Thus my
> app needs to give the users the ability to easily keep both databases
> ie the one at work and the one at home, up-to-date.
>
> One of my concerns was that the size of the file to be transfered be
> kept as small as possible to allow for quick emailing or being
> transfered via floppy disk. If I understand replication correctly, I
> would have a third 'mobile' database that would be used to hold the
> changed data that needed to be transfered between home and work
> databases. Would this make the file size too large to be easily
> transfered? Or would this be the best way of doing it,
> notwithstanding the larger file size?
>
> Any advice on the best way to proceed would be gratefully received!
> Pete
>
>
>
>
> --- In firebird-support@yahoogroups.com, Andy Goodchild
> <goodieauk@y...> wrote:
> > Daniel Rail wrote:
> >
> > > Hi,
> > >
> > > At August 27, 2004, 05:36, petesouthwest wrote:
> > >
> > > Then: thanks Andy, I had quiet stupidly
> > > > overlooked the situation you describe,
> >
> > I did as well and learnt the hard way a long time ago in a former
> life
> > writing Paradox stuff, so you are not the only one.
> > And please do not be afraid to ask questions we all learn from each
> > other here, I certainly learn more than I help.
> >
> > > I think date check field is a very good idea. Also, you'd probably
> > > need the original export file to compare with the actual database
> > > record to see if there was a change since the last export. And, if
> > > there was a change, which field(s) has changed, and does those
> fields
> > > match one or more fields of those changed by the user off-site.
> If you
> > > have some changed fields that match(between the off-site user's
> data
> > > and the server's), you'll need to do some conflict resolution,
> > > basically which change overrides the other. And that might have
> to be
> > > up to the end-user to decide, by showing them when the server
> data was
> > > changed and what is the newest data.
> >
> > I agree with what Daniel says. And if it sounds like a lot of
> extra
> > work that you would rather not do take a look at the replication
> engines
> > out there as they will handle or help handle the conflict
> resolution.
> > Take a look too at IBLogManager as it might be able to help you.
> > If you have any more questions please ask.
> > Regards
> > Andy
>
>
>
>
>
> Yahoo! Groups Links
>
>
>
>