Re: A safe way to upgrade table definitions by using ALTER's

On Aug 27, 2009, at 1:42 PM, Sergey Samokhin wrote:

> Hello.
>
> As I know upgrading database structure from one version to another is
> usually done by applying some sql-script with a set of ALTER's that do
> all the work.
>
> But how do programmers guarantee that ALTER's they have wrote will
> always be applied by administrators to the corresponding version of
> the database?

In the application is where I do it, though it would be possible to
create a .sql script that errored out if the existing version were
not the one it expected.

>
> Is there a standard way to store some kind of metainformation in DB
> (like version of the current definitions of tables) and then check if
> it is too old for being upgraded by a given script?

There's no standard way at the database level, though there are
application frameworks that support it in a way that's standard for
that framework.

>
> By "database structure" I mean definition of tables an application
> uses (data types, constraints, modificators etc), stored procedures
> etc.

I tend to keep a single row table in the database that contains
the current schema version, then have the application apply
upgrade / downgrade patches as needed (or bail out and
tell the user to do it).

Cheers,
Steve