Re: Dev DB Structure Updates

From: Steve Tucknott <steve(at)retsol(dot)co(dot)uk>
To: PostGreSQL <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Dev DB Structure Updates
Date: 2006-05-11 18:39:33
Message-ID: 1147372774.4034.37.camel@vrsol.co.uk
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

We tend to use an app (in our case our language is Genero) to do that.

In the app language create a function for each table that contains code
along the lines of 'CREATE TABLE blah ( ...' that is followed by a call
to a generic table creator function. That function returns whether the
table existed or not. The table create function then carries on to do
table mods etc. The CREATE TABLE and ALTER TABLE strings we use can
contain place markers like:
'CREATE TABLE blah ',
' (',
' {PGS} mycolumn SOME_PGS_SPECIFIC_DATA_TYPE,
{/PGS} ',
' {IFX} mycolumn INFORMIX_EQUIVALENT_DATA_TYPE,
{/IFX} ',
'{ORA} mycolumn ORACLE_EQUIVALENT_DATA_TYPE,
{/ORA} ',
' commonColumn1 CHAR(1), ',
' commonColumn2 NUMERIC(7), ' ......

The creator/modification function then detects the database type being
implemented and 'strips out' the 'irrelevant' code.

We then have a driver app, that contains calls to all the functions
required to 'build'/'modify' (tables/triggers/procedures) the database.
This enables us to easily replicate a database in the current state,
irrespective of the database being used (although because Postgresql has
been so good for us, tends nowadays to be Postgresql anyway). The
functions also alter the tables to bring them up to 'speed' - so we
promote the table creator driver/functions up through dev/test/release
and the app makes sure that test is brought upto dev state, without
losing data in the tables (if adding not null cols, we then update the
table with whatever default is required, then modify the column 'not
null')

The main driver program also loads a table parameter table (a table
containing parameters pertaining to the creation of all the other tables
in the database) - this 'parameter' table is read by the table creator
function, to see what space the table should be created in, how big it
should be, whether it should be dropped before being 're-created',
whether it should just be dropped etc.

If you want more details, let me know and I'll happily provide actual
examples - Genero is a 4gl, so the code is quite legible and easily
converted to another language.

On Thu, 2006-05-11 at 09:55 -0700, operationsengineer1(at)yahoo(dot)com wrote:

> hi all,
>
> i'm reading agile web dev with rails and the author
> discusses how he makes text sql snapshots of his
> databases. when a new column needs to be added, for
> example, he updates the text sql and reloads the file.
>
> in mysql, he puts a statement like...
>
> if table_example exists delete table_example
>
> that is close, but not the exact code. he is trying
> to delete the existing table (wrong structure) and
> replace it with the new structure.
>
> should i uncomment...
>
> -- DROP DATABASE "edb-bms-dev";
>
> to get...
>
> DROP DATABASE "edb-bms-dev";
>
> in order to get similar behavior?i tried using the if
> table_example exists dyntax in pgsql and it didn't
> work.
>
> when i was using a similar strategy, except at the
> table level, the output kicked out an error saying the
> table wasn't there. however, the update went as
> expected - i thought the error was strange and must've
> had something to do with me dropping the table.
>
> is this the best way to control db revisions and make
> updates? one facet of this plan that appeals to me is
> that i can control column order within the db. this
> is important when using rails b/c rails displays the
> columns in order when it generates its web pages, by
> default.
>
> i'd appreciate hearing what others do.
>
> __________________________________________________
> Do You Yahoo!?
> Tired of spam? Yahoo! Mail has the best spam protection around
> http://mail.yahoo.com
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend

Regards,

Steve Tucknott
ReTSol Ltd

DDI: 01903 828769
Mobile: 0773 671 5772

In response to

Browse pgsql-novice by date

  From Date Subject
Next Message Verena Ruff 2006-05-12 12:35:34 Re: index not used with inherited tables
Previous Message Sean Davis 2006-05-11 17:06:28 Re: Dev DB Structure Updates