|
|
Posted by Alvaro G. Vicario on 07/26/06 19:16
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).
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
Its pitfalls are pretty obvious:
1) It will take years to execute (128Kbps, dude)
2) During the update process, the extranet is useless
3) If my app crashes... well, go figure
My second attempt would be:
1) Get primary keys from INET_DB
2) Get records from LOCAL_DB
3) For each record:
3.1) If PK exists in INET_DB: Update record in INET_DB
3.2) Else: Insert record into INET_DB
4) Remove records from INET_DB where PK no longer exists in LOCAL_DB
Now, will someone stop me before I reinvent the square wheel? I'm not sure
this is yet the right approach. I’ve been googling in search of an
algorithm but I could only find solutions for very specific situations. I’d
appreciate a lot any hint or link.
--
-+ http://alvaro.es - Álvaro G. Vicario - Burgos, Spain
++ Mi sitio sobre programación web: http://bits.demogracia.com
+- Mi web de humor con rayos UVA: http://www.demogracia.com
--
Navigation:
[Reply to this message]
|