On 19 Jun 2014, at 3:55pm, jose isaias cabrera <cabr...@wrc.xerox.com> wrote:
> These servers will be in two different servers and in two different parts of > the world, so network access will be very slow. What I am thinking in doing > is to copy the data on Server1 to Server2 and set the starting id at a higher > point than the last id on Server1. For example, Server1 highest project id > is 134000, so, I would set the start id for Server2 to 135000. The Server two > will continue to add new projects, so the next project id will be 135001 and > so forth. Is your whole database really just in a single table ? If not, you have to figure out how to spot related records in other tables. Once you've done that you need to have a 'LastUpdateDate' column on every table. And then you have to worry about spotting rows which have been deleted. Since the row has been deleted, there are no 'LastUpdateDate' values which let you spot it ! Synchronising two different databases is ridiculously difficult to get right. Especially when both of them continue to be modified and when you continue to make use of AUTOINCREMENT columns to number new rows. There is no good general solution. There's not even really a good book on approaches to the problem. >From the information you posted I would recommend that you keep the two >database files separate and explain to your users when to use one and when to >use the other. You can have two different apps, one of which uses the 'old >server' data, and the other accesses the 'new server' data. Or you can have >one app which can flip between database files. If this is at all practical in >your business it will make things much simpler and your users will understand >how their system works far better. However if you have a reason to need both sets of data in the same database, then consider what would happen if instead of introducing a 'LastUpdateDate' field everywhere and the clever programming you'd need to do to use it correctly, do the following: * Create a new table called 'SQLCommandLog' in a new attached database. * Table has the usual AUTOINCREMENT rowid and one text column called 'command'. * All commands which might change the data (INSERT/UPDATE/DELETE) get saved in the table. * To update another database to reflect the changes to this one, simply issue those commands, then empty the table again ready for new commands. That way you don't have to copy a big database file of your data to the other server, you just copy a small database of SQL commands. There are the usual problems with choosing different AUTOINCREMENT row numbers on different servers but this system may be simpler to implement than anything clever with deep understanding of your business logic. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
