pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
thinking about versioning my database schema
5+ messages / 4 participants
[nested] [flat]

* thinking about versioning my database schema
@ 2002-03-04 07:10  Janning Vygen <vygen@gmx.de>
  0 siblings, 2 replies; 5+ messages in thread

From: Janning Vygen @ 2002-03-04 07:10 UTC (permalink / raw)
  To: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: [SQL] thinking about versioning my database schema
@ 2002-03-04 07:51  Dan Langille <dan@langille.org>
  parent: Janning Vygen <vygen@gmx.de>
  1 sibling, 0 replies; 5+ messages in thread

From: Dan Langille @ 2002-03-04 07:51 UTC (permalink / raw)
  To: Janning Vygen <vygen@gmx.de>; +Cc: pgsql-admin@postgresql.org

On 4 Mar 2002 at 8:10, 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?

I have a system table.  One of the fields is the database version.  If I 
change the schema, I modify the version.  The applications knows what 
version it can run with.  If it finds an older version, it reports this ot 
the user and suggests they upgrade their database.
-- 
Dan Langille
The FreeBSD Diary - http://freebsddiary.org/ - practical examples




^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: thinking about versioning my database schema
@ 2002-03-04 13:09  Roberto Mello <rmello@cc.usu.edu>
  parent: Janning Vygen <vygen@gmx.de>
  1 sibling, 1 reply; 5+ messages in thread

From: Roberto Mello @ 2002-03-04 13:09 UTC (permalink / raw)
  To: Janning Vygen <vygen@gmx.de>; +Cc: pgsql-sql

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!!



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: thinking about versioning my database schema
@ 2002-03-04 13:49  Janning Vygen <vygen@gmx.de>
  parent: Roberto Mello <rmello@cc.usu.edu>
  0 siblings, 0 replies; 5+ messages in thread

From: Janning Vygen @ 2002-03-04 13:49 UTC (permalink / raw)
  To: Roberto Mello <rmello@cc.usu.edu>; +Cc: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: thinking about versioning my database schema
@ 2002-03-05 08:13  Matteo Beccati <m.beccati@crpsoftware.it>
  0 siblings, 0 replies; 5+ messages in thread

From: Matteo Beccati @ 2002-03-05 08:13 UTC (permalink / raw)
  To: pgsql-sql

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




^ permalink  raw  reply  [nested|flat] 5+ messages in thread


end of thread, other threads:[~2002-03-05 08:13 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-03-04 07:10 thinking about versioning my database schema Janning Vygen <vygen@gmx.de>
2002-03-04 07:51 ` Dan Langille <dan@langille.org>
2002-03-04 13:09 ` Roberto Mello <rmello@cc.usu.edu>
2002-03-04 13:49   ` Janning Vygen <vygen@gmx.de>
2002-03-05 08:13 Re: thinking about versioning my database schema Matteo Beccati <m.beccati@crpsoftware.it>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox