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/