Simon Slavin wrote...
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 !
Thanks for helping Simon. Yes, there are a bunch of more tables and yes,
each of them have a LastUpdateDate and they all will be based on ProjID,
which is id in the LSOpenProjects table. This is only temporarery until we
close all the records in Server1.db and then, only one db, Server2.db will
be used.
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.
Yes, I understand. For Server1, no record will be added, so the last record
opened, in Server1, will continue to be the last one until it is closed.
For Server2, I will insert a dummy record to be 135001, which will guide the
rest of the new records that will be open in Server2. So the next
autoincrement will be 135002, etc. There last record for Server1 should be,
at most, at 134500, so there is a space of 500 empty records. We want to do
this so that there is an specific number that will guide the original record
for Server2 as well as for Server1.
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.
Yes, this will happen, and they know, but in a few months, Server1 will no
longer be, so we need to continue to update until we close that server.
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.
Hmmmm...
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.
I appreciate your input, Simon.
josé
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
