Re: Dev DB Structure Updates - Mailing list 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@yahoo.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