Hi,

I have already asked this question on multiple forums, but nobody seems to know an answer. So lets see what people on a db forum think of it

I'm working on an application (C++ Qt4) where the client side of the program has a local database (SQLite) and the server has a database (MySQL). Data is send from the local database to the remote database on several occasions. I have no control over the client side and there will be many thousands of clients.

Suppose somebody downloads my program which is version 1. My version 1 database has one table person, with the fields firstname and lastname. A couple of months later I decide the person table needs an additional row, namely email. So I update the table on the server (no problem here I guess) and I make a version 1.1 of my client program. I will be using IcePatch2 from ZeroC (http://www.zeroc.com) to update the files of the program on the client. Or are there any other options available? But how do I update the SQLite database on the client? Because the client already has a table person with (hopefully) a lot of data in it. So the upgrade to version 1.1 has got to go smoothly without the loss of any data. Are there any programs available that handle this kind of problem? Or how have you solved it? All help is welcome. Thanks.

Kind regards,

Nick

that's not a database question, that's an application design and programming question

dbforums.com is a primarily a database forum, so you may not get the answers you're looking for here, either

You'll need to write a script as part of you deployment that makes the necessary database modifications. You should post your question in the forum specific to the database engine you are using.

No control? You can't have the client software check your server for updates?

Some thoughts:

Your installer should, as part of the installation process, offer to update any existing SQLLite data stores to the latest schema. Just stash a version number in a table. Make sure there's no way the user can get stuck with a half-upgraded database!

Qt can be scripted. You can vastly speed up your UI design time by only coding the most critical parts in C++ and leaving much of it in the scripting language. If you only change the scripting, you don't even need to restart the main program, which helps with the next thought:

Don't allow old clients to proliferate. If you can, do automatic and mandatory updates. If you're just downloading a few new QtScript files when you connect to the server, the user won't even care about the updates. Do make sure that updates are done in a secure fashion; take the time to get a proper SSL certificate.

Is the SQL Lite database embedded in your code or is it standalone? if it's part of your package i would imagine you supplied it as embedded. If that is the case then part of your first run of updated code should be to check version of the database compared to newest version, and if there are differences perform your SQL manipulation to it before allowing the program to continue.

Nick

I did something loosely similar a couple of years ago but for far fewer clients. Assuming you have connection details for each client you would need a program to periodically log onto each clients server and ...

1. Check what version of the software they're using - if it's an old version then run scripts on the client db to upgrade their database to the current version. You'll want a series of scripts to update from version 1.1 to 1.2, 1.2 to 1.3 etc and run each in turn until the user is up to date. It would be possible to use this method to initialise a new clients database assuming they log into your system and provide their connection details when they sign up.
   
2. If the client db is up to date then run a script to extract all the new data from each table since you last extracted the data. Obviously each table would need some kind of time stamp. I didn't extract the data as "data" but instead created a script of calls to sprocs that would enter the data into the central database record at a time. This script was then run on the central database. The stored procedures on the central server would update each central table with the clients new data.
   • if the record was already there then it ignored it
   • if it was new it inserted it
   • if it had changed then it just updated the record
3. If anything failed then this would be remembered and the system would attempt a reload in the future. You could also automatically email the client to say if there's a failure. If it all works ok then we'd remember where we were up to for this client - I'd use the time of when you started extracting (ie not finished).

Note:

• This method does not allow data to be deleted though.
• You have to be careful what the user account can do on your server - I'd suggest just allowing them to run these sprocs and nothing else. It may also be worth reading up on sql injection attacks.
• Remember that client databases will often be down so be prepared to retry and report on clients that are no longer accessible.

You'd want to report on repeating failures, max lag time, number of clients, current heartbeat of the program. The method used wasn't too difficult to implement and handled around 50 tables. We split the downloads by lient and by table but this would probably add unwanted complexity.

Hope it helps - please ignore if not.

Mike

EDIT: small change