Anyway, with the history database, I suppose modxDBsynchronize.exe is easier to write for someone fluent in MySQL. E.g.:
1) read the "last synch timestamp" info from a file (see last step)
2) for every modx table (defined in an array), select those with deletedon <> 0 and timestamp > last_synch_timestamp. For each obtained record, write in a temp file a line with the timestamp, followed by the query "DELETE...".
3) for every modx table (defined in an array), select those with editedon <> 0 and deletedon = 0 and timestamp > last_synch_timestamp. For each obtained record, write in the same temp file a line with the timestamp, followed by the query "UPDATE...".
4) for every modx table (defined in an array), select those with editedon == createdon and timestamp > last_synch_timestamp. For each obtained record, write in the same temp file a line with the timestamp, followed by the query "INSERT...".
5) sort the temp file contents (because every line starts with the timestamp), and for every line cut out the first 11 characters (timestamp and, say, a space);
6) rename the temp file with a .sql extension
7) write in a file the last timestamp info, so next time we know when to start from (see first step)
Well, something like that, I suppose. Hope someone will try (i know actually things are not that simple, but I hope someone will be good enough to solve eventual problems... I can think to character set issues, etc.)
Maybe a php script used as cgi could do the job with the "double-click’n’go" approach I would like.
I can see where your coming from here but not every table has ’editedon’, createdon’ etc. In fact only a few do.
You could do it by taking content types lets say for example documents from the site_contents table, take a timestamp and query the history database for all entries > than this timestamp. Then for each document id look in the target database, if the document doesn’t exist then insert it and after that update it, if it does exist then just update it. This should bring the target database up to the same level as the history database
The problem you’ll have is deletions, as Auditor doesn’t use delete triggers at the mo because it doesn’t need them, they could be added however to indicate a document has been deleted at this time, then the updates should work.
Actually this may be a case for adding delete triggers to Auditor 1.2 when I push it.
Like you say I’m sure a tool could be written to do this all automatically.
Use MODx, or the cat gets it!
Yes, this works for documents in the site contents table, but not for snippets in the site snippets table for instance.
If you only really need doc recovery then there is more info in the site content table about docs than in the other tables and you can be smarter with your scripts here.
Use MODx, or the cat gets it!
No, of course I need whole informations because I need perfect synch.
Thanks for the explanation, I’ll wait Auditor 1.2 for my thoughts to take place.
Can I ask you to post a message in this thread when you (or someone for you) make it ready?
PS: as we’re talking of enhancements of Auditor, with triggers on delete, maybe a future version could also be used to add a useful multiple undo/redo feature in MODx manager.
Yes, please post any thoughts you may have for Auditor on the Auditor support thread, it’s more focused there.
I’ve no probs pushing out a 1.2 with a few small bug fixes and delete triggers in the next few weeks, I’ll look at any other thoughts also.
Use MODx, or the cat gets it!
OK, I’ve released Auditor 1.2, this has delete triggers to allow recovery of data that has been deleted, also the history tables now contain a column named ’entityDeleted’ that is set to 1 when the item is deleted.
So, any database syncing tool can now simply query from a known start time across all the tables and apply updates and now deletions between this database and the one you are syncing to. The SQL shouldn’t be too hard here now as you dont need to query certain columns of some tables and different ones for others, all the history tables contain the ’timestamp’ and’ entityDeleted’ columns.
Use MODx, or the cat gets it!
Any updates on the topic?
Thanks...
Website: www.mercologia.com
MODX Revo Tutorials: www.modxperience.com
MODX Professional Partner
Sadly, not any.
But it’s good to see another user is interested on this, this helps to make the topic hotter and more visible than it is now.
