We want to do all our site development and QA on a separate development system. Once we have a new version we are happy with, we need a way to synchronize the live system (where our users are) with changes we have made on the development systems. Obviously it's no bid deal to copy modified PHP files and the like, but the db contents are a different matter, because they combine structure, our content, and user-supplied content (posts, attachments, etc.), and tables are joined using sequential key values (which makes them highly sensitive to row creation order, etc.).

I can see two ways to do this:

1. For each set of changes, write a script to apply all the changes to the live site's db as SQL UPDATE and INSERT statements. Copy the files over, then run the script. This is like a do-it-yourself version of the standard upgrade process. The advantages are that the join key values will get fixed up automatically, and the scripts can be tested thoroughly on a copy of the live site before deployment. The disadvantage is that writing scripts is hard work compared to using Xoops' tools.

2. Produce a generic db synchronizer. This would analyse the differences between the two systems and apply changes from one to the other. So the process would be to copy over new files and then run the script to synch the dbs. The challenge here is keeping all the id keys matched up between different tables. The advantage is that we only need to write this script once, and we can use Xoops' tools to make most of the changes.

Is anyone here doing anything like this? If so how?

Even if you are not in this situation, comments and suggestions would be most welcome. In particular, is there a clean separation between the tables in the db that are modified by user activity and the tables that control the site's structure, that would make the more structural changes easier to apply than I fear?