Subversion on Drupal

[Andy Canfield <an...@pimco.mobi>]

My goal in learning Subversion was to put our web site under version 
control. Now I have my doubts as to whether Subversion can handle it.

The web site uses Drupal. And Drupal has the characteristic that much of 
the site is contained in a MySQL database. For example, if I install a 
module and set it up, the module is a disk file, but the configuration 
of that module is in the database. If I make a change, part of the 
change may be in a PHP code file on disk, part may be in the database. 
The database contains both user data and configuration data, intermingled.

I could get Subversion to work. I would have a pre-commit script to back 
up the database to a disk file in the working copy. I would have a 
post-checkout script to reload the database from the disk file. Along 
with svn commit and svn checkout, this would give us the ability to roll 
back to any earlier version.

What I can not imagine is how to get more than one person to be able to 
work on the site. Yes, Subversion would be able to merge changes to the 
disk files. But I don't see how Subversion can handle merging changes to 
the database. The MySQL database is text; perhaps someone here as 
experience with that. Can MySQL backup files be merged?

Re: Subversion on Drupal

[Geoff Hoffman <gh...@cardinalpath.com>]

On Thu, Aug 11, 2011 at 7:56 PM, Andy Canfield <an...@pimco.mobi>wrote:

> My goal in learning Subversion was to put our web site under version
> control. Now I have my doubts as to whether Subversion can handle it.
>
> The web site uses Drupal. And Drupal has the characteristic that much of
> the site is contained in a MySQL database. For example, if I install a
> module and set it up, the module is a disk file, but the configuration of
> that module is in the database. If I make a change, part of the change may
> be in a PHP code file on disk, part may be in the database. The database
> contains both user data and configuration data, intermingled.
>
> I could get Subversion to work. I would have a pre-commit script to back up
> the database to a disk file in the working copy. I would have a
> post-checkout script to reload the database from the disk file. Along with
> svn commit and svn checkout, this would give us the ability to roll back to
> any earlier version.
>
> What I can not imagine is how to get more than one person to be able to
> work on the site. Yes, Subversion would be able to merge changes to the disk
> files. But I don't see how Subversion can handle merging changes to the
> database. The MySQL database is text; perhaps someone here as experience
> with that. Can MySQL backup files be merged?
>
>

You'll need to analyze your app closely do it right, but it's absolutely
possible.

It's a daunting task for Drupal, WordPress, Joomla or any other CMS-driven
site and everyone who manages these type of sites deals with the issue
differently.

Subversion can handle whatever you svn commit. That's not the problem at
all. The filesystem files that make up your Drupal install are easy to
manage. You'll need to use svn:ignore on the uploads directory, cache, logs,
etc., but that's the easy part.

The tricky part is managing database schema under version control, and there
are myriad ways to output your db schema to a text file or files, try
phpMyAdmin for starters.

You have to plan your version control system to account for and
differentiate between dynamic/user-generated content in the database that it
needs to ignore, versus that which plays a critical role in the ui such as
dropdown items in forms.

You don't want your client's website content in your version control system
if you can avoid it, and with one well-written backup script you can achieve
it.

It goes without saying that you'll need at least one VM or additional
hosting account, virtual host on your server or other technique to develop
and/or stage your planned changes, too.

Re: Subversion on Drupal

[Andy Canfield <an...@pimco.mobi>]

On 08/12/2011 10:48 AM, Ryan Schmidt wrote:
> On Aug 11, 2011, at 21:56, Andy Canfield wrote:
>
>> My goal in learning Subversion was to put our web site under version control. Now I have my doubts as to whether Subversion can handle it.
>>
>> The web site uses Drupal. And Drupal has the characteristic that much of the site is contained in a MySQL database. For example, if I install a module and set it up, the module is a disk file, but the configuration of that module is in the database. If I make a change, part of the change may be in a PHP code file on disk, part may be in the database. The database contains both user data and configuration data, intermingled.
>>
>> I could get Subversion to work. I would have a pre-commit script to back up the database to a disk file in the working copy.
> Not possible, because the working copy is on the client computer, whereas the pre-commit hook (and all hooks) run on the server computer. Unless you're saying you're working with a shared server-side working copy, in which case that's another thing I'd recommend not doing.
My poor expression. It's not on a hook. I mean that when Charlie wants 
to do a checkout, Charlie must do this:
     [1] svn checkout ...
     [2] cd repository
     [3] scripts/post-checkout.sh
My workstation shell script, which is stored in the same repository, 
takes care of reloading the database. I think that the script will work 
for both Linux and OS X; forget Windows.

>> I would have a post-checkout script to reload the database from the disk file.
> There is no post-checkout hook, so this would have to be a script you run manually.
>
Similarly, when Charlie wants to commit, Charlie must do this:
     [1] in GUI, do a Drupal manual backup (maybe I can trigger this in 
a script)
     [1] cd repository
     [2] scripts/pre-commit.sh
     [3] svn commit ...

The pre-commit and post-checkout scripts try to take care of putting the 
database into the revision. They also take care of the problem that some 
files in some directories are created by, and therefore owned by, the 
Apache "user" and must be writable by the Apache "user" in any working 
copy. Subversion stores permissions but does not store ownership;  a 
file owned by user "www-data" under Linux must be writable by user 
"_www" on OS X. And, of course, the scripts would have to fix any other 
problems I run into. Also I might need an "UPDATE" script.

Re: Subversion on Drupal

[Ryan Schmidt <su...@ryandesign.com>]

On Aug 11, 2011, at 21:56, Andy Canfield wrote:

> My goal in learning Subversion was to put our web site under version control. Now I have my doubts as to whether Subversion can handle it.
> 
> The web site uses Drupal. And Drupal has the characteristic that much of the site is contained in a MySQL database. For example, if I install a module and set it up, the module is a disk file, but the configuration of that module is in the database. If I make a change, part of the change may be in a PHP code file on disk, part may be in the database. The database contains both user data and configuration data, intermingled.
> 
> I could get Subversion to work. I would have a pre-commit script to back up the database to a disk file in the working copy.

Not possible, because the working copy is on the client computer, whereas the pre-commit hook (and all hooks) run on the server computer. Unless you're saying you're working with a shared server-side working copy, in which case that's another thing I'd recommend not doing.


> I would have a post-checkout script to reload the database from the disk file.

There is no post-checkout hook, so this would have to be a script you run manually.

RE: Subversion on Drupal

[Bob Archer <Bo...@amsi.com>]

> My goal in learning Subversion was to put our web site under version control.
> Now I have my doubts as to whether Subversion can handle it.
> 
> The web site uses Drupal. And Drupal has the characteristic that much of the
> site is contained in a MySQL database. For example, if I install a module and
> set it up, the module is a disk file, but the configuration of that module is in
> the database. If I make a change, part of the change may be in a PHP code
> file on disk, part may be in the database.
> The database contains both user data and configuration data, intermingled.
> 
> I could get Subversion to work. I would have a pre-commit script to back up
> the database to a disk file in the working copy. I would have a post-checkout
> script to reload the database from the disk file. Along with svn commit and
> svn checkout, this would give us the ability to roll back to any earlier version.
> 
> What I can not imagine is how to get more than one person to be able to
> work on the site. Yes, Subversion would be able to merge changes to the disk
> files. But I don't see how Subversion can handle merging changes to the
> database. The MySQL database is text; perhaps someone here as experience
> with that. Can MySQL backup files be merged?

Versioning database schema changes and data changes is a challenge but not impossible. 

BTW: svn does control binaries but not as well as text files.

There are tools to do db migrations where the migrations are scripts that modify your database. You source control the migration scripts and then your build/deploy process can run these migration scripts. Basically, it inspects the current "version" of the database and runs the appropriate scripts. If you really want to get fancy your scripts can migrate UP or DOWN version too... but usually not necessary to go down version in production. 

You can also dump your database and put that in source control for dev. This way you can recover to any "version" but just loading up that dump. 

We use a tool called DBGhost. It is for SQL Server only but it has a sync engine that will build the database from the source scripts and then compare that to the current live database and make the needed changes. It supports schema and data changes. It also allows you to control data changes by columns, so for example you can have a column in a table that specifies if the rows are "shipped" rows or customer rows and have it not touch the customer data.

Be creative and I'm sure you can come up with a way to revision your database changes. If you google "source control database" you will find lots of ideas and hints.

BOb