[svn.haxx.se] · SVN Dev · SVN Users · SVN Org · TSVN Dev · TSVN Users · Subclipse Dev · Subclipse Users · this month's index

Re: Version control for databases

From: <glists_at_greywether.com>
Date: 2006-05-16 05:42:44 CEST

We do something similar to this. We don't name out patches by the day
they're created but we do script *all* schema-related changes to the
database including the original creation script. We keep these sql
scripts in a subversion controlled folder.

One additional thing we do is keep a table in the database called
"AppliedPatches". Our procedure is such that all patch script's last
task is to write an entry into this table (along with a hand-managed
patchID). This lets us see, at a glance, what "patchlevel" the database
is at.

- Gary

Jamie Lawrence wrote:
> On Mon, 15 May 2006, mike nicholaides wrote:
>> One way I can think of is to dump the contents of the database into a
>> SQL script, and put that under version control. Of course, that
>> doesn't seem like the most elegant solution.
> If your dev team for the DB isn't too large, here's a fairly
> generic approach that, while it has overhead, isn't too bad.
> Somewhere in your project, assume a directory called 'sql'
> in your branch. Assuming some form of the DB already exists,
> the first entity committed will be the full schema. If you
> were committing this today, this would be stored in
> sql/2006/05/15/01.sql
> Now someone goes today in and add a field to a table (or
> whatever). They store the changes required to be made to
> the DB in
> sql/2006/05/15/02.sql
> The idea being, of course, to serialize changes such that
> they can be applied in order to transform the DB from the
> old state to the new one.
> Details and gotchas:
> - It is a good idea to write matching SQL that rolls back
> from the new state to the old state, especially if you
> depend on VC to roll back a running app. This, of course,
> adds quite a bit of work, especially if you need to capture
> data.
> - The SQL files need to be considered read only upon commit,
> if multiple people are working on the DB in order to keep
> serialization. This means that if, in the example above,
> 02.sql had an error, correcting it would have to be done
> in 03.sql (or whenever later). (If someone commits a syntax
> error, after spanking them, this is the only time to modify
> a file and null op it, and then make the fix in a later
> file.)
> - In situations where one is applying DB changes to different
> systems, it is a good idea to commit something like
> sql/2006/05/15/03.checkpoint
> to indicate that that is the point up through which the
> SQL patches have been applied in a given branch
> - Merging branches with SQL changes has to be done manually,
> as these files must (per above) be considered read only.
> - This really has nothing to do with this particular approach,
> but good comments here are important, since one is storing
> a series of DB diffs that humans might well have to step
> through.
> If one becomes reasonably confident in this scheme, scripting
> updates to the DB is really easy. I apply them manually,
> because I don't fully trust these coming in from other developers
> to be correct.
> As mentioned, it is a bit of work, but this has worked well
> for us.
> HTH,
> -j

To unsubscribe, e-mail: users-unsubscribe@subversion.tigris.org
For additional commands, e-mail: users-help@subversion.tigris.org
Received on Tue May 16 05:44:04 2006

This is an archived mail posted to the Subversion Users mailing list.

This site is subject to the Apache Privacy Policy and the Apache Public Forum Archive Policy.