tool to sync two hsqldb databases?

[jvsrvcs]

Seeking:  A free or commercial product / api / tool that can be embedded
into a Java app to sync two HSQLDB databases and handle complex rules

---
Summary:

We have a desktop application that uses the db on the back end to store
info and have some unique syncing problems.

The user has the ability to install the app, do some stuff that puts
data into the local db (call this Client A) and then sync to a central
server, which merely puts the db file on the server (puts the db file
into a column on production database)

The user then has the ability to go to another computer, install the
app, and if the user has an account and logs in, it will download the db
file from a server (where it was put by Client A in the step above)

The user may run either client in offline mode where data is changed at
any point, such that local changes do not get sync'd to the server (but
at some point in the future may get sync'd)

---
What we want to do is handle all cases where Client A (that has been in
offline mode for some time and has made many local changes) gets data
from a recent sync from Client B and handles everything correctly.

At first look this will require a lot of programming to do this, and we
would like to know if there is a tool or product out there that will do
what we need to do.

Is there a tool that can be used to sync between the two databases
(through using a central always on server) when only one app can be
active at any time and handle the offline scenarios?

---
Simply fetching the database files each time a client launches is not an
option, because of the "offline mode" where Client A may have some data
that was added or changed offline (at a later date than the last sync)
so if we get the db file from the server it will erase those changes.

We need to handle complex cases like

Client A: (online mode)
    launch and add some data
    sync the db file to the server
    close client A

Client B: (online mode)
    launch the app and get latest data file from the server
    add some data
    * rename an item
    sync the new data to the server

Client A: (offline mode)
        launch the app, can not sync from server because offline
        * rename the same item as above (so this is the latest rename)
        Now sync from the server

    Expected:  Do not want to get the item renamed as set from Client B,
but should be the rename from Client A, after the sync.

    For this reason we can not simply download the latest db file from
the server and use it.

    We have to do a merge of the db files but handle changes according
to a time stamp, and only merge changes that are the most recent changes.

    There will be collisions and conflicts that we have to resolve and
this looks like a complex programming problem.

If anyone has any idea on free or commercial products that can be
embedded into a Java app to sync two hsqldb databases and handle complex
rules, please
drop me a line.

We have a few ideas, but want to explore all options.


------------------------------------------------------------------------------
Come build with us! The BlackBerry® Developer Conference in SF, CA
is the only developer event you need to attend this year. Jumpstart your
developing skills, take BlackBerry mobile applications to market and stay
ahead of the curve. Join us from November 9-12, 2009. Register now!
http://p.sf.net/sfu/devconf
_______________________________________________
Hsqldb-user mailing list
Hsqldb-user@...
https://lists.sourceforge.net/lists/listinfo/hsqldb-user

[Fred Toussi-2]

Your requirements can be implemented using log tables fairly easily.
Such tables log the changes to the main tables of the database and are
populated by SQL triggers declared on the main tables. Version 1.9
supports SQL triggers.

When synching two databases, you can open both at the same time and
perform selects on the respective log tables and compare the changes.
Any change from one database that conflicts with changes on the other
database is flagged and the action is not copied to the other database.
Non-conflicting actions are copied. The log tables are cleared when
entries have been synched.

Your requirement seems to be "use the latest change to an item with a
given ID", which translates to a log table containing (id,
change_timestamp, table_name). The log table is updated by all update
statements on all critical tables. You select all the records from a
base timestamp from one database, insert them into a temp table in the
second database, join the temp table with the log table on the ID,
resulting in a list of conflicting row ids. You can then get the lists
for the latest versions of these rows from one database and update the
other. The new SQL MERGE command can also help here.

Fred

On Sat, 26 Sep 2009 17:18 -0500, "J.V." <jvsrvcs@...> wrote:

>
> Seeking:  A free or commercial product / api / tool that can be embedded
> into a Java app to sync two HSQLDB databases and handle complex rules
>
> ---
> Summary:
>
> We have a desktop application that uses the db on the back end to store
> info and have some unique syncing problems.
>
> The user has the ability to install the app, do some stuff that puts
> data into the local db (call this Client A) and then sync to a central
> server, which merely puts the db file on the server (puts the db file
> into a column on production database)
>
> The user then has the ability to go to another computer, install the
> app, and if the user has an account and logs in, it will download the db
> file from a server (where it was put by Client A in the step above)
>
> The user may run either client in offline mode where data is changed at
> any point, such that local changes do not get sync'd to the server (but
> at some point in the future may get sync'd)
>
> ---
> What we want to do is handle all cases where Client A (that has been in
> offline mode for some time and has made many local changes) gets data
> from a recent sync from Client B and handles everything correctly.
>
> At first look this will require a lot of programming to do this, and we
> would like to know if there is a tool or product out there that will do
> what we need to do.
>
> Is there a tool that can be used to sync between the two databases
> (through using a central always on server) when only one app can be
> active at any time and handle the offline scenarios?
>
> ---
> Simply fetching the database files each time a client launches is not an
> option, because of the "offline mode" where Client A may have some data
> that was added or changed offline (at a later date than the last sync)
> so if we get the db file from the server it will erase those changes.
>
> We need to handle complex cases like
>
> Client A: (online mode)
>     launch and add some data
>     sync the db file to the server
>     close client A
>
> Client B: (online mode)
>     launch the app and get latest data file from the server
>     add some data
>     * rename an item
>     sync the new data to the server
>
> Client A: (offline mode)
>         launch the app, can not sync from server because offline
>         * rename the same item as above (so this is the latest rename)
>         Now sync from the server
>
>     Expected:  Do not want to get the item renamed as set from Client B,
> but should be the rename from Client A, after the sync.
>
>     For this reason we can not simply download the latest db file from
> the server and use it.
>
>     We have to do a merge of the db files but handle changes according
> to a time stamp, and only merge changes that are the most recent changes.
>
>     There will be collisions and conflicts that we have to resolve and
> this looks like a complex programming problem.
>
> If anyone has any idea on free or commercial products that can be
> embedded into a Java app to sync two hsqldb databases and handle complex
> rules, please
> drop me a line.
>
> We have a few ideas, but want to explore all options.
>
>
> ------------------------------------------------------------------------------
> Come build with us! The BlackBerry® Developer Conference in SF, CA
> is the only developer event you need to attend this year. Jumpstart your
> developing skills, take BlackBerry mobile applications to market and stay
> ahead of the curve. Join us from November 9-12, 2009. Register
> now!
> http://p.sf.net/sfu/devconf
> _______________________________________________
> Hsqldb-user mailing list
> Hsqldb-user@...
> https://lists.sourceforge.net/lists/listinfo/hsqldb-user

------------------------------------------------------------------------------
Come build with us! The BlackBerry® Developer Conference in SF, CA
is the only developer event you need to attend this year. Jumpstart your
developing skills, take BlackBerry mobile applications to market and stay
ahead of the curve. Join us from November 9-12, 2009. Register now!
http://p.sf.net/sfu/devconf
_______________________________________________
Hsqldb-user mailing list
Hsqldb-user@...
https://lists.sourceforge.net/lists/listinfo/hsqldb-user