Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: pass (athena.apache.org: domain of jvsrvcs@gmail.com designates
 209.85.211.185 as permitted sender)
DomainKey-Signature: a=rsa-sha1; c=nofws;
        d=gmail.com; s=gamma;
        h=message-id:date:from:user-agent:mime-version:to:subject
         :content-type:content-transfer-encoding;
        b=wXMwcmSP1TGv0GO6mANK2AD+v8Di3XQubDQ9tfJW5Ny/Zy9xuPCGx6woDsM9Vkvs93
         Edt6FlyzVZc2kvOHc498FslzkMzEB8OnalKLMkP4ZSm8Okns+surWSruGSNskQcESkhw
         zQ8I6eTXH3sRbFDYHFCEg6ZnVpINbueAAWTFk=
Message-ID: <4ABE923C.80306@gmail.com>
Date: Sat, 26 Sep 2009 17:14:20 -0500
From: "J.V." <jvsrvcs@gmail.com>
User-Agent: Thunderbird 2.0.0.23 (Windows/20090812)
MIME-Version: 1.0
To: derby-user@db.apache.org
Subject: tool to sync to derby databases?
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit


Seeking:  A free or commercial product / api / tool that can be embedded
into a Java app to sync two derby 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.