Synch local-remote changes... with the click of a button?

I have this problem: whenever I made a change in the database via the MODx manager, working on local, it needs to be replicated on remote, and it is really a lot of tedious work. You have to remember or note down every single change made, doing the same actions two times, locally and remotely.
Filesystem synchronization is not a problem, as I already use DSynchronize.

Yes, we could backup the database and import it, but it’s not very convenient as a method.

Solution: I’d like the manager to take track of every single action I perform in it. That is, it should log somewhere (e.g. in sql files) the same queries it does on db, whenever they are INSERT, UPDATE, DELETE ones.

So, MODx is freshly installed both on local and remote, I configure remote data on local side and it creates a new log file; I make nodes, add snippets, change some node titles, change some chunks, etc. , with sql queries reflecting whatever I do in manager. When I’m done with changes, and I want the remote site aligned with the local one, I click a button like "please do it for me: local -> remote", and it sends the queries to the remote server. It could also work the other way, i.e. if I do changes on remote side and I want them reflected on local, I would press the "please do it for me: remote -> local" button. After synch, the file is closed and another one is opened and updated with log of my actions, etc.

This way, I could avoid using the remote manager, at least as much as I already avoid manually updating the filesystem by using DSynchronize (i.e. I just need to press a button, which can be considered a tolerable amount of tedious work).

Is that possible? Is there some hope someone will do it? Could it be a manager module?

If not, let me know the ways you adopt to take local and remote copies synchronized.

Quote from: insert_nick at Sep 25, 2008, 09:32 AM

Solution: I’d like the manager to take track of every single action I perform in it. That is, it should log somewhere (e.g. in sql files) the same queries it does on db, whenever they are INSERT, UPDATE, DELETE ones.

This is the trick.. it would involve a lot of core hacking and hacking any plugins and modules you have to accomplish logging every SQL command. If you want the databases to be exactly in sync, have you thought about setting up replication, with your remote server as a slave to the local one?
Note that the system design of Revolution will allow sophisticated content/data migration tools to be created.

Quote from: netProphET at Sep 25, 2008, 10:05 AM

If you want the databases to be exactly in sync, have you thought about setting up replication, with your remote server as a slave to the local one?

No, I haven’t, thanks for suggestion. I’ve never done that but will look if it fits my needs.

Quote from: netProphET at Sep 25, 2008, 10:05 AM

Note that the system design of Revolution will allow sophisticated content/data migration tools to be created.

Very well.
Waiting eagerly for the first stable release of it.

Aside from pure replication and/or logging the SQL commands themselves, your idea for a module that allows another database to be synced to/from is a pretty good idea, and should be possible, if it doesn’t exist already. I would imagine that making it generic enough for public release would be hard, with all the idiosyncracies of mysql versions, permissions etc. The core idea is fairly simple though.
Anyone ?

I agree the pure SQL commands was a rough bad idea.
What if we would log directly the MODX commands required to insert/update/delete? I mean, I think MODx core uses a database abstraction layer. When I do something in manager, I think one of the very last things it does is firing a command; what if we check for success, and in case we write that command down in a file?
Then, on the other end, something like an eval of that file would perform the same actions.

If possible, if someone can suggest how to quickly hack the core in order to achieve such result, someone who knows where the commands are fired, I’d like to do that because it would effectively boost my work. I don’t care for a module or fancy things, I can live with just a file I get at the end of the work day, created by my local manager, and a rough way to commit it to the remote installation, e.g. running a little and dirty and unsecure script that I would remove from the server every time after synch.

Maybe the Auditor module could do this at a pinch, it allows for the copying of all db updates/inserts to a separate history database using MYSQL triggers. you could snapshot a point in time and say, ’take all these rows from these tables at this time and update/insert into this database’ . I’ve already thought of adding snapshotting to Auditor but more for recovery/rollback purposes rather than syncing but it probably could be done.

Any solution you come up with at a higher level than this will be intrusive to MODx code and prone to fail if people update the db outside of MODx. Hacking the core to do this will be a non-trivial task. It doesn’t however help with file changes external to the db.

Have a look at Auditor and see if could be adapted for your needs.

Thank you very much! I didn’t know the existence of your fantastic resource.
Yes, it’s pretty near to what I need; it would be perfect, having a little executable (let me call it modxDBsynchronize.exe now) querying Auditor’s history database, that should:

1) read from a file a "last timestamp" info (see last step);

2) write in a sql file a sequence of queries, based on the data found in Auditor’s history db, limited to records related to actions taken AFTER the "last timestamp";

3) write in a file the "last timestamp" info (see first step)

...that would produce an "automatic version of me" replicating on the remote database all the actions made locally, in the very same order, just by submitting the (small and clean) produced sql file to phpmyadmin.


So, the "real me" should:

1) turn on PC;

2) load MODx manager locally and start working in manager and with files;

3) time to stop working: load DSynchronize.exe and click the Synchronize button in order to synch the remote filesystem with the local one;

4) double-click modxDBsynchronize.exe (or whatever its name will be) to generate a new .sql file (or, better, a .gz one) with just the actions taken since the last execution;

5) send the .sql queries to the remote db (e.g. via phpmyadmin)

6) turn off PC, happy.

Good stuff, I was sure people would think of other uses for the history database other than the one I did, i.e versioning/auditing, this seems like a good candidate.

While we’re dreaming, let’s have a tool that will produce a diff file directly from the local database of just those changes made since the last synch., and then modify MySQL to accept that diff file and implement the changes.

Quote from: BobRay at Sep 26, 2008, 01:13 AM

While we’re dreaming, let’s have a tool that will produce a diff file directly from the local database of just those changes made since the last synch., and then modify MySQL to accept that diff file and implement the changes.

May this perl script be a starting point for someone?

http://sourceforge.net/projects/diffsqltables/