One-way database sync to MySQL

Question

I have an VFP based application with a directory full of DBFs. I use ODBC in .NET to connect and perform transactions on this database. I want to mirror this data to mySQL running on my webhost.

Notes:

• This will be a one-way mirror only. VFP to mySQL
• Only inserts and updates must be supported. Deletes don't matter
• Not all tables are required. In fact, I would prefer to use a defined SELECT statement to only mirror psuedo-views of the necessary data
• I do not have the luxury of a "timemodified" stamp on any VFP records.
• I don't have a ton of data records (maybe a few thousand total) nor do I have a ton of concurrent users on the mySQL side, want to be as efficient as possible though.

Proposed Strategy for Inserts (doesn't seem that bad...):

• Build temp table in mySQL, insert all primary keys of the VFP table/view I want to mirror
• Run "SELECT primaryKey from tempTable not in (SELECT primaryKey from mirroredTable)" on mySQL side to identify missing records
• Generate and run the necessary INSERT sql for those records
• Blow away the temp table

Proposed Strategy for Updates (seems really heavyweight, probably breaks open queries on mySQL dropped table):

• Build temp table in mySQL and insert ALL records from VFP table/view I want to mirror
• Drop existing mySQL table
• Alter tempTable name to new table name

These are just the first strategies that come to mind, I'm sure there are more effective ways of doing it (especially the update side).

I'm looking for some alternate strategies here. Any brilliant ideas?

Answer 1

It sounds like you're going for something small, but you might try glancing at some replication design patterns. Microsoft has documented some data replication patterns here and that is a good starting point. My suggestion is to check out the simple Move Copy of Data pattern.

Answer 2

Are your VFP tables in a VFP database (DBC)? If so, you should be able to use triggers on that database to set up the information about what data needs to updated in MySQL.