Обсуждение: thinking about versioning my database schema
Hi, I had always problems when sharing my database development with others via cvs. Every developer has its own database to test and to run the program. But if he gets the new files via cvs which updates the db schema, it should be an automatic process which brings the database up to date. I want to ask you if it is a reasonable way doing it like this: 1. In src/sql/updates developers can put files like update1_explain.sql 2. every database has a table like create table _version (time timestamp, filename text); 3. after cvs update every developer should run a little script which tests if there are files in src/sql/updates which are not in the table _version. If so, it runs psql -f src/sql/updates/filename and doing an insert of a new line in table _version. If there are more files to update, they will be run in the numbered order. So i am saving the schema version inside the databse. Is it a good idea? or is it nonsens and there is a much better way how to do it? I dont want to recreate the database every time because i want to keep my testdata present. regards janning
On Mon, Mar 04, 2002 at 08:10:24AM +0100, Janning Vygen wrote: > > So i am saving the schema version inside the databse. Is it a good > idea? or is it nonsens and there is a much better way how to do it? Yeah, but then you'd have to change your queries to be able to query the new tables. > I dont want to recreate the database every time because i want to > keep my testdata present. How about a script that dumps your data, dumps your db, reloads the table definitions, then reloads your data? You could have a table with rows for every table that would keep track of the current version you have, that could be updated when this script ran. -Roberto -- +----| http://fslc.usu.edu/ USU Free Software & GNU/Linux Club |------+ Roberto Mello - Computer Science, USU - http://www.brasileiro.net/ http://www.sdl.usu.edu/ - Space Dynamics Lab, Developer Make way! Make way! A PROGRAMMER HAS ARRIVED!!
Am Montag, 4. März 2002 14:09 schrieb Roberto Mello: > On Mon, Mar 04, 2002 at 08:10:24AM +0100, Janning Vygen wrote: > > So i am saving the schema version inside the databse. Is it a > > good idea? or is it nonsens and there is a much better way how to > > do it? > > Yeah, but then you'd have to change your queries to be able to > query the new tables. > > > I dont want to recreate the database every time because i want to > > keep my testdata present. > > How about a script that dumps your data, dumps your db, reloads the > table definitions, then reloads your data? You could have a table > with rows for every table that would keep track of the current > version you have, that could be updated when this script ran. i cant reload my dumped data if the table definition changed. maybe i have renamed a column or something like this. how should my dumped data know this? So i have to write an update script. But then i have to write the new schema AND have to write update scripts and still hve trouble with other developers who should easily get a corresponding schema for alle the queries they just fetched via cvs janning > -Roberto -- Planwerk 6 /websolutions Herzogstraße 86 40215 Düsseldorf fon 0211-6015919 fax 0211-6015917 http://www.planwerk6.de
Hi, > How about a script that dumps your data, dumps your db, > reloads the table > definitions, then reloads your data? You could have a table > with rows for > every table that would keep track of the current version you > have, that > could be updated when this script ran. This is exactly what I did for the update script of my own project phpPgAds... You could take a look to the code if you're interested http://phppgads.sourceforge.net Regards -- Matteo Beccati