Re: database development

Lee Doolan <rldoolanREMOVE(at)pacbell(dot)net> writes:

> In order to maintain the database schema, I started out by using
> a script like this one which I kept stored in my source
> repository:

[snip]

> Then, any time I wanted to change the schema, I would change the
> script, rerun it and, voila! an improved database.
>
> Somewhere along the line, I decided to stop maintaining the
> script and to use pg_dump and pg_restore to maintain both the
> schema and the data. This decision is one which I sometimes
> regret but, hey, I could get back to the shell script form in
> a few hours if I really wanted to.
>
> My question is this: what techniques do other developers use in
> these circumstances?

Well, I use a tool (Enhydra/DODS) that generates the schema and Java
access classes for me, and I have a CreateInitialData class that does
whatever population is necessary. When the dev schema changes, I blow
away the whole DB and recreate it, then rerun CreateInitialData. So
it's not too different from your shell script approach. Clients are
told that any data they put in the system during development can go
away without notice.

For production changes to the schema (which have been quite rare) I
write custom SQL to do ALTER TABLE or whatever. Postgres has made
this difficult on a few occasions (eg due to lack of ALTER TABLE DROP
CONSTRAINT) but so far nothing that can't be worked around.

HTH...

-Doug
--
Let us cross over the river, and rest under the shade of the trees.
--T. J. Jackson, 1863