I'm trying to put using something like SVN to keep a live, and a testing site "in sync"
Here's the theory:
For all module - keep the module code in SVN, then you can see when changes are made, roll back, etc. Once the "testing site" it working well - you can simply svn update the live site - and it should work....
Great for modules - what about their setup data?
Here we get into the "problem" of "user data" and "module setup data"
In principle "user data" can be ignored - in the test site, it might just be some test data
In principle "module setup data" is important to copy accross from testing to the main site.
However - this is not "black and white" - what about (for instance) CCK data - it's "user data" but it is also "module data" - I dont want to re-set up all my CCK elements, or my panels or.....
So - here is my theory:
1/ take a copy of all the live data (using mysql dumping) into an svn repository
2/ check out the copy of the live data for the testing site.
3/ mysql read in the check'ed out data
so, at this point you have
an SVN repository (called MySite)
a check out of that repository (called Live)
a check out of that repository (called Test)
and all the sites have the same data in their tables (for now)...
4/ dump the Live site's data into the Live check-out, and svn commit it.
5/ dump the Test site's data into the Test check-out.
-- Now the scary bit --
svn update the Test site's data.
-- THIS WILL CAUSE CONFLICTS --
6/ Assuming no conflicts - mysql read in the updated data.
(7/ goto 4 - periodically)
Ok - so the principle here is simple - theoretically, svn will sort out conflicts between the data, and whether the data is "user" or "module", so long as the conflicts resolve, then data from the live site will be migrated to the testing site.
Once the testing site is working "just right" then - you can svn commit in the testing site, and svn checkout on the Live side.
This all sounds SOO good. But - the problem: CONFLICTS
So - some tables conflict because they just always will - for instance the cache, access logs, etc.
Here's a list of offenders so far:
accesslog|cache.*|sessions|watchdog|node_counter|history
Actually - this list can be automatically generated, from the tables that Conflict !!!!
But -- then I hit a snag:
variables
It seems that different modules are treating "variables" in different ways. I know variables are definatly "module data" - so they definatly MUST be checked into the svn, etc...
BUT - it seems some modules may be using variables in a "volitile" way....
This may end up being a proposal for Drupal 7, but it would be really great if there was "variables" and "volotile_variables"....
Anyway - how to solve this?
1/ I could split up the variables from the variables table (or any similar offending table), and have a file per entry (yuk), and then simple add the real offenging variables to the list above (automatic)
2/ I could write a script the stripped the offending variables (I like this approach, only it's a little "specific")
Any other ideas?
Here's my code for the "do it all" script. Of course, you need to set up your repo's and directories first (and make sure you can execute mysql etc) - but you'll see the basic idea here...
(Dont try this at home, unless you know what all this is about - you're bound to trash your site, and it's not a magic back-up and do everything solution!!!)
#!/bin/bash
live=MYSITE_web
test=MYSITE_test
bts='Tables_in_|accesslog|cache_page|cache|sessions|watchdog|node_counter|history'
for table in mysql $live -e 'show tables' | egrep -v $bts; do
#echo "Dumping $live/$table"
mysqldump --compact --opt -Q $live $table > /tmp/table.$$.sql
replace -s sites\/all sites\/test.greensocs.com -- /tmp/table.$$.sql
if !(diff -q /tmp/table.$$.sql $live/$table.sql > /dev/null) ; then mv -f /tmp/table.$$.sql $live/$table.sql; else rm /tmp/table.$$.sql; fi
done
svn commit $live -m "update"
for table in mysql $test -e 'show tables' | egrep -v $bts; do
#echo "Dumping $test/$table"
mysqldump --compact --opt -Q $test $table > /tmp/table.$$.sql
if !(diff -q /tmp/table.$$.sql $test/$table.sql > /dev/null) ; then mv -f /tmp/table.$$.sql $test/$table.sql; else rm /tmp/table.$$.sql; fi
done
svn up $test
for table in ls $test/*.sql | egrep -v $bts; do
status=svn status $table | awk '{print $1}'
## echo "$table $status"
if [ "$status" != "C" ] ; then mysql $test < $table; else echo "$table has conflicts, not copying"; fi
done