Thread: database development
I am developing a website which has a postgres database as one
of its components. During development, it is not uncommon to
have to add a new attribute to a table, nor is it uncommon to
add an entirely new table to the schema when changes are made
to the system design --usually subsequent to a meeting
attended by the sales and mgt folks, but I shouldn't get
started on _that_.
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:
SCRIPT> #!/bin/bash
SCRIPT> [. . .]
SCRIPT> ########################################################################
SCRIPT> # A S _ A N O N _ U S E R _ S E Q #
SCRIPT> ########################################################################
SCRIPT> # this sequence is incremented every time a new anonymous user is
SCRIPT> # created in the database
SCRIPT> echo "------------------------------------------------------------------------"
SCRIPT> echo "Creating as_anon_user_seq"
SCRIPT> psql -U "allseer" $ALLSEER_DBNAME <<-EOF
SCRIPT> create sequence as_anon_user_seq;
SCRIPT> EOF
SCRIPT> [. . .]
SCRIPT>
SCRIPT> ########################################################################
SCRIPT> # A S _ P R O J E C T S #
SCRIPT> ########################################################################
SCRIPT> echo "------------------------------------------------------------------------"
SCRIPT> echo "Creating as_projects"
SCRIPT> psql -U "allseer" $ALLSEER_DBNAME <<-EOF
SCRIPT>
SCRIPT> create table as_projects (
SCRIPT> proj_id serial PRIMARY KEY,
SCRIPT> proj_name text UNIQUE,
SCRIPT> proj_url text,
SCRIPT> proj_descr text,
SCRIPT>
SCRIPT> created timestamp,
SCRIPT> modified timestamp default now());
SCRIPT>
SCRIPT> grant all on as_projects to public;
SCRIPT>
SCRIPT> EOF
SCRIPT> [. . .]
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?
-lee
Lee Doolan <rldoolanREMOVE@pacbell.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
Doug McNaught wrote: > > 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. Would you please describe Enhydra tool? A quick look at its website, didn't tell me a complete picture. Regards, Haroldo.
Haroldo Stenger <hstenger@adinet.com.uy> writes: > Doug McNaught wrote: > > 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. > > Would you please describe Enhydra tool? A quick look at its website, > didn't tell me a complete picture. Basically, you design your schema in a Java GUI app (called DODS, for Data Object Design Studio). It generates the SQL to create your database, and also generates Java classes that implement an "object layer" over that schema. You don't (generally) have to write and submit your own queries; you can "build" them by calls to a generated API. Referenced rows in other tables are fetched automatically and instantiated as object references. It's pretty tightly tied into the Enhydra web application framework (which we use for most of what we do), so it may not be super-useful on its own. I like it though. [The Java GUI app is slow and occasionally buggy. Luckily, it stores the schema as XML, so we nowadays just edit the XML by hand and tell DODS to generate its output without putting up the GUI. Much less frustrating.] -Doug -- Let us cross over the river, and rest under the shade of the trees. --T. J. Jackson, 1863