[leaving the original text, as it is reply to an older posting] On Fri, 2006-03-31 at 21:26, Jim Nasby wrote: > On Mar 29, 2006, at 3:25 AM, Csaba Nagy wrote: > > >> Could somebody explain me, or point me to a resource where I can find > >> out what is the recommended practice when a live db needs to be > >> replaced > >> with a new version of it that has a slightly different structure? > > > > Our development infrastructure includes a development data base > > cluster, > > with one data base per developer, and a staging data base with a > > largish > > deployment of the current production data base version. > > > > The developers are free to test whatever data base modifications they > > need on their private development data base. We have a "setup_db" > > script, which creates the data base structure + initial data. The > > script > > is based on an XML file which is processed by XSLT to generate the > > actual schema for Oracle/Postgres. So the developers usually recreate > > Have you considered releasing that creation code? I know there's lots > of places that have a need for stuff like this, and having used a > similar system before I know how powerful it can be. Unfortunately > the company I worked for was too paranoid to release the database > creation tool we used. :( Had they, maybe you wouldn't have had to > write one from scratch. I guess I still would have written it from scratch :-) There ARE some systems which do data mapping from/to code, but they all have problems: too generic, never exactly what you want, etc. What I did is: create a custom XML schema to describe the features I need from the DB, and a pair of XSL style sheets to translate that for postgres/oracle. I guess it would have been better to take another approach, i.e. write some templates (using some templating engine) and feed the data from the XML into that, but the result would be the same, if maybe a bit more readable... The conclusion is: if I would have to set up another system doing something similar, I would write it from scratch again. It really didn't take that much time, and it does exactly what I want. The point of the post was that it's good to have such a system, where the main features are stored in some common format (XML is just an example), which can be version controlled, and the DB specific parts partly generated, partly stored separately and version controlled too. Regarding releasing what I have, it's not much use, cause it's too specific for what we have here, and ugly too. But it works. Cheers, Csaba.
