RE: Subversion on Drupal

> 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
Received on 2011-08-12 15:45:31 CEST