Re: Question: How do you manage version control?

On Fri, Jun 1, 2012 at 8:28 AM, Bryan Montgomery <monty(at)english(dot)net> wrote:
> Hello,
> So we've been reviewing our processes and working on improving them. One
> area we've been lacking is a procedure to version control our database
> functions, table changes, static data etc.
>
> I'm curious how others do it. Ideally, we want it to be part of our release
> / build process so that we deploy functions.

We use subversion. The main schema and static data is in one file.
The procedures split into modules.

When a new version is rolled out, the schema and static data is not
touched, and the procedure modules are all reloaded in a particular
order specified in a text file (we call that file LOADORDER). The
final module to run is named Fixes.sql and more on that below. Fixes
is basically the cumulative set of database patches so we are
guaranteed to get into a specific state from any prior version.

>
> We've also used patch files if we need to modify tables - these are
> typically written to only run once, ie alter table add column x int. Any
> thoughts on putting this in to a process so that it can be run mutliple
> times without any issue?

Sure. This does add some errors to database logs but with some
parameters I think it's possible. The key thing is that every patch
will run in order on every upgrade. So any patches that fail because
they have already been run we expect to fail.

Each patch runs in its own transaction. Patches may change static
information but they need to do it in a way that is multi-run safe.
"update foo set bar = bar + 1" is not a good thing to put in a patch
of this sort. Every patch MUST either fail and roll back if already
run or have no impact if already run.

This does add some errors into the log files, however it also
guarantees a consistent end-point regardless of where you begin along
the prior versions.

Best Wishes,
Chris Travers