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