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

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?

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?

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

Thanks.

--
Sergey Samokhin

P.S. If there is other convinient way to do upgrades which I've missed
- please let me know. I'm still a novice in the PostgreSQL/SQL world.

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

On Fri, Aug 28, 2009 at 12:42:59AM +0400, Sergey Samokhin wrote:
> But how do programmers guarantee that ALTER's they have wrote will
> always be applied by administrators to the corresponding version of
> the database?

How about using the normal integrity constraints that databases provide?
Have some table like:

  CREATE TABLE version (
    feature  TEXT PRIMARY KEY,
    depends  TEXT REFERENCES version,
    inserted TIMESTAMP DEFAULT now()
  );

and at the start of every modification script put a row (or several)
into the table:

  BEGIN;
  INSERT INTO version (feature,depends) VALUES
    ('table foo',NULL);
  CREATE TABLE foo ( id TEXT PRIMARY KEY, value TEXT );
  COMMIT;

and then you can check to see if the constraints are met by doing:

  BEGIN;
  INSERT INTO version (feature,depends) VALUES
    ('table foo add startend dates','table foo');
  ALTER TABLE foo
    ADD COLUMN startdate TIMESTAMP DEFAULT now(),
    ADD COLUMN enddate   TIMESTAMP DEFAULT 'infinity';
  COMMIT;

Not sure if that's the sort of thing that you want/need but I don't
think there's a general solution to the problem.  Determining the
relevant context for this sort of thing is hard.

--
  Sam  http://samason.me.uk/

Hello!

On Fri, Aug 28, 2009 at 5:56 PM, Sam Mason<sam@samason.me.uk> wrote:

> Not sure if that's the sort of thing that you want/need but I don't
> think there's a general solution to the problem.  Determining the
> relevant context for this sort of thing is hard.

The solutions you and Steve Atkins offered seem what I have been
looking for so far. Thanks.

--
Sergey Samokhin