|
|
Posted by Colin McKinnon on 07/26/06 21:11
Alvaro G. Vicario wrote:
> I have a database called LOCAL_DB in a local computer. This DB is managed
> by third party software so I have no control over it. I have another
> database called INET_DB in an Internet server. I design and maintain this
> one (it's an extranet written in PHP).
>
There's a thing on phpclassess which claims to replicate MySQL schemas (you
didn't say what DBMS) but it doesn't understand about indices and doesn't
replicate data.
> I need to write an app that dumps certain fields and tables from LOCAL_DB
> into INET_DB every night, using a DSL connection, so all changes made in
> the office during workday are available at the extranet the following day.
> Both DBMS are different so I’m accessing data through ODBC drivers.
>
> The first simple bold algorithm I can think of is:
>
> 1) Empty INET_DB
> 2) Get records from LOCAL_DB
> 3) For each record : Insert record into INET_DB
>
Assuming we're talking MySQL and you are just publishing the data, then the
bestway to do it would be to slave the remote DB from the local - but that
asssumes you can reconfigure the remote DB and connect across the MySQL
port.
An alternative approach would be to run rsync - which is supposed to just
update the bits of a large file which have changed. But you'd need to to
shutdown the DBMS while it ran.
Failing that why not just shutdown the DBMS and transfer the files across -
it'll probably be a lot quicker than rebuilding the table one row at a
time.
The best solution though would be a custom script which copies over any
changes however you need to make sure that:
1) you never delete data (well you can - you just mark it as inactive then
run a housekeeping job to delete things which have been inactive for, say 2
successful update cycles).
2) you need a timestamp and primary key field on every table.
(BTW - why bother to check if a record exists before you try and insert it -
if its already there and you've got a primary key, the DBM<S will tell you
at the time of insertion).
HTH
C.
Navigation:
[Reply to this message]
|