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

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/