The normal way I've seen database schema versioning is to create a
database version table and use an integer as the schema version.

I've found a few instances where applications use the product version
also as the schema version.

I like the latter because a specific version of my application must
only be used with a specific version of our schema, and so it keeps
things simple: I just have to change one version in my application,
and then write my upgrade logic based on that for both the software
and the database schema. Each build changes the product version
(build number is part of the product version), and so for any software
change there is always a different product version, and so maintaining
two separate versions seems like unnecessary work.

Are there any documents, web sites..., that explain the pros and cons
for the different ways of doing schema versioning?

Even when I use the product version, that can be translated into an
integer, and so it is still a single number, but just formatted in
n.n.n.n format.

David <foo.bar24 (AT) yahoo (DOT) com> writes:

A database schema is unlike an application in that it's much more
troublesome to swap out one schema and try a different one in its place.
For this reason, and others, I find a version number doesn't tell me
enough to reliably re-create the state of the schema.

Instead, I follow the discipline in “Refactoring Databases”, making
changes to the schema only via incremental patches that preserve the
semantics of the existing data. Those patches are kept in a VCS as
discrete schema patch files, with descriptive names; each patch file
makes a small, conceptually distinct, atomic change to the schema.

I track the schema's state with a “patches applied” table in a separate
schema:

=====
CREATE SCHEMA revision_control;
COMMENT ON SCHEMA revision_control IS
'Metadata for revisions to database schema.';

CREATE TABLE revision_control.entry (
revision INTEGER NOT NULL,
name VARCHAR NOT NULL,
applied TIMESTAMP WITH TIME ZONE NOT NULL,
PRIMARY KEY (revision)
);
COMMENT ON TABLE revision_control.entry IS
'Record of individual revisions applied to the running schema.';

COMMENT ON COLUMN revision_control.entry.revision IS
'Sequential revision number of the schema revision.';
COMMENT ON COLUMN revision_control.entry.name IS
'Name of the schema revision applied.';
COMMENT ON COLUMN revision_control.entry.applied IS
'Timestamp when the revision was applied to the database.';

INSERT INTO revision_control.entry
(revision, name, applied)
VALUES
(0, 'initial state', NOW());
=====

Each new entry corresponds to a patch file, written in the DDL of the
database, that has been applied to this instance of the schema. The
files are named to be briefly descriptive of the atomic change being
made by the DDL.

The author of “Refactoring Databases” advocates keeping the DDL of each
patch as another field, of type TEXT, directly in (what corresponds to)
my ‘revision_control.entry’ table above. That has the advantage of
making the changes available directly from the database.

I find the granularity of “what changes have been made to the schema” is
much finer-grained than “what version of the product is this”. A single
increment of the application version would encompass many changes made
to the schema. Likewise, many changes made to the application don't
correspond to a change in the schema. Not a good match.

The best treatment of this topic that I know is the book “Refactoring
Databases” <URL:http://www.databaserefactoring.com/>.

You can get a brief overview of the book covers by reading an essay
<URL:http://www.agiledata.org/essays/databaseRefactoring.html> by the
same author. The essay also includes links to further resources.

--
\ “[i]t is impossible for anyone to begin to learn that which he |
`\ thinks he already knows.” —Epictetus, _Discourses_ |
_o__) |
Ben Finney

On 01/03/2010 08:00 PM, David wrote:
But multiple versions of the application could use the same schema
version! Just think of bug fix releases...

Since DB schema migration is a lot harder than application version
migration it does make sense to keep them separate. That way you do not
need to go through all the hoops of schema migration whenever the schema
stays consistent between app versions.

Another reason that makes me favor the first approach is that then the
schema is self contained. You look at the schema and can then determine
which steps have to be done to upgrade to another schema. The
application then only needs to verify whether the schema version is
compatible with the app version. Note, that you probably want to do
schema migration offline (i.e. when the app is not running) for various
reasons. If you tie this too closely into the application that may mean
extensive downtime when the DB is large since you always need to upgrade
the app to get the schema to the next version.

Kind regards

robert

--
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/