At 07:15 PM 12-08-02 +0000, you wrote:

>Hello,
>
>I'm writing a rather complex project management system (with
>Firebird/IBO) for a customer. This customer want's to "outsource"
>some of the planning, that is, give the software and a copy of their
>database to another company and then, when the company has finished
>their work, syncronize the changed database with their own "main"-
>database.
>
>The problem is the primary keys. Each table has a primary key of
>integer-type and get's its value from a table-specific generator. A
>lot of the tables also contain lookup-key values that reference other
>tables.
>
>I don't know how to implement the syncronization system, without
>risking key violations. What happens if the third-party company adds
>records to a table while the "main"-customer makes changes to the
>same table? Then there are two records with different data, but with
>the same primary key.
>
>Another problem, which is in my opinion more complicated, is if the
>third-party company creates a new record in one of the lookup tables,
>which is referenced by another record. If the "main"-customer also
>creates a new record in the same lookup table, there is not only a
>primary key violation, but also a "lookup-key-violation", if you see
>what I mean.
>
>Frankly I don't know how to solve this problem, and I hope I've
>managed to explain the problem clearly enough. Any help is very much
>appreciated.
>
>Should I create some kind of table locking which prevents the main
>customer from editing those parts of the database that are
>currently "out-sourced"?
>I have a changelog system which logs all inserts, updates and deletes
>in all the tables. Could I perhaps use this system and just overwrite
>any existing records added after the export date?

This is an architectural problem for the briefcase model that is generally
solved by a replication subsystem. You can "roll your own" or you could
take a look at IBO's one-way replication components. You can compile the
IB_RPL.dpr that is in the main directory of your IBO
installation. Although it is meant to be a demo, you can actually use it
as a setup tool for the replication tables. There is online help for the
project at http://www.ibobjects.com/iborpl.html which is also available as
a Winhelp file for registered subscribers.

If you have a requirement for the greater complexity of two-way replication
(which your description seems to imply), it is theoretically possible to
set up two one-way replication subsystems, one for the server and one for
the client. It's quite a lot more complicated...it might be preferable to
use the proprietary IBReplicator (http://www.replication.co.za), a
fully-fledged two-way replication subsystem for Firebird and InterBase,
that is set up via configuration options.


regards,
Helen Borrie (TeamIBO Support)

** Please don't email your support questions privately **
Ask on the list and everyone benefits
Don't forget the IB Objects online FAQ - link from any page at
www.ibobjects.com